"""ORM models for the finance-flux domain (6 tables).

See ``server/alembic/versions/347_finance_flux_schema.py`` for the
canonical schema and ``constants.py`` for value enumerations + the seed
statement layout.

Person references (``*_person_id``) are stored as GUID columns WITHOUT a
real FK to ``identity_persons`` — matching the platform convention that
dodges MySQL/TiDB collation error 3780 on cross-table CHAR(36) FKs. Only
intra-domain references (→ ``finance_flux_periods``) are real FKs.

Layering note: ``finance_flux_lines`` holds machine-pulled figures and is
overwritten on every NetSuite re-sync; ``finance_flux_commentary`` holds
the human edit layer (commentary / recon link / AI narrative) and is
deliberately a separate table so a re-sync never clobbers analyst input.
"""
from __future__ import annotations

import uuid
from datetime import date, datetime
from decimal import Decimal

from sqlalchemy import (
    JSON,
    Boolean,
    Date,
    DateTime,
    Index,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func

from app.core.database import Base
from app.core.sql_types import GUID as Uuid
from app.domains.finance_flux import constants as c


class FinanceFluxConfig(Base):
    """Singleton config row (finance-owned, not admin-owned).

    Holds the variance thresholds, the NetSuite consolidation scope used
    when pulling figures, the enabled comparison modes, and the AI
    commentary settings. Exactly one row is seeded; the service reads the
    earliest row defensively.
    """

    __tablename__ = "finance_flux_config"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    amount_threshold: Mapped[Decimal] = mapped_column(
        Numeric(20, 2), nullable=False, server_default=str(c.DEFAULT_AMOUNT_THRESHOLD),
    )
    pct_threshold: Mapped[Decimal] = mapped_column(
        Numeric(7, 2), nullable=False, server_default=str(c.DEFAULT_PCT_THRESHOLD),
    )
    currency: Mapped[str] = mapped_column(
        String(3), nullable=False, server_default=c.DEFAULT_CURRENCY,
    )
    # NetSuite consolidation scope (finance-selected). Internal ids as text.
    consolidation_subsidiary: Mapped[str | None] = mapped_column(String(64))
    accounting_book: Mapped[str | None] = mapped_column(String(64))
    comparison_modes: Mapped[list[str] | None] = mapped_column(JSON)  # ["mom","yoy"]
    closed_periods_only: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    ai_enabled: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    ai_prompt_template: Mapped[str | None] = mapped_column(Text)
    updated_by_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )


class FinanceFluxLineDef(Base):
    """The statement layout catalog: one row per display line, per statement.

    Finance-editable (label / section / order / owner / per-line threshold
    overrides). ``row_type`` distinguishes pulled lines from computed
    subtotals/totals. ``line_key`` is the stable join key used by
    ``finance_flux_lines`` / ``account_map`` / ``commentary``.
    """

    __tablename__ = "finance_flux_line_defs"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    statement: Mapped[str] = mapped_column(String(2), nullable=False)
    line_key: Mapped[str] = mapped_column(String(64), nullable=False)
    label: Mapped[str] = mapped_column(String(255), nullable=False)
    section: Mapped[str | None] = mapped_column(String(64))
    row_type: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default=c.ROW_LINE,
    )
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    owner_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    owner_label: Mapped[str | None] = mapped_column(String(64))
    # Per-line threshold overrides (NULL → fall back to config defaults).
    threshold_amount: Mapped[Decimal | None] = mapped_column(Numeric(20, 2))
    threshold_pct: Mapped[Decimal | None] = mapped_column(Numeric(7, 2))
    is_active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint("statement", "line_key", name="uq_finance_flux_line_defs_key"),
        Index("ix_finance_flux_line_defs_stmt_sort", "statement", "sort_order"),
    )


class FinanceFluxAccountMap(Base):
    """NetSuite GL account → statement line_key mapping (finance-adjustable).

    Auto-seeded from the NetSuite chart of accounts by the sync engine,
    then refined by finance. ``sign`` flips presentation for contra
    accounts. One account maps to at most one line.
    """

    __tablename__ = "finance_flux_account_map"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    ns_account_id: Mapped[str] = mapped_column(String(64), nullable=False)
    account_number: Mapped[str | None] = mapped_column(String(64))
    account_name: Mapped[str | None] = mapped_column(String(255))
    account_type: Mapped[str | None] = mapped_column(String(32))
    statement: Mapped[str] = mapped_column(String(2), nullable=False)
    line_key: Mapped[str | None] = mapped_column(String(64))
    sign: Mapped[int] = mapped_column(Integer, nullable=False, server_default="1")
    is_active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint("ns_account_id", name="uq_finance_flux_account_map_acct"),
        Index("ix_finance_flux_account_map_line", "line_key"),
    )


class FinanceFluxPeriod(Base):
    """One row per accounting period whose figures we have pulled."""

    __tablename__ = "finance_flux_periods"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_key: Mapped[str] = mapped_column(String(7), nullable=False)  # "2026-05"
    label: Mapped[str] = mapped_column(String(32), nullable=False)      # "May 2026"
    ns_period_id: Mapped[str | None] = mapped_column(String(64))
    start_date: Mapped[date | None] = mapped_column(Date)
    end_date: Mapped[date | None] = mapped_column(Date)
    is_closed: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="0",
    )
    sync_status: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default=c.SYNC_PENDING,
    )
    last_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_sync_error: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint("period_key", name="uq_finance_flux_periods_key"),
    )


class FinanceFluxLine(Base):
    """A pulled figure: (period, statement, line_key) → value in config currency.

    Overwritten wholesale on every NetSuite re-sync. Every parsed report row
    is stored (including ``subtotal``/``total`` rows — the KPI cards source
    directly from those stored total ``line_key``s); ``row_type`` drives the
    display/flag semantics rather than gating what gets persisted.
    """

    __tablename__ = "finance_flux_lines"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    statement: Mapped[str] = mapped_column(String(2), nullable=False)
    line_key: Mapped[str] = mapped_column(String(64), nullable=False)
    value: Mapped[Decimal] = mapped_column(
        Numeric(20, 2), nullable=False, server_default="0",
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    # The FK to finance_flux_periods is declared out-of-line in the
    # migration (table_kw-matched) to stay clear of the 3780 collation
    # trap; the ORM keeps period_id as a plain indexed GUID column.
    __table_args__ = (
        UniqueConstraint(
            "period_id", "statement", "line_key",
            name="uq_finance_flux_lines_period_line",
        ),
        Index("ix_finance_flux_lines_period", "period_id"),
    )


class FinanceFluxLineDetail(Base):
    """Sub-account / GL breakdown beneath a finance line (drill-down).

    The detail rows the report carries under each line (which the line total
    nets), stored so a flux line can expand to show its composition. Overwritten
    wholesale per (period, statement) on re-import alongside the lines.
    """

    __tablename__ = "finance_flux_line_details"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    statement: Mapped[str] = mapped_column(String(2), nullable=False)
    parent_line_key: Mapped[str] = mapped_column(String(64), nullable=False)
    label: Mapped[str] = mapped_column(String(255), nullable=False)
    value: Mapped[Decimal] = mapped_column(
        Numeric(20, 2), nullable=False, server_default="0",
    )
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    __table_args__ = (
        Index(
            "ix_finance_flux_line_details_parent",
            "period_id", "statement", "parent_line_key",
        ),
    )


class FinanceFluxCommentary(Base):
    """Human edit layer per (period, statement, line_key).

    Survives re-syncs. ``ai_narrative`` caches the last百炼-generated text;
    ``commentary`` is the analyst's free-text adopted/edited version.
    """

    __tablename__ = "finance_flux_commentary"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    statement: Mapped[str] = mapped_column(String(2), nullable=False)
    line_key: Mapped[str] = mapped_column(String(64), nullable=False)
    commentary: Mapped[str | None] = mapped_column(Text)
    recon_link: Mapped[str | None] = mapped_column(Text)
    ai_narrative: Mapped[str | None] = mapped_column(Text)
    ai_generated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    edited_by_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint(
            "period_id", "statement", "line_key",
            name="uq_finance_flux_commentary_period_line",
        ),
        Index("ix_finance_flux_commentary_period", "period_id"),
    )


class FinanceFluxKpiDef(Base):
    """Summary-page KPI catalog (finance-editable). ``source_line_key`` is the
    computed line/total that feeds the card; ``higher_is_better`` drives the
    good/bad tone of the KPI's variance.
    """

    __tablename__ = "finance_flux_kpi_defs"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    kpi_key: Mapped[str] = mapped_column(String(64), nullable=False)
    label: Mapped[str] = mapped_column(String(128), nullable=False)
    statement: Mapped[str] = mapped_column(String(2), nullable=False)
    source_line_key: Mapped[str] = mapped_column(String(64), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    higher_is_better: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    is_active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint("kpi_key", name="uq_finance_flux_kpi_defs_key"),
    )


class FinanceFluxKpiOverride(Base):
    """Per-period manual override of an auto-computed KPI value."""

    __tablename__ = "finance_flux_kpi_override"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    kpi_key: Mapped[str] = mapped_column(String(64), nullable=False)
    value: Mapped[Decimal] = mapped_column(Numeric(20, 2), nullable=False)
    note: Mapped[str | None] = mapped_column(Text)
    edited_by_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint(
            "period_id", "kpi_key", name="uq_finance_flux_kpi_override_period_kpi",
        ),
        Index("ix_finance_flux_kpi_override_period", "period_id"),
    )


class FinanceFluxInsightCard(Base):
    """Editorial card per period: Summary "Key Highlights" (card_type=highlight)
    or Insights-page narrative cards (card_type=insight). Free-text + emoji +
    colour, finance-authored.
    """

    __tablename__ = "finance_flux_insight_cards"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    card_type: Mapped[str] = mapped_column(String(16), nullable=False)
    emoji: Mapped[str | None] = mapped_column(String(16))
    color: Mapped[str | None] = mapped_column(String(16))
    title: Mapped[str | None] = mapped_column(String(255))
    body: Mapped[str | None] = mapped_column(Text)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    created_by_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        Index("ix_finance_flux_insight_cards_period", "period_id", "card_type"),
    )


class FinanceFluxFollowup(Base):
    """Recommended follow-up row per period (Item / Owner / Action / Priority)."""

    __tablename__ = "finance_flux_followups"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    period_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    item: Mapped[str | None] = mapped_column(Text)
    owner_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    owner_label: Mapped[str | None] = mapped_column(String(64))
    action: Mapped[str | None] = mapped_column(Text)
    priority: Mapped[str] = mapped_column(
        String(8), nullable=False, server_default=c.PRIORITY_MEDIUM,
    )
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    created_by_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        Index("ix_finance_flux_followups_period", "period_id"),
    )