<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Topper Model
 *
 * Handles top-performing students data retrieval with JOINs
 * on courses and branches for related names.
 */
class Topper extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'toppers';

    /**
     * Get active toppers ordered by sort_order, with JOIN on courses and branches
     * for course name and branch name.
     *
     * @param int $limit Maximum number of toppers to return
     * @return array<int, array<string, mixed>>
     */
    public function getActive(int $limit = 8): array
    {
        $sql = sprintf(
            'SELECT t.*, c.name AS course_name, b.name AS branch_name
             FROM `%s` t
             LEFT JOIN `courses` c ON t.course_id = c.id
             LEFT JOIN `branches` b ON t.branch_id = b.id
             WHERE t.active = :active
             ORDER BY t.sort_order ASC
             LIMIT :limit',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':active', 1, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get toppers ordered by rank (sort_order) with a limit, with JOINs.
     *
     * @param int $limit Maximum number of toppers to return
     * @return array<int, array<string, mixed>>
     */
    public function getByRank(int $limit = 3): array
    {
        $sql = sprintf(
            'SELECT t.*, c.name AS course_name, b.name AS branch_name
             FROM `%s` t
             LEFT JOIN `courses` c ON t.course_id = c.id
             LEFT JOIN `branches` b ON t.branch_id = b.id
             WHERE t.active = :active
             ORDER BY t.sort_order ASC
             LIMIT :limit',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':active', 1, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}
