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

const router = express.Router();

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

const createCategoryValidation = [
  body('name').trim().isLength({ min: 2, max: 50 }).withMessage('Category name must be between 2 and 50 characters'),
  body('description').optional().trim().isLength({ max: 500 }).withMessage('Description must be less than 500 characters'),
  body('is_active').optional().isBoolean().withMessage('is_active must be a boolean value')
];

const updateCategoryValidation = [
  body('name').optional().trim().isLength({ min: 2, max: 50 }).withMessage('Category name must be between 2 and 50 characters'),
  body('description').optional().trim().isLength({ max: 500 }).withMessage('Description must be less than 500 characters'),
  body('is_active').optional().isBoolean().withMessage('is_active must be a boolean value')
];

// Get all categories
router.get('/', authenticateToken, async (req, res) => {
  let connection;
  try {
    connection = await getDb();

    const [rows] = await connection.query(`
      SELECT 
        c.*,
        COUNT(p.id) as product_count
      FROM categories c
      LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
      GROUP BY c.id
      ORDER BY c.name
    `);

    returnToPool(connection);
    res.json({ categories: rows });
  } catch (error) {
    console.error('Error fetching categories:', error);
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to fetch categories' });
  }
});

// Public endpoint for active categories with products
router.get('/public', async (req, res) => {
  let connection;
  try {
    connection = await getDb();

    const [rows] = await connection.query(`
      SELECT 
        c.id,
        c.name,
        c.description,
        COUNT(p.id) as product_count
      FROM categories c
      LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
      WHERE c.is_active = 1
      GROUP BY c.id
      HAVING product_count > 0
      ORDER BY c.name
    `);

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

// Get category by ID
router.get('/:id', authenticateToken, async (req, res) => {
  let connection;
  try {
    const categoryId = req.params.id;
    connection = await getDb();

    const [rows] = await connection.query(
      'SELECT * FROM categories WHERE id = ?',
      [categoryId]
    );

    returnToPool(connection);

    if (rows.length === 0) {
      return res.status(404).json({ error: 'Category not found' });
    }

    res.json({ category: rows[0] });
  } catch (error) {
    console.error('Error fetching category:', error);
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Failed to fetch category' });
  }
});

// Create category
router.post('/', authenticateToken, requireAdmin, createCategoryValidation, async (req, res) => {
  let connection;
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) return res.status(400).json({ errors: errors.array() });

    const { name, description = '', is_active = true } = req.body;
    connection = await getDb();

    const [exists] = await connection.query('SELECT id FROM categories WHERE name = ?', [name]);
    if (exists.length > 0) {
      returnToPool(connection);
      return res.status(409).json({ error: 'Category name already exists' });
    }

    const [result] = await connection.query(
      'INSERT INTO categories (name, description, is_active, created_at, updated_at) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)',
      [name, description, is_active]
    );

    const [newRow] = await connection.query(
      'SELECT id, name, description, is_active, created_at, updated_at FROM categories WHERE id = ?',
      [result.insertId]
    );

    returnToPool(connection);
    res.status(201).json({
      message: 'Category created successfully',
      category: newRow[0]
    });
  } catch (error) {
    console.error('Error creating category:', error);
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// Update category
router.put('/:id', authenticateToken, requireAdmin, [...idValidation, ...updateCategoryValidation], 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, is_active } = req.body;
    connection = await getDb();

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

    if (name && name !== existing[0].name) {
      const [nameConflict] = await connection.query('SELECT id FROM categories WHERE name = ? AND id != ?', [name, id]);
      if (nameConflict.length > 0) {
        returnToPool(connection);
        return res.status(409).json({ error: 'Category name already exists' });
      }
    }

    const updateFields = [];
    const updateValues = [];

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

    updateFields.push('updated_at = CURRENT_TIMESTAMP');

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

    const updateQuery = `UPDATE categories SET ${updateFields.join(', ')} WHERE id = ?`;
    updateValues.push(id);

    await connection.query(updateQuery, updateValues);

    const [updated] = await connection.query(
      'SELECT id, name, description, is_active, created_at, updated_at FROM categories WHERE id = ?',
      [id]
    );

    returnToPool(connection);
    res.json({
      message: 'Category updated successfully',
      category: updated[0]
    });
  } catch (error) {
    console.error('Error updating category:', error);
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// Delete category
router.delete('/:id', authenticateToken, requireAdmin, idValidation, 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;
    connection = await getDb();

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

    const [productCount] = await connection.query('SELECT COUNT(*) AS count FROM products WHERE category_id = ?', [id]);
    if (productCount[0].count > 0) {
      returnToPool(connection);
      return res.status(400).json({
        error: 'Cannot delete category with existing products',
        productsCount: productCount[0].count
      });
    }

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

    returnToPool(connection);
    res.json({ message: 'Category deleted successfully' });
  } catch (error) {
    console.error('Error deleting category:', error);
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

module.exports = router;


// const express = require('express');
// const { authenticateToken, requireAdmin } = require('../middleware/auth');
// const { getDb, returnToPool } = require('../database/init');
// const path = require('path');
// 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 category ID is required')
// ];

// const createCategoryValidation = [
//   body('name').trim().isLength({ min: 2, max: 50 }).withMessage('Category name must be between 2 and 50 characters'),
//   body('description').optional().trim().isLength({ max: 500 }).withMessage('Description must be less than 500 characters'),
//   body('is_active').optional().isBoolean().withMessage('is_active must be a boolean value')
// ];

// const updateCategoryValidation = [
//   body('name').optional().trim().isLength({ min: 2, max: 50 }).withMessage('Category name must be between 2 and 50 characters'),
//   body('description').optional().trim().isLength({ max: 500 }).withMessage('Description must be less than 500 characters'),
//   body('is_active').optional().isBoolean().withMessage('is_active must be a boolean value')
// ];

// // Get all categories
// router.get('/', authenticateToken, async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
    
//     const query = `
//       SELECT 
//         c.*,
//         COUNT(p.id) as product_count
//       FROM categories c
//       LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
//       GROUP BY c.id
//       ORDER BY c.name
//     `;
    
//     const rows = await new Promise((resolve, reject) => {
//       db.all(query, [], (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     returnToPool(db);
//     res.json({ categories: rows });

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

// // Get all categories (public endpoint for clients)
// router.get('/public', async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
    
//     const query = `
//       SELECT 
//         c.id,
//         c.name,
//         c.description,
//         COUNT(p.id) as product_count
//       FROM categories c
//       LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
//       WHERE c.is_active = 1
//       GROUP BY c.id
//       HAVING product_count > 0
//       ORDER BY c.name
//     `;
    
//     const rows = await new Promise((resolve, reject) => {
//       db.all(query, [], (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     returnToPool(db);
//     res.json({ categories: rows });

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

// // Get category by ID
// router.get('/:id', authenticateToken, async (req, res) => {
//   let db;
//   try {
//     db = await getDb();
//     const categoryId = req.params.id;
    
//     const row = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT * FROM categories WHERE id = ?',
//         [categoryId],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     returnToPool(db);

//     if (!row) {
//       return res.status(404).json({ error: 'Category not found' });
//     }
    
//     res.json({ category: row });

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

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

//     const { name, description, is_active = true } = req.body;
//     db = await getDb();

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

//     if (existingCategory) {
//       returnToPool(db);
//       return res.status(409).json({ error: 'Category name already exists' });
//     }

//     // Insert new category
//     const result = await new Promise((resolve, reject) => {
//       db.run(
//         'INSERT INTO categories (name, description, is_active, created_at, updated_at) VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)',
//         [name, description, is_active],
//         function(err) {
//           if (err) reject(err);
//           else resolve({ id: this.lastID });
//         }
//       );
//     });

//     // Get created category
//     const newCategory = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT id, name, description, is_active, created_at, updated_at FROM categories WHERE id = ?',
//         [result.id],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     returnToPool(db);

//     res.status(201).json({
//       message: 'Category created successfully',
//       category: newCategory
//     });

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

// // Update category (admin only)
// router.put('/:id', authenticateToken, requireAdmin, [...idValidation, ...updateCategoryValidation], 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;
//     const { name, description, is_active } = req.body;
//     db = await getDb();

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

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

//     // Check name uniqueness if updating name
//     if (name && name !== existingCategory.name) {
//       const nameExists = await new Promise((resolve, reject) => {
//         db.get('SELECT id FROM categories WHERE name = ? AND id != ?', [name, id], (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         });
//       });

//       if (nameExists) {
//         returnToPool(db);
//         return res.status(409).json({ error: 'Category name already exists' });
//       }
//     }

//     // 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 (is_active !== undefined) {
//       updateFields.push('is_active = ?');
//       updateValues.push(is_active);
//     }

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

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

//     // Build and execute update query
//     const updateQuery = `UPDATE categories 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 category
//     const updatedCategory = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT id, name, description, is_active, created_at, updated_at FROM categories WHERE id = ?',
//         [id],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     returnToPool(db);

//     res.json({
//       message: 'Category updated successfully',
//       category: updatedCategory
//     });

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

// // Delete category (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 category exists
//     const existingCategory = await new Promise((resolve, reject) => {
//       db.get('SELECT id FROM categories WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

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

//     // Check if category has products
//     const productsCount = await new Promise((resolve, reject) => {
//       db.get('SELECT COUNT(*) as count FROM products WHERE category_id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row.count);
//       });
//     });

//     if (productsCount > 0) {
//       returnToPool(db);
//       return res.status(400).json({ 
//         error: 'Cannot delete category with existing products',
//         productsCount: productsCount
//       });
//     }

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

//     returnToPool(db);

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

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

// module.exports = router;
