-- WARNING: In SQLite DELIMITER doesn't work, so this file is handled in a special way

DELIMITER $$

-- INFO: ----- TICKET -----

-- Create trigger to validate seat number before inserting a ticket

-- INSERT

CREATE TRIGGER check_seat_number_before_insert
BEFORE INSERT ON tickets
WHEN EXISTS (
    SELECT 1
    FROM sessions s
    INNER JOIN halls h ON s.hall_id = h.hall_id
    WHERE s.session_id = NEW.session_id
      AND NEW.seat_number > h.capacity
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1001: Seat number exceeds hall capacity');
END$$

-- UPDATE

CREATE TRIGGER check_seat_number_before_update
BEFORE UPDATE ON tickets
WHEN EXISTS (
    SELECT 1
    FROM sessions s
    INNER JOIN halls h ON s.hall_id = h.hall_id
    WHERE s.session_id = NEW.session_id
      AND NEW.seat_number > h.capacity
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1001: Seat number exceeds hall capacity');
END$$

-- Check if the ticket purchase time is later than the session start time

-- INSERT

CREATE TRIGGER prevent_late_ticket_purchase_before_insert
BEFORE INSERT ON tickets
WHEN EXISTS (
    SELECT 1
    FROM sessions
    WHERE session_id = NEW.session_id
      AND DATETIME(NEW.sold_at) >= DATETIME(begin_at)
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1002: Cannot purchase ticket after the session has started');
END$$

-- UPDATE

CREATE TRIGGER prevent_late_ticket_purchase_before_update
BEFORE UPDATE ON tickets
WHEN EXISTS (
    SELECT 1
    FROM sessions
    WHERE session_id = NEW.session_id
      AND DATETIME(NEW.sold_at) >= DATETIME(begin_at)
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1002: Cannot purchase ticket after the session has started');
END$$


-- INFO: ----- REFUND -----

-- Create trigger to prevent selling a ticket after the session has started

-- INSERT

CREATE TRIGGER prevent_late_refunds_before_insert
BEFORE INSERT ON refunds
WHEN EXISTS (
    SELECT 1
    FROM sessions s
    JOIN tickets t ON s.session_id = t.session_id
      AND t.ticket_id = NEW.ticket_id
      WHERE DATETIME(NEW.refund_at) >= DATETIME(s.begin_at)
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1003: Refunds cannot be made after the session has started');
END$$

-- UPDATE 

CREATE TRIGGER prevent_late_refunds_before_update
BEFORE UPDATE ON refunds
WHEN EXISTS (
    SELECT 1
    FROM sessions s
    JOIN tickets t ON s.session_id = t.session_id
      AND t.ticket_id = NEW.ticket_id
      WHERE DATETIME(NEW.refund_at) >= DATETIME(s.begin_at)
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1003: Refunds cannot be made after the session has started');
END$$


-- Create a trigger to check if the refund amount is greater than the ticket sale amount

-- INSERT

CREATE TRIGGER validate_refund_amount_before_insert
BEFORE INSERT ON refunds
WHEN NEW.refund_amount > (
    SELECT ticket_price
    FROM sessions s
    JOIN tickets t ON s.session_id = t.session_id
    WHERE t.ticket_id = NEW.ticket_id
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1004: Refund amount cannot exceed the sale amount');
END$$

-- UPDATE

CREATE TRIGGER validate_refund_amount_before_update
BEFORE UPDATE ON refunds
WHEN NEW.refund_amount > (
    SELECT ticket_price
    FROM sessions s
    JOIN tickets t ON s.session_id = t.session_id
    WHERE t.ticket_id = NEW.ticket_id
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1004: Refund amount cannot exceed the sale amount');
END$$

-- Create a trigger to check if the refund date is earlier than the sale date

-- INSERT

CREATE TRIGGER validate_refund_date_before_insert
BEFORE INSERT ON refunds
WHEN EXISTS (
    SELECT 1
    FROM tickets
    WHERE ticket_id = NEW.ticket_id
      AND DATETIME(NEW.refund_at) <= DATETIME(sold_at)
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1005: Refund date cannot be earlier than the sale date or equal to it');
END$$

-- UPDATE

CREATE TRIGGER validate_refund_date_before_update
BEFORE UPDATE ON refunds
WHEN EXISTS (
    SELECT 1
    FROM tickets
    WHERE ticket_id = NEW.ticket_id
      AND DATETIME(NEW.refund_at) <= DATETIME(sold_at)
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1005: Refund date cannot be earlier than the sale date or equal to it');
END$$

-- INFO: ----- SESSION -----

-- Trigger to prevent overlapping sessions

--INSERT

CREATE TRIGGER prevent_overlapping_sessions_before_insert
BEFORE INSERT ON sessions
WHEN EXISTS (
    SELECT 1
    FROM sessions s
    JOIN movies m_existing ON s.movie_id = m_existing.movie_id
    JOIN movies m_new ON NEW.movie_id = m_new.movie_id
    WHERE s.hall_id = NEW.hall_id
      AND (
          -- The new movie starts within an already occupied time slot  
          NEW.begin_at < DATETIME(s.begin_at, '+' || m_existing.duration || ' minutes')  
          -- Or the existing movie starts within the new movie's time slot  
          AND s.begin_at < DATETIME(NEW.begin_at, '+' || m_new.duration || ' minutes')
      )
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1006: The new session overlaps with an ongoing movie in the hall');
END$$

-- UPDATE

CREATE TRIGGER prevent_overlapping_sessions_before_update
BEFORE UPDATE ON sessions
WHEN EXISTS (
    SELECT 1
    FROM sessions s
    JOIN movies m_existing ON s.movie_id = m_existing.movie_id
    JOIN movies m_new ON NEW.movie_id = m_new.movie_id
    WHERE s.hall_id = NEW.hall_id
      AND (
          -- The new movie starts within an already occupied time slot  
          NEW.begin_at < DATETIME(s.begin_at, '+' || m_existing.duration || ' minutes')  
          -- Or the existing movie starts within the new movie's time slot  
          AND s.begin_at < DATETIME(NEW.begin_at, '+' || m_new.duration || ' minutes')
      )
)
BEGIN
    SELECT RAISE(FAIL, 'Error 1006: The new session overlaps with an ongoing movie in the hall');
END$$

DELIMITER ;