<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;

class DashboardController extends Controller
{
    public function index(Request $request): Response
    {
        $db = App::getInstance()->db();
        $today = date('Y-m-d');
        $now = date('H:i:s');
        $dayOfWeek = (int) date('w');

        // ─── Core Counts ────────────────────────────────────────────────────
        $counts = $db->selectOne("
            SELECT
                (SELECT COUNT(*) FROM sa_players WHERE is_archived = 0) as total_players,
                (SELECT COUNT(*) FROM sa_groups WHERE status = 'active' AND is_archived = 0) as active_groups,
                (SELECT COUNT(*) FROM sa_group_players WHERE status = 'active') as enrolled_players,
                (SELECT COUNT(*) FROM sa_coaches WHERE is_active = 1 AND is_archived = 0) as active_coaches,
                (SELECT COUNT(*) FROM sa_bookings WHERE booking_date = ? AND status NOT IN ('cancelled','no_show')) as today_bookings,
                (SELECT COUNT(*) FROM sa_bookings WHERE booking_date = ? AND booking_type = 'training' AND status NOT IN ('cancelled','no_show')) as today_training_sessions,
                (SELECT COUNT(*) FROM sa_bookings WHERE booking_date = ? AND booking_type = 'hourly' AND status NOT IN ('cancelled','no_show')) as today_hourly_bookings
        ", [$today, $today, $today]);

        // ─── Alerts (Action Required) ───────────────────────────────────────
        $alerts = [];

        $pendingMedical = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_player_documents WHERE document_type = 'medical_cert' AND approval_status = 'pending'"
        )['c'];
        if ($pendingMedical > 0) {
            $alerts[] = ['type' => 'warning', 'icon' => 'heart-pulse', 'text' => $pendingMedical . ' شهادة طبية بانتظار الموافقة', 'link' => '/sa/players', 'count' => $pendingMedical];
        }

        $overdueSubscriptions = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_subscriptions WHERE payment_status IN ('unpaid','overdue') AND period_end < ?",
            [$today]
        )['c'];
        if ($overdueSubscriptions > 0) {
            $alerts[] = ['type' => 'danger', 'icon' => 'alert-triangle', 'text' => $overdueSubscriptions . ' اشتراك متأخر السداد', 'link' => '/sa/subscriptions?payment_status=overdue', 'count' => $overdueSubscriptions];
        }

        $fullGroups = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_groups WHERE status = 'active' AND is_archived = 0 AND is_full = 1"
        )['c'];
        if ($fullGroups > 0) {
            $alerts[] = ['type' => 'info', 'icon' => 'users', 'text' => $fullGroups . ' مجموعة ممتلئة (تحتاج فتح مجموعات جديدة)', 'link' => '/sa/groups', 'count' => $fullGroups];
        }

        $waitlistCount = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_waitlist WHERE status = 'waiting'"
        )['c'];
        if ($waitlistCount > 0) {
            $alerts[] = ['type' => 'info', 'icon' => 'clock', 'text' => $waitlistCount . ' لاعب في قائمة الانتظار', 'link' => '/sa/waitlist', 'count' => $waitlistCount];
        }

        $expiringCards = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_player_cards WHERE status = 'active' AND valid_until BETWEEN ? AND DATE_ADD(?, INTERVAL 7 DAY)",
            [$today, $today]
        )['c'];
        if ($expiringCards > 0) {
            $alerts[] = ['type' => 'warning', 'icon' => 'credit-card', 'text' => $expiringCards . ' كارت ينتهي خلال أسبوع', 'link' => '/sa/cards', 'count' => $expiringCards];
        }

        $medicalGraceExpiring = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_group_players gp
             JOIN sa_players sp ON sp.id = gp.player_id
             WHERE gp.status = 'active' AND gp.medical_grace_deadline IS NOT NULL
               AND gp.medical_grace_deadline BETWEEN ? AND DATE_ADD(?, INTERVAL 3 DAY)
               AND sp.medical_status NOT IN ('fit','conditional')"
            , [$today, $today]
        )['c'];
        if ($medicalGraceExpiring > 0) {
            $alerts[] = ['type' => 'danger', 'icon' => 'heart-pulse', 'text' => $medicalGraceExpiring . ' لاعب تنتهي مهلتهم الطبية خلال 3 أيام', 'link' => '/sa/players', 'count' => $medicalGraceExpiring];
        }

        $eligibleMakeups = (int) $db->selectOne(
            "SELECT COUNT(*) as c FROM sa_makeup_sessions WHERE status = 'eligible' AND expires_at >= ?",
            [$today]
        )['c'];
        if ($eligibleMakeups > 0) {
            $alerts[] = ['type' => 'info', 'icon' => 'refresh-ccw', 'text' => $eligibleMakeups . ' حصة تعويضية بانتظار الجدولة', 'link' => '/sa/makeup-sessions?status=eligible', 'count' => $eligibleMakeups];
        }

        // ─── Live Now: What's happening right now ───────────────────────────
        $liveNow = $db->select(
            "SELECT b.start_time, b.end_time, b.booking_type, b.booker_name, b.booker_type,
                    b.organization_name, b.participant_count,
                    g.name_ar as group_name, c.full_name_ar as coach_name,
                    fu.name_ar as unit_name, f.name_ar as facility_name
             FROM sa_bookings b
             JOIN sa_facility_units fu ON fu.id = b.facility_unit_id
             JOIN sa_facilities f ON f.id = fu.facility_id
             LEFT JOIN sa_groups g ON g.id = b.group_id
             LEFT JOIN sa_coaches c ON c.id = b.coach_id
             WHERE b.booking_date = ? AND b.start_time <= ? AND b.end_time > ?
             AND b.status NOT IN ('cancelled','no_show')
             ORDER BY b.start_time",
            [$today, $now, $now]
        );

        // ─── Upcoming (next 2 hours) ───────────────────────────────────────
        $twoHoursLater = date('H:i:s', strtotime('+2 hours'));
        $upcoming = $db->select(
            "SELECT b.start_time, b.end_time, b.booking_type, b.booker_name, b.booker_type,
                    b.organization_name, b.participant_count,
                    g.name_ar as group_name, c.full_name_ar as coach_name,
                    fu.name_ar as unit_name, f.name_ar as facility_name
             FROM sa_bookings b
             JOIN sa_facility_units fu ON fu.id = b.facility_unit_id
             JOIN sa_facilities f ON f.id = fu.facility_id
             LEFT JOIN sa_groups g ON g.id = b.group_id
             LEFT JOIN sa_coaches c ON c.id = b.coach_id
             WHERE b.booking_date = ? AND b.start_time > ? AND b.start_time <= ?
             AND b.status NOT IN ('cancelled','no_show')
             ORDER BY b.start_time
             LIMIT 10",
            [$today, $now, $twoHoursLater]
        );

        // ─── Facility Utilization Today ─────────────────────────────────────
        $facilityUsage = $db->select(
            "SELECT f.id, f.name_ar, f.facility_type,
                    COUNT(DISTINCT b.id) as booking_count,
                    (SELECT COUNT(*) FROM sa_facility_units WHERE facility_id = f.id AND is_active = 1) as unit_count
             FROM sa_facilities f
             LEFT JOIN sa_facility_units fu ON fu.facility_id = f.id AND fu.is_active = 1
             LEFT JOIN sa_bookings b ON b.facility_unit_id = fu.id AND b.booking_date = ? AND b.status NOT IN ('cancelled','no_show')
             WHERE f.is_active = 1 AND f.is_archived = 0
             GROUP BY f.id, f.name_ar, f.facility_type
             ORDER BY booking_count DESC",
            [$today]
        );

        // ─── Groups Near Capacity (80%+) ────────────────────────────────────
        $groupsNearCapacity = $db->select(
            "SELECT g.id, g.name_ar, g.current_count, g.max_capacity, g.is_full,
                    p.name_ar as program_name, d.name_ar as discipline_name,
                    c.full_name_ar as coach_name
             FROM sa_groups g
             LEFT JOIN sa_programs p ON p.id = g.program_id
             LEFT JOIN sa_disciplines d ON d.id = p.discipline_id
             LEFT JOIN sa_coaches c ON c.id = g.coach_id
             WHERE g.status = 'active' AND g.is_archived = 0
             AND g.current_count >= (g.max_capacity * 0.8)
             ORDER BY (g.current_count / g.max_capacity) DESC
             LIMIT 10"
        );

        // ─── Recent Registrations (last 7 days) ────────────────────────────
        $recentRegistrations = $db->select(
            "SELECT r.id, r.registration_number, r.status, r.created_at, r.player_type,
                    p.full_name_ar as player_name,
                    g.name_ar as group_name, d.name_ar as discipline_name
             FROM sa_registrations r
             INNER JOIN sa_players p ON p.id = r.player_id
             LEFT JOIN sa_groups g ON g.id = r.group_id
             LEFT JOIN sa_programs pr ON pr.id = g.program_id
             LEFT JOIN sa_disciplines d ON d.id = pr.discipline_id
             WHERE r.created_at >= DATE_SUB(?, INTERVAL 7 DAY) AND r.status != 'cancelled'
             ORDER BY r.created_at DESC
             LIMIT 8",
            [$today]
        );

        // ─── Today's Training Schedule Summary ──────────────────────────────
        $todaySchedule = $db->select(
            "SELECT gs.start_time, gs.end_time, g.name_ar as group_name, g.current_count,
                    c.full_name_ar as coach_name, fu.name_ar as unit_name, f.name_ar as facility_name,
                    d.name_ar as discipline_name
             FROM sa_group_schedule gs
             INNER JOIN sa_groups g ON g.id = gs.group_id AND g.status = 'active'
             LEFT JOIN sa_coaches c ON c.id = g.coach_id
             LEFT JOIN sa_facility_units fu ON fu.id = gs.facility_unit_id
             LEFT JOIN sa_facilities f ON f.id = fu.facility_id
             LEFT JOIN sa_programs p ON p.id = g.program_id
             LEFT JOIN sa_disciplines d ON d.id = p.discipline_id
             WHERE gs.day_of_week = ? AND gs.is_active = 1
             ORDER BY gs.start_time, f.name_ar",
            [$dayOfWeek]
        );

        // ─── Coach Load Today ───────────────────────────────────────────────
        $coachLoad = $db->select(
            "SELECT c.id, c.full_name_ar as name,
                    COUNT(DISTINCT gs.group_id) as group_count,
                    MIN(gs.start_time) as first_session,
                    MAX(gs.end_time) as last_session
             FROM sa_coaches c
             INNER JOIN sa_groups g ON g.coach_id = c.id AND g.status = 'active'
             INNER JOIN sa_group_schedule gs ON gs.group_id = g.id AND gs.day_of_week = ? AND gs.is_active = 1
             WHERE c.is_active = 1 AND c.is_archived = 0
             GROUP BY c.id, c.full_name_ar
             ORDER BY group_count DESC
             LIMIT 10",
            [$dayOfWeek]
        );

        // ─── Discipline Breakdown ───────────────────────────────────────────
        $disciplineBreakdown = $db->select(
            "SELECT d.id, d.name_ar,
                    COUNT(DISTINCT g.id) as group_count,
                    SUM(g.current_count) as total_players,
                    SUM(g.max_capacity) as total_capacity
             FROM sa_disciplines d
             INNER JOIN sa_programs p ON p.discipline_id = d.id
             INNER JOIN sa_groups g ON g.program_id = p.id AND g.status = 'active' AND g.is_archived = 0
             WHERE d.is_active = 1 AND d.is_archived = 0
             GROUP BY d.id, d.name_ar
             ORDER BY total_players DESC"
        );

        return $this->view('SportsActivity.Views.dashboard', [
            'counts'              => $counts,
            'alerts'              => $alerts,
            'liveNow'             => $liveNow,
            'upcoming'            => $upcoming,
            'facilityUsage'       => $facilityUsage,
            'groupsNearCapacity'  => $groupsNearCapacity,
            'recentRegistrations' => $recentRegistrations,
            'todaySchedule'       => $todaySchedule,
            'coachLoad'           => $coachLoad,
            'disciplineBreakdown' => $disciplineBreakdown,
            'today'               => $today,
            'now'                 => $now,
            'dayName'             => self::arabicDayName($dayOfWeek),
        ]);
    }

    private static function arabicDayName(int $dayOfWeek): string
    {
        $days = ['الأحد', 'الاثنين', 'الثلاثاء', 'الأربعاء', 'الخميس', 'الجمعة', 'السبت'];
        return $days[$dayOfWeek] ?? '';
    }
}
