-- osoba moze mat rozne adresy a k nim prisluchajuce udaje

ALTER TABLE person ADD COLUMN part_of INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE INDEX person_part_of ON person (part_of);

COMMENT ON COLUMN person.part_of IS 'odkaz na nadradenu osobu, ktorej je tato osoba organizacnou zlozkou, adresou, castou';

-- podla typu spravy su pripustne rozne osoby ako odosielatel

CREATE TABLE message_type_sender (
	id SERIAL NOT NULL,
	type INT NOT NULL REFERENCES message_type (id) ON UPDATE CASCADE ON DELETE CASCADE,
	person INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
	header VARCHAR,
	PRIMARY KEY (id)
);

CREATE INDEX message_type_sender_type ON message_type_sender (type);
CREATE INDEX message_type_sender_person ON message_type_sender (person);

GRANT SELECT ON message_type_sender TO extranet, billing;

COMMENT ON TABLE message_type_sender IS 'odkaz na nadradenu osobu, ktorej je tato osoba organizacnou zlozkou, adresou, castou';
COMMENT ON COLUMN message_type_sender.type IS 'typ spravy';
COMMENT ON COLUMN message_type_sender.person IS 'kto moze odoslat tento typ spravy (organizacna zlozka)';
COMMENT ON COLUMN message_type_sender.header IS 'plne znenie hlavicky odosielatela  v dopise (ak je prazdne pouziju sa udaje z person a person_contact)';

-- osoba podpisana pod spravou moze mat rozne role
-- TODO: nie je toto presne uloha pre tabulky person_role a person_representation?

ALTER TABLE message ADD COLUMN signedby_role INT REFERENCES person_role (id) ON UPDATE CASCADE ON DELETE SET NULL;
CREATE INDEX message_signedby_role ON message (signedby_role);

COMMENT ON COLUMN message.signedby_role IS 'rola osoby, ktora spravu podpisala';

/*
CREATE TABLE message_person_role (
	id SERIAL NOT NULL,
	type INT NOT NULL REFERENCES message_type (id) ON UPDATE CASCADE ON DELETE CASCADE,
	person INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
	header VARCHAR,
	PRIMARY KEY (id)
);
*/