/*
OP_Code для PH_HISTORY
0 - ph updated
1 - ph deleted
2 - ph inserted
3 - attrval deleted
4 - attrval inserted
*/

CREATE OR REPLACE FUNCTION AddUpdatedPh_to_PhHistory() RETURNS TRIGGER AS
$$
BEGIN
    -- триггер фильтруется и смотрит только на эти поля, если добавляем тут, то и в вызывающую функцию тоже:
    IF (OLD.name IS DISTINCT FROM NEW.name) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Name', new.name);
    END IF;
    IF (OLD.filename IS DISTINCT FROM NEW.filename) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'FileName', new.filename);
    END IF;
    IF (OLD.duration IS DISTINCT FROM NEW.duration) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Duration', new.duration);
    END IF;
    IF (OLD.type IS DISTINCT FROM NEW.type) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Type', new.type);
    END IF;
    IF (OLD.intro IS DISTINCT FROM NEW.intro) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Intro', new.intro);
    END IF;
    IF (OLD.outro IS DISTINCT FROM NEW.outro) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Outro', new.outro);
    END IF;
    IF (OLD.fadein IS DISTINCT FROM NEW.fadein) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'FadeIn', new.fadein);
    END IF;
    IF (OLD.fadeout IS DISTINCT FROM NEW.fadeout) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'FadeOut', new.fadeout);
    END IF;
    IF (OLD.startnext IS DISTINCT FROM NEW.startnext) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'StartNext', new.startnext);
    END IF;
    IF (OLD.ph_start IS DISTINCT FROM NEW.ph_start) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Ph_Start', new.ph_start);
    END IF;
    IF (OLD.ph_stop IS DISTINCT FROM NEW.ph_stop) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Ph_Stop', new.ph_stop);
    END IF;
    IF (OLD.cmd IS DISTINCT FROM NEW.cmd) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Cmd', new.cmd);
    END IF;
    IF (OLD.freq IS DISTINCT FROM NEW.freq) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Freq', new.freq);
    END IF;
    IF (OLD.smplsize IS DISTINCT FROM NEW.smplsize) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'SmplSize', new.smplsize);
    END IF;
    IF (OLD.radioid IS DISTINCT FROM NEW.radioid) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'RadioID', new.radioid);
    END IF;
    IF (OLD.deleted IS DISTINCT FROM NEW.deleted) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Deleted', new.deleted);
    END IF;
    IF (OLD.id_number IS DISTINCT FROM NEW.id_number) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'ID_Number', new.id_number);
    END IF;
    IF (OLD.modifyft IS DISTINCT FROM NEW.modifyft) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'ModifyFT', new.modifyft);
    END IF;
    IF (OLD.ddb_owner IS DISTINCT FROM NEW.ddb_owner) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'DDB_Owner', new.ddb_owner);
    END IF;
    IF (OLD.version IS DISTINCT FROM NEW.version) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Version', new.version);
    END IF;
    IF (OLD.ddb_id IS DISTINCT FROM NEW.ddb_id) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'DDB_ID', new.ddb_id);
    END IF;
    IF (OLD.intro2 IS DISTINCT FROM NEW.intro2) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Intro2', new.intro2);
    END IF;
    IF (OLD.intro3 IS DISTINCT FROM NEW.intro3) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Intro3', new.intro3);
    END IF;
    IF (OLD.startthis IS DISTINCT FROM NEW.startthis) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'StartThis', new.startthis);
    END IF;
    IF (OLD.archive IS DISTINCT FROM NEW.archive) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Archive', new.archive);
    END IF;
    IF (OLD.readyonair IS DISTINCT FROM NEW.readyonair) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'ReadyOnAir', new.readyonair);
    END IF;
    IF (OLD.lccode IS DISTINCT FROM NEW.lccode) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'LcCode', new.lccode);
    END IF;
    IF (OLD.publisher IS DISTINCT FROM NEW.publisher) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Publisher', new.publisher);
    END IF;
    IF (OLD.cmd2 IS DISTINCT FROM NEW.cmd2) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'Cmd2', new.cmd2);
    END IF;
    IF (OLD.textsizems IS DISTINCT FROM NEW.textsizems) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'TextSizeMs', new.textsizems);
    END IF;
    IF (OLD.phonotype IS DISTINCT FROM NEW.phonotype) THEN
        INSERT INTO PH_HISTORY (PH_ID, Name, Value) VALUES (new.id, 'PhonoType', new.phonotype);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION AddDeletedPh_to_PhHistory() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO PH_HISTORY (PH_ID, OP_Code) VALUES (old.id, 1);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION AddInsertedPh_to_PhHistory() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'Type', new.Type);
    INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'Name', new.Name);
    INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'FileName', new.FileName);
    INSERT INTO PH_HISTORY (PH_ID, OP_Code, Name, Value) VALUES (new.id, 2, 'ID_Number', new.ID_Number);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION AddDeletedAttribute_to_PhHistory() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO PH_HISTORY (PH_ID, OP_Code, AttrVal_ID) VALUES (old.ph_id, 3, old.attrval_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION AddInsertedAttribute_to_PhHistory() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO PH_HISTORY (PH_ID, OP_Code, AttrVal_ID) VALUES (new.ph_id, 4, new.attrval_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO
$$
    DECLARE
        ver int := 3;

    BEGIN

        IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'components')
        THEN
            IF EXISTS (SELECT veri FROM public.components WHERE name = 'mdb.ph_history' AND veri >= ver)
            THEN
                RAISE NOTICE 'Current PH_HISTORY version is equal or higher than %', ver;
                RETURN;
            END IF;
        ELSE
            RAISE NOTICE 'Components table not found. Script exit';
            RETURN;
        END IF;

        RAISE NOTICE 'Script begin';

        DROP TABLE IF EXISTS PH_HISTORY CASCADE;

        CREATE TABLE PH_HISTORY
        (
            "id"         SERIAL      NOT NULL
                CONSTRAINT PK_hystory PRIMARY KEY,
            "username"   varchar(50) NULL
                CONSTRAINT DF_hystory_UserName   DEFAULT (current_user),
            "hostname"   varchar(50) NULL
                CONSTRAINT DF_hystory_HostName   DEFAULT (inet_server_addr()),
            "modifydate" timestamptz NOT NULL
                CONSTRAINT DF_hystory_ModifyDate DEFAULT (now()),
            "op_code"    smallint    NOT NULL
                CONSTRAINT DF_hystory_OP_Code    DEFAULT ((0)),
            "ph_id"      int         NOT NULL,
            "name"       varchar(50) NULL,
            "value"      text        NULL,
            "attrval_id" int         NULL
        );

        DROP TRIGGER IF EXISTS History_Updated ON PH;
        -- Тут в MS портянка с курсором ! (не совсем понятно зачем)
        CREATE TRIGGER History_Updated
            AFTER UPDATE OF
                -- триггер будет работать только для указанных в запросе полей, если добавляем тут, то и в триггер тоже:
                name, filename, duration, type, intro, outro, fadein, fadeout, startnext, ph_start, ph_stop, cmd, freq,
                smplsize, radioid, deleted, id_number, modifyft, ddb_owner, version, ddb_id, intro2, intro3, startthis,
                archive, readyonair, lccode, publisher, cmd2, textsizems, phonotype
            ON PH
            FOR EACH ROW
        EXECUTE PROCEDURE AddUpdatedPh_to_PhHistory();
        RAISE NOTICE 'Created: TRIGGER History_Updated';

        DROP TRIGGER IF EXISTS History_Deleted ON PH;
        CREATE TRIGGER History_Deleted
            AFTER DELETE
            ON PH
            FOR EACH ROW
        EXECUTE PROCEDURE AddDeletedPh_to_PhHistory();
        RAISE NOTICE 'Created: TRIGGER History_Deleted';

        DROP TRIGGER IF EXISTS History_Inserted ON PH;
        CREATE TRIGGER History_Inserted
            AFTER INSERT
            ON PH
            FOR EACH ROW
        EXECUTE PROCEDURE AddInsertedPh_to_PhHistory();
        RAISE NOTICE 'Created: TRIGGER History_Inserted';

        DROP TRIGGER IF EXISTS History_Attr_Deleted ON PH_TO_ATTRVALS;
        CREATE TRIGGER History_Attr_Deleted
            AFTER DELETE
            ON PH_TO_ATTRVALS
            FOR EACH ROW
        EXECUTE PROCEDURE AddDeletedAttribute_to_PhHistory();
        RAISE NOTICE 'Created: TRIGGER History_Attr_Deleted';

        DROP TRIGGER IF EXISTS History_Attr_Inserted ON PH_TO_ATTRVALS;
        CREATE TRIGGER History_Attr_Inserted
            AFTER INSERT
            ON PH_TO_ATTRVALS
            FOR EACH ROW
        EXECUTE PROCEDURE AddInsertedAttribute_to_PhHistory();
        RAISE NOTICE 'Created: TRIGGER History_Attr_Inserted';

        IF EXISTS (SELECT veri FROM public.components WHERE name = 'mdb.ph_history')
        THEN
            UPDATE public.components set veri = ver WHERE name = 'mdb.ph_history';
        ELSE
            INSERT INTO public.components (name, vers, veri) VALUES ('mdb.ph_history', '', ver);
        END IF;

        RAISE NOTICE 'Script end!';
    END;
$$;