-- Migration from 021 to 022

-- create image_definitions table

-- COPY data to image definitions 

-- add new column to image_zooom_level_transformations (image_definition)

-- migrate image_zoom_level_transformations data

-- drop unused column in image_zoom_level_transformations

-- create relevant indices


-- create image_zoom_levels for acquired images that have no zoom level defined

-- create zoom levels for missing original images

INSERT INTO IMAGE_ZOOM_LEVELS(IS_ORIGINAL, CONTAINER_DATASET_ID, PATH, PHYSICAL_DATASET_PERM_ID)
SELECT 't', DS.ID, '', DS.PERM_ID FROM IMAGE_DATA_SETS DS WHERE 
DS.ID IN (SELECT DISTINCT DS_ID FROM CHANNELS CH WHERE CH.ID IN (SELECT CHANNEL_ID FROM ACQUIRED_IMAGES)) AND
DS.ID NOT IN (SELECT DISTINCT CONTAINER_DATASET_ID FROM IMAGE_ZOOM_LEVELS WHERE IS_ORIGINAL = 't');

--create zoom levels for missing thumbnail images

INSERT INTO IMAGE_ZOOM_LEVELS(IS_ORIGINAL, CONTAINER_DATASET_ID, PATH, PHYSICAL_DATASET_PERM_ID)
SELECT 'f', DS.ID, '', DS.PERM_ID FROM IMAGE_DATA_SETS DS WHERE 
DS.ID IN (SELECT DISTINCT DS_ID FROM CHANNELS CH WHERE CH.ID IN (SELECT DISTINCT CHANNEL_ID FROM ACQUIRED_IMAGES WHERE THUMBNAIL_ID IS NOT NULL)) AND
DS.ID NOT IN (SELECT DISTINCT CONTAINER_DATASET_ID FROM IMAGE_ZOOM_LEVELS WHERE IS_ORIGINAL = 'f');

-- 1 create image_definitions table

CREATE TABLE IMAGE_DEFINITIONS (
  ID BIGSERIAL NOT NULL,
  
  ZOOM_LEVEL_ID TECH_ID NOT NULL,
  CHANNEL_ID TECH_ID NOT NULL,
  
  PRIMARY KEY(ID),
  CONSTRAINT FK_IMAGE_DEFINITIONS_1 FOREIGN KEY (ZOOM_LEVEL_ID) REFERENCES IMAGE_ZOOM_LEVELS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_IMAGE_DEFINITIONS_2 FOREIGN KEY (CHANNEL_ID) REFERENCES CHANNELS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 2 COPY data to image definitions 

insert into image_definitions(channel_id, zoom_level_id) (select ch.id, l.id from channels ch, image_zoom_levels l where ch.ds_id = l.container_dataset_id);

-- add new column to image_zooom_level_transformations (image_definition)

ALTER TABLE image_zoom_level_transformations ADD COLUMN IMAGE_DEFINITION_ID TECH_ID;
ALTER TABLE image_zoom_level_transformations ADD CONSTRAINT FK_IMAGE_ZOOM_LEVEL_TRANSFORMATIONS_3 FOREIGN KEY (IMAGE_DEFINITION_ID) REFERENCES IMAGE_DEFINITIONS (ID);

-- migrate image_zoom_level_transformations data

update image_zoom_level_transformations t set image_definition_id = (select id from image_definitions d where t.channel_id = d.channel_id and t.zoom_level_id = d.zoom_level_id);

-- drop unused column in image_zoom_level_transformations

ALTER TABLE image_zoom_level_transformations DROP CONSTRAINT FK_IMAGE_ZOOM_LEVEL_TRANSFORMATIONS_1;
ALTER TABLE image_zoom_level_transformations DROP CONSTRAINT FK_IMAGE_ZOOM_LEVEL_TRANSFORMATIONS_2;
ALTER TABLE image_zoom_level_transformations DROP COLUMN ZOOM_LEVEL_ID;
ALTER TABLE image_zoom_level_transformations DROP COLUMN CHANNEL_ID;

-- create relevant indices

CREATE INDEX IMAGE_DEFINITIONS_ZLID_IDX ON IMAGE_DEFINITIONS(ZOOM_LEVEL_ID);
CREATE INDEX IMAGE_ZOOM_LEVEL_TRANSFORMATIONS_IDEFID_IDX ON IMAGE_ZOOM_LEVEL_TRANSFORMATIONS(IMAGE_DEFINITION_ID);



-- modify the acquired images table

ALTER TABLE ACQUIRED_IMAGES RENAME TO ACQUIRED_IMAGES_OLD;
    
CREATE TABLE ACQUIRED_IMAGES (
    ID BIGSERIAL  NOT NULL,
	IMG_ID TECH_ID,
	IMAGE_TRANSFORMER_FACTORY BYTEA,

    CHANNEL_STACK_ID  TECH_ID NOT NULL,
    IMAGE_DEFINITION_ID  TECH_ID NOT NULL,

    PRIMARY KEY (ID),
    CONSTRAINT FK_ACQUIRED_IMAGES_1 FOREIGN KEY (CHANNEL_STACK_ID) REFERENCES CHANNEL_STACKS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_ACQUIRED_IMAGES_2 FOREIGN KEY (IMAGE_DEFINITION_ID) REFERENCES IMAGE_DEFINITIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_ACQUIRED_IMAGES_3 FOREIGN KEY (IMG_ID) REFERENCES IMAGES (ID) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE INDEX ACQUIRED_IMAGES_CHANNEL_STACK_IDX ON ACQUIRED_IMAGES(CHANNEL_STACK_ID);
CREATE INDEX ACQUIRED_IMAGES_IMAGE_DEFINITION_IDX ON ACQUIRED_IMAGES(IMAGE_DEFINITION_ID);
CREATE INDEX ACQUIRED_IMAGES_IMG_IDX ON ACQUIRED_IMAGES(IMG_ID);


insert into ACQUIRED_IMAGES (IMG_ID, IMAGE_TRANSFORMER_FACTORY, CHANNEL_STACK_ID, IMAGE_DEFINITION_ID)
	select img_id, image_transformer_factory, channel_stack_id, d.id image_definition_id  from image_definitions d, ACQUIRED_IMAGES_OLD a, image_zoom_levels l 
	where l.id = zoom_level_id and l.is_original = 't' and d.channel_id = a.channel_id;

insert into ACQUIRED_IMAGES (IMG_ID, IMAGE_TRANSFORMER_FACTORY, CHANNEL_STACK_ID, IMAGE_DEFINITION_ID)
	select thumbnail_id, image_transformer_factory, channel_stack_id, d.id image_definition_id  from image_definitions d, ACQUIRED_IMAGES_OLD a, image_zoom_levels l 
	where l.id = zoom_level_id and l.is_original = 'f' and d.channel_id = a.channel_id;

drop table ACQUIRED_IMAGES_OLD;
