# -*- coding: utf-8 -*-
"""Accounts and tenancy: admin (platform) -> parent (tenant) -> kid (sub-tenant).

Every parent owns only the kids whose parent_id is theirs; a parent may own at most max_kids kids.
A suspended parent blocks its own login and the logins of all its kids."""
import re
import secrets
import hashlib
from werkzeug.security import generate_password_hash, check_password_hash
import db_init as db
import levels as CUR

ROLES = ("admin", "parent", "kid")
USER_RE = re.compile(r"^[a-z0-9][a-z0-9._-]{2,29}$")
AVATARS_BY_BAND = {
    "kids": ["🐱", "🐶", "🦊", "🐼", "🐨", "🐯", "🦁", "🐸", "🐵", "🦄", "🐰", "🐻", "🐧", "🦉", "🐙", "🦖", "🐬", "🦋", "🐢", "🚀", "⚽", "🌟"],
    "junior": ["🧑‍🚀", "🦸", "🥷", "🧙", "🐺", "🦅", "🐉", "🦈", "🎧", "🎮", "🏀", "⚽", "🎸", "🛹", "📷", "🧭", "⚡", "🔥", "🌍", "🚀", "🏆", "🎯"],
    "teen": ["🎧", "🎮", "🧠", "📚", "🖋️", "💻", "🎸", "🏀", "⚽", "🏎️", "🛹", "📷", "🌌", "🦅", "🐺", "🦁", "🏔️", "⚡", "🔥", "🌍", "🏆", "🎯"],
}
AVATARS = list(dict.fromkeys(AVATARS_BY_BAND["kids"] + AVATARS_BY_BAND["junior"] + AVATARS_BY_BAND["teen"]))   # every allowed avatar
DEFAULT_MAX_KIDS = 3


def default_avatar(level):
    """First avatar of the age band of the kid's course level (a cat for 8 years old, an explorer for 11)."""
    band = CUR.level(level).get("band", "kids") if CUR.level(level) else "kids"
    return AVATARS_BY_BAND.get(band, AVATARS_BY_BAND["kids"])[0]

SCHEMA = """
CREATE TABLE 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 1,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    last_login TEXT
)"""


class AccountError(Exception):
    """A user-facing problem (shown in Arabic in the dashboards)."""


def migrate(env=None):
    """Upgrade the old users table (kid/parent only) to the tenancy schema, keeping ids and data.
    The old app copied PARENT_*/KID_* from .env into its single parent and kid at every start; that copy is done one
    last time here, so the family keeps the login it had in .env and its progress."""
    c = db.get_db()
    sql = (c.execute("SELECT sql FROM sqlite_master WHERE name='users'").fetchone() or {"sql": ""})["sql"] or ""
    if "'admin'" not in sql and env:
        for role in ("parent", "kid"):
            row = c.execute("SELECT id FROM users WHERE role=? ORDER BY id LIMIT 1", (role,)).fetchone()
            un, pw = (env.get(f"{role.upper()}_USERNAME") or "").strip().lower(), env.get(f"{role.upper()}_PASSWORD")
            if row and un and pw and not c.execute("SELECT 1 FROM users WHERE username=? AND id<>?", (un, row["id"])).fetchone():
                c.execute("UPDATE users SET username=?, password_hash=?, display_name=? WHERE id=?",
                          (un, _legacy(pw), env.get(f"{role.upper()}_DISPLAY_NAME") or un, row["id"]))
        c.commit()
    if "'admin'" not in sql:
        c.execute("PRAGMA foreign_keys=OFF")
        c.execute("PRAGMA legacy_alter_table=ON")      # do not rewrite the other tables' REFERENCES users(id)
        old_cols = [r["name"] for r in c.execute("PRAGMA table_info(users)").fetchall()]
        c.execute("ALTER TABLE users RENAME TO users_old")
        c.execute(SCHEMA)
        keep = [x for x in ("id", "username", "role", "password_hash", "display_name", "created_at") if x in old_cols]
        c.execute(f"INSERT INTO users ({','.join(keep)}) SELECT {','.join(keep)} FROM users_old")
        c.execute("DROP TABLE users_old")
        # the single-family install: every existing kid belongs to the first parent; the global unlock setting moves to each kid
        par = c.execute("SELECT id FROM users WHERE role='parent' ORDER BY id LIMIT 1").fetchone()
        if par:
            c.execute("UPDATE users SET parent_id=? WHERE role='kid'", (par["id"],))
        c.execute("UPDATE users SET course_level=? WHERE role='kid'", (CUR.DEFAULT,))   # the existing course is Level 2
        c.commit()
        c.execute("PRAGMA legacy_alter_table=OFF")
        c.execute("PRAGMA foreign_keys=ON")
    if "course_level" not in [r["name"] for r in c.execute("PRAGMA table_info(users)").fetchall()]:
        c.execute("ALTER TABLE users ADD COLUMN course_level INTEGER DEFAULT 1")
    c.execute("UPDATE users SET avatar=? WHERE role='kid' AND (avatar IS NULL OR avatar='')", (AVATARS[0],))
    c.commit()
    c.close()


# ---------------------------------------------------------------- passwords
def hash_pw(pw):
    return generate_password_hash(pw, method="pbkdf2:sha256:600000")


def _legacy(pw):
    """The Science app's first version stored a plain sha256 of the password."""
    return hashlib.sha256(pw.encode("utf-8")).hexdigest()


def check_pw(user, pw):
    h = user["password_hash"]
    if re.fullmatch(r"[0-9a-f]{64}", h):          # old hash: accept once, then upgrade
        if h == _legacy(pw):
            c = db.get_db()                          # upgrade to a salted hash (even if the old password is short)
            c.execute("UPDATE users SET password_hash=? WHERE id=?", (hash_pw(pw), user["id"]))
            c.commit(); c.close()
            return True
        return False
    return check_password_hash(h, pw)


def gen_password():
    """Easy for a child to type: 2 short words + 2 digits, e.g. «sun-kite-47»."""
    words = ["sun", "cat", "book", "star", "kite", "frog", "moon", "cake", "ball", "tree", "fish", "bus", "duck", "lamp", "ship", "rain"]
    w = [secrets.choice(words).replace("é", "e").replace("è", "e") for _ in range(2)]
    return f"{w[0]}-{w[1]}-{secrets.randbelow(90) + 10}"


def check_new_password(pw):
    if not pw or len(pw) < 6:
        raise AccountError("كلمة المرور يجب أن تكون 6 أحرف على الأقل")
    if len(pw) > 100:
        raise AccountError("كلمة المرور طويلة جداً")


# ---------------------------------------------------------------- reading
def get(uid):
    c = db.get_db()
    r = c.execute("SELECT * FROM users WHERE id=?", (uid,)).fetchone()
    c.close()
    return dict(r) if r else None


def by_username(username):
    c = db.get_db()
    r = c.execute("SELECT * FROM users WHERE username=?", ((username or "").strip().lower(),)).fetchone()
    c.close()
    return dict(r) if r else None


def public(u):
    """What the dashboards may see of an account (never the password hash)."""
    return {k: u.get(k) for k in ("id", "username", "role", "display_name", "parent_id", "max_kids", "active", "avatar", "unlock_all", "course_level", "created_at", "last_login")}


def can_login(u):
    """Active account, and for a kid an active parent."""
    if not u or not u["active"]:
        return False
    if u["role"] == "kid" and u["parent_id"]:
        p = get(u["parent_id"])
        return bool(p and p["active"])
    return True


def authenticate(username, password):
    u = by_username(username)
    if not u or not check_pw(u, password or ""):
        return None, "اسم المستخدم أو كلمة المرور غير صحيحة"
    if not can_login(u):
        return None, "هذا الحساب موقوف. تواصل مع المسؤول."
    c = db.get_db()
    c.execute("UPDATE users SET last_login=datetime('now','localtime') WHERE id=?", (u["id"],))
    c.commit()
    c.close()
    return u, None


def kid_stats_sql(where, args):
    """Kids with their totals in one query: xp, finished activities, last activity, minutes."""
    c = db.get_db()
    rows = c.execute(f"""
        SELECT u.*, COALESCE(a.xp,0) AS xp, COALESCE(a.secs,0) AS secs, a.last_at AS last_activity,
               COALESCE(a.done,0) AS done, COALESCE(p.stars,0) AS stars, COALESCE(p.pts,0) AS pts
        FROM users u
        LEFT JOIN (SELECT user_id, SUM(points_earned) xp, SUM(time_spent) secs, MAX(attempted_at) last_at, COUNT(DISTINCT CASE WHEN partial=0 THEN activity_id END) done
                   FROM activity_attempts GROUP BY user_id) a ON a.user_id=u.id
        LEFT JOIN (SELECT user_id, total_points pts, total_stars stars FROM user_stats) p ON p.user_id=u.id
        WHERE u.role='kid' AND {where} ORDER BY u.display_name COLLATE NOCASE""", args).fetchall()
    c.close()
    out = []
    for r in rows:
        d = public(dict(r))
        d.update(xp=max(r["xp"], r["pts"]), minutes=round(r["secs"] / 60), last_activity=r["last_activity"], done=r["done"], stars=r["stars"])
        out.append(d)
    return out


def kids_of(parent_id):
    return kid_stats_sql("u.parent_id=?", (parent_id,))


def all_kids():
    return kid_stats_sql("1=1", ())


def parents():
    c = db.get_db()
    rows = c.execute("""SELECT u.*, (SELECT COUNT(*) FROM users k WHERE k.parent_id=u.id AND k.role='kid') AS kids
                        FROM users u WHERE u.role='parent' ORDER BY u.display_name COLLATE NOCASE""").fetchall()
    c.close()
    return [dict(public(dict(r)), kids=r["kids"]) for r in rows]


def count_kids(parent_id):
    c = db.get_db()
    n = c.execute("SELECT COUNT(*) n FROM users WHERE role='kid' AND parent_id=?", (parent_id,)).fetchone()["n"]
    c.close()
    return n


def summary():
    c = db.get_db()
    q = lambda s, a=(): c.execute(s, a).fetchone()[0]
    out = dict(parents=q("SELECT COUNT(*) FROM users WHERE role='parent'"), kids=q("SELECT COUNT(*) FROM users WHERE role='kid'"),
               active_parents=q("SELECT COUNT(*) FROM users WHERE role='parent' AND active=1"),
               kids_today=q("SELECT COUNT(DISTINCT user_id) FROM activity_attempts WHERE date(attempted_at)=date('now','localtime')"),
               kids_week=q("SELECT COUNT(DISTINCT user_id) FROM activity_attempts WHERE attempted_at>=datetime('now','localtime','-7 days')"),
               attempts_week=q("SELECT COUNT(*) FROM activity_attempts WHERE attempted_at>=datetime('now','localtime','-7 days')"),
               minutes_week=round(q("SELECT COALESCE(SUM(time_spent),0) FROM activity_attempts WHERE attempted_at>=datetime('now','localtime','-7 days')") / 60),
               seats=q("SELECT COALESCE(SUM(max_kids),0) FROM users WHERE role='parent'"),
               per_level={r[0]: r[1] for r in c.execute("SELECT course_level, COUNT(*) FROM users WHERE role='kid' GROUP BY course_level").fetchall()})
    c.close()
    return out


# ---------------------------------------------------------------- writing
def _clean_username(username):
    u = (username or "").strip().lower()
    if not USER_RE.match(u):
        raise AccountError("اسم المستخدم: 3 إلى 30 حرفاً إنجليزياً صغيراً أو أرقاماً (ويُسمح بـ . _ -)، ويبدأ بحرف أو رقم")
    if by_username(u):
        raise AccountError(f"اسم المستخدم «{u}» مستخدم من قبل، اختر اسماً آخر")
    return u


def _clean_name(name):
    n = (name or "").strip()
    if not n or len(n) > 40:
        raise AccountError("الاسم المعروض مطلوب (40 حرفاً على الأكثر)")
    return n


def _clean_max(v):
    try:
        v = int(v)
    except (TypeError, ValueError):
        raise AccountError("عدد الأطفال المسموح يجب أن يكون رقماً")
    if not 0 <= v <= 50:
        raise AccountError("عدد الأطفال المسموح بين 0 و50")
    return v


def _clean_level(v):
    try:
        v = int(v)
    except (TypeError, ValueError):
        raise AccountError("اختر المستوى الدراسي")
    if v not in CUR.AVAILABLE:
        lv = CUR.level(v)
        raise AccountError(f"«{lv['title_ar']}» غير متاح بعد" if lv else "مستوى دراسي غير معروف")
    return v


def create(role, username, display_name, password, parent_id=None, max_kids=None, avatar="", course_level=None):
    assert role in ROLES
    u, n = _clean_username(username), _clean_name(display_name)
    check_new_password(password)
    if role == "kid":
        par = get(parent_id)
        if not par or par["role"] != "parent":
            raise AccountError("ولي الأمر غير موجود")
        if count_kids(parent_id) >= par["max_kids"]:
            raise AccountError(f"وصل ولي الأمر إلى الحد المسموح ({par['max_kids']} أطفال). يمكن للمسؤول رفع الحد.")
        if avatar and avatar not in AVATARS:
            avatar = ""
    mk = _clean_max(max_kids if max_kids is not None else DEFAULT_MAX_KIDS) if role == "parent" else 0
    lvl = _clean_level(course_level if course_level is not None else getattr(CUR, "DEFAULT", 1)) if role == "kid" else 1
    c = db.get_db()
    cur = c.execute("INSERT INTO users (username, role, password_hash, display_name, parent_id, max_kids, avatar, course_level) VALUES (?,?,?,?,?,?,?,?)",
                    (u, role, hash_pw(password), n, parent_id if role == "kid" else None, mk, avatar or (default_avatar(lvl) if role == "kid" else ""), lvl))
    c.commit()
    uid = cur.lastrowid
    c.close()
    return get(uid)


def update(uid, **f):
    u = get(uid)
    if not u:
        raise AccountError("الحساب غير موجود")
    sets = {}
    if "display_name" in f:
        sets["display_name"] = _clean_name(f["display_name"])
    if "active" in f:
        sets["active"] = 1 if f["active"] else 0
    if "avatar" in f and u["role"] == "kid":
        if f["avatar"] not in AVATARS:
            raise AccountError("صورة رمزية غير معروفة")
        sets["avatar"] = f["avatar"]
    if "course_level" in f and u["role"] == "kid":
        sets["course_level"] = _clean_level(f["course_level"])
    if "unlock_all" in f and u["role"] == "kid":
        sets["unlock_all"] = 1 if f["unlock_all"] else 0
    if "max_kids" in f and u["role"] == "parent":
        mk = _clean_max(f["max_kids"])
        have = count_kids(uid)
        if mk < have:
            raise AccountError(f"لدى ولي الأمر {have} أطفال حالياً، لا يمكن جعل الحد أقل من ذلك")
        sets["max_kids"] = mk
    if sets:
        c = db.get_db()
        c.execute(f"UPDATE users SET {', '.join(k + '=?' for k in sets)} WHERE id=?", (*sets.values(), uid))
        c.commit()
        c.close()
    return get(uid)


def set_password(uid, pw):
    check_new_password(pw)
    c = db.get_db()
    c.execute("UPDATE users SET password_hash=? WHERE id=?", (hash_pw(pw), uid))
    c.commit()
    c.close()


def delete(uid):
    """Delete an account with everything it owns (a parent takes its kids and their progress with it)."""
    u = get(uid)
    if not u or u["role"] == "admin":
        raise AccountError("لا يمكن حذف هذا الحساب")
    ids = [uid]
    if u["role"] == "parent":
        c = db.get_db()
        ids += [r["id"] for r in c.execute("SELECT id FROM users WHERE parent_id=?", (uid,)).fetchall()]
        c.close()
    c = db.get_db()
    for i in ids:
        for t in ("activity_attempts", "experiment_completions", "section_progress", "user_badges", "user_stats"):
            c.execute(f"DELETE FROM {t} WHERE user_id=?", (i,))
        c.execute("DELETE FROM users WHERE id=?", (i,))
    c.commit()
    c.close()
    return len(ids)


# ---------------------------------------------------------------- permissions
def can_manage_kid(actor, kid):
    """Admin manages every kid; a parent only its own."""
    if not actor or not kid or kid["role"] != "kid":
        return False
    return actor["role"] == "admin" or (actor["role"] == "parent" and kid["parent_id"] == actor["id"])


# ---------------------------------------------------------------- first start
def bootstrap(env):
    """Create the admin and the first family from the environment when they do not exist yet.
    Passwords set here are only initial ones: later changes from the dashboards are never overwritten,
    except the admin's when ADMIN_RESET_PASSWORD=1 (recovery)."""
    # ADMIN_*; the old Node app's AUTH_USERNAME / AUTH_PASSWORD are accepted so its admin keeps the same login
    admin_u = (env.get("ADMIN_USERNAME") or env.get("AUTH_USERNAME") or "admin").lower()
    admin_pw = env.get("ADMIN_PASSWORD") or env.get("AUTH_PASSWORD")
    a = by_username(admin_u)
    if not a:
        pw = admin_pw or secrets.token_urlsafe(12)
        create("admin", admin_u, env.get("ADMIN_DISPLAY_NAME") or "Admin", pw)
        if not admin_pw:
            print(f"*** admin account created: username={admin_u} password={pw} (set ADMIN_PASSWORD in .env to choose your own) ***", flush=True)
    elif env.get("ADMIN_RESET_PASSWORD") == "1" and admin_pw:
        set_password(a["id"], admin_pw)
    pu, ku = (env.get("PARENT_USERNAME") or "").lower(), (env.get("KID_USERNAME") or "").lower()
    try:
        if pu and not by_username(pu) and env.get("PARENT_PASSWORD"):
            create("parent", pu, env.get("PARENT_DISPLAY_NAME") or pu, env["PARENT_PASSWORD"])
        p = by_username(pu) if pu else None
        if ku and p and not by_username(ku) and env.get("KID_PASSWORD"):
            create("kid", ku, env.get("KID_DISPLAY_NAME") or ku, env["KID_PASSWORD"], parent_id=p["id"])
    except AccountError as e:          # a bad initial value in .env must not stop the app
        print(f"*** initial family from .env not created: {e} ***", flush=True)