SET memory_limit='2500MB';
SET threads=2;
SET preserve_insertion_order=false;

CREATE OR REPLACE TEMP TABLE covered AS
SELECT DISTINCT store_id FROM read_json_auto('/root/ultron/data/mahally/details.jsonl',
  format='newline_delimited', ignore_errors=true,
  columns={id:'BIGINT', store_id:'BIGINT', dead:'BOOLEAN', sold_quantity:'DOUBLE'})
WHERE dead IS NULL;

CREATE OR REPLACE TEMP TABLE meta AS
SELECT id, created_at, rating_count, trust_score, city
FROM read_json_auto('/root/ultron/data/mahally/meta.jsonl', format='newline_delimited', ignore_errors=true);

CREATE OR REPLACE TEMP TABLE pcount AS
SELECT store_id, count(*) AS prods
FROM read_json_auto('/root/ultron/data/mahally/products.jsonl', format='newline_delimited', ignore_errors=true,
  columns={id:'BIGINT', store_id:'BIGINT'})
GROUP BY store_id;

.print '=== COVERAGE ==='
SELECT
  (SELECT count(*) FROM meta) AS stores_total,
  (SELECT count(*) FROM covered) AS stores_covered,
  round((SELECT count(*) FROM covered) * 100.0 / (SELECT count(*) FROM meta), 1) AS pct_covered;

.print ''
.print '=== COVERED vs NOT — are the remaining stores different? ==='
SELECT
  CASE WHEN c.store_id IS NULL THEN 'not scraped' ELSE 'scraped' END AS grp,
  count(*) AS stores,
  round(median(p.prods), 0) AS med_products,
  round(sum(p.prods) / 1e6, 2) AS total_products_M,
  round(median(m.rating_count), 0) AS med_rating_count,
  round(avg(CASE WHEN m.rating_count > 0 THEN 1 ELSE 0 END) * 100, 1) AS pct_with_any_review,
  round(median(m.trust_score), 1) AS med_trust,
  strftime(to_timestamp(median(m.created_at)), '%Y-%m') AS med_signup
FROM meta m
LEFT JOIN covered c ON c.store_id = m.id
LEFT JOIN pcount p ON p.store_id = m.id
GROUP BY 1 ORDER BY 1;

.print ''
.print '=== SIZE DISTRIBUTION of the unscraped remainder ==='
SELECT CASE WHEN p.prods IS NULL THEN '0 products'
            WHEN p.prods < 5 THEN '1-4'
            WHEN p.prods < 20 THEN '5-19'
            WHEN p.prods < 100 THEN '20-99'
            WHEN p.prods < 500 THEN '100-499'
            ELSE '500+' END AS size_band,
       count(*) AS stores, sum(p.prods) AS products
FROM meta m LEFT JOIN covered c ON c.store_id = m.id LEFT JOIN pcount p ON p.store_id = m.id
WHERE c.store_id IS NULL
GROUP BY 1 ORDER BY 2 DESC;
