----------------------------------------------------------------------------------------------
--  File: migration-018-019.sql
--
-- 
--  This script enables the migration of the database schema from 018 to 019.
-- 
----------------------------------------------------------------------------------------------



--=================================
-- Create New Objects
--=================================

-- Domains

CREATE DOMAIN DESCRIPTION_1000 AS VARCHAR(1000);
CREATE DOMAIN AUTHORIZATION_ROLE as CODE CHECK (VALUE IN ('ADMIN', 'USER', 'OBSERVER', 'ETL_SERVER')) NOT NULL;


-- Tables

CREATE TABLE DATABASE_INSTANCES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,IS_ORIGINAL_SOURCE BOOLEAN_CHAR NOT NULL DEFAULT 'F',REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE ROLE_ASSIGNMENTS (ID TECH_ID NOT NULL,ROLE_CODE AUTHORIZATION_ROLE,GROU_ID TECH_ID,DBIN_ID TECH_ID,PERS_ID_GRANTEE TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);

-- Columns 

-- Create the new DBIN_ID columns initially as NULLable!

ALTER TABLE CONTROLLED_VOCABULARIES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE EXPERIMENT_TYPES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE EXPERIMENTS ADD COLUMN DESCRIPTION DESCRIPTION_250;
ALTER TABLE EXPERIMENTS ADD COLUMN IS_PUBLIC BOOLEAN_CHAR NOT NULL DEFAULT 'F';
ALTER TABLE FILE_FORMAT_TYPES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE GROUPS ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE GROUPS ADD COLUMN GROU_ID_PARENT TECH_ID;
ALTER TABLE GROUPS ADD COLUMN PERS_ID_LEADER TECH_ID;
ALTER TABLE GROUPS ADD COLUMN DESCRIPTION DESCRIPTION_250;
ALTER TABLE GROUPS ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE GROUPS ADD COLUMN PERS_ID_REGISTERER TECH_ID;
ALTER TABLE MATERIALS ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE MATERIAL_TYPES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE OBSERVABLE_TYPES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE PERSONS ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE PERSONS ADD COLUMN PERS_ID_REGISTERER TECH_ID;
ALTER TABLE PERSONS ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE PROCEDURE_TYPES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE PROJECTS ADD COLUMN PERS_ID_LEADER TECH_ID;
ALTER TABLE PROJECTS ADD COLUMN PERS_ID_REGISTERER TECH_ID;
ALTER TABLE PROJECTS ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE PROJECTS ADD COLUMN DESCRIPTION DESCRIPTION_1000;
ALTER TABLE PROPERTY_TYPES ADD COLUMN DBIN_ID TECH_ID;
ALTER TABLE SAMPLES ADD COLUMN DBIN_ID TECH_ID ;
ALTER TABLE SAMPLES ADD COLUMN GROU_ID TECH_ID ;
ALTER TABLE SAMPLE_TYPES ADD COLUMN DBIN_ID TECH_ID;


-- Primary Key Constraints

ALTER TABLE DATABASE_INSTANCES ADD CONSTRAINT DBIN_PK PRIMARY KEY(ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PK PRIMARY KEY(ID);

-- Unique Key Constraints

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

-- Foreign Key Constraints

ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE GROUPS ADD CONSTRAINT GROU_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE GROUPS ADD CONSTRAINT GROU_GROU_FK FOREIGN KEY (GROU_ID_PARENT) REFERENCES GROUPS(ID);
ALTER TABLE GROUPS ADD CONSTRAINT GROU_PERS_FK_LEADER FOREIGN KEY (PERS_ID_LEADER) REFERENCES PERSONS(ID);
ALTER TABLE GROUPS ADD CONSTRAINT GROU_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_LEADER FOREIGN KEY (PERS_ID_LEADER) REFERENCES PERSONS(ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PERS_FK_GRANTEE FOREIGN KEY (PERS_ID_GRANTEE) REFERENCES PERSONS(ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);


-- Sequences

CREATE SEQUENCE DATABASE_INSTANCE_ID_SEQ;
CREATE SEQUENCE ROLE_ASSIGNMENT_ID_SEQ;

-- Indices

CREATE INDEX EXDA_CVTE_FK_I ON EXTERNAL_DATA (CVTE_ID_STOR_FMT);
CREATE INDEX GROU_DBIN_FK_I ON GROUPS (DBIN_ID);
CREATE INDEX GROU_GROU_FK_I ON GROUPS (GROU_ID_PARENT);
CREATE INDEX GROU_PERS_FK_I ON GROUPS (PERS_ID_LEADER);
CREATE INDEX GROU_PERS_REGISTERED_BY_FK_I ON GROUPS (PERS_ID_REGISTERER);
CREATE INDEX PROJ_PERS_FK_I_LEADER ON PROJECTS (PERS_ID_LEADER);
CREATE INDEX PROJ_PERS_FK_I_REGISTERER ON PROJECTS (PERS_ID_REGISTERER);
CREATE INDEX ROAS_DBIN_FK_I ON ROLE_ASSIGNMENTS (DBIN_ID);
CREATE INDEX ROAS_GROU_FK_I ON ROLE_ASSIGNMENTS (GROU_ID);
CREATE INDEX ROAS_PERS_FK_I_GRANTEE ON ROLE_ASSIGNMENTS (PERS_ID_GRANTEE);
CREATE INDEX ROAS_PERS_FK_I_REGISTERER ON ROLE_ASSIGNMENTS (PERS_ID_REGISTERER);


--====================================
-- Alter Objects
--====================================

-- Domains

ALTER DOMAIN BOOLEAN_CHAR_OR_UNKNOWN SET DEFAULT 'U';

-- Tables

ALTER TABLE PERSONS ALTER COLUMN USER_ID SET NOT NULL;

-- Primary Key Constraints


-- Unique Key Constraints

   -- Drop the Unique Key Constraints
ALTER TABLE CONTROLLED_VOCABULARIES DROP CONSTRAINT COVO_BK_UK ;
ALTER TABLE EXPERIMENT_TYPES DROP CONSTRAINT EXTY_BK_UK ;
ALTER TABLE FILE_FORMAT_TYPES DROP CONSTRAINT FFTY_BK_UK ;
ALTER TABLE GROUPS DROP CONSTRAINT GROU_BK_UK ;
ALTER TABLE MATERIALS DROP CONSTRAINT MATE_BK_UK ;
ALTER TABLE MATERIAL_TYPES DROP CONSTRAINT MATY_BK_UK ;
ALTER TABLE OBSERVABLE_TYPES DROP CONSTRAINT OBTY_BK_UK ;
ALTER TABLE PERSONS DROP CONSTRAINT PERS_BK_UK ;
ALTER TABLE PROCEDURE_TYPES DROP CONSTRAINT PCTY_BK_UK ;
ALTER TABLE PROPERTY_TYPES DROP CONSTRAINT PRTY_BK_UK ;
ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_BK_UK ;
ALTER TABLE SAMPLES DROP CONSTRAINT SAMP_BK_UK ;
ALTER TABLE SAMPLE_TYPES DROP CONSTRAINT SATY_BK_UK ;

-- There was a bug in migration - the constraint was not created. So we drop it only if it exists.
create function remove_maty_uk_constraint() returns void AS $$
begin
   perform *
     FROM information_schema.table_constraints WHERE constraint_name='MATY_UK';
   if found
   then
	ALTER TABLE MATERIAL_TYPES DROP CONSTRAINT MATY_UK ;
   end if;
end;
$$ language 'plpgsql';
select remove_maty_uk_constraint();
drop function remove_maty_uk_constraint();

   -- ReCreate the Unique Key Constraints with the extended columns
ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_BK_UK UNIQUE(CODE,IS_INTERNAL_NAMESPACE,DBIN_ID);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE GROUPS ADD CONSTRAINT GROU_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_BK_UK UNIQUE(CODE,MATY_ID,DBIN_ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_BK_UK UNIQUE(DBIN_ID,USER_ID);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_BK_UK UNIQUE(CODE,DBIN_ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_BK_UK UNIQUE(CODE,IS_INTERNAL_NAMESPACE,DBIN_ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID,DBIN_ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_BK_UK UNIQUE(CODE,DBIN_ID,GROU_ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_BK_UK UNIQUE(CODE,DBIN_ID);



-- Foreign Key Constraints

   -- Rename according to the naming convention
ALTER TABLE EXTERNAL_DATA DROP CONSTRAINT EXDA_STOR_FMT_FK;
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_CVTE_FK FOREIGN KEY (CVTE_ID_STOR_FMT) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);


-- Check Constraints

-- Indexes

   -- Rename according to the naming convention
DROP INDEX DATA_SAMP_DERIVED_FROM_FK_I ;
DROP INDEX DATA_SAMP_FK_I ;
CREATE INDEX DATA_SAMP_FK_I_ACQUIRED_FROM ON DATA (SAMP_ID_ACQUIRED_FROM);
CREATE INDEX DATA_SAMP_FK_I_DERIVED_FROM ON DATA (SAMP_ID_DERIVED_FROM);




--====================================
-- Delete Objects
--====================================

-- Domains

DROP DOMAIN DOUBLE_PRECISION_VALUE;

-- Tables

-- Sequences

-- Indices


--=================================
-- Data Migration
--=================================

INSERT INTO database_instances(
              id
            , code
            , is_original_source)
    VALUES (  nextval('DATABASE_INSTANCE_ID_SEQ')
            , 'SYSTEM_DEFAULT'
            , 'T');


-----------------------------------------------------------------------------------
-- Fill the FK column in all tables that reference the DATABASE_INSTANCES table
-----------------------------------------------------------------------------------

update CONTROLLED_VOCABULARIES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update EXPERIMENT_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update FILE_FORMAT_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update GROUPS  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update MATERIALS  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update MATERIAL_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update OBSERVABLE_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update PERSONS  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update PROCEDURE_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update PROPERTY_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update SAMPLES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');

update SAMPLE_TYPES  
set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');


-----------------------------------------------------------------------------------
-- Fill the FK column in all tables that newly reference the PERSONS table
-----------------------------------------------------------------------------------

update GROUPS  
set PERS_ID_REGISTERER = (select id from persons where user_id = 'system');




-- Columns 

-- Change certain new columns to possess the NOT NULL constraint

ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE EXPERIMENT_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE FILE_FORMAT_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE GROUPS ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE GROUPS ALTER COLUMN PERS_ID_REGISTERER SET NOT NULL;
ALTER TABLE MATERIALS ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE MATERIAL_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE OBSERVABLE_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE PERSONS ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE PROCEDURE_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE PROPERTY_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;
ALTER TABLE SAMPLE_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;

-- Check Constraints

ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_DBIN_GROU_ARC_CK CHECK ((DBIN_ID IS NOT NULL AND GROU_ID IS NULL) OR (DBIN_ID IS NULL AND GROU_ID IS NOT NULL));
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_DBIN_GROU_ARC_CK CHECK ((DBIN_ID IS NOT NULL AND GROU_ID IS NULL) OR (DBIN_ID IS NULL AND GROU_ID IS NOT NULL));
