app/database/models.py

from datetime import datetime
from sqlalchemy import (
    Column, Integer, String, Float, DateTime, Text, Boolean,
    ForeignKey, Index, UniqueConstraint
)
from sqlalchemy.orm import relationship
from .db import Base


class HistoricalPrice(Base):
    __tablename__ = "historical_prices"

    id = Column(Integer, primary_key=True, index=True)
    symbol = Column(String(10), nullable=False)
    timestamp = Column(DateTime, nullable=False)
    open = Column(Float, nullable=False)
    high = Column(Float, nullable=False)
    low = Column(Float, nullable=False)
    close = Column(Float, nullable=False)
    adjusted_close = Column(Float)
    volume = Column(Integer, default=0)
    dividend_amount = Column(Float, default=0.0)
    split_coefficient = Column(Float, default=1.0)
    ma20 = Column(Float)
    ma50 = Column(Float)
    rsi = Column(Float)
    created_at = Column(DateTime, default=datetime.utcnow)

    __table_args__ = (
        UniqueConstraint("symbol", "timestamp", name="uq_hist_symbol_ts"),
        Index("ix_hist_symbol", "symbol"),
        Index("ix_hist_timestamp", "timestamp"),
        Index("ix_hist_symbol_ts", "symbol", "timestamp"),
    )


class LiveMarketData(Base):
    __tablename__ = "live_market_data"

    id = Column(Integer, primary_key=True, index=True)
    symbol = Column(String(10), nullable=False)
    timestamp = Column(DateTime, nullable=False)
    open = Column(Float, nullable=False)
    high = Column(Float, nullable=False)
    low = Column(Float, nullable=False)
    close = Column(Float, nullable=False)
    volume = Column(Integer, default=0)
    vwap = Column(Float)
    created_at = Column(DateTime, default=datetime.utcnow)

    __table_args__ = (
        UniqueConstraint("symbol", "timestamp", name="uq_live_symbol_ts"),
        Index("ix_live_symbol", "symbol"),
        Index("ix_live_timestamp", "timestamp"),
        Index("ix_live_symbol_ts", "symbol", "timestamp"),
    )


class MarketNews(Base):
    __tablename__ = "market_news"

    id = Column(Integer, primary_key=True, index=True)
    news_id = Column(String(64), unique=True, nullable=False)
    headline = Column(Text, nullable=False)
    summary = Column(Text)
    source = Column(String(128))
    url = Column(Text)
    published_at = Column(DateTime, nullable=False)
    sentiment = Column(String(20))          # bullish | bearish | neutral
    confidence_score = Column(Float)         # 0.0 – 1.0
    overall_sentiment_score = Column(Float)
    importance_score = Column(Float, default=0.5)
    is_breaking = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    topics = relationship("NewsTopic", back_populates="news", cascade="all, delete-orphan")
    ticker_sentiments = relationship("TickerSentiment", back_populates="news", cascade="all, delete-orphan")

    __table_args__ = (
        Index("ix_news_published_at", "published_at"),
        Index("ix_news_sentiment", "sentiment"),
        Index("ix_news_source", "source"),
    )


class NewsTopic(Base):
    __tablename__ = "news_topics"

    id = Column(Integer, primary_key=True, index=True)
    news_id_fk = Column(Integer, ForeignKey("market_news.id", ondelete="CASCADE"), nullable=False)
    topic = Column(String(128), nullable=False)
    relevance_score = Column(Float, default=0.0)

    news = relationship("MarketNews", back_populates="topics")

    __table_args__ = (
        Index("ix_topic_topic", "topic"),
        Index("ix_topic_news_id", "news_id_fk"),
    )


class TickerSentiment(Base):
    __tablename__ = "ticker_sentiment"

    id = Column(Integer, primary_key=True, index=True)
    news_id_fk = Column(Integer, ForeignKey("market_news.id", ondelete="CASCADE"), nullable=False)
    ticker = Column(String(10), nullable=False)
    relevance_score = Column(Float, default=0.0)
    sentiment_score = Column(Float, default=0.0)
    sentiment_label = Column(String(32))

    news = relationship("MarketNews", back_populates="ticker_sentiments")

    __table_args__ = (
        Index("ix_ts_ticker", "ticker"),
        Index("ix_ts_news_id", "news_id_fk"),
    )


class TechnicalIndicator(Base):
    __tablename__ = "technical_indicators"

    id = Column(Integer, primary_key=True, index=True)
    symbol = Column(String(10), nullable=False)
    timestamp = Column(DateTime, nullable=False)
    indicator_type = Column(String(32), nullable=False)   # SMA20, EMA50, RSI14, BB_UPPER, BB_LOWER, VWAP
    value = Column(Float, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    __table_args__ = (
        UniqueConstraint("symbol", "timestamp", "indicator_type", name="uq_ti_symbol_ts_type"),
        Index("ix_ti_symbol", "symbol"),
        Index("ix_ti_timestamp", "timestamp"),
        Index("ix_ti_ticker_ts", "symbol", "timestamp"),
    )