"""Consolidate URL settings to ``app.frontend_base_url`` + re-enable outbox retry.

Revision ID: 157
Revises: 156
Create Date: 2026-05-13

Note: renumbered 156 → 157 during rebase. PR #273
(``156_offboarding_blocker_notes_multiline_cleanup.py``) landed on main
ahead of this branch and claimed 156; this PR is unrelated to that one
(different table, different concern) so chaining via ``down_revision``
is purely linear with no upstream dependency.

Why
---
Two parallel platform_settings keys were resolving the same concept
(public-facing frontend origin) and drifted out of sync on prod:

  - ``app.frontend_base_url``  →  ``https://helix.pingcap.net``  (correct)
  - ``portal.base_url``        →  ``http://localhost:3000``      (stale)

The Feishu card "Open in Helix" button rendered correctly because the
central ``NotificationService._resolve_absolute_url`` reads
``app.frontend_base_url``. Every email body / Feishu card body, however,
came from caller code that pre-pended ``portal.base_url`` directly into
``url=`` and ``context["portal_url"]`` — so users received messages with
``http://localhost:3000/...`` links even on prod.

The 7 caller files (offboarding escalation, offboarding service,
hr_lifecycle advance + hire_day, asset inventory, provisioning, zoom
seat coordinator) are now updated to read ``app.frontend_base_url`` for
that same prefix. The zoom coordinator additionally read a third key
``frontend.base_url`` (never seeded) and is now consolidated too. This
migration retires the ``portal.base_url`` row so future code that
reaches for it lands on a missing key (the centralized resolver returns
its config-missing sentinel) rather than the stale localhost value.

Also flips ``notification.outbox_retry`` from disabled → enabled in the
same migration, per owner direction (2026-05-13): the cron retries
failed Feishu / email deliveries from the outbox; with it disabled,
transient vendor outages produced permanent silent message loss.

Idempotent: both the DELETE and UPDATE are safe to re-run.

Downgrade
---------
Reinserts ``portal.base_url`` with an empty value (no domain leak on
re-seed; admins must set it themselves) and re-disables outbox_retry.
The code no longer reads ``portal.base_url`` after this PR, so the
reinsertion is purely a marker of the prior state, not a functional
revert. ``app.frontend_base_url`` is left untouched in both directions.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "157"
down_revision = "156"
branch_labels = None
depends_on = None


_OLD_SETTING_KEY = "portal.base_url"
_OUTBOX_JOB_NAME = "notification.outbox_retry"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n"
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "platform_settings"):
        bind.execute(
            sa.text("DELETE FROM platform_settings WHERE `key` = :k"),
            {"k": _OLD_SETTING_KEY},
        )
    if _table_exists(bind, "scheduled_jobs"):
        # Flip outbox retry on. Only update enabled — leave cadence /
        # config alone so any local overrides stick.
        bind.execute(
            sa.text(
                "UPDATE scheduled_jobs "
                "SET enabled = 1, updated_at = NOW() "
                "WHERE name = :n AND enabled = 0",
            ),
            {"n": _OUTBOX_JOB_NAME},
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "platform_settings"):
        # Re-seed as empty so a downgrade does not silently restore the
        # ``localhost:3000`` leak. Admins must set it deliberately if
        # they want to roll back to the parallel-key world.
        bind.execute(
            sa.text(
                "INSERT IGNORE INTO platform_settings "
                "(`key`, `value`, description, created_at, updated_at) "
                "VALUES (:k, JSON_QUOTE(''), :desc, NOW(), NOW())",
            ),
            {
                "k": _OLD_SETTING_KEY,
                "desc": (
                    "[DEPRECATED] Use app.frontend_base_url. Reinserted "
                    "by migration 157 downgrade — leave empty unless "
                    "intentionally reverting the consolidation PR."
                ),
            },
        )
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text(
                "UPDATE scheduled_jobs "
                "SET enabled = 0, updated_at = NOW() "
                "WHERE name = :n",
            ),
            {"n": _OUTBOX_JOB_NAME},
        )