"""Remove products built from the dropped-decimal slug misreading.

The platform writes 1.5-litre slugs as "-15l"; we read fifteen litres, and
worse, each store's tile priced a DIFFERENT default size of the same family,
so these products mixed magnum and 75cl prices in one comparison (a fake $447
"saving" on Armand de Brignac headlined the home page). The collector now
emits each priced size as its own listing; these mixed rows are deleted and
the next crawl rebuilds them correctly. The 12-litre beer case survives: it
is a genuine 24-pack, parsed from its name, not a slug misread.

Revision ID: b4c5d6e7f8a9
Revises: a3b4c5d6e7f8
"""

from alembic import op
from sqlalchemy import text

revision = "b4c5d6e7f8a9"
down_revision = "a3b4c5d6e7f8"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()
    doomed = [
        row[0]
        for row in conn.execute(
            text(
                "SELECT DISTINCT p.id FROM products p JOIN listings l ON l.product_id = p.id "
                "WHERE p.size_ml >= 10000 AND l.url ~ '-[1-9][0-9]l$'"
            )
        ).all()
    ]
    if not doomed:
        return
    conn.execute(
        text(
            "DELETE FROM price_observations WHERE listing_id IN "
            "(SELECT id FROM listings WHERE product_id = ANY(:ids))"
        ),
        {"ids": doomed},
    )
    conn.execute(text("DELETE FROM listings WHERE product_id = ANY(:ids)"), {"ids": doomed})
    conn.execute(text("DELETE FROM awards WHERE product_id = ANY(:ids)"), {"ids": doomed})
    conn.execute(text("DELETE FROM products WHERE id = ANY(:ids)"), {"ids": doomed})


def downgrade() -> None:
    pass