SET threads = 2;
SET memory_limit = '3GB';

CREATE TABLE ev AS
  SELECT *, CASE WHEN kind = 'commit' THEN regexp_extract(collection, '^([^.]+\.[^.]+)', 1) ELSE '_' || kind END AS ns
  FROM read_csv('__WORK__/rows.tsv', delim = '\t', header = false,
    columns = {seq: 'BIGINT', time_us: 'BIGINT', did: 'VARCHAR', kind: 'VARCHAR', op: 'VARCHAR', collection: 'VARCHAR', rkey: 'VARCHAR'},
    quote = '', escape = '', null_padding = true);

COPY (SELECT seq, time_us, did, kind, op, collection, rkey, ns FROM ev ORDER BY seq)
  TO '__WORK__/events' (FORMAT parquet, PARTITION_BY (ns), COMPRESSION zstd);

COPY (
  SELECT __IDX__ AS segment, ns, collection, kind, op,
    count(*) AS events, count(DISTINCT did) AS dids,
    min(seq) AS min_seq, max(seq) AS max_seq, min(time_us) AS min_time_us, max(time_us) AS max_time_us
  FROM ev GROUP BY ALL ORDER BY ns, collection, kind, op
) TO '__WORK__/agg.parquet' (FORMAT parquet, COMPRESSION zstd);