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'})
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;

-- highest-traction stores first, so if we ever stop early we stopped on the least valuable rows
COPY (
  SELECT p.id, p.store_id
  FROM read_json_auto('/root/ultron/data/mahally/products.jsonl', format='newline_delimited', ignore_errors=true,
       columns={id:'BIGINT', store_id:'BIGINT'}) p
  JOIN targets t ON t.store_id = p.store_id
  ORDER BY t.rating_count DESC, p.store_id, p.id
) TO '/root/ultron/data/mahally/targeted-queue.jsonl' (FORMAT JSON, ARRAY false);

SELECT (SELECT count(*) FROM targets) AS target_stores,
       (SELECT count(*) FROM read_json_auto('/root/ultron/data/mahally/targeted-queue.jsonl', format='newline_delimited')) AS queued_products;
