const db = require('../config/db');

// Get items by category with their order
const getItemOrder = async (req, res) => {
  try {
    const { category_id } = req.params;
    
    if (!category_id) {
      return res.status(400).json({ success: false, msg: 'Category ID is required' });
    }

    const [rows] = await db.query(`
      SELECT i.id, i.name, i.image, COALESCE(io.display_order, i.id) as display_order
      FROM items i
      LEFT JOIN item_order io ON i.id = io.item_id AND io.category_id = ?
      WHERE i.category_id = ? AND i.status = 1
      ORDER BY COALESCE(io.display_order, i.id) ASC
    `, [category_id, category_id]);
    
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching item order:', error);
    res.status(500).json({ success: false, msg: 'Error fetching item order' });
  }
};

// Update item order
const updateItemOrder = async (req, res) => {
  try {
    const { category_id } = req.params;
    const { orders } = req.body; // Array of { item_id, display_order }
    
    if (!category_id) {
      return res.status(400).json({ success: false, msg: 'Category ID is required' });
    }
    
    if (!Array.isArray(orders)) {
      return res.status(400).json({ success: false, msg: 'Orders array is required' });
    }

    // Start transaction
    await db.query('START TRANSACTION');

    try {
      // Clear existing orders for this category
      await db.query('DELETE FROM item_order WHERE category_id = ?', [category_id]);

      // Insert new orders
      for (const order of orders) {
        await db.query(
          'INSERT INTO item_order (category_id, item_id, display_order) VALUES (?, ?, ?)',
          [category_id, order.item_id, order.display_order]
        );
      }

      await db.query('COMMIT');
      res.json({ success: true, msg: 'Item order updated successfully' });
    } catch (error) {
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('Error updating item order:', error);
    res.status(500).json({ success: false, msg: 'Error updating item order' });
  }
};

// Reset to original order
const resetItemOrder = async (req, res) => {
  try {
    const { category_id } = req.params;
    
    if (!category_id) {
      return res.status(400).json({ success: false, msg: 'Category ID is required' });
    }

    await db.query('DELETE FROM item_order WHERE category_id = ?', [category_id]);
    res.json({ success: true, msg: 'Item order reset to original order' });
  } catch (error) {
    console.error('Error resetting item order:', error);
    res.status(500).json({ success: false, msg: 'Error resetting item order' });
  }
};

module.exports = {
  getItemOrder,
  updateItemOrder,
  resetItemOrder
};
