import os
import sqlite3
import hashlib
import uuid
import secrets
from datetime import datetime, timedelta
from typing import Optional, List
from flask import Flask, request, redirect, jsonify, make_response, send_file, g
import json
import io

print("=== APP STARTING ===")
app = Flask(__name__)
app.secret_key = secrets.token_hex(32)

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "server.db")


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        license_status TEXT DEFAULT 'active',
        max_devices INTEGER DEFAULT 3,
        activation_key TEXT UNIQUE NOT NULL,
        created_at TEXT NOT NULL
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS devices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        device_name TEXT NOT NULL,
        os TEXT NOT NULL,
        hwid TEXT UNIQUE NOT NULL,
        token TEXT UNIQUE NOT NULL,
        is_blocked INTEGER DEFAULT 0,
        last_checkin TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS commands (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        command_type TEXT NOT NULL,
        payload TEXT,
        executed INTEGER DEFAULT 0,
        created_at TEXT NOT NULL,
        FOREIGN KEY (device_id) REFERENCES devices(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS stats (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        sheets_checked INTEGER DEFAULT 0,
        templates_created INTEGER DEFAULT 0,
        timestamp TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS templates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        content TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS results (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        template_name TEXT NOT NULL,
        data TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS requested_downloads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        type TEXT NOT NULL,
        target_id INTEGER NOT NULL,
        target_name TEXT NOT NULL,
        status TEXT DEFAULT 'pending',
        content TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS demanded_data (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER NOT NULL,
        demand_type TEXT NOT NULL,
        status TEXT DEFAULT 'pending',
        payload TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (device_id) REFERENCES devices(id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS portal_users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        role TEXT NOT NULL,
        created_at TEXT NOT NULL
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS portal_sessions (
        token TEXT PRIMARY KEY,
        portal_user_id INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        expires_at TEXT NOT NULL,
        FOREIGN KEY (portal_user_id) REFERENCES portal_users(id)
    )
    """)

    cursor.execute("SELECT COUNT(*) FROM users")
    if cursor.fetchone()[0] == 0:
        pwd_hash = hashlib.sha256(b"saral123").hexdigest()
        activation_key = "SARAL-TEST-KEY-2082"
        cursor.execute(
            "INSERT INTO users (email, password_hash, license_status, max_devices, activation_key, created_at) VALUES (?, ?, ?, ?, ?, ?)",
            ("test@saralomr.com", pwd_hash, "active", 3, activation_key, datetime.now().isoformat())
        )

    cursor.execute("SELECT COUNT(*) FROM portal_users")
    if cursor.fetchone()[0] == 0:
        pwd_hash = hashlib.sha256(b"saral123").hexdigest()
        now = datetime.now().isoformat()
        for uname, email, role in [
            ("admin", "admin@saralomr.com", "admin"),
            ("staff", "staff@saralomr.com", "staff"),
            ("viewer", "viewer@saralomr.com", "viewer"),
        ]:
            cursor.execute(
                "INSERT INTO portal_users (username, email, password_hash, role, created_at) VALUES (?, ?, ?, ?, ?)",
                (uname, email, pwd_hash, role, now)
            )

    conn.commit()
    conn.close()


init_db()
print("=== DB INITIALIZED ===")


# ==================== HELPERS ====================

def get_user_by_token(token, db):
    cursor = db.cursor()
    cursor.execute("""
        SELECT u.*, d.id as device_id, d.is_blocked, d.hwid as dev_hwid
        FROM users u
        JOIN devices d ON u.id = d.user_id
        WHERE d.token = ?
    """, (token,))
    return cursor.fetchone()


def get_current_portal_user():
    token = request.cookies.get("portal_session")
    if not token:
        return None
    db = get_db()
    cursor = db.cursor()
    cursor.execute("""
        SELECT u.* FROM portal_users u
        JOIN portal_sessions s ON u.id = s.portal_user_id
        WHERE s.token = ? AND s.expires_at > ?
    """, (token, datetime.now().isoformat()))
    user = cursor.fetchone()
    db.close()
    if user:
        return dict(user)
    return None


def require_portal_auth(roles=None):
    """Call at top of route. Returns (user, error_response)."""
    user = get_current_portal_user()
    if not user:
        return None, (jsonify({"status": "error", "message": "Unauthorized"}), 401)
    if roles and user["role"] not in roles:
        return None, (jsonify({"status": "error", "message": "Permission Denied"}), 403)
    return user, None


def load_html(filename):
    path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "templates", filename)
    with open(path, "r", encoding="utf-8") as f:
        return f.read()


# ==================== CLIENT APIS ====================

@app.route("/api/activate", methods=["POST"])
def api_activate():
    data = request.get_json(force=True)
    activation_key = data.get("activation_key")
    device_name = data.get("device_name")
    os_name = data.get("os")
    hwid = data.get("hwid")

    db = get_db()
    cursor = db.cursor()

    cursor.execute("SELECT * FROM users WHERE activation_key = ?", (activation_key,))
    user = cursor.fetchone()

    if not user:
        db.close()
        return jsonify({"detail": "Invalid activation key."}), 401

    if user["license_status"] == "banned":
        db.close()
        return jsonify({"detail": "Your account has been banned."}), 403

    if user["license_status"] == "revoked":
        db.close()
        return jsonify({"detail": "Your license has been revoked. Contact administrator."}), 403

    cursor.execute("SELECT * FROM devices WHERE user_id = ?", (user["id"],))
    devices = cursor.fetchall()

    existing_device = None
    for d in devices:
        if d["hwid"] == hwid:
            existing_device = d
            break

    if not existing_device:
        if len(devices) >= user["max_devices"]:
            db.close()
            return jsonify({"detail": f"Device limit exceeded. Max allowed: {user['max_devices']}. Contact support to upgrade."}), 403
        token = "tok_" + secrets.token_urlsafe(32)
        cursor.execute(
            "INSERT INTO devices (user_id, device_name, os, hwid, token, is_blocked, last_checkin) VALUES (?, ?, ?, ?, ?, 0, ?)",
            (user["id"], device_name, os_name, hwid, token, datetime.now().isoformat())
        )
        db.commit()
        cursor.execute("SELECT * FROM devices WHERE hwid = ?", (hwid,))
        existing_device = cursor.fetchone()
    else:
        if existing_device["is_blocked"] == 1:
            db.close()
            return jsonify({"detail": "This specific device is blocked."}), 403
        cursor.execute(
            "UPDATE devices SET last_checkin = ?, device_name = ?, os = ? WHERE id = ?",
            (datetime.now().isoformat(), device_name, os_name, existing_device["id"])
        )
        db.commit()

    result = {
        "status": "success",
        "token": existing_device["token"],
        "activation_key": user["activation_key"],
        "email": user["email"]
    }
    db.close()
    return jsonify(result)


@app.route("/api/checkin", methods=["POST"])
def api_checkin():
    data = request.get_json(force=True)
    token = data.get("token")
    sheets_checked_delta = data.get("sheets_checked_delta", 0)
    templates_created_delta = data.get("templates_created_delta", 0)
    hwid = data.get("hwid")

    db = get_db()
    user = get_user_by_token(token, db)
    if not user:
        db.close()
        return jsonify({"detail": "Unauthorized session."}), 401

    if user["license_status"] == "banned":
        db.close()
        return jsonify({"status": "banned", "message": "Account has been banned."})

    if user["license_status"] == "revoked":
        db.close()
        return jsonify({"status": "revoked", "message": "License revoked."})

    if user["is_blocked"] == 1:
        db.close()
        return jsonify({"status": "blocked", "message": "This device is blocked."})

    if user["dev_hwid"] != hwid:
        db.close()
        return jsonify({"detail": "Hardware ID mismatch."}), 400

    cursor = db.cursor()
    cursor.execute("UPDATE devices SET last_checkin = ? WHERE id = ?", (datetime.now().isoformat(), user["device_id"]))

    if sheets_checked_delta > 0 or templates_created_delta > 0:
        cursor.execute(
            "INSERT INTO stats (user_id, sheets_checked, templates_created, timestamp) VALUES (?, ?, ?, ?)",
            (user["id"], sheets_checked_delta, templates_created_delta, datetime.now().isoformat())
        )

    cursor.execute("SELECT * FROM requested_downloads WHERE user_id = ? AND status = 'pending'", (user["id"],))
    for pd in cursor.fetchall():
        payload_str = f"{pd['type']}||{pd['target_name']}||{pd['id']}"
        cursor.execute(
            "INSERT INTO commands (device_id, command_type, payload, executed, created_at) VALUES (?, 'upload_file', ?, 0, ?)",
            (user["device_id"], payload_str, datetime.now().isoformat())
        )
        cursor.execute("UPDATE requested_downloads SET status = 'requested', updated_at = ? WHERE id = ?",
                       (datetime.now().isoformat(), pd["id"]))

    cursor.execute("SELECT * FROM demanded_data WHERE device_id = ? AND status = 'pending'", (user["device_id"],))
    for pm in cursor.fetchall():
        payload_str = f"{pm['demand_type']}||{pm['id']}"
        cursor.execute(
            "INSERT INTO commands (device_id, command_type, payload, executed, created_at) VALUES (?, 'demand_data', ?, 0, ?)",
            (user["device_id"], payload_str, datetime.now().isoformat())
        )
        cursor.execute("UPDATE demanded_data SET status = 'requested', updated_at = ? WHERE id = ?",
                       (datetime.now().isoformat(), pm["id"]))

    cursor.execute("SELECT * FROM commands WHERE device_id = ? AND executed = 0", (user["device_id"],))
    cmds = cursor.fetchall()

    response_commands = []
    for cmd in cmds:
        response_commands.append({"id": cmd["id"], "type": cmd["command_type"], "payload": cmd["payload"]})
        cursor.execute("UPDATE commands SET executed = 1 WHERE id = ?", (cmd["id"],))

    db.commit()
    db.close()
    return jsonify({"status": "active", "commands": response_commands})


@app.route("/api/sync/templates", methods=["POST"])
def api_sync_templates():
    data = request.get_json(force=True)
    token = data.get("token")
    name = data.get("name")
    content = data.get("content", "")

    db = get_db()
    user = get_user_by_token(token, db)
    if not user or user["license_status"] != "active" or user["is_blocked"] == 1:
        db.close()
        return jsonify({"detail": "Unauthorized"}), 401

    cursor = db.cursor()
    cursor.execute("SELECT id FROM templates WHERE user_id = ? AND name = ?", (user["id"], name))
    row = cursor.fetchone()
    dummy_content = ""
    if row:
        cursor.execute("UPDATE templates SET content = ?, updated_at = ? WHERE id = ?",
                       (dummy_content, datetime.now().isoformat(), row["id"]))
    else:
        cursor.execute("INSERT INTO templates (user_id, name, content, updated_at) VALUES (?, ?, ?, ?)",
                       (user["id"], name, dummy_content, datetime.now().isoformat()))
    db.commit()
    db.close()
    return jsonify({"status": "success"})


@app.route("/api/sync/results", methods=["POST"])
def api_sync_results():
    data = request.get_json(force=True)
    token = data.get("token")
    template_name = data.get("template_name")
    result_data = data.get("data", "")

    db = get_db()
    user = get_user_by_token(token, db)
    if not user or user["license_status"] != "active" or user["is_blocked"] == 1:
        db.close()
        return jsonify({"detail": "Unauthorized"}), 401

    cursor = db.cursor()
    cursor.execute("INSERT INTO results (user_id, template_name, data, updated_at) VALUES (?, ?, ?, ?)",
                   (user["id"], template_name, "", datetime.now().isoformat()))
    db.commit()
    db.close()
    return jsonify({"status": "success"})


@app.route("/api/upload/file", methods=["POST"])
def api_upload_file():
    data = request.get_json(force=True)
    token = data.get("token")
    req_id = data.get("req_id")
    file_type = data.get("file_type")
    content = data.get("content", "")

    db = get_db()
    user = get_user_by_token(token, db)
    if not user:
        db.close()
        return jsonify({"detail": "Unauthorized"}), 401

    cursor = db.cursor()
    cursor.execute("SELECT * FROM requested_downloads WHERE id = ? AND user_id = ?", (req_id, user["id"]))
    req = cursor.fetchone()

    if req:
        cursor.execute("UPDATE requested_downloads SET status = 'uploaded', content = ?, updated_at = ? WHERE id = ?",
                       (content, datetime.now().isoformat(), req_id))
        if file_type == "template":
            cursor.execute("UPDATE templates SET content = ?, updated_at = ? WHERE id = ?",
                           (content, datetime.now().isoformat(), req["target_id"]))
        elif file_type == "result":
            cursor.execute("UPDATE results SET data = ?, updated_at = ? WHERE id = ?",
                           (content, datetime.now().isoformat(), req["target_id"]))
        db.commit()
        db.close()
        return jsonify({"status": "success"})

    db.close()
    return jsonify({"detail": "Request record not found."}), 404


@app.route("/api/upload/demand", methods=["POST"])
def api_upload_demand():
    data = request.get_json(force=True)
    token = data.get("token")
    demand_id = data.get("demand_id")
    payload = data.get("payload", "")

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT id FROM devices WHERE token = ?", (token,))
    dev = cursor.fetchone()
    if not dev:
        db.close()
        return jsonify({"detail": "Unauthorized device."}), 401

    cursor.execute("UPDATE demanded_data SET status = 'received', payload = ?, updated_at = ? WHERE id = ?",
                   (payload, datetime.now().isoformat(), demand_id))
    db.commit()
    db.close()
    return jsonify({"status": "success"})


# ==================== PORTAL WEB INTERFACE ====================

@app.route("/health")
def health():
    return jsonify({"status": "ok"})


@app.route("/")
def index():
    user = get_current_portal_user()
    if user:
        return redirect("/admin", 303)
    return redirect("/login", 303)


@app.route("/login", methods=["GET"])
def login_page():
    user = get_current_portal_user()
    if user:
        return redirect("/admin", 303)
    return load_html("login.html")


@app.route("/login", methods=["POST"])
def login_post():
    username = request.form.get("username")
    password = request.form.get("password")

    db = get_db()
    cursor = db.cursor()
    pwd_hash = hashlib.sha256(password.encode()).hexdigest()
    cursor.execute(
        "SELECT * FROM portal_users WHERE (username = ? OR email = ?) AND password_hash = ?",
        (username, username, pwd_hash)
    )
    user = cursor.fetchone()
    if not user:
        db.close()
        return jsonify({"status": "error", "message": "Invalid username/email or password."}), 400

    token = secrets.token_hex(32)
    now = datetime.now()
    expires = now + timedelta(days=7)
    cursor.execute(
        "INSERT INTO portal_sessions (token, portal_user_id, created_at, expires_at) VALUES (?, ?, ?, ?)",
        (token, user["id"], now.isoformat(), expires.isoformat())
    )
    db.commit()
    db.close()

    resp = make_response(jsonify({"status": "success", "message": "Logged in successfully."}))
    resp.set_cookie("portal_session", token, max_age=7 * 24 * 3600, httponly=True, samesite="Lax")
    return resp


@app.route("/logout")
def logout():
    token = request.cookies.get("portal_session")
    if token:
        db = get_db()
        cursor = db.cursor()
        cursor.execute("DELETE FROM portal_sessions WHERE token = ?", (token,))
        db.commit()
        db.close()
    resp = make_response(redirect("/login", 303))
    resp.delete_cookie("portal_session")
    return resp


@app.route("/admin")
def get_admin_dashboard():
    user = get_current_portal_user()
    if not user:
        return redirect("/login", 303)
    return load_html("admin.html")


@app.route("/admin/api/dashboard-data")
def get_admin_api_data():
    user = get_current_portal_user()
    if not user:
        return jsonify({"detail": "Unauthorized"}), 401

    db = get_db()
    cursor = db.cursor()

    cursor.execute("SELECT COUNT(*) FROM users")
    total_users = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM devices")
    total_devices = cursor.fetchone()[0]

    cursor.execute("SELECT SUM(sheets_checked) FROM stats")
    row_sheets = cursor.fetchone()
    total_sheets = row_sheets[0] if row_sheets[0] else 0

    cursor.execute("SELECT SUM(templates_created) FROM stats")
    row_temps = cursor.fetchone()
    total_templates = row_temps[0] if row_temps[0] else 0

    cursor.execute("SELECT * FROM users ORDER BY created_at DESC")
    users = [dict(r) for r in cursor.fetchall()]

    cursor.execute("SELECT d.*, u.email as user_email FROM devices d JOIN users u ON d.user_id = u.id ORDER BY d.last_checkin DESC")
    devices = [dict(r) for r in cursor.fetchall()]

    cursor.execute("SELECT t.id, t.name, t.updated_at, LENGTH(t.content) as content_size, u.email as user_email FROM templates t JOIN users u ON t.user_id = u.id ORDER BY t.updated_at DESC")
    templates = [dict(r) for r in cursor.fetchall()]

    cursor.execute("SELECT r.id, r.template_name, r.updated_at, LENGTH(r.data) as data_size, u.email as user_email FROM results r JOIN users u ON r.user_id = u.id ORDER BY r.updated_at DESC")
    results = [dict(r) for r in cursor.fetchall()]

    cursor.execute("SELECT c.*, d.device_name, d.hwid, u.email as user_email FROM commands c JOIN devices d ON c.device_id = d.id JOIN users u ON d.user_id = u.id ORDER BY c.created_at DESC LIMIT 150")
    commands = [dict(r) for r in cursor.fetchall()]

    portal_users = []
    if user["role"] == "admin":
        cursor.execute("SELECT id, username, email, role, created_at FROM portal_users ORDER BY role, username")
        portal_users = [dict(r) for r in cursor.fetchall()]

    cursor.execute("SELECT substr(timestamp, 1, 10) as stat_date, SUM(sheets_checked) as daily_sheets, SUM(templates_created) as daily_templates FROM stats GROUP BY stat_date ORDER BY stat_date ASC LIMIT 14")
    chart_rows = cursor.fetchall()

    chart_dates, chart_sheets, chart_templates = [], [], []
    for r in chart_rows:
        chart_dates.append(r["stat_date"])
        chart_sheets.append(r["daily_sheets"] or 0)
        chart_templates.append(r["daily_templates"] or 0)

    if not chart_rows:
        for i in range(6, -1, -1):
            day = (datetime.now() - timedelta(days=i)).strftime("%Y-%m-%d")
            chart_dates.append(day)
            chart_sheets.append(0)
            chart_templates.append(0)

    cursor.execute("SELECT d.device_name, d.os, d.last_checkin, u.email FROM devices d JOIN users u ON d.user_id = u.id ORDER BY d.last_checkin DESC LIMIT 8")
    recent_checkins = cursor.fetchall()

    cursor.execute("SELECT t.name, t.updated_at, u.email FROM templates t JOIN users u ON t.user_id = u.id ORDER BY t.updated_at DESC LIMIT 8")
    recent_templates = cursor.fetchall()

    cursor.execute("SELECT r.template_name, r.updated_at, u.email FROM results r JOIN users u ON r.user_id = u.id ORDER BY r.updated_at DESC LIMIT 8")
    recent_results = cursor.fetchall()

    activities = []
    for rc in recent_checkins:
        activities.append({"time": rc["last_checkin"], "type": "checkin", "icon": "bi-laptop",
                            "desc": f"Device <strong>{rc['device_name']}</strong> checked in ({rc['os']})", "user": rc["email"]})
    for rt in recent_templates:
        activities.append({"time": rt["updated_at"], "type": "template", "icon": "bi-layout-text-window-reverse",
                            "desc": f"Template <strong>{rt['name']}</strong> synced", "user": rt["email"]})
    for rr in recent_results:
        activities.append({"time": rr["updated_at"], "type": "result", "icon": "bi-journal-check",
                            "desc": f"Checked Results for <strong>{rr['template_name']}</strong> synced", "user": rr["email"]})

    activities.sort(key=lambda x: x["time"], reverse=True)
    activities = activities[:10]

    db.close()

    return jsonify({
        "user": {"username": user["username"], "email": user["email"], "role": user["role"]},
        "stats": {"total_users": total_users, "total_devices": total_devices,
                  "total_sheets": total_sheets, "total_templates": total_templates},
        "chart_data": {"labels": chart_dates, "sheets": chart_sheets, "templates": chart_templates},
        "users": users,
        "devices": devices,
        "templates": templates,
        "results": results,
        "commands": commands,
        "portal_users": portal_users,
        "activities": activities
    })


@app.route("/admin/template/<int:template_id>/content")
def admin_get_template_content(template_id):
    user = get_current_portal_user()
    if not user:
        return jsonify({"detail": "Unauthorized"}), 401
    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT content FROM templates WHERE id = ?", (template_id,))
    row = cursor.fetchone()
    db.close()
    if row and row["content"]:
        return jsonify({"content": "Cache payload exists. Ready for on-demand download."})
    return jsonify({"content": "OMR Template layout payload is not permanently stored on the server due to user privacy. To access this payload, please request a download to pull it dynamically from the OMR client."})


@app.route("/admin/result/<int:result_id>/content")
def admin_get_result_content(result_id):
    user = get_current_portal_user()
    if not user:
        return jsonify({"detail": "Unauthorized"}), 401
    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT data FROM results WHERE id = ?", (result_id,))
    row = cursor.fetchone()
    db.close()
    if row and row["data"]:
        return jsonify({"data": "Cache payload exists. Ready for on-demand download."})
    return jsonify({"data": "Checked student grading results payload is not permanently stored on the server. To access this payload, please request a download to pull it dynamically from the OMR client."})


@app.route("/admin/template/<int:template_id>/download-status")
def admin_template_download_status(template_id):
    user, err = require_portal_auth()
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM templates WHERE id = ?", (template_id,))
    tpl = cursor.fetchone()
    if not tpl:
        db.close()
        return jsonify({"status": "error", "message": "Template not found."})

    if tpl["content"]:
        db.close()
        return jsonify({"status": "available", "url": f"/admin/template/{template_id}/download-file"})

    cursor.execute("SELECT * FROM requested_downloads WHERE type = 'template' AND target_id = ? ORDER BY id DESC LIMIT 1", (template_id,))
    req = cursor.fetchone()

    if req:
        if req["status"] == "uploaded":
            cursor.execute("UPDATE templates SET content = ? WHERE id = ?", (req["content"], template_id))
            db.commit()
            db.close()
            return jsonify({"status": "available", "url": f"/admin/template/{template_id}/download-file"})
        else:
            db.close()
            return jsonify({"status": "queued", "message": "File download requested previously. Still waiting for OMR client app to check-in and upload."})

    cursor.execute(
        "INSERT INTO requested_downloads (user_id, type, target_id, target_name, status, created_at, updated_at) VALUES (?, 'template', ?, ?, 'pending', ?, ?)",
        (tpl["user_id"], template_id, tpl["name"], datetime.now().isoformat(), datetime.now().isoformat())
    )
    db.commit()
    db.close()
    return jsonify({"status": "queued", "message": "File not in server cache. Queued an on-demand retrieval command for the OMR client app. Try again after the client checks in (up to 30s)."})


@app.route("/admin/template/<int:template_id>/download-file")
def admin_template_download_file(template_id):
    user, err = require_portal_auth()
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM templates WHERE id = ?", (template_id,))
    tpl = cursor.fetchone()
    db.close()

    if tpl and tpl["content"]:
        return send_file(
            io.BytesIO(tpl["content"].encode()),
            mimetype="application/octet-stream",
            as_attachment=True,
            download_name=f"{tpl['name']}.homr"
        )
    return jsonify({"detail": "File content not uploaded yet."}), 404


@app.route("/admin/result/<int:result_id>/download-status")
def admin_result_download_status(result_id):
    user, err = require_portal_auth()
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM results WHERE id = ?", (result_id,))
    res = cursor.fetchone()
    if not res:
        db.close()
        return jsonify({"status": "error", "message": "Result record not found."})

    if res["data"]:
        db.close()
        return jsonify({"status": "available", "url": f"/admin/result/{result_id}/download-file"})

    cursor.execute("SELECT * FROM requested_downloads WHERE type = 'result' AND target_id = ? ORDER BY id DESC LIMIT 1", (result_id,))
    req = cursor.fetchone()

    if req:
        if req["status"] == "uploaded":
            cursor.execute("UPDATE results SET data = ? WHERE id = ?", (req["content"], result_id))
            db.commit()
            db.close()
            return jsonify({"status": "available", "url": f"/admin/result/{result_id}/download-file"})
        else:
            db.close()
            return jsonify({"status": "queued", "message": "File download requested previously. Still waiting for OMR client app to check-in and upload."})

    cursor.execute(
        "INSERT INTO requested_downloads (user_id, type, target_id, target_name, status, created_at, updated_at) VALUES (?, 'result', ?, ?, 'pending', ?, ?)",
        (res["user_id"], result_id, res["template_name"], datetime.now().isoformat(), datetime.now().isoformat())
    )
    db.commit()
    db.close()
    return jsonify({"status": "queued", "message": "Grades bundle not in server cache. Queued an on-demand retrieval command for the OMR client app. Try again after the client checks in (up to 30s)."})


@app.route("/admin/result/<int:result_id>/download-file")
def admin_result_download_file(result_id):
    user, err = require_portal_auth()
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM results WHERE id = ?", (result_id,))
    res = cursor.fetchone()
    db.close()

    if res and res["data"]:
        return send_file(
            io.BytesIO(res["data"].encode()),
            mimetype="application/octet-stream",
            as_attachment=True,
            download_name=f"{res['template_name']}_results_backup.json"
        )
    return jsonify({"detail": "File content not uploaded yet."}), 404


# ==================== DIAGNOSTICS DEMAND ====================

@app.route("/admin/device/<int:device_id>/demand-info", methods=["POST"])
def admin_demand_info(device_id):
    user, err = require_portal_auth(roles=["admin", "staff"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT id FROM devices WHERE id = ?", (device_id,))
    if not cursor.fetchone():
        db.close()
        return jsonify({"status": "error", "message": "Device not found."})

    cursor.execute(
        "INSERT INTO demanded_data (device_id, demand_type, status, created_at, updated_at) VALUES (?, 'device_info', 'pending', ?, ?)",
        (device_id, datetime.now().isoformat(), datetime.now().isoformat())
    )
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Device specifications requested. Client will collect silently on checkin."})


@app.route("/admin/device/<int:device_id>/demand-stats", methods=["POST"])
def admin_demand_stats(device_id):
    user, err = require_portal_auth(roles=["admin", "staff"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT id FROM devices WHERE id = ?", (device_id,))
    if not cursor.fetchone():
        db.close()
        return jsonify({"status": "error", "message": "Device not found."})

    cursor.execute(
        "INSERT INTO demanded_data (device_id, demand_type, status, created_at, updated_at) VALUES (?, 'usage_stats', 'pending', ?, ?)",
        (device_id, datetime.now().isoformat(), datetime.now().isoformat())
    )
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Detailed usage log backup requested. Client will compile silently on checkin."})


@app.route("/admin/device/<int:device_id>/demanded-data/<demand_type>")
def admin_get_demanded_data(device_id, demand_type):
    user, err = require_portal_auth()
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute(
        "SELECT * FROM demanded_data WHERE device_id = ? AND demand_type = ? ORDER BY id DESC LIMIT 1",
        (device_id, demand_type)
    )
    row = cursor.fetchone()
    db.close()

    if row:
        if row["status"] == "received":
            return jsonify({"status": "available", "payload": row["payload"], "updated_at": row["updated_at"]})
        return jsonify({"status": "queued", "message": "Diagnostics queued. Awaiting client connection check-in."})
    return jsonify({"status": "empty", "message": "No specs/logs diagnostics requested yet."})


# ==================== ADMIN ACTION ROUTES ====================

@app.route("/admin/user/create", methods=["POST"])
def admin_create_user():
    user, err = require_portal_auth(roles=["admin", "staff"])
    if err:
        return err

    email = request.form.get("email")
    password = request.form.get("password")
    max_devices = int(request.form.get("max_devices", 3))

    db = get_db()
    cursor = db.cursor()
    pwd_hash = hashlib.sha256(password.encode()).hexdigest()
    rand_part = secrets.token_hex(6).upper()
    activation_key = f"SARAL-{rand_part[:4]}-{rand_part[4:8]}-{rand_part[8:]}"

    try:
        cursor.execute(
            "INSERT INTO users (email, password_hash, license_status, max_devices, activation_key, created_at) VALUES (?, ?, ?, ?, ?, ?)",
            (email, pwd_hash, "active", max_devices, activation_key, datetime.now().isoformat())
        )
        db.commit()
        db.close()
        return jsonify({"status": "success", "message": f"Successfully generated license key: {activation_key}"})
    except sqlite3.IntegrityError:
        db.close()
        return jsonify({"status": "error", "message": "An account with this email already exists."})


@app.route("/admin/user/<int:user_id>/toggle-ban", methods=["POST"])
def admin_toggle_ban(user_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT license_status FROM users WHERE id = ?", (user_id,))
    row = cursor.fetchone()

    if row:
        new_status = "active" if row["license_status"] == "banned" else "banned"
        cursor.execute("UPDATE users SET license_status = ? WHERE id = ?", (new_status, user_id))
        if new_status == "banned":
            cursor.execute("SELECT id FROM devices WHERE user_id = ?", (user_id,))
            for dev in cursor.fetchall():
                cursor.execute(
                    "INSERT INTO commands (device_id, command_type, payload, executed, created_at) VALUES (?, 'ban', '', 0, ?)",
                    (dev["id"], datetime.now().isoformat())
                )
        db.commit()
        db.close()
        return jsonify({"status": "success", "new_status": new_status, "message": f"License account status updated to: {new_status.upper()}"})

    db.close()
    return jsonify({"status": "error", "message": "User account not found."})


@app.route("/admin/user/<int:user_id>/toggle-license", methods=["POST"])
def admin_toggle_license(user_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT license_status FROM users WHERE id = ?", (user_id,))
    row = cursor.fetchone()
    if row:
        new_status = "active" if row["license_status"] == "revoked" else "revoked"
        cursor.execute("UPDATE users SET license_status = ? WHERE id = ?", (new_status, user_id))
        db.commit()
        db.close()
        return jsonify({"status": "success", "new_status": new_status, "message": f"License state modified to: {new_status.upper()}"})

    db.close()
    return jsonify({"status": "error", "message": "User not found."})


@app.route("/admin/user/<int:user_id>/update-devices", methods=["POST"])
def admin_update_devices(user_id):
    user, err = require_portal_auth(roles=["admin", "staff"])
    if err:
        return err

    max_devices = int(request.form.get("max_devices", 3))
    db = get_db()
    cursor = db.cursor()
    cursor.execute("UPDATE users SET max_devices = ? WHERE id = ?", (max_devices, user_id))
    db.commit()
    db.close()
    return jsonify({"status": "success", "max_devices": max_devices, "message": f"Updated maximum hardware devices allowed: {max_devices}"})


@app.route("/admin/device/<int:device_id>/toggle-block", methods=["POST"])
def admin_toggle_device_block(device_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT is_blocked FROM devices WHERE id = ?", (device_id,))
    dev = cursor.fetchone()
    if dev:
        new_val = 0 if dev["is_blocked"] == 1 else 1
        cursor.execute("UPDATE devices SET is_blocked = ? WHERE id = ?", (new_val, device_id))
        db.commit()
        db.close()
        msg = "Device block removed successfully." if new_val == 0 else "Device hardware blocked from checking in."
        return jsonify({"status": "success", "new_status": new_val, "message": msg})

    db.close()
    return jsonify({"status": "error", "message": "Device not found."})


@app.route("/admin/device/<int:device_id>/force-logout", methods=["POST"])
def admin_force_logout(device_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO commands (device_id, command_type, payload, executed, created_at) VALUES (?, 'force_logout', '', 0, ?)",
        (device_id, datetime.now().isoformat())
    )
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Forced session logout command sent successfully."})


@app.route("/admin/device/<int:device_id>/kill", methods=["POST"])
def admin_kill_switch(device_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO commands (device_id, command_type, payload, executed, created_at) VALUES (?, 'kill_switch', '', 0, ?)",
        (device_id, datetime.now().isoformat())
    )
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Critical Kill Switch signal dispatched to device registry."})


@app.route("/admin/device/<int:device_id>/delete", methods=["POST"])
def admin_delete_device(device_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("DELETE FROM commands WHERE device_id = ?", (device_id,))
    cursor.execute("DELETE FROM devices WHERE id = ?", (device_id,))
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Hardware registration record cleared."})


@app.route("/admin/commands/clear-pending", methods=["POST"])
def admin_clear_pending_commands():
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    db = get_db()
    cursor = db.cursor()
    cursor.execute("DELETE FROM commands WHERE executed = 0")
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Cleared all pending execution command queues."})


@app.route("/admin/user/<int:user_id>/message", methods=["POST"])
def admin_send_message(user_id):
    user, err = require_portal_auth(roles=["admin", "staff"])
    if err:
        return err

    title = request.form.get("title")
    message = request.form.get("message")
    urgent = request.form.get("urgent")

    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT id FROM devices WHERE user_id = ?", (user_id,))
    devices = cursor.fetchall()

    urg_flag = "1" if urgent == "1" else "0"
    payload_str = f"{title}||{message}||{urg_flag}"

    for dev in devices:
        cursor.execute(
            "INSERT INTO commands (device_id, command_type, payload, executed, created_at) VALUES (?, 'custom_message', ?, 0, ?)",
            (dev["id"], payload_str, datetime.now().isoformat())
        )

    db.commit()
    db.close()
    return jsonify({"status": "success", "message": f"Broadcast notification queued for {len(devices)} device(s)."})


# ==================== PORTAL STAFF MANAGEMENT ====================

@app.route("/admin/portal-user/create", methods=["POST"])
def portal_create_user():
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    username = request.form.get("username")
    email = request.form.get("email")
    password = request.form.get("password")
    role = request.form.get("role")

    db = get_db()
    cursor = db.cursor()
    pwd_hash = hashlib.sha256(password.encode()).hexdigest()

    try:
        cursor.execute(
            "INSERT INTO portal_users (username, email, password_hash, role, created_at) VALUES (?, ?, ?, ?, ?)",
            (username, email, pwd_hash, role, datetime.now().isoformat())
        )
        db.commit()
        db.close()
        return jsonify({"status": "success", "message": f"Successfully created staff account: {username}"})
    except sqlite3.IntegrityError:
        db.close()
        return jsonify({"status": "error", "message": "Username or email is already in use by another staff member."})


@app.route("/admin/portal-user/<int:port_user_id>/update-role", methods=["POST"])
def portal_update_role(port_user_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    if port_user_id == user["id"]:
        return jsonify({"status": "error", "message": "Self-modifying own admin role is locked."}), 400

    role = request.form.get("role")
    db = get_db()
    cursor = db.cursor()
    cursor.execute("UPDATE portal_users SET role = ? WHERE id = ?", (role, port_user_id))
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Role updated successfully."})


@app.route("/admin/portal-user/<int:port_user_id>/delete", methods=["POST"])
def portal_delete_user(port_user_id):
    user, err = require_portal_auth(roles=["admin"])
    if err:
        return err

    if port_user_id == user["id"]:
        return jsonify({"status": "error", "message": "Cannot delete your own admin account."}), 400

    db = get_db()
    cursor = db.cursor()
    cursor.execute("DELETE FROM portal_sessions WHERE portal_user_id = ?", (port_user_id,))
    cursor.execute("DELETE FROM portal_users WHERE id = ?", (port_user_id,))
    db.commit()
    db.close()
    return jsonify({"status": "success", "message": "Portal staff account deleted."})


if __name__ == "__main__":
    app.run(host="127.0.0.1", port=8000, debug=True)