const itemModel = require('../models/itemModel');
const db = require('../db/config');
const ExcelJS = require('exceljs');
const fs = require('fs');
const path = require('path');
const PDFDocument = require("pdfkit-table");

// exports.createItem = async (req, res) => {
//   const item = req.body;
//   await itemModel.createItem(item);
//   res.json({ message: 'Item created successfully' });
// };

// exports.getItems = async (req, res) => {
//   try {
//     const items = await itemModel.getAllItems();
//     res.json(items);
//   } catch (err) {
//     res.status(500).json({ error: err.message });
//   }
// };

// exports.getItem = async (req, res) => {
//   const item = await itemModel.getItemById(req.params.id);
//   if (!item) return res.status(404).json({ message: 'Item not found' });
//   res.json(item);
// };

// exports.updateItem = async (req, res) => {
//   try {
//     const item = req.body;
//     const { id } = req.params;
//     await itemModel.updateItem(id, item);
//     res.json({ message: 'Item updated successfully' });
//   } catch (err) {
//     res.status(500).json({ error: err.message });
//   }
// };

// exports.deleteItem = async (req, res) => {
//   await itemModel.deleteItem(req.params.id);
//   res.json({ message: 'Item deleted successfully' });
// };
// exports.softDeleteItem = async (req, res) => {
//   const { id } = req.params;
//   try {
//     await db.query(`UPDATE items SET status = 'deleted' WHERE id = ?`, [id]);
//     res.json({ message: "Item soft-deleted successfully" });
//   } catch (err) {
//     res.status(500).json({ error: err.message });
//   }
// }
// exports.restoreItem = async (req, res) => {
//   const { id } = req.params;
//   try {
//     await db.query(`UPDATE items SET status = 'active' WHERE id = ?`, [id]);
//     res.json({ message: "Item restored successfully" });
//   } catch (err) {
//     res.status(500).json({ error: err.message });
//   }
// };
// exports.bulkUploadItems = async (req, res) => {
//   const createdCategories = new Set();
//   const createdBrands = new Set();

//   const getOrCreateCategory = async (name) => {
//     const trimmed = name.trim();
//     const [rows] = await db.query('SELECT id FROM categories WHERE name = ?', [trimmed]);
//     if (rows.length > 0) return rows[0].id;

//     const [result] = await db.query('INSERT INTO categories (name) VALUES (?)', [trimmed]);
//     createdCategories.add(trimmed);
//     return result.insertId;
//   };

//   const getOrCreateBrand = async (name) => {
//     const trimmed = name.trim();
//     const [rows] = await db.query('SELECT id FROM brands WHERE name = ?', [trimmed]);
//     if (rows.length > 0) return rows[0].id;

//     const [result] = await db.query('INSERT INTO brands (name) VALUES (?)', [trimmed]);
//     createdBrands.add(trimmed);
//     return result.insertId;
//   };

//   async function generateNextSku() {
//     const [rows] = await db.query("SELECT sku FROM items WHERE sku LIKE 'INV%' ORDER BY id DESC LIMIT 1");
//     let latest = rows.length ? rows[0].sku : 'INV0000';
//     const num = parseInt(latest.replace('INV', ''), 10) + 1;
//     return 'INV' + String(num).padStart(4, '0');
//   }

//   try {
//     const items = req.body.items;

//     // Get current max SKU number
//     const [rows] = await db.query("SELECT sku FROM items WHERE sku LIKE 'INV%' ORDER BY id DESC LIMIT 1");
//     let lastSkuNum = 0;
//     if (rows.length > 0) {
//       const lastSku = rows[0].sku;
//       lastSkuNum = parseInt(lastSku.replace('INV', '')) || 0;
//     }

//     for (const item of items) {
//       const category_id = item.category ? await getOrCreateCategory(item.category) : null;
//       const brand_id = item.brand ? await getOrCreateBrand(item.brand) : null;

//       const sku = item.sku?.trim() || await generateNextSku();

//       await db.query(
//         `INSERT INTO items (
//           name, sku, unit, min_qty, quantity, purchase_price,sales_price, description, category_id, brand_id
//         ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
//         [
//           item.name || "",
//           sku,
//           item.unit || "",
//           item.min_qty || 0,
//           item.quantity || 0,
//           item.purchase_price || 0,
//           item.sales_price || 0,
//           item.description || "",
//           category_id,
//           brand_id
//         ]
//       );
//     }

//     res.json({
//       message: 'Items uploaded successfully.',
//       newly_created_categories: [...createdCategories],
//       newly_created_brands: [...createdBrands]
//     });
//   } catch (err) {
//     console.error(err);
//     res.status(500).json({ error: err.message });
//   }
// };

// exports.exportItemsPdf = async (req, res) => {
//   try {
//     const [items] = await db.query(`
//       SELECT 
//         items.id, items.name, items.sku, items.unit, items.min_qty, items.quantity,
//         items.sales_price, items.purchase_price, items.description,
//         categories.name AS category, brands.name AS brand
//       FROM items
//       LEFT JOIN categories ON items.category_id = categories.id
//       LEFT JOIN brands ON items.brand_id = brands.id
//     `);

//     const doc = new PDFDocument({ margin: 20, size: 'A4', layout: 'portrait' });

//     res.setHeader("Content-Disposition", "attachment; filename=item_list.pdf");
//     res.setHeader("Content-Type", "application/pdf");

//     doc.pipe(res); // stream to browser

//     // Title
//     doc.fontSize(16).text("Item List", { align: "center" });
//     doc.moveDown(1);

//     // Table content
//     const table = {
//       headers: [
//         "Name", "SKU", "Category", "Brand", "Unit", "quantity",
//         "Purchase Price", "Sales Price"
//       ],
//       rows: items.map((item) => [
//         item.name,
//         item.sku,
//         item.category || "-",
//         item.brand || "-",
//         item.unit,
//         item.quantity,
//         item.purchase_price != null ? Number(item.purchase_price).toFixed(2) : "0.00",
//         item.sales_price != null ? Number(item.sales_price).toFixed(2) : "0.00",
//       ])
//     };

//     await doc.table(table, {
//       border: true, // enable column borders
//       columnSpacing: 5,
//       padding: 4,
//       prepareHeader: () => doc.font("Helvetica-Bold").fontSize(10),
//       prepareRow: (row, i) =>
//         doc.font("Helvetica").fontSize(9).fillColor(i % 2 === 0 ? "#000" : "#444")
//     });

//     doc.end();
//   } catch (err) {
//     console.error("PDF export error:", err);
//     res.status(500).json({ error: "PDF generation failed" });
//   }
// };



// exports.exportItemsExcel = async (req, res) => {
//   try {
//     const [items] = await db.query(`
//       SELECT i.name, i.sku, i.unit, i.quantity, i.purchase_price, i.sales_price, 
//              c.name AS category, b.name AS brand 
//       FROM items i
//       LEFT JOIN categories c ON i.category_id = c.id
//       LEFT JOIN brands b ON i.brand_id = b.id
//     `);

//     const workbook = new ExcelJS.Workbook();
//     const sheet = workbook.addWorksheet('Item List');

//     sheet.columns = [
//       { header: 'Name', key: 'name' },
//       { header: 'SKU', key: 'sku' },
//       { header: 'Unit', key: 'unit' },
//       { header: 'Qty', key: 'quantity' },
//       { header: 'Purchase Price', key: 'purchase_price' },
//       { header: 'Sales Price', key: 'sales_price' },
//       { header: 'Category', key: 'category' },
//       { header: 'Brand', key: 'brand' },
//     ];

//     items.forEach(item => sheet.addRow(item));

//     const filePath = path.join(__dirname, '../exports/item_list.xlsx');
//     await workbook.xlsx.writeFile(filePath);
//     res.download(filePath, 'item_list.xlsx', () => fs.unlinkSync(filePath));

//   } catch (err) {
//     console.error(err);
//     res.status(500).json({ error: 'Failed to export Excel' });
//   }
// };

// exports.exportItemsPrint = async (req, res) => {
//   try {
//     const [items] = await db.query(`
//       SELECT i.name, i.sku, i.unit, i.quantity, i.purchase_price, i.sales_price, 
//              c.name AS category, b.name AS brand 
//       FROM items i
//       LEFT JOIN categories c ON i.category_id = c.id
//       LEFT JOIN brands b ON i.brand_id = b.id
//     `);

//     const html = `
//       <html>
//         <head>
//           <title>Item List</title>
//           <style>
//             table { border-collapse: collapse; width: 100%; font-family: sans-serif; }
//             th, td { border: 1px solid #ccc; padding: 8px; text-align: left; }
//             th { background: #f0f0f0; }
//           </style>
//         </head>
//         <body>
//           <h2>Item List</h2>
//           <table>
//             <thead>
//               <tr>
//                 <th>Name</th>
//                 <th>SKU</th>
//                 <th>Category</th>
//                 <th>Brand</th>
//                 <th>Unit</th>
//                 <th>Qty</th>
//                 <th>Purchase Price</th>
//                 <th>Sales Price</th>
//               </tr>
//             </thead>
//             <tbody>
//               ${items.map(item => `
//                 <tr>
//                   <td>${item.name}</td>
//                   <td>${item.sku}</td>
//                   <td>${item.category || ''}</td>
//                   <td>${item.brand || ''}</td>
//                   <td>${item.unit}</td>
//                   <td>${item.quantity}</td>
//                   <td>${item.purchase_price}</td>
//                   <td>${item.sales_price}</td>
//                 </tr>`).join('')}
//             </tbody>
//           </table>
//           <script>window.print()</script>
//         </body>
//       </html>
//     `;

//     res.setHeader('Content-Type', 'text/html');
//     res.send(html);

//   } catch (err) {
//     console.error(err);
//     res.status(500).json({ error: 'Failed to generate printable view' });
//   }
// };


// New Item API


exports.getItem = async (req, res) => {
  try {
    const item = await itemModel.getItemWithSizes(req.params.id);
    res.json(item);
  } catch (error) {
    console.error('Error fetching item:', error);
    res.status(500).json({ error: 'Failed to fetch item' });
  }
};
exports.getAllItems = async (req, res) => {
  try {
    const items = await itemModel.getAllItemsWithSizes();
    res.json(items);
  } catch (error) {
    console.error('Error fetching items:', error);
    res.status(500).json({ error: 'Failed to fetch items' });
  }
};
exports.createItem = async (req, res) => {
  try {
    const {
      name,
      sku,
      unit,
      purchase_price_avg,
      sales_price,
      category_id,
      brand_id,
      description,
      image,
      min_qty,
      sizes
    } = req.body;

    const itemData = {
      name,
      sku,
      unit,
      purchase_price_avg,
      sales_price,
      category_id,
      brand_id,
      description,
      image,
      min_qty
    };

    const itemId = await itemModel.createItemWithSizes(itemData, sizes);
    res.status(201).json({ message: 'Item created', itemId });
  } catch (error) {
    console.error('Error creating item:', error);
    res.status(500).json({ error: 'Failed to create item' });
  }
};


exports.updateItem = async (req, res) => {
  try {
    const { name, sku, unit, purchase_price_avg, sales_price, sizes, image } = req.body;
    const itemData = { name, sku, unit, purchase_price_avg, sales_price, image  };
    await itemModel.updateItemWithSizes(req.params.id, itemData, sizes);
    res.json({ message: 'Item updated' });
  } catch (error) {
    console.error('Error updating item:', error);
    res.status(500).json({ error: 'Failed to update item' });
  }
};