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

// /* ===========================
//    ✅ CREATE EMPLOYEE RELIEVING (WITH FILE UPLOADS)
// =========================== */

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

//     let {
//       employee_id, // ✅ Use employee_id instead of employee_code
//       resignation_date,
//       last_working_day,
//       notice_period_days,
//       notice_period_waived,
//       reason_for_resignation,
//       reporting_manager_approval,
//       hr_approval,
//       legal_approval,
//       final_relieving_approval,
//       reporting_manager_approval_date,
//       hr_approval_date,
//       legal_approval_date,
//       final_approval_date,
//       system_access_revoked,
//       access_revocation_date,
//       email_disabled,
//       id_card_deactivated,
//       company_assets_recovery_status,
//       asset_recovery_date,
//       resignation_letter_received,
//       acceptance_letter_issued,
//       acceptance_letter_date,
//       relieving_letter_issued,
//       relieving_letter_date,
//       exit_documents_signed,
//       nda_confidentiality_reminder,
//       salary_till_relieving_date,
//       notice_pay_adjustment,
//       leave_encashment,
//       bonus_incentives,
//       deductions,
//       final_settlement_amount,
//       fnf_status,
//       settlement_date,
//       insurance_termination_date,
//       pf_status,
//       gratuity_eligibility,
//       gratuity_amount,
//       other_benefits_closure,
//       exit_interview_conducted,
//       exit_interview_date,
//       exit_interview_feedback,
//       rehire_eligibility,
//       blacklist_status,
//       remarks_notes,
//     } = req.body;

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

//     // Fetch real employee details using employee_id
//     const employeeResult = await pool.query(
//       `SELECT employee_id, employee_code, CONCAT(first_name, ' ', last_name) as employee_name 
//        FROM employees 
//        WHERE employee_id = $1 AND status = 'Active' AND is_deleted = false`,
//       [employee_id]
//     );

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

//     const realEmployee = employeeResult.rows[0];
//     const realEmployeeId = realEmployee.employee_id;
//     const employeeName = realEmployee.employee_name;
//     const employeeCode = realEmployee.employee_code;

//     // Check if employee already has an active relieving record
//     const existingRelieving = await pool.query(
//       `SELECT id FROM employee_relieving 
//        WHERE employee_id = $1 AND is_deleted = false 
//        AND final_relieving_approval != 'Rejected'`,
//       [realEmployeeId]
//     );

//     // if (existingRelieving.rows.length > 0) {
//     //   return res.status(400).json({
//     //     success: false,
//     //     message: "Employee already has an active relieving record",
//     //   });
//     // }

//     // Helper function to convert boolean values
//     const toBool = (value) => {
//       if (value === true || value === false) return value;
//       if (value === "true" || value === "Yes") return true;
//       return false;
//     };

//     // ========== FILE HANDLING ==========
//     const resignation_letter_file =
//       req.files?.resignation_letter_file?.[0]?.originalname || null;
//     const acceptance_letter_file =
//       req.files?.acceptance_letter_file?.[0]?.originalname || null;
//     const relieving_letter_file =
//       req.files?.relieving_letter_file?.[0]?.originalname || null;
//     const exit_interview_file =
//       req.files?.exit_interview_file?.[0]?.originalname || null;
//     const settlement_document_file =
//       req.files?.settlement_document_file?.[0]?.originalname || null;

//     const result = await pool.query(
//       `INSERT INTO employee_relieving (
//         employee_id, employee_code, employee_name, org_id,
//         resignation_date, last_working_day, notice_period_days, notice_period_waived,
//         reason_for_resignation,
//         reporting_manager_approval, hr_approval, legal_approval, final_relieving_approval,
//         reporting_manager_approval_date, hr_approval_date, legal_approval_date, final_approval_date,
//         system_access_revoked, access_revocation_date, email_disabled, id_card_deactivated,
//         company_assets_recovery_status, asset_recovery_date,
//         resignation_letter_received, acceptance_letter_issued, acceptance_letter_date,
//         relieving_letter_issued, relieving_letter_date,
//         exit_documents_signed, nda_confidentiality_reminder,
//         salary_till_relieving_date, notice_pay_adjustment, leave_encashment, bonus_incentives,
//         deductions, final_settlement_amount, fnf_status, settlement_date,
//         insurance_termination_date, pf_status, gratuity_eligibility, gratuity_amount,
//         other_benefits_closure,
//         exit_interview_conducted, exit_interview_date, exit_interview_feedback,
//         rehire_eligibility, blacklist_status, remarks_notes,
//         resignation_letter_file, acceptance_letter_file, relieving_letter_file,
//         exit_interview_file, settlement_document_file,
//         created_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,$42,$43,$44,$45,$46,$47,$48,$49,$50,
//         $51,$52,$53,$54,$55,
//         NOW(), NOW()
//       )
//       RETURNING *`,
//       [
//         realEmployeeId,                              // 1
//         employeeCode,                                // 2
//         employeeName,                                // 3
//         org_id,                                      // 4
//         resignation_date || null,                    // 5
//         last_working_day || null,                    // 6
//         notice_period_days || null,                  // 7
//         toBool(notice_period_waived),                // 8
//         reason_for_resignation || null,              // 9
//         reporting_manager_approval || "Pending",     // 10
//         hr_approval || "Pending",                    // 11
//         legal_approval || "Pending",                 // 12
//         final_relieving_approval || "Pending",       // 13
//         reporting_manager_approval_date || null,     // 14
//         hr_approval_date || null,                    // 15
//         legal_approval_date || null,                 // 16
//         final_approval_date || null,                 // 17
//         toBool(system_access_revoked),               // 18
//         access_revocation_date || null,              // 19
//         toBool(email_disabled),                      // 20
//         toBool(id_card_deactivated),                 // 21
//         company_assets_recovery_status || "Pending", // 22
//         asset_recovery_date || null,                 // 23
//         toBool(resignation_letter_received),         // 24
//         toBool(acceptance_letter_issued),            // 25
//         acceptance_letter_date || null,              // 26
//         toBool(relieving_letter_issued),             // 27
//         relieving_letter_date || null,               // 28
//         toBool(exit_documents_signed),               // 29
//         toBool(nda_confidentiality_reminder),        // 30
//         salary_till_relieving_date || null,          // 31
//         notice_pay_adjustment || null,               // 32
//         leave_encashment || null,                    // 33
//         bonus_incentives || null,                    // 34
//         deductions || null,                          // 35
//         final_settlement_amount || null,             // 36
//         fnf_status || null,                          // 37
//         settlement_date || null,                     // 38
//         insurance_termination_date || null,          // 39
//         pf_status || null,                           // 40
//         gratuity_eligibility || null,                // 41
//         gratuity_amount || null,                     // 42
//         other_benefits_closure || null,              // 43
//         toBool(exit_interview_conducted),            // 44
//         exit_interview_date || null,                 // 45
//         exit_interview_feedback || null,             // 46
//         rehire_eligibility || null,                  // 47
//         toBool(blacklist_status),                    // 48
//         remarks_notes || null,                       // 49
//         resignation_letter_file,                     // 50
//         acceptance_letter_file,                      // 51
//         relieving_letter_file,                       // 52
//         exit_interview_file,                         // 53
//         settlement_document_file,                    // 54
//         userId || null,                              // 55
//       ]
//     );

//     await pool.query(
//   `UPDATE employees 
//    SET status = 'Inactive',
//        is_relieving = true,
//        updated_at = NOW()
//    WHERE employee_id = $1`,
//   [realEmployeeId]
// );

//     // // Update employee is_relieving flag
//     // await pool.query(
//     //   `UPDATE employees SET is_relieving = true, updated_at = NOW() WHERE employee_id = $1`,
//     //   [realEmployeeId]
//     // );

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

// /* ===========================
//    ✅ GET ALL RELIEVING RECORDS
// =========================== */

// const getAllRelievingRecords = async (req, res) => {
//   try {
//     const { page = 1, limit = 10, status, search } = req.query;
//     const org_id = req.session.user.orgId;
//     const offset = (page - 1) * limit;

//     const conditions = ["er.is_deleted = false"];
//     const queryParams = [];
//     let paramCount = 1;

//     if (status) {
//       conditions.push(`er.final_relieving_approval = $${paramCount}`);
//       queryParams.push(status);
//       paramCount++;
//     }

//     if (org_id) {
//       conditions.push(`er.org_id = $${paramCount}`);
//       queryParams.push(org_id);
//       paramCount++;
//     }

//     if (search) {
//       conditions.push(
//         `(er.employee_name ILIKE $${paramCount} OR er.employee_code ILIKE $${paramCount} OR er.reason_for_resignation ILIKE $${paramCount})`
//       );
//       queryParams.push(`%${search}%`);
//       paramCount++;
//     }

//     const whereClause = "WHERE " + conditions.join(" AND ");

//     const countResult = await pool.query(
//       `SELECT COUNT(*) as total FROM employee_relieving er ${whereClause}`,
//       queryParams
//     );
//     const totalRecords = parseInt(countResult.rows[0].total);

//     const dataParams = [...queryParams, limit, offset];
//     const result = await pool.query(
//       `SELECT 
//         er.*,
//         e.department,
//         e.designation,
//         e.email,
//         e.phone
//        FROM employee_relieving er
//        LEFT JOIN employees e ON er.employee_id = e.employee_id
//        ${whereClause}
//        ORDER BY er.created_at DESC
//        LIMIT $${paramCount} OFFSET $${paramCount + 1}`,
//       dataParams
//     );

//     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 RELIEVING RECORDS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ===========================
//    ✅ GET RELIEVING BY ID
// =========================== */

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

//     const result = await pool.query(
//       `SELECT 
//         er.*,
//         e.department,
//         e.designation,
//         e.email,
//         e.phone,
//         e.first_name,
//         e.last_name
//        FROM employee_relieving er
//        LEFT JOIN employees e ON er.employee_id = e.employee_id
//        WHERE er.id = $1 AND er.is_deleted = false`,
//       [id]
//     );

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

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

// /* ===========================
//    ✅ GET RELIEVING BY EMPLOYEE
// =========================== */

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

//     const result = await pool.query(
//       `SELECT * FROM employee_relieving
//        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 RELIEVING BY EMPLOYEE ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ===========================
//    ✅ UPDATE RELIEVING RECORD
// =========================== */

// const updateRelievingRecord = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const updates = req.body;
//     const files = req.files;
//     const userId = req.session?.user?.userId || null;

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

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

//     const old = existing.rows[0];

//     // Handle file updates — keep existing if no new file uploaded
//     const resignation_letter_file =
//       files?.resignation_letter_file?.[0]?.originalname ||
//       old.resignation_letter_file;
//     const acceptance_letter_file =
//       files?.acceptance_letter_file?.[0]?.originalname ||
//       old.acceptance_letter_file;
//     const relieving_letter_file =
//       files?.relieving_letter_file?.[0]?.originalname ||
//       old.relieving_letter_file;
//     const exit_interview_file =
//       files?.exit_interview_file?.[0]?.originalname || old.exit_interview_file;
//     const settlement_document_file =
//       files?.settlement_document_file?.[0]?.originalname ||
//       old.settlement_document_file;

//     // Helper to parse Yes/No/bool to boolean or null
//     const parseBool = (val) => {
//       if (val === "Yes" || val === true || val === "true") return true;
//       if (val === "No" || val === false || val === "false") return false;
//       return null;
//     };

//     const result = await pool.query(
//       `UPDATE employee_relieving SET
//         resignation_date               = COALESCE($1,  resignation_date),
//         last_working_day               = COALESCE($2,  last_working_day),
//         notice_period_days             = COALESCE($3,  notice_period_days),
//         notice_period_waived           = COALESCE($4,  notice_period_waived),
//         reason_for_resignation         = COALESCE($5,  reason_for_resignation),
//         reporting_manager_approval     = COALESCE($6,  reporting_manager_approval),
//         hr_approval                    = COALESCE($7,  hr_approval),
//         legal_approval                 = COALESCE($8,  legal_approval),
//         final_relieving_approval       = COALESCE($9,  final_relieving_approval),
//         reporting_manager_approval_date= COALESCE($10, reporting_manager_approval_date),
//         hr_approval_date               = COALESCE($11, hr_approval_date),
//         legal_approval_date            = COALESCE($12, legal_approval_date),
//         final_approval_date            = COALESCE($13, final_approval_date),
//         system_access_revoked          = COALESCE($14, system_access_revoked),
//         access_revocation_date         = COALESCE($15, access_revocation_date),
//         email_disabled                 = COALESCE($16, email_disabled),
//         id_card_deactivated            = COALESCE($17, id_card_deactivated),
//         company_assets_recovery_status = COALESCE($18, company_assets_recovery_status),
//         asset_recovery_date            = COALESCE($19, asset_recovery_date),
//         resignation_letter_received    = COALESCE($20, resignation_letter_received),
//         acceptance_letter_issued       = COALESCE($21, acceptance_letter_issued),
//         acceptance_letter_date         = COALESCE($22, acceptance_letter_date),
//         relieving_letter_issued        = COALESCE($23, relieving_letter_issued),
//         relieving_letter_date          = COALESCE($24, relieving_letter_date),
//         exit_documents_signed          = COALESCE($25, exit_documents_signed),
//         nda_confidentiality_reminder   = COALESCE($26, nda_confidentiality_reminder),
//         salary_till_relieving_date     = COALESCE($27, salary_till_relieving_date),
//         notice_pay_adjustment          = COALESCE($28, notice_pay_adjustment),
//         leave_encashment               = COALESCE($29, leave_encashment),
//         bonus_incentives               = COALESCE($30, bonus_incentives),
//         deductions                     = COALESCE($31, deductions),
//         final_settlement_amount        = COALESCE($32, final_settlement_amount),
//         fnf_status                     = COALESCE($33, fnf_status),
//         settlement_date                = COALESCE($34, settlement_date),
//         insurance_termination_date     = COALESCE($35, insurance_termination_date),
//         pf_status                      = COALESCE($36, pf_status),
//         gratuity_eligibility           = COALESCE($37, gratuity_eligibility),
//         gratuity_amount                = COALESCE($38, gratuity_amount),
//         other_benefits_closure         = COALESCE($39, other_benefits_closure),
//         exit_interview_conducted       = COALESCE($40, exit_interview_conducted),
//         exit_interview_date            = COALESCE($41, exit_interview_date),
//         exit_interview_feedback        = COALESCE($42, exit_interview_feedback),
//         rehire_eligibility             = COALESCE($43, rehire_eligibility),
//         blacklist_status               = COALESCE($44, blacklist_status),
//         remarks_notes                  = COALESCE($45, remarks_notes),
//         resignation_letter_file        = $46,
//         acceptance_letter_file         = $47,
//         relieving_letter_file          = $48,
//         exit_interview_file            = $49,
//         settlement_document_file       = $50,
//         updated_by                     = $51,
//         updated_at                     = NOW()
//       WHERE id = $52 AND is_deleted = false
//       RETURNING *`,
//       [
//         updates.resignation_date || null,
//         updates.last_working_day || null,
//         updates.notice_period_days || null,
//         parseBool(updates.notice_period_waived),
//         updates.reason_for_resignation || null,
//         updates.reporting_manager_approval || null,
//         updates.hr_approval || null,
//         updates.legal_approval || null,
//         updates.final_relieving_approval || null,
//         updates.reporting_manager_approval_date || null,
//         updates.hr_approval_date || null,
//         updates.legal_approval_date || null,
//         updates.final_approval_date || null,
//         parseBool(updates.system_access_revoked),
//         updates.access_revocation_date || null,
//         parseBool(updates.email_disabled),
//         parseBool(updates.id_card_deactivated),
//         updates.company_assets_recovery_status || null,
//         updates.asset_recovery_date || null,
//         parseBool(updates.resignation_letter_received),
//         parseBool(updates.acceptance_letter_issued),
//         updates.acceptance_letter_date || null,
//         parseBool(updates.relieving_letter_issued),
//         updates.relieving_letter_date || null,
//         parseBool(updates.exit_documents_signed),
//         parseBool(updates.nda_confidentiality_reminder),
//         updates.salary_till_relieving_date || null,
//         updates.notice_pay_adjustment || null,
//         updates.leave_encashment || null,
//         updates.bonus_incentives || null,
//         updates.deductions || null,
//         updates.final_settlement_amount || null,
//         updates.fnf_status || null,
//         updates.settlement_date || null,
//         updates.insurance_termination_date || null,
//         updates.pf_status || null,
//         updates.gratuity_eligibility || null,
//         updates.gratuity_amount || null,
//         updates.other_benefits_closure || null,
//         parseBool(updates.exit_interview_conducted),
//         updates.exit_interview_date || null,
//         updates.exit_interview_feedback || null,
//         updates.rehire_eligibility || null,
//         parseBool(updates.blacklist_status),
//         updates.remarks_notes || null,
//         resignation_letter_file,
//         acceptance_letter_file,
//         relieving_letter_file,
//         exit_interview_file,
//         settlement_document_file,
//         userId,
//         id,
//       ]
//     );

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

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

// /* ===========================
//    ✅ UPDATE APPROVAL STATUS
// =========================== */

// const updateApprovalStatus = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { approval_type, status } = req.body;
//     const userId = req.session?.user?.userId || null;

//     const validApprovalTypes = ["reporting_manager", "hr", "legal", "final"];
//     const validStatuses = ["Pending", "Approved", "Rejected"];

//     if (!validApprovalTypes.includes(approval_type)) {
//       return res
//         .status(400)
//         .json({ success: false, message: "Invalid approval type" });
//     }

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

//     const columnMap = {
//       reporting_manager: {
//         col: "reporting_manager_approval",
//         date: "reporting_manager_approval_date",
//       },
//       hr: { col: "hr_approval", date: "hr_approval_date" },
//       legal: { col: "legal_approval", date: "legal_approval_date" },
//       final: { col: "final_relieving_approval", date: "final_approval_date" },
//     };

//     const { col, date } = columnMap[approval_type];

//     const result = await pool.query(
//       `UPDATE employee_relieving SET
//         ${col} = $1,
//         ${date} = CASE WHEN $1 = 'Approved' THEN NOW() ELSE NULL END,
//         updated_by = $2,
//         updated_at = NOW()
//        WHERE id = $3 AND is_deleted = false
//        RETURNING *`,
//       [status, userId, id]
//     );

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

//     res.status(200).json({
//       success: true,
//       message: `${approval_type} approval updated to ${status}`,
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("UPDATE APPROVAL ERROR:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

// /* ===========================
//    ✅ DELETE RELIEVING RECORD (SOFT DELETE)
// =========================== */

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

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

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

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

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

// /* ===========================
//    ✅ GET RELIEVING SUMMARY
// =========================== */

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

//     const result = await pool.query(
//       `SELECT 
//         COUNT(*) as total,
//         COUNT(CASE WHEN final_relieving_approval = 'Pending'  THEN 1 END) as pending,
//         COUNT(CASE WHEN final_relieving_approval = 'Approved' THEN 1 END) as approved,
//         COUNT(CASE WHEN final_relieving_approval = 'Rejected' THEN 1 END) as rejected,
//         COUNT(CASE WHEN DATE_TRUNC('month', resignation_date) = DATE_TRUNC('month', CURRENT_DATE) THEN 1 END) as this_month
//        FROM employee_relieving
//        WHERE is_deleted = false AND org_id = $1`,
//       [org_id]
//     );

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

// module.exports = {
//   createEmployeeRelieving,
//   getAllRelievingRecords,
//   getRelievingById,
//   getRelievingByEmployee,
//   updateRelievingRecord,
//   updateApprovalStatus,
//   deleteRelievingRecord,
//   getRelievingSummary,
// };

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

/* ===========================
   ✅ CREATE EMPLOYEE RELIEVING (WITH FILE UPLOADS)
=========================== */

const createEmployeeRelieving = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    let {
      employee_id,
      resignation_date,
      last_working_day,
      notice_period_days,
      notice_period_waived,
      reason_for_resignation,
      reporting_manager_approval,
      hr_approval,
      legal_approval,
      final_relieving_approval,
      reporting_manager_approval_date,
      hr_approval_date,
      legal_approval_date,
      final_approval_date,
      system_access_revoked,
      access_revocation_date,
      email_disabled,
      id_card_deactivated,
      company_assets_recovery_status,
      asset_recovery_date,
      resignation_letter_received,
      acceptance_letter_issued,
      acceptance_letter_date,
      relieving_letter_issued,
      relieving_letter_date,
      exit_documents_signed,
      nda_confidentiality_reminder,
      salary_till_relieving_date,
      notice_pay_adjustment,
      leave_encashment,
      bonus_incentives,
      deductions,
      final_settlement_amount,
      fnf_status,
      settlement_date,
      insurance_termination_date,
      pf_status,
      gratuity_eligibility,
      gratuity_amount,
      other_benefits_closure,
      exit_interview_conducted,
      exit_interview_date,
      exit_interview_feedback,
      rehire_eligibility,
      blacklist_status,
      remarks_notes,
    } = req.body;

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

    // Fetch real employee details using employee_id
    const employeeResult = await pool.query(
      `SELECT employee_id, employee_code, CONCAT(first_name, ' ', last_name) as employee_name 
       FROM employees 
       WHERE employee_id = $1 AND status = 'Active' AND is_deleted = false`,
      [employee_id]
    );

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

    const realEmployee = employeeResult.rows[0];
    const realEmployeeId = realEmployee.employee_id;
    const employeeName = realEmployee.employee_name;
    const employeeCode = realEmployee.employee_code;

    // Helper function to convert boolean values
    const toBool = (value) => {
      if (value === true || value === false) return value;
      if (value === "true" || value === "Yes") return true;
      return false;
    };

    // ========== FILE HANDLING ==========
    const resignation_letter_file =
      req.files?.resignation_letter_file?.[0]?.originalname || null;
    const acceptance_letter_file =
      req.files?.acceptance_letter_file?.[0]?.originalname || null;
    const relieving_letter_file =
      req.files?.relieving_letter_file?.[0]?.originalname || null;
    const exit_interview_file =
      req.files?.exit_interview_file?.[0]?.originalname || null;
    const settlement_document_file =
      req.files?.settlement_document_file?.[0]?.originalname || null;

    const result = await pool.query(
      `INSERT INTO employee_relieving (
        employee_id, employee_code, employee_name, org_id,
        resignation_date, last_working_day, notice_period_days, notice_period_waived,
        reason_for_resignation,
        reporting_manager_approval, hr_approval, legal_approval, final_relieving_approval,
        reporting_manager_approval_date, hr_approval_date, legal_approval_date, final_approval_date,
        system_access_revoked, access_revocation_date, email_disabled, id_card_deactivated,
        company_assets_recovery_status, asset_recovery_date,
        resignation_letter_received, acceptance_letter_issued, acceptance_letter_date,
        relieving_letter_issued, relieving_letter_date,
        exit_documents_signed, nda_confidentiality_reminder,
        salary_till_relieving_date, notice_pay_adjustment, leave_encashment, bonus_incentives,
        deductions, final_settlement_amount, fnf_status, settlement_date,
        insurance_termination_date, pf_status, gratuity_eligibility, gratuity_amount,
        other_benefits_closure,
        exit_interview_conducted, exit_interview_date, exit_interview_feedback,
        rehire_eligibility, blacklist_status, remarks_notes,
        resignation_letter_file, acceptance_letter_file, relieving_letter_file,
        exit_interview_file, settlement_document_file,
        created_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,$42,$43,$44,$45,$46,$47,$48,$49,$50,
        $51,$52,$53,$54,$55,
        NOW(), NOW()
      )
      RETURNING *`,
      [
        realEmployeeId,
        employeeCode,
        employeeName,
        org_id,
        resignation_date || null,
        last_working_day || null,
        notice_period_days || null,
        toBool(notice_period_waived),
        reason_for_resignation || null,
        reporting_manager_approval || "Pending",
        hr_approval || "Pending",
        legal_approval || "Pending",
        final_relieving_approval || "Pending",
        reporting_manager_approval_date || null,
        hr_approval_date || null,
        legal_approval_date || null,
        final_approval_date || null,
        toBool(system_access_revoked),
        access_revocation_date || null,
        toBool(email_disabled),
        toBool(id_card_deactivated),
        company_assets_recovery_status || "Pending",
        asset_recovery_date || null,
        toBool(resignation_letter_received),
        toBool(acceptance_letter_issued),
        acceptance_letter_date || null,
        toBool(relieving_letter_issued),
        relieving_letter_date || null,
        toBool(exit_documents_signed),
        toBool(nda_confidentiality_reminder),
        salary_till_relieving_date || null,
        notice_pay_adjustment || null,
        leave_encashment || null,
        bonus_incentives || null,
        deductions || null,
        final_settlement_amount || null,
        fnf_status || null,
        settlement_date || null,
        insurance_termination_date || null,
        pf_status || null,
        gratuity_eligibility || null,
        gratuity_amount || null,
        other_benefits_closure || null,
        toBool(exit_interview_conducted),
        exit_interview_date || null,
        exit_interview_feedback || null,
        rehire_eligibility || null,
        toBool(blacklist_status),
        remarks_notes || null,
        resignation_letter_file,
        acceptance_letter_file,
        relieving_letter_file,
        exit_interview_file,
        settlement_document_file,
        userId || null,
      ]
    );

    await pool.query(
      `UPDATE employees 
       SET status = 'Inactive',
           is_relieving = true,
           updated_at = NOW()
       WHERE employee_id = $1`,
      [realEmployeeId]
    );

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

/* ===========================
   ✅ GET ALL RELIEVING RECORDS (FIXED)
=========================== */

const getAllRelievingRecords = async (req, res) => {
  try {
    const { page = 1, limit = 10, status, search } = req.query;
    const org_id = req.session.user.orgId;
    const offset = (page - 1) * limit;

    const conditions = ["er.is_deleted = false"];
    const queryParams = [];
    let paramCount = 1;

    if (status) {
      conditions.push(`er.final_relieving_approval = $${paramCount}`);
      queryParams.push(status);
      paramCount++;
    }

    if (org_id) {
      conditions.push(`er.org_id = $${paramCount}`);
      queryParams.push(org_id);
      paramCount++;
    }

    if (search) {
      conditions.push(
        `(er.employee_name ILIKE $${paramCount} OR er.employee_code ILIKE $${paramCount} OR er.reason_for_resignation ILIKE $${paramCount})`
      );
      queryParams.push(`%${search}%`);
      paramCount++;
    }

    const whereClause = "WHERE " + conditions.join(" AND ");

    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM employee_relieving er ${whereClause}`,
      queryParams
    );
    const totalRecords = parseInt(countResult.rows[0].total);

    const dataParams = [...queryParams, limit, offset];
    
    // ✅ FIXED: Use department_id and designation_id with joins
    const result = await pool.query(
      `SELECT 
        er.*,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email,
        e.phone,
        e.first_name,
        e.last_name
       FROM employee_relieving er
       LEFT JOIN employees e ON er.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
       ${whereClause}
       ORDER BY er.created_at DESC
       LIMIT $${paramCount} OFFSET $${paramCount + 1}`,
      dataParams
    );

    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 RELIEVING RECORDS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET RELIEVING BY ID (FIXED)
=========================== */

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

    const result = await pool.query(
      `SELECT 
        er.*,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email,
        e.phone,
        e.first_name,
        e.last_name
       FROM employee_relieving er
       LEFT JOIN employees e ON er.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 er.id = $1 AND er.is_deleted = false`,
      [id]
    );

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

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

/* ===========================
   ✅ GET RELIEVING BY EMPLOYEE
=========================== */

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

    const result = await pool.query(
      `SELECT * FROM employee_relieving
       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 RELIEVING BY EMPLOYEE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ UPDATE RELIEVING RECORD
=========================== */

const updateRelievingRecord = async (req, res) => {
  try {
    const { id } = req.params;
    const updates = req.body;
    const files = req.files;
   
    // ✅ FIXED: Use 'id' property
    const userId = req.session?.user?.id || null;

    // Debug logging
    console.log("=== UPDATE RELIEVING DEBUG ===");
    console.log("Record ID:", id);
    console.log("User ID from session:", userId);
    console.log("Session user:", req.session?.user);

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

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

    const old = existing.rows[0];

    // Handle file updates
    const resignation_letter_file =
      files?.resignation_letter_file?.[0]?.originalname ||
      old.resignation_letter_file;
    const acceptance_letter_file =
      files?.acceptance_letter_file?.[0]?.originalname ||
      old.acceptance_letter_file;
    const relieving_letter_file =
      files?.relieving_letter_file?.[0]?.originalname ||
      old.relieving_letter_file;
    const exit_interview_file =
      files?.exit_interview_file?.[0]?.originalname || old.exit_interview_file;
    const settlement_document_file =
      files?.settlement_document_file?.[0]?.originalname ||
      old.settlement_document_file;

    // Helper to parse Yes/No/bool to boolean or null
    const parseBool = (val) => {
      if (val === "Yes" || val === true || val === "true") return true;
      if (val === "No" || val === false || val === "false") return false;
      return null;
    };

    const result = await pool.query(
      `UPDATE employee_relieving SET
        resignation_date               = COALESCE($1,  resignation_date),
        last_working_day               = COALESCE($2,  last_working_day),
        notice_period_days             = COALESCE($3,  notice_period_days),
        notice_period_waived           = COALESCE($4,  notice_period_waived),
        reason_for_resignation         = COALESCE($5,  reason_for_resignation),
        reporting_manager_approval     = COALESCE($6,  reporting_manager_approval),
        hr_approval                    = COALESCE($7,  hr_approval),
        legal_approval                 = COALESCE($8,  legal_approval),
        final_relieving_approval       = COALESCE($9,  final_relieving_approval),
        reporting_manager_approval_date= COALESCE($10, reporting_manager_approval_date),
        hr_approval_date               = COALESCE($11, hr_approval_date),
        legal_approval_date            = COALESCE($12, legal_approval_date),
        final_approval_date            = COALESCE($13, final_approval_date),
        system_access_revoked          = COALESCE($14, system_access_revoked),
        access_revocation_date         = COALESCE($15, access_revocation_date),
        email_disabled                 = COALESCE($16, email_disabled),
        id_card_deactivated            = COALESCE($17, id_card_deactivated),
        company_assets_recovery_status = COALESCE($18, company_assets_recovery_status),
        asset_recovery_date            = COALESCE($19, asset_recovery_date),
        resignation_letter_received    = COALESCE($20, resignation_letter_received),
        acceptance_letter_issued       = COALESCE($21, acceptance_letter_issued),
        acceptance_letter_date         = COALESCE($22, acceptance_letter_date),
        relieving_letter_issued        = COALESCE($23, relieving_letter_issued),
        relieving_letter_date          = COALESCE($24, relieving_letter_date),
        exit_documents_signed          = COALESCE($25, exit_documents_signed),
        nda_confidentiality_reminder   = COALESCE($26, nda_confidentiality_reminder),
        salary_till_relieving_date     = COALESCE($27, salary_till_relieving_date),
        notice_pay_adjustment          = COALESCE($28, notice_pay_adjustment),
        leave_encashment               = COALESCE($29, leave_encashment),
        bonus_incentives               = COALESCE($30, bonus_incentives),
        deductions                     = COALESCE($31, deductions),
        final_settlement_amount        = COALESCE($32, final_settlement_amount),
        fnf_status                     = COALESCE($33, fnf_status),
        settlement_date                = COALESCE($34, settlement_date),
        insurance_termination_date     = COALESCE($35, insurance_termination_date),
        pf_status                      = COALESCE($36, pf_status),
        gratuity_eligibility           = COALESCE($37, gratuity_eligibility),
        gratuity_amount                = COALESCE($38, gratuity_amount),
        other_benefits_closure         = COALESCE($39, other_benefits_closure),
        exit_interview_conducted       = COALESCE($40, exit_interview_conducted),
        exit_interview_date            = COALESCE($41, exit_interview_date),
        exit_interview_feedback        = COALESCE($42, exit_interview_feedback),
        rehire_eligibility             = COALESCE($43, rehire_eligibility),
        blacklist_status               = COALESCE($44, blacklist_status),
        remarks_notes                  = COALESCE($45, remarks_notes),
        resignation_letter_file        = $46,
        acceptance_letter_file         = $47,
        relieving_letter_file          = $48,
        exit_interview_file            = $49,
        settlement_document_file       = $50,
        updated_by                     = $51,
        updated_at                     = NOW()
      WHERE id = $52 AND is_deleted = false
      RETURNING *`,
      [
        updates.resignation_date || null,
        updates.last_working_day || null,
        updates.notice_period_days || null,
        parseBool(updates.notice_period_waived),
        updates.reason_for_resignation || null,
        updates.reporting_manager_approval || null,
        updates.hr_approval || null,
        updates.legal_approval || null,
        updates.final_relieving_approval || null,
        updates.reporting_manager_approval_date || null,
        updates.hr_approval_date || null,
        updates.legal_approval_date || null,
        updates.final_approval_date || null,
        parseBool(updates.system_access_revoked),
        updates.access_revocation_date || null,
        parseBool(updates.email_disabled),
        parseBool(updates.id_card_deactivated),
        updates.company_assets_recovery_status || null,
        updates.asset_recovery_date || null,
        parseBool(updates.resignation_letter_received),
        parseBool(updates.acceptance_letter_issued),
        updates.acceptance_letter_date || null,
        parseBool(updates.relieving_letter_issued),
        updates.relieving_letter_date || null,
        parseBool(updates.exit_documents_signed),
        parseBool(updates.nda_confidentiality_reminder),
        updates.salary_till_relieving_date || null,
        updates.notice_pay_adjustment || null,
        updates.leave_encashment || null,
        updates.bonus_incentives || null,
        updates.deductions || null,
        updates.final_settlement_amount || null,
        updates.fnf_status || null,
        updates.settlement_date || null,
        updates.insurance_termination_date || null,
        updates.pf_status || null,
        updates.gratuity_eligibility || null,
        updates.gratuity_amount || null,
        updates.other_benefits_closure || null,
        parseBool(updates.exit_interview_conducted),
        updates.exit_interview_date || null,
        updates.exit_interview_feedback || null,
        updates.rehire_eligibility || null,
        parseBool(updates.blacklist_status),
        updates.remarks_notes || null,
        resignation_letter_file,
        acceptance_letter_file,
        relieving_letter_file,
        exit_interview_file,
        settlement_document_file,
        userId,
        id,
      ]
    );

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

    res.status(200).json({
      success: true,
      message: "Relieving record updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE RELIEVING ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
      detail: error.detail || null,
    });
  }
};

/* ===========================
   ✅ UPDATE APPROVAL STATUS
=========================== */

const updateApprovalStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { approval_type, status } = req.body;
    const userId = req.session?.user?.id || null;

    const validApprovalTypes = ["reporting_manager", "hr", "legal", "final"];
    const validStatuses = ["Pending", "Approved", "Rejected"];

    if (!validApprovalTypes.includes(approval_type)) {
      return res
        .status(400)
        .json({ success: false, message: "Invalid approval type" });
    }

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

    const columnMap = {
      reporting_manager: {
        col: "reporting_manager_approval",
        date: "reporting_manager_approval_date",
      },
      hr: { col: "hr_approval", date: "hr_approval_date" },
      legal: { col: "legal_approval", date: "legal_approval_date" },
      final: { col: "final_relieving_approval", date: "final_approval_date" },
    };

    const { col, date } = columnMap[approval_type];

    const result = await pool.query(
      `UPDATE employee_relieving SET
        ${col} = $1,
        ${date} = CASE WHEN $1 = 'Approved' THEN NOW() ELSE NULL END,
        updated_by = $2,
        updated_at = NOW()
       WHERE id = $3 AND is_deleted = false
       RETURNING *`,
      [status, userId, id]
    );

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

    res.status(200).json({
      success: true,
      message: `${approval_type} approval updated to ${status}`,
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE APPROVAL ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ DELETE RELIEVING RECORD (SOFT DELETE)
=========================== */

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

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

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

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

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

/* ===========================
   ✅ GET RELIEVING SUMMARY
=========================== */

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

    const result = await pool.query(
      `SELECT 
        COUNT(*) as total,
        COUNT(CASE WHEN final_relieving_approval = 'Pending'  THEN 1 END) as pending,
        COUNT(CASE WHEN final_relieving_approval = 'Approved' THEN 1 END) as approved,
        COUNT(CASE WHEN final_relieving_approval = 'Rejected' THEN 1 END) as rejected,
        COUNT(CASE WHEN DATE_TRUNC('month', resignation_date) = DATE_TRUNC('month', CURRENT_DATE) THEN 1 END) as this_month
       FROM employee_relieving
       WHERE is_deleted = false AND org_id = $1`,
      [org_id]
    );

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

module.exports = {
  createEmployeeRelieving,
  getAllRelievingRecords,
  getRelievingById,
  getRelievingByEmployee,
  updateRelievingRecord,
  updateApprovalStatus,
  deleteRelievingRecord,
  getRelievingSummary,
};