"""Unified integrations framework (phase 1).

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

Phase 1 lifts the ``platform_configs`` table (Feishu + SuccessFactors)
onto the new ``integrations`` table. Every kind of external system the
platform talks to will flow through this table going forward; cloud
providers (currently in ``cmdb_provider_accounts``) and software
provisioning connectors (currently in ``software_connectors``) will
be migrated in follow-up phases once the consumer code catches up.

This phase also:
  * Evolves the existing ``integrations`` table (added in 025 for just
    GW / software-connector credentials) to support the generalized
    model: kind + slug + is_primary + capabilities + structured health
    state.
  * Creates ``integration_approval_flows`` so Feishu approval_code
    mappings are data-driven (no more hardcoded ``approval_xxx_code``
    fields in code — admin adds a row in the UI).
  * Creates ``platform_settings`` for non-integration platform config
    (grace periods, defaults, etc.).

Old tables retained for now (migrated in follow-ups):
  * cmdb_provider_accounts → integrations(kind='cloud')  [phase 2]
  * software_connectors.credentials → integrations(kind='software_provisioning')  [phase 3]
"""
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, table_kw_for


revision = "027"
down_revision = "026"


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

    # ─── Evolve `integrations` schema ───
    op.add_column("integrations", sa.Column("kind", sa.String(32), nullable=True))
    op.add_column("integrations", sa.Column("slug", sa.String(64), nullable=True))
    op.add_column(
        "integrations",
        sa.Column("is_primary", sa.Boolean, nullable=False, server_default=sa.text("0")),
    )
    op.add_column("integrations", sa.Column("capabilities", sa.JSON, nullable=True))
    op.add_column("integrations", sa.Column("last_test_status", sa.String(16), nullable=True))
    op.add_column("integrations", sa.Column("last_test_message", sa.Text, nullable=True))
    op.add_column(
        "integrations",
        sa.Column(
            "updated_by",
            ID,
            sa.ForeignKey("identity_persons.id"),
            nullable=True,
        ),
    )

    # Rename provider_type → provider; align secret column names with
    # the old platform_configs naming (shared encrypt helpers).
    op.alter_column(
        "integrations", "provider_type",
        new_column_name="provider", existing_type=sa.String(32),
        existing_nullable=False,
    )
    op.alter_column(
        "integrations", "config_encrypted",
        new_column_name="secrets_encrypted", existing_type=sa.Text,
        existing_nullable=True,
    )
    op.alter_column(
        "integrations", "config_nonce",
        new_column_name="secrets_nonce", existing_type=sa.String(64),
        existing_nullable=True,
    )
    op.alter_column(
        "integrations", "non_secret_config",
        new_column_name="config_public", existing_type=sa.JSON,
        existing_nullable=True,
    )
    op.alter_column(
        "integrations", "last_test_at",
        new_column_name="last_tested_at",
        existing_type=sa.DateTime(timezone=True),
        existing_nullable=True,
    )
    op.alter_column(
        "integrations", "is_active",
        new_column_name="enabled", existing_type=sa.Boolean,
        existing_nullable=False,
    )
    # Drop legacy boolean health columns, replaced by status+message
    op.drop_column("integrations", "last_test_success")
    op.drop_column("integrations", "last_test_error")

    op.drop_constraint("uq_integrations_name", "integrations", type_="unique")
    op.drop_index("ix_integrations_provider_active", table_name="integrations")

    # ─── Sibling tables ───
    op.create_table(
        "integration_approval_flows",
        sa.Column("id", ID, primary_key=True),
        sa.Column(
            "integration_id", ID,
            sa.ForeignKey("integrations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("flow_key", sa.String(64), nullable=False),
        sa.Column("label", sa.String(128), nullable=False),
        sa.Column("external_code", sa.String(128), nullable=False),
        sa.Column("payload_schema", sa.JSON, nullable=True),
        sa.Column("active", sa.Boolean, nullable=False, server_default=sa.text("1")),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.UniqueConstraint(
            "integration_id", "flow_key",
            name="uq_integration_approval_flows_integration_flow_key",
        ),
        **KW,
    )
    op.create_index(
        "ix_integration_approval_flows_flow_key",
        "integration_approval_flows", ["flow_key"],
    )

    op.create_table(
        "platform_settings",
        sa.Column("key", sa.String(128), primary_key=True),
        sa.Column("value", sa.JSON, nullable=False),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column(
            "updated_by", ID,
            sa.ForeignKey("identity_persons.id"),
            nullable=True,
        ),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        **KW,
    )

    # ─── Migrate platform_configs rows → integrations (+ approval_flows) ───
    _migrate_platform_configs_to_integrations()

    # ─── Finalize integrations constraints ───
    # Backfill kind for any pre-025 rows that don't map cleanly
    # (the legacy row was provider_type='legacy_cmdb' → kind='cloud').
    op.execute(
        "UPDATE integrations SET kind='cloud' WHERE kind IS NULL"
    )
    op.alter_column(
        "integrations", "kind",
        existing_type=sa.String(32), nullable=False,
    )

    op.execute(
        "UPDATE integrations SET slug=CONCAT(provider, '-', SUBSTRING(id, 1, 8)) "
        "WHERE slug IS NULL"
    )
    op.alter_column(
        "integrations", "slug",
        existing_type=sa.String(64), nullable=False,
    )
    op.create_unique_constraint(
        "uq_integrations_slug", "integrations", ["slug"],
    )
    op.create_index(
        "ix_integrations_kind_provider",
        "integrations", ["kind", "provider"],
    )
    op.create_index(
        "ix_integrations_kind_primary",
        "integrations", ["kind", "is_primary"],
    )
    op.create_index(
        "ix_integrations_enabled", "integrations", ["enabled"],
    )

    # ─── Drop platform_configs; its data now lives in integrations ───
    op.drop_table("platform_configs")


def downgrade() -> None:
    ID = id_type_for("identity_persons")
    KW = table_kw_for("identity_persons")

    # Restore (empty) platform_configs schema; data is not replayed.
    op.create_table(
        "platform_configs",
        sa.Column("id", ID, primary_key=True),
        sa.Column("category", sa.String(64), nullable=False),
        sa.Column("display_name", sa.String(128), nullable=False),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column("secrets_encrypted", sa.Text, nullable=True),
        sa.Column("secrets_nonce", sa.String(64), nullable=True),
        sa.Column("public_config", sa.JSON, nullable=True),
        sa.Column(
            "updated_by", ID,
            sa.ForeignKey("identity_persons.id"), nullable=True,
        ),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.UniqueConstraint("category", name="uq_platform_configs_category"),
        **KW,
    )

    op.drop_index("ix_integrations_enabled", table_name="integrations")
    op.drop_index("ix_integrations_kind_primary", table_name="integrations")
    op.drop_index("ix_integrations_kind_provider", table_name="integrations")
    op.drop_constraint("uq_integrations_slug", "integrations", type_="unique")
    op.drop_column("integrations", "slug")
    op.drop_column("integrations", "kind")
    op.drop_column("integrations", "is_primary")
    op.drop_column("integrations", "capabilities")
    op.drop_column("integrations", "last_test_status")
    op.drop_column("integrations", "last_test_message")
    op.drop_column("integrations", "updated_by")

    op.drop_table("platform_settings")
    op.drop_table("integration_approval_flows")

    op.alter_column(
        "integrations", "provider",
        new_column_name="provider_type", existing_type=sa.String(32),
    )
    op.alter_column(
        "integrations", "secrets_encrypted",
        new_column_name="config_encrypted", existing_type=sa.Text,
    )
    op.alter_column(
        "integrations", "secrets_nonce",
        new_column_name="config_nonce", existing_type=sa.String(64),
    )
    op.alter_column(
        "integrations", "config_public",
        new_column_name="non_secret_config", existing_type=sa.JSON,
    )
    op.alter_column(
        "integrations", "last_tested_at",
        new_column_name="last_test_at", existing_type=sa.DateTime(timezone=True),
    )
    op.alter_column(
        "integrations", "enabled",
        new_column_name="is_active", existing_type=sa.Boolean,
    )
    op.add_column(
        "integrations",
        sa.Column("last_test_success", sa.Boolean, nullable=True),
    )
    op.add_column(
        "integrations",
        sa.Column("last_test_error", sa.Text, nullable=True),
    )
    op.create_unique_constraint(
        "uq_integrations_name", "integrations", ["name"],
    )
    op.create_index(
        "ix_integrations_provider_active",
        "integrations", ["provider_type", "is_active"],
    )


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


_FEISHU_FLOW_LABELS = {
    "approval_laptop_replacement_code": ("laptop_replacement", "电脑更换"),
    "approval_computer_borrow_code":    ("computer_borrow", "电脑借用"),
    "approval_asset_return_code":       ("asset_return", "资产归还"),
    "approval_offboarding_buyback_code": ("offboarding_buyback", "离职回购"),
    "approval_software_seat_code":      ("software_seat", "软件席位申请"),
    "approval_api_key_request_code":    ("api_key_request", "API Key 申请"),
}


def _migrate_platform_configs_to_integrations() -> None:
    """Port every ``platform_configs`` row to an ``integrations`` row.

    Handles:
      * ``feishu`` — becomes ``kind=chat_approval``, ``provider=feishu``.
        Each ``approval_*_code`` key explodes to one
        ``integration_approval_flows`` row so future approval flows can
        be added via the admin UI without code changes.
      * ``successfactors`` — becomes ``kind=hris``, ``provider=successfactors``.
      * Any other category — treated as ``kind=<category>,
        provider=<category>``; admin can re-classify afterwards.
    """
    bind = op.get_bind()
    configs = list(bind.execute(sa.text(
        "SELECT id, category, display_name, description, "
        "secrets_encrypted, secrets_nonce, public_config "
        "FROM platform_configs"
    )).mappings())
    if not configs:
        return

    for row in configs:
        category = row["category"]
        public_config: dict[str, Any] = _json_load(row["public_config"])

        if category == "feishu":
            kind, provider, slug = "chat_approval", "feishu", "feishu-primary"
            flow_entries: list[tuple[str, str, str]] = []
            remaining_public: dict[str, Any] = {}
            for k, v in public_config.items():
                if k in _FEISHU_FLOW_LABELS and isinstance(v, str) and v:
                    flow_key, label = _FEISHU_FLOW_LABELS[k]
                    flow_entries.append((flow_key, label, v))
                else:
                    remaining_public[k] = v

            new_id = _insert_integration(
                bind,
                kind=kind, provider=provider,
                name=row["display_name"] or "飞书 (Lark)",
                slug=slug, description=row["description"],
                is_primary=True, enabled=True,
                public=remaining_public,
                secrets_encrypted=row["secrets_encrypted"],
                secrets_nonce=row["secrets_nonce"],
            )
            for flow_key, label, code in flow_entries:
                bind.execute(sa.text(
                    "INSERT INTO integration_approval_flows "
                    "(id, integration_id, flow_key, label, external_code, active) "
                    "VALUES (:id, :int_id, :flow_key, :label, :code, 1)"
                ), {
                    "id": str(uuid.uuid4()),
                    "int_id": new_id,
                    "flow_key": flow_key,
                    "label": label,
                    "code": code,
                })

        elif category == "successfactors":
            _insert_integration(
                bind,
                kind="hris", provider="successfactors",
                name=row["display_name"] or "SuccessFactors HRIS",
                slug="successfactors-primary",
                description=row["description"],
                is_primary=True, enabled=True,
                public=public_config,
                secrets_encrypted=row["secrets_encrypted"],
                secrets_nonce=row["secrets_nonce"],
            )

        else:
            _insert_integration(
                bind,
                kind=category, provider=category,
                name=row["display_name"] or category,
                slug=f"{category}-primary",
                description=row["description"],
                is_primary=True, enabled=True,
                public=public_config,
                secrets_encrypted=row["secrets_encrypted"],
                secrets_nonce=row["secrets_nonce"],
            )


def _insert_integration(
    bind,
    *,
    kind: str,
    provider: str,
    name: str,
    slug: str,
    description: str | None,
    is_primary: bool,
    enabled: bool,
    public: dict[str, Any],
    secrets_encrypted: str | None,
    secrets_nonce: str | None,
) -> str:
    new_id = str(uuid.uuid4())
    bind.execute(sa.text(
        "INSERT INTO integrations "
        "(id, kind, provider, name, slug, description, enabled, is_primary, "
        " config_public, secrets_encrypted, secrets_nonce) "
        "VALUES "
        "(:id, :kind, :provider, :name, :slug, :description, :enabled, :is_primary, "
        " :config_public, :secrets_encrypted, :secrets_nonce)"
    ), {
        "id": new_id,
        "kind": kind,
        "provider": provider,
        "name": name,
        "slug": slug,
        "description": description,
        "enabled": 1 if enabled else 0,
        "is_primary": 1 if is_primary else 0,
        "config_public": json.dumps(public) if public else None,
        "secrets_encrypted": secrets_encrypted,
        "secrets_nonce": secrets_nonce,
    })
    return new_id


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 {}