// controllers/itemsController.js

const { getModel } = require('../models');
const mongoose = require('mongoose');
const Item = getModel('Item'); // Get the properly initialized model
const ExcelJS = require('exceljs');
const StockMovement = require('../models/StockMovement');

// Helper function to calculate discounted price
const calculateDiscountedPrice = (price, discount) => {
  if (!discount) return price;
  
  if (discount.type === 'percentage') {
    const discountAmount = (price * discount.value) / 100;
    return Math.max(0, price - discountAmount);
  } else if (discount.type === 'fixed') {
    return Math.max(0, price - discount.value);
  }
  
  return price;
};

// Function to generate a slug
const generateSlug = (name) => {
  if (!name) return `item-${Date.now()}-${Math.random().toString(36).substring(2, 8)}`;
  return name
    .toString()
    .toLowerCase()
    .replace(/[^\w\s-]/g, '')
    .replace(/\s+/g, '-')
    .replace(/-+/g, '-')
    .trim()
    .substring(0, 50) + '-' + Math.random().toString(36).substring(2, 8);
};
const fs = require('fs').promises;
const fsSync = require('fs'); // Keep sync version for some operations
const path = require('path');

// Helper function to safely delete a file
const deleteFile = async (filePath) => {
  try {
    // Check if the file exists and is a file (not a directory)
    const stats = await fs.stat(filePath);
    if (stats.isFile()) {
      await fs.unlink(filePath);
      console.log(`Successfully deleted file: ${filePath}`);
    }
  } catch (error) {
    // Ignore "file not found" errors, but log others
    if (error.code !== 'ENOENT') {
      console.error(`Error deleting file ${filePath}:`, error);
    }
  }
};
const { isValidObjectId } = mongoose;
const { Readable } = require('stream');
const csv = require('csv-parser');
const { promisify } = require('util');
const pipeline = promisify(require('stream').pipeline);

// @desc    Get all items with pagination, search, and filters
// @route   GET /api/items
// @access  Public
const getAllItems = async (req, res) => {
  try {
    // Pagination
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const skip = (page - 1) * limit;

    // Search and filter
    const search = req.query.search || '';
    const status = req.query.status || 'active'; // 'active', 'inactive', or 'all'
    const category = req.query.category;
    const brand = req.query.brand;
    const minPrice = parseFloat(req.query.minPrice);
    const maxPrice = parseFloat(req.query.maxPrice);
    const inStock = req.query.inStock === 'true';
    const sortBy = req.query.sortBy || 'relevance'; // Add sortBy parameter
    const storeId = req.query.store_id || req.query.store; // Support both store_id and store for backward compatibility

    // Build query
    const query = {};
    
    // Store filter - only show items for the specified store
    if (storeId && mongoose.Types.ObjectId.isValid(storeId)) {
      query.store_id = storeId;
    }
    
    // Status filter
    if (status === 'active') query.status = true;
    else if (status === 'inactive') query.status = false;
    
    // Category filter
    if (category) query.category_id = category;
    
    // Brand filter
    if (brand) query.brand_id = brand;
    
    // Price range filter
    if (!isNaN(minPrice) || !isNaN(maxPrice)) {
      query.price = {};
      if (!isNaN(minPrice)) query.price.$gte = minPrice;
      if (!isNaN(maxPrice)) query.price.$lte = maxPrice;
    }
    
    // Stock level filters
    if (req.query.stockLevel) {
      // Get stock settings from database for dynamic thresholds
      const Settings = require('../models/Settings');
      let settings = await Settings.findOne();
      if (!settings) {
        settings = await Settings.create({});
      }

      const outOfStockThreshold = settings.outOfStockThreshold || 5;
      const inStockThreshold = settings.inStockThreshold || 10;

      switch(req.query.stockLevel) {
        case 'in_stock':
          query.quantity = { $gte: inStockThreshold };
          break;
        case 'low_stock':
          query.quantity = { $gt: outOfStockThreshold, $lt: inStockThreshold };
          break;
        case 'out_of_stock':
          query.quantity = { $lte: outOfStockThreshold };
          break;
        case 'below_min':
          query.$expr = { $lt: ['$quantity', '$min_stock_level'] };
          break;
        case 'above_max':
          query.$expr = { $gt: ['$quantity', '$max_stock_level'] };
          break;
      }
    } else if (inStock) {
      query.quantity = { $gt: 0 };
    }

    // Search in name, sku, hsn, or barcode
    if (search) {
      query.$or = [
        { name: { $regex: search, $options: 'i' } },
        { sku: { $regex: search, $options: 'i' } },
        { hsn: { $regex: search, $options: 'i' } },
        { barcode: { $regex: search, $options: 'i' } },
        { description: { $regex: search, $options: 'i' } }
      ];
    }

    // Define sort options
    let sortOption = {};
    switch (sortBy) {
      case 'price_asc':
        sortOption = { price: 1 };
        break;
      case 'price_desc':
        sortOption = { price: -1 };
        break;
      case 'newest':
        sortOption = { createdAt: -1 };
        break;
      case 'relevance':
      default:
        // For relevance, we can sort by a combination of factors or keep it simple
        sortOption = { createdAt: -1 }; // Default to newest first for relevance
        break;
    }

    // Execute query with pagination
    // Get items with pagination and populate related fields
    const [items, total] = await Promise.all([
      Item.find(query)
        .populate('brand_id', 'name')
        .populate('category_id', 'name')
        .populate('unit_id', 'name')
        .populate('tax_id', 'name percentage')
        .populate('discount_id', 'name type value')
        .populate('store_id', 'name')
        .select('+quantity +min_stock_level +max_stock_level +stockHistory')
        .sort(sortOption)
        .skip(skip)
        .limit(limit)
        .lean()
        .then(async items => {
          // Get stock settings from database
          const Settings = require('../models/Settings');
          let settings = await Settings.findOne();
          if (!settings) {
            settings = await Settings.create({});
          }

          const outOfStockThreshold = settings.outOfStockThreshold || 5;
          const inStockThreshold = settings.inStockThreshold || 10;

          return items.map(item => ({
            ...item,
            // Add stock status information using dynamic settings
            stockStatus: item.quantity <= outOfStockThreshold ? 'out_of_stock' : 
                       (item.quantity < inStockThreshold ? 'low_stock' : 'in_stock'),
            // Add formatted stock information
            stockInfo: {
              current: item.quantity || 0,
              min: item.min_stock_level || 0,
              max: item.max_stock_level || null,
              lastUpdated: item.stockHistory?.length > 0 ? 
                          item.stockHistory[item.stockHistory.length - 1].createdAt : null
            }
          }));
        }),
      Item.countDocuments(query)
    ]);

    // Calculate pagination metadata
    const totalPages = Math.ceil(total / limit);
    const hasNextPage = page < totalPages;
    const hasPreviousPage = page > 1;
    
    // Get total count of all products (unfiltered)
    const totalProducts = await Item.countDocuments({});

    res.json({
      success: true,
      data: items,
      pagination: {
        total,
        totalProducts, // Add total count of all products
        totalPages,
        currentPage: page,
        hasNextPage,
        hasPreviousPage,
        limit
      }
    });
  } catch (error) {
    console.error('Error fetching items:', error);
    res.status(500).json({ 
      success: false, 
      msg: 'Error fetching items',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Get single item by ID with stock details
// @access  Public
const getItemById = async (req, res) => {
  try {
    const { id } = req.params;
    
    // Validate ID format
    if (!mongoose.Types.ObjectId.isValid(id)) {
      return res.status(400).json({
        success: false,
        msg: 'Invalid item ID format'
      });
    }
    
    // Get item with stock details and recent movements
    const item = await Item.findById(id)
      .populate('brand_id', 'name')
      .populate('category_id', 'name')
      .populate('unit_id', 'name')
      .populate('tax_id', 'name percentage')
      .populate('discount_id', 'name type value')
      .populate('store_id', 'name')
      .populate({
        path: 'stock_movements',
        options: { sort: { created_at: -1 }, limit: 10 },
        populate: {
          path: 'user',
          select: 'name email'
        }
      })
      .lean();

    if (!item) {
      return res.status(404).json({
        success: false,
        msg: 'Item not found'
      });
    }

    // Get stock settings from database
    const Settings = require('../models/Settings');
    let settings = await Settings.findOne();
    if (!settings) {
      settings = await Settings.create({});
    }

    const outOfStockThreshold = settings.outOfStockThreshold || 5;
    const inStockThreshold = settings.inStockThreshold || 10;

    // Calculate stock status using dynamic settings
    const stockStatus = item.quantity <= outOfStockThreshold
      ? 'out_of_stock'
      : item.quantity < inStockThreshold
        ? 'low_stock'
        : 'in_stock';

    // Add stock information to response
    const itemWithStock = {
      ...item,
      stock_status: stockStatus,
      stock_value: item.quantity * (item.price || 0)
    };

    res.json({ 
      success: true, 
      data: itemWithStock 
    });
  } catch (error) {
    console.error('Error fetching item by ID:', error);
    
    if (error.name === 'CastError') {
      return res.status(400).json({
        success: false,
        msg: 'Invalid item ID format'
      });
    }
    
    res.status(500).json({ 
      success: false, 
      msg: 'Error retrieving item',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Create a new item
// @route   POST /api/items
// @access  Private/Admin
const createItem = async (req, res) => {
  try {
    const {
      name, brand_id, category_id, unit_id, sku, hsn, minimum_qty,
      expire_date, barcode, description, image, price, tax_id,
      tax_type, sales_price, discount_id, store_id, quantity, status = true
    } = req.body;

    // Basic validation
    if (!name || !brand_id || !category_id || !unit_id || !sku) {
      return res.status(400).json({
        success: false,
        msg: 'Item name, brand, category, unit and SKU are required'
      });
    }

    // Check if SKU already exists
    const existingItem = await Item.findOne({ sku, status: true });
    if (existingItem) {
      return res.status(400).json({
        success: false,
        msg: 'An item with this SKU already exists'
      });
    }

    // Handle image uploads
    const images = [];
    
    // Handle single file upload
    if (req.file) {
      images.push({
        url: `/uploads/items/${req.file.filename}`,
        is_primary: true,
        position: 0
      });
    } 
    // Handle multiple files
    else if (req.files && req.files.length > 0) {
      images.push(...req.files.map((file, index) => ({
        url: `/uploads/items/${file.filename}`,
        is_primary: index === 0, // First image is primary by default
        position: index
      })));
    }
    // Handle image URL from body (for backward compatibility)
    else if (image) {
      images.push({
        url: image,
        is_primary: true,
        position: 0
      });
    }

    // Calculate base price and sales price
    const basePrice = parseFloat(price) || 0;
    let finalSalesPrice = basePrice; // Default to base price
    
    // Only process discount if discount_id is provided and not empty
    if (discount_id && discount_id.trim() !== '') {
      try {
        const Discount = getModel('Discount');
        const discount = await Discount.findById(discount_id);
        if (discount) {
          finalSalesPrice = calculateDiscountedPrice(basePrice, discount);
        }
      } catch (error) {
        console.error('Error fetching discount:', error);
        // If there's an error, continue with base price
      }
    }
    
    // If no discount or invalid discount, ensure sales_price is set to base price
    if (!discount_id || discount_id.trim() === '') {
      finalSalesPrice = basePrice;
    }

    // Create new item
    const newItem = new Item({
      name,
      brand_id,
      category_id,
      unit_id,
      sku,
      hsn,
      minimum_qty: minimum_qty || 0,
      expire_date: expire_date || null,
      barcode,
      description,
      images: images, // Store the array of images
      price: basePrice,
      tax_id: tax_id || null,
      tax_type: tax_type || 'inclusive',
      sales_price: finalSalesPrice,
      discount_id: discount_id || null,
      store_id: store_id || null,
      quantity: parseInt(quantity) || 0,
      status
    });

    // Save item to database
    const savedItem = await newItem.save();
    
    // Populate references
    const populatedItem = await Item.findById(savedItem._id)
      .populate('brand_id', 'name')
      .populate('category_id', 'name')
      .populate('unit_id', 'name')
      .populate('tax_id', 'name percentage')
      .populate('discount_id', 'name type value')
      .populate('store_id', 'name')
      .lean();

    res.status(201).json({ 
      success: true, 
      data: populatedItem, 
      msg: 'Item created successfully' 
    });
  } catch (error) {
    console.error('Error creating item:', error);
    
    // Handle duplicate key error
    if (error.code === 11000) {
      return res.status(400).json({
        success: false,
        msg: 'An item with this SKU or barcode already exists',
        field: error.keyPattern ? Object.keys(error.keyPattern)[0] : null
      });
    }
    
    // Handle validation errors
    if (error.name === 'ValidationError') {
      const messages = Object.values(error.errors).map(val => val.message);
      return res.status(400).json({
        success: false,
        msg: 'Validation error',
        errors: messages
      });
    }
    
    res.status(500).json({ 
      success: false, 
      msg: 'Error creating item',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Update an item
// @route   PUT /api/items/:id
// @access  Private/Admin
const updateItem = async (req, res) => {
  try {
    const { id } = req.params;
    
    // Validate ID
    if (!id || !mongoose.Types.ObjectId.isValid(id)) {
      return res.status(400).json({
        success: false,
        msg: 'Valid item ID is required'
      });
    }

    // Find existing item
    const existingItem = await Item.findById(id);
    if (!existingItem) {
      return res.status(404).json({
        success: false,
        msg: 'Item not found'
      });
    }

    // Create an object to track manually changed fields
    const updateData = {};
    const manuallyChangedFields = Object.keys(req.body);

    // Validate and process each manually changed field
    for (const field of manuallyChangedFields) {
      // Skip fields that shouldn't be directly updated
      if (['_id', 'createdAt', 'updatedAt', '__v', 'stockHistory', 'quantity'].includes(field)) {
        continue;
      }
      
      // Handle status field
      if (field === 'status') {
        updateData[field] = req.body[field] === 'true' || req.body[field] === true || req.body[field] === 1 || req.body[field] === '1';
        continue;
      }
      
      // Special handling for store_id - ensure it's a valid ObjectId string
      if (field === 'store_id' && req.body.store_id) {
        if (typeof req.body.store_id === 'object' && req.body.store_id._id) {
          updateData.store_id = req.body.store_id._id;
        } else if (mongoose.Types.ObjectId.isValid(req.body.store_id)) {
          updateData.store_id = req.body.store_id;
        } else {
          return res.status(400).json({
            success: false,
            msg: 'Invalid store ID format',
            field: 'store_id'
          });
        }
        continue;
      }
      
      // Special handling for quantity - we don't update quantity through the regular update endpoint
      // Quantity should only be updated through the dedicated stock update endpoint
      if (field === 'quantity') {
        continue;
      }

      // Special handling for name - ensure it doesn't exceed 100 chars
      if (field === 'name' && req.body.name) {
        if (req.body.name.length > 100) {
          return res.status(400).json({
            success: false,
            msg: 'Item name cannot exceed 100 characters',
            field: 'name'
          });
        }
        updateData[field] = req.body[field];
        continue;
      }
      
      // Special handling for SKU
      if (field === 'sku') {
        const newSku = req.body.sku ? String(req.body.sku).trim() : '';
        const currentSku = existingItem.sku ? String(existingItem.sku).trim() : '';
        
        // Debug logging
        console.log('SKU Update Check:', {
          newSku,
          currentSku,
          skipSkuValidation: req.body.skipSkuValidation,
          body: req.body,
          isEdit: !!req.params.id,
          method: req.method,
          originalUrl: req.originalUrl
        });
        
        // Log all request data for debugging
        console.log('Request body:', req.body);
        console.log('Request params:', req.params);
        console.log('Request query:', req.query);
        
        // Skip SKU validation if skipSkuValidation flag is set to 'true' or if SKU hasn't changed
        const shouldSkipValidation = 
          req.body.skipSkuValidation === 'true' || 
          req.body.skipSkuValidation === true ||
          newSku.toLowerCase() === currentSku.toLowerCase() ||
          newSku === ''; // Allow empty SKU
        
        if (shouldSkipValidation) {
          console.log('Skipping SKU validation');
          updateData.sku = newSku || currentSku; // Keep existing SKU if new one is empty
          continue;
        }
        
        // Only check for SKU existence if we have a new non-empty SKU
        if (newSku) {
          console.log('Checking for duplicate SKU:', newSku);
          // Check if new SKU already exists (case-insensitive check)
          const skuExists = await Item.findOne({ 
            sku: { $regex: new RegExp(`^${newSku.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}$`, 'i') },
            _id: { $ne: id },
            status: { $ne: 0 } // Only check against active items
          }).lean();
          
          if (skuExists) {
            console.log('Duplicate SKU found:', skuExists);
            return res.status(400).json({
              success: false,
              msg: 'An item with this SKU already exists',
              field: 'sku',
              existingItem: skuExists._id
            });
          }
          
          // Update SKU since it's new and unique
          updateData.sku = newSku;
        }
        continue;
      }

      // Handle image updates
      if (field === 'images' || req.files || req.file) {
        let images = [];

        // Use existing images with their primary flags if provided
        if (req.body.existingImages) {
          try {
            const existingImages = JSON.parse(req.body.existingImages);
            
            // Ensure only one image is marked as primary
            let hasPrimary = false;
            images = existingImages.map((img, idx) => {
              // If this is the first image and no primary is set yet, make it primary
              const isPrimary = idx === 0 ? true : (img.is_primary && !hasPrimary);
              if (isPrimary) hasPrimary = true;
              
              return {
                url: img.url,
                is_primary: isPrimary,
                position: img.position || idx,
                _id: img._id || new mongoose.Types.ObjectId()
              };
            });
            
            // If no image is marked as primary, make the first one primary
            if (images.length > 0 && !hasPrimary) {
              images[0].is_primary = true;
            }
          } catch (error) {
            console.error('Error parsing existing images:', error);
            images = [];
          }
        }
        // Keep track of existing images that aren't being removed
        else if (existingItem.images && existingItem.images.length > 0) {
          // Check for removed images
          const removedImages = req.body.removedImages ? JSON.parse(req.body.removedImages) : [];

          // Filter out removed images
          images = existingItem.images.filter(img =>
            !removedImages.includes(img.url)
          );
        }

        // Handle new file uploads
        if (req.files && req.files.length > 0) {
          // Check if we already have a primary image
          const hasExistingPrimary = images.some(img => img.is_primary);
          
          // Add new images to the array
          const newImages = req.files.map((file, index) => ({
            url: `/uploads/items/${file.filename}`,
            // First new image is primary if no primary exists, otherwise not primary
            is_primary: !hasExistingPrimary && index === 0,
            position: images.length + index,
            _id: new mongoose.Types.ObjectId() // Generate a new ObjectId for each image
          }));

          // Add new images to the beginning of the array
          images = [...newImages, ...images];
          
          // If we added new images and no primary is set, make the first image primary
          if (images.length > 0 && !images.some(img => img.is_primary)) {
            images[0].is_primary = true;
          }

          // Ensure we have at least one primary image
          if (images.length > 0 && !images.some(img => img.is_primary)) {
            images[0].is_primary = true;
          }
        }
        // Handle single file upload (for backward compatibility)
        else if (req.file) {
          const newImage = {
            url: `/uploads/items/${req.file.filename}`,
            is_primary: images.length === 0, // Make primary if no images exist
            position: 0,
            _id: new mongoose.Types.ObjectId()
          };

          // Add to beginning of array
          images.unshift(newImage);
        }

        // Update the images array in the update data
        updateData.images = images;
        // Handle numeric fields
        if (['price', 'sales_price', 'minimum_qty', 'quantity'].includes(field)) {
          updateData[field] = parseFloat(req.body[field]);
        } 
        // Handle date fields
        else if (['expire_date'].includes(field)) {
          updateData[field] = req.body[field] ? new Date(req.body[field]) : null;
        }
        // Handle boolean fields
        else if (['status'].includes(field)) {
          updateData[field] = Boolean(req.body[field]);
        }
        // Handle discount changes
        else if (field === 'discount_id') {
          updateData.discount_id = req.body.discount_id || null;
          
          // If price or discount is being updated, we need to recalculate sales_price
          const basePrice = parseFloat(req.body.price) || existingItem.price;
          
          if (req.body.discount_id) {
            try {
              const Discount = getModel('Discount');
              const discount = await Discount.findById(req.body.discount_id);
              if (discount) {
                updateData.sales_price = calculateDiscountedPrice(basePrice, discount);
              } else {
                // If discount not found, keep the original sales price
                updateData.sales_price = basePrice;
              }
            } catch (error) {
              console.error('Error fetching discount:', error);
              updateData.sales_price = basePrice;
            }
          } else {
            // If discount is being removed, set sales_price back to base price
            updateData.sales_price = basePrice;
          }
        }
        // Handle price changes
        else if (field === 'price') {
          const newPrice = parseFloat(req.body.price) || 0;
          updateData.price = newPrice;
          
          // If there's a discount, recalculate sales_price
          if (existingItem.discount_id || updateData.discount_id) {
            const discountId = updateData.discount_id || existingItem.discount_id;
            try {
              const Discount = getModel('Discount');
              const discount = await Discount.findById(discountId);
              if (discount) {
                updateData.sales_price = calculateDiscountedPrice(newPrice, discount);
              } else {
                updateData.sales_price = newPrice;
              }
            } catch (error) {
              console.error('Error fetching discount:', error);
              updateData.sales_price = newPrice;
            }
          } else {
            updateData.sales_price = newPrice;
          }
        }
        // Default handling for other fields
        else {
          updateData[field] = req.body[field];
        }
      }
    }

    // If no fields to update, return early
    if (Object.keys(updateData).length === 0) {
      return res.json({
        success: true,
        msg: 'No changes to update',
        data: existingItem
      });
    }

    // Add updated timestamp
    updateData.updatedAt = new Date();

    // Perform the update
    const updatedItem = await Item.findByIdAndUpdate(
      id,
      { $set: updateData },
      { 
        new: true, 
        runValidators: true,
        context: 'query'
      }
    )
    .populate('brand_id', 'name')
    .populate('category_id', 'name')
    .populate('unit_id', 'name')
    .populate('tax_id', 'name percentage')
    .populate('discount_id', 'name type value')
    .populate('store_id', 'name')
    .lean();

    if (!updatedItem) {
      return res.status(404).json({ 
        success: false, 
        msg: 'Item not found or could not be updated' 
      });
    }

    res.json({ 
      success: true, 
      data: updatedItem, 
      msg: 'Item updated successfully',
      updatedFields: Object.keys(updateData)
    });
  } catch (error) {
    console.error('Error updating item:', error);
    
    // Handle duplicate key error
    if (error.code === 11000) {
      return res.status(400).json({
        success: false,
        msg: 'An item with this SKU or barcode already exists',
        field: error.keyPattern ? Object.keys(error.keyPattern)[0] : null
      });
    }
    
    // Handle validation errors
    if (error.name === 'ValidationError') {
      const messages = Object.values(error.errors).map(val => val.message);
      return res.status(400).json({
        success: false,
        msg: 'Validation error',
        errors: messages
      });
    }
    
    // Handle invalid ObjectId
    if (error.name === 'CastError') {
      return res.status(400).json({
        success: false,
        msg: 'Invalid item ID format'
      });
    }
    
    res.status(500).json({ 
      success: false, 
      msg: 'Error updating item',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Delete an item (hard delete)
// @route   DELETE /api/items/:id
// @access  Private/Admin
const deleteItem = async (req, res) => {
  try {
    const { id } = req.params;

    if (!id) {
      return res.status(400).json({
        success: false,
        msg: 'Item ID is required'
      });
    }

    // Find the item first to remove any uploaded file afterwards
    const item = await Item.findById(id);
    if (!item) {
      return res.status(404).json({
        success: false,
        msg: 'Item not found'
      });
    }

    // Delete the item from DB
    await Item.findByIdAndDelete(id);

    // Function to delete a single image file
    const deleteImageFile = async (imagePath) => {
      if (!imagePath) return false;

      // If it's an object with url property, use that
      if (typeof imagePath === 'object' && imagePath.url) {
        imagePath = imagePath.url;
      }

      if (!imagePath) {
        console.log('No valid image path found');
        return false;
      }

      // Extract just the filename from the path/URL
      const filename = path.basename(imagePath);
      
      // Try different possible locations for the file
      const possiblePaths = [
        // Direct path if it's an absolute path
        imagePath,
        // Relative to uploads/items/
        path.join(__dirname, '..', '..', 'uploads', 'items', filename),
        // Relative to uploads/
        path.join(__dirname, '..', '..', 'uploads', filename),
        // If the path includes 'uploads/items/' already
        path.join(process.cwd(), 'uploads', 'items', filename),
        // If the path is just the filename
        path.join(process.cwd(), 'uploads', filename)
      ];

      // Try each possible path until we find and delete the file
      for (const currentPath of possiblePaths) {
        try {
          console.log(`Attempting to delete item image at: ${currentPath}`);
          
          // Check if file exists and is accessible
          try {
            await fs.access(currentPath);
            // If we get here, file exists and is accessible
            await fs.unlink(currentPath);
            console.log(`Successfully deleted item image: ${currentPath}`);
            return true;
          } catch (accessError) {
            if (accessError.code === 'ENOENT') {
              // File doesn't exist, try next path
              continue;
            }
            // For other errors, log and continue
            console.error(`Error accessing file at ${currentPath}:`, accessError);
          }
        } catch (error) {
          console.error(`Unexpected error while processing ${currentPath}:`, error);
          // Continue to next path even if there's an error
        }
      }
      
      console.warn('Could not find or delete the item image at any of the following paths:', possiblePaths);
      return false;
    };

    // Delete main image if it exists (for backward compatibility)
    if (item.image) {
      try {
        await deleteImageFile(item.image);
      } catch (error) {
        console.error('Error during main image file deletion:', error);
        // Don't fail the entire operation if image deletion fails
      }
    }

    // Delete all images from the images array if it exists
    if (item.images && Array.isArray(item.images) && item.images.length > 0) {
      console.log(`Deleting ${item.images.length} associated images...`);
      
      // Delete each image file
      for (const img of item.images) {
        try {
          await deleteImageFile(img);
        } catch (error) {
          console.error('Error deleting image file:', error);
          // Continue with next image even if one fails
        }
      }
    }

    res.json({
      success: true,
      msg: 'Item permanently deleted'
    });
  } catch (error) {
    console.error('Error deleting item:', error);
    if (error.name === 'CastError') {
      return res.status(400).json({
        success: false,
        msg: 'Invalid item ID format'
      });
    }
    res.status(500).json({
      success: false,
      msg: 'Error deleting item',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Get items by category
// @route   GET /api/items/category/:categoryId
// @access  Public
const getItemsByCategory = async (req, res) => {
  try {
    const { categoryId } = req.params;
    const { page = 1, limit = 10, sort = 'name', order = 'asc' } = req.query;
    
    const options = {
      page: parseInt(page, 10),
      limit: parseInt(limit, 10),
      sort: { [sort]: order === 'desc' ? -1 : 1 },
      populate: [
        { path: 'brand_id', select: 'name' },
        { path: 'category_id', select: 'name' },
        { path: 'unit_id', select: 'name' },
        { path: 'tax_id', select: 'name percentage' },
        { path: 'discount_id', select: 'name type value' }
      ],
      lean: true
    };

    const result = await Item.findByCategory(categoryId, options);
    
    res.json({
      success: true,
      data: result.docs,
      pagination: {
        total: result.total,
        totalPages: result.pages,
        currentPage: result.page,
        hasNextPage: result.page < result.pages,
        hasPreviousPage: result.page > 1,
        limit: result.limit
      }
    });
  } catch (error) {
    console.error('Error fetching items by category:', error);
    res.status(500).json({ 
      success: false, 
      msg: 'Error fetching items by category',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Search items
// @route   GET /api/items/search
// @access  Public
const searchItems = async (req, res) => {
  try {
    const { q, page = 1, limit = 10 } = req.query;
    
    if (!q || q.trim() === '') {
      return res.status(400).json({
        success: false,
        msg: 'Search query is required'
      });
    }

    const result = await Item.search(q, {
      page: parseInt(page, 10),
      limit: parseInt(limit, 10),
      populate: [
        { path: 'brand_id', select: 'name' },
        { path: 'category_id', select: 'name' },
        { path: 'unit_id', select: 'name' }
      ],
      lean: true
    });

    res.json({
      success: true,
      data: result.docs,
      pagination: {
        total: result.total,
        totalPages: result.pages,
        currentPage: result.page,
        hasNextPage: result.page < result.pages,
        hasPreviousPage: result.page > 1,
        limit: result.limit
      }
    });
  } catch (error) {
    console.error('Error searching items:', error);
    res.status(500).json({ 
      success: false, 
      msg: 'Error searching items',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};
// @desc    Update item stock with movement tracking
// @route   PATCH /api/items/:id/stock
// @access  Private/Admin
const updateItemStock = async (req, res) => {
  try {
    console.log('Request body:', req.body);
    const { id } = req.params;
    const userId = req.user?.id || 'system';
    
    // Handle different request body formats
    let quantity, action, note, reference;
    
    if (req.body.quantity !== undefined) {
      // Direct properties format: { quantity: 10, action: 'add', ... }
      ({ quantity, action = 'set', note = 'Stock updated', reference = 'manual' } = req.body);
    } else if (req.body.data && typeof req.body.data === 'object') {
      // Nested data format: { data: { quantity: 10, action: 'add', ... } }
      ({ quantity, action = 'set', note = 'Stock updated', reference = 'manual' } = req.body.data);
    } else {
      return res.status(400).json({
        success: false,
        msg: 'Invalid request format. Expected { quantity: number, action?: string } or { data: { quantity: number, action?: string } }',
        received: req.body
      });
    }

    console.log('Parsed values:', { quantity, action, note, reference });
    
    // Parse and validate quantity
    console.log('Raw quantity value:', quantity, 'Type:', typeof quantity);
    
    // Convert quantity to a number and validate
    let quantityNum;
    if (typeof quantity === 'string') {
      quantityNum = parseFloat(quantity);
    } else if (typeof quantity === 'number') {
      quantityNum = quantity;
    } else {
      return res.status(400).json({
        success: false,
        msg: 'Quantity must be a valid number',
        received: quantity,
        type: typeof quantity,
        field: 'quantity',
        expected: 'A valid number (e.g., 5, 10.5, "15")'
      });
    }
    
    // Additional validation for the parsed number
    if (isNaN(quantityNum)) {
      return res.status(400).json({
        success: false,
        msg: 'Quantity must be a valid number',
        received: quantity,
        type: typeof quantity,
        field: 'quantity',
        parsed: quantityNum
      });
    }
    
    if (quantityNum < 0) {
      return res.status(400).json({
        success: false,
        msg: 'Quantity cannot be negative',
        received: quantity,
        field: 'quantity',
        expected: 'A non-negative number (0 or greater)'
      });
    }

    // Find the item
    const item = await Item.findById(id);
    if (!item) {
      return res.status(404).json({
        success: false,
        msg: 'Item not found'
      });
    }

    const previousQuantity = item.quantity;
    let newQuantity = previousQuantity;
    
    // Calculate new quantity based on action
    switch (action.toLowerCase()) {
      case 'add':
        newQuantity += quantityNum;
        break;
      case 'subtract':
        newQuantity = Math.max(0, newQuantity - quantityNum);
        break;
      case 'set':
      default:
        newQuantity = quantityNum;
    }

    // Create stock movement record
    const stockMovement = new StockMovement({
      product_id: id,
      product_name: item.name,
      sku: item.sku,
      type: action === 'add' ? 'in' : action === 'subtract' ? 'out' : 'adjustment',
      quantity: Math.abs(quantityNum),
      previous_quantity: previousQuantity,
      new_quantity: newQuantity,
      reference,
      notes: note,
      user: userId,
      created_at: new Date()
    });

    // Save stock movement first
    const savedMovement = await stockMovement.save();
    
    // Update item stock and add movement reference
    item.quantity = newQuantity;
    item.last_stock_update = new Date();
    
    if (!item.stock_movements) {
      item.stock_movements = [];
    }
    
    // Add the movement reference and save the updated item
    item.stock_movements.push(savedMovement._id);
    await item.save();

    res.json({
      success: true,
      data: {
        _id: item._id,
        name: item.name,
        sku: item.sku,
        previousQuantity,
        newQuantity,
        difference: newQuantity - previousQuantity,
        movementId: savedMovement._id
      },
      msg: 'Item stock updated successfully'
    });
  } catch (error) {
    console.error('Error updating item stock:', error);
    
    if (error.name === 'CastError') {
      return res.status(400).json({
        success: false,
        msg: 'Invalid item ID format'
      });
    }
    
    res.status(500).json({ 
      success: false, 
      msg: 'Error updating item stock',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// @desc    Get stock movements for an item
// @route   GET /api/items/:id/movements
// @access  Private/Admin
const getItemStockMovements = async (req, res) => {
  try {
    const { id } = req.params;
    const { page = 1, limit = 10 } = req.query;
    
    const item = await Item.findById(id);
    if (!item) {
      return res.status(404).json({
        success: false,
        msg: 'Item not found'
      });
    }
    
    const options = {
      page: parseInt(page, 10),
      limit: parseInt(limit, 10),
      sort: { created_at: -1 },
      populate: {
        path: 'user',
        select: 'name email'
      }
    };
    
    const result = await StockMovement.paginate(
      { product_id: id },
      options
    );
    
    res.json({
      success: true,
      data: {
        item: {
          _id: item._id,
          name: item.name,
          sku: item.sku,
          current_stock: item.quantity
        },
        movements: result.docs,
        pagination: {
          total: result.totalDocs,
          totalPages: result.totalPages,
          currentPage: result.page,
          hasNextPage: result.hasNextPage,
          hasPrevPage: result.hasPrevPage
        }
      }
    });
  } catch (error) {
    console.error('Error getting stock movements:', error);
    
    if (error.name === 'CastError') {
      return res.status(400).json({
        success: false,
        msg: 'Invalid item ID format'
      });
    }
    
    res.status(500).json({ 
      success: false, 
      msg: 'Error retrieving stock movements',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// Helper function to process CSV data
const processCSV = (buffer) => {
  return new Promise((resolve, reject) => {
    try {
      const csv = require('csv-parser');
      const { Readable } = require('stream');
      const csvResults = [];
      const errors = [];
      
      const stream = Readable.from(buffer.toString());
      let rowCount = 0;
      
      stream
        .pipe(csv({
          mapValues: ({ value }) => typeof value === 'string' ? value.trim() : value
        }))
        .on('headers', (headers) => {
          // Validate required headers
          const requiredHeaders = ['name', 'sku', 'price'];
          const missingHeaders = requiredHeaders.filter(h => !headers.includes(h));
          
          if (missingHeaders.length > 0) {
            return reject(new Error(`Missing required headers: ${missingHeaders.join(', ')}`));
          }
        })
        .on('data', (data) => {
          rowCount++;
          try {
            // Clean and validate row data
            const cleanedData = {};
            Object.entries(data).forEach(([key, value]) => {
              if (value !== undefined && value !== '') {
                // Convert header to snake_case
                const cleanKey = key.toLowerCase().trim().replace(/\s+/g, '_');
                cleanedData[cleanKey] = value;
              }
            });
            
            // Basic validation
            if (!cleanedData.name || !cleanedData.sku || !cleanedData.price) {
              throw new Error('Missing required fields (name, sku, or price)');
            }
            
            // Convert price to number
            cleanedData.price = parseFloat(cleanedData.price);
            if (isNaN(cleanedData.price)) {
              throw new Error('Price must be a valid number');
            }
            
            // Add timestamps
            cleanedData.createdAt = new Date();
            cleanedData.updatedAt = new Date();
            
            csvResults.push(cleanedData);
          } catch (error) {
            errors.push({
              row: rowCount + 1, // +1 for header row
              error: error.message,
              data: data
            });
          }
        })
        .on('end', () => {
          if (csvResults.length === 0 && errors.length > 0) {
            return reject(new Error('No valid rows found in CSV file'));
          }
          resolve({ data: csvResults, errors });
        })
        .on('error', (error) => {
          console.error('CSV processing error:', error);
          reject(new Error(`Error processing CSV: ${error.message}`));
        });
    } catch (error) {
      console.error('Error in processCSV:', error);
      reject(new Error(`Error initializing CSV processing: ${error.message}`));
    }
  });
};

const bulkUploadItems = async (req, res) => {
  // Add start time for processing duration
  const startTime = Date.now();
  
  try {
    // Check if we have a file to process
    if (!req.file) {
      return res.status(400).json({ 
        success: false, 
        msg: 'No file uploaded' 
      });
    }

    // Validate file buffer
    if (!req.file.buffer || req.file.buffer.length === 0) {
      return res.status(400).json({ 
        success: false, 
        msg: 'File is empty or corrupted' 
      });
    }
    
    // Log file info for debugging
    console.log('Processing file upload:', {
      originalname: req.file.originalname,
      mimetype: req.file.mimetype,
      size: req.file.size,
      bufferLength: req.file.buffer.length,
      first100Bytes: req.file.buffer.toString('hex', 0, 100)
    });
    
    // Initialize response data
    let results = [];
    let errors = [];
    const fileExtension = req.file.originalname.split('.').pop().toLowerCase();
    
    // Check file content to determine actual file type
    const fileSignature = req.file.buffer.toString('hex', 0, 4);
    const isCSV = fileSignature !== '504b0304'; // Check if not a ZIP file (XLSX is a ZIP archive)
    
    if (isCSV || fileExtension === 'csv') {
      console.log('Processing as CSV file');
      
      try {
        // Process CSV file
        const { data, errors: csvErrors } = await processCSV(req.file.buffer);
        results = data;
        errors = csvErrors;
        
        if (results.length === 0) {
          return res.status(400).json({
            success: false,
            msg: 'No valid data found in CSV file',
            errors: errors
          });
        }
        
      } catch (error) {
        console.error('Error processing CSV file:', error);
        return res.status(400).json({
          success: false,
          msg: 'Error processing CSV file',
          error: error.message
        });
      }
      
    } else if (fileExtension === 'xlsx' || fileExtension === 'xls') {
      console.log('Processing as Excel file with extension:', fileExtension);
      
      try {
        const ExcelJS = require('exceljs');
        const workbook = new ExcelJS.Workbook();
        
        // Log buffer info for debugging
        console.log('File buffer info:', {
          bufferLength: req.file.buffer.length,
          first100Bytes: req.file.buffer.toString('hex', 0, 100)
        });
        
        // Load the workbook with error handling
        // Check if file is actually a CSV with wrong extension
        const isActuallyCSV = req.file.buffer.toString('utf8', 0, 100).includes(',');
        
        if (isActuallyCSV) {
          console.log('File appears to be CSV with .xlsx extension. Processing as CSV...');
          const { data, errors: csvErrors } = await processCSV(req.file.buffer);
          results = data;
          errors = csvErrors;
          
          if (results.length === 0) {
            throw new Error('No valid data found in the file');
          }
          
          console.log(`Successfully processed ${results.length} rows as CSV`);
        } else {
          // Process as actual Excel file
          try {
            await workbook.xlsx.load(req.file.buffer);
            console.log('Successfully loaded Excel workbook');
          } catch (excelError) {
            console.error('Error loading Excel file:', excelError);
            throw new Error('The file could not be read as an Excel file. Please ensure it is a valid Excel file or try saving it as a CSV.');
          }
        }
        
        // Process the Excel file
        if (workbook.worksheets.length === 0) {
          throw new Error('No worksheets found in the Excel file');
        }
        
        const worksheet = workbook.worksheets[0];
        if (!worksheet) {
          throw new Error('No worksheet found in the Excel file');
        }
        
        // Get headers from the first row
        const headerRow = worksheet.getRow(1);
        const headers = [];
        headerRow.eachCell({ includeEmpty: true }, (cell, colNumber) => {
          headers[colNumber] = cell.value ? cell.value.toString().trim().toLowerCase().replace(/\s+/g, '_') : '';
        });
        
        // Validate required headers
        const requiredHeaders = ['name', 'sku', 'price'];
        const missingHeaders = requiredHeaders.filter(h => !headers.includes(h));
        
        if (missingHeaders.length > 0) {
          throw new Error(`Missing required headers: ${missingHeaders.join(', ')}`);
        }
        
        // Process each row
        worksheet.eachRow((row, rowNumber) => {
          if (rowNumber === 1) return; // Skip header row
          
          const rowData = {};
          let hasData = false;
          let hasError = false;
          const rowErrors = [];
          
          // Process each cell in the row
          row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
            const header = headers[colNumber];
            if (header) {
              let value = cell.value;
              
              // Convert date objects to ISO strings
              if (value instanceof Date) {
                value = value.toISOString();
              }
              
              // Convert numbers to proper types
              if (typeof value === 'string' && !isNaN(value) && value.trim() !== '') {
                value = parseFloat(value);
              }
              
              rowData[header] = value;
              
              if (value !== null && value !== undefined && value !== '') {
                hasData = true;
              }
            }
          });
          
          // Validate required fields
          requiredHeaders.forEach(header => {
            if (!rowData[header]) {
              rowErrors.push(`Missing required field: ${header}`);
              hasError = true;
            }
          });
          
          // Validate price is a number
          if (rowData.price && isNaN(parseFloat(rowData.price))) {
            rowErrors.push('Price must be a valid number');
            hasError = true;
          }
          
          if (hasData) {
            // Add timestamps
            rowData.createdAt = new Date();
            rowData.updatedAt = new Date();
            
            if (hasError) {
              errors.push({
                row: rowNumber,
                error: rowErrors.join('; '),
                data: rowData
              });
            } else {
              results.push(rowData);
            }
          }
        });
        
        console.log(`Processed ${results.length} rows from Excel file`);
      } catch (error) {
        console.error('Error processing Excel file:', error);
        
        // More specific error messages for common issues
        let errorMessage = 'Error processing the Excel file.';
        
        if (error.message.includes('not a valid Excel file')) {
          errorMessage = 'The uploaded file is not a valid Excel file. Please ensure it is a valid .xlsx or .xls file.';
        } else if (error.message.includes('corrupted')) {
          errorMessage = 'The Excel file appears to be corrupted. Please try exporting it again from Excel.';
        } else if (error.message.includes('password-protected')) {
          errorMessage = 'Password-protected Excel files are not supported. Please save the file without a password.';
        }
        
        return res.status(400).json({
          success: false,
          msg: errorMessage,
          error: process.env.NODE_ENV === 'development' ? error.message : undefined
        });
      }
    } else {
      return res.status(400).json({
        success: false,
        msg: 'Unsupported file type. Please upload a .xlsx, .xls, or .csv file.'
      });
    }
    
    // If we have no valid results but have errors, return them
    if (results.length === 0 && errors.length > 0) {
      return res.status(400).json({
        success: false,
        msg: 'No valid data found in the file',
        errors: errors,
        totalProcessed: 0,
        totalFailed: errors.length
      });
    }
    
    // Process items sequentially to ensure proper error handling and validation
    const savedItems = [];
    const saveErrors = [];
    
    console.log(`Processing ${results.length} items for database...`);
    
    // Helper function to safely convert string ID to ObjectId
    const toObjectId = (id) => {
      if (!id) return null;
      try {
        return new mongoose.Types.ObjectId(id);
      } catch (error) {
        console.error(`Invalid ObjectId: ${id}`, error);
        return null;
      }
    };
    
    // Check if required ID fields exist in the database
    const checkIdsExist = async (ids, modelName) => {
      if (!ids || ids.length === 0) return [];
      
      const validIds = ids.filter(id => mongoose.Types.ObjectId.isValid(id));
      if (validIds.length === 0) return [];
      
      const Model = getModel(modelName);
      const found = await Model.find({ _id: { $in: validIds } }).select('_id');
      return found.map(doc => doc._id.toString());
    };
    
    // First, collect all reference IDs to check
    const allStoreIds = [...new Set(results.map(item => item.store_id).filter(Boolean))];
    const allBrandIds = [...new Set(results.map(item => item.brand_id).filter(Boolean))];
    const allCategoryIds = [...new Set(results.map(item => item.category_id).filter(Boolean))];
    const allUnitIds = [...new Set(results.map(item => item.unit_id).filter(Boolean))];
    
    // Check which IDs actually exist in the database
    const [existingStoreIds, existingBrandIds, existingCategoryIds, existingUnitIds] = await Promise.all([
      checkIdsExist(allStoreIds, 'Store'),
      checkIdsExist(allBrandIds, 'Brand'),
      checkIdsExist(allCategoryIds, 'Category'),
      checkIdsExist(allUnitIds, 'Unit')
    ]);
    
    // Process each item
    for (let i = 0; i < results.length; i++) {
      const itemData = results[i];
      try {
        // Validate reference IDs exist before processing
        if (itemData.store_id && !existingStoreIds.includes(itemData.store_id)) {
          throw new Error(`Store ID ${itemData.store_id} not found`);
        }
        if (itemData.brand_id && !existingBrandIds.includes(itemData.brand_id)) {
          throw new Error(`Brand ID ${itemData.brand_id} not found`);
        }
        if (itemData.category_id && !existingCategoryIds.includes(itemData.category_id)) {
          throw new Error(`Category ID ${itemData.category_id} not found`);
        }
        if (itemData.unit_id && !existingUnitIds.includes(itemData.unit_id)) {
          throw new Error(`Unit ID ${itemData.unit_id} not found`);
        }
        
        // Clean and validate data
        const cleanData = {
          name: String(itemData.name || '').trim(),
          sku: String(itemData.sku || '').trim().toUpperCase(),
          store_id: toObjectId(itemData.store_id),
          unit_id: toObjectId(itemData.unit_id),
          category_id: toObjectId(itemData.category_id),
          brand_id: toObjectId(itemData.brand_id),
          description: itemData.description ? String(itemData.description).trim() : '',
          price: parseFloat(itemData.price) || 0,
          sales_price: parseFloat(itemData.sales_price) || 0,
          minimum_qty: parseInt(itemData.minimum_qty || itemData.min_quantity, 10) || 1,
          stock_quantity: parseInt(itemData.stock_quantity, 10) || 0,
          hsn: itemData.hsn ? String(itemData.hsn).trim() : '',
          barcode: itemData.barcode ? String(itemData.barcode).trim() : '',
          status: itemData.status !== undefined ? Boolean(itemData.status) : true,
          tax_type: itemData.tax_type || 'exclusive',
          tax_id: toObjectId(itemData.tax_id),
          expire_date: itemData.expire_date || null,
          slug: generateSlug(itemData.name),
          created_at: new Date(),
          updated_at: new Date()
        };
        
        // Validate required fields
        if (!cleanData.name) throw new Error('Item name is required');
        if (!cleanData.sku) throw new Error('SKU is required');
        if (!cleanData.store_id) throw new Error('Store ID is required');
        if (!cleanData.unit_id) throw new Error('Unit ID is required');
        if (!cleanData.category_id) throw new Error('Category ID is required');
        if (!cleanData.brand_id) throw new Error('Brand ID is required');
        
        try {
          // Check if item with same SKU already exists
          const existingItem = await Item.findOne({ sku: cleanData.sku });
          
          if (existingItem) {
            // Update existing item
            Object.assign(existingItem, cleanData);
            existingItem.updated_at = new Date();
            const savedItem = await existingItem.save();
            savedItems.push(savedItem);
          } else {
            // Create new item
            const newItem = new Item(cleanData);
            const savedItem = await newItem.save();
            savedItems.push(savedItem);
          }
        } catch (dbError) {
          console.error(`Database error for item ${cleanData.sku}:`, dbError);
          throw new Error(`Database error: ${dbError.message}`);
        }
        
      } catch (error) {
        console.error(`Error saving item at row ${i + 1}:`, error);
        saveErrors.push({
          row: i + 1,
          error: error.message,
          data: itemData
        });
      }
    }
    
    // Prepare response
    const successCount = savedItems.length;
    const failedCount = saveErrors.length + errors.length;
    
    console.log(`Successfully saved ${successCount} items to the database`);
    
    const response = {
      success: successCount > 0 || errors.length === 0,
      msg: successCount > 0 
        ? `Successfully processed ${successCount} items`
        : 'No items were processed successfully',
      totalProcessed: results.length,
      totalSuccess: successCount,
      totalFailed: failedCount
    };
    
    // Include errors if any
    if (saveErrors.length > 0 || errors.length > 0) {
      response.errors = [...errors, ...saveErrors];
    }
    
    // Log processing time
    const processingTime = (Date.now() - startTime) / 1000;
    console.log(`Bulk upload completed in ${processingTime.toFixed(2)} seconds`);
    
    // Return appropriate status code based on success/failure
    if (successCount === 0 && failedCount > 0) {
      return res.status(400).json(response);
    } else if (failedCount > 0) {
      return res.status(207).json(response); // Partial success
    }
    
    return res.json(response);
  } catch (error) {
    console.error('Unexpected error in bulkUploadItems:', error);
    
    // Log detailed error information
    const errorInfo = {
      message: error.message,
      name: error.name,
      stack: error.stack,
      code: error.code,
      keyPattern: error.keyPattern,
      keyValue: error.keyValue
    };

    console.error('Error in bulk upload:', JSON.stringify(errorInfo, null, 2));
    
    // Return detailed error response
    return res.status(500).json({
      success: false,
      message: 'Error processing bulk upload',
      error: error.message,
      ...(process.env.NODE_ENV === 'development' && {
        stack: error.stack,
        details: errorInfo
      })
    });
  }

    // Process each item in the results
    const processedResults = [];
    const errorResults = [];
    let successCount = 0;
    let errorCount = 0;
    let uploadedCount = 0;
    let skippedCount = 0;

    for (let index = 0; index < results.length; index++) {
    const itemData = results[index];
    try {
      // Map Excel column names to database fields
      const mappedData = {
        name: itemData['Product Name'] || itemData.name,
        sku: itemData['SKU'] || itemData.sku,
        store_id: itemData['Store'] || itemData.store_id,
        unit_id: itemData['Unit'] || itemData.unit_id,
        category_id: itemData['Category'] || itemData.category_id,
        brand_id: itemData['Brand'] || itemData.brand_id,
        price: itemData['Price'],
        sales_price: itemData['Sales Price'],
        min_quantity: itemData['Minimum Qty'],
        expiry_date: itemData['Expire Date'],
        description: itemData['Description']
      };

        // Validate required fields with better error messages
        const requiredFields = [
          { field: 'name', label: 'Product Name' },
          { field: 'sku', label: 'SKU' },
          { field: 'store_id', label: 'Store' },
          { field: 'unit_id', label: 'Unit' },
          { field: 'category_id', label: 'Category' },
          { field: 'brand_id', label: 'Brand' }
        ];
        
        const missingFields = requiredFields.filter(({ field }) => !mappedData[field]);
        
        if (missingFields.length > 0) {
          const missingFieldLabels = missingFields.map(({ label }) => label).join(', ');
          throw new Error(`Missing required fields: ${missingFieldLabels}`);
        }
        
        // Add the mapped data to processed results
        processedResults.push(mappedData);
        
        // If we get here, validation passed
      } catch (error) {
        // Record validation errors with row information
        validationErrors.push({
          row: index + 2, // +2 because: +1 for 1-based index, +1 for header row
          sku: itemData.sku || 'N/A',
          error: error.message,
          rawData: JSON.stringify(itemData, null, 2)
        });
      }
    
    // If there are validation errors, return them immediately
    if (validationErrors.length > 0) {
      return res.status(400).json({
        success: false,
        msg: `Found ${validationErrors.length} validation error(s) in the uploaded file. Please fix these issues and try again.`,
        errors: validationErrors,
        errorCount: validationErrors.length,
        totalCount: results.length
      });
    }
    
    // Process items sequentially
    for (const itemData of processedResults) {
      try {
        // Clean and validate data
        const cleanData = {
          name: String(itemData.name || '').trim(),
          sku: String(itemData.sku || '').trim().toUpperCase(),
          store_id: itemData.store_id,
          unit_id: itemData.unit_id,
          category_id: itemData.category_id,
          brand_id: itemData.brand_id,
          description: itemData.description ? String(itemData.description).trim() : '',
          price: parseFloat(itemData.price) || 0,
          sales_price: parseFloat(itemData.sales_price) || 0,
          min_quantity: parseInt(itemData.min_quantity, 10) || 1,
          expiry_date: itemData.expiry_date ? new Date(itemData.expiry_date) : null,
          status: 'active',
          unit: 'pcs',
          quantity: 0,
          min_stock_level: 0,
          tags: Array.isArray(itemData.tags) 
            ? itemData.tags.map(tag => String(tag).trim())
            : (itemData.tags ? String(itemData.tags).split(',').map(tag => tag.trim()) : [])
        };
        
        // Additional validation
        if (cleanData.price < 0) {
          throw new Error('Price cannot be negative');
        }
        
        if (cleanData.quantity < 0) {
          throw new Error('Quantity cannot be negative');
        }
        
        // Create or update item in database
        const existingItem = await Item.findOne({ sku: cleanData.sku });
        
        if (existingItem) {
          // Update existing item
          Object.assign(existingItem, cleanData);
          existingItem.updated_at = new Date();
          await existingItem.save();
        } else {
          // Create new item
          const newItem = new Item({
            ...cleanData,
            created_at: new Date(),
            updated_at: new Date()
          });
          await newItem.save();
        }
        
        uploadedCount++;
        
      } catch (error) {
        console.error(`Error processing item ${itemData.sku || 'unknown'}:`, error);
        errors.push({
          sku: itemData.sku || 'unknown',
          error: error.message,
          data: itemData
        });
      }
    }
    
    // Prepare detailed response
    const responseData = {
      success: true,
      message: `Successfully processed ${uploadedCount} of ${results.length} items`,
      summary: {
        totalItems: results.length,
        processedSuccessfully: uploadedCount,
        failed: errors.length,
        successRate: results.length > 0 ? Math.round((uploadedCount / results.length) * 100) : 0
      },
      details: {
        processedAt: new Date().toISOString(),
        filename: req.file.originalname,
        fileType: req.file.mimetype,
        fileSize: req.file.size,
        processingTime: `${(Date.now() - req.startTime) / 1000} seconds`
      }
    };

    // Log the upload summary
    console.log('Bulk upload completed:', JSON.stringify({
      ...responseData.summary,
      filename: req.file.originalname,
      processingTime: responseData.details.processingTime
    }, null, 2));

    // Return success response
    return res.json(responseData);
  }
};

const downloadTemplate = async (req, res) => {
  try {
    const workbook = new ExcelJS.Workbook();

    const itemsSheet = workbook.addWorksheet("items_bulk_upload_template");

    const itemColumns = [
      { header: "name", key: "name", width: 20 },
      { header: "sku", key: "sku", width: 15 },
      { header: "brand_id", key: "brand_id", width: 12 },
      { header: "category_id", key: "category_id", width: 15 },
      { header: "unit_id", key: "unit_id", width: 12 },
      { header: "hsn", key: "hsn", width: 15 },
      { header: "minimum_qty", key: "minimum_qty", width: 15 },
      { header: "expire_date", key: "expire_date", width: 15 },
      { header: "barcode", key: "barcode", width: 20 },
      { header: "description", key: "description", width: 30 },
      { header: "image", key: "image", width: 25 },
      { header: "price", key: "price", width: 12 },
      { header: "tax_id", key: "tax_id", width: 12 },
      { header: "tax_type", key: "tax_type", width: 15 },
      { header: "sales_price", key: "sales_price", width: 15 },
      { header: "discount_id", key: "discount_id", width: 15 },
      { header: "store_id", key: "store_id", width: 12 },
      { header: "status", key: "status", width: 12 }
    ];
    itemsSheet.columns = itemColumns;

    // Sample rows
    itemsSheet.addRow({
      name: "Sample Item 1",
      sku: "SKU001",
      brand_id: 1,
      category_id: 1,
      unit_id: 1,
      hsn: "HSN001",
      minimum_qty: 10,
      expire_date: "2025-12-31",
      barcode: "1234567890123",
      description: "This is a sample item description",
      image: "sample1.jpg",
      price: 29.99,
      tax_id: 1,
      tax_type: "inclusive",
      sales_price: 29.99,
      discount_id: 1,
      store_id: 1,
      status: 1,
    });
    itemsSheet.addRow({
      name: "Sample Item 2",
      sku: "SKU002",
      brand_id: 2,
      category_id: 1,
      unit_id: 1,
      hsn: "HSN002",
      minimum_qty: 5,
      expire_date: "2025-06-30",
      barcode: "9876543210987",
      description: "Another sample item description",
      image: "sample2.jpg",
      price: 49.99,
      tax_id: 1,
      tax_type: "exclusive",
      sales_price: 44.99,
      discount_id: 2,
      store_id: 1,
      status: 1,
    });

    const headerRow = itemsSheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFE0E0E0" },
    };

    const referenceTables = [
      { 
        name: 'brands', 
        model: 'Brand', 
        projection: { brand_name: 1, name: 1, status: 1 },
        sort: { brand_name: 1 },
        fields: ['_id', 'brand_name', 'name', 'status']
      },
      { 
        name: 'categories', 
        model: 'Category', 
        projection: { name: 1, status: 1 },
        sort: { name: 1 },
        fields: ['_id', 'name', 'status']
      },
      { 
        name: 'units', 
        model: 'Unit', 
        projection: { name: 1, status: 1 },
        sort: { name: 1 },
        fields: ['_id', 'name', 'status']
      },
      { 
        name: 'taxes', 
        model: 'Tax', 
        projection: { name: 1, status: 1 },
        sort: { name: 1 },
        fields: ['_id', 'name', 'rate', 'status']
      },
      { 
        name: 'discounts', 
        model: 'Discount', 
        projection: { name: 1, status: 1, discount_percent: 1, start_date: 1, end_date: 1 },
        sort: { name: 1 },
        fields: ['_id', 'name', 'discount_percent', 'start_date', 'end_date', 'status']
      },
      { 
        name: 'stores', 
        model: 'Store', 
        projection: { name: 1, status: 1, address: 1, phone: 1 },
        sort: { name: 1 },
        fields: ['_id', 'name', 'address', 'phone', 'status']
      }
    ];

    // Process each reference table
    for (const table of referenceTables) {
      try {
        const Model = getModel(table.model);
        if (!Model) {
          console.warn(`Model ${table.model} not found`);
          continue;
        }
        
        // Fetch data from MongoDB
        const rows = await Model.find(
          { status: 1 },
          table.projection
        ).sort(table.sort).lean();
        
        if (rows.length === 0) {
          console.warn(`No data found for ${table.name}`);
          continue;
        }
        
        // Create worksheet
        const sheet = workbook.addWorksheet(table.name);
        
        // Add headers
        const headers = table.fields;
        sheet.addRow(headers);
        
        // Set column widths
        headers.forEach((header, index) => {
          const column = sheet.getColumn(index + 1);
          column.width = Math.max(header.length + 2, 15); // Minimum width of 15
          
          // Set header style
          const headerCell = sheet.getCell(1, index + 1);
          headerCell.font = { bold: true };
          headerCell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFE0E0E0' }
          };
        });
        
        // Add data rows
        rows.forEach(row => {
          const values = headers.map(header => {
            // Handle nested fields
            if (header.includes('.')) {
              return header.split('.').reduce((obj, key) => 
                (obj && obj[key] !== undefined) ? obj[key] : '', 
                row
              );
            }
            // Format dates if needed
            if (row[header] instanceof Date) {
              return row[header].toISOString().split('T')[0];
            }
            return row[header] !== undefined ? row[header] : '';
          });
          sheet.addRow(values);
        });
      } catch (err) {
        console.error(`Failed to create ${table.name} sheet:`, err.message);
      }
    }

    const buffer = await workbook.xlsx.writeBuffer();
    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader(
      "Content-Disposition",
      'attachment; filename="items_bulk_upload_template.xlsx"'
    );
    res.end(buffer);
  } catch (error) {
    console.error('Error generating Excel template:', error);
    res.status(500).json({ success: false, msg: "Error generating template" });
  }
};

const bulkEditItems = async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ success: false, msg: 'No file uploaded' });
    }
    const results = [];
    const errors = [];
    let updatedCount = 0;

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(req.file.path);

    const worksheet = workbook.getWorksheet('items_bulk_edit_template');
    if (!worksheet) {
      return res.status(400).json({ success: false, msg: 'No "items_bulk_edit_template" sheet found in the Excel file' });
    }

    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber > 1) {
        const rowData = {};
        row.eachCell((cell, colNumber) => {
          const header = worksheet.getRow(1).getCell(colNumber).value;
          if (!header) return;
          const key = header.toString().trim().toLowerCase().replace(/\s+/g, '_');
          rowData[key] = cell.value;
        });
        results.push(rowData);
      }
    });

    for (let i = 0; i < results.length; i++) {
      const row = results[i];

      try {
        if (!row.name || !row.sku || !row.brand_id || !row.category_id || !row.unit_id) {
          errors.push(`Row ${i + 2}: Missing required fields (name, sku, brand_id, category_id, unit_id)`);
          continue;
        }

        const [existing] = await db.query(
          'SELECT id FROM items WHERE sku = ? AND status = 1',
          [row.sku]
        );
        if (existing.length === 0) {
          errors.push(`Row ${i + 2}: SKU "${row.sku}" not found`);
          continue;
        }

        const [result] = await db.query(`
          UPDATE items SET
            name = ?, brand_id = ?, category_id = ?, unit_id = ?, hsn = ?, minimum_qty = ?, expire_date = ?, barcode = ?, description = ?, image = ?, price = ?, tax_id = ?, tax_type = ?, sales_price = ?, discount_id = ?, store_id = ?, status = ?, updated_at = NOW()
          WHERE sku = ? AND status = 1
        `, [
          row.name,
          parseInt(row.brand_id) || null,
          parseInt(row.category_id) || null,
          parseInt(row.unit_id) || null,
          row.hsn || null,
          parseInt(row.minimum_qty) || 0,
          row.expire_date || null,
          row.barcode || null,
          row.description || '',
          row.image || null,
          parseFloat(row.price) || 0,
          parseInt(row.tax_id) || null,
          row.tax_type || 'inclusive',
          parseFloat(row.sales_price) || parseFloat(row.price) || 0,
          parseInt(row.discount_id) || null,
          parseInt(row.store_id) || null,
          parseInt(row.status) || 1,
          row.sku
        ]);

        if (result.affectedRows > 0) {
          updatedCount++;
        } else {
          errors.push(`Row ${i + 2}: Failed to update item with SKU "${row.sku}"`);
        }
      } catch (err) {
        console.error(`Error processing row ${i + 2}:`, err);
        errors.push(`Row ${i + 2}: ${err.message}`);
      }
    }

    fs.unlinkSync(req.file.path);

    res.json({
      success: true,
      data: {
        updated: updatedCount,
        total: results.length,
        errors: errors
      },
      msg: `Successfully updated ${updatedCount} items`
    });
  } catch (error) {
    console.error('Error in bulk edit:', error);
    res.status(500).json({ success: false, msg: 'Error processing bulk edit' });
  }
};

const downloadBulkEditTemplate = async (req, res) => {
  try {
    const workbook = new ExcelJS.Workbook();

    const itemsSheet = workbook.addWorksheet("items_bulk_edit_template");

    const itemColumns = [
      { header: "name", key: "name", width: 20 },
      { header: "sku", key: "sku", width: 15 },
      { header: "brand_id", key: "brand_id", width: 12 },
      { header: "category_id", key: "category_id", width: 15 },
      { header: "unit_id", key: "unit_id", width: 12 },
      { header: "hsn", key: "hsn", width: 15 },
      { header: "minimum_qty", key: "minimum_qty", width: 15 },
      { header: "expire_date", key: "expire_date", width: 15 },
      { header: "barcode", key: "barcode", width: 20 },
      { header: "description", key: "description", width: 30 },
      { header: "image", key: "image", width: 25 },
      { header: "price", key: "price", width: 12 },
      { header: "tax_id", key: "tax_id", width: 12 },
      { header: "tax_type", key: "tax_type", width: 15 },
      { header: "sales_price", key: "sales_price", width: 15 },
      { header: "discount_id", key: "discount_id", width: 15 },
      { header: "store_id", key: "store_id", width: 12 },
      { header: "status", key: "status", width: 12 }
    ];
    itemsSheet.columns = itemColumns;

    const [items] = await db.query(`
      SELECT 
        i.*,
        b.brand_name,
        c.name as category_name,
        u.name as unit_name
      FROM items i
      LEFT JOIN brands b ON i.brand_id = b.id
      LEFT JOIN categories c ON i.category_id = c.id
      LEFT JOIN units u ON i.unit_id = u.id
      WHERE i.status = 1
      ORDER BY i.created_at DESC
      LIMIT 100
    `);

    items.slice(0, 10).forEach(item => {
      itemsSheet.addRow({
        name: item.name,
        sku: item.sku,
        brand_id: item.brand_id,
        category_id: item.category_id,
        unit_id: item.unit_id,
        hsn: item.hsn,
        minimum_qty: item.minimum_qty,
        expire_date: item.expire_date,
        barcode: item.barcode,
        description: item.description,
        image: item.image,
        price: item.price,
        tax_id: item.tax_id,
        tax_type: item.tax_type,
        sales_price: item.sales_price,
        discount_id: item.discount_id,
        store_id: item.store_id,
        status: item.status,
      });
    });

    const headerRow = itemsSheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFE0E0E0" },
    };

    const referenceTables = [
      { name: "brands", query: "SELECT id, brand_name AS name FROM brands WHERE status = 1 ORDER BY brand_name" },
      { name: "categories", query: "SELECT id, name FROM categories WHERE status = 1 ORDER BY name" },
      { name: "units", query: "SELECT id, name FROM units WHERE status = 1 ORDER BY name" }
    ];

    for (const table of referenceTables) {
      try {
        const [rows] = await db.query(table.query);
        const sheet = workbook.addWorksheet(table.name);
        sheet.columns = [
          { header: "id", key: "id", width: 10 },
          { header: "name", key: "name", width: 25 },
        ];
        rows.forEach(r => sheet.addRow(r));
        sheet.getRow(1).font = { bold: true };
        sheet.getRow(1).fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: "FFE0E0E0" },
        };
      } catch (err) {
        console.error(`Failed to create ${table.name} sheet:`, err.message);
      }
    }

    const buffer = await workbook.xlsx.writeBuffer();
    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader(
      "Content-Disposition",
      'attachment; filename="items_bulk_edit_template.xlsx"'
    );
    res.end(buffer);
  } catch (error) {
    console.error('Error generating bulk edit template:', error);
    res.status(500).json({ success: false, msg: "Error generating bulk edit template" });
  }
};

// @desc    Bulk update items from Excel/CSV
// @route   PUT /api/items/bulk-update
// @access  Private/Admin
const bulkUpdateItems = async (req, res) => {
  const session = await mongoose.startSession();
  session.startTransaction();
  
  try {
    console.log('Bulk update request received');
    
    if (!req.file || !req.file.buffer) {
      console.error('No file or file buffer provided');
      return res.status(400).json({ 
        success: false, 
        msg: 'No file uploaded or file is empty' 
      });
    }

    const results = [];
    const errors = [];
    
    console.log(`Processing file: ${req.file.originalname}, Type: ${req.file.mimetype}`);
    
    try {
      // Process the file based on its type
      if (req.file.mimetype === 'text/csv' || req.file.originalname.endsWith('.csv')) {
        console.log('Processing CSV file');
        await new Promise((resolve, reject) => {
          const bufferStream = new Readable();
          bufferStream.push(req.file.buffer);
          bufferStream.push(null);
          
          bufferStream
            .pipe(csv())
            .on('data', (data) => {
              // Clean up the data (remove any undefined or null values)
              const cleanData = {};
              Object.keys(data).forEach(key => {
                if (data[key] !== undefined && data[key] !== null && data[key] !== '') {
                  cleanData[key] = data[key];
                }
              });
              results.push(cleanData);
            })
            .on('end', resolve)
            .on('error', (error) => {
              console.error('CSV processing error:', error);
              reject(new Error(`Error processing CSV: ${error.message}`));
            });
        });
      } else if (
        req.file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ||
        req.file.originalname.endsWith('.xlsx')
      ) {
        console.log('Processing Excel file');
        const workbook = new ExcelJS.Workbook();
        await workbook.xlsx.load(req.file.buffer);
        const worksheet = workbook.worksheets[0];
        
        if (!worksheet || worksheet.rowCount < 2) {
          throw new Error('Excel file is empty or has no data rows');
        }
        
        const headers = [];
        worksheet.getRow(1).eachCell((cell) => {
          if (cell.value) headers.push(String(cell.value).trim());
        });
        
        console.log(`Found ${worksheet.rowCount - 1} rows with headers:`, headers);
        
        worksheet.eachRow((row, rowNumber) => {
          if (rowNumber === 1) return; // Skip header row
          
          const rowData = {};
          row.eachCell((cell, colNumber) => {
            const header = headers[colNumber - 1];
            if (header && cell.value !== undefined && cell.value !== null) {
              rowData[header] = cell.value;
            }
          });
          
          if (Object.keys(rowData).length > 0) {
            results.push(rowData);
          }
        });
      } else {
        throw new Error('Invalid file format. Please upload a CSV or Excel file.');
      }
    } catch (fileError) {
      console.error('File processing error:', fileError);
      return res.status(400).json({ 
        success: false, 
        msg: `Error processing file: ${fileError.message}` 
      });
    }

    console.log(`Processed ${results.length} items from file`);
    
    // Process and update each item
    let updatedCount = 0;
    const bulkOps = [];
    
    for (const [index, item] of results.entries()) {
      try {
        if (!item._id && !item.sku) {
          errors.push({
            row: index + 2,
            id: item._id || 'N/A',
            error: 'Missing _id or sku for update'
          });
          continue;
        }

        const updateData = {};
        const validFields = [
          'name', 'description', 'price', 'sales_price', 'minimum_qty',
          'status', 'tax_type', 'hsn', 'barcode', 'expire_date',
          'brand_id', 'category_id', 'unit_id', 'tax_id', 'discount_id', 'store_id'
        ];

        // Log the raw item data for debugging
        console.log(`Processing row ${index + 2}:`, JSON.stringify(item, null, 2));

        // Only include fields that exist in the item and are not null/undefined
        validFields.forEach(field => {
          if (item[field] !== undefined && item[field] !== null && item[field] !== '') {
            try {
              // Convert numeric fields
              if (['price', 'sales_price', 'minimum_qty'].includes(field)) {
                const numValue = parseFloat(item[field]);
                if (!isNaN(numValue)) {
                  updateData[field] = numValue;
                } else {
                  throw new Error(`Invalid number value for ${field}: ${item[field]}`);
                }
              } 
              // Convert boolean fields
              else if (field === 'status') {
                const value = String(item[field]).toLowerCase();
                if (value === 'true' || value === '1' || value === 'yes') {
                  updateData[field] = true;
                } else if (value === 'false' || value === '0' || value === 'no') {
                  updateData[field] = false;
                } else {
                  throw new Error(`Invalid boolean value for status: ${item[field]}`);
                }
              }
              // Convert date fields
              else if (field === 'expire_date' && item[field]) {
                const dateValue = new Date(item[field]);
                if (dateValue.toString() !== 'Invalid Date') {
                  updateData[field] = dateValue;
                } else {
                  throw new Error(`Invalid date format for expire_date: ${item[field]}`);
                }
              }
              // Handle ObjectId fields
              else if (field.endsWith('_id')) {
                try {
                  if (mongoose.Types.ObjectId.isValid(item[field])) {
                    updateData[field] = new mongoose.Types.ObjectId(item[field]);
                  } else {
                    throw new Error(`Invalid ObjectId format for ${field}`);
                  }
                } catch (error) {
                  throw new Error(`Invalid ${field}: ${item[field]} - ${error.message}`);
                }
              }
              // Include other fields as-is
              else {
                updateData[field] = item[field];
              }
            } catch (fieldError) {
              console.error(`Error processing field ${field}:`, fieldError);
              throw new Error(`Error in field '${field}': ${fieldError.message}`);
            }
          }
        });

        // Skip if no valid fields to update
        if (Object.keys(updateData).length === 0) {
          errors.push({
            row: index + 2,
            id: item._id || item.sku || 'N/A',
            error: 'No valid fields to update'
          });
          continue;
        }

        // Build the query to find the item
        const query = {};
        if (item._id) {
          if (mongoose.Types.ObjectId.isValid(item._id)) {
            query._id = new mongoose.Types.ObjectId(item._id);
          } else {
            throw new Error(`Invalid _id format: ${item._id}`);
          }
        } else if (item.sku) {
          query.sku = item.sku;
        } else {
          throw new Error('Neither _id nor sku provided for update');
        }

        // Add updatedAt timestamp
        updateData.updatedAt = new Date();

        // Create update operation
        bulkOps.push({
          updateOne: {
            filter: query,
            update: { $set: updateData },
            upsert: false
          }
        });
        
        console.log(`Prepared update for ${item._id || item.sku}:`, updateData);
      } catch (error) {
        console.error(`Error processing row ${index + 2}:`, error);
        errors.push({
          row: index + 2,
          id: item._id || item.sku || 'N/A',
          error: error.message
        });
      }
    }

    console.log(`Prepared ${bulkOps.length} operations for bulk update`);
    
    // Execute bulk operations if there are any
    if (bulkOps.length > 0) {
      try {
        console.log('Executing bulk write operation...');
        const result = await Item.bulkWrite(bulkOps, { 
          ordered: false,
          writeConcern: { w: 'majority' },
          session
        });
        
        updatedCount = result.modifiedCount || 0;
        console.log(`Bulk write completed. Updated ${updatedCount} items.`);
        
        // Commit the transaction
        await session.commitTransaction();
        session.endSession();
        
        res.status(200).json({
          success: true,
          msg: `Successfully updated ${updatedCount} items.` + 
               (errors.length > 0 ? ` ${errors.length} items had errors.` : ''),
          updatedCount,
          errorCount: errors.length,
          errors: errors.length > 0 ? errors : undefined
        });
      } catch (error) {
        // Abort the transaction on error
        await session.abortTransaction();
        session.endSession();
        
        console.error('Bulk write error:', error);
        throw new Error(`Error updating items: ${error.message}`);
      }
    } else {
      // No valid operations to perform
      await session.abortTransaction();
      session.endSession();
      
      res.status(400).json({
        success: false,
        msg: 'No valid operations to perform',
        updatedCount: 0,
        errorCount: errors.length,
        errors: errors.length > 0 ? errors : undefined
      });
      return;
    }
  } catch (error) {
    console.error('Error in bulk update:', error);
    
    // Make sure to end the session in case of error
    if (session.inTransaction()) {
      await session.abortTransaction();
      session.endSession();
    }
    
    res.status(500).json({ 
      success: false, 
      msg: 'Error processing bulk update',
      error: error.message,
      stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
    });
  }
};

// @desc    Download bulk upload template
// @route   GET /api/items/bulk-upload-template
// @access  Private/Admin
const downloadBulkUploadTemplate = async (req, res) => {
  try {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Items Template');
    
    // Define headers with descriptions
    const headers = [
      { header: 'name', key: 'name', width: 30, note: 'Required. Product name' },
      { header: 'sku', key: 'sku', width: 20, note: 'Required. Unique SKU' },
      { header: 'description', key: 'description', width: 50, note: 'Product description' },
      { header: 'price', key: 'price', width: 15, note: 'Required. Base price (number)' },
      { header: 'sales_price', key: 'sales_price', width: 15, note: 'Sale price (number, optional)' },
      { header: 'minimum_qty', key: 'minimum_qty', width: 15, note: 'Minimum order quantity (number, default: 0)' },
      { header: 'hsn', key: 'hsn', width: 15, note: 'HSN code' },
      { header: 'barcode', key: 'barcode', width: 20, note: 'Barcode' },
      { header: 'status', key: 'status', width: 15, note: 'true/false (default: true)' },
      { header: 'tax_type', key: 'tax_type', width: 15, note: 'inclusive/exclusive (default: exclusive)' },
      { header: 'expire_date', key: 'expire_date', width: 20, note: 'YYYY-MM-DD format' },
      { header: 'brand_id', key: 'brand_id', width: 30, note: 'Brand ID (from Brands section)' },
      { header: 'category_id', key: 'category_id', width: 30, note: 'Category ID (from Categories section)' },
      { header: 'unit_id', key: 'unit_id', width: 20, note: 'Unit ID (from Units section)' },
      { header: 'tax_id', key: 'tax_id', width: 30, note: 'Tax ID (from Taxes section, optional)' },
      { header: 'discount_id', key: 'discount_id', width: 30, note: 'Discount ID (from Discounts section, optional)' },
      { header: 'store_id', key: 'store_id', width: 30, note: 'Store ID (from Stores section)' }
    ];

    // Add headers to worksheet
    worksheet.columns = headers.map(header => ({
      header: header.header,
      key: header.key,
      width: header.width
    }));

    // Add data validation for status and tax_type
    worksheet.getColumn('status').eachCell((cell, rowNumber) => {
      if (rowNumber > 1) { // Skip header row
        cell.dataValidation = {
          type: 'list',
          allowBlank: true,
          formulae: ['"TRUE,FALSE"'],
          showErrorMessage: true,
          errorStyle: 'error',
          errorTitle: 'Invalid Status',
          error: 'Please select TRUE or FALSE'
        };
      }
    });

    worksheet.getColumn('tax_type').eachCell((cell, rowNumber) => {
      if (rowNumber > 1) { // Skip header row
        cell.dataValidation = {
          type: 'list',
          allowBlank: true,
          formulae: ['"inclusive,exclusive"'],
          showErrorMessage: true,
          errorStyle: 'error',
          errorTitle: 'Invalid Tax Type',
          error: 'Please select "inclusive" or "exclusive"'
        };
      }
    });

    // Add sample data row
    const sampleDate = new Date();
    sampleDate.setFullYear(sampleDate.getFullYear() + 1); // 1 year from now
    
    const sampleData = {
      name: 'Sample Product',
      sku: 'SKU-' + Math.floor(1000 + Math.random() * 9000),
      description: 'This is a sample product',
      price: 99.99,
      sales_price: 89.99,
      minimum_qty: 1,
      hsn: '123456',
      barcode: '123456789012',
      status: true,
      tax_type: 'exclusive',
      expire_date: sampleDate.toISOString().split('T')[0],
      brand_id: 'brand-id-here',
      category_id: 'category-id-here',
      unit_id: 'unit-id-here',
      tax_id: 'tax-id-here',
      discount_id: 'discount-id-here',
      store_id: 'store-id-here'
    };
    
    worksheet.addRow(sampleData);

    // Set response headers
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=bulk-upload-template.xlsx'
    );

    // Send the file
    await workbook.xlsx.write(res);
    res.end();
  } catch (error) {
    console.error('Error generating template:', error);
    res.status(500).json({ 
      success: false, 
      msg: 'Error generating template',
      error: error.message 
    });
  }
};

// @desc    Export items to Excel
// @route   GET /api/items/export
// @access  Private/Admin
const exportItems = async (req, res) => {
  try {
    // Get all items with populated fields
    const items = await Item.find({})
      .populate('brand_id', 'name')
      .populate('category_id', 'name')
      .populate('unit_id', 'name')
      .populate('tax_id', 'name rate')
      .populate('store_id', 'name')
      .lean();

    // Create a new workbook
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Items');

    // Define headers
    const headers = [
      { header: 'ID', key: '_id', width: 25 },
      { header: 'Name', key: 'name', width: 30 },
      { header: 'SKU', key: 'sku', width: 15 },
      { header: 'Description', key: 'description', width: 40 },
      { header: 'Price', key: 'price', width: 12 },
      { header: 'Sales Price', key: 'sales_price', width: 12 },
      { header: 'Stock Quantity', key: 'stock_quantity', width: 15 },
      { header: 'Minimum Qty', key: 'minimum_qty', width: 12 },
      { header: 'HSN Code', key: 'hsn', width: 12 },
      { header: 'Barcode', key: 'barcode', width: 15 },
      { header: 'Status', key: 'status', width: 10 },
      { header: 'Tax Type', key: 'tax_type', width: 12 },
      { header: 'Expire Date', key: 'expire_date', width: 15 },
      { header: 'Brand ID', key: 'brand_id', width: 25 },
      { header: 'Brand Name', key: 'brand_name', width: 25 },
      { header: 'Category ID', key: 'category_id', width: 25 },
      { header: 'Category Name', key: 'category_name', width: 25 },
      { header: 'Unit ID', key: 'unit_id', width: 25 },
      { header: 'Unit Name', key: 'unit_name', width: 15 },
      { header: 'Tax ID', key: 'tax_id', width: 25 },
      { header: 'Tax Name', key: 'tax_name', width: 20 },
      { header: 'Store ID', key: 'store_id', width: 25 },
      { header: 'Store Name', key: 'store_name', width: 25 },
      { header: 'Created At', key: 'createdAt', width: 20 },
      { header: 'Updated At', key: 'updatedAt', width: 20 }
    ];

    // Add headers to worksheet
    worksheet.columns = headers.map(header => ({
      header: header.header,
      key: header.key,
      width: header.width
    }));

    // Add data rows
    items.forEach(item => {
      const row = {
        ...item,
        brand_name: item.brand_id?.name || '',
        category_name: item.category_id?.name || '',
        unit_name: item.unit_id?.name || '',
        tax_name: item.tax_id ? `${item.tax_id.name} (${item.tax_id.rate}%)` : '',
        store_name: item.store_id?.name || '',
        status: item.status ? 'Active' : 'Inactive'
      };
      
      // Format dates
      if (item.createdAt) {
        row.createdAt = new Date(item.createdAt).toISOString();
      }
      if (item.updatedAt) {
        row.updatedAt = new Date(item.updatedAt).toISOString();
      }
      if (item.expire_date) {
        row.expire_date = new Date(item.expire_date).toISOString().split('T')[0];
      }
      
      worksheet.addRow(row);
    });

    // Set response headers
    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=current-products-export.xlsx'
    );

    // Send the file
    await workbook.xlsx.write(res);
    res.end();
  } catch (error) {
    console.error('Error exporting items:', error);
    res.status(500).json({
      success: false,
      msg: 'Error exporting items',
      error: error.message
    });
  }
};

module.exports = {
  // CRUD Operations
  getAllItems,
  getItemById,
  getItemStockMovements,
  createItem,
  updateItem,
  deleteItem,
  
  // Export
  exportItems,
  
  // Category and Search
  getItemsByCategory,
  searchItems,
  
  // Stock Management
  updateItemStock,
  
  // Bulk Operations
  bulkUploadItems,
  bulkUpdateItems,
  downloadTemplate,
  bulkEditItems,
  downloadBulkEditTemplate,
  downloadBulkUploadTemplate
};
