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

Функции

get_current_price

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

CREATE OR REPLACE FUNCTION get_current_price(p_ticket_id BIGINT)
RETURNS FLOAT4 AS $$
DECLARE
    v_base_price FLOAT4;
    v_happening_id BIGINT;
    v_percent INT;
    v_increase BOOLEAN;
    v_final_price FLOAT4;
BEGIN
    SELECT base_price, event_happening_id INTO v_base_price, v_happening_id
    FROM "Ticket" WHERE ticket_id = p_ticket_id;

    SELECT price_change_percent, increase_decrease INTO v_percent, v_increase
    FROM "Event_Period"
    WHERE event_happening_id = v_happening_id
      AND CURRENT_DATE BETWEEN start_date AND end_date
    LIMIT 1;

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

    RETURN v_final_price;
END;
$$ LANGUAGE plpgsql;

calculate_refund_amount

Оваа функција ја пресметува сумата за рефундација со примена на 15% задршка од оригиналната уплата. Таа го идентификува плаќањето преку неговиот уникатен ID и враќа 85% од износот како финален износ за поврат на средства.

CREATE OR REPLACE FUNCTION calculate_refund_amount(p_purchase_id BIGINT)
RETURNS FLOAT4 AS $$
DECLARE
    v_original_price FLOAT4;
    v_refund_amount FLOAT4;
BEGIN
    SELECT purchase_amount INTO v_original_price
    FROM "Ticket_Purchase"
    WHERE purchase_id = p_purchase_id;

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

    v_refund_amount := v_original_price * 0.85;

    RETURN v_refund_amount;
END;
$$ LANGUAGE plpgsql;

Процедури

buy_ticket

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

CREATE OR REPLACE PROCEDURE buy_ticket(
    p_user_id BIGINT,
    p_ticket_id BIGINT
)
AS $$
DECLARE
    v_price FLOAT4;
    v_qr_code TEXT;
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_Purchase" (ticket_id, user_id, qr_code, purchase_amount)
    VALUES (p_ticket_id, p_user_id, v_qr_code, v_price);

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

cancel_ticket

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

CREATE OR REPLACE PROCEDURE cancel_ticket(
    p_purchase_id BIGINT,
    p_reason TEXT
)
AS $$
DECLARE
    v_refund_amount FLOAT4;
    v_ticket_id BIGINT;
BEGIN
    v_refund_amount := calculate_refund_amount(p_purchase_id);

    SELECT ticket_id INTO v_ticket_id 
    FROM "Ticket_Purchase" 
    WHERE purchase_id = p_purchase_id;

    INSERT INTO "Ticket_Refund" (purchase_id, refund_amount, reason)
    VALUES (p_purchase_id, v_refund_amount, p_reason);

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

    RAISE NOTICE 'Ticket cancelled. Refund amount: %', v_refund_amount;
END;
$$ LANGUAGE plpgsql;

schedule_new_happening

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

CREATE OR REPLACE PROCEDURE schedule_new_happening(
    p_event_id BIGINT,
    p_venue_id BIGINT,
    p_time TIMESTAMP,
    p_duration INT,
    p_base_price FLOAT4
)
AS $$
DECLARE
    v_happening_id BIGINT;
BEGIN
    INSERT INTO "Event_Happening" (event_id, event_time, venue_id, duration)
    VALUES (p_event_id, p_time, p_venue_id, p_duration)
    RETURNING event_happening_id INTO v_happening_id;

    INSERT INTO "Ticket" (ticket_type, base_price, is_available, event_happening_id, seat_id)
    SELECT 
        'Standard', 
        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 scheduled and tickets generated.';
END;
$$ LANGUAGE plpgsql;

create_rating

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

CREATE OR REPLACE PROCEDURE create_rating(
    p_user_id BIGINT,
    p_event_happening_id BIGINT,
    p_rating INT,
    p_comment TEXT
)
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 
        FROM "Ticket_Purchase" tp
        JOIN "Ticket" t ON tp.ticket_id = t.ticket_id
        LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id
        WHERE tp.user_id = p_user_id 
          AND t.event_happening_id = p_event_happening_id
          AND tr.refund_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)
    VALUES (p_rating, p_comment, p_event_happening_id, p_user_id);

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

Тригери

trg_check_user_age

Овој тригер врши автоматска проверка на старосната граница на корисникот пред да му дозволи да купи билет за одреден настан. Доколку настанот има ограничување (на пр. 18+), тригерот ја пресметува возраста на корисникот и го блокира внесувањето во табелата за нарачки ако условот не е исполнет.

CREATE OR REPLACE FUNCTION trg_check_user_age()
RETURNS TRIGGER AS $$
DECLARE
    v_user_age INT;
    v_min_age INT;
BEGIN
    SELECT DATE_PART('year', AGE(date_of_birth)) INTO v_user_age
    FROM "User" WHERE user_id = NEW.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 is too young.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_age_before_purchase
BEFORE INSERT ON "Ticket_Purchase"
FOR EACH ROW EXECUTE FUNCTION trg_check_user_age();

trg_check_venue_schedule

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

CREATE OR REPLACE FUNCTION trg_check_venue_schedule()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM "Event_Happening"
        WHERE venue_id = NEW.venue_id
          AND event_time = NEW.event_time
          AND event_happening_id <> NEW.event_happening_id
    ) THEN
        RAISE EXCEPTION 'Venue is already occupied at this time.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

trg_check_performer_availability

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

CREATE OR REPLACE FUNCTION trg_check_performer_availability()
RETURNS TRIGGER AS $$
DECLARE
    v_new_event_time TIMESTAMP;
BEGIN
    SELECT event_time INTO v_new_event_time 
    FROM "Event_Happening" 
    WHERE event_happening_id = NEW.event_happening_id;

    IF EXISTS (
        SELECT 1 
        FROM "Event_Happening_Performer" ehp
        JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
        WHERE ehp.performer_id = NEW.performer_id
          AND eh.event_time = v_new_event_time
          AND ehp.event_happening_id <> NEW.event_happening_id
    ) THEN
        RAISE EXCEPTION 'Performer is already booked for another event at this time.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_performer_before_assign
BEFORE INSERT OR UPDATE ON "Event_Happening_Performer"
FOR EACH ROW EXECUTE FUNCTION trg_check_performer_availability();