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


// /* ===========================
//    ✅ CREATE EMPLOYEE RELIEVING
// =========================== */

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

//     let {
//       employee_code,
//       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,
//       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 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;
//     };

//     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,
//         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, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         realEmployeeId,                           // 1
//         employee_code,                            // 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
//         created_by || 'system',  
//         updated_by || 'system'                  // 50
//       ]
//     );

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

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

const createEmployeeRelieving = async (req, res) => {
  try {
    console.log("=== RELIEVING 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,
      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,
      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 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;

    console.log("Files received:", {
      resignation_letter_file,
      acceptance_letter_file,
      relieving_letter_file,
      exit_interview_file,
      settlement_document_file
    });

    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, 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, NOW(), NOW())
      RETURNING *
      `,
      [
        realEmployeeId,                           // 1
        employee_code,                            // 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
        created_by || 'system',                   // 55
        updated_by || 'system'                    // 56
      ]
    );

    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;
    
    let 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
      WHERE er.is_deleted = false
    `;
    
    const queryParams = [];
    let paramCount = 1;
    
    if (status) {
      query += ` AND er.final_relieving_approval = $${paramCount}`;
      queryParams.push(status);
      paramCount++;
    }
    
    if (org_id) {
      query += ` AND er.org_id = $${paramCount}`;
      queryParams.push(org_id);
      paramCount++;
    }
    
    if (search) {
      query += ` AND (er.employee_name ILIKE $${paramCount} OR er.employee_code ILIKE $${paramCount} OR er.reason_for_resignation 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 er.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 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;

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

    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
      RETURNING *
      `,
      [
        updates.resignation_date || null,
        updates.last_working_day || null,
        updates.notice_period_days || null,
        updates.notice_period_waived,
        updates.reason_for_resignation || null,
        updates.reporting_manager_approval || old.reporting_manager_approval,
        updates.hr_approval || old.hr_approval,
        updates.legal_approval || old.legal_approval,
        updates.final_relieving_approval || old.final_relieving_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.resignation_letter_received,
        updates.acceptance_letter_issued,
        updates.acceptance_letter_date || null,
        updates.relieving_letter_issued,
        updates.relieving_letter_date || null,
        updates.exit_documents_signed,
        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,
        updates.exit_interview_conducted,
        updates.exit_interview_date || null,
        updates.exit_interview_feedback || null,
        updates.rehire_eligibility || null,
        updates.blacklist_status,
        updates.remarks_notes || null,
        resignation_letter_file,
        acceptance_letter_file,
        relieving_letter_file,
        exit_interview_file,
        settlement_document_file,
        updates.updated_by || 'system',
        id
      ]
    );

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

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

const updateApprovalStatus = 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_relieving_approval';
        dateColumn = 'final_approval_date';
        break;
    }

    const result = await pool.query(
      `
      UPDATE employee_relieving
      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: "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.query;
     const org_id =req.session.user.orgId;
    
    let 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
    `;
    
    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 RELIEVING SUMMARY ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

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