-- state domain
CREATE DOMAIN OPERATION_EXECUTION_STATE AS VARCHAR(40) CHECK (VALUE IN ('NEW', 'SCHEDULED', 'RUNNING', 'FINISHED', 'FAILED'));

-- table
CREATE TABLE OPERATION_EXECUTIONS (ID TECH_ID NOT NULL, CODE CODE NOT NULL, STATE OPERATION_EXECUTION_STATE NOT NULL, DESCRIPTION TEXT_VALUE NOT NULL, ERROR TEXT_VALUE, CREATION_DATE TIME_STAMP_DFL, START_DATE TIME_STAMP, FINISH_DATE TIME_STAMP);

-- sequence
CREATE SEQUENCE OPERATION_EXECUTIONS_ID_SEQ;

-- pk
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_PK PRIMARY KEY(ID);

-- code unique constraint
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_CODE_UK UNIQUE (CODE);

-- code index 
CREATE INDEX OPERATION_EXECUTIONS_CODE_I ON OPERATION_EXECUTIONS (CODE);

-- checks
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_STATE_ERROR_CHECK CHECK (
	(STATE != 'FAILED' AND ERROR IS NULL) OR
	(STATE = 'FAILED' AND ERROR IS NOT NULL)
);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_STATE_START_DATE_CHECK CHECK (
	(STATE IN ('NEW','SCHEDULED') AND START_DATE IS NULL) OR 
	(STATE IN ('RUNNING','FINISHED','FAILED') AND START_DATE IS NOT NULL)
);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_STATE_FINISH_DATE_CHECK CHECK (
	(STATE IN ('NEW','SCHEDULED','RUNNING') AND FINISH_DATE IS NULL) OR 
	(STATE IN ('FINISHED','FAILED') AND FINISH_DATE IS NOT NULL)
);
-- grant
GRANT SELECT ON TABLE OPERATION_EXECUTIONS TO GROUP OPENBIS_READONLY;
