backend/app/database/models.py

from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime, ForeignKey, Text, JSON
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")

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")