// Внешние подключения к БД: реестр (admin) + read-only SQL-шлюз (finance.manage).
// Назначение: отчёты и виджеты (в т.ч. построенные ИИ-агентами через custom pages)
// читают внешние витрины данных без новых серверных эндпоинтов и без redeploy.
//
// Безопасность:
// - пароль шифруется AES-256-GCM, по API не отдаётся (только маска);
// - шлюз: только SELECT, без мульти-выражений, принудительный LIMIT (обёртка подзапросом),
//   statement_timeout 10 с; опциональный allowlist таблиц (пустой = все таблицы);
// - каждый запрос пишется в external_db_query_log (кто/когда/SQL/строки/мс);
// - основной рубеж — пользователь внешней БД с реальным GRANT SELECT ( defence in depth:
//   шлюз не заменяет права БД).

import type { Express, Response } from "express";
import { Router } from "express";
import { Pool, type PoolConfig } from "pg";
import { createCipheriv, createDecipheriv, randomBytes, createHash } from "crypto";
import { db } from "../db";
import { sql } from "drizzle-orm";
import { authenticateToken, requirePermission, type AuthenticatedRequest } from "../middleware/auth.middleware";

const MAX_ROWS = 5000;
const STATEMENT_TIMEOUT_MS = 10000;

function encryptionKey(): Buffer {
  const secret = process.env.EXTERNAL_DB_SECRET || process.env.API_KEY_HMAC_SECRET || "";
  if (!secret) {
    console.warn("[external-db] EXTERNAL_DB_SECRET не задан — используется небезопасный dev-ключ");
  }
  return createHash("sha256").update(secret || "iistwin-external-db-dev-key").digest();
}

function encryptPassword(plain: string): string {
  const iv = randomBytes(12);
  const cipher = createCipheriv("aes-256-gcm", encryptionKey(), iv);
  const enc = Buffer.concat([cipher.update(plain, "utf8"), cipher.final()]);
  const tag = cipher.getAuthTag();
  return `${iv.toString("base64")}.${tag.toString("base64")}.${enc.toString("base64")}`;
}

function decryptPassword(stored: string): string {
  const [ivB64, tagB64, dataB64] = stored.split(".");
  if (!ivB64 || !tagB64 || !dataB64) throw new Error("Некорректный формат зашифрованного пароля");
  const decipher = createDecipheriv("aes-256-gcm", encryptionKey(), Buffer.from(ivB64, "base64"));
  decipher.setAuthTag(Buffer.from(tagB64, "base64"));
  return Buffer.concat([decipher.update(Buffer.from(dataB64, "base64")), decipher.final()]).toString("utf8");
}

interface ConnRow {
  id: number;
  organization_id: number;
  name: string;
  host: string;
  port: number;
  database: string;
  username: string;
  password_encrypted: string;
  ssl: boolean;
  allowed_tables: string[];
  is_active: boolean;
  created_at: string;
  updated_at: string;
}

const pools = new Map<number, Pool>();

function getPool(conn: ConnRow): Pool {
  let pool = pools.get(conn.id);
  if (!pool) {
    const cfg: PoolConfig = {
      host: conn.host,
      port: conn.port,
      database: conn.database,
      user: conn.username,
      password: decryptPassword(conn.password_encrypted),
      ssl: conn.ssl ? { rejectUnauthorized: false } : false,
      max: 3,
      connectionTimeoutMillis: 8000,
      statement_timeout: STATEMENT_TIMEOUT_MS,
    } as PoolConfig;
    pool = new Pool(cfg);
    pool.on("error", (err) => console.error(`[external-db] pool ${conn.id} error:`, err.message));
    pools.set(conn.id, pool);
  }
  return pool;
}

function dropPool(connId: number): void {
  const pool = pools.get(connId);
  if (pool) {
    pools.delete(connId);
    pool.end().catch(() => {});
  }
}

// --- Валидация SQL для read-only шлюза ---

// Вырезаем строковые литералы, чтобы ключевые слова внутри них не ломали проверки
function stripStringLiterals(sqlText: string): string {
  return sqlText.replace(/'([^']|'')*'/g, "''");
}

function validateReadOnly(rawSql: unknown): string {
  if (typeof rawSql !== "string" || !rawSql.trim()) {
    throw new Error("SQL запрос не задан");
  }
  const cleaned = rawSql.trim().replace(/;+\s*$/, "");
  if (!/^select\b/i.test(cleaned)) {
    throw new Error("Только SELECT-запросы");
  }
  if (/;\s*\S/.test(cleaned)) {
    throw new Error("Не допускается несколько выражений в одном запросе");
  }
  const noStrings = stripStringLiterals(cleaned);
  const forbidden = /\b(insert|update|delete|drop|alter|truncate|grant|revoke|create|copy|call|execute|do|merge|vacuum|cluster|reindex|refresh)\b/i;
  if (forbidden.test(noStrings)) {
    throw new Error("Запрещённое ключевое слово в запросе");
  }
  return cleaned;
}

function extractTableNames(sqlText: string): string[] {
  const names = new Set<string>();
  const re = /\b(?:from|join)\s+([a-zA-Z_][\w$]*(?:\.[a-zA-Z_][\w$]*)?)/gi;
  let m: RegExpExecArray | null;
  while ((m = re.exec(sqlText)) !== null) {
    const parts = m[1].split(".");
    names.add(parts[parts.length - 1].toLowerCase());
  }
  return [...names];
}

function toPublicConn(row: ConnRow) {
  return {
    id: row.id,
    name: row.name,
    host: row.host,
    port: row.port,
    database: row.database,
    username: row.username,
    hasPassword: true,
    ssl: row.ssl,
    allowedTables: row.allowed_tables ?? [],
    isActive: row.is_active,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

export function registerExternalDbRoutes(app: Express): void {
  const r = Router();
  app.use("/api/external-db", r);

  // Управление подключениями — только admin приложения
  r.use("/connections", authenticateToken, requireAppAdmin);

  r.get("/connections", async (req: AuthenticatedRequest, res: Response) => {
    try {
      const orgId = req.user!.organizationId;
      const rows = await db.execute(sql`
        SELECT * FROM external_db_connections
        WHERE organization_id = ${orgId}
        ORDER BY id
      `);
      res.json((rows.rows as unknown as ConnRow[]).map(toPublicConn));
    } catch (err) {
      console.error(err);
      res.status(500).json({ error: "Ошибка базы данных" });
    }
  });

  r.post("/connections", async (req: AuthenticatedRequest, res: Response) => {
    try {
      const orgId = req.user!.organizationId;
      const { name, host, port, database, username, password, ssl, allowedTables } = req.body ?? {};
      if (!name || !host || !database || !username || !password) {
        return res.status(400).json({ error: "Обязательные поля: name, host, database, username, password" });
      }
      const tables = sanitizeTables(allowedTables);
      const inserted = await db.execute(sql`
        INSERT INTO external_db_connections
          (organization_id, name, host, port, database, username, password_encrypted, ssl, allowed_tables)
        VALUES
          (${orgId}, ${String(name)}, ${String(host)}, ${Number(port) || 5432}, ${String(database)},
           ${String(username)}, ${encryptPassword(String(password))}, ${!!ssl}, ${JSON.stringify(tables)}::jsonb)
        RETURNING *
      `);
      res.status(201).json(toPublicConn(inserted.rows[0] as unknown as ConnRow));
    } catch (err) {
      console.error(err);
      res.status(500).json({ error: "Ошибка базы данных" });
    }
  });

  r.patch("/connections/:id", async (req: AuthenticatedRequest, res: Response) => {
    try {
      const orgId = req.user!.organizationId;
      const id = parseInt(req.params.id, 10);
      const b = req.body ?? {};
      const existing = await findConn(orgId, id);
      if (!existing) return res.status(404).json({ error: "Подключение не найдено" });

      const sets: ReturnType<typeof sql>[] = [];
      if (b.name !== undefined) sets.push(sql`name = ${String(b.name)}`);
      if (b.host !== undefined) sets.push(sql`host = ${String(b.host)}`);
      if (b.port !== undefined) sets.push(sql`port = ${Number(b.port) || 5432}`);
      if (b.database !== undefined) sets.push(sql`database = ${String(b.database)}`);
      if (b.username !== undefined) sets.push(sql`username = ${String(b.username)}`);
      if (b.password) sets.push(sql`password_encrypted = ${encryptPassword(String(b.password))}`);
      if (b.ssl !== undefined) sets.push(sql`ssl = ${!!b.ssl}`);
      if (b.allowedTables !== undefined) sets.push(sql`allowed_tables = ${JSON.stringify(sanitizeTables(b.allowedTables))}::jsonb`);
      if (b.isActive !== undefined) sets.push(sql`is_active = ${!!b.isActive}`);
      if (sets.length === 0) return res.status(400).json({ error: "Нет полей для обновления" });

      const updated = await db.execute(sql`
        UPDATE external_db_connections
        SET ${sql.join(sets, sql`, `)}, updated_at = NOW()
        WHERE id = ${id} AND organization_id = ${orgId}
        RETURNING *
      `);
      dropPool(id);
      res.json(toPublicConn(updated.rows[0] as unknown as ConnRow));
    } catch (err) {
      console.error(err);
      res.status(500).json({ error: "Ошибка базы данных" });
    }
  });

  r.delete("/connections/:id", async (req: AuthenticatedRequest, res: Response) => {
    try {
      const orgId = req.user!.organizationId;
      const id = parseInt(req.params.id, 10);
      await db.execute(sql`
        DELETE FROM external_db_connections WHERE id = ${id} AND organization_id = ${orgId}
      `);
      dropPool(id);
      res.json({ success: true });
    } catch (err) {
      console.error(err);
      res.status(500).json({ error: "Ошибка базы данных" });
    }
  });

  r.post("/connections/:id/test", async (req: AuthenticatedRequest, res: Response) => {
    try {
      const orgId = req.user!.organizationId;
      const id = parseInt(req.params.id, 10);
      const conn = await findConn(orgId, id);
      if (!conn) return res.status(404).json({ error: "Подключение не найдено" });
      const started = Date.now();
      await getPool(conn).query("SELECT 1");
      res.json({ ok: true, durationMs: Date.now() - started });
    } catch (err: any) {
      res.json({ ok: false, error: err?.message || "Ошибка подключения" });
    }
  });

  // --- Read-only шлюз (finance.manage) ---
  r.post("/:id/query", authenticateToken, requirePermission("finance.manage"), async (req: AuthenticatedRequest, res: Response) => {
    const started = Date.now();
    const orgId = req.user!.organizationId;
    const id = parseInt(req.params.id, 10);
    const sqlText = req.body?.sql;
    let rowCount = 0;
    let errorText: string | null = null;
    let conn: ConnRow | null = null;
    try {
      conn = await findConn(orgId, id);
      if (!conn) return res.status(404).json({ error: "Подключение не найдено" });
      if (!conn.is_active) return res.status(403).json({ error: "Подключение отключено" });

      const cleaned = validateReadOnly(sqlText);
      const allowed = (conn.allowed_tables ?? []).map((t) => String(t).toLowerCase());
      // Пустой список = доступ ко ВСЕМ таблицам (ограничение — опционально;
      // основной рубеж — права самого пользователя внешней БД, GRANT SELECT).
      if (allowed.length > 0) {
        const usedTables = extractTableNames(stripStringLiterals(cleaned));
        const denied = usedTables.filter((t) => !allowed.includes(t));
        if (denied.length > 0) {
          return res.status(403).json({ error: `Таблицы не в allowlist: ${denied.join(", ")}` });
        }
      }

      const wrapped = `SELECT * FROM (${cleaned}) AS __gateway_q LIMIT ${MAX_ROWS}`;
      const result = await getPool(conn).query(wrapped);
      rowCount = result.rowCount ?? result.rows.length;
      res.json({ columns: result.fields.map((f) => f.name), rows: result.rows, rowCount, truncated: rowCount >= MAX_ROWS });
    } catch (err: any) {
      errorText = err?.message || String(err);
      if (!res.headersSent) {
        res.status(400).json({ error: errorText });
      }
    } finally {
      if (conn) {
        const botId = (req as any).bot?.id ?? null;
        db.execute(sql`
          INSERT INTO external_db_query_log
            (organization_id, connection_id, user_id, bot_id, sql_text, row_count, duration_ms, error)
          VALUES
            (${orgId}, ${id}, ${req.user!.id}, ${botId},
             ${String(sqlText ?? "").slice(0, 4000)}, ${rowCount}, ${Date.now() - started}, ${errorText})
        `).catch((e) => console.error("[external-db] audit log error:", e.message));
      }
    }
  });

  async function findConn(orgId: number, id: number): Promise<ConnRow | null> {
    const rows = await db.execute(sql`
      SELECT * FROM external_db_connections WHERE id = ${id} AND organization_id = ${orgId}
    `);
    return (rows.rows[0] as unknown as ConnRow) ?? null;
  }
}

function sanitizeTables(input: unknown): string[] {
  if (!Array.isArray(input)) return [];
  return [...new Set(input.map((t) => String(t).trim().toLowerCase()).filter(Boolean))];
}

function requireAppAdmin(req: AuthenticatedRequest, res: Response, next: () => void): void {
  if (req.user?.appRole !== "admin") {
    res.status(403).json({ error: "Требуется роль администратора" });
    return;
  }
  next();
}