SET ROLE postgres;

BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('02_user.sql', current_timestamp);

ALTER FUNCTION mpuls_revoke_connect( varchar, varchar ) OWNER TO :adm_ka_owner;
GRANT EXECUTE ON FUNCTION  mpuls_revoke_connect( varchar, varchar ) 
    TO GROUP mpuls_role_adm;

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO :cm_group;
GRANT USAGE ON SCHEMA public TO :admin_group;
GRANT USAGE ON SCHEMA public TO mpuls_role_adm;
GRANT ALL ON SCHEMA public TO :adm_ka_owner;

COMMIT;

SET ROLE :adm_ka_owner;

BEGIN;

-- ka_benutzergruppe_tbl
-- Tabelle fuer die Benutzergruppen. Jeder Benutzer erhaelt eine
-- Benutzergruppe in der er exklusiv Mitglied ist. 
-- Weitere Benutzergruppen können angelegt werden, die weitere Nutzer
-- enthalten können

CREATE TABLE ka_benutzergruppe_tbl
(
	id serial NOT NULL,
	name varchar(128) NOT NULL,
	CONSTRAINT ka_benutzergruppe_tbl_pkey PRIMARY KEY (id)
);

-- Benutzertabelle
-- Diese Tabelle enthält erweiterte Angaben zu den Nutzern der KA Datenbank.
-- Das Feld 'login' enthält hier den vollen Login-Namen, so wie er clusterweit
-- in der Datenbank bekannt ist (ka_<agentur_<loginname>)
-- 
CREATE TABLE ka_benutzer_tbl
(
	id serial NOT NULL,
	login varchar(64) NOT NULL,
	vorname varchar(64),
	nachname varchar(64),
	telefon varchar(32),
	raumnummer varchar(16),
	filiale varchar(64),
	aktiviert bool DEFAULT true,
	passwort_aendern bool DEFAULT true,
	last_login timestamp,
	gid       int4 REFERENCES ka_benutzergruppe_tbl(id) ON DELETE CASCADE,
	vertreter int4 REFERENCES ka_benutzergruppe_tbl(id) ON DELETE SET DEFAULT,
	CONSTRAINT ka_benutzer_tbl_pkey PRIMARY KEY (id)
); 

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

--
-- Benutzer-Fallakte Transitionstabelle 
-- Diese Tabelle verbindet Nutzer mit Fallakten, und legt auf diese Weise
-- fest welche Benutzer ausser dem initialen Bearbeiter oder dem KA-Admin 
-- Zugriff auf die Fallakte haben.
-- 

CREATE TABLE nm_benutzer_master_tbl (
    id serial PRIMARY KEY,
    benutzer_id int4,
    master_id int4
);

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

-- ka_nm_benutzer_gruppe_tbl
-- Tabelle zur Zuordnung zwischen Benutzern und Benutzergruppen.
CREATE TABLE nm_benutzer_gruppe_tbl
(
	id serial NOT NULL,
	gruppe_id int4 REFERENCES ka_benutzergruppe_tbl(id) ON DELETE CASCADE,
	benutzer_id int4 REFERENCES ka_benutzer_tbl(id) ON DELETE CASCADE,
	CONSTRAINT nm_benutzer_gruppe_tbl_pkey PRIMARY KEY (id)
);

--
-- getuserid()
-- holt id des current_user aus der ka_benutzer_tbl
--
CREATE OR REPLACE FUNCTION getuserid() RETURNS integer
    LANGUAGE 'plpgsql' STABLE
    AS $$
DECLARE
myid integer;
BEGIN
SELECT INTO myid id FROM ka_benutzer_tbl WHERE login = session_user;
RETURN myid;
END;
$$;

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

-- ------------------------------------------------------------------------
-- Anlegen einer Rolle in der DB einer KA:
-- - Ueber mpuls_create_role() Datenbank-Rolle anlegen und 
-- - dann Eintragungen in ka_benutzer_tbl machen.
CREATE OR REPLACE FUNCTION ka_create_role (
        agency          varchar(52),
        grouprole       varchar(64),
        loginname       varchar(64),
        firstname       varchar(64),
        surname         varchar(64),
        phone           varchar(64),
        room            varchar(64),
        branchoffice    varchar(64),
        activated       boolean,
        setpassword     boolean
    ) RETURNS varchar(9) 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
    newrole    RECORD;
    groupid    integer;
    userid     integer;
BEGIN
    SELECT INTO newrole * FROM mpuls_create_role(loginname, grouprole, agency)
        AS (db_name varchar(64), db_user varchar(64), pw varchar(9));
    EXECUTE 'GRANT CONNECT ON DATABASE ' || newrole.db_name 
                || ' TO ' || newrole.db_user; 
    INSERT INTO ka_benutzer_tbl ( login, vorname, nachname,
        telefon, raumnummer, filiale,
        aktiviert, passwort_aendern, gid) 
        VALUES
        (newrole.db_user, firstname, surname,
        phone, room, branchoffice,
        activated, setpassword, NULL);
    SELECT INTO userid currval('ka_benutzer_tbl_id_seq');
    SELECT INTO groupid create_benutzergruppe_ds(surname ||', '|| firstname ||' ('||loginname||')');
    UPDATE ka_benutzer_tbl SET gid = groupid WHERE id = userid;
    INSERT INTO nm_benutzer_gruppe_tbl (benutzer_id, gruppe_id) VALUES (userid, groupid);

    RETURN newrole.pw::varchar(9);
END;
$$ ;

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

CREATE OR REPLACE FUNCTION ka_delete_role (
        loginname       varchar(64),
        agency          varchar(52)
    ) RETURNS VOID 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
    db_user  varchar(128);
    groupid   integer;
BEGIN
    db_user := quote_ident('ka_' || agency || '_' || loginname);
    SELECT INTO groupid gid FROM ka_benutzer_tbl WHERE login = db_user;
    DELETE FROM ka_benutzer_tbl WHERE login = db_user;
    DELETE FROM ka_benutzergruppe_tbl WHERE id = groupid;
    PERFORM mpuls_drop_role(loginname, agency);
END;
$$ ;

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


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

CREATE OR REPLACE FUNCTION create_benutzergruppe_ds(gname varchar) RETURNS integer
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    INSERT INTO ka_benutzergruppe_tbl (name) VALUES (gname);
    RETURN currval('ka_benutzergruppe_tbl_id_seq');
END;
$$ ;

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

CREATE OR REPLACE FUNCTION delete_benutzergruppe_ds(gid integer) RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
DECLARE
BEGIN
    DELETE FROM ka_benutzergruppe_tbl WHERE id = gid;
END;
$$ ;

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

--
-- Voreinstellung Vertretungsregeln setzen
--
CREATE OR REPLACE FUNCTION ka_set_standin (standin_id integer, user_id integer)
    RETURNS VOID 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    UPDATE ka_benutzer_tbl_view set vertreter = standin_id WHERE id = user_id; 
END;
$$ ;

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


--
-- Funktion um die Flagge zurueckzusetzten, dass der Benutzer sein Passwort bei
-- der Neuanmeldung neu setzen muss. Die Funktion muss auch fuer die Benutzer
-- zugaenglich sein.
-- 
CREATE OR REPLACE FUNCTION ka_unset_passrefresh_flag ( userlogin varchar ) 
    RETURNS VOID 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    UPDATE ka_benutzer_tbl_view set passwort_aendern = False 
            WHERE login = userlogin; 
END;
$$ ;

REVOKE ALL ON FUNCTION ka_unset_passrefresh_flag(varchar) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION ka_unset_passrefresh_flag ( userlogin varchar ) 
                TO :admin_group;
GRANT EXECUTE ON FUNCTION ka_unset_passrefresh_flag ( userlogin varchar ) 
                TO :cm_group;

-- 
-- Funktion zum Setzen des Benutzerpassworts
--

CREATE OR REPLACE FUNCTION ka_set_password(loginname varchar, pw varchar)
    RETURNS text 
    LANGUAGE 'plpgsql'
    AS $$
BEGIN
    EXECUTE 'ALTER USER ' || loginname || ' WITH PASSWORD ' 
              || quote_literal(pw);
    EXECUTE ka_unset_passrefresh_flag(loginname);
          RETURN pw::text;
    RETURN ''::text;
END;
$$ ;

REVOKE ALL ON FUNCTION ka_set_password(varchar, varchar) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION ka_set_password(varchar, varchar) 
                TO :admin_group;
GRANT EXECUTE ON FUNCTION ka_set_password(varchar, varchar)    
                TO :cm_group;



CREATE OR REPLACE FUNCTION getrole( user_id integer ) RETURNS varchar 
    LANGUAGE 'plpgsql' STABLE SECURITY DEFINER 
    AS $$
DECLARE
    role varchar;
    oid integer;
BEGIN
    SELECT p.usesysid INTO oid FROM pg_user p JOIN ka_benutzer_tbl b 
        ON b.login = p.usename 
        WHERE b.id = user_id; 
    SELECT groname INTO role FROM pg_group WHERE oid = ANY (grolist);
    RETURN role;
END;
$$;

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

--
-- getuserid()
-- holt id des current_user aus der ka_benutzer_tbl
--
-- Funktion wurde in die 00precond.sql verschoben

--
-- getoid()
-- OID des current_user
--
CREATE OR REPLACE FUNCTION getoid() RETURNS integer
    LANGUAGE 'plpgsql' STABLE
    AS $$
DECLARE
    myoid OID;
BEGIN
    SELECT INTO myoid oid FROM pg_roles WHERE rolname = current_user;
    RETURN myoid;
END;
$$;

REVOKE ALL ON FUNCTION getoid() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION getoid() TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION getoid() 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;

CREATE OR REPLACE FUNCTION getrole( user_id integer ) RETURNS varchar 
    LANGUAGE 'plpgsql' STABLE SECURITY DEFINER 
    AS $$
DECLARE
    role varchar;
    oid integer;
BEGIN
    SELECT p.usesysid INTO oid FROM pg_user p JOIN ka_benutzer_tbl b 
        ON b.login = p.usename 
        WHERE b.id = user_id; 
    SELECT groname INTO role FROM pg_group WHERE oid = ANY (grolist);
    RETURN role;
END;
$$;

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

CREATE OR REPLACE FUNCTION set_last_logintime(userid integer) 
    RETURNS void 
    LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER
    AS $$
BEGIN
    UPDATE ka_benutzer_tbl_view SET last_login = now() WHERE id = userid;
END;
$$ ;

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

-- 
-- Sicht auf die Benutzerdatenbank
--

CREATE OR REPLACE VIEW ka_benutzer_tbl_view AS 
SELECT ka_benutzer_tbl.id, ka_benutzer_tbl.vorname, ka_benutzer_tbl.nachname,
ka_benutzer_tbl.telefon, ka_benutzer_tbl.aktiviert,
ka_benutzer_tbl.passwort_aendern, ka_benutzer_tbl.login,
ka_benutzer_tbl.raumnummer, ka_benutzer_tbl.filiale, p.usesysid,
ka_benutzer_tbl.vertreter, getrole(ka_benutzer_tbl.id) as rolle,
ka_benutzer_tbl.gid,
ka_benutzer_tbl.last_login
FROM ka_benutzer_tbl JOIN pg_user p
ON ka_benutzer_tbl.login = p.usename;

CREATE OR REPLACE RULE ka_benutzer_update_rule AS ON UPDATE TO ka_benutzer_tbl_view 
	DO INSTEAD  UPDATE ka_benutzer_tbl SET 
	vorname = new.vorname, 
	nachname = new.nachname, 
	telefon = new.telefon, 
	aktiviert = new.aktiviert, 
	raumnummer = new.raumnummer, 
	filiale = new.filiale, 
	passwort_aendern = new.passwort_aendern, 
	vertreter = new.vertreter,
	gid = new.gid,
	last_login = new.last_login
	WHERE ka_benutzer_tbl.id = new.id;

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

CREATE OR REPLACE RULE ka_benutzer_delete_rule AS ON DELETE TO ka_benutzer_tbl_view 
    DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_benutzer_insert_rule AS ON INSERT TO ka_benutzer_tbl_view
    DO INSTEAD NOTHING;

-- Sichten auf die Benutzergruppe

CREATE OR REPLACE VIEW ka_benutzergruppe_tbl_view AS 
SELECT g.id, coalesce(b.nachname ||', '|| b.vorname, g.name) AS name FROM ka_benutzer_tbl b RIGHT JOIN ka_benutzergruppe_tbl g ON g.id = b.gid;

CREATE OR REPLACE RULE ka_benutzergruppe_delete_rule AS ON DELETE TO 
ka_benutzergruppe_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_benutzergruppe_insert_rule AS ON INSERT TO 
ka_benutzergruppe_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_benutzergruppe_update_rule AS ON UPDATE TO ka_benutzergruppe_tbl_view 
	DO INSTEAD  UPDATE ka_benutzergruppe_tbl SET 
	name = new.name 
	WHERE ka_benutzergruppe_tbl.id = new.id;

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

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

CREATE TRIGGER t_ka_benutzer_tbl_update AFTER
    UPDATE ON ka_benutzer_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();

CREATE TRIGGER t_ka_benutzer_tbl_delete AFTER
    DELETE ON ka_benutzer_tbl
    FOR EACH ROW EXECUTE PROCEDURE mpuls_log();



COMMIT;
