= Функции, процедури и тригери =

== Процедури ==

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

 [attachment:prodecures.sql prodecures.sql]

=== Процедура 1 : pr_sell_ticket

{{{
#!sql
CREATE OR REPLACE PROCEDURE pr_sell_ticket(
    IN p_passenger_embg CHAR(13),
    IN p_passenger_id INT4,
    IN p_trip_id INT4,
    IN p_start_station_id INT4,
    IN p_end_station_id INT4,
    IN p_seat_number INT4,
    IN p_carriage_number INT4,
    IN p_price NUMERIC(2, 0),
    IN p_payment_method VARCHAR(20),
    OUT o_ticket_id INT4,
    OUT o_payment_id INT4,
    IN p_reservation_id INT4 DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_passenger_exists INT;
    v_trip_exists INT;
    v_station_start_exists INT;
    v_station_end_exists INT;
    v_seat_taken INT;
BEGIN
    SELECT COUNT(*) INTO v_passenger_exists
    FROM Passenger
    WHERE PersonEMBG = p_passenger_embg AND passenger_id = p_passenger_id;

    IF v_passenger_exists = 0 THEN
        RAISE EXCEPTION 'Passenger with EMBG % and ID % does not exist.', p_passenger_embg, p_passenger_id;
    END IF;

    SELECT COUNT(*) INTO v_trip_exists FROM "Train Trip" WHERE trip_id = p_trip_id;
    IF v_trip_exists = 0 THEN
        RAISE EXCEPTION 'Train Trip ID % does not exist.', p_trip_id;
    END IF;

    SELECT COUNT(*) INTO v_station_start_exists FROM Station WHERE station_id = p_start_station_id;
    SELECT COUNT(*) INTO v_station_end_exists FROM Station WHERE station_id = p_end_station_id;
    IF v_station_start_exists = 0 OR v_station_end_exists = 0 THEN
        RAISE EXCEPTION 'Invalid origin or destination station provided.';
    END IF;

    SELECT COUNT(*) INTO v_seat_taken
    FROM Ticket
    WHERE "Train Triptrip_id" = p_trip_id
      AND carriage_number = p_carriage_number
      AND seat_number = p_seat_number
      AND ticket_status = 'Active';

    IF v_seat_taken > 0 THEN
        RAISE EXCEPTION 'Seat % in Carriage % is already booked for Trip %.', p_seat_number, p_carriage_number, p_trip_id;
    END IF;

    IF p_reservation_id IS NOT NULL THEN
        UPDATE Reservation
        SET status = 'Completed'
        WHERE reservation_id = p_reservation_id;
    END IF;

    INSERT INTO Payment (
        payment_method,
        amount,
        transaction_date,
        Reservationreservation_id,
        Passengerpassenger_id,
        PassengerPersonEMBG2
    )
    VALUES (
        p_payment_method,
        p_price,
        CURRENT_DATE,
        p_reservation_id,
        p_passenger_id,
        p_passenger_embg
    )
    RETURNING payment_id INTO o_payment_id;

    INSERT INTO Ticket (
        seat_number,
        carriage_number,
        price,
        ticket_status,
        Paymentpayment_id,
        "Train Triptrip_id",
        Stationstation_id,
        Stationstation_id2
    )
    VALUES (
        p_seat_number,
        p_carriage_number,
        p_price,
        'Active',
        o_payment_id,
        p_trip_id,
        p_start_station_id,
        p_end_station_id
    )
    RETURNING ticket_id INTO o_ticket_id;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transaction rolled back due to error: %', SQLERRM;
        RAISE;
END;
$$;


DO $$
DECLARE
    v_ticket_output INT4;
    v_payment_output INT4;
BEGIN
    CALL pr_sell_ticket(
        p_passenger_embg   => '2510985918703',
        p_passenger_id     => 290,
        p_trip_id          => 4,
        p_start_station_id => 10,
        p_end_station_id   => 14,
        p_seat_number      => 22,
        p_carriage_number  => 2,
        p_price            => 45,
        p_payment_method   => 'Apple Pay',
        o_ticket_id        => v_ticket_output,
        o_payment_id       => v_payment_output,
        p_reservation_id   => NULL
    );

    RAISE NOTICE 'Sale Complete! Ticket ID: %, Payment ID: %', v_ticket_output, v_payment_output;
END $$;

select * from ticket where ticket_id='23989062';
}}}
Оваа процедура автоматски продава билет во системот за возови. Прво проверува дали постојат патникот, патувањето и станиците, и дали седиштето е слободно. Потоа креира плаќање и билет и ако постои резервација ја означува како "Completed". Се користи за автоматизирана и сигурна продажба на билети без грешки и дупли резервации.

=== Процедура 2 : cancel_reservation

{{{
#!sql
CREATE OR REPLACE PROCEDURE cancel_reservation(p_reservation_id INT)
AS $$
BEGIN
    UPDATE Reservation
    SET status = 'Cancelled'
    WHERE reservation_id = p_reservation_id;

    UPDATE Ticket
    SET ticket_status = 'Cancelled'
    WHERE Paymentpayment_id IN (
        SELECT payment_id
        FROM Payment
        WHERE Reservationreservation_id = p_reservation_id
    );
    RAISE NOTICE 'Reservation % has been canceled successfully.', p_reservation_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM Reservation where reservation_id = 17;

SELECT * FROM Ticket
WHERE Paymentpayment_id IN (
    SELECT payment_id FROM Payment WHERE Reservationreservation_id = 17
);

CALL cancel_reservation(17);

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

=== Процедура 3 : apply_seasonal_discount

[[Image("Screenshot 2026-05-20 161953.png", 300px)]]

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

=== Процедура 4 : update_trip_delay

[[Image("Screenshot 2026-05-20 161643.png", 300px)]]

Оваа процедура го ажурира доцнењето и статусот на едно патување. Кога ќе се повика, го поставува бројот на минути на доцнење и автоматски го менува статусот во "Delayed" ако има доцнење или "On Time" ако нема. Се користи за брзо и точно ажурирање на состојбата на возовите во реално време.

=== Процедура 5 : reassign_passengers_to_new_trip

[[Image("Screenshot 2026-05-20 162556.png", 300px)]]

Оваа процедура ги префрла сите патници од едно откажано патување на ново патување. Кога ќе се повика, ги ажурира билетите да покажуваат нов "Train Trip", им го менува статусот во "Reassigned", а старото патување го означува како "Cancelled". Се користи за брзо пренасочување на патници при откажани или променети возни линии.

=== Процедура 6 : transfer_employee

[[Image("Screenshot 2026-05-20 163229.png", 300px)]]

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

=== Процедура 7 : assign_platform_to_trip

[[Image("Screenshot 2026-05-20 163515.png", 300px)]]

Оваа процедура доделува платформа и станица на одредено патување. Кога ќе се повика, го ажурира Train Trip со нова платформа и станица, а ако патувањето не постои, фрла грешка. Се користи за прецизно распоредување на возовите на соодветни платформи во станиците.

=== Процедура 8 : change_trip_platform

[[Image("Screenshot 2026-05-20 164529.png", 300px)]]

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

== Тригери ==

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

 [attachment:triggers.sql triggers.sql]

=== Тригер 1 : auto_set_reservation_status 

[[Image("Screenshot 2026-05-20 142512.png", 300px)]]

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

=== Тригер 2 : prevent_duplicate_seat_booking 

[[Image("Screenshot 2026-05-20 143034.png", 300px)]]

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

=== Тригер 3 : update_trip_status_based_on_delay

[[Image("Screenshot 2026-05-20 143448.png", 300px)]]

Овој trigger автоматски го ажурира статусот на патувањето според доцнењето на возот. Ако бројот на минути за доцнење е поголем од 0, статусот се поставува на "Delayed", а во спротивно на "On Time". Се користи за автоматско следење и прикажување на точниот статус на возовите.

=== Тригер 4 : auto_set_transaction_date

[[Image("Screenshot 2026-05-20 144202.png", 300px)]]

Овој trigger автоматски ја поставува датата на трансакцијата ако не е внесена при креирање на плаќање. Ако transaction_date е NULL, системот ја става тековната дата. Се користи за да се осигураме дека секое плаќање има точен датум и за автоматско и конзистентно водење на финансиски записи.

=== Тригер 5 : prevent_employee_trip_overlap

[[Image("Screenshot 2026-05-20 144545.png", 300px)]]

Овој trigger спречува еден вработен да биде доделен на две патувања во исто време. Пред внесување нов запис во "Train Trip", системот проверува дали веќе постои ист вработен со исто време на поаѓање и ако да, фрла грешка. Се користи за да се избегнат конфликти во распоредот и за правилно распределување на вработените.

=== Тригер 6 : check_and_mark_overbooked

[[Image("Screenshot 2026-05-20 145123.png", 300px)]]

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

=== Тригер 7 : prevent_station_delete_active_trip

[[Image("Screenshot 2026-05-20 145522.png", 300px)]]

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

== Функции ==

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

 [attachment:functions.sql functions.sql]

=== Функција 1 : free_seats_trip

[[Image("Screenshot 2026-05-20 165507.png", 300px)]]

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


=== Функција 1 : free_seats_trip

[[Image("Screenshot 2026-05-20 165507.png", 300px)]]

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

=== Функција 2 : get_route_stations

[[Image("Screenshot 2026-05-20 170403.png", 300px)]]

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


=== Функција 3 : get_next_departing_trains

[[Image("Screenshot 2026-05-20 170830.png", 300px)]]

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


=== Функција 4 : get_passenger_total_spending

[[Image("Screenshot 2026-05-20 171159.png", 300px)]]

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

=== Функција 5 : get_trip_revenue_efficiency

[[Image("Screenshot 2026-05-20 171652.png", 300px)]]

[[Image("Screenshot 2026-05-20 171658.png", 300px)]]

Оваа функција ја анализира ефикасноста на едно патување со воз врз основа на пополнетост и приход. Го пресметува процентот на искористеност на капацитетот и просечниот приход по седиште, па враќа оценка како „High Demand“, „Optimal“ или „Low Efficiency“ заедно со финансискиот резултат. Cе користи за анализа на профитабилноста и перформансите на завршените патувања.

=== Функција 6 : get_passenger_name_by_id

[[Image("Screenshot 2026-05-20 172105.png", 300px)]]

Оваа функција го враќа целосното име на патник според неговиот passenger_id. Ги спојува името и презимето од табелата Person. Ако патникот не постои, враќа порака „Passenger not found“. Cе користи за брзо добивање на информации за патници во извештаи и интерфејсот.

=== Функција 6 : get_most_profitable_route

[[Image("Screenshot 2026-05-20 172105.png", 300px)]]

Оваа функција ја наоѓа најпрофитабилната рута во системот. Го пресметува вкупниот приход од билети за секоја рута и ја враќа онаа со најголем приход, заедно со сумата. Во проектот се користи за анализа на бизнис перформанси и идентификација на најисплатливи линии.