"""Replay candidate-set comparisons in the Amazon Shopping Queries Dataset.

Run: python esci_candidate_depth.py shopping_queries_dataset_examples.parquet
Requires DuckDB 1.4.4. Output is grouped data only.
"""
from hashlib import sha256
import json
from pathlib import Path
import sys

import duckdb


def analyze(path):
    path=Path(path)
    with duckdb.connect() as conn:
        rows=conn.execute('''
            WITH per_query AS (
              SELECT product_locale AS locale, query_id, count(*) AS candidates,
                     count(*) FILTER (WHERE esci_label='E') AS exact,
                     count(*) FILTER (WHERE esci_label='S') AS substitutes
              FROM read_parquet(?) WHERE large_version=1 AND split='test'
              GROUP BY ALL
            )
            SELECT locale, CASE WHEN candidates<=10 THEN '1–10' WHEN candidates<=20 THEN '11–20' ELSE '21+' END AS depth,
                   count(*) AS queries, sum(candidates), sum(exact), sum(substitutes),
                   count(*) FILTER (WHERE exact>0 AND substitutes>0),
                   round(avg(100.0*exact/candidates),5)
            FROM per_query GROUP BY GROUPING SETS ((locale,depth),(locale)) ORDER BY locale,depth
        ''',[str(path)]).fetchall()
    keys=('queries','candidates','exact','substitutes','both','macro_exact_pct')
    data={locale+' | '+(depth or 'all'):dict(zip(keys,values)) for locale,depth,*values in rows}
    assert data['us | all']['queries']==22458 and data['us | all']['candidates']==425762
    return {'source_sha256':sha256(path.read_bytes()).hexdigest(),'groups':data}


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