USE pacs;

START TRANSACTION;

CREATE TABLE SERIES_STATUSES (
  SERIES_ID INTEGER,
  UPLOADED TINYINT NOT NULL DEFAULT 0,
  COMPRESSED TINYINT NOT NULL DEFAULT 0,
  IMPORT_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO SERIES_STATUSES (SERIES_ID, UPLOADED, COMPRESSED, IMPORT_DATE)
SELECT d_ser.ID, MIN(d_obj.UPLOADED_TO_REGIONAL), MIN(d_obj.COMPRESSED), MIN(d_obj.UPLOAD_DATE)
FROM DICOM_SERIES d_ser
INNER JOIN DICOM_OBJECTS d_obj ON d_ser.ID = d_obj.SERIES_ID
GROUP BY d_ser.ID;

ALTER TABLE SERIES_STATUSES
ADD PRIMARY KEY (SERIES_ID),
ADD CONSTRAINT DICOM_SERIES_STATUS_FK
  FOREIGN KEY (SERIES_ID)
  REFERENCES DICOM_SERIES(ID)
  ON DELETE CASCADE;

DROP INDEX OBJECTS_ARCHIVED ON DICOM_OBJECTS;
ALTER TABLE DICOM_OBJECTS DROP COLUMN UPLOAD_DATE, DROP COLUMN COMPRESSED, DROP COLUMN UPLOADED_TO_REGIONAL;
CREATE INDEX SERIES_STORAGE_IDX ON DICOM_OBJECTS(STORAGE_DEVICE_ID, SERIES_ID);

CREATE INDEX SERIES_STATUS_IDX ON SERIES_STATUSES(UPLOADED, COMPRESSED, IMPORT_DATE ASC, SERIES_ID);

ALTER TABLE DICOM_UNREMOVED_PATHS ADD COLUMN ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

COMMIT;
