BEGIN;

CREATE TABLE person_role (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	PRIMARY KEY (id)
);

COMMENT ON TABLE person_role IS 'ciselnik moznych roli v ramci zastupovania';

CREATE TYPE enum_acl_dataset_name AS ENUM('person','contract','ipfon_account','ipfon_telno');

CREATE TABLE acl (
	id SERIAL NOT NULL,
	dataset_name enum_acl_dataset_name NOT NULL,
	dataset_id INT,
	dataset_field VARCHAR,
	person INT REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE,
	person_role INT REFERENCES person_role(id) ON UPDATE CASCADE ON DELETE CASCADE,
	permit_select BOOLEAN,
	permit_update BOOLEAN,
	permit_insert BOOLEAN,
	permit_delete BOOLEAN,
	PRIMARY KEY (id)
);

COMMENT ON COLUMN acl.dataset_name IS 'nazov datoveho zdroja';
COMMENT ON COLUMN acl.dataset_id IS 'id riadku v ramci datoveho zdroja';
COMMENT ON COLUMN acl.dataset_field IS 'nazov polozky (stlpca) v ramci datoveho zdroja';
COMMENT ON COLUMN acl.person IS 'id osoby, ktora ma/nema opravnenie';
COMMENT ON COLUMN acl.person_role IS 'id roly, ktora ma/nema opravnenie';
COMMENT ON COLUMN acl.permit_select IS 'ci dana osoba/rola moze zdroj (riadok, polozku) citat';
COMMENT ON COLUMN acl.permit_update IS 'ci dana osoba/rola moze zdroj (riadok, polozku) menit ak uz existuje';
COMMENT ON COLUMN acl.permit_insert IS 'ci dana osoba/rola moze do zdroja vlozit novy riadok ak este neexistuje';
COMMENT ON COLUMN acl.permit_select IS 'ci dana osoba/rola moze zdroj (riadok) vymazat';

CREATE INDEX acl_dataset_name_person_role_dataset_id
	ON acl (dataset_name, person, person_role, dataset_id);

CREATE TABLE person_representation (
	id SERIAL NOT NULL,
	representing INT NOT NULL REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE,
	represented INT REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE,
	person_role INT REFERENCES person_role(id) ON UPDATE CASCADE ON DELETE CASCADE,
	contract INT REFERENCES contract(id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (id)
);

COMMENT ON TABLE person_representation IS 'kto zastupuje aku osobu (spolocnost) a v akej roli tam posobi';

CREATE INDEX person_representation_representing ON person_representation (representing);
CREATE INDEX person_representation_role ON person_representation (person_role);
CREATE INDEX person_representation_represented ON person_representation (represented);
CREATE INDEX person_representation_contract ON person_representation (contract);

INSERT INTO person_representation (representing, represented) SELECT supervisor AS representing, subordinate AS represented FROM entitled_person ORDER BY id;

-- pristupove prava ku novym tabulkam
GRANT SELECT ON person_role TO extranet;
GRANT SELECT, INSERT, UPDATE, DELETE ON person_representation TO extranet;
GRANT USAGE ON SEQUENCE person_representation_id_seq TO extranet;
GRANT SELECT, INSERT, UPDATE, DELETE ON acl TO extranet;
GRANT USAGE ON SEQUENCE acl_id_seq TO extranet;

COMMIT;
