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

// Helper function to generate unique log ID
const generateLogId = async () => {
  try {
    // Get the maximum log_id from the project_log table
    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) {
    // Fallback to timestamp-based ID
    return parseInt(Date.now() + Math.floor(Math.random() * 1000));
  }
};

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

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

// CREATE
router.post("/", async (req, res) => {
  const {
    project_id,
    project_name,
    project_lead_emp_id,
    type_id,
    status_id,
    invoice_status_id,
    priority_level_id,
    project_start_date,
    deadline,
    percent_completed,
    estimated_hours,
    actual_hours,
    remarks,
    division_id,
    po_status,
  } = req.body;
  try {
    // Map PO Status string to ID
    let po_status_id = null;
    if (po_status) {
      const [poStatusResult] = await db.execute(
        "SELECT id FROM po_status WHERE state = ?",
        [po_status]
      );
      if (poStatusResult.length > 0) {
        po_status_id = poStatusResult[0].id;
      }
    }

    // Insert into projects table
    const [result] = await db.execute(
      "INSERT INTO projects (project_id,project_name, project_lead_emp_id, type_id, status_id, invoice_status_id, priority_level_id, project_start_date, deadline, percent_completed, estimated_hours, actual_hours, remarks, division_id, po_status_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        project_id,
        project_name,
        project_lead_emp_id,
        type_id,
        status_id,
        invoice_status_id,
        priority_level_id,
        project_start_date,
        deadline,
        percent_completed,
        estimated_hours,
        actual_hours,
        remarks,
        division_id,
        po_status_id,
      ]
    );

    // Prepare log data
    const [updatedProject] = await db.execute(
      "SELECT * FROM project_view WHERE project_id = ?",
      [req.body.project_id]
    );
    const updated = updatedProject[0];

    // Prepare log data with complete updated project state
    const logData = {
      log_id: await generateLogId(),
      user_id: getCurrentUserId(req),
      project_name: updated.project_name,
      type_name: updated.type_name,
      status_name: updated.status_name,
      invoice_status_name: updated.invoice_status_name,
      priority_level_name: updated.priority_level_name,
      deadline: updated.deadline
        ? updated.deadline.toISOString().slice(0, 19).replace("T", " ")
        : null,
      percent_completed: updated.percent_completed,
      estimated_hours: updated.estimated_hours,
      actual_hours: updated.actual_hours,
      remarks: updated.remarks,
      action: "Project Created",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    // Create project log entry
    await createProjectLog(logData);

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

// GET projects with NULL kickoff_form
router.get("/missing-forms", async (req, res) => {
  try {
    const [rows] = await db.execute(`
      SELECT 
        p.project_name,
        'NULL' as status,
        COALESCE(e.employee_name, 'Not Assigned') as project_lead_name
      FROM projects p
      LEFT JOIN employees e ON p.project_lead_emp_id = e.employee_id
      WHERE p.kickoff_form IS NULL
      ORDER BY p.project_name
    `);

    res.json(rows);
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message,
    });
  }
});

// GET all projects with form submission status
router.get("/with-form-status", async (req, res) => {
  try {
    const [rows] = await db.execute(`
      SELECT 
        p.project_id,
        p.project_name,
        p.project_lead_emp_id,
        p.type_id,
        p.status_id,
        p.invoice_status_id,
        p.priority_level_id,
        p.project_start_date,
        p.deadline,
        p.percent_completed,
        p.estimated_hours,
        p.actual_hours,
        p.remarks,
        p.division_id,
        p.kickoff_form,
        CASE 
          WHEN p.kickoff_form IS NULL THEN false 
          ELSE true 
        END as form_submitted
      FROM projects p
      ORDER BY p.project_name
    `);

    res.json({
      success: true,
      count: rows.length,
      projects: rows,
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message,
    });
  }
});

// READ ONE
router.get("/:id", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT * FROM projects WHERE project_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 update_fields = [];
  const update_values = [];

  // Only include fields that are provided in the request body
  if (req.body.project_name !== undefined) {
    update_fields.push("project_name = ?");
    update_values.push(req.body.project_name);
  }
  if (req.body.project_lead_emp_id !== undefined) {
    update_fields.push("project_lead_emp_id = ?");
    update_values.push(req.body.project_lead_emp_id);
  }
  if (req.body.type_id !== undefined) {
    update_fields.push("type_id = ?");
    update_values.push(req.body.type_id);
  }
  if (req.body.status_id !== undefined) {
    update_fields.push("status_id = ?");
    update_values.push(req.body.status_id);
  }
  if (req.body.invoice_status_id !== undefined) {
    update_fields.push("invoice_status_id = ?");
    update_values.push(req.body.invoice_status_id);
  }
  if (req.body.priority_level_id !== undefined) {
    update_fields.push("priority_level_id = ?");
    update_values.push(req.body.priority_level_id);
  }
  if (req.body.project_start_date !== undefined) {
    update_fields.push("project_start_date = ?");
    update_values.push(req.body.project_start_date);
  }
  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.division_id !== undefined) {
    update_fields.push("division_id = ?");
    update_values.push(req.body.division_id);
  }
  if (req.body.po_status !== undefined) {
    // Map PO Status string to ID
    let po_status_id = null;
    if (req.body.po_status) {
      const [poStatusResult] = await db.execute(
        "SELECT id FROM po_status WHERE state = ?",
        [req.body.po_status]
      );
      if (poStatusResult.length > 0) {
        po_status_id = poStatusResult[0].id;
      }
    }
    update_fields.push("po_status_id = ?");
    update_values.push(po_status_id);
  }

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

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

  try {
    // First, get the current project data to use in log
    const [current_project] = await db.execute(
      "SELECT * FROM projects WHERE project_id = ?",
      [req.params.id]
    );
    if (current_project.length === 0) {
      return res.status(404).json({ error: "Project not found" });
    }

    const project = current_project[0];

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

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

    // Prepare log data with complete updated project state
    const log_data = {
      log_id: await generateLogId(),
      user_id: getCurrentUserId(req),
      project_name: updated.project_name,
      type_name: updated.type_name,
      status_name: updated.status_name,
      invoice_status_name: updated.invoice_status_name,
      priority_level_name: updated.priority_level_name,
      deadline: updated.deadline
        ? updated.deadline.toISOString().slice(0, 19).replace("T", " ")
        : null,
      percent_completed: updated.percent_completed,
      estimated_hours: updated.estimated_hours,
      actual_hours: updated.actual_hours,
      remarks: updated.remarks,
      action: "Project Details Updated",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

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

    res.json({ id: req.params.id, project_name: updated.project_name });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE
router.delete("/:id", async (req, res) => {
  try {
    const [updated_project] = await db.execute(
      "SELECT * FROM project_view WHERE project_id = ?",
      [req.params.id]
    );
    const updated = updated_project[0];

    // Prepare log data with complete updated project state
    const log_data = {
      log_id: await generateLogId(),
      user_id: getCurrentUserId(req),
      project_name: updated.project_name,
      type_name: updated.type_name,
      status_name: updated.status_name,
      invoice_status_name: updated.invoice_status_name,
      priority_level_name: updated.priority_level_name,
      deadline: updated.deadline
        ? updated.deadline.toISOString().slice(0, 19).replace("T", " ")
        : null,
      percent_completed: updated.percent_completed,
      estimated_hours: updated.estimated_hours,
      actual_hours: updated.actual_hours,
      remarks: updated.remarks,
      action: "Project Deleted",
      created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
    };

    const [result] = await db.execute(
      "DELETE FROM projects WHERE project_id = ?",
      [req.params.id]
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "Not found" });
    res.json({ message: "Deleted" });

    // Create project log entry
    try {
      await createProjectLog(log_data);
    } catch (log_error) {
      // Don't fail the main request if logging fails
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Debug endpoint to check project log status
router.get("/log-status", async (req, res) => {
  try {
    // Check if project_log table exists and has data
    const [tableCheck] = await db.execute("SHOW TABLES LIKE 'project_log'");
    if (tableCheck.length === 0) {
      return res.json({
        status: "error",
        message: "project_log table does not exist",
      });
    }

    // Get total count
    const [countResult] = await db.execute(
      "SELECT COUNT(*) as total FROM project_log"
    );
    const totalCount = countResult[0].total;

    // Get recent entries
    const [recentEntries] = await db.execute(
      "SELECT log_id, project_name, action, created_at FROM project_log ORDER BY created_at DESC LIMIT 5"
    );

    // Check for users
    const [usersResult] = await db.execute(
      "SELECT user_id, username FROM users LIMIT 5"
    );

    res.json({
      status: "success",
      total_logs: totalCount,
      recent_entries: recentEntries,
      available_users: usersResult,
      message: `Project log table has ${totalCount} entries`,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
