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

SET NAMES 'utf8';

SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- label
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `label`;


CREATE TABLE `label`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `hierarchy_id` INTEGER  NOT NULL,
  `code` VARCHAR(255)  NOT NULL,
  `filename` VARCHAR(2048)  NOT NULL,
  `format` VARCHAR(32)  NOT NULL,
  `checksum` VARCHAR(40)  NOT NULL,
  `lifetime` INTEGER,
  `is_system` INTEGER default 0 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_code` (`code`),
  INDEX `label_FI_1` (`hierarchy_id`),
  CONSTRAINT `label_FK_1`
    FOREIGN KEY (`hierarchy_id`)
    REFERENCES `hierarchy` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- label_i18n
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `label_i18n`;


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

#-----------------------------------------------------------------------------
#-- sf_guard_group_label
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_group_label`;


CREATE TABLE `sf_guard_group_label`
(
  `sf_guard_group_id` INTEGER  NOT NULL,
  `label_id` INTEGER  NOT NULL,
  PRIMARY KEY (`sf_guard_group_id`,`label_id`),
  CONSTRAINT `sf_guard_group_label_FK_1`
    FOREIGN KEY (`sf_guard_group_id`)
    REFERENCES `sf_guard_group` (`id`)
    ON DELETE CASCADE,
  INDEX `sf_guard_group_label_FI_2` (`label_id`),
  CONSTRAINT `sf_guard_group_label_FK_2`
    FOREIGN KEY (`label_id`)
    REFERENCES `label` (`id`)
    ON DELETE CASCADE
)Type=InnoDB;

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

DROP TABLE IF EXISTS `news`;


CREATE TABLE `news`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `hierarchy_id` INTEGER  NOT NULL,
  `label_id` INTEGER  NOT NULL,
  `sf_guard_user_id` INTEGER  NOT NULL,
  `code` VARCHAR(255)  NOT NULL,
  `available` INTEGER default 1 NOT NULL,
  `expired_at` DATE,
  `filename` VARCHAR(2048)  NOT NULL,
  `format` VARCHAR(32)  NOT NULL,
  `checksum` VARCHAR(40)  NOT NULL,
  `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` (`label_id`),
  CONSTRAINT `news_FK_2`
    FOREIGN KEY (`label_id`)
    REFERENCES `label` (`id`)
    ON DELETE CASCADE,
  INDEX `news_FI_3` (`sf_guard_user_id`),
  CONSTRAINT `news_FK_3`
    FOREIGN KEY (`sf_guard_user_id`)
    REFERENCES `sf_guard_user` (`id`)
    ON DELETE CASCADE
)Type=InnoDB;

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

DROP TABLE IF EXISTS `news_i18n`;

CREATE TABLE `news_i18n`
(
  `id` INTEGER  NOT NULL,
  `culture` VARCHAR(7)  NOT NULL,
  `title` VARCHAR(1024)  NOT NULL,
  `description` VARCHAR(2048),
  `text` 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
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `news_sf_guard_user`;


CREATE TABLE `news_sf_guard_user`
(
  `news_id` INTEGER  NOT NULL,
  `sf_guard_user_id` INTEGER  NOT NULL,
  `read_at` DATETIME,
  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;


#-----------------------------------------------------------------------------
#-- template 
#-----------------------------------------------------------------------------

ALTER TABLE `template`
  ADD `filename` VARCHAR(2048) AFTER `end`,
  ADD `file_format` VARCHAR(32) AFTER `filename`,
  ADD `checksum` VARCHAR(40) AFTER `file_format`,
  ADD `lowres_download` INTEGER default 0 NOT NULL AFTER `available`;


#-----------------------------------------------------------------------------
#-- template_edition
#-----------------------------------------------------------------------------

ALTER TABLE  `template_edition`
  ADD `created_at` DATETIME AFTER `temporary_conf`,
  ADD `updated_at` DATETIME AFTER `created_at`;

#-----------------------------------------------------------------------------
#-- pdf_edition
#-----------------------------------------------------------------------------

ALTER TABLE  `pdf_edition`
  ADD `created_at` DATETIME AFTER `temporary_data`,
  ADD `updated_at` DATETIME AFTER `created_at`;



SET FOREIGN_KEY_CHECKS = 1;
