CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Evaluation FOR EACH ROW BEGIN
  DECLARE ticket_count INT;
  SELECT COUNT(*) INTO ticket_count
  FROM Ticket t
  JOIN Performance p ON t.Performance_ID = p.Performance_ID
  WHERE t.Visitor_ID = NEW.Visitor_ID
    AND t.Performance_ID = NEW.Performance_ID
    AND t.Activated = 1;
  IF ticket_count = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Evaluation not allowed: visitor must have an activated ticket for the performance.';
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Event FOR EACH ROW BEGIN
  DECLARE overlap_count INT;
  SELECT COUNT(*) INTO overlap_count
  FROM Event
  WHERE Stage_ID = NEW.Stage_ID
    AND Festival_ID = NEW.Festival_ID
    AND DATE(Start_Time) = DATE(NEW.Start_Time)
    AND (
      NEW.Start_Time < End_Time AND
      NEW.End_Time > Start_Time
    );
  IF overlap_count > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Error: Overlapping event detected on the same stage.';
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE UPDATE ON Event FOR EACH ROW BEGIN
  DECLARE overlap_count INT;
  SELECT COUNT(*) INTO overlap_count
  FROM Event
  WHERE Stage_ID = NEW.Stage_ID
    AND Festival_ID = NEW.Festival_ID
    AND Event_ID != NEW.Event_ID
    AND (
      NEW.Start_Time < End_Time AND
      NEW.End_Time > Start_Time
    );
  IF overlap_count > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Error: Overlapping event detected on the same stage.';
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Performance FOR EACH ROW BEGIN
  DECLARE year INT;
  DECLARE consecutive_years INT;
  IF NEW.Artist_ID IS NOT NULL THEN
    
    SELECT f.Year INTO year
    FROM Event e
    JOIN Festival f ON e.Festival_ID = f.Festival_ID
    WHERE e.Event_ID = NEW.Event_ID;
    
    SELECT COUNT(DISTINCT f.Year) INTO consecutive_years
    FROM Performance p
    JOIN Event e ON p.Event_ID = e.Event_ID
    JOIN Festival f ON e.Festival_ID = f.Festival_ID
    WHERE p.Artist_ID = NEW.Artist_ID
      AND f.Year BETWEEN year - 2 AND year;
    IF consecutive_years > 3 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Artist cannot perform more than 3 consecutive years.';
    END IF;
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Performance FOR EACH ROW BEGIN
  DECLARE current_year INT;
  DECLARE consecutive_years INT;
  IF NEW.Band_ID IS NOT NULL THEN
    
    SELECT f.Year INTO current_year
    FROM Event e
    JOIN Festival f ON e.Festival_ID = f.Festival_ID
    WHERE e.Event_ID = NEW.Event_ID;
    
    SELECT COUNT(DISTINCT f.Year) INTO consecutive_years
    FROM Performance p
    JOIN Event e ON p.Event_ID = e.Event_ID
    JOIN Festival f ON e.Festival_ID = f.Festival_ID
    WHERE p.Band_ID = NEW.Band_ID
      AND f.Year BETWEEN current_year - 2 AND current_year;
    IF consecutive_years >= 3 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Band cannot perform more than 3 consecutive years.';
    END IF;
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Performance FOR EACH ROW BEGIN
  DECLARE previous_end_time DATETIME;
  DECLARE break_duration INT;
  DECLARE conflicting_count INT;
   
  SELECT COUNT(*) INTO conflicting_count
  FROM Performance
  WHERE Event_ID = NEW.Event_ID
    AND DATE(Start_Time) = DATE(NEW.Start_Time)
    AND (
      
      (NEW.Start_Time BETWEEN Start_Time AND (Start_Time + INTERVAL Duration MINUTE - INTERVAL 1 SECOND))
      OR ((NEW.Start_Time + INTERVAL NEW.Duration MINUTE - INTERVAL 1 SECOND) BETWEEN Start_Time AND (Start_Time + INTERVAL Duration MINUTE - INTERVAL 1 SECOND))
      OR (Start_Time BETWEEN NEW.Start_Time AND (NEW.Start_Time + INTERVAL NEW.Duration MINUTE - INTERVAL 1 SECOND))
    );
  IF conflicting_count > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Performance time overlaps with an existing performance.';
  END IF;
  SELECT MAX(Start_Time + INTERVAL Duration MINUTE) INTO previous_end_time
  FROM Performance
  WHERE Event_ID = NEW.Event_ID
    AND Start_Time < NEW.Start_Time
    AND DATE(Start_Time) = DATE(NEW.Start_Time);  
  IF previous_end_time IS NOT NULL THEN
    SET break_duration = TIMESTAMPDIFF(MINUTE, previous_end_time, NEW.Start_Time);
    
    IF break_duration < 5 OR break_duration > 30 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Break between performances must be between 5 and 30 minutes.';
    END IF;
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Performance FOR EACH ROW BEGIN
  DECLARE overlap_count INT;
  DECLARE festival_id INT;
  IF NEW.Artist_ID IS NOT NULL THEN
    SELECT e.Festival_ID INTO festival_id
    FROM Event e
    WHERE e.Event_ID = NEW.Event_ID;
    SELECT COUNT(*) INTO overlap_count
    FROM Performance p
    JOIN Event e ON p.Event_ID = e.Event_ID
    WHERE p.Artist_ID = NEW.Artist_ID
      AND e.Festival_ID = festival_id
      AND (
        NEW.Start_Time < ADDTIME(p.Start_Time, SEC_TO_TIME(p.Duration * 60)) AND
        ADDTIME(NEW.Start_Time, SEC_TO_TIME(NEW.Duration * 60)) > p.Start_Time
      );
    IF overlap_count > 0 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Artist has overlapping performance in the same festival.';
    END IF;
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Performance FOR EACH ROW BEGIN
  DECLARE overlap_count INT;
  DECLARE festival_id INT;
  IF NEW.Band_ID IS NOT NULL THEN
    SELECT e.Festival_ID INTO festival_id
    FROM Event e
    WHERE e.Event_ID = NEW.Event_ID;
    SELECT COUNT(*) INTO overlap_count
    FROM Performance p
    JOIN Event e ON p.Event_ID = e.Event_ID
    WHERE p.Band_ID = NEW.Band_ID
      AND e.Festival_ID = festival_id
      AND (
        NEW.Start_Time < ADDTIME(p.Start_Time, SEC_TO_TIME(p.Duration * 60)) AND
        ADDTIME(NEW.Start_Time, SEC_TO_TIME(NEW.Duration * 60)) > p.Start_Time
      );
    IF overlap_count > 0 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Band has overlapping performance in the same festival.';
    END IF;
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Ticket FOR EACH ROW BEGIN
  DECLARE total_tickets INT;
  DECLARE stage_capacity INT;
  SELECT COUNT(*) INTO total_tickets
  FROM Ticket
  WHERE Performance_ID = NEW.Performance_ID;
  SELECT s.Max_Capacity INTO stage_capacity
  FROM Performance p
  JOIN Event e ON p.Event_ID = e.Event_ID
  JOIN Stage s ON e.Stage_ID = s.Stage_ID
  WHERE p.Performance_ID = NEW.Performance_ID;
  IF total_tickets >= stage_capacity THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cannot sell ticket: stage capacity exceeded.';
  END IF;
END

CREATE TRIGGER ${nameWithSchemaName} BEFORE INSERT ON Ticket FOR EACH ROW BEGIN
  DECLARE vip_tickets INT;
  DECLARE stage_capacity INT;
  DECLARE max_vip_tickets INT;
  IF NEW.Category = 'VIP' THEN
    SELECT COUNT(*) INTO vip_tickets
    FROM Ticket
    WHERE Performance_ID = NEW.Performance_ID
      AND Category = 'VIP';
    SELECT s.Max_Capacity INTO stage_capacity
    FROM Performance p
    JOIN Event e ON p.Event_ID = e.Event_ID
    JOIN Stage s ON e.Stage_ID = s.Stage_ID
    WHERE p.Performance_ID = NEW.Performance_ID;
    SET max_vip_tickets = FLOOR(stage_capacity * 0.10);
    IF vip_tickets >= max_vip_tickets THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot sell VIP ticket: VIP limit exceeded.';
    END IF;
  END IF;
END