import sqlite3
import os
import hashlib

DB_PATH = os.environ.get("DB_PATH")
if not DB_PATH:
    DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "_Data", "science_grades.db")
    DB_PATH = os.path.normpath(DB_PATH)


def get_db():
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = get_db()
    c = conn.cursor()

    c.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        role TEXT NOT NULL CHECK(role IN ('admin','parent','kid')),
        password_hash TEXT NOT NULL,
        display_name TEXT NOT NULL,
        parent_id INTEGER,
        max_kids INTEGER DEFAULT 3,
        active INTEGER DEFAULT 1,
        avatar TEXT DEFAULT '',
        unlock_all INTEGER DEFAULT 0,
        course_level INTEGER DEFAULT 2,
        created_at TEXT DEFAULT (datetime('now','localtime')),
        last_login TEXT
    );

    CREATE TABLE IF NOT EXISTS units (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT NOT NULL,
        name TEXT NOT NULL,
        color TEXT NOT NULL,
        icon TEXT NOT NULL,
        sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS chapters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        unit_id INTEGER NOT NULL REFERENCES units(id),
        code TEXT NOT NULL,
        name TEXT NOT NULL,
        sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS sections (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        chapter_id INTEGER NOT NULL REFERENCES chapters(id),
        code TEXT NOT NULL,
        name TEXT NOT NULL,
        sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS activities (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        section_id INTEGER NOT NULL REFERENCES sections(id),
        activity_type TEXT NOT NULL,
        title TEXT NOT NULL,
        data_json TEXT NOT NULL,
        points INTEGER DEFAULT 10,
        sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS experiments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        section_id INTEGER NOT NULL REFERENCES sections(id),
        title TEXT NOT NULL,
        data_json TEXT NOT NULL,
        points INTEGER DEFAULT 20,
        sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS activity_attempts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL REFERENCES users(id),
        activity_id INTEGER NOT NULL REFERENCES activities(id),
        score REAL NOT NULL,
        points_earned INTEGER NOT NULL,
        answers_json TEXT,
        time_spent INTEGER DEFAULT 0,
        attempted_at TEXT DEFAULT (datetime('now','localtime'))
    );

    CREATE TABLE IF NOT EXISTS experiment_completions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL REFERENCES users(id),
        experiment_id INTEGER NOT NULL REFERENCES experiments(id),
        completed INTEGER DEFAULT 0,
        notes TEXT DEFAULT '',
        completed_at TEXT DEFAULT (datetime('now','localtime'))
    );

    CREATE TABLE IF NOT EXISTS badges (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        icon TEXT NOT NULL,
        color TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS user_badges (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL REFERENCES users(id),
        badge_id INTEGER NOT NULL REFERENCES badges(id),
        awarded_at TEXT DEFAULT (datetime('now','localtime')),
        UNIQUE(user_id, badge_id)
    );

    CREATE TABLE IF NOT EXISTS section_progress (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL REFERENCES users(id),
        section_id INTEGER NOT NULL REFERENCES sections(id),
        status TEXT DEFAULT 'not_started',
        best_score REAL DEFAULT 0,
        total_points INTEGER DEFAULT 0,
        activities_completed INTEGER DEFAULT 0,
        last_accessed TEXT,
        UNIQUE(user_id, section_id)
    );

    CREATE TABLE IF NOT EXISTS user_stats (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL REFERENCES users(id),
        total_points INTEGER DEFAULT 0,
        total_stars INTEGER DEFAULT 0,
        streak_days INTEGER DEFAULT 0,
        last_active_date TEXT,
        UNIQUE(user_id)
    );
    """)

    # accounts are created by accounts.bootstrap() only when missing (never overwritten here)
    if "level" not in [r["name"] for r in c.execute("PRAGMA table_info(units)").fetchall()]:
        c.execute("ALTER TABLE units ADD COLUMN level INTEGER DEFAULT 2")
    if "partial" not in [r["name"] for r in c.execute("PRAGMA table_info(activity_attempts)").fetchall()]:
        c.execute("ALTER TABLE activity_attempts ADD COLUMN partial INTEGER DEFAULT 0")   # a game left before its end

    badges = [
        ("first_steps", "First Steps", "Complete your first activity", "star", "#FFD700"),
        ("plant_expert", "Plant Expert", "Complete all Plant chapter activities", "leaf", "#4CAF50"),
        ("body_master", "Body Master", "Complete all People chapter activities", "heart", "#E91E63"),
        ("matter_genius", "Matter Genius", "Complete all States of Matter activities", "atom", "#2196F3"),
        ("force_hero", "Force Hero", "Complete all Motion & Forces activities", "bolt", "#FF9800"),
        ("earth_guardian", "Earth Guardian", "Complete all Earth Science activities", "globe", "#8BC34A"),
        ("sky_explorer", "Sky Explorer", "Complete all The Sky activities", "moon", "#9C27B0"),
        ("perfect_score", "Perfect Score", "Get 100% on any activity", "trophy", "#FFD700"),
        ("scientist", "Young Scientist", "Complete 5 experiments", "flask", "#00BCD4"),
        ("streak_7", "Week Warrior", "Learn 7 days in a row", "fire", "#FF5722"),
        ("all_complete", "Science Champion", "Complete the entire workbook", "crown", "#FFD700"),
        ("quiz_master", "Quiz Master", "Complete 20 activities", "brain", "#7C4DFF"),
    ]
    for code, name, desc, icon, color in badges:
        c.execute("INSERT OR IGNORE INTO badges (code,name,description,icon,color) VALUES (?,?,?,?,?)",
                  (code, name, desc, icon, color))

    conn.commit()
    conn.close()
    print(f"Database initialized at {DB_PATH}")


if __name__ == "__main__":
    init_db()