SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('04_configuration.sql', current_timestamp);

CREATE TABLE ka_konfiguration_tbl
(
	id serial NOT NULL,
	bez varchar(64) NOT NULL,
	wert text,
	CONSTRAINT ka_konfiguration_tbl_pkey PRIMARY KEY (id)
);

INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('fkz', '');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('ka_name', '');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('ka_adresse', '');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('ee_formular', '');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('fp_formular', '');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('ud_formular', '');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('anon_transfer', 'on');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('max_speicherdauer', '365');
INSERT INTO ka_konfiguration_tbl (bez, wert) VALUES ('jmdstrukur_permission_userid', '');

--
-- Funktionen zum Erstellen und Loeschen von Konfigurationen 
--

CREATE OR REPLACE FUNCTION create_konfiguration_ds(_bez varchar, _wert varchar) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    INSERT INTO ka_konfiguration_tbl(bez, wert) VALUES (_bez, _wert);
    RETURN currval('ka_konfiguration_tbl_id_seq');
END;
$$ ;

CREATE OR REPLACE FUNCTION delete_konfiguration_ds(kid integer ) RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    DELETE FROM ka_konfiguration_tbl WHERE id = kid;
END;
$$ ;

-- Sicht auf die KA Konfigurations Tabelle 

CREATE OR REPLACE VIEW ka_konfiguration_tbl_view AS 
SELECT ka_konfiguration_tbl.id, ka_konfiguration_tbl.bez,
ka_konfiguration_tbl.wert FROM ka_konfiguration_tbl;

CREATE OR REPLACE RULE ka_konfiguration_delete_rule AS ON DELETE TO 
ka_konfiguration_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_konfiguration_insert_rule AS ON INSERT TO 
ka_konfiguration_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_konfiguration_update_rule AS ON UPDATE TO ka_konfiguration_tbl_view 
	DO INSTEAD UPDATE ka_konfiguration_tbl SET
	wert = new.wert
	WHERE bez = new.bez;

GRANT SELECT ON ka_konfiguration_tbl_view TO GROUP :cm_group;
GRANT SELECT ON ka_konfiguration_tbl_view TO GROUP :admin_group;
GRANT UPDATE ON ka_konfiguration_tbl_view TO GROUP :admin_group;

-- 
-- TRIGGERS
--

-- ka_konfiguration_tbl
CREATE TRIGGER t_ka_konfiguration_tbl_insert AFTER
    INSERT ON ka_konfiguration_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_konfiguration_tbl_update AFTER
    UPDATE ON ka_konfiguration_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_konfiguration_tbl_delete AFTER
    DELETE ON ka_konfiguration_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();



COMMIT;
