

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

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


/* ===========================
   ✅ CREATE EMPLOYEE (with dynamic employee_id from prefixes)
=========================== */
/* ===========================
   ✅ CREATE EMPLOYEE (with dynamic employee_id from prefixes)
   ✅ Allows future joining dates (no error)
   ✅ Prevents login access until joining date
   ✅ Emergency contact mandatory
   ✅ Camel case formatting for names
   ✅ Bank details optional
=========================== */
const createEmployee = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;

  try {
    const {
      first_name, last_name, gender, date_of_birth, marital_status,
      email, phone,
      address, city, state, country, pin_code,
      department, designation, employment_type, joining_date,
      confirmation_date, work_location, reporting_manager,
      working_status, salary, role, probation_months,
      pan_number, aadhar_number, passport_number, driving_license,
      bank_details,
      emergency_name, emergency_relationship, emergency_phone,
    } = req.body;

    // Format names to Proper Case (Camel Case style)
    const toProperCase = (str) => {
      if (!str || typeof str !== 'string') return str;
      return str
        .trim()
        .toLowerCase()
        .split(' ')
        .map(word => word.charAt(0).toUpperCase() + word.slice(1))
        .join(' ');
    };

    // Format account holder name
    const formatAccountHolderName = (name) => {
      if (!name) return name;
      return name
        .trim()
        .split(' ')
        .map(word => {
          if (word.length > 0) {
            return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
          }
          return word;
        })
        .join(' ');
    };

    const formattedFirstName = toProperCase(first_name);
    const formattedLastName = toProperCase(last_name);
    const formattedCity = toProperCase(city);
    const formattedState = toProperCase(state);
    const formattedCountry = toProperCase(country);
    const formattedWorkLocation = toProperCase(work_location);
    const formattedEmergencyName = toProperCase(emergency_name);
    const formattedEmergencyRelation = toProperCase(emergency_relationship);
    const formattedAddress = address ? toProperCase(address) : null;

    // Parse bank details if sent as JSON string (OPTIONAL)
    let bankDetailsArray = [];
    if (bank_details) {
      try {
        bankDetailsArray = typeof bank_details === 'string' 
          ? JSON.parse(bank_details) 
          : bank_details;
      } catch (e) {
        console.error("Failed to parse bank_details:", e);
      }
    }

    /* ── Files ── */
    const profile_image = getUploadPath(req.files?.profileImage);
    const pan_file = getUploadPath(req.files?.panFile);
    const aadhar_file = getUploadPath(req.files?.aadharFile);
    const passport_file = getUploadPath(req.files?.passportFile);
    const license_file = getUploadPath(req.files?.licenseFile);

    /* ── Validations ── */
    
    // 1. Emergency Contact Validation (MANDATORY)
    if (!emergency_name || !emergency_name.trim()) {
      return res.status(400).json({ 
        success: false, 
        message: "Emergency contact name is required" 
      });
    }
    
    if (!emergency_relationship || !emergency_relationship.trim()) {
      return res.status(400).json({ 
        success: false, 
        message: "Emergency contact relationship is required" 
      });
    }
    
    if (!emergency_phone || !emergency_phone.trim()) {
      return res.status(400).json({ 
        success: false, 
        message: "Emergency contact phone number is required" 
      });
    }

    // 2. Aadhaar Validation
    if (!aadhar_number || !/^\d{12}$/.test(aadhar_number)) {
      return res.status(400).json({ 
        success: false, 
        message: "Valid 12-digit Aadhaar number is required" 
      });
    }

    // 3. Age Validation
    if (date_of_birth) {
      const dob = new Date(date_of_birth);
      const today = new Date();
      let age = today.getFullYear() - dob.getFullYear();
      const m = today.getMonth() - dob.getMonth();
      if (m < 0 || (m === 0 && today.getDate() < dob.getDate())) age--;
      if (age < 18) {
        return res.status(400).json({ 
          success: false, 
          message: "Employee must be at least 18 years old" 
        });
      }
    }

    // 4. Required Fields Validation
    if (!first_name || !last_name || !email || !phone || !city || !state || !country || !pin_code || !salary || !org_id) {
      return res.status(400).json({ 
        success: false, 
        message: "Required fields are missing" 
      });
    }

    // 5. Salary Validation
    if (isNaN(salary) || salary <= 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Salary must be a positive number" 
      });
    }

    // 6. Department & Designation Validation
    if (!department || !designation) {
      return res.status(400).json({ 
        success: false, 
        message: "Department & Designation are required" 
      });
    }

    // 7. Probation Validation
    if (working_status === "Probation" && !probation_months) {
      return res.status(400).json({ 
        success: false, 
        message: "Probation months are required when status is Probation" 
      });
    }

    /* ── Generate Employee ID from prefixes table ── */
    const prefixResult = await pool.query(`
      SELECT id, prefix_code, last_number 
      FROM prefixes 
      WHERE module_name = 'employee_id' 
        AND org_id = $1 
        AND status = 'ACTIVE' 
        AND is_deleted = false
      FOR UPDATE
    `, [org_id]);

    if (prefixResult.rows.length === 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Employee ID prefix not configured. Please contact administrator." 
      });
    }

    const prefixConfig = prefixResult.rows[0];
    const prefixCode = prefixConfig.prefix_code;
    let nextNumber = prefixConfig.last_number + 1;
    const newEmployeeId = `${prefixCode}${nextNumber.toString().padStart(4, '0')}`;

    // Determine employee access status based on joining date
    // "Active" = can login, "Pending" = cannot login until joining date
    const joinDate = joining_date ? new Date(joining_date) : null;
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    
    let employeeStatus = "Pending"; // Default - no access
    
    if (joinDate) {
      joinDate.setHours(0, 0, 0, 0);
      if (joinDate <= today) {
        employeeStatus = "Active"; // Can access today
      } else {
        employeeStatus = "Pending"; // Future date - no access until joining date
      }
    }

    // Start transaction
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');

      // Update the last_number in prefixes table
      await client.query(`
        UPDATE prefixes 
        SET last_number = $1, updated_at = NOW()
        WHERE id = $2
      `, [nextNumber, prefixConfig.id]);

      /* ── Insert Employee with formatted names ── */
      const empQuery = `
        INSERT INTO public.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_id, designation_id, employment_type,
          joining_date, confirmation_date, work_location, reporting_manager_id,
          working_status, role, probation_months, salary,
          pan_number, aadhar_number, passport_number, driving_license,
          pan_file, aadhar_file, passport_file, license_file,
          emergency_name, emergency_relationship, emergency_phone,
          status, org_id, created_by
        ) 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
        ) RETURNING *;
      `;

      const empValues = [
        newEmployeeId,                                    // $1
        formattedFirstName || first_name?.trim(),        // $2
        formattedLastName || last_name?.trim(),          // $3
        gender || "other",                               // $4
        date_of_birth || null,                           // $5
        marital_status || "Single",                      // $6
        email.trim().toLowerCase(),                      // $7
        phone,                                           // $8
        profile_image,                                   // $9
        formattedAddress || address || null,             // $10
        formattedCity || city,                           // $11
        formattedState || state,                         // $12
        formattedCountry || country,                     // $13
        pin_code,                                        // $14
        newEmployeeId,                                   // $15 - employee_code
        department || null,                              // $16 - department_id
        designation || null,                             // $17 - designation_id
        employment_type || "full-time",                  // $18
        joining_date || null,                            // $19
        confirmation_date || null,                       // $20
        formattedWorkLocation || work_location || null,  // $21
        reporting_manager || null,                       // $22 - reporting_manager_id
        working_status || "Regular",                     // $23
        role || "others",                                // $24
        working_status === "Probation" ? probation_months : null, // $25
        parseFloat(salary) || 0,                         // $26
        pan_number ? pan_number.toUpperCase() : null,    // $27
        aadhar_number || null,                           // $28
        passport_number ? passport_number.toUpperCase() : null, // $29
        driving_license ? driving_license.toUpperCase() : null, // $30
        pan_file,                                        // $31
        aadhar_file,                                     // $32
        passport_file,                                   // $33
        license_file,                                    // $34
        formattedEmergencyName || emergency_name?.trim(), // $35
        formattedEmergencyRelation || emergency_relationship?.trim(), // $36
        emergency_phone?.trim(),                         // $37
        employeeStatus,                                  // $38 - Active or Pending
        org_id,                                          // $39
        userId || null,                                  // $40
      ];

      const empResult = await client.query(empQuery, empValues);
      const createdEmployee = empResult.rows[0];
      const employeeId = createdEmployee.id;

      /* ── Insert Bank Details (Optional - only if provided) ── */
      if (bankDetailsArray.length > 0) {
        for (let i = 0; i < bankDetailsArray.length; i++) {
          const bank = bankDetailsArray[i];
          const formattedAccountHolder = formatAccountHolderName(bank.account_holder_name);
          
          const bankQuery = `
            INSERT INTO public.employee_bank_details (
              employee_id, bank_name, account_holder_name,
              account_number, ifsc_code, branch, account_type,
              is_primary, created_by, updated_by
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
          `;
          
          await client.query(bankQuery, [
            employeeId,
            toProperCase(bank.bank_name),
            formattedAccountHolder || bank.account_holder_name,
            bank.account_number,
            bank.ifsc_code ? bank.ifsc_code.toUpperCase() : null,
            toProperCase(bank.branch),
            bank.account_type || 'Savings',
            bank.is_primary || (i === 0),
            userId,
            userId
          ]);
        }
      }

      await client.query('COMMIT');

      // /* ── Create user account ONLY if status is Active (joining date <= today) ── */
      // const finalRole = (role || "others").toLowerCase();

      // if (finalRole !== "others" && employeeStatus === "Active") {
      //   try {
      //     const existingUser = await pool.query(
      //       `SELECT id FROM users WHERE email = $1 AND org_id = $2`,
      //       [email.trim().toLowerCase(), org_id]
      //     );

      //     if (existingUser.rows.length === 0) {
      //       const tempPassword = phone.toString();
      //       const hashedPassword = await bcrypt.hash(tempPassword, 10);

      //       await pool.query(
      //         `INSERT INTO users (
      //           name, first_name, last_name, email, phone,
      //           role, org_id, status, is_deleted,
      //           reporting_manager, department_id, designation_id,
      //           joined_date, salary, employee_type,
      //           created_by, password
      //         ) VALUES (
      //           $1,$2,$3,$4,$5,
      //           $6,$7,'Approved',false,
      //           $8,$9,$10,
      //           $11,$12,$13,
      //           $14,$15
      //         )`,
      //         [
      //           `${formattedFirstName} ${formattedLastName}`,
      //           formattedFirstName,
      //           formattedLastName,
      //           email.trim().toLowerCase(),
      //           phone,
      //           finalRole,
      //           org_id,
      //           reporting_manager || null,
      //           department || null,
      //           designation || null,
      //           joining_date || null,
      //           parseFloat(salary) || 0,
      //           employment_type || "full-time",
      //           userId || null,
      //           hashedPassword,
      //         ]
      //       );

      //       console.log(`✅ User account created: ${email} [${finalRole}]`);
      //     } else {
      //       await pool.query(
      //         `UPDATE users SET role = $1, updated_at = NOW() WHERE email = $2 AND org_id = $3`,
      //         [finalRole, email.trim().toLowerCase(), org_id]
      //       );
      //       console.log(`✅ User role updated: ${email} → ${finalRole}`);
      //     }
      //   } catch (userErr) {
      //     console.error("⚠️ User account creation failed (employee still saved):", userErr.message);
      //   }
      // } else if (finalRole !== "others" && employeeStatus === "Pending") {
      //   console.log(`⏳ User account will be created on joining date: ${joining_date} for ${email}`);
      // }
      /* ── Create user account ONLY if status is Active (joining date <= today) ── */
const finalRole = (role || "others").toLowerCase();

if (finalRole !== "others" && employeeStatus === "Active") {
  try {
    const existingUser = await pool.query(
      `SELECT id FROM users WHERE email = $1 AND org_id = $2`,
      [email.trim().toLowerCase(), org_id]
    );

    if (existingUser.rows.length === 0) {
      // ✅ DEFAULT PASSWORD = PHONE NUMBER
      const defaultPassword = phone.toString(); // Use phone number as password
      const hashedPassword = await bcrypt.hash(defaultPassword, 10);
      
      console.log(` User account created with default password: ${defaultPassword} (phone number)`);

      await pool.query(
        `INSERT INTO users (
          name, first_name, last_name, email, phone,
          role, org_id, status, is_deleted,
          reporting_manager, department_id, designation_id,
          joined_date, salary, employee_type,
          created_by, password
        ) VALUES (
          $1,$2,$3,$4,$5,
          $6,$7,'Approved',false,
          $8,$9,$10,
          $11,$12,$13,
          $14,$15
        )`,
        [
          `${formattedFirstName} ${formattedLastName}`,
          formattedFirstName,
          formattedLastName,
          email.trim().toLowerCase(),
          phone,
          finalRole,
          org_id,
          reporting_manager || null,
          department || null,
          designation || null,
          joining_date || null,
          parseFloat(salary) || 0,
          employment_type || "full-time",
          userId || null,
          hashedPassword,
        ]
      );

      console.log(` User account created: ${email} [${finalRole}] with phone as default password`);
    } else {
      await pool.query(
        `UPDATE users SET role = $1, updated_at = NOW() WHERE email = $2 AND org_id = $3`,
        [finalRole, email.trim().toLowerCase(), org_id]
      );
      console.log(` User role updated: ${email} → ${finalRole}`);
    }
  } catch (userErr) {
    console.error(" User account creation failed (employee still saved):", userErr.message);
  }
} else if (finalRole !== "others" && employeeStatus === "Pending") {
  console.log(` User account will be created on joining date: ${joining_date} for ${email}`);
}

      // Success message (no error for future dates)
      let responseMessage = "Employee created successfully";
      if (employeeStatus === "Pending" && joining_date) {
        responseMessage = `Employee created successfully. Login access will be granted on ${joining_date}.`;
      }

      return res.status(201).json({
        success: true,
        message: responseMessage,
        employee_id: newEmployeeId,
        status: employeeStatus,
        joining_date: joining_date,
        data: createdEmployee,
      });

    } catch (dbError) {
      await client.query('ROLLBACK');
      throw dbError;
    } finally {
      client.release();
    }

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

/* ===========================
   ✅ GET ALL EMPLOYEES (from session org)
=========================== */
const getEmployees = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { status, search, limit = 1000, department_id, designation_id } = req.query;

    const orgIdNum = parseInt(org_id);
    if (isNaN(orgIdNum)) {
      return res.status(400).json({ 
        success: false, 
        message: "Invalid organization ID" 
      });
    }

    let query = `
      SELECT 
        e.id,
        e.employee_id,
        e.employee_code,
        e.first_name,
        e.last_name,
        e.email,
        e.phone,
        e.department_id,
        e.designation_id,
        e.working_status,
        e.status,              -- ✅ ADD THIS - the actual status column
        e.salary,
        e.profile_image,
        e.joining_date,
        e.is_onboarding,       -- ✅ ADD THIS
        e.benefits_completed,  -- ✅ ADD THIS
        COALESCE(d.dept_name, e.department_id, 'Unknown') AS department_name,
        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_deleted = false
        AND e.org_id = $1::integer
    `;
    
    const params = [orgIdNum];
    let paramIndex = 2;

    // Status filter should use the 'status' column, not 'working_status'
    if (status && status !== "" && status !== "undefined") {
      query += ` AND e.status = $${paramIndex}::text`;
      params.push(status);
      paramIndex++;
    }

    if (department_id && department_id !== "" && department_id !== "undefined") {
      query += ` AND e.department_id = $${paramIndex}::text`;
      params.push(String(department_id));
      paramIndex++;
    }

    if (designation_id && designation_id !== "" && designation_id !== "undefined") {
      query += ` AND e.designation_id = $${paramIndex}::text`;
      params.push(String(designation_id));
      paramIndex++;
    }

    if (search && search !== "" && search !== "undefined") {
      query += ` AND (e.first_name ILIKE $${paramIndex}::text OR e.last_name ILIKE $${paramIndex}::text OR e.employee_code ILIKE $${paramIndex}::text OR e.email ILIKE $${paramIndex}::text)`;
      params.push(`%${search}%`);
      paramIndex++;
    }

    query += ` ORDER BY e.created_at DESC`;
    
    if (limit && limit !== "" && limit !== "undefined") {
      const limitNum = parseInt(limit);
      if (!isNaN(limitNum) && limitNum > 0) {
        query += ` LIMIT $${paramIndex}::integer`;
        params.push(limitNum);
      }
    }

    const result = await pool.query(query, params);

    // Fetch manager names separately
    const employees = [];
    for (const emp of result.rows) {
      let manager_name = null;
      if (emp.reporting_manager_id && emp.reporting_manager_id !== '') {
        const managerResult = await pool.query(`
          SELECT CONCAT(first_name, ' ', last_name) AS manager_name
          FROM employees
          WHERE id::text = $1 AND is_deleted = false
        `, [emp.reporting_manager_id]);
        
        if (managerResult.rows.length) {
          manager_name = managerResult.rows[0].manager_name;
        }
      }
      
      employees.push({
        id: emp.id,
        employee_id: emp.employee_id,
        employee_code: emp.employee_code,
        first_name: emp.first_name,
        last_name: emp.last_name,
        email: emp.email,
        phone: emp.phone,
        department_id: emp.department_id,
        designation_id: emp.designation_id,
        department_name: emp.department_name || "—",
        designation_name: emp.designation_name || "—",
        working_status: emp.working_status,
        status: emp.status || "Pending",        // ✅ Use the actual status column
        salary: emp.salary,
        profile_image: emp.profile_image,
        joining_date: emp.joining_date,
        is_onboarding: emp.is_onboarding,
        benefits_completed: emp.benefits_completed,
        manager_name: manager_name
      });
    }

    res.json({
      success: true,
      data: employees,
      totalRecords: employees.length,
    });
  } catch (err) {
    console.error("GET EMPLOYEES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE BY ID (with bank details)
=========================== */
const getEmployeeById = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;

    // First, get the employee data
    const result = await pool.query(`
      SELECT 
        e.*,
        COALESCE(d.dept_name, 'Unknown') AS department_name,
        COALESCE(des.designation_name, '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.id = $1
        AND e.org_id = $2
        AND e.is_deleted = false
    `, [id, org_id]);

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

    const employee = result.rows[0];

    // Fetch manager name separately if reporting_manager_id exists
    if (employee.reporting_manager_id && employee.reporting_manager_id !== '') {
      const managerResult = await pool.query(`
        SELECT CONCAT(first_name, ' ', last_name) AS manager_name
        FROM employees
        WHERE id::text = $1 AND is_deleted = false
      `, [employee.reporting_manager_id]);
      
      if (managerResult.rows.length) {
        employee.manager_name = managerResult.rows[0].manager_name;
      }
    }

    // Fetch bank details
    const bankDetails = await pool.query(`
      SELECT 
        id, bank_name, account_holder_name, account_number,
        ifsc_code, branch, account_type, is_primary, notes
      FROM public.employee_bank_details
      WHERE employee_id = $1 AND is_deleted = false AND is_active = true
      ORDER BY is_primary DESC
    `, [id]);

    employee.bank_details = bankDetails.rows;

    res.json({
      success: true,
      data: employee,
    });

  } catch (err) {
    console.error("GET EMPLOYEE ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};
/* ===========================
   ✅ UPDATE EMPLOYEE
=========================== */
const updateEmployee = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;
    
    const {
      first_name,
      last_name,
      gender,
      date_of_birth,
      marital_status,
      email,
      phone,
      address,
      city,
      state,
      country,
      pin_code,
      department,        // This is the department_id value
      designation,       // This is the designation_id value
      employment_type,
      joining_date,
      confirmation_date,
      work_location,
      reporting_manager,
      working_status,
      salary,
      role,
      probation_months,
      pan_number,
      aadhar_number,
      passport_number,
      driving_license,
      bank_details,
      emergency_name,
      emergency_relationship,
      emergency_phone,
    } = req.body;

    // Parse bank details
    let bankDetailsArray = [];
    if (bank_details) {
      try {
        bankDetailsArray = typeof bank_details === 'string' 
          ? JSON.parse(bank_details) 
          : bank_details;
      } catch (e) {
        console.error("Failed to parse bank_details:", e);
      }
    }

    // Get existing employee
    const existingEmployee = await pool.query(
      `SELECT * FROM employees WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );

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

    const existing = existingEmployee.rows[0];

    // Validate Aadhaar
    if (!aadhar_number || !/^\d{12}$/.test(aadhar_number)) {
      return res.status(400).json({
        success: false,
        message: "Valid 12-digit Aadhaar number is required",
      });
    }

    // Validate Bank Details if provided
    if (bankDetailsArray.length > 0) {
      for (const bank of bankDetailsArray) {
        if (!bank.bank_name || !bank.account_holder_name || !bank.account_number || !bank.ifsc_code || !bank.branch) {
          return res.status(400).json({
            success: false,
            message: "All bank details are required: Bank Name, Account Holder, Account Number, IFSC Code, Branch",
          });
        }
      }
    }

    // Handle file uploads
    const removeProfileImage = req.body.remove_profileImage === "true";
    const removePanFile = req.body.remove_panFile === "true";
    const removeAadharFile = req.body.remove_aadharFile === "true";
    const removePassportFile = req.body.remove_passportFile === "true";
    const removeLicenseFile = req.body.remove_licenseFile === "true";

    const profile_image = removeProfileImage ? null
      : getUploadPath(req.files?.profileImage, existing.profile_image);
    const pan_file = removePanFile ? null
      : getUploadPath(req.files?.panFile, existing.pan_file);
    const aadhar_file = removeAadharFile ? null
      : getUploadPath(req.files?.aadharFile, existing.aadhar_file);
    const passport_file = removePassportFile ? null
      : getUploadPath(req.files?.passportFile, existing.passport_file);
    const license_file = removeLicenseFile ? null
      : getUploadPath(req.files?.licenseFile, existing.license_file);

    // Age validation
    if (date_of_birth) {
      const dob = new Date(date_of_birth);
      const today = new Date();
      const age = today.getFullYear() - dob.getFullYear();
      const monthDiff = today.getMonth() - dob.getMonth();
      
      if (monthDiff < 0 || (monthDiff === 0 && today.getDate() < dob.getDate())) {
        if (age - 1 < 18) {
          return res.status(400).json({
            success: false,
            message: "Employee must be at least 18 years old"
          });
        }
      } else if (age < 18) {
        return res.status(400).json({
          success: false,
          message: "Employee must be at least 18 years old"
        });
      }
    }

    // Validate salary
    if (salary && (isNaN(salary) || salary <= 0)) {
      return res.status(400).json({
        success: false,
        message: "Salary must be a positive number",
      });
    }

    // Validate probation months
    if (working_status === "Probation" && !probation_months) {
      return res.status(400).json({
        success: false,
        message: "Probation months are required when status is Probation",
      });
    }

    // Start transaction
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');

      // FIXED: Changed 'department' to 'department_id' and 'designation' to 'designation_id'
      const query = `
        UPDATE employees
        SET 
          first_name = COALESCE($1, first_name),
          last_name = COALESCE($2, last_name),
          gender = COALESCE($3, gender),
          date_of_birth = COALESCE($4, date_of_birth),
          marital_status = COALESCE($5, marital_status),
          email = COALESCE($6, email),
          phone = COALESCE($7, phone),
          address = COALESCE($8, address),
          city = COALESCE($9, city),
          state = COALESCE($10, state),
          country = COALESCE($11, country),
          pin_code = COALESCE($12, pin_code),
          department_id = COALESCE($13, department_id),
          designation_id = COALESCE($14, designation_id),
          employment_type = COALESCE($15, employment_type),
          joining_date = COALESCE($16, joining_date),
          confirmation_date = COALESCE($17, confirmation_date),
          work_location = COALESCE($18, work_location),
          reporting_manager_id = COALESCE($19, reporting_manager_id),
          working_status = COALESCE($20, working_status),
          salary = COALESCE($21, salary),
          role = COALESCE($22, role),
          probation_months = COALESCE($23, probation_months),
          pan_number = COALESCE($24, pan_number),
          aadhar_number = COALESCE($25, aadhar_number),
          passport_number = COALESCE($26, passport_number),
          driving_license = COALESCE($27, driving_license),
          emergency_name = COALESCE($28, emergency_name),
          emergency_relationship = COALESCE($29, emergency_relationship),
          emergency_phone = COALESCE($30, emergency_phone),
          profile_image = $31,
          pan_file = $32,
          aadhar_file = $33,
          passport_file = $34,
          license_file = $35,
          updated_by = $36,
          updated_at = NOW()
        WHERE id = $37 
          AND org_id = $38 
          AND is_deleted = false
        RETURNING *;
      `;

      const values = [
        first_name || null,
        last_name || null,
        gender || null,
        date_of_birth || null,
        marital_status || null,
        email || null,
        phone || null,
        address || null,
        city || null,
        state || null,
        country || null,
        pin_code || null,
        department || null,      // This maps to department_id
        designation || null,     // This maps to designation_id
        employment_type || null,
        joining_date || null,
        confirmation_date || null,
        work_location || null,
        reporting_manager || null,  // This maps to reporting_manager_id
        working_status || null,
        salary ? parseFloat(salary) : null,
        role || null,
        probation_months || null,
        pan_number || null,
        aadhar_number || null,
        passport_number || null,
        driving_license || null,
        emergency_name || null,
        emergency_relationship || null,
        emergency_phone || null,
        profile_image,
        pan_file,
        aadhar_file,
        passport_file,
        license_file,
        userId,
        id,
        org_id,
      ];

      const result = await client.query(query, values);

      if (!result.rows.length) {
        await client.query('ROLLBACK');
        return res.status(404).json({
          success: false,
          message: "Employee not found",
        });
      }

      // Update bank details - soft delete existing and insert new
      await client.query(
        `UPDATE public.employee_bank_details 
         SET is_deleted = true, updated_by = $1, updated_at = NOW()
         WHERE employee_id = $2 AND is_deleted = false`,
        [userId, id]
      );

      // Insert new bank details
      for (let i = 0; i < bankDetailsArray.length; i++) {
        const bank = bankDetailsArray[i];
        const bankQuery = `
          INSERT INTO public.employee_bank_details (
            employee_id, bank_name, account_holder_name,
            account_number, ifsc_code, branch, account_type,
            is_primary, created_by, updated_by
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        `;
        
        await client.query(bankQuery, [
          id,
          bank.bank_name,
          bank.account_holder_name,
          bank.account_number,
          bank.ifsc_code,
          bank.branch,
          bank.account_type || 'Savings',
          bank.is_primary || (i === 0),
          userId,
          userId
        ]);
      }

      await client.query('COMMIT');

      // Fetch updated employee with bank details
      const updatedEmployee = result.rows[0];
      const updatedBankDetails = await pool.query(`
        SELECT id, bank_name, account_holder_name, account_number,
               ifsc_code, branch, account_type, is_primary
        FROM public.employee_bank_details
        WHERE employee_id = $1 AND is_deleted = false AND is_active = true
      `, [id]);
      
      updatedEmployee.bank_details = updatedBankDetails.rows;

      res.json({
        success: true,
        message: "Employee updated successfully ✅",
        data: updatedEmployee,
      });
      
    } catch (dbError) {
      await client.query('ROLLBACK');
      throw dbError;
    } finally {
      client.release();
    }
    
  } catch (err) {
    console.error("UPDATE EMPLOYEE ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};
/* ===========================
   ✅ DELETE EMPLOYEE (SOFT)
=========================== */
const deleteEmployee = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    const result = await pool.query(
      `
      UPDATE employees
      SET is_deleted = true,
          deleted_at = NOW(),
          updated_by = $1,
          updated_at = NOW()
      WHERE id = $2
        AND org_id = $3
        AND is_deleted = false
      RETURNING id
      `,
      [userId, id, org_id]
    );

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

    res.json({
      success: true,
      message: "Employee deleted successfully ✅",
    });
  } catch (err) {
    console.error("DELETE EMPLOYEE ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ COMPLETE ONBOARDING
=========================== */
const completeOnboarding = async (req, res) => {
  try {
    const { employee_id } = req.body;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    const result = await pool.query(`
      UPDATE employees
      SET is_onboarding = true,
          updated_by = $1,
          updated_at = NOW()
      WHERE employee_id = $2
        AND org_id = $3
        AND is_deleted = false
      RETURNING id
    `, [userId, employee_id, org_id]);

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

    res.json({ 
      success: true, 
      message: "Onboarding completed successfully" 
    });
  } catch (err) {
    console.error("COMPLETE ONBOARDING ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: err.message 
    });
  }
};

/* ===========================
   ✅ COMPLETE BENEFITS
=========================== */
const completeBenefits = async (req, res) => {
  try {
    const { employee_id } = req.body;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    const result = await pool.query(`
      UPDATE employees
      SET benefits_completed = true,
          updated_by = $1,
          updated_at = NOW()
      WHERE employee_id = $2
        AND org_id = $3
        AND is_deleted = false
      RETURNING id
    `, [userId, employee_id, org_id]);

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

    res.json({ 
      success: true, 
      message: "Benefits completed successfully" 
    });
  } catch (err) {
    console.error("COMPLETE BENEFITS ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: err.message 
    });
  }
};

/* ===========================
   ✅ EXIT EMPLOYEE
=========================== */
const exitEmployee = async (req, res) => {
  try {
    const { employee_id, termination_date, relieving_date } = req.body;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    const result = await pool.query(`
      UPDATE employees
      SET 
        termination_date = $1,
        relieving_date = $2,
        working_status = 'Terminated',
        updated_by = $3,
        updated_at = NOW()
      WHERE employee_id = $4
        AND org_id = $5
        AND is_deleted = false
      RETURNING id
    `, [termination_date, relieving_date, userId, employee_id, org_id]);

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

    res.json({ 
      success: true, 
      message: "Employee exited successfully" 
    });
  } catch (err) {
    console.error("EXIT EMPLOYEE ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: err.message 
    });
  }
};

/* ===========================
   ✅ GET EMPLOYEE STATS
=========================== */
const getEmployeeStats = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    const result = await pool.query(`
      SELECT
        COUNT(*) FILTER (WHERE working_status = 'Regular') AS regular,
        COUNT(*) FILTER (WHERE working_status = 'Probation') AS probation,
        COUNT(*) FILTER (WHERE working_status = 'Relieved') AS relieved,
        COUNT(*) FILTER (WHERE working_status = 'Terminated') AS terminated,
        COUNT(*) FILTER (WHERE is_onboarding = false) AS pending_onboarding,
        COUNT(*) FILTER (WHERE benefits_completed = false) AS pending_benefits,
        COUNT(*) AS total
      FROM employees
      WHERE org_id = $1
        AND is_deleted = false
    `, [org_id]);

    res.json({ 
      success: true, 
      data: result.rows[0] 
    });
  } catch (err) {
    console.error("GET EMPLOYEE STATS ERROR:", err);
    res.status(500).json({ 
      success: false, 
      message: err.message 
    });
  }
};

/* ===========================
   ✅ GET EMPLOYEES BY ORGANIZATION
=========================== */
const getEmployeesByOrganization = async (req, res) => {
  try {
    const { orgId } = req.params;
    const { status, search, limit = 1000, department_id, designation_id } = req.query;

    // Validate orgId is a number
    const orgIdNum = parseInt(orgId);
    if (isNaN(orgIdNum)) {
      return res.status(400).json({ 
        success: false, 
        message: "Invalid organization ID" 
      });
    }

    let query = `
      SELECT 
        e.id,
        e.employee_id,
        e.employee_code,
        e.first_name,
        e.last_name,
        e.email,
        e.phone,
        e.gender,
        e.date_of_birth,
        e.marital_status,
        e.address,
        e.city,
        e.state,
        e.country,
        e.pin_code,
        e.department_id,
        e.designation_id,
        e.employment_type,
        e.joining_date,
        e.confirmation_date,
        e.work_location,
        e.reporting_manager_id,
        e.working_status,
        e.salary,
        e.role,
        e.probation_months,
        e.pan_number,
        e.aadhar_number,
        e.passport_number,
        e.driving_license,
        e.emergency_name,
        e.emergency_relationship,
        e.emergency_phone,
        e.profile_image,
        e.is_onboarding,
        e.benefits_completed,
        e.status AS db_status,
        e.created_at,
        e.updated_at,
        e.org_id,
        COALESCE(d.dept_name, e.department_id, 'Unknown') AS department_name,
        COALESCE(des.designation_name, e.designation_id, 'Unknown') AS designation_name,
        CONCAT(m.first_name, ' ', m.last_name) AS manager_name
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.org_id = e.org_id AND COALESCE(d.is_deleted, false) = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.org_id = e.org_id AND COALESCE(des.is_deleted, false) = false
      LEFT JOIN employees m ON m.id::text = e.reporting_manager_id AND COALESCE(m.is_deleted, false) = false
      WHERE COALESCE(e.is_deleted, false) = false
        AND e.org_id = $1::integer
    `;
    
    const params = [orgIdNum];
    let paramIndex = 2;

    // Handle status filter
    if (status && status !== "" && status !== "undefined") {
      query += ` AND e.working_status = $${paramIndex}::text`;
      params.push(status);
      paramIndex++;
    }

    // Handle department_id filter - department_id is VARCHAR
    if (department_id && department_id !== "" && department_id !== "undefined") {
      query += ` AND e.department_id = $${paramIndex}::text`;
      params.push(String(department_id));
      paramIndex++;
    }

    // Handle designation_id filter - designation_id is VARCHAR
    if (designation_id && designation_id !== "" && designation_id !== "undefined") {
      query += ` AND e.designation_id = $${paramIndex}::text`;
      params.push(String(designation_id));
      paramIndex++;
    }

    // Handle search filter
    if (search && search !== "" && search !== "undefined") {
      query += ` AND (e.first_name ILIKE $${paramIndex}::text OR e.last_name ILIKE $${paramIndex}::text OR e.employee_code ILIKE $${paramIndex}::text OR e.email ILIKE $${paramIndex}::text)`;
      params.push(`%${search}%`);
      paramIndex++;
    }

    query += ` ORDER BY e.created_at DESC`;
    
    if (limit && limit !== "" && limit !== "undefined") {
      const limitNum = parseInt(limit);
      if (!isNaN(limitNum) && limitNum > 0) {
        query += ` LIMIT $${paramIndex}::integer`;
        params.push(limitNum);
      }
    }

    console.log("Executing query with params:", params);
    const result = await pool.query(query, params);

    const employees = result.rows.map(emp => ({
      id: emp.id,
      employee_id: emp.employee_id,
      employee_code: emp.employee_code,
      employee_name: `${emp.first_name} ${emp.last_name}`,
      first_name: emp.first_name,
      last_name: emp.last_name,
      email: emp.email,
      phone: emp.phone,
      gender: emp.gender,
      department_id: emp.department_id,
      department_name: emp.department_name || "Unknown",
      designation_id: emp.designation_id,
      designation_name: emp.designation_name || "Unknown",
      working_status: emp.working_status,
      status: emp.working_status || emp.db_status || "Active",
      salary: emp.salary,
      joining_date: emp.joining_date,
      profile_image: emp.profile_image,
      is_onboarding: emp.is_onboarding,
      is_benefits_completed: emp.benefits_completed || false,
      manager_name: emp.manager_name || null,
      created_at: emp.created_at,
      org_id: emp.org_id
    }));

    res.json({
      success: true,
      data: employees,
      totalRecords: employees.length,
      organization_id: orgIdNum
    });
  } catch (err) {
    console.error("GET EMPLOYEES BY ORGANIZATION ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEES BY DEPARTMENT
=========================== */
const getEmployeesByDepartment = async (req, res) => {
  try {
    const { departmentId } = req.params;
    const org_id = req.session.user.orgId;
    const { status, search } = req.query;

    let query = `
      SELECT 
        e.id, e.employee_id, e.employee_code, e.first_name, e.last_name,
        e.email, e.phone, e.designation, e.working_status AS status,
        e.profile_image, e.joining_date
      FROM employees e
      WHERE e.is_deleted = false
        AND e.org_id = $1
        AND e.department_id = $2
    `;
    
    const params = [org_id, departmentId];
    let paramIndex = 3;

    if (status) {
      query += ` AND e.working_status = $${paramIndex++}`;
      params.push(status);
    }

    if (search) {
      query += ` AND (e.first_name ILIKE $${paramIndex} OR e.last_name ILIKE $${paramIndex} OR e.employee_code ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
    }

    query += ` ORDER BY e.first_name ASC`;

    const result = await pool.query(query, params);

    res.json({
      success: true,
      data: result.rows,
      totalRecords: result.rows.length,
    });
  } catch (err) {
    console.error("GET EMPLOYEES BY DEPARTMENT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ UPDATE EMPLOYEE STATUS
=========================== */
const updateEmployeeStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    const validStatuses = ["Active", "Inactive", "Probation", "Terminated", "Relieved"];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: `Invalid status. Must be one of: ${validStatuses.join(", ")}`
      });
    }

    const result = await pool.query(
      `UPDATE employees 
       SET working_status = $1::text, 
           updated_by = $2::integer, 
           updated_at = NOW()
       WHERE id = $3::integer 
         AND org_id = $4::integer 
         AND is_deleted = false
       RETURNING id, employee_code, first_name, last_name, working_status`,
      [status, userId, id, org_id]
    );

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

    res.json({
      success: true,
      message: `Employee status updated to ${status}`,
      data: result.rows[0]
    });
  } catch (err) {
    console.error("UPDATE EMPLOYEE STATUS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE COUNT BY ORGANIZATION
=========================== */
const getEmployeeCountByOrganization = async (req, res) => {
  try {
    const { orgId } = req.params;

    const result = await pool.query(
      `SELECT 
         COUNT(*) AS total,
         COUNT(*) FILTER (WHERE working_status = 'Active') AS active,
         COUNT(*) FILTER (WHERE working_status = 'Inactive') AS inactive,
         COUNT(*) FILTER (WHERE working_status = 'Probation') AS probation,
         COUNT(*) FILTER (WHERE working_status = 'Terminated') AS terminated
       FROM employees
       WHERE org_id = $1 
         AND is_deleted = false`,
      [orgId]
    );

    res.json({
      success: true,
      data: result.rows[0]
    });
  } catch (err) {
    console.error("GET EMPLOYEE COUNT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ DEBUG - Check organization data
=========================== */
const debugOrgData = async (req, res) => {
  try {
    const { orgId } = req.params;
    
    const departments = await pool.query(
      `SELECT id, dept_name, status FROM departments WHERE org_id = $1 AND is_deleted = false`,
      [orgId]
    );
    
    const designations = await pool.query(
      `SELECT d.id, d.designation_name, d.department_id, dep.dept_name as department_name
       FROM designations d
       LEFT JOIN departments dep ON dep.id = d.department_id AND dep.org_id = d.org_id
       WHERE d.org_id = $1 AND d.is_deleted = false`,
      [orgId]
    );
    
    const employees = await pool.query(
      `SELECT 
         e.id, e.employee_code, e.first_name, e.last_name,
         e.department_id, e.designation_id,
         d.dept_name as dept_name,
         des.designation_name as desig_name
       FROM employees e
       LEFT JOIN departments d ON d.id = e.department_id AND d.org_id = e.org_id
       LEFT JOIN designations des ON des.id = e.designation_id AND des.org_id = e.org_id
       WHERE e.org_id = $1 AND e.is_deleted = false
       LIMIT 10`,
      [orgId]
    );
    
    const missingDept = await pool.query(
      `SELECT COUNT(*) as count
       FROM employees e
       LEFT JOIN departments d ON d.id = e.department_id AND d.org_id = e.org_id
       WHERE e.org_id = $1 
         AND e.is_deleted = false 
         AND e.department_id IS NOT NULL
         AND d.id IS NULL`,
      [orgId]
    );
    
    const missingDesig = await pool.query(
      `SELECT COUNT(*) as count
       FROM employees e
       LEFT JOIN designations des ON des.id = e.designation_id AND des.org_id = e.org_id
       WHERE e.org_id = $1 
         AND e.is_deleted = false 
         AND e.designation_id IS NOT NULL
         AND des.id IS NULL`,
      [orgId]
    );
    
    res.json({
      success: true,
      data: {
        departments: departments.rows,
        designations: designations.rows,
        sample_employees: employees.rows,
        stats: {
          total_departments: departments.rows.length,
          total_designations: designations.rows.length,
          employees_without_valid_department: parseInt(missingDept.rows[0].count),
          employees_without_valid_designation: parseInt(missingDesig.rows[0].count)
        }
      }
    });
  } catch (err) {
    console.error("DEBUG ORG DATA ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ DEBUG - Check all employees
=========================== */
const debugAllEmployees = async (req, res) => {
  try {
    const sessionOrgId = req.session.user.orgId;
    
    const allEmployees = await pool.query(`
      SELECT id, employee_id, employee_code, first_name, last_name, email, org_id, is_deleted, working_status
      FROM employees
      LIMIT 20
    `);
    
    const countByOrg = await pool.query(`
      SELECT org_id, COUNT(*) as total, 
             COUNT(*) FILTER (WHERE is_deleted = false) as active
      FROM employees
      GROUP BY org_id
    `);
    
    const sessionOrgEmployees = await pool.query(`
      SELECT id, employee_id, employee_code, first_name, last_name, email, org_id
      FROM employees
      WHERE org_id = $1 AND is_deleted = false
    `, [sessionOrgId]);
    
    res.json({
      success: true,
      data: {
        session_org_id: sessionOrgId,
        all_employees_sample: allEmployees.rows,
        count_by_org: countByOrg.rows,
        employees_in_session_org: sessionOrgEmployees.rows,
        session_org_count: sessionOrgEmployees.rows.length
      }
    });
  } catch (err) {
    console.error("DEBUG ALL EMPLOYEES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEE BANK DETAILS
=========================== */
const getEmployeeBankDetails = async (req, res) => {
  try {
    const { employee_id } = req.params;
    const org_id = req.session.user.orgId;

    const result = await pool.query(
      `SELECT 
         bd.id,
         bd.bank_name,
         bd.account_holder_name,
         bd.account_number,
         bd.ifsc_code,
         bd.branch,
         bd.account_type,
         bd.is_primary
       FROM employee_bank_details bd
       JOIN employees e ON e.id = bd.employee_id
       WHERE e.employee_code = $1 
         AND e.org_id = $2 
         AND bd.is_deleted = false 
         AND bd.is_active = true
       ORDER BY bd.is_primary DESC`,
      [employee_id, org_id]
    );

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET EMPLOYEE BANK DETAILS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  createEmployee,
  getEmployees,
  getEmployeeById,
  getEmployeesByOrganization,
  getEmployeesByDepartment,
  getEmployeeCountByOrganization,
  updateEmployee,
  updateEmployeeStatus,
  deleteEmployee,
  completeOnboarding,
  completeBenefits,
  exitEmployee,
  getEmployeeStats,
  debugOrgData,
  debugAllEmployees,
   getEmployeeBankDetails, 
};