server.js

const express = require("express");
const mysql = require("mysql2/promise");

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

const PORT = process.env.PORT || 8080;

function getDbConfig() {
  if (process.env.DATABASE_URL) {
    const url = new URL(process.env.DATABASE_URL);
    return {
      host: url.hostname,
      port: Number(url.port) || 3306,
      user: url.username,
      password: url.password,
      database: url.pathname.replace("/", ""),
      ssl: url.searchParams.get("sslmode") === "require" ? {} : undefined,
    };
  }
  return {
    host: process.env.MYSQL_HOST || "localhost",
    port: Number(process.env.MYSQL_PORT) || 3306,
    user: process.env.MYSQL_USER || "root",
    password: process.env.MYSQL_PASSWORD || "",
    database: process.env.MYSQL_DATABASE || "myapi",
  };
}

let pool;

async function initDb() {
  pool = mysql.createPool({ ...getDbConfig(), waitForConnections: true, connectionLimit: 10 });
  await pool.execute(`
    CREATE TABLE IF NOT EXISTS tasks (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      done BOOLEAN DEFAULT FALSE,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);
  console.log("Database initialised – tasks table ready");
}

// --- Health ---

app.get("/health", async (_req, res) => {
  try {
    await pool.execute("SELECT 1");
    res.json({ status: "ok" });
  } catch (err) {
    res.status(503).json({ status: "error", message: err.message });
  }
});

// --- CRUD ---

app.get("/api/tasks", async (_req, res) => {
  const [rows] = await pool.execute("SELECT * FROM tasks ORDER BY created_at DESC");
  res.json(rows);
});

app.post("/api/tasks", async (req, res) => {
  const { title } = req.body;
  if (!title) return res.status(400).json({ error: "title is required" });
  const [result] = await pool.execute("INSERT INTO tasks (title) VALUES (?)", [title]);
  const [rows] = await pool.execute("SELECT * FROM tasks WHERE id = ?", [result.insertId]);
  res.status(201).json(rows[0]);
});

app.get("/api/tasks/:id", async (req, res) => {
  const [rows] = await pool.execute("SELECT * FROM tasks WHERE id = ?", [req.params.id]);
  if (rows.length === 0) return res.status(404).json({ error: "not found" });
  res.json(rows[0]);
});

app.put("/api/tasks/:id", async (req, res) => {
  const { title, done } = req.body;
  const [existing] = await pool.execute("SELECT * FROM tasks WHERE id = ?", [req.params.id]);
  if (existing.length === 0) return res.status(404).json({ error: "not found" });

  const newTitle = title ?? existing[0].title;
  const newDone = done ?? existing[0].done;
  await pool.execute("UPDATE tasks SET title = ?, done = ? WHERE id = ?", [newTitle, newDone, req.params.id]);
  const [rows] = await pool.execute("SELECT * FROM tasks WHERE id = ?", [req.params.id]);
  res.json(rows[0]);
});

app.delete("/api/tasks/:id", async (req, res) => {
  const [result] = await pool.execute("DELETE FROM tasks WHERE id = ?", [req.params.id]);
  if (result.affectedRows === 0) return res.status(404).json({ error: "not found" });
  res.status(204).end();
});

// --- Start ---

initDb()
  .then(() => {
    app.listen(PORT, () => console.log(`API listening on :${PORT}`));
  })
  .catch((err) => {
    console.error("Failed to initialise database:", err.message);
    process.exit(1);
  });