"""User-facing tables: screeners, watchlists, alerts, portfolios."""
from __future__ import annotations

from datetime import date, datetime
from typing import Optional

from sqlalchemy import (
    JSON, Boolean, Date, DateTime, ForeignKey, Index, String, UniqueConstraint, func,
)
from sqlalchemy.orm import Mapped, mapped_column

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


class Screener(Base, TimestampMixin):
    """A saved filter set. owner_user_id = NULL means a system/built-in strategy (spec §14)."""

    __tablename__ = "screeners"

    id: Mapped[str] = uuid_pk()
    owner_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    key: Mapped[Optional[str]] = mapped_column(String(48), nullable=True, unique=True)  # e.g. "QUALITY_AT_REASONABLE_PRICE" for system strategies
    name: Mapped[str] = mapped_column(String(128))
    filter_json: Mapped[dict] = mapped_column(JSON)  # SCREENING.md §1 shape
    weights: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)  # overrides DEFAULT_WEIGHTS
    is_system: Mapped[bool] = mapped_column(Boolean, default=False)


class ScreenResult(Base, TimestampMixin):
    """Cached execution snapshot of a screener run (Redis holds the hot cache; this is the
    durable record used by the Daily Global Scan diffing, spec §40)."""

    __tablename__ = "screen_results"

    id: Mapped[str] = uuid_pk()
    screener_id: Mapped[str] = mapped_column(ForeignKey("screeners.id"), index=True)
    run_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)
    security_ids: Mapped[list] = mapped_column(JSON)  # ordered list of security_id, ranked


class Watchlist(Base, TimestampMixin):
    __tablename__ = "watchlists"

    id: Mapped[str] = uuid_pk()
    owner_user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), index=True)
    name: Mapped[str] = mapped_column(String(128), default="My Watchlist")


class WatchlistItem(Base, TimestampMixin):
    __tablename__ = "watchlist_items"
    __table_args__ = (UniqueConstraint("watchlist_id", "security_id", name="uq_watchlist_item"),)

    id: Mapped[str] = uuid_pk()
    watchlist_id: Mapped[str] = mapped_column(ForeignKey("watchlists.id"), index=True)
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    note: Mapped[Optional[str]] = mapped_column(nullable=True)


class Alert(Base, TimestampMixin):
    __tablename__ = "alerts"

    id: Mapped[str] = uuid_pk()
    owner_user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), index=True)
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    alert_type: Mapped[str] = mapped_column(String(32))
    # PRICE_ENTERS_STRONG_BUY | PRICE_ENTERS_BUY | PRICE_REACHES_FAIR_VALUE | MATERIAL_OVERVALUATION
    # | ROIC_CHANGE | FCF_CHANGE | DEBT_CHANGE | NEW_EARNINGS | GUIDANCE_CHANGE | ESTIMATE_REVISION
    # | RECOMMENDATION_CHANGE
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    last_triggered_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    delivery_channel: Mapped[str] = mapped_column(String(16), default="IN_APP")  # IN_APP only implemented — see SPEC_COVERAGE.md


class Portfolio(Base, TimestampMixin):
    __tablename__ = "portfolios"

    id: Mapped[str] = uuid_pk()
    owner_user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), index=True)
    name: Mapped[str] = mapped_column(String(128), default="My Portfolio")
    base_currency: Mapped[str] = mapped_column(String(3), default="USD")


class PortfolioPosition(Base, TimestampMixin):
    __tablename__ = "portfolio_positions"
    __table_args__ = (UniqueConstraint("portfolio_id", "security_id", name="uq_portfolio_position"),)

    id: Mapped[str] = uuid_pk()
    portfolio_id: Mapped[str] = mapped_column(ForeignKey("portfolios.id"), index=True)
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    shares: Mapped[float] = mapped_column()
    average_cost_basis: Mapped[Optional[float]] = mapped_column(nullable=True)
    target_allocation_pct: Mapped[Optional[float]] = mapped_column(nullable=True)


class AlertEventLog(Base, TimestampMixin):
    """One thing that actually HAPPENED to a security (final master pass, §59).

    Deliberately separate from `Alert` above, which is a user's *subscription* — a standing request
    to be told about a class of event. This table is the event log itself: what changed, from what,
    to what, when, why, and how serious. §59 requires all six, and §104.26 makes a real backend
    event log an acceptance criterion rather than a nice-to-have.

    Rows are produced by `app/engines/alerts.py::detect_alert_events()`, which is pure and
    unit-tested. Delivery (email / web push / Telegram) is **NOT IMPLEMENTED** and deliberately not
    modelled here: §59 requires the core to stay independent of any notification provider, and the
    way to honour that is for detection to stop at a provider-agnostic record. A delivery worker
    reads this table later; nothing about that worker constrains this schema.

    `owner_user_id` is intentionally absent: an event is a fact about a security, not about a
    person. Fan-out to subscribers is a read-time join against `Alert`, so one price move produces
    one row rather than one row per watching user.
    """

    __tablename__ = "alert_events"
    __table_args__ = (
        UniqueConstraint("security_id", "event_type", "occurred_on",
                         name="uq_alert_event_security_type_date"),
        Index("ix_alert_events_occurred_severity", "occurred_on", "severity"),
    )

    id: Mapped[str] = uuid_pk()
    security_id: Mapped[str] = mapped_column(ForeignKey("securities.id"), index=True)
    event_type: Mapped[str] = mapped_column(String(48), index=True)
    #: JSON rather than Float: a recommendation change stores strings, a score change stores
    #: numbers, and a sell trigger stores a trigger name. One column, honestly typed as "whatever
    #: this event's before/after actually is".
    previous_value: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    new_value: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    severity: Mapped[str] = mapped_column(String(16), index=True)  # INFO | WARNING | CRITICAL
    reason: Mapped[str] = mapped_column(String(512))
    occurred_on: Mapped[date] = mapped_column(Date, index=True)
    #: Which model produced it — an event detected under one set of weights is not the same claim
    #: as one detected under another (§39).
    model_version: Mapped[Optional[str]] = mapped_column(String(16), nullable=True)


class EmergingClassification(Base, TimestampMixin):
    """Emerging / Established classification and Emerging Score (final master pass, §20, §52).

    Kept out of `scores` on purpose. §20 requires Emerging Opportunities to be a **separate**
    surface from Top Opportunities, and §38 requires the same of the Multibagger signal — storing
    the emerging score alongside the Overall Score is the first step toward someone blending them.

    `signals` holds the per-signal breakdown from
    `app/engines/discovery/emerging.py::EmergingAssessment.as_dict()`, including the signals that
    could NOT be computed and why — so a low or absent score is always explainable, and a young
    company is never silently penalised for the things nobody could measure yet.
    """

    __tablename__ = "emerging_classifications"
    __table_args__ = (
        UniqueConstraint("security_id", "calculation_date", name="uq_emerging_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)
    #: ESTABLISHED | EMERGING | INSUFFICIENT_HISTORY
    status: Mapped[str] = mapped_column(String(24), index=True)
    years_of_history: Mapped[int] = mapped_column()
    emerging_score: Mapped[Optional[float]] = mapped_column(nullable=True, index=True)
    signals: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    discovery_reasons: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    #: The date this security was FIRST classified EMERGING — §52's "Discovery Date" column.
    first_discovered_on: Mapped[Optional[date]] = mapped_column(Date, nullable=True)
    model_version: Mapped[Optional[str]] = mapped_column(String(16), nullable=True)