#
# MySQL upgrade script for upgrade database from version 2.0.45 to 2.0.46
#



#-----------------------------------------------------------------------------
#-- printing_state
#-----------------------------------------------------------------------------

ALTER TABLE `printing_state`
    ADD `is_asked_budget` INTEGER default 0 NOT NULL AFTER `name`,
    ADD `is_consumed_budget` INTEGER default 0 NOT NULL AFTER `is_asked_budget`;

UPDATE `printing_state` SET `is_asked_budget` = 1
    WHERE code = 'waiting';

UPDATE `printing_state` SET `is_consumed_budget` = 1
    WHERE code = 'valid'
      OR code='in_progress'
      OR code='sent'
      OR code='invoice';


#-----------------------------------------------------------------------------
#-- media
#-----------------------------------------------------------------------------



CREATE TABLE `media`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`hierarchy_id` INTEGER  NOT NULL,
	`is_public` INTEGER default 1 NOT NULL,
	`available` INTEGER default 0 NOT NULL,
	`start` DATE,
	`end` DATE,
	PRIMARY KEY (`id`),
	INDEX `media_FI_1` (`hierarchy_id`),
	CONSTRAINT `media_FK_1`
		FOREIGN KEY (`hierarchy_id`)
		REFERENCES `hierarchy` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- media_i18n
#-----------------------------------------------------------------------------



CREATE TABLE `media_i18n`
(
	`id` INTEGER  NOT NULL,
	`culture` VARCHAR(7)  NOT NULL,
	`name` VARCHAR(256)  NOT NULL,
	`description` TEXT,
	`sf_guard_user_id` INTEGER  NOT NULL,
	`filename` VARCHAR(2048),
	`filesize` INTEGER,
	`file_format` VARCHAR(255),
	`checksum` VARCHAR(40)  NOT NULL,
	`preview_filename` VARCHAR(2048),
	`preview_format` VARCHAR(32),
	`preview_checksum` VARCHAR(40),
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`,`culture`),
	CONSTRAINT `media_i18n_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `media` (`id`)
		ON DELETE CASCADE,
	INDEX `media_i18n_FI_2` (`sf_guard_user_id`),
	CONSTRAINT `media_i18n_FK_2`
		FOREIGN KEY (`sf_guard_user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- media_convertion
#-----------------------------------------------------------------------------



CREATE TABLE `media_convertion`
(
	`media_id` INTEGER  NOT NULL,
	`media_culture` VARCHAR(7)  NOT NULL,
	`mime_type` VARCHAR(256)  NOT NULL,
	`extension` VARCHAR(6)  NOT NULL,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`media_id`,`media_culture`),
	CONSTRAINT `media_convertion_FK_1`
		FOREIGN KEY (`media_id`,`media_culture`)
		REFERENCES `media_i18n` (`id`,`culture`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- media_sf_guard_group
#-----------------------------------------------------------------------------



CREATE TABLE `media_sf_guard_group`
(
	`media_id` INTEGER  NOT NULL,
	`sf_guard_group_id` INTEGER  NOT NULL,
	PRIMARY KEY (`media_id`,`sf_guard_group_id`),
	CONSTRAINT `media_sf_guard_group_FK_1`
		FOREIGN KEY (`media_id`)
		REFERENCES `media` (`id`)
		ON DELETE CASCADE,
	INDEX `media_sf_guard_group_FI_2` (`sf_guard_group_id`),
	CONSTRAINT `media_sf_guard_group_FK_2`
		FOREIGN KEY (`sf_guard_group_id`)
		REFERENCES `sf_guard_group` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- media_tpl_tag
#-----------------------------------------------------------------------------



CREATE TABLE `media_tpl_tag`
(
	`media_id` INTEGER  NOT NULL,
	`tpl_tag_id` INTEGER  NOT NULL,
	PRIMARY KEY (`media_id`,`tpl_tag_id`),
	CONSTRAINT `media_tpl_tag_FK_1`
		FOREIGN KEY (`media_id`)
		REFERENCES `media` (`id`)
		ON DELETE CASCADE,
	INDEX `media_tpl_tag_FI_2` (`tpl_tag_id`),
	CONSTRAINT `media_tpl_tag_FK_2`
		FOREIGN KEY (`tpl_tag_id`)
		REFERENCES `tpl_tag` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;


#-----------------------------------------------------------------------------
#-- Adding new credentials
#-----------------------------------------------------------------------------
INSERT INTO sf_guard_group(name, description)
    VALUES ('Assets manager', 'People allowed to manage digital assets');

INSERT INTO sf_guard_group(name, description)
    VALUES ('Assets user', 'People allowed to download digital assets');

INSERT INTO sf_guard_permission (name, description) VALUES ('adminMedias', 'allow to manage digital assets');
INSERT INTO sf_guard_permission (name, description) VALUES ('viewMedias', 'allow to download digital assets');

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 = 'adminMedias'
          AND
          (
              sf_guard_group.name = 'admin'
            OR
              sf_guard_group.name = 'Global Administrator'
            OR
              sf_guard_group.name = 'Local Administrator'
            OR
              sf_guard_group.name = 'Assets manager'
          );

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 = 'viewMedias'
          AND
          (
              sf_guard_group.name = 'admin'
            OR
              sf_guard_group.name = 'Global Administrator'
            OR
              sf_guard_group.name = 'Local Administrator'
            OR
              sf_guard_group.name = 'Assets manager'
            OR
              sf_guard_group.name = 'Assets user'
          );


