Database Programming

Prevention of double booking the same room

create or replace function trg_prevent_double_booking()
returns trigger
as $$
    begin
        if NEW.booking_status in ('PENDING','CONFIRMED') then
            if exists(
                select 1
                from bookings b
                where b.room_id = NEW.room_id
                and b.booking_status in ('PENDING','CONFIRMED')
                and daterange(b.check_in_date, b.check_out_date, '[)')
                  && daterange(NEW.check_in_date, NEW.check_out_date, '[)')
            )then
                raise exception 'Room % is already booked',NEW.room_id;
            end if;
        end if;
        return new;
    end;
    $$ language plpgsql;

create trigger prevent_double_booking
    before insert on bookings for each row execute function trg_prevent_double_booking();

Validate the dates of a booking

create or replace function trg_validate_booking_dates()
returns trigger as $$
    begin
        if new.check_in_date >= new.check_out_date then
            raise exception 'Check out date must be after check in date';
        end if;
        if new.check_in_date < now() then
            raise exception 'Check in date cannot be in the past';
        end if;
        return new;
    end;
    $$ language plpgsql;

create trigger validate_booking_dates
    before insert or update on bookings for each row
    execute function trg_validate_booking_dates();

A review can be made only for a complete booking

create or replace function trg_review_only_completed_booking()
returns trigger
as $$
    declare v_status varchar;
    begin
        select booking_status
        into v_status
            from bookings
        where booking_id = new.booking_id;

        if v_status <> 'COMPLETED' then
            raise exception 'Can only review completed bookings';
        end if;
        return new;
    end;
    $$ language plpgsql;

create trigger review_only_completed_booking
    before insert or update on reviews for each row
    execute function trg_review_only_completed_booking();

Check if a room is available

create or replace function fn_is_room_available(p_room_id bigint,p_check_in_date date, p_check_out_date date)
returns boolean
language plpgsql
as $$
    begin
        if p_check_in_date >= p_check_out_date then
            return false;
        end if;

        if not exists(
            select 1
            from rooms r
            where r.room_id = p_room_id
            and r.status = 'ACTIVE'
        )then
            return false;
        end if;

        if exists(
            select 1
            from bookings b
            where b.room_id = p_room_id
            and b.booking_status in ('PENDING','CONFIRMED')
            and daterange(b.check_in_date,b.check_out_date,'[)')
            && daterange(p_check_in_date,p_check_out_date,'[)')
        )then
            return false;
        end if;
        return true;
    end;
    $$;

Calculate the number of nights stayed

create or replace function fn_calculate_nights(p_check_in_date date, p_check_out_date date)
returns int
language plpgsql
as $$
    begin
        if p_check_in_date >= p_check_out_date then
            raise exception 'Check out must be after check in';
        end if;
        return p_check_out_date - p_check_in_date;
    end;
    $$;

Calculate the total price of a booking

create or replace function fn_calculate_booking_price(p_room_id bigint, p_check_in_date date,p_check_out_date date,
p_guest_count int)
returns numeric
language plpgsql
as $$
    declare
        v_price_per_night numeric;
        v_capacity int;
        v_extra_capacity int;
        v_extra_guest_price numeric;

        v_nights int;
        v_room_total numeric;
        v_amenities_total numeric;
        v_extra_guests int;
        v_extra_guest_total numeric;
        v_total numeric;
    begin
        select
            r.price_per_night,
            r.capacity,
            coalesce(r.extra_capacity,0),
            coalesce(r.extra_guest_price,0)
        into
        v_price_per_night,
            v_capacity,
            v_extra_capacity,
            v_extra_guest_price
        from rooms r
        where r.room_id = p_room_id;

        if p_check_in_date>=p_check_out_date then
            raise exception 'Check out must be after check in';
        end if;
        v_nights := p_check_in_date - p_check_out_date;
        if p_guest_count > v_capacity + v_extra_capacity then
            raise exception 'Guest count exceeds';
        end if;
        v_room_total := v_price_per_night * v_nights;

        select coalesce(sum(a.price),0)
        into v_amenities_total
        from room_amenities ra
        join amenities a on a.amenity_id = ra.amenity_id
        where ra.room_id = p_room_id
        and a.is_included = false;

        v_extra_guests := greatest(p_guest_count-v_capacity,0);
        v_extra_guest_total := v_extra_guests*v_extra_guest_price*v_nights;

        v_total := v_room_total + v_extra_guest_total +v_amenities_total;
        return v_total;

    end;
    $$;

Cancel a booking

create or replace procedure sp_cancel_booking(p_booking_id bigint)
language plpgsql
as $$
    declare
        v_status varchar;
    begin
        select b.booking_status
        into v_status
            from bookings b
        where booking_id = p_booking_id;

        if v_status is null then
            raise exception 'Booking does not exsists';
        end if;

        if v_status = 'COMPLETED' then
            raise exception 'Completed booking cannot be canceled';
        end if;
        if v_status = 'CANCELLED' then
            raise exception 'The booking is already cancelled';
        end if;

        update bookings
        set booking_status = 'CANCELLED'
        where booking_id = p_booking_id;

    end;
    $$;

Complete old bookings

create or replace procedure  sp_complete_finished_bookings()
language plpgsql
as $$
    begin
        update bookings
        set booking_status = 'COMPLETED'
        where booking_status = 'CONFIRMED'
        and check_out_date < current_date;
    end;
    $$;