SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('03_datamodel.sql', current_timestamp);

--
-- CREATE TABLES
--

CREATE TABLE phase_tbl (
    id SERIAL PRIMARY KEY NOT NULL,
    value VARCHAR(24) NOT NULL
);

CREATE TABLE master_tbl (
    id SERIAL PRIMARY KEY NOT NULL,
    uuid_id CHAR(36),
    bearbeiter_id int NOT NULL,
    einwilligung INTEGER DEFAULT -1,
    phase INTEGER DEFAULT -1,
    UNIQUE (uuid_id),
    FOREIGN KEY (phase) REFERENCES phase_tbl (id)
);

-- Benutzer Fallakten Transitionstabelle
ALTER TABLE nm_benutzer_master_tbl ADD CONSTRAINT has_group_fk FOREIGN KEY
(benutzer_id) REFERENCES ka_benutzergruppe_tbl (id) ON DELETE CASCADE;

ALTER TABLE nm_benutzer_master_tbl ADD CONSTRAINT has_master_fk FOREIGN KEY
(master_id) REFERENCES master_tbl (id) ON DELETE CASCADE;

-- Jede Fallakte muss einen Bearbeiter haben.
ALTER TABLE master_tbl ADD CONSTRAINT has_bearbeiter_id_fk FOREIGN KEY
(bearbeiter_id) REFERENCES ka_benutzer_tbl;

CREATE TABLE ka_status_tbl
(
	id serial NOT NULL,
	master_id int4 REFERENCES master_tbl(id) ON DELETE CASCADE,
	status    int4,
	zugriff   timestamp,
	erstellt  timestamp DEFAULT now(),
	anonymisiert timestamp,
	CONSTRAINT ka_status_tbl_pkey PRIMARY KEY (id)
);

-- ------ This is static code and therefore may be put into a separate file. -------

-- Used to track the structure changes of a case
-- DROP TABLE case_structure CASCADE;

CREATE TABLE case_structure (
    id        SERIAL PRIMARY KEY NOT NULL,
    master_id int4 NOT NULL REFERENCES master_tbl(id) ON DELETE CASCADE,
    modified  boolean,
    cache     TEXT
);

-- each time a new case is created create a new entry in the strcuture table

-- DROP TRIGGER case_insert_trigger ON master_tbl;
-- DROP FUNCTION case_insert_func();

CREATE OR REPLACE FUNCTION case_insert_func() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO case_structure (master_id, modified) VALUES (NEW.id, true);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER case_insert_trigger AFTER INSERT ON master_tbl
FOR EACH ROW EXECUTE PROCEDURE case_insert_func();

-- ------ end of static code -------
-- PLPython is an untrusted language. -> Need to be postgres.
SET ROLE postgres;

-- DROP FUNCTION compute_case_structure(int4);
CREATE OR REPLACE FUNCTION compute_case_structure(case_id int4) RETURNS TEXT
AS $$

class Node(object):

    def __init__(self, name, id=None, children=[]):
        self.name = name
        self.id = id
        self.children = children

    def to_json(self, out):
        out.append('{"name":"%s","id":%d' % (self.name, self.id))
        if self.children:
            out.append(',"children":[')
            first = True
            for child in self.children:
                if first:
                    first = False
                else:
                    out.append(",")
                child.to_json(out)
            out.append("]")
        out.append("}")

    def recursive_build(self, mid, children):
        select = "SELECT id FROM %s WHERE master_id = %d" % (self.name, mid)
        ids = [r['id'] for r in plpy.execute(select)]
        for id in ids:
            sub_children = []
            node = Node(self.name, id, sub_children)
            for child in self.children:
                child.recursive_build(id, sub_children)
            children.append(node)

TREE_STRUCTURE = Node('master_tbl', children = [])


children = []
tree = Node(TREE_STRUCTURE.name, case_id, children)
for child in TREE_STRUCTURE.children:
    child.recursive_build(case_id, children)
out = []
tree.to_json(out)
return "".join(out)

$$ LANGUAGE plpythonu EXTERNAL SECURITY DEFINER;
ALTER FUNCTION compute_case_structure(int4) OWNER TO :adm_ka_owner;

-- DROP FUNCTION get_case_structure(int4);
CREATE OR REPLACE FUNCTION get_case_structure(case_id int4) RETURNS TEXT AS $$

r = plpy.execute("SELECT modified, cache FROM case_structure "
                 "WHERE master_id = %d" % case_id, 1)

if not r:
    return ""
r = r[0]

if r['modified']: # need refresh
    structure = plpy.execute("SELECT compute_case_structure(%d) as s;"
                             % case_id, 1)[0]["s"]
    plan = plpy.prepare("UPDATE case_structure SET modified = false, "
                        "cache = $1 WHERE master_id = $2", ["text", "int4"])
    plpy.execute(plan, [structure, case_id])
    return structure

return r['cache']

$$ LANGUAGE plpythonu EXTERNAL SECURITY DEFINER;
ALTER FUNCTION get_case_structure(int4) OWNER TO :adm_ka_owner;

-- Continue as db-owner from here
SET ROLE :adm_ka_owner;

CREATE OR REPLACE FUNCTION create_master_tbl(nuuid char(36))
    RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER AS
$$
DECLARE
    standin integer;
    rid     integer;
BEGIN
    rid := create_master_ds(nuuid);
    IF nuuid IS NULL THEN
    END IF;
    RETURN rid;
END;
$$;

REVOKE ALL ON FUNCTION create_master_tbl(char(36)) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION create_master_tbl(char(36))
    TO GROUP :cm_group;

CREATE OR REPLACE FUNCTION delete_master_tbl(mid integer) RETURNS void
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
    st integer;
BEGIN
    SELECT INTO st s.status FROM master_tbl m JOIN ka_status_tbl s
        ON s.master_id = m.id WHERE m.id = mid;

    IF NOT st in (3, 5) THEN
        RAISE EXCEPTION 'case is not deletable';
    END IF;

    DELETE FROM master_tbl WHERE id = mid;
END;
$$;

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


CREATE OR REPLACE FUNCTION create_status_ds(mid integer)
    RETURNS VOID 
    LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER 
    AS $$
BEGIN
    INSERT INTO ka_status_tbl (master_id, zugriff, status) 
            VALUES (mid, now(), 1);
END;
$$;

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


INSERT INTO phase_tbl (id, value) VALUES (-1, 'Keine Angabe');

--
-- GRANTS ON TABLES
--
GRANT SELECT ON phase_tbl TO GROUP :cm_group;
GRANT SELECT ON phase_tbl TO GROUP :admin_group;
--
--
-- views for master table and repeat groups
--
CREATE OR REPLACE VIEW master_tbl_view
AS SELECT master_tbl.* FROM master_tbl
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 master_tbl_update_rule AS ON UPDATE TO master_tbl_view
DO INSTEAD UPDATE master_tbl SET
    phase = NEW.phase,
    einwilligung = NEW.einwilligung
WHERE id = NEW.id;

CREATE OR REPLACE RULE master_tbl_delete_rule AS ON DELETE TO master_tbl_view
    DO INSTEAD NOTHING;

CREATE OR REPLACE RULE master_tbl_insert_rule AS ON INSERT TO master_tbl_view
    DO INSTEAD NOTHING;

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

CREATE OR REPLACE  VIEW master_tbl_eval_total_view AS
    SELECT m.* FROM master_tbl m JOIN ka_status_tbl s
        ON m.id = s.master_id
        WHERE
            s.status <> 3 AND
            (m.bearbeiter_id = (getuserid())::integer)
            OR pg_has_role('admin','MEMBER');

GRANT SELECT ON master_tbl_eval_total_view TO :cm_group;
GRANT SELECT ON master_tbl_eval_total_view TO :admin_group;

-- --
-- -- Name: master_tbl_eval_incm_view; Type: VIEW; Schema: public; Owner: postgres
-- --
-- 
-- CREATE OR REPLACE  VIEW master_tbl_eval_incm_view AS
--     SELECT * FROM master_tbl_eval_total_view
--         WHERE phase IN (2,3,4,5);
-- 
-- GRANT SELECT ON public.master_tbl_eval_incm_view TO :cm_group;
-- GRANT SELECT ON public.master_tbl_eval_incm_view TO :admin_group;
-- 
-- CREATE OR REPLACE  VIEW master_tbl_incm_view AS
--     SELECT * FROM master_tbl_view
--         WHERE phase IN (2,3,4,5);
-- 
-- GRANT SELECT ON public.master_tbl_incm_view TO :cm_group;
-- GRANT SELECT ON public.master_tbl_incm_view TO :admin_group;

CREATE OR REPLACE VIEW master_tbl_anonym_view
AS SELECT
    id,
    uuid_id,
    bearbeiter_id,
    phase 
    FROM master_tbl_view;

CREATE OR REPLACE RULE master_tbl_anonym_delete_rule AS ON DELETE TO master_tbl_anonym_view
    DO INSTEAD NOTHING;

CREATE OR REPLACE RULE master_tbl_anonym_insert_rule AS ON INSERT TO master_tbl_anonym_view
    DO INSTEAD NOTHING;

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

--
-- Function to anonymize a case and all of its repeat groups
--
CREATE OR REPLACE FUNCTION anonymize_case(mid int)
RETURNS void AS
$$
BEGIN
    PERFORM anonymize_uuid(mid);
    -- anonymize master table
    UPDATE master_tbl_view SET
    	id = id
    WHERE id = mid;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

REVOKE ALL ON FUNCTION anonymize_case(int) FROM public;
GRANT EXECUTE ON FUNCTION anonymize_case(int) TO GROUP :admin_group;

--
-- Funktionen zum Anlegen neuer (Teile von) Fallakten
-- Diese sind nur fuer die Gruppe CM_KA aufrufbar (Admin_KA darf keine neuen 
-- Akten anlegen) und laufen mit den Berechtigungen des zentralen
-- administrativen Benutzers der KA
CREATE OR REPLACE FUNCTION create_master_ds(nuuid char(36)) 
    RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER AS 
$$
DECLARE
    standin integer;
    mid     integer;
BEGIN
    INSERT INTO master_tbl (bearbeiter_id) VALUES (getuserid());
    SELECT INTO standin vertreter from ka_benutzer_tbl WHERE id = getuserid();

    IF standin IS NOT NULL THEN 
        INSERT INTO nm_benutzer_master_tbl(benutzer_id, master_id) 
                VALUES (standin, currval('master_tbl_id_seq'));
    END IF;

    SELECT INTO mid currval('master_tbl_id_seq'); 

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

    PERFORM create_status_ds(mid);
    RETURN mid;
END;
$$;

REVOKE ALL ON FUNCTION create_master_ds(char(36)) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION create_master_ds(char(36))
    TO GROUP :cm_group;

--
-- Funktionen zum Loeschen einer Fallakte inklusive aller angehaengten 
-- Unterstuetzungsangebote, Dateien Termine etc.
-- Diese sind nur fuer die Gruppe ADMIN_KA aufrufbar (CM_KA sollen keine Akten
-- loeschen koennen) und laufen mit den Berechtigungen des zentralen
-- administrativen Benutzers der KA

CREATE OR REPLACE FUNCTION delete_master_ds(mid integer) RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
    st integer;
BEGIN
    SELECT INTO st s.status FROM master_tbl m JOIN ka_status_tbl s
        ON s.master_id = m.id WHERE m.id = mid;

    IF NOT st in (3, 5) THEN
        RAISE EXCEPTION 'case is not deletable';
    END IF;

    DELETE FROM master_tbl WHERE id = mid;
END;
$$ ;

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

--
-- Funktionen zum Hinzufuegen und Loeschen der Benutzer in die
-- Benutzer-Fallakten Transitionstabelle, sowie zum Setzen des Bearbeiters
-- einer Fallakte.
--

CREATE OR REPLACE FUNCTION create_nm_benutzer_master_ds ( userid integer, 
    masterid integer ) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    INSERT INTO nm_benutzer_master_tbl (benutzer_id, master_id) 
        VALUES (userid, masterid);
    RETURN currval('nm_benutzer_master_tbl_id_seq');
END;
$$ ;

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


CREATE OR REPLACE FUNCTION delete_nm_benutzer_master_ds ( userid integer,
    masterid integer ) RETURNS VOID 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    DELETE FROM nm_benutzer_master_tbl 
            WHERE master_id = masterid AND benutzer_id = userid; 
END;
$$ ;

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

-- Funktionen zu Erstellen und Loeschen neuer Eintraeger in der
-- nm_benutzer_gruppe Tabelle

CREATE OR REPLACE FUNCTION create_nm_benutzer_gruppe_ds(bid integer, gid integer) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    INSERT INTO nm_benutzer_gruppe_tbl (benutzer_id, gruppe_id) VALUES (bid, gid);
    RETURN currval('nm_benutzer_gruppe_tbl_id_seq');
END;
$$ ;
REVOKE ALL ON FUNCTION create_nm_benutzer_gruppe_ds( integer, integer ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION create_nm_benutzer_gruppe_ds( integer, integer ) TO GROUP :admin_group;

CREATE OR REPLACE FUNCTION delete_nm_benutzer_gruppe_ds( bid integer, gid integer) RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    DELETE FROM nm_benutzer_gruppe_tbl WHERE gruppe_id = gid AND benutzer_id = bid;
END;
$$ ;
REVOKE ALL ON FUNCTION delete_nm_benutzer_gruppe_ds( integer, integer ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION delete_nm_benutzer_gruppe_ds( integer, integer ) TO GROUP :admin_group;


CREATE OR REPLACE FUNCTION set_case_editor (userid integer, masterid integer) 
    RETURNS VOID 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    UPDATE master_tbl set bearbeiter_id = userid WHERE id = masterid;
END;
$$ ;

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

--
-- isEditorOrStandin(integer)
-- Liefert TRUE wenn der Benutzer fuer die Fallakte 
-- a) Bearbeiter oder
-- b) Vertreter ist
-- Sonst FALSE
--

CREATE OR REPLACE FUNCTION isEditorOrStandin(mid integer)
    RETURNS boolean 
    LANGUAGE 'plpgsql' STABLE
    AS $$
DECLARE
    bid integer;
    isAllowed integer;
BEGIN
    SELECT bearbeiter_id into bid from master_tbl_view WHERE id = mid;
    IF bid = getuserid() THEN
        RETURN TRUE;
    ELSE 
        SELECT INTO isAllowed benutzer_id FROM 
        nm_benutzer_master_tbl_view 
        WHERE master_id = mid 
        AND benutzer_id IN (
                SELECT gruppe_id FROM nm_benutzer_gruppe_tbl_view 
                WHERE benutzer_id = getuserid()
                );
    END IF;
    RETURN FOUND;
END;
$$;

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

-- Funktionen zum Setzen der Einverstaendniserkärung 
CREATE OR REPLACE FUNCTION set_ee_status(mid integer, status integer) 
    RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER AS 
$$
BEGIN
    UPDATE master_tbl_view SET einwilligung = status WHERE id = mid;
END;
$$;

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

--
-- Loescht die UUID in einer Akte und traegt sie in die Liste der
-- anonymisierten UUIDs ein.
--
CREATE OR REPLACE FUNCTION anonymize_uuid(mid int)
RETURNS void AS
$$
DECLARE
    nuuid char(36);
BEGIN
    SELECT INTO nuuid uuid_id FROM master_tbl_view WHERE id = mid;
    IF NOT is_uuid_anonymized(nuuid) THEN
        INSERT INTO anonymized_uuids (uuid_id) VALUES (nuuid);
    END IF;
    UPDATE master_tbl SET uuid_id = NULL WHERE id = mid;
END;
$$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

REVOKE ALL ON FUNCTION anonymize_uuid(int) FROM public;
GRANT EXECUTE ON FUNCTION anonymize_uuid(int) TO GROUP :admin_group;

--
-- VIEWS
--

-- 
-- Sicht auf die Benutzer-Fallakte Transitionstabelle
--

CREATE OR REPLACE VIEW nm_benutzer_master_tbl_view AS 
SELECT nm_benutzer_master_tbl.id, nm_benutzer_master_tbl.benutzer_id,
nm_benutzer_master_tbl.master_id 
FROM nm_benutzer_master_tbl;

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

CREATE OR REPLACE RULE nm_benutzer_master_delete_rule AS ON DELETE TO 
nm_benutzer_master_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_master_insert_rule AS ON INSERT TO
nm_benutzer_master_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_master_update_rule AS ON UPDATE TO
nm_benutzer_master_tbl_view DO INSTEAD NOTHING;

-- 
-- Sicht auf die Benutzer-Gruppen Transitionstabelle
--

CREATE OR REPLACE VIEW nm_benutzer_gruppe_tbl_view AS 
SELECT nm_benutzer_gruppe_tbl.id, nm_benutzer_gruppe_tbl.benutzer_id,
nm_benutzer_gruppe_tbl.gruppe_id 
FROM nm_benutzer_gruppe_tbl;

CREATE OR REPLACE RULE nm_benutzer_gruppe_delete_rule AS ON DELETE TO 
nm_benutzer_gruppe_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_gruppe_insert_rule AS ON INSERT TO
nm_benutzer_gruppe_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_gruppe_update_rule AS ON UPDATE TO
nm_benutzer_gruppe_tbl_view DO INSTEAD NOTHING;

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

-- 
-- Sicht auf die Benutzer-Fallakte Transitionstabelle
--

CREATE OR REPLACE VIEW nm_benutzer_master_tbl_view AS 
SELECT nm_benutzer_master_tbl.id, nm_benutzer_master_tbl.benutzer_id,
nm_benutzer_master_tbl.master_id 
FROM nm_benutzer_master_tbl;

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

CREATE OR REPLACE RULE nm_benutzer_master_delete_rule AS ON DELETE TO 
nm_benutzer_master_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_master_insert_rule AS ON INSERT TO
nm_benutzer_master_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_master_update_rule AS ON UPDATE TO
nm_benutzer_master_tbl_view DO INSTEAD NOTHING;

-- 
-- Sicht auf die Benutzer-Gruppen Transitionstabelle
--

CREATE OR REPLACE VIEW nm_benutzer_gruppe_tbl_view AS 
SELECT nm_benutzer_gruppe_tbl.id, nm_benutzer_gruppe_tbl.benutzer_id,
nm_benutzer_gruppe_tbl.gruppe_id 
FROM nm_benutzer_gruppe_tbl;

CREATE OR REPLACE RULE nm_benutzer_gruppe_delete_rule AS ON DELETE TO 
nm_benutzer_gruppe_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_gruppe_insert_rule AS ON INSERT TO
nm_benutzer_gruppe_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE nm_benutzer_gruppe_update_rule AS ON UPDATE TO
nm_benutzer_gruppe_tbl_view DO INSTEAD NOTHING;

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

-- Sicht auf die KA Status Tabelle 

CREATE OR REPLACE VIEW ka_status_tbl_view AS 
SELECT
	ka_status_tbl.id,
	ka_status_tbl.master_id,
	ka_status_tbl.status, 
	ka_status_tbl.zugriff,
	ka_status_tbl.erstellt,
	ka_status_tbl.anonymisiert
FROM ka_status_tbl;

CREATE OR REPLACE RULE ka_status_delete_rule AS ON DELETE TO 
ka_status_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_status_insert_rule AS ON INSERT TO 
ka_status_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_status_update_rule AS ON UPDATE TO ka_status_tbl_view 
	DO INSTEAD UPDATE ka_status_tbl SET
	status  = new.status,
	zugriff = new.zugriff,
	anonymisiert = new.anonymisiert
WHERE id = new.id;

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


-- 
-- TRIGGERS
--

--
-- Automatisches Setzen eines Zeitstempels in der ka_status_tbl
-- Diese Trigger sorgen dafür, dass bei jeder Bearbeitung der Fallakten der
-- Zeitpunkt der Bearbeitung gespeichert wird.
--
CREATE OR REPLACE FUNCTION set_lastaccess_time( masterid integer ) 
RETURNS void AS 
$$
BEGIN
    UPDATE ka_status_tbl_view set zugriff = now() WHERE master_id = masterid;  
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION master_lastaccess_log() RETURNS trigger AS $master_lastaccess_log$
BEGIN
    EXECUTE set_lastaccess_time(NEW.id);
    RETURN NEW;
END;
$master_lastaccess_log$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION status_lastaccess_log() RETURNS trigger AS $status_lastaccess_log$
BEGIN
IF NEW.status <> OLD.status AND OLD.zugriff IS NOT NULL THEN
    EXECUTE set_lastaccess_time(NEW.master_id);
END IF;
RETURN NEW;
END;
$status_lastaccess_log$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION rg_lastaccess_log() RETURNS trigger AS $rg_lastaccess_log$
BEGIN
IF TG_OP = 'DELETE' THEN
    EXECUTE set_lastaccess_time(OLD.master_id);
    RETURN OLD;
ELSE
    EXECUTE set_lastaccess_time(NEW.master_id);
    RETURN NEW;
END IF;
END;
$rg_lastaccess_log$ LANGUAGE plpgsql;

-- Trigger, der nach Uebertragung der Fallakte an einen anderen
-- Bearbeiter den alten und neuen Bearbeiter eintraegt in das Logbuch eintraegt.
--
CREATE OR REPLACE FUNCTION master_ds_editor_changed() RETURNS trigger AS $master_ds_editor_changed$
DECLARE
    lid      integer;
    old_user RECORD;
    new_user RECORD;
BEGIN
    IF OLD.bearbeiter_id <> NEW.bearbeiter_id THEN

        SELECT INTO old_user * FROM ka_benutzer_tbl WHERE id = OLD.bearbeiter_id;
        SELECT INTO new_user * FROM ka_benutzer_tbl WHERE id = NEW.bearbeiter_id;

        SELECT INTO lid create_logbuch_ds(NEW.id, NULL);

        UPDATE ka_logbuch_tbl_view
            SET art = 0, 
            notiz = 'Fallakte übertragen von ' 
                    || coalesce(old_user.nachname, '-/-') || ', '
                    || coalesce(old_user.vorname,  '-/-') || ' an '
                    || coalesce(new_user.nachname, '-/-') || ', '
                    || coalesce(new_user.vorname,  '-/-')
        WHERE
            id = lid;
    END IF;
    RETURN NEW;
END;
$master_ds_editor_changed$ LANGUAGE plpgsql;


CREATE TRIGGER t_delete_anonymized_uuid 
    AFTER DELETE ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE delete_anonymized_uuid();

CREATE TRIGGER t_master_tbl_insert AFTER
    INSERT ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log(); 

CREATE TRIGGER t_master_tbl_update AFTER
    UPDATE ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_master_tbl_delete AFTER
    DELETE ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t1_master_tbl_update AFTER
    UPDATE ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE master_lastaccess_log();

CREATE TRIGGER t1_nm_benutzer_master_tbl_insert AFTER
    INSERT ON nm_benutzer_master_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_nm_benutzer_master_tbl_update AFTER
    UPDATE ON nm_benutzer_master_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_nm_benutzer_master_tbl_delete AFTER
    DELETE ON nm_benutzer_master_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_status_tbl_update AFTER
    UPDATE ON ka_status_tbl
    FOR EACH ROW EXECUTE PROCEDURE status_lastaccess_log();

CREATE TRIGGER master_ds_editor_changed_trigger AFTER
    UPDATE ON master_tbl
    FOR EACH ROW EXECUTE PROCEDURE master_ds_editor_changed();

COMMIT;
