SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('05_documents.sql', current_timestamp);

CREATE TABLE ka_dokumente_tbl
(
	id serial NOT NULL,
    	uuid_id CHAR(36) UNIQUE,
	master_id int4 REFERENCES master_tbl(id) ON DELETE CASCADE,
	groesse int4,
	mime varchar(64),
	daten bytea, 
	name varchar(128),
	CONSTRAINT ka_dokumente_tbl_pkey PRIMARY KEY (id)
);

--
-- Funktionen zum Erstellen von Dokumenten
--
CREATE OR REPLACE FUNCTION create_fall_dokument_ds(mid integer, nuuid char(36), filename varchar) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE 
	did integer;
	rid integer;
BEGIN
    IF NOT isEditorOrStandin(mid) THEN
        RAISE EXCEPTION 'DS ID not session_user ID';
    END IF;

    SELECT INTO did id from ka_dokumente_tbl WHERE name = filename AND mid = master_id;
    IF NOT FOUND THEN
    	INSERT INTO ka_dokumente_tbl(master_id) VALUES (mid);
    	SELECT INTO rid currval('ka_dokumente_tbl_id_seq');
    ELSE 
        RETURN did;
    END IF;

    IF nuuid IS NOT NULL THEN
        UPDATE ka_dokumente_tbl 
        SET    uuid_id = nuuid 
        WHERE  id = rid;
    ELSE
        PERFORM set_uuid('ka_dokumente_tbl', rid);
    END IF;
    RETURN rid;
END;
$$ ;

REVOKE ALL ON FUNCTION create_fall_dokument_ds(integer, char(36), varchar) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION create_fall_dokument_ds( integer, char(36), varchar) TO GROUP :cm_group;


CREATE OR REPLACE FUNCTION delete_fall_dokument_ds(did integer) RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
    mid integer;
BEGIN
    SELECT INTO mid master_id FROM ka_dokumente_tbl WHERE id = did;
    IF NOT isEditorOrStandin(mid) THEN
        RAISE EXCEPTION 'DS ID not session_user ID';
    END IF;
    DELETE FROM ka_dokumente_tbl WHERE id = did;
END;
$$ ;

REVOKE ALL ON FUNCTION delete_fall_dokument_ds(integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION delete_fall_dokument_ds(integer) 
                TO GROUP :cm_group;

--
-- Funktion, die alle Anhaenge zu einem gegebenen Fall loescht
--
CREATE OR REPLACE FUNCTION delete_all_fall_dokumente(mid integer) 
RETURNS void 
LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
AS $$
BEGIN
    DELETE FROM ka_dokumente_tbl WHERE master_id = mid;
END;
$$;

REVOKE ALL     ON FUNCTION delete_all_fall_dokumente(integer) FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION delete_all_fall_dokumente(integer) TO GROUP :admin_group;

-- Globale Dokumente 

CREATE OR REPLACE FUNCTION create_global_dokument_ds(filename varchar) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
    did integer;
BEGIN
    SELECT INTO did id from ka_dokumente_tbl WHERE name = filename and master_id IS NULL;
    IF  NOT FOUND THEN
    	INSERT INTO ka_dokumente_tbl (master_id) VALUES (NULL);
    	RETURN currval('ka_dokumente_tbl_id_seq');
    ELSE
        RETURN did;
    END IF;
END;
$$ ;

REVOKE ALL ON FUNCTION create_global_dokument_ds(varchar) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION create_global_dokument_ds(varchar) TO GROUP :admin_group;


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

REVOKE ALL ON FUNCTION delete_global_dokument_ds(did integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION delete_global_dokument_ds( integer ) 
            TO GROUP :admin_group;

-- 
-- Sicht auf die Dokumenten Tabelle 
--

CREATE OR REPLACE VIEW ka_global_dokumente_tbl_view AS 
SELECT ka_dokumente_tbl.id, ka_dokumente_tbl.master_id,
ka_dokumente_tbl.groesse, ka_dokumente_tbl.mime, ka_dokumente_tbl.daten, ka_dokumente_tbl.name 
FROM ka_dokumente_tbl WHERE ka_dokumente_tbl.master_id is NULL;

CREATE OR REPLACE RULE ka_global_dokumente_delete_rule AS ON DELETE TO 
ka_global_dokumente_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_global_dokumente_insert_rule AS ON INSERT TO 
ka_global_dokumente_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_global_dokumente_update_rule AS ON UPDATE TO ka_global_dokumente_tbl_view 
	DO INSTEAD  UPDATE ka_dokumente_tbl SET 
	groesse = new.groesse, 
	mime = new.mime, 
	daten = new.daten, 
	name = new.name 
	WHERE ka_dokumente_tbl.id = new.id;

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

CREATE OR REPLACE VIEW ka_fall_dokumente_tbl_view AS 
SELECT ka_dokumente_tbl.id, ka_dokumente_tbl.uuid_id, ka_dokumente_tbl.master_id,
ka_dokumente_tbl.groesse, ka_dokumente_tbl.mime, ka_dokumente_tbl.daten,
ka_dokumente_tbl.name FROM ka_dokumente_tbl 
INNER JOIN master_tbl ON
ka_dokumente_tbl.master_id = master_tbl.id 
WHERE master_tbl.bearbeiter_id = getuserid()
    OR master_tbl.id IN (
        SELECT nm.master_id from nm_benutzer_master_tbl nm
        WHERE nm.benutzer_id IN (
	    SELECT nm2.gruppe_id from nm_benutzer_gruppe_tbl nm2 
	    WHERE nm2.benutzer_id = getuserid()
	)
    )
    OR pg_has_role('admin','MEMBER');

CREATE OR REPLACE RULE ka_fall_dokumente_delete_rule AS ON DELETE TO 
ka_fall_dokumente_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_fall_dokumente_insert_rule AS ON INSERT TO 
ka_fall_dokumente_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_fall_dokumente_update_rule AS ON UPDATE TO ka_fall_dokumente_tbl_view 
	DO INSTEAD  UPDATE ka_dokumente_tbl SET 
	groesse = new.groesse, 
	mime = new.mime, 
	daten = new.daten, 
	name = new.name 
	WHERE ka_dokumente_tbl.id = new.id;

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

--
-- TRIGGERS
--

-- ka_dokumente_tbl
CREATE TRIGGER t_ka_dokumente_tbl_insert AFTER
    INSERT ON ka_dokumente_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_dokumente_tbl_update AFTER
    UPDATE ON ka_dokumente_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_dokumente_tbl_delete AFTER
    DELETE ON ka_dokumente_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t1_ka_dokumente_tbl_insert AFTER
    INSERT ON ka_dokumente_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_dokumente_tbl_update AFTER
    UPDATE ON ka_dokumente_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_dokumente_tbl_delete AFTER
    DELETE ON ka_dokumente_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();



COMMIT;
