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

// /* ===========================
//    ✅ ASSIGN SHIFT TO EMPLOYEE
// =========================== */

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

//     let {
//       employee_code,
//       shift_id,
//       assignment_date,
//       effective_from,
//       effective_to,
//       is_permanent,
//       reason,
//       approved_by,
//       org_id,
//       created_by
//     } = req.body;

//     // Validation
//     if (!employee_code || !shift_id || !assignment_date || !org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee code, shift ID, assignment date, and org_id are required",
//       });
//     }

//     // Fetch employee id
//     const employeeResult = await pool.query(
//       `SELECT id, employee_code, first_name, last_name 
//        FROM employees 
//        WHERE employee_code = $1 AND is_deleted = false`,
//       [employee_code]
//     );

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

//     const realEmployeeId = employeeResult.rows[0].id;

//     // Check if shift exists
//     const shiftResult = await pool.query(
//       `SELECT id, shift_name FROM shifts WHERE id = $1 AND is_deleted = false`,
//       [shift_id]
//     );

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

//     // Check if employee already has an assignment for this date
//     const existingAssignment = await pool.query(
//       `SELECT id FROM shift_assignments 
//        WHERE employee_id = $1 
//          AND assignment_date = $2 
//          AND is_deleted = false`,
//       [realEmployeeId, assignment_date]
//     );

//     if (existingAssignment.rows.length > 0) {
//       // Update existing assignment
//       const result = await pool.query(
//         `
//         UPDATE shift_assignments
//         SET shift_id = $1,
//             effective_from = $2,
//             effective_to = $3,
//             is_permanent = $4,
//             reason = $5,
//             approved_by = $6,
//             updated_by = $7,
//             updated_at = NOW()
//         WHERE id = $8
//         RETURNING *
//         `,
//         [
//           shift_id,
//           effective_from || assignment_date,
//           effective_to || null,
//           is_permanent || false,
//           reason || null,
//           approved_by || null,
//           created_by || null,
//           existingAssignment.rows[0].id
//         ]
//       );

//       return res.status(200).json({
//         success: true,
//         message: `Shift updated successfully for ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name}`,
//         data: result.rows[0],
//       });
//     } else {
//       // Insert new assignment
//       const result = await pool.query(
//         `
//         INSERT INTO shift_assignments (
//           employee_id,
//           shift_id,
//           assignment_date,
//           effective_from,
//           effective_to,
//           is_permanent,
//           is_active,
//           reason,
//           approved_by,
//           org_id,
//           created_by,
//           created_at,
//           updated_at
//         )
//         VALUES ($1, $2, $3, $4, $5, $6, true, $7, $8, $9, $10, NOW(), NOW())
//         RETURNING *
//         `,
//         [
//           realEmployeeId,
//           shift_id,
//           assignment_date,
//           effective_from || assignment_date,
//           effective_to || null,
//           is_permanent || false,
//           reason || null,
//           approved_by || null,
//           org_id,
//           created_by || null
//         ]
//       );

//       return res.status(201).json({
//         success: true,
//         message: `Shift "${shiftResult.rows[0].shift_name}" assigned to ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} successfully`,
//         data: result.rows[0],
//       });
//     }
//   } catch (error) {
//     console.error("ASSIGN SHIFT ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };

// /* ===========================
//    ✅ GET ALL SHIFT ASSIGNMENTS
// =========================== */

// const getShiftAssignments = async (req, res) => {
//   try {
//     const { 
//       page = 1, 
//       limit = 10, 
//       employee_id,
//       shift_id,
//       is_active,
//       start_date,
//       end_date,
//       org_id 
//     } = req.query;
    
//     const offset = (page - 1) * limit;
//     let query = `
//       SELECT
//         sa.*,
//         e.employee_code,
//         e.first_name,
//         e.last_name,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation,
//         s.shift_name,
//         s.shift_code,
//         s.start_time,
//         s.end_time,
//         CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name
//       FROM shift_assignments sa
//       JOIN employees e ON sa.employee_id = e.id
//       JOIN shifts s ON sa.shift_id = s.id
//       LEFT JOIN employees approver ON sa.approved_by = approver.id
//       WHERE sa.is_deleted = false
//     `;
    
//     const queryParams = [];
//     let paramIndex = 1;
    
//     if (employee_id) {
//       query += ` AND e.employee_code = $${paramIndex}`;
//       queryParams.push(employee_id);
//       paramIndex++;
//     }
    
//     if (shift_id) {
//       query += ` AND sa.shift_id = $${paramIndex}`;
//       queryParams.push(shift_id);
//       paramIndex++;
//     }
    
//     if (is_active !== undefined) {
//       query += ` AND sa.is_active = $${paramIndex}`;
//       queryParams.push(is_active === 'true');
//       paramIndex++;
//     }
    
//     if (start_date) {
//       query += ` AND sa.assignment_date >= $${paramIndex}`;
//       queryParams.push(start_date);
//       paramIndex++;
//     }
    
//     if (end_date) {
//       query += ` AND sa.assignment_date <= $${paramIndex}`;
//       queryParams.push(end_date);
//       paramIndex++;
//     }
    
//     if (org_id) {
//       query += ` AND sa.org_id = $${paramIndex}`;
//       queryParams.push(org_id);
//       paramIndex++;
//     }
    
//     // Get total count
//     const countQuery = query.replace(
//       /SELECT.*FROM/,
//       "SELECT COUNT(*) as total FROM"
//     );
//     const countResult = await pool.query(countQuery, queryParams);
//     const totalRecords = parseInt(countResult.rows[0].total);
    
//     // Add pagination
//     query += ` ORDER BY sa.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
//     queryParams.push(limit, offset);
    
//     const result = await pool.query(query, queryParams);
    
//     res.json({
//       success: true,
//       data: result.rows,
//       pagination: {
//         currentPage: parseInt(page),
//         totalPages: Math.ceil(totalRecords / limit),
//         totalRecords: totalRecords,
//         recordsPerPage: parseInt(limit)
//       }
//     });
//   } catch (err) {
//     console.error("FETCH SHIFT ASSIGNMENTS ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };

// /* ===========================
//    ✅ GET SHIFT ASSIGNMENT BY ID
// =========================== */

// const getShiftAssignmentById = async (req, res) => {
//   try {
//     const { id } = req.params;
    
//     const result = await pool.query(
//       `
//       SELECT
//         sa.*,
//         e.employee_code,
//         e.first_name,
//         e.last_name,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation,
//         s.shift_name,
//         s.shift_code,
//         s.start_time,
//         s.end_time,
//         CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name
//       FROM shift_assignments sa
//       JOIN employees e ON sa.employee_id = e.id
//       JOIN shifts s ON sa.shift_id = s.id
//       LEFT JOIN employees approver ON sa.approved_by = approver.id
//       WHERE sa.id = $1 AND sa.is_deleted = false
//       `,
//       [id]
//     );
    
//     if (!result.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "Shift assignment not found",
//       });
//     }
    
//     res.status(200).json({
//       success: true,
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("GET SHIFT ASSIGNMENT BY ID ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: "Fetch failed",
//     });
//   }
// };

// /* ===========================
//    ✅ UPDATE SHIFT ASSIGNMENT
// =========================== */

// const updateShiftAssignment = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const {
//       shift_id,
//       effective_from,
//       effective_to,
//       is_permanent,
//       is_active,
//       reason,
//       approved_by,
//       updated_by
//     } = req.body;
    
//     // Check if assignment exists
//     const existing = await pool.query(
//       `SELECT * FROM shift_assignments WHERE id = $1 AND is_deleted = false`,
//       [id]
//     );
    
//     if (!existing.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "Shift assignment not found",
//       });
//     }
    
//     const old = existing.rows[0];
    
//     const result = await pool.query(
//       `
//       UPDATE shift_assignments
//       SET
//         shift_id = COALESCE($1, shift_id),
//         effective_from = COALESCE($2, effective_from),
//         effective_to = COALESCE($3, effective_to),
//         is_permanent = COALESCE($4, is_permanent),
//         is_active = COALESCE($5, is_active),
//         reason = COALESCE($6, reason),
//         approved_by = COALESCE($7, approved_by),
//         updated_by = COALESCE($8, updated_by),
//         updated_at = NOW()
//       WHERE id = $9
//       RETURNING *
//       `,
//       [
//         shift_id || old.shift_id,
//         effective_from || old.effective_from,
//         effective_to !== undefined ? effective_to : old.effective_to,
//         is_permanent !== undefined ? is_permanent : old.is_permanent,
//         is_active !== undefined ? is_active : old.is_active,
//         reason || old.reason,
//         approved_by || old.approved_by,
//         updated_by || old.updated_by,
//         id
//       ]
//     );
    
//     res.status(200).json({
//       success: true,
//       message: "Shift assignment updated successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("UPDATE SHIFT ASSIGNMENT ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };

// /* ===========================
//    ✅ DELETE SHIFT ASSIGNMENT (Soft Delete)
// =========================== */

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

// /* ===========================
//    ✅ GET EMPLOYEE CURRENT SHIFT
// =========================== */

// const getEmployeeCurrentShift = async (req, res) => {
//   try {
//     const { employee_code } = req.params;
    
//     if (!employee_code) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee code is required",
//       });
//     }
    
//     const result = await pool.query(
//       `
//       SELECT
//         sa.id,
//         sa.assignment_date,
//         sa.effective_from,
//         sa.effective_to,
//         sa.is_permanent,
//         sa.is_active,
//         sa.reason,
//         s.id as shift_id,
//         s.shift_name,
//         s.shift_code,
//         s.start_time,
//         s.end_time,
//         s.total_hours,
//         s.working_hours,
//         s.lunch_start,
//         s.lunch_end,
//         s.first_break_start,
//         s.first_break_end,
//         s.second_break_start,
//         s.second_break_end,
//         CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name
//       FROM shift_assignments sa
//       JOIN shifts s ON sa.shift_id = s.id
//       JOIN employees e ON sa.employee_id = e.id
//       LEFT JOIN employees approver ON sa.approved_by = approver.id
//       WHERE e.employee_code = $1 
//         AND sa.is_active = true 
//         AND sa.is_deleted = false
//         AND s.is_deleted = false
//       ORDER BY sa.created_at DESC
//       LIMIT 1
//       `,
//       [employee_code]
//     );
    
//     if (!result.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "No active shift found for this employee",
//       });
//     }
    
//     res.status(200).json({
//       success: true,
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("GET EMPLOYEE CURRENT SHIFT ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };

// /* ===========================
//    ✅ GET EMPLOYEE SHIFT HISTORY
// =========================== */

// const getEmployeeShiftHistory = async (req, res) => {
//   try {
//     const { employee_code } = req.params;
//     const { limit = 10 } = req.query;
    
//     if (!employee_code) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee code is required",
//       });
//     }
    
//     const result = await pool.query(
//       `
//       SELECT
//         sa.id,
//         sa.assignment_date,
//         sa.effective_from,
//         sa.effective_to,
//         sa.is_permanent,
//         sa.is_active,
//         sa.reason,
//         sa.created_at,
//         sa.created_by,
//         s.shift_name,
//         s.shift_code,
//         s.start_time,
//         s.end_time,
//         CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name
//       FROM shift_assignments sa
//       JOIN shifts s ON sa.shift_id = s.id
//       JOIN employees e ON sa.employee_id = e.id
//       LEFT JOIN employees approver ON sa.approved_by = approver.id
//       WHERE e.employee_code = $1 
//         AND sa.is_deleted = false
//       ORDER BY sa.created_at DESC
//       LIMIT $2
//       `,
//       [employee_code, limit]
//     );
    
//     res.json({
//       success: true,
//       data: result.rows,
//       totalRecords: result.rows.length,
//     });
//   } catch (err) {
//     console.error("GET EMPLOYEE SHIFT HISTORY ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };

// /* ===========================
//    ✅ GET SHIFT ASSIGNMENTS BY SHIFT
// =========================== */

// const getAssignmentsByShift = async (req, res) => {
//   try {
//     const { shift_id } = req.params;
//     const { is_active = true } = req.query;
    
//     const result = await pool.query(
//       `
//       SELECT
//         sa.id,
//         sa.assignment_date,
//         sa.effective_from,
//         sa.effective_to,
//         sa.is_permanent,
//         sa.is_active,
//         e.employee_code,
//         e.first_name,
//         e.last_name,
//         CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
//         e.department,
//         e.designation
//       FROM shift_assignments sa
//       JOIN employees e ON sa.employee_id = e.id
//       WHERE sa.shift_id = $1 
//         AND sa.is_active = $2
//         AND sa.is_deleted = false
//         AND e.is_deleted = false
//       ORDER BY e.first_name, e.last_name
//       `,
//       [shift_id, is_active === 'true']
//     );
    
//     res.json({
//       success: true,
//       data: result.rows,
//       totalRecords: result.rows.length,
//     });
//   } catch (err) {
//     console.error("GET ASSIGNMENTS BY SHIFT ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };

// /* ===========================
//    ✅ DEACTIVATE SHIFT ASSIGNMENT
// =========================== */

// const deactivateShiftAssignment = async (req, res) => {
//   try {
//     const { employee_code, effective_to, reason, updated_by } = req.body;
    
//     if (!employee_code) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee code is required",
//       });
//     }
    
//     // Get employee id
//     const employeeResult = await pool.query(
//       `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
//       [employee_code]
//     );
    
//     if (employeeResult.rows.length === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Employee not found",
//       });
//     }
    
//     const result = await pool.query(
//       `
//       UPDATE shift_assignments
//       SET is_active = false,
//           effective_to = COALESCE($1, effective_to, CURRENT_DATE),
//           reason = COALESCE($2, reason, 'Deactivated by admin'),
//           updated_by = COALESCE($3, updated_by),
//           updated_at = NOW()
//       WHERE employee_id = $4 
//         AND is_active = true
//         AND is_deleted = false
//       RETURNING id, is_active, effective_to
//       `,
//       [effective_to, reason, updated_by, employeeResult.rows[0].id]
//     );
    
//     if (result.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "No active shift assignment found for this employee",
//       });
//     }
    
//     res.json({
//       success: true,
//       message: "Shift assignment deactivated successfully",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("DEACTIVATE SHIFT ASSIGNMENT ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };

// /* ===========================
//    ✅ BULK ASSIGN SHIFT
// =========================== */

// const bulkAssignShift = async (req, res) => {
//   try {
//     const { employee_codes, shift_id, assignment_date, effective_from, reason, approved_by, org_id, created_by } = req.body;
    
//     if (!employee_codes || !employee_codes.length || !shift_id || !assignment_date || !org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee codes array, shift ID, assignment date, and org_id are required",
//       });
//     }
    
//     const results = [];
//     let successCount = 0;
//     let failCount = 0;
    
//     for (const employee_code of employee_codes) {
//       try {
//         // Get employee id
//         const employeeResult = await pool.query(
//           `SELECT id FROM employees WHERE employee_code = $1 AND is_deleted = false`,
//           [employee_code]
//         );
        
//         if (employeeResult.rows.length === 0) {
//           failCount++;
//           results.push({ employee_code, success: false, message: "Employee not found" });
//           continue;
//         }
        
//         const realEmployeeId = employeeResult.rows[0].id;
        
//         // Insert assignment
//         const result = await pool.query(
//           `
//           INSERT INTO shift_assignments (
//             employee_id,
//             shift_id,
//             assignment_date,
//             effective_from,
//             effective_to,
//             is_permanent,
//             is_active,
//             reason,
//             approved_by,
//             org_id,
//             created_by,
//             created_at,
//             updated_at
//           )
//           VALUES ($1, $2, $3, $4, $5, false, true, $6, $7, $8, $9, NOW(), NOW())
//           RETURNING id
//           `,
//           [
//             realEmployeeId,
//             shift_id,
//             assignment_date,
//             effective_from || assignment_date,
//             null,
//             reason || null,
//             approved_by || null,
//             org_id,
//             created_by || null
//           ]
//         );
        
//         successCount++;
//         results.push({ employee_code, success: true, id: result.rows[0].id });
//       } catch (error) {
//         failCount++;
//         results.push({ employee_code, success: false, message: error.message });
//       }
//     }
    
//     res.status(200).json({
//       success: true,
//       message: `Successfully assigned ${successCount} employee(s), ${failCount} failed`,
//       data: {
//         success_count: successCount,
//         fail_count: failCount,
//         results: results
//       }
//     });
//   } catch (error) {
//     console.error("BULK ASSIGN SHIFT ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };

// module.exports = {
//   assignShiftToEmployee,
//   getShiftAssignments,
//   getShiftAssignmentById,
//   updateShiftAssignment,
//   deleteShiftAssignment,
//   getEmployeeCurrentShift,
//   getEmployeeShiftHistory,
//   getAssignmentsByShift,
//   deactivateShiftAssignment,
//   bulkAssignShift
// };


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

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

/**
 * Check for overlapping shift assignments
 */
const checkOverlappingAssignments = async (employeeId, effectiveFrom, effectiveTo, excludeId = null) => {
  let query = `
    SELECT id, effective_from, effective_to, shift_id
    FROM shift_assignments
    WHERE employee_id = $1
      AND is_deleted = false
      AND is_active = true
      AND (
        (effective_from <= $2 AND (effective_to IS NULL OR effective_to >= $2))
        OR
        (effective_from <= $3 AND (effective_to IS NULL OR effective_to >= $3))
        OR
        (effective_from >= $2 AND (effective_to IS NULL OR effective_to <= $3))
      )
  `;
  
  const params = [employeeId, effectiveFrom, effectiveTo];
  
  if (excludeId) {
    query += ` AND id != $4`;
    params.push(excludeId);
  }
  
  const result = await pool.query(query, params);
  return result.rows;
};

/**
 * Validate date range
 */
const validateDateRange = (effectiveFrom, effectiveTo) => {
  if (effectiveTo && new Date(effectiveTo) < new Date(effectiveFrom)) {
    throw new Error("Effective end date cannot be before start date");
  }
};

/**
 * Update attendance records after shift change
 */
const updateAttendanceShift = async (employeeId, newShiftName, effectiveFrom, updatedBy) => {
  return await pool.query(
    `
    UPDATE attendance 
    SET shift_type = $1,
        updated_by = $2,
        updated_at = NOW()
    WHERE employee_id = $3 
      AND attendance_date >= $4
      AND is_deleted = false
    `,
    [newShiftName, updatedBy, employeeId, effectiveFrom]
  );
};

/**
 * Auto-update assignment status based on dates
 */
const autoUpdateAssignmentStatus = async () => {
  const result = await pool.query(`
    UPDATE shift_assignments
    SET is_active = false,
        updated_at = NOW()
    WHERE is_active = true
      AND effective_to IS NOT NULL
      AND effective_to < CURRENT_DATE
      AND is_deleted = false
    RETURNING id
  `);
  return result.rows;
};

/**
 * Validate approver has proper permissions
 */
const validateApprover = async (approverId) => {
  if (!approverId) return true;
  
  const result = await pool.query(
    `
    SELECT id, role, can_approve_shifts 
    FROM employees 
    WHERE id = $1 
      AND is_deleted = false
      AND (role IN ('admin', 'manager', 'hr') OR can_approve_shifts = true)
    `,
    [approverId]
  );
  
  return result.rows.length > 0;
};

/**
 * Build paginated query
 */
const buildPaginatedQuery = async (baseQuery, countQuery, params, page, limit) => {
  const offset = (page - 1) * limit;
  
  // Get total count
  const countResult = await pool.query(countQuery, params);
  const totalRecords = parseInt(countResult.rows[0]?.total || 0);
  
  // Add pagination to base query
  const paginatedQuery = `${baseQuery} LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  const queryParams = [...params, limit, offset];
  
  const result = await pool.query(paginatedQuery, queryParams);
  
  return {
    data: result.rows,
    pagination: {
      currentPage: parseInt(page),
      totalPages: Math.ceil(totalRecords / limit),
      totalRecords: totalRecords,
      recordsPerPage: parseInt(limit)
    }
  };
};

/* ===========================
   ✅ ASSIGN SHIFT TO EMPLOYEE
=========================== */

const assignShiftToEmployee = async (req, res) => {
  const client = await pool.connect();
  
  try {
    console.log("=== ASSIGN SHIFT DEBUG ===");
    console.log("req.body:", req.body);

    let {
      employee_code,
      shift_id,
      assignment_date,
      effective_from,
      effective_to,
      is_permanent,
      reason,
      approved_by,
      org_id,
      created_by,
      update_attendance = true
    } = req.body;

    // Validation
    if (!employee_code || !shift_id || !assignment_date || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Employee code, shift ID, assignment date, and org_id are required",
      });
    }

    // Validate date range
    validateDateRange(effective_from || assignment_date, effective_to);

    // Validate approver permissions
    if (approved_by && !(await validateApprover(approved_by))) {
      return res.status(403).json({
        success: false,
        message: "Approver not found or doesn't have permission to approve shift assignments",
      });
    }

    await client.query('BEGIN');

    // Fetch employee
    const employeeResult = await client.query(
      `SELECT id, employee_code, first_name, last_name, org_id 
       FROM employees 
       WHERE employee_code = $1 AND is_deleted = false`,
      [employee_code]
    );

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

    const realEmployeeId = employeeResult.rows[0].id;
    const employeeOrgId = employeeResult.rows[0].org_id;

    // Verify org_id matches
    if (employeeOrgId !== org_id) {
      await client.query('ROLLBACK');
      return res.status(403).json({
        success: false,
        message: "Employee does not belong to this organization",
      });
    }

    // Check if shift exists
    const shiftResult = await client.query(
      `SELECT id, shift_name FROM shifts WHERE id = $1 AND is_deleted = false`,
      [shift_id]
    );

    if (shiftResult.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({
        success: false,
        message: "Shift not found",
      });
    }

    const effectiveFromDate = effective_from || assignment_date;
    const effectiveToDate = effective_to || null;

    // Check for overlapping assignments
    const overlapping = await checkOverlappingAssignments(
      realEmployeeId, 
      effectiveFromDate, 
      effectiveToDate,
      null
    );

    if (overlapping.length > 0) {
      await client.query('ROLLBACK');
      return res.status(409).json({
        success: false,
        message: "Overlapping shift assignment exists",
        overlapping_assignments: overlapping
      });
    }

    // Check if assignment already exists for this date
    const existingAssignment = await client.query(
      `SELECT id FROM shift_assignments 
       WHERE employee_id = $1 
         AND assignment_date = $2 
         AND is_deleted = false`,
      [realEmployeeId, assignment_date]
    );

    let result;
    let attendanceUpdateCount = 0;

    if (existingAssignment.rows.length > 0) {
      // Update existing assignment
      result = await client.query(
        `
        UPDATE shift_assignments
        SET shift_id = $1,
            effective_from = $2,
            effective_to = $3,
            is_permanent = $4,
            reason = $5,
            approved_by = $6,
            updated_by = $7,
            updated_at = NOW()
        WHERE id = $8
        RETURNING *
        `,
        [
          shift_id,
          effectiveFromDate,
          effectiveToDate,
          is_permanent || false,
          reason || null,
          approved_by || null,
          created_by || null,
          existingAssignment.rows[0].id
        ]
      );

      // Update attendance if requested
      if (update_attendance) {
        const attendanceResult = await updateAttendanceShift(
          realEmployeeId,
          shiftResult.rows[0].shift_name,
          effectiveFromDate,
          created_by
        );
        attendanceUpdateCount = attendanceResult.rowCount;
      }

      await client.query('COMMIT');

      return res.status(200).json({
        success: true,
        message: `Shift updated successfully for ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name}`,
        data: {
          assignment: result.rows[0],
          attendance_records_updated: attendanceUpdateCount
        },
      });
    } else {
      // Deactivate any overlapping active assignments
      await client.query(
        `
        UPDATE shift_assignments
        SET is_active = false,
            effective_to = $1,
            reason = COALESCE(reason, 'Overridden by new assignment on ' || $2),
            updated_at = NOW()
        WHERE employee_id = $3
          AND is_active = true
          AND is_deleted = false
          AND (
            (effective_from <= $1 AND (effective_to IS NULL OR effective_to >= $1))
            OR
            (effective_from >= $4 AND effective_to <= $5)
          )
        `,
        [effectiveFromDate, assignment_date, realEmployeeId, effectiveFromDate, effectiveToDate || effectiveFromDate]
      );

      // Insert new assignment
      result = await client.query(
        `
        INSERT INTO shift_assignments (
          employee_id,
          shift_id,
          assignment_date,
          effective_from,
          effective_to,
          is_permanent,
          is_active,
          reason,
          approved_by,
          org_id,
          created_by,
          created_at,
          updated_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, true, $7, $8, $9, $10, NOW(), NOW())
        RETURNING *
        `,
        [
          realEmployeeId,
          shift_id,
          assignment_date,
          effectiveFromDate,
          effectiveToDate,
          is_permanent || false,
          reason || null,
          approved_by || null,
          org_id,
          created_by || null
        ]
      );

      // Update attendance if requested
      if (update_attendance) {
        const attendanceResult = await updateAttendanceShift(
          realEmployeeId,
          shiftResult.rows[0].shift_name,
          effectiveFromDate,
          created_by
        );
        attendanceUpdateCount = attendanceResult.rowCount;
      }

      await client.query('COMMIT');

      return res.status(201).json({
        success: true,
        message: `Shift "${shiftResult.rows[0].shift_name}" assigned to ${employeeResult.rows[0].first_name} ${employeeResult.rows[0].last_name} successfully`,
        data: {
          assignment: result.rows[0],
          attendance_records_updated: attendanceUpdateCount
        },
      });
    }
  } catch (error) {
    await client.query('ROLLBACK');
    console.error("ASSIGN SHIFT ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET ALL SHIFT ASSIGNMENTS
=========================== */

const getShiftAssignments = async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 10, 
      employee_id,
      shift_id,
      is_active,
      start_date,
      end_date,
      org_id 
    } = req.query;
    
    // Build base query
    let baseQuery = `
      SELECT
        sa.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name,
        CONCAT(creator.first_name, ' ', creator.last_name) AS created_by_name,
        CONCAT(updater.first_name, ' ', updater.last_name) AS updated_by_name
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      JOIN shifts s ON sa.shift_id = s.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      LEFT JOIN employees creator ON sa.created_by = creator.id
      LEFT JOIN employees updater ON sa.updated_by = updater.id
      WHERE sa.is_deleted = false
    `;
    
    // Build count query
    let countQuery = `
      SELECT COUNT(DISTINCT sa.id) as total
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      JOIN shifts s ON sa.shift_id = s.id
      WHERE sa.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    if (employee_id) {
      const condition = ` AND e.employee_code = $${paramIndex}`;
      baseQuery += condition;
      countQuery += condition;
      queryParams.push(employee_id);
      paramIndex++;
    }
    
    if (shift_id) {
      const condition = ` AND sa.shift_id = $${paramIndex}`;
      baseQuery += condition;
      countQuery += condition;
      queryParams.push(shift_id);
      paramIndex++;
    }
    
    if (is_active !== undefined) {
      const condition = ` AND sa.is_active = $${paramIndex}`;
      baseQuery += condition;
      countQuery += condition;
      queryParams.push(is_active === 'true');
      paramIndex++;
    }
    
    if (start_date) {
      const condition = ` AND sa.assignment_date >= $${paramIndex}`;
      baseQuery += condition;
      countQuery += condition;
      queryParams.push(start_date);
      paramIndex++;
    }
    
    if (end_date) {
      const condition = ` AND sa.assignment_date <= $${paramIndex}`;
      baseQuery += condition;
      countQuery += condition;
      queryParams.push(end_date);
      paramIndex++;
    }
    
    if (org_id) {
      const condition = ` AND sa.org_id = $${paramIndex}`;
      baseQuery += condition;
      countQuery += condition;
      queryParams.push(org_id);
      paramIndex++;
    }
    
    baseQuery += ` ORDER BY sa.created_at DESC`;
    
    // Auto-update expired assignments before fetching
    await autoUpdateAssignmentStatus();
    
    const result = await buildPaginatedQuery(baseQuery, countQuery, queryParams, page, limit);
    
    res.json({
      success: true,
      ...result
    });
  } catch (err) {
    console.error("FETCH SHIFT ASSIGNMENTS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET SHIFT ASSIGNMENT BY ID
=========================== */

const getShiftAssignmentById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      SELECT
        sa.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation,
        e.email,
        e.phone,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        s.total_hours,
        s.working_hours,
        s.lunch_start,
        s.lunch_end,
        s.first_break_start,
        s.first_break_end,
        s.second_break_start,
        s.second_break_end,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name,
        CONCAT(creator.first_name, ' ', creator.last_name) AS created_by_name,
        CONCAT(updater.first_name, ' ', updater.last_name) AS updated_by_name
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      JOIN shifts s ON sa.shift_id = s.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      LEFT JOIN employees creator ON sa.created_by = creator.id
      LEFT JOIN employees updater ON sa.updated_by = updater.id
      WHERE sa.id = $1 AND sa.is_deleted = false
      `,
      [id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Shift assignment not found",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET SHIFT ASSIGNMENT BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ UPDATE SHIFT ASSIGNMENT
=========================== */

const updateShiftAssignment = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const {
      shift_id,
      effective_from,
      effective_to,
      is_permanent,
      is_active,
      reason,
      approved_by,
      updated_by,
      update_attendance = true
    } = req.body;
    
    await client.query('BEGIN');
    
    // Check if assignment exists
    const existing = await client.query(
      `SELECT sa.*, e.id as employee_id, s.shift_name 
       FROM shift_assignments sa
       JOIN employees e ON sa.employee_id = e.id
       JOIN shifts s ON sa.shift_id = s.id
       WHERE sa.id = $1 AND sa.is_deleted = false`,
      [id]
    );
    
    if (!existing.rows.length) {
      await client.query('ROLLBACK');
      return res.status(404).json({
        success: false,
        message: "Shift assignment not found",
      });
    }
    
    const old = existing.rows[0];
    const newEffectiveFrom = effective_from || old.effective_from;
    const newEffectiveTo = effective_to !== undefined ? effective_to : old.effective_to;
    
    // Validate date range
    validateDateRange(newEffectiveFrom, newEffectiveTo);
    
    // Validate approver permissions
    if (approved_by && !(await validateApprover(approved_by))) {
      await client.query('ROLLBACK');
      return res.status(403).json({
        success: false,
        message: "Approver not found or doesn't have permission",
      });
    }
    
    // Check for overlapping assignments (excluding current)
    const overlapping = await checkOverlappingAssignments(
      old.employee_id,
      newEffectiveFrom,
      newEffectiveTo,
      parseInt(id)
    );
    
    if (overlapping.length > 0) {
      await client.query('ROLLBACK');
      return res.status(409).json({
        success: false,
        message: "Overlapping shift assignment exists",
        overlapping_assignments: overlapping
      });
    }
    
    // Get new shift name if shift_id changed
    let newShiftName = old.shift_name;
    if (shift_id && shift_id !== old.shift_id) {
      const shiftResult = await client.query(
        `SELECT shift_name FROM shifts WHERE id = $1 AND is_deleted = false`,
        [shift_id]
      );
      if (shiftResult.rows.length > 0) {
        newShiftName = shiftResult.rows[0].shift_name;
      }
    }
    
    const result = await client.query(
      `
      UPDATE shift_assignments
      SET
        shift_id = COALESCE($1, shift_id),
        effective_from = COALESCE($2, effective_from),
        effective_to = $3,
        is_permanent = COALESCE($4, is_permanent),
        is_active = COALESCE($5, is_active),
        reason = COALESCE($6, reason),
        approved_by = COALESCE($7, approved_by),
        updated_by = COALESCE($8, updated_by),
        updated_at = NOW()
      WHERE id = $9
      RETURNING *
      `,
      [
        shift_id || old.shift_id,
        effective_from || old.effective_from,
        effective_to !== undefined ? effective_to : old.effective_to,
        is_permanent !== undefined ? is_permanent : old.is_permanent,
        is_active !== undefined ? is_active : old.is_active,
        reason || old.reason,
        approved_by || old.approved_by,
        updated_by || old.updated_by,
        id
      ]
    );
    
    let attendanceUpdateCount = 0;
    
    // Update attendance if shift changed and update_attendance is true
    if (update_attendance && shift_id && shift_id !== old.shift_id && is_active !== false) {
      const attendanceResult = await updateAttendanceShift(
        old.employee_id,
        newShiftName,
        newEffectiveFrom,
        updated_by
      );
      attendanceUpdateCount = attendanceResult.rowCount;
    }
    
    await client.query('COMMIT');
    
    res.status(200).json({
      success: true,
      message: "Shift assignment updated successfully",
      data: {
        assignment: result.rows[0],
        attendance_records_updated: attendanceUpdateCount
      },
    });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error("UPDATE SHIFT ASSIGNMENT ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ DELETE SHIFT ASSIGNMENT (Soft Delete)
=========================== */

const deleteShiftAssignment = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const { updated_by } = req.body;
    
    await client.query('BEGIN');
    
    // Check if assignment exists and get employee info
    const assignment = await client.query(
      `SELECT sa.*, e.id as employee_id 
       FROM shift_assignments sa
       JOIN employees e ON sa.employee_id = e.id
       WHERE sa.id = $1 AND sa.is_deleted = false`,
      [id]
    );
    
    if (assignment.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({
        success: false,
        message: "Shift assignment not found",
      });
    }
    
    const result = await client.query(
      `
      UPDATE shift_assignments
      SET is_deleted = true,
          is_active = false,
          updated_by = COALESCE($1, updated_by),
          updated_at = NOW()
      WHERE id = $2 AND is_deleted = false
      RETURNING id
      `,
      [updated_by || null, id]
    );
    
    await client.query('COMMIT');
    
    res.json({
      success: true,
      message: "Shift assignment deleted successfully",
    });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error("DELETE SHIFT ASSIGNMENT ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Delete failed",
    });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET EMPLOYEE CURRENT SHIFT
=========================== */

const getEmployeeCurrentShift = async (req, res) => {
  try {
    const { employee_code } = req.params;
    const { as_of_date } = req.query;
    
    if (!employee_code) {
      return res.status(400).json({
        success: false,
        message: "Employee code is required",
      });
    }
    
    const currentDate = as_of_date || new Date().toISOString().split('T')[0];
    
    // Auto-update expired assignments
    await autoUpdateAssignmentStatus();
    
    const result = await pool.query(
      `
      SELECT
        sa.id,
        sa.assignment_date,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.is_active,
        sa.reason,
        s.id as shift_id,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        s.total_hours,
        s.working_hours,
        s.lunch_start,
        s.lunch_end,
        s.first_break_start,
        s.first_break_end,
        s.second_break_start,
        s.second_break_end,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name
      FROM shift_assignments sa
      JOIN shifts s ON sa.shift_id = s.id
      JOIN employees e ON sa.employee_id = e.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      WHERE e.employee_code = $1 
        AND sa.is_deleted = false
        AND s.is_deleted = false
        AND sa.effective_from <= $2
        AND (sa.effective_to IS NULL OR sa.effective_to >= $2)
      ORDER BY sa.is_active DESC, sa.created_at DESC
      LIMIT 1
      `,
      [employee_code, currentDate]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "No active shift found for this employee on the specified date",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET EMPLOYEE CURRENT SHIFT ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET EMPLOYEE SHIFT HISTORY
=========================== */

const getEmployeeShiftHistory = async (req, res) => {
  try {
    const { employee_code } = req.params;
    const { limit = 50, offset = 0, start_date, end_date } = req.query;
    
    if (!employee_code) {
      return res.status(400).json({
        success: false,
        message: "Employee code is required",
      });
    }
    
    let query = `
      SELECT
        sa.id,
        sa.assignment_date,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.is_active,
        sa.reason,
        sa.created_at,
        sa.created_by,
        sa.updated_at,
        sa.updated_by,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        CONCAT(approver.first_name, ' ', approver.last_name) AS approved_by_name,
        CONCAT(creator.first_name, ' ', creator.last_name) AS created_by_name
      FROM shift_assignments sa
      JOIN shifts s ON sa.shift_id = s.id
      JOIN employees e ON sa.employee_id = e.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      LEFT JOIN employees creator ON sa.created_by = creator.id
      WHERE e.employee_code = $1 
        AND sa.is_deleted = false
    `;
    
    const queryParams = [employee_code];
    let paramIndex = 2;
    
    if (start_date) {
      query += ` AND sa.assignment_date >= $${paramIndex}`;
      queryParams.push(start_date);
      paramIndex++;
    }
    
    if (end_date) {
      query += ` AND sa.assignment_date <= $${paramIndex}`;
      queryParams.push(end_date);
      paramIndex++;
    }
    
    query += ` ORDER BY sa.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    queryParams.push(limit, offset);
    
    const result = await pool.query(query, queryParams);
    
    // Get total count
    const countResult = await pool.query(
      `
      SELECT COUNT(*) as total
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      WHERE e.employee_code = $1 AND sa.is_deleted = false
      `,
      [employee_code]
    );
    
    res.json({
      success: true,
      data: result.rows,
      pagination: {
        limit: parseInt(limit),
        offset: parseInt(offset),
        total: parseInt(countResult.rows[0].total)
      }
    });
  } catch (err) {
    console.error("GET EMPLOYEE SHIFT HISTORY ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET SHIFT ASSIGNMENTS BY SHIFT
=========================== */

const getAssignmentsByShift = async (req, res) => {
  try {
    const { shift_id } = req.params;
    const { is_active = true, as_of_date } = req.query;
    
    const targetDate = as_of_date || new Date().toISOString().split('T')[0];
    
    let query = `
      SELECT
        sa.id,
        sa.assignment_date,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.is_active,
        e.employee_code,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation,
        e.email,
        e.phone
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      WHERE sa.shift_id = $1 
        AND sa.is_deleted = false
        AND e.is_deleted = false
    `;
    
    const queryParams = [shift_id];
    
    if (is_active === 'true') {
      query += ` AND sa.is_active = true AND sa.effective_from <= $2 AND (sa.effective_to IS NULL OR sa.effective_to >= $2)`;
      queryParams.push(targetDate);
    } else if (is_active === 'false') {
      query += ` AND sa.is_active = false`;
    }
    
    query += ` ORDER BY e.first_name, e.last_name`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      totalRecords: result.rows.length,
    });
  } catch (err) {
    console.error("GET ASSIGNMENTS BY SHIFT ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ DEACTIVATE SHIFT ASSIGNMENT
=========================== */

const deactivateShiftAssignment = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { employee_code, effective_to, reason, updated_by, update_attendance = true } = req.body;
    
    if (!employee_code) {
      return res.status(400).json({
        success: false,
        message: "Employee code is required",
      });
    }
    
    await client.query('BEGIN');
    
    // Get employee id and current shift info
    const employeeResult = await client.query(
      `SELECT e.id, e.employee_code, sa.id as assignment_id, sa.shift_id, s.shift_name
       FROM employees e
       LEFT JOIN shift_assignments sa ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
       LEFT JOIN shifts s ON sa.shift_id = s.id
       WHERE e.employee_code = $1 AND e.is_deleted = false`,
      [employee_code]
    );
    
    if (employeeResult.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({
        success: false,
        message: "Employee not found",
      });
    }
    
    const employee = employeeResult.rows[0];
    
    if (!employee.assignment_id) {
      await client.query('ROLLBACK');
      return res.status(404).json({
        success: false,
        message: "No active shift assignment found for this employee",
      });
    }
    
    const newEffectiveTo = effective_to || new Date().toISOString().split('T')[0];
    
    const result = await client.query(
      `
      UPDATE shift_assignments
      SET is_active = false,
          effective_to = COALESCE($1, effective_to, CURRENT_DATE),
          reason = COALESCE($2, reason, 'Deactivated by admin'),
          updated_by = COALESCE($3, updated_by),
          updated_at = NOW()
      WHERE id = $4 
        AND is_active = true
        AND is_deleted = false
      RETURNING id, is_active, effective_to
      `,
      [newEffectiveTo, reason, updated_by, employee.assignment_id]
    );
    
    let attendanceUpdateCount = 0;
    
    // Update attendance records to mark future dates as needing shift update
    if (update_attendance) {
      const attendanceResult = await client.query(
        `
        UPDATE attendance 
        SET shift_type = NULL,
            remarks = COALESCE(remarks, 'Shift deactivated - needs reassignment'),
            updated_by = $1,
            updated_at = NOW()
        WHERE employee_id = $2 
          AND attendance_date >= $3
          AND is_deleted = false
        `,
        [updated_by, employee.id, newEffectiveTo]
      );
      attendanceUpdateCount = attendanceResult.rowCount;
    }
    
    await client.query('COMMIT');
    
    res.json({
      success: true,
      message: `Shift assignment deactivated successfully for ${employee_code}`,
      data: {
        assignment: result.rows[0],
        attendance_records_updated: attendanceUpdateCount
      },
    });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error("DEACTIVATE SHIFT ASSIGNMENT ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ BULK ASSIGN SHIFT
=========================== */

const bulkAssignShift = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { 
      employee_codes, 
      shift_id, 
      assignment_date, 
      effective_from, 
      effective_to,
      reason, 
      approved_by, 
      org_id, 
      created_by,
      update_attendance = true 
    } = req.body;
    
    if (!employee_codes || !employee_codes.length || !shift_id || !assignment_date || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Employee codes array, shift ID, assignment date, and org_id are required",
      });
    }
    
    // Validate date range
    validateDateRange(effective_from || assignment_date, effective_to);
    
    // Validate approver
    if (approved_by && !(await validateApprover(approved_by))) {
      return res.status(403).json({
        success: false,
        message: "Approver not found or doesn't have permission",
      });
    }
    
    // Get shift details once
    const shiftResult = await pool.query(
      `SELECT id, shift_name FROM shifts WHERE id = $1 AND is_deleted = false`,
      [shift_id]
    );
    
    if (shiftResult.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Shift not found",
      });
    }
    
    await client.query('BEGIN');
    
    // Fetch all employees in one query
    const employeeCodesStr = employee_codes.map((_, i) => `$${i + 1}`).join(',');
    const employeesQuery = `
      SELECT id, employee_code, first_name, last_name, org_id
      FROM employees 
      WHERE employee_code IN (${employeeCodesStr}) 
        AND is_deleted = false
    `;
    const employeesResult = await client.query(employeesQuery, employee_codes);
    
    const employeeMap = new Map();
    employeesResult.rows.forEach(emp => {
      employeeMap.set(emp.employee_code, emp);
    });
    
    const results = [];
    let successCount = 0;
    let failCount = 0;
    let totalAttendanceUpdated = 0;
    const effectiveFromDate = effective_from || assignment_date;
    const effectiveToDate = effective_to || null;
    
    for (const employee_code of employee_codes) {
      try {
        const employee = employeeMap.get(employee_code);
        
        if (!employee) {
          failCount++;
          results.push({ employee_code, success: false, message: "Employee not found" });
          continue;
        }
        
        if (employee.org_id !== org_id) {
          failCount++;
          results.push({ employee_code, success: false, message: "Employee does not belong to this organization" });
          continue;
        }
        
        // Check for overlapping assignments
        const overlapping = await checkOverlappingAssignments(
          employee.id,
          effectiveFromDate,
          effectiveToDate,
          null
        );
        
        if (overlapping.length > 0) {
          failCount++;
          results.push({ 
            employee_code, 
            success: false, 
            message: "Overlapping shift assignment exists",
            overlapping: overlapping.length
          });
          continue;
        }
        
        // Deactivate overlapping active assignments
        await client.query(
          `
          UPDATE shift_assignments
          SET is_active = false,
              effective_to = $1,
              reason = COALESCE(reason, 'Overridden by bulk assignment on ' || $2),
              updated_at = NOW()
          WHERE employee_id = $3
            AND is_active = true
            AND is_deleted = false
          `,
          [effectiveFromDate, assignment_date, employee.id]
        );
        
        // Insert new assignment
        const insertResult = await client.query(
          `
          INSERT INTO shift_assignments (
            employee_id,
            shift_id,
            assignment_date,
            effective_from,
            effective_to,
            is_permanent,
            is_active,
            reason,
            approved_by,
            org_id,
            created_by,
            created_at,
            updated_at
          )
          VALUES ($1, $2, $3, $4, $5, false, true, $6, $7, $8, $9, NOW(), NOW())
          RETURNING id
          `,
          [
            employee.id,
            shift_id,
            assignment_date,
            effectiveFromDate,
            effectiveToDate,
            reason || null,
            approved_by || null,
            org_id,
            created_by || null
          ]
        );
        
        let attendanceCount = 0;
        
        // Update attendance records
        if (update_attendance) {
          const attendanceResult = await updateAttendanceShift(
            employee.id,
            shiftResult.rows[0].shift_name,
            effectiveFromDate,
            created_by
          );
          attendanceCount = attendanceResult.rowCount;
          totalAttendanceUpdated += attendanceCount;
        }
        
        successCount++;
        results.push({ 
          employee_code, 
          success: true, 
          id: insertResult.rows[0].id,
          attendance_records_updated: attendanceCount
        });
      } catch (error) {
        failCount++;
        results.push({ employee_code, success: false, message: error.message });
      }
    }
    
    await client.query('COMMIT');
    
    res.status(200).json({
      success: true,
      message: `Successfully assigned ${successCount} employee(s), ${failCount} failed`,
      data: {
        success_count: successCount,
        fail_count: failCount,
        total_attendance_records_updated: totalAttendanceUpdated,
        results: results
      }
    });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error("BULK ASSIGN SHIFT ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET ACTIVE ASSIGNMENTS REPORT
=========================== */

const getActiveAssignmentsReport = async (req, res) => {
  try {
    const { org_id, department, as_of_date } = req.query;
    const targetDate = as_of_date || new Date().toISOString().split('T')[0];
    
    let query = `
      SELECT
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        COUNT(DISTINCT sa.employee_id) as total_employees,
        STRING_AGG(DISTINCT e.department, ', ') as departments,
        COUNT(CASE WHEN sa.is_permanent = true THEN 1 END) as permanent_assignments,
        COUNT(CASE WHEN sa.is_permanent = false THEN 1 END) as temporary_assignments
      FROM shift_assignments sa
      JOIN shifts s ON sa.shift_id = s.id
      JOIN employees e ON sa.employee_id = e.id
      WHERE sa.is_deleted = false
        AND sa.is_active = true
        AND sa.effective_from <= $1
        AND (sa.effective_to IS NULL OR sa.effective_to >= $1)
        AND e.is_deleted = false
    `;
    
    const queryParams = [targetDate];
    let paramIndex = 2;
    
    if (org_id) {
      query += ` AND sa.org_id = $${paramIndex}`;
      queryParams.push(org_id);
      paramIndex++;
    }
    
    if (department) {
      query += ` AND e.department = $${paramIndex}`;
      queryParams.push(department);
      paramIndex++;
    }
    
    query += ` GROUP BY s.id, s.shift_name, s.shift_code, s.start_time, s.end_time
               ORDER BY s.start_time ASC`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      as_of_date: targetDate,
      total_shifts: result.rows.length,
    });
  } catch (err) {
    console.error("GET ACTIVE ASSIGNMENTS REPORT ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ BULK DEACTIVATE SHIFT ASSIGNMENTS
=========================== */

const bulkDeactivateShiftAssignments = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { employee_codes, effective_to, reason, updated_by, update_attendance = true } = req.body;
    
    if (!employee_codes || !employee_codes.length) {
      return res.status(400).json({
        success: false,
        message: "Employee codes array is required",
      });
    }
    
    await client.query('BEGIN');
    
    // Fetch employees with their current active assignments
    const employeeCodesStr = employee_codes.map((_, i) => `$${i + 1}`).join(',');
    const employeesQuery = `
      SELECT e.id, e.employee_code, sa.id as assignment_id, sa.shift_id, s.shift_name
      FROM employees e
      LEFT JOIN shift_assignments sa ON e.id = sa.employee_id AND sa.is_active = true AND sa.is_deleted = false
      LEFT JOIN shifts s ON sa.shift_id = s.id
      WHERE e.employee_code IN (${employeeCodesStr}) AND e.is_deleted = false
    `;
    const employeesResult = await client.query(employeesQuery, employee_codes);
    
    const results = [];
    let successCount = 0;
    let failCount = 0;
    let totalAttendanceUpdated = 0;
    const newEffectiveTo = effective_to || new Date().toISOString().split('T')[0];
    
    for (const employee of employeesResult.rows) {
      try {
        if (!employee.assignment_id) {
          failCount++;
          results.push({ employee_code: employee.employee_code, success: false, message: "No active shift assignment found" });
          continue;
        }
        
        // Deactivate assignment
        await client.query(
          `
          UPDATE shift_assignments
          SET is_active = false,
              effective_to = COALESCE($1, effective_to, CURRENT_DATE),
              reason = COALESCE($2, reason, 'Deactivated by bulk operation'),
              updated_by = COALESCE($3, updated_by),
              updated_at = NOW()
          WHERE id = $4
          `,
          [newEffectiveTo, reason, updated_by, employee.assignment_id]
        );
        
        let attendanceCount = 0;
        
        // Update attendance records
        if (update_attendance) {
          const attendanceResult = await client.query(
            `
            UPDATE attendance 
            SET shift_type = NULL,
                remarks = COALESCE(remarks, 'Shift deactivated - needs reassignment'),
                updated_by = $1,
                updated_at = NOW()
            WHERE employee_id = $2 
              AND attendance_date >= $3
              AND is_deleted = false
            `,
            [updated_by, employee.id, newEffectiveTo]
          );
          attendanceCount = attendanceResult.rowCount;
          totalAttendanceUpdated += attendanceCount;
        }
        
        successCount++;
        results.push({ 
          employee_code: employee.employee_code, 
          success: true,
          attendance_records_updated: attendanceCount
        });
      } catch (error) {
        failCount++;
        results.push({ employee_code: employee.employee_code, success: false, message: error.message });
      }
    }
    
    await client.query('COMMIT');
    
    res.json({
      success: true,
      message: `Successfully deactivated ${successCount} employee(s), ${failCount} failed`,
      data: {
        success_count: successCount,
        fail_count: failCount,
        total_attendance_records_updated: totalAttendanceUpdated,
        results: results
      }
    });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error("BULK DEACTIVATE SHIFT ASSIGNMENTS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

/* ===========================
   ✅ GET UPCOMING SHIFT CHANGES
=========================== */

const getUpcomingShiftChanges = async (req, res) => {
  try {
    const { org_id, days_ahead = 7 } = req.query;
    const futureDate = new Date();
    futureDate.setDate(futureDate.getDate() + parseInt(days_ahead));
    const futureDateStr = futureDate.toISOString().split('T')[0];
    const today = new Date().toISOString().split('T')[0];
    
    let query = `
      SELECT
        e.employee_code,
        e.first_name,
        e.last_name,
        e.department,
        e.email,
        s.shift_name as new_shift,
        sa.effective_from,
        sa.reason,
        sa.created_at as assigned_on,
        CONCAT(creator.first_name, ' ', creator.last_name) as assigned_by
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      JOIN shifts s ON sa.shift_id = s.id
      LEFT JOIN employees creator ON sa.created_by = creator.id
      WHERE sa.is_deleted = false
        AND sa.is_active = true
        AND sa.effective_from > $1
        AND sa.effective_from <= $2
    `;
    
    const queryParams = [today, futureDateStr];
    let paramIndex = 3;
    
    if (org_id) {
      query += ` AND sa.org_id = $${paramIndex}`;
      queryParams.push(org_id);
      paramIndex++;
    }
    
    query += ` ORDER BY sa.effective_from ASC, e.first_name, e.last_name`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      days_ahead: parseInt(days_ahead),
      total_upcoming_changes: result.rows.length,
    });
  } catch (err) {
    console.error("GET UPCOMING SHIFT CHANGES ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ EXPORT ASSIGNMENTS
=========================== */

const exportAssignments = async (req, res) => {
  try {
    const { org_id, status = 'active', format = 'json' } = req.query;
    
    let query = `
      SELECT
        e.employee_code,
        e.first_name,
        e.last_name,
        e.department,
        e.designation,
        s.shift_name,
        s.shift_code,
        s.start_time,
        s.end_time,
        sa.effective_from,
        sa.effective_to,
        sa.is_permanent,
        sa.reason,
        sa.assignment_date,
        CONCAT(approver.first_name, ' ', approver.last_name) as approved_by
      FROM shift_assignments sa
      JOIN employees e ON sa.employee_id = e.id
      JOIN shifts s ON sa.shift_id = s.id
      LEFT JOIN employees approver ON sa.approved_by = approver.id
      WHERE sa.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    if (org_id) {
      query += ` AND sa.org_id = $${paramIndex}`;
      queryParams.push(org_id);
      paramIndex++;
    }
    
    if (status === 'active') {
      query += ` AND sa.is_active = true`;
    } else if (status === 'inactive') {
      query += ` AND sa.is_active = false`;
    }
    
    query += ` ORDER BY e.employee_code, sa.effective_from DESC`;
    
    const result = await pool.query(query, queryParams);
    
    if (format === 'csv') {
      // Convert to CSV
      const headers = ['Employee Code', 'First Name', 'Last Name', 'Department', 'Designation', 
                       'Shift Name', 'Shift Code', 'Start Time', 'End Time', 'Effective From', 
                       'Effective To', 'Is Permanent', 'Reason', 'Assignment Date', 'Approved By'];
      
      const csvRows = [headers];
      for (const row of result.rows) {
        csvRows.push([
          row.employee_code,
          row.first_name,
          row.last_name,
          row.department,
          row.designation,
          row.shift_name,
          row.shift_code,
          row.start_time,
          row.end_time,
          row.effective_from,
          row.effective_to || '',
          row.is_permanent ? 'Yes' : 'No',
          row.reason || '',
          row.assignment_date,
          row.approved_by || ''
        ]);
      }
      
      const csv = csvRows.map(row => row.join(',')).join('\n');
      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', `attachment; filename=shift_assignments_${new Date().toISOString().split('T')[0]}.csv`);
      return res.send(csv);
    }
    
    res.json({
      success: true,
      data: result.rows,
      total_records: result.rows.length,
      exported_at: new Date().toISOString()
    });
  } catch (err) {
    console.error("EXPORT ASSIGNMENTS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

module.exports = {
  assignShiftToEmployee,
  getShiftAssignments,
  getShiftAssignmentById,
  updateShiftAssignment,
  deleteShiftAssignment,
  getEmployeeCurrentShift,
  getEmployeeShiftHistory,
  getAssignmentsByShift,
  deactivateShiftAssignment,
  bulkAssignShift,
  bulkDeactivateShiftAssignments,
  getActiveAssignmentsReport,
  getUpcomingShiftChanges,
  exportAssignments
};