#!/usr/bin/env python3
"""
Migrationsskript zum Import der Lehrmaterialien aus dem Dateisystem in die Datenbank.
"""

import os
import argparse
import mysql.connector
import sys
from pathlib import Path
import logging

# Logging konfigurieren
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

def get_db_connection(host, user, password, database):
    """Stellt eine Verbindung zur MySQL-Datenbank her."""
    try:
        conn = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        return conn
    except Exception as e:
        logger.error(f"Datenbankverbindungsfehler: {e}")
        return None

def get_teacher_id_by_code(cursor, school_code, teacher_code):
    """Ermittelt die ID eines Lehrers anhand der Schul- und Lehrercodes."""
    try:
        cursor.execute(
            "SELECT id FROM teachers WHERE school_code = %s AND teacher_code = %s",
            (school_code, teacher_code)
        )
        result = cursor.fetchone()
        if result:
            return result[0]
        return None
    except Exception as e:
        logger.error(f"Fehler beim Abrufen der Lehrer-ID: {e}")
        return None

def get_subject_id_by_name(cursor, subject_name):
    """Ermittelt die ID eines Fachs anhand des Namens."""
    try:
        cursor.execute(
            "SELECT id FROM subjects WHERE name = %s",
            (subject_name,)
        )
        result = cursor.fetchone()
        if result:
            return result[0]
        return None
    except Exception as e:
        logger.error(f"Fehler beim Abrufen der Fach-ID: {e}")
        return None

def get_assignment_type_id(cursor, name, teacher_id, subject_id):
    """Ermittelt die ID eines Aufgabentyps anhand des Namens und der Lehrer-ID."""
    try:
        # Zuerst in der Zuordnungstabelle nach dem Aufgabentyp suchen
        cursor.execute("""
            SELECT at.id 
            FROM assignment_types at
            JOIN teacher_subject_assignment_types tsat ON at.id = tsat.assignment_type_id
            WHERE at.name = %s AND tsat.teacher_id = %s AND tsat.subject_id = %s
        """, (name, teacher_id, subject_id))
        
        result = cursor.fetchone()
        if result:
            return result[0]
        
        # Wenn nicht gefunden, überprüfen, ob der Aufgabentyp existiert
        cursor.execute(
            "SELECT id FROM assignment_types WHERE name = %s",
            (name,)
        )
        result = cursor.fetchone()
        if result:
            assignment_type_id = result[0]
            
            # Aufgabentyp mit Lehrer und Fach verknüpfen
            cursor.execute("""
                INSERT INTO teacher_subject_assignment_types 
                (teacher_id, subject_id, assignment_type_id)
                VALUES (%s, %s, %s)
            """, (teacher_id, subject_id, assignment_type_id))
            
            return assignment_type_id
        
        # Wenn der Aufgabentyp nicht existiert, neu anlegen
        cursor.execute(
            "INSERT INTO assignment_types (name) VALUES (%s)",
            (name,)
        )
        assignment_type_id = cursor.lastrowid
        
        # Aufgabentyp mit Lehrer und Fach verknüpfen
        cursor.execute("""
            INSERT INTO teacher_subject_assignment_types 
            (teacher_id, subject_id, assignment_type_id)
            VALUES (%s, %s, %s)
        """, (teacher_id, subject_id, assignment_type_id))
        
        return assignment_type_id
    except Exception as e:
        logger.error(f"Fehler beim Abrufen/Erstellen des Aufgabentyps: {e}")
        return None

def read_file_content(file_path):
    """Liest den Inhalt einer Datei ein."""
    try:
        with open(file_path, 'r', encoding='utf-8') as file:
            return file.read()
    except Exception as e:
        logger.error(f"Fehler beim Lesen der Datei {file_path}: {e}")
        return None

def add_material(cursor, teacher_id, subject_id, assignment_type_id, name, year):
    """Fügt ein neues Material in die Datenbank ein."""
    try:
        cursor.execute("""
            INSERT INTO materials 
            (teacher_id, subject_id, assignment_type_id, name, description, year)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (teacher_id, subject_id, assignment_type_id, name, f"Importiert aus {name}", year))
        
        return cursor.lastrowid
    except Exception as e:
        logger.error(f"Fehler beim Hinzufügen des Materials: {e}")
        return None

def add_material_content(cursor, material_id, content_type, title, content):
    """Fügt einen Materialinhalt in die Datenbank ein."""
    try:
        cursor.execute("""
            INSERT INTO material_contents 
            (material_id, content_type, title, content)
            VALUES (%s, %s, %s, %s)
        """, (material_id, content_type, title, content))
        
        return cursor.lastrowid
    except Exception as e:
        logger.error(f"Fehler beim Hinzufügen des Materialinhalts: {e}")
        return None

def migrate_materials(conn, base_dir):
    """
    Migriert die Materialien aus dem Dateisystem in die Datenbank.
    
    Verzeichnisstruktur: base_dir/school/teacher/year/subject/assignment_type/
    """
    cursor = conn.cursor()
    
    # Überprüfen, ob die neuen Tabellen existieren
    try:
        cursor.execute("SHOW TABLES LIKE 'materials'")
        materials_exists = cursor.fetchone() is not None
        
        cursor.execute("SHOW TABLES LIKE 'material_contents'")
        contents_exists = cursor.fetchone() is not None
        
        if not materials_exists or not contents_exists:
            logger.error("Die erforderlichen Tabellen 'materials' und 'material_contents' existieren nicht.")
            return False
    except Exception as e:
        logger.error(f"Fehler beim Überprüfen der Tabellen: {e}")
        return False
    
    # Durch die Verzeichnisstruktur navigieren
    for school_dir in os.listdir(base_dir):
        school_path = os.path.join(base_dir, school_dir)
        if not os.path.isdir(school_path):
            continue
        
        for teacher_dir in os.listdir(school_path):
            teacher_path = os.path.join(school_path, teacher_dir)
            if not os.path.isdir(teacher_path):
                continue
            
            # Lehrer-ID abrufen
            teacher_id = get_teacher_id_by_code(cursor, school_dir, teacher_dir)
            if not teacher_id:
                logger.warning(f"Lehrer mit Code {school_dir}/{teacher_dir} nicht gefunden, überspringe.")
                continue
            
            for year_dir in os.listdir(teacher_path):
                year_path = os.path.join(teacher_path, year_dir)
                if not os.path.isdir(year_path):
                    continue
                
                for subject_dir in os.listdir(year_path):
                    subject_path = os.path.join(year_path, subject_dir)
                    if not os.path.isdir(subject_path):
                        continue
                    
                    # Fach-ID abrufen
                    subject_name = subject_dir.capitalize()
                    subject_id = get_subject_id_by_name(cursor, subject_name)
                    if not subject_id:
                        logger.warning(f"Fach {subject_name} nicht gefunden, überspringe.")
                        continue
                    
                    for assignment_type_dir in os.listdir(subject_path):
                        assignment_type_path = os.path.join(subject_path, assignment_type_dir)
                        if not os.path.isdir(assignment_type_path):
                            continue
                        
                        # Aufgabentyp-ID abrufen oder neu anlegen
                        assignment_type_name = assignment_type_dir.capitalize()
                        assignment_type_id = get_assignment_type_id(cursor, assignment_type_name, teacher_id, subject_id)
                        if not assignment_type_id:
                            logger.warning(f"Aufgabentyp {assignment_type_name} konnte nicht verarbeitet werden, überspringe.")
                            continue
                        
                        # Erstelle ein Basismaterial für diesen Aufgabentyp
                        logger.info(f"Verarbeite Material für {school_dir}/{teacher_dir}/{year_dir}/{subject_dir}/{assignment_type_dir}")
                        material_name = f"{subject_dir} - {assignment_type_dir}"
                        material_id = add_material(cursor, teacher_id, subject_id, assignment_type_id, material_name, year_dir)
                        if not material_id:
                            logger.error(f"Fehler beim Erstellen des Materials für {material_name}")
                            continue
                        
                        # Verarbeite Aufgabe.html
                        task_path = os.path.join(assignment_type_path, "aufgabe.html")
                        if os.path.exists(task_path):
                            task_content = read_file_content(task_path)
                            if task_content:
                                add_material_content(cursor, material_id, "task", None, task_content)
                                logger.info(f"Aufgabe für {material_name} importiert")
                        
                        # Verarbeite Prompt.html
                        prompt_path = os.path.join(assignment_type_path, "prompt.html")
                        if os.path.exists(prompt_path):
                            prompt_content = read_file_content(prompt_path)
                            if prompt_content:
                                add_material_content(cursor, material_id, "prompt", None, prompt_content)
                                logger.info(f"Prompt für {material_name} importiert")
                        
                        # Verarbeite Kriterien.html
                        criteria_path = os.path.join(assignment_type_path, "kriterien.html")
                        if os.path.exists(criteria_path):
                            criteria_content = read_file_content(criteria_path)
                            if criteria_content:
                                add_material_content(cursor, material_id, "criteria", None, criteria_content)
                                logger.info(f"Kriterien für {material_name} importiert")
                        
                        # Verarbeite Originaltexte
                        originals_dir = os.path.join(assignment_type_path, "originaltexte")
                        if os.path.exists(originals_dir) and os.path.isdir(originals_dir):
                            for original_file in os.listdir(originals_dir):
                                if original_file.endswith(".html"):
                                    original_path = os.path.join(originals_dir, original_file)
                                    original_content = read_file_content(original_path)
                                    if original_content:
                                        # Titel ist der Dateiname ohne .html
                                        title = original_file[:-5]
                                        add_material_content(cursor, material_id, "original_text", title, original_content)
                                        logger.info(f"Originaltext {title} für {material_name} importiert")
    
    # Änderungen speichern
    conn.commit()
    return True

def setup_db_tables(conn):
    """Erstellt die erforderlichen Tabellen in der Datenbank."""
    cursor = conn.cursor()
    
    try:
        # Überprüfen, ob die Tabellen bereits existieren
        cursor.execute("SHOW TABLES LIKE 'materials'")
        materials_exists = cursor.fetchone() is not None
        
        cursor.execute("SHOW TABLES LIKE 'material_contents'")
        contents_exists = cursor.fetchone() is not None
        
        if materials_exists and contents_exists:
            logger.info("Die Tabellen existieren bereits.")
            return True
        
        # Tabellen erstellen
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS materials (
                id INT AUTO_INCREMENT PRIMARY KEY,
                teacher_id INT NOT NULL,
                subject_id INT NOT NULL,
                assignment_type_id INT NOT NULL,
                name VARCHAR(255) NOT NULL,
                description TEXT,
                year VARCHAR(20) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
                FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
                FOREIGN KEY (assignment_type_id) REFERENCES assignment_types(id) ON DELETE CASCADE
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS material_contents (
                id INT AUTO_INCREMENT PRIMARY KEY,
                material_id INT NOT NULL,
                content_type ENUM('prompt', 'criteria', 'task', 'original_text') NOT NULL,
                title VARCHAR(255),
                content TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
                UNIQUE KEY (material_id, content_type, title)
            )
        """)
        
        conn.commit()
        logger.info("Tabellen erfolgreich erstellt.")
        return True
        
    except Exception as e:
        conn.rollback()
        logger.error(f"Fehler beim Erstellen der Tabellen: {e}")
        return False

def main():
    parser = argparse.ArgumentParser(description='Migriert Lehrmaterialien vom Dateisystem in die Datenbank.')
    parser.add_argument('--host', required=True, help='MySQL-Host')
    parser.add_argument('--user', required=True, help='MySQL-Benutzer')
    parser.add_argument('--password', required=True, help='MySQL-Passwort')
    parser.add_argument('--database', required=True, help='MySQL-Datenbankname')
    parser.add_argument('--materials-dir', default='schule-materialien', help='Pfad zum Materialien-Verzeichnis')
    parser.add_argument('--setup-tables', action='store_true', help='Erstelle die benötigten Tabellen in der Datenbank')
    
    args = parser.parse_args()
    
    logger.info("Verbinde mit der Datenbank...")
    conn = get_db_connection(args.host, args.user, args.password, args.database)
    
    if not conn:
        logger.error("Konnte keine Verbindung zur Datenbank herstellen. Abbruch.")
        sys.exit(1)
    
    # Wenn --setup-tables gesetzt ist, Tabellen erstellen
    if args.setup_tables:
        logger.info("Erstelle Tabellen...")
        if not setup_db_tables(conn):
            logger.error("Fehler beim Erstellen der Tabellen. Abbruch.")
            sys.exit(1)
    
    base_dir = Path(args.materials_dir)
    if not base_dir.exists() or not base_dir.is_dir():
        logger.error(f"Das Verzeichnis {base_dir} existiert nicht. Abbruch.")
        sys.exit(1)
    
    logger.info(f"Starte Migration von Materialien aus {base_dir}...")
    
    if migrate_materials(conn, base_dir):
        logger.info("Migration erfolgreich abgeschlossen.")
    else:
        logger.error("Fehler bei der Migration.")
        sys.exit(1)
    
    if conn.is_connected():
        conn.close()
        logger.info("Datenbankverbindung geschlossen.")

if __name__ == "__main__":
    main()