models.py

# =========================
# models.py (scrapper)
# =========================
# The scrapper only needs the tables required to (a) know what to fetch and
# (b) hand raw HTML off to the LLM service. All parsing / sentiment / sector /
# stock-metric tables live in the LLM service.

from sqlalchemy import (
    Column,
    String,
    Text,
    Integer,
    text as text_func,
)

import time
import uuid
from database import Base


# raw_html.type is stored as one of these plain lowercase strings.
NEWS = "news"
STOCK = "stock"

# Macro/market series types. Each MacroSource row carries one of these as its
# raw_html.type, so the LLM side can route it to the right processor.
FII_DII = "fii_dii"
VIX = "vix"
COMMODITY = "commodity"
INDEX = "index"
FX = "fx"
YIELD = "yield"
MACRO_TYPES = (FII_DII, VIX, COMMODITY, INDEX, FX, YIELD)


class RawHTML(Base):
    """A single fetched page. Stored verbatim (true raw HTML) and served to the
    LLM service via GET_RAW_HTML, then hard-deleted via DELETE_RAW_HTML once the
    consumer has stored its own copy.

    `type` is a plain lowercase string ("news" / "stock") to keep value handling
    unambiguous across the HTTP boundary.
    """

    __tablename__ = "raw_html"

    id = Column(String, primary_key=True, index=True, default=lambda: str(uuid.uuid4()))

    source = Column(String, nullable=False)

    html = Column(Text, nullable=False)

    created_at = Column(Integer, default=lambda: int(time.time() * 1000))

    type = Column(String, nullable=False)

    # Market country for stock pages (e.g. "INDIA", "UNITED STATES"), derived
    # from the StockSources row at fetch time. NULL for news pages.
    country = Column(String, nullable=True)


# =========================
# NEWS SOURCES TABLE
# =========================


class NewsSource(Base):

    __tablename__ = "news_sources"

    id = Column(String, primary_key=True, index=True, default=lambda: str(uuid.uuid4()))

    name = Column(String, nullable=False, unique=True)

    # `website` is the URL the scraper fetches for this news source.
    website = Column(String, nullable=False)

    created_at = Column(Integer, default=lambda: int(time.time() * 1000))

    last_fetched_at = Column(Integer, nullable=True)

    last_attempted_at = Column(Integer, nullable=True)

    failure_count = Column(
        Integer, nullable=False, default=0, server_default=text_func("0")
    )

    failure_reason = Column(Text, nullable=True)

    type = Column(String, nullable=False)


# =========================
# STOCK SOURCES TABLE
# =========================


class StockSources(Base):

    __tablename__ = "stock_sources"

    id = Column(String, primary_key=True, index=True, default=lambda: str(uuid.uuid4()))
    created_at = Column(Integer, default=lambda: int(time.time() * 1000))
    updated_at = Column(Integer, nullable=True)
    company_name = Column(String, nullable=False)
    ticker = Column(String, nullable=False)
    country = Column(String, nullable=False)
    exchange = Column(String, nullable=False)
    sector = Column(String, nullable=False)

    # Pre-built Google Finance URL, computed once when the CSV is loaded so the
    # stock_enhancer worker just fetches it instead of constructing it per tick.
    url = Column(String, nullable=True)

    last_fetched_at = Column(Integer, nullable=True)
    last_attempted_at = Column(Integer, nullable=True)
    failure_count = Column(
        Integer, nullable=False, default=0, server_default=text_func("0")
    )
    failure_reason = Column(Text, nullable=True)


# =========================
# MACRO SOURCES TABLE
# =========================
# Config-driven market/macro series (FII/DII, VIX, commodities, indices, FX,
# bond yields). Mirrors StockSources' fetch/failure shape but carries a `type`
# (one of MACRO_TYPES) and a `market` (key into config.MARKET_CONFIG) instead of
# ticker/sector semantics. The same generic fetch-after-close worker drives it.


class MacroSource(Base):

    __tablename__ = "macro_sources"

    id = Column(String, primary_key=True, index=True, default=lambda: str(uuid.uuid4()))
    created_at = Column(Integer, default=lambda: int(time.time() * 1000))
    updated_at = Column(Integer, nullable=True)

    name = Column(String, nullable=False)
    type = Column(String, nullable=False)  # one of MACRO_TYPES
    country = Column(String, nullable=True)
    market = Column(String, nullable=False)  # key into MARKET_CONFIG
    exchange = Column(String, nullable=True)

    # Pre-built fetch URL (Google Finance quote URL or a raw page URL), computed
    # once when the CSV is loaded.
    url = Column(String, nullable=True)

    last_fetched_at = Column(Integer, nullable=True)
    last_attempted_at = Column(Integer, nullable=True)
    failure_count = Column(
        Integer, nullable=False, default=0, server_default=text_func("0")
    )
    failure_reason = Column(Text, nullable=True)