CREATE DATABASE data_test;
\c data_test
CREATE TABLE tb_invoice_header (
    inv_id serial,
    client_id integer,
    payable_recievable character varying(15),
    amount numeric(10,2),
    amount_paid numeric(10,2),
    date_paid date,
    cheque character varying(20),
    cust_inv character varying(40),
    comments character varying(100),
    bank_date date
);

CREATE TABLE tb_invoice_detail (
    id serial,
    invoice_id integer,
    code character varying(15),
    description character varying(100),
    amount numeric(15,2),
    vat_code smallint,
    line_total numeric(15,2)
);

CREATE TABLE tb_activities (
    id serial NOT NULL,
    ac_code character varying(25),
    number smallint,
    description character varying(75)
);

CREATE TABLE tb_account_codes (
    id serial NOT NULL,
    head character varying(5),
    vote_sub_head smallint NOT NULL,
    number smallint,
    activity_code character varying(15),
    sc_description character varying(60),
    description character varying(50)
);

CREATE VIEW vw_ac AS
    SELECT tb_activities.id, tb_account_codes.sc_description, ac_code FROM (tb_activities JOIN tb_account_codes ON (((tb_activities.ac_code)::text = ((tb_account_codes.head)::text || (tb_account_codes.vote_sub_head)::text))));
CREATE VIEW vw_rec_sum AS
    SELECT code, sum(line_total) AS sum FROM (tb_invoice_detail JOIN tb_invoice_header ON ((tb_invoice_detail.invoice_id = tb_invoice_header.inv_id))) WHERE ((payable_recievable)::text = 'R'::text) GROUP BY payable_recievable, code ORDER BY code;
CREATE VIEW vw_pay_sum AS
    SELECT code, sum(line_total) AS sum FROM (tb_invoice_detail JOIN tb_invoice_header ON ((tb_invoice_detail.invoice_id = tb_invoice_header.inv_id))) WHERE ((payable_recievable)::text = 'P'::text) GROUP BY payable_recievable, code ORDER BY code;
CREATE VIEW vw_budget AS
    SELECT CASE WHEN ((vw_rec_sum.code IS NULL) AND (vw_pay_sum.sum IS NOT NULL)) THEN vw_pay_sum.code ELSE vw_pay_sum.code END AS code, CASE WHEN ("left"((ac_code)::text, 2) = 'SA'::text) THEN 'Salary Costs'::text WHEN ("left"((ac_code)::text, 2) = 'SC'::text) THEN 'Startup Costs'::text WHEN ("left"((ac_code)::text, 2) = 'RC'::text) THEN 'Running Costs'::text WHEN ("left"((ac_code)::text, 2) = 'TC'::text) THEN 'Training Costs'::text ELSE NULL::text END AS "case", sc_description, vw_rec_sum.sum AS "Budget", vw_pay_sum.sum AS expenditure, CASE WHEN (vw_pay_sum.sum IS NULL) THEN vw_rec_sum.sum WHEN (vw_pay_sum.sum < 0.00) THEN (0.00 - vw_pay_sum.sum) ELSE (vw_rec_sum.sum - vw_pay_sum.sum) END AS balance FROM ((vw_rec_sum RIGHT JOIN vw_pay_sum ON (((vw_rec_sum.code)::text = (vw_pay_sum.code)::text))) RIGHT JOIN vw_ac ON (((vw_pay_sum.code)::text = (vw_ac.id)::text))) ORDER BY to_number((vw_rec_sum.code)::text, '999'::text);