<?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;
use App\Modules\SportsActivity\Services\EnrollmentService;

class PlayerAssignmentController extends Controller
{
    public function index(Request $request): Response
    {
        $db = App::getInstance()->db();

        $disciplineId = trim((string) $request->get('discipline_id', ''));
        $programSearch = trim((string) $request->get('program', ''));
        $status = trim((string) $request->get('status', 'unassigned'));

        $disciplines = $db->select(
            "SELECT id, name_ar, code FROM sa_disciplines WHERE is_archived = 0 ORDER BY name_ar"
        );

        $where = "pl.is_archived = 0";
        $params = [];

        if ($status === 'unassigned') {
            if ($disciplineId !== '') {
                $where .= " AND NOT EXISTS (
                    SELECT 1 FROM sa_group_players gp
                    INNER JOIN sa_groups g ON g.id = gp.group_id
                    INNER JOIN sa_programs p ON p.id = g.program_id
                    WHERE p.discipline_id = ? AND gp.player_id = pl.id AND gp.status IN ('active','pending_payment')
                )";
                $params[] = (int) $disciplineId;
            } else {
                $where .= " AND NOT EXISTS (
                    SELECT 1 FROM sa_group_players gp
                    WHERE gp.player_id = pl.id AND gp.status IN ('active','pending_payment')
                )";
            }
        }

        if ($programSearch !== '') {
            $where .= " AND EXISTS (
                SELECT 1 FROM sa_programs prg
                JOIN sa_disciplines disc ON disc.id = prg.discipline_id
                WHERE prg.name_ar LIKE ? AND prg.is_archived = 0
            )";
            $params[] = "%{$programSearch}%";
        }

        $players = $db->select("
            SELECT pl.id, pl.full_name_ar, pl.date_of_birth, pl.gender, pl.phone,
                   pl.medical_status, pl.medical_expiry_date, pl.photo_path, pl.player_type,
                   pl.created_at,
                   (SELECT GROUP_CONCAT(g.name_ar SEPARATOR ', ')
                    FROM sa_group_players gp
                    INNER JOIN sa_groups g ON g.id = gp.group_id
                    WHERE gp.player_id = pl.id AND gp.status IN ('active','pending_payment')
                   ) as current_groups
            FROM sa_players pl
            WHERE {$where}
            ORDER BY pl.full_name_ar ASC
            LIMIT 200
        ", $params);

        return $this->view('SportsActivity.Views.assignments.index', [
            'players'      => $players,
            'disciplines'  => $disciplines,
            'filters'      => ['discipline_id' => $disciplineId, 'status' => $status, 'program' => $programSearch],
        ]);
    }

    public function show(Request $request, string $playerId): Response
    {
        $db = App::getInstance()->db();

        $player = $db->selectOne("SELECT * FROM sa_players WHERE id = ? AND is_archived = 0", [(int) $playerId]);
        if (!$player) {
            return $this->redirect('/sa/assignments')->withError('اللاعب غير موجود');
        }

        $disciplineId = trim((string) $request->get('discipline_id', ''));
        $programId = trim((string) $request->get('program_id', ''));
        $programSearch = trim((string) $request->get('program_search', ''));

        // All disciplines
        $disciplines = $db->select(
            "SELECT id, name_ar, code FROM sa_disciplines WHERE is_archived = 0 ORDER BY name_ar"
        );

        // Programs filtered by discipline + search
        $programWhere = "p.is_archived = 0";
        $programParams = [];
        if ($disciplineId !== '') {
            $programWhere .= " AND p.discipline_id = ?";
            $programParams[] = (int) $disciplineId;
        }
        if ($programSearch !== '') {
            $programWhere .= " AND p.name_ar LIKE ?";
            $programParams[] = "%{$programSearch}%";
        }

        $programs = $db->select("
            SELECT p.id, p.name_ar, p.discipline_id, p.monthly_fee_member, p.monthly_fee_nonmember,
                   p.max_capacity, p.age_from, p.age_to,
                   d.name_ar as discipline_name,
                   (SELECT COUNT(*) FROM sa_groups g WHERE g.program_id = p.id AND g.status = 'active' AND g.is_archived = 0 AND g.is_full = 0) as available_groups
            FROM sa_programs p
            JOIN sa_disciplines d ON d.id = p.discipline_id
            WHERE {$programWhere}
            ORDER BY d.name_ar, p.name_ar
        ", $programParams);

        // Coaches for selected program (or all if no program selected)
        $coaches = [];
        if ($programId !== '') {
            $coaches = $db->select("
                SELECT DISTINCT c.id, c.full_name_ar, c.phone, c.photo_path,
                       (SELECT COUNT(*) FROM sa_groups g2
                        WHERE g2.program_id = ? AND g2.status = 'active' AND g2.is_archived = 0 AND g2.is_full = 0
                        AND (g2.coach_id = c.id OR EXISTS (SELECT 1 FROM sa_group_coaches gc WHERE gc.group_id = g2.id AND gc.coach_id = c.id))
                       ) as available_groups,
                       (SELECT SUM(g3.current_count) FROM sa_groups g3
                        WHERE g3.program_id = ? AND g3.status = 'active' AND g3.is_archived = 0
                        AND (g3.coach_id = c.id OR EXISTS (SELECT 1 FROM sa_group_coaches gc2 WHERE gc2.group_id = g3.id AND gc2.coach_id = c.id))
                       ) as total_players
                FROM sa_coaches c
                WHERE c.is_archived = 0 AND c.is_active = 1
                AND (
                    EXISTS (SELECT 1 FROM sa_coach_disciplines cd WHERE cd.coach_id = c.id AND cd.discipline_id = (SELECT discipline_id FROM sa_programs WHERE id = ?))
                    OR EXISTS (SELECT 1 FROM sa_groups g4 WHERE g4.program_id = ? AND (g4.coach_id = c.id OR EXISTS (SELECT 1 FROM sa_group_coaches gc3 WHERE gc3.group_id = g4.id AND gc3.coach_id = c.id)))
                )
                ORDER BY c.full_name_ar
            ", [(int) $programId, (int) $programId, (int) $programId, (int) $programId]);
        } elseif ($disciplineId !== '') {
            $coaches = $db->select("
                SELECT c.id, c.full_name_ar, c.phone, c.photo_path
                FROM sa_coaches c
                WHERE c.is_archived = 0 AND c.is_active = 1
                AND EXISTS (SELECT 1 FROM sa_coach_disciplines cd WHERE cd.coach_id = c.id AND cd.discipline_id = ?)
                ORDER BY c.full_name_ar
            ", [(int) $disciplineId]);
        }

        // Current enrollments
        $currentEnrollments = $db->select("
            SELECT gp.*, g.name_ar as group_name, p.name_ar as program_name
            FROM sa_group_players gp
            JOIN sa_groups g ON g.id = gp.group_id
            JOIN sa_programs p ON p.id = g.program_id
            WHERE gp.player_id = ? AND gp.status IN ('active','pending_payment')
            ORDER BY gp.enrolled_at DESC
        ", [(int) $playerId]);

        // Best available group for selected program (least crowded)
        $bestGroupId = null;
        if ($programId !== '') {
            $bestGroup = $db->selectOne("
                SELECT id FROM sa_groups
                WHERE program_id = ? AND status = 'active' AND is_archived = 0 AND is_full = 0
                ORDER BY current_count ASC LIMIT 1
            ", [(int) $programId]);
            $bestGroupId = $bestGroup ? (int) $bestGroup['id'] : null;
        }

        return $this->view('SportsActivity.Views.assignments.show', [
            'player'             => $player,
            'disciplines'        => $disciplines,
            'programs'           => $programs,
            'coaches'            => $coaches,
            'currentEnrollments' => $currentEnrollments,
            'bestGroupId'        => $bestGroupId,
            'filters'            => [
                'discipline_id'  => $disciplineId,
                'program_id'     => $programId,
                'program_search' => $programSearch,
            ],
        ]);
    }

    public function assign(Request $request, string $playerId): Response
    {
        $groupId = (int) $request->post('group_id', 0);
        $force = (bool) $request->post('force', false);

        if (!$groupId) {
            return $this->redirect('/sa/assignments/' . $playerId)->withError('يجب اختيار مجموعة');
        }

        if ($force) {
            $result = EnrollmentService::forceEnroll($groupId, (int) $playerId);
        } else {
            $result = EnrollmentService::enroll($groupId, (int) $playerId);
        }

        if (!$result['success']) {
            return $this->redirect('/sa/assignments/' . $playerId)->withError($result['error']);
        }

        $msg = 'تم تسجيل اللاعب بنجاح';
        if (!empty($result['medical_warning'])) {
            $msg .= ' — ' . $result['medical_warning'];
        }
        if (!empty($result['request_number'])) {
            $msg .= ' — رقم الطلب: ' . $result['request_number'];
        }

        return $this->redirect('/sa/assignments/' . $playerId)->withSuccess($msg);
    }

    public function assignByCoach(Request $request, string $playerId): Response
    {
        $programId = (int) $request->post('program_id', 0);
        $coachId = (int) $request->post('coach_id', 0);

        if (!$programId) {
            return $this->redirect('/sa/assignments/' . $playerId)->withError('يجب اختيار البرنامج');
        }
        if (!$coachId) {
            return $this->redirect('/sa/assignments/' . $playerId)->withError('يجب اختيار المدرب');
        }

        $result = EnrollmentService::enrollByCoach($programId, $coachId, (int) $playerId);

        if (!$result['success']) {
            return $this->redirect('/sa/assignments/' . $playerId . '?program_id=' . $programId)->withError($result['error']);
        }

        $msg = 'تم تسجيل اللاعب بنجاح مع المدرب المحدد';
        if (!empty($result['medical_warning'])) {
            $msg .= ' — ' . $result['medical_warning'];
        }
        if (!empty($result['request_number'])) {
            $msg .= ' — رقم الطلب: ' . $result['request_number'];
        }

        return $this->redirect('/sa/assignments/' . $playerId)->withSuccess($msg);
    }
}
