-- Migration from 108 to 109

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 OLD.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
       );
    );

ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD COLUMN SPACE_ID TECH_ID;

ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_SPACE_FK FOREIGN KEY (SPACE_ID) REFERENCES SPACES(ID) ON DELETE SET NULL;

CREATE INDEX SAMPRELH_MAIN_SAMP_FK_SPACE_FK_I ON SAMPLE_RELATIONSHIPS_HISTORY (MAIN_SAMP_ID, SPACE_ID);

ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD COLUMN PROJ_ID TECH_ID;

ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_PROJ_FK FOREIGN KEY (PROJ_ID) REFERENCES PROJECTS(ID) ON DELETE SET NULL;

CREATE INDEX EXRELH_MAIN_EXPE_FK_PROJ_FK_I ON EXPERIMENT_RELATIONSHIPS_HISTORY (MAIN_EXPE_ID, PROJ_ID);

CREATE TABLE PROJECT_RELATIONSHIPS_HISTORY (
	ID TECH_ID NOT NULL,
	MAIN_PROJ_ID TECH_ID NOT NULL,
	RELATION_TYPE TEXT_VALUE,
	EXPE_ID TECH_ID,
	SPACE_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 PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ;

ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_PK PRIMARY KEY(ID);

ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_MAIN_PROJ_FK FOREIGN KEY (MAIN_PROJ_ID) REFERENCES PROJECTS(ID) ON DELETE CASCADE;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE SET NULL;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_SPACE_FK FOREIGN KEY (SPACE_ID) REFERENCES SPACES(ID) ON DELETE SET NULL;

CREATE INDEX PRRELH_MAIN_PROJ_FK_I ON PROJECT_RELATIONSHIPS_HISTORY (MAIN_PROJ_ID);
CREATE INDEX PRRELH_MAIN_PROJ_FK_EXPE_FK_I ON PROJECT_RELATIONSHIPS_HISTORY (MAIN_PROJ_ID, EXPE_ID);
CREATE INDEX PRRELH_MAIN_PROJ_FK_SPACE_FK_I ON PROJECT_RELATIONSHIPS_HISTORY (MAIN_PROJ_ID, SPACE_ID);

GRANT SELECT ON TABLE PROJECT_RELATIONSHIPS_HISTORY TO GROUP OPENBIS_READONLY;

GRANT SELECT ON SEQUENCE PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ TO GROUP OPENBIS_READONLY;

-- experiment -> project

CREATE OR REPLACE RULE experiment_project_update AS
    ON UPDATE TO experiments_all 
    WHERE (OLD.PROJ_ID != NEW.PROJ_ID OR OLD.PROJ_ID IS NULL) AND NEW.PROJ_ID IS NOT NULL
    DO ALSO (
       UPDATE PROJECT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_PROJ_ID = OLD.PROJ_ID AND EXPE_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO PROJECT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_PROJ_ID,
         RELATION_TYPE, 
         EXPE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.PROJ_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.PERM_ID,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_EXPE_ID = OLD.ID AND PROJ_ID = OLD.PROJ_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO EXPERIMENT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_EXPE_ID,
         RELATION_TYPE, 
         PROJ_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.PROJ_ID, 
         (SELECT CODE FROM PROJECTS WHERE ID = NEW.PROJ_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE experiment_project_remove_update AS
    ON UPDATE TO experiments_all 
    WHERE OLD.PROJ_ID IS NOT NULL AND NEW.PROJ_ID IS NULL
    DO ALSO (
       UPDATE PROJECT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_PROJ_ID = OLD.PROJ_ID AND EXPE_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       UPDATE EXPERIMENT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_EXPE_ID = OLD.ID AND PROJ_ID = OLD.PROJ_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
    );
    
CREATE OR REPLACE RULE experiment_project_insert AS
    ON INSERT TO experiments_all 
    WHERE NEW.PROJ_ID IS NOT NULL
       DO ALSO (
       INSERT INTO PROJECT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_PROJ_ID,
         RELATION_TYPE, 
         EXPE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.PROJ_ID, 
         'OWNER', 
         NEW.ID, 
         NEW.PERM_ID,
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
       INSERT INTO EXPERIMENT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_EXPE_ID,
         RELATION_TYPE, 
         PROJ_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.PROJ_ID, 
         (SELECT CODE FROM PROJECTS WHERE ID = NEW.PROJ_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
   
CREATE OR REPLACE RULE experiment_project_delete AS
    ON DELETE TO experiments_all 
    WHERE OLD.PROJ_ID IS NOT NULL
       DO ALSO 
       UPDATE PROJECT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = current_timestamp 
         WHERE MAIN_PROJ_ID = OLD.PROJ_ID AND EXPE_ID = OLD.ID AND VALID_UNTIL_TIMESTAMP IS NULL;

-- project -> space

CREATE OR REPLACE RULE project_space_update AS
    ON UPDATE TO projects 
    WHERE (OLD.SPACE_ID != NEW.SPACE_ID OR OLD.SPACE_ID IS NULL) AND NEW.SPACE_ID IS NOT NULL
    DO ALSO (
       UPDATE PROJECT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_PROJ_ID = OLD.ID AND SPACE_ID = OLD.SPACE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO PROJECT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_PROJ_ID,
         RELATION_TYPE, 
         SPACE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.SPACE_ID, 
         (SELECT CODE FROM SPACES WHERE ID = NEW.SPACE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE project_space_remove_update AS
    ON UPDATE TO projects 
    WHERE OLD.SPACE_ID IS NOT NULL AND NEW.SPACE_ID IS NULL
    DO ALSO (
       UPDATE PROJECT_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP
         WHERE MAIN_PROJ_ID = OLD.ID AND SPACE_ID = OLD.SPACE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
    );
    
CREATE OR REPLACE RULE project_space_insert AS
    ON INSERT TO projects 
    WHERE NEW.SPACE_ID IS NOT NULL
       DO ALSO (
       INSERT INTO PROJECT_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_PROJ_ID,
         RELATION_TYPE, 
         SPACE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.SPACE_ID, 
         (SELECT CODE FROM SPACES WHERE ID = NEW.SPACE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );

-- sample -> space

CREATE OR REPLACE RULE sample_space_update AS
    ON UPDATE TO samples_all 
    WHERE (OLD.SPACE_ID != NEW.SPACE_ID OR OLD.SPACE_ID IS NULL OR OLD.EXPE_ID IS NOT NULL) AND NEW.SPACE_ID IS NOT NULL AND NEW.EXPE_ID IS NULL
    DO ALSO (
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_SAMP_ID = OLD.ID AND SPACE_ID = OLD.SPACE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
       INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         SPACE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.SPACE_ID, 
         (SELECT CODE FROM SPACES WHERE ID = NEW.SPACE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
    );
    
CREATE OR REPLACE RULE sample_space_remove_update AS
    ON UPDATE TO samples_all 
    WHERE OLD.SPACE_ID IS NOT NULL AND (NEW.SPACE_ID IS NULL OR (OLD.EXPE_ID IS NULL AND NEW.EXPE_ID IS NOT NULL))
    DO ALSO (
       UPDATE SAMPLE_RELATIONSHIPS_HISTORY SET VALID_UNTIL_TIMESTAMP = NEW.MODIFICATION_TIMESTAMP 
         WHERE MAIN_SAMP_ID = OLD.ID AND SPACE_ID = OLD.SPACE_ID AND VALID_UNTIL_TIMESTAMP IS NULL;
    );
    
CREATE OR REPLACE RULE sample_space_insert AS
    ON INSERT TO samples_all 
    WHERE NEW.EXPE_ID IS NULL AND NEW.SPACE_ID IS NOT NULL
    DO ALSO (
      INSERT INTO SAMPLE_RELATIONSHIPS_HISTORY (
         ID, 
         MAIN_SAMP_ID,
         RELATION_TYPE, 
         SPACE_ID, 
         ENTITY_PERM_ID,
         PERS_ID_AUTHOR,
         VALID_FROM_TIMESTAMP
       ) VALUES (
         nextval('SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ'), 
         NEW.ID, 
         'OWNED', 
         NEW.SPACE_ID, 
         (SELECT CODE FROM SPACES WHERE ID = NEW.SPACE_ID),
         NEW.PERS_ID_MODIFIER,
         NEW.MODIFICATION_TIMESTAMP
       );
   );
