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

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

// const createEmployeeBenefits = async (req, res) => {
//   try {
//     console.log("=== BENEFITS DEBUG ===");
//     console.log("req.body:", req.body);

//     let {
//       employee_id,
//       org_id,
//       // Health & Insurance Benefits
//       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,
//       // Retirement & Pension
//       provident_fund_applicable,
//       pension_scheme_applicable,
//       employee_contribution_percent,
//       employer_contribution_percent,
//       gratuity_eligible,
//       gratuity_start_date,
//       // Leave & Time-Off Benefits
//       leave_policy_assigned,
//       annual_leave_balance,
//       sick_leave_balance,
//       casual_leave_balance,
//       maternity_paternity_leave_eligible,
//       comp_off_eligibility,
//       leave_encashment_applicable,
//       // Allowances
//       housing_allowance_hra,
//       transport_allowance,
//       meal_allowance,
//       travel_allowance,
//       mobile_internet_reimbursement,
//       special_allowance,
//       bonus_eligibility,
//       bonus_percentage_amount,
//       // Benefit Status & Tracking
//       benefits_effective_date,
//       benefits_expiry_date,
//       remarks_notes,
//       created_by,
//       updated_by
//     } = req.body;

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

//     // Verify employee exists
//     const employeeResult = await pool.query(
//       `SELECT employee_id, CONCAT(first_name, ' ', last_name) as employee_name 
//        FROM employees 
//        WHERE employee_id = $1 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,
//         updated_by,
//         created_at,
//         updated_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, $33, $34, $35, $36, $37, $38, $39, $40,$41, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         employee_id,
//         org_id,
//         // Health & Insurance Benefits
//         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,
//         // Retirement & Pension
//         provident_fund_applicable || null,
//         pension_scheme_applicable || null,
//         employee_contribution_percent || null,
//         employer_contribution_percent || null,
//         gratuity_eligible || null,
//         gratuity_start_date || null,
//         // Leave & Time-Off Benefits
//         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,
//         // Allowances
//         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,
//         // Benefit Status & Tracking
//         benefits_effective_date || null,
//         benefits_expiry_date || null,
//         remarks_notes || null,
//         created_by || 'system',
//         updated_by || 'system'
//       ]
//     );

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

// /* ===========================
//    ✅ GET ALL BENEFITS
// =========================== */

// const getAllBenefits = async (req, res) => {
//   try {
//     const { page = 1, limit = 10, org_id, search } = req.query;
//     const offset = (page - 1) * limit;
    
//     let query = `
//       SELECT 
//         eb.*,
//         e.employee_code,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation,
//         e.email
//       FROM employee_benefits eb
//       LEFT JOIN employees e ON eb.employee_id = e.employee_id
//       WHERE eb.is_deleted = false
//     `;
    
//     const queryParams = [];
//     let paramCount = 1;
    
//     if (org_id) {
//       query += ` AND eb.org_id = $${paramCount}`;
//       queryParams.push(org_id);
//       paramCount++;
//     }
    
//     if (search) {
//       query += ` AND (e.first_name ILIKE $${paramCount} OR e.last_name ILIKE $${paramCount} OR e.employee_code ILIKE $${paramCount})`;
//       queryParams.push(`%${search}%`);
//       paramCount++;
//     }
    
//     // Get total count
//     const countQuery = query.replace(
//       /SELECT.*FROM/,
//       'SELECT COUNT(*) as total FROM'
//     );
//     const countResult = await pool.query(countQuery, queryParams);
//     const totalRecords = parseInt(countResult.rows[0].total);
    
//     query += ` ORDER BY eb.created_at DESC LIMIT $${paramCount} OFFSET $${paramCount + 1}`;
//     queryParams.push(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.department,
//         e.designation,
//         e.email,
//         e.phone
//       FROM employee_benefits eb
//       LEFT JOIN employees e ON eb.employee_id = e.employee_id
//       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 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;

//     // Check if record exists
//     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 Benefits
//         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),
//         -- Retirement & Pension
//         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 & Time-Off Benefits
//         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),
//         -- Allowances
//         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),
//         -- Benefit Status & Tracking
//         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,
//         updates.updated_by || 'system',
//         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 (SOFT DELETE)
// =========================== */

// 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.query;
    
//     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,
//   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;

    let {
      employee_id,
      // org_id,
      // Health & Insurance Benefits
      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,
      // Retirement & Pension
      provident_fund_applicable,
      pension_scheme_applicable,
      employee_contribution_percent,
      employer_contribution_percent,
      gratuity_eligible,
      gratuity_start_date,
      // Leave & Time-Off Benefits
      leave_policy_assigned,
      annual_leave_balance,
      sick_leave_balance,
      casual_leave_balance,
      maternity_paternity_leave_eligible,
      comp_off_eligibility,
      leave_encashment_applicable,
      // Allowances
      housing_allowance_hra,
      transport_allowance,
      meal_allowance,
      travel_allowance,
      mobile_internet_reimbursement,
      special_allowance,
      bonus_eligibility,
      bonus_percentage_amount,
      // Benefit Status & Tracking
      benefits_effective_date,
      benefits_expiry_date,
      remarks_notes,
      created_by
    } = req.body;

    // Validation
    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",
      });
    }

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

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

    console.log("Employee verified:", employeeResult.rows[0]);

    // First, check if the table has all columns
    const checkColumns = await pool.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'employee_benefits'
    `);
    
    console.log("Available columns:", checkColumns.rows.map(c => c.column_name));

    // Insert query - make sure column names match exactly
    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,
        created_by || 'system'
      ]
    );

    console.log("Insert successful:", result.rows[0]);

    res.status(201).json({
      success: true,
      message: "Employee benefits created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE BENEFITS ERROR:", error);
    console.error("Error detail:", error.detail);
    console.error("Error message:", error.message);
    
    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 = 10, 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.department,
        e.designation,
        e.email
      FROM employee_benefits eb
      LEFT JOIN employees e ON eb.employee_id = e.employee_id
      WHERE eb.is_deleted = false
    `;
    
    const queryParams = [];
    let paramCount = 1;
    
    if (org_id) {
      query += ` AND eb.org_id = $${paramCount}`;
      queryParams.push(org_id);
      paramCount++;
    }
    
    if (search) {
      query += ` AND (e.first_name ILIKE $${paramCount} OR e.last_name ILIKE $${paramCount} OR e.employee_code ILIKE $${paramCount})`;
      queryParams.push(`%${search}%`);
      paramCount++;
    }
    
    const countQuery = query.replace(
      /SELECT.*FROM/,
      'SELECT COUNT(*) as total FROM'
    );
    const countResult = await pool.query(countQuery, queryParams);
    const totalRecords = parseInt(countResult.rows[0].total);
    
    query += ` ORDER BY eb.created_at DESC LIMIT $${paramCount} OFFSET $${paramCount + 1}`;
    queryParams.push(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.department,
        e.designation,
        e.email,
        e.phone
      FROM employee_benefits eb
      LEFT JOIN employees e ON eb.employee_id = e.employee_id
      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 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 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,
        updates.updated_by || 'system',
        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.query;
    
     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,
  getBenefitsByEmployee,
  updateBenefits,
  deleteBenefits,
  getBenefitsSummary,
};