"""Compare explicit pack counts in the public Amazon ESCI examples and products.

Pass the examples Parquet file followed by the products Parquet file. Requires DuckDB.
"""
import json
import sys

import duckdb


def analyze(examples, products):
    with duckdb.connect() as db:
        db.execute(r"""
            CREATE MACRO packs(s) AS list_distinct(list_transform(
                regexp_extract_all(lower(s), '\b([1-9][0-9]{0,2})[- ]+packs?\b', 1)
                || regexp_extract_all(lower(s), '\bpacks?\s+of\s+([1-9][0-9]{0,2})\b', 1),
                x -> CAST(x AS INTEGER)))
        """)
        assert db.execute("SELECT packs('12-pack') = [12], packs('pack of 3') = [3], array_length(packs('4oz')) = 0").fetchone()==(True,True,True)
        raw=db.execute("""
            WITH matching AS (
                SELECT query_id, product_id, product_locale, esci_label,
                       packs(query) AS query_counts
                FROM read_parquet(?)
                WHERE split='test' AND large_version=1 AND product_locale='us'
            ), queries AS (
                SELECT * FROM matching WHERE array_length(query_counts)=1
            ), catalog AS (
                SELECT product_id, product_locale, any_value(product_title) AS product_title, count(*) AS catalog_count
                FROM read_parquet(?)
                WHERE product_locale='us'
                GROUP BY product_id, product_locale
            ), joined AS (
                SELECT query_id, esci_label, query_counts[1] AS requested,
                       packs(coalesce(product_title,'')) AS title_counts, catalog_count
                FROM queries LEFT JOIN catalog USING(product_id, product_locale)
            )
            SELECT esci_label, count(*), count(DISTINCT query_id),
                   count(*) FILTER (WHERE catalog_count IS DISTINCT FROM 1),
                   count(*) FILTER (WHERE catalog_count=1 AND array_length(title_counts)=0),
                   count(*) FILTER (WHERE catalog_count=1 AND array_length(title_counts)>1),
                   count(*) FILTER (WHERE catalog_count=1 AND array_length(title_counts)=1 AND title_counts[1]=requested),
                   count(*) FILTER (WHERE catalog_count=1 AND array_length(title_counts)=1 AND title_counts[1]<>requested)
            FROM joined GROUP BY esci_label ORDER BY esci_label
        """, [str(examples),str(products)]).fetchall()
    fields=('pairs','queries','unresolved_product','silent_title','multiple_counts','explicit_agreement','explicit_disagreement')
    result={label:dict(zip(fields,values)) for label,*values in raw}
    assert set(result)=={'E','S','I','C'}
    assert all(row['pairs']==sum(row[k] for k in fields[2:]) for row in result.values())
    return result


if __name__=='__main__':
    print(json.dumps(analyze(*sys.argv[1:]),indent=2))
