<?php
declare(strict_types=1);

final class RankingService
{
    public function __construct(private PDO $db)
    {
    }

    public function getAcademicYears(): array
    {
        return $this->db->query(
            'SELECT id, name, start_date, end_date, is_current
             FROM academic_years
             ORDER BY is_current DESC, start_date DESC, id DESC'
        )->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getCurrentAcademicYearId(): int
    {
        $value = $this->db->query(
            'SELECT id
             FROM academic_years
             ORDER BY is_current DESC, start_date DESC, id DESC
             LIMIT 1'
        )->fetchColumn();

        return (int)($value ?: 0);
    }

    public function getProvinces(): array
    {
        $rows = $this->db->query(
            "SELECT DISTINCT TRIM(province) AS province
             FROM students
             WHERE province IS NOT NULL
               AND TRIM(province) <> ''
             ORDER BY province ASC"
        )->fetchAll(PDO::FETCH_ASSOC);

        return array_values(array_filter(array_map(
            static fn(array $row): string => (string)$row['province'],
            $rows
        )));
    }

    public function getTop10(
        int $academicYearId,
        string $period,
        ?string $province,
        ?int $grade
    ): array {
        [$xpExpression, $joinSql, $whereSql, $params] = $this->buildQueryParts(
            $academicYearId,
            $period,
            $province,
            $grade
        );

        $sql = "
            SELECT
                s.id AS student_id,
                s.full_name,
                s.province,
                s.current_level_id AS grade,
                s.avatar_source,
                s.custom_avatar_path,
                {$xpExpression} AS xp
            FROM students s
            {$joinSql}
            WHERE s.status = 'active'
            {$whereSql}
            GROUP BY
                s.id,
                s.full_name,
                s.province,
                s.current_level_id,
                s.avatar_source,
                s.custom_avatar_path
            HAVING xp > 0
            ORDER BY xp DESC, s.full_name ASC, s.id ASC
            LIMIT 10
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as $index => &$row) {
            $row['rank'] = $index + 1;
            $row['student_id'] = (int)$row['student_id'];
            $row['grade'] = $row['grade'] !== null ? (int)$row['grade'] : null;
            $row['xp'] = (int)$row['xp'];
        }
        unset($row);

        return $rows;
    }

    public function getStudentRank(
        int $studentId,
        int $academicYearId,
        string $period,
        ?string $province,
        ?int $grade
    ): ?array {
        [$xpExpression, $joinSql, $whereSql, $params] = $this->buildQueryParts(
            $academicYearId,
            $period,
            $province,
            $grade
        );

        $sql = "
            SELECT
                s.id AS student_id,
                s.full_name,
                s.province,
                s.current_level_id AS grade,
                {$xpExpression} AS xp
            FROM students s
            {$joinSql}
            WHERE s.status = 'active'
            {$whereSql}
            GROUP BY
                s.id,
                s.full_name,
                s.province,
                s.current_level_id
            HAVING xp > 0
            ORDER BY xp DESC, s.full_name ASC, s.id ASC
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        $rank = 0;
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $rank++;

            if ((int)$row['student_id'] !== $studentId) {
                continue;
            }

            return [
                'rank' => $rank,
                'student_id' => (int)$row['student_id'],
                'full_name' => (string)$row['full_name'],
                'province' => $row['province'],
                'grade' => $row['grade'] !== null ? (int)$row['grade'] : null,
                'xp' => (int)$row['xp'],
            ];
        }

        return null;
    }

    public function getStudent(int $studentId): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT id, full_name, province, current_level_id AS grade, lifetime_xp
             FROM students
             WHERE id = :student_id
             LIMIT 1'
        );
        $stmt->execute([':student_id' => $studentId]);

        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function buildQueryParts(
        int $academicYearId,
        string $period,
        ?string $province,
        ?int $grade
    ): array {
        $period = strtolower(trim($period));
        $params = [];
        $whereSql = '';

        if ($province !== null && trim($province) !== '') {
            $whereSql .= ' AND s.province = :province';
            $params[':province'] = trim($province);
        }

        if ($grade !== null && $grade >= 1 && $grade <= 5) {
            $whereSql .= ' AND s.current_level_id = :grade';
            $params[':grade'] = $grade;
        }

        if ($period === 'all_time') {
            return ['MAX(s.lifetime_xp)', '', $whereSql, $params];
        }

        if ($period === 'week' || $period === 'month') {
            $params[':academic_year_id'] = $academicYearId;

            $dateCondition = $period === 'week'
                ? 'xt.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)'
                : 'xt.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)';

            return [
                'COALESCE(SUM(xt.xp_change), 0)',
                "LEFT JOIN xp_transactions xt
                    ON xt.student_id = s.id
                   AND xt.academic_year_id = :academic_year_id
                   AND {$dateCondition}",
                $whereSql,
                $params,
            ];
        }

        $params[':academic_year_id'] = $academicYearId;

        return [
            'COALESCE(MAX(sx.xp), 0)',
            'LEFT JOIN student_xp sx
                ON sx.student_id = s.id
               AND sx.academic_year_id = :academic_year_id',
            $whereSql,
            $params,
        ];
    }
}
