"""post_attachment: per-post sha uniqueness (empty-post flood fix)

Revision ID: 0043
Revises: 0042
Create Date: 2026-06-08

PostAttachment.sha256 was GLOBALLY unique, so a non-art file the creator attaches
to many posts (a standard pdf/zip/link-card) only ever got ONE row — on the first
post — leaving every later post a bare shell (no image, no attachment). The native
Patreon backfill of Anduo surfaced 1589 such shells (operator-flagged 2026-06-08).

Switch to PER-POST uniqueness: the on-disk blob stays sha-deduped, but each post
gets its own row. Replace the unique sha256 index with a plain lookup index plus
two partial uniques — (post_id, sha256) for real posts and (sha256) for the
NULL-post filesystem case (still one row per file there).

Existing data has ≤1 row per sha (the old global unique), so the new partial
uniques can't be violated on upgrade — no data backfill needed here. The bare-post
shells themselves are removed by the separate prune-empty-posts cleanup tool.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0043"
down_revision: Union[str, None] = "0042"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Drop the global unique index; recreate it as a plain (non-unique) lookup
    # index so sha-based reads keep their index (matches the model's index=True).
    op.drop_index("ix_post_attachment_sha256", table_name="post_attachment")
    op.create_index(
        "ix_post_attachment_sha256", "post_attachment", ["sha256"],
    )
    op.create_index(
        "uq_post_attachment_post_sha", "post_attachment",
        ["post_id", "sha256"], unique=True,
        postgresql_where=sa.text("post_id IS NOT NULL"),
    )
    op.create_index(
        "uq_post_attachment_null_post_sha", "post_attachment",
        ["sha256"], unique=True,
        postgresql_where=sa.text("post_id IS NULL"),
    )


def downgrade() -> None:
    op.drop_index(
        "uq_post_attachment_null_post_sha", table_name="post_attachment"
    )
    op.drop_index(
        "uq_post_attachment_post_sha", table_name="post_attachment"
    )
    op.drop_index("ix_post_attachment_sha256", table_name="post_attachment")
    op.create_index(
        "ix_post_attachment_sha256", "post_attachment", ["sha256"],
        unique=True,
    )