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

// /* ===========================
//    ✅ CREATE EMPLOYEE BENEFITS
// =========================== */

// const createEmployeeBenefits = async (req, res) => {
//   try {
//     console.log("=== BENEFITS DEBUG ===");
//     console.log("Received body:", req.body);
//     const org_id = req.session.user.orgId;
//     const userId = req.session.user.userId;

//     let {
//       employee_id,
//       health_insurance_applicable,
//       insurance_provider_name,
//       policy_number,
//       coverage_type,
//       coverage_amount,
//       premium_amount,
//       employer_contribution,
//       employee_contribution,
//       insurance_start_date,
//       insurance_end_date,
//       dependent_coverage_details,
//       provident_fund_applicable,
//       pension_scheme_applicable,
//       employee_contribution_percent,
//       employer_contribution_percent,
//       gratuity_eligible,
//       gratuity_start_date,
//       leave_policy_assigned,
//       annual_leave_balance,
//       sick_leave_balance,
//       casual_leave_balance,
//       maternity_paternity_leave_eligible,
//       comp_off_eligibility,
//       leave_encashment_applicable,
//       housing_allowance_hra,
//       transport_allowance,
//       meal_allowance,
//       travel_allowance,
//       mobile_internet_reimbursement,
//       special_allowance,
//       bonus_eligibility,
//       bonus_percentage_amount,
//       benefits_effective_date,
//       benefits_expiry_date,
//       remarks_notes,
//       created_by,
//     } = req.body;

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

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

//     const employeeResult = await pool.query(
//       `SELECT employee_id, CONCAT(first_name, ' ', last_name) as employee_name 
//        FROM employees 
//        WHERE employee_id = $1 AND is_onboarding = true AND benefits_completed = false AND is_deleted = false`,
//       [employee_id]
//     );

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

//     const result = await pool.query(
//       `
//       INSERT INTO employee_benefits (
//         employee_id, org_id,
//         health_insurance_applicable, insurance_provider_name, policy_number,
//         coverage_type, coverage_amount, premium_amount, employer_contribution,
//         employee_contribution, insurance_start_date, insurance_end_date,
//         dependent_coverage_details, provident_fund_applicable,
//         pension_scheme_applicable, employee_contribution_percent,
//         employer_contribution_percent, gratuity_eligible, gratuity_start_date,
//         leave_policy_assigned, annual_leave_balance, sick_leave_balance,
//         casual_leave_balance, maternity_paternity_leave_eligible,
//         comp_off_eligibility, leave_encashment_applicable,
//         housing_allowance_hra, transport_allowance, meal_allowance,
//         travel_allowance, mobile_internet_reimbursement, special_allowance,
//         bonus_eligibility, bonus_percentage_amount,
//         benefits_effective_date, benefits_expiry_date, remarks_notes, created_by
//       )
//       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,$33,$34,$35,$36,$37,$38
//       )
//       RETURNING *
//       `,
//       [
//         employee_id, org_id,
//         health_insurance_applicable || null, insurance_provider_name || null,
//         policy_number || null, coverage_type || null, coverage_amount || null,
//         premium_amount || null, employer_contribution || null,
//         employee_contribution || null, insurance_start_date || null,
//         insurance_end_date || null, dependent_coverage_details || null,
//         provident_fund_applicable || null, pension_scheme_applicable || null,
//         employee_contribution_percent || null, employer_contribution_percent || null,
//         gratuity_eligible || null, gratuity_start_date || null,
//         leave_policy_assigned || null, annual_leave_balance || null,
//         sick_leave_balance || null, casual_leave_balance || null,
//         maternity_paternity_leave_eligible || null, comp_off_eligibility || null,
//         leave_encashment_applicable || null, housing_allowance_hra || null,
//         transport_allowance || null, meal_allowance || null,
//         travel_allowance || null, mobile_internet_reimbursement || null,
//         special_allowance || null, bonus_eligibility || null,
//         bonus_percentage_amount || null, benefits_effective_date || null,
//         benefits_expiry_date || null, remarks_notes || null, userId,
//       ]
//     );

//     await pool.query(
//       `UPDATE employees SET benefits_completed = true, updated_at = NOW() WHERE employee_id = $1`,
//       [employee_id]
//     );

//     res.status(201).json({
//       success: true,
//       message: "Employee benefits created successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("CREATE BENEFITS ERROR:", error);
//     res.status(500).json({ success: false, message: error.message, detail: error.detail || null });
//   }
// };

// /* ===========================
//    ✅ GET ALL BENEFITS
// =========================== */
// const getAllBenefits = async (req, res) => {
//   try {
//     const { page = 1, limit = 200, search } = req.query;
//     const offset = (page - 1) * limit;
//     const org_id = req.session.user.orgId;

//     let query = `
//       SELECT
//         eb.*,
//         e.employee_code,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.joining_date,

//         -- Use ::text casting for department since e.department_id is VARCHAR
//         COALESCE(
//           d1.dept_name,
//           d2.dept_name,
//           e.department_id,
//           e.department
//         ) AS department_name,

//         -- Use ::text casting for designation since e.designation_id is VARCHAR
//         COALESCE(
//           des1.designation_name,
//           des2.designation_name,
//           e.designation_id,
//           e.designation
//         ) AS designation_name,

//         e.email

//       FROM employee_benefits eb

//       LEFT JOIN employees e
//         ON eb.employee_id = e.employee_id

//       -- department via VARCHAR department_id column (cast to text)
//       LEFT JOIN departments d1
//         ON d1.id::text = e.department_id
//         AND d1.is_deleted = false

//       -- department via text "department" column
//       LEFT JOIN departments d2
//         ON d2.id::text = e.department
//         AND d2.is_deleted = false

//       -- designation via VARCHAR designation_id column (cast to text)
//       LEFT JOIN designations des1
//         ON des1.id::text = e.designation_id
//         AND des1.is_deleted = false

//       -- designation via text "designation" column
//       LEFT JOIN designations des2
//         ON des2.id::text = e.designation
//         AND des2.is_deleted = false

//       WHERE eb.is_deleted = false
//         AND eb.org_id = $1::integer
//     `;

//     const queryParams = [org_id];
//     let paramCount = 2;

//     if (search) {
//       query += ` AND (
//         e.first_name  ILIKE $${paramCount}::text OR
//         e.last_name   ILIKE $${paramCount}::text OR
//         e.employee_code ILIKE $${paramCount}::text
//       )`;
//       queryParams.push(`%${search}%`);
//       paramCount++;
//     }

//     // Count query
//     const countQuery = `
//       SELECT COUNT(*) AS total
//       FROM employee_benefits eb
//       LEFT JOIN employees e ON eb.employee_id = e.employee_id
//       WHERE eb.is_deleted = false AND eb.org_id = $1::integer
//       ${search ? `AND (e.first_name ILIKE $2::text OR e.last_name ILIKE $2::text OR e.employee_code ILIKE $2::text)` : ""}
//     `;
//     const countResult = await pool.query(countQuery, queryParams);
//     const totalRecords = parseInt(countResult.rows[0].total);

//     query += ` ORDER BY eb.created_at DESC LIMIT $${paramCount}::integer OFFSET $${paramCount + 1}::integer`;
//     queryParams.push(parseInt(limit), offset);

//     const result = await pool.query(query, queryParams);

//     res.json({
//       success: true,
//       data: result.rows,
//       pagination: {
//         currentPage: parseInt(page),
//         totalPages: Math.ceil(totalRecords / limit),
//         totalRecords,
//         recordsPerPage: parseInt(limit),
//       },
//     });
//   } catch (err) {
//     console.error("FETCH BENEFITS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };


// /* ===========================
//    ✅ GET BENEFITS BY ID
// =========================== */

// const getBenefitsById = async (req, res) => {
//   try {
//     const { id } = req.params;

//     const result = await pool.query(
//       `
//       SELECT
//         eb.*,
//         e.employee_code,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.joining_date,
//         COALESCE(d1.dept_name, d2.dept_name, e.department) AS department_name,
//         COALESCE(des1.designation_name, des2.designation_name, e.designation) AS designation_name,
//         e.email,
//         e.phone
//       FROM employee_benefits eb
//       LEFT JOIN employees e ON eb.employee_id = e.employee_id
//       LEFT JOIN departments  d1  ON d1.id = e.department_id  AND d1.is_deleted  = false
//       LEFT JOIN departments  d2  ON d2.id::text = e.department AND d2.is_deleted = false
//       LEFT JOIN designations des1 ON des1.id = e.designation_id AND des1.is_deleted = false
//       LEFT JOIN designations des2 ON des2.id::text = e.designation AND des2.is_deleted = false
//       WHERE eb.id = $1 AND eb.is_deleted = false
//       `,
//       [id]
//     );

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

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

// /* ===========================
//    ✅ GET EMPLOYEES FOR BENEFITS (pending)
// =========================== */
// const getEmployeesForBenefits = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;

//     if (!org_id) {
//       return res.status(400).json({ success: false, message: "Org ID missing from session" });
//     }

//     const result = await pool.query(
//       `
//       SELECT
//         e.employee_id,
//         e.first_name,
//         e.last_name,
//         e.joining_date,
//         e.department_id,
//         e.designation_id,
//         -- Use ::text casting for joins
//         COALESCE(
//           d1.dept_name,
//           d2.dept_name,
//           e.department_id,
//           e.department
//         ) AS department_name,
//         COALESCE(
//           des1.designation_name,
//           des2.designation_name,
//           e.designation_id,
//           e.designation
//         ) AS designation_name
//       FROM employees e
//       LEFT JOIN departments d1
//         ON d1.id::text = e.department_id
//         AND d1.is_deleted = false
//       LEFT JOIN departments d2
//         ON d2.id::text = e.department
//         AND d2.is_deleted = false
//       LEFT JOIN designations des1
//         ON des1.id::text = e.designation_id
//         AND des1.is_deleted = false
//       LEFT JOIN designations des2
//         ON des2.id::text = e.designation
//         AND des2.is_deleted = false
//       WHERE e.is_onboarding = true
//         AND e.benefits_completed = false
//         AND e.is_deleted = false
//         AND e.org_id = $1::integer
//       ORDER BY e.created_at DESC
//       `,
//       [org_id]
//     );

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("FETCH BENEFITS EMPLOYEES ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ===========================
//    ✅ GET BENEFITS BY EMPLOYEE
// =========================== */

// const getBenefitsByEmployee = async (req, res) => {
//   try {
//     const { employee_id } = req.params;

//     const result = await pool.query(
//       `SELECT * FROM employee_benefits
//        WHERE employee_id = $1 AND is_deleted = false
//        ORDER BY created_at DESC`,
//       [employee_id]
//     );

//     res.status(200).json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET BENEFITS BY EMPLOYEE ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ===========================
//    ✅ UPDATE BENEFITS
// =========================== */

// const updateBenefits = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const updates = req.body;
//     const userId = req.session.user.id;

//     const existing = await pool.query(
//       `SELECT * FROM employee_benefits WHERE id = $1 AND is_deleted = false`,
//       [id]
//     );

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

//     const result = await pool.query(
//       `
//       UPDATE employee_benefits SET
//         health_insurance_applicable        = COALESCE($1,  health_insurance_applicable),
//         insurance_provider_name            = COALESCE($2,  insurance_provider_name),
//         policy_number                      = COALESCE($3,  policy_number),
//         coverage_type                      = COALESCE($4,  coverage_type),
//         coverage_amount                    = COALESCE($5,  coverage_amount),
//         premium_amount                     = COALESCE($6,  premium_amount),
//         employer_contribution              = COALESCE($7,  employer_contribution),
//         employee_contribution              = COALESCE($8,  employee_contribution),
//         insurance_start_date               = COALESCE($9,  insurance_start_date),
//         insurance_end_date                 = COALESCE($10, insurance_end_date),
//         dependent_coverage_details         = COALESCE($11, dependent_coverage_details),
//         provident_fund_applicable          = COALESCE($12, provident_fund_applicable),
//         pension_scheme_applicable          = COALESCE($13, pension_scheme_applicable),
//         employee_contribution_percent      = COALESCE($14, employee_contribution_percent),
//         employer_contribution_percent      = COALESCE($15, employer_contribution_percent),
//         gratuity_eligible                  = COALESCE($16, gratuity_eligible),
//         gratuity_start_date                = COALESCE($17, gratuity_start_date),
//         leave_policy_assigned              = COALESCE($18, leave_policy_assigned),
//         annual_leave_balance               = COALESCE($19, annual_leave_balance),
//         sick_leave_balance                 = COALESCE($20, sick_leave_balance),
//         casual_leave_balance               = COALESCE($21, casual_leave_balance),
//         maternity_paternity_leave_eligible = COALESCE($22, maternity_paternity_leave_eligible),
//         comp_off_eligibility               = COALESCE($23, comp_off_eligibility),
//         leave_encashment_applicable        = COALESCE($24, leave_encashment_applicable),
//         housing_allowance_hra              = COALESCE($25, housing_allowance_hra),
//         transport_allowance                = COALESCE($26, transport_allowance),
//         meal_allowance                     = COALESCE($27, meal_allowance),
//         travel_allowance                   = COALESCE($28, travel_allowance),
//         mobile_internet_reimbursement      = COALESCE($29, mobile_internet_reimbursement),
//         special_allowance                  = COALESCE($30, special_allowance),
//         bonus_eligibility                  = COALESCE($31, bonus_eligibility),
//         bonus_percentage_amount            = COALESCE($32, bonus_percentage_amount),
//         benefits_effective_date            = COALESCE($33, benefits_effective_date),
//         benefits_expiry_date               = COALESCE($34, benefits_expiry_date),
//         remarks_notes                      = COALESCE($35, remarks_notes),
//         updated_by                         = $36,
//         updated_at                         = NOW()
//       WHERE id = $37
//       RETURNING *
//       `,
//       [
//         updates.health_insurance_applicable || null,
//         updates.insurance_provider_name || null,
//         updates.policy_number || null,
//         updates.coverage_type || null,
//         updates.coverage_amount || null,
//         updates.premium_amount || null,
//         updates.employer_contribution || null,
//         updates.employee_contribution || null,
//         updates.insurance_start_date || null,
//         updates.insurance_end_date || null,
//         updates.dependent_coverage_details || null,
//         updates.provident_fund_applicable || null,
//         updates.pension_scheme_applicable || null,
//         updates.employee_contribution_percent || null,
//         updates.employer_contribution_percent || null,
//         updates.gratuity_eligible || null,
//         updates.gratuity_start_date || null,
//         updates.leave_policy_assigned || null,
//         updates.annual_leave_balance || null,
//         updates.sick_leave_balance || null,
//         updates.casual_leave_balance || null,
//         updates.maternity_paternity_leave_eligible || null,
//         updates.comp_off_eligibility || null,
//         updates.leave_encashment_applicable || null,
//         updates.housing_allowance_hra || null,
//         updates.transport_allowance || null,
//         updates.meal_allowance || null,
//         updates.travel_allowance || null,
//         updates.mobile_internet_reimbursement || null,
//         updates.special_allowance || null,
//         updates.bonus_eligibility || null,
//         updates.bonus_percentage_amount || null,
//         updates.benefits_effective_date || null,
//         updates.benefits_expiry_date || null,
//         updates.remarks_notes || null,
//         userId,
//         id,
//       ]
//     );

//     res.status(200).json({ success: true, message: "Benefits updated successfully", data: result.rows[0] });
//   } catch (error) {
//     console.error("UPDATE BENEFITS ERROR:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /* ===========================
//    ✅ DELETE BENEFITS
// =========================== */

// const deleteBenefits = async (req, res) => {
//   try {
//     const { id } = req.params;

//     const existing = await pool.query(
//       `SELECT * FROM employee_benefits WHERE id = $1 AND is_deleted = false`,
//       [id]
//     );

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

//     await pool.query(
//       `UPDATE employee_benefits SET is_deleted = true, updated_at = NOW() WHERE id = $1`,
//       [id]
//     );

//     res.json({ success: true, message: "Benefits record deleted successfully ✅" });
//   } catch (err) {
//     console.error("DELETE BENEFITS ERROR:", err);
//     res.status(500).json({ success: false, message: "Delete failed" });
//   }
// };

// /* ===========================
//    ✅ GET BENEFITS SUMMARY
// =========================== */

// const getBenefitsSummary = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;

//     let query = `
//       SELECT
//         COUNT(*) AS total_benefits,
//         COUNT(DISTINCT employee_id) AS total_employees,
//         COUNT(CASE WHEN health_insurance_applicable = 'Yes' THEN 1 END) AS health_insurance_count,
//         COUNT(CASE WHEN provident_fund_applicable = 'Yes' THEN 1 END) AS pf_count,
//         COUNT(CASE WHEN gratuity_eligible = 'Yes' THEN 1 END) AS gratuity_count,
//         COUNT(CASE WHEN bonus_eligibility = 'Yes' THEN 1 END) AS bonus_count
//       FROM employee_benefits
//       WHERE is_deleted = false
//     `;

//     const queryParams = [];
//     if (org_id) {
//       query += ` AND org_id = $1`;
//       queryParams.push(org_id);
//     }

//     const result = await pool.query(query, queryParams);
//     res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("GET BENEFITS SUMMARY ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// module.exports = {
//   createEmployeeBenefits,
//   getAllBenefits,
//   getBenefitsById,
//   getEmployeesForBenefits,
//   getBenefitsByEmployee,
//   updateBenefits,
//   deleteBenefits,
//   getBenefitsSummary,
// };

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

/* ===========================
   ✅ CREATE EMPLOYEE BENEFITS
=========================== */

const createEmployeeBenefits = async (req, res) => {
  try {
    console.log("=== BENEFITS DEBUG ===");
    console.log("Received body:", req.body);
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    let {
      employee_id,
      health_insurance_applicable,
      insurance_provider_name,
      policy_number,
      coverage_type,
      coverage_amount,
      premium_amount,
      employer_contribution,
      employee_contribution,
      insurance_start_date,
      insurance_end_date,
      dependent_coverage_details,
      provident_fund_applicable,
      pension_scheme_applicable,
      employee_contribution_percent,
      employer_contribution_percent,
      gratuity_eligible,
      gratuity_start_date,
      leave_policy_assigned,
      annual_leave_balance,
      sick_leave_balance,
      casual_leave_balance,
      maternity_paternity_leave_eligible,
      comp_off_eligibility,
      leave_encashment_applicable,
      housing_allowance_hra,
      transport_allowance,
      meal_allowance,
      travel_allowance,
      mobile_internet_reimbursement,
      special_allowance,
      bonus_eligibility,
      bonus_percentage_amount,
      benefits_effective_date,
      benefits_expiry_date,
      remarks_notes,
    } = req.body;

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

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

    const employeeResult = await pool.query(
      `SELECT employee_id, CONCAT(first_name, ' ', last_name) as employee_name 
       FROM employees 
       WHERE employee_id = $1 AND is_onboarding = true AND benefits_completed = false AND is_deleted = false`,
      [employee_id]
    );

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

    const result = await pool.query(
      `
      INSERT INTO employee_benefits (
        employee_id, org_id,
        health_insurance_applicable, insurance_provider_name, policy_number,
        coverage_type, coverage_amount, premium_amount, employer_contribution,
        employee_contribution, insurance_start_date, insurance_end_date,
        dependent_coverage_details, provident_fund_applicable,
        pension_scheme_applicable, employee_contribution_percent,
        employer_contribution_percent, gratuity_eligible, gratuity_start_date,
        leave_policy_assigned, annual_leave_balance, sick_leave_balance,
        casual_leave_balance, maternity_paternity_leave_eligible,
        comp_off_eligibility, leave_encashment_applicable,
        housing_allowance_hra, transport_allowance, meal_allowance,
        travel_allowance, mobile_internet_reimbursement, special_allowance,
        bonus_eligibility, bonus_percentage_amount,
        benefits_effective_date, benefits_expiry_date, remarks_notes, created_by
      )
      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,$33,$34,$35,$36,$37,$38
      )
      RETURNING *
      `,
      [
        employee_id, org_id,
        health_insurance_applicable || null, insurance_provider_name || null,
        policy_number || null, coverage_type || null, coverage_amount || null,
        premium_amount || null, employer_contribution || null,
        employee_contribution || null, insurance_start_date || null,
        insurance_end_date || null, dependent_coverage_details || null,
        provident_fund_applicable || null, pension_scheme_applicable || null,
        employee_contribution_percent || null, employer_contribution_percent || null,
        gratuity_eligible || null, gratuity_start_date || null,
        leave_policy_assigned || null, annual_leave_balance || null,
        sick_leave_balance || null, casual_leave_balance || null,
        maternity_paternity_leave_eligible || null, comp_off_eligibility || null,
        leave_encashment_applicable || null, housing_allowance_hra || null,
        transport_allowance || null, meal_allowance || null,
        travel_allowance || null, mobile_internet_reimbursement || null,
        special_allowance || null, bonus_eligibility || null,
        bonus_percentage_amount || null, benefits_effective_date || null,
        benefits_expiry_date || null, remarks_notes || null, userId,
      ]
    );

    await pool.query(
      `UPDATE employees SET benefits_completed = true, updated_at = NOW() WHERE employee_id = $1`,
      [employee_id]
    );

    res.status(201).json({
      success: true,
      message: "Employee benefits created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE BENEFITS ERROR:", error);
    res.status(500).json({ success: false, message: error.message, detail: error.detail || null });
  }
};

/* ===========================
   ✅ GET ALL BENEFITS
=========================== */
const getAllBenefits = async (req, res) => {
  try {
    const { page = 1, limit = 200, search } = req.query;
    const offset = (page - 1) * limit;
    const org_id = req.session.user.orgId;

    let query = `
      SELECT
        eb.*,
        e.employee_code,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.joining_date,
        e.department_id,
        e.designation_id,

        -- department name from departments table using department_id
        COALESCE(
          d.dept_name,
          e.department_id,
          'Unknown'
        ) AS department_name,

        -- designation name from designations table using designation_id
        COALESCE(
          des.designation_name,
          e.designation_id,
          'Unknown'
        ) AS designation_name,

        e.email

      FROM employee_benefits eb

      LEFT JOIN employees e
        ON eb.employee_id = e.employee_id

      -- Join via department_id column (cast to text since e.department_id is VARCHAR)
      LEFT JOIN departments d
        ON d.id::text = e.department_id
        AND d.is_deleted = false

      -- Join via designation_id column (cast to text since e.designation_id is VARCHAR)
      LEFT JOIN designations des
        ON des.id::text = e.designation_id
        AND des.is_deleted = false

      WHERE eb.is_deleted = false
        AND eb.org_id = $1::integer
    `;

    const queryParams = [org_id];
    let paramCount = 2;

    if (search) {
      query += ` AND (
        e.first_name ILIKE $${paramCount}::text OR
        e.last_name ILIKE $${paramCount}::text OR
        e.employee_code ILIKE $${paramCount}::text
      )`;
      queryParams.push(`%${search}%`);
      paramCount++;
    }

    // Count query
    let countQuery = `
      SELECT COUNT(*) AS total
      FROM employee_benefits eb
      LEFT JOIN employees e ON eb.employee_id = e.employee_id
      WHERE eb.is_deleted = false AND eb.org_id = $1::integer
    `;
    
    if (search) {
      countQuery += ` AND (e.first_name ILIKE $2::text OR e.last_name ILIKE $2::text OR e.employee_code ILIKE $2::text)`;
    }
    
    const countResult = await pool.query(countQuery, queryParams);
    const totalRecords = parseInt(countResult.rows[0].total);

    query += ` ORDER BY eb.created_at DESC LIMIT $${paramCount}::integer OFFSET $${paramCount + 1}::integer`;
    queryParams.push(parseInt(limit), offset);

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

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / limit),
        totalRecords,
        recordsPerPage: parseInt(limit),
      },
    });
  } catch (err) {
    console.error("FETCH BENEFITS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET BENEFITS BY ID
=========================== */
const getBenefitsById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `
      SELECT
        eb.*,
        e.employee_code,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.joining_date,
        e.department_id,
        e.designation_id,
        COALESCE(d.dept_name, e.department_id, 'Unknown') AS department_name,
        COALESCE(des.designation_name, e.designation_id, 'Unknown') AS designation_name,
        e.email,
        e.phone
      FROM employee_benefits eb
      LEFT JOIN employees e ON eb.employee_id = e.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 eb.id = $1 AND eb.is_deleted = false
      `,
      [id]
    );

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

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

/* ===========================
   ✅ GET EMPLOYEES FOR BENEFITS (pending)
=========================== */
const getEmployeesForBenefits = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    if (!org_id) {
      return res.status(400).json({ success: false, message: "Org ID missing from session" });
    }

    const result = await pool.query(
      `
      SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.joining_date,
        e.department_id,
        e.designation_id,
        COALESCE(d.dept_name, e.department_id, 'Unknown') AS department_name,
        COALESCE(des.designation_name, e.designation_id, '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.is_onboarding = true
        AND e.benefits_completed = false
        AND e.is_deleted = false
        AND e.org_id = $1::integer
      ORDER BY e.created_at DESC
      `,
      [org_id]
    );

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

/* ===========================
   ✅ GET BENEFITS BY EMPLOYEE
=========================== */
const getBenefitsByEmployee = async (req, res) => {
  try {
    const { employee_id } = req.params;

    const result = await pool.query(
      `SELECT * FROM employee_benefits
       WHERE employee_id = $1 AND is_deleted = false
       ORDER BY created_at DESC`,
      [employee_id]
    );

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

/* ===========================
   ✅ UPDATE BENEFITS
=========================== */
const updateBenefits = async (req, res) => {
  try {
    const { id } = req.params;
    const updates = req.body;
    const userId = req.session.user.id;

    const existing = await pool.query(
      `SELECT * FROM employee_benefits WHERE id = $1 AND is_deleted = false`,
      [id]
    );

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

    const result = await pool.query(
      `
      UPDATE employee_benefits SET
        health_insurance_applicable        = COALESCE($1, health_insurance_applicable),
        insurance_provider_name            = COALESCE($2, insurance_provider_name),
        policy_number                      = COALESCE($3, policy_number),
        coverage_type                      = COALESCE($4, coverage_type),
        coverage_amount                    = COALESCE($5, coverage_amount),
        premium_amount                     = COALESCE($6, premium_amount),
        employer_contribution              = COALESCE($7, employer_contribution),
        employee_contribution              = COALESCE($8, employee_contribution),
        insurance_start_date               = COALESCE($9, insurance_start_date),
        insurance_end_date                 = COALESCE($10, insurance_end_date),
        dependent_coverage_details         = COALESCE($11, dependent_coverage_details),
        provident_fund_applicable          = COALESCE($12, provident_fund_applicable),
        pension_scheme_applicable          = COALESCE($13, pension_scheme_applicable),
        employee_contribution_percent      = COALESCE($14, employee_contribution_percent),
        employer_contribution_percent      = COALESCE($15, employer_contribution_percent),
        gratuity_eligible                  = COALESCE($16, gratuity_eligible),
        gratuity_start_date                = COALESCE($17, gratuity_start_date),
        leave_policy_assigned              = COALESCE($18, leave_policy_assigned),
        annual_leave_balance               = COALESCE($19, annual_leave_balance),
        sick_leave_balance                 = COALESCE($20, sick_leave_balance),
        casual_leave_balance               = COALESCE($21, casual_leave_balance),
        maternity_paternity_leave_eligible = COALESCE($22, maternity_paternity_leave_eligible),
        comp_off_eligibility               = COALESCE($23, comp_off_eligibility),
        leave_encashment_applicable        = COALESCE($24, leave_encashment_applicable),
        housing_allowance_hra              = COALESCE($25, housing_allowance_hra),
        transport_allowance                = COALESCE($26, transport_allowance),
        meal_allowance                     = COALESCE($27, meal_allowance),
        travel_allowance                   = COALESCE($28, travel_allowance),
        mobile_internet_reimbursement      = COALESCE($29, mobile_internet_reimbursement),
        special_allowance                  = COALESCE($30, special_allowance),
        bonus_eligibility                  = COALESCE($31, bonus_eligibility),
        bonus_percentage_amount            = COALESCE($32, bonus_percentage_amount),
        benefits_effective_date            = COALESCE($33, benefits_effective_date),
        benefits_expiry_date               = COALESCE($34, benefits_expiry_date),
        remarks_notes                      = COALESCE($35, remarks_notes),
        updated_by                         = $36,
        updated_at                         = NOW()
      WHERE id = $37
      RETURNING *
      `,
      [
        updates.health_insurance_applicable || null,
        updates.insurance_provider_name || null,
        updates.policy_number || null,
        updates.coverage_type || null,
        updates.coverage_amount || null,
        updates.premium_amount || null,
        updates.employer_contribution || null,
        updates.employee_contribution || null,
        updates.insurance_start_date || null,
        updates.insurance_end_date || null,
        updates.dependent_coverage_details || null,
        updates.provident_fund_applicable || null,
        updates.pension_scheme_applicable || null,
        updates.employee_contribution_percent || null,
        updates.employer_contribution_percent || null,
        updates.gratuity_eligible || null,
        updates.gratuity_start_date || null,
        updates.leave_policy_assigned || null,
        updates.annual_leave_balance || null,
        updates.sick_leave_balance || null,
        updates.casual_leave_balance || null,
        updates.maternity_paternity_leave_eligible || null,
        updates.comp_off_eligibility || null,
        updates.leave_encashment_applicable || null,
        updates.housing_allowance_hra || null,
        updates.transport_allowance || null,
        updates.meal_allowance || null,
        updates.travel_allowance || null,
        updates.mobile_internet_reimbursement || null,
        updates.special_allowance || null,
        updates.bonus_eligibility || null,
        updates.bonus_percentage_amount || null,
        updates.benefits_effective_date || null,
        updates.benefits_expiry_date || null,
        updates.remarks_notes || null,
        userId,
        id,
      ]
    );

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

/* ===========================
   ✅ DELETE BENEFITS
=========================== */
const deleteBenefits = async (req, res) => {
  try {
    const { id } = req.params;

    const existing = await pool.query(
      `SELECT * FROM employee_benefits WHERE id = $1 AND is_deleted = false`,
      [id]
    );

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

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

    res.json({ success: true, message: "Benefits record deleted successfully ✅" });
  } catch (err) {
    console.error("DELETE BENEFITS ERROR:", err);
    res.status(500).json({ success: false, message: "Delete failed" });
  }
};

/* ===========================
   ✅ GET BENEFITS SUMMARY
=========================== */
const getBenefitsSummary = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    let query = `
      SELECT
        COUNT(*) AS total_benefits,
        COUNT(DISTINCT employee_id) AS total_employees,
        COUNT(CASE WHEN health_insurance_applicable = 'Yes' THEN 1 END) AS health_insurance_count,
        COUNT(CASE WHEN provident_fund_applicable = 'Yes' THEN 1 END) AS pf_count,
        COUNT(CASE WHEN gratuity_eligible = 'Yes' THEN 1 END) AS gratuity_count,
        COUNT(CASE WHEN bonus_eligibility = 'Yes' THEN 1 END) AS bonus_count
      FROM employee_benefits
      WHERE is_deleted = false
    `;

    const queryParams = [];
    if (org_id) {
      query += ` AND org_id = $1::integer`;
      queryParams.push(org_id);
    }

    const result = await pool.query(query, queryParams);
    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET BENEFITS SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  createEmployeeBenefits,
  getAllBenefits,
  getBenefitsById,
  getEmployeesForBenefits,
  getBenefitsByEmployee,
  updateBenefits,
  deleteBenefits,
  getBenefitsSummary,
};