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

Функции

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;

    -- Примена на 15% пенали (се враќаат 85% од цената)
    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;

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;
    v_seat_record RECORD;
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;

    FOR v_seat_record IN
        SELECT seat_id FROM "Seat" s
        JOIN "Section" sec ON s.section_id = sec.section_id
        WHERE sec.venue_id = p_venue_id
    LOOP
        INSERT INTO "Ticket" (ticket_type, base_price, is_available, event_happening_id, seat_id)
        VALUES ('Standard', p_base_price, TRUE, v_happening_id, v_seat_record.seat_id);
    END LOOP;
    
    RAISE NOTICE 'Event scheduled and tickets generated.';
END;
$$ LANGUAGE plpgsql;

Тригери

trg_check_ticket_availability

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

CREATE OR REPLACE FUNCTION trg_check_ticket_availability()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT is_available FROM "Ticket" WHERE ticket_id = NEW.ticket_id) = FALSE THEN
        RAISE EXCEPTION 'Ticket is already sold or unavailable.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_ticket_availability_before_buy
BEFORE INSERT ON "Ticket_Purchase"
FOR EACH ROW EXECUTE FUNCTION trg_check_ticket_availability();

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

trg_validate_event_rating

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

CREATE OR REPLACE FUNCTION trg_validate_event_rating()
RETURNS TRIGGER 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 = NEW.user_id 
          AND t.event_happening_id = NEW.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;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_rating_eligibility_before_insert
BEFORE INSERT ON "Event_Happening_Rating"
FOR EACH ROW EXECUTE FUNCTION trg_validate_event_rating();