"""Unify products fragmented by missing sizes and restricted barcodes.

Avolta tile names often omit the size their URL slug states, so those listings
became size-less products that could not merge with their properly-sized twins
("Johnnie Walker Blue Label" at three airports sat apart from the 1L product
the rest of the world was on). And products ingested before the
restricted-circulation rule still carry GS1 02/2x codes as identity. Both are
now handled at ingest; this backfills:

1. Restricted-prefix gtins on products are cleared (not global identity).
2. Size-less products take the size their listings' URLs agree on.
3. Match keys are recomputed where the size changed.
4. Barcode-less twins sharing a key merge into the canonical product.

Revision ID: f2a3b4c5d6e7
Revises: e1f2a3b4c5d6
"""

from alembic import op
from sqlalchemy import text

revision = "f2a3b4c5d6e7"
down_revision = "e1f2a3b4c5d6"
branch_labels = None
depends_on = None


def upgrade() -> None:
    from app.services.normalize import match_key, parse_size_ml_from_url

    conn = op.get_bind()

    # 1. Restricted-circulation codes are not identity.
    conn.execute(
        text(
            "UPDATE products SET gtin = NULL "
            "WHERE gtin IS NOT NULL AND (gtin LIKE '02%' "
            "OR (length(gtin) = 13 AND gtin LIKE '2%'))"
        )
    )

    # 2 + 3. Sizes from listing URLs, where every listing of the product agrees.
    for pid, brand, name in conn.execute(
        text(
            "SELECT p.id, p.brand, p.name FROM products p "
            "WHERE p.size_ml IS NULL AND EXISTS "
            "(SELECT 1 FROM listings l WHERE l.product_id = p.id)"
        )
    ).all():
        sizes = {
            parse_size_ml_from_url(url)
            for (url,) in conn.execute(
                text("SELECT url FROM listings WHERE product_id = :p"), {"p": pid}
            ).all()
        }
        sizes.discard(None)
        if len(sizes) != 1:
            continue
        size = sizes.pop()
        conn.execute(
            text("UPDATE products SET size_ml = :s, match_key = :k WHERE id = :i"),
            {"s": size, "k": match_key(brand, name, size), "i": pid},
        )

    # 4. Merge barcode-less twins into their canonical product (same rules as
    #    the d0e1f2a3b4c5 repair: never merge two distinct barcodes).
    groups = conn.execute(
        text(
            "SELECT match_key, array_agg(id ORDER BY (gtin IS NOT NULL) DESC, id) "
            "FROM products GROUP BY match_key HAVING count(*) > 1"
        )
    ).all()
    for _, ids in groups:
        canonical, rest = ids[0], ids[1:]
        gtins = dict(
            conn.execute(
                text("SELECT id, gtin FROM products WHERE id = ANY(:ids)"), {"ids": ids}
            ).all()
        )
        mergeable = [i for i in rest if gtins.get(i) is None]
        if not mergeable:
            continue
        conn.execute(
            text("UPDATE listings SET product_id=:c WHERE product_id = ANY(:ids)"),
            {"c": canonical, "ids": mergeable},
        )
        conn.execute(
            text(
                "UPDATE awards a SET product_id=:c WHERE a.product_id = ANY(:ids) "
                "AND NOT EXISTS (SELECT 1 FROM awards b WHERE b.product_id=:c "
                "AND b.competition=a.competition AND b.year IS NOT DISTINCT FROM a.year)"
            ),
            {"c": canonical, "ids": mergeable},
        )
        conn.execute(
            text("DELETE FROM awards WHERE product_id = ANY(:ids)"), {"ids": mergeable}
        )
        conn.execute(
            text(
                "UPDATE products c SET thumb_url = d.thumb_url, image_url = d.image_url, "
                "image_source = d.image_source FROM products d "
                "WHERE c.id=:c AND c.thumb_url IS NULL AND d.id = ANY(:ids) "
                "AND d.thumb_url IS NOT NULL AND d.id = "
                "(SELECT min(id) FROM products WHERE id = ANY(:ids) AND thumb_url IS NOT NULL)"
            ),
            {"c": canonical, "ids": mergeable},
        )
        conn.execute(
            text("DELETE FROM products WHERE id = ANY(:ids)"), {"ids": mergeable}
        )


def downgrade() -> None:
    # The cleared codes were never valid identity; merges are the corrected state.
    pass