#
# MySQL upgrade script for upgrade database from version 2.0.41 to 2.0.42
#





#-----------------------------------------------------------------------------
#-- image_state
#-----------------------------------------------------------------------------



CREATE TABLE `image_state`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`position` INTEGER,
	`code` VARCHAR(255)  NOT NULL,
	`name` VARCHAR(255)  NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_code` (`code`),
	UNIQUE KEY `unique_name` (`name`)
)Type=InnoDB;

INSERT INTO image_state (position, code, name) VALUES (100, 'draft', 'Draft');
INSERT INTO image_state (position, code, name) VALUES (200, 'waiting', 'Waiting');
INSERT INTO image_state (position, code, name) VALUES (300, 'invalid', 'Invalid');
INSERT INTO image_state (position, code, name) VALUES (400, 'for_creator_only', 'For creator only');
INSERT INTO image_state (position, code, name) VALUES (500, 'for_everyone', 'For everyone');

#-----------------------------------------------------------------------------
#-- image_history
#-----------------------------------------------------------------------------



CREATE TABLE `image_history`
(
	`id` INTEGER  NOT NULL,
	`image_id` INTEGER  NOT NULL,
	`user_history_id` INTEGER  NOT NULL,
	`comment` TEXT,
	PRIMARY KEY (`id`),
	INDEX `image_history_FI_1` (`image_id`),
	CONSTRAINT `image_history_FK_1`
		FOREIGN KEY (`image_id`)
		REFERENCES `image` (`id`)
		ON DELETE CASCADE,
	INDEX `image_history_FI_2` (`user_history_id`),
	CONSTRAINT `image_history_FK_2`
		FOREIGN KEY (`user_history_id`)
		REFERENCES `user_history` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- image_comment
#-----------------------------------------------------------------------------



CREATE TABLE `image_comment`
(
	`id` INTEGER  NOT NULL,
	`image_id` INTEGER  NOT NULL,
	`sf_guard_user_id` INTEGER,
	`user_history_id` INTEGER  NOT NULL,
	`reply_to_image_comment_id` INTEGER,
	`is_read` INTEGER default 0 NOT NULL,
	`comment` TEXT,
	PRIMARY KEY (`id`),
	INDEX `image_comment_FI_1` (`image_id`),
	CONSTRAINT `image_comment_FK_1`
		FOREIGN KEY (`image_id`)
		REFERENCES `image` (`id`)
		ON DELETE CASCADE,
	INDEX `image_comment_FI_2` (`sf_guard_user_id`),
	CONSTRAINT `image_comment_FK_2`
		FOREIGN KEY (`sf_guard_user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON DELETE SET NULL,
	INDEX `image_comment_FI_3` (`user_history_id`),
	CONSTRAINT `image_comment_FK_3`
		FOREIGN KEY (`user_history_id`)
		REFERENCES `user_history` (`id`),
	INDEX `image_comment_FI_4` (`reply_to_image_comment_id`),
	CONSTRAINT `image_comment_FK_4`
		FOREIGN KEY (`reply_to_image_comment_id`)
		REFERENCES `image_comment` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;


#-----------------------------------------------------------------------------
#-- print_job_image
#-----------------------------------------------------------------------------



CREATE TABLE `print_job_image`
(
	`print_job_id` INTEGER  NOT NULL,
	`template_image_library_id` INTEGER  NOT NULL,
	`image_id` INTEGER  NOT NULL,
	UNIQUE KEY `unique_print_job_image` (`print_job_id`, `template_image_library_id`, `image_id`),
	CONSTRAINT `print_job_image_FK_1`
		FOREIGN KEY (`print_job_id`)
		REFERENCES `print_job` (`id`)
		ON DELETE CASCADE,
	INDEX `print_job_image_FI_2` (`template_image_library_id`),
	CONSTRAINT `print_job_image_FK_2`
		FOREIGN KEY (`template_image_library_id`)
		REFERENCES `template_image_library` (`id`)
		ON DELETE CASCADE,
	INDEX `print_job_image_FI_3` (`image_id`),
	CONSTRAINT `print_job_image_FK_3`
		FOREIGN KEY (`image_id`)
		REFERENCES `image` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;


#-----------------------------------------------------------------------------
#-- image_library
#-----------------------------------------------------------------------------

ALTER TABLE `image_library`
  ADD COLUMN `default_image_state_id` INTEGER AFTER `new_content_by_users`,
  ADD COLUMN `force_cmyk` INTEGER default 1 NOT NULL AFTER `default_image_state_id`,
  ADD COLUMN `force_resolution` INTEGER default 0 NOT NULL AFTER `force_cmyk`,
  ADD COLUMN `force_ratio` INTEGER default 0 NOT NULL AFTER `force_resolution`,
  ADD CONSTRAINT `image_library_FK_2`
      FOREIGN KEY (`default_image_state_id`)
      REFERENCES `image_state` (`id`);


#-----------------------------------------------------------------------------
#-- image
#-----------------------------------------------------------------------------


ALTER TABLE `image`
  ADD COLUMN `image_state_id` INTEGER AFTER `crop_method_id`,
  ADD COLUMN `sf_guard_user_id` INTEGER AFTER `image_state_id`,
  ADD COLUMN `user_history_id` INTEGER AFTER `sf_guard_user_id`,
  ADD INDEX `image_FI_4` (`image_state_id`),
  ADD CONSTRAINT `image_FK_4`
      FOREIGN KEY (`image_state_id`)
      REFERENCES `image_state` (`id`),
  ADD INDEX `image_FI_5` (`sf_guard_user_id`),
  ADD CONSTRAINT `image_FK_5`
      FOREIGN KEY (`sf_guard_user_id`)
      REFERENCES `sf_guard_user` (`id`)
      ON DELETE SET NULL,
  ADD INDEX `image_FI_6` (`user_history_id`),
  ADD CONSTRAINT `image_FK_6`
      FOREIGN KEY (`user_history_id`)
      REFERENCES `user_history` (`id`);

UPDATE `image` SET `code` = `checksum`;

UPDATE `image` SET image_state_id = (SELECT id from image_state where code = 'for_everyone');

#-----------------------------------------------------------------------------
#-- image_i18n
#-----------------------------------------------------------------------------


ALTER TABLE `image_i18n`
  ADD COLUMN `description` TEXT AFTER `name`;

#-----------------------------------------------------------------------------
#-- password_history
#-----------------------------------------------------------------------------



CREATE TABLE `password_history`
(
  `user_id` INTEGER  NOT NULL,
  `algorithm` VARCHAR(128) default 'sha1' NOT NULL,
  `salt` VARCHAR(128)  NOT NULL,
  `password` VARCHAR(128)  NOT NULL,
  `created_at` DATETIME,
  INDEX `password_history_FI_1` (`user_id`),
  CONSTRAINT `password_history_FK_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `sf_guard_user` (`id`)
    ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- url
#-----------------------------------------------------------------------------



CREATE TABLE `url`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`sf_guard_user_id` INTEGER  NOT NULL,
	`print_job_id` INTEGER,
	`code` VARCHAR(128)  NOT NULL,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_code` (`code`),
	INDEX `url_FI_1` (`sf_guard_user_id`),
	CONSTRAINT `url_FK_1`
		FOREIGN KEY (`sf_guard_user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON DELETE CASCADE,
	INDEX `url_FI_2` (`print_job_id`),
	CONSTRAINT `url_FK_2`
		FOREIGN KEY (`print_job_id`)
		REFERENCES `print_job` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- Adding new credentials
#-----------------------------------------------------------------------------

INSERT INTO sf_guard_permission (name, description) VALUES ('reviewImage', 'allow to review user proposed images');

INSERT INTO sf_guard_group_permission (group_id, permission_id)
        SELECT sf_guard_group.id, sf_guard_permission.id
        FROM sf_guard_group, sf_guard_permission
        WHERE
            sf_guard_permission.name = 'reviewImage'
          AND
            sf_guard_group.name = 'admin';

INSERT INTO sf_guard_group_permission (group_id, permission_id)
        SELECT sf_guard_group.id, sf_guard_permission.id
        FROM sf_guard_group, sf_guard_permission
        WHERE
            sf_guard_permission.name = 'reviewImage'
          AND
            sf_guard_group.name = 'Global Administrator';

INSERT INTO sf_guard_group_permission (group_id, permission_id)
        SELECT sf_guard_group.id, sf_guard_permission.id
        FROM sf_guard_group, sf_guard_permission
        WHERE
            sf_guard_permission.name = 'reviewImage'
          AND
            sf_guard_group.name = 'Local Administrator';

INSERT INTO sf_guard_group_permission (group_id, permission_id)
        SELECT sf_guard_group.id, sf_guard_permission.id
        FROM sf_guard_group, sf_guard_permission
        WHERE
            sf_guard_permission.name = 'reviewImage'
          AND
            sf_guard_group.name = 'Images manager';



