api/db.js

// api/db.js — NT DB Core Engine
import { readFile, writeFile } from 'fs/promises';
import { existsSync, mkdirSync } from 'fs';
import { join } from 'path';
import crypto from 'crypto';

const DB_DIR = process.env.DB_DIR || join(process.cwd(), 'db');
if (!existsSync(DB_DIR)) mkdirSync(DB_DIR, { recursive: true });

// ── File helpers ──────────────────────────────────────────────────────────────
async function readTable(table) {
  try { return JSON.parse(await readFile(join(DB_DIR, `${table}.json`), 'utf8')); }
  catch { return []; }
}

async function writeTable(table, rows) {
  await writeFile(join(DB_DIR, `${table}.json`), JSON.stringify(rows, null, 2), 'utf8');
}

export async function readSchema() {
  try { return JSON.parse(await readFile(join(DB_DIR, '_schema.json'), 'utf8')); }
  catch { return { tables: {} }; }
}

export async function writeSchema(schema) {
  await writeFile(join(DB_DIR, '_schema.json'), JSON.stringify(schema, null, 2), 'utf8');
}

// ── Query helpers ─────────────────────────────────────────────────────────────
function applyFilters(rows, filters) {
  return rows.filter(row => filters.every(({ col, op, val }) => {
    const cell = row[col];
    switch (op) {
      case 'eq':   return String(cell) == String(val);
      case 'neq':  return String(cell) != String(val);
      case 'gt':   return Number(cell) > Number(val);
      case 'gte':  return Number(cell) >= Number(val);
      case 'lt':   return Number(cell) < Number(val);
      case 'lte':  return Number(cell) <= Number(val);
      case 'like': return String(cell).toLowerCase().includes(String(val).replace(/%/g, '').toLowerCase());
      case 'in':   return Array.isArray(val) && val.includes(cell);
      default:     return true;
    }
  }));
}

function applySelect(rows, columns) {
  if (!columns || columns === '*') return rows;
  const cols = columns.split(',').map(c => c.trim());
  return rows.map(row => Object.fromEntries(cols.filter(c => c in row).map(c => [c, row[c]])));
}

function applyOrder(rows, col, dir = 'asc') {
  return [...rows].sort((a, b) => {
    if (a[col] < b[col]) return dir === 'asc' ? -1 : 1;
    if (a[col] > b[col]) return dir === 'asc' ? 1 : -1;
    return 0;
  });
}

function autoFill(record, tableDef) {
  const out = { ...record };
  for (const [col, def] of Object.entries(tableDef?.columns || {})) {
    if (def.auto) {
      if (def.type === 'uuid' && def.primaryKey) out[col] = out[col] || crypto.randomUUID();
      if (def.type === 'timestamp')              out[col] = out[col] || new Date().toISOString();
    }
  }
  return out;
}

function validate(record, tableDef) {
  const errors = [];
  for (const [col, def] of Object.entries(tableDef?.columns || {})) {
    if (def.required && !def.auto && !(col in record)) errors.push(`Missing required field: ${col}`);
  }
  return errors;
}

// ── Query Builder ─────────────────────────────────────────────────────────────
class QueryBuilder {
  constructor(table) {
    this._table    = table;
    this._filters  = [];
    this._select   = '*';
    this._orderCol = null;
    this._orderDir = 'asc';
    this._limit    = null;
    this._offset   = 0;
    this._op       = 'select';
    this._payload  = null;
  }

  select(cols = '*') { this._select = cols; return this; }
  insert(data)       { this._op = 'insert'; this._payload = data; return this; }
  update(data)       { this._op = 'update'; this._payload = data; return this; }
  delete()           { this._op = 'delete'; return this; }

  eq(col, val)   { this._filters.push({ col, op: 'eq',   val }); return this; }
  neq(col, val)  { this._filters.push({ col, op: 'neq',  val }); return this; }
  gt(col, val)   { this._filters.push({ col, op: 'gt',   val }); return this; }
  gte(col, val)  { this._filters.push({ col, op: 'gte',  val }); return this; }
  lt(col, val)   { this._filters.push({ col, op: 'lt',   val }); return this; }
  lte(col, val)  { this._filters.push({ col, op: 'lte',  val }); return this; }
  like(col, val) { this._filters.push({ col, op: 'like', val }); return this; }
  in(col, val)   { this._filters.push({ col, op: 'in',   val }); return this; }

  order(col, dir = 'asc') { this._orderCol = col; this._orderDir = dir; return this; }
  limit(n)  { this._limit = n;  return this; }
  offset(n) { this._offset = n; return this; }

  async execute() {
    const schema   = await readSchema();
    const tableDef = schema.tables?.[this._table];

    // INSERT
    if (this._op === 'insert') {
      const rows    = await readTable(this._table);
      const records = Array.isArray(this._payload) ? this._payload : [this._payload];
      const inserted = [];
      for (const rec of records) {
        const errors = validate(rec, tableDef);
        if (errors.length) throw new Error(errors.join(', '));
        const filled = autoFill(rec, tableDef);
        rows.push(filled);
        inserted.push(filled);
      }
      await writeTable(this._table, rows);
      return { data: inserted, error: null };
    }

    // UPDATE
    if (this._op === 'update') {
      const all     = await readTable(this._table);
      const updated = all.map(row => {
        if (applyFilters([row], this._filters).length === 0) return row;
        return { ...row, ...this._payload, updated_at: new Date().toISOString() };
      });
      await writeTable(this._table, updated);
      return { data: applyFilters(updated, this._filters), error: null };
    }

    // DELETE
    if (this._op === 'delete') {
      const all  = await readTable(this._table);
      const gone = applyFilters(all, this._filters);
      await writeTable(this._table, all.filter(row => !gone.includes(row)));
      return { data: gone, error: null };
    }

    // SELECT
    let rows = applyFilters(await readTable(this._table), this._filters);
    if (this._orderCol) rows = applyOrder(rows, this._orderCol, this._orderDir);
    if (this._limit)    rows = rows.slice(this._offset, this._offset + this._limit);
    else if (this._offset) rows = rows.slice(this._offset);
    return { data: applySelect(rows, this._select), error: null };
  }

  then(resolve, reject) { return this.execute().then(resolve, reject); }
}

export default {
  from:   (table) => new QueryBuilder(table),
  schema: readSchema,
};