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

== Функции

=== `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;

}}}

=== `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;

}}}

== Тригери

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

}}}