"""
Modèles étendus pour TIM CASH - Tables supplémentaires
Fonctionnalités avancées pour une plateforme financière complète
"""
from sqlalchemy import Column, Integer, 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

# ==================== ÉNUMÉRATIONS ÉTENDUES ====================

class KYCStatus(enum.Enum):
    PENDING = "pending"
    UNDER_REVIEW = "under_review"
    APPROVED = "approved"
    REJECTED = "rejected"
    EXPIRED = "expired"

class DocumentType(enum.Enum):
    NATIONAL_ID = "national_id"
    PASSPORT = "passport"
    DRIVING_LICENSE = "driving_license"
    UTILITY_BILL = "utility_bill"
    BANK_STATEMENT = "bank_statement"
    SELFIE = "selfie"

class MerchantCategory(enum.Enum):
    RESTAURANT = "restaurant"
    RETAIL = "retail"
    PHARMACY = "pharmacy"
    FUEL_STATION = "fuel_station"
    SUPERMARKET = "supermarket"
    TRANSPORT = "transport"
    EDUCATION = "education"
    HEALTHCARE = "healthcare"
    ENTERTAINMENT = "entertainment"
    OTHER = "other"

class ProductType(enum.Enum):
    AIRTIME = "airtime"
    DATA_BUNDLE = "data_bundle"
    ELECTRICITY = "electricity"
    WATER = "water"
    INTERNET = "internet"
    TV_SUBSCRIPTION = "tv_subscription"
    INSURANCE = "insurance"
    SCHOOL_FEES = "school_fees"

class CampaignType(enum.Enum):
    CASHBACK = "cashback"
    DISCOUNT = "discount"
    REFERRAL = "referral"
    LOYALTY = "loyalty"
    SEASONAL = "seasonal"

class SupportTicketStatus(enum.Enum):
    OPEN = "open"
    IN_PROGRESS = "in_progress"
    RESOLVED = "resolved"
    CLOSED = "closed"
    ESCALATED = "escalated"

class SupportPriority(enum.Enum):
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    URGENT = "urgent"

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

# ==================== TABLES KYC ET VÉRIFICATION ====================

class KYCDocument(Base):
    """Documents KYC pour vérification d'identité"""
    __tablename__ = "kyc_documents"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    document_type = Column(Enum(DocumentType), nullable=False)
    document_number = Column(String, nullable=False)
    document_url = Column(String, nullable=False)  # URL du fichier uploadé
    status = Column(Enum(KYCStatus), default=KYCStatus.PENDING)
    verification_notes = Column(Text)
    verified_by = Column(String, ForeignKey("users.id"))
    expiry_date = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    verified_at = Column(DateTime(timezone=True))
    
    # Relations
    user = relationship("User", foreign_keys=[user_id])
    verifier = relationship("User", foreign_keys=[verified_by])

class UserProfile(Base):
    """Profil utilisateur étendu"""
    __tablename__ = "user_profiles"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), unique=True, nullable=False)
    date_of_birth = Column(DateTime(timezone=True))
    gender = Column(String(10))
    address = Column(Text)
    city_id = Column(String, ForeignKey("cities.id"))
    postal_code = Column(String(20))
    occupation = Column(String(100))
    monthly_income = Column(Float)
    emergency_contact_name = Column(String(100))
    emergency_contact_phone = Column(String(20))
    preferred_language = Column(String(10), default="fr")
    timezone = Column(String(50), default="Africa/Abidjan")
    avatar_url = Column(String)
    kyc_status = Column(Enum(KYCStatus), default=KYCStatus.PENDING)
    kyc_level = Column(Integer, default=1)  # 1=Basic, 2=Intermediate, 3=Advanced
    risk_score = Column(Float, default=0.0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relations
    user = relationship("User")
    city = relationship("City")

# ==================== TABLES MARCHANDS ET COMMERCE ====================

class Merchant(Base):
    """Marchands partenaires"""
    __tablename__ = "merchants"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    business_name = Column(String, nullable=False)
    business_registration = Column(String, unique=True)
    category = Column(Enum(MerchantCategory), nullable=False)
    description = Column(Text)
    address = Column(Text)
    city_id = Column(String, ForeignKey("cities.id"))
    latitude = Column(Float)
    longitude = Column(Float)
    phone = Column(String(20))
    email = Column(String)
    website = Column(String)
    logo_url = Column(String)
    is_verified = Column(Boolean, default=False)
    is_active = Column(Boolean, default=True)
    commission_rate = Column(Float, default=2.0)  # % commission
    total_transactions = Column(Integer, default=0)
    total_volume = Column(Float, default=0.0)
    rating = Column(Float, default=0.0)
    review_count = Column(Integer, default=0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    verified_at = Column(DateTime(timezone=True))
    
    # Relations
    user = relationship("User")
    city = relationship("City")
    products = relationship("Product", back_populates="merchant")
    reviews = relationship("MerchantReview", back_populates="merchant")

class Product(Base):
    """Produits et services disponibles"""
    __tablename__ = "products"
    
    id = Column(String, primary_key=True, index=True)
    merchant_id = Column(String, ForeignKey("merchants.id"), nullable=False)
    name = Column(String, nullable=False)
    description = Column(Text)
    product_type = Column(Enum(ProductType), nullable=False)
    price = Column(Float, nullable=False)
    currency = Column(String(10), default="FCFA")
    image_url = Column(String)
    is_active = Column(Boolean, default=True)
    stock_quantity = Column(Integer)
    min_purchase = Column(Float, default=0.0)
    max_purchase = Column(Float)
    commission_rate = Column(Float, default=1.0)
    total_sales = Column(Integer, default=0)
    total_revenue = Column(Float, default=0.0)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relations
    merchant = relationship("Merchant", back_populates="products")
    purchases = relationship("ProductPurchase", back_populates="product")

class ProductPurchase(Base):
    """Achats de produits/services"""
    __tablename__ = "product_purchases"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    product_id = Column(String, ForeignKey("products.id"), nullable=False)
    merchant_id = Column(String, ForeignKey("merchants.id"), nullable=False)
    quantity = Column(Integer, default=1)
    unit_price = Column(Float, nullable=False)
    total_amount = Column(Float, nullable=False)
    commission_amount = Column(Float, default=0.0)
    status = Column(String, default="completed")
    reference = Column(String, unique=True, index=True)
    recipient_info = Column(JSON)  # Pour airtime, etc.
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    user = relationship("User")
    product = relationship("Product", back_populates="purchases")
    merchant = relationship("Merchant")

class MerchantReview(Base):
    """Avis sur les marchands"""
    __tablename__ = "merchant_reviews"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    merchant_id = Column(String, ForeignKey("merchants.id"), nullable=False)
    rating = Column(Integer, nullable=False)  # 1-5 étoiles
    comment = Column(Text)
    is_verified_purchase = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    user = relationship("User")
    merchant = relationship("Merchant", back_populates="reviews")

# ==================== TABLES MARKETING ET FIDÉLITÉ ====================

class Campaign(Base):
    """Campagnes marketing et promotions"""
    __tablename__ = "campaigns"
    
    id = Column(String, primary_key=True, index=True)
    name = Column(String, nullable=False)
    description = Column(Text)
    campaign_type = Column(Enum(CampaignType), nullable=False)
    target_audience = Column(JSON)  # Critères de ciblage
    reward_type = Column(String)  # cashback, discount, points
    reward_value = Column(Float)  # Montant ou pourcentage
    min_transaction_amount = Column(Float, default=0.0)
    max_reward_per_user = Column(Float)
    total_budget = Column(Float)
    spent_budget = Column(Float, default=0.0)
    start_date = Column(DateTime(timezone=True))
    end_date = Column(DateTime(timezone=True))
    is_active = Column(Boolean, default=True)
    participants_count = Column(Integer, default=0)
    total_rewards_given = Column(Float, default=0.0)
    created_by = Column(String, ForeignKey("users.id"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    creator = relationship("User")
    participations = relationship("CampaignParticipation", back_populates="campaign")

class CampaignParticipation(Base):
    """Participation aux campagnes"""
    __tablename__ = "campaign_participations"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    campaign_id = Column(String, ForeignKey("campaigns.id"), nullable=False)
    transaction_id = Column(String, ForeignKey("transactions.id"))
    reward_earned = Column(Float, default=0.0)
    status = Column(String, default="pending")  # pending, approved, paid
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    user = relationship("User")
    campaign = relationship("Campaign", back_populates="participations")

class LoyaltyProgram(Base):
    """Programme de fidélité"""
    __tablename__ = "loyalty_programs"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), unique=True, nullable=False)
    points_balance = Column(Integer, default=0)
    tier_level = Column(String, default="Bronze")  # Bronze, Silver, Gold, Platinum
    total_points_earned = Column(Integer, default=0)
    total_points_redeemed = Column(Integer, default=0)
    referral_count = Column(Integer, default=0)
    last_activity = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    user = relationship("User")

class Referral(Base):
    """Système de parrainage"""
    __tablename__ = "referrals"
    
    id = Column(String, primary_key=True, index=True)
    referrer_id = Column(String, ForeignKey("users.id"), nullable=False)
    referred_id = Column(String, ForeignKey("users.id"), nullable=False)
    referral_code = Column(String, unique=True, index=True)
    status = Column(String, default="pending")  # pending, completed, rewarded
    reward_amount = Column(Float, default=0.0)
    conditions_met = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    completed_at = Column(DateTime(timezone=True))
    
    # Relations
    referrer = relationship("User", foreign_keys=[referrer_id])
    referred = relationship("User", foreign_keys=[referred_id])

# ==================== TABLES SUPPORT ET COMMUNICATION ====================

class SupportTicket(Base):
    """Tickets de support client"""
    __tablename__ = "support_tickets"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    assigned_to = Column(String, ForeignKey("users.id"))
    subject = Column(String, nullable=False)
    description = Column(Text, nullable=False)
    category = Column(String)  # technical, billing, account, etc.
    priority = Column(Enum(SupportPriority), default=SupportPriority.MEDIUM)
    status = Column(Enum(SupportTicketStatus), default=SupportTicketStatus.OPEN)
    resolution = Column(Text)
    satisfaction_rating = Column(Integer)  # 1-5
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    resolved_at = Column(DateTime(timezone=True))
    
    # Relations
    user = relationship("User", foreign_keys=[user_id])
    agent = relationship("User", foreign_keys=[assigned_to])
    messages = relationship("SupportMessage", back_populates="ticket")

class SupportMessage(Base):
    """Messages dans les tickets de support"""
    __tablename__ = "support_messages"
    
    id = Column(String, primary_key=True, index=True)
    ticket_id = Column(String, ForeignKey("support_tickets.id"), nullable=False)
    sender_id = Column(String, ForeignKey("users.id"), nullable=False)
    message = Column(Text, nullable=False)
    is_internal = Column(Boolean, default=False)  # Note interne agent
    attachments = Column(JSON)  # URLs des fichiers joints
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    ticket = relationship("SupportTicket", back_populates="messages")
    sender = relationship("User")

# ==================== TABLES ANALYTICS ET REPORTING ====================

class UserSession(Base):
    """Sessions utilisateur pour analytics"""
    __tablename__ = "user_sessions"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"))
    session_token = Column(String, unique=True, index=True)
    device_info = Column(JSON)
    ip_address = Column(String)
    user_agent = Column(Text)
    location = Column(JSON)  # Géolocalisation
    started_at = Column(DateTime(timezone=True), server_default=func.now())
    ended_at = Column(DateTime(timezone=True))
    duration_minutes = Column(Integer)
    pages_visited = Column(Integer, default=0)
    actions_performed = Column(Integer, default=0)
    
    # Relations
    user = relationship("User")

class TransactionAnalytics(Base):
    """Analytics détaillées des transactions"""
    __tablename__ = "transaction_analytics"
    
    id = Column(String, primary_key=True, index=True)
    transaction_id = Column(String, ForeignKey("transactions.id"), unique=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    merchant_id = Column(String, ForeignKey("merchants.id"))
    transaction_date = Column(DateTime(timezone=True))
    hour_of_day = Column(Integer)
    day_of_week = Column(Integer)
    month = Column(Integer)
    year = Column(Integer)
    amount_range = Column(String)  # 0-1K, 1K-5K, 5K-10K, etc.
    device_type = Column(String)  # mobile, web, nfc
    location_city = Column(String)
    location_country = Column(String)
    is_first_transaction = Column(Boolean, default=False)
    time_since_last_transaction = Column(Integer)  # minutes
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    user = relationship("User")
    merchant = relationship("Merchant")

class SystemMetrics(Base):
    """Métriques système en temps réel"""
    __tablename__ = "system_metrics"
    
    id = Column(String, primary_key=True, index=True)
    metric_name = Column(String, nullable=False)
    metric_value = Column(Float, nullable=False)
    metric_unit = Column(String)  # count, percentage, fcfa, seconds
    category = Column(String)  # performance, business, technical
    timestamp = Column(DateTime(timezone=True), server_default=func.now())
    additional_data = Column(JSON)  # Données supplémentaires

# ==================== TABLES SÉCURITÉ ET AUDIT ====================

class SecurityEvent(Base):
    """Événements de sécurité"""
    __tablename__ = "security_events"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"))
    event_type = Column(String, nullable=False)  # login_failed, suspicious_transaction, etc.
    severity = Column(String, default="medium")  # low, medium, high, critical
    description = Column(Text)
    ip_address = Column(String)
    user_agent = Column(Text)
    location = Column(JSON)
    additional_data = Column(JSON)
    is_resolved = Column(Boolean, default=False)
    resolved_by = Column(String, ForeignKey("users.id"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    resolved_at = Column(DateTime(timezone=True))
    
    # Relations
    user = relationship("User", foreign_keys=[user_id])
    resolver = relationship("User", foreign_keys=[resolved_by])

class AuditLog(Base):
    """Journal d'audit complet"""
    __tablename__ = "audit_logs"
    
    id = Column(String, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.id"))
    action = Column(String, nullable=False)
    resource_type = Column(String)  # user, transaction, wallet, etc.
    resource_id = Column(String)
    old_values = Column(JSON)
    new_values = Column(JSON)
    ip_address = Column(String)
    user_agent = Column(Text)
    timestamp = Column(DateTime(timezone=True), server_default=func.now())
    
    # Relations
    user = relationship("User")

# ==================== TABLES CONFIGURATION ====================

class SystemConfiguration(Base):
    """Configuration système"""
    __tablename__ = "system_configurations"
    
    id = Column(String, primary_key=True, index=True)
    key = Column(String, unique=True, nullable=False)
    value = Column(Text, nullable=False)
    data_type = Column(String, default="string")  # string, integer, float, boolean, json
    category = Column(String)  # limits, rates, features, etc.
    description = Column(Text)
    is_sensitive = Column(Boolean, default=False)  # Masquer dans l'interface
    updated_by = Column(String, ForeignKey("users.id"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relations
    updater = relationship("User")

class FeatureFlag(Base):
    """Flags de fonctionnalités"""
    __tablename__ = "feature_flags"
    
    id = Column(String, primary_key=True, index=True)
    name = Column(String, unique=True, nullable=False)
    description = Column(Text)
    is_enabled = Column(Boolean, default=False)
    rollout_percentage = Column(Float, default=0.0)  # 0-100%
    target_users = Column(JSON)  # Liste d'IDs utilisateurs
    target_countries = Column(JSON)
    start_date = Column(DateTime(timezone=True))
    end_date = Column(DateTime(timezone=True))
    created_by = Column(String, ForeignKey("users.id"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # Relations
    creator = relationship("User")
