<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;

final class SaPlayerReportService
{
    public static function generate(array $filters): array
    {
        $db = App::getInstance()->db();

        $columns = [
            'player_name'      => 'اسم اللاعب',
            'national_id'      => 'الرقم القومي',
            'phone'            => 'الهاتف',
            'player_type'      => 'النوع',
            'discipline_name'  => 'اللعبة',
            'program_name'     => 'البرنامج',
            'group_name'       => 'المجموعة',
            'coach_name'       => 'المدرب',
            'enrollment_status'=> 'حالة التسجيل',
            'medical_status'   => 'الحالة الطبية',
            'medical_expiry'   => 'انتهاء الشهادة الطبية',
            'card_status'      => 'حالة الكارت',
            'reg_fee_paid'     => 'رسوم التسجيل',
            'sub_status'       => 'حالة الاشتراك',
            'branch_name'      => 'الفرع',
        ];

        $where = ['sp.is_archived = 0'];
        $params = [];

        if (!empty($filters['discipline_id'])) {
            $where[] = 'd.id = ?';
            $params[] = (int) $filters['discipline_id'];
        }
        if (!empty($filters['program_id'])) {
            $where[] = 'p.id = ?';
            $params[] = (int) $filters['program_id'];
        }
        if (!empty($filters['group_id'])) {
            $where[] = 'g.id = ?';
            $params[] = (int) $filters['group_id'];
        }
        if (!empty($filters['player_id'])) {
            $where[] = 'sp.id = ?';
            $params[] = (int) $filters['player_id'];
        }
        if (!empty($filters['player_type'])) {
            $where[] = 'sp.player_type = ?';
            $params[] = $filters['player_type'];
        }
        if (!empty($filters['medical_status'])) {
            $where[] = 'sp.medical_status = ?';
            $params[] = $filters['medical_status'];
        }
        if (!empty($filters['payment_status'])) {
            $where[] = 'latest_sub.payment_status = ?';
            $params[] = $filters['payment_status'];
        }
        if (!empty($filters['branch_id'])) {
            $where[] = 'sp.branch_id = ?';
            $params[] = (int) $filters['branch_id'];
        }

        $whereClause = implode(' AND ', $where);

        $sql = "
            SELECT
                sp.id AS player_id,
                sp.full_name_ar AS player_name,
                sp.national_id,
                COALESCE(sp.phone, sp.guardian_phone) AS phone,
                sp.player_type,
                COALESCE(d.name_ar, '—') AS discipline_name,
                COALESCE(p.name_ar, '—') AS program_name,
                COALESCE(g.name_ar, '—') AS group_name,
                COALESCE(c.full_name_ar, '—') AS coach_name,
                COALESCE(gp.status, '—') AS enrollment_status,
                sp.medical_status,
                sp.medical_expiry_date AS medical_expiry,
                sp.card_status,
                sp.registration_fee_paid AS reg_fee_paid,
                COALESCE(latest_sub.payment_status, '—') AS sub_status,
                COALESCE(br.name_ar, '—') AS branch_name
            FROM sa_players sp
            LEFT JOIN sa_group_players gp ON gp.player_id = sp.id
                AND gp.status NOT IN ('removed','transferred')
            LEFT JOIN sa_groups g ON g.id = gp.group_id AND g.is_archived = 0
            LEFT JOIN sa_programs p ON p.id = g.program_id AND p.is_archived = 0
            LEFT JOIN sa_disciplines d ON d.id = p.discipline_id AND d.is_archived = 0
            LEFT JOIN sa_coaches c ON c.id = g.coach_id
            LEFT JOIN branches br ON br.id = sp.branch_id
            LEFT JOIN (
                SELECT s1.player_id, s1.group_id, s1.payment_status
                FROM sa_subscriptions s1
                INNER JOIN (
                    SELECT player_id, group_id, MAX(period_end) AS max_end
                    FROM sa_subscriptions
                    GROUP BY player_id, group_id
                ) s2 ON s1.player_id = s2.player_id
                    AND s1.group_id = s2.group_id
                    AND s1.period_end = s2.max_end
            ) latest_sub ON latest_sub.player_id = sp.id
                AND latest_sub.group_id = COALESCE(g.id, 0)
            WHERE {$whereClause}
            ORDER BY sp.full_name_ar
        ";

        $rows = $db->select($sql, $params);

        $statusLabels = [
            'pending_payment' => 'بانتظار الدفع',
            'active'          => 'نشط',
            'paused'          => 'متوقف',
            'removed'         => 'محذوف',
            'transferred'     => 'منقول',
            'pending'         => 'معلق',
            'fit'             => 'لائق',
            'conditional'     => 'مشروط',
            'unfit'           => 'غير لائق',
            'expired'         => 'منتهي',
            'inactive'        => 'غير نشط',
            'active'          => 'نشط',
            'suspended'       => 'موقوف',
            'revoked'         => 'ملغي',
            'unpaid'          => 'غير مدفوع',
            'paid'            => 'مدفوع',
            'partial'         => 'جزئي',
            'exempt'          => 'معفي',
            'overdue'         => 'متأخر',
            'member'          => 'عضو',
            'non_member'      => 'غير عضو',
        ];

        $formatted = [];
        foreach ($rows as $row) {
            $formatted[] = [
                'player_name'       => $row['player_name'],
                'national_id'       => $row['national_id'] ?? '—',
                'phone'             => $row['phone'] ?? '—',
                'player_type'       => $statusLabels[$row['player_type']] ?? $row['player_type'],
                'discipline_name'   => $row['discipline_name'],
                'program_name'      => $row['program_name'],
                'group_name'        => $row['group_name'],
                'coach_name'        => $row['coach_name'],
                'enrollment_status' => $statusLabels[$row['enrollment_status']] ?? $row['enrollment_status'],
                'medical_status'    => $statusLabels[$row['medical_status']] ?? $row['medical_status'],
                'medical_expiry'    => $row['medical_expiry'] ?? '—',
                'card_status'       => $statusLabels[$row['card_status']] ?? $row['card_status'],
                'reg_fee_paid'      => $row['reg_fee_paid'] ? 'نعم' : 'لا',
                'sub_status'        => $statusLabels[$row['sub_status']] ?? $row['sub_status'],
                'branch_name'       => $row['branch_name'],
            ];
        }

        return [
            'columns' => $columns,
            'rows'    => $formatted,
            'total'   => count($formatted),
        ];
    }
}
