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

import type { Database } from 'sql.js';
import { logger } from '../../utils/logger.js';
import { queryAll, queryOne, execute, batchInsert, queryScalar } from './SqlJsCompat.js';
import { DatabaseAdapter } from '../../platform/db/PrismaDatabaseAdapter.js';

export interface StorageAdapter {
    queryAll<T = any>(sql: string, params?: any[]): Promise<T[]>;
    queryOne<T = any>(sql: string, params?: any[]): Promise<T | null>;
    queryScalar<T = any>(sql: string, params?: any[]): Promise<T | null>;
    execute(sql: string, params?: any[]): Promise<number>;
    batchInsert(tableName: string, columns: string[], rows: any[][]): Promise<number>;
    isAvailable(): boolean;
    mode: 'sqlite' | 'postgres';
}

export class SqlJsStorageAdapter implements StorageAdapter {
    constructor(private db: Database | null) { }

    mode: 'sqlite' | 'postgres' = 'sqlite';

    isAvailable(): boolean {
        return !!this.db;
    }

    async queryAll<T = any>(sql: string, params: any[] = []): Promise<T[]> {
        if (!this.db) return [];
        // SqlJsCompat functions are synchronous for sql.js, but we wrap in Promise for common interface
        return Promise.resolve(queryAll<T>(this.db, sql, params));
    }

    async queryOne<T = any>(sql: string, params: any[] = []): Promise<T | null> {
        if (!this.db) return null;
        return Promise.resolve(queryOne<T>(this.db, sql, params));
    }

    async queryScalar<T = any>(sql: string, params: any[] = []): Promise<T | null> {
        if (!this.db) return null;
        return Promise.resolve(queryScalar<T>(this.db, sql, params));
    }

    async execute(sql: string, params: any[] = []): Promise<number> {
        if (!this.db) return 0;
        return Promise.resolve(execute(this.db, sql, params));
    }

    async batchInsert(tableName: string, columns: string[], rows: any[][]): Promise<number> {
        if (!this.db) return 0;
        return Promise.resolve(batchInsert(this.db, tableName, columns, rows));
    }
}

export class PostgresStorageAdapter implements StorageAdapter {
    constructor(private prisma: DatabaseAdapter) { }

    mode: 'sqlite' | 'postgres' = 'postgres';

    isAvailable(): boolean {
        return this.prisma.isAvailable();
    }

    async queryAll<T = any>(sql: string, params: any[] = []): Promise<T[]> {
        // Postgres uses $1, $2, etc. instead of ?
        const { sql: pgSql, params: pgParams } = this.convertSql(sql, params);
        return await this.prisma.query(pgSql, pgParams);
    }

    async queryOne<T = any>(sql: string, params: any[] = []): Promise<T | null> {
        const rows = await this.queryAll<T>(sql, params);
        return rows.length > 0 ? rows[0] : null;
    }

    async queryScalar<T = any>(sql: string, params: any[] = []): Promise<T | null> {
        const rows = await this.queryAll(sql, params);
        if (rows.length === 0) return null;
        const firstValue = Object.values(rows[0])[0];
        return firstValue as T;
    }

    async execute(sql: string, params: any[] = []): Promise<number> {
        const { sql: pgSql, params: pgParams } = this.convertSql(sql, params);
        return await this.prisma.execute(pgSql, pgParams);
    }

    async batchInsert(tableName: string, columns: string[], rows: any[][]): Promise<number> {
        if (rows.length === 0) return 0;

        // Construct standard VALUES clause but with $1, $2...
        // This is tricky with variable params.
        // A simple loop implementation for now
        let count = 0;
        for (const row of rows) {
            const placeholders = row.map((_, i) => `$${i + 1}`).join(', ');
            const sql = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
            await this.prisma.query(sql, row);
            count++;
        }
        return count;
    }

    private convertSql(sql: string, params: any[]): { sql: string, params: any[] } {
        // Convert ? to $1, $2, etc.
        let paramIndex = 1;
        const pgSql = sql.replace(/\?/g, () => `$${paramIndex++}`);
        return { sql: pgSql, params };
    }
}