= Advanced Database Development (SQL DDL) =

== 1. Data constraints requirements: Booking Overlapping and Sitter Availability ==
'''Data requirements description'''

Bookings must adhere to real world time and scheduling:
 * A Pet Sitter cannot have overlapping confirmed bookings - they cant be in two places at once.
 * A Pet Owner cannot book the same pet twice on overlapping dates/times.
 * A booking cannot be created if the {{{date_from}}} is in the past.

=== '''Implementation'''
'''Triggers'''
{{{
#!sql
CREATE OR REPLACE FUNCTION petsitter_trg_booking_validation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.date_from < now() THEN
    RAISE EXCEPTION 'Cannot create a booking in the past (date_from=%)', NEW.date_from;
  END IF;

  IF NEW.date_to <= NEW.date_from THEN
    RAISE EXCEPTION 'Booking end date (%) must be after start date (%)', NEW.date_to, NEW.date_from;
  END IF;

  IF NEW.status = 'Confirmed' THEN
    IF EXISTS (
      SELECT 1 FROM bookings b
      WHERE b.sitter_id = NEW.sitter_id
        AND b.status = 'Confirmed'
        AND b.date_from < NEW.date_to 
        AND b.date_to > NEW.date_from
        AND (TG_OP <> 'UPDATE' OR b.booking_id <> NEW.booking_id)
    ) THEN
      RAISE EXCEPTION 'Sitter % is already booked for these dates.', NEW.sitter_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_booking_validation ON bookings;
CREATE TRIGGER trg_booking_validation
BEFORE INSERT OR UPDATE ON bookings
FOR EACH ROW
EXECUTE FUNCTION petsitter_trg_booking_validation();
}}}

----

== 2. Data constraints requirements: Review/Transaction Integrity ==
'''Data requirements description'''

Reviews are associated with the completion of a booking:
 * A review can only be inserted if the relevant booking has the status 'Completed'.
 * A booking can have a maximum of ONE (1) review.
 * Once a review is created, the rating cannot be edited.

=== '''Implementation'''
'''Triggers'''
{{{
#!sql
CREATE OR REPLACE FUNCTION petsitter_trg_review_integrity()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_booking_status varchar;
BEGIN
  -- Get booking status
  SELECT status INTO v_booking_status
  FROM bookings
  WHERE booking_id = NEW.booking_id;

  IF v_booking_status IS NULL THEN
    RAISE EXCEPTION 'Booking % not found.', NEW.booking_id;
  END IF;

  -- Block reviews for bookings that arent completed
  IF v_booking_status <> 'Completed' THEN
    RAISE EXCEPTION 'Cannot leave a review for a booking that is % (Must be Completed)', v_booking_status;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_review_integrity ON reviews;
CREATE TRIGGER trg_review_integrity
BEFORE INSERT ON reviews
FOR EACH ROW
EXECUTE FUNCTION petsitter_trg_review_integrity();

-- Make reviews non-updateable
CREATE OR REPLACE FUNCTION petsitter_trg_reviews_no_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'Reviews are permanent. Updates are not allowed for review_id %.', OLD.review_id;
END;
$$;

DROP TRIGGER IF EXISTS trg_reviews_no_update ON reviews;
CREATE TRIGGER trg_reviews_no_update
BEFORE UPDATE ON reviews
FOR EACH ROW
EXECUTE FUNCTION petsitter_trg_reviews_no_update();
}}}

----

== 3. Data constraints requirements: Platform Analytics and Reporting ==
'''Data requirements description'''

The system can create reports of long term business operations:
 * '''Monthly reports:''' Calculates total revenue and booking counts per sitter, per month.
 * '''Top Rated Sitters:''' Calculates average ratings and total completed jobs.

=== '''Implementation'''
'''Views'''
{{{
#!sql
CREATE OR REPLACE VIEW v_monthly_sitter_reports AS
SELECT 
  u.user_id AS sitter_id,
  u.first_name,
  u.last_name,
  date_trunc('month', b.date_to) AS month,
  COUNT(b.booking_id) AS total_completed_jobs,
  SUM(p.amount) AS total_revenue
FROM users u
JOIN bookings b ON u.user_id = b.sitter_id
JOIN payments p ON b.booking_id = p.booking_id
WHERE b.status = 'Completed'
GROUP BY u.user_id, u.first_name, u.last_name, date_trunc('month', b.date_to)
ORDER BY month DESC, total_revenue DESC;

CREATE OR REPLACE VIEW v_top_rated_sitters AS
SELECT 
  u.user_id AS sitter_id,
  u.first_name,
  u.last_name,
  COUNT(r.review_id) AS total_reviews,
  ROUND(AVG(r.rating)::numeric, 2) AS average_rating
FROM users u
JOIN bookings b ON u.user_id = b.sitter_id
JOIN reviews r ON b.booking_id = r.booking_id
GROUP BY u.user_id, u.first_name, u.last_name
HAVING COUNT(r.review_id) > 0
ORDER BY average_rating DESC, total_reviews DESC;
}}}

----

== 4. Data constraints requirements: Background Jobs (Automated System Cleanup) ==
'''Data requirements description'''

Rules for pending bookings that must be enforced asynchronously by the database server to keep the platform clean:
 * If a booking request remains 'Pending' and the {{{date_from}}} passes, it should automatically be marked as 'Expired'.
 * Runs automatically every hour

=== '''Implementation'''
'''Stored procedures'''
{{{
#!sql
-- Enable pg cronjob
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Stored procedure for clean up expired bookings
CREATE OR REPLACE PROCEDURE petsitter_job_expire_pending_bookings()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE bookings
  SET status = 'Expired'
  WHERE status = 'Pending'
    AND date_from < now();
END;
$$;
}}}

'''Views'''
{{{
CREATE OR REPLACE VIEW v_expired_pending_bookings AS
SELECT *
FROM bookings
WHERE status = 'Expired';
}}}

'''Cronjob'''
{{{
-- Schedule the job to run every hour
SELECT cron.schedule('petsitter_expire_bookings', '0 * * * *', $$CALL petsitter_job_expire_pending_bookings();$$);
}}}