"""Role-change scope review — add ``review_status`` +
``review_status_changed_at`` columns on ``cmdb_cloud_access_grants``,
plus a seed row for the daily ``role_change.scope_review_sweep`` cron.

Revision ID: 236
Revises: 235
Create Date: 2026-06-01

Background
==========

Issue #623 — when an employee changes department / title / line-manager,
the platform's existing role-change preview/apply path (added much
earlier in ``ProvisioningService``) re-evaluates default policies for
the new dept, but does NOT review old-dept-associated cloud scope
access. Without this column there is no way to mark "this grant was
issued because the holder was in old dept X; new dept is Y; a human
must decide keep-or-revoke."

The policy decision (do NOT auto-revoke; instead flag for human review;
30-day stalled-review escalation) is documented in
`docs/ops/cloud-lifecycle-ops.md` and was captured on the issue.

Schema
======

* ``review_status``           NULL | pending_review | confirmed_keep |
                              confirmed_revoke. Default NULL.
* ``review_status_changed_at`` UTC timestamp; set whenever
  ``review_status`` is written. Used by the 30-day sweep so the cron
  doesn't trigger on rows merely UPDATEd (``updated_at`` would bump on
  every unrelated edit). NULL when ``review_status`` is also NULL.

* Composite index ``(review_status, review_status_changed_at)`` so the
  daily sweep filter `WHERE review_status='pending_review' AND
  review_status_changed_at < now-30d` is index-only.

Idempotent — re-running this migration is a no-op (information_schema
probes guard every DDL).
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "236"
down_revision = "235"
branch_labels = None
depends_on = None

_TABLE = "cmdb_cloud_access_grants"


def _column_exists(table: str, column: str) -> bool:
    row = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM information_schema.COLUMNS WHERE "
                "TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
                "AND COLUMN_NAME = :c LIMIT 1"
            ),
            {"t": table, "c": column},
        )
        .scalar()
    )
    return bool(row)


def _index_exists(table: str, index: str) -> bool:
    row = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM information_schema.STATISTICS WHERE "
                "TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
                "AND INDEX_NAME = :i LIMIT 1"
            ),
            {"t": table, "i": index},
        )
        .scalar()
    )
    return bool(row)


def upgrade() -> None:
    if not _column_exists(_TABLE, "review_status"):
        op.add_column(
            _TABLE,
            sa.Column("review_status", sa.String(32), nullable=True),
        )

    if not _column_exists(_TABLE, "review_status_changed_at"):
        op.add_column(
            _TABLE,
            sa.Column(
                "review_status_changed_at",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )

    # Composite index for the daily sweep:
    #   WHERE review_status='pending_review'
    #         AND review_status_changed_at < now - 30d
    if not _index_exists(_TABLE, "idx_grants_review_status_changed"):
        op.create_index(
            "idx_grants_review_status_changed",
            _TABLE,
            ["review_status", "review_status_changed_at"],
        )

    # Seed the daily sweep cron row. 02:00 UTC = 10:00 SH = early
    # business hours in HQ but not so early that a Feishu card buries
    # an admin during normal review windows.
    exists = (
        op.get_bind()
        .execute(
            sa.text(
                "SELECT 1 FROM scheduled_jobs "
                "WHERE name = 'role_change.scope_review_sweep' LIMIT 1"
            ),
        )
        .scalar()
    )
    if not exists:
        op.get_bind().execute(
            sa.text(
                """
                INSERT INTO scheduled_jobs
                    (id, name, display_name, description, handler_key,
                     trigger_type, interval_seconds, cron_expr, timezone,
                     enabled, config, run_count, failure_count,
                     created_at, updated_at)
                VALUES
                    (UUID(),
                     'role_change.scope_review_sweep',
                     '转岗 pending_review 扫描',
                     '每天 10:00 (Asia/Shanghai) 扫描 cmdb_cloud_access_grants 中 '
                     'review_status=pending_review 且 review_status_changed_at '
                     '已过 30 天的行,向 scope owner + IT 推 Feishu 卡催决断。 '
                     '幂等:NotificationOutbox 的 (template_key, related_resource_id) '
                     '存在即跳过。其他 governance/lifecycle 类 cron 均跑 SH 时区, '
                     '保持一致。',
                     'role_change.scope_review_sweep', 'cron', NULL,
                     '0 10 * * *', 'Asia/Shanghai',
                     1, JSON_OBJECT(), 0, 0, NOW(), NOW())
                """
            )
        )


def downgrade() -> None:
    if _index_exists(_TABLE, "idx_grants_review_status_changed"):
        op.drop_index("idx_grants_review_status_changed", table_name=_TABLE)
    if _column_exists(_TABLE, "review_status_changed_at"):
        op.drop_column(_TABLE, "review_status_changed_at")
    if _column_exists(_TABLE, "review_status"):
        op.drop_column(_TABLE, "review_status")
    op.get_bind().execute(
        sa.text(
            "DELETE FROM scheduled_jobs "
            "WHERE name = 'role_change.scope_review_sweep'"
        ),
    )