# Phase 3E.2 — Recurring Schedule Exception Backend

## 1. Migration
A new migration \ackend/migrations/20260807_001_create_employee_shift_schedule_exceptions.php\ was created and applied successfully using the project's native \migration_runner.php\.

**Table created:**
\employee_shift_schedule_exceptions\
- \exception_id\ INT(11) AUTO_INCREMENT PK
- \schedule_id\ INT(11) FK to \employee_shift_schedule\ (ON DELETE RESTRICT ON UPDATE CASCADE)
- \exception_date\ DATE
- \
eplacement_work_time_id\ INT(11) FK to \work_time\ (ON DELETE RESTRICT ON UPDATE CASCADE)
- \
eason\ TEXT
- \is_active\ TINYINT(1) DEFAULT 1
- \created_at\, \created_by\, \updated_at\, \updated_by\
- \UNIQUE KEY uq_schedule_date (schedule_id, exception_date)\

**No existing schedule records were modified or migrated.**

## 2. Exception Table Lifecycle
- **First override:** \INSERT\
- **Change override:** \ON DUPLICATE KEY UPDATE\ (updates same row)
- **Revert:** \is_active = 0\
- **Override same date again:** Reactivates same row (\is_active = 1\)

## 3. Canonical Resolver Precedence
\ackend/schedule-manager/helpers/effective_schedule_helper.php\ was updated.
\
esolveEffectiveSchedule()\ now seamlessly integrates exceptions:
1. Finds the winning active base schedule.
2. Queries the \employee_shift_schedule_exceptions\ table for the matching \schedule_id\ + \date\.
3. If an active exception exists, replaces the returned \work_time\ payload with the replacement shift.
4. Existing legacy consumers (e.g. \update_attendance.php\) receive the effective shift array seamlessly without knowing it's an exception, preserving all late/undertime formulas.

## 4. Conflict Validation (\conflict_helper.php\)
The shared \detectScheduleConflicts()\ function was upgraded:
- Added \\\ and \\\ to safely suppress a parent schedule's occurrence when validating its own replacement shift.
- **Exception Awareness for Range Validations:** When an endpoint creates a new recurring schedule, the conflict helper natively queries \employee_shift_schedule_exceptions\ to check if the new schedule overlaps with any effective exception shifts within the date range.
- **Exception Awareness for Single-Date Validations:** When creating a single-date schedule (or another exception), the helper evaluates other active base schedules. If another base schedule has an active exception on that date, it uses the exception's shift time for overlap comparison instead of the base shift.
- **No Regressions:** All existing Final-Schedule Writers automatically inherit this protection without modification.

## 5. Exception API Endpoints
**\ackend/schedule-manager/exceptions/create.php\**
- Strict transaction ordering and validation.
- Requires \schedules.manage\ and validates tenant scope.
- Enforces employee \FOR UPDATE\ lock.
- Verifies the requested date is a valid base occurrence using \scheduleOccursOnDate()\.
- Rejects past dates, invalid replacement shifts, and empty reasons.
- Runs exception-aware conflict validation.
- Performs an UPSERT operation.

**\ackend/schedule-manager/exceptions/revert.php\**
- Strict transaction ordering and scope validation.
- Enforces employee \FOR UPDATE\ lock.
- Evaluates whether the restored base occurrence conflicts with any current effective schedules that might have been added while the exception was active.
- Safely sets \is_active = 0\.

## 6. Parent Inactive Behavior
If a parent schedule is deactivated or its range changed so the exception date no longer falls within it, the canonical resolver naturally ignores the exception because \scheduleOccursOnDate()\ evaluates the parent first. Orphaned exceptions remain dormant safely.

## 7. Known Legacy Behaviors Preserved
- \
ecurrence_interval\ remains ignored by the occurrence algorithm.
- Legacy monthly semantics (day-of-month matching) remain intact.
- Exact priority ties resolve to the latest \effective_date\ and then fall back to query order.

## 8. Readiness
Phase 3E.2 backend is complete and thoroughly unit-tested. Phase 3E.3 (Dashboard Rendering & Range Resolvers) is READY.