"""Merge duplicate dashboard widgets into the one that covers both (#5213)

Revision ID: 0032_retire_duplicate_widgets
Revises: 0031_ansible_run_origin
Create Date: 2026-10-07

Host Agent — Resources (host_resources) folds into Hosts — Overview
(hosts_overview), and UniFi — Devices (unifi_devices) into UniFi — Overview
(unifi_overview); widgets.RETIRED names the pairs. A dashboard that already
shows the surviving widget loses the retired placement; any other keeps its
cell, now showing the survivor. The old widget's own options don't apply to
the new one, so they are cleared.
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = "0032_retire_duplicate_widgets"
down_revision: Union[str, None] = "0031_ansible_run_origin"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

# Frozen here rather than imported: a migration keeps meaning what it meant.
_PAIRS = (("host_resources", "hosts_overview"), ("unifi_devices", "unifi_overview"))


def upgrade() -> None:
    for old, new in _PAIRS:
        op.execute(sa.text("""
            DELETE FROM dashboard_widgets d
            WHERE d.widget_key = :old
              AND EXISTS (SELECT 1 FROM dashboard_widgets k
                          WHERE k.dashboard_id = d.dashboard_id AND k.widget_key = :new)
        """).bindparams(old=old, new=new))
        # Two placements of the old widget on one dashboard: keep the first.
        op.execute(sa.text("""
            DELETE FROM dashboard_widgets d
            WHERE d.widget_key = :old
              AND EXISTS (SELECT 1 FROM dashboard_widgets k
                          WHERE k.dashboard_id = d.dashboard_id AND k.widget_key = :old
                            AND k.id < d.id)
        """).bindparams(old=old))
        op.execute(sa.text("""
            UPDATE dashboard_widgets SET widget_key = :new, config_json = NULL
            WHERE widget_key = :old
        """).bindparams(old=old, new=new))


def downgrade() -> None:
    # The merge is one-way: which survivor cells were once the retired widget
    # isn't recorded, and the retired widgets no longer exist to render.
    pass