#!/usr/bin/env python3
"""
Script de recréation complète de la base de données TIM CASH
Compatible SQLite et MySQL
"""
import os
import sys
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from models.models import *
from database import Base, DATABASE_URL
import uuid
from dotenv import load_dotenv

# Charger les variables d'environnement
load_dotenv()

def recreate_database():
    """Recréer complètement la base de données"""
    print("🔄 Recréation de la base de données TIM CASH...")
    print(f"📡 URL de connexion: {DATABASE_URL}")
    
    # Gestion spécifique selon le type de base de données
    if "sqlite" in DATABASE_URL:
        # Supprimer l'ancienne base SQLite si elle existe
        db_file = "timcash.db"
        if os.path.exists(db_file):
            try:
                os.remove(db_file)
                print("✅ Ancienne base de données SQLite supprimée")
            except Exception as e:
                print(f"⚠️  Impossible de supprimer l'ancienne base: {e}")
                print("   Veuillez arrêter le serveur backend et relancer ce script")
                return False
    elif "mysql" in DATABASE_URL:
        print("🗄️ Utilisation de MySQL - Suppression des tables existantes...")
        try:
            # Créer une connexion temporaire pour nettoyer
            temp_engine = create_engine(DATABASE_URL)
            Base.metadata.drop_all(bind=temp_engine)
            print("✅ Tables MySQL supprimées")
            temp_engine.dispose()
        except Exception as e:
            print(f"⚠️  Erreur lors de la suppression des tables MySQL: {e}")
            print("   Assurez-vous que MySQL est démarré et accessible")
            return False
    
    # Créer le moteur de base de données
    engine = create_engine(DATABASE_URL)
    
    # Créer toutes les tables
    Base.metadata.create_all(bind=engine)
    print("✅ Tables créées avec succès")
    
    # Créer une session
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    db = SessionLocal()
    
    try:
        # 1. Créer les pays
        print("📍 Création des pays...")
        cote_ivoire = Country(
            id=str(uuid.uuid4()),
            name="Côte d'Ivoire",
            code="CI",
            currency="FCFA",
            currency_symbol="FCFA",
            flag="🇨🇮",
            is_active=True
        )
        
        senegal = Country(
            id=str(uuid.uuid4()),
            name="Sénégal",
            code="SN", 
            currency="FCFA",
            currency_symbol="FCFA",
            flag="🇸🇳",
            is_active=True
        )
        
        db.add(cote_ivoire)
        db.add(senegal)
        db.commit()
        
        # 2. Créer les villes
        print("🏙️ Création des villes...")
        cities = [
            City(id=str(uuid.uuid4()), name="Abidjan", country_id=cote_ivoire.id, branch_code="TIM-CI-ABJ", is_active=True),
            City(id=str(uuid.uuid4()), name="Yamoussoukro", country_id=cote_ivoire.id, branch_code="TIM-CI-YAM", is_active=True),
            City(id=str(uuid.uuid4()), name="Bouaké", country_id=cote_ivoire.id, branch_code="TIM-CI-BOU", is_active=True),
            City(id=str(uuid.uuid4()), name="Dakar", country_id=senegal.id, branch_code="TIM-SN-DKR", is_active=True),
            City(id=str(uuid.uuid4()), name="Thiès", country_id=senegal.id, branch_code="TIM-SN-THI", is_active=True),
        ]
        
        for city in cities:
            db.add(city)
        db.commit()
        
        # 3. Créer le stock administrateur
        print("💰 Création du stock administrateur...")
        admin_stock = AdminStock(
            id=str(uuid.uuid4()),
            balance=847000000.0,  # 847M FCFA
            total_credits=847000000.0,
            total_debits=0.0,
            total_commissions=0.0,
            currency="FCFA"
        )
        db.add(admin_stock)
        db.commit()
        
        # 4. Créer les utilisateurs
        print("👥 Création des utilisateurs...")
        
        # Admin
        admin_user = User(
            id=str(uuid.uuid4()),
            username="admin",
            email="admin@timcash.ci",
            password_hash="$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj3QJflLxQjm",  # admin123
            full_name="Administrateur TIM CASH",
            phone="+225 07 12 34 56 78",
            is_admin=True,
            is_active=True,
            tim_account_type=TimAccountType.TIM_BUSINESS
        )
        
        # Marie Kouassi (TIM_MAXI)
        marie_user = User(
            id=str(uuid.uuid4()),
            username="marie.kouassi",
            email="marie.kouassi@timcash.ci",
            password_hash="$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj3QJflLxQjm",  # password123
            full_name="Marie Kouassi",
            phone="+225 07 11 22 33 44",
            is_admin=False,
            is_active=True,
            tim_account_type=TimAccountType.TIM_MAXI
        )
        
        # Jean Baptiste (TIM_MINI)
        jean_user = User(
            id=str(uuid.uuid4()),
            username="jean.baptiste",
            email="jean.baptiste@timcash.ci",
            password_hash="$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj3QJflLxQjm",  # password123
            full_name="Jean Baptiste",
            phone="+225 07 55 66 77 88",
            is_admin=False,
            is_active=True,
            tim_account_type=TimAccountType.TIM_MINI
        )
        
        # Fatou Diallo (TIM_BUSINESS)
        fatou_user = User(
            id=str(uuid.uuid4()),
            username="fatou.diallo",
            email="fatou.diallo@timcash.ci",
            password_hash="$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj3QJflLxQjm",  # password123
            full_name="Fatou Diallo",
            phone="+225 07 99 88 77 66",
            is_admin=False,
            is_active=True,
            tim_account_type=TimAccountType.TIM_BUSINESS
        )
        
        users = [admin_user, marie_user, jean_user, fatou_user]
        for user in users:
            db.add(user)
        db.commit()
        
        # 5. Créer les portefeuilles
        print("💳 Création des portefeuilles...")
        
        # Portefeuille Admin
        admin_wallet = Wallet(
            id=str(uuid.uuid4()),
            user_id=admin_user.id,
            balance=12847000.0,  # 12.8M FCFA
            max_balance=None,  # Illimité pour TIM_BUSINESS
            is_active=True,
            nfc_enabled=True,
            currency="FCFA"
        )
        
        # Portefeuille Marie (TIM_MAXI)
        marie_wallet = Wallet(
            id=str(uuid.uuid4()),
            user_id=marie_user.id,
            balance=2847000.0,  # 2.8M FCFA
            max_balance=12000000.0,  # 12M FCFA max
            is_active=True,
            nfc_enabled=True,
            currency="FCFA"
        )
        
        # Portefeuille Jean (TIM_MINI)
        jean_wallet = Wallet(
            id=str(uuid.uuid4()),
            user_id=jean_user.id,
            balance=125000.0,  # 125K FCFA
            max_balance=500000.0,  # 500K FCFA max
            is_active=True,
            nfc_enabled=False,
            currency="FCFA"
        )
        
        # Portefeuille Fatou (TIM_BUSINESS)
        fatou_wallet = Wallet(
            id=str(uuid.uuid4()),
            user_id=fatou_user.id,
            balance=5500000.0,  # 5.5M FCFA
            max_balance=None,  # Illimité pour TIM_BUSINESS
            is_active=True,
            nfc_enabled=True,
            currency="FCFA"
        )
        
        wallets = [admin_wallet, marie_wallet, jean_wallet, fatou_wallet]
        for wallet in wallets:
            db.add(wallet)
        db.commit()
        
        # 6. Créer les taux de commission
        print("📊 Création des taux de commission...")
        commission_rates = [
            CommissionRate(id=str(uuid.uuid4()), service_type="recharge_cinetpay", rate=0.005, description="Recharge CinetPay"),
            CommissionRate(id=str(uuid.uuid4()), service_type="nfc_withdrawal", rate=0.02, description="Retrait NFC"),
            CommissionRate(id=str(uuid.uuid4()), service_type="mobile_transfer", rate=0.01, description="Transfert mobile"),
            CommissionRate(id=str(uuid.uuid4()), service_type="pret_mobile", rate=0.06, description="Prêt mobile"),
            CommissionRate(id=str(uuid.uuid4()), service_type="loan_penalty", rate=0.15, description="Pénalité de retard"),
            CommissionRate(id=str(uuid.uuid4()), service_type="virement_bancaire", rate=500.0, description="Virement bancaire (fixe)"),
            CommissionRate(id=str(uuid.uuid4()), service_type="recharge_by_business", rate=0.01, description="Recharge par TIM BUSINESS"),
        ]
        
        for rate in commission_rates:
            db.add(rate)
        db.commit()
        
        # 7. Créer les capacités d'appareils NFC
        print("📱 Création des capacités d'appareils...")
        device_capabilities = [
            DeviceCapability(
                id=str(uuid.uuid4()),
                device_model="Samsung Galaxy A54",
                brand="Samsung",
                has_nfc=True,
                nfc_enabled=True,
                os_version="Android 13",
                user_count=1247,
                transaction_count=3456,
                success_rate=98.7,
                location="Abidjan",
                country="Côte d'Ivoire",
                city="Abidjan"
            ),
            DeviceCapability(
                id=str(uuid.uuid4()),
                device_model="iPhone 14",
                brand="Apple",
                has_nfc=True,
                nfc_enabled=False,
                os_version="iOS 17.2",
                user_count=892,
                transaction_count=2134,
                success_rate=95.2,
                location="Cocody",
                country="Côte d'Ivoire",
                city="Abidjan"
            ),
            DeviceCapability(
                id=str(uuid.uuid4()),
                device_model="Tecno Spark 10",
                brand="Tecno",
                has_nfc=False,
                nfc_enabled=False,
                os_version="Android 12",
                user_count=2156,
                transaction_count=1923,
                success_rate=89.4,
                location="Yopougon",
                country="Côte d'Ivoire",
                city="Abidjan"
            ),
            DeviceCapability(
                id=str(uuid.uuid4()),
                device_model="Xiaomi Redmi Note 12",
                brand="Xiaomi",
                has_nfc=True,
                nfc_enabled=True,
                os_version="Android 13",
                user_count=756,
                transaction_count=2876,
                success_rate=97.1,
                location="Adjamé",
                country="Côte d'Ivoire",
                city="Abidjan"
            ),
            DeviceCapability(
                id=str(uuid.uuid4()),
                device_model="Infinix Note 30",
                brand="Infinix",
                has_nfc=False,
                nfc_enabled=False,
                os_version="Android 12",
                user_count=1834,
                transaction_count=1687,
                success_rate=91.8,
                location="Marcory",
                country="Côte d'Ivoire",
                city="Abidjan"
            ),
        ]
        
        for device in device_capabilities:
            db.add(device)
        db.commit()
        
        # 8. Créer quelques transactions d'exemple
        print("💸 Création des transactions d'exemple...")
        transactions = [
            Transaction(
                id=str(uuid.uuid4()),
                user_id=marie_user.id,
                transaction_type=TransactionType.RECHARGE_CINETPAY,
                amount=50000.0,
                commission_amount=250.0,
                commission_rate=0.005,
                status=TransactionStatus.COMPLETED,
                description="Recharge CinetPay - 50,000 FCFA",
                reference=f"RELOAD-{str(uuid.uuid4())[:8]}",
                payment_method=PaymentMethod.MOBILE_MONEY,
                created_at=datetime.utcnow() - timedelta(hours=2)
            ),
            Transaction(
                id=str(uuid.uuid4()),
                user_id=jean_user.id,
                transaction_type=TransactionType.NFC_DEPOSIT,
                amount=25000.0,
                commission_amount=0.0,
                commission_rate=0.0,
                status=TransactionStatus.COMPLETED,
                description="Dépôt NFC - Boutique Centrale",
                reference=f"NFC-{str(uuid.uuid4())[:8]}",
                merchant_name="Boutique Centrale",
                location="Abidjan Centre",
                created_at=datetime.utcnow() - timedelta(hours=1)
            ),
            Transaction(
                id=str(uuid.uuid4()),
                user_id=fatou_user.id,
                transaction_type=TransactionType.MOBILE_TRANSFER,
                amount=100000.0,
                commission_amount=1000.0,
                commission_rate=0.01,
                status=TransactionStatus.COMPLETED,
                description="Transfert vers Marie Kouassi",
                reference=f"TRANSFER-{str(uuid.uuid4())[:8]}",
                created_at=datetime.utcnow() - timedelta(minutes=30)
            ),
        ]
        
        for transaction in transactions:
            db.add(transaction)
        db.commit()
        
        # 9. Créer quelques notifications
        print("🔔 Création des notifications...")
        notifications = [
            Notification(
                id=str(uuid.uuid4()),
                user_id=marie_user.id,
                title="Recharge CinetPay Réussie",
                message="Votre recharge de 50,000 FCFA a été traitée avec succès",
                type="recharge_cinetpay",
                priority=NotificationPriority.MEDIUM,
                is_read=False,
                created_at=datetime.utcnow() - timedelta(hours=2)
            ),
            Notification(
                id=str(uuid.uuid4()),
                user_id=jean_user.id,
                title="Paiement NFC Réussi",
                message="Votre paiement NFC de 25,000 FCFA chez Boutique Centrale a été traité",
                type="nfc_payment",
                priority=NotificationPriority.LOW,
                is_read=True,
                created_at=datetime.utcnow() - timedelta(hours=1)
            ),
            Notification(
                id=str(uuid.uuid4()),
                user_id=admin_user.id,
                title="Stock Admin - Niveau Optimal",
                message="Le stock administrateur est à 847M FCFA - Niveau optimal maintenu",
                type="admin_stock",
                priority=NotificationPriority.HIGH,
                is_read=False,
                created_at=datetime.utcnow() - timedelta(minutes=15)
            ),
        ]
        
        for notification in notifications:
            db.add(notification)
        db.commit()
        
        print("✅ Base de données recréée avec succès !")
        print("\n📊 Résumé des données créées:")
        print(f"   - 👥 Utilisateurs: {db.query(User).count()}")
        print(f"   - 💳 Portefeuilles: {db.query(Wallet).count()}")
        print(f"   - 🌍 Pays: {db.query(Country).count()}")
        print(f"   - 🏙️ Villes: {db.query(City).count()}")
        print(f"   - 💸 Transactions: {db.query(Transaction).count()}")
        print(f"   - 📊 Taux commission: {db.query(CommissionRate).count()}")
        print(f"   - 📱 Appareils NFC: {db.query(DeviceCapability).count()}")
        print(f"   - 🔔 Notifications: {db.query(Notification).count()}")
        print(f"   - 💰 Stock admin: {admin_stock.balance:,.0f} FCFA")
        
        print("\n👥 Comptes de test créés:")
        print("   - admin@timcash.ci / admin123 (TIM_BUSINESS)")
        print("   - marie.kouassi@timcash.ci / password123 (TIM_MAXI)")
        print("   - jean.baptiste@timcash.ci / password123 (TIM_MINI)")
        print("   - fatou.diallo@timcash.ci / password123 (TIM_BUSINESS)")
        
        return True
        
    except Exception as e:
        print(f"❌ Erreur lors de la création des données: {e}")
        db.rollback()
        return False
    finally:
        db.close()

if __name__ == "__main__":
    success = recreate_database()
    if success:
        print("\n🎉 Migration complète réussie !")
        print("Vous pouvez maintenant redémarrer le serveur backend.")
    else:
        print("\n💥 Échec de la migration.")
        sys.exit(1)
