const {
  Order,
  OrderItem,
  OrderStatus,
  User,
  Product,
  ProductVariant,
  Address,
  Payment,
  Shipment,
  Coupon,
  CouponUsage,
  Inventory,
  Notification,
  sequelize
} = require('../models');
const { Op } = require('sequelize');

// Create a new order
exports.createOrder = async (req, res) => {
  const transaction = await sequelize.transaction();
  
  try {
    const {
      userId,
      items, // [{ productId, productVariantId, quantity, price }]
      shippingAddressId,
      billingAddressId,
      paymentMethod,
      couponCode,
      shippingCost,
      notes
    } = req.body;

    // Validate user
    const user = await User.findByPk(userId);
    if (!user) {
      await transaction.rollback();
      return res.status(404).json({ 
        success: false, 
        message: 'User not found' 
      });
    }

    // Validate addresses
    const shippingAddress = await Address.findOne({
      where: { id: shippingAddressId, userId }
    });
    
    const billingAddress = await Address.findOne({
      where: { id: billingAddressId, userId }
    });

    if (!shippingAddress || !billingAddress) {
      await transaction.rollback();
      return res.status(404).json({ 
        success: false, 
        message: 'Invalid address' 
      });
    }

    // Validate and calculate items
    let subtotal = 0;
    let totalTax = 0;
    const validatedItems = [];

    for (const item of items) {
      let product, variant, price, stock;

      if (item.productVariantId) {
        variant = await ProductVariant.findByPk(item.productVariantId, {
          include: [{ model: Product }]
        });
        
        if (!variant) {
          await transaction.rollback();
          return res.status(404).json({ 
            success: false, 
            message: `Product variant ${item.productVariantId} not found` 
          });
        }

        product = variant.Product;
        price = variant.price;
        stock = variant.stock;
      } else {
        product = await Product.findByPk(item.productId);
        
        if (!product) {
          await transaction.rollback();
          return res.status(404).json({ 
            success: false, 
            message: `Product ${item.productId} not found` 
          });
        }

        price = product.price;
        stock = product.stock;
      }

      // Check stock availability
      if (stock < item.quantity) {
        await transaction.rollback();
        return res.status(400).json({ 
          success: false, 
          message: `Insufficient stock for ${product.name}. Available: ${stock}` 
        });
      }

      // Calculate item totals
      const itemSubtotal = price * item.quantity;
      const itemTax = itemSubtotal * (product.taxRate || 0) / 100;
      
      validatedItems.push({
        productId: item.productId,
        productVariantId: item.productVariantId || null,
        quantity: item.quantity,
        price: price,
        discount: 0, // Will be updated if coupon applies
        tax: itemTax,
        subtotal: itemSubtotal
      });

      subtotal += itemSubtotal;
      totalTax += itemTax;
    }

    // Apply coupon if provided
    let discount = 0;
    let couponId = null;

    if (couponCode) {
      const coupon = await Coupon.findOne({
        where: {
          code: couponCode,
          isActive: true,
          startDate: { [Op.lte]: new Date() },
          endDate: { [Op.gte]: new Date() },
          [Op.or]: [
            { usageLimit: null },
            { usageCount: { [Op.lt]: sequelize.col('usageLimit') } }
          ]
        }
      });

      if (!coupon) {
        await transaction.rollback();
        return res.status(400).json({ 
          success: false, 
          message: 'Invalid or expired coupon code' 
        });
      }

      // Check minimum purchase amount
      if (coupon.minPurchaseAmount && subtotal < coupon.minPurchaseAmount) {
        await transaction.rollback();
        return res.status(400).json({ 
          success: false, 
          message: `Minimum purchase amount of ${coupon.minPurchaseAmount} required` 
        });
      }

      // Calculate discount
      if (coupon.discountType === 'percentage') {
        discount = (subtotal * coupon.discountValue) / 100;
        if (coupon.maxDiscountAmount && discount > coupon.maxDiscountAmount) {
          discount = coupon.maxDiscountAmount;
        }
      } else if (coupon.discountType === 'fixed') {
        discount = coupon.discountValue;
      }

      couponId = coupon.id;
    }

    // Calculate total
    const total = subtotal + totalTax + (shippingCost || 0) - discount;

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

    // Create order
    const order = await Order.create({
      userId,
      orderNumber,
      status: 'pending',
      subtotal,
      tax: totalTax,
      shippingCost: shippingCost || 0,
      discount,
      total,
      paymentMethod,
      paymentStatus: 'pending',
      shippingAddressId,
      billingAddressId,
      notes
    }, { transaction });

    // Create order items and update inventory
    for (const item of validatedItems) {
      await OrderItem.create({
        orderId: order.id,
        ...item
      }, { transaction });

      // Update inventory
      if (item.productVariantId) {
        await ProductVariant.decrement('stock', {
          by: item.quantity,
          where: { id: item.productVariantId },
          transaction
        });
      } else {
        await Product.decrement('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction
        });
      }

      // Update inventory table
      await Inventory.create({
        productId: item.productId,
        productVariantId: item.productVariantId,
        quantity: -item.quantity,
        type: 'sale',
        referenceId: order.id,
        referenceType: 'order',
        notes: `Order ${orderNumber}`
      }, { transaction });
    }

    // Create order status record
    await OrderStatus.create({
      orderId: order.id,
      status: 'pending',
      description: 'Order placed successfully',
      updatedBy: userId
    }, { transaction });

    // Record coupon usage
    if (couponId) {
      await CouponUsage.create({
        couponId,
        userId,
        orderId: order.id,
        discountAmount: discount,
        usedAt: new Date()
      }, { transaction });

      await Coupon.increment('usageCount', {
        where: { id: couponId },
        transaction
      });
    }

    // Create notification
    await Notification.create({
      userId,
      type: 'order_placed',
      title: 'Order Placed Successfully',
      message: `Your order ${orderNumber} has been placed successfully`,
      link: `/orders/${order.id}`,
      isRead: false
    }, { transaction });

    await transaction.commit();

    // Fetch complete order details
    const completeOrder = await Order.findByPk(order.id, {
      include: [
        {
          model: OrderItem,
          include: [
            { model: Product },
            { model: ProductVariant }
          ]
        },
        { model: Address, as: 'ShippingAddress' },
        { model: Address, as: 'BillingAddress' }
      ]
    });

    res.status(201).json({
      success: true,
      message: 'Order created successfully',
      data: completeOrder
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Create order error:', error);
    res.status(500).json({
      success: false,
      message: 'Error creating order',
      error: error.message
    });
  }
};

// Get all orders (Admin - with pagination, filtering, sorting)
exports.getAllOrders = async (req, res) => {
  try {
    const {
      page = 1,
      limit = 20,
      status,
      paymentStatus,
      startDate,
      endDate,
      search,
      sortBy = 'createdAt',
      sortOrder = 'DESC'
    } = req.query;

    const offset = (page - 1) * limit;

    // Build where clause
    const whereClause = {};

    if (status) {
      whereClause.status = status;
    }

    if (paymentStatus) {
      whereClause.paymentStatus = paymentStatus;
    }

    if (startDate || endDate) {
      whereClause.createdAt = {};
      if (startDate) {
        whereClause.createdAt[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereClause.createdAt[Op.lte] = new Date(endDate);
      }
    }

    if (search) {
      whereClause[Op.or] = [
        { orderNumber: { [Op.like]: `%${search}%` } },
        { '$User.firstName$': { [Op.like]: `%${search}%` } },
        { '$User.lastName$': { [Op.like]: `%${search}%` } },
        { '$User.email$': { [Op.like]: `%${search}%` } }
      ];
    }

    const { count, rows: orders } = await Order.findAndCountAll({
      where: whereClause,
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email', 'phone']
        },
        {
          model: OrderItem,
          include: [
            { model: Product, attributes: ['id', 'name', 'sku'] },
            { model: ProductVariant, attributes: ['id', 'sku', 'attributes'] }
          ]
        }
      ],
      limit: parseInt(limit),
      offset: parseInt(offset),
      order: [[sortBy, sortOrder]],
      distinct: true
    });

    // Calculate statistics
    const stats = await Order.findOne({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('total')), 'totalRevenue'],
        [sequelize.fn('AVG', sequelize.col('total')), 'averageOrderValue'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'totalOrders']
      ],
      where: whereClause,
      raw: true
    });

    res.status(200).json({
      success: true,
      data: orders,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(count / limit),
        totalItems: count,
        itemsPerPage: parseInt(limit)
      },
      stats: {
        totalRevenue: parseFloat(stats.totalRevenue || 0),
        averageOrderValue: parseFloat(stats.averageOrderValue || 0),
        totalOrders: parseInt(stats.totalOrders || 0)
      }
    });

  } catch (error) {
    console.error('Get all orders error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching orders',
      error: error.message
    });
  }
};

// Get customer orders
exports.getCustomerOrders = async (req, res) => {
  try {
    const { userId } = req.params;
    const {
      page = 1,
      limit = 10,
      status,
      sortBy = 'createdAt',
      sortOrder = 'DESC'
    } = req.query;

    const offset = (page - 1) * limit;

    const whereClause = { userId };

    if (status) {
      whereClause.status = status;
    }

    const { count, rows: orders } = await Order.findAndCountAll({
      where: whereClause,
      include: [
        {
          model: OrderItem,
          include: [
            { 
              model: Product, 
              attributes: ['id', 'name', 'sku', 'image'] 
            },
            { 
              model: ProductVariant, 
              attributes: ['id', 'sku', 'attributes', 'image'] 
            }
          ]
        },
        {
          model: OrderStatus,
          limit: 1,
          order: [['createdAt', 'DESC']]
        },
        {
          model: Shipment
        }
      ],
      limit: parseInt(limit),
      offset: parseInt(offset),
      order: [[sortBy, sortOrder]]
    });

    res.status(200).json({
      success: true,
      data: orders,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(count / limit),
        totalItems: count,
        itemsPerPage: parseInt(limit)
      }
    });

  } catch (error) {
    console.error('Get customer orders error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching customer orders',
      error: error.message
    });
  }
};

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

    const order = await Order.findByPk(orderId, {
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email', 'phone']
        },
        {
          model: OrderItem,
          include: [
            { model: Product },
            { model: ProductVariant }
          ]
        },
        {
          model: Address,
          as: 'ShippingAddress'
        },
        {
          model: Address,
          as: 'BillingAddress'
        },
        {
          model: OrderStatus,
          order: [['createdAt', 'DESC']]
        },
        {
          model: Shipment
        },
        {
          model: Payment
        }
      ]
    });

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

    res.status(200).json({
      success: true,
      data: order
    });

  } catch (error) {
    console.error('Get order by ID error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching order',
      error: error.message
    });
  }
};

// Update order status
exports.updateOrderStatus = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderId } = req.params;
    const { status, description, updatedBy } = req.body;

    const validStatuses = [
      'pending',
      'confirmed',
      'processing',
      'shipped',
      'delivered',
      'cancelled',
      'refunded',
      'failed'
    ];

    if (!validStatuses.includes(status)) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid order status'
      });
    }

    const order = await Order.findByPk(orderId, {
      include: [{ model: User }]
    });

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

    // Update order status
    await order.update({ status }, { transaction });

    // Create status history record
    await OrderStatus.create({
      orderId,
      status,
      description: description || `Order status updated to ${status}`,
      updatedBy
    }, { transaction });

    // If order is cancelled, restore inventory
    if (status === 'cancelled' && order.status !== 'cancelled') {
      const orderItems = await OrderItem.findAll({
        where: { orderId }
      });

      for (const item of orderItems) {
        if (item.productVariantId) {
          await ProductVariant.increment('stock', {
            by: item.quantity,
            where: { id: item.productVariantId },
            transaction
          });
        } else {
          await Product.increment('stock', {
            by: item.quantity,
            where: { id: item.productId },
            transaction
          });
        }

        await Inventory.create({
          productId: item.productId,
          productVariantId: item.productVariantId,
          quantity: item.quantity,
          type: 'return',
          referenceId: orderId,
          referenceType: 'order_cancellation',
          notes: `Order ${order.orderNumber} cancelled`
        }, { transaction });
      }
    }

    // Create notification for user
    await Notification.create({
      userId: order.userId,
      type: 'order_status_update',
      title: `Order ${status}`,
      message: `Your order ${order.orderNumber} has been ${status}`,
      link: `/orders/${orderId}`,
      isRead: false
    }, { transaction });

    await transaction.commit();

    res.status(200).json({
      success: true,
      message: 'Order status updated successfully',
      data: { orderId, status }
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Update order status error:', error);
    res.status(500).json({
      success: false,
      message: 'Error updating order status',
      error: error.message
    });
  }
};

// Cancel order
exports.cancelOrder = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderId } = req.params;
    const { reason, cancelledBy } = req.body;

    const order = await Order.findByPk(orderId, {
      include: [{ model: OrderItem }]
    });

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

    // Check if order can be cancelled
    const nonCancellableStatuses = ['delivered', 'cancelled', 'refunded'];
    if (nonCancellableStatuses.includes(order.status)) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: `Cannot cancel order with status: ${order.status}`
      });
    }

    // Update order status
    await order.update({
      status: 'cancelled',
      cancelReason: reason
    }, { transaction });

    // Restore inventory
    for (const item of order.OrderItems) {
      if (item.productVariantId) {
        await ProductVariant.increment('stock', {
          by: item.quantity,
          where: { id: item.productVariantId },
          transaction
        });
      } else {
        await Product.increment('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction
        });
      }

      await Inventory.create({
        productId: item.productId,
        productVariantId: item.productVariantId,
        quantity: item.quantity,
        type: 'return',
        referenceId: orderId,
        referenceType: 'order_cancellation',
        notes: `Order ${order.orderNumber} cancelled: ${reason}`
      }, { transaction });
    }

    // Create status history
    await OrderStatus.create({
      orderId,
      status: 'cancelled',
      description: `Order cancelled: ${reason}`,
      updatedBy: cancelledBy
    }, { transaction });

    // Create notification
    await Notification.create({
      userId: order.userId,
      type: 'order_cancelled',
      title: 'Order Cancelled',
      message: `Your order ${order.orderNumber} has been cancelled`,
      link: `/orders/${orderId}`,
      isRead: false
    }, { transaction });

    await transaction.commit();

    res.status(200).json({
      success: true,
      message: 'Order cancelled successfully',
      data: { orderId, status: 'cancelled' }
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Cancel order error:', error);
    res.status(500).json({
      success: false,
      message: 'Error cancelling order',
      error: error.message
    });
  }
};

// Get order statistics
exports.getOrderStats = async (req, res) => {
  try {
    const {
      startDate,
      endDate,
      groupBy = 'day' // day, week, month, year
    } = req.query;

    const whereClause = {};

    if (startDate || endDate) {
      whereClause.createdAt = {};
      if (startDate) {
        whereClause.createdAt[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereClause.createdAt[Op.lte] = new Date(endDate);
      }
    }

    // Overall statistics
    const overallStats = await Order.findOne({
      attributes: [
        [sequelize.fn('COUNT', sequelize.col('id')), 'totalOrders'],
        [sequelize.fn('SUM', sequelize.col('total')), 'totalRevenue'],
        [sequelize.fn('AVG', sequelize.col('total')), 'averageOrderValue'],
        [sequelize.fn('SUM', sequelize.col('subtotal')), 'totalSubtotal'],
        [sequelize.fn('SUM', sequelize.col('tax')), 'totalTax'],
        [sequelize.fn('SUM', sequelize.col('shippingCost')), 'totalShipping'],
        [sequelize.fn('SUM', sequelize.col('discount')), 'totalDiscount']
      ],
      where: whereClause,
      raw: true
    });

    // Status breakdown
    const statusBreakdown = await Order.findAll({
      attributes: [
        'status',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('total')), 'revenue']
      ],
      where: whereClause,
      group: ['status'],
      raw: true
    });

    // Payment status breakdown
    const paymentStatusBreakdown = await Order.findAll({
      attributes: [
        'paymentStatus',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('total')), 'amount']
      ],
      where: whereClause,
      group: ['paymentStatus'],
      raw: true
    });

    // Payment method breakdown
    const paymentMethodBreakdown = await Order.findAll({
      attributes: [
        'paymentMethod',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('total')), 'amount']
      ],
      where: whereClause,
      group: ['paymentMethod'],
      raw: true
    });

    // Time-based grouping
    let dateFormat;
    switch (groupBy) {
      case 'hour':
        dateFormat = '%Y-%m-%d %H:00:00';
        break;
      case 'day':
        dateFormat = '%Y-%m-%d';
        break;
      case 'week':
        dateFormat = '%Y-%U';
        break;
      case 'month':
        dateFormat = '%Y-%m';
        break;
      case 'year':
        dateFormat = '%Y';
        break;
      default:
        dateFormat = '%Y-%m-%d';
    }

    const timeSeries = await Order.findAll({
      attributes: [
        [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat), 'period'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
        [sequelize.fn('SUM', sequelize.col('total')), 'revenue'],
        [sequelize.fn('AVG', sequelize.col('total')), 'averageOrderValue']
      ],
      where: whereClause,
      group: [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat)],
      order: [[sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat), 'ASC']],
      raw: true
    });

    // Top products
    const topProducts = await OrderItem.findAll({
      attributes: [
        'productId',
        [sequelize.fn('SUM', sequelize.col('quantity')), 'totalQuantity'],
        [sequelize.fn('SUM', sequelize.col('subtotal')), 'totalRevenue'],
        [sequelize.fn('COUNT', sequelize.col('OrderItem.id')), 'orderCount']
      ],
      include: [
        {
          model: Order,
          attributes: [],
          where: whereClause
        },
        {
          model: Product,
          attributes: ['id', 'name', 'sku', 'image']
        }
      ],
      group: ['productId'],
      order: [[sequelize.fn('SUM', sequelize.col('quantity')), 'DESC']],
      limit: 10,
      subQuery: false
    });

    // Top customers
    const topCustomers = await Order.findAll({
      attributes: [
        'userId',
        [sequelize.fn('COUNT', sequelize.col('Order.id')), 'orderCount'],
        [sequelize.fn('SUM', sequelize.col('total')), 'totalSpent'],
        [sequelize.fn('AVG', sequelize.col('total')), 'averageOrderValue']
      ],
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email']
        }
      ],
      where: whereClause,
      group: ['userId'],
      order: [[sequelize.fn('SUM', sequelize.col('total')), 'DESC']],
      limit: 10,
      subQuery: false
    });

    res.status(200).json({
      success: true,
      data: {
        overall: {
          totalOrders: parseInt(overallStats.totalOrders || 0),
          totalRevenue: parseFloat(overallStats.totalRevenue || 0),
          averageOrderValue: parseFloat(overallStats.averageOrderValue || 0),
          totalSubtotal: parseFloat(overallStats.totalSubtotal || 0),
          totalTax: parseFloat(overallStats.totalTax || 0),
          totalShipping: parseFloat(overallStats.totalShipping || 0),
          totalDiscount: parseFloat(overallStats.totalDiscount || 0)
        },
        statusBreakdown,
        paymentStatusBreakdown,
        paymentMethodBreakdown,
        timeSeries,
        topProducts,
        topCustomers
      }
    });

  } catch (error) {
    console.error('Get order stats error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching order statistics',
      error: error.message
    });
  }
};

// Update payment status
exports.updatePaymentStatus = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderId } = req.params;
    const { paymentStatus, transactionId, paymentDetails } = req.body;

    const validPaymentStatuses = ['pending', 'paid', 'failed', 'refunded', 'partially_refunded'];

    if (!validPaymentStatuses.includes(paymentStatus)) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid payment status'
      });
    }

    const order = await Order.findByPk(orderId);

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

    // Update order payment status
    await order.update({ paymentStatus }, { transaction });

    // Create or update payment record
    await Payment.upsert({
      orderId,
      transactionId,
      amount: order.total,
      status: paymentStatus,
      method: order.paymentMethod,
      details: paymentDetails,
      paidAt: paymentStatus === 'paid' ? new Date() : null
    }, { transaction });

    // If payment is successful, update order status
    if (paymentStatus === 'paid' && order.status === 'pending') {
      await order.update({ status: 'confirmed' }, { transaction });

      await OrderStatus.create({
        orderId,
        status: 'confirmed',
        description: 'Payment received, order confirmed'
      }, { transaction });
    }

    // Create notification
    await Notification.create({
      userId: order.userId,
      type: 'payment_status_update',
      title: `Payment ${paymentStatus}`,
      message: `Payment for order ${order.orderNumber} is ${paymentStatus}`,
      link: `/orders/${orderId}`,
      isRead: false
    }, { transaction });

    await transaction.commit();

    res.status(200).json({
      success: true,
      message: 'Payment status updated successfully',
      data: { orderId, paymentStatus }
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Update payment status error:', error);
    res.status(500).json({
      success: false,
      message: 'Error updating payment status',
      error: error.message
    });
  }
};

// Bulk update orders
exports.bulkUpdateOrders = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderIds, updates } = req.body;

    if (!Array.isArray(orderIds) || orderIds.length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Order IDs must be a non-empty array'
      });
    }

    const allowedUpdates = ['status', 'paymentStatus'];
    const updateFields = {};

    for (const [key, value] of Object.entries(updates)) {
      if (allowedUpdates.includes(key)) {
        updateFields[key] = value;
      }
    }

    if (Object.keys(updateFields).length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'No valid update fields provided'
      });
    }

    // Update orders
    await Order.update(updateFields, {
      where: { id: { [Op.in]: orderIds } },
      transaction
    });

    // Create status history for each order if status was updated
    if (updateFields.status) {
      const statusRecords = orderIds
      await OrderStatus.bulkCreate(statusRecords, { transaction });
}

await transaction.commit();

res.status(200).json({
  success: true,
  message: `${orderIds.length} orders updated successfully`,
  data: { updatedCount: orderIds.length }
});
} catch (error) {
await transaction.rollback();
console.error('Bulk update orders error:', error);
res.status(500).json({
success: false,
message: 'Error updating orders',
error: error.message
});
}
};
// Export orders to CSV
exports.exportOrders = async (req, res) => {
try {
const { startDate, endDate, status, paymentStatus } = req.query;
const whereClause = {};

if (status) {
  whereClause.status = status;
}

if (paymentStatus) {
  whereClause.paymentStatus = paymentStatus;
}

if (startDate || endDate) {
  whereClause.createdAt = {};
  if (startDate) {
    whereClause.createdAt[Op.gte] = new Date(startDate);
  }
  if (endDate) {
    whereClause.createdAt[Op.lte] = new Date(endDate);
  }
}

const orders = await Order.findAll({
  where: whereClause,
  include: [
    {
      model: User,
      attributes: ['firstName', 'lastName', 'email']
    },
    {
      model: OrderItem,
      include: [{ model: Product, attributes: ['name', 'sku'] }]
    }
  ],
  order: [['createdAt', 'DESC']]
});

// Format data for CSV
const csvData = orders.map(order => ({
  'Order Number': order.orderNumber,
  'Customer Name': `${order.User.firstName} ${order.User.lastName}`,
  'Customer Email': order.User.email,
  'Order Date': order.createdAt,
  'Status': order.status,
  'Payment Status': order.paymentStatus,
  'Payment Method': order.paymentMethod,
  'Order Number': order.orderNumber,
      'Customer Name': `${order.User.firstName} ${order.User.lastName}`,
      'Customer Email': order.User.email,
      'Order Date': order.createdAt,
      'Status': order.status,
      'Payment Status': order.paymentStatus,
      'Payment Method': order.paymentMethod,
      'Items': order.OrderItems.map(item => item.Product.name).join(', '),
      'Subtotal': order.subtotal,
      'Tax': order.tax,
      'Shipping': order.shippingCost,
      'Discount': order.discount,
      'Total': order.total
    }));

    // Convert to CSV format
    const headers = Object.keys(csvData[0] || {}).join(',');
    const rows = csvData.map(row => Object.values(row).join(','));
    const csv = [headers, ...rows].join('\n');

    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', `attachment; filename=orders-${Date.now()}.csv`);
    res.status(200).send(csv);

  } catch (error) {
    console.error('Export orders error:', error);
    res.status(500).json({
      success: false,
      message: 'Error exporting orders',
      error: error.message
    });
  }
};

// Get order invoice
exports.getOrderInvoice = async (req, res) => {
  try {
    const { orderId } = req.params;

    const order = await Order.findByPk(orderId, {
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email', 'phone']
        },
        {
          model: OrderItem,
          include: [
            { model: Product },
            { model: ProductVariant }
          ]
        },
        {
          model: Address,
          as: 'ShippingAddress'
        },
        {
          model: Address,
          as: 'BillingAddress'
        }
      ]
    });

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

    // Format invoice data
    const invoiceData = {
      orderNumber: order.orderNumber,
      orderDate: order.createdAt,
      status: order.status,
      customer: {
        name: `${order.User.firstName} ${order.User.lastName}`,
        email: order.User.email,
        phone: order.User.phone
      },
      shippingAddress: {
        street: order.ShippingAddress.street,
        city: order.ShippingAddress.city,
        state: order.ShippingAddress.state,
        postalCode: order.ShippingAddress.postalCode,
        country: order.ShippingAddress.country
      },
      billingAddress: {
        street: order.BillingAddress.street,
        city: order.BillingAddress.city,
        state: order.BillingAddress.state,
        postalCode: order.BillingAddress.postalCode,
        country: order.BillingAddress.country
      },
      items: order.OrderItems.map(item => ({
        name: item.Product.name,
        sku: item.Product.sku,
        variant: item.ProductVariant ? item.ProductVariant.attributes : null,
        quantity: item.quantity,
        price: item.price,
        discount: item.discount,
        tax: item.tax,
        subtotal: item.subtotal
      })),
      subtotal: order.subtotal,
      tax: order.tax,
      shippingCost: order.shippingCost,
      discount: order.discount,
      total: order.total,
      paymentMethod: order.paymentMethod,
      paymentStatus: order.paymentStatus
    };

    res.status(200).json({
      success: true,
      data: invoiceData
    });

  } catch (error) {
    console.error('Get order invoice error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching order invoice',
      error: error.message
    });
  }
};

// Track order
exports.trackOrder = async (req, res) => {
  try {
    const { orderNumber } = req.params;

    const order = await Order.findOne({
      where: { orderNumber },
      include: [
        {
          model: OrderStatus,
          order: [['createdAt', 'ASC']]
        },
        {
          model: Shipment
        }
      ]
    });

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

    const trackingInfo = {
      orderNumber: order.orderNumber,
      currentStatus: order.status,
      orderDate: order.createdAt,
      estimatedDelivery: order.Shipment?.estimatedDelivery || null,
      statusHistory: order.OrderStatuses.map(status => ({
        status: status.status,
        description: status.description,
        timestamp: status.createdAt
      })),
      shipment: order.Shipment ? {
        trackingNumber: order.Shipment.trackingNumber,
        carrier: order.Shipment.carrier,
        method: order.Shipment.method,
        status: order.Shipment.status,
        shippedAt: order.Shipment.shippedAt,
        deliveredAt: order.Shipment.deliveredAt
      } : null
    };

    res.status(200).json({
      success: true,
      data: trackingInfo
    });

  } catch (error) {
    console.error('Track order error:', error);
    res.status(500).json({
      success: false,
      message: 'Error tracking order',
      error: error.message
    });
  }
};

// Reorder (create new order from existing order)
exports.reorder = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderId } = req.params;
    const { userId, shippingAddressId, billingAddressId } = req.body;

    const originalOrder = await Order.findByPk(orderId, {
      include: [{ model: OrderItem }]
    });

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

    // Validate items availability and get current prices
    const items = [];
    for (const item of originalOrder.OrderItems) {
      let product, price, stock;

      if (item.productVariantId) {
        const variant = await ProductVariant.findByPk(item.productVariantId, {
          include: [{ model: Product }]
        });

        if (!variant || !variant.Product.isActive) {
          continue; // Skip unavailable products
        }

        product = variant.Product;
        price = variant.price;
        stock = variant.stock;
      } else {
        product = await Product.findByPk(item.productId);

        if (!product || !product.isActive) {
          continue; // Skip unavailable products
        }

        price = product.price;
        stock = product.stock;
      }

      if (stock >= item.quantity) {
        items.push({
          productId: item.productId,
          productVariantId: item.productVariantId,
          quantity: item.quantity,
          price: price
        });
      }
    }

    if (items.length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'None of the items from the original order are available'
      });
    }

    // Create new order using the createOrder logic
    const newOrderData = {
      userId: userId || originalOrder.userId,
      items,
      shippingAddressId: shippingAddressId || originalOrder.shippingAddressId,
      billingAddressId: billingAddressId || originalOrder.billingAddressId,
      paymentMethod: originalOrder.paymentMethod,
      shippingCost: originalOrder.shippingCost
    };

    // Call createOrder internally (you might want to extract the logic to a separate function)
    // For now, we'll create a simplified version

    let subtotal = 0;
    let totalTax = 0;

    for (const item of items) {
      const product = await Product.findByPk(item.productId);
      const itemSubtotal = item.price * item.quantity;
      const itemTax = itemSubtotal * (product.taxRate || 0) / 100;

      subtotal += itemSubtotal;
      totalTax += itemTax;
    }

    const total = subtotal + totalTax + (newOrderData.shippingCost || 0);
    const orderNumber = `ORD-${Date.now()}-${Math.random().toString(36).substr(2, 9).toUpperCase()}`;

    const newOrder = await Order.create({
      userId: newOrderData.userId,
      orderNumber,
      status: 'pending',
      subtotal,
      tax: totalTax,
      shippingCost: newOrderData.shippingCost || 0,
      discount: 0,
      total,
      paymentMethod: newOrderData.paymentMethod,
      paymentStatus: 'pending',
      shippingAddressId: newOrderData.shippingAddressId,
      billingAddressId: newOrderData.billingAddressId
    }, { transaction });

    for (const item of items) {
      const product = await Product.findByPk(item.productId);
      const itemSubtotal = item.price * item.quantity;
      const itemTax = itemSubtotal * (product.taxRate || 0) / 100;

      await OrderItem.create({
        orderId: newOrder.id,
        productId: item.productId,
        productVariantId: item.productVariantId,
        quantity: item.quantity,
        price: item.price,
        discount: 0,
        tax: itemTax,
        subtotal: itemSubtotal
      }, { transaction });

      // Update inventory
      if (item.productVariantId) {
        await ProductVariant.decrement('stock', {
          by: item.quantity,
          where: { id: item.productVariantId },
          transaction
        });
      } else {
        await Product.decrement('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction
        });
      }
    }

    await OrderStatus.create({
      orderId: newOrder.id,
      status: 'pending',
      description: 'Reorder created from previous order'
    }, { transaction });

    await transaction.commit();

    res.status(201).json({
      success: true,
      message: 'Order recreated successfully',
      data: newOrder
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Reorder error:', error);
    res.status(500).json({
      success: false,
      message: 'Error creating reorder',
      error: error.message
    });
  }
};

// Get recent orders
exports.getRecentOrders = async (req, res) => {
  try {
    const { limit = 10 } = req.query;

    const orders = await Order.findAll({
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: OrderItem,
          include: [
            { model: Product, attributes: ['id', 'name', 'image'] }
          ]
        }
      ],
      order: [['createdAt', 'DESC']],
      limit: parseInt(limit)
    });

    res.status(200).json({
      success: true,
      data: orders
    });

  } catch (error) {
    console.error('Get recent orders error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching recent orders',
      error: error.message
    });
  }
};

// Get pending orders
exports.getPendingOrders = async (req, res) => {
  try {
    const { page = 1, limit = 20 } = req.query;
    const offset = (page - 1) * limit;

    const { count, rows: orders } = await Order.findAndCountAll({
      where: {
        status: {
          [Op.in]: ['pending', 'confirmed', 'processing']
        }
      },
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: OrderItem,
          include: [{ model: Product }]
        }
      ],
      order: [['createdAt', 'ASC']],
      limit: parseInt(limit),
      offset: parseInt(offset)
    });

    res.status(200).json({
      success: true,
      data: orders,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(count / limit),
        totalItems: count,
        itemsPerPage: parseInt(limit)
      }
    });

  } catch (error) {
    console.error('Get pending orders error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching pending orders',
      error: error.message
    });
  }
};

// Search orders
exports.searchOrders = async (req, res) => {
  try {
    const {
      query,
      page = 1,
      limit = 20
    } = req.query;

    if (!query) {
      return res.status(400).json({
        success: false,
        message: 'Search query is required'
      });
    }

    const offset = (page - 1) * limit;

    const { count, rows: orders } = await Order.findAndCountAll({
      where: {
        [Op.or]: [
          { orderNumber: { [Op.like]: `%${query}%` } },
          { '$User.firstName$': { [Op.like]: `%${query}%` } },
          { '$User.lastName$': { [Op.like]: `%${query}%` } },
          { '$User.email$': { [Op.like]: `%${query}%` } },
          { '$User.phone$': { [Op.like]: `%${query}%` } }
        ]
      },
      include: [
        {
          model: User,
          attributes: ['id', 'firstName', 'lastName', 'email', 'phone']
        },
        {
          model: OrderItem,
          include: [{ model: Product }]
        }
      ],
      limit: parseInt(limit),
      offset: parseInt(offset),
      order: [['createdAt', 'DESC']],
      distinct: true
    });

    res.status(200).json({
      success: true,
      data: orders,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(count / limit),
        totalItems: count,
        itemsPerPage: parseInt(limit)
      }
    });

  } catch (error) {
    console.error('Search orders error:', error);
    res.status(500).json({
      success: false,
      message: 'Error searching orders',
      error: error.message
    });
  }
};

// Get order count by status
exports.getOrderCountByStatus = async (req, res) => {
  try {
    const counts = await Order.findAll({
      attributes: [
        'status',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count']
      ],
      group: ['status'],
      raw: true
    });

    const countMap = counts.reduce((acc, item) => {
      acc[item.status] = parseInt(item.count);
      return acc;
    }, {});

    res.status(200).json({
      success: true,
      data: countMap
    });

  } catch (error) {
    console.error('Get order count error:', error);
    res.status(500).json({
      success: false,
      message: 'Error fetching order counts',
      error: error.message
    });
  }
};

// Update order items
exports.updateOrderItems = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderId } = req.params;
    const { items } = req.body; // [{ orderItemId, quantity }]

    const order = await Order.findByPk(orderId);

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

    // Only allow updates for pending orders
    if (!['pending', 'confirmed'].includes(order.status)) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Cannot update items for orders in current status'
      });
    }

    let newSubtotal = 0;
    let newTax = 0;

    for (const item of items) {
      const orderItem = await OrderItem.findOne({
        where: { id: item.orderItemId, orderId },
        include: [{ model: Product }]
      });

      if (!orderItem) {
        await transaction.rollback();
        return res.status(404).json({
          success: false,
          message: `Order item ${item.orderItemId} not found`
        });
      }

      const quantityDiff = item.quantity - orderItem.quantity;

      // Update inventory
      if (orderItem.productVariantId) {
        const variant = await ProductVariant.findByPk(orderItem.productVariantId);
        
        if (variant.stock < quantityDiff) {
          await transaction.rollback();
          return res.status(400).json({
            success: false,
            message: `Insufficient stock for ${orderItem.Product.name}`
          });
        }

        await ProductVariant.decrement('stock', {
          by: quantityDiff,
          where: { id: orderItem.productVariantId },
          transaction
        });
      } else {
        if (orderItem.Product.stock < quantityDiff) {
          await transaction.rollback();
          return res.status(400).json({
            success: false,
            message: `Insufficient stock for ${orderItem.Product.name}`
          });
        }

        await Product.decrement('stock', {
          by: quantityDiff,
          where: { id: orderItem.productId },
          transaction
        });
      }

      // Update order item
      const newItemSubtotal = orderItem.price * item.quantity;
      const newItemTax = newItemSubtotal * (orderItem.Product.taxRate || 0) / 100;

      await orderItem.update({
        quantity: item.quantity,
        subtotal: newItemSubtotal,
        tax: newItemTax
      }, { transaction });

      newSubtotal += newItemSubtotal;
      newTax += newItemTax;
    }

    // Update order totals
    const newTotal = newSubtotal + newTax + order.shippingCost - order.discount;

    await order.update({
      subtotal: newSubtotal,
      tax: newTax,
      total: newTotal
    }, { transaction });

    await transaction.commit();

    res.status(200).json({
      success: true,
      message: 'Order items updated successfully',
      data: { orderId, newTotal }
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Update order items error:', error);
    res.status(500).json({
      success: false,
      message: 'Error updating order items',
      error: error.message
    });
  }
};

// Delete order (soft delete - only for pending/cancelled orders)
exports.deleteOrder = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { orderId } = req.params;

    const order = await Order.findByPk(orderId, {
      include: [{ model: OrderItem }]
    });

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

    // Only allow deletion of pending or cancelled orders
    if (!['pending', 'cancelled'].includes(order.status)) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Cannot delete order in current status'
      });
    }

    // Restore inventory if order was pending
    if (order.status === 'pending') {
      for (const item of order.OrderItems) {
        if (item.productVariantId) {
          await ProductVariant.increment('stock', {
            by: item.quantity,
            where: { id: item.productVariantId },
            transaction
          });
        } else {
          await Product.increment('stock', {
            by: item.quantity,
            where: { id: item.productId },
            transaction
          });
        }
      }
    }

    // Soft delete
    await order.destroy({ transaction });

    await transaction.commit();

    res.status(200).json({
      success: true,
      message: 'Order deleted successfully'
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Delete order error:', error);
    res.status(500).json({
      success: false,
      message: 'Error deleting order',
      error: error.message
    });
  }
};