#!/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 "COMPLETE DATABASE SYNCHRONIZATION"
echo "========================================="
echo "Syncs: Missing Tables + Missing Columns"
echo "Source: $SOURCE_HOST:$SOURCE_PORT"
echo "Target: $TARGET_HOST:$TARGET_PORT"
echo "Database: $DB_NAME"
echo "========================================="

# Clear PAGER
unset PAGER

# Log file
LOG_FILE="sync_complete_$(date +%Y%m%d_%H%M%S).log"
echo "Log: $LOG_FILE"
echo ""

# Function to log messages
log() {
    echo "$1"
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}

# Function to run MySQL query
mysql_query() {
    local host=$1
    local port=$2
    local query=$3
    mysql -h "$host" -P "$port" -u "$USER" -p"$PASS" "$DB_NAME" -sN -e "$query" 2>/dev/null
}

# PART 1: SYNC MISSING TABLES
log "=== PART 1: Syncing Missing Tables ==="

TABLES_SOURCE=$(mysql_query "$SOURCE_HOST" "$SOURCE_PORT" "SHOW TABLES")
TABLES_TARGET=$(mysql_query "$TARGET_HOST" "$TARGET_PORT" "SHOW TABLES")

TABLES_CREATED=0
TABLES_FAILED=0

for TABLE in $TABLES_SOURCE; do
    # Check if table exists in target
    if ! echo "$TABLES_TARGET" | grep -q "^$TABLE$"; then
        log "Table missing: $TABLE"
        
        # Get CREATE statement
        CREATE_STMT=$(mysql_query "$SOURCE_HOST" "$SOURCE_PORT" "SHOW CREATE TABLE \`$TABLE\`" | cut -f2)
        
        if [ -n "$CREATE_STMT" ]; then
            if mysql_query "$TARGET_HOST" "$TARGET_PORT" "$CREATE_STMT"; then
                log "  ✓ Table created"
                TABLES_CREATED=$((TABLES_CREATED + 1))
            else
                log "  ✗ Failed to create table"
                TABLES_FAILED=$((TABLES_FAILED + 1))
            fi
        else
            log "  ✗ Could not get CREATE statement"
            TABLES_FAILED=$((TABLES_FAILED + 1))
        fi
    fi
done

log "Tables created: $TABLES_CREATED"
log "Tables failed: $TABLES_FAILED"

# PART 2: SYNC MISSING COLUMNS
log ""
log "=== PART 2: Syncing Missing Columns ==="

# Refresh target tables list after creating new tables
TABLES_TARGET=$(mysql_query "$TARGET_HOST" "$TARGET_PORT" "SHOW TABLES")

COLUMNS_ADDED=0
COLUMNS_FAILED=0

for TABLE in $TABLES_SOURCE; do
    # Only check columns if table exists in target
    if echo "$TABLES_TARGET" | grep -q "^$TABLE$"; then
        log "Checking columns for table: $TABLE"
        
        # Get column definitions from source
        SOURCE_COLS=$(mysql_query "$SOURCE_HOST" "$SOURCE_PORT" \
                     "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")
        
        # Get column names from target
        TARGET_COL_NAMES=$(mysql_query "$TARGET_HOST" "$TARGET_PORT" \
                          "SELECT column_name FROM information_schema.columns 
                           WHERE table_schema = '$DB_NAME' AND table_name = '$TABLE'")
        
        # Process each source column
        while IFS=$'\t' read -r COL_NAME COL_TYPE IS_NULLABLE COL_DEFAULT EXTRA COL_COMMENT; do
            # Check if column exists in target
            if ! echo "$TARGET_COL_NAMES" | grep -q "^$COL_NAME$"; then
                log "  Column missing: $COL_NAME"
                
                # Build ALTER TABLE statement
                ALTER_SQL="ALTER TABLE \`$TABLE\` ADD COLUMN \`$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 [ "$COL_DEFAULT" != "NULL" ] && [ -n "$COL_DEFAULT" ]; then
                    # Check if default value needs quoting
                    if [[ "$COL_DEFAULT" =~ ^[0-9]+$ ]] || [[ "$COL_DEFAULT" =~ ^[0-9]*\.[0-9]+$ ]] || \
                       [[ "$COL_DEFAULT" =~ ^(CURRENT_TIMESTAMP|NULL|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 in comment
                    COL_COMMENT_ESC=$(echo "$COL_COMMENT" | sed "s/'/''/g")
                    ALTER_SQL="$ALTER_SQL COMMENT '$COL_COMMENT_ESC'"
                fi
                
                # Try to add column
                if mysql_query "$TARGET_HOST" "$TARGET_PORT" "$ALTER_SQL"; then
                    log "    ✓ Column added"
                    COLUMNS_ADDED=$((COLUMNS_ADDED + 1))
                else
                    log "    ✗ Failed to add column"
                    COLUMNS_FAILED=$((COLUMNS_FAILED + 1))
                fi
            fi
        done <<< "$SOURCE_COLS"
    fi
done

# SUMMARY
echo ""
echo "========================================="
echo "SYNCHRONIZATION SUMMARY"
echo "========================================="
echo "MISSING TABLES:"
echo "  Created: $TABLES_CREATED"
echo "  Failed:  $TABLES_FAILED"
echo ""
echo "MISSING COLUMNS:"
echo "  Added:   $COLUMNS_ADDED"
echo "  Failed:  $COLUMNS_FAILED"
echo ""
echo "Log file: $LOG_FILE"
echo "========================================="