ressourcenplanung/server/index.js

const express = require('express');
const cors = require('cors');
const db = require('./db');
const { startBackupScheduler, runBackup } = require('./backup');
const { hashPassword, verifyPassword, createSession, requireAuth, requireRole } = require('./auth');

const app = express();
app.use(cors());
app.use(express.json());

const PORT = process.env.PORT || 8080;

// ---------- auth ----------

app.post('/api/auth/login', (req, res) => {
  const { email, password } = req.body;
  if (!email || !password) {
    return res.status(400).json({ error: 'email und password sind erforderlich' });
  }
  const user = db.prepare('SELECT * FROM users WHERE email = ?').get(email);
  if (!user) {
    return res.status(401).json({ error: 'E-Mail oder Passwort falsch' });
  }

  if (user.password_hash === null) {
    // Erster Login für dieses Konto: das eingegebene Passwort wird als neues Passwort übernommen.
    db.prepare('UPDATE users SET password_hash = ? WHERE id = ?').run(hashPassword(password), user.id);
  } else if (!verifyPassword(password, user.password_hash)) {
    return res.status(401).json({ error: 'E-Mail oder Passwort falsch' });
  }

  const token = createSession(user.id);
  res.json({
    token,
    user: { id: user.id, employee_id: user.employee_id, email: user.email, role: user.role },
  });
});

app.use('/api', requireAuth);

app.post('/api/auth/logout', (req, res) => {
  db.prepare('DELETE FROM sessions WHERE token = ?').run(req.sessionToken);
  res.status(204).end();
});

app.get('/api/auth/me', (req, res) => {
  res.json(req.user);
});

// ---------- helpers ----------

function isoWeekKey(date) {
  const d = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  const dayNum = (d.getUTCDay() + 6) % 7; // Monday = 0
  d.setUTCDate(d.getUTCDate() - dayNum + 3);
  const firstThursday = new Date(Date.UTC(d.getUTCFullYear(), 0, 4));
  const week =
    1 + Math.round(((d - firstThursday) / 86400000 - 3 + ((firstThursday.getUTCDay() + 6) % 7)) / 7);
  return `${d.getUTCFullYear()}-W${String(week).padStart(2, '0')}`;
}

function mondayOf(date) {
  const d = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  const dayNum = (d.getUTCDay() + 6) % 7;
  d.setUTCDate(d.getUTCDate() - dayNum);
  return d;
}

function addDays(date, days) {
  const d = new Date(date);
  d.setUTCDate(d.getUTCDate() + days);
  return d;
}

function parseDate(s) {
  const [y, m, d] = s.split('-').map(Number);
  return new Date(Date.UTC(y, m - 1, d));
}

// ---------- employees ----------

app.get('/api/employees', (req, res) => {
  res.json(db.prepare('SELECT * FROM employees ORDER BY last_name, first_name').all());
});

app.post('/api/employees', requireRole('admin', 'teamleiter'), (req, res) => {
  const { first_name, last_name, email, role, weekly_capacity_hours } = req.body;
  if (!first_name || !last_name) {
    return res.status(400).json({ error: 'first_name und last_name sind erforderlich' });
  }
  const result = db
    .prepare(
      'INSERT INTO employees (first_name, last_name, email, role, weekly_capacity_hours) VALUES (?, ?, ?, ?, ?)'
    )
    .run(first_name, last_name, email || null, role || null, weekly_capacity_hours ?? 40);
  res.status(201).json(db.prepare('SELECT * FROM employees WHERE id = ?').get(result.lastInsertRowid));
});

app.put('/api/employees/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  const { first_name, last_name, email, role, weekly_capacity_hours } = req.body;
  const existing = db.prepare('SELECT * FROM employees WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  db.prepare(
    'UPDATE employees SET first_name = ?, last_name = ?, email = ?, role = ?, weekly_capacity_hours = ? WHERE id = ?'
  ).run(
    first_name ?? existing.first_name,
    last_name ?? existing.last_name,
    email ?? existing.email,
    role ?? existing.role,
    weekly_capacity_hours ?? existing.weekly_capacity_hours,
    req.params.id
  );
  res.json(db.prepare('SELECT * FROM employees WHERE id = ?').get(req.params.id));
});

app.delete('/api/employees/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare('DELETE FROM employees WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

// ---------- teams ----------

app.get('/api/teams', (req, res) => {
  const teams = db.prepare('SELECT * FROM teams ORDER BY name').all();
  const members = db
    .prepare(
      `SELECT tm.team_id, e.* FROM team_members tm
       JOIN employees e ON e.id = tm.employee_id`
    )
    .all();
  res.json(
    teams.map((t) => ({
      ...t,
      members: members.filter((m) => m.team_id === t.id).map(({ team_id, ...rest }) => rest),
    }))
  );
});

app.post('/api/teams', requireRole('admin', 'teamleiter'), (req, res) => {
  const { name, description } = req.body;
  if (!name) return res.status(400).json({ error: 'name ist erforderlich' });
  const result = db
    .prepare('INSERT INTO teams (name, description) VALUES (?, ?)')
    .run(name, description || null);
  res.status(201).json({ ...db.prepare('SELECT * FROM teams WHERE id = ?').get(result.lastInsertRowid), members: [] });
});

app.put('/api/teams/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  const { name, description } = req.body;
  const existing = db.prepare('SELECT * FROM teams WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  db.prepare('UPDATE teams SET name = ?, description = ? WHERE id = ?').run(
    name ?? existing.name,
    description ?? existing.description,
    req.params.id
  );
  res.json(db.prepare('SELECT * FROM teams WHERE id = ?').get(req.params.id));
});

app.delete('/api/teams/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare('DELETE FROM teams WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

app.post('/api/teams/:id/members', requireRole('admin', 'teamleiter'), (req, res) => {
  const { employee_id } = req.body;
  db.prepare('INSERT OR IGNORE INTO team_members (team_id, employee_id) VALUES (?, ?)').run(
    req.params.id,
    employee_id
  );
  res.status(201).end();
});

app.delete('/api/teams/:id/members/:employeeId', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare('DELETE FROM team_members WHERE team_id = ? AND employee_id = ?').run(
    req.params.id,
    req.params.employeeId
  );
  res.status(204).end();
});

// ---------- projects & tasks ----------

app.get('/api/projects', (req, res) => {
  const projects = db.prepare('SELECT * FROM projects ORDER BY start_date').all();
  const tasks = db.prepare('SELECT * FROM tasks ORDER BY start_date').all();
  const assignments = db
    .prepare(
      `SELECT a.*, (e.first_name || ' ' || e.last_name) AS employee_name FROM assignments a
       JOIN employees e ON e.id = a.employee_id`
    )
    .all();
  const deps = db.prepare('SELECT * FROM task_dependencies').all();

  const tasksByProject = projects.map((p) => ({
    ...p,
    tasks: tasks
      .filter((t) => t.project_id === p.id)
      .map((t) => ({
        ...t,
        assignments: assignments.filter((a) => a.task_id === t.id),
        depends_on: deps.filter((d) => d.task_id === t.id).map((d) => d.depends_on_task_id),
      })),
  }));
  res.json(tasksByProject);
});

app.post('/api/projects', requireRole('admin', 'teamleiter'), (req, res) => {
  const { name, description, start_date, end_date, color } = req.body;
  if (!name) return res.status(400).json({ error: 'name ist erforderlich' });
  const result = db
    .prepare(
      'INSERT INTO projects (name, description, start_date, end_date, color) VALUES (?, ?, ?, ?, ?)'
    )
    .run(name, description || null, start_date || null, end_date || null, color || '#4f46e5');
  res.status(201).json(db.prepare('SELECT * FROM projects WHERE id = ?').get(result.lastInsertRowid));
});

app.put('/api/projects/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  const existing = db.prepare('SELECT * FROM projects WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  const { name, description, start_date, end_date, color } = req.body;
  db.prepare(
    'UPDATE projects SET name = ?, description = ?, start_date = ?, end_date = ?, color = ? WHERE id = ?'
  ).run(
    name ?? existing.name,
    description ?? existing.description,
    start_date ?? existing.start_date,
    end_date ?? existing.end_date,
    color ?? existing.color,
    req.params.id
  );
  res.json(db.prepare('SELECT * FROM projects WHERE id = ?').get(req.params.id));
});

app.delete('/api/projects/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare('DELETE FROM projects WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

app.post('/api/tasks', requireRole('admin', 'teamleiter'), (req, res) => {
  const { project_id, name, start_date, end_date, estimated_hours, status } = req.body;
  if (!project_id || !name || !start_date || !end_date) {
    return res.status(400).json({ error: 'project_id, name, start_date, end_date sind erforderlich' });
  }
  const result = db
    .prepare(
      'INSERT INTO tasks (project_id, name, start_date, end_date, estimated_hours, status) VALUES (?, ?, ?, ?, ?, ?)'
    )
    .run(project_id, name, start_date, end_date, estimated_hours || 0, status || 'geplant');
  res.status(201).json(db.prepare('SELECT * FROM tasks WHERE id = ?').get(result.lastInsertRowid));
});

app.put('/api/tasks/:id', requireRole('admin', 'teamleiter', 'mitarbeiter'), (req, res) => {
  const existing = db.prepare('SELECT * FROM tasks WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });

  if (req.user.role === 'mitarbeiter') {
    const assigned = db
      .prepare('SELECT 1 FROM assignments WHERE task_id = ? AND employee_id = ?')
      .get(req.params.id, req.user.employee_id);
    if (!assigned) return res.status(403).json({ error: 'nicht zugewiesen' });
    const keys = Object.keys(req.body);
    if (keys.length === 0 || keys.some((k) => k !== 'status')) {
      return res.status(400).json({ error: 'Mitarbeiter dürfen nur den Status ändern' });
    }
    db.prepare('UPDATE tasks SET status = ? WHERE id = ?').run(req.body.status, req.params.id);
    return res.json(db.prepare('SELECT * FROM tasks WHERE id = ?').get(req.params.id));
  }

  const { name, start_date, end_date, estimated_hours, status } = req.body;
  db.prepare(
    'UPDATE tasks SET name = ?, start_date = ?, end_date = ?, estimated_hours = ?, status = ? WHERE id = ?'
  ).run(
    name ?? existing.name,
    start_date ?? existing.start_date,
    end_date ?? existing.end_date,
    estimated_hours ?? existing.estimated_hours,
    status ?? existing.status,
    req.params.id
  );
  res.json(db.prepare('SELECT * FROM tasks WHERE id = ?').get(req.params.id));
});

app.delete('/api/tasks/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare('DELETE FROM tasks WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

app.post('/api/tasks/:id/duplicate', requireRole('admin', 'teamleiter'), (req, res) => {
  const existing = db.prepare('SELECT * FROM tasks WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  const { start_date, end_date } = req.body;
  if (!start_date || !end_date) {
    return res.status(400).json({ error: 'start_date und end_date sind erforderlich' });
  }
  const result = db
    .prepare(
      'INSERT INTO tasks (project_id, name, start_date, end_date, estimated_hours, status) VALUES (?, ?, ?, ?, ?, ?)'
    )
    .run(existing.project_id, existing.name, start_date, end_date, existing.estimated_hours, existing.status);
  const newTaskId = result.lastInsertRowid;

  const insertAssignment = db.prepare(
    'INSERT INTO assignments (task_id, employee_id, allocated_hours_per_week) VALUES (?, ?, ?)'
  );
  const originalAssignments = db.prepare('SELECT * FROM assignments WHERE task_id = ?').all(req.params.id);
  for (const a of originalAssignments) {
    insertAssignment.run(newTaskId, a.employee_id, a.allocated_hours_per_week);
  }

  const newTask = db.prepare('SELECT * FROM tasks WHERE id = ?').get(newTaskId);
  const newAssignments = db
    .prepare(
      `SELECT a.*, (e.first_name || ' ' || e.last_name) AS employee_name FROM assignments a
       JOIN employees e ON e.id = a.employee_id WHERE a.task_id = ?`
    )
    .all(newTaskId);
  res.status(201).json({ ...newTask, assignments: newAssignments, depends_on: [] });
});

app.post('/api/tasks/:id/dependencies', requireRole('admin', 'teamleiter'), (req, res) => {
  const { depends_on_task_id } = req.body;
  db.prepare(
    'INSERT OR IGNORE INTO task_dependencies (task_id, depends_on_task_id) VALUES (?, ?)'
  ).run(req.params.id, depends_on_task_id);
  res.status(201).end();
});

app.delete('/api/tasks/:id/dependencies/:dependsOnId', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare(
    'DELETE FROM task_dependencies WHERE task_id = ? AND depends_on_task_id = ?'
  ).run(req.params.id, req.params.dependsOnId);
  res.status(204).end();
});

// ---------- assignments ----------

app.post('/api/assignments', requireRole('admin', 'teamleiter'), (req, res) => {
  const { task_id, employee_id, allocated_hours_per_week } = req.body;
  if (!task_id || !employee_id) {
    return res.status(400).json({ error: 'task_id und employee_id sind erforderlich' });
  }
  const result = db
    .prepare(
      `INSERT INTO assignments (task_id, employee_id, allocated_hours_per_week) VALUES (?, ?, ?)
       ON CONFLICT (task_id, employee_id) DO UPDATE SET allocated_hours_per_week = excluded.allocated_hours_per_week`
    )
    .run(task_id, employee_id, allocated_hours_per_week || 0);
  const id = result.lastInsertRowid || db.prepare(
    'SELECT id FROM assignments WHERE task_id = ? AND employee_id = ?'
  ).get(task_id, employee_id).id;
  res.status(201).json(db.prepare('SELECT * FROM assignments WHERE id = ?').get(id));
});

app.put('/api/assignments/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  const { allocated_hours_per_week } = req.body;
  db.prepare('UPDATE assignments SET allocated_hours_per_week = ? WHERE id = ?').run(
    allocated_hours_per_week,
    req.params.id
  );
  res.json(db.prepare('SELECT * FROM assignments WHERE id = ?').get(req.params.id));
});

app.delete('/api/assignments/:id', requireRole('admin', 'teamleiter'), (req, res) => {
  db.prepare('DELETE FROM assignments WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

// ---------- vacations ----------

function findVacationConflict({ role, substituteEmployeeId, startDate, endDate, excludeId }) {
  return db
    .prepare(
      `SELECT v.id FROM vacations v
       JOIN employees e ON e.id = v.employee_id
       JOIN users u ON u.employee_id = e.id
       WHERE u.role = ? AND v.substitute_employee_id = ? AND v.id != ?
         AND v.start_date <= ? AND v.end_date >= ?`
    )
    .get(role, substituteEmployeeId, excludeId, endDate, startDate);
}

function vacationRow(id) {
  return db
    .prepare(
      `SELECT v.*,
              (emp.first_name || ' ' || emp.last_name) AS employee_name,
              (sub.first_name || ' ' || sub.last_name) AS substitute_name
       FROM vacations v
       JOIN employees emp ON emp.id = v.employee_id
       JOIN employees sub ON sub.id = v.substitute_employee_id
       WHERE v.id = ?`
    )
    .get(id);
}

app.get('/api/vacations', (req, res) => {
  res.json(
    db
      .prepare(
        `SELECT v.*,
                (emp.first_name || ' ' || emp.last_name) AS employee_name,
                (sub.first_name || ' ' || sub.last_name) AS substitute_name
         FROM vacations v
         JOIN employees emp ON emp.id = v.employee_id
         JOIN employees sub ON sub.id = v.substitute_employee_id
         ORDER BY v.start_date DESC`
      )
      .all()
  );
});

app.post('/api/vacations', requireRole('admin', 'teamleiter', 'mitarbeiter'), (req, res) => {
  let { employee_id, start_date, end_date, substitute_employee_id, note } = req.body;
  if (req.user.role === 'mitarbeiter') {
    employee_id = req.user.employee_id;
  }
  if (!employee_id || !start_date || !end_date || !substitute_employee_id) {
    return res
      .status(400)
      .json({ error: 'employee_id, start_date, end_date und substitute_employee_id sind erforderlich' });
  }
  if (start_date > end_date) {
    return res.status(400).json({ error: 'start_date darf nicht nach end_date liegen' });
  }
  if (Number(substitute_employee_id) === Number(employee_id)) {
    return res.status(400).json({ error: 'Vertreter darf nicht die urlaubende Person selbst sein' });
  }

  const conflict = findVacationConflict({
    role: req.user.role,
    substituteEmployeeId: substitute_employee_id,
    startDate: start_date,
    endDate: end_date,
    excludeId: -1,
  });
  if (conflict) {
    return res
      .status(409)
      .json({ error: 'Vertreter ist bereits für einen überlappenden Zeitraum in dieser Rolle eingetragen' });
  }

  const result = db
    .prepare(
      'INSERT INTO vacations (employee_id, start_date, end_date, substitute_employee_id, note) VALUES (?, ?, ?, ?, ?)'
    )
    .run(employee_id, start_date, end_date, substitute_employee_id, note || null);
  res.status(201).json(vacationRow(result.lastInsertRowid));
});

app.put('/api/vacations/:id', requireRole('admin', 'teamleiter', 'mitarbeiter'), (req, res) => {
  const existing = db.prepare('SELECT * FROM vacations WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  if (req.user.role === 'mitarbeiter' && existing.employee_id !== req.user.employee_id) {
    return res.status(403).json({ error: 'keine Berechtigung' });
  }

  const start_date = req.body.start_date ?? existing.start_date;
  const end_date = req.body.end_date ?? existing.end_date;
  const substitute_employee_id = req.body.substitute_employee_id ?? existing.substitute_employee_id;
  const note = req.body.note ?? existing.note;

  if (start_date > end_date) {
    return res.status(400).json({ error: 'start_date darf nicht nach end_date liegen' });
  }
  if (Number(substitute_employee_id) === Number(existing.employee_id)) {
    return res.status(400).json({ error: 'Vertreter darf nicht die urlaubende Person selbst sein' });
  }

  const requesterRole = db
    .prepare('SELECT role FROM users WHERE employee_id = ?')
    .get(existing.employee_id)?.role;
  if (requesterRole) {
    const conflict = findVacationConflict({
      role: requesterRole,
      substituteEmployeeId: substitute_employee_id,
      startDate: start_date,
      endDate: end_date,
      excludeId: existing.id,
    });
    if (conflict) {
      return res
        .status(409)
        .json({ error: 'Vertreter ist bereits für einen überlappenden Zeitraum in dieser Rolle eingetragen' });
    }
  }

  db.prepare(
    'UPDATE vacations SET start_date = ?, end_date = ?, substitute_employee_id = ?, note = ? WHERE id = ?'
  ).run(start_date, end_date, substitute_employee_id, note, req.params.id);
  res.json(vacationRow(req.params.id));
});

app.delete('/api/vacations/:id', requireRole('admin', 'teamleiter', 'mitarbeiter'), (req, res) => {
  const existing = db.prepare('SELECT * FROM vacations WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  if (req.user.role === 'mitarbeiter' && existing.employee_id !== req.user.employee_id) {
    return res.status(403).json({ error: 'keine Berechtigung' });
  }
  db.prepare('DELETE FROM vacations WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

// ---------- utilization heatmap ----------

app.get('/api/utilization', (req, res) => {
  const from = req.query.from ? parseDate(req.query.from) : mondayOf(new Date());
  const weekCount = Math.min(Number(req.query.weeks) || 12, 52);

  const weeks = [];
  for (let i = 0; i < weekCount; i++) {
    const weekStart = addDays(mondayOf(from), i * 7);
    weeks.push({ key: isoWeekKey(weekStart), start: weekStart });
  }

  const employees = db.prepare('SELECT * FROM employees ORDER BY last_name, first_name').all();
  const rows = db
    .prepare(
      `SELECT a.employee_id, a.allocated_hours_per_week, t.start_date, t.end_date
       FROM assignments a JOIN tasks t ON t.id = a.task_id`
    )
    .all();
  const vacationRows = db.prepare('SELECT employee_id, start_date, end_date FROM vacations').all();

  function overlapDaysInWeek(startStr, endStr, week) {
    const rangeStart = parseDate(startStr);
    const rangeEnd = parseDate(endStr);
    const weekEnd = addDays(week.start, 6);
    const overlapStart = rangeStart > week.start ? rangeStart : week.start;
    const overlapEnd = rangeEnd < weekEnd ? rangeEnd : weekEnd;
    return Math.max(0, Math.round((overlapEnd - overlapStart) / 86400000) + 1);
  }

  const data = {};
  const vacationDays = {};
  for (const e of employees) {
    data[e.id] = {};
    vacationDays[e.id] = {};
  }

  for (const row of rows) {
    for (const week of weeks) {
      const overlapDays = overlapDaysInWeek(row.start_date, row.end_date, week);
      if (overlapDays <= 0) continue;
      const hours = row.allocated_hours_per_week * (overlapDays / 7);
      data[row.employee_id][week.key] = (data[row.employee_id][week.key] || 0) + hours;
    }
  }

  for (const row of vacationRows) {
    if (!vacationDays[row.employee_id]) continue;
    for (const week of weeks) {
      const overlapDays = overlapDaysInWeek(row.start_date, row.end_date, week);
      if (overlapDays <= 0) continue;
      vacationDays[row.employee_id][week.key] = (vacationDays[row.employee_id][week.key] || 0) + overlapDays;
    }
  }

  res.json({
    weeks: weeks.map((w) => w.key),
    employees: employees.map((e) => ({
      id: e.id,
      name: `${e.first_name} ${e.last_name}`,
      weekly_capacity_hours: e.weekly_capacity_hours,
      hours: weeks.map((w) => Math.round((data[e.id][w.key] || 0) * 10) / 10),
      capacity: weeks.map((w) => {
        const days = Math.min(7, vacationDays[e.id][w.key] || 0);
        return Math.round(e.weekly_capacity_hours * (1 - days / 7) * 10) / 10;
      }),
    })),
  });
});

app.get('/health', (req, res) => res.json({ ok: true }));

// ---------- backup ----------

app.post('/api/backup', requireRole('admin'), async (req, res) => {
  try {
    const file = await runBackup();
    res.status(201).json({ file });
  } catch (err) {
    res.status(500).json({ error: 'Backup fehlgeschlagen' });
  }
});

// ---------- users (Benutzerverwaltung) ----------

app.get('/api/users', requireRole('admin'), (req, res) => {
  res.json(
    db
      .prepare(
        `SELECT u.id, u.employee_id, u.email, u.role,
                (e.first_name || ' ' || e.last_name) AS employee_name
         FROM users u LEFT JOIN employees e ON e.id = u.employee_id
         ORDER BY u.email`
      )
      .all()
  );
});

app.post('/api/users', requireRole('admin'), (req, res) => {
  const { employee_id, email, role } = req.body;
  if (!email || !role) {
    return res.status(400).json({ error: 'email und role sind erforderlich' });
  }
  // Passwort wird bewusst nicht hier gesetzt: der Mitarbeiter vergibt es selbst beim ersten Login.
  const result = db
    .prepare('INSERT INTO users (employee_id, email, password_hash, role) VALUES (?, ?, NULL, ?)')
    .run(employee_id || null, email, role);
  res.status(201).json(
    db
      .prepare('SELECT id, employee_id, email, role FROM users WHERE id = ?')
      .get(result.lastInsertRowid)
  );
});

app.put('/api/users/:id', requireRole('admin'), (req, res) => {
  const existing = db.prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);
  if (!existing) return res.status(404).json({ error: 'nicht gefunden' });
  const email = req.body.email ?? existing.email;
  const role = req.body.role ?? existing.role;
  // reset_password: true setzt den Hash zurück auf NULL, das Konto vergibt beim
  // nächsten Login wieder ein neues Passwort (gleicher Mechanismus wie bei Neuanlage).
  const passwordHash = req.body.reset_password ? null : existing.password_hash;
  db.prepare('UPDATE users SET email = ?, role = ?, password_hash = ? WHERE id = ?').run(
    email,
    role,
    passwordHash,
    req.params.id
  );
  res.json(db.prepare('SELECT id, employee_id, email, role FROM users WHERE id = ?').get(req.params.id));
});

app.delete('/api/users/:id', requireRole('admin'), (req, res) => {
  db.prepare('DELETE FROM users WHERE id = ?').run(req.params.id);
  res.status(204).end();
});

// serve built frontend in production
const path = require('path');
const fs = require('fs');
const clientDist = path.join(__dirname, '..', 'client', 'dist');
if (fs.existsSync(clientDist)) {
  app.use(express.static(clientDist));
  app.get(/^(?!\/api|\/health).*/, (req, res) => {
    res.sendFile(path.join(clientDist, 'index.html'));
  });
}

// generischer Fehler-Handler: SQLite-Fremdschlüsselverletzungen (z. B. Mitarbeiter
// löschen, der noch als Urlaubsvertreter eingetragen ist) sauber als 409 statt 500 melden
app.use((err, req, res, next) => {
  if (err && err.code === 'SQLITE_CONSTRAINT_FOREIGNKEY') {
    return res.status(409).json({ error: 'Datensatz wird an anderer Stelle noch referenziert' });
  }
  console.error(err);
  res.status(500).json({ error: 'interner Fehler' });
});

app.listen(PORT, () => {
  console.log(`Ressourcenplanung-API läuft auf http://localhost:${PORT}`);
  startBackupScheduler();
});