CREATE TABLE bank (
    bank_id integer NOT NULL,
    bank_mfo integer NOT NULL,
    bank_name character varying(255) NOT NULL
);

CREATE TABLE client (
    cl_id integer NOT NULL,
    cl_name varchar(50) NOT NULL,
    cl_bank_id integer NOT NULL,
    cl_birthday timestamp NOT NULL,
    cl_money numeric NOT NULL,
    cl_sex integer NOT NULL
);

CREATE TABLE operation (
    operation_id integer NOT NULL,
    bank_id integer NOT NULL,
    operation_date date NOT NULL,
    operation_type integer NOT NULL,
    operation_description text,
    op_cl_id integer,
    is_confirmed boolean
);

CREATE TABLE auth_right (
    class_name VARCHAR(255) NOT NULL
);

CREATE VIEW client_view AS
    SELECT 
	    client.cl_id, 
	    client.cl_name, 
	    client.cl_bank_id, 
	    (SELECT bank.bank_name FROM pfdemo.bank WHERE (bank.bank_id = client.cl_bank_id)) AS bank_name, 
	    client.cl_birthday, 
	    client.cl_money, 
	    client.cl_sex 
    FROM 
    	client;

INSERT INTO bank (bank_id, bank_mfo, bank_name) VALUES
(93,     300335,'Aval Bank'),
(94,     300335,'Raiffeisen Bank Aval'),
(95,     321228,'Ukrprom Bank'),
(96,     322948,'Forum Bank'),
(97,     322755,'CJSC'),
(98,  280101703,'Banca Sociala'),
(99,  280101609,'Banca de Economii'),
(100, 280101868,'Banca Comerciala Romana'),
(101, 280101701,'Victoriabank'),
(102, 280101716,'Eurocreditbank'),
(103, 280101881,'Investprivatbank'),
(104, 280101745,'Comertbank'),
(105, 280101749,'Mobiasbanca'),
(106, 280101325,'Moldindconbank'),
(107, 280101723,'Moldova Agroindbank'),
(108, 280101856,'Unibank'),
(109, 280101803,'Universalbank'),
(110, 280101735,'Fincombank'),
(111, 280101793,'Eximbank'),
(112, 280101845,'Energbank'),
(113, 10100000,'Narodowy Bank Polski'),
(114, 11400000,'BRE Bank SA'),
(115, 14700002,'Euro Bank SA'),
(116, 17900001,'Calyon Bank Polska SA'),
(117, 21300004,'VOLKSWAGEN BANK POLSKA SA'),
(118, 21400007,'Fiat Bank Polska SA'),
(119, 22900003,'Dexia Kommunalkredit Bank Polska SA'),
(120, 19500001,'GMAC Bank Polska SA');

INSERT INTO client (cl_id, cl_name, cl_bank_id, cl_birthday, cl_money, cl_sex) VALUES
(2, 'test2',          103, '2007-05-01 00:04:01', 2001,	2),
(3, 'Tom Ridik',      101, '2007-05-05 00:04:02', 1000,	1),
(4, 'Inna Gorobchuk', 109, '2005-04-06 00:06:03', 3050,	1);

INSERT INTO operation (operation_id, bank_id, operation_date, operation_type, operation_description, op_cl_id) VALUES
(462,  93, '2007-05-01', 2,	'no desc', 2),
(463,  99, '2007-05-24', 1,	'no desc', 2),
(465, 106, '2007-05-17', 3,	'no desc', 2),
(466, 103, '2007-05-13', 1,	'no desc', 2),
(467, 101, '2007-05-26', 3,	'no desc', 2),
(468, 104, '2007-05-01', 2,	'no desc', 2),
(469, 110, '2007-05-07', 1,	'test',	   2),
(464, 117, '2007-05-31', 4,	'test2',   3),
(470,  99, '2007-05-03', 1,	'test 4',  3),
(471, 116, '2007-05-31', 1,	'no desc', 4);

INSERT INTO auth_right (class_name) VALUES
('net.pleso.demo.client.bl.bank.EF.AddBankEF'),
('net.pleso.demo.client.bl.bank.EF.EditBankEF'),
('net.pleso.demo.client.bl.bank.BankRB$RowDeleter'),
('net.pleso.demo.client.bl.bank.BankRB$BankRBDataSource'),
('net.pleso.demo.client.bl.operation.EF.AddOperationEF'),
('net.pleso.demo.client.bl.operation.EF.EditOperationEF'),
('net.pleso.demo.client.bl.operation.OperationRB$OperationRBDataSource'),
('net.pleso.demo.client.bl.client.EF.AddClientEF'),
('net.pleso.demo.client.bl.client.EF.EditClientEF'),
('net.pleso.demo.client.bl.client.ClientRB$RowDeleter'),
('net.pleso.demo.client.bl.client.ClientRB$ClientRBDataSource'),
('net.pleso.demo.client.bl.client.EditClientEF$ClientOperationsProvider'),
('net.pleso.demo.client.bl.operation.OperationByClientRB$OperationRBDataSource');


ALTER TABLE auth_right
    ADD CONSTRAINT auth_right_pk PRIMARY KEY (class_name);
    
ALTER TABLE bank
    ADD CONSTRAINT bank_pkey PRIMARY KEY (bank_id);

ALTER TABLE client
    ADD CONSTRAINT client_pkey PRIMARY KEY (cl_id);

ALTER TABLE operation
    ADD CONSTRAINT operation_pkey PRIMARY KEY (operation_id);    
    
ALTER TABLE client
    ADD CONSTRAINT client_bank_fkey FOREIGN KEY (cl_bank_id) REFERENCES bank(bank_id);
    
ALTER TABLE operation
    ADD CONSTRAINT opreation_bank_fkey FOREIGN KEY (bank_id) REFERENCES bank(bank_id);  
    
ALTER TABLE operation
    ADD CONSTRAINT opreation_client_fkey FOREIGN KEY (op_cl_id) REFERENCES client(cl_id);

ALTER TABLE bank MODIFY COLUMN bank_id INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE client MODIFY COLUMN cl_id INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE operation MODIFY COLUMN operation_id INTEGER NOT NULL AUTO_INCREMENT;