--------------Функции------------------------------
---------- Вкупна цена со попуст---------
CREATE OR REPLACE FUNCTION get_reservation_total_with_discount(
    p_reservation_id INT,
    p_promotion_id INT
)
RETURNS FLOAT AS $$
DECLARE
    v_total FLOAT;
    v_discount FLOAT;
    v_final FLOAT;
BEGIN
    -- Вкупна цена на сите тикети за резервацијата
    SELECT SUM(price)
    INTO v_total
    FROM TICKET
    WHERE reservation_id = p_reservation_id;

    -- Попустот од промоцијата (во проценти)
    SELECT discount
    INTO v_discount
    FROM PROMOTION
    WHERE promotion_id = p_promotion_id;

    -- Пресметај крајна цена
    v_final := v_total - (v_total * v_discount / 100);

    RETURN v_final;
END;
$$ LANGUAGE plpgsql;
-----------------Слободни места-------------
CREATE OR REPLACE FUNCTION get_available_seats(
    p_screening_id INT
)
RETURNS INT AS $$
DECLARE
    v_capacity INT;
    v_sold INT;
BEGIN
    -- Капацитет на салата
    SELECT ch.capacity
    INTO v_capacity
    FROM SCREENING s
    JOIN CINEMA_HALL ch ON ch.cinemahall_id = s.cinemahall_id
    WHERE s.screening_id = p_screening_id;

    -- Продадени тикети
    SELECT COUNT(*)
    INTO v_sold
    FROM TICKET
    WHERE screening_id = p_screening_id;

    RETURN v_capacity - v_sold;
END;
$$ LANGUAGE plpgsql;
-------------------Просечен рејтинг--------------
CREATE OR REPLACE FUNCTION get_movie_avg_rating(
    p_movie_id INT
)
RETURNS FLOAT AS $$
DECLARE
    v_avg FLOAT;
BEGIN
    SELECT ROUND(AVG(rating)::numeric, 2)
    INTO v_avg
    FROM REVIEW
    WHERE movie_id = p_movie_id;

    RETURN v_avg;
END;
$$ LANGUAGE plpgsql;
----------------Процедури--------------------
----------------Генерирање тикети за проекција  — ------------
CREATE OR REPLACE PROCEDURE generate_tickets_for_screening(
    p_screening_id INT,
    p_price FLOAT
)
LANGUAGE plpgsql AS $$
DECLARE
    v_cinemahall_id INT;
    v_cinema_id INT;
    v_seat RECORD;
    v_count INT := 0;
    v_screening_date DATE;
BEGIN
    -- Провери дали проекцијата постои
    IF NOT EXISTS (SELECT 1 FROM SCREENING WHERE screening_id = p_screening_id) THEN
        RAISE EXCEPTION 'Проекцијата со ID % не постои!', p_screening_id;
    END IF;

    -- Провери дали датумот на проекцијата не е во минатото
    SELECT date INTO v_screening_date FROM SCREENING WHERE screening_id = p_screening_id;
    IF v_screening_date < CURRENT_DATE THEN
        RAISE EXCEPTION 'Не може да се генерираат тикети за минати проекции! Датум: %', v_screening_date;
    END IF;

    -- Провери дали цената е валидна
    IF p_price <= 0 THEN
        RAISE EXCEPTION 'Цената мора да биде поголема од 0!';
    END IF;

    -- Земи ја салата
    SELECT cinemahall_id INTO v_cinemahall_id
    FROM SCREENING WHERE screening_id = p_screening_id;

    -- Провери дали салата постои
    IF NOT EXISTS (SELECT 1 FROM CINEMA_HALL WHERE cinemahall_id = v_cinemahall_id) THEN
        RAISE EXCEPTION 'Салата со ID % не постои!', v_cinemahall_id;
    END IF;

    -- Провери дали салата е активна
    IF NOT EXISTS (SELECT 1 FROM CINEMA_HALL WHERE cinemahall_id = v_cinemahall_id AND status = 'ACTIVE') THEN
        RAISE EXCEPTION 'Салата со ID % не е активна!', v_cinemahall_id;
    END IF;

    -- Провери дали киното постои
    SELECT cinema_id INTO v_cinema_id FROM CINEMA_HALL WHERE cinemahall_id = v_cinemahall_id;
    IF NOT EXISTS (SELECT 1 FROM CINEMA WHERE cinema_id = v_cinema_id) THEN
        RAISE EXCEPTION 'Киното со ID % не постои!', v_cinema_id;
    END IF;

    -- Провери дали воопшто има седишта во салата
    IF NOT EXISTS (SELECT 1 FROM SEAT WHERE cinemahall_id = v_cinemahall_id) THEN
        RAISE EXCEPTION 'Салата со ID % нема седишта!', v_cinemahall_id;
    END IF;

    -- За секое седиште во салата креирај тикет
    FOR v_seat IN
        SELECT seat_id FROM SEAT WHERE cinemahall_id = v_cinemahall_id
    LOOP
        IF NOT EXISTS (
            SELECT 1 FROM TICKET
            WHERE seat_id = v_seat.seat_id
            AND screening_id = p_screening_id
        ) THEN
            INSERT INTO TICKET (price, user_id, reservation_id, seat_id, screening_id)
            VALUES (p_price, NULL, NULL, v_seat.seat_id, p_screening_id);
            v_count := v_count + 1;
        END IF;
    END LOOP;

    IF v_count = 0 THEN
        RAISE NOTICE 'Сите тикети за проекција % веќе се генерирани!', p_screening_id;
    ELSE
        RAISE NOTICE 'Генерирани % тикети за проекција % во сала % по цена %',
            v_count, p_screening_id, v_cinemahall_id, p_price;
    END IF;
END;
$$;
-------------------------Процедура 2 — Купување тикет со производи-----------------
CREATE OR REPLACE PROCEDURE purchase_ticket_with_products(
    p_ticket_id INT,
    p_user_id VARCHAR(13),
    p_reservation_id INT,
    p_products INT[]
)
LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INT;
    v_price FLOAT;
    v_screening_id INT;
    v_screening_date DATE;
    v_total_price FLOAT := 0;
BEGIN
    -- Провери дали тикетот постои
    IF NOT EXISTS (SELECT 1 FROM TICKET WHERE ticket_id = p_ticket_id) THEN
        RAISE EXCEPTION 'Тикетот со ID % не постои!', p_ticket_id;
    END IF;

    -- Провери дали тикетот е слободен
    IF EXISTS (SELECT 1 FROM TICKET WHERE ticket_id = p_ticket_id AND user_id IS NOT NULL) THEN
        RAISE EXCEPTION 'Тикетот со ID % е веќе купен!', p_ticket_id;
    END IF;

    -- Провери дали корисникот постои
    IF NOT EXISTS (SELECT 1 FROM "USER" WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'Корисникот % не постои!', p_user_id;
    END IF;

    -- Провери дали резервацијата постои
    IF NOT EXISTS (SELECT 1 FROM RESERVATION WHERE reservation_id = p_reservation_id) THEN
        RAISE EXCEPTION 'Резервацијата со ID % не постои!', p_reservation_id;
    END IF;

    -- Провери дали резервацијата не е откажана
    IF EXISTS (SELECT 1 FROM RESERVATION WHERE reservation_id = p_reservation_id AND status = 'CANCELLED') THEN
        RAISE EXCEPTION 'Резервацијата со ID % е откажана!', p_reservation_id;
    END IF;

    -- Провери дали проекцијата не е во минатото
    SELECT screening_id INTO v_screening_id FROM TICKET WHERE ticket_id = p_ticket_id;
    SELECT date INTO v_screening_date FROM SCREENING WHERE screening_id = v_screening_id;
    IF v_screening_date < CURRENT_DATE THEN
        RAISE EXCEPTION 'Не може да се купи тикет за минати проекции! Датум: %', v_screening_date;
    END IF;

    -- Провери дали производите постојат и пресметај вкупна цена
    FOREACH v_product_id IN ARRAY p_products
    LOOP
        IF NOT EXISTS (SELECT 1 FROM PRODUCT WHERE product_id = v_product_id) THEN
            RAISE EXCEPTION 'Производот со ID % не постои!', v_product_id;
        END IF;
        SELECT price INTO v_price FROM PRODUCT WHERE product_id = v_product_id;
        v_total_price := v_total_price + v_price;
    END LOOP;

    -- Ажурирај го тикетот
    UPDATE TICKET
    SET user_id = p_user_id,
        reservation_id = p_reservation_id
    WHERE ticket_id = p_ticket_id;

    -- Додај ги производите
    FOREACH v_product_id IN ARRAY p_products
    LOOP
        INSERT INTO PURCHASED_PRODUCT (numbers, ticket_id, product_id)
        VALUES (1, p_ticket_id, v_product_id);
    END LOOP;

    RAISE NOTICE 'Тикетот % е успешно купен! Корисник: %, Производи: %, Вкупна цена на производи: %',
        p_ticket_id, p_user_id, array_length(p_products, 1), v_total_price;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Грешка: % — се прави ROLLBACK!', SQLERRM;
        ROLLBACK;
        RETURN;
END;
$$;
----------------------------Процедура 3 — Додај review за филм--------------------
CREATE OR REPLACE PROCEDURE add_movie_review(
    p_user_id VARCHAR(13),
    p_movie_id INT,
    p_rating INT,
    p_comment VARCHAR(255)
)
LANGUAGE plpgsql AS $$
DECLARE
    v_existing_reviews INT;
    v_avg_rating FLOAT;
BEGIN
    -- Провери дали корисникот постои
    IF NOT EXISTS (SELECT 1 FROM "USER" WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'Корисникот % не постои!', p_user_id;
    END IF;

    -- Провери дали филмот постои
    IF NOT EXISTS (SELECT 1 FROM MOVIE WHERE movie_id = p_movie_id) THEN
        RAISE EXCEPTION 'Филмот со ID % не постои!', p_movie_id;
    END IF;

    -- Провери дали рејтингот е валиден
    IF p_rating < 1 OR p_rating > 10 THEN
        RAISE EXCEPTION 'Рејтингот мора да биде помеѓу 1 и 10! Внесен рејтинг: %', p_rating;
    END IF;

    -- Провери дали коментарот е празен
    IF p_comment IS NULL OR TRIM(p_comment) = '' THEN
        RAISE EXCEPTION 'Коментарот не може да биде празен!';
    END IF;

    -- Провери дали корисникот купил тикет за овој филм
    IF NOT EXISTS (
        SELECT 1 FROM TICKET t
        JOIN SCREENING s ON s.screening_id = t.screening_id
        WHERE t.user_id = p_user_id
        AND s.movie_id = p_movie_id
    ) THEN
        RAISE EXCEPTION 'Корисникот % не купил тикет за филмот %!', p_user_id, p_movie_id;
    END IF;

    -- Провери дали корисникот веќе оставил review за овој филм
    IF EXISTS (SELECT 1 FROM REVIEW WHERE user_id = p_user_id AND movie_id = p_movie_id) THEN
        RAISE EXCEPTION 'Корисникот % веќе оставил review за филмот %!', p_user_id, p_movie_id;
    END IF;

    -- Додај review
    INSERT INTO REVIEW (comment, date, user_id, movie_id, rating)
    VALUES (p_comment, CURRENT_DATE, p_user_id, p_movie_id, p_rating);

    -- Пресметај нов просечен рејтинг
    SELECT COUNT(*), ROUND(AVG(rating)::numeric, 2)
    INTO v_existing_reviews, v_avg_rating
    FROM REVIEW WHERE movie_id = p_movie_id;

    RAISE NOTICE 'Review за филм % додаден! Рејтинг: %, Вкупно reviews: %, Просечен рејтинг: %',
        p_movie_id, p_rating, v_existing_reviews, v_avg_rating;
END;
$$;

-----------------------Тригери------------------------------------
----------------Тригер 1 — Спречи двојно резервирање на исто седиште-----------
CREATE OR REPLACE FUNCTION check_seat_availability()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM TICKET
        WHERE seat_id = NEW.seat_id
        AND screening_id = NEW.screening_id
    ) THEN
        RAISE EXCEPTION 'Седиштето % е веќе зафатено за оваа проекција!', NEW.seat_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_check_seat_availability
BEFORE INSERT ON TICKET
FOR EACH ROW
EXECUTE FUNCTION check_seat_availability();
-----------------------Тригер 2 — Автоматска нотификација при резервација-----------
    CREATE OR REPLACE FUNCTION notify_on_reservation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO NOTIFICATION (message, date, user_id)
    VALUES (
        'Вашата резервација е успешно креирана со ID: ' || NEW.reservation_id,
        CURRENT_DATE,
        NEW.user_id
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_notify_on_reservation
AFTER INSERT ON USER_RESERVATION
FOR EACH ROW
EXECUTE FUNCTION notify_on_reservation();
-----------------------Тригер 3 — Автоматски COMPLETED статус----------------------
CREATE OR REPLACE FUNCTION complete_past_reservations()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date < CURRENT_DATE THEN
        UPDATE RESERVATION r
        SET status = 'COMPLETED'
        FROM TICKET t
        WHERE t.reservation_id = r.reservation_id
        AND t.screening_id = NEW.screening_id
        AND r.status = 'CONFIRMED';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE EXTENSION IF NOT EXISTS postgis;



-- Тест процедура 1 — генерирај тикети
CALL generate_tickets_for_screening(55105026, 350.00);

ALTER TABLE TICKET ALTER COLUMN user_id DROP NOT NULL;
ALTER TABLE TICKET ALTER COLUMN reservation_id DROP NOT NULL;

SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'ticket'
AND column_name IN ('user_id', 'reservation_id');

CALL generate_tickets_for_screening(55105026, 350.00);

-- Земи слободен тикет од новата проекција
SELECT ticket_id FROM TICKET
WHERE screening_id = 55105026
AND user_id IS NULL
LIMIT 1;

-- Земи вистински user_id
SELECT user_id FROM "USER" LIMIT 1;

-- Земи вистински reservation_id со статус PENDING
SELECT reservation_id FROM RESERVATION
WHERE status = 'PENDING'
LIMIT 1;

-- Земи вистински product_id
SELECT product_id FROM PRODUCT LIMIT 3;

CALL purchase_ticket_with_products(12818005, 'admin01', 4000005, ARRAY[1, 2, 3]);
CALL add_movie_review('admin01', 195, 8, 'Одличен филм, препорачувам!');

-- Земи филм за кој admin01 има тикет но нема review
SELECT DISTINCT s.movie_id
FROM TICKET t
JOIN SCREENING s ON s.screening_id = t.screening_id
WHERE t.user_id = 'admin01'
AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin01')
LIMIT 1;

SELECT DISTINCT s.movie_id
FROM TICKET t
JOIN SCREENING s ON s.screening_id = t.screening_id
WHERE t.user_id = 'admin01'
AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin01')
LIMIT 1;

SELECT DISTINCT s.movie_id
FROM TICKET t
JOIN SCREENING s ON s.screening_id = t.screening_id
WHERE t.user_id = 'admin01'
AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin01')
LIMIT 1;
SELECT user_id FROM "USER" WHERE user_id != 'admin01' LIMIT 1;

SELECT DISTINCT s.movie_id
FROM TICKET t
JOIN SCREENING s ON s.screening_id = t.screening_id
WHERE t.user_id = 'admin02'
AND s.movie_id NOT IN (SELECT movie_id FROM REVIEW WHERE user_id = 'admin02')
LIMIT 1;
CALL add_movie_review('admin02', 195, 7, 'Многу добар филм!');