#!/bin/bash

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"

echo "Enter MySQL password for $USER:"
read -s PASS
echo ""
unset PAGER

TABLE="catalog_products"

echo "Fixing columns for: $TABLE"
echo "========================================"

# Get all columns from source with full details
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'
     ORDER BY ordinal_position" | \
while IFS=$'\t' read COL_NAME COL_TYPE IS_NULLABLE COL_DEFAULT EXTRA COL_COMMENT; do
    
    # Check if column exists in target
    EXISTS=$(mysql -h $TARGET_HOST -P $TARGET_PORT -u $USER -p$PASS $DB_NAME -sN -e \
            "SELECT COUNT(*) FROM information_schema.columns 
             WHERE table_schema='$DB_NAME' AND table_name='$TABLE' AND column_name='$COL_NAME'")
    
    if [ "$EXISTS" -eq 0 ]; then
        echo "Adding column: $COL_NAME ($COL_TYPE)"
        
        # Build ALTER TABLE statement
        ALTER_SQL="ALTER TABLE \`$TABLE\` ADD \`$COL_NAME\` $COL_TYPE"
        
        # NULL/NOT NULL
        if [ "$IS_NULLABLE" = "NO" ]; then
            ALTER_SQL="$ALTER_SQL NOT NULL"
        else
            ALTER_SQL="$ALTER_SQL NULL"
        fi
        
        # DEFAULT value
        if [ -n "$COL_DEFAULT" ] && [ "$COL_DEFAULT" != "NULL" ]; then
            # Handle special default values
            if [[ "$COL_DEFAULT" =~ ^CURRENT_TIMESTAMP ]]; then
                ALTER_SQL="$ALTER_SQL DEFAULT $COL_DEFAULT"
            elif [[ "$COL_DEFAULT" =~ ^[0-9]+$ ]] || [[ "$COL_DEFAULT" =~ ^[0-9]*\.[0-9]+$ ]]; then
                ALTER_SQL="$ALTER_SQL DEFAULT $COL_DEFAULT"
            elif [[ "$COL_DEFAULT" =~ ^'(true|false|TRUE|FALSE)'$ ]]; then
                ALTER_SQL="$ALTER_SQL DEFAULT $COL_DEFAULT"
            else
                ALTER_SQL="$ALTER_SQL DEFAULT '$COL_DEFAULT'"
            fi
        fi
        
        # AUTO_INCREMENT
        if [[ "$EXTRA" == *"auto_increment"* ]]; then
            ALTER_SQL="$ALTER_SQL AUTO_INCREMENT"
        fi
        
        # COMMENT
        if [ -n "$COL_COMMENT" ]; then
            # Escape single quotes
            COL_COMMENT_ESC=$(echo "$COL_COMMENT" | sed "s/'/''/g")
            ALTER_SQL="$ALTER_SQL COMMENT '$COL_COMMENT_ESC'"
        fi
        
        # Add AFTER clause for proper ordering (except first column)
        if [ "$COL_NAME" != "id" ]; then
            # Try to get previous column
            PREV_COL=$(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 DESC LIMIT 1")
            if [ -n "$PREV_COL" ]; then
                ALTER_SQL="$ALTER_SQL AFTER \`$PREV_COL\`"
            fi
        fi
        
        # Execute
        echo "  SQL: $ALTER_SQL"
        if mysql -h $TARGET_HOST -P $TARGET_PORT -u $USER -p$PASS $DB_NAME -e "$ALTER_SQL" 2>&1 | grep -v "Using a password"; then
            echo "  ✓ Added successfully"
        else
            echo "  ✗ Failed to add"
        fi
        echo ""
    else
        echo "✓ Column exists: $COL_NAME"
    fi
done

echo "========================================"
echo "COMPLETE"
echo "========================================"