DELIMITER //

# DROP FUNCTION F_DEADLINE_UPDATE_IS_NEEDED;
# DROP PROCEDURE P_UPDATE_CATEGORY_DEADLINE;
# DROP TRIGGER T_ADD_TASK;
# DROP TRIGGER T_DELETE_CATEGORY;
# DROP TRIGGER T_UPDATE_TASK;
# DROP TRIGGER T_DELETE_TASK;
SET GLOBAL log_bin_trust_function_creators = 1;

CREATE PROCEDURE P_UPDATE_CATEGORY_DEADLINE(category_id INT)
BEGIN
  SET @category_has_parent = TRUE;
  SET @update_performed = TRUE;

  WHILE @category_has_parent AND @update_performed DO
  SET @category_has_parent = FALSE;
  SET @update_performed = FALSE;
  SET @category_parent_id = (SELECT c.parent_id FROM CORE_TASK_CATEGORY c WHERE  c.id = category_id);

  SET @deadline_by_category = (SELECT MAX(c.deadline) FROM CORE_TASK_CATEGORY c WHERE c.parent_id = category_id);
  SET @deadline_by_task = (SELECT MAX(t.deadline) FROM CORE_TASK t WHERE t.category_id = category_id);
  SET @current_deadline = (SELECT c.deadline FROM CORE_TASK_CATEGORY c WHERE c.id = category_id);
  SET @deadline_to_set = @deadline_by_category;

  IF (@deadline_to_set IS NULL OR @deadline_to_set < @deadline_by_task) THEN
    SET @deadline_to_set = @deadline_by_task;
  END IF;

  IF (@deadline_to_set IS NULL OR @current_deadline IS NULL OR @deadline_to_set <> @current_deadline) THEN
    UPDATE CORE_TASK_CATEGORY c
    SET c.deadline = @deadline_to_set,
        c.version  = c.version + 1
    WHERE c.id = category_id;

    SET @update_performed = TRUE;
  END IF;
  IF (@category_parent_id IS NOT NULL) THEN
    SET @category_has_parent = TRUE;
    SET category_id = @category_parent_id;
  END IF;
  END WHILE;

END;

CREATE FUNCTION F_DEADLINE_UPDATE_IS_NEEDED(category_id INT, proposed_deadline DATETIME)
  RETURNS BOOLEAN
BEGIN
  SET @current_deadline = (SELECT c.deadline FROM CORE_TASK_CATEGORY c WHERE c.id = category_id);
  RETURN @current_deadline IS NULL AND proposed_deadline IS NOT NULL OR
         proposed_deadline IS NULL AND @current_deadline IS NOT NULL OR
         @current_deadline <> proposed_deadline;
END;

CREATE TRIGGER T_ADD_TASK
  AFTER
    INSERT
  ON CORE_TASK
  FOR EACH ROW
BEGIN
  IF (F_DEADLINE_UPDATE_IS_NEEDED(NEW.category_id, NEW.deadline)) THEN
    CALL P_UPDATE_CATEGORY_DEADLINE(NEW.category_id);
  END IF;
END;

CREATE TRIGGER T_UPDATE_TASK
  AFTER UPDATE
  ON CORE_TASK
  FOR EACH ROW
BEGIN

  IF (F_DEADLINE_UPDATE_IS_NEEDED(NEW.category_id, NEW.deadline)) THEN
    CALL P_UPDATE_CATEGORY_DEADLINE(NEW.category_id);
  END IF;

END;

CREATE TRIGGER T_DELETE_TASK
  AFTER DELETE
  ON CORE_TASK
  FOR EACH ROW
BEGIN
  IF (F_DEADLINE_UPDATE_IS_NEEDED(OLD.category_id, NULL)) THEN
    CALL P_UPDATE_CATEGORY_DEADLINE(OLD.category_id);
  END IF;
END;


CREATE TRIGGER T_DELETE_CATEGORY
  AFTER DELETE
  ON CORE_TASK_CATEGORY
  FOR EACH ROW
BEGIN
  IF (F_DEADLINE_UPDATE_IS_NEEDED(OLD.parent_id, NULL)) THEN
    CALL P_UPDATE_CATEGORY_DEADLINE(OLD.parent_id);
  END IF;
END;

//

DELIMITER ;