-- noinspection SqlNoDataSourceInspectionForFile

-- noinspection SqlDialectInspectionForFile

ALTER TABLE `sta_EventAttendee` ADD `dietarySpecialNeeds` TEXT NULL DEFAULT NULL AFTER `company`;
ALTER TABLE `sta_EventAttendee` ADD `credentials` VARCHAR(255) NULL DEFAULT NULL AFTER `lastName`;
ALTER TABLE `sta_EventAttendee` ADD `profession` VARCHAR(255) NULL DEFAULT NULL AFTER `company`;
ALTER TABLE `sta_EventAttendee`
  ADD `address` VARCHAR(255) NULL DEFAULT NULL AFTER `company`,
  ADD `address2` VARCHAR(255) NULL DEFAULT NULL AFTER `address`,
  ADD `address3` VARCHAR(255) NULL DEFAULT NULL AFTER `address2`,
  ADD `city` VARCHAR(255) NULL DEFAULT NULL AFTER `address3`,
  ADD `state` VARCHAR(255) NULL DEFAULT NULL AFTER `city`,
  ADD `zip` VARCHAR(16) NULL DEFAULT NULL AFTER `state`,
  ADD `countryId` CHAR(2) NULL DEFAULT NULL AFTER `zip`;

ALTER TABLE `sta_EventAttendee`
  ADD `workPhone` VARCHAR(255) NULL DEFAULT NULL AFTER `countryId`,
  ADD `fax` VARCHAR(255) NULL DEFAULT NULL AFTER `workPhone`;

ALTER TABLE `sta_EventAttendee` ADD `spouseFullName` VARCHAR(255) NULL DEFAULT NULL AFTER `credentials`;

CREATE TABLE `sta_EventEarlyBirdPrice` (
 `eventId` int(10) unsigned NOT NULL,
 `date` date NOT NULL,
 `member` decimal(11,2) NOT NULL,
 `nonmember` decimal(11,2) NOT NULL,
 `resident` decimal(11,2) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`eventId`,`date`),
 KEY `eventId` (`eventId`),
 CONSTRAINT `sta_EventEarlyBirdPrice_ibfk_1` FOREIGN KEY (`eventId`) REFERENCES `sta_Event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `sta_EventAlaCarteItem` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `eventId` int(10) unsigned NOT NULL,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `price` decimal(11,2) NOT NULL,
 `sorter` int(11) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `eventId` (`eventId`),
 KEY `sorter` (`sorter`),
 CONSTRAINT `sta_EventAlaCarteItem_ibfk_1` FOREIGN KEY (`eventId`) REFERENCES `sta_Event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `sta_EventAttendeeAlaCarteItem` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `attendeeId` int(10) unsigned NOT NULL,
 `originalId` int(10) unsigned DEFAULT NULL,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `price` decimal(11,2) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `attendeeId` (`attendeeId`),
 KEY `originalId` (`originalId`),
 CONSTRAINT `sta_EventAttendeeAlaCarteItem_ibfk_3` FOREIGN KEY (`attendeeId`) REFERENCES `sta_EventAttendee` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sta_EventAttendeeAlaCarteItem_ibfk_2` FOREIGN KEY (`originalId`) REFERENCES `sta_EventAlaCarteItem` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `sta_EventWorkshop` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `eventId` int(10) unsigned NOT NULL,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `description` text COLLATE utf8_unicode_ci NOT NULL,
 `sorter` int(11) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `eventId` (`eventId`),
 KEY `sorter` (`sorter`),
 CONSTRAINT `sta_EventWorkshop_ibfk_1` FOREIGN KEY (`eventId`) REFERENCES `sta_Event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `sta_EventAttendee` ADD `workshopTitle` VARCHAR(255) NULL DEFAULT NULL AFTER `dietarySpecialNeeds`;


-- ---------------------------------------------------------------------------------------------------------------------
-- from db/routinesAndTriggers.sql
-- ---------------------------------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS makeSecure;

DELIMITER $$$
CREATE PROCEDURE makeSecure(_isSecure ENUM('y', 'n'))
not deterministic modifies sql data
BEGIN

  IF _isSecure = 'y' THEN
    ALTER TABLE `sta_Page` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y';
    ALTER TABLE `sta_DraftPage` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y';
  ELSEIF _isSecure = 'n' THEN
    ALTER TABLE `sta_Page` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'n';
    ALTER TABLE `sta_DraftPage` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'n';
  ELSE
    SIGNAL SQLSTATE
      'ERR0R'
    SET
      MESSAGE_TEXT = "Incorrect isSecure argument. Use CALL makeSecure('y') or CALL makeSecure('n')",
      MYSQL_ERRNO = 1;
  END IF;

  UPDATE `sta_Page` SET `isSecure` = _isSecure;
  UPDATE `sta_DraftPage` SET `isSecure` = _isSecure;
  UPDATE `sta_DPageOption` SET `value` = _isSecure WHERE `name` = 'isSecure';
  UPDATE `sta_PageOption` SET `value` = _isSecure WHERE `name` = 'isSecure';
  UPDATE `sta_DraftPageOption` SET `value` = _isSecure WHERE `name` = 'isSecure';
END $$$
DELIMITER ;