"""Track skipped no-price rows, and remove zero-priced data already stored

A retailer lists gift-with-purchase items and unavailable lines at zero. Those
were being ingested as real prices and sorted to the top of "cheapest", which
made the comparison look broken. The collector now skips them; this removes the
ones already written, along with any product left with no prices at all.

Revision ID: d4e5f6a7b8c9
Revises: c3d4e5f6a7b8
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "d4e5f6a7b8c9"
down_revision: str | None = "c3d4e5f6a7b8"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column(
        "collection_runs",
        sa.Column("skipped_no_price", sa.Integer(), nullable=False, server_default="0"),
    )
    op.execute("DELETE FROM price_observations WHERE price <= 0")
    op.execute(
        "DELETE FROM listings WHERE id NOT IN (SELECT DISTINCT listing_id FROM price_observations)"
    )
    op.execute(
        "DELETE FROM awards WHERE product_id NOT IN (SELECT DISTINCT product_id FROM listings)"
    )
    op.execute(
        "DELETE FROM products WHERE id NOT IN (SELECT DISTINCT product_id FROM listings)"
    )


def downgrade() -> None:
    op.drop_column("collection_runs", "skipped_no_price")