"""image_prediction table (DDL only — backfill runs as a background task)

Normalizes the per-image tagger predictions out of the JSON blob into a
queryable table (#768). This migration creates ONLY the table + indexes — it
is pure DDL and commits instantly, so web boots immediately.

The data backfill from the existing image_record.tagger_predictions JSON is
deliberately NOT done here. Doing it inline made the whole migration one
transaction over the ~100 GB TOAST: nothing committed until the very end, it
was invisible/unmonitorable mid-run, and an early MATERIALIZED-CTE form spilled
the full 100 GB to temp. Instead the backfill is the
backend.app.tasks.admin.backfill_image_predictions_task — batched by id window,
committed per chunk (visible progress + resumable), idempotent
(ON CONFLICT DO NOTHING). Trigger it from Settings → Maintenance once web is up.

The old image_record.tagger_predictions column is left in place (vestigial) and
dropped in a follow-up once the backfill + code cutover are verified — dropping
it needs an ACCESS EXCLUSIVE lock on the hot image_record table (the 0044 lock
class), so it's deferred to a quiesced-worker window.

Revision ID: 0045
Revises: 0044
Create Date: 2026-06-10

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0045"
down_revision: Union[str, None] = "0044"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "image_prediction",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "image_record_id", sa.Integer(),
            sa.ForeignKey("image_record.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("raw_name", sa.String(length=255), nullable=False),
        sa.Column("category", sa.String(length=64), nullable=False),
        sa.Column("score", sa.Float(), nullable=False),
        sa.UniqueConstraint(
            "image_record_id", "raw_name", name="image_raw_name",
        ),
    )
    op.create_index(
        "ix_image_prediction_image", "image_prediction", ["image_record_id"],
    )
    op.create_index(
        "ix_image_prediction_name_score", "image_prediction",
        ["raw_name", "score"],
    )
    # No data backfill here — see the module docstring. The one-time copy from
    # image_record.tagger_predictions runs as backfill_image_predictions_task
    # (batched, resumable, idempotent), kept out of this transaction so web boots
    # without waiting on a ~100 GB pass.


def downgrade() -> None:
    op.drop_index("ix_image_prediction_name_score", "image_prediction")
    op.drop_index("ix_image_prediction_image", "image_prediction")
    op.drop_table("image_prediction")