// controllers/shoppingController.js
require('dotenv').config();
const { 
  Cart,
  CartItem,
  Orders,
  Order,
  OrderItem,
  Product,
  Brand,
  MpesaPayment,
  Customer,
  Shipment,
  sequelize 
} = require('../models');
const { getMpesaService } = require('../services/mpesaService');
const { Op } = require('sequelize');

const NodeCache = require('node-cache');

// Cache for frequently accessed data
const shoppingCache = new NodeCache({ 
  stdTTL: 300, // 5 minutes
  checkperiod: 60 
});

// ============================================================================
// UTILITY FUNCTIONS
// ============================================================================

/**
 * Format currency to KES
 */
const formatCurrency = (amount) => {
  return `KSh ${Number(amount).toLocaleString()}`;
};

/**
 * Generate unique order ID
 */
const generateOrderId = () => {
  const timestamp = Date.now();
  const random = Math.random().toString(36).substr(2, 9).toUpperCase();
  return `ORD-${timestamp}-${random}`;
};

/**
 * Generate unique tracking number
 */
const generateTrackingNumber = () => {
  const timestamp = Date.now();
  const random = Math.random().toString(36).substr(2, 6).toUpperCase();
  return `TRK-${timestamp}-${random}`;
};

/**
 * Normalize Kenyan phone number
 */
const normalizePhoneNumber = (phone) => {
  // Remove all non-numeric characters
  let cleaned = phone.replace(/\D/g, '');
  
  // Convert to 254 format
  if (cleaned.startsWith('0')) {
    cleaned = '254' + cleaned.slice(1);
  } else if (cleaned.startsWith('+254')) {
    cleaned = cleaned.slice(1);
  } else if (cleaned.startsWith('254')) {
    // Already in correct format
  } else if (cleaned.length === 9) {
    cleaned = '254' + cleaned;
  }
  
  return cleaned;
};






/**
 * Calculate order totals
 */
const calculateOrderTotals = (items, shippingCost = 0, discountAmount = 0) => {
  const subtotal = items.reduce((sum, item) => sum + item.subtotal, 0);
  
  // Apply discount
  const discountedSubtotal = subtotal - discountAmount;
  
  // 16% VAT on discounted amount
  const tax = Math.round(discountedSubtotal * 0.16);
  
  const total = discountedSubtotal + tax + shippingCost;

  return {
    subtotal: parseFloat(subtotal.toFixed(2)),
    discountAmount: parseFloat(discountAmount.toFixed(2)),
    shippingCost: parseFloat(shippingCost.toFixed(2)),
    tax: parseFloat(tax.toFixed(2)),
    total: parseFloat(total.toFixed(2))
  };
};

/**
 * Validate shipping details
 */
const validateShippingDetails = (shipping) => {
  const errors = [];

  if (!shipping.fullName?.trim()) {
    errors.push('Full name is required');
  }

  if (!shipping.phone?.trim()) {
    errors.push('Phone number is required');
  } else {
    try {
      const normalized = normalizePhoneNumber(shipping.phone);
      if (!/^254[17]\d{8}$/.test(normalized)) {
        errors.push('Please enter a valid Kenyan phone number');
      }
    } catch (e) {
      errors.push('Invalid phone number format');
    }
  }

  if (!shipping.email?.trim()) {
    errors.push('Email is required');
  } else if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(shipping.email)) {
    errors.push('Please enter a valid email address');
  }

  if (!shipping.address?.trim()) {
    errors.push('Delivery address is required');
  }

  if (!shipping.city?.trim()) {
    errors.push('City is required');
  }

  if (!shipping.county?.trim()) {
    errors.push('County is required');
  }

  return errors;
};

// ============================================================================
// CART MANAGEMENT
// ============================================================================

/**
 * Get or create cart for user/session
 * GET /api/shopping/cart
 */
exports.getCart = async (req, res) => {
  try {
    const { userId, sessionId } = req.query;

    if (!userId && !sessionId) {
      return res.status(400).json({
        success: false,
        message: 'Either userId or sessionId is required'
      });
    }

    // Find or create cart
    const where = userId ? { userId } : { sessionId };
    
    let cart = await Cart.findOne({
      where: {
        ...where,
        status: 'active'
      },
      include: [
        {
          model: CartItem,
          as: 'items',
          include: [
            {
              model: Product,
              as: 'product',
              include: [{ model: Brand, as: 'brand' }]
            }
          ]
        }
      ]
    });

    if (!cart) {
      // Create new cart
      cart = await Cart.create({
        userId: userId || null,
        sessionId: sessionId || null,
        status: 'active',
        expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000) // 7 days
      });
    }

    // Validate items and update prices if needed
    if (cart.items && cart.items.length > 0) {
      const { validatedItems, errors } = await validateCartItems(cart.items);
      
      if (errors.length > 0) {
        console.warn('Cart validation warnings:', errors);
      }
    }

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

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

/**
 * Add item to cart
 * POST /api/shopping/cart/add
 */
exports.addToCart = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { userId, sessionId, productId, quantity = 1 } = req.body;

    if (!userId && !sessionId) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Either userId or sessionId is required'
      });
    }

    if (!productId || quantity < 1) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Valid productId and quantity are required'
      });
    }

    // Validate product
    const product = await Product.findByPk(productId, {
      include: [{ model: Brand, as: 'brand' }],
      transaction: t
    });

    if (!product || !product.isActive) {
      await t.rollback();
      return res.status(404).json({
        success: false,
        message: 'Product not found or unavailable'
      });
    }

    if (product.stock < quantity) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Only ${product.stock} items available in stock`
      });
    }

    // Find or create cart
    const where = userId ? { userId } : { sessionId };
    
    let cart = await Cart.findOne({
      where: {
        ...where,
        status: 'active'
      },
      transaction: t
    });

    if (!cart) {
      cart = await Cart.create({
        userId: userId || null,
        sessionId: sessionId || null,
        status: 'active',
        expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000)
      }, { transaction: t });
    }

    // Check if item already in cart
    const existingItem = await CartItem.findOne({
      where: {
        cartId: cart.id,
        productId: productId
      },
      transaction: t
    });

    if (existingItem) {
      // Update quantity
      const newQuantity = existingItem.quantity + quantity;
      
      if (product.stock < newQuantity) {
        await t.rollback();
        return res.status(400).json({
          success: false,
          message: `Cannot add ${quantity} more. Only ${product.stock - existingItem.quantity} additional items available`
        });
      }

      await existingItem.update({
        quantity: newQuantity,
        price: product.unitPrice
      }, { transaction: t });
    } else {
      // Add new item
      await CartItem.create({
        cartId: cart.id,
        productId: productId,
        quantity: quantity,
        price: product.unitPrice
      }, { transaction: t });
    }

    await t.commit();

    // Fetch updated cart
    const updatedCart = await Cart.findByPk(cart.id, {
      include: [
        {
          model: CartItem,
          as: 'items',
          include: [
            {
              model: Product,
              as: 'product',
              include: [{ model: Brand, as: 'brand' }]
            }
          ]
        }
      ]
    });

    res.status(200).json({
      success: true,
      message: 'Item added to cart',
      cart: updatedCart
    });

  } catch (error) {
    if (!t.finished) await t.rollback();
    console.error('Add to cart error:', error);
    res.status(500).json({
      success: false,
      message: 'Error adding item to cart',
      error: error.message
    });
  }
};

/**
 * Update cart item quantity
 * PUT /api/shopping/cart/item/:itemId
 */
exports.updateCartItem = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { itemId } = req.params;
    const { quantity } = req.body;

    if (quantity < 0) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Quantity must be positive. Use delete endpoint to remove items.'
      });
    }

    const cartItem = await CartItem.findByPk(itemId, {
      include: [{ model: Product, as: 'product' }],
      transaction: t
    });

    if (!cartItem) {
      await t.rollback();
      return res.status(404).json({
        success: false,
        message: 'Cart item not found'
      });
    }

    // If quantity is 0, delete the item
    if (quantity === 0) {
      await cartItem.destroy({ transaction: t });
      await t.commit();
      
      return res.status(200).json({
        success: true,
        message: 'Item removed from cart'
      });
    }

    // Validate stock
    if (cartItem.product.stock < quantity) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Only ${cartItem.product.stock} items available`
      });
    }

    await cartItem.update({
      quantity: quantity,
      price: cartItem.product.unitPrice
    }, { transaction: t });

    await t.commit();

    // Fetch updated cart
    const updatedCart = await Cart.findByPk(cartItem.cartId, {
      include: [
        {
          model: CartItem,
          as: 'items',
          include: [
            {
              model: Product,
              as: 'product',
              include: [{ model: Brand, as: 'brand' }]
            }
          ]
        }
      ]
    });

    res.status(200).json({
      success: true,
      message: 'Cart updated',
      cart: updatedCart
    });

  } catch (error) {
    if (!t.finished) await t.rollback();
    console.error('Update cart item error:', error);
    res.status(500).json({
      success: false,
      message: 'Error updating cart item',
      error: error.message
    });
  }
};

/**
 * Remove item from cart
 * DELETE /api/shopping/cart/item/:itemId
 */
exports.removeFromCart = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { itemId } = req.params;

    const cartItem = await CartItem.findByPk(itemId, { transaction: t });

    if (!cartItem) {
      await t.rollback();
      return res.status(404).json({
        success: false,
        message: 'Cart item not found'
      });
    }

    const cartId = cartItem.cartId;
    await cartItem.destroy({ transaction: t });
    await t.commit();

    // Fetch updated cart
    const updatedCart = await Cart.findByPk(cartId, {
      include: [
        {
          model: CartItem,
          as: 'items',
          include: [
            {
              model: Product,
              as: 'product',
              include: [{ model: Brand, as: 'brand' }]
            }
          ]
        }
      ]
    });

    res.status(200).json({
      success: true,
      message: 'Item removed from cart',
      cart: updatedCart
    });

  } catch (error) {
    if (!t.finished) await t.rollback();
    console.error('Remove from cart error:', error);
    res.status(500).json({
      success: false,
      message: 'Error removing item from cart',
      error: error.message
    });
  }
};

/**
 * Clear entire cart
 * DELETE /api/shopping/cart/:cartId
 */
exports.clearCart = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { cartId } = req.params;

    await CartItem.destroy({
      where: { cartId },
      transaction: t
    });

    await Cart.update(
      { status: 'abandoned' },
      { where: { id: cartId }, transaction: t }
    );

    await t.commit();

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

  } catch (error) {
    if (!t.finished) await t.rollback();
    console.error('Clear cart error:', error);
    res.status(500).json({
      success: false,
      message: 'Error clearing cart',
      error: error.message
    });
  }
};

// ============================================================================
// CHECKOUT & ORDER CREATION
// ============================================================================

/**
 * Normalize cart items by merging duplicate products
 * @param {Array} items - Raw cart items
 * @returns {Array} Normalized cart items
 */
const normalizeCartItems = (items = []) => {
  const map = new Map();
  
  for (const item of items) {
    if (!item?.product?.id) continue;
    
    const productId = item.product.id;
    
    if (!map.has(productId)) {
      map.set(productId, {
        product: item.product,
        quantity: item.quantity || 0
      });
    } else {
      map.get(productId).quantity += (item.quantity || 0);
    }
  }
  
  return Array.from(map.values());
};

/**
 * Validate cart items with database locks
 * @param {Array} cartItems - Normalized cart items
 * @param {Object} transaction - Sequelize transaction
 * @returns {Object} { validatedItems, errors }
 */
const validateCartItems = async (cartItems, transaction) => {
  const errors = [];
  const validatedItems = [];
  
  for (const item of cartItems) {
    const productId = item.product?.id;
    
    if (!productId) {
      errors.push('Invalid cart item: missing product ID');
      continue;
    }
    
    if (!Number.isInteger(item.quantity) || item.quantity <= 0) {
      errors.push(`Invalid quantity for product ${productId}`);
      continue;
    }
    
    try {
      const product = await Product.findByPk(productId, {
        include: [{ model: Brand, as: 'brand' }],
        transaction,
        lock: transaction.LOCK.UPDATE
      });
      
      if (!product) {
        errors.push(`Product ${productId} not found`);
        continue;
      }
      
      if (!product.isActive) {
        errors.push(`${product.name} is no longer available`);
        continue;
      }
      
      if (product.isTrackInventory && product.stock < item.quantity) {
        errors.push(
          `${product.name}: Only ${product.stock} item(s) available, but ${item.quantity} requested`
        );
        continue;
      }
      
      const currentPrice = Number(product.unitPrice);
      const cartPrice = Number(item.product?.unitPrice);
      
      if (!Number.isFinite(currentPrice) || currentPrice < 0) {
        errors.push(`Invalid price for ${product.name}`);
        continue;
      }
      
      if (!Number.isFinite(cartPrice) || cartPrice < 0) {
        errors.push(`Invalid cart price for ${product.name}`);
        continue;
      }
      
      if (Math.abs(currentPrice - cartPrice) > 0.01) {
        console.warn(
          `Price changed for ${product.name}: ${cartPrice} → ${currentPrice}`
        );
      }
      
      validatedItems.push({
        productId: product.id,
        product,
        quantity: item.quantity,
        unitPrice: currentPrice,
        subtotal: currentPrice * item.quantity
      });
    } catch (error) {
      console.error(`Error validating product ${productId}:`, error);
      errors.push(`Failed to validate product ${productId}`);
    }
  }
  
  return { validatedItems, errors };
};

/**
 * Validate shipping information
 * @param {Object} shipping - Shipping details
 * @returns {Object} { isValid, errors }
 */
const validateShipping = (shipping) => {
  const errors = [];
  
  if (!shipping) {
    errors.push('Shipping information is required');
    return { isValid: false, errors };
  }
  
  const requiredFields = ['fullName', 'email', 'phone', 'address', 'city', 'county'];
  
  for (const field of requiredFields) {
    if (!shipping[field] || typeof shipping[field] !== 'string' || !shipping[field].trim()) {
      errors.push(`Shipping ${field} is required`);
    }
  }
  
  // Email validation
  if (shipping.email) {
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(shipping.email)) {
      errors.push('Invalid email address');
    }
  }
  
  // Phone validation (basic)
  if (shipping.phone) {
    const phoneRegex = /^[\d\s+()-]+$/;
    if (!phoneRegex.test(shipping.phone)) {
      errors.push('Invalid phone number format');
    }
  }
  
  return { isValid: errors.length === 0, errors };
};

/**
 * Validate payment information
 * @param {Object} payment - Payment details
 * @returns {Object} { isValid, errors }
 */
const validatePayment = (payment) => {
  const errors = [];
  
  if (!payment) {
    errors.push('Payment information is required');
    return { isValid: false, errors };
  }
  
  if (!payment.method || typeof payment.method !== 'string') {
    errors.push('Payment method is required');
    return { isValid: false, errors };
  }
  
  const allowedMethods = ['cash', 'card', 'bank_transfer'];
  if (!allowedMethods.includes(payment.method.toLowerCase())) {
    errors.push(`Invalid payment method. Allowed: ${allowedMethods.join(', ')}`);
  }
  
  return { isValid: errors.length === 0, errors };
};

/**
 * Create order controller
 * @param {Object} req - Express request
 * @param {Object} res - Express response
 */
exports.createOrder = async (req, res) => {
  let transaction;
  
  try {
    // Start transaction
    transaction = await sequelize.transaction();
    
    const { items, shipping, payment, customerId, userId } = req.body;
    
    // Validate request body structure
    if (!req.body || typeof req.body !== 'object') {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid request body'
      });
    }
    
    // Validate cart items
    if (!items || !Array.isArray(items) || items.length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Cart is empty'
      });
    }
    
    // Validate shipping
    const shippingValidation = validateShipping(shipping);
    if (!shippingValidation.isValid) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid shipping information',
        errors: shippingValidation.errors
      });
    }
    
    // Validate payment
    const paymentValidation = validatePayment(payment);
    if (!paymentValidation.isValid) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid payment information',
        errors: paymentValidation.errors
      });
    }
    
    // Normalize cart (merge duplicates)
    const cartItems = normalizeCartItems(items);
    
    if (cartItems.length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'No valid items in cart'
      });
    }
    
    // Validate cart items with database locks
    const { validatedItems, errors } = await validateCartItems(cartItems, transaction);
    
    if (errors.length > 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Cart validation failed',
        errors
      });
    }
    
    if (validatedItems.length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'No valid items to order'
      });
    }
    
    // Calculate totals
    const subtotal = validatedItems.reduce((sum, item) => sum + item.subtotal, 0);
    
    if (!Number.isFinite(subtotal) || subtotal <= 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid order total'
      });
    }
    
    const shippingCost = subtotal > 5000 ? 0 : 300;
    const taxAmount = subtotal * 0.16;
    const totalAmount = subtotal + taxAmount + shippingCost;
    
    // Create order
    const order = await Order.create(
      {
        orderId: generateOrderId(),
        customerId: customerId || null,
        userId: userId || null,
        customerName: shipping.fullName.trim(),
        customerEmail: shipping.email.trim().toLowerCase(),
        customerPhone: normalizePhoneNumber(shipping.phone),
        shippingAddress: shipping.address.trim(),
        shippingCity: shipping.city.trim(),
        shippingCounty: shipping.county.trim(),
        subtotal,
        shippingCost,
        taxAmount,
        totalAmount,
        paymentMethod: payment.method.toLowerCase(),
        paymentStatus: 'pending',
        orderStatus: 'pending',
        orderDate: new Date()
      },
      { transaction }
    );
    
    if (!order || !order.id) {
      throw new Error('Failed to create order');
    }
    
    // Create order items
    for (const item of validatedItems) {
      await OrderItem.create(
        {
          orderId: order.id,
          productId: item.productId,
          productName: item.product.name,
          productSku: item.product.sku || null,
          quantity: item.quantity,
          unitPrice: item.unitPrice,
          subtotal: item.subtotal,
          taxAmount: item.subtotal * 0.16
        },
        { transaction }
      );
    }
    
    // Update stock for tracked inventory items
    for (const item of validatedItems) {
      if (item.product.isTrackInventory) {
        const [affectedRows] = await Product.decrement('stock', {
          by: item.quantity,
          where: {
            id: item.productId,
             stock: { [Op.gte]: item.quantity }
          },
          transaction
        });
        
        if (affectedRows === 0) {
          throw new Error(`Insufficient stock for ${item.product.name}`);
        }
      }
    }
    
    // Update order status to confirmed
    await order.update(
      {
        orderStatus: 'confirmed',
        paymentStatus: payment.method === 'cash' ? 'pending' : 'pending'
      },
      { transaction }
    );
    
    // Create shipment record
    const estimatedDeliveryDate = new Date(Date.now() + 3 * 24 * 60 * 60 * 1000);
    
    await Shipment.create(
      {
        orderId: order.id,
        trackingNumber: generateTrackingNumber(),
        carrier: 'Standard Delivery',
        method: shippingCost === 0 ? 'Free Shipping' : 'Standard Shipping',
        status: 'pending',
        estimatedDelivery: estimatedDeliveryDate
      },
      { transaction }
    );
    
    // Commit transaction
    await transaction.commit();
    
    // Success response
    return res.status(201).json({
      success: true,
      message: 'Order created successfully',
      data: {
        orderId: order.orderId,
        totalAmount,
        estimatedDelivery: estimatedDeliveryDate.toISOString().split('T')[0]
      }
    });
    
  } catch (error) {
    // Rollback transaction if not already finished
    if (transaction && !transaction.finished) {
      try {
        await transaction.rollback();
      } catch (rollbackError) {
        console.error('Transaction rollback error:', rollbackError);
      }
    }
    
    console.error('Create order error:', error);
    
    // Handle specific error types
    if (error.name === 'SequelizeValidationError') {
      return res.status(400).json({
        success: false,
        message: 'Validation error',
        errors: error.errors.map(e => e.message)
      });
    }
    
    if (error.name === 'SequelizeUniqueConstraintError') {
      return res.status(409).json({
        success: false,
        message: 'Duplicate order detected'
      });
    }
    
    if (error.name === 'SequelizeDatabaseError') {
      return res.status(500).json({
        success: false,
        message: 'Database error occurred'
      });
    }
    
    // Generic error response
    return res.status(500).json({
      success: false,
      message: 'An error occurred while creating the order',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};


/**
 * Update order status after M-Pesa callback
 * PUT /api/shopping/orders/:orderId/payment-status
 */
exports.updateOrderPaymentStatus = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { orderId } = req.params;
    const { status, mpesaReceiptNumber } = req.body;

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

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

    if (status === 'completed') {
      // Deduct stock
      for (const item of order.items) {
        await Product.decrement('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction: t
        });
      }

      // Update order
      await order.update({
        paymentStatus: 'paid',
        orderStatus: 'confirmed',
        mpesaReceiptNumber: mpesaReceiptNumber,
        paidAt: new Date()
      }, { transaction: t });

      // Update shipment status
      await Shipment.update(
        { status: 'processing' },
        { where: { orderId: order.id }, transaction: t }
      );
    } else if (status === 'failed') {
      await order.update({
        paymentStatus: 'failed',
        orderStatus: 'cancelled'
      }, { transaction: t });
    }

    await t.commit();

    res.status(200).json({
      success: true,
      message: 'Order payment status updated',
      order
    });

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

// ============================================================================
// ORDER MANAGEMENT
// ============================================================================

/**
 * Get order by ID
 * GET /api/shopping/orders/:orderId
 */
exports.getOrder = async (req, res) => {
  try {
    const { orderId } = req.params;

    const order = await Orders.findOne({
      where: { orderId: orderId },
      include: [
        { model: OrderItem, as: 'items' },
        { model: Shipment, as: 'shipment' }
      ]
    });

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

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

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

/**
 * Get customer orders
 * GET /api/shopping/orders/customer/:customerId
 */
exports.getCustomerOrders = async (req, res) => {
  try {
    const { customerId } = req.params;
    const { status, page = 1, limit = 20 } = req.query;

    const where = { customerId };
    if (status) where.orderStatus = status;

    const offset = (page - 1) * limit;

    const { count, rows: orders } = await Orders.findAndCountAll({
      where,
      include: [
        { model: OrderItem, as: 'items' },
        { model: Shipment, as: 'shipment' }
      ],
      order: [['createdAt', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset)
    });

    res.status(200).json({
      success: true,
      orders,
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    });

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

/**
 * Cancel order
 * PUT /api/shopping/orders/:orderId/cancel
 */
exports.cancelOrder = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { orderId } = req.params;
    const { reason } = req.body;

    const order = await Orders.findOne({
      where: { orderId },
      include: [{ model: OrderItem, as: 'items' }],
      transaction: t
    });

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

    // Can only cancel pending or confirmed orders
    if (!['pending', 'confirmed', 'processing'].includes(order.orderStatus)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Cannot cancel order with status: ${order.orderStatus}`
      });
    }

    // Restore stock if order was confirmed
    if (order.orderStatus === 'confirmed' && order.paymentStatus === 'paid') {
      for (const item of order.items) {
        await Product.increment('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction: t
        });
      }
    }

    await order.update({
      orderStatus: 'cancelled',
      cancelReason: reason || 'Customer requested cancellation',
      cancelledAt: new Date()
    }, { transaction: t });

    // Update shipment
    await Shipment.update(
      { status: 'cancelled' },
      { where: { orderId: order.id }, transaction: t }
    );

    await t.commit();

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

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

// ============================================================================
// SHIPMENT TRACKING
// ============================================================================

/**
 * Update shipment status
 * PUT /api/shopping/shipments/:trackingNumber
 */
exports.updateShipment = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { trackingNumber } = req.params;
    const { status, carrier, shippedAt, deliveredAt } = req.body;

    const shipment = await Shipment.findOne({
      where: { trackingNumber },
      include: [{ model: Orders, as: 'order' }],
      transaction: t
    });

    if (!shipment) {
      await t.rollback();
      return res.status(404).json({
        success: false,
        message: 'Shipment not found'
      });
    }

    const updateData = { status };
    if (carrier) updateData.carrier = carrier;
    if (shippedAt) updateData.shippedAt = new Date(shippedAt);
    if (deliveredAt) updateData.deliveredAt = new Date(deliveredAt);

    await shipment.update(updateData, { transaction: t });

    // Update order status based on shipment status
    if (status === 'shipped') {
      await Orders.update(
        { orderStatus: 'shipped' },
        { where: { id: shipment.orderId }, transaction: t }
      );
    } else if (status === 'delivered') {
      await Orders.update(
        { 
          orderStatus: 'delivered',
          deliveredAt: new Date()
        },
        { where: { id: shipment.orderId }, transaction: t }
      );
    }

    await t.commit();

    res.status(200).json({
      success: true,
      message: 'Shipment updated successfully',
      shipment
    });

  } catch (error) {
    if (!t.finished) await t.rollback();
    console.error('Update shipment error:', error);
    res.status(500).json({
      success: false,
      message: 'Error updating shipment',
      error: error.message
    });
  }
};

/**
 * Track shipment by tracking number
 * GET /api/shopping/shipments/track/:trackingNumber
 */
exports.trackShipment = async (req, res) => {
  try {
    const { trackingNumber } = req.params;

    const shipment = await Shipment.findOne({
      where: { trackingNumber },
      include: [
        {
          model: Orders,
          as: 'order',
          include: [{ model: OrderItem, as: 'items' }]
        }
      ]
    });

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

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

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

// ============================================================================
// ADMIN ENDPOINTS
// ============================================================================

/**
 * Get all orders (Admin)
 * GET /api/shopping/admin/orders
 */
exports.getAllOrders = async (req, res) => {
  try {
    const {
      status,
      paymentStatus,
      startDate,
      endDate,
      page = 1,
      limit = 50
    } = req.query;

    const where = {};
    if (status) where.orderStatus = status;
    if (paymentStatus) where.paymentStatus = paymentStatus;
    
    if (startDate && endDate) {
      where.orderDate = {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      };
    } else if (startDate) {
      where.orderDate = { [Op.gte]: new Date(startDate) };
    } else if (endDate) {
      where.orderDate = { [Op.lte]: new Date(endDate) };
    }

    const offset = (page - 1) * limit;

    const { count, rows: orders } = await Orders.findAndCountAll({
      where,
      include: [
        { model: OrderItem, as: 'items' },
        { model: Shipment, as: 'shipment' }
      ],
      order: [['createdAt', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset)
    });

    res.status(200).json({
      success: true,
      orders,
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    });

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

/**
 * Update order status (Admin)
 * PUT /api/shopping/admin/orders/:orderId/status
 */
exports.updateOrderStatus = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { orderId } = req.params;
    const { status, notes } = req.body;

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

    if (!validStatuses.includes(status)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Invalid status. Must be one of: ${validStatuses.join(', ')}`
      });
    }

    const order = await Orders.findOne({
      where: { orderId },
      transaction: t
    });

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

    await order.update({
      orderStatus: status,
      adminNotes: notes || order.adminNotes
    }, { transaction: t });

    // Update shipment status accordingly
    if (status === 'shipped') {
      await Shipment.update(
        { 
          status: 'shipped',
          shippedAt: new Date()
        },
        { where: { orderId: order.id }, transaction: t }
      );
    } else if (status === 'delivered') {
      await Shipment.update(
        { 
          status: 'delivered',
          deliveredAt: new Date()
        },
        { where: { orderId: order.id }, transaction: t }
      );
    }

    await t.commit();

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

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

/**
 * Get order statistics (Admin)
 * GET /api/shopping/admin/statistics
 */
exports.getOrderStatistics = async (req, res) => {
  try {
    const { startDate, endDate } = req.query;

    const where = {};
    if (startDate && endDate) {
      where.orderDate = {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      };
    }

    // Total orders
    const totalOrders = await Orders.count({ where });

    // Orders by status
    const ordersByStatus = await Orders.findAll({
      where,
      attributes: [
        'orderStatus',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalRevenue']
      ],
      group: ['orderStatus'],
      raw: true
    });

    // Orders by payment method
    const ordersByPayment = await Orders.findAll({
      where,
      attributes: [
        'paymentMethod',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount']
      ],
      group: ['paymentMethod'],
      raw: true
    });

    // Total revenue
    const totalRevenue = await Orders.sum('totalAmount', {
      where: {
        ...where,
        paymentStatus: 'paid'
      }
    });

    // Average order value
    const avgOrderValue = totalOrders > 0 ? totalRevenue / totalOrders : 0;

    // Pending payments
    const pendingPayments = await Orders.sum('totalAmount', {
      where: {
        ...where,
        paymentStatus: 'pending'
      }
    });

    // Top selling products
    const topProducts = await OrderItem.findAll({
      attributes: [
        'productId',
        'productName',
        [sequelize.fn('SUM', sequelize.col('quantity')), 'totalQuantity'],
        [sequelize.fn('SUM', sequelize.col('subtotal')), 'totalRevenue']
      ],
      include: [
        {
          model: Orders,
          as: 'order',
          where,
          attributes: []
        }
      ],
      group: ['productId', 'productName'],
      order: [[sequelize.fn('SUM', sequelize.col('quantity')), 'DESC']],
      limit: 10,
      raw: true
    });

    res.status(200).json({
      success: true,
      statistics: {
        totalOrders,
        totalRevenue: parseFloat((totalRevenue || 0).toFixed(2)),
        avgOrderValue: parseFloat((avgOrderValue || 0).toFixed(2)),
        pendingPayments: parseFloat((pendingPayments || 0).toFixed(2)),
        ordersByStatus,
        ordersByPayment,
        topProducts
      }
    });

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

// ============================================================================
// WEBHOOK HANDLERS
// ============================================================================

/**
 * M-Pesa callback handler
 * POST /api/shopping/webhooks/mpesa-callback
 */
exports.handleMpesaCallback = async (req, res) => {
  const startTime = Date.now();
  
  try {
    const callbackData = req.body;
    console.log('📥 M-PESA CALLBACK (Shopping):', JSON.stringify(callbackData, null, 2));

    // Acknowledge immediately
    res.status(200).json({
      ResultCode: 0,
      ResultDesc: 'Accepted'
    });

    // Process in background
    setImmediate(async () => {
      try {
        const mpesaService = getMpesaService();
        const { success, data } = mpesaService.extractCallbackData(callbackData);

        if (!data.CheckoutRequestID) {
          console.error('❌ Missing CheckoutRequestID in callback');
          return;
        }

        // Find M-Pesa payment record
        const mpesaPayment = await MpesaPayment.findOne({
          where: { CheckoutRequestID: data.CheckoutRequestID }
        });

        if (!mpesaPayment) {
          console.error('❌ M-Pesa payment not found:', data.CheckoutRequestID);
          return;
        }

        // Check if already processed
        if (mpesaPayment.status === 'Completed' || mpesaPayment.status === 'Failed') {
          console.log('⚠️ Callback already processed:', data.CheckoutRequestID);
          return;
        }

        // Update M-Pesa payment
        await mpesaPayment.update({
          ...data,
          status: success ? 'Completed' : 'Failed',
          callbackReceivedAt: new Date()
        });

        console.log(`✓ M-Pesa payment ${data.CheckoutRequestID} → ${success ? 'COMPLETED' : 'FAILED'}`);

        // Update order
        if (mpesaPayment.orderId) {
          const t = await sequelize.transaction();
          
          try {
            const order = await Orders.findByPk(mpesaPayment.orderId, {
              include: [{ model: OrderItem, as: 'items' }],
              transaction: t
            });

            if (order) {
              if (success) {
                // Deduct stock
                for (const item of order.items) {
                  await Product.decrement('stock', {
                    by: item.quantity,
                    where: { id: item.productId },
                    transaction: t
                  });
                }

                // Update order
                await order.update({
                  paymentStatus: 'paid',
                  orderStatus: 'confirmed',
                  mpesaReceiptNumber: data.MpesaReceiptNumber,
                  paidAt: new Date()
                }, { transaction: t });

                // Update shipment
                await Shipment.update(
                  { status: 'processing' },
                  { where: { orderId: order.id }, transaction: t }
                );

                console.log(`✓ Order ${order.orderId} confirmed and stock deducted`);
              } else {
                await order.update({
                  paymentStatus: 'failed',
                  orderStatus: 'cancelled'
                }, { transaction: t });

                console.log(`✓ Order ${order.orderId} cancelled due to payment failure`);
              }
            }

            await t.commit();
          } catch (txnError) {
            await t.rollback();
            console.error('❌ Failed to update order:', txnError.message);
          }
        }

        const processingTime = Date.now() - startTime;
        console.log(`⏱ Callback processed in ${processingTime}ms`);

      } catch (bgError) {
        console.error('❌ Background callback processing error:', bgError);
      }
    });

  } catch (error) {
    console.error('❌ Callback error:', error);
    
    if (!res.headersSent) {
      res.status(200).json({
        ResultCode: 0,
        ResultDesc: 'Accepted'
      });
    }
  }
};

// ============================================================================
// ABANDONED CART RECOVERY
// ============================================================================

/**
 * Get abandoned carts
 * GET /api/shopping/admin/abandoned-carts
 */
exports.getAbandonedCarts = async (req, res) => {
  try {
    const { days = 3, page = 1, limit = 20 } = req.query;

    const cutoffDate = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
    const offset = (page - 1) * limit;

    const { count, rows: carts } = await Cart.findAndCountAll({
      where: {
        status: 'active',
        updatedAt: {
          [Op.lt]: cutoffDate
        }
      },
      include: [
        {
          model: CartItem,
          as: 'items',
          include: [
            {
              model: Product,
              as: 'product',
              include: [{ model: Brand, as: 'brand' }]
            }
          ]
        }
      ],
      order: [['updatedAt', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset)
    });

    res.status(200).json({
      success: true,
      carts,
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    });

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

/**
 * Clean up expired carts (Cron job endpoint)
 * DELETE /api/shopping/admin/cleanup-carts
 */
exports.cleanupExpiredCarts = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const now = new Date();

    // Find expired carts
    const expiredCarts = await Cart.findAll({
      where: {
        expiresAt: {
          [Op.lt]: now
        },
        status: 'active'
      },
      transaction: t
    });

    // Update status to expired
    await Cart.update(
      { status: 'expired' },
      {
        where: {
          expiresAt: { [Op.lt]: now },
          status: 'active'
        },
        transaction: t
      }
    );

    await t.commit();

    res.status(200).json({
      success: true,
      message: `${expiredCarts.length} carts expired`,
      count: expiredCarts.length
    });

  } catch (error) {
    if (!t.finished) await t.rollback();
    console.error('Cleanup carts error:', error);
    res.status(500).json({
      success: false,
      message: 'Error cleaning up carts',
      error: error.message
    });
  }
};

module.exports = exports;