-- docustore zlucene so zasielanim faktur postou a SMS

\encoding UTF8

CREATE TABLE message_medium (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	person_contact_type INT REFERENCES person_contact_type (id) ON UPDATE CASCADE ON DELETE SET NULL,
	PRIMARY KEY (id)
);
CREATE INDEX message_medium_person_contact_type ON message_medium(person_contact_type);

COMMENT ON TABLE message_medium IS 'Prenosovy kanal, ktorym bola (ma byt) sprava odoslana/dorucena';
COMMENT ON COLUMN message_medium.person_contact_type IS 'Odkaz na typ kontaktu, ktory sa ma v komunikacii pouzit';

INSERT INTO message_medium (name, person_contact_type) VALUES
	('E-mail', 1),
	('Telefón', 2),
	('SMS', 4),
	('Slovenská pošta', NULL);



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

COMMENT ON TABLE message_type IS 'Typ spravy popisuje zakladne zatriedenie podla dovodu, pre ktory odosielatel povazoval za potrebne sa vobec ozvat';
COMMENT ON COLUMN message_type.person_contact_flag IS 'Nazov stlpca z person_contact, ktory ma byt nastaveny ak ma na neho prist sprava tohto typu';

INSERT INTO message_type (name, person_contact_flag) VALUES
	('Nezatriedené', NULL),
	('Zmluva', NULL),
	('Odovzdávací protokol', NULL),
	('Výpoveď zmluvy', NULL),
	('Splnomocnenie', NULL),
	('Objednávka', NULL),
	('Žiadosť', NULL),
	('Ponuka', NULL),
	('Faktúra', NULL),
	('Elektronická faktúra', 'send_billing'),
	('Upomienka', 'send_billing'),
	('Upozornenie', NULL),
	('Výzva', NULL),
	('Oznámenie', NULL),
	('Sťažnosť', NULL),
	('Technická informácia', 'send_tech'),
	('Marketingová správa', 'send_marketing'),
	('Promotion', 'send_marketing'),
	('Spam', NULL),
	('Ostatné', NULL);



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

COMMENT ON TABLE message_status IS 'Stav v akom sa sprava naposledy nachadzala';

INSERT INTO message_status (id, name, description) VALUES
-- vseobecne stavy platne pre viacere media pri odosielani
	(0, 'Pripravená na odoslanie', 'Východzí stav odchodzích správ - správa fyzicky existuje ešte len ako záznam v databáze'),
	(1, 'Odoslaná', 'Správa opustila portál alebo priestory firmy - jej doručenie je na externej firme, alebo na inom serveri'),
	(2, 'Doručená', 'Správa bola preukázateľne doručená'),

-- vseobecne stavy platne pre viacere media pri prijimani
	(50, 'Nevybavená', 'Východzí stav prijatých správ - správa došla do spoločnosti a je potrebné ju ďalej spracovať'),
	(51, 'Rieši sa', 'Správu dostal na starosť konkrétny človek a čaká na vybavenie'),
	(52, 'Vybavená', 'Správa bola spracovaná a na jej základe sa niečo podniklo'),
	(53, 'Vzaté na vedomie', 'Správa bola spracovaná a jej výsledkom je, že nie je potrebné nič robiť'),

-- chybove a vynimocne stavy
	(90, 'Nedoručiteľná', 'Správa sa vrátila ako definitívne nedoručená bez udania dôvodu'),
	(91, 'Odmietnutá', 'Správa sa vrátila ako nedoručená pretože ju adresát odmietol prevziať'),
	(92, 'Adresát neznámy', 'Správa sa vrátila ako nedoručená pretože adresát je doručovateľovi neznámy'),

-- stavy suvisiace s emailom
	(100, 'Dočasne nedoručená', 'O správe prišiel späť kód 4xx, ktorý hovorí o tom, že chyba môže byť dočasná a ešte sa budú vykonávať pokusy o doručenie'),

-- stavy suvisiace s telefonom - treba vobec?
	-- nedviha = nedorucitelna => id=90
	-- odmietol hovor = odmietnuta => id=91
	-- halali = adresat neznamy => id=92
	(200, 'Prerušená', 'Došlo ku prerušeniu počas hovoru'),

-- stavy suvisiace so SMS - netreba
	-- nedorucena => id=1
	-- dorucena => id=2
	-- nedorucitelna => id=90

-- stavy suvisiace s papierovymi dopismi pri odosielani
	(300, 'Odoslaná na tlač', 'Správa bola odoslaná na tlačiareň a má sa v najbližšej dobe vytlačiť'),
	(301, 'Čaká na podpis', 'Správa je vytlačená a je potrebné ju podpísať'),
	(302, 'Čaká na zabalenie', 'Správa je vytlačená a je potrebné ju zabaliť do obálky'),
	(303, 'Čaká na odoslanie', 'Správa je zabalená a je potrebné ju hodiť do schránky alebo podať na pošte');
	-- odoslana => id=1



CREATE TABLE message (
	id SERIAL NOT NULL,

	sender INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE SET NULL,
	sender_contact INT REFERENCES person_contact (id) ON UPDATE CASCADE ON DELETE SET NULL,
	sender_uri VARCHAR,

	signedby INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE SET NULL,
	sentby INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE SET NULL,

	recipient INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE SET NULL,
	recipient_contact INT REFERENCES person_contact (id) ON UPDATE CASCADE ON DELETE SET NULL,
	recipient_uri VARCHAR,

	created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT DATE_TRUNC('second',NOW()),
	sent TIMESTAMP WITH TIME ZONE,
	received TIMESTAMP WITH TIME ZONE,

	type INT NOT NULL REFERENCES message_type (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	medium INT NOT NULL REFERENCES message_medium (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	status INT NOT NULL REFERENCES message_status (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFAULT 0,
	inbound BOOLEAN NOT NULL,

	rtid INT,
	evid INT,

	subject VARCHAR,
	body TEXT,

	PRIMARY KEY (id)
);

CREATE INDEX message_sender ON message(sender);
CREATE INDEX message_sender_contact ON message(sender_contact);
CREATE INDEX message_signedby ON message(signedby);
CREATE INDEX message_sentby ON message(sentby);
CREATE INDEX message_recipient ON message(recipient);
CREATE INDEX message_recipient_contact ON message(recipient_contact);
CREATE INDEX message_type ON message(type);
CREATE INDEX message_medium ON message(medium);
CREATE INDEX message_status ON message(status);

COMMENT ON TABLE message IS 'Spravy v komunikacii s externym prostredim';
COMMENT ON COLUMN message.sender IS 'Odosielatel spravy - ID z tabulky person (ak je)';
COMMENT ON COLUMN message.sender_contact IS 'Kontakt pouzity pri odosielani - ID z tabulky person_contact (ak je)';
COMMENT ON COLUMN message.sender_uri IS 'Hodnota kontaktu odosielatela v case odoslania';
COMMENT ON COLUMN message.signedby IS 'Osoba podpisana pod spravou';
COMMENT ON COLUMN message.sentby IS 'Osoba, ktora spravu realne odoslala (pri poste moze by iny nez signedby)';
COMMENT ON COLUMN message.recipient IS 'Prijemca spravy - ID z tabulky person (ak je)';
COMMENT ON COLUMN message.recipient_contact IS 'Kontakt pouzity pre prijem - ID z tabulky person_contact (ak je)';
COMMENT ON COLUMN message.recipient_uri IS 'Hodnota kontaktu prijemcu v case odoslania';
COMMENT ON COLUMN message.created IS 'Datum a cas vytvorenia zaznamu';
COMMENT ON COLUMN message.sent IS 'Datum a cas kedy bola sprava odoslana';
COMMENT ON COLUMN message.received IS 'Datum a cas kedy bola sprava preukazatelne prijata';
COMMENT ON COLUMN message.type IS 'Typ spravy - ID z tabulky message_type';
COMMENT ON COLUMN message.medium IS 'Medium ktorym sprava prisla, alebo odisla - ID z tabulky message_medium';
COMMENT ON COLUMN message.status IS 'Stav spravy - ID z tabulky message_status';
COMMENT ON COLUMN message.inbound IS 'Priznak ci ide o doslu spravu (true), alebo o spravu odoslanu (false)';
COMMENT ON COLUMN message.rtid IS 'ID ticketu z RT ak sa tato sprava tyka nejakeho ticketu';
COMMENT ON COLUMN message.evid IS 'Cislo zmluvy ak sa tato sprava tyke nejakej zmluvy';
COMMENT ON COLUMN message.subject IS 'Predmet spravy (pri SMS prazdne)';
COMMENT ON COLUMN message.body IS 'Telo spravy (pri SMS obsahuje celu spravu)';



CREATE TABLE message_cc (
	id SERIAL NOT NULL,
	message INT REFERENCES message(id) ON UPDATE CASCADE ON DELETE CASCADE,

	recipient INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE SET NULL,
	recipient_contact INT REFERENCES person_contact (id) ON UPDATE CASCADE ON DELETE SET NULL,
	recipient_uri VARCHAR,

	sent TIMESTAMP WITH TIME ZONE,
	received TIMESTAMP WITH TIME ZONE,

	status INT NOT NULL REFERENCES message_status (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFAULT 0,

	PRIMARY KEY (id)
);

CREATE INDEX message_cc_message ON message_cc(message);
CREATE INDEX message_cc_recipient ON message_cc(recipient);
CREATE INDEX message_cc_recipient_contact ON message_cc(recipient_contact);
CREATE INDEX message_cc_status ON message_cc(status);

COMMENT ON TABLE message_cc IS 'Kopie sprav - komu vsetkemu sprava isla na vedomie';
COMMENT ON COLUMN message_cc.message IS 'ID spravy z tabulky message';
COMMENT ON COLUMN message_cc.recipient IS 'Prijemca spravy - ID z tabulky person (ak je)';
COMMENT ON COLUMN message_cc.recipient_contact IS 'Kontakt pouzity pre prijem - ID z tabulky person_contact (ak je)';
COMMENT ON COLUMN message_cc.recipient_uri IS 'Hodnota kontaktu prijemcu v case odoslania';
COMMENT ON COLUMN message_cc.sent IS 'Datum a cas kedy bola sprava odoslana';
COMMENT ON COLUMN message_cc.received IS 'Datum a cas kedy bola sprava preukazatelne prijata';
COMMENT ON COLUMN message_cc.status IS 'Stav spravy - ID z tabulky message_status';



CREATE TABLE message_file (
	id SERIAL NOT NULL,
	message INT REFERENCES message(id) ON UPDATE CASCADE ON DELETE CASCADE,
	name VARCHAR NOT NULL,
	path VARCHAR NOT NULL,
	size INT NOT NULL,
	mimetype VARCHAR,
	attachment BOOLEAN NOT NULL DEFAULT FALSE,
	PRIMARY KEY (id)
);

CREATE INDEX message_file_message ON message_file(message);

COMMENT ON TABLE message_file IS 'Prilohy sprav a subory so spravami suvisiace';
COMMENT ON COLUMN message_file.message IS 'ID spravy z tabulky message';
COMMENT ON COLUMN message_file.name IS 'Nazov suboru aj s priponou, tak ako by bol ulozeny v pocitaci bezneho cloveka';
COMMENT ON COLUMN message_file.path IS 'Relativna cesta k suboru v ramci uloziska na serveri (moze byt ina od "name")';
COMMENT ON COLUMN message_file.size IS 'Velkost suboru v bajtoch';
COMMENT ON COLUMN message_file.mimetype IS 'Typ suboru podla MIME databazy';
COMMENT ON COLUMN message_file.attachment IS 'Priznak ci ide o prilohu spravy (true), alebo o spravu samotnu (false) - ak je to priloha malo by tam byt nieco ine nez co je v message.body';
