<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Course Model
 *
 * Handles courses offered by the institute with fallback queries.
 */
class Course extends Model
{
    protected string $table = 'courses';

    /**
     * Get featured and active courses.
     *
     * Tries `is_featured` first (modern schema), then `featured` (legacy),
     * then falls back to all active courses. Each strategy is wrapped in
     * try-catch so a missing column doesn't break the homepage.
     */
    public function getFeatured(int $limit = 6): array
    {
        // Detect which "featured" column exists — check both names.
        // Use INFORMATION_SCHEMA for reliability (SHOW COLUMNS requires
        // a slightly different fetch pattern on some MySQL drivers).
        $featuredCol = '';
        try {
            $stmt = $this->db->prepare(
                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = :table
                   AND COLUMN_NAME IN ('is_featured', 'featured')
                 ORDER BY COLUMN_NAME = 'is_featured' DESC
                 LIMIT 1"
            );
            $stmt->execute([':table' => $this->table]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!empty($row['COLUMN_NAME'])) {
                $featuredCol = $row['COLUMN_NAME'];
            }
        } catch (\Throwable $e) {
            $featuredCol = '';
        }

        // If we found a featured column, query it
        if ($featuredCol !== '') {
            try {
                $sql = sprintf(
                    'SELECT * FROM `%s` WHERE `active` = :active AND `%s` = :featured ORDER BY `created_at` DESC LIMIT :limit',
                    $this->table,
                    $featuredCol
                );
                $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();
                $results = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                if (!empty($results)) return $results;
            } catch (\Throwable $e) {
                // Fall through to fallback
            }
        }

        // Fallback: all active courses
        return $this->getAllActive($limit);
    }

    /**
     * Get all active courses with a limit.
     */
    public function getAllActive(int $limit = 6): array
    {
        try {
            $sql = sprintf('SELECT * FROM `%s` WHERE `active` = :active ORDER BY `created_at` 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 $e) {
            // Fallback: without active/created_at
            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 $e2) {
                return [];
            }
        }
    }

    /**
     * Get all active courses by category.
     */
    public function getByCategory(string $category): array
    {
        try {
            $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) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Get all distinct course categories.
     */
    public function getCategories(): array
    {
        try {
            $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) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Get the count of active courses.
     */
    public function getActiveCount(): int
    {
        try {
            $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);
        } catch (\Throwable $e) {
            return 0;
        }
    }
}
