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

// CREATE
router.post("/", async (req, res) => {
  const { division_id, role_id } = req.body;
  try {
    await db.execute(
      "INSERT INTO divisions_roles (division_id, role_id) VALUES (?, ?)",
      [division_id, role_id]
    );
    res.status(201).json({ division_id, role_id });
  } 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_roles");
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// READ ONE (by composite key)
router.get("/:division_id/:role_id", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT * FROM divisions_roles WHERE division_id = ? AND role_id = ?",
      [req.params.division_id, req.params.role_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 (by composite key)
router.put("/:division_id/:role_id", async (req, res) => {
  const { division_id, role_id } = req.body;
  try {
    const [result] = await db.execute(
      "UPDATE divisions_roles SET division_id = ?, role_id = ? WHERE division_id = ? AND role_id = ?",
      [division_id, role_id, req.params.division_id, req.params.role_id]
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "Not found" });
    res.json({ division_id, role_id });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE (by composite key)
router.delete("/:division_id/:role_id", async (req, res) => {
  try {
    const [result] = await db.execute(
      "DELETE FROM divisions_roles WHERE division_id = ? AND role_id = ?",
      [req.params.division_id, req.params.role_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;
