DatabaseProgramming: AssignSeat.sql

create or replace procedure SetSeat(
    TicketID_P int,
    SeatID_P int
) as $$
declare
    TicketFlightID int;
    TicketSeatID int;
    TicketSeatClassID int;
    TicketStatusID_V int;
    SeatFlightID int;
    SeatReserved bool;
    SeatClassID_V int;
    BoardedStatus int;
begin
    select id
    into strict BoardedStatus
    from ticketstatus
    where name = 'Boarded';

    select flightid, seatid, seatclassid, ticketstatusid
    into TicketFlightID, TicketSeatID, TicketSeatClassID, TicketStatusID_V
    from ticket
    where id = TicketID_P
    for update;

    if not found then
        raise exception 'Ticket not found!';
    end if;

    if TicketStatusID_V = BoardedStatus then
        raise exception 'Ticket is already boarded!';
    end if;

    if TicketSeatID = SeatID_P then
        raise exception 'Ticket already has that seat!';
    end if;

    select s.flightid, s.reserved, sc.seatclassid
    into SeatFlightID, SeatReserved, SeatClassID_V
    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 not found!';
    end if;

    if SeatFlightID <> TicketFlightID then
        raise exception 'Seat is not on the ticket''s flight!';
    end if;

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

    if SeatClassID_V <> TicketSeatClassID then
        raise exception 'Seat class does not match the ticket''s class!';
    end if;

    if TicketSeatID is not null then
        update seat
        set reserved = false
        where id = TicketSeatID;
    end if;

    update ticket
    set seatid = SeatID_P
    where id = TicketID_P;

    update seat
    set reserved = true
    where id = SeatID_P;
end;
$$ language plpgsql;