import uuid import datetime from typing import List, Optional from sqlalchemy import String, Integer, Float, DateTime, ForeignKey, UniqueConstraint, BigInteger, func, Boolean from sqlalchemy.orm import Mapped, mapped_column, relationship from backend.app.database.session import Base class User(Base): __tablename__ = "users" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False) full_name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) google_id: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False) picture_url: Mapped[Optional[str]] = mapped_column(String(500), nullable=True) # Email Verification fields is_verified: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) verification_code: Mapped[Optional[str]] = mapped_column(String(10), nullable=True) verification_code_expires_at: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime(timezone=True), nullable=True) # Credit SaaS parameters credits: Mapped[int] = mapped_column(Integer, default=5, nullable=False) last_credit_refresh: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) created_at: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Subscription parameters subscription_tier: Mapped[str] = mapped_column(String(50), default="free", nullable=False) messages_remaining: Mapped[int] = mapped_column(Integer, default=3, nullable=False) monthly_messages_used: Mapped[int] = mapped_column(Integer, default=0, nullable=False) last_billing_date: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationships watchlists: Mapped[List["Watchlist"]] = relationship(back_populates="user", cascade="all, delete-orphan") alerts: Mapped[List["Alert"]] = relationship(back_populates="user", cascade="all, delete-orphan") transactions: Mapped[List["PaymentTransaction"]] = relationship(back_populates="user", cascade="all, delete-orphan") saved_strategies: Mapped[List["SavedStrategy"]] = relationship(back_populates="user", cascade="all, delete-orphan") prediction_logs: Mapped[List["PredictionLog"]] = relationship(back_populates="user", cascade="all, delete-orphan") strategy_logs: Mapped[List["StrategyLog"]] = relationship(back_populates="user", cascade="all, delete-orphan") class PaymentTransaction(Base): __tablename__ = "payment_transactions" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False) # Razorpay Specifics razorpay_order_id: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False) razorpay_payment_id: Mapped[Optional[str]] = mapped_column(String(255), unique=True, index=True, nullable=True) amount: Mapped[int] = mapped_column(Integer, nullable=False) # in paisa (e.g., 10000 = ₹100) status: Mapped[str] = mapped_column(String(50), default="created", nullable=False) # created, captured, failed credits_credited: Mapped[int] = mapped_column(Integer, nullable=False) created_at: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationship user: Mapped["User"] = relationship(back_populates="transactions") class Watchlist(Base): __tablename__ = "watchlists" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) ticker: Mapped[str] = mapped_column(String(50), nullable=False, index=True) created_at: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationship user: Mapped["User"] = relationship(back_populates="watchlists") class Alert(Base): __tablename__ = "alerts" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) ticker: Mapped[str] = mapped_column(String(50), nullable=False, index=True) target_price: Mapped[float] = mapped_column(Float, nullable=False) condition: Mapped[str] = mapped_column(String(20), nullable=False) # "above" or "below" is_active: Mapped[bool] = mapped_column(default=True, nullable=False) is_triggered: Mapped[bool] = mapped_column(default=False, nullable=False) last_notified_at: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime(timezone=True), nullable=True) created_at: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationship user: Mapped["User"] = relationship(back_populates="alerts") class StockHistory(Base): __tablename__ = "stock_history" id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True) ticker: Mapped[str] = mapped_column(String(50), nullable=False, index=True) timestamp: Mapped[datetime.datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True) # Candlestick values open: Mapped[float] = mapped_column(Float, nullable=False) high: Mapped[float] = mapped_column(Float, nullable=False) low: Mapped[float] = mapped_column(Float, nullable=False) close: Mapped[float] = mapped_column(Float, nullable=False) volume: Mapped[int] = mapped_column(BigInteger, nullable=False) # Prevent duplicate records for the same ticker at the same timestamp __table_args__ = ( UniqueConstraint("ticker", "timestamp", name="uq_ticker_timestamp"), ) class SavedStrategy(Base): __tablename__ = "saved_strategies" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) ticker: Mapped[str] = mapped_column(String(50), nullable=False, index=True) bullish_probability: Mapped[int] = mapped_column(Integer, nullable=False) reason: Mapped[str] = mapped_column(String(5000), nullable=False) created_at: Mapped[datetime.datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationship user: Mapped["User"] = relationship(back_populates="saved_strategies") class PredictionLog(Base): __tablename__ = "prediction_logs" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) ticker: Mapped[str] = mapped_column(String(50), index=True) timestamp: Mapped[datetime.datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True) # Model configuration model_version: Mapped[str] = mapped_column(String(50), default="v1.0") confidence: Mapped[float] = mapped_column(Float) # probability value between 0.0 and 1.0 predicted_action: Mapped[str] = mapped_column(String(20)) # "BUY", "SELL", "HOLD" # Price boundaries entry_price: Mapped[float] = mapped_column(Float) target_price: Mapped[Optional[float]] = mapped_column(Float, nullable=True) stop_loss: Mapped[Optional[float]] = mapped_column(Float, nullable=True) # Actual outcome values (populated later by celery worker) actual_price_1h: Mapped[Optional[float]] = mapped_column(Float, nullable=True) actual_price_24h: Mapped[Optional[float]] = mapped_column(Float, nullable=True) status: Mapped[str] = mapped_column(String(20), default="pending", index=True) # "pending", "completed" outcome: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) # "success", "failed", "neutral" pnl: Mapped[Optional[float]] = mapped_column(Float, nullable=True) # percent return asset_class: Mapped[Optional[str]] = mapped_column(String(50), nullable=True) # "crypto", "index", "tech" # Relationship user: Mapped["User"] = relationship(back_populates="prediction_logs") class StrategyLog(Base): __tablename__ = "strategy_logs" id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4) user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) ticker: Mapped[str] = mapped_column(String(50), index=True) timestamp: Mapped[datetime.datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True) model_version: Mapped[str] = mapped_column(String(50), default="v1.0") bullish_probability: Mapped[int] = mapped_column(Integer) # AI Recommended Levels ai_entry: Mapped[float] = mapped_column(Float) ai_target: Mapped[float] = mapped_column(Float) ai_stop_loss: Mapped[float] = mapped_column(Float) # User Customized Levels user_entry: Mapped[float] = mapped_column(Float) user_target: Mapped[float] = mapped_column(Float) user_stop_loss: Mapped[float] = mapped_column(Float) # Outcomes (populated by background worker) status: Mapped[str] = mapped_column(String(20), default="pending", index=True) # "pending", "completed" ai_outcome: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) # "success", "failed" user_outcome: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) # "success", "failed" actual_exit_price: Mapped[Optional[float]] = mapped_column(Float, nullable=True) asset_class: Mapped[Optional[str]] = mapped_column(String(50), nullable=True) # "crypto", "index", "tech" # Relationship user: Mapped["User"] = relationship(back_populates="strategy_logs")