START TRANSACTION;

  DROP TABLE IF EXISTS `clients`;
  CREATE TABLE IF NOT EXISTS `clients` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `password` BLOB NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `active` ENUM('T','F') NOT NULL DEFAULT 'F',
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB;

  DROP TABLE IF EXISTS `clients_urls`;
  CREATE TABLE IF NOT EXISTS `clients_urls` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` INT UNSIGNED NOT NULL,
    `code` TEXT NOT NULL,
    `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
    `expired` ENUM('T','F') NOT NULL DEFAULT 'F',
    `active` ENUM('T','F') NOT NULL DEFAULT 'F',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB;

  INSERT INTO `fields` VALUES (NULL, 'menuClients', 'backend', 'Menu / Clients', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Clients', 'script');

  INSERT INTO `fields` VALUES (NULL, 'btnAddClient', 'backend', 'Buttons / Add Client', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', '+ Add client', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblUsername', 'backend', 'Label / Username', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Username', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblPassword', 'backend', 'Label / Password', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Password', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblEmail', 'backend', 'Label / Email', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Email', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_titles_ARRAY_AC03', 'arrays', 'error_titles_ARRAY_AC03', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Client added!', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_bodies_ARRAY_AC03', 'arrays', 'error_bodies_ARRAY_AC03', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'All the changes made to this client have been saved.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_titles_ARRAY_AC01', 'arrays', 'error_titles_ARRAY_AC01', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Client updated!', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_bodies_ARRAY_AC01', 'arrays', 'error_bodies_ARRAY_AC01', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'All the changes made to this client have been saved.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'notify_arr_ARRAY_registration', 'arrays', 'notify_arr_ARRAY_registration', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Client - registration link email', 'script');

  INSERT INTO `fields` VALUES (NULL, 'notify_arr_ARRAY_recovery', 'arrays', 'notify_arr_ARRAY_recovery', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Client - registration recovery email', 'script');

  INSERT INTO `multi_lang` VALUES (NULL, 1, 'pjCalendar', '::LOCALE::', 'client_subject_registration', '', 'data');
  INSERT INTO `multi_lang` VALUES (NULL, 1, 'pjCalendar', '::LOCALE::', 'client_tokens_registration', '', 'data');

  INSERT INTO `multi_lang` VALUES (NULL, 1, 'pjCalendar', '::LOCALE::', 'client_subject_recovery', '', 'data');
  INSERT INTO `multi_lang` VALUES (NULL, 1, 'pjCalendar', '::LOCALE::', 'client_tokens_recovery', '', 'data');

  INSERT INTO `fields` VALUES (NULL, 'lblLogin', 'frontend', 'Label / Login', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Login', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblForgotPassword', 'frontend', 'Label / Forgot password', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Forgot password?', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblInvalidLogin', 'frontend', 'Label / Invalid login', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Invalid username or password', 'script');

  INSERT INTO `options` (`foreign_id`, `key`, `tab_id`, `value`, `label`, `type`, `order`, `is_visible`, `style`) VALUES
    (1, 'o_login_attempts', 3, 3, NULL, 'int', 14, 1, NULL),
    (1, 'o_link_expiration_hours', 3, 48, NULL, 'int', 15, 1, NULL);

  INSERT INTO `fields` VALUES (NULL, 'opt_o_login_attempts', 'backend', 'Options / Login attempts', 'script', '2015-11-17 07:11:13');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Login attempts', 'script');

  INSERT INTO `fields` VALUES (NULL, 'opt_o_link_expiration_hours', 'backend', 'Options / Link expiration hours', 'script', '2015-11-17 07:11:13');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'The link will expire in', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblBackToLogin', 'frontend', 'Label / Back to login', 'script', '2015-11-17 07:11:13');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Back to login', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblForgotPasswordSuccess', 'frontend', 'Label / Forgot password success', 'script', '2015-11-17 07:11:13');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Email has been send successful.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_titles_ARRAY_AC05', 'arrays', 'error_titles_ARRAY_AC05', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Email sent', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_bodies_ARRAY_AC05', 'arrays', 'error_bodies_ARRAY_AC05', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Email has been sent successful.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'btnPaymentConfirmationEmail', 'frontend', 'Button / Send payment email', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Send Payment Email', 'script');

  INSERT INTO `options` (`foreign_id`, `key`, `tab_id`, `value`, `label`, `type`, `order`, `is_visible`, `style`) VALUES
    (1, 'o_cancel_pending_reservations', 3, 48, NULL, 'int', 16, 1, NULL);

  INSERT INTO `fields` VALUES (NULL, 'opt_o_cancel_pending_reservations', 'backend', 'Options / Cancel pending reservations', 'script', '2015-11-17 07:11:13');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Pending reservations will be automatically cancelled after', 'script');

  ALTER TABLE `employees` ADD `timezone` VARCHAR(255) NULL AFTER `is_active`;
  ALTER TABLE `clients` ADD `timezone` VARCHAR(255) NULL AFTER `active`;

  INSERT INTO `fields` VALUES (NULL, 'employee_timezone', 'backend', 'Label / Employee timezone', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Timezone', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblClientTimezone', 'backend', 'Label / Client timezone', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Timezone', 'script');

  INSERT INTO `fields` VALUES (NULL, 'front_timezone', 'frontend', 'Label / Timezone', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Timezone', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblLogout', 'frontend', 'Label / Logout', 'script', '2017-03-09 05:27:35');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Logout', 'script');

  DROP TABLE IF EXISTS `extras`;
  CREATE TABLE IF NOT EXISTS `extras` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NULL,
    `status` ENUM('T','F') NOT NULL DEFAULT 'T',
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB;

  DROP TABLE IF EXISTS `extras_options`;
  CREATE TABLE IF NOT EXISTS `extras_options` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `extra_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NULL,
    `price` DECIMAL(9,2) NULL,
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB;

  DROP TABLE IF EXISTS `bookings_extras`;
  CREATE TABLE IF NOT EXISTS `bookings_extras` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `booking_id` INT UNSIGNED NOT NULL,
    `extra_id` INT UNSIGNED NOT NULL,
    `extra_option_id` INT UNSIGNED NOT NULL,
    `price` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB;

  INSERT INTO `fields` VALUES (NULL, 'menuExtras', 'backend', 'Menu / Extras', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Extras', 'script');

  INSERT INTO `fields` VALUES (NULL, 'btnAddExtra', 'backend', 'Buttons / Add extra', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', '+ Add Extra', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblExtraName', 'backend', 'Label / Extra name', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Name', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblExtraOption', 'backend', 'Label / Extra option', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Option', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_titles_ARRAY_AEX03', 'arrays', 'error_titles_ARRAY_AEX03', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Extra added', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_bodies_ARRAY_AEX03', 'arrays', 'error_bodies_ARRAY_AEX03', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'A new extra has been added to the list.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_titles_ARRAY_AEX04', 'arrays', 'error_titles_ARRAY_AEX04', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Extra failed to add', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_bodies_ARRAY_AEX04', 'arrays', 'error_bodies_ARRAY_AEX04', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'We are sorry that the new extra has not been added successfully.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_titles_ARRAY_AEX01', 'arrays', 'error_titles_ARRAY_AEX01', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Extra updated', 'script');

  INSERT INTO `fields` VALUES (NULL, 'error_bodies_ARRAY_AEX01', 'arrays', 'error_bodies_ARRAY_AEX01', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'All changes made to the extra have been saved successfully.', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblExtras', 'frontend', 'Label / Extras', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Extras', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblExtra', 'frontend', 'Label / Extra', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Extra', 'script');

  SET @id := (SELECT `id` FROM `fields` WHERE `key` = "error_bodies_ARRAY_AO25");
  UPDATE `multi_lang` SET `content` = 'Email notifications will be sent to people who make a booking after the booking form is completed or/and payment is made. If you leave subject field blank no email will be sent. You can use tokens in the email messages to personalize them.<br /><br /><table width="100%" border="0" cellspacing="0" cellpadding="0"><tbody><tr><td width="50%" valign="top"><p>{Name} - customer name;<br />{Phone} - customer phone number; <br />{Email} - customer e-mail address; <br />{Notes} - additional notes; <br />{Address1} - address 1; <br />{Address2} - address 2; <br />{City} - city; <br />{State} - state; <br />{Zip} - zip code; <br />{Country} - country; <br />{Username} - Username; <br />{Password} - Password; <br />{Link} - Link; <br /></p></td><td width="50%" valign="top">{BookingID} - Booking ID; <br />{Services} - Selected services<br />{CCType} - CC type; <br />{CCNum} - CC number; <br />{CCExpMonth} - CC exp.month; <br />{CCExpYear} - CC exp.year; <br />{CCSec} - CC sec. code; <br />{PaymentMethod} - selected payment method; <br />{Price} - price for selected services; <br />{Deposit} - Deposit amount; <br />{Tax} - Tax amount; <br />{Total} - Total amount; <br />{CancelURL} - Link for booking cancellation;<br/>{Extras} - Extras; </td></tr></tbody></table>' WHERE `foreign_id` = @id AND `model` = "pjField" AND `field` = "title";

  INSERT INTO `options` (`foreign_id`, `key`, `tab_id`, `value`, `label`, `type`, `order`, `is_visible`, `style`) VALUES
    (1, 'o_install_url', 1, NULL, NULL, 'string', 21, 1, NULL);

  INSERT INTO `fields` VALUES (NULL, 'opt_o_install_url', 'backend', 'Options / Main Installation URL', 'script', NULL);
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Main Installation URL', 'script');

  INSERT INTO `fields` VALUES (NULL, 'lblForgotPasswordError', 'frontend', 'Label / Forgot password error', 'script', '2015-11-17 07:11:13');
  SET @id := (SELECT LAST_INSERT_ID());
  INSERT INTO `multi_lang` VALUES (NULL, @id, 'pjField', '::LOCALE::', 'title', 'Email doesn''t exist.', 'script');

  ALTER TABLE `bookings` ADD `client_id` INT UNSIGNED NULL AFTER `payment_method`;

COMMIT;