const express = require('express');
const router = express.Router();
const { getDb, ensureUserDefaultCategories } = require('../db/database');

// GET /api/categories
router.get('/', (req, res) => {
  const db = getDb();
  ensureUserDefaultCategories(req.user.id);
  res.json(db.prepare('SELECT * FROM categories WHERE user_id = ? ORDER BY name ASC').all(req.user.id));
});

// POST /api/categories
router.post('/', (req, res) => {
  const db = getDb();
  const { name } = req.body;
  if (!name) return res.status(400).json({ error: 'name is required' });

  try {
    const result = db.prepare('INSERT INTO categories (user_id, name) VALUES (?, ?)').run(req.user.id, name.trim());
    const created = db.prepare('SELECT * FROM categories WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(created);
  } catch (e) {
    if (e.message.includes('UNIQUE')) {
      return res.status(409).json({ error: 'Category already exists' });
    }
    throw e;
  }
});

// PUT /api/categories/:id
router.put('/:id', (req, res) => {
  const db = getDb();
  const { name } = req.body;
  if (!name) return res.status(400).json({ error: 'name is required' });

  const cat = db.prepare('SELECT id FROM categories WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id);
  if (!cat) return res.status(404).json({ error: 'Category not found' });

  try {
    db.prepare("UPDATE categories SET name = ?, updated_at = datetime('now') WHERE id = ? AND user_id = ?")
      .run(name.trim(), req.params.id, req.user.id);
    res.json(db.prepare('SELECT * FROM categories WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id));
  } catch (e) {
    if (e.message.includes('UNIQUE')) {
      return res.status(409).json({ error: 'Category already exists' });
    }
    throw e;
  }
});

// DELETE /api/categories/:id
router.delete('/:id', (req, res) => {
  const db = getDb();
  const cat = db.prepare('SELECT id FROM categories WHERE id = ? AND user_id = ?').get(req.params.id, req.user.id);
  if (!cat) return res.status(404).json({ error: 'Category not found' });
  db.prepare('DELETE FROM categories WHERE id = ? AND user_id = ?').run(req.params.id, req.user.id);
  res.json({ success: true });
});

module.exports = router;