-- migration-019-020.sql


-- Creating domains

--------
-- Added
--------

CREATE DOMAIN EVENT_TYPE AS VARCHAR(40) CHECK (VALUE IN ('DELETION', 'INVALIDATION', 'MOVEMENT'));

----------
-- Changed
----------

-- Change the domain AUTHORIZATION_ROLE by changing its TYPE to VARCHAR(40) and dropping the NOT NULL constraint

ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN ROLE_CODE TYPE CODE;
DROP DOMAIN AUTHORIZATION_ROLE;
CREATE DOMAIN AUTHORIZATION_ROLE AS VARCHAR(40) CHECK (VALUE IN ('ADMIN', 'USER', 'OBSERVER', 'ETL_SERVER')) ;
ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN ROLE_CODE TYPE AUTHORIZATION_ROLE;


-- Creating tables

--------
-- Added
--------

CREATE TABLE EVENTS (ID TECH_ID NOT NULL,EVENT_TYPE EVENT_TYPE NOT NULL,DATA_ID TECH_ID,DESCRIPTION DESCRIPTION_250,REASON DESCRIPTION_250,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);

----------
-- Changed
----------

ALTER TABLE DATA ADD COLUMN IS_DELETED BOOLEAN_CHAR DEFAULT 'F';
ALTER TABLE DATA ADD COLUMN IS_VALID BOOLEAN_CHAR DEFAULT 'T';

ALTER TABLE EXTERNAL_DATA ADD COLUMN CVTE_ID_STORE TECH_ID;

ALTER TABLE PERSONS ADD COLUMN GROU_ID TECH_ID;

ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN ROLE_CODE SET NOT NULL;

UPDATE EXPERIMENTS SET PERS_ID_REGISTERER = (SELECT ID FROM PERSONS WHERE USER_ID ='system') WHERE PERS_ID_REGISTERER IS NULL;
ALTER TABLE EXPERIMENTS ALTER COLUMN PERS_ID_REGISTERER SET NOT NULL;

UPDATE PROJECTS SET PERS_ID_REGISTERER = (SELECT ID FROM PERSONS WHERE USER_ID ='system') WHERE PERS_ID_REGISTERER IS NULL;
ALTER TABLE PROJECTS ALTER COLUMN PERS_ID_REGISTERER SET NOT NULL;

UPDATE PERSONS SET GROU_ID = (SELECT ID FROM GROUPS LIMIT 1);

-- Creating sequences

--------
-- Added
--------

CREATE SEQUENCE EVENT_ID_SEQ;


-- Creating primary key constraints

--------
-- Added
--------

ALTER TABLE EVENTS ADD CONSTRAINT EVNT_PK PRIMARY KEY(ID);


-- Creating unique constraints

ALTER TABLE EVENTS ADD CONSTRAINT EVNT_BK_UK UNIQUE(EVENT_TYPE,DATA_ID);

----------
-- Changed
----------

ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_BK_UK ;

ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID,DBIN_ID);


-- Creating foreign key constraints

--------
-- Added
--------

ALTER TABLE EVENTS ADD CONSTRAINT EVNT_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA(ID);
ALTER TABLE EVENTS ADD CONSTRAINT EVNT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_CVTE_STORED_ON_FK FOREIGN KEY (CVTE_ID_STORE) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);



-- Creating check constraints


-- Creating indices

--------
-- Added
--------

CREATE INDEX EVNT_DATA_FK_I ON EVENTS (DATA_ID);
CREATE INDEX EVNT_PERS_FK_I ON EVENTS (PERS_ID_REGISTERER);
CREATE INDEX EXDA_CVTE_STORED_ON_FK_I ON EXTERNAL_DATA (CVTE_ID_STORE);
CREATE INDEX PERS_GROU_FK_I ON PERSONS (GROU_ID);
