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

// Get all categories with their order
const getCategoryOrder = async (req, res) => {
  try {
    const [rows] = await db.query(`
      SELECT c.id, c.name, c.image, COALESCE(co.display_order, c.id) as display_order
      FROM categories c
      LEFT JOIN category_order co ON c.id = co.category_id
      WHERE c.status = 1
      ORDER BY COALESCE(co.display_order, c.id) ASC
    `);
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching category order:', error);
    res.status(500).json({ success: false, msg: 'Error fetching category order' });
  }
};

// Update category order
const updateCategoryOrder = async (req, res) => {
  try {
    const { orders } = req.body; // Array of { category_id, display_order }
    
    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
      await db.query('DELETE FROM category_order');

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

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

// Reset to original order
const resetCategoryOrder = async (req, res) => {
  try {
    await db.query('DELETE FROM category_order');
    res.json({ success: true, msg: 'Category order reset to original order' });
  } catch (error) {
    console.error('Error resetting category order:', error);
    res.status(500).json({ success: false, msg: 'Error resetting category order' });
  }
};

module.exports = {
  getCategoryOrder,
  updateCategoryOrder,
  resetCategoryOrder
};
