"""External-origin approval flows + seed SOC2 邮件组 grant flow.

Until now ``handle_feishu_callback`` silently dropped any webhook for
an ``instance_code`` that wasn't already in ``approval_requests`` —
because the handler assumed all flows are Helix-initiated (Helix
calls ``instance/create`` first, which writes the row, and only
then does Feishu push events back). That assumption holds for every
domain we've built so far.

The SOC2 邮件组 flow breaks it. Submission happens directly in
Feishu (an existing org process; IT is the final approver and was
manually pasting the applicant into ``soc2@pingcap.com``). To
automate it we let Feishu push webhooks for ALL instances of the
SOC2 template (subscribe is per-template, not per-instance) and
handle them even when Helix never created the parent row.

Design:

* New column ``accept_external_origin`` on
  ``integration_approval_flows``. Defaults to ``0`` so existing
  Helix-initiated flows preserve the strict silent-drop behavior
  (no behavior change for any of the 13 existing flow_keys).
* When the callback handler sees an unknown ``instance_code`` and
  the matching flow row has ``accept_external_origin = 1``, it
  synthesizes an ``approval_requests`` row from the webhook
  ``instance_detail`` and continues into the normal domain
  dispatcher.
* Seed one row for the SOC2 邮件组 flow (Feishu template
  ``9A42C433-6A4B-411C-9B2E-9182BEEBC6BD``), bound to the primary
  chat_approval / feishu integration, with
  ``accept_external_origin = 1``.

The seed is best-effort: skipped if no enabled feishu integration
exists (fresh local dev) or if the row already exists (re-run /
prod where it was inserted manually for testing). Idempotent.

Revision ID: 149
Revises: 148
Create Date: 2026-05-12
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "149"
down_revision = "148"
branch_labels = None
depends_on = None


_SOC2_FLOW_KEY = "soc2_email_group_grant"
_SOC2_FLOW_LABEL = "SOC2 邮件组授权"
_SOC2_EXTERNAL_CODE = "9A42C433-6A4B-411C-9B2E-9182BEEBC6BD"


def upgrade() -> None:
    op.add_column(
        "integration_approval_flows",
        sa.Column(
            "accept_external_origin",
            sa.Boolean(),
            nullable=False,
            server_default=sa.text("0"),
        ),
    )

    bind = op.get_bind()

    # Find the primary enabled Feishu chat_approval integration.
    # We do NOT hardcode the integration UUID — it differs between
    # local dev and prod, and 027_unified_integrations generates a
    # fresh UUID per env.
    row = bind.execute(
        sa.text(
            "SELECT id FROM integrations "
            "WHERE kind = 'chat_approval' AND provider = 'feishu' "
            "  AND enabled = 1 "
            "ORDER BY is_primary DESC, created_at ASC LIMIT 1"
        )
    ).fetchone()
    if not row:
        # Fresh DB without a Feishu integration — admin will configure
        # it later, and the rerun-safe migration 149 has nothing to
        # seed. The column add above is sufficient.
        return
    feishu_integration_id = row[0]

    existing = bind.execute(
        sa.text(
            "SELECT id FROM integration_approval_flows "
            "WHERE integration_id = :int_id AND flow_key = :flow_key"
        ),
        {"int_id": feishu_integration_id, "flow_key": _SOC2_FLOW_KEY},
    ).fetchone()
    if existing:
        # Already seeded (e.g. inserted manually in prod before
        # this migration shipped). Flip ``accept_external_origin``
        # ON only when the column is currently NULL or 0 — we must
        # NOT silently re-enable a row an admin deliberately disabled
        # after an incident. ``active`` is left alone for the same
        # reason. (Round-21 review fix: original UPDATE was blanket
        # and would clobber operator intent on re-runs.)
        bind.execute(
            sa.text(
                "UPDATE integration_approval_flows "
                "SET accept_external_origin = 1 "
                "WHERE id = :id "
                "  AND (accept_external_origin IS NULL "
                "       OR accept_external_origin = 0)"
            ),
            {"id": existing[0]},
        )
        return

    bind.execute(
        sa.text(
            "INSERT INTO integration_approval_flows "
            "(id, integration_id, flow_key, label, external_code, "
            " active, accept_external_origin) "
            "VALUES (:id, :int_id, :flow_key, :label, :code, 1, 1)"
        ),
        {
            "id": str(uuid.uuid4()),
            "int_id": feishu_integration_id,
            "flow_key": _SOC2_FLOW_KEY,
            "label": _SOC2_FLOW_LABEL,
            "code": _SOC2_EXTERNAL_CODE,
        },
    )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(
        sa.text(
            "DELETE FROM integration_approval_flows "
            "WHERE flow_key = :flow_key"
        ),
        {"flow_key": _SOC2_FLOW_KEY},
    )
    op.drop_column("integration_approval_flows", "accept_external_origin")