<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Practice Set Model
 *
 * Handles practice exam set definitions with course relationships
 * and published set retrieval for the frontend.
 */
class PracticeSet extends Model
{
    protected string $table = 'practice_sets';

    /**
     * Get all published practice sets with course info.
     *
     * @return array<int, array<string, mixed>>
     */
    public function getPublished(): array
    {
        // Check if user_id column exists in practice_attempts (safe migration)
        $hasUserIdCol = false;
        try {
            $colCheck = $this->db->query("SHOW COLUMNS FROM `practice_attempts` LIKE 'user_id'");
            $hasUserIdCol = ($colCheck !== false && $colCheck->fetch() !== false);
        } catch (\Throwable $e) {
            // Table might not exist yet, that's OK
        }

        // Build the unique_student_count subquery based on available columns
        if ($hasUserIdCol) {
            $uniqueStudentSub = '(SELECT COUNT(DISTINCT CASE WHEN pa2.user_id IS NOT NULL THEN pa2.user_id ELSE pa2.student_id END) '
                              . 'FROM `practice_attempts` pa2 WHERE pa2.practice_set_id = ps.id '
                              . 'AND (pa2.user_id IS NOT NULL OR pa2.student_id IS NOT NULL)) AS unique_student_count';
        } else {
            $uniqueStudentSub = '(SELECT COUNT(DISTINCT pa2.student_id) '
                              . 'FROM `practice_attempts` pa2 WHERE pa2.practice_set_id = ps.id '
                              . 'AND pa2.student_id IS NOT NULL) AS unique_student_count';
        }

        $sql = sprintf(
            'SELECT ps.*, c.name AS course_name, c.category AS course_category,
                    (SELECT COUNT(*) FROM `practice_questions` pq WHERE pq.practice_set_id = ps.id) AS question_count,
                    (SELECT COUNT(*) FROM `practice_attempts` pa WHERE pa.practice_set_id = ps.id) AS attempt_count,
                    %s
             FROM `%s` ps
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE ps.is_published = :published
             ORDER BY ps.created_at DESC',
            $uniqueStudentSub,
            $this->table
        );

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

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

    /**
     * Get a practice set with all its questions.
     *
     * @param int $id Practice set ID
     * @return array<string, mixed>
     */
    public function getWithQuestions(int $id): array
    {
        $sql = sprintf(
            'SELECT ps.*, c.name AS course_name
             FROM `%s` ps
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE ps.id = :id
             LIMIT 1',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);
        $set = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($set === false) {
            return [];
        }

        // Fetch questions
        $qSql = 'SELECT * FROM `practice_questions`
                 WHERE `practice_set_id` = :set_id
                 ORDER BY `sort_order` ASC';

        $qStmt = $this->db->prepare($qSql);
        $qStmt->execute([':set_id' => $id]);
        $set['questions'] = $qStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        return $set;
    }

    /**
     * Get published practice sets filtered by course category.
     *
     * @param string $category Course category (e.g., 'ccc', 'olevel')
     * @return array<int, array<string, mixed>>
     */
    public function getPublishedByCategory(string $category): array
    {
        $sql = sprintf(
            'SELECT ps.*, c.name AS course_name, c.category AS course_category,
                    (SELECT COUNT(*) FROM `practice_questions` pq WHERE pq.practice_set_id = ps.id) AS question_count
             FROM `%s` ps
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE ps.is_published = :published AND c.category = :category
             ORDER BY ps.created_at DESC',
            $this->table
        );

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

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