-- Migration from 160 to 161

-- add project_id column
ALTER TABLE ROLE_ASSIGNMENTS ADD COLUMN PROJECT_ID TECH_ID;

-- add project_id column to unique contraints
ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_PE_SPACE_BK_UK;
ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_AG_SPACE_BK_UK;

CREATE UNIQUE INDEX ROAS_PE_SPACE_PROJECT_BK_UK ON ROLE_ASSIGNMENTS (PERS_ID_GRANTEE, ROLE_CODE, coalesce(SPACE_ID,-1), coalesce(PROJECT_ID,-1)); 
CREATE UNIQUE INDEX ROAS_AG_SPACE_PROJECT_BK_UK ON ROLE_ASSIGNMENTS (AG_ID_GRANTEE, ROLE_CODE, coalesce(SPACE_ID,-1), coalesce(PROJECT_ID,-1)); 

-- add project_id foreign key
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PROJECT_FK FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID) ON DELETE CASCADE;

-- add project_id index
CREATE INDEX ROAS_PROJECT_FK_I ON ROLE_ASSIGNMENTS (PROJECT_ID);

-- add space_id and project_id check
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_SPACE_PROJECT_CK CHECK (SPACE_ID IS NULL OR PROJECT_ID IS NULL);
