"""Backfill: clear packaging words stored as product categories

A retailer feed labels some products by packaging ("Bottle", "Gift Box") rather
than by category. The collector now drops those, but rows written before that
change still carry them, so they are cleared here rather than left to drift.

Revision ID: a1b2c3d4e5f6
Revises: fb59f2e88621
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "a1b2c3d4e5f6"
down_revision: str | None = "fb59f2e88621"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

PACKAGING_WORDS = ("Bottle", "Gift Box", "Gift Set", "Gift Pack", "Tin", "Carton", "Case", "Can", "Tube", "Miniature")


def upgrade() -> None:
    op.execute(
        sa.text("UPDATE products SET category = NULL WHERE lower(category) = ANY(:words)").bindparams(
            sa.bindparam("words", value=[w.lower() for w in PACKAGING_WORDS], type_=sa.ARRAY(sa.String))
        )
    )


def downgrade() -> None:
    # The original values are not recoverable, and were not meaningful.
    pass