#
# MySQL upgrade script for upgrade database from version 2.0.12 to 2.0.13
# 

#-----------------------------------------------------------------------------
#-- sf_guard_user_profile
#-----------------------------------------------------------------------------

ALTER TABLE `sf_guard_user_profile` ADD `validation_level` VARCHAR(255) AFTER `cellphone`;

UPDATE sf_guard_user_profile
    SET validation_level = 'review'
    WHERE
      sf_guard_user_profile.user_id
      IN (SELECT sf_guard_user.id
        FROM sf_guard_user
        WHERE sf_guard_user.username = 'admin'
        OR sf_guard_user.username = 'global_admin'
        OR sf_guard_user.username = 'local_admin'
        OR sf_guard_user.username = 'reviewer');

#-----------------------------------------------------------------------------
#-- user_history
#-----------------------------------------------------------------------------

ALTER TABLE `user_history` ADD `validation_level` VARCHAR(255) AFTER `cellphone`;

#-----------------------------------------------------------------------------
#-- print_target
#-----------------------------------------------------------------------------



CREATE TABLE `print_target`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`code` VARCHAR(255)  NOT NULL,
	`name` VARCHAR(255)  NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_print_target_code` (`code`),
	UNIQUE KEY `unique_print_target_name` (`name`)
)Type=InnoDB;

INSERT INTO print_target(code, name) VALUES('local', 'Local printing');
INSERT INTO print_target(code, name) VALUES('printer', 'Printer');

#-----------------------------------------------------------------------------
#-- workflow
#-----------------------------------------------------------------------------



CREATE TABLE `workflow`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255)  NOT NULL,
	`hierarchy_id` INTEGER  NOT NULL,
	`print_target_id` INTEGER  NOT NULL,
	`validation_levels` VARCHAR(1024),
	PRIMARY KEY (`id`),
	INDEX `workflow_FI_1` (`hierarchy_id`),
	CONSTRAINT `workflow_FK_1`
		FOREIGN KEY (`hierarchy_id`)
		REFERENCES `hierarchy` (`id`)
		ON DELETE CASCADE,
	INDEX `workflow_FI_2` (`print_target_id`),
	CONSTRAINT `workflow_FK_2`
		FOREIGN KEY (`print_target_id`)
		REFERENCES `print_target` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

INSERT INTO workflow (name, hierarchy_id, print_target_id, validation_levels)
    SELECT 'Local printing without validation', hierarchy.id, print_target.id, NULL
    FROM hierarchy, print_target
    WHERE
      hierarchy.hierarchy_id IS NULL
      AND print_target.code = 'local';

INSERT INTO workflow (name, hierarchy_id, print_target_id, validation_levels)
    SELECT 'Professional printer printing with 1 level validation', hierarchy.id, print_target.id, 'review'
    FROM hierarchy, print_target
    WHERE
      hierarchy.hierarchy_id IS NULL
      AND print_target.code = 'printer';

#-----------------------------------------------------------------------------
#-- template_workflow
#-----------------------------------------------------------------------------



CREATE TABLE `template_workflow`
(
	`template_id` INTEGER  NOT NULL,
	`workflow_id` INTEGER  NOT NULL,
	PRIMARY KEY (`template_id`,`workflow_id`),
	CONSTRAINT `template_workflow_FK_1`
		FOREIGN KEY (`template_id`)
		REFERENCES `template` (`id`)
		ON DELETE CASCADE,
	INDEX `template_workflow_FI_2` (`workflow_id`),
	CONSTRAINT `template_workflow_FK_2`
		FOREIGN KEY (`workflow_id`)
		REFERENCES `workflow` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

INSERT INTO template_workflow(template_id, workflow_id)
    SELECT template.id, workflow.id
    FROM template, workflow JOIN print_target ON (workflow.print_target_id = print_target.id)
    WHERE
      print_target.code = 'printer';

#-----------------------------------------------------------------------------
#-- print_job
#-----------------------------------------------------------------------------

ALTER TABLE `print_job`
    ADD `workflow_id` INTEGER AFTER `printer_history_id`,
    ADD INDEX `print_job_FI_10` (`workflow_id`),
    ADD CONSTRAINT `print_job_FK_10`
      FOREIGN KEY (`workflow_id`)
      REFERENCES `workflow` (`id`),
    ADD `current_validation_level` VARCHAR(255) AFTER `workflow_id`;

UPDATE print_job, workflow JOIN print_target ON (workflow.print_target_id = print_target.id)
    SET print_job.workflow_id = workflow.id
    WHERE
      print_target.code = 'printer';

UPDATE print_job JOIN printing_state ON (print_job.printing_state_id = printing_state.id)
    SET print_job.current_validation_level = 'review'
    WHERE printing_state.code = 'waiting';

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

ALTER TABLE `printing_state` ADD `name` VARCHAR(255) AFTER `code`;

ALTER TABLE `printing_state` DROP FOREIGN KEY `printing_state_FK_1`;
ALTER TABLE `printing_state` DROP INDEX `printing_state_FI_1`;
ALTER TABLE `printing_state` DROP `hierarchy_id`;

UPDATE printing_state SET name='Invoiced' WHERE code = 'invoice';
UPDATE printing_state SET name='Waiting for validation' WHERE code = 'waiting';
UPDATE printing_state SET name='Invalid' WHERE code = 'invalid';
UPDATE printing_state SET name='Valid' WHERE code = 'valid';
UPDATE printing_state SET name='Canceled by printer' WHERE code = 'canceled_by_printer';
UPDATE printing_state SET name='Printing in progress' WHERE code = 'in_progress';
UPDATE printing_state SET name='Sent' WHERE code = 'sent';

INSERT INTO printing_state(code, name) VALUES ('printable', 'Printable');

ALTER TABLE printing_state CHANGE name name VARCHAR(255) NOT NULL;

DROP TABLE printing_state_i18n;

#-----------------------------------------------------------------------------
#-- Ajout des nouvelles permissions.
#-----------------------------------------------------------------------------

INSERT INTO sf_guard_permission (name, description) VALUES ('manageWorkflow', 'allow to manage workflows');

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 = 'manageWorkflow'
          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 = 'manageWorkflow'
          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 = 'editTemplateConf'
          AND
            sf_guard_group.name = 'admin';


