-- Migration from 106 to 107

CREATE TABLE EXPERIMENT_RELATIONSHIPS_HISTORY (
	ID TECH_ID NOT NULL,
	MAIN_EXPE_ID TECH_ID NOT NULL,
	RELATION_TYPE TEXT_VALUE,
	SAMP_ID TECH_ID,
	DATA_ID TECH_ID,
	ENTITY_PERM_ID TEXT_VALUE,
	PERS_ID_AUTHOR TECH_ID,
	VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, 
	VALID_UNTIL_TIMESTAMP TIME_STAMP
);

CREATE SEQUENCE EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ;

ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_PK PRIMARY KEY(ID);

ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_MAIN_EXPE_FK FOREIGN KEY (MAIN_EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE SET NULL;

CREATE INDEX EXRELH_MAIN_EXPE_FK_I ON EXPERIMENT_RELATIONSHIPS_HISTORY (MAIN_EXPE_ID);
CREATE INDEX EXRELH_MAIN_EXPE_FK_SAMP_FK_I ON EXPERIMENT_RELATIONSHIPS_HISTORY (MAIN_EXPE_ID, SAMP_ID);
CREATE INDEX EXRELH_MAIN_EXPE_FK_DATA_FK_I ON EXPERIMENT_RELATIONSHIPS_HISTORY (MAIN_EXPE_ID, DATA_ID);


CREATE TABLE SAMPLE_RELATIONSHIPS_HISTORY (
	ID TECH_ID NOT NULL,
	MAIN_SAMP_ID TECH_ID NOT NULL,
	RELATION_TYPE TEXT_VALUE,
	EXPE_ID TECH_ID,
	SAMP_ID TECH_ID,
	DATA_ID TECH_ID,
	ENTITY_PERM_ID TEXT_VALUE,
	PERS_ID_AUTHOR TECH_ID,
	VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, 
	VALID_UNTIL_TIMESTAMP TIME_STAMP
);

CREATE SEQUENCE SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ;

ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_PK PRIMARY KEY(ID);

ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_MAIN_SAMP_FK FOREIGN KEY (MAIN_SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE CASCADE;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE SET NULL;

CREATE INDEX SAMPRELH_MAIN_SAMP_FK_I ON SAMPLE_RELATIONSHIPS_HISTORY (MAIN_SAMP_ID);
CREATE INDEX SAMPRELH_MAIN_SAMP_FK_EXPE_FK_I ON SAMPLE_RELATIONSHIPS_HISTORY (MAIN_SAMP_ID, EXPE_ID);
CREATE INDEX SAMPRELH_MAIN_SAMP_FK_SAMP_FK_I ON SAMPLE_RELATIONSHIPS_HISTORY (MAIN_SAMP_ID, SAMP_ID);
CREATE INDEX SAMPRELH_MAIN_SAMP_FK_DATA_FK_I ON SAMPLE_RELATIONSHIPS_HISTORY (MAIN_SAMP_ID, DATA_ID);

CREATE TABLE DATA_SET_RELATIONSHIPS_HISTORY (
	ID TECH_ID NOT NULL,
	MAIN_DATA_ID TECH_ID NOT NULL,
	RELATION_TYPE TEXT_VALUE,
	EXPE_ID TECH_ID,
	SAMP_ID TECH_ID,
	DATA_ID TECH_ID,
	ENTITY_PERM_ID TEXT_VALUE,
	PERS_ID_AUTHOR TECH_ID,
	VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, 
	VALID_UNTIL_TIMESTAMP TIME_STAMP
);

CREATE SEQUENCE DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ;

ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_PK PRIMARY KEY(ID);

ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_MAIN_DATA_FK FOREIGN KEY (MAIN_DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE SET NULL;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE SET NULL;

CREATE INDEX DATARELH_MAIN_DATA_FK_I ON DATA_SET_RELATIONSHIPS_HISTORY (MAIN_DATA_ID);
CREATE INDEX DATARELH_MAIN_DATA_FK_EXPE_FK_I ON DATA_SET_RELATIONSHIPS_HISTORY (MAIN_DATA_ID, EXPE_ID);
CREATE INDEX DATARELH_MAIN_DATA_FK_SAMP_FK_I ON DATA_SET_RELATIONSHIPS_HISTORY (MAIN_DATA_ID, SAMP_ID);
CREATE INDEX DATARELH_MAIN_DATA_FK_DATA_FK_I ON DATA_SET_RELATIONSHIPS_HISTORY (MAIN_DATA_ID, DATA_ID);

GRANT SELECT ON TABLE EXPERIMENT_RELATIONSHIPS_HISTORY TO GROUP OPENBIS_READONLY;
GRANT SELECT ON TABLE SAMPLE_RELATIONSHIPS_HISTORY TO GROUP OPENBIS_READONLY;
GRANT SELECT ON TABLE DATA_SET_RELATIONSHIPS_HISTORY TO GROUP OPENBIS_READONLY;

GRANT SELECT ON SEQUENCE EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ TO GROUP OPENBIS_READONLY;
GRANT SELECT ON SEQUENCE SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ TO GROUP OPENBIS_READONLY;
GRANT SELECT ON SEQUENCE DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ TO GROUP OPENBIS_READONLY;

-- sample -> experiment

CREATE OR REPLACE RULE sample_experiment_update AS
    ON UPDATE TO samples_all 
    WHERE (OLD.EXPE_ID != NEW.EXPE_ID OR OLD.EXPE_ID IS NULL) AND NEW.EXPE_ID IS NOT NULL
    DO ALSO (
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_EXPE_ID = OLD.EXPE_ID AND SAMP_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO EXPERIMENT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_EXPE_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.EXPE_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.PERM_ID,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_SAMP_ID = OLD.ID AND EXPE_ID = OLD.EXPE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         EXPE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.EXPE_ID, 
         (SELECT PERM_ID FROM EXPERIMENTS_ALL WHERE ID = NEW.EXPE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE sample_experiment_remove_update AS
    ON UPDATE TO samples_all 
    WHERE OLD.EXPE_ID IS NOT NULL AND NEW.EXPE_ID IS NULL
    DO ALSO (
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_EXPE_ID = OLD.EXPE_ID AND SAMP_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_SAMP_ID = OLD.ID AND EXPE_ID = OLD.EXPE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
    );
    
CREATE OR REPLACE RULE sample_experiment_insert AS
    ON INSERT TO samples_all 
    WHERE NEW.EXPE_ID IS NOT NULL
       DO ALSO (
       INSERT INTO EXPERIMENT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_EXPE_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.EXPE_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.PERM_ID,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         EXPE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.EXPE_ID, 
         (SELECT PERM_ID FROM EXPERIMENTS_ALL WHERE ID = NEW.EXPE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
   
CREATE OR REPLACE RULE sample_experiment_delete AS
    ON DELETE TO samples_all 
    WHERE OLD.EXPE_ID IS NOT NULL
       DO ALSO 
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp 
         WHERE MAIN_EXPE_ID = OLD.EXPE_ID AND SAMP_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
     
-- container samples
       
CREATE OR REPLACE RULE sample_container_update AS
    ON UPDATE TO samples_all 
    WHERE (OLD.SAMP_ID_PART_OF != NEW.SAMP_ID_PART_OF OR NEW.SAMP_ID_PART_OF IS NULL) AND NEW.SAMP_ID_PART_OF IS NOT NULL
    DO ALSO (
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE (MAIN_SAMP_ID = OLD.SAMP_ID_PART_OF AND SAMP_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINER')
           OR (MAIN_SAMP_ID = OLD.ID AND SAMP_ID = OLD.SAMP_ID_PART_OF AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINED');
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.SAMP_ID_PART_OF, 
         'CONTAINER', 
         NEW.ID, 
         NEW.PERM_ID,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'CONTAINED', 
         NEW.SAMP_ID_PART_OF, 
         (SELECT PERM_ID FROM SAMPLES_ALL WHERE ID = NEW.SAMP_ID_PART_OF),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE sample_container_remove_update AS
    ON UPDATE TO samples_all 
    WHERE OLD.SAMP_ID_PART_OF IS NOT NULL AND NEW.SAMP_ID_PART_OF IS NULL
    DO ALSO (
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE (MAIN_SAMP_ID = OLD.SAMP_ID_PART_OF AND SAMP_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINER')
           OR (MAIN_SAMP_ID = OLD.ID AND SAMP_ID = OLD.SAMP_ID_PART_OF AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINED');
    );
    
CREATE OR REPLACE RULE sample_container_insert AS
    ON INSERT TO samples_all 
    WHERE NEW.SAMP_ID_PART_OF IS NOT NULL
       DO ALSO (
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.SAMP_ID_PART_OF, 
         'CONTAINER', 
         NEW.ID, 
         NEW.PERM_ID,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'CONTAINED', 
         NEW.SAMP_ID_PART_OF, 
         (SELECT PERM_ID FROM SAMPLES_ALL WHERE ID = NEW.SAMP_ID_PART_OF),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
   
CREATE OR REPLACE RULE sample_container_delete AS
    ON DELETE TO samples_all 
    WHERE OLD.SAMP_ID_PART_OF IS NOT NULL
       DO ALSO 
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp 
         WHERE MAIN_SAMP_ID = OLD.SAMP_ID_PART_OF AND SAMP_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINER';

-- dataset -> eperiment

CREATE OR REPLACE RULE dataset_experiment_update AS
    ON UPDATE TO data_all 
    WHERE (OLD.EXPE_ID != NEW.EXPE_ID OR OLD.SAMP_ID IS NOT NULL) AND NEW.SAMP_ID IS NULL
    DO ALSO (
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_EXPE_ID = OLD.EXPE_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO EXPERIMENT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_EXPE_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.EXPE_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.CODE,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE (MAIN_DATA_ID = OLD.ID AND EXPE_ID = OLD.EXPE_ID AND VALID_UNTIL_TIMESTAMP IS NULL);
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         EXPE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.EXPE_ID, 
         (SELECT PERM_ID FROM EXPERIMENTS_ALL WHERE ID = NEW.EXPE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE dataset_experiment_remove_update AS
    ON UPDATE TO data_all 
    WHERE OLD.SAMP_ID IS NULL AND NEW.SAMP_ID IS NOT NULL
    DO ALSO (
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_EXPE_ID = OLD.EXPE_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_DATA_ID = OLD.ID AND EXPE_ID = OLD.EXPE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
    );
    
CREATE OR REPLACE RULE dataset_experiment_insert AS
    ON INSERT TO data_all 
    WHERE NEW.EXPE_ID IS NOT NULL AND NEW.SAMP_ID IS NULL
       DO ALSO (
       INSERT INTO EXPERIMENT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_EXPE_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.EXPE_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.CODE,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         EXPE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.EXPE_ID, 
         (SELECT PERM_ID FROM EXPERIMENTS_ALL WHERE ID = NEW.EXPE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
   
CREATE OR REPLACE RULE dataset_experiment_delete AS
    ON DELETE TO data_all 
    WHERE OLD.EXPE_ID IS NOT NULL AND OLD.SAMP_ID IS NULL
       DO ALSO 
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp 
         WHERE MAIN_EXPE_ID = OLD.EXPE_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;

-- dataset -> sample

CREATE OR REPLACE RULE dataset_sample_update AS
    ON UPDATE TO data_all 
    WHERE (OLD.SAMP_ID != NEW.SAMP_ID OR OLD.SAMP_ID IS NULL) AND NEW.SAMP_ID IS NOT NULL
    DO ALSO (
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_SAMP_ID = OLD.SAMP_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.SAMP_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.CODE,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE (MAIN_DATA_ID = OLD.ID AND SAMP_ID = OLD.SAMP_ID AND VALID_UNTIL_TIMESTAMP IS NULL);
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.SAMP_ID, 
         (SELECT PERM_ID FROM SAMPLES_ALL WHERE ID = NEW.SAMP_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE dataset_sample_remove_update AS
    ON UPDATE TO data_all 
    WHERE OLD.SAMP_ID IS NOT NULL AND NEW.SAMP_ID IS NULL
    DO ALSO (
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_SAMP_ID = OLD.SAMP_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_DATA_ID = OLD.ID AND SAMP_ID = OLD.SAMP_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
    );
    
CREATE OR REPLACE RULE dataset_sample_insert AS
    ON INSERT TO data_all 
    WHERE NEW.SAMP_ID IS NOT NULL
       DO ALSO (
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.SAMP_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.CODE,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         SAMP_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.SAMP_ID, 
         (SELECT PERM_ID FROM SAMPLES_ALL WHERE ID = NEW.SAMP_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
   
CREATE OR REPLACE RULE dataset_sample_delete AS
    ON DELETE TO data_all 
    WHERE OLD.SAMP_ID IS NOT NULL
       DO ALSO 
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp 
         WHERE MAIN_SAMP_ID = OLD.SAMP_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;

-- dataset container

CREATE OR REPLACE RULE dataset_container_update AS
    ON UPDATE TO data_all 
    WHERE (OLD.CTNR_ID != NEW.CTNR_ID OR OLD.CTNR_ID IS NULL) AND NEW.CTNR_ID IS NOT NULL
    DO ALSO (
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE (MAIN_DATA_ID = OLD.CTNR_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINER')
           OR (MAIN_DATA_ID = OLD.ID AND DATA_ID = OLD.CTNR_ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINED');
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.CTNR_ID, 
         'CONTAINER', 
         NEW.ID, 
         NEW.CODE,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'CONTAINED', 
         NEW.CTNR_ID, 
         (SELECT CODE FROM data_all WHERE ID = NEW.CTNR_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE data_container_remove_update AS
    ON UPDATE TO data_all 
    WHERE OLD.CTNR_ID IS NOT NULL AND NEW.CTNR_ID IS NULL
    DO ALSO (
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE (MAIN_DATA_ID = OLD.CTNR_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINER')
           OR (MAIN_DATA_ID = OLD.ID AND DATA_ID = OLD.CTNR_ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINED');
    );
    
CREATE OR REPLACE RULE data_container_insert AS
    ON INSERT TO data_all 
    WHERE NEW.CTNR_ID IS NOT NULL
       DO ALSO (
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.CTNR_ID, 
         'CONTAINER', 
         NEW.ID, 
         NEW.CODE,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_DATA_ID,
         RELATION_TYPE, 
         DATA_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'CONTAINED', 
         NEW.CTNR_ID, 
         (SELECT CODE FROM data_all WHERE ID = NEW.CTNR_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
   
CREATE OR REPLACE RULE data_container_delete AS
    ON DELETE TO data_all 
    WHERE OLD.CTNR_ID IS NOT NULL
       DO ALSO 
       UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp 
         WHERE MAIN_DATA_ID = OLD.CTNR_ID AND DATA_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL AND RELATION_TYPE = 'CONTAINER';

-- dataset parent-child relationship
         
CREATE OR REPLACE RULE data_parent_child_insert AS
    ON INSERT TO data_set_relationships_all
    WHERE NEW.DEL_ID IS NULL
       DO ALSO (
         INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_DATA_ID,
           RELATION_TYPE, 
           DATA_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.DATA_ID_PARENT, 
           'PARENT', 
           NEW.DATA_ID_CHILD, 
           (SELECT CODE FROM data_all WHERE ID = NEW.DATA_ID_CHILD),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
         INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_DATA_ID,
           RELATION_TYPE, 
           DATA_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.DATA_ID_CHILD, 
           'CHILD', 
           NEW.DATA_ID_PARENT, 
           (SELECT CODE FROM data_all WHERE ID = NEW.DATA_ID_PARENT),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
       );

CREATE OR REPLACE RULE data_parent_child_delete AS
    ON DELETE TO data_set_relationships_all
    WHERE OLD.DEL_ID IS NULL
       DO ALSO (
         UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp
           WHERE (MAIN_DATA_ID = OLD.DATA_ID_PARENT AND DATA_ID = OLD.DATA_ID_CHILD AND VALID_UNTIL_TIMESTAMP IS NULL)
             OR (MAIN_DATA_ID = OLD.DATA_ID_CHILD AND DATA_ID = OLD.DATA_ID_PARENT AND VALID_UNTIL_TIMESTAMP IS NULL);
       );
       
CREATE OR REPLACE RULE data_parent_child_update AS
    ON UPDATE TO data_set_relationships_all
    WHERE NEW.DEL_ID IS NOT NULL AND OLD.DEL_ID IS NULL
       DO ALSO (
         UPDATE DATA_SET_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp
           WHERE (MAIN_DATA_ID = OLD.DATA_ID_PARENT AND DATA_ID = OLD.DATA_ID_CHILD AND VALID_UNTIL_TIMESTAMP IS NULL)
             OR (MAIN_DATA_ID = OLD.DATA_ID_CHILD AND DATA_ID = OLD.DATA_ID_PARENT AND VALID_UNTIL_TIMESTAMP IS NULL);
       );
       
CREATE OR REPLACE RULE data_parent_child_revert_update AS
    ON UPDATE TO data_set_relationships_all
    WHERE OLD.DEL_ID IS NOT NULL AND NEW.DEL_ID IS NULL
       DO ALSO (
         INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_DATA_ID,
           RELATION_TYPE, 
           DATA_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.DATA_ID_PARENT, 
           'PARENT', 
           NEW.DATA_ID_CHILD, 
           (SELECT CODE FROM data_all WHERE ID = NEW.DATA_ID_CHILD),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
         INSERT INTO DATA_SET_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_DATA_ID,
           RELATION_TYPE, 
           DATA_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.DATA_ID_CHILD, 
           'CHILD', 
           NEW.DATA_ID_PARENT, 
           (SELECT CODE FROM data_all WHERE ID = NEW.DATA_ID_PARENT),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
       );

-- samples parent-child relationship

CREATE OR REPLACE RULE sample_parent_child_insert AS
    ON INSERT TO sample_relationships_all
    WHERE NEW.DEL_ID IS NULL
       DO ALSO (
         INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_SAMP_ID,
           RELATION_TYPE, 
           SAMP_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.SAMPLE_ID_PARENT, 
           'PARENT', 
           NEW.SAMPLE_ID_CHILD, 
           (SELECT PERM_ID FROM samples_all WHERE ID = NEW.SAMPLE_ID_CHILD),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
         INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_SAMP_ID,
           RELATION_TYPE, 
           SAMP_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.SAMPLE_ID_CHILD, 
           'CHILD', 
           NEW.SAMPLE_ID_PARENT, 
           (SELECT PERM_ID FROM samples_all WHERE ID = NEW.SAMPLE_ID_PARENT),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
       );
       
CREATE OR REPLACE RULE sample_parent_child_delete AS
    ON DELETE TO sample_relationships_all
    WHERE OLD.DEL_ID IS NULL
       DO ALSO (
         UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp
           WHERE (MAIN_SAMP_ID = OLD.SAMPLE_ID_PARENT AND SAMP_ID = OLD.SAMPLE_ID_CHILD AND VALID_UNTIL_TIMESTAMP IS NULL)
             OR (MAIN_SAMP_ID = OLD.SAMPLE_ID_CHILD AND SAMP_ID = OLD.SAMPLE_ID_PARENT AND VALID_UNTIL_TIMESTAMP IS NULL);
       );

CREATE OR REPLACE RULE sample_parent_child_update AS
    ON UPDATE TO sample_relationships_all
    WHERE NEW.DEL_ID IS NOT NULL AND OLD.DEL_ID IS NULL
       DO ALSO (
         UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp
           WHERE (MAIN_SAMP_ID = OLD.SAMPLE_ID_PARENT AND SAMP_ID = OLD.SAMPLE_ID_CHILD AND VALID_UNTIL_TIMESTAMP IS NULL)
             OR (MAIN_SAMP_ID = OLD.SAMPLE_ID_CHILD AND SAMP_ID = OLD.SAMPLE_ID_PARENT AND VALID_UNTIL_TIMESTAMP IS NULL);
       );

CREATE OR REPLACE RULE sample_parent_child_revert_update AS
    ON UPDATE TO sample_relationships_all
    WHERE NEW.DEL_ID IS NULL AND OLD.DEL_ID IS NOT NULL
       DO ALSO (
         INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_SAMP_ID,
           RELATION_TYPE, 
           SAMP_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.SAMPLE_ID_PARENT, 
           'PARENT', 
           NEW.SAMPLE_ID_CHILD, 
           (SELECT PERM_ID FROM samples_all WHERE ID = NEW.SAMPLE_ID_CHILD),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
         INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
           ID, 
           MAIN_SAMP_ID,
           RELATION_TYPE, 
           SAMP_ID, 
           ENTITY_PERM_ID,
           PERS_ID_AUTHOR,
           VALID_FROM_TIMESTAMP
         ) VALUES (
           nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
           NEW.SAMPLE_ID_CHILD, 
           'CHILD', 
           NEW.SAMPLE_ID_PARENT, 
           (SELECT PERM_ID FROM samples_all WHERE ID = NEW.SAMPLE_ID_PARENT),
           NEW.PERS_ID_AUTHOR,
           NEW.MODIFICATION_TIMESTAMP
         );
       );
