

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

const DeductionEngine = require("../../services/deduction/deductionEngine");



/* ===========================
   ✅ HELPER FUNCTIONS (Define ONCE)
=========================== */

const getCurrentTime = () => new Date().toTimeString().split(" ")[0];

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 deriveStatus = (checkIn, shiftStart, overrideStatus) => {
  if (overrideStatus && ["Holiday", "Leave"].includes(overrideStatus)) return overrideStatus;
  if (!checkIn) return "Absent";
  const lateMin = calculateLateMinutes(checkIn, shiftStart || checkIn);
  if (lateMin > 15) return "Late";
  return "Present";
};


// Make sure this function exists and is working
const syncAttendanceFromTransactions = async (employeeId, attendanceDate, orgId) => {
    try {
        // Call the PostgreSQL function
        const result = await pool.query(
            `SELECT sync_attendance_for_employee($1, $2, $3) as attendance_id`,
            [employeeId, attendanceDate, orgId]
        );
        
        const attendanceId = result.rows[0]?.attendance_id;
        
        if (attendanceId) {
            // Sync to buffer
            await syncBufferForRecord(attendanceId);
            console.log(`✅ Synced attendance record ${attendanceId} for employee ${employeeId}`);
        }
        
        return attendanceId;
    } catch (error) {
        console.error("syncAttendanceFromTransactions error:", error);
        throw error;
    }
};
// ✅ SINGLE DEFINITION - Remove any other declarations of this function
const syncBufferForRecord = async (attendanceId) => {
  try {
    await pool.query(
      `INSERT INTO attendance_buffer (
         attendance_id, employee_id, employee_code, employee_name,
         shift_type, attendance_date, check_in_time, check_out_time,
         shift_start_time, shift_end_time, total_hours, overtime_hours,
         late_minutes, attendance_status, month_year, org_id, department_id,
         is_present, is_absent, is_late, is_holiday, is_leave,
         created_at, updated_at
       )
       SELECT
         a.id, e.id, e.employee_code,
         CONCAT(e.first_name, ' ', e.last_name),
         a.shift_type, a.attendance_date, a.check_in_time, a.check_out_time,
         a.shift_start_time, a.shift_end_time, a.work_hours, a.overtime_hours,
         a.late_minutes, a.attendance_status,
         TO_CHAR(a.attendance_date, 'YYYY-MM'),
         a.org_id, e.department_id,
         (a.attendance_status = 'Present'),
         (a.attendance_status = 'Absent'),
         (a.attendance_status = 'Late'),
         (a.attendance_status = 'Holiday'),
         (a.attendance_status = 'Leave'),
         NOW(), NOW()
       FROM attendance a
       JOIN employees e ON a.employee_id = e.id
       WHERE a.id = $1
       ON CONFLICT (attendance_id) DO UPDATE SET
         check_in_time = EXCLUDED.check_in_time,
         check_out_time = EXCLUDED.check_out_time,
         shift_start_time = EXCLUDED.shift_start_time,
         shift_end_time = EXCLUDED.shift_end_time,
         total_hours = EXCLUDED.total_hours,
         overtime_hours = EXCLUDED.overtime_hours,
         late_minutes = EXCLUDED.late_minutes,
         attendance_status = EXCLUDED.attendance_status,
         is_present = EXCLUDED.is_present,
         is_absent = EXCLUDED.is_absent,
         is_late = EXCLUDED.is_late,
         is_holiday = EXCLUDED.is_holiday,
         is_leave = EXCLUDED.is_leave,
         department_id = EXCLUDED.department_id,
         is_deleted = false,
         updated_at = NOW()`,
      [attendanceId]
    );
    
    // Trigger real-time payroll recalculation
    await recalcAffectedPayrollRecords(attendanceId);
    
  } catch (err) {
    console.error(`syncBufferForRecord(${attendanceId}) failed:`, err.message);
  }
};

// ✅ NEW FUNCTION: Recalculate payroll when attendance changes
const recalcAffectedPayrollRecords = async (attendanceId) => {
  try {
    const attResult = await pool.query(
      `SELECT employee_id, attendance_date FROM attendance WHERE id = $1`,
      [attendanceId]
    );
    
    if (attResult.rows.length === 0) return;
    
    const { employee_id, attendance_date } = attResult.rows[0];
    const month = new Date(attendance_date).getMonth() + 1;
    const year = new Date(attendance_date).getFullYear();
    
    // Check if needs_recalculation column exists
    const columnCheck = await pool.query(`
      SELECT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'payroll_records' AND column_name = 'needs_recalculation'
      )
    `);
    const hasNeedsRecalc = columnCheck.rows[0].exists;
    
    if (hasNeedsRecalc) {
      await pool.query(
        `UPDATE payroll_records 
         SET needs_recalculation = true, updated_at = NOW()
         WHERE employee_id = $1 AND month = $2 AND year = $3 AND is_deleted = false`,
        [employee_id, month, year]
      );
    }
    
  } catch (err) {
    console.error("recalcAffectedPayrollRecords error:", err);
  }
};

// Helper functions for employee lookup
const getEmployeeById = async (employeeId) => {
  const result = await pool.query(
    `SELECT
       e.id, e.employee_code, e.first_name, e.last_name,
       e.department_id, e.designation, e.org_id, e.salary,
       sg.id AS shift_group_id, sg.group_name,
       sp.id AS plan_id,
       s.id AS 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_group_members sgm ON e.id = sgm.employee_id
     LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
     LEFT JOIN shift_plans sp ON sg.org_id = sp.org_id AND sp.is_deleted = false
     LEFT JOIN shifts s ON s.org_id = e.org_id AND s.is_deleted = false
     WHERE e.id = $1 AND e.status = 'Active' AND e.is_deleted = false
     ORDER BY sp.created_at DESC, s.created_at DESC
     LIMIT 1`,
    [employeeId]
  );
  return result.rows[0] || null;
};

const getEmployeeWithShift = async (employeeCode) => {
  const result = await pool.query(
    `SELECT
       e.id, e.employee_code, e.first_name, e.last_name,
       e.department_id, e.designation, e.org_id, e.salary,
       sg.id AS shift_group_id, sg.group_name,
       sp.id AS plan_id,
       s.id AS 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_group_members sgm ON e.id = sgm.employee_id
     LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
     LEFT JOIN shift_plans sp ON sg.org_id = sp.org_id AND sp.is_deleted = false
     LEFT JOIN shifts s ON s.org_id = e.org_id AND s.is_deleted = false
     WHERE e.employee_code = $1 AND e.status = 'Active' AND e.is_deleted = false
     ORDER BY sp.created_at DESC, s.created_at DESC
     LIMIT 1`,
    [employeeCode]
  );
  return result.rows[0] || null;
};

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 (err) {
    console.error("Error checking holiday:", err);
    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,
      employee_code,
      attendance_date,
      check_in_time,
      check_out_time,
      attendance_status,
      remarks,
      original_shift_id,
      actual_shift_id,
      swap_with_employee_id,
      is_double_shift,
      second_shift_id,
    } = req.body;

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

    const employee = employee_id
      ? await getEmployeeById(employee_id)
      : await getEmployeeWithShift(employee_code);

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

    const isHoliday = await checkIsHoliday(attendance_date, org_id);

    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],
      });
    }

    const shiftStart = employee.shift_start_time || null;
    const shiftEnd   = employee.shift_end_time   || null;

    const finalCheckIn  = check_in_time  || null;
    const finalCheckOut = check_out_time || null;

    let finalStatus;
    if (isHoliday) {
      finalStatus = "Holiday";
    } else if (attendance_status && ["Leave", "Holiday"].includes(attendance_status)) {
      finalStatus = attendance_status;
    } else {
      finalStatus = deriveStatus(finalCheckIn, shiftStart, attendance_status);
    }

    const lateMinutes = shiftStart && finalCheckIn ? calculateLateMinutes(finalCheckIn, shiftStart) : 0;
    const isLate      = lateMinutes > 15;

    const workHours = finalCheckIn && finalCheckOut
      ? calculateWorkHours(finalCheckIn, finalCheckOut)
      : 0;

    const shiftHours = employee.shift_working_hours || 8;
    const overtimeHours = workHours > shiftHours
      ? parseFloat((workHours - shiftHours).toFixed(2))
      : 0;

    if (overtimeHours > 0 && !["Holiday", "Leave", "Half Day", "Cross Shift", "Shift Swap", "Double Shift", "Late", "Absent"].includes(finalStatus)) {
      finalStatus = "Overtime";
    }

    const result = await pool.query(
      `INSERT INTO attendance (
         employee_id, employee_code, 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,$21,NOW(),NOW()
       ) RETURNING *`,
      [
        employee.id,
        employee.employee_code,
        attendance_date,
        finalCheckIn,
        finalCheckOut,
        employee.shift_name || "General",
        shiftStart,
        shiftEnd,
        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,
      ]
    );

    await syncBufferForRecord(result.rows[0].id);

    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 });
  }
};



const checkIn = async (req, res) => {
  const org_id =  1;
  const user_id =  1;
  
  try {
    let {
      employee_id,
      employee_code,
      attendance_date,
      check_in_time,
      latitude,
      longitude,
      office_location,
    } = req.body;

    // Validate required fields
    if ((!employee_id && !employee_code) || !attendance_date) {
      return res.status(400).json({
        success: false,
        message: "employee_id (or employee_code) and attendance_date are required",
      });
    }

    // Get employee with shift information
    let employeeQuery = `
      SELECT 
        e.id, e.employee_code, e.first_name, e.last_name,
        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,
        s.id AS 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.status = 'Active' AND e.is_deleted = false
    `;
    
    let queryParams;
    if (employee_id && !isNaN(employee_id)) {
      employeeQuery += ` AND e.id = $1`;
      queryParams = [parseInt(employee_id)];
    } else {
      const identifier = employee_code || employee_id;
      employeeQuery += ` AND e.employee_code = $1`;
      queryParams = [identifier];
    }
    
    const employeeResult = await pool.query(employeeQuery, queryParams);
    
    if (employeeResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const employee = employeeResult.rows[0];
    const shiftStart = employee.shift_start_time;
    const finalCheckIn = check_in_time || shiftStart || getCurrentTime();

    // Calculate late minutes
    let lateMinutes = 0;
    let isLate = false;
    let attendanceStatus = "Present";

    if (shiftStart && finalCheckIn) {
      lateMinutes = calculateLateMinutes(finalCheckIn, shiftStart);
      isLate = lateMinutes > 15;
      attendanceStatus = isLate ? "Late" : "Present";
    }

    // Check if it's a holiday
    const isHoliday = await checkIsHoliday(attendance_date, org_id);
    const finalStatus = isHoliday ? "Holiday" : attendanceStatus;

    // Check if attendance record exists
    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]
    );

    // Insert into transaction table (this will trigger auto-sync)
    const transactionDateTime = `${attendance_date} ${finalCheckIn}`;
    await pool.query(
      `INSERT INTO attendance_transaction (
        employee_id, attendance_date, transaction_time, transaction_type,
        latitude, longitude, office_location, org_id, created_by
      ) VALUES ($1, $2, $3, 'CHECK_IN', $4, $5, $6, $7, $8)`,
      [employee.id, attendance_date, transactionDateTime,
       latitude || null, longitude || null, office_location || null,
       org_id, user_id]
    );

    let result;

    if (existing.rows.length > 0) {
      // Update existing record
      if (existing.rows[0].check_in_time) {
        return res.status(409).json({ success: false, message: "Check-in already marked for today" });
      }
      
      result = await pool.query(
        `UPDATE attendance SET
           check_in_time = $1, 
           is_late = $2, 
           late_minutes = $3,
           attendance_status = $4,
           shift_start_time = $5,
           shift_end_time = $6,
           shift_type = $7,
           latitude = COALESCE($8, latitude),
           longitude = COALESCE($9, longitude),
           office_location = COALESCE($10, office_location),
           updated_by = $11, 
           updated_at = NOW()
         WHERE id = $12 RETURNING *`,
        [finalCheckIn, isLate, lateMinutes, finalStatus,
         shiftStart, employee.shift_end_time, employee.shift_name || "General",
         latitude || null, longitude || null, office_location || null,
         user_id, existing.rows[0].id]
      );
    } else {
      // Create new record
      result = await pool.query(
        `INSERT INTO attendance (
           employee_id, employee_code, attendance_date, check_in_time,
           shift_type, shift_start_time, shift_end_time, shift_working_hours,
           is_late, late_minutes, attendance_status,
           latitude, longitude, office_location,
           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, employee.employee_code, attendance_date, finalCheckIn,
          employee.shift_name || "General", shiftStart, employee.shift_end_time, employee.shift_working_hours || 8,
          isLate, lateMinutes, finalStatus,
          latitude || null, longitude || null, office_location || null,
          org_id, user_id
        ]
      );
    }

    // Sync buffer
    await syncBufferForRecord(result.rows[0].id);

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

// const checkOut = async (req, res) => {
//     const org_id =  1; // Allow org_id from body for testing, but prefer session
//     const userId =  1;
//     try {
//         let {
//             employee_id,
//             employee_code,
//             attendance_date,
//             check_out_time,
//             latitude,
//             longitude,
//             office_location,
//         } = req.body;

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

//         // Get employee
//         const employee = employee_id
//             ? await getEmployeeById(employee_id)
//             : await getEmployeeWithShift(employee_code);

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

//         const finalCheckOut = check_out_time || getCurrentTime();
        
//         // Get today's date for the transaction
//         const today = new Date().toISOString().split('T')[0];
//         const transactionDateTime = `${attendance_date} ${finalCheckOut}`;
        
//         // ✅ INSERT INTO TRANSACTION TABLE
//         const transactionResult = await pool.query(
//             `INSERT INTO attendance_transaction (
//                 employee_id, attendance_date, transaction_time, transaction_type,
//                 latitude, longitude, office_location, org_id, created_by, created_at
//             ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
//             RETURNING *`,
//             [employee.id, attendance_date, transactionDateTime, 'CHECK_OUT',
//              latitude || null, longitude || null, office_location || null, org_id, userId || null]
//         );

//         // ✅ FORCE SYNC ATTENDANCE TABLE
//         await syncAttendanceFromTransactions(employee.id, attendance_date, org_id);

//         // Get the updated attendance record
//         const updatedAttendance = await pool.query(
//             `SELECT * FROM attendance 
//              WHERE employee_id = $1 AND attendance_date = $2 AND is_deleted = false`,
//             [employee.id, attendance_date]
//         );

//         res.status(200).json({
//             success: true,
//             message: "Check-out recorded and attendance synced successfully",
//             data: {
//                 transaction: transactionResult.rows[0],
//                 attendance: updatedAttendance.rows[0]
//             }
//         });
//     } catch (error) {
//         console.error("CHECK-OUT ERROR:", error);
//         res.status(500).json({ success: false, message: error.message });
//     }
// };
const checkOut = async (req, res) => {
  const org_id =  1;
  const userId =  1;
  
  try {
    let {
      employee_id,
      employee_code,
      attendance_date,
      check_out_time,
      latitude,
      longitude,
      office_location,
    } = req.body;

    // Validate required fields
    if ((!employee_id && !employee_code) || !attendance_date) {
      return res.status(400).json({
        success: false,
        message: "employee_id (or employee_code) and attendance_date are required",
      });
    }

    // Get employee with shift information
    let employeeQuery = `
      SELECT 
        e.id, e.employee_code, e.first_name, e.last_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.status = 'Active' AND e.is_deleted = false
    `;
    
    let queryParams;
    if (employee_id && !isNaN(employee_id)) {
      employeeQuery += ` AND e.id = $1`;
      queryParams = [parseInt(employee_id)];
    } else {
      const identifier = employee_code || employee_id;
      employeeQuery += ` AND e.employee_code = $1`;
      queryParams = [identifier];
    }
    
    const employeeResult = await pool.query(employeeQuery, queryParams);
    
    if (employeeResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const employee = employeeResult.rows[0];
    const finalCheckOut = check_out_time || employee.shift_end_time || getCurrentTime();

    // Get existing attendance record
    const existing = await pool.query(
      `SELECT a.id, a.check_in_time, a.is_double_shift, a.attendance_status,
              a.is_checked_out
       FROM attendance a
       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" });
    }

    const record = existing.rows[0];

    if (record.is_checked_out) {
      return res.status(409).json({ success: false, message: "Employee already checked out" });
    }

    if (!record.check_in_time) {
      return res.status(400).json({ success: false, message: "Employee hasn't checked in yet" });
    }

    // Insert into transaction table (this will trigger auto-sync)
    const transactionDateTime = `${attendance_date} ${finalCheckOut}`;
    await pool.query(
      `INSERT INTO attendance_transaction (
        employee_id, attendance_date, transaction_time, transaction_type,
        latitude, longitude, office_location, org_id, created_by
      ) VALUES ($1, $2, $3, 'CHECK_OUT', $4, $5, $6, $7, $8)`,
      [employee.id, attendance_date, transactionDateTime,
       latitude || null, longitude || null, office_location || null,
       org_id, userId]
    );

    // Calculate work hours (handles overnight shifts)
    const workHours = calculateWorkHours(record.check_in_time, finalCheckOut);
    
    let shiftHours = employee.shift_working_hours || 8;
    if (record.is_double_shift) shiftHours *= 2;

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

    // Determine final status
    const lockedStatuses = ["Holiday", "Leave", "Half Day", "Cross Shift", "Shift Swap", "Double Shift"];
    let finalStatus = record.attendance_status;
    
    if (!lockedStatuses.includes(finalStatus)) {
      if (overtimeHours > 0) {
        finalStatus = "Overtime";
      } else if (record.check_in_time && finalStatus !== "Late") {
        finalStatus = "Present";
      }
    }

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

    // Sync buffer
    await syncBufferForRecord(record.id);

    res.status(200).json({
      success: true,
      message: "Check-out recorded successfully",
      data: {
        attendance: result.rows[0],
        transaction_recorded: true,
        work_hours: workHours,
        overtime_hours: overtimeHours
      }
    });
  } catch (error) {
    console.error("CHECK-OUT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};
const getEmployeeTransactions = async (req, res) => {
    try {
        const { employee_id, start_date, end_date } = req.query;
        const org_id = req.session.user.orgId;
        
        let query = `
            SELECT * FROM attendance_transaction 
            WHERE org_id = $1 AND is_deleted = false
        `;
        let params = [org_id];
        let idx = 2;
        
        if (employee_id) {
            query += ` AND employee_id = $${idx++}`;
            params.push(employee_id);
        }
        if (start_date) {
            query += ` AND attendance_date >= $${idx++}`;
            params.push(start_date);
        }
        if (end_date) {
            query += ` AND attendance_date <= $${idx++}`;
            params.push(end_date);
        }
        
        query += ` ORDER BY transaction_time DESC`;
        
        const result = await pool.query(query, params);
        
        res.json({
            success: true,
            data: result.rows,
            count: result.rows.length
        });
    } catch (error) {
        console.error("GET TRANSACTIONS ERROR:", error);
        res.status(500).json({ success: false, message: error.message });
    }
};
/* ===========================
   ✅ INDIVIDUAL CHECKOUT (by attendance ID)
   FIX: Added overtime calculation + buffer sync.
=========================== */
const individualCheckout = async (req, res) => {
  try {
    const { id } = req.params;
    const { check_out_time, updated_by } = req.body;

    // FIX: include is_checked_out in SELECT
    const existing = await pool.query(
      `SELECT a.id, a.check_in_time, a.is_checked_out, a.is_double_shift,
              a.attendance_status,
              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" });
    }

    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 *= 2;

    // FIX: correct overtime calculation
    const overtimeHours = workHours > shiftHours
      ? parseFloat((workHours - shiftHours).toFixed(2))
      : 0;

    const lockedStatuses = ["Holiday", "Leave", "Half Day", "Cross Shift", "Shift Swap", "Double Shift"];
    let finalStatus = record.attendance_status;
    if (!lockedStatuses.includes(finalStatus)) {
      if (overtimeHours > 0) {
        finalStatus = "Overtime";
      } else if (record.check_in_time && finalStatus !== "Late") {
        finalStatus = "Present";
      }
    }

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

    // Sync buffer with new overtime values
    await syncBufferForRecord(parseInt(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 });
  }
};

/* ===========================
   ✅ UPDATE ATTENDANCE (check-in / check-out time edit)
   FIX: Added buffer sync after every time update.
=========================== */
const updateAttendance = async (req, res) => {
  try {
    const { id } = req.params;
    const { check_in_time, check_out_time } = req.body;

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

    const existingRes = await pool.query(
      `SELECT a.*, s.working_hours AS shift_working_hours, s.start_time AS db_shift_start
       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 (existingRes.rows.length === 0) {
      return res.status(404).json({ success: false, message: `Attendance record ${id} not found` });
    }

    const old = existingRes.rows[0];

    // Normalize to HH:MM:SS
    const normalizeTime = (t) => {
      if (!t) return null;
      const timeRegexHHMM   = /^([0-1][0-9]|2[0-3]):[0-5][0-9]$/;
      const timeRegexHHMMSS = /^([0-1][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$/;
      if (timeRegexHHMM.test(t))   return `${t}:00`;
      if (timeRegexHHMMSS.test(t)) return t;
      return null;
    };

    const newCheckIn  = check_in_time  !== undefined ? normalizeTime(check_in_time)  : old.check_in_time;
    const newCheckOut = check_out_time !== undefined ? normalizeTime(check_out_time) : old.check_out_time;

    if (check_in_time !== undefined && !newCheckIn) {
      return res.status(400).json({ success: false, message: "Invalid check_in_time format. Use HH:MM or HH:MM:SS" });
    }
    if (check_out_time !== undefined && !newCheckOut) {
      return res.status(400).json({ success: false, message: "Invalid check_out_time format. Use HH:MM or HH:MM:SS" });
    }

    const shiftStart  = old.shift_start_time || old.db_shift_start;
    const lateMinutes = newCheckIn && shiftStart ? calculateLateMinutes(newCheckIn, shiftStart) : 0;
    const isLate      = lateMinutes > 15;

    // FIX: recalculate work_hours from the new times
    const workHours = newCheckIn && newCheckOut
      ? calculateWorkHours(newCheckIn, newCheckOut)
      : 0;

    const shiftHours    = old.shift_working_hours || 8;

    // FIX: correct overtime = work_hours - shift_hours (never negative)
    const overtimeHours = workHours > shiftHours
      ? parseFloat((workHours - shiftHours).toFixed(2))
      : 0;

    // FIX: re-derive status including Overtime
    const lockedStatuses = ["Holiday", "Leave", "Half Day", "Cross Shift", "Shift Swap", "Double Shift"];
    let finalStatus = old.attendance_status;
    if (!lockedStatuses.includes(finalStatus)) {
      if (!newCheckIn)              finalStatus = "Absent";
      else if (isLate)              finalStatus = "Late";
      else if (overtimeHours > 0)   finalStatus = "Overtime";  // FIX: was missing Overtime here
      else                          finalStatus = "Present";
    }

    const result = await pool.query(
      `UPDATE attendance SET
         check_in_time     = $1,
         check_out_time    = $2,
         work_hours        = $3,
         overtime_hours    = $4,
         is_late           = $5,
         late_minutes      = $6,
         attendance_status = $7,
         updated_at        = NOW()
       WHERE id = $8 RETURNING *`,
      [newCheckIn, newCheckOut, workHours, overtimeHours, isLate, lateMinutes, finalStatus, id]
    );

    // FIX: sync buffer after every time edit so reports reflect real values
    await syncBufferForRecord(parseInt(id));

    res.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 });
  }
};

/* ===========================
   ✅ 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];

    // ✅ FIXED: Removed e.department and e.designation - use department_id and designation_id with JOINs
    let empQuery = `
      SELECT e.id, e.employee_code, e.first_name, e.last_name,
             COALESCE(d.dept_name, 'Unknown') AS department,
             COALESCE(des.designation_name, 'Unknown') AS designation,
             sg.id AS shift_group_id, sg.group_name,
             COALESCE(s.shift_name, 'General') AS shift_name,
             s.id AS shift_id,
             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 departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      LEFT JOIN shift_group_members sgm ON e.id = sgm.employee_id
      LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
      LEFT JOIN shift_plans sp ON sg.org_id = sp.org_id AND sp.is_deleted = false
      LEFT JOIN shifts s ON s.org_id = e.org_id AND s.is_deleted = false
      WHERE e.org_id = $1 AND e.status = 'Active' AND e.is_deleted = false`;

    const empParams = [org_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 = [], late = [], absent = [], other = [];

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

      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,
        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 "Overtime":
          present.push({ ...employeeData, badge: "OT" });
          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 a.*, 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.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 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(`overtime_hours = $${i++}`); params.push(0);
        updates.push(`is_checked_out = $${i++}`); params.push(false);
        break;
      case "Overtime":
        updates.push(`is_late = $${i++}`);        params.push(false);
        updates.push(`late_minutes = $${i++}`);   params.push(0);
        // FIX: recalculate overtime_hours from actual work_hours
        if (old.work_hours && parseFloat(old.work_hours) > (old.shift_working_hours || 8)) {
          const otHours = parseFloat(
            (parseFloat(old.work_hours) - (old.shift_working_hours || 8)).toFixed(2)
          );
          updates.push(`overtime_hours = $${i++}`); params.push(otHours);
        } else {
          // If no overtime in data, still mark status but set a default 0 overtime
          updates.push(`overtime_hours = $${i++}`); params.push(0);
        }
        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) {
          updates.push(`work_hours = $${i++}`); params.push(calculateWorkHours(old.check_in_time, old.check_out_time));
        }
        updates.push(`overtime_hours = $${i++}`); params.push(0);
        updates.push(`is_late = $${i++}`);         params.push(false);
        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
    );

    // Sync buffer after status change
    await syncBufferForRecord(parseInt(id));

    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 required",
//       });
//     }

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

//     let isHoliday = false;
//     try {
//       const hc = 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 = hc.rows.length > 0;
//     } catch (_) {}

//     const finalStatus = isHoliday ? "Holiday" : status;
//     let successCount = 0, failCount = 0;
//     const results = [];
//     const syncIds = [];

//     for (const employeeCode of employee_ids) {
//       try {
//         // Get employee with shift from shift_plan_cells
//         const empResult = await pool.query(
//           `SELECT e.id, e.employee_code,
//                   COALESCE(s.shift_name, 'General') AS shift_name,
//                   s.start_time,
//                   s.end_time,
//                   s.working_hours,
//                   s.id AS shift_id
//            FROM employees e
//            LEFT JOIN shift_group_members sgm ON e.id = sgm.employee_id
//            LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
//            LEFT JOIN shift_plan_cells spc ON spc.group_id = sgm.group_id AND spc.assign_date = $1
//            LEFT JOIN shifts s ON s.id = spc.shift_id
//            WHERE e.employee_code = $2 AND e.status = 'Active' AND e.is_deleted = false`,
//           [date, employeeCode]
//         );

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

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

//         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]
//         );

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

//         let attendanceId;

//         if (existing.rows.length > 0) {
//           attendanceId = existing.rows[0].id;
//           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,
//                     overtime_hours = 0, is_late = false, late_minutes = 0, is_checked_out = false
//                 WHERE id = $3`;
//               queryParams = [finalStatus, updated_by || userId || null, attendanceId];
//               break;
              
//             case "Late":
//               // ✅ FIX: For Late status, keep existing check-in or use shift start, mark as late
//               const currentCheckIn = existing.rows[0].check_in_time || emp.start_time;
//               const currentCheckOut = existing.rows[0].check_out_time || emp.end_time;
              
//               // Calculate late minutes based on shift start
//               let lateMins = 30; // Default late minutes
//               if (currentCheckIn && emp.start_time) {
//                 lateMins = calculateLateMinutes(currentCheckIn, emp.start_time);
//                 if (lateMins < 1) lateMins = 30; // If not late but marking as Late, set 30 min default
//               }
              
//               // Calculate work hours if both times exist
//               let workHrs = 0;
//               let otHrs = 0;
//               if (currentCheckIn && currentCheckOut) {
//                 workHrs = calculateWorkHours(currentCheckIn, currentCheckOut);
//                 const shiftHrs = emp.working_hours || 8;
//                 otHrs = workHrs > shiftHrs ? parseFloat((workHrs - shiftHrs).toFixed(2)) : 0;
//               }
              
//               updateQuery = `
//                 UPDATE attendance
//                 SET attendance_status = $1, updated_by = $2, updated_at = NOW(),
//                     is_late = true, late_minutes = $3,
//                     work_hours = $4, overtime_hours = $5
//                 WHERE id = $6`;
//               queryParams = [finalStatus, updated_by || userId || null, lateMins, workHrs, otHrs, attendanceId];
//               break;
              
//             default:
//               // Present, Overtime, etc.
//               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 || userId || null, attendanceId];
//           }

//           await pool.query(updateQuery, queryParams);
          
//         } else {
//           // No existing record - create new
//           let checkIn = null;
//           let checkOut = null;
//           let derivedStatus = finalStatus;
//           let workHrs = 0;
//           let otHrs = 0;
//           let isLate = false;
//           let lateMins = 0;

//           if (finalStatus === "Present") {
//             // Use shift times
//             checkIn = emp.start_time;
//             checkOut = emp.end_time;
//             isLate = false;
//             lateMins = 0;
            
//             if (checkIn && checkOut) {
//               workHrs = calculateWorkHours(checkIn, checkOut);
//               const shiftHrs = emp.working_hours || 8;
//               otHrs = workHrs > shiftHrs ? parseFloat((workHrs - shiftHrs).toFixed(2)) : 0;
//               if (otHrs > 0) derivedStatus = "Overtime";
//             }
            
//           } else if (finalStatus === "Late") {
//             // ✅ FIX: For Late status, use shift times but mark as late
//             checkIn = emp.start_time;
//             checkOut = emp.end_time;
//             isLate = true;
//             // Calculate late minutes - default 30 if shift start exists
//             lateMins = emp.start_time ? calculateLateMinutes(emp.start_time, emp.start_time) + 30 : 30;
//             if (lateMins < 1) lateMins = 30;
            
//             if (checkIn && checkOut) {
//               workHrs = calculateWorkHours(checkIn, checkOut);
//               const shiftHrs = emp.working_hours || 8;
//               otHrs = workHrs > shiftHrs ? parseFloat((workHrs - shiftHrs).toFixed(2)) : 0;
//             }
//             derivedStatus = "Late";
            
//           } else if (finalStatus === "Absent") {
//             checkIn = null;
//             checkOut = null;
//             isLate = false;
//             lateMins = 0;
//             workHrs = 0;
//             otHrs = 0;
//             derivedStatus = "Absent";
//           }

//           const insertResult = await pool.query(
//             `INSERT INTO attendance (
//                employee_id, employee_code, attendance_date, attendance_status,
//                shift_type, shift_start_time, shift_end_time,
//                check_in_time, check_out_time, work_hours, overtime_hours,
//                is_late, late_minutes,
//                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,$17,NOW(),NOW())
//              RETURNING id`,
//             [
//               empId, employeeCode, date, derivedStatus,
//               emp.shift_name || "General",
//               emp.start_time || null, emp.end_time || null,
//               checkIn, checkOut, workHrs, otHrs,
//               isLate, lateMins,
//               emp.shift_id || null, emp.shift_id || null,
//               org_id, updated_by || userId || null,
//             ]
//           );
//           attendanceId = insertResult.rows[0].id;
//         }

//         syncIds.push(attendanceId);
//         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 });
//       }
//     }

//     // Sync buffer for all successfully processed records
//     await Promise.all(syncIds.map((sid) => syncBufferForRecord(sid)));

//     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 });
//   }
// };
// In attendanceManagementController.js - Fix the bulkMarkAttendance function
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, check_in_time, check_out_time } = req.body;

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

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

    let isHoliday = false;
    try {
      const hc = 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 = hc.rows.length > 0;
    } catch (_) {}

    const finalStatus = isHoliday ? "Holiday" : status;
    let successCount = 0, failCount = 0;
    const results = [];
    const syncIds = [];

    for (const employeeCode of employee_ids) {
      try {
        // Get employee with shift info
        const empResult = await pool.query(
          `SELECT e.id, e.employee_code,
                  COALESCE(s.shift_name, 'General') AS shift_name,
                  s.start_time,
                  s.end_time,
                  s.working_hours,
                  s.id AS shift_id
           FROM employees e
           LEFT JOIN shift_group_members sgm ON e.id = sgm.employee_id
           LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
           LEFT JOIN shift_plan_cells spc ON spc.group_id = sgm.group_id AND spc.assign_date = $1
           LEFT JOIN shifts s ON s.id = spc.shift_id
           WHERE e.employee_code = $2 AND e.status = 'Active' AND e.is_deleted = false`,
          [date, employeeCode]
        );

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

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

        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]
        );

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

        // Determine check-in and check-out times based on status
        let finalCheckIn = null;
        let finalCheckOut = null;
        let finalCheckInTime = null;
        let finalCheckOutTime = null;
        
        if (finalStatus === "Present" || finalStatus === "Late" || finalStatus === "Overtime") {
          // Use provided times or shift times
          finalCheckInTime = check_in_time || emp.start_time;
          finalCheckOutTime = check_out_time || emp.end_time;
        }

        let attendanceId;

        if (existing.rows.length > 0) {
          attendanceId = existing.rows[0].id;
          
          // Update existing record with times
          const updateQuery = `
            UPDATE attendance
            SET attendance_status = $1, 
                updated_by = $2, 
                updated_at = NOW(),
                check_in_time = COALESCE($3, check_in_time),
                check_out_time = COALESCE($4, check_out_time),
                is_late = $5,
                late_minutes = $6,
                work_hours = $7,
                overtime_hours = $8
            WHERE id = $9
          `;
          
          // Calculate work hours and late minutes
          let lateMins = 0;
          let isLate = false;
          let workHrs = 0;
          let otHrs = 0;
          
          if (finalCheckInTime && emp.start_time) {
            lateMins = calculateLateMinutes(finalCheckInTime, emp.start_time);
            isLate = lateMins > 15 && finalStatus === "Late";
          }
          
          if (finalCheckInTime && finalCheckOutTime) {
            workHrs = calculateWorkHours(finalCheckInTime, finalCheckOutTime);
            const shiftHrs = emp.working_hours || 8;
            otHrs = workHrs > shiftHrs ? parseFloat((workHrs - shiftHrs).toFixed(2)) : 0;
          }
          
          await pool.query(updateQuery, [
            finalStatus, updated_by || userId || null, 
            finalCheckInTime, finalCheckOutTime,
            isLate, lateMins, workHrs, otHrs, attendanceId
          ]);
          
        } else {
          // Create new record with times
          let workHrs = 0;
          let otHrs = 0;
          let isLate = false;
          let lateMins = 0;
          
          if (finalCheckInTime && finalCheckOutTime) {
            workHrs = calculateWorkHours(finalCheckInTime, finalCheckOutTime);
            const shiftHrs = emp.working_hours || 8;
            otHrs = workHrs > shiftHrs ? parseFloat((workHrs - shiftHrs).toFixed(2)) : 0;
          }
          
          if (finalCheckInTime && emp.start_time) {
            lateMins = calculateLateMinutes(finalCheckInTime, emp.start_time);
            isLate = lateMins > 15 && finalStatus === "Late";
          }
          
          // In the INSERT query for bulkMarkAttendance, add shift_id:
// const insertResult = await pool.query(
//   `INSERT INTO attendance (
//      employee_id, employee_code, attendance_date, attendance_status,
//      shift_type, shift_start_time, shift_end_time, shift_id,  // ← ADD shift_id
//      check_in_time, check_out_time, work_hours, overtime_hours,
//      is_late, late_minutes,
//      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,$17,NOW(),NOW())
//    RETURNING id`,
//   [
//     empId, employeeCode, date, finalStatus,
//     emp.shift_name || "General",
//     emp.start_time || null, emp.end_time || null,
//     emp.shift_id || null,  // ← ADD THIS LINE (shift_id)
//     finalCheckInTime, finalCheckOutTime, workHrs, otHrs,
//     isLate, lateMins,
//     emp.shift_id || null, emp.shift_id || null,
//     org_id, updated_by || userId || null,
//   ]
// );
// In bulkMarkAttendance, when creating new record:
const insertResult = await pool.query(
  `INSERT INTO attendance (
     employee_id, employee_code, attendance_date, attendance_status,
     shift_type, shift_start_time, shift_end_time, shift_working_hours,
     check_in_time, check_out_time, work_hours, overtime_hours,
     is_late, late_minutes,
     original_shift_id, actual_shift_id, shift_id,  -- ← ADD 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,$17,$18,$19,NOW(),NOW())
   RETURNING id`,
  [
    empId, employeeCode, date, finalStatus,
    emp.shift_name || "General",
    emp.start_time || null, emp.end_time || null,
    emp.working_hours || 8,
    finalCheckInTime, finalCheckOutTime, workHrs, otHrs,
    isLate, lateMins,
    emp.shift_id || null, emp.shift_id || null,
    emp.shift_id || null,  // ← shift_id column
    org_id, updated_by || userId || null,
  ]
);
          attendanceId = insertResult.rows[0].id;
        }

        syncIds.push(attendanceId);
        successCount++;
        results.push({ 
          employee_code: employeeCode, 
          success: true,
          check_in_time: finalCheckInTime,
          check_out_time: finalCheckOutTime
        });
      } catch (err) {
        console.error(`Error processing ${employeeCode}:`, err.message);
        failCount++;
        results.push({ employee_code: employeeCode, success: false, message: err.message });
      }
    }

    // Sync buffer for all successfully processed records
    await Promise.all(syncIds.map((sid) => syncBufferForRecord(sid)));

    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
   FIX: Added buffer sync for each checked-out record.
=========================== */
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 required" });
    }

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

    for (const employeeCode of employee_ids) {
      try {
        const empResult = await pool.query(
          `SELECT e.id FROM employees WHERE employee_code = $1 AND status = 'Active' 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,
                  a.attendance_status,
                  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];
        const finalCheckout = checkout_time || rec.shift_end_time || getCurrentTime();

        const workHours = rec.check_in_time ? calculateWorkHours(rec.check_in_time, finalCheckout) : 0;
        let shiftHours  = rec.shift_working_hours || 8;
        if (rec.is_double_shift) shiftHours *= 2;

        // FIX: correct overtime calculation
        const overtimeHours = workHours > shiftHours
          ? parseFloat((workHours - shiftHours).toFixed(2))
          : 0;

        const lockedStatuses = ["Holiday", "Leave", "Half Day", "Cross Shift", "Shift Swap", "Double Shift"];
        let finalStatus = rec.attendance_status;
        if (!lockedStatuses.includes(finalStatus)) {
          if (overtimeHours > 0) {
            finalStatus = "Overtime";
          } else if (rec.check_in_time && finalStatus !== "Late") {
            finalStatus = "Present";
          }
        }

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

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

    // Sync buffer for all checked-out records
    await Promise.all(syncIds.map((sid) => syncBufferForRecord(sid)));

    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 (paginated, with filters)
=========================== */
const getAttendance = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const {
      page = 1, limit = 20,
      employee_id, start_date, end_date,
      shift_type, attendance_status, date,
    } = req.query;

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

    const offset = (parseInt(page) - 1) * parseInt(limit);

    const conditions = [
      "a.is_deleted = false",
      "e.is_deleted = false",
      "e.status = 'Active'",
      "a.org_id = $1",
    ];
    const params = [org_id];
    let idx = 2;

    if (employee_id) {
      if (isNaN(employee_id)) {
        conditions.push(`e.employee_code = $${idx++}`);
        params.push(employee_id);
      } else {
        conditions.push(`a.employee_id = $${idx++}`);
        params.push(parseInt(employee_id));
      }
    }

    if (shift_type) {
      conditions.push(`COALESCE(s.shift_name, a.shift_type, 'General') ILIKE $${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
       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}`,
      params
    );
    const total = parseInt(countResult.rows[0].total);

    // ✅ FIXED: Removed e.department and e.designation
    const dataResult = await pool.query(
      `SELECT
         a.*,
         e.employee_code, e.first_name, e.last_name,
         COALESCE(d.dept_name, 'Unknown') AS department,
         COALESCE(des.designation_name, 'Unknown') AS designation,
         e.email, e.phone,
         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 departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       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++}::integer OFFSET $${idx++}::integer`,
      [...params, parseInt(limit), offset]
    );

    res.json({
      success: true,
      data: dataResult.rows,
      pagination: {
        page: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalRecords: total,
        limit: parseInt(limit),
      },
    });
  } catch (error) {
    console.error("GET ATTENDANCE ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};
/* ===========================
   ✅ DAILY RESET
   FIX: Does NOT pre-fill check_in/check_out on reset.
   Actual times should only come from real check-in/check-out actions.
   This prevents false "0 overtime" from shift-defaults overwriting real data.
=========================== */
// 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);
    
//     // ✅ Default to "Present" with shift times
//     const defaultStatus = isHoliday ? "Holiday" : "Present";

//     const employeesResult = await pool.query(
//       `SELECT e.id, e.employee_code,
//               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,
//               s.id AS shift_id
//        FROM employees e
//        LEFT JOIN shift_group_members sgm ON e.id = sgm.employee_id
//        LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
//        LEFT JOIN shift_plan_cells spc ON spc.group_id = sgm.group_id AND spc.assign_date = $1
//        LEFT JOIN shifts s ON s.id = spc.shift_id
//        WHERE e.org_id = $2 AND e.status = 'Active' AND e.is_deleted = false`,
//       [today, org_id]
//     );

//     let createdCount = 0, skippedCount = 0;
//     const syncIds = [];

//     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) {
//         skippedCount++;
//         continue;
//       }

//       // Auto-fill with shift times
//       const autoCheckIn = emp.shift_start_time;
//       const autoCheckOut = emp.shift_end_time;
//       const autoWorkHours = autoCheckIn && autoCheckOut ? calculateWorkHours(autoCheckIn, autoCheckOut) : 0;
//       const autoOvertimeHours = autoWorkHours > (emp.shift_working_hours || 8) 
//         ? parseFloat((autoWorkHours - (emp.shift_working_hours || 8)).toFixed(2)) 
//         : 0;

//       const insertResult = await pool.query(
//         `INSERT INTO attendance (
//            employee_id, employee_code, attendance_date, attendance_status,
//            shift_type, shift_start_time, shift_end_time,
//            check_in_time, check_out_time,
//            work_hours, overtime_hours,
//            is_late, late_minutes,
//            original_shift_id, actual_shift_id,
//            org_id, created_at, updated_at
//          ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,NOW(),NOW())
//          RETURNING id`,
//         [
//           emp.id, emp.employee_code, today, defaultStatus,
//           emp.shift_name, emp.shift_start_time, emp.shift_end_time,
//           autoCheckIn, autoCheckOut, autoWorkHours, autoOvertimeHours,
//           false, 0, emp.shift_id, emp.shift_id, org_id,
//         ]
//       );

//       syncIds.push(insertResult.rows[0].id);
//       createdCount++;
//     }

//     await Promise.all(syncIds.map((sid) => syncBufferForRecord(sid)));

//     res.json({
//       success: true,
//       message: `Daily reset done. ${createdCount} created, ${skippedCount} skipped.`,
//       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 });
//   }
// };
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";

    // ✅ FIX: Get complete shift info from shift_plan_cells
    const employeesResult = await pool.query(
      `SELECT DISTINCT ON (e.id)
         e.id, 
         e.employee_code,
         e.org_id,
         sg.id AS group_id,
         sg.group_name,
         s.id AS shift_id,           -- ← Get shift_id
         s.shift_name,               -- ← Get shift_name
         s.start_time AS shift_start_time,
         s.end_time AS shift_end_time,
         s.working_hours AS shift_working_hours,
         spc.assign_date,
         spc.col_index
       FROM employees e
       LEFT JOIN shift_group_members sgm ON e.id = sgm.employee_id
       LEFT JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
       LEFT JOIN shift_plan_cells spc ON spc.group_id = sgm.group_id 
         AND spc.assign_date = $1
         AND spc.org_id = e.org_id
         AND spc.is_deleted = false
       LEFT JOIN shifts s ON s.id = spc.shift_id AND s.is_deleted = false
       WHERE e.org_id = $2 
         AND e.status = 'Active' 
         AND e.is_deleted = false
       ORDER BY e.id, spc.assign_date DESC`,
      [today, org_id]
    );

    let createdCount = 0, skippedCount = 0;
    const syncIds = [];

    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) { 
        skippedCount++; 
        continue; 
      }

      // ✅ FIX: Use shift times from plan
      const shiftStart = emp.shift_start_time;
      const shiftEnd = emp.shift_end_time;
      const shiftId = emp.shift_id;
      const shiftType = emp.shift_name || (shiftId ? "Assigned Shift" : "General");
      
      // Auto-fill check-in/out with shift times
      const autoCheckIn = shiftStart;
      const autoCheckOut = shiftEnd;
      const autoWorkHours = autoCheckIn && autoCheckOut ? calculateWorkHours(autoCheckIn, autoCheckOut) : 0;
      const autoOvertimeHours = autoWorkHours > (emp.shift_working_hours || 8) 
        ? parseFloat((autoWorkHours - (emp.shift_working_hours || 8)).toFixed(2)) 
        : 0;

      const insertResult = await pool.query(
        `INSERT INTO attendance (
           employee_id, employee_code, attendance_date, attendance_status,
           shift_type, shift_start_time, shift_end_time, shift_working_hours,
           check_in_time, check_out_time,
           work_hours, overtime_hours,
           is_late, late_minutes,
           original_shift_id, actual_shift_id, shift_id,  -- ← ADD shift_id
           org_id, created_at, updated_at
         ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,NOW(),NOW())
         RETURNING id`,
        [
          emp.id, emp.employee_code, today, defaultStatus,
          shiftType,                        // ← shift_type from shift_name
          shiftStart, shiftEnd,             // ← shift_start_time, shift_end_time
          emp.shift_working_hours || 8,
          autoCheckIn, autoCheckOut,        // ← check_in_time, check_out_time
          autoWorkHours, autoOvertimeHours,
          false, 0,
          shiftId, shiftId,                 // ← original_shift_id, actual_shift_id
          shiftId,                          // ← shift_id (NEW column)
          org_id,
        ]
      );

      syncIds.push(insertResult.rows[0].id);
      createdCount++;
    }

    await Promise.all(syncIds.map((sid) => syncBufferForRecord(sid)));

    res.json({
      success: true,
      message: `Daily reset done. ${createdCount} created, ${skippedCount} skipped.`,
      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 });
  }
};

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

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

    const result = await pool.query(
      `SELECT DISTINCT ON (e.id)
         e.id, e.employee_code,
         CONCAT(e.first_name,' ',e.last_name) AS employee_name,
         e.department, e.designation,
         sg.group_name,
         COALESCE(s.shift_name, 'General') AS shift_name,
         s.start_time AS shift_start_time, s.end_time AS shift_end_time,
         COALESCE(a.attendance_status, 'Absent') AS attendance_status,
         a.check_in_time, a.check_out_time,
         a.work_hours, a.overtime_hours,
         a.is_late, a.late_minutes, a.is_checked_out,
         a.id AS attendance_id
       FROM employees e
       JOIN shift_group_members sgm ON e.id = sgm.employee_id
       JOIN shift_groups sg ON sgm.group_id = sg.id AND sg.is_deleted = false
LEFT JOIN shift_plan_cells spc
  ON spc.group_id = sgm.group_id
 AND spc.assign_date = $1
 AND spc.org_id = e.org_id

LEFT JOIN shifts s
  ON s.id = spc.shift_id
 AND s.is_deleted = false
       LEFT JOIN attendance a
         ON e.id = a.employee_id AND a.attendance_date = $1 AND a.is_deleted = false
       WHERE sgm.group_id = $2 AND e.org_id = $3
         AND e.status = 'Active' AND e.is_deleted = false`,
      [targetDate, group_id, org_id]
    );

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



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

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

    if (!org_id) {
      return res.status(401).json({ success: false, message: "Session expired" });
    }

    // Get shift assignment for this group on this date
    const plannerResult = await pool.query(
      `SELECT 
         spc.id AS cell_id,
         spc.plan_id,
         spc.group_id,
         spc.shift_id,
         spc.assign_date,
         spc.col_index,
         sg.group_name,
         s.shift_name,
         s.shift_code,
         s.start_time,
         s.end_time,
         s.working_hours
       FROM shift_plan_cells spc
       LEFT JOIN shift_groups sg ON sg.id = spc.group_id AND sg.is_deleted = false
       LEFT JOIN shifts s ON s.id = spc.shift_id AND s.is_deleted = false
       WHERE spc.group_id = $1 
         AND spc.assign_date = $2 
         AND spc.org_id = $3
       ORDER BY spc.col_index
       LIMIT 1`,
      [group_id, targetDate, org_id]
    );

    const shiftStartTime = plannerResult.rows[0]?.start_time || null;
    const shiftEndTime = plannerResult.rows[0]?.end_time || null;
    const shiftName = plannerResult.rows[0]?.shift_name || 'No Shift Assigned';
    const shiftWorkingHours = plannerResult.rows[0]?.working_hours || 8;

    // Get employees with attendance records
    const employeesResult = await pool.query(
      `SELECT
          e.id,
          e.employee_code,
          CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
          e.profile_image,
          COALESCE(d.dept_name, 'Unknown') AS department,
          COALESCE(des.designation_name, 'Unknown') AS designation,
          sg.id AS group_id,
          sg.group_name,
          a.id AS attendance_id,
          a.attendance_date,
          a.attendance_status,
          a.check_in_time,
          a.check_out_time,
          COALESCE(a.work_hours, 0) AS work_hours,
          COALESCE(a.overtime_hours, 0) AS overtime_hours,
          COALESCE(a.is_late, false) AS is_late,
          COALESCE(a.late_minutes, 0) AS late_minutes,
          COALESCE(a.is_checked_out, false) AS is_checked_out,
          COALESCE(a.is_double_shift, false) AS is_double_shift,
          a.office_location,
          a.remarks,
          CASE WHEN a.id IS NULL THEN false ELSE true END AS is_marked
       FROM shift_group_members sgm
       JOIN shift_groups sg ON sg.id = sgm.group_id AND sg.is_deleted = false
       JOIN employees e ON e.id = sgm.employee_id 
         AND e.status = 'Active' 
         AND e.is_deleted = false 
         AND e.org_id = $3
       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
       LEFT JOIN attendance a ON a.employee_id = e.id 
         AND a.attendance_date = $2 
         AND a.org_id = $3 
         AND a.is_deleted = false
       WHERE sgm.group_id = $1
       ORDER BY e.first_name, e.last_name`,
      [group_id, targetDate, org_id]
    );

    // ✅ CRITICAL FIX: Copy shift times to check_in/out if NULL
    const enrichedEmployees = employeesResult.rows.map(emp => ({
      ...emp,
      shift_name: shiftName,
      shift_start_time: shiftStartTime,
      shift_end_time: shiftEndTime,
      shift_working_hours: shiftWorkingHours,
      // ✅ If check_in_time is NULL, use shift_start_time
      check_in_time: emp.check_in_time || shiftStartTime,
      // ✅ If check_out_time is NULL, use shift_end_time
      check_out_time: emp.check_out_time || shiftEndTime,
      // ✅ Set default attendance_status based on shift availability
      attendance_status: emp.attendance_status || (shiftStartTime ? "Present" : "Absent"),
    }));

    return res.json({
      success: true,
      data: enrichedEmployees,
      shift_info: plannerResult.rows[0] ? {
        cell_id: plannerResult.rows[0].cell_id,
        plan_id: plannerResult.rows[0].plan_id,
        group_id: plannerResult.rows[0].group_id,
        shift_id: plannerResult.rows[0].shift_id,
        assign_date: plannerResult.rows[0].assign_date,
        col_index: plannerResult.rows[0].col_index,
        group_name: plannerResult.rows[0].group_name,
        shift_name: shiftName,
        shift_start_time: shiftStartTime,
        shift_end_time: shiftEndTime,
        shift_working_hours: shiftWorkingHours
      } : null,
      total: enrichedEmployees.length
    });
  } catch (err) {
    console.error("GET EMPLOYEES BY GROUP ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ BACKFILL NULL work_hours
   Call this endpoint ONCE to fix all existing rows that have times but NULL work_hours.
   Route: POST /attendance/backfill-work-hours
=========================== */
const backfillWorkHours = async (req, res) => {
  const org_id = req.session.user.orgId;
  try {
    // Step 1: Update work_hours and overtime_hours for rows with times but NULL work_hours
    const fixResult = await pool.query(
      `UPDATE attendance a
       SET
         work_hours = ROUND(
           EXTRACT(EPOCH FROM (
             check_out_time::time - check_in_time::time +
             CASE WHEN check_out_time::time < check_in_time::time
                  THEN INTERVAL '24 hours' ELSE INTERVAL '0' END
           )) / 3600.0, 2
         ),
         overtime_hours = GREATEST(0, ROUND(
           EXTRACT(EPOCH FROM (
             check_out_time::time - check_in_time::time +
             CASE WHEN check_out_time::time < check_in_time::time
                  THEN INTERVAL '24 hours' ELSE INTERVAL '0' END
           )) / 3600.0, 2
         ) - COALESCE(s.working_hours, 8), 2)),
         attendance_status = CASE
           WHEN
             ROUND(
               EXTRACT(EPOCH FROM (
                 check_out_time::time - check_in_time::time +
                 CASE WHEN check_out_time::time < check_in_time::time
                      THEN INTERVAL '24 hours' ELSE INTERVAL '0' END
               )) / 3600.0, 2
             ) > COALESCE(s.working_hours, 8)
             AND a.attendance_status NOT IN ('Holiday','Leave','Half Day','Cross Shift','Shift Swap','Double Shift','Late')
           THEN 'Overtime'
           ELSE a.attendance_status
         END,
         updated_at = NOW()
       FROM shift_assignments sa2
       LEFT JOIN shifts s ON sa2.shift_id = s.id AND s.is_deleted = false
       WHERE a.employee_id = sa2.employee_id
         AND sa2.is_active = true AND sa2.is_deleted = false
         AND a.check_in_time IS NOT NULL
         AND a.check_out_time IS NOT NULL
         AND a.work_hours IS NULL
         AND a.is_deleted = false
         AND a.org_id = $1`,
      [org_id]
    );

    // Step 2: Also fix rows where work_hours = 0 but both times exist (edge case from old resets)
    const fixZeroResult = await pool.query(
      `UPDATE attendance a
       SET
         work_hours = ROUND(
           EXTRACT(EPOCH FROM (
             check_out_time::time - check_in_time::time +
             CASE WHEN check_out_time::time < check_in_time::time
                  THEN INTERVAL '24 hours' ELSE INTERVAL '0' END
           )) / 3600.0, 2
         ),
         overtime_hours = GREATEST(0, ROUND(
           EXTRACT(EPOCH FROM (
             check_out_time::time - check_in_time::time +
             CASE WHEN check_out_time::time < check_in_time::time
                  THEN INTERVAL '24 hours' ELSE INTERVAL '0' END
           )) / 3600.0, 2
         ) - COALESCE(s.working_hours, 8), 2)),
         updated_at = NOW()
       FROM shift_assignments sa2
       LEFT JOIN shifts s ON sa2.shift_id = s.id AND s.is_deleted = false
       WHERE a.employee_id = sa2.employee_id
         AND sa2.is_active = true AND sa2.is_deleted = false
         AND a.check_in_time IS NOT NULL
         AND a.check_out_time IS NOT NULL
         AND a.work_hours = 0
         AND a.overtime_hours = 0
         AND a.attendance_status NOT IN ('Absent','Holiday','Leave')
         AND a.is_deleted = false
         AND a.org_id = $1`,
      [org_id]
    );

    // Step 3: Recalculate entire buffer for this org
    await pool.query(
      `DELETE FROM attendance_buffer
WHERE org_id = $1`,
      [org_id]
    );

    const bufferResult = await pool.query(
      `INSERT INTO attendance_buffer (
         attendance_id, employee_id, employee_code, employee_name,
         shift_type, attendance_date, check_in_time, check_out_time,
         shift_start_time, shift_end_time, total_hours, overtime_hours,
         late_minutes, attendance_status, month_year, org_id,
         is_present, is_absent, is_late, is_holiday, is_leave,
         created_at, updated_at
       )
       SELECT
         a.id, e.id, e.employee_code,
         CONCAT(e.first_name, ' ', e.last_name),
         a.shift_type, a.attendance_date, a.check_in_time, a.check_out_time,
         a.shift_start_time, a.shift_end_time, a.work_hours, a.overtime_hours,
         a.late_minutes, a.attendance_status,
         TO_CHAR(a.attendance_date, 'YYYY-MM'),
         a.org_id,
         (a.attendance_status = 'Present'),
         (a.attendance_status = 'Absent'),
         (a.attendance_status = 'Late'),
         (a.attendance_status = 'Holiday'),
         (a.attendance_status = 'Leave'),
         NOW(), NOW()
       FROM attendance a
       JOIN employees e ON a.employee_id = e.id AND e.status = 'Active' AND e.is_deleted = false
       WHERE a.is_deleted = false AND a.org_id = $1
       ON CONFLICT (attendance_id) DO UPDATE SET
         check_in_time     = EXCLUDED.check_in_time,
         check_out_time    = EXCLUDED.check_out_time,
         total_hours       = EXCLUDED.total_hours,
         overtime_hours    = EXCLUDED.overtime_hours,
         late_minutes      = EXCLUDED.late_minutes,
         attendance_status = EXCLUDED.attendance_status,
         is_present        = EXCLUDED.is_present,
         is_absent         = EXCLUDED.is_absent,
         is_late           = EXCLUDED.is_late,
         is_holiday        = EXCLUDED.is_holiday,
         is_leave          = EXCLUDED.is_leave,
         is_deleted        = false,
         updated_at        = NOW()`,
      [org_id]
    );

    res.json({
      success: true,
      message: "Backfill complete. All work_hours and overtime_hours recalculated.",
      data: {
        null_rows_fixed:  fixResult.rowCount,
        zero_rows_fixed:  fixZeroResult.rowCount,
        buffer_synced:    bufferResult.rowCount,
      },
    });
  } catch (error) {
    console.error("BACKFILL WORK HOURS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ Unchanged helpers
=========================== */
const getAttendanceById = async (req, res) => { /* same as original */ };

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" });

    // Also soft-delete from buffer
    await pool.query(
      `UPDATE attendance_buffer SET is_deleted = true, updated_at = NOW()
       WHERE attendance_id = $1`,
      [id]
    );

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

const getAttendanceSummary    = async (req, res) => { /* same as original */ };
const getLateAttendanceReport = async (req, res) => { /* same as original */ };

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) {
    res.status(500).json({ success: false, message: error.message });
  }
};


const getShiftPlanSchedule = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const { start_date, end_date } = req.query;

    if (!start_date || !end_date) {
      return res.status(400).json({ success: false, message: "start_date and end_date are required" });
    }

    const result = await pool.query(
      `SELECT 
         spc.id,
         spc.plan_id,
         spc.group_id,
         sg.group_name,
         spc.shift_id,
         s.shift_name,
         s.start_time,
         s.end_time,
         s.working_hours,
         spc.assign_date,
         spc.col_index
       FROM shift_plan_cells spc
       JOIN shift_groups sg ON sg.id = spc.group_id AND sg.is_deleted = false
       LEFT JOIN shifts s ON s.id = spc.shift_id AND s.is_deleted = false
       WHERE spc.org_id = $1
         AND spc.assign_date BETWEEN $2 AND $3
         AND spc.is_deleted = false
       ORDER BY spc.assign_date, spc.col_index, sg.group_name`,
      [org_id, start_date, end_date]
    );

    // Group by date
    const scheduleByDate = {};
    result.rows.forEach(row => {
      if (!scheduleByDate[row.assign_date]) {
        scheduleByDate[row.assign_date] = [];
      }
      scheduleByDate[row.assign_date].push(row);
    });

    return res.json({
      success: true,
      data: scheduleByDate,
      meta: {
        start_date,
        end_date,
        total_days: Object.keys(scheduleByDate).length,
        total_cells: result.rows.length
      }
    });
  } catch (err) {
    console.error("GET SHIFT PLAN SCHEDULE ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};
const updateAttendanceTime = async (req, res) => { /* same as original */ };
const debugAttendance      = async (req, res) => { /* same as original */ };



// Updated attendanceManagementController.js - Key changes only


// Enhanced syncBufferForRecord with department info
// const syncBufferForRecord = async (attendanceId) => {
//   try {
//     await pool.query(
//       `INSERT INTO attendance_buffer (
//          attendance_id, employee_id, employee_code, employee_name,
//          shift_type, attendance_date, check_in_time, check_out_time,
//          shift_start_time, shift_end_time, total_hours, overtime_hours,
//          late_minutes, attendance_status, month_year, org_id, department_id,
//          is_present, is_absent, is_late, is_holiday, is_leave,
//          created_at, updated_at
//        )
//        SELECT
//          a.id, e.id, e.employee_code,
//          CONCAT(e.first_name, ' ', e.last_name),
//          a.shift_type, a.attendance_date, a.check_in_time, a.check_out_time,
//          a.shift_start_time, a.shift_end_time, a.work_hours, a.overtime_hours,
//          a.late_minutes, a.attendance_status,
//          TO_CHAR(a.attendance_date, 'YYYY-MM'),
//          a.org_id, e.department_id,
//          (a.attendance_status = 'Present'),
//          (a.attendance_status = 'Absent'),
//          (a.attendance_status = 'Late'),
//          (a.attendance_status = 'Holiday'),
//          (a.attendance_status = 'Leave'),
//          NOW(), NOW()
//        FROM attendance a
//        JOIN employees e ON a.employee_id = e.id
//        WHERE a.id = $1
//        ON CONFLICT (attendance_id) DO UPDATE SET
//          check_in_time = EXCLUDED.check_in_time,
//          check_out_time = EXCLUDED.check_out_time,
//          total_hours = EXCLUDED.total_hours,
//          overtime_hours = EXCLUDED.overtime_hours,
//          attendance_status = EXCLUDED.attendance_status,
//          updated_at = NOW()`,
//       [attendanceId]
//     );
    
//     // ✅ TRIGGER REAL-TIME PAYROLL RECALCULATION
//     await recalcAffectedPayrollRecords(attendanceId);
    
//   } catch (err) {
//     console.error(`syncBufferForRecord failed:`, err.message);
//   }
// };

// NEW FUNCTION: Recalculate payroll when attendance changes
// const recalcAffectedPayrollRecords = async (attendanceId) => {
//   try {
//     // Get the employee and date from attendance record
//     const attResult = await pool.query(
//       `SELECT employee_id, attendance_date FROM attendance WHERE id = $1`,
//       [attendanceId]
//     );
    
//     if (attResult.rows.length === 0) return;
    
//     const { employee_id, attendance_date } = attResult.rows[0];
//     const month = new Date(attendance_date).getMonth() + 1;
//     const year = new Date(attendance_date).getFullYear();
    
//     // Mark payroll records as stale (needs recalculation)
//     await pool.query(
//       `UPDATE payroll_records 
//        SET needs_recalculation = true, 
//            updated_at = NOW()
//        WHERE employee_id = $1 
//          AND month = $2 
//          AND year = $3
//          AND is_deleted = false`,
//       [employee_id, month, year]
//     );
    
//     // Optional: Auto-recalculate if configured
//     if (process.env.AUTO_RECALCULATE_PAYROLL === 'true') {
//       const result = await pool.query(
//         `SELECT id, employee_id, month, year, department_id, gross_salary 
//          FROM payroll_records 
//          WHERE employee_id = $1 AND month = $2 AND year = $3 AND needs_recalculation = true`,
//         [employee_id, month, year]
//       );
      
//       for (const record of result.rows) {
//         await recalculateSinglePayroll(record.id);
//       }
//     }
    
//   } catch (err) {
//     console.error("recalcAffectedPayrollRecords error:", err);
//   }
// };

// NEW FUNCTION: Recalculate a single payroll record
const recalculateSinglePayroll = async (payrollId) => {
  try {
    const record = await pool.query(
      `SELECT pr.*, e.salary, e.department_id 
       FROM payroll_records pr
       JOIN employees e ON e.id = pr.employee_id
       WHERE pr.id = $1 AND pr.is_deleted = false`,
      [payrollId]
    );
    
    if (record.rows.length === 0) return;
    
    const { employee_id, month, year, department_id, salary } = record.rows[0];
    
    const engine = new DeductionEngine(record.rows[0].org_id, month, year);
    const payroll = await engine.calculateEmployeePayroll(employee_id, department_id, parseFloat(salary));
    
    await pool.query(
      `UPDATE payroll_records SET
         present_days = $1, absent_days = $2, late_days = $3,
         half_days = $4, loss_of_pay_days = $5, paid_days = $6,
         overtime_hours = $7, ot_payment = $8,
         gross_salary = $9, total_earnings = $10,
         pf_employee = $11, pf_employer = $12,
         esi_employee = $13, esi_employer = $14,
         professional_tax = $15, tds = $16,
         late_deduction = $17, absent_deduction = $18,
         half_day_deduction = $19, lop_deduction = $20,
         total_deductions = $21, net_salary = $22,
         needs_recalculation = false, updated_at = NOW()
       WHERE id = $23`,
      [
        payroll.attendance.present_days,
        payroll.attendance.absent_days,
        payroll.attendance.late_days,
        payroll.attendance.half_days,
        payroll.loss_of_pay_days,
        payroll.paid_days,
        payroll.attendance.total_overtime_hours,
        payroll.ot_payment,
        payroll.prorated_gross,
        payroll.total_earnings,
        payroll.deductions.standard.pf_employee,
        payroll.deductions.standard.pf_employee, // employer matches employee for simplicity
        0, 0, // ESI placeholders
        payroll.deductions.standard.professional_tax,
        payroll.deductions.standard.tds,
        payroll.deductions.attendance.late_deduction,
        payroll.deductions.attendance.absent_deduction,
        payroll.deductions.attendance.half_day_deduction,
        payroll.deductions.attendance.loss_of_pay_deduction,
        payroll.deductions.total,
        payroll.net_salary,
        payrollId
      ]
    );
    
  } catch (err) {
    console.error("recalculateSinglePayroll error:", err);
  }
};
module.exports = {
  createAttendance,
  checkIn,
  checkOut,
  individualCheckout,
  debugAttendance,
  getTodayAttendance,
  updateIndividualStatus,
  bulkMarkAttendance,
  bulkCheckout,
  getAttendance,
  getAttendanceById,
  updateAttendance,
  deleteAttendance,
  getAttendanceSummary,
  getLateAttendanceReport,
  getEmployeesByShift,
  getEmployeesByGroup,
  dailyReset,
  getShifts,
  updateAttendanceTime,
  backfillWorkHours, // NEW: register this route as POST /attendance/backfill-work-hours
  getShiftPlanSchedule ,
  syncBufferForRecord ,
  recalcAffectedPayrollRecords ,
  recalculateSinglePayroll ,
  getEmployeeTransactions,
  syncAttendanceFromTransactions,
};