
START TRANSACTION;

ALTER TABLE `services` ADD COLUMN `gender` enum('male','female','child') DEFAULT NULL;

DROP TABLE IF EXISTS `subservices`;
CREATE TABLE IF NOT EXISTS `subservices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `calendar_id` int(10) DEFAULT NULL,
  `status` enum('T','F') DEFAULT 'T',
  PRIMARY KEY (`id`),
  KEY `status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `services_subservices`;
CREATE TABLE IF NOT EXISTS `services_subservices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,         
  `service_id` int(10) unsigned NOT NULL,               
  `subservice_id` int(10) unsigned NOT NULL,                
  PRIMARY KEY (`id`),                                    
  UNIQUE KEY `service_id` (`service_id`,`subservice_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `bookings_employees`;
CREATE TABLE IF NOT EXISTS `bookings_employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,         
  `booking_id` int(10) unsigned NOT NULL,               
  `employee_id` int(10) unsigned NOT NULL,                
  PRIMARY KEY (`id`),                                    
  UNIQUE KEY `booking_id` (`booking_id`,`employee_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `bookings_subservices`;
CREATE TABLE IF NOT EXISTS `bookings_subservices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,         
  `booking_id` int(10) unsigned NOT NULL,               
  `subservice_id` int(10) unsigned NOT NULL,
  `qty` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),                                    
  UNIQUE KEY `booking_id` (`booking_id`,`subservice_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE `plugin_auth_users` ADD COLUMN `category` enum('employee','apprentice') DEFAULT NULL;
UPDATE `options` SET `is_visible`=0 WHERE `key`='o_seo_url';


INSERT INTO `plugin_base_fields` VALUES (NULL, 'lblServiceGender', 'backend', 'Label / Gender', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Gender', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'service_gender_ARRAY_male', 'arrays', 'service_gender_ARRAY_male', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Male', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'service_gender_ARRAY_female', 'arrays', 'service_gender_ARRAY_female', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Female', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'service_gender_ARRAY_child', 'arrays', 'service_gender_ARRAY_child', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Child', 'script');


INSERT INTO `plugin_base_fields` VALUES (NULL, 'menuSubservices', 'backend', 'Menu / Sub-services', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Sub-services', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_name', 'backend', 'Label / Name', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Name', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_statuses_ARRAY_T', 'arrays', 'subservice_statuses_ARRAY_T', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Active', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_statuses_ARRAY_F', 'arrays', 'subservice_statuses_ARRAY_F', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Inactive', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_create_title', 'backend', 'Label / Add sub-service', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Add sub-service', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_update_title', 'backend', 'Label / Update sub-service', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Update sub-service', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'lblSubservice', 'backend', 'Label / Sub-service', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Sub-service', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_index_title', 'backend', 'Label / Sub-services title', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Sub-services', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_index_body', 'backend', 'Label / Sub-services desc', 'script', NULL);
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Below you can see the available sub-services that you can assign them to each services. Under Add sub-service tab you can add a new sub-service. Or use the edit icon for each sub-service to modify it.', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'employee_category', 'backend', 'Label / Category', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Category', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'employee_categories_ARRAY_employee', 'arrays', 'employee_categories_ARRAY_employee', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Employee', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'employee_categories_ARRAY_apprentice', 'arrays', 'employee_categories_ARRAY_apprentice', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Apprentice', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'booking_apprentices', 'backend', 'Label / Apprentices', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Apprentices', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'booking_reminder_apprentices', 'backend', 'Label / Send to apprentices', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Send to apprentices', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'subservice_description', 'backend', 'Label / Description', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Description', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'booking_subservices', 'backend', 'Label / Sub-services', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Sub-services', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'front_btn_any_employee', 'frontend', 'Button / Any Employee', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Any Employee', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'front_previous', 'frontend', 'Label / Previous', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Previous', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'front_next', 'frontend', 'Label / Next', 'script', '2018-09-19 07:20:53');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjBaseField', '::LOCALE::', 'title', 'Next', 'script');



SET @id := (SELECT `id` FROM `fields` WHERE `key` = "opt_o_email_body_text");
UPDATE `multi_lang` SET `content` = '<div class="col-xs-6">
<div><small>{Name}</small></div>
<div><small>{Phone}</small></div>
<div><small>{Email}</small></div>
<div><small>{Notes}</small></div>
<div><small>{Address1}</small></div>
<div><small>{Address2}</small></div>
<div><small>{City}</small></div>
<div><small>{State}</small></div>
<div><small>{Zip}</small></div>
<div><small>{Country}</small></div>

</div>
<div class="col-xs-6">
<div><small>{BookingID}</small></div>
<div><small>{Services}</small></div>
<div><small>{Subservices}</small></div>
<div><small>{PaymentMethod}</small></div>
<div><small>{Price}</small></div>
<div><small>{Deposit}</small></div>
<div><small>{Tax}</small></div>
<div><small>{Total}</small></div>
<div><small>{CancelURL}</small></div>
 </div>
' WHERE `foreign_id` = @id AND `model` = "pjField" AND `field` = "title";

SET @id := (SELECT `id` FROM `fields` WHERE `key` = "opt_o_reminder_body_text");
UPDATE `multi_lang` SET `content` = '<div class="col-xs-6">
<p>{Name}</p>
<p>{Phone}</p>
<p>{Email}</p>
<p>{BookingID}</p>
<p>{Services}</p>
<p>{Subservices}</p>
</div>
<div class="col-xs-6">
<p>{Price}</p>
<p>{Deposit}</p>
<p>{Tax}</p>
<p>{Total}</p>
<p>{CancelURL}</p>
</div>
' WHERE `foreign_id` = @id AND `model` = "pjField" AND `field` = "title";


INSERT INTO `plugin_base_fields` VALUES (NULL, 'pjAdminSubservices', 'backend', '', 'script', '2015-03-20 11:37:44');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Sub-services Menu', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'pjAdminSubservices_pjActionIndex', 'backend', 'pjAdminSubservices_pjActionIndex', 'script', '2015-03-20 11:37:44');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Sub-services List', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'pjAdminSubservices_pjActionCreate', 'backend', 'pjAdminSubservices_pjActionCreate', 'script', '2015-03-20 11:37:44');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Add sub-service', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'pjAdminSubservices_pjActionUpdate', 'backend', 'pjAdminSubservices_pjActionUpdate', 'script', '2015-03-20 11:37:44');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Update sub-service', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'pjAdminSubservices_pjActionDelete', 'backend', 'pjAdminSubservices_pjActionDelete', 'script', '2015-03-20 11:37:44');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Delete single sub-service', 'script');

INSERT INTO `plugin_base_fields` VALUES (NULL, 'pjAdminSubservices_pjActionDeleteBulk', 'backend', 'pjAdminSubservices_pjActionDeleteBulk', 'script', '2015-03-20 11:37:44');
SET @id := (SELECT LAST_INSERT_ID());
INSERT INTO `plugin_base_multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Delete multiple sub-services', 'script');

INSERT INTO `plugin_auth_permissions` (`id`, `parent_id`, `key`) VALUES (NULL, NULL, 'pjAdminSubservices');
SET @level_1_id := (SELECT LAST_INSERT_ID());

  INSERT INTO `plugin_auth_permissions` (`id`, `parent_id`, `key`) VALUES (NULL, @level_1_id, 'pjAdminSubservices_pjActionIndex');
  SET @level_2_id := (SELECT LAST_INSERT_ID());

    INSERT INTO `plugin_auth_permissions` (`id`, `parent_id`, `key`) VALUES (NULL, @level_2_id, 'pjAdminSubservices_pjActionCreate');
    INSERT INTO `plugin_auth_permissions` (`id`, `parent_id`, `key`) VALUES (NULL, @level_2_id, 'pjAdminSubservices_pjActionUpdate');
    INSERT INTO `plugin_auth_permissions` (`id`, `parent_id`, `key`) VALUES (NULL, @level_2_id, 'pjAdminSubservices_pjActionDelete');
    INSERT INTO `plugin_auth_permissions` (`id`, `parent_id`, `key`) VALUES (NULL, @level_2_id, 'pjAdminSubservices_pjActionDeleteBulk');
    

COMMIT;