#!/usr/bin/env python3
"""
Script pour vérifier pourquoi le Pack Platinum de bruno n'a pas été enregistré
et créer une table d'audit pour les achats de packs cash.
"""

import mysql.connector
from python.db_config import DB_CONFIG

def main():
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)
    
    print("=" * 70)
    print("AUDIT DES ACHATS DE PACKS CASH")
    print("=" * 70)
    
    # 1. Vérifier les tables d'audit existantes
    print("\n1. TABLES D'AUDIT EXISTANTES:")
    cursor.execute("""
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'RollerLogic' AND TABLE_NAME LIKE '%audit%'
    """)
    tables = cursor.fetchall()
    if tables:
        for table in tables:
            print(f"   - {table['TABLE_NAME']}")
    else:
        print("   ⚠️ Aucune table d'audit trouvée")
    
    # 2. Vérifier s'il existe une table d'audit ou de logs
    print("\n2. RECHERCHE D'UNE TABLE POUR ENREGISTRER LES ACHATS:")
    cursor.execute("""
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'RollerLogic' AND TABLE_NAME LIKE '%transaction%'
    """)
    tables = cursor.fetchall()
    if tables:
        for table in tables:
            print(f"   - {table['TABLE_NAME']}")
    else:
        print("   ⚠️ Aucune table de transactions trouvée")
    
    # 3. Créer une table d'audit pour les achats de packs cash
    print("\n3. CRÉATION DE LA TABLE D'AUDIT (user_cash_pack_purchases):")
    try:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_cash_pack_purchases (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                pack_id VARCHAR(50) NOT NULL,
                pack_name VARCHAR(100) NOT NULL,
                price_eur DECIMAL(10,2) NOT NULL,
                items_hints INT DEFAULT 0,
                items_undos INT DEFAULT 0,
                items_replays INT DEFAULT 0,
                bonus_points INT DEFAULT 0,
                stripe_session_id VARCHAR(255) DEFAULT NULL,
                stripe_payment_intent_id VARCHAR(255) DEFAULT NULL,
                status VARCHAR(50) DEFAULT 'pending',
                purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                processed_at TIMESTAMP NULL,
                INDEX idx_user (user_id),
                INDEX idx_pack (pack_id),
                INDEX idx_stripe_session (stripe_session_id),
                INDEX idx_status (status),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """)
        conn.commit()
        print("   ✓ Table créée avec succès")
    except Exception as e:
        if "already exists" in str(e):
            print("   ✓ Table existe déjà")
        else:
            print(f"   ❌ Erreur: {e}")
    
    # 4. Vérifier les tables de paiement existantes
    print("\n4. TABLES DE PAIEMENT EXISTANTES:")
    cursor.execute("""
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'RollerLogic'
        ORDER BY TABLE_NAME
    """)
    all_tables = cursor.fetchall()
    payment_related = [t for t in all_tables if any(x in t['TABLE_NAME'] for x in ['pack', 'payment', 'wallet', 'stripe', 'user'])]
    for table in payment_related:
        print(f"   - {table['TABLE_NAME']}")
    
    # 5. Ajouter un enregistrement d'audit pour le paiement de bruno
    print("\n5. ENREGISTREMENT DU PAIEMENT PLATINUM POUR BRUNO:")
    try:
        user_id = 1  # bruno
        pack_id = "platinum"
        pack_name = "Pack Platinum"
        price_eur = 6.99
        
        cursor.execute("""
            INSERT INTO user_cash_pack_purchases 
            (user_id, pack_id, pack_name, price_eur, items_hints, items_undos, items_replays, bonus_points, status, processed_at)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (user_id, pack_id, pack_name, price_eur, 24, 16, 4, 180, 'completed'))
        conn.commit()
        print(f"   ✓ Enregistrement ajouté: {pack_name} ({price_eur}€)")
    except Exception as e:
        print(f"   ❌ Erreur: {e}")
        conn.rollback()
    
    # 6. Afficher tous les achats de packs cash
    print("\n6. TOUS LES ACHATS DE PACKS CASH:")
    try:
        cursor.execute("""
            SELECT 
                id, user_id, pack_name, price_eur, status, purchased_at, processed_at
            FROM user_cash_pack_purchases
            ORDER BY purchased_at DESC
            LIMIT 10
        """)
        purchases = cursor.fetchall()
        if purchases:
            for p in purchases:
                status_emoji = "✓" if p['status'] == 'completed' else "⏳"
                print(f"   {status_emoji} ID:{p['id']} | User:{p['user_id']} | {p['pack_name']} ({p['price_eur']}€)")
                print(f"      Acheté: {p['purchased_at']} | Traité: {p['processed_at']}")
        else:
            print("   ⚠️ Aucun achat enregistré")
    except Exception as e:
        print(f"   ⚠️ Pas de table user_cash_pack_purchases: {e}")
    
    print("\n" + "=" * 70)
    conn.close()

if __name__ == "__main__":
    main()