#!/usr/bin/env python3
"""
Importer toutes les transactions de l'application mobile (SQLite) vers MySQL
Pour affichage dans la page admin
"""

import sqlite3
import pymysql
import uuid
from datetime import datetime
import bcrypt

def import_mobile_transactions():
    """Importer les transactions depuis SQLite vers MySQL"""
    
    print("\n📱 Import des transactions de l'application mobile")
    print("=" * 80)
    
    # Vérifier que la base SQLite existe
    sqlite_db = "timcash.db"
    try:
        sqlite_conn = sqlite3.connect(sqlite_db)
        sqlite_cursor = sqlite_conn.cursor()
    except Exception as e:
        print(f"❌ Impossible d'ouvrir {sqlite_db}: {e}")
        return False
    
    # Connexion MySQL
    mysql_conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='timcash',
        password='timcash123',
        database='timcash_db',
        charset='utf8mb4'
    )
    mysql_cursor = mysql_conn.cursor()
    
    try:
        # 1. Compter les transactions dans SQLite
        sqlite_cursor.execute("SELECT COUNT(*) FROM transactions")
        sqlite_tx_count = sqlite_cursor.fetchone()[0]
        
        print(f"\n📊 Transactions dans SQLite: {sqlite_tx_count}")
        
        if sqlite_tx_count == 0:
            print("⚠️  Aucune transaction à importer")
            return True
        
        # 2. Récupérer les ID de pays (Côte d'Ivoire)
        mysql_cursor.execute("SELECT id FROM countries WHERE code = 'CI'")
        country_result = mysql_cursor.fetchone()
        if not country_result:
            print("❌ Côte d'Ivoire non trouvée!")
            return False
        country_id = country_result[0]
        
        # 3. Importer les utilisateurs d'abord
        print("\n👥 Import des utilisateurs...")
        sqlite_cursor.execute("""
            SELECT id, email, username, full_name, last_name, phone, 
                   hashed_password, is_active, is_admin, tim_account_type, 
                   created_at, genre, date_naissance, photo_profile, 
                   photo_recto, photo_verso, otp_secret, commission_payee, updated_at
            FROM users
        """)
        
        users = sqlite_cursor.fetchall()
        users_imported = 0
        users_skipped = 0
        
        for user in users:
            (user_id, email, username, full_name, last_name, phone, 
             hashed_password, is_active, is_admin, tim_account_type, 
             created_at, genre, date_naissance, photo_profile, 
             photo_recto, photo_verso, otp_secret, commission_payee, updated_at) = user
            
            # Vérifier si l'utilisateur existe déjà
            mysql_cursor.execute("SELECT id FROM users WHERE email = %s", (email,))
            if mysql_cursor.fetchone():
                users_skipped += 1
                continue
            
            try:
                # Insérer l'utilisateur
                mysql_cursor.execute("""
                    INSERT INTO users (
                        id, email, username, full_name, last_name, phone,
                        hashed_password, is_active, is_admin, tim_account_type,
                        created_at, genre, date_naissance, photo_profile,
                        photo_recto, photo_verso, otp_secret, commission_payee,
                        updated_at, country_id
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    )
                """, (
                    user_id, email, username, full_name, last_name, phone,
                    hashed_password, is_active, is_admin, tim_account_type,
                    created_at, genre, date_naissance, photo_profile,
                    photo_recto, photo_verso, otp_secret, commission_payee,
                    updated_at, country_id
                ))
                users_imported += 1
            except Exception as e:
                print(f"   ⚠️  Erreur pour {email}: {e}")
                users_skipped += 1
        
        mysql_conn.commit()
        print(f"   ✅ {users_imported} utilisateurs importés")
        print(f"   ⚪ {users_skipped} utilisateurs déjà existants")
        
        # 4. Importer les portefeuilles
        print("\n💳 Import des portefeuilles...")
        sqlite_cursor.execute("""
            SELECT id, user_id, balance, max_balance, is_active,
                   nfc_enabled, currency, created_at, updated_at
            FROM wallets
        """)
        
        wallets = sqlite_cursor.fetchall()
        wallets_imported = 0
        wallets_skipped = 0
        
        for wallet in wallets:
            (wallet_id, user_id, balance, max_balance, is_active,
             nfc_enabled, currency, created_at, updated_at) = wallet
            
            # Vérifier si le portefeuille existe
            mysql_cursor.execute("SELECT id FROM wallets WHERE user_id = %s", (user_id,))
            if mysql_cursor.fetchone():
                wallets_skipped += 1
                continue
            
            try:
                mysql_cursor.execute("""
                    INSERT INTO wallets (
                        id, user_id, balance, max_balance, is_active,
                        nfc_enabled, currency, created_at, updated_at
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    wallet_id, user_id, balance, max_balance, is_active,
                    nfc_enabled, currency, created_at, updated_at
                ))
                wallets_imported += 1
            except Exception as e:
                print(f"   ⚠️  Erreur pour wallet {wallet_id}: {e}")
                wallets_skipped += 1
        
        mysql_conn.commit()
        print(f"   ✅ {wallets_imported} portefeuilles importés")
        print(f"   ⚪ {wallets_skipped} portefeuilles déjà existants")
        
        # 5. Importer les transactions
        print("\n💸 Import des transactions...")
        sqlite_cursor.execute("""
            SELECT id, user_id, transaction_type, amount, commission_amount,
                   commission_rate, status, description, reference,
                   external_reference, payment_method, merchant_name,
                   location, device_info, created_at, completed_at
            FROM transactions
        """)
        
        transactions = sqlite_cursor.fetchall()
        tx_imported = 0
        tx_skipped = 0
        tx_errors = 0
        
        for transaction in transactions:
            (tx_id, user_id, tx_type, amount, commission_amount,
             commission_rate, status, description, reference,
             external_reference, payment_method, merchant_name,
             location, device_info, created_at, completed_at) = transaction
            
            # Vérifier si la transaction existe
            mysql_cursor.execute("SELECT id FROM transactions WHERE id = %s", (tx_id,))
            if mysql_cursor.fetchone():
                tx_skipped += 1
                continue
            
            # Vérifier que l'utilisateur existe
            mysql_cursor.execute("SELECT id FROM users WHERE id = %s", (user_id,))
            if not mysql_cursor.fetchone():
                print(f"   ⚠️  Utilisateur {user_id} non trouvé pour transaction {tx_id}")
                tx_errors += 1
                continue
            
            try:
                mysql_cursor.execute("""
                    INSERT INTO transactions (
                        id, user_id, transaction_type, amount, commission_amount,
                        commission_rate, status, description, reference,
                        external_reference, payment_method, merchant_name,
                        location, device_info, created_at, completed_at
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    )
                """, (
                    tx_id, user_id, tx_type, amount, commission_amount,
                    commission_rate, status, description, reference,
                    external_reference, payment_method, merchant_name,
                    location, device_info, created_at, completed_at
                ))
                tx_imported += 1
            except Exception as e:
                print(f"   ⚠️  Erreur pour transaction {tx_id}: {e}")
                tx_errors += 1
        
        mysql_conn.commit()
        print(f"   ✅ {tx_imported} transactions importées")
        print(f"   ⚪ {tx_skipped} transactions déjà existantes")
        print(f"   ❌ {tx_errors} erreurs")
        
        # 6. Importer les prêts
        print("\n🏦 Import des prêts...")
        sqlite_cursor.execute("""
            SELECT id, user_id, amount, interest_rate, penalty_rate,
                   remaining_balance, monthly_payment, total_payments,
                   payments_completed, status, due_date, days_overdue,
                   guarantee_deposit, deposit_paid, auto_approved,
                   wallet_credited, created_at, approved_at
            FROM loans
        """)
        
        loans = sqlite_cursor.fetchall()
        loans_imported = 0
        loans_skipped = 0
        
        for loan in loans:
            (loan_id, user_id, amount, interest_rate, penalty_rate,
             remaining_balance, monthly_payment, total_payments,
             payments_completed, status, due_date, days_overdue,
             guarantee_deposit, deposit_paid, auto_approved,
             wallet_credited, created_at, approved_at) = loan
            
            # Vérifier si le prêt existe
            mysql_cursor.execute("SELECT id FROM loans WHERE id = %s", (loan_id,))
            if mysql_cursor.fetchone():
                loans_skipped += 1
                continue
            
            # Vérifier que l'utilisateur existe
            mysql_cursor.execute("SELECT id FROM users WHERE id = %s", (user_id,))
            if not mysql_cursor.fetchone():
                continue
            
            try:
                # Insérer uniquement les colonnes communes
                mysql_cursor.execute("""
                    INSERT INTO loans (
                        id, user_id, amount, interest_rate, penalty_rate,
                        status, due_date, created_at, approved_at
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s
                    )
                """, (
                    loan_id, user_id, amount, interest_rate, penalty_rate,
                    status, due_date, created_at, approved_at
                ))
                loans_imported += 1
            except Exception as e:
                print(f"   ⚠️  Erreur pour prêt {loan_id}: {e}")
                loans_skipped += 1
        
        mysql_conn.commit()
        print(f"   ✅ {loans_imported} prêts importés")
        print(f"   ⚪ {loans_skipped} prêts déjà existants")
        
        # 7. Importer les notifications
        print("\n🔔 Import des notifications...")
        sqlite_cursor.execute("""
            SELECT id, user_id, title, message, type, priority,
                   is_read, action_url, amount, merchant,
                   created_at, read_at
            FROM notifications
        """)
        
        notifications = sqlite_cursor.fetchall()
        notif_imported = 0
        notif_skipped = 0
        
        for notif in notifications:
            (notif_id, user_id, title, message, notif_type, priority,
             is_read, action_url, amount, merchant,
             created_at, read_at) = notif
            
            # Vérifier si la notification existe
            mysql_cursor.execute("SELECT id FROM notifications WHERE id = %s", (notif_id,))
            if mysql_cursor.fetchone():
                notif_skipped += 1
                continue
            
            # Vérifier que l'utilisateur existe
            mysql_cursor.execute("SELECT id FROM users WHERE id = %s", (user_id,))
            if not mysql_cursor.fetchone():
                continue
            
            try:
                mysql_cursor.execute("""
                    INSERT INTO notifications (
                        id, user_id, title, message, type, priority,
                        is_read, action_url, amount, merchant,
                        created_at, read_at
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    )
                """, (
                    notif_id, user_id, title, message, notif_type, priority,
                    is_read, action_url, amount, merchant,
                    created_at, read_at
                ))
                notif_imported += 1
            except Exception as e:
                notif_skipped += 1
        
        mysql_conn.commit()
        print(f"   ✅ {notif_imported} notifications importées")
        print(f"   ⚪ {notif_skipped} notifications déjà existantes")
        
        # Statistiques finales
        print("\n" + "=" * 80)
        print("📊 RÉSUMÉ DE L'IMPORT")
        print("=" * 80)
        print(f"   👥 Utilisateurs: {users_imported} importés, {users_skipped} existants")
        print(f"   💳 Portefeuilles: {wallets_imported} importés, {wallets_skipped} existants")
        print(f"   💸 Transactions: {tx_imported} importées, {tx_skipped} existantes")
        print(f"   🏦 Prêts: {loans_imported} importés, {loans_skipped} existants")
        print(f"   🔔 Notifications: {notif_imported} importées, {notif_skipped} existantes")
        
        # Vérification finale
        mysql_cursor.execute("SELECT COUNT(*) FROM transactions")
        final_tx_count = mysql_cursor.fetchone()[0]
        
        mysql_cursor.execute("SELECT COUNT(*) FROM users")
        final_users_count = mysql_cursor.fetchone()[0]
        
        print("\n✅ État final MySQL:")
        print(f"   👥 Total utilisateurs: {final_users_count}")
        print(f"   💸 Total transactions: {final_tx_count}")
        
        print("\n🎉 Import terminé avec succès!")
        print("=" * 80 + "\n")
        
        sqlite_conn.close()
        mysql_conn.close()
        
        return True
        
    except Exception as e:
        print(f"\n❌ Erreur lors de l'import: {e}")
        import traceback
        traceback.print_exc()
        mysql_conn.rollback()
        sqlite_conn.close()
        mysql_conn.close()
        return False

if __name__ == "__main__":
    import sys
    
    print("\n📱 IMPORT DES TRANSACTIONS MOBILES - TIM CASH")
    print("=" * 80)
    print("\nCe script va importer toutes les données de l'application mobile")
    print("(depuis timcash.db SQLite) vers MySQL pour affichage dans l'admin.")
    print("\nDonnées importées:")
    print("   - Utilisateurs")
    print("   - Portefeuilles")
    print("   - Transactions")
    print("   - Prêts")
    print("   - Notifications")
    
    confirmation = input("\n❓ Continuer? (oui/non): ").strip().lower()
    
    if confirmation in ['oui', 'o', 'yes', 'y']:
        if import_mobile_transactions():
            print("\n💡 Les transactions sont maintenant visibles dans l'interface admin")
            print("   Démarrez le serveur: python -m uvicorn main:app --reload --port 8001")
            print("   Admin: http://localhost:8001/docs")
            sys.exit(0)
        else:
            sys.exit(1)
    else:
        print("\n❌ Import annulé")
        sys.exit(0)
