

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


// /* ===========================
//    ✅ CREATE ATTENDANCE
// =========================== */

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

//     let {
//       employee_id: employeeCode,
//       attendance_date,
//       check_in_time,
//       check_out_time,
//       shift_type: shiftFromRequest,
//       work_hours,
//       latitude,
//       longitude,
//       office_location,
//       remarks,
//       org_id,
//       created_by
//     } = req.body;

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

//     // Fetch employee id only (don't try to fetch shift column)
//     const employeeResult = await pool.query(
//       `SELECT id 
//        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 realEmployeeId = employeeResult.rows[0].id;
//     // Use shift from request or default to "General"
//     const finalShift = shiftFromRequest || "General";

//     // Check if attendance already exists
//     const existingAttendance = await pool.query(
//       `SELECT id, attendance_status 
//        FROM attendance 
//        WHERE employee_id = $1 
//          AND attendance_date = $2 
//          AND is_deleted = false`,
//       [realEmployeeId, attendance_date]
//     );

//     if (existingAttendance.rows.length > 0) {
//       return res.status(409).json({
//         success: false,
//         message: `Attendance already marked for this employee on ${attendance_date}`,
//         existing: existingAttendance.rows[0]
//       });
//     }

//     // Calculate work hours
//     let calculatedWorkHours = work_hours;
//     let calculatedOvertime = 0;

    
    
//     if (check_in_time && check_out_time && !work_hours) {
//       const [inHours, inMinutes] = check_in_time.split(':');
//       const [outHours, outMinutes] = check_out_time.split(':');
      
//       let checkInDate = new Date();
//       checkInDate.setHours(parseInt(inHours), parseInt(inMinutes), 0);
      
//       let checkOutDate = new Date();
//       checkOutDate.setHours(parseInt(outHours), parseInt(outMinutes), 0);
      
//       let diffHours = (checkOutDate - checkInDate) / (1000 * 60 * 60);
      
//       if (diffHours < 0) {
//         diffHours += 24;
//       }
      
//       calculatedWorkHours = parseFloat(diffHours.toFixed(2));
      
//       if (calculatedWorkHours > 8) {
//         calculatedOvertime = calculatedWorkHours - 8;
//       }
//     }

//     // Insert attendance record
//     const result = await pool.query(
//       `
//       INSERT INTO attendance (
//         employee_id,
//         attendance_date,
//         check_in_time,
//         check_out_time,
//         shift_type,
//         work_hours,
//         overtime_hours,
//         latitude,
//         longitude,
//         office_location,
//         remarks,
//         org_id,
//         created_by,
//         created_at,
//         updated_at
//       )
//       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         realEmployeeId,
//         attendance_date,
//         check_in_time || null,
//         check_out_time || null,
//         finalShift,
//         calculatedWorkHours,
//         calculatedOvertime,
//         latitude || null,
//         longitude || null,
//         office_location || null,
//         remarks || null,
//         org_id,
//         created_by || null
//       ]
//     );

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

// /* ===========================
//    ✅ CHECK-IN (Mark Check-in)
// =========================== */

// const checkIn = async (req, res) => {
//   try {
//     console.log("=== CHECK-IN DEBUG ===");
//     console.log("req.body:", req.body);

//     let {
//       employee_id: employeeCode,
//       attendance_date,
//       check_in_time,
//       shift_type: shiftFromRequest,
//       latitude,
//       longitude,
//       office_location,
//       org_id,
//       created_by
//     } = req.body;

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

//     // Fetch employee id only
//     const employeeResult = await pool.query(
//       `SELECT id 
//        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 realEmployeeId = employeeResult.rows[0].id;
//     const finalShift = shiftFromRequest || "General";

//     // Check if attendance exists
//     const existingAttendance = await pool.query(
//       `SELECT id, check_in_time 
//        FROM attendance 
//        WHERE employee_id = $1 
//          AND attendance_date = $2 
//          AND is_deleted = false`,
//       [realEmployeeId, attendance_date]
//     );

//         // In checkIn function, after getting shift details
// if (shiftResult.rows[0] && check_in_time > shiftResult.rows[0].start_time) {
//   const lateMinutes = calculateLateMinutes(check_in_time, shiftResult.rows[0].start_time);
//   // Update with late minutes
// }
//     if (existingAttendance.rows.length > 0) {
//       // Update existing record with check-in
//       if (existingAttendance.rows[0].check_in_time) {
//         return res.status(409).json({
//           success: false,
//           message: "Check-in already marked for today",
//         });
//       }

//       const result = await pool.query(
//         `
//         UPDATE attendance
//         SET check_in_time = $1,
//             latitude = COALESCE($2, latitude),
//             longitude = COALESCE($3, longitude),
//             office_location = COALESCE($4, office_location),
//             shift_type = COALESCE($5, shift_type),
//             updated_by = $6,
//             updated_at = NOW()
//         WHERE id = $7
//         RETURNING *
//         `,
//         [
//           check_in_time || new Date().toLocaleTimeString(),
//           latitude || null,
//           longitude || null,
//           office_location || null,
//           finalShift,
//           created_by || null,
//           existingAttendance.rows[0].id
//         ]
//       );

//       return res.status(200).json({
//         success: true,
//         message: "Check-in marked successfully",
//         data: result.rows[0],
//       });
//     } else {
//       // Create new attendance record
//       const result = await pool.query(
//         `
//         INSERT INTO attendance (
//           employee_id,
//           attendance_date,
//           check_in_time,
//           shift_type,
//           latitude,
//           longitude,
//           office_location,
//           org_id,
//           created_by,
//           created_at,
//           updated_at
//         )
//         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
//         RETURNING *
//         `,
//         [
//           realEmployeeId,
//           attendance_date,
//           check_in_time || new Date().toLocaleTimeString(),
//           finalShift,
//           latitude || null,
//           longitude || null,
//           office_location || null,
//           org_id,
//           created_by || null
//         ]
//       );

//       return res.status(201).json({
//         success: true,
//         message: "Check-in marked successfully",
//         data: result.rows[0],
//       });
//     }
//   } catch (error) {
//     console.error("CHECK-IN ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };


// /* ===========================
//    ✅ CHECK-OUT (Mark Check-out)
// =========================== */

// const checkOut = async (req, res) => {
//   try {
//     console.log("=== CHECK-OUT DEBUG ===");
//     console.log("req.body:", req.body);

//     let {
//       employee_id: employeeCode,
//       attendance_date,
//       check_out_time,
//       org_id,
//       updated_by
//     } = req.body;

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

//     // Fetch real employee id
//     const employeeResult = await pool.query(
//       `SELECT id 
//        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 realEmployeeId = employeeResult.rows[0].id;

//     // Check if attendance exists with check-in
//     const existingAttendance = await pool.query(
//       `SELECT id, check_in_time 
//        FROM attendance 
//        WHERE employee_id = $1 
//          AND attendance_date = $2 
//          AND is_deleted = false`,
//       [realEmployeeId, attendance_date]
//     );

//     // In checkOut function, after confirming check-in exists
// const workHours = calculateWorkHours(oldCheckIn, check_out_time);
// const overtime = workHours > 8 ? workHours - 8 : 0;
// // Update both work_hours and overtime_hours

//     if (existingAttendance.rows.length === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "No check-in found for today. Please check-in first.",
//       });
//     }

//     if (!existingAttendance.rows[0].check_in_time) {
//       return res.status(400).json({
//         success: false,
//         message: "Please check-in before checking out",
//       });
//     }

//     // Update with check-out
//     const result = await pool.query(
//       `
//       UPDATE attendance
//       SET check_out_time = $1,
//           updated_by = $2,
//           updated_at = NOW()
//       WHERE id = $3
//       RETURNING *
//       `,
//       [
//         check_out_time || new Date().toLocaleTimeString(),
//         updated_by || null,
//         existingAttendance.rows[0].id
//       ]
//     );

//     res.status(200).json({
//       success: true,
//       message: "Check-out marked successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("CHECK-OUT ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };



// /* ===========================
//    ✅ GET ATTENDANCE BY ID (with shift details)
// =========================== */

// const getAttendanceById = async (req, res) => {
//   try {
//     const { id } = req.params;
    
//     const result = await pool.query(
//       `
//       SELECT
//         a.*,
//         e.employee_code,
//         e.first_name,
//         e.last_name,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation,
//         e.email,
//         e.phone,
//         s.id as shift_id,
//         s.shift_name,
//         s.shift_code,
//         s.start_time as shift_start_time,
//         s.end_time as shift_end_time,
//         s.total_hours as shift_total_hours,
//         s.working_hours as shift_working_hours,
//         s.lunch_start,
//         s.lunch_end,
//         s.first_break_start,
//         s.first_break_end,
//         s.second_break_start,
//         s.second_break_end
//       FROM attendance a
//       LEFT JOIN employees e ON a.employee_id = e.id
//       LEFT JOIN shifts s ON a.shift_type = s.shift_name AND s.is_deleted = false
//       WHERE a.id = $1 AND a.is_deleted = false
//       `,
//       [id]
//     );
    
//     if (!result.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "Attendance record not found",
//       });
//     }
    
//     res.status(200).json({
//       success: true,
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("GET ATTENDANCE BY ID ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: "Fetch failed",
//     });
//   }
// };
// /* ===========================
//    ✅ UPDATE ATTENDANCE (FIXED - Properly handles status changes)
// =========================== */

// const updateAttendance = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const {
//       attendance_status,
//       check_in_time,
//       check_out_time,
//       shift_type,
//       work_hours,
//       latitude,
//       longitude,
//       office_location,
//       remarks,
//       updated_by,
//       is_late,
//       late_minutes
//     } = req.body;

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

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

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

//     // Build dynamic update query
//     const updates = [];
//     const queryParams = [];
//     let paramIndex = 1;

//     // Handle attendance_status specifically
//     if (attendance_status !== undefined) {
//       updates.push(`attendance_status = $${paramIndex}`);
//       queryParams.push(attendance_status);
//       paramIndex++;
      
//       // Also update is_late based on status
//       if (attendance_status === 'Late') {
//         updates.push(`is_late = $${paramIndex}`);
//         queryParams.push(true);
//         paramIndex++;
//         if (late_minutes !== undefined) {
//           updates.push(`late_minutes = $${paramIndex}`);
//           queryParams.push(late_minutes);
//           paramIndex++;
//         } else {
//           updates.push(`late_minutes = $${paramIndex}`);
//           queryParams.push(30);
//           paramIndex++;
//         }
//       } else if (attendance_status === 'Present') {
//         updates.push(`is_late = $${paramIndex}`);
//         queryParams.push(false);
//         paramIndex++;
//         updates.push(`late_minutes = $${paramIndex}`);
//         queryParams.push(0);
//         paramIndex++;
//       } else if (attendance_status === 'Absent') {
//         updates.push(`is_late = $${paramIndex}`);
//         queryParams.push(false);
//         paramIndex++;
//         updates.push(`late_minutes = $${paramIndex}`);
//         queryParams.push(0);
//         paramIndex++;
//         // Clear check-in/check-out for absent
//         updates.push(`check_in_time = $${paramIndex}`);
//         queryParams.push(null);
//         paramIndex++;
//         updates.push(`check_out_time = $${paramIndex}`);
//         queryParams.push(null);
//         paramIndex++;
//         updates.push(`work_hours = $${paramIndex}`);
//         queryParams.push(0);
//         paramIndex++;
//       }
//     }

//     // Handle other fields
//     if (check_in_time !== undefined) {
//       updates.push(`check_in_time = $${paramIndex}`);
//       queryParams.push(check_in_time);
//       paramIndex++;
//     }

//     if (check_out_time !== undefined) {
//       updates.push(`check_out_time = $${paramIndex}`);
//       queryParams.push(check_out_time);
//       paramIndex++;
//     }

//     if (shift_type !== undefined) {
//       updates.push(`shift_type = $${paramIndex}`);
//       queryParams.push(shift_type);
//       paramIndex++;
//     }

//     if (work_hours !== undefined) {
//       updates.push(`work_hours = $${paramIndex}`);
//       queryParams.push(work_hours);
//       paramIndex++;
//     }

//     if (latitude !== undefined) {
//       updates.push(`latitude = $${paramIndex}`);
//       queryParams.push(latitude);
//       paramIndex++;
//     }

//     if (longitude !== undefined) {
//       updates.push(`longitude = $${paramIndex}`);
//       queryParams.push(longitude);
//       paramIndex++;
//     }

//     if (office_location !== undefined) {
//       updates.push(`office_location = $${paramIndex}`);
//       queryParams.push(office_location);
//       paramIndex++;
//     }

//     if (remarks !== undefined) {
//       updates.push(`remarks = $${paramIndex}`);
//       queryParams.push(remarks);
//       paramIndex++;
//     }

//     if (is_late !== undefined && attendance_status !== 'Late' && attendance_status !== 'Present' && attendance_status !== 'Absent') {
//       updates.push(`is_late = $${paramIndex}`);
//       queryParams.push(is_late);
//       paramIndex++;
//     }

//     if (late_minutes !== undefined && attendance_status !== 'Late') {
//       updates.push(`late_minutes = $${paramIndex}`);
//       queryParams.push(late_minutes);
//       paramIndex++;
//     }

//     // Always update updated_by and updated_at
//     updates.push(`updated_by = $${paramIndex}`);
//     queryParams.push(updated_by || old.updated_by || 'system');
//     paramIndex++;
    
//     updates.push(`updated_at = NOW()`);

//     // Add id as last parameter
//     queryParams.push(id);

//     const query = `
//       UPDATE attendance
//       SET ${updates.join(', ')}
//       WHERE id = $${paramIndex}
//       RETURNING *
//     `;

//     console.log("Update Query:", query);
//     console.log("Query Params:", queryParams);

//     const result = await pool.query(query, queryParams);
    
//     res.status(200).json({
//       success: true,
//       message: "Attendance updated successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("UPDATE ATTENDANCE ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };
// /* ===========================
//    ✅ DELETE ATTENDANCE (Soft Delete)
// =========================== */

// const deleteAttendance = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { updated_by } = req.body;
    
//     const result = await pool.query(
//       `
//       UPDATE attendance
//       SET is_deleted = true,
//           updated_by = COALESCE($1, updated_by),
//           updated_at = NOW()
//       WHERE id = $2 AND is_deleted = false
//       RETURNING id
//       `,
//       [updated_by || null, id]
//     );
    
//     if (result.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Attendance record not found",
//       });
//     }
    
//     res.json({
//       success: true,
//       message: "Attendance deleted successfully",
//     });
//   } catch (err) {
//     console.error("DELETE ATTENDANCE ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: "Delete failed",
//     });
//   }
// };

// /* ===========================
//    ✅ GET ATTENDANCE SUMMARY BY EMPLOYEE
// =========================== */

// const getAttendanceSummary = async (req, res) => {
//   try {
//     const { employee_code, month, year, org_id } = req.query;
    
//     let query = `
//       SELECT
//         e.employee_code,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.shift as employee_shift,
//         COUNT(CASE WHEN a.attendance_status = 'Present' THEN 1 END) as present_days,
//         COUNT(CASE WHEN a.attendance_status = 'Absent' THEN 1 END) as absent_days,
//         COUNT(CASE WHEN a.attendance_status = 'Late' THEN 1 END) as late_days,
//         COUNT(CASE WHEN a.attendance_status = 'Half Day' THEN 1 END) as half_days,
//         COUNT(CASE WHEN a.is_late = true THEN 1 END) as late_marks,
//         SUM(a.late_minutes) as total_late_minutes,
//         SUM(a.overtime_hours) as total_overtime_hours,
//         SUM(a.work_hours) as total_work_hours,
//         ROUND(AVG(a.work_hours), 2) as avg_work_hours
//       FROM employees e
//       LEFT JOIN attendance a ON e.id = a.employee_id AND a.is_deleted = false
//       WHERE e.is_deleted = false
//     `;
    
//     const queryParams = [];
//     let paramIndex = 1;
    
//     if (employee_code) {
//       query += ` AND e.employee_code = $${paramIndex}`;
//       queryParams.push(employee_code);
//       paramIndex++;
//     }
    
//     // if (org_id) {
//     //   query += ` AND a.org_id = $${paramIndex}`;
//     //   queryParams.push(org_id);
//     //   paramIndex++;
//     // }
//     if (org_id) {
//   query += ` AND e.org_id = $${paramIndex}`; // Filter on employees table instead
// }
    
//     if (year) {
//       query += ` AND EXTRACT(YEAR FROM a.attendance_date) = $${paramIndex}`;
//       queryParams.push(year);
//       paramIndex++;
//     }
    
//     if (month) {
//       query += ` AND EXTRACT(MONTH FROM a.attendance_date) = $${paramIndex}`;
//       queryParams.push(month);
//       paramIndex++;
//     }
    
//     query += ` GROUP BY e.id, e.employee_code, e.first_name, e.last_name, e.department, e.shift
//                ORDER BY e.employee_code`;
    
//     const result = await pool.query(query, queryParams);
    
//     res.json({
//       success: true,
//       data: result.rows,
//     });
//   } catch (err) {
//     console.error("GET ATTENDANCE SUMMARY ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };



// /* ===========================
//    ✅ GET TODAY'S ATTENDANCE (with shift details)
// =========================== */

// const getTodayAttendance = async (req, res) => {
//   try {
//     const { org_id } = req.query;
//     const today = new Date().toISOString().split('T')[0];
    
//     let query = `
//       SELECT
//         a.*,
//         e.employee_code,
//         e.first_name,
//         e.last_name,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation,
//         s.id as shift_id,
//         s.shift_name,
//         s.start_time as shift_start_time,
//         s.end_time as shift_end_time,
//         s.total_hours as shift_total_hours,
//         s.working_hours as shift_working_hours,
//         s.lunch_start,
//         s.lunch_end,
//         s.first_break_start,
//         s.first_break_end,
//         s.second_break_start,
//         s.second_break_end
//       FROM attendance a
//       JOIN employees e ON a.employee_id = e.id
//       LEFT JOIN shifts s ON a.shift_type = s.shift_name AND s.is_deleted = false
//       WHERE a.attendance_date = $1 
//         AND a.is_deleted = false
//         AND e.is_deleted = false
//     `;
    
//     const queryParams = [today];
//     let paramIndex = 2;
    
//     if (org_id) {
//       query += ` AND a.org_id = $${paramIndex}`;
//       queryParams.push(org_id);
//       paramIndex++;
//     }
    
//     query += ` ORDER BY a.check_in_time DESC`;
    
//     const result = await pool.query(query, queryParams);
    
//     res.json({
//       success: true,
//       data: result.rows,
//       totalRecords: result.rows.length,
//     });
//   } catch (err) {
//     console.error("GET TODAY'S ATTENDANCE ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };


// /* ===========================
//    ✅ GET LATE ATTENDANCE REPORT (with shift details)
// =========================== */

// const getLateAttendanceReport = async (req, res) => {
//   try {
//     const { start_date, end_date, org_id } = req.query;
    
//     let query = `
//       SELECT
//         a.attendance_date,
//         e.employee_code,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         a.shift_type,
//         a.check_in_time,
//         a.late_minutes,
//         a.remarks,
//         s.start_time as shift_start_time,
//         s.end_time as shift_end_time
//       FROM attendance a
//       JOIN employees e ON a.employee_id = e.id
//       LEFT JOIN shifts s ON a.shift_type = s.shift_name AND s.is_deleted = false
//       WHERE a.is_late = true 
//         AND a.is_deleted = false
//     `;
    
//     const queryParams = [];
//     let paramIndex = 1;
    
//     if (start_date) {
//       query += ` AND a.attendance_date >= $${paramIndex}`;
//       queryParams.push(start_date);
//       paramIndex++;
//     }
    
//     if (end_date) {
//       query += ` AND a.attendance_date <= $${paramIndex}`;
//       queryParams.push(end_date);
//       paramIndex++;
//     }
    
//     if (org_id) {
//       query += ` AND a.org_id = $${paramIndex}`;
//       queryParams.push(org_id);
//       paramIndex++;
//     }
    
//     query += ` ORDER BY a.late_minutes DESC`;
    
//     const result = await pool.query(query, queryParams);
    
//     res.json({
//       success: true,
//       data: result.rows,
//     });
//   } catch (err) {
//     console.error("GET LATE ATTENDANCE REPORT ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };
// /* ===========================
//    ✅ GET ALL ATTENDANCE (with shift details from shifts table)
// =========================== */

// const getAttendance = async (req, res) => {
//   try {
//     const { 
//       page = 1, 
//       limit = 10, 
//       employee_id,
//       start_date,
//       end_date,
//       shift_type,
//       attendance_status,
//       org_id 
//     } = req.query;
    
//     const offset = (page - 1) * limit;
    
//     // Build the WHERE clause
//     let whereConditions = [];
//     let queryParams = [];
//     let paramIndex = 1;
    
//     if (employee_id) {
//       whereConditions.push(`e.employee_code = $${paramIndex}`);
//       queryParams.push(employee_id);
//       paramIndex++;
//     }
    
//     if (shift_type) {
//       whereConditions.push(`a.shift_type = $${paramIndex}`);
//       queryParams.push(shift_type);
//       paramIndex++;
//     }
    
//     if (attendance_status) {
//       whereConditions.push(`a.attendance_status = $${paramIndex}`);
//       queryParams.push(attendance_status);
//       paramIndex++;
//     }
    
//     if (start_date) {
//       whereConditions.push(`a.attendance_date >= $${paramIndex}`);
//       queryParams.push(start_date);
//       paramIndex++;
//     }
    
//     if (end_date) {
//       whereConditions.push(`a.attendance_date <= $${paramIndex}`);
//       queryParams.push(end_date);
//       paramIndex++;
//     }
    
//     if (org_id) {
//       whereConditions.push(`a.org_id = $${paramIndex}`);
//       queryParams.push(org_id);
//       paramIndex++;
//     }
    
//     const whereClause = whereConditions.length > 0 
//       ? `WHERE a.is_deleted = false AND ${whereConditions.join(' AND ')}`
//       : `WHERE a.is_deleted = false`;
    
//     // Get total count
//     let countQuery = `
//       SELECT COUNT(*) as total
//       FROM attendance a
//       JOIN employees e ON a.employee_id = e.id
//       ${whereClause}
//     `;
    
//     console.log("Count Query:", countQuery);
//     console.log("Count Params:", queryParams);
    
//     const countResult = await pool.query(countQuery, queryParams);
//     const totalRecords = parseInt(countResult.rows[0]?.total || 0);
    
//     // Get paginated data with shift details from shifts table
//     const dataQuery = `
//       SELECT
//         a.*,
//         e.employee_code,
//         e.first_name,
//         e.last_name,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation,
//         s.id as shift_id,
//         s.shift_name,
//         s.shift_code,
//         s.start_time as shift_start_time,
//         s.end_time as shift_end_time,
//         s.total_hours as shift_total_hours,
//         s.working_hours as shift_working_hours,
//         s.lunch_start,
//         s.lunch_end,
//         s.first_break_start,
//         s.first_break_end,
//         s.second_break_start,
//         s.second_break_end
//       FROM attendance a
//       JOIN employees e ON a.employee_id = e.id
//       LEFT JOIN shifts s ON a.shift_type = s.shift_name AND s.is_deleted = false
//       ${whereClause}
//       ORDER BY a.attendance_date DESC, a.check_in_time DESC
//       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
//     `;
    
//     queryParams.push(limit, offset);
    
//     console.log("Data Query:", dataQuery);
//     console.log("Data Params:", queryParams);
    
//     const result = await pool.query(dataQuery, queryParams);
    
//     res.json({
//       success: true,
//       data: result.rows,
//       pagination: {
//         currentPage: parseInt(page),
//         totalPages: Math.ceil(totalRecords / limit),
//         totalRecords: totalRecords,
//         recordsPerPage: parseInt(limit)
//       }
//     });
//   } catch (err) {
//     console.error("FETCH ATTENDANCE ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };

// module.exports = {
//   createAttendance,
//   checkIn,
//   checkOut,
//   getAttendance,
//   getAttendanceById,
//   updateAttendance,
//   deleteAttendance,
//   getAttendanceSummary,
//   getTodayAttendance,
//   getLateAttendanceReport,
//   // getEmployeesByShift
// };

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


/* ===========================
   ✅ HELPER FUNCTIONS
=========================== */

const getCurrentTime = () => new Date().toTimeString().split(" ")[0]; // "HH:MM:SS"

const calculateLateMinutes = (checkInTime, shiftStartTime) => {
  if (!checkInTime || !shiftStartTime) return 0;
  const [ciH, ciM] = checkInTime.split(":").map(Number);
  const [ssH, ssM] = shiftStartTime.split(":").map(Number);
  const diff = ciH * 60 + ciM - (ssH * 60 + ssM);
  return diff > 0 ? diff : 0;
};

const calculateWorkHours = (checkInTime, checkOutTime) => {
  if (!checkInTime || !checkOutTime) return 0;
  const [inH, inM] = checkInTime.split(":").map(Number);
  const [outH, outM] = checkOutTime.split(":").map(Number);
  let diff = (outH * 60 + outM - (inH * 60 + inM)) / 60;
  if (diff < 0) diff += 24;
  return parseFloat(diff.toFixed(2));
};

const getEmployeeWithShift = async (employeeCode) => {
  const result = await pool.query(
    `SELECT
       e.id, e.employee_code, e.first_name, e.last_name, e.department,
       e.designation, e.org_id,
       sa.shift_id,
       s.shift_name, s.start_time AS shift_start_time,
       s.end_time AS shift_end_time,
       s.working_hours AS shift_working_hours
     FROM employees e
     LEFT JOIN shift_assignments sa
       ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
     LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
     WHERE e.employee_code = $1 AND e.is_deleted = false`,
    [employeeCode]
  );
  return result.rows[0] || null;
};

/* ===========================
   ✅ CHECK IS HOLIDAY
=========================== */
const checkIsHoliday = async (date, orgId) => {
  try {
    const result = await pool.query(
      `SELECT id FROM holidays
       WHERE holiday_date = $1 AND orgid = $2
       AND is_active = true AND is_deleted = false`,
      [date, orgId]
    );
    return result.rows.length > 0;
  } catch (error) {
    console.error("Error checking holiday:", error);
    return false;
  }
};

/* ===========================
   ✅ CREATE ATTENDANCE
=========================== */
const createAttendance = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    let {
      employee_id: employeeCode,
      attendance_date,
      check_in_time,
      check_out_time,
      attendance_status,
      remarks,
      shift_type,
      shift_start_time: bodyShiftStart,
      shift_end_time: bodyShiftEnd,
      original_shift_id,
      actual_shift_id,
      swap_with_employee_id,
      is_double_shift,
      second_shift_id,
    } = req.body;

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

    const employee = await getEmployeeWithShift(employeeCode);
    if (!employee) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    // ✅ Check holiday
    const isHoliday = await checkIsHoliday(attendance_date, org_id);
    let finalAttendanceStatus = attendance_status;
    if (isHoliday) {
      finalAttendanceStatus = "Holiday";
    }

    const existing = await pool.query(
      `SELECT id FROM attendance
       WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
      [employee.id, attendance_date]
    );
    if (existing.rows.length > 0) {
      return res.status(409).json({
        success: false,
        message: `Attendance already exists for ${attendance_date}`,
        existing: existing.rows[0],
      });
    }

    // ✅ Prefer employee's assigned shift times, fall back to what frontend sent
    const shiftStartTime = employee.shift_start_time || bodyShiftStart || null;
    const shiftEndTime   = employee.shift_end_time   || bodyShiftEnd   || null;

    // ✅ DEFAULT check_in to shift_start_time, check_out to shift_end_time
    // Only override if user explicitly provided values
    const finalCheckIn  = check_in_time  || shiftStartTime || null;
    const finalCheckOut = check_out_time || shiftEndTime   || null;

    // ✅ Fetch shift_type from DB (employee's assigned shift name)
    const finalShiftType = employee.shift_name || shift_type || "General";

    let lateMinutes = 0;
    let isLate = false;
    if (finalCheckIn && shiftStartTime) {
      lateMinutes = calculateLateMinutes(finalCheckIn, shiftStartTime);
      isLate = lateMinutes > 15;
    }

    let workHours = 0;
    let overtimeHours = 0;
    if (finalCheckIn && finalCheckOut) {
      workHours = calculateWorkHours(finalCheckIn, finalCheckOut);
      const shiftHours = employee.shift_working_hours || 8;
      overtimeHours = workHours > shiftHours ? parseFloat((workHours - shiftHours).toFixed(2)) : 0;
    }

    const finalStatus = finalAttendanceStatus || (isLate ? "Late" : "Present");

    const result = await pool.query(
      `INSERT INTO attendance (
         employee_id, attendance_date, check_in_time, check_out_time,
         shift_type, shift_start_time, shift_end_time,
         work_hours, overtime_hours, is_late, late_minutes,
         attendance_status, original_shift_id, actual_shift_id,
         swap_with_employee_id, is_double_shift, second_shift_id,
         remarks, org_id, created_by, created_at, updated_at
       ) VALUES (
         $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,NOW(),NOW()
       ) RETURNING *`,
      [
        employee.id, attendance_date, finalCheckIn, finalCheckOut,
        finalShiftType, shiftStartTime, shiftEndTime,
        workHours, overtimeHours, isLate, lateMinutes,
        finalStatus,
        original_shift_id || employee.shift_id || null,
        actual_shift_id || employee.shift_id || null,
        swap_with_employee_id || null,
        is_double_shift || false,
        second_shift_id || null,
        remarks || null, org_id, userId || null,
      ]
    );

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

/* ===========================
   ✅ CHECK-IN
=========================== */
const checkIn = async (req, res) => {
  const org_id = req.session.user.orgId;
  const user_id = req.session.user.id;
  try {
    let {
      employee_id: employeeCode,
      attendance_date,
      check_in_time,
      latitude,
      longitude,
      office_location,
    } = req.body;

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

    const employee = await getEmployeeWithShift(employeeCode);
    if (!employee) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    // ✅ Default check_in_time to shift start time if not provided
    const shiftStartTime = employee.shift_start_time;
    const finalCheckIn = check_in_time || shiftStartTime || getCurrentTime();

    const lateMinutes = shiftStartTime ? calculateLateMinutes(finalCheckIn, shiftStartTime) : 0;
    const isLate = lateMinutes > 15;
    const status = isLate ? "Late" : "Present";

    const existing = await pool.query(
      `SELECT id, check_in_time FROM attendance
       WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
      [employee.id, attendance_date]
    );

    if (existing.rows.length > 0) {
      if (existing.rows[0].check_in_time) {
        return res.status(409).json({ success: false, message: "Check-in already marked for today" });
      }
      const result = await pool.query(
        `UPDATE attendance SET
           check_in_time = $1, is_late = $2, late_minutes = $3,
           attendance_status = $4,
           latitude = COALESCE($5, latitude),
           longitude = COALESCE($6, longitude),
           office_location = COALESCE($7, office_location),
           updated_by = $8, updated_at = NOW()
         WHERE id = $9 RETURNING *`,
        [finalCheckIn, isLate, lateMinutes, status,
         latitude || null, longitude || null, office_location || null,
         user_id || null, existing.rows[0].id]
      );
      return res.status(200).json({ success: true, message: "Check-in marked", data: result.rows[0] });
    }

    const isHoliday = await checkIsHoliday(attendance_date, org_id);

    // ✅ Fetch shift_type from DB
    const finalShiftType = employee.shift_name || "General";

    const result = await pool.query(
      `INSERT INTO attendance (
         employee_id, attendance_date, check_in_time, shift_type,
         shift_start_time, shift_end_time, is_late, late_minutes,
         attendance_status, latitude, longitude, office_location,
         original_shift_id, actual_shift_id,
         org_id, created_by, created_at, updated_at
       ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,NOW(),NOW())
       RETURNING *`,
      [
        employee.id, attendance_date, finalCheckIn,
        finalShiftType,
        employee.shift_start_time || null,
        employee.shift_end_time || null,
        isHoliday ? false : isLate,
        isHoliday ? 0 : lateMinutes,
        isHoliday ? "Holiday" : status,
        latitude || null, longitude || null, office_location || null,
        employee.shift_id || null, employee.shift_id || null,
        org_id, user_id || null,
      ]
    );
    res.status(201).json({ success: true, message: "Check-in marked", data: result.rows[0] });
  } catch (error) {
    console.error("CHECK-IN ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ CHECK-OUT (Manager triggered)
=========================== */
const checkOut = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    let {
      employee_id: employeeCode,
      attendance_date,
      check_out_time,
      updated_by,
    } = req.body;

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

    const employee = await getEmployeeWithShift(employeeCode);
    if (!employee) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const existing = await pool.query(
      `SELECT a.id, a.check_in_time, a.is_double_shift,
              s.working_hours AS shift_working_hours
       FROM attendance a
       LEFT JOIN shift_assignments sa
         ON a.employee_id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
       WHERE a.employee_id = $1 AND a.attendance_date = $2 AND a.is_deleted = false`,
      [employee.id, attendance_date]
    );

    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "No attendance record found for today" });
    }

    const record = existing.rows[0];
    if (record.is_checked_out) {
      return res.status(409).json({ success: false, message: "Employee already checked out" });
    }

    // ✅ Default check_out_time to shift end time if not provided
    const finalCheckOut = check_out_time || employee.shift_end_time || getCurrentTime();
    const workHours = record.check_in_time
      ? calculateWorkHours(record.check_in_time, finalCheckOut)
      : 0;

    let shiftHours = record.shift_working_hours || 8;
    if (record.is_double_shift) shiftHours = shiftHours * 2;

    const overtime = workHours > shiftHours
      ? parseFloat((workHours - shiftHours).toFixed(2))
      : 0;

    const result = await pool.query(
      `UPDATE attendance SET
         check_out_time = $1, work_hours = $2, overtime_hours = $3,
         is_checked_out = true, checked_out_at = NOW(),
         updated_by = $4, updated_at = NOW()
       WHERE id = $5 RETURNING *`,
      [finalCheckOut, workHours, overtime, updated_by || userId || null, record.id]
    );

    res.status(200).json({
      success: true,
      message: "Check-out marked successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CHECK-OUT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ INDIVIDUAL CHECKOUT (by attendance ID)
=========================== */
const individualCheckout = async (req, res) => {
  try {
    const { id } = req.params;
    const { check_out_time, updated_by } = req.body;

    const existing = await pool.query(
      `SELECT a.id, a.check_in_time, a.is_checked_out, a.is_double_shift,
              s.working_hours AS shift_working_hours,
              s.end_time AS shift_end_time
       FROM attendance a
       LEFT JOIN shift_assignments sa
         ON a.employee_id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
       WHERE a.id = $1 AND a.is_deleted = false`,
      [id]
    );

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

    const record = existing.rows[0];
    if (record.is_checked_out) {
      return res.status(409).json({ success: false, message: "Already checked out" });
    }

    // ✅ Default to shift end time if not provided
    const finalCheckOut = check_out_time || record.shift_end_time || getCurrentTime();
    const workHours = record.check_in_time
      ? calculateWorkHours(record.check_in_time, finalCheckOut)
      : 0;

    let shiftHours = record.shift_working_hours || 8;
    if (record.is_double_shift) shiftHours = shiftHours * 2;
    const overtime = workHours > shiftHours ? parseFloat((workHours - shiftHours).toFixed(2)) : 0;

    const result = await pool.query(
      `UPDATE attendance SET
         check_out_time = $1, work_hours = $2, overtime_hours = $3,
         is_checked_out = true, checked_out_at = NOW(),
         updated_by = $4, updated_at = NOW()
       WHERE id = $5 RETURNING *`,
      [finalCheckOut, workHours, overtime, updated_by || null, id]
    );

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

/* ===========================
   ✅ GET TODAY'S ATTENDANCE (per shift view)
=========================== */
const getTodayAttendance = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { shift_id } = req.query;
    if (!org_id) {
      return res.status(400).json({ success: false, message: "org_id is required" });
    }

    const today = new Date().toISOString().split("T")[0];

    let empQuery = `
      SELECT e.id, e.employee_code, e.first_name, e.last_name,
             e.department, e.designation,
             sa.shift_id,
             COALESCE(s.shift_name, 'General') AS shift_name,
             s.start_time AS shift_start_time,
             s.end_time AS shift_end_time,
             s.working_hours AS shift_working_hours
      FROM employees e
      LEFT JOIN shift_assignments sa
        ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
      LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
      WHERE e.org_id = $1 AND e.is_deleted = false`;

    const empParams = [org_id];
    if (shift_id) {
      empQuery += ` AND sa.shift_id = $2`;
      empParams.push(shift_id);
    }

    const employeesResult = await pool.query(empQuery, empParams);

    const attendanceResult = await pool.query(
      `SELECT * FROM attendance
       WHERE attendance_date = $1 AND org_id = $2 AND is_deleted = false`,
      [today, org_id]
    );

    const attendanceMap = new Map();
    attendanceResult.rows.forEach((a) => attendanceMap.set(a.employee_id, a));

    const present = [];
    const late = [];
    const absent = [];
    const other = [];

    for (const emp of employeesResult.rows) {
      const attendance = attendanceMap.get(emp.id);
      const status = attendance ? attendance.attendance_status : "Present";

      const employeeData = {
        id: emp.id,
        employee_code: emp.employee_code,
        employee_name: `${emp.first_name} ${emp.last_name}`,
        department: emp.department,
        designation: emp.designation,
        shift_id: emp.shift_id,
        shift_name: emp.shift_name,
        shift_start_time: attendance?.shift_start_time || emp.shift_start_time,
        shift_end_time: attendance?.shift_end_time || emp.shift_end_time,
        check_in_time: attendance?.check_in_time || null,
        check_out_time: attendance?.check_out_time || null,
        work_hours: attendance?.work_hours || null,
        overtime_hours: attendance?.overtime_hours || 0,
        late_minutes: attendance?.late_minutes || 0,
        is_late: attendance?.is_late || false,
        is_checked_out: attendance?.is_checked_out || false,
        is_double_shift: attendance?.is_double_shift || false,
        status: status,
        attendance_id: attendance?.id || null,
        remarks: attendance?.remarks || null,
      };

      switch (status) {
        case "Present":
          present.push(employeeData);
          break;
        case "Late":
          late.push(employeeData);
          break;
        case "Absent":
          absent.push(employeeData);
          break;
        case "Cross Shift":
          present.push({ ...employeeData, badge: "Cross Shift" });
          break;
        case "Double Shift":
          present.push({ ...employeeData, badge: "Double Shift" });
          break;
        case "Half Day":
          present.push({ ...employeeData, badge: "Half Day" });
          break;
        case "Shift Swap":
          present.push({ ...employeeData, badge: "Shift Swap" });
          break;
        case "Holiday":
        case "Leave":
          other.push(employeeData);
          break;
        default:
          present.push(employeeData);
      }
    }

    res.json({
      success: true,
      data: {
        present,
        late,
        absent,
        other,
        total_employees: employeesResult.rows.length,
        date: today,
        counts: {
          present: present.length,
          late: late.length,
          absent: absent.length,
          other: other.length,
        },
      },
    });
  } catch (error) {
    console.error("GET TODAY ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ UPDATE INDIVIDUAL STATUS
=========================== */
const updateIndividualStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const {
      attendance_status,
      late_minutes,
      original_shift_id,
      actual_shift_id,
      swap_with_employee_id,
      is_double_shift,
      second_shift_id,
      remarks,
      updated_by,
    } = req.body;

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

    const existing = await pool.query(
      `SELECT * FROM attendance WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Attendance record not found" });
    }

    const old = existing.rows[0];
    const updates = [];
    const params = [];
    let i = 1;

    updates.push(`attendance_status = $${i++}`);
    params.push(attendance_status);

    switch (attendance_status) {
      case "Late":
        updates.push(`is_late = $${i++}`); params.push(true);
        updates.push(`late_minutes = $${i++}`); params.push(late_minutes ?? 30);
        break;
      case "Present":
        updates.push(`is_late = $${i++}`); params.push(false);
        updates.push(`late_minutes = $${i++}`); params.push(0);
        break;
      case "Absent":
        updates.push(`is_late = $${i++}`); params.push(false);
        updates.push(`late_minutes = $${i++}`); params.push(0);
        updates.push(`check_in_time = $${i++}`); params.push(null);
        updates.push(`check_out_time = $${i++}`); params.push(null);
        updates.push(`work_hours = $${i++}`); params.push(0);
        updates.push(`is_checked_out = $${i++}`); params.push(false);
        break;
      case "Cross Shift":
        if (actual_shift_id) {
          updates.push(`actual_shift_id = $${i++}`); params.push(actual_shift_id);
        }
        if (original_shift_id) {
          updates.push(`original_shift_id = $${i++}`); params.push(original_shift_id);
        }
        updates.push(`is_late = $${i++}`); params.push(false);
        break;
      case "Shift Swap":
        if (swap_with_employee_id) {
          updates.push(`swap_with_employee_id = $${i++}`); params.push(swap_with_employee_id);
        }
        updates.push(`is_late = $${i++}`); params.push(false);
        break;
      case "Double Shift":
        updates.push(`is_double_shift = $${i++}`); params.push(true);
        if (second_shift_id) {
          updates.push(`second_shift_id = $${i++}`); params.push(second_shift_id);
        }
        updates.push(`is_late = $${i++}`); params.push(false);
        if (old.check_in_time && old.check_out_time) {
          const baseHours = calculateWorkHours(old.check_in_time, old.check_out_time);
          const doubleHours = parseFloat((baseHours * 2).toFixed(2));
          updates.push(`work_hours = $${i++}`); params.push(doubleHours);
        }
        break;
      case "Half Day":
        if (old.check_in_time && old.check_out_time) {
          const hrs = calculateWorkHours(old.check_in_time, old.check_out_time);
          updates.push(`work_hours = $${i++}`); params.push(hrs);
        }
        break;
      default:
        updates.push(`is_late = $${i++}`); params.push(false);
    }

    if (remarks !== undefined) {
      updates.push(`remarks = $${i++}`); params.push(remarks);
    }
    updates.push(`updated_by = $${i++}`); params.push(updated_by || null);
    updates.push(`updated_at = NOW()`);
    params.push(id);

    const result = await pool.query(
      `UPDATE attendance SET ${updates.join(", ")} WHERE id = $${i} RETURNING *`,
      params
    );

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

/* ===========================
   ✅ BULK MARK ATTENDANCE
=========================== */
// const bulkMarkAttendance = async (req, res) => {
//   const org_id = req.session.user.orgId;
//   const userId = req.session.user.id;
//   try {
//     const { employee_ids, status, date, updated_by } = req.body;

//     if (!employee_ids?.length || !status || !date || !org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "employee_ids, status, date, org_id required"
//       });
//     }

//     const validStatuses = ["Present", "Absent", "Late", "Half Day", "Leave", "Cross Shift", "Double Shift", "Shift Swap"];
//     if (!validStatuses.includes(status)) {
//       return res.status(400).json({
//         success: false,
//         message: `Invalid status. Must be one of: ${validStatuses.join(", ")}`
//       });
//     }

//     let isHoliday = false;
//     try {
//       const holidayCheck = await pool.query(
//         `SELECT id FROM holidays WHERE holiday_date = $1 AND orgid = $2 AND is_active = true AND is_deleted = false`,
//         [date, org_id]
//       );
//       isHoliday = holidayCheck.rows.length > 0;
//     } catch (holidayError) {
//       console.error("Error checking holiday:", holidayError);
//     }

//     const finalStatus = isHoliday ? "Holiday" : status;

//     let successCount = 0;
//     let failCount = 0;
//     const results = [];

//     for (const employeeCode of employee_ids) {
//       try {
//         const empResult = await pool.query(
//           `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
//           [employeeCode]
//         );

//         if (empResult.rows.length === 0) {
//           failCount++;
//           results.push({ employee_code: employeeCode, success: false, message: "Employee not found" });
//           continue;
//         }

//         const empId = empResult.rows[0].id;

//         const existing = await pool.query(
//           `SELECT id, attendance_status, is_checked_out FROM attendance
//            WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
//           [empId, date]
//         );

//         if (existing.rows.length > 0 && existing.rows[0].is_checked_out === true) {
//           failCount++;
//           results.push({
//             employee_code: employeeCode,
//             success: false,
//             message: "Employee already checked out, cannot modify"
//           });
//           continue;
//         }

//         if (existing.rows.length > 0) {
//           let updateQuery = "";
//           let queryParams = [];

//           switch(finalStatus) {
//             case "Absent":
//               updateQuery = `
//                 UPDATE attendance
//                 SET attendance_status = $1, updated_by = $2, updated_at = NOW(),
//                     check_in_time = NULL, check_out_time = NULL, work_hours = 0,
//                     is_late = false, late_minutes = 0, is_checked_out = false
//                 WHERE id = $3
//               `;
//               queryParams = [finalStatus, updated_by || null, existing.rows[0].id];
//               break;
//             case "Late":
//               updateQuery = `
//                 UPDATE attendance
//                 SET attendance_status = $1, updated_by = $2, updated_at = NOW(),
//                     is_late = true, late_minutes = 30
//                 WHERE id = $3
//               `;
//               queryParams = [finalStatus, updated_by || null, existing.rows[0].id];
//               break;
//             default:
//               updateQuery = `
//                 UPDATE attendance
//                 SET attendance_status = $1, updated_by = $2, updated_at = NOW(),
//                     is_late = false, late_minutes = 0
//                 WHERE id = $3
//               `;
//               queryParams = [finalStatus, updated_by || null, existing.rows[0].id];
//           }

//           await pool.query(updateQuery, queryParams);
//         } else {
//           // ✅ For new inserts, fetch shift info to set check_in/check_out defaults
//           const empShiftResult = await pool.query(
//             `SELECT s.shift_name, s.start_time, s.end_time, sa.shift_id
//              FROM employees e
//              LEFT JOIN shift_assignments sa ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
//              LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
//              WHERE e.id = $1`,
//             [empId]
//           );
//           const shiftInfo = empShiftResult.rows[0] || {};

//           await pool.query(
//             `INSERT INTO attendance (
//               employee_id, attendance_date, attendance_status,
//               shift_type, shift_start_time, shift_end_time,
//               check_in_time, check_out_time,
//               original_shift_id, actual_shift_id,
//               org_id, created_by, created_at, updated_at
//             ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW(), NOW())`,
//             [
//               empId, date, finalStatus,
//               shiftInfo.shift_name || "General",
//               shiftInfo.start_time || null,
//               shiftInfo.end_time || null,
//               // ✅ Default check_in to shift start, check_out to shift end
//               finalStatus !== "Absent" ? (shiftInfo.start_time || null) : null,
//               finalStatus !== "Absent" ? (shiftInfo.end_time || null) : null,
//               shiftInfo.shift_id || null,
//               shiftInfo.shift_id || null,
//               org_id, updated_by || userId || null
//             ]
//           );
//         }

//         successCount++;
//         results.push({ employee_code: employeeCode, success: true });

//       } catch (err) {
//         console.error(`Error processing ${employeeCode}:`, err.message);
//         failCount++;
//         results.push({ employee_code: employeeCode, success: false, message: err.message });
//       }
//     }

//     res.json({
//       success: true,
//       message: `${successCount} marked as ${finalStatus}, ${failCount} failed`,
//       data: { success_count: successCount, fail_count: failCount, results },
//     });

//   } catch (error) {
//     console.error("BULK MARK ATTENDANCE ERROR:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };
/* ===========================
   ✅ BULK MARK ATTENDANCE - COMPLETE FIX
=========================== */
const bulkMarkAttendance = async (req, res) => {
  const org_id = req.session.user?.orgId;
  const userId = req.session.user?.id;
  
  console.log("=== BULK MARK ATTENDANCE START ===");
  console.log("Request body:", JSON.stringify(req.body, null, 2));
  
  try {
    const { employee_ids, status, date, updated_by } = req.body;

    // Validation
    if (!employee_ids?.length || !status || !date || !org_id) {
      return res.status(400).json({ 
        success: false, 
        message: "employee_ids, status, date, org_id required" 
      });
    }

    const validStatuses = ["Present", "Absent", "Late", "Half Day", "Leave", "Cross Shift", "Double Shift", "Shift Swap"];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({ 
        success: false, 
        message: `Invalid status. Must be one of: ${validStatuses.join(", ")}` 
      });
    }

    // Check if it's a holiday
    let isHoliday = false;
    try {
      const holidayCheck = await pool.query(
        `SELECT id FROM holidays WHERE holiday_date = $1 AND orgid = $2 AND is_active = true AND is_deleted = false`,
        [date, org_id]
      );
      isHoliday = holidayCheck.rows.length > 0;
      console.log(`Holiday check: ${isHoliday}`);
    } catch (holidayError) {
      console.error("Error checking holiday:", holidayError);
    }
    
    const finalStatus = isHoliday ? "Holiday" : status;

    let successCount = 0;
    let failCount = 0;
    const results = [];

    for (const employeeCode of employee_ids) {
      try {
        // Get employee with their shift information
        const employeeWithShift = await getEmployeeWithShift(employeeCode);
        
        if (!employeeWithShift) {
          failCount++;
          results.push({ employee_code: employeeCode, success: false, message: "Employee not found" });
          continue;
        }

        const empId = employeeWithShift.id;
        
        // Check if attendance exists
        const existing = await pool.query(
          `SELECT id, attendance_status, is_checked_out, check_in_time, check_out_time 
           FROM attendance 
           WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
          [empId, date]
        );

        // Skip if already checked out
        if (existing.rows.length > 0 && existing.rows[0].is_checked_out === true) {
          failCount++;
          results.push({ 
            employee_code: employeeCode, 
            success: false, 
            message: "Employee already checked out, cannot modify" 
          });
          continue;
        }

        // Get shift times
        const shiftStartTime = employeeWithShift.shift_start_time;
        const shiftEndTime = employeeWithShift.shift_end_time;
        const shiftName = employeeWithShift.shift_name || "General";
        const shiftId = employeeWithShift.shift_id;

        // Calculate times based on status
        let checkInTime = null;
        let checkOutTime = null;
        let workHours = 0;
        let isLate = false;
        let lateMinutes = 0;

        if (finalStatus === "Present" || finalStatus === "Late") {
          // For Present/Late, use shift times
          checkInTime = shiftStartTime;
          checkOutTime = shiftEndTime;
          
          if (checkInTime && shiftStartTime) {
            lateMinutes = calculateLateMinutes(checkInTime, shiftStartTime);
            isLate = lateMinutes > 15;
          }
          
          if (checkInTime && checkOutTime) {
            workHours = calculateWorkHours(checkInTime, checkOutTime);
          }
        } else if (finalStatus === "Absent") {
          // For Absent, set times to NULL
          checkInTime = null;
          checkOutTime = null;
          workHours = 0;
          isLate = false;
          lateMinutes = 0;
        }

        if (existing.rows.length > 0) {
          // UPDATE existing record
          const updateQuery = `
            UPDATE attendance 
            SET 
              attendance_status = $1, 
              updated_by = $2, 
              updated_at = NOW(),
              check_in_time = $3,
              check_out_time = $4,
              work_hours = $5,
              is_late = $6,
              late_minutes = $7,
              shift_type = $8,
              shift_start_time = $9,
              shift_end_time = $10,
              original_shift_id = $11,
              actual_shift_id = $12
            WHERE id = $13
            RETURNING *
          `;
          
          const queryParams = [
            finalStatus, 
            updated_by || userId || null,
            checkInTime,
            checkOutTime,
            workHours,
            isLate,
            lateMinutes,
            shiftName,
            shiftStartTime,
            shiftEndTime,
            shiftId,
            shiftId,
            existing.rows[0].id
          ];
          
          const result = await pool.query(updateQuery, queryParams);
          console.log(`Updated attendance for ${employeeCode}:`, result.rows[0]);
          
        } else {
          // INSERT new record
          const insertQuery = `
            INSERT INTO attendance (
              employee_id, attendance_date, attendance_status,
              check_in_time, check_out_time, work_hours,
              is_late, late_minutes,
              shift_type, shift_start_time, shift_end_time,
              original_shift_id, actual_shift_id,
              org_id, created_by, created_at, updated_at
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NOW(), NOW())
            RETURNING *
          `;
          
          const insertParams = [
            empId, date, finalStatus,
            checkInTime,
            checkOutTime,
            workHours,
            isLate,
            lateMinutes,
            shiftName,
            shiftStartTime,
            shiftEndTime,
            shiftId,
            shiftId,
            org_id,
            updated_by || userId || null
          ];
          
          const result = await pool.query(insertQuery, insertParams);
          console.log(`Inserted attendance for ${employeeCode}:`, result.rows[0]);
        }
        
        successCount++;
        results.push({ 
          employee_code: employeeCode, 
          success: true,
          status: finalStatus,
          check_in: checkInTime,
          check_out: checkOutTime
        });
        
      } catch (err) {
        console.error(`Error processing ${employeeCode}:`, err.message);
        failCount++;
        results.push({ employee_code: employeeCode, success: false, message: err.message });
      }
    }

    res.json({
      success: true,
      message: `${successCount} marked as ${finalStatus}, ${failCount} failed`,
      data: { success_count: successCount, fail_count: failCount, results },
    });
    
  } catch (error) {
    console.error("BULK MARK ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};
/* ===========================
   ✅ BULK CHECKOUT
=========================== */
const bulkCheckout = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const { employee_ids, date, checkout_time, updated_by } = req.body;

    if (!employee_ids?.length || !date || !org_id) {
      return res.status(400).json({ success: false, message: "employee_ids, date, org_id required" });
    }

    let successCount = 0;
    let failCount = 0;
    const results = [];

    for (const employeeCode of employee_ids) {
      try {
        const empResult = await pool.query(
          `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
          [employeeCode]
        );
        if (empResult.rows.length === 0) {
          failCount++;
          results.push({ employee_code: employeeCode, success: false, message: "Not found" });
          continue;
        }

        const empId = empResult.rows[0].id;
        const attResult = await pool.query(
          `SELECT a.id, a.check_in_time, a.is_checked_out, a.is_double_shift,
                  s.working_hours AS shift_working_hours,
                  s.end_time AS shift_end_time
           FROM attendance a
           LEFT JOIN shift_assignments sa
             ON a.employee_id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
           LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
           WHERE a.employee_id = $1 AND a.attendance_date = $2 AND a.is_deleted = false`,
          [empId, date]
        );

        if (attResult.rows.length === 0 || attResult.rows[0].is_checked_out) {
          failCount++;
          results.push({
            employee_code: employeeCode,
            success: false,
            message: attResult.rows.length === 0 ? "No attendance record" : "Already checked out",
          });
          continue;
        }

        const rec = attResult.rows[0];
        // ✅ Default to shift end time if no checkout_time provided
        const finalCheckoutTime = checkout_time || rec.shift_end_time || getCurrentTime();

        const workHours = rec.check_in_time
          ? calculateWorkHours(rec.check_in_time, finalCheckoutTime)
          : 0;
        let shiftHours = rec.shift_working_hours || 8;
        if (rec.is_double_shift) shiftHours *= 2;
        const overtime = workHours > shiftHours ? parseFloat((workHours - shiftHours).toFixed(2)) : 0;

        await pool.query(
          `UPDATE attendance SET
             check_out_time = $1, work_hours = $2, overtime_hours = $3,
             is_checked_out = true, checked_out_at = NOW(),
             updated_by = $4, updated_at = NOW()
           WHERE id = $5`,
          [finalCheckoutTime, workHours, overtime, updated_by || userId || null, rec.id]
        );

        successCount++;
        results.push({ employee_code: employeeCode, success: true, work_hours: workHours });
      } catch (err) {
        failCount++;
        results.push({ employee_code: employeeCode, success: false, message: err.message });
      }
    }

    res.json({
      success: true,
      message: `${successCount} checked out, ${failCount} failed`,
      data: { success_count: successCount, fail_count: failCount, results },
    });
  } catch (error) {
    console.error("BULK CHECKOUT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// /* ===========================
//    ✅ GET ALL ATTENDANCE (with filters including date)
// =========================== */
// const getAttendance = async (req, res) => {
//   try {
//     const org_id = req.session.user?.orgId;
//     const {
//       page = 1, limit = 10,
//       employee_id, start_date, end_date,
//       shift_type, attendance_status,
//       date, // ✅ single date filter from frontend date picker
//     } = req.query;

//     if (!org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Organization ID not found in session"
//       });
//     }

//     const offset = (page - 1) * limit;
//     const conditions = ["a.is_deleted = false", "e.is_deleted = false", "a.org_id = $1"];
//     const params = [org_id];
//     let idx = 2;

//     if (employee_id) {
//       conditions.push(`e.employee_code = $${idx++}`);
//       params.push(employee_id);
//     }
//     if (shift_type) {
//       conditions.push(`COALESCE(s.shift_name, a.shift_type, 'General') = $${idx++}`);
//       params.push(shift_type);
//     }
//     if (attendance_status) {
//       conditions.push(`a.attendance_status = $${idx++}`);
//       params.push(attendance_status);
//     }
//     // ✅ Single date filter (from frontend date picker)
//     if (date) {
//       conditions.push(`a.attendance_date = $${idx++}`);
//       params.push(date);
//     } else {
//       // date range filters (existing behaviour)
//       if (start_date) {
//         conditions.push(`a.attendance_date >= $${idx++}`);
//         params.push(start_date);
//       }
//       if (end_date) {
//         conditions.push(`a.attendance_date <= $${idx++}`);
//         params.push(end_date);
//       }
//     }

//     const where = `WHERE ${conditions.join(" AND ")}`;

//     const countResult = await pool.query(
//       `SELECT COUNT(*) AS total FROM attendance a
//        JOIN employees e ON a.employee_id = e.id
//        ${where}`,
//       params
//     );
//     const total = parseInt(countResult.rows[0].total);

//     const dataResult = await pool.query(
//       `SELECT
//          a.*,
//          e.employee_code, e.first_name, e.last_name,
//          CONCAT(e.first_name,' ',e.last_name) AS employee_name,
//          e.department, e.designation, e.email, e.phone,
//          COALESCE(s.shift_name, a.shift_type, 'General') AS shift_type,
//          s.id AS shift_id, s.shift_name, s.shift_code,
//          s.start_time AS shift_start, s.end_time AS shift_end,
//          s.working_hours AS shift_working_hours
//        FROM attendance a
//        JOIN employees e ON a.employee_id = e.id
//        LEFT JOIN shift_assignments sa
//          ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
//        LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
//        ${where}
//        ORDER BY a.attendance_date DESC, a.created_at DESC
//        LIMIT $${idx++} OFFSET $${idx++}`,
//       [...params, parseInt(limit), offset]
//     );

//     res.json({
//       success: true,
//       data: dataResult.rows,
//       pagination: {
//         currentPage: parseInt(page),
//         totalPages: Math.ceil(total / limit),
//         totalRecords: total,
//         recordsPerPage: parseInt(limit),
//       },
//     });
//   } catch (error) {
//     console.error("GET ATTENDANCE ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//       stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
//     });
//   }
// };
const getAttendance = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const {
      page = 1, limit = 10,
      employee_id, start_date, end_date,
      shift_type, attendance_status,
      date,
    } = req.query;

    if (!org_id) {
      return res.status(400).json({
        success: false,
        message: "Organization ID not found in session"
      });
    }

    const offset = (page - 1) * limit;
    const conditions = ["a.is_deleted = false", "e.is_deleted = false", "a.org_id = $1"];
    const params = [org_id];
    let idx = 2;

    if (employee_id) {
      conditions.push(`e.employee_code = $${idx++}`);
      params.push(employee_id);
    }
    if (shift_type) {
      conditions.push(`COALESCE(s.shift_name, a.shift_type, 'General') = $${idx++}`);
      params.push(shift_type);
    }
    if (attendance_status) {
      conditions.push(`a.attendance_status = $${idx++}`);
      params.push(attendance_status);
    }
    if (date) {
      conditions.push(`a.attendance_date = $${idx++}`);
      params.push(date);
    } else {
      if (start_date) {
        conditions.push(`a.attendance_date >= $${idx++}`);
        params.push(start_date);
      }
      if (end_date) {
        conditions.push(`a.attendance_date <= $${idx++}`);
        params.push(end_date);
      }
    }

    const where = `WHERE ${conditions.join(" AND ")}`;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total FROM attendance a
       JOIN employees e ON a.employee_id = e.id
       ${where}`,
      params
    );
    const total = parseInt(countResult.rows[0].total);

    const dataResult = await pool.query(
      `SELECT
         a.*,
         e.employee_code, e.first_name, e.last_name,
         CONCAT(e.first_name,' ',e.last_name) AS employee_name,
         e.department, e.designation, e.email, e.phone,
         -- ✅ IMPORTANT: Get shift name from the shifts table
         COALESCE(s.shift_name, a.shift_type, 'General') AS shift_name,
         s.id AS shift_id, s.shift_code,
         s.start_time AS shift_start, s.end_time AS shift_end,
         s.working_hours AS shift_working_hours
       FROM attendance a
       JOIN employees e ON a.employee_id = e.id
       LEFT JOIN shift_assignments sa
         ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
       ${where}
       ORDER BY a.attendance_date DESC, a.created_at DESC
       LIMIT $${idx++} OFFSET $${idx++}`,
      [...params, parseInt(limit), offset]
    );

    // ✅ Transform the data to include shift_name
    const transformedData = dataResult.rows.map(row => ({
      ...row,
      shift: row.shift_name // Add this alias for frontend
    }));

    res.json({
      success: true,
      data: transformedData,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / limit),
        totalRecords: total,
        recordsPerPage: parseInt(limit),
      },
    });
  } catch (error) {
    console.error("GET ATTENDANCE ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
      stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
    });
  }
};


/* ===========================
   ✅ GET ATTENDANCE BY ID
=========================== */
const getAttendanceById = async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(
      `SELECT
         a.*,
         e.employee_code, e.first_name, e.last_name,
         CONCAT(e.first_name,' ',e.last_name) AS employee_name,
         e.department, e.designation, e.email, e.phone,
         s.id AS shift_id, s.shift_name, s.shift_code,
         s.start_time, s.end_time, s.working_hours,
         s.lunch_start, s.lunch_end,
         os.shift_name AS original_shift_name,
         as2.shift_name AS actual_shift_name,
         ss.shift_name AS second_shift_name,
         CONCAT(sw.first_name,' ',sw.last_name) AS swap_employee_name
       FROM attendance a
       LEFT JOIN employees e ON a.employee_id = e.id
       LEFT JOIN shift_assignments sa
         ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id
       LEFT JOIN shifts os ON a.original_shift_id = os.id
       LEFT JOIN shifts as2 ON a.actual_shift_id = as2.id
       LEFT JOIN shifts ss ON a.second_shift_id = ss.id
       LEFT JOIN employees sw ON a.swap_with_employee_id = sw.id
       WHERE a.id = $1 AND a.is_deleted = false`,
      [id]
    );

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Record not found" });
    }
    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("GET ATTENDANCE BY ID ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ UPDATE ATTENDANCE
=========================== */
const updateAttendance = async (req, res) => {
  try {
    const { id } = req.params;
    const { check_in_time, check_out_time } = req.body;

    if (!id || isNaN(id)) {
      return res.status(400).json({ success: false, message: "Invalid attendance ID" });
    }

    let updateQuery = '';
    let queryParams = [];

    if (check_in_time !== undefined) {
      updateQuery = 'UPDATE attendance SET check_in_time = $1, updated_at = NOW() WHERE id = $2 RETURNING *';
      queryParams = [check_in_time, id];
    } else if (check_out_time !== undefined) {
      updateQuery = 'UPDATE attendance SET check_out_time = $1, updated_at = NOW() WHERE id = $2 RETURNING *';
      queryParams = [check_out_time, id];
    } else {
      return res.status(400).json({ success: false, message: "Please provide check_in_time or check_out_time" });
    }

    const result = await pool.query(updateQuery, queryParams);

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: `Attendance record with ID ${id} not found or already deleted` });
    }

    const updatedRecord = result.rows[0];

    if (updatedRecord.check_in_time && updatedRecord.check_out_time) {
      const workHours = calculateWorkHours(updatedRecord.check_in_time, updatedRecord.check_out_time);
      await pool.query(`UPDATE attendance SET work_hours = $1 WHERE id = $2`, [workHours, id]);
      updatedRecord.work_hours = workHours;
    }

    res.json({ success: true, message: "Attendance updated successfully", data: updatedRecord });
  } catch (error) {
    console.error("UPDATE ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ DELETE ATTENDANCE (Soft)
=========================== */
const deleteAttendance = async (req, res) => {
  try {
    const { id } = req.params;
    const { updated_by } = req.body;

    const result = await pool.query(
      `UPDATE attendance SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND is_deleted = false RETURNING id`,
      [updated_by || null, id]
    );

    if (!result.rowCount) {
      return res.status(404).json({ success: false, message: "Record not found" });
    }
    res.json({ success: true, message: "Deleted successfully" });
  } catch (error) {
    console.error("DELETE ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET ATTENDANCE SUMMARY
=========================== */
const getAttendanceSummary = async (req, res) => {
  try {
    const { employee_code, month, year, org_id } = req.query;

    const conditions = ["e.is_deleted = false"];
    const params = [];
    let i = 1;

    if (employee_code) { conditions.push(`e.employee_code = $${i++}`); params.push(employee_code); }
    if (org_id) { conditions.push(`e.org_id = $${i++}`); params.push(org_id); }
    if (year) { conditions.push(`EXTRACT(YEAR FROM a.attendance_date) = $${i++}`); params.push(year); }
    if (month) { conditions.push(`EXTRACT(MONTH FROM a.attendance_date) = $${i++}`); params.push(month); }

    const where = `WHERE ${conditions.join(" AND ")}`;

    const result = await pool.query(
      `SELECT
         e.employee_code,
         CONCAT(e.first_name,' ',e.last_name) AS employee_name,
         e.department,
         s.shift_name,
         COUNT(CASE WHEN a.attendance_status = 'Present' THEN 1 END) AS present_days,
         COUNT(CASE WHEN a.attendance_status = 'Absent' THEN 1 END) AS absent_days,
         COUNT(CASE WHEN a.attendance_status = 'Late' THEN 1 END) AS late_days,
         COUNT(CASE WHEN a.attendance_status = 'Half Day' THEN 1 END) AS half_days,
         COUNT(CASE WHEN a.attendance_status = 'Holiday' THEN 1 END) AS holiday_days,
         COUNT(CASE WHEN a.attendance_status = 'Leave' THEN 1 END) AS leave_days,
         SUM(COALESCE(a.late_minutes, 0)) AS total_late_minutes,
         SUM(COALESCE(a.overtime_hours, 0)) AS total_overtime_hours,
         SUM(COALESCE(a.work_hours, 0)) AS total_work_hours,
         ROUND(AVG(a.work_hours), 2) AS avg_work_hours
       FROM employees e
       LEFT JOIN attendance a ON e.id = a.employee_id AND a.is_deleted = false
       LEFT JOIN shift_assignments sa
         ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id
       ${where}
       GROUP BY e.id, e.employee_code, e.first_name, e.last_name, e.department, s.shift_name
       ORDER BY e.employee_code`,
      params
    );

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

/* ===========================
   ✅ GET LATE ATTENDANCE REPORT
=========================== */
const getLateAttendanceReport = async (req, res) => {
  try {
    const { start_date, end_date } = req.query;
    const org_id = req.session.user.orgId;

    const conditions = ["a.is_late = true", "a.is_deleted = false"];
    const params = [];
    let i = 1;

    if (start_date) { conditions.push(`a.attendance_date >= $${i++}`); params.push(start_date); }
    if (end_date) { conditions.push(`a.attendance_date <= $${i++}`); params.push(end_date); }
    if (org_id) { conditions.push(`a.org_id = $${i++}`); params.push(org_id); }

    const result = await pool.query(
      `SELECT
         a.attendance_date, a.check_in_time, a.late_minutes,
         e.employee_code,
         CONCAT(e.first_name,' ',e.last_name) AS employee_name,
         e.department,
         s.shift_name, s.start_time AS shift_start_time
       FROM attendance a
       JOIN employees e ON a.employee_id = e.id
       LEFT JOIN shift_assignments sa
         ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id
       WHERE ${conditions.join(" AND ")}
       ORDER BY a.late_minutes DESC, a.attendance_date DESC`,
      params
    );

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

/* ===========================
   ✅ GET EMPLOYEES BY SHIFT
=========================== */
const getEmployeesByShift = async (req, res) => {
  try {
    const { shift_id, date } = req.query;
    const org_id = req.session.user.orgId;
    const targetDate = date || new Date().toISOString().split("T")[0];

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

    const result = await pool.query(
      `SELECT
         e.id, e.employee_code,
         CONCAT(e.first_name,' ',e.last_name) AS employee_name,
         e.department, e.designation,
         COALESCE(a.attendance_status, 'Present') AS attendance_status,
         a.check_in_time, a.check_out_time, a.work_hours,
         a.is_late, a.late_minutes, a.is_checked_out,
         a.id AS attendance_id,
         s.shift_name, s.start_time AS shift_start_time, s.end_time AS shift_end_time
       FROM employees e
       JOIN shift_assignments sa
         ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       JOIN shifts s ON sa.shift_id = s.id
       LEFT JOIN attendance a
         ON e.id = a.employee_id AND a.attendance_date = $1 AND a.is_deleted = false
       WHERE sa.shift_id = $2 AND e.org_id = $3 AND e.is_deleted = false`,
      [targetDate, shift_id, org_id]
    );

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

// /* ===========================
//    ✅ DAILY RESET
// =========================== */
// const dailyReset = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;

//     if (!org_id) {
//       return res.status(400).json({ success: false, message: "org_id not found in session" });
//     }

//     const today = new Date().toISOString().split("T")[0];
//     const isHoliday = await checkIsHoliday(today, org_id);
//     const defaultStatus = isHoliday ? "Holiday" : "Present";

//     const employeesResult = await pool.query(
//       `SELECT e.id,
//               COALESCE(s.shift_name, 'General') AS shift_name,
//               s.start_time AS shift_start_time,
//               s.end_time AS shift_end_time,
//               sa.shift_id
//        FROM employees e
//        LEFT JOIN shift_assignments sa
//          ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
//        LEFT JOIN shifts s ON sa.shift_id = s.id
//        WHERE e.org_id = $1 AND e.is_deleted = false`,
//       [org_id]
//     );

//     let createdCount = 0;
//     let skippedCount = 0;

//     for (const emp of employeesResult.rows) {
//       const existing = await pool.query(
//         `SELECT id FROM attendance WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
//         [emp.id, today]
//       );

//       if (existing.rows.length === 0) {
//         await pool.query(
//           `INSERT INTO attendance (
//              employee_id, attendance_date, attendance_status,
//              shift_type, shift_start_time, shift_end_time,
//              check_in_time, check_out_time,
//              original_shift_id, actual_shift_id,
//              org_id, created_at, updated_at
//            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NOW(), NOW())`,
//           [
//             emp.id, today, defaultStatus,
//             emp.shift_name, emp.shift_start_time, emp.shift_end_time,
//             // ✅ Default check_in to shift start, check_out to shift end on daily reset
//             defaultStatus !== "Holiday" ? (emp.shift_start_time || null) : null,
//             defaultStatus !== "Holiday" ? (emp.shift_end_time || null) : null,
//             emp.shift_id, emp.shift_id,
//             org_id,
//           ]
//         );
//         createdCount++;
//       } else {
//         skippedCount++;
//       }
//     }

//     res.json({
//       success: true,
//       message: `Daily reset done. ${createdCount} created, ${skippedCount} skipped (already exist).`,
//       data: { date: today, default_status: defaultStatus, created: createdCount, skipped: skippedCount },
//     });
//   } catch (error) {
//     console.error("DAILY RESET ERROR:", error);
//     res.status(500).json({ success: false, message: error.message });
//   }
// };

/* ===========================
   ✅ DAILY RESET
=========================== */
const dailyReset = async (req, res) => {
  try {
    // ✅ Get org_id from session or from request body
    let org_id = req.session.user?.orgId;
    
    // If not in session, try to get from request body
    if (!org_id && req.body.org_id) {
      org_id = req.body.org_id;
    }
    
    // If still not found, try to get from query params
    if (!org_id && req.query.org_id) {
      org_id = req.query.org_id;
    }
    
    console.log("Daily Reset - org_id:", org_id);
    console.log("Session user:", req.session.user);
    
    if (!org_id) {
      return res.status(400).json({ 
        success: false, 
        message: "Organization ID not found. Please provide org_id in request body." 
      });
    }

    const today = new Date().toISOString().split("T")[0];
    console.log("Today's date:", today);
    
    // Check if it's a holiday
    const isHoliday = await checkIsHoliday(today, org_id);
    const defaultStatus = isHoliday ? "Holiday" : "Present";
    
    console.log("Is holiday:", isHoliday);
    console.log("Default status:", defaultStatus);

    // Get all employees with their shift information
    const employeesResult = await pool.query(
      `SELECT e.id,
              COALESCE(s.shift_name, 'General') AS shift_name,
              s.start_time AS shift_start_time,
              s.end_time AS shift_end_time,
              sa.shift_id
       FROM employees e
       LEFT JOIN shift_assignments sa
         ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
       WHERE e.org_id = $1 AND e.is_deleted = false`,
      [org_id]
    );

    console.log(`Found ${employeesResult.rows.length} employees`);

    let createdCount = 0;
    let skippedCount = 0;
    let errorCount = 0;

    for (const emp of employeesResult.rows) {
      try {
        // Check if attendance already exists for today
        const existing = await pool.query(
          `SELECT id FROM attendance WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
          [emp.id, today]
        );

        if (existing.rows.length === 0) {
          // Insert new attendance record
          await pool.query(
            `INSERT INTO attendance (
               employee_id, attendance_date, attendance_status,
               shift_type, shift_start_time, shift_end_time,
               check_in_time, check_out_time,
               original_shift_id, actual_shift_id,
               org_id, created_at, updated_at
             ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NOW(), NOW())`,
            [
              emp.id, today, defaultStatus,
              emp.shift_name || 'General', 
              emp.shift_start_time || null, 
              emp.shift_end_time || null,
              // Default check_in to shift start, check_out to shift end (but not for Holiday)
              defaultStatus !== "Holiday" && defaultStatus !== "Absent" ? (emp.shift_start_time || null) : null,
              defaultStatus !== "Holiday" && defaultStatus !== "Absent" ? (emp.shift_end_time || null) : null,
              emp.shift_id || null, 
              emp.shift_id || null,
              org_id,
            ]
          );
          createdCount++;
        } else {
          skippedCount++;
        }
      } catch (err) {
        console.error(`Error processing employee ${emp.id}:`, err.message);
        errorCount++;
      }
    }

    res.json({
      success: true,
      message: `Daily reset completed. ${createdCount} created, ${skippedCount} already existed, ${errorCount} errors.`,
      data: { 
        date: today, 
        default_status: defaultStatus, 
        created: createdCount, 
        skipped: skippedCount,
        errors: errorCount,
        is_holiday: isHoliday
      },
    });
    
  } catch (error) {
    console.error("DAILY RESET ERROR:", error);
    res.status(500).json({ 
      success: false, 
      message: error.message,
      stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
    });
  }
};

/* ===========================
   ✅ GET SHIFTS
=========================== */
const getShifts = async (req, res) => {
  const org_id = req.session.user?.orgId;
  try {
    const result = await pool.query(
      `SELECT * FROM shifts WHERE org_id = $1 AND is_deleted = false ORDER BY start_time`,
      [org_id]
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("GET SHIFTS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ UPDATE ATTENDANCE TIME
=========================== */
// const updateAttendanceTime = async (req, res) => {
//   const { id } = req.params;
//   const { check_in_time, check_out_time } = req.body;

//   try {
//     const updates = [];
//     const values = [];
//     let i = 1;

//     if (check_in_time !== undefined) {
//       updates.push(`check_in_time = $${i++}`);
//       values.push(check_in_time);
//     }

//     if (check_out_time !== undefined) {
//       updates.push(`check_out_time = $${i++}`);
//       values.push(check_out_time);
//     }

//     if (updates.length === 0) {
//       return res.status(400).json({ success: false, message: "No fields provided" });
//     }

//     values.push(id);

//     const result = await pool.query(
//       `UPDATE attendance
//        SET ${updates.join(", ")}, updated_at = NOW()
//        WHERE id = $${i}
//        RETURNING *`,
//       values
//     );

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

//     res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("POSTGRES ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
// In attendanceManagementController.js - fix updateAttendanceTime

// In attendanceManagementController.js - Complete rewrite of updateAttendanceTime
// In attendanceManagementController.js - make sure this function is properly handling the update
const updateAttendanceTime = async (req, res) => {
  const { id } = req.params;
  const { check_in_time, check_out_time } = req.body;

  try {
    console.log("Update request received:", { id, check_in_time, check_out_time });
    
    // Validate id
    if (!id || isNaN(id)) {
      return res.status(400).json({ success: false, message: "Invalid attendance ID" });
    }

    // First check if record exists and is not checked out
    const checkRecord = await pool.query(
      `SELECT id, is_checked_out FROM attendance WHERE id = $1 AND is_deleted = false`,
      [id]
    );

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

    if (checkRecord.rows[0].is_checked_out) {
      return res.status(400).json({ success: false, message: "Cannot modify times after checkout" });
    }

    const updates = [];
    const values = [];
    let i = 1;

    if (check_in_time !== undefined) {
      // Ensure time format is HH:MM:SS
      let formattedTime = check_in_time;
      if (formattedTime && formattedTime.split(':').length === 2) {
        formattedTime = `${formattedTime}:00`;
      }
      updates.push(`check_in_time = $${i++}`);
      values.push(formattedTime);
    }

    if (check_out_time !== undefined) {
      let formattedTime = check_out_time;
      if (formattedTime && formattedTime.split(':').length === 2) {
        formattedTime = `${formattedTime}:00`;
      }
      updates.push(`check_out_time = $${i++}`);
      values.push(formattedTime);
    }

    if (updates.length === 0) {
      return res.status(400).json({ success: false, message: "No fields provided" });
    }

    values.push(id);

    const result = await pool.query(
      `UPDATE attendance
       SET ${updates.join(", ")}, updated_at = NOW()
       WHERE id = $${i}
       RETURNING *`,
      values
    );

    // Recalculate work hours if both times are set
    if (result.rows[0] && result.rows[0].check_in_time && result.rows[0].check_out_time) {
      const workHours = calculateWorkHours(result.rows[0].check_in_time, result.rows[0].check_out_time);
      await pool.query(`UPDATE attendance SET work_hours = $1 WHERE id = $2`, [workHours, id]);
      result.rows[0].work_hours = workHours;
    }

    res.json({ success: true, message: "Time updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("UPDATE ATTENDANCE TIME ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ===========================
   ✅ DEBUG ATTENDANCE ENDPOINT
=========================== */
const debugAttendance = async (req, res) => {
  try {
    const { id } = req.params;

    const attendance = await pool.query(`SELECT * FROM attendance WHERE id = $1`, [id]);
    const columns = await pool.query(`
      SELECT column_name, data_type
      FROM information_schema.columns
      WHERE table_name = 'attendance'
      AND column_name IN ('id', 'check_in_time', 'check_out_time')
    `);

    res.json({
      success: true,
      debug: {
        attendance_exists: attendance.rows.length > 0,
        attendance_record: attendance.rows[0] || null,
        table_columns: columns.rows,
        current_time: new Date().toISOString(),
        sample_time_format: "10:00:00"
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

// Add to attendanceManagementController.js
const debugShiftAssignments = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    
    const result = await pool.query(`
      SELECT 
        e.id, e.employee_code, e.first_name, e.last_name,
        sa.shift_id, sa.is_active, sa.is_deleted,
        s.shift_name, s.start_time, s.end_time
      FROM employees e
      LEFT JOIN shift_assignments sa 
        ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
      LEFT JOIN shifts s ON sa.shift_id = s.id AND s.is_deleted = false
      WHERE e.org_id = $1 AND e.is_deleted = false
      LIMIT 10
    `, [org_id]);
    
    res.json({
      success: true,
      data: result.rows,
      message: "Check if shift assignments are properly set"
    });
  } catch (error) {
    res.status(500).json({ success: false, message: error.message });
  }
};



module.exports = {
  createAttendance,
  checkIn,
  checkOut,
  individualCheckout,
  debugAttendance,
  getTodayAttendance,
  updateIndividualStatus,
  bulkMarkAttendance,
  bulkCheckout,
  getAttendance,
  getAttendanceById,
  updateAttendance,
  deleteAttendance,
  getAttendanceSummary,
  getLateAttendanceReport,
  getEmployeesByShift,
  dailyReset,
  getShifts,
  updateAttendanceTime,
  debugShiftAssignments,
}; 