// controllers/transactionController.js
const { Transaction, TransactionItem, TransactionPayment,DailySales,AuditLog, Product, User, Store, Register, sequelize } = require('../models');
const { Op } = require('sequelize');

const bcrypt = require('bcryptjs');

// ============================================
// TRANSACTION STATUS CONSTANTS
// ============================================
const TRANSACTION_STATUS = {
  OPEN: 'open',                    // Items being added
  SUSPENDED: 'suspended',          // Temporarily saved
  PENDING_PAYMENT: 'pending_payment', // Awaiting payment
  AUTHORIZED: 'authorized',        // Payment authorized (card pre-auth)
  PAID: 'paid',                    // Payment successful
  COMPLETED: 'completed',          // Fully processed
  VOIDED: 'voided',               // Cancelled before payment
  REFUNDED: 'refunded',           // Money returned after completion
  PARTIALLY_REFUNDED: 'partially_refunded', // Partial refund
  FAILED: 'failed',               // Payment declined/error
  REVERSED: 'reversed'            // M-Pesa reversal
};


// Utility function to recalculate transaction totals based on its current items
const recalculateTransactionTotals = async (transactionId, transactionInstance, t) => {
    // Fetch all current items for the transaction within the same transaction 't'
    const currentItems = await TransactionItem.findAll({
        where: { transactionId },
        include: [{ model: Product, as: 'product' }],
        transaction: t
    });

    let subtotal = 0;
    
    // 1. Calculate new Subtotal
    for (const item of currentItems) {
        // Line total is calculated from current item data
        const lineTotal = item.unitPrice * item.quantity;
        subtotal += lineTotal;

        // Optionally update lineTotal and taxAmount in TransactionItem if they are being recalculated based on external factors
        // Since we're using current TransactionItem unitPrice, we just ensure it's calculated correctly here
        await item.update({
            lineTotal,
            // Assuming 16% tax applied to line total
            taxAmount: lineTotal * 0.16 
        }, { transaction: t });
    }

    // Use existing discount info from the Transaction instance
    const discountAmount = transactionInstance.discountAmount;
    const discountPercent = transactionInstance.discountPercent;
    
    let finalDiscount = 0;
    if (discountPercent > 0) {
        finalDiscount = subtotal * (discountPercent / 100);
    } else {
        finalDiscount = discountAmount;
    }

    // Calculate tax (16% on taxable amount)
    const taxableAmount = subtotal - finalDiscount;
    const taxAmount = taxableAmount * 0.16;
    
    // Calculate total
    const totalAmount = taxableAmount + taxAmount;

    // Update the transaction instance with the new totals
    await transactionInstance.update({
        subtotal,
        discountAmount: finalDiscount,
        taxAmount,
        totalAmount,
    }, { transaction: t });

    return transactionInstance;
};




exports.createTransaction = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { storeId, registerId, customerId, cashierId, items, payments } = req.body;

    // Validate required fields
    if (!storeId || !registerId || !cashierId || !items || !items.length) {
      await t.rollback();
      return res.status(400).json({ 
        success: false, 
        message: 'Missing required fields: storeId, registerId, cashierId, or items' 
      });
    }

    // For M-Pesa, payments might be empty initially (will be updated via callback)
    // For other payment methods, payments array is required
    const paymentMethod = payments?.[0]?.paymentMethod;
    const isMobileMoneyPayment = paymentMethod === 'mobile_money';

    if (!payments || !payments.length) {
      await t.rollback();
      return res.status(400).json({ 
        success: false, 
        message: 'Payment information is required' 
      });
    }

    // Validate stock availability for all items
    for (const item of items) {
      const product = await Product.findByPk(item.productId);
      
      if (!product) {
        await t.rollback();
        return res.status(404).json({ 
          success: false, 
          message: `Product with ID ${item.productId} not found` 
        });
      }

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

      if (!product.isActive) {
        await t.rollback();
        return res.status(400).json({ 
          success: false, 
          message: `Product ${product.name} is not active` 
        });
      }
    }

    // Calculate totals
    let subtotal = 0;
    const itemsWithCalculations = [];

    for (const item of items) {
      const product = await Product.findByPk(item.productId);
      const lineTotal = item.unitPrice * item.quantity;
      subtotal += lineTotal;

      itemsWithCalculations.push({
        productId: item.productId,
        quantity: item.quantity,
        unitPrice: item.unitPrice,
        lineTotal,
        product
      });
    }

    // Calculate discount if provided
    const discountAmount = req.body.discountAmount || 0;
    const discountPercent = req.body.discountPercent || 0;
    
    let finalDiscount = 0;
    if (discountPercent > 0) {
      finalDiscount = subtotal * (discountPercent / 100);
    } else {
      finalDiscount = discountAmount;
    }

    // Calculate tax (16%)
    const taxableAmount = subtotal - finalDiscount;
    const taxAmount = taxableAmount * 0.16;
    
    // Calculate total
    const totalAmount = taxableAmount + taxAmount;

    // Validate payment amount (skip for pending mobile money payments)
    const totalPaid = payments.reduce((sum, payment) => sum + payment.amount, 0);
    
    // For mobile money, we create transaction as 'pending' and validate later
    if (!isMobileMoneyPayment && totalPaid < totalAmount) {
      await t.rollback();
      return res.status(400).json({ 
        success: false, 
        message: `Insufficient payment. Total: ${totalAmount.toFixed(2)}, Paid: ${totalPaid.toFixed(2)}` 
      });
    }

    // Generate transaction number
    const date = new Date();
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    
    // Get last transaction number for today
    const lastTransaction = await Transaction.findOne({
      where: {
        transactionNumber: {
          [Op.like]: `TXN${year}${month}${day}%`
        }
      },
      order: [['transactionNumber', 'DESC']],
      transaction: t
    });

    let sequence = 1;
    if (lastTransaction) {
      const lastSequence = parseInt(lastTransaction.transactionNumber.slice(-4));
      sequence = lastSequence + 1;
    }

    const transactionNumber = `TXN${year}${month}${day}${String(sequence).padStart(4, '0')}`;

    // Determine transaction status
    // For mobile money, status is 'pending' until M-Pesa callback confirms
    // For cash/card, status is 'completed' immediately
    const transactionStatus = isMobileMoneyPayment ? 'pending' : 'completed';

    // Create transaction
    const transaction = await Transaction.create({
      transactionNumber,
      storeId,
      registerId,
      customerId: customerId || null,
      cashierId,
      transactionDate: new Date(),
      subtotal,
      discountAmount: finalDiscount,
      discountPercent: discountPercent || 0,
      taxAmount,
      totalAmount,
      status: transactionStatus
    }, { transaction: t });

    // Create transaction items
    for (const item of itemsWithCalculations) {
      await TransactionItem.create({
        transactionId: transaction.id,
        productId: item.productId,
        quantity: item.quantity,
        unitPrice: item.unitPrice,
        lineTotal: item.lineTotal,
        discountAmount: 0,
        taxAmount: item.lineTotal * 0.16
      }, { transaction: t });

      // Update product stock only if not mobile money or if mobile money is already paid
      if (!isMobileMoneyPayment) {
        await Product.decrement('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction: t
        });
      }
    }

    // Create payment records
    for (const payment of payments) {
      // For mobile money, status is 'pending' until confirmation
      const paymentStatus = isMobileMoneyPayment ? 'pending' : 'completed';
      
      await TransactionPayment.create({
        transactionId: transaction.id,
        paymentMethod: payment.paymentMethod,
        amount: payment.amount,
        paymentDate: new Date(),
        status: paymentStatus,
        referenceNumber: payment.referenceNumber || null
      }, { transaction: t });
    }

    // Update DailySales only for completed transactions
    if (!isMobileMoneyPayment) {
      const today = new Date();
      const salesDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());

      const existingDaily = await DailySales.findOne({
        where: { storeId, registerId, salesDate },
        transaction: t
      });

      // Group payments by method
      const cashSales = payments
        .filter(p => p.paymentMethod === 'cash')
        .reduce((a, b) => a + b.amount, 0);

      const cardSales = payments
        .filter(p => p.paymentMethod === 'card')
        .reduce((a, b) => a + b.amount, 0);

      const mobilePayments = payments
        .filter(p => ['mobile_money', 'mpesa'].includes(p.paymentMethod))
        .reduce((a, b) => a + b.amount, 0);

      if (existingDaily) {
        await existingDaily.increment({
          totalTransactions: 1,
          totalItems: itemsWithCalculations.reduce((sum, i) => sum + i.quantity, 0),
          grossSales: subtotal,
          discounts: finalDiscount,
          taxes: taxAmount,
          netSales: totalAmount,
          refunds: 0,
          cashSales,
          cardSales,
          mobilePayments
        }, { transaction: t });
      } else {
        await DailySales.create({
          storeId,
          registerId,
          salesDate,
          totalTransactions: 1,
          totalItems: itemsWithCalculations.reduce((sum, i) => sum + i.quantity, 0),
          grossSales: subtotal,
          discounts: finalDiscount,
          taxes: taxAmount,
          netSales: totalAmount,
          refunds: 0,
          cashSales,
          cardSales,
          mobilePayments
        }, { transaction: t });
      }
    }

    // Commit transaction
    await t.commit();

    // Fetch complete transaction with associations
    const completeTransaction = await Transaction.findByPk(transaction.id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ]
    });

    res.status(201).json({
      success: true,
      message: isMobileMoneyPayment 
        ? 'Transaction created. Awaiting M-Pesa payment confirmation.' 
        : 'Transaction created successfully',
      transaction: completeTransaction
    });

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




// Update transaction status (called by M-Pesa callback)
exports.updateTransactionStatus = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { transactionId } = req.params;
    const { status, mpesaReceiptNumber } = req.body;

    const transaction = await Transaction.findByPk(transactionId, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ],
      transaction: t
    });

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

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

    // Update payment status and reference number
    if (transaction.transactionpayment && transaction.transactionpayment.length > 0) {
      for (const payment of transaction.transactionpayment) {
        await payment.update({
          status: status === 'completed' ? 'completed' : 'failed',
          referenceNumber: mpesaReceiptNumber || payment.referenceNumber
        }, { transaction: t });
      }
    }

    // If payment is completed, update stock and daily sales
    if (status === 'completed') {
      // Update product stock
      for (const item of transaction.transactionsitems) {
        await Product.decrement('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction: t
        });
      }

      // Update daily sales
      const today = new Date(transaction.transactionDate);
      const salesDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());

      const existingDaily = await DailySales.findOne({
        where: { 
          storeId: transaction.storeId, 
          registerId: transaction.registerId, 
          salesDate 
        },
        transaction: t
      });

      const mobilePayment = transaction.totalAmount;
      const totalItems = transaction.transactionsitems.reduce((sum, i) => sum + i.quantity, 0);

      if (existingDaily) {
        await existingDaily.increment({
          totalTransactions: 1,
          totalItems,
          grossSales: transaction.subtotal,
          discounts: transaction.discountAmount,
          taxes: transaction.taxAmount,
          netSales: transaction.totalAmount,
          mobilePayments: mobilePayment
        }, { transaction: t });
      } else {
        await DailySales.create({
          storeId: transaction.storeId,
          registerId: transaction.registerId,
          salesDate,
          totalTransactions: 1,
          totalItems,
          grossSales: transaction.subtotal,
          discounts: transaction.discountAmount,
          taxes: transaction.taxAmount,
          netSales: transaction.totalAmount,
          refunds: 0,
          cashSales: 0,
          cardSales: 0,
          mobilePayments: mobilePayment
        }, { transaction: t });
      }
    }

    await t.commit();

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

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




// Get all transactions with filters
exports.getAllTransactions = async (req, res) => {
  try {
    const { 
      storeId, 
      registerId, 
      cashierId, 
      customerId,
      startDate, 
      endDate, 
      status,
      paymentMethod,
      page = 1, 
      limit = 50 
    } = req.query;

    const offset = (page - 1) * limit;
    const where = {};

    if (storeId) where.storeId = storeId;
    if (registerId) where.registerId = registerId;
    if (cashierId) where.cashierId = cashierId;
    if (customerId) where.customerId = customerId;
    if (status) where.status = status;

    if (startDate && endDate) {
      where.transactionDate = {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      };
    } else if (startDate) {
      where.transactionDate = {
        [Op.gte]: new Date(startDate)
      };
    } else if (endDate) {
      where.transactionDate = {
        [Op.lte]: new Date(endDate)
      };
    }

    const include = [
      {
        model: TransactionItem,
        as: 'transactionsitems',
        include: [{ 
          model: Product, 
          as: 'product',
          attributes: ['id', 'name', 'sku', 'unitPrice'] 
        }]
      },
      {
        model: TransactionPayment,
        as: 'transactionpayment'
      },
      {
        model: User,
        as: 'cashier',
        attributes: ['id', 'firstName', 'lastName', 'email']
      },
      {
        model: Store,
        as: 'store',
        attributes: ['id', 'name']
      }
    ];

    // Filter by payment method if specified
    if (paymentMethod) {
      include[1].where = { paymentMethod };
    }

    const { count, rows: transactions } = await Transaction.findAndCountAll({
      where,
      include,
      order: [['transactionDate', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset),
      distinct: true
    });

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

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

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

//     const transaction = await Transaction.findByPk(id, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ 
//             model: Product, 
//             as: 'product',
//             attributes: ['id', 'name', 'sku', 'unitPrice', 'imageUrl'] 
//           }]
//         },
//         {
//           model: TransactionPayment,
//           as: 'transactionpayment'
//         },
//         {
//           model: User,
//           as: 'cashier',
//           attributes: ['id', 'firstName', 'lastName', 'email']
//         },
//         // {
//         //   model: User,
//         //   as: 'customer',
//         //   attributes: ['id', 'firstName', 'lastName', 'email', 'phone']
//         // },
//         {
//           model: Store,
//           as: 'store',
//           attributes: ['id', 'name', 'address', 'phone', 'email']
//         },
//         {
//           model: Register,
//           as: 'register',
//           attributes: ['id', 'code', 'status']
//         }
//       ]
//     });

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

//     console.log('Fetched transaction:', transaction);

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

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




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

    // Validate ID
    if (!id || isNaN(id)) {
      return res.status(400).json({ 
        success: false, 
        message: 'Invalid transaction ID' 
      });
    }

    const transaction = await Transaction.findByPk(id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          required: false, // Don't fail if no items
          include: [{ 
            model: Product, 
            as: 'product',
            required: false,
            attributes: ['id', 'name', 'sku', 'unitPrice', 'imageUrl'] 
          }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment',
          required: false // Don't fail if no payments
        },
        {
          model: User,
          as: 'cashier',
          required: false,
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: Store,
          as: 'store',
          required: false,
          attributes: ['id', 'name', 'address', 'phone', 'email']
        },
        {
          model: Register,
          as: 'register',
          required: false,
          attributes: ['id', 'code', 'status']
        }
      ]
    });

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

    // Ensure all arrays exist (even if empty)
    const formattedTransaction = {
      ...transaction.toJSON(),
      transactionsitems: transaction.transactionsitems || [],
      transactionpayment: transaction.transactionpayment || []
    };

    console.log('Fetched transaction:', {
      id: formattedTransaction.id,
      itemsCount: formattedTransaction.transactionsitems.length,
      paymentsCount: formattedTransaction.transactionpayment.length
    });

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

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

// Get transaction by transaction number
exports.getTransactionByNumber = async (req, res) => {
  try {
    const { transactionNumber } = req.params;

    const transaction = await Transaction.findOne({
      where: { transactionNumber },
      include: [
        {
          model: TransactionItem,
          as: 'items',
          include: [{ 
            model: Product, 
            as: 'product' 
          }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName']
        },
        {
          model: Store,
          as: 'store'
        }
      ]
    });

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

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

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

// Void/Cancel transaction
// exports.voidTransaction = async (req, res) => {
//   const t = await sequelize.transaction();
  
//   try {
//     const { id } = req.params;
//     const { managerPin, reason } = req.body;
//     const voidedBy = req.user.id; // Assuming user is attached to req by auth middleware

//     // ============================================
//     // 1. VALIDATION - Transaction Existence
//     // ============================================
//     const transaction = await Transaction.findByPk(id, {
//       include: [
//         { 
//           model: TransactionItem, 
//           as: 'transactionsitems',
//           include: [{
//             model: Product,
//             as: 'product',
//             attributes: ['id', 'name', 'sku', 'stock']
//           }]
//         },
//         {
//           model: TransactionPayment,
//           as: 'transactionpayment'
//         },
//         {
//           model: User,
//           as: 'cashier',
//           attributes: ['id', 'firstName', 'lastName']
//         },
//         {
//           model: Register,
//           as: 'register',
//           attributes: ['id', 'code']
//         }
//       ],
//       transaction: t
//     });

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

//     // ============================================
//     // 2. VALIDATION - Transaction Status
//     // ============================================
//     if (transaction.status === 'voided') {
//       await t.rollback();
//       return res.status(400).json({ 
//         success: false, 
//         message: 'Transaction is already voided' 
//       });
//     }

//     if (transaction.status !== 'completed') {
//       await t.rollback();
//       return res.status(400).json({ 
//         success: false, 
//         message: `Cannot void transaction with status: ${transaction.status}` 
//       });
//     }

//     // ============================================
//     // 3. VALIDATION - Same Day Check (Business Rule)
//     // ============================================
//     const transactionDate = new Date(transaction.transactionDate);
//     const today = new Date();
    
//     // Reset hours to compare only dates
//     transactionDate.setHours(0, 0, 0, 0);
//     today.setHours(0, 0, 0, 0);
    
//     if (transactionDate.getTime() !== today.getTime()) {
//       await t.rollback();
//       return res.status(400).json({ 
//         success: false, 
//         message: 'Void operation only allowed on the same day as the transaction. Use refund instead.' 
//       });
//     }

//     // ============================================
//     // 4. VALIDATION - Manager Authorization
//     // ============================================
//     if (!managerPin) {
//       await t.rollback();
//       return res.status(400).json({ 
//         success: false, 
//         message: 'Manager PIN is required' 
//       });
//     }

//     // Verify manager PIN (adjust based on your user model)
//     const manager = await User.findOne({
//       where: { 
//         id: req.user.id,
//         // role: ['manager', 'admin', 'super_admin'] // Adjust roles as needed
//       },
//       transaction: t
//     });

//     console.log('FOUND ROLES',manager)

    

//     if (!manager) {
//       await t.rollback();
//       return res.status(403).json({ 
//         success: false, 
//         message: 'Unauthorized: Manager privileges required' 
//       });
//     }

//     // Verify PIN (assuming you have bcrypt for password comparison)
//     const bcrypt = require('bcryptjs');
//     const isPinValid = await bcrypt.compare(managerPin, manager.pin || manager.password);
    
//     if (!isPinValid) {
//       await t.rollback();
//       return res.status(401).json({ 
//         success: false, 
//         message: 'Invalid manager PIN' 
//       });
//     }

//     // ============================================
//     // 5. VALIDATION - Payment Method Check
//     // ============================================
//     const payment = transaction.transactionpayment?.[0];
    
//     if (payment?.paymentMethod === 'mobile_money') {
//       // For M-Pesa, we might need to initiate a reversal instead
//       await t.rollback();
//       return res.status(400).json({ 
//         success: false, 
//         message: 'M-Pesa transactions require reversal through payment gateway. Use reverse operation instead.' 
//       });
//     }

//     // ============================================
//     // 6. RESTORE INVENTORY
//     // ============================================
//     const items = transaction.transactionsitems || [];
    
//     if (!items || items.length === 0) {
//       await t.rollback();
//       return res.status(400).json({ 
//         success: false, 
//         message: 'No items found in transaction' 
//       });
//     }

//     const inventoryUpdates = [];
    
//     for (const item of items) {
//       if (!item.product) {
//         console.warn(`Product not found for item ${item.id}`);
//         continue;
//       }

//       // Restore stock
//       await Product.increment('stock', {
//         by: item.quantity,
//         where: { id: item.productId },
//         transaction: t
//       });

//       // Track what was restored for audit
//       inventoryUpdates.push({
//         productId: item.productId,
//         productName: item.product.name,
//         sku: item.product.sku,
//         quantityRestored: item.quantity,
//         previousStock: item.product.stock,
//         newStock: item.product.stock + item.quantity
//       });
//     }

//     // ============================================
//     // 7. UPDATE TRANSACTION STATUS
//     // ============================================
//     await transaction.update({
//       status: 'voided',
//       voidReason: reason || 'No reason provided',
//       voidedBy: voidedBy,
//       voidedAt: new Date()
//     }, { transaction: t });

//     // ============================================
//     // 8. UPDATE PAYMENT STATUS
//     // ============================================
//     if (payment) {
//       await TransactionPayment.update(
//         { 
//           status: 'voided',
//           voidedAt: new Date()
//         },
//         { 
//           where: { id: payment.id },
//           transaction: t 
//         }
//       );
//     }

//     // ============================================
//     // 9. CREATE AUDIT LOG
//     // ============================================
//     // Assuming you have an AuditLog model
//     await AuditLog.create({
//       action: 'VOID_TRANSACTION',
//       entityType: 'Transaction',
//       entityId: transaction.id,
//       userId: voidedBy,
//       details: JSON.stringify({
//         transactionNumber: transaction.transactionNumber,
//         originalAmount: transaction.totalAmount,
//         reason: reason,
//         inventoryRestored: inventoryUpdates,
//         voidedBy: {
//           id: manager.id,
//           name: `${manager.firstName} ${manager.lastName}`
//         },
//         cashier: {
//           id: transaction.cashier?.id,
//           name: `${transaction.cashier?.firstName} ${transaction.cashier?.lastName}`
//         },
//         register: transaction.register?.code
//       }),
//       ipAddress: req.ip,
//       userAgent: req.get('user-agent'),
//       timestamp: new Date()
//     }, { transaction: t });

//     // ============================================
//     // 10. UPDATE REGISTER TOTALS (if tracking daily totals)
//     // ============================================
//     if (transaction.registerId) {
//       // Deduct from register's daily total
//       await Register.decrement('dailyTotal', {
//         by: parseFloat(transaction.totalAmount),
//         where: { id: transaction.registerId },
//         transaction: t
//       });
//     }

//     // ============================================
//     // 11. COMMIT TRANSACTION
//     // ============================================
//     await t.commit();

//     // ============================================
//     // 12. SEND SUCCESS RESPONSE
//     // ============================================
//     res.status(200).json({
//       success: true,
//       message: 'Transaction voided successfully',
//       transaction: {
//         id: transaction.id,
//         transactionNumber: transaction.transactionNumber,
//         status: 'voided',
//         voidedAt: new Date(),
//         voidedBy: {
//           id: manager.id,
//           name: `${manager.firstName} ${manager.lastName}`
//         },
//         originalAmount: transaction.totalAmount,
//         itemsRestored: inventoryUpdates.length
//       },
//       inventoryUpdates
//     });

//   } catch (error) {
//     // ============================================
//     // 13. ERROR HANDLING
//     // ============================================
//     await t.rollback();
    
//     console.error('Void transaction error:', {
//       transactionId: req.params.id,
//       error: error.message,
//       stack: error.stack,
//       userId: req.user?.id
//     });

//     // Send appropriate error response
//     if (error.name === 'SequelizeValidationError') {
//       return res.status(400).json({ 
//         success: false, 
//         message: 'Validation error', 
//         errors: error.errors.map(e => e.message)
//       });
//     }

//     if (error.name === 'SequelizeForeignKeyConstraintError') {
//       return res.status(400).json({ 
//         success: false, 
//         message: 'Database constraint error. Transaction may have related records.' 
//       });
//     }

//     res.status(500).json({ 
//       success: false, 
//       message: 'Error voiding transaction. Please try again or contact support.', 
//       error: process.env.NODE_ENV === 'development' ? error.message : undefined
//     });
//   }
// };

exports.voidTransaction = async (req, res) => {
  // Make sure 'bcryptjs' and 'sequelize' (and 'Op' if needed for the query) are available globally 
  // or imported at the top of the file where this controller is defined.
  // const bcrypt = require('bcryptjs'); // Assuming this is imported globally or available in scope
  // const { Op } = require('sequelize'); // Assuming this is imported globally or available in scope
  
  const t = await sequelize.transaction();

  try {
    const { id } = req.params;
    const { managerPin, reason } = req.body;
    const voidedBy = req.user.id; // Assuming user is attached to req by auth middleware

    // ============================================
    // 1. VALIDATION - Transaction Existence
    // ============================================
    const transaction = await Transaction.findByPk(id, {
      // ... (Rest of transaction includes)
      include: [
        { 
          model: TransactionItem, 
          as: 'transactionsitems',
          include: [{
            model: Product,
            as: 'product',
            attributes: ['id', 'name', 'sku', 'stock']
          }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName']
        },
        {
          model: Register,
          as: 'register',
          attributes: ['id', 'code']
        }
      ],
      transaction: t
    });

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

    // ============================================
    // 2. VALIDATION - Transaction Status
    // ============================================
    if (transaction.status === 'voided') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Transaction is already voided'
      });
    }

    if (transaction.status !== 'completed') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Cannot void transaction with status: ${transaction.status}`
      });
    }

    // ============================================
    // 3. VALIDATION - Same Day Check (Business Rule)
    // ============================================
    const transactionDate = new Date(transaction.transactionDate);
    const today = new Date();

    // Reset hours to compare only dates
    transactionDate.setHours(0, 0, 0, 0);
    today.setHours(0, 0, 0, 0);

    if (transactionDate.getTime() !== today.getTime()) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Void operation only allowed on the same day as the transaction. Use refund instead.'
      });
    }

    // ============================================
    // 4. VALIDATION - Manager Authorization (REPLACED LOGIC)
    // ============================================
    if (!managerPin) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Manager PIN is required'
      });
    }

    // PIN Format Validation (from checkManagerPin)
    if (managerPin.toString().length !== 4 || !/^\d{4}$/.test(managerPin)) {
      await t.rollback();
      return res.status(400).json({ message: 'PIN must be exactly 4 digits' });
    }

   

    // Step 1: Get ALL active users who have a PIN set and are managers/admins
    // NOTE: I've kept the includes from your checkManagerPin function for completeness, 
    // but you might simplify them if you only need basic user info for the voiding log.
    const usersWithPin = await User.findAll({
      where: {
        isActive: true,
        pin: { [Op.ne]: null }, // Has a PIN
        // IMPORTANT: Add your role check here if only managers can void:
        role: { [Op.in]: ['manager', 'admin', 'super_admin'] } 
      },
      // Removed complex includes for simplicity, only fetching necessary data for authorization
      attributes: ['id', 'firstName', 'lastName', 'role', 'pin', 'password'],
      transaction: t, // Use the transaction
    });

    // Step 2: Loop through users and compare entered PIN with their hashed PIN
    let authenticatedManager = null;
    const pinToCompare = managerPin.toString();

    for (const user of usersWithPin) {
      // Assuming the manager PIN is stored in a 'managerPin' field, not 'pin' or 'password' as in your original void func
      const isMatch = await bcrypt.compare(pinToCompare, user.pin); 
      if (isMatch) {
        authenticatedManager = user;
        break;
      }
    }

    // Step 3: If no match found → invalid PIN
    if (!authenticatedManager) {
      await t.rollback();
      return res.status(401).json({
        success: false,
        message: 'Invalid manager PIN or insufficient privileges'
      });
    }

    // The 'manager' object needed for the Audit Log and success response is now 'authenticatedManager'
    const manager = authenticatedManager; 
    console.log('Manager PIN login successful for:', manager.firstName, 'Role:', manager.role);
    // END REPLACED LOGIC

    // ============================================
    // 5. VALIDATION - Payment Method Check
    // ============================================
    const payment = transaction.transactionpayment?.[0];

    if (payment?.paymentMethod === 'mobile_money') {
      // For M-Pesa, we might need to initiate a reversal instead
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'M-Pesa transactions require reversal through payment gateway. Use reverse operation instead.'
      });
    }

    // ============================================
    // 6. RESTORE INVENTORY
    // ============================================
    const items = transaction.transactionsitems || [];

    if (!items || items.length === 0) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'No items found in transaction'
      });
    }

    const inventoryUpdates = [];

    for (const item of items) {
      if (!item.product) {
        console.warn(`Product not found for item ${item.id}`);
        continue;
      }

      // Restore stock
      await Product.increment('stock', {
        by: item.quantity,
        where: { id: item.productId },
        transaction: t
      });

      // Track what was restored for audit
      inventoryUpdates.push({
        productId: item.productId,
        productName: item.product.name,
        sku: item.product.sku,
        quantityRestored: item.quantity,
        previousStock: item.product.stock,
        newStock: item.product.stock + item.quantity
      });
    }

    // ============================================
    // 7. UPDATE TRANSACTION STATUS
    // ============================================
    await transaction.update({
      status: 'voided',
      voidReason: reason || 'No reason provided',
      voidedBy: manager.id, // Use authenticatedManager.id
      voidedAt: new Date()
    }, { transaction: t });

    // ============================================
    // 8. UPDATE PAYMENT STATUS
    // ============================================
    if (payment) {
      await TransactionPayment.update(
        {
          status: 'voided',
          voidedAt: new Date()
        },
        {
          where: { id: payment.id },
          transaction: t
        }
      );
    }

    // ============================================
    // 9. CREATE AUDIT LOG
    // ============================================
    // Assuming you have an AuditLog model
    await AuditLog.create({
      action: 'VOID_TRANSACTION',
      entityType: 'Transaction',
      entityId: transaction.id,
      userId: voidedBy,
      details: JSON.stringify({
        transactionNumber: transaction.transactionNumber,
        originalAmount: transaction.totalAmount,
        reason: reason,
        inventoryRestored: inventoryUpdates,
        voidedBy: {
          id: manager.id, // Use authenticatedManager.id
          name: `${manager.firstName} ${manager.lastName}`
        },
        cashier: {
          id: transaction.cashier?.id,
          name: `${transaction.cashier?.firstName} ${transaction.cashier?.lastName}`
        },
        register: transaction.register?.code
      }),
      ipAddress: req.ip,
      userAgent: req.get('user-agent'),
      timestamp: new Date()
    }, { transaction: t });

    // ============================================
    // 10. UPDATE REGISTER TOTALS (if tracking daily totals)
    // ============================================
    if (transaction.registerId) {
      // Deduct from register's daily total
      await Register.decrement('currentCash', {
        by: parseFloat(transaction.totalAmount),
        where: { id: transaction.registerId },
        transaction: t
      });
    }

    // ============================================
    // 11. COMMIT TRANSACTION
    // ============================================
    await t.commit();

    // ============================================
    // 12. SEND SUCCESS RESPONSE
    // ============================================
    res.status(200).json({
      success: true,
      message: 'Transaction voided successfully',
      transaction: {
        id: transaction.id,
        transactionNumber: transaction.transactionNumber,
        status: 'voided',
        voidedAt: new Date(),
        voidedBy: {
          id: manager.id, // Use authenticatedManager.id
          name: `${manager.firstName} ${manager.lastName}`
        },
        originalAmount: transaction.totalAmount,
        itemsRestored: inventoryUpdates.length
      },
      inventoryUpdates
    });

  } catch (error) {
    // ... (Error handling remains the same)
    await t.rollback();

    console.error('Void transaction error:', {
      transactionId: req.params.id,
      error: error.message,
      stack: error.stack,
      userId: req.user?.id
    });

    // Send appropriate error response
    if (error.name === 'SequelizeValidationError') {
      return res.status(400).json({
        success: false,
        message: 'Validation error',
        errors: error.errors.map(e => e.message)
      });
    }

    if (error.name === 'SequelizeForeignKeyConstraintError') {
      return res.status(400).json({
        success: false,
        message: 'Database constraint error. Transaction may have related records.'
      });
    }

    res.status(500).json({
      success: false,
      message: 'Error voiding transaction. Please try again or contact support.',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// Get sales summary/statistics
exports.getSalesSummary = async (req, res) => {
  try {
    const { storeId, startDate, endDate, groupBy = 'day' } = req.query;

    const where = {
      status: 'completed'
    };

    if (storeId) where.storeId = storeId;

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

    // Total sales
    const totalSales = await Transaction.sum('totalAmount', { where });
    const totalTransactions = await Transaction.count({ where });
    
    // Average transaction value
    const avgTransactionValue = totalTransactions > 0 ? totalSales / totalTransactions : 0;

    // Sales by payment method
    const salesByPayment = await TransactionPayment.findAll({
      attributes: [
        'paymentMethod',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('amount')), 'total']
      ],
      include: [{
        model: Transaction,
        as: 'transaction',
        where,
        attributes: []
      }],
      group: ['paymentMethod'],
      raw: true
    });

    // Top selling products
    const topProducts = await TransactionItem.findAll({
      attributes: [
        'productId',
        [sequelize.fn('SUM', sequelize.col('quantity')), 'totalQuantity'],
        [sequelize.fn('SUM', sequelize.col('lineTotal')), 'totalRevenue']
      ],
      include: [
        {
          model: Transaction,
          as: 'transaction',
          where,
          attributes: []
        },
        {
          model: Product,
          as: 'product',
          attributes: ['id', 'name', 'sku']
        }
      ],
      group: ['productId', 'product.id', 'product.name', 'product.sku'],
      order: [[sequelize.fn('SUM', sequelize.col('quantity')), 'DESC']],
      limit: 10,
      raw: false
    });

    // Sales by cashier
    const salesByCashier = await Transaction.findAll({
      attributes: [
        'cashierId',
        [sequelize.fn('COUNT', sequelize.col('Transaction.id')), 'transactionCount'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalSales']
      ],
      where,
      include: [{
        model: User,
        as: 'cashier',
        attributes: ['id', 'firstName', 'lastName']
      }],
      group: ['cashierId', 'cashier.id', 'cashier.firstName', 'cashier.lastName'],
      order: [[sequelize.fn('SUM', sequelize.col('totalAmount')), 'DESC']],
      raw: false
    });

    res.status(200).json({
      success: true,
      summary: {
        totalSales: parseFloat(totalSales) || 0,
        totalTransactions,
        avgTransactionValue: parseFloat(avgTransactionValue) || 0,
        salesByPayment,
        topProducts,
        salesByCashier
      }
    });

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

// Get daily sales report
exports.getDailySalesReport = async (req, res) => {
  try {
    const { storeId, date } = req.query;
    
    const targetDate = date ? new Date(date) : new Date();
    const startOfDay = new Date(targetDate.setHours(0, 0, 0, 0));
    const endOfDay = new Date(targetDate.setHours(23, 59, 59, 999));

    const where = {
      transactionDate: {
        [Op.between]: [startOfDay, endOfDay]
      },
      status: 'completed'
    };

    if (storeId) where.storeId = storeId;

    const transactions = await Transaction.findAll({
      where,
      include: [
        {
          model: TransactionItem,
          as: 'items',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName']
        }
      ],
      order: [['transactionDate', 'DESC']]
    });

    const totalSales = transactions.reduce((sum, t) => sum + parseFloat(t.totalAmount), 0);
    const totalItems = transactions.reduce((sum, t) => 
      sum + t.items.reduce((itemSum, item) => itemSum + item.quantity, 0), 0
    );

    // Group by payment method
    const paymentSummary = transactions.reduce((acc, t) => {
      t.payments.forEach(payment => {
        if (!acc[payment.paymentMethod]) {
          acc[payment.paymentMethod] = { count: 0, amount: 0 };
        }
        acc[payment.paymentMethod].count += 1;
        acc[payment.paymentMethod].amount += parseFloat(payment.amount);
      });
      return acc;
    }, {});

    res.status(200).json({
      success: true,
      report: {
        date: targetDate,
        totalTransactions: transactions.length,
        totalSales,
        totalItems,
        paymentSummary,
        transactions
      }
    });

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

// controllers/productController.js - Additional method
exports.searchProductByBarcode = async (req, res) => {
  try {
    const { barcode } = req.params;

    const product = await Product.findOne({
      where: {
        sku: barcode,
        isActive: true
      }
    });

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

    if (product.stock <= 0) {
      return res.status(400).json({
        success: false,
        message: 'Product out of stock',
        product
      });
    }

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

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

// ==================== GET USER'S TRANSACTIONS (with date filters) ====================
exports.getUserTransactions = async (req, res) => {
  try {
    const { userId } = req.params;
    const { 
      registerId, 
      date,        // Single date (today or specific date)
      startDate,   // For date range
      endDate,     // For date range
      status,
      paymentMethod,
      page = 1, 
      limit = 50 
    } = req.query;

    const offset = (page - 1) * limit;
    const where = { cashierId: userId };

    // Filter by register if provided
    if (registerId) {
      where.registerId = registerId;
    }

    // Filter by status if provided
    if (status) {
      where.status = status;
    }

    // Date filtering logic
    if (date) {
      // Single date filter (e.g., today or specific date)
      const targetDate = new Date(date);
      const startOfDay = new Date(targetDate.setHours(0, 0, 0, 0));
      const endOfDay = new Date(targetDate.setHours(23, 59, 59, 999));
      
      where.transactionDate = {
        [Op.between]: [startOfDay, endOfDay]
      };
    } else if (startDate && endDate) {
      // Date range filter
      const start = new Date(startDate);
      start.setHours(0, 0, 0, 0);
      
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      
      where.transactionDate = {
        [Op.between]: [start, end]
      };
    } else if (startDate) {
      // Only start date provided
      const start = new Date(startDate);
      start.setHours(0, 0, 0, 0);
      
      where.transactionDate = {
        [Op.gte]: start
      };
    } else if (endDate) {
      // Only end date provided
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      
      where.transactionDate = {
        [Op.lte]: end
      };
    }

    const include = [
      {
        model: TransactionItem,
        as: 'transactionsitems',
        include: [{ 
          model: Product, 
          as: 'product',
          attributes: ['id', 'name', 'sku', 'unitPrice', 'imageUrl'] 
        }]
      },
      {
        model: TransactionPayment,
        as: 'transactionpayment'
      },
      {
        model: User,
        as: 'cashier',
        attributes: ['id', 'firstName', 'lastName', 'email']
      },
      {
        model: Store,
        as: 'store',
        attributes: ['id', 'name', 'address', 'phone']
      },
      {
        model: Register,
        as: 'register',
        attributes: ['id', 'code', 'status']
      }
    ];

    // Filter by payment method if specified
    if (paymentMethod) {
      include[1].where = { paymentMethod };
    }

    const { count, rows: transactions } = await Transaction.findAndCountAll({
      where,
      include,
      order: [['transactionDate', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset),
      distinct: true
    });

    // Calculate summary statistics
    const totalSales = transactions.reduce((sum, t) => sum + parseFloat(t.totalAmount || 0), 0);
    const totalItems = transactions.reduce((sum, t) => {
      return sum + (t.transactionsitems?.reduce((itemSum, item) => itemSum + item.quantity, 0) || 0);
    }, 0);

    res.status(200).json({
      success: true,
      transactions,
      summary: {
        totalTransactions: count,
        totalSales: totalSales.toFixed(2),
        totalItems,
        averageTransactionValue: count > 0 ? (totalSales / count).toFixed(2) : '0.00'
      },
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    });

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

// ==================== GET TODAY'S TRANSACTIONS FOR USER ====================
exports.getUserTodayTransactions = async (req, res) => {
  try {
    const { userId } = req.params;
    const { registerId, status, paymentMethod } = req.query;

    const today = new Date();
    const startOfDay = new Date(today.setHours(0, 0, 0, 0));
    const endOfDay = new Date(today.setHours(23, 59, 59, 999));

    const where = {
      cashierId: userId,
      transactionDate: {
        [Op.between]: [startOfDay, endOfDay]
      }
    };

    if (registerId) where.registerId = registerId;
    if (status) where.status = status;

    const include = [
      {
        model: TransactionItem,
        as: 'transactionsitems',
        include: [{ 
          model: Product, 
          as: 'product',
          attributes: ['id', 'name', 'sku', 'unitPrice', 'imageUrl'] 
        }]
      },
      {
        model: TransactionPayment,
        as: 'transactionpayment'
      },
      {
        model: Register,
        as: 'register',
        attributes: ['id', 'code', 'status']
      }
    ];

    if (paymentMethod) {
      include[1].where = { paymentMethod };
    }

    const transactions = await Transaction.findAll({
      where,
      include,
      order: [['transactionDate', 'DESC']]
    });

    // Calculate today's summary
    const totalSales = transactions.reduce((sum, t) => sum + parseFloat(t.totalAmount || 0), 0);
    const totalItems = transactions.reduce((sum, t) => {
      return sum + (t.transactionsitems?.reduce((itemSum, item) => itemSum + item.quantity, 0) || 0);
    }, 0);

    // Payment method breakdown
    const paymentBreakdown = transactions.reduce((acc, t) => {
      t.transactionpayment?.forEach(payment => {
        const method = payment.paymentMethod;
        if (!acc[method]) {
          acc[method] = { count: 0, total: 0 };
        }
        acc[method].count += 1;
        acc[method].total += parseFloat(payment.amount || 0);
      });
      return acc;
    }, {});

    res.status(200).json({
      success: true,
      date: startOfDay.toISOString().split('T')[0],
      transactions,
      summary: {
        totalTransactions: transactions.length,
        totalSales: totalSales.toFixed(2),
        totalItems,
        averageTransactionValue: transactions.length > 0 ? (totalSales / transactions.length).toFixed(2) : '0.00',
        paymentBreakdown
      }
    });

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

// ==================== GET REGISTER TRANSACTIONS ====================
exports.getRegisterTransactions = async (req, res) => {
  try {
    const { registerId } = req.params;
    const { 
      date,
      startDate,
      endDate,
      status,
      cashierId,
      page = 1,
      limit = 50
    } = req.query;

    const offset = (page - 1) * limit;
    const where = { registerId };

    if (status) where.status = status;
    if (cashierId) where.cashierId = cashierId;

    // Date filtering
    if (date) {
      const targetDate = new Date(date);
      const startOfDay = new Date(targetDate.setHours(0, 0, 0, 0));
      const endOfDay = new Date(targetDate.setHours(23, 59, 59, 999));
      
      where.transactionDate = {
        [Op.between]: [startOfDay, endOfDay]
      };
    } else if (startDate && endDate) {
      const start = new Date(startDate);
      start.setHours(0, 0, 0, 0);
      
      const end = new Date(endDate);
      end.setHours(23, 59, 59, 999);
      
      where.transactionDate = {
        [Op.between]: [start, end]
      };
    }

    const { count, rows: transactions } = await Transaction.findAndCountAll({
      where,
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName', 'email']
        }
      ],
      order: [['transactionDate', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset),
      distinct: true
    });

    const totalSales = transactions.reduce((sum, t) => sum + parseFloat(t.totalAmount || 0), 0);

    res.status(200).json({
      success: true,
      transactions,
      summary: {
        totalTransactions: count,
        totalSales: totalSales.toFixed(2)
      },
      pagination: {
        total: count,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(count / limit)
      }
    });

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


// Add this NEW endpoint to your transactionController.js

// Get recent transactions (UPDATED - includes all payment methods and statuses)
// exports.getRecentTransactions = async (req, res) => {
//   try {
//     const { limit = 10, storeId } = req.query;

//     const where = {};
//     if (storeId) where.storeId = storeId;

//     const transactions = await Transaction.findAll({
//       where,
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ 
//             model: Product, 
//             as: 'product',
//             attributes: ['id', 'name', 'sku', 'unitPrice'] 
//           }]
//         },
//         {
//           model: TransactionPayment,
//           as: 'transactionpayment',
//           // Include ALL payment statuses (pending, completed, failed)
//           // Remove any default status filter
//         },
//         {
//           model: User,
//           as: 'cashier',
//           attributes: ['id', 'firstName', 'lastName', 'email']
//         }
//       ],
//       order: [['transactionDate', 'DESC']],
//       limit: parseInt(limit),
//       raw: false
//     });

//     // Map transactions to match dashboard expectations
//     const formattedTransactions = transactions.map(txn => {
//       const payment = txn.transactionpayment?.[0];
      
//       return {
//         id: txn.id,
//         transactionNumber: txn.transactionNumber,
//         total_amount: txn.totalAmount,
//         created_at: txn.transactionDate,
//         items_count: txn.transactionsitems?.length || 0,
//         status: txn.status,
//         payment_method: payment?.paymentMethod || 'cash',
//         payment_status: payment?.status,
//         // Include the full relationship for filtering
//         transactionpayment: txn.transactionpayment || [],
//         transactionsitems: txn.transactionsitems || []
//       };
//     });

//     res.status(200).json({
//       success: true,
//       transactions: formattedTransactions
//     });

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


// UPDATED getRecentTransactions endpoint in transactionController.js

// exports.getRecentTransactions = async (req, res) => {
//   try {
//     const { limit = 10, storeId } = req.query;

//     const where = {};
//     if (storeId) where.storeId = storeId;

//     const transactions = await Transaction.findAll({
//       where,
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ 
//             model: Product, 
//             as: 'product',
//             attributes: ['id', 'name', 'sku', 'unitPrice'] 
//           }]
//         },
//         {
//           model: TransactionPayment,
//           as: 'transactionpayment',
//           // IMPORTANT: Don't filter by status - include ALL payments
//           required: false // This ensures transactions without payments are still included
//         },
//         {
//           model: User,
//           as: 'cashier',
//           attributes: ['id', 'firstName', 'lastName', 'email']
//         }
//       ],
//       order: [['transactionDate', 'DESC']],
//       limit: parseInt(limit),
//       raw: false
//     });

//     // Map transactions with proper payment method handling
//     const formattedTransactions = transactions.map(txn => {
//       // Get all payments for this transaction
//       const payments = txn.transactionpayment || [];
      
//       // Find the primary payment method (first one or largest amount)
//       let primaryPayment = null;
//       if (payments.length > 0) {
//         primaryPayment = payments.reduce((prev, current) => 
//           (parseFloat(current.amount) > parseFloat(prev.amount)) ? current : prev
//         );
//       }
      
//       return {
//         id: txn.id,
//         transactionNumber: txn.transactionNumber,
//         total_amount: txn.totalAmount,
//         created_at: txn.transactionDate,
//         items_count: txn.transactionsitems?.length || 0,
//         status: txn.status,
//         // Use the primary payment method, default to 'cash' if no payment found
//         payment_method: primaryPayment?.paymentMethod || 'cash',
//         payment_status: primaryPayment?.status || 'completed',
//         // Include ALL payments for frontend filtering
//         transactionpayment: payments.map(p => ({
//           id: p.id,
//           paymentMethod: p.paymentMethod,
//           amount: p.amount,
//           status: p.status,
//           referenceNumber: p.referenceNumber
//         })),
//         transactionsitems: txn.transactionsitems || []
//       };
//     });

//     res.status(200).json({
//       success: true,
//       transactions: formattedTransactions
//     });

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



exports.getRecentTransactions = async (req, res) => {
  try {
    const { limit = 10, storeId } = req.query;

    const where = {};
    if (storeId) where.storeId = storeId;

    console.log('Fetching recent transactions...'); // Debug log

    const transactions = await Transaction.findAll({
      where,
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ 
            model: Product, 
            as: 'product',
            attributes: ['id', 'name', 'sku', 'unitPrice'] 
          }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment',
          required: false // Include transactions even without payments
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName', 'email']
        }
      ],
      order: [['transactionDate', 'DESC']],
      limit: parseInt(limit),
      raw: false
    });

    console.log(`Found ${transactions.length} transactions`); // Debug log

    // Map transactions with explicit payment method handling
    const formattedTransactions = transactions.map(txn => {
      const payments = txn.transactionpayment || [];
      
      console.log(`Transaction ${txn.id} has ${payments.length} payments:`, 
        payments.map(p => `${p.paymentMethod} - ${p.amount}`)); // Debug log
      
      // Get primary payment (largest amount)
      let primaryPaymentMethod = 'cash';
      let primaryPaymentStatus = 'completed';
      
      if (payments.length > 0) {
        const primaryPayment = payments.reduce((prev, current) => 
          (parseFloat(current.amount || 0) > parseFloat(prev.amount || 0)) ? current : prev
        );
        primaryPaymentMethod = primaryPayment.paymentMethod;
        primaryPaymentStatus = primaryPayment.status;
      }
      
      return {
        id: txn.id,
        transactionNumber: txn.transactionNumber,
        total_amount: parseFloat(txn.totalAmount),
        created_at: txn.transactionDate,
        items_count: txn.transactionsitems?.length || 0,
        status: txn.status,
        payment_method: primaryPaymentMethod, // Direct field for easy access
        payment_status: primaryPaymentStatus,
        // Keep full payment array for filtering
        transactionpayment: payments.map(p => ({
          id: p.id,
          paymentMethod: p.paymentMethod,
          amount: parseFloat(p.amount),
          status: p.status,
          referenceNumber: p.referenceNumber
        }))
      };
    });

    console.log('Formatted transactions sample:', 
      formattedTransactions.slice(0, 2).map(t => ({
        id: t.id,
        payment_method: t.payment_method,
        payments: t.transactionpayment
      }))); // Debug log

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

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






// UPDATED getPaymentStatistics endpoint
exports.getPaymentStatistics = async (req, res) => {
  try {
    const { storeId, startDate, endDate } = req.query;

    const where = {};
    if (storeId) where.storeId = storeId;

    // Default to today if no dates provided
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    const endOfDay = new Date();
    endOfDay.setHours(23, 59, 59, 999);

    // Build date filter
    if (startDate && endDate) {
      where.transactionDate = {
        [Op.between]: [new Date(startDate), new Date(endDate)]
      };
    } else if (startDate) {
      where.transactionDate = {
        [Op.gte]: new Date(startDate)
      };
    } else if (endDate) {
      where.transactionDate = {
        [Op.lte]: new Date(endDate)
      };
    } else {
      // Default to today
      where.transactionDate = {
        [Op.between]: [today, endOfDay]
      };
    }

    // Get ALL transactions with their payment details (including pending)
    const transactions = await Transaction.findAll({
      where,
      include: [
        {
          model: TransactionPayment,
          as: 'transactionpayment',
          attributes: ['paymentMethod', 'amount', 'status'],
          required: false // Include transactions even if they have no payments yet
        }
      ],
      raw: false
    });

    // Calculate statistics by payment method
    const paymentStats = {
      cash: { count: 0, total: 0, completed: 0, pending: 0 },
      card: { count: 0, total: 0, completed: 0, pending: 0 },
      mobile_money: { count: 0, total: 0, completed: 0, pending: 0 },
      mpesa: { count: 0, total: 0, completed: 0, pending: 0 }
    };

    transactions.forEach(txn => {
      if (txn.transactionpayment && Array.isArray(txn.transactionpayment)) {
        txn.transactionpayment.forEach(payment => {
          const method = payment.paymentMethod || 'cash';
          
          // Initialize method if it doesn't exist
          if (!paymentStats[method]) {
            paymentStats[method] = { count: 0, total: 0, completed: 0, pending: 0 };
          }
          
          const amount = parseFloat(payment.amount || 0);
          
          paymentStats[method].count += 1;
          paymentStats[method].total += amount;
          
          // Track status-specific totals
          if (payment.status === 'completed') {
            paymentStats[method].completed += amount;
          } else if (payment.status === 'pending') {
            paymentStats[method].pending += amount;
          }
        });
      }
    });

    res.status(200).json({
      success: true,
      statistics: paymentStats,
      summary: {
        totalTransactions: transactions.length,
        period: startDate && endDate ? 'custom' : 'today'
      }
    });

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

// ==================== REVERSE/REFUND TRANSACTION ====================
exports.reverseTransactionOne = async (req, res) => {
  const t = await sequelize.transaction();

  try {
    const { id } = req.params;
    const { reason, reversedBy, refundMethod, partialRefund, refundAmount } = req.body;

    // Validate required fields
    if (!reason || !reversedBy) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Reason and reversedBy are required'
      });
    }

    // Fetch transaction with all related data
    const transaction = await Transaction.findByPk(id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ],
      transaction: t
    });

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

    // Check if transaction is already reversed or voided
    if (transaction.status === 'reversed' || transaction.status === 'voided') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Transaction already ${transaction.status}`
      });
    }

    // Only allow reversal of completed or pending transactions
    if (!['completed', 'pending'].includes(transaction.status)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Can only reverse completed or pending transactions'
      });
    }

    // Calculate refund amount
    const totalRefundAmount = partialRefund 
      ? parseFloat(refundAmount) 
      : parseFloat(transaction.totalAmount);

    if (partialRefund && totalRefundAmount > parseFloat(transaction.totalAmount)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Refund amount cannot exceed transaction total'
      });
    }

    // Restore stock for all items (full refund) or proportionally (partial refund)
    for (const item of transaction.transactionsitems) {
      const refundQuantity = partialRefund
        ? Math.floor(item.quantity * (totalRefundAmount / parseFloat(transaction.totalAmount)))
        : item.quantity;

      await Product.increment('stock', {
        by: refundQuantity,
        where: { id: item.productId },
        transaction: t
      });

      // Update transaction item to track refunded quantity
      await TransactionItem.update(
        { 
          refundedQuantity: refundQuantity,
          refundedAmount: item.lineTotal * (refundQuantity / item.quantity)
        },
        { 
          where: { id: item.id },
          transaction: t 
        }
      );
    }

    // Update payment records to reflect refund
    for (const payment of transaction.transactionpayment) {
      const refundPaymentAmount = partialRefund
        ? payment.amount * (totalRefundAmount / parseFloat(transaction.totalAmount))
        : payment.amount;

      await TransactionPayment.update(
        {
          status: 'refunded',
          refundedAmount: refundPaymentAmount,
          refundDate: new Date(),
          refundMethod: refundMethod || payment.paymentMethod
        },
        {
          where: { id: payment.id },
          transaction: t
        }
      );
    }

    // Update transaction status and add refund details
    await transaction.update({
      status: 'reversed',
      refundReason: reason,
      refundedBy: reversedBy,
      refundedAt: new Date(),
      refundAmount: totalRefundAmount,
      isPartialRefund: partialRefund || false
    }, { transaction: t });

    // Update DailySales - subtract the refunded amounts
    const transactionDate = new Date(transaction.transactionDate);
    const salesDate = new Date(
      transactionDate.getFullYear(),
      transactionDate.getMonth(),
      transactionDate.getDate()
    );

    const dailySales = await DailySales.findOne({
      where: {
        storeId: transaction.storeId,
        registerId: transaction.registerId,
        salesDate
      },
      transaction: t
    });

    if (dailySales) {
      // Calculate refund breakdown by payment method
      const refundBreakdown = {
        cashRefund: 0,
        cardRefund: 0,
        mobileRefund: 0
      };

      transaction.transactionpayment.forEach(payment => {
        const refundAmt = partialRefund
          ? payment.amount * (totalRefundAmount / parseFloat(transaction.totalAmount))
          : payment.amount;

        if (payment.paymentMethod === 'cash') {
          refundBreakdown.cashRefund += refundAmt;
        } else if (payment.paymentMethod === 'card') {
          refundBreakdown.cardRefund += refundAmt;
        } else if (['mobile_money', 'mpesa'].includes(payment.paymentMethod)) {
          refundBreakdown.mobileRefund += refundAmt;
        }
      });

      const totalItems = transaction.transactionsitems.reduce(
        (sum, item) => sum + (item.refundedQuantity || item.quantity), 
        0
      );

      await dailySales.decrement({
        totalTransactions: partialRefund ? 0 : 1,
        totalItems: totalItems,
        grossSales: partialRefund ? totalRefundAmount : transaction.subtotal,
        discounts: partialRefund 
          ? transaction.discountAmount * (totalRefundAmount / parseFloat(transaction.totalAmount))
          : transaction.discountAmount,
        taxes: partialRefund
          ? transaction.taxAmount * (totalRefundAmount / parseFloat(transaction.totalAmount))
          : transaction.taxAmount,
        netSales: totalRefundAmount,
        cashSales: refundBreakdown.cashRefund,
        cardSales: refundBreakdown.cardRefund,
        mobilePayments: refundBreakdown.mobileRefund
      }, { transaction: t });

      // Increment refunds counter
      await dailySales.increment({
        refunds: totalRefundAmount
      }, { transaction: t });
    }

    await t.commit();

    // Fetch updated transaction
    const updatedTransaction = await Transaction.findByPk(id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ]
    });

    res.status(200).json({
      success: true,
      message: partialRefund 
        ? 'Transaction partially refunded successfully' 
        : 'Transaction reversed successfully',
      transaction: updatedTransaction,
      refundAmount: totalRefundAmount
    });

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


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

  try {
    const { id } = req.params;
    const { managerPin, reason, partialRefund, refundAmount } = req.body;
    const reversedBy = req.user.id; // From auth middleware

    // ============================================
    // 1. VALIDATION - Required Fields
    // ============================================
    if (!managerPin) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Manager PIN is required'
      });
    }

    if (!reason || reason.trim().length < 10) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Reason is required (minimum 10 characters)'
      });
    }

    // ============================================
    // 2. FETCH TRANSACTION WITH ALL RELATIONS
    // ============================================
    const transaction = await Transaction.findByPk(id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ 
            model: Product, 
            as: 'product',
            attributes: ['id', 'name', 'sku', 'stock', 'unitPrice']
          }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: Register,
          as: 'register',
          attributes: ['id', 'code']
        },
        {
          model: Store,
          as: 'store',
          attributes: ['id', 'name']
        }
      ],
      transaction: t
    });

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

    // ============================================
    // 3. VALIDATION - Transaction Status
    // ============================================
    if (transaction.status === 'reversed') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Transaction is already reversed',
        reversedAt: transaction.refundedAt,
        reversedBy: transaction.refundedBy
      });
    }

    if (transaction.status === 'voided') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Cannot reverse a voided transaction'
      });
    }

    if (!['completed', 'pending'].includes(transaction.status)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Cannot reverse transaction with status: ${transaction.status}`
      });
    }

    // ============================================
    // 4. VALIDATION - Manager Authorization
    // ============================================
    const manager = await User.findOne({
      where: { 
        id: reversedBy,
        role: ['manager', 'admin', 'super_admin']
      },
      transaction: t
    });

    if (!manager) {
      await t.rollback();
      return res.status(403).json({ 
        success: false, 
        message: 'Unauthorized: Manager privileges required' 
      });
    }

    const bcrypt = require('bcryptjs');
    const isPinValid = await bcrypt.compare(managerPin, manager.pin || manager.password);
    
    if (!isPinValid) {
      await t.rollback();
      return res.status(401).json({ 
        success: false, 
        message: 'Invalid manager PIN' 
      });
    }

    // ============================================
    // 5. VALIDATION - Refund Amount
    // ============================================
    const transactionTotal = parseFloat(transaction.totalAmount);
    const totalRefundAmount = partialRefund 
      ? parseFloat(refundAmount) 
      : transactionTotal;

    if (isNaN(totalRefundAmount) || totalRefundAmount <= 0) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid refund amount'
      });
    }

    if (totalRefundAmount > transactionTotal) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Refund amount (${totalRefundAmount}) cannot exceed transaction total (${transactionTotal})`
      });
    }

    // Validate minimum refund amount (prevent tiny refunds)
    if (partialRefund && totalRefundAmount < 10) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Minimum refund amount is KES 10'
      });
    }

    // ============================================
    // 6. CHECK FOR EXISTING REFUNDS
    // ============================================
    const previousRefundAmount = parseFloat(transaction.refundAmount || 0);
    const remainingRefundable = transactionTotal - previousRefundAmount;

    if (previousRefundAmount > 0) {
      if (totalRefundAmount > remainingRefundable) {
        await t.rollback();
        return res.status(400).json({
          success: false,
          message: `Only KES ${remainingRefundable.toFixed(2)} remaining for refund`,
          previousRefund: previousRefundAmount,
          transactionTotal: transactionTotal
        });
      }
    }

    // ============================================
    // 7. PAYMENT METHOD VALIDATION & M-PESA REVERSAL
    // ============================================
    const payment = transaction.transactionpayment?.[0];
    let mpesaReversalSuccess = false;
    let mpesaReversalDetails = null;

    if (payment?.paymentMethod === 'mobile_money' && payment.referenceNumber) {
      // Initiate M-Pesa reversal
      try {
        const mpesaResponse = await axios.post(
          `${process.env.BACKEND_URL || 'http://localhost:5000'}/api/mpesa/reverse`,
          {
            transactionID: payment.referenceNumber,
            amount: totalRefundAmount,
            occasion: `Reversal: ${reason.substring(0, 50)}`,
            remarks: `Transaction ${transaction.transactionNumber} reversal`
          },
          {
            headers: { Authorization: req.headers.authorization }
          }
        );

        if (mpesaResponse.data.success) {
          mpesaReversalSuccess = true;
          mpesaReversalDetails = mpesaResponse.data.data;
        } else {
          // M-Pesa reversal failed - decide whether to continue or abort
          await t.rollback();
          return res.status(400).json({
            success: false,
            message: 'M-Pesa reversal failed. Please try again or contact support.',
            mpesaError: mpesaResponse.data.message
          });
        }
      } catch (mpesaError) {
        console.error('M-Pesa reversal error:', mpesaError);
        await t.rollback();
        return res.status(500).json({
          success: false,
          message: 'Failed to process M-Pesa reversal',
          error: mpesaError.message
        });
      }
    }

    // ============================================
    // 8. RESTORE INVENTORY
    // ============================================
    const items = transaction.transactionsitems || [];
    
    if (!items || items.length === 0) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'No items found in transaction'
      });
    }

    const inventoryUpdates = [];
    const refundRatio = totalRefundAmount / transactionTotal;

    for (const item of items) {
      if (!item.product) {
        console.warn(`Product not found for item ${item.id}`);
        continue;
      }

      // Calculate refund quantity (proportional for partial refunds)
      const refundQuantity = partialRefund
        ? Math.floor(item.quantity * refundRatio)
        : item.quantity;

      // Don't restore if quantity is 0
      if (refundQuantity === 0) continue;

      // Calculate refunded amount for this item
      const itemRefundAmount = parseFloat(item.lineTotal) * (refundQuantity / item.quantity);

      // Restore stock
      await Product.increment('stock', {
        by: refundQuantity,
        where: { id: item.productId },
        transaction: t
      });

      // Update transaction item with refund details
      const previouslyRefunded = parseFloat(item.refundedAmount || 0);
      const previousQuantityRefunded = parseInt(item.refundedQuantity || 0);

      await TransactionItem.update(
        { 
          refundedQuantity: previousQuantityRefunded + refundQuantity,
          refundedAmount: previouslyRefunded + itemRefundAmount
        },
        { 
          where: { id: item.id },
          transaction: t 
        }
      );

      inventoryUpdates.push({
        productId: item.productId,
        productName: item.product.name,
        sku: item.product.sku,
        quantityRefunded: refundQuantity,
        amountRefunded: itemRefundAmount,
        previousStock: item.product.stock,
        newStock: item.product.stock + refundQuantity
      });
    }

    // ============================================
    // 9. UPDATE PAYMENT RECORDS
    // ============================================
    const refundMethod = payment?.paymentMethod || 'cash';
    
    for (const paymentRecord of transaction.transactionpayment) {
      const paymentRefundAmount = parseFloat(paymentRecord.amount) * refundRatio;
      const previouslyRefunded = parseFloat(paymentRecord.refundedAmount || 0);

      await TransactionPayment.update(
        {
          status: totalRefundAmount >= transactionTotal ? 'refunded' : 'partial_refund',
          refundedAmount: previouslyRefunded + paymentRefundAmount,
          refundDate: new Date(),
          refundMethod: refundMethod,
          mpesaReversalDetails: mpesaReversalSuccess ? JSON.stringify(mpesaReversalDetails) : null
        },
        {
          where: { id: paymentRecord.id },
          transaction: t
        }
      );
    }

    // ============================================
    // 10. UPDATE TRANSACTION STATUS
    // ============================================
    const newRefundTotal = previousRefundAmount + totalRefundAmount;
    const isFullyRefunded = newRefundTotal >= transactionTotal;

    await transaction.update({
      status: isFullyRefunded ? 'reversed' : 'partial_refund',
      refundReason: reason,
      refundedBy: reversedBy,
      refundedAt: new Date(),
      refundAmount: newRefundTotal,
      isPartialRefund: !isFullyRefunded
    }, { transaction: t });

    // ============================================
    // 11. UPDATE DAILY SALES
    // ============================================
    const transactionDate = new Date(transaction.transactionDate);
    const salesDate = new Date(
      transactionDate.getFullYear(),
      transactionDate.getMonth(),
      transactionDate.getDate()
    );

    const dailySales = await DailySales.findOne({
      where: {
        storeId: transaction.storeId,
        registerId: transaction.registerId,
        salesDate
      },
      transaction: t
    });

    if (dailySales) {
      // Calculate refund breakdown by payment method
      const refundBreakdown = {
        cashRefund: 0,
        cardRefund: 0,
        mobileRefund: 0
      };

      transaction.transactionpayment.forEach(paymentRecord => {
        const refundAmt = parseFloat(paymentRecord.amount) * refundRatio;

        if (paymentRecord.paymentMethod === 'cash') {
          refundBreakdown.cashRefund += refundAmt;
        } else if (paymentRecord.paymentMethod === 'card') {
          refundBreakdown.cardRefund += refundAmt;
        } else if (['mobile_money', 'mpesa'].includes(paymentRecord.paymentMethod)) {
          refundBreakdown.mobileRefund += refundAmt;
        }
      });

      const totalItemsRefunded = inventoryUpdates.reduce(
        (sum, item) => sum + item.quantityRefunded, 
        0
      );

      // Calculate proportional amounts
      const proportionalDiscount = parseFloat(transaction.discountAmount || 0) * refundRatio;
      const proportionalTax = parseFloat(transaction.taxAmount || 0) * refundRatio;
      const proportionalGross = parseFloat(transaction.subtotal || 0) * refundRatio;

      await dailySales.decrement({
        totalTransactions: isFullyRefunded ? 1 : 0,
        totalItems: totalItemsRefunded,
        grossSales: proportionalGross,
        discounts: proportionalDiscount,
        taxes: proportionalTax,
        netSales: totalRefundAmount,
        cashSales: refundBreakdown.cashRefund,
        cardSales: refundBreakdown.cardRefund,
        mobilePayments: refundBreakdown.mobileRefund
      }, { transaction: t });

      // Increment refunds counter
      await dailySales.increment({
        refunds: totalRefundAmount,
        refundCount: 1
      }, { transaction: t });
    }

    // ============================================
    // 12. UPDATE REGISTER TOTALS
    // ============================================
    if (transaction.registerId) {
      await Register.decrement('dailyTotal', {
        by: totalRefundAmount,
        where: { id: transaction.registerId },
        transaction: t
      });
    }

    // ============================================
    // 13. CREATE AUDIT LOG
    // ============================================
    await AuditLog.create({
      action: isFullyRefunded ? 'REVERSE_TRANSACTION' : 'PARTIAL_REFUND',
      entityType: 'Transaction',
      entityId: transaction.id,
      userId: reversedBy,
      details: JSON.stringify({
        transactionNumber: transaction.transactionNumber,
        originalAmount: transactionTotal,
        refundAmount: totalRefundAmount,
        totalRefunded: newRefundTotal,
        isPartialRefund: !isFullyRefunded,
        reason: reason,
        inventoryRestored: inventoryUpdates,
        paymentMethod: refundMethod,
        mpesaReversal: mpesaReversalSuccess ? mpesaReversalDetails : null,
        reversedBy: {
          id: manager.id,
          name: `${manager.firstName} ${manager.lastName}`
        },
        cashier: {
          id: transaction.cashier?.id,
          name: `${transaction.cashier?.firstName} ${transaction.cashier?.lastName}`
        },
        register: transaction.register?.code,
        store: transaction.store?.name
      }),
      ipAddress: req.ip,
      userAgent: req.get('user-agent'),
      timestamp: new Date()
    }, { transaction: t });

    // ============================================
    // 14. COMMIT TRANSACTION
    // ============================================
    await t.commit();

    // ============================================
    // 15. FETCH UPDATED TRANSACTION
    // ============================================
    const updatedTransaction = await Transaction.findByPk(id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ]
    });

    // ============================================
    // 16. SEND SUCCESS RESPONSE
    // ============================================
    res.status(200).json({
      success: true,
      message: isFullyRefunded 
        ? 'Transaction reversed successfully' 
        : 'Transaction partially refunded successfully',
      transaction: updatedTransaction,
      refundDetails: {
        refundAmount: totalRefundAmount,
        totalRefunded: newRefundTotal,
        remainingRefundable: transactionTotal - newRefundTotal,
        isFullyRefunded: isFullyRefunded,
        refundMethod: refundMethod,
        itemsRestored: inventoryUpdates.length,
        mpesaReversal: mpesaReversalSuccess ? {
          success: true,
          details: mpesaReversalDetails
        } : null
      },
      inventoryUpdates
    });

  } catch (error) {
    // ============================================
    // 17. ERROR HANDLING
    // ============================================
    if (!t.finished) {
      await t.rollback();
    }
    
    console.error('Reverse transaction error:', {
      transactionId: req.params.id,
      error: error.message,
      stack: error.stack,
      userId: req.user?.id
    });

    // Send appropriate error response
    if (error.name === 'SequelizeValidationError') {
      return res.status(400).json({
        success: false,
        message: 'Validation error',
        errors: error.errors.map(e => e.message)
      });
    }

    if (error.name === 'SequelizeForeignKeyConstraintError') {
      return res.status(400).json({
        success: false,
        message: 'Database constraint error. Transaction may have related records.'
      });
    }

    res.status(500).json({
      success: false,
      message: 'Error reversing transaction. Please try again or contact support.',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};





// ==================== SUSPEND TRANSACTION (Park/Hold) ====================
// exports.suspendTransaction = async (req, res) => {
//   const t = await sequelize.transaction();

//   try {
//     const { storeId, registerId, cashierId, items, note } = req.body;

//     if (!storeId || !registerId || !cashierId || !items || !items.length) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Missing required fields'
//       });
//     }

//     // Calculate totals
//     let subtotal = 0;
//     const itemsWithCalculations = [];

//     for (const item of items) {
//       const product = await Product.findByPk(item.productId);
      
//       if (!product) {
//         await t.rollback();
//         return res.status(404).json({
//           success: false,
//           message: `Product with ID ${item.productId} not found`
//         });
//       }

//       const lineTotal = item.unitPrice * item.quantity;
//       subtotal += lineTotal;

//       itemsWithCalculations.push({
//         productId: item.productId,
//         quantity: item.quantity,
//         unitPrice: item.unitPrice,
//         lineTotal
//       });
//     }

//     // Generate suspended transaction number
//     const date = new Date();
//     const suspendNumber = `SUSP${date.getTime()}`;

//     // Create suspended transaction
//     const transaction = await Transaction.create({
//       transactionNumber: suspendNumber,
//       storeId,
//       registerId,
//       cashierId,
//       transactionDate: new Date(),
//       subtotal,
//       discountAmount: 0,
//       taxAmount: subtotal * 0.16,
//       totalAmount: subtotal * 1.16,
//       status: 'suspended',
//       suspendNote: note || 'Transaction parked for later'
//     }, { transaction: t });

//     // Create transaction items
//     for (const item of itemsWithCalculations) {
//       await TransactionItem.create({
//         transactionId: transaction.id,
//         productId: item.productId,
//         quantity: item.quantity,
//         unitPrice: item.unitPrice,
//         lineTotal: item.lineTotal,
//         taxAmount: item.lineTotal * 0.16
//       }, { transaction: t });
//     }

//     await t.commit();

//     const suspendedTransaction = await Transaction.findByPk(transaction.id, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         }
//       ]
//     });

//     res.status(201).json({
//       success: true,
//       message: 'Transaction suspended successfully',
//       transaction: suspendedTransaction
//     });

//   } catch (error) {
//     if (!t.finished) {
//       await t.rollback();
//     }
//     console.error('Suspend transaction error:', error);
//     res.status(500).json({
//       success: false,
//       message: 'Error suspending transaction',
//       error: error.message
//     });
//   }
// };


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

//   try {
//     const { transactionId } = req.params; // ✅ GET transaction ID from URL
//     const { storeId, registerId, cashierId, items, note } = req.body;

//     if (!storeId || !registerId || !cashierId || !items || !items.length) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Missing required fields: storeId, registerId, cashierId, items'
//       });
//     }

//     // ✅ FIND EXISTING TRANSACTION
//     const existingTransaction = await Transaction.findByPk(transactionId, { transaction: t });
//     if (!existingTransaction) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Transaction not found'
//       });
//     }

//     // ✅ VERIFY IT'S OPEN
//     if (existingTransaction.status !== 'open') {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Only open transactions can be suspended'
//       });
//     }

//     // Calculate totals from provided items
//     let subtotal = 0;
//     const itemsWithCalculations = [];

//     for (const item of items) {
//       const product = await Product.findByPk(item.productId, { transaction: t });
      
//       if (!product) {
//         await t.rollback();
//         return res.status(404).json({
//           success: false,
//           message: `Product with ID ${item.productId} not found`
//         });
//       }

//       const lineTotal = item.unitPrice * item.quantity;
//       subtotal += lineTotal;

//       itemsWithCalculations.push({
//         productId: item.productId,
//         quantity: item.quantity,
//         unitPrice: item.unitPrice,
//         lineTotal
//       });
//     }

//     // ✅ CREATE NEW SUSPENDED TRANSACTION
//     const date = new Date();
//     const suspendNumber = `SUSP${date.getTime()}`;

//     const suspendedTransaction = await Transaction.create({
//       transactionNumber: suspendNumber,
//       storeId,
//       registerId,
//       cashierId,
//       transactionDate: new Date(),
//       subtotal,
//       discountAmount: 0,
//       taxAmount: subtotal * 0.16,
//       totalAmount: subtotal * 1.16,
//       status: 'suspended',
//       suspendNote: note || 'Transaction parked for later'
//     }, { transaction: t });

//     // ✅ CREATE SUSPENDED ITEMS
//     for (const item of itemsWithCalculations) {
//       await TransactionItem.create({
//         transactionId: suspendedTransaction.id,
//         productId: item.productId,
//         quantity: item.quantity,
//         unitPrice: item.unitPrice,
//         lineTotal: item.lineTotal,
//         taxAmount: item.lineTotal * 0.16
//       }, { transaction: t });
//     }

//     // ✅ OPTIONAL: Mark original transaction as suspended or voided
//     await existingTransaction.update({ 
//       status: 'suspended', 
//       suspendNote: note || 'Moved to suspended transaction',
//       suspendedTransactionId: suspendedTransaction.id 
//     }, { transaction: t });

//     await t.commit();

//     // ✅ RETURN NEW SUSPENDED TRANSACTION WITH ITEMS
//     const fullSuspendedTransaction = await Transaction.findByPk(suspendedTransaction.id, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         }
//       ]
//     });

//     res.status(201).json({
//       success: true,
//       message: 'Transaction suspended successfully',
//       transaction: fullSuspendedTransaction
//     });

//   } catch (error) {
//     if (!t.finished) {
//       await t.rollback();
//     }
//     console.error('Suspend transaction error:', error);
//     res.status(500).json({
//       success: false,
//       message: 'Error suspending transaction',
//       error: error.message
//     });
//   }
// };

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

  try {
    // ✅ EXTRACT PARAMETERS
    const { transactionId } = req.params;
    const { storeId, registerId, cashierId, items, note } = req.body;

    // ✅ VALIDATE REQUIRED FIELDS
    if (!transactionId || !storeId || !registerId || !cashierId || !items || !items.length) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Missing required fields: transactionId=${!!transactionId}, storeId=${!!storeId}, registerId=${!!registerId}, cashierId=${!!cashierId}, items=${items?.length || 0}`
      });
    }

    console.log(`🔍 Suspend attempt: TXN ${transactionId}, ${items.length} items`);

    // ✅ FETCH ORIGINAL TRANSACTION WITH LOCK
    const existingTransaction = await Transaction.findByPk(transactionId, { 
      transaction: t,
      lock: t.LOCK.UPDATE
    });

    if (!existingTransaction) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Original transaction ${transactionId} not found`
      });
    }

    if (existingTransaction.status !== 'open') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Transaction ${transactionId} must be OPEN to suspend (current: ${existingTransaction.status})`
      });
    }

    // ✅ PREVENT DUPLICATES
    const existingSuspended = await Transaction.findOne({
      where: { 
        originalTransactionId: transactionId,
        status: 'suspended'
      },
      transaction: t
    });

    if (existingSuspended) {
      const fullTransaction = await Transaction.findByPk(existingSuspended.id, {
        include: [
          {
            model: TransactionItem,
            as: 'transactionsitems',
            include: [{ model: Product, as: 'product' }]
          }
        ]
      });
      
      console.log(`✅ Returned existing suspended transaction: ${existingSuspended.transactionNumber}`);
      await t.commit();
      return res.status(200).json({
        success: true,
        message: 'Transaction already suspended - returning existing suspended transaction',
        transaction: fullTransaction
      });
    }

    // ✅ VALIDATE & CALCULATE ITEMS
    let subtotal = 0;
    const validatedItems = [];

    for (let i = 0; i < items.length; i++) {
      const item = items[i];

      if (!item.productId || item.quantity == null || item.unitPrice == null) {
        await t.rollback();
        return res.status(400).json({
          success: false,
          message: `Invalid item #${i + 1}: Missing productId, quantity, or unitPrice`
        });
      }

      const product = await Product.findByPk(item.productId, { transaction: t });
      if (!product) {
        await t.rollback();
        return res.status(400).json({
          success: false,
          message: `Product ${item.productId} not found`
        });
      }

      const quantity = parseFloat(item.quantity) || 0;
      const unitPrice = parseFloat(item.unitPrice) || 0;
      const discountAmount = parseFloat(item.discountAmount) || 0;
      const lineSubtotal = quantity * unitPrice;
      const lineTaxAmount = lineSubtotal * 0.16; // 16% VAT
      const lineTotalAmount = lineSubtotal + lineTaxAmount - discountAmount;

      validatedItems.push({
        productId: parseInt(item.productId),
        productVariantId: item.productVariantId || null,
        quantity,
        unitPrice,
        discountAmount,
        taxAmount: parseFloat(lineTaxAmount.toFixed(2)),
        totalAmount: parseFloat(lineTotalAmount.toFixed(2))
      });

      subtotal += lineSubtotal;
    }

    // ✅ GENERATE UNIQUE SUSPEND NUMBER
    const date = new Date();
    const suspendNumber = `SUSP${date.getFullYear()}${String(date.getMonth() + 1).padStart(2, '0')}${String(date.getDate()).padStart(2, '0')}${String(date.getHours()).padStart(2, '0')}${String(date.getMinutes()).padStart(2, '0')}${String(date.getSeconds()).padStart(2, '0')}`;

    // ✅ CREATE SUSPENDED TRANSACTION
    const totalTax = validatedItems.reduce((sum, item) => sum + item.taxAmount, 0);
    const totalAmount = validatedItems.reduce((sum, item) => sum + item.totalAmount, 0);

    const suspendedTransaction = await Transaction.create({
      transactionNumber: suspendNumber,
      storeId: parseInt(storeId),
      registerId: parseInt(registerId),
      cashierId: parseInt(cashierId),
      transactionDate: new Date(),
      subtotal: parseFloat(subtotal.toFixed(2)),
      discountAmount: 0,
      taxAmount: parseFloat(totalTax.toFixed(2)),
      totalAmount: parseFloat(totalAmount.toFixed(2)),
      status: 'suspended',
      suspendNote: note || 'Customer requested hold',
      isSuspended: true,
      suspendedAt: new Date(),
      originalTransactionId: transactionId
    }, { transaction: t });

    // ✅ CREATE ALL TRANSACTION ITEMS IN BULK
    const transactionItemsPayload = validatedItems.map(item => ({
      transactionId: suspendedTransaction.id,
      productId: item.productId,
      productVariantId: item.productVariantId,
      quantity: item.quantity,
      unitPrice: item.unitPrice,
      discountAmount: item.discountAmount,
      taxAmount: item.taxAmount,
      totalAmount: item.totalAmount
    }));

    await TransactionItem.bulkCreate(transactionItemsPayload, { transaction: t });

    // ✅ MARK ORIGINAL TRANSACTION AS SUSPENDED
    await existingTransaction.update({
      status: 'suspended',
      suspendNote: `Moved to suspended transaction ${suspendNumber}`
    }, { transaction: t });

    await t.commit();

    // ✅ RETURN COMPLETE SUSPENDED TRANSACTION WITH ITEMS
    const fullSuspendedTransaction = await Transaction.findByPk(suspendedTransaction.id, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        }
      ]
    });

    console.log(`✅ Created suspended transaction: ${suspendNumber} (${validatedItems.length} items, KES ${subtotal.toFixed(2)})`);

    res.status(201).json({
      success: true,
      message: 'Transaction suspended successfully',
      transaction: fullSuspendedTransaction
    });

  } catch (error) {
    if (t && !t.finished) {
      await t.rollback();
    }
    console.error('❌ Suspend transaction error:', error);
    res.status(500).json({
      success: false,
      message: 'Error suspending transaction',
      error: error.message
    });
  }
};

// ==================== RESUME SUSPENDED TRANSACTION ====================
// exports.resumeSuspendedTransaction = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { storeId, registerId, cashierId } = req.body;

//     // ✅ STEP 1: Find transaction with items
//     const transaction = await Transaction.findByPk(id, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         }
//       ]
//     });

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

//     if (transaction.status !== 'suspended') {
//       return res.status(400).json({
//         success: false,
//         message: 'Transaction is not suspended'
//       });
//     }

//     // ✅ STEP 2: Verify stock availability
//     for (const item of transaction.transactionsitems) {
//       if (item.product.stock < item.quantity) {
//         return res.status(400).json({
//           success: false,
//           message: `Insufficient stock for ${item.product.name}. Available: ${item.product.stock}, Needed: ${item.quantity}`
//         });
//       }
//     }

//     // ✅ STEP 3: ACTUALLY RESUME - UPDATE STATUS
//     await transaction.update({ 
//       status: 'open',
//       resumedAt: new Date()
//     });

//     // ✅ STEP 4: Return FULL transaction with items
//     const resumedTransaction = await Transaction.findByPk(id, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         }
//       ]
//     });

//     res.status(200).json({
//       success: true,
//       message: 'Transaction resumed successfully',
//       transaction: resumedTransaction
//     });

//   } catch (error) {
//     console.error('Resume suspended transaction error:', error);
//     res.status(500).json({
//       success: false,
//       message: 'Error resuming transaction',
//       error: error.message
//     });
//   }
// };



/**
 * Resume a suspended transaction
 * @route POST /api/transactions/:id/resume
 */
exports.resumeSuspendedTransaction = async (req, res) => {
  const startTime = Date.now();
  const { id } = req.params;
  const { storeId, registerId, cashierId } = req.body;

  // Log incoming request
  console.log(`[RESUME_TX] Starting resume for transaction ${id}`, {
    storeId,
    registerId,
    cashierId,
    timestamp: new Date().toISOString()
  });

  // Input validation
  if (!id || isNaN(parseInt(id))) {
    console.error(`[RESUME_TX] Invalid transaction ID: ${id}`);
    return res.status(400).json({
      success: false,
      message: 'Invalid transaction ID provided'
    });
  }

  if (!storeId || !registerId || !cashierId) {
    console.error(`[RESUME_TX] Missing required fields`, {
      storeId: !!storeId,
      registerId: !!registerId,
      cashierId: !!cashierId
    });
    return res.status(400).json({
      success: false,
      message: 'Missing required fields: storeId, registerId, or cashierId'
    });
  }

  let transaction = null;

  try {
    // STEP 1: Find transaction with items and products
    console.log(`[RESUME_TX] Fetching transaction ${id} from database`);
    
    transaction = await Transaction.findByPk(parseInt(id), {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          required: false, // Allow transactions with no items
          include: [
            {
              model: Product,
              as: 'product',
              required: true // Ensure product exists
            }
          ]
        }
      ]
    });

    // Check if transaction exists
    if (!transaction) {
      console.warn(`[RESUME_TX] Transaction not found: ${id}`);
      return res.status(404).json({
        success: false,
        message: `Transaction with ID ${id} not found`
      });
    }

    console.log(`[RESUME_TX] Transaction found`, {
      id: transaction.id,
      status: transaction.status,
      itemCount: transaction.transactionsitems?.length || 0
    });

    // STEP 2: Validate transaction status
    if (transaction.status !== 'suspended') {
      console.warn(`[RESUME_TX] Invalid status for transaction ${id}`, {
        currentStatus: transaction.status,
        expectedStatus: 'suspended'
      });
      return res.status(400).json({
        success: false,
        message: `Transaction cannot be resumed. Current status: ${transaction.status}`,
        currentStatus: transaction.status
      });
    }

    // STEP 3: Validate transaction ownership/permissions
    if (transaction.storeId && transaction.storeId.toString() !== storeId.toString()) {
      console.error(`[RESUME_TX] Store mismatch`, {
        transactionStoreId: transaction.storeId,
        requestedStoreId: storeId
      });
      return res.status(403).json({
        success: false,
        message: 'Transaction belongs to a different store'
      });
    }

    // STEP 4: Check if transaction has items
    if (!transaction.transactionsitems || transaction.transactionsitems.length === 0) {
      console.warn(`[RESUME_TX] Transaction ${id} has no items`);
      return res.status(400).json({
        success: false,
        message: 'Cannot resume transaction with no items'
      });
    }

    // STEP 5: Verify stock availability for all items
    console.log(`[RESUME_TX] Checking stock availability for ${transaction.transactionsitems.length} items`);
    
    const stockIssues = [];
    
    for (const item of transaction.transactionsitems) {
      if (!item.product) {
        console.error(`[RESUME_TX] Missing product for item`, {
          itemId: item.id,
          productId: item.productId
        });
        stockIssues.push({
          productId: item.productId,
          productName: 'Unknown Product',
          message: 'Product not found in database'
        });
        continue;
      }

      const availableStock = parseFloat(item.product.stock) || 0;
      const requiredQuantity = parseFloat(item.quantity) || 0;

      if (availableStock < requiredQuantity) {
        console.warn(`[RESUME_TX] Insufficient stock`, {
          productId: item.product.id,
          productName: item.product.name,
          available: availableStock,
          required: requiredQuantity,
          shortage: requiredQuantity - availableStock
        });
        
        stockIssues.push({
          productId: item.product.id,
          productName: item.product.name,
          available: availableStock,
          required: requiredQuantity,
          shortage: requiredQuantity - availableStock
        });
      }
    }

    // If there are stock issues, return detailed error
    if (stockIssues.length > 0) {
      console.error(`[RESUME_TX] Stock validation failed`, {
        transactionId: id,
        issueCount: stockIssues.length,
        issues: stockIssues
      });

      return res.status(400).json({
        success: false,
        message: 'Insufficient stock for one or more items',
        stockIssues: stockIssues,
        details: stockIssues.map(issue => 
          `${issue.productName}: Need ${issue.required}, Available ${issue.available}`
        ).join('; ')
      });
    }

    // STEP 6: Resume the transaction
    console.log(`[RESUME_TX] Updating transaction ${id} to open status`);
    
    await transaction.update({
      status: 'open',
      resumedAt: new Date(),
      registerId: registerId,
      cashierId: cashierId,
      storeId: storeId
    });

    console.log(`[RESUME_TX] Transaction ${id} updated successfully`);

    // STEP 7: Fetch the complete updated transaction
    const resumedTransaction = await Transaction.findByPk(parseInt(id), {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [
            {
              model: Product,
              as: 'product'
            }
          ]
        }
      ]
    });

    const duration = Date.now() - startTime;
    
    console.log(`[RESUME_TX] Transaction ${id} resumed successfully`, {
      duration: `${duration}ms`,
      itemCount: resumedTransaction.transactionsitems?.length || 0,
      totalAmount: resumedTransaction.totalAmount
    });

    return res.status(200).json({
      success: true,
      message: 'Transaction resumed successfully',
      transaction: resumedTransaction,
      metadata: {
        itemCount: resumedTransaction.transactionsitems?.length || 0,
        resumedAt: resumedTransaction.resumedAt,
        duration: `${duration}ms`
      }
    });

  } catch (error) {
    const duration = Date.now() - startTime;
    
    // Comprehensive error logging
    console.error(`[RESUME_TX] Error resuming transaction ${id}`, {
      duration: `${duration}ms`,
      errorName: error.name,
      errorMessage: error.message,
      errorStack: error.stack,
      transactionId: id,
      transactionStatus: transaction?.status,
      requestData: { storeId, registerId, cashierId },
      timestamp: new Date().toISOString()
    });

    // Handle specific Sequelize errors
    if (error.name === 'SequelizeValidationError') {
      console.error(`[RESUME_TX] Validation error:`, error.errors.map(e => ({
        field: e.path,
        message: e.message,
        value: e.value
      })));

      return res.status(400).json({
        success: false,
        message: 'Validation error',
        errors: error.errors.map(e => ({
          field: e.path,
          message: e.message
        }))
      });
    }

    if (error.name === 'SequelizeDatabaseError') {
      console.error(`[RESUME_TX] Database error:`, {
        sql: error.sql,
        parameters: error.parameters
      });

      return res.status(500).json({
        success: false,
        message: 'Database error occurred while resuming transaction'
      });
    }

    if (error.name === 'SequelizeForeignKeyConstraintError') {
      console.error(`[RESUME_TX] Foreign key constraint error:`, {
        table: error.table,
        fields: error.fields
      });

      return res.status(400).json({
        success: false,
        message: 'Invalid reference data in transaction'
      });
    }

    // Generic error response
    return res.status(500).json({
      success: false,
      message: 'An unexpected error occurred while resuming the transaction',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

// ==================== GET SUSPENDED TRANSACTIONS ====================


exports.getSuspendedTransactions = async (req, res) => {
  try {
    const { storeId, registerId, cashierId } = req.query;

    console.log("Incoming suspended transactions query:", { storeId, registerId, cashierId });

    const where = { status: 'suspended' };

    if (storeId) where.storeId = Number(storeId);
    if (registerId) where.registerId = Number(registerId);
    if (cashierId) where.cashierId = Number(cashierId);

    console.log("Final where clause:", where);

    const transactions = await Transaction.findAll({
      where,
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName']
        }
      ],
      order: [['transactionDate', 'DESC']]
    });

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

  } catch (error) {
    console.error('❌ FULL ERROR OBJECT:', JSON.stringify(error, null, 2));
    console.error('❌ ERROR STACK:', error.stack);

    res.status(400).json({
      success: false,
      message: 'Error fetching suspended transactions',
      error: error.message,
      details: error
    });
  }
};



// ==================== DELETE SUSPENDED TRANSACTION ====================
exports.deleteSuspendedTransaction = async (req, res) => {
  const t = await sequelize.transaction();

  try {
    const { id } = req.params;

    const transaction = await Transaction.findByPk(id, { transaction: t });

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

    if (transaction.status !== 'suspended') {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Can only delete suspended transactions'
      });
    }

    // Delete transaction items first
    await TransactionItem.destroy({
      where: { transactionId: id },
      transaction: t
    });

    // Delete transaction
    await transaction.destroy({ transaction: t });

    await t.commit();

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

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

// ==================== EXCHANGE ITEM IN TRANSACTION ====================
exports.exchangeItem = async (req, res) => {
  const t = await sequelize.transaction();

  try {
    const { transactionId } = req.params;
    const { oldProductId, newProductId, quantity, reason, exchangedBy } = req.body;

    if (!oldProductId || !newProductId || !quantity || !reason || !exchangedBy) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Missing required fields'
      });
    }

    // Fetch transaction
    const transaction = await Transaction.findByPk(transactionId, {
      include: [{ model: TransactionItem, as: 'transactionsitems' }],
      transaction: t
    });

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

    // Find the item being exchanged
    const oldItem = transaction.transactionsitems.find(
      item => item.productId === oldProductId
    );

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

    if (oldItem.quantity < quantity) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Exchange quantity exceeds purchased quantity'
      });
    }

    // Fetch both products
    const oldProduct = await Product.findByPk(oldProductId, { transaction: t });
    const newProduct = await Product.findByPk(newProductId, { transaction: t });

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

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

    // Restore old product stock
    await Product.increment('stock', {
      by: quantity,
      where: { id: oldProductId },
      transaction: t
    });

    // Reduce new product stock
    await Product.decrement('stock', {
      by: quantity,
      where: { id: newProductId },
      transaction: t
    });

    // Calculate price difference
    const priceDifference = (newProduct.unitPrice - oldProduct.unitPrice) * quantity;

    // Update or create transaction items
    if (oldItem.quantity === quantity) {
      // Replace the entire item
      await oldItem.update({
        productId: newProductId,
        unitPrice: newProduct.unitPrice,
        lineTotal: newProduct.unitPrice * quantity,
        taxAmount: newProduct.unitPrice * quantity * 0.16,
        exchangedFrom: oldProductId,
        exchangeReason: reason
      }, { transaction: t });
    } else {
      // Reduce old item quantity
      await oldItem.update({
        quantity: oldItem.quantity - quantity,
        lineTotal: oldItem.unitPrice * (oldItem.quantity - quantity),
        taxAmount: oldItem.unitPrice * (oldItem.quantity - quantity) * 0.16
      }, { transaction: t });

      // Add new item
      await TransactionItem.create({
        transactionId: transaction.id,
        productId: newProductId,
        quantity,
        unitPrice: newProduct.unitPrice,
        lineTotal: newProduct.unitPrice * quantity,
        taxAmount: newProduct.unitPrice * quantity * 0.16,
        exchangedFrom: oldProductId,
        exchangeReason: reason
      }, { transaction: t });
    }

    // Update transaction totals
    await transaction.update({
      subtotal: transaction.subtotal + priceDifference,
      taxAmount: transaction.taxAmount + (priceDifference * 0.16),
      totalAmount: transaction.totalAmount + (priceDifference * 1.16),
      lastModifiedBy: exchangedBy,
      lastModifiedAt: new Date()
    }, { transaction: t });

    // Handle price difference payment if needed
    if (priceDifference > 0) {
      // Customer needs to pay more - create pending payment
      await TransactionPayment.create({
        transactionId: transaction.id,
        paymentMethod: 'pending',
        amount: priceDifference * 1.16,
        status: 'pending',
        paymentDate: new Date()
      }, { transaction: t });
    } else if (priceDifference < 0) {
      // Refund customer - create refund payment
      await TransactionPayment.create({
        transactionId: transaction.id,
        paymentMethod: 'refund',
        amount: Math.abs(priceDifference * 1.16),
        status: 'completed',
        paymentDate: new Date()
      }, { transaction: t });
    }

    await t.commit();

    // Fetch updated transaction
    const updatedTransaction = await Transaction.findByPk(transactionId, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ]
    });

    res.status(200).json({
      success: true,
      message: 'Item exchanged successfully',
      transaction: updatedTransaction,
      priceDifference: priceDifference * 1.16
    });

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

// ==================== GET TRANSACTION HISTORY FOR RECEIPT REPRINT ====================
exports.getTransactionForReprint = async (req, res) => {
  try {
    const { transactionNumber } = req.params;

    const transaction = await Transaction.findOne({
      where: { transactionNumber },
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        },
        {
          model: User,
          as: 'cashier',
          attributes: ['id', 'firstName', 'lastName', 'email']
        },
        {
          model: Store,
          as: 'store'
        },
        {
          model: Register,
          as: 'register'
        }
      ]
    });

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

    res.status(200).json({
      success: true,
      transaction,
      reprint: true,
      reprintedAt: new Date()
    });

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

exports.checkManagerPin = async (req, res) => {
  try {
    const { managerPin } = req.body;

    if (!pin || pin.toString().length !== 4 || !/^\d{4}$/.test(pin)) {
      return res.status(400).json({ message: 'PIN must be exactly 4 digits' });
    }

    // Step 1: Get ALL active users who have a PIN set
    const usersWithPin = await User.findAll({
      where: {
        isActive: true,
        managerPin: { [Op.ne]: null } // Has a PIN
      },
      include: [
        { model: Store, as: 'store' },
        { model: Register, as: 'openedRegisters' },
        { model: RegisterAssignment, as: 'registerAssignments' },
        {
          model: Register,
          as: 'assignedRegisters',
          through: { attributes: ['id', 'assignedAt', 'isPrimary', 'isActive'] },
        },
      ],
    });

    // Step 2: Loop through users and compare entered PIN with their hashed PIN
    let authenticatedUser = null;

    for (const user of usersWithPin) {
      const isMatch = await bcrypt.compare(managerPin.toString(), user.pin);
      if (isMatch) {
        authenticatedUser = user;
        break;
      }
    }

    // Step 3: If no match found → invalid PIN
    if (!authenticatedUser) {
      return res.status(401).json({ message: 'Invalid PIN' });
    }

    console.log('PIN login successful for:', authenticatedUser.username, 'Role:', authenticatedUser.role);

    

   

    // Return user data
    res.json({
      message: 'Login successful',
      user: {
       authenticatedUser,
      },
    });
  } catch (error) {
    console.error('PIN login error:', error);
    res.status(500).json({ message: 'Login failed. Please try again.' });
  }
};



// ==================== NEW TRANSACTION CONTROLLERS  ====================



// ============================================
// 1. START NEW TRANSACTION (OPEN STATUS)
// ============================================
exports.startTransaction = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { storeId, registerId, cashierId, customerId } = req.body;

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

    // Generate transaction number
    const date = new Date();
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    
    const lastTransaction = await Transaction.findOne({
      where: {
        transactionNumber: {
          [Op.like]: `TXN${year}${month}${day}%`
        }
      },
      order: [['transactionNumber', 'DESC']],
      transaction: t
    });

    let sequence = 1;
    if (lastTransaction) {
      const lastSequence = parseInt(lastTransaction.transactionNumber.slice(-4));
      sequence = lastSequence + 1;
    }

    const transactionNumber = `TXN${year}${month}${day}${String(sequence).padStart(4, '0')}`;

    // Create transaction with OPEN status
    const transaction = await Transaction.create({
      transactionNumber,
      storeId,
      registerId,
      customerId: customerId || null,
      cashierId,
      transactionDate: new Date(),
      status: TRANSACTION_STATUS.OPEN,
      subtotal: 0,
      taxAmount: 0,
      totalAmount: 0,
      discountAmount: 0,
      discountPercent: 0
    }, { transaction: t });

    await t.commit();

    res.status(201).json({
      success: true,
      message: 'Transaction started',
      transaction
    });

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

// ============================================
// 2. ADD ITEMS TO OPEN TRANSACTION
// ============================================
exports.addItemsToTransaction = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { transactionId } = req.params;
    const { items } = req.body;

    const transaction = await Transaction.findByPk(transactionId, { transaction: t });

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

    // Only allow adding items to OPEN transactions
    if (transaction.status !== TRANSACTION_STATUS.OPEN) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Cannot add items. Transaction status is: ${transaction.status}`
      });
    }

    // Validate and add items
    let subtotal = parseFloat(transaction.subtotal);
    
    for (const item of items) {
      const product = await Product.findByPk(item.productId, { transaction: t });
      
      if (!product || !product.isActive) {
        await t.rollback();
        return res.status(404).json({
          success: false,
          message: `Product ${item.productId} not found or inactive`
        });
      }

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

      const lineTotal = item.unitPrice * item.quantity;
      subtotal += lineTotal;

      // Check if item already exists
      const existingItem = await TransactionItem.findOne({
        where: {
          transactionId: transaction.id,
          productId: item.productId
        },
        transaction: t
      });

      if (existingItem) {
        // Update quantity
        await existingItem.update({
          quantity: existingItem.quantity + item.quantity,
          lineTotal: existingItem.lineTotal + lineTotal
        }, { transaction: t });
      } else {
        // Add new item
        await TransactionItem.create({
          transactionId: transaction.id,
          productId: item.productId,
          quantity: item.quantity,
          unitPrice: item.unitPrice,
          lineTotal,
          taxAmount: lineTotal * 0.16
        }, { transaction: t });
      }
    }

    // Update transaction totals
    const taxAmount = subtotal * 0.16;
    const totalAmount = subtotal + taxAmount;

    await transaction.update({
      subtotal,
      taxAmount,
      totalAmount
    }, { transaction: t });

    await t.commit();

    // Fetch updated transaction
    const updatedTransaction = await Transaction.findByPk(transactionId, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        }
      ]
    });

    res.status(200).json({
      success: true,
      message: 'Items added successfully',
      transaction: updatedTransaction
    });

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

// ============================================
// 3. SUSPEND TRANSACTION (PARK/HOLD)
// ============================================
// exports.suspendTransaction = async (req, res) => {
//   const t = await sequelize.transaction();
  
//   try {
//     const { transactionId } = req.params;
//     const { reason } = req.body;

//     const transaction = await Transaction.findByPk(transactionId, { transaction: t });

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

//     if (transaction.status !== TRANSACTION_STATUS.OPEN) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Can only suspend open transactions'
//       });
//     }

//     await transaction.update({
//       status: TRANSACTION_STATUS.SUSPENDED,
//       suspendNote: reason || 'Transaction suspended',
//       suspendedAt: new Date()
//     }, { transaction: t });

//     await t.commit();

//     res.status(200).json({
//       success: true,
//       message: 'Transaction suspended',
//       transaction
//     });

//   } catch (error) {
//     if (!t.finished) await t.rollback();
//     console.error('Suspend transaction error:', error);
//     res.status(500).json({
//       success: false,
//       message: 'Error suspending transaction',
//       error: error.message
//     });
//   }
// };

// ============================================
// 4. RESUME SUSPENDED TRANSACTION
// ============================================
exports.resumeTransaction = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { transactionId } = req.params;

    const transaction = await Transaction.findByPk(transactionId, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        }
      ],
      transaction: t
    });

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

    if (transaction.status !== TRANSACTION_STATUS.SUSPENDED) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Can only resume suspended transactions'
      });
    }

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

    await transaction.update({
      status: TRANSACTION_STATUS.OPEN,
      resumedAt: new Date()
    }, { transaction: t });

    await t.commit();

    res.status(200).json({
      success: true,
      message: 'Transaction resumed',
      transaction
    });

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

// ============================================
// 5. FINALIZE TRANSACTION (PENDING PAYMENT)
// ============================================
// exports.finalizeTransaction = async (req, res) => {
//   const t = await sequelize.transaction();
  
//   try {
//     const { transactionId } = req.params;
//     const { discountAmount, discountPercent } = req.body;

//     const transaction = await Transaction.findByPk(transactionId, {
//       include: [{ model: TransactionItem, as: 'transactionsitems' }],
//       transaction: t
//     });

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

//     if (transaction.status !== TRANSACTION_STATUS.OPEN) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Can only finalize open transactions'
//       });
//     }

//     if (!transaction.transactionsitems || transaction.transactionsitems.length === 0) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Cannot finalize empty transaction'
//       });
//     }

//     // Calculate discount
//     let finalDiscount = 0;
//     if (discountPercent > 0) {
//       finalDiscount = transaction.subtotal * (discountPercent / 100);
//     } else if (discountAmount > 0) {
//       finalDiscount = discountAmount;
//     }

//     // Recalculate totals
//     const taxableAmount = transaction.subtotal - finalDiscount;
//     const taxAmount = taxableAmount * 0.16;
//     const totalAmount = taxableAmount + taxAmount;

//     await transaction.update({
//       status: TRANSACTION_STATUS.PENDING_PAYMENT,
//       discountAmount: finalDiscount,
//       discountPercent: discountPercent || 0,
//       taxAmount,
//       totalAmount,
//       finalizedAt: new Date()
//     }, { transaction: t });

//     await t.commit();

//     res.status(200).json({
//       success: true,
//       message: 'Transaction finalized. Ready for payment.',
//       transaction
//     });

//   } catch (error) {
//     if (!t.finished) await t.rollback();
//     console.error('Finalize transaction error:', error);
//     res.status(500).json({
//       success: false,
//       message: 'Error finalizing transaction',
//       error: error.message
//     });
//   }
// };

// exports.finalizeTransaction = async (req, res) => {
//   const t = await sequelize.transaction();
  
//   try {
//     console.log('🚀 FINALIZE STARTED');
//     console.log('📍 Request params:', req.params);
//     console.log('📍 Request body:', req.body);
    
//     const { transactionId } = req.params;
//     const { discountAmount = 0, discountPercent = 0 } = req.body;

//     console.log('🔍 Finalize params:', { 
//       transactionId, 
//       transactionIdType: typeof transactionId,
//       discountAmount, 
//       discountPercent 
//     });

//     // ✅ VALIDATE transactionId IS NUMBER
//     if (!transactionId || isNaN(transactionId)) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: `Invalid transactionId: ${transactionId} (${typeof transactionId})`
//       });
//     }

//     const transaction = await Transaction.findByPk(parseInt(transactionId), {
//       include: [{ model: TransactionItem, as: 'transactionsitems' }],
//       transaction: t
//     });

//     console.log('📊 TRANSACTION FOUND:', !!transaction);
//     if (transaction) {
//       console.log('📊 Transaction details:', {
//         id: transaction.id,
//         status: transaction.status,
//         subtotal: transaction.subtotal,
//         itemsCount: transaction.transactionsitems?.length || 0
//       });
//     }

//     if (!transaction) {
//       await t.rollback();
//       console.log('❌ Transaction NOT found');
//       return res.status(404).json({
//         success: false,
//         message: `Transaction ${transactionId} not found`
//       });
//     }

//     if (transaction.status !== 'open') {
//       await t.rollback();
//       console.log('❌ Wrong status:', transaction.status);
//       return res.status(400).json({
//         success: false,
//         message: `Can only finalize open transactions (current: ${transaction.status})`
//       });
//     }

//     if (!transaction.transactionsitems || transaction.transactionsitems.length === 0) {
//       await t.rollback();
//       console.log('❌ No items in transaction');
//       return res.status(400).json({
//         success: false,
//         message: 'Cannot finalize empty transaction'
//       });
//     }

//     // ✅ CALCULATE DISCOUNT SAFELY
//     let finalDiscount = 0;
//     if (discountPercent > 0) {
//       finalDiscount = parseFloat((transaction.subtotal * (discountPercent / 100)).toFixed(2));
//       console.log('💰 Discount by %:', { discountPercent, finalDiscount });
//     } else if (discountAmount > 0) {
//       finalDiscount = parseFloat(discountAmount.toFixed(2));
//       console.log('💰 Discount by amount:', { discountAmount, finalDiscount });
//     }

//     // ✅ RECALCULATE TOTALS
//     const taxableAmount = parseFloat((transaction.subtotal - finalDiscount).toFixed(2));
//     const taxAmount = parseFloat((taxableAmount * 0.16).toFixed(2));
//     const totalAmount = parseFloat((taxableAmount + taxAmount).toFixed(2));

//     console.log('💰 FINAL TOTALS:', { 
//       subtotal: transaction.subtotal, 
//       finalDiscount, 
//       taxableAmount, 
//       taxAmount, 
//       totalAmount 
//     });

//     console.log('✏️ UPDATING TRANSACTION...');
//     const updateResult = await transaction.update({
//       status: 'pending_payment',
//       discountAmount: finalDiscount,
//       discountPercent: parseFloat(discountPercent) || 0,
//       taxAmount,
//       totalAmount,
//       finalizedAt: new Date()
//     }, { transaction: t });

//     console.log('✅ UPDATE RESULT:', !!updateResult);

//     await t.commit();
//     console.log('✅ TRANSACTION COMMITTED');

//     // ✅ RETURN UPDATED TRANSACTION
//     const finalizedTransaction = await Transaction.findByPk(transactionId, {
//       include: [
//         { 
//           model: TransactionItem, 
//           as: 'transactionsitems', 
//           include: [{ model: Product, as: 'product' }] 
//         }
//       ]
//     });

//     console.log('✅ FINALIZE SUCCESS');
//     res.status(200).json({
//       success: true,
//       message: 'Transaction finalized successfully',
//       transaction: finalizedTransaction
//     });

//   } catch (error) {
//     console.error('🔥 ============ FULL ERROR DUMP ============');
//     console.error('❌ ERROR TYPE:', typeof error);
//     console.error('❌ ERROR NAME:', error.name);
//     console.error('❌ ERROR MESSAGE:', error.message);
//     console.error('❌ ERROR STACK:', error.stack);
//     console.error('❌ FULL ERROR:', JSON.stringify(error, Object.getOwnPropertyNames(error), 2));
//     console.error('🔥 ========================================');
    
//     if (!t.finished) {
//       console.log('🔄 Rolling back transaction...');
//       await t.rollback();
//     }
    
//     res.status(500).json({
//       success: false,
//       message: 'Error finalizing transaction',
//       error: error.message,
//       details: {
//         name: error.name,
//         stack: error.stack?.split('\n').slice(0, 5).join('\n')
//       }
//     });
//   }
// };


exports.finalizeTransaction = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    console.log('🚀 FINALIZE STARTED');
    console.log('📍 Request params:', req.params);
    console.log('📍 Request body:', req.body);
    
    const { transactionId } = req.params;
    const { 
      discountAmount = 0, 
      discountPercent = 0,
      items = [],
      storeId,
      registerId,
      cashierId 
    } = req.body;

    console.log('🔍 Finalize params:', { 
      transactionId, 
      transactionIdType: typeof transactionId,
      discountAmount, 
      discountPercent,
      itemsCount: items.length
    });

    // ✅ VALIDATE transactionId IS NUMBER
    if (!transactionId || isNaN(transactionId)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Invalid transactionId: ${transactionId} (${typeof transactionId})`
      });
    }

    const transaction = await Transaction.findByPk(parseInt(transactionId), {
      include: [{ model: TransactionItem, as: 'transactionsitems' }],
      transaction: t
    });

    console.log('📊 TRANSACTION FOUND:', !!transaction);
    if (transaction) {
      console.log('📊 Transaction details:', {
        id: transaction.id,
        status: transaction.status,
        subtotal: transaction.subtotal,
        itemsCount: transaction.transactionsitems?.length || 0
      });
    }

    if (!transaction) {
      await t.rollback();
      console.log('❌ Transaction NOT found');
      return res.status(404).json({
        success: false,
        message: `Transaction ${transactionId} not found`
      });
    }

    if (transaction.status !== 'open') {
      await t.rollback();
      console.log('❌ Wrong status:', transaction.status);
      return res.status(400).json({
        success: false,
        message: `Can only finalize open transactions (current: ${transaction.status})`
      });
    }

    // ✅ SAVE/UPDATE ITEMS IF PROVIDED
    if (items && items.length > 0) {
      console.log('💾 Saving transaction items...');
      
      // Delete existing items for this transaction
      await TransactionItem.destroy({
        where: { transactionId: transaction.id },
        transaction: t
      });

      // Calculate subtotal from items
      let calculatedSubtotal = 0;
      
      // Create new items
      for (const item of items) {
        await TransactionItem.create({
          transactionId: transaction.id,
          productId: item.productId,
          productVariantId: item.productVariantId,
          quantity: item.quantity,
          unitPrice: item.unitPrice,
          discountAmount: item.discountAmount || 0,
          taxAmount: item.taxAmount,
          totalAmount: item.totalAmount
        }, { transaction: t });
        
        calculatedSubtotal += (item.unitPrice * item.quantity);
      }

      // Update transaction subtotal
      transaction.subtotal = parseFloat(calculatedSubtotal.toFixed(2));
      console.log('💰 Updated subtotal:', transaction.subtotal);
    }

    // Verify transaction has items
    const finalItems = await TransactionItem.findAll({
      where: { transactionId: transaction.id },
      transaction: t
    });

    if (!finalItems || finalItems.length === 0) {
      await t.rollback();
      console.log('❌ No items in transaction after processing');
      return res.status(400).json({
        success: false,
        message: 'Cannot finalize empty transaction'
      });
    }

    // ✅ CALCULATE DISCOUNT SAFELY
    let finalDiscount = 0;
    if (discountPercent > 0) {
      finalDiscount = parseFloat((transaction.subtotal * (discountPercent / 100)).toFixed(2));
      console.log('💰 Discount by %:', { discountPercent, finalDiscount });
    } else if (discountAmount > 0) {
      finalDiscount = parseFloat(discountAmount.toFixed(2));
      console.log('💰 Discount by amount:', { discountAmount, finalDiscount });
    }

    // ✅ RECALCULATE TOTALS
    const taxableAmount = parseFloat((transaction.subtotal - finalDiscount).toFixed(2));
    const taxAmount = parseFloat((taxableAmount * 0.16).toFixed(2));
    const totalAmount = parseFloat((taxableAmount + taxAmount).toFixed(2));

    console.log('💰 FINAL TOTALS:', { 
      subtotal: transaction.subtotal, 
      finalDiscount, 
      taxableAmount, 
      taxAmount, 
      totalAmount 
    });

    console.log('✏️ UPDATING TRANSACTION...');
    
    // Build update object
    const updateData = {
      status: 'pending_payment',
      discountAmount: finalDiscount,
      discountPercent: parseFloat(discountPercent) || 0,
      taxAmount,
      totalAmount,
      finalizedAt: new Date()
    };

    // Add optional fields if provided
    if (storeId) updateData.storeId = storeId;
    if (registerId) updateData.registerId = registerId;
    if (cashierId) updateData.cashierId = cashierId;

    const updateResult = await transaction.update(updateData, { transaction: t });

    console.log('✅ UPDATE RESULT:', !!updateResult);

    await t.commit();
    console.log('✅ TRANSACTION COMMITTED');

    // ✅ RETURN UPDATED TRANSACTION
    const finalizedTransaction = await Transaction.findByPk(transactionId, {
      include: [
        { 
          model: TransactionItem, 
          as: 'transactionsitems', 
          include: [{ model: Product, as: 'product' }] 
        }
      ]
    });

    console.log('✅ FINALIZE SUCCESS');
    res.status(200).json({
      success: true,
      message: 'Transaction finalized successfully',
      transaction: finalizedTransaction
    });

  } catch (error) {
    console.error('🔥 ============ FULL ERROR DUMP ============');
    console.error('❌ ERROR TYPE:', typeof error);
    console.error('❌ ERROR NAME:', error.name);
    console.error('❌ ERROR MESSAGE:', error.message);
    console.error('❌ ERROR STACK:', error.stack);
    console.error('❌ FULL ERROR:', JSON.stringify(error, Object.getOwnPropertyNames(error), 2));
    console.error('🔥 ========================================');
    
    if (!t.finished) {
      console.log('🔄 Rolling back transaction...');
      await t.rollback();
    }
    
    res.status(500).json({
      success: false,
      message: 'Error finalizing transaction',
      error: error.message,
      details: {
        name: error.name,
        stack: error.stack?.split('\n').slice(0, 5).join('\n')
      }
    });
  }
};

// ============================================
// 6. PROCESS PAYMENT (MULTIPLE METHODS)
// ============================================
// exports.processPayment = async (req, res) => {
//   const t = await sequelize.transaction();
  
//   try {
//     const { transactionId } = req.params;
//     const { payments } = req.body; // Array of payment methods

//     console.log('Processing payments:', payments);

//     const transaction = await Transaction.findByPk(transactionId, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         }
//       ],
//       transaction: t
//     });

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

//     if (transaction.status !== TRANSACTION_STATUS.PENDING_PAYMENT) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: `Cannot process payment. Status: ${transaction.status}`
//       });
//     }

//     // Validate payment amounts
//     const totalPaid = payments.reduce((sum, p) => sum + parseFloat(p.amount), 0);
//     const totalRequired = parseFloat(transaction.totalAmount);

//     // Check for mobile money
//     const hasMobileMoney = payments.some(p => p.paymentMethod === 'mobile_money');

//     if (!hasMobileMoney && totalPaid < totalRequired) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: `Insufficient payment. Required: ${totalRequired}, Received: ${totalPaid}`
//       });
//     }

//     // Create payment records
//     for (const payment of payments) {
//       const paymentStatus = payment.paymentMethod === 'mobile_money' 
//         ? 'pending' 
//         : 'completed';

//       await TransactionPayment.create({
//         transactionId: transaction.id,
//         paymentMethod: payment.paymentMethod,
//         amount: payment.amount,
//         paymentDate: new Date(),
//         status: paymentStatus,
//         referenceNumber: payment.referenceNumber || null,
//         cardType: payment.cardType || null,
//         last4Digits: payment.last4Digits || null
//       }, { transaction: t });
//     }

//     // Update transaction status
//     let newStatus;
//     if (hasMobileMoney) {
//       newStatus = TRANSACTION_STATUS.PENDING_PAYMENT; // Wait for M-Pesa callback
//     } else {
//       newStatus = TRANSACTION_STATUS.PAID;
      
//       // Deduct stock immediately for non-mobile payments
//       for (const item of transaction.transactionsitems) {
//         await Product.decrement('stock', {
//           by: item.quantity,
//           where: { id: item.productId },
//           transaction: t
//         });
//       }
//     }

//     await transaction.update({
//       status: newStatus,
//       paidAt: new Date()
//     }, { transaction: t });

//     await t.commit();

//     res.status(200).json({
//       success: true,
//       message: hasMobileMoney 
//         ? 'Payment initiated. Awaiting confirmation.' 
//         : 'Payment successful',
//       transaction
//     });

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

// exports.processPayment = async (req, res) => {
//   const t = await sequelize.transaction();
  
//   try {
//     console.log('🚀 PROCESS PAYMENT STARTED');
//     const { transactionId } = req.params;
//     const { payments } = req.body; // Array of payment methods

//     // ✅ VALIDATE INPUT
//     if (!transactionId || isNaN(transactionId)) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Invalid transaction ID'
//       });
//     }

//     if (!payments || !Array.isArray(payments) || payments.length === 0) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'No payment methods provided'
//       });
//     }

//     // Validate each payment
//     for (const payment of payments) {
//       if (!payment.paymentMethod || !payment.amount) {
//         await t.rollback();
//         return res.status(400).json({
//           success: false,
//           message: 'Invalid payment data. Each payment must have paymentMethod and amount.'
//         });
//       }

//       const validMethods = ['cash', 'card', 'mobile_money', 'bank_transfer'];
//       if (!validMethods.includes(payment.paymentMethod)) {
//         await t.rollback();
//         return res.status(400).json({
//           success: false,
//           message: `Invalid payment method: ${payment.paymentMethod}`
//         });
//       }
//     }

//     console.log('📋 Processing payments:', JSON.stringify(payments, null, 2));

//     // ✅ FETCH TRANSACTION
//     const transaction = await Transaction.findByPk(parseInt(transactionId), {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         }
//       ],
//       transaction: t
//     });

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

//     console.log('📊 Transaction found:', {
//       id: transaction.id,
//       status: transaction.status,
//       totalAmount: transaction.totalAmount
//     });

//     // ✅ VALIDATE TRANSACTION STATUS
//     if (transaction.status !== TRANSACTION_STATUS.PENDING_PAYMENT) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: `Cannot process payment. Current status: ${transaction.status}. Expected: pending_payment`
//       });
//     }

//     // ✅ VALIDATE ITEMS EXIST
//     if (!transaction.transactionsitems || transaction.transactionsitems.length === 0) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: 'Transaction has no items'
//       });
//     }

//     // ✅ CALCULATE AND VALIDATE AMOUNTS WITH ROUNDING TOLERANCE
//     const totalPaid = parseFloat(
//       payments.reduce((sum, p) => sum + parseFloat(p.amount), 0).toFixed(2)
//     );
//     const totalRequired = parseFloat(parseFloat(transaction.totalAmount).toFixed(2));
    
//     // Allow 0.10 cents tolerance for rounding errors
//     const TOLERANCE = 0.10;
//     const difference = Math.abs(totalRequired - totalPaid);

//     console.log('💰 Payment validation:', {
//       totalRequired,
//       totalPaid,
//       difference,
//       withinTolerance: difference <= TOLERANCE
//     });

//     // Check for mobile money
//     const hasMobileMoney = payments.some(p => p.paymentMethod === 'mobile_money');

//     // Only validate exact amounts for non-mobile-money payments
//     if (!hasMobileMoney && totalPaid < (totalRequired - TOLERANCE)) {
//       await t.rollback();
//       return res.status(400).json({
//         success: false,
//         message: `Insufficient payment. Required: ${totalRequired.toFixed(2)}, Received: ${totalPaid.toFixed(2)}, Short: ${(totalRequired - totalPaid).toFixed(2)}`
//       });
//     }

//     // Warn if overpayment (but allow it)
//     if (totalPaid > totalRequired + TOLERANCE) {
//       console.log('⚠️ Warning: Overpayment detected', {
//         required: totalRequired,
//         paid: totalPaid,
//         excess: (totalPaid - totalRequired).toFixed(2)
//       });
//     }

//     console.log('✅ Payment validation passed');

//     // ✅ CREATE PAYMENT RECORDS
//     const createdPayments = [];
//     for (const payment of payments) {
//       const paymentStatus = payment.paymentMethod === 'mobile_money' 
//         ? 'pending' 
//         : 'completed';

//       const paymentRecord = await TransactionPayment.create({
//         transactionId: transaction.id,
//         paymentMethod: payment.paymentMethod,
//         amount: parseFloat(parseFloat(payment.amount).toFixed(2)),
//         paymentDate: new Date(),
//         status: paymentStatus,
//         referenceNumber: payment.referenceNumber || `REF-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`,
//         cardType: payment.cardType || null,
//         last4Digits: payment.last4Digits || null,
//         phoneNumber: payment.phoneNumber || null, // For mobile money
//         metadata: payment.metadata || null
//       }, { transaction: t });

//       createdPayments.push(paymentRecord);
//       console.log(`💳 Payment record created: ${payment.paymentMethod} - KES ${payment.amount}`);
//     }

//     // ✅ DETERMINE NEW STATUS & HANDLE STOCK
//     let newStatus;
//     let shouldDeductStock = false;

//     if (hasMobileMoney) {
//       // Keep pending for mobile money (wait for callback)
//       newStatus = TRANSACTION_STATUS.PENDING_PAYMENT;
//       console.log('📱 Mobile money detected - keeping status as pending_payment');
//     } else {
//       // Mark as authorized for immediate payments
//       newStatus = TRANSACTION_STATUS.AUTHORIZED;
//       shouldDeductStock = true;
//       console.log('✅ Immediate payment - updating to authorized and deducting stock');
//     }

//     // ✅ DEDUCT STOCK FOR IMMEDIATE PAYMENTS
//     if (shouldDeductStock) {
//       console.log('📦 Deducting stock...');
//       for (const item of transaction.transactionsitems) {
//         const product = await Product.findByPk(item.productId, { transaction: t });
        
//         if (!product) {
//           await t.rollback();
//           return res.status(400).json({
//             success: false,
//             message: `Product not found: ${item.productId}`
//           });
//         }

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

//         await Product.decrement('stock', {
//           by: item.quantity,
//           where: { id: item.productId },
//           transaction: t
//         });

//         console.log(`📦 Deducted ${item.quantity} units from product ${product.name} (ID: ${product.id})`);
//       }
//     }

//     // ✅ UPDATE TRANSACTION
//     await transaction.update({
//       status: newStatus,
//       paidAt: hasMobileMoney ? null : new Date(), // Only set paidAt for completed payments
//       authorizedAt: shouldDeductStock ? new Date() : null
//     }, { transaction: t });

//     console.log(`✅ Transaction updated to status: ${newStatus}`);

//     // ✅ COMMIT TRANSACTION
//     await t.commit();
//     console.log('✅ DATABASE TRANSACTION COMMITTED');

//     // ✅ FETCH FINAL TRANSACTION STATE
//     const finalTransaction = await Transaction.findByPk(transactionId, {
//       include: [
//         {
//           model: TransactionItem,
//           as: 'transactionsitems',
//           include: [{ model: Product, as: 'product' }]
//         },
//         {
//           model: TransactionPayment,
//           as: 'transactionpayment'
//         }
//       ]
//     });

//     console.log('✅ PROCESS PAYMENT SUCCESS');

//     res.status(200).json({
//       success: true,
//       message: hasMobileMoney 
//         ? 'Payment initiated. Awaiting M-Pesa confirmation.' 
//         : 'Payment processed successfully',
//       transaction: finalTransaction,
//       payments: createdPayments,
//       summary: {
//         totalRequired: totalRequired.toFixed(2),
//         totalPaid: totalPaid.toFixed(2),
//         change: Math.max(0, totalPaid - totalRequired).toFixed(2),
//         status: newStatus,
//         awaitingConfirmation: hasMobileMoney
//       }
//     });

//   } catch (error) {
//     console.error('🔥 ============ PAYMENT ERROR ============');
//     console.error('❌ ERROR TYPE:', typeof error);
//     console.error('❌ ERROR NAME:', error.name);
//     console.error('❌ ERROR MESSAGE:', error.message);
//     console.error('❌ ERROR STACK:', error.stack);
//     console.error('🔥 ========================================');
    
//     if (!t.finished) {
//       console.log('🔄 Rolling back transaction...');
//       await t.rollback();
//     }
    
//     res.status(500).json({
//       success: false,
//       message: 'Error processing payment',
//       error: error.message,
//       details: {
//         name: error.name,
//         timestamp: new Date().toISOString()
//       }
//     });
//   }
// };


exports.processPayment = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    console.log('🚀 PROCESS PAYMENT STARTED');
    const { transactionId } = req.params;
    const { payments } = req.body; // Array of payment methods

    // ✅ VALIDATE INPUT
    if (!transactionId || isNaN(transactionId)) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Invalid transaction ID'
      });
    }

    if (!payments || !Array.isArray(payments) || payments.length === 0) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'No payment methods provided'
      });
    }

    // Validate each payment
    for (const payment of payments) {
      if (!payment.paymentMethod || !payment.amount) {
        await t.rollback();
        return res.status(400).json({
          success: false,
          message: 'Invalid payment data. Each payment must have paymentMethod and amount.'
        });
      }

      const validMethods = ['cash', 'card', 'mobile_money', 'bank_transfer'];
      if (!validMethods.includes(payment.paymentMethod)) {
        await t.rollback();
        return res.status(400).json({
          success: false,
          message: `Invalid payment method: ${payment.paymentMethod}`
        });
      }
    }

    console.log('📋 Processing payments:', JSON.stringify(payments, null, 2));

    // ✅ FETCH TRANSACTION
    const transaction = await Transaction.findByPk(parseInt(transactionId), {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        }
      ],
      transaction: t
    });

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

    console.log('📊 Transaction found:', {
      id: transaction.id,
      status: transaction.status,
      totalAmount: transaction.totalAmount
    });

    // ✅ VALIDATE TRANSACTION STATUS
    if (transaction.status !== TRANSACTION_STATUS.PENDING_PAYMENT) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Cannot process payment. Current status: ${transaction.status}. Expected: pending_payment`
      });
    }

    // ✅ VALIDATE ITEMS EXIST
    if (!transaction.transactionsitems || transaction.transactionsitems.length === 0) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Transaction has no items'
      });
    }

    // ✅ CALCULATE AND VALIDATE AMOUNTS WITH ROUNDING TOLERANCE
    const totalPaid = parseFloat(
      payments.reduce((sum, p) => sum + parseFloat(p.amount), 0).toFixed(2)
    );
    const totalRequired = parseFloat(parseFloat(transaction.totalAmount).toFixed(2));
    
    // Allow 0.50 cents tolerance for rounding errors (increased from 0.10)
    // This handles edge cases where subtotal + tax calculations may differ slightly
    const TOLERANCE = 0.50;
    const difference = totalRequired - totalPaid;

    console.log('💰 Payment validation:', {
      totalRequired,
      totalPaid,
      difference,
      withinTolerance: Math.abs(difference) <= TOLERANCE
    });

    // Check for mobile money
    const hasMobileMoney = payments.some(p => p.paymentMethod === 'mobile_money');

    // Only validate for significant underpayments (beyond tolerance)
    if (!hasMobileMoney && difference > TOLERANCE) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: `Insufficient payment. Required: ${totalRequired.toFixed(2)}, Received: ${totalPaid.toFixed(2)}, Short: ${difference.toFixed(2)}`
      });
    }

    // Accept payment if within tolerance or overpaid
    if (difference > 0 && difference <= TOLERANCE) {
      console.log(`✅ Accepting payment with small shortage of ${difference.toFixed(2)} (within tolerance)`);
    }

    // Warn if overpayment (but allow it)
    if (totalPaid > totalRequired + TOLERANCE) {
      console.log('⚠️ Warning: Overpayment detected', {
        required: totalRequired,
        paid: totalPaid,
        excess: (totalPaid - totalRequired).toFixed(2)
      });
    }

    console.log('✅ Payment validation passed');

    // ✅ CREATE PAYMENT RECORDS
    const createdPayments = [];
    for (const payment of payments) {
      const paymentStatus = payment.paymentMethod === 'mobile_money' 
        ? 'pending' 
        : 'completed';

      const paymentRecord = await TransactionPayment.create({
        transactionId: transaction.id,
        paymentMethod: payment.paymentMethod,
        amount: parseFloat(parseFloat(payment.amount).toFixed(2)),
        paymentDate: new Date(),
        status: paymentStatus,
        referenceNumber: payment.referenceNumber || `REF-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`,
        cardType: payment.cardType || null,
        last4Digits: payment.last4Digits || null,
        phoneNumber: payment.phoneNumber || null, // For mobile money
        metadata: payment.metadata || null
      }, { transaction: t });

      createdPayments.push(paymentRecord);
      console.log(`💳 Payment record created: ${payment.paymentMethod} - KES ${payment.amount}`);
    }

    // ✅ DETERMINE NEW STATUS & HANDLE STOCK
    let newStatus;
    let shouldDeductStock = false;

    if (hasMobileMoney) {
      // Keep pending for mobile money (wait for callback)
      newStatus = TRANSACTION_STATUS.PENDING_PAYMENT;
      console.log('📱 Mobile money detected - keeping status as pending_payment');
    } else {
      // Mark as authorized for immediate payments
      // newStatus = TRANSACTION_STATUS.AUTHORIZED;//OLD STATUS  WILL REVISE IT LATER

      newStatus = TRANSACTION_STATUS.PAID

      
      shouldDeductStock = true;
      console.log('✅ Immediate payment - updating to authorized and deducting stock');
    }

    // ✅ DEDUCT STOCK FOR IMMEDIATE PAYMENTS
    if (shouldDeductStock) {
      console.log('📦 Deducting stock...');
      for (const item of transaction.transactionsitems) {
        const product = await Product.findByPk(item.productId, { transaction: t });

    //     // 🔍 DEBUG HERE
    // console.log("DEBUG ITEM:", {
    //   itemId: item.id,
    //   productId: item.productId,
    //   productName: item.product?.name,
    //   quantity: item.quantity,
    //   quantityType: typeof item.quantity,
    //   rawItem: item
    // });
        
        if (!product) {
          await t.rollback();
          return res.status(400).json({
            success: false,
            message: `Product not found: ${item.productId}`
          });
        }

        const qty = Number(item.quantity);

        if (product.stock < qty) {
          await t.rollback();
          return res.status(400).json({
            success: false,
            message: `Insufficient stock for ${product.name}. Available: ${product.stock}, Required: ${qty}`
          });
        }

        await Product.decrement('stock', {
          by: item.quantity,
          where: { id: item.productId },
          transaction: t
        });

        console.log(`📦 Deducted ${item.quantity} units from product ${product.name} (ID: ${product.id})`);
      }
    }

    // ✅ UPDATE TRANSACTION
    await transaction.update({
      status: newStatus,
      paidAt: hasMobileMoney ? null : new Date(), // Only set paidAt for completed payments
      authorizedAt: shouldDeductStock ? new Date() : null
    }, { transaction: t });

    console.log(`✅ Transaction updated to status: ${newStatus}`);

    // ✅ COMMIT TRANSACTION
    await t.commit();
    console.log('✅ DATABASE TRANSACTION COMMITTED');

    // ✅ FETCH FINAL TRANSACTION STATE
    const finalTransaction = await Transaction.findByPk(transactionId, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ]
    });

    console.log('✅ PROCESS PAYMENT SUCCESS');

    res.status(200).json({
      success: true,
      message: hasMobileMoney 
        ? 'Payment initiated. Awaiting M-Pesa confirmation.' 
        : 'Payment processed successfully',
      transaction: finalTransaction,
      payments: createdPayments,
      summary: {
        totalRequired: totalRequired.toFixed(2),
        totalPaid: totalPaid.toFixed(2),
        change: Math.max(0, totalPaid - totalRequired).toFixed(2),
        status: newStatus,
        awaitingConfirmation: hasMobileMoney
      }
    });

  } catch (error) {
    console.error('🔥 ============ PAYMENT ERROR ============');
    console.error('❌ ERROR TYPE:', typeof error);
    console.error('❌ ERROR NAME:', error.name);
    console.error('❌ ERROR MESSAGE:', error.message);
    console.error('❌ ERROR STACK:', error.stack);
    console.error('🔥 ========================================');
    
    if (!t.finished) {
      console.log('🔄 Rolling back transaction...');
      await t.rollback();
    }
    
    res.status(500).json({
      success: false,
      message: 'Error processing payment',
      error: error.message,
      details: {
        name: error.name,
        timestamp: new Date().toISOString()
      }
    });
  }
};

// ============================================
// 7. COMPLETE TRANSACTION (AFTER PAYMENT)
// ============================================
exports.completeTransaction = async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    const { transactionId } = req.params;

    const transaction = await Transaction.findByPk(transactionId, {
      include: [
        {
          model: TransactionItem,
          as: 'transactionsitems',
          include: [{ model: Product, as: 'product' }]
        },
        {
          model: TransactionPayment,
          as: 'transactionpayment'
        }
      ],
      transaction: t
    });

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

    if (transaction.status !== TRANSACTION_STATUS.PAID) {
      await t.rollback();
      return res.status(400).json({
        success: false,
        message: 'Can only complete paid transactions'
      });
    }

    // Update daily sales
    const today = new Date();
    const salesDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());

    const existingDaily = await DailySales.findOne({
      where: { 
        storeId: transaction.storeId, 
        registerId: transaction.registerId, 
        salesDate 
      },
      transaction: t
    });

    // Calculate payment breakdown
    const cashSales = transaction.transactionpayment
      .filter(p => p.paymentMethod === 'cash')
      .reduce((sum, p) => sum + parseFloat(p.amount), 0);

    const cardSales = transaction.transactionpayment
      .filter(p => p.paymentMethod === 'card')
      .reduce((sum, p) => sum + parseFloat(p.amount), 0);

    const mobilePayments = transaction.transactionpayment
      .filter(p => ['mobile_money', 'mpesa'].includes(p.paymentMethod))
      .reduce((sum, p) => sum + parseFloat(p.amount), 0);

    const totalItems = transaction.transactionsitems.reduce((sum, i) => sum + i.quantity, 0);

    if (existingDaily) {
      await existingDaily.increment({
        totalTransactions: 1,
        totalItems,
        grossSales: transaction.subtotal,
        discounts: transaction.discountAmount,
        taxes: transaction.taxAmount,
        netSales: transaction.totalAmount,
        cashSales,
        cardSales,
        mobilePayments
      }, { transaction: t });
    } else {
      await DailySales.create({
        storeId: transaction.storeId,
        registerId: transaction.registerId,
        salesDate,
        totalTransactions: 1,
        totalItems,
        grossSales: transaction.subtotal,
        discounts: transaction.discountAmount,
        taxes: transaction.taxAmount,
        netSales: transaction.totalAmount,
        refunds: 0,
        cashSales,
        cardSales,
        mobilePayments
      }, { transaction: t });
    }

    // Mark as completed
    await transaction.update({
      status: TRANSACTION_STATUS.COMPLETED,
      completedAt: new Date()
    }, { transaction: t });

    await t.commit();

    res.status(200).json({
      success: true,
      message: 'Transaction completed successfully',
      transaction
    });

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





/**
 * 3. Remove item from transaction (DELETE /:transactionId/items/:itemId)
 */
exports.removeItemFromTransaction = async (req, res) => {
    const t = await sequelize.transaction();
    
    try {
        const { transactionId, itemId } = req.params;

        // 1. Find the item to be removed
        const transactionItem = await TransactionItem.findOne({
            where: {
                id: itemId,
                transactionId: transactionId
            },
            include: [{ model: Product, as: 'product' }],
            transaction: t
        });

        if (!transactionItem) {
            await t.rollback();
            return res.status(404).json({
                success: false,
                message: 'Transaction item not found or does not belong to this transaction'
            });
        }
        
        // 2. Find the parent transaction
        const transaction = await Transaction.findByPk(transactionId, { transaction: t });

        if (!transaction) {
            await t.rollback();
            return res.status(404).json({
                success: false,
                message: 'Transaction not found'
            });
        }
        
        // Check if the transaction is in a state that allows modification (e.g., 'pending' or 'draft')
        if (transaction.status === 'completed' || transaction.status === 'voided') {
            await t.rollback();
            return res.status(400).json({
                success: false,
                message: `Cannot remove item from a ${transaction.status} transaction`
            });
        }

        // 3. Restore stock (Only for completed or confirmed transactions, but based on your logic, stock is decremented
        // only on completion/confirmation. We'll assume the transaction is in a 'draft/pending' state if modifiable.
        // If it was a 'pending' mobile money transaction and stock was *not* decremented yet, no restoration is needed.
        // If your business logic dictates stock is decremented immediately, this part needs review based on transaction status.
        // Assuming stock IS NOT decremented yet if the transaction is still modifiable.

        // If the transaction status indicates stock *was* already decremented (e.g., 'completed' but you bypass the check):
        // if (transaction.status === 'completed') {
        //     await Product.increment('stock', {
        //         by: transactionItem.quantity,
        //         where: { id: transactionItem.productId },
        //         transaction: t
        //     });
        // }

        // 4. Delete the item
        await transactionItem.destroy({ transaction: t });

        // 5. Recalculate transaction totals
        const updatedTransaction = await recalculateTransactionTotals(transactionId, transaction, t);
        
        // Check if no items remain and adjust status if needed (business logic decision)
        const remainingItems = await TransactionItem.count({ where: { transactionId }, transaction: t });
        if (remainingItems === 0) {
             // Example: If no items left, mark as 'cancelled' or 'draft' with total 0
            await updatedTransaction.update({ status: 'cancelled', totalAmount: 0, subtotal: 0, taxAmount: 0, discountAmount: 0 }, { transaction: t });
        }


        // 6. Log Audit (Optional)
        await AuditLog.create({
            action: 'REMOVE_TRANSACTION_ITEM',
            entityType: 'Transaction',
            entityId: transaction.id,
            userId: req.user.id,
            details: JSON.stringify({
                itemId: itemId,
                productId: transactionItem.productId,
                quantityRemoved: transactionItem.quantity,
                oldTotal: transaction.totalAmount,
                newTotal: updatedTransaction.totalAmount,
            }),
            timestamp: new Date()
        }, { transaction: t });


        await t.commit();

        res.status(200).json({
            success: true,
            message: 'Item removed and transaction totals updated successfully',
            transaction: updatedTransaction
        });

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

/**
 * 4. Update item quantity (PATCH /:transactionId/items/:itemId)
 */
exports.updateItemQuantity = async (req, res) => {
    const t = await sequelize.transaction();
    
    try {
        const { transactionId, itemId } = req.params;
        const { quantity } = req.body;

        if (quantity === undefined || quantity === null || !Number.isInteger(Number(quantity)) || Number(quantity) <= 0) {
            await t.rollback();
            return res.status(400).json({
                success: false,
                message: 'Invalid or missing quantity'
            });
        }
        const newQuantity = parseInt(quantity);

        // 1. Find the item to be updated
        const transactionItem = await TransactionItem.findOne({
            where: {
                id: itemId,
                transactionId: transactionId
            },
            include: [{ model: Product, as: 'product' }],
            transaction: t
        });

        if (!transactionItem || !transactionItem.product) {
            await t.rollback();
            return res.status(404).json({
                success: false,
                message: 'Transaction item or associated product not found'
            });
        }
        
        // 2. Find the parent transaction
        const transaction = await Transaction.findByPk(transactionId, { transaction: t });

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

        // Check if the transaction is in a state that allows modification (e.g., 'pending' or 'draft')
        if (transaction.status === 'completed' || transaction.status === 'voided') {
            await t.rollback();
            return res.status(400).json({
                success: false,
                message: `Cannot update item in a ${transaction.status} transaction`
            });
        }

        const oldQuantity = transactionItem.quantity;
        const quantityDifference = newQuantity - oldQuantity;
        const product = transactionItem.product;

        // 3. Validate stock availability for the change
        if (quantityDifference > 0) {
            // Check if there is enough stock for the increase
            if (product.stock < quantityDifference) {
                await t.rollback();
                return res.status(400).json({
                    success: false,
                    message: `Insufficient stock for ${product.name}. Available: ${product.stock}, Requested increase: ${quantityDifference}`
                });
            }

            // Decrement stock for the *increase* if stock is managed immediately.
            // Again, assuming stock is NOT decremented yet if the transaction is still modifiable.
            // If stock IS decremented immediately, this logic would apply here:
            /* if (transaction.status === 'completed' || transaction.status === 'pending') { 
                await Product.decrement('stock', {
                    by: quantityDifference,
                    where: { id: product.id },
                    transaction: t
                });
            }
            */

        } else if (quantityDifference < 0) {
            // Stock is effectively "restored" for the decrease.
            // If stock IS decremented immediately, this logic would apply here:
            /* if (transaction.status === 'completed' || transaction.status === 'pending') { 
                await Product.increment('stock', {
                    by: Math.abs(quantityDifference),
                    where: { id: product.id },
                    transaction: t
                });
            }
            */
        }
        
        // 4. Update the item quantity and recalculate line total
        const newLineTotal = transactionItem.unitPrice * newQuantity;
        const newTaxAmount = newLineTotal * 0.16;
        
        await transactionItem.update({
            quantity: newQuantity,
            lineTotal: newLineTotal,
            taxAmount: newTaxAmount
        }, { transaction: t });

        // 5. Recalculate transaction totals
        const updatedTransaction = await recalculateTransactionTotals(transactionId, transaction, t);

        // 6. Log Audit (Optional)
        await AuditLog.create({
            action: 'UPDATE_ITEM_QUANTITY',
            entityType: 'Transaction',
            entityId: transaction.id,
            userId: req.user.id,
            details: JSON.stringify({
                itemId: itemId,
                productId: transactionItem.productId,
                oldQuantity,
                newQuantity,
                quantityDifference,
                oldTotal: transaction.totalAmount,
                newTotal: updatedTransaction.totalAmount,
            }),
            timestamp: new Date()
        }, { transaction: t });
        
        await t.commit();

        res.status(200).json({
            success: true,
            message: 'Item quantity and transaction totals updated successfully',
            transaction: updatedTransaction,
            transactionItem: transactionItem
        });

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


