mek-tech-consulting/routes/spa/finance.js

const express = require('express');
const router = express.Router();
const { getDb, logAudit } = require('../../db');
const emailService = require('../../email');

// ============ TIME ENTRIES ============

router.get('/time', (req, res) => {
  const db = getDb();
  const { client_id, date_from, date_to } = req.query;
  let sql = `
    SELECT t.*, c.name as client_name, e.title as engagement_title, u.username
    FROM time_entries t
    LEFT JOIN clients c ON c.id = t.client_id
    LEFT JOIN engagements e ON e.id = t.engagement_id
    LEFT JOIN users u ON u.id = t.user_id
    WHERE 1=1`;
  const params = [];
  if (client_id) { sql += ' AND t.client_id = ?'; params.push(client_id); }
  if (date_from) { sql += ' AND t.date >= ?'; params.push(date_from); }
  if (date_to) { sql += ' AND t.date <= ?'; params.push(date_to); }
  sql += ' ORDER BY t.date DESC, t.created_at DESC';
  const entries = db.prepare(sql).all(...params);
  res.json({
    entries,
    totalHours: entries.reduce((s, e) => s + e.hours, 0),
    billableHours: entries.filter(e => e.billable).reduce((s, e) => s + e.hours, 0)
  });
});

router.post('/time', (req, res) => {
  const db = getDb();
  const { client_id, engagement_id, date, hours, description, billable, hourly_rate } = req.body;
  if (!date || !hours) return res.status(400).json({ error: 'Datum en uren zijn verplicht' });
  const result = db.prepare('INSERT INTO time_entries (user_id, client_id, engagement_id, date, hours, description, billable, hourly_rate) VALUES (?,?,?,?,?,?,?,?)')
    .run(req.session.userId, client_id || null, engagement_id || null, date, parseFloat(hours), description || '', billable ? 1 : 0, parseFloat(hourly_rate) || 150.0);
  logAudit(req.session.userId, req.session.username, 'time_entry_created', 'time_entry', result.lastInsertRowid, `${hours}u geregistreerd op ${date} via SPA`);
  res.status(201).json({ id: result.lastInsertRowid });
});

router.put('/time/:id', (req, res) => {
  const db = getDb();
  const { client_id, engagement_id, date, hours, description, billable, hourly_rate } = req.body;
  db.prepare("UPDATE time_entries SET client_id=?, engagement_id=?, date=?, hours=?, description=?, billable=?, hourly_rate=?, updated_at=datetime('now') WHERE id=?")
    .run(client_id || null, engagement_id || null, date, parseFloat(hours), description || '', billable ? 1 : 0, parseFloat(hourly_rate) || 150.0, req.params.id);
  res.json({ ok: true });
});

router.delete('/time/:id', (req, res) => {
  const db = getDb();
  db.prepare('DELETE FROM time_entries WHERE id = ?').run(req.params.id);
  res.json({ ok: true });
});

// Factuur genereren uit billable uren
router.post('/time/create-invoice', (req, res) => {
  const db = getDb();
  const { client_id, date_from, date_to } = req.body;
  if (!client_id) return res.status(400).json({ error: 'Client is verplicht' });
  const entries = db.prepare(`
    SELECT t.*, e.title as engagement_title
    FROM time_entries t
    LEFT JOIN engagements e ON e.id = t.engagement_id
    WHERE t.client_id = ? AND t.billable = 1
    AND (? IS NULL OR t.date >= ?) AND (? IS NULL OR t.date <= ?)
  `).all(client_id, date_from || null, date_from || null, date_to || null, date_to || null);
  if (entries.length === 0) return res.status(400).json({ error: 'Geen facturabele uren gevonden' });
  const totalHours = entries.reduce((s, e) => s + e.hours, 0);
  const rate = entries[0].hourly_rate || 150;
  const subtotal = totalHours * rate;
  const tax = subtotal * 0.21;
  const count = db.prepare('SELECT COUNT(*) as cnt FROM invoices').get().cnt + 1;
  const number = `FACT-${new Date().getFullYear()}-${String(count).padStart(4, '0')}`;
  const date = new Date().toISOString().split('T')[0];
  const result = db.prepare('INSERT INTO invoices (client_id, number, status, date, due_date, subtotal, tax, total, notes) VALUES (?,?,?,?,?,?,?,?,?)')
    .run(client_id, number, 'draft', date, '', subtotal, tax, subtotal + tax, `Factuur gegenereerd uit ${entries.length} tijdregistraties`);
  const invoiceId = result.lastInsertRowid;
  const insert = db.prepare('INSERT INTO invoice_items (invoice_id, description, quantity, unit_price, total) VALUES (?,?,?,?,?)');
  entries.forEach(e => {
    const lineTotal = e.hours * (e.hourly_rate || rate);
    insert.run(invoiceId, `${e.date} - ${e.engagement_title || 'Werkzaamheden'}: ${e.description || ''}`, e.hours, e.hourly_rate || rate, lineTotal);
  });
  logAudit(req.session.userId, req.session.username, 'invoice_from_time', 'invoice', invoiceId, `Factuur ${number} gemaakt uit ${entries.length} uren via SPA`);
  res.status(201).json({ id: invoiceId, number });
});

// ============ INVOICES ============

router.get('/invoices', (req, res) => {
  const db = getDb();
  const invoices = db.prepare(`
    SELECT i.*, c.name as client_name
    FROM invoices i JOIN clients c ON c.id = i.client_id
    ORDER BY i.created_at DESC
  `).all();
  res.json({
    invoices,
    totalOutstanding: invoices.filter(i => i.status === 'sent' || i.status === 'overdue').reduce((s, i) => s + i.total, 0),
    totalPaid: invoices.filter(i => i.status === 'paid').reduce((s, i) => s + i.total, 0)
  });
});

router.post('/invoices', (req, res) => {
  const db = getDb();
  const { client_id, date, due_date, notes, items, subtotal, tax } = req.body;
  if (!client_id) return res.status(400).json({ error: 'Client is verplicht' });
  const count = db.prepare('SELECT COUNT(*) as cnt FROM invoices').get().cnt + 1;
  const number = `FACT-${new Date().getFullYear()}-${String(count).padStart(4, '0')}`;
  const sub = parseFloat(subtotal) || 0;
  const taxAmt = parseFloat(tax) || 0;
  const result = db.prepare('INSERT INTO invoices (client_id, number, status, date, due_date, subtotal, tax, total, notes) VALUES (?,?,?,?,?,?,?,?,?)')
    .run(client_id, number, 'draft', date || new Date().toISOString().split('T')[0], due_date || '', sub, taxAmt, sub + taxAmt, notes || '');
  const invoiceId = result.lastInsertRowid;
  if (items && Array.isArray(items)) {
    const insert = db.prepare('INSERT INTO invoice_items (invoice_id, description, quantity, unit_price, total) VALUES (?,?,?,?,?)');
    for (const item of items) {
      if (item.description) {
        const itemTotal = (parseFloat(item.quantity) || 1) * (parseFloat(item.unit_price) || 0);
        insert.run(invoiceId, item.description, parseFloat(item.quantity) || 1, parseFloat(item.unit_price) || 0, itemTotal);
      }
    }
  }
  logAudit(req.session.userId, req.session.username, 'invoice_created', 'invoice', invoiceId, `Factuur ${number} aangemaakt via SPA`);
  res.status(201).json({ id: invoiceId, number });
});

router.get('/invoices/:id', (req, res) => {
  const db = getDb();
  const invoice = db.prepare('SELECT i.*, c.name as client_name, c.contact_name, c.contact_email, c.contact_phone, c.website FROM invoices i JOIN clients c ON c.id = i.client_id WHERE i.id = ?').get(req.params.id);
  if (!invoice) return res.status(404).json({ error: 'Factuur niet gevonden' });
  const items = db.prepare('SELECT * FROM invoice_items WHERE invoice_id = ?').all(req.params.id);
  res.json({ invoice, items });
});

router.post('/invoices/:id/status', (req, res) => {
  const db = getDb();
  db.prepare("UPDATE invoices SET status=?, updated_at=datetime('now') WHERE id=?").run(req.body.status, req.params.id);
  res.json({ ok: true });
});

router.delete('/invoices/:id', (req, res) => {
  const db = getDb();
  db.prepare('DELETE FROM invoices WHERE id = ?').run(req.params.id);
  res.json({ ok: true });
});

router.post('/invoices/:id/send-email', async (req, res) => {
  const db = getDb();
  const invoice = db.prepare('SELECT i.*, c.name as client_name, c.contact_email FROM invoices i JOIN clients c ON c.id = i.client_id WHERE i.id = ?').get(req.params.id);
  if (!invoice) return res.status(404).json({ error: 'Factuur niet gevonden' });
  const account = db.prepare('SELECT * FROM email_accounts WHERE user_id = ? AND active = 1 LIMIT 1').get(req.session.userId);
  if (!account) return res.status(400).json({ error: 'Geen e-mailaccount geconfigureerd' });
  const items = db.prepare('SELECT * FROM invoice_items WHERE invoice_id = ?').all(req.params.id);
  let text = `Factuur ${invoice.number}\n\nClient: ${invoice.client_name}\nDatum: ${invoice.date}\nVervaldatum: ${invoice.due_date || '-'}\n\n`;
  items.forEach(item => { text += `${item.description}  x${item.quantity}  EUR ${(item.unit_price || 0).toFixed(2)}  EUR ${(item.total || 0).toFixed(2)}\n`; });
  text += `\nSubtotaal: EUR ${(invoice.subtotal || 0).toFixed(2)}\nBTW: EUR ${(invoice.tax || 0).toFixed(2)}\nTotaal: EUR ${(invoice.total || 0).toFixed(2)}\n\nMek-Tech Consulting`;
  const to = invoice.contact_email || account.email;
  try {
    await emailService.sendEmail(account.id, to, `Factuur ${invoice.number}`, text, text.replace(/\n/g, '<br>'));
    logAudit(req.session.userId, req.session.username, 'invoice_emailed', 'invoice', invoice.id, `Factuur ${invoice.number} gemaild naar ${to}`);
    db.prepare("UPDATE invoices SET status='sent', updated_at=datetime('now') WHERE id=?").run(req.params.id);
    res.json({ ok: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ FINANCE OVERZICHT ============

router.get('/finance', (req, res) => {
  const db = getDb();
  const year = parseInt(req.query.year) || new Date().getFullYear();
  const timeStats = db.prepare(`
    SELECT strftime('%m', date) as month, SUM(hours) as hours,
      SUM(CASE WHEN billable = 1 THEN hours ELSE 0 END) as billable_hours
    FROM time_entries WHERE strftime('%Y', date) = ?
    GROUP BY month ORDER BY month
  `).all(String(year));
  const invoiceStats = db.prepare(`
    SELECT strftime('%m', date) as month,
      SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) as paid,
      SUM(CASE WHEN status IN ('sent','overdue') THEN total ELSE 0 END) as outstanding
    FROM invoices WHERE strftime('%Y', date) = ?
    GROUP BY month ORDER BY month
  `).all(String(year));
  const totals = {
    hoursThisYear: db.prepare("SELECT COALESCE(SUM(hours),0) as s FROM time_entries WHERE strftime('%Y', date) = ?").get(String(year)).s,
    paidThisYear: db.prepare("SELECT COALESCE(SUM(total),0) as s FROM invoices WHERE status='paid' AND strftime('%Y', date) = ?").get(String(year)).s,
    outstanding: db.prepare("SELECT COALESCE(SUM(total),0) as s FROM invoices WHERE status IN ('sent','overdue')").get().s,
    draftTotal: db.prepare("SELECT COALESCE(SUM(total),0) as s FROM invoices WHERE status='draft'").get().s
  };
  res.json({ year, timeStats, invoiceStats, totals });
});

module.exports = router;