"""Simplify offboarding policy surface.

Revision ID: 033
Revises: 032
Create Date: 2026-04-20

Structural cleanup based on review feedback:

1. **Offboarding policy belongs to the software, not its connector.**
   A software with ``policy=sso_cascade`` should not require a
   connector at all; a software with ``policy=auto_revoke`` must have
   one. The old design forced every software to have a connector just
   so you could stamp a policy on it.

   → Move ``offboarding_policy`` + ``offboarding_config`` from
     ``software_connectors`` onto ``asset_software_catalog``.

2. **Seed a default Email (SMTP) integration row** so IT admins see
   it in the Integrations list on first boot and only have to paste
   host + credentials to activate it. Matches the pattern the Feishu
   / SuccessFactors rows established in migration 027.

3. **Seed platform_settings keys** that the notification service
   references in templates (``company.display_name``, ``it.contact_email``,
   ``portal.base_url``) so new-hire welcome emails and leaver
   reminders render sensibly out of the box. Values are placeholders;
   admin edits via /admin/platform-settings.

Note: ``cloud_identity`` kind is being retired at the driver layer
(AWS IAM revoke folds into the existing ``cloud/aws`` driver) — no DB
change needed because kind is a plain string and no row references
``cloud_identity`` yet.
"""
from __future__ import annotations

import json
import uuid

import sqlalchemy as sa
from alembic import op


revision = "033"
down_revision = "032"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── (1) Move offboarding policy onto the software row ────────
    op.add_column(
        "asset_software_catalog",
        sa.Column(
            "offboarding_policy",
            sa.String(32),
            nullable=False,
            server_default="sso_cascade",
        ),
    )
    op.add_column(
        "asset_software_catalog",
        sa.Column("offboarding_config", sa.JSON, nullable=True),
    )

    # Backfill: carry over whatever policy was stored on the linked
    # software_connectors row (if any). One software → one connector,
    # so a simple subquery is enough.
    op.execute(
        """
        UPDATE asset_software_catalog sw
        LEFT JOIN software_connectors sc ON sc.software_id = sw.id
        SET sw.offboarding_policy   = COALESCE(sc.offboarding_policy, 'sso_cascade'),
            sw.offboarding_config   = sc.offboarding_config
        """
    )

    op.drop_column("software_connectors", "offboarding_policy")
    op.drop_column("software_connectors", "offboarding_config")

    # ── (2) Seed default Email (SMTP) integration ────────────────
    _seed_email_integration()

    # ── (3) Seed platform_settings with notification defaults ────
    _seed_platform_settings()


def downgrade() -> None:
    op.add_column(
        "software_connectors",
        sa.Column(
            "offboarding_policy",
            sa.String(32),
            nullable=False,
            server_default="sso_cascade",
        ),
    )
    op.add_column(
        "software_connectors",
        sa.Column("offboarding_config", sa.JSON, nullable=True),
    )

    op.execute(
        """
        UPDATE software_connectors sc
        LEFT JOIN asset_software_catalog sw ON sw.id = sc.software_id
        SET sc.offboarding_policy   = COALESCE(sw.offboarding_policy, 'sso_cascade'),
            sc.offboarding_config   = sw.offboarding_config
        """
    )

    op.drop_column("asset_software_catalog", "offboarding_config")
    op.drop_column("asset_software_catalog", "offboarding_policy")

    # Seed rows are kept on downgrade (the upgrade-time insert is
    # idempotent, and removing them on downgrade could surprise an
    # admin who has edited them). Operators can delete manually if
    # needed.


# ────────────────────────────────────────────────────────────────
# Seed helpers
# ────────────────────────────────────────────────────────────────


def _seed_email_integration() -> None:
    """Insert a placeholder Email/SMTP integration row if the tenant
    doesn't already have one. Idempotent."""
    bind = op.get_bind()
    exists = bind.execute(
        sa.text(
            "SELECT 1 FROM integrations WHERE kind='email' LIMIT 1"
        )
    ).scalar()
    if exists:
        return

    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, capabilities) "
            "VALUES "
            "(:id, 'email', 'smtp', :name, :slug, :desc, "
            " 0, 1, "
            " :public, NULL, NULL, NULL)"
        ),
        {
            "id": new_id,
            "name": "Email (SMTP)",
            "slug": "email-smtp-primary",
            "desc": (
                "默认邮件通道 — 欢迎信、离职提醒、转让接收邮件都通过这里发送。"
                "填写 SMTP host / port / 用户名 / 密码后启用。"
            ),
            "public": json.dumps(
                {
                    "host": "",
                    "port": 587,
                    "use_tls": True,
                    "from_address": "",
                    "from_name": "Helix IT 平台",
                },
                ensure_ascii=False,
            ),
        },
    )


def _seed_platform_settings() -> None:
    """Seed default notification-related settings."""
    bind = op.get_bind()
    defaults: list[tuple[str, dict | str, str]] = [
        ("company.display_name", "公司", "邮件与通知中使用的公司名。"),
        (
            "it.contact_email",
            "it@example.com",
            "新员工欢迎信中的 IT 联系邮箱。",
        ),
        (
            "portal.base_url",
            "http://localhost:3000",
            "通知链接的站点根地址(例:https://helix.example.com)。",
        ),
    ]
    for key, value, description in defaults:
        exists = bind.execute(
            sa.text("SELECT 1 FROM platform_settings WHERE `key`=:k"),
            {"k": key},
        ).scalar()
        if exists:
            continue
        bind.execute(
            sa.text(
                "INSERT INTO platform_settings (`key`, value, description) "
                "VALUES (:k, :v, :d)"
            ),
            {
                "k": key,
                "v": json.dumps(value, ensure_ascii=False),
                "d": description,
            },
        )