draw-things-index

/**
 * Draw Things Project File Parser (.sqlite3)
 * 
 * Parses Draw Things project files (SQLite databases with FlatBuffers blobs).
 * Each project contains multiple generations with full metadata.
 * 
 * Structure:
 * - tensorhistorynode: FlatBuffers TensorHistoryNode per generated image
 *   - __pk0 = Canvas/Layer index
 *   - __pk1 = History version (1 = generated)
 *   - p = TensorHistoryNode BLOB
 * - thumbnailhistorynode: Generated images (PNG/JPEG blobs)
 * - texthistorynode: Prompt history
 * 
 * ⚠️ FlatBuffers types duplicated from draw-things-chat/src/interfaces
 * ⚠️ Uses sql.js (WASM) instead of better-sqlite3 to avoid code-signing issues
 */

import initSqlJs, { type Database, type SqlJsStatic } from 'sql.js';
import { existsSync, readFileSync, statSync, unlinkSync } from 'fs';
import { execFileSync } from 'child_process';
import { tmpdir } from 'os';
import { join } from 'path';
import * as flatbuffers from 'flatbuffers';
import { SamplerType, TensorHistoryNode, ThumbnailHistoryNode, TextHistoryNode, TextType } from '../../flatbuffers/index.js';
import type { GenerationMetadata } from '../../types.js';

// Singleton SQL.js instance
let SQL: SqlJsStatic | null = null;

async function getSqlJs(): Promise<SqlJsStatic> {
  if (!SQL) {
    SQL = await initSqlJs();
  }
  return SQL;
}

/**
 * Open a SQLite database with WAL-awareness.
 * sql.js reads only the main .sqlite3 file and ignores -wal/-shm sidecar files.
 * When a WAL file exists, we use the native `sqlite3` CLI to create a clean
 * temporary copy via VACUUM INTO, then load that instead.
 */
async function openDatabase(filePath: string): Promise<{ db: Database; tempFile?: string }> {
  const sqlJs = await getSqlJs();
  const walPath = filePath + '-wal';

  if (existsSync(walPath)) {
    const tempFile = join(tmpdir(), `dt-index-${Date.now()}-${Math.random().toString(36).slice(2, 8)}.sqlite3`);
    try {
      execFileSync('sqlite3', [filePath, `VACUUM INTO '${tempFile.replace(/'/g, "''")}'`], { timeout: 30_000 });
      const buffer = readFileSync(tempFile);
      const db = new sqlJs.Database(buffer);
      return { db, tempFile };
    } catch (e) {
      // Fallback: read main file without WAL data
      try { unlinkSync(tempFile); } catch { /* ignore */ }
      console.warn(`[ProjectParser] WAL merge failed for ${filePath}, reading main file only:`, e);
    }
  }

  const buffer = readFileSync(filePath);
  return { db: new sqlJs.Database(buffer) };
}

function cleanupTempFile(tempFile?: string) {
  if (tempFile) {
    try { unlinkSync(tempFile); } catch { /* ignore */ }
  }
}

// ═══════════════════════════════════════════════════════════════
// Types
// ═══════════════════════════════════════════════════════════════

/**
 * Raw row from tensorhistorynode table
 */
interface TensorHistoryRow {
  __pk0: number;  // Canvas/Layer index
  __pk1: number;  // History version
  p: Buffer;      // FlatBuffers GenerationConfiguration blob
}

/**
 * Raw row from thumbnailhistorynode table
 */
interface ThumbnailRow {
  __pk0: number;  // Unique ID
  p: Buffer;      // Image data (PNG/JPEG)
}

type PromptCandidate = {
  prompt: string;
  negativePrompt?: string;
  score: number;
};

type TextPromptRow = {
  lineage: number;
  logicalTime: number;
  prompt: string;
  negativePrompt?: string;
  score: number;
};

type TextEdit = {
  type: TextType;
  location: number;
  length: number;
  text: string;
};

type DecodedTextHistory = {
  lineage: number;
  logicalTime: number;
  startEdits: number;
  startPositiveText: string;
  startNegativeText: string;
  modifications: TextEdit[];
};

type DecodedGenerationConfig = {
  seed?: number;
  steps?: number;
  cfgScale?: number;
  width?: number;
  height?: number;
  sampler?: string;
  model?: string;
};

type DecodedTensorHistory = DecodedGenerationConfig & {
  prompt?: string;
  negativePrompt?: string;
  loras?: string[];
  previewId?: number;
  logicalTime?: number;
  textLineage?: number;
  textEdits?: number;
  inferenceTimeMs?: number;
  clipId?: bigint;
  indexInAClip?: number;
  numFrames?: number;
};

/**
 * Parsed generation from a project file
 */
export interface ProjectGeneration {
  canvasIndex: number;
  historyVersion: number;
  metadata: GenerationMetadata;
  thumbnailId?: number;
}

// ═══════════════════════════════════════════════════════════════
// Helpers
// ═══════════════════════════════════════════════════════════════

function promptLooksTaggy(s: string): boolean {
  const t = s.toLowerCase();
  return (
    t.includes('best quality') ||
    t.includes('high quality') ||
    t.includes('trending on artstation') ||
    t.includes('award-winning') ||
    t.includes('cgsociety')
  );
}

function scorePromptCandidate(s: string): number {
  const t = s.trim();
  if (t.length < 10) return -1;
  if (t.startsWith('{') || t.startsWith('[')) return -1;
  if (/\.(ckpt|safetensors|gguf|pt|pth)$/i.test(t)) return -1;
  if (/(?:^|\s)lora/i.test(t) && /\.(ckpt|safetensors)$/i.test(t)) return -1;
  if (/^\w+:\/\//.test(t)) return -1;
  if (/^\/[A-Za-z0-9_\-./]+$/.test(t)) return -1;

  // Base score: length
  let score = t.length;

  // Lexical diversity reward (helps prefer descriptive prompts over short tag lists)
  const words = t
    .toLowerCase()
    .split(/[^a-z0-9]+/)
    .filter(Boolean);
  const uniqueWords = new Set(words);
  score += uniqueWords.size * 3;

  // Sentence-like punctuation reward
  if (t.includes('.')) score += 10;
  if (t.includes("'")) score += 5;

  // Penalize tag-heavy lists (often not the actual prompt field)
  const commaCount = t.split(',').length - 1;
  if (commaCount >= 6) score -= 25;
  if (promptLooksTaggy(t)) score -= 80;
  if (/timings|duration|profile/i.test(t)) score -= 30;
  if (/[{}]/.test(t)) score -= 20;

  return score;
}

function extractBestPromptFromBlob(blob: Buffer): PromptCandidate | null {
  const strings = extractStringsFromBlob(blob);
  if (!strings.length) return null;

  // Deduplicate while preserving best score
  const byString = new Map<string, number>();
  for (const s of strings) {
    const t = s.trim();
    if (!t) continue;
    const score = scorePromptCandidate(t);
    if (score < 0) continue;
    const prev = byString.get(t);
    if (prev == null || score > prev) byString.set(t, score);
  }

  const ranked = [...byString.entries()]
    .map(([s, score]) => ({ s, score }))
    .sort((a, b) => b.score - a.score);

  if (!ranked.length) return null;
  const prompt = ranked[0].s;

  const looksNegative = (s: string): boolean =>
    /[\u4e00-\u9fff]|worst quality|low quality|bad|ugly|blurry|deformed|jpeg artifacts/i.test(s);

  const negativePrompt = ranked
    .slice(1)
    .filter((r) => r.s !== prompt && looksNegative(r.s))
    .sort((a, b) => b.score - a.score)[0]?.s;

  return {
    prompt,
    negativePrompt,
    score: ranked[0].score,
  };
}

function samplerToString(sampler: SamplerType): string {
  const names: Record<number, string> = {
    [SamplerType.DPMPP2MKarras]: 'DPM++ 2M Karras',
    [SamplerType.EulerA]: 'Euler A',
    [SamplerType.DDIM]: 'DDIM',
    [SamplerType.PLMS]: 'PLMS',
    [SamplerType.DPMPPSDEKarras]: 'DPM++ SDE Karras',
    [SamplerType.UniPC]: 'UniPC',
    [SamplerType.LCM]: 'LCM',
    [SamplerType.EulerASubstep]: 'Euler A Substep',
    [SamplerType.DPMPPSDESubstep]: 'DPM++ SDE Substep',
    [SamplerType.TCD]: 'TCD',
    [SamplerType.EulerATrailing]: 'Euler A Trailing',
    [SamplerType.DPMPPSDETrailing]: 'DPM++ SDE Trailing',
    [SamplerType.DPMPP2MAYS]: 'DPM++ 2M AYS',
    [SamplerType.EulerAAYS]: 'Euler A AYS',
    [SamplerType.DPMPPSDEAYS]: 'DPM++ SDE AYS',
    [SamplerType.DPMPP2MTrailing]: 'DPM++ 2M Trailing',
    [SamplerType.DDIMTrailing]: 'DDIM Trailing',
    [SamplerType.UniPCTrailing]: 'UniPC Trailing',
    [SamplerType.UniPCAYS]: 'UniPC AYS',
    [SamplerType.TCDTrailing]: 'TCD Trailing',
  };
  return names[sampler] ?? `Unknown(${sampler})`;
}

function bigintToSafeNumber(v: bigint): number | undefined {
  const n = Number(v);
  if (!Number.isFinite(n)) return undefined;
  if (BigInt(Math.trunc(n)) !== v) return undefined;
  return n;
}

function coerceFlatbuffersString(v: unknown): string | undefined {
  if (typeof v === 'string') return v;
  if (v instanceof Uint8Array) {
    try {
      return Buffer.from(v).toString('utf8');
    } catch {
      return undefined;
    }
  }
  return undefined;
}

function decodeTextHistoryNodeFromBlob(blob: Buffer): DecodedTextHistory | null {
  const u8 = new Uint8Array(blob.buffer, blob.byteOffset, blob.byteLength);
  for (const kind of ['root', 'size'] as const) {
    try {
      const bb = new flatbuffers.ByteBuffer(u8);
      const node =
        kind === 'size'
          ? TextHistoryNode.getSizePrefixedRootAsTextHistoryNode(bb)
          : TextHistoryNode.getRootAsTextHistoryNode(bb);

      const lineage = bigintToSafeNumber(node.lineage());
      const logicalTime = bigintToSafeNumber(node.logicalTime());
      const startEdits = bigintToSafeNumber(node.startEdits()) ?? 0;
      if (lineage == null || logicalTime == null) return null;

      const startPositiveText = coerceFlatbuffersString(node.startPositiveText()) ?? '';
      const startNegativeText = coerceFlatbuffersString(node.startNegativeText()) ?? '';

      const modifications: TextEdit[] = [];
      for (let i = 0; i < node.modificationsLength(); i++) {
        const mod = node.modifications(i);
        if (!mod) continue;
        const range = mod.range();
        const text = coerceFlatbuffersString(mod.text()) ?? '';
        const location = range?.location() ?? 0;
        const length = range?.length() ?? 0;
        modifications.push({
          type: mod.type(),
          location,
          length,
          text,
        });
      }

      return {
        lineage,
        logicalTime,
        startEdits,
        startPositiveText,
        startNegativeText,
        modifications,
      };
    } catch {
      // ignore
    }
  }
  return null;
}

function applyTextEdit(base: string, edit: TextEdit): string {
  const loc = Math.max(0, Math.min(base.length, edit.location));
  const end = Math.max(loc, Math.min(base.length, loc + Math.max(0, edit.length)));
  return base.slice(0, loc) + edit.text + base.slice(end);
}

function replayTextHistory(
  history: DecodedTextHistory,
  upToEdits?: number,
): { prompt: string; negativePrompt: string } {
  let prompt = history.startPositiveText ?? '';
  let negativePrompt = history.startNegativeText ?? '';

  let toApply = history.modifications.length;
  if (upToEdits != null && Number.isFinite(upToEdits)) {
    const remaining = upToEdits - history.startEdits;
    if (remaining <= 0) toApply = 0;
    else toApply = Math.min(history.modifications.length, remaining);
  }

  for (let i = 0; i < toApply; i++) {
    const edit = history.modifications[i];
    if (edit.type === TextType.PositiveText) {
      prompt = applyTextEdit(prompt, edit);
    } else {
      negativePrompt = applyTextEdit(negativePrompt, edit);
    }
  }

  return { prompt, negativePrompt };
}

function decodeTensorHistoryNodeFromBlob(blob: Buffer): DecodedTensorHistory | null {
  const u8 = new Uint8Array(blob.buffer, blob.byteOffset, blob.byteLength);
  for (const kind of ['root', 'size'] as const) {
    try {
      const bb = new flatbuffers.ByteBuffer(u8);
      const node =
        kind === 'size'
          ? TensorHistoryNode.getSizePrefixedRootAsTensorHistoryNode(bb)
          : TensorHistoryNode.getRootAsTensorHistoryNode(bb);

      const previewId = bigintToSafeNumber(node.previewId());
      const logicalTime = bigintToSafeNumber(node.logicalTime());
      const textLineage = bigintToSafeNumber(node.textLineage());
      const textEdits = bigintToSafeNumber(node.textEdits());

      const loras: string[] = [];
      for (let i = 0; i < node.lorasLength(); i++) {
        const loraFile = coerceFlatbuffersString(node.loras(i)?.file());
        if (loraFile?.trim()) loras.push(loraFile);
      }

      const model = coerceFlatbuffersString(node.model()) ?? undefined;
      const prompt = coerceFlatbuffersString(node.textPrompt()) ?? undefined;
      const negativePrompt = coerceFlatbuffersString(node.negativeTextPrompt()) ?? undefined;

      let width = node.startWidth() || node.targetImageWidth() || undefined;
      let height = node.startHeight() || node.targetImageHeight() || undefined;

      // Some Draw Things project blobs appear to store dimensions in 64px tile units
      // (e.g. 12×16 corresponds to 768×1024). Normalize to pixel units.
      if (
        width != null &&
        height != null &&
        Number.isFinite(width) &&
        Number.isFinite(height) &&
        width > 0 &&
        height > 0 &&
        width <= 64 &&
        height <= 64
      ) {
        width = width * 64;
        height = height * 64;
      }
      const generationTimeSec = node.generationTime();
      const inferenceTimeMs = Number.isFinite(generationTimeSec) && generationTimeSec > 0
        ? Math.round(generationTimeSec * 1000)
        : undefined;

      const clipId = node.clipId();
      const indexInAClip = node.indexInAClip();
      const numFrames = node.numFrames();

      return {
        seed: node.seed(),
        steps: node.steps(),
        cfgScale: node.guidanceScale(),
        width,
        height,
        sampler: samplerToString(node.sampler()),
        model: model?.trim() ? model : undefined,
        prompt: prompt?.trim() ? prompt : undefined,
        negativePrompt: negativePrompt?.trim() ? negativePrompt : undefined,
        loras: loras.length ? loras : undefined,
        previewId,
        logicalTime,
        textLineage,
        textEdits,
        inferenceTimeMs,
        clipId,
        indexInAClip,
        numFrames,
      };
    } catch {
      // ignore
    }
  }
  return null;
}

function pickTextPromptForGeneration(
  textByLineage: Map<number, TextPromptRow[]>,
  generationLogicalTime: number,
): PromptCandidate | null {
  if (textByLineage.size === 0) return null;

  // Prefer lineage 0 if present; else pick the lineage with most entries.
  const lineage = textByLineage.has(0)
    ? 0
    : [...textByLineage.entries()].sort((a, b) => b[1].length - a[1].length)[0][0];

  const rows = textByLineage.get(lineage) ?? [];
  if (!rows.length) return null;

  const candidate = [...rows]
    .filter((r) => r.logicalTime <= generationLogicalTime)
    .sort((a, b) => b.logicalTime - a.logicalTime)[0];

  const chosen = candidate ?? rows[rows.length - 1];
  return { prompt: chosen.prompt, negativePrompt: chosen.negativePrompt, score: chosen.score };
}

function pickTextHistoryForGeneration(
  historyByLineage: Map<number, DecodedTextHistory[]>,
  lineage: number,
  generationLogicalTime: number,
): DecodedTextHistory | null {
  const rows = historyByLineage.get(lineage) ?? [];
  if (!rows.length) return null;
  const chosen = [...rows]
    .filter((r) => r.logicalTime <= generationLogicalTime)
    .sort((a, b) => b.logicalTime - a.logicalTime)[0];
  return chosen ?? rows[rows.length - 1];
}

/**
 * Parse a FlatBuffers blob to extract generation metadata
 * 
 * The blob structure in tensorhistorynode is a wrapper around GenerationConfiguration.
 * Since we don't have the wrapper schema, we extract strings directly from the blob.
 * 
 * Known string patterns in the blob:
 * - Prompt (positive prompt for the image)
 * - Negative prompt (prefixed with common phrases)
 * - Model filename (ends with .ckpt, .safetensors, etc.)
 * - LoRA filenames
 * - Profile JSON (timing information)
 */
function parseGenerationConfigBlob(blob: Buffer, projectFile: string, canvasIndex: number): GenerationMetadata | null {
  try {
    // Extract all null-terminated strings from the blob
    const strings = extractStringsFromBlob(blob);
    
    if (strings.length === 0) {
      return null;
    }
    
    const decoded = decodeTensorHistoryNodeFromBlob(blob);

    // Find model (filename ending in .ckpt or .safetensors)
    const modelPattern = /\.(ckpt|safetensors|gguf)$/i;
    const loraPattern = /lora.*\.(ckpt|safetensors)/i;
    
    let model = 'unknown';
    const loras: string[] = [];
    let profileJson: string | undefined;
    
    for (const str of strings) {
      // Skip very short strings
      if (str.length < 3) continue;
      
      // Profile JSON
      if (str.startsWith('{"') && str.includes('timings')) {
        profileJson = str;
        continue;
      }
      
      // Model file
      if (modelPattern.test(str) && !loraPattern.test(str)) {
        model = str;
        continue;
      }
      
      // LoRA file
      if (loraPattern.test(str)) {
        loras.push(str);
        continue;
      }
    }

    // If FlatBuffers decode found a plausible model, prefer it.
    if (decoded?.model && modelPattern.test(decoded.model)) model = decoded.model;
    // Prefer decoded loras when available.
    if (decoded?.loras?.length) {
      for (const l of decoded.loras) loras.push(l);
    }
    
    // Parse profile for timing info (prefer tensor_history.generation_time when available)
    let inferenceTimeMs: number | undefined = decoded?.inferenceTimeMs;
    if (profileJson) {
      try {
        const profile = JSON.parse(profileJson);
        if (profile.duration) {
          inferenceTimeMs = inferenceTimeMs ?? Math.round(profile.duration * 1000);
        }
      } catch {
        // Ignore JSON parse errors
      }
    }
    
    const metadata: GenerationMetadata = {
      // Prefer decoded prompts; caller may still override with better lineage reconstruction.
      prompt: decoded?.prompt ?? '',
      negativePrompt: decoded?.negativePrompt,
      model,
      loras: loras.length > 0 ? [...new Set(loras)] : undefined,
      sampler: decoded?.sampler,
      steps: decoded?.steps,
      cfgScale: decoded?.cfgScale,
      seed: decoded?.seed,
      width: decoded?.width,
      height: decoded?.height,
      inferenceTimeMs,
      sourceInfo: {
        type: 'draw_things_project',
        projectFile,
      },
    };
    
    return metadata;
  } catch (error) {
    console.error(`[ProjectParser] Failed to parse FlatBuffers blob:`, error);
    return null;
  }
}

/**
 * Extract null-terminated strings from a binary buffer
 * Returns strings longer than 3 characters
 */
function extractStringsFromBlob(blob: Buffer): string[] {
  const strings: string[] = [];
  let currentString = '';
  
  for (let i = 0; i < blob.length; i++) {
    const byte = blob[i];
    
    // Printable ASCII range (including extended for UTF-8 start bytes)
    if (byte >= 32 && byte < 127) {
      currentString += String.fromCharCode(byte);
    } else if (byte >= 0xC0 && byte <= 0xF7) {
      // UTF-8 multi-byte sequence start - try to decode
      let utf8Bytes = [byte];
      let expectedBytes = byte < 0xE0 ? 2 : byte < 0xF0 ? 3 : 4;
      
      for (let j = 1; j < expectedBytes && i + j < blob.length; j++) {
        const nextByte = blob[i + j];
        if ((nextByte & 0xC0) === 0x80) {
          utf8Bytes.push(nextByte);
        } else {
          break;
        }
      }
      
      if (utf8Bytes.length === expectedBytes) {
        try {
          const decoded = Buffer.from(utf8Bytes).toString('utf-8');
          currentString += decoded;
          i += expectedBytes - 1;
        } catch {
          // Not valid UTF-8, end current string
          if (currentString.length >= 3) {
            strings.push(currentString);
          }
          currentString = '';
        }
      }
    } else {
      // Non-printable byte - end current string
      if (currentString.length >= 3) {
        strings.push(currentString);
      }
      currentString = '';
    }
  }
  
  // Don't forget the last string
  if (currentString.length >= 3) {
    strings.push(currentString);
  }
  
  return strings;
}

// ═══════════════════════════════════════════════════════════════
// Main Parser
// ═══════════════════════════════════════════════════════════════

/**
 * Parse a Draw Things project file (.sqlite3)
 * Returns all generations found in the project
 */
export async function parseDrawThingsProject(filePath: string): Promise<ProjectGeneration[]> {
  const results: ProjectGeneration[] = [];
  
  let db: Database | null = null;
  let tempFile: string | undefined;
  try {
    ({ db, tempFile } = await openDatabase(filePath));
    
    // Check if tensorhistorynode table exists
    const tableCheck = db.exec(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='tensorhistorynode'"
    );
    
    if (!tableCheck.length || !tableCheck[0].values.length) {
      console.log(`[ProjectParser] No tensorhistorynode table in ${filePath}`);
      return results;
    }
    
    // Query all generation configs (pk1 >= 1 = all generated versions including variants)
    const result = db.exec(
      "SELECT __pk0, __pk1, p FROM tensorhistorynode WHERE __pk1 >= 1 ORDER BY __pk0, __pk1"
    );
    
    if (!result.length) return results;
    
    type SqlValue = number | string | Uint8Array | null;
    const rows = result[0].values.map((row: SqlValue[]) => ({
      __pk0: row[0] as number,
      __pk1: row[1] as number,
      p: Buffer.from(row[2] as Uint8Array)
    }));
    
    console.log(`[ProjectParser] Found ${rows.length} generations in ${filePath}`);
    
    for (const row of rows) {
      const metadata = parseGenerationConfigBlob(row.p, filePath, row.__pk0);
      if (metadata) {
        results.push({
          canvasIndex: row.__pk0,
          historyVersion: row.__pk1,
          metadata,
        });
      }
    }
    
  } catch (error) {
    console.error(`[ProjectParser] Failed to open/parse ${filePath}:`, error);
  } finally {
    db?.close();
    cleanupTempFile(tempFile);
  }
  
  return results;
}

/**
 * Parse multiple Draw Things project files (metadata only, NO thumbnails)
 * Thumbnails are extracted on-demand via getThumbnailForProject()
 */
export async function parseDrawThingsProjects(filePaths: string[]): Promise<GenerationMetadata[]> {
  const results: GenerationMetadata[] = [];
  
  for (const filePath of filePaths) {
    const generations = await parseDrawThingsProjectMetadataOnly(filePath);
    results.push(...generations);
  }
  
  return results;
}

/**
 * Parse a project for metadata ONLY (no thumbnail extraction)
 * This is fast and safe for large collections.
 * 
 * To get thumbnails, use getThumbnailForProject() on-demand.
 */
export async function parseDrawThingsProjectMetadataOnly(filePath: string): Promise<GenerationMetadata[]> {
  const results: GenerationMetadata[] = [];
  
  // Get file modification time for timestamp
  let timestamp: string | undefined;
  try {
    const stats = statSync(filePath);
    timestamp = stats.mtime.toLocaleString('de-DE', { timeZone: 'Europe/Berlin' });
  } catch {
    // Ignore stat errors
  }
  
  let db: Database | null = null;
  let tempFile: string | undefined;
  try {
    ({ db, tempFile } = await openDatabase(filePath));
    
    // Check if tables exist
    const tensorCheck = db.exec(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='tensorhistorynode'"
    );
    
    if (!tensorCheck.length || !tensorCheck[0].values.length) {
      return results;
    }
    
    // ═══════════════════════════════════════════════════════════════
    // STEP 1: Load prompts from texthistorynode (deterministic via TextHistoryNode)
    // We select the latest text logical_time <= generation logical_time, then replay edits up to text_edits.
    // ═══════════════════════════════════════════════════════════════
    const textByLineage = new Map<number, TextPromptRow[]>();
    const textHistoryByLineage = new Map<number, DecodedTextHistory[]>();
    const textResult = db.exec('SELECT __pk0, __pk1, p FROM texthistorynode ORDER BY __pk0, __pk1');
    if (textResult.length && textResult[0].values.length) {
      for (const row of textResult[0].values) {
        const blob = Buffer.from(row[2] as Uint8Array);
        const decoded = decodeTextHistoryNodeFromBlob(blob);
        if (!decoded) continue;

        const list = textHistoryByLineage.get(decoded.lineage) ?? [];
        list.push(decoded);
        textHistoryByLineage.set(decoded.lineage, list);

        // Also compute a "final" prompt snapshot for fallback scoring.
        const final = replayTextHistory(decoded);
        const prompt = final.prompt.trim();
        if (!prompt) continue;
        const score = scorePromptCandidate(prompt);
        if (score < 0) continue;
        const neg = final.negativePrompt.trim();

        const snapList = textByLineage.get(decoded.lineage) ?? [];
        snapList.push({
          lineage: decoded.lineage,
          logicalTime: decoded.logicalTime,
          prompt,
          negativePrompt: neg || undefined,
          score,
        });
        textByLineage.set(decoded.lineage, snapList);
      }
    }
    
    // ═══════════════════════════════════════════════════════════════
    // STEP 2: Load generation configs from tensorhistorynode
    // ═══════════════════════════════════════════════════════════════
    const result = db.exec(
      "SELECT rowid, __pk0, __pk1, p FROM tensorhistorynode WHERE __pk1 >= 1 ORDER BY __pk0, __pk1"
    );
    
    if (!result.length) return results;
    
    // Get thumbnail IDs from linking table (tensorhistorynode__f86.f86 = thumbnailhistorynode.__pk0)
    const thumbnailLinkResult = db.exec(
      "SELECT rowid, f86 FROM tensorhistorynode__f86"
    );
    const thumbnailIdMap = new Map<number, number>();
    if (thumbnailLinkResult.length) {
      for (const row of thumbnailLinkResult[0].values) {
        thumbnailIdMap.set(row[0] as number, row[1] as number);
      }
    }
    
    type SqlValue = number | string | Uint8Array | null;
    const generationRows = result[0].values.map((row: SqlValue[]) => ({
      rowid: row[0] as number,
      __pk0: row[1] as number,
      __pk1: row[2] as number,
      p: Buffer.from(row[3] as Uint8Array)
    }));

    // Track video clips: only emit the last frame (highest indexInAClip) per clip
    const pendingVideoClips = new Map<bigint, { metadata: GenerationMetadata; indexInAClip: number }>();
    
    for (const row of generationRows) {
      const metadata = parseGenerationConfigBlob(row.p, filePath, row.__pk0);
      
      if (metadata) {
        const decoded = decodeTensorHistoryNodeFromBlob(row.p);

        // ── Prompt Resolution ────────────────────────────────────
        // texthistorynode is THE authoritative source for user prompts.
        // An empty prompt is VALID (upscaling, img2img).
        // The textPrompt field in TensorHistoryNode may contain
        // encoder-specific text (openClipGText), NOT the user prompt.
        // NO heuristics, NO scoring — text history is the single source of truth.

        const generationLogicalTime = decoded?.logicalTime ?? 0;
        let promptResolved = false;

        // pk1=1 rows are the initial canvas state (imported images), not generation events.
        // Do not inherit text history from later generations on the same canvas lineage.
        const isCanvasTemplate = row.__pk1 === 1;

        // Skip imported/canvas images that were never actually generated.
        // Draw Things only writes generation_time > 0 when the denoiser ran.
        // Entries with inferenceTimeMs === undefined are init images, drag-and-drop
        // imports, or empty canvas layers — not user-visible generations.
        if (decoded?.inferenceTimeMs === undefined) continue;

        // (1) Deterministic edit-replay from TextHistoryNode
        if (!isCanvasTemplate && decoded?.textLineage != null && decoded?.textEdits != null) {
          const history = pickTextHistoryForGeneration(
            textHistoryByLineage, decoded.textLineage, generationLogicalTime
          );
          if (history) {
            const rebuilt = replayTextHistory(history, decoded.textEdits);
            metadata.prompt = rebuilt.prompt;
            metadata.negativePrompt = rebuilt.negativePrompt || metadata.negativePrompt;
            promptResolved = true;
          }
        }

        // (2) Snapshot from TextHistoryNode (no edit replay available)
        if (!isCanvasTemplate && !promptResolved) {
          const textCand = pickTextPromptForGeneration(textByLineage, generationLogicalTime);
          if (textCand) {
            metadata.prompt = textCand.prompt;
            if (textCand.negativePrompt) metadata.negativePrompt = textCand.negativePrompt;
            promptResolved = true;
          }
        }

        // (3) No text history at all — keep whatever parseGenerationConfigBlob set
        //     (decoded textPrompt from TensorHistoryNode). This is the only case
        //     where we accept the FlatBuffer field, since there's no text history
        //     to contradict it.
        
        metadata.timestamp = timestamp;
        // Store thumbnail ID for later retrieval (linked via tensorhistorynode__f86)
        // Format: project:///path/to/file.sqlite3#thumbnailId
        const thumbnailId = decoded?.previewId ?? thumbnailIdMap.get(row.rowid);
        if (thumbnailId !== undefined) metadata.imagePaths = [`project://${filePath}#${thumbnailId}`];

        // Deduplicate video clips: only keep the last frame per clip
        const clipId = decoded?.clipId;
        if (clipId !== undefined && clipId !== -1n) {
          const indexInAClip = decoded?.indexInAClip ?? 0;
          if (decoded?.numFrames && decoded.numFrames > 1) metadata.numFrames = decoded.numFrames;
          const existing = pendingVideoClips.get(clipId);
          if (!existing || indexInAClip > existing.indexInAClip) {
            pendingVideoClips.set(clipId, { metadata, indexInAClip });
          }
        } else {
          results.push(metadata);
        }
      }
    }

    // Emit the last frame of each video clip as a single indexed entry
    for (const clip of pendingVideoClips.values()) {
      results.push(clip.metadata);
    }
    
  } catch (error) {
    console.error(`[ProjectParser] Failed to parse ${filePath}:`, error);
  } finally {
    db?.close();
    cleanupTempFile(tempFile);
  }
  
  return results;
}

/**
 * Extract prompts from texthistorynode blob (FlatBuffers)
 * The blob contains positive and negative prompts as strings
 */
// NOTE: old extractPromptsFromTextBlob() removed in favor of extractBestPromptFromBlob().

/**
 * Extract a single thumbnail on-demand from a project file
 * Call this only when the user wants to VIEW a specific result.
 * 
 * @param projectPath - Path to the .sqlite3 project file
 * @param thumbnailId - The thumbnail ID from tensorhistorynode__f86 (stored in imagePaths)
 * @returns JPEG buffer or null if not found
 */
export async function getThumbnailForProject(projectPath: string, thumbnailId: number): Promise<Buffer | null> {
  let db: Database | null = null;
  let tempFile: string | undefined;
  try {
    ({ db, tempFile } = await openDatabase(projectPath));
    
    // Get thumbnail by its unique ID (__pk0)
    const result = db.exec(
      `SELECT p FROM thumbnailhistorynode WHERE __pk0 = ${thumbnailId}`
    );
    
    if (result.length && result[0].values.length) {
      const blob = Buffer.from(result[0].values[0][0] as Uint8Array);
      return decodeThumbnailFromBlob(blob) ?? extractImageFromThumbnailBlob(blob);
    }
    
  } catch (error) {
    console.error(`[ProjectParser] Failed to get thumbnail from ${projectPath}:`, error);
  } finally {
    db?.close();
    cleanupTempFile(tempFile);
  }
  
  return null;
}

/**
 * Parse a project:// URI to extract project path and thumbnail ID
 * @param uri - URI in format "project:///path/to/file.sqlite3#thumbnailId"
 * @returns { projectPath, thumbnailId } or null if invalid
 */
export function parseProjectUri(uri: string): { projectPath: string; thumbnailId: number } | null {
  if (!uri.startsWith('project://')) {
    return null;
  }
  
  const withoutScheme = uri.slice('project://'.length);
  const hashIndex = withoutScheme.lastIndexOf('#');
  
  if (hashIndex === -1) {
    return null;
  }
  
  const projectPath = withoutScheme.slice(0, hashIndex);
  const thumbnailId = parseInt(withoutScheme.slice(hashIndex + 1), 10);
  
  if (isNaN(thumbnailId)) {
    return null;
  }
  
  return { projectPath, thumbnailId };
}

/**
 * Get thumbnail image data for a specific thumbnail ID from a project
 * Returns the raw image buffer (PNG/JPEG)
 */
export async function getProjectThumbnail(filePath: string, thumbnailId: number): Promise<Buffer | null> {
  let db: Database | null = null;
  let tempFile: string | undefined;
  try {
    ({ db, tempFile } = await openDatabase(filePath));
    
    // Thumbnails are stored with unique ID (__pk0)
    // The ID comes from tensorhistorynode__f86.f86
    
    const result = db.exec(
      `SELECT __pk0, p FROM thumbnailhistorynode WHERE __pk0 = ${thumbnailId}`
    );
    
    if (result.length && result[0].values.length) {
      return Buffer.from(result[0].values[0][1] as Uint8Array);
    }
    
  } catch (error) {
    console.error(`[ProjectParser] Failed to get thumbnail from ${filePath}:`, error);
  } finally {
    db?.close();
    cleanupTempFile(tempFile);
  }
  
  return null;
}

/**
 * Get all thumbnails from a project file
 * Returns array of { id, buffer } pairs
 * 
 * Note: Thumbnails are wrapped in a FlatBuffers structure.
 * The actual image data (JPEG) starts after a header.
 */
export async function getAllProjectThumbnails(filePath: string): Promise<Array<{ id: number; buffer: Buffer }>> {
  const results: Array<{ id: number; buffer: Buffer }> = [];
  
  let db: Database | null = null;
  let tempFile: string | undefined;
  try {
    ({ db, tempFile } = await openDatabase(filePath));
    
    const tableCheck = db.exec(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='thumbnailhistorynode'"
    );
    
    if (!tableCheck.length || !tableCheck[0].values.length) {
      return results;
    }
    
    const result = db.exec(
      "SELECT __pk0, p FROM thumbnailhistorynode"
    );
    
    if (!result.length) return results;
    
    for (const row of result[0].values) {
      const blob = Buffer.from(row[1] as Uint8Array);
      // The blob is a FlatBuffers ThumbnailHistoryNode.
      const imageBuffer = decodeThumbnailFromBlob(blob) ?? extractImageFromThumbnailBlob(blob);
      if (imageBuffer) {
        results.push({
          id: row[0] as number,
          buffer: imageBuffer,
        });
      }
    }
    
  } catch (error) {
    console.error(`[ProjectParser] Failed to get thumbnails from ${filePath}:`, error);
  } finally {
    db?.close();
    cleanupTempFile(tempFile);
  }
  
  return results;
}

/**
 * Extract the actual image data from a thumbnail blob
 * The blob has a FlatBuffers wrapper around the JPEG/PNG data
 */
function extractImageFromThumbnailBlob(blob: Buffer): Buffer | null {
  // Look for JPEG header (FFD8FF)
  const jpegMarker = Buffer.from([0xFF, 0xD8, 0xFF]);
  let jpegStart = blob.indexOf(jpegMarker);
  
  if (jpegStart !== -1) {
    // Find JPEG end marker (FFD9)
    const jpegEnd = blob.lastIndexOf(Buffer.from([0xFF, 0xD9]));
    if (jpegEnd !== -1 && jpegEnd > jpegStart) {
      return blob.slice(jpegStart, jpegEnd + 2);
    }
    // No end marker found, return from start to end
    return blob.slice(jpegStart);
  }
  
  // Look for PNG header (89504E47)
  const pngMarker = Buffer.from([0x89, 0x50, 0x4E, 0x47]);
  let pngStart = blob.indexOf(pngMarker);
  
  if (pngStart !== -1) {
    // PNG end marker is IEND chunk
    const iendMarker = Buffer.from([0x49, 0x45, 0x4E, 0x44, 0xAE, 0x42, 0x60, 0x82]);
    const pngEnd = blob.lastIndexOf(iendMarker);
    if (pngEnd !== -1 && pngEnd > pngStart) {
      return blob.slice(pngStart, pngEnd + 8);
    }
    return blob.slice(pngStart);
  }
  
  // No image found
  return null;
}

function decodeThumbnailFromBlob(blob: Buffer): Buffer | null {
  const u8 = new Uint8Array(blob.buffer, blob.byteOffset, blob.byteLength);
  for (const kind of ['root', 'size'] as const) {
    try {
      const bb = new flatbuffers.ByteBuffer(u8);
      const node =
        kind === 'size'
          ? ThumbnailHistoryNode.getSizePrefixedRootAsThumbnailHistoryNode(bb)
          : ThumbnailHistoryNode.getRootAsThumbnailHistoryNode(bb);
      const data = node.dataArray();
      if (data && data.length) return Buffer.from(data);
    } catch {
      // ignore
    }
  }
  return null;
}