"""provenance-race: dedupe + UNIQUE(image_record_id, post_id) on image_provenance

Revision ID: 0021
Revises: 0020
Create Date: 2026-05-26

Closes the race in Importer._apply_sidecar's existence-check + INSERT pattern.
Two workers writing for the same (image, post) pair both saw no existing row
and both inserted, leaving duplicates that then broke .scalar_one_or_none()
on every subsequent deep-scan rederive against those images
(MultipleResultsFound). Most plausibly seeded when the 5-min recovery sweep
re-enqueued a still-running long-import task and the second worker collided
with the first inside _apply_sidecar.

Migration steps:
  1. DELETE all but min(id) per (image_record_id, post_id) pair. Operator's
     DB had 2 affected pairs at write-time; harmless no-op if zero.
  2. Add UNIQUE constraint so the importer's new savepoint+IntegrityError
     recovery path can trip on collision and re-select, mirroring
     uq_source_artist_platform_url and uq_post_source_external_id.
"""
from typing import Sequence, Union

from alembic import op

revision: str = "0021"
down_revision: Union[str, None] = "0020"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute(
        """
        DELETE FROM image_provenance ip1
        USING image_provenance ip2
        WHERE ip1.image_record_id = ip2.image_record_id
          AND ip1.post_id = ip2.post_id
          AND ip1.id > ip2.id
        """
    )
    op.create_unique_constraint(
        "uq_image_provenance_image_post",
        "image_provenance",
        ["image_record_id", "post_id"],
    )


def downgrade() -> None:
    op.drop_constraint(
        "uq_image_provenance_image_post",
        "image_provenance",
        type_="unique",
    )