"""Derived/computed tables: latest metrics (fast screener reads), full metric history, valuation
snapshots, and scores (docs/DATA_MODEL.md)."""
from __future__ import annotations

from datetime import date
from typing import Optional

from sqlalchemy import JSON, Date, ForeignKey, Index, String, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, TimestampMixin, uuid_pk


class Metric(Base, TimestampMixin):
    """Latest value per (security, metric) — what the screener/rankings/company-page read from.
    Never recomputed per request; refreshed by the metrics-engine worker on ingestion events."""

    __tablename__ = "metrics"
    __table_args__ = (
        UniqueConstraint("security_id", "metric_key", name="uq_metric_security_key"),
        Index("ix_metrics_industry_key_value", "industry_id", "metric_key", "value"),
    )

    id: Mapped[str] = uuid_pk()
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    industry_id: Mapped[str] = mapped_column(ForeignKey("industries.id"), index=True)
    metric_key: Mapped[str] = mapped_column(String(48), index=True)
    value: Mapped[Optional[float]] = mapped_column(nullable=True, index=True)
    status: Mapped[str] = mapped_column(String(16))
    applicability: Mapped[str] = mapped_column(String(24))
    formula_version: Mapped[str] = mapped_column(String(16))
    as_of: Mapped[date] = mapped_column(Date)
    inputs_used: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    note: Mapped[Optional[str]] = mapped_column(nullable=True)


class MetricHistory(Base, TimestampMixin):
    """Append-only history of every computed metric value — never overwritten (spec §73)."""

    __tablename__ = "metric_history"
    __table_args__ = (
        UniqueConstraint("security_id", "metric_key", "as_of", "formula_version", name="uq_metric_history"),
        Index("ix_metric_history_security_key_asof", "security_id", "metric_key", "as_of"),
    )

    id: Mapped[str] = uuid_pk()
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    metric_key: Mapped[str] = mapped_column(String(48), index=True)
    value: Mapped[Optional[float]] = mapped_column(nullable=True)
    status: Mapped[str] = mapped_column(String(16))
    applicability: Mapped[str] = mapped_column(String(24))
    formula_version: Mapped[str] = mapped_column(String(16))
    as_of: Mapped[date] = mapped_column(Date, index=True)
    calculation_date: Mapped[date] = mapped_column(Date)


class Valuation(Base, TimestampMixin):
    """One WACC/DCF/multiples/blended-Fair-Value snapshot per security per calculation_date."""

    __tablename__ = "valuation"
    __table_args__ = (UniqueConstraint("security_id", "calculation_date", name="uq_valuation_security_date"),)

    id: Mapped[str] = uuid_pk()
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    calculation_date: Mapped[date] = mapped_column(Date, index=True)

    wacc: Mapped[Optional[float]] = mapped_column(nullable=True)
    wacc_inputs: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)

    dcf_bear_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    dcf_base_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    dcf_bull_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    dcf_assumptions: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)

    multiples_fair_values: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)  # {"pe": 100.0, "ev_to_ebitda": 78.0, ...}
    business_profile: Mapped[Optional[str]] = mapped_column(String(24), nullable=True)

    bear_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    base_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    bull_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    weighted_fair_value: Mapped[Optional[float]] = mapped_column(nullable=True)
    fair_value_confidence: Mapped[Optional[float]] = mapped_column(nullable=True)

    price_at_calculation: Mapped[Optional[float]] = mapped_column(nullable=True)
    margin_of_safety: Mapped[Optional[float]] = mapped_column(nullable=True)
    strong_buy_price: Mapped[Optional[float]] = mapped_column(nullable=True)
    buy_price: Mapped[Optional[float]] = mapped_column(nullable=True)
    overvalued_price: Mapped[Optional[float]] = mapped_column(nullable=True)

    expected_return_3y: Mapped[Optional[float]] = mapped_column(nullable=True)
    expected_return_5y: Mapped[Optional[float]] = mapped_column(nullable=True)
    expected_return_10y: Mapped[Optional[float]] = mapped_column(nullable=True)
    expected_return_components: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)

    methodology_version: Mapped[str] = mapped_column(String(16), default="v1")


class Score(Base, TimestampMixin):
    """5 sub-scores + overall + confidence per security per calculation_date."""

    __tablename__ = "scores"
    __table_args__ = (UniqueConstraint("security_id", "calculation_date", name="uq_score_security_date"),)

    id: Mapped[str] = uuid_pk()
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    calculation_date: Mapped[date] = mapped_column(Date, index=True)

    quality_score: Mapped[Optional[float]] = mapped_column(nullable=True, index=True)
    financial_health_score: Mapped[Optional[float]] = mapped_column(nullable=True)
    growth_score: Mapped[Optional[float]] = mapped_column(nullable=True)
    competitive_advantage_score: Mapped[Optional[float]] = mapped_column(nullable=True)
    valuation_score: Mapped[Optional[float]] = mapped_column(nullable=True)
    overall_score: Mapped[Optional[float]] = mapped_column(nullable=True, index=True)
    risk_score: Mapped[Optional[float]] = mapped_column(nullable=True)

    weights_used: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    subscore_detail: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)  # per spec §49 audit trail

    recommendation: Mapped[Optional[str]] = mapped_column(String(16), nullable=True, index=True)
    recommendation_confidence: Mapped[Optional[float]] = mapped_column(nullable=True)
    triggers_fired: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)

    # AUDIT FIX (StockLab overhaul, Part A1 -- docs/AUDIT_GUI.md's "Score/Confidence/Data Quality
    # wiring gap" finding). Deliberately separate from `recommendation_confidence` above, which is
    # a narrower, older field scoped to the Buy/Sell recommendation specifically
    # (app/engines/recommendation/). `confidence_score` is the platform-wide Confidence Score
    # (app/engines/scoring/confidence.py) -- "how much of the scoring framework could actually be
    # computed, from what quality of inputs" -- and `data_quality_score` is the independent Data
    # Quality Score (app/engines/scoring/data_quality.py) -- "how reliable is the underlying data,
    # scoped to the data itself, comparable across companies/runs". Never conflate the three:
    # overall_score, confidence_score, and data_quality_score answer three different questions.
    # `*_components` carries each engine's component breakdown (name/weight/raw_score/contribution)
    # for traceability/audit -- not currently exposed via the API (ScoreOut exposes only the two
    # scalar values), but persisted so a future drill-through view doesn't need a recompute.
    confidence_score: Mapped[Optional[float]] = mapped_column(nullable=True)
    confidence_components: Mapped[Optional[list]] = mapped_column(JSON, nullable=True)
    data_quality_score: Mapped[Optional[float]] = mapped_column(nullable=True)
    data_quality_components: Mapped[Optional[list]] = mapped_column(JSON, nullable=True)

    methodology_version: Mapped[str] = mapped_column(String(16), default="v1")