SET memory_limit='2500MB';
SET threads=2;
SET preserve_insertion_order=false;
COPY (
  WITH d AS (
    SELECT * FROM read_json_auto('/root/ultron/data/mahally/details.jsonl', format='newline_delimited', ignore_errors=true)
    QUALIFY row_number() OVER (PARTITION BY id) = 1
  ),
  p AS (
    SELECT id, name AS pname, created_at, rating AS prating, rating_count
    FROM read_json_auto('/root/ultron/data/mahally/products.jsonl', format='newline_delimited', ignore_errors=true)
    QUALIFY row_number() OVER (PARTITION BY id) = 1
  ),
  s AS (
    SELECT id AS sid, main_activity_en AS activity
    FROM read_json_auto('/root/ultron/data/mahally/stores.jsonl', format='newline_delimited', ignore_errors=true)
  )
  SELECT d.id, d.store_id,
         coalesce(p.pname, d.name) AS name,
         d.price, d.cost_price, d.sold_quantity,
         d.domain,
         p.created_at, p.prating AS rating, p.rating_count,
         s.activity
  FROM d
  LEFT JOIN p ON d.id = p.id
  LEFT JOIN s ON d.store_id = s.sid
  WHERE d.price IS NOT NULL AND d.sold_quantity > 0
) TO '/root/ultron/data/mahally/rep2-joined.jsonl' (FORMAT JSON, ARRAY false);
