== Database Programming

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

== Процедури

**Процедура за почеток на смена на возач**

Процедурата проверува дали постои id-то на возачот и VIN бројот кај возилото и фрла соодветен исклучот. Доколку возачот и возилото се валидни се додава нов запис за смена во табелата `Driver_Vehicle`.

{{{
create or replace procedure start_shift(
    in driver_id int4,
    in vin_vehicle varchar(17)
)
    language plpgsql as
$$
begin
    if not exists(select user_id from driver where user_id = driver_id) then
        raise exception 'Driver with id % does not exist', driver_id;
    end if;

    if not exists(select vin from vehicle where vin = vin_vehicle) then
        raise exception 'Vehicle with VIN % does not exist', vin_vehicle;
    end if;

    insert into driver_vehicle(vin_vehicle, id_driver, time_from, time_to)
    values (vin_vehicle, driver_id, now(), null);
    commit;
end;
$$;
}}}

**Процедура за завршување на смена на возач**

Процедурата проверува дали постои id-то на возачот, доколку не постои фрла соодветен исклучок. Потоа се проверува дали возачот има точно една активна смена која не е завршена. На крај се ажурира записот во табелата `Driver_Vehicle` на тој начин што се поставува тековното време во колоната `time_to`.

{{{
create or replace procedure finish_shift(
    in driver_id int4
)
    language plpgsql as
$$
declare
    num_of_shifts int4;
begin
    if not exists(select user_id from driver where user_id = driver_id) then
        raise exception 'Driver with id % does not exist', driver_id;
    end if;

    num_of_shifts := (select count(*) from driver_vehicle where id_driver = driver_id and time_to is null);

    if (num_of_shifts != 1) then
        raise exception 'Driver has not started a shift';
    end if;

    update driver_vehicle
    set time_to=now()
    where id_driver = driver_id
      and time_to is null;
    commit;
end;
$$;
}}}
**Процедура за вработување на возач во компанија**

Процедурата проверува дали id-то на возачот е валидно, доколку не постои фрла соодветен исклучок.

{{{
create or replace procedure employ_driver(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
                   select 1
                   from driver
                   where user_id=id_employee)
               then
                   raise exception 'Driver does not exist';
        else
               insert into
                   employmenthistory (employee_user_id, start_date, end_date, company_id)
                   values (id_employee,current_date, null,id_company);
        end if;
    end;
$$;
}}}

**Процедура за вработување на диспечер во компанија**

Процедурата проверува дали id-то на диспечерот е валидно, доколку не постои фрла соодветен исклучок.

{{{
create or replace procedure employ_dispatcher(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
                   select 1
                   from dispatcher
                   where user_id=id_employee)
               then
                   raise notice 'Dispatcher does not exist';
        else
               insert into
                   employmenthistory (employee_user_id, start_date, end_date, company_id)
                   values (id_employee,current_date, null,id_company);
        end if;
    end;
$$;
}}}

**Процедура за отпуштање/завршување на договор на возач во компанија**

Процедурата прави две проверки. Првата проверка ја пребарува табелата Driver за id-то на возачот, доколку истото не постои се фрла соодветен исклучок. Потоа се проверува дали постои соодветен запис во табелата `EmploymentHistory` кој потврдува дека возачот е тековно вработен во компанијата. На крај се променува записот од табелата `EmploymentHistory` на тој начин што се става краен датум.

{{{
create or replace procedure fire_driver(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
               select 1
               from driver
               where user_id=id_employee)
           then
               raise exception 'Driver does not exist';
       else
           if not exists (
               select 1
               from employmenthistory
               where employee_user_id=id_employee and end_date==null and company_id=id_company)
           then
               raise exception 'Driver does not have an employment';
           else
               update employmenthistory
               set end_date=current_date
               where employee_user_id==id_employee and end_date==null and company_id=id_company;
               raise notice 'Driver is fired';
               commit;
           end if;
       end if;
    end;
$$;
}}}

**Процедура за отпуштање/завршување на договор на диспечер во компанија**

Процедурата прави две проверки. Првата проверка ја пребарува табелата `Dispatcher` за id-то на диспечарот, доколку истото не постои се фрла соодветен исклучок. Потоа се проверува дали постои соодветен запис во табелата `EmploymentHistory` кој потврдува дека деспечерот е тековно вработен во компанијата. На крај се променува записот од табелата `EmploymentHistory` на тој начин што се става краен датум.

{{{
create or replace procedure fire_dispatcher(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
               select 1
               from dispatcher
               where user_id=id_employee)
           then
               raise notice 'Dispatcher does not exist';
       else
           if not exists(
               select 1
               from employmenthistory
               where employee_user_id=id_employee and end_date==null and company_id=id_company)
           then
               raise exception 'Dispatcher does not have an employment';
           else
               update employmenthistory
               set end_date=current_date
               where employee_user_id=id_employee and end_date==null and company_id=id_company;
               raise notice 'Dispatcher is fired';
               commit;
           end if;
       end if;
    end;
$$;
}}}

**Процедура за пишување на оцена**

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

{{{
create or replace procedure write_rating(rating numeric, comment text, id_ride int, id_customer int)
language plpgsql
as $$
    begin
        insert into review (rating, comment, ride_id, customer_user_id)
        values (write_rating.rating,write_rating.comment,id_ride,id_customer);
    end;
$$;
}}}

**Процедура за испраќање порака**

Пораките можат да бидат испратени од возачот или патникот и се однесуваат на едно возење.

{{{
create or replace procedure write_chat(new_message text, id_ride int, id_user int)
language plpgsql
as $$
    begin
        insert into chatmessage(message, user_id_from, ride_id)
        values (new_message,id_user,id_ride);
    end;
$$;
}}}

**Процедура за поднесување пријава**

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

{{{
create or replace procedure write_report(id_ride int, user_id int, new_message text, new_title text,temp_latitude float, temp_long float, new_reason text)
language plpgsql
as $$
    begin
        insert into report(ride_id, customer_user_id, message, title, latitude, longitude, reason)
        values (id_ride,user_id,new_message,new_title,temp_latitude,temp_long,new_reason);
    end;
$$;
}}}

**Процедура за креирање на барање за превоз**

Процедурата `make_request` се повикува кога корисникот креира ново барање за превоз. Најпрво се проверува дали постои корисникот со даденото `customer_id`, по што се валидираат координатите за почетната и крајната локација. Дополнително се проверуваат параметрите поврзани со бројот на патници, детски седишта и багаж, при што се фрлаат соодветни исклучоци доколку некоја вредност е невалидна. Исто така, процедурата проверува дали почетната и крајната локација не се исти. Доколку сите проверки се успешни, се додава нов запис во табелата `Request` со статус `pending`, а идентификаторот на новокреираното барање се враќа преку излезниот параметар `request_id`.

{{{
create or replace procedure make_request(
    out request_id int4,
    in customer_id int4,
    in start_latitude double precision,
    in start_longitude double precision,
    in end_latitude double precision,
    in end_longitude double precision,
    in number_of_adult_passengers int4 default 1,
    in number_of_children int4 default 0,
    in female_driver boolean default false,
    in luggage boolean default false,
    in luggage_count int4 default 0,
    in baby_seat_count int4 default 0
)
    language plpgsql as
$$
begin
    if not exists(select user_id
                  from customer c
                  where c.user_id = customer_id)
    then
        raise exception 'Customer with id % does not exist', customer_id;
    end if;

    if start_latitude < -90.0 or start_latitude > 90.0
        or start_longitude < -180.0 or start_longitude > 180.0
        or end_latitude < -90.0 or end_latitude > 90.0
        or end_longitude < -180.0 or end_longitude > 180.0
    then
        raise exception 'Invalid location values';
    end if;

    if number_of_adult_passengers < 0 then
        raise exception 'Number of adult passengers cannot be negative';
    end if;

    if number_of_children < 0 then
        raise exception 'Number of children cannot be negative';
    end if;

    if number_of_adult_passengers + number_of_children < 0 then
        raise exception 'There must be at least 1 passenger';
    end if;

    if baby_seat_count < 0 then
        raise exception 'Baby seat count cannot be negative';
    end if;

    if luggage = false and luggage_count > 0 then
        raise exception 'Luggage count cannot be greater than zero if the luggage flag is set to false.';
    end if;

    if luggage = true and luggage_count <= 0 then
        raise exception 'Invalid luggage count parameters';
    end if;

    if start_latitude = end_latitude and start_longitude = end_longitude then
        raise exception 'Start and destination location cannot be the same';
    end if;

    insert into request(customer_user_id,
                                      start_latitude,
                                      start_longitude,
                                      end_latitude,
                                      end_longitude,
                                      timestamp,
                                      number_of_adult_passengers,
                                      number_of_children,
                                      status,
                                      female_driver,
                                      luggage,
                                      luggage_count,
                                      baby_seat_count)
    values (customer_id,
            make_request.start_latitude,
            make_request.start_longitude,
            make_request.end_latitude,
            make_request.end_longitude,
            now(),
            make_request.number_of_adult_passengers,
            make_request.number_of_children,
            'pending',
            make_request.female_driver,
            make_request.luggage,
            make_request.luggage_count,
            make_request.baby_seat_count)
    returning id into request_id;
    commit;
end;
$$;
}}}

**Процедура за додавање на Waypoint на некое барање**

Процедурата `add_waypoint` се повикува кога корисникот сака да додаде попатна точка на постоечко барање за превоз. Најпрво се проверува дали постои барањето со даденото `request_id`, по што се валидираат координатите на waypoint-от. Дополнително се проверува дали истиот `waypoint` веќе е додаден за конкретното барање, при што се фрла соодветен исклучок доколку постои дупликат. Потоа се пресметува редниот број (`seqno`) на новиот `waypoint` врз основа на бројот на веќе постоечки waypoints за тоа барање. Доколку сите проверки се успешни, се додава нов запис во табелата `Waypoints`.

{{{
create or replace procedure add_waypoint(
    in request_id int4,
    in latitude int4,
    in longitude int4
)
    language plpgsql as
$$
declare
    prev_seqno int4;
begin
    if not exists(select id from request where id = request_id) then
        raise exception 'Request with id % does not exist', request_id;
    end if;

    if latitude < -90.0 or latitude > 90.0
        or longitude < -180.0 or longitude > 180.0 then
        raise exception 'Invalid waypoint coordinates';
    end if;

    if exists(select id
              from waypoints w
              where w.latitude = add_waypoint.latitude
                and w.longitude = add_waypoint.longitude
                and w.request_id = add_waypoint.request_id) then
        raise exception 'Waypoint already added';
    end if;

    select count(*)
    into prev_seqno
    from waypoints w
    where w.request_id = add_waypoint.request_id;

    insert into waypoints(latitude, longitude, seqno, request_id)
    values (add_waypoint.latitude, add_waypoint.longitude, prev_seqno + 1, add_waypoint.request_id);
    commit;
end;
$$;
}}}

**Процедура за зпочнување на возење со такси**

Процедурата проверува дали постои id-то на понудата, доколку не постои фрла соодветен исклучок. Потоа се пронаоѓа најблискиот слободен возач кој е на активна смена. На крај се додава нов запис во табелата `Ride` со статус `in_progress`, а статусите на понудата и барањето се ажурираат во accepted и `in_progress`.

{{{
create or replace procedure start_ride(
    in offer_id int4,
    out ride_id int4
)
    language plpgsql as
$$
declare
    v_request_id      int4;
    start_latitude  double precision;
    start_longitude double precision;
    driver_id       int4;
    v_vehicle_vin     varchar(17);
--     v_status          ride_status;
begin
    if not exists(select id from offer where id = offer_id) then
        raise exception 'Offer with id % does not exist', offer_id;
    end if;

    select o.request_id,
           r.start_latitude,
           r.start_longitude
    into
        request_id,
        start_latitude,
        start_longitude
    from offer o
             join request r on o.request_id = r.id
    where o.id = offer_id;

    select d.user_id, dc.vin_vehicle
    into driver_id,
        v_vehicle_vin
    from driver d
             join driver_vehicle dc on d.user_id = dc.id_driver
    where dc.time_to is null
      and d.latitude is not null
      and d.longitude is not null
    order by power(start_latitude - d.latitude, 2) + power(start_longitude - d.longitude, 2)
    limit 1;

    if driver_id is null then
        raise exception 'No active drivers available';
    end if;

    insert into ride(start_time,
                     end_time,
                     distance_traveled,
                     vehicle_vin,
                     driver_user_id,
                     request_id,
                     status,
                     offer_id)
    values (now(),
            null,
            0,
            v_vehicle_vin,
            driver_id,
            v_request_id,
            'in_progress',
            start_ride.offer_id);

    update offer
    set status='accepted'
    where id = offer_id;

    update request
    set status='in_progress'
    where id = v_request_id;

    commit;
end;
$$;
}}}

** Процедура за креирање понуда за одредено барање **

Процедурата се повикува кога диспечерот креира нова понуда за одредено барање за превоз. Најпрво се проверува дали постои барање со даденото `request_id` и дали истото е во статус pending, при што се фрла соодветен исклучок доколку условот не е исполнет. Потоа се валидираат влезните параметри: цената мора да биде поголема од нула, а проценетото време на пристигнување (eta) мора да биде во иднина во однос на тековното време. Дополнително, процедурата проверува дали диспечерот и возачот се тековно вработени во истата компанија преку пребарување на нивните активни договори во табелата `EmploymentHistory`. Доколку сите проверки се успешни, се додава нов запис во табелата `Offer` со иницијален статус pending.

{{{
create or replace procedure create_offer(
    request_id int4,
    dispatcher_user_id int4,
    driver_user_id int4,
    price numeric(19, 2),
    currency_catalog_id int4,
    eta timestamp,
    customer_user_id int4
)
    language plpgsql
AS
$$
begin
    if not exists(select * from request where request.id = request_id and request.status = 'pending') then
        raise exception 'Request with id % and pending status does not exist', request_id;
    end if;
    if price <= 0 then
        raise exception 'Price has to be greater than 0';
    end if;
    if eta <= now() then
        raise exception 'ETA cannot be lower than the time of creation';
    end if;

    if (select company_id from employmenthistory where employee_user_id = dispatcher_user_id and end_date is null) !=
       (select company_id from employmenthistory where employee_user_id = driver_user_id and end_date is null) then
        raise exception 'Dispatcher and driver company mismatch';
    end if;

    insert into offer(status, created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id,
                      eta, customer_user_id)
    values ('pending', now(), create_offer.request_id, create_offer.dispatcher_user_id, create_offer.driver_user_id,
            create_offer.price, create_offer.currency_catalog_id, create_offer.eta, create_offer.customer_user_id);
    commit;
end;
$$;
}}}

** Процедура за прифаќање на понуда **

Оваа процедура се повикува кога корисникот прифаќа одредена понуда за превоз. Таа го ажурира статусот на избраната понуда и на самото барање во `accepted`, додека сите останати конкурентни понуди за истото барање се одбиваат, односно нивниот статус се променува во `rejected`.

{{{
create or replace procedure accept_offer(offer_id int4)
    language plpgsql
as
$$
declare
    request_id_proc int4;
begin
    if not exists(select * from offer where id = offer_id and status = 'pending') then
        raise exception 'Offer with id = % and pending status does not exist', offer_id;
    end if;
    request_id_proc := (select request_id from offer where id = offer_id);
    if not exists(select * from request where id = request_id_proc and status = 'pending') then
        raise exception 'The request for the given offer is not in pending status';
    end if;
    update offer set status = 'accepted' where id = offer_id;
    update offer set status = 'rejected' where id != offer_id and offer.request_id = request_id_proc;
    update request set status = 'accepted' where id = request_id_proc;
    commit;
end;
$$;
}}}
== Функции и тригери

**Функција и тригер за проверка на достапност на возач**

Функцијата `ride_assign` враќа тригер кој фрла исклучок во случај возачот да е зафатен со друго возење во моментот на доделување на возач. Тригерот се извршува пред внес на запис во табелата `Ride`.

{{{
create or replace function ride_assign() returns trigger as
$ride_assign$
begin
    if exists(select * from ride where ride.status = 'in_progress' and ride.driver_user_id = NEW.driver_user_id) then
        raise exception 'Driver not free';
    end if;
    return NEW;
end;
$ride_assign$ language plpgsql;

create trigger ride_assign before insert on ride
    for each row execute function ride_assign();
}}}

**Функција и тригер кој спречува два пати доделување на активна смена на ист возач**

Функцијата проверува дали постои id-то на возачот и VIN бројот на возилото, при што фрла соодветен исклучок доколку некој од нив не е валиден. Дополнително, се проверува дали возачот е на смена. Тригерот се извршува пред внесување или ажурирање на запис во табелата Driver_Vehicle.

{{{
create or replace function check_active_shift()
    returns trigger as
$check_active_shift$
begin
    if not exists(select user_id from driver where user_id = new.id_driver) then
        raise exception 'Driver with id % does not exist', new.id_driver;
    end if;

    if not exists(select vin from vehicle where vin = new.vin_vehicle) then
        raise exception 'Vehicle with VIN % does not exist', new.vin_vehicle;
    end if;

    if exists(select id
              from driver_vehicle
              where id_driver = new.id_driver
                and time_to is null) then
        raise exception 'Driver is already on shift';
    end if;

    return new;
end;
$check_active_shift$ language plpgsql;
create trigger check_active_shift
    before insert or update
    on driver_vehicle
    for each row
execute function check_active_shift();
}}}

** Функција и тригер за спречување на манипулација со цената **

Функцијата враќа тригер кој спречува промена на цената на понудата откако истата ќе добие статус `accepted` или `completed`. Дополнително, се прави проверка дали внесената цена е поголема од нула, при што се фрла соодветен исклучок доколку овие услови не се исполнети.

{{{
create or replace function prevent_price_manipulation()
    returns trigger as
$prevent_price_manipulation$
begin
    if OLD.status in ('accepted', 'completed') and NEW.price <> OLD.price then
        raise exception 'Price cannot be modified once the offer is accepted or completed. Current status: %', OLD.status;
    end if;

    if NEW.price <= 0 then
        raise exception 'Price must be a positive value';
    end if;

    return NEW;
end;
$prevent_price_manipulation$ language plpgsql;

create trigger prevent_price_manipulation
    before update on offer
    for each row
    execute function prevent_price_manipulation();
}}}

**Тригер и функција за проверка на единствена оценка**

Секој корисник смее да остави максимум една оценка за секое возење. Функцијата враќа тригер кој доколку во табелата `Review` постои оценка од корисникот за тоа возење фрла соодветен исклучот. Тригерот се извршува пред внес на запис во табелата `Review`.

{{{
create or replace function check_one_review()
returns trigger
language plpgsql
as $$
    begin
        if 0 < (select count(*)
                from review
                where ride_id=new.ride_id and customer_user_id=new.customer_user_id)
        then
            raise exception 'There is already a review from the same customer for this ride';
        end if;
        return new;
    end;
$$;
create or replace trigger check_valid_review
    before insert on review
    for each row
    execute function check_one_review();
}}}

**Тригер и функција за проверка на бројот на слободни седишта**

Секој корисник при испраќање на барање за превоз напоменува колку патници ќе има (деца, воздрасни и бебиња). Функцијата `check_available_seats` проверува дали во возилото кое се нуди во понудата има доволно седишта. Функцијата враќа тригер кои се извршува пред внес или промена на запис во табелата `Offer`.

{{{
create or replace function check_available_seats()
returns trigger
language plpgsql
as $$
    begin
        if (select v.passenger_capacity
            from vehicle v
            inner join driver_vehicle dv on v.vin=dv.vin_vehicle
            where dv.id_driver=new.driver_user_id)<(select r.number_of_children+r.number_of_adult_passengers+r.baby_seat_count
                                                    from request r
                                                    inner join offer o on r.id=o.request_id
                                                    where o.id=new.id)
            then
            raise exception 'Not enough seats in the vehicle';
        end if;
        return new;
    end;
$$;
create or replace trigger check_valid_seats
    before insert or update on offer
    for each row
    execute function check_available_seats();
}}}

**Функција и тригер за спречување на возење со неважечка возачка дозвола**

Функцијата го пребарува датумот на истекување на возачката дозвола за соодветниот возач преку поврзување на табелите `Driver` и `DriverLicense`. Доколку дозволата е истечена во однос на тековниот датум, се фрла соодветен исклучок. Тригерот се извршува пред внес на нов запис во табелата `Ride`.

{{{
create or replace function prevent_expired_license_ride()
    returns trigger as
$prevent_expired_license_ride$
declare
    exp_date date;
begin
    select dl.expire_date
    into exp_date
    from driver d
             join driverlicense dl on d.driver_license_id = dl.id
    where d.user_id = new.driver_user_id;

    if exp_date is not null and exp_date < current_date then
        raise exception 'Driver license expired';
    end if;

    return new;
end;
$prevent_expired_license_ride$ language plpgsql;
create or replace trigger prevent_expired_license_ride
    before insert
    on ride
    for each row
execute function prevent_expired_license_ride();
}}}

**Функција и тригер за спречување на промена на статус на завршена понуда**

Функцијата проверува дали претходниот статус на понудата бил `completed`, при што фрла соодветен исклучок доколку се направи обид за негова промена во било кој друг статус. Тригерот се извршува пред ажурирање на запис во табелата `Offer`.

{{{
create or replace function prevent_completed_offer_downgrade()
    returns trigger as
$prevent_completed_offer_downgrade$
begin
    if old.status = 'completed'
        and new.status <> 'completed' then
        raise exception 'Completed offer cannot change status';
    end if;

    return new;
end;
$prevent_completed_offer_downgrade$ language plpgsql;
create or replace trigger prevent_completed_offer_downgrade
    before update
    on offer
    for each row
execute function prevent_completed_offer_downgrade();
}}}

** Функција и тригер за контрола на времето на важност на понудата **

Оваа функција враќа тригер кој спречува прифаќање на понуди кои се постари од 5 минути од моментот на нивното креирање. Доколку се направи обид за промена на статусот од `pending` во `accepted` по истекот на овој рок, статусот на понудата автоматски се менува во `expired` и истата не може да биде прифатена.

{{{
create or replace function enforce_offer_validity()
    returns trigger as
$enforce_offer_validity$
begin
    if OLD.status = 'pending' and NEW.status = 'accepted' then
      if (NOW() - OLD.created_at) > interval '5 minutes' then
            NEW.status := 'expired';
            raise notice 'Offer % has expired and cannot be accepted.', OLD.id;
            return NEW;
        end if;
    end if;

    return NEW;
end;
$enforce_offer_validity$ language plpgsql;

create trigger trg_enforce_offer_validity
    before update on offer
    for each row
    execute function enforce_offer_validity();
}}}

**Функција и тригер за проверка на валидноста на email адреса и телефонски број**

Функцијата проверува дали постои корисник со ист email или ист телефонски број и фрла исклучоци `User with this email already exists` и `User with this phone number already exists` соодветно. Функцијата враќа тригер кои се извршува пред секое додавање на нов запис-корисник во табелата `AppUser`.

{{{
create or replace function unique_email_and_phone()
returns trigger
as
    $$
    begin
        if exists(
            select 1
            from appuser
            where appuser.email=NEW.email
        ) then
            raise exception 'User with this email already exists';
        end if ;
        if exists(
            select 1
            from appuser
            where appuser.phone_number=NEW.phone_number
        ) then
            raise exception 'User with this phone number already exists';
        end if ;
    end;
    $$;
create trigger check_valid_email_and_phone_number
before insert on Appuser
for each row
execute function unique_email_and_phone()
}}}