const express = require('express');
const { authenticateToken, requireAdmin } = require('../middleware/auth');
const { uploadProductImage, handleUploadError, deleteFile, getFileUrl } = require('../middleware/upload');
const { getDb, returnToPool } = require('../database/init');
const { validationResult, body, param } = require('express-validator');
const path = require('path');

const router = express.Router();

// Validation rules
const idValidation = [param('id').isInt({ min: 1 }).withMessage('Valid product ID is required')];

const createProductValidation = [
  body('name').trim().isLength({ min: 2, max: 100 }).withMessage('Product name must be between 2 and 100 characters'),
  body('description').optional().trim().isLength({ max: 1000 }).withMessage('Description must be less than 1000 characters'),
  body('price').isFloat({ min: 0 }).withMessage('Valid price is required'),
  body('category_id').isInt({ min: 1 }).withMessage('Valid category is required'),
  body('current_stock').optional().isInt({ min: 0 }).withMessage('Current stock must be a non-negative integer'),
  body('status').optional().isIn(['active', 'inactive']).withMessage('Status must be active or inactive')
];

const updateProductValidation = [
  body('name').optional().trim().isLength({ min: 2, max: 100 }).withMessage('Product name must be between 2 and 100 characters'),
  body('description').optional().trim().isLength({ max: 1000 }).withMessage('Description must be less than 1000 characters'),
  body('price').optional().isFloat({ min: 0 }).withMessage('Valid price is required'),
  body('category_id').optional().isInt({ min: 1 }).withMessage('Valid category is required'),
  body('current_stock').optional().isInt({ min: 0 }).withMessage('Current stock must be a non-negative integer'),
  body('status').optional().isIn(['active', 'inactive']).withMessage('Status must be active or inactive')
];

// Helper to parse JSON image field into URLs
const parseImages = (rows, req) => {
  return rows.map(product => {
    let images = [];
    try {
      images = JSON.parse(product.images || '[]').map(img => getFileUrl(req, img, 'products'));
    } catch (e) {
      console.warn(`Failed to parse images for product ${product.id}:`, e);
    }
    return { ...product, images };
  });
};

// GET /products (admin or user)
router.get('/', authenticateToken, async (req, res) => {
  let connection;
  try {
    connection = await getDb();
    const [products] = await connection.query(
      `SELECT p.*, c.name as category_name FROM products p
       LEFT JOIN categories c ON p.category_id = c.id`
    );
    returnToPool(connection);
    res.json({ products: parseImages(products, req) });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to fetch products' });
  }
});

// GET /products/public
router.get('/public', async (req, res) => {
  let connection;
  try {
    connection = await getDb();
    const [products] = await connection.query(
      `SELECT p.*, c.name as category_name FROM products p
       LEFT JOIN categories c ON p.category_id = c.id
       WHERE p.status = 'active' AND p.current_stock > 0`
    );
    returnToPool(connection);
    res.json({ products: parseImages(products, req) });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to fetch public products' });
  }
});

// GET /products/:id
router.get('/:id', async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    connection = await getDb();
    const [rows] = await connection.query(
      `SELECT p.*, c.name as category_name FROM products p
       LEFT JOIN categories c ON p.category_id = c.id
       WHERE p.id = ?`, [id]
    );
    returnToPool(connection);
    if (!rows.length) return res.status(404).json({ error: 'Product not found' });
    res.json({ product: parseImages(rows, req)[0] });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to fetch product' });
  }
});

// POST /products
router.post('/', authenticateToken, requireAdmin, uploadProductImage, handleUploadError, createProductValidation, async (req, res) => {
  let connection;
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      if (req.file) deleteFile(req.file.path);
      return res.status(400).json({ errors: errors.array() });
    }

    const { name, description, price, category_id, current_stock = 0, status = 'active' } = req.body;
    const imagesJson = req.file ? JSON.stringify([req.file.filename]) : null;

    connection = await getDb();
    const [result] = await connection.query(
      `INSERT INTO products (name, description, price, category_id, current_stock, status, images, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [name, description, price, category_id, current_stock, status, imagesJson]
    );

    const [newProductRows] = await connection.query(
      `SELECT p.*, c.name as category_name FROM products p
       LEFT JOIN categories c ON p.category_id = c.id WHERE p.id = ?`,
      [result.insertId]
    );

    returnToPool(connection);
    res.status(201).json({
      message: 'Product created successfully',
      product: parseImages(newProductRows, req)[0]
    });
  } catch (err) {
    if (connection) returnToPool(connection);
    if (req.file) deleteFile(req.file.path);
    res.status(500).json({ error: 'Server error while creating product' });
  }
});

// PUT /products/:id
router.put('/:id', authenticateToken, requireAdmin, [...idValidation, ...updateProductValidation], async (req, res) => {
  let connection;
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) return res.status(400).json({ errors: errors.array() });

    const { id } = req.params;
    const { name, description, price, category_id, current_stock, status } = req.body;
    connection = await getDb();

    const [rows] = await connection.query('SELECT * FROM products WHERE id = ?', [id]);
    if (rows.length === 0) {
      returnToPool(connection);
      return res.status(404).json({ error: 'Product not found' });
    }

    const updateFields = [];
    const values = [];
    if (name !== undefined) { updateFields.push('name = ?'); values.push(name); }
    if (description !== undefined) { updateFields.push('description = ?'); values.push(description); }
    if (price !== undefined) { updateFields.push('price = ?'); values.push(price); }
    if (category_id !== undefined) { updateFields.push('category_id = ?'); values.push(category_id); }
    if (current_stock !== undefined) { updateFields.push('current_stock = ?'); values.push(current_stock); }
    if (status !== undefined) { updateFields.push('status = ?'); values.push(status); }

    if (updateFields.length === 0) {
      returnToPool(connection);
      return res.status(400).json({ error: 'No fields to update' });
    }

    updateFields.push('updated_at = NOW()');
    const sql = `UPDATE products SET ${updateFields.join(', ')} WHERE id = ?`;
    values.push(id);
    await connection.query(sql, values);

    const [updatedRows] = await connection.query(
      `SELECT p.*, c.name as category_name FROM products p
       LEFT JOIN categories c ON p.category_id = c.id
       WHERE p.id = ?`, [id]
    );

    returnToPool(connection);
    res.json({ message: 'Product updated', product: parseImages(updatedRows, req)[0] });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Error updating product' });
  }
});

// DELETE /products/:id
router.delete('/:id', authenticateToken, requireAdmin, idValidation, async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    connection = await getDb();
    const [rows] = await connection.query('SELECT images FROM products WHERE id = ?', [id]);
    if (!rows.length) {
      returnToPool(connection);
      return res.status(404).json({ error: 'Product not found' });
    }

    await connection.query('DELETE FROM products WHERE id = ?', [id]);
    returnToPool(connection);

    const images = JSON.parse(rows[0].images || '[]');
    for (const img of images) {
      deleteFile(path.join(__dirname, '..', 'uploads', 'products', img));
    }

    res.json({ message: 'Product deleted successfully' });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to delete product' });
  }
});


// POST /products/:id/image
router.post('/:id/image', authenticateToken, requireAdmin, idValidation, uploadProductImage, handleUploadError, async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    connection = await getDb();

    const [rows] = await connection.query('SELECT images FROM products WHERE id = ?', [id]);
    if (rows.length === 0) {
      if (req.file) deleteFile(req.file.path);
      returnToPool(connection);
      return res.status(404).json({ error: 'Product not found' });
    }

    const currentImages = JSON.parse(rows[0].images || '[]');
    currentImages.push(req.file.filename);
    await connection.query('UPDATE products SET images = ?, updated_at = NOW() WHERE id = ?', [JSON.stringify(currentImages), id]);
    returnToPool(connection);

    res.json({ message: 'Image uploaded successfully' });
  } catch (err) {
    if (req.file) deleteFile(req.file.path);
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Image upload failed' });
  }
});

// DELETE /products/:id/images
router.delete('/:id/images', authenticateToken, requireAdmin, idValidation, async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    connection = await getDb();

    const [rows] = await connection.query('SELECT images FROM products WHERE id = ?', [id]);
    if (rows.length === 0) {
      returnToPool(connection);
      return res.status(404).json({ error: 'Product not found' });
    }

    const images = JSON.parse(rows[0].images || '[]');
    for (const img of images) {
      deleteFile(path.join(__dirname, '..', 'uploads', 'products', img));
    }

    await connection.query('UPDATE products SET images = ?, updated_at = NOW() WHERE id = ?', [JSON.stringify([]), id]);
    returnToPool(connection);

    res.json({ message: 'All images deleted successfully' });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to delete images' });
  }
});

// GET /products/debug/database (for debugging)
router.get('/debug/database', async (req, res) => {
  let connection;
  try {
    connection = await getDb();
    const [rows] = await connection.query('SELECT * FROM products');
    returnToPool(connection);
    res.json({ products: rows });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Database debug error' });
  }
});


module.exports = router;


// const express = require('express');
// const { authenticateToken, requireAdmin } = require('../middleware/auth');
// const { uploadProductImage, uploadProductImages, handleUploadError, deleteFile, getFileUrl } = require('../middleware/upload');
// const { getDb, returnToPool } = require('../database/init');
// const path = require('path');
// const fs = require('fs');
// const { validationResult, body, param } = require('express-validator');

// const router = express.Router();
// const DB_PATH = process.env.DB_PATH || './database/usasya.db';

// // Validation rules
// const idValidation = [
//   param('id').isInt({ min: 1 }).withMessage('Valid product ID is required')
// ];

// const createProductValidation = [
//   body('name').trim().isLength({ min: 2, max: 100 }).withMessage('Product name must be between 2 and 100 characters'),
//   body('description').optional().trim().isLength({ max: 1000 }).withMessage('Description must be less than 1000 characters'),
//   body('price').isFloat({ min: 0 }).withMessage('Valid price is required'),
//   body('category_id').isInt({ min: 1 }).withMessage('Valid category is required'),
//   body('current_stock').optional().isInt({ min: 0 }).withMessage('Current stock must be a non-negative integer'),
//   body('status').optional().isIn(['active', 'inactive']).withMessage('Status must be active or inactive')
// ];

// const updateProductValidation = [
//   body('name').optional().trim().isLength({ min: 2, max: 100 }).withMessage('Product name must be between 2 and 100 characters'),
//   body('description').optional().trim().isLength({ max: 1000 }).withMessage('Description must be less than 1000 characters'),
//   body('price').optional().isFloat({ min: 0 }).withMessage('Valid price is required'),
//   body('category_id').optional().isInt({ min: 1 }).withMessage('Valid category is required'),
//   body('current_stock').optional().isInt({ min: 0 }).withMessage('Current stock must be a non-negative integer'),
//   body('status').optional().isIn(['active', 'inactive']).withMessage('Status must be active or inactive')
// ];

// // Helper to parse images from JSON string
// const parseImages = (rows, req) => {
//   return rows.map(p => {
//     const { images, ...product } = p;
//     console.log('Raw images data for product', p.id, ':', images);
    
//     let parsedImages = [];
//     if (images) {
//       try {
//         const imageArray = JSON.parse(images);
//         parsedImages = imageArray.map(img => getFileUrl(req, img, 'products'));
//         console.log('Parsed images for product', p.id, ':', parsedImages);
//       } catch (error) {
//         console.error('Error parsing images for product', p.id, ':', error);
//         parsedImages = [];
//       }
//     }
    
//     return {
//       ...product,
//       images: parsedImages
//     };
//   });
// };

// // Get all products
// router.get('/', authenticateToken, async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
//     const {
//       category_id, status, search, sort_by = 'created_at', sort_order = 'DESC',
//       limit = 50, offset = 0
//     } = req.query;

//     const allowedSortFields = ['name', 'price', 'created_at', 'category_name', 'current_stock', 'status'];
//     const sortField = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
//     const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

//     let baseQuery = `FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE 1=1`;
//     const params = [];
//     const countParams = [];

//     if (category_id) {
//       baseQuery += ' AND p.category_id = ?';
//       params.push(category_id);
//       countParams.push(category_id);
//     }
//     if (status) {
//       baseQuery += ' AND p.status = ?';
//       params.push(status);
//       countParams.push(status);
//     }
//     if (search) {
//       baseQuery += ' AND (p.name LIKE ? OR p.description LIKE ? OR c.name LIKE ?)';
//       const searchTerm = `%${search}%`;
//       params.push(searchTerm, searchTerm, searchTerm);
//       countParams.push(searchTerm, searchTerm, searchTerm);
//     }

//     let finalQuery = 'SELECT p.*, c.name as category_name ' + baseQuery;
//     finalQuery += sortField === 'category_name' ? ` ORDER BY c.name ${sortDirection}, p.name ASC` : ` ORDER BY p.${sortField} ${sortDirection}`;
//     finalQuery += ' LIMIT ? OFFSET ?';
//     params.push(parseInt(limit), parseInt(offset));

//     const rows = await new Promise((resolve, reject) => {
//       db.all(finalQuery, params, (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     const products = parseImages(rows, req);
//     const countQuery = 'SELECT COUNT(*) as total ' + baseQuery;
    
//     const countRow = await new Promise((resolve, reject) => {
//       db.get(countQuery, countParams, (err, countRow) => {
//         if (err) reject(err);
//         else resolve(countRow);
//       });
//     });

//     returnToPool(db);

//     res.json({
//       products,
//       total: countRow.total,
//       page: Math.floor(offset / limit) + 1,
//       totalPages: Math.ceil(countRow.total / limit)
//     });

//   } catch (error) {
//     console.error('Error fetching products:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Failed to fetch products' });
//   }
// });

// // Get public products
// router.get('/public', async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
//     const {
//       category_id, search, sort_by = 'created_at', sort_order = 'DESC',
//       limit = 20, offset = 0, min_price, max_price
//     } = req.query;

//     const allowedSortFields = ['name', 'price', 'created_at', 'category_name'];
//     const sortField = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
//     const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

//     let baseQuery = `FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.status = 'active' AND p.current_stock > 0`;
//     const params = [];
//     const countParams = [];

//     if (category_id) {
//       baseQuery += ' AND p.category_id = ?';
//       params.push(category_id);
//       countParams.push(category_id);
//     }
//     if (search) {
//       baseQuery += ' AND (p.name LIKE ? OR p.description LIKE ? OR c.name LIKE ?)';
//       const searchTerm = `%${search}%`;
//       params.push(searchTerm, searchTerm, searchTerm);
//       countParams.push(searchTerm, searchTerm, searchTerm);
//     }
//     if (min_price) {
//       baseQuery += ' AND p.price >= ?';
//       params.push(parseFloat(min_price));
//       countParams.push(parseFloat(min_price));
//     }
//     if (max_price) {
//       baseQuery += ' AND p.price <= ?';
//       params.push(parseFloat(max_price));
//       countParams.push(parseFloat(max_price));
//     }

//     let finalQuery = 'SELECT p.*, c.name as category_name ' + baseQuery;
//     finalQuery += sortField === 'category_name' ? ` ORDER BY c.name ${sortDirection}, p.name ASC` : ` ORDER BY p.${sortField} ${sortDirection}`;
//     finalQuery += ' LIMIT ? OFFSET ?';
//     params.push(parseInt(limit), parseInt(offset));

//     const rows = await new Promise((resolve, reject) => {
//       db.all(finalQuery, params, (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     const products = parseImages(rows, req);
//     const countQuery = 'SELECT COUNT(*) as total ' + baseQuery;
    
//     const countRow = await new Promise((resolve, reject) => {
//       db.get(countQuery, countParams, (err, countRow) => {
//         if (err) reject(err);
//         else resolve(countRow);
//       });
//     });

//     returnToPool(db);

//     res.json({
//       products,
//       pagination: {
//         total: countRow.total,
//         limit: parseInt(limit),
//         offset: parseInt(offset),
//         has_more: countRow.total > (parseInt(offset) + parseInt(limit))
//       },
//       filters: { category_id, search, sort_by: sortField, sort_order: sortDirection, min_price, max_price }
//     });

//   } catch (error) {
//     console.error('Error fetching public products:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Failed to fetch public products.' });
//   }
// });

// // Get product by ID
// router.get('/:id', async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
//     const productId = req.params.id;
//     const query = `
//       SELECT p.*, c.name as category_name
//       FROM products p
//       LEFT JOIN categories c ON p.category_id = c.id
//       WHERE p.id = ?`;

//     const row = await new Promise((resolve, reject) => {
//       db.get(query, [productId], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     returnToPool(db);

//     if (!row) return res.status(404).json({ error: 'Product not found' });
//     const product = parseImages([row], req)[0];
//     res.json({ product });

//   } catch (error) {
//     console.error('Error fetching product:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Failed to fetch product' });
//   }
// });

// // Create new product (admin only)
// router.post('/', authenticateToken, requireAdmin, uploadProductImage, handleUploadError, createProductValidation, async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { name, description, price, category_id, current_stock, status = 'active' } = req.body;
//     db = await getDb();

//     // Check if category exists
//     const category = await new Promise((resolve, reject) => {
//       db.get('SELECT id FROM categories WHERE id = ?', [category_id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!category) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       returnToPool(db);
//       return res.status(400).json({ error: 'Invalid category' });
//     }

//     // Insert new product
//     const imagesData = req.file ? JSON.stringify([req.file.filename]) : null;
//     console.log('Storing images data:', imagesData);
    
//     const result = await new Promise((resolve, reject) => {
//       db.run(
//         'INSERT INTO products (name, description, price, category_id, current_stock, status, images, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)',
//         [name, description, price, category_id, current_stock, status, imagesData],
//         function(err) {
//           if (err) reject(err);
//           else resolve({ id: this.lastID });
//         }
//       );
//     });

//     // Get created product with category info
//     const newProduct = await new Promise((resolve, reject) => {
//       db.get(
//         `SELECT p.*, c.name as category_name 
//          FROM products p 
//          LEFT JOIN categories c ON p.category_id = c.id 
//          WHERE p.id = ?`,
//         [result.id],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     returnToPool(db);

//     res.status(201).json({
//       message: 'Product created successfully',
//       product: {
//         ...newProduct,
//         images: newProduct.images ? JSON.parse(newProduct.images).map(img => getFileUrl(req, img, 'products')) : []
//       }
//     });

//   } catch (error) {
//     console.error('Error creating product:', error);
//     if (db) returnToPool(db);
//     if (req.file) {
//       deleteFile(req.file.path);
//     }
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Update product (admin only)
// router.put('/:id', authenticateToken, requireAdmin, uploadProductImage, handleUploadError, [...idValidation, ...updateProductValidation], async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     const { name, description, price, category_id, current_stock, status } = req.body;
//     db = await getDb();

//     // Check if product exists
//     const existingProduct = await new Promise((resolve, reject) => {
//       db.get('SELECT images FROM products WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!existingProduct) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       returnToPool(db);
//       return res.status(404).json({ error: 'Product not found' });
//     }

//     // Check if category exists (if updating category)
//     if (category_id) {
//       const category = await new Promise((resolve, reject) => {
//         db.get('SELECT id FROM categories WHERE id = ?', [category_id], (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         });
//       });

//       if (!category) {
//         if (req.file) {
//           deleteFile(req.file.path);
//         }
//         returnToPool(db);
//         return res.status(400).json({ error: 'Invalid category' });
//       }
//     }

//     // Handle image update
//     let imagesToUpdate = existingProduct.images;
//     if (req.file) {
//       // Delete old images if exists
//       if (existingProduct.images) {
//         const oldImages = JSON.parse(existingProduct.images);
//         for (const img of oldImages) {
//           deleteFile(path.join(__dirname, '..', 'uploads', 'products', img));
//         }
//       }
//       imagesToUpdate = JSON.stringify([req.file.filename]);
//     }

//     // Prepare update fields
//     const updateFields = [];
//     const updateValues = [];

//     if (name !== undefined) {
//       updateFields.push('name = ?');
//       updateValues.push(name);
//     }

//     if (description !== undefined) {
//       updateFields.push('description = ?');
//       updateValues.push(description);
//     }

//     if (price !== undefined) {
//       updateFields.push('price = ?');
//       updateValues.push(price);
//     }

//     if (category_id !== undefined) {
//       updateFields.push('category_id = ?');
//       updateValues.push(category_id);
//     }

//     if (current_stock !== undefined) {
//       updateFields.push('current_stock = ?');
//       updateValues.push(current_stock);
//     }

//     if (status !== undefined) {
//       updateFields.push('status = ?');
//       updateValues.push(status);
//     }

//     if (imagesToUpdate !== existingProduct.images) {
//       updateFields.push('images = ?');
//       updateValues.push(imagesToUpdate);
//     }

//     // Always update the updated_at timestamp
//     updateFields.push('updated_at = CURRENT_TIMESTAMP');

//     if (updateFields.length === 1) { // Only updated_at
//       returnToPool(db);
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ error: 'No fields to update' });
//     }

//     // Build and execute update query
//     const updateQuery = `UPDATE products SET ${updateFields.join(', ')} WHERE id = ?`;
//     updateValues.push(id);

//     await new Promise((resolve, reject) => {
//       db.run(updateQuery, updateValues, function(err) {
//         if (err) reject(err);
//         else resolve({ changes: this.changes });
//       });
//     });

//     // Get updated product with category info
//     const updatedProduct = await new Promise((resolve, reject) => {
//       db.get(
//         `SELECT p.*, c.name as category_name 
//          FROM products p 
//          LEFT JOIN categories c ON p.category_id = c.id 
//          WHERE p.id = ?`,
//         [id],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     returnToPool(db);

//     res.json({
//       message: 'Product updated successfully',
//       product: {
//         ...updatedProduct,
//         images: updatedProduct.images ? JSON.parse(updatedProduct.images).map(img => getFileUrl(req, img, 'products')) : []
//       }
//     });

//   } catch (error) {
//     console.error('Error updating product:', error);
//     if (db) returnToPool(db);
//     if (req.file) {
//       deleteFile(req.file.path);
//     }
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Delete product (admin only)
// router.delete('/:id', authenticateToken, requireAdmin, idValidation, async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     db = await getDb();

//     // Check if product exists and get images
//     const existingProduct = await new Promise((resolve, reject) => {
//       db.get('SELECT images FROM products WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!existingProduct) {
//       returnToPool(db);
//       return res.status(404).json({ error: 'Product not found' });
//     }

//     // Delete product
//     await new Promise((resolve, reject) => {
//       db.run('DELETE FROM products WHERE id = ?', [id], function(err) {
//         if (err) reject(err);
//         else resolve({ changes: this.changes });
//       });
//     });

//     returnToPool(db);

//     // Delete image files if exists
//     if (existingProduct.images) {
//       const images = JSON.parse(existingProduct.images);
//       for (const img of images) {
//         deleteFile(path.join(__dirname, '..', 'uploads', 'products', img));
//       }
//     }

//     res.json({ message: 'Product deleted successfully' });

//   } catch (error) {
//     console.error('Error deleting product:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Upload product image
// router.post('/:id/image', authenticateToken, requireAdmin, uploadProductImage, handleUploadError, idValidation, async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ errors: errors.array() });
//     }

//     if (!req.file) {
//       return res.status(400).json({ error: 'No file uploaded' });
//     }

//     const { id } = req.params;
//     db = await getDb();

//     // Check if product exists and get current images
//     const existingProduct = await new Promise((resolve, reject) => {
//       db.get('SELECT images FROM products WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!existingProduct) {
//       deleteFile(req.file.path);
//       returnToPool(db);
//       return res.status(404).json({ error: 'Product not found' });
//     }

//     // Delete old images if exists
//     if (existingProduct.images) {
//       const oldImages = JSON.parse(existingProduct.images);
//       for (const img of oldImages) {
//         deleteFile(path.join(__dirname, '..', 'uploads', 'products', img));
//       }
//     }

//     // Update product with new images
//     await new Promise((resolve, reject) => {
//       db.run(
//         'UPDATE products SET images = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
//         [req.file ? JSON.stringify([req.file.filename]) : null, id],
//         function(err) {
//           if (err) reject(err);
//           else resolve({ changes: this.changes });
//         }
//       );
//     });

//     returnToPool(db);

//     res.json({
//       message: 'Product images uploaded successfully',
//       images: req.file ? [getFileUrl(req, req.file.filename, 'products')] : []
//     });

//   } catch (error) {
//     console.error('Error uploading product images:', error);
//     if (db) returnToPool(db);
//     if (req.file) {
//       deleteFile(req.file.path);
//     }
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Delete product images
// router.delete('/:id/images', authenticateToken, requireAdmin, idValidation, async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     db = await getDb();

//     // Get current images
//     const product = await new Promise((resolve, reject) => {
//       db.get('SELECT images FROM products WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!product) {
//       returnToPool(db);
//       return res.status(404).json({ error: 'Product not found' });
//     }

//     if (!product.images) {
//       returnToPool(db);
//       return res.status(404).json({ error: 'No images to delete' });
//     }

//     // Delete image files
//     const images = JSON.parse(product.images);
//     for (const img of images) {
//       deleteFile(path.join(__dirname, '..', 'uploads', 'products', img));
//     }

//     // Update product to remove image references
//     await new Promise((resolve, reject) => {
//       db.run(
//         'UPDATE products SET images = NULL, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
//         [id],
//         function(err) {
//           if (err) reject(err);
//           else resolve({ changes: this.changes });
//         }
//       );
//     });

//     returnToPool(db);

//     res.json({ message: 'Product images deleted successfully' });

//   } catch (error) {
//     console.error('Error deleting product images:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Debug endpoint to check database content
// router.get('/debug/database', authenticateToken, requireAdmin, async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
    
//     const rows = await new Promise((resolve, reject) => {
//       db.all('SELECT id, name, images FROM products LIMIT 10', (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     returnToPool(db);
    
//     res.json({
//       message: 'Database content for debugging',
//       products: rows
//     });

//   } catch (error) {
//     console.error('Error in debug endpoint:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// module.exports = router;
