<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Models;

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

class Group extends Model
{
    protected static string $table = 'sa_groups';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = true;
    protected static bool $dispatchEvents = true;

    protected static array $fillable = [
        'code', 'name_ar', 'name_en', 'program_id', 'coach_id',
        'min_capacity', 'max_capacity', 'current_count', 'is_full',
        'season_start', 'season_end', 'status',
    ];

    public static function getStatusOptions(): array
    {
        return [
            'active' => 'نشط',
            'paused' => 'متوقف',
            'completed' => 'مكتمل',
            'cancelled' => 'ملغي',
        ];
    }

    public static function getSchedule(int $groupId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT gs.*, fu.name_ar as unit_name, fu.code as unit_code
             FROM sa_group_schedule gs
             LEFT JOIN sa_facility_units fu ON fu.id = gs.facility_unit_id
             WHERE gs.group_id = ? AND gs.is_active = 1
             ORDER BY gs.day_of_week ASC, gs.start_time ASC",
            [$groupId]
        );
    }

    public static function getPlayers(int $groupId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT gp.*, p.full_name_ar as player_name, p.registration_serial as player_code, p.phone, p.medical_status,
                    (SELECT COUNT(*) FROM sa_subscriptions s
                     WHERE s.player_id = gp.player_id AND s.group_id = gp.group_id
                     AND s.payment_status IN ('unpaid','overdue','partial')) as unpaid_count,
                    (SELECT SUM(s2.final_amount) FROM sa_subscriptions s2
                     WHERE s2.player_id = gp.player_id AND s2.group_id = gp.group_id
                     AND s2.payment_status IN ('unpaid','overdue','partial')) as unpaid_total
             FROM sa_group_players gp
             JOIN sa_players p ON p.id = gp.player_id
             WHERE gp.group_id = ? AND gp.status IN ('active','pending_payment')
             ORDER BY FIELD(gp.status, 'pending_payment', 'active'), p.full_name_ar ASC",
            [$groupId]
        );
    }

    public static function getActive(): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT g.*, p.name_ar as program_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_coaches c ON c.id = g.coach_id
             WHERE g.status = 'active' AND g.is_archived = 0
             ORDER BY g.name_ar ASC",
            []
        );
    }
}
