# Phase 3E — Recurring Schedule Exceptions & Single-Date Override Design

## 1. Executive Summary
Phase 3E introduces the ability to change a single occurrence of a recurring schedule without destructively modifying or splitting the base pattern. This design specifies a dedicated exception data model and a centralized resolution strategy to guarantee that Scheduling, Attendance, and Dashboard modules all agree on the effective shift for any given date.

## 2. Current Recurring-Schedule Problem
Currently, APIs query \employee_shift_schedule\ and apply YYYY-MM-DD and days-of-week logic to determine if an employee is working. If HR needs to change a single Wednesday shift to an Evening shift, they must either permanently deactivate the base schedule and create fragmented new schedules, or rely on dangerous priority hacks with overlapping records. Both approaches degrade the integrity of the recurring pattern.

## 3. Repository Consumer Audit
* **Scheduling (Grid, Range APIs)**: \get_employee_schedules.php\, \
ead-schedules-range.php\, and \get_employee_schedule.php\ directly query the table. *Must understand exceptions.*
* **Attendance**: \update_attendance.php\ and \get_attendance_rules.php\ use helpers like \getEmployeeShift()\ to fetch the expected shift. *Must understand exceptions to accurately compute lates/undertime.*
* **Payroll**: Primarily indirect (via Attendance). However, \dtr_summary.php\ directly queries schedules (though it appears retired). *Must use centralized resolver if active.*
* **Leave/Holiday**: Indirect. Leave/Holiday rules act as overlays and do not directly read schedules for base creation. *Not directly affected.*

## 4. Current Effective-Schedule Resolution
The HRIS resolves schedules ad-hoc across different endpoints. For example, \getEmployeeShift()\ in attendance executes a direct \SELECT\ with days-of-week matching in PHP. There is no single canonical \
esolveEffectiveSchedule(emp, date)\ helper, meaning exception logic could easily become fragmented if not centralized.

## 5. Data Model Options
* **Option A (Dedicated Exception Table)**: Leaves base schedule pristine. Links exceptions via \schedule_id\ + \exception_date\.
* **Option B (Split Recurring Record)**: Destroys the base pattern, making future edits to the \
series\ impossible.
* **Option C (Priority Overlaps)**: Creates overlapping records in the same table, making it difficult to distinguish a true new schedule from a 1-day override.

## 6. Recommended Exception Model
**Option A** is strongly recommended. 
Table: \employee_shift_schedule_exceptions\
- \exception_id\ (PK)
- \schedule_id\ (FK to base \employee_shift_schedule\)
- \exception_date\ (DATE, the exact occurrence being overridden)
- \
eplacement_work_time_id\ (FK to \work_time\)
- \
eason\ (TEXT, mandatory)
- \is_active\ (TINYINT)
- \created_at\, \created_by\, etc.
*Constraint*: \UNIQUE(schedule_id, exception_date)\

## 7. Exception Precedence
1. Find applicable active base schedule for the date.
2. Verify the date is an actual occurrence of the base schedule.
3. Check \employee_shift_schedule_exceptions\ for an active override.
4. If exists: \effective_shift = replacement_work_time_id\.
5. Else: \effective_shift = base_work_time_id\.

## 8. Conflict Validation Design
When validating a new exception for Date X on Base Schedule Y, the \detectScheduleConflicts()\ helper must be updated to logically *suppress* the occurrence of Base Schedule Y on Date X, preventing a false-positive self-conflict. It must continue to check all other active schedules (Base Z) to ensure the new replacement shift doesn't overlap with an unrelated schedule.

## 9. Locking / Concurrency
Exception creation will reuse the Phase 3D serialization model:
\BEGIN -\> Lock employee FOR UPDATE -\> Verify occurrence -\> Validate conflicts -\> Insert Exception -\> COMMIT\.

## 10. Attendance Impact
CRITICAL. \getEmployeeShift()\ in \ackend/attendance/update_attendance.php\ must be updated to left-join or separately query the exceptions table, returning the \
eplacement_work_time_id\ (and joining the new \work_time\ row) whenever an active exception matches the \ttendance_date\.

## 11. DTR / Late / Undertime Impact
By updating \getEmployeeShift()\, the downstream DTR calculations will automatically use the expected start/end times of the *replacement* shift. No core math changes are needed in the late/undertime algorithms as long as the correct shift is supplied.

## 12. Payroll Impact
Indirect. Payroll relies on DTR. Exception logic ensures DTR is calculated correctly against the override shift. 

## 13. Leave / Holiday Impact
Leaves and Holidays remain context overlays. If an exception changes Aug 19 to Evening, and an Approved Leave exists for Aug 19, the UI and Payroll will treat it as Leave applied to an Evening shift.

## 14. Dashboard Impact
\get_employee_schedules.php\ must retrieve exceptions and attach them to the schedule data. The React grid will detect the override and render an \Override\ badge or tooltip showing both the base shift and the replacement shift.

## 15. Export Impact
Exports must output the *effective* shift. If a centralized PHP resolver is used, exports will naturally inherit the exception shift.

## 16. Notification Impact
When an exception is created, a targeted notification can alert the employee: \Your
schedule
for
[Date]
has
been
changed
to
[New
Shift].\

## 17. API Design
- \POST /api/schedule-manager/exceptions/create.php\
- \POST /api/schedule-manager/exceptions/deactivate.php\ (revert to base)
- Existing read APIs updated to include exception arrays.

## 18. Query / Performance Design
Indexes on \employee_shift_schedule_exceptions\:
- \(schedule_id, exception_date)\
- \(exception_date, is_active)\
Range queries in the dashboard can efficiently left-join exceptions or fetch them via \WHERE schedule_id IN (...)\.

## 19. Tenant Isolation
Exceptions implicitly inherit tenant boundaries through their parent \schedule_id\. Standard \dmin_request_require_employee_scope()\ will be enforced during creation.

## 20. Legacy Replace Retirement Plan
Once Exceptions are stable, the dangerous \Replace
Overlapping
Dates\ fallback in the Advanced Assignment Wizard can be disabled, as single-date overrides will satisfy the primary use case for replacement.

## 21. Migration / Rollback
New exception-table schema migration is required; no migration of existing schedule records is required. The new table starts empty. Legacy schedules operate identically. If rollback is needed, simply ignoring the exception table restores legacy behavior.

## 22. Risks
- Fragmentation: If any endpoint misses the exception table, it will calculate attendance based on the wrong shift.
- Orphaned Exceptions: If a base schedule is shortened (e.g. \end_date\ moved up), exceptions beyond the new end date become orphaned. The API must enforce ignoring exceptions if the base occurrence no longer exists.

## 23. Open Decisions
- *Canonical Resolver*: Should we fully rewrite all schedule reads to use a single new \
esolveEffectiveSchedule()\ helper in this phase, or just patch the 3-4 existing read endpoints individually? (Recommendation: Patch existing endpoints first to minimize regression, then centralize in a refactoring phase).

## 24. Phase 3E Implementation Plan
1. **Schema**: Create \employee_shift_schedule_exceptions\ table.
2. **Backend Read APIs**: Update Dashboard read endpoints to return exceptions.
3. **Backend Attendance**: Update \getEmployeeShift()\ to resolve exceptions.
4. **Backend Conflict Logic**: Update \detectScheduleConflicts()\ to suppress overridden base occurrences and respect exceptions.
5. **Backend Write APIs**: Implement exception create/deactivate endpoints.
6. **Frontend**: Update Grid cell rendering (Override badge/tooltip).
7. **Frontend**: Implement \Change
This
Date\ vs \Manage
Recurring
Schedule\ UX.

## 25. Acceptance Checklist
- Base schedule remains untouched when exception is created.
- Attendance strictly uses exception shift times.
- Conflict validation ignores the replaced base occurrence.
- Grid clearly identifies the exception visually.