-- tabulky pre ulozenie veci suvisiacich s middleware

BEGIN;

ALTER TABLE rtv_package ADD PRIMARY KEY (id);

-- typy settopboxov
CREATE TABLE rtv_stb_type (
	id SERIAL NOT NULL,
	name VARCHAR NOT NULL,
	PRIMARY KEY (id)
);

INSERT INTO rtv_stb_type (id, name) VALUES
	(1,'DLink DIB120'),
	(2,'Infomir MAG 250'),
	(10,'Motorola VIP 1003');

SELECT setval('rtv_stb_type_id_seq', 10);

-- zoznam settopboxov
CREATE TABLE rtv_stb (
	id SERIAL NOT NULL,
	evid NUMERIC(10,0) NOT NULL,
	name VARCHAR NOT NULL,
	mac MACADDR NOT NULL,
	type INT NOT NULL REFERENCES rtv_stb_type (id) ON UPDATE CASCADE ON DELETE RESTRICT,
	PRIMARY KEY (id)
);

CREATE INDEX rtv_stb_type_idx ON rtv_stb (type);

-- zoznam balickov ku zmluve
CREATE TABLE rtv_contract_package (
	id SERIAL NOT NULL,
	evid NUMERIC(10,0) NOT NULL,
	package INT NOT NULL REFERENCES rtv_package (id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (id)
);

CREATE INDEX rtv_contract_package_evid ON rtv_contract_package (evid);
CREATE INDEX rtv_contract_package_package ON rtv_contract_package (package);

-- este prava
GRANT SELECT ON rtv_stb_type TO extranet;

GRANT SELECT, INSERT, UPDATE ON rtv_stb TO extranet;
GRANT USAGE ON rtv_stb_id_seq TO extranet;

GRANT SELECT, INSERT, UPDATE, DELETE ON rtv_contract_package TO extranet;
GRANT USAGE ON rtv_contract_package_id_seq TO extranet;

-- doplnene parametre podla 2.9.2014
ALTER TABLE rtv_stb ADD COLUMN activation TIMESTAMP;
ALTER TABLE rtv_stb ADD COLUMN deactivation TIMESTAMP;
ALTER TABLE rtv_stb ADD COLUMN parentallock VARCHAR(4) NOT NULL DEFAULT '0000';

ALTER TABLE rtv_contract_package ADD COLUMN activation TIMESTAMP;
ALTER TABLE rtv_contract_package ADD COLUMN deactivation TIMESTAMP;

COMMENT ON COLUMN rtv_stb.parentallock IS 'Kod, ktorym sa odomknu programy pre dospelych';
COMMENT ON COLUMN rtv_stb.evid IS 'Zmluva ku ktorej je zariadenie zapojene';
COMMENT ON COLUMN rtv_stb.mac IS 'MAC adresa zariadenia (s dvojbodkami, hex cislice su lowercase)';
COMMENT ON COLUMN rtv_stb.type IS 'Typ zariadenia z tabulky rtv_stb_type';

COMMENT ON TABLE rtv_stb IS 'Zoznam zariadeni (set top boxov)';
COMMENT ON TABLE rtv_stb_type IS 'Zoznam typov zariadeni (set top boxov)';
COMMENT ON TABLE rtv_contract_package IS 'Vazobna tabulka medzi zmluvou a balickami programov';

-- rtv_channel
ALTER TABLE rtv_channel ADD COLUMN xmltvname VARCHAR NOT NULL DEFAULT '';
COMMENT ON COLUMN rtv_channel.xmltvname IS 'ID resp. nazov kanalu v EPG XML';

ALTER TABLE rtv_channel ADD COLUMN position SMALLINT NOT NULL DEFAULT 255;
COMMENT ON COLUMN rtv_channel.position IS 'Poradie v akom sa kanaly maju zobrazit vo vypise';

ALTER TABLE rtv_channel ADD COLUMN active SMALLINT NOT NULL DEFAULT 1;
COMMENT ON COLUMN rtv_channel.active IS 'Ci je dany kanal aktivny (1=ano, 0=nie)';

ALTER TABLE rtv_channel ADD COLUMN parentalcontrol SMALLINT NOT NULL DEFAULT 0;
COMMENT ON COLUMN rtv_channel.parentalcontrol IS 'Ci je dany kanal len pre dospelych (1=ano, 0=nie)';

-- channel_avail => rtv_headend_channel
ALTER TABLE rtv_headend_channel ALTER COLUMN headend SET NOT NULL;
COMMENT ON COLUMN rtv_headend_channel.headend IS 'Headend = lokalita';

ALTER TABLE rtv_headend_channel ALTER COLUMN channel SET NOT NULL;

ALTER TABLE rtv_headend_channel ADD COLUMN ip INET;
COMMENT ON COLUMN rtv_headend_channel.ip IS 'Multicast IP adresa';

ALTER TABLE rtv_headend_channel ADD COLUMN port INT;
COMMENT ON COLUMN rtv_headend_channel.port IS 'Cislo portu pre multicast';

ALTER TABLE rtv_headend_channel ADD COLUMN active SMALLINT NOT NULL DEFAULT 1;
COMMENT ON COLUMN rtv_headend_channel.active IS 'Ci je tento zaznam aktivny (1=ano, 0=nie)';

ALTER TABLE rtv_headend_channel ADD COLUMN priority SMALLINT NOT NULL DEFAULT 0;
COMMENT ON COLUMN rtv_headend_channel.priority IS 'Priorita zaznamu (ak je viac zaznamov)';

-- priznak changed na synchronizaciu s middleware
ALTER TABLE rtv_stb ADD COLUMN changed TIMESTAMP;
ALTER TABLE rtv_contract_package ADD COLUMN changed TIMESTAMP;

COMMIT;
