"""SQLAlchemy models for the Identity Sync Center.

Three tables, all created in migration 121:

* :class:`IdentitySyncTarget` — one row per outbound system instance
  (e.g. "Google dept push", "Navan production", "Okta staging").
* :class:`IdentitySyncState` — per-(target, person) projection state.
  Carries the last payload hash so unchanged people are skipped on the
  next run, the external_id assigned by the downstream system, and the
  last error if a push failed.
* :class:`IdentitySyncRun` — per-target run history. Includes manual,
  cron, and per-person event runs in one place so the admin UI doesn't
  need to look at three tables to reconstruct what happened.

Counters on IdentitySyncRun (created/updated/skipped/...) are
denormalized from the runner result so the runs list view doesn't have
to parse JSON for every row.
"""
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,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

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


class IdentitySyncTarget(Base):
    """One outbound sync target (one downstream system instance)."""

    __tablename__ = "identity_sync_targets"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint("slug", name="uq_identity_sync_targets_slug"),
        Index("ix_identity_sync_targets_enabled", "enabled"),
        Index("ix_identity_sync_targets_next_run_at", "next_run_at"),
        Index("ix_identity_sync_targets_kind", "kind"),
    )

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

    name: Mapped[str] = mapped_column(String(128), nullable=False)
    slug: Mapped[str] = mapped_column(String(64), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)

    # ``scim`` | ``navan`` | ``google_schema``. See ``runners/`` for
    # the runner that consumes each kind. ``navan`` is a SCIM preset.
    kind: Mapped[str] = mapped_column(String(32), nullable=False)

    integration_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("integrations.id"),
    )

    enabled: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="0",
    )

    # ``deactivate`` (default) | ``delete`` | ``keep``. Applied when a
    # person who was previously synced no longer matches the filter
    # (typical case: employment_status flipped to ``offboarded`` and
    # the filter excludes offboarded employees).
    on_offboard: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="deactivate",
    )

    # Operation dimension of the sync-template matrix (migration 371):
    # ``create_update`` (default — upsert everyone in scope) |
    # ``create_only`` (link once, never re-touch an already-linked person) |
    # ``update_only`` (only maintain already-linked people, never create).
    # Gated in ``_sweep`` on ``identity_sync_state.external_id`` link state.
    write_mode: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="create_update",
    )

    mapping_config: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    filter_config: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    schedule_config: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    runner_options: Mapped[dict[str, Any] | None] = mapped_column(JSON)

    # Master on/off switch for this target's failure alerting. When
    # ``False`` the alert notifier (``alert_notifier.py``) sends nothing
    # on a failed / partial run — not even to the recipients below. The
    # admin can mute a noisy target without losing the recipient list.
    alert_enabled: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=True, server_default="1",
    )

    # Per-target failure-alert recipients: list of ``identity_persons.id``
    # (UUID strings). When a run finalises with status ``failed`` /
    # ``partial`` AND ``alert_enabled`` is true, the alert notifier
    # (``alert_notifier.py``) pushes an in-app + Feishu + email card to
    # each of these people. NULL / empty ⇒ nobody is notified (there is
    # deliberately NO IT-admin fallback: a target alerts only the people
    # the admin explicitly picked, never the whole admin org by default —
    # see DECISIONS.md 2026-06-19). The cron dispatcher's separate
    # scheduled-job failure backstop still pages admins on a cron-tick
    # failure; that is independent of this list.
    alert_recipient_ids: Mapped[list[str] | None] = mapped_column(JSON)

    # Run state (mirrored from latest IdentitySyncRun for fast list-view
    # rendering — the runs table is source of truth).
    last_run_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_run_status: Mapped[str | None] = mapped_column(String(16))
    last_run_summary: Mapped[dict[str, Any] | None] = mapped_column(JSON)
    next_run_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    # Consecutive SCHEDULED-run failures since the last ok/partial sweep.
    # Drives same-day failure backoff in ``_finalise_run``: instead of a
    # single transient vendor failure costing 24h of staleness (the cron
    # cursor used to advance to tomorrow's slot on ANY finalise), a failed
    # scheduled run re-arms ``next_run_at`` to a short exponential backoff
    # and retries the SAME day, up to ``_SCHED_FAILURE_MAX_SAMEDAY_RETRIES``
    # attempts, before falling back to the next cron slot. Reset to 0 on
    # any ok/partial scheduled run. A ``cancelled`` run (worker restart /
    # deploy) never touches this — it is an infra event, not a data
    # failure, and re-arms immediately regardless of streak.
    sched_failure_streak: Mapped[int] = mapped_column(
        Integer, nullable=False, server_default="0",
    )

    created_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    updated_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    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))


class IdentitySyncState(Base):
    """Per-(target, person) projection state.

    last_payload_hash is the sha256 of the rendered, sorted-key JSON
    payload sent to the downstream system. The runner skips a person
    whose freshly-rendered payload hash matches the stored hash — that
    is the core "don't blast the API for unchanged people" guarantee.

    last_status:
        ``pending`` (initial; no push yet)
      | ``success`` (last push succeeded, payload current)
      | ``skipped`` (rendered payload unchanged — counts as a synced
        state, not a failure)
      | ``failed`` (last push raised; last_error set; retry_count
        bumped)
      | ``deactivated`` (person fell out of the filter and the
        target's on_offboard policy deactivated them)
      | ``deleted`` (same, but on_offboard=delete)
      | ``excluded`` (person fell out of the filter and on_offboard=keep)
    """

    __tablename__ = "identity_sync_state"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "target_id", "person_id",
            name="uq_identity_sync_state_target_person",
        ),
        Index(
            "ix_identity_sync_state_target_status",
            "target_id", "last_status",
        ),
        Index("ix_identity_sync_state_person", "person_id"),
        Index(
            "ix_identity_sync_state_auto_included",
            "target_id", "auto_included_reason",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    target_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_sync_targets.id", ondelete="CASCADE"),
        nullable=False,
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id", ondelete="CASCADE"),
        nullable=False,
    )

    external_id: Mapped[str | None] = mapped_column(String(255))
    last_payload_hash: Mapped[str | None] = mapped_column(String(64))
    last_status: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="pending",
    )
    last_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # Last time the runner ACTUALLY talked to the vendor for this person
    # (success / skipped / excluded outcome), as opposed to a cheap
    # payload-hash skip that never touched the vendor. Drives the
    # periodic drift re-verification: even when the HRIS payload hash is
    # unchanged, a row whose ``last_verified_at`` is older than
    # ``_DRIFT_REVERIFY_INTERVAL`` is re-pushed so the runner re-asserts
    # the Helix-managed fields against LIVE vendor state — correcting any
    # drift made on the vendor side outside Helix (a manually-edited
    # Feishu email / name / dept). NULL ⇒ never vendor-verified ⇒ treated
    # as stale (re-verify on next sweep). Backfilled to ``last_synced_at``
    # in the migration so existing rows don't all re-push at once.
    last_verified_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    last_error: Mapped[str | None] = mapped_column(Text)
    retry_count: Mapped[int] = mapped_column(
        Integer, nullable=False, server_default="0",
    )
    # Set to a non-NULL reason (e.g. ``"manager_of_foreign"``) when the
    # sweep auto-pulled this person into scope rather than the filter
    # matching them directly. Auto-included rows are treated as
    # permanently in-scope — the out-of-scope deactivate path skips
    # them, even if the reason for inclusion (e.g. having a foreign
    # direct report) is no longer present.
    auto_included_reason: Mapped[str | None] = mapped_column(String(64))
    # Set once when the first-create notification is enqueued for an
    # auto-included row. Never cleared — subsequent sweeps see a
    # non-NULL value and skip re-notification.
    notified_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(),
    )


class IdentitySyncDepartmentState(Base):
    """Per-(target, dept_code) projection state for department-shaped
    sync targets (Feishu Contact ``feishu_contact`` kind, Phase A —
    the dept reconciliation phase that runs internally before the
    user phase; not a separately registered kind).

    Mirrors :class:`IdentitySyncState`'s shape but keyed on
    ``dept_code`` instead of ``person_id``. Created in migration 214.

    Why a parallel table instead of bolting dept rows onto
    ``identity_sync_state``:

    * Different orchestrator path — departments push in topological
      order, soft-delete on disappear, separate mass-action brake.
    * Clean queries — ``identity_sync_state`` has a strict
      (target_id, person_id) unique constraint and a hot per-person
      event hook path; mixing dept rows in would require every query
      to guard against null person_id.
    * Different state vocabulary — ``soft_deleted`` is a dept-only
      status; the user table never uses it.

    ``dept_code`` equals ``identity_org_nodes.org_node_source_id`` AND
    equals the Feishu ``open_department_id`` the runner stamps on the
    downstream record — see ADR for the "no two-way mapping" decision.
    """

    __tablename__ = "identity_sync_department_state"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "target_id", "dept_code",
            name="uq_identity_sync_department_state_target_dept",
        ),
        Index(
            "ix_identity_sync_department_state_target_status",
            "target_id", "last_status",
        ),
        Index("ix_identity_sync_department_state_dept_code", "dept_code"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    target_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_sync_targets.id", ondelete="CASCADE"),
        nullable=False,
    )
    dept_code: Mapped[str] = mapped_column(String(128), nullable=False)

    external_id: Mapped[str | None] = mapped_column(String(255))
    last_payload_hash: Mapped[str | None] = mapped_column(String(64))
    last_status: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="pending",
    )
    last_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # See IdentitySyncState.last_verified_at — same periodic drift
    # re-verification, keyed on dept_code. A dept renamed / re-parented
    # on Feishu outside Helix is re-asserted once its row goes stale.
    last_verified_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    last_error: Mapped[str | None] = mapped_column(Text)
    retry_count: 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(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )


class IdentitySyncRunItem(Base):
    """One line of the per-run ledger — exactly what was sent where.

    Backs the admin's per-run drill-down (and the person-by-person
    cutover testing workflow): for every vendor-touching operation the
    sweep / single-person push performed, one row records the subject,
    the operation outcome, the rendered request payload as sent, and
    the vendor's response detail / error. Created in migration 273.

    Volume control: cron sweeps do NOT record hash-skip rows (at
    steady state nearly everyone is ``skipped`` — recording them would
    be ~targets×population rows/day of noise); manual and event runs
    record skips too, because "I pushed this person and it said
    skipped (unchanged)" is exactly what the admin needs to see while
    testing. Retention: ``identity_sync.run_items_cleanup`` cron
    deletes rows older than 90 days.

    ``request_payload`` is the rendered vendor body (same thing the
    preview endpoint shows) — admin-only surface, no credential
    material (credentials live in headers, never in payloads).
    ``response_detail`` carries the runner's structured outcome detail
    (vendor message / errorCode / http status), pre-redacted by the
    runner layer.
    """

    __tablename__ = "identity_sync_run_items"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index("ix_identity_sync_run_items_run", "run_id"),
        Index(
            "ix_identity_sync_run_items_target_created",
            "target_id", "created_at",
        ),
        Index("ix_identity_sync_run_items_person", "person_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    run_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_sync_runs.id", ondelete="CASCADE"),
        nullable=False,
    )
    target_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_sync_targets.id", ondelete="CASCADE"),
        nullable=False,
    )
    # ``person`` | ``department`` | ``group``
    item_type: Mapped[str] = mapped_column(String(16), nullable=False)
    person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    # Email / dept name / group name — survives person hard-delete.
    subject_display: Mapped[str] = mapped_column(String(255), nullable=False)
    # Vendor-side key: SCIM id / employeeID / dept_code / group id.
    external_key: Mapped[str | None] = mapped_column(String(255))
    # created | updated | skipped | failed | deactivated | deleted |
    # excluded | soft_deleted
    outcome: Mapped[str] = mapped_column(String(16), nullable=False)
    request_payload: Mapped[dict[str, Any] | None] = mapped_column(JSON)
    response_detail: Mapped[dict[str, Any] | None] = mapped_column(JSON)
    error: Mapped[str | None] = mapped_column(Text)
    duration_ms: Mapped[int | None] = mapped_column(Integer)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )


class IdentitySyncVendorRecord(Base):
    """One record as it CURRENTLY exists on the vendor side (对端数据).

    Populated by the per-target vendor-snapshot refresh (admin button /
    MCP tool) — a read-only pull of the downstream system's live
    users / departments / groups, normalized just enough to filter
    (display_name, email, is_active) with the trimmed raw vendor
    record kept for the detail view. Refresh is wipe-and-replace per
    (target, record_type); ``fetched_at`` stamps the snapshot age.

    ``matched_person_id`` is resolved at refresh time (email and/or
    employee-number match against identity_persons) — NULL means
    "vendor-only": an account Helix doesn't know, the disable blind
    spot to review before cutover. Created in migration 274.
    """

    __tablename__ = "identity_sync_vendor_records"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "target_id", "record_type", "external_key",
            name="uq_identity_sync_vendor_rec_key",
        ),
        Index(
            "ix_identity_sync_vendor_rec_target_type",
            "target_id", "record_type",
        ),
        Index("ix_identity_sync_vendor_rec_person", "matched_person_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    target_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_sync_targets.id", ondelete="CASCADE"),
        nullable=False,
    )
    # ``user`` | ``department`` | ``group``
    record_type: Mapped[str] = mapped_column(String(16), nullable=False)
    # Vendor-unique key: SCIM id / employeeID / od-XXX / custDeptNumber
    # / Google primaryEmail / group id.
    external_key: Mapped[str] = mapped_column(String(255), nullable=False)
    display_name: Mapped[str | None] = mapped_column(String(255))
    email: Mapped[str | None] = mapped_column(String(255))
    # Vendor-native status string (e.g. huilianyi 1001/1003, feishu
    # frozen) — kept verbatim; ``is_active`` is the normalized filter.
    vendor_status: Mapped[str | None] = mapped_column(String(32))
    is_active: Mapped[bool | None] = mapped_column(Boolean)
    matched_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    raw: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    fetched_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )


class IdentitySyncDestructivePlan(Base):
    """Fail-closed snapshot awaiting approval before mass deprovisioning."""

    __tablename__ = "identity_sync_destructive_plans"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index("ix_idsync_destructive_target_status", "target_id", "status", "created_at"),
        Index("ix_idsync_destructive_target_scope_fp", "target_id", "scope", "fingerprint"),
        Index("ix_idsync_destructive_token_hash", "confirmation_token_hash"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    target_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_sync_targets.id", ondelete="CASCADE"), nullable=False,
    )
    scope: Mapped[str] = mapped_column(String(32), nullable=False)
    action: Mapped[str] = mapped_column(String(24), nullable=False)
    fingerprint: Mapped[str] = mapped_column(String(64), nullable=False)
    target_snapshot_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    item_count: Mapped[int] = mapped_column(Integer, nullable=False)
    item_snapshot: Mapped[list[dict[str, Any]]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(16), nullable=False, server_default="pending")
    plan_expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    confirmation_token_hash: Mapped[str | None] = mapped_column(String(64))
    confirmed_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    confirmed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    confirmation_expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_run_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_sync_runs.id", ondelete="SET NULL"),
    )
    consumed_run_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_sync_runs.id", ondelete="SET NULL"),
    )
    consumed_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    consumed_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(),
    )


class IdentitySyncRun(Base):
    """One execution of one target — manual, cron, or per-person event."""

    __tablename__ = "identity_sync_runs"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index(
            "ix_identity_sync_runs_target_started",
            "target_id", "started_at",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    target_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_sync_targets.id", ondelete="CASCADE"),
        nullable=False,
    )
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    duration_ms: Mapped[int | None] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String(16), nullable=False)
    trigger_source: Mapped[str] = mapped_column(String(96), nullable=False)

    persons_in_scope: Mapped[int | None] = mapped_column(Integer)
    created_count: Mapped[int | None] = mapped_column(Integer)
    updated_count: Mapped[int | None] = mapped_column(Integer)
    skipped_count: Mapped[int | None] = mapped_column(Integer)
    deactivated_count: Mapped[int | None] = mapped_column(Integer)
    failed_count: Mapped[int | None] = mapped_column(Integer)
    # Persons the vendor rejected for a non-transient, data-dependent
    # reason (missing required field / unmapped code / resigned account).
    # First-class like ``failed_count`` so the 监控看板 can chart it
    # separately: a blocked backlog is a data/config task, not a sync
    # failure, and must not dilute the success rate or page anyone.
    blocked_count: Mapped[int | None] = mapped_column(Integer)
    error_message: Mapped[str | None] = mapped_column(Text)
    result_summary: Mapped[dict[str, Any] | None] = mapped_column(JSON)