= Програмирање на базата на податоци


== Функции

=== `get_current_price`

Функцијата овозможува динамично одредување на цената на билетот во реално време. Таа го мапира билетот кон неговиот настан, ја проверува валидноста на тековниот датум во рамките на дефинираните временски периоди (`Event_Period`) и применува соодветен попуст. Со користење на '''COALESCE''', функцијата гарантира враќање на основната цена доколку не е дефиниран активен период на попуст.

{{{

CREATE OR REPLACE FUNCTION get_current_price(p_ticket_id BIGINT)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_base_price     DECIMAL(10,2);
    v_happening_id   BIGINT;
    v_discount_percent INT;
    v_final_price    DECIMAL(10,2);
BEGIN
    SELECT base_price, event_happening_id
    INTO v_base_price, v_happening_id
    FROM "Ticket"
    WHERE ticket_id = p_ticket_id;

    SELECT price_discount_percent
    INTO v_discount_percent
    FROM "Event_Period"
    WHERE event_happening_id = v_happening_id
      AND CURRENT_DATE BETWEEN start_date AND end_date
    LIMIT 1;

    v_final_price := COALESCE(v_base_price * (1 - (v_discount_percent / 100.0)), v_base_price);

    RETURN v_final_price;
END;
$$;

}}}


=== `calculate_refund_amount`

Функцијата ја автоматизира пресметката на износот за рефундација. Со примена на фиксна административна такса од 15% (враќање на 85% од износот), се спроведува бизнис политиката за заштита на оперативните трошоци на компанијата. Функцијата вклучува и проверка за постоење на ставката преку '''RAISE EXCEPTION''' за заштита од невалидни прашалници.

{{{

CREATE OR REPLACE FUNCTION calculate_refund_amount(p_order_item_id BIGINT)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_original_price DECIMAL(10,2);
BEGIN
    SELECT item_price
    INTO v_original_price
    FROM "Ticket_Order_Item"
    WHERE order_item_id = p_order_item_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order item record not found.';
    END IF;

    RETURN v_original_price * 0.85;
END;
$$;

}}}


== Процедури

=== `buy_ticket`

Процедурата го координира комплетниот трансакциски циклус на купување: пресметка на моментална цена (преку `get_current_price`), генерирање на уникатен QR-код и ажурирање на статусот на билетот во инвентарот. Со ова се осигурува „Atomic“ природа на операцијата - билетот се резервира инстантно за да се спречи конфликт при истовремена продажба.

{{{

CREATE OR REPLACE PROCEDURE buy_ticket(p_user_id BIGINT, p_ticket_id BIGINT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_price     DECIMAL(10,2);
    v_qr_code   TEXT;
    v_order_id  BIGINT;
BEGIN
    v_price := get_current_price(p_ticket_id);
    v_qr_code := 'QR-' || p_ticket_id || '-' || p_user_id || '-' || EXTRACT(EPOCH FROM NOW());

    INSERT INTO "Ticket_Order" (user_id, order_amount)
    VALUES (p_user_id, v_price)
    RETURNING order_id INTO v_order_id;

    INSERT INTO "Ticket_Order_Item" (order_id, ticket_id, qr_code, item_price)
    VALUES (v_order_id, p_ticket_id, v_qr_code, v_price);

    UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = p_ticket_id;
END;
$$;

}}}


=== `cancel_ticket`

Оваа процедура менаџира процес на враќање на средства. Покрај пресметката на рефундацијата и евидентирањето во табелите `Ticket_Refund` и `Ticket_Refund_Item`, процедурата го ослободува билетот (`is_available = TRUE`), овозможувајќи негова повторна продажба. Оваа грануларност дозволува прецизно сметководствено следење на секоја поединечна ставка од нарачката.

{{{

CREATE OR REPLACE PROCEDURE cancel_ticket(p_order_item_id BIGINT, p_reason TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_refund_amount DECIMAL(10,2);
    v_ticket_id     BIGINT;
    v_order_id      BIGINT;
    v_refund_id     BIGINT;
BEGIN
    v_refund_amount := calculate_refund_amount(p_order_item_id);

    SELECT order_id, ticket_id
    INTO v_order_id, v_ticket_id
    FROM "Ticket_Order_Item"
    WHERE order_item_id = p_order_item_id;

    INSERT INTO "Ticket_Refund" (order_id, refund_amount, reasons)
    VALUES (v_order_id, v_refund_amount, p_reason)
    RETURNING refund_id INTO v_refund_id;

    INSERT INTO "Ticket_Refund_Item" (refund_id, order_item_id, item_price)
    VALUES (v_refund_id, p_order_item_id, v_refund_amount);

    UPDATE "Ticket" SET is_available = TRUE WHERE ticket_id = v_ticket_id;
END;
$$;

}}}


=== `schedule_new_happening`

Процедура наменета за администратори, која овозможува брзо „пуштање во продажба“ на нов настан. Таа автоматски го инстанцира терминот и, што е најважно, врши масовно внесување на записи во `Ticket` табелата за сите седишта во одбраната сала. Со ова се елиминира потребата од рачно креирање на билети, со што се зголемува ефикасноста при планирањето.

{{{

CREATE OR REPLACE PROCEDURE schedule_new_happening(
    p_admin_id BIGINT,
    p_event_id BIGINT,
    p_venue_id BIGINT,
    p_time TIMESTAMP,
    p_duration_minutes INT,
    p_base_price DECIMAL(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_happening_id BIGINT;
BEGIN
    INSERT INTO "Event_Happening" (event_id, event_time, venue_id, duration_minutes, organizers)
    VALUES (p_event_id, p_time, p_venue_id, p_duration_minutes, 'System Generated')
    RETURNING event_happening_id INTO v_happening_id;

    INSERT INTO "Ticket" (base_price, is_available, event_happening_id, seat_id)
    SELECT p_base_price, TRUE, v_happening_id, s.seat_id
    FROM "Seat" s
    JOIN "Section" sec ON s.section_id = sec.section_id
    WHERE sec.venue_id = p_venue_id;
END;
$$;

}}}


=== `create_rating`

Оваа процедура ја заштитува веродостојноста на системот за рецензии. Преку сложен '''SELECT''' прашалник, таа верификува дали корисникот бил вистински сопственик на билет кој не е рефундиран. Со ова се спречува „лажирање“ на оценки и се обезбедува квалитетен социјален доказ за квалитетот на настаните.

{{{

CREATE OR REPLACE PROCEDURE create_rating(p_user_id BIGINT, p_event_happening_id BIGINT, p_rating INT, p_comment TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM "Ticket_Order" o
        JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
        JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
        LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
        WHERE o.user_id = p_user_id
          AND t.event_happening_id = p_event_happening_id
          AND tri.refund_item_id IS NULL
    ) THEN
        RAISE EXCEPTION 'Access Denied: Cannot rate.';
    END IF;

    INSERT INTO "Event_Happening_Rating" (rating, comment, event_happening_id, user_id, timestamp)
    VALUES (p_rating, p_comment, p_event_happening_id, p_user_id, CURRENT_TIMESTAMP);
END;
$$;

}}}


== Тригери

=== `trg_check_user_age`

Овој тригер служи како „чувар“ на законската регулатива. Пред вметнување на било која ставка во нарачка, системот ја пресметува возраста на купувачот врз основа на датумот на раѓање и ја споредува со `min_age` атрибутот на настанот. Доколку корисникот не ја исполнува старосната граница, операцијата се прекинува пред да дојде до финализирање на нарачката.

{{{

CREATE OR REPLACE FUNCTION trg_check_user_age()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_age  INT;
    v_min_age   INT;
    v_user_id   BIGINT;
BEGIN
    SELECT user_id INTO v_user_id FROM "Ticket_Order" WHERE order_id = NEW.order_id;

    SELECT DATE_PART('year', AGE(date_of_birth))
    INTO v_user_age
    FROM "Regular_User"
    WHERE user_id = v_user_id;

    SELECT e.min_age
    INTO v_min_age
    FROM "Ticket" t
    JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
    JOIN "Event" e ON eh.event_id = e.event_id
    WHERE t.ticket_id = NEW.ticket_id;

    IF v_user_age < v_min_age THEN
        RAISE EXCEPTION 'Age restriction.';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER check_age_before_item_insert
BEFORE INSERT ON "Ticket_Order_Item"
FOR EACH ROW EXECUTE FUNCTION trg_check_user_age();

}}}


=== `trg_limit_tickets_per_happening`

Овој тригер обезбедува фер дистрибуција на билети преку ограничување на бројот на карти кои еден корисник може да ги купи за истиот настан. Со лимитирање на купувањето на максимум 4 билети по корисник, се спречува појавата на препродажба („scalping“) и се овозможува поголем број на уникатни посетители да дојдат до влезници.

{{{

CREATE OR REPLACE FUNCTION trg_limit_tickets_per_happening()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_happening_id BIGINT;
    v_count        INT;
BEGIN
    SELECT event_happening_id INTO v_happening_id FROM "Ticket" WHERE ticket_id = NEW.ticket_id;

    SELECT COUNT(*) INTO v_count
    FROM "Ticket_Order_Item" toi
    JOIN "Ticket_Order" o ON toi.order_id = o.order_id
    JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
    WHERE o.user_id = (SELECT user_id FROM "Ticket_Order" WHERE order_id = NEW.order_id)
      AND t.event_happening_id = v_happening_id;

    IF v_count >= 4 THEN
        RAISE EXCEPTION 'Purchase limit reached: You cannot own more than 4 tickets for this event.';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_limit_user_happening_tickets
BEFORE INSERT ON "Ticket_Order_Item"
FOR EACH ROW EXECUTE FUNCTION trg_limit_tickets_per_happening();

}}}


=== `trg_prevent_double_booking`

Овој тригер е критичен за операционата стабилност. Тој спречува преклопување на термини во иста сала, земајќи ги предвид времетраењето на настанот и задолжителното „бафер“ време од 3 часа за техничка подготовка. При секој обид за вметнување или ажурирање, се врши проверка преку '''OVERLAPS''' операторот, со што се гарантира дека распоредот на салата е секогаш логички исправен.

{{{

CREATE OR REPLACE FUNCTION trg_prevent_double_booking()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM "Event_Happening"
        WHERE venue_id = NEW.venue_id
          AND event_happening_id <> NEW.event_happening_id
          AND (NEW.event_time, NEW.event_time + (NEW.duration_minutes || ' minutes')::INTERVAL + INTERVAL '3 hours')
              OVERLAPS (event_time, event_time + (duration_minutes || ' minutes')::INTERVAL + INTERVAL '3 hours')
    ) THEN
        RAISE EXCEPTION 'Venue occupied.';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER check_venue_integrity
BEFORE INSERT OR UPDATE ON "Event_Happening"
FOR EACH ROW EXECUTE FUNCTION trg_prevent_double_booking();

}}}