Procedures

create_parking_reservation

CREATE OR REPLACE PROCEDURE public.create_parking_reservation(
    IN p_user_id integer,
    IN p_vehicle_id integer,
    IN p_parking_id integer,
    IN p_reservation_status_id integer,
    IN p_start_time timestamp without time zone,
    IN p_end_time timestamp without time zone
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM app_user WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'User does not exist.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM vehicle
        WHERE vehicle_id = p_vehicle_id AND user_id = p_user_id
    ) THEN
        RAISE EXCEPTION 'Vehicle does not exist or does not belong to this user.';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM parking WHERE parking_id = p_parking_id) THEN
        RAISE EXCEPTION 'Parking does not exist.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM parking
        WHERE parking_id = p_parking_id AND supports_reservation = true
    ) THEN
        RAISE EXCEPTION 'Parking does not support reservations.';
    END IF;

    IF p_start_time >= p_end_time THEN
        RAISE EXCEPTION 'Start time must be before end time.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM parking_spot
        WHERE parking_id = p_parking_id AND status = 'free'
    ) THEN
        RAISE EXCEPTION 'No free parking spots available.';
    END IF;

    INSERT INTO reservation (
        user_id,
        vehicle_id,
        parking_id,
        reservation_status_id,
        start_time,
        end_time,
        reservation_code
    )
    VALUES (
        p_user_id,
        p_vehicle_id,
        p_parking_id,
        p_reservation_status_id,
        p_start_time,
        p_end_time,
        'RES-' || p_user_id || '-' || EXTRACT(EPOCH FROM NOW())::INT
    );

    UPDATE parking_spot
    SET status = 'reserved'
    WHERE parking_spot_id = (
        SELECT parking_spot_id
        FROM parking_spot
        WHERE parking_id = p_parking_id AND status = 'free'
        LIMIT 1
    );
END;
$procedure$;

Оваа процедура креира нова паркинг резервација само доколку се исполнети сите потребни услови, како постоење на корисник, возило, паркинг и слободно место. Ја имплементира бизнис логиката за управување со резервации и автоматски резервира слободно паркинг место.

cancel_reservation

CREATE OR REPLACE PROCEDURE public.cancel_reservation(
    IN p_reservation_id integer
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM reservation
        WHERE reservation_id = p_reservation_id
    ) THEN
        RAISE EXCEPTION 'Reservation does not exist.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM reservation
        WHERE reservation_id = p_reservation_id
          AND reservation_status = 'cancelled'
    ) THEN
        RAISE EXCEPTION 'Reservation is already cancelled.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM reservation
        WHERE reservation_id = p_reservation_id
          AND reservation_status = 'completed'
    ) THEN
        RAISE EXCEPTION 'Completed reservation cannot be cancelled.';
    END IF;

    UPDATE reservation
    SET reservation_status = 'cancelled'
    WHERE reservation_id = p_reservation_id;
END;
$procedure$;

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

complete_reservation

CREATE OR REPLACE PROCEDURE public.complete_reservation(
    IN p_reservation_id integer
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM reservation
        WHERE reservation_id = p_reservation_id
    ) THEN
        RAISE EXCEPTION 'Reservation does not exist.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM reservation
        WHERE reservation_id = p_reservation_id
          AND reservation_status = 'completed'
    ) THEN
        RAISE EXCEPTION 'Reservation is already completed.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM reservation
        WHERE reservation_id = p_reservation_id
          AND reservation_status = 'cancelled'
    ) THEN
        RAISE EXCEPTION 'Cancelled reservation cannot be completed.';
    END IF;

    UPDATE reservation
    SET reservation_status = 'completed'
    WHERE reservation_id = p_reservation_id;
END;
$procedure$;

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

end_parking_session

CREATE OR REPLACE PROCEDURE public.end_parking_session(
    IN p_session_id integer,
    IN p_session_status_id integer
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM parking_session WHERE session_id = p_session_id) THEN
        RAISE EXCEPTION 'Parking session does not exist.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM parking_session
        WHERE session_id = p_session_id AND end_time IS NOT NULL
    ) THEN
        RAISE EXCEPTION 'Parking session is already finished.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM session_status
        WHERE session_status_id = p_session_status_id
    ) THEN
        RAISE EXCEPTION 'Session status does not exist.';
    END IF;

    UPDATE parking_session
    SET end_time = NOW(),
        session_status_id = p_session_status_id
    WHERE session_id = p_session_id;
END;
$procedure$;

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

fill_total_amount

CREATE OR REPLACE PROCEDURE public.fill_total_amount(
    IN p_session_id integer
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM parking_session WHERE session_id = p_session_id) THEN
        RAISE EXCEPTION 'Parking session does not exist.';
    END IF;

    IF EXISTS (
        SELECT 1 FROM parking_session
        WHERE session_id = p_session_id AND end_time IS NULL
    ) THEN
        RAISE EXCEPTION 'Parking session is still active.';
    END IF;

    UPDATE parking_session
    SET total_amount = calculate_session_amount(p_session_id)
    WHERE session_id = p_session_id;
END;
$procedure$;

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

start_parking_session

CREATE OR REPLACE PROCEDURE public.start_parking_session(
    IN p_user_id integer,
    IN p_vehicle_id integer,
    IN p_parking_id integer,
    IN p_tariff_id integer,
    IN p_session_status_id integer
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM app_user WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'User does not exist.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM vehicle
        WHERE vehicle_id = p_vehicle_id AND user_id = p_user_id
    ) THEN
        RAISE EXCEPTION 'Vehicle does not exist or does not belong to this user.';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM parking WHERE parking_id = p_parking_id) THEN
        RAISE EXCEPTION 'Parking does not exist.';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM tariff WHERE tariff_id = p_tariff_id) THEN
        RAISE EXCEPTION 'Tariff does not exist.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM session_status WHERE session_status_id = p_session_status_id
    ) THEN
        RAISE EXCEPTION 'Session status does not exist.';
    END IF;

    IF has_active_session(p_user_id) THEN
        RAISE EXCEPTION 'User already has an active parking session.';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM parking_spot
        WHERE parking_id = p_parking_id AND status = 'free'
    ) THEN
        RAISE EXCEPTION 'No free parking spots available.';
    END IF;

    INSERT INTO parking_session (
        user_id,
        vehicle_id,
        parking_id,
        tariff_id,
        session_status_id,
        start_time,
        end_time,
        total_amount
    )
    VALUES (
        p_user_id,
        p_vehicle_id,
        p_parking_id,
        p_tariff_id,
        p_session_status_id,
        NOW(),
        NULL,
        0.00
    );

    UPDATE parking_spot
    SET status = 'occupied'
    WHERE parking_spot_id = (
        SELECT parking_spot_id
        FROM parking_spot
        WHERE parking_id = p_parking_id AND status = 'free'
        LIMIT 1
    );
END;
$procedure$;

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

Functions

calculate_session_amount

CREATE OR REPLACE FUNCTION public.calculate_session_amount(p_session_id integer)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
    v_start TIMESTAMP;
    v_end TIMESTAMP;
    v_tariff_id INT;
    v_price NUMERIC;
    v_hours NUMERIC;
BEGIN
    SELECT start_time, end_time, tariff_id
    INTO v_start, v_end, v_tariff_id
    FROM parking_session
    WHERE session_id = p_session_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Parking session does not exist.';
    END IF;

    IF v_end IS NULL THEN
        RETURN 0.00;
    END IF;

    v_hours := CEIL(EXTRACT(EPOCH FROM (v_end - v_start)) / 3600.0);

    SELECT price
    INTO v_price
    FROM price_list
    WHERE tariff_id = v_tariff_id
      AND CURRENT_DATE BETWEEN valid_from AND valid_to
    ORDER BY valid_from DESC
    LIMIT 1;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Valid price for tariff does not exist.';
    END IF;

    RETURN ROUND(v_hours * v_price, 2);
END;
$function$;

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

has_active_session

CREATE OR REPLACE FUNCTION public.has_active_session(p_user_id integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE
    v_exists BOOLEAN;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM app_user
        WHERE user_id = p_user_id
    ) THEN
        RAISE EXCEPTION 'User does not exist.';
    END IF;

    SELECT EXISTS (
        SELECT 1
        FROM parking_session
        WHERE user_id = p_user_id
          AND end_time IS NULL
    )
    INTO v_exists;

    RETURN v_exists;
END;
$function$;

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

trg_set_total_amount

CREATE OR REPLACE FUNCTION public.trg_set_total_amount()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
    v_price NUMERIC;
    v_hours NUMERIC;
BEGIN
    IF NEW.end_time IS NULL THEN
        NEW.total_amount := 0.00;
    ELSE
        IF NEW.start_time IS NULL THEN
            RAISE EXCEPTION 'Start time cannot be null.';
        END IF;

        IF NEW.end_time <= NEW.start_time THEN
            RAISE EXCEPTION 'End time must be after start time.';
        END IF;

        SELECT price
        INTO v_price
        FROM price_list
        WHERE tariff_id = NEW.tariff_id
          AND CURRENT_DATE BETWEEN valid_from AND valid_to
        ORDER BY valid_from DESC
        LIMIT 1;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Valid price for tariff does not exist.';
        END IF;

        v_hours := CEIL(EXTRACT(EPOCH FROM (NEW.end_time - NEW.start_time)) / 3600.0);

        NEW.total_amount := ROUND(v_hours * v_price, 2);
    END IF;

    RETURN NEW;
END;
$function$;

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

Triggers

trg_update_total_amount

CREATE TRIGGER trg_update_total_amount
BEFORE INSERT OR UPDATE OF end_time
ON public.parking_session
FOR EACH ROW
EXECUTE FUNCTION trg_set_total_amount();

Овој trigger автоматски се активира при креирање нова паркинг сесија или при промена на крајното време на постоечка сесија. Ја имплементира бизнис логиката за автоматска пресметка на вкупниот износ за паркирање без потреба од рачна интервенција, преку повикување на trigger функцијата trg_set_total_amount().