DatabaseProgramming: PurchaseTicket.sql

create or replace procedure PurchaseTicket(
    PassengerID_P int,
    FlightID_P int,
    SeatClassID_P int,
    SeatID_P int,
    ReservationID_P int
) as $$
declare
    Departure timestamp;
    Distance_V int;
    SeatReserved bool;
    SeatFlightID int;
    SeatSurcharge numeric(10, 2);
    SeatActualClassID int;
    ClassMultiplier numeric(4, 2);
    BasePrice_V numeric(10, 2);
    FinalPrice_V int;
    CanceledTicketStatus int;
    ClassCapacity int;
    ClassSold int;
begin
    if SeatClassID_P is null then
        raise exception 'Seat class must be specified!';
    end if;

    if not exists (select 1 from seatclass where id = SeatClassID_P) then
        raise exception 'Seat class does not exist!';
    end if;

    select id into strict CanceledTicketStatus
    from ticketstatus where name = 'Canceled Flight';

    select f.departure, sf.distance
    into Departure, Distance_V
    from flight f
    join scheduledflight sf on sf.id = f.scheduleid
    where f.id = FlightID_P
    for update of f;

    if not found then
        raise exception 'Flight does not exist!';
    end if;

    if Departure <= now() + interval '3 hours' then
        raise exception 'Not allowed to purchase ticket for this flight!';
    end if;

    if exists (select 1 from canceledflight where flightid = FlightID_P) then
        raise exception 'Flight has been canceled!';
    end if;

    if not exists (select 1 from passenger where id = PassengerID_P) then
        raise exception 'Passenger does not exist!';
    end if;

    if not exists (
        select 1 from reservation
        where id = ReservationID_P and passengerid = PassengerID_P
    ) then
        raise exception 'Reservation does not exist or does not belong to the right passenger!';
    end if;

    if SeatID_P is not null then
        select s.reserved, s.flightid, sc.price, sc.seatclassid
        into SeatReserved, SeatFlightID, SeatSurcharge, SeatActualClassID
        from seat s
        join seatconfiguration sc on sc.id = s.seatconfigurationid
        where s.id = SeatID_P
        for update of s;

        if not found then
            raise exception 'Seat does not exist!';
        end if;

        if SeatFlightID <> FlightID_P then
            raise exception 'Seat is not for the correct flight!';
        end if;

        if SeatReserved then
            raise exception 'Seat is already reserved!';
        end if;

        if SeatActualClassID <> SeatClassID_P then
            raise exception 'Seat class does not match the chosen seat!';
        end if;
    else
        SeatSurcharge := 0;

        select count(*)
        into ClassCapacity
        from seat s
        join seatconfiguration sc on sc.id = s.seatconfigurationid
        where s.flightid = FlightID_P
          and sc.seatclassid = SeatClassID_P;

        select count(*)
        into ClassSold
        from ticket
        where flightid = FlightID_P
          and seatclassid = SeatClassID_P
          and ticketstatusid <> CanceledTicketStatus;

        if ClassSold >= ClassCapacity then
            raise exception 'No more seats available in the chosen class!';
        end if;
    end if;

    ClassMultiplier := case SeatClassID_P
        when 1 then 1
        when 2 then 1.5
        when 3 then 2
        when 4 then 3
    end;

    BasePrice_V  := Distance_V * ClassMultiplier;
    FinalPrice_V := ceil(BasePrice_V + SeatSurcharge);

    insert into ticket(
        seatid, passengerid, baseprice, finalprice,
        seatclassid, reservationid, flightid
    )
    values (
        SeatID_P, PassengerID_P, BasePrice_V, FinalPrice_V,
        SeatClassID_P, ReservationID_P, FlightID_P
    );
end;
$$ language plpgsql;