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

Функции

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();