temp_first.txt

/**
 * Database Client
 * 
 * SQLite database operations for AI context storage.
 * Handles CRUD operations, queries, and vector search.
 */

import Database from 'better-sqlite3';
import * as sqliteVec from 'sqlite-vec';
import { createHash } from 'crypto';
import path from 'path';
import fs from 'fs';
import {
  SCHEMA_SQL,
  VECTOR_SCHEMA_SQL,
  TEMPLATE_SCHEMA_SQL,
  SCHEMA_VERSION,
  type ContextType,
  type RelationType,
  type SyncStatus,
  type AITool
} from './schema.js';

// Import TodoTask and TodoSession types for todo list management
import type { TodoTask, TodoSession } from '../agent-system/todolist-manager.js';

/**
 * Context item structure
 */
export interface ContextItem {
  id: string;
  type: ContextType;
  name: string;
  content: string;
  metadata?: Record<string, unknown>;
  filePath?: string;
  contentHash?: string;
  createdAt?: string;
  updatedAt?: string;
}

/**
 * Knowledge graph edge
 */
export interface GraphEdge {
  id?: number;
  sourceId: string;
  targetId: string;
  relationType: RelationType;
  weight?: number;
  metadata?: Record<string, unknown>;
}

/**
 * Git commit record
 */
export interface GitCommit {
  sha: string;
  message: string;
  authorName?: string;
  authorEmail?: string;
  timestamp: string;
  filesChanged?: string[];
  stats?: { additions: number; deletions: number };
}

/**
 * Sync state record
 */
export interface SyncState {
  id: string;
  tool: string;
  contentHash?: string;
  lastSync: string;
  filePath?: string;
  status: SyncStatus;
  metadata?: Record<string, unknown>;
  k0ntextVersion?: string;
  userModified?: number;
  lastChecked?: string;
}

/**
 * AI tool configuration record
 */
export interface AIToolConfig {
  id: string;
  toolName: AITool;
  configPath: string;
  content: string;
  contentHash?: string;
  lastSync: string;
  status: SyncStatus;
  metadata?: Record<string, unknown>;
}

/**
 * Version tracking record
 */
export interface VersionTracking {
  tool: string;
  k0ntextVersion: string;
  userModified?: boolean;
  lastChecked?: string;
  filePath?: string;
  contentHash?: string;
}

/**
 * Generated file record
 */
export interface GeneratedFile {
  id: string;
  tool: string;
  filePath: string;
  contentHash: string;
  backupPath?: string;
  generatedAt: string;
  lastVerifiedAt?: string;
  userModified: boolean;
  metadata?: Record<string, unknown>;
}

/**
 * Search result with similarity score
 */
export interface SearchResult {
  item: ContextItem;
  similarity: number;
}

/**
 * Database client for AI context storage
 */
export class DatabaseClient {
  private db: Database.Database;
  private dbPath: string;

  /**
   * Create a new DatabaseClient with async initialization
   * This is the recommended way to create a DatabaseClient as it handles migrations
   */
  static async create(projectRoot: string, dbFileName = '.k0ntext.db'): Promise<DatabaseClient> {
    const instance = new DatabaseClient(projectRoot, dbFileName, true);
    await instance.initSchema();
    return instance;
  }

  /**
   * Legacy synchronous constructor
   * @deprecated Use DatabaseClient.create() instead for proper migration support
   */
  constructor(projectRoot: string, dbFileName = '.k0ntext.db', skipInit = false) {
    this.dbPath = path.join(projectRoot, dbFileName);

    // Ensure directory exists
    const dbDir = path.dirname(this.dbPath);
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true });
    }

    this.db = new Database(this.dbPath);

    // Enable foreign keys
    this.db.pragma('foreign_keys = ON');

    // Load sqlite-vec extension
    sqliteVec.load(this.db);

    // Only initialize schema if not using create() method
    if (!skipInit) {
      this.initSchemaSync();
    }
  }

  /**
   * Synchronous schema initialization for legacy constructor
   */
  private initSchemaSync(): void {
    this.migrateLegacyDatabase();
    this.db.exec(SCHEMA_SQL);
    this.db.exec(VECTOR_SCHEMA_SQL);
    this.db.exec(TEMPLATE_SCHEMA_SQL);

    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO schema_version (version, applied_at)
      VALUES (?, datetime('now'))
    `);
    stmt.run(SCHEMA_VERSION);
  }

  /**
   * Migrate legacy database
   */
  private migrateLegacyDatabase(): void {
    const legacyPath = path.join(process.cwd(), '.ai-context.db');
    const newPath = this.dbPath;

    if (fs.existsSync(legacyPath) && !fs.existsSync(newPath)) {
      fs.copyFileSync(legacyPath, newPath);
      console.log(`✓ Migrated .ai-context.db to .k0ntext.db`);
    }
  }

  /**
   * Initialize database schema with migration support
   */
  private async initSchema(): Promise<void> {
    // Migrate legacy database first
    this.migrateLegacyDatabase();

    // Check if database is new (no context_items table)
    const isNewDatabase = !this.db.prepare(`
      SELECT name FROM sqlite_master WHERE type='table' AND name='context_items'
    `).get();

    if (isNewDatabase) {
      // New database - create all tables from schema
      this.db.exec(SCHEMA_SQL);
      this.db.exec(VECTOR_SCHEMA_SQL);
      this.db.exec(TEMPLATE_SCHEMA_SQL);
    } else {
      // Existing database - run migrations
      try {
        const { MigrationRunner } = await import('./migrations/index.js');
        const runner = new MigrationRunner(this, path.dirname(this.dbPath));

        const status = await runner.getStatus();
        if (status.needsMigration) {
          console.log(`Database schema ${status.currentVersion} -> ${status.targetVersion}`);
          await runner.migrate({ backup: true });
        }
      } catch (error) {
        // Migration failed - log but don't crash
        console.warn(`Migration check failed: ${error instanceof Error ? error.message : error}`);
      }
    }

    // Ensure schema_version is set
    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO schema_version (version, applied_at)
      VALUES (?, datetime('now'))
    `);
    stmt.run(SCHEMA_VERSION);
  }

  /**
   * Execute callback within a transaction (sync)
   */
  transaction<T>(callback: () => T): T;

  /**
   * Execute callback within a transaction (async)
   */
  transaction<T>(callback: () => Promise<T>): Promise<T>;

  /**
   * Execute callback within a transaction (implementation)
   */
  transaction<T>(callback: () => T | Promise<T>): T | Promise<T> {
    // Detect if callback is async by checking if it returns a Promise
    const result = callback();

    if (result instanceof Promise) {
      // For async, use manual transaction control
      return (async () => {
        this.db.exec('BEGIN TRANSACTION');
        try {
          const value = await result;
          this.db.exec('COMMIT');
          return value;
        } catch (error) {
          this.db.exec('ROLLBACK');
          throw error;
        }
      })();
    } else {
      // For sync, use better-sqlite3 transaction helper
      const txn = this.db.transaction(callback as () => T);
      return txn();
    }
  }

  /**
   * Begin a manual transaction (returns rollback/commit functions)
   */
  beginTransaction(): { rollback: () => void; commit: () => void } {
    this.db.exec('BEGIN TRANSACTION');
    return {
      rollback: () => this.db.exec('ROLLBACK'),
      commit: () => this.db.exec('COMMIT')
    };
  }

  /**
   * Check database connection health
   */
  healthCheck(): { healthy: boolean; error?: string } {
    try {
      this.db.prepare('SELECT 1').get();
      return { healthy: true };
    } catch (error) {
      return {
        healthy: false,
        error: error instanceof Error ? error.message : String(error)
      };
    }
  }

  /**
   * Generate content hash for deduplication (public for modification detection)
   */
  public hashContent(content: string): string {
    return createHash('sha256').update(content).digest('hex').slice(0, 16);
  }

  /**
   * Generate a unique ID for a context item
   */
  private generateId(type: ContextType, name: string): string {
    return `${type}:${name.toLowerCase().replace(/[^a-z0-9]/g, '-')}`;
  }

  // ==================== Context Items ====================

  /**
   * Insert or update a context item
   */
  upsertItem(item: Omit<ContextItem, 'id' | 'contentHash' | 'createdAt' | 'updatedAt'>): ContextItem {
    const id = this.generateId(item.type, item.name);
    const contentHash = this.hashContent(item.content);
    
    const stmt = this.db.prepare(`
      INSERT INTO context_items (id, type, name, content, metadata, file_path, content_hash, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'))
      ON CONFLICT(id) DO UPDATE SET
        content = excluded.content,
        metadata = excluded.metadata,
        file_path = excluded.file_path,
        content_hash = excluded.content_hash,
        updated_at = datetime('now')
      RETURNING *
    `);
    
    const row = stmt.get(
      id,
      item.type,
      item.name,
      item.content,
      item.metadata ? JSON.stringify(item.metadata) : null,
      item.filePath || null,
      contentHash
    ) as Record<string, unknown>;
    
    return this.rowToItem(row);
  }

  /**
   * Get a context item by ID
   */
  getItem(id: string): ContextItem | null {
    const stmt = this.db.prepare('SELECT * FROM context_items WHERE id = ?');
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.rowToItem(row) : null;
  }

  /**
   * Get items by type
   */
  getItemsByType(type: ContextType): ContextItem[] {
    const stmt = this.db.prepare('SELECT * FROM context_items WHERE type = ? ORDER BY name');
    const rows = stmt.all(type) as Record<string, unknown>[];
    return rows.map(row => this.rowToItem(row));
  }

  /**
   * Get all items
   */
  getAllItems(): ContextItem[] {
    const stmt = this.db.prepare('SELECT * FROM context_items ORDER BY type, name');
    const rows = stmt.all() as Record<string, unknown>[];
    return rows.map(row => this.rowToItem(row));
  }

  /**
   * Delete a context item
   */
  deleteItem(id: string): boolean {
    const stmt = this.db.prepare('DELETE FROM context_items WHERE id = ?');
    const result = stmt.run(id);
    return result.changes > 0;
  }

  /**
   * Delete items older than specified days
   */
  deleteStaleItems(daysOld: number, type?: ContextType): number {
    const stmt = this.db.prepare(`
      DELETE FROM context_items
      WHERE datetime(updated_at) < datetime('now', '-' || ? || ' days')
      ${type ? 'AND type = ?' : ''}
    `);
    const result = stmt.run(...(type ? [daysOld, type] : [daysOld]));
    return result.changes;
  }

  /**
   * Search items by text (full-text grep-style)
   */
  searchText(query: string, type?: ContextType): ContextItem[] {
    const pattern = `%${query}%`;
    let sql = 'SELECT * FROM context_items WHERE (content LIKE ? OR name LIKE ?)';
    const params: unknown[] = [pattern, pattern];
    
    if (type) {
      sql += ' AND type = ?';
      params.push(type);
    }
    
    sql += ' ORDER BY name LIMIT 50';
    
    const stmt = this.db.prepare(sql);
    const rows = stmt.all(...params) as Record<string, unknown>[];
    return rows.map(row => this.rowToItem(row));
  }

  /**
   * Convert database row to ContextItem
   */
  private rowToItem(row: Record<string, unknown>): ContextItem {
    return {
      id: row.id as string,
      type: row.type as ContextType,
      name: row.name as string,
      content: row.content as string,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined,
      filePath: row.file_path as string | undefined,
      contentHash: row.content_hash as string | undefined,
      createdAt: row.created_at as string | undefined,
      updatedAt: row.updated_at as string | undefined
    };
  }

  /**
   * Calculate relevance score for a search result
   */
  private calculateRelevance(
    item: ContextItem,
    query: string,
    baseScore: number
  ): number {
    let score = baseScore;

    // Boost score for exact name matches
    if (item.name.toLowerCase().includes(query.toLowerCase())) {
      score *= 1.5;
    }

    // Boost score for recently updated items
    if (item.updatedAt) {
      const daysSinceUpdate = (Date.now() - new Date(item.updatedAt).getTime()) / (1000 * 60 * 60 * 24);
      if (daysSinceUpdate < 7) {
        score *= 1.2; // 20% boost for items updated within a week
      }
    }

    // Boost score for certain types
    if (item.type === 'workflow' || item.type === 'agent') {
      score *= 1.1;
    }

    return score;
  }

  // ==================== AI Tool Configs ====================

  /**
   * Upsert an AI tool configuration
   */
  upsertToolConfig(config: Omit<AIToolConfig, 'contentHash' | 'lastSync'>): AIToolConfig {
    const contentHash = this.hashContent(config.content);
    
    const stmt = this.db.prepare(`
      INSERT INTO ai_tool_configs (id, tool_name, config_path, content, content_hash, last_sync, status, metadata)
      VALUES (?, ?, ?, ?, ?, datetime('now'), ?, ?)
      ON CONFLICT(id) DO UPDATE SET
        content = excluded.content,
        content_hash = excluded.content_hash,
        last_sync = datetime('now'),
        status = excluded.status,
        metadata = excluded.metadata
      RETURNING *
    `);
    
    const row = stmt.get(
      config.id,
      config.toolName,
      config.configPath,
      config.content,
      contentHash,
      config.status,
      config.metadata ? JSON.stringify(config.metadata) : null
    ) as Record<string, unknown>;
    
    return {
      id: row.id as string,
      toolName: row.tool_name as AITool,
      configPath: row.config_path as string,
      content: row.content as string,
      contentHash: row.content_hash as string,
      lastSync: row.last_sync as string,
      status: row.status as SyncStatus,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined
    };
  }

  /**
   * Get tool configs by tool name
   */
  getToolConfigs(toolName: AITool): AIToolConfig[] {
    const stmt = this.db.prepare('SELECT * FROM ai_tool_configs WHERE tool_name = ?');
    const rows = stmt.all(toolName) as Record<string, unknown>[];
    
    return rows.map(row => ({
      id: row.id as string,
      toolName: row.tool_name as AITool,
      configPath: row.config_path as string,
      content: row.content as string,
      contentHash: row.content_hash as string,
      lastSync: row.last_sync as string,
      status: row.status as SyncStatus,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined
    }));
  }

  /**
   * Get all tool configs
   */
  getAllToolConfigs(): AIToolConfig[] {
    const stmt = this.db.prepare('SELECT * FROM ai_tool_configs ORDER BY tool_name');
    const rows = stmt.all() as Record<string, unknown>[];
    
    return rows.map(row => ({
      id: row.id as string,
      toolName: row.tool_name as AITool,
      configPath: row.config_path as string,
      content: row.content as string,
      contentHash: row.content_hash as string,
      lastSync: row.last_sync as string,
      status: row.status as SyncStatus,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined
    }));
  }

  // ==================== Knowledge Graph ====================

  /**
   * Add a relationship to the knowledge graph
   */
  addRelation(edge: Omit<GraphEdge, 'id'>): GraphEdge {
    const stmt = this.db.prepare(`
      INSERT INTO knowledge_graph (source_id, target_id, relation_type, weight, metadata)
      VALUES (?, ?, ?, ?, ?)
      ON CONFLICT(source_id, target_id, relation_type) DO UPDATE SET
        weight = excluded.weight,
        metadata = excluded.metadata
      RETURNING *
    `);
    
    const row = stmt.get(
      edge.sourceId,
      edge.targetId,
      edge.relationType,
      edge.weight ?? 1.0,
      edge.metadata ? JSON.stringify(edge.metadata) : null
    ) as Record<string, unknown>;
    
    return {
      id: row.id as number,
      sourceId: row.source_id as string,
      targetId: row.target_id as string,
      relationType: row.relation_type as RelationType,
      weight: row.weight as number,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined
    };
  }

  /**
   * Get relations from a source item
   */
  getRelationsFrom(sourceId: string, relationType?: RelationType): GraphEdge[] {
    let sql = `
      SELECT kg.*, ci.name as target_name
      FROM knowledge_graph kg
      JOIN context_items ci ON kg.target_id = ci.id
      WHERE kg.source_id = ?
    `;
    const params: unknown[] = [sourceId];
    
    if (relationType) {
      sql += ' AND kg.relation_type = ?';
      params.push(relationType);
    }
    
    sql += ' ORDER BY kg.weight DESC';
    
    const stmt = this.db.prepare(sql);
    const rows = stmt.all(...params) as Record<string, unknown>[];
    
    return rows.map(row => ({
      id: row.id as number,
      sourceId: row.source_id as string,
      targetId: row.target_id as string,
      relationType: row.relation_type as RelationType,
      weight: row.weight as number,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined
    }));
  }

  /**
   * Get relations to a target item
   */
  getRelationsTo(targetId: string, relationType?: RelationType): GraphEdge[] {
    let sql = `
      SELECT kg.*, ci.name as source_name
      FROM knowledge_graph kg
      JOIN context_items ci ON kg.source_id = ci.id
      WHERE kg.target_id = ?
    `;
    const params: unknown[] = [targetId];
    
    if (relationType) {
      sql += ' AND kg.relation_type = ?';
      params.push(relationType);
    }
    
    sql += ' ORDER BY kg.weight DESC';
    
    const stmt = this.db.prepare(sql);
    const rows = stmt.all(...params) as Record<string, unknown>[];
    
    return rows.map(row => ({
      id: row.id as number,
      sourceId: row.source_id as string,
      targetId: row.target_id as string,
      relationType: row.relation_type as RelationType,
      weight: row.weight as number,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined
    }));
  }

  /**
   * Traverse the graph from a starting point
   */
  traverseGraph(startId: string, maxDepth = 3): Map<string, { item: ContextItem; depth: number }> {
    const visited = new Map<string, { item: ContextItem; depth: number }>();
    const queue: Array<{ id: string; depth: number }> = [{ id: startId, depth: 0 }];
    
    while (queue.length > 0) {
      const { id, depth } = queue.shift()!;
      
      if (visited.has(id) || depth > maxDepth) continue;
      
      const item = this.getItem(id);
      if (!item) continue;
      
      visited.set(id, { item, depth });
      
      // Get all outgoing relations
      const relations = this.getRelationsFrom(id);
      for (const rel of relations) {
        if (!visited.has(rel.targetId)) {
          queue.push({ id: rel.targetId, depth: depth + 1 });
        }
      }
    }
    
    return visited;
  }

  // ==================== Git Commits ====================

  /**
   * Insert or update a git commit
   */
  upsertCommit(commit: GitCommit): void {
    const stmt = this.db.prepare(`
      INSERT INTO git_commits (sha, message, author_name, author_email, timestamp, files_changed, stats)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(sha) DO UPDATE SET
        message = excluded.message,
        files_changed = excluded.files_changed,
        stats = excluded.stats
    `);
    
    stmt.run(
      commit.sha,
      commit.message,
      commit.authorName || null,
      commit.authorEmail || null,
      commit.timestamp,
      commit.filesChanged ? JSON.stringify(commit.filesChanged) : null,
      commit.stats ? JSON.stringify(commit.stats) : null
    );
  }

  /**
   * Get recent commits
   */
  getRecentCommits(limit = 50): GitCommit[] {
    const stmt = this.db.prepare(`
      SELECT * FROM git_commits
      ORDER BY timestamp DESC
      LIMIT ?
    `);
    
    const rows = stmt.all(limit) as Record<string, unknown>[];
    
    return rows.map(row => ({
      sha: row.sha as string,
      message: row.message as string,
      authorName: row.author_name as string | undefined,
      authorEmail: row.author_email as string | undefined,
      timestamp: row.timestamp as string,
      filesChanged: row.files_changed ? JSON.parse(row.files_changed as string) : undefined,
      stats: row.stats ? JSON.parse(row.stats as string) : undefined
    }));
  }

  // ==================== Sync State ====================

  /**
   * Update sync state for a tool
   */
  updateSyncState(state: SyncState): void {
    const stmt = this.db.prepare(`
      INSERT INTO sync_state (id, tool, content_hash, last_sync, file_path, status, metadata)
      VALUES (?, ?, ?, datetime('now'), ?, ?, ?)
      ON CONFLICT(id) DO UPDATE SET
        content_hash = excluded.content_hash,
        last_sync = datetime('now'),
        status = excluded.status,
        metadata = excluded.metadata
    `);
    
    stmt.run(
      state.id,
      state.tool,
      state.contentHash || null,
      state.filePath || null,
      state.status,
      state.metadata ? JSON.stringify(state.metadata) : null
    );
  }

  /**
   * Get sync state for a tool
   */
  getSyncState(tool: string): SyncState[] {
    const stmt = this.db.prepare('SELECT * FROM sync_state WHERE tool = ?');
    const rows = stmt.all(tool) as Record<string, unknown>[];

    return rows.map(row => ({
      id: row.id as string,
      tool: row.tool as string,
      contentHash: row.content_hash as string | undefined,
      lastSync: row.last_sync as string,
      filePath: row.file_path as string | undefined,
      status: row.status as SyncStatus,
      metadata: row.metadata ? JSON.parse(row.metadata as string) : undefined,
      k0ntextVersion: row.k0ntext_version as string | undefined,
      userModified: row.user_modified as number | undefined,
      lastChecked: row.last_checked as string | undefined
    }));
  }

  // ==================== TodoList Management ====================
  /**
   * Insert a new todo session
   */
  insertTodoSession(id: string, name: string, metadata?: Record<string, unknown>): void {
    const stmt = this.db.prepare(
      "INSERT INTO todo_sessions (id, name, created_at, updated_at, parent_session, metadata) VALUES (?, ?, datetime('now'), datetime('now'), ?, ?)"
    );
    stmt.run(id, name, metadata || null);
  }

  /**
   * Insert a new todo task
   */
  insertTodoTask(sessionId: string, taskId: string, subject: string, description: string, status: string, dependencies: string | null, assignedTo: string | null): void {
    const stmt = this.db.prepare(
      'INSERT INTO todo_tasks (id, session_id, subject, description, status, dependencies, assigned_to, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime(\'now\'))'
    );
    stmt.run(taskId, sessionId, subject, description, status, dependencies, assignedTo);
  }

  /**
   * Get a todo session by ID
   */
  getTodoSession(sessionId: string): Record<string, unknown> | null {
    const stmt = this.db.prepare('SELECT * FROM todo_sessions WHERE id = ?');
    return stmt.get(sessionId) || null;
  }

  /**
   * Get all tasks for a todo session
   */
  getTodoTasks(sessionId: string): Record<string, unknown>[] {
    const stmt = this.db.prepare('SELECT * FROM todo_tasks WHERE session_id = ? ORDER BY created_at ASC');
    return stmt.all(sessionId) || [];
  }

  /**
   * Update a todo task
   */
  updateTodoTask(taskId: string, updates: { status?: string; subject?: string; description?: string; completedAt?: string }): void {
    const parts: string[] = [];
    const values: (string | number)[] = [];

    if (updates.status !== undefined) {
      parts.push('status = ?');
      values.push(updates.status);
    }
    if (updates.subject !== undefined) {
      parts.push('subject = ?');
      values.push(updates.subject);
    }
    if (updates.description !== undefined) {
      parts.push('description = ?');
      values.push(updates.description);
    }
    if (updates.completedAt !== undefined) {
      parts.push('completed_at = ?');
      values.push(updates.completedAt);
    }

    if (parts.length === 0) return;

    const stmt = this.db.prepare(`
      UPDATE todo_tasks SET ${parts.join(', ')} WHERE id = ?
    `);
    stmt.run(...values, taskId);
  }

  // ==================== TodoList Management ====================