cookie-tracker/server/routes/products.js

const express = require('express');
const router = express.Router();
const { getDb } = require('../db');
const { parseId, isValidDate, parseLimit } = require('../utils');

router.get('/', (req, res) => {
  try {
    const db = getDb();
    const rows = db.prepare('SELECT * FROM products ORDER BY name').all();
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/stock-history', (req, res) => {
  try {
    const db = getDb();
    const { product_id, start, end, limit } = req.query;
    if (start && !isValidDate(start)) return res.status(400).json({ error: 'Invalid start date. Use YYYY-MM-DD format.' });
    if (end && !isValidDate(end)) return res.status(400).json({ error: 'Invalid end date. Use YYYY-MM-DD format.' });
    let sql = `
      SELECT sa.*, p.name as product_name
      FROM stock_adjustments sa
      JOIN products p ON p.id = sa.product_id
      WHERE 1=1
    `;
    const params = [];
    if (product_id) { const pid = parseId(product_id); if (!pid) return res.status(400).json({ error: 'Invalid product_id' }); sql += ' AND sa.product_id = ?'; params.push(pid); }
    if (start) { sql += ' AND sa.created_at >= ?'; params.push(start); }
    if (end) { sql += ' AND sa.created_at <= ?'; params.push(end + ' 23:59:59'); }
    sql += ' ORDER BY sa.created_at DESC';
    const lim = limit ? parseLimit(limit) : null;
    if (lim) { sql += ' LIMIT ?'; params.push(lim); }
    const rows = db.prepare(sql).all(...params);
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/:id', (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid product ID' });
    const db = getDb();
    const row = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    if (!row) return res.status(404).json({ error: 'Product not found' });
    res.json(row);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/', (req, res) => {
  try {
    const db = getDb();
    const { name, price = 0, quantity_on_hand = 0, low_stock_threshold = 0 } = req.body;
    if (!name || name.trim() === '') {
      return res.status(400).json({ error: 'Name is required' });
    }
    const result = db.prepare(
      'INSERT INTO products (name, price, quantity_on_hand, low_stock_threshold) VALUES (?, ?, ?, ?)'
    ).run(name.trim(), Number(price), Number(quantity_on_hand), Number(low_stock_threshold));
    const row = db.prepare('SELECT * FROM products WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(row);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.put('/:id', (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid product ID' });
    const db = getDb();
    const { name, price, low_stock_threshold } = req.body;
    const existing = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    if (!existing) return res.status(404).json({ error: 'Product not found' });
    const n = name !== undefined ? name.trim() : existing.name;
    const p = price !== undefined ? Number(price) : existing.price;
    const t = low_stock_threshold !== undefined ? Number(low_stock_threshold) : existing.low_stock_threshold;
    if (!n) return res.status(400).json({ error: 'Name is required' });
    db.prepare(
      'UPDATE products SET name = ?, price = ?, low_stock_threshold = ? WHERE id = ?'
    ).run(n, p, t, id);
    const row = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    res.json(row);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.patch('/:id/stock', (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid product ID' });
    const db = getDb();
    const { adjustment } = req.body;
    if (adjustment === undefined || adjustment === null) {
      return res.status(400).json({ error: 'adjustment is required' });
    }
    const delta = Number(adjustment);
    const row = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    if (!row) return res.status(404).json({ error: 'Product not found' });
    const newQty = row.quantity_on_hand + delta;
    if (newQty < 0) {
      return res.status(400).json({ error: 'Quantity would go negative' });
    }
    db.prepare('UPDATE products SET quantity_on_hand = ? WHERE id = ?').run(newQty, id);
    db.prepare('INSERT INTO stock_adjustments (product_id, adjustment, reason) VALUES (?, ?, ?)').run(id, delta, 'restock');
    const updated = db.prepare('SELECT * FROM products WHERE id = ?').get(id);
    res.json(updated);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.delete('/:id', (req, res) => {
  try {
    const id = parseId(req.params.id);
    if (!id) return res.status(400).json({ error: 'Invalid product ID' });
    const db = getDb();
    const existing = db.prepare('SELECT id FROM products WHERE id = ?').get(id);
    if (!existing) return res.status(404).json({ error: 'Product not found' });
    const refs = db.prepare('SELECT COUNT(*) as count FROM order_items WHERE product_id = ?').get(id);
    if (refs.count > 0) return res.status(409).json({ error: 'Cannot delete product that has been used in orders' });
    db.prepare('DELETE FROM products WHERE id = ?').run(id);
    res.status(204).send();
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;