"""The decisions ledger and the proposals store (Stream K2; plan W13, W15, W16; the escalation-2
spec §1). One append-only table of decisions, held by a Postgres trigger; a batch is the unit of
approval and undo; a pass is the unit a proposals file loads under; a proposal is a work item in
the ledger's own shape; a redirect is a slug's forwarding address (brands, product lines, places).

Sources of truth: this module, `alembic/versions/d2e3f4a5b6c7_*.py`, `services/decisions/`,
`.logs/planning/decisions-model-spec-2026-09-16.md`. Every JSON column is `JSONVariant`; every uid
is a UUID native on Postgres and CHAR(32) on SQLite, born in Python so the kit and the server
agree. The CHECKs that need a dialect (the field grammar) live in the migration; the writer
validates them (`services/decisions/writer.py`).
"""

from __future__ import annotations

import uuid
from datetime import datetime
from typing import Any

from sqlalchemy import BigInteger, Boolean, CheckConstraint, DateTime, ForeignKey, Index, Integer, Numeric, String, Text, \
    UniqueConstraint, Uuid, func, text
from sqlalchemy.orm import Mapped, mapped_column

from sqlalchemy import JSON
from sqlalchemy.dialects.postgresql import JSONB

from app.models.accounts import JSONVariant
from app.models.base import Base

#: A JSON column where Python None is SQL NULL (a `release` has no value; the CHECK reads `value IS
#: NULL`); a decided EMPTY is written as `JSON.NULL`, the JSON null, which is a value.
NullableJSON = JSON(none_as_null=True).with_variant(JSONB(none_as_null=True), "postgresql")

BigId = BigInteger().with_variant(Integer(), "sqlite")

ENTITY_TYPES = ("brand", "product_line", "product_variant", "listing", "attribute_wording", "suggestion", "place")
BATCH_KINDS = ("sheet", "desk", "route", "cli", "undo", "replay")
MODES = ("individual", "bulk")
PASS_KINDS = ("session", "rule", "arrival")
PROPOSAL_STATUSES = ("open", "approved", "rejected", "withdrawn", "stale", "parked", "deferred")
#: Highest first. "critical" means the answer sets a precedent or makes a new kind of thing;
#: "none" means there is nothing to decide and the row exists so it can be marked seen.
ATTENTION_LEVELS = ("critical", "high", "medium", "low", "none")
RESOLUTIONS = ("resolved", "creatable", "unresolved")
REDIRECT_KINDS = ("brand", "product_line", "place")


def _in(col: str, values: tuple[str, ...]) -> str:
    return f"{col} IN ({', '.join(repr(v) for v in values)})"


class ProposalPass(Base):
    """One AI pass, rule-generator run or ingest's standing arrival pass (spec §1.3): the unit a
    proposals file loads under and a bad pass is withdrawn by. `name` is its identity on every host."""

    __tablename__ = "proposal_passes"
    __table_args__ = (
        UniqueConstraint("name", name="uq_proposal_passes_name"),
        CheckConstraint(_in("kind", PASS_KINDS), name="ck_proposal_passes_kind"),
        Index("ix_proposal_passes_kind_loaded", "kind", "loaded_at"),
        Index("ix_proposal_passes_scope", "scope_brand_slug"),
    )

    id: Mapped[int] = mapped_column(BigId, primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    kind: Mapped[str] = mapped_column(String(12), nullable=False)
    process_version: Mapped[str] = mapped_column(String(20), nullable=False)
    rules_version: Mapped[str] = mapped_column(String(8), nullable=False)
    generator: Mapped[str] = mapped_column(String(80), nullable=False)
    scope_brand_slug: Mapped[str | None] = mapped_column(String(160))
    loaded_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    loaded_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    loaded_by_username: Mapped[str | None] = mapped_column(String(64))
    source_file: Mapped[str | None] = mapped_column(Text)
    file_sha256: Mapped[str | None] = mapped_column(String(64))
    counts: Mapped[dict] = mapped_column(JSONVariant, nullable=False, default=dict, server_default="{}")
    note: Mapped[str | None] = mapped_column(Text)
    withdrawn_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    withdrawn_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    withdrawn_reason: Mapped[str | None] = mapped_column(Text)


class DecisionBatch(Base):
    """The unit of approval and of undo (spec §1.2): inserted at open, updated exactly once at
    close (`closed_at`, `summary`); the Postgres trigger `decision_batches_guard` refuses anything
    else. `uid` crosses hosts: a batch undone on staging is undone on production by the same uid."""

    __tablename__ = "decision_batches"
    __table_args__ = (
        UniqueConstraint("uid", name="uq_decision_batches_uid"),
        CheckConstraint(_in("kind", BATCH_KINDS), name="ck_decision_batches_kind"),
        CheckConstraint(_in("mode", MODES), name="ck_decision_batches_mode"),
        CheckConstraint("(kind = 'undo') = (reverses_batch_id IS NOT NULL)", name="ck_decision_batches_undo"),
        Index("ix_decision_batches_opened", "opened_at"),
        Index("ix_decision_batches_by", "by_account_id", "opened_at"),
    )

    id: Mapped[int] = mapped_column(BigId, primary_key=True)
    uid: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), nullable=False, default=uuid.uuid4)
    opened_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    origin_host: Mapped[str] = mapped_column(String(20), nullable=False)
    kind: Mapped[str] = mapped_column(String(12), nullable=False)
    mode: Mapped[str] = mapped_column(String(10), nullable=False)
    by_account_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    by_username: Mapped[str | None] = mapped_column(String(64))
    acting_as_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    pass_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("proposal_passes.id"))
    scope: Mapped[dict | None] = mapped_column(JSONVariant)
    reverses_batch_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decision_batches.id"))
    replayed_from: Mapped[str | None] = mapped_column(String(60))
    note: Mapped[str | None] = mapped_column(Text)
    summary: Mapped[dict] = mapped_column(JSONVariant, nullable=False, default=dict, server_default="{}")


class Proposal(Base):
    """What the review sheet reads (spec §1.4): one row per proposed ruling over one value, in the
    ledger's own shape, so approval is a copy with provenance. Mutable by design: a work item;
    its permanent record is the decision it became plus `audit_log`."""

    __tablename__ = "proposals"
    __table_args__ = (
        UniqueConstraint("uid", name="uq_proposals_uid"),
        UniqueConstraint("pass_id", "entity_type", "natural_key", "field", name="uq_proposals_pass_target"),
        CheckConstraint(_in("entity_type", ENTITY_TYPES), name="ck_proposals_entity_type"),
        CheckConstraint(_in("resolution", RESOLUTIONS), name="ck_proposals_resolution"),
        CheckConstraint(_in("status", PROPOSAL_STATUSES), name="ck_proposals_status"),
        CheckConstraint("attention IS NULL OR " + _in("attention", ATTENTION_LEVELS).replace("attention IN", "attention IN"),
                        name="ck_proposals_attention"),
        Index("ix_proposals_sheet", "brand_slug", "status", "sheet_line_ref", "position"),
        Index("ix_proposals_pass_status", "pass_id", "status"),
        Index("ix_proposals_target", "entity_type", "natural_key", "field"),
    )

    id: Mapped[int] = mapped_column(BigId, primary_key=True)
    uid: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), nullable=False, default=uuid.uuid4)
    pass_id: Mapped[int] = mapped_column(BigId, ForeignKey("proposal_passes.id"), nullable=False)
    brand_slug: Mapped[str] = mapped_column(String(160), nullable=False)
    sheet_line_ref: Mapped[str | None] = mapped_column(String(500))
    position: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    entity_type: Mapped[str] = mapped_column(String(20), nullable=False)
    natural_key: Mapped[str] = mapped_column(String(500), nullable=False)
    natural_key_detail: Mapped[dict | None] = mapped_column(JSONVariant)
    entity_id: Mapped[int | None] = mapped_column(BigInteger)
    resolution: Mapped[str] = mapped_column(String(12), nullable=False, default="unresolved", server_default="unresolved")
    field: Mapped[str] = mapped_column(String(80), nullable=False)
    value: Mapped[Any] = mapped_column(JSONVariant, nullable=False)
    rule_value: Mapped[Any] = mapped_column(NullableJSON, nullable=True)
    current_value: Mapped[Any] = mapped_column(NullableJSON, nullable=True)
    against_decision_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id", name="fk_proposals_against_decision", use_alter=True))
    generator: Mapped[str] = mapped_column(String(80), nullable=False)
    reason: Mapped[str | None] = mapped_column(Text)
    evidence: Mapped[list] = mapped_column(JSONVariant, nullable=False, default=list, server_default="[]")
    confidence: Mapped[float | None] = mapped_column(Numeric(4, 3))
    spot_check: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default=text("false"))
    #: How much of a person's judgement this row needs, which is NOT how sure the machine is:
    #: a near-certain merge that sets a precedent for every set after it needs more of a person
    #: than a doubtful attribute on one variant nobody else stocks. `confidence` stays as what the
    #: pass measured; this is what the page shows (`ATTENTION_LEVELS`).
    attention: Mapped[str | None] = mapped_column(String(10), nullable=True)
    status: Mapped[str] = mapped_column(String(12), nullable=False, default="open", server_default="open")
    corrected_value: Mapped[Any] = mapped_column(NullableJSON, nullable=True)
    decision_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id", name="fk_proposals_decision", use_alter=True))
    resolution_note: Mapped[str | None] = mapped_column(Text)
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    resolved_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    detail: Mapped[dict] = mapped_column(JSONVariant, nullable=False, default=dict, server_default="{}")
    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 Decision(Base):
    """One act of a person, or of a person approving a proposal (spec §1.1). Never updated or
    deleted: a later row on the same field supersedes an earlier one by being newer
    (`decided_at`, then `id`); undo is a row; a consequence names its cause. `entity_id` is this
    host's read key; `uid` and `natural_key` are the transport across hosts."""

    __tablename__ = "decisions"
    __table_args__ = (
        UniqueConstraint("uid", name="uq_decisions_uid"),
        CheckConstraint(_in("entity_type", ENTITY_TYPES), name="ck_decisions_entity_type"),
        CheckConstraint(_in("effect", ("set", "release")), name="ck_decisions_effect"),
        CheckConstraint(_in("origin", ("person", "proposal")), name="ck_decisions_origin"),
        CheckConstraint(_in("mode", MODES), name="ck_decisions_mode"),
        CheckConstraint("(effect = 'release') = (value IS NULL)", name="ck_decisions_release_value"),
        CheckConstraint("reverses_id IS NULL OR origin = 'person'", name="ck_decisions_undo_is_a_person"),
        CheckConstraint("restores_id IS NULL OR reverses_id IS NOT NULL", name="ck_decisions_restores"),
        CheckConstraint("origin <> 'proposal' OR pass_id IS NOT NULL", name="ck_decisions_proposal_pass"),
        Index("ix_decisions_effective", "entity_type", "entity_id", "field", "decided_at", "id"),
        Index("ix_decisions_natural", "entity_type", "natural_key"),
        Index("ix_decisions_batch", "batch_id", "id"),
        Index("ix_decisions_decided_at", "decided_at"),
        Index("ix_decisions_by", "decided_by", "decided_at"),
    )

    id: Mapped[int] = mapped_column(BigId, primary_key=True)
    uid: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), nullable=False, default=uuid.uuid4)
    decided_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    recorded_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    origin_host: Mapped[str] = mapped_column(String(20), nullable=False)
    entity_type: Mapped[str] = mapped_column(String(20), nullable=False)
    entity_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    natural_key: Mapped[str] = mapped_column(String(500), nullable=False)
    natural_key_detail: Mapped[dict | None] = mapped_column(JSONVariant)
    field: Mapped[str] = mapped_column(String(80), nullable=False)
    effect: Mapped[str] = mapped_column(String(8), nullable=False, default="set", server_default="set")
    value: Mapped[Any] = mapped_column(NullableJSON, nullable=True)
    value_ref_id: Mapped[int | None] = mapped_column(BigInteger)
    rule_value: Mapped[Any] = mapped_column(NullableJSON, nullable=True)
    prior_value: Mapped[Any] = mapped_column(NullableJSON, nullable=True)
    supersedes_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id"))
    reverses_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id"))
    restores_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id"))
    caused_by_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id"))
    rules_version: Mapped[str] = mapped_column(String(8), nullable=False)
    origin: Mapped[str] = mapped_column(String(10), nullable=False)
    proposal_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("proposals.id", name="fk_decisions_proposal", use_alter=True))
    pass_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("proposal_passes.id"))
    batch_id: Mapped[int] = mapped_column(BigId, ForeignKey("decision_batches.id"), nullable=False)
    mode: Mapped[str] = mapped_column(String(10), nullable=False)
    decided_by: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"))
    decided_by_username: Mapped[str | None] = mapped_column(String(64))
    reason: Mapped[str | None] = mapped_column(Text)
    replayed_from: Mapped[str | None] = mapped_column(String(60))
    detail: Mapped[dict | None] = mapped_column(JSONVariant)

    # The shim's names for one release (`services/overrides.py`): what the rules had, who, when.
    @property
    def collected_value(self) -> Any:
        return self.rule_value

    @property
    def set_by(self) -> int | None:
        return self.decided_by

    @property
    def set_at(self) -> datetime:
        return self.decided_at

    @property
    def entity_key(self) -> str:
        return str(self.entity_id)


class Redirect(Base):
    """A slug's forwarding address (brief decision 7; plan W18): brands, product lines and places
    share it; chains are flattened at write so every old slug points at the live row. K6 writes
    the rows through `publish.redirect_write()`; the replay resolver reads it last."""

    __tablename__ = "redirects"
    __table_args__ = (
        CheckConstraint(_in("kind", REDIRECT_KINDS), name="ck_redirects_kind"),
        Index("ix_redirects_kind_to", "kind", "to_slug"),
    )

    from_slug: Mapped[str] = mapped_column(String(240), primary_key=True)
    kind: Mapped[str] = mapped_column(String(16), nullable=False)
    to_slug: Mapped[str] = mapped_column(String(240), nullable=False)
    since: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    decision_id: Mapped[int | None] = mapped_column(BigId, ForeignKey("decisions.id"))


#: The suites' TABLES lists take these together: a kit that builds the ledger builds all of it.
LEDGER_TABLES = [ProposalPass.__table__, DecisionBatch.__table__, Proposal.__table__, Decision.__table__, Redirect.__table__]