Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/20/26 16:32:39 (5 days ago)

Author:

231055

Comment:

--

DatabaseProgramming

@@ +1 @@
+== Функции ==
+{{{#!sql
+-- ============================================================
+--  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);
+}}}
+
+== Процедури ==
+{{{#!sql
+-- ============================================================
+--  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ригери ==
+{{{#!sql
+-- ============================================================
+--  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;
+}}}
+
+
+