<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Modules\SportsActivity\SaConstants;

final class TrainingAttendanceService
{
    public static function getGroupSessionsForDate(int $groupId, string $date): array
    {
        $db = App::getInstance()->db();
        $dayOfWeek = (int) date('w', strtotime($date));

        return $db->select(
            "SELECT gs.*, fu.name_ar as unit_name, f.name_ar as facility_name
             FROM sa_group_schedule gs
             JOIN sa_facility_units fu ON fu.id = gs.facility_unit_id
             JOIN sa_facilities f ON f.id = fu.facility_id
             WHERE gs.group_id = ? AND gs.day_of_week = ? AND gs.is_active = 1",
            [$groupId, $dayOfWeek]
        );
    }

    public static function getActivePlayers(int $groupId): array
    {
        $db = App::getInstance()->db();

        return $db->select(
            "SELECT gp.id as enrollment_id, gp.player_id, gp.medical_grace_deadline, gp.paused_months,
                    sp.full_name_ar, sp.registration_serial, sp.medical_status, sp.medical_expiry_date,
                    sp.photo_path, sp.player_type
             FROM sa_group_players gp
             JOIN sa_players sp ON sp.id = gp.player_id
             WHERE gp.group_id = ? AND gp.status = ?
             ORDER BY sp.full_name_ar ASC",
            [$groupId, SaConstants::STATUS_ACTIVE]
        );
    }

    public static function getExistingAttendance(int $groupId, string $date): array
    {
        $db = App::getInstance()->db();

        $rows = $db->select(
            "SELECT * FROM sa_training_attendance WHERE group_id = ? AND session_date = ?",
            [$groupId, $date]
        );

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['player_id']] = $row;
        }
        return $map;
    }

    public static function recordBulk(int $groupId, string $date, array $attendanceData, int $recordedBy): array
    {
        $db = App::getInstance()->db();
        $dayOfWeek = (int) date('w', strtotime($date));

        $schedule = $db->selectOne(
            "SELECT id FROM sa_group_schedule WHERE group_id = ? AND day_of_week = ? AND is_active = 1 LIMIT 1",
            [$groupId, $dayOfWeek]
        );

        $db->beginTransaction();
        try {
            $db->query(
                "DELETE FROM sa_training_attendance WHERE group_id = ? AND session_date = ?",
                [$groupId, $date]
            );

            $recorded = 0;
            $absentees = [];
            $now = date('Y-m-d H:i:s');

            foreach ($attendanceData as $entry) {
                $playerId = (int) ($entry['player_id'] ?? 0);
                $status = $entry['status'] ?? 'absent';
                if (!in_array($status, ['present', 'absent', 'excused', 'late', 'makeup'], true)) {
                    $status = 'absent';
                }

                $db->insert('sa_training_attendance', [
                    'group_id'       => $groupId,
                    'player_id'      => $playerId,
                    'session_date'   => $date,
                    'day_of_week'    => $dayOfWeek,
                    'schedule_id'    => $schedule ? (int) $schedule['id'] : null,
                    'status'         => $status,
                    'check_in_time'  => $entry['check_in_time'] ?? null,
                    'check_out_time' => $entry['check_out_time'] ?? null,
                    'excuse_reason'  => $status === 'excused' ? ($entry['excuse_reason'] ?? null) : null,
                    'recorded_by'    => $recordedBy,
                    'created_at'     => $now,
                    'updated_at'     => $now,
                ]);

                if ($status === 'absent') {
                    $absentees[] = $playerId;
                }
                $recorded++;
            }

            $db->commit();

            if (!empty($absentees)) {
                self::checkAbsenceThreshold($groupId, $absentees, $date);
            }

            return ['success' => true, 'recorded' => $recorded];
        } catch (\Throwable $e) {
            $db->rollBack();
            return ['success' => false, 'error' => 'فشل تسجيل الحضور: ' . $e->getMessage()];
        }
    }

    public static function getPlayerAttendanceSummary(int $playerId, int $groupId, ?string $from = null, ?string $to = null): array
    {
        $db = App::getInstance()->db();

        $where = "group_id = ? AND player_id = ?";
        $params = [$groupId, $playerId];

        if ($from) {
            $where .= " AND session_date >= ?";
            $params[] = $from;
        }
        if ($to) {
            $where .= " AND session_date <= ?";
            $params[] = $to;
        }

        $row = $db->selectOne(
            "SELECT
                COUNT(*) as total_sessions,
                SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present_count,
                SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_count,
                SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END) as excused_count,
                SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_count,
                SUM(CASE WHEN status = 'makeup' THEN 1 ELSE 0 END) as makeup_count
             FROM sa_training_attendance WHERE {$where}",
            $params
        );

        $total = (int) ($row['total_sessions'] ?? 0);
        $present = (int) ($row['present_count'] ?? 0) + (int) ($row['late_count'] ?? 0) + (int) ($row['makeup_count'] ?? 0);

        return [
            'total'          => $total,
            'present'        => (int) ($row['present_count'] ?? 0),
            'absent'         => (int) ($row['absent_count'] ?? 0),
            'excused'        => (int) ($row['excused_count'] ?? 0),
            'late'           => (int) ($row['late_count'] ?? 0),
            'makeup'         => (int) ($row['makeup_count'] ?? 0),
            'attendance_rate' => $total > 0 ? round(($present / $total) * 100, 1) : 0.0,
        ];
    }

    private static function checkAbsenceThreshold(int $groupId, array $playerIds, string $date): void
    {
        $db = App::getInstance()->db();
        $thresholdRow = $db->selectOne(
            "SELECT config_value FROM system_config WHERE config_key = 'sa.absence_threshold'",
            []
        );
        $threshold = (int) ($thresholdRow['config_value'] ?? 5);

        $monthStart = date('Y-m-01', strtotime($date));
        $monthEnd = date('Y-m-t', strtotime($date));

        foreach ($playerIds as $playerId) {
            $absentCount = $db->selectOne(
                "SELECT COUNT(*) as cnt FROM sa_training_attendance
                 WHERE group_id = ? AND player_id = ? AND status = 'absent'
                   AND session_date BETWEEN ? AND ?",
                [$groupId, $playerId, $monthStart, $monthEnd]
            );

            if ((int) ($absentCount['cnt'] ?? 0) >= $threshold) {
                EventBus::dispatch('sa.absence_threshold.reached', [
                    'player_id' => $playerId,
                    'group_id'  => $groupId,
                    'count'     => (int) $absentCount['cnt'],
                    'month'     => date('Y-m', strtotime($date)),
                ]);
            }
        }
    }
}
