"""Drop image URLs that Open Food Facts cannot actually serve.

OFF returns an image URL for records whose barcode it failed to parse; those
carry the literal path segment "/products/invalid/" and 404, which renders as a
broken-image icon in the product grid. The enricher now rejects them on the way
in -- this clears the ones already stored.

Revision ID: b8c9d0e1f2a3
Revises: a7b8c9d0e1f2
"""

from alembic import op

revision = "b8c9d0e1f2a3"
down_revision = "a7b8c9d0e1f2"
branch_labels = None
depends_on = None

# Anything under images.openfoodfacts.org whose path is not a numeric barcode.
_BAD = (
    "thumb_url LIKE 'https://images.openfoodfacts.org/%' "
    "AND thumb_url !~ '/images/products/([0-9]{3}/)+[0-9]+/|/images/products/[0-9]+/'"
)


def upgrade() -> None:
    op.execute(f"UPDATE products SET thumb_url = NULL WHERE {_BAD}")
    op.execute(
        "UPDATE products SET image_url = NULL WHERE "
        "image_url LIKE 'https://images.openfoodfacts.org/%' "
        "AND image_url !~ '/images/products/([0-9]{3}/)+[0-9]+/|/images/products/[0-9]+/'"
    )
    # An image_source with nothing left to credit is noise on the product page.
    op.execute(
        "UPDATE products SET image_source = NULL "
        "WHERE thumb_url IS NULL AND image_url IS NULL AND image_source IS NOT NULL"
    )


def downgrade() -> None:
    # The discarded URLs did not resolve; there is nothing worth restoring.
    pass