#!/usr/bin/env python3
"""
Skript zum Testen der MySQL-Datenbankverbindung und zum Exportieren der Datenbankstruktur.
Debug version to help identify issues loading environment variables.
"""

import os
import sys
import subprocess
import shutil
from dotenv import load_dotenv

# Füge das Hauptverzeichnis zum Python-Pfad hinzu
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

def debug_env_loading():
    """ Prints debug information about environment variables and .env file loading. """
    
    env_file_path = os.path.join(os.path.dirname(__file__), '..', '.env')
    print("DEBUG: Attempting to load .env file from:", env_file_path)
    
    # Check if .env file exists at that location
    exists = os.path.isfile(env_file_path)
    print("DEBUG: Does .env file exist at that path? ->", exists)

    print("DEBUG: Environment variables BEFORE load_dotenv() call:")
    for key in ("MYSQL_HOST","MYSQL_USER","MYSQL_PASSWORD","MYSQL_DATABASE","MYSQL_PORT"):
        print(f"  {key}={os.getenv(key)}")

    # Attempt to load .env
    try:
        load_dotenv(env_file_path, override=True)
        print("DEBUG: Successfully ran load_dotenv(...)")
    except Exception as e:
        print("DEBUG: Exception occurred during load_dotenv:", e)

    print("DEBUG: Environment variables AFTER load_dotenv() call:")
    for key in ("MYSQL_HOST","MYSQL_USER","MYSQL_PASSWORD","MYSQL_DATABASE","MYSQL_PORT"):
        print(f"  {key}={os.getenv(key)!r}")  # !r shows the raw Python string including quotes

def check_requirements():
    """
    Überprüft, ob alle erforderlichen Python-Pakete installiert sind.
    """
    try:
        import mysql.connector
        print("mysql-connector-python ist installiert.")
        return True
    except ImportError:
        print("mysql-connector-python ist nicht installiert. Bitte installieren Sie es mit 'pip install mysql-connector-python'.")
        return False

def test_mysql_connection():
    """
    Testet die Verbindung zur MySQL-Datenbank.
    
    Returns:
        bool: True, wenn die Verbindung erfolgreich ist, sonst False
    """
    # MySQL-Konfiguration
    host = os.getenv('MYSQL_HOST')
    user = os.getenv('MYSQL_USER')
    password = os.getenv('MYSQL_PASSWORD')
    database = os.getenv('MYSQL_DATABASE')
    port = int(os.getenv('MYSQL_PORT', 3306))
    
    print(f"DEBUG: MySQL-Konfiguration: Host={host}, Port={port}, User={user}, Database={database}")
    print(f"DEBUG: (Reminder) Password is => {repr(password)}")

    try:
        import mysql.connector
        from mysql.connector import Error
        
        print("Versuche, zur MySQL-Datenbank zu verbinden...")
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            port=port
        )
        
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"Verbunden mit MySQL Server Version: {db_info}")
            
            cursor = connection.cursor()
            cursor.execute("SHOW TABLES;")
            tables = cursor.fetchall()
            
            print(f"Tabellen in der Datenbank '{database}':")
            for table in tables:
                print(f"- {table[0]}")
                
                cursor.execute(f"DESCRIBE {table[0]};")
                columns = cursor.fetchall()
                print("  Spalten:")
                for column in columns:
                    print(f"    - {column[0]} ({column[1]})")
            
            cursor.close()
            connection.close()
            print("MySQL-Verbindung erfolgreich getestet und geschlossen.")
            return True
    except Error as e:
        print(f"Fehler bei der Verbindung zur MySQL-Datenbank: {e}")
        return False
    except Exception as e:
        print(f"Unerwarteter Fehler: {e}")
        return False

def export_database_structure():
    """
    Exportiert die Datenbankstruktur (ohne Daten) in eine SQL-Datei.
    
    Returns:
        bool: True, wenn der Export erfolgreich ist, sonst False
    """
    # MySQL-Konfiguration
    host = os.getenv('MYSQL_HOST')
    user = os.getenv('MYSQL_USER')
    password = os.getenv('MYSQL_PASSWORD')
    database = os.getenv('MYSQL_DATABASE')
    port = os.getenv('MYSQL_PORT', '3306')
    
    sql_file = f"{database}_structure.sql"
    
    mysqldump_path = shutil.which("mysqldump")
    if not mysqldump_path:
        print("mysqldump ist nicht im PATH. Versuche Python-Methode...")
        return export_structure_with_python()

    try:
        print(f"Exportiere Datenbankstruktur nach {sql_file} (mysqldump)...")
        cmd = [
            mysqldump_path,
            f"--host={host}",
            f"--port={port}",
            f"--user={user}",
            f"--password={password}",
            "--no-data",
            "--skip-comments",
            database
        ]
        
        with open(sql_file, 'w') as f:
            result = subprocess.run(cmd, stdout=f, stderr=subprocess.PIPE, text=True)
            
        if result.returncode == 0:
            print(f"Datenbankstruktur erfolgreich nach {sql_file} exportiert.")
            return True
        else:
            print(f"Fehler beim Exportieren: {result.stderr}")
            print("Versuche nun Python-Methode...")
            return export_structure_with_python()
    except Exception as e:
        print(f"Fehler beim Exportieren mit mysqldump: {e}")
        print("Versuche nun Python-Methode...")
        return export_structure_with_python()

def export_structure_with_python():
    """
    Exportiert die Datenbankstruktur mithilfe von Python-Code.
    
    Returns:
        bool: True, wenn der Export erfolgreich ist, sonst False
    """
    host = os.getenv('MYSQL_HOST')
    user = os.getenv('MYSQL_USER')
    password = os.getenv('MYSQL_PASSWORD')
    database = os.getenv('MYSQL_DATABASE')
    port = int(os.getenv('MYSQL_PORT', 3306))
    sql_file = f"{database}_structure.sql"
    
    try:
        import mysql.connector
        from mysql.connector import Error
        
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            port=port
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute("SHOW TABLES;")
            tables = cursor.fetchall()
            
            with open(sql_file, 'w') as f:
                f.write(f"-- Database structure for {database}\n\n")
                for table in tables:
                    table_name = table[0]
                    cursor.execute(f"SHOW CREATE TABLE {table_name};")
                    create_table = cursor.fetchone()[1]
                    f.write(f"{create_table};\n\n")
            
            cursor.close()
            connection.close()
            print(f"Datenbankstruktur erfolgreich nach {sql_file} exportiert (Python-Methode).")
            return True
    except Error as e:
        print(f"Fehler bei der Verbindung zur MySQL-Datenbank: {e}")
        return False
    except Exception as e:
        print(f"Unerwarteter Fehler: {e}")
        return False

if __name__ == "__main__":
    # First, debug environment loading:
    debug_env_loading()
    
    # Then check requirements:
    if not check_requirements():
        print("Bitte installieren Sie die erforderlichen Pakete und versuchen Sie es erneut.")
        sys.exit(1)
    
    # Test DB connection:
    connection_success = test_mysql_connection()
    
    # Export the structure if connection succeeded:
    if connection_success:
        export_database_structure()
    else:
        print("Datenbankexport übersprungen, da keine Verbindung hergestellt werden konnte.")