-- Add NOT NULL constraint to FILES.USER_ID as we are now prepared to handle the case of user deletion

UPDATE FILES SET USER_ID = (SELECT MIN(ID) from USERS) WHERE USER_ID IS NULL;
ALTER TABLE FILES ALTER COLUMN USER_ID SET NOT NULL;

-- Along the same lines, do not allow to delete a user that still owns files or other users.

ALTER TABLE FILES DROP CONSTRAINT FILE_USER_FK;
ALTER TABLE FILES ADD CONSTRAINT FILE_USER_FK FOREIGN KEY (USER_ID) REFERENCES USERS(ID) ON DELETE NO ACTION;
ALTER TABLE USERS DROP CONSTRAINT USER_REGISTRATOR_FK;
ALTER TABLE USERS ADD CONSTRAINT USER_REGISTRATOR_FK FOREIGN KEY (USER_ID_REGISTRATOR) REFERENCES USERS(ID) ON DELETE NO ACTION;

-- Remove column USERS.IS_PERMANENT as it duplicates USERS.EXPIRATION_DATE

ALTER TABLE USERS DROP COLUMN IS_PERMANENT;

-- Switch file and user retention from hours to days

UPDATE QUOTA_GROUPS 
	SET FILE_RETENTION = FILE_RETENTION / 24, USER_RETENTION = USER_RETENTION / 24;
UPDATE QUOTA_GROUPS SET FILE_RETENTION = 1 WHERE FILE_RETENTION = 0;
UPDATE QUOTA_GROUPS SET USER_RETENTION = 1 WHERE USER_RETENTION = 0;

-- Switch domain duration (and QUOTA_GROUPS.USER_RETENTION and QUOTA_GROUP.FILE_RETENTION) 
-- from BIGINT to INTEGER

ALTER TABLE QUOTA_GROUPS ALTER COLUMN FILE_RETENTION TYPE INTEGER;
ALTER TABLE QUOTA_GROUPS ALTER COLUMN USER_RETENTION TYPE INTEGER;

DROP DOMAIN DURATION;
CREATE DOMAIN DURATION AS INTEGER;

ALTER TABLE QUOTA_GROUPS ALTER COLUMN FILE_RETENTION TYPE DURATION;
ALTER TABLE QUOTA_GROUPS ALTER COLUMN USER_RETENTION TYPE DURATION;

-- Add FILES.REGISTRATOR_CODE and set it to the value referred to by the FILES.USER_ID

ALTER TABLE FILES ADD COLUMN REGISTRATOR_CODE USER_CODE;
UPDATE FILES F SET REGISTRATOR_CODE = (SELECT USER_CODE FROM USERS U WHERE U.ID = F.USER_ID);
UPDATE FILES SET REGISTRATOR_CODE = 'UNKNOWN' WHERE REGISTRATOR_CODE IS NULL;
ALTER TABLE FILES ALTER COLUMN REGISTRATOR_CODE SET NOT NULL;