const { Return, ReturnItem, Transaction, TransactionItem, Customer, Store, Register, User, Product, Inventory, Refund, sequelize } = require('../models');
const { Op } = require('sequelize');
const { validationResult } = require('express-validator');

/**
 * Helper: Generate unique return number
 */
const generateReturnNumber = async (storeId) => {
  const date = new Date();
  const year = date.getFullYear().toString().slice(-2);
  const month = (date.getMonth() + 1).toString().padStart(2, '0');
  const day = date.getDate().toString().padStart(2, '0');
  
  const prefix = `RET${year}${month}${day}`;
  
  const lastReturn = await Return.findOne({
    where: {
      returnNumber: {
        [Op.like]: `${prefix}%`
      }
    },
    order: [['createdAt', 'DESC']]
  });

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

  return `${prefix}${sequence.toString().padStart(4, '0')}`;
};

/**
 * Helper: Get already returned quantity for a transaction item
 */
const getReturnedQuantity = async (transactionItemId, transaction = null) => {
  const returnItems = await ReturnItem.findAll({
    where: { transactionItemId },
    include: [{
      model: Return,
      as: 'return',
      where: {
        status: {
          [Op.in]: ['pending', 'approved', 'completed']
        }
      }
    }],
    transaction
  });

  return returnItems.reduce((total, item) => total + item.quantity, 0);
};

/**
 * Helper: Restock inventory
 */
const restockInventory = async (productId, storeId, quantity, transaction) => {
  const inventory = await Inventory.findOne({
    where: { productId, storeId },
    transaction
  });

  if (inventory) {
    await inventory.update({
      quantity: parseFloat(inventory.quantity) + quantity,
      lastRestockedAt: new Date()
    }, { transaction });
  } else {
    await Inventory.create({
      productId,
      storeId,
      quantity,
      lastRestockedAt: new Date()
    }, { transaction });
  }
};

/**
 * Create a new return
 * POST /api/returns
 */
exports.createReturn = async (req, res) => {

  let transaction;
  
  try {

    transaction = await sequelize.transaction();
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      await transaction.rollback();
      return res.status(400).json({ 
        success: false, 
        errors: errors.array() 
      });
    }

    const {
      transactionId,
      customerId,
      
      reason,
      product,
      items,
      refundMethod,
      notes
    } = req.body;


    const storeId = parseInt(items[0].storeId, 10);
    const registerId = parseInt(items[0].registerId, 10);

    console.log('THE STORE AND REGISTER ID IS:', storeId, registerId);

    console.log('THE RETURNED BODY:', items);

    const userId = req.user.id;

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

    console.log('THE ORIGINAL TRANSCATION DATA IS',originalTransaction)

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

    const purchaseDate = new Date(originalTransaction.createdAt);
    const currentDate = new Date();
    const daysFromPurchase = Math.floor((currentDate - purchaseDate) / (1000 * 60 * 60 * 24));
    const returnWindowDays = 30;
    const isWithinReturnWindow = daysFromPurchase <= returnWindowDays;

    const returnNumber = await generateReturnNumber(storeId);

    let subtotal = 0;
    let taxAmount = 0;
    const returnItems = [];

    for (const item of items) {
      const transactionItem = originalTransaction.transactionsitems.find(
        ti => ti.id === item.transactionItemId
      );

      if (!transactionItem) {
        await transaction.rollback();
        return res.status(400).json({
          success: false,
          message: `Transaction item ${item.transactionItemId} not found`
        });
      }

      const alreadyReturned = await getReturnedQuantity(item.transactionItemId, transaction);
      const availableForReturn = transactionItem.quantity - alreadyReturned;

      if (item.quantity > availableForReturn) {
        await transaction.rollback();
        return res.status(400).json({
          success: false,
          message: `Cannot return ${item.quantity} units of ${transactionItem.product.name}. Only ${availableForReturn} available for return.`
        });
      }

      const itemSubtotal = parseFloat(transactionItem.price) * item.quantity;
      const itemTax = parseFloat(transactionItem.taxAmount || 0) * (item.quantity / transactionItem.quantity);
      
      subtotal += itemSubtotal;
      taxAmount += itemTax;

      returnItems.push({
        transactionItemId: item.transactionItemId,
        productId: transactionItem.productId,
        quantity: item.quantity,
        unitPrice: transactionItem.price,
        subtotal: itemSubtotal,
        taxAmount: itemTax,
        totalAmount: itemSubtotal + itemTax,
        reason: item.reason || reason,
        condition: item.condition || 'good'
      });
    }

    let restockingFee = 0;
    if (!isWithinReturnWindow) {
      restockingFee = subtotal * 0.15;
    }

    const totalAmount = subtotal + taxAmount;
    const refundAmount = totalAmount - restockingFee;


    // const returnType = items.length === originalTransaction.tra.length ? 'full' : 'partial';

    const originalItemsCount = originalTransaction.transactionsitems.length;
    const returnItemsCount = items.length;

const returnType = returnItemsCount === originalItemsCount
  ? "full"
  : "partial";

    const returnRecord = await Return.create({
      transactionId,
      returnNumber,
      customerId,
      storeId,
      registerId,
      reason,
      returnType,
      status: 'pending',
      subtotal,
      taxAmount,
      totalAmount,
      restockingFee,
      refundMethod,
      refundAmount,
      processedBy: userId,
      processedAt: new Date(),
      notes,
      isWithinReturnWindow,
      daysFromPurchase
    }, { transaction });

    await Promise.all(
      returnItems.map(item => 
        ReturnItem.create({
          returnId: returnRecord.id,
          ...item
        }, { transaction })
      )
    );

 

    const completeReturn = await Return.findByPk(returnRecord.id, {
      include: [
        { model: Customer, as: 'Customer' },
        { model: Store, as: 'Store' },
        { model: Register, as: 'Register' },
        { model: User, as: 'Processor', attributes: ['id', 'firstName', 'lastName', 'email'] },
        { model: User, as: 'Approver', attributes: ['id', 'firstName', 'lastName', 'email'] },
        { 
          model: ReturnItem, 
          as: 'ReturnItems',
          include: [{ model: Product, as: 'product' }]
        }
        // { model: Transaction, as: 'originalTransaction' }
      ]
    });
    
    await transaction.commit();
    return res.status(201).json({
      success: true,
      message: 'Return created successfully',
      data: completeReturn
    });

  } catch (error) {
    // await transaction.rollback();
    console.error('Create return error:', error);

    // prevent double rollback crash
    if (transaction&& !transaction.finished) {
      await transaction.rollback();
    }
    return res.status(500).json({
      success: false,
      message: 'Failed to create return',
      error: error.message
    });
  }
};

/**
 * Get all returns with filters
 * GET /api/returns
 */
exports.getReturns = async (req, res) => {
  try {
    const {
      page = 1,
      limit = 20,
      status,
      storeId,
      customerId,
      startDate,
      endDate,
      returnNumber,
      search
    } = req.query;

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

    if (status) where.status = status;
    if (storeId) where.storeId = storeId;
    if (customerId) where.customerId = customerId;
    if (returnNumber) where.returnNumber = { [Op.like]: `%${returnNumber}%` };

    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) {
        const endDateTime = new Date(endDate);
        endDateTime.setHours(23, 59, 59, 999);
        where.createdAt[Op.lte] = endDateTime;
      }
    }

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

    const { count, rows } = await Return.findAndCountAll({
      where,
      limit: parseInt(limit),
      offset,
      order: [['createdAt', 'DESC']],
      include: [
        { model: Customer, as: 'Customer' },
        { model: Store, as: 'Store' },
        { model: User, as: 'Processor', attributes: ['id', 'firstName', 'lastName'] },
        { 
          model: ReturnItem, 
          as: 'ReturnItems',
          include: [{ model: Product, as: 'product' }]
        }
      ]
    });

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

  } catch (error) {
    console.error('Get returns error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch returns',
      error: error.message
    });
  }
};

/**
 * Get single return by ID
 * GET /api/returns/:id
 */
exports.getReturnById = async (req, res) => {
  try {
    const { id } = req.params;

    const returnRecord = await Return.findByPk(id, {
      include: [
        { model: Customer, as: 'Customer' },
        { model: Store, as: 'Store' },
        { model: Register, as: 'Register' },
        { 
          model: User, 
          as: 'Processor', 
          attributes: ['id', 'firstName', 'lastName', 'email'] 
        },
        { 
          model: User, 
          as: 'Approver', 
          attributes: ['id', 'firstName', 'lastName', 'email'] 
        },
        { 
          model: ReturnItem, 
          as: 'ReturnItems',
          include: [
            { model: Product, as: 'product' },
            { model: TransactionItem, as: 'transactionItem' }
          ]
        },
        // { 
        //   model: Transaction, 
        //   as: 'originalTransaction',
        //   attributes: ['id', 'transactionNumber', 'totalAmount', 'createdAt']
        // },
        // { model: Refund, as: 'refund' }
      ]
    });

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

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

  } catch (error) {
    console.error('Get return by ID error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch return',
      error: error.message
    });
  }
};

/**
 * Approve return
 * PUT /api/returns/:id/approve
 */

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

  try {
    const { id } = req.params;
    const { notes, restockItems = true } = req.body;
    const userId = req.user.id;

    const returnRecord = await Return.findByPk(id, {
      include: [
        { model: ReturnItem, as: 'ReturnItems' },
        { model: Customer, as: 'Customer' },
        { model: Store, as: 'Store' }
      ],
      transaction
    });

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

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

    await returnRecord.update({
      status: 'approved',
      approvedBy: userId,
      approvedAt: new Date(),
      notes: notes || returnRecord.notes
    }, { transaction });

    if (restockItems) {
      for (const item of returnRecord.ReturnItems) {
        await restockInventory(
          item.productId,
          returnRecord.storeId,
          item.quantity,
          transaction
        );
      }
    }

    await transaction.commit();

    const updatedReturn = await Return.findByPk(id, {
      include: [
        { model: Customer, as: 'Customer' },
        { model: Store, as: 'Store' },
        { model: User, as: 'Processor', attributes: ['id', 'firstName', 'lastName'] },
        { model: User, as: 'Approver', attributes: ['id', 'firstName', 'lastName'] },
        { model: ReturnItem, as: 'ReturnItems', include: [{ model: Product, as: 'product' }] }
      ]
    });

    return res.status(200).json({
      success: true,
      message: 'Return approved successfully',
      data: updatedReturn
    });

  } catch (error) {
    // Safely rollback only if transaction is still active
    try {
      await transaction.rollback();
    } catch (rollbackError) {
      // Transaction was already finished, ignore the error
      console.log('Transaction already finished, skipping rollback');
    }
    
    console.error('Approve return error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to approve return',
      error: error.message
    });
  }
};
// exports.approveReturn = async (req, res) => {
//   const transaction = await sequelize.transaction();

//   try {
//     const { id } = req.params;
//     const { notes, restockItems = true } = req.body;
//     const userId = req.user.id;

//     const returnRecord = await Return.findByPk(id, {
//       include: [
//         { model: ReturnItem, as: 'ReturnItems' },
//         { model: Customer, as: 'Customer' },
//         { model: Store, as: 'Store' }
//       ],
//       transaction
//     });

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

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

//     await returnRecord.update({
//       status: 'approved',
//       approvedBy: userId,
//       approvedAt: new Date(),
//       notes: notes || returnRecord.notes
//     }, { transaction });

//     if (restockItems) {
//       for (const item of returnRecord.ReturnItems) {
//         await restockInventory(
//           item.productId,
//           returnRecord.storeId,
//           item.quantity,
//           transaction
//         );
//       }
//     }

//     await transaction.commit();

//     const updatedReturn = await Return.findByPk(id, {
//       include: [
//         { model: Customer, as: 'customer' },
//         { model: Store, as: 'Store' },
//         { model: User, as: 'Processor', attributes: ['id', 'firstName', 'lastName'] },
//         { model: User, as: 'Approver', attributes: ['id', 'firstName', 'lastName'] },
//         { model: ReturnItem, as: 'ReturnItem', include: [{ model: Product, as: 'product' }] }
//       ]
//     });

//     return res.status(200).json({
//       success: true,
//       message: 'Return approved successfully',
//       data: updatedReturn
//     });

//   } catch (error) {
//     await transaction.rollback();
//     console.error('Approve return error:', error);
//     return res.status(500).json({
//       success: false,
//       message: 'Failed to approve return',
//       error: error.message
//     });
//   }
// };

/**
 * Reject return
 * PUT /api/returns/:id/reject
 */
exports.rejectReturn = async (req, res) => {
  try {
    const { id } = req.params;
    const { rejectionReason } = req.body;
    const userId = req.user.id;

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

    const returnRecord = await Return.findByPk(id);

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

    if (returnRecord.status !== 'pending') {
      return res.status(400).json({
        success: false,
        message: `Cannot reject return with status: ${returnRecord.status}`
      });
    }

    await returnRecord.update({
      status: 'rejected',
      approvedBy: userId,
      approvedAt: new Date(),
      rejectionReason
    });

    const updatedReturn = await Return.findByPk(id, {
      include: [
        { model: Customer, as: 'customer' },
        { model: User, as: 'approver', attributes: ['id', 'firstName', 'lastName'] },
        { model: ReturnItem, as: 'items', include: [{ model: Product, as: 'product' }] }
      ]
    });

    return res.status(200).json({
      success: true,
      message: 'Return rejected',
      data: updatedReturn
    });

  } catch (error) {
    console.error('Reject return error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to reject return',
      error: error.message
    });
  }
};

/**
 * Complete return with refund
 * PUT /api/returns/:id/complete
 */
exports.completeReturn = async (req, res) => {
  const transaction = await sequelize.transaction();
  let committed = false;

  try {
    const { id } = req.params;
    const { refundMethod, refundAmount, paymentReference } = req.body;
    const userId = req.user.id;

    const returnRecord = await Return.findByPk(id, {
      include: [{ model: ReturnItem, as: 'ReturnItems' }],
      transaction
    });

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

    if (returnRecord.status !== 'approved') {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Return must be approved before completion'
      });
    }

    const finalRefundAmount = refundAmount || returnRecord.refundAmount;

    await Refund.create({
      returnId: returnRecord.id,
      transactionId: returnRecord.transactionId,
      customerId: returnRecord.customerId,
      refundNumber: `REF${Date.now()}`,
      amount: finalRefundAmount,
      method: refundMethod || returnRecord.refundMethod,
      status: 'completed',
      paymentReference,
      processedBy: userId,
      processedAt: new Date()
    }, { transaction });

    await returnRecord.update({
      status: 'completed',
      refundMethod: refundMethod || returnRecord.refundMethod,
      refundAmount: finalRefundAmount
    }, { transaction });

    await transaction.commit();
    committed = true;

    const completedReturn = await Return.findByPk(id, {
      include: [
        { model: Customer, as: 'Customer' },
        // { model: Refund, as: 'Refund' },
        { model: ReturnItem, as: 'ReturnItems', include: [{ model: Product, as: 'product' }] }
      ]
    });

    return res.status(200).json({
      success: true,
      message: 'Return completed successfully',
      data: completedReturn
    });

  } catch (error) {
    if (!committed) {
      await transaction.rollback();
    }
    
    console.error('Complete return error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to complete return',
      error: error.message
    });
  }
};

/**
 * Cancel return
 * PUT /api/returns/:id/cancel
 */
exports.cancelReturn = async (req, res) => {
  try {
    const { id } = req.params;
    const { reason } = req.body;

    const returnRecord = await Return.findByPk(id);

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

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

    await returnRecord.update({
      status: 'cancelled',
      notes: reason || returnRecord.notes
    });

    return res.status(200).json({
      success: true,
      message: 'Return cancelled successfully',
      data: returnRecord
    });

  } catch (error) {
    console.error('Cancel return error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to cancel return',
      error: error.message
    });
  }
};

/**
 * Get return statistics
 * GET /api/returns/stats
 */
exports.getReturnStatistics = async (req, res) => {
  try {
    const { storeId, startDate, endDate } = req.query;
    const where = {};

    if (storeId) where.storeId = storeId;
    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) {
        const endDateTime = new Date(endDate);
        endDateTime.setHours(23, 59, 59, 999);
        where.createdAt[Op.lte] = endDateTime;
      }
    }

    const [
      totalReturns,
      pendingReturns,
      approvedReturns,
      rejectedReturns,
      completedReturns,
      totalRefundAmount
    ] = await Promise.all([
      Return.count({ where }),
      Return.count({ where: { ...where, status: 'pending' } }),
      Return.count({ where: { ...where, status: 'approved' } }),
      Return.count({ where: { ...where, status: 'rejected' } }),
      Return.count({ where: { ...where, status: 'completed' } }),
      Return.sum('refundAmount', { where: { ...where, status: 'completed' } })
    ]);

    const topReasons = await Return.findAll({
      where,
      attributes: [
        'reason',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count']
      ],
      group: ['reason'],
      order: [[sequelize.literal('count'), 'DESC']],
      limit: 10,
      raw: true
    });

    const thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

    const returnsByDay = await Return.findAll({
      where: {
        ...where,
        createdAt: {
          [Op.gte]: thirtyDaysAgo
        }
      },
      attributes: [
        [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount']
      ],
      group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
      order: [[sequelize.fn('DATE', sequelize.col('createdAt')), 'ASC']],
      raw: true
    });

    return res.status(200).json({
      success: true,
      data: {
        totalReturns,
        pendingReturns,
        approvedReturns,
        rejectedReturns,
        completedReturns,
        totalRefundAmount: totalRefundAmount || 0,
        topReasons,
        returnsByDay
      }
    });

  } catch (error) {
    console.error('Get return statistics error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch return statistics',
      error: error.message
    });
  }
};

/**
 * Update return
 * PUT /api/returns/:id
 */
exports.updateReturn = async (req, res) => {
  try {
    const { id } = req.params;
    const updateData = req.body;

    const returnRecord = await Return.findByPk(id);

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

    if (returnRecord.status === 'completed') {
      return res.status(400).json({
        success: false,
        message: 'Cannot update completed return'
      });
    }

    const allowedFields = ['reason', 'notes', 'refundMethod', 'restockingFee'];
    const filteredData = {};
    
    allowedFields.forEach(field => {
      if (updateData[field] !== undefined) {
        filteredData[field] = updateData[field];
      }
    });

    await returnRecord.update(filteredData);

    const updatedReturn = await Return.findByPk(id, {
      include: [
        { model: Customer, as: 'customer' },
        { model: ReturnItem, as: 'items', include: [{ model: Product, as: 'product' }] }
      ]
    });

    return res.status(200).json({
      success: true,
      message: 'Return updated successfully',
      data: updatedReturn
    });

  } catch (error) {
    console.error('Update return error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to update return',
      error: error.message
    });
  }
};

/**
 * Delete return (soft delete)
 * DELETE /api/returns/:id
 */
exports.deleteReturn = async (req, res) => {
  try {
    const { id } = req.params;

    const returnRecord = await Return.findByPk(id);

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

    if (returnRecord.status === 'completed') {
      return res.status(400).json({
        success: false,
        message: 'Cannot delete completed return'
      });
    }

    await returnRecord.destroy();

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

  } catch (error) {
    console.error('Delete return error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to delete return',
      error: error.message
    });
  }
};

/**
 * Batch approve returns
 * POST /api/returns/batch/approve
 */
exports.batchApproveReturns = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { returnIds, restockItems = true } = req.body;
    const userId = req.user.id;

    if (!Array.isArray(returnIds) || returnIds.length === 0) {
      await transaction.rollback();
      return res.status(400).json({
        success: false,
        message: 'Return IDs array is required'
      });
    }

    const returns = await Return.findAll({
      where: {
        id: { [Op.in]: returnIds },
        status: 'pending'
      },
      include: [{ model: ReturnItem, as: 'items' }],
      transaction
    });

    if (returns.length === 0) {
      await transaction.rollback();
      return res.status(404).json({
        success: false,
        message: 'No pending returns found'
      });
    }

    const approvedReturns = [];

    for (const returnRecord of returns) {
      await returnRecord.update({
        status: 'approved',
        approvedBy: userId,
        approvedAt: new Date()
      }, { transaction });

      if (restockItems) {
        for (const item of returnRecord.items) {
          await restockInventory(
            item.productId,
            returnRecord.storeId,
            item.quantity,
            transaction
          );
        }
      }

      approvedReturns.push(returnRecord.id);
    }

    await transaction.commit();

    return res.status(200).json({
      success: true,
      message: `${approvedReturns.length} returns approved successfully`,
      data: { approvedReturns }
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Batch approve returns error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to batch approve returns',
      error: error.message
    });
  }
};

/**
 * Check if item can be returned
 * GET /api/returns/check/:transactionId/:transactionItemId
 */
exports.checkReturnEligibility = async (req, res) => {
  try {
    const { transactionId, transactionItemId } = req.params;

    const transactionItem = await TransactionItem.findOne({
      where: {
        id: transactionItemId,
        transactionId
      },
      include: [
        { model: Product, as: 'product' },
        { model: Transaction, as: 'transaction' }
      ]
    });

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

    const alreadyReturned = await getReturnedQuantity(transactionItemId);
    const availableForReturn = transactionItem.quantity - alreadyReturned;

    const purchaseDate = new Date(transactionItem.transaction.createdAt);
    const currentDate = new Date();
    const daysFromPurchase = Math.floor((currentDate - purchaseDate) / (1000 * 60 * 60 * 24));
    const returnWindowDays = 30;
    const isWithinReturnWindow = daysFromPurchase <= returnWindowDays;

    return res.status(200).json({
      success: true,
      data: {
        canReturn: availableForReturn > 0 && isWithinReturnWindow,
        availableQuantity: availableForReturn,
        originalQuantity: transactionItem.quantity,
        alreadyReturned,
        isWithinReturnWindow,
        daysFromPurchase,
        returnWindowDays,
        product: transactionItem.product
      }
    });

  } catch (error) {
    console.error('Check return eligibility error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to check return eligibility',
      error: error.message
    });
  }
};

/**
 * Get returns for a specific customer
 * GET /api/returns/customer/:customerId
 */
exports.getCustomerReturns = async (req, res) => {
  try {
    const { customerId } = req.params;
    const { page = 1, limit = 20, status } = req.query;

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

    if (status) where.status = status;

    const { count, rows } = await Return.findAndCountAll({
      where,
      limit: parseInt(limit),
      offset,
      order: [['createdAt', 'DESC']],
      include: [
        { model: Store, as: 'store' },
        { 
          model: ReturnItem, 
          as: 'items',
          include: [{ model: Product, as: 'product' }]
        },
        { model: Refund, as: 'refund' }
      ]
    });

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

  } catch (error) {
    console.error('Get customer returns error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch customer returns',
      error: error.message
    });
  }
};

/**
 * Generate return receipt
 * GET /api/returns/:id/receipt
 */
exports.generateReturnReceipt = async (req, res) => {
  try {
    const { id } = req.params;

    const returnRecord = await Return.findByPk(id, {
      include: [
        { model: Customer, as: 'Customer' },
        { model: Store, as: 'Store' },
        { model: Register, as: 'Register' },
        { model: User, as: 'Processor', attributes: ['firstName', 'lastName'] },
        { 
          model: ReturnItem, 
          as: 'ReturnItems',
          include: [{ model: Product, as: 'product' }]
        },
        { model: Transaction, as: 'originalTransaction' },
        { model: Refund, as: 'refund' }
      ]
    });

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

    const receipt = {
      returnNumber: returnRecord.returnNumber,
      date: returnRecord.createdAt,
      status: returnRecord.status,
      store: {
        name: returnRecord.store?.name,
        address: returnRecord.store?.address,
        phone: returnRecord.store?.phone
      },
      customer: returnRecord.customer ? {
        name: `${returnRecord.customer.firstName} ${returnRecord.customer.lastName}`,
        email: returnRecord.customer.email,
        phone: returnRecord.customer.phone
      } : null,
      originalTransaction: {
        transactionNumber: returnRecord.originalTransaction?.transactionNumber,
        date: returnRecord.originalTransaction?.createdAt
      },
      items: returnRecord.items.map(item => ({
        name: item.product?.name,
        sku: item.product?.sku,
        quantity: item.quantity,
        unitPrice: parseFloat(item.unitPrice).toFixed(2),
        subtotal: parseFloat(item.subtotal).toFixed(2),
        reason: item.reason
      })),
      totals: {
        subtotal: parseFloat(returnRecord.subtotal).toFixed(2),
        tax: parseFloat(returnRecord.taxAmount).toFixed(2),
        restockingFee: parseFloat(returnRecord.restockingFee || 0).toFixed(2),
        total: parseFloat(returnRecord.totalAmount).toFixed(2),
        refundAmount: parseFloat(returnRecord.refundAmount).toFixed(2)
      },
      refund: returnRecord.refund ? {
        method: returnRecord.refund.method,
        amount: parseFloat(returnRecord.refund.amount).toFixed(2),
        reference: returnRecord.refund.paymentReference,
        processedAt: returnRecord.refund.processedAt
      } : null,
      processor: returnRecord.processor ? 
        `${returnRecord.processor.firstName} ${returnRecord.processor.lastName}` : null,
      reason: returnRecord.reason,
      notes: returnRecord.notes
    };

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

  } catch (error) {
    console.error('Generate return receipt error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to generate return receipt',
      error: error.message
    });
  }
};

/**
 * Export returns to CSV
 * GET /api/returns/export/csv
 */
exports.exportReturnsToCSV = async (req, res) => {
  try {
    const { startDate, endDate, status, storeId } = req.query;
    const where = {};

    if (status) where.status = status;
    if (storeId) where.storeId = storeId;
    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) {
        const endDateTime = new Date(endDate);
        endDateTime.setHours(23, 59, 59, 999);
        where.createdAt[Op.lte] = endDateTime;
      }
    }

    const returns = await Return.findAll({
      where,
      include: [
        { model: Customer, as: 'customer' },
        { model: Store, as: 'store' },
        { model: User, as: 'processor', attributes: ['firstName', 'lastName'] }
      ],
      order: [['createdAt', 'DESC']]
    });

    const headers = [
      'Return Number',
      'Date',
      'Customer',
      'Store',
      'Status',
      'Subtotal',
      'Tax',
      'Restocking Fee',
      'Total',
      'Refund Amount',
      'Refund Method',
      'Reason',
      'Processed By',
      'Days From Purchase',
      'Within Window'
    ].join(',');

    const rows = returns.map(r => {
      const customerName = r.customer ? 
        `${r.customer.firstName} ${r.customer.lastName}` : 'Walk-in';
      const processorName = r.processor ? 
        `${r.processor.firstName} ${r.processor.lastName}` : 'N/A';
      
      return [
        r.returnNumber,
        new Date(r.createdAt).toISOString(),
        `"${customerName}"`,
        `"${r.store?.name || 'N/A'}"`,
        r.status,
        parseFloat(r.subtotal).toFixed(2),
        parseFloat(r.taxAmount).toFixed(2),
        parseFloat(r.restockingFee || 0).toFixed(2),
        parseFloat(r.totalAmount).toFixed(2),
        parseFloat(r.refundAmount || 0).toFixed(2),
        r.refundMethod || 'N/A',
        `"${r.reason.replace(/"/g, '""')}"`,
        `"${processorName}"`,
        r.daysFromPurchase || 0,
        r.isWithinReturnWindow ? 'Yes' : 'No'
      ].join(',');
    }).join('\n');

    const csv = `${headers}\n${rows}`;

    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', `attachment; filename=returns_${new Date().toISOString().split('T')[0]}.csv`);
    return res.status(200).send(csv);

  } catch (error) {
    console.error('Export returns to CSV error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to export returns',
      error: error.message
    });
  }
};

/**
 * Get returns by date range with aggregations
 * GET /api/returns/reports/date-range
 */
exports.getReturnsByDateRange = async (req, res) => {
  try {
    const { startDate, endDate, storeId, groupBy = 'day' } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({
        success: false,
        message: 'Start date and end date are required'
      });
    }

    const where = {
      createdAt: {
        [Op.gte]: new Date(startDate),
        [Op.lte]: new Date(endDate)
      }
    };

    if (storeId) where.storeId = storeId;

    let dateFormat;
    switch (groupBy) {
      case 'month':
        dateFormat = '%Y-%m';
        break;
      case 'week':
        dateFormat = '%Y-%U';
        break;
      case 'day':
      default:
        dateFormat = '%Y-%m-%d';
        break;
    }

    const returns = await Return.findAll({
      where,
      attributes: [
        [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat), 'period'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount'],
        [sequelize.fn('SUM', sequelize.col('refundAmount')), 'totalRefund'],
        [sequelize.fn('AVG', sequelize.col('daysFromPurchase')), 'avgDaysFromPurchase']
      ],
      group: [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat)],
      order: [[sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat), 'ASC']],
      raw: true
    });

    const statusBreakdown = await Return.findAll({
      where,
      attributes: [
        'status',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('refundAmount')), 'totalRefund']
      ],
      group: ['status'],
      raw: true
    });

    return res.status(200).json({
      success: true,
      data: {
        returns,
        statusBreakdown,
        summary: {
          totalReturns: returns.reduce((sum, r) => sum + parseInt(r.count), 0),
          totalAmount: returns.reduce((sum, r) => sum + parseFloat(r.totalAmount || 0), 0),
          totalRefund: returns.reduce((sum, r) => sum + parseFloat(r.totalRefund || 0), 0)
        }
      }
    });

  } catch (error) {
    console.error('Get returns by date range error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch returns report',
      error: error.message
    });
  }
};

/**
 * Get top returned products
 * GET /api/returns/reports/top-products
 */
exports.getTopReturnedProducts = async (req, res) => {
  try {
    const { startDate, endDate, storeId, limit = 10 } = req.query;
    const where = {};

    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) {
        const endDateTime = new Date(endDate);
        endDateTime.setHours(23, 59, 59, 999);
        where.createdAt[Op.lte] = endDateTime;
      }
    }

    if (storeId) where.storeId = storeId;

    const topProducts = await ReturnItem.findAll({
      attributes: [
        'productId',
        [sequelize.fn('SUM', sequelize.col('quantity')), 'totalQuantity'],
        [sequelize.fn('COUNT', sequelize.col('ReturnItem.id')), 'returnCount'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount']
      ],
      include: [
        {
          model: Product,
          as: 'product',
          attributes: ['id', 'name', 'sku', 'category']
        },
        {
          model: Return,
          as: 'return',
          attributes: [],
          where
        }
      ],
      group: ['productId', 'product.id'],
      order: [[sequelize.literal('totalQuantity'), 'DESC']],
      limit: parseInt(limit),
      raw: true,
      nest: true
    });

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

  } catch (error) {
    console.error('Get top returned products error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch top returned products',
      error: error.message
    });
  }
};

/**
 * Get return rate analysis
 * GET /api/returns/reports/return-rate
 */
exports.getReturnRateAnalysis = async (req, res) => {
  try {
    const { startDate, endDate, storeId } = req.query;

    if (!startDate || !endDate) {
      return res.status(400).json({
        success: false,
        message: 'Start date and end date are required'
      });
    }

    const where = {
      createdAt: {
        [Op.gte]: new Date(startDate),
        [Op.lte]: new Date(endDate)
      }
    };

    if (storeId) where.storeId = storeId;

    const totalTransactions = await Transaction.count({
      where: {
        createdAt: where.createdAt,
        ...(storeId && { storeId })
      }
    });

    const totalReturns = await Return.count({ where });

    const returnsByReason = await Return.findAll({
      where,
      attributes: [
        'reason',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount']
      ],
      group: ['reason'],
      order: [[sequelize.literal('count'), 'DESC']],
      raw: true
    });

    const returnsByCategory = await ReturnItem.findAll({
      attributes: [
        [sequelize.col('product.category'), 'category'],
        [sequelize.fn('COUNT', sequelize.col('ReturnItem.id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('quantity')), 'totalQuantity']
      ],
      include: [
        {
          model: Product,
          as: 'product',
          attributes: []
        },
        {
          model: Return,
          as: 'return',
          attributes: [],
          where
        }
      ],
      group: [sequelize.col('product.category')],
      order: [[sequelize.literal('count'), 'DESC']],
      raw: true
    });

    const returnRate = totalTransactions > 0 
      ? ((totalReturns / totalTransactions) * 100).toFixed(2)
      : 0;

    return res.status(200).json({
      success: true,
      data: {
        totalTransactions,
        totalReturns,
        returnRate: parseFloat(returnRate),
        returnsByReason,
        returnsByCategory
      }
    });

  } catch (error) {
    console.error('Get return rate analysis error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch return rate analysis',
      error: error.message
    });
  }
};

/**
 * Get refund summary
 * GET /api/returns/reports/refund-summary
 */
exports.getRefundSummary = async (req, res) => {
  try {
    const { startDate, endDate, storeId } = req.query;
    const where = {};

    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) {
        const endDateTime = new Date(endDate);
        endDateTime.setHours(23, 59, 59, 999);
        where.createdAt[Op.lte] = endDateTime;
      }
    }

    if (storeId) where.storeId = storeId;

    const refundsByMethod = await Return.findAll({
      where: {
        ...where,
        status: 'completed'
      },
      attributes: [
        'refundMethod',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('refundAmount')), 'totalAmount']
      ],
      group: ['refundMethod'],
      raw: true
    });

    const totalRestockingFees = await Return.sum('restockingFee', {
      where: {
        ...where,
        status: 'completed'
      }
    });

    const avgRefundAmount = await Return.findOne({
      where: {
        ...where,
        status: 'completed'
      },
      attributes: [
        [sequelize.fn('AVG', sequelize.col('refundAmount')), 'avgAmount']
      ],
      raw: true
    });

    const refundsByDay = await Return.findAll({
      where: {
        ...where,
        status: 'completed'
      },
      attributes: [
        [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('refundAmount')), 'totalAmount']
      ],
      group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
      order: [[sequelize.fn('DATE', sequelize.col('createdAt')), 'ASC']],
      raw: true
    });

    return res.status(200).json({
      success: true,
      data: {
        refundsByMethod,
        totalRestockingFees: totalRestockingFees || 0,
        avgRefundAmount: parseFloat(avgRefundAmount?.avgAmount || 0).toFixed(2),
        refundsByDay
      }
    });

  } catch (error) {
    console.error('Get refund summary error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch refund summary',
      error: error.message
    });
  }
};

/**
 * Process automatic approval for eligible returns
 * POST /api/returns/auto-approve
 */
exports.autoApproveEligibleReturns = async (req, res) => {
  const transaction = await sequelize.transaction();

  try {
    const { maxAmount = 1000, maxDaysFromPurchase = 7 } = req.body;
    const userId = req.user.id;

    const eligibleReturns = await Return.findAll({
      where: {
        status: 'pending',
        isWithinReturnWindow: true,
        daysFromPurchase: { [Op.lte]: maxDaysFromPurchase },
        totalAmount: { [Op.lte]: maxAmount }
      },
      include: [{ model: ReturnItem, as: 'items' }],
      transaction
    });

    if (eligibleReturns.length === 0) {
      await transaction.rollback();
      return res.status(200).json({
        success: true,
        message: 'No eligible returns found for auto-approval',
        data: { approvedCount: 0 }
      });
    }

    for (const returnRecord of eligibleReturns) {
      await returnRecord.update({
        status: 'approved',
        approvedBy: userId,
        approvedAt: new Date(),
        notes: `Auto-approved (amount: ${returnRecord.totalAmount}, days: ${returnRecord.daysFromPurchase})`
      }, { transaction });

      for (const item of returnRecord.items) {
        await restockInventory(
          item.productId,
          returnRecord.storeId,
          item.quantity,
          transaction
        );
      }
    }

    await transaction.commit();

    return res.status(200).json({
      success: true,
      message: `${eligibleReturns.length} returns auto-approved successfully`,
      data: {
        approvedCount: eligibleReturns.length,
        approvedIds: eligibleReturns.map(r => r.id)
      }
    });

  } catch (error) {
    await transaction.rollback();
    console.error('Auto-approve returns error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to auto-approve returns',
      error: error.message
    });
  }
};

/**
 * Get return timeline/history
 * GET /api/returns/:id/timeline
 */
exports.getReturnTimeline = async (req, res) => {
  try {
    const { id } = req.params;

    const returnRecord = await Return.findByPk(id, {
      include: [
        { 
          model: User, 
          as: 'processor', 
          attributes: ['id', 'firstName', 'lastName', 'email'] 
        },
        { 
          model: User, 
          as: 'approver', 
          attributes: ['id', 'firstName', 'lastName', 'email'] 
        }
      ]
    });

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

    const timeline = [];

    timeline.push({
      event: 'created',
      timestamp: returnRecord.createdAt,
      user: returnRecord.processor ? {
        id: returnRecord.processor.id,
        name: `${returnRecord.processor.firstName} ${returnRecord.processor.lastName}`
      } : null,
      details: `Return ${returnRecord.returnNumber} created`
    });

    if (returnRecord.approvedAt) {
      timeline.push({
        event: returnRecord.status === 'rejected' ? 'rejected' : 'approved',
        timestamp: returnRecord.approvedAt,
        user: returnRecord.approver ? {
          id: returnRecord.approver.id,
          name: `${returnRecord.approver.firstName} ${returnRecord.approver.lastName}`
        } : null,
        details: returnRecord.status === 'rejected' 
          ? `Return rejected: ${returnRecord.rejectionReason}`
          : 'Return approved'
      });
    }

    if (returnRecord.status === 'completed') {
      timeline.push({
        event: 'completed',
        timestamp: returnRecord.updatedAt,
        details: `Refund processed: Ksh.${parseFloat(returnRecord.refundAmount).toFixed(2)}`
      });
    }

    timeline.sort((a, b) => new Date(a.timestamp) - new Date(b.timestamp));

    return res.status(200).json({
      success: true,
      data: {
        returnId: returnRecord.id,
        returnNumber: returnRecord.returnNumber,
        currentStatus: returnRecord.status,
        timeline
      }
    });

  } catch (error) {
    console.error('Get return timeline error:', error);
    return res.status(500).json({
      success: false,
      message: 'Failed to fetch return timeline',
      error: error.message
    });
  }
};