"""Aggregate Amazon ESCI product-search judgments. Requires DuckDB 1.4.4.

Run: python analysis_esci.py shopping_queries_dataset_examples.parquet
Source: https://github.com/amazon-science/esci-data/tree/main/shopping_queries_dataset
Only aggregate counts are printed. The Parquet file stays local.
"""
from hashlib import sha256
import json
from pathlib import Path
import sys

import duckdb


COLOR = r'(^|[^a-z])(black|white|red|blue|green|yellow|orange|pink|purple|brown|gray|grey)([^a-z]|$)'


def analyze(path):
    with duckdb.connect() as db:
        results = db.execute('''
            WITH by_query AS (
                SELECT query_id,
                       CASE WHEN regexp_matches(lower(query), ?) THEN 'Color term' ELSE 'No color term' END AS group_name,
                       count(*) AS pairs,
                       count(*) FILTER (WHERE esci_label='E') AS exact,
                       count(*) FILTER (WHERE esci_label='S') AS substitute,
                       count(*) FILTER (WHERE esci_label='C') AS complement,
                       count(*) FILTER (WHERE esci_label='I') AS irrelevant
                FROM read_parquet(?)
                WHERE product_locale='us' AND split='test' AND large_version=1
                GROUP BY ALL
            )
            SELECT group_name, count(*) AS queries, sum(pairs), sum(exact), sum(substitute),
                   sum(complement), sum(irrelevant), count(*) FILTER (WHERE exact=0),
                   count(*) FILTER (WHERE exact=0 AND substitute>0),
                   count(*) FILTER (WHERE exact>0 AND substitute>0)
            FROM by_query GROUP BY group_name ORDER BY group_name
        ''', [COLOR, str(path)]).fetchall()
    fields = ('queries','pairs','exact','substitute','complement','irrelevant','no_exact','substitute_only_fallback','exact_and_substitute')
    groups = {g: dict(zip(fields, counts)) for g, *counts in results}
    assert set(groups) == {'Color term', 'No color term'}
    assert all(g['pairs']==sum(g[k] for k in fields[2:6]) for g in groups.values())
    assert sum(g['queries'] for g in groups.values())==22458
    assert sum(g['pairs'] for g in groups.values())==425762
    return {'sha256':sha256(Path(path).read_bytes()).hexdigest(), 'groups':groups}


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