


const { pool } = require("../../config/db");
const puppeteer = require('puppeteer');

// Add this at the top of deductionController.js after the requires
const MONTH_NUM_TO_NAME = {
  1: "January", 2: "February", 3: "March", 4: "April",
  5: "May", 6: "June", 7: "July", 8: "August",
  9: "September", 10: "October", 11: "November", 12: "December"
};

/* ===========================
   HELPER FUNCTIONS
=========================== */

const calculatePerDaySalary = (monthlySalary, workingDays) => {
  return workingDays > 0 ? monthlySalary / workingDays : 0;
};

/* ===========================
   ✅ DEDUCTION RULES CRUD
=========================== */

/**
 * CREATE DEDUCTION RULE
 * POST /api/deductions/rules
 */
const createDeductionRule = async (req, res) => {
  const org_id = req.session.user.orgId;

  try {
    const {
      rule_name,
      rule_type,
      calculation_type,
      percentage,
      fixed_amount,
      threshold_minutes,
      max_per_day,
      is_active,
    } = req.body;

    if (!rule_name || !rule_type || !calculation_type) {
      return res.status(400).json({
        success: false,
        message: "Rule name, rule type, and calculation type are required",
      });
    }

    // Duplicate check
    const existingCheck = await pool.query(
      `SELECT id FROM deduction_rules
       WHERE rule_name = $1 AND (org_id = $2 OR org_id IS NULL) AND is_deleted = false`,
      [rule_name, org_id]
    );

    if (existingCheck.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "A deduction rule with this name already exists",
      });
    }

    const result = await pool.query(
      `INSERT INTO deduction_rules (
          org_id, rule_name, rule_type, calculation_type,
          percentage, fixed_amount, threshold_minutes, max_per_day, is_active
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        RETURNING *`,
      [
        org_id,
        rule_name,
        rule_type,
        calculation_type,
        parseFloat(percentage) || 0,
        parseFloat(fixed_amount) || 0,
        parseInt(threshold_minutes) || 0,
        parseFloat(max_per_day) || 1,
        is_active !== undefined ? is_active : true,
      ]
    );

    res.status(201).json({
      success: true,
      message: "Deduction rule created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("CREATE DEDUCTION RULE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GET ALL DEDUCTION RULES
 * GET /api/deductions/rules
 */
const getDeductionRules = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { rule_type, is_active } = req.query;

    let query = `
      SELECT * FROM deduction_rules
      WHERE is_deleted = false AND (org_id = $1 OR org_id IS NULL)
    `;
    const params = [org_id];
    let idx = 2;

    if (rule_type) {
      query += ` AND rule_type = $${idx++}`;
      params.push(rule_type);
    }
    if (is_active !== undefined) {
      query += ` AND is_active = $${idx++}`;
      params.push(is_active === "true");
    }

    query += ` ORDER BY rule_type, rule_name`;

    const result = await pool.query(query, params);
    res.json({ success: true, data: result.rows, count: result.rows.length });
  } catch (err) {
    console.error("GET DEDUCTION RULES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GET DEDUCTION RULE BY ID
 * GET /api/deductions/rules/:id
 */
const getDeductionRuleById = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    const result = await pool.query(
      `SELECT * FROM deduction_rules
       WHERE id = $1 AND is_deleted = false AND (org_id = $2 OR org_id IS NULL)`,
      [id, org_id]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Deduction rule not found" });
    }

    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET DEDUCTION RULE BY ID ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * UPDATE DEDUCTION RULE
 * PUT /api/deductions/rules/:id
 */
const updateDeductionRule = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    const {
      rule_name,
      rule_type,
      calculation_type,
      percentage,
      fixed_amount,
      threshold_minutes,
      max_per_day,
      is_active,
    } = req.body;

    // Check exists
    const existing = await pool.query(
      `SELECT id, rule_name FROM deduction_rules
       WHERE id = $1 AND (org_id = $2 OR org_id IS NULL) AND is_deleted = false`,
      [id, org_id]
    );

    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Deduction rule not found" });
    }

    // Duplicate name check (exclude self)
    if (rule_name && rule_name !== existing.rows[0].rule_name) {
      const dup = await pool.query(
        `SELECT id FROM deduction_rules
         WHERE rule_name = $1 AND (org_id = $2 OR org_id IS NULL)
         AND id != $3 AND is_deleted = false`,
        [rule_name, org_id, id]
      );
      if (dup.rows.length > 0) {
        return res.status(400).json({
          success: false,
          message: "A deduction rule with this name already exists",
        });
      }
    }

    const result = await pool.query(
      `UPDATE deduction_rules SET
          rule_name         = COALESCE($1, rule_name),
          rule_type         = COALESCE($2, rule_type),
          calculation_type  = COALESCE($3, calculation_type),
          percentage        = COALESCE($4, percentage),
          fixed_amount      = COALESCE($5, fixed_amount),
          threshold_minutes = COALESCE($6, threshold_minutes),
          max_per_day       = COALESCE($7, max_per_day),
          is_active         = COALESCE($8, is_active),
          updated_at        = NOW()
        WHERE id = $9 AND (org_id = $10 OR org_id IS NULL)
        RETURNING *`,
      [
        rule_name || null,
        rule_type || null,
        calculation_type || null,
        percentage !== undefined && percentage !== "" ? parseFloat(percentage) : null,
        fixed_amount !== undefined && fixed_amount !== "" ? parseFloat(fixed_amount) : null,
        threshold_minutes !== undefined && threshold_minutes !== "" ? parseInt(threshold_minutes) : null,
        max_per_day !== undefined && max_per_day !== "" ? parseFloat(max_per_day) : null,
        is_active !== undefined ? is_active : null,
        id,
        org_id,
      ]
    );

    res.json({
      success: true,
      message: "Deduction rule updated successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("UPDATE DEDUCTION RULE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * DELETE DEDUCTION RULE (soft delete)
 * DELETE /api/deductions/rules/:id
 */
const deleteDeductionRule = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    const existing = await pool.query(
      `SELECT id FROM deduction_rules
       WHERE id = $1 AND (org_id = $2 OR org_id IS NULL) AND is_deleted = false`,
      [id, org_id]
    );

    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Deduction rule not found" });
    }

    await pool.query(
      `UPDATE deduction_rules SET is_deleted = true, updated_at = NOW()
       WHERE id = $1 AND (org_id = $2 OR org_id IS NULL)`,
      [id, org_id]
    );

    res.json({ success: true, message: "Deduction rule deleted successfully" });
  } catch (err) {
    console.error("DELETE DEDUCTION RULE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ EMPLOYEE SALARY FUNCTIONS (WITH DEPARTMENT/DESIGNATION JOINS)
=========================== */

/**
 * GET EMPLOYEE SALARY
 * GET /api/deductions/salary/:employee_id
 */
const getEmployeeSalary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { employee_id } = req.params;

    // ✅ JOIN with departments and designations tables to get names
    const result = await pool.query(
      `SELECT e.id, e.employee_code, e.first_name, e.last_name,
              e.salary, 
              COALESCE(d.dept_name, 'Unknown') AS department,
              COALESCE(des.designation_name, 'Unknown') AS designation, 
              e.org_id
       FROM employees e
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       WHERE e.employee_code = $1 AND e.org_id = $2 AND e.status = 'Active' AND e.is_deleted = false`,
      [employee_id, org_id]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const emp = result.rows[0];
    const salary = parseFloat(emp.salary) || 0;

    res.json({
      success: true,
      data: {
        employee: {
          id: emp.id,
          code: emp.employee_code,
          name: `${emp.first_name} ${emp.last_name}`,
          department: emp.department,
          designation: emp.designation,
        },
        salary: {
          monthly_gross: salary,
          basic: parseFloat((salary * 0.4).toFixed(2)),
          hra: parseFloat((salary * 0.2).toFixed(2)),
          conveyance: parseFloat((salary * 0.05).toFixed(2)),
          special_allowance: parseFloat((salary * 0.35).toFixed(2)),
        },
      },
    });
  } catch (err) {
    console.error("GET EMPLOYEE SALARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * UPDATE EMPLOYEE SALARY
 * PUT /api/deductions/salary/:employee_id
 */
const updateEmployeeSalary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;
    const { employee_id } = req.params;
    const { salary } = req.body;

    if (!salary || salary <= 0) {
      return res.status(400).json({ success: false, message: "Valid salary amount is required" });
    }

    const empCheck = await pool.query(
      `SELECT id FROM employees
       WHERE employee_code = $1 AND status = 'Active' AND org_id = $2`,
      [employee_id, org_id]
    );

    if (empCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const result = await pool.query(
      `UPDATE employees
       SET salary = $1, updated_by = $2, updated_at = NOW()
       WHERE employee_code = $3 AND status = 'Active' AND org_id = $4
       RETURNING id, employee_code, first_name, last_name, salary`,
      [salary, userId, employee_id, org_id]
    );

    const emp = result.rows[0];
    res.json({
      success: true,
      message: "Employee salary updated successfully",
      data: {
        employee: { code: emp.employee_code, name: `${emp.first_name} ${emp.last_name}` },
        salary: { monthly_gross: parseFloat(emp.salary) },
      },
    });
  } catch (err) {
    console.error("UPDATE EMPLOYEE SALARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * CREATE EMPLOYEE SALARY RECORD
 * POST /api/deductions/salary
 */
const createEmployeeSalary = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;

  try {
    const {
      employee_id, basic, hra, conveyance, special_allowance,
      shift_allowance, holiday_wages, advance_bonus, monthly_gross, effective_from,
    } = req.body;

    if (!employee_id) {
      return res.status(400).json({ success: false, message: "Employee ID is required" });
    }

    const empResult = await pool.query(
      `SELECT id FROM employees WHERE employee_code = $1 AND status = 'Active' AND is_deleted = false`,
      [employee_id]
    );

    if (empResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const empNumericId = empResult.rows[0].id;
    const calculatedGross = monthly_gross ||
      ((basic || 0) + (hra || 0) + (conveyance || 0) +
        (special_allowance || 0) + (shift_allowance || 0) +
        (holiday_wages || 0) + (advance_bonus || 0));

    const result = await pool.query(
      `INSERT INTO employee_salary (
          employee_id, basic, hra, conveyance, special_allowance,
          shift_allowance, holiday_wages, advance_bonus, monthly_gross,
          effective_from, org_id, created_by, created_at
        ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,NOW())
        RETURNING *`,
      [
        empNumericId, basic || 0, hra || 0, conveyance || 0,
        special_allowance || 0, shift_allowance || 0, holiday_wages || 0,
        advance_bonus || 0, calculatedGross,
        effective_from || new Date().toISOString().split("T")[0],
        org_id, userId,
      ]
    );

    res.status(201).json({ success: true, message: "Employee salary created successfully", data: result.rows[0] });
  } catch (err) {
    console.error("CREATE EMPLOYEE SALARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * DELETE EMPLOYEE SALARY (soft)
 * DELETE /api/deductions/salary/:employee_id
 */
const deleteEmployeeSalary = async (req, res) => {
  try {
    const { employee_id } = req.params;
    const org_id = req.session.user.orgId;

    const empResult = await pool.query(
      `SELECT id FROM employees WHERE employee_code = $1 AND status = 'Active' AND is_deleted = false`,
      [employee_id]
    );

    if (empResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const result = await pool.query(
      `UPDATE employee_salary SET is_deleted = true, deleted_at = NOW()
       WHERE employee_id = $1 AND org_id = $2 RETURNING id`,
      [empResult.rows[0].id, org_id]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Employee salary record not found" });
    }

    res.json({ success: true, message: "Employee salary deleted successfully" });
  } catch (err) {
    console.error("DELETE EMPLOYEE SALARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GET ALL EMPLOYEES WITH SALARY (WITH DEPARTMENT/DESIGNATION NAMES)
 * GET /api/deductions/employees-salary
 */
const getAllEmployeesSalary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { page = 1, limit = 10, search } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    // ✅ JOIN with departments and designations tables
    let query = `
      SELECT 
        e.id, 
        e.employee_code, 
        e.first_name, 
        e.last_name,
        e.salary, 
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation, 
        e.email, 
        e.phone
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE e.org_id = $1 AND e.status = 'Active' AND e.is_deleted = false
    `;
    const params = [org_id];

    if (search) {
      query += ` AND (e.employee_code ILIKE $2 OR e.first_name ILIKE $2 OR e.last_name ILIKE $2)`;
      params.push(`%${search}%`);
    }

    const countResult = await pool.query(
      `SELECT COUNT(DISTINCT e.id) as total FROM employees e WHERE e.org_id = $1 AND e.status = 'Active' AND e.is_deleted = false`,
      [org_id]
    );
    const total = parseInt(countResult.rows[0].total);

    query += ` ORDER BY e.created_at DESC LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
    params.push(parseInt(limit), offset);

    const result = await pool.query(query, params);

    const employees = result.rows.map((emp) => ({
      id: emp.id,
      employee_code: emp.employee_code,
      employee: `${emp.first_name} ${emp.last_name}`,
      department: emp.department,
      designation: emp.designation,
      salary: emp.salary,
      email: emp.email,
      phone: emp.phone,
      salary_components: {
        monthly_gross: parseFloat(emp.salary) || 0,
        basic: parseFloat(emp.salary) * 0.4 || 0,
        hra: parseFloat(emp.salary) * 0.2 || 0,
        conveyance: parseFloat(emp.salary) * 0.05 || 0,
        special_allowance: parseFloat(emp.salary) * 0.35 || 0,
      },
    }));

    res.json({
      success: true,
      data: employees,
      pagination: { 
        page: parseInt(page), 
        limit: parseInt(limit), 
        total, 
        totalPages: Math.ceil(total / parseInt(limit)) 
      },
    });
  } catch (err) {
    console.error("GET ALL EMPLOYEES SALARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ DEDUCTION SUMMARY (WITH DEPARTMENT/DESIGNATION NAMES)
=========================== */

/**
 * GET DEDUCTION SUMMARY
 * GET /api/deductions/summary?employee_id=&month=&year=
 */
const getDeductionSummary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { employee_id, month, year } = req.query;

    if (!employee_id || !month || !year) {
      return res.status(400).json({
        success: false,
        message: "employee_id, month, and year are required",
      });
    }

    // ✅ JOIN with departments and designations tables
    const empResult = await pool.query(
      `SELECT e.id, e.employee_code, e.first_name, e.last_name,
              COALESCE(d.dept_name, 'Unknown') AS department,
              COALESCE(des.designation_name, 'Unknown') AS designation,
              e.salary
       FROM employees e
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       WHERE e.employee_code = $1 AND e.org_id = $2 AND e.status = 'Active' AND e.is_deleted = false`,
      [employee_id, org_id]
    );

    if (empResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const employee = empResult.rows[0];
    const employeeIdNum = employee.id;
    const monthlySalary = parseFloat(employee.salary) || 0;

    // Period info
    const monthNum = parseInt(month);
    const yearNum = parseInt(year);
    const totalDays = new Date(yearNum, monthNum, 0).getDate();

    // Get attendance
    const attResult = await pool.query(
      `SELECT
          COUNT(CASE WHEN attendance_status = 'Present' THEN 1 END) AS present_days,
          COUNT(CASE WHEN attendance_status = 'Absent' THEN 1 END) AS absent_days,
          COUNT(CASE WHEN attendance_status = 'Late' THEN 1 END) AS late_days,
          COUNT(CASE WHEN attendance_status = 'Half Day' THEN 1 END) AS half_days,
          COUNT(CASE WHEN attendance_status = 'Holiday' THEN 1 END) AS holiday_days,
          COUNT(CASE WHEN attendance_status = 'Leave' THEN 1 END) AS leave_days,
          SUM(COALESCE(late_minutes, 0)) AS total_late_minutes,
          SUM(COALESCE(work_hours, 0)) AS total_work_hours
       FROM attendance
       WHERE employee_id = $1
         AND EXTRACT(YEAR FROM attendance_date) = $2
         AND EXTRACT(MONTH FROM attendance_date) = $3
         AND is_deleted = false`,
      [employeeIdNum, yearNum, monthNum]
    );

    const att = attResult.rows[0] || {};
    const workingDays = totalDays - (parseInt(att.holiday_days) || 0);
    const paidDays = (parseInt(att.present_days) || 0) + (parseInt(att.half_days) || 0) * 0.5 + (parseInt(att.leave_days) || 0);
    const lossOfPayDays = Math.max(0, workingDays - paidDays);

    const perDaySalary = workingDays > 0 ? monthlySalary / workingDays : 0;
    const perHourSalary = perDaySalary / 8;
    const perMinuteSalary = perHourSalary / 60;

    // Get deduction rules
    const rulesResult = await pool.query(
      `SELECT * FROM deduction_rules
       WHERE (org_id = $1 OR org_id IS NULL) AND is_active = true AND is_deleted = false`,
      [org_id]
    );
    const rules = rulesResult.rows;
    const getRule = (name) => rules.find(r => r.rule_name === name);

    // Attendance deductions
    let lateDeduction = 0;
    const lateRule = getRule("Late Deduction");
    if (lateRule && (parseInt(att.total_late_minutes) || 0) > (lateRule.threshold_minutes || 0)) {
      const deductableMinutes = (parseInt(att.total_late_minutes) || 0) - (lateRule.threshold_minutes || 0);
      const pctFactor = lateRule.calculation_type === "percentage" ? (lateRule.percentage || 0) / 100 : 1;
      lateDeduction = Math.min(
        deductableMinutes * perMinuteSalary * pctFactor,
        perDaySalary * ((lateRule.max_per_day || 50) / 100)
      );
    }

    const absentRule = getRule("Absent Deduction");
    const absentDeduction = absentRule
      ? (parseInt(att.absent_days) || 0) * perDaySalary * ((absentRule.percentage || 100) / 100)
      : 0;

    const halfDayRule = getRule("Half Day Deduction");
    const halfDayDeduction = halfDayRule
      ? (parseInt(att.half_days) || 0) * perDaySalary * ((halfDayRule.percentage || 50) / 100)
      : 0;

    const lopRule = getRule("Loss of Pay");
    const lossOfPayDeduction = lopRule
      ? lossOfPayDays * perDaySalary * ((lopRule.percentage || 100) / 100)
      : 0;

    // Standard deductions
    let professionalTax = 0;
    const ptRule = getRule("Professional Tax");
    if (ptRule && monthlySalary > (ptRule.threshold_minutes || 20000)) {
      professionalTax = ptRule.calculation_type === "fixed"
        ? parseFloat(ptRule.fixed_amount || 200)
        : monthlySalary * ((ptRule.percentage || 0) / 100);
    }

    let pfEmployee = 0;
    const pfRule = getRule("Provident Fund");
    if (pfRule) {
      const pct = (pfRule.percentage || 12) / 100;
      const cap = parseFloat(pfRule.fixed_amount || 1800);
      pfEmployee = Math.min(monthlySalary * pct, cap);
    }

    // TDS calculation
    const annualIncome = monthlySalary * 12;
    let tds = 0;
    if (annualIncome > 250000) {
      if (annualIncome <= 500000) {
        tds = ((annualIncome - 250000) * 0.05) / 12;
      } else if (annualIncome <= 1000000) {
        tds = (12500 + (annualIncome - 500000) * 0.2) / 12;
      } else {
        tds = (112500 + (annualIncome - 1000000) * 0.3) / 12;
      }
    }

    // Manual deductions
    const manualResult = await pool.query(
      `SELECT * FROM deductions
       WHERE employee_id = $1 AND month = $2 AND year = $3
         AND is_manual = true AND is_deleted = false`,
      [employeeIdNum, monthNum, yearNum]
    );
    const manualDeductions = manualResult.rows;
    const manualTotal = manualDeductions.reduce((s, d) => s + parseFloat(d.amount), 0);

    const totalDeductions = professionalTax + pfEmployee + tds + lateDeduction + absentDeduction + halfDayDeduction + lossOfPayDeduction + manualTotal;

    res.json({
      success: true,
      data: {
        employee: {
          id: employee.id,
          code: employee.employee_code,
          name: `${employee.first_name} ${employee.last_name}`,
          department: employee.department,
          designation: employee.designation,
        },
        period: {
          month: monthNum,
          year: yearNum,
          month_name: new Date(yearNum, monthNum - 1).toLocaleString("default", { month: "long" }),
        },
        attendance: {
          total_days: totalDays,
          working_days: workingDays,
          paid_days: paidDays,
          loss_of_pay_days: lossOfPayDays,
          present_days: parseInt(att.present_days || 0),
          absent_days: parseInt(att.absent_days || 0),
          late_days: parseInt(att.late_days || 0),
          half_days: parseInt(att.half_days || 0),
          holiday_days: parseInt(att.holiday_days || 0),
          leave_days: parseInt(att.leave_days || 0),
          leaves_taken: parseInt(att.leave_days || 0),
          total_late_minutes: parseInt(att.total_late_minutes || 0),
        },
        salary: {
          monthly_gross: monthlySalary,
          per_day_salary: parseFloat(perDaySalary.toFixed(2)),
          per_hour_salary: parseFloat(perHourSalary.toFixed(2)),
          per_minute_salary: parseFloat(perMinuteSalary.toFixed(6)),
          basic: parseFloat((monthlySalary * 0.4).toFixed(2)),
          hra: parseFloat((monthlySalary * 0.2).toFixed(2)),
          conveyance: parseFloat((monthlySalary * 0.05).toFixed(2)),
          special_allowance: parseFloat((monthlySalary * 0.35).toFixed(2)),
          shift_allowance: 0,
          holiday_wages: 0,
          advance_bonus: 0,
        },
        deductions: {
          standard: {
            professional_tax: parseFloat(professionalTax.toFixed(2)),
            pf_employee: parseFloat(pfEmployee.toFixed(2)),
            tds: parseFloat(tds.toFixed(2)),
            total: parseFloat((professionalTax + pfEmployee + tds).toFixed(2)),
          },
          attendance: {
            late_deduction: parseFloat(lateDeduction.toFixed(2)),
            absent_deduction: parseFloat(absentDeduction.toFixed(2)),
            half_day_deduction: parseFloat(halfDayDeduction.toFixed(2)),
            loss_of_pay_deduction: parseFloat(lossOfPayDeduction.toFixed(2)),
            total: parseFloat((lateDeduction + absentDeduction + halfDayDeduction + lossOfPayDeduction).toFixed(2)),
          },
          manual: manualDeductions.map(m => ({
            id: m.id,
            deduction_type: m.deduction_type,
            amount: parseFloat(m.amount),
            reason: m.reason,
            month: m.month,
            year: m.year
          })),
          manual_total: parseFloat(manualTotal.toFixed(2)),
          total: parseFloat(totalDeductions.toFixed(2)),
        },
        net_salary: parseFloat((monthlySalary - totalDeductions).toFixed(2)),
      },
    });
  } catch (err) {
    console.error("GET DEDUCTION SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ MANUAL DEDUCTIONS
=========================== */

/**
 * ADD MANUAL DEDUCTION
 * POST /api/deductions/manual
 */
const addManualDeduction = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;
    const { employee_id, month, year, deduction_type, amount, reason } = req.body;

    if (!employee_id || !month || !year || !deduction_type || amount === undefined) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: employee_id, month, year, deduction_type, amount",
      });
    }

    const empResult = await pool.query(
      `SELECT id FROM employees WHERE employee_code = $1 AND status = 'Active' AND org_id = $2 AND is_deleted = false`,
      [employee_id, org_id]
    );

    if (empResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const result = await pool.query(
      `INSERT INTO deductions (
          employee_id, month, year, deduction_type, amount,
          reason, is_manual, org_id, created_by, created_at
        ) VALUES ($1,$2,$3,$4,$5,$6,true,$7,$8,NOW())
        RETURNING *`,
      [empResult.rows[0].id, month, year, deduction_type, amount, reason || null, org_id, userId]
    );

    res.status(201).json({
      success: true,
      message: "Manual deduction added successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("ADD MANUAL DEDUCTION ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * UPDATE MANUAL DEDUCTION
 * PUT /api/deductions/manual/:id
 */
const updateManualDeduction = async (req, res) => {
  try {
    const userId = req.session.user.id;
    const { id } = req.params;
    const { amount, reason } = req.body;

    const result = await pool.query(
      `UPDATE deductions SET
          amount     = COALESCE($1, amount),
          reason     = COALESCE($2, reason),
          updated_by = $3,
          updated_at = NOW()
       WHERE id = $4 AND is_manual = true AND is_deleted = false
       RETURNING *`,
      [amount, reason, userId, id]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Manual deduction not found" });
    }

    res.json({ success: true, message: "Manual deduction updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("UPDATE MANUAL DEDUCTION ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * DELETE MANUAL DEDUCTION (soft)
 * DELETE /api/deductions/manual/:id
 */
const deleteManualDeduction = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `UPDATE deductions SET is_deleted = true, deleted_at = NOW()
       WHERE id = $1 AND is_manual = true RETURNING id`,
      [id]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Manual deduction not found" });
    }

    res.json({ success: true, message: "Manual deduction deleted successfully" });
  } catch (err) {
    console.error("DELETE MANUAL DEDUCTION ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GENERATE PAYROLL
 * POST /api/deductions/payroll/generate
 */
const generatePayroll = async (req, res) => {
  const client = await pool.connect();
  try {
    const { employee_ids, month, year, payment_type = "Bank Transfer" } = req.body;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    const monthNum = toMonthNum(month);
    if (!employee_ids?.length || !monthNum || !year) {
      return res.status(400).json({ 
        success: false, 
        message: "employee_ids, month, and year are required" 
      });
    }

    const yearInt = parseInt(year);
    const totalDaysInMonth = new Date(yearInt, monthNum, 0).getDate();

    await client.query("BEGIN");

    const generated = [];
    const errors = [];

    for (const empId of employee_ids) {
      try {
        // Get employee details with department name
        const empResult = await client.query(
          `SELECT 
             e.id, 
             e.employee_code, 
             e.first_name, 
             e.last_name, 
             COALESCE(e.salary, 0) AS salary,
             e.department_id,
             COALESCE(d.dept_name, 'Unknown') AS department_name
           FROM employees e
           LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
           WHERE e.id = $1 
             AND e.status = 'Active' 
             AND e.is_deleted = false`,
          [empId]
        );

        if (!empResult.rows.length) {
          errors.push({ employee_id: empId, error: "Employee not found" });
          continue;
        }

        const emp = empResult.rows[0];
        const monthlySalary = parseFloat(emp.salary) || 0;

        // Get attendance for the month
        const attResult = await client.query(
          `SELECT
              COUNT(CASE WHEN attendance_status IN ('Present', 'Late', 'Overtime') THEN 1 END) AS present_days,
              COUNT(CASE WHEN attendance_status = 'Absent' THEN 1 END) AS absent_days,
              COUNT(CASE WHEN attendance_status = 'Late' THEN 1 END) AS late_days,
              COUNT(CASE WHEN attendance_status = 'Half Day' THEN 1 END) AS half_days,
              SUM(COALESCE(late_minutes, 0)) AS total_late_minutes,
              SUM(COALESCE(overtime_hours, 0)) AS total_overtime_hours
           FROM attendance
           WHERE employee_id = $1
             AND EXTRACT(YEAR FROM attendance_date) = $2
             AND EXTRACT(MONTH FROM attendance_date) = $3
             AND is_deleted = false`,
          [empId, yearInt, monthNum]
        );

        const att = attResult.rows[0] || {};
        const presentDays = parseInt(att.present_days) || 0;
        const absentDays = parseInt(att.absent_days) || 0;
        const lateDays = parseInt(att.late_days) || 0;
        const halfDays = parseInt(att.half_days) || 0;
        const lateMinutes = parseInt(att.total_late_minutes) || 0;
        const overtimeHours = Math.min(parseFloat(att.total_overtime_hours) || 0, 4);

        const workingDays = totalDaysInMonth;
        const paidDays = presentDays + (halfDays * 0.5);
        const perDaySalary = workingDays > 0 ? monthlySalary / workingDays : 0;
        const proratedGross = perDaySalary * paidDays;
        
        // Overtime calculation (1.5x hourly rate)
        const otPayment = overtimeHours > 0 ? overtimeHours * (perDaySalary / 8) * 1.5 : 0;
        
        // Standard deductions
        const pfEmployee = Math.min(monthlySalary * 0.12, 1800);
        
        let professionalTax = 0;
        if (proratedGross >= 20000) professionalTax = 200;
        else if (proratedGross >= 15000) professionalTax = 150;
        else if (proratedGross >= 10000) professionalTax = 100;
        
        // Attendance deductions
        const absentDeduction = absentDays * perDaySalary;
        const halfDayDeduction = halfDays * perDaySalary * 0.5;
        const lateDeduction = lateMinutes > 15
          ? Math.min((lateMinutes - 15) * (perDaySalary / 480) * 0.25, perDaySalary * 0.5)
          : 0;
        
        const totalDeductions = pfEmployee + professionalTax + absentDeduction + halfDayDeduction + lateDeduction;
        const netSalary = proratedGross + otPayment - totalDeductions;

        // Insert or update payroll record
        const upsertResult = await client.query(
          `INSERT INTO payroll_records (
             org_id, employee_id, department_id, month, year, payment_type,
             total_calendar_days, working_days, 
             present_days, absent_days, late_days, 
             half_days, total_late_minutes, paid_days, overtime_hours,
             gross_salary, ot_payment,
             pf_employee, professional_tax,
             late_deduction, absent_deduction, half_day_deduction,
             total_deductions, net_salary,
             status, payment_status, generated_by, created_at
           ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, 'Generated', 'Pending', $25, NOW())
           ON CONFLICT (employee_id, month, year) DO UPDATE SET
             department_id = EXCLUDED.department_id,
             present_days = EXCLUDED.present_days,
             absent_days = EXCLUDED.absent_days,
             late_days = EXCLUDED.late_days,
             half_days = EXCLUDED.half_days,
             total_late_minutes = EXCLUDED.total_late_minutes,
             paid_days = EXCLUDED.paid_days,
             overtime_hours = EXCLUDED.overtime_hours,
             gross_salary = EXCLUDED.gross_salary,
             ot_payment = EXCLUDED.ot_payment,
             pf_employee = EXCLUDED.pf_employee,
             professional_tax = EXCLUDED.professional_tax,
             late_deduction = EXCLUDED.late_deduction,
             absent_deduction = EXCLUDED.absent_deduction,
             half_day_deduction = EXCLUDED.half_day_deduction,
             total_deductions = EXCLUDED.total_deductions,
             net_salary = EXCLUDED.net_salary,
             updated_at = NOW()
           RETURNING id`,
          [
            org_id, empId, emp.department_id, monthNum, yearInt, payment_type,
            totalDaysInMonth, workingDays,
            presentDays, absentDays, lateDays,
            halfDays, lateMinutes, paidDays, overtimeHours,
            proratedGross, otPayment,
            pfEmployee, professionalTax,
            lateDeduction, absentDeduction, halfDayDeduction,
            totalDeductions, netSalary,
            userId
          ]
        );

        generated.push({
          employee_id: empId,
          employee_code: emp.employee_code,
          department_name: emp.department_name,
          payroll_id: upsertResult.rows[0].id,
          net_salary: netSalary,
        });
      } catch (empErr) {
        console.error(`Error generating payroll for employee ${empId}:`, empErr);
        errors.push({ employee_id: empId, error: empErr.message });
      }
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Payroll generated for ${generated.length} of ${employee_ids.length} employees`,
      data: { generated, errors },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("GENERATE PAYROLL ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};
/* ===========================
   ✅ DEDUCTION HISTORY
=========================== */

/**
 * GET DEDUCTION HISTORY
 * GET /api/deductions/history?employee_id=&year=
 */
const getDeductionHistory = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { employee_id, year } = req.query;

    let query = `
      SELECT d.*, e.employee_code,
             CONCAT(e.first_name, ' ', e.last_name) AS employee_name
      FROM deductions d
      JOIN employees e ON d.employee_id = e.id
      WHERE d.org_id = $1 AND d.is_deleted = false
    `;
    const params = [org_id];
    let idx = 2;

    if (employee_id) {
      query += ` AND e.employee_code = $${idx}`;
      params.push(employee_id);
      idx++;
    }
    if (year) {
      query += ` AND d.year = $${idx}`;
      params.push(parseInt(year));
      idx++;
    }

    query += ` ORDER BY d.year DESC, d.month DESC, d.created_at DESC`;

    const result = await pool.query(query, params);
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET DEDUCTION HISTORY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ PAYROLL PROCESSING
=========================== */

const MONTH_MAP = {
  January: 1, February: 2, March: 3, April: 4,
  May: 5, June: 6, July: 7, August: 8,
  September: 9, October: 10, November: 11, December: 12,
};

const toMonthNum = (month) =>
  typeof month === "number" ? month : MONTH_MAP[month] || null;

/**
 * GET PAYROLL GROUPS (Departments with names)
 * GET /api/deductions/payroll-groups
 */
const getPayrollGroups = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    // ✅ Get department names from departments table
    const result = await pool.query(
      `SELECT 
         d.id,
         d.dept_name AS group_name,
         COUNT(e.id) FILTER (WHERE e.status = 'Active' AND e.is_deleted = false) AS employee_count
       FROM departments d
       LEFT JOIN employees e ON e.department_id::text = d.id::text AND e.org_id = d.org_id
       WHERE d.org_id = $1 AND d.is_deleted = false AND d.status = true
       GROUP BY d.id, d.dept_name
       ORDER BY d.dept_name`,
      [org_id]
    );

    const totalResult = await pool.query(
      `SELECT COUNT(*) AS total 
       FROM employees 
       WHERE org_id = $1 AND status = 'Active' AND is_deleted = false`,
      [org_id]
    );

    const departments = result.rows.map(r => ({
      id: String(r.id),
      deptName: r.group_name,
      name: r.group_name,
      employee_count: parseInt(r.employee_count) || 0
    }));

    const groups = [
      {
        id: "all",
        deptName: "All Departments",
        name: "All Departments",
        employee_count: parseInt(totalResult.rows[0].total) || 0
      },
      ...departments
    ];

    res.json({ success: true, data: groups });
  } catch (err) {
    console.error("getPayrollGroups error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};


/**
 * RECALCULATE PAYROLL
 * POST /api/deductions/payroll/recalculate
 */
const recalculatePayroll = async (req, res) => {
  try {
    const { employee_id, month, year, department_id } = req.body;
    const org_id = req.session.user.orgId;

    let query = `
      SELECT pr.id, pr.employee_id, pr.month, pr.year, 
             e.salary, e.department_id
      FROM payroll_records pr
      JOIN employees e ON e.id = pr.employee_id
      WHERE pr.org_id = $1 AND pr.is_deleted = false
    `;
    const params = [org_id];
    let idx = 2;

    if (employee_id) {
      query += ` AND pr.employee_id = $${idx++}`;
      params.push(employee_id);
    }
    if (month) {
      query += ` AND pr.month = $${idx++}`;
      params.push(month);
    }
    if (year) {
      query += ` AND pr.year = $${idx++}`;
      params.push(year);
    }
    if (department_id && department_id !== 'all') {
      query += ` AND e.department_id = $${idx++}`;
      params.push(department_id);
    }

    const records = await pool.query(query, params);
    const DeductionEngine = require("../../services/deduction/deductionEngine");
    const engine = new DeductionEngine(org_id, month || null, year || null);
    
    let recalculated = 0;
    for (const record of records.rows) {
      const payroll = await engine.calculateEmployeePayroll(
        record.employee_id,
        record.department_id,
        parseFloat(record.salary)
      );
      
      await pool.query(
        `UPDATE payroll_records SET
           present_days = $1, absent_days = $2, late_days = $3,
           half_days = $4, loss_of_pay_days = $5, paid_days = $6,
           overtime_hours = $7, ot_payment = $8,
           gross_salary = $9, total_earnings = $10,
           pf_employee = $11, professional_tax = $12, tds = $13,
           late_deduction = $14, absent_deduction = $15,
           half_day_deduction = $16, lop_deduction = $17,
           total_deductions = $18, net_salary = $19,
           needs_recalculation = false, updated_at = NOW()
         WHERE id = $20`,
        [
          payroll.attendance.present_days,
          payroll.attendance.absent_days,
          payroll.attendance.late_days,
          payroll.attendance.half_days,
          payroll.loss_of_pay_days,
          payroll.paid_days,
          payroll.attendance.total_overtime_hours,
          payroll.ot_payment,
          payroll.prorated_gross,
          payroll.total_earnings,
          payroll.deductions.standard.pf_employee,
          payroll.deductions.standard.professional_tax,
          payroll.deductions.standard.tds,
          payroll.deductions.attendance.late_deduction,
          payroll.deductions.attendance.absent_deduction,
          payroll.deductions.attendance.half_day_deduction,
          payroll.deductions.attendance.loss_of_pay_deduction,
          payroll.deductions.total,
          payroll.net_salary,
          record.id
        ]
      );
      recalculated++;
    }

    res.json({
      success: true,
      message: `Recalculated ${recalculated} payroll records`,
      data: { recalculated }
    });
  } catch (err) {
    console.error("recalculatePayroll error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};


/**
 * GET MONTHLY PAYROLL SUMMARY (Department-wise)
 * GET /api/deductions/payroll/summary
 */
const getMonthlyPayrollSummary = async (req, res) => {
  try {
    const { month, year, department_id } = req.query;
    const org_id = req.session.user.orgId;
    const monthNum = toMonthNum(month);

    console.log("getMonthlyPayrollSummary params:", { month, year, department_id, monthNum });

    if (!monthNum || !year) {
      return res.status(400).json({ success: false, message: "month and year are required" });
    }

    const yearInt = parseInt(year);

    // Build query to get department-wise payroll summary
    let query = `
      SELECT 
        COALESCE(d.id, 0) AS department_id,
        COALESCE(d.dept_name, 'Unassigned') AS department_name,
        COUNT(DISTINCT pr.employee_id) AS employee_count,
        COUNT(*) FILTER (WHERE pr.payment_status = 'Paid') AS paid_count,
        COUNT(*) FILTER (WHERE pr.payment_status = 'Pending') AS pending_count,
        COUNT(*) FILTER (WHERE pr.payment_status = 'Generated') AS generated_count,
        COUNT(*) FILTER (WHERE pr.payment_status = 'Processing') AS processing_count,
        COALESCE(SUM(pr.gross_salary), 0) AS total_gross,
        COALESCE(SUM(pr.total_deductions), 0) AS total_deductions,
        COALESCE(SUM(pr.net_salary), 0) AS total_net,
        COALESCE(SUM(pr.ot_payment), 0) AS total_ot
      FROM payroll_records pr
      JOIN employees e ON e.id = pr.employee_id 
        AND e.status = 'Active' 
        AND e.is_deleted = false
      LEFT JOIN departments d ON d.id::text = e.department_id 
        AND d.is_deleted = false
      WHERE pr.month = $1 
        AND pr.year = $2 
        AND pr.org_id = $3 
        AND pr.is_deleted = false
    `;
    
    const params = [monthNum, yearInt, org_id];
    let paramIndex = 4;

    // Filter by department if specified
    if (department_id && department_id !== 'all' && department_id !== '0') {
      query += ` AND e.department_id = $${paramIndex}`;
      params.push(String(department_id));
      paramIndex++;
    }

    query += ` GROUP BY d.id, d.dept_name ORDER BY d.dept_name NULLS LAST`;

    console.log("Executing query");
    const result = await pool.query(query, params);
    console.log("Query result rows:", result.rows.length);

    // If no payroll records found, return attendance-based summary
    if (result.rows.length === 0) {
      const attendanceResult = await pool.query(
        `SELECT 
           COALESCE(d.id, 0) AS department_id,
           COALESCE(d.dept_name, 'Unassigned') AS department_name,
           COUNT(DISTINCT e.id) AS employee_count
         FROM employees e
         LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
         WHERE e.org_id = $1 AND e.status = 'Active' AND e.is_deleted = false
         GROUP BY d.id, d.dept_name`,
        [org_id]
      );
      
      const totals = {
        employee_count: attendanceResult.rows.reduce((sum, r) => sum + parseInt(r.employee_count), 0),
        total_gross: 0,
        total_deductions: 0,
        total_net: 0,
        total_ot: 0
      };
      
      return res.json({
        success: true,
        data: attendanceResult.rows,
        totals,
        grand_total: totals,
        month: monthNum,
        month_name: MONTH_NUM_TO_NAME[monthNum],
        year: yearInt,
        has_payroll_records: false,
      });
    }

    const totals = result.rows.reduce(
      (acc, row) => ({
        employee_count: acc.employee_count + parseInt(row.employee_count),
        paid_count: acc.paid_count + parseInt(row.paid_count),
        pending_count: acc.pending_count + parseInt(row.pending_count),
        generated_count: acc.generated_count + parseInt(row.generated_count),
        processing_count: acc.processing_count + parseInt(row.processing_count),
        total_gross: acc.total_gross + parseFloat(row.total_gross),
        total_deductions: acc.total_deductions + parseFloat(row.total_deductions),
        total_net: acc.total_net + parseFloat(row.total_net),
        total_ot: acc.total_ot + parseFloat(row.total_ot),
      }),
      { 
        employee_count: 0, paid_count: 0, pending_count: 0, 
        generated_count: 0, processing_count: 0, 
        total_gross: 0, total_deductions: 0, total_net: 0, total_ot: 0 
      }
    );

    res.json({
      success: true,
      data: result.rows,
      totals,
      grand_total: totals,
      month: monthNum,
      month_name: MONTH_NUM_TO_NAME[monthNum],
      year: yearInt,
      has_payroll_records: true,
    });
  } catch (err) {
    console.error("getMonthlyPayrollSummary error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GET GROUP SUMMARY BY MONTH
 * GET /api/deductions/payroll/group-summary
 */
const getGroupSummaryByMonth = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { month, year } = req.query;
    const monthNum = toMonthNum(month);

    if (!monthNum || !year) {
      return res.status(400).json({ success: false, message: "month and year are required" });
    }

    const result = await pool.query(
      `SELECT 
         COALESCE(d.dept_name, 'Unassigned') AS group_name,
         COUNT(DISTINCT pr.employee_id) AS employee_count,
         COALESCE(SUM(pr.gross_salary), 0) AS total_gross,
         COALESCE(SUM(pr.total_deductions), 0) AS total_deductions,
         COALESCE(SUM(pr.net_salary), 0) AS total_net,
         COUNT(*) FILTER (WHERE pr.payment_status = 'Paid') AS paid_count,
         COUNT(*) FILTER (WHERE pr.payment_status = 'Pending') AS pending_count
       FROM payroll_records pr
       JOIN employees e ON e.id = pr.employee_id
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       WHERE pr.month = $1 AND pr.year = $2 AND pr.org_id = $3 AND pr.is_deleted = false
       GROUP BY d.dept_name 
       ORDER BY group_name`,
      [monthNum, parseInt(year), org_id]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET GROUP SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GET EMPLOYEE PAYROLL DETAILS
 * GET /api/deductions/payroll/details
 */
const getEmployeePayrollDetails = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { month, year, group_name } = req.query;
    const monthNum = toMonthNum(month);

    if (!monthNum || !year) {
      return res.status(400).json({ success: false, message: "month and year are required" });
    }

    let query = `
      SELECT pr.id, pr.employee_id, pr.month, pr.year,
             pr.gross_salary, pr.total_deductions, pr.net_salary,
             pr.payment_status AS status, pr.paid_date, pr.payment_type, pr.created_at,
             e.employee_code,
             e.first_name || ' ' || e.last_name AS employee_name,
             COALESCE(des.designation_name, 'Unknown') AS designation,
             COALESCE(d.dept_name, 'Unknown') AS department,
             COALESCE(d.dept_name, 'Unassigned') AS payroll_group
      FROM payroll_records pr
      JOIN employees e ON e.id = pr.employee_id
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE pr.month = $1 AND pr.year = $2 AND pr.org_id = $3 AND pr.is_deleted = false
    `;
    const params = [monthNum, parseInt(year), org_id];
    let paramIndex = 4;

    if (group_name && group_name !== "All" && group_name !== "all") {
      query += ` AND d.dept_name = $${paramIndex}`;
      params.push(group_name);
      paramIndex++;
    }

    query += ` ORDER BY d.dept_name NULLS LAST, e.first_name`;

    const result = await pool.query(query, params);
    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET EMPLOYEE PAYROLL DETAILS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * GET PAYROLL BY ID
 * GET /api/deductions/payroll/:id
 */
const getPayrollById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `SELECT pr.*,
              e.employee_code,
              e.first_name || ' ' || e.last_name AS employee_name,
              COALESCE(des.designation_name, 'Unknown') AS designation,
              COALESCE(d.dept_name, 'Unknown') AS department
       FROM payroll_records pr
       JOIN employees e ON e.id = pr.employee_id
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       WHERE pr.id = $1 AND pr.is_deleted = false`,
      [id]
    );

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }

    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET PAYROLL BY ID ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * UPDATE PAYROLL STATUS
 * PUT /api/deductions/payroll/:id/status
 */
const updatePayrollStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status, paid_date } = req.body;
    const userId = req.session.user.id;

    const validStatuses = ["Pending", "Generated", "Processing", "Paid", "Cancelled", "Hold"];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({ success: false, message: "Invalid status" });
    }

    const result = await pool.query(
      `UPDATE payroll_records
       SET payment_status = $1, paid_date = $2, updated_by = $3, updated_at = NOW()
       WHERE id = $4 AND is_deleted = false
       RETURNING *`,
      [status, paid_date || null, userId, id]
    );

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }

    res.json({ success: true, message: "Payroll status updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("UPDATE PAYROLL STATUS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * DELETE PAYROLL (soft)
 * DELETE /api/deductions/payroll/:id
 */
const deletePayroll = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `UPDATE payroll_records SET is_deleted = true, updated_at = NOW()
       WHERE id = $1 RETURNING id`,
      [id]
    );

    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }

    res.json({ success: true, message: "Payroll record deleted successfully" });
  } catch (err) {
    console.error("DELETE PAYROLL ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};







/**
 * GET EMPLOYEES FOR PAYROLL (WITH DEPARTMENT/DESIGNATION NAMES)
 * GET /api/deductions/payroll/employees
 */
const getEmployeesForPayroll = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { month, year, department_id } = req.query;
    const monthNum = toMonthNum(month);

    if (!monthNum || !year) {
      return res.status(400).json({ success: false, message: "month and year are required" });
    }

    // ✅ JOIN with departments and designations tables
    let query = `
      SELECT 
        e.id AS employee_db_id,
        e.employee_code,
        TRIM(e.first_name || ' ' || COALESCE(e.last_name, '')) AS employee_name,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.department_id,
        COALESCE(d.dept_name, 'Unknown') AS department_name,
        COALESCE(e.salary, 0) AS monthly_salary
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE e.org_id = $1 
        AND e.is_deleted = false 
        AND e.status = 'Active'
    `;
    const params = [org_id];
    let paramIndex = 2;

    if (department_id && department_id !== 'all') {
      query += ` AND e.department_id = $${paramIndex}`;
      params.push(String(department_id));
      paramIndex++;
    }

    query += ` ORDER BY d.dept_name NULLS LAST, e.first_name`;

    const empResult = await pool.query(query, params);
    const employees = empResult.rows;

    if (!employees.length) {
      return res.json({ success: true, data: [] });
    }

    const DeductionEngine = require("../../services/deduction/deductionEngine");
    const engine = new DeductionEngine(org_id, monthNum, parseInt(year));
    const enriched = [];

    for (const emp of employees) {
      const monthlySalary = parseFloat(emp.monthly_salary) || 0;
      const payroll = await engine.calculateEmployeePayroll(
        emp.employee_db_id,
        emp.department_id,
        monthlySalary
      );

      const existingResult = await pool.query(
        `SELECT id, payment_status FROM payroll_records
         WHERE employee_id = $1 AND month = $2 AND year = $3 AND is_deleted = false
         LIMIT 1`,
        [emp.employee_db_id, monthNum, parseInt(year)]
      );
      const existing = existingResult.rows[0];

      enriched.push({
        id: emp.employee_db_id,
        employee_db_id: emp.employee_db_id,
        employee_code: emp.employee_code,
        employee_name: emp.employee_name,
        designation: emp.designation,
        department_id: emp.department_id,
        department_name: emp.department_name,
        present_days: payroll.attendance.present_days,
        absent_days: payroll.attendance.absent_days,
        late_days: payroll.attendance.late_days,
        half_days: payroll.attendance.half_days,
        paid_days: payroll.paid_days,
        loss_of_pay_days: payroll.loss_of_pay_days,
        total_late_minutes: payroll.attendance.total_late_minutes,
        overtime_hours: payroll.attendance.total_overtime_hours,
        monthly_salary: monthlySalary,
        gross_salary: payroll.prorated_gross,
        prorated_gross: payroll.prorated_gross,
        ot_payment: payroll.ot_payment,
        total_earnings: payroll.total_earnings,
        pf_employee: payroll.deductions.standard.pf_employee,
        professional_tax: payroll.deductions.standard.professional_tax,
        tds: payroll.deductions.standard.tds,
        late_deduction: payroll.deductions.attendance.late_deduction,
        absent_deduction: payroll.deductions.attendance.absent_deduction,
        half_day_deduction: payroll.deductions.attendance.half_day_deduction,
        lop_deduction: payroll.deductions.attendance.loss_of_pay_deduction,
        total_deductions: payroll.deductions.total,
        net_salary: payroll.net_salary,
        already_generated: !!existing,
        payroll_id: existing?.id || null,
        payment_status: existing?.payment_status || null,
      });
    }

    res.json({
      success: true,
      data: enriched,
      month: monthNum,
      year: parseInt(year),
      total_employees: enriched.length
    });
  } catch (err) {
    console.error("getEmployeesForPayroll error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};


/**
 * Generate PDF for employee deduction report using Puppeteer
 * POST /api/deductions/generate-pdf
 */
const generateDeductionPDF = async (req, res) => {
  let browser = null;
  
  try {
    const { employee_id, month, year } = req.body;
    const org_id = req.session.user.orgId;

    if (!employee_id || !month || !year) {
      return res.status(400).json({ 
        success: false, 
        message: "employee_id, month, and year are required" 
      });
    }

    // Fetch employee data with department and designation names
    const empResult = await pool.query(
      `SELECT e.id, e.employee_code, e.first_name, e.last_name,
              e.salary,
              COALESCE(d.dept_name, 'Unknown') AS department_name,
              COALESCE(des.designation_name, 'Unknown') AS designation_name
       FROM employees e
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       WHERE e.employee_code = $1 AND e.org_id = $2 
       AND e.status = 'Active' AND e.is_deleted = false`,
      [employee_id, org_id]
    );

    if (empResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const employee = empResult.rows[0];
    const monthlySalary = parseFloat(employee.salary) || 0;
    const monthNum = parseInt(month);
    const yearNum = parseInt(year);
    const monthName = new Date(yearNum, monthNum - 1).toLocaleString('default', { month: 'long' });

    // Get attendance data
    const attResult = await pool.query(
      `SELECT
          COUNT(CASE WHEN attendance_status IN ('Present','Late','Overtime') THEN 1 END) AS present_days,
          COUNT(CASE WHEN attendance_status = 'Absent' THEN 1 END) AS absent_days,
          COUNT(CASE WHEN attendance_status = 'Late' THEN 1 END) AS late_days,
          COUNT(CASE WHEN attendance_status = 'Half Day' THEN 1 END) AS half_days,
          COUNT(CASE WHEN attendance_status = 'Leave' THEN 1 END) AS leave_days,
          SUM(COALESCE(late_minutes, 0)) AS total_late_minutes
       FROM attendance
       WHERE employee_id = $1
         AND EXTRACT(YEAR FROM attendance_date) = $2
         AND EXTRACT(MONTH FROM attendance_date) = $3
         AND is_deleted = false`,
      [employee.id, yearNum, monthNum]
    );

    const att = attResult.rows[0] || {};
    const totalDays = new Date(yearNum, monthNum, 0).getDate();
    const workingDays = totalDays - (parseInt(att.leave_days) || 0);
    const paidDays = parseInt(att.present_days || 0) + (parseInt(att.half_days || 0) * 0.5);
    const lossOfPayDays = Math.max(0, workingDays - paidDays);
    const perDaySalary = workingDays > 0 ? monthlySalary / workingDays : 0;

    // Get deduction rules
    const rulesResult = await pool.query(
      `SELECT * FROM deduction_rules
       WHERE (org_id = $1 OR org_id IS NULL) 
       AND is_active = true AND is_deleted = false`,
      [org_id]
    );
    const rules = rulesResult.rows;
    const getRule = (name) => rules.find(r => r.rule_name === name);

    // Standard deductions
    let professionalTax = 0;
    const ptRule = getRule("Professional Tax");
    if (ptRule && monthlySalary > (ptRule.threshold_minutes || 20000)) {
      professionalTax = ptRule.calculation_type === 'fixed' 
        ? parseFloat(ptRule.fixed_amount || 200)
        : monthlySalary * ((ptRule.percentage || 0) / 100);
    } else {
      if (monthlySalary >= 20000) professionalTax = 200;
      else if (monthlySalary >= 15000) professionalTax = 150;
      else if (monthlySalary >= 10000) professionalTax = 100;
    }

    let pfEmployee = 0;
    const pfRule = getRule("Provident Fund");
    if (pfRule) {
      const pct = (pfRule.percentage || 12) / 100;
      const cap = parseFloat(pfRule.fixed_amount || 1800);
      pfEmployee = Math.min(monthlySalary * pct, cap);
    } else {
      pfEmployee = Math.min(monthlySalary * 0.12, 1800);
    }

    let tds = 0;
    const annualIncome = monthlySalary * 12;
    if (annualIncome > 250000) {
      if (annualIncome <= 500000) tds = ((annualIncome - 250000) * 0.05) / 12;
      else if (annualIncome <= 1000000) tds = (12500 + (annualIncome - 500000) * 0.2) / 12;
      else tds = (112500 + (annualIncome - 1000000) * 0.3) / 12;
    }

    // Attendance deductions
    let lateDeduction = 0;
    const lateRule = getRule("Late Deduction");
    if (lateRule && (parseInt(att.total_late_minutes || 0) > (lateRule.threshold_minutes || 0))) {
      const deductableMinutes = parseInt(att.total_late_minutes) - (lateRule.threshold_minutes || 0);
      const perMinuteSalary = perDaySalary / 480;
      const pctFactor = lateRule.calculation_type === 'percentage' ? (lateRule.percentage || 0) / 100 : 1;
      lateDeduction = Math.min(deductableMinutes * perMinuteSalary * pctFactor, perDaySalary * ((lateRule.max_per_day || 50) / 100));
    } else {
      const lateMinutes = parseInt(att.total_late_minutes || 0);
      if (lateMinutes > 15) {
        lateDeduction = Math.min((lateMinutes - 15) * (perDaySalary / 480) * 0.25, perDaySalary * 0.5);
      }
    }

    const absentRule = getRule("Absent Deduction");
    const absentDeduction = absentRule 
      ? (parseInt(att.absent_days || 0) * perDaySalary * ((absentRule.percentage || 100) / 100))
      : (parseInt(att.absent_days || 0) * perDaySalary);

    const halfDayRule = getRule("Half Day Deduction");
    const halfDayDeduction = halfDayRule
      ? (parseInt(att.half_days || 0) * perDaySalary * ((halfDayRule.percentage || 50) / 100))
      : (parseInt(att.half_days || 0) * perDaySalary * 0.5);

    const lopRule = getRule("Loss of Pay");
    const lossOfPayDeduction = lopRule
      ? lossOfPayDays * perDaySalary * ((lopRule.percentage || 100) / 100)
      : lossOfPayDays * perDaySalary;

    const totalDeductions = professionalTax + pfEmployee + tds + lateDeduction + absentDeduction + halfDayDeduction + lossOfPayDeduction;
    const netSalary = monthlySalary - totalDeductions;

    // Prepare data for PDF
    const pdfData = {
      employee: {
        name: `${employee.first_name} ${employee.last_name}`,
        code: employee.employee_code,
        department: employee.department_name,
        designation: employee.designation_name
      },
      period: {
        month: monthName,
        year: yearNum,
        working_days: workingDays
      },
      salary: {
        monthly_gross: monthlySalary,
        basic: monthlySalary * 0.4,
        hra: monthlySalary * 0.2,
        conveyance: monthlySalary * 0.05,
        special_allowance: monthlySalary * 0.35
      },
      attendance: {
        present_days: parseInt(att.present_days || 0),
        absent_days: parseInt(att.absent_days || 0),
        late_days: parseInt(att.late_days || 0),
        half_days: parseInt(att.half_days || 0),
        loss_of_pay_days: lossOfPayDays,
        total_late_minutes: parseInt(att.total_late_minutes || 0)
      },
      deductions: {
        standard: {
          professional_tax: professionalTax,
          pf_employee: pfEmployee,
          tds: tds,
          total: professionalTax + pfEmployee + tds
        },
        attendance: {
          late_deduction: lateDeduction,
          absent_deduction: absentDeduction,
          half_day_deduction: halfDayDeduction,
          loss_of_pay_deduction: lossOfPayDeduction,
          total: lateDeduction + absentDeduction + halfDayDeduction + lossOfPayDeduction
        },
        total: totalDeductions
      },
      net_salary: netSalary
    };

    // Generate HTML
    const html = generatePDFHTML(pdfData);

    // Launch Puppeteer
    browser = await puppeteer.launch({
      headless: 'new',
      args: ['--no-sandbox', '--disable-setuid-sandbox', '--disable-dev-shm-usage']
    });
    
    const page = await browser.newPage();
    await page.setContent(html, { waitUntil: 'networkidle0', timeout: 30000 });
    
    const pdfBuffer = await page.pdf({
      format: 'A4',
      printBackground: true,
      margin: { top: '20px', right: '20px', bottom: '20px', left: '20px' },
      scale: 0.95
    });
    
    await browser.close();

    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="deduction_${employee.employee_code}_${monthName}_${yearNum}.pdf"`);
    res.send(pdfBuffer);

  } catch (err) {
    console.error("Generate PDF error:", err);
    if (browser) await browser.close();
    res.status(500).json({ success: false, message: err.message });
  }
};

// Helper function to generate HTML for PDF
const generatePDFHTML = (data) => {
  return `<!DOCTYPE html>
  <html>
  <head>
    <meta charset="UTF-8">
    <title>Deduction Report - ${data.employee.name}</title>
    <style>
      * { margin: 0; padding: 0; box-sizing: border-box; }
      body { 
        font-family: Arial, Helvetica, sans-serif; 
        font-size: 11px; 
        padding: 15px; 
        line-height: 1.4;
      }
      .header { text-align: center; margin-bottom: 15px; border-bottom: 2px solid #333; padding-bottom: 8px; }
      .header h2 { font-size: 16px; margin-bottom: 3px; }
      .header p { color: #666; font-size: 10px; }
      .employee-info { background: #f5f5f5; padding: 8px; margin-bottom: 12px; border-radius: 4px; }
      .employee-info table { width: 100%; }
      .employee-info td { padding: 3px 5px; }
      .section { margin-bottom: 12px; }
      .section-title { background: #3b82f6; color: white; padding: 4px 8px; font-size: 11px; font-weight: bold; margin-bottom: 6px; }
      .section-title.attendance { background: #8b5cf6; }
      .section-title.deductions { background: #ef4444; }
      .section-title.salary { background: #10b981; }
      table { width: 100%; border-collapse: collapse; }
      th, td { border: 1px solid #ddd; padding: 5px; text-align: left; }
      th { background: #f0f0f0; font-weight: bold; }
      .text-right { text-align: right; }
      .text-center { text-align: center; }
      .net-pay { background: #10b981; color: white; padding: 10px; margin-top: 10px; border-radius: 4px; text-align: center; }
      .net-pay h3 { font-size: 14px; margin-bottom: 3px; }
      .net-pay .amount { font-size: 20px; font-weight: bold; }
      .footer { text-align: center; margin-top: 15px; padding-top: 8px; border-top: 1px solid #ddd; font-size: 8px; color: #666; }
      .row { display: flex; gap: 10px; margin-bottom: 10px; }
      .col { flex: 1; }
    </style>
  </head>
  <body>
    <div class="header">
      <h2>Employee Deduction Statement</h2>
      <p>Generated on ${new Date().toLocaleDateString()}</p>
    </div>

    <div class="employee-info">
      <table>
        <tr>
          <td width="25%"><strong>Employee Name:</strong></td>
          <td>${data.employee.name}</td>
          <td width="25%"><strong>Employee Code:</strong></td>
          <td>${data.employee.code}</td>
        </tr>
        <tr>
          <td><strong>Department:</strong></td>
          <td>${data.employee.department}</td>
          <td><strong>Designation:</strong></td>
          <td>${data.employee.designation}</td>
        </tr>
        <tr>
          <td><strong>Period:</strong></td>
          <td>${data.period.month} ${data.period.year}</td>
          <td><strong>Working Days:</strong></td>
          <td>${data.period.working_days}</td>
        </tr>
      </table>
    </div>

    <div class="section">
      <div class="section-title attendance">Attendance Summary</div>
      <div class="row">
        <div class="col"><strong>Present:</strong> ${data.attendance.present_days}</div>
        <div class="col"><strong>Absent:</strong> ${data.attendance.absent_days}</div>
        <div class="col"><strong>Late Days:</strong> ${data.attendance.late_days}</div>
        <div class="col"><strong>Half Days:</strong> ${data.attendance.half_days}</div>
        <div class="col"><strong>LOP Days:</strong> ${data.attendance.loss_of_pay_days}</div>
        <div class="col"><strong>Late Mins:</strong> ${data.attendance.total_late_minutes}</div>
      </div>
    </div>

    <div class="section">
      <div class="section-title salary">Salary Breakdown</div>
      <table>
        <tr>
          <th width="60%">Component</th>
          <th class="text-right">Amount (₹)</th>
        </tr>
        <tr>
          <td>Basic Salary</td>
          <td class="text-right">${Math.round(data.salary.basic).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>HRA</td>
          <td class="text-right">${Math.round(data.salary.hra).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Conveyance</td>
          <td class="text-right">${Math.round(data.salary.conveyance).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Special Allowance</td>
          <td class="text-right">${Math.round(data.salary.special_allowance).toLocaleString('en-IN')}</td>
        </tr>
        <tr style="background:#f0f0f0; font-weight:bold;">
          <td>Gross Salary</td>
          <td class="text-right">${Math.round(data.salary.monthly_gross).toLocaleString('en-IN')}</td>
        </tr>
      </table>
    </div>

    <div class="section">
      <div class="section-title deductions">Deduction Details</div>
      <table>
        <tr>
          <th width="60%">Deduction Type</th>
          <th class="text-right">Amount (₹)</th>
        </tr>
        <tr>
          <td>Professional Tax</td>
          <td class="text-right">${Math.round(data.deductions.standard.professional_tax).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Provident Fund (Employee)</td>
          <td class="text-right">${Math.round(data.deductions.standard.pf_employee).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>TDS</td>
          <td class="text-right">${Math.round(data.deductions.standard.tds).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Late Deduction</td>
          <td class="text-right">${Math.round(data.deductions.attendance.late_deduction).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Absent Deduction</td>
          <td class="text-right">${Math.round(data.deductions.attendance.absent_deduction).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Half Day Deduction</td>
          <td class="text-right">${Math.round(data.deductions.attendance.half_day_deduction).toLocaleString('en-IN')}</td>
        </tr>
        <tr>
          <td>Loss of Pay (LOP)</td>
          <td class="text-right">${Math.round(data.deductions.attendance.loss_of_pay_deduction).toLocaleString('en-IN')}</td>
        </tr>
        <tr style="background:#f0f0f0; font-weight:bold;">
          <td>Total Deductions</td>
          <td class="text-right">${Math.round(data.deductions.total).toLocaleString('en-IN')}</td>
        </tr>
      </table>
    </div>

    <div class="net-pay">
      <h3>Net Payable Amount</h3>
      <div class="amount">₹ ${Math.round(data.net_salary).toLocaleString('en-IN')}</div>
      <small>After all deductions</small>
    </div>

    <div class="footer">
      <p>This is a computer-generated document. No signature required.</p>
    </div>
  </body>
  </html>`;
};
// Keep the rest of your functions (generatePayroll, getMonthlyPayrollSummary, etc.) as they are
// They already work correctly with the joins

/* ===========================
   EXPORTS
=========================== */
module.exports = {
  createDeductionRule,
  getDeductionRules,
  getDeductionRuleById,
  updateDeductionRule,
  deleteDeductionRule,
  getEmployeeSalary,
  updateEmployeeSalary,
  createEmployeeSalary,
  deleteEmployeeSalary,
  getAllEmployeesSalary,
  getDeductionSummary,
  addManualDeduction,
  updateManualDeduction,
  deleteManualDeduction,
  getDeductionHistory,
  getPayrollGroups,
  getEmployeesForPayroll,
  generatePayroll,
  getMonthlyPayrollSummary,
  getGroupSummaryByMonth,
  getEmployeePayrollDetails,
  getPayrollById,
  updatePayrollStatus,
  deletePayroll,
  recalculatePayroll,
  generateDeductionPDF,
};