"""
database.py — Multi-tenant schema for TimetableGen SaaS.

Tenancy model: shared database, school_id on every table.
Each school has one or more users (headmaster, timetable in-charge, viewer).
Sessions stored server-side in the `session` table.
"""
import sqlite3, os, secrets, hashlib

DB_PATH = os.environ.get("DB_PATH", "timetable.db")

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn

def init_db():
    conn = get_db()
    conn.executescript("""
    -- ── Users & Auth ─────────────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS school (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        name          TEXT NOT NULL,
        address       TEXT,
        board         TEXT DEFAULT 'CBSE',
        city          TEXT,
        state         TEXT,
        pin           TEXT,
        phone         TEXT,
        email         TEXT,
        logo_path     TEXT,
        is_active     INTEGER DEFAULT 1,
        plan          TEXT DEFAULT 'free',   -- free | pro
        created_at    TEXT DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS user (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        school_id     INTEGER NOT NULL,
        name          TEXT NOT NULL,
        email         TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        role          TEXT DEFAULT 'staff',  -- owner | admin | staff
        is_active     INTEGER DEFAULT 1,
        created_at    TEXT DEFAULT (datetime('now')),
        last_login    TEXT,
        FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS session (
        token         TEXT PRIMARY KEY,
        user_id       INTEGER NOT NULL,
        school_id     INTEGER NOT NULL,
        created_at    TEXT DEFAULT (datetime('now')),
        expires_at    TEXT NOT NULL,
        FOREIGN KEY(user_id)   REFERENCES user(id)   ON DELETE CASCADE,
        FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS password_reset (
        token         TEXT PRIMARY KEY,
        user_id       INTEGER NOT NULL,
        created_at    TEXT DEFAULT (datetime('now')),
        expires_at    TEXT NOT NULL,
        used          INTEGER DEFAULT 0,
        FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE CASCADE
    );

    -- ── School Data (all scoped by school_id) ────────────────────────────────
    CREATE TABLE IF NOT EXISTS class_section (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        school_id   INTEGER NOT NULL,
        grade       TEXT NOT NULL,
        section     TEXT NOT NULL DEFAULT 'A',
        room_no     TEXT,
        UNIQUE(school_id, grade, section),
        FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS subject (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        school_id     INTEGER NOT NULL,
        name          TEXT NOT NULL,
        code          TEXT NOT NULL,
        subject_type  TEXT DEFAULT 'theory',
        color         TEXT DEFAULT '#4F8EF7',
        UNIQUE(school_id, code),
        FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS teacher (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        school_id   INTEGER NOT NULL,
        name        TEXT NOT NULL,
        emp_code    TEXT,
        phone       TEXT,
        email       TEXT,
        is_active   INTEGER DEFAULT 1,
        UNIQUE(school_id, emp_code),
        FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS teacher_subject (
        teacher_id  INTEGER NOT NULL,
        subject_id  INTEGER NOT NULL,
        PRIMARY KEY(teacher_id, subject_id),
        FOREIGN KEY(teacher_id) REFERENCES teacher(id) ON DELETE CASCADE,
        FOREIGN KEY(subject_id) REFERENCES subject(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS timetable_config (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        school_id       INTEGER NOT NULL,
        academic_year   TEXT NOT NULL DEFAULT '2025-26',
        working_days    TEXT NOT NULL DEFAULT 'Mon,Tue,Wed,Thu,Fri,Sat',
        periods_per_day INTEGER NOT NULL DEFAULT 8,
        period_duration INTEGER NOT NULL DEFAULT 45,
        break_after     TEXT DEFAULT '4',
        break_duration  INTEGER DEFAULT 30,
        lab_days        TEXT DEFAULT 'Wed,Thu',
        pt_days         TEXT DEFAULT 'Sat',
        is_active       INTEGER DEFAULT 1,
        FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS class_subject_requirement (
        id               INTEGER PRIMARY KEY AUTOINCREMENT,
        class_id         INTEGER NOT NULL,
        subject_id       INTEGER NOT NULL,
        teacher_id       INTEGER NOT NULL,
        periods_per_week INTEGER NOT NULL DEFAULT 5,
        UNIQUE(class_id, subject_id),
        FOREIGN KEY(class_id)   REFERENCES class_section(id) ON DELETE CASCADE,
        FOREIGN KEY(subject_id) REFERENCES subject(id)       ON DELETE CASCADE,
        FOREIGN KEY(teacher_id) REFERENCES teacher(id)       ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS timetable (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        config_id   INTEGER NOT NULL,
        class_id    INTEGER NOT NULL,
        day         TEXT NOT NULL,
        period_no   INTEGER NOT NULL,
        subject_id  INTEGER,
        teacher_id  INTEGER,
        is_free     INTEGER DEFAULT 0,
        is_break    INTEGER DEFAULT 0,
        note        TEXT,
        UNIQUE(config_id, class_id, day, period_no),
        FOREIGN KEY(config_id)  REFERENCES timetable_config(id) ON DELETE CASCADE,
        FOREIGN KEY(class_id)   REFERENCES class_section(id)    ON DELETE CASCADE,
        FOREIGN KEY(subject_id) REFERENCES subject(id),
        FOREIGN KEY(teacher_id) REFERENCES teacher(id)
    );

    CREATE TABLE IF NOT EXISTS substitution (
        id                      INTEGER PRIMARY KEY AUTOINCREMENT,
        config_id               INTEGER NOT NULL,
        absent_teacher_id       INTEGER NOT NULL,
        substitute_teacher_id   INTEGER NOT NULL,
        class_id                INTEGER NOT NULL,
        day                     TEXT NOT NULL,
        period_no               INTEGER NOT NULL,
        date                    TEXT DEFAULT (date('now')),
        created_at              TEXT DEFAULT (datetime('now')),
        FOREIGN KEY(config_id)                  REFERENCES timetable_config(id),
        FOREIGN KEY(absent_teacher_id)          REFERENCES teacher(id),
        FOREIGN KEY(substitute_teacher_id)      REFERENCES teacher(id),
        FOREIGN KEY(class_id)                   REFERENCES class_section(id)
    );

    -- ── Platform Admin ────────────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS platform_admin (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        email         TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        created_at    TEXT DEFAULT (datetime('now'))
    );
    """)
    conn.commit()
    conn.close()
    print(f"[DB] Initialized at {DB_PATH}")