apps/backend/src/mcp/memory/SqlJsCompat.ts

/**
 * ╔══════════════════════════════════════════════════════════════════════════════╗
 * ║                    SQL.JS COMPATIBILITY LAYER                                 ║
 * ║                                                                                ║
 * ║  Provides cross-platform compatible database operations for sql.js.           ║
 * ║  Handles API differences between Node.js and Docker/Alpine environments.      ║
 * ╚══════════════════════════════════════════════════════════════════════════════╝
 */

import type { Database } from 'sql.js';
import { logger } from '../../utils/logger.js';

export interface QueryResult {
    columns: string[];
    values: any[][];
}

/**
 * Execute a SELECT query and return results as objects
 * Compatible with both standard sql.js and Docker/Alpine builds
 */
export function queryAll<T = Record<string, any>>(
    db: Database,
    sql: string,
    params: any[] = []
): T[] {
    try {
        // Try the exec method first (most compatible)
        const results = db.exec(sql, params);
        
        if (!results || results.length === 0) {
            return [];
        }

        const { columns, values } = results[0];
        return values.map(row => {
            const obj: Record<string, any> = {};
            columns.forEach((col, i) => {
                obj[col] = row[i];
            });
            return obj as T;
        });
    } catch (execError) {
        // Fallback: try prepare/bind/step pattern
        try {
            const stmt = db.prepare(sql);
            const results: T[] = [];
            
            // Check if bind exists and is a function
            if (params.length > 0 && typeof stmt.bind === 'function') {
                stmt.bind(params);
            }
            
            // Check if step is a function
            if (typeof stmt.step === 'function') {
                while (stmt.step()) {
                    if (typeof stmt.getAsObject === 'function') {
                        results.push(stmt.getAsObject() as T);
                    }
                }
            }
            
            if (typeof stmt.free === 'function') {
                stmt.free();
            }
            
            return results;
        } catch (prepareError) {
            logger.warn('⚠️ SqlJsCompat queryAll failed:', { sql: sql.substring(0, 100), execError, prepareError });
            return [];
        }
    }
}

/**
 * Execute a SELECT query and return first result as object
 */
export function queryOne<T = Record<string, any>>(
    db: Database,
    sql: string,
    params: any[] = []
): T | null {
    const results = queryAll<T>(db, sql, params);
    return results.length > 0 ? results[0] : null;
}

/**
 * Execute a SELECT query and return a single value
 */
export function queryScalar<T = any>(
    db: Database,
    sql: string,
    params: any[] = []
): T | null {
    try {
        const results = db.exec(sql, params);
        
        if (!results || results.length === 0 || results[0].values.length === 0) {
            return null;
        }
        
        return results[0].values[0][0] as T;
    } catch (error) {
        logger.warn('⚠️ SqlJsCompat queryScalar failed:', { sql: sql.substring(0, 100), error });
        return null;
    }
}

/**
 * Execute an INSERT/UPDATE/DELETE statement
 * Returns number of changes (if available)
 */
export function execute(
    db: Database,
    sql: string,
    params: any[] = []
): number {
    try {
        // Use run for statements without return values
        if (params.length === 0) {
            db.run(sql);
        } else {
            // Try exec with params
            db.exec(sql, params);
        }
        
        // Try to get changes count
        try {
            const changes = queryScalar<number>(db, 'SELECT changes()');
            return changes ?? 0;
        } catch {
            return 0;
        }
    } catch (error) {
        logger.warn('⚠️ SqlJsCompat execute failed:', { sql: sql.substring(0, 100), error });
        throw error;
    }
}

/**
 * Execute multiple statements in a transaction
 */
export function executeTransaction(
    db: Database,
    statements: Array<{ sql: string; params?: any[] }>
): void {
    try {
        db.run('BEGIN TRANSACTION');
        
        for (const { sql, params = [] } of statements) {
            if (params.length === 0) {
                db.run(sql);
            } else {
                db.exec(sql, params);
            }
        }
        
        db.run('COMMIT');
    } catch (error) {
        try {
            db.run('ROLLBACK');
        } catch {
            // Ignore rollback errors
        }
        throw error;
    }
}

/**
 * Batch insert multiple rows efficiently
 */
export function batchInsert(
    db: Database,
    tableName: string,
    columns: string[],
    rows: any[][]
): number {
    if (rows.length === 0) return 0;
    
    const placeholders = columns.map(() => '?').join(', ');
    const sql = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
    
    let inserted = 0;
    
    try {
        db.run('BEGIN TRANSACTION');
        
        for (const row of rows) {
            try {
                db.exec(sql, row);
                inserted++;
            } catch (rowError) {
                logger.warn('⚠️ Batch insert row failed:', rowError);
            }
        }
        
        db.run('COMMIT');
    } catch (error) {
        try {
            db.run('ROLLBACK');
        } catch {
            // Ignore rollback errors
        }
        logger.warn('⚠️ Batch insert transaction failed:', error);
    }
    
    return inserted;
}

/**
 * Check if a table exists
 */
export function tableExists(db: Database, tableName: string): boolean {
    const result = queryScalar<number>(
        db,
        `SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?`,
        [tableName]
    );
    return (result ?? 0) > 0;
}

/**
 * Get row count for a table
 */
export function getRowCount(db: Database, tableName: string): number {
    const result = queryScalar<number>(db, `SELECT COUNT(*) FROM ${tableName}`);
    return result ?? 0;
}