<?php
declare(strict_types=1);

namespace App\Modules\PlayerAffairs\Models;

use App\Core\Model;
use App\Core\App;

class Player extends Model
{
    protected static string $table = 'players';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = false;
    protected static bool $autoTrackAuthor = true;

    protected static array $fillable = [
        'player_type',
        'member_id',
        'activity_id_number',
        'registration_serial',
        'full_name_ar',
        'full_name_en',
        'national_id',
        'passport_number',
        'date_of_birth',
        'gender',
        'phone',
        'email',
        'address',
        'guardian_name',
        'guardian_phone',
        'guardian_national_id',
        'guardian_relationship',
        'photo_path',
        'medical_status',
        'medical_expiry_date',
        'school_name',
        'school_grade',
        'registration_fee_paid',
        'card_status',
        'access_window_minutes',
        'notes',
        'branch_id',
        'is_archived',
        'archived_at',
        'archived_by',
    ];

    /**
     * Get player types with Arabic labels.
     */
    public static function getPlayerTypes(): array
    {
        return [
            'member'     => 'عضو',
            'non_member' => 'غير عضو',
        ];
    }

    /**
     * Get genders with Arabic labels.
     */
    public static function getGenders(): array
    {
        return [
            'male'   => 'ذكر',
            'female' => 'أنثى',
        ];
    }

    /**
     * Get card statuses with Arabic labels.
     */
    public static function getCardStatuses(): array
    {
        return [
            'inactive'  => 'غير فعال',
            'active'    => 'فعال',
            'suspended' => 'موقوف',
            'revoked'   => 'ملغى',
        ];
    }

    /**
     * Get medical statuses with Arabic labels.
     */
    public static function getMedicalStatuses(): array
    {
        return [
            'pending'     => 'قيد المراجعة',
            'fit'         => 'لائق',
            'conditional' => 'لائق بشروط',
            'unfit'       => 'غير لائق',
            'expired'     => 'منتهي',
        ];
    }

    /**
     * Get guardian relationships with Arabic labels.
     */
    public static function getGuardianRelationships(): array
    {
        return [
            'father'  => 'الأب',
            'mother'  => 'الأم',
            'brother' => 'الأخ',
            'sister'  => 'الأخت',
            'other'   => 'أخرى',
        ];
    }

    /**
     * Get the Arabic label for a card status.
     */
    public static function getCardStatusLabel(string $status): string
    {
        $statuses = self::getCardStatuses();
        return $statuses[$status] ?? $status;
    }

    /**
     * Get the badge color for a card status.
     */
    public static function getCardStatusColor(string $status): string
    {
        $colors = [
            'inactive'  => '#6B7280',
            'active'    => '#059669',
            'suspended' => '#D97706',
            'revoked'   => '#DC2626',
        ];
        return $colors[$status] ?? '#6B7280';
    }

    /**
     * Get the Arabic label for a medical status.
     */
    public static function getMedicalStatusLabel(string $status): string
    {
        $statuses = self::getMedicalStatuses();
        return $statuses[$status] ?? $status;
    }

    /**
     * Get the badge color for a medical status.
     */
    public static function getMedicalStatusColor(string $status): string
    {
        $colors = [
            'pending'     => '#6B7280',
            'fit'         => '#059669',
            'conditional' => '#D97706',
            'unfit'       => '#DC2626',
            'expired'     => '#9CA3AF',
        ];
        return $colors[$status] ?? '#6B7280';
    }

    /**
     * Calculate age from date_of_birth.
     */
    public function getAge(): ?int
    {
        if (empty($this->date_of_birth)) {
            return null;
        }
        $dob = new \DateTime($this->date_of_birth);
        $now = new \DateTime();
        return (int) $dob->diff($now)->y;
    }

    /**
     * Check if the player is a minor (under 18).
     */
    public function isMinor(): bool
    {
        $age = $this->getAge();
        return $age !== null && $age < 18;
    }

    /**
     * Get all active (non-archived) players ordered by name.
     */
    public static function allActive(): array
    {
        return static::query()
            ->where('is_archived', '=', 0)
            ->orderBy('full_name_ar', 'ASC')
            ->get();
    }

    /**
     * Search players with filters and pagination.
     */
    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();

        $joins = '';
        $where = ['p.is_archived = 0'];
        $params = [];

        // Text search: name, national_id, registration_serial
        if (!empty($filters['q'])) {
            $search = '%' . $filters['q'] . '%';
            $where[] = '(p.full_name_ar LIKE ? OR p.full_name_en LIKE ? OR p.national_id LIKE ? OR p.registration_serial LIKE ?)';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }

        // Player type filter
        if (!empty($filters['player_type'])) {
            $where[] = 'p.player_type = ?';
            $params[] = $filters['player_type'];
        }

        // Card status filter
        if (!empty($filters['card_status'])) {
            $where[] = 'p.card_status = ?';
            $params[] = $filters['card_status'];
        }

        // Medical status filter
        if (!empty($filters['medical_status'])) {
            $where[] = 'p.medical_status = ?';
            $params[] = $filters['medical_status'];
        }

        // Discipline filter (derived from enrollments → academies → disciplines)
        if (!empty($filters['discipline_id'])) {
            $joins .= ' INNER JOIN academy_enrollments ae_disc ON ae_disc.player_id = p.id AND ae_disc.status IN (\'active\', \'suspended\')';
            $joins .= ' INNER JOIN academies a_disc ON a_disc.id = ae_disc.academy_id';
            $where[] = 'a_disc.discipline_id = ?';
            $params[] = (int) $filters['discipline_id'];
        }

        // Academy filter (join academy_enrollments)
        if (!empty($filters['academy_id'])) {
            $joins .= ' INNER JOIN academy_enrollments ae ON ae.player_id = p.id';
            $where[] = 'ae.academy_id = ?';
            $params[] = (int) $filters['academy_id'];
        }

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

        // Count total
        $countSql = "SELECT COUNT(DISTINCT p.id) as total FROM players p {$joins} WHERE {$whereClause}";
        $countRow = $db->selectOne($countSql, $params);
        $total = (int) ($countRow['total'] ?? 0);

        // Pagination
        $lastPage = max(1, (int) ceil($total / $perPage));
        $page = min($page, $lastPage);
        $offset = ($page - 1) * $perPage;

        // Fetch data
        $dataSql = "SELECT DISTINCT p.* FROM players p {$joins} WHERE {$whereClause} ORDER BY p.full_name_ar ASC LIMIT {$perPage} OFFSET {$offset}";
        $data = $db->select($dataSql, $params);

        return [
            'data'       => $data,
            'pagination' => [
                'current_page' => $page,
                'last_page'    => $lastPage,
                'per_page'     => $perPage,
                'total'        => $total,
            ],
        ];
    }
}
