"""Retire the unverified pingcap-us Confluence subscription to "pending
contract" state, and stop counting raw product_access as paid seats.

Revision ID: 227
Revises: 226
Create Date: 2026-05-31

Background (prod audit 2026-05-31, cross-checked against the Atlassian
renewal quote "Pingcap续费AtlassianCloud产品报价 2026-05-25"):

* The tidb Confluence subscription is **10 paid seats** (Cloud Premium,
  US$1,300/yr) per the contract — confirmed by the marketplace add-on tiers
  (Mosaic / Translations for Confluence both 10/10). But the Org-Admin
  ``product_access`` API reports ~100 active Confluence holders on
  ``tidb.atlassian.net``. Confluence grants broad access that does NOT all
  consume a paid license, so product_access overstates paid seats ~10x. The
  application now clamps each plan's holder count to its contracted
  ``max_seats`` (``billing_service._effective_seats``) and surfaces the
  excess as an ``under_licensed`` finding — NO data change needed for tidb
  (its ``max_seats`` is already 10).

* The ``Confluence (PingCAP US)`` catalog was seeded by migration 206 from
  GUESSED numbers (~30) with no contract on file. The user confirms
  ``pingcap-us.atlassian.net`` is a genuinely independent paid Atlassian
  org — but its contract (and therefore its real paid-seat count) is not
  available, so the ~28 ``product_access`` holders are NOT a validated paid
  count. Rather than keep fabricating a 30-seat subscription (which
  ``max``-collapsed the headline to a wrong number), we retire it to a
  "pending contract" state: deactivate the plan + connector, drop the stale
  seat-sync stat row, resolve its external-holder rows, and remark the
  catalog. ``get_jira_plans`` only loads ``status='active'`` plans, so this
  also stops the daily reconcile from re-deriving the bogus count.

When the pingcap-us contract is obtained: reactivate the plan/connector,
set ``max_seats`` to the contracted number, and (because it is a disjoint
*site* from tidb) move ``_stat_family_key`` to
``(product_key, frozenset(paid_sites))`` so the two independent Confluence
subscriptions SUM in the headline instead of ``max``-collapsing.

Idempotent: every step is a no-op if the catalog is missing or already in
the target state. ``downgrade`` reactivates the plan/connector (it does NOT
re-fabricate the deleted stat row — that came from a real sync and will be
re-created on the next reconcile if the plan is active).
"""
from __future__ import annotations

import logging

import sqlalchemy as sa

from alembic import op

logger = logging.getLogger(__name__)

revision = "227"
down_revision = "226"
branch_labels = None
depends_on = None

_US_CATALOG = "Confluence (PingCAP US)"
_PENDING_REMARK = (
    "【待录入合同 / pending contract】pingcap-us.atlassian.net 是独立付费 "
    "Atlassian org,但其合同坐席数尚未录入。Org-Admin product_access 的活跃"
    "访问者数(~28)不是付费坐席口径,故不计入「生效坐席」headline。拿到 "
    "pingcap-us 的 Atlassian 续费报价单后:重新激活本 plan/connector、按合同"
    "设置 max_seats、并把 _stat_family_key 改成 (product_key, 站点集合) 让"
    "两个独立 Confluence 订阅相加而非 max 塌缩。"
)


def upgrade() -> None:
    bind = op.get_bind()

    software_id = bind.execute(
        sa.text(
            "SELECT id FROM asset_software_catalog WHERE name = :n LIMIT 1"
        ),
        {"n": _US_CATALOG},
    ).scalar()
    if software_id is None:
        logger.info("227: %r catalog absent — nothing to retire", _US_CATALOG)
        return

    # 1. Deactivate the plan(s) so get_jira_plans (status='active') skips it.
    bind.execute(
        sa.text(
            "UPDATE software_license_plans SET status = 'inactive' "
            "WHERE software_id = :sid AND status = 'active'"
        ),
        {"sid": software_id},
    )

    # 2. Deactivate the connector so no provisioning/sync path uses it.
    bind.execute(
        sa.text(
            "UPDATE software_connectors SET is_active = 0 "
            "WHERE software_id = :sid AND is_active = 1"
        ),
        {"sid": software_id},
    )

    # 3. Drop the stale seat-sync stat row (the fabricated ~30 that
    #    max-collapsed the Confluence headline). Keyed by software_id.
    bind.execute(
        sa.text(
            "DELETE FROM software_seat_sync_stat WHERE software_id = :sid"
        ),
        {"sid": software_id},
    )

    # 4. Resolve the external-holder rows so they stop showing as open
    #    anomalies (they are real accounts on a real org, just not a
    #    validated paid subscription here — preserve the rows for history).
    bind.execute(
        sa.text(
            "UPDATE software_external_holder SET resolved = 1, "
            "resolved_at = COALESCE(resolved_at, NOW()) "
            "WHERE software_id = :sid AND resolved = 0"
        ),
        {"sid": software_id},
    )

    # 5. Remark the catalog so the pending state is documented in the UI.
    bind.execute(
        sa.text(
            "UPDATE asset_software_catalog SET remark = :r WHERE id = :sid"
        ),
        {"r": _PENDING_REMARK, "sid": software_id},
    )
    logger.info(
        "227: retired %r to pending-contract state (plan inactive, stat "
        "row dropped, holders resolved)", _US_CATALOG,
    )


def downgrade() -> None:
    """Reactivate the pingcap-us plan + connector. The deleted stat row is
    NOT re-fabricated — it will be re-created by the next reconcile once the
    plan is active again."""
    bind = op.get_bind()
    software_id = bind.execute(
        sa.text(
            "SELECT id FROM asset_software_catalog WHERE name = :n LIMIT 1"
        ),
        {"n": _US_CATALOG},
    ).scalar()
    if software_id is None:
        return
    bind.execute(
        sa.text(
            "UPDATE software_license_plans SET status = 'active' "
            "WHERE software_id = :sid AND status = 'inactive'"
        ),
        {"sid": software_id},
    )
    bind.execute(
        sa.text(
            "UPDATE software_connectors SET is_active = 1 "
            "WHERE software_id = :sid AND is_active = 0"
        ),
        {"sid": software_id},
    )