Changes between Version 4 and Version 5 of DatabaseProgramming

Timestamp:

05/20/26 15:46:32 (6 days ago)

Author:

231035

Comment:

--

DatabaseProgramming

@@ -1 +1 @@
 = 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
 {{{
@@ -22 +94 @@
     $$ language plpgsql;
 
-create trigger prevent_double_booking
+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 $$
@@ -41 +112 @@
     $$ language plpgsql;
 
-create trigger validate_booking_dates
+create or replace trigger validate_booking_dates
     before insert or update on bookings for each row
     execute function trg_validate_booking_dates();
@@ -47 +118 @@
 == 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;
+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;
-        return new;
+            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;
-
-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
@@ -101 +176 @@
     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
@@ -219 +351 @@
 }}}
 
-== 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,
-    p_total_price    NUMERIC
+== 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
-    IF p_check_out_date <= p_check_in_date THEN
-        RAISE EXCEPTION 'Check-out date must be after check-in date';
-    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;
-
-    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,
-        p_total_price,
-        'PENDING'
-    );
-
-    RAISE NOTICE 'Booking created successfully';
+    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;
+}}}