Функции

-- ============================================================
--  FUNCTION1: го зема возачот, дистанцата и попустот, ги наоѓа цените од неговото возило во базата и враќа финалната цена на вожњата.
-- ============================================================
CREATE OR REPLACE FUNCTION fn_calculate_ride_price(
    p_driver_id       INT,
    p_distance_km     NUMERIC,
    p_discount_pct    NUMERIC DEFAULT 0
)
RETURNS NUMERIC AS $$
DECLARE
    v_base_price    NUMERIC;
    v_price_per_km  NUMERIC;
    v_final_price   NUMERIC;
BEGIN
    -- Земи ги цените според типот на возилото на возачот
    -- Ако има 2 возила → го земаме поскапото (MAX)
    SELECT 
        MAX(pr.base_price),
        MAX(pr.price_per_km)
    INTO 
        v_base_price,
        v_price_per_km
    FROM Drivers_Vehicle_ownership dvo
    JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id
    JOIN Vehicle_types     vt ON vo.vehicle_type_id       = vt.vehicle_type_id
    JOIN Pricing_rules     pr ON vt.vehicle_type_id       = pr.vehicle_type_id
    WHERE dvo.driver_id = p_driver_id;

    -- Ако возачот нема возило → fallback на Economy цена
    IF v_base_price IS NULL THEN
        v_base_price   := 80.00;
        v_price_per_km := 40.00;
    END IF;

    -- Пресметај финална цена со попуст
    v_final_price := ROUND(
        (v_base_price + p_distance_km * v_price_per_km) 
        * (1.0 - p_discount_pct / 100.0),
        2
    );

    RETURN v_final_price;
END;
$$ LANGUAGE plpgsql;

                      
--(driver_id,distance_km,discount%)                               
-- Economy возач, 5km, без попуст → (80 + 5*40) * 1.0 = 280.00
SELECT fn_calculate_ride_price(1, 5, 0);

-- Luxury возач, 10km, 10% попуст → (100 + 10*60) * 0.9 = 630.00
SELECT fn_calculate_ride_price(7, 10, 10);



-- ============================================================
--  FUNCTION2: Наоѓа најблискиот слободен возач до pickup локацијата користејќи ги координатите (latitude/longitude). 
-- ============================================================

CREATE OR REPLACE FUNCTION fn_find_nearest_driver(
    p_pickup_location_id INT
)
RETURNS TABLE(
    driver_id           INT,
    driver_name         VARCHAR,
    driver_phone        VARCHAR,
    driver_rating       NUMERIC,
    vehicle_plate       VARCHAR,
    vehicle_class       VARCHAR,
    distance_meters     NUMERIC
) AS $$
DECLARE
    v_pickup_lat  NUMERIC;
    v_pickup_lng  NUMERIC;
BEGIN
    -- Земи ги координатите на pickup локацијата
    SELECT latitude, longitude
    INTO v_pickup_lat, v_pickup_lng
    FROM Locations
    WHERE location_id = p_pickup_location_id;

    IF v_pickup_lat IS NULL THEN
        RAISE EXCEPTION 'Локацијата со ID % не постои', p_pickup_location_id;
    END IF;

    RETURN QUERY
    SELECT
        d.driver_id,
        d.name                                          AS driver_name,
        d.phone                                         AS driver_phone,
        d.rating                                        AS driver_rating,
        vo.plate                                        AS vehicle_plate,
        vt.description                                  AS vehicle_class,
        -- формула за растојание во метри
        ROUND(
            6371000 * 2 * ASIN(SQRT(
                POWER(SIN(RADIANS(l.latitude  - v_pickup_lat) / 2), 2) +
                COS(RADIANS(v_pickup_lat)) *
                COS(RADIANS(l.latitude))  *
                POWER(SIN(RADIANS(l.longitude - v_pickup_lng) / 2), 2)
            ))
        ::NUMERIC, 2)                                   AS distance_meters
    FROM Active_drivers ad
    JOIN Drivers d        ON ad.driver_id             = d.driver_id
    JOIN Locations l      ON ad.current_location_id   = l.location_id
    JOIN Vehicle_ownership vo ON ad.vehicle_ownership_id = vo.vehicle_ownership_id
    JOIN Vehicle_types vt ON vo.vehicle_type_id       = vt.vehicle_type_id
    -- Само слободни и активни возачи
    WHERE ad.is_available = true
      AND d.status_id = (
          SELECT status_id FROM Status 
          WHERE status_tip = 'driver' AND status_text = 'active' LIMIT 1
      )
    ORDER BY distance_meters ASC
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- Најди најблизок возач до локација 1
SELECT * FROM fn_find_nearest_driver(1);
-- Најди најблизок возач до локација 500
SELECT * FROM fn_find_nearest_driver(500);

Процедури

-- ============================================================
--  PROCEDURE 1: sp_request_ride
--  Креира нова вожња: наоѓа најблизок слободен возач,
--  пресметува цена, вметнува во Rides и праќа нотификација.
-- ============================================================
CREATE OR REPLACE PROCEDURE sp_request_ride(
    p_user_id            INT,
    p_pickup_location_id INT,
    p_dropoff_location_id INT,
    p_distance_km        NUMERIC,
    OUT p_ride_id        INT
)
LANGUAGE plpgsql AS $$
DECLARE
    v_driver_id          INT;
    v_vehicle_ownership_id INT;
    v_discount_pct       NUMERIC := 0;
    v_final_price        NUMERIC;
    v_status_requested   INT;
    v_notif_status       INT;
    v_message_id         INT;
BEGIN
    -- 1. Земи го статусот 'requested' за вожњата
    SELECT status_id INTO v_status_requested
    FROM Status
    WHERE status_tip = 'ride' AND status_text = 'requested'
    LIMIT 1;

    -- 2. Провери дали корисникот постои и е активен
    IF NOT EXISTS (
        SELECT 1 FROM Users u
        JOIN Status s ON u.status_id = s.status_id
        WHERE u.user_id = p_user_id AND s.status_text = 'active'
    ) THEN
        RAISE EXCEPTION 'Корисникот со ID % не постои или не е активен', p_user_id;
    END IF;

    -- 3. Провери дали локациите постојат
    IF NOT EXISTS (SELECT 1 FROM Locations WHERE location_id = p_pickup_location_id) THEN
        RAISE EXCEPTION 'Pickup локацијата со ID % не постои', p_pickup_location_id;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM Locations WHERE location_id = p_dropoff_location_id) THEN
        RAISE EXCEPTION 'Dropoff локацијата со ID % не постои', p_dropoff_location_id;
    END IF;

    -- 4. Провери дали дистанцата е валидна
    IF p_distance_km <= 0 THEN
        RAISE EXCEPTION 'Дистанцата мора да биде поголема од 0';
    END IF;

    -- 5. Пресметај попуст: >= 9km → 10%, инаку 0%
    IF p_distance_km >= 9 THEN
        v_discount_pct := 10.0;
    END IF;

    -- 6. Најди најблизок слободен возач (ја повикуваме функцијата fn_find_nearest_driver)
    SELECT driver_id INTO v_driver_id
    FROM fn_find_nearest_driver(p_pickup_location_id);

    IF v_driver_id IS NULL THEN
        -- Ако нема слободен возач → порака 9 (Нема достапни возачи)
        SELECT message_id INTO v_message_id
        FROM Messages WHERE message_text LIKE 'Nemate dostapni%' LIMIT 1;

        SELECT status_id INTO v_notif_status
        FROM Status WHERE status_tip = 'ride' AND status_text = 'cancelled' LIMIT 1;

        -- Вметнуваме "dummy" нотификација без ride_id (не можеме бидејќи ride_id е NOT NULL)
        -- Затоа само фрламе exception
        RAISE EXCEPTION 'Нема достапни возачи во моментот. Обидете се повторно подоцна.';
    END IF;

    -- 7. Земи го vehicle_ownership_id на тој возач (примарното возило)
    SELECT Vehicle_ownership_id INTO v_vehicle_ownership_id
    FROM Drivers_Vehicle_ownership
    WHERE driver_id = v_driver_id
    LIMIT 1;

    -- 8. Пресметај финална цена (ја повикуваме функцијата fn_calculate_ride_price)
    v_final_price := fn_calculate_ride_price(v_driver_id, p_distance_km, v_discount_pct);

    -- 9. Вметни ја вожњата во Rides
    INSERT INTO Rides (
        user_id,
        driver_id,
        vehicle_ownership_id,
        pickup_location_id,
        dropoff_location_id,
        status_id,
        request_time,
        pickup_time,
        start_time,
        end_time,
        distance_km,
        final_price,
        discount_percentage
    ) VALUES (
        p_user_id,
        v_driver_id,
        v_vehicle_ownership_id,
        p_pickup_location_id,
        p_dropoff_location_id,
        v_status_requested,
        NOW(),
        NOW() + interval '5 minutes',   -- очекувано pickup
        NOW() + interval '8 minutes',   -- очекуван почеток
        NOW() + interval '20 minutes',  -- очекуван крај
        p_distance_km,
        v_final_price,
        v_discount_pct
    )
    RETURNING ride_id INTO p_ride_id;

    -- 10. Прати нотификација: "Барањето е успешно испратено"
    SELECT message_id INTO v_message_id
    FROM Messages WHERE message_text LIKE 'Vaseto baranje e uspesno%' LIMIT 1;

    SELECT status_id INTO v_notif_status
    FROM Status WHERE status_tip = 'ride' AND status_text = 'completed' LIMIT 1;

    INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
    VALUES (v_message_id, NOW(), p_user_id, p_ride_id, v_notif_status);

    RAISE NOTICE 'Вожњата е успешно креирана: ride_id=%, driver_id=%, цена=% MKD',
        p_ride_id, v_driver_id, v_final_price;

END;
$$;

-- Тест:
DO $$
DECLARE
    v_ride_id INT;
BEGIN
    CALL sp_request_ride(
        p_user_id             => 1000,
        p_pickup_location_id  => 1,
        p_dropoff_location_id => 500,
        p_distance_km         => 4.5,
        p_ride_id             => v_ride_id
    );
    RAISE NOTICE 'Новата вожња има ID: %', v_ride_id;
END $$;



-- ============================================================
--  PROCEDURE 2: sp_complete_ride
--  Ја завршува вожњата: го менува статусот на 'completed',
--  вметнува плаќање и ажурира активноста на возачот.
-- ============================================================
CREATE OR REPLACE PROCEDURE sp_complete_ride(
    p_ride_id         INT,
    p_payment_method  VARCHAR  -- 'CASH' или 'CARD'
)
LANGUAGE plpgsql AS $$
DECLARE
    v_ride              Rides%ROWTYPE;
    v_status_completed  INT;
    v_status_in_prog    INT;
    v_status_accepted   INT;
    v_pay_method_id     INT;
    v_pay_status_id     INT;
    v_notif_status      INT;
    v_message_id        INT;
BEGIN
    -- 1. Земи ги податоците за вожњата со LOCK за да спречиме race condition
    SELECT * INTO v_ride
    FROM Rides
    WHERE ride_id = p_ride_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Вожњата со ID % не постои', p_ride_id;
    END IF;

    -- 2. Земи ги потребните статус ID-а
    SELECT status_id INTO v_status_completed
    FROM Status WHERE status_tip = 'ride' AND status_text = 'completed' LIMIT 1;

    SELECT status_id INTO v_status_in_prog
    FROM Status WHERE status_tip = 'ride' AND status_text = 'in_progress' LIMIT 1;

    SELECT status_id INTO v_status_accepted
    FROM Status WHERE status_tip = 'ride' AND status_text = 'accepted' LIMIT 1;

    -- 3. Провери дека вожњата е во дозволена состојба за да се заврши
    IF v_ride.status_id NOT IN (v_status_in_prog, v_status_accepted) THEN
        RAISE EXCEPTION 'Вожњата со ID % не може да се заврши — тековен статус не е in_progress или accepted', p_ride_id;
    END IF;

    -- 4. Провери дали методот на плаќање е валиден
    SELECT payment_method_id INTO v_pay_method_id
    FROM Payment_methods
    WHERE payment_tip = UPPER(p_payment_method);

    IF v_pay_method_id IS NULL THEN
        RAISE EXCEPTION 'Невалиден метод на плаќање: %. Користете CASH или CARD', p_payment_method;
    END IF;

    -- 5. Ажурирај го статусот на вожњата на 'completed' и end_time на NOW()
    --    (тригерот trg_notify_on_ride_status_change автоматски ќе прати нотификација)
    UPDATE Rides
    SET status_id = v_status_completed,
        end_time  = NOW()
    WHERE ride_id = p_ride_id;

    -- 6. Вметни плаќање
    SELECT status_id INTO v_pay_status_id
    FROM Status WHERE status_tip = 'payment' AND status_text = 'completed' LIMIT 1;

    INSERT INTO Payments (amount, payment_method_id, payment_time, ride_id, status_id)
    VALUES (v_ride.final_price, v_pay_method_id, NOW(), p_ride_id, v_pay_status_id);

    -- 7. Ослободи го возачот → is_available = true
    UPDATE Active_drivers
    SET is_available = true
    WHERE driver_id = v_ride.driver_id;

    RAISE NOTICE 'Вожњата % е завршена. Платено % MKD со %.',
        p_ride_id, v_ride.final_price, UPPER(p_payment_method);

END;
$$;

-- Тест:
-- Прво најди вожња со статус in_progress или accepted
SELECT ride_id, status_id FROM Rides
WHERE status_id IN (
    SELECT status_id FROM Status
    WHERE status_tip='ride' AND status_text IN ('in_progress','accepted')
)
LIMIT 3;

-- Потоа заврши ја
CALL sp_complete_ride(p_ride_id => 3000021, p_payment_method => 'CARD');

-- Провери резултат
SELECT ride_id, status_id, end_time FROM Rides WHERE ride_id = 3000021;
SELECT * FROM Payments WHERE ride_id = 3000021 ORDER BY payment_id DESC LIMIT 1;

Tригери

-- ============================================================
--  TRIGGER1: автоматски го ажурира rating на возачот во Drivers табелата секој пат кога некој ќе додаде нов рејтинг во Ratings табелата.
-- ============================================================
-- Прво правиме функција која тригерот ја повикува
CREATE OR REPLACE FUNCTION fn_update_driver_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE Drivers
    SET rating = (
        SELECT ROUND(AVG(score)::NUMERIC, 2)
        FROM Ratings
        WHERE driver_id = NEW.driver_id
    )
    WHERE driver_id = NEW.driver_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Потоа го правиме тригерот
CREATE OR REPLACE TRIGGER trg_update_driver_rating
AFTER INSERT ON Ratings
FOR EACH ROW
EXECUTE FUNCTION fn_update_driver_rating();

-- Погледни го рејтингот пред (4.77)
SELECT driver_id, rating FROM Drivers WHERE driver_id = 40;

-- Додади нов рејтинг
INSERT INTO Ratings (score, comment, created_at, ride_id, user_id, driver_id)
VALUES (5, 'Odlichen vozac!', NOW(), 6675760, 25761, 40);

-- Погледни го рејтингот после → треба автоматски да се смени
SELECT driver_id, rating FROM Drivers WHERE driver_id = 40;

-- ============================================================
--  TRIGGER2:автоматски вметнува нотификација во Notifications кога се менува статусот на вожњата.
-- ============================================================
CREATE OR REPLACE FUNCTION fn_notify_on_ride_status_change()
RETURNS TRIGGER AS $$
DECLARE
    v_message_id    INT;
    v_status_text   VARCHAR;
    v_notif_status  INT;
BEGIN
    -- Земи го текстот на новиот статус
    SELECT status_text INTO v_status_text
    FROM Status WHERE status_id = NEW.status_id;

    -- Одреди која порака да се прати според новиот статус
    v_message_id := CASE v_status_text
        WHEN 'accepted'    THEN (SELECT message_id FROM Messages WHERE message_text LIKE 'Vozacot go prifati%'       LIMIT 1)
        WHEN 'in_progress' THEN (SELECT message_id FROM Messages WHERE message_text LIKE 'Vozacot pristigna%'        LIMIT 1)
        WHEN 'completed'   THEN (SELECT message_id FROM Messages WHERE message_text LIKE 'Plakjanjeto e uspesno%'    LIMIT 1)
        WHEN 'cancelled'   THEN (SELECT message_id FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano%'     LIMIT 1)
        ELSE NULL
    END;

    -- Ако нема порака за овој статус → не прави ништо
    IF v_message_id IS NULL THEN
        RETURN NEW;
    END IF;

    -- Статус на нотификацијата
    SELECT status_id INTO v_notif_status
    FROM Status WHERE status_tip = 'ride' AND status_text = 'completed' LIMIT 1;

    -- Вметни нотификација
    INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
    VALUES (v_message_id, NOW(), NEW.user_id, NEW.ride_id, v_notif_status);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_notify_on_ride_status_change
AFTER UPDATE OF status_id ON Rides
FOR EACH ROW
WHEN (OLD.status_id IS DISTINCT FROM NEW.status_id)
EXECUTE FUNCTION fn_notify_on_ride_status_change();

SELECT * FROM Notifications WHERE ride_id = 12907878;
-- Смени статус на вожњата
UPDATE Rides SET status_id = (
    SELECT status_id FROM Status 
    WHERE status_tip='ride' AND status_text='accepted' LIMIT 1
) WHERE ride_id = 12907878;

-- Провери дали се додала нотификација автоматски
SELECT * FROM Notifications WHERE ride_id = 12907878;

--===============================================
--za da najdeme nekoj ride ss cancelled status
--===============================================
SELECT * FROM Notifications;
-- Najdi ride koj ima status cancelled
SELECT ride_id, status_id FROM Rides 
WHERE status_id = (
    SELECT status_id FROM Status 
    WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1
)
LIMIT 5;