SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('07_logbook.sql', current_timestamp);

-- ka_logbuch_art_tbl
--
-- Speichert die verschiedenen Arten von Logbuch-Eintraegen
--

CREATE TABLE ka_logbuch_art_tbl (
    id         SERIAL NOT NULL,
    beschreibung VARCHAR(256),
    CONSTRAINT ka_logbuch_art_tbl_pkey PRIMARY KEY (id)
);

GRANT SELECT ON ka_logbuch_art_tbl TO GROUP :cm_group;
GRANT SELECT ON ka_logbuch_art_tbl TO GROUP :admin_group;

INSERT INTO ka_logbuch_art_tbl (id, beschreibung) VALUES  (-2, 'Sonstige Angaben');
INSERT INTO ka_logbuch_art_tbl (id, beschreibung) VALUES  (-1, 'Keine Angabe');
INSERT INTO ka_logbuch_art_tbl (id, beschreibung) VALUES  (0, 'Automatischer Eintrag');

CREATE TABLE ka_logbuch_typ_tbl (
    id         SERIAL NOT NULL,
    beschreibung VARCHAR(256),
    CONSTRAINT ka_logbuch_typ_tbl_pkey PRIMARY KEY (id)
);

GRANT SELECT ON ka_logbuch_typ_tbl TO GROUP :cm_group;
GRANT SELECT ON ka_logbuch_typ_tbl TO GROUP :admin_group;

INSERT INTO ka_logbuch_typ_tbl (id, beschreibung) VALUES (-1, 'Keine Angabe');

-- ka_logbuch_tbl
--
-- Verwaltet Logbucheintraege zu Fallakten
--

CREATE TABLE ka_logbuch_tbl (
    id         SERIAL NOT NULL,
    master_id  int4 REFERENCES master_tbl(id)         ON DELETE CASCADE,
    uuid_id    CHAR(36),
    datum      TIMESTAMP,
    bearbeiter VARCHAR(256),
    art        int4 REFERENCES ka_logbuch_art_tbl(id) ON DELETE SET DEFAULT,
    typ        int4 REFERENCES ka_logbuch_typ_tbl(id) ON DELETE SET DEFAULT,
    kurz_notiz VARCHAR(256),
    notiz      text,
    dauer      interval,
    UNIQUE (uuid_id),
    CONSTRAINT ka_logbuch_tbl_pkey PRIMARY KEY (id)
);


--
-- Funktionen zum Anlegen und Loeschen von Logbucheintraegen
--
CREATE OR REPLACE FUNCTION create_logbuch_ds(mid integer, nuuid CHAR(36)) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
AS $$
DECLARE
    lid integer;
    editor_name varchar;
BEGIN
    IF NOT (isEditorOrStandin(mid) OR pg_has_role('admin','MEMBER')) THEN
        RAISE EXCEPTION 'DS ID not session_user ID';
    END IF;

    SELECT INTO editor_name nachname || ', ' || vorname from ka_benutzer_tbl WHERE id = getuserid();
    INSERT INTO ka_logbuch_tbl(master_id, datum, bearbeiter) VALUES (mid, now(), editor_name);

    SELECT INTO lid currval('ka_logbuch_tbl_id_seq');

    IF nuuid IS NOT NULL THEN
        UPDATE ka_logbuch_tbl SET uuid_id = nuuid WHERE id = lid;
    ELSE
        PERFORM set_uuid('ka_logbuch_tbl', lid);
    END IF;

    RETURN lid;
END;
$$;

REVOKE ALL    ON FUNCTION create_logbuch_ds(integer, CHAR(36)) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION create_logbuch_ds(integer, CHAR(36)) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION create_logbuch_ds(integer, CHAR(36)) TO GROUP :admin_group;

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

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


CREATE OR REPLACE FUNCTION delete_all_logbuch(mid integer) 
RETURNS void 
LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
AS $$
BEGIN
    DELETE FROM ka_logbuch_tbl WHERE master_id = mid;
END;
$$;

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


CREATE OR REPLACE FUNCTION anonymize_logbuch(mid integer) 
RETURNS void 
LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
AS $$
BEGIN
    -- DELETE FROM ka_logbuch_tbl WHERE master_id = mid;
    UPDATE ka_logbuch_tbl SET
        uuid_id    = NULL,
        bearbeiter = md5(lower(trim(both ' ' from coalesce(bearbeiter, '')))),
        kurz_notiz = NULL,
        notiz      = NULL
    WHERE master_id = mid;
END;
$$;

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


-- Wrapper Funktion die alle noetigen Funktionen zum Anonymisieren einer
-- Fallakte aufruft.
CREATE OR REPLACE FUNCTION anonymize_case_wrapper(mid integer)
RETURNS void AS
$$
BEGIN
	PERFORM anonymize_case(mid);
	PERFORM delete_all_fall_dokumente(mid);
	PERFORM delete_all_fall_termine(mid);
	PERFORM anonymize_logbuch(mid);
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

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

--
-- Views auf dem Logbuch
--

CREATE OR REPLACE VIEW ka_logbuch_tbl_view 
    AS SELECT
        ka_logbuch_tbl.id         AS id,
        ka_logbuch_tbl.uuid_id    AS uuid_id,
        ka_logbuch_tbl.master_id  AS master_id,
        ka_logbuch_tbl.datum      AS datum,
        ka_logbuch_tbl.bearbeiter AS bearbeiter,
        ka_logbuch_tbl.art        AS art,
        ka_logbuch_tbl.typ        AS typ,
        ka_logbuch_tbl.kurz_notiz AS kurz_notiz,
        ka_logbuch_tbl.notiz      AS notiz,
        ka_logbuch_tbl.dauer      AS dauer
    FROM ka_logbuch_tbl
    INNER JOIN master_tbl ON
        ka_logbuch_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_logbuch_update_rule
    AS ON UPDATE TO ka_logbuch_tbl_view 
	DO INSTEAD
        UPDATE ka_logbuch_tbl SET
            datum      = new.datum,
            bearbeiter = new.bearbeiter,
            art        = new.art,
            typ        = new.typ,
            kurz_notiz = new.kurz_notiz,
            notiz      = new.notiz,
            dauer      = new.dauer
	    WHERE ka_logbuch_tbl.id = new.id;

CREATE OR REPLACE RULE ka_logbuch_delete_rule 
    AS ON DELETE TO  ka_logbuch_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_logbuch_insert_rule
    AS ON INSERT TO ka_logbuch_tbl_view DO INSTEAD NOTHING;

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

-- 
-- TRIGGERS
-- 

CREATE OR REPLACE FUNCTION master_ds_created_logbook() RETURNS trigger AS $master_ds_created_logbook$
DECLARE
    lid integer;
BEGIN
    SELECT INTO lid create_logbuch_ds(NEW.id, NULL);
    UPDATE ka_logbuch_tbl_view
        SET art = 0, typ = -1, notiz = 'Fallakte angelegt.'
    WHERE
        id = lid;
    RETURN NEW;
END;
$master_ds_created_logbook$ LANGUAGE plpgsql;

CREATE TRIGGER master_ds_created_logbook_trigger AFTER
    INSERT ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE master_ds_created_logbook();


-- ka_logbuch_tbl
CREATE TRIGGER t_ka_logbuch_tbl_insert AFTER
    INSERT ON ka_logbuch_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_logbuch_tbl_update AFTER
    UPDATE ON ka_logbuch_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_logbuch_tbl_delete AFTER
    DELETE ON ka_logbuch_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t1_ka_logbuch_tbl_insert AFTER
    INSERT ON ka_logbuch_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_logbuch_tbl_update AFTER
    UPDATE ON ka_logbuch_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_logbuch_tbl_delete AFTER
    DELETE ON ka_logbuch_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

COMMIT;
