#!/usr/bin/env python3
"""
Création directe de la base de données MySQL TIM CASH avec toutes les tables étendues
"""

import os
import sys
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import uuid
from dotenv import load_dotenv

# Charger les variables d'environnement
load_dotenv()

# Import des modèles au niveau module
try:
    from database import Base, get_db
    from models.models import *
    from extended_models import *
    print("✅ Imports des modèles réussis")
except Exception as e:
    print(f"❌ Erreur d'import: {e}")
    sys.exit(1)

def setup_mysql_database():
    """Configurer la base de données MySQL"""
    print("🛠️ Configuration de la base de données MySQL...")
    
    mysql_url = os.getenv("DATABASE_URL", "mysql+pymysql://timcash:timcash123@localhost/timcash_db")
    print(f"📡 URL MySQL: {mysql_url}")
    
    try:
        # Connexion à MySQL (sans spécifier la base)
        base_url = mysql_url.rsplit('/', 1)[0]  # Enlever le nom de la base
        engine = create_engine(base_url + "/mysql", pool_pre_ping=True)
        
        with engine.connect() as conn:
            # Créer la base de données si elle n'existe pas
            conn.execute(text("CREATE DATABASE IF NOT EXISTS timcash_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"))
            conn.commit()
            print("✅ Base de données 'timcash_db' créée/vérifiée")
            
        engine.dispose()
        return True
        
    except Exception as e:
        print(f"❌ Erreur de configuration MySQL: {e}")
        print("💡 Assurez-vous que MySQL est démarré et accessible")
        return False

def create_mysql_tables():
    """Créer toutes les tables MySQL"""
    print("\n🏗️ Création des tables MySQL...")
    
    mysql_url = os.getenv("DATABASE_URL", "mysql+pymysql://timcash:timcash123@localhost/timcash_db")
    
    try:
        # Créer le moteur MySQL
        engine = create_engine(mysql_url, pool_pre_ping=True, echo=False)
        
        # Supprimer les tables existantes (nettoyage)
        print("🧹 Suppression des tables existantes...")
        Base.metadata.drop_all(bind=engine)
        
        # Créer toutes les tables (principales + étendues)
        print("📋 Création de toutes les tables...")
        Base.metadata.create_all(bind=engine)
        
        # Vérifier les tables créées
        from sqlalchemy import inspect
        inspector = inspect(engine)
        tables = inspector.get_table_names()
        
        print(f"✅ {len(tables)} tables créées avec succès:")
        
        # Organiser les tables par catégorie
        categories = {
            "🔐 Authentification": ["users", "user_profiles", "kyc_documents"],
            "💰 Finance": ["wallets", "transactions", "loans", "commission_rates"],
            "🌍 Géographie": ["countries", "cities"],
            "📱 NFC": ["nfc_devices"],
            "🏪 Commerce": ["merchants", "products", "product_purchases", "merchant_reviews"],
            "🎯 Marketing": ["campaigns", "campaign_participations", "loyalty_programs", "referrals", "advertisements"],
            "🎧 Support": ["support_tickets", "support_messages"],
            "📊 Analytics": ["user_sessions", "transaction_analytics", "system_metrics"],
            "🔒 Sécurité": ["security_events", "audit_logs"],
            "⚙️ Configuration": ["system_configurations", "feature_flags"]
        }
        
        for category, category_tables in categories.items():
            print(f"\n{category}:")
            for table in category_tables:
                if table in tables:
                    print(f"   ✅ {table}")
                else:
                    print(f"   ❌ {table} (manquante)")
        
        # Tables non catégorisées
        uncategorized = set(tables) - set([table for cat_tables in categories.values() for table in cat_tables])
        if uncategorized:
            print(f"\n📋 Autres tables:")
            for table in sorted(uncategorized):
                print(f"   ✅ {table}")
        
        engine.dispose()
        return True
        
    except Exception as e:
        print(f"❌ Erreur lors de la création des tables: {e}")
        import traceback
        traceback.print_exc()
        return False

def populate_initial_data():
    """Peupler la base avec des données initiales"""
    print("\n📦 Peuplement avec des données initiales...")
    
    mysql_url = os.getenv("DATABASE_URL", "mysql+pymysql://timcash:timcash123@localhost/timcash_db")
    
    try:
        engine = create_engine(mysql_url, pool_pre_ping=True)
        SessionLocal = sessionmaker(bind=engine)
        db = SessionLocal()
        
        # 1. Créer les pays
        print("🌍 Création des pays...")
        countries_data = [
            {
                "id": "CI",
                "name": "Côte d'Ivoire",
                "code": "CI",
                "currency": "FCFA",
                "currency_symbol": "FCFA",
                "flag": "🇨🇮",
                "is_active": True
            },
            {
                "id": "SN", 
                "name": "Sénégal",
                "code": "SN",
                "currency": "FCFA",
                "currency_symbol": "FCFA",
                "flag": "🇸🇳",
                "is_active": True
            },
            {
                "id": "ML",
                "name": "Mali", 
                "code": "ML",
                "currency": "FCFA",
                "currency_symbol": "FCFA",
                "flag": "🇲🇱",
                "is_active": True
            }
        ]
        
        for country_data in countries_data:
            country = Country(**country_data)
            db.merge(country)
        
        # 2. Créer les villes
        print("🏙️ Création des villes...")
        cities_data = [
            # Côte d'Ivoire
            {"id": "ABJ", "name": "Abidjan", "country_id": "CI", "branch_code": "TIM-CI-ABJ", "is_active": True},
            {"id": "YAM", "name": "Yamoussoukro", "country_id": "CI", "branch_code": "TIM-CI-YAM", "is_active": True},
            {"id": "BKE", "name": "Bouaké", "country_id": "CI", "branch_code": "TIM-CI-BKE", "is_active": True},
            # Sénégal
            {"id": "DKR", "name": "Dakar", "country_id": "SN", "branch_code": "TIM-SN-DKR", "is_active": True},
            {"id": "THI", "name": "Thiès", "country_id": "SN", "branch_code": "TIM-SN-THI", "is_active": True},
            # Mali
            {"id": "BKO", "name": "Bamako", "country_id": "ML", "branch_code": "TIM-ML-BKO", "is_active": True}
        ]
        
        for city_data in cities_data:
            city = City(**city_data)
            db.merge(city)
        
        # 3. Créer les taux de commission
        print("💰 Configuration des taux de commission...")
        commission_rates_data = [
            {
                "id": "recharge_cinetpay",
                "name": "Recharge CinetPay",
                "type": "percentage",
                "value": 0.5,
                "category": "recharge_cinetpay",
                "description": "Commission sur les recharges CinetPay",
                "is_active": True
            },
            {
                "id": "recharge_business", 
                "name": "Recharge TIM BUSINESS",
                "type": "percentage",
                "value": 2.0,
                "category": "recharge_business", 
                "description": "Commission pour les recharges effectuées par TIM BUSINESS",
                "is_active": True
            },
            {
                "id": "nfc_transaction",
                "name": "Transaction NFC",
                "type": "fixed",
                "value": 25.0,
                "category": "nfc_transaction",
                "description": "Frais par transaction NFC",
                "is_active": True
            },
            {
                "id": "loan_interest",
                "name": "Intérêts Prêt",
                "type": "percentage", 
                "value": 6.0,
                "category": "loan_interest",
                "description": "Taux d'intérêt sur les prêts SOS",
                "is_active": True
            },
            {
                "id": "loan_penalty",
                "name": "Pénalité Prêt",
                "type": "percentage",
                "value": 15.0, 
                "category": "loan_penalty",
                "description": "Pénalité en cas de retard de remboursement",
                "is_active": True
            }
        ]
        
        for rate_data in commission_rates_data:
            rate = CommissionRate(**rate_data)
            db.merge(rate)
        
        # 4. Créer un utilisateur admin
        print("👤 Création de l'utilisateur administrateur...")
        from passlib.context import CryptContext
        pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
        
        admin_user = User(
            id="admin",
            email="admin@timcash.ci",
            hashed_password=pwd_context.hash("admin123"),
            full_name="Administrateur TIM CASH",
            phone="+225 07 00 00 00 00",
            is_active=True,
            is_admin=True,
            tim_account_type="TIM_BUSINESS",
            country_id="CI",
            city_id="ABJ"
        )
        db.merge(admin_user)
        
        # 5. Créer le wallet admin
        print("💼 Création du wallet administrateur...")
        admin_wallet = Wallet(
            id="wallet_admin",
            user_id="admin",
            balance=847000000.0,  # 847M FCFA
            currency="FCFA",
            is_active=True
        )
        db.merge(admin_wallet)
        
        # 6. Configurations système
        print("⚙️ Configuration du système...")
        system_configs = [
            {
                "id": "platform_name",
                "key": "platform_name",
                "value": "TIM CASH",
                "description": "Nom de la plateforme",
                "is_active": True
            },
            {
                "id": "default_currency", 
                "key": "default_currency",
                "value": "FCFA",
                "description": "Devise par défaut",
                "is_active": True
            },
            {
                "id": "max_loan_amount",
                "key": "max_loan_amount", 
                "value": "10000",
                "description": "Montant maximum des prêts SOS",
                "is_active": True
            }
        ]
        
        for config_data in system_configs:
            config = SystemConfiguration(**config_data)
            db.merge(config)
        
        db.commit()
        db.close()
        
        print("✅ Données initiales créées avec succès")
        return True
        
    except Exception as e:
        print(f"❌ Erreur lors du peuplement: {e}")
        import traceback
        traceback.print_exc()
        return False

def main():
    """Fonction principale"""
    print("🚀 TIM CASH - Configuration MySQL Complète")
    print("=" * 50)
    
    # Étape 1: Configurer MySQL
    if not setup_mysql_database():
        print("\n💥 Échec de la configuration MySQL")
        sys.exit(1)
    
    # Étape 2: Créer les tables
    if not create_mysql_tables():
        print("\n💥 Échec de la création des tables")
        sys.exit(1)
    
    # Étape 3: Peupler avec des données initiales
    if not populate_initial_data():
        print("\n💥 Échec du peuplement initial")
        sys.exit(1)
    
    print("\n🎉 Configuration MySQL complète réussie !")
    print("\n📋 Prochaines étapes:")
    print("   1. Testez la configuration: python test_extended_tables.py")
    print("   2. Démarrez le serveur: python -m uvicorn main:app --reload --port 8001")
    print("   3. Accédez à l'API: http://localhost:8001/docs")
    print("   4. Connectez-vous avec: admin@timcash.ci / admin123")

if __name__ == "__main__":
    main()
