import os
import json
import hashlib
import sqlite3
from functools import wraps
from dotenv import load_dotenv
from flask import Flask, request, jsonify, session, render_template, redirect, url_for, send_from_directory, g

load_dotenv()

from db_init import DB_PATH, get_db
import curriculum
import games_data
import accounts as ACC
import levels as LV
from datetime import timedelta

app = Flask(__name__)
app.secret_key = os.environ.get("SECRET_KEY") or "science_gr2_secret_key_2026"
app.config.update(SESSION_COOKIE_HTTPONLY=True, SESSION_COOKIE_SAMESITE="Lax", PERMANENT_SESSION_LIFETIME=timedelta(days=30), JSON_AS_ASCII=False)
HOME = {"kid": "/", "parent": "/", "admin": "/admin"}
ASSETS_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "assets")

BASE_PATH = os.environ.get("BASE_PATH", "").rstrip("/")
if BASE_PATH and not BASE_PATH.startswith("/"):
    BASE_PATH = "/" + BASE_PATH


def _asset_version():
    """Changes whenever a script or stylesheet changes, so phones never run an old cached copy after a deploy."""
    import hashlib
    h = hashlib.md5()
    for root, _, files in os.walk(os.path.join(app.root_path, "static")):
        for f in sorted(files):
            if f.endswith((".js", ".css")):
                h.update(f.encode()); h.update(str(os.path.getmtime(os.path.join(root, f))).encode())
    return h.hexdigest()[:8]


ASSET_V = _asset_version()


@app.context_processor
def inject_base_path():
    return {"base_path": BASE_PATH, "v": ASSET_V}


def bp_redirect(endpoint):
    return redirect(url_for(endpoint))          # url_for already carries the /science prefix (SCRIPT_NAME)


def go(path):
    """Redirect to an app path under the prefix the request came through (/science behind the proxy, none locally)."""
    return redirect(request.script_root + path)


def current_user():
    if "cu" not in g:
        u = ACC.get(session.get("user_id")) if session.get("user_id") else None
        g.cu = u if u and ACC.can_login(u) else None
    return g.cu


def login_required(*roles):
    """login_required() any account; login_required("kid") / ("parent", "admin") restrict the roles."""
    roles = tuple(r for r in roles if r)
    def decorator(f):
        @wraps(f)
        def wrapper(*args, **kwargs):
            u = current_user()
            api = request.is_json or request.path.startswith("/api/")
            if not u:
                session.clear()
                return (jsonify({"error": "not_logged_in"}), 401) if api else bp_redirect("login_page")
            if roles and u["role"] not in roles:
                return (jsonify({"error": "wrong_role"}), 403) if api else go(HOME[u["role"]])
            session["role"], session["display_name"] = u["role"], u["display_name"]
            return f(*args, **kwargs)
        return wrapper
    return decorator


ACT_TYPES = [("flashcards", "Flashcards", 10), ("quiz", "Quiz Time!", 15), ("truefalse", "True or False", 10), ("matching", "Match It!", 15),
             ("sorting", "Sort & Classify", 15), ("memory", "Memory Game", 15), ("labeling", "Label the Diagram", 15)]


def _sync_rows(c, table, section_id, rows):
    """Write a section's activities / experiments in order, UPDATING the rows that exist so their ids — and the kids'
    attempts that point to them — survive a restart. Only rows beyond the new count are deleted (with their attempts)."""
    cols = "activity_type,title,data_json,points" if table == "activities" else "title,data_json,points"
    done_table, done_col = ("activity_attempts", "activity_id") if table == "activities" else ("experiment_completions", "experiment_id")
    old = c.execute(f"SELECT id, sort_order FROM {table} WHERE section_id=? ORDER BY sort_order, id", (section_id,)).fetchall()
    by_order = {}
    for r in old:
        if r["sort_order"] in by_order:                     # duplicates left by the old rebuild: drop the extra copy
            c.execute(f"DELETE FROM {done_table} WHERE {done_col}=?", (r["id"],)); c.execute(f"DELETE FROM {table} WHERE id=?", (r["id"],))
        else:
            by_order[r["sort_order"]] = r["id"]
    for k, vals in enumerate(rows):
        if k in by_order:
            c.execute(f"UPDATE {table} SET {', '.join(x + '=?' for x in cols.split(','))} WHERE id=?", (*vals, by_order[k]))
        else:
            c.execute(f"INSERT INTO {table} (section_id,{cols},sort_order) VALUES (?,{','.join('?' * len(vals))},?)", (section_id, *vals, k))
    for order, rid in by_order.items():
        if order >= len(rows):
            c.execute(f"DELETE FROM {done_table} WHERE {done_col}=?", (rid,)); c.execute(f"DELETE FROM {table} WHERE id=?", (rid,))


def build_curriculum_in_db():
    conn = get_db()
    c = conn.cursor()
    for ui, unit in enumerate(curriculum.UNITS):
        lvl = unit.get("level", LV.DEFAULT)
        c.execute("SELECT id FROM units WHERE code=?", (unit["code"],))
        row = c.fetchone()
        if row:
            unit_id = row["id"]
            c.execute("UPDATE units SET name=?, color=?, icon=?, sort_order=?, level=? WHERE id=?", (unit["name"], unit["color"], unit["icon"], ui, lvl, unit_id))
        else:
            c.execute("INSERT INTO units (code,name,color,icon,sort_order,level) VALUES (?,?,?,?,?,?)",
                      (unit["code"], unit["name"], unit["color"], unit["icon"], ui, lvl))
            unit_id = c.lastrowid

        for ci, chapter in enumerate(unit["chapters"]):
            c.execute("SELECT id FROM chapters WHERE code=? AND unit_id=?", (chapter["code"], unit_id))
            row = c.fetchone()
            if row:
                chapter_id = row["id"]
            else:
                c.execute("INSERT INTO chapters (unit_id,code,name,sort_order) VALUES (?,?,?,?)",
                          (unit_id, chapter["code"], chapter["name"], ci))
                chapter_id = c.lastrowid

            for si, section in enumerate(chapter["sections"]):
                c.execute("SELECT id FROM sections WHERE code=? AND chapter_id=?", (section["code"], chapter_id))
                row = c.fetchone()
                if row:
                    section_id = row["id"]
                else:
                    c.execute("INSERT INTO sections (chapter_id,code,name,sort_order) VALUES (?,?,?,?)",
                              (chapter_id, section["code"], section["name"], si))
                    section_id = c.lastrowid

                acts = [(t, title, json.dumps(section[t]), pts) for t, title, pts in ACT_TYPES if section.get(t)]
                for game in games_data.get_games(section["code"]):
                    gdata = {k: v for k, v in game.items() if k not in ("type", "title", "points")}
                    acts.append((game["type"], game["title"], json.dumps(gdata), game.get("points", 15)))
                _sync_rows(c, "activities", section_id, acts)
                _sync_rows(c, "experiments", section_id, [(exp["title"], json.dumps(exp), 25) for exp in section.get("experiments", [])])

    conn.commit()
    conn.close()


def unit_level_of_activity(c, activity_id, table="activities"):
    r = c.execute(f"SELECT u.level FROM {table} a JOIN sections s ON a.section_id=s.id JOIN chapters ch ON s.chapter_id=ch.id "
                  f"JOIN units u ON ch.unit_id=u.id WHERE a.id=?", (activity_id,)).fetchone()
    return r["level"] if r else None


def get_user_stats(user_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM user_stats WHERE user_id=?", (user_id,))
    row = c.fetchone()
    if not row:
        c.execute("INSERT INTO user_stats (user_id) VALUES (?)", (user_id,))
        conn.commit()
        c.execute("SELECT * FROM user_stats WHERE user_id=?", (user_id,))
        row = c.fetchone()
    conn.close()
    return dict(row)


def update_user_stats(user_id, points_add):
    conn = get_db()
    c = conn.cursor()
    import datetime
    today = datetime.date.today().isoformat()
    c.execute("SELECT * FROM user_stats WHERE user_id=?", (user_id,))
    row = c.fetchone()
    if not row:
        c.execute("INSERT INTO user_stats (user_id,total_points,last_active_date) VALUES (?,?,?)",
                  (user_id, points_add, today))
    else:
        new_total = row["total_points"] + points_add
        new_stars = new_total // 50
        last_date = row["last_active_date"]
        streak = row["streak_days"]
        if last_date != today:
            import datetime
            try:
                prev = datetime.datetime.strptime(last_date, "%Y-%m-%d").date()
                curr = datetime.datetime.strptime(today, "%Y-%m-%d").date()
                if (curr - prev).days == 1:
                    streak += 1
                else:
                    streak = 1
            except:
                streak = 1
        c.execute("UPDATE user_stats SET total_points=?, total_stars=?, streak_days=?, last_active_date=? WHERE user_id=?",
                  (new_total, new_stars, streak, today, user_id))
    conn.commit()
    conn.close()


def check_badges(user_id):
    conn = get_db()
    c = conn.cursor()
    awarded = []

    c.execute("SELECT COUNT(*) as n FROM activity_attempts WHERE user_id=? AND partial=0", (user_id,))
    if c.fetchone()["n"] > 0:
        c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code='first_steps'))", (user_id,))
        awarded.append("first_steps")

    c.execute("SELECT COUNT(DISTINCT a.id) as n FROM activity_attempts aa JOIN activities a ON aa.activity_id=a.id WHERE aa.user_id=? AND aa.score>=100 AND aa.partial=0", (user_id,))
    if c.fetchone()["n"] > 0:
        c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code='perfect_score'))", (user_id,))
        awarded.append("perfect_score")

    c.execute("SELECT COUNT(DISTINCT ec.experiment_id) as n FROM experiment_completions ec WHERE ec.user_id=? AND ec.completed=1", (user_id,))
    if c.fetchone()["n"] >= 5:
        c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code='scientist'))", (user_id,))
        awarded.append("scientist")

    c.execute("SELECT COUNT(DISTINCT activity_id) as n FROM activity_attempts WHERE user_id=? AND partial=0", (user_id,))
    if c.fetchone()["n"] >= 20:
        c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code='quiz_master'))", (user_id,))
        awarded.append("quiz_master")

    stats = get_user_stats(user_id)
    if stats["streak_days"] >= 7:
        c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code='streak_7'))", (user_id,))
        awarded.append("streak_7")

    c.execute("""
        SELECT s.id, ch.id as ch_id, ch.name FROM sections s
        JOIN chapters ch ON s.chapter_id=ch.id
    """)
    sections = c.fetchall()
    chapter_completion = {}
    for sec in sections:
        c.execute("SELECT COUNT(*) as total FROM activities WHERE section_id=?", (sec["id"],))
        total_act = c.fetchone()["total"]
        c.execute("SELECT COUNT(DISTINCT activity_id) as done FROM activity_attempts WHERE user_id=? AND partial=0 AND activity_id IN (SELECT id FROM activities WHERE section_id=?)", (user_id, sec["id"]))
        done_act = c.fetchone()["done"]
        ch_name = sec["name"]
        if ch_name not in chapter_completion:
            chapter_completion[ch_name] = {"total": 0, "done": 0}
        chapter_completion[ch_name]["total"] += total_act
        chapter_completion[ch_name]["done"] += done_act

    badge_map = {
        "Plants": "plant_expert", "People": "body_master",
        "States of Matter": "matter_genius", "Motion and Forces": "force_hero",
        "Earth Materials & Natural Resources": "earth_guardian", "The Sky": "sky_explorer",
    }
    for ch_name, badge_code in badge_map.items():
        if ch_name in chapter_completion:
            info = chapter_completion[ch_name]
            if info["total"] > 0 and info["done"] >= info["total"]:
                c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code=?))", (user_id, badge_code))
                awarded.append(badge_code)

    total_all = sum(v["total"] for v in chapter_completion.values())
    done_all = sum(v["done"] for v in chapter_completion.values())
    if total_all > 0 and done_all >= total_all:
        c.execute("INSERT OR IGNORE INTO user_badges (user_id,badge_id) VALUES (?,(SELECT id FROM badges WHERE code='all_complete'))", (user_id,))
        awarded.append("all_complete")

    conn.commit()
    conn.close()
    return awarded


def update_section_progress(user_id, section_id, score, points):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM section_progress WHERE user_id=? AND section_id=?", (user_id, section_id))
    row = c.fetchone()
    if not row:
        c.execute("INSERT INTO section_progress (user_id,section_id,status,best_score,total_points,activities_completed,last_accessed) VALUES (?,?,?,?,?,?,datetime('now','localtime'))",
                  (user_id, section_id, "in_progress", score, points, 1))
    else:
        new_best = max(row["best_score"], score)
        new_points = row["total_points"] + points
        new_done = row["activities_completed"] + 1
        c.execute("SELECT COUNT(*) as total FROM activities WHERE section_id=?", (section_id,))
        total = c.fetchone()["total"]
        status = "completed" if new_done >= total else "in_progress"
        c.execute("UPDATE section_progress SET best_score=?,total_points=?,activities_completed=?,status=?,last_accessed=datetime('now','localtime') WHERE user_id=? AND section_id=?",
                  (new_best, new_points, new_done, status, user_id, section_id))
    conn.commit()
    conn.close()


@app.route("/assets/<path:filename>")
def serve_asset(filename):
    return send_from_directory(ASSETS_DIR, filename)


@app.route("/")
def index():
    u = current_user()
    if not u:
        return bp_redirect("login_page")
    if u["role"] == "admin":
        return go("/admin")
    if u["role"] == "parent":
        return render_template("parent.html", me=ACC.public(u))
    return render_template("kid.html")


@app.route("/parent")
@login_required("parent", "admin")
def parent_page():
    return render_template("parent.html", me=ACC.public(current_user()))


@app.route("/report")
@login_required("parent", "admin")
def report_page():
    """One kid's science report (shown inside the parent dashboard)."""
    return render_template("report.html")


@app.route("/admin")
@login_required("admin")
def admin_page():
    return render_template("admin.html", me=ACC.public(current_user()))


@app.route("/login")
def login_page():
    u = current_user()
    if u:
        return go(HOME[u["role"]])
    return render_template("login.html")


@app.route("/logout")
def logout_page():
    session.clear()
    return bp_redirect("login_page")


@app.route("/api/login", methods=["POST"])
def api_login():
    data = request.get_json(silent=True) or {}
    u, err = ACC.authenticate(data.get("username", "").strip(), data.get("password", ""))
    if err:
        msg = "This account is suspended." if "موقوف" in err else "Invalid username or password"
        return jsonify({"error": msg, "error_ar": err}), 401
    session.clear()
    session.update(user_id=u["id"], username=u["username"], role=u["role"], display_name=u["display_name"])
    session.permanent = True
    return jsonify({"role": u["role"], "display_name": u["display_name"], "home": request.script_root + HOME[u["role"]]})


@app.route("/api/logout", methods=["POST"])
def api_logout():
    session.clear()
    return jsonify({"ok": True})


@app.route("/api/me")
@login_required()
def api_me():
    stats = get_user_stats(session["user_id"])
    conn = get_db()
    c = conn.cursor()
    c.execute("""SELECT b.* FROM user_badges ub JOIN badges b ON ub.badge_id=b.id WHERE ub.user_id=? ORDER BY b.id""", (session["user_id"],))
    badges = [dict(r) for r in c.fetchall()]
    conn.close()
    return jsonify({
        "user_id": session["user_id"],
        "username": session["username"],
        "role": session["role"],
        "display_name": session["display_name"],
        "course_level": current_user()["course_level"],
        "course": LV.level(current_user()["course_level"]),
        "stats": stats,
        "badges": badges,
    })


@app.route("/api/curriculum")
@login_required()
def api_curriculum():
    conn = get_db()
    c = conn.cursor()
    result = []
    u = current_user()
    if u["role"] == "kid":               # a kid sees only the units of its level
        c.execute("SELECT * FROM units WHERE level=? ORDER BY sort_order", (u["course_level"],))
    else:
        c.execute("SELECT * FROM units ORDER BY sort_order")
    units = c.fetchall()
    for u in units:
        unit = dict(u)
        c.execute("SELECT * FROM chapters WHERE unit_id=? ORDER BY sort_order", (u["id"],))
        chapters = c.fetchall()
        unit["chapters"] = []
        for ch in chapters:
            chapter = dict(ch)
            c.execute("SELECT * FROM sections WHERE chapter_id=? ORDER BY sort_order", (ch["id"],))
            sections = c.fetchall()
            chapter["sections"] = []
            for sec in sections:
                section = dict(sec)
                c.execute("SELECT id,activity_type,title,points,sort_order FROM activities WHERE section_id=? ORDER BY sort_order", (sec["id"],))
                section["activities"] = [dict(r) for r in c.fetchall()]
                c.execute("SELECT id,title,points,sort_order FROM experiments WHERE section_id=? ORDER BY sort_order", (sec["id"],))
                section["experiments"] = [dict(r) for r in c.fetchall()]
                if session["role"] == "kid":
                    c.execute("SELECT COUNT(DISTINCT activity_id) as done FROM activity_attempts WHERE user_id=? AND partial=0 AND activity_id IN (SELECT id FROM activities WHERE section_id=?)", (session["user_id"], sec["id"]))
                    section["activities_done"] = c.fetchone()["done"]
                    c.execute("SELECT COUNT(DISTINCT experiment_id) as done FROM experiment_completions WHERE user_id=? AND completed=1 AND experiment_id IN (SELECT id FROM experiments WHERE section_id=?)", (session["user_id"], sec["id"]))
                    section["experiments_done"] = c.fetchone()["done"]
                chapter["sections"].append(section)
            unit["chapters"].append(chapter)
        result.append(unit)
    conn.close()
    return jsonify(result)


@app.route("/api/activity/<int:activity_id>")
@login_required("kid")
def api_get_activity(activity_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT a.*, s.chapter_id FROM activities a JOIN sections s ON a.section_id=s.id WHERE a.id=?", (activity_id,))
    row = c.fetchone()
    lvl = unit_level_of_activity(c, activity_id)
    conn.close()
    if not row or lvl != current_user()["course_level"]:
        return jsonify({"error": "not_found"}), 404
    d = dict(row)
    d["data_json"] = json.loads(d["data_json"])
    return jsonify(d)


@app.route("/api/activity/<int:activity_id>/submit", methods=["POST"])
@login_required("kid")
def api_submit_activity(activity_id):
    data = request.get_json()
    score = data.get("score", 0)
    points_earned = max(0, int(data.get("points_earned", 0) or 0))
    answers = data.get("answers", {})
    time_spent = min(max(int(data.get("time_spent", 0) or 0), 0), 1800)
    partial = 1 if data.get("partial") else 0

    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM activities WHERE id=?", (activity_id,))
    act = c.fetchone()
    if not act or unit_level_of_activity(c, activity_id) != current_user()["course_level"]:
        conn.close()
        return jsonify({"error": "not_found"}), 404
    points_earned = min(points_earned, act["points"] + 10)      # never more than the activity is worth (+ bonus)

    c.execute("INSERT INTO activity_attempts (user_id,activity_id,score,points_earned,answers_json,time_spent,partial) VALUES (?,?,?,?,?,?,?)",
              (session["user_id"], activity_id, score, points_earned, json.dumps(answers), time_spent, partial))
    conn.commit()
    conn.close()

    update_user_stats(session["user_id"], points_earned)
    if not partial:                                  # an unfinished game never completes its section
        update_section_progress(session["user_id"], act["section_id"], score, points_earned)
    new_badges = check_badges(session["user_id"])

    stats = get_user_stats(session["user_id"])
    badge_rows = []
    if new_badges:
        conn2 = get_db()
        c2 = conn2.cursor()
        placeholders = ",".join("?" * len(new_badges))
        c2.execute(f"SELECT b.* FROM user_badges ub JOIN badges b ON ub.badge_id=b.id WHERE ub.user_id=? AND b.code IN ({placeholders})",
                   [session["user_id"]] + new_badges)
        badge_rows = [dict(r) for r in c2.fetchall()]
        conn2.close()

    return jsonify({
        "ok": True,
        "score": score,
        "points_earned": points_earned,
        "total_points": stats["total_points"],
        "total_stars": stats["total_stars"],
        "new_badges": badge_rows,
    })


@app.route("/api/experiment/<int:experiment_id>")
@login_required("kid")
def api_get_experiment(experiment_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM experiments WHERE id=?", (experiment_id,))
    row = c.fetchone()
    lvl = unit_level_of_activity(c, experiment_id, "experiments")
    conn.close()
    if not row or lvl != current_user()["course_level"]:
        return jsonify({"error": "not_found"}), 404
    d = dict(row)
    d["data_json"] = json.loads(d["data_json"])
    return jsonify(d)


@app.route("/api/experiment/<int:experiment_id>/complete", methods=["POST"])
@login_required("kid")
def api_complete_experiment(experiment_id):
    data = request.get_json()
    notes = data.get("notes", "")
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM experiments WHERE id=?", (experiment_id,))
    exp = c.fetchone()
    if not exp or unit_level_of_activity(c, experiment_id, "experiments") != current_user()["course_level"]:
        conn.close()
        return jsonify({"error": "not_found"}), 404

    c.execute("SELECT * FROM experiment_completions WHERE user_id=? AND experiment_id=?", (session["user_id"], experiment_id))
    existing = c.fetchone()
    if existing:
        c.execute("UPDATE experiment_completions SET completed=1,notes=?,completed_at=datetime('now','localtime') WHERE id=?", (notes, existing["id"]))
    else:
        c.execute("INSERT INTO experiment_completions (user_id,experiment_id,completed,notes) VALUES (?,?,1,?)",
                  (session["user_id"], experiment_id, notes))
    conn.commit()
    conn.close()

    update_user_stats(session["user_id"], exp["points"])
    update_section_progress(session["user_id"], exp["section_id"], 100, exp["points"])
    new_badges = check_badges(session["user_id"])
    stats = get_user_stats(session["user_id"])

    return jsonify({
        "ok": True,
        "points_earned": exp["points"],
        "total_points": stats["total_points"],
        "total_stars": stats["total_stars"],
        "new_badges": new_badges,
    })


@app.route("/api/parent/dashboard")
@login_required("parent", "admin")
def api_parent_dashboard():
    p = _parent_scope()
    if not p:
        return jsonify({"error": "parent_not_found"}), 404
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM users WHERE role='kid' AND parent_id=? ORDER BY display_name", (p["id"],))
    kids = c.fetchall()
    result = []
    for kid in kids:
        kid_data = {"id": kid["id"], "display_name": kid["display_name"], "username": kid["username"], "course_level": kid["course_level"],
                    "course": LV.level(kid["course_level"]), "avatar": kid["avatar"], "active": kid["active"]}
        stats = get_user_stats(kid["id"])
        kid_data["stats"] = stats

        c.execute("SELECT COUNT(*) as n FROM activity_attempts WHERE user_id=? AND partial=0", (kid["id"],))
        kid_data["total_attempts"] = c.fetchone()["n"]

        c.execute("SELECT AVG(score) as avg_score FROM activity_attempts WHERE user_id=? AND partial=0", (kid["id"],))
        row = c.fetchone()
        kid_data["avg_score"] = round(row["avg_score"], 1) if row["avg_score"] else 0

        c.execute("SELECT COUNT(DISTINCT experiment_id) as n FROM experiment_completions WHERE user_id=? AND completed=1", (kid["id"],))
        kid_data["experiments_done"] = c.fetchone()["n"]

        c.execute("""SELECT b.* FROM user_badges ub JOIN badges b ON ub.badge_id=b.id WHERE ub.user_id=? ORDER BY b.id""", (kid["id"],))
        kid_data["badges"] = [dict(r) for r in c.fetchall()]

        c.execute("""
            SELECT s.name as section_name, s.code as section_code,
                   ch.name as chapter_name, u.name as unit_name, u.color as unit_color,
                   sp.status, sp.best_score, sp.total_points, sp.activities_completed,
                   (SELECT COUNT(*) FROM activities WHERE section_id=s.id) as total_activities,
                   sp.last_accessed
            FROM sections s
            JOIN chapters ch ON s.chapter_id=ch.id
            JOIN units u ON ch.unit_id=u.id
            LEFT JOIN section_progress sp ON sp.section_id=s.id AND sp.user_id=?
            WHERE u.level=?
            ORDER BY u.sort_order, ch.sort_order, s.sort_order
        """, (kid["id"], kid["course_level"]))
        kid_data["section_progress"] = [dict(r) for r in c.fetchall()]

        c.execute("""
            SELECT aa.attempted_at, aa.score, aa.points_earned, aa.time_spent, aa.partial,
                   a.title, a.activity_type, s.name as section_name, s.code as section_code
            FROM activity_attempts aa
            JOIN activities a ON aa.activity_id=a.id
            JOIN sections s ON a.section_id=s.id
            WHERE aa.user_id=?
            ORDER BY aa.attempted_at DESC
            LIMIT 50
        """, (kid["id"],))
        kid_data["recent_activity"] = [dict(r) for r in c.fetchall()]

        c.execute("""
            SELECT ec.completed_at, e.title, s.name as section_name
            FROM experiment_completions ec
            JOIN experiments e ON ec.experiment_id=e.id
            JOIN sections s ON e.section_id=s.id
            WHERE ec.user_id=? AND ec.completed=1
            ORDER BY ec.completed_at DESC
        """, (kid["id"],))
        kid_data["experiments_completed"] = [dict(r) for r in c.fetchall()]

        result.append(kid_data)

    c.execute("SELECT * FROM badges ORDER BY id")
    all_badges = [dict(r) for r in c.fetchall()]
    conn.close()
    return jsonify({"kids": result, "all_badges": all_badges})


@app.route("/api/parent/reset", methods=["POST"])
@login_required("parent", "admin")
def api_parent_reset():
    data = request.get_json(silent=True) or {}
    k = managed_kid(data.get("kid_id"))
    if not k:
        return jsonify({"error": "غير مسموح"}), 403
    _reset_kid(k["id"])
    return jsonify({"ok": True})


def _reset_kid(kid_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM activity_attempts WHERE user_id=?", (kid_id,))
    c.execute("DELETE FROM experiment_completions WHERE user_id=?", (kid_id,))
    c.execute("DELETE FROM section_progress WHERE user_id=?", (kid_id,))
    c.execute("DELETE FROM user_badges WHERE user_id=?", (kid_id,))
    c.execute("UPDATE user_stats SET total_points=0, total_stars=0, streak_days=0 WHERE user_id=?", (kid_id,))
    conn.commit()
    conn.close()


# ================================================================ accounts: admin -> parents -> kids (same API as the other courses)
def fail(msg, code=400):
    return jsonify(error=msg), code


def body():
    return request.get_json(force=True, silent=True) or {}


def managed_kid(kid_id):
    """The kid the caller may manage (admin: any kid; parent: only its own), else None."""
    try:
        k = ACC.get(int(kid_id))
    except (TypeError, ValueError):
        return None
    return k if ACC.can_manage_kid(current_user(), k) else None


def _parent_scope():
    """The parent whose kids the request is about: a parent itself, or the admin's ?parent= / body parent_id."""
    u = current_user()
    if u["role"] == "parent":
        return u
    pid = request.args.get("parent") or body().get("parent_id")
    p = ACC.get(int(pid)) if pid and str(pid).isdigit() else None
    return p if p and p["role"] == "parent" else None


@app.post("/api/account/password")
@login_required("parent", "admin")
def api_own_password():
    d = body()
    u = current_user()
    if not ACC.check_pw(u, d.get("old", "")):
        return fail("كلمة المرور الحالية غير صحيحة")
    try:
        ACC.set_password(u["id"], d.get("new", ""))
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True)


@app.post("/api/kid/<int:kid_id>/reset")
@login_required("parent", "admin")
def api_kid_reset(kid_id):
    k = managed_kid(kid_id)
    if not k:
        return fail("غير مسموح", 403)
    _reset_kid(k["id"])
    return jsonify(ok=True)


@app.get("/api/kids")
@login_required("parent", "admin")
def api_kids():
    u = current_user()
    if u["role"] == "admin" and not request.args.get("parent"):
        return jsonify(kids=ACC.all_kids(), parents={p["id"]: p for p in ACC.parents()}, avatars=ACC.AVATARS, levels=LV.LEVELS)
    p = _parent_scope()
    if not p:
        return fail("ولي الأمر غير موجود", 404)
    return jsonify(kids=ACC.kids_of(p["id"]), parent=ACC.public(p), max_kids=p["max_kids"], avatars=ACC.AVATARS, levels=LV.LEVELS)


@app.post("/api/kids")
@login_required("parent", "admin")
def api_kid_create():
    p = _parent_scope()
    if not p:
        return fail("اختر ولي الأمر")
    d = body()
    password = d.get("password") or ACC.gen_password()
    try:
        k = ACC.create("kid", d.get("username"), d.get("display_name"), password, parent_id=p["id"], avatar=d.get("avatar", ""),
                       course_level=d.get("course_level", LV.DEFAULT))
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True, kid=ACC.public(k), password=password)


@app.post("/api/kids/<int:kid_id>")
@login_required("parent", "admin")
def api_kid_update(kid_id):
    k = managed_kid(kid_id)
    if not k:
        return fail("غير مسموح", 403)
    d = body()
    try:
        k = ACC.update(k["id"], **{f: d[f] for f in ("display_name", "avatar", "unlock_all", "active", "course_level") if f in d})
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True, kid=ACC.public(k))


@app.post("/api/kids/<int:kid_id>/password")
@login_required("parent", "admin")
def api_kid_password(kid_id):
    k = managed_kid(kid_id)
    if not k:
        return fail("غير مسموح", 403)
    password = body().get("password") or ACC.gen_password()
    try:
        ACC.set_password(k["id"], password)
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True, password=password, username=k["username"])


@app.post("/api/kids/<int:kid_id>/delete")
@login_required("parent", "admin")
def api_kid_delete(kid_id):
    k = managed_kid(kid_id)
    if not k:
        return fail("غير مسموح", 403)
    ACC.delete(k["id"])
    return jsonify(ok=True)


def _parent(pid):
    p = ACC.get(pid)
    return p if p and p["role"] == "parent" else None


@app.get("/api/admin/summary")
@login_required("admin")
def api_admin_summary():
    return jsonify(ACC.summary())


@app.get("/api/admin/parents")
@login_required("admin")
def api_admin_parents():
    return jsonify(parents=ACC.parents())


@app.post("/api/admin/parents")
@login_required("admin")
def api_admin_parent_create():
    d = body()
    password = d.get("password") or ACC.gen_password()
    try:
        p = ACC.create("parent", d.get("username"), d.get("display_name"), password, max_kids=d.get("max_kids", ACC.DEFAULT_MAX_KIDS))
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True, parent=ACC.public(p), password=password)


@app.post("/api/admin/parents/<int:pid>")
@login_required("admin")
def api_admin_parent_update(pid):
    if not _parent(pid):
        return fail("ولي الأمر غير موجود", 404)
    d = body()
    try:
        p = ACC.update(pid, **{f: d[f] for f in ("display_name", "max_kids", "active") if f in d})
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True, parent=ACC.public(p))


@app.post("/api/admin/parents/<int:pid>/password")
@login_required("admin")
def api_admin_parent_password(pid):
    p = _parent(pid)
    if not p:
        return fail("ولي الأمر غير موجود", 404)
    password = body().get("password") or ACC.gen_password()
    try:
        ACC.set_password(pid, password)
    except ACC.AccountError as e:
        return fail(str(e))
    return jsonify(ok=True, password=password, username=p["username"])


@app.post("/api/admin/parents/<int:pid>/delete")
@login_required("admin")
def api_admin_parent_delete(pid):
    if not _parent(pid):
        return fail("ولي الأمر غير موجود", 404)
    return jsonify(ok=True, deleted=ACC.delete(pid))


def init_app():
    from db_init import init_db
    init_db()
    ACC.migrate(os.environ)
    ACC.bootstrap(os.environ)
    build_curriculum_in_db()


if __name__ == "__main__":
    init_app()
    port = int(os.environ.get("PORT", 5050))
    debug = os.environ.get("FLASK_DEBUG", "0") == "1"
    app.run(debug=debug, port=port, host="0.0.0.0")