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

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

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'})
WHERE dead IS NULL;

CREATE OR REPLACE TEMP TABLE targets AS
SELECT m.id AS store_id, m.rating_count
FROM read_json_auto('/root/ultron/data/mahally/meta.jsonl', format='newline_delimited', ignore_errors=true) m
LEFT JOIN covered c ON c.store_id = m.id
WHERE c.store_id IS NULL AND coalesce(m.rating_count, 0) >= 50;

.print '=== TARGETED RUN: how much of the value is already in ==='
SELECT
  count(*) AS target_stores,
  count(*) FILTER (WHERE d.store_id IS NOT NULL) AS stores_fetched,
  sum(t.rating_count) AS target_reviews,
  sum(t.rating_count) FILTER (WHERE d.store_id IS NOT NULL) AS reviews_fetched,
  round(sum(t.rating_count) FILTER (WHERE d.store_id IS NOT NULL) * 100.0 / sum(t.rating_count), 1) AS pct_of_targeted_traction
FROM targets t LEFT JOIN done d ON d.store_id = t.store_id;

.print ''
.print '=== rows landed so far ==='
SELECT count(*) AS rows_total,
       count(*) FILTER (WHERE sold_quantity > 0) AS rows_with_sales,
       count(*) FILTER (WHERE cost_price > 0 AND cost_price < price) AS rows_with_real_cost
FROM read_json_auto('/root/ultron/data/mahally/targeted-details.jsonl', format='newline_delimited', ignore_errors=true,
  columns={id:'BIGINT', store_id:'BIGINT', price:'DOUBLE', cost_price:'DOUBLE', sold_quantity:'DOUBLE', dead:'BOOLEAN'});
