SGJP / Kuznia

-- DEFAULTS

ALTER TABLE history
ALTER timestamp_ SET DEFAULT clock_timestamp();

ALTER TABLE history_historyframe
ALTER user_id SET DEFAULT current_setting('var.user_id' :: TEXT) :: INTEGER,
ALTER transaction_began SET DEFAULT transaction_timestamp();

-- make_history_

CREATE OR REPLACE FUNCTION make_history_()
    RETURNS TRIGGER
LANGUAGE plpgsql
AS
    $BODY$

    DECLARE
        ri                         RECORD;
        oldValue                   TEXT;
        newValue                   TEXT;
        isColumnSignificant        BOOLEAN;
        isValueModified            BOOLEAN;
        lexemeId                   INTEGER;
        patternId                  INTEGER;
        temp                       RECORD;
        userId                     TEXT;

        operation                  VARCHAR;
        table_oid                  INTEGER;
        table_name1                VARCHAR;
        column_name1               VARCHAR;
        ordinal_position_of_column INTEGER;
        old_value                  TEXT;
        new_value                  TEXT;
        frameId                    INTEGER;
    BEGIN
        IF (TG_OP = 'DELETE')
        THEN
            temp := OLD;
        ELSE
            temp := NEW;
        END IF;

        SELECT
            current_setting('var.user_id')
        INTO userId;
        IF (userId = '0')
        THEN
            RETURN temp;
        END IF;

        IF (TG_TABLE_NAME = 'leksemy')
        THEN
            lexemeId := temp.id;
        ELSIF (TG_TABLE_NAME IN ('odmieniasie', 'leksemy_w_slownikach'))
            THEN
                lexemeId := temp.l_id;
        ELSIF (TG_TABLE_NAME IN
               ('kwalifikatory_leksemow', 'dictionary_lexemeav', 'dictionary_lexemecv'))
            THEN
                lexemeId := temp.lexeme_id;
        ELSIF (TG_TABLE_NAME = 'odsylacze')
            THEN
                lexemeId := temp.l_id_od;
        ELSIF (TG_TABLE_NAME = 'kwalifikatory_odmieniasiow')
            THEN
                SELECT
                    l_id
                INTO lexemeId
                FROM odmieniasie o
                WHERE o.id = temp.inflection_id;
        END IF;
        IF (TG_TABLE_NAME = 'wzory')
        THEN
            patternId := temp.id;
        ELSIF (TG_TABLE_NAME = 'zakonczenia')
            THEN
                patternId := temp.w_id;
        ELSIF (TG_TABLE_NAME = 'kwalifikatory_zakonczen')
            THEN
                SELECT
                    w_id
                INTO patternId
                FROM zakonczenia z
                WHERE z.id = temp.ending_id;
        ELSIF (TG_TABLE_NAME = 'wzory_genders')
            THEN
                patternId := temp.pattern_id;
        END IF;

        FOR ri IN
        SELECT
            ordinal_position,
            column_name,
            data_type
        FROM information_schema.columns
        WHERE
            table_schema = quote_ident(TG_TABLE_SCHEMA)
            AND table_name = quote_ident(TG_TABLE_NAME)
        ORDER BY ordinal_position
        LOOP
-- NEW value
        IF (TG_OP = 'DELETE')
        THEN
            newValue := '' :: VARCHAR;
        ELSE
            EXECUTE 'SELECT ($1).' || ri.column_name || '::text'
            INTO newValue
            USING NEW;
        END IF;
-- OLD value
        IF (TG_OP = 'INSERT')
        THEN
            oldValue := '' :: VARCHAR;
        ELSE
            EXECUTE 'SELECT ($1).' || ri.column_name || '::text'
            INTO oldValue
            USING OLD;
        END IF;
        IF newValue IS NULL
        THEN
            newValue := '' :: VARCHAR;
        END IF;
        IF oldValue IS NULL
        THEN
            oldValue := '' :: VARCHAR;
        END IF;

        isColumnSignificant := (position('_x_' IN ri.column_name) < 1) AND
                               (ri.column_name NOT IN
                                ('id', 'l_id', 'lexeme_id', 'l_id_od',
                                 'data_modyfikacji', 'odpowiedzialny'));
        IF isColumnSignificant
        THEN
            isValueModified := oldValue != newValue;
            IF isValueModified
            THEN
                IF frameId IS NULL
                THEN
                    SELECT frame.id
                    INTO frameId
                    FROM history_historyframe frame
                    WHERE transaction_began = transaction_timestamp()
                        AND user_id :: TEXT = userId
                        AND frame.lexeme_id IS NOT DISTINCT FROM lexemeId
                        AND frame.pattern_id IS NOT DISTINCT FROM patternId;
                END IF;
                IF frameId is NULL
                THEN
                    INSERT INTO history_historyframe (lexeme_id, pattern_id)
                    VALUES (lexemeId, patternId)
                    RETURNING history_historyframe.id
                    INTO frameId;
                END IF;

                operation := TG_OP;
                table_oid := TG_RELID;
                table_name1 := TG_TABLE_NAME;
                column_name1 := ri.column_name :: VARCHAR;
                ordinal_position_of_column := ri.ordinal_position;
                old_value := oldValue :: TEXT;
                new_value := newValue :: TEXT;
                INSERT INTO history (operation_, table_oid_, table_name_, id_, column_name_, ordinal_position_of_column_, old_value_, new_value_, frame_id)
                    VALUES (operation, table_oid, table_name1, temp.id, column_name1, ordinal_position_of_column, old_value, new_value, frameId);
            END IF;
        END IF;
        END LOOP;

        RETURN temp;
    END;

    $BODY$;


-- TRIGGERS

-- leksemy

DROP TRIGGER IF EXISTS leksemy_trigger_history_ ON leksemy;
CREATE TRIGGER leksemy_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON leksemy
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS odmieniasie_trigger_history_ ON odmieniasie;
CREATE TRIGGER odmieniasie_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON odmieniasie
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS kwalifikatory_leksemow_trigger_history_
ON kwalifikatory_leksemow;
CREATE TRIGGER kwalifikatory_leksemow_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON kwalifikatory_leksemow
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS kwalifikatory_odmieniasiow_trigger_history_
ON kwalifikatory_odmieniasiow;
CREATE TRIGGER kwalifikatory_odmieniasiow_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON kwalifikatory_odmieniasiow
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS leksemy_w_slownikach_trigger_history_
ON leksemy_w_slownikach;
CREATE TRIGGER leksemy_w_slownikach_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON leksemy_w_slownikach
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS dictionary_lexemecv_trigger_history_
ON dictionary_lexemecv;
CREATE TRIGGER dictionary_lexemecv_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON dictionary_lexemecv
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS odsylacze_trigger_history_ ON odsylacze;
CREATE TRIGGER odsylacze_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON odsylacze
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS dictionary_lexemeav_trigger_history_
ON dictionary_lexemeav;
CREATE TRIGGER dictionary_lexemeav_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON dictionary_lexemeav
FOR EACH ROW EXECUTE PROCEDURE make_history_();

-- wzory

DROP TRIGGER IF EXISTS wzory_trigger_history_ ON wzory;
CREATE TRIGGER wzory_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON wzory
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS zakonczenia_trigger_history_ ON zakonczenia;
CREATE TRIGGER zakonczenia_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON zakonczenia
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS kwalifikatory_zakonczen_trigger_history_
ON kwalifikatory_zakonczen;
CREATE TRIGGER kwalifikatory_zakonczen_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON kwalifikatory_zakonczen
FOR EACH ROW EXECUTE PROCEDURE make_history_();

DROP TRIGGER IF EXISTS wzory_genders_trigger_history_ ON wzory_genders;
CREATE TRIGGER wzory_genders_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON wzory_genders
FOR EACH ROW EXECUTE PROCEDURE make_history_();

--CREATE TRIGGER XXX_trigger_history_
--AFTER INSERT OR UPDATE OR DELETE ON XXX_
--FOR EACH ROW EXECUTE PROCEDURE make_history_();