#!/usr/bin/env python3
"""
Performance-optimierter MySQL Datenbankvergleich mit Differential Backup Support
Erstellt SQL-Diff Dateien zwischen zwei Datenbanken

Hauptfeatures:
- Performance-optimiert mit Batch-Processing
- Differential Backup Mode
- Intelligente AUTO_INCREMENT Behandlung
- Shopware-spezifische Funktionen
- Parallel Processing
- Umfangreiche Fehlerbehandlung
"""

import argparse
import fnmatch
import hashlib
import json
import os
import re
import shutil
import threading
import time
from concurrent.futures import ThreadPoolExecutor
from datetime import datetime, timezone, timedelta
from multiprocessing import Pool
from typing import Dict, List, Optional, Set, Tuple, Any

import psutil
import pymysql
from tqdm import tqdm


# ==============================================================================
# CONFIGURATION & CONSTANTS
# ==============================================================================

LIVE_VERSION_ID = '0fa91ce3e96a4bc2be4bd9ce752c3425'
DEFAULT_BATCH_SIZE = 10000
DEFAULT_JOBS = 4
DEFAULT_OUTPUT_DIR = 'diff_output'
DEFAULT_STATE_FILE = '.db_diff_state.json'

# Shopware-spezifische Konfiguration
SHOPWARE_NUMBER_TABLES = ['order', 'customer', 'product', 'document']
SHOPWARE_NUMBER_COLUMNS = {
    'order': 'order_number',
    'customer': 'customer_number', 
    'product': 'product_number',
    'document': 'document_number'
}

# Performance-kritische Tabellen
HIGH_PRIORITY_TABLES = ['order', 'customer', 'product', 'category']
LOW_PRIORITY_PREFIXES = ['log_', 'cache_', 'session_', 'tmp_']


# ==============================================================================
# UTILITY CLASSES
# ==============================================================================

class PerformanceMonitor:
    """Überwacht Performance-Metriken während des Vergleichs"""
    
    def __init__(self):
        self.start_time = time.time()
        self.processed_tables = 0
        self.processed_rows = 0
        self.memory_peaks = []
        self.running = True
        self._monitor_thread = None

    def start_monitoring(self):
        """Startet Background-Monitoring"""
        def monitor():
            while self.running:
                try:
                    memory_mb = psutil.Process().memory_info().rss / 1024 / 1024
                    self.memory_peaks.append(memory_mb)
                    time.sleep(5)
                except Exception:
                    pass  # Ignore monitoring errors

        self._monitor_thread = threading.Thread(target=monitor, daemon=True)
        self._monitor_thread.start()

    def stop_monitoring(self):
        """Stoppt das Monitoring"""
        self.running = False
        if self._monitor_thread:
            self._monitor_thread.join(timeout=1)

    def get_stats(self) -> Dict[str, float]:
        """Gibt Performance-Statistiken zurück"""
        duration = time.time() - self.start_time
        avg_memory = sum(self.memory_peaks) / len(self.memory_peaks) if self.memory_peaks else 0
        max_memory = max(self.memory_peaks) if self.memory_peaks else 0

        return {
            'duration': duration,
            'tables_processed': self.processed_tables,
            'rows_processed': self.processed_rows,
            'avg_memory_mb': avg_memory,
            'max_memory_mb': max_memory,
            'rows_per_second': self.processed_rows / duration if duration > 0 else 0
        }


class DifferentialBackupState:
    """Verwaltet den Status für Differential Backups"""
    
    def __init__(self, state_file: str):
        self.state_file = state_file
        self.state = self._load_state()

    def _load_state(self) -> Dict[str, Any]:
        """Lädt den letzten Backup-Status"""
        if os.path.exists(self.state_file):
            try:
                with open(self.state_file, 'r') as f:
                    return json.load(f)
            except Exception as e:
                print(f"⚠️  Konnte Backup-Status nicht laden: {e}")
        return {}

    def save_state(self, table_states: Dict[str, Dict[str, Any]]):
        """Speichert den aktuellen Backup-Status"""
        try:
            self.state.update(table_states)
            with open(self.state_file, 'w') as f:
                json.dump(self.state, f, indent=2, default=str)
            print(f"💾 Backup-Status gespeichert: {self.state_file}")
        except Exception as e:
            print(f"❌ Fehler beim Speichern des Backup-Status: {e}")

    def get_last_sync(self, table: str) -> Optional[datetime]:
        """Gibt den letzten Sync-Zeitpunkt für eine Tabelle zurück"""
        return self.state.get(table, {}).get('last_sync')

    def get_last_max_id(self, table: str) -> Optional[int]:
        """Gibt die letzte maximale ID für eine Tabelle zurück"""
        return self.state.get(table, {}).get('last_max_id', 0)


# ==============================================================================
# DATABASE CONNECTION & UTILITIES
# ==============================================================================

def create_connection(host: str, user: str, password: str, port: int = 3306, 
                     database: Optional[str] = None, socket_path: Optional[str] = None,
                     **kwargs) -> pymysql.Connection:
    """Erstellt eine Datenbankverbindung mit optimierten Einstellungen"""
    default_kwargs = {
        'charset': 'utf8mb4',
        'autocommit': False,
        'read_timeout': 600,
        'write_timeout': 600,
        'init_command': 'SET SESSION innodb_lock_wait_timeout = 600',
    }
    default_kwargs.update(kwargs)

    # Unix Socket für localhost oder expliziten Socket-Pfad (z.B. SSH-Forwarding)
    if host == 'localhost' or socket_path:
        conn_params = {
            'unix_socket': socket_path or '/var/run/mysqld/mysqld.sock',
            'user': user,
            'password': password,
            **default_kwargs
        }
    else:
        conn_params = {
            'host': host,
            'user': user,
            'password': password,
            'port': port,
            **default_kwargs
        }
    
    if database:
        conn_params['database'] = database

    return pymysql.connect(**conn_params)


def validate_connection(host: str, user: str, password: str, port: int, database: str,
                       socket_path: Optional[str] = None) -> bool:
    """Validiert die Datenbankverbindung"""
    try:
        conn = create_connection(host, user, password, port, database, socket_path=socket_path, connect_timeout=10)
        cursor = conn.cursor()
        cursor.execute("SELECT 1")
        cursor.close()
        conn.close()
        return True
    except Exception as e:
        print(f"❌ Verbindung zu {database} fehlgeschlagen: {e}")
        return False


def check_database_timezone(cursor, db_name: str) -> Optional[bool]:
    """Prüft die Zeitzoneneinstellungen der Datenbank"""
    try:
        cursor.execute("SELECT @@system_time_zone, @@time_zone, NOW(), UTC_TIMESTAMP()")
        result = cursor.fetchone()

        if result:
            system_tz, session_tz, local_now, utc_now = result
            print(f"🌍 Datenbank-Zeitzone Info für {db_name}:")
            print(f"   System-Zeitzone: {system_tz}")
            print(f"   Session-Zeitzone: {session_tz}")
            print(f"   Lokale Zeit: {local_now}")
            print(f"   UTC Zeit: {utc_now}")

            if local_now != utc_now:
                time_diff = local_now - utc_now if local_now > utc_now else utc_now - local_now
                hours_diff = time_diff.total_seconds() / 3600
                print(f"   Zeitdifferenz: {hours_diff:.1f} Stunden")
                return True
            else:
                print(f"   💡 Lokale Zeit = UTC Zeit → Datenbank läuft in UTC")
                return False

    except Exception as e:
        print(f"⚠️  Konnte Zeitzone nicht ermitteln: {e}")
        return None


# ==============================================================================
# TABLE METADATA & SCHEMA ANALYSIS
# ==============================================================================

def get_primary_key(cursor, db_name: str, table_name: str) -> List[str]:
    """Ermittelt die Primärschlüssel-Spalten einer Tabelle"""
    cursor.execute("""
        SELECT COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME = %s
          AND CONSTRAINT_NAME = 'PRIMARY'
        ORDER BY ORDINAL_POSITION
    """, (db_name, table_name))
    return [row[0] for row in cursor.fetchall()]


def get_table_priority(cursor, db_name: str, table_name: str) -> int:
    """Bestimmt die Priorität einer Tabelle basierend auf Größe und Typ"""
    cursor.execute("""
        SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, ENGINE
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
    """, (db_name, table_name))

    result = cursor.fetchone()
    if not result:
        return 5

    rows, data_length, index_length, engine = result
    total_size = (data_length or 0) + (index_length or 0)

    # Prioritätsregeln
    if any(table_name.startswith(prefix) for prefix in LOW_PRIORITY_PREFIXES):
        return 1  # Niedrigste Priorität
    elif rows and rows > 1000000:  # > 1M Zeilen
        return 2
    elif total_size > 100 * 1024 * 1024:  # > 100MB
        return 3
    elif table_name in HIGH_PRIORITY_TABLES:
        return 9  # Hohe Priorität
    else:
        return 5  # Normale Priorität


def get_column_metadata(cursor, db_name: str, table_name: str) -> Dict[str, Set[str]]:
    """Sammelt alle Metadaten zu Tabellenspalten"""
    metadata = {
        'generated': set(),
        'auto_increment': {},
        'json': set(),
        'unique': set(),
        'binary': set()
    }
    
    # Generierte Spalten
    cursor.execute("""
        SELECT COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME = %s
          AND (EXTRA LIKE %s OR GENERATION_EXPRESSION IS NOT NULL)
    """, (db_name, table_name, '%GENERATED%'))
    metadata['generated'] = {row[0] for row in cursor.fetchall()}

    # AUTO_INCREMENT Spalten
    cursor.execute("""
        SELECT COLUMN_NAME, DATA_TYPE
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME = %s
          AND EXTRA LIKE '%%auto_increment%%'
    """, (db_name, table_name))
    metadata['auto_increment'] = {row[0]: row[1] for row in cursor.fetchall()}

    # JSON CHECK Constraints
    cursor.execute("""
        SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE
        FROM information_schema.CHECK_CONSTRAINTS cc
        JOIN information_schema.TABLE_CONSTRAINTS tc
             ON cc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
             AND cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        WHERE tc.TABLE_SCHEMA = %s
          AND tc.TABLE_NAME = %s
          AND cc.CHECK_CLAUSE LIKE %s
    """, (db_name, table_name, '%json_valid%'))
    
    for constraint_name, check_clause in cursor.fetchall():
        match = re.search(r'json_valid\s*\(\s*`([^`]+)`\s*\)', check_clause)
        if match:
            metadata['json'].add(match.group(1))

    # UNIQUE KEY Spalten
    cursor.execute("""
        SELECT DISTINCT kcu.COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE kcu
        JOIN information_schema.TABLE_CONSTRAINTS tc
             ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
             AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
             AND kcu.TABLE_NAME = tc.TABLE_NAME
        WHERE kcu.TABLE_SCHEMA = %s
          AND kcu.TABLE_NAME = %s
          AND tc.CONSTRAINT_TYPE = 'UNIQUE'
    """, (db_name, table_name))
    metadata['unique'] = {row[0] for row in cursor.fetchall()}

    # Binäre Spalten werden separat ermittelt
    cursor.execute(f"SHOW COLUMNS FROM `{db_name}`.`{table_name}`")
    for row in cursor.fetchall():
        column_name, column_type = row[0], row[1]
        if column_type.lower().startswith(('binary', 'varbinary')):
            metadata['binary'].add(column_name)

    return metadata


# ==============================================================================
# PERFORMANCE OPTIMIZATION
# ==============================================================================

def optimize_batch_size(table_size_mb: float, available_memory_mb: float) -> int:
    """Bestimmt optimale Batch-Größe basierend auf Tabellengröße und verfügbarem Speicher"""
    if table_size_mb < 10:
        return 50000
    elif table_size_mb < 100:
        return 25000
    elif table_size_mb < 1000:
        return 10000
    else:
        # Dynamisch basierend auf verfügbarem Speicher
        if available_memory_mb > 8000:
            return 5000
        elif available_memory_mb > 4000:
            return 2500
        else:
            return 1000


def create_temporary_indexes(cursor, db_name: str, table_name: str, columns: List[str]) -> List[str]:
    """Erstellt temporäre Indizes für bessere Performance"""
    temp_indexes = []
    timestamp_columns = ['created_at', 'updated_at']
    
    for col in timestamp_columns:
        if col not in columns:
            continue
            
        # Prüfe ob Index bereits existiert
        cursor.execute("""
            SELECT COUNT(*)
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = %s
              AND COLUMN_NAME = %s
        """, (db_name, table_name, col))

        if cursor.fetchone()[0] == 0:
            try:
                index_name = f"tmp_idx_{col}_{int(time.time())}"
                cursor.execute(f"ALTER TABLE `{db_name}`.`{table_name}` ADD INDEX `{index_name}` (`{col}`)")
                temp_indexes.append(index_name)
                print(f"    🔍 Temporärer Index für {col} erstellt")
            except Exception as e:
                print(f"    ⚠️  Konnte temporären Index für {col} nicht erstellen: {e}")

    return temp_indexes


def drop_temporary_indexes(cursor, db_name: str, table_name: str, temp_indexes: List[str]):
    """Entfernt temporäre Indizes"""
    for index_name in temp_indexes:
        try:
            cursor.execute(f"ALTER TABLE `{db_name}`.`{table_name}` DROP INDEX `{index_name}`")
            print(f"    🗑️  Temporärer Index {index_name} entfernt")
        except Exception as e:
            print(f"    ⚠️  Konnte temporären Index {index_name} nicht entfernen: {e}")


# ==============================================================================
# DATA FETCHING & PROCESSING
# ==============================================================================

def fetch_table_data_streaming(cursor, db_name: str, table_name: str, columns: List[str],
                              since_ts: Optional[datetime] = None, batch_size: int = DEFAULT_BATCH_SIZE,
                              only_live_version: bool = True, differential: bool = False,
                              last_max_id: Optional[int] = None) -> List[Tuple]:
    """Streamt Tabellendaten in Batches mit optimierter Performance"""
    
    col_list = ", ".join(f"`{c}`" for c in columns)
    filter_clauses = []
    params = []

    # Live Version Filter für Shopware
    if only_live_version and 'version_id' in columns:
        filter_clauses.append("`version_id` = %s")
        params.append(LIVE_VERSION_ID)

    # Differential Backup Filter
    if differential and last_max_id is not None:
        cursor.execute("""
            SELECT COLUMN_NAME
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = %s
              AND EXTRA LIKE '%%auto_increment%%'
        """, (db_name, table_name))
        ai_col = cursor.fetchone()

        if ai_col:
            filter_clauses.append(f"`{ai_col[0]}` > %s")
            params.append(last_max_id)
            print(f"    📈 Differential: Lade nur Datensätze mit {ai_col[0]} > {last_max_id}")

    # Zeitbasierte Filter
    if since_ts:
        time_filters = []
        if 'created_at' in columns:
            time_filters.append("`created_at` >= %s")
            params.append(since_ts)
        if 'updated_at' in columns:
            time_filters.append("(`updated_at` >= %s OR `updated_at` IS NULL)")
            params.append(since_ts)
        
        if time_filters:
            filter_clauses.extend(time_filters)

    # Query zusammenbauen
    filter_clause = ""
    if filter_clauses:
        filter_clause = "WHERE " + " AND ".join(filter_clauses)

    # Optimierte Sortierung
    pk_columns = get_primary_key(cursor, db_name, table_name)
    order_clause = ""
    if pk_columns:
        order_clause = f"ORDER BY {', '.join(f'`{pk}`' for pk in pk_columns)}"

    # Hole Gesamtanzahl
    count_query = f"SELECT COUNT(*) FROM `{db_name}`.`{table_name}` {filter_clause}"
    cursor.execute(count_query, params)
    total_rows = cursor.fetchone()[0]

    if total_rows == 0:
        return []

    print(f"    📊 Lade {total_rows:,} Zeilen in Batches von {batch_size:,}")

    # Batch-Processing für große Tabellen
    if total_rows > batch_size:
        all_data = []
        for offset in tqdm(range(0, total_rows, batch_size),
                          desc=f"Loading {table_name}",
                          unit="batch",
                          leave=False):
            try:
                query = f"""
                    SELECT {col_list} 
                    FROM `{db_name}`.`{table_name}` 
                    {filter_clause} 
                    {order_clause} 
                    LIMIT %s OFFSET %s
                """
                cursor.execute(query, params + [batch_size, offset])
                batch_data = cursor.fetchall()
                all_data.extend(batch_data)

                # Kurze Pause zur DB-Entlastung
                if len(batch_data) == batch_size:
                    time.sleep(0.01)

            except Exception as e:
                print(f"    ❌ Fehler bei Batch {offset}-{offset+batch_size}: {e}")
                break

        return all_data
    else:
        # Kleine Tabelle - eine Abfrage
        query = f"SELECT {col_list} FROM `{db_name}`.`{table_name}` {filter_clause} {order_clause}"
        cursor.execute(query, params)
        return cursor.fetchall()


def row_to_dict(columns: List[str], row: Tuple) -> Dict[str, Any]:
    """Konvertiert Tupel zu Dictionary"""
    return dict(zip(columns, row))


# ==============================================================================
# SQL GENERATION
# ==============================================================================

def quote_value(val: Any, is_binary: bool = False, is_json: bool = False) -> str:
    """Quotiert Werte für SQL mit spezieller Behandlung für verschiedene Typen"""
    if val is None:
        return 'NULL'
    
    if isinstance(val, (int, float)):
        return str(val)
    
    if isinstance(val, bytes):
        return "0x" + val.hex().upper()
    
    if is_binary and isinstance(val, str):
        try:
            return "0x" + val.encode('latin-1').hex().upper()
        except:
            return "'" + str(val).replace("'", "''") + "'"

    # JSON-Spalten
    if is_json and isinstance(val, str):
        if val.strip() == '':
            return 'NULL'
        try:
            json.loads(val)  # Validierung
            escaped = val.replace('\\', '\\\\').replace("'", "\\'")
            return "'" + escaped + "'"
        except json.JSONDecodeError:
            print(f"⚠️  Ungültiges JSON erkannt, verwende NULL")
            return 'NULL'

    # Prüfe auf serialisierte PHP-Daten
    if isinstance(val, str):
        if '\0' in val or (val.startswith(('O:', 'a:', 's:', 'i:', 'b:', 'N;')) and ':' in val):
            return "0x" + val.encode('utf-8').hex().upper()

    return "'" + str(val).replace("'", "''") + "'"


def generate_insert(table: str, columns: List[str], row_dict: Dict[str, Any],
                   metadata: Dict[str, Set[str]], pk_columns: List[str],
                   on_duplicate: bool = False) -> str:
    """Generiert INSERT Statement mit intelligenter Spaltenbehandlung"""
    
    # Filtere problematische Spalten
    insert_columns = []
    for col in columns:
        # Überspringe generierte Spalten
        if col in metadata['generated']:
            continue
        
        # Überspringe AUTO_INCREMENT bei NULL/0 Werten
        if col in metadata['auto_increment']:
            val = row_dict.get(col)
            if val is None or val == 0:
                continue
        
        insert_columns.append(col)

    if not insert_columns:
        return ""

    cols = ', '.join(f"`{c}`" for c in insert_columns)
    vals = ', '.join(
        quote_value(row_dict[c], 
                   c in metadata['binary'], 
                   c in metadata['json'])
        for c in insert_columns
    )

    sql = f"INSERT INTO `{table}` ({cols}) VALUES ({vals})"
    
    if on_duplicate:
        # ON DUPLICATE KEY UPDATE für sichere Inserts
        update_columns = [c for c in insert_columns 
                         if c not in pk_columns and c not in metadata['unique']]
        if update_columns:
            update_parts = [f"`{col}` = VALUES(`{col}`)" for col in update_columns]
            sql += " ON DUPLICATE KEY UPDATE " + ", ".join(update_parts)
    
    return sql + ";"


def generate_update(table: str, columns: List[str], row_dict: Dict[str, Any],
                   pk_columns: List[str], metadata: Dict[str, Set[str]]) -> str:
    """Generiert UPDATE Statement"""
    update_columns = [c for c in columns 
                     if c not in pk_columns and c not in metadata['generated']]
    
    if not update_columns:
        return ""
    
    set_parts = [
        f"`{c}` = {quote_value(row_dict[c], c in metadata['binary'], c in metadata['json'])}"
        for c in update_columns
    ]
    
    where_parts = [
        f"`{c}` = {quote_value(row_dict[c], c in metadata['binary'], c in metadata['json'])}"
        for c in pk_columns
    ]
    
    return f"UPDATE `{table}` SET {', '.join(set_parts)} WHERE {' AND '.join(where_parts)};"


def generate_delete(table: str, pk_values: Tuple, pk_columns: List[str],
                   metadata: Dict[str, Set[str]]) -> str:
    """Generiert DELETE Statement"""
    where_parts = [
        f"`{c}` = {quote_value(v, c in metadata['binary'], c in metadata['json'])}"
        for c, v in zip(pk_columns, pk_values)
    ]
    return f"DELETE FROM `{table}` WHERE {' AND '.join(where_parts)};"


# ==============================================================================
# TIMESTAMP & TIMEZONE HANDLING
# ==============================================================================

def parse_since_timestamp(since_str: str, timezone_aware: bool = True) -> Optional[datetime]:
    """Parst und konvertiert Zeitstempel korrekt für UTC-Datenbanken"""
    if not since_str:
        return None

    try:
        dt = datetime.strptime(since_str, "%Y-%m-%d %H:%M:%S")

        if timezone_aware:
            import time
            local_tz_offset = time.timezone if time.daylight == 0 else time.altzone
            local_tz_offset_hours = -local_tz_offset // 3600

            print(f"🌍 Zeitzone-Info: Server läuft in UTC{local_tz_offset_hours:+d}")
            print(f"🕐 Eingabe '{since_str}' wird als lokale Zeit interpretiert")

            local_tz = timezone(timedelta(hours=local_tz_offset_hours))
            dt_local = dt.replace(tzinfo=local_tz)
            dt_utc = dt_local.astimezone(timezone.utc)

            print(f"🔄 Konvertiert zu UTC: {dt_utc.strftime('%Y-%m-%d %H:%M:%S')} UTC")
            return dt_utc.replace(tzinfo=None)
        else:
            print(f"🕐 Verwende '{since_str}' als direkte Zeit")
            return dt

    except ValueError as e:
        print(f"❌ Ungültiges Datumsformat für --since: {e}")
        print("💡 Erwartet: 'YYYY-MM-DD HH:MM:SS'")
        return None


# ==============================================================================
# AUTO_INCREMENT HANDLING
# ==============================================================================

def handle_auto_increment_conflicts(cursor, db_name: str, table_name: str,
                                  insert_rows: List[Dict[str, Any]],
                                  ai_columns: Dict[str, str]) -> Tuple[List[Dict[str, Any]], List[str]]:
    """Behandelt AUTO_INCREMENT Konflikte intelligent"""
    if not ai_columns or not insert_rows:
        return insert_rows, []

    ai_col = list(ai_columns.keys())[0]
    
    # Hole aktuelle MAX-ID
    cursor.execute(f"SELECT COALESCE(MAX(`{ai_col}`), 0) FROM `{db_name}`.`{table_name}`")
    current_max = cursor.fetchone()[0]
    
    # Hole AUTO_INCREMENT Wert mit expliziter Datenbankauswahl
    cursor.execute(f"SHOW TABLE STATUS FROM `{db_name}` LIKE '{table_name}'")
    table_status = cursor.fetchone()
    current_ai = table_status[10] if table_status else current_max + 1
    
    # Analysiere Konflikte
    safe_rows = []
    conflict_count = 0
    
    for row in insert_rows:
        if ai_col in row and row[ai_col] is not None:
            ai_value = row[ai_col]
            
            # Prüfe auf Konflikt
            if ai_value <= current_max:
                # Entferne AUTO_INCREMENT Spalte - lasse DB neue ID generieren
                new_row = row.copy()
                del new_row[ai_col]
                safe_rows.append(new_row)
                conflict_count += 1
            else:
                safe_rows.append(row)
        else:
            safe_rows.append(row)
    
    statements = []
    if conflict_count > 0:
        # Setze sicheren AUTO_INCREMENT Wert
        safe_start = max(current_max, current_ai) + 1000
        statements.append(f"-- Löse {conflict_count} AUTO_INCREMENT Konflikte für {table_name}")
        statements.append(f"ALTER TABLE `{db_name}`.`{table_name}` AUTO_INCREMENT = {safe_start};")
        print(f"    🔧 {table_name}: {conflict_count} AUTO_INCREMENT Konflikte gelöst")
    
    return safe_rows, statements


# ==============================================================================
# CORE COMPARISON LOGIC
# ==============================================================================

def is_table_ignored(table_name: str, ignore_patterns: List[str]) -> bool:
    """Prüft ob Tabelle ignoriert werden soll"""
    return any(fnmatch.fnmatch(table_name, pattern) for pattern in ignore_patterns)


def compare_table_optimized(table: str, args_dict: Dict[str, Any],
                          perf_monitor: Optional[PerformanceMonitor] = None,
                          backup_state: Optional[DifferentialBackupState] = None) -> Dict[str, Any]:
    """Optimierte Tabellenvergleichsfunktion"""
    
    args = argparse.Namespace(**args_dict)
    
    def log(msg: str):
        print(f"[{table}] {msg}")

    if is_table_ignored(table, args.ignore_patterns):
        log("🚫 Tabelle wird ignoriert")
        return {"table": table, "status": "ignored"}

    stats = {
        "table": table,
        "inserts": 0,
        "updates": 0, 
        "deletes": 0,
        "newer_in_target": 0,
        "status": "success",
        "rows_processed": 0,
        "max_id": None
    }

    try:
        # Datenbankverbindungen erstellen (Source + Destination)
        src_cfg = args.src_config
        dst_cfg = args.dst_config
        conn_src = create_connection(src_cfg['host'], src_cfg['user'], src_cfg['password'], src_cfg['port'], socket_path=src_cfg.get('socket'))
        conn_dst = create_connection(dst_cfg['host'], dst_cfg['user'], dst_cfg['password'], dst_cfg['port'], socket_path=dst_cfg.get('socket'))
        cursor = conn_src.cursor()     # Default cursor = Quelle
        cursor_dst = conn_dst.cursor()  # Separater Cursor für Ziel

        log("🔍 Beginne optimierten Vergleich")

        # Prüfe Primärschlüssel
        pk_columns = get_primary_key(cursor, args.db_from, table)
        if not pk_columns:
            log("⚠️  Kein Primärschlüssel - übersprungen")
            stats["status"] = "no_primary_key"
            return stats

        # Prüfe ob Zieltabelle existiert
        cursor_dst.execute(f"SHOW TABLES FROM `{args.db_to}` LIKE '{table}'")
        if not cursor_dst.fetchone():
            log("⚠️  Tabelle existiert nicht in Zieldatenbank")
            stats["status"] = "table_not_exists"
            return stats

        # Hole Tabellenmetadaten
        cursor.execute(f"SHOW COLUMNS FROM `{args.db_from}`.`{table}`")
        column_info = cursor.fetchall()
        columns = [row[0] for row in column_info]
        
        metadata = get_column_metadata(cursor, args.db_from, table)
        
        # Performance-Optimierungen
        available_memory = psutil.virtual_memory().available / 1024 / 1024
        cursor.execute("""
            SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
        """, (args.db_from, table))
        
        table_stats = cursor.fetchone()
        estimated_rows = table_stats[0] if table_stats else 0
        table_size_mb = ((table_stats[1] or 0) + (table_stats[2] or 0)) / 1024 / 1024

        batch_size = getattr(args, 'batch_size', 0)
        if batch_size <= 0:
            batch_size = optimize_batch_size(table_size_mb, available_memory)

        log(f"📊 ~{estimated_rows:,} Zeilen, {table_size_mb:.1f}MB, Batch: {batch_size:,}")

        # Differential Backup Parameter
        last_max_id = None
        if getattr(args, 'differential', False) and backup_state:
            last_max_id = backup_state.get_last_max_id(table)
            if last_max_id:
                log(f"🔄 Differential Mode: Letzte MAX ID = {last_max_id}")

        # Temporäre Indizes für große Tabellen
        temp_indexes = []
        if getattr(args, 'create_temp_indexes', False) and table_size_mb > 100:
            temp_indexes = create_temporary_indexes(cursor, args.db_from, table, columns)

        try:
            # Daten laden
            log("📥 Lade Quelldaten")
            data_from = fetch_table_data_streaming(
                cursor, args.db_from, table, columns,
                since_ts=getattr(args, 'since_ts', None),
                batch_size=batch_size,
                only_live_version=getattr(args, 'only_live_version', False),
                differential=getattr(args, 'differential', False),
                last_max_id=last_max_id
            )

            log("📥 Lade Zieldaten")
            data_to = fetch_table_data_streaming(
                cursor_dst, args.db_to, table, columns,
                since_ts=getattr(args, 'since_ts', None),
                batch_size=batch_size,
                only_live_version=getattr(args, 'only_live_version', False)
            )

            stats["rows_processed"] = len(data_from) + len(data_to)

            if perf_monitor:
                perf_monitor.processed_rows += stats["rows_processed"]
                perf_monitor.processed_tables += 1

        finally:
            # Temporäre Indizes entfernen
            if temp_indexes:
                drop_temporary_indexes(cursor, args.db_from, table, temp_indexes)

        # Daten zu Dictionaries konvertieren
        log("🔄 Konvertiere und vergleiche Daten")
        
        dict_from = {}
        max_id_value = None

        for row in tqdm(data_from, desc="Processing source", leave=False):
            pk_tuple = tuple(row[i] for i, col in enumerate(columns) if col in pk_columns)
            row_dict = row_to_dict(columns, row)
            dict_from[pk_tuple] = row_dict

            # Verfolge MAX ID für Differential Backup
            if getattr(args, 'differential', False) and metadata['auto_increment']:
                ai_col = list(metadata['auto_increment'].keys())[0]
                ai_index = columns.index(ai_col)
                if row[ai_index] and (max_id_value is None or row[ai_index] > max_id_value):
                    max_id_value = row[ai_index]

        dict_to = {}
        for row in tqdm(data_to, desc="Processing target", leave=False):
            pk_tuple = tuple(row[i] for i, col in enumerate(columns) if col in pk_columns)
            dict_to[pk_tuple] = row_to_dict(columns, row)

        stats["max_id"] = max_id_value

        # Ausgabedateien vorbereiten
        output_dir = getattr(args, 'output_dir', DEFAULT_OUTPUT_DIR)
        out_path = os.path.join(output_dir, f"{table}.sql")
        rollback_path = os.path.join(output_dir, f"rollback_{table}.sql")
        
        newer_dir = os.path.join(output_dir, "newer_in_target")
        os.makedirs(newer_dir, exist_ok=True)
        newer_target_path = os.path.join(newer_dir, f"{table}.newer_in_target.sql")

        # Vergleich durchführen
        dry_run = getattr(args, 'dry_run', False)
        
        with (open(out_path, 'w', encoding='utf-8') if not dry_run else open(os.devnull, 'w')) as out_file:
            rollback_file = open(rollback_path, 'w', encoding='utf-8') if not dry_run else open(os.devnull, 'w')
            newer_target_file = open(newer_target_path, 'w', encoding='utf-8') if not dry_run else None

            # Sammle INSERT-Rows für AUTO_INCREMENT Behandlung
            insert_rows = []
            for pk in set(dict_from.keys()).difference(dict_to.keys()):
                row_from = dict_from.get(pk)
                if row_from:
                    insert_rows.append(row_from)

            # Behandle AUTO_INCREMENT Konflikte
            ai_statements = []
            if insert_rows and metadata['auto_increment']:
                insert_rows, ai_statements = handle_auto_increment_conflicts(
                    cursor_dst, args.db_to, table, insert_rows, metadata['auto_increment']
                )

            # Schreibe AUTO_INCREMENT Statements
            if ai_statements and not dry_run:
                out_file.write("-- AUTO_INCREMENT Konfliktbehandlung\n")
                for stmt in ai_statements:
                    out_file.write(stmt + "\n")
                out_file.write("\n")

            # Verarbeite alle Änderungen
            insert_index = 0
            for pk in tqdm(set(dict_from.keys()).union(dict_to.keys()), 
                          desc=f"Comparing {table}", leave=False):
                
                row_from = dict_from.get(pk)
                row_to = dict_to.get(pk)

                if row_from and not row_to:
                    # INSERT
                    if insert_index < len(insert_rows):
                        actual_row = insert_rows[insert_index]
                        insert_index += 1
                    else:
                        actual_row = row_from

                    sql = generate_insert(
                        table, columns, actual_row, metadata, pk_columns,
                        on_duplicate=getattr(args, 'insert_on_duplicate', False)
                    )
                    
                    if sql:
                        if not dry_run:
                            out_file.write(sql + '\n')
                            rollback_sql = generate_delete(table, pk, pk_columns, metadata)
                            if rollback_sql:
                                rollback_file.write(rollback_sql + '\n')
                        else:
                            print(f"-- (DRY-RUN) {sql}")
                        stats["inserts"] += 1

                elif not row_from and row_to and getattr(args, 'allow_deletes', False):
                    # DELETE
                    sql = generate_delete(table, pk, pk_columns, metadata)
                    if sql:
                        if not dry_run:
                            out_file.write(sql + '\n')
                            rollback_sql = generate_insert(table, columns, row_to, metadata, pk_columns)
                            if rollback_sql:
                                rollback_file.write(rollback_sql + '\n')
                        else:
                            print(f"-- (DRY-RUN) {sql}")
                        stats["deletes"] += 1

                elif row_from and row_to:
                    # Mögliches UPDATE
                    compare_columns = [c for c in columns 
                                     if c not in pk_columns and c not in metadata['generated']]
                    differing_cols = [c for c in compare_columns if row_from[c] != row_to[c]]

                    # Ignoriere reine updated_at Änderungen
                    if len(differing_cols) == 1 and differing_cols[0] == 'updated_at':
                        continue

                    if differing_cols:
                        # Prüfe ob Ziel neuer ist
                        newer_in_target = False
                        ts_reasons = []
                        
                        for ts_field in ['updated_at', 'created_at']:
                            if ts_field in row_from and ts_field in row_to:
                                from_ts, to_ts = row_from[ts_field], row_to[ts_field]
                                if isinstance(from_ts, datetime) and isinstance(to_ts, datetime):
                                    if to_ts > from_ts:
                                        newer_in_target = True
                                        ts_reasons.append(f"{ts_field}: {from_ts} → {to_ts}")

                        if newer_in_target:
                            # Dokumentiere neuere Datensätze in Ziel
                            if newer_target_file:
                                newer_target_file.write(f"-- PK: {pk}\n")
                                newer_target_file.write(f"-- Differenzen: {', '.join(differing_cols)}\n")
                                newer_target_file.write(f"-- Zeitstempel: {'; '.join(ts_reasons)}\n")
                                where_clause = ' AND '.join(
                                    f"`{col}` = {quote_value(pk_val, col in metadata['binary'], col in metadata['json'])}"
                                    for col, pk_val in zip(pk_columns, pk)
                                )
                                newer_target_file.write(f"SELECT * FROM `{table}` WHERE {where_clause};\n\n")
                            stats["newer_in_target"] += 1
                            
                        elif not getattr(args, 'skip_updates', False):
                            # UPDATE durchführen
                            sql = generate_update(table, columns, row_from, pk_columns, metadata)
                            if sql:
                                if not dry_run:
                                    out_file.write(sql + '\n')
                                    rollback_sql = generate_update(table, columns, row_to, pk_columns, metadata)
                                    if rollback_sql:
                                        rollback_file.write(rollback_sql + '\n')
                                else:
                                    print(f"-- (DRY-RUN) {sql}")
                                stats["updates"] += 1

            # Dateien schließen
            if rollback_file != open(os.devnull, 'w'):
                rollback_file.close()
            if newer_target_file:
                newer_target_file.close()

        # Aufräumen leerer Dateien
        total_changes = stats["inserts"] + stats["updates"] + stats["deletes"]
        
        if total_changes == 0 and not dry_run:
            if os.path.exists(out_path):
                os.remove(out_path)
            if os.path.exists(rollback_path):
                os.remove(rollback_path)
            log("✅ Keine Änderungen - Dateien gelöscht")
        else:
            log(f"💾 {stats['inserts']} INSERTs, {stats['updates']} UPDATEs, "
                f"{stats['deletes']} DELETEs, {stats['newer_in_target']} neuere in Ziel")

        if stats["newer_in_target"] == 0 and not dry_run and os.path.exists(newer_target_path):
            os.remove(newer_target_path)

        cursor.close()
        cursor_dst.close()
        conn_src.close()
        conn_dst.close()

    except Exception as e:
        import traceback
        log(f"❌ Fehler: {e}")
        log(f"Stack-Trace:\n{traceback.format_exc()}")
        stats["status"] = "error"
        stats["error"] = str(e)

    return stats


# ==============================================================================
# OUTPUT & MERGING
# ==============================================================================

def create_merged_output(args, tables: List[str], all_stats: List[Dict[str, Any]]):
    """Erstellt zusammengefasste Output-Dateien"""
    
    if not getattr(args, 'merge_output', False):
        return

    merged_path = os.path.join(os.getcwd(), "merged_diff.sql")
    
    # Sammle Insert-Statistiken
    insert_stats = {s["table"]: s.get("inserts", 0) for s in all_stats if s.get("inserts", 0) > 0}
    
    with open(merged_path, 'w', encoding='utf-8') as merged_file:
        # Header
        total_stats = {
            "inserts": sum(s.get("inserts", 0) for s in all_stats),
            "updates": sum(s.get("updates", 0) for s in all_stats),
            "deletes": sum(s.get("deletes", 0) for s in all_stats),
            "total_rows": sum(s.get("rows_processed", 0) for s in all_stats)
        }
        
        merged_file.write(f"-- Optimierter Datenbank-Diff generiert am {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        merged_file.write(f"-- Quelle: {args.db_from} → Ziel: {args.db_to}\n")
        merged_file.write(f"-- Statistik: {total_stats['inserts']} INSERTs, {total_stats['updates']} UPDATEs, {total_stats['deletes']} DELETEs\n")
        merged_file.write(f"-- Zeilen verarbeitet: {total_stats['total_rows']:,}\n")
        
        if hasattr(args, 'since_ts') and args.since_ts:
            merged_file.write(f"-- Zeitfilter: ab {args.since_ts}\n")
        
        if getattr(args, 'differential', False):
            merged_file.write(f"-- Differential Backup Mode\n")
        
        merged_file.write("\n")

        # Performance-Einstellungen
        merged_file.write("-- Performance-optimierte Einstellungen\n")
        merged_file.write("SET FOREIGN_KEY_CHECKS = 0;\n")
        merged_file.write("SET UNIQUE_CHECKS = 0;\n")
        merged_file.write("SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n")
        merged_file.write("SET AUTOCOMMIT = 0;\n")
        merged_file.write("START TRANSACTION;\n\n")

        # AUTO_INCREMENT Vorbereitung
        if insert_stats:
            merged_file.write("-- AUTO_INCREMENT Vorbereitung\n")
            append_auto_increment_statements(args, tables, merged_file, insert_stats)

        # Diff-Statements
        output_dir = getattr(args, 'output_dir', DEFAULT_OUTPUT_DIR)
        sql_files = []
        for fname in sorted(os.listdir(output_dir)):
            if (fname.endswith('.sql') and 
                not fname.startswith('rollback_') and 
                'newer_in_target' not in fname):
                sql_files.append(fname)

        if sql_files:
            merged_file.write("-- Diff-Statements\n")
            for fname in sql_files:
                file_path = os.path.join(output_dir, fname)
                with open(file_path, 'r', encoding='utf-8') as f:
                    content = f.read().strip()
                    if content:
                        merged_file.write(f"-- Datei: {fname}\n")
                        merged_file.write(content)
                        merged_file.write("\n\n")

        # Transaction abschließen
        merged_file.write("COMMIT;\n")
        merged_file.write("SET AUTOCOMMIT = 1;\n")
        merged_file.write("SET FOREIGN_KEY_CHECKS = 1;\n")
        merged_file.write("SET UNIQUE_CHECKS = 1;\n")

    print(f"📝 Zusammengefasste Diff-Datei: {merged_path}")

    # Erstelle auch Rollback-Datei
    create_merged_rollback(args, output_dir)
    create_merged_newer_in_target(args, output_dir)


def append_auto_increment_statements(args, tables: List[str], merged_file, insert_stats: Dict[str, int]):
    """Fügt intelligente AUTO_INCREMENT Statements hinzu"""
    
    dst = args.dst_config
    conn = create_connection(dst['host'], dst['user'], dst['password'], dst['port'], args.db_to, socket_path=dst.get('socket'))
    cursor = conn.cursor()
    
    for table in tables:
        if table not in insert_stats or insert_stats[table] == 0:
            continue
            
        cursor.execute(f"""
            SELECT COLUMN_NAME
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = %s
              AND EXTRA LIKE '%%auto_increment%%'
        """, (args.db_to, table))
        
        ai_columns = [row[0] for row in cursor.fetchall()]
        if not ai_columns:
            continue
            
        ai_col = ai_columns[0]
        
        # Hole aktuelle Werte
        cursor.execute(f"SELECT COALESCE(MAX(`{ai_col}`), 0) FROM `{args.db_to}`.`{table}`")
        current_max = cursor.fetchone()[0]
        
        # Verwende explizite Datenbankauswahl für SHOW TABLE STATUS
        cursor.execute(f"SHOW TABLE STATUS FROM `{args.db_to}` LIKE '{table}'")
        table_status = cursor.fetchone()
        current_ai = table_status[10] if table_status else 0
        
        inserted_rows = insert_stats[table]
        
        # Berechne sicheren Wert
        buffer_size = max(100, inserted_rows * 2)
        safe_start = max(current_max, current_ai) + buffer_size
        
        merged_file.write(f"-- AUTO_INCREMENT für {table}: MAX={current_max}, AI={current_ai}, Inserts={inserted_rows}\n")
        merged_file.write(f"ALTER TABLE `{table}` AUTO_INCREMENT = {safe_start};\n")
    
    cursor.close()
    conn.close()
    merged_file.write("\n")


def create_merged_rollback(args, output_dir: str):
    """Erstellt zusammengefasste Rollback-Datei"""
    merged_rollback_path = os.path.join(os.getcwd(), "merged_rollback.sql")
    
    with open(merged_rollback_path, 'w', encoding='utf-8') as merged_rollback:
        merged_rollback.write(f"-- Rollback generiert am {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n")
        merged_rollback.write("SET FOREIGN_KEY_CHECKS = 0;\n")
        merged_rollback.write("SET UNIQUE_CHECKS = 0;\n")
        merged_rollback.write("START TRANSACTION;\n\n")

        for fname in sorted(os.listdir(output_dir)):
            if fname.startswith("rollback_") and fname.endswith(".sql"):
                file_path = os.path.join(output_dir, fname)
                with open(file_path, 'r', encoding='utf-8') as f:
                    content = f.read().strip()
                    if content:
                        merged_rollback.write(f"-- Datei: {fname}\n")
                        merged_rollback.write(content + '\n\n')

        merged_rollback.write("COMMIT;\n")
        merged_rollback.write("SET FOREIGN_KEY_CHECKS = 1;\n")
        merged_rollback.write("SET UNIQUE_CHECKS = 1;\n")

    print(f"📝 Rollback-Datei: {merged_rollback_path}")


def create_merged_newer_in_target(args, output_dir: str):
    """Erstellt zusammengefasste Datei für neuere Ziel-Datensätze"""
    newer_dir = os.path.join(output_dir, "newer_in_target")
    if not os.path.exists(newer_dir) or not os.listdir(newer_dir):
        return

    newer_merge_path = os.path.join(os.getcwd(), "merged_newer_in_target.sql")
    
    with open(newer_merge_path, 'w', encoding='utf-8') as merged_file:
        merged_file.write("-- Neuere Datensätze in Ziel-Datenbank\n")
        merged_file.write(f"-- Generiert am {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        merged_file.write(f"-- Quelle: {args.db_from} → Ziel: {args.db_to}\n\n")

        for fname in sorted(os.listdir(newer_dir)):
            if fname.endswith('.sql'):
                file_path = os.path.join(newer_dir, fname)
                with open(file_path, 'r', encoding='utf-8') as f:
                    content = f.read().strip()
                    if content:
                        merged_file.write(f"-- Datei: {fname}\n")
                        merged_file.write(content)
                        merged_file.write("\n\n")

    print(f"📝 Neuere Ziel-Datensätze: {newer_merge_path}")


# ==============================================================================
# COMMAND LINE INTERFACE & MAIN
# ==============================================================================

def create_argument_parser() -> argparse.ArgumentParser:
    """Erstellt den Argument Parser"""
    parser = argparse.ArgumentParser(
        description="Performance-optimierter MySQL Datenbankvergleich",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Beispiele:
  %(prog)s --from backup_db --to live_db --user myuser --password mypass
  %(prog)s --from backup_db --to live_db --user myuser --password mypass --since "2025-01-01 00:00:00"
  %(prog)s --from backup_db --to live_db --user myuser --password mypass --differential --tables "order,customer"
        """
    )
    
    # Verbindungsparameter
    parser.add_argument('--host', default='localhost', help='MySQL Host (Standard: localhost)')
    parser.add_argument('--user', required=True, help='MySQL Benutzername')
    parser.add_argument('--password', required=True, help='MySQL Passwort')
    parser.add_argument('--port', default=3306, type=int, help='MySQL Port (Standard: 3306)')
    
    # Datenbankparameter
    parser.add_argument('--from', dest='db_from', required=True, help='Quelldatenbank')
    parser.add_argument('--to', dest='db_to', required=True, help='Zieldatenbank')
    
    # Tabellenauswahl
    parser.add_argument('--tables', help='Kommagetrennte Liste von Tabellen')
    parser.add_argument('--ignore', help='Zu ignorierende Tabellen (Wildcards erlaubt)')
    
    # Zeitfilter
    parser.add_argument('--since', help='Nur Zeilen ab Timestamp (lokale Zeit → UTC)')
    parser.add_argument('--since-utc', help='Nur Zeilen ab UTC-Timestamp')
    
    # Output-Optionen
    parser.add_argument('--output-dir', default=DEFAULT_OUTPUT_DIR, help='Ausgabeverzeichnis')
    parser.add_argument('--merge-output', action='store_true', help='Alle Diffs in einer Datei zusammenfassen')
    parser.add_argument('--dry-run', action='store_true', help='Keine Dateien schreiben')
    
    # Verhalten
    parser.add_argument('--allow-deletes', action='store_true', help='DELETE-Statements erzeugen')
    parser.add_argument('--insert-on-duplicate', action='store_true', help='ON DUPLICATE KEY UPDATE verwenden')
    parser.add_argument('--skip-updates', action='store_true', help='Keine UPDATE-Statements')
    
    # Shopware-spezifisch
    parser.add_argument('--only-live-version', action='store_true', help='Nur Live-Version Datensätze')
    parser.add_argument('--enable-shopware-numbers', action='store_true', help='Shopware Nummerierung')
    
    # Performance
    parser.add_argument('--jobs', type=int, default=DEFAULT_JOBS, help='Anzahl paralleler Prozesse')
    parser.add_argument('--batch-size', type=int, default=0, help='Batch-Größe (0=automatisch)')
    parser.add_argument('--create-temp-indexes', action='store_true', help='Temporäre Indizes erstellen')
    parser.add_argument('--performance-monitoring', action='store_true', help='Performance-Monitoring')
    
    # Differential Backup
    parser.add_argument('--differential', action='store_true', help='Differential Backup Mode')
    parser.add_argument('--state-file', default=DEFAULT_STATE_FILE, help='Status-Datei für Differential Backup')
    parser.add_argument('--reset-state', action='store_true', help='Status zurücksetzen')
    
    # Cross-Server Sync (optional — für Abgleich zwischen zwei Servern)
    parser.add_argument('--src-host', help='Quell-Host (überschreibt --host für Quelle)')
    parser.add_argument('--src-user', help='Quell-User (überschreibt --user für Quelle)')
    parser.add_argument('--src-password', help='Quell-Passwort (überschreibt --password für Quelle)')
    parser.add_argument('--src-port', type=int, help='Quell-Port (überschreibt --port für Quelle)')
    parser.add_argument('--src-socket', help='Quell Unix-Socket Pfad (z.B. SSH-Socket-Forwarding)')
    parser.add_argument('--dst-host', help='Ziel-Host (überschreibt --host für Ziel)')
    parser.add_argument('--dst-user', help='Ziel-User (überschreibt --user für Ziel)')
    parser.add_argument('--dst-password', help='Ziel-Passwort (überschreibt --password für Ziel)')
    parser.add_argument('--dst-port', type=int, help='Ziel-Port (überschreibt --port für Ziel)')
    parser.add_argument('--dst-socket', help='Ziel Unix-Socket Pfad')
    
    return parser


def main():
    """Hauptfunktion"""
    start_time = datetime.now()
    
    # Argument Parsing
    parser = create_argument_parser()
    args = parser.parse_args()
    
    # Zeitstempel-Verarbeitung
    since_ts = None
    if args.since and args.since_utc:
        print("❌ Bitte nur --since ODER --since-utc verwenden")
        return 1
    
    if args.since:
        since_ts = parse_since_timestamp(args.since, timezone_aware=True)
        if since_ts is None:
            return 1
    elif args.since_utc:
        since_ts = parse_since_timestamp(args.since_utc, timezone_aware=False)
        if since_ts is None:
            return 1
    
    args.since_ts = since_ts
    
    # Performance-Monitor initialisieren
    perf_monitor = None
    if args.performance_monitoring:
        perf_monitor = PerformanceMonitor()
        perf_monitor.start_monitoring()
        print("📊 Performance-Monitoring aktiviert")
    
    # Differential Backup State
    backup_state = None
    if args.differential:
        if args.reset_state and os.path.exists(args.state_file):
            os.remove(args.state_file)
            print(f"🔄 Status zurückgesetzt: {args.state_file}")
        
        backup_state = DifferentialBackupState(args.state_file)
        print(f"🔄 Differential Backup aktiviert")
    
    # Connection Configs bauen (Dual-Connection oder Single)
    args.src_config = {
        'host': getattr(args, 'src_host', None) or args.host,
        'user': getattr(args, 'src_user', None) or args.user,
        'password': getattr(args, 'src_password', None) or args.password,
        'port': getattr(args, 'src_port', None) or args.port,
        'socket': getattr(args, 'src_socket', None),
    }
    args.dst_config = {
        'host': getattr(args, 'dst_host', None) or args.host,
        'user': getattr(args, 'dst_user', None) or args.user,
        'password': getattr(args, 'dst_password', None) or args.password,
        'port': getattr(args, 'dst_port', None) or args.port,
        'socket': getattr(args, 'dst_socket', None),
    }

    src = args.src_config
    dst = args.dst_config

    if src['host'] != dst['host'] or src.get('socket') != dst.get('socket'):
        print(f"🔀 Cross-Server Modus: {src['host']} → {dst['host']}")

    # Verbindungsvalidierung
    print("🔗 Validiere Datenbankverbindungen...")
    if not validate_connection(src['host'], src['user'], src['password'], src['port'], args.db_from, socket_path=src.get('socket')):
        return 1
    if not validate_connection(dst['host'], dst['user'], dst['password'], dst['port'], args.db_to, socket_path=dst.get('socket')):
        return 1
    print("✅ Verbindungen erfolgreich validiert")
    
    # Output-Verzeichnis vorbereiten
    if os.path.exists(args.output_dir):
        print(f"🧹 Bereinige Output-Verzeichnis: {args.output_dir}")
        shutil.rmtree(args.output_dir)
    
    os.makedirs(args.output_dir, exist_ok=True)
    print(f"📁 Output-Verzeichnis: {args.output_dir}")
    
    # Ignore-Patterns verarbeiten
    args.ignore_patterns = [p.strip() for p in args.ignore.split(',')] if args.ignore else []
    
    # Tabellenliste erstellen
    src = args.src_config
    conn = create_connection(src['host'], src['user'], src['password'], src['port'], socket_path=src.get('socket'))
    cursor = conn.cursor()
    
    if args.tables:
        tables = [t.strip() for t in args.tables.split(',')]
    else:
        cursor.execute(f"SHOW TABLES FROM `{args.db_from}`")
        tables = [row[0] for row in cursor.fetchall()]
    
    # Tabellenpriorisierung
    table_priorities = []
    for table in tables:
        priority = get_table_priority(cursor, args.db_from, table)
        table_priorities.append((priority, table))
    
    table_priorities.sort(key=lambda x: x[0], reverse=True)
    tables = [table for priority, table in table_priorities]
    
    print("📋 Tabellen priorisiert:")
    for i, (priority, table) in enumerate(table_priorities[:5]):
        print(f"   {i+1}. {table} (Priorität: {priority})")
    
    cursor.close()
    conn.close()
    
    print(f"\n🚀 Starte Vergleich: {len(tables)} Tabellen mit {args.jobs} Prozessen")
    
    # Zeige System-Info
    available_memory = psutil.virtual_memory().available / 1024 / 1024
    print(f"💾 Verfügbarer Speicher: {available_memory:.0f} MB")
    
    if args.batch_size > 0:
        print(f"📦 Manuelle Batch-Größe: {args.batch_size:,}")
    else:
        print(f"📦 Automatische Batch-Optimierung aktiviert")
    
    # Parallele Verarbeitung
    args_dict = vars(args)
    all_stats = []
    
    with Pool(processes=args.jobs) as pool:
        results = pool.starmap(
            compare_table_optimized,
            [(table, args_dict, perf_monitor, backup_state) for table in tables]
        )
        all_stats.extend(results)
    
    # Performance-Monitoring stoppen
    if perf_monitor:
        perf_monitor.stop_monitoring()
        perf_stats = perf_monitor.get_stats()
        print(f"\n📊 Performance-Statistiken:")
        print(f"   ⏱️  Laufzeit: {perf_stats['duration']:.1f}s")
        print(f"   📋 Tabellen: {perf_stats['tables_processed']}")
        print(f"   📝 Zeilen: {perf_stats['rows_processed']:,}")
        print(f"   🚀 Zeilen/s: {perf_stats['rows_per_second']:.0f}")
        print(f"   💾 Ø Memory: {perf_stats['avg_memory_mb']:.0f} MB")
        print(f"   📈 Max Memory: {perf_stats['max_memory_mb']:.0f} MB")
    
    # Differential Backup State aktualisieren
    if args.differential and backup_state:
        table_states = {}
        for stat in all_stats:
            if stat.get("status") == "success" and stat.get("max_id"):
                table_states[stat["table"]] = {
                    "last_sync": datetime.now(),
                    "last_max_id": stat["max_id"]
                }
        
        if table_states:
            backup_state.save_state(table_states)
            print(f"💾 Differential State für {len(table_states)} Tabellen aktualisiert")
    
    # Statistiken berechnen
    total_stats = {
        "inserts": sum(s.get("inserts", 0) for s in all_stats),
        "updates": sum(s.get("updates", 0) for s in all_stats),
        "deletes": sum(s.get("deletes", 0) for s in all_stats),
        "newer_in_target": sum(s.get("newer_in_target", 0) for s in all_stats),
        "errors": len([s for s in all_stats if s.get("status") == "error"]),
        "ignored": len([s for s in all_stats if s.get("status") == "ignored"]),
        "no_pk": len([s for s in all_stats if s.get("status") == "no_primary_key"]),
        "not_exists": len([s for s in all_stats if s.get("status") == "table_not_exists"]),
        "total_rows": sum(s.get("rows_processed", 0) for s in all_stats)
    }
    
    # Ergebnisse anzeigen
    print(f"\n📊 Gesamtstatistik:")
    print(f"   📝 {total_stats['inserts']:,} INSERTs")
    print(f"   🔄 {total_stats['updates']:,} UPDATEs")
    print(f"   🗑️ {total_stats['deletes']:,} DELETEs")
    print(f"   🕒 {total_stats['newer_in_target']:,} neuere in Ziel")
    print(f"   📊 {total_stats['total_rows']:,} Zeilen verarbeitet")
    
    if total_stats['errors'] > 0:
        print(f"   ❌ {total_stats['errors']} Fehler")
        error_tables = [s for s in all_stats if s.get("status") == "error"]
        for error_table in error_tables[:5]:  # Zeige nur erste 5
            print(f"      - {error_table['table']}: {error_table.get('error', 'Unbekannt')}")
    
    if total_stats['ignored'] > 0:
        print(f"   🚫 {total_stats['ignored']} ignoriert")
    
    if total_stats['no_pk'] > 0:
        print(f"   🔑 {total_stats['no_pk']} ohne Primärschlüssel")
    
    if total_stats['not_exists'] > 0:
        print(f"   📋 {total_stats['not_exists']} nicht in Ziel vorhanden")
    
    print(f"\n🎉 Vergleich abgeschlossen. Output: {args.output_dir}")
    
    # Merged Output erstellen
    if args.merge_output:
        create_merged_output(args, tables, all_stats)
    
    # Laufzeit berechnen
    end_time = datetime.now()
    duration = end_time - start_time
    hours, remainder = divmod(duration.total_seconds(), 3600)
    minutes, seconds = divmod(remainder, 60)
    
    print(f"\n⏱️  Gesamtlaufzeit: ", end="")
    if hours > 0:
        print(f"{int(hours)}h {int(minutes)}m {int(seconds)}s")
    elif minutes > 0:
        print(f"{int(minutes)}m {int(seconds)}s")
    else:
        print(f"{seconds:.2f}s")
    
    # Performance-Zusammenfassung
    if total_stats['total_rows'] > 0:
        rows_per_second = total_stats['total_rows'] / duration.total_seconds()
        print(f"🚀 Performance: {rows_per_second:.0f} Zeilen/Sekunde")
    
    # Abschlussmeldung
    total_changes = total_stats["inserts"] + total_stats["updates"] + total_stats["deletes"]
    if total_changes > 0:
        print(f"\n🔧 {total_changes:,} Änderungen gefunden!")
        if args.merge_output:
            print(f"💡 Führe merged_diff.sql auf der Ziel-DB aus")
        if total_stats["newer_in_target"] > 0:
            print(f"⚠️  {total_stats['newer_in_target']} Datensätze in Ziel sind neuer")
        if args.differential:
            print(f"🔄 Differential Backup abgeschlossen")
    else:
        print(f"\n✅ Beide Datenbanken sind synchron!")
        if args.differential:
            print(f"🔄 Differential Status aktualisiert")
    
    return 0


if __name__ == "__main__":
    try:
        exit_code = main()
        exit(exit_code)
    except KeyboardInterrupt:
        print("\n\n⚠️  Abbruch durch Benutzer")
        exit(1)
    except Exception as e:
        print(f"\n\n❌ Unerwarteter Fehler: {e}")
        import traceback
        traceback.print_exc()
        exit(1)