const express = require("express");
const router = express.Router();
const db = require("../db");

// GET total department count
router.get("/count", async (req, res) => {
  try {
    const [rows] = await db.execute("SELECT COUNT(*) as total_departments FROM divisions");
    res.json({
      total_departments: rows[0].total_departments
    });
  } catch (err) {
    console.error("Error getting total department count:", err);
    res.status(500).json({ error: err.message });
  }
});

// CREATE
router.post("/", async (req, res) => {
  const { division_name } = req.body;
  try {
    const [result] = await db.execute(
      "INSERT INTO divisions (division_name) VALUES (?)",
      [division_name]
    );
    res.status(201).json({ id: result.insertId, division_name });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// READ ALL
router.get("/", async (req, res) => {
  try {
    const [rows] = await db.execute("SELECT * FROM divisions");
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// READ ONE
router.get("/:id", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT * FROM divisions WHERE division_id = ?",
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: "Not found" });
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// UPDATE
router.put("/:id", async (req, res) => {
  const { division_name } = req.body;
  try {
    const [result] = await db.execute(
      "UPDATE divisions SET division_name = ? WHERE division_id = ?",
      [division_name, req.params.id]
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "Not found" });
    res.json({ id: req.params.id, division_name });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE
router.delete("/:id", async (req, res) => {
  try {
    const [result] = await db.execute(
      "DELETE FROM divisions WHERE division_id = ?",
      [req.params.id]
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "Not found" });
    res.json({ message: "Deleted" });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
