backend/app/database/models.py

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