Database Programming

Create a booking

create or replace procedure book_room(
    p_room_id        bigint,
    p_guest_id       bigint,
    p_check_in_date  date,
    p_check_out_date date,
    p_guests_count   int
)
as $$
    declare
        v_total_price numeric;
    begin
        if p_check_out_date <= p_check_in_date then
            raise exception 'Check-out date must be after check-in date';
        end if;

        if fn_is_room_available(
            p_room_id,
            p_check_in_date,
            p_check_out_date
        ) = false then
            raise exception 'Room % is not available for selected dates', p_room_id;
        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
            raise exception 'Room % is already booked for selected dates', p_room_id;
        end if;

        v_total_price := fn_calculate_booking_price(
            p_room_id,
            p_check_in_date,
            p_check_out_date,
            p_guests_count
        );

        insert into bookings (
            room_id,
            guest_id,
            check_in_date,
            check_out_date,
            guests_count,
            total_price,
            booking_status
        )
        values (
            p_room_id,
            p_guest_id,
            p_check_in_date,
            p_check_out_date,
            p_guests_count,
            v_total_price,
            'PENDING'
        );

        update availability_windows aw
        set status = 'BOOKED'
        where aw.room_id = p_room_id
          and aw.available_date >= p_check_in_date
          and aw.available_date < p_check_out_date;

        raise notice 'Booking created successfully. Total price: %', v_total_price;
    end;
    $$ language plpgsql;

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 or replace 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 or replace 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 procedure review_booking(r_booking_id bigint,rating int,comment varchar)
as $$
    declare
            v_status varchar;
        guest_id bigint;
        property bigint;
    begin
        select b.booking_status,b.guest_id,r.property_id
        into v_status,guest_id,property
        from bookings b
        join rooms r on b.room_id = r.room_id
        where booking_id = r_booking_id;



        if v_status <> 'COMPLETED' then
            raise exception  'Can only review completed bookings';
        end if;
        insert into reviews(booking_id, guest_id, property_id, rating, comment) values
                                                                                    (r_booking_id,guest_id,
                                                                                    property, review_booking.rating,review_booking.comment);

    end;
    $$ language plpgsql;

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;
    $$;

Create timestamp

create or replace function fn_planora_make_timestamp(p_date date,p_time time)
returns timestamp
language plpgsql
as $$
    begin
        return p_date::timestamp + p_time;
    end;
    $$;

Check if 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 $$
DECLARE
    v_needed_days INT;
    v_available_days INT;
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;

    v_needed_days := fn_calculate_nights(p_check_in_date := p_check_in_date,p_check_out_date := p_check_out_date );

    SELECT COUNT(*)
    INTO v_available_days
    FROM availability_windows aw
    WHERE aw.room_id = p_room_id
      AND aw.available_date >= p_check_in_date
      AND aw.available_date < p_check_out_date
      AND aw.status = 'AVAILABLE';

    IF v_available_days = v_needed_days THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
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;
    $$;

Flush availability_window dates that are in the past

CREATE OR REPLACE PROCEDURE refresh_availability_windows(
    p_days_ahead INT DEFAULT 30
)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM availability_windows
    WHERE available_date < CURRENT_DATE;

    INSERT INTO availability_windows (room_id,available_date,status)
    SELECT
        r.room_id,
        d.available_day::date,
        'AVAILABLE'
    FROM rooms r
    CROSS JOIN generate_series(
        CURRENT_DATE,
        CURRENT_DATE + p_days_ahead,
        INTERVAL '1 day'
    ) AS d(available_day)
    WHERE r.status = 'ACTIVE'
      AND NOT EXISTS (
          SELECT 1
          FROM bookings b
          WHERE b.room_id = r.room_id
            AND b.booking_status IN ('PENDING', 'CONFIRMED')
            AND daterange(b.check_in_date, b.check_out_date, '[)')
                && daterange(d.available_day::date, d.available_day::date + 1, '[)')
      )
      AND NOT EXISTS (
          SELECT 1
          FROM availability_windows aw
          WHERE aw.room_id = r.room_id
            AND aw.available_date = d.available_day::date
      );

    RAISE NOTICE 'Availability windows refreshed for % days ahead', p_days_ahead;
END;
$$;

Get user favorite listings

CREATE OR REPLACE FUNCTION get_user_favorite_listings(
    p_user_id BIGINT
)
RETURNS TABLE (
    property_id BIGINT,
    property_title VARCHAR,
    property_status VARCHAR,
    created_at TIMESTAMP,
    description TEXT,
    city VARCHAR,
    country VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.property_id,
        p.title::VARCHAR,
        p.status::VARCHAR,
        p.created_at,
        p.description::text,
        a.city::VARCHAR,
        c.country_name::VARCHAR
    FROM favorite_listings f
    LEFT JOIN properties p
        ON p.property_id = f.property_id
    LEFT JOIN addresses a
        ON a.address_id = p.address_id
    LEFT JOIN countries c
        ON c.country_id = a.country_id
    WHERE f.user_id = p_user_id;
END;
$$;

Confirm a booking

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

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

        if v_status = 'CONFIRMED' then
            raise exception 'Booking is already confirmed';
        end if;

        if v_status = 'CANCELLED' then
            raise exception 'Cancelled booking cannot be confirmed';
        end if;

        if v_status = 'COMPLETED' then
            raise exception 'Completed booking cannot be confirmed';
        end if;

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

        raise notice 'Booking % confirmed successfully', p_booking_id;
    end;
    $$ language plpgsql;