#
# MySQL upgrade script for upgrade database from version 2.0.31 to 2.0.32
#


#-----------------------------------------------------------------------------
#-- extranet_link
#-----------------------------------------------------------------------------



CREATE TABLE `extranet_link`
(
        `id` INTEGER  NOT NULL AUTO_INCREMENT,
        `url` VARCHAR(4096)  NOT NULL,
        `filename` VARCHAR(2048)  NOT NULL,
        `format` VARCHAR(32)  NOT NULL,
        `checksum` VARCHAR(40)  NOT NULL,
        `position` FLOAT,
        `hierarchy_id` INTEGER  NOT NULL,
        PRIMARY KEY (`id`),
        INDEX `extranet_link_FI_1` (`hierarchy_id`),
        CONSTRAINT `extranet_link_FK_1`
                FOREIGN KEY (`hierarchy_id`)
                REFERENCES `hierarchy` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- extranet_link_i18n
#-----------------------------------------------------------------------------



CREATE TABLE `extranet_link_i18n`
(
        `id` INTEGER  NOT NULL,
        `culture` VARCHAR(7)  NOT NULL,
        `title` VARCHAR(256)  NOT NULL,
        `description` TEXT,
        PRIMARY KEY (`id`,`culture`),
        CONSTRAINT `extranet_link_i18n_FK_1`
                FOREIGN KEY (`id`)
                REFERENCES `extranet_link` (`id`)
                ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- news_category
#-----------------------------------------------------------------------------



CREATE TABLE `news_category`
(
	`id` INTEGER  NOT NULL,
	`hierarchy_id` INTEGER  NOT NULL,
	`code` VARCHAR(255)  NOT NULL,
	`filename` VARCHAR(2048),
	`expiration_delay` INTEGER,
	`is_system` INTEGER default 0 NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_code` (`code`),
	INDEX `news_category_FI_1` (`hierarchy_id`),
	CONSTRAINT `news_category_FK_1`
		FOREIGN KEY (`hierarchy_id`)
		REFERENCES `hierarchy` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- news_category_i18n
#-----------------------------------------------------------------------------



CREATE TABLE `news_category_i18n`
(
	`id` INTEGER  NOT NULL,
	`culture` VARCHAR(7)  NOT NULL,
	`name` VARCHAR(256)  NOT NULL,
	`description` TEXT,
	PRIMARY KEY (`id`,`culture`),
	CONSTRAINT `news_category_i18n_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `news_category` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_group_news_category
#-----------------------------------------------------------------------------



CREATE TABLE `sf_guard_group_news_category`
(
	`sf_guard_group_id` INTEGER  NOT NULL,
	`news_category_id` INTEGER  NOT NULL,
	PRIMARY KEY (`sf_guard_group_id`,`news_category_id`),
	CONSTRAINT `sf_guard_group_news_category_FK_1`
		FOREIGN KEY (`sf_guard_group_id`)
		REFERENCES `sf_guard_group` (`id`)
		ON DELETE CASCADE,
	INDEX `sf_guard_group_news_category_FI_2` (`news_category_id`),
	CONSTRAINT `sf_guard_group_news_category_FK_2`
		FOREIGN KEY (`news_category_id`)
		REFERENCES `news_category` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- news
#-----------------------------------------------------------------------------



CREATE TABLE `news`
(
	`id` INTEGER  NOT NULL,
	`hierarchy_id` INTEGER  NOT NULL,
	`news_category_id` INTEGER  NOT NULL,
	`user_history_id` INTEGER,
	`code` VARCHAR(255)  NOT NULL,
	`available` INTEGER default 1 NOT NULL,
	`start_at` DATE,
	`end_at` DATE,
	`filename` VARCHAR(2048),
	`url` VARCHAR(4096),
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_code` (`code`),
	INDEX `news_FI_1` (`hierarchy_id`),
	CONSTRAINT `news_FK_1`
		FOREIGN KEY (`hierarchy_id`)
		REFERENCES `hierarchy` (`id`),
	INDEX `news_FI_2` (`news_category_id`),
	CONSTRAINT `news_FK_2`
		FOREIGN KEY (`news_category_id`)
		REFERENCES `news_category` (`id`)
		ON DELETE CASCADE,
	INDEX `news_FI_3` (`user_history_id`),
	CONSTRAINT `news_FK_3`
		FOREIGN KEY (`user_history_id`)
		REFERENCES `user_history` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- news_i18n
#-----------------------------------------------------------------------------



CREATE TABLE `news_i18n`
(
	`id` INTEGER  NOT NULL,
	`culture` VARCHAR(7)  NOT NULL,
	`title` VARCHAR(1024)  NOT NULL,
	`summary` VARCHAR(2048),
	`contents` TEXT,
	PRIMARY KEY (`id`,`culture`),
	CONSTRAINT `news_i18n_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `news` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- news_sf_guard_user
#-----------------------------------------------------------------------------



CREATE TABLE `news_sf_guard_user`
(
	`news_id` INTEGER  NOT NULL,
	`sf_guard_user_id` INTEGER  NOT NULL,
	PRIMARY KEY (`news_id`,`sf_guard_user_id`),
	CONSTRAINT `news_sf_guard_user_FK_1`
		FOREIGN KEY (`news_id`)
		REFERENCES `news` (`id`)
		ON DELETE CASCADE,
	INDEX `news_sf_guard_user_FI_2` (`sf_guard_user_id`),
	CONSTRAINT `news_sf_guard_user_FK_2`
		FOREIGN KEY (`sf_guard_user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

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

ALTER TABLE `image`
  ADD `code` VARCHAR(64) AFTER `image_library_id`,
  ADD `source_updated` INTEGER default 0 NOT NULL,
  ADD `url` VARCHAR(4096),
  ADD `url_updated_at` DATETIME,
  ADD `url_format` VARCHAR(32),
  ADD `url_checksum` VARCHAR(40),
  ADD `url_width` INTEGER,
  ADD `url_height` INTEGER,
  ADD `url_colorspace` VARCHAR(32),
  ADD `created_at` DATETIME,
  ADD `updated_at` DATETIME,
  ADD UNIQUE KEY `unique_code_image_library` (`image_library_id`, `code`);

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


