import os
import pymysql
from dotenv import load_dotenv

load_dotenv("/home/maxi/Projekt/wtc.waiswuaschd.de/.env")

conn = pymysql.connect(
    host=os.getenv("MARIADB_HOST", "db.waiswuaschd.de"),
    user=os.getenv("MARIADB_USER", "v2_usr_wtc_cookbook"),
    password=os.getenv("MARIADB_PASS", "XKikHf&j6Wj!rs5%A8#ZzyvE#*A1hT"),
    database=os.getenv("MARIADB_NAME", "v2_db_wtc_cookbook"),
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
    autocommit=False
)

try:
    with conn.cursor() as cur:
        # Check if column printed_at exists
        cur.execute("""
            SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = 'recipes' AND COLUMN_NAME = 'printed_at'
        """, (os.getenv("MARIADB_NAME", "v2_db_wtc_cookbook"),))
        row = cur.fetchone()
        if not row:
            print("Füge Spalte 'printed_at' zur Tabelle 'recipes' hinzu...")
            cur.execute("ALTER TABLE recipes ADD COLUMN printed_at DATETIME NULL DEFAULT NULL AFTER image_url")
            conn.commit()
            print("Spalte 'printed_at' erfolgreich hinzugefügt.")
        else:
            print("Spalte 'printed_at' existiert bereits.")

        cur.execute("DESCRIBE recipes")
        for col in cur.fetchall():
            if col["Field"] in ["id", "title", "image_url", "printed_at", "created_at"]:
                print(f"  {col['Field']}: {col['Type']} (Null: {col['Null']})")

except Exception as e:
    conn.rollback()
    print("Fehler:", e)
    raise
finally:
    conn.close()