// controllers/productController.js
const multer = require('multer');
const path = require('path');
const fs = require('fs').promises;
const { Product, Category, Brand, ProductVariant,ProductImage, Inventory } = require('../models');
const { Op } = require('sequelize');

const ExcelJS = require('exceljs');


// Configure multer
const upload = multer({
  storage: multer.memoryStorage(),
  limits: { fileSize: 10 * 1024 * 1024 }, // 10MB
  fileFilter: (req, file, cb) => {
    const allowed = [
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'application/vnd.ms-excel'
    ];
    if (allowed.includes(file.mimetype)) {
      cb(null, true);
    } else {
      cb(new Error('Only Excel files (.xlsx, .xls) are allowed'));
    }
  }
});

exports.getAllProductsDetailed = async (req, res) => {
  try {
    const { page = 1, limit = 20, categoryId, brandId, search, isActive } = req.query;
    const offset = (page - 1) * limit;

    const where = {};
    if (categoryId) where.categoryId = categoryId;
    if (brandId) where.brandId = brandId;
    if (isActive !== undefined) where.isActive = isActive === 'true';
    if (search) {
      where[Op.or] = [
        { name: { [Op.like]: `%${search}%` } },
        { sku: { [Op.like]: `%${search}%` } },
        { barcode: { [Op.like]: `%${search}%` } }
      ];
    }

    const { count, rows } = await Product.findAndCountAll({
      where,
      include: [
        { model: Category, as: 'category' },
        { model: Brand, as: 'brand' },
        // { model: ProductVariant, as: 'variants' },
        { model: Inventory, as: 'inventory' }
      ],
      limit: parseInt(limit),
      offset: parseInt(offset),
      order: [['name', 'ASC']]
    });

    console.log("PRODUCT OBJECT IS",rows)

    res.json({
      products: rows,
      totalPages: Math.ceil(count / limit),
      currentPage: parseInt(page),
      totalProducts: count
    });
  } catch (error) {
    console.log("ERROR IN FETCHING PRODUCTS:",error)
    res.status(500).json({ message: 'Error fetching products', error: error.message });
  }
};



// exports.getAllProducts = async (req, res) => {
//   try {

//     const { page = 1, limit = 20, search, isActive } = req.query;
//     const offset = (page - 1) * limit;

//   const { count, rows } = await Product.findAndCountAll({
//       where: { isActive: true }
//     });

//     console.log('Fetched Products:', rows);

//     const productData = rows.map(row => row.toJSON());

//     res.json({
//       products: rows,
//       productsjson: productData,
//       totalPages: Math.ceil(count / limit),
//       currentPage: parseInt(page),
//       totalProducts: count
//     });
//   } catch (error) {
//     res.status(500).json({ message: 'Error fetching products', error: error.message });
//   }
// };
exports.getAllProducts = async (req, res) => {
  try {
    const { page = 1, limit = 20, search, isActive } = req.query;
    const offset = (page - 1) * limit;

    // Validate pagination parameters
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    
    if (isNaN(pageNum) || pageNum < 1) {
      return res.status(400).json({ 
        message: 'Invalid page number',
        error: 'Page must be a positive integer'
      });
    }
    
    if (isNaN(limitNum) || limitNum < 1 || limitNum > 100) {
      return res.status(400).json({ 
        message: 'Invalid limit',
        error: 'Limit must be between 1 and 100'
      });
    }

    // Build where clause
    const whereClause = {};
    
    if (isActive !== undefined && isActive !== '') {
      whereClause.isActive = isActive === 'true';
    }
    
    if (search && search.trim() !== '') {
      whereClause[Op.or] = [
        { name: { [Op.like]: `%${search.trim()}%` } },
        { sku: { [Op.like]: `%${search.trim()}%` } },
        { barcode: { [Op.like]: `%${search.trim()}%` } }
      ];
    }

    // Fetch products with associations
    const { count, rows } = await Product.findAndCountAll({
      where: whereClause,
      limit: limitNum,
      offset: offset,
      include: [
        {
          model: Category,
          as: 'category',
          attributes: ['id', 'name', 'isActive'],
          required: false // LEFT JOIN instead of INNER JOIN
        },
        {
          model: Brand,
          as: 'brand',
          attributes: ['id', 'name', 'isActive'],
          required: false
        },
        {
          model: Inventory,
          as: 'inventory',
          attributes: ['id', 'quantityOnHand', 'quantityReserved', 'quantityAvailable'],
          required: false
        },
        {
          model: ProductImage,
          as: 'images',
          attributes: ['id', 'productId', 'imageUrl', 'altText', 'order', 'isDefault'],
          required: false,
          separate: true,
          order: [['order', 'ASC']]
        }
      ],
      order: [['createdAt', 'DESC']],
      distinct: true // Important for accurate count with associations
    });

    console.log('Fetched Products:', rows.length);

    // Safely map products to JSON
    const productData = rows.map(row => {
      try {
        return row.toJSON();
      } catch (err) {
        console.error('Error converting product to JSON:', err);
        return row.get({ plain: true });
      }
    });

    // Calculate total pages
    const totalPages = Math.ceil(count / limitNum);

    // Send response
    res.status(200).json({
      success: true,
      products: rows,
      productsjson: productData,
      totalPages: totalPages,
      currentPage: pageNum,
      totalProducts: count,
      perPage: limitNum
    });

  } catch (error) {
    console.error('Error fetching products:', error);
    
    // Handle specific Sequelize errors
    if (error.name === 'SequelizeDatabaseError') {
      return res.status(500).json({ 
        success: false,
        message: 'Database error occurred',
        error: 'Error querying products from database'
      });
    }
    
    if (error.name === 'SequelizeValidationError') {
      return res.status(400).json({ 
        success: false,
        message: 'Validation error',
        error: error.errors.map(e => e.message).join(', ')
      });
    }
    
    // Generic error response
    res.status(500).json({ 
      success: false,
      message: 'Error fetching products',
      error: process.env.NODE_ENV === 'development' ? error.message : 'Internal server error'
    });
  }
};

exports.getProductById = async (req, res) => {
  try {
    const product = await Product.findByPk(req.params.id, {
      include: [
        { model: Category, as: 'category' },
        { model: Brand, as: 'brand' },
        { model: ProductVariant, as: 'variants' },
        { model: Inventory, as: 'inventory' }
      ]
    });

    if (!product) {
      return res.status(404).json({ message: 'Product not found' });
    }

    res.json(product);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching product', error: error.message });
  }
};

exports.createProduct = async (req, res) => {
  try {
    const product = await Product.create(req.body);
    res.status(201).json({ message: 'Product created successfully', product });
  } catch (error) {
    console.log(error) 
    res.status(500).json({ message: 'Error creating product', error: error.message });
  }
};

exports.updateProduct = async (req, res) => {
  try {
    const product = await Product.findByPk(req.params.id);

    if (!product) {
      return res.status(404).json({ message: 'Product not found' });
    }

    await product.update(req.body);
    res.json({ message: 'Product updated successfully', product });
  } catch (error) {
    res.status(500).json({ message: 'Error updating product', error: error.message });
  }
};

exports.deleteProduct = async (req, res) => {
  try {
    const product = await Product.findByPk(req.params.id);

    if (!product) {
      return res.status(404).json({ message: 'Product not found' });
    }

    await product.update({ isActive: false });
    res.json({ message: 'Product deactivated successfully' });
  } catch (error) {
    res.status(500).json({ message: 'Error deleting product', error: error.message });
  }
};

exports.searchByBarcode = async (req, res) => {
  try {
    const { barcode } = req.params;

    const product = await Product.findOne({
      where: { barcode, isActive: true },
      include: [
        { model: Category, as: 'category' },
        { model: Brand, as: 'brand' },
        { model: Inventory, as: 'inventory' }
      ]
    });

    if (!product) {
      return res.status(404).json({ message: 'Product not found' });
    }

    res.json(product);
  } catch (error) {
    res.status(500).json({ message: 'Error searching product', error: error.message });
  }
};

// Get top products (this would need a separate tracking mechanism or SaleItems table)
exports.getTopProducts = async (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 5;

    // Placeholder - you'll need to implement product sales tracking
    // This could be done via a ProductSales table or SaleItems table
    const products = await Product.findAll({
      order: [['stock', 'DESC']],
      limit
    });

    console.log("TOP PRODUCTS:",products)

    const formatted = products.map((p, index) => ({
      id: p.id,
      name: p.name,
      sku: p.sku,
      sales_count: 0, // Would come from sales tracking
      total_quantity: 0,
      revenue: 0
    }));

    res.json(formatted);
  } catch (error) {
    console.error('Error fetching top products:', error);
    res.status(500).json({ error: 'Failed to fetch top products', details: error.message });
  }
};



// Add this to your productController.js

// Download Import Template
exports.downloadImportTemplate = async (req, res) => {
  try {
    const workbook = new ExcelJS.Workbook();
    const ws = workbook.addWorksheet('Products');

    // Define columns
    ws.columns = [
      { header: 'Product Name*', key: 'name', width: 30 },
      { header: 'SKU', key: 'sku', width: 15 },
      { header: 'Barcode', key: 'barcode', width: 18 },
      { header: 'Category', key: 'category', width: 20 },
      { header: 'Brand', key: 'brand', width: 20 },
      { header: 'Supplier', key: 'supplier', width: 20 },
      { header: 'Unit Price*', key: 'unitPrice', width: 12 },
      { header: 'Cost Price', key: 'costPrice', width: 12 },
      { header: 'Initial Stock', key: 'stock', width: 12 },
      { header: 'Reorder Level', key: 'reorderLevel', width: 15 },
      { header: 'Min Stock', key: 'minStock', width: 12 },
      { header: 'Max Stock', key: 'maxStock', width: 12 },
      { header: 'Track Inventory (Yes/No)', key: 'trackInventory', width: 20 },
      { header: 'Active (Yes/No)', key: 'active', width: 15 },
      { header: 'Description', key: 'description', width: 40 }
    ];

    // Style header
    ws.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } };
    ws.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF6B46C1' }
    };
    ws.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    ws.getRow(1).height = 25;

    // Add sample data
    ws.addRow({
      name: 'Samsung Galaxy S24',
      sku: 'SKU001',
      barcode: '1234567890123',
      category: 'Electronics',
      brand: 'Samsung',
      supplier: 'Tech Suppliers Ltd',
      unitPrice: 799.99,
      costPrice: 600.00,
      stock: 50,
      reorderLevel: 10,
      minStock: 5,
      maxStock: 200,
      trackInventory: 'Yes',
      active: 'Yes',
      description: 'Latest Samsung flagship smartphone'
    });

    ws.addRow({
      name: 'Nike Air Max Shoes',
      sku: 'SKU002',
      barcode: '9876543210987',
      category: 'Clothing',
      brand: 'Nike',
      supplier: 'Fashion Distributors',
      unitPrice: 129.99,
      costPrice: 80.00,
      stock: 100,
      reorderLevel: 20,
      minStock: 10,
      maxStock: 500,
      trackInventory: 'Yes',
      active: 'Yes',
      description: 'Premium athletic footwear'
    });

    // Instructions sheet
    const instructionWs = workbook.addWorksheet('Instructions');
    instructionWs.columns = [
      { width: 80 }
    ];

    instructionWs.addRow(['PRODUCT IMPORT TEMPLATE - INSTRUCTIONS']).font = { 
      bold: true, 
      size: 14 
    };
    instructionWs.addRow([]);
    instructionWs.addRow(['Required Fields (marked with *):']);
    instructionWs.addRow(['  • Product Name: Must be unique and not empty']);
    instructionWs.addRow(['  • Unit Price: Must be greater than 0']);
    instructionWs.addRow([]);
    instructionWs.addRow(['Optional Fields:']);
    instructionWs.addRow(['  • SKU: Stock Keeping Unit (must be unique if provided)']);
    instructionWs.addRow(['  • Category & Brand: Will be created automatically if they don\'t exist']);
    instructionWs.addRow(['  • Track Inventory: Use "Yes" or "No" (default: No)']);
    instructionWs.addRow(['  • Active: Use "Yes" or "No" (default: No)']);
    instructionWs.addRow([]);
    instructionWs.addRow(['Tips:']);
    instructionWs.addRow(['  • Do not modify column headers']);
    instructionWs.addRow(['  • Delete the sample data rows before importing your products']);
    instructionWs.addRow(['  • Maximum file size: 10MB']);
    instructionWs.addRow(['  • Supported formats: .xlsx']);

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

    await workbook.xlsx.write(res);
    res.end();

  } catch (error) {
    console.error('Template generation error:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Failed to generate template' 
    });
  }
};




// =============== EXCEL IMPORT PRODUCTS ===============
exports.importProductsFromExcel = async (req, res) => {
  const singleUpload = upload.single('file');

  singleUpload(req, res, async (err) => {
    if (err) return res.status(400).json({ success: false, message: err.message });
    if (!req.file) return res.status(400).json({ success: false, message: 'No file uploaded' });

    const workbook = new ExcelJS.Workbook();
    let imported = 0;
    let skipped = 0;
    const errors = [];

    // ROBUST boolean parser
    const toBool = (cell) => {
      if (cell === null || cell === undefined || cell === '') return false;
      const val = String(cell).trim().toLowerCase();
      return ['yes', 'y', 'true', 't', '1', 'active', 'on'].includes(val);
    };

    try {
      await workbook.xlsx.load(req.file.buffer);
      const ws = workbook.worksheets[0];
      
      if (!ws || ws.actualRowCount < 2) {
        return res.status(400).json({ 
          success: false, 
          message: 'No data found in Excel file' 
        });
      }

      const categoryCache = {};
      const brandCache = {};

      // FIXED: MySQL-compatible case-insensitive search
      const findOrCreate = async (Model, name, cache) => {
        if (!name) return null;
        
        const trimmedName = String(name).trim();
        const key = trimmedName.toLowerCase();
        
        // Check cache first
        if (cache[key]) return cache[key];

        try {
          // MySQL: Use LIKE with LOWER() for case-insensitive search
          // Or use sequelize.where with sequelize.fn
          let record = await Model.findOne({ 
            where: {
              name: {
                [Op.like]: trimmedName // MySQL's LIKE is case-insensitive by default
              }
            }
          });

          // If not found, create new
          if (!record) {
            record = await Model.create({ 
              name: trimmedName, 
              isActive: true 
            });
          }

          cache[key] = record;
          return record;
        } catch (error) {
          console.error(`Error finding/creating ${Model.name}:`, error);
          return null;
        }
      };

      console.log(`Processing ${ws.actualRowCount - 1} rows...`);

      // Process rows starting from row 2
      for (let r = 2; r <= ws.actualRowCount; r++) {
        const row = ws.getRow(r);
        const get = (col) => row.getCell(col).value;

        try {
          // Extract data from Excel columns
          const data = {
            name: String(get(1) || '').trim(),
            sku: String(get(2) || '').trim() || null,
            barcode: String(get(3) || '').trim() || null,
            unitPrice: parseFloat(get(7)) || 0,
            costPrice: parseFloat(get(8)) || 0,
            description: String(get(15) || '').trim() || '',
            isTrackInventory: toBool(get(13)),
            isActive: toBool(get(14)),
            stock: parseInt(get(9)) || 0,
            reorderLevel: parseInt(get(10)) || 0,
            minStock: parseInt(get(11)) || 0,
            maxStock: parseInt(get(12)) || 0,
          };

          // Validation
          if (!data.name) {
            errors.push(`Row ${r}: Product name is required`);
            skipped++;
            continue;
          }

          if (!data.unitPrice || data.unitPrice <= 0) {
            errors.push(`Row ${r}: Unit price must be greater than 0`);
            skipped++;
            continue;
          }

          // Get Category & Brand names
          const catName = String(get(4) || '').trim();
          const brandName = String(get(5) || '').trim();

          // Find or create Category and Brand
          const [category, brand] = await Promise.all([
            catName ? findOrCreate(Category, catName, categoryCache) : null,
            brandName ? findOrCreate(Brand, brandName, brandCache) : null,
          ]);

          data.categoryId = category?.id || null;
          data.brandId = brand?.id || null;

          // Check for duplicate SKU
          if (data.sku) {
            const exists = await Product.findOne({ 
              where: { sku: data.sku } 
            });
            
            if (exists) {
              errors.push(`Row ${r}: SKU "${data.sku}" already exists`);
              skipped++;
              continue;
            }
          }

          // Create product
          const product = await Product.create(data);

          // Create inventory record if tracking is enabled
          if (data.isTrackInventory) {
            await Inventory.create({
              productId: product.id,
              currentStock: data.stock,
              minStock: data.minStock,
              maxStock: data.maxStock,
              reorderLevel: data.reorderLevel,
            });
          }

          imported++;
          
        } catch (e) {
          console.error(`Row ${r} error:`, e);
          errors.push(`Row ${r}: ${e.message || 'Invalid data'}`);
          skipped++;
        }
      }

      // Send response
      console.log(`Import completed: ${imported} imported, ${skipped} skipped`);
      
      res.json({
        success: true,
        message: `Import completed: ${imported} imported, ${skipped} skipped`,
        imported,
        skipped,
        errors: errors.length > 0 ? errors.slice(0, 10) : undefined, // Limit errors to first 10
      });

    } catch (error) {
      console.error('Import error:', error);
      res.status(500).json({ 
        success: false, 
        message: 'Server error during import',
        error: error.message 
      });
    }
  });
};






// Configure multer for image uploads
const storage = multer.diskStorage({
  destination: async (req, file, cb) => {
    const uploadDir = path.join(__dirname, '../uploads/products');
    
    // Create directory if it doesn't exist
    try {
      await fs.mkdir(uploadDir, { recursive: true });
      cb(null, uploadDir);
    } catch (error) {
      cb(error, null);
    }
  },
  filename: (req, file, cb) => {
    // Generate unique filename: timestamp-randomstring-originalname
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    const ext = path.extname(file.originalname);
    const nameWithoutExt = path.basename(file.originalname, ext);
    cb(null, `${nameWithoutExt}-${uniqueSuffix}${ext}`);
  }
});

// File filter to accept only images
const fileFilter = (req, file, cb) => {
  const allowedTypes = /jpeg|jpg|png|gif|webp/;
  const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
  const mimetype = allowedTypes.test(file.mimetype);

  if (extname && mimetype) {
    cb(null, true);
  } else {
    cb(new Error('Aina ya faili haibali! Only images are allowed (JPEG, PNG, GIF, WEBP)'), false);
  }
};

// Multer upload instance
const upload_ = multer({
  storage: storage,
  fileFilter: fileFilter,
  limits: {
    fileSize: 5 * 1024 * 1024 // 5MB max file size
  }
}).array('images', 10); // Allow up to 10 images at once

// Upload product images
exports.uploadProductImages = async (req, res) => {
  upload_(req, res, async (err) => {
    if (err instanceof multer.MulterError) {
      if (err.code === 'LIMIT_FILE_SIZE') {
        return res.status(400).json({
          success: false,
          message: 'Faili kubwa sana! File size exceeds 5MB limit'
        });
      }
      if (err.code === 'LIMIT_FILE_COUNT') {
        return res.status(400).json({
          success: false,
          message: 'Picha nyingi sana! Maximum 10 images allowed'
        });
      }
      return res.status(400).json({
        success: false,
        message: err.message
      });
    } else if (err) {
      return res.status(400).json({
        success: false,
        message: err.message
      });
    }

    try {
      if (!req.files || req.files.length === 0) {
        return res.status(400).json({
          success: false,
          message: 'Hakuna picha zilizopakiwa | No images uploaded'
        });
      }

      // Generate URLs for uploaded images
      const imageUrls = req.files.map(file => {
        // Return relative URL that frontend can use
        return `/uploads/products/${file.filename}`;
      });

      res.status(200).json({
        success: true,
        message: `${req.files.length} picha zimepakiwa | ${req.files.length} images uploaded successfully`,
        imageUrls: imageUrls,
        count: req.files.length
      });

    } catch (error) {
      console.error('Error uploading images:', error);
      
      // Clean up uploaded files if there's an error
      if (req.files) {
        for (const file of req.files) {
          try {
            await fs.unlink(file.path);
          } catch (unlinkError) {
            console.error('Error deleting file:', unlinkError);
          }
        }
      }

      res.status(500).json({
        success: false,
        message: 'Imeshindikana kupakia picha | Failed to upload images',
        error: error.message
      });
    }
  });
};

// Get product images
exports.getProductImages = async (req, res) => {
  try {
    const { productId } = req.params;

    const images = await ProductImage.findAll({
      where: { productId },
      order: [['order', 'ASC'], ['isDefault', 'DESC']]
    });

    res.status(200).json({
      success: true,
      images: images,
      count: images.length
    });

  } catch (error) {
    console.error('Error fetching product images:', error);
    res.status(500).json({
      success: false,
      message: 'Imeshindikana kupata picha | Failed to fetch product images',
      error: error.message
    });
  }
};

// Delete product image
exports.deleteProductImage = async (req, res) => {
  try {
    const { imageId } = req.params;

    const image = await ProductImage.findByPk(imageId);

    if (!image) {
      return res.status(404).json({
        success: false,
        message: 'Picha haipatikani | Image not found'
      });
    }

    // Delete file from filesystem
    const imagePath = path.join(__dirname, '..', image.imageUrl);
    try {
      await fs.unlink(imagePath);
    } catch (error) {
      console.error('Error deleting image file:', error);
      // Continue even if file deletion fails
    }

    // Delete from database
    await image.destroy();

    res.status(200).json({
      success: true,
      message: 'Picha imefutwa | Image deleted successfully'
    });

  } catch (error) {
    console.error('Error deleting product image:', error);
    res.status(500).json({
      success: false,
      message: 'Imeshindikana kufuta picha | Failed to delete image',
      error: error.message
    });
  }
};

// Update product with images (modified version of your existing create/update)
exports.createProductWithImages = async (req, res) => {
  try {
    const {
      name,
      sku,
      barcode,
      description,
      unitPrice,
      costPrice,
      stock,
      categoryId,
      brandId,
      isActive,
      isTrackInventory,
      reorderLevel,
      minStock,
      maxStock,
      images // Array of image objects: [{ imageUrl, order, isDefault }]
    } = req.body;

    // Validate required fields
    if (!name || !sku || !unitPrice) {
      return res.status(400).json({
        success: false,
        message: 'Taarifa muhimu zinakosekana | Required fields missing'
      });
    }

    // Create product
    const product = await Product.create({
      name,
      sku,
      barcode,
      description,
      unitPrice,
      costPrice: costPrice || 0,
      stock: stock || 0,
      categoryId: categoryId || null,
      brandId: brandId || null,
      isActive: isActive !== undefined ? isActive : true,
      isTrackInventory: isTrackInventory || false,
      reorderLevel: reorderLevel || 0,
      minStock: minStock || 0,
      maxStock: maxStock || 0
    });

    // Create product images if provided
    if (images && Array.isArray(images) && images.length > 0) {
      const imageRecords = images.map((img, index) => ({
        productId: product.id,
        imageUrl: img.imageUrl,
        altText: img.altText || `${name} - Image ${index + 1}`,
        order: img.order !== undefined ? img.order : index,
        isDefault: img.isDefault !== undefined ? img.isDefault : index === 0
      }));

      await ProductImage.bulkCreate(imageRecords);
    }

    // Fetch complete product with images
    const completeProduct = await Product.findByPk(product.id, {
      include: [
        { model: ProductImage, as: 'images' }
      ]
    });

    res.status(201).json({
      success: true,
      message: 'Bidhaa imeongezwa! | Product created successfully',
      product: completeProduct
    });

  } catch (error) {
    console.error('Error creating product:', error);
    res.status(500).json({
      success: false,
      message: 'Imeshindikana kuongeza bidhaa | Failed to create product',
      error: error.message
    });
  }
};

// Update product with images
exports.updateProductWithImages = async (req, res) => {
  try {
    const { id } = req.params;
    const {
      name,
      sku,
      barcode,
      description,
      unitPrice,
      costPrice,
      stock,
      categoryId,
      brandId,
      isActive,
      isTrackInventory,
      reorderLevel,
      minStock,
      maxStock,
      images // Array of image objects
    } = req.body;

    // Find product
    const product = await Product.findByPk(id);

    if (!product) {
      return res.status(404).json({
        success: false,
        message: 'Bidhaa haipatikani | Product not found'
      });
    }

    // Update product
    await product.update({
      name,
      sku,
      barcode,
      description,
      unitPrice,
      costPrice,
      stock,
      categoryId: categoryId || null,
      brandId: brandId || null,
      isActive,
      isTrackInventory,
      reorderLevel,
      minStock,
      maxStock
    });

    // Update images if provided
    if (images && Array.isArray(images)) {
      // Delete existing images
      const existingImages = await ProductImage.findAll({
        where: { productId: id }
      });

      // Delete old image files
      for (const img of existingImages) {
        const imagePath = path.join(__dirname, '..', img.imageUrl);
        try {
          await fs.unlink(imagePath);
        } catch (error) {
          console.error('Error deleting old image file:', error);
        }
      }

      // Delete old records
      await ProductImage.destroy({ where: { productId: id } });

      // Create new image records
      if (images.length > 0) {
        const imageRecords = images.map((img, index) => ({
          productId: id,
          imageUrl: img.imageUrl,
          altText: img.altText || `${name} - Image ${index + 1}`,
          order: img.order !== undefined ? img.order : index,
          isDefault: img.isDefault !== undefined ? img.isDefault : index === 0
        }));

        await ProductImage.bulkCreate(imageRecords);
      }
    }

    // Fetch updated product with images
    const updatedProduct = await Product.findByPk(id, {
      include: [
        { model: ProductImage, as: 'images' }
      ]
    });

    res.status(200).json({
      success: true,
      message: 'Bidhaa imeboreshwa! | Product updated successfully',
      product: updatedProduct
    });

  } catch (error) {
    console.error('Error updating product:', error);
    res.status(500).json({
      success: false,
      message: 'Imeshindikana kuboresha bidhaa | Failed to update product',
      error: error.message
    });
  }
};



