-- rozdelenie sprav na spolocny zaklad a jednotlive varianty

-- faza I. - vytvorenie tabulky
BEGIN;

CREATE TABLE message_variant (
	id SERIAL NOT NULL,
	message INT NOT NULL REFERENCES message(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	sender_contact INT REFERENCES person_contact(id) ON UPDATE CASCADE ON DELETE SET NULL,
	sender_uri VARCHAR,
	recipient_contact INT REFERENCES person_contact(id) ON UPDATE CASCADE ON DELETE SET NULL,
	recipient_uri VARCHAR,
	created TIMESTAMP NOT NULL DEFAULT date_trunc('second', now()),
	sent TIMESTAMP,
	received TIMESTAMP,
	medium INT NOT NULL REFERENCES message_medium(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	medium_variant INT REFERENCES message_medium_variant(id) ON UPDATE CASCADE ON DELETE SET NULL,
	status INT NOT NULL REFERENCES message_status(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	subject VARCHAR,
	body TEXT,
	recvcode VARCHAR,
	withdraw TIMESTAMP,
	billitem INT,
	layout INT REFERENCES message_layout(id) ON UPDATE CASCADE ON DELETE RESTRICT,
	template_variant INT REFERENCES message_template_variant(id) ON UPDATE CASCADE ON DELETE SET NULL,
	cash_on_delivery VARCHAR,
	include_pppu VARCHAR,
	price_netto NUMERIC(9,6),
	price_brutto NUMERIC(9,6),
	carrier_pages SMALLINT,
	tracking_number VARCHAR,
	PRIMARY KEY (id)
);

COMMENT ON COLUMN message_variant.message IS 'Sprava - ID z tabulky message';
COMMENT ON COLUMN message_variant.sender_contact IS 'Kontakt pouzity pri odosielani - ID z tabulky person_contact (ak je)';
COMMENT ON COLUMN message_variant.sender_uri IS 'Hodnota kontaktu odosielatela v case vytvorenia zaznamu';
COMMENT ON COLUMN message_variant.recipient_contact IS 'Kontakt pouzity pre prijem - ID z tabulky person_contact (ak je)';
COMMENT ON COLUMN message_variant.recipient_uri IS 'Hodnota kontaktu prijemcu v case vytvorenia zaznamu';
COMMENT ON COLUMN message_variant.created IS 'Datum a cas vytvorenia zaznamu';
COMMENT ON COLUMN message_variant.sent IS 'Datum a cas kedy bola sprava odoslana';
COMMENT ON COLUMN message_variant.received IS 'Datum a cas kedy bola sprava preukazatelne prijata';
COMMENT ON COLUMN message_variant.medium IS 'Medium ktorym sprava prisla, alebo odisla - ID z tabulky message_medium';
COMMENT ON COLUMN message_variant.medium_variant IS 'Variant media spravy - ID z tabulky message_medium_variant';
COMMENT ON COLUMN message_variant.status IS 'Stav spravy - ID z tabulky message_status';
COMMENT ON COLUMN message_variant.subject IS 'Predmet spravy (pri SMS prazdne)';
COMMENT ON COLUMN message_variant.body IS 'Telo spravy (pri SMS obsahuje celu spravu)';
COMMENT ON COLUMN message_variant.recvcode IS 'ID alebo kod, pod ktorym to prevzala protistrana pri uspesnom pokuse o dorucenie';
COMMENT ON COLUMN message_variant.withdraw IS 'Ak je vyplnene, sprava bola stornovana (spatvzata) po tom, co uz bola spracovana (odoslana)';
COMMENT ON COLUMN message_variant.billitem IS 'Skupina billitems (billitem_joiner), ktorou agregator nauctoval spravu odosielatelovi';
COMMENT ON COLUMN message_variant.layout IS 'Layout odoslanej spravy - ID z tabulky message_layout';
COMMENT ON COLUMN message_variant.template_variant IS 'ID variantu sablony ak ide o spravu generovanu cez sablonu';
COMMENT ON COLUMN message_variant.cash_on_delivery IS 'Udaje pre dobierku, vo formate JSON (pozri API/2 zelenej posty)';
COMMENT ON COLUMN message_variant.include_pppu IS 'Udaje pre prilozene seky, vo formate JSON (pozri API/2 zelenej posty)';
COMMENT ON COLUMN message_variant.price_netto IS 'Cena s DPH aku za odoslanie nauctoval poskytovatel postovej sluzby';
COMMENT ON COLUMN message_variant.price_brutto IS 'Cena bez DPH aku za odoslanie nauctoval poskytovatel postovej sluzby';
COMMENT ON COLUMN message_variant.carrier_pages IS 'Kolko stran v zasielke napocital poskytovatel postovej sluzby';
COMMENT ON COLUMN message_variant.tracking_number IS 'Podacie cislo pre ucely sledovania stavu zasielky v systemoch tretich stran';

CREATE INDEX message_variant_message ON message_variant (message);
CREATE INDEX message_variant_sender_contact ON message_variant (sender_contact);
CREATE INDEX message_variant_recipient_contact ON message_variant (recipient_contact);
CREATE INDEX message_variant_medium ON message_variant (medium);
CREATE INDEX message_variant_medium_variant ON message_variant (medium_variant);
CREATE INDEX message_variant_status ON message_variant (status);
CREATE INDEX message_variant_layout ON message_variant (layout);
CREATE INDEX message_variant_template_variant ON message_variant (template_variant);

GRANT SELECT, INSERT, UPDATE, DELETE ON message_variant TO extranet, billing;
GRANT USAGE ON message_variant_id_seq TO extranet, billing;

-- poznacit stare stlpce aby sa nepouzivali
COMMENT ON COLUMN message.sender_contact IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.sender_uri IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.recipient_contact IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.recipient_uri IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.created IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.sent IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.received IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.medium IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.medium_variant IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.status IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.subject IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.body IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.recvcode IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.withdraw IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.billitem IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.layout IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.template_variant IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.cash_on_delivery IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.include_pppu IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.price_netto IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.price_brutto IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.carrier_pages IS 'deprecated (presunute do message_variant)';
COMMENT ON COLUMN message.tracking_number IS 'deprecated (presunute do message_variant)';

-- stlpec pre JSON data
ALTER TABLE message ADD COLUMN template_data_json TEXT;
COMMENT ON COLUMN message.template_data_json IS 'Vstupne data pre generovanie zo sablony (vo formate JSON)';

-- stlpec pre nazov spravy v listingu
ALTER TABLE message ADD COLUMN name VARCHAR;
COMMENT ON COLUMN message.name IS 'Obecny nazov spravy pre ucely listingu (nemusi byt zhodny s predmetom spravy v jednotlivych variantoch)';

-- stlpec pre poznacenie sablony v message
ALTER TABLE message ADD COLUMN template INT REFERENCES message_template(id) ON UPDATE CASCADE ON DELETE SET NULL;
COMMENT ON COLUMN message.template IS 'ID sablony z tabulky message_template';
CREATE INDEX message_template_idx ON message (template);

-- odkazy na message, ktore po novom budu na message_variant
ALTER TABLE message_cc ADD COLUMN message_variant INT REFERENCES message_variant(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE message_file ADD COLUMN message_variant INT REFERENCES message_variant(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE message_log ADD COLUMN message_variant INT REFERENCES message_variant(id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE INDEX message_cc_message_variant ON message_cc (message_variant);
CREATE INDEX message_file_message_variant ON message_file (message_variant);
CREATE INDEX message_log_message_variant ON message_log (message_variant);

COMMENT ON COLUMN message_cc.message_variant IS 'Sprava o ktoru ide - ID z tabulky message_variant';
COMMENT ON COLUMN message_file.message_variant IS 'Sprava o ktoru ide - ID z tabulky message_variant';
COMMENT ON COLUMN message_log.message_variant IS 'Sprava o ktoru ide - ID z tabulky message_variant';

-- uprava starych odkazov, ktore budu neskor odstranene
ALTER TABLE message_log ALTER COLUMN message DROP NOT NULL;

COMMENT ON COLUMN message_cc.message IS 'deprecated (ma odkazovat len na message_variant)';
COMMENT ON COLUMN message_log.message IS 'deprecated (ma odkazovat len na message_variant)';

ALTER TABLE message ALTER COLUMN created DROP NOT NULL;
ALTER TABLE message ALTER COLUMN medium DROP NOT NULL;
ALTER TABLE message ALTER COLUMN status DROP NOT NULL;

COMMIT;

-- faza II. - migracia dat
BEGIN;

-- insert z message do message_variant
INSERT INTO message_variant (
	message, sender_contact, sender_uri, recipient_contact,
	recipient_uri, created, sent, received, medium, medium_variant, status,
	subject, body, recvcode, withdraw, billitem, layout, template_variant,
	cash_on_delivery, include_pppu, price_netto, price_brutto, carrier_pages,
	tracking_number
)
SELECT id AS message, sender_contact, sender_uri, recipient_contact,
	recipient_uri, created, sent, received, medium, medium_variant, status,
	subject, body, recvcode, withdraw, billitem, layout, template_variant,
	cash_on_delivery, include_pppu, price_netto, price_brutto, carrier_pages,
	tracking_number FROM message ORDER BY id;

UPDATE message SET template=(SELECT template FROM message_template_variant WHERE message_template.id=message.template_variant LIMIT 1);
UPDATE message SET name=subject;

UPDATE message_cc SET message_variant=(SELECT id FROM message_variant WHERE message_variant.message=message_cc.message);
UPDATE message_file SET message_variant=(SELECT id FROM message_variant WHERE message_variant.message=message_file.message);
UPDATE message_log SET message_variant=(SELECT id FROM message_variant WHERE message_variant.message=message_log.message);

-- insert z message_cc do message_variant
INSERT INTO message_variant (
	message, sender_contact, sender_uri, recipient_contact,
	recipient_uri, created, sent, received, medium, medium_variant, status,
	subject, body, recvcode, withdraw, billitem, layout, template_variant,
	cash_on_delivery, include_pppu, price_netto, price_brutto, carrier_pages,
	tracking_number
)
SELECT m.id AS message, sender_contact, sender_uri, c.recipient_contact,
	c.recipient_uri, created, c.sent, c.received, medium, medium_variant, c.status,
	subject, body, recvcode, withdraw, billitem, layout, template_variant,
	cash_on_delivery, include_pppu, price_netto, price_brutto, carrier_pages,
	tracking_number FROM message m, message_cc c WHERE m.id=c.message ORDER BY c.id;

COMMIT;

-- faza III. - upratovanie
BEGIN;

ALTER TABLE message DROP COLUMN sender_contact;
ALTER TABLE message DROP COLUMN sender_uri;
ALTER TABLE message DROP COLUMN recipient_contact;
ALTER TABLE message DROP COLUMN recipient_uri;
ALTER TABLE message DROP COLUMN created;
ALTER TABLE message DROP COLUMN sent;
ALTER TABLE message DROP COLUMN received;
ALTER TABLE message DROP COLUMN medium;
ALTER TABLE message DROP COLUMN medium_variant;
ALTER TABLE message DROP COLUMN status;
ALTER TABLE message DROP COLUMN subject;
ALTER TABLE message DROP COLUMN body;
ALTER TABLE message DROP COLUMN recvcode;
ALTER TABLE message DROP COLUMN withdraw;
ALTER TABLE message DROP COLUMN billitem;
ALTER TABLE message DROP COLUMN layout;
ALTER TABLE message DROP COLUMN template_variant;
ALTER TABLE message DROP COLUMN cash_on_delivery;
ALTER TABLE message DROP COLUMN include_pppu;
ALTER TABLE message DROP COLUMN price_netto;
ALTER TABLE message DROP COLUMN price_brutto;
ALTER TABLE message DROP COLUMN carrier_pages;
ALTER TABLE message DROP COLUMN tracking_number;

ALTER TABLE message_cc DROP COLUMN message;
ALTER TABLE message_log DROP COLUMN message;

DROP TABLE message_cc;

COMMIT;


-- fix message.template
UPDATE message SET template=(SELECT mtv.template FROM message_template_variant mtv, message_variant mv WHERE mtv.id=mv.template_variant AND mv.message=message.id LIMIT 1);

