"""ORM rows owned by the finance domain — the unified spend ledger.

Four tables, all standalone (no foreign keys). Person / integration /
software references are stored as plain ``String(36)`` id columns, NOT
``ForeignKey`` — these are rebuildable projection / config / log tables
and a hard FK to ``identity_persons.id`` (CHAR(36), utf8mb4_unicode_ci)
trips TiDB's CHAR/VARCHAR + collation FK strictness on prod even when
local MySQL accepts it (see the AI-spending period rollup, which made
the same call for the same reason, and memory
``table_kw_for_sibling_vs_person_fk_3780``).

* ``finance_fx_monthly`` — month-frozen FX rate (USD per unit of a
  currency) used to normalise native-currency spend to USD. One row per
  ``(period, currency)``. Cloud cost is already USD-at-ingest so it
  never consults this; software / procurement native amounts do.
* ``finance_spend_facts`` — the materialised unified ledger. One row per
  monthly aggregation unit ``(period, cost_type, basis, department,
  cost_center, owner, vendor, sub_category)``. Rebuilt per period by the
  rollup cron / coordinator; ``fact_key`` makes the rebuild an
  idempotent upsert-by-replace.
* ``finance_period_close`` — per-period close state. A closed period is
  frozen: the dashboard serves its numbers as final, and any later
  change to the underlying facts is captured as a restatement instead of
  silently moving the number.
* ``finance_restatements`` — audit log of post-close total movements,
  so finance can answer "why did last month's number change since I
  last looked".
"""
from __future__ import annotations

from datetime import datetime
from decimal import Decimal

from sqlalchemy import (
    JSON,
    BigInteger,
    Boolean,
    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

# ── Canonical enum-ish string values (stored as VARCHAR, not ENUM, so
#    adding a value later is a no-migration change) ──

# cost_type — the spend stream a fact belongs to.
COST_TYPE_CLOUD = "cloud"
COST_TYPE_SOFTWARE = "software"
COST_TYPE_AI = "ai"
COST_TYPE_HARDWARE = "hardware"
COST_TYPE_PROCUREMENT = "procurement"
COST_TYPES = (
    COST_TYPE_CLOUD,
    COST_TYPE_SOFTWARE,
    COST_TYPE_AI,
    COST_TYPE_HARDWARE,
    COST_TYPE_PROCUREMENT,
)

# basis — the accounting lens.
#   ``consumption`` = recurring/accrual cost we burn each month (cloud /
#     software / ai seats / **hardware depreciation**). These partition
#     the consumption universe with no overlap and sum to "本月消耗了多少成本".
#   ``procurement`` = cash / commitment outflow via purchase orders.
#   ``cash``        = hardware purchase cash outflow in the month it was
#     paid (the asset's purchase_cost). The CASH lens on hardware,
#     distinct from its accrual (depreciation) lens above.
# The bases are DIFFERENT views of the same money (a hardware asset's
# purchase cash and its monthly depreciation describe one spend from two
# angles; a software PO and its seat bills likewise), so the summary
# NEVER sums across bases into one grand total — it reports a consumption
# total and the cash/commitment totals side by side. This is the single
# guard that keeps "总共花了多少" honest instead of double-counting.
BASIS_CONSUMPTION = "consumption"
BASIS_PROCUREMENT = "procurement"
BASIS_CASH = "cash"

# allocation_source — how department / cost_center / owner was decided.
ALLOC_RULE = "rule"            # matched an explicit cloud allocation rule
ALLOC_SCOPE_OWNER = "scope_owner"  # cloud scope-owner fallback
ALLOC_FREETEXT = "freetext"    # procurement free-text department field
ALLOC_DERIVED = "derived"      # derived from owner person's org / cost_center
ALLOC_NONE = "none"            # unattributed → "未分摊" bucket

# period close status
CLOSE_OPEN = "open"
CLOSE_CLOSED = "closed"


class FinanceFxMonthly(Base):
    """Month-frozen FX rate: USD per one unit of ``currency``.

    For CNY this is ≈0.14 (1 CNY ≈ 0.14 USD). The rollup coordinator
    multiplies a native amount by this rate to get the USD amount, and
    **freezes** the rate it used onto each spend fact (``fx_rate_used``)
    so a later FX refresh can never retroactively move a closed period's
    USD total.

    ``source``:
      * ``frankfurter`` — pulled from frankfurter.dev (ECB-published)
        historical endpoint for that month's last business day.
      * ``manual``      — an admin pinned the rate for that month.
      * ``live_fallback`` — the open/current month has no frozen rate
        yet, so the coordinator used the live platform rate and recorded
        it provisionally; replaced by ``frankfurter`` when the month
        closes.

    USD itself is seeded at rate 1.0 so the conversion path is uniform
    (USD spend × 1.0 = USD).
    """

    __tablename__ = "finance_fx_monthly"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "period", "currency", name="uq_finance_fx_monthly_period_ccy",
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    # "YYYY-MM"
    period: Mapped[str] = mapped_column(String(7), nullable=False)
    currency: Mapped[str] = mapped_column(String(8), nullable=False)
    # USD per one unit of ``currency``. Numeric(18, 8) — FX needs more
    # fractional precision than money columns (0.14012345).
    usd_per_unit: Mapped[Decimal] = mapped_column(Numeric(18, 8), nullable=False)
    source: Mapped[str] = mapped_column(
        String(16), nullable=False, default="frankfurter",
        server_default="frankfurter",
    )
    # True once the month is closed and the rate is final (won't be
    # re-pulled). live_fallback rows are frozen=False.
    frozen: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    note: Mapped[str | None] = mapped_column(String(255))
    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 FinanceSpendFact(Base):
    """One materialised monthly spend aggregation unit.

    Grain: ``(period, cost_type, basis, department, cost_center,
    owner_person_id, vendor, sub_category)``. The rollup coordinator
    produces this set per period; a refresh DELETEs the period's rows
    and bulk-INSERTs the freshly computed set in one transaction, so
    ``fact_key`` (a deterministic hash of the grain) makes the rebuild
    idempotent and lets a partial re-run converge.

    ``amount_usd`` is the normalised figure every aggregate sums.
    ``amount_native`` + ``currency_native`` keep the original-currency
    value for display and external reconciliation. ``fx_rate_used`` +
    ``fx_basis`` record exactly how the USD figure was derived so a
    number is always traceable:
      * ``native_usd``   — source was already USD (cloud / AI), rate 1.0
      * ``month_frozen`` — converted at the month-frozen FX rate
      * ``live``         — converted at the live rate (open month only)
    """

    __tablename__ = "finance_spend_facts"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("fact_key", name="uq_finance_spend_facts_fact_key"),
        # Hot read paths: every dashboard query filters by period and
        # then groups by one of these dimensions.
        Index("ix_fin_facts_period_type", "period", "cost_type"),
        Index("ix_fin_facts_period_dept", "period", "department"),
        Index("ix_fin_facts_period_cc", "period", "cost_center"),
        Index("ix_fin_facts_period_vendor", "period", "vendor"),
        Index("ix_fin_facts_period_owner", "period", "owner_person_id"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    # Deterministic hash of the grain — see coordinator._fact_key.
    fact_key: Mapped[str] = mapped_column(String(64), nullable=False)
    # "YYYY-MM"
    period: Mapped[str] = mapped_column(String(7), nullable=False)
    cost_type: Mapped[str] = mapped_column(String(16), nullable=False)
    basis: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=BASIS_CONSUMPTION, server_default=BASIS_CONSUMPTION,
    )
    # Human label of the spend's secondary axis: cloud service / software
    # name / AI model or bucket / procurement category.
    sub_category: Mapped[str | None] = mapped_column(String(255))
    vendor: Mapped[str | None] = mapped_column(String(255))

    # ── Attribution (nullable → "未分摊" bucket) ──
    department: Mapped[str | None] = mapped_column(String(255))
    cost_center: Mapped[str | None] = mapped_column(String(64))
    owner_person_id: Mapped[str | None] = mapped_column(String(36))
    allocation_source: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=ALLOC_NONE, server_default=ALLOC_NONE,
    )

    # ── Money ──
    amount_native: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    currency_native: Mapped[str] = mapped_column(String(8), nullable=False)
    amount_usd: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    fx_rate_used: Mapped[Decimal | None] = mapped_column(Numeric(18, 8))
    fx_basis: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default="native_usd", server_default="native_usd",
    )

    # ── Provenance / freshness ──
    # An open month's facts are provisional (sources still landing); a
    # closed month's are final.
    is_provisional: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    source_domain: Mapped[str] = mapped_column(String(32), nullable=False)
    # Drill handle back to the owning domain (integration_id /
    # software_id / order_number / 'ai:<bucket>'), so the detail drawer
    # can re-query the source rows.
    source_ref: Mapped[str | None] = mapped_column(String(255))
    # Pins the inputs that change what a live recompute would return
    # (whitelist + semantics version). A read trusts only rows whose
    # fingerprint matches the current one.
    rollup_fingerprint: Mapped[str] = mapped_column(String(64), nullable=False)
    computed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False,
    )


class FinancePeriodClose(Base):
    """Per-period close state for the unified ledger.

    One row per period. ``status='closed'`` freezes the period: the
    rollup cron stops re-materialising it on the normal cadence (a
    reopen is required to recompute), the FX rates for the period are
    frozen, and any later detected change in the recomputed total lands
    in ``finance_restatements`` rather than silently moving the figure.
    """

    __tablename__ = "finance_period_close"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("period", name="uq_finance_period_close_period"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    period: Mapped[str] = mapped_column(String(7), nullable=False)
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default=CLOSE_OPEN, server_default=CLOSE_OPEN,
    )
    # USD grand total (consumption basis) snapshotted at close time — the
    # baseline a restatement diffs against.
    closed_total_usd: Mapped[Decimal | None] = mapped_column(Numeric(18, 6))
    closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    closed_by_id: Mapped[str | None] = mapped_column(String(36))
    reopened_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    reopened_by_id: Mapped[str | None] = mapped_column(String(36))
    note: Mapped[str | None] = mapped_column(Text)
    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 FinanceRestatement(Base):
    """Audit log of a closed period's total moving after the fact.

    Written when a rollup recompute of a *closed* period produces a
    USD total that differs from ``finance_period_close.closed_total_usd``
    beyond a cent. Lets finance see "April moved by +$1,240 on 2026-06-10
    because a late AWS bill landed" rather than discovering a silently
    different number.
    """

    __tablename__ = "finance_restatements"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index("ix_fin_restate_period", "period", "detected_at"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    period: Mapped[str] = mapped_column(String(7), nullable=False)
    cost_type: Mapped[str | None] = mapped_column(String(16))
    prev_usd: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    new_usd: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    delta_usd: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    reason: Mapped[str | None] = mapped_column(Text)
    detected_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False,
    )


class FinanceSavedView(Base):
    """A saved filter/pivot configuration for the spend explorer.

    Lets a finance user persist "研发部 云+AI 季度" once and re-open it.
    ``config`` is the opaque explorer state (group_by + filters + range);
    ``is_shared`` exposes it to all finance viewers (else owner-only).
    No FK on ``owner_person_id`` (plain String(36)) — same TiDB-FK
    avoidance as the ledger tables.
    """

    __tablename__ = "finance_saved_views"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index("ix_fin_saved_views_owner", "owner_person_id"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    owner_person_id: Mapped[str] = mapped_column(String(36), nullable=False)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    config: Mapped[dict] = mapped_column(JSON, nullable=False)
    is_shared: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    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,
    )


# ── External-actuals layer (Phase 2 — 汇联易 / external finance systems) ──

# mapped_cost_type sentinel for a 汇联易 line that is a NEW spend category
# (travel / meals / opex) Helix has no other source for — these are
# promoted into the unified ledger. IT-overlap lines (a SaaS / cloud
# invoice paid through 汇联易) keep their cost_type (cloud/software/ai/
# procurement) and are reconciliation-only (NOT promoted), so a yuan is
# never counted twice.
EXPENSE_COST_TYPE = "expense"


class FinanceExternalStatement(Base):
    """One apportionment line of an external actual-spend document.

    The external-actuals ledger that complements the internal
    ``finance_spend_facts``. Today the only source is 汇联易 (expense
    reports / reimbursements / invoices / payments), pulled read-only via
    :mod:`app.core.coordinators.finance_huilianyi_coordinator`. One row
    per ``(source, external_doc_no, line_no)`` — 汇联易 apportions each
    expense across cost-center / department / project lines, and we keep
    that grain so reconciliation and cost-centre attribution are exact.

    Two uses, governed by ``promote_to_ledger`` (the no-double-count
    switch):
      * **Reconciliation** — every row is compared against the internal
        ledger per (period, cost_center, vendor) to surface variance.
      * **Complete-the-picture** — rows whose ``expense_category`` is on
        the non-IT promotion whitelist (``promote_to_ledger=True``) are
        ALSO surfaced in the unified spend view as ``cost_type='expense'``
        (travel / meals / opex Helix has no other source for). IT-overlap
        rows stay ``promote_to_ledger=False`` (already counted internally).

    Standalone (no FK), same TiDB-FK avoidance as the rest of the domain.
    """

    __tablename__ = "finance_external_statements"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("statement_key", name="uq_finance_ext_stmt_key"),
        Index("ix_fin_ext_period_cc", "period", "cost_center"),
        Index("ix_fin_ext_period_dept", "period", "department"),
        Index("ix_fin_ext_period_vendor", "period", "vendor"),
        Index("ix_fin_ext_source_doc", "source", "external_doc_no"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    # Deterministic hash of (source, external_doc_no, line_no) — idempotent upsert.
    statement_key: Mapped[str] = mapped_column(String(64), nullable=False)
    source: Mapped[str] = mapped_column(String(32), nullable=False)
    external_doc_no: Mapped[str] = mapped_column(String(128), nullable=False)
    line_no: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    # Recognition month (YYYY-MM) — from the document's accounting/submit date.
    period: Mapped[str] = mapped_column(String(7), nullable=False)

    expense_category: Mapped[str | None] = mapped_column(String(128))
    # Which Helix cost_type this line overlaps with, or EXPENSE_COST_TYPE
    # for a new (non-IT) category. NULL = unclassified.
    mapped_cost_type: Mapped[str | None] = mapped_column(String(16))
    # True → this line is promoted into finance_spend_facts (PR-B). Only
    # non-IT-overlap categories; the guard against double-counting.
    promote_to_ledger: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )

    cost_center: Mapped[str | None] = mapped_column(String(64))
    department: Mapped[str | None] = mapped_column(String(255))
    project: Mapped[str | None] = mapped_column(String(128))
    vendor: Mapped[str | None] = mapped_column(String(255))
    owner_person_id: Mapped[str | None] = mapped_column(String(36))

    amount_native: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    currency: Mapped[str] = mapped_column(String(8), nullable=False)
    amount_usd: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
    fx_rate_used: Mapped[Decimal | None] = mapped_column(Numeric(18, 8))
    fx_basis: Mapped[str] = mapped_column(
        String(16), nullable=False, default="month_frozen",
        server_default="month_frozen",
    )

    # Full procure-to-pay chain status carried from 汇联易.
    invoice_no: Mapped[str | None] = mapped_column(String(128))
    invoice_verified: Mapped[bool | None] = mapped_column(Boolean)
    doc_status: Mapped[str | None] = mapped_column(String(32))
    payment_status: Mapped[str | None] = mapped_column(String(32))
    paid_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    raw: Mapped[dict | None] = mapped_column(JSON)
    pulled_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 FinanceCostCenter(Base):
    """Authoritative cost-centre master mirrored from the external system.

    汇联易 is the company's system of record for cost centres (every
    expense apportions to one). Mirroring the master here lets the finance
    domain validate / enrich the weakly-derived ``cost_center`` axis
    (today inferred from ``identity_persons.cost_center``) against the
    authoritative source, and surface coverage gaps. Standalone, no FK.
    """

    __tablename__ = "finance_cost_centers"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("source", "code", name="uq_finance_cost_center_src_code"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    source: Mapped[str] = mapped_column(String(32), nullable=False)
    code: Mapped[str] = mapped_column(String(64), nullable=False)
    name: Mapped[str | None] = mapped_column(String(255))
    parent_code: Mapped[str | None] = mapped_column(String(64))
    active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=True, server_default="1",
    )
    # Active-employee headcount currently mapped to this cost centre.
    # Populated for ``source='hris'`` rows by the HRIS reconciler so the
    # coverage UI can show "63 cost centres / 497 people" without a join;
    # NULL for externally-mirrored rows we have no headcount for.
    headcount: Mapped[int | None] = mapped_column(Integer)
    raw: Mapped[dict | None] = mapped_column(JSON)
    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(),
        onupdate=func.now(), nullable=False,
    )


class FinanceBudget(Base):
    """Annual spend budget, in USD, scoped by an optional dimension tuple.

    The finance domain's OWN budget — distinct from ``procurement_budgets``
    (which locks forward PO commitments to gate approval, cash basis). This
    one is the plan the *materialised actuals* (``finance_spend_facts``) are
    measured against: one ceiling per ``(fiscal_year, cost_type, department,
    cost_center)``, where any dimension may be NULL = "all". A NULL on a
    dimension means the budget spans every value of it; the read service
    matches actuals by exact-equality on whichever dimensions are set.

    Budgets are NON-OVERLAPPING (like procurement budgets): NULL is a
    wildcard, so every dimension must be disjoint from another budget in the
    same fiscal year. Production writes serialize the wildcard-overlap check
    with a per-FY MySQL/TiDB advisory lock through commit. The legacy UNIQUE
    remains a defence for fully concrete exact tuples but cannot enforce NULL
    on MySQL/TiDB. Reads diagnose any pre-fix overlap, retain both rows for
    Finance remediation, and fail closed by excluding conflicted pools from
    aggregate totals and alerts. Amounts are USD to align with the unified
    ledger's ``amount_usd`` (no per-budget FX). Standalone, no FK — same
    rebuildable-projection rationale as the sibling tables, and avoids
    TiDB's CHAR/VARCHAR FK strictness.
    """

    __tablename__ = "finance_budgets"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "fiscal_year", "cost_type", "department", "cost_center",
            name="uq_finance_budget_scope",
        ),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    fiscal_year: Mapped[int] = mapped_column(Integer, nullable=False)
    # NULL on any dimension = "all values of this dimension".
    cost_type: Mapped[str | None] = mapped_column(String(32))
    department: Mapped[str | None] = mapped_column(String(128))
    cost_center: Mapped[str | None] = mapped_column(String(64))
    amount_usd: Mapped[Decimal] = mapped_column(Numeric(16, 2), nullable=False)
    # Person accountable for the pool — the ONLY recipient of burn-rate
    # alerts (a cost-centre owner / manager, never a mass employee push).
    owner_person_id: Mapped[str | None] = mapped_column(String(36))
    note: Mapped[str | None] = mapped_column(Text)
    # Monotonic alert dedup (0 / 80 / 100) — mirrors
    # ``procurement_budgets.last_alerted_threshold`` so a crossing fires
    # once and re-arms only if the budget is raised (burn drops back).
    last_alerted_threshold: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, server_default="0",
    )
    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 FinanceFindingAck(Base):
    """Triage state for a cost-governance finding — the 'action' on a finding.

    Cost-governance surfaces (wasted/idle seats, contract renewals, abnormal
    movers, orphan cost centres, expense-hygiene flags) are recomputed every
    read, so without a place to record a decision they re-nag forever. This
    table is that place: one row per ``(kind, ref)`` carrying a triage status
    (``acknowledged`` / ``snoozed`` / ``resolved``), an optional snooze date,
    and who decided + why. The governance reads left-join it to annotate each
    finding, and a snoozed finding whose ``snooze_until`` is still in the
    future is filtered out of the active list — turning the findings wall
    into a managed work queue. Generic by design (kind + ref) so every
    finding family can adopt it. Standalone, no FK (rebuildable log table,
    dodges TiDB CHAR/VARCHAR FK strictness).
    """

    __tablename__ = "finance_finding_acks"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("kind", "ref", name="uq_finance_finding_ack_kind_ref"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    # Finding family, e.g. 'savings_seat' / 'contract_renewal' / 'mover' /
    # 'orphan_cost_center' / 'expense_dup'.
    kind: Mapped[str] = mapped_column(String(32), nullable=False)
    # Stable per-finding key within the kind (seat external_ref, contract
    # number, cost-centre code, invoice no, …).
    ref: Mapped[str] = mapped_column(String(255), nullable=False)
    status: Mapped[str] = mapped_column(String(16), nullable=False)  # acknowledged|snoozed|resolved
    snooze_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    note: Mapped[str | None] = mapped_column(Text)
    actor_person_id: Mapped[str | None] = mapped_column(String(36))
    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,
    )


# ── NetSuite inbound: the book of record as a reconcilable sub-ledger ──
#
# NetSuite is the company's official general ledger. These four tables
# land the SUMMARIZED GL movement plus the supporting financial master
# data, pulled read-only via SuiteQL by
# :mod:`app.core.coordinators.finance_netsuite_coordinator`. They are the
# "actual" leg of the finance spine (budget → commitment → consumption →
# **actual (NS GL)** → variance), reconciled against the bottom-up
# ``finance_spend_facts``. Standalone, no FK — same rebuildable-projection
# + TiDB CHAR/VARCHAR FK-avoidance rationale as the rest of the domain.


class FinanceGLActual(Base):
    """One summarized GL movement from NetSuite.

    Grain = ``(period, subsidiary, account, department)`` — NetSuite
    aggregates server-side via SuiteQL ``GROUP BY``; Helix NEVER pulls raw
    transaction lines. ``amount_native`` is the SIGNED GL movement
    (debit positive) in the subsidiary's base currency; ``amount_usd`` is
    converted at the period's frozen FX. ``is_closed`` mirrors whether the
    NetSuite accounting period is closed (a closed period is pulled once
    then frozen — never re-pulled). Idempotent replace per (source,
    period) on re-sync; ``gl_key`` is the deterministic grain hash.
    """

    __tablename__ = "finance_gl_actuals"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("gl_key", name="uq_finance_gl_key"),
        Index("ix_fin_gl_period_acct", "period", "account_number"),
        Index("ix_fin_gl_period_sub", "period", "subsidiary_id"),
        Index("ix_fin_gl_period_dept", "period", "department_id"),
        Index("ix_fin_gl_period_type", "period", "account_type"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    gl_key: Mapped[str] = mapped_column(String(64), nullable=False)
    source: Mapped[str] = mapped_column(
        String(32), nullable=False, default="netsuite", server_default="netsuite",
    )
    period: Mapped[str] = mapped_column(String(7), nullable=False)  # YYYY-MM

    subsidiary_id: Mapped[str | None] = mapped_column(String(64))
    account_id: Mapped[str] = mapped_column(String(64), nullable=False)
    account_number: Mapped[str | None] = mapped_column(String(128))
    account_name: Mapped[str | None] = mapped_column(String(255))
    # NetSuite account type (Expense / Income / Bank / COGS / …) — the
    # reconciliation sign + cost_type mapping (PR2) keys off this.
    account_type: Mapped[str | None] = mapped_column(String(64))
    department_id: Mapped[str | None] = mapped_column(String(64))
    department_name: Mapped[str | None] = mapped_column(String(255))

    amount_native: Mapped[Decimal] = mapped_column(Numeric(20, 2), nullable=False)
    currency: Mapped[str] = mapped_column(String(8), nullable=False)
    amount_usd: Mapped[Decimal] = mapped_column(Numeric(20, 2), nullable=False)
    fx_rate_used: Mapped[Decimal | None] = mapped_column(Numeric(20, 8))
    fx_basis: Mapped[str] = mapped_column(
        String(16), nullable=False, default="month_frozen",
        server_default="month_frozen",
    )
    # True when the NetSuite accounting period is closed (book frozen).
    is_closed: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    raw: Mapped[dict | None] = mapped_column(JSON)
    pulled_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 FinanceNetSuiteSubsidiary(Base):
    """NetSuite subsidiary (legal entity) master, mirrored read-only.

    Carries the human-set ``legal_entity_id`` mapping to Helix's
    ``asset_legal_entities`` so consolidated GL can be sliced by Helix's
    legal-entity dimension. The mapping is preserved across re-syncs (the
    vendor refresh never clobbers it). Standalone, no FK.
    """

    __tablename__ = "finance_ns_subsidiaries"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("subsidiary_id", name="uq_finance_ns_subsidiary_id"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    subsidiary_id: Mapped[str] = mapped_column(String(64), nullable=False)
    name: Mapped[str | None] = mapped_column(String(255))
    full_name: Mapped[str | None] = mapped_column(String(512))
    currency: Mapped[str | None] = mapped_column(String(16))
    parent_id: Mapped[str | None] = mapped_column(String(64))
    is_elimination: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    is_inactive: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    # Human-set mapping to asset_legal_entities.id (Helix legal-entity
    # dimension). NULL until an admin maps it; never overwritten by sync.
    legal_entity_id: Mapped[str | None] = mapped_column(String(36))
    raw: Mapped[dict | None] = mapped_column(JSON)
    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(),
        onupdate=func.now(), nullable=False,
    )


class FinanceNetSuiteAccount(Base):
    """NetSuite chart-of-accounts master, mirrored read-only.

    The GL account catalog. Drives the account → cost_type / flux line_key
    mapping used by reconciliation (PR2). Standalone, no FK.
    """

    __tablename__ = "finance_ns_accounts"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("account_id", name="uq_finance_ns_account_id"),
        Index("ix_finance_ns_account_number", "account_number"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    account_id: Mapped[str] = mapped_column(String(64), nullable=False)
    account_number: Mapped[str | None] = mapped_column(String(128))
    account_name: Mapped[str | None] = mapped_column(String(255))
    account_type: Mapped[str | None] = mapped_column(String(64))
    parent_id: Mapped[str | None] = mapped_column(String(64))
    is_inactive: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    # Helix cost_type this account's spend reconciles against (cloud /
    # software / ai / hardware / procurement / expense), or NULL =
    # unclassified (surfaced as a reconciliation exception). Seeded by a
    # name/type heuristic on master sync (only when NULL — never overrides a
    # human classification) + admin-editable. `__none__` is a sentinel an
    # admin can set to deliberately exclude an account (e.g. revenue,
    # intercompany) from cost reconciliation.
    mapped_cost_type: Mapped[str | None] = mapped_column(String(16))
    raw: Mapped[dict | None] = mapped_column(JSON)
    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(),
        onupdate=func.now(), nullable=False,
    )


class FinanceNetSuiteAccountingPeriod(Base):
    """NetSuite accounting-period master, mirrored read-only.

    Maps a NetSuite posting period to a Helix calendar ``period`` (YYYY-MM,
    from the period start date) and records whether it is ``closed`` so GL
    pull knows which periods are frozen. Quarter / year roll-up periods are
    flagged so the GL pull excludes them (they double-count their months).
    Standalone, no FK.
    """

    __tablename__ = "finance_ns_accounting_periods"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("period_id", name="uq_finance_ns_period_id"),
        Index("ix_finance_ns_period_calendar", "period"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    period_id: Mapped[str] = mapped_column(String(64), nullable=False)
    period_name: Mapped[str | None] = mapped_column(String(128))
    start_date: Mapped[str | None] = mapped_column(String(32))
    end_date: Mapped[str | None] = mapped_column(String(32))
    # Helix calendar month (YYYY-MM) derived from start_date; NULL if the
    # period is a quarter/year roll-up with no single month.
    period: Mapped[str | None] = mapped_column(String(7))
    closed: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    is_adjustment: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    is_quarter: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    is_year: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    raw: Mapped[dict | None] = mapped_column(JSON)
    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(),
        onupdate=func.now(), nullable=False,
    )