-- Migration from 165 to 166

-- table
CREATE TABLE SEMANTIC_ANNOTATIONS (ID TECH_ID NOT NULL,
	PERM_ID CODE NOT NULL,
	SATY_ID TECH_ID, 
	STPT_ID TECH_ID,
	PRTY_ID TECH_ID,
	PREDICATE_ONTOLOGY_ID TEXT,
	PREDICATE_ONTOLOGY_VERSION TEXT,
	PREDICATE_ACCESSION_ID TEXT,
	DESCRIPTOR_ONTOLOGY_ID TEXT,
	DESCRIPTOR_ONTOLOGY_VERSION TEXT,
	DESCRIPTOR_ACCESSION_ID TEXT,
	CREATION_DATE time_stamp_dfl NOT NULL
	);

-- sequence
CREATE SEQUENCE SEMANTIC_ANNOTATION_ID_SEQ;

-- pk
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_PK PRIMARY KEY(ID);

-- fk
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_SATY_ID_FK FOREIGN KEY (SATY_ID) REFERENCES SAMPLE_TYPES(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_STPT_ID_FK FOREIGN KEY (STPT_ID) REFERENCES SAMPLE_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_PRTY_ID_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

-- index 
CREATE INDEX SEMANTIC_ANNOTATIONS_SATY_ID_I ON SEMANTIC_ANNOTATIONS (SATY_ID);
CREATE INDEX SEMANTIC_ANNOTATIONS_STPT_ID_I ON SEMANTIC_ANNOTATIONS (STPT_ID);
CREATE INDEX SEMANTIC_ANNOTATIONS_PRTY_ID_I ON SEMANTIC_ANNOTATIONS (PRTY_ID);

-- checks
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_SSP_CK CHECK 
	((SATY_ID IS NOT NULL AND STPT_ID IS NULL AND PRTY_ID IS NULL) OR 
	 (SATY_ID IS NULL AND STPT_ID IS NOT NULL AND PRTY_ID IS NULL) OR
	 (SATY_ID IS NULL AND STPT_ID IS NULL AND PRTY_ID IS NOT NULL)
	);

-- grant
GRANT SELECT ON TABLE SEMANTIC_ANNOTATIONS TO GROUP OPENBIS_READONLY;
