Advanced Database Development

1. Prevent Overlapping Reservations

A resource cannot have two active reservations (approved, pending, or completed) that overlap in time. This business rule cannot be enforced with simple column-level constraints because it requires cross-row time-range overlap detection combined with status filtering.

Implementation: Trigger

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

    IF EXISTS (
        SELECT 1 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 r.start_time < NEW.end_time
          AND r.end_time > NEW.start_time
    ) THEN
        RAISE EXCEPTION 'Reservation overlaps with an existing reservation for resource_id %',
            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 the reservations table. It checks whether any existing active reservation (not cancelled or rejected) for the same resource has a time range that overlaps with the new reservation. Cancelled and rejected reservations are excluded from both the check and as subjects of the check, since they no longer occupy the resource.

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;