#
# MySQL upgrade script for upgrade database from version 2.0.38 to 2.0.39
#




#-----------------------------------------------------------------------------
#-- weight
#-----------------------------------------------------------------------------



CREATE TABLE `weight`
(
  `id` INTEGER  NOT NULL,
  PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- tpl_tag_weight
#-----------------------------------------------------------------------------



CREATE TABLE `tpl_tag_weight`
(
  `tpl_tag_id` INTEGER  NOT NULL,
  `weight_id` INTEGER  NOT NULL,
  PRIMARY KEY (`tpl_tag_id`,`weight_id`),
  CONSTRAINT `tpl_tag_weight_FK_1`
    FOREIGN KEY (`tpl_tag_id`)
    REFERENCES `tpl_tag` (`id`)
    ON DELETE CASCADE,
  INDEX `tpl_tag_weight_FI_2` (`weight_id`),
  CONSTRAINT `tpl_tag_weight_FK_2`
    FOREIGN KEY (`weight_id`)
    REFERENCES `weight` (`id`)
    ON DELETE CASCADE
)Type=InnoDB;



#-----------------------------------------------------------------------------
#-- tpl_tag
#-----------------------------------------------------------------------------


ALTER TABLE `tpl_tag`
  ADD COLUMN `filename` VARCHAR(2048)  NULL AFTER `id`,
  ADD COLUMN `format` VARCHAR(32)  NULL AFTER `filename`,
  ADD COLUMN `checksum` VARCHAR(40)  NULL AFTER `format`;

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

ALTER TABLE `print_job`
  ADD COLUMN `delivery_address_id` INTEGER AFTER `points`,
  ADD COLUMN `bill_address_id` INTEGER AFTER `delivery_country`,
  ADD INDEX `print_job_FI_11` (`delivery_address_id`),
  ADD CONSTRAINT `print_job_FK_11`
      FOREIGN KEY (`delivery_address_id`)
      REFERENCES `address` (`id`)
      ON DELETE SET NULL,
  ADD INDEX `print_job_FI_12` (`bill_address_id`),
  ADD CONSTRAINT `print_job_FK_12`
      FOREIGN KEY (`bill_address_id`)
      REFERENCES `address` (`id`)
      ON DELETE SET NULL;

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

INSERT INTO sf_guard_permission (name, description) VALUES ('viewDeliveryAddress', 'allow to allways view orders delivery addresses');
INSERT INTO sf_guard_permission (name, description) VALUES ('viewBillingAddress', 'allow to allways view orders billing addresses');

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 = 'viewDeliveryAddress' OR sf_guard_permission.name = 'viewBillingAddress' )
          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 = 'viewDeliveryAddress' OR sf_guard_permission.name = 'viewBillingAddress' )
          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 = 'viewDeliveryAddress' OR sf_guard_permission.name = 'viewBillingAddress' )
          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 = 'viewDeliveryAddress' OR sf_guard_permission.name = 'viewBillingAddress' )
          AND
            sf_guard_group.name = 'Reviewer';

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 = 'viewDeliveryAddress' OR sf_guard_permission.name = 'viewBillingAddress' )
          AND
            sf_guard_group.name = 'Printer';



