"""
Exam Platform v2 — single-file Flask app, Liara/Render-ready
"""
import csv
import io
import json
import os
import logging
import secrets
from pathlib import Path
from datetime import datetime
from functools import wraps
from html import escape
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError
from urllib.parse import urlparse

from flask import (
    Flask, render_template, request, redirect, url_for,
    session, send_from_directory, flash, abort, Response,
)
from werkzeug.middleware.proxy_fix import ProxyFix
from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash
import sqlite3

# ── App setup ─────────────────────────────────────────────────
app = Flask(__name__)
secret_key = os.environ.get("SECRET_KEY")
if not secret_key:
    secret_key = "change-me-in-production-via-env"
    logging.warning("SECRET_KEY is not set. Set a long random value in your host env vars.")
app.secret_key = secret_key
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1)


def _default_data_dir() -> Path:
    if data_dir := os.environ.get("DATA_DIR"):
        return Path(data_dir)
    if os.name == "nt":
        return Path.cwd() / "runtime"
    if os.environ.get("RENDER"):
        return Path("/opt/render/project/src/data")
    return Path(os.environ.get("TMPDIR", "/tmp")) / "exam_platform"


default_data_dir = _default_data_dir()
app.config["UPLOAD_FOLDER"] = os.environ.get("UPLOAD_FOLDER", str(default_data_dir / "exams"))
app.config["ASSET_FOLDER"] = os.environ.get("ASSET_FOLDER", str(default_data_dir / "assets"))
app.config["MAX_CONTENT_LENGTH"] = 5 * 1024 * 1024   # 5 MB max upload
app.config["ALLOWED_EXTENSIONS"] = {"html"}
app.config["DB_PATH"] = os.environ.get("DB_PATH", str(default_data_dir / "instance" / "exams.db"))
app.config["SESSION_COOKIE_HTTPONLY"] = True
app.config["SESSION_COOKIE_SAMESITE"] = "Lax"
_session_secure_default = "1" if os.environ.get("RENDER") else "0"
app.config["SESSION_COOKIE_SECURE"] = (
    os.environ.get("SESSION_COOKIE_SECURE", _session_secure_default) == "1"
)
app.config["SCHOOL_NAME"] = os.environ.get("SCHOOL_NAME", "مدرسه آنلاین")
app.config["SCHOOL_TAGLINE"] = os.environ.get("SCHOOL_TAGLINE", "سامانه آزمون‌های مدرسه")
app.config["APP_TIMEZONE"] = os.environ.get("APP_TIMEZONE", "Asia/Tehran")

upload_dir = Path(app.config["UPLOAD_FOLDER"]).resolve()
asset_dir = Path(app.config["ASSET_FOLDER"]).resolve()
db_path = Path(app.config["DB_PATH"]).resolve()
upload_dir.mkdir(parents=True, exist_ok=True)
asset_dir.mkdir(parents=True, exist_ok=True)
db_path.parent.mkdir(parents=True, exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s"
)
logger = logging.getLogger(__name__)


def app_timezone():
    try:
        return ZoneInfo(app.config["APP_TIMEZONE"])
    except ZoneInfoNotFoundError:
        return ZoneInfo("UTC")


# ── Database helpers ───────────────────────────────────────────
def get_db():
    conn = sqlite3.connect(app.config["DB_PATH"], timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys=ON")
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    return conn


def init_db():
    conn = get_db()
    c = conn.cursor()

    c.executescript("""
        CREATE TABLE IF NOT EXISTS exams (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            title       TEXT    NOT NULL,
            filename    TEXT    NOT NULL UNIQUE,
            description TEXT    DEFAULT \'\',
            password    TEXT    DEFAULT \'\',
            active      INTEGER DEFAULT 1,
            sort_order  INTEGER DEFAULT 0,
            subject     TEXT    DEFAULT \'\',
            grade       TEXT    DEFAULT \'\',
            teacher     TEXT    DEFAULT \'\',
            duration_minutes INTEGER DEFAULT 0,
            starts_at   TEXT    DEFAULT \'\',
            ends_at     TEXT    DEFAULT \'\',
            instructions TEXT   DEFAULT \'\',
            created_at  TEXT    DEFAULT (datetime(\'now\')),
            updated_at  TEXT    DEFAULT (datetime(\'now\'))
        );

        CREATE TABLE IF NOT EXISTS admins (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            username   TEXT    NOT NULL UNIQUE,
            password   TEXT    NOT NULL,
            created_at TEXT    DEFAULT (datetime(\'now\'))
        );

        CREATE TABLE IF NOT EXISTS results (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            exam_id    INTEGER NOT NULL REFERENCES exams(id) ON DELETE CASCADE,
            student    TEXT    DEFAULT \'ناشناس\',
            score      TEXT,
            details    TEXT,
            submitted_at TEXT  DEFAULT (datetime(\'now\'))
        );

        CREATE TABLE IF NOT EXISTS login_log (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            username   TEXT,
            success    INTEGER,
            ip         TEXT,
            ts         TEXT DEFAULT (datetime(\'now\'))
        );

        CREATE TABLE IF NOT EXISTS students (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            student_code TEXT    NOT NULL UNIQUE,
            first_name   TEXT    NOT NULL,
            last_name    TEXT    NOT NULL,
            grade        TEXT    DEFAULT \'\',
            password     TEXT    NOT NULL,
            active       INTEGER DEFAULT 1,
            created_at   TEXT    DEFAULT (datetime(\'now\'))
        );

        CREATE INDEX IF NOT EXISTS idx_exams_active_sort ON exams(active, sort_order, id);
        CREATE INDEX IF NOT EXISTS idx_results_exam_time ON results(exam_id, submitted_at);
        CREATE INDEX IF NOT EXISTS idx_login_log_ts ON login_log(ts);
        CREATE INDEX IF NOT EXISTS idx_students_code ON students(student_code);
    """)

    existing_columns = {
        row["name"] for row in c.execute("PRAGMA table_info(exams)").fetchall()
    }
    exam_column_defaults = {
        "subject": "TEXT DEFAULT ''",
        "grade": "TEXT DEFAULT ''",
        "teacher": "TEXT DEFAULT ''",
        "duration_minutes": "INTEGER DEFAULT 0",
        "starts_at": "TEXT DEFAULT ''",
        "ends_at": "TEXT DEFAULT ''",
        "instructions": "TEXT DEFAULT ''",
    }
    for column, definition in exam_column_defaults.items():
        if column not in existing_columns:
            c.execute(f"ALTER TABLE exams ADD COLUMN {column} {definition}")

    result_columns = {
        row["name"] for row in c.execute("PRAGMA table_info(results)").fetchall()
    }
    result_column_defaults = {
        "student_id": "INTEGER REFERENCES students(id) ON DELETE SET NULL",
        "violations": "INTEGER DEFAULT 0",
        "duration_seconds": "INTEGER DEFAULT 0",
        "answers_json": "TEXT DEFAULT ''",
        "score_numeric": "REAL",
        "max_score": "REAL",
        "percent_score": "REAL",
        "question_count": "INTEGER DEFAULT 0",
        "correct_count": "INTEGER DEFAULT 0",
        "incorrect_count": "INTEGER DEFAULT 0",
        "blank_count": "INTEGER DEFAULT 0",
        "ip_address": "TEXT DEFAULT ''",
        "user_agent": "TEXT DEFAULT ''",
        "status": "TEXT DEFAULT 'pending'",
        "admin_note": "TEXT DEFAULT ''",
        "reviewed_at": "TEXT DEFAULT ''",
    }
    for column, definition in result_column_defaults.items():
        if column not in result_columns:
            c.execute(f"ALTER TABLE results ADD COLUMN {column} {definition}")

    c.execute(
        "CREATE INDEX IF NOT EXISTS idx_results_student ON results(student_id)"
    )
    c.execute(
        "CREATE INDEX IF NOT EXISTS idx_results_status ON results(status)"
    )

    default_admin = os.environ.get("ADMIN_USERNAME", "admin")
    default_password = os.environ.get("ADMIN_PASSWORD", "Admin@1234")
    if default_password == "Admin@1234":
        logger.warning("Using default admin password. Set ADMIN_PASSWORD before deploying.")
    default_pw = generate_password_hash(default_password)
    c.execute(
        "INSERT OR IGNORE INTO admins (username, password) VALUES (?, ?)",
        (default_admin, default_pw)
    )
    conn.commit()
    conn.close()
    logger.info("Database initialised.")


init_db()


# ── Decorators ─────────────────────────────────────────────────
def login_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if not session.get("admin_id"):
            flash("لطفاً ابتدا وارد شوید.", "warning")
            return redirect(url_for("admin_login"))
        return f(*args, **kwargs)
    return decorated


def student_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if not session.get("student_id"):
            flash("لطفاً با حساب دانش‌آموزی وارد شوید.", "warning")
            return redirect(url_for("student_login", next=request.path))
        return f(*args, **kwargs)
    return decorated


def get_student_by_id(student_id):
    conn = get_db()
    student = conn.execute(
        "SELECT * FROM students WHERE id=? AND active=1", (student_id,)
    ).fetchone()
    conn.close()
    return student


def student_display_name(student):
    return f"{student['first_name']} {student['last_name']}".strip()


def safe_next_url(value):
    if not value or not value.startswith("/") or value.startswith("//"):
        return None
    parsed = urlparse(value)
    if parsed.netloc or parsed.scheme:
        return None
    return value


def allowed_file(filename):
    return (
        "." in filename
        and filename.rsplit(".", 1)[1].lower() in app.config["ALLOWED_EXTENSIONS"]
    )


def allowed_extension(filename, extensions):
    return "." in (filename or "") and filename.rsplit(".", 1)[1].lower() in extensions


def safe_int(value, default=0, minimum=None, maximum=None):
    try:
        number = int(value)
    except (TypeError, ValueError):
        number = default
    if minimum is not None:
        number = max(minimum, number)
    if maximum is not None:
        number = min(maximum, number)
    return number


def score_to_float(value):
    text = (value or "").strip().replace(",", ".")
    numeric = []
    dot_seen = False
    for char in text:
        if char.isdigit():
            numeric.append(char)
        elif char == "." and not dot_seen:
            numeric.append(char)
            dot_seen = True
        elif numeric:
            break
    if not numeric:
        return None
    try:
        return float("".join(numeric))
    except ValueError:
        return None


def parse_float_field(value):
    if value is None:
        return None
    text = str(value).strip().replace(",", ".")
    if not text:
        return None
    try:
        return float(text)
    except ValueError:
        return score_to_float(text)


def summarize_answers_payload(raw_answers):
    summary = {
        "question_count": 0,
        "correct_count": 0,
        "incorrect_count": 0,
        "blank_count": 0,
    }
    raw_answers = (raw_answers or "").strip()
    if not raw_answers:
        return summary
    try:
        payload = json.loads(raw_answers)
    except (TypeError, ValueError):
        return summary

    if isinstance(payload, dict):
        for key in ("answers", "questions", "items", "responses", "results"):
            if isinstance(payload.get(key), list):
                payload = payload[key]
                break

    if isinstance(payload, dict):
        payload = [
            {"question": key, "answer": value}
            for key, value in payload.items()
        ]
    if not isinstance(payload, list):
        return summary

    for item in payload[:1000]:
        summary["question_count"] += 1
        if isinstance(item, dict):
            value = (
                item.get("answer")
                or item.get("selected")
                or item.get("value")
                or item.get("studentAnswer")
                or item.get("response")
                or ""
            )
            correctness = (
                item.get("correct")
                if "correct" in item
                else item.get("isCorrect", item.get("status", None))
            )
        else:
            value = item
            correctness = None

        if value in (None, "", [], {}):
            summary["blank_count"] += 1

        if isinstance(correctness, bool):
            if correctness:
                summary["correct_count"] += 1
            elif value not in (None, "", [], {}):
                summary["incorrect_count"] += 1
        elif isinstance(correctness, str):
            normalized = correctness.strip().lower()
            if normalized in {"correct", "right", "true", "yes", "1", "درست"}:
                summary["correct_count"] += 1
            elif normalized in {"incorrect", "wrong", "false", "no", "0", "نادرست"}:
                summary["incorrect_count"] += 1

    if summary["incorrect_count"] == 0 and summary["correct_count"]:
        answered_wrong = summary["question_count"] - summary["blank_count"] - summary["correct_count"]
        summary["incorrect_count"] = max(0, answered_wrong)
    return summary


def build_result_metrics(score, max_score, answers_json):
    score_numeric = parse_float_field(score)
    max_score_numeric = parse_float_field(max_score)
    answer_summary = summarize_answers_payload(answers_json)

    percent_score = None
    if score_numeric is not None and max_score_numeric and max_score_numeric > 0:
        percent_score = round((score_numeric / max_score_numeric) * 100, 2)
    elif score_numeric is not None:
        percent_score = score_numeric

    return {
        "score_numeric": score_numeric,
        "max_score": max_score_numeric,
        "percent_score": percent_score,
        **answer_summary,
    }


def format_duration(seconds):
    seconds = safe_int(seconds, default=0, minimum=0)
    if not seconds:
        return ""
    minutes, remainder = divmod(seconds, 60)
    if minutes >= 60:
        hours, minutes = divmod(minutes, 60)
        return f"{hours} ساعت و {minutes} دقیقه"
    if minutes:
        return f"{minutes} دقیقه و {remainder} ثانیه"
    return f"{remainder} ثانیه"


def status_label(status):
    return {
        "pending": "در انتظار بررسی",
        "approved": "تأیید شده",
        "rejected": "رد شده",
    }.get(status or "pending", "در انتظار بررسی")


def result_risk(result):
    reasons = []
    violations = safe_int(result["violations"], default=0, minimum=0)
    if violations >= 3:
        reasons.append(f"{violations} تخلف")
    elif violations > 0:
        reasons.append(f"{violations} تخلف")
    if safe_int(result["duration_seconds"], default=0) < 60:
        reasons.append("زمان بسیار کوتاه")
    if not (result["score"] or "").strip():
        reasons.append("بدون نمره")
    if (result["details"] or "").strip().lower() in {"", "-", "none", "null"}:
        reasons.append("جزئیات ناقص")
    return reasons


def clean_text(value, limit=120):
    return (value or "").strip()[:limit]


def split_student_name(full_name):
    parts = clean_text(full_name, 180).split()
    if not parts:
        return "", ""
    if len(parts) == 1:
        return parts[0], "-"
    return parts[0], " ".join(parts[1:])


def parse_student_import(file_storage):
    raw = file_storage.read()
    text = raw.decode("utf-8-sig")
    sample = text[:2048]
    try:
        dialect = csv.Sniffer().sniff(sample, delimiters=",;\t")
    except csv.Error:
        dialect = csv.excel

    rows = list(csv.reader(io.StringIO(text), dialect))
    rows = [[cell.strip() for cell in row] for row in rows if any(cell.strip() for cell in row)]
    if not rows:
        return []

    aliases = {
        "student_code": {"student_code", "code", "stu_code", "stu code", "id", "شماره دانش آموزی", "کد", "کد دانش آموزی"},
        "first_name": {"first_name", "firstname", "first name", "نام"},
        "last_name": {"last_name", "lastname", "last name", "family", "surname", "نام خانوادگی"},
        "full_name": {"full_name", "fullname", "full name", "student", "student_name", "name", "name family", "نام و نام خانوادگی"},
        "grade": {"grade", "class", "level", "پایه", "کلاس"},
        "password": {"password", "pass", "رمز", "رمز عبور"},
    }
    first_row = [cell.lower().replace("-", "_") for cell in rows[0]]
    has_header = any(cell in names for cell in first_row for names in aliases.values())

    mapped_rows = []
    if has_header:
        index = {}
        for i, header in enumerate(first_row):
            for field, names in aliases.items():
                if header in names:
                    index[field] = i
        data_rows = rows[1:]
        for row in data_rows:
            item = {}
            for field, i in index.items():
                item[field] = row[i] if i < len(row) else ""
            mapped_rows.append(item)
    else:
        for row in rows:
            mapped_rows.append({
                "student_code": row[0] if len(row) > 0 else "",
                "full_name": row[1] if len(row) > 1 else "",
                "grade": row[2] if len(row) > 2 else "",
                "password": row[3] if len(row) > 3 else "",
            })

    students = []
    for item in mapped_rows:
        code = clean_text(item.get("student_code"), 40)
        first_name = clean_text(item.get("first_name"), 80)
        last_name = clean_text(item.get("last_name"), 80)
        if not first_name and not last_name:
            first_name, last_name = split_student_name(item.get("full_name", ""))
        students.append({
            "student_code": code,
            "first_name": first_name,
            "last_name": last_name,
            "grade": clean_text(item.get("grade"), 80),
            "password": clean_text(item.get("password"), 120) or code,
        })
    return students


def unique_exam_filename(original_name):
    filename = secure_filename(original_name or "")
    if not filename or not allowed_file(filename):
        return None
    ts = datetime.utcnow().strftime("%Y%m%d%H%M%S%f")
    return f"{ts}_{filename}"


def exam_file_path(filename):
    target = (upload_dir / filename).resolve()
    if upload_dir not in target.parents and target != upload_dir:
        abort(400)
    return target


def unique_asset_filename(original_name):
    filename = secure_filename(original_name or "")
    if not filename or not allowed_extension(filename, {"pdf"}):
        return None
    ts = datetime.utcnow().strftime("%Y%m%d%H%M%S%f")
    return f"{ts}_{filename}"


def asset_file_path(filename):
    target = (asset_dir / filename).resolve()
    if asset_dir not in target.parents and target != asset_dir:
        abort(400)
    return target


@app.route("/exam-assets/<path:filename>")
@student_required
def exam_asset(filename):
    return send_from_directory(asset_dir, filename)


def normalize_builder_questions(raw_questions):
    questions = []
    if isinstance(raw_questions, str):
        try:
            raw_questions = json.loads(raw_questions)
        except (TypeError, ValueError):
            return []
    if not isinstance(raw_questions, list):
        return []

    allowed_types = {"single", "multiple", "true_false", "short", "essay"}
    for item in raw_questions[:200]:
        if not isinstance(item, dict):
            continue
        qtype = (item.get("type") or "single").strip()
        if qtype not in allowed_types:
            qtype = "single"
        prompt = clean_text(item.get("prompt") or item.get("question"), 1200)
        if not prompt:
            continue
        options = [
            clean_text(option, 400)
            for option in (item.get("options") or [])
            if clean_text(option, 400)
        ][:10]
        if qtype in {"single", "multiple"} and len(options) < 2:
            continue
        if qtype == "true_false":
            options = ["True", "False"]

        correct = item.get("correct")
        if qtype == "multiple":
            if not isinstance(correct, list):
                correct = [correct]
            correct = [
                safe_int(value, default=-1, minimum=-1, maximum=len(options) - 1)
                for value in correct
            ]
            correct = sorted({value for value in correct if value >= 0})
        elif qtype in {"single", "true_false"}:
            correct = safe_int(correct, default=0, minimum=0, maximum=len(options) - 1)
        else:
            correct = clean_text(correct, 500)

        questions.append({
            "type": qtype,
            "prompt": prompt,
            "options": options,
            "correct": correct,
            "points": safe_int(item.get("points"), default=1, minimum=0, maximum=100),
            "explanation": clean_text(item.get("explanation"), 1200),
        })
    return questions


def parse_generated_exam_questions(text):
    rows = []
    text = (text or "").strip()
    if not text:
        return rows
    try:
        dialect = csv.Sniffer().sniff(text[:2048], delimiters="|,;\t")
        source_rows = csv.reader(io.StringIO(text), dialect)
    except csv.Error:
        source_rows = (line.split("|") for line in text.splitlines())

    for row in source_rows:
        cells = [cell.strip() for cell in row if cell.strip()]
        if len(cells) < 3:
            continue
        lower = [cell.lower() for cell in cells]
        if lower[0] in {"question", "سوال", "پرسش"}:
            continue
        question = cells[0]
        correct = cells[-1]
        options = cells[1:-1]
        if len(options) < 2:
            continue
        correct_index = None
        if correct.isdigit():
            index = int(correct) - 1
            if 0 <= index < len(options):
                correct_index = index
        if correct_index is None:
            for index, option in enumerate(options):
                if option.strip().lower() == correct.strip().lower():
                    correct_index = index
                    break
        if correct_index is None:
            correct_index = 0
        rows.append({
            "type": "single",
            "prompt": question[:800],
            "options": [option[:300] for option in options[:8]],
            "correct": correct_index,
            "points": 1,
            "explanation": "",
        })
    return rows[:200]


def build_generated_exam_html(title, instructions, questions, duration_minutes=0, attachments=None):
    safe_title = escape(title or "Generated Exam")
    safe_instructions = escape(instructions or "")
    payload = json.dumps(questions, ensure_ascii=False)
    attachment_payload = json.dumps(attachments or [], ensure_ascii=False)
    duration_seconds = max(0, safe_int(duration_minutes, default=0, minimum=0, maximum=600)) * 60
    return f"""<!DOCTYPE html>
<html lang="fa" dir="rtl">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>{safe_title}</title>
<style>
  body {{ font-family: Tahoma, Arial, sans-serif; background:#eef2f7; color:#111827; margin:0; line-height:1.7; }}
  .wrap {{ width:min(920px, calc(100% - 28px)); margin:0 auto; padding:24px 0; }}
  .card {{ background:#fff; border:1px solid #dbe3ee; border-radius:8px; padding:20px; margin-bottom:14px; }}
  h1 {{ margin:0 0 6px; font-size:1.5rem; }}
  .meta {{ color:#64748b; font-size:.9rem; }}
  .question {{ border-top:1px solid #e5e7eb; padding-top:16px; margin-top:16px; }}
  .option {{ display:flex; gap:8px; align-items:flex-start; padding:8px 0; }}
  textarea, input[type="text"] {{ width:100%; border:1px solid #dbe3ee; border-radius:8px; padding:10px; font-family:inherit; }}
  textarea {{ min-height:110px; resize:vertical; }}
  .attachments a {{ display:inline-flex; margin:6px 0 0 8px; padding:6px 10px; border-radius:8px; background:#eef2ff; color:#3730a3; }}
  button {{ min-height:42px; border:0; border-radius:8px; padding:9px 18px; font-weight:800; cursor:pointer; }}
  .primary {{ background:#2563eb; color:#fff; }}
  .ghost {{ background:#fff; border:1px solid #dbe3ee; color:#334155; }}
  .bar {{ display:flex; justify-content:space-between; gap:12px; flex-wrap:wrap; align-items:center; }}
  .timer {{ font-weight:800; color:#b45309; }}
  .result {{ display:none; background:#dcfce7; color:#166534; border:1px solid #86efac; border-radius:8px; padding:12px; margin-top:14px; }}
</style>
</head>
<body>
<main class="wrap">
  <section class="card">
    <div class="bar">
      <div>
        <h1>{safe_title}</h1>
        <p class="meta">{safe_instructions}</p>
        <div class="attachments" id="attachments"></div>
      </div>
      <div class="timer" id="timer"></div>
    </div>
  </section>
  <form id="examForm" class="card">
    <div id="questions"></div>
    <div class="bar" style="margin-top:18px;">
      <button type="submit" class="primary">ثبت پاسخ‌ها</button>
      <button type="button" class="ghost" id="clearBtn">پاک کردن پاسخ‌ها</button>
    </div>
    <div class="result" id="resultBox"></div>
  </form>
</main>
<script>
const questions = {payload};
const attachments = {attachment_payload};
const durationSeconds = {duration_seconds};
const startedAt = Date.now();
let violations = 0;

function renderQuestions() {{
  const target = document.getElementById("questions");
  document.getElementById("attachments").innerHTML = attachments.map(file => `<a href="${{file.url}}" target="_blank">${{escapeHtml(file.name)}}</a>`).join("");
  target.innerHTML = questions.map((q, qi) => `
    <div class="question">
      <strong>${{qi + 1}}. ${{escapeHtml(q.prompt)}} <span class="meta">(${{q.points || 0}})</span></strong>
      ${{renderInput(q, qi)}}
    </div>
  `).join("");
}}

function renderInput(q, qi) {{
  if (q.type === "essay") return `<textarea name="q${{qi}}" placeholder="پاسخ تشریحی"></textarea>`;
  if (q.type === "short") return `<input type="text" name="q${{qi}}" placeholder="پاسخ کوتاه">`;
  const inputType = q.type === "multiple" ? "checkbox" : "radio";
  return q.options.map((option, oi) => `
        <label class="option">
          <input type="${{inputType}}" name="q${{qi}}" value="${{oi}}">
          <span>${{escapeHtml(option)}}</span>
        </label>
      `).join("");
}}

function escapeHtml(value) {{
  return String(value).replace(/[&<>"']/g, ch => ({{"&":"&amp;","<":"&lt;",">":"&gt;","\\"":"&quot;","'":"&#39;"}}[ch]));
}}

function updateTimer() {{
  if (!durationSeconds) return;
  const elapsed = Math.floor((Date.now() - startedAt) / 1000);
  const remaining = Math.max(0, durationSeconds - elapsed);
  const min = Math.floor(remaining / 60);
  const sec = String(remaining % 60).padStart(2, "0");
  document.getElementById("timer").textContent = `${{min}}:${{sec}}`;
  if (remaining === 0) submitExam();
}}

function collectAnswers() {{
  return questions.map((q, qi) => {{
    const fields = [...document.querySelectorAll(`[name="q${{qi}}"]`)];
    let selected = null;
    let answer = "";
    if (q.type === "multiple") {{
      selected = fields.filter(field => field.checked).map(field => Number(field.value));
      answer = selected.map(index => q.options[index]).join(", ");
    }} else if (q.type === "short" || q.type === "essay") {{
      selected = fields[0]?.value.trim() || "";
      answer = selected;
    }} else {{
      const checked = fields.find(field => field.checked);
      selected = checked ? Number(checked.value) : null;
      answer = selected === null ? "" : q.options[selected];
    }}
    const correct = isCorrect(q, selected);
    return {{
      question: q.prompt,
      type: q.type,
      selected,
      answer,
      correctAnswer: correctAnswerText(q),
      correct,
      points: q.points || 0
    }};
  }});
}}

function isCorrect(q, selected) {{
  if (q.type === "essay") return false;
  if (q.type === "short") return String(selected || "").trim().toLowerCase() === String(q.correct || "").trim().toLowerCase();
  if (q.type === "multiple") {{
    const a = [...(selected || [])].sort().join(",");
    const b = [...(q.correct || [])].sort().join(",");
    return a === b;
  }}
  return selected === q.correct;
}}

function correctAnswerText(q) {{
  if (q.type === "short" || q.type === "essay") return q.correct || "";
  if (q.type === "multiple") return (q.correct || []).map(index => q.options[index]).join(", ");
  return q.options[q.correct] || "";
}}

async function submitExam(event) {{
  if (event) event.preventDefault();
  const answers = collectAnswers();
  const maxScore = questions.reduce((sum, q) => sum + (q.points || 0), 0);
  const correct = answers.reduce((sum, item) => sum + (item.correct ? item.points : 0), 0);
  const body = new URLSearchParams();
  const platform = window.EXAM_PLATFORM || {{}};
  body.set("student", platform.student?.fullName || "");
  body.set("student_code", platform.student?.code || "");
  body.set("score", String(correct));
  body.set("max_score", String(maxScore));
  body.set("details", `${{correct}} از ${{maxScore}} امتیاز`);
  body.set("duration_seconds", String(Math.floor((Date.now() - startedAt) / 1000)));
  body.set("violations", String(violations));
  body.set("answers_json", JSON.stringify(answers));
  const response = await fetch(platform.submitUrl || window.location.pathname.replace(/\\/start$/, "/submit"), {{
    method: "POST",
    headers: {{"Content-Type": "application/x-www-form-urlencoded"}},
    body
  }});
  if (response.ok) {{
    document.getElementById("resultBox").style.display = "block";
    document.getElementById("resultBox").textContent = `نتیجه ثبت شد: ${{correct}} از ${{maxScore}}`;
    document.querySelectorAll("button,input").forEach(el => el.disabled = true);
  }} else {{
    alert("خطا در ثبت نتیجه. لطفا دوباره تلاش کنید.");
  }}
}}

document.addEventListener("visibilitychange", () => {{ if (document.hidden) violations += 1; }});
document.getElementById("examForm").addEventListener("submit", submitExam);
document.getElementById("clearBtn").addEventListener("click", () => document.getElementById("examForm").reset());
renderQuestions();
if (durationSeconds) setInterval(updateTimer, 1000);
updateTimer();
</script>
</body>
</html>
"""


def get_csrf_token():
    token = session.get("_csrf_token")
    if not token:
        token = secrets.token_urlsafe(32)
        session["_csrf_token"] = token
    return token


@app.context_processor
def inject_csrf_token():
    student = None
    if session.get("student_id"):
        student = get_student_by_id(session["student_id"])
    return {
        "csrf_token": get_csrf_token,
        "school_name": app.config["SCHOOL_NAME"],
        "school_tagline": app.config["SCHOOL_TAGLINE"],
        "exam_state": exam_state,
        "format_datetime": format_datetime,
        "format_duration": format_duration,
        "status_label": status_label,
        "result_risk": result_risk,
        "current_student": student,
        "student_display_name": student_display_name,
    }


@app.before_request
def protect_form_posts():
    if request.method != "POST":
        return
    if request.path.startswith("/admin") or request.path.startswith("/student"):
        expected = session.get("_csrf_token") or ""
        submitted = request.form.get("csrf_token") or ""
        if not expected or not secrets.compare_digest(submitted, expected):
            abort(400)


def now_str():
    return datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")


def local_now():
    return datetime.now(app_timezone())


def local_now_key():
    return local_now().strftime("%Y-%m-%dT%H:%M")


def normalize_datetime(value):
    value = (value or "").strip()
    if not value:
        return ""
    try:
        parsed = datetime.fromisoformat(value.replace(" ", "T"))
    except ValueError:
        return ""
    return parsed.strftime("%Y-%m-%dT%H:%M")


def format_datetime(value):
    value = (value or "").strip()
    if not value:
        return "بدون محدودیت"
    try:
        parsed = datetime.fromisoformat(value.replace(" ", "T"))
    except ValueError:
        return value
    return parsed.strftime("%Y/%m/%d - %H:%M")


def exam_state(exam):
    now_key = local_now_key()
    if not exam["active"]:
        return "inactive", "غیرفعال"
    if exam["starts_at"] and exam["starts_at"] > now_key:
        return "upcoming", "در انتظار شروع"
    if exam["ends_at"] and exam["ends_at"] < now_key:
        return "ended", "پایان‌یافته"
    return "open", "در حال برگزاری"


def build_exam_platform_script(exam_id, student):
    payload = {
        "examId": exam_id,
        "submitUrl": url_for("submit_result", exam_id=exam_id),
        "student": {
            "id": student["id"],
            "code": student["student_code"],
            "firstName": student["first_name"],
            "lastName": student["last_name"],
            "fullName": student_display_name(student),
            "grade": student["grade"] or "",
        },
    }
    return (
        "<script>window.EXAM_PLATFORM="
        + json.dumps(payload, ensure_ascii=False)
        + ";</script>"
    )


def serve_exam_html(exam, student):
    path = exam_file_path(exam["filename"])
    if not path.exists():
        abort(404)
    html = path.read_text(encoding="utf-8")
    bootstrap = build_exam_platform_script(exam["id"], student)
    if "<head>" in html:
        html = html.replace("<head>", "<head>" + bootstrap, 1)
    elif "<body>" in html:
        html = html.replace("<body>", "<body>" + bootstrap, 1)
    else:
        html = bootstrap + html
    return Response(html, mimetype="text/html; charset=utf-8")


# ══════════════════════════════════════════════════════════════
#  PUBLIC ROUTES
# ══════════════════════════════════════════════════════════════

@app.route("/")
def index():
    selected_grade = request.args.get("grade", "").strip()
    selected_subject = request.args.get("subject", "").strip()
    where = ["active=1"]
    params = []
    if selected_grade:
        where.append("grade=?")
        params.append(selected_grade)
    if selected_subject:
        where.append("subject=?")
        params.append(selected_subject)

    conn = get_db()
    exams = conn.execute(
        "SELECT * FROM exams WHERE " + " AND ".join(where) + " ORDER BY sort_order, id",
        params
    ).fetchall()
    grades = conn.execute(
        "SELECT DISTINCT grade FROM exams WHERE active=1 AND grade<>'' ORDER BY grade"
    ).fetchall()
    subjects = conn.execute(
        "SELECT DISTINCT subject FROM exams WHERE active=1 AND subject<>'' ORDER BY subject"
    ).fetchall()
    conn.close()
    return render_template(
        "index.html",
        exams=exams,
        grades=grades,
        subjects=subjects,
        selected_grade=selected_grade,
        selected_subject=selected_subject,
    )


@app.route("/healthz")
def healthz():
    try:
        conn = get_db()
        conn.execute("SELECT 1").fetchone()
        conn.close()
    except sqlite3.Error:
        logger.exception("Health check failed")
        return {"status": "error"}, 500
    return {"status": "ok"}


@app.route("/exam/<int:exam_id>")
@student_required
def take_exam(exam_id):
    conn = get_db()
    exam = conn.execute(
        "SELECT * FROM exams WHERE id=? AND active=1", (exam_id,)
    ).fetchone()
    conn.close()

    if not exam:
        abort(404)

    student = get_student_by_id(session["student_id"])
    conn = get_db()
    already = conn.execute(
        "SELECT id FROM results WHERE exam_id=? AND student_id=?",
        (exam_id, student["id"]),
    ).fetchone()
    conn.close()

    return render_template(
        "exam_lobby.html",
        exam=exam,
        state=exam_state(exam)[0],
        already_submitted=bool(already),
    )


@app.route("/exam/<int:exam_id>/start")
@student_required
def start_exam(exam_id):
    conn = get_db()
    exam = conn.execute(
        "SELECT * FROM exams WHERE id=? AND active=1", (exam_id,)
    ).fetchone()
    conn.close()

    if not exam:
        abort(404)

    state, _ = exam_state(exam)
    if state != "open":
        flash("این آزمون در حال حاضر قابل شرکت نیست.", "warning")
        return redirect(url_for("take_exam", exam_id=exam_id))

    student = get_student_by_id(session["student_id"])
    conn = get_db()
    already = conn.execute(
        "SELECT id FROM results WHERE exam_id=? AND student_id=?",
        (exam_id, student["id"]),
    ).fetchone()
    conn.close()
    if already:
        flash("شما قبلاً در این آزمون شرکت کرده‌اید.", "info")
        return redirect(url_for("student_dashboard"))

    # Password-protected exam: check session token
    if exam["password"]:
        if session.get(f"exam_auth_{exam_id}") != exam_id:
            return redirect(url_for("exam_password", exam_id=exam_id))

    return serve_exam_html(exam, student)


@app.route("/exam/<int:exam_id>/password", methods=["GET", "POST"])
@student_required
def exam_password(exam_id):
    conn = get_db()
    exam = conn.execute(
        "SELECT id, title, password FROM exams WHERE id=? AND active=1", (exam_id,)
    ).fetchone()
    conn.close()

    if not exam:
        abort(404)

    if not exam["password"]:
        return redirect(url_for("take_exam", exam_id=exam_id))

    error = None
    if request.method == "POST":
        if request.form.get("password") == exam["password"]:
            session[f"exam_auth_{exam_id}"] = exam_id
            return redirect(url_for("start_exam", exam_id=exam_id))
        error = "رمز عبور اشتباه است."

    return render_template("exam_password.html", exam=exam, error=error)


@app.route("/exam/<int:exam_id>/submit", methods=["POST"])
def submit_result(exam_id):
    """Endpoint for HTML exam files to POST results back."""
    student_id = session.get("student_id")
    student_name = request.form.get("student", "ناشناس").strip()[:120]
    student_code = request.form.get("student_code", "").strip()[:40]
    score = request.form.get("score", "")[:200]
    max_score = request.form.get("max_score") or request.form.get("total_score") or request.form.get("out_of")
    details = request.form.get("details", "")[:8000]
    violations = safe_int(request.form.get("violations"), default=0, minimum=0, maximum=999)
    duration_seconds = safe_int(
        request.form.get("duration_seconds"), default=0, minimum=0, maximum=86400
    )
    answers_json = (request.form.get("answers_json") or "")[:50000]
    metrics = build_result_metrics(score, max_score, answers_json)
    ip_address = (request.headers.get("X-Forwarded-For", request.remote_addr or "").split(",")[0].strip())[:80]
    user_agent = (request.headers.get("User-Agent") or "")[:500]

    conn = get_db()
    exam = conn.execute(
        "SELECT * FROM exams WHERE id=? AND active=1", (exam_id,)
    ).fetchone()
    if not exam:
        conn.close()
        abort(404)
    if exam_state(exam)[0] != "open":
        conn.close()
        abort(403)

    if student_id:
        db_student = conn.execute(
            "SELECT * FROM students WHERE id=? AND active=1", (student_id,)
        ).fetchone()
        if db_student:
            student_name = student_display_name(db_student)
            student_code = db_student["student_code"]
            existing = conn.execute(
                "SELECT id FROM results WHERE exam_id=? AND student_id=?",
                (exam_id, student_id),
            ).fetchone()
            if existing:
                conn.close()
                return render_template(
                    "result_submitted.html",
                    student=student_name,
                    score=score,
                    duplicate=True,
                )

    conn.execute(
        "INSERT INTO results "
        "(exam_id, student_id, student, score, details, violations, duration_seconds, answers_json, "
        "score_numeric, max_score, percent_score, question_count, correct_count, incorrect_count, "
        "blank_count, ip_address, user_agent) "
        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (
            exam_id, student_id, f"{student_name} ({student_code})" if student_code else student_name,
            score, details, violations, duration_seconds, answers_json,
            metrics["score_numeric"], metrics["max_score"], metrics["percent_score"],
            metrics["question_count"], metrics["correct_count"], metrics["incorrect_count"],
            metrics["blank_count"], ip_address, user_agent,
        ),
    )
    conn.commit()
    conn.close()
    logger.info("Exam %s result from %s", exam_id, student_name)
    return render_template("result_submitted.html", student=student_name, score=score)


# ══════════════════════════════════════════════════════════════
#  STUDENT AUTH
# ══════════════════════════════════════════════════════════════

@app.route("/student/login", methods=["GET", "POST"])
def student_login():
    if session.get("student_id"):
        return redirect(url_for("student_dashboard"))

    error = None
    next_url = safe_next_url(request.args.get("next", ""))

    if request.method == "POST":
        ip = request.remote_addr or "unknown"
        next_url = safe_next_url(request.form.get("next", "")) or next_url

        if not _check_rate_limit(ip):
            error = "تعداد تلاش‌های ناموفق زیاد است. ۱۰ دقیقه صبر کنید."
        else:
            code = request.form.get("student_code", "").strip()
            password = request.form.get("password", "")
            conn = get_db()
            student = conn.execute(
                "SELECT * FROM students WHERE student_code=? AND active=1", (code,)
            ).fetchone()

            success = student and check_password_hash(student["password"], password)
            conn.execute(
                "INSERT INTO login_log (username, success, ip) VALUES (?,?,?)",
                (f"student:{code}", int(bool(success)), ip),
            )
            conn.commit()
            conn.close()

            if success:
                session["student_id"] = student["id"]
                session["student_code"] = student["student_code"]
                session["student_name"] = student_display_name(student)
                logger.info("Student login: %s from %s", code, ip)
                return redirect(next_url or url_for("student_dashboard"))

            error = "کد دانش‌آموزی یا رمز عبور اشتباه است."

    return render_template("student_login.html", error=error, next_url=next_url)


@app.route("/student/logout")
def student_logout():
    session.pop("student_id", None)
    session.pop("student_code", None)
    session.pop("student_name", None)
    flash("از حساب دانش‌آموزی خارج شدید.", "info")
    return redirect(url_for("index"))


@app.route("/student/dashboard")
@student_required
def student_dashboard():
    student = get_student_by_id(session["student_id"])
    conn = get_db()
    exams = conn.execute(
        "SELECT * FROM exams WHERE active=1 ORDER BY sort_order, id"
    ).fetchall()
    results = conn.execute(
        "SELECT r.*, e.title as exam_title, e.subject, e.grade "
        "FROM results r JOIN exams e ON e.id=r.exam_id "
        "WHERE r.student_id=? ORDER BY r.submitted_at DESC",
        (student["id"],),
    ).fetchall()
    submitted_ids = {
        row["exam_id"]
        for row in conn.execute(
            "SELECT exam_id FROM results WHERE student_id=?", (student["id"],)
        ).fetchall()
    }
    conn.close()
    return render_template(
        "student_dashboard.html",
        student=student,
        exams=exams,
        results=results,
        submitted_ids=submitted_ids,
    )


# ══════════════════════════════════════════════════════════════
#  ADMIN AUTH
# ══════════════════════════════════════════════════════════════

def _check_rate_limit(ip: str) -> bool:
    """Returns True if IP is allowed to try logging in (5 failed attempts / 10 min)."""
    conn = get_db()
    count = conn.execute(
        "SELECT COUNT(*) FROM login_log "
        "WHERE ip=? AND success=0 AND ts > datetime('now', '-10 minutes')",
        (ip,),
    ).fetchone()[0]
    conn.close()
    return count < 5


@app.route("/admin/login", methods=["GET", "POST"])
def admin_login():
    if session.get("admin_id"):
        return redirect(url_for("admin_dashboard"))

    error = None
    if request.method == "POST":
        ip = request.remote_addr or "unknown"

        if not _check_rate_limit(ip):
            error = "تعداد تلاش‌های ناموفق زیاد است. ۱۰ دقیقه صبر کنید."
        else:
            username = request.form.get("username", "").strip()
            password = request.form.get("password", "")
            conn = get_db()
            admin = conn.execute(
                "SELECT * FROM admins WHERE username=?", (username,)
            ).fetchone()

            success = admin and check_password_hash(admin["password"], password)
            conn.execute(
                "INSERT INTO login_log (username, success, ip) VALUES (?,?,?)",
                (username, int(bool(success)), ip)
            )
            conn.commit()
            conn.close()

            if success:
                session["admin_id"]   = admin["id"]
                session["admin_name"] = admin["username"]
                logger.info("Admin login: %s from %s", username, ip)
                return redirect(url_for("admin_dashboard"))

            error = "نام کاربری یا رمز عبور اشتباه است."

    return render_template("admin_login.html", error=error)


@app.route("/admin/logout")
def admin_logout():
    session.clear()
    flash("از سیستم خارج شدید.", "info")
    return redirect(url_for("admin_login"))


# ══════════════════════════════════════════════════════════════
#  ADMIN DASHBOARD
# ══════════════════════════════════════════════════════════════

@app.route("/admin/")
@app.route("/admin/dashboard")
@login_required
def admin_dashboard():
    conn = get_db()
    exams = conn.execute(
        "SELECT *, "
        "(SELECT COUNT(*) FROM results r WHERE r.exam_id=e.id) as result_count, "
        "(SELECT COUNT(*) FROM results r WHERE r.exam_id=e.id AND COALESCE(r.status, 'pending')='pending') as pending_count, "
        "(SELECT COUNT(*) FROM results r WHERE r.exam_id=e.id AND "
        "(COALESCE(r.violations,0) > 0 OR COALESCE(r.duration_seconds,0) < 60 OR COALESCE(r.score,'')='')) as flagged_count "
        "FROM exams e ORDER BY sort_order, id"
    ).fetchall()
    stats = {
        "total_exams": conn.execute("SELECT COUNT(*) FROM exams").fetchone()[0],
        "active_exams": conn.execute("SELECT COUNT(*) FROM exams WHERE active=1").fetchone()[0],
        "total_results": conn.execute("SELECT COUNT(*) FROM results").fetchone()[0],
        "total_students": conn.execute("SELECT COUNT(*) FROM students WHERE active=1").fetchone()[0],
        "pending_results": conn.execute(
            "SELECT COUNT(*) FROM results WHERE COALESCE(status, 'pending')='pending'"
        ).fetchone()[0],
        "flagged_results": conn.execute(
            "SELECT COUNT(*) FROM results WHERE "
            "COALESCE(violations,0) > 0 OR COALESCE(duration_seconds,0) < 60 OR COALESCE(score,'')=''"
        ).fetchone()[0],
        "open_exams": 0,
    }
    conn.close()
    stats["open_exams"] = sum(1 for exam in exams if exam_state(exam)[0] == "open")
    return render_template("admin_dashboard.html", exams=exams, stats=stats)


# ── Analytics API Endpoints ────────────────────────────────────

@app.route("/admin/exam-builder")
@login_required
def exam_builder():
    return render_template("admin_exam_builder.html")


@app.route("/admin/dashboard/stats")
@login_required
def dashboard_stats():
    """API endpoint for dashboard analytics data."""
    conn = get_db()

    # Get all results with student info
    all_results = conn.execute("""
        SELECT r.*, s.grade as student_grade
        FROM results r
        LEFT JOIN students s ON r.student_id = s.id
    """).fetchall()

    # Get all exams
    all_exams = conn.execute("SELECT * FROM exams").fetchall()

    # Calculate class/subject performance
    class_subject_stats = conn.execute("""
        SELECT
            e.grade,
            e.subject,
            COUNT(r.id) as result_count,
            AVG(CAST(REPLACE(REPLACE(r.score, ',', ''), ' ', '') AS FLOAT)) as avg_score,
            COUNT(CASE WHEN COALESCE(r.status, 'pending') = 'pending' THEN 1 END) as pending_count,
            COUNT(CASE WHEN (COALESCE(r.violations,0) > 0 OR COALESCE(r.duration_seconds,0) < 60 OR COALESCE(r.score,'')='') THEN 1 END) as flagged_count
        FROM results r
        JOIN exams e ON r.exam_id = e.id
        WHERE e.grade != '' AND e.subject != ''
        GROUP BY e.grade, e.subject
    """).fetchall()

    conn.close()

    # Calculate metrics
    score_values = []
    for r in all_results:
        try:
            score = float(str(r["score"]).replace(",", "").strip())
            score_values.append(score)
        except (ValueError, TypeError):
            pass

    avg_score = round(sum(score_values) / len(score_values), 2) if score_values else 0

    # 30-day trend - count results per day
    recent_results = [r for r in all_results if r["submitted_at"]]
    submission_trend = {}
    for r in recent_results:
        date = r["submitted_at"][:10]
        submission_trend[date] = submission_trend.get(date, 0) + 1

    return {
        "avg_score": avg_score,
        "avg_completion_rate": round(len(all_results) / max(len(all_exams), 1) * 100, 1) if all_exams else 0,
        "class_subject_performance": [
            {
                "grade": row["grade"],
                "subject": row["subject"],
                "avg_score": round(row["avg_score"] or 0, 2),
                "result_count": row["result_count"],
                "completion_rate": round(row["result_count"] / max(len(all_exams), 1) * 100, 1),
                "status": "green" if (row["avg_score"] or 0) >= 70 else ("yellow" if (row["avg_score"] or 0) >= 50 else "red"),
                "flagged_count": row["flagged_count"],
            }
            for row in class_subject_stats
        ],
        "submission_trend": sorted(submission_trend.items()),
    }


@app.route("/admin/dashboard/high-risk-students")
@login_required
def high_risk_students():
    """API endpoint for identifying struggling students."""
    conn = get_db()

    # Calculate average score across all results
    avg_result = conn.execute(
        "SELECT AVG(CAST(REPLACE(REPLACE(score, ',', ''), ' ', '') AS FLOAT)) as avg_score FROM results WHERE score != ''"
    ).fetchone()
    global_avg = float(avg_result["avg_score"]) if avg_result["avg_score"] else 0

    # Find students below average
    students_at_risk = conn.execute("""
        SELECT
            s.id,
            s.student_code,
            s.first_name,
            s.last_name,
            s.grade,
            AVG(CAST(REPLACE(REPLACE(r.score, ',', ''), ' ', '') AS FLOAT)) as avg_score,
            COUNT(r.id) as exam_count,
            COUNT(CASE WHEN COALESCE(r.status, 'pending') = 'pending' THEN 1 END) as pending_count
        FROM students s
        LEFT JOIN results r ON s.id = r.student_id
        WHERE s.active = 1
        GROUP BY s.id
        HAVING COUNT(r.id) > 0 AND AVG(CAST(REPLACE(REPLACE(r.score, ',', ''), ' ', '') AS FLOAT)) < ?
        ORDER BY avg_score ASC
        LIMIT 10
    """, (global_avg,)).fetchall()

    conn.close()

    return {
        "global_average": round(global_avg, 2),
        "at_risk_students": [
            {
                "id": row["id"],
                "code": row["student_code"],
                "name": f"{row['first_name']} {row['last_name']}".strip(),
                "grade": row["grade"],
                "avg_score": round(row["avg_score"] or 0, 2),
                "exam_count": row["exam_count"],
                "pending_reviews": row["pending_count"],
            }
            for row in students_at_risk
        ],
    }


@app.route("/admin/add", methods=["POST"])
@login_required
def add_exam():
    title       = request.form.get("title", "").strip()
    description = request.form.get("description", "").strip()[:500]
    subject     = request.form.get("subject", "").strip()[:80]
    grade       = request.form.get("grade", "").strip()[:80]
    teacher     = request.form.get("teacher", "").strip()[:80]
    instructions = request.form.get("instructions", "").strip()[:1200]
    duration_minutes = safe_int(request.form.get("duration_minutes"), default=0, minimum=0, maximum=600)
    starts_at   = normalize_datetime(request.form.get("starts_at"))
    ends_at     = normalize_datetime(request.form.get("ends_at"))
    active      = 1 if request.form.get("active") else 0
    password    = request.form.get("password", "").strip()
    sort_order  = safe_int(request.form.get("sort_order"), default=0, minimum=0)
    file        = request.files.get("file")

    if not title:
        flash("عنوان آزمون الزامی است.", "danger")
        return redirect(url_for("admin_dashboard"))

    if not file or not allowed_file(file.filename):
        flash("فقط فایل‌های HTML مجاز هستند.", "danger")
        return redirect(url_for("admin_dashboard"))

    if starts_at and ends_at and ends_at <= starts_at:
        flash("زمان پایان باید بعد از زمان شروع باشد.", "danger")
        return redirect(url_for("admin_dashboard"))

    filename = unique_exam_filename(file.filename)
    if not filename:
        flash("نام فایل معتبر نیست.", "danger")
        return redirect(url_for("admin_dashboard"))
    file.save(exam_file_path(filename))

    conn = get_db()
    conn.execute(
        "INSERT INTO exams (title, filename, description, password, active, sort_order, "
        "subject, grade, teacher, duration_minutes, starts_at, ends_at, instructions) "
        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (
            title, filename, description, password, active, sort_order,
            subject, grade, teacher, duration_minutes, starts_at, ends_at, instructions,
        )
    )
    conn.commit()
    conn.close()
    flash(f"آزمون «{title}» با موفقیت اضافه شد.", "success")
    return redirect(url_for("admin_dashboard"))


@app.route("/admin/generate", methods=["POST"])
@login_required
def generate_exam():
    title       = request.form.get("title", "").strip()
    description = request.form.get("description", "").strip()[:500]
    subject     = request.form.get("subject", "").strip()[:80]
    grade       = request.form.get("grade", "").strip()[:80]
    teacher     = request.form.get("teacher", "").strip()[:80]
    instructions = request.form.get("instructions", "").strip()[:1200]
    duration_minutes = safe_int(request.form.get("duration_minutes"), default=0, minimum=0, maximum=600)
    starts_at   = normalize_datetime(request.form.get("starts_at"))
    ends_at     = normalize_datetime(request.form.get("ends_at"))
    active      = 1 if request.form.get("active") else 0
    password    = request.form.get("password", "").strip()
    sort_order  = safe_int(request.form.get("sort_order"), default=0, minimum=0)
    question_file = request.files.get("question_file")
    html_file = request.files.get("html_file")
    pdf_files = request.files.getlist("pdf_files")
    source_text = request.form.get("questions", "")
    questions_json = request.form.get("questions_json", "")
    has_html_file = bool(html_file and html_file.filename)

    if not title:
        flash("Exam title is required.", "danger")
        return redirect(url_for("admin_dashboard"))
    if starts_at and ends_at and ends_at <= starts_at:
        flash("Exam end time must be after start time.", "danger")
        return redirect(url_for("admin_dashboard"))

    if has_html_file:
        if not allowed_file(html_file.filename):
            flash("Ready-made forms must be HTML files.", "danger")
            return redirect(url_for("exam_builder"))
        filename = unique_exam_filename(html_file.filename)
        html_file.save(exam_file_path(filename))
        questions = []
    elif question_file and question_file.filename:
        raw = question_file.read(250000)
        try:
            source_text = raw.decode("utf-8-sig")
        except UnicodeDecodeError:
            source_text = raw.decode("cp1256", errors="ignore")

    if not has_html_file:
        questions = normalize_builder_questions(questions_json) or parse_generated_exam_questions(source_text)
    if not has_html_file and not questions:
        flash("Enter at least one valid question. Format: question | option 1 | option 2 | correct option", "danger")
        return redirect(url_for("exam_builder"))

    attachments = []
    for pdf in pdf_files:
        if not pdf or not pdf.filename:
            continue
        if not allowed_extension(pdf.filename, {"pdf"}):
            flash("Only PDF files are allowed as resources.", "danger")
            return redirect(url_for("exam_builder"))
        asset_name = unique_asset_filename(pdf.filename)
        pdf.save(asset_file_path(asset_name))
        attachments.append({
            "name": pdf.filename[:160],
            "url": url_for("exam_asset", filename=asset_name),
        })

    if not has_html_file:
        filename = unique_exam_filename(f"{title}.html") or unique_exam_filename("generated_exam.html")
        html = build_generated_exam_html(title, instructions, questions, duration_minutes, attachments)
        exam_file_path(filename).write_text(html, encoding="utf-8")

    conn = get_db()
    conn.execute(
        "INSERT INTO exams (title, filename, description, password, active, sort_order, "
        "subject, grade, teacher, duration_minutes, starts_at, ends_at, instructions) "
        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (
            title, filename, description, password, active, sort_order,
            subject, grade, teacher, duration_minutes, starts_at, ends_at, instructions,
        )
    )
    conn.commit()
    conn.close()
    flash(f"Generated exam created with {len(questions)} questions.", "success")
    return redirect(url_for("admin_dashboard"))


@app.route("/admin/edit/<int:exam_id>", methods=["GET", "POST"])
@login_required
def edit_exam(exam_id):
    conn = get_db()
    exam = conn.execute("SELECT * FROM exams WHERE id=?", (exam_id,)).fetchone()
    if not exam:
        conn.close()
        abort(404)

    if request.method == "POST":
        title       = request.form.get("title", "").strip()
        description = request.form.get("description", "").strip()[:500]
        subject     = request.form.get("subject", "").strip()[:80]
        grade       = request.form.get("grade", "").strip()[:80]
        teacher     = request.form.get("teacher", "").strip()[:80]
        instructions = request.form.get("instructions", "").strip()[:1200]
        duration_minutes = safe_int(request.form.get("duration_minutes"), default=0, minimum=0, maximum=600)
        starts_at   = normalize_datetime(request.form.get("starts_at"))
        ends_at     = normalize_datetime(request.form.get("ends_at"))
        active      = 1 if request.form.get("active") else 0
        password    = request.form.get("password", "").strip()
        sort_order  = safe_int(request.form.get("sort_order"), default=0, minimum=0)

        if not title:
            flash("عنوان آزمون الزامی است.", "danger")
            conn.close()
            return redirect(url_for("edit_exam", exam_id=exam_id))

        if starts_at and ends_at and ends_at <= starts_at:
            flash("زمان پایان باید بعد از زمان شروع باشد.", "danger")
            conn.close()
            return redirect(url_for("edit_exam", exam_id=exam_id))

        # Optional new file upload
        file = request.files.get("file")
        filename = exam["filename"]
        if file and file.filename and allowed_file(file.filename):
            new_filename = unique_exam_filename(file.filename)
            if not new_filename:
                flash("نام فایل معتبر نیست.", "danger")
                conn.close()
                return redirect(url_for("edit_exam", exam_id=exam_id))
            file.save(exam_file_path(new_filename))
            # Remove old file
            old = exam_file_path(filename)
            if old.exists():
                old.unlink()
            filename = new_filename

        conn.execute(
            "UPDATE exams SET title=?, description=?, active=?, password=?, "
            "sort_order=?, filename=?, subject=?, grade=?, teacher=?, duration_minutes=?, "
            "starts_at=?, ends_at=?, instructions=?, updated_at=? WHERE id=?",
            (
                title, description, active, password, sort_order, filename,
                subject, grade, teacher, duration_minutes, starts_at, ends_at,
                instructions, now_str(), exam_id,
            )
        )
        conn.commit()
        conn.close()
        flash(f"آزمون «{title}» ویرایش شد.", "success")
        return redirect(url_for("admin_dashboard"))

    conn.close()
    return render_template("admin_edit.html", exam=exam)


@app.route("/admin/toggle/<int:exam_id>", methods=["POST"])
@login_required
def toggle_exam(exam_id):
    conn = get_db()
    conn.execute(
        "UPDATE exams SET active = CASE WHEN active=1 THEN 0 ELSE 1 END, "
        "updated_at=? WHERE id=?",
        (now_str(), exam_id)
    )
    conn.commit()
    conn.close()
    flash("وضعیت آزمون تغییر کرد.", "info")
    return redirect(url_for("admin_dashboard"))


@app.route("/admin/delete/<int:exam_id>", methods=["POST"])
@login_required
def delete_exam(exam_id):
    conn = get_db()
    exam = conn.execute("SELECT * FROM exams WHERE id=?", (exam_id,)).fetchone()
    if exam:
        filepath = exam_file_path(exam["filename"])
        if filepath.exists():
            filepath.unlink()
        conn.execute("DELETE FROM exams WHERE id=?", (exam_id,))
        conn.execute("DELETE FROM results WHERE exam_id=?", (exam_id,))
        conn.commit()
        flash(f"آزمون «{exam['title']}» حذف شد.", "info")
    conn.close()
    return redirect(url_for("admin_dashboard"))


@app.route("/admin/results/<int:exam_id>")
@login_required
def exam_results(exam_id):
    page = safe_int(request.args.get("page"), default=1, minimum=1)
    per_page = 25
    offset  = (page - 1) * per_page
    status_filter = request.args.get("status", "all").strip()
    if status_filter not in {"all", "pending", "approved", "rejected"}:
        status_filter = "all"
    query = request.args.get("q", "").strip()[:80]
    suspicious_only = request.args.get("suspicious") == "1"

    conn = get_db()
    exam    = conn.execute("SELECT * FROM exams WHERE id=?", (exam_id,)).fetchone()
    if not exam:
        conn.close()
        abort(404)

    stats_rows = conn.execute(
        "SELECT COALESCE(status, 'pending') as status, COUNT(*) as count "
        "FROM results WHERE exam_id=? GROUP BY COALESCE(status, 'pending')",
        (exam_id,),
    ).fetchall()
    status_counts = {"pending": 0, "approved": 0, "rejected": 0}
    for row in stats_rows:
        status_counts[row["status"]] = row["count"]
    total_all = sum(status_counts.values())
    flagged_count = conn.execute(
        "SELECT COUNT(*) FROM results WHERE exam_id=? AND "
        "(COALESCE(violations,0) > 0 OR COALESCE(duration_seconds,0) < 60 OR COALESCE(score,'')='')",
        (exam_id,),
    ).fetchone()[0]

    where = ["r.exam_id=?"]
    params = [exam_id]
    if status_filter != "all":
        where.append("COALESCE(r.status, 'pending')=?")
        params.append(status_filter)
    if query:
        like = f"%{query}%"
        where.append(
            "(r.student LIKE ? OR s.student_code LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR r.score LIKE ?)"
        )
        params.extend([like, like, like, like, like])
    if suspicious_only:
        where.append(
            "(COALESCE(r.violations,0) > 0 OR COALESCE(r.duration_seconds,0) < 60 OR COALESCE(r.score,'')='')"
        )
    where_sql = " AND ".join(where)

    total   = conn.execute(
        "SELECT COUNT(*) FROM results r LEFT JOIN students s ON s.id=r.student_id WHERE " + where_sql,
        params,
    ).fetchone()[0]
    results = conn.execute(
        "SELECT r.*, s.student_code, s.first_name, s.last_name "
        "FROM results r LEFT JOIN students s ON s.id=r.student_id "
        "WHERE " + where_sql + " ORDER BY "
        "CASE COALESCE(r.status, 'pending') WHEN 'pending' THEN 0 WHEN 'rejected' THEN 1 ELSE 2 END, "
        "COALESCE(r.violations,0) DESC, r.submitted_at DESC LIMIT ? OFFSET ?",
        params + [per_page, offset],
    ).fetchall()
    conn.close()

    score_values = [
        value for value in (score_to_float(row["score"]) for row in results)
        if value is not None
    ]
    page_stats = {
        "average_score": round(sum(score_values) / len(score_values), 2) if score_values else None,
        "highest_score": max(score_values) if score_values else None,
        "flagged_count": flagged_count,
    }
    pages = (total + per_page - 1) // per_page
    return render_template(
        "admin_results.html",
        exam=exam, results=results,
        page=page, pages=pages, total=total, total_all=total_all,
        status_counts=status_counts, status_filter=status_filter,
        query=query, suspicious_only=suspicious_only, page_stats=page_stats,
    )


@app.route("/admin/results/<int:exam_id>/export")
@login_required
def export_results(exam_id):
    """Export results as CSV download."""
    from flask import Response

    conn = get_db()
    exam    = conn.execute("SELECT * FROM exams WHERE id=?", (exam_id,)).fetchone()
    if not exam:
        conn.close()
        abort(404)
    results = conn.execute(
        "SELECT r.student, r.score, r.details, r.submitted_at, r.violations, "
        "r.duration_seconds, r.status, r.admin_note, s.student_code "
        "FROM results r LEFT JOIN students s ON s.id=r.student_id "
        "WHERE r.exam_id=? ORDER BY r.submitted_at DESC",
        (exam_id,),
    ).fetchall()
    conn.close()

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(["student_code", "student", "score", "status", "admin_note", "violations", "duration_seconds", "details", "submitted_at"])
    for r in results:
        writer.writerow([
            r["student_code"] or "",
            r["student"],
            r["score"],
            r["status"] or "pending",
            r["admin_note"] or "",
            r["violations"] or 0,
            r["duration_seconds"] or 0,
            r["details"],
            r["submitted_at"],
        ])

    filename = secure_filename(f"results_{exam['title']}_{now_str()}.csv")
    return Response(
        "\ufeff" + buf.getvalue(),   # BOM for Excel RTL
        mimetype="text/csv",
        headers={"Content-Disposition": f"attachment; filename={filename}"}
    )


@app.route("/admin/exams/<int:exam_id>/report")
@login_required
def exam_report(exam_id):
    """Detailed exam performance report with analytics."""
    conn = get_db()
    exam = conn.execute("SELECT * FROM exams WHERE id=?", (exam_id,)).fetchone()
    if not exam:
        conn.close()
        abort(404)

    results = conn.execute(
        "SELECT r.*, s.student_code, s.first_name, s.last_name, s.grade "
        "FROM results r LEFT JOIN students s ON r.student_id=s.id "
        "WHERE r.exam_id=? ORDER BY r.score DESC",
        (exam_id,)
    ).fetchall()
    conn.close()

    # Calculate statistics
    score_values = []
    for r in results:
        try:
            score = float(str(r["score"]).replace(",", "").strip())
            score_values.append(score)
        except (ValueError, TypeError):
            pass

    stats = {
        "total_results": len(results),
        "pending": sum(1 for r in results if not r["status"] or r["status"] == "pending"),
        "approved": sum(1 for r in results if r["status"] == "approved"),
        "rejected": sum(1 for r in results if r["status"] == "rejected"),
        "avg_score": round(sum(score_values) / len(score_values), 2) if score_values else None,
        "min_score": min(score_values) if score_values else None,
        "max_score": max(score_values) if score_values else None,
        "avg_duration": round(sum((r["duration_seconds"] or 0) for r in results) / max(len(results), 1), 0),
        "flagged_count": sum(1 for r in results if (r["violations"] or 0) > 0 or (r["duration_seconds"] or 0) < 60),
    }

    # Score distribution buckets
    buckets = {0: 0, 20: 0, 40: 0, 60: 0, 80: 0, 100: 0}
    for score in score_values:
        if score < 20:
            buckets[0] += 1
        elif score < 40:
            buckets[20] += 1
        elif score < 60:
            buckets[40] += 1
        elif score < 80:
            buckets[60] += 1
        elif score < 100:
            buckets[80] += 1
        else:
            buckets[100] += 1

    return render_template("admin_exam_report.html", exam=exam, results=results, stats=stats, score_buckets=buckets)


@app.route("/admin/results")
@login_required
def all_results():
    selected_grade = request.args.get("grade", "").strip()[:80]
    selected_subject = request.args.get("subject", "").strip()[:80]
    selected_status = request.args.get("status", "all").strip()
    if selected_status not in {"all", "pending", "approved", "rejected"}:
        selected_status = "all"
    query = request.args.get("q", "").strip()[:80]

    where = ["1=1"]
    params = []
    if selected_grade:
        where.append("e.grade=?")
        params.append(selected_grade)
    if selected_subject:
        where.append("e.subject=?")
        params.append(selected_subject)
    if selected_status != "all":
        where.append("COALESCE(r.status, 'pending')=?")
        params.append(selected_status)
    if query:
        like = f"%{query}%"
        where.append(
            "(r.student LIKE ? OR s.student_code LIKE ? OR e.title LIKE ? OR e.teacher LIKE ? OR r.score LIKE ?)"
        )
        params.extend([like, like, like, like, like])
    where_sql = " AND ".join(where)

    conn = get_db()
    grades = conn.execute(
        "SELECT DISTINCT grade FROM exams WHERE grade<>'' ORDER BY grade"
    ).fetchall()
    subjects = conn.execute(
        "SELECT DISTINCT subject FROM exams WHERE subject<>'' ORDER BY subject"
    ).fetchall()
    results = conn.execute(
        "SELECT r.*, e.title as exam_title, e.subject, e.grade, e.teacher, "
        "s.student_code, s.first_name, s.last_name "
        "FROM results r JOIN exams e ON e.id=r.exam_id "
        "LEFT JOIN students s ON s.id=r.student_id "
        "WHERE " + where_sql + " "
        "ORDER BY e.grade, e.subject, e.title, r.submitted_at DESC",
        params,
    ).fetchall()
    conn.close()

    score_values = [
        value for value in (score_to_float(row["score"]) for row in results)
        if value is not None
    ]
    status_counts = {"pending": 0, "approved": 0, "rejected": 0}
    flagged_count = 0
    groups = {}
    for row in results:
        status = row["status"] or "pending"
        if status in status_counts:
            status_counts[status] += 1
        risks = result_risk(row)
        if risks:
            flagged_count += 1

        key = (row["grade"] or "No class", row["subject"] or "No subject")
        group = groups.setdefault(key, {
            "grade": key[0],
            "subject": key[1],
            "count": 0,
            "pending": 0,
            "approved": 0,
            "rejected": 0,
            "flagged": 0,
            "scores": [],
            "exams": {},
        })
        group["count"] += 1
        if status in {"pending", "approved", "rejected"}:
            group[status] += 1
        if risks:
            group["flagged"] += 1
        score_value = score_to_float(row["score"])
        if score_value is not None:
            group["scores"].append(score_value)

        exam_group = group["exams"].setdefault(row["exam_id"], {
            "id": row["exam_id"],
            "title": row["exam_title"],
            "teacher": row["teacher"] or "",
            "count": 0,
            "scores": [],
            "flagged": 0,
        })
        exam_group["count"] += 1
        if score_value is not None:
            exam_group["scores"].append(score_value)
        if risks:
            exam_group["flagged"] += 1

    grouped_results = []
    for group in groups.values():
        scores = group.pop("scores")
        group["average_score"] = round(sum(scores) / len(scores), 2) if scores else None
        group["highest_score"] = max(scores) if scores else None
        group["exams"] = list(group["exams"].values())
        for exam_group in group["exams"]:
            scores = exam_group.pop("scores")
            exam_group["average_score"] = round(sum(scores) / len(scores), 2) if scores else None
            exam_group["highest_score"] = max(scores) if scores else None
        grouped_results.append(group)

    overall = {
        "total": len(results),
        "average_score": round(sum(score_values) / len(score_values), 2) if score_values else None,
        "highest_score": max(score_values) if score_values else None,
        "flagged": flagged_count,
    }
    return render_template(
        "admin_all_results.html",
        results=results,
        grouped_results=grouped_results,
        grades=grades,
        subjects=subjects,
        selected_grade=selected_grade,
        selected_subject=selected_subject,
        selected_status=selected_status,
        query=query,
        status_counts=status_counts,
        overall=overall,
    )


@app.route("/admin/results/export")
@login_required
def export_all_results():
    selected_grade = request.args.get("grade", "").strip()[:80]
    selected_subject = request.args.get("subject", "").strip()[:80]
    selected_status = request.args.get("status", "all").strip()
    if selected_status not in {"all", "pending", "approved", "rejected"}:
        selected_status = "all"
    query = request.args.get("q", "").strip()[:80]

    where = ["1=1"]
    params = []
    if selected_grade:
        where.append("e.grade=?")
        params.append(selected_grade)
    if selected_subject:
        where.append("e.subject=?")
        params.append(selected_subject)
    if selected_status != "all":
        where.append("COALESCE(r.status, 'pending')=?")
        params.append(selected_status)
    if query:
        like = f"%{query}%"
        where.append(
            "(r.student LIKE ? OR s.student_code LIKE ? OR e.title LIKE ? OR e.teacher LIKE ? OR r.score LIKE ?)"
        )
        params.extend([like, like, like, like, like])

    conn = get_db()
    rows = conn.execute(
        "SELECT e.grade, e.subject, e.title as exam_title, e.teacher, "
        "s.student_code, r.student, r.score, r.status, r.admin_note, r.violations, "
        "r.duration_seconds, r.details, r.submitted_at "
        "FROM results r JOIN exams e ON e.id=r.exam_id "
        "LEFT JOIN students s ON s.id=r.student_id "
        "WHERE " + " AND ".join(where) + " "
        "ORDER BY e.grade, e.subject, e.title, r.submitted_at DESC",
        params,
    ).fetchall()
    conn.close()

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow([
        "class", "subject", "exam", "teacher", "student_code", "student",
        "score", "status", "admin_note", "violations", "duration_seconds",
        "details", "submitted_at",
    ])
    for row in rows:
        writer.writerow([
            row["grade"] or "",
            row["subject"] or "",
            row["exam_title"] or "",
            row["teacher"] or "",
            row["student_code"] or "",
            row["student"] or "",
            row["score"] or "",
            row["status"] or "pending",
            row["admin_note"] or "",
            row["violations"] or 0,
            row["duration_seconds"] or 0,
            row["details"] or "",
            row["submitted_at"] or "",
        ])

    filename = secure_filename(f"all_results_{now_str()}.csv")
    return Response(
        "\ufeff" + buf.getvalue(),
        mimetype="text/csv",
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )


@app.route("/admin/settings", methods=["GET", "POST"])
@login_required
def admin_settings():
    error = success = None
    if request.method == "POST":
        current  = request.form.get("current_password", "")
        new_pw   = request.form.get("new_password", "")
        confirm  = request.form.get("confirm_password", "")

        if new_pw != confirm:
            error = "رمز عبور جدید و تکرار آن یکسان نیستند."
        elif len(new_pw) < 8:
            error = "رمز عبور باید حداقل ۸ کاراکتر باشد."
        else:
            conn = get_db()
            admin = conn.execute(
                "SELECT * FROM admins WHERE id=?", (session["admin_id"],)
            ).fetchone()
            if not check_password_hash(admin["password"], current):
                error = "رمز عبور فعلی اشتباه است."
            else:
                conn.execute(
                    "UPDATE admins SET password=? WHERE id=?",
                    (generate_password_hash(new_pw), session["admin_id"])
                )
                conn.commit()
                success = "رمز عبور با موفقیت تغییر کرد."
            conn.close()

    return render_template("admin_settings.html", error=error, success=success)


# ══════════════════════════════════════════════════════════════
#  ADMIN — STUDENTS
# ══════════════════════════════════════════════════════════════

@app.route("/admin/students")
@login_required
def admin_students():
    conn = get_db()
    students = conn.execute(
        "SELECT s.*, "
        "(SELECT COUNT(*) FROM results r WHERE r.student_id=s.id) as result_count "
        "FROM students s ORDER BY s.grade, s.last_name, s.first_name"
    ).fetchall()
    conn.close()
    return render_template("admin_students.html", students=students)


@app.route("/admin/students/add", methods=["POST"])
@login_required
def add_student():
    code = request.form.get("student_code", "").strip()
    first_name = request.form.get("first_name", "").strip()
    last_name = request.form.get("last_name", "").strip()
    grade = request.form.get("grade", "").strip()[:80]
    password = request.form.get("password", "").strip() or code

    if not code or not first_name or not last_name:
        flash("کد، نام و نام خانوادگی الزامی است.", "danger")
        return redirect(url_for("admin_students"))
    if len(password) < 4:
        flash("رمز عبور باید حداقل ۴ کاراکتر باشد.", "danger")
        return redirect(url_for("admin_students"))

    conn = get_db()
    try:
        conn.execute(
            "INSERT INTO students (student_code, first_name, last_name, grade, password) "
            "VALUES (?,?,?,?,?)",
            (code, first_name, last_name, grade, generate_password_hash(password)),
        )
        conn.commit()
        flash(f"دانش‌آموز «{first_name} {last_name}» ثبت شد.", "success")
    except sqlite3.IntegrityError:
        flash("این کد دانش‌آموزی قبلاً ثبت شده است.", "danger")
    conn.close()
    return redirect(url_for("admin_students"))


@app.route("/admin/students/import", methods=["POST"])
@login_required
def import_students():
    file = request.files.get("file")
    update_existing = bool(request.form.get("update_existing"))
    if not file or not file.filename:
        flash("لطفاً فایل CSV دانش‌آموزان را انتخاب کنید.", "danger")
        return redirect(url_for("admin_students"))
    if not file.filename.lower().endswith(".csv"):
        flash("برای ورود گروهی فقط فایل CSV مجاز است.", "danger")
        return redirect(url_for("admin_students"))

    try:
        students = parse_student_import(file)
    except UnicodeDecodeError:
        flash("فایل CSV باید با کدگذاری UTF-8 ذخیره شده باشد.", "danger")
        return redirect(url_for("admin_students"))
    except csv.Error:
        flash("فایل CSV قابل خواندن نیست.", "danger")
        return redirect(url_for("admin_students"))

    created = updated = skipped = 0
    conn = get_db()
    for student in students:
        if (
            not student["student_code"]
            or not student["first_name"]
            or not student["last_name"]
            or len(student["password"]) < 4
        ):
            skipped += 1
            continue

        existing = conn.execute(
            "SELECT id FROM students WHERE student_code=?", (student["student_code"],)
        ).fetchone()
        password_hash = generate_password_hash(student["password"])
        if existing:
            if update_existing:
                conn.execute(
                    "UPDATE students SET first_name=?, last_name=?, grade=?, password=?, active=1 "
                    "WHERE student_code=?",
                    (
                        student["first_name"], student["last_name"], student["grade"],
                        password_hash, student["student_code"],
                    ),
                )
                updated += 1
            else:
                skipped += 1
            continue

        conn.execute(
            "INSERT INTO students (student_code, first_name, last_name, grade, password) "
            "VALUES (?,?,?,?,?)",
            (
                student["student_code"], student["first_name"], student["last_name"],
                student["grade"], password_hash,
            ),
        )
        created += 1
    conn.commit()
    conn.close()

    flash(f"ورود گروهی تمام شد: {created} دانش‌آموز جدید، {updated} به‌روزرسانی، {skipped} ردیف رد شد.", "success")
    return redirect(url_for("admin_students"))


@app.route("/admin/students/export")
@login_required
def export_students():
    from flask import Response

    conn = get_db()
    students = conn.execute(
        "SELECT student_code, first_name, last_name, grade, active, created_at "
        "FROM students ORDER BY grade, last_name, first_name"
    ).fetchall()
    conn.close()

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(["student_code", "first_name", "last_name", "grade", "active", "created_at"])
    for s in students:
        writer.writerow([
            s["student_code"], s["first_name"], s["last_name"], s["grade"],
            "yes" if s["active"] else "no", s["created_at"],
        ])
    return Response(
        "\ufeff" + buf.getvalue(),
        mimetype="text/csv",
        headers={"Content-Disposition": f"attachment; filename=students_{now_str()}.csv"},
    )


@app.route("/admin/students/reset-password/<int:student_id>", methods=["POST"])
@login_required
def reset_student_password(student_id):
    password = request.form.get("password", "").strip()
    if len(password) < 4:
        flash("رمز عبور باید حداقل ۴ کاراکتر باشد.", "danger")
        return redirect(url_for("admin_students"))

    conn = get_db()
    student = conn.execute("SELECT student_code FROM students WHERE id=?", (student_id,)).fetchone()
    if not student:
        conn.close()
        abort(404)
    conn.execute(
        "UPDATE students SET password=? WHERE id=?",
        (generate_password_hash(password), student_id),
    )
    conn.commit()
    conn.close()
    flash(f"رمز عبور دانش‌آموز با کد {student['student_code']} تغییر کرد.", "success")
    return redirect(url_for("admin_students"))


@app.route("/admin/students/toggle/<int:student_id>", methods=["POST"])
@login_required
def toggle_student(student_id):
    conn = get_db()
    conn.execute(
        "UPDATE students SET active = CASE WHEN active=1 THEN 0 ELSE 1 END WHERE id=?",
        (student_id,),
    )
    conn.commit()
    conn.close()
    flash("وضعیت دانش‌آموز تغییر کرد.", "info")
    return redirect(url_for("admin_students"))


@app.route("/admin/students/delete/<int:student_id>", methods=["POST"])
@login_required
def delete_student(student_id):
    conn = get_db()
    conn.execute("DELETE FROM students WHERE id=?", (student_id,))
    conn.commit()
    conn.close()
    flash("دانش‌آموز حذف شد.", "info")
    return redirect(url_for("admin_students"))


@app.route("/admin/results/delete/<int:result_id>", methods=["POST"])
@login_required
def delete_result(result_id):
    conn = get_db()
    row = conn.execute("SELECT exam_id FROM results WHERE id=?", (result_id,)).fetchone()
    if row:
        exam_id = row["exam_id"]
        conn.execute("DELETE FROM results WHERE id=?", (result_id,))
        conn.commit()
        conn.close()
        flash("نتیجه حذف شد.", "info")
        return redirect(url_for("exam_results", exam_id=exam_id))
    conn.close()
    abort(404)


# ── Error handlers ─────────────────────────────────────────────
@app.route("/admin/results/review/<int:result_id>", methods=["POST"])
@login_required
def review_result(result_id):
    status = request.form.get("status", "pending").strip()
    if status not in {"pending", "approved", "rejected"}:
        abort(400)
    admin_note = request.form.get("admin_note", "").strip()[:1000]

    conn = get_db()
    row = conn.execute("SELECT exam_id FROM results WHERE id=?", (result_id,)).fetchone()
    if not row:
        conn.close()
        abort(404)

    conn.execute(
        "UPDATE results SET status=?, admin_note=?, reviewed_at=? WHERE id=?",
        (status, admin_note, now_str(), result_id),
    )
    conn.commit()
    conn.close()
    flash("بررسی نتیجه ذخیره شد.", "success")
    return redirect(url_for("exam_results", exam_id=row["exam_id"]))


@app.route("/admin/results/<int:exam_id>/bulk-review", methods=["POST"])
@login_required
def bulk_review_results(exam_id):
    status = request.form.get("status", "").strip()
    if status not in {"approved", "rejected", "pending"}:
        abort(400)
    result_ids = [
        safe_int(value, default=0, minimum=0)
        for value in request.form.getlist("result_ids")
    ]
    result_ids = [value for value in result_ids if value]
    if not result_ids:
        flash("هیچ نتیجه‌ای انتخاب نشده است.", "warning")
        return redirect(url_for("exam_results", exam_id=exam_id))

    placeholders = ",".join("?" for _ in result_ids)
    params = [status, now_str(), exam_id] + result_ids
    conn = get_db()
    exam = conn.execute("SELECT id FROM exams WHERE id=?", (exam_id,)).fetchone()
    if not exam:
        conn.close()
        abort(404)
    conn.execute(
        f"UPDATE results SET status=?, reviewed_at=? WHERE exam_id=? AND id IN ({placeholders})",
        params,
    )
    changed = conn.total_changes
    conn.commit()
    conn.close()
    flash(f"{changed} نتیجه به‌روزرسانی شد.", "success")
    return redirect(url_for("exam_results", exam_id=exam_id))


@app.errorhandler(404)
def not_found(e):
    return render_template("errors/404.html"), 404

@app.errorhandler(400)
def bad_request(e):
    return "درخواست نامعتبر است.", 400

@app.errorhandler(403)
def forbidden(e):
    return "دسترسی به این آزمون در حال حاضر مجاز نیست.", 403

@app.errorhandler(500)
def server_error(e):
    logger.exception("Unhandled 500")
    return render_template("errors/500.html"), 500

@app.errorhandler(413)
def too_large(e):
    flash("حجم فایل بیش از ۵ مگابایت است.", "danger")
    return redirect(url_for("admin_dashboard"))


if __name__ == "__main__":
    port = int(os.environ.get("PORT", "8000"))
    app.run(host="0.0.0.0", port=port, debug=False)