#!/bin/bash

# ============ CONFIGURATION ============
SOURCE_HOST="127.0.0.1"
SOURCE_PORT="3307"
TARGET_HOST="100.125.94.41"
TARGET_PORT="3306"
DB_NAME="giftwrap_mts"
USER="bordales_admin"

read -sp "MySQL Password: " PASS
echo ""
# ======================================

echo "========================================="
echo "COLUMN SYNCHRONIZATION SCRIPT"
echo "========================================="
echo "Checks for missing columns in existing tables"
echo "Source: $SOURCE_HOST:$SOURCE_PORT"
echo "Target: $TARGET_HOST:$TARGET_PORT"
echo "Database: $DB_NAME"
echo "========================================="

# Clear PAGER
unset PAGER

# Function to get column definition
get_column_def() {
    local table=$1
    local column=$2
    mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $USER -p$PASS $DB_NAME -sN -e \
        "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
         FROM information_schema.columns 
         WHERE table_schema = '$DB_NAME' 
           AND table_name = '$table'
           AND column_name = '$column'" 2>/dev/null
}

echo ""
echo "🔍 Checking for missing columns in existing tables..."

# Get all tables that exist in BOTH databases
TABLES=$(mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $USER -p$PASS $DB_NAME -sN -e "SHOW TABLES" 2>/dev/null)

TOTAL_COLUMNS_ADDED=0

for TABLE in $TABLES; do
    # Check if table exists in target
    EXISTS_IN_TARGET=$(mysql -h $TARGET_HOST -P $TARGET_PORT -u $USER -p$PASS $DB_NAME -sN -e \
                      "SELECT COUNT(*) FROM information_schema.tables 
                       WHERE table_schema = '$DB_NAME' AND table_name = '$TABLE'" 2>/dev/null)
    
    if [ "$EXISTS_IN_TARGET" -eq 1 ]; then
        echo ""
        echo "📊 Table: $TABLE"
        echo "   Checking columns..."
        
        # Get columns from source
        SOURCE_COLUMNS=$(mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $USER -p$PASS $DB_NAME -sN -e \
                        "SELECT column_name FROM information_schema.columns 
                         WHERE table_schema = '$DB_NAME' AND table_name = '$TABLE'
                         ORDER BY ordinal_position" 2>/dev/null)
        
        # Get columns from target
        TARGET_COLUMNS=$(mysql -h $TARGET_HOST -P $TARGET_PORT -u $USER -p$PASS $DB_NAME -sN -e \
                        "SELECT column_name FROM information_schema.columns 
                         WHERE table_schema = '$DB_NAME' AND table_name = '$TABLE'
                         ORDER BY ordinal_position" 2>/dev/null)
        
        # Find missing columns
        MISSING_COLUMNS=$(echo "$SOURCE_COLUMNS" | grep -v -F "$TARGET_COLUMNS")
        
        if [ -n "$MISSING_COLUMNS" ]; then
            echo "   Missing columns found!"
            
            for COLUMN in $MISSING_COLUMNS; do
                # Get full column definition from source
                COL_DEF=$(get_column_def "$TABLE" "$COLUMN")
                
                if [ -n "$COL_DEF" ]; then
                    IFS=$'\t' read -r COL_NAME COL_TYPE IS_NULLABLE COL_DEFAULT EXTRA COL_COMMENT <<< "$COL_DEF"
                    
                    # Build ALTER TABLE statement
                    ALTER_SQL="ALTER TABLE \`$TABLE\` ADD COLUMN \`$COL_NAME\` $COL_TYPE"
                    
                    # Add NULL/NOT NULL
                    if [ "$IS_NULLABLE" = "NO" ]; then
                        ALTER_SQL="$ALTER_SQL NOT NULL"
                    else
                        ALTER_SQL="$ALTER_SQL NULL"
                    fi
                    
                    # Add DEFAULT if exists
                    if [ "$COL_DEFAULT" != "NULL" ] && [ -n "$COL_DEFAULT" ]; then
                        ALTER_SQL="$ALTER_SQL DEFAULT '$COL_DEFAULT'"
                    fi
                    
                    # Add AUTO_INCREMENT if applicable
                    if [[ "$EXTRA" == *"auto_increment"* ]]; then
                        ALTER_SQL="$ALTER_SQL AUTO_INCREMENT"
                    fi
                    
                    # Add COMMENT if exists
                    if [ -n "$COL_COMMENT" ]; then
                        ALTER_SQL="$ALTER_SQL COMMENT '$COL_COMMENT'"
                    fi
                    
                    # Add column position (try to maintain order)
                    # This is complex, so we'll add at the end for simplicity
                    ALTER_SQL="$ALTER_SQL;"
                    
                    echo "   + Adding column: $COL_NAME ($COL_TYPE)"
                    
                    # Execute ALTER TABLE
                    if mysql -h $TARGET_HOST -P $TARGET_PORT -u $USER -p$PASS $DB_NAME -sN -e "$ALTER_SQL" 2>/dev/null; then
                        echo "     ✓ Added successfully"
                        TOTAL_COLUMNS_ADDED=$((TOTAL_COLUMNS_ADDED + 1))
                    else
                        echo "     ✗ Failed to add column"
                    fi
                fi
            done
        else
            echo "   ✓ All columns exist"
        fi
    fi
done

echo ""
echo "========================================="
echo "SYNCHRONIZATION COMPLETE"
echo "========================================="
echo "Total columns added: $TOTAL_COLUMNS_ADDED"
echo "========================================="