-- zmeny v messages pre nove vlastnosti zelenej posty

-- JSON parametre pre zelenu postu
-- dobierka (#79250)
ALTER TABLE message ADD COLUMN cash_on_delivery VARCHAR;
COMMENT ON COLUMN message.cash_on_delivery IS 'Udaje pre dobierku, vo formate JSON (pozri API/2 zelenej posty)';

-- postove poukazy (#75691)
ALTER TABLE message ADD COLUMN include_pppu VARCHAR;
COMMENT ON COLUMN message.include_pppu IS 'Udaje pre prilozene seky, vo formate JSON (pozri API/2 zelenej posty)';

-- udaje zistene zo zelenej posty po odoslani zasielky (#72278)
ALTER TABLE message ADD COLUMN price_netto NUMERIC(8,2);
ALTER TABLE message ADD COLUMN price_brutto NUMERIC(8,2);
ALTER TABLE message ADD COLUMN carrier_pages SMALLINT;
ALTER TABLE message ADD COLUMN tracking_number VARCHAR;

COMMENT ON COLUMN message.price_netto IS 'Cena bez DPH aku za odoslanie nauctoval poskytovatel postovej sluzby';
COMMENT ON COLUMN message.price_brutto IS 'Cena s DPH aku za odoslanie nauctoval poskytovatel postovej sluzby';
COMMENT ON COLUMN message.carrier_pages IS 'Kolko stran v zasielke napocital poskytovatel postovej sluzby';
COMMENT ON COLUMN message.tracking_number IS 'Podacie cislo pre ucely sledovania stavu zasielky v systemoch tretich stran';

-- sledovanie priebehu dorucovania (#72278)
CREATE TABLE message_log (
	id SERIAL NOT NULL,
	message INT NOT NULL REFERENCES message (id) ON UPDATE CASCADE ON DELETE CASCADE,
	carrier INT NOT NULL REFERENCES person (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	created TIMESTAMP NOT NULL DEFAULT NOW(),
	event_date TIMESTAMP NOT NULL,
	code VARCHAR NOT NULL,
	description VARCHAR,
	location VARCHAR,
	PRIMARY KEY (id)
);
CREATE INDEX message_log_message ON message_log (message);
CREATE INDEX message_log_carrier ON message_log (carrier);

COMMENT ON TABLE message_log IS 'Zaznamy o priebehu dorucovania a zmenach stavov odoslanych sprav';
COMMENT ON COLUMN message_log.message IS 'Sprava o ktoru ide - ID z tabulky message';
COMMENT ON COLUMN message_log.carrier IS 'Od koho sme zaznam importovali - ID z tabulky person';
COMMENT ON COLUMN message_log.created IS 'Kedy sme zaznam zapisali do tejto tabulky';
COMMENT ON COLUMN message_log.event_date IS 'Kedy ku udalosti prislo podla povodcu informacie (podla osoby zo stlpca carrier)';
COMMENT ON COLUMN message_log.code IS 'Kod alebo ID stavu';
COMMENT ON COLUMN message_log.description IS 'Ludsky popis stavu, pripadne dalsie podrobnosti ktore sa nehodia inde';
COMMENT ON COLUMN message_log.location IS 'Udaje o mieste kde k udalosti doslo, ak to dava zmysel';

GRANT SELECT, INSERT ON message_log TO extranet;
GRANT USAGE ON message_log_id_seq TO extranet;

GRANT SELECT, INSERT ON message_log TO billing;
GRANT USAGE ON message_log_id_seq TO billing;

-- vytvorenie variant pre posielanie bez tlacenia adresy na zadnej strane (#75690)
INSERT INTO message_medium_variant (medium, name, params, tarifitem)
SELECT medium, name || ' bez backpage adresy', params || ',backpage=false', tarifitem FROM message_medium_variant WHERE medium=4 ORDER BY id;

-- zmeny 4.5.2017:
BEGIN;

-- oprava vymenenych popisov
COMMENT ON COLUMN message.price_netto IS 'Cena s DPH aku za odoslanie nauctoval poskytovatel postovej sluzby';
COMMENT ON COLUMN message.price_brutto IS 'Cena bez DPH aku za odoslanie nauctoval poskytovatel postovej sluzby';

-- oprava poctu desatinnych miest ceny na 6
ALTER TABLE message ALTER COLUMN price_netto SET DATA TYPE NUMERIC(9,6);
ALTER TABLE message ALTER COLUMN price_brutto SET DATA TYPE NUMERIC(9,6);

-- odstranenie povinneho datumu eventu v tabulke message_log (niektore bohuzial nemaju datum)
ALTER TABLE message_log ALTER COLUMN event_date DROP NOT NULL;

-- oprava formatu parametrov produktu pre zelenu postu
UPDATE message_medium_variant SET params=REPLACE(params, '=', ':') WHERE medium=4 AND params LIKE '%=%';

COMMIT;
