arkantu-core-tools

/**
 * zoteroManager.ts — Gestor RAG de Zotero para arkantu-core-tools
 *
 * Basado en el enfoque de phuocnguyen90/omnimind pero usando better-sqlite3
 * en lugar de sql.js (más eficiente: no carga el DB de 255 MB en RAM).
 *
 * Lee el Zotero SQLite en modo READ-ONLY usando una copia temporal
 * para no interferir con Zotero abierto (WAL mode).
 *
 * Tablas usadas:
 *   items, itemData, itemDataValues, fields,
 *   itemCreators, creators, creatorTypes,
 *   itemAttachments, itemTags, tags, collections, collectionItems
 */

import { copyFile, mkdtemp, rm } from "fs/promises";
import { existsSync } from "fs";
import { join } from "path";
import * as os from "os";
import { truncate } from "./utils";

// ── Tipos ──────────────────────────────────────────────────────────────────
export interface ZoteroItem {
  id: number;
  key: string;
  title: string;
  authors: string;
  year: string;
  doi: string;
  abstract: string;
  tags: string;
  collections: string;
  pdf_path: string | null;  // ruta relativa al storage: "storage:KEY/file.pdf"
  storage_key: string | null;
}

export interface ZoteroSearchResult extends ZoteroItem {
  snippet?: string;  // fragmento del abstract relevante
}

// ── Abrir DB (copia temporal para no bloquear Zotero) ─────────────────────
async function openZoteroDB(dbPath: string): Promise<any> {
  // better-sqlite3 con modo inmutable (readonly sin locks)
  const Database = require("better-sqlite3");

  // Primero intentamos abrir directamente en modo readonly inmutable
  // (funciona si Zotero no tiene un lock exclusivo activo)
  try {
    const db = new Database(dbPath, { readonly: true, fileMustExist: true });
    db.pragma("query_only = ON");
    return { db, tmpDir: null };
  } catch {
    // Si falla por lock, hacemos una copia temporal
    const tmpDir = await mkdtemp(join(os.tmpdir(), "zotero-"));
    const tmpPath = join(tmpDir, "zotero.sqlite");
    await copyFile(dbPath, tmpPath);
    const db = new Database(tmpPath, { readonly: true, fileMustExist: true });
    db.pragma("query_only = ON");
    return { db, tmpDir };
  }
}

async function closeDB(db: any, tmpDir: string | null) {
  try { db.close(); } catch {}
  if (tmpDir) {
    await rm(tmpDir, { recursive: true, force: true }).catch(() => {});
  }
}

// ── Resolver field IDs dinámicamente ──────────────────────────────────────
function resolveFieldIds(db: any): Record<string, number> {
  const rows = db.prepare(
    `SELECT fieldName, fieldID FROM fields WHERE fieldName IN ('title','date','DOI','abstractNote','url','ISBN','publisher')`
  ).all() as Array<{ fieldName: string; fieldID: number }>;

  const fids: Record<string, number> = {};
  for (const r of rows) fids[r.fieldName] = r.fieldID;
  return fids;
}

function resolveAuthorTypeId(db: any): number {
  const row = db.prepare(
    `SELECT creatorTypeID FROM creatorTypes WHERE creatorType = 'author'`
  ).get() as { creatorTypeID: number } | undefined;
  return row?.creatorTypeID ?? 1;
}

// ── Query principal de papers ──────────────────────────────────────────────
function buildMainQuery(fids: Record<string, number>, authorTypeId: number) {
  const titleId   = fids["title"]        ?? -1;
  const dateId    = fids["date"]         ?? -1;
  const doiId     = fids["DOI"]          ?? -1;
  const abstractId = fids["abstractNote"] ?? -1;

  return `
    SELECT
        i.itemID    AS id,
        i.key       AS key,
        tv.value    AS title,
        GROUP_CONCAT(c.lastName || ', ' || COALESCE(c.firstName,''), '; ') AS authors,
        dv.value    AS year,
        doiv.value  AS doi,
        av.value    AS abstract,
        att.path    AS pdf_path,
        atti.key    AS storage_key,
        COALESCE(GROUP_CONCAT(DISTINCT t.name), '') AS tags,
        COALESCE(GROUP_CONCAT(DISTINCT col.collectionName), '') AS collections
    FROM items i
    LEFT JOIN itemData    td   ON td.itemID   = i.itemID AND td.fieldID   = ${titleId}
    LEFT JOIN itemDataValues tv ON tv.valueID = td.valueID
    LEFT JOIN itemData    dd   ON dd.itemID   = i.itemID AND dd.fieldID   = ${dateId}
    LEFT JOIN itemDataValues dv ON dv.valueID = dd.valueID
    LEFT JOIN itemData    doid ON doid.itemID = i.itemID AND doid.fieldID = ${doiId}
    LEFT JOIN itemDataValues doiv ON doiv.valueID = doid.valueID
    LEFT JOIN itemData    ad   ON ad.itemID   = i.itemID AND ad.fieldID   = ${abstractId}
    LEFT JOIN itemDataValues av ON av.valueID = ad.valueID
    LEFT JOIN itemCreators ic  ON ic.itemID   = i.itemID AND ic.creatorTypeID = ${authorTypeId}
    LEFT JOIN creators c       ON c.creatorID = ic.creatorID
    LEFT JOIN (
        SELECT parentItemID, MIN(itemID) AS itemID, path
        FROM itemAttachments
        WHERE contentType = 'application/pdf' AND path LIKE 'storage:%'
        GROUP BY parentItemID
    ) att ON att.parentItemID = i.itemID
    LEFT JOIN items atti ON atti.itemID = att.itemID
    LEFT JOIN itemTags it2 ON it2.itemID = i.itemID
    LEFT JOIN tags t ON t.tagID = it2.tagID
    LEFT JOIN collectionItems ci ON ci.itemID = i.itemID
    LEFT JOIN collections col ON col.collectionID = ci.collectionID
    WHERE i.itemTypeID NOT IN (14, 26)   -- excluir adjuntos y notas
      AND tv.value IS NOT NULL
    GROUP BY i.itemID
  `;
}

// ── Búsqueda por texto ─────────────────────────────────────────────────────
/**
 * Busca papers en Zotero por texto en título, autores, abstract, DOI o tags.
 * Hace BM25 básico ponderado: título > autores > abstract/tags.
 */
export async function searchZotero(
  dbPath: string,
  query: string,
  limit = 10
): Promise<ZoteroSearchResult[]> {
  const { db, tmpDir } = await openZoteroDB(dbPath);
  try {
    const fids = resolveFieldIds(db);
    const authorTypeId = resolveAuthorTypeId(db);
    const sql = buildMainQuery(fids, authorTypeId);

    const rows = db.prepare(sql).all() as ZoteroItem[];

    // Score cada resultado por relevancia al query
    const terms = query.toLowerCase().split(/\s+/).filter(t => t.length > 2);
    const scored = rows.map(r => {
      let score = 0;
      const title    = (r.title    ?? "").toLowerCase();
      const authors  = (r.authors  ?? "").toLowerCase();
      const abstract = (r.abstract ?? "").toLowerCase();
      const tags     = (r.tags     ?? "").toLowerCase();

      for (const term of terms) {
        if (title.includes(term))    score += 3;
        if (authors.includes(term))  score += 2;
        if (abstract.includes(term)) score += 1;
        if (tags.includes(term))     score += 1;
        if ((r.doi ?? "").toLowerCase().includes(term)) score += 3;
      }
      return { ...r, score };
    });

    return scored
      .filter(r => r.score > 0)
      .sort((a, b) => b.score - a.score)
      .slice(0, limit)
      .map(({ score, ...r }) => {
        // Extraer snippet relevante del abstract
        const abstract = r.abstract ?? "";
        let snippet = "";
        if (abstract && terms.length > 0) {
          const idx = terms
            .map(t => abstract.toLowerCase().indexOf(t))
            .filter(i => i >= 0)
            .sort((a, b) => a - b)[0] ?? 0;
          const start = Math.max(0, idx - 80);
          snippet = abstract.substring(start, start + 300);
          if (start > 0) snippet = "…" + snippet;
          if (start + 300 < abstract.length) snippet += "…";
        }
        return { ...r, snippet };
      });
  } finally {
    await closeDB(db, tmpDir);
  }
}

// ── Búsqueda por ID exacto o DOI ──────────────────────────────────────────
export async function getZoteroItem(
  dbPath: string,
  query: string
): Promise<ZoteroItem | null> {
  const { db, tmpDir } = await openZoteroDB(dbPath);
  try {
    const fids = resolveFieldIds(db);
    const authorTypeId = resolveAuthorTypeId(db);
    const sql = buildMainQuery(fids, authorTypeId);

    const rows = db.prepare(sql).all() as ZoteroItem[];
    const q = query.toLowerCase();

    return rows.find(r =>
      r.key?.toLowerCase() === q ||
      r.doi?.toLowerCase() === q ||
      r.title?.toLowerCase().includes(q)
    ) ?? null;
  } finally {
    await closeDB(db, tmpDir);
  }
}

// ── Estadísticas de la biblioteca ─────────────────────────────────────────
export async function getZoteroStats(dbPath: string): Promise<{
  total: number;
  with_pdf: number;
  collections: string[];
  tags_top: string[];
}> {
  const { db, tmpDir } = await openZoteroDB(dbPath);
  try {
    const total = (db.prepare(
      `SELECT COUNT(*) AS n FROM items WHERE itemTypeID NOT IN (14,26)`
    ).get() as any).n;

    const withPdf = (db.prepare(
      `SELECT COUNT(DISTINCT parentItemID) AS n FROM itemAttachments WHERE contentType='application/pdf' AND path LIKE 'storage:%'`
    ).get() as any).n;

    const cols = db.prepare(
      `SELECT collectionName FROM collections ORDER BY collectionName LIMIT 30`
    ).all() as Array<{ collectionName: string }>;

    const tags = db.prepare(
      `SELECT t.name, COUNT(*) AS n FROM tags t JOIN itemTags it ON it.tagID=t.tagID GROUP BY t.tagID ORDER BY n DESC LIMIT 20`
    ).all() as Array<{ name: string; n: number }>;

    return {
      total,
      with_pdf: withPdf,
      collections: cols.map(c => c.collectionName),
      tags_top: tags.map(t => `${t.name} (${t.n})`),
    };
  } finally {
    await closeDB(db, tmpDir);
  }
}

// ── Resolver ruta física del PDF ───────────────────────────────────────────
export function resolvePdfPath(storagePath: string, item: ZoteroItem): string | null {
  if (!item.pdf_path || !item.storage_key) return null;
  // pdf_path tiene formato "storage:FILENAME.pdf"
  const fileName = item.pdf_path.replace("storage:", "");
  return join(storagePath, item.storage_key, fileName);
}

// ── Leer PDF como texto plano (vía pdftotext si está disponible) ──────────
export async function readZoteroPdf(
  storagePath: string,
  item: ZoteroItem,
  maxChars = 8000
): Promise<string> {
  const pdfPath = resolvePdfPath(storagePath, item);
  if (!pdfPath) return "Este paper no tiene PDF adjunto en Zotero.";

  if (!existsSync(pdfPath)) {
    return `PDF no encontrado en disco: ${pdfPath}`;
  }

  // Intentar con pdftotext (poppler-utils)
  try {
    const { runShell } = await import("./utils");
    const result = await runShell(
      `pdftotext -l 15 "${pdfPath}" - 2>/dev/null || echo "PDFTOTEXT_NOT_AVAILABLE"`,
      os.tmpdir(),
      15000
    );
    if (result.stdout && !result.stdout.includes("PDFTOTEXT_NOT_AVAILABLE")) {
      return truncate(result.stdout.trim(), maxChars);
    }
  } catch {}

  // Fallback: devolver abstract si no hay pdftotext
  return `PDF disponible en: ${pdfPath}\n\nAbstract:\n${item.abstract ?? "(sin abstract)"}`;
}