Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
100.00%
174 / 174
100.00%
12 / 12
 CRAP
100.00%
1 / 1
ShiftRepository
100.00%
174 / 174
100.00%
12 / 12
 38
100.00%
1 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 findById
100.00%
11 / 11
100.00%
1 / 1
 3
 findByDateRange
100.00%
9 / 9
100.00%
1 / 1
 2
 findByEmployeeAndDateRange
100.00%
10 / 10
100.00%
1 / 1
 2
 checkOverlap
100.00%
13 / 13
100.00%
1 / 1
 3
 create
100.00%
15 / 15
100.00%
1 / 1
 3
 update
100.00%
36 / 36
100.00%
1 / 1
 11
 softDelete
100.00%
5 / 5
100.00%
1 / 1
 1
 copyWeek
100.00%
42 / 42
100.00%
1 / 1
 5
 getCopyPreview
100.00%
19 / 19
100.00%
1 / 1
 4
 deleteOverlappingShifts
100.00%
7 / 7
100.00%
1 / 1
 1
 getWeeklyScheduledHours
100.00%
6 / 6
100.00%
1 / 1
 2
1<?php
2
3namespace BuyerKiosk\Scheduling\Repositories;
4
5use BuyerKiosk\Scheduling\Models\Shift;
6use DateTime;
7use PDO;
8
9/**
10 * ShiftRepository
11 *
12 * Data access layer for schedule shifts.
13 * Handles CRUD operations, overlap detection, and week copy functionality.
14 *
15 * @package BuyerKiosk\Scheduling\Repositories
16 */
17class ShiftRepository
18{
19    private PDO $db;
20
21    public function __construct(PDO $db)
22    {
23        $this->db = $db;
24    }
25
26    /**
27     * Find a shift by ID
28     *
29     * @param int $shiftId Shift ID
30     * @param bool $includeDeleted Include soft-deleted shifts
31     * @return Shift|null
32     */
33    public function findById(int $shiftId, bool $includeDeleted = false): ?Shift
34    {
35        $sql = "
36            SELECT
37                s.*,
38                u.firstName AS employeeFirstName,
39                u.lastName AS employeeLastName,
40                p.name AS positionName,
41                p.color AS positionColor
42            FROM scheduleShifts s
43            INNER JOIN kiosk_users.users u ON s.employeeId = u.id
44            LEFT JOIN schedulePositions p ON s.positionId = p.positionId
45            WHERE s.shiftId = :shiftId
46        ";
47
48        if (!$includeDeleted) {
49            $sql .= " AND s.deleted_at IS NULL";
50        }
51
52        $stmt = $this->db->prepare($sql);
53        $stmt->bindValue(':shiftId', $shiftId, PDO::PARAM_INT);
54        $stmt->execute();
55
56        $row = $stmt->fetch(PDO::FETCH_ASSOC);
57        if (!$row) {
58            return null;
59        }
60
61        return Shift::fromRow($row);
62    }
63
64    /**
65     * Find shifts within a date range
66     *
67     * @param DateTime $start Range start (inclusive)
68     * @param DateTime $end Range end (exclusive)
69     * @return Shift[]
70     */
71    public function findByDateRange(DateTime $start, DateTime $end): array
72    {
73        $stmt = $this->db->prepare("
74            SELECT
75                s.*,
76                u.firstName AS employeeFirstName,
77                u.lastName AS employeeLastName,
78                p.name AS positionName,
79                p.color AS positionColor
80            FROM scheduleShifts s
81            INNER JOIN kiosk_users.users u ON s.employeeId = u.id
82            LEFT JOIN schedulePositions p ON s.positionId = p.positionId
83            WHERE s.shiftStart >= :start
84              AND s.shiftStart < :end
85              AND s.deleted_at IS NULL
86              AND u.enabled = 1
87            ORDER BY s.shiftStart ASC, u.lastName ASC
88        ");
89        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
90        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
91        $stmt->execute();
92
93        $shifts = [];
94        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
95            $shifts[] = Shift::fromRow($row);
96        }
97
98        return $shifts;
99    }
100
101    /**
102     * Find shifts for a specific employee within a date range
103     *
104     * @param int $employeeId Employee ID
105     * @param DateTime $start Range start
106     * @param DateTime $end Range end
107     * @return Shift[]
108     */
109    public function findByEmployeeAndDateRange(int $employeeId, DateTime $start, DateTime $end): array
110    {
111        $stmt = $this->db->prepare("
112            SELECT
113                s.*,
114                u.firstName AS employeeFirstName,
115                u.lastName AS employeeLastName,
116                p.name AS positionName,
117                p.color AS positionColor
118            FROM scheduleShifts s
119            INNER JOIN kiosk_users.users u ON s.employeeId = u.id
120            LEFT JOIN schedulePositions p ON s.positionId = p.positionId
121            WHERE s.employeeId = :employeeId
122              AND s.shiftStart >= :start
123              AND s.shiftStart < :end
124              AND s.deleted_at IS NULL
125            ORDER BY s.shiftStart ASC
126        ");
127        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
128        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
129        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
130        $stmt->execute();
131
132        $shifts = [];
133        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
134            $shifts[] = Shift::fromRow($row);
135        }
136
137        return $shifts;
138    }
139
140    /**
141     * Check if an employee has an overlapping shift
142     *
143     * @param int $employeeId Employee ID
144     * @param DateTime $start Shift start time
145     * @param DateTime $end Shift end time
146     * @param int|null $excludeShiftId Shift ID to exclude from check (for updates)
147     * @return bool True if overlap exists
148     */
149    public function checkOverlap(int $employeeId, DateTime $start, DateTime $end, ?int $excludeShiftId = null): bool
150    {
151        $sql = "
152            SELECT COUNT(*) as overlap_count
153            FROM scheduleShifts
154            WHERE employeeId = :employeeId
155              AND deleted_at IS NULL
156              AND shiftStart < :end
157              AND shiftEnd > :start
158        ";
159
160        if ($excludeShiftId !== null) {
161            $sql .= " AND shiftId != :excludeShiftId";
162        }
163
164        $stmt = $this->db->prepare($sql);
165        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
166        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
167        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
168
169        if ($excludeShiftId !== null) {
170            $stmt->bindValue(':excludeShiftId', $excludeShiftId, PDO::PARAM_INT);
171        }
172
173        $stmt->execute();
174        $result = $stmt->fetch(PDO::FETCH_ASSOC);
175
176        return (int)$result['overlap_count'] > 0;
177    }
178
179    /**
180     * Create a new shift
181     *
182     * @param Shift $shift Shift to create
183     * @return Shift Created shift with ID
184     * @throws \RuntimeException If shift overlaps with existing shift
185     */
186    public function create(Shift $shift): Shift
187    {
188        // Check for overlap before creating
189        if ($this->checkOverlap($shift->getEmployeeId(), $shift->getShiftStart(), $shift->getShiftEnd())) {
190            throw new \RuntimeException('OVERLAP: Employee already has a shift during this time');
191        }
192
193        $data = $shift->toDbArray();
194
195        $stmt = $this->db->prepare("
196            INSERT INTO scheduleShifts (employeeId, shiftStart, shiftEnd, positionId, notes, createdByUserId)
197            VALUES (:employeeId, :shiftStart, :shiftEnd, :positionId, :notes, :createdByUserId)
198        ");
199
200        $stmt->bindValue(':employeeId', $data['employeeId'], PDO::PARAM_INT);
201        $stmt->bindValue(':shiftStart', $data['shiftStart']);
202        $stmt->bindValue(':shiftEnd', $data['shiftEnd']);
203        $stmt->bindValue(':positionId', $data['positionId'], $data['positionId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
204        $stmt->bindValue(':notes', $data['notes']);
205        $stmt->bindValue(':createdByUserId', $data['createdByUserId'], PDO::PARAM_INT);
206
207        $stmt->execute();
208
209        $shiftId = (int)$this->db->lastInsertId();
210        $shift->setShiftId($shiftId);
211
212        // Return fresh copy with all hydrated fields
213        return $this->findById($shiftId) ?? $shift;
214    }
215
216    /**
217     * Update an existing shift
218     *
219     * @param Shift $shift Shift to update
220     * @param DateTime|null $expectedUpdatedAt For optimistic concurrency check
221     * @return Shift Updated shift
222     * @throws \RuntimeException If overlap or stale write detected
223     */
224    public function update(Shift $shift, ?DateTime $expectedUpdatedAt = null): Shift
225    {
226        if ($shift->getShiftId() === null) {
227            throw new \InvalidArgumentException('Cannot update shift without shiftId');
228        }
229
230        // Check for overlap (excluding this shift)
231        if ($this->checkOverlap(
232            $shift->getEmployeeId(),
233            $shift->getShiftStart(),
234            $shift->getShiftEnd(),
235            $shift->getShiftId()
236        )) {
237            throw new \RuntimeException('OVERLAP: Employee already has a shift during this time');
238        }
239
240        $data = $shift->toDbArray();
241
242        $sql = "
243            UPDATE scheduleShifts
244            SET employeeId = :employeeId,
245                shiftStart = :shiftStart,
246                shiftEnd = :shiftEnd,
247                positionId = :positionId,
248                notes = :notes
249            WHERE shiftId = :shiftId
250              AND deleted_at IS NULL
251        ";
252
253        if ($expectedUpdatedAt !== null) {
254            $sql .= " AND updated_at = :expectedUpdatedAt";
255        }
256
257        $stmt = $this->db->prepare($sql);
258
259        $stmt->bindValue(':shiftId', $shift->getShiftId(), PDO::PARAM_INT);
260        $stmt->bindValue(':employeeId', $data['employeeId'], PDO::PARAM_INT);
261        $stmt->bindValue(':shiftStart', $data['shiftStart']);
262        $stmt->bindValue(':shiftEnd', $data['shiftEnd']);
263        $stmt->bindValue(':positionId', $data['positionId'], $data['positionId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
264        $stmt->bindValue(':notes', $data['notes']);
265
266        if ($expectedUpdatedAt !== null) {
267            $stmt->bindValue(':expectedUpdatedAt', $expectedUpdatedAt->format('Y-m-d H:i:s'));
268        }
269
270        $stmt->execute();
271
272        if ($stmt->rowCount() === 0) {
273            if ($expectedUpdatedAt !== null) {
274                $checkStmt = $this->db->prepare("
275                    SELECT updated_at, deleted_at
276                    FROM scheduleShifts
277                    WHERE shiftId = :shiftId
278                ");
279                $checkStmt->bindValue(':shiftId', $shift->getShiftId(), PDO::PARAM_INT);
280                $checkStmt->execute();
281                $row = $checkStmt->fetch(PDO::FETCH_ASSOC);
282
283                if (!$row || $row['deleted_at'] !== null) {
284                    throw new \RuntimeException('NOT_FOUND: Shift does not exist');
285                }
286
287                // MySQL may report 0 affected rows when values are unchanged.
288                // Treat this as success if the concurrency token still matches.
289                if (($row['updated_at'] ?? null) !== $expectedUpdatedAt->format('Y-m-d H:i:s')) {
290                    throw new \RuntimeException('STALE_WRITE: Shift changed since you loaded it');
291                }
292            }
293        }
294
295        return $this->findById($shift->getShiftId()) ?? $shift;
296    }
297
298    /**
299     * Soft delete a shift
300     *
301     * @param int $shiftId Shift ID to delete
302     * @return bool Success status
303     */
304    public function softDelete(int $shiftId): bool
305    {
306        $stmt = $this->db->prepare("
307            UPDATE scheduleShifts
308            SET deleted_at = NOW()
309            WHERE shiftId = :shiftId
310              AND deleted_at IS NULL
311        ");
312        $stmt->bindValue(':shiftId', $shiftId, PDO::PARAM_INT);
313        $stmt->execute();
314
315        return $stmt->rowCount() > 0;
316    }
317
318    /**
319     * Copy shifts from one week to another
320     *
321     * @param DateTime $sourceStart Source week start
322     * @param DateTime $targetStart Target week start
323     * @param bool $overwrite Whether to overwrite existing shifts in target week
324     * @param int $createdByUserId User ID performing the copy
325     * @return array{copied: int, skipped: int, conflicts: array} Copy results
326     */
327    public function copyWeek(
328        DateTime $sourceStart,
329        DateTime $targetStart,
330        bool $overwrite,
331        int $createdByUserId
332    ): array {
333        // Calculate week ends (7 days later)
334        $sourceEnd = (clone $sourceStart)->modify('+7 days');
335        $targetEnd = (clone $targetStart)->modify('+7 days');
336
337        // Calculate the day offset between source and target
338        $dayOffset = $sourceStart->diff($targetStart)->days;
339        $offsetDirection = $targetStart > $sourceStart ? '+' : '-';
340
341        // Get source shifts
342        $sourceShifts = $this->findByDateRange($sourceStart, $sourceEnd);
343
344        $copied = 0;
345        $skipped = 0;
346        $conflicts = [];
347
348        foreach ($sourceShifts as $sourceShift) {
349            // Calculate new times by applying the offset
350            $newStart = (clone $sourceShift->getShiftStart())->modify("{$offsetDirection}{$dayOffset} days");
351            $newEnd = (clone $sourceShift->getShiftEnd())->modify("{$offsetDirection}{$dayOffset} days");
352
353            // Check for existing conflict
354            $hasConflict = $this->checkOverlap(
355                $sourceShift->getEmployeeId(),
356                $newStart,
357                $newEnd
358            );
359
360            if ($hasConflict) {
361                if ($overwrite) {
362                    // Delete conflicting shifts for this employee in this time range
363                    $this->deleteOverlappingShifts($sourceShift->getEmployeeId(), $newStart, $newEnd);
364                } else {
365                    // Skip this shift
366                    $conflicts[] = [
367                        'employeeId' => $sourceShift->getEmployeeId(),
368                        'employeeName' => $sourceShift->getEmployeeName(),
369                        'originalStart' => $sourceShift->getShiftStart()->format('Y-m-d H:i'),
370                        'targetStart' => $newStart->format('Y-m-d H:i'),
371                    ];
372                    $skipped++;
373                    continue;
374                }
375            }
376
377            // Create the new shift
378            $newShift = new Shift(
379                $sourceShift->getEmployeeId(),
380                $newStart,
381                $newEnd,
382                $createdByUserId
383            );
384            $newShift->setPositionId($sourceShift->getPositionId());
385            $newShift->setNotes($sourceShift->getNotes());
386
387            $this->create($newShift);
388            $copied++;
389        }
390
391        return [
392            'copied' => $copied,
393            'skipped' => $skipped,
394            'conflicts' => $conflicts,
395        ];
396    }
397
398    /**
399     * Get copy preview (conflicts) without actually copying
400     *
401     * @param DateTime $sourceStart Source week start
402     * @param DateTime $targetStart Target week start
403     * @return array{shiftCount: int, conflicts: array}
404     */
405    public function getCopyPreview(DateTime $sourceStart, DateTime $targetStart): array
406    {
407        $sourceEnd = (clone $sourceStart)->modify('+7 days');
408        $dayOffset = $sourceStart->diff($targetStart)->days;
409        $offsetDirection = $targetStart > $sourceStart ? '+' : '-';
410
411        $sourceShifts = $this->findByDateRange($sourceStart, $sourceEnd);
412        $conflicts = [];
413
414        foreach ($sourceShifts as $sourceShift) {
415            $newStart = (clone $sourceShift->getShiftStart())->modify("{$offsetDirection}{$dayOffset} days");
416            $newEnd = (clone $sourceShift->getShiftEnd())->modify("{$offsetDirection}{$dayOffset} days");
417
418            if ($this->checkOverlap($sourceShift->getEmployeeId(), $newStart, $newEnd)) {
419                $conflicts[] = [
420                    'employeeId' => $sourceShift->getEmployeeId(),
421                    'employeeName' => $sourceShift->getEmployeeName(),
422                    'originalStart' => $sourceShift->getShiftStart()->format('Y-m-d H:i'),
423                    'targetStart' => $newStart->format('Y-m-d H:i'),
424                ];
425            }
426        }
427
428        return [
429            'shiftCount' => count($sourceShifts),
430            'conflicts' => $conflicts,
431        ];
432    }
433
434    /**
435     * Delete all overlapping shifts for an employee in a time range
436     *
437     * @param int $employeeId Employee ID
438     * @param DateTime $start Start time
439     * @param DateTime $end End time
440     * @return int Number of shifts deleted
441     */
442    private function deleteOverlappingShifts(int $employeeId, DateTime $start, DateTime $end): int
443    {
444        $stmt = $this->db->prepare("
445            UPDATE scheduleShifts
446            SET deleted_at = NOW()
447            WHERE employeeId = :employeeId
448              AND deleted_at IS NULL
449              AND shiftStart < :end
450              AND shiftEnd > :start
451        ");
452        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
453        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
454        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
455        $stmt->execute();
456
457        return $stmt->rowCount();
458    }
459
460    /**
461     * Get total scheduled hours for an employee in a week
462     *
463     * @param int $employeeId Employee ID
464     * @param DateTime $weekStart Start of week
465     * @return float Total hours
466     */
467    public function getWeeklyScheduledHours(int $employeeId, DateTime $weekStart): float
468    {
469        $weekEnd = (clone $weekStart)->modify('+7 days');
470
471        $shifts = $this->findByEmployeeAndDateRange($employeeId, $weekStart, $weekEnd);
472
473        $totalHours = 0;
474        foreach ($shifts as $shift) {
475            $totalHours += $shift->getDurationHours();
476        }
477
478        return $totalHours;
479    }
480}