

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

// Helper functions
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"
};

function toMonthNum(monthStr) {
  if (!monthStr) return null;
  if (!isNaN(monthStr)) return parseInt(monthStr, 10);
  const 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
  };
  return map[monthStr] ?? null;
}

// ============================================================
// GET PAYROLL GROUPS (Returns departments for dropdown)
// ============================================================
const getPayrollGroups = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

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

// ============================================================
// GET EMPLOYEES FOR PAYROLL (UPDATED WITH CORRECT CALCULATIONS)
// ============================================================
const getEmployeesForPayroll = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { month, year, group_id } = req.query;
    const monthNum = toMonthNum(month);

    console.log("getEmployeesForPayroll params:", { month, year, group_id, monthNum });

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

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

    // Fetch employees with department and designation
    let empQuery = `
      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, 'Unassigned') 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 empParams = [org_id];
    let paramIndex = 2;

    if (group_id && group_id !== "all" && group_id !== "0") {
      empQuery += ` AND e.department_id = $${paramIndex}`;
      empParams.push(String(group_id));
      paramIndex++;
    }

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

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

    if (!employees.length) {
      return res.json({
        success: true,
        data: [],
        month: monthNum,
        year: yearInt,
        total_employees: 0,
      });
    }

    const employeeIds = employees.map((e) => e.employee_db_id);

    // Fetch attendance
    const attResult = await pool.query(
      `SELECT
         employee_id,
         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,
         COUNT(CASE WHEN attendance_status = 'Holiday'  THEN 1 END) AS holiday_days,
         COALESCE(SUM(overtime_hours), 0)  AS total_overtime_hours,
         COALESCE(SUM(late_minutes), 0)    AS total_late_minutes
       FROM attendance
       WHERE employee_id = ANY($1::int[])
         AND EXTRACT(YEAR  FROM attendance_date) = $2
         AND EXTRACT(MONTH FROM attendance_date) = $3
         AND is_deleted = false
       GROUP BY employee_id`,
      [employeeIds, yearInt, monthNum]
    );

    const attMap = {};
    attResult.rows.forEach((row) => {
      attMap[row.employee_id] = row;
    });

    // Fetch existing payroll records
    const prResult = await pool.query(
      `SELECT employee_id, payment_status, net_salary, gross_salary,
              total_deductions, ot_payment, paid_days, pf_employee, professional_tax
       FROM payroll_records
       WHERE employee_id = ANY($1::int[])
         AND month = $2
         AND year  = $3
         AND org_id = $4
         AND is_deleted = false`,
      [employeeIds, monthNum, yearInt, org_id]
    );

    const prMap = {};
    prResult.rows.forEach((row) => {
      prMap[row.employee_id] = row;
    });

    // Get deduction rules for accurate calculations
    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);

    const enriched = employees.map((emp) => {
      const att = attMap[emp.employee_db_id] || {};
      const pr = prMap[emp.employee_db_id] || null;

      const monthlySalary = parseFloat(emp.monthly_salary) || 0;
      
      // Calculate working days (total days - holidays - leaves)
      const holidayDays = parseInt(att.holiday_days) || 0;
      const leaveDays = parseInt(att.leave_days) || 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 otHours = Math.min(parseFloat(att.total_overtime_hours) || 0, 4);

      const workingDays = totalDaysInMonth - holidayDays;
      const paidDays = presentDays + (halfDays * 0.5) + leaveDays;
      const lossOfPayDays = Math.max(0, workingDays - paidDays);
      const perDaySalary = workingDays > 0 ? monthlySalary / workingDays : 0;
      const perHourSalary = perDaySalary / 8;
      const perMinuteSalary = perHourSalary / 60;
      
      const proratedGross = workingDays > 0 ? perDaySalary * paidDays : monthlySalary;

      // Overtime payment (1.5x hourly rate)
      const otPayment = otHours > 0 ? otHours * (perDaySalary / 8) * 1.5 : 0;

      // Get deduction rules and calculate
      const pfRule = getRule("Provident Fund");
      const pfEmployee = pfRule
        ? Math.min(monthlySalary * ((pfRule.percentage || 12) / 100), parseFloat(pfRule.fixed_amount || 1800))
        : Math.min(monthlySalary * 0.12, 1800);

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

      // TDS calculation
      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 using rules
      const lateRule = getRule("Late Deduction");
      let lateDeduction = 0;
      if (lateRule && lateMinutes > (lateRule.threshold_minutes || 0)) {
        const deductableMinutes = lateMinutes - (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)
        );
      } else if (lateMinutes > 15) {
        lateDeduction = Math.min((lateMinutes - 15) * (perDaySalary / 480) * 0.25, perDaySalary * 0.5);
      }

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

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

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

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

      // Calculate salary components
      const basic = monthlySalary * 0.4;
      const hra = monthlySalary * 0.2;
      const conveyance = monthlySalary * 0.05;
      const specialAllowance = monthlySalary * 0.35;

      return {
        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 || "Unassigned",
        monthly_salary: monthlySalary,
        basic: basic,
        hra: hra,
        conveyance: conveyance,
        special_allowance: specialAllowance,
        total_calendar_days: totalDaysInMonth,
        working_days: workingDays,
        present_days: presentDays,
        absent_days: absentDays,
        late_days: lateDays,
        half_days: halfDays,
        leave_days: leaveDays,
        holiday_days: holidayDays,
        loss_of_pay_days: lossOfPayDays,
        total_late_minutes: lateMinutes,
        paid_days: paidDays,
        overtime_hours: otHours,
        gross_salary: monthlySalary,
        prorated_gross: proratedGross,
        ot_payment: otPayment,
        pf_employee: pfEmployee,
        professional_tax: professionalTax,
        tds: tds,
        late_deduction: lateDeduction,
        absent_deduction: absentDeduction,
        half_day_deduction: halfDayDeduction,
        loss_of_pay_deduction: lossOfPayDeduction,
        total_deductions: totalDeductions,
        net_salary: netSalary,
        already_generated: !!pr,
        payment_status: pr?.payment_status || null,
      };
    });

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

// ============================================================
// GET MONTHLY PAYROLL SUMMARY (Department-wise)
// ============================================================
const getMonthlyPayrollSummary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { month, year, department_id } = req.query;
    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);

    // ✅ FIXED: Use department_id column (VARCHAR) with ::text casting
    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 using department_id column
    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);

    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] || month,
      year: yearInt,
      has_payroll_records: result.rows.length > 0,
    });
  } catch (err) {
    console.error("getMonthlyPayrollSummary error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// GENERATE PAYROLL (UPDATED WITH CORRECT CALCULATIONS)
// ============================================================
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();

    // Get deduction rules
    const rulesResult = await client.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);

    await client.query("BEGIN");

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

    for (const empId of employee_ids) {
      try {
        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,
             d.dept_name 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 employee = empResult.rows[0];
        const monthlySalary = parseFloat(employee.salary) || 0;
        const departmentId = employee.department_id ? parseInt(employee.department_id) : null;

        // Fetch attendance
        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,
             COUNT(CASE WHEN attendance_status = 'Leave'    THEN 1 END) AS leave_days,
             COUNT(CASE WHEN attendance_status = 'Holiday'  THEN 1 END) AS holiday_days,
             COALESCE(SUM(overtime_hours), 0) AS total_overtime_hours,
             COALESCE(SUM(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`,
          [empId, yearInt, monthNum]
        );

        const att = attResult.rows[0] || {};

        const holidayDays = parseInt(att.holiday_days) || 0;
        const leaveDays = parseInt(att.leave_days) || 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 otHours = Math.min(parseFloat(att.total_overtime_hours) || 0, 4);

        const workingDays = totalDaysInMonth - holidayDays;
        const paidDays = presentDays + (halfDays * 0.5) + leaveDays;
        const lossOfPayDays = Math.max(0, workingDays - paidDays);
        const perDaySalary = workingDays > 0 ? monthlySalary / workingDays : 0;
        const perHourSalary = perDaySalary / 8;
        const perMinuteSalary = perHourSalary / 60;

        const proratedGross = workingDays > 0 ? perDaySalary * paidDays : monthlySalary;
        const otPayment = otHours > 0 ? otHours * (perDaySalary / 8) * 1.5 : 0;

        // Calculate using deduction rules
        const pfRule = getRule("Provident Fund");
        const pfEmployee = pfRule
          ? Math.min(monthlySalary * ((pfRule.percentage || 12) / 100), parseFloat(pfRule.fixed_amount || 1800))
          : Math.min(monthlySalary * 0.12, 1800);

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

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

        const lateRule = getRule("Late Deduction");
        let lateDeduction = 0;
        if (lateRule && lateMinutes > (lateRule.threshold_minutes || 0)) {
          const deductableMinutes = lateMinutes - (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)
          );
        } else if (lateMinutes > 15) {
          lateDeduction = Math.min((lateMinutes - 15) * (perDaySalary / 480) * 0.25, perDaySalary * 0.5);
        }

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

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

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

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

        // Calculate salary components
        const basic = monthlySalary * 0.4;
        const hra = monthlySalary * 0.2;
        const conveyance = monthlySalary * 0.05;
        const specialAllowance = monthlySalary * 0.35;

        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, leave_days,
             total_late_minutes, paid_days, loss_of_pay_days, overtime_hours,
             gross_salary, basic, hra, conveyance, special_allowance, ot_payment,
             pf_employee, professional_tax, tds,
             late_deduction, absent_deduction, half_day_deduction, lop_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, $25, $26,
             $27, $28, $29, $30,
             $31, $32,
             'Generated', 'Generated', $33, 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,
             leave_days = EXCLUDED.leave_days,
             total_late_minutes = EXCLUDED.total_late_minutes,
             paid_days = EXCLUDED.paid_days,
             loss_of_pay_days = EXCLUDED.loss_of_pay_days,
             overtime_hours = EXCLUDED.overtime_hours,
             gross_salary = EXCLUDED.gross_salary,
             basic = EXCLUDED.basic,
             hra = EXCLUDED.hra,
             conveyance = EXCLUDED.conveyance,
             special_allowance = EXCLUDED.special_allowance,
             ot_payment = EXCLUDED.ot_payment,
             pf_employee = EXCLUDED.pf_employee,
             professional_tax = EXCLUDED.professional_tax,
             tds = EXCLUDED.tds,
             late_deduction = EXCLUDED.late_deduction,
             absent_deduction = EXCLUDED.absent_deduction,
             half_day_deduction = EXCLUDED.half_day_deduction,
             lop_deduction = EXCLUDED.lop_deduction,
             total_deductions = EXCLUDED.total_deductions,
             net_salary = EXCLUDED.net_salary,
             updated_at = NOW()
           RETURNING id`,
          [
            org_id, empId, departmentId, monthNum, yearInt, payment_type,
            totalDaysInMonth, workingDays,
            presentDays, absentDays, lateDays, halfDays, leaveDays,
            lateMinutes, paidDays, lossOfPayDays, otHours,
            monthlySalary, basic, hra, conveyance, specialAllowance, otPayment,
            pfEmployee, professionalTax, tds,
            lateDeduction, absentDeduction, halfDayDeduction, lossOfPayDeduction,
            totalDeductions, netSalary,
            userId,
          ]
        );

        generated.push({
          employee_id: empId,
          employee_code: employee.employee_code,
          department_id: departmentId,
          department_name: employee.department_name || "Unassigned",
          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("generatePayroll error:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ============================================================
// GET YEARLY SUMMARY
// ============================================================
const getYearlySummary = async (req, res) => {
  try {
    const { year, department_id } = req.query;
    const org_id = req.session.user.orgId;

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

    const yearInt = parseInt(year);

    let query = `
      SELECT 
        pr.month,
        COUNT(DISTINCT pr.employee_id) AS employee_count,
        COUNT(*) FILTER (WHERE pr.payment_status = 'Paid') AS paid_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
      FROM payroll_records pr
      JOIN employees e ON e.id = pr.employee_id AND e.status = 'Active' AND e.is_deleted = false
      WHERE pr.year = $1 AND pr.org_id = $2 AND pr.is_deleted = false
    `;
    const params = [yearInt, org_id];
    let paramIndex = 3;

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

    query += ` GROUP BY pr.month ORDER BY pr.month`;

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

    const monthMap = {};
    result.rows.forEach((row) => {
      monthMap[row.month] = {
        month: row.month,
        month_name: MONTH_NUM_TO_NAME[row.month],
        has_payroll: true,
        total_employees: parseInt(row.employee_count),
        paid_count: parseInt(row.paid_count),
        total_gross: parseFloat(row.total_gross),
        total_deductions: parseFloat(row.total_deductions),
        total_net: parseFloat(row.total_net),
      };
    });

    const allMonths = Array.from({ length: 12 }, (_, i) => {
      const m = i + 1;
      return monthMap[m] || {
        month: m,
        month_name: MONTH_NUM_TO_NAME[m],
        has_payroll: false,
        total_employees: 0,
        paid_count: 0,
        total_gross: 0,
        total_deductions: 0,
        total_net: 0,
      };
    });

    const yearlyTotals = result.rows.reduce(
      (acc, row) => ({
        total_employees: Math.max(acc.total_employees, parseInt(row.employee_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_employees: 0, total_gross: 0, total_deductions: 0, total_net: 0 }
    );

    res.json({ success: true, data: allMonths, totals: yearlyTotals, year: yearInt });
  } catch (err) {
    console.error("getYearlySummary error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// GET EMPLOYEE PAYROLL DETAILS (FIXED)
// ============================================================
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" });
    }

    const yearInt = parseInt(year);

    // ✅ FIXED: Use designation_id instead of designation
    let query = `
      SELECT 
        pr.*,
        e.employee_code,
        TRIM(e.first_name || ' ' || COALESCE(e.last_name, '')) AS employee_name,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.department_id,
        d.dept_name AS department_name
      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
      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, yearInt, 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,
      count: result.rows.length,
      month: monthNum,
      month_name: MONTH_NUM_TO_NAME[monthNum],
      year: yearInt,
    });
  } catch (err) {
    console.error("getEmployeePayrollDetails error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
// ============================================================
// GET PAYROLL BY ID (FIXED)
// ============================================================
const getPayrollById = async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT pr.*,
              e.employee_code,
              TRIM(e.first_name || ' ' || COALESCE(e.last_name, '')) AS employee_name,
              COALESCE(des.designation_name, 'Unknown') AS designation,
              e.department_id,
              d.dept_name AS department_name,
              e.date_of_joining, e.pan_number,
              e.bank_account_number, e.esi_number,
              e.pf_account_number, e.uan_number
       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
       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`,
      [req.params.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("getPayrollById error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// UPDATE PAYROLL
// ============================================================
const updatePayroll = async (req, res) => {
  try {
    const { id } = req.params;
    const { payment_status, paid_date, payment_reference, remarks } = req.body;

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

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }
    
    res.json({ success: true, message: "Payroll updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("updatePayroll error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// UPDATE PAYROLL STATUS
// ============================================================
const updatePayrollStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status, paid_date, payment_method, payment_reference, remarks } = req.body;
    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 = COALESCE($2, paid_date),
           payment_type = COALESCE($3, payment_type),
           payment_reference = COALESCE($4, payment_reference),
           remarks = COALESCE($5, remarks),
           updated_at = NOW()
       WHERE id = $6 AND is_deleted = false
       RETURNING *`,
      [status, paid_date || null, payment_method || null, payment_reference || null, remarks || null, id]
    );

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }
    
    res.json({ success: true, message: `Status updated to ${status}`, data: result.rows[0] });
  } catch (err) {
    console.error("updatePayrollStatus error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// BULK UPDATE STATUS
// ============================================================
const bulkUpdateStatus = async (req, res) => {
  const client = await pool.connect();
  try {
    const { ids, status, paid_date, payment_reference } = req.body;
    const validStatuses = ["Pending", "Generated", "Processing", "Paid", "Cancelled", "Hold"];

    if (!ids?.length) return res.status(400).json({ success: false, message: "ids required" });
    if (!validStatuses.includes(status)) return res.status(400).json({ success: false, message: "Invalid status" });

    await client.query("BEGIN");
    const result = await client.query(
      `UPDATE payroll_records
       SET payment_status = $1,
           paid_date = COALESCE($2, paid_date),
           payment_reference = COALESCE($3, payment_reference),
           updated_at = NOW()
       WHERE id = ANY($4::int[]) AND is_deleted = false
       RETURNING id`,
      [status, paid_date || null, payment_reference || null, ids]
    );
    await client.query("COMMIT");

    res.json({
      success: true,
      message: `${result.rowCount} record(s) updated to ${status}`,
      updated_ids: result.rows.map((r) => r.id),
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("bulkUpdateStatus error:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

// ============================================================
// DELETE PAYROLL
// ============================================================
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 AND is_deleted = false
       RETURNING id`,
      [id]
    );
    
    if (!result.rows.length) {
      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("deletePayroll error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// GET EMPLOYEE PAYROLL HISTORY (FIXED)
// ============================================================
const getEmployeePayrollHistory = async (req, res) => {
  try {
    const { employeeId } = req.params;
    const { year } = req.query;
    const org_id = req.session.user.orgId;

    let query = `
      SELECT pr.*,
             e.employee_code,
             TRIM(e.first_name || ' ' || COALESCE(e.last_name, '')) AS employee_name,
             COALESCE(des.designation_name, 'Unknown') AS designation,
             e.department_id,
             d.dept_name AS department_name
      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
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE pr.org_id = $1 AND pr.is_deleted = false
        AND (e.id = $2 OR e.employee_code = $2::text)
    `;
    const params = [org_id, employeeId];
    
    if (year) {
      params.push(parseInt(year));
      query += ` AND pr.year = $${params.length}`;
    }
    
    query += ` ORDER BY pr.year DESC, pr.month DESC`;

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

// ============================================================
// GET PAYSLIP (FIXED)
// ============================================================
const getPayslip = async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT pr.*,
              e.employee_code,
              TRIM(e.first_name || ' ' || COALESCE(e.last_name, '')) AS employee_name,
              COALESCE(des.designation_name, 'Unknown') AS designation,
              e.department_id,
              d.dept_name AS department_name,
              e.date_of_joining, e.gender, e.pan_number,
              e.bank_account_number, e.esi_number,
              e.pf_account_number, e.uan_number, e.location,
              TO_CHAR(MAKE_DATE(pr.year::INT, pr.month::INT, 1), 'Mon YYYY') AS month_year_label
      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
      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`,
      [req.params.id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Payroll not found" });
    }
    
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("getPayslip error:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ============================================================
// GET MONTHLY ATTENDANCE SUMMARY (fallback)
// ============================================================
const getMonthlyAttendanceSummary = async (req, res) => {
  try {
    const { month, year, department_id } = req.query;
    const org_id = req.session.user.orgId;
    const monthNum = toMonthNum(month);

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

    const yearInt = parseInt(year);

    let query = `
      SELECT 
        COALESCE(d.id, 0) AS department_id,
        COALESCE(d.dept_name, 'Unassigned') AS department_name,
        COUNT(DISTINCT e.id) AS employee_count,
        COUNT(CASE WHEN a.attendance_status IN ('Present', 'Late', 'Overtime') THEN 1 END) AS present_days,
        COUNT(CASE WHEN a.attendance_status = 'Absent' THEN 1 END) AS absent_days,
        COALESCE(SUM(a.overtime_hours), 0) AS total_overtime_hours
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN attendance a ON a.employee_id = e.id 
        AND EXTRACT(YEAR FROM a.attendance_date) = $1
        AND EXTRACT(MONTH FROM a.attendance_date) = $2
        AND a.is_deleted = false
      WHERE e.org_id = $3 
        AND e.status = 'Active' 
        AND e.is_deleted = false
    `;
    
    const params = [yearInt, monthNum, org_id];
    let paramIndex = 4;

    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`;

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

    const data = result.rows.map(row => ({
      department_id: row.department_id,
      department_name: row.department_name,
      employee_count: parseInt(row.employee_count),
      total_gross: 0,
      total_deductions: 0,
      total_net: 0,
      present_days: parseInt(row.present_days) || 0,
      absent_days: parseInt(row.absent_days) || 0,
      total_overtime_hours: parseFloat(row.total_overtime_hours) || 0
    }));

    const totals = data.reduce(
      (acc, r) => ({ 
        ...acc, 
        employee_count: acc.employee_count + r.employee_count 
      }),
      { employee_count: 0, total_gross: 0, total_deductions: 0, total_net: 0 }
    );

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

module.exports = {
  getPayrollGroups,
  getEmployeesForPayroll,
  generatePayroll,
  getYearlySummary,
  getMonthlyPayrollSummary,
  getEmployeePayrollDetails,
  getPayrollById,
  updatePayroll,
  updatePayrollStatus,
  bulkUpdateStatus,
  deletePayroll,
  getEmployeePayrollHistory,
  getPayslip,
  getMonthlyAttendanceSummary,
};