"""
Modèles de base de données TIM CASH (corrigés)
"""
from sqlalchemy import (
    Column, Integer, Date, String, Float, DateTime, Boolean, Text, ForeignKey, Enum, JSON
)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database import Base
import enum
from datetime import datetime
import random
import string

# ==========================
# ENUMS
# ==========================
class TimAccountType(enum.Enum):
    TIM_MINI = "TIM_MINI"
    TIM_MAXI = "TIM_MAXI"
    TIM_BUSINESS = "TIM_BUSINESS"


class TransactionStatus(enum.Enum):
    PENDING = "pending"
    COMPLETED = "completed"
    FAILED = "failed"
    CANCELLED = "cancelled"


class TransactionType(enum.Enum):
    NFC_DEPOSIT = "nfc_deposit"
    NFC_WITHDRAWAL = "nfc_withdrawal"
    RECHARGE_CINETPAY = "recharge_cinetpay"
    RECHARGE_BY_BUSINESS = "recharge_by_business"
    PRET_MOBILE = "pret_mobile"
    COMMISSION = "commission"
    REMBOURSEMENT_AUTO = "remboursement_auto"
    VIREMENT_BANCAIRE = "virement_bancaire"
    MOBILE_TRANSFER = "mobile_transfer"
    PRELEVEMENT = "prelevement"
    DEPOT = "depot"
    RECHARGEMENT = "rechargement"
    RETRAIT = "retrait"
    TRANSFERT = "transfert"
    PAIEMENT = "paiement"
    REMBOURSEMENT = "remboursement"
    DECAISSEMENT = "decaissement"


class LoanStatus(enum.Enum):
    PENDING = "pending"
    APPROVED = "approved"
    ACTIVE = "active"
    COMPLETED = "completed"
    OVERDUE = "overdue"
    REJECTED = "rejected"
    PAYMER = "payer"


class PaymentMethod(enum.Enum):
    MOBILE_MONEY = "mobile_money"
    CARD = "card"
    BANK_TRANSFER = "bank_transfer"


class NotificationPriority(enum.Enum):
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"


class GenreEnum(str, enum.Enum):
    HOMME = "homme"
    FEMME = "femme"
    AUTRE = "autre"

# ==========================
# MODELS
# ==========================

class User(Base):
    __tablename__ = "users"

    id = Column(String, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=True)
    username = Column(String, unique=True, index=True, nullable=True)
    full_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    genre = Column(Enum(GenreEnum), nullable=True)
    date_naissance = Column(Date, nullable=True)
    phone = Column(String, unique=True, index=True, nullable=True)
    hashed_password = Column(String, nullable=True)
    commission_payee = Column(Boolean, default=False)

    # Photos
    photo_profile = Column(String, nullable=True)

    is_active = Column(Boolean, default=True)
    otp_secret = Column(String, nullable=True)
    tim_account_type = Column(Enum(TimAccountType), default=TimAccountType.TIM_MINI)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    # Relations
    country_id = Column(String, ForeignKey("countries.id"), nullable=False)
    country = relationship("Country", back_populates="users")
    wallet = relationship("Wallet", back_populates="user", uselist=False)
    transactions = relationship("Transaction", back_populates="user")
    loans = relationship("Loan", back_populates="user")
    notifications = relationship("Notification", back_populates="user")
    kyc_verifications = relationship("KYCVerification", back_populates="user")
    nfc_cards = relationship("NFCCard", back_populates="user")
    cards = relationship("Card", back_populates="user")
    cards_virtuals = relationship("CardVirtualUser", back_populates="user")
    device_tokens = relationship("UserDeviceToken", back_populates="user")
    prelevements_merchant = relationship("PrelevementRequest", foreign_keys='PrelevementRequest.merchant_id', backref='merchant')
    prelevements_client = relationship("PrelevementRequest", foreign_keys='PrelevementRequest.client_id', backref='client')
    
    

class AdminUser(Base):
    __tablename__ = "admin_users"

    id = Column(String, primary_key=True, index=True)
    full_name = Column(String, nullable=False, index=True)
    phone_number = Column(String, unique=True, index=True)
    email = Column(String, unique=True)
    password = Column(String)  # tu pourras le hasher après
    role = Column(String, default="admin") 
    is_admin = Column(Boolean, default=False)# superadmin, editor, etc.
    active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())


class Wallet(Base):
    __tablename__ = "wallets"

    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), unique=True, nullable=False)
    balance = Column(Float, default=0.0)
    max_balance = Column(Float, nullable=True)
    is_active = Column(Boolean, default=True)
    nfc_enabled = Column(Boolean, default=True)
    currency = Column(String(10), default="FCFA")
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    user = relationship("User", back_populates="wallet")


class Transaction(Base):
    __tablename__ = "transactions"

    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    transaction_type = Column(Enum(TransactionType), nullable=False)
    amount = Column(Float, nullable=False)
    commission_amount = Column(Float, default=0.0)
    commission_rate = Column(Float, default=0.0)
    status = Column(Enum(TransactionStatus), default=TransactionStatus.PENDING)
    description = Column(Text)
    reference = Column(String, unique=True, index=True)
    external_reference = Column(String)
    payment_method = Column(Enum(PaymentMethod))
    wallet = Column(String)
    currency = Column(String(10), default="FCFA")
    merchant_name = Column(String)
    phone_reciveid = Column(String)
    location = Column(String)
    device_info = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    completed_at = Column(DateTime(timezone=True))

    # Relations
    user = relationship("User", back_populates="transactions")

    # 🔗 Relation inverse avec Notification
    notifications = relationship("Notification", back_populates="transaction", lazy="select")
    
class PrelevementRequest(Base):
    __tablename__ = "prelevement"

    id = Column(String, primary_key=True)
    merchant_id = Column(String, ForeignKey("users.id"), nullable=False)
    client_id = Column(String, ForeignKey("users.id"), nullable=False)
    notifications_id = Column(String, ForeignKey("notifications.id"), nullable=True)  # <--
    amount = Column(Float, nullable=False)
    description = Column(String(255))
    status = Column(String, default="pending")  # pending, approved, rejected, completed
    created_at = Column(DateTime, default=datetime.utcnow)


class Loan(Base):
    __tablename__ = "loans"

    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    amount = Column(Float, nullable=False)
    interest_rate = Column(Float, default=6.0)
    penalty_rate = Column(Float, default=15.0)
    remaining_balance = Column(Float, nullable=False)
    monthly_payment = Column(Float, nullable=False)
    total_payments = Column(Integer, nullable=False)
    payments_completed = Column(Integer, default=0)
    status = Column(Enum(LoanStatus), default=LoanStatus.PENDING)
    due_date = Column(DateTime(timezone=True))
    days_overdue = Column(Integer, default=0)
    guarantee_deposit = Column(Float, default=0.0)
    deposit_paid = Column(Boolean, default=False)
    auto_approved = Column(Boolean, default=False)
    wallet_credited = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    approved_at = Column(DateTime(timezone=True))

    user = relationship("User", back_populates="loans")


class AdminStock(Base):
    __tablename__ = "admin_stock"

    id = Column(Integer, primary_key=True, index=True)
    balance = Column(Float, default=847000000.0)
    total_credits = Column(Float, default=0.0)
    total_debits = Column(Float, default=0.0)
    total_commissions = Column(Float, default=0.0)
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())


class Country(Base):
    __tablename__ = "countries"

    id = Column(String, primary_key=True, index=True)
    name = Column(String, nullable=False)
    code = Column(String(3), unique=True, nullable=False)
    currency = Column(String, default="FCFA")
    currency_symbol = Column(String, default="FCFA")
    flag = Column(String)
    is_active = Column(Boolean, default=True)
    total_users = Column(Integer, default=0)
    total_volume = Column(Float, default=0.0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    cities = relationship("City", back_populates="country")
    users = relationship("User", back_populates="country")


class City(Base):
    __tablename__ = "cities"

    id = Column(String, primary_key=True, index=True)
    name = Column(String, nullable=False)
    country_id = Column(String, ForeignKey("countries.id"), nullable=False)
    branch_code = Column(String, unique=True)
    is_active = Column(Boolean, default=True)
    users = Column(Integer, default=0)
    volume = Column(Float, default=0.0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    country = relationship("Country", back_populates="cities")


class Advertisement(Base):
    __tablename__ = "advertisements"

    id = Column(String, primary_key=True, index=True)
    title = Column(String, nullable=False)
    description = Column(Text)
    image_url = Column(String)
    ad_type = Column(String)
    status = Column(String, default="active")
    target_countries = Column(Text)
    target_cities = Column(Text)
    start_date = Column(DateTime(timezone=True))
    end_date = Column(DateTime(timezone=True))
    budget = Column(Float, default=0.0)
    spent = Column(Float, default=0.0)
    impressions = Column(Integer, default=0)
    clicks = Column(Integer, default=0)
    ctr = Column(Float, default=0.0)
    created_by = Column(String, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class CommissionRate(Base):
    __tablename__ = "commission_rates"

    id = Column(String, primary_key=True, index=True)
    name = Column(String, nullable=False)
    rate_type = Column(String, nullable=False)
    value = Column(Float, nullable=False)
    category = Column(String, nullable=False)
    description = Column(Text)
    min_amount = Column(Float)
    max_amount = Column(Float)
    is_active = Column(Boolean, default=True)
    updated_by = Column(String, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())


class Notification(Base):
    __tablename__ = "notifications"

    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    title = Column(String, nullable=False)
    message = Column(Text, nullable=False)
    type = Column(String, nullable=False)
    priority = Column(Enum(NotificationPriority), default=NotificationPriority.MEDIUM)
    is_read = Column(Boolean, default=False)
    action_url = Column(String)
    amount = Column(Float)
    merchant = Column(String)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    read_at = Column(DateTime(timezone=True))

    # 🔗 Ajout du lien vers la transaction
    transaction_id = Column(String, ForeignKey("transactions.id"), nullable=True)
    prelevement_id = Column(String, ForeignKey("prelevement.id"), nullable=True)  # <--
    reference = Column(String, nullable=True)

    # Relations
    user = relationship("User", back_populates="notifications")
    transaction = relationship("Transaction", back_populates="notifications", lazy="joined")

class UserDeviceToken(Base):
    __tablename__ = "user_device_tokens"

    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    device_token = Column(String, nullable=False, unique=True)
    device_type = Column(String, nullable=False)
    device_name = Column(String)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    last_used = Column(DateTime(timezone=True), server_default=func.now())

    user = relationship("User", back_populates="device_tokens")


class DeviceCapability(Base):
    __tablename__ = "device_capabilities"

    id = Column(String, primary_key=True, index=True)
    device_model = Column(String, nullable=False)
    brand = Column(String, nullable=False)
    has_nfc = Column(Boolean, default=False)
    nfc_enabled = Column(Boolean, default=False)
    os_version = Column(String)
    user_count = Column(Integer, default=0)
    transaction_count = Column(Integer, default=0)
    success_rate = Column(Float, default=0.0)
    location = Column(String)
    country = Column(String)
    city = Column(String)
    last_seen = Column(DateTime(timezone=True), server_default=func.now())


class Workflow(Base):
    __tablename__ = "workflows"

    id = Column(String, primary_key=True, index=True)
    name = Column(String, nullable=False)
    description = Column(Text)
    status = Column(String, default="draft")
    category = Column(String, nullable=False)
    triggers = Column(Integer, default=0)
    executions = Column(Integer, default=0)
    success_rate = Column(Float, default=0.0)
    steps = Column(Text)
    last_run = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())


class NFCCard(Base):
    __tablename__ = "nfc_cards"

    id = Column(String, primary_key=True, index=True)
    card_uid = Column(String, unique=True, nullable=False, index=True)
    card_number = Column(String, unique=True, nullable=False, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    card_type = Column(String, default="physical")
    status = Column(String, default="active")
    balance = Column(Float, default=0.0)
    max_balance = Column(Float, default=50000.0)
    is_primary = Column(Boolean, default=False)
    last_used_at = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    user = relationship("User", back_populates="nfc_cards")


class NFCCardTransaction(Base):
    __tablename__ = "nfc_card_transactions"

    id = Column(String, primary_key=True, index=True)
    card_id = Column(String, ForeignKey("nfc_cards.id"), nullable=False)
    transaction_id = Column(String, ForeignKey("transactions.id"), nullable=False)
    operation_type = Column(String, nullable=False)
    card_data = Column(Text)
    device_info = Column(Text)
    location = Column(String)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    card = relationship("NFCCard")
    transaction = relationship("Transaction")


class Card(Base):
    __tablename__ = "cards"

    id = Column(String(8), primary_key=True, index=True)
    phone = Column(String(50), unique=True, nullable=False, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    status = Column(String(20), default="active")
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    user = relationship("User", back_populates="cards")

    def __repr__(self):
        return f"<Card(id={self.id}, uid={self.uid}, user_id={self.user_id}, status={self.status})>"

    @staticmethod
    def generate_card_id():
        return ''.join(random.choices(string.digits, k=8))


class CardVirtual(Base):
    __tablename__ = "cards_virtual"

    id = Column(String(8), primary_key=True, index=True, default=lambda: ''.join(random.choices(string.digits, k=8)))
    price = Column(Float, default=0.0)
    type_card = Column(String, nullable=False)
    currency = Column(String, nullable=False)
    status = Column(String(20), default="active")
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    def __repr__(self):
        return f"<Card(id={self.id}, ustatus={self.status})>"

    @staticmethod
    def generate_card_id():
        return ''.join(random.choices(string.digits, k=8))

class CardVirtualUser(Base):
    __tablename__ = "cards_virtual_user"

    id = Column(String(8), primary_key=True, index=True, default=lambda: ''.join(random.choices(string.digits, k=8)))
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    card_id = Column(String(8), nullable=False)
    acquired_at = Column(DateTime(timezone=True), server_default=func.now())

    user = relationship("User", back_populates="cards_virtuals")
    
class KYCVerification(Base):
    __tablename__ = "kyc_verifications"

    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    document_front_image = Column(String, nullable=False)
    document_back_image = Column(String)
    status = Column(String, default="pending")
    kyc_active = Column(Boolean, default= False)
    submitted_at = Column(DateTime(timezone=True), server_default=func.now())
    reviewed_at = Column(DateTime(timezone=True))
    reviewer_id = Column(String)

    user = relationship("User")