"""Add IAM capability to AliCloud (海外) integration row.

Revision ID: 067
Revises: 066
Create Date: 2026-04-25

Context: ``AlicloudIntlProvider`` now declares ``ProviderCapability.IAM``
(per-sub-account RAM ``ListUsers`` via STS AssumeRole). The prod row
predates this change and has ``capabilities`` frozen at the original
six (servers, domains, dns_records, cdn, object_storage,
load_balancers), which means
``scheduled_jobs_provisioning._resolve_capabilities`` returns those six
verbatim and the ``cmdb.sync.alicloud-intl-primary.iam`` scheduled_jobs
row never gets created on next worker boot.

This migration appends ``"iam"`` to the ``capabilities`` JSON array for
every ``alicloud_intl`` integration row that doesn't already have it.
The ScheduledJobsService reconcile loop runs at worker startup and
auto-creates the missing IAM cron entry. No application code change
needed beyond what already shipped in this PR.

Idempotent — re-running is a no-op (only updates rows where ``iam``
is not already present).

Aliyun (国内) intentionally NOT touched — the domestic tenant uses a
single-account credential model with no Resource Directory, so RAM
``ListUsers`` against it would just produce the master-account RAM
users (a tiny set). Will be enabled separately if and when 国内
becomes IAM-relevant.

Downgrade reverses by stripping ``"iam"`` back out — same idempotent
shape.
"""
from __future__ import annotations

from alembic import op

# revision identifiers, used by Alembic.
revision = "067"
down_revision = "066"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # MySQL JSON_ARRAY_APPEND with the JSON_SEARCH guard — atomic, no
    # row-by-row Python iteration. ``JSON_SEARCH(... 'iam')`` returns
    # NULL when ``iam`` is not in the array, so the WHERE clause skips
    # already-migrated rows.
    op.execute(
        """
        UPDATE integrations
        SET capabilities = JSON_ARRAY_APPEND(
            COALESCE(capabilities, JSON_ARRAY()),
            '$',
            'iam'
        )
        WHERE provider = 'alicloud_intl'
          AND kind = 'cloud'
          AND (
            capabilities IS NULL
            OR JSON_SEARCH(capabilities, 'one', 'iam') IS NULL
          )
        """
    )


def downgrade() -> None:
    # Reverse: strip ``iam`` back out. ``JSON_REMOVE`` needs the JSON
    # path which JSON_SEARCH gives us. ``JSON_UNQUOTE(JSON_SEARCH)``
    # returns the path string (e.g. ``"$[6]"``) for in-place removal.
    op.execute(
        """
        UPDATE integrations
        SET capabilities = JSON_REMOVE(
            capabilities,
            JSON_UNQUOTE(JSON_SEARCH(capabilities, 'one', 'iam'))
        )
        WHERE provider = 'alicloud_intl'
          AND kind = 'cloud'
          AND JSON_SEARCH(capabilities, 'one', 'iam') IS NOT NULL
        """
    )