"""ORM models for the approval-flow registry (审批流程台账).

See ``server/alembic/versions/553_approval_flow_registry.py`` for the
canonical schema and ``constants.py`` for the value enumerations.
"""
from __future__ import annotations

import uuid
from datetime import datetime
from typing import Any

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

from app.core.database import Base
from app.core.sql_types import GUID as Uuid
from app.domains.approval_registry import constants as const


class ApprovalFlow(Base):
    """One approval definition that exists in Feishu, and who answers
    for it.

    ``owner_person_id`` is the point of the table, and it is nullable
    for the same reason the system registry's is: a register that
    invents an owner to look complete is worse than one that admits a
    hole. ``owner_source`` says how the name got there, because
    "inferred from who signs it" and "the owner said yes" are different
    facts that a single non-null column would flatten into one.

    Two group columns, not one. ``source_group`` is what the Feishu
    instance API reports; ``category`` is what Helix publishes. They
    are separate because they genuinely disagree in this tenant — the
    approval admin console files 域名DNS解析申请 under
    ``IT Services & Security`` while the API returns ``IT Equipment``
    for the same definition. Publishing the API value alone would show
    everyone a taxonomy no administrator recognises, and overwriting it
    would destroy the provenance of what upstream actually said.
    """

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

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)

    #: Feishu's approval definition code — the natural key. Upper-cased
    #: on write: Feishu emits upper-case UUIDs and a lower-case copy
    #: from a hand-typed import would create a duplicate row for the
    #: same flow.
    approval_code: Mapped[str] = mapped_column(
        String(const.MAX_CODE_CHARS), nullable=False,
    )
    name: Mapped[str] = mapped_column(String(const.MAX_NAME_CHARS), nullable=False)

    #: Group name exactly as the Feishu instance API reported it.
    #: Read-only provenance — never edited here.
    source_group: Mapped[str | None] = mapped_column(String(const.MAX_CATEGORY_CHARS))
    #: The classification Helix publishes. Seeded from ``source_group``
    #: and editable, because the console's taxonomy is the one people
    #: recognise.
    category: Mapped[str | None] = mapped_column(String(const.MAX_CATEGORY_CHARS))

    status: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=const.STATUS_ACTIVE, server_default=const.STATUS_ACTIVE,
    )
    #: Raw upstream status string, kept so a future Feishu value we do
    #: not map yet is visible instead of silently collapsing to active.
    feishu_status: Mapped[str | None] = mapped_column(String(32))

    #: What this flow is for, in Helix's words. Written by IT; shown on
    #: the all-staff page so "which flow do I use" has an answer.
    description: Mapped[str | None] = mapped_column(Text)

    # ── Accountability ──
    owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    owner_source: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=const.OWNER_SOURCE_UNASSIGNED,
        server_default=const.OWNER_SOURCE_UNASSIGNED,
    )
    #: Snapshot of the owning org at the last ownership write, so a
    #: later transfer cannot retroactively re-attribute history.
    owner_department: Mapped[str | None] = mapped_column(String(255))
    owner_assigned_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Last time a human confirmed they own this. Drives the half-year
    #: campaign's due set and the 逾期 read.
    owner_confirmed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Evidence behind an inferred owner: the approver, their share of
    #: decisions, the sample size and the window. Kept so a human
    #: judging the suggestion can see what it was based on rather than
    #: being asked to trust a name.
    inference_meta: Mapped[dict[str, Any] | None] = mapped_column(JSON)

    #: The definition's 流程管理员 as Feishu reports them, resolved to
    #: people: ``[{user_id, name, email, person_id, active}]``.
    #:
    #: A list, not a foreign key, because the upstream fact is a list —
    #: 7 of this tenant's flows name three or more admins. Collapsing
    #: it to one column would force the register to pick, and picking
    #: is exactly how a guess becomes folklore. ``owner_person_id``
    #: still holds the single accountable person when there is one; this
    #: is the published answer to 「找谁」 when there is not.
    #:
    #: Denormalised on purpose. ``person_id`` may be NULL — an admin
    #: with no ``identity_persons`` row is a real state (contractor,
    #: deleted account) and the name is still the useful answer.
    feishu_admins: Mapped[list[dict[str, Any]] | None] = mapped_column(JSON)
    #: When the admin list was last read from Feishu. Distinct from
    #: ``last_synced_at``: an older sweep read the definition without
    #: asking for admins at all, so "synced" does not imply "admins
    #: known", and a NULL here means never-asked rather than none-exist.
    feishu_admins_synced_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    #: Last time IT was told this row's owner and its Feishu 流程管理员
    #: disagree. Bookkeeping about the PUSH, not about the world: drift
    #: itself is derived from ``feishu_admins`` on every read
    #: (:func:`serializers.feishu_admin_drift`) so that fixing the
    #: console clears it with no write here. Stored only so a standing
    #: backlog is re-raised on a cadence instead of daily or never;
    #: nulled again the moment the row stops drifting, which is what
    #: makes a re-broken flow a new finding rather than a silent one.
    feishu_admin_drift_notified_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )

    #: Whether this row appears on the all-staff page. Default true —
    #: the register exists to be published; the flag is for the rare
    #: flow IT wants tracked but not advertised.
    published: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=True, server_default="1",
    )

    # ── Provenance / freshness ──
    source: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=const.SOURCE_DISCOVERED, server_default=const.SOURCE_DISCOVERED,
    )
    #: Instances seen by the most recent discovery sweep. An activity
    #: signal for triage ("nobody has used this in a year"), never a
    #: business statistic — the sweep samples per person and does not
    #: claim to be a count of everything ever submitted.
    observed_instances: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, server_default="0",
    )
    last_instance_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    first_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_discovered_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Last successful definition read (name + status) from Feishu.
    last_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Why the last definition read failed, if it did. A flow whose
    #: definition 404s upstream is a signal (deleted in Feishu), not an
    #: error to swallow.
    last_sync_error: Mapped[str | None] = mapped_column(String(512))

    created_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    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(),
    )
    deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    owner = relationship(
        "IdentityPerson", foreign_keys=[owner_person_id], lazy="raise",
    )

    __table_args__ = (
        UniqueConstraint("approval_code", name="uq_registry_approval_flows_code"),
        Index("ix_registry_approval_flows_owner", "owner_person_id", "deleted_at"),
        Index("ix_registry_approval_flows_status", "status", "deleted_at"),
        Index("ix_registry_approval_flows_owner_source", "owner_source", "status"),
        Index("ix_registry_approval_flows_published", "published", "status"),
    )

    def __repr__(self) -> str:
        return (
            f"<ApprovalFlow {self.approval_code} "
            f"owner={self.owner_person_id} src={self.owner_source}>"
        )


class ApprovalFlowEvent(Base):
    """Append-only ownership + lifecycle trail for one approval flow.

    Separate from the audit log for the same reason the system
    registry's is: the audit log answers "who did what across the
    platform", this answers "who has been responsible for THIS flow,
    and since when" — the question asked when a flow approves something
    it should not have.
    """

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

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    flow_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("registry_approval_flows.id", ondelete="CASCADE"),
        nullable=False,
    )
    event_type: Mapped[str] = mapped_column(String(32), nullable=False)
    actor_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    #: Snapshot so the trail still reads correctly after the actor
    #: leaves. ``None`` means the platform itself acted (a sweep).
    actor_name: Mapped[str | None] = mapped_column(String(255))
    body: Mapped[str | None] = mapped_column(Text)
    meta: Mapped[dict[str, Any] | None] = mapped_column(JSON)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    __table_args__ = (
        Index("ix_registry_approval_flow_events_flow", "flow_id", "created_at"),
    )

    def __repr__(self) -> str:
        return f"<ApprovalFlowEvent {self.event_type} {self.flow_id}>"


class FlowConfirmationRound(Base):
    """One half-yearly ownership confirmation campaign.

    A round is a real object rather than a derived query because the
    governance question is 「2026-H2 那轮做完了吗、完成率多少、谁没回」,
    and that cannot be answered from ``owner_confirmed_at`` alone: a
    flow registered after the round opened was never asked, and a flow
    whose owner changed mid-round was asked of somebody else.
    """

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

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    #: ``2026-H2`` — the natural key, so a second open in the same window
    #: is a no-op rather than a duplicate campaign.
    label: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=const.ROUND_OPEN, server_default=const.ROUND_OPEN,
    )
    opened_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Who opened it — NULL when the scheduled boundary job did.
    opened_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    #: Frozen counts written at close. The live numbers are computed from
    #: the items; this is what the round looked like when it ended, so a
    #: later transfer cannot rewrite last season's completion rate.
    final_stats: Mapped[dict[str, Any] | None] = mapped_column(JSON)
    notified_owners: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, 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(),
    )

    __table_args__ = (
        UniqueConstraint("label", name="uq_registry_flow_rounds_label"),
        Index("ix_registry_flow_rounds_status", "status", "opened_at"),
    )

    def __repr__(self) -> str:
        return f"<FlowConfirmationRound {self.label} {self.status}>"


class FlowConfirmationItem(Base):
    """One (round, flow) ask, and what the owner did about it.

    ``owner_person_id`` is a **snapshot of who was asked**, not a live
    pointer at the flow's current owner. If the answer was "hand it to
    somebody else", the item still has to say who was asked — otherwise
    the round reads as though the new owner had been asked and answered.
    """

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

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    round_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("registry_flow_confirmation_rounds.id", ondelete="CASCADE"),
        nullable=False,
    )
    flow_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("registry_approval_flows.id", ondelete="CASCADE"),
        nullable=False,
    )
    owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    state: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=const.ITEM_PENDING, server_default=const.ITEM_PENDING,
    )
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Where the answer came from: ``web`` / ``mobile`` / ``token`` (the
    #: card button, no login). Kept because "did the card actually work"
    #: is a question the next round will ask.
    decided_via: Mapped[str | None] = mapped_column(String(16))
    transferred_to_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    note: Mapped[str | None] = mapped_column(Text)
    notified_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    escalated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    __table_args__ = (
        UniqueConstraint(
            "round_id", "flow_id", name="uq_registry_flow_items_round_flow",
        ),
        Index("ix_registry_flow_items_round_state", "round_id", "state"),
        Index("ix_registry_flow_items_owner", "owner_person_id", "state"),
    )

    def __repr__(self) -> str:
        return f"<FlowConfirmationItem {self.flow_id} {self.state}>"


class ApprovalFlowRequest(Base):
    """An employee asking for an approval flow that does not exist yet.

    The register publishes what exists; this is how something gets to
    exist. It is deliberately a *request for IT to build a flow*, not a
    flow the platform creates: Feishu's API can create an approval
    definition, but a definition created that way **can never be
    deactivated or deleted** — Feishu documents this outright, and this
    tenant already carries two undeletable probe definitions as proof.
    Building the flow in the console keeps the owner's ability to change
    or stop it later, which is half of what owning it means.

    ``registered_flow_id`` is what closes the loop: until a real
    ``registry_approval_flows`` row exists, an approved request is an
    employee still waiting.
    """

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

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    requester_person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="CASCADE"), nullable=False,
    )
    #: What the employee wants the flow to be called. A suggestion — IT
    #: names it in the console, and the register takes the real name
    #: from Feishu on the next definition sync.
    proposed_name: Mapped[str] = mapped_column(
        String(const.MAX_NAME_CHARS), nullable=False,
    )
    #: Why it is needed. The single most useful field on the whole row:
    #: IT decides from it, and it becomes the flow's published
    #: description so the next employee knows when to use it.
    purpose: Mapped[str] = mapped_column(Text, nullable=False)
    proposed_category: Mapped[str | None] = mapped_column(
        String(const.MAX_CATEGORY_CHARS),
    )
    #: Who should answer for it. Defaults to the requester — the person
    #: who needed it is usually the person who knows it.
    proposed_owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )

    status: Mapped[str] = mapped_column(
        String(16), nullable=False,
        default=const.FLOW_REQ_PENDING, server_default=const.FLOW_REQ_PENDING,
    )
    decided_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: IT's words back to the requester — required on a rejection, so
    #: 「不批」 never arrives without a reason.
    decision_note: Mapped[str | None] = mapped_column(Text)

    #: Set when IT has built the flow and filled its code in.
    registered_flow_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("registry_approval_flows.id", ondelete="SET NULL"),
    )
    registered_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    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(),
    )

    requester = relationship(
        "IdentityPerson", foreign_keys=[requester_person_id], lazy="raise",
    )
    proposed_owner = relationship(
        "IdentityPerson", foreign_keys=[proposed_owner_person_id], lazy="raise",
    )

    __table_args__ = (
        Index(
            "ix_registry_flow_requests_requester",
            "requester_person_id", "status",
        ),
        Index("ix_registry_flow_requests_status", "status", "created_at"),
    )

    def __repr__(self) -> str:
        return f"<ApprovalFlowRequest {self.proposed_name} {self.status}>"