| 3 | | == 1. Prevent Overlapping Reservations == |
| 4 | | |
| 5 | | 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. |
| | 3 | == 1. Prevent Overlapping Reservations (Full and Partial) == |
| | 4 | |
| | 5 | 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: |
| | 6 | |
| | 7 | * '''Full overlap (exact or containing):''' the new reservation fully contains the existing one, or the existing one fully contains the new one. |
| | 8 | * '''Partial overlap on the left:''' the new reservation starts before the existing one and ends inside it. |
| | 9 | * '''Partial overlap on the right:''' the new reservation starts inside the existing one and ends after it. |
| | 10 | * '''Exact match:''' both reservations have identical start and end times. |
| | 11 | |
| | 12 | 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. |
| | 13 | |
| | 14 | This cannot be enforced with a UNIQUE constraint because UNIQUE compares equal values only; it cannot test interval overlap across rows. |
| 17 | | IF EXISTS ( |
| 18 | | SELECT 1 FROM reservations r |
| 19 | | WHERE r.resource_id = NEW.resource_id |
| 20 | | AND r.reservation_id != NEW.reservation_id |
| 21 | | AND r.status IN ('approved', 'pending', 'completed') |
| 22 | | AND r.start_time < NEW.end_time |
| 23 | | AND r.end_time > NEW.start_time |
| 24 | | ) THEN |
| 25 | | RAISE EXCEPTION 'Reservation overlaps with an existing reservation for resource_id %', |
| 26 | | NEW.resource_id; |
| | 31 | -- Detect any active reservation on the same resource whose interval |
| | 32 | -- overlaps with the new reservation (full, exact, or partial). |
| | 33 | SELECT r.reservation_id, r.start_time, r.end_time |
| | 34 | INTO v_conflict_id, v_conflict_start, v_conflict_end |
| | 35 | FROM reservations r |
| | 36 | WHERE r.resource_id = NEW.resource_id |
| | 37 | AND r.reservation_id != NEW.reservation_id |
| | 38 | AND r.status IN ('approved', 'pending', 'completed') |
| | 39 | AND ( |
| | 40 | -- canonical half-open overlap test (catches all 4 overlap cases) |
| | 41 | (r.start_time < NEW.end_time AND r.end_time > NEW.start_time) |
| | 42 | OR -- explicit: new fully contains existing |
| | 43 | (NEW.start_time <= r.start_time AND NEW.end_time >= r.end_time) |
| | 44 | OR -- explicit: existing fully contains new |
| | 45 | (r.start_time <= NEW.start_time AND r.end_time >= NEW.end_time) |
| | 46 | OR -- explicit: partial overlap on the left side of new |
| | 47 | (NEW.start_time < r.start_time |
| | 48 | AND NEW.end_time > r.start_time |
| | 49 | AND NEW.end_time <= r.end_time) |
| | 50 | OR -- explicit: partial overlap on the right side of new |
| | 51 | (NEW.start_time >= r.start_time |
| | 52 | AND NEW.start_time < r.end_time |
| | 53 | AND NEW.end_time > r.end_time) |
| | 54 | ) |
| | 55 | LIMIT 1; |
| | 56 | |
| | 57 | IF v_conflict_id IS NOT NULL THEN |
| | 58 | RAISE EXCEPTION |
| | 59 | 'Reservation overlaps with existing reservation #% (% - %) on resource_id %', |
| | 60 | v_conflict_id, v_conflict_start, v_conflict_end, NEW.resource_id; |
| 39 | | 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. |
| | 73 | 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. |
| | 74 | |
| | 75 | '''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. |
