CREATE TABLE users (
    id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE COLLATE NOCASE,
    display_name TEXT NOT NULL, password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('super_admin','admin','staff','student')),
    band TEXT CHECK(band IN ('5–7','8–11','12–14','15–18')),
    active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
    must_change INTEGER NOT NULL DEFAULT 1 CHECK(must_change IN (0,1)),
    auth_version INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL
);
CREATE TABLE lessons (
    id INTEGER PRIMARY KEY, owner_id INTEGER NOT NULL REFERENCES users(id),
    title TEXT NOT NULL, strand TEXT NOT NULL,
    band TEXT NOT NULL CHECK(band IN ('5–7','8–11','12–14','15–18')),
    summary TEXT NOT NULL, objective TEXT NOT NULL,
    body TEXT NOT NULL, practical TEXT NOT NULL, quiz_json TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','review','published')),
    revision INTEGER NOT NULL DEFAULT 1,
    updated_at TEXT NOT NULL, published_at TEXT
);
CREATE INDEX lesson_status_band ON lessons(status, band);
CREATE TABLE attempts (
    id INTEGER PRIMARY KEY, student_id INTEGER NOT NULL REFERENCES users(id),
    lesson_id INTEGER NOT NULL REFERENCES lessons(id), lesson_revision INTEGER NOT NULL,
    title TEXT NOT NULL, score INTEGER NOT NULL, total INTEGER NOT NULL,
    reflection TEXT NOT NULL, feedback_json TEXT NOT NULL, created_at TEXT NOT NULL
);
CREATE INDEX attempts_student_lesson ON attempts(student_id, lesson_id, id);
CREATE TABLE login_limits (
    bucket TEXT PRIMARY KEY, hits INTEGER NOT NULL, expires_at INTEGER NOT NULL
);