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 `an_Page` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y';
        ALTER TABLE `an_DraftPage` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y';
    ELSEIF _isSecure = 'n' THEN
        ALTER TABLE `an_Page` CHANGE `isSecure` `isSecure` ENUM('n','y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'n';
        ALTER TABLE `an_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 `an_Page` SET `isSecure` = _isSecure;
    UPDATE `an_DraftPage` SET `isSecure` = _isSecure;
    UPDATE `an_DPageOption` SET `value` = _isSecure WHERE `name` = 'isSecure';
    UPDATE `an_PageOption` SET `value` = _isSecure WHERE `name` = 'isSecure';
    UPDATE `an_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 `an_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 `an_EventAttendee` SET `company` = NULL WHERE `userId` = _userId;
    ELSE
        UPDATE `an_EventAttendee` SET `company` = _companyName WHERE `userId` = _userId;
    END IF;
END $$$
DELIMITER ;

DROP PROCEDURE IF EXISTS updateEventAttendeeCompanyByCompanyId;

DELIMITER $$$
CREATE PROCEDURE updateEventAttendeeCompanyByCompanyId(_companyId int unsigned, _companyName VARCHAR(255))
not deterministic modifies sql data
BEGIN
    IF _companyName = '' THEN
        UPDATE `an_EventAttendee` AS `ea`
        JOIN `an_User` AS `u` ON `u`.`id` = `ea`.`userId` AND `u`.`companyId` = _companyId
        SET `ea`.`company` = NULL;
    ELSE
        UPDATE `an_EventAttendee` AS `ea`
        JOIN `an_User` AS `u` ON `u`.`id` = `ea`.`userId` AND `u`.`companyId` = _companyId
        SET `ea`.`company` = _companyName;
    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 `an_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.companyId, 'NULL') != IFNULL(OLD.companyId, 'NULL') THEN
        IF NEW.companyId IS NULL THEN
            CALL updateEventAttendeeCompanyByUserId(NEW.id, '');
        ELSE
            CALL updateEventAttendeeCompanyByUserId(NEW.id, (SELECT `name` FROM `an_Company` WHERE `id` = NEW.companyId LIMIT 1));
        END IF;
    END IF;
end $$$
DELIMITER ;

DELIMITER $$$
DROP TRIGGER IF EXISTS `Company_au` $$$
CREATE TRIGGER `Company_au` AFTER UPDATE
ON `an_Company` FOR EACH ROW
begin
    IF NEW.name != OLD.name THEN
        CALL updateEventAttendeeCompanyByCompanyId(NEW.id, NEW.name);
    END IF;
end $$$
DELIMITER ;

DELIMITER $$$
DROP TRIGGER IF EXISTS `Company_bd` $$$
CREATE TRIGGER `Company_bd` BEFORE DELETE
ON `an_Company` FOR EACH ROW
begin
    CALL updateEventAttendeeCompanyByCompanyId(OLD.id, '');
end $$$
DELIMITER ;