"""Polish the seeded scope-request templates: highlight money + drop
duplicated URL line on Feishu cards.

Revision ID: 104
Revises: 103
Create Date: 2026-05-02

Three things change for the cloud scope-request notifications seeded
in migration 071:

* ``${budget:,.2f}`` becomes ``[r]${budget:,.2f}[/r]`` so the amount
  renders red+bold on the Feishu card and bold in email. The
  ``[r]`` markup convention is interpreted by
  ``app.notifications.service._apply_inline_markup`` per channel.

* The inline ``"请到 Helix 审批: {portal_url}"`` line gets replaced
  with the channel-aware ``{cta_line}`` placeholder. On Feishu cards
  the placeholder collapses to empty (the action button already
  carries the URL — duplicating it as text was noise the user
  flagged), and on email it expands to the same wording so the
  email body remains clickable.

* The ``cloud.scope_request.manual_required`` template gets the
  ``[r]`` budget treatment too — it doesn't carry an inline URL line
  (body says "go to the cloud console") so the cta_line swap doesn't
  apply.

* ``cloud.scope_request.rejected`` — no money, no URL, untouched.

The migration is idempotent + edit-safe: it only rewrites rows whose
``body_text`` / ``body_text_en`` matches the migration-071 baseline
verbatim. Operators who customised templates via the admin UI keep
their custom text — the audit trail of seed-vs-custom stays clean.

Doc-update rule (CLAUDE.md): PLATFORM.md + ROADMAP.md updated in the
same PR. cloud-lifecycle-ops.md untouched (this is a notification UX
fix, not a cloud lifecycle change).
"""
from __future__ import annotations

import json

import sqlalchemy as sa

from alembic import op


revision = "104"
down_revision = "103"
branch_labels = None
depends_on = None


# Pairs of (key, field, before, after). Matching is exact-string so the
# migration leaves admin-customised rows alone — no fuzzy substring
# replace inside an arbitrary body_text.
_REWRITES: list[tuple[str, str, str, str]] = [
    # ── cloud.scope_request.pending ─────────────────────────────
    (
        "cloud.scope_request.pending",
        "body_text",
        (
            "申请人: {requester_name} ({requester_email})\n"
            "云厂商: {provider}\n"
            "名字: {scope_name}\n"
            "说明: {description}\n"
            "月度预算: ${budget:,.2f}\n\n"
            "请到 Helix 审批: {portal_url}"
        ),
        (
            "申请人: {requester_name} ({requester_email})\n"
            "云厂商: {provider}\n"
            "名字: {scope_name}\n"
            "说明: {description}\n"
            "月度预算: [r]${budget:,.2f}[/r]\n\n"
            "{cta_line}"
        ),
    ),
    (
        "cloud.scope_request.pending",
        "body_text_en",
        (
            "Requester: {requester_name} ({requester_email})\n"
            "Cloud: {provider}\n"
            "Name: {scope_name}\n"
            "Description: {description}\n"
            "Monthly Budget: ${budget:,.2f}\n\n"
            "Please approve in Helix: {portal_url}"
        ),
        (
            "Requester: {requester_name} ({requester_email})\n"
            "Cloud: {provider}\n"
            "Name: {scope_name}\n"
            "Description: {description}\n"
            "Monthly Budget: [r]${budget:,.2f}[/r]\n\n"
            "{cta_line}"
        ),
    ),
    # ── cloud.scope_request.manual_required ─────────────────────
    (
        "cloud.scope_request.manual_required",
        "body_text",
        (
            "申请已批准 (审批人: {approver_name}).\n"
            "云厂商: {provider}\n"
            "名字: {scope_name}\n"
            "月度预算: ${budget:,.2f}\n\n"
            "Helix 当前未实现 {provider} 的自动子账号创建.\n"
            "请到云控制台手动创建子账号 / 订阅, 然后到 Helix 填入云端 ID 完成关联."
        ),
        (
            "申请已批准 (审批人: {approver_name}).\n"
            "云厂商: {provider}\n"
            "名字: {scope_name}\n"
            "月度预算: [r]${budget:,.2f}[/r]\n\n"
            "Helix 当前未实现 {provider} 的自动子账号创建.\n"
            "请到云控制台手动创建子账号 / 订阅, 然后到 Helix 填入云端 ID 完成关联."
        ),
    ),
    (
        "cloud.scope_request.manual_required",
        "body_text_en",
        (
            "Request approved by {approver_name}.\n"
            "Cloud: {provider}\n"
            "Name: {scope_name}\n"
            "Monthly Budget: ${budget:,.2f}\n\n"
            "Helix does not yet auto-provision {provider} sub-accounts.\n"
            "Please create the sub-account / subscription in the cloud "
            "console manually, then paste the cloud-side ID in Helix to "
            "close the loop."
        ),
        (
            "Request approved by {approver_name}.\n"
            "Cloud: {provider}\n"
            "Name: {scope_name}\n"
            "Monthly Budget: [r]${budget:,.2f}[/r]\n\n"
            "Helix does not yet auto-provision {provider} sub-accounts.\n"
            "Please create the sub-account / subscription in the cloud "
            "console manually, then paste the cloud-side ID in Helix to "
            "close the loop."
        ),
    ),
    # ── cloud.scope_request.provisioned ─────────────────────────
    (
        "cloud.scope_request.provisioned",
        "body_text",
        (
            "云子账号已创建并分配给你为 owner.\n"
            "云厂商: {provider}\n"
            "账号 ID: {external_id}\n"
            "月度预算: ${budget:,.2f}\n"
            "默认告警: 日均 25%、月度 50/80/100% 已配置.\n\n"
            "请到 Helix 查看: {portal_url}"
        ),
        (
            "云子账号已创建并分配给你为 owner.\n"
            "云厂商: {provider}\n"
            "账号 ID: {external_id}\n"
            "月度预算: [r]${budget:,.2f}[/r]\n"
            "默认告警: 日均 25%、月度 50/80/100% 已配置.\n\n"
            "{cta_line}"
        ),
    ),
    (
        "cloud.scope_request.provisioned",
        "body_text_en",
        (
            "Your cloud sub-account is ready and you are the owner.\n"
            "Cloud: {provider}\n"
            "Account ID: {external_id}\n"
            "Monthly Budget: ${budget:,.2f}\n"
            "Default alerts: daily 25%, monthly 50/80/100% configured.\n\n"
            "View in Helix: {portal_url}"
        ),
        (
            "Your cloud sub-account is ready and you are the owner.\n"
            "Cloud: {provider}\n"
            "Account ID: {external_id}\n"
            "Monthly Budget: [r]${budget:,.2f}[/r]\n"
            "Default alerts: daily 25%, monthly 50/80/100% configured.\n\n"
            "{cta_line}"
        ),
    ),
]


def _rewrite_template_field(
    bind, key: str, field: str, before: str, after: str,
) -> bool:
    """Rewrite ONE field of ONE template row IF its current value
    matches ``before`` exactly. Returns True when a row was updated.
    Operates on the JSON column ``platform_settings.value``.
    """
    setting_key = f"email_template.{key}"
    row = bind.execute(
        sa.text(
            "SELECT value FROM platform_settings WHERE `key` = :k LIMIT 1"
        ),
        {"k": setting_key},
    ).fetchone()
    if row is None:
        return False
    raw = row[0]
    if isinstance(raw, str):
        try:
            payload = json.loads(raw)
        except json.JSONDecodeError:
            return False
    elif isinstance(raw, dict):
        payload = raw
    else:
        return False
    if payload.get(field) != before:
        # Either someone customised it or the migration already ran
        # — leave alone so we don't trample operator edits.
        return False
    payload[field] = after
    bind.execute(
        sa.text(
            "UPDATE platform_settings SET value = :v, updated_at = NOW() "
            "WHERE `key` = :k"
        ),
        {"k": setting_key, "v": json.dumps(payload, ensure_ascii=False)},
    )
    return True


def upgrade() -> None:
    bind = op.get_bind()
    for key, field, before, after in _REWRITES:
        _rewrite_template_field(bind, key, field, before, after)


def downgrade() -> None:
    bind = op.get_bind()
    for key, field, before, after in _REWRITES:
        # Swap the args so we rewrite the post-upgrade text back to
        # the pre-upgrade text. Idempotent + edit-safe — same guard
        # as upgrade().
        _rewrite_template_field(bind, key, field, after, before)