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

// exports.createItem = async (item) => {
//   const [result] = await db.query(
//     `INSERT INTO items 
//       (name, sku, description, qty, purchase_price, sales_price, image, unit, min_qty, category_id, brand_id) 
//      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
//     [
//       item.name,
//       item.sku || null,
//       item.description || null,
//       item.qty || 0,
//       item.purchase_price || 0,
//       item.sales_price || 0,
//       item.image || null,
//       item.unit || null,
//       item.min_qty || 0,
//       item.category_id || null,
//       item.brand_id || null
//     ]
//   );
//   return result;
// };

// exports.getAllItems = async () => {
//   const [rows] = await db.query(`
//     SELECT 
//       i.*, 
//       c.name AS category_name,
//       b.name AS brand_name
//     FROM items i
//     LEFT JOIN categories c ON i.category_id = c.id
//     LEFT JOIN brands b ON i.brand_id = b.id
//     WHERE status = 'active'
//   `);
//   return rows;
// };
// exports.getAllWithPurchasePrices = () => {
//   return db.query(
//     'SELECT id, name, purchase_price_avg, purchase_price_last FROM items ORDER BY name'
//   );
// };
// exports.getItemById = async (id) => {
//   const [rows] = await db.query('SELECT * FROM items WHERE id = ?', [id]);
//   return rows[0];
// };

// exports.updateItem = async (id, item) => {
//   const [result] = await db.query(
//     `UPDATE items SET 
//       name = ?, 
//       sku = ?, 
//       description = ?, 
//       qty = ?, 
//       purchase_price = ?, 
//       sales_price = ?, 
//       image = ?, 
//       unit = ?, 
//       min_qty = ?, 
//       category_id = ?, 
//       brand_id = ?
//      WHERE id = ?`,
//     [
//       item.name,
//       item.sku || null,
//       item.description || null,
//       item.qty || 0,
//       item.purchase_price || 0,
//       item.sales_price || 0,
//       item.image || null,
//       item.unit || null,
//       item.min_qty || 0,
//       item.category_id || null,
//       item.brand_id || null,
//       id
//     ]
//   );
//   return result;
// };;

// exports.deleteItem = async (id) => {
//   const [result] = await db.query('DELETE FROM items WHERE id = ?', [id]);
//   return result;
// };

// new items API

exports.getItemWithSizes = async (itemId) => {
  const [[item]] = await db.query('SELECT * FROM items WHERE id = ?', [itemId]);
  const [sizes] = await db.query('SELECT size, quantity FROM item_sizes WHERE item_id = ?', [itemId]);

  item.sizes = sizes;
  item.total_quantity = sizes.reduce((sum, s) => sum + s.quantity, 0);

  return item;
};
exports.getAllItemsWithSizes = async () => {
  const [items] = await db.query(
    `SELECT i.*, c.name AS category_name, b.name AS brand_name
     FROM items i
     LEFT JOIN categories c ON i.category_id = c.id
     LEFT JOIN brands b ON i.brand_id = b.id`
  );

  const [allSizes] = await db.query('SELECT item_id, size, quantity FROM item_sizes');
  const sizesMap = allSizes.reduce((map, s) => {
    if (!map[s.item_id]) map[s.item_id] = [];
    map[s.item_id].push({ size: s.size, quantity: s.quantity });
    return map;
  }, {});

  return items.map(item => {
    const sizes = sizesMap[item.id] || [];
    return {
      ...item,
      sizes,
      total_quantity: sizes.reduce((sum, s) => sum + s.quantity, 0),
      category_name: item.category_name || null,
      brand_name: item.brand_name || null
    };
  });
};
exports.createItemWithSizes = async (itemData, sizes) => {
  const totalQty = sizes.reduce((sum, s) => sum + Number(s.quantity || 0), 0);

  const [result] = await db.query(
    `INSERT INTO items 
      (name, sku, description, qty, purchase_price, sales_price, image, unit, min_qty, category_id, brand_id) 
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      itemData.name,
      itemData.sku || null,
      itemData.description || null,
      totalQty,
      itemData.purchase_price || 0,
      itemData.sales_price || 0,
      itemData.image || null,
      itemData.unit || null,
      itemData.min_qty || 0,
      itemData.category_id || null,
      itemData.brand_id || null
    ]
  );

  const itemId = result.insertId;

  for (const s of sizes) {
    await db.query(
      'INSERT INTO item_sizes (item_id, size, quantity) VALUES (?, ?, ?)',
      [itemId, s.size, s.quantity]
    );
  }

  return itemId;
};

exports.updateItemWithSizes = async (itemId, itemData, sizes) => {
  const totalQty = sizes.reduce((sum, s) => sum + Number(s.quantity || 0), 0);

  await db.query(
    `UPDATE items SET 
      name = ?, sku = ?, description = ?, qty = ?, purchase_price = ?, 
      sales_price = ?, image = ?, unit = ?, min_qty = ?, category_id = ?, brand_id = ?
     WHERE id = ?`,
    [
      itemData.name,
      itemData.sku || null,
      itemData.description || null,
      totalQty,
      itemData.purchase_price || 0,
      itemData.sales_price || 0,
      itemData.image || null,
      itemData.unit || null,
      itemData.min_qty || 0,
      itemData.category_id || null,
      itemData.brand_id || null,
      itemId,
      itemData.image  || null,
    ]
  );

  await db.query('DELETE FROM item_sizes WHERE item_id = ?', [itemId]);

  for (const s of sizes) {
    await db.query(
      'INSERT INTO item_sizes (item_id, size, quantity) VALUES (?, ?, ?)',
      [itemId, s.size, s.quantity]
    );
  }

  return true;
};