"""Remove the orphan duplicate ``NexusCRM Seat`` plan left by 315 + 317.

Revision ID: 321
Revises: 320
Create Date: 2026-06-22

On the prod env that 317 targets, a pre-existing ``Nexuscrm`` catalog had
a ``Default`` plan holding the bulk-init seats. Migration 315's plan guard
matched on ``name='NexusCRM Seat'`` (which the ``Default`` plan did not
satisfy), so 315 **inserted a second, empty $0 plan** named
``NexusCRM Seat``. Migration 317 then renamed the original ``Default`` →
``NexusCRM Seat``. Result: two active plans on the catalog both named
``NexusCRM Seat`` — one real (the renamed Default, carries the seats), one
empty $0 phantom — which clutters the 软件费用 plan list and the
change-plan dropdown 317 exists to clean up. ``software_license_plans``
has no ``(software_id, name)`` unique key, so both persist.

This forward-only fix deletes the empty phantom. It is strictly safe: a
duplicate is removed ONLY when it has zero references across every table
that points at a plan (seat allocations / external holders / sync stat /
monthly bills + history / procurement order items), so the plan holding
the real seats is always the one kept. No-op on a fresh env (a single
``NexusCRM Seat`` plan) and idempotent on re-run.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "321"
down_revision = "320"
branch_labels = None
depends_on = None


# table → the column that references software_license_plans.id. Most use
# ``plan_id``; procurement_order_items names its FK ``target_license_plan_id``.
_REF_TABLES = {
    "software_seat_allocations": "plan_id",
    "software_external_holder": "plan_id",
    "software_seat_sync_stat": "plan_id",
    "software_monthly_bills": "plan_id",
    "software_monthly_bill_history": "plan_id",
    "procurement_order_items": "target_license_plan_id",
}


def _ref_count(bind, plan_id: str) -> int:
    total = 0
    for table, col in _REF_TABLES.items():
        total += (
            bind.execute(
                sa.text(f"SELECT COUNT(*) FROM {table} WHERE {col} = :p"),
                {"p": plan_id},
            ).scalar()
            or 0
        )
    return total


def upgrade() -> None:
    bind = op.get_bind()
    cat_id = bind.execute(
        sa.text(
            "SELECT id FROM asset_software_catalog "
            "WHERE LOWER(name) = 'nexuscrm' LIMIT 1"
        )
    ).scalar()
    if cat_id is None:
        return

    plan_ids = [
        row[0]
        for row in bind.execute(
            sa.text(
                "SELECT id FROM software_license_plans "
                "WHERE software_id = :cid AND name = 'NexusCRM Seat' "
                "AND status = 'active'"
            ),
            {"cid": cat_id},
        ).fetchall()
    ]
    if len(plan_ids) < 2:
        return  # nothing to dedupe

    refs = {pid: _ref_count(bind, pid) for pid in plan_ids}
    # Keep the plan with the most references (holds the real seats); on a
    # tie / all-zero, max() keeps the first deterministically.
    keeper = max(plan_ids, key=lambda p: refs[p])
    for pid in plan_ids:
        if pid == keeper:
            continue
        if refs[pid] == 0:
            bind.execute(
                sa.text("DELETE FROM software_license_plans WHERE id = :p"),
                {"p": pid},
            )


def downgrade() -> None:
    """No-op — re-creating a phantom empty plan serves no purpose."""