-- The corpus lab: typified application archetypes on one small database (multi-tenant events with dimensions and set membership, orders, sensor readings, web
-- requests, a ledger, attribute rows, graph edges, date partitions, a large dimension). Sizes follow :n_events (default 20000: about 60 MB, seconds to load); the data is
-- seeded, so the same statement meets the same rows in every run (test/corpus/README.md).
\set ON_ERROR_STOP on
\if :{?n_events} \else \set n_events 20000 \endif
\if :{?n_entities} \else \set n_entities 2000 \endif
set client_min_messages = warning;
select setseed(0.42);
select :n_events / 4 as n_orders, :n_events / 2 as n_ledger, :n_events * 3 / 4 as n_web, :n_events * 2 / 5 as n_edges, :n_events / 500 + 1 as n_reads, 50000 as n_big \gset
set sift.col_auto_merge = off;
drop table if exists events, dim_a, kinds, dim_b, orders, order_items, products, customers, readings, weblog, ledger, eav, edges, events_p, lookup_big, tenants, spans, wide cascade;

create table tenants (id int primary key, plan text);
create table dim_a (id int primary key, tenant_id int not null, type text not null, region text not null);
create table kinds (id int primary key, name text, enabled boolean);
create table dim_b (set_id int not null, dim_a_id int not null, deleted_at timestamptz);
create index dim_b_key on dim_b (set_id, dim_a_id);

create table events (
  id bigint generated by default as identity primary key,
  tenant_id int not null, dim_a_id int not null, kind_id int not null,
  status text not null, region text not null,
  amount float8, qty int, score int,
  created_at timestamptz not null, d date not null,
  note text, label text not null, attrs jsonb, tags int[], payload text);

insert into tenants select g, (array['free','pro','team'])[1 + g % 3] from generate_series(1, 50) g;
insert into dim_a select g, 1 + g % 50, 'type_' || (g % 6), 'region_' || (g % 12) from generate_series(1, :n_entities) g;
insert into kinds select g, 'kind' || g, g % 11 <> 0 from generate_series(1, 40) g;
insert into dim_b select 1, g, null from generate_series(1, 11) g;
insert into dim_b select 2, g, null from generate_series(100, 174) g;
insert into dim_b select 3, g, null from generate_series(250, 252) g;
insert into dim_b select 4, g, null from generate_series(20, 38) g;
insert into dim_b select 5, g, null from generate_series(1, 1000) g;
insert into dim_b select 1, 99, now() from generate_series(1, 1) g;

-- dim_a_id is Zipf-like (a few busy entities); rows are inserted in random order (scattered heap)
insert into events (tenant_id, dim_a_id, kind_id, status, region, amount, qty, score, created_at, d, note, label, attrs, tags, payload)
select e.tenant_id, e.id, 1 + (e.id * 7 + g) % 40,
       (array['new','open','paid','shipped','closed'])[1 + (random() * 4)::int],
       e.region,
       case when random() < .05 then null else round((random() * 1000)::numeric, 2)::float8 end,
       1 + (random() * 9)::int, (random() * 100)::int,
       ts, ts::date,
       case when random() < .4 then 'note ' || (random() * 1000)::int || case when random() < .1 then ' urgent' else '' end end,
       'label_' || (floor(power(random(), 2) * 50000))::int,
       case when random() < .8 then jsonb_build_object('a', (random() * 100)::int, 'b', (array['x','y','z'])[1 + (random() * 2)::int], 'flag', random() < .3) end,
       case when random() < .6 then array[(random() * 20)::int, (random() * 20)::int] end,
       repeat('p', 60)
from (select 1 + floor(power(random(), 2.5) * :n_entities)::int as eid, g, timestamptz '2024-01-01' + random() * interval '1000 days' as ts
      from generate_series(1, :n_events) g) s
join dim_a e on e.id = s.eid
order by random();

create index events_entity_d on events (dim_a_id, d);
create index events_d on events (d);
create index events_tenant_created on events (tenant_id, created_at);
create index events_label on events (label);
create index events_status on events (status);
create index events_attrs on events using gin (attrs);
create index events_tags on events using gin (tags);
create index events_note_trgm on events (note text_pattern_ops);
create index events_col on events using sift_col (id, tenant_id, dim_a_id, kind_id, status, region, amount, qty, score, created_at, d, note, label, attrs, tags)
  with (key_cut = dim_a_id, paths = 'attrs.a float8, attrs.b astext, attrs.flag astext');

-- e-commerce
create table customers (id int primary key, country text not null);
create table products (id int primary key, category text not null, price float8 not null);
create table orders (id int primary key, customer_id int not null, created_at timestamptz not null, status text not null, total float8 not null);
create table order_items (id bigint generated by default as identity primary key, order_id int not null, foreign_id_b int not null, qty int not null, price float8 not null);
insert into customers select g, (array['us','br','de','fr','jp','in','uk'])[1 + g % 7] from generate_series(1, 20000) g;
insert into products select g, 'cat_' || (g % 40), round((1 + random() * 200)::numeric, 2)::float8 from generate_series(1, 5000) g;
insert into orders select g, 1 + floor(power(random(), 2) * 20000)::int, timestamptz '2025-01-01' + random() * interval '600 days',
       (array['new','paid','shipped','returned'])[1 + (random() * 3)::int], 0 from generate_series(1, :n_orders) g;
insert into order_items (order_id, foreign_id_b, qty, price)
select o.id, 1 + floor(power(random(), 1.7) * 5000)::int, 1 + (random() * 4)::int, round((1 + random() * 200)::numeric, 2)::float8
from orders o, generate_series(1, 3) i where random() < 1.0;
update orders o set total = s.t from (select order_id, sum(qty * price) t from order_items group by 1) s where s.order_id = o.id;
create index orders_customer on orders (customer_id, created_at);
create index orders_created on orders (created_at);
create index items_order on order_items (order_id);
create index items_product on order_items (foreign_id_b);
create index orders_col on orders using sift_col (id, customer_id, created_at, status, total);
create index items_col on order_items using sift_col (id, order_id, foreign_id_b, qty, price);

-- IoT
create table readings (sensor_id int not null, ts timestamptz not null, value float8, quality int);
insert into readings select s, timestamptz '2026-01-01' + (g * interval '10 minutes'), 20 + 10 * sin(g / 50.0) + random() * 3, (random() * 3)::int
from generate_series(1, 500) s, generate_series(1, :n_reads) g order by random();
create index readings_sensor_ts on readings (sensor_id, ts);
create index readings_ts on readings (ts);
create index readings_col on readings using sift_col (sensor_id, ts, value, quality);

-- web analytics
create table weblog (id bigint generated by default as identity primary key, path text not null, status int not null, ts timestamptz not null, bytes int, ua text, session_id int);
insert into weblog (path, status, ts, bytes, ua, session_id)
select '/p/' || floor(power(random(), 3) * 3000)::int || case when random() < .1 then '/edit' else '' end,
       (array[200,200,200,200,200,301,404,500])[1 + (random() * 7)::int], timestamptz '2026-03-01' + random() * interval '60 days',
       (random() * 50000)::int, (array['ua_a','ua_b','ua_c','ua_d'])[1 + (random() * 3)::int], 1 + (random() * 40000)::int
from generate_series(1, :n_web);
create index weblog_ts on weblog (ts);
create index weblog_path on weblog (path text_pattern_ops);
create index weblog_session on weblog (session_id, ts);
create index weblog_col on weblog using sift_col (id, path, status, ts, bytes, ua, session_id);

-- ledger
create table ledger (id bigint generated by default as identity primary key, account_id int not null, posted_at date not null, amount numeric(12,2) not null, kind text not null);
insert into ledger (account_id, posted_at, amount, kind)
select 1 + floor(power(random(), 2) * 5000)::int, date '2024-01-01' + (random() * 900)::int, round(((random() - .45) * 2000)::numeric, 2), (array['credit','debit','fee','refund'])[1 + (random() * 3)::int]
from generate_series(1, :n_ledger);
create index ledger_acct on ledger (account_id, posted_at);
create index ledger_col on ledger using sift_col (id, account_id, posted_at, amount, kind);

-- EAV
create table eav (dim_a_id int not null, attr text not null, value text);
insert into eav select 1 + (g % 30000), 'attr_' || (g % 13), 'v' || (random() * 50)::int from generate_series(1, :n_web) g;
create index eav_key on eav (dim_a_id, attr);
create index eav_attr_value on eav (attr, value);
create index eav_col on eav using sift_col (dim_a_id, attr, value);

-- graph adjacency
create table edges (src int not null, dst int not null);
insert into edges select 1 + floor(power(random(), 2) * 50000)::int, 1 + (random() * 50000)::int from generate_series(1, :n_edges);
create index edges_src on edges (src);
create index edges_dst on edges (dst);
create index edges_col on edges using sift_col (src, dst);

-- declarative partitions by date
create table events_p (id bigint not null, dim_a_id int not null, d date not null, amount float8, status text) partition by range (d);
create table events_p_2024 partition of events_p for values from ('2024-01-01') to ('2025-01-01');
create table events_p_2025 partition of events_p for values from ('2025-01-01') to ('2026-01-01');
create table events_p_2026 partition of events_p for values from ('2026-01-01') to ('2027-01-01');
insert into events_p select id, dim_a_id, d, amount, status from events where id % 2 = 0;
create index events_p_entity on events_p (dim_a_id, d);
create index events_p_col on events_p using sift_col (id, dim_a_id, d, amount, status);

-- a large dimension (50,000 rows, over the 10,000 of the key-set rewrites), joined by key sets
create table lookup_big (id int primary key, dim_a_id int not null, grp int not null);
insert into lookup_big select g, 1 + (g % :n_entities), g % 100 from generate_series(1, :n_big) g;
create index lookup_big_entity on lookup_big (dim_a_id);

-- time spans with range types (the range columns are not indexed, their plain bounds are) and a wide table of measures with soft-deleted rows
create table spans (id bigint generated by default as identity primary key, tenant_id int not null, dim_a_id int not null, starts date not null, ends date not null,
  during daterange not null, seen tstzrange, qty int, amount float8);
insert into spans (tenant_id, dim_a_id, starts, ends, during, seen, qty, amount)
select 1 + g % 50, 1 + (g * 7) % :n_entities, s, s + len, daterange(s, s + len), case when g % 5 <> 0 then tstzrange(s::timestamptz, (s + len)::timestamptz + interval '6 hours') end,
       1 + (random() * 9)::int, case when random() < .05 then null else round((random() * 500)::numeric, 2)::float8 end
from (select g, date '2024-01-01' + (random() * 900)::int as s, 1 + (random() * 30)::int as len from generate_series(1, :n_events / 4) g) t
order by random();
create index spans_col on spans using sift_col (id, tenant_id, dim_a_id, starts, ends, during, seen, qty, amount);

create table wide (id bigint generated by default as identity primary key, tenant_id int not null, kind_id int not null, status text not null, created_at timestamptz not null, deleted_at timestamptz,
  m01 float8, m02 float8, m03 float8, m04 float8, m05 float8, m06 float8, m07 float8, m08 float8, m09 float8, m10 float8, m11 float8, m12 float8,
  n01 int, n02 int, n03 int, n04 int, n05 int, n06 int, n07 int, n08 int, n09 int, n10 int, n11 int, n12 int);
insert into wide (tenant_id, kind_id, status, created_at, deleted_at, m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12, n01, n02, n03, n04, n05, n06, n07, n08, n09, n10, n11, n12)
select 1 + g % 50, 1 + (g * 3) % 40, (array['new','open','paid','shipped','closed'])[1 + (random() * 4)::int], ts, case when random() < .08 then ts + interval '3 days' end,
       round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8,
       round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8,
       case when random() < .2 then null else round((random() * 100)::numeric, 2)::float8 end, case when random() < .2 then null else round((random() * 100)::numeric, 2)::float8 end,
       round((random() * 100)::numeric, 2)::float8, round((random() * 100)::numeric, 2)::float8,
       (random() * 100)::int, (random() * 100)::int, (random() * 100)::int, (random() * 100)::int, (random() * 100)::int, (random() * 100)::int,
       (random() * 100)::int, (random() * 100)::int, case when random() < .3 then null else (random() * 100)::int end, (random() * 100)::int, (random() * 100)::int, (random() * 100)::int
from (select g, timestamptz '2024-01-01' + random() * interval '900 days' as ts from generate_series(1, :n_events / 2) g) t
order by random();
create index wide_col on wide using sift_col (id, tenant_id, kind_id, status, created_at, deleted_at, m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12,
  n01, n02, n03, n04, n05, n06, n07, n08, n09, n10, n11, n12);

vacuum (analyze) tenants, dim_a, kinds, dim_b, events, customers, products, orders, order_items, readings, weblog, ledger, eav, edges, events_p, lookup_big, spans, wide;
select relname, reltuples::bigint, pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname in ('events','orders','order_items','readings','weblog','ledger','eav','edges','events_p_2025','lookup_big') order by 1;
select indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid)) from pg_index where indexrelid::regclass::text like '%\_col' or indexrelid::regclass::text like '%\_col%' order by 1;