server.js

import express from 'express';
import cors from 'cors';
import bodyParser from 'body-parser';
import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import path from 'path';
import fs from 'fs';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const app = express();
const PORT = 3000;

app.use(cors());
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));

let db;

const initDatabase = () => {
  return new Promise((resolve, reject) => {
    db = new sqlite3.Database(':memory:', (err) => {
      if (err) reject(err);
      
      db.serialize(() => {
        // Create sample tables
        db.run(`
          CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            email TEXT NOT NULL,
            age INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
          )
        `);

        db.run(`
          CREATE TABLE products (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            category TEXT,
            stock INTEGER
          )
        `);

        db.run(`
          CREATE TABLE orders (
            id INTEGER PRIMARY KEY,
            user_id INTEGER,
            product_id INTEGER,
            quantity INTEGER,
            order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(id),
            FOREIGN KEY(product_id) REFERENCES products(id)
          )
        `);

        // Insert sample data
        const insertUsers = db.prepare('INSERT INTO users (username, email, age) VALUES (?, ?, ?)');
        insertUsers.run('john_doe', 'john@example.com', 28);
        insertUsers.run('jane_smith', 'jane@example.com', 32);
        insertUsers.run('bob_johnson', 'bob@example.com', 25);
        insertUsers.finalize();

        const insertProducts = db.prepare('INSERT INTO products (name, price, category, stock) VALUES (?, ?, ?, ?)');
        insertProducts.run('Laptop', 999.99, 'Electronics', 15);
        insertProducts.run('Mouse', 29.99, 'Electronics', 50);
        insertProducts.run('Keyboard', 79.99, 'Electronics', 30);
        insertProducts.run('Monitor', 299.99, 'Electronics', 10);
        insertProducts.finalize();

        const insertOrders = db.prepare('INSERT INTO orders (user_id, product_id, quantity) VALUES (?, ?, ?)');
        insertOrders.run(1, 1, 1);
        insertOrders.run(2, 2, 2);
        insertOrders.run(1, 3, 1);
        insertOrders.finalize();

        resolve();
      });
    });
  });
};

app.post('/api/query', (req, res) => {
  const { query } = req.body;

  if (!query || query.trim() === '') {
    return res.status(400).json({ error: 'Query is required' });
  }

  const sqlQuery = query.trim();
  const upperQuery = sqlQuery.toUpperCase();

  // Security: Only allow SELECT, and prevent dangerous operations
  if (!upperQuery.startsWith('SELECT') && !upperQuery.startsWith('PRAGMA')) {
    return res.status(403).json({ error: 'Only SELECT queries are allowed for safety' });
  }

  if (upperQuery.includes('DROP') || upperQuery.includes('DELETE') || upperQuery.includes('UPDATE') || upperQuery.includes('INSERT')) {
    return res.status(403).json({ error: 'Destructive operations are not allowed' });
  }

  if (upperQuery.includes('ATTACH') || upperQuery.includes('DETACH')) {
    return res.status(403).json({ error: 'File operations are not allowed' });
  }

  db.all(sqlQuery, [], (err, rows) => {
    if (err) {
      return res.json({ error: err.message });
    }
    res.json({ result: rows, success: true });
  });
});

app.get('/api/schema', (req, res) => {
  db.all("SELECT name FROM sqlite_master WHERE type='table'", [], (err, tables) => {
    if (err) {
      return res.json({ error: err.message });
    }

    const schema = {};
    let completed = 0;

    if (tables.length === 0) {
      return res.json({ schema: {} });
    }

    tables.forEach(table => {
      db.all(`PRAGMA table_info(${table.name})`, [], (err, columns) => {
        if (!err) {
          schema[table.name] = columns;
        }
        completed++;
        if (completed === tables.length) {
          res.json({ schema });
        }
      });
    });
  });
});

initDatabase().then(() => {
  app.listen(PORT, () => {
    console.log(`🚀 SQL Learn App running at http://localhost:${PORT}`);
    console.log('📚 Open this URL in your browser to start learning SQL!');
  });
}).catch(err => {
  console.error('Failed to initialize database:', err);
  process.exit(1);
});