osint_obsidian_plugin/main.ts

// Plugin de Obsidian "OSINT DB". Plugin fino: no embebe ninguna base de datos,
// habla HTTP con el service local osint_db (FastAPI + DuckDB) y renderiza los
// resultados como tablas dentro de las notas mediante el code block `osintdb`.
import {
  App,
  Editor,
  Plugin,
  PluginSettingTab,
  Setting,
  requestUrl,
} from "obsidian";
import { parseBlock, ParsedBlock } from "./parse";

interface OsintDbSettings {
  /** URL base del service osint_db. */
  baseUrl: string;
  /** Límite de filas por defecto cuando el bloque no declara max_rows. */
  maxRows: number;
}

const DEFAULT_SETTINGS: OsintDbSettings = {
  baseUrl: "http://127.0.0.1:8771",
  maxRows: 500,
};

/** Respuesta de /api/query y /api/query/named. */
interface QueryResponse {
  status: string;
  error?: string;
  columns?: string[];
  rows?: Record<string, unknown>[];
  row_count?: number;
  truncated?: boolean;
}

const SNIPPET = [
  "```osintdb",
  "title: Mi consulta",
  "max_rows: 100",
  "SELECT * FROM main.personas LIMIT 20;",
  "```",
  "",
].join("\n");

export default class OsintDbPlugin extends Plugin {
  settings: OsintDbSettings = DEFAULT_SETTINGS;

  async onload(): Promise<void> {
    await this.loadSettings();
    this.addSettingTab(new OsintDbSettingTab(this.app, this));

    this.registerMarkdownCodeBlockProcessor("osintdb", (source, el) => {
      const block = parseBlock(source);
      const container = el.createDiv({ cls: "osintdb-block" });
      void this.renderBlock(container, block);
    });

    this.addCommand({
      id: "insert-query-block",
      name: "Insertar bloque de query",
      editorCallback: (editor: Editor) => {
        editor.replaceSelection(SNIPPET);
      },
    });
  }

  async loadSettings(): Promise<void> {
    this.settings = Object.assign({}, DEFAULT_SETTINGS, await this.loadData());
  }

  async saveSettings(): Promise<void> {
    await this.saveData(this.settings);
  }

  /** Ejecuta la query del bloque contra el service y pinta el resultado. */
  private async renderBlock(container: HTMLElement, block: ParsedBlock): Promise<void> {
    container.empty();

    if (block.error) {
      this.renderError(container, block.error);
      return;
    }

    const header = container.createDiv({ cls: "osintdb-header" });
    header.createEl("span", {
      cls: "osintdb-title",
      text: block.title ?? (block.query ? `query: ${block.query}` : "SQL"),
    });
    const refreshBtn = header.createEl("button", {
      cls: "osintdb-refresh",
      text: "Refrescar",
      attr: { type: "button", "aria-label": "Re-ejecutar la query" },
    });
    refreshBtn.addEventListener("click", () => {
      void this.renderBlock(container, block);
    });

    const body = container.createDiv({ cls: "osintdb-body" });
    body.createEl("div", { cls: "osintdb-loading", text: "Consultando osint_db..." });

    const maxRows = block.maxRows ?? this.settings.maxRows;
    let data: QueryResponse;
    try {
      data = await this.runQuery(block, maxRows);
    } catch (e) {
      body.empty();
      this.renderUnreachable(body);
      return;
    }

    body.empty();
    if (data.status !== "ok") {
      this.renderError(body, data.error ?? "El service devolvió un error sin mensaje.");
      return;
    }
    this.renderTable(body, data);
  }

  /** Llama a /api/query o /api/query/named según la forma del bloque. */
  private async runQuery(block: ParsedBlock, maxRows: number): Promise<QueryResponse> {
    const base = this.settings.baseUrl.replace(/\/+$/, "");
    const isNamed = block.query !== undefined;
    const url = isNamed ? `${base}/api/query/named` : `${base}/api/query`;
    const payload = isNamed
      ? { name: block.query, max_rows: maxRows }
      : { sql: block.sql, params: [], max_rows: maxRows };

    // requestUrl (API de Obsidian) evita CORS; nunca usar fetch aquí.
    const res = await requestUrl({
      url,
      method: "POST",
      contentType: "application/json",
      body: JSON.stringify(payload),
      throw: false,
    });
    return res.json as QueryResponse;
  }

  private renderTable(parent: HTMLElement, data: QueryResponse): void {
    const columns = data.columns ?? [];
    const rows = data.rows ?? [];

    if (columns.length === 0) {
      parent.createEl("div", { cls: "osintdb-empty", text: "La query no devolvió columnas." });
      return;
    }

    const wrapper = parent.createDiv({ cls: "osintdb-table-wrapper" });
    const table = wrapper.createEl("table", { cls: "osintdb-table" });
    const thead = table.createEl("thead");
    const headRow = thead.createEl("tr");
    for (const col of columns) {
      headRow.createEl("th", { text: col });
    }

    const tbody = table.createEl("tbody");
    for (const row of rows) {
      const tr = tbody.createEl("tr");
      for (const col of columns) {
        const td = tr.createEl("td");
        this.renderCell(td, col, row[col]);
      }
    }

    const count = data.row_count ?? rows.length;
    const suffix = data.truncated ? " (truncado)" : "";
    parent.createEl("div", {
      cls: "osintdb-footer",
      text: `${count} fila${count === 1 ? "" : "s"}${suffix}`,
    });
  }

  /**
   * Pinta una celda. Los valores se insertan siempre como texto (createEl usa
   * textContent por debajo), nunca como HTML. Las columnas note_path con valor
   * terminado en .md se renderizan como enlace interno al estilo Obsidian.
   */
  private renderCell(td: HTMLElement, column: string, value: unknown): void {
    if (value === null || value === undefined) {
      td.createEl("span", { cls: "osintdb-null", text: "∅" });
      return;
    }
    const text = typeof value === "object" ? JSON.stringify(value) : String(value);

    if (column === "note_path" && text.endsWith(".md")) {
      const link = td.createEl("a", { cls: "internal-link", text });
      link.addEventListener("click", (ev) => {
        ev.preventDefault();
        void this.app.workspace.openLinkText(text.slice(0, -3), "");
      });
      return;
    }
    td.setText(text);
  }

  private renderError(parent: HTMLElement, message: string): void {
    const box = parent.createDiv({ cls: "osintdb-error" });
    box.createEl("strong", { text: "Error osint_db" });
    box.createEl("div", { text: message });
  }

  private renderUnreachable(parent: HTMLElement): void {
    const box = parent.createDiv({ cls: "osintdb-error" });
    box.createEl("strong", { text: `osint_db no responde en ${this.settings.baseUrl}` });
    box.createEl("div", {
      text: "Arranca el service local (ver projects/osint/apps/osint_db/app.md), por ejemplo:",
    });
    box.createEl("code", {
      text: "cd ~/fn_registry/projects/osint/apps/osint_db && .venv/bin/python -m uvicorn main:app --port 8771",
    });
  }
}

class OsintDbSettingTab extends PluginSettingTab {
  plugin: OsintDbPlugin;

  constructor(app: App, plugin: OsintDbPlugin) {
    super(app, plugin);
    this.plugin = plugin;
  }

  display(): void {
    const { containerEl } = this;
    containerEl.empty();

    new Setting(containerEl)
      .setName("URL base del service osint_db")
      .setDesc("Dirección HTTP donde escucha el service local (FastAPI + DuckDB).")
      .addText((text) =>
        text
          .setPlaceholder(DEFAULT_SETTINGS.baseUrl)
          .setValue(this.plugin.settings.baseUrl)
          .onChange(async (value) => {
            this.plugin.settings.baseUrl = value.trim() || DEFAULT_SETTINGS.baseUrl;
            await this.plugin.saveSettings();
          })
      );

    new Setting(containerEl)
      .setName("max_rows por defecto")
      .setDesc("Límite de filas cuando el bloque no declara la directiva max_rows.")
      .addText((text) =>
        text
          .setPlaceholder(String(DEFAULT_SETTINGS.maxRows))
          .setValue(String(this.plugin.settings.maxRows))
          .onChange(async (value) => {
            const n = Number(value);
            if (Number.isInteger(n) && n > 0) {
              this.plugin.settings.maxRows = n;
              await this.plugin.saveSettings();
            }
          })
      );
  }
}