#!/usr/bin/env python3
"""
Script de création de la base de données TIM CASH étendue
Inclut toutes les tables principales + tables avancées
"""

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 extended_models import *
from database import Base, DATABASE_URL
import uuid
from dotenv import load_dotenv

# Charger les variables d'environnement
load_dotenv()

def create_extended_database():
    """Créer la base de données complète avec toutes les tables"""
    print("🚀 Création de la base de données TIM CASH étendue...")
    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}")
                return False
    elif "mysql" in DATABASE_URL:
        print("🗄️ Utilisation de MySQL - Suppression des tables existantes...")
        try:
            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}")
            return False
    
    # Créer le moteur de base de données
    engine = create_engine(DATABASE_URL)
    
    # Créer toutes les tables (principales + étendues)
    Base.metadata.create_all(bind=engine)
    print("✅ Toutes les tables créées avec succès")
    
    # Lister les tables créées
    inspector = engine.dialect.get_table_names(engine.connect())
    print(f"📋 Tables créées ({len(inspector)}):")
    for table in sorted(inspector):
        print(f"   - {table}")
    
    # Créer une session
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    db = SessionLocal()
    
    try:
        print("\n📊 Création des données de base...")
        
        # 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
        )
        
        mali = Country(
            id=str(uuid.uuid4()),
            name="Mali",
            code="ML",
            currency="FCFA",
            currency_symbol="FCFA",
            flag="🇲🇱",
            is_active=True
        )
        
        db.add_all([cote_ivoire, senegal, mali])
        db.commit()
        
        # 2. Créer les villes
        print("🏙️ Création des villes...")
        cities = [
            # Côte d'Ivoire
            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="San Pedro", country_id=cote_ivoire.id, branch_code="TIM-CI-SPE", is_active=True),
            # Sénégal
            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),
            City(id=str(uuid.uuid4()), name="Saint-Louis", country_id=senegal.id, branch_code="TIM-SN-STL", is_active=True),
            # Mali
            City(id=str(uuid.uuid4()), name="Bamako", country_id=mali.id, branch_code="TIM-ML-BAM", is_active=True),
            City(id=str(uuid.uuid4()), name="Sikasso", country_id=mali.id, branch_code="TIM-ML-SIK", 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=1,
            balance=1000000000.0,  # 1 Milliard FCFA
            total_credits=1000000000.0,
            total_debits=0.0,
            total_commissions=0.0,
        )
        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",
            hashed_password="$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
        )
        
        # Utilisateurs de test
        users_data = [
            {
                "username": "marie.kouassi",
                "email": "marie.kouassi@timcash.ci",
                "full_name": "Marie Kouassi",
                "phone": "+225 07 11 22 33 44",
                "type": TimAccountType.TIM_MAXI,
                "balance": 2847000.0
            },
            {
                "username": "jean.baptiste",
                "email": "jean.baptiste@timcash.ci",
                "full_name": "Jean Baptiste",
                "phone": "+225 07 55 66 77 88",
                "type": TimAccountType.TIM_MINI,
                "balance": 125000.0
            },
            {
                "username": "fatou.diallo",
                "email": "fatou.diallo@timcash.ci",
                "full_name": "Fatou Diallo",
                "phone": "+225 07 99 88 77 66",
                "type": TimAccountType.TIM_BUSINESS,
                "balance": 5500000.0
            },
            {
                "username": "amadou.traore",
                "email": "amadou.traore@timcash.ci",
                "full_name": "Amadou Traoré",
                "phone": "+223 70 11 22 33 44",
                "type": TimAccountType.TIM_MAXI,
                "balance": 1200000.0
            }
        ]
        
        all_users = [admin_user]
        
        for user_data in users_data:
            user = User(
                id=str(uuid.uuid4()),
                username=user_data["username"],
                email=user_data["email"],
                hashed_password="$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj3QJflLxQjm",  # password123
                full_name=user_data["full_name"],
                phone=user_data["phone"],
                is_admin=False,
                is_active=True,
                tim_account_type=user_data["type"]
            )
            all_users.append(user)
        
        for user in all_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=15000000.0,  # 15M FCFA
            max_balance=None,  # Illimité pour TIM_BUSINESS
            is_active=True,
            nfc_enabled=True,
            currency="FCFA"
        )
        db.add(admin_wallet)
        
        # Portefeuilles utilisateurs
        for i, user in enumerate(all_users[1:], 1):
            user_data = users_data[i-1]
            max_balance = None
            if user.tim_account_type == TimAccountType.TIM_MINI:
                max_balance = 500000.0
            elif user.tim_account_type == TimAccountType.TIM_MAXI:
                max_balance = 12000000.0
            
            wallet = Wallet(
                id=str(uuid.uuid4()),
                user_id=user.id,
                balance=user_data["balance"],
                max_balance=max_balance,
                is_active=True,
                nfc_enabled=user.tim_account_type != TimAccountType.TIM_MINI,
                currency="FCFA"
            )
            db.add(wallet)
        
        db.commit()
        
        # 6. Créer les profils utilisateurs étendus
        print("👤 Création des profils utilisateurs...")
        abidjan_city = cities[0]  # Abidjan
        
        for user in all_users:
            profile = UserProfile(
                id=str(uuid.uuid4()),
                user_id=user.id,
                date_of_birth=datetime(1990, 1, 1) if not user.is_admin else datetime(1985, 5, 15),
                gender="M" if "jean" in user.username.lower() or "amadou" in user.username.lower() else "F",
                address=f"123 Rue de la Paix, {abidjan_city.name}",
                city_id=abidjan_city.id,
                postal_code="00225",
                occupation="Entrepreneur" if user.tim_account_type == TimAccountType.TIM_BUSINESS else "Employé",
                monthly_income=500000.0 if user.tim_account_type == TimAccountType.TIM_MINI else 1500000.0,
                emergency_contact_name="Contact Urgence",
                emergency_contact_phone="+225 07 00 00 00 00",
                preferred_language="fr",
                timezone="Africa/Abidjan",
                kyc_status=KYCStatus.APPROVED if user.is_admin else KYCStatus.PENDING,
                kyc_level=3 if user.is_admin else 1,
                risk_score=0.1 if user.is_admin else 0.5
            )
            db.add(profile)
        
        db.commit()
        
        # 7. 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()
        
        # 8. Créer des marchands d'exemple
        print("🏪 Création des marchands...")
        merchants_data = [
            {
                "business_name": "Boutique Centrale",
                "category": MerchantCategory.RETAIL,
                "description": "Boutique générale au centre-ville",
                "commission_rate": 2.5
            },
            {
                "business_name": "Restaurant Le Palmier",
                "category": MerchantCategory.RESTAURANT,
                "description": "Restaurant traditionnel ivoirien",
                "commission_rate": 3.0
            },
            {
                "business_name": "Pharmacie Santé Plus",
                "category": MerchantCategory.PHARMACY,
                "description": "Pharmacie moderne avec service 24h",
                "commission_rate": 1.5
            }
        ]
        
        for i, merchant_data in enumerate(merchants_data):
            user = all_users[i + 1]  # Utiliser les utilisateurs non-admin
            merchant = Merchant(
                id=str(uuid.uuid4()),
                user_id=user.id,
                business_name=merchant_data["business_name"],
                business_registration=f"REG-{str(uuid.uuid4())[:8].upper()}",
                category=merchant_data["category"],
                description=merchant_data["description"],
                address=f"Avenue de la République, {abidjan_city.name}",
                city_id=abidjan_city.id,
                latitude=5.3364 + (i * 0.01),
                longitude=-4.0267 + (i * 0.01),
                phone=f"+225 07 {20+i}{20+i} {30+i}{30+i} {40+i}{40+i}",
                email=f"contact@{merchant_data['business_name'].lower().replace(' ', '')}.ci",
                is_verified=True,
                is_active=True,
                commission_rate=merchant_data["commission_rate"],
                total_transactions=50 + (i * 20),
                total_volume=1000000.0 + (i * 500000.0),
                rating=4.2 + (i * 0.2),
                review_count=15 + (i * 5)
            )
            db.add(merchant)
        
        db.commit()
        
        # 9. Créer des produits
        print("📦 Création des produits...")
        products_data = [
            {"name": "Crédit Orange", "type": ProductType.AIRTIME, "price": 1000.0},
            {"name": "Forfait Internet 1GB", "type": ProductType.DATA_BUNDLE, "price": 2500.0},
            {"name": "Facture CIE", "type": ProductType.ELECTRICITY, "price": 15000.0},
            {"name": "Assurance Auto", "type": ProductType.INSURANCE, "price": 50000.0},
        ]
        
        merchants = db.query(Merchant).all()
        for merchant in merchants:
            for product_data in products_data:
                product = Product(
                    id=str(uuid.uuid4()),
                    merchant_id=merchant.id,
                    name=product_data["name"],
                    description=f"{product_data['name']} - Service de qualité",
                    product_type=product_data["type"],
                    price=product_data["price"],
                    currency="FCFA",
                    is_active=True,
                    stock_quantity=100,
                    min_purchase=product_data["price"],
                    max_purchase=product_data["price"] * 10,
                    commission_rate=1.0,
                    total_sales=10,
                    total_revenue=product_data["price"] * 10
                )
                db.add(product)
        
        db.commit()
        
        # 10. Créer des configurations système
        print("⚙️ Création des configurations système...")
        system_configs = [
            {"key": "max_daily_transaction_limit", "value": "1000000", "data_type": "float", "category": "limits"},
            {"key": "min_transfer_amount", "value": "100", "data_type": "float", "category": "limits"},
            {"key": "max_transfer_amount", "value": "500000", "data_type": "float", "category": "limits"},
            {"key": "default_commission_rate", "value": "2.0", "data_type": "float", "category": "rates"},
            {"key": "maintenance_mode", "value": "false", "data_type": "boolean", "category": "features"},
            {"key": "nfc_enabled", "value": "true", "data_type": "boolean", "category": "features"},
            {"key": "max_login_attempts", "value": "5", "data_type": "integer", "category": "security"},
        ]
        
        for config_data in system_configs:
            config = SystemConfiguration(
                id=str(uuid.uuid4()),
                key=config_data["key"],
                value=config_data["value"],
                data_type=config_data["data_type"],
                category=config_data["category"],
                description=f"Configuration pour {config_data['key']}",
                is_sensitive=False,
                updated_by=admin_user.id
            )
            db.add(config)
        
        db.commit()
        
        # 11. Créer des feature flags
        print("🚩 Création des feature flags...")
        feature_flags = [
            {"name": "new_dashboard", "description": "Nouveau tableau de bord", "enabled": True, "rollout": 100.0},
            {"name": "advanced_analytics", "description": "Analytics avancées", "enabled": False, "rollout": 0.0},
            {"name": "loyalty_program", "description": "Programme de fidélité", "enabled": True, "rollout": 50.0},
            {"name": "merchant_portal", "description": "Portail marchand", "enabled": True, "rollout": 100.0},
        ]
        
        for flag_data in feature_flags:
            flag = FeatureFlag(
                id=str(uuid.uuid4()),
                name=flag_data["name"],
                description=flag_data["description"],
                is_enabled=flag_data["enabled"],
                rollout_percentage=flag_data["rollout"],
                target_users=[],
                target_countries=["CI", "SN", "ML"],
                created_by=admin_user.id
            )
            db.add(flag)
        
        db.commit()
        
        print("✅ Base de données étendue créée avec succès !")
        
        # Statistiques finales
        stats = {
            "Utilisateurs": db.query(User).count(),
            "Portefeuilles": db.query(Wallet).count(),
            "Profils": db.query(UserProfile).count(),
            "Pays": db.query(Country).count(),
            "Villes": db.query(City).count(),
            "Marchands": db.query(Merchant).count(),
            "Produits": db.query(Product).count(),
            "Taux commission": db.query(CommissionRate).count(),
            "Configurations": db.query(SystemConfiguration).count(),
            "Feature flags": db.query(FeatureFlag).count(),
            "Stock admin": f"{admin_stock.balance:,.0f} FCFA"
        }
        
        print("\n📊 Résumé des données créées:")
        for key, value in stats.items():
            print(f"   - {key}: {value}")
        
        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)")
        print("   - amadou.traore@timcash.ci / password123 (TIM_MAXI)")
        
        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 = create_extended_database()
    if success:
        print("\n🎉 Création de la base de données étendue réussie !")
        print("Vous pouvez maintenant démarrer le serveur backend.")
    else:
        print("\n💥 Échec de la création de la base de données étendue.")
        sys.exit(1)
