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

// Middleware to get current user ID from request
const getCurrentUserId = (req) => {
  // Try to get user_id from request body first
  if (req.body && req.body.user_id) {
    return req.body.user_id;
  }

  // Try to get user_id from request headers
  if (req.headers && req.headers["user-id"]) {
    return parseInt(req.headers["user-id"]);
  }

  // Try to get user_id from query parameters
  if (req.query && req.query.user_id) {
    return parseInt(req.query.user_id);
  }

  // Default fallback - use an existing user from the database
  return 201;
};

// Helper function to create employee log entry
const createEmployeeLog = 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 employee_log (log_id, user_id, employee_name, division_name, role_name, project_names, action, remarks, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        logData.log_id,
        logData.user_id,
        logData.employee_name,
        logData.division_name || null,
        logData.role_name || null,
        logData.project_names || null,
        logData.action,
        logData.remarks || null,
        logData.created_at ||
          new Date().toISOString().slice(0, 19).replace("T", " "),
      ]
    );

    return result;
  } catch (error) {
    throw error;
  }
};

// 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;
  }
};

// Helper function to get employee data with project information
const getEmployeeWithProjects = async (employeeId) => {
  const [rows] = await db.execute(
    "SELECT * FROM employee_project_view WHERE employee_id = ?",
    [employeeId]
  );
  return rows[0];
};

// Helper function to generate unique log ID
const generateLogId = async () => {
  try {
    // Get the maximum log_id from the employee_log table
    const [result] = await db.execute(
      "SELECT MAX(log_id) as max_id FROM employee_log"
    );
    const maxId = result[0].max_id || 0;
    return maxId + 1;
  } catch (error) {
    // Fallback to timestamp-based ID
    return parseInt(Date.now() + Math.floor(Math.random() * 1000));
  }
};

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

// Test endpoint to verify employee log functionality
router.get("/test-log", async (req, res) => {
  try {
    const user_id = getCurrentUserId(req);

    // Test creating a log entry
    const testLogData = {
      log_id: await generateLogId(),
      user_id: user_id,
      employee_name: "Test Employee",
      division_name: "Test Division",
      role_name: "Test Role",
      project_names: "Test Project",
      action: "Test Action",
      remarks: "This is a test log entry",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    await createEmployeeLog(testLogData);

    // Verify the log was created
    const [logs] = await db.execute(
      "SELECT * FROM employee_log WHERE employee_name = ? ORDER BY created_at DESC LIMIT 1",
      ["Test Employee"]
    );

    res.json({
      message: "Employee log test successful",
      test_log: logs[0],
      user_id_used: user_id,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Check employee log table status
router.get("/log-status", async (req, res) => {
  try {
    // Get total count of employee logs
    const [countResult] = await db.execute(
      "SELECT COUNT(*) as total FROM employee_log"
    );

    // Get recent logs
    const [recentLogs] = await db.execute(
      "SELECT * FROM employee_log ORDER BY created_at DESC LIMIT 5"
    );

    // Check if table exists and has correct structure
    const [tableInfo] = await db.execute("DESCRIBE employee_log");

    res.json({
      total_logs: countResult[0].total,
      recent_logs: recentLogs,
      table_structure: tableInfo,
      message: "Employee log table status retrieved successfully",
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// CREATE
router.post("/", async (req, res) => {
  const { employee_id, employee_name, division_id, role_id, remarks } =
    req.body;

  const user_id = getCurrentUserId(req);

  try {
    // Validate required fields
    if (!employee_id || !employee_name || !division_id || !role_id) {
      return res.status(400).json({
        error:
          "Missing required fields: employee_id, employee_name, division_id, role_id",
      });
    }

    // Check if employee already exists
    const [existing] = await db.execute(
      "SELECT employee_id FROM employees WHERE employee_id = ?",
      [employee_id]
    );

    if (existing.length > 0) {
      return res
        .status(409)
        .json({ error: "Employee with this ID already exists" });
    }

    // Insert new employee
    const [result] = await db.execute(
      "INSERT INTO employees (employee_id, employee_name, division_id, role_id, remarks) VALUES (?, ?, ?, ?, ?)",
      [employee_id, employee_name, division_id, role_id, remarks || null]
    );

    // Get updated employee data with project information
    const updated = await getEmployeeWithProjects(employee_id);

    if (!updated) {
      throw new Error("Failed to retrieve created employee data");
    }

    // Prepare log data
    const log_data = {
      log_id: await generateLogId(),
      user_id: user_id,
      employee_name: updated.employee_name,
      division_name: updated.division_name,
      role_name: updated.role_name,
      project_names: updated.project_names || "No projects assigned",
      action: "Employee Created",
      remarks: `New employee created: ${updated.employee_name} (ID: ${employee_id})`,
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create employee log entry
    try {
      await createEmployeeLog(log_data);
    } catch (log_error) {
      // Don't fail the main request if logging fails, but log the error
    }

    res.status(201).json({
      id: result.insertId,
      employee_id: employee_id,
      employee_name,
      division_id,
      role_id,
      remarks,
      message: "Employee created successfully",
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

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

// FILTER EMPLOYEES BY DIVISION AND ROLE
router.get("/filter", async (req, res) => {
  try {
    const { division_id, role_id } = req.query;

    // Determine filter condition using switch case
    let filterCondition;
    let params = [];

    // Convert to boolean for switch case
    const hasDivision =
      division_id && division_id !== "null" && division_id !== "";
    const hasRole = role_id && role_id !== "null" && role_id !== "";

    switch (true) {
      case !hasDivision && !hasRole:
        // division_id = null && role_id = null
        filterCondition = "";
        break;

      case hasDivision && !hasRole:
        // division_id && role_id = null
        filterCondition = "WHERE e.division_id = ?";
        params.push(division_id);
        break;

      case !hasDivision && hasRole:
        // division_id = null && role_id
        filterCondition = "WHERE e.role_id = ?";
        params.push(role_id);
        break;

      case hasDivision && hasRole:
        // division_id && role_id
        filterCondition = "WHERE e.division_id = ? AND e.role_id = ?";
        params.push(division_id, role_id);
        break;

      default:
        filterCondition = "";
        break;
    }

    const query = `
      SELECT 
        e.employee_id,
        e.employee_name,
        d.division_id,
        d.division_name,
        r.role_id,
        r.role_name,
        e.remarks
      FROM employees e
      LEFT JOIN divisions d ON e.division_id = d.division_id
      LEFT JOIN roles r ON e.role_id = r.role_id
      ${filterCondition}
      ORDER BY e.employee_id
    `;

    const [rows] = await db.execute(query, params);
    res.json(rows);
  } catch (err) {
    console.error("Error filtering employees:", err);
    res.status(500).json({ error: err.message });
  }
});

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

// UPDATE
router.put("/:id", async (req, res) => {
  const employeeId = req.params.id;
  const { employee_name, division_id, role_id, remarks } = req.body;

  const user_id = getCurrentUserId(req);

  try {
    // Get current employee data before update
    const currentEmployee = await getEmployeeWithProjects(employeeId);
    if (!currentEmployee) {
      return res.status(404).json({ error: "Employee not found" });
    }

    const update_fields = [];
    const update_values = [];
    const changes = [];

    // Track what fields are being updated
    if (
      employee_name !== undefined &&
      employee_name !== currentEmployee.employee_name
    ) {
      update_fields.push("employee_name = ?");
      update_values.push(employee_name);
      changes.push(`Name: ${currentEmployee.employee_name} → ${employee_name}`);
    }

    if (
      division_id !== undefined &&
      division_id !== currentEmployee.division_id
    ) {
      update_fields.push("division_id = ?");
      update_values.push(division_id);
      changes.push(
        `Division: ${currentEmployee.division_name} → [New Division ID: ${division_id}]`
      );
    }

    if (role_id !== undefined && role_id !== currentEmployee.role_id) {
      update_fields.push("role_id = ?");
      update_values.push(role_id);
      changes.push(
        `Role: ${currentEmployee.role_name} → [New Role ID: ${role_id}]`
      );
    }

    if (remarks !== undefined && remarks !== currentEmployee.remarks) {
      update_fields.push("remarks = ?");
      update_values.push(remarks);
      changes.push(
        `Remarks: ${currentEmployee.remarks || "None"} → ${remarks || "None"}`
      );
    }

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

    update_values.push(employeeId); // Add the WHERE clause parameter

    // Update the employee
    const [result] = await db.execute(
      `UPDATE employees SET ${update_fields.join(", ")} WHERE employee_id = ?`,
      update_values
    );

    if (result.affectedRows === 0) {
      return res
        .status(404)
        .json({ error: "Employee not found or no changes made" });
    }

    // Get updated employee data
    const updated = await getEmployeeWithProjects(employeeId);
    if (!updated) {
      throw new Error("Failed to retrieve updated employee data");
    }

    // Prepare log data with detailed change information
    const log_data = {
      log_id: await generateLogId(),
      user_id: user_id,
      employee_name: updated.employee_name,
      division_name: updated.division_name,
      role_name: updated.role_name,
      project_names: updated.project_names || "No projects assigned",
      action: "Employee Details Updated",
      remarks: `Changes made: ${changes.join(", ")}`,
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create employee log entry
    try {
      await createEmployeeLog(log_data);
    } catch (log_error) {
      // Don't fail the main request if logging fails
    }

    res.json({
      id: employeeId,
      employee_name: updated.employee_name,
      changes: changes,
      message: "Employee updated successfully",
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE
router.delete("/:id", async (req, res) => {
  const employeeId = req.params.id;
  const user_id = getCurrentUserId(req);

  try {
    // Check if employee is a user (admin) - users cannot be deleted
    const [userCheck] = await db.execute(
      "SELECT user_id FROM users WHERE employee_id = ?",
      [employeeId]
    );
    
    if (userCheck.length > 0) {
      return res.status(400).json({ 
        error: "Users can't be Deleted",
        isUser: true 
      });
    }

    // Check if employee is assigned to any projects
    const [projectCheck] = await db.execute(
      "SELECT project_id FROM employees_projects WHERE employee_id = ?",
      [employeeId]
    );
    
    if (projectCheck.length > 0) {
      return res.status(400).json({ 
        error: "Cannot delete an employee assigned to a project. Remove the employee from all projects before deleting.",
        hasProjects: true 
      });
    }

    // Get employee data before deletion for logging
    const employee = await getEmployeeWithProjects(employeeId);
    if (!employee) {
      return res.status(404).json({ error: "Employee not found" });
    }

    // Prepare log data
    const log_data = {
      log_id: await generateLogId(),
      user_id: user_id,
      employee_name: employee.employee_name,
      division_name: employee.division_name,
      role_name: employee.role_name,
      project_names: employee.project_names || "No projects assigned",
      action: "Employee Deleted",
      remarks: `Employee ${employee.employee_name} (ID: ${employeeId}) was deleted`,
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Delete the employee
    const [result] = await db.execute(
      "DELETE FROM employees WHERE employee_id = ?",
      [employeeId]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Employee not found" });
    }

    res.json({
      message: "Employee deleted successfully",
      deleted_employee: {
        employee_id: employeeId,
        employee_name: employee.employee_name,
      },
    });

    // Create employee log entry before deletion
    try {
      await createEmployeeLog(log_data);
    } catch (log_error) {
      // Continue with deletion even if logging fails
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
