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

Функции

get_current_price

Оваа функција ја пресметува моменталната цена на билетот со примена на активниот процент на попуст дефиниран за тековниот временски период. Таа проверува дали денешниот датум се наоѓа во некој од опсезите на Event_Period и линеарно ја намалува основната цена на билетот.

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% административна такса. Таа го идентификува билетот преку неговиот уникатен ID во трансакцискиот дел и враќа чисто 85% од оригинално платената цена.

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

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

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;
    v_active    BOOLEAN;
BEGIN
    SELECT is_active INTO v_active FROM "User" WHERE user_id = p_user_id;
    IF v_active IS NOT TRUE THEN
        RAISE EXCEPTION 'Access Denied: User is inactive.';
    END IF;

    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

Оваа процедура менаџира делумно или целосно откажување на купени ставки преку евидентирање на рефундацијата и поврат на средствата со пресметани пенали. По завршување на финансискиот запис во релационите табели, таа автоматски го враќа билетот во статус на достапен за повторна продажба на пазарот.

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

Оваа процедура овозможува авторизиран администратор да закаже нов термин за настан и автоматски да го генерира почетниот инвентар на билети. Системот динамички ги презема сите достапни седишта од дефинираната сала и ги мапира како слободни влезници со почетна базна цена.

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;
    v_active       BOOLEAN;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM "Admin" WHERE user_id = p_admin_id) THEN
        RAISE EXCEPTION 'Access Denied: Not an admin.';
    END IF;

    SELECT is_active INTO v_active FROM "Event" WHERE event_id = p_event_id;
    IF v_active IS NOT TRUE THEN
        RAISE EXCEPTION 'Cannot schedule for an inactive event.';
    END IF;

    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

Оваа процедура ја гарантира веродостојноста на рецензиите преку строга проверка на историјата на нарачки на корисникот. Спуштањето на оцена и коментар е дозволено исклучиво доколку корисникот реално поседува валиден, купен и воедно нерефундиран билет за конкретниот термин на настанот.

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

Овој бизнис тригер врши автоматска валидација на старосната граница пред секое вметнување ставка во кошничката. Системот ја пресметува тековната возраст на купувачот преку неговиот датум на раѓање и инстантно го блокира процесот доколку настанот содржи рестриктивно ограничување за малолетници.

CREATE OR REPLACE FUNCTION trg_check_user_age()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_active    BOOLEAN;
    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 is_active INTO v_active FROM "User" WHERE user_id = v_user_id;

    IF v_active IS NOT TRUE THEN
        RAISE EXCEPTION 'User is inactive.';
    END IF;

    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

...

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

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

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