server.js

// server.js — NT DB Server
import express from 'express';
import { readFile, writeFile } from 'fs/promises';
import { existsSync, mkdirSync } from 'fs';
import { join } from 'path';
import db, { readSchema, writeSchema } from './api/db.js';

const app  = express();
const PORT = process.env.PORT || 7860; // 7860 for HF Spaces, 3000 for local
const DB_DIR = process.env.DB_DIR || join(process.cwd(), 'db');

if (!existsSync(DB_DIR)) mkdirSync(DB_DIR, { recursive: true });

app.use(express.json());

// ── CORS (allow all for now) ──────────────────────────────────────────────────
app.use((req, res, next) => {
  res.setHeader('Access-Control-Allow-Origin', '*');
  res.setHeader('Access-Control-Allow-Methods', 'GET,POST,PATCH,DELETE,OPTIONS');
  res.setHeader('Access-Control-Allow-Headers', 'Content-Type,Authorization,apikey');
  if (req.method === 'OPTIONS') return res.sendStatus(204);
  next();
});

// ── Parse query string filters ────────────────────────────────────────────────
// ?name=eq.Alice&age=gt.25&status=in.(active,trial)
function parseFilters(query, builder) {
  const reserved = new Set(['select', 'order', 'limit', 'offset']);
  for (const [key, val] of Object.entries(query)) {
    if (reserved.has(key)) continue;
    const dot = val.indexOf('.');
    if (dot === -1) continue;
    const op  = val.slice(0, dot);
    let value = val.slice(dot + 1);
    if (op === 'in' && value.startsWith('(') && value.endsWith(')')) {
      value = value.slice(1, -1).split(',').map(v => v.trim());
    } else if (!isNaN(value) && value !== '') {
      value = Number(value);
    }
    if (typeof builder[op] === 'function') builder[op](key, value);
  }
  return builder;
}

// ── Health ────────────────────────────────────────────────────────────────────
app.get('/health', (_, res) => res.json({ status: 'ok', name: 'NT DB', version: '1.0.0' }));

// ── Schema ────────────────────────────────────────────────────────────────────

// GET /rest/v1/schema — view full schema
app.get('/rest/v1/schema', async (req, res) => {
  try { res.json(await readSchema()); }
  catch (err) { res.status(500).json({ error: err.message }); }
});

// POST /rest/v1/schema/tables — create a new table
app.post('/rest/v1/schema/tables', async (req, res) => {
  try {
    const { table, columns } = req.body;
    if (!table) return res.status(400).json({ error: 'table name required' });

    const schema = await readSchema();
    if (schema.tables[table]) return res.status(409).json({ error: `Table '${table}' already exists` });

    // Always add id + created_at
    schema.tables[table] = {
      columns: {
        id:         { type: 'uuid',      primaryKey: true, auto: true },
        created_at: { type: 'timestamp', auto: true },
        updated_at: { type: 'timestamp', auto: true },
        ...(columns || {})
      }
    };

    await writeSchema(schema);

    // Create empty table file
    const tableFile = join(DB_DIR, `${table}.json`);
    if (!existsSync(tableFile)) await writeFile(tableFile, '[]', 'utf8');

    res.status(201).json({ message: `Table '${table}' created`, schema: schema.tables[table] });
  } catch (err) { res.status(500).json({ error: err.message }); }
});

// DELETE /rest/v1/schema/tables/:table — drop a table
app.delete('/rest/v1/schema/tables/:table', async (req, res) => {
  try {
    const schema = await readSchema();
    if (!schema.tables[req.params.table]) return res.status(404).json({ error: 'Table not found' });
    delete schema.tables[req.params.table];
    await writeSchema(schema);
    res.json({ message: `Table '${req.params.table}' dropped` });
  } catch (err) { res.status(500).json({ error: err.message }); }
});

// ── REST API ──────────────────────────────────────────────────────────────────

// GET /rest/v1/:table
app.get('/rest/v1/:table', async (req, res) => {
  try {
    let q = db.from(req.params.table).select(req.query.select || '*');
    q = parseFilters(req.query, q);
    if (req.query.order) {
      const [col, dir] = req.query.order.split('.');
      q.order(col, dir || 'asc');
    }
    if (req.query.limit)  q.limit(parseInt(req.query.limit));
    if (req.query.offset) q.offset(parseInt(req.query.offset));

    const { data, error } = await q;
    if (error) return res.status(400).json({ error });
    res.json(data);
  } catch (err) { res.status(500).json({ error: err.message }); }
});

// POST /rest/v1/:table
app.post('/rest/v1/:table', async (req, res) => {
  try {
    const { data, error } = await db.from(req.params.table).insert(req.body);
    if (error) return res.status(400).json({ error });
    res.status(201).json(data);
  } catch (err) { res.status(500).json({ error: err.message }); }
});

// PATCH /rest/v1/:table
app.patch('/rest/v1/:table', async (req, res) => {
  try {
    let q = db.from(req.params.table).update(req.body);
    q = parseFilters(req.query, q);
    const { data, error } = await q;
    if (error) return res.status(400).json({ error });
    res.json(data);
  } catch (err) { res.status(500).json({ error: err.message }); }
});

// DELETE /rest/v1/:table
app.delete('/rest/v1/:table', async (req, res) => {
  try {
    let q = db.from(req.params.table).delete();
    q = parseFilters(req.query, q);
    const { data, error } = await q;
    if (error) return res.status(400).json({ error });
    res.json(data);
  } catch (err) { res.status(500).json({ error: err.message }); }
});

// ── Dashboard (simple HTML) ───────────────────────────────────────────────────
app.get('/', async (req, res) => {
  const schema = await readSchema().catch(() => ({ tables: {} }));
  const tables = Object.keys(schema.tables);
  res.send(`<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width,initial-scale=1"/>
<title>NT DB Dashboard</title>
<script src="https://cdn.tailwindcss.com"></script>
<style>
  body{background:#0f172a;color:#e2e8f0;font-family:system-ui,sans-serif}
  input,textarea,select{background:#1e293b;border:1px solid #334155;color:#e2e8f0;border-radius:8px;padding:8px 12px;width:100%;outline:none}
  input:focus,textarea:focus{border-color:#22c55e}
  table{width:100%;border-collapse:collapse}
  th{background:#1e293b;color:#94a3b8;font-size:12px;text-transform:uppercase;padding:10px 16px;text-align:left;border-bottom:1px solid #334155}
  td{padding:10px 16px;border-bottom:1px solid #1e293b;font-size:13px;max-width:220px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap}
  tr:hover td{background:#1e293b}
  .btn{padding:7px 18px;border-radius:8px;font-size:13px;font-weight:600;cursor:pointer;border:none;transition:opacity .15s}
  .btn:hover{opacity:.85}
</style>
</head>
<body class="min-h-screen">
<div class="flex items-center gap-3 px-8 py-4 border-b border-slate-700">
  <div class="w-8 h-8 rounded-lg bg-green-500 flex items-center justify-center font-bold text-black text-sm">NT</div>
  <span class="font-bold text-lg">NT DB</span>
  <span class="ml-2 text-xs bg-green-900 text-green-400 px-2 py-0.5 rounded-full font-semibold">● Live</span>
</div>
<div class="flex h-[calc(100vh-57px)]">
  <!-- Sidebar -->
  <div class="w-52 border-r border-slate-700 p-4">
    <div class="flex items-center justify-between mb-3">
      <span class="text-xs font-semibold text-slate-400 uppercase tracking-wider">Tables</span>
      <button onclick="showCreate()" class="text-green-400 text-xl leading-none hover:text-green-300">+</button>
    </div>
    <div id="tableList">
      ${tables.map(t => `<div class="px-3 py-2 rounded-lg text-sm text-slate-300 hover:bg-slate-700 cursor-pointer" onclick="loadTable('${t}')">${t}</div>`).join('')}
    </div>
  </div>
  <!-- Main -->
  <div class="flex-1 overflow-auto p-6" id="main">
    <div class="text-slate-400 text-center mt-24 text-sm">← Select a table or create one</div>
  </div>
</div>
<!-- Modal -->
<div id="modal" class="hidden fixed inset-0 bg-black/60 flex items-center justify-center z-50">
  <div class="bg-slate-800 border border-slate-700 rounded-xl w-full max-w-md p-6" id="modalBody"></div>
</div>
<script>
const BASE = '';
let curTable = null, curSchema = null, curData = [];

async function loadSchema() {
  const r = await fetch(BASE + '/rest/v1/schema');
  curSchema = await r.json();
}

async function loadTable(name) {
  curTable = name;
  await loadSchema();
  const r = await fetch(BASE + '/rest/v1/' + name);
  curData  = await r.json();
  renderTable();
}

function renderTable() {
  const cols = curSchema?.tables?.[curTable]
    ? Object.keys(curSchema.tables[curTable].columns)
    : (curData[0] ? Object.keys(curData[0]) : []);

  document.getElementById('main').innerHTML = \`
    <div class="flex items-center justify-between mb-4">
      <div>
        <h2 class="text-xl font-bold">\${curTable}</h2>
        <p class="text-slate-400 text-sm">\${curData.length} rows</p>
      </div>
      <div class="flex gap-2">
        <input placeholder="Search..." oninput="filterRows(this.value)"
          style="width:200px" class="text-sm"/>
        <button class="btn" style="background:#22c55e;color:#000" onclick="showInsert()">+ Insert</button>
        <button class="btn" style="background:#334155;color:#e2e8f0" onclick="loadTable(curTable)">↻</button>
      </div>
    </div>
    <div style="background:#1e293b;border:1px solid #334155;border-radius:12px;overflow:hidden">
      <div style="overflow-x:auto">
        <table>
          <thead><tr>\${cols.map(c=>\`<th>\${c}</th>\`).join('')}<th>Actions</th></tr></thead>
          <tbody id="tbody">\${curData.map(row=>renderRow(row,cols)).join('')}</tbody>
        </table>
      </div>
    </div>
  \`;
}

function renderRow(row, cols) {
  return \`<tr>
    \${cols.map(c=>\`<td title="\${esc(String(row[c]??''))}">\${esc(String(row[c]??''))}</td>\`).join('')}
    <td class="flex gap-1 py-2">
      <button class="btn" style="background:#334155;color:#e2e8f0;padding:4px 12px" onclick='showEdit(\${JSON.stringify(row)})'>Edit</button>
      <button class="btn" style="background:#ef4444;color:#fff;padding:4px 12px" onclick="delRow('\${row.id}')">Del</button>
    </td>
  </tr>\`;
}

function filterRows(q) {
  const filtered = q ? curData.filter(r=>Object.values(r).some(v=>String(v).toLowerCase().includes(q.toLowerCase()))) : curData;
  const cols = curSchema?.tables?.[curTable] ? Object.keys(curSchema.tables[curTable].columns) : Object.keys(curData[0]||{});
  document.getElementById('tbody').innerHTML = filtered.map(r=>renderRow(r,cols)).join('');
}

function showCreate() {
  modal(\`
    <h3 class="font-bold text-lg mb-4">Create Table</h3>
    <label class="block mb-3"><span class="text-xs text-slate-400 block mb-1">Table Name</span>
      <input id="tName" placeholder="e.g. posts"/></label>
    <label class="block mb-3"><span class="text-xs text-slate-400 block mb-1">Columns (one per line: name:type)</span>
      <textarea id="tCols" rows="5" placeholder="title:string&#10;body:string&#10;author:string&#10;views:number"></textarea></label>
    <div class="flex gap-2 mt-4">
      <button class="btn flex-1" style="background:#22c55e;color:#000" onclick="createTable()">Create</button>
      <button class="btn" style="background:#334155;color:#e2e8f0" onclick="closeModal()">Cancel</button>
    </div>
  \`);
}

async function createTable() {
  const name = document.getElementById('tName').value.trim();
  const raw  = document.getElementById('tCols').value.trim();
  const columns = {};
  raw.split('\\n').forEach(line => {
    const [col, type='string'] = line.split(':').map(s=>s.trim());
    if (col) columns[col] = { type };
  });
  await fetch(BASE+'/rest/v1/schema/tables', {
    method:'POST', headers:{'Content-Type':'application/json'},
    body: JSON.stringify({ table: name, columns })
  });
  closeModal();
  location.reload();
}

function showInsert() {
  const cols = Object.entries(curSchema?.tables?.[curTable]?.columns||{}).filter(([,d])=>!d.auto);
  modal(\`
    <h3 class="font-bold text-lg mb-4">Insert into <span class="text-green-400">\${curTable}</span></h3>
    \${cols.map(([c,d])=>\`
      <label class="block mb-3">
        <span class="text-xs text-slate-400 block mb-1">\${c} <span class="text-slate-500">(\${d.type})</span></span>
        \${d.type==='string'&&(c.includes('body')||c.includes('content')||c.includes('text'))
          ? \`<textarea id="f_\${c}" rows="3"></textarea>\`
          : \`<input id="f_\${c}" placeholder="\${d.type}"/>\`}
      </label>
    \`).join('')}
    <div class="flex gap-2 mt-4">
      <button class="btn flex-1" style="background:#22c55e;color:#000" onclick="insertRow()">Insert</button>
      <button class="btn" style="background:#334155;color:#e2e8f0" onclick="closeModal()">Cancel</button>
    </div>
  \`);
}

async function insertRow() {
  const cols = Object.entries(curSchema?.tables?.[curTable]?.columns||{}).filter(([,d])=>!d.auto);
  const body = {};
  cols.forEach(([c,d])=>{
    const el = document.getElementById('f_'+c);
    if(el?.value) body[c] = d.type==='number' ? Number(el.value) : el.value;
  });
  await fetch(BASE+'/rest/v1/'+curTable, {
    method:'POST', headers:{'Content-Type':'application/json'}, body:JSON.stringify(body)
  });
  closeModal(); loadTable(curTable);
}

function showEdit(row) {
  const cols = Object.entries(curSchema?.tables?.[curTable]?.columns||{}).filter(([c,d])=>!d.auto&&!d.primaryKey);
  modal(\`
    <h3 class="font-bold text-lg mb-4">Edit Row</h3>
    \${cols.map(([c,d])=>\`
      <label class="block mb-3">
        <span class="text-xs text-slate-400 block mb-1">\${c}</span>
        \${d.type==='string'&&(c.includes('body')||c.includes('content')||c.includes('text'))
          ? \`<textarea id="e_\${c}" rows="3">\${esc(String(row[c]??''))}</textarea>\`
          : \`<input id="e_\${c}" value="\${esc(String(row[c]??''))}"/>\`}
      </label>
    \`).join('')}
    <div class="flex gap-2 mt-4">
      <button class="btn flex-1" style="background:#22c55e;color:#000" onclick="updateRow('\${row.id}')">Save</button>
      <button class="btn" style="background:#334155;color:#e2e8f0" onclick="closeModal()">Cancel</button>
    </div>
  \`);
}

async function updateRow(id) {
  const cols = Object.entries(curSchema?.tables?.[curTable]?.columns||{}).filter(([c,d])=>!d.auto&&!d.primaryKey);
  const body = {};
  cols.forEach(([c])=>{ const el=document.getElementById('e_'+c); if(el) body[c]=el.value; });
  await fetch(BASE+'/rest/v1/'+curTable+'?id=eq.'+id, {
    method:'PATCH', headers:{'Content-Type':'application/json'}, body:JSON.stringify(body)
  });
  closeModal(); loadTable(curTable);
}

async function delRow(id) {
  if(!confirm('Delete this row?')) return;
  await fetch(BASE+'/rest/v1/'+curTable+'?id=eq.'+id, {method:'DELETE'});
  loadTable(curTable);
}

function modal(html) {
  document.getElementById('modalBody').innerHTML = html;
  document.getElementById('modal').classList.remove('hidden');
}
function closeModal() { document.getElementById('modal').classList.add('hidden'); }
document.getElementById('modal').addEventListener('click', e => { if(e.target===document.getElementById('modal')) closeModal(); });
function esc(s) { return s.replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;').replace(/"/g,'&quot;'); }
</script>
</body>
</html>`);
});

app.listen(PORT, () => {
  console.log(`🟢 NT DB running on port ${PORT}`);
  console.log(`📋 Dashboard: http://localhost:${PORT}`);
  console.log(`🔌 API:       http://localhost:${PORT}/rest/v1`);
});