"""
Attention subsystem models — event-centric attention data for backtest-friendly queries.

Completely separate from the overlay subsystem (which is real-time monitoring).
This subsystem is designed for fithia2 backtester to query attention data
relative to events (earnings, etc.).
"""

from sqlalchemy import Column, String, Float, Boolean, Integer, Text, Date, Index, UniqueConstraint, JSON
from sqlalchemy.dialects.postgresql import UUID, TIMESTAMP
from datetime import datetime, timezone
import uuid

from app.core.database import Base


class CompanyEntityMap(Base):
    """Ticker → canonical company entity mapping with Wikipedia and GDELT resolution."""

    __tablename__ = "company_entity_map"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ticker = Column(String(10), unique=True, nullable=False, index=True)
    canonical_name = Column(String(500), nullable=False)
    wiki_title = Column(String(500), nullable=True)
    gdelt_query = Column(String(1000), nullable=True)
    aliases_json = Column(JSON, default=list)
    resolver_confidence = Column(Float, default=0.0)
    is_manual_override = Column(Boolean, default=False)
    created_at = Column(
        TIMESTAMP(timezone=True), default=lambda: datetime.now(timezone.utc)
    )
    updated_at = Column(
        TIMESTAMP(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
    )

    __table_args__ = (
        Index("idx_company_entity_map_ticker", "ticker"),
    )


class WikiPageviewsDaily(Base):
    """Daily Wikipedia pageview counts per article title."""

    __tablename__ = "wiki_pageviews_daily"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    wiki_title = Column(String(500), nullable=False)
    date = Column(Date, nullable=False)
    views = Column(Integer, nullable=False)
    created_at = Column(
        TIMESTAMP(timezone=True), default=lambda: datetime.now(timezone.utc)
    )

    __table_args__ = (
        UniqueConstraint("wiki_title", "date", name="uq_wiki_pageviews_daily"),
        Index("idx_wiki_pageviews_daily_title_date", "wiki_title", "date"),
    )


class GdeltArticleRaw(Base):
    """Raw GDELT article records matched to a ticker."""

    __tablename__ = "gdelt_article_raw"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    url = Column(String(2000), unique=True, nullable=False)
    title = Column(Text, nullable=True)
    domain = Column(String(255), nullable=True)
    published_at = Column(TIMESTAMP(timezone=True), nullable=True)
    sourcecountry = Column(String(10), nullable=True)
    matched_ticker = Column(String(10), nullable=False, index=True)
    match_method = Column(String(50), nullable=True)
    match_confidence = Column(Float, default=1.0)
    created_at = Column(
        TIMESTAMP(timezone=True), default=lambda: datetime.now(timezone.utc)
    )

    __table_args__ = (
        Index("idx_gdelt_article_raw_ticker", "matched_ticker"),
        Index("idx_gdelt_article_raw_published_at", "published_at"),
    )


class AttentionFeaturesDaily(Base):
    """Derived daily attention features per ticker — materialized from wiki + gdelt raw data."""

    __tablename__ = "attention_features_daily"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ticker = Column(String(10), nullable=False)
    date = Column(Date, nullable=False)

    # Wikipedia features
    wiki_views = Column(Integer, nullable=True)
    wiki_spike_10d = Column(Float, nullable=True)
    wiki_zscore_20d = Column(Float, nullable=True)

    # GDELT / news features
    gdelt_article_count_1d = Column(Integer, default=0)
    gdelt_article_count_3d = Column(Integer, default=0)
    gdelt_unique_domains_3d = Column(Integer, default=0)
    gdelt_us_article_count_3d = Column(Integer, default=0)

    created_at = Column(
        TIMESTAMP(timezone=True), default=lambda: datetime.now(timezone.utc)
    )

    __table_args__ = (
        UniqueConstraint("ticker", "date", name="uq_attention_features_daily"),
        Index("idx_attention_features_daily_ticker_date", "ticker", "date"),
        Index("idx_attention_features_daily_ticker", "ticker"),
    )