import http from "node:http";
import { mkdir, readFile, readdir, unlink, writeFile } from "node:fs/promises";
import { existsSync } from "node:fs";
import { randomUUID } from "node:crypto";
import bcrypt from "bcryptjs";
import mysql from "mysql2/promise";

const PORT = Number(process.env.PORT || 5174);
const HOST = process.env.HOST || "127.0.0.1";
const DATA_FILE = new URL("../data/local-dashboard.json", import.meta.url);
const AVATAR_UPLOAD_DIR = new URL("../uploads/avatars/", import.meta.url);
const SHARE_DIR = new URL("../uploads/shares/", import.meta.url);
let dbPool;

async function readData() {
  if (!existsSync(DATA_FILE)) {
    return {
      ready: false,
      message: "Todavia no existe data/local-dashboard.json. Ejecuta npm run extract:backup."
    };
  }
  return JSON.parse(await readFile(DATA_FILE, "utf8"));
}

async function readJsonBody(req) {
  let body = "";
  for await (const chunk of req) body += chunk;
  return body ? JSON.parse(body) : {};
}

function normalize(value) {
  return String(value || "").trim().toLowerCase();
}

function publicSession(user, extra = {}) {
  return {
    token: `local-${Date.now()}-${Math.random().toString(16).slice(2)}`,
    user,
    ...extra
  };
}

function formatNumber(value) {
  const numeric = Number(value || 0);
  if (numeric >= 1_000_000) return `${(numeric / 1_000_000).toFixed(1)}M`;
  if (numeric >= 1_000) return `${(numeric / 1_000).toFixed(1)}K`;
  return String(numeric);
}

function isBlockedRow(row) {
  return row.category_group !== "Permitido" && row.category !== "Permitido";
}

function formatRelative(timestamp, latestDate) {
  if (!timestamp) return "-";
  const value = String(timestamp).replace("T", " ").slice(0, 23);
  if (value.slice(0, 10) === latestDate) return "hoy";
  return value.slice(0, 16);
}

function normalizeMysqlTime(value) {
  if (!value) return "";
  if (value instanceof Date) return value.toISOString().replace("T", " ").slice(0, 23);
  return String(value).replace("T", " ").slice(0, 23);
}

function escapeHtml(value) {
  return String(value ?? "")
    .replaceAll("&", "&amp;")
    .replaceAll("<", "&lt;")
    .replaceAll(">", "&gt;")
    .replaceAll('"', "&quot;")
    .replaceAll("'", "&#39;");
}

function compactText(value, max = 320) {
  const text = String(value || "").replace(/\s+/g, " ").trim();
  return text.length > max ? `${text.slice(0, max - 1)}…` : text;
}

function getAllowedOrigin(origin) {
  const allowedOrigins = String(process.env.CORS_ORIGINS || "*")
    .split(",")
    .map((item) => item.trim())
    .filter(Boolean);

  if (allowedOrigins.includes("*")) return "*";
  if (origin && allowedOrigins.includes(origin)) return origin;
  return allowedOrigins[0] || "*";
}

function getPublicBaseUrl(req) {
  if (process.env.PUBLIC_API_BASE) return process.env.PUBLIC_API_BASE.replace(/\/$/, "");
  const host = req.headers["x-forwarded-host"] || req.headers.host || `localhost:${PORT}`;
  const proto = req.headers["x-forwarded-proto"] || (String(host).includes(":8443") ? "https" : "http");
  return `${proto}://${host}`;
}

async function askOpenAI({ system, user, fallback }) {
  const apiKey = getOpenAIKey();
  if (!apiKey) return fallback;

  try {
    const response = await fetch("https://api.openai.com/v1/chat/completions", {
      method: "POST",
      headers: {
        "content-type": "application/json",
        authorization: `Bearer ${apiKey}`
      },
      body: JSON.stringify({
        model: process.env.OPENAI_MODEL || "gpt-4o-mini",
        temperature: 0.25,
        messages: [
          { role: "system", content: system },
          { role: "user", content: user }
        ]
      })
    });
    if (!response.ok) return fallback;
    const data = await response.json();
    return compactText(data.choices?.[0]?.message?.content || fallback, 900);
  } catch {
    return fallback;
  }
}

function getOpenAIKey() {
  return process.env.OPENAI_API_KEY || process.env.CHATGPT_API_KEY || "";
}

async function saveAvatarUpload(upload, userId, req) {
  if (!upload?.dataUrl) return null;

  const match = String(upload.dataUrl).match(/^data:(image\/(?:png|jpeg|jpg|webp|gif));base64,([A-Za-z0-9+/=]+)$/);
  if (!match) throw new Error("La imagen debe ser PNG, JPG, WEBP o GIF");

  const mime = match[1] === "image/jpg" ? "image/jpeg" : match[1];
  const buffer = Buffer.from(match[2], "base64");
  if (buffer.length > 5 * 1024 * 1024) throw new Error("La imagen no puede superar 5 MB");

  const extensionByMime = {
    "image/png": "png",
    "image/jpeg": "jpg",
    "image/webp": "webp",
    "image/gif": "gif"
  };
  const extension = extensionByMime[mime];
  const filename = `user-${userId}-${Date.now()}.${extension}`;

  await mkdir(AVATAR_UPLOAD_DIR, { recursive: true });
  await writeFile(new URL(filename, AVATAR_UPLOAD_DIR), buffer);
  return `${getPublicBaseUrl(req)}/uploads/avatars/${filename}`;
}

async function serveUploadedAvatar(req, res) {
  const url = new URL(req.url, `http://${req.headers.host || "localhost"}`);
  const filename = url.pathname.split("/").pop();
  if (!/^[a-zA-Z0-9._-]+$/.test(filename || "")) {
    res.writeHead(404, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Not found" }));
    return;
  }

  const extension = filename.split(".").pop()?.toLowerCase();
  const mimeByExtension = {
    png: "image/png",
    jpg: "image/jpeg",
    jpeg: "image/jpeg",
    webp: "image/webp",
    gif: "image/gif"
  };

  try {
    const file = await readFile(new URL(filename, AVATAR_UPLOAD_DIR));
    res.writeHead(200, {
      "content-type": mimeByExtension[extension] || "application/octet-stream",
      "cache-control": "public, max-age=31536000, immutable"
    });
    res.end(file);
  } catch {
    res.writeHead(404, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Not found" }));
  }
}

function hasDatabaseConfig() {
  return Boolean(process.env.DATABASE_URL || (process.env.DB_HOST && process.env.DB_USER && process.env.DB_NAME));
}

function getDbPool() {
  if (!hasDatabaseConfig()) return null;
  if (!dbPool) {
    dbPool = process.env.DATABASE_URL
      ? mysql.createPool(process.env.DATABASE_URL)
      : mysql.createPool({
        host: process.env.DB_HOST,
        port: Number(process.env.DB_PORT || 3306),
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD || "",
        database: process.env.DB_NAME,
        waitForConnections: true,
        connectionLimit: Number(process.env.DB_CONNECTION_LIMIT || 10),
        dateStrings: true
      });
  }
  return dbPool;
}

async function findDbUser(body) {
  const pool = getDbPool();
  if (!pool) return null;

  if (body.mode === "support") {
    const adminName = normalize(body.adminName);
    const [rows] = await pool.execute(
      "SELECT id, username, password, display_name, email, role, active FROM users WHERE active = 1 AND role = 'admin' AND (LOWER(display_name) = ? OR LOWER(username) = ? OR LOWER(email) = ?) LIMIT 1",
      [adminName, adminName, adminName]
    );
    return rows[0] || null;
  }

  if (body.mode === "client") {
    const username = normalize(body.username || body.adminName || body.email || body.clientId);
    const [rows] = await pool.execute(
      `SELECT u.id, u.username, u.password, u.display_name, u.email, u.role, u.active, cm.adguard_client_id
       FROM users u
       LEFT JOIN client_mapping cm ON cm.user_id = u.id
       WHERE u.active = 1
         AND u.role = 'client'
         AND (LOWER(u.username) = ? OR LOWER(u.email) = ? OR LOWER(u.display_name) = ? OR LOWER(cm.adguard_client_id) = ?)
       LIMIT 1`,
      [username, username, username, username]
    );
    return rows[0] || null;
  }

  return null;
}

async function loginWithDatabase(body, password) {
  const user = await findDbUser(body);
  if (!user) return null;

  const passwordMatches = await bcrypt.compare(password, user.password);
  if (!passwordMatches) {
    return { error: body.mode === "support" ? "Clave de administrador incorrecta" : "Usuario o clave incorrectos" };
  }

  const pool = getDbPool();
  await pool.execute(
    "UPDATE users SET last_login = NOW(), last_seen = NOW(), login_count = COALESCE(login_count, 0) + 1 WHERE id = ?",
    [user.id]
  );

  if (user.role === "admin") {
    return publicSession({
      type: "admin",
      id: user.id,
      name: user.display_name || user.username,
      role: "Administrador"
    }, { authSource: "database" });
  }

  return publicSession({
    type: "client",
    id: user.id,
    name: user.display_name || user.username,
    role: "Cliente",
    clientId: user.adguard_client_id || user.username
  }, { authSource: "database" });
}

async function buildSummaryFromDatabase() {
  const pool = getDbPool();
  if (!pool) return null;

  const [[latestRow]] = await pool.execute("SELECT log_time latestLogTime FROM query_logs FORCE INDEX (idx_time_reason) ORDER BY log_time DESC LIMIT 1");
  const [[firstRow]] = await pool.execute("SELECT log_time firstLogTime FROM query_logs FORCE INDEX (idx_time_reason) ORDER BY log_time ASC LIMIT 1");
  const [[tableStats]] = await pool.execute(
    "SELECT table_rows queryRows FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'query_logs'"
  );
  const latestLogTime = normalizeMysqlTime(latestRow?.latestLogTime);
  const firstLogTime = normalizeMysqlTime(firstRow?.firstLogTime);
  const latestDate = latestLogTime.slice(0, 10);
  const [[last24hRow]] = await pool.execute(
    "SELECT DATE_FORMAT(DATE_SUB(?, INTERVAL 24 HOUR), '%Y-%m-%d %H:%i:%s') last24hStart",
    [latestLogTime]
  );
  const last24hStart = last24hRow?.last24hStart || `${latestDate} 00:00:00`;

  const [users] = await pool.execute(
    `SELECT id, username, display_name, email, role, active, last_login, avatar, login_count, last_seen
     FROM users
     WHERE active = 1
     ORDER BY role, display_name, username`
  );

  const [mappings] = await pool.execute(
    "SELECT user_id, adguard_client_id, label FROM client_mapping ORDER BY user_id, id"
  );
  const mappedByUser = new Map();
  for (const mapping of mappings) {
    const list = mappedByUser.get(Number(mapping.user_id)) || [];
    list.push(mapping);
    mappedByUser.set(Number(mapping.user_id), list);
  }

  const [clientStats] = await pool.execute(
    `SELECT adguard_client_id,
            COUNT(*) queries24h,
            SUM(CASE WHEN category_group <> 'Permitido' AND category <> 'Permitido' THEN 1 ELSE 0 END) blocked24h,
            MAX(log_time) rawLastActivity
     FROM query_logs
     WHERE log_time >= ?
     GROUP BY adguard_client_id`,
    [last24hStart]
  );
  const statsByClientId = new Map(clientStats.map((row) => [row.adguard_client_id, row]));

  const [hourRows] = await pool.execute(
    `SELECT DATE_FORMAT(log_time, '%Y-%m-%d %H') hour,
            COUNT(*) queries,
            SUM(CASE WHEN category_group <> 'Permitido' AND category <> 'Permitido' THEN 1 ELSE 0 END) blocked
     FROM query_logs
     WHERE log_time >= ?
     GROUP BY DATE_FORMAT(log_time, '%Y-%m-%d %H')
     ORDER BY hour`,
    [last24hStart]
  );

  const [sync] = await pool.execute(
    `SELECT s.server_id, s.status, s.records_fetched, s.records_new, s.started_at, s.finished_at
     FROM sync_log s
     INNER JOIN (
       SELECT server_id, MAX(id) id
       FROM sync_log
       GROUP BY server_id
     ) latest ON latest.id = s.id
     ORDER BY s.server_id`
  );

  const [topAdguard] = await pool.execute(
    `SELECT adguard_client_id id,
            COALESCE(MAX(NULLIF(client_name, '')), adguard_client_id) name,
            COUNT(*) queries30d
     FROM query_logs
     WHERE log_time >= ?
     GROUP BY adguard_client_id
     ORDER BY queries30d DESC
     LIMIT 200`,
    [last24hStart]
  );

  const [topBlockedDomains] = await pool.execute(
    `SELECT domain, category, COUNT(*) count
     FROM query_logs
     WHERE log_time >= ?
       AND category_group <> 'Permitido'
       AND category <> 'Permitido'
     GROUP BY domain, category
     ORDER BY count DESC
     LIMIT 20`,
    [last24hStart]
  );

  const [categories] = await pool.execute(
    `SELECT category name,
            COUNT(*) count,
            SUM(CASE WHEN category_group <> 'Permitido' AND category <> 'Permitido' THEN 1 ELSE 0 END) blocked
     FROM query_logs
     WHERE log_time >= ?
     GROUP BY category
     ORDER BY count DESC`,
    [last24hStart]
  );

  const clientUsers = users.filter((user) => user.role === "client");
  const clients = clientUsers.map((user) => {
    const userMappings = mappedByUser.get(Number(user.id)) || [];
    const clientIds = userMappings.map((mapping) => mapping.adguard_client_id);
    const primaryClientId = clientIds[0] || user.username;
    const summary = clientIds.reduce((acc, clientId) => {
      const row = statsByClientId.get(clientId);
      if (!row) return acc;
      acc.queries += Number(row.queries24h || 0);
      acc.blocked += Number(row.blocked24h || 0);
      const rawLastActivity = normalizeMysqlTime(row.rawLastActivity);
      if (!acc.rawLastActivity || rawLastActivity > acc.rawLastActivity) acc.rawLastActivity = rawLastActivity;
      return acc;
    }, { queries: 0, blocked: 0, rawLastActivity: "" });
    const rate = summary.queries ? Math.round((summary.blocked / summary.queries) * 100) : 0;

    return {
      id: user.id,
      name: user.display_name || user.username,
      email: user.email || user.username,
      clientIds,
      primaryClientId,
      queries24h: summary.queries,
      queries24hLabel: formatNumber(summary.queries),
      blocked24h: summary.blocked,
      blocked24hLabel: formatNumber(summary.blocked),
      blockRate: `${rate}%`,
      lastActivity: formatRelative(summary.rawLastActivity, latestDate),
      rawLastActivity: summary.rawLastActivity,
      active: user.active === 1
    };
  }).sort((a, b) => b.queries24h - a.queries24h || a.name.localeCompare(b.name));

  const todayQueries = clients.reduce((sum, client) => sum + client.queries24h, 0);
  const todayBlocked = clients.reduce((sum, client) => sum + client.blocked24h, 0);

  return {
    generatedAt: new Date().toISOString(),
    source: {
      mode: "live-adguard",
      firstLogTime,
      latestLogTime,
      latestDate,
      queryRows: Number(tableStats?.queryRows || 0)
    },
    metrics: {
      activeClients: clients.length,
      queriesToday: todayQueries,
      queriesTodayLabel: formatNumber(todayQueries),
      blockedToday: todayBlocked,
      blockedTodayLabel: formatNumber(todayBlocked),
      onlineNow: clients.filter((client) => client.lastActivity === "hoy").length,
      blockRateToday: todayQueries ? `${Math.round((todayBlocked / todayQueries) * 100)}%` : "0%"
    },
    admins: users.filter((user) => user.role === "admin").map((user) => {
      const lastSeen = normalizeMysqlTime(user.last_seen || user.last_login);
      const lastSeenMs = lastSeen ? new Date(lastSeen.replace(" ", "T")).getTime() : 0;
      const online = lastSeenMs ? Date.now() - lastSeenMs <= 5 * 60 * 1000 : false;
      return {
        id: user.id,
        name: user.display_name || user.username,
        username: user.username,
        email: user.email,
        role: user.role,
        lastLogin: normalizeMysqlTime(user.last_login),
        lastSeen,
        online,
        sessions: Number(user.login_count || 0),
        avatar: user.avatar,
        status: online ? "en línea" : formatRelative(lastSeen, latestDate)
      };
    }),
    users: users.map((user) => {
      const lastSeen = normalizeMysqlTime(user.last_seen || user.last_login);
      const lastSeenMs = lastSeen ? new Date(lastSeen.replace(" ", "T")).getTime() : 0;
      return {
        id: user.id,
        username: user.username,
        display_name: user.display_name,
        email: user.email,
        role: user.role,
        active: user.active,
        last_login: normalizeMysqlTime(user.last_login),
        last_seen: lastSeen,
        online: lastSeenMs ? Date.now() - lastSeenMs <= 5 * 60 * 1000 : false,
        login_count: Number(user.login_count || 0),
        avatar: user.avatar,
        clientIds: (mappedByUser.get(Number(user.id)) || []).map((mapping) => mapping.adguard_client_id)
      };
    }),
    clients,
    adguardClients: topAdguard.map((row) => ({
      id: row.id,
      name: row.name || row.id,
      queries30d: Number(row.queries30d || 0),
      queries30dLabel: formatNumber(row.queries30d || 0)
    })),
    sync: sync.map((row) => ({
      ...row,
      started_at: normalizeMysqlTime(row.started_at),
      finished_at: normalizeMysqlTime(row.finished_at)
    })),
    hourSeries: hourRows.map((row) => ({
      hour: String(row.hour).slice(11),
      queries: Number(row.queries || 0),
      blocked: Number(row.blocked || 0)
    })),
    topBlockedDomains: topBlockedDomains.map((row) => ({
      domain: row.domain,
      category: row.category,
      count: Number(row.count || 0)
    })),
    categories: categories.map((row) => ({
      name: row.name || "Sin categoria",
      count: Number(row.count || 0),
      blocked: Number(row.blocked || 0)
    })),
    clientReports: {}
  };
}

async function getClientIdentity(pool, clientId) {
  const [ownerRows] = await pool.execute(
    `SELECT u.id userId, u.display_name name, u.email email
     FROM client_mapping cm
     INNER JOIN users u ON u.id = cm.user_id
     WHERE cm.adguard_client_id = ?
     LIMIT 1`,
    [clientId]
  );
  if (!ownerRows[0]?.userId) {
    return { clientIds: [clientId], name: clientId, email: "" };
  }
  const [mappingRows] = await pool.execute("SELECT adguard_client_id FROM client_mapping WHERE user_id = ? ORDER BY id", [ownerRows[0].userId]);
  return {
    clientIds: mappingRows.map((row) => row.adguard_client_id),
    name: ownerRows[0].name || clientId,
    email: ownerRows[0].email || ""
  };
}

async function buildClientReportFromDatabase(clientId, days = 1, logLimit = 5000, logOffset = 0) {
  const pool = getDbPool();
  const [[latestRow]] = await pool.execute("SELECT MAX(log_time) latestLogTime FROM query_logs");
  const latestLogTime = normalizeMysqlTime(latestRow?.latestLogTime);
  const latestDate = latestLogTime.slice(0, 10);
  const rangeDays = Math.min(Math.max(Number(days) || 1, 1), 30);
  const recentLogLimit = Math.min(Math.max(Number(logLimit) || 5000, 500), 20000);
  const recentLogOffset = Math.max(Number(logOffset) || 0, 0);
  const rangeSql = rangeDays <= 1
    ? "SELECT DATE_FORMAT(DATE_SUB(?, INTERVAL 24 HOUR), '%Y-%m-%d %H:%i:%s') rangeStart"
    : `SELECT DATE_FORMAT(DATE_SUB(?, INTERVAL ${rangeDays - 1} DAY), '%Y-%m-%d 00:00:00') rangeStart`;
  const rangeBase = rangeDays <= 1 ? latestLogTime : `${latestDate} 00:00:00`;
  const [[rangeRow]] = await pool.execute(rangeSql, [rangeBase]);
  const rangeStart = rangeRow?.rangeStart || `${latestDate} 00:00:00`;

  const identity = await getClientIdentity(pool, clientId);
  const clientIds = identity.clientIds.length ? identity.clientIds : [clientId];

  const [reportHourRows] = await pool.query(
    `SELECT DATE_FORMAT(log_time, '%Y-%m-%d %H') hour,
            COUNT(*) queries,
            SUM(CASE WHEN category_group <> 'Permitido' AND category <> 'Permitido' THEN 1 ELSE 0 END) blocked
     FROM query_logs
     WHERE log_time >= ?
       AND adguard_client_id IN (?)
     GROUP BY DATE_FORMAT(log_time, '%Y-%m-%d %H')
     ORDER BY hour`,
    [rangeStart, clientIds]
  );
  const [reportCategories] = await pool.query(
    `SELECT category name,
            COUNT(*) count,
            SUM(CASE WHEN category_group <> 'Permitido' AND category <> 'Permitido' THEN 1 ELSE 0 END) blocked
     FROM query_logs
     WHERE log_time >= ?
       AND adguard_client_id IN (?)
     GROUP BY category
     ORDER BY blocked DESC, count DESC
     LIMIT 12`,
    [rangeStart, clientIds]
  );
  const [reportDomains] = await pool.query(
    `SELECT domain, category, COUNT(*) count
     FROM query_logs
     WHERE log_time >= ?
       AND adguard_client_id IN (?)
       AND category_group <> 'Permitido'
       AND category <> 'Permitido'
     GROUP BY domain, category
     ORDER BY count DESC
     LIMIT 10`,
    [rangeStart, clientIds]
  );
  const [[logCountRow]] = await pool.query(
    `SELECT COUNT(*) total
     FROM query_logs
     WHERE log_time >= ?
       AND adguard_client_id IN (?)`,
    [rangeStart, clientIds]
  );
  const [recentLogs] = await pool.query(
    `SELECT log_time time, domain, query_type type, reason status, category, filter_name filter
     FROM query_logs
     WHERE log_time >= ?
       AND adguard_client_id IN (?)
     ORDER BY log_time DESC
     LIMIT ? OFFSET ?`,
    [rangeStart, clientIds, recentLogLimit, recentLogOffset]
  );

  return {
    clientId,
    clientName: identity.name,
    clientEmail: identity.email,
    clientIds,
    days: rangeDays,
    logLimit: recentLogLimit,
    logOffset: recentLogOffset,
    totalLogs: Number(logCountRow?.total || 0),
    rangeStart,
    latestLogTime,
    hourly: reportHourRows.map((row) => ({
      hour: String(row.hour),
      label: `${String(row.hour).slice(11)}:00`,
      queries: Number(row.queries || 0),
      blocked: Number(row.blocked || 0)
    })),
    categories: reportCategories.map((row) => ({
      name: row.name || "Sin categoria",
      count: Number(row.count || 0),
      blocked: Number(row.blocked || 0)
    })),
    topBlockedDomains: reportDomains.map((row) => ({
      domain: row.domain,
      category: row.category,
      count: Number(row.count || 0)
    })),
    recentLogs: recentLogs.map((row) => ({
      time: normalizeMysqlTime(row.time),
      domain: row.domain,
      type: row.type || "A",
      status: row.status || "",
      category: row.category || "Permitido",
      filter: row.filter || "-"
    }))
  };
}

function reportTotals(report) {
  const queries = report.hourly.reduce((sum, row) => sum + Number(row.queries || 0), 0);
  const blocked = report.hourly.reduce((sum, row) => sum + Number(row.blocked || 0), 0);
  const rate = queries ? Math.round((blocked / queries) * 100) : 0;
  const mainCategory = report.categories.find((row) => Number(row.blocked || 0) > 0)?.name || "Permitido";
  return { queries, blocked, rate, mainCategory };
}

async function generateReportSummary(report) {
  const totals = reportTotals(report);
  const domains = report.topBlockedDomains.slice(0, 8).map((row) => `${row.domain} (${row.count}, ${row.category})`).join(", ");
  const fallback = `En los últimos ${report.days} días se registraron ${formatNumber(totals.queries)} consultas DNS y ${formatNumber(totals.blocked)} bloqueos (${totals.rate}%). La categoría principal fue ${totals.mainCategory}. Dominios bloqueados destacados: ${domains || "sin bloqueos relevantes"}.`;
  return askOpenAI({
    fallback,
    system: "Sos un analista de ciberseguridad DNS. Redactá en español argentino, claro y breve, para un cliente no técnico. No inventes datos.",
    user: `Generá un resumen ejecutivo de 4 a 6 líneas para este reporte DNS de ${report.clientName}. Datos: ${JSON.stringify({ days: report.days, totals, categories: report.categories.slice(0, 6), topBlockedDomains: report.topBlockedDomains.slice(0, 8) })}`
  });
}

function percent(value, total) {
  const numeric = Number(value || 0);
  const base = Number(total || 0);
  return base ? Math.max(2, Math.round((numeric / base) * 100)) : 2;
}

function formatReportDate(value) {
  const text = String(value || "").replace("T", " ").slice(0, 10);
  if (!text) return "";
  const [year, month, day] = text.split("-");
  return `${day}/${month}/${year}`;
}

async function explainDomainForReport(pool, row) {
  const domain = String(row.domain || "").trim().toLowerCase();
  const category = String(row.category || "").trim();
  if (!domain) return { ...fallbackDomainExplanation(domain, category), domain, category, source: "fallback" };

  const [cached] = await pool.execute("SELECT domain, que_es, por_que, riesgo, consejo FROM domain_explanations WHERE domain = ? LIMIT 1", [domain]);
  const cachedExplanation = cached[0];
  if (cachedExplanation && (!isGenericDomainExplanation(cachedExplanation) || !getOpenAIKey())) {
    return { ...cachedExplanation, category, source: isGenericDomainExplanation(cachedExplanation) ? "fallback-cache" : "database" };
  }

  const fallback = fallbackDomainExplanation(domain, category);
  const aiText = await askOpenAI({
    fallback: JSON.stringify(fallback),
    system: "Sos un analista DNS de PCASSI para reportes de clientes. Devolvé SOLO JSON válido con keys que_es, por_que, riesgo, consejo. Español simple, claro y amable. 1 frase corta por campo. riesgo: bajo, medio o alto. Nunca menciones AdGuard ni proveedores internos; usá 'ZeroTrust DNS' o 'los servidores de ciberseguridad de PCASSI'.",
    user: `Explicá el dominio bloqueado ${domain}. Clasificación interna: ${category || "sin categoría"}. Eventos de la semana: ${row.count || 0}. Si reconocés el dominio por conocimiento público, nombrá el servicio o tipo de actividad.`
  });

  let explanation = fallback;
  try {
    explanation = { ...fallback, ...JSON.parse(aiText.replace(/^```json|```$/g, "").trim()) };
  } catch {}

  await pool.execute(
    `INSERT INTO domain_explanations (domain, que_es, por_que, riesgo, consejo)
     VALUES (?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE que_es=VALUES(que_es), por_que=VALUES(por_que), riesgo=VALUES(riesgo), consejo=VALUES(consejo)`,
    [domain, explanation.que_es, explanation.por_que, explanation.riesgo, explanation.consejo]
  );

  return { domain, category, ...explanation, source: getOpenAIKey() ? "openai" : "fallback" };
}

async function buildShareDomainCards(report) {
  const pool = getDbPool();
  const rows = report.topBlockedDomains.slice(0, 9);
  const cards = [];
  for (const row of rows) {
    const explanation = await explainDomainForReport(pool, row);
    cards.push({ ...row, explanation });
  }
  return cards;
}

function buildShareHtml(report, summary, expiresAt, domainCards = []) {
  const totals = reportTotals(report);
  const generatedAt = new Date().toLocaleString("es-AR", { timeZone: "America/Argentina/Buenos_Aires" });
  const periodEnd = report.latestLogTime || new Date().toISOString();
  const categoryTotal = report.categories.reduce((sum, row) => sum + Number(row.blocked || 0), 0) || 1;
  const colors = ["#6f7a84", "#ff4f66", "#f5c542", "#25d1a2", "#d93470", "#8a6dff", "#ff7a3d", "#4f8cff", "#9aa5b1"];
  const categoryRows = report.categories.slice(0, 9).map((row, index) => `
    <div class="category-row">
      <span><i style="background:${colors[index % colors.length]}"></i>${escapeHtml(row.name)}</span>
      <div><b style="width:${percent(row.blocked, categoryTotal)}%;background:${colors[index % colors.length]}"></b></div>
      <strong>${escapeHtml(row.blocked || 0)}</strong>
    </div>
  `).join("");
  const domainHtml = domainCards.map((row, index) => {
    const explanation = row.explanation || fallbackDomainExplanation(row.domain, row.category);
    return `
      <article class="domain-card card-${index % 3}">
        <div class="domain-title">
          <h3>${escapeHtml(row.domain)}</h3>
          <span>${escapeHtml(row.category || "Otro Bloqueo")}</span>
          <em>Riesgo ${escapeHtml(explanation.riesgo || "medio")}</em>
        </div>
        <p><b>Qué es:</b> ${escapeHtml(explanation.que_es)}</p>
        <p><b>Por qué lo bloqueamos:</b> ${escapeHtml(explanation.por_que)}</p>
        <div class="advice">💡 ${escapeHtml(explanation.consejo)}</div>
        <footer>Bloqueado ${escapeHtml(row.count)} veces esta semana</footer>
      </article>
    `;
  }).join("");

  return `<!doctype html>
<html lang="es">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Reporte DNS - ${escapeHtml(report.clientName)}</title>
  <style>
    :root{--bg:#edf3fb;--ink:#152033;--muted:#68758a;--blue:#2864e8;--cyan:#44d4e9;--green:#0c9b72;--line:#dce5f0;--shadow:0 22px 70px rgba(42,75,115,.16)}
    *{box-sizing:border-box}body{margin:0;font-family:Inter,system-ui,-apple-system,Segoe UI,sans-serif;background:radial-gradient(circle at 20% 0,#fff 0,#edf3fb 42%,#e8eef8 100%);color:var(--ink)}
    main{max-width:1100px;margin:auto;padding:34px 20px 42px}.top-access{display:flex;justify-content:flex-end;margin-bottom:14px}.panel-access{display:inline-flex;align-items:center;justify-content:center;min-height:42px;padding:0 18px;border-radius:999px;color:white;background:linear-gradient(135deg,#ff4f66,#ff8a3d);box-shadow:0 16px 38px rgba(255,79,102,.28);font-weight:900;text-decoration:none}.panel-access:hover{filter:brightness(1.06)}
    .hero{display:grid;grid-template-columns:1fr 220px;gap:22px;align-items:center;padding:34px;border-radius:22px;color:white;background:linear-gradient(135deg,#173caa,#2f83ff);box-shadow:var(--shadow);overflow:hidden;position:relative}
    .hero:after{content:"";position:absolute;right:-120px;top:-120px;width:360px;height:360px;border-radius:50%;border:1px solid rgba(255,255,255,.18)}.brand{display:flex;align-items:center;gap:16px}.brand img{width:56px;height:56px;border-radius:15px;background:white;padding:6px}.hero h1{font-size:2.05rem;margin:18px 0 8px}.hero p{margin:0;color:#dbeafe}.period{margin-top:22px;color:#bed2ff;font-size:.92rem}.hero-kpi{position:relative;isolation:isolate;overflow:hidden;border:1px solid rgba(255,255,255,.3);border-radius:16px;padding:22px;text-align:center;background:linear-gradient(120deg,#ff3f62,#ff8a3d,#ffb13d,#ff3f62);background-size:220% 220%;box-shadow:0 22px 54px rgba(255,79,102,.32);animation:threatPulse 5s ease-in-out infinite}.hero-kpi:before{content:"";position:absolute;inset:-40%;z-index:-1;background:linear-gradient(90deg,transparent,rgba(255,255,255,.35),transparent);transform:rotate(18deg) translateX(-120%);animation:threatSweep 3.8s ease-in-out infinite}.hero-kpi b{display:block;font-size:3.6rem;line-height:1;text-shadow:0 12px 26px rgba(110,20,20,.24)}.hero-kpi span{font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;font-weight:900}@keyframes threatPulse{0%,100%{background-position:0% 50%;transform:translateY(0)}50%{background-position:100% 50%;transform:translateY(-2px)}}@keyframes threatSweep{0%,45%{transform:rotate(18deg) translateX(-130%)}75%,100%{transform:rotate(18deg) translateX(130%)}}
    .metric-grid{display:grid;grid-template-columns:repeat(3,1fr);gap:16px;margin:22px 0}.metric{display:flex;align-items:center;gap:14px;padding:22px;border-radius:16px;background:white;box-shadow:var(--shadow)}.metric i{display:grid;place-items:center;width:42px;height:42px;border-radius:12px;background:#e7f4ff;color:var(--blue);font-style:normal}.metric b{display:block;font-size:1.75rem;color:var(--blue)}.metric:nth-child(2) b{color:#d93455}.metric:nth-child(3) b{color:#05a66b}.metric span{display:block;color:var(--muted);font-size:.72rem;text-transform:uppercase;letter-spacing:.1em;font-weight:900}
    .panel{padding:24px;border-radius:18px;background:white;box-shadow:var(--shadow);margin-bottom:22px}.panel h2{margin:0 0 18px;font-size:1.15rem}.summary{line-height:1.6;color:#314058}.category-grid{display:grid;grid-template-columns:repeat(3,1fr);gap:12px 26px}.category-row{display:grid;grid-template-columns:1fr 92px 42px;gap:12px;align-items:center;font-size:.92rem}.category-row span{display:flex;gap:8px;align-items:center}.category-row i{width:10px;height:10px;border-radius:3px}.category-row div{height:9px;border-radius:999px;background:#eef2f7;overflow:hidden}.category-row b{display:block;height:100%;border-radius:999px}.category-row strong{text-align:right}
    .domain-grid{display:grid;grid-template-columns:repeat(3,1fr);gap:18px}.domain-card{background:white;border-radius:16px;padding:20px;border-top:4px solid #6f7a84;box-shadow:var(--shadow)}.domain-card.card-1{border-top-color:#05c49a}.domain-card.card-2{border-top-color:#f4c400}.domain-title h3{margin:0 0 10px;font-size:1rem;overflow-wrap:anywhere}.domain-title span,.domain-title em{display:inline-flex;margin:0 6px 10px 0;padding:5px 9px;border-radius:999px;background:#f0f4f8;color:#64748b;font-size:.72rem;font-style:normal}.domain-title em{background:#fff7d6;color:#9a6900}.domain-card p{font-size:.92rem;line-height:1.55;color:#344256}.domain-card p b{color:#17325f}.advice{margin-top:14px;padding:13px;border-radius:12px;background:#ecfdf4;color:#12664b;font-size:.88rem;line-height:1.45}.domain-card footer{margin-top:14px;padding-top:12px;border-top:1px solid #edf2f7;color:#8a96a8;font-size:.78rem}
    .safe{margin-top:28px;padding:30px;border-radius:18px;text-align:center;color:white;background:linear-gradient(135deg,#07704f,#05a67a);box-shadow:0 24px 60px rgba(5,166,122,.22)}.safe h2{margin:0 0 8px}.safe p{margin:0;color:#d9fff2}.page-footer{text-align:center;margin-top:22px;color:#8a96a8;font-size:.82rem}
    @media(max-width:850px){main{padding:18px 12px}.hero,.metric-grid,.category-grid,.domain-grid{grid-template-columns:1fr}.hero{padding:24px}.hero-kpi b{font-size:2.8rem}}
  </style>
</head>
<body>
  <main>
    <div class="top-access"><a class="panel-access" href="https://next.zerotrust.pcassi.ar" target="_blank" rel="noopener">Acceso al panel de ciberseguridad</a></div>
    <section class="hero">
      <div>
        <div class="brand"><img src="https://admin.zerotrust.pcassi.ar/ciberseguridad.png" alt="PCASSI"><strong>ZeroTrust DNS</strong></div>
        <h1>PCASSI está protegiendo tu red</h1>
        <p>Informe de la última semana · ${escapeHtml(report.clientName)}</p>
        <div class="period">Periodo: ${escapeHtml(formatReportDate(report.rangeStart))} — ${escapeHtml(formatReportDate(periodEnd))}</div>
      </div>
      <div class="hero-kpi"><b>${escapeHtml(formatNumber(totals.blocked))}</b><span>Amenazas bloqueadas</span></div>
    </section>
    <section class="metric-grid">
      <div class="metric"><i>◎</i><div><b>${escapeHtml(formatNumber(totals.queries))}</b><span>consultas DNS</span></div></div>
      <div class="metric"><i>●</i><div><b>${escapeHtml(formatNumber(totals.blocked))}</b><span>bloqueadas</span></div></div>
      <div class="metric"><i>✓</i><div><b>${escapeHtml(totals.rate)}%</b><span>tasa de protección</span></div></div>
    </section>
    <section class="panel summary"><h2>Resumen ejecutivo</h2><p>${escapeHtml(summary)}</p></section>
    <section class="panel">
      <h2>¿De qué te protegimos?</h2>
      <div class="category-grid">${categoryRows || "<p>Sin bloqueos relevantes en este periodo.</p>"}</div>
    </section>
    <section>
      <h2>Sitios que intentaron abrir en tu red</h2>
      <div class="domain-grid">${domainHtml || "<article class='domain-card'><h3>Sin dominios bloqueados relevantes</h3><p>Durante este periodo no hubo eventos destacados para explicar.</p></article>"}</div>
    </section>
    <section class="safe"><h2>Con PCASSI estás seguro</h2><p>Tu red está monitoreada las 24 horas. Estos bloqueos ocurrieron de forma automática, sin que tengas que hacer nada.</p></section>
    <footer class="page-footer">ZeroTrust Shield by PCASSI · Ciberseguridad · Informe generado el ${escapeHtml(generatedAt)} · Vence ${escapeHtml(expiresAt.slice(0, 16).replace("T", " "))}</footer>
  </main>
</body>
</html>`;
}

async function cleanupShares() {
  await mkdir(SHARE_DIR, { recursive: true });
  const now = Date.now();
  const files = await readdir(SHARE_DIR).catch(() => []);
  await Promise.all(files.filter((file) => file.endsWith(".json")).map(async (file) => {
    try {
      const meta = JSON.parse(await readFile(new URL(file, SHARE_DIR), "utf8"));
      if (Date.parse(meta.expiresAt) < now) {
        const token = file.replace(/\.json$/, "");
        await unlink(new URL(`${token}.json`, SHARE_DIR)).catch(() => {});
        await unlink(new URL(`${token}.html`, SHARE_DIR)).catch(() => {});
      }
    } catch {}
  }));
}

async function handleShareReport(req, res) {
  const body = await readJsonBody(req);
  const clientId = String(body.clientId || "").trim();
  if (!clientId) {
    res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "clientId requerido" }));
    return;
  }

  await cleanupShares();
  const report = await buildClientReportFromDatabase(clientId, 7);
  const summary = await generateReportSummary(report);
  const domainCards = await buildShareDomainCards(report);
  const token = randomUUID().replaceAll("-", "");
  const expiresAt = new Date(Date.now() + Number(process.env.SHARE_TTL_DAYS || 7) * 86400_000).toISOString();
  const html = buildShareHtml(report, summary, expiresAt, domainCards);
  await mkdir(SHARE_DIR, { recursive: true });
  await writeFile(new URL(`${token}.html`, SHARE_DIR), html);
  await writeFile(new URL(`${token}.json`, SHARE_DIR), JSON.stringify({ token, clientId, expiresAt }));
  const url = `${getPublicBaseUrl(req)}/api/share/${token}`;
  const text = `Reporte DNS de ${report.clientName}: ${url}`;
  res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
  res.end(JSON.stringify({ ok: true, url, whatsappUrl: `https://wa.me/?text=${encodeURIComponent(text)}`, expiresAt, summary }));
}

async function handleShareView(req, res) {
  const token = String(req.url.split("/").pop() || "");
  if (!/^[a-f0-9]{32}$/.test(token)) {
    res.writeHead(404, { "content-type": "text/plain; charset=utf-8" });
    res.end("Reporte no encontrado");
    return;
  }
  try {
    const meta = JSON.parse(await readFile(new URL(`${token}.json`, SHARE_DIR), "utf8"));
    if (Date.parse(meta.expiresAt) < Date.now()) throw new Error("expired");
    const html = await readFile(new URL(`${token}.html`, SHARE_DIR), "utf8");
    res.writeHead(200, { "content-type": "text/html; charset=utf-8", "cache-control": "no-store" });
    res.end(html);
  } catch {
    res.writeHead(404, { "content-type": "text/plain; charset=utf-8" });
    res.end("El reporte temporal venció o no existe.");
  }
}

function fallbackDomainExplanation(domain, category) {
  const d = String(domain || "").toLowerCase();
  const risk = category && category !== "Permitido" ? "medio" : "bajo";
  const known = d.includes("akamai") || d.includes("cloudfront") || d.includes("googleusercontent")
    ? "Parece ser infraestructura de CDN o contenido alojado por terceros."
    : d.includes("wechat") || d.includes("qpic")
      ? "Parece estar relacionado con servicios de mensajería o contenido multimedia externo."
      : "No hay una identificación precisa en cache; parece ser un dominio externo consultado por algún equipo o aplicación.";
  return {
    que_es: known,
    por_que: `Los servidores de ciberseguridad de PCASSI lo clasificaron dentro de ${category || "una categoría de protección"}.`,
    riesgo: risk,
    consejo: "Estás protegido. Si necesitás acceder a este recurso, validalo antes con soporte técnico."
  };
}

function isGenericDomainExplanation(row) {
  const text = `${row?.que_es || ""} ${row?.por_que || ""} ${row?.consejo || ""}`;
  return text.includes("No hay una identificación precisa en cache")
    || text.includes("parece ser un dominio externo consultado")
    || text.toLowerCase().includes("adguard");
}

async function handleDomainExplain(req, res) {
  const body = await readJsonBody(req);
  const domain = String(body.domain || "").trim().toLowerCase();
  const category = String(body.category || "").trim();
  if (!domain) {
    res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "domain requerido" }));
    return;
  }

  const pool = getDbPool();
  const [cached] = await pool.execute("SELECT domain, que_es, por_que, riesgo, consejo FROM domain_explanations WHERE domain = ? LIMIT 1", [domain]);
  const cachedExplanation = cached[0];
  if (cachedExplanation && (!isGenericDomainExplanation(cachedExplanation) || !getOpenAIKey())) {
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({
      ...cachedExplanation,
      cached: true,
      aiAvailable: Boolean(getOpenAIKey()),
      source: isGenericDomainExplanation(cachedExplanation) ? "fallback-cache" : "database"
    }));
    return;
  }

  const fallback = fallbackDomainExplanation(domain, category);
  const aiText = await askOpenAI({
    fallback: JSON.stringify(fallback),
    system: "Sos un analista DNS de PCASSI. Devolvé SOLO JSON válido con keys que_es, por_que, riesgo, consejo. Español simple, claro y útil. 1 frase corta por campo. riesgo: bajo, medio o alto. Nunca menciones AdGuard ni proveedores internos; usá 'ZeroTrust DNS' o 'los servidores de ciberseguridad de PCASSI'. Si reconocés marca, proveedor, CDN, tracking, malware, publicidad o phishing, nombralo.",
    user: `Explicá este dominio observado en un bloqueo DNS: ${domain}. Clasificación interna: ${category || "sin categoría"}. Estado: ${body.status || ""}. Filtro interno: ${body.filter || ""}. Tipo DNS: ${body.type || ""}.`
  });
  let explanation = fallback;
  try {
    explanation = { ...fallback, ...JSON.parse(aiText.replace(/^```json|```$/g, "").trim()) };
  } catch {}

  await pool.execute(
    `INSERT INTO domain_explanations (domain, que_es, por_que, riesgo, consejo)
     VALUES (?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE que_es=VALUES(que_es), por_que=VALUES(por_que), riesgo=VALUES(riesgo), consejo=VALUES(consejo)`,
    [domain, explanation.que_es, explanation.por_que, explanation.riesgo, explanation.consejo]
  );
  res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
  res.end(JSON.stringify({
    domain,
    ...explanation,
    cached: false,
    aiAvailable: Boolean(getOpenAIKey()),
    source: getOpenAIKey() ? "openai" : "fallback"
  }));
}

async function handleUserUpdate(req, res) {
  if (!hasDatabaseConfig()) {
    res.writeHead(503, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Base de datos no configurada" }));
    return;
  }

  const body = await readJsonBody(req);
  const id = Number(body.id);
  if (!id) {
    res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "ID de usuario requerido" }));
    return;
  }

  const username = String(body.username || "").trim();
  const displayName = String(body.display_name || body.name || "").trim();
  const email = String(body.email || "").trim() || null;
  const role = body.role === "admin" ? "admin" : "client";
  const active = body.active === false || body.active === 0 || body.active === "0" ? 0 : 1;
  const uploadedAvatar = await saveAvatarUpload(body.avatarUpload, id, req);
  const avatar = uploadedAvatar || String(body.avatar || "").trim() || null;
  const clientIds = Array.isArray(body.clientIds)
    ? body.clientIds.map((item) => String(item).trim()).filter(Boolean)
    : String(body.clientIds || "").split(",").map((item) => item.trim()).filter(Boolean);
  const password = String(body.password || "");

  if (!username || !displayName) {
    res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Usuario y nombre visible son requeridos" }));
    return;
  }

  const pool = getDbPool();
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    if (password.trim()) {
      const hash = await bcrypt.hash(password, 10);
      await connection.execute(
        `UPDATE users
         SET username = ?, display_name = ?, email = ?, role = ?, active = ?, avatar = ?, password = ?
         WHERE id = ?`,
        [username, displayName, email, role, active, avatar, hash, id]
      );
    } else {
      await connection.execute(
        `UPDATE users
         SET username = ?, display_name = ?, email = ?, role = ?, active = ?, avatar = ?
         WHERE id = ?`,
        [username, displayName, email, role, active, avatar, id]
      );
    }

    await connection.execute("DELETE FROM client_mapping WHERE user_id = ?", [id]);
    if (role === "client" || clientIds.length) {
      let nextMappingId = await getNextId(connection, "client_mapping");
      for (const clientId of clientIds) {
        await connection.execute(
          "INSERT INTO client_mapping (id, user_id, adguard_client_id, label) VALUES (?, ?, ?, ?)",
          [nextMappingId, id, clientId, displayName]
        );
        nextMappingId += 1;
      }
    }

    await connection.commit();
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ ok: true }));
  } catch (error) {
    await connection.rollback();
    res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: error.message }));
  } finally {
    connection.release();
  }
}

async function getNextId(connection, table) {
  const [[row]] = await connection.execute(`SELECT COALESCE(MAX(id), 0) + 1 nextId FROM ${table}`);
  return Number(row.nextId || 1);
}

async function handleUserCreate(req, res) {
  if (!hasDatabaseConfig()) {
    res.writeHead(503, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Base de datos no configurada" }));
    return;
  }

  const body = await readJsonBody(req);
  const username = String(body.username || "").trim();
  const displayName = String(body.display_name || body.name || "").trim();
  const email = String(body.email || "").trim() || null;
  const role = body.role === "admin" ? "admin" : "client";
  const active = body.active === false || body.active === 0 || body.active === "0" ? 0 : 1;
  const clientIds = Array.isArray(body.clientIds)
    ? body.clientIds.map((item) => String(item).trim()).filter(Boolean)
    : String(body.clientIds || "").split(",").map((item) => item.trim()).filter(Boolean);
  const password = String(body.password || "");

  if (!username || !displayName || !password.trim()) {
    res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Usuario, nombre visible y clave son requeridos" }));
    return;
  }

  const pool = getDbPool();
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const id = await getNextId(connection, "users");
    const hash = await bcrypt.hash(password, 10);
    const avatar = String(body.avatar || "").trim() || null;

    await connection.execute(
      `INSERT INTO users (id, username, password, display_name, email, role, active, avatar)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [id, username, hash, displayName, email, role, active, avatar]
    );

    const uploadedAvatar = await saveAvatarUpload(body.avatarUpload, id, req);
    if (uploadedAvatar) {
      await connection.execute("UPDATE users SET avatar = ? WHERE id = ?", [uploadedAvatar, id]);
    }

    if (role === "client" || clientIds.length) {
      let nextMappingId = await getNextId(connection, "client_mapping");
      for (const clientId of clientIds) {
        await connection.execute(
          "INSERT INTO client_mapping (id, user_id, adguard_client_id, label) VALUES (?, ?, ?, ?)",
          [nextMappingId, id, clientId, displayName]
        );
        nextMappingId += 1;
      }
    }

    await connection.commit();
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ ok: true, id }));
  } catch (error) {
    await connection.rollback();
    const message = error?.code === "ER_DUP_ENTRY" ? "Ya existe un usuario con ese login" : error.message;
    res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: message }));
  } finally {
    connection.release();
  }
}

async function handleUserDelete(req, res) {
  if (!hasDatabaseConfig()) {
    res.writeHead(503, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Base de datos no configurada" }));
    return;
  }

  const body = await readJsonBody(req);
  const id = Number(body.id);
  if (!id) {
    res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "ID de usuario requerido" }));
    return;
  }

  const pool = getDbPool();
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await connection.execute("DELETE FROM client_mapping WHERE user_id = ?", [id]);
    const [result] = await connection.execute("DELETE FROM users WHERE id = ?", [id]);
    await connection.commit();
    res.writeHead(result.affectedRows ? 200 : 404, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify(result.affectedRows ? { ok: true } : { error: "Usuario no encontrado" }));
  } catch (error) {
    await connection.rollback();
    res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: error.message }));
  } finally {
    connection.release();
  }
}

async function handleLogin(req, res) {
  const data = await readData();
  const body = await readJsonBody(req);
  const password = String(body.password || "");

  if (!password.trim()) {
    res.writeHead(401, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: "Clave requerida" }));
    return;
  }

  if (hasDatabaseConfig()) {
    const dbLogin = await loginWithDatabase(body, password);
    if (dbLogin?.error) {
      res.writeHead(401, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: dbLogin.error }));
      return;
    }
    if (dbLogin) {
      res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify(dbLogin));
      return;
    }
    res.writeHead(401, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ error: body.mode === "support" ? "Administrador no encontrado" : "Usuario o clave incorrectos" }));
    return;
  }

  if (body.mode === "support") {
    const adminName = normalize(body.adminName);
    const admin = (data.admins || []).find((item) => normalize(item.name) === adminName);
    if (!admin) {
      res.writeHead(401, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: "Administrador no encontrado" }));
      return;
    }
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify(publicSession({
      type: "admin",
      id: admin.id,
      name: admin.name,
      role: admin.role
    }, { authSource: "local-json" })));
    return;
  }

  if (body.mode === "client") {
    const username = normalize(body.username);
    const client = (data.clients || []).find((item) =>
      normalize(item.email) === username ||
      normalize(item.primaryClientId) === username ||
      normalize(item.name).includes(username)
    );
    if (!client) {
      res.writeHead(401, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: "Cliente no encontrado" }));
      return;
    }
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify(publicSession({
      type: "client",
      id: client.id,
      name: client.name,
      role: "Cliente",
      clientId: client.primaryClientId
    }, { authSource: "local-json" })));
    return;
  }

  res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
  res.end(JSON.stringify({ error: "Modo de login invalido" }));
}

const server = http.createServer(async (req, res) => {
  res.setHeader("Access-Control-Allow-Origin", getAllowedOrigin(req.headers.origin));
  res.setHeader("Vary", "Origin");
  res.setHeader("Access-Control-Allow-Headers", "content-type, authorization");
  res.setHeader("Access-Control-Allow-Methods", "GET,POST,OPTIONS");

  if (req.method === "OPTIONS") {
    res.writeHead(204);
    res.end();
    return;
  }

  if (req.url?.startsWith("/uploads/avatars/") && req.method === "GET") {
    await serveUploadedAvatar(req, res);
    return;
  }

  if (req.url === "/api/health") {
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ ok: true }));
    return;
  }

  if (req.url === "/api/auth/login" && req.method === "POST") {
    try {
      await handleLogin(req, res);
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url === "/api/auth/logout" && req.method === "POST") {
    res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
    res.end(JSON.stringify({ ok: true }));
    return;
  }

  if (req.url === "/api/auth/ping" && req.method === "POST") {
    try {
      const body = await readJson(req);
      const userId = Number(body.id || 0);
      if (userId) {
        const pool = getDbPool();
        await pool.execute("UPDATE users SET last_seen = NOW() WHERE id = ? AND active = 1", [userId]);
      }
      res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ ok: true }));
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url === "/api/users/update" && req.method === "POST") {
    try {
      await handleUserUpdate(req, res);
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url === "/api/users/create" && req.method === "POST") {
    try {
      await handleUserCreate(req, res);
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url === "/api/users/delete" && req.method === "POST") {
    try {
      await handleUserDelete(req, res);
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url === "/api/share-report" && req.method === "POST") {
    try {
      await handleShareReport(req, res);
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url?.startsWith("/api/share/") && req.method === "GET") {
    await handleShareView(req, res);
    return;
  }

  if (req.url === "/api/domain-explain" && req.method === "POST") {
    try {
      await handleDomainExplain(req, res);
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url === "/api/summary") {
    try {
      if (hasDatabaseConfig()) {
        const data = await buildSummaryFromDatabase();
        res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
        res.end(JSON.stringify(data));
        return;
      }
      const data = await readData();
      res.writeHead(data.ready === false ? 503 : 200, {
        "content-type": "application/json; charset=utf-8"
      });
      res.end(JSON.stringify(data));
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  if (req.url?.startsWith("/api/client-report") && req.method === "GET") {
    try {
      const url = new URL(req.url, `http://${req.headers.host || "localhost"}`);
      const clientId = url.searchParams.get("clientId");
      if (!clientId) {
        res.writeHead(400, { "content-type": "application/json; charset=utf-8" });
        res.end(JSON.stringify({ error: "clientId requerido" }));
        return;
      }
      if (hasDatabaseConfig()) {
        const days = Number(url.searchParams.get("days") || 1);
        const limit = Number(url.searchParams.get("limit") || (days <= 1 ? 5000 : 10000));
        const offset = Number(url.searchParams.get("offset") || 0);
        const report = await buildClientReportFromDatabase(clientId, days, limit, offset);
        res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
        res.end(JSON.stringify(report));
        return;
      }
      const data = await readData();
      res.writeHead(200, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify(data.clientReports?.[clientId] || { clientId, hourly: [], categories: [], topBlockedDomains: [], recentLogs: [] }));
    } catch (error) {
      res.writeHead(500, { "content-type": "application/json; charset=utf-8" });
      res.end(JSON.stringify({ error: error.message }));
    }
    return;
  }

  res.writeHead(404, { "content-type": "application/json; charset=utf-8" });
  res.end(JSON.stringify({ error: "Not found" }));
});

server.listen(PORT, HOST, () => {
  console.log(`ZeroTrust API listening on http://${HOST}:${PORT}`);
});
