"""Aggregate color-field coverage for US test candidates in Amazon ESCI.

Requires DuckDB. Supply the public examples and products Parquet files, in that order.
The query uses only product_id, product_locale and product_color in the product file.
"""
import json
import sys

import duckdb


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


def analyze(examples, products):
    with duckdb.connect() as db:
        query = '''
            WITH examples AS (
                SELECT query_id, product_id, product_locale, esci_label
                FROM read_parquet(?)
                WHERE split='test' AND large_version=1 AND product_locale='us'
                    AND regexp_matches(lower(query), ?)
            ), product_keys AS (
                SELECT product_id, product_locale,
                       count(*) AS matching_rows,
                       count(*) FILTER (WHERE product_color IS NOT NULL AND trim(product_color)<>'') AS has_color
                FROM read_parquet(?)
                WHERE product_locale='us'
                GROUP BY product_id, product_locale
            )
            SELECT esci_label, count(*) AS pairs,
                count(*) FILTER (WHERE matching_rows IS NULL) AS missing_product,
                count(*) FILTER (WHERE matching_rows>1) AS ambiguous_product,
                count(*) FILTER (WHERE matching_rows=1 AND has_color=0) AS no_color,
                count(*) FILTER (WHERE matching_rows=1 AND has_color=1) AS has_color,
                count(DISTINCT query_id) AS queries,
                count(DISTINCT product_id) AS products
            FROM examples LEFT JOIN product_keys USING (product_id, product_locale)
            GROUP BY esci_label ORDER BY esci_label
        '''
        raw = db.execute(query, [str(examples), COLOR, str(products)]).fetchall()
    labels = ('pairs','missing_product','ambiguous_product','no_color','has_color','queries','products')
    result = {label:dict(zip(labels, values)) for label,*values in raw}
    assert set(result)=={'E','S','C','I'}
    assert sum(r['pairs'] for r in result.values())==25466
    for row in result.values():
        assert row['pairs']==sum(row[key] for key in labels[1:5])
    return result


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