<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Course Model
 *
 * Handles courses offered by the institute.
 */
class Course extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'courses';

    /**
     * Get featured and active courses with a limit.
     *
     * @param int $limit Maximum number of courses to return
     * @return array<int, array<string, mixed>>
     */
    public function getFeatured(int $limit = 6): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `active` = :active AND `is_featured` = :featured ORDER BY `created_at` DESC LIMIT :limit',
            $this->table
        );

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

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

    /**
     * Get all active courses by category.
     *
     * @param string $category The course category to filter by
     * @return array<int, array<string, mixed>>
     */
    public function getByCategory(string $category): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `category` = :category AND `active` = :active ORDER BY `created_at` DESC',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':category' => $category, ':active' => 1]);

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

    /**
     * Get all distinct course categories.
     *
     * @return array<int, array<string, mixed>>
     */
    public function getCategories(): array
    {
        $sql = sprintf(
            'SELECT DISTINCT `category` FROM `%s` WHERE `active` = :active ORDER BY `category` ASC',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':active' => 1]);

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

    /**
     * Get the count of active courses.
     *
     * @return int
     */
    public function getActiveCount(): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS total FROM `%s` WHERE `active` = :active',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':active' => 1]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return (int) ($result['total'] ?? 0);
    }
}
