DatabaseProgramming: CheckInTicket.sql

create or replace procedure CheckIn(TicketID_P int) as $$
declare
    TicketStatusID_V int;
    TicketFlightID int;
    TicketSeatID int;
    TicketSeatClassID int;
    FlightDeparture timestamp;
    ScheduledStatus int;
    CheckedInStatus int;
    BoardedStatus int;
    NewSeatID int;
begin
    select id into strict ScheduledStatus
    from ticketstatus where name = 'Scheduled';

    select id into strict CheckedInStatus
    from ticketstatus where name = 'Checked In';

    select id into strict BoardedStatus
    from ticketstatus where name = 'Boarded';

    select t.ticketstatusid, t.flightid, t.seatid, t.seatclassid, f.departure
    into TicketStatusID_V, TicketFlightID, TicketSeatID, TicketSeatClassID, FlightDeparture
    from ticket t
    join flight f on f.id = t.flightid
    where t.id = TicketID_P
    for update of t;

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

    if TicketStatusID_V = CheckedInStatus then
        raise exception 'Ticket already checked in!';
    end if;

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

    if TicketStatusID_V <> ScheduledStatus then
        raise exception 'Ticket is not in a check-in-eligible state!';
    end if;

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

    if FlightDeparture > now() + interval '8 hours' then
        raise exception 'Check in not yet open!';
    end if;

    if FlightDeparture < now() + interval '45 minutes' then
        raise exception 'Check in closed!';
    end if;

    if TicketSeatID is null then
        select s.id
        into NewSeatID
        from seat s
        join seatconfiguration sc on sc.id = s.seatconfigurationid
        where s.flightid = TicketFlightID
          and s.reserved = false
          and sc.seatclassid = TicketSeatClassID
        order by random()
        limit 1
        for update of s skip locked;

        if NewSeatID is null then
            raise exception 'No available seats matching the ticket class!';
        end if;

        update ticket
        set seatid = NewSeatID
        where id = TicketID_P;

        update seat
        set reserved = true
        where id = NewSeatID;
    end if;

    update ticket
    set ticketstatusid = CheckedInStatus
    where id = TicketID_P;
end;
$$ language plpgsql;