CinemaDB/Faza4: CinemaDB_Faza4.sql

CREATE OR REPLACE PROCEDURE sp_buy_ticket(
    p_user_id       INT,
    p_showtime_id   INT,
    p_seat_id       INT,
    p_payment_method VARCHAR(50),
    p_employee_id   INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_reservation_id INT;
    v_ticket_id      INT;
    v_price          INT;
    v_seat_type_id   INT;
    v_base_price     INT;
    v_start_time     TIMESTAMP;
    v_hall_id        INT;
    v_movie_title    VARCHAR;
    v_seat_row       INT;
    v_seat_number    INT;
    v_seat_type      VARCHAR;
BEGIN
    SELECT s.start_time, s.base_price, s.hall_id, m.title
    INTO v_start_time, v_base_price, v_hall_id, v_movie_title
    FROM Showtime s
    JOIN Movie m ON m.movie_id = s.movie_id
    WHERE s.showtime_id = p_showtime_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Проекцијата не постои.';
    END IF;

    IF v_start_time < CURRENT_TIMESTAMP THEN
        RAISE EXCEPTION 'Не можете да купите билет за завршена проекција.';
    END IF;

    SELECT se.seat_type_id, se.seat_row, se.seat_number, st.type
    INTO v_seat_type_id, v_seat_row, v_seat_number, v_seat_type
    FROM Seat se
    JOIN Seat_Type st ON st.seat_type_id = se.seat_type_id
    WHERE se.seat_id = p_seat_id
      AND se.hall_id = v_hall_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Седиштето не постои или не припаѓа на салата за оваа проекција.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM Ticket
        WHERE showtime_id = p_showtime_id
          AND seat_id = p_seat_id
    ) THEN
        RAISE EXCEPTION 'Седиштето Ред % Број % е веќе зафатено.', v_seat_row, v_seat_number;
    END IF;

    v_price := (v_base_price * CASE v_seat_type_id
        WHEN 1 THEN 1.0
        WHEN 2 THEN 2.0
        WHEN 3 THEN 1.5
        WHEN 4 THEN 1.8
        WHEN 5 THEN 0.7
        ELSE 1.0
    END)::INT;


    INSERT INTO Reservation (user_id, showtime_id, employee_id, reservation_date, status)
    VALUES (p_user_id, p_showtime_id, p_employee_id, CURRENT_DATE, 'CONFIRMED')
    RETURNING reservation_id INTO v_reservation_id;


    INSERT INTO Ticket (showtime_id, seat_id, reservation_id, price, purchase_date)
    VALUES (p_showtime_id, p_seat_id, v_reservation_id, v_price, CURRENT_DATE)
    RETURNING ticket_id INTO v_ticket_id;

    INSERT INTO ReservationPayment (user_id, reservation_id, employee_id, amount, payment_date, payment_method)
    VALUES (p_user_id, v_reservation_id, p_employee_id, v_price, CURRENT_DATE, p_payment_method);

    RAISE NOTICE '=================================';
    RAISE NOTICE 'Билетот е успешно купен!';
    RAISE NOTICE 'Филм:     %', v_movie_title;
    RAISE NOTICE 'Седиште:  Ред % - Број % (%)', v_seat_row, v_seat_number, v_seat_type;
    RAISE NOTICE 'Цена:     % ден', v_price;
    RAISE NOTICE 'Начин:    %', p_payment_method;
    RAISE NOTICE 'Билет ID: %', v_ticket_id;
    RAISE NOTICE 'Резерв ID: %', v_reservation_id;
    RAISE NOTICE '=================================';

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Грешка при купување билет: %', SQLERRM;
END;
$$;

SELECT
    s.showtime_id,
    m.title,
    s.start_time,
    s.hall_id,
    s.base_price
FROM Showtime s
JOIN Movie m ON m.movie_id = s.movie_id
WHERE s.start_time > CURRENT_TIMESTAMP
LIMIT 5;

SELECT se.seat_id, se.seat_row, se.seat_number, st.type, st.price
FROM Seat se
JOIN Seat_Type st ON st.seat_type_id = se.seat_type_id
WHERE se.hall_id = 1
  AND se.seat_id NOT IN (
      SELECT seat_id FROM Ticket WHERE showtime_id = 1761211
  )
LIMIT 5;
CALL sp_buy_ticket(
    1,
    1761211,
    1,
    'Credit Card'
);

CALL sp_buy_ticket(2, 1761211, 1, 'Cash');

CALL sp_buy_ticket(1, 1761211, 2, 'Online');

CALL sp_buy_ticket(1, 1761211, 5, 'Cash');




CREATE OR REPLACE PROCEDURE sp_place_order(
    p_user_id    INT,
    p_product_id INT,
    p_quantity   INT,
    p_employee_id INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock    INT;
    v_price    INT;
    v_order_id INT;
BEGIN

    SELECT stock_quantity, price INTO v_stock, v_price
    FROM Product WHERE product_id = p_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Производот не постои.';
    END IF;

    IF v_stock < p_quantity THEN
        RAISE EXCEPTION 'Нема доволно залиха! Достапни: %, Побарани: %', v_stock, p_quantity;
    END IF;

    INSERT INTO CinemaOrder (user_id, employee_id, order_date, total_price, status)
    VALUES (p_user_id, p_employee_id, CURRENT_DATE, v_price * p_quantity, 'PREPARING')
    RETURNING order_id INTO v_order_id;

    INSERT INTO Order_Product (order_id, product_id, quantity, price_at_order)
    VALUES (v_order_id, p_product_id, p_quantity, v_price);

    UPDATE Product
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;

    RAISE NOTICE 'Нарачката е успешно креирана! Order ID: %', v_order_id;
END;
$$;

CALL sp_place_order(1, 1, 5);

CALL sp_place_order(1, 1, 99999);

CALL sp_place_order(1, 99999, 1);




CREATE PROCEDURE pr_cancel_reservation(p_reservation_id INT)
LANGUAGE plpgsql AS $$
BEGIN

    DELETE FROM Ticket
    WHERE reservation_id = p_reservation_id;

    DELETE FROM ReservationPayment
    WHERE reservation_id = p_reservation_id;
    UPDATE Reservation
    SET status = 'CANCELLED'
    WHERE reservation_id = p_reservation_id;
    COMMIT;
END;
$$;
SELECT reservation_id, status FROM Reservation WHERE reservation_id = 100;
CALL pr_cancel_reservation(100);
SELECT reservation_id, status FROM Reservation WHERE reservation_id = 100;
SELECT * FROM Ticket WHERE reservation_id = 100;





CREATE OR REPLACE PROCEDURE sp_submit_review(
    p_user_id    INT,
    p_movie_id   INT,
    p_rating     INT,
    p_comment    TEXT,
    p_review_date DATE DEFAULT CURRENT_DATE
)
LANGUAGE plpgsql
AS $$
BEGIN

    IF NOT EXISTS (
        SELECT 1
        FROM Reservation r
        JOIN Ticket t ON r.reservation_id = t.reservation_id
        JOIN Showtime s ON t.showtime_id = s.showtime_id
        WHERE r.user_id = p_user_id
          AND s.movie_id = p_movie_id
          AND s.end_time < CURRENT_TIMESTAMP
    ) THEN
        RAISE EXCEPTION 'Не можете да оставите рецензија. Или немате купено билет за овој филм, или проекцијата се уште не е завршена.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM Review
        WHERE user_id = p_user_id
          AND movie_id = p_movie_id
    ) THEN
        RAISE EXCEPTION 'Веќе оставивте рецензија за овој филм.';
    END IF;

    IF p_rating < 1 OR p_rating > 10 THEN
        RAISE EXCEPTION 'Оценката мора да биде помеѓу 1 и 10.';
    END IF;

    INSERT INTO Review (user_id, movie_id, rating, comment, review_date)
    VALUES (p_user_id, p_movie_id, p_rating, p_comment, p_review_date);

    RAISE NOTICE 'Рецензијата е успешно зачувана.';
END;
$$;
CALL sp_submit_review(99999, 50, 9, 'Great movie!');
CALL sp_submit_review(72244, 4781, 8, 'Odlicen film, preporacuvam!');
CALL sp_submit_review(72244, 4781, 7, 'Vtorata recenzija...');
CALL sp_submit_review(14667, 4781, 15, 'Super film!');




CREATE OR REPLACE FUNCTION fn_get_dynamic_price(
    p_showtime_id  INT,
    p_seat_type_id INT,
    p_user_id      INT DEFAULT NULL
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_base_price   INT;
    v_total_seats  INT;
    v_sold_seats   INT;
    v_user_tickets INT := 0;
    v_occupancy    NUMERIC;
    v_price        NUMERIC;
    v_type_mult    NUMERIC;
    v_occ_mult     NUMERIC := 1.0;
    v_bulk_mult    NUMERIC := 1.0;
BEGIN
    SELECT s.base_price, h.capacity
    INTO v_base_price, v_total_seats
    FROM Showtime s
    JOIN Hall h ON h.hall_id = s.hall_id
    WHERE s.showtime_id = p_showtime_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Проекцијата со ID % не постои.', p_showtime_id;
    END IF;

    SELECT COUNT(*)
    INTO v_sold_seats
    FROM Ticket
    WHERE showtime_id = p_showtime_id;

    v_occupancy := v_sold_seats::NUMERIC / NULLIF(v_total_seats, 0) * 100;

    IF v_occupancy > 60 THEN
        v_occ_mult := 1.2;
    END IF;

    v_type_mult := CASE p_seat_type_id
        WHEN 1 THEN 1.0
        WHEN 2 THEN 2.0
        WHEN 3 THEN 1.5
        WHEN 4 THEN 1.8
        WHEN 5 THEN 0.7
        ELSE 1.0
    END;

    IF p_user_id IS NOT NULL THEN
        SELECT COUNT(*)
        INTO v_user_tickets
        FROM Reservation r
        JOIN Ticket t ON t.reservation_id = r.reservation_id
        WHERE r.user_id     = p_user_id
          AND r.showtime_id = p_showtime_id
          AND r.status     != 'CANCELLED';
    END IF;

    IF (v_user_tickets + 1) > 4 THEN
        v_bulk_mult := 0.8;
    END IF;

    v_price := v_base_price * v_type_mult * v_occ_mult * v_bulk_mult;

    RETURN v_price::INT;
END;
$$;

SELECT
    s.showtime_id,
    m.title,
    h.name AS sala,
    h.capacity AS vkupno,
    COUNT(t.ticket_id) AS prodadeni,
    ROUND(COUNT(t.ticket_id) * 100.0 / h.capacity, 1) AS procent
FROM Showtime s
JOIN Movie m ON m.movie_id = s.movie_id
JOIN Hall h ON h.hall_id = s.hall_id
LEFT JOIN Ticket t ON t.showtime_id = s.showtime_id
WHERE s.start_time > CURRENT_TIMESTAMP
GROUP BY s.showtime_id, m.title, h.name, h.capacity
ORDER BY procent DESC
LIMIT 10;


SELECT fn_get_dynamic_price(1, 1) AS cena_standard;

SELECT fn_get_dynamic_price(1, 2) AS cena_vip;

select * from Showtime where showtime_id='1761261';
SELECT fn_get_dynamic_price(1761261, 1) AS cena_polna_sala;

SELECT fn_get_dynamic_price(1761261, 2) AS cena_vip_polna_sala;

SELECT fn_get_dynamic_price(1761211, 1, 1) AS cena_so_bulk_popust;

SELECT fn_get_dynamic_price(1761261, 2, 1) AS cena_vip_polna_bulk;

SELECT fn_get_dynamic_price(9999999, 1, 1);



CREATE OR REPLACE PROCEDURE sp_make_reservation(
    p_user_id        INT,
    p_showtime_id    INT,
    p_seat_id        INT,
    p_employee_id    INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_reservation_id INT;
    v_start_time     TIMESTAMP;
    v_hall_id        INT;
    v_movie_title    VARCHAR;
    v_seat_row       INT;
    v_seat_number    INT;
    v_seat_type      VARCHAR;
    v_base_price     INT;
    v_price          INT;
    v_seat_type_id   INT;
    v_occupancy      NUMERIC;
    v_sold           INT;
    v_capacity       INT;
BEGIN
    SELECT s.start_time, s.hall_id, s.base_price, m.title
    INTO v_start_time, v_hall_id, v_base_price, v_movie_title
    FROM Showtime s
    JOIN Movie m ON m.movie_id = s.movie_id
    WHERE s.showtime_id = p_showtime_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Проекцијата не постои.';
    END IF;

    IF v_start_time < CURRENT_TIMESTAMP THEN
        RAISE EXCEPTION 'Не може да се резервира билет за завршена проекција (%).', v_movie_title;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM CinemaUser WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'Корисникот со ID % не постои.', p_user_id;
    END IF;

    SELECT se.seat_type_id, se.seat_row, se.seat_number, st.type
    INTO v_seat_type_id, v_seat_row, v_seat_number, v_seat_type
    FROM Seat se
    JOIN Seat_Type st ON st.seat_type_id = se.seat_type_id
    WHERE se.seat_id = p_seat_id
      AND se.hall_id = v_hall_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Седиштето (ID: %) не постои или не припаѓа на салата за оваа проекција.', p_seat_id;
    END IF;

    IF EXISTS (
        SELECT 1 FROM Ticket
        WHERE showtime_id = p_showtime_id
          AND seat_id     = p_seat_id
    ) THEN
        RAISE EXCEPTION 'Седиштето Ред % - Број % е веќе зафатено. Изберете друго место.', v_seat_row, v_seat_number;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM Reservation r
        JOIN Ticket t ON t.reservation_id = r.reservation_id
        WHERE r.user_id     = p_user_id
          AND r.showtime_id = p_showtime_id
          AND t.seat_id     = p_seat_id
          AND r.status     != 'CANCELLED'
    ) THEN
        RAISE EXCEPTION 'Корисникот веќе има резервација за ова седиште (Ред % - Број %).', v_seat_row, v_seat_number;
    END IF;

    v_price := fn_get_dynamic_price(p_showtime_id, v_seat_type_id, p_user_id);

    SELECT COUNT(*), h.capacity
    INTO v_sold, v_capacity
    FROM Ticket t
    JOIN Showtime s ON s.showtime_id = t.showtime_id
    JOIN Hall h ON h.hall_id = s.hall_id
    WHERE t.showtime_id = p_showtime_id
    GROUP BY h.capacity;

    v_occupancy := COALESCE(v_sold::NUMERIC / NULLIF(v_capacity, 0) * 100, 0);

    INSERT INTO Reservation (user_id, showtime_id, employee_id, reservation_date, status)
    VALUES (p_user_id, p_showtime_id, p_employee_id, CURRENT_DATE, 'PENDING')
    RETURNING reservation_id INTO v_reservation_id;

    INSERT INTO Ticket (showtime_id, seat_id, reservation_id, price, purchase_date)
    VALUES (p_showtime_id, p_seat_id, v_reservation_id, v_price, CURRENT_DATE);

    RAISE NOTICE '=================================';
    RAISE NOTICE 'Резервацијата е успешно креирана!';
    RAISE NOTICE 'Филм:        %', v_movie_title;
    RAISE NOTICE 'Седиште:     Ред % - Број % (%)', v_seat_row, v_seat_number, v_seat_type;
    RAISE NOTICE 'Пополнетост: % проценти (% / % места)', ROUND(v_occupancy, 1), COALESCE(v_sold, 0), v_capacity;

    IF v_occupancy > 60 THEN
        RAISE NOTICE 'Примена динамична цена (+20 проценти поради висока побарувачка)';
    END IF;

    RAISE NOTICE 'Цена:        % ден', v_price;
    RAISE NOTICE 'Резерв. ID:  %', v_reservation_id;
    RAISE NOTICE 'Статус:      PENDING (плаќањето го потврдува)';
    RAISE NOTICE '=================================';

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Грешка при резервација: %', SQLERRM;
END;
$$;


SELECT se.seat_id, se.seat_row, se.seat_number, st.type
FROM Seat se
JOIN Seat_Type st ON st.seat_type_id = se.seat_type_id
WHERE se.hall_id = (SELECT hall_id FROM Showtime WHERE showtime_id = 1761211)
  AND se.seat_id NOT IN (
      SELECT seat_id FROM Ticket WHERE showtime_id = 1761211
  )
ORDER BY se.seat_row, se.seat_number
LIMIT 10;


CALL sp_make_reservation(1, 1761211, 81, 1);
CALL sp_make_reservation(1, 1761211, 101, 1);
CALL sp_make_reservation(1, 1761211, 121, 1);
CALL sp_make_reservation(1, 1761211, 141, 1);
CALL sp_make_reservation(1, 1761211, 161, 1);


SELECT r.reservation_id, r.status, t.seat_id, t.price
FROM Reservation r
JOIN Ticket t ON t.reservation_id = r.reservation_id
WHERE r.user_id = 1 AND r.showtime_id = 1761211
ORDER BY r.reservation_id;




CREATE FUNCTION fn_hall_occupancy_report(p_showtime_id INT)
RETURNS TABLE (
    red_broj INT,
    vizuelna_sala TEXT,
    slobodna_mesta INT,
    zauzeta_mesta INT,
    procent_zauzeca NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    WITH seat_status AS (
        SELECT
            s.seat_row,
            s.seat_number,
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM Ticket t
                    WHERE t.seat_id = s.seat_id
                      AND t.showtime_id = p_showtime_id
                )
                THEN 'X'
                ELSE '○'
            END AS status
        FROM Seat s
        JOIN Hall h ON h.hall_id = s.hall_id
        JOIN Showtime st ON st.hall_id = h.hall_id
        WHERE st.showtime_id = p_showtime_id
        ORDER BY s.seat_row, s.seat_number
    ),
    row_stats AS (
        SELECT
            seat_row,
            STRING_AGG(status, ' ' ORDER BY seat_number) AS layout,
            SUM(CASE WHEN status = '○' THEN 1 ELSE 0 END)::INT AS free_count,
            SUM(CASE WHEN status = 'X' THEN 1 ELSE 0 END)::INT AS occupied_count,
            COUNT(*)::INT AS total_count
        FROM seat_status
        GROUP BY seat_row
    )
    SELECT
        seat_row AS red_broj,
        layout AS vizuelna_sala,
        free_count AS slobodna_mesta,
        occupied_count AS zauzeta_mesta,
        ROUND((occupied_count::NUMERIC / total_count) * 100, 1) AS procent_zauzeca
    FROM row_stats
    ORDER BY seat_row;
END;
$$ LANGUAGE plpgsql STABLE;




CREATE OR REPLACE FUNCTION fn_find_best_seat_position(
    p_showtime_id INT,
    p_num_seats   INT
)
RETURNS TABLE (
    seat_id    INT,
    seat_row   INT,
    seat_number INT,
    quality    VARCHAR,
    poraka     VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_hall_id          INT;
    v_total_rows       INT;
    v_max_seats_per_row INT;
    v_row              INT;
    v_start_seat       INT;
    v_consecutive      INT;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Showtime WHERE showtime_id = p_showtime_id) THEN
        RETURN QUERY SELECT NULL::INT, NULL::INT, NULL::INT, 'NONE'::VARCHAR, 'Прејекција не постои'::VARCHAR;
        RETURN;
    END IF;

    SELECT sh.hall_id INTO v_hall_id
    FROM Showtime sh
    WHERE sh.showtime_id = p_showtime_id;

    SELECT COALESCE(MAX(s.seat_row), 0), COALESCE(MAX(s.seat_number), 0)
    INTO v_total_rows, v_max_seats_per_row
    FROM Seat s
    WHERE s.hall_id = v_hall_id;

    FOR v_row IN 1..v_total_rows LOOP
        FOR v_start_seat IN 1..(v_max_seats_per_row - p_num_seats + 1) LOOP

            SELECT COUNT(*) INTO v_consecutive
            FROM Seat s
            WHERE s.hall_id     = v_hall_id
              AND s.seat_row    = v_row
              AND s.seat_number >= v_start_seat
              AND s.seat_number <  v_start_seat + p_num_seats
              AND NOT EXISTS (
                  SELECT 1 FROM Ticket t
                  WHERE t.seat_id     = s.seat_id
                    AND t.showtime_id = p_showtime_id
              );

            IF v_consecutive = p_num_seats THEN
                RETURN QUERY
                SELECT
                    s.seat_id,
                    s.seat_row,
                    s.seat_number,
                    'AVAILABLE'::VARCHAR,
                    '✓ Место достапно'::VARCHAR
                FROM Seat s
                WHERE s.hall_id     = v_hall_id
                  AND s.seat_row    = v_row
                  AND s.seat_number >= v_start_seat
                  AND s.seat_number <  v_start_seat + p_num_seats
                  AND NOT EXISTS (
                      SELECT 1 FROM Ticket t
                      WHERE t.seat_id     = s.seat_id
                        AND t.showtime_id = p_showtime_id
                  )
                ORDER BY s.seat_number;
                RETURN;
            END IF;

        END LOOP;
    END LOOP;

    RETURN QUERY SELECT NULL::INT, NULL::INT, NULL::INT, 'NONE'::VARCHAR,
        ('Нема ' || p_num_seats || ' последователни седишта во ист ред. Проверете во друг showtime или помал број на седишта.')::VARCHAR;
END;
$$;


SELECT * FROM fn_find_best_seat_position(1761211, 3);
SELECT * FROM fn_find_best_seat_position(1761211, 5);
SELECT * FROM fn_find_best_seat_position(1761211, 1);
SELECT * FROM fn_find_best_seat_position(9999999, 2);


SELECT * FROM fn_hall_occupancy_report(1761247);


SELECT * FROM fn_find_best_seat_position(1761247, 3);


SELECT * FROM fn_find_best_seat_position(1761247, 5);


SELECT * FROM fn_find_best_seat_position(1761247, 8);




CREATE OR REPLACE FUNCTION prevent_seat_double_booking()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM Ticket
        WHERE showtime_id = NEW.showtime_id
          AND seat_id = NEW.seat_id
    ) THEN
        RAISE EXCEPTION 'ERROR: Seat % is already booked for showtime %',
            NEW.seat_id, NEW.showtime_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM Seat s
        JOIN Hall h ON h.hall_id = s.hall_id
        JOIN Showtime st ON st.hall_id = h.hall_id
        WHERE s.seat_id = NEW.seat_id
          AND st.showtime_id = NEW.showtime_id
    ) THEN
        RAISE EXCEPTION 'ERROR: Seat % does not belong to the hall of showtime %',
            NEW.seat_id, NEW.showtime_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trg_prevent_seat_double_booking
BEFORE INSERT ON Ticket
FOR EACH ROW
EXECUTE FUNCTION prevent_seat_double_booking();

INSERT INTO Ticket (showtime_id, seat_id, reservation_id, price, purchase_date)
VALUES (2, 222, 6, 300, '2025-01-01');

INSERT INTO Ticket (showtime_id, seat_id, reservation_id, price, purchase_date)
VALUES (2, 222, 6, 300, '2025-01-02');


SELECT *
FROM Ticket
WHERE showtime_id = 2
  AND seat_id = 222;




CREATE OR REPLACE FUNCTION prevent_showtime_overlap()
RETURNS TRIGGER AS $$
DECLARE
    v_overlapping_count INT;
BEGIN


    SELECT COUNT(*)
    INTO v_overlapping_count
    FROM Showtime
    WHERE hall_id = NEW.hall_id
      AND showtime_id != COALESCE(NEW.showtime_id, -1)

      AND NOT (NEW.end_time <= start_time OR NEW.start_time >= end_time);

    IF v_overlapping_count > 0 THEN
        RAISE EXCEPTION 'ERROR: Showtime overlaps with % existing showtime(s) in hall %',
            v_overlapping_count, NEW.hall_id;
    END IF;

    IF NEW.start_time >= NEW.end_time THEN
        RAISE EXCEPTION 'ERROR: Start time must be before end time';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS trg_prevent_showtime_overlap_insert ON Showtime;
DROP TRIGGER IF EXISTS trg_prevent_showtime_overlap_update ON Showtime;

CREATE TRIGGER trg_prevent_showtime_overlap_insert
BEFORE INSERT ON Showtime
FOR EACH ROW
EXECUTE FUNCTION prevent_showtime_overlap();

CREATE TRIGGER trg_prevent_showtime_overlap_update
BEFORE UPDATE ON Showtime
FOR EACH ROW
EXECUTE FUNCTION prevent_showtime_overlap();


INSERT INTO Showtime (movie_id, hall_id, start_time, end_time, base_price)
VALUES (1, 1, '2026-06-01 10:00:00', '2026-06-01 12:00:00', 400);

INSERT INTO Showtime (movie_id, hall_id, start_time, end_time, base_price)
VALUES (2, 1, '2026-06-01 11:30:00', '2026-06-01 13:30:00', 450);


UPDATE Showtime SET start_time = '2026-06-01 11:30:00'
WHERE showtime_id = (SELECT MAX(showtime_id) FROM Showtime);


SELECT showtime_id, hall_id, start_time, end_time
FROM Showtime
WHERE hall_id = 1
  AND start_time::DATE = '2026-06-01'
ORDER BY start_time;



CREATE OR REPLACE FUNCTION check_reservation_before_showtime()
RETURNS TRIGGER AS $$
DECLARE
    v_start_time TIMESTAMP;
BEGIN
    SELECT s.start_time INTO v_start_time
    FROM Showtime s
    WHERE s.showtime_id = NEW.showtime_id;

    IF v_start_time IS NULL THEN
        RAISE EXCEPTION 'Прејекцијата не постои.';
    END IF;

    IF NEW.reservation_date >= v_start_time::DATE THEN
        RAISE EXCEPTION 'Резервацијата (%) мора да биде пред почетокот на прејекцијата (%)!',
            NEW.reservation_date, v_start_time;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_check_reservation_before_showtime ON Reservation;
CREATE TRIGGER trg_check_reservation_before_showtime
BEFORE INSERT OR UPDATE ON Reservation
FOR EACH ROW
EXECUTE FUNCTION check_reservation_before_showtime();


CREATE OR REPLACE FUNCTION check_payment_after_reservation()
RETURNS TRIGGER AS $$
DECLARE
    v_reservation_date DATE;
BEGIN
    SELECT r.reservation_date INTO v_reservation_date
    FROM Reservation r
    WHERE r.reservation_id = NEW.reservation_id;

    IF v_reservation_date IS NULL THEN
        RAISE EXCEPTION 'Резервацијата не постои.';
    END IF;

    IF NEW.payment_date < v_reservation_date THEN
        RAISE EXCEPTION 'Плаќањето (%) не може да биде пред резервацијата (%)!',
            NEW.payment_date, v_reservation_date;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_check_payment_after_reservation ON ReservationPayment;
CREATE TRIGGER trg_check_payment_after_reservation
BEFORE INSERT OR UPDATE ON ReservationPayment
FOR EACH ROW
EXECUTE FUNCTION check_payment_after_reservation();

CALL sp_buy_ticket(1, 1761265, 1199, 'Cash');


INSERT INTO Reservation (user_id, showtime_id, employee_id, reservation_date, status)
VALUES (1, 1, 1, '2000-01-01', 'PENDING');


INSERT INTO ReservationPayment (user_id, reservation_id, employee_id, amount, payment_date, payment_method)
VALUES (1, 10000001, 1, 500, '2000-01-01', 'Credit Card');


INSERT INTO ReservationPayment (user_id, reservation_id, employee_id, amount, payment_date, payment_method)
VALUES (1, 10000001, 1, 500, CURRENT_DATE + 3, 'Online');