<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Practice Attempt Model
 *
 * Handles student practice exam attempts, score calculation,
 * and result lookups. Supports both guest and logged-in students.
 *
 * KEY FIX: Attempt counting uses EMAIL as the primary identifier.
 * A user's attempts may be stored with different identifiers across
 * sessions (user_id when logged in, student_email as guest).
 * The countBySetAndUser() method uses a combined OR-query so that
 * ALL attempts belonging to the same user are counted regardless
 * of which identifier was stored.
 */
class PracticeAttempt extends Model
{
    protected string $table = 'practice_attempts';

    /**
     * Check whether the user_id column exists in the practice_attempts table.
     * Result is cached per request via static flag.
     */
    private function hasUserIdColumn(): bool
    {
        static $cache = null;
        if ($cache !== null) {
            return $cache;
        }
        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM `{$this->table}` LIKE 'user_id'");
            $cache = ($stmt !== false && $stmt->fetch() !== false);
        } catch (\Throwable $e) {
            $cache = false;
        }
        return $cache;
    }

    /**
     * Calculate score for a practice set attempt.
     * Supports negative marking.
     *
     * @param int $setId Practice set ID
     * @param array<int, string> $answers question_id => selected answer ('a','b','c','d')
     * @return array{obtained: float, total: int, percentage: float, status: string, correct_count: int, wrong_count: int, unanswered_count: int, details: array}
     */
    public function calculateScore(int $setId, array $answers): array
    {
        $qSql = 'SELECT `id`, `correct_answer`, `marks` FROM `practice_questions`
                 WHERE `practice_set_id` = :set_id ORDER BY `sort_order` ASC';

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

        $sSql = 'SELECT `total_marks`, `pass_marks`, `negative_marks` FROM `practice_sets` WHERE `id` = :id LIMIT 1';
        $sStmt = $this->db->prepare($sSql);
        $sStmt->execute([':id' => $setId]);
        $set = $sStmt->fetch(PDO::FETCH_ASSOC);

        $totalMarks = (int) ($set['total_marks'] ?? 0);
        $passMarks = (int) ($set['pass_marks'] ?? 0);
        $negativeMarks = (float) ($set['negative_marks'] ?? 0);

        $obtained = 0.0;
        $correctCount = 0;
        $wrongCount = 0;
        $unansweredCount = 0;
        $details = [];

        foreach ($questions as $question) {
            $questionId = (int) $question['id'];
            $correctAnswer = $question['correct_answer'];
            $marks = (int) $question['marks'];
            $selected = $answers[$questionId] ?? '';

            if (empty($selected)) {
                $unansweredCount++;
                $earnedMarks = 0;
                $isCorrect = false;
            } elseif ($selected === $correctAnswer) {
                $correctCount++;
                $earnedMarks = $marks;
                $isCorrect = true;
            } else {
                $wrongCount++;
                $earnedMarks = -$negativeMarks;
                $isCorrect = false;
            }

            $obtained += $earnedMarks;

            $details[] = [
                'question_id' => $questionId,
                'selected'    => $selected,
                'correct'     => $correctAnswer,
                'is_correct'  => $isCorrect,
                'marks'       => $marks,
                'earned'      => $earnedMarks,
            ];
        }

        $displayObtained = max(0, $obtained);
        $percentage = $totalMarks > 0 ? round(($displayObtained / $totalMarks) * 100, 2) : 0.00;
        $status = $displayObtained >= $passMarks ? 'pass' : 'fail';

        return [
            'obtained'        => round($displayObtained, 2),
            'total'           => $totalMarks,
            'percentage'      => $percentage,
            'status'          => $status,
            'correct_count'   => $correctCount,
            'wrong_count'     => $wrongCount,
            'unanswered_count'=> $unansweredCount,
            'details'         => $details,
        ];
    }

    /**
     * Get all attempts for a practice set.
     */
    public function getBySet(int $setId): array
    {
        $sql = sprintf(
            'SELECT pa.*, ps.title AS set_title
             FROM `%s` pa
             LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
             WHERE pa.practice_set_id = :set_id
             ORDER BY pa.created_at DESC',
            $this->table
        );

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

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

    /**
     * Get a specific attempt by ID with practice set info.
     */
    public function findWithSet(int $id): ?array
    {
        $sql = sprintf(
            'SELECT pa.*, ps.title AS set_title, ps.title_hindi AS set_title_hindi,
                    ps.total_marks AS set_total_marks, ps.pass_marks AS set_pass_marks,
                    ps.result_published, ps.show_correct_answers, ps.negative_marks,
                    ps.max_attempts
             FROM `%s` pa
             LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
             WHERE pa.id = :id
             LIMIT 1',
            $this->table
        );

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

        return $result !== false ? $result : null;
    }

    /**
     * Count attempts by a user for a specific practice set.
     *
     * Uses a combined OR-query so that ALL attempts belonging to the same
     * user are counted — whether stored with user_id, student_id, email,
     * OR phone. EMAIL or PHONE are the most reliable cross-session
     * identifiers for guest users.
     *
     * @param int $setId Practice set ID
     * @param int|null $userId Logged-in user ID
     * @param int|null $studentId Student record ID
     * @param string $email Guest email (optional)
     * @param string $phone Guest mobile number (digits only, optional)
     * @return int Number of attempts
     */
    public function countBySetAndUser(int $setId, ?int $userId = null, ?int $studentId = null, string $email = '', string $phone = ''): int
    {
        $hasUserIdCol = $this->hasUserIdColumn();
        $hasPhoneCol  = $this->hasColumn('student_phone');

        $conditions = [];
        $params = [':set_id' => $setId];

        // Email is the PRIMARY identifier — always include if available
        if (!empty($email)) {
            $conditions[] = '`student_email` = :email';
            $params[':email'] = $email;
        }

        // Phone is also a primary identifier (used when guest enters mobile)
        if (!empty($phone) && $hasPhoneCol) {
            $conditions[] = '`student_phone` = :phone';
            $params[':phone'] = $phone;
        }

        // Also match by user_id if available (covers backfilled records)
        if ($hasUserIdCol && $userId !== null && $userId > 0) {
            $conditions[] = '`user_id` = :user_id';
            $params[':user_id'] = $userId;
        }

        // Also match by student_id if available
        if ($studentId !== null && $studentId > 0) {
            $conditions[] = '`student_id` = :student_id';
            $params[':student_id'] = $studentId;
        }

        if (empty($conditions)) {
            return 0;
        }

        // Use COUNT(DISTINCT id) with OR to avoid double-counting same rows
        $where = implode(' OR ', $conditions);
        $sql = sprintf(
            'SELECT COUNT(DISTINCT `id`) AS cnt FROM `%s` WHERE `practice_set_id` = :set_id AND (%s)',
            $this->table,
            $where
        );

        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            return (int) ($stmt->fetch()['cnt'] ?? 0);
        } catch (\Throwable $e) {
            error_log('PracticeAttempt::countBySetAndUser error: ' . $e->getMessage());

            // Fallback: try email-only query
            if (!empty($email)) {
                try {
                    $fallbackSql = sprintf(
                        'SELECT COUNT(*) AS cnt FROM `%s` WHERE `practice_set_id` = :set_id AND `student_email` = :email',
                        $this->table
                    );
                    $stmt = $this->db->prepare($fallbackSql);
                    $stmt->execute([':set_id' => $setId, ':email' => $email]);
                    return (int) ($stmt->fetch()['cnt'] ?? 0);
                } catch (\Throwable $e2) {
                    error_log('PracticeAttempt::countBySetAndUser fallback error: ' . $e2->getMessage());
                }
            }

            return 0;
        }
    }

    /**
     * Check whether a given column exists in the practice_attempts table.
     * Result is cached per request via static flag.
     */
    private function hasColumn(string $column): bool
    {
        static $cache = [];
        if (isset($cache[$column])) {
            return $cache[$column];
        }
        try {
            $stmt = $this->db->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND COLUMN_NAME = :col"
            );
            $stmt->execute([':table' => $this->table, ':col' => $column]);
            $cache[$column] = ((int) $stmt->fetchColumn()) > 0;
        } catch (\Throwable $e) {
            $cache[$column] = false;
        }
        return $cache[$column];
    }

    /**
     * Count attempts by a student (by student_id) for a specific practice set.
     */
    public function countBySetAndStudent(int $setId, int $studentId): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS cnt FROM `%s` WHERE `practice_set_id` = :set_id AND `student_id` = :student_id',
            $this->table
        );

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

        return (int) ($stmt->fetch()['cnt'] ?? 0);
    }

    /**
     * Count attempts by a guest (by email) for a specific practice set.
     */
    public function countBySetAndEmail(int $setId, string $email): int
    {
        if (empty($email)) {
            return 0;
        }

        $sql = sprintf(
            'SELECT COUNT(*) AS cnt FROM `%s` WHERE `practice_set_id` = :set_id AND `student_email` = :email',
            $this->table
        );

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

        return (int) ($stmt->fetch()['cnt'] ?? 0);
    }

    /**
     * Get all attempts for a logged-in user across all practice sets.
     * Uses a combined OR-query so that attempts stored with user_id,
     * student_id, or email are ALL returned for the same user.
     */
    public function getByUserId(?int $userId = null, ?int $studentId = null, string $email = ''): array
    {
        $hasUserIdCol = $this->hasUserIdColumn();
        $conditions = [];
        $params = [];

        if (!empty($email)) {
            $conditions[] = 'pa.student_email = :email';
            $params[':email'] = $email;
        }

        if ($hasUserIdCol && $userId !== null && $userId > 0) {
            $conditions[] = 'pa.user_id = :user_id';
            $params[':user_id'] = $userId;
        }

        if ($studentId !== null && $studentId > 0) {
            $conditions[] = 'pa.student_id = :student_id';
            $params[':student_id'] = $studentId;
        }

        if (empty($conditions)) {
            return [];
        }

        $where = implode(' OR ', $conditions);
        $sql = sprintf(
            'SELECT DISTINCT pa.*, ps.title AS set_title, ps.title_hindi AS set_title_hindi,
                    ps.total_marks AS set_total_marks, ps.pass_marks AS set_pass_marks,
                    ps.result_published, ps.duration_minutes, c.name AS course_name
             FROM `%s` pa
             LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE %s
             ORDER BY pa.created_at DESC',
            $this->table,
            $where
        );

        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            error_log('PracticeAttempt::getByUserId error: ' . $e->getMessage());

            // Fallback: email-only
            if (!empty($email)) {
                try {
                    $fbSql = sprintf(
                        'SELECT pa.*, ps.title AS set_title, ps.title_hindi AS set_title_hindi,
                                ps.total_marks AS set_total_marks, ps.pass_marks AS set_pass_marks,
                                ps.result_published, ps.duration_minutes, c.name AS course_name
                         FROM `%s` pa
                         LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
                         LEFT JOIN `courses` c ON ps.course_id = c.id
                         WHERE pa.student_email = :email
                         ORDER BY pa.created_at DESC',
                        $this->table
                    );
                    $stmt = $this->db->prepare($fbSql);
                    $stmt->execute([':email' => $email]);
                    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                } catch (\Throwable $e2) {
                    // Ignore
                }
            }

            return [];
        }
    }

    /**
     * Get all attempts for a logged-in student across all practice sets.
     */
    public function getByStudentId(int $studentId): array
    {
        $sql = sprintf(
            'SELECT pa.*, ps.title AS set_title, ps.title_hindi AS set_title_hindi,
                    ps.total_marks AS set_total_marks, ps.pass_marks AS set_pass_marks,
                    ps.result_published, ps.duration_minutes, c.name AS course_name
             FROM `%s` pa
             LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE pa.student_id = :student_id
             ORDER BY pa.created_at DESC',
            $this->table
        );

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

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

    /**
     * Get all attempts for a guest by email across all practice sets.
     */
    public function getByGuestEmail(string $email): array
    {
        if (empty($email)) {
            return [];
        }

        $sql = sprintf(
            'SELECT pa.*, ps.title AS set_title, ps.title_hindi AS set_title_hindi,
                    ps.total_marks AS set_total_marks, ps.pass_marks AS set_pass_marks,
                    ps.result_published, ps.duration_minutes, c.name AS course_name
             FROM `%s` pa
             LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE pa.student_email = :email
             ORDER BY pa.created_at DESC',
            $this->table
        );

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

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

    /**
     * Get attempt summary for a specific user on a specific practice set.
     * Uses the same combined OR-query approach as countBySetAndUser().
     */
    public function getUserAttemptSummary(int $setId, ?int $studentId, string $email = '', ?int $userId = null, string $phone = ''): array
    {
        $hasUserIdCol = $this->hasUserIdColumn();
        $hasPhoneCol  = $this->hasColumn('student_phone');
        $params = [':set_id' => $setId];
        $conditions = [];

        // Email is PRIMARY
        if (!empty($email)) {
            $conditions[] = 'student_email = :email';
            $params[':email'] = $email;
        }

        // Phone is also PRIMARY
        if (!empty($phone) && $hasPhoneCol) {
            $conditions[] = 'student_phone = :phone';
            $params[':phone'] = $phone;
        }

        if ($hasUserIdCol && $userId !== null && $userId > 0) {
            $conditions[] = 'user_id = :user_id';
            $params[':user_id'] = $userId;
        }

        if ($studentId !== null && $studentId > 0) {
            $conditions[] = 'student_id = :student_id';
            $params[':student_id'] = $studentId;
        }

        if (empty($conditions)) {
            return ['count' => 0, 'latest' => null, 'best_score' => 0];
        }

        $where = 'practice_set_id = :set_id AND (' . implode(' OR ', $conditions) . ')';

        // Count
        $countSql = sprintf('SELECT COUNT(DISTINCT `id`) AS cnt FROM `%s` WHERE %s', $this->table, $where);
        $stmt = $this->db->prepare($countSql);
        $stmt->execute($params);
        $count = (int) ($stmt->fetch()['cnt'] ?? 0);

        // Latest attempt
        $latestSql = sprintf(
            'SELECT * FROM `%s` WHERE %s ORDER BY created_at DESC LIMIT 1',
            $this->table, $where
        );
        $stmt = $this->db->prepare($latestSql);
        $stmt->execute($params);
        $latest = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;

        // Best score
        $bestSql = sprintf(
            'SELECT MAX(percentage) AS best FROM `%s` WHERE %s',
            $this->table, $where
        );
        $stmt = $this->db->prepare($bestSql);
        $stmt->execute($params);
        $bestScore = (float) ($stmt->fetch()['best'] ?? 0);

        return [
            'count'      => $count,
            'latest'     => $latest !== false ? $latest : null,
            'best_score' => $bestScore,
        ];
    }

    /**
     * Get the attempt number for a specific attempt (1st, 2nd, 3rd, etc.)
     * Uses the same OR-query approach for consistent counting.
     */
    public function getAttemptNumber(int $attemptId, int $setId, ?int $userId = null, ?int $studentId = null, string $email = '', string $phone = ''): int
    {
        $hasUserIdCol = $this->hasUserIdColumn();
        $hasPhoneCol  = $this->hasColumn('student_phone');
        $params = [':set_id' => $setId];
        $conditions = [];

        // Email is PRIMARY
        if (!empty($email)) {
            $conditions[] = 'student_email = :email';
            $params[':email'] = $email;
        }

        // Phone is also PRIMARY
        if (!empty($phone) && $hasPhoneCol) {
            $conditions[] = 'student_phone = :phone';
            $params[':phone'] = $phone;
        }

        if ($hasUserIdCol && $userId !== null && $userId > 0) {
            $conditions[] = 'user_id = :user_id';
            $params[':user_id'] = $userId;
        }

        if ($studentId !== null && $studentId > 0) {
            $conditions[] = 'student_id = :student_id';
            $params[':student_id'] = $studentId;
        }

        if (empty($conditions)) {
            return 0;
        }

        $where = 'practice_set_id = :set_id AND (' . implode(' OR ', $conditions) . ')';

        $sql = sprintf(
            'SELECT `id` FROM `%s` WHERE %s ORDER BY created_at ASC',
            $this->table,
            $where
        );

        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            $ids = $stmt->fetchAll(PDO::FETCH_COLUMN);

            $position = array_search($attemptId, $ids, true);
            return $position !== false ? (int) $position + 1 : 0;
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /**
     * Get overall practice test statistics for admin dashboard.
     */
    public function getAdminStats(): array
    {
        $hasUserIdCol = $this->hasUserIdColumn();

        // Total attempts
        $stmt = $this->db->query('SELECT COUNT(*) AS cnt FROM `' . $this->table . '`');
        $totalAttempts = (int) ($stmt->fetch()['cnt'] ?? 0);

        // Unique logged-in students
        if ($hasUserIdCol) {
            $stmt = $this->db->query('SELECT COUNT(DISTINCT CASE WHEN `user_id` IS NOT NULL THEN `user_id` ELSE `student_id` END) AS cnt FROM `' . $this->table . '` WHERE `user_id` IS NOT NULL OR `student_id` IS NOT NULL');
        } else {
            $stmt = $this->db->query('SELECT COUNT(DISTINCT `student_id`) AS cnt FROM `' . $this->table . '` WHERE `student_id` IS NOT NULL');
        }
        $uniqueStudents = (int) ($stmt->fetch()['cnt'] ?? 0);

        // Unique guest emails
        $stmt = $this->db->query('SELECT COUNT(DISTINCT `student_email`) AS cnt FROM `' . $this->table . '` WHERE `student_email` IS NOT NULL AND `student_email` != ""');
        $uniqueGuests = (int) ($stmt->fetch()['cnt'] ?? 0);

        // Average percentage
        $stmt = $this->db->query('SELECT AVG(`percentage`) AS avg_pct FROM `' . $this->table . '`');
        $avgPercentage = round((float) ($stmt->fetch()['avg_pct'] ?? 0), 1);

        // Recent 5 attempts with set titles
        $sql = sprintf(
            'SELECT pa.*, ps.title AS set_title
             FROM `%s` pa
             LEFT JOIN `practice_sets` ps ON pa.practice_set_id = ps.id
             ORDER BY pa.created_at DESC LIMIT 5',
            $this->table
        );
        $stmt = $this->db->query($sql);
        $recentAttempts = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        // Pass/Fail counts
        $stmt = $this->db->query('SELECT `status`, COUNT(*) AS cnt FROM `' . $this->table . '` GROUP BY `status`');
        $statusCounts = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $statusCounts[$row['status']] = (int) $row['cnt'];
        }

        // Attempts today
        $stmt = $this->db->query("SELECT COUNT(*) AS cnt FROM `" . $this->table . "` WHERE DATE(`created_at`) = CURDATE()");
        $attemptsToday = (int) ($stmt->fetch()['cnt'] ?? 0);

        return [
            'total_attempts'   => $totalAttempts,
            'unique_students'  => $uniqueStudents,
            'unique_guests'    => $uniqueGuests,
            'avg_percentage'   => $avgPercentage,
            'recent_attempts'  => $recentAttempts,
            'pass_count'       => $statusCounts['pass'] ?? 0,
            'fail_count'       => $statusCounts['fail'] ?? 0,
            'attempts_today'   => $attemptsToday,
        ];
    }
}
