// controllers/inventoryController.js
const { Inventory, Product, ProductVariant, Store,User, StockMovement,StockAdjustment, Category, Brand, sequelize } = require('../models');
const { Op } = require('sequelize');

// ✅ Fetch all inventory (with optional filters including lowStock)
exports.getInventory = async (req, res) => {
  try {
    const { storeId, productId, lowStock } = req.query;
    const where = {};
    
    // Apply store filter if provided
    if (storeId) where.storeId = storeId;
    
    // Apply product filter if provided
    if (productId) where.productId = productId;

    // Fetch inventory with all associations
    const inventory = await Inventory.findAll({
      where,
      include: [
        { 
          model: Product, 
          as: 'product', 
          include: [
            { model: Category, as: 'category' },
            { model: Brand, as: 'brand' }
          ]
        },
        { model: ProductVariant, as: 'productVariant' },
        { model: Store, as: 'store' }
      ],
      order: [['quantityAvailable', 'ASC']]
    });

    // Filter for low stock items if requested
    let filteredInventory = inventory;
    if (lowStock === 'true') {
      filteredInventory = inventory.filter(item => {
        // Check if product exists and compare available quantity with reorder level
        if (!item.product) return false;
        const reorderLevel = item.product.reorderLevel || 0;
        const availableQty = item.quantityAvailable || 0;
        return availableQty <= reorderLevel;
      });
    }

    res.status(200).json(filteredInventory);
  } catch (error) {
    console.error("INVENTORY ERROR:", error);
    res.status(500).json({ 
      message: 'Error fetching inventory', 
      error: error.message 
    });
  }
};

// ✅ Get only low stock items (alternative dedicated endpoint)
exports.getLowStockItems = async (req, res) => {
  try {
    const { storeId } = req.query;
    const where = {};
    
    if (storeId) where.storeId = storeId;

    const inventory = await Inventory.findAll({
      where,
      include: [
        { 
          model: Product, 
          as: 'product',
          include: [
            { model: Category, as: 'category' },
            { model: Brand, as: 'brand' }
          ]
        },
        { model: ProductVariant, as: 'productVariant' },
        { model: Store, as: 'store' }
      ]
    });

    // Filter items where available quantity is at or below reorder level
    const lowStockItems = inventory.filter(item => {
      if (!item.product) return false;
      const reorderLevel = item.product.reorderLevel || 0;
      const availableQty = item.quantityAvailable || 0;
      return availableQty <= reorderLevel;
    });

    res.status(200).json(lowStockItems);
  } catch (error) {
    console.error("LOW STOCK ERROR:", error);
    res.status(500).json({ 
      message: 'Error fetching low stock items', 
      error: error.message 
    });
  }
};

// ✅ Get inventory summary/statistics
exports.getInventorySummary = async (req, res) => {
  try {
    const { storeId } = req.query;
    const where = {};
    
    if (storeId) where.storeId = storeId;

    const inventory = await Inventory.findAll({
      where,
      include: [
        { 
          model: Product, 
          as: 'product',
          attributes: ['costPrice', 'price', 'reorderLevel']
        }
      ]
    });

    // Calculate summary statistics
    const totalItems = inventory.length;
    const totalOnHand = inventory.reduce((sum, item) => sum + (item.quantityOnHand || 0), 0);
    const totalReserved = inventory.reduce((sum, item) => sum + (item.quantityReserved || 0), 0);
    const totalAvailable = inventory.reduce((sum, item) => sum + (item.quantityAvailable || 0), 0);
    
    const totalValue = inventory.reduce((sum, item) => {
      const costPrice = item.product?.costPrice || 0;
      const quantity = item.quantityOnHand || 0;
      return sum + (costPrice * quantity);
    }, 0);

    const totalRetailValue = inventory.reduce((sum, item) => {
      const retailPrice = item.product?.price || 0;
      const quantity = item.quantityOnHand || 0;
      return sum + (retailPrice * quantity);
    }, 0);

    const lowStockCount = inventory.filter(item => {
      if (!item.product) return false;
      const reorderLevel = item.product.reorderLevel || 0;
      const availableQty = item.quantityAvailable || 0;
      return availableQty <= reorderLevel;
    }).length;

    const outOfStockCount = inventory.filter(item => 
      (item.quantityAvailable || 0) === 0
    ).length;

    res.status(200).json({
      totalItems,
      totalOnHand,
      totalReserved,
      totalAvailable,
      totalCostValue: totalValue,
      totalRetailValue: totalRetailValue,
      potentialProfit: totalRetailValue - totalValue,
      lowStockCount,
      outOfStockCount
    });
  } catch (error) {
    console.error("INVENTORY SUMMARY ERROR:", error);
    res.status(500).json({ 
      message: 'Error fetching inventory summary', 
      error: error.message 
    });
  }
};

// ✅ Adjust inventory quantity
exports.adjustInventory = async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { productId, productVariantId, storeId, quantity, movementType, notes, performedBy } = req.body;

    // Validate required fields
    if (!productId || !storeId || quantity === undefined || !movementType) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Missing required fields: productId, storeId, quantity, movementType' 
      });
    }

    // Find inventory record
    const inventory = await Inventory.findOne({
      where: { 
        productId, 
        storeId, 
        productVariantId: productVariantId || null 
      }
    });

    if (!inventory) {
      await t.rollback();
      return res.status(404).json({ message: 'Inventory record not found' });
    }

    // Calculate new quantities
    const quantityChange = parseInt(quantity);
    const newQuantityOnHand = inventory.quantityOnHand + quantityChange;

    // Prevent negative stock
    if (newQuantityOnHand < 0) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Insufficient stock. Cannot reduce below zero.' 
      });
    }

    // Update inventory
    const newQuantityAvailable = newQuantityOnHand - inventory.quantityReserved;
    
    await inventory.update({
      quantityOnHand: newQuantityOnHand,
      quantityAvailable: newQuantityAvailable,
      lastRestocked: ['purchase', 'adjustment_in', 'transfer_in'].includes(movementType) 
        ? new Date() 
        : inventory.lastRestocked
    }, { transaction: t });

    // Log stock movement
    await StockMovement.create({
      productId,
      productVariantId: productVariantId || null,
      storeId,
      movementType,
      quantity: quantityChange,
      notes: notes || null,
      performedBy: performedBy || null,
      movementDate: new Date()
    }, { transaction: t });

    await t.commit();
    
    res.status(200).json({ 
      message: 'Inventory adjusted successfully', 
      inventory 
    });
  } catch (error) {
    await t.rollback();
    console.error("ADJUST INVENTORY ERROR:", error);
    res.status(500).json({ 
      message: 'Error adjusting inventory', 
      error: error.message 
    });
  }
};

// ✅ Get all stock movement history
exports.getStockMovements = async (req, res) => {
  try {
    const { productId, storeId, movementType, startDate, endDate, limit = 100 } = req.query;
    const where = {};
    
    if (productId) where.productId = productId;
    if (storeId) where.storeId = storeId;
    if (movementType) where.movementType = movementType;
    
    // Date range filter
    if (startDate || endDate) {
      where.movementDate = {};
      if (startDate) where.movementDate[Op.gte] = new Date(startDate);
      if (endDate) where.movementDate[Op.lte] = new Date(endDate);
    }

    const movements = await StockMovement.findAll({
      where,
      include: [
        { model: Product, as: 'product', attributes: ['id', 'name', 'sku'] },
        // { model: ProductVariant, as: 'variant', attributes: ['id', 'name'] },
        { model: Store, as: 'store', attributes: ['id', 'name'] }
      ],
      order: [['movementDate', 'DESC']],
      limit: parseInt(limit)
    });
    
    res.status(200).json(movements);
  } catch (error) {
    console.error("STOCK MOVEMENTS ERROR:", error);
    res.status(500).json({ 
      message: 'Error fetching stock movements', 
      error: error.message 
    });
  }
};

// ✅ Transfer stock between stores
exports.transferStock = async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { productId, productVariantId, fromStoreId, toStoreId, quantity, performedBy } = req.body;

    // Validate required fields
    if (!productId || !fromStoreId || !toStoreId || !quantity) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Missing required fields: productId, fromStoreId, toStoreId, quantity' 
      });
    }

    if (fromStoreId === toStoreId) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Cannot transfer to the same store' 
      });
    }

    const transferQty = parseInt(quantity);
    if (transferQty <= 0) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Transfer quantity must be greater than zero' 
      });
    }

    // Find source and destination inventory records
    const sourceInventory = await Inventory.findOne({
      where: { 
        productId, 
        productVariantId: productVariantId || null, 
        storeId: fromStoreId 
      }
    });
    
    const destInventory = await Inventory.findOne({
      where: { 
        productId, 
        productVariantId: productVariantId || null, 
        storeId: toStoreId 
      }
    });

    if (!sourceInventory) {
      await t.rollback();
      return res.status(404).json({ 
        message: 'Source inventory record not found' 
      });
    }

    if (!destInventory) {
      await t.rollback();
      return res.status(404).json({ 
        message: 'Destination inventory record not found. Please create inventory record for destination store first.' 
      });
    }

    // Check if sufficient stock is available
    if (sourceInventory.quantityAvailable < transferQty) {
      await t.rollback();
      return res.status(400).json({ 
        message: `Insufficient available stock. Only ${sourceInventory.quantityAvailable} units available.` 
      });
    }

    // Update source inventory (subtract)
    await sourceInventory.update({
      quantityOnHand: sourceInventory.quantityOnHand - transferQty,
      quantityAvailable: sourceInventory.quantityAvailable - transferQty
    }, { transaction: t });

    // Update destination inventory (add)
    await destInventory.update({
      quantityOnHand: destInventory.quantityOnHand + transferQty,
      quantityAvailable: destInventory.quantityAvailable + transferQty,
      lastRestocked: new Date()
    }, { transaction: t });

    // Log stock movements for both stores
    await StockMovement.bulkCreate([
      {
        productId,
        productVariantId: productVariantId || null,
        storeId: fromStoreId,
        movementType: 'transfer_out',
        quantity: -transferQty,
        notes: `Transfer to store ${toStoreId}`,
        performedBy: performedBy || null,
        movementDate: new Date()
      },
      {
        productId,
        productVariantId: productVariantId || null,
        storeId: toStoreId,
        movementType: 'transfer_in',
        quantity: transferQty,
        notes: `Received from store ${fromStoreId}`,
        performedBy: performedBy || null,
        movementDate: new Date()
      }
    ], { transaction: t });

    await t.commit();
    
    res.status(200).json({ 
      message: 'Stock transferred successfully',
      transfer: {
        productId,
        productVariantId,
        fromStoreId,
        toStoreId,
        quantity: transferQty
      }
    });
  } catch (error) {
    await t.rollback();
    console.error("TRANSFER STOCK ERROR:", error);
    res.status(500).json({ 
      message: 'Error transferring stock', 
      error: error.message 
    });
  }
};

// ✅ Get inventory by product ID
exports.getInventoryByProduct = async (req, res) => {
  try {
    const { productId } = req.params;

    const inventory = await Inventory.findAll({
      where: { productId },
      include: [
        { 
          model: Product, 
          as: 'product',
          include: [
            { model: Category, as: 'category' },
            { model: Brand, as: 'brand' }
          ]
        },
        { model: ProductVariant, as: 'productVariant' },
        { model: Store, as: 'store' }
      ],
      order: [['store', 'name', 'ASC']]
    });

    if (!inventory || inventory.length === 0) {
      return res.status(404).json({ 
        message: 'No inventory found for this product' 
      });
    }

    res.status(200).json(inventory);
  } catch (error) {
    console.error("GET INVENTORY BY PRODUCT ERROR:", error);
    res.status(500).json({ 
      message: 'Error fetching inventory by product', 
      error: error.message 
    });
  }
};

// ✅ Reserve inventory (for orders)
exports.reserveInventory = async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { productId, productVariantId, storeId, quantity, orderId } = req.body;

    if (!productId || !storeId || !quantity) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Missing required fields: productId, storeId, quantity' 
      });
    }

    const reserveQty = parseInt(quantity);
    if (reserveQty <= 0) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Reserve quantity must be greater than zero' 
      });
    }

    const inventory = await Inventory.findOne({
      where: { 
        productId, 
        storeId, 
        productVariantId: productVariantId || null 
      }
    });

    if (!inventory) {
      await t.rollback();
      return res.status(404).json({ message: 'Inventory record not found' });
    }

    if (inventory.quantityAvailable < reserveQty) {
      await t.rollback();
      return res.status(400).json({ 
        message: `Insufficient available stock. Only ${inventory.quantityAvailable} units available.` 
      });
    }

    await inventory.update({
      quantityReserved: inventory.quantityReserved + reserveQty,
      quantityAvailable: inventory.quantityAvailable - reserveQty
    }, { transaction: t });

    await StockMovement.create({
      productId,
      productVariantId: productVariantId || null,
      storeId,
      movementType: 'reservation',
      quantity: reserveQty,
      notes: orderId ? `Reserved for order ${orderId}` : 'Inventory reserved',
      movementDate: new Date()
    }, { transaction: t });

    await t.commit();
    
    res.status(200).json({ 
      message: 'Inventory reserved successfully', 
      inventory 
    });
  } catch (error) {
    await t.rollback();
    console.error("RESERVE INVENTORY ERROR:", error);
    res.status(500).json({ 
      message: 'Error reserving inventory', 
      error: error.message 
    });
  }
};

// ✅ Release reserved inventory
exports.releaseReservation = async (req, res) => {
  const t = await sequelize.transaction();
  try {
    const { productId, productVariantId, storeId, quantity, orderId } = req.body;

    if (!productId || !storeId || !quantity) {
      await t.rollback();
      return res.status(400).json({ 
        message: 'Missing required fields: productId, storeId, quantity' 
      });
    }

    const releaseQty = parseInt(quantity);

    const inventory = await Inventory.findOne({
      where: { 
        productId, 
        storeId, 
        productVariantId: productVariantId || null 
      }
    });

    if (!inventory) {
      await t.rollback();
      return res.status(404).json({ message: 'Inventory record not found' });
    }

    if (inventory.quantityReserved < releaseQty) {
      await t.rollback();
      return res.status(400).json({ 
        message: `Cannot release ${releaseQty} units. Only ${inventory.quantityReserved} units are reserved.` 
      });
    }

    await inventory.update({
      quantityReserved: inventory.quantityReserved - releaseQty,
      quantityAvailable: inventory.quantityAvailable + releaseQty
    }, { transaction: t });

    await StockMovement.create({
      productId,
      productVariantId: productVariantId || null,
      storeId,
      movementType: 'release',
      quantity: releaseQty,
      notes: orderId ? `Released from order ${orderId}` : 'Reservation released',
      movementDate: new Date()
    }, { transaction: t });

    await t.commit();
    
    res.status(200).json({ 
      message: 'Reservation released successfully', 
      inventory 
    });
  } catch (error) {
    await t.rollback();
    console.error("RELEASE RESERVATION ERROR:", error);
    res.status(500).json({ 
      message: 'Error releasing reservation', 
      error: error.message 
    });
  }
};


exports.createStockAdjustment = async (req, res) => {
  const t = await sequelize.transaction();

  try {
    const {
      storeId,          // ← changed from warehouseId
      adjustmentDate,
      reason,
      type,             // 'positive' or 'negative'
      notes,
      items = []
    } = req.body;

    const adjustedBy = req.user.id; // from auth middleware

    if (!items || items.length === 0) {
      return res.status(400).json({ message: 'At least one item is required' });
    }

    let totalValue = 0;

    for (const item of items) {
      const { productId, quantity, cost, notes: itemNotes } = item;

      if (!productId || !quantity) {
        throw new Error('Product and quantity are required');
      }

      const product = await Product.findByPk(productId);
      if (!product) throw new Error(`Product not found: ${productId}`);

      // const qtyChange = type === 'positive' ? Math.abs(quantity) : -Math.abs(quantity);
      // const newQuantity = product.stock + qtyChange;

      const qtyChange = type === 'positive'
  ? Math.abs(Number(quantity))
  : -Math.abs(Number(quantity));

const currentStock = Number(product.stock);
const newQuantity = currentStock + qtyChange;

      console.log(`Adjusting Product ID ${productId}: Current Stock = ${product.stock}, Change = ${qtyChange}, New Stock = ${newQuantity}`);

      if (newQuantity < 0) {
        throw new Error(`Cannot adjust ${product.name} below zero`);
      }

      const itemCost = cost || product.costPrice || 0;
      const itemValue = itemCost * qtyChange;

      await StockAdjustment.create({
        productId,
        variantId: null,
        storeId,                              // ← changed
        adjustmentType: type === 'positive' ? 'addition' : 'deduction',
        quantityBefore: product.stock,
        quantityAdjusted: qtyChange,
        quantityAfter: newQuantity,
        reason,
        referenceType: 'manual_adjustment',
        referenceId: null,
        cost: itemCost,
        totalValue: itemValue,
        adjustedBy,
        approvedBy: adjustedBy,
        adjustmentDate: adjustmentDate || new Date(),
        notes: notes || itemNotes,
        status: 'approved'
      }, { transaction: t });

      await product.update({ stock: newQuantity }, { transaction: t });

      totalValue += itemValue;
    }

    await t.commit();

    res.status(201).json({
      success: true,
      message: 'Stock adjusted successfully',
      totalValue
    });
  } catch (error) {
    await t.rollback();
    console.error('Stock adjustment error:', error);
    res.status(500).json({ success: false, message: error.message });
  }
};

exports.getStockMovementNew = async (req, res) => {
  try {
    // Pagination
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;

    // Filters
    const {
      productId,
      storeId,
      performedBy,
      movementType, // adjustment_in, adjustment_out
      fromDate,
      toDate,
      sortBy,
      sortOrder
    } = req.query;

    const whereClause = {};

    if (productId) whereClause.productId = productId;
    if (storeId) whereClause.storeId = storeId;
    if (performedBy) whereClause.performedBy = performedBy;
    if (movementType) whereClause.movementType = movementType;

    // Date Range Filter
    if (fromDate || toDate) {
      whereClause.movementDate = {};

      if (fromDate) {
        whereClause.movementDate[Op.gte] = new Date(fromDate);
      }
      if (toDate) {
        whereClause.movementDate[Op.lte] = new Date(toDate + " 23:59:59");
      }
    }

    // Sorting options
    const sortField = sortBy || "movementDate";
    const order = sortOrder?.toUpperCase() === "ASC" ? "ASC" : "DESC";

    // Database Query
    const { rows, count } = await StockMovement.findAndCountAll({
      where: whereClause,
      order: [[sortField, order]],
      limit,
      offset,

      // Include related models
      include: [
        {
         model: Product, 
          as: 'product',
          attributes: ["id", "name", "sku"]
        },
        // {
        //   model: ProductVariant,
        //    as: 'product',
        //   attributes: ["id", "variantName"]
        // },
        {
          model: Store,
           as: 'store',
          attributes: ["id", "name"]
        },
        {
          model: User,
          as: 'user',
          attributes: ["id", "firstName", "lastName"]
        }
      ]
    });

    // Pagination metadata
    const totalPages = Math.ceil(count / limit);

    res.status(200).json({
      message: "Stock adjustments fetched successfully",
      data: rows,
      pagination: {
        total: count,
        page,
        limit,
        totalPages
      }
    });

  } catch (error) {
    console.error("FETCH STOCK ADJUSTMENTS ERROR:", error);
    res.status(500).json({
      message: "Failed to fetch stock adjustments",
      error: error.message
    });
  }
};


// ✅ Get all stock adjustments with filters and pagination
exports.getStockAdjustments = async (req, res) => {
  try {
    // Pagination
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;

    // Filters
    const {
      productId,
      warehouseId,
      adjustmentType, // 'addition' or 'deduction'
      status,
      reason,
      adjustedBy,
      approvedBy,
      startDate,
      endDate,
      sortBy = 'adjustmentDate',
      sortOrder = 'DESC'
    } = req.query;

    const whereClause = {};

    // Apply filters
    if (productId) whereClause.productId = productId;
    if (warehouseId) whereClause.warehouseId = warehouseId;
    if (adjustmentType) whereClause.adjustmentType = adjustmentType;
    if (status) whereClause.status = status;
    if (reason) whereClause.reason = reason;
    if (adjustedBy) whereClause.adjustedBy = adjustedBy;
    if (approvedBy) whereClause.approvedBy = approvedBy;

    // Date range filter
    if (startDate || endDate) {
      whereClause.adjustmentDate = {};
      if (startDate) {
        whereClause.adjustmentDate[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereClause.adjustmentDate[Op.lte] = new Date(endDate + ' 23:59:59');
      }
    }

    // Sorting
    const order = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    // Database query
    const { rows: adjustments, count } = await StockAdjustment.findAndCountAll({
      where: whereClause,
      order: [[sortBy, order]],
      limit,
      offset,
      include: [
        {
          model: Product,
          attributes: ['id', 'name', 'sku', 'costPrice', 'unitPrice']
        },
        {
          model: User,
          as: 'adjustedByUser',
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: User,
          as: 'approvedByUser',
          attributes: ['id', 'firstName', 'lastName', 'email']
        }
      ]
    });

    // Pagination metadata
    const totalPages = Math.ceil(count / limit);

    res.status(200).json({
      message: 'Stock adjustments fetched successfully',
      data: adjustments,
      pagination: {
        total: count,
        page,
        limit,
        totalPages,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1
      }
    });
  } catch (error) {
    console.error('GET STOCK ADJUSTMENTS ERROR:', error);
    res.status(500).json({
      message: 'Error fetching stock adjustments',
      error: error.message
    });
  }
};

// ✅ Get single stock adjustment by ID
exports.getStockAdjustmentById = async (req, res) => {
  try {
    const { id } = req.params;

    const adjustment = await StockAdjustment.findByPk(id, {
      include: [
        {
          model: Product,
          attributes: ['id', 'name', 'sku', 'costPrice', 'price']
        },
        {
          model: User,
          as: 'adjustedByUser',
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: User,
          as: 'approvedByUser',
          attributes: ['id', 'firstName', 'lastName', 'email']
        }
      ]
    });

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

    res.status(200).json({
      message: 'Stock adjustment fetched successfully',
      data: adjustment
    });
  } catch (error) {
    console.error('GET STOCK ADJUSTMENT BY ID ERROR:', error);
    res.status(500).json({
      message: 'Error fetching stock adjustment',
      error: error.message
    });
  }
};

// ✅ Get stock adjustment summary/statistics
exports.getStockAdjustmentSummary = async (req, res) => {
  try {
    const { warehouseId, startDate, endDate } = req.query;
    const whereClause = {};

    if (warehouseId) whereClause.warehouseId = warehouseId;

    // Date range filter
    if (startDate || endDate) {
      whereClause.adjustmentDate = {};
      if (startDate) {
        whereClause.adjustmentDate[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereClause.adjustmentDate[Op.lte] = new Date(endDate + ' 23:59:59');
      }
    }

    const adjustments = await StockAdjustment.findAll({
      where: whereClause,
      attributes: [
        'adjustmentType',
        'totalValue',
        'quantityAdjusted',
        'status'
      ]
    });

    // Calculate summary statistics
    const totalAdjustments = adjustments.length;
    
    const additions = adjustments.filter(a => a.adjustmentType === 'addition');
    const deductions = adjustments.filter(a => a.adjustmentType === 'deduction');
    
    const totalAdditions = additions.reduce((sum, a) => sum + Math.abs(a.quantityAdjusted || 0), 0);
    const totalDeductions = deductions.reduce((sum, a) => sum + Math.abs(a.quantityAdjusted || 0), 0);
    
    const totalValueAdded = additions.reduce((sum, a) => sum + parseFloat(a.totalValue || 0), 0);
    const totalValueDeducted = deductions.reduce((sum, a) => sum + Math.abs(parseFloat(a.totalValue || 0)), 0);
    
    const netQuantityChange = totalAdditions - totalDeductions;
    const netValueChange = totalValueAdded - totalValueDeducted;

    // Status breakdown
    const statusBreakdown = adjustments.reduce((acc, adj) => {
      const status = adj.status || 'unknown';
      acc[status] = (acc[status] || 0) + 1;
      return acc;
    }, {});

    res.status(200).json({
      message: 'Stock adjustment summary fetched successfully',
      summary: {
        totalAdjustments,
        totalAdditions,
        totalDeductions,
        netQuantityChange,
        totalValueAdded,
        totalValueDeducted,
        netValueChange,
        statusBreakdown,
        additionsCount: additions.length,
        deductionsCount: deductions.length
      }
    });
  } catch (error) {
    console.error('GET STOCK ADJUSTMENT SUMMARY ERROR:', error);
    res.status(500).json({
      message: 'Error fetching stock adjustment summary',
      error: error.message
    });
  }
};

// ✅ Get stock adjustments by product
exports.getStockAdjustmentsByProduct = async (req, res) => {
  try {
    const { productId } = req.params;
    const { startDate, endDate, limit = 50 } = req.query;

    const whereClause = { productId };

    // Date range filter
    if (startDate || endDate) {
      whereClause.adjustmentDate = {};
      if (startDate) {
        whereClause.adjustmentDate[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereClause.adjustmentDate[Op.lte] = new Date(endDate + ' 23:59:59');
      }
    }

    const adjustments = await StockAdjustment.findAll({
      where: whereClause,
      order: [['adjustmentDate', 'DESC']],
      limit: parseInt(limit),
      include: [
        {
          model: Product,
          attributes: ['id', 'name', 'sku']
        },
        {
          model: User,
          as: 'adjustedByUser',
          attributes: ['id', 'firstName', 'lastName']
        }
      ]
    });

    if (!adjustments || adjustments.length === 0) {
      return res.status(404).json({
        message: 'No stock adjustments found for this product'
      });
    }

    res.status(200).json({
      message: 'Stock adjustments fetched successfully',
      data: adjustments,
      count: adjustments.length
    });
  } catch (error) {
    console.error('GET STOCK ADJUSTMENTS BY PRODUCT ERROR:', error);
    res.status(500).json({
      message: 'Error fetching stock adjustments by product',
      error: error.message
    });
  }
};

// ✅ Get pending stock adjustments (awaiting approval)
exports.getPendingAdjustments = async (req, res) => {
  try {
    const { warehouseId } = req.query;
    const whereClause = { status: 'pending' };

    if (warehouseId) whereClause.warehouseId = warehouseId;

    const pendingAdjustments = await StockAdjustment.findAll({
      where: whereClause,
      order: [['adjustmentDate', 'DESC']],
      include: [
        {
          model: Product,
          attributes: ['id', 'name', 'sku']
        },
        {
          model: User,
          as: 'adjustedByUser',
          attributes: ['id', 'firstName', 'lastName', 'email']
        }
      ]
    });

    res.status(200).json({
      message: 'Pending adjustments fetched successfully',
      data: pendingAdjustments,
      count: pendingAdjustments.length
    });
  } catch (error) {
    console.error('GET PENDING ADJUSTMENTS ERROR:', error);
    res.status(500).json({
      message: 'Error fetching pending adjustments',
      error: error.message
    });
  }
};

module.exports = exports;


