BEGIN;

CREATE TABLE account_heading (
  id serial not null unique,
  accno text primary key,
  parent_id int references account_heading(id),
  description text
);

CREATE TABLE account (
  id serial not null unique,
  accno text primary key,
  description text,
  category CHAR(1) NOT NULL,
  gifi_accno text,
  heading int not null references account_heading(id),
  contra bool not null default false
);

CREATE TABLE account_link (
   account_id int references account(id),
   description text,
   primary key (account_id, description)
);
INSERT INTO account_heading (id, accno, description)
SELECT id, accno, description FROM chart WHERE charttype = 'H';

INSERT INTO account (id, accno, description, category, gifi_accno, heading, 
	contra)
SELECT id, accno, description, category, gifi_accno, 
	(SELECT id FROM account_heading h WHERE h.accno < c.accno 
	ORDER BY accno DESC limit 1), 
	contra
FROM chart c WHERE charttype = 'A';

INSERT INTO account_link (account_id, description)
SELECT id, link FROM chart WHERE link IN ('AR', 'AP', 'IC') and charttype = 'A';

INSERT INTO account_link (account_id, description)
SELECT id, 'AR_amount' FROM chart WHERE link like '%AR_amount%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AR_tax' FROM chart WHERE link like '%AR_tax%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AR_paid' FROM chart WHERE link like '%AR_paid%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AR_overpayment' FROM chart WHERE link like '%AR_overpayment%'  and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AR_discount' FROM chart WHERE link like '%AR_discount%' and charttype = 'A';

INSERT INTO account_link (account_id, description)
SELECT id, 'AP_amount' FROM chart WHERE link like '%AR_amount%'  and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AP_tax' FROM chart WHERE link like '%AR_tax%'  and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AP_paid' FROM chart WHERE link like '%AR_paid%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AP_overpayment' FROM chart WHERE link like '%AR_overpayment%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'AP_discount' FROM chart WHERE link like '%AR_discount%' and charttype = 'A';

INSERT INTO account_link (account_id, description)
SELECT id, 'IC_sale' FROM chart WHERE link like '%IC_sale%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'IC_cogs' FROM chart WHERE link like '%IC_cogs%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'IC_taxpart' FROM chart WHERE link like '%IC_taxpart%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'IC_taxservice' FROM chart WHERE link like '%IC_taxservice%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'IC_income' FROM chart WHERE link like '%IC_income%' and charttype = 'A';
INSERT INTO account_link (account_id, description)
SELECT id, 'IC_expense' FROM chart WHERE link like '%IC_expense%' and charttype = 'A';

DROP TABLE chart CASCADE;

CREATE OR REPLACE FUNCTION concat_colon(TEXT, TEXT) returns TEXT as
$$
select CASE WHEN $1 IS NULL THEN $2 ELSE $1 || ':' || $2 END;
$$ language sql;

CREATE AGGREGATE concat_colon (
        BASETYPE = text,
        STYPE = text,
        SFUNC = concat_colon
);

CREATE VIEW chart AS
SELECT id, accno, description, 'H' as charttype, NULL as category, NULL as link, NULL as account_heading, null as gifi_accno, false as contra from account_heading UNION
select c.id, c.accno, c.description, 'A' as charttype, c.category, concat_colon(l.description) as link, heading, gifi_accno, contra from account c left join account_link l ON (c.id = l.account_id) group by c.id, c.accno, c.description, c.category, c.heading, c.gifi_accno, c.contra;

alter table cr_report add foreign key(chart_id) references account(id);
alter table cr_coa_to_account add foreign key (chart_id) references account(id);
alter table entity_credit_account add foreign key (discount_account_id) references account(id);
alter table entity_credit_account add foreign key (cash_account_id) references account(id);
alter table tax add foreign key (chart_id) references account(id);
alter table partstax add foreign key (chart_id) references account(id);
alter table entity_credit_account add foreign key (ar_ap_account_id) references account(id);
alter table acc_trans add foreign key (chart_id) references account(id);

GRANT SELECT ON chart TO public;

UPDATE menu_attribute SET value = 'account.pl' WHERE node_id = '137' AND attribute = 'module';
UPDATE menu_attribute SET value = 'new' WHERE node_id = 137 AND attribute = 'action';
COMMIT;
