#!/usr/bin/env bash
# =============================================================
# migrate_username.sh
# Username migration script for IEMR MySQL databases.
# Compatible with Linux and Windows Git Bash.
# Run with: bash migrate_username.sh
# =============================================================

echo "========================================================"
echo "       IEMR Username Migration Script"
echo "========================================================"
echo ""

# ── Simple input using /dev/tty directly ─────────────────────
read_input() {
    local prompt="$1"
    local is_password="$2"

    if [[ "$is_password" == "true" ]]; then
        # Read password without echo
        printf "%s" "$prompt" > /dev/tty
        IFS= read -r READ_VALUE < /dev/tty
    else
        printf "%s" "$prompt" > /dev/tty
        IFS= read -r READ_VALUE < /dev/tty
    fi
}

read_input "Enter OLD Username  : "
OLD_USER="$READ_VALUE"

read_input "Enter NEW Username  : "
NEW_USER="$READ_VALUE"

read_input "Enter MySQL Host    : "
MYSQL_HOST="$READ_VALUE"

read_input "Enter MySQL User    : "
MYSQL_USER="$READ_VALUE"

read_input "Enter MySQL Password: " "true"
MYSQL_PASS="$READ_VALUE"

echo ""
echo "--------------------------------------------------------"
echo "  Old Username : $OLD_USER"
echo "  New Username : $NEW_USER"
echo "  MySQL Host   : $MYSQL_HOST"
echo "  MySQL User   : $MYSQL_USER"
echo "--------------------------------------------------------"
echo ""

# ── Core executor ─────────────────────────────────────────────
# Write password to a temp file so mysql doesn't prompt for it
MYSQL_DEFAULTS_FILE=$(mktemp)
cat > "$MYSQL_DEFAULTS_FILE" << MYCNF
[client]
password=$MYSQL_PASS
MYCNF

execute_update() {
    local label="$1"
    local sql="$2"

    local full_sql="${sql}
SELECT ROW_COUNT();"

    local rows
    rows=$(mysql \
        --defaults-extra-file="$MYSQL_DEFAULTS_FILE" \
        -h "$MYSQL_HOST" \
        -u "$MYSQL_USER" \
        -N -s \
        -e "$full_sql" 2>/dev/null | tail -n 1)

    rows="${rows:-0}"

    printf "%-60s %s rows updated\n" "$label" "$rows"
}

# ── Migrations ────────────────────────────────────────────────

execute_update "db_iemr.m_user" "
UPDATE db_iemr.m_user
SET username='$NEW_USER'
WHERE username='$OLD_USER';"

execute_update "db_identity.i_beneficiarydetails_rmnch" "
UPDATE db_identity.i_beneficiarydetails_rmnch
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE beneficiaryDetails_RmnchId IN (
    SELECT beneficiaryDetails_RmnchId FROM (
        SELECT beneficiaryDetails_RmnchId
        FROM db_identity.i_beneficiarydetails_rmnch
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiaryfamilymapping" "
UPDATE db_identity.i_beneficiaryfamilymapping
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenFamilyMapId IN (
    SELECT BenFamilyMapId FROM (
        SELECT BenFamilyMapId
        FROM db_identity.i_beneficiaryfamilymapping
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiarydetails" "
UPDATE db_identity.i_beneficiarydetails
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BeneficiaryDetailsId IN (
    SELECT BeneficiaryDetailsId FROM (
        SELECT BeneficiaryDetailsId
        FROM db_identity.i_beneficiarydetails
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiarymapping" "
UPDATE db_identity.i_beneficiarymapping
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenMapId IN (
    SELECT BenMapId FROM (
        SELECT BenMapId
        FROM db_identity.i_beneficiarymapping
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiaryidentity" "
UPDATE db_identity.i_beneficiaryidentity
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenIdentityId IN (
    SELECT BenIdentityId FROM (
        SELECT BenIdentityId
        FROM db_identity.i_beneficiaryidentity
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_householddetails" "
UPDATE db_identity.i_householddetails
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE houseHoldDetailsId IN (
    SELECT houseHoldDetailsId FROM (
        SELECT houseHoldDetailsId
        FROM db_identity.i_householddetails
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiaryimage" "
UPDATE db_identity.i_beneficiaryimage
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenImageId IN (
    SELECT BenImageId FROM (
        SELECT BenImageId
        FROM db_identity.i_beneficiaryimage
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiaryaddress" "
UPDATE db_identity.i_beneficiaryaddress
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenAddressID IN (
    SELECT BenAddressID FROM (
        SELECT BenAddressID
        FROM db_identity.i_beneficiaryaddress
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiaryservicemapping" "
UPDATE db_identity.i_beneficiaryservicemapping
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenServiceMapID IN (
    SELECT BenServiceMapID FROM (
        SELECT BenServiceMapID
        FROM db_identity.i_beneficiaryservicemapping
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.m_beneficiaryregidmapping" "
UPDATE db_identity.m_beneficiaryregidmapping
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenRegId IN (
    SELECT BenRegId FROM (
        SELECT BenRegId
        FROM db_identity.m_beneficiaryregidmapping
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_bornbirthdeatils" "
UPDATE db_identity.i_bornbirthdeatils
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BornBirthDeatilsId IN (
    SELECT BornBirthDeatilsId FROM (
        SELECT BornBirthDeatilsId
        FROM db_identity.i_bornbirthdeatils
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiarycontacts" "
UPDATE db_identity.i_beneficiarycontacts
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenContactsID IN (
    SELECT BenContactsID FROM (
        SELECT BenContactsID
        FROM db_identity.i_beneficiarycontacts
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_beneficiaryconsent" "
UPDATE db_identity.i_beneficiaryconsent
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenConsentID IN (
    SELECT BenConsentID FROM (
        SELECT BenConsentID
        FROM db_identity.i_beneficiaryconsent
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_benfamilytag" "
UPDATE db_identity.i_benfamilytag
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenFamilyTagId IN (
    SELECT BenFamilyTagId FROM (
        SELECT BenFamilyTagId
        FROM db_identity.i_benfamilytag
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_identity.i_benfamilytag (secondary update)" "
UPDATE db_identity.i_benfamilytag
SET CreatedBy = '$NEW_USER'
WHERE BenFamilyTagId > 0
  AND CreatedBy = '$OLD_USER';"

execute_update "db_iemr.ELIGIBLE_COUPLE_TRACKING" "
UPDATE db_iemr.ELIGIBLE_COUPLE_TRACKING
SET created_by = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.ELIGIBLE_COUPLE_TRACKING
        WHERE created_By = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_pregnant_woman_register" "
UPDATE db_iemr.t_pregnant_woman_register
SET created_by = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_pregnant_woman_register
        WHERE created_By = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_cbacdetails" "
UPDATE db_iemr.t_cbacdetails
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_cbacdetails
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_eligible_couple_register" "
UPDATE db_iemr.t_eligible_couple_register
SET created_by = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_eligible_couple_register
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_delivery_outcome" "
UPDATE db_iemr.t_delivery_outcome
SET created_by = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_delivery_outcome
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_pnccare" "
UPDATE db_iemr.t_pnccare
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_pnccare
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_infant_register" "
UPDATE db_iemr.t_infant_register
SET created_by = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_infant_register
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_anccare" "
UPDATE db_iemr.t_anccare
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID
        FROM db_iemr.t_anccare
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_pnc_visit" "
UPDATE db_iemr.t_pnc_visit
SET created_by  = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID
        FROM db_iemr.t_pnc_visit
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"


execute_update "db_iemr.t_anc_visit" "
UPDATE db_iemr.t_anc_visit
SET created_by  = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID
        FROM db_iemr.t_anc_visit
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_benvisitdetail" "
UPDATE db_iemr.t_benvisitdetail
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE BenVisitID IN (
    SELECT BenVisitID FROM (
        SELECT BenVisitID
        FROM db_iemr.t_benvisitdetail
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_child_register" "
UPDATE db_iemr.t_child_register
SET created_by  = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID
        FROM db_iemr.t_child_register
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_childvaccinedetail1" "
UPDATE db_iemr.t_childvaccinedetail1
SET CreatedBy  = '$NEW_USER',
    ModifiedBy = '$NEW_USER'
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID
        FROM db_iemr.t_childvaccinedetail1
        WHERE CreatedBy = '$OLD_USER'
    ) AS temp
);"

execute_update "db_iemr.t_pmsma" "
UPDATE db_iemr.t_pmsma
SET created_by = '$NEW_USER',
    updated_by = '$NEW_USER'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM db_iemr.t_pmsma
        WHERE created_by = '$OLD_USER'
    ) AS temp
);"

# ── Cleanup temp file ─────────────────────────────────────────
rm -f "$MYSQL_DEFAULTS_FILE"

# ── Summary ───────────────────────────────────────────────────
echo ""
echo "========================================================"
echo "Username migration completed."
echo "Old Username : $OLD_USER"
echo "New Username : $NEW_USER"
echo "========================================================"