"""Add durable retry scheduling for approval submission intents.

Revision ID: 449
Revises: 448
"""

import sqlalchemy as sa

from alembic import op

revision = "449"
down_revision = "448"
branch_labels = None
depends_on = None

_TABLE = "approval_submission_intents"
_COLUMN = "next_attempt_at"
_INDEX = "ix_approval_submission_intents_next_attempt_at"


def _column_names() -> set[str]:
    return {
        str(column["name"])
        for column in sa.inspect(op.get_bind()).get_columns(_TABLE)
    }


def _index_names() -> set[str]:
    return {
        str(index["name"])
        for index in sa.inspect(op.get_bind()).get_indexes(_TABLE)
        if index.get("name")
    }


def upgrade() -> None:
    # MySQL/TiDB DDL is non-transactional. Inspect each independently so a
    # process killed after ADD COLUMN can safely resume at CREATE INDEX.
    if _COLUMN not in _column_names():
        op.add_column(
            _TABLE,
            sa.Column(_COLUMN, sa.DateTime(timezone=True), nullable=True),
        )
    if _INDEX not in _index_names():
        op.create_index(_INDEX, _TABLE, [_COLUMN], unique=False)

    # Compact rows that reached a terminal state before terminal-v1 existed,
    # and scrub historical provider errors even when a row was already compacted.
    # JSON_EXTRACT is supported by both MySQL 8 and TiDB. Dedicated columns
    # remain the authoritative replay/audit anchors; the JSON receipt only
    # points back to the materialized domain resource.
    op.execute(sa.text("""
        UPDATE approval_submission_intents
        SET payload = CASE
          WHEN COALESCE(
              JSON_UNQUOTE(JSON_EXTRACT(payload, '$.schema')), ''
          ) <> 'terminal-v1' THEN JSON_OBJECT(
            'schema', 'terminal-v1',
            'resource_type', CASE
                WHEN state = 'failed' THEN 'approval_submission'
                WHEN scope = 'asset_request' THEN 'employee_request'
                WHEN scope = 'api_key_request' THEN 'api_key_assignment'
                WHEN scope = 'announcement_request' THEN 'announcement_request'
                WHEN scope = 'vpn_application_request' THEN 'vpn_application'
                WHEN scope = 'ai_tool_application_request' THEN 'ai_tool_application'
                WHEN scope = 'domain_registration_request' THEN 'domain_registration'
                WHEN scope = 'sso_grant_request' THEN 'sso_grant'
                WHEN scope = 'identity_consumer_request' THEN 'identity_consumer_request'
                WHEN scope = 'data_access_group_grant' THEN 'data_access_request'
                WHEN scope LIKE 'cmdb_scope:%' THEN 'cmdb_cloud_scope_request'
                WHEN scope LIKE 'cmdb_dns%' THEN 'cmdb_dns_record_request'
                ELSE 'approval_submission'
            END,
            'resource_id', CASE
                WHEN state = 'failed' THEN CAST(id AS CHAR)
                WHEN scope = 'asset_request' THEN CAST(employee_request_id AS CHAR)
                WHEN scope = 'api_key_request' THEN idempotency_key
                WHEN scope IN (
                    'announcement_request', 'data_access_group_grant'
                ) OR scope LIKE 'cmdb_scope:%' OR scope LIKE 'cmdb_dns%'
                    THEN JSON_UNQUOTE(JSON_EXTRACT(payload, '$.finalization.request_id'))
                WHEN scope IN (
                    'vpn_application_request', 'ai_tool_application_request'
                ) THEN JSON_UNQUOTE(
                    JSON_EXTRACT(payload, '$.finalization.application_id')
                )
                WHEN scope = 'domain_registration_request' THEN JSON_UNQUOTE(
                    JSON_EXTRACT(payload, '$.finalization.domain_registration_id')
                )
                WHEN scope = 'sso_grant_request' THEN JSON_UNQUOTE(
                    JSON_EXTRACT(payload, '$.finalization.grant_id')
                )
                WHEN scope = 'identity_consumer_request' THEN JSON_UNQUOTE(
                    JSON_EXTRACT(payload, '$.finalization.consumer_request_id')
                )
                ELSE CAST(id AS CHAR)
            END
          )
          ELSE payload
        END,
        next_attempt_at = NULL,
        last_error = NULL
        WHERE state IN ('completed', 'compensated', 'failed')
    """))


def downgrade() -> None:
    if _INDEX in _index_names():
        op.drop_index(_INDEX, table_name=_TABLE)
    if _COLUMN in _column_names():
        op.drop_column(_TABLE, _COLUMN)