backend/app/database/models.py

from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime, ForeignKey, Text, JSON, LargeBinary
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.database.database import Base
import uuid

def generate_uuid():
    return str(uuid.uuid4())

class User(Base):
    __tablename__ = "users"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    email = Column(String, unique=True, index=True, nullable=False)
    password_hash = Column(String, nullable=False)
    profile_data = Column(JSON, default={})
    financial_personality = Column(String, default="Unknown")
    ai_personalization_settings = Column(JSON, default={})
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    accounts = relationship("Account", back_populates="user")
    subscriptions = relationship("Subscription", back_populates="user")
    goals = relationship("Goal", back_populates="user")
    investments = relationship("Investment", back_populates="user")
    ai_insights = relationship("AIInsight", back_populates="user")
    notifications = relationship("Notification", back_populates="user")
    analytics_snapshots = relationship("AnalyticsSnapshot", back_populates="user")
    payments = relationship("Payment", back_populates="user")
    chat_sessions = relationship("ChatSession", back_populates="user", cascade="all, delete-orphan")
    preferences = relationship("UserPreference", back_populates="user", uselist=False, cascade="all, delete-orphan")
    documents = relationship("UploadedDocument", back_populates="user", cascade="all, delete-orphan")

class ChatSession(Base):
    __tablename__ = "chat_sessions"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False, index=True)
    title = Column(String, default="New chat", nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    user = relationship("User", back_populates="chat_sessions")
    messages = relationship("ChatMessage", back_populates="session", cascade="all, delete-orphan")

class ChatMessage(Base):
    __tablename__ = "chat_messages"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False, index=True)
    session_id = Column(String, ForeignKey("chat_sessions.id"), nullable=True, index=True)
    role = Column(String, nullable=False)  # user | assistant
    content = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    session = relationship("ChatSession", back_populates="messages")

class Account(Base):
    __tablename__ = "accounts"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    type = Column(String, nullable=False) # e.g. checking, savings
    balance = Column(Float, default=0.0)
    currency = Column(String, default="USD")
    status = Column(String, default="active")

    user = relationship("User", back_populates="accounts")
    transactions = relationship("Transaction", back_populates="account")

class Transaction(Base):
    __tablename__ = "transactions"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    account_id = Column(String, ForeignKey("accounts.id"), nullable=False)
    amount = Column(Float, nullable=False)
    type = Column(String, nullable=False) # credit, debit
    category = Column(String)
    timestamp = Column(DateTime(timezone=True), server_default=func.now())
    merchant = Column(String)
    tags = Column(JSON, default=[])
    ai_generated_metadata = Column(JSON, default={})
    spending_emotion_label = Column(String)

    account = relationship("Account", back_populates="transactions")
    fraud_log = relationship("FraudLog", back_populates="transaction", uselist=False)

class Subscription(Base):
    __tablename__ = "subscriptions"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    merchant = Column(String, nullable=False)
    amount = Column(Float, nullable=False)
    billing_cycle = Column(String, nullable=False) # monthly, yearly
    active = Column(Boolean, default=True)
    ai_usage_detection = Column(JSON, default={})

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

class Goal(Base):
    __tablename__ = "goals"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    title = Column(String, nullable=False)
    target_amount = Column(Float, nullable=False)
    current_amount = Column(Float, default=0.0)
    target_date = Column(DateTime(timezone=True))
    ai_generated_plan = Column(JSON, default={})

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

class Investment(Base):
    __tablename__ = "investments"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    asset_name = Column(String, nullable=False)
    type = Column(String, nullable=False) # stock, crypto, mutual_fund
    amount_invested = Column(Float, default=0.0)
    current_value = Column(Float, default=0.0)
    portfolio_allocation = Column(Float, default=0.0)
    ai_risk_analysis = Column(JSON, default={})

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

class AIInsight(Base):
    __tablename__ = "ai_insights"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    type = Column(String, nullable=False) # recommendation, briefing, cashflow
    content = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

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

class FraudLog(Base):
    __tablename__ = "fraud_logs"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    transaction_id = Column(String, ForeignKey("transactions.id"), nullable=False)
    risk_score = Column(Float, nullable=False)
    suspicious_activity_details = Column(Text)
    status = Column(String, default="pending") # pending, resolved, false_positive

    transaction = relationship("Transaction", back_populates="fraud_log")

class Notification(Base):
    __tablename__ = "notifications"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    title = Column(String, nullable=False)
    message = Column(Text, nullable=False)
    type = Column(String, nullable=False) # alert, insight, warning
    read_status = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

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

class AnalyticsSnapshot(Base):
    __tablename__ = "analytics_snapshots"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    date = Column(DateTime(timezone=True), nullable=False)
    total_balance = Column(Float, default=0.0)
    total_spending = Column(Float, default=0.0)
    total_savings = Column(Float, default=0.0)
    financial_score = Column(Float, default=0.0)
    trends_json = Column(JSON, default={})

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


class Payment(Base):
    __tablename__ = "payments"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    amount = Column(Float, nullable=False)
    currency = Column(String, default="USD")
    recipient_name = Column(String, nullable=False)
    recipient_account = Column(String, nullable=False)
    payment_type = Column(String, nullable=False)   # transfer|bill_payment|subscription|incoming|outgoing
    status = Column(String, default="pending")       # pending|completed|failed|flagged
    risk_score = Column(Float, default=0.0)          # 0–100
    fraud_flag = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    transaction_reference = Column(String, nullable=True)
    note = Column(String, nullable=True)
    ai_insight = Column(Text, nullable=True)

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


# ─── User Preferences (theme + language) ─────────────────────────────────────
class UserPreference(Base):
    __tablename__ = "user_preferences"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False, unique=True, index=True)
    theme = Column(String, default="dark")          # dark | light
    language = Column(String, default="en")         # en | hi | mr
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

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


# ─── Uploaded Documents ───────────────────────────────────────────────────────
class UploadedDocument(Base):
    __tablename__ = "uploaded_documents"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False, index=True)
    filename = Column(String, nullable=False)
    file_type = Column(String, nullable=False)       # pdf | docx | txt | csv
    file_size = Column(Integer, default=0)
    extracted_text = Column(Text, nullable=True)
    ai_summary = Column(Text, nullable=True)
    ai_insights = Column(JSON, default=[])
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    user = relationship("User", back_populates="documents")
    doc_messages = relationship("DocumentMessage", back_populates="document", cascade="all, delete-orphan")


# ─── Document Chat Messages ───────────────────────────────────────────────────
class DocumentMessage(Base):
    __tablename__ = "document_messages"

    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    user_id = Column(String, ForeignKey("users.id"), nullable=False, index=True)
    document_id = Column(String, ForeignKey("uploaded_documents.id"), nullable=False, index=True)
    role = Column(String, nullable=False)            # user | assistant
    content = Column(Text, nullable=False)
    language = Column(String, default="en")
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    document = relationship("UploadedDocument", back_populates="doc_messages")