<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Practice Question Model
 *
 * Handles MCQ questions for practice sets with bilingual support
 * (Hindi + English) and bulk creation for Excel/CSV import.
 */
class PracticeQuestion extends Model
{
    protected string $table = 'practice_questions';

    /**
     * Get all questions for a practice set.
     *
     * @param int $setId Practice set ID
     * @return array<int, array<string, mixed>>
     */
    public function getBySet(int $setId): array
    {
        $sql = sprintf(
            'SELECT * FROM `%s` WHERE `practice_set_id` = :set_id ORDER BY `sort_order` ASC',
            $this->table
        );

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

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

    /**
     * Create multiple questions in a single transaction.
     * Used by Excel/CSV import functionality.
     *
     * @param int $setId Practice set ID
     * @param array<int, array<string, mixed>> $questions Array of question data
     * @return int Number of questions inserted
     */
    public function createBulk(int $setId, array $questions): int
    {
        $this->db->beginTransaction();
        $count = 0;

        try {
            $sql = sprintf(
                'INSERT INTO `%s` (`practice_set_id`, `question_text`, `question_text_hindi`,
                    `option_a`, `option_a_hindi`, `option_b`, `option_b_hindi`,
                    `option_c`, `option_c_hindi`, `option_d`, `option_d_hindi`,
                    `correct_answer`, `marks`, `sort_order`)
                 VALUES (:practice_set_id, :question_text, :question_text_hindi,
                    :option_a, :option_a_hindi, :option_b, :option_b_hindi,
                    :option_c, :option_c_hindi, :option_d, :option_d_hindi,
                    :correct_answer, :marks, :sort_order)',
                $this->table
            );

            $stmt = $this->db->prepare($sql);

            foreach ($questions as $idx => $q) {
                $stmt->execute([
                    ':practice_set_id'    => $setId,
                    ':question_text'      => $q['question_text'] ?? '',
                    ':question_text_hindi'=> $q['question_text_hindi'] ?? null,
                    ':option_a'           => $q['option_a'] ?? '',
                    ':option_a_hindi'     => $q['option_a_hindi'] ?? null,
                    ':option_b'           => $q['option_b'] ?? '',
                    ':option_b_hindi'     => $q['option_b_hindi'] ?? null,
                    ':option_c'           => $q['option_c'] ?? null,
                    ':option_c_hindi'     => $q['option_c_hindi'] ?? null,
                    ':option_d'           => $q['option_d'] ?? null,
                    ':option_d_hindi'     => $q['option_d_hindi'] ?? null,
                    ':correct_answer'     => $q['correct_answer'] ?? 'a',
                    ':marks'              => (int) ($q['marks'] ?? 1),
                    ':sort_order'         => (int) ($q['sort_order'] ?? ($idx + 1)),
                ]);
                $count++;
            }

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }

        return $count;
    }

    /**
     * Count questions for a practice set.
     *
     * @param int $setId Practice set ID
     * @return int
     */
    public function countBySet(int $setId): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS total FROM `%s` WHERE `practice_set_id` = :set_id',
            $this->table
        );

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

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