const router = require('express').Router();
const pool = require('../config/db');
const auth = require('../middleware/auth');
const { requireRole } = require('../middleware/auth');
const { uid, logActivity } = require('../models/helpers');
const { createNotification, checkLowStock } = require('../models/notifications');

router.use(auth);

// Get all products (all roles)
router.get('/', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM products ORDER BY name');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Get single product (all roles)
router.get('/:id', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [req.params.id]);
    if (rows.length === 0) return res.status(404).json({ error: 'Product not found' });
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Create product (master_admin, supplier)
router.post('/', requireRole('master_admin', 'supplier'), async (req, res) => {
  try {
    const id = uid();
    const { name, description, mg, ml, route, cost_price, retail_price, stock_qty, batch, expiry, supplier, low_threshold, added_date } = req.body;
    await pool.query(
      'INSERT INTO products (id, name, description, mg, ml, route, cost_price, retail_price, stock_qty, batch, expiry, supplier, low_threshold, added_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [id, name, description || '', mg || 0, ml || 0, route || 'SQ', cost_price || 0, retail_price || 0, stock_qty || 0, batch || '', expiry || null, supplier || '', low_threshold || 5, added_date || null]
    );
    await logActivity(pool, 'create', 'product', id, name);
    const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [id]);
    await createNotification(pool, { type: 'product', action: 'created', title: 'Product Added', message: `${name} was added to inventory`, detail: { product: rows[0] }, entity: 'product', entityId: id, triggeredBy: req.user.id });
    await checkLowStock(pool, req.user.id);
    res.status(201).json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Update product (master_admin, supplier)
router.put('/:id', requireRole('master_admin', 'supplier'), async (req, res) => {
  try {
    const { name, description, mg, ml, route, cost_price, retail_price, stock_qty, batch, expiry, supplier, low_threshold, added_date } = req.body;

    const [old] = await pool.query('SELECT stock_qty FROM products WHERE id = ?', [req.params.id]);
    const oldQty = old.length > 0 ? old[0].stock_qty : 0;
    const newQty = parseInt(stock_qty) || 0;

    await pool.query(
      'UPDATE products SET name=?, description=?, mg=?, ml=?, route=?, cost_price=?, retail_price=?, stock_qty=?, batch=?, expiry=?, supplier=?, low_threshold=?, added_date=? WHERE id=?',
      [name, description, mg, ml, route, cost_price, retail_price, stock_qty, batch, expiry || null, supplier, low_threshold, added_date || null, req.params.id]
    );

    if (newQty !== oldQty) {
      const diff = newQty - oldQty;
      await pool.query(
        'INSERT INTO stock_movements (product_id, type, qty, reason, reference_id) VALUES (?, ?, ?, ?, ?)',
        [req.params.id, diff > 0 ? 'in' : 'adjust', Math.abs(diff), diff > 0 ? 'Stock added' : 'Stock adjusted', req.params.id]
      );
    }

    await logActivity(pool, 'edit', 'product', req.params.id, name);
    const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [req.params.id]);
    const editAction = newQty !== oldQty ? (newQty > oldQty ? 'stock_increased' : 'stock_decreased') : 'edited';
    const editMsg = newQty !== oldQty ? `${name} stock: ${oldQty} → ${newQty}` : `${name} was updated`;
    await createNotification(pool, { type: 'product', action: editAction, title: editAction === 'edited' ? 'Product Updated' : 'Stock Changed', message: editMsg, detail: { product: rows[0], oldQty, newQty }, entity: 'product', entityId: req.params.id, triggeredBy: req.user.id });
    await checkLowStock(pool, req.user.id);
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Delete product (master_admin, supplier)
router.delete('/:id', requireRole('master_admin', 'supplier'), async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [req.params.id]);
    if (rows.length === 0) return res.status(404).json({ error: 'Not found' });
    const product = rows[0];

    const [orderItems] = await pool.query('SELECT COUNT(*) as cnt FROM order_items WHERE product_id = ?', [req.params.id]);
    if (orderItems[0].cnt > 0) {
      await logActivity(pool, 'delete', 'product', req.params.id, product.name, JSON.stringify({ t: 'prod_del', prod: product }));
      await pool.query('DELETE FROM order_items WHERE product_id = ?', [req.params.id]);
      await pool.query('DELETE FROM products WHERE id = ?', [req.params.id]);
    } else {
      await logActivity(pool, 'delete', 'product', req.params.id, product.name, JSON.stringify({ t: 'prod_del', prod: product }));
      await pool.query('DELETE FROM products WHERE id = ?', [req.params.id]);
    }
    await createNotification(pool, { type: 'product', action: 'deleted', title: 'Product Deleted', message: `${product.name} was removed from inventory`, detail: { product }, entity: 'product', entityId: req.params.id, triggeredBy: req.user.id });
    res.json({ message: 'Product deleted' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
