# -*- coding: utf-8 -*-
"""SQLite storage: users, per-activity progress, attempt history, badges, settings."""
import os
import sqlite3
from datetime import datetime, date

DB_PATH = os.environ.get("DB_PATH") or os.path.normpath(os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "_Data", "french_grades.db"))


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




def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    c = get_db()
    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 ('kid','parent')),
        password_hash TEXT NOT NULL,
        display_name TEXT NOT NULL,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );
    CREATE TABLE IF NOT EXISTS progress (
        user_id INTEGER NOT NULL,
        activity_id TEXT NOT NULL,
        lesson_id TEXT NOT NULL,
        best_score INTEGER DEFAULT 0,
        best_total INTEGER DEFAULT 0,
        best_pct INTEGER DEFAULT 0,
        stars INTEGER DEFAULT 0,
        points INTEGER DEFAULT 0,
        attempts INTEGER DEFAULT 0,
        completed INTEGER DEFAULT 0,
        first_done TEXT,
        last_done TEXT,
        PRIMARY KEY (user_id, activity_id)
    );
    CREATE TABLE IF NOT EXISTS attempts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        activity_id TEXT NOT NULL,
        lesson_id TEXT NOT NULL,
        score INTEGER, total INTEGER, pct INTEGER, stars INTEGER,
        points_earned INTEGER DEFAULT 0,
        seconds INTEGER DEFAULT 0,
        created_at TEXT DEFAULT (datetime('now','localtime'))
    );
    CREATE INDEX IF NOT EXISTS idx_attempts_user ON attempts(user_id, created_at);
    CREATE TABLE IF NOT EXISTS badges (
        user_id INTEGER NOT NULL,
        badge_id TEXT NOT NULL,
        earned_at TEXT DEFAULT (datetime('now','localtime')),
        PRIMARY KEY (user_id, badge_id)
    );
    CREATE TABLE IF NOT EXISTS days (
        user_id INTEGER NOT NULL,
        day TEXT NOT NULL,
        points INTEGER DEFAULT 0,
        activities INTEGER DEFAULT 0,
        PRIMARY KEY (user_id, day)
    );
    CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT
    );
    """)
    if "partial" not in [r["name"] for r in c.execute("PRAGMA table_info(attempts)").fetchall()]:
        c.execute("ALTER TABLE attempts ADD COLUMN partial INTEGER DEFAULT 0")   # a game the kid left before its end
    c.commit()
    c.close()








def get_setting(key, default=None):
    c = get_db()
    row = c.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
    c.close()
    return row["value"] if row else default


def set_setting(key, value):
    c = get_db()
    c.execute("INSERT INTO settings (key, value) VALUES (?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value", (key, str(value)))
    c.commit()
    c.close()


# ---------------------------------------------------------------- progress
def stars_for(pct):
    return 3 if pct >= 90 else 2 if pct >= 60 else 1 if pct >= 30 else 0


def record_attempt(user_id, lesson_id, activity_id, score, total, points_max, seconds=0, partial=False):
    """Store one attempt; returns dict(points_earned, stars, pct, best, new_best)."""
    total = max(int(total or 0), 0)
    score = max(min(int(score or 0), total), 0) if total else 0
    pct = 100 if total == 0 else round(score * 100 / total)
    stars = stars_for(pct)
    points_now = round(points_max * pct / 100) + (5 if pct == 100 else 0)
    c = get_db()
    prev = c.execute("SELECT * FROM progress WHERE user_id=? AND activity_id=?", (user_id, activity_id)).fetchone()
    prev_points = prev["points"] if prev else 0
    new_best = points_now > prev_points
    earned = points_now - prev_points if new_best else (2 if pct >= 60 else 0)
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    completed = 1 if (pct >= 50 and not partial) or (prev and prev["completed"]) else 0   # an unfinished game never completes
    if prev:
        c.execute("""UPDATE progress SET best_score=?, best_total=?, best_pct=?, stars=?, points=?, attempts=attempts+1,
                     completed=?, last_done=? WHERE user_id=? AND activity_id=?""",
                  (max(score, prev["best_score"]) if not new_best else score, total if new_best else prev["best_total"],
                   max(pct, prev["best_pct"]), max(stars, prev["stars"]), max(points_now, prev_points), completed, now, user_id, activity_id))
    else:
        c.execute("""INSERT INTO progress (user_id, activity_id, lesson_id, best_score, best_total, best_pct, stars, points, attempts,
                     completed, first_done, last_done) VALUES (?,?,?,?,?,?,?,?,1,?,?,?)""",
                  (user_id, activity_id, lesson_id, score, total, pct, stars, points_now, completed, now, now))
    c.execute("INSERT INTO attempts (user_id, activity_id, lesson_id, score, total, pct, stars, points_earned, seconds, partial) VALUES (?,?,?,?,?,?,?,?,?,?)",
              (user_id, activity_id, lesson_id, score, total, pct, stars, earned, min(int(seconds or 0), 1800), 1 if partial else 0))
    today = date.today().isoformat()
    c.execute("""INSERT INTO days (user_id, day, points, activities) VALUES (?,?,?,1)
                 ON CONFLICT(user_id, day) DO UPDATE SET points=points+excluded.points, activities=activities+1""", (user_id, today, earned))
    c.commit()
    c.close()
    return dict(points_earned=earned, stars=stars, pct=pct, new_best=new_best, completed=bool(completed))


def progress_map(user_id):
    """Best result per activity, plus the fastest perfect run (personal best for timed challenges)."""
    c = get_db()
    rows = c.execute("SELECT * FROM progress WHERE user_id=?", (user_id,)).fetchall()
    best = dict(c.execute("""SELECT activity_id, MIN(seconds) FROM attempts WHERE user_id=? AND pct=100 AND seconds>0
                             GROUP BY activity_id""", (user_id,)).fetchall())
    c.close()
    out = {}
    for r in rows:
        d = dict(r)
        d["best_secs"] = best.get(r["activity_id"])
        out[r["activity_id"]] = d
    return out


def stats(user_id):
    c = get_db()
    xp = c.execute("SELECT COALESCE(SUM(points_earned),0) AS xp FROM attempts WHERE user_id=?", (user_id,)).fetchone()["xp"]
    row = c.execute("""SELECT COUNT(*) AS done, COALESCE(SUM(stars),0) AS stars, COALESCE(SUM(points),0) AS points,
                       SUM(CASE WHEN best_pct=100 THEN 1 ELSE 0 END) AS perfect, COALESCE(SUM(attempts),0) AS attempts
                       FROM progress WHERE user_id=? AND completed=1""", (user_id,)).fetchone()
    days = [r["day"] for r in c.execute("SELECT day FROM days WHERE user_id=? AND activities>0 ORDER BY day DESC", (user_id,)).fetchall()]
    badges = [r["badge_id"] for r in c.execute("SELECT badge_id FROM badges WHERE user_id=? ORDER BY earned_at", (user_id,)).fetchall()]
    secs = c.execute("SELECT COALESCE(SUM(seconds),0) AS s FROM attempts WHERE user_id=?", (user_id,)).fetchone()["s"]
    c.close()
    streak = 0
    d = date.today()
    dayset = set(days)
    if d.isoformat() not in dayset:
        d = d.fromordinal(d.toordinal() - 1)
    while d.isoformat() in dayset:
        streak += 1
        d = d.fromordinal(d.toordinal() - 1)
    return dict(xp=int(xp), done=row["done"] or 0, stars=row["stars"] or 0, perfect=row["perfect"] or 0, attempts=row["attempts"] or 0,
                days=len(days), streak=streak, badges=badges, seconds=int(secs), today=date.today().isoformat() in dayset)


def award_badge(user_id, badge_id):
    c = get_db()
    cur = c.execute("INSERT OR IGNORE INTO badges (user_id, badge_id) VALUES (?,?)", (user_id, badge_id))
    c.commit()
    new = cur.rowcount > 0
    c.close()
    return new


def history(user_id, limit=60):
    c = get_db()
    rows = c.execute("SELECT * FROM attempts WHERE user_id=? ORDER BY id DESC LIMIT ?", (user_id, limit)).fetchall()
    c.close()
    return [dict(r) for r in rows]


def day_series(user_id, n=14):
    c = get_db()
    rows = c.execute("SELECT day, points, activities FROM days WHERE user_id=? ORDER BY day DESC LIMIT ?", (user_id, n)).fetchall()
    c.close()
    return [dict(r) for r in reversed(rows)]


def reset(user_id, lesson_id=None, activity_id=None):
    c = get_db()
    if activity_id:
        c.execute("DELETE FROM progress WHERE user_id=? AND activity_id=?", (user_id, activity_id))
        c.execute("DELETE FROM attempts WHERE user_id=? AND activity_id=?", (user_id, activity_id))
    elif lesson_id:
        c.execute("DELETE FROM progress WHERE user_id=? AND lesson_id=?", (user_id, lesson_id))
        c.execute("DELETE FROM attempts WHERE user_id=? AND lesson_id=?", (user_id, lesson_id))
    else:
        for t in ("progress", "attempts", "badges", "days"):
            c.execute(f"DELETE FROM {t} WHERE user_id=?", (user_id,))
    c.commit()
    c.close()


def migrate_timezone():
    """Rows written before TZ was set are in UTC: shift them once to local time."""
    import time
    off = -time.timezone // 3600 if not time.daylight else -time.altzone // 3600
    if not off or get_setting("tz_shifted"):
        set_setting("tz_shifted", str(off))
        return
    c = get_db()
    mod = f"{off:+d} hours"
    for table, cols in (("attempts", ["created_at"]), ("badges", ["earned_at"]), ("progress", ["first_done", "last_done"]), ("users", ["created_at"])):
        for col in cols:
            c.execute(f"UPDATE {table} SET {col}=datetime({col}, ?) WHERE {col} IS NOT NULL", (mod,))
    c.commit()
    c.close()
    set_setting("tz_shifted", str(off))