<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;

final class PoolGridService
{
    public static function getFacility(int $facilityId): ?array
    {
        $db = App::getInstance()->db();
        return $db->selectOne(
            "SELECT * FROM sa_facilities WHERE id = ? AND is_active = 1 AND is_archived = 0",
            [$facilityId]
        );
    }

    public static function getTimeSlots(array $facility): array
    {
        $hours = json_decode($facility['operating_hours_json'], true)
                 ?: ['start' => '06:00', 'end' => '22:00', 'slot_minutes' => 60];

        $slots = [];
        $current = strtotime($hours['start']);
        $endTs = strtotime($hours['end']);
        $minutes = (int) $hours['slot_minutes'];

        while ($current < $endTs) {
            $next = $current + ($minutes * 60);
            $slots[] = [
                'start' => date('H:i', $current),
                'end'   => date('H:i', $next),
            ];
            $current = $next;
        }

        return $slots;
    }

    public static function getGridState(int $facilityId, string $date, string $startTime, string $endTime): array
    {
        $db = App::getInstance()->db();

        $facility = self::getFacility($facilityId);
        if (!$facility) {
            return ['error' => 'المرفق غير موجود'];
        }

        $rows = (int) ($facility['pool_grid_rows'] ?? 0);
        $cols = (int) ($facility['pool_grid_cols'] ?? 0);

        if ($rows < 1 || $cols < 1) {
            return ['error' => 'لم يتم تهيئة شبكة الحمام بعد. يرجى ضبط عدد الصفوف والأعمدة.'];
        }

        $bookings = $db->select(
            "SELECT pzb.*, g.name_ar as group_name, g.code as group_code,
                    c.full_name_ar as coach_name
             FROM sa_pool_zone_bookings pzb
             LEFT JOIN sa_groups g ON g.id = pzb.group_id
             LEFT JOIN sa_coaches c ON c.id = pzb.coach_id
             WHERE pzb.facility_id = ? AND pzb.booking_date = ?
             AND pzb.start_time = ? AND pzb.status = 'active'",
            [$facilityId, $date, $startTime . ':00']
        );

        $grid = [];
        for ($r = 0; $r < $rows; $r++) {
            for ($c = 0; $c < $cols; $c++) {
                $grid[$r . ':' . $c] = [
                    'row'    => $r,
                    'col'    => $c,
                    'status' => 'free',
                    'label'  => '',
                    'type'   => null,
                    'group_id' => null,
                    'booking_id' => null,
                ];
            }
        }

        foreach ($bookings as $b) {
            $key = $b['zone_row'] . ':' . $b['zone_col'];
            if (isset($grid[$key])) {
                $grid[$key]['status'] = $b['assignment_type'];
                $grid[$key]['label'] = $b['label'] ?: ($b['group_name'] ?? '');
                $grid[$key]['type'] = $b['assignment_type'];
                $grid[$key]['group_id'] = $b['group_id'] ? (int) $b['group_id'] : null;
                $grid[$key]['booking_id'] = (int) $b['id'];
                $grid[$key]['coach_name'] = $b['coach_name'] ?? '';
                $grid[$key]['notes'] = $b['notes'] ?? '';
            }
        }

        return [
            'facility'   => $facility,
            'rows'       => $rows,
            'cols'       => $cols,
            'grid'       => $grid,
            'date'       => $date,
            'start_time' => $startTime,
            'end_time'   => $endTime,
        ];
    }

    /**
     * Bulk assign cells across one or more time slots.
     */
    public static function bulkAssign(int $facilityId, string $date, array $slots, string $action, array $cells, ?int $groupId, ?string $notes, ?int $templateRunId = null): array
    {
        $db = App::getInstance()->db();
        $ts = date('Y-m-d H:i:s');
        $employeeId = (int) (App::getInstance()->session()->get('employee_id') ?? 0);

        $validActions = ['training', 'blocked', 'maintenance', 'hourly', 'open_access'];
        if (!in_array($action, $validActions, true)) {
            return ['success' => false, 'error' => 'إجراء غير صالح'];
        }

        if ($action === 'training' && !$groupId) {
            return ['success' => false, 'error' => 'المجموعة مطلوبة لتعيين تدريب'];
        }

        $facility = self::getFacility($facilityId);
        if (!$facility) {
            return ['success' => false, 'error' => 'المرفق غير موجود'];
        }

        $maxRows = (int) ($facility['pool_grid_rows'] ?? 0);
        $maxCols = (int) ($facility['pool_grid_cols'] ?? 0);

        $coachId = null;
        $label = '';

        if ($groupId) {
            $group = $db->selectOne("SELECT * FROM sa_groups WHERE id = ? AND status = 'active'", [$groupId]);
            if (!$group) {
                return ['success' => false, 'error' => 'المجموعة غير موجودة أو غير نشطة'];
            }
            $coachId = $group['coach_id'] ? (int) $group['coach_id'] : null;
            $label = $group['name_ar'] ?? 'تدريب';
        } elseif ($action === 'blocked') {
            $label = 'محجوب';
        } elseif ($action === 'maintenance') {
            $label = 'صيانة';
        } elseif ($action === 'hourly') {
            $label = $notes ?: 'حجز ساعة';
        } elseif ($action === 'open_access') {
            $label = 'دخول حر';
        }

        // Pre-fetch all active pool zone bookings for this facility+date
        $allConflicts = $db->select(
            "SELECT zone_row, zone_col, start_time FROM sa_pool_zone_bookings
             WHERE facility_id = ? AND booking_date = ? AND status = 'active'",
            [$facilityId, $date]
        );
        $conflictSet = [];
        foreach ($allConflicts as $c) {
            $conflictSet[$c['start_time'] . ':' . $c['zone_row'] . ':' . $c['zone_col']] = true;
        }

        // Cross-check sa_bookings for this pool facility on the same date
        $bookingConflicts = $db->select(
            "SELECT b.start_time, b.end_time FROM sa_bookings b
             INNER JOIN sa_facility_units fu ON fu.id = b.facility_unit_id
             WHERE fu.facility_id = ? AND b.booking_date = ?
             AND b.status NOT IN ('cancelled', 'no_show')",
            [$facilityId, $date]
        );
        $bookingTimeRanges = [];
        foreach ($bookingConflicts as $bc) {
            $bookingTimeRanges[] = ['start' => $bc['start_time'], 'end' => $bc['end_time']];
        }

        $assigned = 0;
        $skipped = 0;

        foreach ($slots as $slot) {
            $startTime = $slot['start_time'] ?? '';
            $endTime = $slot['end_time'] ?? '';
            if (!$startTime || !$endTime) continue;

            $startTimeFull = (strlen($startTime) === 5) ? $startTime . ':00' : $startTime;
            $endTimeFull = (strlen($endTime) === 5) ? $endTime . ':00' : $endTime;

            foreach ($cells as $cell) {
                $row = (int) ($cell['row'] ?? -1);
                $col = (int) ($cell['col'] ?? -1);

                if ($row < 0 || $row >= $maxRows || $col < 0 || $col >= $maxCols) {
                    $skipped++;
                    continue;
                }

                $key = $startTimeFull . ':' . $row . ':' . $col;
                if (isset($conflictSet[$key])) {
                    $skipped++;
                    continue;
                }

                // For hourly assignments, cross-check against regular bookings
                if ($action === 'hourly' && !empty($bookingTimeRanges)) {
                    $hasBookingConflict = false;
                    foreach ($bookingTimeRanges as $btr) {
                        if ($startTimeFull < $btr['end'] && $endTimeFull > $btr['start']) {
                            $hasBookingConflict = true;
                            break;
                        }
                    }
                    if ($hasBookingConflict) {
                        $skipped++;
                        continue;
                    }
                }

                $insertData = [
                    'facility_id'     => $facilityId,
                    'booking_date'    => $date,
                    'start_time'      => $startTimeFull,
                    'end_time'        => $endTimeFull,
                    'zone_row'        => $row,
                    'zone_col'        => $col,
                    'assignment_type' => ($action === 'open_access') ? 'hourly' : $action,
                    'access_mode'     => ($action === 'open_access') ? 'open_access' : 'reserved',
                    'group_id'        => $groupId,
                    'coach_id'        => $coachId,
                    'label'           => $label,
                    'notes'           => $notes,
                    'status'          => 'active',
                    'created_by'      => $employeeId,
                    'created_at'      => $ts,
                    'updated_at'      => $ts,
                ];

                if ($templateRunId !== null) {
                    $insertData['template_run_id'] = $templateRunId;
                }

                $db->insert('sa_pool_zone_bookings', $insertData);
                $conflictSet[$key] = true;
                $assigned++;
            }
        }

        return ['success' => true, 'assigned' => $assigned, 'skipped' => $skipped];
    }

    /**
     * Bulk clear cells across one or more time slots.
     */
    public static function bulkClear(int $facilityId, string $date, array $slots, array $cells): array
    {
        $db = App::getInstance()->db();
        $ts = date('Y-m-d H:i:s');
        $employeeId = (int) (App::getInstance()->session()->get('employee_id') ?? 0);
        $cleared = 0;

        // Pre-fetch all active bookings for this facility+date
        $allActive = $db->select(
            "SELECT id, zone_row, zone_col, start_time FROM sa_pool_zone_bookings
             WHERE facility_id = ? AND booking_date = ? AND status = 'active'",
            [$facilityId, $date]
        );
        $activeMap = [];
        foreach ($allActive as $a) {
            $activeMap[$a['start_time'] . ':' . $a['zone_row'] . ':' . $a['zone_col']] = (int) $a['id'];
        }

        foreach ($slots as $slot) {
            $startTime = $slot['start_time'] ?? '';
            if (!$startTime) continue;
            $startTimeFull = (strlen($startTime) === 5) ? $startTime . ':00' : $startTime;

            foreach ($cells as $cell) {
                $row = (int) ($cell['row'] ?? -1);
                $col = (int) ($cell['col'] ?? -1);

                if ($row < 0 || $col < 0) continue;

                $key = $startTimeFull . ':' . $row . ':' . $col;
                if (isset($activeMap[$key])) {
                    $db->update('sa_pool_zone_bookings', [
                        'status'       => 'cancelled',
                        'cancelled_by' => $employeeId,
                        'cancelled_at' => $ts,
                        'updated_at'   => $ts,
                    ], 'id = ?', [$activeMap[$key]]);
                    $cleared++;
                }
            }
        }

        return ['success' => true, 'cleared' => $cleared];
    }

    public static function updateGridSize(int $facilityId, int $rows, int $cols): array
    {
        $db = App::getInstance()->db();

        if ($rows < 1 || $rows > 25 || $cols < 1 || $cols > 25) {
            return ['success' => false, 'error' => 'الحد المسموح من 1 إلى 25 صفوف/أعمدة'];
        }

        $db->update('sa_facilities', [
            'pool_grid_rows' => $rows,
            'pool_grid_cols' => $cols,
            'updated_at'     => date('Y-m-d H:i:s'),
        ], 'id = ?', [$facilityId]);

        return ['success' => true];
    }

    /**
     * Copy from one source slot to one or more target slots.
     */
    public static function copySlot(int $facilityId, string $date, string $fromTime, array $toSlots): array
    {
        $db = App::getInstance()->db();
        $ts = date('Y-m-d H:i:s');
        $employeeId = (int) (App::getInstance()->session()->get('employee_id') ?? 0);

        $fromTimeFull = (strlen($fromTime) === 5) ? $fromTime . ':00' : $fromTime;

        $source = $db->select(
            "SELECT * FROM sa_pool_zone_bookings
             WHERE facility_id = ? AND booking_date = ? AND start_time = ? AND status = 'active'",
            [$facilityId, $date, $fromTimeFull]
        );

        if (empty($source)) {
            return ['success' => false, 'error' => 'لا توجد بيانات في الفترة المصدر'];
        }

        $copied = 0;
        $skipped = 0;

        foreach ($toSlots as $target) {
            $toTime = $target['start'] ?? '';
            $toEndTime = $target['end'] ?? '';
            if (!$toTime || !$toEndTime) continue;

            $toTimeFull = (strlen($toTime) === 5) ? $toTime . ':00' : $toTime;
            $toEndTimeFull = (strlen($toEndTime) === 5) ? $toEndTime . ':00' : $toEndTime;

            foreach ($source as $s) {
                $conflict = $db->selectOne(
                    "SELECT id FROM sa_pool_zone_bookings
                     WHERE facility_id = ? AND booking_date = ? AND start_time = ?
                     AND zone_row = ? AND zone_col = ? AND status = 'active'",
                    [$facilityId, $date, $toTimeFull, $s['zone_row'], $s['zone_col']]
                );

                if ($conflict) {
                    $skipped++;
                    continue;
                }

                $db->insert('sa_pool_zone_bookings', [
                    'facility_id'     => $facilityId,
                    'booking_date'    => $date,
                    'start_time'      => $toTimeFull,
                    'end_time'        => $toEndTimeFull,
                    'zone_row'        => $s['zone_row'],
                    'zone_col'        => $s['zone_col'],
                    'assignment_type' => $s['assignment_type'],
                    'group_id'        => $s['group_id'],
                    'coach_id'        => $s['coach_id'],
                    'label'           => $s['label'],
                    'notes'           => $s['notes'],
                    'status'          => 'active',
                    'created_by'      => $employeeId,
                    'created_at'      => $ts,
                    'updated_at'      => $ts,
                ]);
                $copied++;
            }
        }

        return ['success' => true, 'copied' => $copied, 'skipped' => $skipped];
    }
}
