Advanced Database Development

1. Prevent Overlapping Reservations (Full and Partial)

A resource cannot have two active reservations (approved, pending, or completed) whose time ranges overlap in any way. The rule must catch every kind of overlap, not only fully-matching ones:

• Full overlap (exact or containing): the new reservation fully contains the existing one, or the existing one fully contains the new one.
• Partial overlap on the left: the new reservation starts before the existing one and ends inside it.
• Partial overlap on the right: the new reservation starts inside the existing one and ends after it.
• Exact match: both reservations have identical start and end times.

Reservations that only touch at a single endpoint (e.g. 09:00-11:00 followed by 11:00-13:00) are not treated as overlapping — they are allowed.

This cannot be enforced with a UNIQUE constraint because UNIQUE compares equal values only; it cannot test interval overlap across rows.

Implementation: Trigger

CREATE OR REPLACE FUNCTION check_reservation_overlap()
RETURNS TRIGGER AS $$
DECLARE
    v_conflict_id INT;
    v_conflict_start TIMESTAMP;
    v_conflict_end TIMESTAMP;
BEGIN
    -- Cancelled or rejected reservations no longer occupy the resource.
    IF NEW.status IN ('cancelled', 'rejected') THEN
        RETURN NEW;
    END IF;

    -- Detect any active reservation on the same resource whose interval
    -- overlaps with the new reservation (full, exact, or partial).
    SELECT r.reservation_id, r.start_time, r.end_time
      INTO v_conflict_id, v_conflict_start, v_conflict_end
    FROM reservations r
    WHERE r.resource_id = NEW.resource_id
      AND r.reservation_id != NEW.reservation_id
      AND r.status IN ('approved', 'pending', 'completed')
      AND (
           -- canonical half-open overlap test (catches all 4 overlap cases)
           (r.start_time < NEW.end_time AND r.end_time > NEW.start_time)
        OR -- explicit: new fully contains existing
           (NEW.start_time <= r.start_time AND NEW.end_time >= r.end_time)
        OR -- explicit: existing fully contains new
           (r.start_time <= NEW.start_time AND r.end_time >= NEW.end_time)
        OR -- explicit: partial overlap on the left side of new
           (NEW.start_time < r.start_time
            AND NEW.end_time > r.start_time
            AND NEW.end_time <= r.end_time)
        OR -- explicit: partial overlap on the right side of new
           (NEW.start_time >= r.start_time
            AND NEW.start_time < r.end_time
            AND NEW.end_time > r.end_time)
      )
    LIMIT 1;

    IF v_conflict_id IS NOT NULL THEN
        RAISE EXCEPTION
            'Reservation overlaps with existing reservation #% (% - %) on resource_id %',
            v_conflict_id, v_conflict_start, v_conflict_end, NEW.resource_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_reservation_overlap
    BEFORE INSERT OR UPDATE ON reservations
    FOR EACH ROW
    EXECUTE FUNCTION check_reservation_overlap();

The trigger fires before every INSERT or UPDATE on reservations. Cancelled and rejected reservations are excluded from the check (both as subject and as comparison rows) because they no longer hold the resource. When a conflict is found, the exception message includes the conflicting reservation's ID and its interval so the application can show it to the user.

Overlap cases verified by test: new contains existing, existing contains new, exact match, partial-left, partial-right are all rejected; adjacent intervals that share a single endpoint are allowed; non-overlapping intervals are allowed.

2. Enforce Resource Availability Window

Reservations must respect the resource's daily availability hours (available_from / available_to) and weekend restrictions (available_weekends). For example, a classroom available 08:00-20:00 on weekdays should reject a reservation starting at 07:00 or on a Saturday.

Implementation: Trigger

CREATE OR REPLACE FUNCTION check_availability_window()
RETURNS TRIGGER AS $$
DECLARE
    v_resource RECORD;
BEGIN
    IF NEW.status IN ('cancelled', 'rejected') THEN
        RETURN NEW;
    END IF;

    SELECT available_from, available_to, available_weekends
    INTO v_resource
    FROM resources
    WHERE resource_id = NEW.resource_id;

    IF NOT v_resource.available_weekends
       AND EXTRACT(ISODOW FROM NEW.start_time) > 5 THEN
        RAISE EXCEPTION 'Resource (id: %) is not available on weekends', NEW.resource_id;
    END IF;

    IF v_resource.available_from != TIME '00:00'
       OR v_resource.available_to != TIME '23:59' THEN
        IF NEW.start_time::TIME < v_resource.available_from THEN
            RAISE EXCEPTION 'Reservation starts at % which is before resource availability at %',
                NEW.start_time::TIME, v_resource.available_from;
        END IF;
        IF NEW.start_time::DATE = NEW.end_time::DATE
           AND NEW.end_time::TIME > v_resource.available_to THEN
            RAISE EXCEPTION 'Reservation ends at % which is after resource availability at %',
                NEW.end_time::TIME, v_resource.available_to;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_availability_window
    BEFORE INSERT OR UPDATE ON reservations
    FOR EACH ROW
    EXECUTE FUNCTION check_availability_window();

The trigger looks up the resource's availability settings and validates the reservation times. Digital resources with 24-hour availability (00:00-23:59) skip the time-of-day check. The end-time check only applies to same-day reservations to correctly handle multi-day digital resource allocations (e.g., semester-long software licenses).

3. Only Administrators Can Approve Reservations

The approved_by field must reference a user with the Administrator role. This rule goes beyond basic foreign key integrity — it requires a cross-table role check that cannot be expressed as a simple FK constraint.

Implementation: Trigger

CREATE OR REPLACE FUNCTION check_approver_is_admin()
RETURNS TRIGGER AS $$
DECLARE
    v_type_name VARCHAR;
BEGIN
    IF NEW.approved_by IS NOT NULL THEN
        SELECT ut.type_name INTO v_type_name
        FROM users u
        JOIN user_types ut ON u.type_id = ut.type_id
        WHERE u.user_id = NEW.approved_by;

        IF v_type_name IS NULL THEN
            RAISE EXCEPTION 'Approver user_id % does not exist', NEW.approved_by;
        END IF;

        IF v_type_name != 'Administrator' THEN
            RAISE EXCEPTION 'Only administrators can approve reservations. User % is "%"',
                NEW.approved_by, v_type_name;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_approver_is_admin
    BEFORE INSERT OR UPDATE ON reservations
    FOR EACH ROW
    EXECUTE FUNCTION check_approver_is_admin();

The trigger validates on every INSERT and UPDATE that if approved_by is set, the referenced user has the 'Administrator' type. This prevents students or teaching staff from being recorded as approvers.

4. Auto-Complete Past Approved Reservations

Approved reservations whose end time has passed should be automatically marked as completed. This procedure is intended to be called periodically (e.g., as a daily background job) to keep reservation statuses accurate without manual intervention.

Implementation: Stored Procedure

CREATE OR REPLACE PROCEDURE complete_past_reservations()
LANGUAGE plpgsql AS $$
DECLARE
    v_count INT;
BEGIN
    UPDATE reservations
    SET status = 'completed'
    WHERE status = 'approved'
      AND end_time < CURRENT_TIMESTAMP;

    GET DIAGNOSTICS v_count = ROW_COUNT;
    RAISE NOTICE 'Completed % past approved reservations', v_count;
END;
$$;

Usage: CALL complete_past_reservations();

5. Auto-Reject Expired Pending Reservations

Pending reservations whose start time has already passed without receiving approval should be automatically rejected. This prevents stale pending reservations from accumulating and keeps the system consistent.

Implementation: Stored Procedure

CREATE OR REPLACE PROCEDURE reject_expired_pending()
LANGUAGE plpgsql AS $$
DECLARE
    v_count INT;
BEGIN
    UPDATE reservations
    SET status = 'rejected'
    WHERE status = 'pending'
      AND start_time < CURRENT_TIMESTAMP;

    GET DIAGNOSTICS v_count = ROW_COUNT;
    RAISE NOTICE 'Rejected % expired pending reservations', v_count;
END;
$$;

Usage: CALL reject_expired_pending();

6. Resource Utilization Dashboard

An aggregated view showing reservation counts and total reserved hours per resource, broken down by status. Provides a quick overview for administrators to identify high-demand and underutilized resources without writing complex queries.

Implementation: View

CREATE OR REPLACE VIEW resource_utilization_dashboard AS
SELECT
    r.resource_id,
    r.name AS resource_name,
    rt.type_name AS resource_type,
    COALESCE(l.building || ' ' || l.room, 'Digital') AS location,
    COUNT(rv.reservation_id) AS total_reservations,
    COUNT(rv.reservation_id) FILTER (WHERE rv.status IN ('approved', 'completed'))
        AS approved_count,
    COUNT(rv.reservation_id) FILTER (WHERE rv.status = 'rejected')
        AS rejected_count,
    COUNT(rv.reservation_id) FILTER (WHERE rv.status = 'cancelled')
        AS cancelled_count,
    COUNT(rv.reservation_id) FILTER (WHERE rv.status = 'pending')
        AS pending_count,
    COALESCE(ROUND(SUM(EXTRACT(EPOCH FROM (rv.end_time - rv.start_time)) / 3600.0)
        FILTER (WHERE rv.status IN ('approved', 'completed')), 1), 0)
        AS total_reserved_hours
FROM resources r
JOIN resource_types rt ON r.type_id = rt.type_id
LEFT JOIN locations l ON r.location_id = l.location_id
LEFT JOIN reservations rv ON r.resource_id = rv.resource_id
GROUP BY r.resource_id, r.name, rt.type_name, l.building, l.room;

Usage: SELECT * FROM resource_utilization_dashboard ORDER BY total_reservations DESC;

7. Pending Reservations Overview

A view listing all pending reservations with requester information and how many days each has been waiting for approval. Helps administrators prioritize which reservations to review first and identify bottlenecks in the approval process.

Implementation: View

CREATE OR REPLACE VIEW pending_reservations_overview AS
SELECT
    rv.reservation_id,
    u.first_name || ' ' || u.last_name AS requested_by,
    ut.type_name AS user_type,
    r.name AS resource_name,
    rv.start_time,
    rv.end_time,
    rv.purpose,
    rv.created_at,
    ROUND(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rv.created_at)) / 86400.0, 1)
        AS waiting_days
FROM reservations rv
JOIN users u ON rv.user_id = u.user_id
JOIN user_types ut ON u.type_id = ut.type_id
JOIN resources r ON rv.resource_id = r.resource_id
WHERE rv.status = 'pending'
ORDER BY rv.created_at;

Usage: SELECT * FROM pending_reservations_overview;