"""Tests for the SQLAlchemy data-access layer (run against a temp SQLite file)."""

from __future__ import annotations

import pytest

from app import config as config_module
from app import database
from app import db as db_module
from app.answer_grid import from_dict as grid_from_dict, score_student


@pytest.fixture
async def fresh_db(tmp_path, monkeypatch):
    db_file = tmp_path / "t.db"
    cached = config_module.get_settings()
    monkeypatch.setattr(cached, "database_provider", "sqlite", raising=False)
    monkeypatch.setattr(cached, "supabase_db_url", "", raising=False)
    monkeypatch.setattr(cached, "database_path", str(db_file), raising=False)
    monkeypatch.setattr(cached, "admin_emails", "", raising=False)
    db_module.get_engine.cache_clear()
    db_module.get_sessionmaker.cache_clear()
    await database.init_database()
    yield
    db_module.get_engine.cache_clear()
    db_module.get_sessionmaker.cache_clear()


@pytest.mark.unit
async def test_teacher_profile_roundtrip(fresh_db):
    uid = await database.create_user(
        "t@example.com", "hash", full_name="Wang", school="First High"
    )
    user = await database.get_user_by_email("t@example.com")
    assert user["id"] == uid
    assert user["full_name"] == "Wang"
    assert user["school"] == "First High"
    assert user["is_admin"] is False


@pytest.mark.unit
async def test_student_matched_by_normalized_name(fresh_db):
    uid = await database.create_user("t@example.com", "h")
    a = await database.match_or_create_student(uid, "Alice Chen")
    b = await database.match_or_create_student(uid, "  alice   chen ")
    c = await database.match_or_create_student(uid, "Bob")
    assert a == b  # whitespace/case-insensitive match
    assert a != c
    students = await database.get_students(uid)
    assert len(students) == 2


@pytest.mark.unit
async def test_student_timeline_across_quizzes(fresh_db):
    uid = await database.create_user("t@example.com", "h")
    sid = await database.match_or_create_student(uid, "Alice")

    q1 = await database.create_session(uid, "Quiz 1")
    await database.save_student_result(q1, sid, {"answers": ["A"]}, 5, 3, 60.0)
    q2 = await database.create_session(uid, "Quiz 2")
    await database.save_student_result(q2, sid, {"answers": ["B"]}, 5, 5, 100.0)

    timeline = await database.get_student_timeline(sid)
    assert [t["score"] for t in timeline] == [60.0, 100.0]
    assert timeline[0]["quiz_title"] == "Quiz 1"


@pytest.mark.unit
async def test_save_student_result_is_upsert(fresh_db):
    uid = await database.create_user("t@example.com", "h")
    sid = await database.match_or_create_student(uid, "Alice")
    q = await database.create_session(uid, "Quiz")
    await database.save_student_result(q, sid, {"answers": []}, 5, 2, 40.0)
    await database.save_student_result(q, sid, {"answers": []}, 5, 4, 80.0)
    timeline = await database.get_student_timeline(sid)
    assert len(timeline) == 1  # same quiz+student -> updated, not duplicated
    assert timeline[0]["score"] == 80.0


@pytest.mark.unit
async def test_stats_counts(fresh_db):
    uid = await database.create_user("t@example.com", "h")
    sid = await database.match_or_create_student(uid, "Alice")
    q = await database.create_session(uid, "Quiz")
    await database.save_student_result(q, sid, {"answers": []}, 3, 3, 100.0)
    stats = await database.get_stats()
    assert stats["teachers"] == 1
    assert stats["students"] == 1
    assert stats["quizzes"] == 1
    assert stats["student_results"] == 1


@pytest.mark.unit
def test_score_student_grades_only_concrete_keys():
    grid = grid_from_dict(
        {
            "total_questions": 4,
            # Q4 official is blank -> not graded
            "official_answers": ["A", "B", "C", ""],
            "students": [{"name": "Alice", "answers": ["A", "X", "=", ""]}],
            "questions": [],
        }
    )
    graded, correct, score = score_student(grid, 0)
    # Graded = Q1..Q3 (3). Correct: Q1 (A), Q3 ("=") -> 2 correct, Q2 wrong.
    assert graded == 3
    assert correct == 2
    assert score == pytest.approx(66.7, abs=0.1)