-- Make test schema for demonstrating how the postgres optimizer might improve
-- performance on joins with unions

-- Contains a record for each project (job-costing) code
-- Projects are arranged in a hierarchical structure (parent/child)
-- --------------------------------------------------------------
create table proj (
    proj_id		int4 primary key,
    title		varchar,
    par			int4 references proj on update cascade
);
create index i_proj_par on proj (par);

-- Contains a record for every 2 combinations of projects which are related
-- to each other in the hierarchical project tree 
-- (parent/child, ancestor/progenitor, etc.)
-- --------------------------------------------------------------
create table proj_rel (
    anst_id		int4 references proj on update cascade on delete cascade,	-- ancestor project number
    prog_id		int4 references proj on update cascade on delete cascade,	-- progenitor project number
    rel			int4,								-- 0=self, 1=child, 2=grandchild, etc.
    primary key (anst_id, prog_id)
);

-- Contains a record for each account number and an optional alpha code to identify a sub-ledger
-- --------------------------------------------------------------
create table acct (
    acct_id		int4 primary key,	-- account number
    title		varchar,		-- name of the account
    code		varchar			-- alpha code for the account
);
create index i_acct_code on acct (code);

-- Each sub-ledger contains transactions unique to a certain part of the business
-- In addiiton to the standard fields, they all share in common, each sub-ledger
-- contains additional fields that are unique to it (so they can not all be
-- stored in a single table).  In our actual implementation, these sub-ledgers
-- are actually implemented as views joining even lower level tables.
-- --------------------------------------------------------------
create table subledg_A (
    rid			int4 primary key,		-- record ID
    amount		numeric(14,2),
    proj		int4 references proj on update cascade on delete cascade,
    unique_A		varchar				-- some other data
);
create index i_subledg_A_proj on subledg_A (proj);
-- --------------------------------------------------------------
create table subledg_B (
    rid			int4 primary key,		-- record ID
    amount		numeric(14,2),
    proj		int4 references proj on update cascade on delete cascade,
    unique_B		varchar				-- some other data
);
create index i_subledg_B_proj on subledg_B (proj);
-- --------------------------------------------------------------
create table subledg_C (
    rid			int4 primary key,		-- record ID
    amount		numeric(14,2),
    proj		int4 references proj on update cascade on delete cascade,
    unique_C		varchar				-- some other data
);
create index i_subledg_C_proj on subledg_C (proj);

-- These views allow a standard account code to presented in the appropriate ledgers
-- --------------------------------------------------------------
create view subview_A as select
   'AP ' || rid as trans_id,
    l.amount, l.proj,
    a.acct_id as acct
    from	subledg_A	l
        join	acct		a on a.code = 'ap';
-- --------------------------------------------------------------
create view subview_B as select
   'AR ' || rid as trans_id,
    l.amount, l.proj,
    a.acct_id as acct
    from	subledg_B	l
        join	acct		a on a.code = 'ar';
-- --------------------------------------------------------------
create view subview_C as select
   'PR ' || rid as trans_id,
    l.amount, l.proj,
    a.acct_id as acct
    from	subledg_C	l
        join	acct		a on a.code = 'pr';

-- General ledger - this should contain all transactions from all subledgers
-- --------------------------------------------------------------
create view gen_ledg as
    select trans_id, amount, proj, acct from subview_A
    union 
    select trans_id, amount, proj, acct from subview_B
    union 
    select trans_id, amount, proj, acct from subview_C;

-- Populate the project table:
insert into proj (proj_id,title,par) values ( 1, 'The main parent project', null);
insert into proj (proj_id,title,par) values ( 2, 'First  child of 1', 1);
insert into proj (proj_id,title,par) values ( 3, 'Second child of 1', 1);
insert into proj (proj_id,title,par) values ( 4, 'First  child of 2', 2);
insert into proj (proj_id,title,par) values ( 5, 'second child of 2', 2);
insert into proj (proj_id,title,par) values ( 6, 'First  child of 5', 5);
insert into proj (proj_id,title,par) values ( 7, 'Second child of 5', 5);
insert into proj (proj_id,title,par) values ( 8, 'Third  child of 5', 5);
insert into proj (proj_id,title,par) values ( 9, 'Fourth child of 5', 5);
insert into proj (proj_id,title,par) select *,'Sample project',1 from generate_series(10,5000);

-- Populate the project relationships table:
insert into proj_rel (anst_id,prog_id,rel) select proj_id, proj_id, 0 from proj;		-- self
insert into proj_rel (anst_id,prog_id,rel) select par, proj_id, 1 from proj where proj_id != 1;	-- parents
insert into proj_rel (anst_id,prog_id,rel) select 1, proj_id, 2 from proj where proj_id in (4,5);
insert into proj_rel (anst_id,prog_id,rel) select 1, proj_id, 3 from proj where proj_id in (6,7,8,9);
insert into proj_rel (anst_id,prog_id,rel) select 2, proj_id, 2 from proj where proj_id in (6,7,8,9);

-- Populate the account table:
insert into acct (acct_id,title,code) values ( 100, 'Account 100', 'cash');
insert into acct (acct_id,title,code) values ( 101, 'Account 101', null);
insert into acct (acct_id,title,code) values ( 102, 'Account 102', null);
insert into acct (acct_id,title,code) values ( 103, 'Account 103', 'ar');
insert into acct (acct_id,title,code) values ( 104, 'Account 104', null);
insert into acct (acct_id,title,code) values ( 105, 'Account 105', null);
insert into acct (acct_id,title,code) values ( 106, 'Account 106', 'ap');
insert into acct (acct_id,title,code) values ( 107, 'Account 107', null);
insert into acct (acct_id,title,code) values ( 108, 'Account 108', 'pr');
insert into acct (acct_id,title,code) values ( 109, 'Account 109', null);

insert into subledg_A (rid,amount,proj) select *, (random() * 10000), (random() * 4999) + 1 from generate_series(1,100000);
insert into subledg_B (rid,amount,proj) select *, (random() * 10000), (random() * 4999) + 1 from generate_series(1,100000);
insert into subledg_C (rid,amount,proj) select *, (random() * 10000), (random() * 4999) + 1 from generate_series(1,100000);
vacuum analyze;