"""software_external_holder: add holder_key dedup column (per-device seats).

The seat-reconciliation framework keyed external/anomaly holders by
(plan_id, email) — one row per email. That assumes a per-USER seat
(one billable seat == one person). Jamf Pro (MDM) bills per managed
DEVICE, and one employee may hold several devices, so collapsing on a
shared email would undercount seats and merge distinct devices into
one anomaly row.

Add a ``holder_key`` dedup column. Per-user vendors leave it == email
(backfilled here, and the recorder writes holder_key=email when the
caller passes no override), so their behaviour is byte-for-byte
unchanged. Per-device vendors (Jamf) set holder_key = device serial.
The uniqueness key moves from (plan_id, email) to (plan_id, holder_key);
``email`` becomes nullable (now display-only, no longer the join key).

Backward-compatible: the new unique index over the backfilled
holder_key == email is identical in extent to the old (plan_id, email)
index, so no existing row collides.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "345"
down_revision = "344"
branch_labels = None
depends_on = None

_TBL = "software_external_holder"
_OLD_UQ = "uq_software_external_holder_plan_email"
_NEW_UQ = "uq_software_external_holder_plan_holder_key"


def upgrade() -> None:
    # 1) Add the column nullable so the backfill can populate it.
    op.add_column(_TBL, sa.Column("holder_key", sa.String(255), nullable=True))
    # 2) Backfill: per-user vendors' dedup key is their (lowercased) email.
    op.execute(
        f"UPDATE {_TBL} SET holder_key = LOWER(email) "
        "WHERE holder_key IS NULL AND email IS NOT NULL"
    )
    # 3) Lock it down — holder_key is the join key, always present going forward.
    op.alter_column(
        _TBL, "holder_key",
        existing_type=sa.String(255), nullable=False,
    )
    # 4) email is now display-only; per-device rows may carry NULL.
    op.alter_column(
        _TBL, "email",
        existing_type=sa.String(255), nullable=True,
    )
    # 5) Swap the unique key (plan_id, email) -> (plan_id, holder_key).
    op.drop_index(_OLD_UQ, table_name=_TBL)
    op.create_index(_NEW_UQ, _TBL, ["plan_id", "holder_key"], unique=True)


def downgrade() -> None:
    op.drop_index(_NEW_UQ, table_name=_TBL)
    op.create_index(_OLD_UQ, _TBL, ["plan_id", "email"], unique=True)
    op.alter_column(
        _TBL, "email",
        existing_type=sa.String(255), nullable=False,
    )
    op.drop_column(_TBL, "holder_key")