-- doplnenie poloziek v doc_tree
\encoding UTF8

BEGIN;

DELETE FROM doc_tree;
SELECT setval('doc_tree_id_seq'::regclass, 1, false);

INSERT INTO doc_tree (position, name, parent) VALUES (1, '1 Vitajte', null);
INSERT INTO doc_tree (position, name, parent) VALUES (2, '2 Na začiatok to hlavné', null);
INSERT INTO doc_tree (position, name, parent) VALUES (3, '2.1 Zákaznícky portál moj.alternet.sk', 2);
INSERT INTO doc_tree (position, name, parent) VALUES (4, '2.2 Ako uhrádzať za služby', 2);
INSERT INTO doc_tree (position, name, parent) VALUES (5, '2.3 Reklamácia kvality alebo účtovania', 2);
INSERT INTO doc_tree (position, name, parent) VALUES (6, '2.4 Nahlasovanie porúch', 2);
INSERT INTO doc_tree (position, name, parent) VALUES (7, '3 Zriadenie fyzického pripojenia', null);
INSERT INTO doc_tree (position, name, parent) VALUES (8, '3.1 Káblové pripojenie', 7);
INSERT INTO doc_tree (position, name, parent) VALUES (9, '3.2 Bezdrôtové pripojenie', 7);
INSERT INTO doc_tree (position, name, parent) VALUES (10, '4 Služba Internet', null);
INSERT INTO doc_tree (position, name, parent) VALUES (11, '4.1 Nastavenie pripojenia k pevnej (káblovej) sieti', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (12, '4.1.1 Jediný pripojený počítač', 11);
INSERT INTO doc_tree (position, name, parent) VALUES (13, '4.1.2 Pripojenie viacerých počítačov', 11);
INSERT INTO doc_tree (position, name, parent) VALUES (14, '4.2 Nastavenie pripojenia k bezdrôtovej sieti', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (15, '4.2.1 Jediný pripojený počítač', 14);
INSERT INTO doc_tree (position, name, parent) VALUES (16, '4.2.2 Pripojenie viacerých počítačov', 14);
INSERT INTO doc_tree (position, name, parent) VALUES (17, '4.3 DNS servery', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (18, '4.4 Statická verejná IP adresa', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (19, '4.5 Špeciálne nastavenia pri použití neverejnej IP adresy', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (20, '4.5.1 FTP - použitie pasívneho módu', 19);
INSERT INTO doc_tree (position, name, parent) VALUES (21, '4.5.2 Používanie SMTP serverov pre odosielanie pošty', 19);
INSERT INTO doc_tree (position, name, parent) VALUES (22, '4.6 Pravidlá férového používania', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (23, '4.7 Poučenie o uchovávaní prenášaných údajov', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (24, '4.8 Zmena programu služieb', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (25, '4.9 Zrušenie služby', 10);
INSERT INTO doc_tree (position, name, parent) VALUES (26, '5 Elektronická pošta', NULL);
INSERT INTO doc_tree (position, name, parent) VALUES (27, '5.1 Rozsah poskytovanej služby', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (28, '5.2 Vytvorenie schránky a adresy', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (29, '5.3 Webmail - webový prístup k pošte', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (30, '5.4 Prístup cez poštový klient (Thunderbird)', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (31, '5.5 Zmena hesla', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (32, '5.6 Ochrana proti vírusom a nevyžiadanej pošte', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (33, '5.7 Poučenie o uchovávaní prenášaných údajov', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (34, '5.8 Zrušenie schránky', 26);
INSERT INTO doc_tree (position, name, parent) VALUES (35, '6 FTP server', NULL);
INSERT INTO doc_tree (position, name, parent) VALUES (36, '6.1 Popis služby', 35);
INSERT INTO doc_tree (position, name, parent) VALUES (37, '6.2 Vytvorenie konta pre FTP a Download Manager', 35);
INSERT INTO doc_tree (position, name, parent) VALUES (38, '6.3 Pripojenie na FTP server', 35);
INSERT INTO doc_tree (position, name, parent) VALUES (39, '6.4 Použitie Download Managera (Sancho)', 35);
INSERT INTO doc_tree (position, name, parent) VALUES (40, '6.5 Zrušenie FTP konta', 35);
INSERT INTO doc_tree (position, name, parent) VALUES (41, '7 Webhosting', null);
INSERT INTO doc_tree (position, name, parent) VALUES (42, '7.1 Poskytované služby', 41);
INSERT INTO doc_tree (position, name, parent) VALUES (43, '7.2 Vytvorenie web priestoru', 41);
INSERT INTO doc_tree (position, name, parent) VALUES (44, '7.3 Umiestnenie webstránky a jej aktualizácia', 41);
INSERT INTO doc_tree (position, name, parent) VALUES (45, '7.4 Zrušenie web priestoru', 41);
INSERT INTO doc_tree (position, name, parent) VALUES (46, '8 Kontakt', null);

INSERT INTO doc (doc_tree, url, name, text) values (1, 'index', 'Vitajte', 'lorem ipsum');

-- zmena vazby medzi doc a doc_tree opacne
ALTER TABLE doc_tree ADD COLUMN doc INT REFERENCES doc (id) ON UPDATE CASCADE ON DELETE SET NULL;
CREATE INDEX doc_tree_doc ON doc_tree (doc);
UPDATE doc_tree SET doc=(SELECT id FROM doc d WHERE d.doc_tree=doc_tree.id);
ALTER TABLE doc DROP COLUMN doc_tree;

-- presunute dalsie stlpce z doc do stromu
ALTER TABLE doc_tree ADD COLUMN url VARCHAR;
COMMENT ON COLUMN doc_tree.url IS 'SEO friendly cast URL';
CREATE INDEX doc_tree_url ON doc_tree (url);
UPDATE doc_tree SET url='';
ALTER TABLE doc_tree ALTER COLUMN url SET NOT NULL;

ALTER TABLE doc_tree ADD COLUMN active BOOLEAN NOT NULL DEFAULT true;
COMMENT ON COLUMN doc_tree.active IS 'ci sa polozka ma zobrazovat';

ALTER TABLE doc_tree ADD COLUMN supplier INT REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE;
COMMENT ON COLUMN doc_tree.supplier IS 'pre ktory brand plati tato polozka (null = vsetky)';
CREATE INDEX doc_tree_supplier ON doc_tree (supplier);

ALTER TABLE doc_tree ADD COLUMN access_level SMALLINT NOT NULL DEFAULT 0;
COMMENT ON COLUMN doc_tree.access_level IS 'potrebna uroven opravnenia: 0=vsetci, 1=prihlaseni, 2=partner, 3=admin';

ALTER TABLE doc DROP COLUMN url;
ALTER TABLE doc DROP COLUMN access_level;
COMMENT ON COLUMN doc.name IS 'interny popis clanku - vidi len admin';

INSERT INTO acl (dataset_name, permit_select) VALUES ('doc',true);
DELETE FROM acl WHERE dataset_name='doc_tree' AND permit_select=true;

ALTER TABLE doc ALTER COLUMN name DROP NOT NULL;

GRANT SELECT, INSERT, UPDATE, DELETE ON doc_tree TO extranet;
GRANT USAGE ON doc_tree_id_seq TO extranet;

GRANT SELECT, INSERT, UPDATE, DELETE ON doc TO extranet;
GRANT USAGE ON doc_id_seq TO extranet;

GRANT SELECT, INSERT, UPDATE, DELETE ON doc_file TO extranet;
GRANT USAGE ON doc_file_id_seq TO extranet;

ALTER TABLE doc_tree ALTER COLUMN position SET DEFAULT 0;
ALTER TABLE doc_file ADD COLUMN type VARCHAR;

-- z dna 7.10.2014 - odstranit cislovanie z poloziek v DB (michal)
UPDATE doc_tree SET name=substring(name from '[0-9\\.]* ?(.*)');

COMMIT;
