SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('06_appointments.sql', current_timestamp);

-- Speichert grundlegende Angaben zu einem Termin. Weitergehende Angaben
-- sollten in einer extra Tabelle gespeichert werden.
CREATE TABLE ka_termine_tbl
(
	id serial NOT NULL,
	master_id int4 REFERENCES master_tbl(id) ON DELETE CASCADE,
	sdatum timestamp,
	edatum timestamp,
	name varchar(128),
	beschreibung text,
	art int4 DEFAULT 0, 
	CONSTRAINT ka_termine_tbl_pkey PRIMARY KEY (id)
);

--
-- Funktionen zum Erstellen und Löschen von Terminen
--

CREATE OR REPLACE FUNCTION create_fall_termin_ds(mid integer) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    IF NOT isEditorOrStandin(mid) THEN
        RAISE EXCEPTION 'DS ID not session_user ID';
    END IF;
    INSERT INTO ka_termine_tbl(master_id) VALUES (mid);
    RETURN currval('ka_termine_tbl_id_seq');
END;
$$ ;

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


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

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

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

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

-- Globale Dokumente 

CREATE OR REPLACE FUNCTION create_global_termin_ds() RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    INSERT INTO ka_termine_tbl (master_id) VALUES (NULL);
    RETURN currval('ka_termine_tbl_id_seq');
END;
$$ ;

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


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

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

CREATE OR REPLACE FUNCTION getlastcaseappointment(mid int4)
	RETURNS "timestamp" AS
$BODY$
DECLARE
	maxdate timestamp;
BEGIN
	SELECT INTO maxdate max(date::timestamp) FROM (
	SELECT zugriff as date from 
	ka_status_tbl_view
	WHERE master_id = mid
	UNION
	SELECT max(sdatum) as date from 
	ka_fall_termine_tbl_view
	WHERE master_id = mid
	UNION
	SELECT max(edatum) as date from 
	ka_fall_termine_tbl_view
	WHERE master_id = mid
	) as test;
	RETURN maxdate;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
REVOKE ALL ON FUNCTION getlastcaseappointment(int) FROM public;
GRANT EXECUTE ON FUNCTION getlastcaseappointment(int)  TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION getlastcaseappointment(int)  TO GROUP :admin_group;

-- 
-- Sicht auf die Termine Tabelle 
--

CREATE OR REPLACE VIEW ka_global_termine_tbl_view AS 
SELECT ka_termine_tbl.id, ka_termine_tbl.master_id, ka_termine_tbl.sdatum, 
ka_termine_tbl.edatum, ka_termine_tbl.name, ka_termine_tbl.beschreibung, ka_termine_tbl.art
FROM ka_termine_tbl WHERE ka_termine_tbl.master_id is NULL;

CREATE OR REPLACE RULE ka_global_termine_delete_rule AS ON DELETE TO 
ka_global_termine_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_global_termine_insert_rule AS ON INSERT TO 
ka_global_termine_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_global_termine_update_rule AS ON UPDATE TO ka_global_termine_tbl_view 
	DO INSTEAD  UPDATE ka_termine_tbl SET 
	sdatum = new.sdatum, 
	edatum = new.edatum, 
	name = new.name,
	beschreibung = new.beschreibung,
	art = new.art
	WHERE ka_termine_tbl.id = new.id;

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

CREATE OR REPLACE VIEW ka_fall_termine_tbl_view AS 
SELECT ka_termine_tbl.id, ka_termine_tbl.master_id,
ka_termine_tbl.sdatum, ka_termine_tbl.edatum,
ka_termine_tbl.name, ka_termine_tbl.beschreibung, ka_termine_tbl.art FROM ka_termine_tbl 
INNER JOIN master_tbl ON
ka_termine_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_termine_delete_rule AS ON DELETE TO 
ka_fall_termine_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_fall_termine_insert_rule AS ON INSERT TO 
ka_fall_termine_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_fall_termine_update_rule AS ON UPDATE TO ka_fall_termine_tbl_view 
	DO INSTEAD  UPDATE ka_termine_tbl SET 
	sdatum = new.sdatum, 
	edatum = new.edatum, 
	name = new.name,
	beschreibung = new.beschreibung,
	art = new.art
	WHERE ka_termine_tbl.id = new.id;

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

-- 
-- TRIGGERS
--
-- ka_termine_tbl
CREATE TRIGGER t_ka_termine_tbl_insert AFTER
    INSERT ON ka_termine_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_termine_tbl_update AFTER
    UPDATE ON ka_termine_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_termine_tbl_delete AFTER
    DELETE ON ka_termine_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t1_ka_termine_tbl_insert AFTER
    INSERT ON ka_termine_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_termine_tbl_update AFTER
    UPDATE ON ka_termine_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();

CREATE TRIGGER t1_ka_termine_tbl_delete AFTER
    DELETE ON ka_termine_tbl
    FOR EACH ROW EXECUTE PROCEDURE rg_lastaccess_log();



COMMIT;
