#!/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"

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

# Function to get table columns
get_columns() {
    local host=$1
    local port=$2
    local table=$3
    mysql -h "$host" -P "$port" -u "$USER" -p"$PASS" "$DB_NAME" -sN -e \
        "SELECT column_name, column_type, is_nullable, column_key, column_default, extra
         FROM information_schema.columns 
         WHERE table_schema='$DB_NAME' AND table_name='$table'
         ORDER BY ordinal_position"
}

echo "========================================="
echo "COLUMN COMPARISON TOOL"
echo "========================================="

# Check specific tables
TABLES="catalog_category_group_categories catalog_category_groups catalog_category_hierarchy catalog_products"

for TABLE in $TABLES; do
    echo ""
    echo "=== TABLE: $TABLE ==="
    
    # Get columns from source
    echo "Getting source columns..."
    SOURCE_COLS=$(get_columns "$SOURCE_HOST" "$SOURCE_PORT" "$TABLE")
    SOURCE_COUNT=$(echo "$SOURCE_COLS" | wc -l)
    
    # Get columns from target
    echo "Getting target columns..."
    TARGET_COLS=$(get_columns "$TARGET_HOST" "$TARGET_PORT" "$TABLE")
    TARGET_COUNT=$(echo "$TARGET_COLS" | wc -l)
    
    echo ""
    echo "SOURCE ($SOURCE_COUNT columns):"
    if [ "$SOURCE_COUNT" -eq 0 ]; then
        echo "  Table not found in source!"
    else
        echo "$SOURCE_COLS" | while IFS=$'\t' read -r COL_NAME COL_TYPE IS_NULLABLE COL_KEY COL_DEFAULT EXTRA; do
            echo "  - $COL_NAME ($COL_TYPE) $([ "$IS_NULLABLE" = "NO" ] && echo "NOT NULL") $([ -n "$COL_DEFAULT" ] && echo "DEFAULT: $COL_DEFAULT")"
        done
    fi
    
    echo ""
    echo "TARGET ($TARGET_COUNT columns):"
    if [ "$TARGET_COUNT" -eq 0 ]; then
        echo "  Table not found in target!"
    else
        echo "$TARGET_COLS" | while IFS=$'\t' read -r COL_NAME COL_TYPE IS_NULLABLE COL_KEY COL_DEFAULT EXTRA; do
            echo "  - $COL_NAME ($COL_TYPE) $([ "$IS_NULLABLE" = "NO" ] && echo "NOT NULL") $([ -n "$COL_DEFAULT" ] && echo "DEFAULT: $COL_DEFAULT")"
        done
    fi
    
    # Find differences
    echo ""
    echo "DIFFERENCES:"
    
    # Get just column names
    SOURCE_NAMES=$(echo "$SOURCE_COLS" | cut -f1 | sort)
    TARGET_NAMES=$(echo "$TARGET_COLS" | cut -f1 | sort)
    
    MISSING_IN_TARGET=$(comm -23 <(echo "$SOURCE_NAMES") <(echo "$TARGET_NAMES"))
    MISSING_IN_SOURCE=$(comm -13 <(echo "$SOURCE_NAMES") <(echo "$TARGET_NAMES"))
    
    if [ -n "$MISSING_IN_TARGET" ]; then
        echo "  Missing in TARGET:"
        echo "$MISSING_IN_TARGET" | sed 's/^/    - /'
    fi
    
    if [ -n "$MISSING_IN_SOURCE" ]; then
        echo "  Missing in SOURCE:"
        echo "$MISSING_IN_SOURCE" | sed 's/^/    - /'
    fi
    
    if [ -z "$MISSING_IN_TARGET" ] && [ -z "$MISSING_IN_SOURCE" ]; then
        echo "  ✓ Tables match exactly"
    fi
    
    echo "========================================="
done