"""Create cmdb_cloud_scope_owner_confirmations + add last_owner_confirmed_at.

Revision ID: 168
Revises: 167
Create Date: 2026-05-15

Persists every owner-related governance event on a cloud scope so the
detail-page "Owner 与治理记录" tab and the two new crons (semiannual
owner confirmation, monthly zero-spend alert) have a single source of
truth. One table stores all event kinds:

  * ``semiannual_pending``       — cron pushed a confirm card, awaiting response
  * ``semiannual_confirmed``     — owner clicked "确认是我管理"
  * ``semiannual_transferred``   — owner clicked "转给别人" and picked a new owner
  * ``zero_spend_pending``       — cron pushed a zero-spend card, awaiting response
  * ``zero_spend_keep``          — owner picked "保留" with a written reason
  * ``zero_spend_cleanup``       — owner picked "我来清理"
  * ``zero_spend_handover_it``   — owner picked "联系 IT 处理"
  * ``escalated_to_it``          — 14 days passed with no response, IT admins notified

A ``_pending`` row is the seed; its ``response_at`` / ``response_kind``
get filled when the owner clicks. A 14-day escalation writes a second
``escalated_to_it`` row (NOT a status flip on the pending row — keeps
the timeline shape clean).

Also adds ``cmdb_cloud_scopes.last_owner_confirmed_at`` — the source of
truth for "180 天到期" detection. Updated by:
  * The semiannual confirm endpoint (owner clicked 确认)
  * ``assign_scope_owner`` (transfer implicitly confirms current owner
    — the new owner restarts the 180-day clock)

NULL on existing rows means "never confirmed" — the cron treats NULL
the same as "older than 180 days ago" and queues a confirm.

**No FK constraints declared.** Prod TiDB and local Docker MySQL have
DIFFERENT column types + collations on the FK targets:

  * ``cmdb_cloud_scopes.id`` — prod: ``VARCHAR(36) utf8mb4_bin``;
    local: ``VARCHAR(36) <default collation>``
  * ``identity_persons.id`` — prod: ``CHAR(36) utf8mb4_unicode_ci``;
    local: ``VARCHAR(36) <default collation>``

(Direct INFORMATION_SCHEMA query against prod TiDB confirms the
drift; migrations 001/052 declared both as ``sa.String(36)`` =
VARCHAR(36) with no explicit collation, but prod's tables evolved
through TiDB version changes that normalised some columns to CHAR
and assigned different collations.)

A single migration that hardcodes prod's actual column types breaks
local CI; a migration that hardcodes the original ``sa.String(36)``
breaks prod (``ERROR 3780 (HY000): Referencing column 'X' and
referenced column 'Y' in foreign key constraint are incompatible``).

Resolution: **don't declare FK constraints on these columns**. The
referential integrity guarantees live in the application layer —
``OwnerConfirmationService`` validates scope + person existence
before every insert, scope decommissioning soft-deletes via
``deleted_at`` (never DELETE), and the cron handlers explicitly
skip rows with vanished scopes. Sibling tables in this domain
(``cmdb_cost_facts.cost_owner_person_id`` and others) also store
person/scope ids as plain ``String(36)`` without DB-level FK for the
same prod-drift reason.

The cron / API filter on ``scope_id`` is index-driven (the
``ix_cmdb_oc_scope_created`` composite below), so cascade lookups
stay fast without the FK helper.

See memory ``feedback_tidb_fk_strictness``.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "168"
down_revision = "167"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "cmdb_cloud_scope_owner_confirmations",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("scope_id", sa.String(36), nullable=False),
        # The person who was owner at the moment this event was created.
        # Captured even for transfer events — the row records "this owner
        # was asked to confirm" so a later owner change doesn't erase
        # who was on the hook at the time.
        sa.Column("owner_person_id", sa.String(36), nullable=True),
        sa.Column("kind", sa.String(64), nullable=False),
        # Pending → responded / escalated. ``responded`` covers all the
        # leaf kinds (confirmed / transferred / keep / cleanup /
        # handover_it). ``escalated`` is its own kind, not a status on
        # the pending row.
        sa.Column(
            "status",
            sa.String(32),
            nullable=False,
            server_default=sa.text("'pending'"),
        ),
        # Outcome — populated on the seed pending row once the owner
        # responds, OR set immediately on a "responded directly via UI"
        # row (no preceding cron card). NULL while pending.
        sa.Column("response_kind", sa.String(64), nullable=True),
        sa.Column("response_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("responded_by_person_id", sa.String(36), nullable=True),
        # For ``semiannual_transferred`` events, the chosen new owner.
        sa.Column("transferred_to_person_id", sa.String(36), nullable=True),
        # Free-text reason. Required for ``zero_spend_keep`` (owner must
        # justify keeping a $0-spend scope), optional otherwise.
        sa.Column("reason", sa.Text(), nullable=True),
        # Snapshot fields captured at the moment the pending card was
        # sent — so the audit-trail UI can show "what you saw when you
        # decided" even if cost-facts ingest / membership later change.
        sa.Column("spend_recent_30d_usd", sa.Numeric(14, 6), nullable=True),
        sa.Column("spend_recent_180d_usd", sa.Numeric(14, 6), nullable=True),
        sa.Column("spend_last_month_usd", sa.Numeric(14, 6), nullable=True),
        sa.Column("member_count_snapshot", sa.Integer(), nullable=True),
        sa.Column("notified_at", sa.DateTime(timezone=True), nullable=True),
        # Soft pointer to the outbox row that delivered the prompt.
        # No FK — outbox rows can be purged by the retention sweep
        # independent of timeline rows, and prod-vs-local schema drift
        # would make a hard FK declaration brittle (see top docstring).
        sa.Column("notification_outbox_id", sa.String(36), nullable=True),
        sa.Column("escalated_at", sa.DateTime(timezone=True), nullable=True),
        # Soft self-reference: the seed pending row that triggered this
        # escalation (only set on ``escalated_to_it`` rows). Threads
        # cause→consequence in the timeline view. Append-only model
        # makes a hard self-FK unnecessary.
        sa.Column("parent_confirmation_id", sa.String(36), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        mysql_engine="InnoDB",
        mysql_charset="utf8mb4",
    )
    op.create_index(
        "ix_cmdb_oc_scope_created",
        "cmdb_cloud_scope_owner_confirmations",
        ["scope_id", "created_at"],
    )
    op.create_index(
        "ix_cmdb_oc_kind_status",
        "cmdb_cloud_scope_owner_confirmations",
        ["kind", "status"],
    )
    op.create_index(
        "ix_cmdb_oc_owner_status",
        "cmdb_cloud_scope_owner_confirmations",
        ["owner_person_id", "status"],
    )

    op.add_column(
        "cmdb_cloud_scopes",
        sa.Column(
            "last_owner_confirmed_at",
            sa.DateTime(timezone=True),
            nullable=True,
        ),
    )


def downgrade() -> None:
    op.drop_column("cmdb_cloud_scopes", "last_owner_confirmed_at")
    op.drop_index(
        "ix_cmdb_oc_owner_status",
        table_name="cmdb_cloud_scope_owner_confirmations",
    )
    op.drop_index(
        "ix_cmdb_oc_kind_status",
        table_name="cmdb_cloud_scope_owner_confirmations",
    )
    op.drop_index(
        "ix_cmdb_oc_scope_created",
        table_name="cmdb_cloud_scope_owner_confirmations",
    )
    op.drop_table("cmdb_cloud_scope_owner_confirmations")