#
# MySQL upgrade script for upgrade database from version 2.0.36 to 2.0.37
#



#-----------------------------------------------------------------------------
#-- address
#-----------------------------------------------------------------------------

ALTER TABLE address ADD KEY `name_index`(`name`);

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

ALTER TABLE `sf_guard_user_profile`
    CHANGE COLUMN `available_points` `available_points` FLOAT AFTER `bill_country`,
    ADD KEY `firstname_index`(`firstname`),
    ADD KEY `lastname_index`(`lastname`),
    ADD KEY `position_index`(`position`),
    ADD KEY `validation_level_index`(`validation_level`);

#-----------------------------------------------------------------------------
#-- points_history
#-----------------------------------------------------------------------------



CREATE TABLE `points_history`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_history_id` INTEGER  NOT NULL,
	`points` FLOAT,
	`evolution` FLOAT,
	`comment` VARCHAR(512),
	PRIMARY KEY (`id`),
	INDEX `points_history_FI_1` (`user_history_id`),
	CONSTRAINT `points_history_FK_1`
		FOREIGN KEY (`user_history_id`)
		REFERENCES `user_history` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- format_printer_price
#-----------------------------------------------------------------------------

ALTER TABLE `format_printer_price`
    ADD COLUMN `delivery_price` FLOAT AFTER `price`,
    ADD COLUMN `points` FLOAT AFTER `delivery_price`,
    ADD COLUMN `delivery_points` FLOAT AFTER `points`,
    ADD COLUMN `final_delivery_price` FLOAT AFTER `delivery_points`;

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

ALTER TABLE `print_job`
    ADD COLUMN `points` FLOAT AFTER `price`,
    ADD COLUMN `final_delivery_cost` FLOAT AFTER `delivery_cost`;

#-----------------------------------------------------------------------------
#-- translation
#-----------------------------------------------------------------------------

INSERT INTO translation (code) VALUES ('points');
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'en', 'points' FROM translation WHERE code = 'points';
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'fr', 'points' FROM translation WHERE code = 'points';

INSERT INTO translation (code) VALUES ('create-a-document');
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'en', 'Create a document' FROM translation WHERE code = 'create-a-document';
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'fr', 'Créer un document' FROM translation WHERE code = 'create-a-document';

INSERT INTO translation (code) VALUES ('my-points');
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'en', 'My points' FROM translation WHERE code = 'my-points';
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'fr', 'Mes points' FROM translation WHERE code = 'my-points';

INSERT INTO translation (code) VALUES ('the-date-you-have');
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'en', '%date%, you have' FROM translation WHERE code = 'the-date-you-have';
INSERT INTO translation_i18n (id, culture, value) SELECT translation.id, 'fr', 'Le %date%, vous avez' FROM translation WHERE code = 'the-date-you-have';

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

INSERT INTO sf_guard_permission (name, description) VALUES ('viewRealPrices', 'allow to view real prices when points are enabled');

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 = 'viewRealPrices'
          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 = 'viewRealPrices'
          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 = 'viewRealPrices'
          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 = 'viewRealPrices'
          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 = 'viewRealPrices'
          AND
            sf_guard_group.name = 'Printer';



