<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Modules\Cashier\Services\PaymentRequestService;

final class CoachAssessmentService
{
    public static function getPendingPlayers(?int $coachId = null): array
    {
        $db = App::getInstance()->db();

        $sql = "SELECT r.id as registration_id, r.registration_number, r.selected_disciplines,
                       r.player_type, r.created_at as registration_date,
                       p.id as player_id, p.full_name_ar, p.full_name_en, p.national_id,
                       p.date_of_birth, p.gender, p.phone, p.photo_path, p.medical_status
                FROM sa_registrations r
                INNER JOIN sa_players p ON p.id = r.player_id
                WHERE r.status = 'completed' AND r.payment_status = 'paid'
                  AND NOT EXISTS (
                      SELECT 1 FROM sa_group_players gp
                      WHERE gp.player_id = r.player_id AND gp.status IN ('active','pending_payment')
                  )
                ORDER BY r.created_at DESC";

        return $db->select($sql);
    }

    public static function getAvailableGroups(?int $disciplineId = null): array
    {
        $db = App::getInstance()->db();

        $sql = "SELECT g.id, g.name_ar, g.code, g.current_count, g.max_capacity, g.is_full,
                       p.id as program_id, p.name_ar as program_name,
                       p.monthly_fee_member, p.monthly_fee_nonmember,
                       d.id as discipline_id, 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.is_full = 0";

        $params = [];
        if ($disciplineId) {
            $sql .= " AND d.id = ?";
            $params[] = $disciplineId;
        }

        $sql .= " ORDER BY d.name_ar ASC, g.name_ar ASC";

        return $db->select($sql, $params);
    }

    public static function assess(int $registrationId, int $groupId, string $skillLevel, string $notes, int $assessedBy): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();

        $registration = $db->selectOne(
            "SELECT r.*, p.full_name_ar, p.player_type, p.member_id
             FROM sa_registrations r
             INNER JOIN sa_players p ON p.id = r.player_id
             WHERE r.id = ? AND r.payment_status = 'paid'",
            [$registrationId]
        );
        if (!$registration) {
            return ['success' => false, 'error' => 'التسجيل غير موجود أو لم يتم الدفع'];
        }

        $playerId = (int) $registration['player_id'];

        $existing = $db->selectOne(
            "SELECT id FROM sa_group_players WHERE group_id = ? AND player_id = ? AND status IN ('active','pending_payment')",
            [$groupId, $playerId]
        );
        if ($existing) {
            return ['success' => false, 'error' => 'اللاعب مسجل بالفعل في هذه المجموعة'];
        }

        $group = $db->selectOne(
            "SELECT g.*, p.monthly_fee_member, p.monthly_fee_nonmember, p.name_ar as program_name
             FROM sa_groups g
             LEFT JOIN sa_programs p ON p.id = g.program_id
             WHERE g.id = ? AND g.status = 'active' AND g.is_archived = 0",
            [$groupId]
        );
        if (!$group) {
            return ['success' => false, 'error' => 'المجموعة غير موجودة أو غير نشطة'];
        }

        if ((int) $group['current_count'] >= (int) $group['max_capacity']) {
            return ['success' => false, 'error' => 'المجموعة ممتلئة — السعة القصوى ' . $group['max_capacity']];
        }

        $playerType = $registration['player_type'] ?? 'non_member';
        $monthlyFee = $playerType === 'member'
            ? (float) ($group['monthly_fee_member'] ?? 0)
            : (float) ($group['monthly_fee_nonmember'] ?? 0);

        $db->beginTransaction();
        try {
            $enrollmentId = $db->insert('sa_group_players', [
                'group_id'         => $groupId,
                'player_id'        => $playerId,
                'enrolled_at'      => date('Y-m-d'),
                'status'           => 'active',
                'assessed_by'      => $assessedBy,
                'assessment_notes' => $notes ?: null,
                'skill_level'      => $skillLevel,
                'assessed_at'      => date('Y-m-d H:i:s'),
                'created_at'       => date('Y-m-d H:i:s'),
                'updated_at'       => date('Y-m-d H:i:s'),
                'created_by'       => $employee ? (int) $employee->id : $assessedBy,
            ]);

            $newCount = (int) $group['current_count'] + 1;
            $db->update('sa_groups', [
                'current_count' => $newCount,
                'is_full'       => $newCount >= (int) $group['max_capacity'] ? 1 : 0,
                'updated_at'    => date('Y-m-d H:i:s'),
            ], 'id = ?', [$groupId]);

            $db->update('sa_registrations', [
                'group_id'   => $groupId,
                'status'     => 'assessed',
                'updated_at' => date('Y-m-d H:i:s'),
            ], 'id = ?', [$registrationId]);

            if ($monthlyFee > 0) {
                $periodStart = date('Y-m-01');
                $periodEnd = date('Y-m-t');

                $db->insert('sa_subscriptions', [
                    'subscription_number' => self::generateSubNumber(),
                    'player_id'           => $playerId,
                    'group_id'            => $groupId,
                    'period_start'        => $periodStart,
                    'period_end'          => $periodEnd,
                    'amount'              => $monthlyFee,
                    'final_amount'        => $monthlyFee,
                    'payment_status'      => 'unpaid',
                    'auto_generated'      => 1,
                    'created_at'          => date('Y-m-d H:i:s'),
                    'updated_at'          => date('Y-m-d H:i:s'),
                ]);
            }

            $db->commit();
        } catch (\Throwable $e) {
            $db->rollBack();
            return ['success' => false, 'error' => 'فشل التقييم: ' . $e->getMessage()];
        }

        EventBus::dispatch('sa.player.assessed', [
            'registration_id' => $registrationId,
            'player_id'       => $playerId,
            'group_id'        => $groupId,
            'skill_level'     => $skillLevel,
            'assessed_by'     => $assessedBy,
        ]);

        return [
            'success'       => true,
            'enrollment_id' => $enrollmentId,
            'group_name'    => $group['name_ar'],
            'monthly_fee'   => $monthlyFee,
        ];
    }

    private static function generateSubNumber(): string
    {
        $db = App::getInstance()->db();
        $prefix = 'SUB-' . date('Y') . '-';
        $row = $db->selectOne(
            "SELECT MAX(CAST(SUBSTRING(subscription_number, " . (strlen($prefix) + 1) . ") AS UNSIGNED)) as max_num
             FROM sa_subscriptions WHERE subscription_number LIKE ?",
            [$prefix . '%']
        );
        $next = ((int) ($row['max_num'] ?? 0)) + 1;
        return $prefix . str_pad((string) $next, 6, '0', STR_PAD_LEFT);
    }
}
