// controllers/purchaseOrderController.js
const { PurchaseOrder, PurchaseOrderItem,StockMovement, Product, ProductVariant, Supplier, Store, User } = require('../models');
const { Op } = require('sequelize');

// Get all purchase orders with pagination and filters
// exports.getAllPurchaseOrders = async (req, res) => {
//   try {
//     const { page = 1, limit = 10, status, supplierId, storeId, search } = req.query;
//     const offset = (page - 1) * limit;

//     const where = {};
//     if (status) where.status = status;
//     if (supplierId) where.supplierId = supplierId;
//     if (storeId) where.storeId = storeId;
//     if (search) {
//       where.orderNumber = { [Op.like]: `%${search}%` };
//     }

//     const { count, rows } = await PurchaseOrder.findAndCountAll({
//       where,
//       limit: parseInt(limit),
//       offset: parseInt(offset),
//       include: [
//         { model: Supplier, as: 'supplier', attributes: ['id', 'name', 'email', 'phone'] },
//         { model: Store, as: 'store', attributes: ['id', 'name', 'address'] },
//         { model: User, as: 'creator', attributes: ['id', 'name', 'email'] },
//         { model: User, as: 'approver', attributes: ['id', 'name', 'email'] },
//         {
//           model: PurchaseOrderItem,
//           as: 'items',
//           include: [
//             { model: Product, as: 'product', attributes: ['id', 'name', 'sku'] },
//             { model: ProductVariant, as: 'productVariant', attributes: ['id', 'name', 'sku'] }
//           ]
//         }
//       ],
//       order: [['createdAt', 'DESC']]
//     });

//     res.status(200).json({
//       success: true,
//       data: rows,
//       pagination: {
//         total: count,
//         page: parseInt(page),
//         limit: parseInt(limit),
//         pages: Math.ceil(count / limit)
//       }
//     });
//   } catch (error) {
//     console.error('Error fetching purchase orders:', error);
//     res.status(500).json({ success: false, message: 'Error fetching purchase orders', error: error.message });
//   }
// };

exports.getAllPurchaseOrders = async (req, res) => {
  try {
    const { page = 1, limit = 10, status, supplierId, storeId, search } = req.query;
    const offset = (page - 1) * limit;
    const where = {};

    if (status) where.status = status;
    if (supplierId) where.supplierId = supplierId;
    if (storeId) where.storeId = storeId;
    if (search) {
      where.orderNumber = { [Op.like]: `%${search}%` };
    }

    const { count, rows } = await PurchaseOrder.findAndCountAll({
      where,
      limit: parseInt(limit),
      offset: parseInt(offset),
      include: [
        { 
          model: Supplier, 
          as: 'supplier', 
          attributes: ['id', 'name', 'email', 'phone'],
          required: false // Make optional to avoid inner join issues
        },
        { 
          model: Store, 
          as: 'store', 
          attributes: ['id', 'name', 'address'],
          required: false
        },
        { 
          model: User, 
          as: 'creator', 
          attributes: ['id', 'firstName','lastName', 'email'],
          required: false
        },
        { 
          model: User, 
          as: 'approver', 
          attributes: ['id', 'firstName','lastName', 'email'],
          required: false
        },
        {
          model: PurchaseOrderItem,
          as: 'items',
          attributes: ['id', 'quantity', 'unitCost', 'totalCost', 'receivedQuantity'],
          required: false,
          include: [
            { 
              model: Product, 
              as: 'product', 
              attributes: ['id', 'name', 'sku'],
              required: false
            },
            { 
              model: ProductVariant, 
              as: 'productVariant', 
              attributes: ['id', 'name', 'sku'],
              required: false
            }
          ]
        }
      ],
      order: [['createdAt', 'DESC']],
      distinct: true
    });

    res.status(200).json({
      success: true,
      data: rows,
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    });
  } catch (error) {
    console.error('Error fetching purchase orders:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error fetching purchase orders', 
      error: error.message 
    });
  }
};

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

    const purchaseOrder = await PurchaseOrder.findByPk(id, {
      include: [
        { model: Supplier, as: 'supplier' },
        { model: Store, as: 'store' },
        {
          model: PurchaseOrderItem,
          as: 'items',
          include: [
            { model: Product, as: 'product' },
            { model: ProductVariant, as: 'productVariant' }
          ]
        }
      ]
    });

    if (!purchaseOrder) {
      return res.status(404).json({ 
        success: false, 
        message: 'Purchase order not found' 
      });
    }

    res.status(200).json({ 
      success: true, 
      data: purchaseOrder 
    });
  } catch (error) {
    console.error('Error fetching purchase order:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error fetching purchase order', 
      error: error.message 
    });
  }
};

// Get single purchase order by ID
// exports.getPurchaseOrderById = async (req, res) => {
//   try {
//     const { id } = req.params;

//     const purchaseOrder = await PurchaseOrder.findByPk(id, {
//       include: [
//         { model: Supplier, as: 'supplier' },
//         { model: Store, as: 'store' },
//         { model: User, as: 'creator', attributes: ['id', 'name', 'email'] },
//         { model: User, as: 'approver', attributes: ['id', 'name', 'email'] },
//         {
//           model: PurchaseOrderItem,
//           as: 'items',
//           include: [
//             { model: Product, as: 'product' },
//             { model: ProductVariant, as: 'productVariant' }
//           ]
//         }
//       ]
//     });

//     if (!purchaseOrder) {
//       return res.status(404).json({ success: false, message: 'Purchase order not found' });
//     }

//     res.status(200).json({ success: true, data: purchaseOrder });
//   } catch (error) {
//     console.error('Error fetching purchase order:', error);
//     res.status(500).json({ success: false, message: 'Error fetching purchase order', error: error.message });
//   }
// };

// Create new purchase order
exports.createPurchaseOrder = async (req, res) => {
  const transaction = await require('../models').sequelize.transaction();
  
  try {
    const {
      supplierId,
      storeId,
      orderDate,
      expectedDeliveryDate,
      status = 'draft',
      shippingCost = 0,
      notes,
      items
    } = req.body;

    // Validate required fields
    if (!supplierId || !storeId || !items || items.length === 0) {
      await transaction.rollback();
      return res.status(400).json({ 
        success: false, 
        message: 'Supplier, store, and at least one item are required' 
      });
    }

    // Generate order number
    const orderNumber = `PO-${Date.now()}-${Math.random().toString(36).substr(2, 9).toUpperCase()}`;

    // Calculate totals
    let subtotal = 0;
    items.forEach(item => {
      const itemTotal = parseFloat(item.quantity) * parseFloat(item.unitCost);
      item.totalCost = itemTotal;
      subtotal += itemTotal;
    });

    const taxAmount = subtotal * 0.16; // 16% VAT
    const totalAmount = subtotal + taxAmount + parseFloat(shippingCost);

    // Create purchase order
    const purchaseOrder = await PurchaseOrder.create({
      supplierId,
      storeId,
      orderNumber,
      orderDate: orderDate || new Date(),
      expectedDeliveryDate,
      status,
      subtotal: subtotal.toFixed(2),
      taxAmount: taxAmount.toFixed(2),
      shippingCost: parseFloat(shippingCost).toFixed(2),
      totalAmount: totalAmount.toFixed(2),
      notes,
      createdBy: req.user.id
    }, { transaction });

    // Create purchase order items
    const orderItems = items.map(item => ({
      purchaseOrderId: purchaseOrder.id,
      productId: item.productId,
      productVariantId: item.productVariantId || null,
      quantity: item.quantity,
      unitCost: parseFloat(item.unitCost).toFixed(2),
      totalCost: item.totalCost.toFixed(2),
      receivedQuantity: 0
    }));

    await PurchaseOrderItem.bulkCreate(orderItems, { transaction });

    await transaction.commit();

    // Fetch the created order with associations
    const createdOrder = await PurchaseOrder.findByPk(purchaseOrder.id, {
      include: [
        { model: Supplier, as: 'supplier' },
        { model: Store, as: 'store' },
        { model: User, as: 'creator', attributes: ['id', 'firstName', 'email'] },
        {
          model: PurchaseOrderItem,
          as: 'items',
          include: [
            { model: Product, as: 'product' },
            { model: ProductVariant, as: 'productVariant' }
          ]
        }
      ]
    });

    res.status(201).json({ 
      success: true, 
      message: 'Purchase order created successfully', 
      data: createdOrder 
    });
  } catch (error) {
    // Only rollback if transaction hasn't been finished
    if (!transaction.finished) {
      await transaction.rollback();
    }
    console.error('Error creating purchase order:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error creating purchase order', 
      error: error.message 
    });
  }
};
// Update purchase order
exports.updatePurchaseOrder = async (req, res) => {
  const transaction = await require('../models').sequelize.transaction();
  
  try {
    const { id } = req.params;
    const {
      supplierId,
      storeId,
      orderDate,
      expectedDeliveryDate,
      status,
      shippingCost,
      notes,
      items
    } = req.body;

    const purchaseOrder = await PurchaseOrder.findByPk(id);

    if (!purchaseOrder) {
      await transaction.rollback();
      return res.status(404).json({ success: false, message: 'Purchase order not found' });
    }

    // Don't allow editing of approved or received orders
    if (['approved', 'received', 'cancelled'].includes(purchaseOrder.status)) {
      await transaction.rollback();
      return res.status(400).json({ 
        success: false, 
        message: `Cannot edit purchase order with status: ${purchaseOrder.status}` 
      });
    }

    // Update purchase order details
    if (items && items.length > 0) {
      // Recalculate totals
      let subtotal = 0;
      items.forEach(item => {
        const itemTotal = parseFloat(item.quantity) * parseFloat(item.unitCost);
        item.totalCost = itemTotal;
        subtotal += itemTotal;
      });

      const taxAmount = subtotal * 0.16;
      const totalAmount = subtotal + taxAmount + parseFloat(shippingCost || purchaseOrder.shippingCost);

      await purchaseOrder.update({
        supplierId: supplierId || purchaseOrder.supplierId,
        storeId: storeId || purchaseOrder.storeId,
        orderDate: orderDate || purchaseOrder.orderDate,
        expectedDeliveryDate: expectedDeliveryDate || purchaseOrder.expectedDeliveryDate,
        status: status || purchaseOrder.status,
        subtotal: subtotal.toFixed(2),
        taxAmount: taxAmount.toFixed(2),
        shippingCost: parseFloat(shippingCost || purchaseOrder.shippingCost).toFixed(2),
        totalAmount: totalAmount.toFixed(2),
        notes: notes !== undefined ? notes : purchaseOrder.notes
      }, { transaction });

      // Delete existing items and create new ones
      await PurchaseOrderItem.destroy({ where: { purchaseOrderId: id }, transaction });

      const orderItems = items.map(item => ({
        purchaseOrderId: id,
        productId: item.productId,
        productVariantId: item.productVariantId || null,
        quantity: item.quantity,
        unitCost: parseFloat(item.unitCost).toFixed(2),
        totalCost: item.totalCost.toFixed(2),
        receivedQuantity: item.receivedQuantity || 0
      }));

      await PurchaseOrderItem.bulkCreate(orderItems, { transaction });
    } else {
      await purchaseOrder.update({
        supplierId: supplierId || purchaseOrder.supplierId,
        storeId: storeId || purchaseOrder.storeId,
        orderDate: orderDate || purchaseOrder.orderDate,
        expectedDeliveryDate: expectedDeliveryDate || purchaseOrder.expectedDeliveryDate,
        status: status || purchaseOrder.status,
        shippingCost: shippingCost !== undefined ? parseFloat(shippingCost).toFixed(2) : purchaseOrder.shippingCost,
        notes: notes !== undefined ? notes : purchaseOrder.notes
      }, { transaction });
    }

    await transaction.commit();

    // Fetch updated order
    const updatedOrder = await PurchaseOrder.findByPk(id, {
      include: [
        { model: Supplier, as: 'supplier' },
        { model: Store, as: 'store' },
        { model: User, as: 'creator', attributes: ['id', 'name', 'email'] },
        { model: User, as: 'approver', attributes: ['id', 'name', 'email'] },
        {
          model: PurchaseOrderItem,
          as: 'items',
          include: [
            { model: Product, as: 'product' },
            { model: ProductVariant, as: 'productVariant' }
          ]
        }
      ]
    });

    res.status(200).json({ 
      success: true, 
      message: 'Purchase order updated successfully', 
      data: updatedOrder 
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error updating purchase order:', error);
    res.status(500).json({ success: false, message: 'Error updating purchase order', error: error.message });
  }
};

// Approve purchase order
exports.approvePurchaseOrder = async (req, res) => {
  try {
    const { id } = req.params;

    const purchaseOrder = await PurchaseOrder.findByPk(id);

    if (!purchaseOrder) {
      return res.status(404).json({ success: false, message: 'Purchase order not found' });
    }

    if (purchaseOrder.status !== 'pending') {
      return res.status(400).json({ 
        success: false, 
        message: 'Only pending purchase orders can be approved' 
      });
    }

    await purchaseOrder.update({
      status: 'approved',
      approvedBy: req.user.id,
      approvedAt: new Date()
    });

    const approvedOrder = await PurchaseOrder.findByPk(id, {
      include: [
        { model: Supplier, as: 'supplier' },
        { model: Store, as: 'store' },
        { model: User, as: 'creator', attributes: ['id', 'firstName', 'email'] },
        { model: User, as: 'approver', attributes: ['id', 'firstName', 'email'] },
        {
          model: PurchaseOrderItem,
          as: 'items',
          include: [
            { model: Product, as: 'product' },
            { model: ProductVariant, as: 'productVariant' }
          ]
        }
      ]
    });

    res.status(200).json({ 
      success: true, 
      message: 'Purchase order approved successfully', 
      data: approvedOrder 
    });
  } catch (error) {
    console.error('Error approving purchase order:', error);
    res.status(500).json({ success: false, message: 'Error approving purchase order', error: error.message });
  }
};

// Cancel purchase order
exports.cancelPurchaseOrder = async (req, res) => {
  try {
    const { id } = req.params;

    const purchaseOrder = await PurchaseOrder.findByPk(id);

    if (!purchaseOrder) {
      return res.status(404).json({ success: false, message: 'Purchase order not found' });
    }

    if (['received', 'cancelled'].includes(purchaseOrder.status)) {
      return res.status(400).json({ 
        success: false, 
        message: `Cannot cancel purchase order with status: ${purchaseOrder.status}` 
      });
    }

    await purchaseOrder.update({ status: 'cancelled' });

    res.status(200).json({ 
      success: true, 
      message: 'Purchase order cancelled successfully', 
      data: purchaseOrder 
    });
  } catch (error) {
    console.error('Error cancelling purchase order:', error);
    res.status(500).json({ success: false, message: 'Error cancelling purchase order', error: error.message });
  }
};

// Receive items from purchase order
exports.receiveItems = async (req, res) => {
  const transaction = await require('../models').sequelize.transaction();
  
  try {
    const { id } = req.params;
    const { receivedItems } = req.body; // Array of { itemId, receivedQuantity }

    const purchaseOrder = await PurchaseOrder.findByPk(id, {
      include: [{ model: PurchaseOrderItem, as: 'items' }]
    });

    if (!purchaseOrder) {
      await transaction.rollback();
      return res.status(404).json({ success: false, message: 'Purchase order not found' });
    }

    if (purchaseOrder.status !== 'approved') {
      await transaction.rollback();
      return res.status(400).json({ 
        success: false, 
        message: 'Only approved purchase orders can receive items' 
      });
    }

    // Update received quantities for each item AND update product stock
    for (const receivedItem of receivedItems) {
      const item = await PurchaseOrderItem.findByPk(receivedItem.itemId, {
        include: [{ model: Product, as: 'product' }]
      });
      
      if (item && item.purchaseOrderId === parseInt(id)) {
        // Update received quantity in purchase order item
        await item.update({
          receivedQuantity: receivedItem.receivedQuantity
        }, { transaction });

        // **UPDATE PRODUCT STOCK** - This is the missing part!
        if (item.productId) {
          const product = await Product.findByPk(item.productId);
          if (product) {
            const currentStock = parseFloat(product.stock || 0);
            const receivedQty = parseFloat(receivedItem.receivedQuantity || 0);
            const newStock = currentStock + receivedQty;

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

            console.log(`Updated product ${product.id} stock: ${currentStock} + ${receivedQty} = ${newStock}`);
          }
        }

        // If you also have product variants, update variant stock
        if (item.productVariantId) {
          const variant = await ProductVariant.findByPk(item.productVariantId);
          if (variant) {
            const currentVariantStock = parseFloat(variant.stock || 0);
            const receivedQty = parseFloat(receivedItem.receivedQuantity || 0);
            const newVariantStock = currentVariantStock + receivedQty;

            await variant.update({
              stock: newVariantStock
            }, { transaction });

            console.log(`Updated variant ${variant.id} stock: ${currentVariantStock} + ${receivedQty} = ${newVariantStock}`);
          }
        }

        // **CREATE STOCK MOVEMENT RECORD** - For audit trail
        await StockMovement.create({
          productId: item.productId,
          productVariantId: item.productVariantId,
          storeId: purchaseOrder.storeId,
          movementType: 'purchase_received',
          quantity: receivedItem.receivedQuantity,
          referenceType: 'purchase_order',
          referenceId: id,
          notes: `Received from PO #${purchaseOrder.orderNumber}`,
          createdBy: req.user.id
        }, { transaction });
      }
    }

    // Check if all items are fully received
    const items = await PurchaseOrderItem.findAll({ 
      where: { purchaseOrderId: id }, 
      transaction 
    });
    const allReceived = items.every(item => item.receivedQuantity >= item.quantity);

    if (allReceived) {
      await purchaseOrder.update({ 
        status: 'received',
        receivedDate: new Date()
      }, { transaction });
    }

    await transaction.commit();

    const updatedOrder = await PurchaseOrder.findByPk(id, {
      include: [
        { model: Supplier, as: 'supplier' },
        { model: Store, as: 'store' },
        {
          model: PurchaseOrderItem,
          as: 'items',
          include: [
            { model: Product, as: 'product' },
            { model: ProductVariant, as: 'productVariant' }
          ]
        }
      ]
    });

    res.status(200).json({ 
      success: true, 
      message: 'Items received successfully and inventory updated', 
      data: updatedOrder 
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error receiving items:', error);
    res.status(500).json({ 
      success: false, 
      message: 'Error receiving items', 
      error: error.message 
    });
  }
};




// Delete purchase order
exports.deletePurchaseOrder = async (req, res) => {
  const transaction = await require('../models').sequelize.transaction();
  
  try {
    const { id } = req.params;

    const purchaseOrder = await PurchaseOrder.findByPk(id);

    if (!purchaseOrder) {
      await transaction.rollback();
      return res.status(404).json({ success: false, message: 'Purchase order not found' });
    }

    // Only allow deletion of draft or cancelled orders
    if (!['draft', 'cancelled'].includes(purchaseOrder.status)) {
      await transaction.rollback();
      return res.status(400).json({ 
        success: false, 
        message: 'Only draft or cancelled purchase orders can be deleted' 
      });
    }

    await PurchaseOrderItem.destroy({ where: { purchaseOrderId: id }, transaction });
    await purchaseOrder.destroy({ transaction });

    await transaction.commit();

    res.status(200).json({ 
      success: true, 
      message: 'Purchase order deleted successfully' 
    });
  } catch (error) {
    await transaction.rollback();
    console.error('Error deleting purchase order:', error);
    res.status(500).json({ success: false, message: 'Error deleting purchase order', error: error.message });
  }
};

// Get suppliers list
exports.getSuppliers = async (req, res) => {
  try {
    const suppliers = await Supplier.findAll({
      attributes: ['id', 'name', 'email', 'phone', 'address'],
      order: [['name', 'ASC']]
    });

    res.status(200).json({ success: true, data: suppliers });
  } catch (error) {
    console.error('Error fetching suppliers:', error);
    res.status(500).json({ success: false, message: 'Error fetching suppliers', error: error.message });
  }
};

// Get stores list
exports.getStores = async (req, res) => {
  try {
    const stores = await Store.findAll({
      attributes: ['id', 'name', 'address', 'phone'],
      order: [['name', 'ASC']]
    });

    res.status(200).json({ success: true, data: stores });
  } catch (error) {
    console.error('Error fetching stores:', error);
    res.status(500).json({ success: false, message: 'Error fetching stores', error: error.message });
  }
};

// Get products with variants
exports.getProducts = async (req, res) => {
  try {
    const { search } = req.query;
    const where = {};

    if (search) {
      where[Op.or] = [
        { name: { [Op.like]: `%${search}%` } },
        { sku: { [Op.like]: `%${search}%` } }
      ];
    }

    const products = await Product.findAll({
      where,
      include: [
        { 
          model: ProductVariant, 
          as: 'variants',
          attributes: ['id', 'name', 'sku', 'price']
        }
      ],
      attributes: ['id', 'name', 'sku', 'description'],
      order: [['name', 'ASC']]
    });

    res.status(200).json({ success: true, data: products });
  } catch (error) {
    console.error('Error fetching products:', error);
    res.status(500).json({ success: false, message: 'Error fetching products', error: error.message });
  }
};


// In your main app.js or server.js, add:
// const purchaseOrderRoutes = require('./routes/purchaseOrderRoutes');
// app.use('/api/purchase-orders', purchaseOrderRoutes);