import sqlite3
import os
from flask import g


def get_db():
    if 'db' not in g:
        db_path = os.environ.get('DATABASE', 'scores.db')
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        g.db = sqlite3.connect(db_path)
        g.db.row_factory = sqlite3.Row
    return g.db


def close_db(e=None):
    db = g.pop('db', None)
    if db is not None:
        db.close()


def init_db():
    db = get_db()
    db.executescript('''
        CREATE TABLE IF NOT EXISTS quiz_results (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            user_sub     TEXT    NOT NULL,
            user_name    TEXT    NOT NULL DEFAULT '',
            quiz_id      TEXT    NOT NULL,
            score        INTEGER NOT NULL,
            total        INTEGER NOT NULL,
            completed_at TEXT    NOT NULL DEFAULT (datetime('now'))
        );
        CREATE INDEX IF NOT EXISTS idx_results_user ON quiz_results(user_sub);
    ''')
    db.commit()


def save_result(user_sub, user_name, quiz_id, score, total):
    db = get_db()
    db.execute(
        'INSERT INTO quiz_results (user_sub, user_name, quiz_id, score, total) VALUES (?,?,?,?,?)',
        (user_sub, user_name, quiz_id, score, total),
    )
    db.commit()


def get_user_results(user_sub):
    db = get_db()
    return db.execute(
        'SELECT * FROM quiz_results WHERE user_sub=? ORDER BY completed_at DESC',
        (user_sub,),
    ).fetchall()


def get_best_score(user_sub, quiz_id):
    db = get_db()
    row = db.execute(
        'SELECT MAX(score) as best, total FROM quiz_results WHERE user_sub=? AND quiz_id=?',
        (user_sub, quiz_id),
    ).fetchone()
    return row if row and row['best'] is not None else None