source: server.py@ a762898

#!/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
from dotenv import load_dotenv
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
load_dotenv(BASE_DIR / ".env")

print("GRAFANA_API_TOKEN =", os.environ.get("GRAFANA_API_TOKEN"))


# ----------------------------
# 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
GRAFANA_TOKEN = os.environ.get("GRAFANA_TOKEN", "")

# 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)
        )
        """)

    # Environment settings (switch-ови по env)
    c.execute("""
        CREATE TABLE IF NOT EXISTS env_settings(
            tenant_id INTEGER NOT NULL,
            env_name TEXT NOT NULL,
            save_process_history INTEGER DEFAULT 0,
            created_at TEXT,
            updated_at TEXT,
            PRIMARY KEY(tenant_id, env_name),
            FOREIGN KEY(tenant_id) REFERENCES tenants(id)
        )
    """)

    # Latest processes (only current snapshot)
    c.execute("""
        CREATE TABLE IF NOT EXISTS computer_processes_current(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER NOT NULL,
            pid INTEGER,
            name TEXT,
            cpu_percent REAL,
            memory_mb REAL,
            username TEXT,
            cmdline TEXT,
            timestamp TEXT,
            FOREIGN KEY(computer_id) REFERENCES computers(id)
        )
    """)

    # History processes (optional, controlled by switch)
    c.execute("""
        CREATE TABLE IF NOT EXISTS computer_processes_history(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            computer_id INTEGER NOT NULL,
            pid INTEGER,
            name TEXT,
            cpu_percent REAL,
            memory_mb REAL,
            username TEXT,
            cmdline TEXT,
            timestamp TEXT,
            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 is_process_history_enabled(tenant_id: int, env_name: str) -> bool:
    conn = db()
    c = conn.cursor()
    c.execute("""
        SELECT save_process_history
        FROM env_settings
        WHERE tenant_id=? AND env_name=?
        LIMIT 1
    """, (tenant_id, env_name))
    row = c.fetchone()
    conn.close()

    # default: OFF ако нема запис
    return bool(row["save_process_history"]) if row else False


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 sysmon_events WHERE timestamp < ?", (cutoff,))
            c.execute("DELETE FROM network_connections WHERE timestamp < ?", (cutoff,))
            c.execute("DELETE FROM computer_processes_history 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})

@app.route("/api/admin/env-settings/<env_name>", methods=["GET"])
@require_tenant_admin()
def admin_get_env_settings(env_name):
    tenant_id = request.user["tenant_id"]

    conn = db()
    c = conn.cursor()
    c.execute("""
        SELECT save_process_history
        FROM env_settings
        WHERE tenant_id=? AND env_name=?
        LIMIT 1
    """, (tenant_id, env_name))
    row = c.fetchone()
    conn.close()

    return jsonify({
        "env": env_name,
        "save_process_history": bool(row["save_process_history"]) if row else False
    })

@app.route("/api/admin/env-settings/<env_name>", methods=["POST"])
@require_tenant_admin()
def admin_set_env_settings(env_name):
    tenant_id = request.user["tenant_id"]
    data = request.get_json(force=True, silent=True) or {}
    enabled = 1 if bool(data.get("save_process_history")) else 0

    conn = db()
    c = conn.cursor()
    c.execute("""
        INSERT INTO env_settings(tenant_id, env_name, save_process_history, created_at, updated_at)
        VALUES(?, ?, ?, datetime('now'), datetime('now'))
        ON CONFLICT(tenant_id, env_name) DO UPDATE SET
            save_process_history=excluded.save_process_history,
            updated_at=datetime('now')
    """, (tenant_id, env_name, enabled))
    conn.commit()
    conn.close()

    return jsonify({"ok": True, "env": env_name, "save_process_history": bool(enabled)})

# ----------------------------
# 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: CURRENT (always)
        # ----------------------------
        # Clear old current snapshot for this computer
        c.execute("DELETE FROM computer_processes_current WHERE computer_id=?", (computer_id,))

        # Insert new snapshot
        for proc in (data.get("processes") or []):
            c.execute("""
                INSERT INTO computer_processes_current(
                    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,
            ))

        # ----------------------------
        # processes: HISTORY (optional)
        # ----------------------------
        if is_process_history_enabled(tenant_id, env_name):
            for proc in (data.get("processes") or []):
                c.execute("""
                    INSERT INTO computer_processes_history(
                        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,
            "process_history_enabled": bool(is_process_history_enabled(tenant_id, env_name)),
        })

    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_current
        WHERE computer_id=?
        ORDER BY timestamp DESC
        LIMIT 200
    """, (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_current WHERE computer_id=? ORDER BY timestamp", (computer_id,))
    out["processes_current"] = [dict(r) for r in c.fetchall()]

    c.execute("SELECT * FROM computer_processes_history WHERE computer_id=? ORDER BY timestamp", (computer_id,))
    out["processes_history"] = [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_current 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 на серверот."

    prompt = f"""Ти си асистент за безбедност и систем мониторинг.
Одговарај базирано ИСКЛУЧИВО на дадените логови.
Ако нема доволно податоци, кажи: 'Нема доволно информации во логовите.'.

Прашање: {question}

Логови:
{context or 'Нема логови.'}
"""

    resp = client.responses.create(
        model="gpt-4.1-mini",
        input=[{"role": "user", "content": [{"type": "input_text", "text": prompt}]}],
    )

    text = getattr(resp, "output_text", "") or ""
    return text.strip() or "Нема доволно информации во логовите."

def require_grafana():
    def deco(fn):
        @wraps(fn)
        def wrap(*args, **kwargs):
            tok = request.headers.get("X-Grafana-Token", "")
            if not GRAFANA_TOKEN or tok != GRAFANA_TOKEN:
                return jsonify({"error": "Unauthorized"}), 401
            return fn(*args, **kwargs)
        return wrap
    return deco
@app.route("/api/public/stats", methods=["GET"])
@require_grafana()
def grafana_stats():
    # ако сакаш само една околина:
    env = request.args.get("env", "default")

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

    # вкупно sysmon во 24h (за сите tenants, или ако сакаш само 1 tenant додади филтер)
    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.env_name = ?
    """, (env,))
    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.env_name = ?
    """, (env,))
    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(),
        "env": env,
    })

def require_grafana_token(fn):
    @wraps(fn)
    def wrapper(*args, **kwargs):
        expected = os.environ.get("GRAFANA_API_TOKEN") or ""

        # 1) пробај Authorization: Bearer <token>
        auth = request.headers.get("Authorization", "")
        token = ""
        if auth.startswith("Bearer "):
            token = auth.replace("Bearer ", "").strip()

        # 2) ако нема header, пробај query param ?token=
        if not token:
            token = (request.args.get("token") or "").strip()

        if not expected or token != expected:
            return jsonify({"error": "Invalid Grafana token"}), 401

        return fn(*args, **kwargs)
    return wrapper

@app.route("/api/public/ips", methods=["GET"])
@require_grafana_token
def api_public_ips():
    tenant_id = request.args.get("tenant_id", type=int)
    env = request.args.get("env", "default")
    hours = int(request.args.get("hours", "24"))
    limit = int(request.args.get("limit", "200"))

    if not tenant_id:
        return jsonify({"error": "Missing tenant_id"}), 400

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

    c.execute("""
        SELECT DISTINCT
            substr(nc.remote_address, 1,
                   CASE
                       WHEN instr(nc.remote_address, ':') > 0
                       THEN instr(nc.remote_address, ':') - 1
                       ELSE length(nc.remote_address)
                   END
            ) AS ip
        FROM network_connections nc
        JOIN computers c2 ON c2.id = nc.computer_id
        WHERE c2.tenant_id = ?
          AND c2.env_name = ?
          AND nc.timestamp > datetime('now', ?)
          AND nc.remote_address IS NOT NULL
          AND nc.remote_address != ''
        ORDER BY ip
        LIMIT ?
    """, (tenant_id, env, f"-{hours} hours", limit))

    ips = [r["ip"] for r in c.fetchall()]
    conn.close()

    return jsonify({"ips": ips})



@app.route("/api/public/sankey", methods=["GET"])
@require_grafana_token
def api_public_sankey():
    tenant_id = int(request.args.get("tenant_id", "1"))
    env = request.args.get("env", "office1")
    hours = int(request.args.get("hours", "24"))
    limit = int(request.args.get("limit", "20"))

    computers = (request.args.get("computers") or "all").strip()
    ips = (request.args.get("ips") or "all").strip()

    # normalize grafana All values
    if computers in ("", "__all"):
        computers = "all"
    if ips in ("", "__all"):
        ips = "all"

    comp_list = [c.strip() for c in computers.split(",") if c.strip()] if computers != "all" else []
    ip_list = [i.strip() for i in ips.split(",") if i.strip()] if ips != "all" else []

    comp_clause = ""
    ip_clause = ""
    params = [tenant_id, env, f"-{hours} hours"]

    # ✅ target без port за IPv4: "1.2.3.4:443" -> "1.2.3.4"
    # ⚠️ за IPv6 ќе го остави како што е (за да не го расипеме со split по ':')
    target_expr = """
    CASE
      WHEN nc.remote_address LIKE '%:%' AND nc.remote_address NOT LIKE '%:%:%'
      THEN substr(nc.remote_address, 1, instr(nc.remote_address, ':') - 1)
      ELSE nc.remote_address
    END
    """

    if comp_list:
        comp_clause = "AND comp.name IN (" + ",".join(["?"] * len(comp_list)) + ")"
        params.extend(comp_list)

    # ✅ Филтер по IP без порт (истиот expr)
    if ip_list:
        ip_clause = f"AND ({target_expr}) IN (" + ",".join(["?"] * len(ip_list)) + ")"
        params.extend(ip_list)

    # LIMIT на крај
    params.append(limit)

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

    c.execute(f"""
        SELECT
            comp.name AS source,
            ({target_expr}) AS target,
            COUNT(*) AS value
        FROM network_connections nc
        JOIN computers comp ON comp.id = nc.computer_id
        WHERE comp.tenant_id = ?
          AND comp.env_name = ?
          AND nc.timestamp > datetime('now', ?)
          AND nc.remote_address IS NOT NULL
          AND nc.remote_address != ''

          -- ✅ тргни loopback spam (овие прават да изгледа како да нема линии)
          AND nc.remote_address NOT LIKE '127.0.0.1%'
          AND nc.remote_address NOT LIKE '::1%'

          {comp_clause}
          {ip_clause}
        GROUP BY comp.name, target
        ORDER BY value DESC
        LIMIT ?
    """, params)

    rows = [dict(r) for r in c.fetchall()]
    conn.close()
    return jsonify(rows)

from urllib.parse import urlparse

def _ip_only(addr: str):
    # addr може да е "1.2.3.4:443" или "hostname:port"
    if not addr:
        return None
    # ако има повеќе ':' (ipv6), ќе го оставиме целото; за ipv4 split е ок
    if addr.count(":") == 1:
        return addr.split(":")[0]
    return addr

@app.route("/api/graph/net", methods=["GET"])
@require_user()
def api_graph_net():
    tenant_id = request.user["tenant_id"]
    env = request.args.get("env") or request.headers.get("X-Env", "default")
    computer = request.args.get("computer")  # optional
    since_hours = int(request.args.get("since_hours") or 24)

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

    params = [tenant_id, env, since_hours]
    comp_clause = ""
    if computer:
        comp_clause = "AND c2.name = ?"
        params.append(computer)

    # Вади top remote IP по број на конекции
    c.execute(f"""
        SELECT
          c2.name AS computer_name,
          n.remote_address AS remote_address,
          COUNT(*) AS cnt
        FROM network_connections n
        JOIN computers c2 ON c2.id = n.computer_id
        WHERE c2.tenant_id = ?
          AND c2.env_name = ?
          AND n.timestamp > datetime('now', '-' || ? || ' hours')
          {comp_clause}
          AND n.remote_address IS NOT NULL
          AND n.remote_address <> ''
        GROUP BY c2.name, n.remote_address
        ORDER BY cnt DESC
        LIMIT 200
    """, params)

    rows = c.fetchall()
    conn.close()

    # Build NodeGraph-like structure
    nodes = {}
    edges = []

    def add_node(node_id, title, ntype):
        if node_id not in nodes:
            nodes[node_id] = {"id": node_id, "title": title, "subTitle": ntype}

    for r in rows:
        comp_name = r["computer_name"]
        remote = _ip_only(r["remote_address"]) or r["remote_address"]
        cnt = int(r["cnt"] or 0)

        comp_id = f"comp:{comp_name}"
        ip_id = f"ip:{remote}"

        add_node(comp_id, comp_name, "computer")
        add_node(ip_id, remote, "remote_ip")

        edges.append({
            "id": f"{comp_id}->{ip_id}",
            "source": comp_id,
            "target": ip_id,
            "mainStat": str(cnt),
        })

    return jsonify({
        "nodes": list(nodes.values()),
        "edges": edges,
        "meta": {"env": env, "computer": computer, "since_hours": since_hours}
    })

@app.route("/api/public/netgraph", methods=["GET"])
@require_grafana_token
def api_public_netgraph():
    tenant_id = int(request.args.get("tenant_id", "1"))
    env = request.args.get("env", "default")
    hours = int(request.args.get("hours", "6"))
    limit_edges = int(request.args.get("limit", "200"))
    computer = request.args.get("computer")  # <-- NEW (optional)

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

    comp_clause = ""
    params = [tenant_id, env, f"-{hours} hours"]

    if computer and computer != "all":
        comp_clause = "AND comp.name = ?"
        params.append(computer)

    params.append(limit_edges)

    c.execute(f"""
        SELECT
            comp.name AS src,
            nc.remote_address AS remote,
            COUNT(*) AS cnt
        FROM network_connections nc
        JOIN computers comp ON comp.id = nc.computer_id
        WHERE comp.tenant_id = ?
          AND comp.env_name = ?
          AND nc.timestamp > datetime('now', ?)
          {comp_clause}
          AND nc.remote_address IS NOT NULL
          AND nc.remote_address != ''
        GROUP BY comp.name, nc.remote_address
        ORDER BY cnt DESC
        LIMIT ?
    """, params)

    rows = c.fetchall()
    conn.close()

    def ip_only(addr: str) -> str:
        if not addr:
            return ""
        if addr.startswith("[") and "]" in addr:
            return addr[1:addr.index("]")]
        if ":" in addr:
            return addr.split(":")[0]
        return addr

    nodes = {}
    edges = []

    for r in rows:
        src = r["src"]
        remote_ip = ip_only(r["remote"])
        cnt = int(r["cnt"] or 0)
        if not remote_ip:
            continue

        nodes[src] = {"id": src, "title": src, "subTitle": env, "mainStat": "PC"}
        ip_id = f"IP:{remote_ip}"
        if ip_id not in nodes:
            nodes[ip_id] = {"id": ip_id, "title": remote_ip, "subTitle": "remote", "mainStat": "IP"}

        edges.append({
            "id": f"{src}->{ip_id}",
            "source": src,
            "target": ip_id,
            "mainStat": cnt
        })

    return jsonify({"nodes": list(nodes.values()), "edges": edges})
@app.route("/api/public/computers", methods=["GET"])
@require_grafana_token
def api_public_computers():
    tenant_id = int(request.args.get("tenant_id", "1"))
    env = request.args.get("env", "default")

    conn = db()
    c = conn.cursor()
    c.execute("""
        SELECT name
        FROM computers
        WHERE tenant_id = ? AND env_name = ?
        ORDER BY name
    """, (tenant_id, env))
    names = [r["name"] for r in c.fetchall()]
    conn.close()

    return jsonify({"computers": names})

@app.route("/api/public/stats", methods=["GET"])
@require_grafana_token
def public_stats():
    # ⚠️ ако сакаш tenant по tenant, ќе мора да додадеш tenant_id како query param,
    # или да имаш 1 tenant во проект за сега.
    tenant_id = request.args.get("tenant_id", type=int)
    if not tenant_id:
        return jsonify({"error": "Missing tenant_id"}), 400

    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(),
    })


def ask_llm_sql_generator(question: str, tenant_id: int, env: str, computer_name: str | None):
    """
    Returns dict: { "queries": [...], "notes": "...", "scope": {...} }
    Only SELECT/WITH queries, no modifications.
    """
    if not client:
        return {
            "queries": [],
            "notes": "Немаш поставено OPENAI_API_KEY на серверот.",
            "scope": {"tenant_id": tenant_id, "env": env, "computer_name": computer_name},
        }

    schema = """
SQLite schema (важни табели):
- computers(id, tenant_id, env_name, name, user, ip, os, first_seen, last_seen, sysmon_available)
- computer_history(computer_id, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp)
- computer_processes_current(computer_id, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
- computer_processes_history(computer_id, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
- sysmon_events(computer_id, event_id, event_type, message, timestamp, details)
- network_connections(computer_id, pid, local_address, remote_address, status, process_name, timestamp)

Правила:
- Врати САМО валиден JSON.
- JSON формат: {"queries":[...], "notes":"..."}.
- queries мора да бидат само SELECT или WITH (никако INSERT/UPDATE/DELETE/ALTER/DROP).
- максимум 3 queries.
- користи placeholders: :tenant_id, :env, :computer_name (ако е дадено).
- ако нема доволно инфо -> queries празно и notes објаснува.
""".strip()

    user_scope = {
        "tenant_id": tenant_id,
        "env": env,
        "computer_name": computer_name,
    }

    user_msg = f"""
Прашање од корисник: {question}

Scope (вметни во WHERE):
- tenant_id = :tenant_id
- env = :env
- computer_name = :computer_name (ако не е null)

Врати 1-3 SELECT/WITH queries за да се добијат потребните податоци.
""".strip()

    completion = client.chat.completions.create(
        model="gpt-4.1-mini",
        messages=[
            {"role": "system", "content": schema},
            {"role": "user", "content": user_msg},
        ],
        temperature=0.1,
    )

    raw = completion.choices[0].message.content or ""

    # Safe parse JSON
    try:
        out = json.loads(raw)
        if not isinstance(out, dict):
            raise ValueError("Not an object")
        queries = out.get("queries") or []
        notes = out.get("notes") or ""

        # hard safety filter: keep only SELECT/WITH
        safe_queries = []
        for q in queries:
            if not isinstance(q, str):
                continue
            qs = q.strip().lower()
            if qs.startswith("select") or qs.startswith("with"):
                # block obvious multi-statement
                if ";" in q.strip().rstrip(";"):
                    continue
                safe_queries.append(q.strip())

        return {"queries": safe_queries[:3], "notes": notes, "scope": user_scope}

    except Exception:
        # fallback ако AI не врати JSON
        return {
            "queries": [],
            "notes": "AI не врати валиден JSON. Обиди се повторно (или намали прашање).",
            "scope": user_scope,
            "raw": raw[:1200],
        }


@app.route("/api/chat", methods=["POST"])
@require_user()
def api_chat():
    try:
        tenant_id = request.user["tenant_id"]
        env = request.headers.get("X-Env", "default")  # важно!
        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

        out = ask_llm_sql_generator(
            question=question,
            tenant_id=tenant_id,
            env=env,
            computer_name=computer_name,
        )

        # Тука не извршуваме SQL, само враќаме query за копирање.
        return jsonify({
            "ok": True,
            "queries": out.get("queries", []),
            "notes": out.get("notes", ""),
            "scope": out.get("scope", {}),
            # "raw": out.get("raw")  # ако сакаш debug
        }), 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)