-- Migration from 123 to 124

DROP TABLE METAPROJECT_ASSIGNMENTS;
DROP SEQUENCE METAPROJECT_ASSIGNMENT_ID_SEQ;

CREATE TABLE METAPROJECT_ASSIGNMENTS_ALL (
	ID              TECH_ID NOT NULL,
	MEPR_ID         TECH_ID NOT NULL,
	EXPE_ID         TECH_ID,
	SAMP_ID         TECH_ID,
	DATA_ID         TECH_ID,
	MATE_ID         TECH_ID,
	DEL_ID					TECH_ID,
	CREATION_DATE  TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE SEQUENCE METAPROJECT_ASSIGNMENT_ID_SEQ;

ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_PK PRIMARY KEY(ID);

ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_FK FOREIGN KEY (MEPR_ID) REFERENCES METAPROJECTS(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_EXPE_ID_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_SAMP_ID_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_DATA_ID_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MATE_ID_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_DEL_ID_FK FOREIGN KEY (DEL_ID) REFERENCES DELETIONS(ID);

ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_CHECK_NN CHECK (
	(EXPE_ID IS NOT NULL AND SAMP_ID IS NULL AND DATA_ID IS NULL AND MATE_ID IS NULL) OR
	(EXPE_ID IS NULL AND SAMP_ID IS NOT NULL AND DATA_ID IS NULL AND MATE_ID IS NULL) OR
	(EXPE_ID IS NULL AND SAMP_ID IS NULL AND DATA_ID IS NOT NULL AND MATE_ID IS NULL) OR
	(EXPE_ID IS NULL AND SAMP_ID IS NULL AND DATA_ID IS NULL AND MATE_ID IS NOT NULL));

ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_EXPE_ID_UK UNIQUE (MEPR_ID, EXPE_ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_SAMP_ID_UK UNIQUE (MEPR_ID, SAMP_ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_DATA_ID_UK UNIQUE (MEPR_ID, DATA_ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_MATE_ID_UK UNIQUE (MEPR_ID, MATE_ID);

CREATE INDEX METAPROJECT_ASSIGNMENTS_ALL_MEPR_FK_I ON METAPROJECT_ASSIGNMENTS_ALL (MEPR_ID);
CREATE INDEX METAPROJECT_ASSIGNMENTS_ALL_EXPE_FK_I ON METAPROJECT_ASSIGNMENTS_ALL (EXPE_ID);
CREATE INDEX METAPROJECT_ASSIGNMENTS_ALL_SAMP_FK_I ON METAPROJECT_ASSIGNMENTS_ALL (SAMP_ID);
CREATE INDEX METAPROJECT_ASSIGNMENTS_ALL_DATA_FK_I ON METAPROJECT_ASSIGNMENTS_ALL (DATA_ID);
CREATE INDEX METAPROJECT_ASSIGNMENTS_ALL_MATE_FK_I ON METAPROJECT_ASSIGNMENTS_ALL (MATE_ID);
CREATE INDEX METAPROJECT_ASSIGNMENTS_ALL_DEL_FK_I ON METAPROJECT_ASSIGNMENTS_ALL (DEL_ID);

CREATE VIEW METAPROJECT_ASSIGNMENTS AS
   SELECT ID, MEPR_ID, EXPE_ID, SAMP_ID, DATA_ID, MATE_ID, DEL_ID, CREATION_DATE
   FROM METAPROJECT_ASSIGNMENTS_ALL 
   WHERE DEL_ID IS NULL;
   
CREATE OR REPLACE RULE METAPROJECT_ASSIGNMENTS_INSERT AS
    ON INSERT TO METAPROJECT_ASSIGNMENTS DO INSTEAD 
       INSERT INTO METAPROJECT_ASSIGNMENTS_ALL (
         ID, 
         MEPR_ID,
         EXPE_ID,
			   SAMP_ID,
			   DATA_ID,
			   MATE_ID,
			   DEL_ID,
			   CREATION_DATE
       ) VALUES (
         NEW.ID, 
         NEW.MEPR_ID,
         NEW.EXPE_ID,
			   NEW.SAMP_ID,
			   NEW.DATA_ID,
			   NEW.MATE_ID,
			   NEW.DEL_ID,
			   NEW.CREATION_DATE
       );

CREATE OR REPLACE RULE METAPROJECT_ASSIGNMENTS_UPDATE AS
    ON UPDATE TO METAPROJECT_ASSIGNMENTS DO INSTEAD 
       UPDATE METAPROJECT_ASSIGNMENTS_ALL
          SET 
			      ID = NEW.ID, 
         		MEPR_ID = NEW.MEPR_ID,
         		EXPE_ID = NEW.EXPE_ID,
			   		SAMP_ID = NEW.SAMP_ID,
			   		DATA_ID = NEW.DATA_ID,
			   		MATE_ID = NEW.MATE_ID,
			   		DEL_ID = NEW.DEL_ID,
			   		CREATION_DATE = NEW.CREATION_DATE
          WHERE ID = NEW.ID;
          
CREATE OR REPLACE RULE METAPROJECT_ASSIGNMENTS_DELETE AS
    ON DELETE TO METAPROJECT_ASSIGNMENTS DO INSTEAD
       DELETE FROM METAPROJECT_ASSIGNMENTS_ALL
              WHERE ID = OLD.ID;
   
GRANT SELECT ON TABLE METAPROJECT_ASSIGNMENTS_ALL TO GROUP OPENBIS_READONLY;
GRANT SELECT ON METAPROJECT_ASSIGNMENTS TO GROUP OPENBIS_READONLY;
GRANT SELECT ON SEQUENCE METAPROJECT_ASSIGNMENT_ID_SEQ TO GROUP OPENBIS_READONLY; 

-- replace a unique constraint on metaproject (name, owner) pair after making the name case preserving but case insensitive for searching
DROP INDEX METAPROJECTS_NAME_OWNER_I;
ALTER TABLE METAPROJECTS DROP CONSTRAINT METAPROJECTS_NAME_OWNER_UK;
CREATE UNIQUE INDEX METAPROJECTS_NAME_OWNER_UK ON METAPROJECTS (lower(NAME), OWNER);