backend/app/models/database.py

from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy import String, Integer, ForeignKey, Float, DateTime
from app.db.session import Base
from typing import List
import datetime

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    email: Mapped[str] = mapped_column(String, unique=True, index=True)
    username: Mapped[str] = mapped_column(String, unique=True, index=True, nullable=True)
    full_name: Mapped[str] = mapped_column(String)
    avatar_url: Mapped[str] = mapped_column(String, nullable=True)
    google_id: Mapped[str] = mapped_column(String, unique=True, index=True, nullable=True)
    theme: Mapped[str] = mapped_column(String, default="dark", nullable=True)
    
    # Relationships
    indicators: Mapped[List["UserIndicator"]] = relationship("UserIndicator", back_populates="user", cascade="all, delete-orphan")
    watchlist: Mapped[List["WatchlistItem"]] = relationship("WatchlistItem", back_populates="user", cascade="all, delete-orphan")
    universe: Mapped[List["UniverseItem"]] = relationship("UniverseItem", back_populates="user", cascade="all, delete-orphan")
    ai_models: Mapped[List["UserAIModel"]] = relationship("UserAIModel", back_populates="user", cascade="all, delete-orphan")
    sessions: Mapped[List["UserSession"]] = relationship("UserSession", back_populates="user", cascade="all, delete-orphan")
    agentic_scans: Mapped[List["UserAgenticScan"]] = relationship("UserAgenticScan", back_populates="user", cascade="all, delete-orphan")

class WatchlistItem(Base):
    __tablename__ = "watchlist"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"))
    symbol: Mapped[str] = mapped_column(String, index=True)
    name: Mapped[str] = mapped_column(String)
    exchange: Mapped[str] = mapped_column(String, nullable=True)
    
    user: Mapped["User"] = relationship("User", back_populates="watchlist")

class UniverseItem(Base):
    __tablename__ = "universe"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"))
    symbol: Mapped[str] = mapped_column(String, index=True)
    name: Mapped[str] = mapped_column(String)
    last_updated: Mapped[datetime.datetime] = mapped_column(DateTime, default=datetime.datetime.utcnow)
    
    user: Mapped["User"] = relationship("User", back_populates="universe")

class HistoricalData(Base):
    __tablename__ = "historical_data"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    symbol: Mapped[str] = mapped_column(String, index=True)
    date: Mapped[datetime.datetime] = mapped_column(DateTime, index=True)
    open: Mapped[float] = mapped_column(Float)
    high: Mapped[float] = mapped_column(Float)
    low: Mapped[float] = mapped_column(Float)
    close: Mapped[float] = mapped_column(Float)
    volume: Mapped[float] = mapped_column(Float)
    # Storing indicators as JSON string for flexibility
    indicators: Mapped[str] = mapped_column(String, nullable=True)

class UserIndicator(Base):
    __tablename__ = "user_indicators"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"))
    indicator_id: Mapped[str] = mapped_column(String, index=True)
    category: Mapped[str] = mapped_column(String)
    enabled: Mapped[bool] = mapped_column(Integer, default=1) # 1 for True in SQLite
    
    user: Mapped["User"] = relationship("User", back_populates="indicators")

class UserAIModel(Base):
    __tablename__ = "user_ai_models"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"))
    name: Mapped[str] = mapped_column(String)
    model_type: Mapped[str] = mapped_column(String) # 'RandomForest', 'LSTM', etc.
    created_at: Mapped[datetime.datetime] = mapped_column(DateTime, default=datetime.datetime.utcnow)
    target_symbol: Mapped[str] = mapped_column(String)
    
    # Store configuration and results as JSON
    parameters: Mapped[str] = mapped_column(String, nullable=True) # JSON
    metrics: Mapped[str] = mapped_column(String, nullable=True) # JSON {mse: 0.1, r2: 0.8}
    file_path: Mapped[str] = mapped_column(String, nullable=True) # Path to .joblib or .h5 file

    user: Mapped["User"] = relationship("User", back_populates="ai_models")

class UserSession(Base):
    __tablename__ = "user_sessions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"))
    login_at: Mapped[datetime.datetime] = mapped_column(DateTime, default=datetime.datetime.utcnow)
    logout_at: Mapped[datetime.datetime] = mapped_column(DateTime, nullable=True)
    ip_address: Mapped[str] = mapped_column(String, nullable=True)
    user_agent: Mapped[str] = mapped_column(String, nullable=True)
    
    user: Mapped["User"] = relationship("User", back_populates="sessions")

class UserAgenticScan(Base):
    __tablename__ = "user_agentic_scans"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"))
    symbol: Mapped[str] = mapped_column(String, index=True)
    decision: Mapped[str] = mapped_column(String) # 'TRADE' or 'WAIT'
    confidence: Mapped[float] = mapped_column(Float)
    recommendation: Mapped[str] = mapped_column(String)
    entry_price: Mapped[str] = mapped_column(String, nullable=True)
    created_at: Mapped[datetime.datetime] = mapped_column(DateTime, default=datetime.datetime.utcnow)
    
    # Store full analysis as JSON
    raw_logs: Mapped[str] = mapped_column(String, nullable=True) # JSON Logs
    risk_warning: Mapped[str] = mapped_column(String, nullable=True)

    user: Mapped["User"] = relationship("User", back_populates="agentic_scans")