"""Reproduce US ESCI search-length and model-token counts from the public Parquet files."""
import argparse
import json

import duckdb


MODEL_PATTERN=r'\b[a-z]{1,6}-?[0-9]{2,6}[a-z0-9-]{0,6}\b'


def length_counts(examples):
    with duckdb.connect() as con:
        items=con.execute(r"""
            WITH per_query AS (
                SELECT query_id,
                    CASE array_length(regexp_split_to_array(trim(query), '\s+'))
                         WHEN 1 THEN 'short' WHEN 2 THEN 'short'
                         WHEN 3 THEN 'middle' WHEN 4 THEN 'middle' ELSE 'long' END AS band,
                    count(*) AS pairs, count(*) FILTER(WHERE esci_label='E') AS exact,
                    count(*) FILTER(WHERE esci_label='S') AS substitute,
                    count(*) FILTER(WHERE esci_label='I') AS irrelevant
                FROM read_parquet(?)
                WHERE split='test' AND large_version=1 AND product_locale='us'
                GROUP BY ALL
            )
            SELECT band,count(*),sum(pairs),sum(exact),sum(substitute),sum(irrelevant),
                   count(*) FILTER(WHERE exact=0),count(*) FILTER(WHERE substitute>0),
                   count(*) FILTER(WHERE irrelevant>0)
            FROM per_query GROUP BY band ORDER BY band
        """,[str(examples)]).fetchall()
    fields=('queries','pairs','exact','substitute','irrelevant','no_exact','has_substitute','has_irrelevant')
    return {band:dict(zip(fields,counts)) for band,*counts in items}


def model_counts(examples, products):
    with duckdb.connect() as con:
        items=con.execute(r"""
            WITH query_records AS (
              SELECT query_id,product_id,product_locale,esci_label,
                     list_distinct(regexp_extract_all(lower(query),?)) AS query_tokens
              FROM read_parquet(?)
              WHERE split='test' AND large_version=1 AND product_locale='us'
            ), eligible AS (
              SELECT * FROM query_records WHERE array_length(query_tokens)=1
            ), catalog AS (
              SELECT product_id,product_locale,list_distinct(regexp_extract_all(lower(coalesce(product_title,'')),?)) AS title_tokens
              FROM read_parquet(?) WHERE product_locale='us'
            ), pairs AS (
              SELECT eligible.*,title_tokens FROM eligible JOIN catalog USING(product_id,product_locale)
            )
            SELECT esci_label,count(*),count(DISTINCT query_id),
                   count(*) FILTER(WHERE array_length(title_tokens)=0),
                   count(*) FILTER(WHERE array_length(title_tokens)>0 AND NOT list_contains(title_tokens,query_tokens[1])),
                   count(*) FILTER(WHERE list_contains(title_tokens,query_tokens[1]))
            FROM pairs GROUP BY esci_label ORDER BY esci_label
        """,[MODEL_PATTERN,str(examples),MODEL_PATTERN,str(products)]).fetchall()
    fields=('pairs','queries','no_token','different_token','query_token')
    return {label:dict(zip(fields,counts)) for label,*counts in items}


if __name__=='__main__':
    parser=argparse.ArgumentParser(description=__doc__)
    parser.add_argument('examples')
    parser.add_argument('products',nargs='?')
    args=parser.parse_args()
    result={'query_length':length_counts(args.examples)}
    if args.products:
        result['model_token']=model_counts(args.examples,args.products)
    print(json.dumps(result,indent=2))
