"""
Migration : ajout de la colonne pack_type à payment_transactions
et rétro-remplissage des transactions existantes.

Exécuter AVANT le déploiement du nouveau webhook idempotent.

Usage:
    python migrate_payment_pack_type.py
    python migrate_payment_pack_type.py --dry-run    (affiche sans exécuter)
"""
import sys
import mysql.connector
from python.db_config import DB_CONFIG


def get_connection():
    """Connexion à la BDD OVH CloudDB."""
    return mysql.connector.connect(
        host=DB_CONFIG["host"],
        port=DB_CONFIG["port"],
        user=DB_CONFIG["user"],
        password=DB_CONFIG["password"],
        database=DB_CONFIG["database"],
    )


def column_exists(cursor, table: str, column: str) -> bool:
    """Vérifie si une colonne existe déjà dans une table."""
    cursor.execute(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS "
        "WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
        (DB_CONFIG["database"], table, column),
    )
    return cursor.fetchone()[0] > 0


def run_migration(dry_run: bool = False):
    conn = get_connection()
    cursor = conn.cursor()

    print(f"Connecté à {DB_CONFIG['host']}:{DB_CONFIG['port']} / {DB_CONFIG['database']}")
    print(f"Mode: {'DRY-RUN (simulation)' if dry_run else 'EXECUTION RÉELLE'}")
    print("-" * 60)

    # ── Étape 1 : Ajouter la colonne pack_type ──
    if column_exists(cursor, "payment_transactions", "pack_type"):
        print("✓ Colonne pack_type existe déjà dans payment_transactions — skip")
    else:
        sql = (
            "ALTER TABLE `payment_transactions` "
            "ADD COLUMN `pack_type` varchar(30) NOT NULL DEFAULT 'avatar' "
            "AFTER `pack_id`"
        )
        print(f"→ ALTER TABLE payment_transactions ADD COLUMN pack_type ...")
        if not dry_run:
            cursor.execute(sql)
            conn.commit()
            print("  ✓ Colonne ajoutée avec succès")
        else:
            print(f"  [DRY-RUN] SQL: {sql}")

    # ── Étape 2 : Rétro-remplir les transactions VIP existantes ──
    has_pack_type = column_exists(cursor, "payment_transactions", "pack_type")

    if not has_pack_type and dry_run:
        # En dry-run la colonne n'a pas été créée, on simule seulement
        sql = (
            "UPDATE `payment_transactions` "
            "SET `pack_type` = 'vip' "
            "WHERE `pack_id` LIKE '%%vip%%' AND `pack_type` = 'avatar'"
        )
        print(f"→ Rétro-remplissage des transactions VIP ...")
        print(f"  [DRY-RUN] SQL: {sql}")
    else:
        cursor.execute(
            "SELECT COUNT(*) FROM payment_transactions "
            "WHERE pack_id LIKE '%%vip%%' AND pack_type = 'avatar'"
        )
        vip_count = cursor.fetchone()[0]

        if vip_count == 0:
            print("✓ Aucune transaction VIP à rétro-remplir — skip")
        else:
            sql = (
                "UPDATE `payment_transactions` "
                "SET `pack_type` = 'vip' "
                "WHERE `pack_id` LIKE '%%vip%%' AND `pack_type` = 'avatar'"
            )
            print(f"→ Rétro-remplissage de {vip_count} transaction(s) VIP ...")
            if not dry_run:
                cursor.execute(sql)
                conn.commit()
                print(f"  ✓ {cursor.rowcount} ligne(s) mise(s) à jour")
            else:
                print(f"  [DRY-RUN] SQL: {sql}")

    # ── Étape 3 : Vérification finale ──
    print("-" * 60)
    print("Vérification de la structure payment_transactions :")
    cursor.execute("DESCRIBE payment_transactions")
    for row in cursor.fetchall():
        marker = " ◀ NOUVEAU" if row[0] == "pack_type" else ""
        print(f"  {row[0]:30s} {row[1]}{marker}")

    if has_pack_type or not dry_run:
        cursor.execute("SELECT COUNT(*) FROM payment_transactions")
        total = cursor.fetchone()[0]
        cursor.execute(
            "SELECT pack_type, COUNT(*) FROM payment_transactions GROUP BY pack_type"
        )
        groups = cursor.fetchall()
        print(f"\nTotal transactions : {total}")
        for pack_type, count in groups:
            print(f"  {pack_type}: {count}")
    else:
        print("\n  [DRY-RUN] Distribution par pack_type non disponible (colonne pas encore créée)")

    cursor.close()
    conn.close()
    print("\n✓ Migration terminée.")


if __name__ == "__main__":
    dry = "--dry-run" in sys.argv
    try:
        run_migration(dry_run=dry)
    except mysql.connector.Error as e:
        print(f"\n✗ Erreur MySQL : {e}", file=sys.stderr)
        sys.exit(1)
    except Exception as e:
        print(f"\n✗ Erreur : {e}", file=sys.stderr)
        sys.exit(1)