server.js

const express = require("express");
const axios = require("axios");
const swaggerUi = require("swagger-ui-express");
const swaggerJsdoc = require("swagger-jsdoc");
require("dotenv").config();

const app = express();
app.use(express.json());

// Swagger configuration
const swaggerOptions = {
  definition: {
    openapi: "3.0.0",
    info: {
      title: "SQLite Express API",
      version: "1.0.0",
      description: "Express.js server connected to remote SQLite database",
    },
    servers: [
      {
        url: `http://localhost:${process.env.PORT || 3000}`,
        description: "Development server",
      },
    ],
  },
  apis: ["./server.js"],
};

const swaggerSpec = swaggerJsdoc(swaggerOptions);
app.use("/swagger", swaggerUi.serve, swaggerUi.setup(swaggerSpec));
app.use("/api-docs", swaggerUi.serve, swaggerUi.setup(swaggerSpec));

// Environment variables
const SQLITE_SERVER = process.env.DB_URL || "http://localhost:8000";
const DATABASE = process.env.DATABASE || "default";
const PORT = process.env.PORT || 3000;

console.log(`🔗 Connected to SQLite Server: ${SQLITE_SERVER}`);
console.log(`📦 Using Database: ${DATABASE}`);

// Helper function for SQLite API calls
async function querySQLite(sql, method = "POST", endpoint = "/api/v1/query") {
  try {
    const payload = { sql, project: DATABASE };
    const response = await axios({
      method,
      url: `${SQLITE_SERVER}${endpoint}`,
      data: payload,
      headers: { "Content-Type": "application/json" },
    });

    return response.data;
  } catch (err) {
    console.error("SQLite Query Error:", err.message);
    throw err;
  }
}

// Health check
/**
 * @swagger
 * /health:
 *   get:
 *     summary: Health check
 *     description: Check if server and database are healthy
 *     responses:
 *       200:
 *         description: Server is healthy
 *       500:
 *         description: Server error
 */
app.get("/health", async (req, res) => {
  try {
    const response = await axios.get(`${SQLITE_SERVER}/api/v1/heartbeat`);
    if (response.status === 200) {
      res.status(200).json({ status: "healthy", database: DATABASE });
    } else {
      res.status(500).json({ status: "unhealthy" });
    }
  } catch (err) {
    res.status(500).json({ status: "error", message: err.message });
  }
});

// Get all products
/**
 * @swagger
 * /api/products:
 *   get:
 *     summary: Get all products
 *     description: Retrieve all products from the database
 *     responses:
 *       200:
 *         description: List of products
 *       500:
 *         description: Failed to fetch products
 */
app.get("/api/products", async (req, res) => {
  try {
    const result = await querySQLite(
      "SELECT * FROM products",
      "POST",
      "/api/v1/query",
    );
    res.status(200).json(result.data || []);
  } catch (err) {
    console.error("GET /api/products error:", err.message);
    res.status(500).json({ error: err.message || "Failed to fetch products" });
  }
});

// Get product by ID
/**
 * @swagger
 * /api/products/{id}:
 *   get:
 *     summary: Get product by ID
 *     description: Retrieve a specific product
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *     responses:
 *       200:
 *         description: Product found
 *       404:
 *         description: Product not found
 */
app.get("/api/products/:id", async (req, res) => {
  try {
    const result = await querySQLite(
      `SELECT * FROM products WHERE id = ${req.params.id}`,
      "POST",
      "/api/v1/query",
    );
    if (result.data && result.data.length > 0) {
      res.status(200).json(result.data[0]);
    } else {
      res.status(404).json({ error: "Product not found" });
    }
  } catch (err) {
    res.status(500).json({ error: "Database query failed" });
  }
});

// Create product
/**
 * @swagger
 * /api/products:
 *   post:
 *     summary: Create a new product
 *     description: Add a new product to the database
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name:
 *                 type: string
 *               price:
 *                 type: number
 *               stock:
 *                 type: integer
 *     responses:
 *       201:
 *         description: Product created
 *       400:
 *         description: Missing required fields
 */
app.post("/api/products", async (req, res) => {
  try {
    const { name, price, stock } = req.body;

    if (!name || price === undefined) {
      return res
        .status(400)
        .json({ error: "Missing required fields: name, price" });
    }

    const sql = `INSERT INTO products (name, price, stock) VALUES ('${name}', ${price}, ${stock || 0})`;
    const result = await querySQLite(sql, "POST", "/api/v1/execute");

    res.status(201).json({
      status: "created",
      message: "Product created successfully",
      rows_affected: result.rows_affected,
    });
  } catch (err) {
    res.status(500).json({ error: "Failed to create product" });
  }
});

// Update product
/**
 * @swagger
 * /api/products/{id}:
 *   put:
 *     summary: Update a product
 *     description: Update product information
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *     requestBody:
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name:
 *                 type: string
 *               price:
 *                 type: number
 *               stock:
 *                 type: integer
 *     responses:
 *       200:
 *         description: Product updated
 */
app.put("/api/products/:id", async (req, res) => {
  try {
    const { name, price, stock } = req.body;
    const updates = [];

    if (name) updates.push(`name = '${name}'`);
    if (price !== undefined) updates.push(`price = ${price}`);
    if (stock !== undefined) updates.push(`stock = ${stock}`);

    if (updates.length === 0) {
      return res.status(400).json({ error: "No fields to update" });
    }

    const sql = `UPDATE products SET ${updates.join(", ")} WHERE id = ${req.params.id}`;
    const result = await querySQLite(sql, "POST", "/api/v1/execute");

    res.status(200).json({
      status: "updated",
      message: "Product updated successfully",
      rows_affected: result.rows_affected,
    });
  } catch (err) {
    res.status(500).json({ error: "Failed to update product" });
  }
});

// Delete product
/**
 * @swagger
 * /api/products/{id}:
 *   delete:
 *     summary: Delete a product
 *     description: Remove a product from the database
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *     responses:
 *       200:
 *         description: Product deleted
 */
app.delete("/api/products/:id", async (req, res) => {
  try {
    const sql = `DELETE FROM products WHERE id = ${req.params.id}`;
    const result = await querySQLite(sql, "POST", "/api/v1/execute");

    res.status(200).json({
      status: "deleted",
      message: "Product deleted successfully",
      rows_affected: result.rows_affected,
    });
  } catch (err) {
    res.status(500).json({ error: "Failed to delete product" });
  }
});

// Get all orders
/**
 * @swagger
 * /api/orders:
 *   get:
 *     summary: Get all orders
 *     description: Retrieve all orders from the database
 *     responses:
 *       200:
 *         description: List of orders
 */
app.get("/api/orders", async (req, res) => {
  try {
    const result = await querySQLite(
      "SELECT * FROM orders",
      "POST",
      "/api/v1/query",
    );
    res.status(200).json(result.data || []);
  } catch (err) {
    res.status(500).json({ error: "Failed to fetch orders" });
  }
});

// Create order
/**
 * @swagger
 * /api/orders:
 *   post:
 *     summary: Create a new order
 *     description: Add a new order to the database
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               customer_name:
 *                 type: string
 *               total:
 *                 type: number
 *               status:
 *                 type: string
 *     responses:
 *       201:
 *         description: Order created
 */
app.post("/api/orders", async (req, res) => {
  try {
    const { customer_name, total, status } = req.body;

    if (!customer_name) {
      return res
        .status(400)
        .json({ error: "Missing required field: customer_name" });
    }

    const sql = `INSERT INTO orders (customer_name, total, status) VALUES ('${customer_name}', ${total || 0}, '${status || "pending"}')`;
    const result = await querySQLite(sql, "POST", "/api/v1/execute");

    res.status(201).json({
      status: "created",
      message: "Order created successfully",
      rows_affected: result.rows_affected,
    });
  } catch (err) {
    res.status(500).json({ error: "Failed to create order" });
  }
});

// Get database info
/**
 * @swagger
 * /api/info:
 *   get:
 *     summary: Get database info
 *     description: Retrieve database schema and server information
 *     responses:
 *       200:
 *         description: Database information
 */
app.get("/api/info", async (req, res) => {
  try {
    const response = await axios.get(
      `${SQLITE_SERVER}/api/v1/schema?project=${DATABASE}`,
    );
    res.status(200).json({
      server: SQLITE_SERVER,
      database: DATABASE,
      schema: response.data.schema || {},
    });
  } catch (err) {
    res.status(500).json({ error: "Failed to fetch database info" });
  }
});

app.listen(PORT, () => {
  console.log(`\n✅ Express Server running on port ${PORT}`);
  console.log(`📍 API: http://localhost:${PORT}`);
  console.log(`🔗 SQLite Server: ${SQLITE_SERVER}`);
  console.log(`📦 Database: ${DATABASE}\n`);
});