-- varianty postovych zasielok

\encoding UTF8

CREATE TABLE message_medium_variant (
	id SERIAL NOT NULL,
	medium INT NOT NULL REFERENCES message_medium (id) ON UPDATE CASCADE ON DELETE CASCADE,
	name VARCHAR NOT NULL,
	params VARCHAR NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX message_medium_variant_medium ON message_medium_variant (medium);

GRANT SELECT ON message_medium_variant TO extranet, billing;

-- moznosti:
-- printing: blackWhite, color
-- correspondence: 2ndClass, registered, registeredWithAdvice
-- envelope: c4, dl, ...

-- alebo:
-- product: Z2TRA4SCBDOODL, ...

INSERT INTO message_medium_variant (medium, name, params) VALUES
	(4, 'normal čiernobielo', 'printing:blackWhite,correspondence:2ndClass'),
	(4, 'normal farebne', 'printing:color,correspondence:2ndClass'),

	(4, 'doporučene ČB', 'printing:blackWhite,correspondence:registered'),
	(4, 'doporučene farebne', 'printing:color,correspondence:registered');

ALTER TABLE message ADD COLUMN medium_variant INT REFERENCES message_medium_variant (id) ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX message_message_medium_variant ON message (medium_variant);

COMMENT ON message.medium_variant IS 'Variant odosielanej spravy - ID z tabulky message_medium_variant';

-- treba nam evidovat ID pod akym sa to odoslalo
ALTER TABLE message ADD COLUMN recvcode VARCHAR;
COMMENT ON message.recvcode IS 'ID alebo kod, pod ktorym to prevzala protistrana pri uspesnom pokuse o dorucenie';

-- oprava aby bolo rovnake nazvoslovie v jednotlivych polozkach
UPDATE message_medium_variant SET name='doporučene čiernobielo' WHERE id=3;

-- varianty s dorucenkami
INSERT INTO message_medium_variant (medium, name, params) VALUES
	(4, 's doručenkou čiernobielo', 'printing:blackWhite,correspondence:registeredWithAdvice'),
	(4, 's doručenkou farebne', 'printing:color,correspondence:registeredWithAdvice');
