"""
TIM CASH API avec base de données SQLAlchemy
"""
import os
from fastapi import FastAPI, Form, HTTPException, Depends, status, Request
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from fastapi.middleware.cors import CORSMiddleware
from fastapi.staticfiles import StaticFiles
from pydantic import BaseModel, EmailStr, validator
from typing import Optional, List, Dict, Any
from datetime import datetime, time, timedelta
import jwt
import bcrypt
import uvicorn
from enum import Enum
import logging
import requests
import json
from slowapi import Limiter, _rate_limit_exceeded_handler
from slowapi.util import get_remote_address
from slowapi.errors import RateLimitExceeded
from slowapi.middleware import SlowAPIMiddleware
from sqlalchemy.orm import Session
import uuid
from api.base_route import api_router
from core.config import settings

# Imports de notre base de données
from models.models import  *
from schemas.cinepay_schem import CinetPayNotify
from services.users import UPLOAD_DIR
from database import get_db, SessionLocal
from models import *
from utility.auth_token import get_current_admin
from fastapi_pagination import Page, add_pagination, paginate
from celery import Celery, shared_task
# from utility.payment_api import InitPaymentWeb
from utility.transactions import cinetpay_service
from utility.transfert import cinetpay_service_transfert
# Initialize rate limiter
limiter = Limiter(key_func=get_remote_address)

# Initialize FastAPI app
def include_router(app):
    app.include_router(api_router)
    
app = FastAPI(
    title="TimCash API",
    description="Comprehensive Financial Platform API with Database",
    version="2.0.0"
)

# Add rate limiting middleware
app.state.limiter = limiter
app.add_exception_handler(RateLimitExceeded, _rate_limit_exceeded_handler)
app.add_middleware(SlowAPIMiddleware)

# # Configure logging
# logging.basicConfig(
#     level=logging.INFO,
#     format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
# )


if not os.path.exists('logs'):
   os.makedirs('logs')

logging.basicConfig(filename='logs/app.log',level=logging.INFO , format='%(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

logger = logging.getLogger(__name__)

celery = Celery(
    __name__,
    broker=settings.CELERY_BROKER_URL,
    backend=settings.CELERY_RESULT_BACKEND
)

# CORS middleware
app.add_middleware(
    CORSMiddleware,
    allow_origins=[settings.APP_BASE_WEB, settings.APP_BASE_WEB_LOCAL],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Include API routers
include_router(app)

app.mount("/uploads", StaticFiles(directory=UPLOAD_DIR), name="profile_photos")
# Security
security = HTTPBearer()
from core.config import settings
SECRET_KEY = settings.SECRET_KEY
ALGORITHM = settings.ALGORITHM
ACCESS_TOKEN_EXPIRE_MINUTES = settings.ACCESS_TOKEN_EXPIRE_MINUTES

# Enums
class UserRole(str, Enum):
    USER = "user"
    ADMIN = "admin"

class PaymentStatus(str, Enum):
    PENDING = "pending"
    COMPLETED = "completed"
    FAILED = "failed"

# Pydantic models
class UserCreate(BaseModel):
    username: str
    email: EmailStr
    password: str
    full_name: str
    phone: Optional[str] = None
    tim_account_type: Optional[str] = "TIM_MINI"

class UserLogin(BaseModel):
    email: EmailStr
    password: str

class Token(BaseModel):
    access_token: str
    refresh_token: str
    token_type: str

class WalletReloadRequest(BaseModel):
    amount: float
    payment_method: str = "cinetpay"

class TransferRequest(BaseModel):
    recipient_email: str
    amount: float
    description: Optional[str] = None

class LoanRequest(BaseModel):
    amount: float
    purpose: Optional[str] = None

class CinetPayCallbackRequest(BaseModel):
    """
    Callback simplifié pour tests
    Pour production, utiliser CinetPayCallback de schemas/transaction.py
    """
    transaction_id: str
    status: str
    amount: float  # Montant APRÈS déduction commission CinetPay
    reference: str
    signature: str
    operator_id: Optional[str] = None  # ID de l'opérateur (Wave, Orange, etc.)

# CinetPay configuration
# CINETPAY_API_KEY = "your-cinetpay-api-key"
# CINETPAY_SITE_ID = "your-site-id"
# CINETPAY_SECRET_KEY = "your-secret-key"
# CINETPAY_BASE_URL = "https://api-checkout.cinetpay.com/v2"

# Utility functions

def get_admin_user(current_user: User = Depends(get_current_admin)):
    if not current_user.is_admin:
        raise HTTPException(status_code=403, detail="Admin access required")
    return current_user

def get_user_wallet(user: User, db: Session):
    wallet = db.query(Wallet).filter(Wallet.user_id == user.id).first()
    if not wallet:
        # Créer un wallet si il n'existe pas
        wallet = Wallet(
            id=str(uuid.uuid4()),
            user_id=user.id,
            balance=0.0,
            max_balance=get_account_limit(user.tim_account_type)
        )
        db.add(wallet)
        db.commit()
        db.refresh(wallet)
    return wallet

def get_account_limit(account_type: TimAccountType):
    limits = {
        TimAccountType.TIM_MINI: 2000000.0,      # 2M FCFA
        TimAccountType.TIM_MAXI: 2000000.0,      # 2M FCFA
        TimAccountType.TIM_BUSINESS: None        # Illimité
    }
    return limits.get(account_type)

def get_admin_stock(db: Session):
    stock = db.query(AdminStock).first()
    if not stock:
        stock = AdminStock(
            balance=847000000.0,  # 847M FCFA
            total_credits=0.0,
            total_debits=0.0,
            total_commissions=0.0
        )
        db.add(stock)
        db.commit()
        db.refresh(stock)
    return stock

def init_admin_stock(db: Session):
    """Initialiser le stock admin s'il n'existe pas"""
    stock = db.query(AdminStock).first()
    if not stock:
        stock = AdminStock(
            id=str(uuid.uuid4()),
            balance=847000000.0,  # 847M FCFA
            total_credits=0.0,
            total_debits=0.0,
            total_commissions=0.0
        )
        db.add(stock)
        db.commit()
    return stock

def init_default_commissions(db: Session):
    """Initialiser les commissions par défaut si elles n'existent pas"""
    existing_commissions = db.query(CommissionRate).count()
    
    if existing_commissions == 0:
        default_commissions = [
            {
                "name": "Transfert TIM MINI/MAXI",
                "rate_type": "percentage",
                "value": 1.0,
                "category": "transfer",
                "description": "Commission de 1% sur les transferts entre TIM MINI et TIM MAXI",
                "is_active": True,
                "updated_by": "system"
            },
            {
                "name": "Commission Recharge Tim Mini/Maxi/Business",
                "rate_type": "percentage",
                "value": 3.0,
                "category": "recharge",
                "description": "Commission 3% sur recharges (1% CinetPay + 2% TimCash) - Tim Mini, Tim Maxi, Tim Business",
                "is_active": True,
                "updated_by": "system"
            },
            {
                "name": "Prêt SOS TIM MAXI",
                "rate_type": "percentage",
                "value": 5.0,
                "category": "loan_interest",
                "description": "Intérêts de 5% sur les prêts SOS pour TIM MAXI",
                "min_amount": 0,
                "max_amount": 10000,
                "is_active": True,
                "updated_by": "system"
            },
            {
                "name": "Prêt SOS TIM BUSINESS",
                "rate_type": "percentage",
                "value": 3.0,
                "category": "loan_interest",
                "description": "Intérêts de 3% sur les prêts SOS pour TIM BUSINESS",
                "min_amount": 0,
                "max_amount": 50000,
                "is_active": True,
                "updated_by": "system"
            },
            {
                "name": "Paiement NFC",
                "rate_type": "percentage",
                "value": 0.0,
                "category": "nfc_payment",
                "description": "Pas de commission sur les paiements NFC",
                "is_active": True,
                "updated_by": "system"
            }
        ]
        
        for comm_data in default_commissions:
            commission = CommissionRate(
                id=str(uuid.uuid4()),
                **comm_data
            )
            db.add(commission)
        
        db.commit()
        print(f"✅ {len(default_commissions)} commissions par défaut créées")

# Routes
@app.get("/")
async def root():
    return {
        "message": "TIM CASH API v2.0 - Database Edition",
        "status": "active",
        "database": "SQLAlchemy + SQLite",
        "timestamp": datetime.utcnow()
    }

@app.get("/admin/stock")
async def get_admin_stock_info(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    stock = get_admin_stock(db)

    # Calculer la variation sur les dernières 24 heures
    yesterday = datetime.utcnow() - timedelta(days=1)

    # Calculer les crédits et débits des dernières 24h
    recent_credits = db.query(func.sum(Transaction.amount)).filter(
        Transaction.created_at >= yesterday,
        Transaction.amount > 0,
        Transaction.status == TransactionStatus.COMPLETED
    ).scalar() or 0

    recent_debits = db.query(func.sum(Transaction.amount)).filter(
        Transaction.created_at >= yesterday,
        Transaction.amount < 0,
        Transaction.status == TransactionStatus.COMPLETED
    ).scalar() or 0

    # La variation = crédits - débits des dernières 24h
    variation_24h = recent_credits + recent_debits  # recent_debits est déjà négatif

    # Calculer le pourcentage de variation
    previous_balance = stock.balance - variation_24h
    variation_percentage = (variation_24h / previous_balance * 100) if previous_balance != 0 else 0

    # Compter le nombre total d'utilisateurs
    total_users = db.query(User).count()

    # Compter le nombre de transactions NFC (dépôts et retraits)
    nfc_transactions_count = db.query(Transaction).filter(
        Transaction.transaction_type.in_([TransactionType.NFC_DEPOSIT, TransactionType.NFC_WITHDRAWAL])
    ).count()

    return {
        "balance": stock.balance,
        "total_credits": stock.total_credits,
        "total_debits": stock.total_debits,
        "total_commissions": stock.total_commissions,
        "variation_24h": variation_24h,
        "variation_percentage": round(variation_percentage, 2),
        "last_updated": stock.updated_at,
        "currency": "FCFA",
        "total_users": total_users,
        "nfc_transactions": nfc_transactions_count
    }

class AdminStockUpdateRequest(BaseModel):
    amount: float
    type: str  # 'credit' or 'debit'
    description: str

@app.post("/admin/stock/update")
async def update_admin_stock_balance(
    request: AdminStockUpdateRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """Mettre à jour le solde du compte stock admin"""
    stock = get_admin_stock(db)
    
    if request.type == 'credit':
        stock.balance += request.amount
        stock.total_credits += request.amount
    elif request.type == 'debit':
        if stock.balance < request.amount:
            raise HTTPException(status_code=400, detail="Insufficient balance in admin stock")
        stock.balance -= request.amount
        stock.total_debits += request.amount
    else:
        raise HTTPException(status_code=400, detail="Invalid transaction type. Use 'credit' or 'debit'")
    
    # Créer une transaction pour l'historique
    transaction = Transaction(
        id=str(uuid.uuid4()),
        user_id=admin_user.id,
        transaction_type=TransactionType.MOBILE_TRANSFER if request.type == 'credit' else TransactionType.MOBILE_TRANSFER,
        amount=request.amount if request.type == 'credit' else -request.amount,
        commission_amount=0,
        status=TransactionStatus.COMPLETED,
        description=request.description,
        reference=f"STOCK-{request.type.upper()}-{datetime.now().strftime('%Y%m%d%H%M%S')}"
    )
    
    db.add(transaction)
    db.commit()
    db.refresh(stock)
    
    return {
        "success": True,
        "message": f"Stock {request.type} successful",
        "balance": stock.balance,
        "total_credits": stock.total_credits,
        "total_debits": stock.total_debits,
        "transaction": {
            "id": transaction.id,
            "reference": transaction.reference,
            "amount": request.amount,
            "type": request.type,
            "description": request.description,
            "created_at": transaction.created_at
        }
    }

class AdminStockResetRequest(BaseModel):
    balance: float = 0.0
    confirm: bool = False

@app.post("/admin/stock/reset")
async def reset_admin_stock(
    request: AdminStockResetRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """Réinitialiser le compte stock admin - ATTENTION: Action irréversible!"""
    if not request.confirm:
        raise HTTPException(status_code=400, detail="Confirmation required. Set 'confirm' to true")
    
    stock = get_admin_stock(db)
    
    # Réinitialiser tous les compteurs
    stock.balance = request.balance
    stock.total_credits = 0.0
    stock.total_debits = 0.0
    stock.total_commissions = 0.0
    
    db.commit()
    db.refresh(stock)
    
    return {
        "success": True,
        "message": "Stock account reset successfully",
        "balance": stock.balance,
        "total_credits": stock.total_credits,
        "total_debits": stock.total_debits,
        "total_commissions": stock.total_commissions
    }

class AdminStockSetBalanceRequest(BaseModel):
    balance: float
    description: str = "Définition manuelle du solde"

@app.post("/admin/stock/set-balance")
async def set_admin_stock_balance(
    request: AdminStockSetBalanceRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """Définir directement le solde du compte stock admin"""
    stock = get_admin_stock(db)
    
    old_balance = stock.balance
    stock.balance = request.balance
    
    # Créer une transaction pour tracer le changement
    transaction = Transaction(
        id=str(uuid.uuid4()),
        user_id=admin_user.id,
        transaction_type=TransactionType.MOBILE_TRANSFER,
        amount=request.balance - old_balance,
        commission_amount=0,
        status=TransactionStatus.COMPLETED,
        description=f"{request.description} (Ancien: {old_balance:,.0f} → Nouveau: {request.balance:,.0f} FCFA)",
        reference=f"STOCK-SET-{datetime.now().strftime('%Y%m%d%H%M%S')}"
    )
    
    db.add(transaction)
    db.commit()
    db.refresh(stock)
    
    return {
        "success": True,
        "message": "Stock balance set successfully",
        "old_balance": old_balance,
        "new_balance": stock.balance,
        "difference": request.balance - old_balance,
        "transaction": {
            "id": transaction.id,
            "reference": transaction.reference,
            "description": transaction.description,
            "created_at": transaction.created_at
        }
    }
@app.get("/admin/users")
async def get_all_users(
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Récupérer tous les utilisateurs avec leurs statistiques + infos KYC.
    Endpoint admin uniquement.
    """
    users = db.query(User).all()

    users_data = []
    for user in users:
        wallet = get_user_wallet(user, db)

        # Compter les transactions
        total_transactions = db.query(Transaction).filter(
            Transaction.user_id == user.id,
            Transaction.status == TransactionStatus.COMPLETED
        ).count()

        # Dernière activité
        last_transaction = db.query(Transaction).filter(
            Transaction.user_id == user.id
        ).order_by(Transaction.created_at.desc()).first()

        last_activity = last_transaction.created_at if last_transaction else user.created_at

        # Vérifier carte NFC
        has_nfc_card = db.query(NFCCard).filter(
            NFCCard.user_id == user.id,
            NFCCard.status == 'active'
        ).first() is not None

        # Récupérer la vérification KYC associée
        kyc = db.query(KYCVerification).filter(
            KYCVerification.user_id == user.id
        ).first()

        kyc_data = None
        if kyc:
            kyc_data = {
                "kyc_id": kyc.id,
                "status": kyc.status,
                "document_front_image": kyc.document_front_image,
                "document_back_image": kyc.document_back_image,
                "submitted_at": kyc.submitted_at,
                "reviewed_at": kyc.reviewed_at,
                "reviewer_id": kyc.reviewer_id,
            }

        users_data.append({
            "id": user.id,
            "username": user.username,
            "email": user.email,
            "full_name": user.full_name,
            "tim_account_type": user.tim_account_type.value,
            "is_active": user.is_active,
            "wallet_balance": wallet.balance,
            "total_transactions": total_transactions,
            "has_nfc_card": has_nfc_card,
            "last_activity": last_activity,
            "created_at": user.created_at,
            "kyc": kyc_data  # 👈 AJOUT ICI
        })

    return {
        "users": users_data,
        "total_count": len(users_data)
    }

@app.get("/admin/users/{user_id}")
async def get_user_details(user_id: str, admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer les détails complets d'un utilisateur.
    Endpoint admin uniquement.
    """
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")

    wallet = get_user_wallet(user, db)

    # Récupérer les transactions récentes
    recent_transactions = db.query(Transaction).filter(
        Transaction.user_id == user.id
    ).order_by(Transaction.created_at.desc()).limit(10).all()

    # Statistiques
    total_transactions = db.query(Transaction).filter(
        Transaction.user_id == user.id,
        Transaction.status == TransactionStatus.COMPLETED
    ).count()

    total_volume = db.query(func.sum(func.abs(Transaction.amount))).filter(
        Transaction.user_id == user.id,
        Transaction.status == TransactionStatus.COMPLETED
    ).scalar() or 0

    # Carte NFC
    nfc_card = db.query(NFCCard).filter(NFCCard.user_id == user.id).first()

    return {
        "id": user.id,
        "username": user.username,
        "email": user.email,
        "full_name": user.full_name,
        "tim_account_type": user.tim_account_type.value,
        "is_active": user.is_active,
        "is_admin": user.is_admin,
        "wallet_balance": wallet.balance,
        "wallet_max_balance": wallet.max_balance,
        "total_transactions": total_transactions,
        "total_volume": total_volume,
        "nfc_card": {
            "id": nfc_card.id,
            "card_uid": nfc_card.card_uid,
            "card_number": nfc_card.card_number,
            "status": nfc_card.status,
            "balance": nfc_card.balance
        } if nfc_card else None,
        "recent_transactions": [
            {
                "id": t.id,
                "type": t.transaction_type.value,
                "amount": t.amount,
                "status": t.status.value,
                "created_at": t.created_at
            } for t in recent_transactions
        ],
        "created_at": user.created_at
    }

@app.put("/admin/users/{user_id}/status")
async def update_user_status(
    user_id: str,
    is_active: bool,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Activer ou désactiver un utilisateur.
    Endpoint admin uniquement.
    """
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")

    user.is_active = is_active
    db.commit()

    return {
        "success": True,
        "message": f"Utilisateur {'activé' if is_active else 'désactivé'} avec succès",
        "user_id": user_id,
        "is_active": is_active
    }

@app.put("/admin/users/{user_id}/role")
async def update_user_role(
    user_id: str,
    is_admin: bool,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Modifier le rôle d'un utilisateur (admin ou non).
    Endpoint admin uniquement.
    """
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")

    user.is_admin = is_admin
    db.commit()

    return {
        "success": True,
        "message": f"Rôle utilisateur modifié avec succès",
        "user_id": user_id,
        "is_admin": is_admin
    }

@app.delete("/admin/users/{user_id}")
async def delete_user(
    user_id: str,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Supprimer un utilisateur (soft delete).
    Endpoint admin uniquement.
    """
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")

    # Soft delete : désactiver l'utilisateur au lieu de le supprimer
    user.is_active = False
    db.commit()

    return {
        "success": True,
        "message": "Utilisateur supprimé avec succès",
        "user_id": user_id
    }

@app.get("/admin/wallets")
async def get_all_wallets(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer tous les wallets avec les informations utilisateur associées.
    Endpoint admin uniquement.
    """
    # Récupérer tous les wallets avec jointure sur les utilisateurs
    wallets = db.query(Wallet).join(User, Wallet.user_id == User.id).all()

    wallets_data = []
    for wallet in wallets:
        user = wallet.user
        wallets_data.append({
            "id": wallet.id,
            "user_id": wallet.user_id,
            "user_name": user.full_name or user.username or user.email,
            "user_email": user.email,
            "user_phone": user.phone,
            "tim_type": user.tim_account_type.value if user.tim_account_type else "TIM_MINI",
            "balance": wallet.balance,
            "max_balance": wallet.max_balance,
            "currency": wallet.currency,
            "is_active": wallet.is_active,
            "nfc_enabled": wallet.nfc_enabled,
            "created_at": wallet.created_at.isoformat() if wallet.created_at else None,
            "updated_at": wallet.updated_at.isoformat() if wallet.updated_at else None
        })

    return {
        "wallets": wallets_data,
        "total_count": len(wallets_data)
    }

@app.get("/admin/recharges")
async def get_all_recharges(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer toutes les recharges (CinetPay et TIM Business).
    Endpoint admin uniquement.
    """
    # Récupérer les recharges CinetPay et TIM Business
    recharges_cinetpay = db.query(Transaction).filter(
        Transaction.transaction_type == TransactionType.RECHARGE_CINETPAY
    ).all()

  
    # Combiner toutes les recharges
    all_recharges = recharges_cinetpay 

    recharges_data = []
    for recharge in all_recharges:
        # Récupérer l'utilisateur
        user = db.query(User).filter(User.id == recharge.user_id).first()

        # Déterminer la méthode de recharge
        method = "cinetpay" if recharge.transaction_type == TransactionType.RECHARGE_CINETPAY else "tim_business"

        recharges_data.append({
            "id": recharge.id,
            "user_id": recharge.user_id,
            "user_name": user.full_name or user.username or user.email if user else "Utilisateur inconnu",
            "tim_type": user.tim_account_type.value if user and user.tim_account_type else "TIM_MINI",
            "amount": abs(recharge.amount),  # Valeur absolue pour affichage
            "commission": recharge.commission_amount or 0.0,
            "method": method,
            "status": recharge.status.value if recharge.status else "pending",
            "transaction_id": recharge.reference,
            "external_reference": recharge.external_reference,
            "payment_method": recharge.payment_method.value if recharge.payment_method else None,
            "description": recharge.description,
            "created_at": recharge.created_at.isoformat() if recharge.created_at else None,
            "completed_at": recharge.completed_at.isoformat() if recharge.completed_at else None
        })

    # Trier par date de création (plus récent en premier)
    recharges_data.sort(key=lambda x: x["created_at"] or "", reverse=True)

    return {
        "recharges": recharges_data,
        "total_count": len(recharges_data)
    }

@app.get("/admin/workflows")
async def get_all_workflows(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer tous les workflows.
    Endpoint admin uniquement.
    """
    import json

    # Récupérer tous les workflows
    workflows = db.query(Workflow).all()

    workflows_data = []
    for workflow in workflows:
        # Parser les steps JSON
        steps = []
        if workflow.steps:
            try:
                steps = json.loads(workflow.steps)
            except:
                steps = []

        workflows_data.append({
            "id": workflow.id,
            "name": workflow.name,
            "description": workflow.description,
            "status": workflow.status,
            "category": workflow.category,
            "triggers": workflow.triggers or 0,
            "executions": workflow.executions or 0,
            "success_rate": workflow.success_rate or 0.0,
            "steps": steps,
            "last_run": workflow.last_run.isoformat() if workflow.last_run else None,
            "created_at": workflow.created_at.isoformat() if workflow.created_at else None,
            "updated_at": workflow.updated_at.isoformat() if workflow.updated_at else None
        })

    # Trier par date de création (plus récent en premier)
    workflows_data.sort(key=lambda x: x["created_at"] or "", reverse=True)

    return {
        "workflows": workflows_data,
        "total_count": len(workflows_data)
    }

@app.get("/admin/loans")
async def get_all_loans(admin_user: AdminUser = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer tous les prêts.
    Endpoint admin uniquement.
    """
    from datetime import datetime

    # Récupérer tous les prêts
    loans = db.query(Loan).all()

    loans_data = []
    for loan in loans:
        # Récupérer l'utilisateur
        user = db.query(User).filter(User.id == loan.user_id).first()

        # Calculer les jours de retard
        # days_overdue = 0
        # if loan.due_date and loan.due_date < datetime.utcnow() and loan.status != LoanStatus.COMPLETED:
        #     days_overdue = (datetime.utcnow() - loan.due_date).days

        # Calculer la pénalité
        # penalty_amount = 0
        # if days_overdue > 0 and loan.penalty_rate:
        #     penalty_amount = (loan.amount + loan.amount * loan.interest_rate) * loan.penalty_rate

        loans_data.append({
            "id": loan.id,
            "user_id": loan.user_id,
            "user_name": user.full_name or user.username or user.email if user else "Utilisateur inconnu",
            "tim_type": user.tim_account_type.value if user and user.tim_account_type else "TIM_MINI",
            "amount": loan.amount,
            "interest_rate": loan.interest_rate or 0.0,
            "penalty_rate": loan.penalty_rate or 0.0,
            "remaining_balance": loan.remaining_balance or loan.amount,
            "monthly_payment": loan.monthly_payment or 0.0,
            "total_payments": loan.total_payments or 0,
            "payments_completed": loan.payments_completed or 0,
            # "days_overdue": days_overdue,
            # "penalty_amount": penalty_amount,
            "status": loan.status.value if loan.status else "pending",
            "due_date": loan.due_date.isoformat() if loan.due_date else None,
            "created_at": loan.created_at.isoformat() if loan.created_at else None,
            "approved_at": loan.approved_at.isoformat() if loan.approved_at else None,
            # "document_recto": loan.document_recto,
            # "document_verso": loan.document_verso
        })

    # Trier par date de création (plus récent en premier)
    loans_data.sort(key=lambda x: x["created_at"] or "", reverse=True)

    return {
        "loans": loans_data,
        "total_count": len(loans_data)
    }

@app.get("/admin/loans/{loan_id}/documents/{doc_type}")
async def get_loan_document(
    loan_id: str,
    doc_type: str,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Récupérer un document de prêt (recto ou verso).
    Endpoint admin uniquement.
    doc_type: 'recto' ou 'verso'
    """
    from fastapi.responses import FileResponse
    import os

    # Vérifier que le type de document est valide
    if doc_type not in ['recto', 'verso']:
        raise HTTPException(status_code=400, detail="Type de document invalide. Utilisez 'recto' ou 'verso'")

    # Récupérer le prêt
    loan = db.query(Loan).filter(Loan.id == loan_id).first()
    if not loan:
        raise HTTPException(status_code=404, detail="Prêt introuvable")

    # Récupérer le chemin du document
    document_path = loan.document_recto if doc_type == 'recto' else loan.document_verso

    if not document_path:
        raise HTTPException(status_code=404, detail=f"Document {doc_type} non disponible pour ce prêt")

    # Vérifier que le fichier existe
    if not os.path.exists(document_path):
        raise HTTPException(status_code=404, detail=f"Fichier {doc_type} introuvable sur le serveur")

    # Retourner le fichier
    return FileResponse(
        document_path,
        media_type="image/jpeg",
        filename=f"loan_{loan_id}_{doc_type}.jpg"
    )

@app.get("/admin/loans/overdue")
async def get_overdue_loans(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer tous les prêts en retard.
    Endpoint admin uniquement.
    """
    from datetime import datetime

    # Récupérer tous les prêts en retard
    loans = db.query(Loan).filter(
        Loan.due_date < datetime.utcnow(),
        Loan.status != LoanStatus.COMPLETED
    ).all()

    loans_data = []
    for loan in loans:
        # Récupérer l'utilisateur
        user = db.query(User).filter(User.id == loan.user_id).first()

        # Calculer les jours de retard
        days_overdue = (datetime.utcnow() - loan.due_date).days if loan.due_date else 0

        # Calculer la pénalité
        penalty_amount = 0
        if days_overdue > 0 and loan.penalty_rate:
            penalty_amount = (loan.amount + loan.amount * loan.interest_rate) * loan.penalty_rate

        loans_data.append({
            "id": loan.id,
            "user_id": loan.user_id,
            "user_name": user.full_name or user.username or user.email if user else "Utilisateur inconnu",
            "tim_type": user.tim_account_type.value if user and user.tim_account_type else "TIM_MINI",
            "amount": loan.amount,
            "interest_rate": loan.interest_rate or 0.0,
            "penalty_rate": loan.penalty_rate or 0.0,
            "remaining_balance": loan.remaining_balance or loan.amount,
            "monthly_payment": loan.monthly_payment or 0.0,
            "total_payments": loan.total_payments or 0,
            "payments_completed": loan.payments_completed or 0,
            "days_overdue": days_overdue,
            "penalty_amount": penalty_amount,
            "status": "overdue",  # Forcer le statut à overdue
            "due_date": loan.due_date.isoformat() if loan.due_date else None,
            "created_at": loan.created_at.isoformat() if loan.created_at else None,
            "approved_at": loan.approved_at.isoformat() if loan.approved_at else None
        })

    # Trier par jours de retard (plus en retard en premier)
    loans_data.sort(key=lambda x: x["days_overdue"], reverse=True)

    return {
        "loans": loans_data,
        "total_count": len(loans_data)
    }

@app.get("/admin/loans/repayments")
async def get_loan_repayments(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer tous les remboursements de prêts.
    Endpoint admin uniquement.
    """
    from datetime import datetime

    # Récupérer tous les prêts avec leurs informations de remboursement
    loans = db.query(Loan).all()

    repayments_data = []
    for loan in loans:
        # Récupérer l'utilisateur
        user = db.query(User).filter(User.id == loan.user_id).first()

        # Calculer les jours de retard
        days_overdue = 0
        if loan.due_date and loan.due_date < datetime.utcnow() and loan.status != LoanStatus.COMPLETED:
            days_overdue = (datetime.utcnow() - loan.due_date).days

        # Calculer les montants
        total_amount = loan.amount + (loan.amount * loan.interest_rate) if loan.interest_rate else loan.amount
        paid_amount = total_amount - (loan.remaining_balance or 0)

        # Calculer la pénalité si en retard
        penalty_amount = 0
        if days_overdue > 0 and loan.penalty_rate:
            penalty_amount = total_amount * loan.penalty_rate

        # Déterminer le statut du remboursement
        if loan.status == LoanStatus.COMPLETED:
            repayment_status = "completed"
        elif days_overdue > 0:
            repayment_status = "overdue"
        elif paid_amount > 0 and loan.remaining_balance > 0:
            repayment_status = "partial"
        else:
            repayment_status = "pending"

        # Récupérer l'historique des paiements (transactions de remboursement)
        payment_transactions = db.query(Transaction).filter(
            Transaction.external_reference == loan.id,
            Transaction.transaction_type.in_([TransactionType.REMBOURSEMENT_AUTO, TransactionType.MOBILE_TRANSFER])
        ).all()

        payment_history = []
        last_payment = None
        for txn in payment_transactions:
            payment_history.append({
                "date": txn.created_at.isoformat() if txn.created_at else None,
                "amount": abs(txn.amount),
                "type": "payment",
                "reference": txn.reference
            })
            if txn.created_at:
                if not last_payment or txn.created_at > last_payment:
                    last_payment = txn.created_at

        repayments_data.append({
            "id": loan.id,
            "user_id": loan.user_id,
            "user_name": user.full_name or user.username or user.email if user else "Utilisateur inconnu",
            "tim_type": user.tim_account_type.value if user and user.tim_account_type else "TIM_MINI",
            "loan_amount": loan.amount,
            "interest_rate": loan.interest_rate or 0.0,
            "penalty_rate": loan.penalty_rate or 0.0,
            "total_amount": total_amount,
            "paid_amount": paid_amount,
            "remaining_amount": loan.remaining_balance or 0.0,
            "due_date": loan.due_date.isoformat() if loan.due_date else None,
            "days_overdue": days_overdue,
            "penalty_amount": penalty_amount,
            "status": repayment_status,
            "last_payment": last_payment.isoformat() if last_payment else None,
            "payment_history": payment_history,
            "created_at": loan.created_at.isoformat() if loan.created_at else None
        })

    # Trier par date de création (plus récent en premier)
    repayments_data.sort(key=lambda x: x["created_at"] or "", reverse=True)

    return {
        "repayments": repayments_data,
        "total_count": len(repayments_data),
        "stats": {
            "pending": len([r for r in repayments_data if r["status"] == "pending"]),
            "overdue": len([r for r in repayments_data if r["status"] == "overdue"]),
            "completed": len([r for r in repayments_data if r["status"] == "completed"]),
            "partial": len([r for r in repayments_data if r["status"] == "partial"])
        }
    }

@app.get("/admin/tim-business-recharges")
async def get_tim_business_recharges(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer toutes les recharges TIM BUSINESS.
    Endpoint admin uniquement.
    """
    from datetime import datetime, timedelta

    # Récupérer toutes les transactions de type RECHARGE_BY_BUSINESS
    recharges = db.query(Transaction).filter(
        Transaction.transaction_type == TransactionType.RECHARGE_BY_BUSINESS
    ).order_by(Transaction.created_at.desc()).all()

    recharges_data = []
    total_amount = 0
    total_commissions_business = 0
    total_commissions_timcash = 0
    today_recharges = 0
    today_amount = 0

    today = datetime.utcnow().date()

    for txn in recharges:
        # Récupérer l'utilisateur (TIM BUSINESS)
        user = db.query(User).filter(User.id == txn.user_id).first()

        # Calculer les commissions (10% total: 4% TIM BUSINESS + 6% TIM CASH)
        if txn.amount > 0:  # Crédit (recharge reçue)
            amount = txn.amount
            total_commission = amount * 0.10
            business_commission = amount * 0.04
            timcash_commission = amount * 0.06
            total_with_commission = amount + total_commission
        else:  # Débit (recharge effectuée)
            total_with_commission = abs(txn.amount)
            total_commission = total_with_commission * 0.10
            amount = total_with_commission - total_commission
            business_commission = total_with_commission * 0.04
            timcash_commission = total_with_commission * 0.06

        # Extraire le numéro de téléphone du bénéficiaire depuis la description
        beneficiary_phone = "N/A"
        beneficiary_account_type = "TIM_MINI"
        if txn.description:
            # Format: "Recharge de 1500 FCFA vers +225XXXXXXXX"
            import re
            phone_match = re.search(r'\+?\d{10,15}', txn.description)
            if phone_match:
                beneficiary_phone = phone_match.group()

        # Statistiques
        total_amount += amount
        total_commissions_business += business_commission
        total_commissions_timcash += timcash_commission

        if txn.created_at and txn.created_at.date() == today:
            today_recharges += 1
            today_amount += amount

        recharges_data.append({
            "id": txn.id,
            "reference": txn.reference,
            "amount": amount,
            "total_commission": total_commission,
            "business_commission": business_commission,
            "timcash_commission": timcash_commission,
            "total_amount": total_with_commission,
            "status": txn.status.value,
            "beneficiary_phone": beneficiary_phone,
            "beneficiary_account_type": beneficiary_account_type,
            "business_user_id": txn.user_id,
            "business_user_name": user.full_name or user.username or user.email if user else "Utilisateur inconnu",
            "created_at": txn.created_at.isoformat() if txn.created_at else None,
            "completed_at": txn.completed_at.isoformat() if txn.completed_at else None,
            "description": txn.description
        })

    return {
        "recharges": recharges_data,
        "total_count": len(recharges_data),
        "stats": {
            "total_recharges": len(recharges_data),
            "total_amount": total_amount,
            "total_commissions_business": total_commissions_business,
            "total_commissions_timcash": total_commissions_timcash,
            "today_recharges": today_recharges,
            "today_amount": today_amount
        }
    }

@app.get("/admin/analytics/nfc")
async def get_nfc_analytics(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer les analytics NFC (capacités d'appareils et transactions).
    Endpoint admin uniquement.
    """
    # Récupérer les capacités d'appareils
    device_capabilities = db.query(DeviceCapability).all()

    # Récupérer les transactions NFC
    nfc_transactions = db.query(Transaction).filter(
        Transaction.transaction_type.in_([TransactionType.NFC_DEPOSIT, TransactionType.NFC_WITHDRAWAL])
    ).all()

    # Calculer les statistiques
    total_users = db.query(User).count()
    nfc_capable_devices = len([d for d in device_capabilities if d.has_nfc])
    nfc_enabled_devices = len([d for d in device_capabilities if d.nfc_enabled])
    total_transactions = db.query(Transaction).count()
    nfc_transaction_count = len(nfc_transactions)
    traditional_transactions = total_transactions - nfc_transaction_count

    # Calculer le volume total
    total_volume = sum(abs(t.amount) for t in nfc_transactions)

    # Préparer les données des appareils
    devices_data = []
    for device in device_capabilities:
        devices_data.append({
            "id": device.id,
            "device_model": device.device_model,
            "brand": device.brand,
            "has_nfc": device.has_nfc,
            "nfc_enabled": device.nfc_enabled,
            "os_version": device.os_version,
            "user_count": device.user_count,
            "transaction_count": device.transaction_count,
            "success_rate": device.success_rate,
            "location": device.location,
            "country": device.country,
            "city": device.city,
            "last_seen": device.last_seen.isoformat() if device.last_seen else None
        })

    # Préparer les analytics par zone géographique
    location_analytics = []
    locations = {}

    for device in device_capabilities:
        key = f"{device.country}_{device.city}"
        if key not in locations:
            locations[key] = {
                "zone": device.location or device.city,
                "country": device.country,
                "city": device.city,
                "total_users": 0,
                "nfc_capable_users": 0,
                "nfc_enabled_users": 0,
                "transactions_total": 0,
                "transactions_nfc": 0,
                "transactions_traditional": 0
            }

        locations[key]["total_users"] += device.user_count
        if device.has_nfc:
            locations[key]["nfc_capable_users"] += device.user_count
        if device.nfc_enabled:
            locations[key]["nfc_enabled_users"] += device.user_count
        locations[key]["transactions_total"] += device.transaction_count
        if device.has_nfc and device.nfc_enabled:
            locations[key]["transactions_nfc"] += device.transaction_count
        else:
            locations[key]["transactions_traditional"] += device.transaction_count

    for loc_data in locations.values():
        # Calculer le taux d'adoption
        adoption_rate = 0
        if loc_data["total_users"] > 0:
            adoption_rate = (loc_data["nfc_enabled_users"] / loc_data["total_users"]) * 100

        location_analytics.append({
            "zone": loc_data["zone"],
            "country": loc_data["country"],
            "city": loc_data["city"],
            "total_users": loc_data["total_users"],
            "nfc_capable_users": loc_data["nfc_capable_users"],
            "nfc_enabled_users": loc_data["nfc_enabled_users"],
            "transactions_today": loc_data["transactions_total"],
            "transactions_nfc": loc_data["transactions_nfc"],
            "transactions_traditional": loc_data["transactions_traditional"],
            "average_amount": 0,  # À calculer si nécessaire
            "peak_hour": "N/A",
            "adoption_rate": round(adoption_rate, 2)
        })

    return {
        "device_capabilities": devices_data,
        "location_analytics": location_analytics,
        "stats": {
            "total_users": total_users,
            "nfc_capable_devices": nfc_capable_devices,
            "nfc_enabled_devices": nfc_enabled_devices,
            "total_transactions": total_transactions,
            "nfc_transactions": nfc_transaction_count,
            "traditional_transactions": traditional_transactions,
            "total_volume": total_volume
        }
    }

@app.get("/admin/reports/cinetpay")
async def get_cinetpay_reports(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer les rapports des transactions CinetPay.
    Endpoint admin uniquement.
    """
    # Récupérer toutes les transactions CinetPay
    cinetpay_transactions = db.query(Transaction).filter(
        Transaction.transaction_type == TransactionType.RECHARGE_CINETPAY
    ).order_by(Transaction.created_at.desc()).all()

    # Préparer les données des transactions
    transactions_data = []
    total_amount = 0
    total_commissions = 0
    total_auto_repayments = 0

    for tx in cinetpay_transactions:
        # Récupérer l'utilisateur
        user = db.query(User).filter(User.id == tx.user_id).first()

        # Mapper le statut
        status = 'success'
        if tx.status == TransactionStatus.PENDING:
            status = 'pending'
        elif tx.status == TransactionStatus.FAILED:
            status = 'failed'
        elif tx.status == TransactionStatus.COMPLETED:
            status = 'success'

        # Calculer le remboursement automatique si applicable
        auto_repayment = 0
        # Vérifier s'il y a un remboursement automatique lié à cette transaction
        if tx.description and 'remboursement' in tx.description.lower():
            # Extraire le montant du remboursement de la description si possible
            # Pour l'instant, on laisse à 0
            auto_repayment = 0

        transaction_data = {
            "id": tx.id,
            "user_id": tx.user_id,
            "user_name": user.full_name if user else "Utilisateur inconnu",
            "tim_type": user.tim_account_type.value if user and user.tim_account_type else "TIM_MAXI",
            "amount": tx.amount,
            "commission": tx.commission_amount,
            "commission_rate": tx.commission_rate * 100 if tx.commission_rate else 0.5,  # Convertir en pourcentage
            "status": status,
            "transaction_id": tx.reference,
            "cinetpay_ref": tx.external_reference or tx.reference,
            "payment_method": tx.payment_method.value if tx.payment_method else "mobile_money",
            "created_at": tx.created_at.isoformat() if tx.created_at else None,
            "completed_at": tx.completed_at.isoformat() if tx.completed_at else None,
            "auto_repayment": auto_repayment
        }

        transactions_data.append(transaction_data)

        # Calculer les totaux
        if tx.status == TransactionStatus.COMPLETED:
            total_amount += tx.amount
            total_commissions += tx.commission_amount
            total_auto_repayments += auto_repayment

    # Calculer les statistiques
    successful_txs = len([tx for tx in cinetpay_transactions if tx.status == TransactionStatus.COMPLETED])
    success_rate = (successful_txs / len(cinetpay_transactions) * 100) if len(cinetpay_transactions) > 0 else 0

    # Répartition par statut
    status_breakdown = {
        "success": len([tx for tx in cinetpay_transactions if tx.status == TransactionStatus.COMPLETED]),
        "pending": len([tx for tx in cinetpay_transactions if tx.status == TransactionStatus.PENDING]),
        "failed": len([tx for tx in cinetpay_transactions if tx.status == TransactionStatus.FAILED])
    }

    # Répartition par type de compte
    type_breakdown = {}
    for tx in cinetpay_transactions:
        user = db.query(User).filter(User.id == tx.user_id).first()
        if user and user.tim_account_type:
            account_type = user.tim_account_type.value
            if account_type not in type_breakdown:
                type_breakdown[account_type] = 0
            type_breakdown[account_type] += 1

    return {
        "transactions": transactions_data,
        "total_count": len(transactions_data),
        "stats": {
            "total_amount": total_amount,
            "total_commissions": total_commissions,
            "total_auto_repayments": total_auto_repayments,
            "success_rate": round(success_rate, 2),
            "successful_transactions": successful_txs,
            "status_breakdown": status_breakdown,
            "type_breakdown": type_breakdown
        }
    }

# Wallet Operations
@app.post("/wallet/reload")
@app.get("/admin/monitoring")
async def get_system_monitoring(admin_user: AdminUser = Depends(get_admin_user), db: Session = Depends(get_db)):
    """
    Récupérer les métriques de monitoring système en temps réel.
    Endpoint admin uniquement.
    """
    from datetime import datetime, timedelta
    import psutil
    import time

    # Métriques de la base de données
    total_users = db.query(User).count()
    total_transactions = db.query(Transaction).count()
    total_wallets = db.query(Wallet).count()
    total_loans = db.query(Loan).count()

    # Transactions par statut
    pending_transactions = db.query(Transaction).filter(Transaction.status == TransactionStatus.PENDING).count()
    completed_transactions = db.query(Transaction).filter(Transaction.status == TransactionStatus.COMPLETED).count()
    failed_transactions = db.query(Transaction).filter(Transaction.status == TransactionStatus.FAILED).count()

    # Prêts par statut
    active_loans = db.query(Loan).filter(Loan.status.in_([LoanStatus.APPROVED, LoanStatus.ACTIVE])).count()
    overdue_loans = db.query(Loan).filter(Loan.status == LoanStatus.OVERDUE).count()
    completed_loans = db.query(Loan).filter(Loan.status == LoanStatus.COMPLETED).count()

    # Activité récente (dernières 24h)
    last_24h = datetime.utcnow() - timedelta(hours=24)
    recent_transactions = db.query(Transaction).filter(Transaction.created_at >= last_24h).count()
    recent_users = db.query(User).filter(User.created_at >= last_24h).count()

    # Volume financier
    total_volume = db.query(Transaction).filter(
        Transaction.status == TransactionStatus.COMPLETED
    ).with_entities(Transaction.amount).all()
    total_volume_sum = sum([abs(t[0]) for t in total_volume]) if total_volume else 0

    # Stock admin
    admin_stock = get_admin_stock(db)

    # Métriques système (CPU, RAM, etc.)
    try:
        cpu_percent = psutil.cpu_percent(interval=0.1)
        memory = psutil.virtual_memory()
        disk = psutil.disk_usage('/')

        system_metrics = {
            "cpu_usage": round(cpu_percent, 2),
            "memory_usage": round(memory.percent, 2),
            "memory_available": round(memory.available / (1024**3), 2),  # GB
            "memory_total": round(memory.total / (1024**3), 2),  # GB
            "disk_usage": round(disk.percent, 2),
            "disk_free": round(disk.free / (1024**3), 2),  # GB
            "disk_total": round(disk.total / (1024**3), 2)  # GB
        }
    except Exception as e:
        print(f"Erreur lors de la récupération des métriques système: {e}")
        system_metrics = {
            "cpu_usage": 0,
            "memory_usage": 0,
            "memory_available": 0,
            "memory_total": 0,
            "disk_usage": 0,
            "disk_free": 0,
            "disk_total": 0
        }

    # Santé du système
    health_status = "healthy"
    health_issues = []

    if system_metrics["cpu_usage"] > 80:
        health_status = "warning"
        health_issues.append("CPU usage is high")

    if system_metrics["memory_usage"] > 85:
        health_status = "critical" if health_status != "critical" else health_status
        health_issues.append("Memory usage is critical")

    if system_metrics["disk_usage"] > 90:
        health_status = "critical"
        health_issues.append("Disk space is critically low")

    if failed_transactions > completed_transactions * 0.1:  # Plus de 10% d'échecs
        health_status = "warning" if health_status == "healthy" else health_status
        health_issues.append("High transaction failure rate")

    # Temps de réponse API (simulé)
    api_response_time = round(time.time() * 1000 % 100, 2)  # Simulé entre 0-100ms

    return {
        "health": {
            "status": health_status,
            "issues": health_issues,
            "uptime": "99.9%",  # À implémenter avec un vrai système de monitoring
            "last_check": datetime.utcnow().isoformat()
        },
        "system": system_metrics,
        "database": {
            "total_users": total_users,
            "total_transactions": total_transactions,
            "total_wallets": total_wallets,
            "total_loans": total_loans,
            "connection_status": "connected"
        },
        "transactions": {
            "total": total_transactions,
            "pending": pending_transactions,
            "completed": completed_transactions,
            "failed": failed_transactions,
            "success_rate": round((completed_transactions / total_transactions * 100) if total_transactions > 0 else 0, 2),
            "recent_24h": recent_transactions
        },
        "loans": {
            "total": total_loans,
            "active": active_loans,
            "overdue": overdue_loans,
            "completed": completed_loans
        },
        "performance": {
            "api_response_time": api_response_time,
            "total_volume": total_volume_sum,
            "admin_stock_balance": admin_stock.balance,
            "total_commissions": admin_stock.total_commissions
        },
        "activity": {
            "recent_transactions_24h": recent_transactions,
            "recent_users_24h": recent_users
        }
    }

@app.get("/admin/transactions")
async def get_admin_transactions(
    limit: int = 100,
    offset: int = 0,
    status: str = None,
    transaction_type: str = None,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Récupérer toutes les transactions (admin uniquement).
    Supporte le filtrage par statut et type de transaction.
    """
    # Construire la requête de base
    query = db.query(Transaction)

    # Appliquer les filtres si fournis
    if status:
        try:
            status_enum = TransactionStatus(status.upper())
            query = query.filter(Transaction.status == status_enum)
        except ValueError:
            pass  # Ignorer les statuts invalides

    if transaction_type:
        try:
            type_enum = TransactionType(transaction_type.upper())
            query = query.filter(Transaction.transaction_type == type_enum)
        except ValueError:
            pass  # Ignorer les types invalides

    # Compter le total
    total_count = query.count()

    # Récupérer les transactions avec pagination
    transactions = query.order_by(Transaction.created_at.desc()).offset(offset).limit(limit).all()

    # Préparer les données
    transactions_data = []
    for tx in transactions:
        # Récupérer l'utilisateur
        user = db.query(User).filter(User.id == tx.user_id).first()

        transactions_data.append({
            "id": tx.id,
            "user_id": tx.user_id,
            "user_email": user.email if user else None,
            "user_name": user.full_name if user else None,
            "transaction_type": tx.transaction_type.value,
            "amount": tx.amount,
            "commission_amount": tx.commission_amount,
            "commission_rate": tx.commission_rate,
            "status": tx.status.value,
            "description": tx.description,
            "reference": tx.reference,
            "external_reference": tx.external_reference,
            "payment_method": tx.payment_method.value if tx.payment_method else None,
            "merchant_name": tx.merchant_name,
            "location": tx.location,
            "created_at": tx.created_at.isoformat() if tx.created_at else None,
            "completed_at": tx.completed_at.isoformat() if tx.completed_at else None
        })

    return {
        "transactions": transactions_data,
        "total_count": total_count,
        "limit": limit,
        "offset": offset
    }

@app.get("/admin/transactions/stats")
async def get_admin_transactions_stats(
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    Récupérer les statistiques des transactions (admin uniquement).
    """
    # Compter les transactions par statut
    stats_by_status = {}
    for status in TransactionStatus:
        count = db.query(Transaction).filter(Transaction.status == status).count()
        stats_by_status[status.value.lower()] = count

    # Compter les transactions par type
    stats_by_type = {}
    for tx_type in TransactionType:
        count = db.query(Transaction).filter(Transaction.transaction_type == tx_type).count()
        stats_by_type[tx_type.value.lower()] = count

    # Calculer les montants totaux
    total_amount = db.query(func.sum(func.abs(Transaction.amount))).filter(
        Transaction.status == TransactionStatus.COMPLETED
    ).scalar() or 0

    total_commissions = db.query(func.sum(Transaction.commission_amount)).filter(
        Transaction.status == TransactionStatus.COMPLETED
    ).scalar() or 0

    # Transactions des dernières 24h
    yesterday = datetime.utcnow() - timedelta(days=1)
    recent_transactions_24h = db.query(Transaction).filter(
        Transaction.created_at >= yesterday
    ).count()

    # Total des transactions
    total_transactions = db.query(Transaction).count()

    return {
        "total_transactions": total_transactions,
        "stats_by_status": stats_by_status,
        "stats_by_type": stats_by_type,
        "total_amount": float(total_amount),
        "total_commissions": float(total_commissions),
        "recent_transactions_24h": recent_transactions_24h,
        "generated_at": datetime.utcnow().isoformat()
    }

# Countries and Cities
@app.get("/countries")
async def get_countries(db: Session = Depends(get_db)):
    countries = db.query(Country).all()
    
    countries_data = []
    for country in countries:
        cities = db.query(City).filter(City.country_id == country.id).all()
        cities_data = [{
            "id": city.id,
            "name": city.name,
            "branch_code": city.branch_code,
            "is_active": city.is_active,
            "users": city.users,
            "volume": city.volume,
            "created_at": city.created_at
        } for city in cities]
        
        countries_data.append({
            "id": country.id,
            "name": country.name,
            "code": country.code,
            "currency": country.currency,
            "currency_symbol": country.currency_symbol,
            "flag": country.flag,
            "is_active": country.is_active,
            "total_users": country.total_users,
            "total_volume": country.total_volume,
            "cities": cities_data,
            "created_at": country.created_at
        })
    
    return {
        "countries": countries_data,
        "total_count": len(countries_data)
    }

# Commission Rates
class CommissionRateRequest(BaseModel):
    name: str
    rate_type: str  # percentage, fixed
    value: float
    category: str  # recharge_cinetpay, transfer, loan_interest, etc.
    description: Optional[str] = None
    min_amount: Optional[float] = None
    max_amount: Optional[float] = None
    is_active: bool = True

@app.get("/admin/commission-rates")
async def get_commission_rates(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    rates = db.query(CommissionRate).all()
    
    rates_data = []
    for rate in rates:
        rates_data.append({
            "id": rate.id,
            "name": rate.name,
            "rate_type": rate.rate_type,
            "value": rate.value,
            "category": rate.category,
            "description": rate.description,
            "min_amount": rate.min_amount,
            "max_amount": rate.max_amount,
            "is_active": rate.is_active,
            "updated_by": rate.updated_by,
            "created_at": rate.created_at,
            "updated_at": rate.updated_at
        })
    
    return {
        "commission_rates": rates_data,
        "total_count": len(rates_data)
    }

@app.post("/admin/commission-rates")
async def create_commission_rate(
    request: CommissionRateRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """Créer un nouveau taux de commission"""
    commission_rate = CommissionRate(
        id=str(uuid.uuid4()),
        name=request.name,
        rate_type=request.rate_type,
        value=request.value,
        category=request.category,
        description=request.description,
        min_amount=request.min_amount,
        max_amount=request.max_amount,
        is_active=request.is_active,
        updated_by=admin_user.email
    )
    
    db.add(commission_rate)
    db.commit()
    db.refresh(commission_rate)
    
    return {
        "success": True,
        "message": "Commission rate created successfully",
        "commission_rate": {
            "id": commission_rate.id,
            "name": commission_rate.name,
            "rate_type": commission_rate.rate_type,
            "value": commission_rate.value,
            "category": commission_rate.category,
            "description": commission_rate.description,
            "min_amount": commission_rate.min_amount,
            "max_amount": commission_rate.max_amount,
            "is_active": commission_rate.is_active,
            "updated_by": commission_rate.updated_by,
            "created_at": commission_rate.created_at
        }
    }

@app.put("/admin/commission-rates/{rate_id}")
async def update_commission_rate(
    rate_id: str,
    request: CommissionRateRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """Mettre à jour un taux de commission"""
    commission_rate = db.query(CommissionRate).filter(CommissionRate.id == rate_id).first()
    
    if not commission_rate:
        raise HTTPException(status_code=404, detail="Commission rate not found")
    
    commission_rate.name = request.name
    commission_rate.rate_type = request.rate_type
    commission_rate.value = request.value
    commission_rate.category = request.category
    commission_rate.description = request.description
    commission_rate.min_amount = request.min_amount
    commission_rate.max_amount = request.max_amount
    commission_rate.is_active = request.is_active
    commission_rate.updated_by = admin_user.email
    commission_rate.updated_at = datetime.now()
    
    db.commit()
    db.refresh(commission_rate)
    
    return {
        "success": True,
        "message": "Commission rate updated successfully",
        "commission_rate": {
            "id": commission_rate.id,
            "name": commission_rate.name,
            "rate_type": commission_rate.rate_type,
            "value": commission_rate.value,
            "category": commission_rate.category,
            "description": commission_rate.description,
            "min_amount": commission_rate.min_amount,
            "max_amount": commission_rate.max_amount,
            "is_active": commission_rate.is_active,
            "updated_by": commission_rate.updated_by,
            "updated_at": commission_rate.updated_at
        }
    }

@app.delete("/admin/commission-rates/{rate_id}")
async def delete_commission_rate(
    rate_id: str,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """Supprimer un taux de commission"""
    commission_rate = db.query(CommissionRate).filter(CommissionRate.id == rate_id).first()
    
    if not commission_rate:
        raise HTTPException(status_code=404, detail="Commission rate not found")
    
    db.delete(commission_rate)
    db.commit()
    
    return {
        "success": True,
        "message": "Commission rate deleted successfully"
    }

# Device Analytics
@app.get("/admin/device-analytics")
async def get_device_analytics(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    devices = db.query(DeviceCapability).all()
    
    devices_data = []
    for device in devices:
        devices_data.append({
            "id": device.id,
            "device_model": device.device_model,
            "brand": device.brand,
            "has_nfc": device.has_nfc,
            "nfc_enabled": device.nfc_enabled,
            "os_version": device.os_version,
            "user_count": device.user_count,
            "transaction_count": device.transaction_count,
            "success_rate": device.success_rate,
            "location": device.location,
            "country": device.country,
            "city": device.city,
            "last_seen": device.last_seen
        })
    
    return {
        "devices": devices_data,
        "total_count": len(devices_data)
    }

# Dashboard Stats
@app.get("/admin/dashboard-stats")
async def get_dashboard_stats(admin_user: User = Depends(get_admin_user), db: Session = Depends(get_db)):
    total_users = db.query(User).count()
    total_transactions = db.query(Transaction).count()
    total_loans = db.query(Loan).count()
    active_loans = db.query(Loan).filter(Loan.status.in_([LoanStatus.APPROVED, LoanStatus.ACTIVE, LoanStatus.OVERDUE])).count()
    
    # Calculer le volume total
    total_volume = db.query(Transaction).filter(
        Transaction.status == TransactionStatus.COMPLETED
    ).with_entities(Transaction.amount).all()
    total_volume_sum = sum([abs(t[0]) for t in total_volume]) if total_volume else 0
    
    # Stock admin
    admin_stock = get_admin_stock(db)
    
    return {
        "total_users": total_users,
        "total_transactions": total_transactions,
        "total_loans": total_loans,
        "active_loans": active_loans,
        "total_volume": total_volume_sum,
        "admin_stock_balance": admin_stock.balance,
        "total_commissions": admin_stock.total_commissions,
        "nfc_transactions": db.query(Transaction).filter(
            Transaction.transaction_type == TransactionType.NFC_DEPOSIT
        ).count()
    }

@app.get("/admin/recent-transactions")
async def get_recent_transactions(
    limit: int = 10,
    admin_user: User = Depends(get_admin_user), 
    db: Session = Depends(get_db)
):
    """Récupérer les transactions récentes pour le dashboard admin"""
    transactions = db.query(Transaction).order_by(
        Transaction.created_at.desc()
    ).limit(limit).all()
    
    transactions_data = []
    for tx in transactions:
        # Récupérer les infos utilisateur
        user = db.query(User).filter(User.id == tx.user_id).first()
        
        transactions_data.append({
            "id": tx.id,
            "type": tx.transaction_type.value,
            "amount": abs(tx.amount),  # Montant absolu
            "status": tx.status.value,
            "description": tx.description or f"Transaction {tx.transaction_type.value}",
            "createdAt": tx.created_at.isoformat() if tx.created_at else None,
            "user": {
                "id": user.id if user else None,
                "full_name": user.full_name if user else "Utilisateur inconnu",
                "tim_account_type": user.tim_account_type.value if user and user.tim_account_type else None
            },
            "reference": tx.reference,
            "commission_amount": tx.commission_amount or 0,
            "merchant_name": tx.merchant_name,
            "location": tx.location
        })
    
    return {
        "transactions": transactions_data,
        "total_count": len(transactions_data)
    }

@app.get("/admin/financial-performance")
async def get_financial_performance(
    period: str = "12months",
    admin_user: User = Depends(get_admin_user), 
    db: Session = Depends(get_db)
):
    """Récupérer les données de performance financière pour le graphique"""
    from sqlalchemy import func, extract
    from datetime import datetime, timedelta
    
    # Calculer la période
    end_date = datetime.now()
    if period == "12months":
        start_date = end_date - timedelta(days=365)
        date_format = "%Y-%m"
        group_by_format = "month"
    elif period == "6months":
        start_date = end_date - timedelta(days=180)
        date_format = "%Y-%m"
        group_by_format = "month"
    else:  # 30days
        start_date = end_date - timedelta(days=30)
        date_format = "%Y-%m-%d"
        group_by_format = "day"
    
    # Récupérer les transactions par période
    if group_by_format == "month":
        # Grouper par mois
        monthly_data = db.query(
            extract('year', Transaction.created_at).label('year'),
            extract('month', Transaction.created_at).label('month'),
            func.sum(func.abs(Transaction.amount)).label('total_amount'),
            func.count(Transaction.id).label('transaction_count')
        ).filter(
            Transaction.created_at >= start_date,
            Transaction.status == TransactionStatus.COMPLETED
        ).group_by(
            extract('year', Transaction.created_at),
            extract('month', Transaction.created_at)
        ).order_by(
            extract('year', Transaction.created_at),
            extract('month', Transaction.created_at)
        ).all()
        
        # Créer les données du graphique
        chart_data = []
        month_names = ['Jan', 'Fév', 'Mar', 'Avr', 'Mai', 'Jun', 
                      'Jul', 'Aoû', 'Sep', 'Oct', 'Nov', 'Déc']
        
        # Initialiser tous les mois à 0
        current_date = start_date
        while current_date <= end_date:
            month_key = f"{current_date.year}-{current_date.month:02d}"
            chart_data.append({
                "label": month_names[current_date.month - 1],
                "value": 0,
                "change": 0
            })
            if current_date.month == 12:
                current_date = current_date.replace(year=current_date.year + 1, month=1)
            else:
                current_date = current_date.replace(month=current_date.month + 1)
        
        # Remplir avec les vraies données
        for data in monthly_data:
            year, month, total_amount, count = data
            month_index = int(month) - 1
            if 0 <= month_index < len(chart_data):
                chart_data[month_index]["value"] = float(total_amount or 0)
    
    # Calculer les métriques de performance
    total_transactions = db.query(Transaction).filter(
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).count()
    
    # Transactions par type
    nfc_transactions = db.query(Transaction).filter(
        Transaction.transaction_type.in_([TransactionType.NFC_DEPOSIT, TransactionType.NFC_WITHDRAWAL]),
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).count()
    
    mobile_loans = db.query(Transaction).filter(
        Transaction.transaction_type == TransactionType.PRET_MOBILE,
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).count()
    
    cinetpay_recharges = db.query(Transaction).filter(
        Transaction.transaction_type == TransactionType.RECHARGE_CINETPAY,
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).count()
    
    # Volume total
    total_volume = db.query(func.sum(func.abs(Transaction.amount))).filter(
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).scalar() or 0
    
    return {
        "chart_data": chart_data[-12:],  # Derniers 12 mois
        "metrics": {
            "nfc_transactions": nfc_transactions,
            "mobile_loans": mobile_loans,
            "cinetpay_recharges": cinetpay_recharges,
            "total_volume": float(total_volume),
            "total_transactions": total_transactions
        },
        "period": period
    }

@app.get("/admin/report-data")
async def get_report_data(
    period: str = "30d",
    report_type: str = "financial",
    admin_user: User = Depends(get_admin_user), 
    db: Session = Depends(get_db)
):
    """Récupérer les données complètes pour le générateur de rapport"""
    from sqlalchemy import func, extract
    from datetime import datetime, timedelta
    
    # Calculer la période
    end_date = datetime.now()
    if period == "7d":
        start_date = end_date - timedelta(days=7)
        months_back = 6
    elif period == "30d":
        start_date = end_date - timedelta(days=30)
        months_back = 6
    elif period == "90d":
        start_date = end_date - timedelta(days=90)
        months_back = 6
    else:  # 1y
        start_date = end_date - timedelta(days=365)
        months_back = 12
    
    # Données pour graphique mensuel (6 derniers mois)
    chart_data = []
    month_names = ['Jan', 'Fév', 'Mar', 'Avr', 'Mai', 'Jun', 
                  'Jul', 'Aoû', 'Sep', 'Oct', 'Nov', 'Déc']
    
    # Récupérer les données mensuelles
    # Note: MySQL ne supporte pas FILTER, on utilise CASE WHEN à la place
    from sqlalchemy import case

    monthly_stats = db.query(
        extract('year', Transaction.created_at).label('year'),
        extract('month', Transaction.created_at).label('month'),
        func.count(Transaction.id).label('transaction_count'),
        func.sum(func.abs(Transaction.amount)).label('total_volume'),
        func.sum(
            case(
                (Transaction.transaction_type.in_([TransactionType.NFC_DEPOSIT, TransactionType.NFC_WITHDRAWAL]), 1),
                else_=0
            )
        ).label('nfc_count')
    ).filter(
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).group_by(
        extract('year', Transaction.created_at),
        extract('month', Transaction.created_at)
    ).all()
    
    # Créer les données des 6 derniers mois
    current_date = end_date
    for i in range(months_back):
        month_data = {
            "month": month_names[current_date.month - 1],
            "transactions": 0,
            "volume": 0,
            "nfc": 0
        }
        
        # Chercher les données réelles pour ce mois
        for stat in monthly_stats:
            if stat.year == current_date.year and stat.month == current_date.month:
                month_data["transactions"] = int(stat.transaction_count or 0)
                month_data["volume"] = float(stat.total_volume or 0)
                month_data["nfc"] = int(stat.nfc_count or 0)
                break
        
        chart_data.insert(0, month_data)
        
        # Mois précédent
        if current_date.month == 1:
            current_date = current_date.replace(year=current_date.year - 1, month=12)
        else:
            current_date = current_date.replace(month=current_date.month - 1)
    
    # Données pour diagramme circulaire (répartition par type)
    transaction_types = db.query(
        Transaction.transaction_type,
        func.count(Transaction.id).label('count'),
        func.sum(func.abs(Transaction.amount)).label('total_amount')
    ).filter(
        Transaction.created_at >= start_date,
        Transaction.status == TransactionStatus.COMPLETED
    ).group_by(Transaction.transaction_type).all()
    
    # Calculer le total pour les pourcentages
    total_amount = sum(float(t.total_amount or 0) for t in transaction_types)
    total_count = sum(int(t.count or 0) for t in transaction_types)
    
    pie_data = []
    colors = ['#D97706', '#F59E0B', '#FBBF24', '#EF4444', '#10B981', '#3B82F6', '#8B5CF6']
    
    type_labels = {
        'NFC_DEPOSIT': 'Paiements NFC',
        'NFC_WITHDRAWAL': 'Retraits NFC',
        'RECHARGE_CINETPAY': 'Recharges CinetPay',
        'RECHARGE_BY_BUSINESS': 'Recharges Business',
        'PRET_MOBILE': 'Prêts SOS',
        'MOBILE_TRANSFER': 'Virements',
        'COMMISSION': 'Commissions',
        'TRANSFER_OUT': 'Transferts sortants',
        'TRANSFER_IN': 'Transferts entrants'
    }
    
    for i, tx_type in enumerate(transaction_types):
        amount = float(tx_type.total_amount or 0)
        percentage = (amount / total_amount * 100) if total_amount > 0 else 0
        
        pie_data.append({
            "label": type_labels.get(tx_type.transaction_type.value, tx_type.transaction_type.value),
            "value": round(percentage, 1),
            "color": colors[i % len(colors)],
            "amount": amount,
            "count": int(tx_type.count or 0)
        })
    
    # Trier par montant décroissant
    pie_data.sort(key=lambda x: x['amount'], reverse=True)
    
    # Garder seulement les 6 premiers + regrouper le reste
    if len(pie_data) > 6:
        others_amount = sum(item['amount'] for item in pie_data[6:])
        others_count = sum(item['count'] for item in pie_data[6:])
        others_percentage = (others_amount / total_amount * 100) if total_amount > 0 else 0
        
        pie_data = pie_data[:6]
        if others_amount > 0:
            pie_data.append({
                "label": "Autres",
                "value": round(others_percentage, 1),
                "color": "#6B7280",
                "amount": others_amount,
                "count": others_count
            })
    
    # Statistiques générales
    stats = {
        "total_transactions": total_count,
        "total_volume": total_amount,
        "period_days": (end_date - start_date).days,
        "avg_daily_transactions": total_count / max((end_date - start_date).days, 1),
        "avg_transaction_amount": total_amount / max(total_count, 1)
    }
    
    return {
        "chart_data": chart_data,
        "pie_data": pie_data,
        "stats": stats,
        "period": period,
        "report_type": report_type,
        "generated_at": datetime.now().isoformat()
    }
@app.post("/wallet-paiements/callback")
async def cinetpay_callback( request: Request,cpm_trans_id: str = Form(...),cpm_site_id: str = Form(...), db: Session = Depends(get_db)):

    logger.debug(request)

    if not cpm_trans_id:
        return {"status": "ignored", "message": "Missing cpm_trans_id"}
    
    if cpm_site_id != "5880897":
        raise HTTPException(status_code=400, detail="Invalid site_id")



    transaction = db.query(Transaction).filter(Transaction.reference == cpm_trans_id).first()
    if not transaction:
        print("⚠️ Transaction non trouvée :", cpm_trans_id)
        return {"status": "ignored", "message": "Transaction not found"}

    result = await cinetpay_service.check_transaction(cpm_trans_id)
    transaction_data = result.get("transaction", {})
    cinet_status = transaction_data.get("status")
    external_transaction_id = transaction_data.get("operator_id")


    if transaction.status != TransactionStatus.COMPLETED:
           
        if  cinet_status == "ACCEPTED":
           
            # Recharge wallet
            if transaction.transaction_type == TransactionType.RECHARGE_CINETPAY :
                transaction.status = TransactionStatus.COMPLETED
                transaction.completed_at = datetime.utcnow()
                transaction.external_reference = transaction_data.get('transaction_id') if transaction_data else None
                transaction.wallet = transaction_data.get('wallet') if transaction_data else None
                transaction.currency = transaction_data.get('currency') if transaction_data else None
                user = db.query(User).filter(User.id == transaction.user_id).first()
                if user:
                    loands = db.query(Loan).filter(Loan.user_id == user.id, Loan.status == LoanStatus.APPROVED).all()
                    if loands and loands[0].remaining_balance > 0:
                        for loand in loands:
                            if transaction.amount >= loand.remaining_balance:
                                transaction.amount -= loand.remaining_balance
                                loand.remaining_balance = 0
                                loand.status = LoanStatus.COMPLETED
                                loand.completed_at = datetime.utcnow()
                                transaction = Transaction(
                                    id=str(uuid.uuid4()),
                                    user_id=user.id,
                                    transaction_type=TransactionType.REMBOURSEMENT_AUTO,
                                    amount=loand.remaining_balance,
                                    status=TransactionStatus.COMPLETED,
                                    description=f"Remboursement automatique du prêt {loand.id} suite à une recharge CinetPay.",
                                    reference=f"REM-{uuid.uuid4().hex[:10].upper()}",
                                    created_at=datetime.utcnow(),
                                    completed_at=datetime.utcnow(),
                                )
                                db.add(transaction)
                                notification = Notification(
                                    id=str(uuid.uuid4()),
                                    type="Remboursement",
                                    user_id=user.id,
                                    title="Remboursement de R-online",
                                    message=f"Votre prêt R-online a été entièrement remboursé grâce à votre rechargement de {transaction.amount} FCFA.",
                                    is_read=False,
                                    created_at=datetime.utcnow()
                                )
                                db.add(notification)
                            else:
                                loand.remaining_balance -= transaction.amount
                                notification = Notification(
                                    id=str(uuid.uuid4()),
                                    type="Remboursement",
                                    user_id=user.id,
                                    title="Remboursement partiel de R-online",
                                    message=f"Un remboursement partiel de {transaction.amount} FCFA a été effectué sur votre prêt R-online grâce à votre rechargement.",
                                    is_read=False,
                                    created_at=datetime.utcnow()
                                )
                                db.add(notification)
                                transaction.amount = 0
                                break
                    
                    wallet = get_user_wallet(user, db)
                    wallet.balance += transaction.amount

                    from services.commission_service import CommissionService
                    commission_rates = CommissionService.get_recharge_commission_rates(db)
                    commission_calculation = CommissionService.calculate_commissions(transaction.amount, commission_rates)

                    admin_stock = get_admin_stock(db)
                    admin_stock.balance += commission_calculation["timcash_commission"]
                    admin_stock.total_commissions += commission_calculation["timcash_commission"]
                    notification = Notification(
                    id=str(uuid.uuid4()),
                    type="Rechargement",
                    user_id=user.id,
                    title="Rechargement de compte",
                    message=f"Votre compte a été rechargé avec succès avec une somme de {transaction.amount} FCFA.",
                    is_read=False,
                    created_at=datetime.utcnow()
                )
                db.add(notification)
            elif transaction.transaction_type == TransactionType.COMMISSION:
                transaction.status = TransactionStatus.COMPLETED
                transaction.completed_at = datetime.utcnow()
                transaction.external_reference = transaction_data.get('transaction_id') if transaction_data else None
                transaction.wallet = transaction_data.get('wallet') if transaction_data else None
                transaction.currency = transaction_data.get('currency') if transaction_data else None
                # Gérer les prêts mobiles ici
                user = db.query(User).filter(User.id == transaction.user_id).first()
                if user:
                    user.commission_payee = True
                    notification = Notification(
                        id=str(uuid.uuid4()),
                        type="Commission",
                        user_id=user.id,
                        title="Commission",
                        message=f"Votre commission de {transaction.amount} FCFA a été versée.",
                        is_read=False,
                        created_at=datetime.utcnow()
                    )
                    db.add(notification)
                    
            elif transaction.transaction_type == TransactionType.REMBOURSEMENT_AUTO:
                transaction.status = TransactionStatus.COMPLETED
                transaction.completed_at = datetime.utcnow()
                transaction.external_reference = transaction_data.get('transaction_id') if transaction_data else None
                transaction.wallet = transaction_data.get('wallet') if transaction_data else None
                transaction.currency = transaction_data.get('currency') if transaction_data else None
                # Gérer les remboursements automatiques de prêts ici
                loan = db.query(Loan).filter(Loan.user_id == transaction.user_id, Loan.status == LoanStatus.APPROVED).first()
                user = db.query(User).filter(User.id == transaction.user_id).first()
                if loan:
                    loan.remaining_balance -= transaction.amount
                    if loan.remaining_balance <= 0:
                        loan.status = LoanStatus.COMPLETED
                        loan.completed_at = datetime.utcnow()
                        notification = Notification(
                        id=str(uuid.uuid4()),
                        type="Remboursement",
                        user_id=user.id,
                        title="Remboursement de R-online",
                        message=f"Un remboursement automatique de {transaction.amount} FCFA a été effectué pour votre prêt.",
                        is_read=False,
                        created_at=datetime.utcnow()
                    )
                    db.add_all([notification, loan])
                    
                    
        elif cinet_status == "REFUSED":
            transaction.status = TransactionStatus.FAILED
    db.add(transaction)
    db.commit()
    return {"status": "success", "message": "Callback processed", "transaction_id": transaction.id}


@app.api_route("/wallet-paiements/notify", methods=["GET", "POST"])
async def transfert_notifiy( request: Request,transaction_id: str = Form(...),client_transaction_id: str = Form(...), db: Session = Depends(get_db),):

    # logger.debug(request)
    # transaction_id = request.headers.get("transaction_id")
    # client_transaction_id = request.headers.get("client_transaction_id")

    if not transaction_id:
        return {"status": "ignored", "message": "Missing transaction_id"}
   

    transaction = db.query(Transaction).filter(Transaction.reference == client_transaction_id).first()
    if not transaction:
        print("⚠️ Transaction non trouvée :", client_transaction_id)
        return {"status": "ignored", "message": "Transaction not found"}

    result = await cinetpay_service_transfert.check_transaction(client_transaction_id)
    print(result)

    # Vérification du code retour
    if result.get("code") != 0:
        transaction.status = TransactionStatus.FAILED
        db.commit()
        return {"status": "failed", "message": "CinetPay error"}

    # Récupération des données
    data_list = result.get("data", [])

    if not data_list:
        transaction.status = TransactionStatus.FAILED
        db.commit()
        return {"status": "failed", "message": "No transaction data"}

    transaction_data = data_list[0]
    cinet_status = transaction_data.get("treatment_status")

    # Validation
    if cinet_status == "VAL":
        if transaction.status != TransactionStatus.COMPLETED:
            transaction.status = TransactionStatus.COMPLETED
            transaction.wallet = transaction_data.get('wallet') if transaction_data else None
            transaction.external_reference = transaction_data.get('transaction_id') if transaction_data else None
            transaction.phone_reciveid = transaction_data.get('receiver') if transaction_data else None
            transaction.completed_at = datetime.utcnow()

            if transaction.transaction_type == TransactionType.RETRAIT:
                user = db.query(User).filter(User.id == transaction.user_id).first()
                if user:
                    notification = Notification(
                        id=str(uuid.uuid4()),
                        type="Retrait",
                        user_id=user.id,
                        title="Retrait de compte",
                        message=f"Votre retrait a été effectué avec succès d'une somme de {abs(transaction.amount)} FCFA.",
                        is_read=False,
                        created_at=datetime.utcnow()
                    )
                    db.add(notification)
    else:
        transaction.status = TransactionStatus.FAILED

    db.add(transaction)
    db.commit()

    return {
        "status": "success",
        "message": "Callback processed",
        "transaction_id": transaction.id
    }


# ==================== NFC CARDS ADMIN ENDPOINTS ====================

class NFCCardCreateRequest(BaseModel):
    card_uid: str
    card_number: str
    user_id: str
    card_type: str = "physical"
    max_balance: float = 50000.0
    is_primary: bool = False

class NFCCardUpdateStatusRequest(BaseModel):
    status: str  # active, inactive, blocked, lost, stolen
    reason: Optional[str] = None

@app.post("/admin/nfc-cards/create")
async def admin_create_nfc_card(
    request: NFCCardCreateRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    [ADMIN] Créer une nouvelle carte NFC depuis le backoffice
    La carte sera automatiquement synchronisée avec l'application mobile
    """
    from services.nfc_card_service import NFCCardService

    try:
        # Créer la carte
        card = NFCCardService.create_nfc_card(
            db=db,
            user_id=request.user_id,
            card_uid=request.card_uid,
            card_number=request.card_number,
            card_type=request.card_type,
            max_balance=request.max_balance,
            is_primary=request.is_primary
        )

        # Récupérer les informations utilisateur
        user = db.query(User).filter(User.id == request.user_id).first()

        # TODO: Notifier l'application mobile via WebSocket ou Push Notification
        # await notify_mobile_app(user.id, "card_created", card.id)

        return {
            "success": True,
            "message": "Carte NFC créée avec succès",
            "card": {
                "id": card.id,
                "card_uid": card.card_uid,
                "card_number": card.card_number,
                "user_id": card.user_id,
                "user_name": user.full_name if user else None,
                "user_phone": user.phone if user else None,
                "status": card.status,
                "balance": card.balance,
                "max_balance": card.max_balance,
                "is_primary": card.is_primary,
                "created_at": card.created_at
            }
        }
    except ValueError as e:
        raise HTTPException(status_code=400, detail=str(e))
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Erreur lors de la création de la carte: {str(e)}")

@app.put("/admin/nfc-cards/{card_id}/status")
async def admin_update_nfc_card_status(
    card_id: str,
    request: NFCCardUpdateStatusRequest,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    [ADMIN] Activer/Désactiver une carte NFC depuis le backoffice
    Le changement sera automatiquement synchronisé avec l'application mobile
    """
    from models import NFCCard

    # Vérifier que le statut est valide
    valid_statuses = ["active", "inactive", "blocked", "lost", "stolen"]
    if request.status not in valid_statuses:
        raise HTTPException(status_code=400, detail=f"Statut invalide. Valeurs acceptées: {', '.join(valid_statuses)}")

    # Récupérer la carte
    card = db.query(NFCCard).filter(NFCCard.id == card_id).first()
    if not card:
        raise HTTPException(status_code=404, detail="Carte NFC introuvable")

    # Sauvegarder l'ancien statut
    old_status = card.status

    # Mettre à jour le statut
    card.status = request.status
    card.updated_at = datetime.utcnow()

    db.commit()
    db.refresh(card)

    # Récupérer les informations utilisateur
    user = db.query(User).filter(User.id == card.user_id).first()

    # TODO: Notifier l'application mobile via WebSocket ou Push Notification
    # await notify_mobile_app(card.user_id, "card_status_changed", {
    #     "card_id": card.id,
    #     "old_status": old_status,
    #     "new_status": card.status,
    #     "reason": request.reason
    # })

    return {
        "success": True,
        "message": f"Statut de la carte mis à jour: {old_status} → {request.status}",
        "card": {
            "id": card.id,
            "card_uid": card.card_uid,
            "card_number": card.card_number,
            "user_id": card.user_id,
            "user_name": user.full_name if user else None,
            "user_phone": user.phone if user else None,
            "status": card.status,
            "balance": card.balance,
            "max_balance": card.max_balance,
            "is_primary": card.is_primary,
            "updated_at": card.updated_at
        },
        "change": {
            "old_status": old_status,
            "new_status": card.status,
            "reason": request.reason,
            "changed_by": admin_user.email,
            "changed_at": datetime.utcnow().isoformat()
        }
    }

@app.get("/admin/nfc-cards")
async def admin_get_all_nfc_cards(
    limit: int = 100,
    offset: int = 0,
    status: Optional[str] = None,
    user_id: Optional[str] = None,
    admin_user: User = Depends(get_admin_user),
    db: Session = Depends(get_db)
):
    """
    [ADMIN] Récupérer toutes les cartes NFC avec filtres
    """
    from models import NFCCard

    # Construire la requête
    query = db.query(NFCCard)

    # Filtrer par statut si spécifié
    if status:
        query = query.filter(NFCCard.status == status)

    # Filtrer par utilisateur si spécifié
    if user_id:
        query = query.filter(NFCCard.user_id == user_id)

    # Compter le total
    total_count = query.count()

    # Appliquer la pagination
    cards = query.order_by(NFCCard.created_at.desc()).offset(offset).limit(limit).all()

    # Enrichir avec les informations utilisateur
    cards_data = []
    for card in cards:
        user = db.query(User).filter(User.id == card.user_id).first()
        cards_data.append({
            "id": card.id,
            "card_uid": card.card_uid,
            "card_number": card.card_number,
            "user_id": card.user_id,
            "user_name": user.full_name if user else None,
            "user_phone": user.phone if user else None,
            "user_email": user.email if user else None,
            "status": card.status,
            "balance": card.balance,
            "max_balance": card.max_balance,
            "is_primary": card.is_primary,
            "last_used_at": card.last_used_at,
            "created_at": card.created_at,
            "updated_at": card.updated_at
        })

    return {
        "cards": cards_data,
        "total_count": total_count,
        "limit": limit,
        "offset": offset
    }
    
@celery.task
def send_notification(device_token: str):
    logger.info("starting background task")
    time.sleep(10)  # simulates slow network call to firebase/sns
    try:
        a=11/0  #critical part that may fail, and its analysis is important
        logger.info(f"notification sent {device_token}")
    except Exception as e:
        logger.error(f"exception while division {e}")


@app.get("/push/{device_token}")
async def notify(device_token: str):
    logger.info("sending notification in background")
    send_notification.delay(device_token)
    return {"message": "Notification sent"}


if __name__ == "__main__":
    include_router(app)
    uvicorn.run(app, host="0.0.0.0", port=8001)
