queries.ts

import { SqliteDatabase } from './sqlite-adapter';
import type {
  AssetKind,
  AssetRecord,
  GraphStats,
  RelationKind,
  SearchResult,
} from '../types';
import { ASSET_KINDS, emptyKindCounts } from '../types';

function rowToAsset(row: Record<string, unknown>): AssetRecord {
  return {
    id: row.id as number,
    kind: row.kind as AssetKind,
    name: row.name as string,
    qualifiedName: row.qualified_name as string,
    sourceFile: row.source_file as string,
    definitionJson: row.definition_json as string,
    metadataJson: row.metadata_json as string,
  };
}

export class QueryBuilder {
  constructor(private db: SqliteDatabase) {}

  upsertAsset(asset: Omit<AssetRecord, 'id'>): number {
    const result = this.db.prepare(`
      INSERT INTO assets (kind, name, qualified_name, source_file, definition_json, metadata_json)
      VALUES (?, ?, ?, ?, ?, ?)
      ON CONFLICT(kind, qualified_name, source_file) DO UPDATE SET
        name = excluded.name,
        definition_json = excluded.definition_json,
        metadata_json = excluded.metadata_json
    `).run(
      asset.kind,
      asset.name,
      asset.qualifiedName,
      asset.sourceFile,
      asset.definitionJson,
      asset.metadataJson,
    );
    const id = Number(result.lastInsertRowid);
    if (id > 0) return id;
    const row = this.db.prepare(
      'SELECT id FROM assets WHERE kind = ? AND qualified_name = ? AND source_file = ?',
    ).get(asset.kind, asset.qualifiedName, asset.sourceFile) as { id: number };
    return row.id;
  }

  addRelation(relation: RelationKind, fromId: number, toId: number): void {
    this.db.prepare(`
      INSERT INTO relations (relation, from_id, to_id)
      SELECT ?, ?, ?
      WHERE NOT EXISTS (
        SELECT 1 FROM relations WHERE relation = ? AND from_id = ? AND to_id = ?
      )
    `).run(relation, fromId, toId, relation, fromId, toId);
  }

  upsertFile(filePath: string, hash: string, plugin?: string): void {
    this.db.prepare(`
      INSERT INTO files (path, hash, indexed_at, plugin)
      VALUES (?, ?, ?, ?)
      ON CONFLICT(path) DO UPDATE SET hash = excluded.hash, indexed_at = excluded.indexed_at, plugin = excluded.plugin
    `).run(filePath, hash, Date.now(), plugin ?? null);
  }

  getFileHash(filePath: string): string | null {
    const row = this.db.prepare('SELECT hash FROM files WHERE path = ?').get(filePath) as { hash: string } | undefined;
    return row?.hash ?? null;
  }

  getFileEntry(filePath: string): { hash: string; plugin: string | null } | null {
    const row = this.db.prepare('SELECT hash, plugin FROM files WHERE path = ?').get(filePath) as
      { hash: string; plugin: string | null } | undefined;
    if (!row) return null;
    return { hash: row.hash, plugin: row.plugin ?? null };
  }

  listFiles(): Array<{ path: string; plugin: string | null; indexedAt: number }> {
    const rows = this.db.prepare('SELECT path, plugin, indexed_at FROM files ORDER BY path').all() as Record<string, unknown>[];
    return rows.map((r) => ({
      path: r.path as string,
      plugin: (r.plugin as string | null) ?? null,
      indexedAt: r.indexed_at as number,
    }));
  }

  clearSourceFile(sourceFile: string): void {
    const assetIds = this.db.prepare('SELECT id FROM assets WHERE source_file = ?').all(sourceFile) as { id: number }[];
    for (const { id } of assetIds) {
      this.db.prepare('DELETE FROM relations WHERE from_id = ? OR to_id = ?').run(id, id);
    }
    this.db.prepare('DELETE FROM assets WHERE source_file = ?').run(sourceFile);
    this.db.prepare('DELETE FROM files WHERE path = ?').run(sourceFile);
  }

  setMetadata(key: string, value: string): void {
    this.db.prepare(`
      INSERT INTO metadata (key, value) VALUES (?, ?)
      ON CONFLICT(key) DO UPDATE SET value = excluded.value
    `).run(key, value);
  }

  getMetadata(key: string): string | null {
    const row = this.db.prepare('SELECT value FROM metadata WHERE key = ?').get(key) as { value: string } | undefined;
    return row?.value ?? null;
  }

  search(query: string, limit = 10, kind?: AssetKind): SearchResult[] {
    const pattern = `%${query}%`;
    const sql = kind
      ? `SELECT * FROM assets WHERE kind = ? AND (name LIKE ? OR qualified_name LIKE ? OR metadata_json LIKE ?) LIMIT ?`
      : `SELECT * FROM assets WHERE name LIKE ? OR qualified_name LIKE ? OR metadata_json LIKE ? LIMIT ?`;
    const rows = kind
      ? this.db.prepare(sql).all(kind, pattern, pattern, pattern, limit)
      : this.db.prepare(sql).all(pattern, pattern, pattern, limit);
    return (rows as Record<string, unknown>[]).map((row) => {
      const asset = rowToAsset(row);
      let detail = '';
      try {
        const meta = JSON.parse(asset.metadataJson) as Record<string, unknown>;
        detail = typeof meta.summary === 'string' ? meta.summary : asset.kind;
      } catch {
        detail = asset.kind;
      }
      return {
        kind: asset.kind,
        id: asset.id,
        name: asset.name,
        qualifiedName: asset.qualifiedName,
        detail,
        sourceFile: asset.sourceFile,
      };
    });
  }

  getAssetById(id: number): AssetRecord | null {
    const row = this.db.prepare('SELECT * FROM assets WHERE id = ?').get(id) as Record<string, unknown> | undefined;
    return row ? rowToAsset(row) : null;
  }

  findAssets(kind: AssetKind, nameOrQualified: string, limit = 20): AssetRecord[] {
    const pattern = `%${nameOrQualified}%`;
    const rows = this.db.prepare(`
      SELECT * FROM assets
      WHERE kind = ? AND (name = ? OR qualified_name = ? OR name LIKE ? OR qualified_name LIKE ?)
      LIMIT ?
    `).all(kind, nameOrQualified, nameOrQualified, pattern, pattern, limit) as Record<string, unknown>[];
    return rows.map(rowToAsset);
  }

  getDependencies(assetId: number): Array<{ relation: RelationKind; asset: AssetRecord }> {
    const rows = this.db.prepare(`
      SELECT r.relation, a.*
      FROM relations r
      JOIN assets a ON a.id = r.to_id
      WHERE r.from_id = ?
    `).all(assetId) as Record<string, unknown>[];
    return rows.map((row) => ({
      relation: row.relation as RelationKind,
      asset: rowToAsset(row),
    }));
  }

  getConsumers(assetId: number): Array<{ relation: RelationKind; asset: AssetRecord }> {
    const rows = this.db.prepare(`
      SELECT r.relation, a.*
      FROM relations r
      JOIN assets a ON a.id = r.from_id
      WHERE r.to_id = ?
    `).all(assetId) as Record<string, unknown>[];
    return rows.map((row) => ({
      relation: row.relation as RelationKind,
      asset: rowToAsset(row),
    }));
  }

  getImpact(assetId: number, depth = 2): AssetRecord[] {
    const visited = new Set<number>();
    const result: AssetRecord[] = [];
    const queue: Array<{ id: number; d: number }> = [{ id: assetId, d: 0 }];

    while (queue.length > 0) {
      const item = queue.shift();
      if (!item || visited.has(item.id)) continue;
      visited.add(item.id);
      if (item.d > 0) {
        const asset = this.getAssetById(item.id);
        if (asset) result.push(asset);
      }
      if (item.d >= depth) continue;
      for (const { asset: consumer } of this.getConsumers(item.id)) {
        if (!visited.has(consumer.id)) queue.push({ id: consumer.id, d: item.d + 1 });
      }
      for (const { asset: dep } of this.getDependencies(item.id)) {
        if (!visited.has(dep.id)) queue.push({ id: dep.id, d: item.d + 1 });
      }
    }
    return result;
  }

  getStats(): GraphStats {
    const byKind = emptyKindCounts();
    for (const kind of ASSET_KINDS) {
      const row = this.db.prepare('SELECT COUNT(*) as c FROM assets WHERE kind = ?').get(kind) as { c: number };
      byKind[kind] = row.c;
    }
    const totalAssets = Object.values(byKind).reduce((a, b) => a + b, 0);
    const relationCount = (this.db.prepare('SELECT COUNT(*) as c FROM relations').get() as { c: number }).c;
    const fileCount = (this.db.prepare('SELECT COUNT(*) as c FROM files').get() as { c: number }).c;
    const indexStatus = this.getMetadata('index_status') === 'ready' ? 'ready' : 'stub';
    return { totalAssets, byKind, relationCount, fileCount, indexStatus };
  }

  listAssets(opts: { limit?: number; kind?: AssetKind; offset?: number } = {}): AssetRecord[] {
    const limit = opts.limit ?? 50;
    const offset = opts.offset ?? 0;
    const rows = opts.kind
      ? this.db.prepare(
        'SELECT * FROM assets WHERE kind = ? ORDER BY id LIMIT ? OFFSET ?',
      ).all(opts.kind, limit, offset)
      : this.db.prepare(
        'SELECT * FROM assets ORDER BY id LIMIT ? OFFSET ?',
      ).all(limit, offset);
    return (rows as Record<string, unknown>[]).map(rowToAsset);
  }

  countAssetsByKind(kind: AssetKind): number {
    const row = this.db.prepare(
      'SELECT COUNT(*) as c FROM assets WHERE kind = ?',
    ).get(kind) as { c: number };
    return row.c;
  }

  findAssetByQualified(
    kind: AssetKind,
    qualifiedName: string,
    sourceFile?: string,
  ): AssetRecord | null {
    const row = sourceFile
      ? this.db.prepare(
        'SELECT * FROM assets WHERE kind = ? AND qualified_name = ? AND source_file = ?',
      ).get(kind, qualifiedName, sourceFile)
      : this.db.prepare(
        'SELECT * FROM assets WHERE kind = ? AND qualified_name = ? LIMIT 1',
      ).get(kind, qualifiedName);
    return row ? rowToAsset(row as Record<string, unknown>) : null;
  }

  deleteAsset(kind: AssetKind, qualifiedName: string, sourceFile: string): boolean {
    const asset = this.findAssetByQualified(kind, qualifiedName, sourceFile);
    if (!asset) return false;
    this.db.prepare('DELETE FROM relations WHERE from_id = ? OR to_id = ?').run(asset.id, asset.id);
    this.db.prepare(
      'DELETE FROM assets WHERE kind = ? AND qualified_name = ? AND source_file = ?',
    ).run(kind, qualifiedName, sourceFile);
    return true;
  }

  mergeMetadata(assetId: number, patch: Record<string, unknown>): void {
    const asset = this.getAssetById(assetId);
    if (!asset) return;
    let existing: Record<string, unknown> = {};
    try {
      existing = JSON.parse(asset.metadataJson) as Record<string, unknown>;
    } catch { /* ignore */ }
    const merged = { ...existing, ...patch };
    this.db.prepare('UPDATE assets SET metadata_json = ? WHERE id = ?').run(
      JSON.stringify(merged),
      assetId,
    );
  }

  listTrackedFilesWithoutAssets(): Array<{ path: string; plugin: string | null }> {
    const rows = this.db.prepare(`
      SELECT f.path, f.plugin
      FROM files f
      LEFT JOIN assets a ON a.source_file = f.path
      WHERE a.id IS NULL
      ORDER BY f.path
    `).all() as Record<string, unknown>[];
    return rows.map((r) => ({
      path: r.path as string,
      plugin: (r.plugin as string | null) ?? null,
    }));
  }

  countAssetsByEnrichedBy(source: 'indexer' | 'agent'): number {
    const pattern = source === 'agent' ? '%"enrichedBy":"agent"%' : '%"enrichedBy":"indexer"%';
    const row = this.db.prepare(
      'SELECT COUNT(*) as c FROM assets WHERE metadata_json LIKE ?',
    ).get(pattern) as { c: number };
    return row.c;
  }
}