"""Software connector bindings — split credentials from per-software params.

Revision ID: 028
Revises: 027
Create Date: 2026-04-18

``software_connectors`` historically mixed credentials
(service_account_json, app_secret) with per-software params
(group_email, group_chat_id) in one ``config`` JSON column, identified
by a ``connector_type`` string. After this migration:

  * Credentials live on the ``integrations`` row pointed to by
    ``integration_id`` (already nullable FK since migration 025).
    ``integration_id`` is now NOT NULL.
  * Per-software params move to a new ``binding_config`` JSON column.
  * ``connector_type`` + ``config`` columns are dropped. The driver is
    derived from the linked integration's ``(kind, provider)`` pair.

Data migration: every row either already has an ``integration_id`` (and
its ``config`` is treated as binding_config verbatim), or it's migrated
by splitting ``config`` into credential keys → a fresh integration row
and remaining keys → ``binding_config``.

On local/prod at time of writing, ``software_connectors`` is empty, so
the backfill runs against zero rows; the code path is exercised only
once real bindings are introduced via the new admin UI.
"""
from __future__ import annotations

import json
import uuid
from typing import Any

import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for


revision = "028"
down_revision = "027"


# Per-old-connector-type: which keys in ``config`` were credentials vs
# binding params. Used by the data backfill to split an inline-config
# row into a new integration + a thin binding.
_CONNECTOR_TYPE_MAP: dict[str, tuple[str, set[str]]] = {
    # connector_type   →  (new provider, set of credential keys)
    "google_workspace_group": ("google_workspace", {"service_account_json", "impersonate_subject_email"}),
    "google_workspace_user":  ("google_workspace", {"service_account_json", "impersonate_subject_email"}),
    "feishu_group":            ("feishu_group",     {"app_id", "app_secret"}),
    "manual_notify":           ("manual",           set()),
}

# Per-old-connector-type: extra binding params to add that weren't
# explicit in the old config (e.g. Google Workspace "operation" was
# implied by the connector_type itself).
_IMPLIED_BINDING: dict[str, dict[str, Any]] = {
    "google_workspace_group": {"operation": "group_member"},
    "google_workspace_user":  {"operation": "user_suspend"},
}


def upgrade() -> None:
    ID = id_type_for("identity_persons")

    # 1) Add binding_config; columns nullable during the transition.
    op.add_column(
        "software_connectors",
        sa.Column("binding_config", sa.JSON, nullable=True),
    )

    # 2) Backfill every row.
    _backfill_bindings()

    # 3) Drop the now-redundant columns + tighten constraints.
    op.drop_column("software_connectors", "connector_type")
    op.drop_column("software_connectors", "config")
    op.alter_column(
        "software_connectors", "integration_id",
        existing_type=ID, nullable=False,
    )


def downgrade() -> None:
    """Restore the old column shape. Data is NOT reconstructed."""
    op.add_column(
        "software_connectors",
        sa.Column("connector_type", sa.String(32), nullable=True),
    )
    op.add_column(
        "software_connectors",
        sa.Column("config", sa.JSON, nullable=True),
    )
    op.execute(
        "UPDATE software_connectors SET config = JSON_OBJECT() "
        "WHERE config IS NULL"
    )
    op.alter_column(
        "software_connectors", "config",
        existing_type=sa.JSON, nullable=False,
    )
    op.alter_column(
        "software_connectors", "connector_type",
        existing_type=sa.String(32), nullable=False,
    )
    op.alter_column(
        "software_connectors", "integration_id",
        existing_type=id_type_for("identity_persons"), nullable=True,
    )
    op.drop_column("software_connectors", "binding_config")


# ────────────────────────────────────────────────────────────────
# Data migration
# ────────────────────────────────────────────────────────────────


def _backfill_bindings() -> None:
    bind = op.get_bind()
    rows = list(bind.execute(sa.text(
        "SELECT id, software_id, connector_type, config, integration_id "
        "FROM software_connectors"
    )).mappings())
    if not rows:
        return

    for row in rows:
        old_config = _json_load(row["config"])
        connector_type = row["connector_type"] or ""

        if row["integration_id"]:
            # Already linked — credentials live on the integration.
            # Treat the whole ``config`` as binding params, augmented by
            # any implied keys the connector_type embeds.
            binding = dict(old_config)
            binding.update(_IMPLIED_BINDING.get(connector_type, {}))
            bind.execute(sa.text(
                "UPDATE software_connectors SET binding_config = :bc WHERE id = :id"
            ), {
                "id": row["id"],
                "bc": json.dumps(binding) if binding else None,
            })
            continue

        # Inline-credential case: create a new integration + split config.
        provider, cred_keys = _CONNECTOR_TYPE_MAP.get(
            connector_type, (connector_type, set()),
        )
        creds = {k: v for k, v in old_config.items() if k in cred_keys}
        binding = {k: v for k, v in old_config.items() if k not in cred_keys}
        binding.update(_IMPLIED_BINDING.get(connector_type, {}))

        new_int_id = str(uuid.uuid4())
        slug = f"{provider}-{str(row['id']).replace('-', '')[:8]}"
        # NOTE: old ``config`` was stored plain-text (no
        # ``secrets_encrypted`` column existed on the old table), so we
        # can only preserve the non-secret part into ``config_public``.
        # The admin MUST re-enter secret fields via the new
        # /admin/integrations form after this migration — this is a
        # security-positive change (credentials were never encrypted
        # before).
        bind.execute(sa.text(
            "INSERT INTO integrations "
            "(id, kind, provider, name, slug, enabled, is_primary, "
            " config_public, secrets_encrypted, secrets_nonce) "
            "VALUES "
            "(:id, 'software_provisioning', :provider, :name, :slug, 1, 0, "
            " :public, NULL, NULL)"
        ), {
            "id": new_int_id,
            "provider": provider,
            "name": f"{provider} (migrated from software_connector {row['id']})",
            "slug": slug,
            # Only *non-secret* credential keys go here — anything the
            # driver considers secret needs re-entry. This dict ends up
            # holding nothing for GW/Feishu since both their cred keys
            # are secrets; admin will re-enter them.
            "public": None,
        })

        bind.execute(sa.text(
            "UPDATE software_connectors SET "
            "integration_id = :int_id, binding_config = :bc "
            "WHERE id = :id"
        ), {
            "id": row["id"],
            "int_id": new_int_id,
            "bc": json.dumps(binding) if binding else None,
        })

        if creds:
            # Surface a loud warning in the migration output so operators
            # know to re-enter the credentials.
            print(
                f"[028] software_connector {row['id']} ({connector_type}): "
                f"created integration {slug}. You MUST re-enter "
                f"{sorted(creds.keys())} in /admin/integrations.",
            )


def _json_load(v: Any) -> dict[str, Any]:
    if isinstance(v, dict):
        return v
    if isinstance(v, str) and v:
        try:
            return json.loads(v)
        except Exception:  # noqa: BLE001
            return {}
    return {}