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

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


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

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

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

//     // ✅ Fetch real employee_id and joining_date from employees table
//     const employeeResult = await pool.query(
//       `SELECT id, employee_id, joining_date 
//        FROM employees 
//        WHERE employee_code = $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 realEmployeeId = realEmployee.employee_id;       
//     const dbJoiningDate = joining_date || realEmployee.joining_date;

//     /* ================= FILES ================= */
//     const offer_letter_file = req.files?.offer_letter_file?.[0]?.filename || null;
//     const identity_proof_file = req.files?.identity_proof_file?.[0]?.filename || null;
//     const address_proof_file = req.files?.address_proof_file?.[0]?.filename || null;
//     const education_certificates_file = req.files?.education_certificates_file?.[0]?.filename || null;
//     const policy_acceptance_file = req.files?.policy_acceptance_file?.[0]?.filename || null;
//     const training_assignment_file = req.files?.training_assignment_file?.[0]?.filename || null;

//     const result = await pool.query(
//       `
//       INSERT INTO employee_onboarding (
//         employee_id,         
//         org_id,
//         joining_date,
//         selected_asset,
//         offer_letter_file,
//         identity_proof_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, $11)
//       RETURNING *
//       `,
//       [
//         realEmployeeId,
//         org_id,
//         dbJoiningDate || null,
//         selected_asset || "None",
//         offer_letter_file,
//         identity_proof_file,
//         address_proof_file,
//         education_certificates_file,
//         policy_acceptance_file,
//         training_assignment_file,
//         remarks || null,
//       ]
//     );

//     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,
//     });
//   }
// };
const createEmployeeOnboarding = async (req, res) => {
  try {
    console.log("=== ONBOARDING DEBUG ===");
    console.log("req.body:", req.body);
    console.log("req.files:", req.files);

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

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

    // Fetch real employee details
    const employeeResult = await pool.query(
      `SELECT employee_id, joining_date 
       FROM employees 
       WHERE employee_code = $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 realEmployeeId = realEmployee.employee_id;
    const dbJoiningDate = joining_date || realEmployee.joining_date;

    /* ================= FILES - Use originalname instead of filename ================= */
    const offer_letter_file = req.files?.offer_letter_file?.[0]?.originalname || null;
    const identity_proof_file = req.files?.identity_proof_file?.[0]?.originalname || null;
    const address_proof_file = req.files?.address_proof_file?.[0]?.originalname || null;
    const education_certificates_file = req.files?.education_certificates_file?.[0]?.originalname || null;
    const policy_acceptance_file = req.files?.policy_acceptance_file?.[0]?.originalname || null;
    const training_assignment_file = req.files?.training_assignment_file?.[0]?.originalname || null;

    console.log("Stored Original Filenames:", {
      offer_letter_file,
      identity_proof_file,
      address_proof_file,
      education_certificates_file,
      policy_acceptance_file,
      training_assignment_file
    });

    const result = await pool.query(
      `
      INSERT INTO employee_onboarding (
        employee_id,
        org_id,
        joining_date,
        selected_asset,
        offer_letter_file,
        identity_proof_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, $11)
      RETURNING *
      `,
      [
        realEmployeeId,
        org_id,
        dbJoiningDate || null,
        selected_asset || "None",
        offer_letter_file,
        identity_proof_file,
        address_proof_file,
        education_certificates_file,
        policy_acceptance_file,
        training_assignment_file,
        remarks || null,
      ]
    );

    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_code,
        e.joining_date,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation
      FROM employee_onboarding eo
      JOIN employees e
        ON eo.employee_id = e.employee_id
      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 BY ID
=========================== */

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

    const result = await pool.query(
      `
      SELECT
        eo.*,
        e.employee_code,
        e.joining_date,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation
      FROM employee_onboarding eo
      LEFT JOIN employees e
        ON eo.employee_id = e.employee_id
      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 identity_proof_file =
      req.files?.identity_proof_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,
        identity_proof_file = $3,
        address_proof_file = $4,
        education_certificates_file = $5,
        policy_acceptance_file = $6,
        training_assignment_file = $7,
        updated_at = NOW()
      WHERE id = $8
      RETURNING *
      `,
      [
        selected_asset || old.selected_asset,
        offer_letter_file || old.offer_letter_file,
        identity_proof_file ||
          old.identity_proof_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,
};