schema.sql

--- TODO ADD a distriubuted function
begin;

SET LOCAL citus.multi_shard_modify_mode TO 'sequential';

create table countries(
  id serial primary key
  , name text
  , code varchar(2) collate "C" unique
);
insert into countries(name, code) select 'country-'||i, i::text from generate_series(10,99) i;
select create_reference_table('countries');

create table orgs (
    id bigserial primary key
  , name text
  , created_at timestamptz default now()
);
select create_distributed_table('orgs', 'id');

create table users (
    id bigserial
  , org_id bigint references orgs(id)
  , name text
  , created_at timestamptz default now()
  , country_id int -- references countries(id)
  , score bigint generated always as (id + country_id) stored
  , primary key (org_id, id)
);
select create_distributed_table('users', 'org_id');
alter table users add constraint fk_user_country foreign key (country_id) references countries(id);
 
create table orders (
    id bigserial
  , org_id bigint references orgs(id)
  , user_id bigint
  , price int
  , info jsonb
  , primary key (org_id, id)
  , foreign key (org_id, user_id) references users(org_id, id)
);
select create_distributed_table('orders', 'org_id'); 

create table events (
    id bigserial not null
  , user_id bigint not null
  , org_id bigint not null 
  , event_time timestamp not null default now()
  , event_type int not null default 0
  , payload jsonb
  , primary key (user_id, id)
);
create index event_time_idx on events using BRIN (event_time);
create index event_json_idx on events using gin(payload);
select create_distributed_table('events', 'user_id'); -- on purpose don't collocate on correctly on org_id


create table local_data(
  id bigserial primary key
  , val int default ( (random()*100)::int )
);


insert into orgs(id, name) select  i,'org-'||i from generate_series(1,1000) i;
insert into users(id, name, org_id, country_id) select i,'user-'||i, i%1000+1, (i%90)+1  from generate_series(1,100000) i;
insert into orders(id, org_id, user_id, price) select i, ((i%100000+1)%1000)+1 , i%100000+1, i/100 from generate_series(1,1000000) i;
insert into events(id, org_id, user_id, event_type) select i, ((i%100000+1)%1000)+1 , i%100000+1, i/100 from generate_series(1,1000000) i;
insert into local_data(id) select generate_series(1,1000);