"""Treat a past offboarding handover as the new owner's confirmation.

Revision ID: 532
Revises: 531

Policy (2026-08-24, product decision): when offboarding force-transfers a
cloud subscription off a leaver, the successor already receives the
"[自动转让] … N 项资产已转给你" push naming that subscription. That push IS
the confirmation — asking them again days later about the same handover is
telling them twice and calling the second one a governance control.

``hr_lifecycle/advance_service.py`` now stamps
``last_owner_confirmed_at`` at handover time. This migration applies the
same rule to handovers that already happened: every scope whose ownership
was force-transferred by offboarding and whose clock is still NULL gets
stamped with the timestamp of its most recent handover — the moment the
successor was actually told.

Why the handover time rather than ``now``: the 180-day clock should run
from when the person was informed, not from when this migration ran.
Stamping ``now`` would silently grant every one of these scopes a fresh
half-year of no questions.

Scope of the change, measured on production 2026-08-24: 11 scopes have
ever been force-transferred; 2 already carry a confirmation timestamp and
are left alone; 9 are NULL and get stamped (7 of those also have an open
prompt addressed to the departed owner, which the daily reminder sweep
voids on its next tick — the two mechanisms are independent and agree).

Idempotent: re-running changes nothing, because every candidate now has a
non-NULL clock.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "532"
down_revision = "531"
branch_labels = None
depends_on = None

_ACTION = "cmdb.cloud_scope.owner_force_transferred"


def upgrade() -> None:
    bind = op.get_bind()
    # Correlated subquery rather than a JOIN UPDATE: TiDB is fine with
    # both, but this form keeps the "most recent handover per scope"
    # semantics explicit and needs no derived-table alias.
    result = bind.execute(
        sa.text(
            """
            UPDATE cmdb_cloud_scopes s
               SET s.last_owner_confirmed_at = (
                     SELECT MAX(a.created_at) FROM audit_logs a
                      WHERE a.resource_type = 'cmdb_cloud_scope'
                        AND a.resource_id = s.id
                        AND a.action = :action
                        AND a.status = 'success'
                   )
             WHERE s.last_owner_confirmed_at IS NULL
               AND EXISTS (
                     SELECT 1 FROM audit_logs a2
                      WHERE a2.resource_type = 'cmdb_cloud_scope'
                        AND a2.resource_id = s.id
                        AND a2.action = :action
                        AND a2.status = 'success'
                   )
            """
        ),
        {"action": _ACTION},
    )
    print(
        f"532: stamped last_owner_confirmed_at on {result.rowcount} "
        "offboarding-handover scope(s)"
    )


def downgrade() -> None:
    # Deliberately NOT reversed. Clearing the column would re-mark these
    # scopes "never confirmed" and make their owners immediately due for a
    # card about a handover they were already told about — the exact
    # behaviour this migration exists to stop. The stamped value is
    # recoverable from the audit rows it was derived from.
    pass