#!/usr/bin/env python3
"""
JTL Shop MariaDB Optimierung
============================
Erstellt fehlende Indizes, aktiviert FULLTEXT-Suche und optimiert MariaDB-Konfiguration.

Features:
- Backup aller Sucheinstellungen vor Änderungen
- Erstellt 5 Index-Typen auf allen betroffenen Shops
- Aktiviert FULLTEXT-Suche
- Generiert optimierte MariaDB-Konfiguration
- Generiert Rollback-Skript für Notfälle

Autor: Claude
Datum: 2025-12-12
"""

import subprocess
import json
import sys
import math
import os
from datetime import datetime
from pathlib import Path


# Konfiguration
TIMESTAMP = datetime.now().strftime("%Y%m%d_%H%M%S")
BACKUP_FILE = f"/root/jtl_search_backup_{TIMESTAMP}.json"
ROLLBACK_FILE = f"/root/jtl_search_rollback_{TIMESTAMP}.py"
LOG_FILE = f"/root/jtl_index_log_{TIMESTAMP}.txt"
MARIADB_CONFIG_FILE = "/etc/mysql/conf.d/ZZ-ztl-final-override.cnf"
MARIADB_CONFIG_BACKUP = f"/root/mariadb_config_backup_{TIMESTAMP}.cnf"

# Index-Definitionen
INDEXES = [
    {
        "name": "idx_kLieferscheinPos",
        "table": "tlieferscheinposinfo",
        "columns": "kLieferscheinPos",
        "check_column": "kLieferscheinPos"
    },
    {
        "name": "idx_kLieferschein",
        "table": "tlieferscheinpos",
        "columns": "kLieferschein",
        "check_column": "kLieferschein"
    },
    {
        "name": "idx_kKampagneDef_kKey",
        "table": "tkampagnevorgang",
        "columns": "kKampagneDef, kKey",
        "check_column": "kKampagneDef"
    },
    {
        "name": "idx_kXSellArtikel",
        "table": "txsellkauf",
        "columns": "kXSellArtikel",
        "check_column": "kXSellArtikel"
    }
]

FULLTEXT_INDEX = {
    "name": "idx_tartikel_fulltext",
    "table": "tartikel",
    "columns": "cName, cSeo, cSuchbegriffe, cArtNr, cKurzBeschreibung, cBeschreibung, cBarcode, cISBN, cHAN"
}


class Logger:
    def __init__(self, log_file):
        self.log_file = log_file
        self.messages = []
    
    def log(self, message, level="INFO"):
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        formatted = f"[{timestamp}] [{level}] {message}"
        print(formatted)
        self.messages.append(formatted)
    
    def save(self):
        with open(self.log_file, 'w') as f:
            f.write("\n".join(self.messages))
        print(f"\nLog gespeichert: {self.log_file}")


def run_mysql(query, database=None):
    """Führt MySQL-Query aus und gibt Ergebnis zurück."""
    cmd = ["mysql", "-N", "-B", "-e", query]
    if database:
        cmd.extend(["-D", database])
    
    result = subprocess.run(cmd, capture_output=True, text=True)
    if result.returncode != 0:
        return None, result.stderr
    return result.stdout.strip(), None


def get_all_shops():
    """Ermittelt alle JTL-Shop-Datenbanken."""
    query = """
        SELECT DISTINCT table_schema 
        FROM information_schema.tables 
        WHERE table_name = 'tartikel' 
        AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
        ORDER BY table_schema;
    """
    result, err = run_mysql(query)
    if err:
        return []
    return [line.strip() for line in result.split('\n') if line.strip()]


def check_index_exists(shop, table, check_column):
    """Prüft ob ein Index für die Spalte bereits existiert."""
    query = f"""
        SELECT COUNT(*) FROM information_schema.statistics 
        WHERE table_schema = '{shop}' 
        AND table_name = '{table}' 
        AND column_name = '{check_column}'
        AND index_name != 'PRIMARY';
    """
    result, _ = run_mysql(query)
    return result and int(result) > 0


def check_fulltext_exists(shop):
    """Prüft ob FULLTEXT-Index bereits existiert."""
    query = f"""
        SELECT COUNT(*) FROM information_schema.statistics 
        WHERE table_schema = '{shop}' 
        AND table_name = 'tartikel' 
        AND index_type = 'FULLTEXT';
    """
    result, _ = run_mysql(query)
    return result and int(result) > 0


def check_table_exists(shop, table):
    """Prüft ob Tabelle existiert."""
    query = f"""
        SELECT COUNT(*) FROM information_schema.tables 
        WHERE table_schema = '{shop}' 
        AND table_name = '{table}';
    """
    result, _ = run_mysql(query)
    return result and int(result) > 0


def get_search_setting(shop):
    """Liest aktuelle suche_fulltext Einstellung."""
    query = f"SELECT cWert FROM `{shop}`.teinstellungen WHERE cName = 'suche_fulltext' LIMIT 1;"
    result, _ = run_mysql(query)
    return result if result else "NICHT_GESETZT"


def set_search_setting(shop, value):
    """Setzt suche_fulltext Einstellung."""
    query = f"UPDATE `{shop}`.teinstellungen SET cWert = '{value}' WHERE cName = 'suche_fulltext';"
    _, err = run_mysql(query)
    return err is None


def create_index(shop, index_name, table, columns, is_fulltext=False):
    """Erstellt einen Index."""
    if is_fulltext:
        query = f"CREATE FULLTEXT INDEX `{index_name}` ON `{shop}`.`{table}` ({columns});"
    else:
        query = f"CREATE INDEX `{index_name}` ON `{shop}`.`{table}` ({columns});"
    
    _, err = run_mysql(query)
    return err


def get_total_database_size_gb():
    """Ermittelt die Gesamtgröße aller Datenbanken in GB."""
    query = """
        SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) 
        FROM information_schema.tables 
        WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
    """
    result, err = run_mysql(query)
    if err or not result:
        return None
    try:
        return float(result)
    except ValueError:
        return None


def calculate_buffer_pool_size(db_size_gb):
    """Berechnet Buffer Pool Size: DB-Größe × 1.2, aufgerundet auf ganze GB."""
    recommended = db_size_gb * 1.2
    return math.ceil(recommended)


def generate_mariadb_config(buffer_pool_gb):
    """Generiert die MariaDB-Konfiguration mit dynamischem Buffer Pool."""
    config = f"""# JTL-Shop MariaDB Optimierung
# Generiert am: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}
# Buffer Pool berechnet: DB-Größe × 1.2 = {buffer_pool_gb}G

[mysqld]
# InnoDB Buffer Pool (dynamisch berechnet)
innodb_buffer_pool_size = {buffer_pool_gb}G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2

# InnoDB Performance
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# Query Cache deaktiviert (bei MariaDB 10.1.7+ empfohlen)
query_cache_type = 0
query_cache_size = 0

# Table Cache für viele Shops
table_definition_cache = 20000
table_open_cache = 15000
open_files_limit = 30000

# Performance
skip_name_resolve = 1
optimizer_search_depth = 0
key_buffer_size = 64M

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 0

# Connections
max_connections = 400
thread_cache_size = 100
wait_timeout = 120

# Buffer für JTL-Queries
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
"""
    return config


def backup_existing_config():
    """Erstellt ein Backup der existierenden Config, falls vorhanden."""
    if os.path.exists(MARIADB_CONFIG_FILE):
        with open(MARIADB_CONFIG_FILE, 'r') as f:
            content = f.read()
        with open(MARIADB_CONFIG_BACKUP, 'w') as f:
            f.write(content)
        return True
    return False


def write_mariadb_config(config_content):
    """Schreibt die MariaDB-Konfiguration."""
    with open(MARIADB_CONFIG_FILE, 'w') as f:
        f.write(config_content)


def restart_mariadb():
    """Startet MariaDB neu."""
    result = subprocess.run(
        ["systemctl", "restart", "mariadb"],
        capture_output=True,
        text=True
    )
    return result.returncode == 0, result.stderr


def generate_rollback_script(backup_data):
    """Generiert ein Rollback-Skript."""
    script = f'''#!/usr/bin/env python3
"""
JTL Search Settings Rollback Script
====================================
Generiert am: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}

Dieses Skript stellt die ursprünglichen suche_fulltext Einstellungen wieder her.
"""

import subprocess

BACKUP_DATA = {json.dumps(backup_data, indent=4)}

def run_mysql(query):
    result = subprocess.run(["mysql", "-N", "-e", query], capture_output=True, text=True)
    return result.returncode == 0

def main():
    print("=== JTL Search Settings Rollback ===")
    print(f"Stelle {{len(BACKUP_DATA)}} Einstellungen wieder her...\\n")
    
    success = 0
    failed = 0
    
    for shop, value in BACKUP_DATA.items():
        if value == "NICHT_GESETZT":
            print(f"  [SKIP] {{shop}}: Keine Einstellung vorhanden")
            continue
        
        query = f"UPDATE `{{shop}}`.teinstellungen SET cWert = '{{value}}' WHERE cName = 'suche_fulltext';"
        if run_mysql(query):
            print(f"  [OK] {{shop}}: suche_fulltext = '{{value}}'")
            success += 1
        else:
            print(f"  [FEHLER] {{shop}}")
            failed += 1
    
    print(f"\\n=== Rollback abgeschlossen ===")
    print(f"Erfolgreich: {{success}}")
    print(f"Fehlgeschlagen: {{failed}}")

if __name__ == "__main__":
    main()
'''
    
    with open(ROLLBACK_FILE, 'w') as f:
        f.write(script)
    
    # Ausführbar machen
    subprocess.run(["chmod", "+x", ROLLBACK_FILE])


def main():
    logger = Logger(LOG_FILE)
    
    print("=" * 60)
    print("JTL Shop MariaDB Optimierung")
    print("=" * 60)
    print()
    
    # Phase 0: Shops ermitteln
    logger.log("Ermittle alle JTL-Shop-Datenbanken...")
    shops = get_all_shops()
    logger.log(f"Gefunden: {len(shops)} Shops")
    print()
    
    if not shops:
        logger.log("Keine Shops gefunden. Abbruch.", "ERROR")
        return
    
    # Phase 1: Backup der Sucheinstellungen
    print("=" * 60)
    print("PHASE 1: Backup der Sucheinstellungen")
    print("=" * 60)
    
    backup_data = {}
    for shop in shops:
        setting = get_search_setting(shop)
        backup_data[shop] = setting
        logger.log(f"  {shop}: suche_fulltext = '{setting}'")
    
    # Backup speichern
    with open(BACKUP_FILE, 'w') as f:
        json.dump(backup_data, f, indent=2)
    logger.log(f"Backup gespeichert: {BACKUP_FILE}")
    
    # Rollback-Skript generieren
    generate_rollback_script(backup_data)
    logger.log(f"Rollback-Skript generiert: {ROLLBACK_FILE}")
    print()
    
    # Phase 2: Standard-Indizes erstellen
    print("=" * 60)
    print("PHASE 2: Standard-Indizes erstellen")
    print("=" * 60)
    
    index_stats = {"created": 0, "skipped": 0, "failed": 0}
    
    for idx in INDEXES:
        logger.log(f"\n--- Index: {idx['name']} auf {idx['table']} ---")
        
        for shop in shops:
            # Prüfen ob Tabelle existiert
            if not check_table_exists(shop, idx['table']):
                continue
            
            # Prüfen ob Index existiert
            if check_index_exists(shop, idx['table'], idx['check_column']):
                logger.log(f"  [SKIP] {shop}: Index existiert bereits")
                index_stats["skipped"] += 1
                continue
            
            # Index erstellen
            err = create_index(shop, idx['name'], idx['table'], idx['columns'])
            if err:
                logger.log(f"  [FEHLER] {shop}: {err}", "ERROR")
                index_stats["failed"] += 1
            else:
                logger.log(f"  [OK] {shop}: Index erstellt")
                index_stats["created"] += 1
    
    print()
    
    # Phase 3: FULLTEXT-Index erstellen
    print("=" * 60)
    print("PHASE 3: FULLTEXT-Index erstellen")
    print("=" * 60)
    
    fulltext_stats = {"created": 0, "skipped": 0, "failed": 0}
    
    for shop in shops:
        # Prüfen ob FULLTEXT bereits existiert
        if check_fulltext_exists(shop):
            logger.log(f"  [SKIP] {shop}: FULLTEXT existiert bereits")
            fulltext_stats["skipped"] += 1
            continue
        
        # FULLTEXT-Index erstellen
        logger.log(f"  [WORK] {shop}: Erstelle FULLTEXT-Index (kann dauern)...")
        err = create_index(
            shop, 
            FULLTEXT_INDEX['name'], 
            FULLTEXT_INDEX['table'], 
            FULLTEXT_INDEX['columns'],
            is_fulltext=True
        )
        
        if err:
            logger.log(f"  [FEHLER] {shop}: {err}", "ERROR")
            fulltext_stats["failed"] += 1
        else:
            logger.log(f"  [OK] {shop}: FULLTEXT-Index erstellt")
            fulltext_stats["created"] += 1
    
    print()
    
    # Phase 4: FULLTEXT-Suche aktivieren
    print("=" * 60)
    print("PHASE 4: FULLTEXT-Suche aktivieren")
    print("=" * 60)
    
    search_stats = {"updated": 0, "skipped": 0, "failed": 0}
    
    for shop in shops:
        current = backup_data.get(shop, "NICHT_GESETZT")
        
        if current == "Y":
            logger.log(f"  [SKIP] {shop}: Bereits aktiviert")
            search_stats["skipped"] += 1
            continue
        
        if current == "NICHT_GESETZT":
            logger.log(f"  [SKIP] {shop}: Keine Einstellung vorhanden")
            search_stats["skipped"] += 1
            continue
        
        # Einstellung ändern
        if set_search_setting(shop, "Y"):
            logger.log(f"  [OK] {shop}: suche_fulltext = 'Y' (war: '{current}')")
            search_stats["updated"] += 1
        else:
            logger.log(f"  [FEHLER] {shop}", "ERROR")
            search_stats["failed"] += 1
    
    print()
    
    # Phase 5: MariaDB-Konfiguration optimieren
    print("=" * 60)
    print("PHASE 5: MariaDB-Konfiguration optimieren")
    print("=" * 60)
    
    config_stats = {"success": False, "restart": False}
    
    # Datenbankgröße ermitteln
    logger.log("Ermittle Gesamtgröße aller Datenbanken...")
    db_size_gb = get_total_database_size_gb()
    
    if db_size_gb is None:
        logger.log("Konnte Datenbankgröße nicht ermitteln. Überspringe Config-Optimierung.", "ERROR")
    else:
        logger.log(f"  Gesamtgröße: {db_size_gb} GB")
        
        # Buffer Pool berechnen
        buffer_pool_gb = calculate_buffer_pool_size(db_size_gb)
        logger.log(f"  Berechneter Buffer Pool: {db_size_gb} GB × 1.2 = {buffer_pool_gb} GB")
        
        # Backup der existierenden Config
        if backup_existing_config():
            logger.log(f"  Bestehendes Config-Backup erstellt: {MARIADB_CONFIG_BACKUP}")
        else:
            logger.log(f"  Keine bestehende Config gefunden, erstelle neu")
        
        # Config generieren und schreiben
        config_content = generate_mariadb_config(buffer_pool_gb)
        write_mariadb_config(config_content)
        logger.log(f"  Config geschrieben: {MARIADB_CONFIG_FILE}")
        config_stats["success"] = True
        
        # MariaDB neustarten
        logger.log("  Starte MariaDB neu...")
        success, err = restart_mariadb()
        if success:
            logger.log("  [OK] MariaDB erfolgreich neugestartet")
            config_stats["restart"] = True
        else:
            logger.log(f"  [FEHLER] MariaDB Neustart fehlgeschlagen: {err}", "ERROR")
    
    print()
    
    # Zusammenfassung
    print("=" * 60)
    print("ZUSAMMENFASSUNG")
    print("=" * 60)
    print()
    print("Standard-Indizes:")
    print(f"  Erstellt:       {index_stats['created']}")
    print(f"  Übersprungen:   {index_stats['skipped']}")
    print(f"  Fehlgeschlagen: {index_stats['failed']}")
    print()
    print("FULLTEXT-Index:")
    print(f"  Erstellt:       {fulltext_stats['created']}")
    print(f"  Übersprungen:   {fulltext_stats['skipped']}")
    print(f"  Fehlgeschlagen: {fulltext_stats['failed']}")
    print()
    print("Sucheinstellung (suche_fulltext = 'Y'):")
    print(f"  Aktualisiert:   {search_stats['updated']}")
    print(f"  Übersprungen:   {search_stats['skipped']}")
    print(f"  Fehlgeschlagen: {search_stats['failed']}")
    print()
    print("MariaDB-Konfiguration:")
    print(f"  Config erstellt: {'Ja' if config_stats['success'] else 'Nein'}")
    print(f"  MariaDB Restart: {'Erfolgreich' if config_stats['restart'] else 'Fehlgeschlagen/Übersprungen'}")
    print()
    print("=" * 60)
    print("WICHTIGE DATEIEN")
    print("=" * 60)
    print(f"  Search Backup:   {BACKUP_FILE}")
    print(f"  Rollback-Skript: {ROLLBACK_FILE}")
    print(f"  Config Backup:   {MARIADB_CONFIG_BACKUP}")
    print(f"  MariaDB Config:  {MARIADB_CONFIG_FILE}")
    print(f"  Log:             {LOG_FILE}")
    print()
    print("Bei Problemen:")
    print(f"  Rollback Search: python3 {ROLLBACK_FILE}")
    print(f"  Rollback Config: cp {MARIADB_CONFIG_BACKUP} {MARIADB_CONFIG_FILE} && systemctl restart mariadb")
    print()
    
    # Log speichern
    logger.save()


if __name__ == "__main__":
    main()