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

const createEmployee = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const data = req.body;
    console
    const employeeId = "EMP-" + Date.now();

    // ================= FILES =================
    const profileImage = req.files?.profileImage?.[0]?.filename || null;
    const panFile = req.files?.panFile?.[0]?.filename || null;
    const aadharFile = req.files?.aadharFile?.[0]?.filename || null;
    const passportFile = req.files?.passportFile?.[0]?.filename || null;
    const licenseFile = req.files?.licenseFile?.[0]?.filename || null;

    // ================= NEW ONBOARDING FILES =================
    const offerLetterFile =
  req.files?.offerLetterFile?.[0]?.filename || null;

const identityProofFile =
  req.files?.identityProofFile?.[0]?.filename || null;

const addressProofFile =
  req.files?.addressProofFile?.[0]?.filename || null;

const educationCertificatesFile =
  req.files?.educationCertificatesFile?.[0]?.filename || null;

const policyAcceptanceFile =
  req.files?.policyAcceptanceFile?.[0]?.filename || null;

const trainingAssignmentFile =
  req.files?.trainingAssignmentFile?.[0]?.filename || null;
  
  // ================= BENEFITS =================
const salaryRevisionDate = data.salaryRevisionDate || null;
const previousSalary = data.previousSalary || "";
const revisedSalary = data.revisedSalary || "";
const bonusAmount = data.bonusAmount || "";

const benefitType = data.benefitType || "";
const insurancePlan = data.insurancePlan || "";

const startBenefitsDate = data.startBenefitsDate || null;
const endBenefitsDate = data.endBenefitsDate || null;

    // const gender = data.gender
    //   ? data.gender.trim().toLowerCase()
    //   : "other";

       const gender = data.gender
      ? data.gender.trim().toLowerCase()
      : null;
     

    const query = `
      INSERT INTO employees (
        employee_id, first_name, last_name, gender,
        date_of_birth, marital_status,
        email, phone, profile_image,

        address, city, state, country, pin_code,

        employee_code, department, designation,
        employment_type, joining_date, confirmation_date,
        work_location, reporting_manager, employee_status,

        pan_number, aadhar_number, passport_number, driving_license,
        pan_file, aadhar_file, passport_file, license_file,

        bank_name, account_holder_name, account_number, ifsc_code, branch,

        emergency_name, emergency_relationship, emergency_phone,

        offer_letter_file,
        identity_proof_file,
        address_proof_file,
        education_certificates_file,
        selected_asset,
        policy_acceptance_file,
        training_assignment_file,
        remarks,

salary_revision_date,
previous_salary,
revised_salary,
bonus_amount,
benefit_type,
insurance_plan,
start_benefits_date,
end_benefits_date,

org_id, created_at
      )
      VALUES (
        '${employeeId}',
        '${data.firstName}',
        '${data.lastName}',
        '${data.gender ? `${data.gender.trim().toLowerCase()}` : "other"}',

        ${data.dateOfBirth ? `'${data.dateOfBirth}'` : "NULL"},
        ${data.maritalStatus ? `'${data.maritalStatus}'` : "NULL"},

        '${data.email}',
        '${data.phone}',
        ${profileImage ? `'${profileImage}'` : "NULL"},

        '${data.address || ""}',
        '${data.city}',
        '${data.state}',
        '${data.country}',
        '${data.pinCode}',

        '${data.employeeCode || ""}',
        '${data.department || "Other"}',
        '${data.designation || "Other"}',
        '${data.employmentType || "full-time"}',

        ${data.joiningDate ? `'${data.joiningDate}'` : "NULL"},
        ${data.confirmationDate ? `'${data.confirmationDate}'` : "NULL"},

        '${data.workLocation || ""}',
        '${data.reportingManager || "Other"}',
        '${data.employeeStatus || "Active"}',

        '${data.pan || ""}',
        '${data.aadhar || ""}',
        '${data.passport || ""}',
        '${data.license || ""}',

        ${panFile ? `'${panFile}'` : "NULL"},
        ${aadharFile ? `'${aadharFile}'` : "NULL"},
        ${passportFile ? `'${passportFile}'` : "NULL"},
        ${licenseFile ? `'${licenseFile}'` : "NULL"},

        '${data.bankName || ""}',
        '${data.accountHolderName || ""}',
        '${data.accountNumber || ""}',
        '${data.ifscCode || ""}',
        '${data.branch || ""}',

        '${data.emergencyName || ""}',
        '${data.emergencyRelation || ""}',
        '${data.emergencyPhone || ""}',

        ${offerLetterFile ? `'${offerLetterFile}'` : "NULL"},
        ${identityProofFile ? `'${identityProofFile}'` : "NULL"},
        ${addressProofFile ? `'${addressProofFile}'` : "NULL"},
        ${educationCertificatesFile ? `'${educationCertificatesFile}'` : "NULL"},
        '${data.selectedAsset || "None"}',
        ${policyAcceptanceFile ? `'${policyAcceptanceFile}'` : "NULL"},
        ${trainingAssignmentFile ? `'${trainingAssignmentFile}'` : "NULL"},
        '${data.remarks || ""}',

${salaryRevisionDate ? `'${salaryRevisionDate}'` : "NULL"},
'${previousSalary}',
'${revisedSalary}',
'${bonusAmount}',
'${benefitType}',
'${insurancePlan}',
${startBenefitsDate ? `'${startBenefitsDate}'` : "NULL"},
${endBenefitsDate ? `'${endBenefitsDate}'` : "NULL"},

'${data.orgId}',
NOW()
       
      )
      RETURNING *;
    `;

    const result = await pool.query(query);

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


// const getEmployees = async (req, res) => {
//   try {
//     const result = await pool.query(
//       "SELECT * FROM employees WHERE is_deleted = false ORDER BY created_at DESC"
//     );

//     res.json({
//       success: true,
//       data: result.rows,
//     });

//   } catch (error) {
//     console.error("GET ERROR:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

const getEmployees = async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10; // 10 records per page
    const offset = (page - 1) * limit;

    const result = await pool.query(
      `SELECT * FROM employees 
       WHERE is_deleted = false
       ORDER BY created_at DESC
       LIMIT $1 OFFSET $2`,
      [limit, offset]
    );

    // total records for frontend to calculate pages
    const countResult = await pool.query(
      `SELECT COUNT(*) FROM employees WHERE is_deleted = false`
    );

    res.json({
      success: true,
      data: result.rows,
      total: parseInt(countResult.rows[0].count),
      page,
      limit,
    });
  } catch (error) {
    console.error("GET ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

const getEmployeeById = async (req, res) => {
  try {
    const result = await pool.query(
      "SELECT * FROM employees WHERE id = $1 AND is_deleted = false",
      [req.params.id]
    );

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

    res.json({
      success: true,
      data: result.rows[0],
    }); 

  } catch (error) {
    res.status(500).json({ message: error.message });
  }
};






const updateEmployee = async (req, res) => {
  try {
    const data = req.body;
    console.log("UPDATE PAYLOAD:", data);

    // FILES
    const profileImage = req.files?.profileImage?.[0]?.filename || null;
    const panFile = req.files?.panFile?.[0]?.filename || null;
    const aadharFile = req.files?.aadharFile?.[0]?.filename || null;
    const passportFile = req.files?.passportFile?.[0]?.filename || null;
    const licenseFile = req.files?.licenseFile?.[0]?.filename || null;

    const offerLetterFile =
  req.files?.offerLetterFile?.[0]?.filename || null;

const identityProofFile =
  req.files?.identityProofFile?.[0]?.filename || null;

const addressProofFile =
  req.files?.addressProofFile?.[0]?.filename || null;

const educationCertificatesFile =
  req.files?.educationCertificatesFile?.[0]?.filename || null;

const policyAcceptanceFile =
  req.files?.policyAcceptanceFile?.[0]?.filename || null;

const trainingAssignmentFile =
  req.files?.trainingAssignmentFile?.[0]?.filename || null;

  // ================= BENEFITS =================
const salaryRevisionDate = data.salaryRevisionDate || null;
const previousSalary = data.previousSalary || "";
const revisedSalary = data.revisedSalary || "";
const bonusAmount = data.bonusAmount || "";

const benefitType = data.benefitType || "";
const insurancePlan = data.insurancePlan || "";

const startBenefitsDate = data.startBenefitsDate || null;
const endBenefitsDate = data.endBenefitsDate || null;
    // ✅ Normalize gender
    const gender = data.gender
      ? data.gender.trim().toLowerCase()
      : null;

    const query = `
      UPDATE employees SET

         employee_id = ${data.employeeId ? `'${data.employeeId}'` : "employee_id"},
        first_name = ${data.firstName ? `'${data.firstName}'` : "first_name"},
        last_name = ${data.lastName ? `'${data.lastName}'` : "last_name"},

        gender = ${gender ? `'${gender}'` : "gender"},

       



        date_of_birth = ${
          data.dateOfBirth ? `'${data.dateOfBirth}'` : "NULL"
        },
        marital_status = ${
          data.maritalStatus ? `'${data.maritalStatus}'` : "marital_status"
        },

       email = ${data.email ? `'${data.email}'` : "email"},
        phone = ${data.phone ? `'${data.phone}'` : "phone"},

        profile_image = ${
          profileImage ? `'${profileImage}'` : "profile_image"
        },

        
        address = ${data.address ? `'${data.address}'` : "address"},
city = ${data.city ? `'${data.city}'` : "city"},
state = ${data.state ? `'${data.state}'` : "state"},
country = ${data.country ? `'${data.country}'` : "country"},
pin_code = ${data.pinCode ? `'${data.pinCode}'` : "pin_code"},

        employee_code = '${data.employeeCode || ""}',
        department = '${data.department || "Other"}',
        designation = '${data.designation || "Other"}',

        employment_type = '${data.employmentType || "full-time"}',

        joining_date = ${
          data.joiningDate ? `'${data.joiningDate}'` : "joining_date"
        },
        confirmation_date = ${
          data.confirmationDate
            ? `'${data.confirmationDate}'`
            : "confirmation_date"
        },

        work_location = '${data.workLocation || ""}',
        reporting_manager = '${data.reportingManager || "Other"}',
        employee_status = '${data.employeeStatus || "Active"}',

        pan_number = '${data.pan || ""}',
        aadhar_number = '${data.aadhar || ""}',
        passport_number = '${data.passport || ""}',
        driving_license = '${data.license || ""}',

        pan_file = ${panFile ? `'${panFile}'` : "pan_file"},
        aadhar_file = ${aadharFile ? `'${aadharFile}'` : "aadhar_file"},
        passport_file = ${passportFile ? `'${passportFile}'` : "passport_file"},
        license_file = ${licenseFile ? `'${licenseFile}'` : "license_file"},

        bank_name = '${data.bankName || ""}',
        account_holder_name = '${data.accountHolderName || ""}',
        account_number = '${data.accountNumber || ""}',
        ifsc_code = '${data.ifscCode || ""}',
        branch = '${data.branch || ""}',

        emergency_name = '${data.emergencyName || ""}',
        emergency_relationship = '${data.emergencyRelation || ""}',
        emergency_phone = '${data.emergencyPhone || ""}',
        offer_letter_file = ${
  offerLetterFile ? `'${offerLetterFile}'` : "offer_letter_file"
},
identity_proof_file = ${
  identityProofFile ? `'${identityProofFile}'` : "identity_proof_file"
},
address_proof_file = ${
  addressProofFile ? `'${addressProofFile}'` : "address_proof_file"
},
education_certificates_file = ${
  educationCertificatesFile
    ? `'${educationCertificatesFile}'`
    : "education_certificates_file"
},
selected_asset = '${data.selectedAsset || "None"}',
policy_acceptance_file = ${
  policyAcceptanceFile
    ? `'${policyAcceptanceFile}'`
    : "policy_acceptance_file"
},
training_assignment_file = ${
  trainingAssignmentFile
    ? `'${trainingAssignmentFile}'`
    : "training_assignment_file"
},
remarks = '${data.remarks || ""}',


salary_revision_date = ${
  salaryRevisionDate ? `'${salaryRevisionDate}'` : "salary_revision_date"
},
previous_salary = '${previousSalary}',
revised_salary = '${revisedSalary}',
bonus_amount = '${bonusAmount}',
benefit_type = '${benefitType}',
insurance_plan = '${insurancePlan}',
start_benefits_date = ${
  startBenefitsDate ? `'${startBenefitsDate}'` : "start_benefits_date"
},
end_benefits_date = ${
  endBenefitsDate ? `'${endBenefitsDate}'` : "end_benefits_date"
},

updated_at = NOW()

    WHERE id = '${req.params.id}'
      RETURNING *;
    `;

    console.log("FINAL UPDATE QUERY:", query);

    const result = await pool.query(query);

    res.json({
      success: true,
      message: "Employee updated successfully ",
      data: result.rows[0],
    });

  } catch (error) {
    console.error("UPDATE ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};



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

    const query = `
      UPDATE employees
      SET 
        is_deleted = true,
        updated_at = NOW()
      WHERE id = '${id}'
        AND is_deleted = false
      RETURNING *;
    `;

    console.log("DELETE QUERY:", query);

    const result = await pool.query(query);

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

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

module.exports = {
  createEmployee,
  getEmployees,
  getEmployeeById,
  updateEmployee,
  deleteEmployee,
};