SET ROLE :adm_ka_owner;

BEGIN;

-- Allow users to create temporary tables (needed for evaluations)
GRANT TEMP ON DATABASE :db_name TO PUBLIC;

--
-- Init logging of DB updates.
--
CREATE TABLE db_history (
        id SERIAL PRIMARY KEY, 
        beschreibung VARCHAR(80), 
        zeitpunkt TIMESTAMP);
REVOKE ALL ON db_history FROM public;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('00_preconditions.sql', current_timestamp);

CREATE TABLE dbinfo (
    id INTEGER PRIMARY KEY NOT NULL,
    key   VARCHAR(128) NOT NULL,
    value VARCHAR(256) 
);

INSERT INTO dbinfo (id, key, value) VALUES (1, 'version', CURRENT_DATE);
INSERT INTO dbinfo (id, key, value) VALUES (2, 'datadump', NULL);

GRANT SELECT ON dbinfo TO PUBLIC;

--
-- anonymized_uuids
-- Liste von UUIDs, die bereits anonymisiert wurden. Die UUIDs
-- in den Akten selbst werden beim Anonymisieren aus
-- datenschutzrechtlichen Gruenden geloescht.
--
CREATE TABLE anonymized_uuids (
	id      serial   PRIMARY KEY,
    uuid_id CHAR(36) UNIQUE NOT NULL
);
REVOKE ALL ON db_history FROM public;

--
-- Testet, ob eine UUID schon anonmisiert wurde.
--
CREATE OR REPLACE FUNCTION is_uuid_anonymized(nuuid char(36))
    RETURNS BOOL AS
$$
DECLARE 
    anon BOOL;
BEGIN
    IF nuuid is NULL THEN
        RETURN TRUE;
    END IF;
    SELECT INTO anon TRUE FROM anonymized_uuids WHERE uuid_id = nuuid;
    IF anon THEN
        RETURN TRUE;
    END IF;
    RETURN FALSE;
END;
$$ LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER;

REVOKE ALL ON FUNCTION is_uuid_anonymized(char(36)) FROM public;
GRANT EXECUTE ON FUNCTION is_uuid_anonymized(char(36)) TO GROUP :admin_group;
GRANT EXECUTE ON FUNCTION is_uuid_anonymized(char(36)) TO GROUP :cm_group;

--
-- Wenn eine Akte anonymisiert ist, hat sie keine UUID mehr. Folglich
-- Kann man aus der anonymized_uuids auch nichts mehr loeschen.
-- Falls sich dort trotzdem ein gleicher Eintrag finden sollte, ist
-- dies eine potentielle Quelle fuer eine Inkonsistenz. 
--
CREATE OR REPLACE FUNCTION delete_anonymized_uuid() RETURNS TRIGGER AS $$
BEGIN
    -- Diese _sollte_ nichts loeschen!
    DELETE FROM anonymized_uuids WHERE uuid_id = OLD.uuid_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- PL/Python ist eine 'untrusted' Sprache
SET ROLE postgres;

-- Generiert eine UUID
--
CREATE OR REPLACE FUNCTION generate_uuidgen()
RETURNS   text AS
$BODY$
    from commands import getstatusoutput
    from random   import randint
    try:
        result = getstatusoutput("/usr/bin/uuid -v4")
        if result[0] != 0: raise Exception()
        return result[1].strip()
    except:
        count = 500
        while count > 0:
            nid = "%08x-%04x-%04x-%04x-%012x" % (
                randint(0, 1 <<  8*4),
                randint(0, 1 <<  4*4),
                randint(0, 1 <<  4*4),
                randint(0, 1 <<  4*4),
                randint(0, 1 << 12*4))
            if len(nid) == 36:
                return nid
            count -= 1
        raise Exception("UUID generator failed")
$BODY$
LANGUAGE 'plpythonu' VOLATILE;

REVOKE ALL ON FUNCTION generate_uuidgen() FROM public;
GRANT EXECUTE ON FUNCTION generate_uuidgen() TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION generate_uuidgen() TO GROUP :admin_group;
ALTER FUNCTION generate_uuidgen() OWNER TO :adm_ka_owner;

-- Ab hier als :adm_ka_owner
SET ROLE :adm_ka_owner;

--
-- Setzt in der Tabelle 't_name' in der Spalte mit der
-- ID 'id' das Feld 'uuid_id' mit einer UUID.
-- Bei Verletzung des Unique-Constraints wird bis
-- zu 500 mal neu gewuerfelt.
--
CREATE OR REPLACE FUNCTION set_uuid(t_name text, id int) 
RETURNS void AS 
$$
DECLARE
    xuuid_id char(36);
BEGIN
    FOR i IN 0..500 LOOP
        SELECT INTO xuuid_id generate_uuidgen();
        BEGIN
            EXECUTE 'UPDATE '           || t_name   || 
                    ' SET uuid_id = ''' || xuuid_id ||
                    ''' WHERE id = '    || id;
        EXCEPTION WHEN unique_violation THEN
            CONTINUE;
        END;
        RETURN;
    END LOOP;
    RAISE EXCEPTION 'set_uuid failed';
END;
$$ LANGUAGE plpgsql;
REVOKE ALL ON FUNCTION set_uuid(text, int) FROM public;

--
-- TRIGGERS --
--
CREATE OR REPLACE FUNCTION mpuls_log() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        RAISE LOG '% on % (id: %) by %', TG_OP, TG_TABLE_NAME, OLD.id, session_user;
        RETURN OLD;
    ELSE
        RAISE LOG '% on % (id: %) by %', TG_OP, TG_TABLE_NAME, NEW.id, session_user;
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;



COMMIT;
