-- --------------------------------------------------------------------------------------------------------------------
-- 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 `qs_EventAttendee` SET `company` = NULL WHERE `userId` = _userId;
    ELSE
        UPDATE `qs_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 `qs_EventAttendee` AS `ea`
        JOIN `qs_User` AS `u` ON `u`.`id` = `ea`.`userId` AND `u`.`companyId` = _companyId
        SET `ea`.`company` = NULL;
    ELSE
        UPDATE `qs_EventAttendee` AS `ea`
        JOIN `qs_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 `qs_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 `qs_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 `qs_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 `qs_Company` FOR EACH ROW
begin
    CALL updateEventAttendeeCompanyByCompanyId(OLD.id, '');
end $$$
DELIMITER ;

UPDATE `qs_EventAttendee` AS `ea`
JOIN `qs_User` AS `u` ON `u`.`id` = `ea`.`userId`
LEFT JOIN `qs_Company` AS `c` ON `c`.`id` = `u`.`companyId`
SET `ea`.`company` = `c`.`name`;