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 ;


DROP PROCEDURE IF EXISTS updateEventAttendeeName;

DELIMITER $$$
CREATE PROCEDURE updateEventAttendeeName(_userId int unsigned, _firstName VARCHAR(255), _lastName VARCHAR(255))
not deterministic modifies sql data
BEGIN
    UPDATE `sta_EventAttendee` SET `firstName` = _firstName, `lastName` = _lastName WHERE `userId` = _userId;
END $$$
DELIMITER ;

-- --------------------------------------------------------------------------------------------------------------------
-- Update EventAttendee Company
-- --------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS updateEventAttendeeCompanyByUserId;

DELIMITER $$$
CREATE PROCEDURE updateEventAttendeeCompanyByUserId(_userId int unsigned, _companyName VARCHAR(255))
not deterministic modifies sql data
BEGIN
    IF _companyName = '' THEN
        UPDATE `sta_EventAttendee` SET `company` = NULL WHERE `userId` = _userId;
    ELSE
        UPDATE `sta_EventAttendee` SET `company` = _companyName WHERE `userId` = _userId;
    END IF;
END $$$
DELIMITER ;

-- --------------------------------------------------------------------------------------------------------------------
-- T R I G G E R S
-- --------------------------------------------------------------------------------------------------------------------
DELIMITER $$$
DROP TRIGGER IF EXISTS `User_au` $$$
CREATE TRIGGER `User_au` AFTER UPDATE
ON `sta_User` FOR EACH ROW
begin
    IF NEW.firstName != OLD.firstName OR  NEW.lastName != OLD.lastName THEN
        CALL updateEventAttendeeName(NEW.id, NEW.firstName, NEW.lastName);
    END IF;
    IF IFNULL(NEW.companyName, 'NULL') != IFNULL(OLD.companyName, 'NULL') THEN
        IF NEW.companyName IS NULL THEN
            CALL updateEventAttendeeCompanyByUserId(NEW.id, '');
        ELSE
            CALL updateEventAttendeeCompanyByUserId(NEW.id, NEW.companyName);
        END IF;
    END IF;
end $$$
DELIMITER ;