source: server.py@ 505f39a

#!/usr/bin/env python3
"""
netIntel Flask Server (multi-tenant + Google login + JWT cookie session + env tokens)

Auth:
- POST /api/auth/google  -> sets HttpOnly cookie "session"
- GET  /api/me           -> returns current user claims
- POST /api/auth/logout  -> clears cookie

Tenant admin (JWT cookie; admin role):
- GET/POST /api/admin/environments
- POST     /api/admin/tokens

Agent (X-Env-Token):
- POST /receive

Tenant-scoped dashboard (JWT cookie):
- GET  /api/stats
- GET  /api/computers
- GET  /api/computer/<name>
- GET  /api/export/<name>
- POST /api/chat
"""

import os
import json
import time
import sqlite3
import traceback
import threading
import secrets
from datetime import datetime, timedelta
from functools import wraps

from flask import Flask, request, jsonify, Response
from dotenv import load_dotenv

import jwt
from google.oauth2 import id_token
from google.auth.transport import requests as grequests

# Optional OpenAI
try:
    from openai import OpenAI
except Exception:
    OpenAI = None

from flask_cors import CORS

load_dotenv()

# ----------------------------
# Config
# ----------------------------
DB_FILE = os.environ.get("DB_FILE", "lan_logs_sysmon.db")
LOG_DIR = os.environ.get("LOG_DIR", "received_data_sysmon")

OPENAI_API_KEY = os.environ.get("OPENAI_API_KEY", "")
GOOGLE_CLIENT_ID = os.environ.get("GOOGLE_CLIENT_ID", "")
JWT_SECRET = os.environ.get("JWT_SECRET", "dev-change-me")
JWT_ISSUER = os.environ.get("JWT_ISSUER", "netintel")
COOKIE_SECURE = os.environ.get("COOKIE_SECURE", "0") == "1"  # set 1 only on HTTPS

# CORS origins (add your LAN origins if needed)
EXTRA_ORIGINS = [o.strip() for o in os.environ.get("CORS_ORIGINS", "").split(",") if o.strip()]
DEFAULT_ORIGINS = [
    "http://localhost:5173",
    "http://127.0.0.1:5173",
]
ALLOWED_ORIGINS = list(dict.fromkeys(DEFAULT_ORIGINS + EXTRA_ORIGINS))

client = OpenAI(api_key=OPENAI_API_KEY, timeout=15) if (OPENAI_API_KEY and OpenAI) else None

# ----------------------------
# App
# ----------------------------
app = Flask(__name__)
os.makedirs(LOG_DIR, exist_ok=True)

CORS(
    app,
    supports_credentials=True,
    origins=ALLOWED_ORIGINS,
    allow_headers=[
        "Content-Type",
        "X-Admin-Session",
        "X-Env",
        "X-Env-Token",
    ],
    methods=["GET", "POST", "OPTIONS"],
)


# ----------------------------
# DB helpers
# ----------------------------
def db():
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """Create tables if missing + light migrations."""
    conn = db()
    c = conn.cursor()

    c.execute("PRAGMA foreign_keys = ON;")

    # Tenants / users / memberships
    c.execute("""
        CREATE TABLE IF NOT EXISTS tenants(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            owner_email TEXT NOT NULL,
            created_at TEXT
        )
        """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS users(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            name TEXT,
            picture TEXT,
            created_at TEXT
        )
        """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS memberships(
            user_id INTEGER NOT NULL,
            tenant_id INTEGER NOT NULL,
            role TEXT DEFAULT 'admin',
            created_at TEXT,
            PRIMARY KEY(user_id, tenant_id),
            FOREIGN KEY(user_id) REFERENCES users(id),
            FOREIGN KEY(tenant_id) REFERENCES tenants(id)
        )
        """)

    # Environments (unique per tenant)
    c.execute("""
        CREATE TABLE IF NOT EXISTS environments(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tenant_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            created_at TEXT,
            UNIQUE(tenant_id, name),
            FOREIGN KEY(tenant_id) REFERENCES tenants(id)
        )
        """)

    # Env tokens (agent tokens)
    c.execute("""
        CREATE TABLE IF NOT EXISTS env_tokens(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tenant_id INTEGER NOT NULL,
            env_name TEXT NOT NULL,
            token TEXT UNIQUE NOT NULL,
            created_at TEXT,
            expires_at TEXT,
            FOREIGN KEY(tenant_id) REFERENCES tenants(id)
        )
        """)

    # Computers
    c.execute("""
        CREATE TABLE IF NOT EXISTS computers(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tenant_id INTEGER NOT NULL,
            env_name TEXT DEFAULT 'default',
            name TEXT NOT NULL,
            user TEXT,
            ip TEXT,
            os TEXT,
            first_seen TEXT,
            last_seen TEXT,
            sysmon_available INTEGER DEFAULT 0,
            UNIQUE(tenant_id, name)
        )
        """)

    # History
    c.execute("""
        CREATE TABLE IF NOT EXISTS computer_history(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER,
            cpu_usage REAL,
            ram_usage REAL,
            disk_usage REAL,
            network_sent_mb REAL,
            network_recv_mb REAL,
            timestamp TEXT,
            FOREIGN KEY(computer_id) REFERENCES computers(id)
        )
        """)

    # Processes
    c.execute("""
        CREATE TABLE IF NOT EXISTS computer_processes(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER,
            pid INTEGER,
            name TEXT,
            cpu_percent REAL,
            memory_mb REAL,
            username TEXT,
            cmdline TEXT,
            timestamp TEXT,
            FOREIGN KEY(computer_id) REFERENCES computers(id)
        )
        """)

    # Sysmon events
    c.execute("""
        CREATE TABLE IF NOT EXISTS sysmon_events(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER,
            event_id INTEGER,
            event_type TEXT,
            message TEXT,
            timestamp TEXT,
            details TEXT,
            FOREIGN KEY(computer_id) REFERENCES computers(id)
        )
        """)

    # Network connections
    c.execute("""
        CREATE TABLE IF NOT EXISTS network_connections(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER,
            pid INTEGER,
            local_address TEXT,
            remote_address TEXT,
            status TEXT,
            process_name TEXT,
            timestamp TEXT,
            FOREIGN KEY(computer_id) REFERENCES computers(id)
        )
        """)

    # Security alerts
    c.execute("""
        CREATE TABLE IF NOT EXISTS security_alerts(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER,
            alert_type TEXT,
            severity TEXT,
            description TEXT,
            timestamp TEXT,
            resolved INTEGER DEFAULT 0,
            FOREIGN KEY(computer_id) REFERENCES computers(id)
        )
        """)

    conn.commit()
    conn.close()
    print(f"✅ DB ready: {DB_FILE}")


# ----------------------------
# JWT helpers
# ----------------------------
def make_jwt(payload: dict, minutes=60 * 24):
    exp = datetime.utcnow() + timedelta(minutes=minutes)
    data = {**payload, "iss": JWT_ISSUER, "exp": exp}
    return jwt.encode(data, JWT_SECRET, algorithm="HS256")


def read_jwt(token: str):
    return jwt.decode(token, JWT_SECRET, algorithms=["HS256"], issuer=JWT_ISSUER)


def require_user():
    def deco(fn):
        @wraps(fn)
        def wrap(*args, **kwargs):
            tok = request.cookies.get("session") or ""
            if not tok:
                return jsonify({"error": "Unauthorized"}), 401
            try:
                claims = read_jwt(tok)
            except Exception:
                return jsonify({"error": "Unauthorized"}), 401
            request.user = claims
            return fn(*args, **kwargs)

        return wrap

    return deco


def require_tenant_admin():
    def deco(fn):
        @wraps(fn)
        def wrap(*args, **kwargs):
            tok = request.cookies.get("session") or ""
            if not tok:
                return jsonify({"error": "Unauthorized"}), 401
            try:
                claims = read_jwt(tok)
            except Exception:
                return jsonify({"error": "Unauthorized"}), 401
            if claims.get("role") != "admin":
                return jsonify({"error": "Forbidden"}), 403
            request.user = claims
            return fn(*args, **kwargs)

        return wrap

    return deco


# ----------------------------
# Env-token helpers (agents)
# ----------------------------
def get_env_from_token(req):
    tok = req.headers.get("X-Env-Token", "")
    if not tok:
        return (None, None)

    conn = db()
    c = conn.cursor()
    c.execute("""
            SELECT env_name, tenant_id
            FROM env_tokens
            WHERE token = ?
              AND (expires_at IS NULL OR expires_at > datetime('now'))
            LIMIT 1
        """, (tok,))
    row = c.fetchone()
    conn.close()

    if not row:
        return (None, None)
    return (row["env_name"], row["tenant_id"])


# ----------------------------
# Utility
# ----------------------------
def save_json_file(data):
    info = data.get("info") or {}
    computer_name = info.get("computer_name", "unknown")
    date_str = datetime.now().strftime("%Y%m%d_%H")

    computer_dir = os.path.join(LOG_DIR, computer_name)
    os.makedirs(computer_dir, exist_ok=True)
    filepath = os.path.join(computer_dir, f"{date_str}.json")

    if os.path.exists(filepath):
        try:
            with open(filepath, "r", encoding="utf-8") as f:
                existing_data = json.load(f)
        except Exception:
            existing_data = []
    else:
        existing_data = []

    existing_data.append({
        "timestamp": info.get("timestamp"),
        "info": info,
        "processes_count": len(data.get("processes", [])),
        "sysmon_events_count": len((data.get("security_data") or {}).get("sysmon_events", [])),
    })

    with open(filepath, "w", encoding="utf-8") as f:
        json.dump(existing_data, f, indent=2, ensure_ascii=False)


def cleanup_old_data():
    """Deletes old rows (30 days) every hour."""
    while True:
        time.sleep(3600)
        try:
            conn = db()
            c = conn.cursor()
            cutoff = (datetime.now() - timedelta(days=30)).isoformat()

            c.execute("DELETE FROM computer_history WHERE timestamp < ?", (cutoff,))
            c.execute("DELETE FROM computer_processes WHERE timestamp < ?", (cutoff,))
            c.execute("DELETE FROM sysmon_events WHERE timestamp < ?", (cutoff,))
            c.execute("DELETE FROM network_connections WHERE timestamp < ?", (cutoff,))

            conn.commit()
            conn.close()
            print(f"[Cleanup] Deleted entries older than {cutoff}")
        except Exception as e:
            print("[Cleanup] Error:", e)


# ----------------------------
# Auth endpoints
# ----------------------------
@app.route("/api/auth/google", methods=["POST"])
def auth_google():
    data = request.get_json(force=True, silent=True) or {}
    credential = (data.get("credential") or "").strip()
    if not credential:
        return jsonify({"error": "Missing credential"}), 400
    if not GOOGLE_CLIENT_ID:
        return jsonify({"error": "Server missing GOOGLE_CLIENT_ID"}), 500

    try:
        idinfo = id_token.verify_oauth2_token(
            credential,
            grequests.Request(),
            GOOGLE_CLIENT_ID,
        )
        email = idinfo.get("email")
        name = idinfo.get("name") or ""
        picture = idinfo.get("picture") or ""

        if not email:
            return jsonify({"error": "No email in token"}), 400

        conn = db()
        c = conn.cursor()

        # upsert user
        c.execute("SELECT id FROM users WHERE email=?", (email,))
        row = c.fetchone()
        if row:
            user_id = row["id"]
            c.execute("UPDATE users SET name=?, picture=? WHERE id=?", (name, picture, user_id))
        else:
            c.execute(
                "INSERT INTO users(email, name, picture, created_at) VALUES(?,?,?, datetime('now'))",
                (email, name, picture),
            )
            user_id = c.lastrowid

        # membership -> tenant (create tenant if first time)
        c.execute("SELECT tenant_id, role FROM memberships WHERE user_id=? LIMIT 1", (user_id,))
        m = c.fetchone()

        if not m:
            tenant_name = email.split("@")[0]
            c.execute(
                "INSERT INTO tenants(name, owner_email, created_at) VALUES(?,?, datetime('now'))",
                (tenant_name, email),
            )
            tenant_id = c.lastrowid
            role = "admin"

            c.execute(
                "INSERT INTO memberships(user_id, tenant_id, role, created_at) VALUES(?,?, 'admin', datetime('now'))",
                (user_id, tenant_id),
            )

            # default env for this tenant
            c.execute(
                "INSERT OR IGNORE INTO environments(tenant_id, name, created_at) VALUES(?, 'default', datetime('now'))",
                (tenant_id,),
            )
        else:
            tenant_id = m["tenant_id"]
            role = m["role"] or "admin"

        conn.commit()
        conn.close()

        session = make_jwt(
            {
                "uid": user_id,
                "email": email,
                "name": name,
                "role": role,
                "tenant_id": tenant_id,
            },
            minutes=60 * 24,
        )

        resp = jsonify({"ok": True, "user": {"email": email, "name": name, "role": role, "tenant_id": tenant_id}})
        resp.set_cookie(
            "session",
            session,
            httponly=True,
            samesite="Lax",
            secure=COOKIE_SECURE,
            max_age=60 * 60 * 24,
        )
        return resp

    except Exception as e:
        return jsonify({"error": "Invalid Google token", "details": str(e)}), 401


@app.route("/api/me", methods=["GET"])
@require_user()
def api_me():
    return jsonify({"user": request.user})


@app.route("/api/auth/logout", methods=["POST"])
def auth_logout():
    resp = jsonify({"ok": True})
    resp.set_cookie("session", "", expires=0)
    return resp


# ----------------------------
# Admin endpoints
# ----------------------------
@app.route("/api/admin/environments", methods=["GET"])
@require_tenant_admin()
def admin_list_envs():
    tenant_id = request.user["tenant_id"]
    conn = db()
    c = conn.cursor()
    c.execute("SELECT name FROM environments WHERE tenant_id=? ORDER BY name", (tenant_id,))
    envs = [r["name"] for r in c.fetchall()]
    conn.close()
    if not envs:
        envs = ["default"]
    return jsonify({"environments": envs})


@app.route("/api/admin/environments", methods=["POST"])
@require_tenant_admin()
def admin_create_env():
    tenant_id = request.user["tenant_id"]
    data = request.get_json(force=True, silent=True) or {}
    name = (data.get("name") or "").strip()
    if not name:
        return jsonify({"error": "Missing env name"}), 400

    conn = db()
    c = conn.cursor()
    try:
        c.execute(
            "INSERT INTO environments(tenant_id, name, created_at) VALUES(?, ?, datetime('now'))",
            (tenant_id, name),
        )
        conn.commit()
    except Exception as e:
        conn.close()
        return jsonify({"error": str(e)}), 400
    conn.close()
    return jsonify({"ok": True, "name": name})


@app.route("/api/admin/tokens", methods=["POST"])
@require_tenant_admin()
def admin_generate_token():
    tenant_id = request.user["tenant_id"]
    data = request.get_json(force=True, silent=True) or {}
    env = (data.get("env") or "").strip()
    if not env:
        return jsonify({"error": "Missing env"}), 400

    conn = db()
    c = conn.cursor()
    c.execute("SELECT 1 FROM environments WHERE tenant_id=? AND name=? LIMIT 1", (tenant_id, env))
    if not c.fetchone():
        conn.close()
        return jsonify({"error": "Environment not found"}), 404

    token = secrets.token_urlsafe(32)
    c.execute("""
            INSERT INTO env_tokens(tenant_id, env_name, token, created_at, expires_at)
            VALUES(?, ?, ?, datetime('now'), datetime('now', '+90 days'))
        """, (tenant_id, env, token))
    conn.commit()
    conn.close()

    return jsonify({"ok": True, "env": env, "token": token})


# ----------------------------
# Agent endpoint
# ----------------------------
@app.route("/receive", methods=["POST"])
def receive_data():
    try:
        data = request.get_json(force=True, silent=True) or {}
        if not data:
            return jsonify({"error": "No data"}), 400

        env_name, tenant_id = get_env_from_token(request)
        if not env_name or not tenant_id:
            return jsonify({"error": "Missing or invalid X-Env-Token"}), 401

        info = data.get("info") or {}
        computer_name = info.get("computer_name")
        if not computer_name:
            return jsonify({"error": "Missing info.computer_name"}), 400

        now_iso = datetime.now().isoformat()
        security_data = data.get("security_data") or {}

        conn = db()
        c = conn.cursor()

        # Find by (tenant_id + name)
        c.execute("SELECT id FROM computers WHERE tenant_id=? AND name=? LIMIT 1", (tenant_id, computer_name))
        row = c.fetchone()

        if row:
            computer_id = row["id"]
            c.execute("""
                    UPDATE computers
                    SET user=?, ip=?, os=?, last_seen=?, sysmon_available=?, env_name=?
                    WHERE id=? AND tenant_id=?
                """, (
                info.get("user"),
                info.get("ip_address"),
                info.get("os"),
                now_iso,
                int(info.get("is_sysmon_available", 0) or 0),
                env_name,
                computer_id,
                tenant_id,
            ))
        else:
            c.execute("""
                    INSERT INTO computers(tenant_id, env_name, name, user, ip, os, first_seen, last_seen, sysmon_available)
                    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                tenant_id,
                env_name,
                computer_name,
                info.get("user"),
                info.get("ip_address"),
                info.get("os"),
                now_iso,
                now_iso,
                int(info.get("is_sysmon_available", 0) or 0),
            ))
            computer_id = c.lastrowid

        # history row
        c.execute("""
                INSERT INTO computer_history(computer_id, cpu_usage, ram_usage, disk_usage,
                                             network_sent_mb, network_recv_mb, timestamp)
                VALUES(?, ?, ?, ?, ?, ?, ?)
            """, (
            computer_id,
            float(info.get("cpu_usage") or 0),
            float(info.get("ram_usage") or 0),
            float(info.get("disk_usage") or 0),
            float(info.get("network_sent_mb") or 0),
            float(info.get("network_recv_mb") or 0),
            info.get("timestamp") or now_iso,
        ))

        # processes
        for proc in (data.get("processes") or []):
            c.execute("""
                    INSERT INTO computer_processes(computer_id, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
                    VALUES(?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                computer_id,
                proc.get("pid"),
                proc.get("name"),
                float(proc.get("cpu_percent") or 0),
                float(proc.get("memory_mb") or 0),
                proc.get("user") or proc.get("username"),
                proc.get("cmdline"),
                info.get("timestamp") or now_iso,
            ))

        # sysmon events
        sysmon_count = 0
        for ev in (security_data.get("sysmon_events") or []):
            c.execute("""
                    INSERT INTO sysmon_events(computer_id, event_id, event_type, message, timestamp, details)
                    VALUES(?, ?, ?, ?, ?, ?)
                """, (
                computer_id,
                ev.get("event_id"),
                ev.get("event_type", "Unknown"),
                ev.get("message", ""),
                ev.get("timestamp") or (info.get("timestamp") or now_iso),
                json.dumps(ev, ensure_ascii=False),
            ))
            sysmon_count += 1

        # network connections
        for nc in (security_data.get("network_connections") or []):
            c.execute("""
                    INSERT INTO network_connections(computer_id, pid, local_address, remote_address, status, process_name, timestamp)
                    VALUES(?, ?, ?, ?, ?, ?, ?)
                """, (
                computer_id,
                nc.get("pid"),
                nc.get("local_address"),
                nc.get("remote_address"),
                nc.get("status"),
                nc.get("process_name"),
                info.get("timestamp") or now_iso,
            ))

        conn.commit()
        conn.close()

        # optional JSON archive
        try:
            save_json_file(data)
        except Exception:
            pass

        return jsonify({
            "ok": True,
            "computer_id": computer_id,
            "env": env_name,
            "tenant_id": tenant_id,
            "sysmon_events_saved": sysmon_count,
            "server_time": now_iso,
        })

    except Exception as e:
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


# ----------------------------
# Dashboard API (tenant scoped)
# ----------------------------
@app.route("/api/computers", methods=["GET"])
@require_user()
def api_computers():
    tenant_id = request.user["tenant_id"]
    env = request.headers.get("X-Env", "default")

    conn = db()
    c = conn.cursor()
    c.execute("""
            SELECT id, name, user, ip, os, first_seen, last_seen, sysmon_available
            FROM computers
            WHERE tenant_id=? AND env_name=?
            ORDER BY last_seen DESC
        """, (tenant_id, env))
    rows = c.fetchall()

    computers = []
    for r in rows:
        cid = r["id"]

        c.execute("""
                SELECT COUNT(*) AS n
                FROM computer_history
                WHERE computer_id=? AND timestamp > datetime('now','-24 hours')
            """, (cid,))
        recent_logs = int(c.fetchone()["n"] or 0)

        c.execute("""
                SELECT COUNT(*) AS n
                FROM sysmon_events
                WHERE computer_id=? AND timestamp > datetime('now','-24 hours')
            """, (cid,))
        recent_sysmon = int(c.fetchone()["n"] or 0)

        c.execute("""
                SELECT cpu_usage, ram_usage, timestamp
                FROM computer_history
                WHERE computer_id=?
                ORDER BY timestamp DESC
                LIMIT 5
            """, (cid,))
        metrics = c.fetchall()
        avg_cpu = round(sum(m["cpu_usage"] for m in metrics) / len(metrics), 1) if metrics else 0.0
        avg_ram = round(sum(m["ram_usage"] for m in metrics) / len(metrics), 1) if metrics else 0.0

        status = "offline"
        status_color = "gray"
        try:
            last_seen = r["last_seen"]
            if last_seen:
                dt = datetime.fromisoformat(
                    last_seen.replace("Z", "+00:00")) if "T" in last_seen else datetime.fromisoformat(last_seen)
                diff = (datetime.now() - dt).total_seconds()
                if diff < 60:
                    status, status_color = "online", "green"
                elif diff < 300:
                    status, status_color = "idle", "orange"
        except Exception:
            pass

        computers.append({
            "name": r["name"],
            "user": r["user"],
            "ip": r["ip"],
            "os": r["os"],
            "first_seen": r["first_seen"],
            "last_seen": r["last_seen"],
            "sysmon_available": bool(r["sysmon_available"]),
            "recent_logs": recent_logs,
            "recent_sysmon": recent_sysmon,
            "avg_cpu": avg_cpu,
            "avg_ram": avg_ram,
            "status": status,
            "status_color": status_color,
        })

    conn.close()
    return jsonify(computers)


@app.route("/api/stats", methods=["GET"])
@require_user()
def api_stats():
    tenant_id = request.user["tenant_id"]

    conn = db()
    c = conn.cursor()

    c.execute("""
            SELECT COUNT(*) AS n
            FROM sysmon_events s
            JOIN computers c2 ON c2.id = s.computer_id
            WHERE s.timestamp > datetime('now','-24 hours')
              AND c2.tenant_id = ?
        """, (tenant_id,))
    total_sysmon = int(c.fetchone()["n"] or 0)

    c.execute("""
            SELECT COUNT(*) AS n
            FROM network_connections n
            JOIN computers c2 ON c2.id = n.computer_id
            WHERE n.timestamp > datetime('now','-24 hours')
              AND c2.tenant_id = ?
        """, (tenant_id,))
    total_net = int(c.fetchone()["n"] or 0)

    conn.close()
    return jsonify({
        "total_sysmon_events": total_sysmon,
        "total_network_connections": total_net,
        "server_time": datetime.now().isoformat(),
    })


@app.route("/api/computer/<computer_name>", methods=["GET"])
@require_user()
def api_computer_details(computer_name):
    tenant_id = request.user["tenant_id"]

    conn = db()
    c = conn.cursor()
    c.execute("""
            SELECT id, name, user, ip, os, first_seen, last_seen, env_name
            FROM computers
            WHERE tenant_id=? AND name=?
            LIMIT 1
        """, (tenant_id, computer_name))
    comp = c.fetchone()
    if not comp:
        conn.close()
        return jsonify({"error": "Computer not found"}), 404

    computer_id = comp["id"]

    c.execute("SELECT COUNT(*) AS n FROM computer_history WHERE computer_id=?", (computer_id,))
    total_logs = int(c.fetchone()["n"] or 0)

    c.execute("""
            SELECT cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp
            FROM computer_history
            WHERE computer_id=?
            ORDER BY timestamp DESC
            LIMIT 100
        """, (computer_id,))
    history = [dict(r) for r in c.fetchall()]
    current_metrics = history[0] if history else {}

    c.execute("""
            SELECT pid, name, username, cpu_percent, memory_mb, cmdline, timestamp
            FROM computer_processes
            WHERE computer_id=?
            ORDER BY timestamp DESC
            LIMIT 100
        """, (computer_id,))
    processes = [dict(r) for r in c.fetchall()]

    c.execute("""
            SELECT event_id, event_type, message, timestamp, details
            FROM sysmon_events
            WHERE computer_id=?
            ORDER BY timestamp DESC
            LIMIT 200
        """, (computer_id,))
    sysmon = []
    for r in c.fetchall():
        d = {}
        try:
            d = json.loads(r["details"]) if r["details"] else {}
        except Exception:
            d = {}
        sysmon.append({
            "event_id": r["event_id"],
            "event_type": r["event_type"],
            "message": r["message"],
            "timestamp": r["timestamp"],
            "details": d,
        })

    c.execute("""
            SELECT pid, process_name, local_address, remote_address, status, timestamp
            FROM network_connections
            WHERE computer_id=?
            ORDER BY timestamp DESC
            LIMIT 100
        """, (computer_id,))
    net = [dict(r) for r in c.fetchall()]

    conn.close()

    return jsonify({
        "computer": {
            "id": comp["id"],
            "name": comp["name"],
            "user": comp["user"],
            "ip": comp["ip"],
            "os": comp["os"],
            "first_seen": comp["first_seen"],
            "last_seen": comp["last_seen"],
            "env_name": comp["env_name"],
            "total_logs": total_logs,
        },
        "history": history,
        "current_metrics": current_metrics,
        "recent_processes": processes,
        "sysmon_events": sysmon,
        "network_connections": net,
    })


@app.route("/api/export/<computer_name>", methods=["GET"])
@require_user()
def api_export(computer_name):
    tenant_id = request.user["tenant_id"]

    conn = db()
    c = conn.cursor()
    c.execute("""
            SELECT *
            FROM computers
            WHERE tenant_id=? AND name=?
            LIMIT 1
        """, (tenant_id, computer_name))
    comp = c.fetchone()
    if not comp:
        conn.close()
        return jsonify({"error": "Computer not found"}), 404

    computer_id = comp["id"]
    out = {"computer": dict(comp)}

    c.execute("SELECT * FROM computer_history WHERE computer_id=? ORDER BY timestamp", (computer_id,))
    out["history"] = [dict(r) for r in c.fetchall()]

    c.execute("SELECT * FROM sysmon_events WHERE computer_id=? ORDER BY timestamp", (computer_id,))
    out["sysmon_events"] = [dict(r) for r in c.fetchall()]

    c.execute("SELECT * FROM computer_processes WHERE computer_id=? ORDER BY timestamp", (computer_id,))
    out["processes"] = [dict(r) for r in c.fetchall()]

    c.execute("SELECT * FROM network_connections WHERE computer_id=? ORDER BY timestamp", (computer_id,))
    out["network_connections"] = [dict(r) for r in c.fetchall()]

    conn.close()

    return Response(
        json.dumps(out, indent=2, default=str, ensure_ascii=False),
        mimetype="application/json",
        headers={"Content-Disposition": f"attachment; filename={computer_name}_export.json"},
    )


# ----------------------------
# Simple RAG chat
# ----------------------------
def build_rag_context(tenant_id: int, question: str, computer_name=None, limit_per_table=10):
    conn = db()
    c = conn.cursor()

    params = [tenant_id]
    comp_clause = ""
    if computer_name:
        comp_clause = "AND c2.name = ?"
        params.append(computer_name)

    # sysmon
    c.execute(f"""
            SELECT s.timestamp, s.event_id, s.event_type, s.message
            FROM sysmon_events s
            JOIN computers c2 ON c2.id = s.computer_id
            WHERE c2.tenant_id = ? {comp_clause}
            ORDER BY s.timestamp DESC
            LIMIT ?
        """, (*params, limit_per_table))
    sysmon_rows = c.fetchall()

    # perf
    c.execute(f"""
            SELECT h.timestamp, h.cpu_usage, h.ram_usage, h.disk_usage, h.network_sent_mb, h.network_recv_mb
            FROM computer_history h
            JOIN computers c2 ON c2.id = h.computer_id
            WHERE c2.tenant_id = ? {comp_clause}
            ORDER BY h.timestamp DESC
            LIMIT ?
        """, (*params, limit_per_table))
    perf_rows = c.fetchall()

    # proc
    c.execute(f"""
            SELECT p.timestamp, p.pid, p.name, p.cpu_percent, p.memory_mb
            FROM computer_processes p
            JOIN computers c2 ON c2.id = p.computer_id
            WHERE c2.tenant_id = ? {comp_clause}
            ORDER BY p.timestamp DESC
            LIMIT ?
        """, (*params, limit_per_table))
    proc_rows = c.fetchall()

    conn.close()

    lines = []
    for r in sysmon_rows:
        lines.append(f"[SYSMON] {r['timestamp']} | Event {r['event_id']} ({r['event_type']}): {r['message']}")
    for r in perf_rows:
        lines.append(
            f"[PERF] {r['timestamp']} | CPU {r['cpu_usage']}% | RAM {r['ram_usage']}% | Disk {r['disk_usage']}% | "
            f"Net sent {r['network_sent_mb']}MB / recv {r['network_recv_mb']}MB"
        )
    for r in proc_rows:
        lines.append(
            f"[PROC] {r['timestamp']} | PID {r['pid']} | {r['name']} | CPU {r['cpu_percent']}% | RAM {r['memory_mb']}MB"
        )

    return "\n".join(lines)


def ask_llm_with_context(question, context):
    if not client:
        return "Немаш поставено OPENAI_API_KEY на серверот."

    system_msg = (
        "Ти си асистент за безбедност и систем мониторинг. "
        "Одговараш базирано ИСКЛУЧИВО на дадените логови. "
        "Ако нема доволно податоци, кажи дека нема доволно информации. "
        "Кога корисникот прашува за процеси и нивна потрошувачка, користи ги [PROC] редовите."
    )

    completion = client.chat.completions.create(
        model="gpt-4.1-mini",
        messages=[
            {"role": "system", "content": system_msg},
            {"role": "user", "content": f"Прашање: {question}\n\nЛогови:\n{context or 'Нема логови.'}"},
        ],
        temperature=0.2,
    )
    return completion.choices[0].message.content


@app.route("/api/chat", methods=["POST"])
@require_user()
def api_chat():
    try:
        tenant_id = request.user["tenant_id"]
        data = request.get_json(force=True, silent=True) or {}
        question = (data.get("question") or "").strip()
        computer_name = data.get("computer_name") or None

        if not question:
            return jsonify({"error": "No question provided"}), 400

        ctx = build_rag_context(tenant_id, question, computer_name=computer_name, limit_per_table=10)
        ans = ask_llm_with_context(question, ctx)
        return jsonify({"answer": ans or ""}), 200

    except Exception as e:
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


# ----------------------------
# Misc
# ----------------------------
@app.route("/ping")
def ping():
    return jsonify({
        "status": "alive",
        "server_time": datetime.now().isoformat(),
        "database": DB_FILE,
        "cors_origins": ALLOWED_ORIGINS,
    })


@app.route("/")
def root():
    return jsonify({
        "ok": True,
        "service": "netIntel server",
        "hint": "frontend should call /api/* endpoints",
    })


if __name__ == "__main__":
    init_db()

    cleanup_thread = threading.Thread(target=cleanup_old_data, daemon=True)
    cleanup_thread.start()

    print("🚀 netIntel server running on 0.0.0.0:5555")
    # debug=False recommended; if you need debug, set True temporarily
    app.run(host="0.0.0.0", port=5555, debug=False, threaded=True)