-- Longer descriptions
ALTER TABLE sample_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE data_set_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE experiment_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE material_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE controlled_vocabularies ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE data_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE file_format_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE locator_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE property_types ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE events ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE events ALTER COLUMN reason TYPE DESCRIPTION_1000;
ALTER TABLE groups ALTER COLUMN description TYPE DESCRIPTION_1000;
ALTER TABLE invalidations ALTER COLUMN reason TYPE DESCRIPTION_1000;


DROP TYPE DESCRIPTION_250;

-- Add label and description to controlled vocabularies

ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD COLUMN label COLUMN_LABEL;
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD COLUMN description DESCRIPTION_1000;

-- Add authorization groups

CREATE TABLE AUTHORIZATION_GROUPS (ID TECH_ID NOT NULL, DBIN_ID TECH_ID NOT NULL, CODE CODE NOT NULL, DESCRIPTION DESCRIPTION_1000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_PK PRIMARY KEY(ID);
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
CREATE SEQUENCE AUTHORIZATION_GROUP_ID_SEQ;

CREATE TABLE AUTHORIZATION_GROUP_PERSONS (AG_ID TECH_ID NOT NULL, PERS_ID TECH_ID NOT NULL);
ALTER TABLE AUTHORIZATION_GROUP_PERSONS ADD CONSTRAINT AGP_PK PRIMARY KEY(PERS_ID,AG_ID);
ALTER TABLE AUTHORIZATION_GROUP_PERSONS ADD CONSTRAINT AGP_AG_FK FOREIGN KEY (AG_ID) REFERENCES AUTHORIZATION_GROUPS(ID);
ALTER TABLE AUTHORIZATION_GROUP_PERSONS ADD CONSTRAINT AGP_PERS_FK FOREIGN KEY (PERS_ID) REFERENCES PERSONS(ID);

ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN PERS_ID_GRANTEE DROP NOT NULL;
ALTER TABLE ROLE_ASSIGNMENTS ADD COLUMN AG_ID_GRANTEE TECH_ID;

ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PE_GROUP_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PE_INSTANCE_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,DBIN_ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_AG_GROUP_BK_UK UNIQUE(AG_ID_GRANTEE,ROLE_CODE,GROU_ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_AG_INSTANCE_BK_UK UNIQUE(AG_ID_GRANTEE,ROLE_CODE,DBIN_ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_AG_PERS_ARC_CK CHECK ((AG_ID_GRANTEE IS NOT NULL AND PERS_ID_GRANTEE IS NULL) OR (AG_ID_GRANTEE IS NULL AND PERS_ID_GRANTEE IS NOT NULL));
CREATE INDEX ROAS_AG_FK_I_GRANTEE ON ROLE_ASSIGNMENTS (AG_ID_GRANTEE);

ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_GROUP_BK_UK; 
ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_INSTANCE_BK_UK;  

GRANT SELECT ON SEQUENCE AUTHORIZATION_GROUP_ID_SEQ TO GROUP OPENBIS_READONLY;
GRANT SELECT ON TABLE AUTHORIZATION_GROUPS TO GROUP OPENBIS_READONLY;
GRANT SELECT ON TABLE AUTHORIZATION_GROUP_PERSONS TO GROUP OPENBIS_READONLY;

-- remove group leader and parent

ALTER TABLE groups DROP COLUMN grou_id_parent;
ALTER TABLE groups DROP COLUMN pers_id_leader;