DatabaseProgramming: db-programming.sql

-- ---------------------------------- ФУНКЦИИ ---------------------------------------- --


-- -------------ФУНКЦИЈА 1: Слободни места за настан--------------------
CREATE OR REPLACE FUNCTION fn_slobodni_mesta(p_eventid BIGINT)
RETURNS INT AS $$
DECLARE
    v_kapacitet INT;
    v_prodadeni INT;
BEGIN
    SELECT SUM(h.capacity)
    INTO v_kapacitet
    FROM event_hall eh
    JOIN hall h ON h.id = eh.hallid
    WHERE eh.eventid = p_eventid;

    SELECT COUNT(*)
    INTO v_prodadeni
    FROM ticket
    WHERE eventid = p_eventid AND status = 'ACTIVE';

    RETURN v_kapacitet - v_prodadeni;
END;
$$ LANGUAGE plpgsql;

SELECT fn_slobodni_mesta(100) AS slobodni_mesta; 
-- Резултат: slobodni_mesta 736

-- Функцијата го пресметува бројот на слободни места за даден настан така што 
-- од вкупниот капацитет на сите сали го одзема бројот на активни тикети. 
-- Ја имплементира бизнис логиката за проверка на достапност пред купување на тикет 
-- и се користи во процедурата proc_kupi_tiket и proc_dodaj_vo_waitlist.


-- --------------ФУНКЦИЈА 2: Вкупен приход по настан ---------------------
CREATE OR REPLACE FUNCTION fn_prihod_po_nastan(p_eventid BIGINT)
RETURNS INT AS $$
DECLARE
    v_prihod INT;
BEGIN
    SELECT SUM(p.amount)
    INTO v_prihod
    FROM payment p
    JOIN user_order uo ON uo.id = p.user_orderid
    JOIN ticket t ON t.user_orderid = uo.id
    WHERE t.eventid = p_eventid AND p.status = 'COMPLETED';

    RETURN v_prihod;
END;
$$ LANGUAGE plpgsql;

SELECT fn_prihod_po_nastan(1) AS vkupen_prihod; 
-- Резултат: 370300

-- Функцијата го пресметува вкупниот приход за даден настан преку 
-- сумирање на сите завршени плаќања поврзани со тикетите за тој настан. 
-- Ја имплементира бизнис логиката за финансиски извештај 
-- и се користи при генерирање на статистики за организаторите на настани.



-- --------------ФУНКЦИЈА 3: Дозвола за оставање рецензија ---------------------

CREATE OR REPLACE FUNCTION fn_moze_review(
    p_userid  BIGINT,
    p_eventid BIGINT
)
RETURNS BOOLEAN AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM ticket
        WHERE app_userid = p_userid
          AND eventid    = p_eventid
          AND status IN ('USED')
    ) THEN
        RETURN FALSE;
    END IF;

    IF EXISTS (
        SELECT 1 FROM review
        WHERE app_userid = p_userid
          AND eventid    = p_eventid
    ) THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

SELECT fn_moze_review(28547, 35) AS moze_review;

-- Резултат: true

-- Функцијата проверува дали корисникот смее да остави рецензија за даден настан 
-- — враќа TRUE само ако корисникот има искористен тикет за тој настан и сè уште нема оставено рецензија. 
-- Ја имплементира бизнис логиката за заштита од лажни рецензии 
-- и се користи во процедурата proc_ostavi_review и тригерот trig_review.


-- --------------ФУНКЦИЈА 4: Статистика за рецензии ---------------------

CREATE OR REPLACE FUNCTION fn_statistika_recenzii(p_eventid BIGINT)
RETURNS TABLE (
    vkupno_recenzii BIGINT,
    prosecen_rejting NUMERIC,
    pet_zvezdi BIGINT,
    cetiri_zvezdi BIGINT,
    tri_zvezdi BIGINT,
    dve_zvezdi BIGINT,
    edna_zvezda BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)                                            AS vkupno_recenzii,
        ROUND(AVG(rating), 2)                              AS prosecen_rejting,
        COUNT(*) FILTER (WHERE rating = 5)                 AS pet_zvezdi,
        COUNT(*) FILTER (WHERE rating = 4)                 AS cetiri_zvezdi,
        COUNT(*) FILTER (WHERE rating = 3)                 AS tri_zvezdi,
        COUNT(*) FILTER (WHERE rating = 2)                 AS dve_zvezdi,
        COUNT(*) FILTER (WHERE rating = 1)                 AS edna_zvezda
    FROM review
    WHERE eventid = p_eventid;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM fn_statistika_recenzii(33);


-- Резултат: 94  3.83  33  29  19  9  4


-- Функцијата враќа детална статистика за рецензиите на даден настан 
-- — вкупен број, просечен рејтинг и распределба по број на ѕвезди. 
-- Ја имплементира бизнис логиката за приказ на статистики на страницата на настанот 
-- и им овозможува на корисниците да донесат информирана одлука пред купување на тикет.


-- --------------ФУНКЦИЈА 5: Позиција во waitlist ---------------------

CREATE OR REPLACE FUNCTION fn_pozicija_vo_waitlist(
    p_userid  BIGINT,
    p_eventid BIGINT
)
RETURNS INT AS $$
DECLARE
    v_pozicija INT;
BEGIN
    SELECT pozicija INTO v_pozicija
    FROM (
        SELECT
            uw.app_userid,
            ROW_NUMBER() OVER (ORDER BY w.created_at ASC) AS pozicija
        FROM waitlist w
        JOIN user_waitlist uw ON uw.waitlistid = w.id
        WHERE w.eventid = p_eventid
          AND w.status  = 'PENDING'
    ) sub
    WHERE app_userid = p_userid;

    IF v_pozicija IS NULL THEN
        RAISE EXCEPTION 'Korisnikot % ne e vo waitlist za event %', p_userid, p_eventid;
    END IF;

    RETURN v_pozicija;
END;
$$ LANGUAGE plpgsql;

SELECT fn_pozicija_vo_waitlist(2, 1) AS pozicija;
-- Резултат: pozicija 1

-- Функцијата го враќа редниот број на корисникот во листата 
-- на чекање за даден настан, подреден по времето на пријавување. 
-- Ја имплементира бизнис логиката за информирање на корисникот за 
-- неговата позиција во редот при пополнет настан.


-- --------------ФУНКЦИЈА 6: Верификација на тикет ---------------------

CREATE OR REPLACE FUNCTION fn_verifikacija_tiket(p_ticketid BIGINT)
RETURNS BOOLEAN AS $$
DECLARE
    v_status     VARCHAR(20);
    v_start_date DATE;
    v_end_date   DATE;
BEGIN
    SELECT t.status, e.start_date, e.end_date
    INTO v_status, v_start_date, v_end_date
    FROM ticket t
    JOIN event e ON e.id = t.eventid
    WHERE t.id = p_ticketid;

    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Tiketot % ne postoi', p_ticketid;
    END IF;

    IF v_status != 'ACTIVE' THEN
        RETURN FALSE;
    END IF;

    IF CURRENT_DATE > v_end_date THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

SELECT fn_verifikacija_tiket(1) AS e_validen;
-- Резултат: false

-- Функцијата проверува дали тикетот е валиден за скенирање при влез на настан 
-- — враќа TRUE само ако тикетот е со статус ACTIVE и настанот сè уште не е завршен. 
-- Ја имплементира бизнис логиката за контрола на влез на настани.



-- ---------------------------------- ПРОЦЕДУРИ ---------------------------------------- --


-- --------------ПРОЦЕДУРА 1: Најава на корисник ---------------------

CREATE OR REPLACE PROCEDURE proc_najavi_korisnik(
    p_email    VARCHAR(50),
    p_password VARCHAR(255)
) LANGUAGE plpgsql AS $$
DECLARE
    v_userid    BIGINT;
    v_role_name VARCHAR(20);
BEGIN
    SELECT id INTO v_userid
    FROM app_user
    WHERE email = p_email
      AND user_password  = p_password
      AND id != 1;

    IF v_userid IS NULL THEN
        RAISE EXCEPTION 'Pogreshеn email ili lozinka';
    END IF;

    SELECT r.role_name INTO v_role_name
    FROM user_roles ur
    JOIN roles r ON r.id = ur.rolesid
    WHERE ur.app_userid = v_userid
    LIMIT 1;

    RAISE NOTICE 'Korisnikot % se najavi kako %', p_email, v_role_name;
END;
$$;


SELECT email, user_password  FROM app_user WHERE id != 1 LIMIT 1;
-- Gertie.Gold0@gmail.com         GertieGold

CALL proc_najavi_korisnik('Gertie.Gold0@gmail.com', 'GertieGold');
-- Резултат: Korisnikot Gertie.Gold0@gmail.com se najavi kako USER

-- Процедурата ја верификува најавата на корисникот преку проверка на email и лозинка 
-- и ја враќа неговата улога во системот. 
-- Ја имплементира бизнис логиката за автентикација на корисници. 
-- Напомена: ова е поедноставена верзија — во продукциски систем 
-- лозинката би се споредувала со bcrypt хаш.


-- --------------ПРОЦЕДУРА 2: Купување на тикет ---------------------

CREATE OR REPLACE PROCEDURE proc_kupi_tiket(
    p_userid BIGINT,
    p_eventid BIGINT,
    p_hallid BIGINT,
    p_typeid BIGINT,
    p_seatid BIGINT,
    p_promo_codeid BIGINT DEFAULT NULL
) LANGUAGE plpgsql AS $$
DECLARE
    v_orderid     BIGINT;
    v_price       INT;
    v_discount    INT := 0;
    v_final_price INT;
    v_status_id   BIGINT;
    v_ticket_code VARCHAR(50);
BEGIN
    SELECT price INTO v_price
    FROM event_ticket_type
    WHERE eventid = p_eventid AND ticket_typeid = p_typeid;

    IF v_price IS NULL THEN
        RAISE EXCEPTION 'Ne postoi ticket type % za event %', p_typeid, p_eventid;
    END IF;

    IF fn_slobodni_mesta(p_eventid) <= 0 THEN
        RAISE EXCEPTION 'Nema slobodni mesta za event %', p_eventid;
    END IF;

    IF p_promo_codeid IS NOT NULL THEN
        SELECT discount_percent INTO v_discount
        FROM promo_code
        WHERE id = p_promo_codeid AND expiration_date >= CURRENT_DATE;

        IF v_discount IS NULL THEN
            RAISE EXCEPTION 'Promo kodot ne e validen ili e istecen';
        END IF;
    END IF;

    v_final_price := v_price - (v_price * v_discount / 100);

    SELECT id INTO v_status_id
    FROM status
    WHERE status_name = 'CREATED';

    INSERT INTO user_order (order_date, total_amount, app_userid, statusid, promo_codeid)
    VALUES (CURRENT_DATE, v_final_price, p_userid, v_status_id, p_promo_codeid)
    RETURNING id INTO v_orderid;

    v_ticket_code := 'TKT-' || v_orderid || '-' || extract(epoch FROM now())::BIGINT;

    INSERT INTO ticket (code, status, ticket_typeid, user_orderid, seatid, app_userid, eventid, hallid)
    VALUES (v_ticket_code, 'ACTIVE', p_typeid, v_orderid, p_seatid, p_userid, p_eventid, p_hallid);

    RAISE NOTICE 'Tiketot e kreiran so kod: %', v_ticket_code;
END;
$$;

CALL proc_kupi_tiket(2, 1, 1, 1, 5, NULL);
-- Резултат: Tiketot e kreiran so kod: TKT-12500002-1778661805

-- Процедурата го имплементира целиот процес на купување тикет 
-- — проверува дали постои типот на тикет за тој настан, дали има слободни места, 
-- дали промо кодот е валиден, ја пресметува конечната цена со попуст и креира нарачка и тикет. 
-- Ја имплементира централната бизнис логика за продажба на тикети во апликацијата.


-- --------------ПРОЦЕДУРА 3: Плаќање на нарачка ---------------------

CREATE OR REPLACE PROCEDURE proc_plati_narachka(
    p_orderid         BIGINT,
    p_payment_methodid BIGINT
) LANGUAGE plpgsql AS $$
DECLARE
    v_amount        INT;
    v_status_name   VARCHAR(20);
    v_paid_id       BIGINT;
BEGIN
    -- Proveri dali orderot postoi
    IF NOT EXISTS (SELECT 1 FROM user_order WHERE id = p_orderid) THEN
        RAISE EXCEPTION 'Order % ne postoi', p_orderid;
    END IF;

    -- Zemi status na orderot
    SELECT s.status_name INTO v_status_name
    FROM user_order uo
    JOIN status s ON s.id = uo.statusid
    WHERE uo.id = p_orderid;

    -- Proveri dali e vekje platen
    IF v_status_name = 'PAID' THEN
        RAISE EXCEPTION 'Order % e vekje platen', p_orderid;
    END IF;

    -- Proveri dali e otkazan
    IF v_status_name = 'CANCELLED' THEN
        RAISE EXCEPTION 'Order % e otkazan i ne moze da se plati', p_orderid;
    END IF;

    -- Zemi go iznosot
    SELECT total_amount INTO v_amount
    FROM user_order
    WHERE id = p_orderid;

    -- Kreiraj payment
    INSERT INTO payment (amount, status, payment_date, user_orderid, payment_methodid)
    VALUES (v_amount, 'COMPLETED', CURRENT_DATE, p_orderid, p_payment_methodid);

   
   -- Azuriraj status na orderot vo PAID direktno vo procedurata
    SELECT id INTO v_paid_id FROM status WHERE status_name = 'PAID';
    UPDATE user_order SET statusid = v_paid_id WHERE id = p_orderid;

    -- Prati notifikacija
    INSERT INTO notification (message, created_at, app_userid)
    SELECT 'Narachkata e uspeshno platena', CURRENT_DATE, app_userid
    FROM user_order WHERE id = p_orderid;
   

    RAISE NOTICE 'Order % e platеn so iznos %', p_orderid, v_amount;
END;
$$;


SELECT uo.id 
FROM user_order uo
JOIN status s ON s.id = uo.statusid
WHERE s.status_name = 'CREATED'
LIMIT 1;

CALL proc_plati_narachka(4, 1);

-- Резултат:
-- Order 4 e azuriran vo PAID
-- Order 4 e platеn so iznos 3000

-- Процедурата го обработува плаќањето на нарачка — проверува дали нарачката постои 
-- и дали не е веќе платена или откажана, креира запис за плаќање, го 
-- ажурира статусот на нарачката во PAID и праќа нотификација до корисникот. 
-- Ја имплементира бизнис логиката за финансиски трансакции во апликацијата.

-- --------------ПРОЦЕДУРА 4: Откажување на нарачка ---------------------


-- Поради споро извршување, додаден е индекс
CREATE INDEX IF NOT EXISTS idx_ticket_userorderid ON ticket(user_orderid);


CREATE OR REPLACE PROCEDURE proc_otkazi_narachka(p_orderid BIGINT)
LANGUAGE plpgsql AS $$
DECLARE
    v_paymentid  BIGINT;
    v_amount     INT;
    v_status_id  BIGINT;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM user_order WHERE id = p_orderid) THEN
        RAISE EXCEPTION 'Order % ne postoi', p_orderid;
    END IF;

    SELECT id INTO v_status_id
    FROM status
    WHERE status_name = 'CANCELLED';

    UPDATE user_order
    SET statusid = v_status_id
    WHERE id = p_orderid;

    UPDATE ticket
    SET status = 'CANCELLED'
    WHERE user_orderid = p_orderid;

    SELECT id, amount INTO v_paymentid, v_amount
    FROM payment
    WHERE user_orderid = p_orderid AND status = 'COMPLETED'
    LIMIT 1;

    IF v_paymentid IS NOT NULL THEN
        INSERT INTO refund (amount, reason, status, paymentid)
        VALUES (v_amount, 'Customer request', 'APPROVED', v_paymentid);

        RAISE NOTICE 'Refund kreiran za order %', p_orderid;
    END IF;

    RAISE NOTICE 'Narachkata % e otkazana', p_orderid;
END;
$$;


-- Барање на order со COMPLETED payment
SELECT uo.id, count(t.id) AS tiketi
FROM user_order uo
JOIN payment p ON p.user_orderid = uo.id AND p.status = 'COMPLETED'
LEFT JOIN ticket t ON t.user_orderid = uo.id
GROUP BY uo.id
ORDER BY tiketi ASC
LIMIT 5;

CALL proc_otkazi_narachka(12500001 );

-- Процедурата го обработува откажувањето на нарачка — ги откажува нарачката 
-- и сите нејзини тикети и доколку нарачката е претходно платена, автоматски креира барање за рефундирање. 
-- Ја имплементира бизнис логиката за откажување и рефундирање во апликацијата.


-- --------------ПРОЦЕДУРА 5: Додавање корисник на waitlist ---------------------


CREATE OR REPLACE PROCEDURE proc_dodaj_vo_waitlist(
    p_userid  BIGINT,
    p_eventid BIGINT
) LANGUAGE plpgsql AS $$
DECLARE
    v_waitlist_id BIGINT;
BEGIN
    -- Dali postoi nastanot
    IF NOT EXISTS (SELECT 1 FROM event WHERE id = p_eventid) THEN
        RAISE EXCEPTION 'Nastanot % ne postoi', p_eventid;
    END IF;

    -- Dali korisnikot postoi
    IF NOT EXISTS (SELECT 1 FROM app_user WHERE id = p_userid AND id != 1) THEN
        RAISE EXCEPTION 'Korisnikot % ne postoi', p_userid;
    END IF;

    -- Ako ima slobodni mesta ne go stavaj vo waitlist
    IF fn_slobodni_mesta(p_eventid) > 0 THEN
        RAISE EXCEPTION 'Ima slobodni mesta za event %, kupi tiket namesto waitlist', p_eventid;
    END IF;

    -- Dali korisnikot vekje e vo waitlist za toj event
    IF EXISTS (
        SELECT 1 FROM user_waitlist uw
        JOIN waitlist w ON w.id = uw.waitlistid
        WHERE uw.app_userid = p_userid
          AND w.eventid = p_eventid
          AND w.status = 'PENDING'
    ) THEN
        RAISE EXCEPTION 'Korisnikot % vekje e vo waitlist za event %', p_userid, p_eventid;
    END IF;

    -- Kreiranje nov waitlist zapis za toj event
    INSERT INTO waitlist (created_at, status, eventid)
    VALUES (CURRENT_DATE, 'PENDING', p_eventid)
    RETURNING id INTO v_waitlist_id;

    -- Dodavanje na korisnikot vo waitlist
    INSERT INTO user_waitlist (app_userid, waitlistid)
    VALUES (p_userid, v_waitlist_id);

    -- Trigеrot trig_waitlist ke go konfirmira avtomatski
    -- koga ke se oslobodi mesto (pri otkazuvanje na tiket)

    -- Prati notifikacija
    INSERT INTO notification (message, created_at, app_userid)
    VALUES ('You have been added to the waitlist.', CURRENT_DATE, p_userid);

    RAISE NOTICE 'Korisnikot % e dodaden vo waitlist za event %', p_userid, p_eventid;
END;
$$;

CALL proc_dodaj_vo_waitlist(
    (SELECT id FROM app_user WHERE id != 1 LIMIT 1),
    (SELECT id FROM event LIMIT 1)
);

SELECT id FROM event WHERE fn_slobodni_mesta(id) <= 0 LIMIT 1;
-- Процедурата го додава корисникот на листата на чекање за пополнет настан
--  — проверува дали настанот и корисникот постојат, дали навистина нема слободни места, 
-- дали корисникот не е веќе на листата и по успешно додавање праќа нотификација. 
-- Ја имплементира бизнис логиката за управување со листата на чекање при пополнети настани.


-- --------------ПРОЦЕДУРА 6: Оставање на рецензија ---------------------


CREATE OR REPLACE PROCEDURE proc_ostavi_review(
    p_userid   BIGINT,
    p_eventid  BIGINT,
    p_rating   INT,
    p_comment  VARCHAR(255) DEFAULT NULL
) LANGUAGE plpgsql AS $$
BEGIN
    -- Proveri dali nastanot postoi
    IF NOT EXISTS (SELECT 1 FROM event WHERE id = p_eventid) THEN
        RAISE EXCEPTION 'Nastanot % ne postoi', p_eventid;
    END IF;

    -- Proveri dali nastanot e minаt
    IF NOT EXISTS (SELECT 1 FROM event WHERE id = p_eventid AND end_date < CURRENT_DATE) THEN
        RAISE EXCEPTION 'Ne moze da se ostavi review za nastan koj ne e zavrshen';
    END IF;

    -- Koristi ja funkcijata fn_moze_review
    IF NOT fn_moze_review(p_userid, p_eventid) THEN
        RAISE EXCEPTION 'Korisnikot % ne smee da ostavi review za event %', p_userid, p_eventid;
    END IF;

    -- Proveri dali ratingot e validen
    IF p_rating NOT BETWEEN 1 AND 5 THEN
        RAISE EXCEPTION 'Ratingot mora da bide pomegu 1 i 5';
    END IF;

    -- Vmetni review
    INSERT INTO review (rating, review_comment, app_userid, eventid)
    VALUES (p_rating, p_comment, p_userid, p_eventid);

    RAISE NOTICE 'Review e ostaveno za event % od korisnik %', p_eventid, p_userid;
END;
$$;


SELECT app_userid, eventid 
FROM ticket 
WHERE status = 'USED'
LIMIT 1;

-- 21615        49952

SELECT * FROM review WHERE app_userid = 21615 AND eventid = 49952;
-- Овој корисник нема reviews за тој настан

CALL proc_ostavi_review(21615, 49952, 5, 'Odlichno iskustvo!');
-- Резултат: Review e ostaveno za event 49952 od korisnik 21615

-- Процедурата го обработува оставањето рецензија за минат настан 
-- — проверува дали настанот постои и е завршен, го користи fn_moze_review за 
-- да провери дали корисникот смее да остави рецензија и дали рејтингот е во валиден опсег. 
-- Ја имплементира бизнис логиката за систем на рецензии во апликацијата.


-- ---------------------------------- ТРИГЕРИ ---------------------------------------- --


-- --------------ТРИГЕР 1: При откажан тикет -> confirm на следен од waitlist ---------------------
CREATE OR REPLACE FUNCTION fn_trig_waitlist()
RETURNS TRIGGER AS $$
DECLARE
    v_waitlist_id BIGINT;
BEGIN
    IF NEW.status = 'CANCELLED' AND OLD.status != 'CANCELLED' THEN

        SELECT w.id INTO v_waitlist_id
        FROM waitlist w
        WHERE w.eventid = NEW.eventid AND w.status = 'PENDING'
        ORDER BY w.created_at
        LIMIT 1;

        IF v_waitlist_id IS NOT NULL THEN
            UPDATE waitlist
            SET status = 'CONFIRMED'
            WHERE id = v_waitlist_id;

            RAISE NOTICE 'Waitlist % e potvrden za event %', v_waitlist_id, NEW.eventid;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_waitlist
AFTER UPDATE ON ticket
FOR EACH ROW
EXECUTE FUNCTION fn_trig_waitlist();


UPDATE ticket SET status = 'CANCELLED' WHERE id = (
    SELECT t.id FROM ticket t
    JOIN waitlist w ON w.eventid = t.eventid
    WHERE w.status = 'PENDING'
    LIMIT 1
);

-- Тригерот се активира автоматски по секое ажурирање на статусот на тикет во CANCELLED 
-- и го потврдува првиот корисник во редот на чекање за тој настан. 
-- Ја имплементира бизнис логиката за автоматско управување со листата на чекање при ослободување на место.


-- -------------- ТРИГЕР 2: Заштита од двоен refund ---------------------

CREATE OR REPLACE FUNCTION fn_trig_refund()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM refund
        WHERE paymentid = NEW.paymentid
          AND status IN ('REQUESTED', 'APPROVED')
    ) THEN
        RAISE EXCEPTION 'Vekje postoi refund za payment %', NEW.paymentid;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_refund
BEFORE INSERT ON refund
FOR EACH ROW
EXECUTE FUNCTION fn_trig_refund();

-- Test - obid za dvoen refund
-- Najdi payment koj vekje ima refund
SELECT paymentid FROM refund 
WHERE status IN ('REQUESTED', 'APPROVED') 
LIMIT 1; --1


INSERT INTO refund (amount, reason, status, paymentid)
VALUES (100, 'Test duplikat', 'REQUESTED', 1); 
-- Резултат: Error: Vekje postoi refund za payment 1

-- Тригерот се активира пред секое вметнување во табелата REFUND 
-- и проверува дали за тоа плаќање веќе постои активно барање за рефундирање. 
-- Ја имплементира бизнис логиката за заштита од двојно рефундирање на исто плаќање.


-- -------------- ТРИГЕР 3: Ажурирање на quantity_available ---------------------

CREATE OR REPLACE FUNCTION fn_trig_quantity()
RETURNS TRIGGER AS $$
BEGIN
    -- Pri nov ACTIVE tiket - namalи quantity_available
    IF TG_OP = 'INSERT' AND NEW.status = 'ACTIVE' THEN
        UPDATE event_ticket_type
        SET quantity_available = quantity_available - 1
        WHERE eventid       = NEW.eventid
          AND ticket_typeid = NEW.ticket_typeid;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Ne postoi ticket type % za event %', 
                NEW.ticket_typeid, NEW.eventid;
        END IF;

    -- Pri otkazuvanje na tiket - zgolemi quantity_available
    ELSIF TG_OP = 'UPDATE' 
        AND NEW.status = 'CANCELLED' 
        AND OLD.status != 'CANCELLED' THEN

        UPDATE event_ticket_type
        SET quantity_available = quantity_available + 1
        WHERE eventid       = NEW.eventid
          AND ticket_typeid = NEW.ticket_typeid;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_quantity
AFTER INSERT OR UPDATE ON ticket
FOR EACH ROW
EXECUTE FUNCTION fn_trig_quantity();

-- Test INSERT - нов тикет
SELECT quantity_available FROM event_ticket_type 
WHERE eventid = 33 AND ticket_typeid = 1;
-- quantity_available пред insert == 692

-- Додавање нов тикет
INSERT INTO ticket (code, status, ticket_typeid, user_orderid, seatid, app_userid, eventid, hallid)
VALUES ('TKT-TEST-TRIG', 'ACTIVE', 1,
        (SELECT MIN(id) FROM user_order),
        NULL,
        (SELECT MIN(id) FROM app_user WHERE id != 1),
        33,
        (SELECT MIN(hallid) FROM event_hall WHERE eventid = 33));


SELECT quantity_available FROM event_ticket_type 
WHERE eventid = 33 AND ticket_typeid = 1; 
-- quantity_available по insert == 691

-- Test UPDATE - откажување тикет
UPDATE ticket SET status = 'CANCELLED' WHERE code = 'TKT-TEST-TRIG';

SELECT quantity_available FROM event_ticket_type 
WHERE eventid = 33 AND ticket_typeid = 1;
-- quantity_available по откажување == 692


-- Тригерот се активира по секое вметнување или ажурирање на тикет и 
-- автоматски го ажурира бројот на достапни тикети во табелата event_ticket_type 
-- — го намалува при продажба и го зголемува при откажување. 
-- Ја имплементира бизнис логиката за следење на достапноста на тикети во реално време.