#!/bin/bash

# ==============================================================================
# DB Sync Wrapper Script - Cross-Server Datenabgleich
# Läuft auf Server 2 (Ziel), vergleicht mit Server 1 (Quelle) via SSH
# ==============================================================================

# Konfiguration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PYTHON_SCRIPT="$SCRIPT_DIR/db_data_diff.py"
VENV_PATH="$SCRIPT_DIR/venv"
LOG_DIR="$SCRIPT_DIR/logs"
STATE_FILE="$SCRIPT_DIR/.db_diff_state.json"
LOCK_FILE="/tmp/db_sync_wrapper.lock"

# --- Quell-Server (Server 1) ---
SRC_SSH_HOST="mng323.whf-server.de"
SRC_SSH_USER="we8upmb"
SRC_SSH_PORT="1025"
SRC_SSH_KEY="/var/www/we8upmb/home/.ssh/id_ed25519"
SRC_DB_USER="12-production"
SRC_DB_PASS="O4NSh8E{^R_15U=T"
SRC_DB_NAME="12-production"

# --- Ziel-Server (Server 2, lokal) ---
DST_DB_SOCKET="/var/run/mysqld/mysqld.sock"
DST_DB_USER="12-production"
DST_DB_PASS="O4NSh8E{^R_15U=T"
DST_DB_NAME="12-production"

# --- SSH Socket Forwarding ---
REMOTE_MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
LOCAL_FORWARDED_SOCKET="/tmp/db_sync_remote_mysql.sock"

# --- Optionen ---
WEBHOOK_URL=""
AUTO_APPLY="false"
JOBS=4
OUTPUT_DIR="$SCRIPT_DIR/diff_output"

# Ignore-Pattern (Shopware-spezifisch)
IGNORE_TABLES="messenger_*,sendcloud_*,migration,sales_channel_api_context,refresh_token,b2b_*,pickware_*,version*,webhook*,log_*,product_keyword_dictionary,usage_data_entity_deletion,product_search_keyword,product_translation,product_visibility, cms_*, seo_*,document"

# ==============================================================================
# FUNKTIONEN
# ==============================================================================

get_timestamp() { date '+%Y-%m-%d %H:%M:%S'; }
log() { echo "[$(get_timestamp)] $1" | tee -a "$LOG_DIR/sync.log"; }
log_error() { echo "[$(get_timestamp)] ERROR: $1" | tee -a "$LOG_DIR/sync.log" >&2; }

cleanup() {
    # SSH-Socket-Tunnel beenden
    if [[ -S "$LOCAL_FORWARDED_SOCKET" ]]; then
        log "Schließe SSH-Socket-Tunnel..."
        ssh -O exit -S /tmp/db_sync_ssh_control \
            -p "$SRC_SSH_PORT" "$SRC_SSH_USER@$SRC_SSH_HOST" 2>/dev/null || true
        rm -f "$LOCAL_FORWARDED_SOCKET" 2>/dev/null || true
    fi

    # Lock-Datei entfernen
    rm -f "$LOCK_FILE" 2>/dev/null || true
}

check_lock() {
    if [[ -f "$LOCK_FILE" ]]; then
        local pid=$(cat "$LOCK_FILE" 2>/dev/null)
        if [[ -n "$pid" ]] && kill -0 "$pid" 2>/dev/null; then
            log_error "Sync bereits aktiv (PID: $pid)"
            exit 1
        else
            log "Verwaiste Lock-Datei entfernt"
            rm -f "$LOCK_FILE"
        fi
    fi
    echo $$ > "$LOCK_FILE"
    trap cleanup EXIT
}

setup_logging() {
    mkdir -p "$LOG_DIR"
    find "$LOG_DIR" -name "sync-*.log" -mtime +30 -delete 2>/dev/null || true
}

setup_python_env() {
    if [[ ! -d "$VENV_PATH" ]]; then
        log_error "Virtual Environment nicht gefunden: $VENV_PATH"
        log "Erstelle venv mit: python3 -m venv $VENV_PATH && $VENV_PATH/bin/pip install pymysql psutil tqdm"
        exit 1
    fi
    source "$VENV_PATH/bin/activate"
    log "✅ Virtual Environment aktiviert"

    if [[ ! -f "$PYTHON_SCRIPT" ]]; then
        log_error "Python-Script nicht gefunden: $PYTHON_SCRIPT"
        exit 1
    fi

    python -c "import pymysql, psutil, tqdm" 2>/dev/null || {
        log_error "Benötigte Python-Module fehlen (pymysql, psutil, tqdm)"
        log "Installiere mit: pip install pymysql psutil tqdm"
        exit 1
    }
}

# ==============================================================================
# SSH SOCKET FORWARDING
# ==============================================================================

open_ssh_socket_tunnel() {
    log "Öffne SSH-Socket-Tunnel: $LOCAL_FORWARDED_SOCKET -> $SRC_SSH_HOST:$REMOTE_MYSQL_SOCKET"

    # Alten Socket aufräumen
    rm -f "$LOCAL_FORWARDED_SOCKET" 2>/dev/null || true

    # SSH-Socket-Forwarding (Unix Socket -> Unix Socket)
    # Server 1 sieht die Verbindung dann als "localhost" (nicht 127.0.0.1)
    ssh -f -N \
        -L "$LOCAL_FORWARDED_SOCKET:$REMOTE_MYSQL_SOCKET" \
        -p "$SRC_SSH_PORT" \
        -i "$SRC_SSH_KEY" \
        -o StrictHostKeyChecking=no \
        -o ServerAliveInterval=60 \
        -o ControlMaster=yes \
        -o ControlPath=/tmp/db_sync_ssh_control \
        "$SRC_SSH_USER@$SRC_SSH_HOST" || {
            log_error "SSH-Socket-Tunnel konnte nicht geöffnet werden"
            exit 1
        }

    # Warten bis Socket verfügbar
    local retries=0
    while [[ ! -S "$LOCAL_FORWARDED_SOCKET" ]] && (( retries < 10 )); do
        sleep 1
        retries=$((retries + 1))
    done

    if [[ ! -S "$LOCAL_FORWARDED_SOCKET" ]]; then
        log_error "SSH-Socket-Tunnel nicht verfügbar nach 10 Sekunden"
        exit 1
    fi

    log "✅ SSH-Socket-Tunnel aktiv: $LOCAL_FORWARDED_SOCKET"
}

test_connections() {
    log "Teste Datenbankverbindungen..."

    python -c "
import pymysql
try:
    conn = pymysql.connect(unix_socket='$LOCAL_FORWARDED_SOCKET', user='$SRC_DB_USER', password='$SRC_DB_PASS', database='$SRC_DB_NAME')
    conn.close()
    print('✅ Quell-DB ($SRC_DB_NAME auf $SRC_SSH_HOST) erreichbar')
except Exception as e:
    print(f'❌ Quell-DB Verbindung fehlgeschlagen: {e}')
    exit(1)

try:
    conn = pymysql.connect(unix_socket='$DST_DB_SOCKET', user='$DST_DB_USER', password='$DST_DB_PASS', database='$DST_DB_NAME')
    conn.close()
    print('✅ Ziel-DB ($DST_DB_NAME lokal) erreichbar')
except Exception as e:
    print(f'❌ Ziel-DB Verbindung fehlgeschlagen: {e}')
    exit(1)
" || {
        log_error "Datenbankverbindung fehlgeschlagen"
        exit 1
    }
}

# ==============================================================================
# SYNC
# ==============================================================================

calculate_since_time() {
    if [[ -f "$STATE_FILE" ]]; then
        local last_sync=$(python -c "
import json
from datetime import datetime, timedelta
try:
    with open('$STATE_FILE', 'r') as f:
        data = json.load(f)
    latest = None
    for table, info in data.items():
        if 'last_sync' in info:
            sync_time = datetime.fromisoformat(info['last_sync'].replace('Z', '+00:00'))
            if latest is None or sync_time > latest:
                latest = sync_time
    if latest:
        safe_time = latest - timedelta(hours=1)
        print(safe_time.strftime('%Y-%m-%d %H:%M:%S'))
    else:
        yesterday = datetime.now() - timedelta(days=1)
        print(yesterday.strftime('%Y-%m-%d %H:%M:%S'))
except Exception:
    yesterday = datetime.now() - timedelta(days=1)
    print(yesterday.strftime('%Y-%m-%d %H:%M:%S'))
" 2>/dev/null)
        if [[ -n "$last_sync" ]]; then
            echo "$last_sync"
        else
            date -d "25 hours ago" '+%Y-%m-%d %H:%M:%S'
        fi
    else
        date -d "1 day ago" '+%Y-%m-%d %H:%M:%S'
    fi
}

run_sync() {
    local since_time="$1"
    local mode="$2"
    local daily_log="$LOG_DIR/sync-$(date '+%Y-%m-%d').log"

    log "Starte $mode-Sync seit: $since_time"

    local cmd=(
        python "$PYTHON_SCRIPT"
        --host="localhost"
        --user="$SRC_DB_USER"
        --password="$SRC_DB_PASS"
        --from="$SRC_DB_NAME"
        --to="$DST_DB_NAME"
        --src-socket="$LOCAL_FORWARDED_SOCKET"
        --dst-socket="$DST_DB_SOCKET"
        --dst-user="$DST_DB_USER"
        --dst-password="$DST_DB_PASS"
        --ignore="$IGNORE_TABLES"
        --output-dir="$OUTPUT_DIR"
        --merge-output
        --insert-on-duplicate
        --skip-updates
        --jobs="$JOBS"
        --state-file="$STATE_FILE"
        --batch-size=10000
    )

    if [[ "$mode" == "differential" ]] || [[ "$mode" == "full-differential" ]]; then
        cmd+=(--differential)
    fi

    cmd+=(--since="$since_time")

    log "Kommando: ${cmd[*]}"

    if "${cmd[@]}" 2>&1 | tee -a "$daily_log"; then
        log "✅ $mode-Sync erfolgreich"

        if [[ -f "$SCRIPT_DIR/merged_diff.sql" ]]; then
            local changes=$(grep -c "INSERT\|UPDATE\|DELETE" "$SCRIPT_DIR/merged_diff.sql" 2>/dev/null || echo "0")
            log "📊 $changes SQL-Statements generiert"

            if [[ "$AUTO_APPLY" == "true" && "$changes" -gt "0" && "$changes" -lt "10000" ]]; then
                log "🔧 Führe Änderungen automatisch aus..."
                if mariadb --socket="$DST_DB_SOCKET" -u"$DST_DB_USER" -p"$DST_DB_PASS" "$DST_DB_NAME" < "$SCRIPT_DIR/merged_diff.sql" 2>&1 | tee -a "$daily_log"; then
                    log "✅ Änderungen angewendet"
                else
                    log_error "❌ Fehler beim Anwenden der Änderungen"
                fi
            fi
        else
            log "ℹ️  Keine Änderungen gefunden"
        fi
        return 0
    else
        log_error "❌ $mode-Sync fehlgeschlagen"
        return 1
    fi
}

cleanup_old_outputs() {
    find "$SCRIPT_DIR" -name "merged_diff.sql" -mtime +7 -delete 2>/dev/null || true
    find "$SCRIPT_DIR" -name "merged_rollback.sql" -mtime +7 -delete 2>/dev/null || true
    find "$SCRIPT_DIR" -name "merged_newer_in_target.sql" -mtime +7 -delete 2>/dev/null || true
    find "$SCRIPT_DIR" -name "$OUTPUT_DIR-*" -type d -mtime +7 -exec rm -rf {} + 2>/dev/null || true
}

send_stats() {
    local status="$1"
    if [[ -n "$WEBHOOK_URL" ]]; then
        curl -X POST -H "Content-Type: application/json" \
             -d "{\"text\":\"DB-Sync Cross-Server: $status\"}" \
             "$WEBHOOK_URL" 2>/dev/null || true
    fi
}

# ==============================================================================
# HAUPTFUNKTIONEN
# ==============================================================================

sync_once() {
    local force_full="$1"

    setup_logging
    check_lock
    setup_python_env
    open_ssh_socket_tunnel
    test_connections
    cleanup_old_outputs

    log "=== Cross-Server DB Sync gestartet ==="
    log "Quelle: $SRC_DB_NAME @ $SRC_SSH_HOST"
    log "Ziel:   $DST_DB_NAME @ localhost"

    local since_time
    local mode="differential"

    if [[ "$force_full" == "true" ]] || [[ ! -f "$STATE_FILE" ]]; then
        mode="full"
        since_time="2024-01-01 00:00:00"
        log "Vollständiger Sync"
        if [[ ! -f "$STATE_FILE" ]]; then
            mode="full-differential"
        fi
    else
        since_time=$(calculate_since_time)
        log "Differentieller Sync"
    fi

    if run_sync "$since_time" "$mode"; then
        send_stats "SUCCESS"
        log "=== DB Sync erfolgreich ==="
        return 0
    else
        send_stats "FAILED"
        log_error "=== DB Sync fehlgeschlagen ==="
        return 1
    fi
}

show_help() {
    cat << EOF
Cross-Server DB Sync — Vergleicht Quell-DB (Server 1) mit Ziel-DB (Server 2)

Verwendung:
    $0 [OPTION]

Optionen:
    --once          Einmaliger Sync (Standard)
    --full          Vollständiger Sync (ignoriert State)
    --test          Nur Verbindungstest
    --status        Zeige Status-Informationen
    --reset-state   Lösche State-File
    --help          Diese Hilfe

Architektur:
    Server 2 ──SSH-Socket-Forwarding──> Server 1 (MariaDB als localhost)
    Server 2 ──lokal──> Server 2 (MariaDB)
    
    Das Script öffnet einen SSH-Unix-Socket-Tunnel zu Server 1.
    Server 1 sieht die Verbindung als "localhost" (nicht 127.0.0.1).

Logs: $LOG_DIR/
State: $STATE_FILE
EOF
}

show_status() {
    echo "=== Cross-Server DB Sync Status ==="
    echo "Quelle:     $SRC_DB_NAME @ $SRC_SSH_HOST"
    echo "Ziel:       $DST_DB_NAME @ localhost"
    echo "Script:     $PYTHON_SCRIPT"
    echo "VEnv:       $VENV_PATH"
    echo "State File: $STATE_FILE"
    echo ""

    if [[ -f "$STATE_FILE" ]]; then
        echo "Letzter Sync:"
        setup_python_env 2>/dev/null
        python -c "
import json
from datetime import datetime
try:
    with open('$STATE_FILE', 'r') as f:
        data = json.load(f)
    for table, info in sorted(data.items()):
        if 'last_sync' in info:
            print(f'  {table}: {info[\"last_sync\"]} (Max ID: {info.get(\"last_max_id\", \"N/A\")})')
except Exception as e:
    print(f'Fehler: {e}')
"
    else
        echo "Noch kein Sync durchgeführt"
    fi

    echo ""
    if [[ -f "$LOCK_FILE" ]]; then
        echo "Status: LÄUFT (PID: $(cat "$LOCK_FILE"))"
    else
        echo "Status: GESTOPPT"
    fi
}

# ==============================================================================
# PARAMETER
# ==============================================================================

case "${1:-}" in
    --once)
        sync_once
        exit $?
        ;;
    --full)
        sync_once "true"
        exit $?
        ;;
    --test)
        setup_logging
        setup_python_env
        open_ssh_socket_tunnel
        test_connections
        cleanup
        log "✅ Alle Tests erfolgreich"
        ;;
    --status)
        show_status
        ;;
    --reset-state)
        if [[ -f "$STATE_FILE" ]]; then
            rm -f "$STATE_FILE"
            echo "State-File gelöscht: $STATE_FILE"
        else
            echo "Kein State-File vorhanden"
        fi
        ;;
    --help)
        show_help
        ;;
    "")
        sync_once
        exit $?
        ;;
    *)
        echo "Unbekannte Option: $1"
        echo "Verwende --help für Hilfe"
        exit 1
        ;;
esac