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

Функции

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;

    IF FOUND THEN
        v_final_price := v_base_price * (1 - (v_discount_percent / 100.0));
    ELSE
        v_final_price := v_base_price;
    END IF;

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

    v_refund_amount := v_original_price * 0.85;

    RETURN v_refund_amount;
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;
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;

    COMMIT;
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;

    RAISE NOTICE 'Ticket item cancelled successfully. Refund amount: %', v_refund_amount;
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;
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM "Admin" WHERE user_id = p_admin_id
    ) THEN
        RAISE EXCEPTION 'Access Denied: Only administrators can schedule a new event happening.';
    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 Organizer')
    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;

    RAISE NOTICE 'Event happening successfully scheduled by admin % and tickets generated.', p_admin_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: You can only rate events you attended and did not refund.';
    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);

    RAISE NOTICE 'Rating successfully submitted.';
END;
$$;

Тригери

trg_check_user_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 'Access Denied: User does not meet the minimum age restriction for this event.';
    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_sync_order_amount

Овој тригер одржува конзистентност на податоците преку автоматска синхронизација на финансиската сума во главната нарачка при секоја измена на нејзините ставки. Тој динамички го прекалкулира вкупниот износ земајќи ги предвид само активните и нерефундираните билети по секој INSERT, UPDATE или DELETE.

CREATE OR REPLACE FUNCTION trg_sync_order_amount()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_order_id BIGINT;
    v_new_total DECIMAL(10,2);
BEGIN
    IF TG_OP = 'DELETE' THEN
        v_target_order_id := OLD.order_id;
    ELSE
        v_target_order_id := NEW.order_id;
    END IF;

    SELECT COALESCE(SUM(toi.item_price), 0.00) INTO v_new_total
    FROM "Ticket_Order_Item" toi
    LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
    WHERE toi.order_id = v_target_order_id AND tri.refund_item_id IS NULL;

    UPDATE "Ticket_Order"
    SET order_amount = v_new_total
    WHERE order_id = v_target_order_id;

    RETURN NEW;
END;
$$;

CREATE TRIGGER sync_order_total_on_change
AFTER INSERT OR UPDATE OR DELETE ON "Ticket_Order_Item"
FOR EACH ROW EXECUTE FUNCTION trg_sync_order_amount();

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 'Access Denied: Venue is already occupied or currently in technical preparation (3-hour buffer required).';
    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();