"""Half-yearly owner-confirmation for cloud servers.

Adds two pieces:

1. ``cmdb_servers.last_owner_confirmed_at`` — timestamp stamped when
   the owner clicks "确认仍在使用" in the half-yearly confirmation
   card. The cron uses ``last_owner_confirmed_at < now - 180d`` to
   pick eligible servers; NULL is treated as "never confirmed →
   eligible".

2. ``cmdb_server_owner_confirmations`` — owner-aggregated tracking
   row. One row per (owner, period_start) per cron tick. Stores the
   snapshot of server UUIDs the owner was asked to confirm so a
   subsequent "确认全部" applies only to the original snapshot (an
   admin re-assigning a server mid-period doesn't accidentally get
   marked as confirmed).

   Unique key ``(owner_person_id, period_start)`` is the idempotency
   anchor — re-running the cron on the same day for the same owner
   is a no-op via ``INSERT … ON DUPLICATE KEY UPDATE`` semantics.

Companion: PR introduces the new model ``CmdbServerOwnerConfirmation``
(see ``app/domains/cmdb/models.py``), the service
``ServerOwnerConfirmationService``, the cron handler
``cmdb.server_owner_semiannual_confirmation``, the
``GET /cmdb/server-confirmations/mine`` / ``POST .../{id}/respond``
endpoints, and the matching MCP tool ``cloud_servers_my_owner_confirmations``.

Down-migration drops the table + column; do NOT run on prod after
confirmations have been issued (data loss).

Revision ID: 216
Revises: 215
Create Date: 2026-05-29

NOTE: This was originally written as revision 213; both 213 and 214
were claimed first on main (213 by PR #561 Confluence revoke,
214 by PR #563 Feishu downstream sync identity_sync_department_state,
215 by PR #563 seed_feishu_contact_target). Renamed to 216 +
``down_revision="215"`` per the alembic_revision_race rule.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa
from alembic import op


revision = "216"
down_revision = "215"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "cmdb_servers",
        sa.Column(
            "last_owner_confirmed_at",
            sa.DateTime(timezone=True),
            nullable=True,
        ),
    )

    op.create_table(
        "cmdb_server_owner_confirmations",
        sa.Column("id", sa.CHAR(36), primary_key=True),
        sa.Column("owner_person_id", sa.String(36), nullable=False),
        sa.Column("period_start", sa.Date(), nullable=False),
        sa.Column("server_ids", sa.JSON(), nullable=False),
        sa.Column(
            "status",
            sa.String(32),
            nullable=False,
            server_default="pending",
        ),
        sa.Column("response_kind", sa.String(32), nullable=True),
        sa.Column("responded_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("notified_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("notification_outbox_id", sa.String(36), nullable=True),
        sa.Column("escalated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.UniqueConstraint(
            "owner_person_id", "period_start",
            name="uq_cmdb_server_owner_conf_owner_period",
        ),
    )
    op.create_index(
        "ix_cmdb_server_owner_conf_owner_status",
        "cmdb_server_owner_confirmations",
        ["owner_person_id", "status"],
    )

    # Seed the cron row. Idempotent — re-runs (or environments that
    # already created the row out-of-band) hit the NOT EXISTS guard.
    bind = op.get_bind()
    if bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scheduled_jobs'"
        )
    ).fetchone() is not None:
        bind.execute(
            sa.text(
                "INSERT INTO scheduled_jobs "
                "(id, name, display_name, description, handler_key, "
                "trigger_type, cron_expr, timezone, enabled, config, "
                "run_count, failure_count, created_at, updated_at) "
                "SELECT :id, :name, :display, :desc, :handler, 'cron', "
                ":cron, 'Asia/Shanghai', 1, '{}', 0, 0, NOW(), NOW() "
                "FROM DUAL WHERE NOT EXISTS "
                "(SELECT 1 FROM scheduled_jobs WHERE name = :name)"
            ),
            {
                "id": str(uuid.uuid4()),
                "name": "cmdb.server_owner_semiannual_confirmation",
                "display": "云服务器 Owner 半年盘点",
                "desc": (
                    "每周一 09:00（Asia/Shanghai）扫描所有有 owner 的云服务器,"
                    "按 owner 聚合发送半年盘点确认卡片。"
                    "跳过规则:无 owner / owner 离职 / 新机器(<90 天)/ 已停机或下线。"
                ),
                "handler": "cmdb.server_owner_semiannual_confirmation",
                "cron": "0 9 * * 1",  # Mon 09:00 Shanghai
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scheduled_jobs'"
        )
    ).fetchone() is not None:
        bind.execute(
            sa.text(
                "DELETE FROM scheduled_jobs WHERE handler_key = "
                "'cmdb.server_owner_semiannual_confirmation'"
            )
        )
    op.drop_index(
        "ix_cmdb_server_owner_conf_owner_status",
        table_name="cmdb_server_owner_confirmations",
    )
    op.drop_table("cmdb_server_owner_confirmations")
    op.drop_column("cmdb_servers", "last_owner_confirmed_at")