"""ORM models owned by the insights/dashboard aggregation surface.

Two cached-LLM-narrative tables today, both mirroring the proven
``ai_spending_summaries`` shape (bilingual markdown + structured JSON
list + aggregates snapshot + token counts) — see
:class:`app.domains.ai_spending.models.AiSpendingSummary`:

* :class:`InsightsImpactSummary` — the Platform Impact (平台成果) page's
  all-time executive summary (one newest row wins).
* :class:`InsightsHrAiSummary` — the HR 洞察 page's per-(section, range)
  diagnosis for the 人员洞察 / 入离职洞察 tabs.
"""
from __future__ import annotations

import uuid
from datetime import datetime

from sqlalchemy import (
    JSON,
    DateTime,
    ForeignKey,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.core.database import Base
from app.core.sql_types import GUID as Uuid


class InsightsImpactSummary(Base):
    """Cached bilingual LLM narrative for the Platform Impact page.

    One row per ``(period, language)`` where ``period`` is the as-of
    DATE (``YYYY-MM-DD``) the aggregates were read — the impact page is
    all-time cumulative, so the natural cadence is "a fresh narrative
    every so often", not calendar months. The weekly cron writes one
    row per run day; a manual regenerate on the same day replaces that
    day's row in place. Readers take the newest row regardless of date.
    """

    __tablename__ = "insights_impact_summaries"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "period", "language",
            name="uq_insights_impact_summaries_period_lang",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid, primary_key=True, default=uuid.uuid4,
    )
    period: Mapped[str] = mapped_column(String(10), nullable=False)
    # 'bilingual' — single row carries both languages (same convention
    # and the same String(16) sizing lesson as ai_spending_summaries:
    # String(8) silently truncates the 9-char value on MySQL strict).
    language: Mapped[str] = mapped_column(String(16), nullable=False)
    summary_md_en: Mapped[str | None] = mapped_column(Text)
    summary_md_zh: Mapped[str | None] = mapped_column(Text)
    highlights: Mapped[list | None] = mapped_column(JSON)
    aggregates: Mapped[dict | None] = mapped_column(JSON)
    model_used: Mapped[str | None] = mapped_column(String(128))
    input_token_count: Mapped[int | None] = mapped_column(Integer)
    output_token_count: Mapped[int | None] = mapped_column(Integer)
    # ``Uuid`` (= GUID = CHAR(36)) to match the CHAR(36)-stored
    # ``identity_persons.id`` PK — same reasoning as AiSpendingSummary:
    # ``String(36)`` would diverge between the test ``create_all``
    # schema (VARCHAR) and the Alembic-created prod schema (CHAR).
    generated_by_user_id: Mapped[str | None] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    generated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(), nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(), nullable=False,
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(), onupdate=func.now(), nullable=False,
    )


class InsightsHrAiSummary(Base):
    """Cached bilingual LLM diagnosis for the HR 洞察 tabs.

    One row per ``(section, range_key)`` — ``section`` is ``people`` or
    ``lifecycle`` (matching the two rebuilt insights tabs) and
    ``range_key`` is the time-window selector (``7d`` / ``30d`` / ``90d``
    / ``12m`` / ``24m``). Unlike the all-time impact page, the HR tabs
    are range-parameterised, so the narrative is cached per window and a
    manual regenerate replaces that ``(section, range)`` row in place.

    Generation is on-demand (a global_admin / hr clicks 生成 on the AI
    panel); there is no cron warming these, so a missing row renders as
    an inviting empty state rather than an error. ``metrics`` snapshots
    the compact digest fed to the model so the row is self-describing
    for debugging.
    """

    __tablename__ = "insights_hr_ai_summaries"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "section", "range_key",
            name="uq_insights_hr_ai_summaries_section_range",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid, primary_key=True, default=uuid.uuid4,
    )
    section: Mapped[str] = mapped_column(String(32), nullable=False)
    range_key: Mapped[str] = mapped_column(String(8), nullable=False)
    summary_md_en: Mapped[str | None] = mapped_column(Text)
    summary_md_zh: Mapped[str | None] = mapped_column(Text)
    highlights: Mapped[list | None] = mapped_column(JSON)
    metrics: Mapped[dict | None] = mapped_column(JSON)
    model_used: Mapped[str | None] = mapped_column(String(128))
    input_token_count: Mapped[int | None] = mapped_column(Integer)
    output_token_count: Mapped[int | None] = mapped_column(Integer)
    generated_by_user_id: Mapped[str | None] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    generated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(), nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(), nullable=False,
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(), onupdate=func.now(), nullable=False,
    )