app/database.py

"""
database.py — SQLite schema + helpers for Teacher AI progress tracking.
Tables: students, sessions, attempts, topic_mastery, error_log
"""

import sqlite3
import os
from contextlib import contextmanager
from pathlib import Path

# Store DB inside the app folder — persists between restarts
# Path: /code/app/data/teacher_ai.db
BASE_DIR = Path(__file__).parent
DATA_DIR = BASE_DIR / "data"
DATA_DIR.mkdir(exist_ok=True)   # create /code/app/data/ if it doesn't exist

DB_PATH = str(DATA_DIR / "teacher_ai.db")


def get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


@contextmanager
def db():
    conn = get_conn()
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def init_db():
    with db() as conn:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS students (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            name        TEXT    NOT NULL,
            role        TEXT    NOT NULL DEFAULT 'student',
            level       TEXT    NOT NULL DEFAULT 'high_school',
            streak      INTEGER NOT NULL DEFAULT 0,
            last_active TEXT,
            created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS attempts (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id    INTEGER NOT NULL REFERENCES students(id),
            question      TEXT    NOT NULL,
            topic         TEXT    NOT NULL,
            error_type    TEXT,
            response      TEXT,
            difficulty    INTEGER NOT NULL DEFAULT 5,
            mode          TEXT    NOT NULL DEFAULT 'text',
            explain_level TEXT    NOT NULL DEFAULT 'normal',
            created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS topic_mastery (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id    INTEGER NOT NULL REFERENCES students(id),
            topic         TEXT    NOT NULL,
            attempts      INTEGER NOT NULL DEFAULT 0,
            correct       INTEGER NOT NULL DEFAULT 0,
            mastery_score REAL    NOT NULL DEFAULT 0.0,
            last_updated  TEXT    NOT NULL DEFAULT (datetime('now')),
            UNIQUE(student_id, topic)
        );

        CREATE TABLE IF NOT EXISTS error_log (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id  INTEGER NOT NULL REFERENCES students(id),
            topic       TEXT    NOT NULL,
            error_type  TEXT    NOT NULL,
            count       INTEGER NOT NULL DEFAULT 1,
            last_seen   TEXT    NOT NULL DEFAULT (datetime('now')),
            UNIQUE(student_id, topic, error_type)
        );

        CREATE INDEX IF NOT EXISTS idx_attempts_student ON attempts(student_id);
        CREATE INDEX IF NOT EXISTS idx_attempts_topic   ON attempts(topic);
        CREATE INDEX IF NOT EXISTS idx_mastery_student  ON topic_mastery(student_id);
        """)

        # Always ensure the default Guest student (id=1) exists.
        # All unauthenticated requests use student_id=1.
        conn.execute("""
            INSERT OR IGNORE INTO students (id, name, role, level)
            VALUES (1, 'Guest', 'student', 'high_school')
        """)

    print(f"[DB] Initialised at {DB_PATH}")