<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Topper Model
 *
 * Handles top-performing student data with fallback queries for missing columns.
 */
class Topper extends Model
{
    protected string $table = 'toppers';

    /**
     * Get active toppers with course and branch names.
     *
     * @param int $limit Maximum number of toppers
     * @return array<int, array<string, mixed>>
     */
    public function getActive(int $limit = 8): array
    {
        try {
            $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) ?: [];
        } catch (\Throwable $e) {
            // Fallback 1: try without JOINs (courses/branches tables might have issues)
            try {
                $sql = sprintf(
                    'SELECT * FROM `%s` WHERE `active` = :active ORDER BY `id` DESC 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) ?: [];
            } catch (\Throwable $e2) {
                // Fallback 2: try without WHERE and ORDER BY
                try {
                    $sql = sprintf('SELECT * FROM `%s` ORDER BY `id` DESC LIMIT :limit', $this->table);
                    $stmt = $this->db->prepare($sql);
                    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
                    $stmt->execute();
                    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                } catch (\Throwable $e3) {
                    return [];
                }
            }
        }
    }

    /**
     * Get toppers by rank (top N).
     *
     * @param int $limit Number of toppers
     * @return array<int, array<string, mixed>>
     */
    public function getByRank(int $limit = 3): array
    {
        // Same as getActive but with smaller default limit
        return $this->getActive($limit);
    }
}
