const express = require("express");
const router = express.Router();
const db = require("../db");
const { buildApiUrl, API_ENDPOINTS } = require("../config");

// Helper function to generate unique log ID for project logs
const generateProjectLogId = async () => {
  try {
    const [result] = await db.execute(
      "SELECT MAX(log_id) as max_id FROM project_log"
    );
    const maxId = result[0].max_id || 0;
    return maxId + 1;
  } catch (error) {
    return parseInt(Date.now() + Math.floor(Math.random() * 1000));
  }
};

// Helper function to create project log entry
const createProjectLog = async (logData) => {
  try {
    // First, verify that the user exists
    const [userCheck] = await db.execute(
      "SELECT user_id FROM users WHERE user_id = ?",
      [logData.user_id]
    );

    if (userCheck.length === 0) {
      logData.user_id = 201;

      // Check if default user exists
      const [defaultUserCheck] = await db.execute(
        "SELECT user_id FROM users WHERE user_id = 201"
      );

      if (defaultUserCheck.length === 0) {
        throw new Error("No valid user found for logging");
      }
    }

    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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        logData.log_id,
        logData.user_id,
        logData.project_name,
        logData.type_name || null,
        logData.status_name || null,
        logData.invoice_status_name || null,
        logData.priority_level_name || null,
        logData.deadline || null,
        logData.percent_completed || null,
        logData.estimated_hours || null,
        logData.actual_hours || null,
        logData.remarks || null,
        logData.action,
        logData.created_at ||
          new Date().toISOString().slice(0, 19).replace("T", " "),
      ]
    );
    
    return result;
  } catch (error) {
    throw error;
  }
};

// CREATE
router.post("/", async (req, res) => {
  const { employee_id, project_id } = req.body;
  try {
    await db.execute(
      "INSERT INTO employees_projects (employee_id, project_id) VALUES (?, ?)",
      [employee_id, project_id]
    );

    // Get updated employee data for employee log
    const [updated_project] = await db.execute(
      "SELECT * FROM employee_project_view WHERE employee_id = ?",
      [employee_id]
    );
    const updated = updated_project[0];
    
    // Get project data for project log
    const [project_data] = await db.execute(
      "SELECT * FROM project_view WHERE project_id = ?",
      [project_id]
    );
    const project = project_data[0];
    
    // Prepare employee log data
    const employee_log_data = {
      log_id: parseInt(employee_id + Math.random() * 1000),
      user_id: req.body.user_id,
      employee_name: updated.employee_name,
      division_name: updated.division_name,
      role_name: updated.role_name,
      project_names: updated.project_names,
      action: "Employee Assigned to Project",
      remarks: updated.remarks,
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create employee log entry
    try {
      await fetch(buildApiUrl(API_ENDPOINTS.EMPLOYEE_LOG), {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(employee_log_data),
      });
    } catch (log_error) {
      console.error("Error logging employee update:", log_error);
      // Don't fail the main request if logging fails
    }

    // Prepare project log data
    const project_log_data = {
      log_id: await generateProjectLogId(),
      user_id: req.body.user_id,
      project_name: project.project_name,
      type_name: project.type_name,
      status_name: project.status_name,
      invoice_status_name: project.invoice_status_name,
      priority_level_name: project.priority_level_name,
      deadline: project.deadline
        ? project.deadline.toISOString().slice(0, 19).replace("T", " ")
        : null,
      percent_completed: project.percent_completed,
      estimated_hours: project.estimated_hours,
      actual_hours: project.actual_hours,
      remarks: `Employee ${updated.employee_name} (${updated.role_name}) assigned to project`,
      action: "Employee Added to Project",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create project log entry
    try {
      await createProjectLog(project_log_data);
    } catch (log_error) {
      // Don't fail the main request if logging fails
    }

    res.status(201).json({ employee_id, project_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 employees_projects");
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// READ ONE (by composite key)
router.get("/:employee_id/:project_id", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT * FROM employees_projects WHERE employee_id = ? AND project_id = ?",
      [req.params.employee_id, req.params.project_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("/:employee_id/:project_id", async (req, res) => {
  const update_fields = [];
  const update_values = [];

  // Only include fields that are provided in the request body
  if (req.body.employee_id !== undefined) {
    update_fields.push("employee_id = ?");
    update_values.push(req.body.employee_id);
  }
  if (req.body.project_id !== undefined) {
    update_fields.push("project_id = ?");
    update_values.push(req.body.project_id);
  }

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

  // Add the WHERE clause parameters
  update_values.push(req.params.employee_id);
  update_values.push(req.params.project_id);

  try {
    const [result] = await db.execute(
      `UPDATE employees_projects SET ${update_fields.join(
        ", "
      )} WHERE employee_id = ? AND project_id = ?`,
      update_values
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "Not found" });

    // Get the updated employee data for logging
    const updated_employee_id = req.params.employee_id;
    const [updated_project] = await db.execute(
      "SELECT * FROM employee_project_view WHERE employee_id = ?",
      [req.params.employee_id]
    );
    const updated = updated_project[0];

    // Get project data for project log
    const [project_data] = await db.execute(
      "SELECT * FROM project_view WHERE project_id = ?",
      [req.params.project_id]
    );
    const project = project_data[0];

    // Prepare employee log data
    const employee_log_data = {
      log_id: parseInt(updated_employee_id + Math.random() * 1000),
      user_id: req.body.user_id,
      employee_name: updated.employee_name,
      division_name: updated.division_name,
      role_name: updated.role_name,
      project_names: updated.project_names,
      action: "Employee Project Assignment Updated",
      remarks: updated.remarks,
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create employee log entry
    try {
      await fetch(buildApiUrl(API_ENDPOINTS.EMPLOYEE_LOG), {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(employee_log_data),
      });
    } catch (log_error) {
      // Logging error
    }

    // Prepare project log data
    const project_log_data = {
      log_id: await generateProjectLogId(),
      user_id: req.body.user_id,
      project_name: project.project_name,
      type_name: project.type_name,
      status_name: project.status_name,
      invoice_status_name: project.invoice_status_name,
      priority_level_name: project.priority_level_name,
      deadline: project.deadline
        ? project.deadline.toISOString().slice(0, 19).replace("T", " ")
        : null,
      percent_completed: project.percent_completed,
      estimated_hours: project.estimated_hours,
      actual_hours: project.actual_hours,
      remarks: `Employee ${updated.employee_name} (${updated.role_name}) project assignment updated`,
      action: "Employee Project Assignment Updated",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create project log entry
    try {
      await createProjectLog(project_log_data);
    } catch (log_error) {
      // Logging error
    }

    res.json({
      employee_id: req.body.employee_id || req.params.employee_id,
      project_id: req.body.project_id || req.params.project_id,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE (by composite key)
router.delete("/:employee_id/:project_id", async (req, res) => {
  try {
    // Get employee data before deletion for employee log
    const [updated_project] = await db.execute(
      "SELECT * FROM employee_project_view WHERE employee_id = ?",
      [req.params.employee_id]
    );
    const updated = updated_project[0];
    
    // Get project data for project log
    const [project_data] = await db.execute(
      "SELECT * FROM project_view WHERE project_id = ?",
      [req.params.project_id]
    );
    const project = project_data[0];
    
    // Prepare employee log data
    const employee_log_data = {
      log_id: parseInt(req.params.employee_id + Math.random() * 1000),
      user_id: req.body.user_id,
      employee_name: updated.employee_name,
      division_name: updated.division_name,
      role_name: updated.role_name,
      project_names: updated.project_names,
      action: "Employee Removed from Project",
      remarks: updated.remarks,
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create employee log entry
    try {
      await fetch(buildApiUrl(API_ENDPOINTS.EMPLOYEE_LOG), {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(employee_log_data),
      });
    } catch (log_error) {
      // Don't fail the main request if logging fails
    }

    // Prepare project log data
    const project_log_data = {
      log_id: await generateProjectLogId(),
      user_id: req.body.user_id,
      project_name: project.project_name,
      type_name: project.type_name,
      status_name: project.status_name,
      invoice_status_name: project.invoice_status_name,
      priority_level_name: project.priority_level_name,
      deadline: project.deadline
        ? project.deadline.toISOString().slice(0, 19).replace("T", " ")
        : null,
      percent_completed: project.percent_completed,
      estimated_hours: project.estimated_hours,
      actual_hours: project.actual_hours,
      remarks: `Employee ${updated.employee_name} (${updated.role_name}) removed from project`,
      action: "Employee Removed from Project",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create project log entry
    try {
      await createProjectLog(project_log_data);
    } catch (log_error) {
      // Don't fail the main request if logging fails
    }

    const [result] = await db.execute(
      "DELETE FROM employees_projects WHERE employee_id = ? AND project_id = ?",
      [req.params.employee_id, req.params.project_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;
