const express = require("express");
const router = express.Router();
const db = require("../db");
const fetch = (...args) =>
  import("node-fetch").then(({ default: fetch }) => fetch(...args));

// CREATE - Add new project log
router.post("/", async (req, res) => {
  const {
    log_id,
    user_id,
    project_name,
    type_name,
    status_name,
    invoice_status_name,
    priority_level_name,
    deadline,
    percent_completed,
    estimated_hours,
    actual_hours,
    remarks,
    action,
    created_at,
  } = req.body;

  try {
    const [result] = await db.execute(
      "INSERT INTO project_log (log_id, user_id, project_name, type_name, status_name, invoice_status_name, priority_level_name, deadline, percent_completed, estimated_hours, actual_hours, remarks, action, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        log_id,
        user_id,
        project_name,
        type_name,
        status_name,
        invoice_status_name,
        priority_level_name,
        deadline,
        percent_completed,
        estimated_hours,
        actual_hours,
        remarks,
        action,
        created_at,
      ]
    );
    res.status(201).json({ id: result.insertId });
    console.log(result);
  } catch (err) {
    res.status(500).json({ error: err.message });
    console.log(err);
  }
});

// READ ALL - Get all project logs
router.get("/", async (req, res) => {
  try {
    const [rows] = await db.execute("SELECT * FROM project_log");
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});
// Get project_log for a particular project(ananya)
router.get("/project_log_by_project", async (req, res) => {
  const { project_name } = req.query;
  console.log("🔍 /track_project hit with:", project_name);

  if (!project_name || project_name.trim() === "") {
    return res.status(400).json({ error: "Project name is required" });
  }

  try {
    const [results] = await db.execute(
      `SELECT * FROM project_log WHERE project_name = ? ORDER BY created_at DESC`,
      [project_name]
    );

    console.log("✅ Results:", results);

    if (results.length === 0) {
      return res
        .status(404)
        .json({ message: "No logs found for this project" });
    }

    res.status(200).json(results);
  } catch (err) {
    console.error("❌ DB error:", err.message);
    res.status(500).json({ error: "Internal Server Error" });
  }
});

// Route: Get project logs done by a user(ananya)
router.get("/project_logs_by_user", async (req, res) => {
  const { name } = req.query;

  console.log("📥 /project_logs_by_user hit with name:", name);

  if (!name || name.trim() === "") {
    return res.status(400).json({ error: "Username is required" });
  }

  try {
    // 1. Get user_id from users table
    const [userResult] = await db.execute(
      "SELECT user_id FROM users WHERE username = ?",
      [name]
    );

    if (userResult.length === 0) {
      return res.status(404).json({ error: "User not found" });
    }

    const user_id = userResult[0].user_id;

    // 2. Fetch project logs for this user_id
    const [logs] = await db.execute(
      `SELECT *
       FROM project_log
       WHERE user_id = ?
       ORDER BY created_at DESC`,
      [user_id]
    );

    if (logs.length === 0) {
      return res.status(404).json({ message: "No logs found for this user" });
    }

    res.status(200).json(logs);
  } catch (err) {
    console.error("❌ Error fetching project logs:", err);
    res
      .status(500)
      .json({ error: "Internal server error", details: err.message });
  }
});

// READ BY PROJECT NAME - for tracking a project

// READ ONE - Get a specific log by ID
router.get("/:id", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT * FROM project_log WHERE log_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 - Update a log entry (✅ Fixed Route)
router.put("/update/:id", async (req, res) => {
  const update_fields = [];
  const update_values = [];

  if (req.body.user_id !== undefined) {
    update_fields.push("user_id = ?");
    update_values.push(req.body.user_id);
  }
  if (req.body.project_name !== undefined) {
    update_fields.push("project_name = ?");
    update_values.push(req.body.project_name);
  }
  if (req.body.type_name !== undefined) {
    update_fields.push("type_name = ?");
    update_values.push(req.body.type_name);
  }
  if (req.body.status_name !== undefined) {
    update_fields.push("status_name = ?");
    update_values.push(req.body.status_name);
  }
  if (req.body.invoice_status_name !== undefined) {
    update_fields.push("invoice_status_name = ?");
    update_values.push(req.body.invoice_status_name);
  }
  if (req.body.priority_level_name !== undefined) {
    update_fields.push("priority_level_name = ?");
    update_values.push(req.body.priority_level_name);
  }
  if (req.body.deadline !== undefined) {
    update_fields.push("deadline = ?");
    update_values.push(req.body.deadline);
  }
  if (req.body.percent_completed !== undefined) {
    update_fields.push("percent_completed = ?");
    update_values.push(req.body.percent_completed);
  }
  if (req.body.estimated_hours !== undefined) {
    update_fields.push("estimated_hours = ?");
    update_values.push(req.body.estimated_hours);
  }
  if (req.body.actual_hours !== undefined) {
    update_fields.push("actual_hours = ?");
    update_values.push(req.body.actual_hours);
  }
  if (req.body.remarks !== undefined) {
    update_fields.push("remarks = ?");
    update_values.push(req.body.remarks);
  }
  if (req.body.action !== undefined) {
    update_fields.push("action = ?");
    update_values.push(req.body.action);
  }
  if (req.body.created_at !== undefined) {
    update_fields.push("created_at = ?");
    update_values.push(req.body.created_at);
  }

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

  update_values.push(req.params.id); // Add WHERE clause parameter

  try {
    const [result] = await db.execute(
      `UPDATE project_log SET ${update_fields.join(", ")} WHERE log_id = ?`,
      update_values
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "Not found" });
    res.json({ id: req.params.id });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE - Delete a log entry
router.delete("/:id", async (req, res) => {
  try {
    const [result] = await db.execute(
      "DELETE FROM project_log WHERE log_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;
