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

// /* ===========================
//    ✅ CREATE EMPLOYEE ONBOARDING
// =========================== */



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

//     let { employee_id: employeeCode, selected_asset, remarks, joining_date } = req.body;

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

//     const employeeResult = await pool.query(
//       `SELECT employee_id, joining_date FROM employees WHERE employee_id = $1 AND is_deleted = false`,
//       [employeeCode]
//     );

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

//     const realEmployee = employeeResult.rows[0];
//     const dbJoiningDate = joining_date || realEmployee.joining_date;

//     const getFilePath = (fileArray) => {
//       if (!fileArray || !fileArray[0]) return null;
//       const fullPath = fileArray[0].path.replace(/\\/g, "/");
//       const relativePath = fullPath.split("uploads/")[1];
//       return relativePath ? "uploads/" + relativePath : null;
//     };

//     const offer_letter_file          = getFilePath(req.files?.offer_letter_file);
//     const address_proof_file         = getFilePath(req.files?.address_proof_file);
//     const education_certificates_file = getFilePath(req.files?.education_certificates_file);
//     const policy_acceptance_file     = getFilePath(req.files?.policy_acceptance_file);
//     const training_assignment_file   = getFilePath(req.files?.training_assignment_file);

//     if (!policy_acceptance_file) {
//       return res.status(400).json({ success: false, message: "Policy Acceptance document is required" });
//     }

//     const result = await pool.query(
//       `INSERT INTO employee_onboarding (
//         employee_id, org_id, joining_date, selected_asset,
//         offer_letter_file, address_proof_file,
//         education_certificates_file, policy_acceptance_file,
//         training_assignment_file, remarks
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
//       RETURNING *`,
//       [
//         realEmployee.employee_id,
//         org_id,
//         dbJoiningDate || null,
//         selected_asset || "None",
//         offer_letter_file,
//         address_proof_file,
//         education_certificates_file,
//         policy_acceptance_file,
//         training_assignment_file,
//         remarks || null,
//       ]
//     );

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

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






// /* ===========================
//    ✅ GET ALL ONBOARDINGS
// =========================== */
// const getEmployeeOnboardings = async (req, res) => {
//   try {
//     const result = await pool.query(`
//       SELECT
//         eo.*,
//         e.employee_id,
//         e.joining_date,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,

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

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

//         e.department_id,
//         e.designation_id

//       FROM employee_onboarding eo
//       JOIN employees e ON eo.employee_id = e.employee_id

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

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

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

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

//       WHERE eo.is_deleted = false
//       ORDER BY eo.created_at DESC
//     `);

//     res.json({
//       success: true,
//       data: result.rows,
//       totalRecords: result.rows.length,
//       currentPage: 1,
//     });
//   } catch (err) {
//     console.error("FETCH ONBOARDING ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };


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

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

//     const result = await pool.query(
//       `
//       SELECT
//         e.employee_id,
//         e.first_name,
//         e.last_name,
//         e.joining_date,
//         e.department_id,
//         e.designation_id,

//         -- department: use ::text casting
//         COALESCE(
//           d1.dept_name,
//           d2.dept_name,
//           e.department_id,
//           e.department
//         ) AS department_name,

//         -- designation: use ::text casting
//         COALESCE(
//           des1.designation_name,
//           des2.designation_name,
//           e.designation_id,
//           e.designation
//         ) AS designation_name

//       FROM employees e

//       LEFT JOIN departments d1
//         ON d1.id::text = e.department_id
//         AND d1.is_deleted = false

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

//       LEFT JOIN designations des1
//         ON des1.id::text = e.designation_id
//         AND des1.is_deleted = false

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

//       WHERE e.is_onboarding = false
//         AND e.is_deleted = false
//         AND e.org_id = $1::integer
//       ORDER BY e.created_at DESC
//       `,
//       [org_id]
//     );

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



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

//     const result = await pool.query(
//       `
//       SELECT
//         eo.*,
//         e.employee_id,
//         e.joining_date,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,

//         COALESCE(
//           d1.dept_name,
//           d2.dept_name,
//           e.department_id,
//           e.department
//         ) AS department_name,

//         COALESCE(
//           des1.designation_name,
//           des2.designation_name,
//           e.designation_id,
//           e.designation
//         ) AS designation_name

//       FROM employee_onboarding eo
//       LEFT JOIN employees e ON eo.employee_id = e.employee_id

//       LEFT JOIN departments d1
//         ON d1.id::text = e.department_id
//         AND d1.is_deleted = false

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

//       LEFT JOIN designations des1
//         ON des1.id::text = e.designation_id
//         AND des1.is_deleted = false

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

//       WHERE eo.id = $1
//         AND eo.is_deleted = false
//       `,
//       [id]
//     );

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

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

// /* ===========================
//    ✅ UPDATE
// =========================== */
// const updateEmployeeOnboarding = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { selected_asset } = req.body;

//     const offer_letter_file = req.files?.offer_letter_file?.[0]?.filename;
//     const address_proof_file = req.files?.address_proof_file?.[0]?.filename;
//     const education_certificates_file = req.files?.education_certificates_file?.[0]?.filename;
//     const policy_acceptance_file = req.files?.policy_acceptance_file?.[0]?.filename;
//     const training_assignment_file = req.files?.training_assignment_file?.[0]?.filename;

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

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

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

//     const result = await pool.query(
//       `
//       UPDATE employee_onboarding
//       SET
//         selected_asset = $1,
//         offer_letter_file = $2,
//         address_proof_file = $3,
//         education_certificates_file = $4,
//         policy_acceptance_file = $5,
//         training_assignment_file = $6,
//         updated_at = NOW()
//       WHERE id = $7
//       RETURNING *
//       `,
//       [
//         selected_asset || old.selected_asset,
//         offer_letter_file || old.offer_letter_file,
//         address_proof_file || old.address_proof_file,
//         education_certificates_file || old.education_certificates_file,
//         policy_acceptance_file || old.policy_acceptance_file,
//         training_assignment_file || old.training_assignment_file,
//         id,
//       ]
//     );

//     res.status(200).json({
//       success: true,
//       message: "Updated successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("UPDATE ONBOARDING ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };
// /* ===========================
//    ✅ DELETE
// =========================== */
// const deleteEmployeeOnboarding = async (req, res) => {
//   try {
//     const { id } = req.params;

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

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


// module.exports = {
//   createEmployeeOnboarding,
//   getEmployeeOnboardings,
//   getEmployeeOnboardingById,
//   updateEmployeeOnboarding,
//   deleteEmployeeOnboarding,
//   getEmployeesForOnboarding
// };


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

/* ===========================
   ✅ CREATE EMPLOYEE ONBOARDING
=========================== */

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

    let { employee_id: employeeCode, selected_asset, remarks, joining_date } = req.body;

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

    const employeeResult = await pool.query(
      `SELECT employee_id, joining_date FROM employees WHERE employee_id = $1 AND is_deleted = false`,
      [employeeCode]
    );

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

    const realEmployee = employeeResult.rows[0];
    const dbJoiningDate = joining_date || realEmployee.joining_date;

    const getFilePath = (fileArray) => {
      if (!fileArray || !fileArray[0]) return null;
      const fullPath = fileArray[0].path.replace(/\\/g, "/");
      const relativePath = fullPath.split("uploads/")[1];
      return relativePath ? "uploads/" + relativePath : null;
    };

    const offer_letter_file = getFilePath(req.files?.offer_letter_file);
    const address_proof_file = getFilePath(req.files?.address_proof_file);
    const education_certificates_file = getFilePath(req.files?.education_certificates_file);
    const policy_acceptance_file = getFilePath(req.files?.policy_acceptance_file);
    const training_assignment_file = getFilePath(req.files?.training_assignment_file);

    if (!policy_acceptance_file) {
      return res.status(400).json({ success: false, message: "Policy Acceptance document is required" });
    }

    const result = await pool.query(
      `INSERT INTO employee_onboarding (
        employee_id, org_id, joining_date, selected_asset,
        offer_letter_file, address_proof_file,
        education_certificates_file, policy_acceptance_file,
        training_assignment_file, remarks
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
      RETURNING *`,
      [
        realEmployee.employee_id,
        org_id,
        dbJoiningDate || null,
        selected_asset || "None",
        offer_letter_file,
        address_proof_file,
        education_certificates_file,
        policy_acceptance_file,
        training_assignment_file,
        remarks || null,
      ]
    );

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

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

/* ===========================
   ✅ GET ALL ONBOARDINGS
=========================== */
const getEmployeeOnboardings = async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT
        eo.*,
        e.employee_id,
        e.joining_date,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department_id,
        e.designation_id,

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

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

      FROM employee_onboarding eo
      JOIN employees e ON eo.employee_id = e.employee_id

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

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

      WHERE eo.is_deleted = false
      ORDER BY eo.created_at DESC
    `);

    res.json({
      success: true,
      data: result.rows,
      totalRecords: result.rows.length,
      currentPage: 1,
    });
  } catch (err) {
    console.error("FETCH ONBOARDING ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

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

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

    const result = await pool.query(
      `
      SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.joining_date,
        e.department_id,
        e.designation_id,

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

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

      FROM employees e

      LEFT JOIN departments d
        ON d.id::text = e.department_id
        AND d.is_deleted = false

      LEFT JOIN designations des
        ON des.id::text = e.designation_id
        AND des.is_deleted = false

      WHERE e.is_onboarding = false
        AND e.is_deleted = false
        AND e.org_id = $1::integer
      ORDER BY e.created_at DESC
      `,
      [org_id]
    );

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

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

    const result = await pool.query(
      `
      SELECT
        eo.*,
        e.employee_id,
        e.joining_date,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department_id,
        e.designation_id,

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

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

      FROM employee_onboarding eo
      LEFT JOIN employees e ON eo.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 eo.id = $1
        AND eo.is_deleted = false
      `,
      [id]
    );

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

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

/* ===========================
   ✅ UPDATE ONBOARDING
=========================== */
const updateEmployeeOnboarding = async (req, res) => {
  try {
    const { id } = req.params;
    const { selected_asset } = req.body;

    const getFilePath = (fileArray) => {
      if (!fileArray || !fileArray[0]) return null;
      const fullPath = fileArray[0].path.replace(/\\/g, "/");
      const relativePath = fullPath.split("uploads/")[1];
      return relativePath ? "uploads/" + relativePath : null;
    };

    const offer_letter_file = getFilePath(req.files?.offer_letter_file);
    const address_proof_file = getFilePath(req.files?.address_proof_file);
    const education_certificates_file = getFilePath(req.files?.education_certificates_file);
    const policy_acceptance_file = getFilePath(req.files?.policy_acceptance_file);
    const training_assignment_file = getFilePath(req.files?.training_assignment_file);

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

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

    const old = existing.rows[0];

    const result = await pool.query(
      `
      UPDATE employee_onboarding
      SET
        selected_asset = COALESCE($1, selected_asset),
        offer_letter_file = COALESCE($2, offer_letter_file),
        address_proof_file = COALESCE($3, address_proof_file),
        education_certificates_file = COALESCE($4, education_certificates_file),
        policy_acceptance_file = COALESCE($5, policy_acceptance_file),
        training_assignment_file = COALESCE($6, training_assignment_file),
        remarks = COALESCE($7, remarks),
        updated_at = NOW()
      WHERE id = $8
      RETURNING *
      `,
      [
        selected_asset || old.selected_asset,
        offer_letter_file || old.offer_letter_file,
        address_proof_file || old.address_proof_file,
        education_certificates_file || old.education_certificates_file,
        policy_acceptance_file || old.policy_acceptance_file,
        training_assignment_file || old.training_assignment_file,
        req.body.remarks || old.remarks,
        id,
      ]
    );

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

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

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

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

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

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

module.exports = {
  createEmployeeOnboarding,
  getEmployeeOnboardings,
  getEmployeeOnboardingById,
  updateEmployeeOnboarding,
  deleteEmployeeOnboarding,
  getEmployeesForOnboarding
};