= Напредни бази на податоци =
= Фаза 5 — Функции, процедури и тригери =
== Проект: DriveNet ==

Александар Милошевски 231138
Исидора Кузмановска 231052
Андон Михајлов 231016

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

----

= ФУНКЦИИ =

=== 1. calculate_booking_fare ===

==== Опис:

Ја пресметува вкупната цена за одредена резервација врз основа на сегментите кои патникот ги поминува и бројот на патници на секој сегмент. Цената се дели рамномерно меѓу сите патници на секој сегмент — доколку на еден сегмент патуваат 3 патници, секој плаќа 1/3 од цената на тој сегмент.

==== Сигнатура:
{{{
calculate_booking_fare(p_booking_id INT) RETURNS DECIMAL(10,2)
}}}

==== Влезни параметри:

|| **Параметар** || **Тип** || **Опис** ||
|| `p_booking_id` || INT || ID на резервацијата ||

==== Логика:

 1. Го наоѓа `ride_id` и `price_per_km` преку `bookings JOIN rides`
 2. За секој сегмент во `passenger_segments` каде `is_present = TRUE` ја пресметува цената: `(distance_km × price_per_km) / passengers_on_segment`
 3. Ги сумира сите сегменти и го враќа вкупниот износ

==== Исклучоци:

 * `RAISE EXCEPTION` — доколку `booking_id` не постои

==== Имплементација:
{{{
CREATE OR REPLACE FUNCTION calculate_booking_fare(p_booking_id INT)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total        DECIMAL(10,2) := 0;
    v_ride_id      INT;
    v_price_per_km DECIMAL(8,2);
    rec            RECORD;
BEGIN
    SELECT b.ride_id, r.price_per_km
    INTO v_ride_id, v_price_per_km
    FROM bookings b
    JOIN rides r ON r.id = b.ride_id
    WHERE b.id = p_booking_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Booking % not found', p_booking_id;
    END IF;

    FOR rec IN
        SELECT rs.distance_km, ps.passengers_on_segment
        FROM passenger_segments ps
        JOIN route_segments rs ON rs.id = ps.segment_id
        WHERE ps.booking_id = p_booking_id
          AND ps.is_present = TRUE
    LOOP
        v_total := v_total + ROUND(
            (rec.distance_km * v_price_per_km) / rec.passengers_on_segment,
            2
        );
    END LOOP;

    RETURN v_total;
END;
$$;
}}}

==== Пример за употреба:
{{{
SELECT calculate_booking_fare(1);
}}}

----

=== 2. is_driver_available ===

==== Опис:

Проверува дали возачот веќе има закажано возење кое временски се преклопува со бараниот термин. Се користи пред креирање на ново возење за да се спречат конфликти во распоредот. Буферот е ±3 часа околу бараното време на поаѓање.

==== Сигнатура:
{{{
is_driver_available(p_driver_id INT, p_departure TIMESTAMP) RETURNS BOOLEAN
}}}

==== Влезни параметри:

|| **Параметар** || **Тип** || **Опис** ||
|| `p_driver_id` || INT || ID на возачот ||
|| `p_departure` || TIMESTAMP || Бараното време на поаѓање ||

==== Логика:

 1. Брои колку возења со статус `scheduled` или `in_progress` има возачот во прозорецот `[departure - 3h, departure + 3h]`
 2. Доколку бројот е 0 враќа `TRUE` (слободен), инаку враќа `FALSE` (зафатен)

==== Имплементација:
{{{
CREATE OR REPLACE FUNCTION is_driver_available(
    p_driver_id INT,
    p_departure TIMESTAMP
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM rides
    WHERE driver_id = p_driver_id
      AND status IN ('scheduled', 'in_progress')
      AND departure_time BETWEEN (p_departure - INTERVAL '3 hours')
                             AND (p_departure + INTERVAL '3 hours');

    RETURN v_count = 0;
END;
$$;
}}}

==== Пример за употреба:
{{{
-- Провери дали возачот е слободен на 15.06.2025 во 10:00
SELECT is_driver_available(1, '2025-06-15 10:00:00');
}}}

----

=== 3. get_available_seats ===

==== Опис:

Го враќа тековниот број на слободни места за одредено возење. Се користи пред резервација за да се потврди дека има место за нов патник.

==== Сигнатура:
{{{
get_available_seats(p_ride_id INT) RETURNS INT
}}}

==== Влезни параметри:

|| **Параметар** || **Тип** || **Опис** ||
|| `p_ride_id` || INT || ID на возењето ||

==== Логика:

 1. Го чита полето `seats_available` директно од табелата `rides`
 2. Доколку возењето не постои фрла исклучок

==== Исклучоци:

 * `RAISE EXCEPTION` — доколку `ride_id` не постои

==== Имплементација:
{{{
CREATE OR REPLACE FUNCTION get_available_seats(p_ride_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_seats INT;
BEGIN
    SELECT seats_available
    INTO v_seats
    FROM rides
    WHERE id = p_ride_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ride % not found', p_ride_id;
    END IF;

    RETURN v_seats;
END;
$$;
}}}

==== Пример за употреба:
{{{
SELECT get_available_seats(1);
}}}

----

= ПРОЦЕДУРИ =

=== 4. book_ride ===

==== Опис:

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

==== Сигнатура:
{{{
book_ride(p_passenger_id INT, p_ride_id INT,
          p_pickup_stop_id INT, p_dropoff_stop_id INT)
}}}

==== Влезни параметри:

|| **Параметар** || **Тип** || **Опис** ||
|| `p_passenger_id` || INT || ID на патникот ||
|| `p_ride_id` || INT || ID на возењето ||
|| `p_pickup_stop_id` || INT || ID на постојката за качување ||
|| `p_dropoff_stop_id` || INT || ID на постојката за слегување ||

==== Логика:

 1. Ги заклучува редовите со `FOR UPDATE` за да се спречат состојби на трка
 2. Проверува дали возењето постои и дали е со статус `scheduled`
 3. Проверува дали `seats_available > 0`
 4. Проверува дали патникот веќе има резервација за ова возење
 5. Вметнува нов ред во `bookings` со статус `confirmed`
 6. Го намалува `seats_available` за 1
 7. Вметнува запис во `booking_status_history`

==== Исклучоци:

 * `RAISE EXCEPTION` — возењето не постои
 * `RAISE EXCEPTION` — возењето не е со статус `scheduled`
 * `RAISE EXCEPTION` — нема слободни места
 * `RAISE EXCEPTION` — патникот веќе има резервација за ова возење

==== Имплементација:
{{{
CREATE OR REPLACE PROCEDURE book_ride(
    p_passenger_id    INT,
    p_ride_id         INT,
    p_pickup_stop_id  INT,
    p_dropoff_stop_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_booking_id  INT;
    v_seats       INT;
    v_ride_status VARCHAR(20);
BEGIN
    SELECT status, seats_available
    INTO v_ride_status, v_seats
    FROM rides
    WHERE id = p_ride_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ride % not found', p_ride_id;
    END IF;

    IF v_ride_status != 'scheduled' THEN
        RAISE EXCEPTION 'Ride % is not available for booking (status: %)',
            p_ride_id, v_ride_status;
    END IF;

    IF v_seats <= 0 THEN
        RAISE EXCEPTION 'No available seats for ride %', p_ride_id;
    END IF;

    IF EXISTS (
        SELECT 1 FROM bookings
        WHERE ride_id = p_ride_id
          AND passenger_id = p_passenger_id
    ) THEN
        RAISE EXCEPTION 'Passenger % already has a booking for ride %',
            p_passenger_id, p_ride_id;
    END IF;

    INSERT INTO bookings (
        ride_id, passenger_id,
        pickup_stop_id, dropoff_stop_id,
        status, created_at
    )
    VALUES (
        p_ride_id, p_passenger_id,
        p_pickup_stop_id, p_dropoff_stop_id,
        'confirmed', NOW()
    )
    RETURNING id INTO v_booking_id;

    UPDATE rides
    SET seats_available = seats_available - 1
    WHERE id = p_ride_id;

    INSERT INTO booking_status_history (booking_id, status, changed_at)
    VALUES (v_booking_id, 'confirmed', NOW());

    RAISE NOTICE 'Booking % created for passenger % on ride %',
        v_booking_id, p_passenger_id, p_ride_id;
END;
$$;
}}}

==== Пример за употреба:
{{{
CALL book_ride(1, 1, 1, 4);
}}}

----

=== 5. cancel_booking ===

==== Опис:

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

==== Сигнатура:
{{{
cancel_booking(p_booking_id INT, p_reason VARCHAR(300) DEFAULT NULL)
}}}

==== Влезни параметри:

|| **Параметар** || **Тип** || **Опис** ||
|| `p_booking_id` || INT || ID на резервацијата ||
|| `p_reason` || VARCHAR(300) || Причина за откажување (опционално) ||

==== Логика:

 1. Ги зема деталите за резервацијата и поврзаното возење со `FOR UPDATE`
 2. Проверува дали статусот е `pending` или `confirmed`
 3. Проверува дали до поаѓањето има повеќе од 1 час
 4. Го менува статусот на `cancelled`
 5. Го зголемува `seats_available` за 1
 6. Вметнува запис во `booking_status_history` со причината

==== Исклучоци:

 * `RAISE EXCEPTION` — резервацијата не постои
 * `RAISE EXCEPTION` — статусот не дозволува откажување
 * `RAISE EXCEPTION` — помалку од 1 час до поаѓање

==== Имплементација:
{{{
CREATE OR REPLACE PROCEDURE cancel_booking(
    p_booking_id INT,
    p_reason     VARCHAR(300) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ride_id   INT;
    v_status    VARCHAR(20);
    v_departure TIMESTAMP;
BEGIN
    SELECT b.ride_id, b.status, r.departure_time
    INTO v_ride_id, v_status, v_departure
    FROM bookings b
    JOIN rides r ON r.id = b.ride_id
    WHERE b.id = p_booking_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Booking % not found', p_booking_id;
    END IF;

    IF v_status NOT IN ('pending', 'confirmed') THEN
        RAISE EXCEPTION 'Cannot cancel booking % with status %',
            p_booking_id, v_status;
    END IF;

    IF v_departure <= NOW() + INTERVAL '1 hour' THEN
        RAISE EXCEPTION 'Cannot cancel booking % — less than 1 hour before departure',
            p_booking_id;
    END IF;

    UPDATE bookings
    SET status = 'cancelled'
    WHERE id = p_booking_id;

    UPDATE rides
    SET seats_available = seats_available + 1
    WHERE id = v_ride_id;

    INSERT INTO booking_status_history (booking_id, status, changed_at, reason)
    VALUES (p_booking_id, 'cancelled', NOW(), p_reason);

    RAISE NOTICE 'Booking % cancelled successfully', p_booking_id;
END;
$$;
}}}

==== Пример за употреба:
{{{
CALL cancel_booking(1, 'Патникот не може да патува');
}}}

----

=== 6. complete_ride ===

==== Опис:

Го завршува возењето и автоматски ги завршува сите активни резервации. За секоја резервација се пресметува и зачувува финалната тарифа преку функцијата `calculate_booking_fare`.

==== Сигнатура:
{{{
complete_ride(p_ride_id INT)
}}}

==== Влезни параметри:

|| **Параметар** || **Тип** || **Опис** ||
|| `p_ride_id` || INT || ID на возењето ||

==== Логика:

 1. Проверува дали возењето постои и дали е со статус `in_progress`
 2. Го менува статусот на возењето во `completed`
 3. За секоја резервација со статус `confirmed` или `picked_up`:
    * Го менува статусот во `completed`
    * Го поставува `dropoff_confirmed_at = NOW()`
    * Ја пресметува финалната тарифа преку `calculate_booking_fare`
    * Ја зачувува тарифата во `booking_final_fare` (INSERT или UPDATE)
    * Вметнува запис во `booking_status_history`

==== Исклучоци:

 * `RAISE EXCEPTION` — возењето не постои
 * `RAISE EXCEPTION` — возењето не е со статус `in_progress`

==== Имплементација:
{{{
CREATE OR REPLACE PROCEDURE complete_ride(p_ride_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR(20);
    rec      RECORD;
    v_fare   DECIMAL(10,2);
BEGIN
    SELECT status INTO v_status
    FROM rides
    WHERE id = p_ride_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ride % not found', p_ride_id;
    END IF;

    IF v_status != 'in_progress' THEN
        RAISE EXCEPTION 'Ride % cannot be completed (status: %)',
            p_ride_id, v_status;
    END IF;

    UPDATE rides
    SET status = 'completed'
    WHERE id = p_ride_id;

    FOR rec IN
        SELECT id FROM bookings
        WHERE ride_id = p_ride_id
          AND status IN ('confirmed', 'picked_up')
    LOOP
        UPDATE bookings
        SET status = 'completed',
            dropoff_confirmed_at = NOW()
        WHERE id = rec.id;

        v_fare := calculate_booking_fare(rec.id);

        INSERT INTO booking_final_fare (booking_id, total_amount, calculated_at)
        VALUES (rec.id, v_fare, NOW())
        ON CONFLICT (booking_id)
        DO UPDATE SET
            total_amount  = EXCLUDED.total_amount,
            calculated_at = EXCLUDED.calculated_at;

        INSERT INTO booking_status_history (booking_id, status, changed_at)
        VALUES (rec.id, 'completed', NOW());
    END LOOP;

    RAISE NOTICE 'Ride % completed successfully', p_ride_id;
END;
$$;
}}}

==== Пример за употреба:
{{{
CALL complete_ride(1);
}}}

----

= ТРИГЕРИ =

=== 7. trg_booking_status_history ===

==== Опис:

Автоматски запишува во табелата `booking_status_history` при секоја промена на статусот на резервација. Обезбедува целосна историја на промени без да мора апликацијата рачно да ги следи.

==== Тип: `AFTER UPDATE` на табела `bookings`

==== Логика:

 1. Се активира по секој `UPDATE` на табелата `bookings`
 2. Проверува дали `OLD.status IS DISTINCT FROM NEW.status`
 3. Доколку статусот се сменил, вметнува нов ред во `booking_status_history`

==== Имплементација:
{{{
CREATE OR REPLACE FUNCTION fn_booking_status_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO booking_status_history (booking_id, status, changed_at)
        VALUES (NEW.id, NEW.status, NOW());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_booking_status_history
AFTER UPDATE ON bookings
FOR EACH ROW
EXECUTE FUNCTION fn_booking_status_history();
}}}

==== Пример — активирање:
{{{
-- Овој UPDATE автоматски ќе вметне ред во booking_status_history
UPDATE bookings SET status = 'picked_up' WHERE id = 1;
}}}

----

=== 8. trg_ride_status_history ===

==== Опис:

Автоматски запишува во табелата `ride_status_history` при секоја промена на статусот на возење. Обезбедува целосна хронологија на состојбите на возењето.

==== Тип: `AFTER UPDATE` на табела `rides`

==== Логика:

 1. Се активира по секој `UPDATE` на табелата `rides`
 2. Проверува дали `OLD.status IS DISTINCT FROM NEW.status`
 3. Доколку статусот се сменил, вметнува нов ред во `ride_status_history`

==== Имплементација:
{{{
CREATE OR REPLACE FUNCTION fn_ride_status_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO ride_status_history (ride_id, status, changed_at)
        VALUES (NEW.id, NEW.status, NOW());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_ride_status_history
AFTER UPDATE ON rides
FOR EACH ROW
EXECUTE FUNCTION fn_ride_status_history();
}}}

==== Пример — активирање:
{{{
-- Овој UPDATE автоматски ќе вметне ред во ride_status_history
UPDATE rides SET status = 'in_progress' WHERE id = 1;
}}}

----

=== 9. trg_audit_log ===

==== Опис:

Автоматски запишува во табелата `audit_logs` за сите `INSERT`, `UPDATE` и `DELETE` операции на критичните табели: `rides`, `bookings` и `users`. Го чува и старото и новото состојба на редот во JSON формат.

==== Тип: `AFTER INSERT OR UPDATE OR DELETE` на табели `rides`, `bookings`, `users`

==== Логика:

 1. За `INSERT` — зачувува `new_data = row_to_json(NEW)`, `old_data = NULL`
 2. За `UPDATE` — зачувува `old_data = row_to_json(OLD)` и `new_data = row_to_json(NEW)`
 3. За `DELETE` — зачувува `old_data = row_to_json(OLD)`, `new_data = NULL`

==== Имплементација:
{{{
CREATE OR REPLACE FUNCTION fn_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (
            table_name, record_id, operation,
            old_data, new_data, changed_at
        )
        VALUES (
            TG_TABLE_NAME, NEW.id, 'INSERT',
            NULL, row_to_json(NEW)::TEXT, NOW()
        );
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (
            table_name, record_id, operation,
            old_data, new_data, changed_at
        )
        VALUES (
            TG_TABLE_NAME, NEW.id, 'UPDATE',
            row_to_json(OLD)::TEXT, row_to_json(NEW)::TEXT, NOW()
        );
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (
            table_name, record_id, operation,
            old_data, new_data, changed_at
        )
        VALUES (
            TG_TABLE_NAME, OLD.id, 'DELETE',
            row_to_json(OLD)::TEXT, NULL, NOW()
        );
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER trg_audit_log_rides
AFTER INSERT OR UPDATE OR DELETE ON rides
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_log_bookings
AFTER INSERT OR UPDATE OR DELETE ON bookings
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_log_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION fn_audit_log();
}}}

==== Пример — активирање:
{{{
-- Секој од овие автоматски ќе вметне ред во audit_logs
INSERT INTO rides (...) VALUES (...);
UPDATE bookings SET status = 'cancelled' WHERE id = 1;
DELETE FROM users WHERE id = 999;
}}}

----

= Резиме =

|| **#** || **Име** || **Тип** || **Опис** ||
|| 1 || `calculate_booking_fare` || Функција || Пресметува вкупна цена за резервација ||
|| 2 || `is_driver_available` || Функција || Проверува конфликт на термин за возач ||
|| 3 || `get_available_seats` || Функција || Враќа број на слободни места за возење ||
|| 4 || `book_ride` || Процедура || Целосен процес на резервација ||
|| 5 || `cancel_booking` || Процедура || Откажување на резервација со проверки ||
|| 6 || `complete_ride` || Процедура || Завршување на возење и пресметка на тарифи ||
|| 7 || `trg_booking_status_history` || Тригер || Автоматски лог на промени на статус на резервација ||
|| 8 || `trg_ride_status_history` || Тригер || Автоматски лог на промени на статус на возење ||
|| 9 || `trg_audit_log` || Тригер || Audit лог за rides, bookings и users ||