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

// Helper to format project dates like the database (YYYY-MM-DD)
function format_project_dates(project) {
  return {
    ...project,
    project_start_date: project.project_start_date
      ? typeof project.project_start_date === "string"
        ? project.project_start_date
        : project.project_start_date.toLocaleDateString("en-CA") // YYYY-MM-DD format
      : null,
    deadline: project.deadline
      ? typeof project.deadline === "string"
        ? project.deadline
        : project.deadline.toLocaleDateString("en-CA") // YYYY-MM-DD format
      : null,
  };
}

// GET /api/filterations/completed_tasks_count
router.get("/completed_tasks_count", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT COUNT(*) as completed_tasks 
       FROM projects p
       JOIN project_status ps ON p.status_id = ps.status_id
       WHERE LOWER(ps.status_name) = 'completed' 
       AND p.percent_completed = 100`
    );
    res.json({
      completed_tasks: rows[0].completed_tasks,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/active_projects_count
router.get("/active_projects_count", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT COUNT(*) as active_projects 
       FROM projects p
       JOIN project_status ps ON p.status_id = ps.status_id
       WHERE (p.percent_completed != 100 OR LOWER(ps.status_name) != 'completed')`
    );
    res.json({
      active_projects: rows[0].active_projects,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_type_count
router.get("/project_type_count", async (req, res) => {
  try {
    const { time_period, year } = req.query;

    let dateFilter = "";
    let params = [];

    if (time_period || year) {
      const currentDate = new Date();
      let filterDate = currentDate;

      // If year is specified, use that year instead of current year
      if (year) {
        filterDate = new Date(parseInt(year), 0, 1);
      }

      switch (time_period) {
        case "last_month":
          const lastMonth = new Date(
            filterDate.getFullYear(),
            filterDate.getMonth() - 1,
            1
          );
          const lastMonthEnd = new Date(
            filterDate.getFullYear(),
            filterDate.getMonth(),
            0
          );
          dateFilter =
            "WHERE p.project_start_date >= ? AND p.project_start_date <= ?";
          params.push(lastMonth.toISOString().split("T")[0]);
          params.push(lastMonthEnd.toISOString().split("T")[0]);
          break;

        case "current_year":
          const currentYearStart = new Date(filterDate.getFullYear(), 0, 1);
          dateFilter = "WHERE p.project_start_date >= ?";
          params.push(currentYearStart.toISOString().split("T")[0]);
          break;

        case "current_quarter":
          const currentQuarter = Math.floor(filterDate.getMonth() / 3);
          const quarterStartMonth = currentQuarter * 3;
          const quarterStart = new Date(
            filterDate.getFullYear(),
            quarterStartMonth,
            1
          );
          // For 4 months, we'll extend to the next quarter
          const quarterEndMonth = Math.min(quarterStartMonth + 4, 11);
          const quarterEnd = new Date(
            filterDate.getFullYear(),
            quarterEndMonth + 1,
            0
          );
          dateFilter =
            "WHERE p.project_start_date >= ? AND p.project_start_date <= ?";
          params.push(quarterStart.toISOString().split("T")[0]);
          params.push(quarterEnd.toISOString().split("T")[0]);
          break;

        case "last_3_years":
          const last3Years = new Date(filterDate.getFullYear() - 3, 0, 1);
          dateFilter = "WHERE p.project_start_date >= ?";
          params.push(last3Years.toISOString().split("T")[0]);
          break;

        case "last_5_years":
          const last5Years = new Date(filterDate.getFullYear() - 5, 0, 1);
          dateFilter = "WHERE p.project_start_date >= ?";
          params.push(last5Years.toISOString().split("T")[0]);
          break;

        default:
          // If only year is specified without time_period, filter by that year
          if (year) {
            const yearStart = new Date(parseInt(year), 0, 1);
            const yearEnd = new Date(parseInt(year), 11, 31);
            dateFilter =
              "WHERE p.project_start_date >= ? AND p.project_start_date <= ?";
            params.push(yearStart.toISOString().split("T")[0]);
            params.push(yearEnd.toISOString().split("T")[0]);
          }
          break;
      }
    }

    const query = `
      SELECT pt.type_name, COUNT(p.project_id) AS project_count
      FROM project_type pt
      LEFT JOIN projects p ON pt.type_id = p.type_id
      ${dateFilter}
      GROUP BY pt.type_id, pt.type_name
      ORDER BY project_count DESC
    `;

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

// GET /api/filterations/project_status_count
router.get("/project_status_count", async (req, res) => {
  try {
    const { time_period, year } = req.query;

    let dateFilter = "";
    let params = [];

    if (time_period || year) {
      const currentDate = new Date();
      let filterDate = currentDate;

      // If year is specified, use that year instead of current year
      if (year) {
        filterDate = new Date(parseInt(year), 0, 1);
      }

      switch (time_period) {
        case "last_month":
          const lastMonth = new Date(
            filterDate.getFullYear(),
            filterDate.getMonth() - 1,
            1
          );
          const lastMonthEnd = new Date(
            filterDate.getFullYear(),
            filterDate.getMonth(),
            0
          );
          dateFilter =
            "WHERE p.project_start_date >= ? AND p.project_start_date <= ?";
          params.push(lastMonth.toISOString().split("T")[0]);
          params.push(lastMonthEnd.toISOString().split("T")[0]);
          break;

        case "current_year":
          const currentYearStart = new Date(filterDate.getFullYear(), 0, 1);
          dateFilter = "WHERE p.project_start_date >= ?";
          params.push(currentYearStart.toISOString().split("T")[0]);
          break;

        case "current_quarter":
          const currentQuarter = Math.floor(filterDate.getMonth() / 3);
          const quarterStartMonth = currentQuarter * 3;
          const quarterStart = new Date(
            filterDate.getFullYear(),
            quarterStartMonth,
            1
          );
          // For 4 months, we'll extend to the next quarter
          const quarterEndMonth = Math.min(quarterStartMonth + 4, 11);
          const quarterEnd = new Date(
            filterDate.getFullYear(),
            quarterEndMonth + 1,
            0
          );
          dateFilter =
            "WHERE p.project_start_date >= ? AND p.project_start_date <= ?";
          params.push(quarterStart.toISOString().split("T")[0]);
          params.push(quarterEnd.toISOString().split("T")[0]);
          break;

        case "last_3_years":
          const last3Years = new Date(filterDate.getFullYear() - 3, 0, 1);
          dateFilter = "WHERE p.project_start_date >= ?";
          params.push(last3Years.toISOString().split("T")[0]);
          break;

        case "last_5_years":
          const last5Years = new Date(filterDate.getFullYear() - 5, 0, 1);
          dateFilter = "WHERE p.project_start_date >= ?";
          params.push(last5Years.toISOString().split("T")[0]);
          break;

        default:
          // If only year is specified without time_period, filter by that year
          if (year) {
            const yearStart = new Date(parseInt(year), 0, 1);
            const yearEnd = new Date(parseInt(year), 11, 31);
            dateFilter =
              "WHERE p.project_start_date >= ? AND p.project_start_date <= ?";
            params.push(yearStart.toISOString().split("T")[0]);
            params.push(yearEnd.toISOString().split("T")[0]);
          }
          break;
      }
    }

    const query = `
      SELECT ps.status_name, COUNT(p.project_id) AS project_count
      FROM project_status ps
      LEFT JOIN projects p ON ps.status_id = p.status_id
      ${dateFilter}
      GROUP BY ps.status_id, ps.status_name
    `;

    const [rows] = await db.execute(query, params);

    // Get the status order from the database
    const [statusOrderRows] = await db.execute(
      `SELECT status_name FROM project_status ORDER BY status_id`
    );

    const desiredOrder = statusOrderRows.map((row) => row.status_name);

    // Sort the results according to the desired order
    const sortedRows = desiredOrder.map((statusName) => {
      const found = rows.find((row) => row.status_name === statusName);
      return found || { status_name: statusName, project_count: 0 };
    });

    res.json(sortedRows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/division_employee_count
router.get("/division_employee_count", async (req, res) => {
  try {
    const { time_period, year } = req.query;

    let dateFilter = "";
    let params = [];

    if (time_period || year) {
      const currentDate = new Date();
      let filterDate = currentDate;

      // If year is specified, use that year instead of current year
      if (year) {
        filterDate = new Date(parseInt(year), 0, 1);
      }

      switch (time_period) {
        case "last_month":
          const lastMonth = new Date(
            filterDate.getFullYear(),
            filterDate.getMonth() - 1,
            1
          );
          const lastMonthEnd = new Date(
            filterDate.getFullYear(),
            filterDate.getMonth(),
            0
          );
          dateFilter =
            "WHERE epv.project_start_date >= ? AND epv.project_start_date <= ?";
          params.push(lastMonth.toISOString().split("T")[0]);
          params.push(lastMonthEnd.toISOString().split("T")[0]);
          break;

        case "current_year":
          const currentYearStart = new Date(filterDate.getFullYear(), 0, 1);
          dateFilter = "WHERE epv.project_start_date >= ?";
          params.push(currentYearStart.toISOString().split("T")[0]);
          break;

        case "current_quarter":
          const currentQuarter = Math.floor(filterDate.getMonth() / 3);
          const quarterStartMonth = currentQuarter * 3;
          const quarterStart = new Date(
            filterDate.getFullYear(),
            quarterStartMonth,
            1
          );
          // For 4 months, we'll extend to the next quarter
          const quarterEndMonth = Math.min(quarterStartMonth + 4, 11);
          const quarterEnd = new Date(
            filterDate.getFullYear(),
            quarterEndMonth + 1,
            0
          );
          dateFilter =
            "WHERE epv.project_start_date >= ? AND epv.project_start_date <= ?";
          params.push(quarterStart.toISOString().split("T")[0]);
          params.push(quarterEnd.toISOString().split("T")[0]);
          break;

        case "last_3_years":
          const last3Years = new Date(filterDate.getFullYear() - 3, 0, 1);
          dateFilter = "WHERE epv.project_start_date >= ?";
          params.push(last3Years.toISOString().split("T")[0]);
          break;

        case "last_5_years":
          const last5Years = new Date(filterDate.getFullYear() - 5, 0, 1);
          dateFilter = "WHERE epv.project_start_date >= ?";
          params.push(last5Years.toISOString().split("T")[0]);
          break;

        default:
          // If only year is specified without time_period, filter by that year
          if (year) {
            const yearStart = new Date(parseInt(year), 0, 1);
            const yearEnd = new Date(parseInt(year), 11, 31);
            dateFilter =
              "WHERE epv.project_start_date >= ? AND epv.project_start_date <= ?";
            params.push(yearStart.toISOString().split("T")[0]);
            params.push(yearEnd.toISOString().split("T")[0]);
          }
          break;
      }
    }

    const query = `
      SELECT division_name, COUNT(DISTINCT employee_id) AS employee_count
      FROM employee_project_view epv
      ${dateFilter}
      GROUP BY division_name
    `;

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

// GET /api/filterations/division_employee_count_by_name_accurate - New accurate API for departments page
router.get("/division_employee_count_by_name_accurate", async (req, res) => {
  try {
    // Get accurate counts by directly querying the base tables
    const [rows] = await db.execute(
      `SELECT 
         d.division_id,
         d.division_name,
         COUNT(DISTINCT e.employee_id) AS employee_count,
         COUNT(DISTINCT p.project_id) AS project_count
       FROM divisions d
       LEFT JOIN employees e ON d.division_id = e.division_id
       LEFT JOIN projects p ON d.division_id = p.division_id
       GROUP BY d.division_id, d.division_name
       ORDER BY d.division_name`
    );

    if (rows.length === 0) {
      return res.status(404).json({ error: "No divisions found" });
    }

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

// GET /api/filterations/employees_by_division_accurate - New accurate API for showing employees in a division
router.get("/employees_by_division_accurate", async (req, res) => {
  const { division_id } = req.query;

  if (!division_id) {
    return res.status(400).json({
      error: "Division ID parameter is required",
    });
  }

  try {
    // Get all employees in the specific division by division_id, regardless of project assignments
    const [rows] = await db.execute(
      `SELECT 
         e.employee_id,
         e.employee_name,
         d.division_name,
         r.role_name,
         e.remarks
       FROM employees e
       JOIN divisions d ON e.division_id = d.division_id
       LEFT JOIN roles r ON e.role_id = r.role_id
       WHERE e.division_id = ?
       ORDER BY e.employee_name`,
      [division_id]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/projects_by_division_accurate - New accurate API for showing projects in a division
router.get("/projects_by_division_accurate", async (req, res) => {
  const { division_id } = req.query;

  if (!division_id) {
    return res.status(400).json({
      error: "Division ID parameter is required",
    });
  }

  try {
    // Get all projects in the specific division by division_id, regardless of employee assignments
    const [rows] = await db.execute(
      `SELECT DISTINCT
         p.project_id,
         p.project_name,
         p.project_lead_emp_id,
         pl.employee_name AS project_lead_name,
         pt.type_name,
         ps.status_name,
         ins.invoice_status_name,
         ppl.priority_level_name,
         p.project_start_date,
         p.deadline,
         p.percent_completed,
         p.estimated_hours,
         p.actual_hours,
         p.remarks,
         d.division_name
       FROM projects p
       JOIN divisions d ON p.division_id = d.division_id
       LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
       LEFT JOIN project_type pt ON p.type_id = pt.type_id
       LEFT JOIN project_status ps ON p.status_id = ps.status_id
       LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
       LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
       WHERE p.division_id = ?
       ORDER BY p.project_name`,
      [division_id]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/projects_with_lead_details
router.get("/projects_with_lead_details", async (req, res) => {
  try {
    const query = `
      SELECT 
        p.project_id,
        p.project_name,
        p.project_lead_emp_id,
        pl.employee_name AS project_lead_name,
        pt.type_name,
        ps.status_name,
        ins.invoice_status_name,
        ppl.priority_level_name,
        p.project_start_date,
        p.deadline,
        p.percent_completed,
        p.estimated_hours,
        p.actual_hours,
        p.remarks,
        p.division_id,
        d.division_name,
        pos.state AS po_status
      FROM 
        projects p
      LEFT JOIN 
        employees pl ON p.project_lead_emp_id = pl.employee_id
      LEFT JOIN 
        project_type pt ON p.type_id = pt.type_id
      LEFT JOIN 
        project_status ps ON p.status_id = ps.status_id
      LEFT JOIN 
        invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
      LEFT JOIN 
        project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
      LEFT JOIN 
        divisions d ON p.division_id = d.division_id
      LEFT JOIN 
        po_status pos ON p.po_status_id = pos.id
    `;

    const [rows] = await db.execute(query);
    const projects_formatted = rows.map(format_project_dates);
    res.json(projects_formatted);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/projects_with_ids - Returns projects with actual IDs for editing
router.get("/projects_with_ids", async (req, res) => {
  try {
    // Query to get projects with both IDs and names for editing
    const [rows] = await db.execute(`
      SELECT 
        p.project_id,
        p.project_name,
        p.project_lead_emp_id,
        pl.employee_name AS project_lead_name,
        p.type_id,
        pt.type_name,
        p.status_id,
        ps.status_name,
        p.invoice_status_id,
        ins.invoice_status_name,
        p.priority_level_id,
        ppl.priority_level_name,
        p.project_start_date,
        p.deadline,
        p.percent_completed,
        p.estimated_hours,
        p.actual_hours,
        p.remarks,
        p.division_id,
        d.division_name
      FROM projects p
      LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
      LEFT JOIN project_type pt ON p.type_id = pt.type_id
      LEFT JOIN project_status ps ON p.status_id = ps.status_id
      LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
      LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
      LEFT JOIN divisions d ON p.division_id = d.division_id
      ORDER BY p.project_id DESC
    `);
    const projects_formatted = rows.map(format_project_dates);
    res.json(projects_formatted);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/projects_from_view - Returns projects using project_view with po_status
router.get("/projects_from_view", async (req, res) => {
  try {
    // Query using the project_view which includes po_status
    const [rows] = await db.execute(`
      SELECT 
        project_id,
        project_name,
        project_lead_emp_id,
        project_lead_name,
        type_name,
        status_name,
        invoice_status_name,
        priority_level_name,
        project_start_date,
        deadline,
        percent_completed,
        estimated_hours,
        actual_hours,
        remarks,
        division_name,
        po_status
      FROM project_view
      ORDER BY project_id DESC
    `);
    const projects_formatted = rows.map(format_project_dates);
    res.json(projects_formatted);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_logs_by_date_range?start_date=YYYY-MM-DD&end_date=YYYY-MM-DD
router.get("/project_logs_by_date_range", async (req, res) => {
  const { start_date, end_date } = req.query;

  if (!start_date || !end_date) {
    return res.status(400).json({
      error: "Both start_date and end_date are required in YYYY-MM-DD format",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT 
        pl.*,
        p.po_status_id,
        ps.state as po_status_name
       FROM project_log pl
       LEFT JOIN projects p ON pl.project_name = p.project_name
       LEFT JOIN po_status ps ON p.po_status_id = ps.id
       WHERE DATE(pl.created_at) BETWEEN ? AND ?
       ORDER BY pl.created_at DESC`,
      [start_date, end_date]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/employee_logs_by_date_range?start_date=YYYY-MM-DD&end_date=YYYY-MM-DD
router.get("/employee_logs_by_date_range", async (req, res) => {
  const { start_date, end_date } = req.query;

  if (!start_date || !end_date) {
    return res.status(400).json({
      error: "Both start_date and end_date are required in YYYY-MM-DD format",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT * FROM employee_log 
       WHERE DATE(created_at) BETWEEN ? AND ?
       ORDER BY created_at DESC`,
      [start_date, end_date]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/all_employee_logs - Get all employee logs without date restrictions
router.get("/all_employee_logs", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT * FROM employee_log 
       ORDER BY created_at DESC
       LIMIT 1000`
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/all_project_logs - Get all project logs without date restrictions
router.get("/all_project_logs", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT 
        pl.*,
        p.po_status_id,
        ps.state as po_status_name
       FROM project_log pl
       LEFT JOIN projects p ON pl.project_name = p.project_name
       LEFT JOIN po_status ps ON p.po_status_id = ps.id
       ORDER BY pl.created_at DESC
       LIMIT 1000`
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/employees_by_division?division=DIVISION_NAME
router.get("/employees_by_division", async (req, res) => {
  const { division } = req.query;

  if (!division) {
    return res.status(400).json({
      error: "Division parameter is required",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT 
        employee_id,
        employee_name,
        division_name,
        role_name,
        project_names,
        remarks
       FROM employee_project_view 
       WHERE division_name = ?
       ORDER BY employee_name`,
      [division]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/projects_by_division?division=DIVISION_NAME
router.get("/projects_by_division", async (req, res) => {
  const { division } = req.query;

  if (!division) {
    return res.status(400).json({
      error: "Division parameter is required",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT DISTINCT
        p.project_id,
        p.project_name,
        p.project_lead_emp_id,
        pl.employee_name AS project_lead_name,
        pt.type_name,
        ps.status_name,
        ins.invoice_status_name,
        ppl.priority_level_name,
        p.project_start_date,
        p.deadline,
        p.percent_completed,
        p.estimated_hours,
        p.actual_hours,
        p.remarks
       FROM projects p
       JOIN employees_projects ep ON p.project_id = ep.project_id
       JOIN employees e ON ep.employee_id = e.employee_id
       JOIN divisions d ON e.division_id = d.division_id
       LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
       LEFT JOIN project_type pt ON p.type_id = pt.type_id
       LEFT JOIN project_status ps ON p.status_id = ps.status_id
       LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
       LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
       WHERE d.division_name = ?
       ORDER BY p.project_name`,
      [division]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_names_by_division?division=DIVISION_NAME
router.get("/project_names_by_division", async (req, res) => {
  const { division } = req.query;

  if (!division) {
    return res.status(400).json({
      error: "Division parameter is required",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT DISTINCT p.project_name
       FROM projects p
       JOIN employees_projects ep ON p.project_id = ep.project_id
       JOIN employees e ON ep.employee_id = e.employee_id
       JOIN divisions d ON e.division_id = d.division_id
       WHERE d.division_name = ?
       ORDER BY p.project_name`,
      [division]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/projects_by_lead_name?lead_name=LEAD_NAME
router.get("/projects_by_lead_name", async (req, res) => {
  const { lead_name } = req.query;

  if (!lead_name) {
    return res.status(400).json({
      error: "Lead name parameter is required",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT 
        p.project_id,
        p.project_name,
        p.project_lead_emp_id,
        pl.employee_name AS project_lead_name,
        pt.type_name,
        ps.status_name,
        ins.invoice_status_name,
        ppl.priority_level_name,
        p.project_start_date,
        p.deadline,
        p.percent_completed,
        p.estimated_hours,
        p.actual_hours,
        p.remarks
       FROM projects p
       LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
       LEFT JOIN project_type pt ON p.type_id = pt.type_id
       LEFT JOIN project_status ps ON p.status_id = ps.status_id
       LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
       LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
       WHERE p.project_name = ?
       ORDER BY p.project_name`,
      [lead_name]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_trends_yearly - Get project trends for current year and 7 previous years
// Parameters: division_id (optional) - Filter by specific division
// Returns: { labels: ["2018", "2019", ...], data: [10, 15, ...] }
// Used by: Dashboard ProjectTrendsChart component
router.get("/project_trends_yearly", async (req, res) => {
  try {
    const { division_id } = req.query;
    const currentYear = new Date().getFullYear();
    const startYear = currentYear - 7;

    let query = `SELECT 
      YEAR(p.project_start_date) as year,
      COUNT(*) as project_count
     FROM projects p`;

    let whereClause = ` WHERE p.project_start_date IS NOT NULL 
       AND YEAR(p.project_start_date) >= ?`;

    let queryParams = [startYear];

    // Add division filter if provided
    if (division_id && division_id !== "all") {
      query += ` JOIN divisions d ON p.division_id = d.division_id`;
      whereClause += ` AND d.division_id = ?`;
      queryParams.push(division_id);
    }

    query += whereClause + ` GROUP BY YEAR(p.project_start_date) ORDER BY year`;

    const [rows] = await db.execute(query, queryParams);

    // Create labels and data arrays for current year and 7 previous years
    const labels = [];
    const data = [];

    for (let year = startYear; year <= currentYear; year++) {
      labels.push(year.toString());
      const yearData = rows.find((row) => row.year === year);
      data.push(yearData ? yearData.project_count : 0);
    }

    res.json({
      labels: labels,
      data: data,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_trends_monthly?year=YYYY - Get monthly project trends for a specific year
// Parameters: year (required) - Year in YYYY format, division_id (optional) - Filter by specific division
// Returns: { labels: ["Jan", "Feb", ...], data: [5, 8, ...] }
// Used by: Dashboard ProjectTrendsChart component
router.get("/project_trends_monthly", async (req, res) => {
  const { year, division_id } = req.query;

  if (!year) {
    return res.status(400).json({
      error: "Year parameter is required in YYYY format",
    });
  }

  try {
    let query = `SELECT 
      MONTH(p.project_start_date) as month,
      COUNT(*) as project_count
     FROM projects p`;

    let whereClause = ` WHERE p.project_start_date IS NOT NULL 
       AND YEAR(p.project_start_date) = ?`;

    let queryParams = [year];

    // Add division filter if provided
    if (division_id && division_id !== "all") {
      query += ` JOIN divisions d ON p.division_id = d.division_id`;
      whereClause += ` AND d.division_id = ?`;
      queryParams.push(division_id);
    }

    query +=
      whereClause + ` GROUP BY MONTH(p.project_start_date) ORDER BY month`;

    const [rows] = await db.execute(query, queryParams);

    // Create labels and data arrays for all 12 months
    const monthNames = [
      "Jan",
      "Feb",
      "Mar",
      "Apr",
      "May",
      "Jun",
      "Jul",
      "Aug",
      "Sep",
      "Oct",
      "Nov",
      "Dec",
    ];

    const labels = monthNames;
    const data = [];

    for (let month = 1; month <= 12; month++) {
      const monthData = rows.find((row) => row.month === month);
      data.push(monthData ? monthData.project_count : 0);
    }

    res.json({
      labels: labels,
      data: data,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_trends_quarterly?year=YYYY - Get quarterly project trends for a specific year
// Parameters: year (required) - Year in YYYY format, division_id (optional) - Filter by specific division
// Returns: { labels: ["Jan-Mar", "Apr-Jun", "Jul-Sep", "Oct-Dec"], data: [10, 15, 8, 12] }
// Used by: Dashboard ProjectTrendsChart component
router.get("/project_trends_quarterly", async (req, res) => {
  const { year, division_id } = req.query;

  if (!year) {
    return res.status(400).json({
      error: "Year parameter is required in YYYY format",
    });
  }

  try {
    let query = `SELECT 
      QUARTER(p.project_start_date) as quarter,
      COUNT(*) as project_count
     FROM projects p`;

    let whereClause = ` WHERE p.project_start_date IS NOT NULL 
       AND YEAR(p.project_start_date) = ?`;

    let queryParams = [year];

    // Add division filter if provided
    if (division_id && division_id !== "all") {
      query += ` JOIN divisions d ON p.division_id = d.division_id`;
      whereClause += ` AND d.division_id = ?`;
      queryParams.push(division_id);
    }

    query +=
      whereClause + ` GROUP BY QUARTER(p.project_start_date) ORDER BY quarter`;

    const [rows] = await db.execute(query, queryParams);

    // Create labels and data arrays for all 4 quarters
    const labels = ["Jan-Mar", "Apr-Jun", "Jul-Sep", "Oct-Dec"];
    const data = [];

    for (let quarter = 1; quarter <= 4; quarter++) {
      const quarterData = rows.find((row) => row.quarter === quarter);
      data.push(quarterData ? quarterData.project_count : 0);
    }

    res.json({
      labels: labels,
      data: data,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/project_trends_summary - Get summary statistics for project trends
// Returns: { current_year_projects, previous_year_projects, total_projects, average_projects_per_year, growth_percentage, current_year }
// Used by: Dashboard components for summary statistics
router.get("/project_trends_summary", async (req, res) => {
  try {
    const currentYear = new Date().getFullYear();

    // Get current year projects
    const [currentYearProjects] = await db.execute(
      `SELECT COUNT(*) as count FROM projects WHERE YEAR(project_start_date) = ?`,
      [currentYear]
    );

    // Get previous year projects
    const [previousYearProjects] = await db.execute(
      `SELECT COUNT(*) as count FROM projects WHERE YEAR(project_start_date) = ?`,
      [currentYear - 1]
    );

    // Get total projects
    const [totalProjects] = await db.execute(
      `SELECT COUNT(*) as count FROM projects WHERE project_start_date IS NOT NULL`
    );

    // Get average projects per year
    const [avgProjectsPerYear] = await db.execute(
      `SELECT AVG(project_count) as avg_count FROM (
        SELECT YEAR(project_start_date) as year, COUNT(*) as project_count
        FROM projects 
        WHERE project_start_date IS NOT NULL
        GROUP BY YEAR(project_start_date)
      ) as yearly_counts`
    );

    // Calculate growth percentage
    const currentCount = currentYearProjects[0].count;
    const previousCount = previousYearProjects[0].count;
    const growthPercentage =
      previousCount > 0
        ? (((currentCount - previousCount) / previousCount) * 100).toFixed(1)
        : 0;

    res.json({
      current_year_projects: currentCount,
      previous_year_projects: previousCount,
      total_projects: totalProjects[0].count,
      average_projects_per_year: Math.round(
        avgProjectsPerYear[0].avg_count || 0
      ),
      growth_percentage: parseFloat(growthPercentage),
      current_year: currentYear,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});
router.get("/pie-chart-project-type-percentages", async (req, res) => {
  const query = `
    SELECT 
        pt.type_name,
        COUNT(p.project_id) AS project_count,
        ROUND((COUNT(p.project_id) / total.total_projects) * 100, 2) AS percentage
    FROM 
        projects p
    JOIN 
        project_type pt ON p.type_id = pt.type_id
    JOIN (
        SELECT COUNT(*) AS total_projects
        FROM projects
    ) AS total ON 1=1
    GROUP BY 
        pt.type_name, total.total_projects;
  `;

  try {
    const [results] = await db.execute(query);
    res.json({
      success: true,
      data: results,
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: "Server error",
      error: error.message,
    });
  }
});
//division wise employees and project count and their details(ananya)

// GET /api/filterations/projects_close_to_deadline - Get projects within 10 days of deadline
// Returns: Array of projects with project details and days left until deadline
// Used by: Dashboard DeadlineAlert component
router.get("/projects_close_to_deadline", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT 
        p.project_id,
        p.project_name,
        COALESCE(ppl.priority_level_name, 'Not Assigned') AS project_priority,
        COALESCE(pl.employee_name, 'Not Assigned') AS project_lead,
        p.deadline,
        DATEDIFF(p.deadline, CURDATE()) AS days_left
       FROM projects p
       LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
       LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
       WHERE p.deadline IS NOT NULL 
         AND p.deadline >= CURDATE()
         AND DATEDIFF(p.deadline, CURDATE()) <= 10
       ORDER BY days_left ASC, p.project_name ASC`
    );

    // Format the dates and add urgency level
    const formattedRows = rows.map((project) => ({
      ...project,
      deadline: project.deadline
        ? project.deadline.toLocaleDateString("en-CA")
        : null,
      urgency_level:
        project.days_left <= 3
          ? "critical"
          : project.days_left <= 7
          ? "warning"
          : "normal",
    }));

    res.json(formattedRows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/overdue_projects - Get projects that have passed their deadline
// Returns: Array of overdue projects with project details and days overdue
// Used by: Dashboard OverdueProjects component
router.get("/overdue_projects", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT 
        p.project_id,
        p.project_name,
        COALESCE(ppl.priority_level_name, 'Not Assigned') AS project_priority,
        COALESCE(pl.employee_name, 'Not Assigned') AS project_lead,
        p.deadline,
        ABS(DATEDIFF(p.deadline, CURDATE())) AS days_overdue
       FROM projects p
       LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
       LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
       LEFT JOIN project_status ps ON p.status_id = ps.status_id
       WHERE p.deadline IS NOT NULL 
         AND p.deadline < CURDATE()
         AND LOWER(ps.status_name) != 'completed'
       ORDER BY days_overdue DESC, p.project_name ASC`
    );

    // Format the dates and add severity level
    const formattedRows = rows.map((project) => ({
      ...project,
      deadline: project.deadline
        ? project.deadline.toLocaleDateString("en-CA")
        : null,
      severity_level:
        project.days_overdue >= 30
          ? "critical"
          : project.days_overdue >= 14
          ? "high"
          : project.days_overdue >= 7
          ? "medium"
          : "low",
    }));

    res.json(formattedRows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/employees_by_project?project_id=PROJECT_ID
// Returns: Array of employees assigned to a specific project
// Used by: Project management and team assignment features
router.get("/employees_by_project", async (req, res) => {
  const { project_id } = req.query;

  if (!project_id) {
    return res.status(400).json({
      error: "Project ID parameter is required",
    });
  }

  try {
    const [rows] = await db.execute(
      `SELECT 
        e.employee_id,
        e.employee_name,
        d.division_name,
        r.role_name,
        e.remarks
       FROM employees_projects ep
       JOIN employees e ON ep.employee_id = e.employee_id
       LEFT JOIN divisions d ON e.division_id = d.division_id
       LEFT JOIN roles r ON e.role_id = r.role_id
       WHERE ep.project_id = ?
       ORDER BY e.employee_name`,
      [project_id]
    );

    if (rows.length === 0) {
      return res.status(404).json({
        error: "No employees found for this project",
        project_id: project_id,
      });
    }

    res.json({
      project_id: project_id,
      employee_count: rows.length,
      employees: rows,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/filterations/dashboard_stats_percentage_changes - Get percentage changes for dashboard stats
// Returns: Object with percentage changes for employees, projects, departments, and tasks
// Used by: Dashboard StatsGrid component for showing real-time percentage changes
router.get("/dashboard_stats_percentage_changes", async (req, res) => {
  try {
    const currentDate = new Date();
    const currentMonth = currentDate.getMonth() + 1; // 1-12
    const currentYear = currentDate.getFullYear();

    // Calculate previous month and year
    let previousMonth = currentMonth - 1;
    let previousYear = currentYear;
    if (previousMonth === 0) {
      previousMonth = 12;
      previousYear = currentYear - 1;
    }

    // Get current month counts
    const [currentMonthEmployees] = await db.execute(
      `SELECT COUNT(*) as count FROM employees 
       WHERE MONTH(created_at) = ? AND YEAR(created_at) = ?`,
      [currentMonth, currentYear]
    );

    const [currentMonthProjects] = await db.execute(
      `SELECT COUNT(*) as count FROM projects 
       WHERE MONTH(project_start_date) = ? AND YEAR(project_start_date) = ?`,
      [currentMonth, currentYear]
    );

    const [currentMonthDepartments] = await db.execute(
      `SELECT COUNT(*) as count FROM divisions 
       WHERE MONTH(created_at) = ? AND YEAR(created_at) = ?`,
      [currentMonth, currentYear]
    );

    const [currentMonthTasks] = await db.execute(
      `SELECT COUNT(*) as count FROM project_log 
       WHERE MONTH(created_at) = ? AND YEAR(created_at) = ? AND action = 'Task Completed'`,
      [currentMonth, currentYear]
    );

    // Get previous month counts
    const [previousMonthEmployees] = await db.execute(
      `SELECT COUNT(*) as count FROM employees 
       WHERE MONTH(created_at) = ? AND YEAR(created_at) = ?`,
      [previousMonth, previousYear]
    );

    const [previousMonthProjects] = await db.execute(
      `SELECT COUNT(*) as count FROM projects 
       WHERE MONTH(project_start_date) = ? AND YEAR(project_start_date) = ?`,
      [previousMonth, previousYear]
    );

    const [previousMonthDepartments] = await db.execute(
      `SELECT COUNT(*) as count FROM divisions 
       WHERE MONTH(created_at) = ? AND YEAR(created_at) = ?`,
      [previousMonth, previousYear]
    );

    const [previousMonthTasks] = await db.execute(
      `SELECT COUNT(*) as count FROM project_log 
       WHERE MONTH(created_at) = ? AND YEAR(created_at) = ? AND action = 'Task Completed'`,
      [previousMonth, previousYear]
    );

    // Calculate percentage changes
    const calculatePercentageChange = (current, previous) => {
      if (previous === 0) {
        return current > 0 ? 100 : 0;
      }
      return (((current - previous) / previous) * 100).toFixed(1);
    };

    const employeeChange = calculatePercentageChange(
      currentMonthEmployees[0].count,
      previousMonthEmployees[0].count
    );

    const projectChange = calculatePercentageChange(
      currentMonthProjects[0].count,
      previousMonthProjects[0].count
    );

    const departmentChange = calculatePercentageChange(
      currentMonthDepartments[0].count,
      previousMonthDepartments[0].count
    );

    const taskChange = calculatePercentageChange(
      currentMonthTasks[0].count,
      previousMonthTasks[0].count
    );

    res.json({
      employees: {
        current: currentMonthEmployees[0].count,
        previous: previousMonthEmployees[0].count,
        change: parseFloat(employeeChange),
        trend:
          parseFloat(employeeChange) > 0
            ? "up"
            : parseFloat(employeeChange) < 0
            ? "down"
            : "stable",
      },
      projects: {
        current: currentMonthProjects[0].count,
        previous: previousMonthProjects[0].count,
        change: parseFloat(projectChange),
        trend:
          parseFloat(projectChange) > 0
            ? "up"
            : parseFloat(projectChange) < 0
            ? "down"
            : "stable",
      },
      departments: {
        current: currentMonthDepartments[0].count,
        previous: previousMonthDepartments[0].count,
        change: parseFloat(departmentChange),
        trend:
          parseFloat(departmentChange) > 0
            ? "up"
            : parseFloat(departmentChange) < 0
            ? "down"
            : "stable",
      },
      tasks: {
        current: currentMonthTasks[0].count,
        previous: previousMonthTasks[0].count,
        change: parseFloat(taskChange),
        trend:
          parseFloat(taskChange) > 0
            ? "up"
            : parseFloat(taskChange) < 0
            ? "down"
            : "stable",
      },
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// To Get the projects that have not submitted the form
router.get("/is_form_pending", (req, res) => {
  const query = `
        SELECT project_name
        FROM projects
        WHERE client_form IS NULL
    `;
  db.query(query, (err, results) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }

    const formatted = results.map((row) => ({
      project_name: row.project_name,
      form_pending: true,
    }));

    res.json(formatted);
  });
});

// GET /api/filterations/outstanding_projects - Get projects with outstanding payments
// Returns: Array of projects with invoice status "Part Payment Received" or "Submitted"
// Used by: Dashboard and reporting components for payment tracking and outstanding amount management
router.get("/outstanding_projects", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT 
        p.project_id,
        p.project_name,
        COALESCE(pl.employee_name, 'Not Assigned') AS project_lead,
        COALESCE(pt.type_name, 'Not Assigned') AS project_type,
        COALESCE(ps.status_name, 'Not Assigned') AS project_status,
        COALESCE(ins.invoice_status_name, 'Not Assigned') AS invoice_status,
        COALESCE(ppl.priority_level_name, 'Not Assigned') AS project_priority,
        p.project_start_date,
        p.deadline,
        p.percent_completed,
        p.estimated_hours,
        p.actual_hours,
        p.remarks,
        COALESCE(d.division_name, 'Not Assigned') AS division_name,
        COALESCE(pos.state, 'Not Set') AS po_status
       FROM projects p
       LEFT JOIN employees pl ON p.project_lead_emp_id = pl.employee_id
       LEFT JOIN project_type pt ON p.type_id = pt.type_id
       LEFT JOIN project_status ps ON p.status_id = ps.status_id
       LEFT JOIN invoice_status ins ON p.invoice_status_id = ins.invoice_status_id
       LEFT JOIN project_priority_level ppl ON p.priority_level_id = ppl.priority_level_id
       LEFT JOIN divisions d ON p.division_id = d.division_id
       LEFT JOIN po_status pos ON p.po_status_id = pos.id
       WHERE p.invoice_status_id IN (2, 6)  -- 2 = "Submitted", 6 = "Part Payment Received"
       ORDER BY p.project_name ASC`
    );

    // Format the dates using the existing helper function
    const projects_formatted = rows.map(format_project_dates);

    res.json({
      success: true,
      count: projects_formatted.length,
      projects: projects_formatted,
      invoice_statuses: {
        "2": "Submitted",
        "6": "Part Payment Received"
      }
    });
  } catch (err) {
    res.status(500).json({ 
      success: false,
      error: err.message 
    });
  }
});

module.exports = router;
