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




// /* ===========================
//    ✅ CREATE EMPLOYEE TERMINATION
// =========================== */

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

//     let {
//       employee_code,
//       org_id,
//       termination_initiated_by,
//       termination_date,
//       last_working_day,
//       termination_type,
//       immediate_termination,
//       notice_period_applicable,
//       notice_period_waived,
//       primary_reason,
//       detailed_reason,
//       supporting_documents_attached,
//       disciplinary_action_history,
//       investigation_conducted,
//       investigation_start_date,
//       investigation_end_date,
//       show_cause_notice_issued,
//       employee_response_received,
//       legal_compliance_check,
//       reporting_manager_approval,
//       hr_approval,
//       legal_approval,
//       final_termination_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,
//       termination_letter_issued,
//       termination_letter_date,
//       exit_documents_signed,
//       nda_confidentiality_reminder,
//       salary_till_termination_date,
//       notice_pay_recovery_payment,
//       leave_encashment,
//       bonus_incentives,
//       deductions,
//       final_settlement_amount,
//       fnf_status,
//       settlement_date,
//       insurance_termination_date,
//       pf_status,
//       gratuity_eligibility,
//       gratuity_amount,
//       other_benefits_closure,
//       rehire_eligibility,
//       blacklist_status,
//       remarks_notes,
//       created_by,
//       updated_by
//     } = req.body;

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

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

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

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

//     if (existingTermination.rows.length > 0) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee already has an active termination 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;
//     };

//     const result = await pool.query(
//       `
//       INSERT INTO employee_termination (
//         employee_id, employee_code, employee_name,
//         org_id,
//         termination_initiated_by, termination_date, last_working_day, termination_type,
//         immediate_termination, notice_period_applicable, notice_period_waived,
//         primary_reason, detailed_reason,
//         supporting_documents_attached, disciplinary_action_history,
//         investigation_conducted, investigation_start_date, investigation_end_date,
//         show_cause_notice_issued, employee_response_received, legal_compliance_check,
//         reporting_manager_approval, hr_approval, legal_approval, final_termination_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,
//         termination_letter_issued, termination_letter_date,
//         exit_documents_signed, nda_confidentiality_reminder,
//         salary_till_termination_date, notice_pay_recovery_payment, leave_encashment,
//         bonus_incentives, deductions, final_settlement_amount, fnf_status, settlement_date,
//         insurance_termination_date, pf_status, gratuity_eligibility, gratuity_amount,
//         other_benefits_closure, rehire_eligibility, blacklist_status, 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, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56,$57, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         realEmployeeId,                           // 1
//         employee_code,                            // 2
//         employeeName,                             // 3
//         org_id,                                   // 4
//         termination_initiated_by || null,         // 5
//         termination_date || null,                 // 6
//         last_working_day || null,                 // 7
//         termination_type || null,                 // 8
//         toBool(immediate_termination),            // 9
//         toBool(notice_period_applicable),         // 10
//         toBool(notice_period_waived),             // 11
//         primary_reason || null,                   // 12
//         detailed_reason || null,                  // 13
//         toBool(supporting_documents_attached),    // 14
//         toBool(disciplinary_action_history),      // 15
//         toBool(investigation_conducted),          // 16
//         investigation_start_date || null,         // 17
//         investigation_end_date || null,           // 18
//         toBool(show_cause_notice_issued),         // 19
//         toBool(employee_response_received),       // 20
//         toBool(legal_compliance_check),           // 21
//         reporting_manager_approval || 'Pending',  // 22
//         hr_approval || 'Pending',                 // 23
//         legal_approval || 'Pending',              // 24
//         final_termination_approval || 'Pending',  // 25
//         reporting_manager_approval_date || null,  // 26
//         hr_approval_date || null,                 // 27
//         legal_approval_date || null,              // 28
//         final_approval_date || null,              // 29
//         toBool(system_access_revoked),            // 30
//         access_revocation_date || null,           // 31
//         toBool(email_disabled),                   // 32
//         toBool(id_card_deactivated),              // 33
//         company_assets_recovery_status || 'Pending', // 34
//         asset_recovery_date || null,              // 35
//         toBool(termination_letter_issued),        // 36
//         termination_letter_date || null,          // 37
//         toBool(exit_documents_signed),            // 38
//         toBool(nda_confidentiality_reminder),     // 39
//         salary_till_termination_date || null,     // 40
//         notice_pay_recovery_payment || null,      // 41
//         leave_encashment || null,                 // 42
//         bonus_incentives || null,                 // 43
//         deductions || null,                       // 44
//         final_settlement_amount || null,          // 45
//         fnf_status || null,                       // 46
//         settlement_date || null,                  // 47
//         insurance_termination_date || null,       // 48
//         pf_status || null,                        // 49
//         gratuity_eligibility || null,             // 50
//         gratuity_amount || null,                  // 51
//         other_benefits_closure || null,           // 52
//         rehire_eligibility || null,               // 53
//         toBool(blacklist_status),                 // 54
//         remarks_notes || null,                    // 55
//         created_by || 'system' ,                   // 56
//         updated_by || 'system' 
//       ]
//     );

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

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

const createEmployeeTermination = async (req, res) => {
  try {
    console.log("=== TERMINATION DEBUG ===");
    console.log("req.body:", req.body);
    console.log("req.files:", req.files);
    const org_id = req.session.user.orgId;

    let {
      employee_code,
      // org_id,
      termination_initiated_by,
      termination_date,
      last_working_day,
      termination_type,
      immediate_termination,
      notice_period_applicable,
      notice_period_waived,
      primary_reason,
      detailed_reason,
      supporting_documents_attached,
      disciplinary_action_history,
      investigation_conducted,
      investigation_start_date,
      investigation_end_date,
      show_cause_notice_issued,
      employee_response_received,
      legal_compliance_check,
      reporting_manager_approval,
      hr_approval,
      legal_approval,
      final_termination_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,
      termination_letter_issued,
      termination_letter_date,
      exit_documents_signed,
      nda_confidentiality_reminder,
      salary_till_termination_date,
      notice_pay_recovery_payment,
      leave_encashment,
      bonus_incentives,
      deductions,
      final_settlement_amount,
      fnf_status,
      settlement_date,
      insurance_termination_date,
      pf_status,
      gratuity_eligibility,
      gratuity_amount,
      other_benefits_closure,
      rehire_eligibility,
      blacklist_status,
      remarks_notes,
      created_by,
      
    } = req.body;

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

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

    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;

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

    if (existingTermination.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "Employee already has an active termination 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 termination_letter_file = req.files?.termination_letter_file?.[0]?.originalname || null;
    const investigation_report_file = req.files?.investigation_report_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;

    console.log("Files received:", {
      termination_letter_file,
      investigation_report_file,
      exit_interview_file,
      settlement_document_file
    });

    const result = await pool.query(
      `
      INSERT INTO employee_termination (
        employee_id, employee_code, employee_name,
        org_id,
        termination_initiated_by, termination_date, last_working_day, termination_type,
        immediate_termination, notice_period_applicable, notice_period_waived,
        primary_reason, detailed_reason,
        supporting_documents_attached, disciplinary_action_history,
        investigation_conducted, investigation_start_date, investigation_end_date,
        show_cause_notice_issued, employee_response_received, legal_compliance_check,
        reporting_manager_approval, hr_approval, legal_approval, final_termination_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,
        termination_letter_issued, termination_letter_date,
        exit_documents_signed, nda_confidentiality_reminder,
        salary_till_termination_date, notice_pay_recovery_payment, leave_encashment,
        bonus_incentives, deductions, final_settlement_amount, fnf_status, settlement_date,
        insurance_termination_date, pf_status, gratuity_eligibility, gratuity_amount,
        other_benefits_closure, rehire_eligibility, blacklist_status, remarks_notes,
        termination_letter_file, investigation_report_file, exit_interview_file, settlement_document_file,
        created_by,  created_at
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60,  NOW(), NOW())
      RETURNING *
      `,
      [
        realEmployeeId,                           // 1
        employee_code,                            // 2
        employeeName,                             // 3
        org_id,                                   // 4
        termination_initiated_by || null,         // 5
        termination_date || null,                 // 6
        last_working_day || null,                 // 7
        termination_type || null,                 // 8
        toBool(immediate_termination),            // 9
        toBool(notice_period_applicable),         // 10
        toBool(notice_period_waived),             // 11
        primary_reason || null,                   // 12
        detailed_reason || null,                  // 13
        toBool(supporting_documents_attached),    // 14
        toBool(disciplinary_action_history),      // 15
        toBool(investigation_conducted),          // 16
        investigation_start_date || null,         // 17
        investigation_end_date || null,           // 18
        toBool(show_cause_notice_issued),         // 19
        toBool(employee_response_received),       // 20
        toBool(legal_compliance_check),           // 21
        reporting_manager_approval || 'Pending',  // 22
        hr_approval || 'Pending',                 // 23
        legal_approval || 'Pending',              // 24
        final_termination_approval || 'Pending',  // 25
        reporting_manager_approval_date || null,  // 26
        hr_approval_date || null,                 // 27
        legal_approval_date || null,              // 28
        final_approval_date || null,              // 29
        toBool(system_access_revoked),            // 30
        access_revocation_date || null,           // 31
        toBool(email_disabled),                   // 32
        toBool(id_card_deactivated),              // 33
        company_assets_recovery_status || 'Pending', // 34
        asset_recovery_date || null,              // 35
        toBool(termination_letter_issued),        // 36
        termination_letter_date || null,          // 37
        toBool(exit_documents_signed),            // 38
        toBool(nda_confidentiality_reminder),     // 39
        salary_till_termination_date || null,     // 40
        notice_pay_recovery_payment || null,      // 41
        leave_encashment || null,                 // 42
        bonus_incentives || null,                 // 43
        deductions || null,                       // 44
        final_settlement_amount || null,          // 45
        fnf_status || null,                       // 46
        settlement_date || null,                  // 47
        insurance_termination_date || null,       // 48
        pf_status || null,                        // 49
        gratuity_eligibility || null,             // 50
        gratuity_amount || null,                  // 51
        other_benefits_closure || null,           // 52
        rehire_eligibility || null,               // 53
        toBool(blacklist_status),                 // 54
        remarks_notes || null,                    // 55
        termination_letter_file,                  // 56
        investigation_report_file,                // 57
        exit_interview_file,                      // 58
        settlement_document_file,                 // 59
        created_by || 'system'                // 60
        // updated_by || 'system'                    // 61
      ]
    );

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


/* ===========================
   ✅ GET ALL TERMINATION RECORDS
=========================== */

const getAllTerminationRecords = 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;
    
    let query = `
      SELECT 
        et.*,
        e.department,
        e.designation,
        e.email,
        e.phone
      FROM employee_termination et
      LEFT JOIN employees e ON et.employee_id = e.employee_id
      WHERE et.is_deleted = false
    `;
    
    const queryParams = [];
    let paramCount = 1;
    
    if (status) {
      query += ` AND et.final_termination_approval = $${paramCount}`;
      queryParams.push(status);
      paramCount++;
    }
    
    if (org_id) {
      query += ` AND et.org_id = $${paramCount}`;
      queryParams.push(org_id);
      paramCount++;
    }
    
    if (search) {
      query += ` AND (et.employee_name ILIKE $${paramCount} OR et.employee_code ILIKE $${paramCount} OR et.primary_reason 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 et.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 TERMINATION RECORDS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET TERMINATION BY ID
=========================== */

const getTerminationById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      SELECT 
        et.*,
        e.department,
        e.designation,
        e.email,
        e.phone,
        e.first_name,
        e.last_name
      FROM employee_termination et
      LEFT JOIN employees e ON et.employee_id = e.employee_id
      WHERE et.id = $1 AND et.is_deleted = false
      `,
      [id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Termination record not found",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET TERMINATION BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ GET TERMINATION BY EMPLOYEE
=========================== */

const getTerminationByEmployee = async (req, res) => {
  try {
    const { employee_id } = req.params;
    
    const result = await pool.query(
      `
      SELECT * FROM employee_termination
      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 TERMINATION BY EMPLOYEE ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ UPDATE TERMINATION RECORD
=========================== */

const updateTerminationRecord = async (req, res) => {
  try {
    const { id } = req.params;
    const updates = req.body;
    const files = req.files;

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

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

    const old = existing.rows[0];

    // Handle file updates
    const termination_letter_file = files?.termination_letter_file?.[0]?.originalname || old.termination_letter_file;
    const investigation_report_file = files?.investigation_report_file?.[0]?.originalname || old.investigation_report_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;

    const result = await pool.query(
      `
      UPDATE employee_termination
      SET
        termination_initiated_by = COALESCE($1, termination_initiated_by),
        termination_date = COALESCE($2, termination_date),
        last_working_day = COALESCE($3, last_working_day),
        termination_type = COALESCE($4, termination_type),
        immediate_termination = COALESCE($5, immediate_termination),
        notice_period_applicable = COALESCE($6, notice_period_applicable),
        notice_period_waived = COALESCE($7, notice_period_waived),
        primary_reason = COALESCE($8, primary_reason),
        detailed_reason = COALESCE($9, detailed_reason),
        supporting_documents_attached = COALESCE($10, supporting_documents_attached),
        disciplinary_action_history = COALESCE($11, disciplinary_action_history),
        investigation_conducted = COALESCE($12, investigation_conducted),
        investigation_start_date = COALESCE($13, investigation_start_date),
        investigation_end_date = COALESCE($14, investigation_end_date),
        show_cause_notice_issued = COALESCE($15, show_cause_notice_issued),
        employee_response_received = COALESCE($16, employee_response_received),
        legal_compliance_check = COALESCE($17, legal_compliance_check),
        reporting_manager_approval = COALESCE($18, reporting_manager_approval),
        hr_approval = COALESCE($19, hr_approval),
        legal_approval = COALESCE($20, legal_approval),
        final_termination_approval = COALESCE($21, final_termination_approval),
        reporting_manager_approval_date = COALESCE($22, reporting_manager_approval_date),
        hr_approval_date = COALESCE($23, hr_approval_date),
        legal_approval_date = COALESCE($24, legal_approval_date),
        final_approval_date = COALESCE($25, final_approval_date),
        system_access_revoked = COALESCE($26, system_access_revoked),
        access_revocation_date = COALESCE($27, access_revocation_date),
        email_disabled = COALESCE($28, email_disabled),
        id_card_deactivated = COALESCE($29, id_card_deactivated),
        company_assets_recovery_status = COALESCE($30, company_assets_recovery_status),
        asset_recovery_date = COALESCE($31, asset_recovery_date),
        termination_letter_issued = COALESCE($32, termination_letter_issued),
        termination_letter_date = COALESCE($33, termination_letter_date),
        exit_documents_signed = COALESCE($34, exit_documents_signed),
        nda_confidentiality_reminder = COALESCE($35, nda_confidentiality_reminder),
        salary_till_termination_date = COALESCE($36, salary_till_termination_date),
        notice_pay_recovery_payment = COALESCE($37, notice_pay_recovery_payment),
        leave_encashment = COALESCE($38, leave_encashment),
        bonus_incentives = COALESCE($39, bonus_incentives),
        deductions = COALESCE($40, deductions),
        final_settlement_amount = COALESCE($41, final_settlement_amount),
        fnf_status = COALESCE($42, fnf_status),
        settlement_date = COALESCE($43, settlement_date),
        insurance_termination_date = COALESCE($44, insurance_termination_date),
        pf_status = COALESCE($45, pf_status),
        gratuity_eligibility = COALESCE($46, gratuity_eligibility),
        gratuity_amount = COALESCE($47, gratuity_amount),
        other_benefits_closure = COALESCE($48, other_benefits_closure),
        rehire_eligibility = COALESCE($49, rehire_eligibility),
        blacklist_status = COALESCE($50, blacklist_status),
        remarks_notes = COALESCE($51, remarks_notes),
        termination_letter_file = $52,
        investigation_report_file = $53,
        exit_interview_file = $54,
        settlement_document_file = $55,
        updated_by = $56,
        updated_at = NOW()
      WHERE id = $57
      RETURNING *
      `,
      [
        updates.termination_initiated_by || null,
        updates.termination_date || null,
        updates.last_working_day || null,
        updates.termination_type || null,
        updates.immediate_termination,
        updates.notice_period_applicable,
        updates.notice_period_waived,
        updates.primary_reason || null,
        updates.detailed_reason || null,
        updates.supporting_documents_attached,
        updates.disciplinary_action_history,
        updates.investigation_conducted,
        updates.investigation_start_date || null,
        updates.investigation_end_date || null,
        updates.show_cause_notice_issued,
        updates.employee_response_received,
        updates.legal_compliance_check,
        updates.reporting_manager_approval || old.reporting_manager_approval,
        updates.hr_approval || old.hr_approval,
        updates.legal_approval || old.legal_approval,
        updates.final_termination_approval || old.final_termination_approval,
        updates.reporting_manager_approval_date || null,
        updates.hr_approval_date || null,
        updates.legal_approval_date || null,
        updates.final_approval_date || null,
        updates.system_access_revoked,
        updates.access_revocation_date || null,
        updates.email_disabled,
        updates.id_card_deactivated,
        updates.company_assets_recovery_status || old.company_assets_recovery_status,
        updates.asset_recovery_date || null,
        updates.termination_letter_issued,
        updates.termination_letter_date || null,
        updates.exit_documents_signed,
        updates.nda_confidentiality_reminder,
        updates.salary_till_termination_date || null,
        updates.notice_pay_recovery_payment || 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,
        updates.rehire_eligibility || null,
        updates.blacklist_status,
        updates.remarks_notes || null,
        termination_letter_file,
        investigation_report_file,
        exit_interview_file,
        settlement_document_file,
        updates.updated_by || 'system',
        id
      ]
    );

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

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

const updateTerminationApproval = async (req, res) => {
  try {
    const { id } = req.params;
    const { approval_type, status, updated_by } = req.body;

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

    let columnName = '';
    let dateColumn = '';

    switch (approval_type) {
      case 'reporting_manager':
        columnName = 'reporting_manager_approval';
        dateColumn = 'reporting_manager_approval_date';
        break;
      case 'hr':
        columnName = 'hr_approval';
        dateColumn = 'hr_approval_date';
        break;
      case 'legal':
        columnName = 'legal_approval';
        dateColumn = 'legal_approval_date';
        break;
      case 'final':
        columnName = 'final_termination_approval';
        dateColumn = 'final_approval_date';
        break;
    }

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

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Termination 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 TERMINATION APPROVAL ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

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

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

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

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

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

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

/* ===========================
   ✅ GET TERMINATION SUMMARY
=========================== */

const getTerminationSummary = async (req, res) => {
  try {
    // const { org_id } = req.query;
    const org_id = req.session.user.orgId;
    
    let query = `
      SELECT 
        COUNT(*) as total,
        COUNT(CASE WHEN final_termination_approval = 'Pending' THEN 1 END) as pending,
        COUNT(CASE WHEN final_termination_approval = 'Approved' THEN 1 END) as approved,
        COUNT(CASE WHEN final_termination_approval = 'Rejected' THEN 1 END) as rejected,
        COUNT(CASE WHEN termination_type = 'Voluntary' THEN 1 END) as voluntary,
        COUNT(CASE WHEN termination_type = 'Involuntary' THEN 1 END) as involuntary,
        COUNT(CASE WHEN termination_type = 'Mutual' THEN 1 END) as mutual,
        COUNT(CASE WHEN DATE_TRUNC('month', termination_date) = DATE_TRUNC('month', CURRENT_DATE) THEN 1 END) as this_month
      FROM employee_termination
      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 TERMINATION SUMMARY ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET TERMINATION BY REASON
=========================== */

const getTerminationByReason = async (req, res) => {
  try {
    // const { org_id } = req.query;
    const org_id = req.session.user.orgId;
    
    let query = `
      SELECT 
        primary_reason,
        COUNT(*) as count
      FROM employee_termination
      WHERE is_deleted = false AND primary_reason IS NOT NULL
    `;
    
    const queryParams = [];
    
    if (org_id) {
      query += ` AND org_id = $1`;
      queryParams.push(org_id);
    }
    
    query += ` GROUP BY primary_reason ORDER BY count DESC`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET TERMINATION BY REASON ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

module.exports = {
  createEmployeeTermination,
  getAllTerminationRecords,
  getTerminationById,
  getTerminationByEmployee,
  updateTerminationRecord,
  updateTerminationApproval,
  deleteTerminationRecord,
  getTerminationSummary,
  getTerminationByReason,
};