#!/bin/bash

DB="growthdeskuser_wa_username_es2"
USER="growthdeskuser_wa_username_esusr"

echo "Enter MySQL password:"
read -s PASSWORD

echo ""
echo "Fetching tables with 'id' column..."

# Get all tables with an 'id' column that is an integer type
TABLES=$(mysql -u "$USER" -p"$PASSWORD" -N -e "
    SELECT table_name, data_type
    FROM information_schema.columns
    WHERE table_schema = '$DB'
    AND column_name = 'id'
    AND data_type IN ('int', 'tinyint', 'smallint', 'mediumint', 'bigint');
")

if [ -z "$TABLES" ]; then
    echo "No tables with integer 'id' column found."
    exit 1
fi

echo "Running ALTER TABLE on each table..."
echo ""

SUCCESS=0
FAILED=0
SKIPPED=0

while IFS=$'\t' read -r TABLE DATATYPE; do
    echo -n "  Altering $TABLE (id is $DATATYPE) ... "

    # Use the actual data type to preserve BIGINT etc.
    DATATYPE_UPPER=$(echo "$DATATYPE" | tr '[:lower:]' '[:upper:]')

    # Check if primary key already exists
    HAS_PK=$(mysql -u "$USER" -p"$PASSWORD" -N -e "
        SELECT COUNT(*) FROM information_schema.table_constraints
        WHERE table_schema = '$DB'
        AND table_name = '$TABLE'
        AND constraint_type = 'PRIMARY KEY';
    " 2>/dev/null)

    if [ "$HAS_PK" -gt 0 ]; then
        # Drop existing primary key first, then re-add with auto increment
        RESULT=$(mysql -u "$USER" -p"$PASSWORD" "$DB" -e "
            ALTER TABLE \`$TABLE\` DROP PRIMARY KEY, MODIFY \`id\` $DATATYPE_UPPER NOT NULL AUTO_INCREMENT PRIMARY KEY;
        " 2>&1)
    else
        RESULT=$(mysql -u "$USER" -p"$PASSWORD" "$DB" -e "
            ALTER TABLE \`$TABLE\` MODIFY \`id\` $DATATYPE_UPPER NOT NULL AUTO_INCREMENT PRIMARY KEY;
        " 2>&1)
    fi

    if [ $? -eq 0 ]; then
        echo "✓ Done"
        ((SUCCESS++))
    else
        echo "✗ Failed: $RESULT"
        ((FAILED++))
    fi
done <<< "$TABLES"

echo ""
echo "=============================="
echo "  Done! Success: $SUCCESS | Failed: $FAILED | Skipped: $SKIPPED"
echo "=============================="