#!/usr/bin/env python3
"""
Migration Script: SQLite -> MariaDB
Überträgt alle Daten (Gerichte und Rezepte) von der lokalen SQLite-Datenbank in die MariaDB.
Verwendung:
  python3 migrate_to_mariadb.py
"""
import os
import sqlite3
import sys
from dotenv import load_dotenv

load_dotenv()

try:
    import pymysql
    import pymysql.cursors
except ImportError:
    print("Fehler: PyMySQL ist nicht installiert. Bitte 'pip install pymysql' ausführen.")
    sys.exit(1)

SQLITE_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data", "wtc.db")

MARIADB_HOST = os.getenv("MARIADB_HOST", "127.0.0.1")
MARIADB_PORT = int(os.getenv("MARIADB_PORT", "3306"))
MARIADB_USER = os.getenv("MARIADB_USER", "v2_usr_wtc")
MARIADB_PASS = os.getenv("MARIADB_PASS", "Wtc_Kochbuch_Pass_2026!")
MARIADB_NAME = os.getenv("MARIADB_NAME", "v2_db_wtc")

def migrate():
    print(f"=== Starte Migration: SQLite ({SQLITE_PATH}) -> MariaDB ({MARIADB_HOST}:{MARIADB_PORT}/{MARIADB_NAME}) ===")

    if not os.path.exists(SQLITE_PATH):
        print(f"Hinweis: Keine SQLite-Datenbank unter '{SQLITE_PATH}' gefunden.")
        return

    # 1. SQLite öffnen
    sqlite_conn = sqlite3.connect(SQLITE_PATH)
    sqlite_conn.row_factory = sqlite3.Row
    sqlite_cursor = sqlite_conn.cursor()

    # 2. MariaDB öffnen
    try:
        maria_conn = pymysql.connect(
            host=MARIADB_HOST,
            port=MARIADB_PORT,
            user=MARIADB_USER,
            password=MARIADB_PASS,
            database=MARIADB_NAME,
            charset="utf8mb4",
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=False
        )
    except Exception as e:
        print(f"Fehler bei Verbindung zur MariaDB: {e}")
        print("Tipp: Wurde init_mariadb.sql bereits auf dem MariaDB-Server ausgeführt?")
        sys.exit(1)

    maria_cursor = maria_conn.cursor()

    try:
        # A. Gerichte migrieren
        sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='dishes'")
        if sqlite_cursor.fetchone():
            sqlite_cursor.execute("SELECT id, name, category, effort, created_at FROM dishes")
            dishes = sqlite_cursor.fetchall()
            print(f"Gefundene Gerichte in SQLite: {len(dishes)}")

            for d in dishes:
                maria_cursor.execute("""
                    INSERT INTO dishes (id, name, category, effort, created_at)
                    VALUES (%s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE category=VALUES(category), effort=VALUES(effort)
                """, (d["id"], d["name"], d["category"], d["effort"], d["created_at"]))
            print("Gerichte erfolgreich nach MariaDB übertragen.")

        # B. Rezepte migrieren
        sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='recipes'")
        if sqlite_cursor.fetchone():
            sqlite_cursor.execute("""
                SELECT id, title, category, chapter_number, effort, prep_time, cook_time, portions, ingredients, instructions, notes, source, image_url, created_at, updated_at
                FROM recipes
            """)
            recipes = sqlite_cursor.fetchall()
            print(f"Gefundene Rezepte in SQLite: {len(recipes)}")

            for r in recipes:
                source_val = r["source"] if "source" in r.keys() and r["source"] else ""
                maria_cursor.execute("""
                    INSERT INTO recipes (id, title, category, chapter_number, effort, prep_time, cook_time, portions, ingredients, instructions, notes, source, image_url, created_at, updated_at)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE 
                        title=VALUES(title), category=VALUES(category), chapter_number=VALUES(chapter_number),
                        effort=VALUES(effort), prep_time=VALUES(prep_time), cook_time=VALUES(cook_time),
                        portions=VALUES(portions), ingredients=VALUES(ingredients), instructions=VALUES(instructions),
                        notes=VALUES(notes), source=VALUES(source), image_url=VALUES(image_url)
                """, (
                    r["id"], r["title"], r["category"], r["chapter_number"], r["effort"],
                    r["prep_time"], r["cook_time"], r["portions"], r["ingredients"],
                    r["instructions"], r["notes"], source_val, r["image_url"], r["created_at"], r["updated_at"]
                ))
            print("Rezepte erfolgreich nach MariaDB übertragen.")

        maria_conn.commit()
        print("\n✅ Migration erfolgreich abgeschlossen!")
        print("Um die Anwendung auf MariaDB umzustellen, setze in der .env-Datei:")
        print("DB_ENGINE=mariadb")

    except Exception as e:
        maria_conn.rollback()
        print(f"Fehler während der Migration: {e}")
        raise
    finally:
        sqlite_conn.close()
        maria_conn.close()

if __name__ == "__main__":
    migrate()