<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;

final class SlotAvailabilityService
{
    public static function check(
        int $facilityUnitId,
        string $date,
        string $startTime,
        string $endTime,
        int $spotsRequested = 1,
        ?int $excludeBookingId = null
    ): array {
        $db = App::getInstance()->db();

        $unit = $db->selectOne(
            "SELECT fu.*, f.id as fac_id FROM sa_facility_units fu
             JOIN sa_facilities f ON f.id = fu.facility_id
             WHERE fu.id = ? AND fu.is_active = 1",
            [$facilityUnitId]
        );

        if (!$unit) {
            return ['available' => false, 'reason' => 'الوحدة غير موجودة أو غير مفعلة', 'remaining' => 0];
        }

        $blackout = $db->selectOne(
            "SELECT id FROM sa_blackout_dates
             WHERE (facility_id = ? OR facility_unit_id = ?)
             AND blackout_date = ?
             AND (start_time IS NULL OR (start_time < ? AND end_time > ?))",
            [(int) $unit['fac_id'], $facilityUnitId, $date, $endTime, $startTime]
        );

        if ($blackout) {
            return ['available' => false, 'reason' => 'المرفق محجوب في هذا التوقيت', 'remaining' => 0];
        }

        // Cross-check zone bookings for facilities with grid configured
        $facilityType = $db->selectOne(
            "SELECT facility_type, pool_grid_rows, pool_grid_cols FROM sa_facilities WHERE id = ?",
            [(int) $unit['fac_id']]
        );
        if ($facilityType && (int) ($facilityType['pool_grid_rows'] ?? 0) > 0 && (int) ($facilityType['pool_grid_cols'] ?? 0) > 0) {
            $poolZoneBlocked = (int) ($db->selectOne(
                "SELECT COUNT(*) as cnt FROM sa_pool_zone_bookings
                 WHERE facility_id = ? AND booking_date = ? AND status = 'active'
                 AND assignment_type IN ('blocked', 'maintenance', 'training')
                 AND start_time < ? AND end_time > ?",
                [(int) $unit['fac_id'], $date, $endTime, $startTime]
            )['cnt'] ?? 0);

            if ($poolZoneBlocked > 0) {
                $totalLanes = (int) ($db->selectOne(
                    "SELECT pool_grid_cols FROM sa_facilities WHERE id = ?",
                    [(int) $unit['fac_id']]
                )['pool_grid_cols'] ?? 0);

                if ($totalLanes > 0 && $poolZoneBlocked >= $totalLanes) {
                    return ['available' => false, 'reason' => 'جميع حارات الحمام مشغولة في هذا التوقيت (شبكة الحمام)', 'remaining' => 0];
                }
            }
        }

        $excludeClause = $excludeBookingId ? " AND id != ?" : "";
        $params = [$facilityUnitId, $date, $endTime, $startTime];
        if ($excludeBookingId) {
            $params[] = $excludeBookingId;
        }

        if ($unit['booking_mode'] === 'exclusive') {
            $existing = $db->selectOne(
                "SELECT id FROM sa_bookings
                 WHERE facility_unit_id = ? AND booking_date = ?
                 AND status NOT IN ('cancelled','no_show')
                 AND start_time < ? AND end_time > ?{$excludeClause}
                 LIMIT 1",
                $params
            );

            if ($existing) {
                return ['available' => false, 'reason' => 'الوحدة محجوزة بالفعل في هذا التوقيت', 'remaining' => 0];
            }

            return ['available' => true, 'reason' => null, 'remaining' => (int) $unit['max_capacity']];
        }

        $occupied = $db->selectOne(
            "SELECT COALESCE(SUM(spots_reserved), 0) as total
             FROM sa_bookings
             WHERE facility_unit_id = ? AND booking_date = ?
             AND status NOT IN ('cancelled','no_show')
             AND start_time < ? AND end_time > ?{$excludeClause}",
            $params
        );

        $used = (int) ($occupied['total'] ?? 0);
        $remaining = (int) $unit['max_capacity'] - $used;

        if ($spotsRequested > $remaining) {
            return [
                'available' => false,
                'reason' => "السعة غير كافية. المتبقي: {$remaining}، المطلوب: {$spotsRequested}",
                'remaining' => $remaining,
            ];
        }

        return ['available' => true, 'reason' => null, 'remaining' => $remaining];
    }

    public static function getUnitScheduleForDate(int $facilityUnitId, string $date): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT b.*, g.name_ar as group_name, c.full_name_ar as coach_name
             FROM sa_bookings b
             LEFT JOIN sa_groups g ON g.id = b.group_id
             LEFT JOIN sa_coaches c ON c.id = b.coach_id
             WHERE b.facility_unit_id = ? AND b.booking_date = ?
             AND b.status NOT IN ('cancelled','no_show')
             ORDER BY b.start_time",
            [$facilityUnitId, $date]
        );
    }
}
