
-- Database update August 27, 2018


-- Deletes ZoneHighlight index and table
DROP INDEX IF EXISTS idx_zonehighlight_analysis;
DROP TABLE IF EXISTS ZoneHighlight;


-- Creates ZoneGroup table and create index
CREATE TABLE ZoneGroup (
  zoneGroupId bigint GENERATED ALWAYS AS IDENTITY,
  analysisId bigint NOT NULL,
  name varchar(250) NOT NULL,
  zoneIds text NOT NULL, -- [553,557,558,555, etc...]
  groupDefault BOOLEAN NOT NULL DEFAULT FALSE,

  PRIMARY KEY (zoneGroupId),
  FOREIGN KEY (analysisId) REFERENCES Analysis(analysisId)
);
ALTER SEQUENCE ZoneGroup_zoneGroupId_seq RESTART WITH 100;

CREATE INDEX idx_zonegroup_analysis ON Analysis(analysisId);


-- Add ZoneGroup root folder for older analysis
INSERT INTO Folders (analysisId, type, data)
		select distinct analysisId, 'ZoneGroup', '{"caption":"/","id":"no-id","contents":[],"children":[]}'
			from Folders where analysisId not in (select distinct analysisId from Folders where "type" = 'ZoneGroup');