projektuebersicht/server.js

/*
 * Express-Server der Projektübersicht: liefert die statischen Frontend-Dateien aus
 * public/ (Test.html unter "/") und stellt den Frontend-Modulen unter public/js/
 * eine JSON-API zum Laden und Speichern der Sitzungsdaten bereit. Die Daten liegen
 * in einer SQLite-Datenbank (node:sqlite, Node >= 22.13) unter sessions/projektuebersicht.db.
 *
 * Das Schema ist voll relational normalisiert: statt eines grossen JSON-Dokuments
 * pro Woche gibt es eigene Tabellen für Wochen-Metadaten, Wochen-Personen, Rollen,
 * Anwesenheit, Projekte, LPH-Werte, Projekt-Zuordnungen, Projektpläne, TODOs,
 * Meilensteine, Abhängigkeiten sowie die globalen Personen inkl. Rollen und
 * Abwesenheiten. Die HTTP-API bleibt davon unberührt: Das Frontend sendet und
 * erhält weiterhin komplette Wochen- und Personen-Objekte (JSON); der Server
 * verteilt sie beim Speichern auf die Tabellen und setzt sie beim Laden wieder
 *  Zusammen (Serializer/Deserializer).
 *
 *  Eine bestehende Datenbank aus dem alten Ein-Dokument-pro-Woche-Format wird
 *  automatisch auf das relationale Schema umgestellt.
 *
 *  Standardport ist 3003, überschreibbar über die Umgebungsvariable PORT.
 *  Endpunkte: GET /, GET /health, GET /load-all, POST /save-week,
 *  POST /delete-week,
 *  GET /backup (SQLite-Snapshot als .db-Download; zusätzlich automatische Backups
 *  nach sessions/backups/ beim Serverstart), POST /import (Backup-Wiederherstellung,
 *  roher Binär-Body, schema-validiert).
 *  Ungültige Abteilungen fallen auf ELT zurück.
 */
const express = require('express');
const fs = require('fs');
const path = require('path');
const os = require('os');
const zlib = require('zlib');
const crypto = require('crypto');
const { DatabaseSync } = require('node:sqlite');
// pdfjs-dist/mammoth: reine Textauszug-Bibliotheken für den Protokoll-Import
// (POST /project-protokolle/import) — erste Abhängigkeiten außer
// express/cors, siehe CLAUDE.md "### Protokolle". pdfjs-dist statt des
// zunächst gewählten pdf-parse: dessen mitgelieferte, seit 2017
// eingefrorene pdf.js-Kopie liefert in diesem Prozess reproduzierbar
// "bad XRef entry", sobald Node's eigenes http-Modul geladen ist (bereits
// durch das bloße `require('http')` allein auslösbar, unabhängig vom
// tatsächlichen PDF-Inhalt) — in einem Express-Server also immer, da
// express selbst transitiv http lädt. Ein eigenständiges pdfjs-dist ist
// nicht betroffen. Das native @napi-rs/canvas (nur für Seiten-Rendering
// zu Bildern, hier nie gebraucht) ist bei pdfjs-dist nur eine optionale
// Abhängigkeit; die Projekt-.npmrc (`omit=optional`) verhindert, dass es
// überhaupt installiert wird — keine native Kompilierung, kein Canvas.
const mammoth = require('mammoth');
// undici: NICHT für fetch() selbst (das liefert Node schon global) — nur für
// die Agent-Klasse, um der KI-Erkennung gegen einen eigenen Server (siehe
// fetchMitTimeout() weiter unten) ein höheres Verbindungslimit als Node/
// undicis eigenen Vorgabewert mitzugeben, siehe dort.
const { Agent: UndiciAgent } = require('undici');
let pdfjsLibPromise = null;
function ladePdfjs(){
  if(!pdfjsLibPromise) pdfjsLibPromise = import('pdfjs-dist/legacy/build/pdf.mjs');
  return pdfjsLibPromise;
}
async function extrahierePdfText(buf){
  const pdfjsLib = await ladePdfjs();
  const loadingTask = pdfjsLib.getDocument({ data: new Uint8Array(buf) });
  try {
    const doc = await loadingTask.promise;
    let text = '';
    for(let i = 1; i <= doc.numPages; i++){
      const page = await doc.getPage(i);
      const content = await page.getTextContent();
      // Textfragmente stehen ohne Zeilenumbrüche in content.items — erkennePunkteAusText()
      // braucht aber echte Zeilen (Punkt-Marker müssen am Zeilenanfang stehen). Ein
      // Umbruch wird eingefügt, sobald sich die Y-Koordinate (transform[5]) ändert,
      // exakt wie es das vormals genutzte pdf-parse intern selbst tat.
      let lastY = null;
      content.items.forEach(item => {
        if(lastY !== null && item.transform[5] !== lastY) text += '\n';
        else if(lastY !== null) text += ' ';
        text += item.str;
        lastY = item.transform[5];
      });
      text += '\n\n';
    }
    return text;
  } finally {
    loadingTask.destroy();
  }
}
const app = express();

// Wichtige Konstanten: Serverport (PORT-Env) und die gültigen Abteilungsordner
const PORT = process.env.PORT || 3003;
const VALID_DEPARTMENTS = new Set(['ELT','HLS','ARCH']);

// SANDBOX_WEEK_LABEL: die eine, dauerhaft reservierte "Sitzung", in der alle
// Planung-Terminpläne liegen (CLAUDE.md, "Planung — projektunabhängige
// Terminplan-Sandbox"). Kein echtes Datum, keine echte Kalenderwoche — der
// Name selbst ist der Marker, der dieses `weeks`-Label von jeder echten
// Sitzung unterscheidet. MUSS mit der gleichnamigen Konstante in
// public/js/globals.js übereinstimmen (kein gemeinsames Modulsystem zwischen
// Server und Client in diesem Projekt, siehe "Frontend architecture").
const SANDBOX_WEEK_LABEL = '__sandbox__';
// Projekt-Zuordnungs-Rollen aus ROLES in public/js/globals.js (bewusst dupliziert,
// kein Bundler vorhanden — NICHT zu verwechseln mit users.role ('admin'/'member')).
const VALID_DISCIPLINE_ROLES = new Set(['Planung','Projektleitung','Bauüberwachung']);
// VALID_AUFTRAGSSTATUS: der Weg von der Anfrage zum abgeschlossenen Auftrag
// (projects.auftragsstatus). Die Reihenfolge ist die fachliche Abfolge und
// steht genauso in AUFTRAGSSTATUS in public/js/auftrag.js — bewusst
// dupliziert, es gibt keinen Bundler. Serverseitig geprüft wird das nicht
// wegen der Oberfläche, sondern weil /save-week der einzige Schreibweg ist:
// ein Tippfehler im Wert wäre sonst dauerhaft in der Datenbank und jede
// Auswertung ("welche Projekte sind noch Angebot?") liefe daran vorbei.
const VALID_AUFTRAGSSTATUS = new Set(['anfrage','angebot','beauftragt','abgeschlossen']);
// DEFAULT_AUFTRAGSSTATUS: Vorgabe für Bestandsdaten und für unbelegte Felder.
// 'beauftragt' und nicht 'anfrage', weil der gesamte Altbestand beauftragte
// Arbeit ist — jeder andere Anfangswert hätte alle laufenden Projekte
// rückwirkend zu Akquise erklärt.
const DEFAULT_AUFTRAGSSTATUS = 'beauftragt';
// AUFTRAGSSTATUS_LABELS: lesbare Bezeichnungen für den CSV-Export. Der
// Schlüssel selbst ('anfrage') steht in der Datenbank, in einer Tabelle für
// Menschen hat er nichts zu suchen.
const AUFTRAGSSTATUS_LABELS = {
  anfrage: 'Anfrage', angebot: 'Angebot',
  beauftragt: 'Beauftragt', abgeschlossen: 'Abgeschlossen'
};

// normalizeAuftragsstatus: bringt einen eingehenden Wert auf einen gültigen
// Schlüssel. Wird beim SCHREIBEN gebraucht (serializeWeek) — dort ist der Wert
// bereits von der Route geprüft, leer oder fehlend darf er aber sein (alte
// Clients, Demo-Daten) und wird dann zur Vorgabe.
function normalizeAuftragsstatus(value){
  const v = String(value || '').trim();
  return VALID_AUFTRAGSSTATUS.has(v) ? v : DEFAULT_AUFTRAGSSTATUS;
}

// findInvalidAuftragsstatus: sucht in einer eingehenden Woche das erste Projekt
// mit unbekanntem Auftragsstatus. Getrennt von normalizeAuftragsstatus(), weil
// der Schreibweg BEIM SPEICHERN nicht stillschweigend zurechtbiegen darf: Ein
// unbekannter Wert ist ein Fehler im Aufruf und wird mit 400 abgewiesen,
// sonst käme aus der Oberfläche "Angebot" heraus und in der Datenbank stünde
// "beauftragt". Ein fehlendes/leeres Feld ist dagegen erlaubt (alte Clients).
// Rückgabe: { nr, name, wert } oder null, wenn alles gültig ist.
function findInvalidAuftragsstatus(week){
  const projects = Array.isArray(week && week.projects) ? week.projects : [];
  for(const p of projects){
    const raw = p && p.auftragsstatus;
    if(raw === undefined || raw === null || raw === '') continue;
    const v = String(raw).trim();
    if(!VALID_AUFTRAGSSTATUS.has(v)){
      return { nr: String((p && p.nr) || ''), name: String((p && p.name) || ''), wert: v };
    }
  }
  return null;
}

// Globale Middleware: CORS, JSON-Body-Limit (2 MB) und statisches Serving von public/ ohne Cache für Skripte/Styles
app.use(require('cors')());
// Der JSON-Parser darf NICHT über die Routen mit rohem Binär-Body laufen:
// Der Browser schickt beim Datei-Upload den Content-Type der Datei mit, und
// bei einer .json-Datei würde express.json() den Datenstrom verbrauchen, bevor
// express.raw() ihn bekommt ("Keine Datei empfangen"). 8 MB statt 2 MB, weil
// eine Sitzung mit vielen Projekten und langen Kommentaren sonst irgendwann
// still am 413 scheitert.
const RAW_BODY_ROUTES = new Set(['/import', '/project-files', '/project-protokolle/import']);
const jsonBodyParser = express.json({ limit: '8mb' });
app.use((req, res, next) => {
  if(req.method === 'POST' && RAW_BODY_ROUTES.has(req.path)) return next();
  jsonBodyParser(req, res, next);
});
// attachUser liest bei jedem Request das Session-Cookie und setzt req.user
// (siehe Definition weiter unten im Abschnitt "Auth: Sessions, Middleware,
// Endpunkte" — als function-Deklaration ist sie im gesamten Modul gehoistet).
// Muss vor express.static laufen, damit sie garantiert für jeden Request greift.
app.use(attachUser);
app.use(express.static(path.join(__dirname, 'public'), {
  setHeaders: (res, path) => {
    if(path.endsWith('.js') || path.endsWith('.css') || path.endsWith('.html')){
      res.set('Cache-Control', 'no-cache, no-store, must-revalidate');
      res.set('Pragma', 'no-cache');
      res.set('Expires', '0');
    }
  }
}));

// Datenverzeichnis sessions/ bei Bedarf anlegen (bind-mounted in Docker, enthält die DB)
const SESSIONS_DIR = path.join(__dirname, 'sessions');
if(!fs.existsSync(SESSIONS_DIR)) fs.mkdirSync(SESSIONS_DIR, { recursive: true });

// SQLite-Datenbank öffnen (wird angelegt, falls nicht vorhanden). Der Pfad ist
// über die Umgebungsvariable DB_PATH übersteuerbar (für Tests/Backups), sonst
// standardmäßig sessions/projektuebersicht.db.
const DB_PATH = process.env.DB_PATH ? path.resolve(process.env.DB_PATH) : path.join(SESSIONS_DIR, 'projektuebersicht.db');
const db = new DatabaseSync(DB_PATH);
db.exec('PRAGMA journal_mode = WAL;');
db.exec('PRAGMA foreign_keys = ON;');
db.exec('PRAGMA busy_timeout = 5000;'); // 5 s warten statt SQLITE_BUSY-Fehlern bei parallelen Schreibern

// ---------- Schema (voll relational normalisiert) ----------

// Der serialisierte Datenbestand einer Woche besteht aus folgenden Entitäten:
//  - weeks: Metadaten (id, label, date, locked)
//  - week_people / week_people_roles: personen-Kopien pro Woche
//  - attendance: Anwesenheit pro Woche/Person
//  - projects: Projekt-Metadaten pro Woche
//  - project_lph: LPH-Fortschrittswerte pro Projekt
//  - project_assign: Zuordnungen (Haupt/Optional) pro Projekt/Person/Rolle
//  - project_plan: Plan-Kopf (start, end, progress) pro Projekt
//  - plan_people: im Plan beteiligte Personen
//  - plan_todos / plan_todo_assignees / plan_todo_deps: TODO-Listen
//  - plan_milestones / plan_milestone_deps: Meilensteine
//  - globale Personen pro Abteilung kommen seit der Ablösung von
//    people/person_roles/person_absences aus users/user_roles/user_absences
//    (siehe loadGlobalPeople, migrateRetirePeopleTablesIntoUsers)

db.exec(`CREATE TABLE IF NOT EXISTS weeks (
  label TEXT NOT NULL,
  week_id TEXT NOT NULL DEFAULT '',
  date TEXT NOT NULL DEFAULT '',
  locked INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (label)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS week_people (
  week_label TEXT NOT NULL,
  person_id TEXT NOT NULL,
  name TEXT NOT NULL DEFAULT '',
  full_name TEXT NOT NULL DEFAULT '',
  array_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, person_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS week_people_roles (
  week_label TEXT NOT NULL,
  person_id TEXT NOT NULL,
  role TEXT NOT NULL,
  role_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, person_id, role)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS attendance (
  week_label TEXT NOT NULL,
  person_id TEXT NOT NULL,
  present INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, person_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS projects (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  nr TEXT NOT NULL DEFAULT '',
  name TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT '',
  status_author TEXT NOT NULL DEFAULT '',
  status_at TEXT NOT NULL DEFAULT '',
  "group" TEXT NOT NULL DEFAULT '',
  array_order INTEGER NOT NULL DEFAULT 0,
  order_value INTEGER NOT NULL DEFAULT 0,
  -- besprochen: im Teammeeting abgehakt. Gehört zur SITZUNG, nicht zum
  -- Projekt: Jede Woche wird jedes Projekt neu durchgegangen, und der
  -- Haken beantwortet die Frage "hatten wir das schon?" nur für diese eine
  -- Sitzung. Deshalb steht er hier und nicht an einer wochenunabhängigen
  -- Stelle; beim Duplizieren einer Sitzung wird er zurückgesetzt.
  besprochen INTEGER NOT NULL DEFAULT 0,
  -- groesse: Umfang des Projekts als Zahl zwischen 0 und 1, gemessen am
  -- Referenzhonorar (app_settings.referenzHonorar). Sie tritt an die Stelle
  -- des Honorars, wenn keines hinterlegt ist, und ist damit die Grundlage
  -- der Auslastungsrechnung in Stunden. 0 = keine Angabe: Das Projekt ist
  -- dann nicht bewertbar und wird in der Auslastung als Lücke ausgewiesen,
  -- nicht stillschweigend als Null gezählt.
  groesse REAL NOT NULL DEFAULT 0,
  -- auftragsstatus: Wo das Projekt zwischen Anfrage und Abschluss steht
  -- ('anfrage'|'angebot'|'beauftragt'|'abgeschlossen', siehe
  -- VALID_AUFTRAGSSTATUS). Im Unterschied zu besprochen gehört der Wert zum
  -- PROJEKT und nicht zur Sitzung: Ein Angebot bleibt ein Angebot, bis es
  -- beauftragt wird, und wandert beim Duplizieren der Sitzung deshalb mit.
  -- Die Gruppe "In Anbahnung" deutete das bisher nur an; sie bleibt die
  -- Sortierung auf dem Board, sagt aber nichts darüber, ob schon ein Angebot
  -- draußen ist.
  auftragsstatus TEXT NOT NULL DEFAULT 'beauftragt',
  -- updated_at: Zeitstempel der letzten Speicherung DIESES Projekts. Bis zur
  -- Planung-Sandbox (CLAUDE.md, "Planung — projektunabhängige Terminplan-
  -- Sandbox") gab es nur eine Revision je WOCHE (weeks.updated_at) — genug,
  -- solange jede Sitzung nur wenige Projekte auf einmal hat. Die Sandbox legt
  -- aber mehrere, voneinander unabhängige Terminpläne in EINER reservierten
  -- Woche ab; ohne eine Zeitangabe je Projekt ließe sich "zuletzt bearbeitet"
  -- in der Planungsliste nicht je Plan unterscheiden. Für echte Projekte
  -- harmlos mitgeführt, aber ungenutzt.
  updated_at TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (week_label, project_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS project_tags (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  tag TEXT NOT NULL,
  tag_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id, tag)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS project_lph (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  lph TEXT NOT NULL,
  value TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (week_label, project_id, lph)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS project_assign (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  person_id TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT '',
  state INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id, person_id, role)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS project_aufwand (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  lph TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT '',
  value REAL NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id, lph, role)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS project_aktuelle_lp (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  lph TEXT NOT NULL,
  PRIMARY KEY (week_label, project_id, lph)
)`);
// project_beauftragte_lph: welche Leistungsphasen für dieses Projekt
// BEAUFTRAGT sind — nicht zu verwechseln mit project_aktuelle_lp (die eine
// gerade amtierende Phase) oder project_lph (Fortschritt in Prozent).
// Grundlage für die Honorar-Terminplanung (siehe js/plan/plan-honorar.js):
// das vereinbarte Honorar wird auf genau diese Phasen verteilt.
db.exec(`CREATE TABLE IF NOT EXISTS project_beauftragte_lph (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  lph TEXT NOT NULL,
  PRIMARY KEY (week_label, project_id, lph)
)`);
// project_gewerke: welche Fachbereiche des Büros das Projekt bearbeiten
// ('ELT'|'HLS'|'ARCH', dieselben Werte wie users.department). Ein Projekt
// kann zu mehreren gehören — ein Schulanbau etwa zu ELT und HLS.
//
// Wozu: Die Auswahl der Projektbeteiligten wird darauf eingeschränkt. Ein
// reines Elektro-Projekt soll nicht die ganze Belegschaft zur Auswahl
// stellen; wer trotzdem jemanden aus einem anderen Gewerk braucht, holt ihn
// ausdrücklich dazu (siehe den Wizard in js/projekt-wizard.js).
//
// Eigene Tabelle statt einer Spalte mit Trennzeichen — gleiche Bauart wie
// project_beauftragte_lph, und damit dieselben Wege beim Serialisieren.
db.exec(`CREATE TABLE IF NOT EXISTS project_gewerke (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  gewerk TEXT NOT NULL,
  PRIMARY KEY (week_label, project_id, gewerk)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS project_plan (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  start TEXT NOT NULL DEFAULT '',
  end TEXT NOT NULL DEFAULT '',
  progress INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS plan_people (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  person_id TEXT NOT NULL,
  array_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id, person_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS plan_todos (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  todo_id TEXT NOT NULL,
  title TEXT NOT NULL DEFAULT '',
  start TEXT NOT NULL DEFAULT '',
  end TEXT NOT NULL DEFAULT '',
  done INTEGER NOT NULL DEFAULT 0,
  progress INTEGER NOT NULL DEFAULT 0,
  sort_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id, todo_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_assignees (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  todo_id TEXT NOT NULL,
  person_id TEXT NOT NULL,
  PRIMARY KEY (week_label, project_id, todo_id, person_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_deps (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  todo_id TEXT NOT NULL,
  depends_on_id TEXT NOT NULL,
  PRIMARY KEY (week_label, project_id, todo_id, depends_on_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS plan_milestones (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  milestone_id TEXT NOT NULL,
  name TEXT NOT NULL DEFAULT '',
  date TEXT NOT NULL DEFAULT '',
  position TEXT NOT NULL DEFAULT '',
  sort_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (week_label, project_id, milestone_id)
)`);
db.exec(`CREATE TABLE IF NOT EXISTS plan_milestone_deps (
  week_label TEXT NOT NULL,
  project_id TEXT NOT NULL,
  milestone_id TEXT NOT NULL,
  depends_on_id TEXT NOT NULL,
  PRIMARY KEY (week_label, project_id, milestone_id, depends_on_id)
)`);
// people / person_roles / person_absences (globale Personen pro Abteilung) sind
// abgelöst worden (siehe migrateRetirePeopleTablesIntoUsers weiter unten) —
// werden deshalb hier bewusst NICHT mehr unconditionally angelegt. Für Alt-DBs
// im früheren Ein-Dokument-Format legt migrateLegacySchema() sie transient an,
// migrateRetirePeopleTablesIntoUsers() überführt/droppt sie danach beim selben
// Start.

// ---------- User-Management, Sessions, Datei-Freigabe (Schema, additiv) ----------
db.exec(`CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL DEFAULT '',
  email TEXT NOT NULL DEFAULT '',
  password_hash TEXT NOT NULL,
  password_salt TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'member',
  person_id TEXT NOT NULL DEFAULT '',
  active INTEGER NOT NULL DEFAULT 1,
  must_change_password INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL DEFAULT ''
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_users_person_id ON users(person_id)`);

db.exec(`CREATE TABLE IF NOT EXISTS user_sessions (
  token TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT '',
  last_seen_at TEXT NOT NULL DEFAULT '',
  expires_at TEXT NOT NULL DEFAULT '',
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);

// user_roles/user_absences: Rollen (Planung/Projektleitung/Bauüberwachung) und
// Abwesenheiten je Benutzer — lösen die alten people/person_roles/
// person_absences-Tabellen vollständig ab (loadGlobalPeople liest nur noch
// hieraus, migrateRetirePeopleTablesIntoUsers überführt/droppt die Altdaten).
db.exec(`CREATE TABLE IF NOT EXISTS user_roles (
  user_id INTEGER NOT NULL, role TEXT NOT NULL, role_order INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, role),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// created_by: wer den Eintrag angelegt hat. Grundlage der Löschberechtigung —
// eintragen darf jeder für jeden (die Projektleitung pflegt Urlaube ihres
// Teams mit), aber entfernen nur die betroffene Person selbst, wer ihn
// eingetragen hat, oder ein Administrator. 0 = Altbestand ohne Angabe.
db.exec(`CREATE TABLE IF NOT EXISTS user_absences (
  user_id INTEGER NOT NULL, id TEXT NOT NULL,
  start TEXT NOT NULL DEFAULT '', end TEXT NOT NULL DEFAULT '',
  type TEXT NOT NULL DEFAULT '', note TEXT NOT NULL DEFAULT '',
  created_by INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, id),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// Persönliche KI-Zugangsdaten für den Protokoll-Import (POST
// /project-protokolle/import), siehe readUserAiKey()/writeUserAiKey() und
// CLAUDE.md "### Protokolle": jedes Konto pflegt seinen eigenen Schlüssel +
// optionale abweichende API-URL (z. B. ein Firmen-Proxy statt der
// offiziellen Cloud-API) unter Einstellungen → Mein Konto — bewusst eine
// eigene, kleine User-Kind-Tabelle statt Zeilen in app_settings, das
// strukturell ein firmenweiter Singleton-Speicher ist.
db.exec(`CREATE TABLE IF NOT EXISTS user_ai_keys (
  user_id INTEGER NOT NULL, provider TEXT NOT NULL,
  api_key TEXT NOT NULL DEFAULT '', api_url TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (user_id, provider),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);

db.exec(`CREATE TABLE IF NOT EXISTS project_files (
  id TEXT PRIMARY KEY,
  project_nr TEXT NOT NULL,
  project_name TEXT NOT NULL DEFAULT '',
  original_filename TEXT NOT NULL,
  stored_name TEXT NOT NULL,
  content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
  size_bytes INTEGER NOT NULL DEFAULT 0,
  uploaded_by_user_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL DEFAULT ''
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_project_files_scope ON project_files(project_nr)`);

db.exec(`CREATE TABLE IF NOT EXISTS project_file_reviewers (
  file_id TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  decision TEXT NOT NULL DEFAULT 'pending',
  decided_at TEXT NOT NULL DEFAULT '',
  note TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (file_id, user_id)
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_pfr_user_pending ON project_file_reviewers(user_id, decision)`);

db.exec(`CREATE TABLE IF NOT EXISTS project_file_comments (
  id TEXT PRIMARY KEY,
  file_id TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT ''
)`);

// Protokolle (Besprechungsprotokolle je Projekt) — wie project_files über
// project_nr mit dem Projekt verknüpft, NICHT über week_label/project_id:
// project_id wird bei jedem automatischen Sitzungswechsel
// (ensureCurrentWeekRollover()) neu vergeben, ein Protokoll muss aber über
// Sitzungen hinweg bestehen bleiben. week_label ist hier reine Information
// (welche Sitzung lief beim Anlegen), keine Scoping-Spalte.
db.exec(`CREATE TABLE IF NOT EXISTS project_protokolle (
  id TEXT PRIMARY KEY,
  project_nr TEXT NOT NULL,
  project_name TEXT NOT NULL DEFAULT '',
  nummer INTEGER NOT NULL,
  datum TEXT NOT NULL DEFAULT '',
  betreff TEXT NOT NULL DEFAULT '',
  ort TEXT NOT NULL DEFAULT '',
  verfasser_user_id INTEGER NOT NULL,
  week_label TEXT NOT NULL DEFAULT '',
  quelle_dateiname TEXT NOT NULL DEFAULT '',
  quelle_stored_name TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL DEFAULT ''
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_protokolle_project ON project_protokolle(project_nr)`);

// protokoll_teilnehmer: Freitext-Teilnehmerliste je Protokoll (Name + Firma),
// unabhängig von Benutzerkonten — Baubesprechungen haben typischerweise
// externe Teilnehmer (Bauherr, ausführende Firmen) ohne Login hier.
db.exec(`CREATE TABLE IF NOT EXISTS protokoll_teilnehmer (
  id TEXT PRIMARY KEY,
  protokoll_id TEXT NOT NULL,
  name TEXT NOT NULL DEFAULT '',
  firma TEXT NOT NULL DEFAULT '',
  anwesend INTEGER NOT NULL DEFAULT 1,
  array_order INTEGER NOT NULL DEFAULT 0
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_protokoll_teilnehmer_protokoll ON protokoll_teilnehmer(protokoll_id)`);

// protokoll_punkte: die eigentlichen Besprechungspunkte. stamm_id verbindet
// alle fortgeschriebenen Kopien "desselben" Punkts über mehrere Protokolle
// hinweg (siehe createProtokoll()) — nur daran hängt, ob ein Punkt als neu
// gilt (neu=1), NICHT an nummer/gruppe: beides ist freier, jederzeit vom
// Nutzer änderbarer Anzeige-Text (nummer z.B. "1", "1.1", "2.3"; gruppe z.B.
// "ELT"/"HLS" — keine feste Liste, entsteht einfach durchs Eintragen).
db.exec(`CREATE TABLE IF NOT EXISTS protokoll_punkte (
  id TEXT PRIMARY KEY,
  protokoll_id TEXT NOT NULL,
  stamm_id TEXT NOT NULL,
  nummer TEXT NOT NULL DEFAULT '',
  gruppe TEXT NOT NULL DEFAULT '',
  neu INTEGER NOT NULL DEFAULT 0,
  thema TEXT NOT NULL DEFAULT '',
  text TEXT NOT NULL DEFAULT '',
  zustaendig TEXT NOT NULL DEFAULT '',
  termin TEXT NOT NULL DEFAULT '',
  erledigt INTEGER NOT NULL DEFAULT 0,
  erstellt_am TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'offen',
  array_order INTEGER NOT NULL DEFAULT 0
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_protokoll_punkte_protokoll ON protokoll_punkte(protokoll_id)`);

// lph_templates: firmenweite Aufgaben-Vorlagen je Leistungsphase. Anders als
// alle Tabellen darüber hängt diese an KEINER Sitzung und an keinem Projekt —
// sie ist globale Konfiguration ("in LPH 5 machen wir immer diese Schritte"),
// aus der auf der Projektplan-Seite per Klick TODOs erzeugt werden (siehe
// js/plan/plan-templates.js). offset_days = Start relativ zum beim Einfügen
// gewählten Datum (0 = am Starttag), duration_days = Dauer in Kalendertagen
// (mindestens 1). Die erzeugten TODOs sind danach ganz normale plan_todos und
// haben keinerlei Verbindung mehr zur Vorlage — spätere Änderungen an der
// Vorlage wirken sich also NICHT auf bereits eingefügte Aufgaben aus (bewusst:
// ein laufender Projektplan soll sich nicht rückwirkend ändern).
db.exec(`CREATE TABLE IF NOT EXISTS lph_templates (
  id TEXT PRIMARY KEY,
  lph TEXT NOT NULL,
  title TEXT NOT NULL DEFAULT '',
  offset_days INTEGER NOT NULL DEFAULT 0,
  duration_days INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER NOT NULL DEFAULT 0
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_lph_templates_lph ON lph_templates(lph)');

// lph_honorar_anteile: firmenweite Honorar-Anteile je Leistungsphase in
// Prozent (HOAI-Sätze). Bewusst NICHT als Konstante im Code, sondern
// pflegbar — die Sätze hängen vom Leistungsbild ab (Technische Ausrüstung
// nach Anlage 15 unterscheidet sich von der Objektplanung nach Anlage 10)
// und ändern sich mit HOAI-Novellen. Startet leer und wird in der
// Honorar-Maske auf der Projektplan-Seite von Administratoren befüllt.
db.exec(`CREATE TABLE IF NOT EXISTS lph_honorar_anteile (
  lph TEXT PRIMARY KEY,
  percent REAL NOT NULL DEFAULT 0
)`);

// app_settings: firmenweite Einzelwerte als Schlüssel/Wert-Paare. Werte
// stehen als TEXT und werden beim Lesen gewandelt; welche Schlüssel es gibt,
// welchen Typ und welche Vorgabe sie haben, steht in APP_SETTING_DEFS weiter
// unten — dort liegt auch die Prüfung. Eine Zeile existiert erst, wenn der
// Wert einmal gesetzt wurde; sonst gilt die Vorgabe. Damit brauchen neue
// Einstellungen weder eine eigene Tabelle noch eine Migration.
db.exec(`CREATE TABLE IF NOT EXISTS app_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL DEFAULT ''
)`);

// holidays: firmenweite Feiertage und sonstige arbeitsfreie Tage. Bewusst
// eine eigene Tabelle statt eines JSON-Werts in app_settings — es ist eine
// Liste, die wächst, und sie soll sich einzeln pflegen lassen. Sie wirkt
// überall dort, wo in WERKTAGEN gerechnet wird: workdaysBetween()/
// addWorkdays() in public/js/utils.js, also in der Terminplan-Berechnung aus
// dem Honorar und in der Ressourcen-Auslastung des Projektbereichs. Die
// Terminplanung über Abhängigkeiten (Auto-Schedule) rechnet weiterhin in
// Kalendertagen — das war schon vorher so.
db.exec(`CREATE TABLE IF NOT EXISTS holidays (
  date TEXT PRIMARY KEY,
  name TEXT NOT NULL DEFAULT ''
)`);

// ---------- Migration: altes Ein-Dokument-Format -> relationales Schema ----------

// hasLegacyWeeksTable: erkennt das alte Schema an der Daten-Spalte in `weeks`.
function hasLegacyWeeksTable(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('weeks') WHERE name = 'data'").all();
  return cols.length > 0;
}

// serializeWeek: zerlegt ein Wochen-Objekt (JSON vom Frontend) in die relationalen
// Tabellen. Wird in einer Transaktion vom Aufrufer verwendet (deleteWeekData +
// serializeWeek). Ersetzt alle Zeilen der Woche.
function serializeWeek(week){
  const weekLabel = String(week.label || '');
  const nowIso = new Date().toISOString(); // Revision: wird bei jedem Speichern aktualisiert
  const upsertWeek = db.prepare(`INSERT INTO weeks (label, week_id, date, locked, updated_at) VALUES (?, ?, ?, ?, ?)
    ON CONFLICT(label) DO UPDATE SET week_id = excluded.week_id, date = excluded.date, locked = excluded.locked, updated_at = excluded.updated_at`);
  upsertWeek.run(weekLabel, String(week.id || ''), String(week.date || ''), week.locked ? 1 : 0, nowIso);

  const insWeekPerson = db.prepare(`INSERT INTO week_people (week_label, person_id, name, full_name, array_order)
    VALUES (?, ?, ?, ?, ?)`);
  const insWeekPersonRole = db.prepare(`INSERT OR IGNORE INTO week_people_roles (week_label, person_id, role, role_order)
    VALUES (?, ?, ?, ?)`);
  const insAttendance = db.prepare(`INSERT OR REPLACE INTO attendance (week_label, person_id, present)
    VALUES (?, ?, ?)`);

  // Sicherheitsnetz gegen doppelte Personen-Kennungen im Teilnehmerkreis:
  // ein zweiter Eintrag mit derselben id würde den Primärschlüssel verletzen
  // und die gesamte Transaktion (= das Speichern der Sitzung) abbrechen.
  const seenPersonIds = new Set();
  (Array.isArray(week.people) ? week.people : []).filter(p => {
    const pid = String((p && p.id) || '');
    if(!pid || seenPersonIds.has(pid)) return false;
    seenPersonIds.add(pid);
    return true;
  }).forEach((p, idx) => {
    insWeekPerson.run(weekLabel, String(p.id || ''), String(p.name || ''), String(p.fullName || ''), idx);
    (Array.isArray(p.role) ? p.role : []).forEach((r, ridx) => insWeekPersonRole.run(weekLabel, String(p.id || ''), String(r || ''), ridx));
  });
  const att = week.attendance || {};
  Object.keys(att).forEach(pid => {
    insAttendance.run(weekLabel, pid, att[pid] ? 1 : 0);
  });

  const insProject = db.prepare(`INSERT INTO projects (week_label, project_id, nr, name, status, status_author, status_at, "group", array_order, order_value, project_uid, honorar, besprochen, auftragsstatus, groesse, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
  const insBeauftragteLp = db.prepare(`INSERT OR IGNORE INTO project_beauftragte_lph (week_label, project_id, lph)
    VALUES (?, ?, ?)`);
  const insGewerk = db.prepare(`INSERT OR IGNORE INTO project_gewerke (week_label, project_id, gewerk)
    VALUES (?, ?, ?)`);
  const insTag = db.prepare(`INSERT OR IGNORE INTO project_tags (week_label, project_id, tag, tag_order)
    VALUES (?, ?, ?, ?)`);
  const insLph = db.prepare(`INSERT OR REPLACE INTO project_lph (week_label, project_id, lph, value)
    VALUES (?, ?, ?, ?)`);
  const insAssign = db.prepare(`INSERT OR REPLACE INTO project_assign (week_label, project_id, person_id, role, state)
    VALUES (?, ?, ?, ?, ?)`);
  const insAufwand = db.prepare(`INSERT OR REPLACE INTO project_aufwand (week_label, project_id, lph, role, value)
    VALUES (?, ?, ?, ?, ?)`);
  const insAktuelleLp = db.prepare(`INSERT OR IGNORE INTO project_aktuelle_lp (week_label, project_id, lph)
    VALUES (?, ?, ?)`);
  const insPlan = db.prepare(`INSERT OR REPLACE INTO project_plan (week_label, project_id, start, end, progress)
    VALUES (?, ?, ?, ?, ?)`);
  const insPlanPerson = db.prepare(`INSERT OR IGNORE INTO plan_people (week_label, project_id, person_id, array_order)
    VALUES (?, ?, ?, ?)`);
  const insTodo = db.prepare(`INSERT INTO plan_todos (week_label, project_id, todo_id, title, start, end, done, progress, sort_order)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`);
  const insTodoAssignee = db.prepare(`INSERT OR IGNORE INTO plan_todo_assignees (week_label, project_id, todo_id, person_id)
    VALUES (?, ?, ?, ?)`);
  const insTodoDep = db.prepare(`INSERT OR IGNORE INTO plan_todo_deps (week_label, project_id, todo_id, depends_on_id)
    VALUES (?, ?, ?, ?)`);
  const insMilestone = db.prepare(`INSERT INTO plan_milestones (week_label, project_id, milestone_id, name, date, position, sort_order)
    VALUES (?, ?, ?, ?, ?, ?, ?)`);
  const insMilestoneDep = db.prepare(`INSERT OR IGNORE INTO plan_milestone_deps (week_label, project_id, milestone_id, depends_on_id)
    VALUES (?, ?, ?, ?)`);

  (Array.isArray(week.projects) ? week.projects : []).forEach((p, idx) => {
    const projId = String(p.id || '');
    insProject.run(weekLabel, projId, String(p.nr || ''), String(p.name || ''),
      String(p.status || ''), String(p.statusAuthor || ''), String(p.statusAt || ''),
      String(p.group || ''), idx, typeof p.order === 'number' ? p.order : idx,
      String(p.projectUid || ''), Number(p.honorar) || 0, p.besprochen ? 1 : 0,
      normalizeAuftragsstatus(p.auftragsstatus), normalizeGroesse(p.groesse), nowIso);
    (Array.isArray(p.beauftragteLP) ? p.beauftragteLP : []).forEach(lph => {
      insBeauftragteLp.run(weekLabel, projId, String(lph || ''));
    });
    // Nur gültige Gewerke schreiben: Ein unbekannter Wert wäre stiller
    // Datenmüll, der die Beteiligten-Auswahl später leer laufen ließe.
    (Array.isArray(p.gewerke) ? p.gewerke : []).forEach(g => {
      const wert = String(g || '').trim().toUpperCase();
      if(VALID_DEPARTMENTS.has(wert)) insGewerk.run(weekLabel, projId, wert);
    });
    (Array.isArray(p.tags) ? p.tags : []).forEach((tag, tidx) => {
      const clean = String(tag || '').trim();
      if(clean) insTag.run(weekLabel, projId, clean, tidx);
    });
    const lph = p.lph || {};
    Object.keys(lph).forEach(l => insLph.run(weekLabel, projId, String(l || ''), String(lph[l] === undefined ? '' : lph[l])));
    const assign = p.assign || {};
    Object.keys(assign).forEach(key => {
      // Key-Format: "personId|Rolle"
      const sep = key.indexOf('|');
      const pid = sep >= 0 ? key.slice(0, sep) : key;
      const role = sep >= 0 ? key.slice(sep + 1) : '';
      insAssign.run(weekLabel, projId, pid, role, Number(assign[key]) || 0);
    });
    const aufwand = p.aufwandProLP || {};
    Object.keys(aufwand).forEach(lph => {
      const roles = aufwand[lph] || {};
      Object.keys(roles).forEach(role => {
        const v = Number(roles[role]);
        if(!isNaN(v)) insAufwand.run(weekLabel, projId, String(lph || ''), String(role || ''), v);
      });
    });
    (Array.isArray(p.aktuelleLP) ? p.aktuelleLP : []).forEach(lph => {
      insAktuelleLp.run(weekLabel, projId, String(lph || ''));
    });
    const plan = p.plan || null;
    if(plan){
      insPlan.run(weekLabel, projId, String(plan.start || ''), String(plan.end || ''), Number(plan.progress) || 0);
      (Array.isArray(plan.people) ? plan.people : []).forEach((pid, pidx) => insPlanPerson.run(weekLabel, projId, String(pid || ''), pidx));
      (Array.isArray(plan.todos) ? plan.todos : []).forEach((t, tidx) => {
        insTodo.run(weekLabel, projId, String(t.id || ''), String(t.title || ''),
          String(t.start || ''), String(t.end || ''), t.done ? 1 : 0, Number(t.progress) || 0, tidx);
        (Array.isArray(t.assignees) ? t.assignees : []).forEach(pid => insTodoAssignee.run(weekLabel, projId, String(t.id || ''), String(pid || '')));
        (Array.isArray(t.dependsOn) ? t.dependsOn : []).forEach(did => insTodoDep.run(weekLabel, projId, String(t.id || ''), String(did || '')));
      });
      (Array.isArray(plan.milestones) ? plan.milestones : []).forEach((m, midx) => {
        insMilestone.run(weekLabel, projId, String(m.id || ''), String(m.name || ''), String(m.date || ''), String(m.position || ''), midx);
        (Array.isArray(m.dependsOn) ? m.dependsOn : []).forEach(did => insMilestoneDep.run(weekLabel, projId, String(m.id || ''), String(did || '')));
      });
    }
  });
  // Rückgabe der Revision für den Aufrufer (saveWeekRelational -> Route)
  return nowIso;
}

// deleteWeekData: entfernt alle relationalen Zeilen einer Woche (Label)
function deleteWeekData(weekLabel){
  const tables = ['week_people_roles','week_people','attendance','project_tags','project_lph','project_assign',
    'project_aufwand','project_aktuelle_lp','project_beauftragte_lph','project_gewerke',
    'project_plan','plan_people','plan_todos','plan_todo_assignees','plan_todo_deps',
    'plan_milestones','plan_milestone_deps','projects'];
  tables.forEach(t => {
    db.prepare(`DELETE FROM ${t} WHERE week_label = ?`).run(weekLabel);
  });
  // Die Wochen-Tabelle selbst nutzt `label` statt `week_label`
  db.prepare('DELETE FROM weeks WHERE label = ?').run(weekLabel);
}

// saveWeekRelational: speichert eine Woche vollständig relational (Transaktion).
// Rückgabe: die neu gesetzte Revision `updated_at` (ISO-Zeitstempel), damit der
// Client seine lokale Basis aktualisieren kann (sonst würde er beim nächsten
// Speichern einen falschen Konflikt mit sich selbst melden).
function saveWeekRelational(week){
  const weekLabel = String(week.label || '');
  const tx = db.exec('BEGIN');
  try {
    deleteWeekData(weekLabel);
    const updatedAt = serializeWeek(week);
    db.exec('COMMIT');
    return updatedAt;
  } catch(err){
    db.exec('ROLLBACK');
    throw err;
  }
}

// loadGlobalPeople: setzt die (jetzt firmenweite, nicht mehr abteilungs-
// gescopte) Personenliste aus den Tabellen users/user_roles/user_absences
// zusammen. Rückgabeform unverändert: Array von
// {id,name,fullName,email,role:[],absences:[]}.
function loadGlobalPeople(){
  // person_id != '' : Ein Konto ohne Personen-Kennung (z. B. das Bootstrap-
  // Konto "admin") ist keine planbare Person. Ohne diesen Filter landen
  // mehrere solcher Konten mit derselben leeren Kennung im Teilnehmerkreis
  // einer Sitzung und verletzen dort den Primärschlüssel
  // (week_label, person_id) — das Speichern schlüge dauerhaft mit 500 fehl.
  const users = db.prepare(`SELECT * FROM users WHERE active = 1 AND person_id != '' ORDER BY display_name, username`).all();
  const roles = db.prepare(`SELECT * FROM user_roles ORDER BY role_order`).all();
  const absences = db.prepare(`SELECT * FROM user_absences`).all();
  const rolesByUser = {}; roles.forEach(r => (rolesByUser[r.user_id] = rolesByUser[r.user_id] || []).push(r.role));
  const absByUser = {}; absences.forEach(a => (absByUser[a.user_id] = absByUser[a.user_id] || []).push({ id:a.id, start:a.start, end:a.end, type:a.type, note:a.note }));
  return users.map(u => ({
    id: u.person_id,
    name: u.initials || u.username,
    fullName: u.display_name,
    email: u.email,
    role: rolesByUser[u.id] || [],
    absences: absByUser[u.id] || [],
    // Vertragliche Wochenstunden; 0 heißt "nicht gepflegt" und wird im
    // Frontend wie eine volle Stelle behandelt (js/load/load.js).
    capacityHours: Number(u.capacity_hours) || 0,
    // Gewerk der Person (users.department, ein VALID_DEPARTMENTS-Wert).
    // Nötig für die Auswahl der Projektbeteiligten: Ein Projekt trägt seine
    // Gewerke (project_gewerke), und angeboten werden zunächst nur die
    // Mitarbeiter daraus. Leer = keinem Gewerk zugeordnet; solche Personen
    // erscheinen nur über die ausdrückliche Auswahl "aus anderen Gewerken".
    gewerk: u.department || '',
  }));
}

// loadWeeks: setzt alle Wochen (jetzt eine einzige globale Reihe, nicht mehr
// pro Abteilung) aus den relationalen Tabellen wieder zu kompletten
// Wochen-Objekten zusammen. Rückgabe: Array von Wochen-Objekten, sortiert
// nach Label.
function loadWeeks(){
  const weeks = db.prepare('SELECT * FROM weeks ORDER BY label').all();
  const weekPeople = db.prepare('SELECT * FROM week_people ORDER BY array_order').all();
  const weekRoles = db.prepare('SELECT * FROM week_people_roles ORDER BY role_order').all();
  const attendance = db.prepare('SELECT * FROM attendance').all();
  const projects = db.prepare('SELECT * FROM projects ORDER BY array_order').all();
  const tags = db.prepare('SELECT * FROM project_tags ORDER BY tag_order').all();
  const lph = db.prepare('SELECT * FROM project_lph').all();
  const assign = db.prepare('SELECT * FROM project_assign').all();
  const aufwand = db.prepare('SELECT * FROM project_aufwand').all();
  const aktuelleLp = db.prepare('SELECT * FROM project_aktuelle_lp').all();
  const beauftragteLp = db.prepare('SELECT * FROM project_beauftragte_lph').all();
  const gewerke = db.prepare('SELECT * FROM project_gewerke').all();
  const plans = db.prepare('SELECT * FROM project_plan').all();
  const planPeople = db.prepare('SELECT * FROM plan_people ORDER BY array_order').all();
  const todos = db.prepare('SELECT * FROM plan_todos ORDER BY sort_order').all();
  const todoAssignees = db.prepare('SELECT * FROM plan_todo_assignees').all();
  const todoDeps = db.prepare('SELECT * FROM plan_todo_deps').all();
  const milestones = db.prepare('SELECT * FROM plan_milestones ORDER BY sort_order').all();
  const milestoneDeps = db.prepare('SELECT * FROM plan_milestone_deps').all();

  // Index-Karten für schnellen Zugriff
  const weekKey = (label) => label;
  const projKey = (label, pid) => label + '|' + pid;
  const todoKey = (label, pid, tid) => label + '|' + pid + '|' + tid;
  const msKey = (label, pid, mid) => label + '|' + pid + '|' + mid;

  const weekPeopleMap = {};
  const weekRolesMap = {};
  const attendanceMap = {};
  // Anhängen statt per Index setzen: Die Abfrage ist bereits nach array_order
  // sortiert. Eine Index-Zuweisung würde bei zwei gleichen Ordnungszahlen in
  // derselben Woche (möglich nach der Abteilungs-Zusammenführung) die erste
  // Person still überschreiben und Lücken im Array hinterlassen.
  weekPeople.forEach(r => {
    const k = weekKey(r.week_label);
    (weekPeopleMap[k] = weekPeopleMap[k] || []).push({ id: r.person_id, name: r.name, fullName: r.full_name });
  });
  weekRoles.forEach(r => {
    const k = weekKey(r.week_label);
    const arr = (weekRolesMap[k] = weekRolesMap[k] || {});
    (arr[r.person_id] = arr[r.person_id] || []).push(r.role);
  });
  attendance.forEach(r => {
    attendanceMap[weekKey(r.week_label) + '|' + r.person_id] = !!r.present;
  });

  const projectsMap = {};
  projects.forEach(r => {
    projectsMap[projKey(r.week_label, r.project_id)] = {
      id: r.project_id, nr: r.nr, name: r.name, status: r.status,
      statusAuthor: r.status_author || '', statusAt: r.status_at || '',
      group: r.group,
      tags: [], lph: {}, assign: {}, order: r.order_value, plan: null,
      projectUid: r.project_uid,
      honorar: r.honorar,
      besprochen: !!r.besprochen,
      groesse: normalizeGroesse(r.groesse),
      // Rückfall auf die Vorgabe, weil Zeilen aus der Zeit vor der Spalte
      // (und aus einem älteren Backup, siehe IMPORT_OPTIONAL_COLS) einen
      // leeren Wert tragen können — die Oberfläche soll keinen Leerzustand
      // anzeigen müssen.
      auftragsstatus: normalizeAuftragsstatus(r.auftragsstatus),
      beauftragteLP: [],
      gewerke: [],
      updatedAt: r.updated_at || '',
      _arrayOrder: r.array_order,
    };
  });
  tags.forEach(r => { const p = projectsMap[projKey(r.week_label, r.project_id)]; if(p) p.tags.push(r.tag); });
  lph.forEach(r => { const p = projectsMap[projKey(r.week_label, r.project_id)]; if(p) p.lph[r.lph] = r.value; });
  assign.forEach(r => {
    const p = projectsMap[projKey(r.week_label, r.project_id)];
    if(p) p.assign[r.person_id + (r.role ? '|' + r.role : '')] = r.state;
  });
  aufwand.forEach(r => {
    const p = projectsMap[projKey(r.week_label, r.project_id)];
    if(p){ p.aufwandProLP = p.aufwandProLP || {}; (p.aufwandProLP[r.lph] = p.aufwandProLP[r.lph] || {})[r.role] = r.value; }
  });
  aktuelleLp.forEach(r => {
    const p = projectsMap[projKey(r.week_label, r.project_id)];
    if(p){ p.aktuelleLP = p.aktuelleLP || []; if(!p.aktuelleLP.includes(r.lph)) p.aktuelleLP.push(r.lph); }
  });
  beauftragteLp.forEach(r => {
    const p = projectsMap[projKey(r.week_label, r.project_id)];
    if(p && !p.beauftragteLP.includes(r.lph)) p.beauftragteLP.push(r.lph);
  });
  gewerke.forEach(r => {
    const p = projectsMap[projKey(r.week_label, r.project_id)];
    if(p && !p.gewerke.includes(r.gewerk)) p.gewerke.push(r.gewerk);
  });

  const planMap = {};
  plans.forEach(r => { planMap[projKey(r.week_label, r.project_id)] = { start: r.start, end: r.end, progress: r.progress, people: [], todos: [], milestones: [] }; });
  planPeople.forEach(r => {
    const plan = planMap[projKey(r.week_label, r.project_id)];
    if(plan) plan.people.push(r.person_id);
  });

  const todoMap = {};
  todos.forEach(r => {
    const k = todoKey(r.week_label, r.project_id, r.todo_id);
    todoMap[k] = { id: r.todo_id, title: r.title, start: r.start, end: r.end, done: !!r.done, dependsOn: [], assignees: [], progress: r.progress };
  });
  todoAssignees.forEach(r => {
    const t = todoMap[todoKey(r.week_label, r.project_id, r.todo_id)];
    if(t) t.assignees.push(r.person_id);
  });
  todoDeps.forEach(r => {
    const t = todoMap[todoKey(r.week_label, r.project_id, r.todo_id)];
    if(t) t.dependsOn.push(r.depends_on_id);
  });

  const msMap = {};
  milestones.forEach(r => {
    const k = msKey(r.week_label, r.project_id, r.milestone_id);
    msMap[k] = { id: r.milestone_id, name: r.name, date: r.date, position: r.position, dependsOn: [] };
  });
  milestoneDeps.forEach(r => {
    const m = msMap[msKey(r.week_label, r.project_id, r.milestone_id)];
    if(m) m.dependsOn.push(r.depends_on_id);
  });

  // TODO- und Meilenstein-Objekte in die Projekt-Pläne einsortieren
  Object.keys(todoMap).forEach(k => {
    const parts = k.split('|');
    const plan = planMap[parts[0] + '|' + parts[1]];
    if(plan) plan.todos.push(todoMap[k]);
  });
  Object.keys(msMap).forEach(k => {
    const parts = k.split('|');
    const plan = planMap[parts[0] + '|' + parts[1]];
    if(plan) plan.milestones.push(msMap[k]);
  });

  // Wochen-Objekte zusammensetzen
  return weeks.map(w => {
    const wkey = weekKey(w.label);
    const people = (weekPeopleMap[wkey] || []).filter(Boolean).map(p => {
      const roles = (weekRolesMap[wkey] || {})[p.id] || [];
      const full = Object.assign({}, p);
      if(roles.length) full.role = roles;
      return full;
    });
    const att = {};
    (weekPeopleMap[wkey] || []).filter(Boolean).forEach(p => {
      const present = attendanceMap[wkey + '|' + p.id];
      if(present !== undefined) att[p.id] = present;
    });
    const weekProjects = Object.keys(projectsMap)
      .filter(k => k.startsWith(w.label + '|'))
      .map(k => {
        const p = projectsMap[k];
        const plan = planMap[k];
        if(plan) p.plan = plan;
        return p;
      })
      .sort((a,b) => a._arrayOrder - b._arrayOrder)
      .map(({ _arrayOrder, ...rest }) => rest);
    return {
      id: w.week_id, label: w.label, date: w.date,
      people, projects: weekProjects, attendance: att, locked: !!w.locked,
      updatedAt: w.updated_at || '',
    };
  });
}

// ---------- Datenbank-Backup ----------
//
// Zwei Wege: (1) Beim Serverstart wird automatisch eine Kopie der SQLite-DB
// als zeitgestempelte Datei in sessions/backups/ abgelegt (nur die letzten
// aufbewahrten Backups werden behalten). (2) GET /backup liefert einen konsistenten
// Snapshot der DB als herunterladbare .db-Datei (z. B. über den Button
// "DB Backup" im Menü der Hauptseite). Beide nutzen `VACUUM INTO`, das auch
// bei aktivem WAL-Mode eine konsistente Einzeldatei erzeugt (WAL-Inhalte
// werden einbezogen).

// BACKUP_DIR: Zielordner der automatischen Backups (sessions/backups, Docker-
// bind-mounted → bleibt persistent).
const BACKUP_DIR = path.join(SESSIONS_DIR, 'backups');
// Anzahl der automatisch behaltenen Backups (die ältesten werden verworfen)
// Wie viele automatische Backups aufbewahrt werden, ist seit dem Ausbau der
// Einstellungen firmenweit pflegbar (Schlüssel 'maxBackups'); dieser Wert ist
// nur noch die Vorgabe, falls nichts gesetzt ist.
const DEFAULT_MAX_BACKUPS = 10;

// createDbSnapshot: erzeugt einen konsistenten Snapshot der SQLite-DB in die
// angegebene Zieldatei (VACUUM INTO; funktioniert auch bei WAL-Mode).
function createDbSnapshot(targetPath){
  const quoted = String(targetPath).replace(/'/g, "''");
  db.exec(`VACUUM INTO '${quoted}'`);
}

// runAutoBackup: legt ein zeitgestempeltes Backup in BACKUP_DIR ab und
// verwirft ältere, sobald die eingestellte Anzahl überschritten ist. Fehler werden nur
// geloggt (Backup darf den Serverstart nicht verhindern).
function runAutoBackup(){
  try {
    if(!fs.existsSync(BACKUP_DIR)) fs.mkdirSync(BACKUP_DIR, { recursive: true });
    // Zeitstempel mit Millisekunden, damit auch zwei Backups innerhalb einer
    // Sekunde (z. B. direkt nach dem Import) eindeutige Dateinamen erhalten.
    const base = 'projektuebersicht_' + new Date().toISOString().replace(/[:.]/g, '-').slice(0, 23);
    let file = path.join(BACKUP_DIR, base + '.db');
    let suffix = 1;
    while(fs.existsSync(file)) file = path.join(BACKUP_DIR, base + '-' + (suffix++) + '.db');
    createDbSnapshot(file);
    console.log('Datenbank-Backup erstellt:', file);

    // Nur die letzten (einstellbar, Schlüssel 'maxBackups') behalten
    const backups = fs.readdirSync(BACKUP_DIR)
      .filter(f => /^projektuebersicht_.+\.db$/.test(f))
      .map(f => ({ name: f, mtime: fs.statSync(path.join(BACKUP_DIR, f)).mtimeMs }))
      .sort((a, b) => b.mtime - a.mtime);
    backups.slice(readAppSetting('maxBackups')).forEach(b => {
      fs.unlinkSync(path.join(BACKUP_DIR, b.name));
    });
  } catch(err){
    console.error('Automatisches Backup fehlgeschlagen:', err.message);
  }
}

// ---------- Datenbank-Import (Wiederherstellung aus Backup) ----------
//
// POST /import nimmt eine mit "DB Backup" erzeugte .db-Datei als rohen
// Binär-Body entgegen, validiert das Schema und ersetzt den gesamten
// Datenbestand der laufenden Datenbank durch den Inhalt der Backup-Datei.
// Das Aufspielen erfolgt per ATTACH + DELETE/INSERT-SELECT in einer
// Transaktion: Die Live-Verbindung bleibt stabil, bei Fehlern wird
// zurückgerollt. Vor dem Import wird der Zustand vor dem Import als frisches
// Backup gesichert (runAutoBackup), damit er rückgängig gemacht werden kann.

// IMPORT_TABLES: die Sitzungs-/Projektdaten-Tabellen des relationalen Schemas;
// Grundlage für Validierung, Löschvorgang und Datentransfer. Die Reihenfolge
// ist ohne Bedeutung, da das Schema keine Foreign-Key-Constraints deklariert.
// BEWUSST NICHT enthalten sind die firmenweiten Konfigurations- und
// Kontotabellen (users, user_sessions, user_roles, user_absences,
// project_files, project_file_reviewers, project_file_comments sowie
// lph_templates): eine Wiederherstellung soll Sitzungsdaten zurückrollen,
// aber niemals Konten, Freigabestände oder Aufgaben-Vorlagen überschreiben.
const IMPORT_TABLES = ['weeks','week_people','week_people_roles','attendance','projects',
  'project_tags','project_lph','project_assign','project_aufwand','project_aktuelle_lp',
  'project_beauftragte_lph',
  'project_gewerke',
  'project_plan','plan_people','plan_todos','plan_todo_assignees','plan_todo_deps',
  'plan_milestones','plan_milestone_deps',
  'project_protokolle','protokoll_teilnehmer','protokoll_punkte'];

// dbTableColumns: liefert die Spaltennamen einer Tabelle (funktioniert gegen
// jede DatabaseSync-Instanz, auch read-only Backups) via pragma_table_info.
function dbTableColumns(conn, table){
  return conn.prepare('SELECT name FROM pragma_table_info(?)').all(table).map(r => r.name);
}

// IMPORT_OPTIONAL_COLS: Spalten, die in älteren Backups fehlen dürfen (sie
// werden beim Import mit dem Default-Wert der Live-Tabelle neu belegt).
// Die Liste muss mitwachsen, wenn Spalten ergänzt werden — sonst ist
// ausgerechnet das automatische Backup, das unmittelbar VOR einer
// Wiederherstellung geschrieben wird, nach einem Versionssprung nicht mehr
// einspielbar.
const IMPORT_OPTIONAL_COLS = ['updated_at', 'project_uid', 'honorar', 'status_author', 'status_at',
  'auftragsstatus', 'groesse', 'besprochen'];

// restoreDbFromBackup: validiert die Backup-Datei (muss das bekannte Schema
// mit identischen Spalten wie die Live-DB besitzen) und ersetzt den
// Datenbestand der Live-DB durch den Snapshot. Parameter: backupPath (Pfad).
// Wirft bei ungültigem Schema oder Transferfehler (Transaktion wird gerollt).
function restoreDbFromBackup(backupPath){
  const backupConn = new DatabaseSync(backupPath, { readOnly: true });
  try {
    // 1) Schema-Validierung: Tabelle muss existieren und jede Spalte der
    // Live-DB (außer den als optional markierten) enthalten. Zusätzliche
    // Spalten im Backup (z. B. aus einer neueren Version) werden ignoriert.
    for(const t of IMPORT_TABLES){
      const liveCols = dbTableColumns(db, t);
      const backCols = dbTableColumns(backupConn, t);
      if(liveCols.length === 0) throw new Error('Live-Schema defekt (Tabelle fehlt): ' + t);
      if(backCols.length === 0) throw new Error('Ungültiges Backup (Tabelle fehlt): ' + t);
      const missing = liveCols.filter(c => !backCols.includes(c) && !IMPORT_OPTIONAL_COLS.includes(c));
      if(missing.length > 0) throw new Error('Backup-Schema passt nicht zur Live-DB (Tabelle ' + t + ', fehlt: ' + missing.join(', ') + ')');
    }

    // 2) Datentransfer: Backup anbinden, Bestand löschen, Snapshot übernehmen.
    // ATTACH und BEGIN stehen INNERHALB des try: Bricht BEGIN ab (z. B. weil
    // noch eine Transaktion offen ist), muss das finally trotzdem abhängen —
    // sonst bliebe import_backup dauerhaft gebunden und jeder weitere Import
    // scheiterte bis zum Serverneustart.
    try {
      db.exec(`ATTACH DATABASE '${String(backupPath).replace(/'/g, "''")}' AS import_backup`);
      db.exec('BEGIN');
      IMPORT_TABLES.forEach(t => db.exec('DELETE FROM ' + t));
      for(const t of IMPORT_TABLES){
        const liveCols = dbTableColumns(db, t);
        const backCols = dbTableColumns(backupConn, t);
        // Nur Spalten übertragen, die BEIDE Schemas enthalten (alte Backups
        // ohne updated_at bleiben so importierbar).
        const common = liveCols.filter(c => backCols.includes(c));
        // Anführungszeichen um jede Spalte: `projects` enthält die
        // SQL-Schlüsselwort-Spalte "group", die sonst einen Syntaxfehler wirft.
        const quoted = common.map(c => '"' + c + '"').join(', ');
        const marks = common.map(() => '?').join(', ');
        const rows = backupConn.prepare('SELECT ' + quoted + ' FROM ' + t).all();
        const ins = db.prepare('INSERT INTO ' + t + ' (' + quoted + ') VALUES (' + marks + ')');
        rows.forEach(r => ins.run(...common.map(c => r[c])));
      }
      db.exec('COMMIT');
    } catch(err){
      // Beide Aufräumschritte dürfen den ursprünglichen Fehler nicht
      // verdecken: Scheiterte schon ATTACH, gibt es weder Transaktion noch
      // angehängte Datenbank, und ROLLBACK/DETACH würden ihrerseits werfen.
      try { db.exec('ROLLBACK'); } catch(e){ /* keine offene Transaktion */ }
      throw err;
    } finally {
      try { db.exec('DETACH DATABASE import_backup'); } catch(e){ /* nie angehängt */ }
    }
  } finally {
    backupConn.close();
  }
}

// ---------- Migration: altes Ein-Dokument-Format -> relationales Schema ----------

// migrateLegacySchema: überführt eine bestehende Datenbank aus dem alten Format
// (eine `weeks`-Tabelle mit JSON-`data`-Spalte sowie `people` mit JSON-`data`)
// in das relationale Schema. Wird einmalig beim Start ausgeführt.
function migrateLegacySchema(){
  if(!hasLegacyWeeksTable()) return;
  console.log('Migriere altes Wochen-Dokument-Format in relationales Schema …');

  // Alte Wochen-Dokumente lesen
  const legacyWeeks = db.prepare('SELECT department, label, data FROM weeks').all();
  // Alte globale Personen-Dokumente lesen (eine JSON-Array-Zeile pro Abteilung)
  const legacyPeopleRows = [];
  const hasLegacyPeople = db.prepare("SELECT COUNT(*) AS c FROM sqlite_master WHERE type='table' AND name='people' AND sql LIKE '%data%'").get().c > 0;
  if(hasLegacyPeople){
    legacyPeopleRows.push(...db.prepare('SELECT department, data FROM people').all());
  }
  const tx = db.exec('BEGIN');
  try {
    // Alte Tabellen entfernen (Reihenfolge: erst Kind-Tabellen, dann Stamm)
    db.exec(`DROP TABLE IF EXISTS week_people_roles; DROP TABLE IF EXISTS week_people;
      DROP TABLE IF EXISTS attendance; DROP TABLE IF EXISTS project_lph;
      DROP TABLE IF EXISTS project_assign; DROP TABLE IF EXISTS project_aufwand;
      DROP TABLE IF EXISTS project_aktuelle_lp; DROP TABLE IF EXISTS project_plan;
      DROP TABLE IF EXISTS plan_people; DROP TABLE IF EXISTS plan_todos;
      DROP TABLE IF EXISTS plan_todo_assignees; DROP TABLE IF EXISTS plan_todo_deps;
      DROP TABLE IF EXISTS plan_milestones; DROP TABLE IF EXISTS plan_milestone_deps;
      DROP TABLE IF EXISTS projects; DROP TABLE IF EXISTS weeks;
      DROP TABLE IF EXISTS people; DROP TABLE IF EXISTS person_roles; DROP TABLE IF EXISTS person_absences;`);

    // Neue Tabellen anlegen (Schema-DDL oben ausführen)
    db.exec(`CREATE TABLE IF NOT EXISTS weeks (
      department TEXT NOT NULL, label TEXT NOT NULL, week_id TEXT NOT NULL DEFAULT '',
      date TEXT NOT NULL DEFAULT '', locked INTEGER NOT NULL DEFAULT 0,
      updated_at TEXT NOT NULL DEFAULT '',
      PRIMARY KEY (department, label)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS week_people (
      department TEXT NOT NULL, week_label TEXT NOT NULL, person_id TEXT NOT NULL,
      name TEXT NOT NULL DEFAULT '', full_name TEXT NOT NULL DEFAULT '',
      array_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, person_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS week_people_roles (
      department TEXT NOT NULL, week_label TEXT NOT NULL, person_id TEXT NOT NULL,
      role TEXT NOT NULL, role_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, person_id, role)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS attendance (
      department TEXT NOT NULL, week_label TEXT NOT NULL, person_id TEXT NOT NULL,
      present INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, person_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS projects (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      nr TEXT NOT NULL DEFAULT '', name TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT '',
      "group" TEXT NOT NULL DEFAULT '', array_order INTEGER NOT NULL DEFAULT 0,
      order_value INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (department, week_label, project_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS project_lph (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      lph TEXT NOT NULL, value TEXT NOT NULL DEFAULT '',
      PRIMARY KEY (department, week_label, project_id, lph)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS project_assign (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      person_id TEXT NOT NULL, role TEXT NOT NULL DEFAULT '', state INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (department, week_label, project_id, person_id, role)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS project_aufwand (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      lph TEXT NOT NULL, role TEXT NOT NULL DEFAULT '', value REAL NOT NULL DEFAULT 0,
      PRIMARY KEY (department, week_label, project_id, lph, role)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS project_aktuelle_lp (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      lph TEXT NOT NULL, PRIMARY KEY (department, week_label, project_id, lph)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS project_plan (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      start TEXT NOT NULL DEFAULT '', end TEXT NOT NULL DEFAULT '', progress INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (department, week_label, project_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS plan_people (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      person_id TEXT NOT NULL, array_order INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (department, week_label, project_id, person_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS plan_todos (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      todo_id TEXT NOT NULL, title TEXT NOT NULL DEFAULT '', start TEXT NOT NULL DEFAULT '',
      end TEXT NOT NULL DEFAULT '', done INTEGER NOT NULL DEFAULT 0, progress INTEGER NOT NULL DEFAULT 0,
      sort_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, week_label, project_id, todo_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_assignees (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      todo_id TEXT NOT NULL, person_id TEXT NOT NULL,
      PRIMARY KEY (department, week_label, project_id, todo_id, person_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS plan_todo_deps (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      todo_id TEXT NOT NULL, depends_on_id TEXT NOT NULL,
      PRIMARY KEY (department, week_label, project_id, todo_id, depends_on_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS plan_milestones (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      milestone_id TEXT NOT NULL, name TEXT NOT NULL DEFAULT '', date TEXT NOT NULL DEFAULT '',
      position TEXT NOT NULL DEFAULT '', sort_order INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (department, week_label, project_id, milestone_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS plan_milestone_deps (
      department TEXT NOT NULL, week_label TEXT NOT NULL, project_id TEXT NOT NULL,
      milestone_id TEXT NOT NULL, depends_on_id TEXT NOT NULL,
      PRIMARY KEY (department, week_label, project_id, milestone_id, depends_on_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS people (
      department TEXT NOT NULL, person_id TEXT NOT NULL, name TEXT NOT NULL DEFAULT '',
      full_name TEXT NOT NULL DEFAULT '', email TEXT NOT NULL DEFAULT '',
      sort_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, person_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS person_roles (
      department TEXT NOT NULL, person_id TEXT NOT NULL, role TEXT NOT NULL,
      role_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (department, person_id, role)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS person_absences (
      department TEXT NOT NULL, person_id TEXT NOT NULL, absence TEXT NOT NULL,
      PRIMARY KEY (department, person_id, absence)
    )`);

    // Alte Dokumente einlesen und relational speichern. Die Tabellen wurden
    // gerade neu angelegt und sind leer; `serializeWeek` genügt (kein eigener
    // Transaktions-Wrapper, da die äußere BEGIN/COMMIT der Migration aktiv ist).
    legacyWeeks.forEach(row => {
      let week;
      try { week = JSON.parse(row.data); } catch(e){ console.error('Kaputte Woche bei Migration:', row.label, e.message); return; }
      if(!week || !week.label) return;
      serializeWeek(week);
    });
    // Globale Personen aus dem alten Dokument-Format migrieren. serializeGlobalPeople
    // startet selbst eine Transaktion, daher werden die Zeilen hier direkt in die
    // neuen Tabellen eingefügt (serializeGlobalPeople ist in Transaktionen nicht
    // aufrufbar, weil BEGIN innerhalb BEGIN nicht erlaubt ist).
    legacyPeopleRows.forEach(row => {
      let people;
      try { people = JSON.parse(row.data); } catch(e){ console.error('Kaputte Personen-Liste bei Migration:', row.department, e.message); return; }
      if(!Array.isArray(people)) return;
      const insPerson = db.prepare(`INSERT INTO people (department, person_id, name, full_name, email, sort_order)
        VALUES (?, ?, ?, ?, ?, ?)`);
      const insRole = db.prepare(`INSERT OR IGNORE INTO person_roles (department, person_id, role, role_order) VALUES (?, ?, ?, ?)`);
      const insAbsence = db.prepare(`INSERT OR IGNORE INTO person_absences (department, person_id, absence) VALUES (?, ?, ?)`);
      people.forEach((p, idx) => {
        insPerson.run(row.department, String(p.id || ''), String(p.name || ''), String(p.fullName || ''), String(p.email || ''), idx);
        (Array.isArray(p.role) ? p.role : []).forEach((r, ridx) => insRole.run(row.department, String(p.id || ''), String(r || ''), ridx));
        (Array.isArray(p.absences) ? p.absences : []).forEach(a => insAbsence.run(row.department, String(p.id || ''), String(a || '')));
      });
    });
    db.exec('COMMIT');
    console.log('Migration abgeschlossen (' + legacyWeeks.length + ' Wochen).');
  } catch(err){
    db.exec('ROLLBACK');
    console.error('Migration fehlgeschlagen:', err.message);
    throw err;
  }
}

// migrateWeeksUpdatedAt: ergänzt die `updated_at`-Spalte in bestehenden
// relationalen Datenbanken (idempotent; neue DBs haben die Spalte bereits).
function migrateWeeksUpdatedAt(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('weeks')").all().map(r => r.name);
  if(!cols.includes('updated_at')){
    db.exec("ALTER TABLE weeks ADD COLUMN updated_at TEXT NOT NULL DEFAULT ''");
    console.log('Wochen-Tabelle um updated_at ergänzt.');
  }
}

// migrateRemoveDepartmentPartition: löst die bisherige Abteilungs-
// Partitionierung (ELT/HLS/ARCH als getrennte Wochen-/Projekt-Reihen) auf.
// Idempotent (Guard: `weeks` hat noch eine `department`-Spalte). Zweistufig:
// (1) Wochen mit gleichem Label aus verschiedenen Ex-Abteilungen werden zu
//     einer globalen Woche zusammengeführt (week_people/-roles/attendance
//     ebenfalls vereinigt).
// (2) Innerhalb jeder Woche werden Projekte mit gleicher, nicht-leerer `nr`
//     aus verschiedenen Ex-Abteilungen zu EINEM Projekt verschmolzen
//     (bestätigt vom Nutzer: es handelt sich um dieselben realen Bauprojekte,
//     die bisher getrennt je Gewerk geführt wurden) — inkl. Team, Leistungs-
//     phasen, Gantt/TODOs. Jede beteiligte Ex-Abteilung wird zum Tag
//     (project_tags). Konflikte (abweichende Namen/Werte) werden mit
//     deterministischer Präzedenz ELT→HLS→ARCH aufgelöst und vollständig
//     protokolliert, niemals still verworfen.
function migrateRemoveDepartmentPartition(){
  const weeksCols = db.prepare("SELECT name FROM pragma_table_info('weeks')").all().map(r => r.name);
  if(!weeksCols.includes('department')) return; // bereits migriert oder frische DB

  console.log('Migriere: Abteilungs-Partitionierung wird entfernt (Wochen und gleichnummerige Projekte werden zusammengeführt) …');

  // Zusätzlicher, eigens benannter Sicherheits-Schnappschuss VOR jeder
  // Änderung (zusätzlich zum regulären Auto-Backup, das erst NACH dem
  // Serverstart läuft) — falls je eine manuelle Wiederherstellung nötig wird.
  try {
    if(!fs.existsSync(BACKUP_DIR)) fs.mkdirSync(BACKUP_DIR, { recursive: true });
    const snapshotPath = path.join(BACKUP_DIR, 'pre-department-migration-' + new Date().toISOString().replace(/[:.]/g, '-') + '.db');
    createDbSnapshot(snapshotPath);
    console.log('Vor-Migrations-Snapshot gespeichert:', snapshotPath);
  } catch(err){
    console.error('Vor-Migrations-Snapshot fehlgeschlagen (Migration wird trotzdem fortgesetzt):', err.message);
  }

  const DEPT_ORDER = ['ELT', 'HLS', 'ARCH'];
  const deptRank = (d) => { const i = DEPT_ORDER.indexOf(d); return i === -1 ? DEPT_ORDER.length : i; };
  const conflicts = [];

  db.exec('BEGIN');
  try {
    // ---------- Stufe 1: Wochen zusammenführen (nach label) ----------
    db.exec(`CREATE TABLE weeks_new (
      label TEXT NOT NULL, week_id TEXT NOT NULL DEFAULT '', date TEXT NOT NULL DEFAULT '',
      locked INTEGER NOT NULL DEFAULT 0, updated_at TEXT NOT NULL DEFAULT '', PRIMARY KEY (label)
    )`);
    const labels = db.prepare('SELECT DISTINCT label FROM weeks').all().map(r => r.label);
    const insWeekNew = db.prepare('INSERT INTO weeks_new (label, week_id, date, locked, updated_at) VALUES (?,?,?,?,?)');
    labels.forEach(label => {
      const rows = db.prepare('SELECT * FROM weeks WHERE label = ?').all(label)
        .sort((a, b) => deptRank(a.department) - deptRank(b.department));
      if(rows.length > 1){
        conflicts.push(`Woche "${label}": ${rows.length} Abteilungs-Kopien zusammengeführt (${rows.map(r => r.department + '=' + r.date).join(', ')}).`);
      }
      const canonical = rows[0];
      const maxUpdatedAt = rows.map(r => r.updated_at || '').sort().slice(-1)[0] || '';
      insWeekNew.run(label, canonical.week_id, canonical.date, canonical.locked, maxUpdatedAt);
    });
    db.exec('DROP TABLE weeks');
    db.exec('ALTER TABLE weeks_new RENAME TO weeks');

    // week_people: bei gleicher (week_label,person_id) aus mehreren
    // Ex-Abteilungen gewinnt die Präzedenz-Reihenfolge.
    db.exec(`CREATE TABLE week_people_new (
      week_label TEXT NOT NULL, person_id TEXT NOT NULL, name TEXT NOT NULL DEFAULT '',
      full_name TEXT NOT NULL DEFAULT '', array_order INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, person_id)
    )`);
    {
      const wpInsert = db.prepare('INSERT INTO week_people_new (week_label,person_id,name,full_name,array_order) VALUES (?,?,?,?,?)');
      const seen = new Set();
      db.prepare('SELECT * FROM week_people').all()
        .sort((a, b) => deptRank(a.department) - deptRank(b.department) || a.array_order - b.array_order)
        .forEach(r => {
          const key = r.week_label + '|' + r.person_id;
          if(seen.has(key)){
            conflicts.push(`week_people ${r.week_label}/${r.person_id}: Duplikat aus ${r.department} verworfen (Erst-Eintrag gewinnt).`);
            return;
          }
          seen.add(key);
          wpInsert.run(r.week_label, r.person_id, r.name, r.full_name, r.array_order);
        });
    }
    db.exec('DROP TABLE week_people');
    db.exec('ALTER TABLE week_people_new RENAME TO week_people');

    // week_people_roles: reine Mengenvereinigung, keine Konflikte möglich
    // (gleiche Rolle mehrfach ist harmlos, INSERT OR IGNORE übernimmt das).
    db.exec(`CREATE TABLE week_people_roles_new (
      week_label TEXT NOT NULL, person_id TEXT NOT NULL, role TEXT NOT NULL,
      role_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (week_label, person_id, role)
    )`);
    db.exec(`INSERT OR IGNORE INTO week_people_roles_new (week_label,person_id,role,role_order)
      SELECT week_label,person_id,role,role_order FROM week_people_roles`);
    db.exec('DROP TABLE week_people_roles');
    db.exec('ALTER TABLE week_people_roles_new RENAME TO week_people_roles');

    // attendance: bei widersprüchlicher Anwesenheit gewinnt die Präzedenz.
    db.exec(`CREATE TABLE attendance_new (
      week_label TEXT NOT NULL, person_id TEXT NOT NULL, present INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, person_id)
    )`);
    {
      const attInsert = db.prepare('INSERT INTO attendance_new (week_label,person_id,present) VALUES (?,?,?)');
      const seen = new Map();
      db.prepare('SELECT * FROM attendance').all()
        .sort((a, b) => deptRank(a.department) - deptRank(b.department))
        .forEach(r => {
          const key = r.week_label + '|' + r.person_id;
          if(seen.has(key)){
            if(seen.get(key) !== r.present){
              conflicts.push(`attendance ${r.week_label}/${r.person_id}: widersprüchliche Anwesenheit (${r.department}=${r.present}) verworfen, Erst-Eintrag gewinnt.`);
            }
            return;
          }
          seen.set(key, r.present);
          attInsert.run(r.week_label, r.person_id, r.present);
        });
    }
    db.exec('DROP TABLE attendance');
    db.exec('ALTER TABLE attendance_new RENAME TO attendance');

    // ---------- Stufe 2: Projekte je Woche nach `nr` zusammenführen ----------
    db.exec(`CREATE TABLE projects_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, nr TEXT NOT NULL DEFAULT '',
      name TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT '', "group" TEXT NOT NULL DEFAULT '',
      array_order INTEGER NOT NULL DEFAULT 0, order_value INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, project_id)
    )`);
    db.exec(`CREATE TABLE IF NOT EXISTS project_tags (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, tag TEXT NOT NULL,
      tag_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (week_label, project_id, tag)
    )`);

    // Gruppierung: gleiche (week_label,nr) mit nicht-leerer nr gehören
    // zusammen; jede Zeile mit leerer nr bildet ihre eigene Einzel-Gruppe
    // (nichts zum Abgleichen vorhanden, bleibt eigenständig).
    const groups = new Map();
    let soloCounter = 0;
    db.prepare('SELECT * FROM projects').all().forEach(r => {
      const groupKey = (r.nr && r.nr.trim() !== '')
        ? r.week_label + '|nr:' + r.nr
        : r.week_label + '|solo:' + (soloCounter++);
      if(!groups.has(groupKey)) groups.set(groupKey, []);
      groups.get(groupKey).push(r);
    });

    const insProjectNew = db.prepare(`INSERT INTO projects_new (week_label,project_id,nr,name,status,"group",array_order,order_value)
      VALUES (?,?,?,?,?,?,?,?)`);
    const insTagNew = db.prepare('INSERT OR IGNORE INTO project_tags (week_label,project_id,tag,tag_order) VALUES (?,?,?,?)');
    // projectIdMap: alter Schlüssel "department|week_label|project_id" -> neue
    // (überlebende) project_id innerhalb derselben week_label.
    const projectIdMap = new Map();

    groups.forEach(rawRows => {
      const rows = rawRows.slice().sort((a, b) => deptRank(a.department) - deptRank(b.department));
      const survivor = rows[0];
      const weekLabel = survivor.week_label;
      const newProjectId = survivor.project_id;

      rows.forEach(r => projectIdMap.set(r.department + '|' + r.week_label + '|' + r.project_id, newProjectId));

      if(rows.length > 1){
        if(new Set(rows.map(r => r.name)).size > 1){
          conflicts.push(`Projekt ${survivor.nr} (${weekLabel}): unterschiedliche Namen zusammengeführt (${rows.map(r => r.department + '="' + r.name + '"').join(', ')}), "${survivor.name}" übernommen.`);
        }
        if(new Set(rows.map(r => r.group)).size > 1){
          conflicts.push(`Projekt ${survivor.nr} (${weekLabel}): unterschiedliche Gruppen (${rows.map(r => r.department + '="' + r.group + '"').join(', ')}), "${survivor.group}" übernommen.`);
        }
      }
      // Status (freier Text): alle nicht-leeren Texte mit Abteilungs-Präfix
      // zusammenführen statt einen zu verwerfen — kein Informationsverlust.
      // Identischer Text aus mehreren Abteilungen (z. B. eine gemeinsame
      // Besprechungsnotiz) wird dedupliziert statt wortgleich doppelt gezeigt.
      const statusByText = new Map();
      rows.forEach(r => {
        const t = (r.status || '').trim();
        if(!t) return;
        if(!statusByText.has(t)) statusByText.set(t, []);
        statusByText.get(t).push(r.department);
      });
      const mergedStatus = Array.from(statusByText.entries())
        .map(([text, depts]) => `[${depts.join('+')}] ${text}`)
        .join(' | ');

      insProjectNew.run(weekLabel, newProjectId, survivor.nr, survivor.name, mergedStatus, survivor.group, survivor.array_order, survivor.order_value);

      Array.from(new Set(rows.map(r => r.department))).forEach((d, idx) => insTagNew.run(weekLabel, newProjectId, d, idx));
    });
    db.exec('DROP TABLE projects');
    db.exec('ALTER TABLE projects_new RENAME TO projects');

    // mapProjectId: löst für eine alte (department,week_label,project_id) die
    // neue (verschmolzene) project_id auf. Fällt auf die alte ID zurück, falls
    // aus irgendeinem Grund kein Mapping existiert (sollte nie vorkommen, da
    // JEDE alte projects-Zeile oben ein Mapping bekommen hat).
    function mapProjectId(department, weekLabel, projectId){
      return projectIdMap.get(department + '|' + weekLabel + '|' + projectId) || projectId;
    }

    // project_lph: Vereinigung je (week_label,neue_project_id,lph); bei
    // widersprüchlichen Werten gewinnt das Maximum (weiter fortgeschrittene
    // Abteilung bestimmt den gemeinsamen Fortschritt dieser Leistungsphase).
    db.exec(`CREATE TABLE project_lph_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, lph TEXT NOT NULL,
      value TEXT NOT NULL DEFAULT '', PRIMARY KEY (week_label, project_id, lph)
    )`);
    {
      const merged = new Map();
      db.prepare('SELECT * FROM project_lph').all()
        .sort((a, b) => deptRank(a.department) - deptRank(b.department))
        .forEach(r => {
          const newPid = mapProjectId(r.department, r.week_label, r.project_id);
          const key = r.week_label + '|' + newPid + '|' + r.lph;
          const existing = merged.get(key);
          if(existing){
            if(existing.value !== r.value){
              const a = Number(existing.value), b = Number(r.value);
              const winner = (!isNaN(a) && !isNaN(b)) ? (b > a ? r.value : existing.value) : existing.value;
              conflicts.push(`project_lph ${r.week_label}/${newPid}/LP${r.lph}: unterschiedliche Werte (${existing.value} vs ${r.value}) — Maximum "${winner}" übernommen.`);
              existing.value = winner;
            }
            return;
          }
          merged.set(key, { weekLabel: r.week_label, projectId: newPid, lph: r.lph, value: r.value });
        });
      const ins = db.prepare('INSERT INTO project_lph_new (week_label,project_id,lph,value) VALUES (?,?,?,?)');
      merged.forEach(v => ins.run(v.weekLabel, v.projectId, v.lph, v.value));
    }
    db.exec('DROP TABLE project_lph');
    db.exec('ALTER TABLE project_lph_new RENAME TO project_lph');

    // project_assign: Vereinigung je (week_label,neue_project_id,person_id,role);
    // bei widersprüchlichem Status (0/1/2) gewinnt das Maximum.
    db.exec(`CREATE TABLE project_assign_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, person_id TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT '', state INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, project_id, person_id, role)
    )`);
    {
      const merged = new Map();
      db.prepare('SELECT * FROM project_assign').all()
        .sort((a, b) => deptRank(a.department) - deptRank(b.department))
        .forEach(r => {
          const newPid = mapProjectId(r.department, r.week_label, r.project_id);
          const key = r.week_label + '|' + newPid + '|' + r.person_id + '|' + r.role;
          const existing = merged.get(key);
          if(existing){
            if(existing.state !== r.state){
              const winner = Math.max(existing.state, r.state);
              conflicts.push(`project_assign ${r.week_label}/${newPid}/${r.person_id}|${r.role}: unterschiedlicher Status (${existing.state} vs ${r.state}) — Maximum ${winner} übernommen.`);
              existing.state = winner;
            }
            return;
          }
          merged.set(key, { weekLabel: r.week_label, projectId: newPid, personId: r.person_id, role: r.role, state: r.state });
        });
      const ins = db.prepare('INSERT INTO project_assign_new (week_label,project_id,person_id,role,state) VALUES (?,?,?,?,?)');
      merged.forEach(v => ins.run(v.weekLabel, v.projectId, v.personId, v.role, v.state));
    }
    db.exec('DROP TABLE project_assign');
    db.exec('ALTER TABLE project_assign_new RENAME TO project_assign');

    // project_aufwand: Vereinigung je (week_label,neue_project_id,lph,role);
    // bei Widerspruch gewinnt die Präzedenz (Gewichtungsfaktoren sind eher
    // Konfiguration als Fortschritt, kein "Maximum" sinnvoll).
    db.exec(`CREATE TABLE project_aufwand_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, lph TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT '', value REAL NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, project_id, lph, role)
    )`);
    {
      const merged = new Map();
      db.prepare('SELECT * FROM project_aufwand').all()
        .sort((a, b) => deptRank(a.department) - deptRank(b.department))
        .forEach(r => {
          const newPid = mapProjectId(r.department, r.week_label, r.project_id);
          const key = r.week_label + '|' + newPid + '|' + r.lph + '|' + r.role;
          const existing = merged.get(key);
          if(existing){
            if(existing.value !== r.value){
              conflicts.push(`project_aufwand ${r.week_label}/${newPid}/LP${r.lph}/${r.role}: unterschiedliche Werte (${existing.value} vs ${r.value}) — erster Wert (${existing.value}) übernommen.`);
            }
            return;
          }
          merged.set(key, { weekLabel: r.week_label, projectId: newPid, lph: r.lph, role: r.role, value: r.value });
        });
      const ins = db.prepare('INSERT INTO project_aufwand_new (week_label,project_id,lph,role,value) VALUES (?,?,?,?,?)');
      merged.forEach(v => ins.run(v.weekLabel, v.projectId, v.lph, v.role, v.value));
    }
    db.exec('DROP TABLE project_aufwand');
    db.exec('ALTER TABLE project_aufwand_new RENAME TO project_aufwand');

    // project_aktuelle_lp: reine Mengenvereinigung (ein Projekt kann jetzt
    // mehrere gleichzeitig aktive Leistungsphasen zeigen — gewollt).
    db.exec(`CREATE TABLE project_aktuelle_lp_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, lph TEXT NOT NULL,
      PRIMARY KEY (week_label, project_id, lph)
    )`);
    {
      const ins = db.prepare('INSERT OR IGNORE INTO project_aktuelle_lp_new (week_label,project_id,lph) VALUES (?,?,?)');
      db.prepare('SELECT * FROM project_aktuelle_lp').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.lph);
      });
    }
    db.exec('DROP TABLE project_aktuelle_lp');
    db.exec('ALTER TABLE project_aktuelle_lp_new RENAME TO project_aktuelle_lp');

    // project_plan (Gantt-Rahmen je Projekt): start = frühester, end =
    // spätester Termin (gemeinsame Zeitspanne beider Arbeitsanteile);
    // progress-Widerspruch -> Maximum.
    db.exec(`CREATE TABLE project_plan_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, start TEXT NOT NULL DEFAULT '',
      end TEXT NOT NULL DEFAULT '', progress INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, project_id)
    )`);
    {
      const merged = new Map();
      db.prepare('SELECT * FROM project_plan').all()
        .sort((a, b) => deptRank(a.department) - deptRank(b.department))
        .forEach(r => {
          const newPid = mapProjectId(r.department, r.week_label, r.project_id);
          const key = r.week_label + '|' + newPid;
          const existing = merged.get(key);
          if(existing){
            if(r.start && (!existing.start || r.start < existing.start)) existing.start = r.start;
            if(r.end && (!existing.end || r.end > existing.end)) existing.end = r.end;
            if(existing.progress !== r.progress){
              const winner = Math.max(existing.progress, r.progress);
              conflicts.push(`project_plan ${r.week_label}/${newPid}: unterschiedlicher Fortschritt (${existing.progress}% vs ${r.progress}%) — Maximum ${winner}% übernommen.`);
              existing.progress = winner;
            }
            return;
          }
          merged.set(key, { weekLabel: r.week_label, projectId: newPid, start: r.start, end: r.end, progress: r.progress });
        });
      const ins = db.prepare('INSERT INTO project_plan_new (week_label,project_id,start,end,progress) VALUES (?,?,?,?,?)');
      merged.forEach(v => ins.run(v.weekLabel, v.projectId, v.start, v.end, v.progress));
    }
    db.exec('DROP TABLE project_plan');
    db.exec('ALTER TABLE project_plan_new RENAME TO project_plan');

    // plan_people: reine Mengenvereinigung.
    db.exec(`CREATE TABLE plan_people_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, person_id TEXT NOT NULL,
      array_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (week_label, project_id, person_id)
    )`);
    {
      const ins = db.prepare('INSERT OR IGNORE INTO plan_people_new (week_label,project_id,person_id,array_order) VALUES (?,?,?,?)');
      db.prepare('SELECT * FROM plan_people ORDER BY array_order').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.person_id, r.array_order);
      });
    }
    db.exec('DROP TABLE plan_people');
    db.exec('ALTER TABLE plan_people_new RENAME TO plan_people');

    // plan_todos / plan_todo_assignees / plan_todo_deps / plan_milestones /
    // plan_milestone_deps: todo_id/milestone_id sind zufällige uid()-Strings,
    // daher praktisch kein Kollisionsrisiko — reines Umhängen auf die neue
    // project_id (das Gantt/TODO des vereinten Projekts zeigt danach schlicht
    // die Aufgaben aller beteiligten Abteilungen zusammen).
    db.exec(`CREATE TABLE plan_todos_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, todo_id TEXT NOT NULL,
      title TEXT NOT NULL DEFAULT '', start TEXT NOT NULL DEFAULT '', end TEXT NOT NULL DEFAULT '',
      done INTEGER NOT NULL DEFAULT 0, progress INTEGER NOT NULL DEFAULT 0, sort_order INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (week_label, project_id, todo_id)
    )`);
    {
      const ins = db.prepare(`INSERT OR IGNORE INTO plan_todos_new (week_label,project_id,todo_id,title,start,end,done,progress,sort_order) VALUES (?,?,?,?,?,?,?,?,?)`);
      db.prepare('SELECT * FROM plan_todos ORDER BY sort_order').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.todo_id, r.title, r.start, r.end, r.done, r.progress, r.sort_order);
      });
    }
    db.exec('DROP TABLE plan_todos');
    db.exec('ALTER TABLE plan_todos_new RENAME TO plan_todos');

    db.exec(`CREATE TABLE plan_todo_assignees_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, todo_id TEXT NOT NULL, person_id TEXT NOT NULL,
      PRIMARY KEY (week_label, project_id, todo_id, person_id)
    )`);
    {
      const ins = db.prepare('INSERT OR IGNORE INTO plan_todo_assignees_new (week_label,project_id,todo_id,person_id) VALUES (?,?,?,?)');
      db.prepare('SELECT * FROM plan_todo_assignees').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.todo_id, r.person_id);
      });
    }
    db.exec('DROP TABLE plan_todo_assignees');
    db.exec('ALTER TABLE plan_todo_assignees_new RENAME TO plan_todo_assignees');

    db.exec(`CREATE TABLE plan_todo_deps_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, todo_id TEXT NOT NULL, depends_on_id TEXT NOT NULL,
      PRIMARY KEY (week_label, project_id, todo_id, depends_on_id)
    )`);
    {
      const ins = db.prepare('INSERT OR IGNORE INTO plan_todo_deps_new (week_label,project_id,todo_id,depends_on_id) VALUES (?,?,?,?)');
      db.prepare('SELECT * FROM plan_todo_deps').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.todo_id, r.depends_on_id);
      });
    }
    db.exec('DROP TABLE plan_todo_deps');
    db.exec('ALTER TABLE plan_todo_deps_new RENAME TO plan_todo_deps');

    db.exec(`CREATE TABLE plan_milestones_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, milestone_id TEXT NOT NULL,
      name TEXT NOT NULL DEFAULT '', date TEXT NOT NULL DEFAULT '', position TEXT NOT NULL DEFAULT '',
      sort_order INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (week_label, project_id, milestone_id)
    )`);
    {
      const ins = db.prepare(`INSERT OR IGNORE INTO plan_milestones_new (week_label,project_id,milestone_id,name,date,position,sort_order) VALUES (?,?,?,?,?,?,?)`);
      db.prepare('SELECT * FROM plan_milestones ORDER BY sort_order').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.milestone_id, r.name, r.date, r.position, r.sort_order);
      });
    }
    db.exec('DROP TABLE plan_milestones');
    db.exec('ALTER TABLE plan_milestones_new RENAME TO plan_milestones');

    db.exec(`CREATE TABLE plan_milestone_deps_new (
      week_label TEXT NOT NULL, project_id TEXT NOT NULL, milestone_id TEXT NOT NULL, depends_on_id TEXT NOT NULL,
      PRIMARY KEY (week_label, project_id, milestone_id, depends_on_id)
    )`);
    {
      const ins = db.prepare('INSERT OR IGNORE INTO plan_milestone_deps_new (week_label,project_id,milestone_id,depends_on_id) VALUES (?,?,?,?)');
      db.prepare('SELECT * FROM plan_milestone_deps').all().forEach(r => {
        ins.run(r.week_label, mapProjectId(r.department, r.week_label, r.project_id), r.milestone_id, r.depends_on_id);
      });
    }
    db.exec('DROP TABLE plan_milestone_deps');
    db.exec('ALTER TABLE plan_milestone_deps_new RENAME TO plan_milestone_deps');

    // project_files: department-Spalte entfernen (jetzt korrekt über
    // project_nr allein identifiziert, da gleichnummerige Projekte ab jetzt
    // als EIN Projekt gelten). Physische Dateien lagen bisher unter
    // sessions/uploads/<department>/<datei>, künftig flach unter
    // sessions/uploads/<datei> — best-effort verschoben (Tabelle ist aktuell
    // in der Praxis leer, dieser Pfad daher selten).
    const pfCols = db.prepare("SELECT name FROM pragma_table_info('project_files')").all().map(r => r.name);
    if(pfCols.includes('department')){
      db.exec(`CREATE TABLE project_files_new (
        id TEXT PRIMARY KEY, project_nr TEXT NOT NULL, project_name TEXT NOT NULL DEFAULT '',
        original_filename TEXT NOT NULL, stored_name TEXT NOT NULL,
        content_type TEXT NOT NULL DEFAULT 'application/octet-stream', size_bytes INTEGER NOT NULL DEFAULT 0,
        uploaded_by_user_id INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'pending',
        created_at TEXT NOT NULL DEFAULT '', updated_at TEXT NOT NULL DEFAULT ''
      )`);
      db.exec(`INSERT INTO project_files_new (id,project_nr,project_name,original_filename,stored_name,content_type,size_bytes,uploaded_by_user_id,status,created_at,updated_at)
        SELECT id,project_nr,project_name,original_filename,stored_name,content_type,size_bytes,uploaded_by_user_id,status,created_at,updated_at FROM project_files`);
      const oldFileRows = db.prepare('SELECT id, department, stored_name FROM project_files').all();
      db.exec('DROP TABLE project_files');
      db.exec('ALTER TABLE project_files_new RENAME TO project_files');
      db.exec('DROP INDEX IF EXISTS idx_project_files_scope');
      db.exec('CREATE INDEX idx_project_files_scope ON project_files(project_nr)');
      oldFileRows.forEach(r => {
        try {
          const oldPath = path.join(SESSIONS_DIR, 'uploads', r.department, r.stored_name);
          const newPath = path.join(SESSIONS_DIR, 'uploads', r.stored_name);
          if(fs.existsSync(oldPath) && !fs.existsSync(newPath)) fs.renameSync(oldPath, newPath);
        } catch(err){
          conflicts.push(`project_files ${r.id}: physische Datei konnte nicht verschoben werden (${err.message}).`);
        }
      });
    }

    db.exec('COMMIT');
    console.log(`Migration abgeschlossen. ${conflicts.length} Konflikt(e)/Hinweis(e) protokolliert:`);
    conflicts.forEach(c => console.log('  - ' + c));
  } catch(err){
    db.exec('ROLLBACK');
    console.error('Department-Migration fehlgeschlagen, Datenbank unverändert:', err);
    throw err;
  }
}

// migrateAddProjectUid: vergibt jeder projects-Zeile eine dauerhafte,
// wochenunabhängige project_uid (Basis für echte Projekt-Historie über
// mehrere Sitzungen hinweg, siehe "Projekt-Historie"-Architekturnotiz).
// Anders als project_id (wird bei jedem cloneWeek() neu vergeben, siehe
// js/weeks.js) und nr (informell stabil, aber änderbar/leer möglich) wird
// project_uid einmal bei echter Projekt-Entstehung erzeugt
// (saveNewProj()/demoProj()) und danach nie wieder verändert — cloneWeek()
// kopiert sie unverändert mit. Läuft NACH migrateRemoveDepartmentPartition()
// (setzt eine bereits globale, nicht mehr abteilungsgetrennte projects-
// Tabelle voraus). Idempotent (Guard: Spalte existiert bereits).
// migrateAddProjectBesprochen: rüstet die Spalte `besprochen` nach
// (idempotentes ALTER TABLE, gleiches Muster wie migrateUsersDepartment()).
// Bestandszeilen starten mit 0 — für vergangene Sitzungen ist der Haken
// ohnehin bedeutungslos.
function migrateAddProjectBesprochen(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(!cols.includes('besprochen')){
    db.exec('ALTER TABLE projects ADD COLUMN besprochen INTEGER NOT NULL DEFAULT 0');
    console.log('Projekt-Tabelle um besprochen ergänzt.');
  }
}

// migrateAddProjectGroesse: rüstet die Spalte `groesse` nach (idempotentes
// ALTER TABLE, gleiches Muster wie migrateAddProjectBesprochen()).
// Bestandszeilen starten mit 0 = keine Angabe; sie erscheinen in der
// Auslastung als nicht bewertbar, bis jemand entweder ein Honorar oder eine
// Größe pflegt. Absichtlich kein Vorgabewert wie 0,5: Eine erfundene Größe
// erzeugte Zahlen, die nach Messung aussehen.
// normalizeGroesse: Projektgröße auf den gültigen Bereich bringen.
// Erlaubt ist 0 bis 1 (0 = keine Angabe, 1 = Projekt in Höhe des
// Referenzhonorars). Unbrauchbare Werte werden zu 0 und damit zu einer
// sichtbaren Lücke, nicht zu einer erfundenen Zahl.
// Parameter: wert (beliebig). Rückgabe: Zahl zwischen 0 und 1.
function normalizeGroesse(wert){
  const n = Number(wert);
  if(!Number.isFinite(n) || n <= 0) return 0;
  return n > 1 ? 1 : n;
}

// migrateBackfillProjectGewerkeAusTags: setzt für Projekte OHNE jede
// project_gewerke-Zeile das Gewerk aus dem Schlagwort, das
// migrateRemoveDepartmentPartition() beim Zusammenführen der früheren
// Abteilungs-Sitzungen automatisch als project_tags-Eintrag vergeben hat
// (ELT/HLS/ARCH). Ohne diese Übernahme verschwände jedes Bestandsprojekt aus
// jeder Gewerke-gefilterten Ansicht des Wochenberichts (berichtProjekte()),
// weil project_gewerke — der Assistent "Neues Projekt" schreibt hierhin —
// für alle vor dem Assistenten angelegten Projekte leer ist. Idempotent:
// sobald ein Projekt mindestens eine project_gewerke-Zeile hat (egal woher,
// auch aus dieser Migration selbst), wird es bei künftigen Läufen
// übersprungen — eine spätere manuelle Korrektur in den Projekteinstellungen
// wird also nicht rückgängig gemacht.
function migrateBackfillProjectGewerkeAusTags(){
  const kandidaten = db.prepare(`
    SELECT DISTINCT pt.week_label, pt.project_id, pt.tag
    FROM project_tags pt
    WHERE pt.tag IN ('ELT','HLS','ARCH')
      AND NOT EXISTS (
        SELECT 1 FROM project_gewerke pg
        WHERE pg.week_label = pt.week_label AND pg.project_id = pt.project_id
      )
  `).all();
  if(!kandidaten.length) return;
  const ins = db.prepare('INSERT OR IGNORE INTO project_gewerke (week_label, project_id, gewerk) VALUES (?,?,?)');
  db.exec('BEGIN');
  try {
    kandidaten.forEach(r => ins.run(r.week_label, r.project_id, r.tag));
    db.exec('COMMIT');
    console.log('Gewerke aus Schlagwörtern übernommen:', kandidaten.length, 'Zuordnung(en).');
  } catch(err){
    db.exec('ROLLBACK');
    console.error('Gewerke-Backfill aus Schlagwörtern fehlgeschlagen:', err.message);
  }
}

function migrateAddProjectGroesse(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(!cols.includes('groesse')){
    db.exec('ALTER TABLE projects ADD COLUMN groesse REAL NOT NULL DEFAULT 0');
    console.log('Projekt-Tabelle um groesse ergänzt.');
  }
}

// migrateAddProjectUpdatedAt: rüstet die Spalte `updated_at` nach (idempotent,
// gleiches ALTER-TABLE-Muster wie migrateAddProjectGroesse()). Gebraucht von
// der Planung-Sandbox, siehe Spaltenkommentar am CREATE TABLE oben.
function migrateAddProjectUpdatedAt(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(!cols.includes('updated_at')){
    db.exec("ALTER TABLE projects ADD COLUMN updated_at TEXT NOT NULL DEFAULT ''");
    console.log('Projekt-Tabelle um updated_at ergänzt.');
  }
}

// migrateAddProtokollQuelle: rüstet die beiden Spalten nach, die ein
// importiertes Protokoll von einem manuell angelegten unterscheiden
// (idempotent, gleiches Muster wie migrateAddProjectGroesse()). Beide
// bleiben für ein manuell angelegtes Protokoll leer — siehe
// legeProtokollAn() und CLAUDE.md "### Protokolle".
function migrateAddProtokollQuelle(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('project_protokolle')").all().map(r => r.name);
  if(!cols.includes('quelle_dateiname')){
    db.exec("ALTER TABLE project_protokolle ADD COLUMN quelle_dateiname TEXT NOT NULL DEFAULT ''");
    db.exec("ALTER TABLE project_protokolle ADD COLUMN quelle_stored_name TEXT NOT NULL DEFAULT ''");
    console.log('Protokoll-Tabelle um Quelle-Spalten ergänzt.');
  }
}

// migrateAddProtokollPunktErstelltAm: rüstet das Datum nach, an dem ein
// Punkt zum ERSTEN Mal erfasst wurde (Erik's Wunsch) — bestehende Zeilen
// bleiben leer, da ihr echtes Entstehungsdatum nicht mehr rekonstruierbar
// ist (idempotent, gleiches Muster wie migrateAddProtokollQuelle()).
function migrateAddProtokollPunktErstelltAm(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('protokoll_punkte')").all().map(r => r.name);
  if(!cols.includes('erstellt_am')){
    db.exec("ALTER TABLE protokoll_punkte ADD COLUMN erstellt_am TEXT NOT NULL DEFAULT ''");
    console.log('Protokoll-Punkte-Tabelle um erstellt_am ergänzt.');
  }
}

// migrateAddProtokollPunktStatus: löst das bisherige boolesche `erledigt`
// in einen Status mit drei Werten auf (offen/erledigt/info, Eriks Wunsch
// nach einem dritten Zustand für rein informative Punkte ohne eigene
// Handlung). `erledigt` bleibt als Spalte bestehen und wird weiterhin
// synchron mitgeschrieben (reine Rückwärtskompatibilität, z. B. für
// IMPORT_TABLES-Restores älterer Backups) — maßgeblich für die Anwendung
// ist ab hier ausschließlich `status`. Bestehende Zeilen werden anhand
// ihres bisherigen `erledigt`-Werts befüllt, der ADD-COLUMN-Standardwert
// 'offen' allein würde erledigte Altpunkte fälschlich wieder öffnen.
function migrateAddProtokollPunktStatus(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('protokoll_punkte')").all().map(r => r.name);
  if(!cols.includes('status')){
    db.exec("ALTER TABLE protokoll_punkte ADD COLUMN status TEXT NOT NULL DEFAULT 'offen'");
    db.exec("UPDATE protokoll_punkte SET status = 'erledigt' WHERE erledigt = 1");
    console.log('Protokoll-Punkte-Tabelle um status ergänzt.');
  }
}

// ensureSandboxWeek: legt die reservierte `weeks`-Zeile für die Planung-
// Sandbox einmalig an (INSERT OR IGNORE — beim zweiten und jedem weiteren
// Start ist sie schon da). Kein echtes Datum, nie gesperrt. Alle Terminpläne
// der Planung hängen an diesem einen `week_label`, siehe SANDBOX_WEEK_LABEL.
function ensureSandboxWeek(){
  db.prepare(`INSERT OR IGNORE INTO weeks (label, week_id, date, locked, updated_at)
    VALUES (?, 'sandbox', '', 0, '')`).run(SANDBOX_WEEK_LABEL);
}

// getIsoWeekInfo: ISO-Kalenderwoche UND -Wochenjahr eines Datums (das
// Wochenjahr kann an Jahresgrenzen vom Kalenderjahr abweichen — Silvester
// kann in KW 1 des Folgejahres liegen). Portiert von getWeekNumber()
// (public/js/utils.js) — reines, browserunabhängiges JS, hier um das
// Wochenjahr erweitert, das für den Sitzungswechsel unten gebraucht wird.
// Parameter: date (Date). Rückgabe: {week, year}.
function getIsoWeekInfo(date){
  const d = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  const dayNum = d.getUTCDay() || 7;
  d.setUTCDate(d.getUTCDate() + 4 - dayNum);
  const year = d.getUTCFullYear();
  const yearStart = new Date(Date.UTC(year, 0, 1));
  const week = Math.ceil((((d - yearStart) / 86400000) + 1) / 7);
  return { week, year };
}

// formatWeekLabel: "KW <n zweistellig> / <JJJJ>" — das Format, das sowohl
// validateWeekLabel()s Regex (früher public/js/weeks.js) als auch
// weekSortKey()/weekSortKeyServer() erwarten.
function formatWeekLabel(week, year){
  return 'KW ' + String(week).padStart(2, '0') + ' / ' + year;
}

// ensureCurrentWeekRollover: legt automatisch eine neue Sitzung an, sobald
// der Kalender in eine neue ISO-Woche gewechselt hat — unabhängig davon, ob
// irgendjemand die Anwendung gerade nutzt (siehe CLAUDE.md, Abschnitt zum
// automatischen Sitzungswechsel). Ersetzt die früheren, manuellen Knöpfe
// "Neue Sitzung"/"Sitzung duplizieren" (public/js/weeks.js, entfernt).
// Repliziert exakt das Rücksetzverhalten des früheren cloneWeek(): neue
// week-/Projekt-ids, Kommentar/Verfasser/Zeitstempel/besprochen geleert,
// alles andere (Beteiligte, Zuordnungen, Terminpläne, Honorar, beauftragte
// Leistungsphasen, Gewerke, project_uid) bleibt unverändert erhalten. Sperrt
// anschließend — sofern app_settings.autoLockAktiv aktiv ist — alle anderen
// echten Sitzungen, dieselbe Invariante, die applyAutoLock() bisher nur
// clientseitig durchsetzte. Ohne jede bestehende echte Sitzung (frische
// Installation) wird stattdessen eine leere Sitzung angelegt (wie es früher
// newWeek() tat), damit ein frisches System nicht für immer ohne jede
// Sitzung dasteht. Fehler werden geloggt, nie geworfen — darf den
// Serverbetrieb nie stören. Keine Parameter, keine Rückgabe.
function ensureCurrentWeekRollover(){
  try {
    const echteWochen = loadWeeks().filter(w => w.label !== SANDBOX_WEEK_LABEL);
    const { week, year } = getIsoWeekInfo(new Date());
    const heuteKey = String(year) + '-' + String(week).padStart(2, '0');
    const neuesLabel = formatWeekLabel(week, year);

    let neueWoche;
    if(echteWochen.length === 0){
      // Frische Installation: keine Quelle zum Kopieren vorhanden.
      neueWoche = {
        id: crypto.randomUUID(), label: neuesLabel, date: heuteIsoLokal(),
        people: loadGlobalPeople(), projects: [], attendance: {}, locked: false
      };
    } else {
      const sortiert = echteWochen.slice()
        .sort((a, b) => weekSortKeyServer(a.label).localeCompare(weekSortKeyServer(b.label)));
      const letzte = sortiert[sortiert.length - 1];
      const letzteKey = weekSortKeyServer(letzte.label);
      if(heuteKey <= letzteKey) return; // schon auf dem Stand der aktuellen Kalenderwoche

      neueWoche = JSON.parse(JSON.stringify(letzte));
      neueWoche.id = crypto.randomUUID();
      neueWoche.label = neuesLabel;
      neueWoche.date = heuteIsoLokal();
      neueWoche.attendance = {};
      neueWoche.locked = false;
      delete neueWoche.updatedAt;
      (neueWoche.projects || []).forEach(p => {
        p.id = crypto.randomUUID();
        p.status = '';
        p.statusAuthor = '';
        p.statusAt = '';
        p.besprochen = false;
        delete p.statusState;
      });
    }

    saveWeekRelational(neueWoche);
    console.log('Automatischer Sitzungswechsel: neue Sitzung "' + neuesLabel + '" angelegt.');

    if(Number(readAppSettings().autoLockAktiv)){
      const nowIso = new Date().toISOString();
      db.prepare(`UPDATE weeks SET locked = 1, updated_at = ?
        WHERE label != ? AND label != ?`).run(nowIso, neuesLabel, SANDBOX_WEEK_LABEL);
    }
  } catch(err){
    console.error('Automatischer Sitzungswechsel fehlgeschlagen:', err);
  }
}

function migrateAddProjectUid(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(!cols.includes('project_uid')){
    db.exec("ALTER TABLE projects ADD COLUMN project_uid TEXT NOT NULL DEFAULT ''");
  } else {
    // Die Spalte allein genügt als Abschluss-Kennzeichen NICHT: Das ALTER
    // TABLE läuft außerhalb der Transaktion, das Nachfüllen darin. Brach das
    // Nachfüllen einmal ab (z. B. SQLITE_BUSY), existierte die Spalte, war
    // aber überall leer — und jeder weitere Start übersprang die Migration.
    // Die Projekt-Historie über Sitzungen hinweg wäre dauerhaft tot gewesen.
    const offen = db.prepare("SELECT COUNT(*) AS c FROM projects WHERE project_uid = ''").get();
    if(!offen || offen.c === 0) return; // wirklich fertig
    console.log('Migriere: project_uid wird nachgefüllt (' + offen.c + ' Zeile(n) ohne Kennung) …');
  }

  // Backfill für Bestandsdaten: Zeilen mit gleicher, nicht-leerer nr über
  // alle Wochen hinweg gelten als dasselbe reale Projekt und bekommen
  // dieselbe uid. Zeilen ohne nr können nicht zusammengeführt werden (kein
  // anderer stabiler Schlüssel vorhanden) und bekommen je eine eigene uid.
  // Bekannte Grenze dieses einmaligen Rückblicks: eine in der Vergangenheit
  // wiederverwendete nr (zwei echte Projekte) wird fälschlich zusammengeführt,
  // eine mitten in der Historie geänderte nr wird fälschlich getrennt — ab
  // dem Zeitpunkt dieser Migration tritt das Problem nicht mehr auf, da
  // project_uid danach nie wieder aus nr neu berechnet wird.
  const rows = db.prepare('SELECT week_label, project_id, nr, project_uid FROM projects').all();
  const uidByNr = new Map();
  // Bereits vergebene Kennungen als Vorgabe übernehmen: Beim Nachfüllen eines
  // Teilbestands (siehe Guard oben) dürfen vorhandene Kennungen NICHT neu
  // gewürfelt werden — sonst risse die Verbindung desselben Projekts über
  // mehrere Sitzungen hinweg ab.
  rows.forEach(r => {
    const key = (r.nr || '').trim();
    if(key && r.project_uid && !uidByNr.has(key)) uidByNr.set(key, r.project_uid);
  });
  const update = db.prepare('UPDATE projects SET project_uid = ? WHERE week_label = ? AND project_id = ?');

  db.exec('BEGIN');
  try {
    rows.forEach(r => {
      if(r.project_uid) return; // schon gesetzt, nicht anfassen
      const key = (r.nr || '').trim();
      let projectUid;
      if(key){
        if(!uidByNr.has(key)) uidByNr.set(key, crypto.randomUUID());
        projectUid = uidByNr.get(key);
      } else {
        projectUid = crypto.randomUUID();
      }
      update.run(projectUid, r.week_label, r.project_id);
    });
    db.exec('COMMIT');
  } catch(err){
    db.exec('ROLLBACK');
    console.error('project_uid-Migration fehlgeschlagen, Datenbank unverändert:', err);
    throw err;
  }
  console.log(`project_uid vergeben: ${rows.length} Zeile(n), ${uidByNr.size} eindeutige Projekt-Historie(n) über nr gruppiert.`);
}

// migrateAddProjectHonorar: ergänzt die Spalte `honorar` in der Tabelle
// `projects` (vereinbartes Netto-Honorar in Euro, Grundlage der
// Honorar-Terminplanung). Idempotent, gleiches Muster wie
// migrateWeeksUpdatedAt(). Bestandsprojekte bekommen 0 und gelten damit als
// "kein Honorar hinterlegt" — die Honorar-Maske verlangt dann eine Eingabe,
// bevor sie rechnet.
function migrateAddProjectHonorar(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(cols.includes('honorar')) return;
  db.exec('ALTER TABLE projects ADD COLUMN honorar REAL NOT NULL DEFAULT 0');
  console.log('Projekte-Tabelle um honorar ergänzt.');
}

// migrateAddProjectStatusAuthor: ergänzt die Spalten `status_author` und
// `status_at` in der Tabelle `projects` — Verfasser (Anzeigename zum
// Zeitpunkt des Schreibens) und Zeitstempel des Wochenkommentars. Bewusst
// der Anzeigename und nicht die user_id: der Kommentar ist ein historischer
// Eintrag, er soll auch nach dem Löschen oder Umbenennen eines Kontos noch
// aussagen, wer ihn damals verfasst hat. Idempotent, gleiches Muster wie
// migrateAddProjectHonorar(). Bestandskommentare bekommen leere Werte und
// werden in der Oberfläche als "unbekannt" ausgewiesen — der Verfasser ist
// rückwirkend nicht ermittelbar.
function migrateAddProjectStatusAuthor(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(cols.includes('status_author')) return;
  db.exec("ALTER TABLE projects ADD COLUMN status_author TEXT NOT NULL DEFAULT ''");
  db.exec("ALTER TABLE projects ADD COLUMN status_at TEXT NOT NULL DEFAULT ''");
  console.log('Projekt-Tabelle um status_author/status_at ergänzt.');
}

// migrateAddProjectAuftragsstatus: rüstet die Spalte `auftragsstatus` nach
// (idempotentes ALTER TABLE, gleiches Muster wie
// migrateAddProjectBesprochen()). Bestandszeilen bekommen über den
// Spalten-Default 'beauftragt' — der Altbestand ist beauftragte Arbeit, und
// jeder andere Anfangswert hätte laufende Projekte rückwirkend zu Akquise
// erklärt (siehe DEFAULT_AUFTRAGSSTATUS).
function migrateAddProjectAuftragsstatus(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('projects')").all().map(r => r.name);
  if(!cols.includes('auftragsstatus')){
    db.exec("ALTER TABLE projects ADD COLUMN auftragsstatus TEXT NOT NULL DEFAULT 'beauftragt'");
    console.log('Projekt-Tabelle um auftragsstatus ergänzt.');
  }
}

// migrateUsersDepartment: ergänzt die Spalten `department`/`initials` in
// bestehenden users-Tabellen (idempotent; neue DBs haben sie bereits).
function migrateUsersDepartment(){
  const cols = db.prepare("SELECT name FROM pragma_table_info('users')").all().map(r => r.name);
  if(!cols.includes('department')){
    db.exec("ALTER TABLE users ADD COLUMN department TEXT NOT NULL DEFAULT ''");
    console.log('Benutzer-Tabelle um department ergänzt.');
  }
  if(!cols.includes('initials')){
    db.exec("ALTER TABLE users ADD COLUMN initials TEXT NOT NULL DEFAULT ''");
    console.log('Benutzer-Tabelle um initials ergänzt.');
  }
  // capacity_hours: vertragliche Wochenstunden. Bis dahin kannte die
  // Auslastungsrechnung keine Teilzeit — sie maß jede Person an einer vollen
  // Stelle, eine halbe Stelle erschien deshalb dauerhaft unterbeschäftigt
  // (in js/load/load.js ausdrücklich als offener Punkt vermerkt). 0 bedeutet
  // "nicht gepflegt" und wird wie eine volle Stelle behandelt, damit
  // Bestandskonten ihr bisheriges Bild behalten.
  if(!cols.includes('capacity_hours')){
    db.exec('ALTER TABLE users ADD COLUMN capacity_hours REAL NOT NULL DEFAULT 0');
    console.log('Benutzer-Tabelle um capacity_hours ergänzt.');
  }
}

// dedupeUserPersonIds: person_id musste bisher nur je Abteilung eindeutig
// sein (alte people-Tabelle); jetzt identifiziert person_id global einen
// Benutzer, daher müssen Bestandskollisionen vor dem UNIQUE INDEX aufgelöst
// werden. Alle bis auf die erste Zeile je Kollision bekommen "_<id>" angehängt.
function dedupeUserPersonIds(){
  const dupes = db.prepare(`SELECT person_id, COUNT(*) c FROM users WHERE person_id != '' GROUP BY person_id HAVING c > 1`).all();
  dupes.forEach(d => {
    const rows = db.prepare('SELECT id FROM users WHERE person_id = ? ORDER BY id').all(d.person_id);
    rows.slice(1).forEach(r => {
      const newId = d.person_id + '_' + r.id;
      db.prepare('UPDATE users SET person_id = ? WHERE id = ?').run(newId, r.id);
      console.warn(`Person-ID-Kollision behoben: Benutzer #${r.id} bekommt "${newId}" statt "${d.person_id}" (bitte in der Benutzerverwaltung prüfen).`);
    });
  });
}

// ensureUserPersonIdUniqueIndex: löst zuerst Bestandskollisionen auf (siehe
// dedupeUserPersonIds) und legt danach den UNIQUE-Index an (idempotent,
// überspringt leere person_id-Werte per partiellem Index).
function ensureUserPersonIdUniqueIndex(){
  dedupeUserPersonIds();
  db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_person_id_unique ON users(person_id) WHERE person_id != ''`);
}

// migrateRetirePeopleTablesIntoUsers: löst das alte, abteilungsbezogene
// Projektbeteiligte-Schema (people/person_roles/person_absences) endgültig ab,
// nachdem es durch die globalen User-Accounts (users/user_roles/user_absences)
// ersetzt wurde. Idempotent: läuft nur, solange die Tabelle `people` noch
// existiert. person_absences wird bewusst NICHT übernommen (enthielt bereits
// vorher keine sinnvollen Daten, siehe serializeGlobalPeople-Altcode), und für
// historische people-Zeilen ohne passenden User wird KEIN Platzhalter-User
// angelegt (würde die Benutzerliste mit nicht-loginfähigen Karteileichen
// verschmutzen) — stattdessen wird einmalig auf der Konsole protokolliert.
function migrateRetirePeopleTablesIntoUsers(){
  const stillExists = db.prepare("SELECT COUNT(*) c FROM sqlite_master WHERE type='table' AND name='people'").get().c > 0;
  if(!stillExists) return;
  console.log('Migriere altes Projektbeteiligte-Schema (people/person_roles/person_absences) …');
  const legacyPeople = db.prepare('SELECT * FROM people').all();
  const legacyRoles = db.prepare('SELECT * FROM person_roles').all();
  let merged = 0; const unmatched = [];
  db.exec('BEGIN');
  try {
    legacyPeople.forEach(p => {
      const user = db.prepare('SELECT id FROM users WHERE department = ? AND person_id = ?').get(p.department, p.person_id);
      if(!user){ unmatched.push(p.department + '/' + p.person_id + ' (' + p.full_name + ')'); return; }
      legacyRoles.filter(r => r.department === p.department && r.person_id === p.person_id)
        .forEach(r => db.prepare('INSERT OR IGNORE INTO user_roles (user_id, role, role_order) VALUES (?,?,?)').run(user.id, r.role, r.role_order));
      merged++;
    });
    db.exec('DROP TABLE people; DROP TABLE person_roles; DROP TABLE person_absences;');
    db.exec('COMMIT');
    console.log(`Migration abgeschlossen: ${merged} Personen-Rollen übernommen. ${unmatched.length} alte Einträge ohne passenden Benutzer (nicht automatisch migrierbar):`);
    unmatched.forEach(u => console.log('  - ' + u));
  } catch(err){ db.exec('ROLLBACK'); console.error('Migration fehlgeschlagen:', err.message); throw err; }
}

// ---------- Auth: Passwort-Hashing ----------

// hashPassword: erzeugt Salt + scrypt-Hash für ein neues/geändertes Passwort.
function hashPassword(password){
  const salt = crypto.randomBytes(16).toString('hex');
  const hash = crypto.scryptSync(password, salt, 64).toString('hex');
  return { hash, salt };
}

// verifyPassword: prüft ein eingegebenes Passwort gegen gespeicherten Salt+Hash
// (zeitkonstanter Vergleich gegen Timing-Angriffe).
function verifyPassword(password, salt, expectedHash){
  const hash = crypto.scryptSync(password, salt, 64);
  const expected = Buffer.from(expectedHash, 'hex');
  return hash.length === expected.length && crypto.timingSafeEqual(hash, expected);
}

// bootstrapInitialAdmin: legt beim allerersten Start (users-Tabelle leer) ein
// Admin-Konto mit zufällig generiertem Passwort an (kein Hardcoded-Passwort im
// Code). Läuft danach nie wieder (Guard: COUNT(*) > 0). Das Klartext-Passwort
// wird einmalig auf die Konsole ausgegeben und zusätzlich nach
// sessions/INITIAL_ADMIN_PASSWORD.txt geschrieben (liegt außerhalb von
// public/, wird also nie von express.static ausgeliefert).
function bootstrapInitialAdmin(){
  const row = db.prepare('SELECT COUNT(*) AS c FROM users').get();
  if(row.c > 0) return;
  const password = crypto.randomBytes(15).toString('base64url');
  const { hash, salt } = hashPassword(password);
  const now = new Date().toISOString();
  db.prepare('INSERT INTO users (username, display_name, role, password_hash, password_salt, must_change_password, created_at, updated_at) VALUES (?,?,?,?,?,1,?,?)')
    .run('admin', 'Administrator', 'admin', hash, salt, now, now);
  const msg = 'Erstmaliges Admin-Konto angelegt.\n  Benutzername: admin\n  Passwort:     ' + password + '\n  (Bitte nach dem ersten Login sofort ändern.)';
  console.log(msg);
  try {
    fs.writeFileSync(path.join(SESSIONS_DIR, 'INITIAL_ADMIN_PASSWORD.txt'), msg + '\n', 'utf8');
  } catch(err){
    console.error('Konnte INITIAL_ADMIN_PASSWORD.txt nicht schreiben:', err);
  }
}

// Migration beim Serverstart ausführen
migrateLegacySchema();
migrateWeeksUpdatedAt();
migrateRemoveDepartmentPartition();
migrateBackfillProjectGewerkeAusTags();
migrateAddProjectUid();
migrateAddProjectBesprochen();
migrateAddProjectHonorar();
migrateAddProjectStatusAuthor();
migrateAddProjectAuftragsstatus();
migrateAddProjectGroesse();
migrateAddProjectUpdatedAt();
migrateAddProtokollQuelle();
migrateAddProtokollPunktErstelltAm();
migrateAddProtokollPunktStatus();
migrateUsersDepartment();
ensureSandboxWeek();
ensureUserPersonIdUniqueIndex();
migrateRetirePeopleTablesIntoUsers();
// migrateAbsencesCreatedBy: ergänzt die Spalte created_by in bestehenden
// Datenbanken (idempotent). Altbestand behält 0 und ist damit nur von der
// betroffenen Person selbst oder von Administratoren löschbar.
(function migrateAbsencesCreatedBy(){
  try {
    const cols = db.prepare("SELECT name FROM pragma_table_info('user_absences')").all().map(r => r.name);
    if(!cols.includes('created_by')){
      db.exec("ALTER TABLE user_absences ADD COLUMN created_by INTEGER NOT NULL DEFAULT 0");
      console.log('Migriert: user_absences.created_by ergänzt.');
    }
  } catch(err){
    console.error('Migration user_absences.created_by fehlgeschlagen:', err.message);
  }
})();

bootstrapInitialAdmin();

// Abgelaufene Sitzungen beim Start entfernen. Sie wurden bisher nur ignoriert,
// blieben aber für immer in der Tabelle stehen — und damit auch in jedem
// Backup-Snapshot.
try {
  const removed = db.prepare('DELETE FROM user_sessions WHERE expires_at <= ?').run(new Date().toISOString());
  if(removed && removed.changes) console.log('Abgelaufene Sitzungen entfernt:', removed.changes);
} catch(err){
  console.error('Aufräumen der Sitzungen fehlgeschlagen:', err.message);
}

// ---------- Auth: Sessions, Middleware, Endpunkte ----------

const SESSION_COOKIE = 'puebersicht_session';
// Gültigkeitsdauer einer Anmeldung. Vorgabe 30 Tage, firmenweit über den
// Schlüssel 'sessionTage' pflegbar (siehe sessionMaxAgeMs()).
const DEFAULT_SESSION_TAGE = 30;

// sessionMaxAgeMs: aktuelle Gültigkeitsdauer in Millisekunden. Wirkt auf neu
// erzeugte Sitzungen; bestehende behalten ihr gespeichertes expires_at.
function sessionMaxAgeMs(){
  return readAppSetting('sessionTage') * 24 * 60 * 60 * 1000;
}

// parseCookies: minimaler handgeschriebener Cookie-Parser (kein cookie-parser-
// Paket — Projekt-Konvention: nur express+cors als npm-Abhängigkeiten).
function parseCookies(req){
  const header = req.headers.cookie || '';
  const out = {};
  header.split(';').forEach(part => {
    const idx = part.indexOf('=');
    if(idx === -1) return;
    const k = part.slice(0, idx).trim();
    const v = part.slice(idx + 1).trim();
    if(k) out[k] = decodeURIComponent(v);
  });
  return out;
}

// setSessionCookie / clearSessionCookie: setzen bzw. löschen das Session-Cookie
// (HttpOnly, SameSite=Lax, 30 Tage Laufzeit, Secure nur bei HTTPS-Requests).
function setSessionCookie(res, token, req){
  const secure = req && req.secure ? '; Secure' : '';
  res.setHeader('Set-Cookie', `${SESSION_COOKIE}=${encodeURIComponent(token)}; Path=/; HttpOnly; SameSite=Lax; Max-Age=${Math.floor(sessionMaxAgeMs()/1000)}${secure}`);
}

function clearSessionCookie(res){
  res.setHeader('Set-Cookie', `${SESSION_COOKIE}=; Path=/; HttpOnly; SameSite=Lax; Max-Age=0`);
}

// attachUser: liest das Session-Cookie, lädt (falls gültig) Session+User und
// setzt req.user entsprechend (oder null). Läuft für JEDEN Request (auch vor
// dem statischen Datei-Ausliefern), damit spätere Routen sich stets auf
// req.user verlassen können. Aktualisiert nebenbei last_seen_at der Session.
function attachUser(req, res, next){
  req.user = null;
  const cookies = parseCookies(req);
  const token = cookies[SESSION_COOKIE];
  if(!token) return next();
  const row = db.prepare(`
    SELECT s.expires_at AS expires_at, u.id AS id, u.username AS username,
           u.display_name AS display_name, u.email AS email, u.role AS role,
           u.person_id AS person_id, u.active AS active,
           u.must_change_password AS must_change_password, u.department AS department
    FROM user_sessions s JOIN users u ON u.id = s.user_id
    WHERE s.token = ?
  `).get(token);
  if(!row || !row.active || row.expires_at <= new Date().toISOString()) return next();
  req.user = {
    id: row.id,
    username: row.username,
    displayName: row.display_name,
    email: row.email,
    role: row.role,
    personId: row.person_id,
    mustChangePassword: !!row.must_change_password,
    // department: das eigene Gewerk des Kontos — der Wochenbericht schlägt es
    // als Vorauswahl vor (js/wochenbericht/wochenbericht.js), damit man nicht
    // jedes Mal manuell das eigene Gewerk wählen muss.
    department: row.department || ''
  };
  try {
    db.prepare('UPDATE user_sessions SET last_seen_at = ? WHERE token = ?').run(new Date().toISOString(), token);
  } catch(err){
    console.error('Konnte last_seen_at nicht aktualisieren:', err);
  }
  next();
}
// (Registrierung via app.use(attachUser) erfolgt bewusst weiter oben, direkt
// nach express.json() und vor express.static — siehe dort.)

// requireAuth / requireRole: Middleware-Fabriken für spätere Phasen, die damit
// bestehende Routen absichern. In dieser Phase werden sie auf KEINE bestehende
// Route angewendet.
function requireAuth(req, res, next){
  if(!req.user) return res.status(401).json({ ok:false, error:'Nicht angemeldet.' });
  next();
}
function requireRole(role){
  return function(req, res, next){
    if(!req.user) return res.status(401).json({ ok:false, error:'Nicht angemeldet.' });
    if(req.user.role !== role) return res.status(403).json({ ok:false, error:'Keine Berechtigung.' });
    next();
  };
}

// Route: Login — prüft Benutzername/Passwort, legt bei Erfolg eine neue Session
// an und setzt das Session-Cookie. Liefert bei falschem Benutzernamen, falschem
// Passwort oder inaktivem Konto absichtlich dieselbe Fehlermeldung (kein
// Username-Enumeration-Unterschied).
// loginAttempts: Fehlversuche je Absender-Adresse (nur im Arbeitsspeicher,
// kein Zustand in der Datenbank). Nach den eingestellten Fehlversuchen ist
// die Anmeldung für die eingestellte Dauer gesperrt — ohne diese Bremse kann ein
// Passwort beliebig oft durchprobiert werden.
const loginAttempts = new Map();
// Vorgaben der Anmeldebremse; firmenweit über 'loginMaxVersuche' und
// 'loginSperreMinuten' pflegbar (siehe loginMaxAttempts()/loginBlockMs()).
const DEFAULT_LOGIN_MAX_ATTEMPTS = 10;
const DEFAULT_LOGIN_BLOCK_MINUTEN = 10;
function loginMaxAttempts(){ return readAppSetting('loginMaxVersuche'); }
function loginBlockMs(){ return readAppSetting('loginSperreMinuten') * 60 * 1000; }

// Schlüssel aus Benutzername UND Absender-Adresse: Nur nach der Adresse zu
// sperren würde im Büro alle hinter derselben Leitung aussperren, sobald sich
// eine Person zehnmal vertippt. So ist immer nur das betroffene Konto von
// dieser Stelle aus gebremst.
function loginClientKey(req, username){
  const ip = String((req.headers['x-forwarded-for'] || '').split(',')[0].trim() || req.socket.remoteAddress || 'unbekannt');
  return String(username || '').toLowerCase() + '|' + ip;
}

function loginIsBlocked(key){
  const entry = loginAttempts.get(key);
  if(!entry) return false;
  if(Date.now() - entry.first > loginBlockMs()){ loginAttempts.delete(key); return false; }
  return entry.count >= loginMaxAttempts();
}

function loginNoteFailure(key){
  const entry = loginAttempts.get(key);
  if(!entry || Date.now() - entry.first > loginBlockMs()) loginAttempts.set(key, { count: 1, first: Date.now() });
  else entry.count++;
}

app.post('/login', (req, res) => {
  const { username, password } = req.body || {};
  if(!username || !password) return res.status(400).json({ ok:false, error:'Benutzername und Passwort erforderlich.' });
  const clientKey = loginClientKey(req, username);
  if(loginIsBlocked(clientKey)){
    return res.status(429).json({ ok:false, error:'Zu viele Fehlversuche. Bitte in einigen Minuten erneut versuchen.' });
  }
  const row = db.prepare('SELECT * FROM users WHERE username = ?').get(username);
  // Auch bei unbekanntem Benutzernamen einmal hashen: Sonst verrät die
  // Antwortzeit (scrypt läuft bewusst langsam), welche Konten es gibt.
  const salt = row ? row.password_salt : 'unbekannt';
  const expected = row ? row.password_hash : crypto.scryptSync('x', 'unbekannt', 64).toString('hex');
  const passwordOk = verifyPassword(password, salt, expected);
  if(!row || !row.active || !passwordOk){
    loginNoteFailure(clientKey);
    return res.status(401).json({ ok:false, error:'Benutzername oder Passwort falsch.' });
  }
  loginAttempts.delete(clientKey);
  const token = crypto.randomBytes(32).toString('hex');
  const now = new Date().toISOString();
  const expiresAt = new Date(Date.now() + sessionMaxAgeMs()).toISOString();
  db.prepare('INSERT INTO user_sessions (token, user_id, created_at, last_seen_at, expires_at) VALUES (?,?,?,?,?)')
    .run(token, row.id, now, now, expiresAt);
  setSessionCookie(res, token, req);
  res.json({
    ok: true,
    user: {
      id: row.id,
      username: row.username,
      displayName: row.display_name,
      role: row.role,
      personId: row.person_id,
      mustChangePassword: !!row.must_change_password
    }
  });
});

// Route: Logout — löscht die Session des aktuellen Cookies und leert das Cookie.
app.post('/logout', requireAuth, (req, res) => {
  const cookies = parseCookies(req);
  const token = cookies[SESSION_COOKIE];
  if(token) db.prepare('DELETE FROM user_sessions WHERE token = ?').run(token);
  clearSessionCookie(res);
  res.json({ ok:true });
});

// Route: liefert den aktuell angemeldeten Benutzer (oder null) — Basis für das
// clientseitige Login-Overlay.
app.get('/whoami', (req, res) => {
  res.json({ ok:true, user: req.user || null });
});

// Route: Passwort ändern — verlangt immer das aktuelle Passwort (auch bei
// erzwungenem must_change_password), neues Passwort muss mind. 8 Zeichen haben.
app.post('/change-password', requireAuth, (req, res) => {
  const { currentPassword, newPassword } = req.body || {};
  if(!currentPassword || !newPassword) return res.status(400).json({ ok:false, error:'Aktuelles und neues Passwort erforderlich.' });
  if(newPassword.length < 8) return res.status(400).json({ ok:false, error:'Neues Passwort muss mindestens 8 Zeichen haben.' });
  const row = db.prepare('SELECT * FROM users WHERE id = ?').get(req.user.id);
  if(!row || !verifyPassword(currentPassword, row.password_salt, row.password_hash)){
    return res.status(401).json({ ok:false, error:'Aktuelles Passwort falsch.' });
  }
  const { hash, salt } = hashPassword(newPassword);
  db.prepare('UPDATE users SET password_hash = ?, password_salt = ?, must_change_password = 0, updated_at = ? WHERE id = ?')
    .run(hash, salt, new Date().toISOString(), row.id);
  res.json({ ok:true });
});

// Route: eigene E-Mail-Adresse ändern — im Unterschied zu Anzeigename, Rolle
// und Personen-Zuordnung (weiterhin nur PATCH /admin/users/:id) pflegt jedes
// Konto seine E-Mail selbst, deshalb hier statt in der Admin-Benutzerverwaltung.
// Kein Formatzwang (wie auch beim Admin-Pfad) — nur eine leere Eingabe wird
// abgelehnt, das Leeren des Feldes geschieht wie gehabt über die
// Benutzerverwaltung.
app.post('/change-email', requireAuth, (req, res) => {
  const { email } = req.body || {};
  const trimmed = String(email || '').trim();
  if(!trimmed) return res.status(400).json({ ok:false, error:'E-Mail-Adresse erforderlich.' });
  db.prepare('UPDATE users SET email = ?, updated_at = ? WHERE id = ?')
    .run(trimmed, new Date().toISOString(), req.user.id);
  res.json({ ok:true, email: trimmed });
});

// ---------- Admin-Benutzerverwaltung ----------
//
// Alle Routen dieses Abschnitts sind `requireRole('admin')`-geschützt und
// dienen dem Abschnitt Benutzerverwaltung des Bereichs Admin. Sie legen
// Benutzerkonten an, ändern sie, setzen Passwörter zurück und löschen Konten
// (Soft-Delete standardmäßig, Hard-Delete nur ohne Referenzen in
// project_files/project_file_reviewers/project_file_comments).

// parseCapacityHours: prüft die vertraglichen Wochenstunden eines Kontos.
// Leer/undefiniert ergibt 0 = "nicht gepflegt"; das Frontend rechnet dann mit
// einer vollen Stelle, damit Bestandskonten ihr bisheriges Auslastungsbild
// behalten. Rückgabe: Zahl 0–80 oder null bei unbrauchbarer Eingabe.
function parseCapacityHours(value){
  if(value === undefined || value === null || value === '') return 0;
  const n = Number(value);
  if(!Number.isFinite(n) || n < 0 || n > 80) return null;
  return n;
}

// generateRandomPassword: erzeugt ein zufälliges Klartext-Passwort für neu
// angelegte Accounts bzw. einen Passwort-Reset (gleiche Methode wie
// bootstrapInitialAdmin).
function generateRandomPassword(){
  return crypto.randomBytes(15).toString('base64url');
}

// serializeUserRow: wandelt eine users-Zeile in die öffentliche Repräsentation
// um (OHNE password_hash/password_salt).
function serializeUserRow(row){
  return {
    id: row.id,
    username: row.username,
    displayName: row.display_name,
    email: row.email,
    role: row.role,
    personId: row.person_id,
    department: row.department,
    initials: row.initials,
    capacityHours: Number(row.capacity_hours) || 0,
    active: !!row.active,
    mustChangePassword: !!row.must_change_password,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

// loadUserRolesByUserId: Batch-Query aller user_roles, gruppiert nach user_id
// (analog zum bestehenden Muster in loadGlobalPeople() für person_roles) —
// vermeidet eine Einzelabfrage pro Zeile in GET /admin/users bzw. GET /users.
function loadUserRolesByUserId(){
  const rows = db.prepare('SELECT * FROM user_roles ORDER BY role_order').all();
  const byUser = {};
  rows.forEach(r => { (byUser[r.user_id] = byUser[r.user_id] || []).push(r.role); });
  return byUser;
}

// Route: Liste aller Benutzer (ohne Passwortfelder), sortiert nach Benutzername.
app.get('/admin/users', requireRole('admin'), (req, res) => {
  try {
    const rows = db.prepare('SELECT * FROM users ORDER BY username').all();
    const rolesByUser = loadUserRolesByUserId();
    res.json({ ok: true, users: rows.map(r => Object.assign(serializeUserRow(r), { roles: rolesByUser[r.id] || [] })) });
  } catch(err){
    console.error('Admin-Benutzerliste fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Abwesenheiten eines einzelnen Benutzers, sortiert nach Start-Datum.
// GET /admin/users liefert bewusst keine Abwesenheiten mit (würde die Liste
// unnötig aufblähen) — das Bearbeiten-Modal in admin-users.js lädt sie beim
// Öffnen separat über diese Route nach.
app.get('/admin/users/:id/absences', requireRole('admin'), (req, res) => {
  const id = Number(req.params.id);
  try {
    const rows = db.prepare('SELECT id, start, end, type, note FROM user_absences WHERE user_id = ? ORDER BY start').all(id);
    res.json({ ok: true, absences: rows });
  } catch(err){
    console.error('Abwesenheiten laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Legt einen neuen Benutzer an. Passwort entweder explizit (mind. 8
// Zeichen) oder automatisch generiert (generatePassword: true); in jedem Fall
// muss der neue Account beim ersten Login sein Passwort ändern. `personId`
// wird 1:1 vom Client übernommen (Berechnung/Vorschau via personIdForName()
// passiert clientseitig).
app.post('/admin/users', requireRole('admin'), (req, res) => {
  const { username, displayName, email, role, personId, department, initials, capacityHours, roles, password, generatePassword } = req.body || {};
  if(!username || !String(username).trim()) return res.status(400).json({ ok: false, error: 'Benutzername erforderlich.' });
  if(!displayName || !String(displayName).trim()) return res.status(400).json({ ok: false, error: 'Anzeigename erforderlich.' });
  const finalRole = role ? String(role) : 'member';
  if(finalRole !== 'admin' && finalRole !== 'member') return res.status(400).json({ ok: false, error: 'Ungültige Rolle.' });
  if(typeof department !== 'string' || !VALID_DEPARTMENTS.has(department)){
    return res.status(400).json({ ok: false, error: 'Ungültige oder fehlende Abteilung.' });
  }
  const roleList = Array.isArray(roles) ? roles : [];
  for(const r of roleList){
    if(!VALID_DISCIPLINE_ROLES.has(r)) return res.status(400).json({ ok: false, error: 'Ungültige Rolle: ' + r });
  }
  // capacityHours: vertragliche Wochenstunden (Teilzeit). 0 bzw. keine
  // Angabe heißt "nicht gepflegt" — das Frontend rechnet dann mit einer
  // vollen Stelle weiter.
  const capacity = parseCapacityHours(capacityHours);
  if(capacity === null) return res.status(400).json({ ok: false, error: 'Wochenstunden müssen zwischen 0 und 80 liegen.' });

  let plainPassword;
  if(generatePassword === true){
    plainPassword = generateRandomPassword();
  } else if(typeof password === 'string' && password){
    if(password.length < 8) return res.status(400).json({ ok: false, error: 'Passwort muss mindestens 8 Zeichen haben.' });
    plainPassword = password;
  } else {
    return res.status(400).json({ ok: false, error: 'Passwort erforderlich (manuell oder generatePassword).' });
  }

  const { hash, salt } = hashPassword(plainPassword);
  const now = new Date().toISOString();
  try {
    const info = db.prepare(`INSERT INTO users
      (username, display_name, email, role, person_id, department, initials, capacity_hours, password_hash, password_salt, active, must_change_password, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 1, ?, ?)`)
      .run(String(username).trim(), String(displayName).trim(), String(email || ''), finalRole, String(personId || ''), department, String(initials || ''), capacity, hash, salt, now, now);
    const insRole = db.prepare('INSERT INTO user_roles (user_id, role, role_order) VALUES (?, ?, ?)');
    roleList.forEach((r, idx) => insRole.run(info.lastInsertRowid, r, idx));
    const row = db.prepare('SELECT * FROM users WHERE id = ?').get(info.lastInsertRowid);
    const result = { ok: true, user: Object.assign(serializeUserRow(row), { roles: roleList }) };
    if(generatePassword === true) result.generatedPassword = plainPassword;
    res.json(result);
  } catch(err){
    if(err && String(err.message || '').includes('person_id')){
      return res.status(409).json({ ok: false, error: 'Person-ID bereits vergeben.' });
    }
    if(err && String(err.message || '').includes('UNIQUE')){
      return res.status(409).json({ ok: false, error: 'Benutzername bereits vergeben.' });
    }
    console.error('Benutzer anlegen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Aktualisiert einen bestehenden Benutzer (nur die übergebenen Felder).
// Schützt einen Admin davor, sich selbst zu deaktivieren oder zu degradieren.
app.patch('/admin/users/:id', requireRole('admin'), (req, res) => {
  const id = Number(req.params.id);
  const row = db.prepare('SELECT * FROM users WHERE id = ?').get(id);
  if(!row) return res.status(404).json({ ok: false, error: 'Benutzer nicht gefunden.' });

  const { displayName, email, role, active, personId, department, initials, capacityHours, roles } = req.body || {};
  if(role !== undefined && role !== 'admin' && role !== 'member'){
    return res.status(400).json({ ok: false, error: 'Ungültige Rolle.' });
  }
  if(department !== undefined && !VALID_DEPARTMENTS.has(department)){
    return res.status(400).json({ ok: false, error: 'Ungültige Abteilung.' });
  }
  let capacity;
  if(capacityHours !== undefined){
    capacity = parseCapacityHours(capacityHours);
    if(capacity === null) return res.status(400).json({ ok: false, error: 'Wochenstunden müssen zwischen 0 und 80 liegen.' });
  }
  let roleList;
  if(roles !== undefined){
    roleList = Array.isArray(roles) ? roles : [];
    for(const r of roleList){
      if(!VALID_DISCIPLINE_ROLES.has(r)) return res.status(400).json({ ok: false, error: 'Ungültige Rolle: ' + r });
    }
  }
  const isSelf = req.user.id === id;
  const wouldDeactivate = active !== undefined && !active;
  const wouldDemote = role !== undefined && row.role === 'admin' && role === 'member';
  if(isSelf && (wouldDeactivate || wouldDemote)){
    return res.status(400).json({ ok: false, error: 'Sie können sich nicht selbst deaktivieren oder degradieren.' });
  }

  const fields = [];
  const values = [];
  if(displayName !== undefined){ fields.push('display_name = ?'); values.push(String(displayName)); }
  if(email !== undefined){ fields.push('email = ?'); values.push(String(email)); }
  if(role !== undefined){ fields.push('role = ?'); values.push(role); }
  if(active !== undefined){ fields.push('active = ?'); values.push(active ? 1 : 0); }
  if(personId !== undefined){ fields.push('person_id = ?'); values.push(String(personId)); }
  if(department !== undefined){ fields.push('department = ?'); values.push(department); }
  if(initials !== undefined){ fields.push('initials = ?'); values.push(String(initials)); }
  if(capacity !== undefined){ fields.push('capacity_hours = ?'); values.push(capacity); }
  const now = new Date().toISOString();
  fields.push('updated_at = ?'); values.push(now);
  values.push(id);
  try {
    db.prepare(`UPDATE users SET ${fields.join(', ')} WHERE id = ?`).run(...values);
    if(roleList !== undefined){
      db.prepare('DELETE FROM user_roles WHERE user_id = ?').run(id);
      const insRole = db.prepare('INSERT INTO user_roles (user_id, role, role_order) VALUES (?, ?, ?)');
      roleList.forEach((r, idx) => insRole.run(id, r, idx));
    }
    // Deaktivierung erzwingt sofortigen Logout (alle Sessions löschen).
    if(wouldDeactivate){
      db.prepare('DELETE FROM user_sessions WHERE user_id = ?').run(id);
    }
    const updated = db.prepare('SELECT * FROM users WHERE id = ?').get(id);
    const updatedRoles = roleList !== undefined ? roleList : (loadUserRolesByUserId()[id] || []);
    res.json({ ok: true, user: Object.assign(serializeUserRow(updated), { roles: updatedRoles }) });
  } catch(err){
    if(err && String(err.message || '').includes('person_id')){
      return res.status(409).json({ ok: false, error: 'Person-ID bereits vergeben.' });
    }
    console.error('Benutzer aktualisieren fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Setzt das Passwort eines Benutzers auf ein neues Zufallspasswort
// zurück, erzwingt eine Passwortänderung beim nächsten Login und löscht alle
// bestehenden Sessions (erzwingt Neuanmeldung).
app.post('/admin/users/:id/reset-password', requireRole('admin'), (req, res) => {
  const id = Number(req.params.id);
  const row = db.prepare('SELECT * FROM users WHERE id = ?').get(id);
  if(!row) return res.status(404).json({ ok: false, error: 'Benutzer nicht gefunden.' });
  const plainPassword = generateRandomPassword();
  const { hash, salt } = hashPassword(plainPassword);
  const now = new Date().toISOString();
  try {
    db.prepare('UPDATE users SET password_hash = ?, password_salt = ?, must_change_password = 1, updated_at = ? WHERE id = ?')
      .run(hash, salt, now, id);
    db.prepare('DELETE FROM user_sessions WHERE user_id = ?').run(id);
    res.json({ ok: true, generatedPassword: plainPassword });
  } catch(err){
    console.error('Passwort zurücksetzen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Löscht einen Benutzer. Standardmäßig Soft-Delete (active = 0 +
// Sessions löschen, erzwingt Logout); mit `?hard=1` endgültiges Löschen,
// sofern der Benutzer nirgends referenziert wird (project_files.uploaded_by_user_id,
// project_file_reviewers.user_id, project_file_comments.user_id — diese Tabellen
// sind schon angelegt, aber aktuell noch leer). Ein Admin kann sich selbst
// weder soft- noch hard-löschen.
app.delete('/admin/users/:id', requireRole('admin'), (req, res) => {
  const id = Number(req.params.id);
  const row = db.prepare('SELECT * FROM users WHERE id = ?').get(id);
  if(!row) return res.status(404).json({ ok: false, error: 'Benutzer nicht gefunden.' });
  if(req.user.id === id) return res.status(400).json({ ok: false, error: 'Sie können Ihren eigenen Account nicht löschen.' });

  const hard = req.query.hard === '1';
  try {
    if(hard){
      const referenced = db.prepare(`SELECT
          (SELECT COUNT(*) FROM project_files WHERE uploaded_by_user_id = ?) +
          (SELECT COUNT(*) FROM project_file_reviewers WHERE user_id = ?) +
          (SELECT COUNT(*) FROM project_file_comments WHERE user_id = ?) AS c`)
        .get(id, id, id);
      if(referenced.c > 0){
        return res.status(409).json({ ok: false, error: 'Benutzer wird noch referenziert und kann nicht endgültig gelöscht werden.' });
      }
      // user_sessions dieses Users werden durch ON DELETE CASCADE automatisch mitgelöscht.
      db.prepare('DELETE FROM users WHERE id = ?').run(id);
      return res.json({ ok: true, hard: true });
    }
    const now = new Date().toISOString();
    db.prepare('UPDATE users SET active = 0, updated_at = ? WHERE id = ?').run(now, id);
    db.prepare('DELETE FROM user_sessions WHERE user_id = ?').run(id);
    res.json({ ok: true, hard: false });
  } catch(err){
    console.error('Benutzer löschen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Speichert eine komplette Woche (als JSON-Objekt) für eine Abteilung
app.post('/save-week', requireAuth, (req, res) => {
  const { week, baseUpdatedAt } = req.body || {};
  if(!week || typeof week.label !== 'string' || !week.label) return res.status(400).json({ ok: false, error: 'Missing week or label' });
  // Auftragsstatus prüfen, BEVOR irgendetwas geschrieben wird: /save-week ist
  // der einzige Schreibweg für Projektdaten, ein unbekannter Wert bliebe also
  // dauerhaft stehen (siehe findInvalidAuftragsstatus()).
  const badStatus = findInvalidAuftragsstatus(week);
  if(badStatus){
    return res.status(400).json({
      ok: false,
      error: 'Unbekannter Auftragsstatus "' + badStatus.wert + '" am Projekt '
        + ((badStatus.nr ? badStatus.nr + ' ' : '') + badStatus.name).trim()
        + '. Erlaubt: ' + Array.from(VALID_AUFTRAGSSTATUS).join(', ') + '.'
    });
  }
  try {
    // Revisions-/Konflikt-Check: Mit `baseUpdatedAt` meldet der Client die
    // Version, auf der seine lokalen Daten beruhen. Ist die Woche auf dem
    // Server zwischenzeitlich von jemand anderem gespeichert worden, wird
    // NICHT überschrieben, sondern ein Konflikt gemeldet (HTTP 409).
    // Wichtig: Auch eine leere Basis (updated_at='' direkt nach der Migration)
    // wird geprüft – nur Clients OHNE baseUpdatedAt-Feld (alte Versionen)
    // überspringen die Prüfung. Der Check läuft synchron vor dem Schreiben,
    // daher gibt es im Prozess keine Race-Condition zwischen Prüfung und
    // Transaktion.
    // Sperr-Status serverseitig durchsetzen: Eine gesperrte Sitzung darf NUR
    // über /unlock-week (requireRole('admin')) wieder geöffnet werden. Das
    // Sperren selbst (false -> true) bleibt jedem erlaubt, weil es die
    // Auto-Sperre des Clients (applyAutoLock(), public/js/weeks.js) beim
    // normalen Speichern mitschreibt. Ohne diese Prüfung wäre die Sperre reine
    // Client-Logik: ein beliebiges Mitglied könnte die Woche mit locked:false
    // zurückschreiben und die Admin-Grenze umgehen.
    const lockRow = db.prepare('SELECT locked FROM weeks WHERE label = ?').get(String(week.label));
    if(lockRow && lockRow.locked && !week.locked && !(req.user && req.user.role === 'admin')){
      return res.status(403).json({
        ok: false,
        error: 'Die Sitzung "' + week.label + '" ist gesperrt. Nur Administratoren können sie wieder öffnen.'
      });
    }

    if(typeof baseUpdatedAt === 'string'){
      const row = db.prepare('SELECT updated_at FROM weeks WHERE label = ?').get(String(week.label));
      if(row){
        if(baseUpdatedAt !== row.updated_at){
          return res.status(409).json({
            ok: false,
            conflict: true,
            error: 'Die Sitzung "' + week.label + '" wurde zwischenzeitlich von einer anderen Person geändert.',
            updatedAt: row.updated_at
          });
        }
      } else if(baseUpdatedAt !== ''){
        // Die Woche existiert am Server nicht mehr (vermutlich gelöscht). Ein
        // Save mit nicht-leerer, also älterer Revision darf eine gelöschte
        // Sitzung nicht wiederbeleben; nur neue Wochen (leere/fehlende Basis)
        // dürfen angelegt werden.
        return res.status(409).json({
          ok: false,
          conflict: true,
          error: 'Die Sitzung "' + week.label + '" wurde zwischenzeitlich gelöscht.',
          updatedAt: ''
        });
      }
    }
    // `updatedAt` zurückgeben, damit der Client seine lokale Revisions-Basis
    // aktualisieren kann (verhindert falsche Eigen-Konflikte beim nächsten Save).
    const updatedAt = saveWeekRelational(week);
    console.log('Saved week:', week.label);
    return res.json({ ok: true, updatedAt: updatedAt || new Date().toISOString() });
  } catch(err){
    console.error('Save week error', err);
    return res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Löscht eine Woche (fehlende Woche wird toleriert)
app.post('/delete-week', requireRole('admin'), (req, res) => {
  const { label } = req.body || {};
  if(!label) return res.status(400).json({ ok: false, error: 'Missing label' });
  if(label === SANDBOX_WEEK_LABEL) return res.status(400).json({ ok: false, error: 'Die Planung-Sandbox ist keine Sitzung und kann hier nicht gelöscht werden.' });
  try {
    const row = db.prepare('SELECT 1 FROM weeks WHERE label = ?').get(String(label));
    const existed = !!row;
    // In einer Transaktion: deleteWeekData() räumt 18 Tabellen leer. Bricht
    // eines der DELETEs ab, blieben sonst Waisen-Zeilen zurück, die eine
    // später gleichnamig angelegte Sitzung wieder einsammeln würde.
    db.exec('BEGIN');
    try {
      deleteWeekData(String(label));
      db.exec('COMMIT');
    } catch(err){
      try { db.exec('ROLLBACK'); } catch(e){ /* keine offene Transaktion */ }
      throw err;
    }
    console.log('Deleted week:', label, existed ? '' : '(nicht vorhanden)');
    return res.json({ ok: true, deleted: existed });
  } catch(err){
    console.error('Delete week error', err);
    return res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Entsperrt eine gesperrte Woche (Admin-only — ersetzt die bisher rein
// clientseitige Entsperr-Logik). Setzt `locked = 0` und schreibt eine neue
// Revision (`updated_at`), damit der Client seine lokale Sync-Basis
// aktualisieren kann (gleiche Konvention wie bei /save-week).
app.post('/unlock-week', requireRole('admin'), (req, res) => {
  const { label } = req.body || {};
  if(!label) return res.status(400).json({ ok: false, error: 'Missing label' });
  if(label === SANDBOX_WEEK_LABEL) return res.status(400).json({ ok: false, error: 'Die Planung-Sandbox ist keine Sitzung und wird nie gesperrt.' });
  try {
    const row = db.prepare('SELECT 1 FROM weeks WHERE label = ?').get(String(label));
    if(!row) return res.status(404).json({ ok: false, error: 'Sitzung nicht gefunden.' });
    const updatedAt = new Date().toISOString();
    db.prepare('UPDATE weeks SET locked = 0, updated_at = ? WHERE label = ?').run(updatedAt, String(label));
    console.log('Unlocked week:', label);
    return res.json({ ok: true, updatedAt });
  } catch(err){
    console.error('Unlock week error', err);
    return res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Lädt alle Wochen (jetzt eine einzige globale Reihe) plus die
// firmenweite Personenliste. Liefert strukturierte JSON-Objekte (aus den
// relationalen Tabellen zusammengesetzt); nach Label sortiert.
app.get('/load-all', requireAuth, (req, res) => {
  try {
    const allWeeks = loadWeeks();
    // Die reservierte Sandbox-"Sitzung" (Planung, siehe SANDBOX_WEEK_LABEL)
    // ist keine echte Sitzung — sie wird separat als sandboxPlans
    // ausgeliefert, statt in weeks[] zu landen. So kommt sie clientseitig
    // strukturell nie in db.weeks an und kann dort auch nicht versehentlich
    // in Board/Wochenbericht/Auslastung/Sitzungsverwaltung auftauchen.
    const weeks = allWeeks.filter(w => w.label !== SANDBOX_WEEK_LABEL);
    const sandbox = allWeeks.find(w => w.label === SANDBOX_WEEK_LABEL);
    const sandboxPlans = sandbox ? sandbox.projects : [];
    const sandboxUpdatedAt = sandbox ? sandbox.updatedAt : '';
    const people = loadGlobalPeople();
    res.json({ ok: true, weeks, people, sandboxPlans, sandboxUpdatedAt });
  } catch(err){
    console.error('Load error', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// ---------- Dashboard ----------

// heuteIsoLokal / isoPlusTageLokal: Datum als YYYY-MM-TT in der ZEITZONE DES
// SERVERS, nicht in UTC.
//
// new Date().toISOString().slice(0,10) rechnet nach UTC um: In deutscher
// Sommerzeit (UTC+2) liefert es zwischen 00:00 und 02:00 Ortszeit noch den
// Vortag. Termine von heute galten in diesem Fenster als überfällig, und die
// Abwesenheitsliste ließ eine heute beginnende Abwesenheit aus. Voraussetzung
// ist, dass der Server auf die Zeitzone des Büros gestellt ist — im
// Docker-Container also TZ=Europe/Berlin.
// Parameter: (isoPlusTageLokal) n = Tage in die Zukunft, auch negativ.
// Rückgabe: String.
function heuteIsoLokal(){
  return isoPlusTageLokal(0);
}
function isoPlusTageLokal(n){
  const d = new Date();
  d.setDate(d.getDate() + (Number(n) || 0));
  const m = String(d.getMonth() + 1).padStart(2, '0');
  const t = String(d.getDate()).padStart(2, '0');
  return d.getFullYear() + '-' + m + '-' + t;
}

// weekSortKeyServer: serverseitige Portierung von weekSortKey()
// (public/js/utils.js) — wird NUR im Fallback von /my-dashboard gebraucht,
// wenn die applyAutoLock()-Invariante (genau eine entsperrte Woche je
// Abteilung) ausnahmsweise verletzt ist (0 oder >1 entsperrte Wochen).
function weekSortKeyServer(label){
  const m = String(label || '').match(/KW\s*(\d+)[^\d]+(\d{4})/i);
  if(m){
    const kw = m[1].padStart(2, '0');
    return `${m[2]}-${kw}`;
  }
  return String(label || '').toLowerCase();
}

// Route: persönliches Dashboard des angemeldeten Benutzers — liefert die
// offenen TODOs des eigenen person_id in der aktuell entsperrten (firmenweit
// globalen) Sitzung, ausstehende Datei-Freigaben sowie `colleagueAbsences`
// (aktuelle/anstehende Abwesenheiten aller anderen aktiven Nutzer, siehe
// loadGlobalPeople()). Jede Aufgabe bekommt zusätzlich die Tags ihres
// Projekts (`tags: string[]`, ggf. leer) mit — separat nachgeladen aus
// project_tags, gruppiert nach project_id (siehe Kommentar unten), NICHT per
// JOIN (würde bei mehreren Tags Aufgaben-Zeilen verdoppeln).
app.get('/my-dashboard', requireAuth, (req, res) => {
  try {
    // Normalfall: applyAutoLock() garantiert genau eine entsperrte Woche
    // (jetzt firmenweit global, nicht mehr je Abteilung).
    const unlocked = db.prepare('SELECT label FROM weeks WHERE locked = 0').all();
    let weekLabel = null;
    if(unlocked.length === 1){
      weekLabel = unlocked[0].label;
    } else {
      // Fallback (0 oder >1 entsperrte Wochen, sollte normalerweise nicht
      // vorkommen): jüngste Woche nach weekSortKeyServer() wählen.
      const all = db.prepare('SELECT label FROM weeks').all();
      if(all.length > 0){
        weekLabel = all.map(r => r.label)
          .sort((a, b) => weekSortKeyServer(b).localeCompare(weekSortKeyServer(a)))[0];
      }
    }
    let tasks = [];
    if(weekLabel && req.user.personId){
      tasks = db.prepare(`
        SELECT t.todo_id AS todoId, t.title AS title, t.start AS start, t.end AS end,
               t.progress AS progress, t.project_id AS projectId, p.nr AS projectNr, p.name AS projectName
        FROM plan_todos t
        JOIN plan_todo_assignees a
          ON a.week_label = t.week_label
         AND a.project_id = t.project_id AND a.todo_id = t.todo_id
        JOIN projects p
          ON p.week_label = t.week_label
         AND p.project_id = t.project_id
        WHERE t.week_label = ? AND t.done = 0 AND a.person_id = ?
      `).all(weekLabel, req.user.personId);
      // Sortierung nach Fälligkeit aufsteigend, leere Werte ans Ende.
      tasks.sort((a, b) => {
        if(!a.end && !b.end) return 0;
        if(!a.end) return 1;
        if(!b.end) return -1;
        return a.end < b.end ? -1 : (a.end > b.end ? 1 : 0);
      });
      // Tags der zugehörigen Projekte nachladen: project_tags hat keine
      // todo_id, ein direkter JOIN würde also bei mehreren Tags pro Projekt
      // die TODO-Zeile vervielfachen. Separate, nach (week_label, project_id)
      // gruppierte Abfrage stattdessen (gleiches Muster wie beim Zusammenbau
      // der Projekte in loadWeeks()). project_id ist nur ein internes
      // Zuordnungsfeld für diese Nachladung und wird vor der Antwort wieder
      // entfernt (kein Teil der bisherigen /my-dashboard-Antwortform).
      const projectIds = [...new Set(tasks.map(t => t.projectId))];
      if(projectIds.length > 0){
        const placeholders = projectIds.map(() => '?').join(',');
        const tagRows = db.prepare(`
          SELECT project_id AS projectId, tag FROM project_tags
          WHERE week_label = ? AND project_id IN (${placeholders})
          ORDER BY tag_order
        `).all(weekLabel, ...projectIds);
        const tagsByProject = {};
        tagRows.forEach(r => { (tagsByProject[r.projectId] || (tagsByProject[r.projectId] = [])).push(r.tag); });
        tasks.forEach(t => { t.tags = tagsByProject[t.projectId] || []; });
      }
      tasks.forEach(t => { delete t.projectId; });
    }

    // ---------- Termine der eigenen Projekte ----------
    //
    // Nicht dasselbe wie `tasks` oben: Dort stehen die Aufgaben, die der
    // Person PERSÖNLICH zugewiesen sind — auch in Projekten, mit denen sie
    // sonst nichts zu tun hat. Hier stehen die Termine der PROJEKTE, denen
    // sie zugeordnet ist (project_assign), unabhängig davon, wer die einzelne
    // Aufgabe erledigt. Eine Projektleitung muss wissen, wann im eigenen
    // Projekt etwas fällig wird, auch wenn ein anderer es abarbeitet.
    //
    // Doppelungen werden unterdrückt: Was schon unter `tasks` steht,
    // erscheint hier nicht noch einmal (siehe eigeneTodoIds).
    //
    // Der Zeitraum endet nach 21 Tagen. Ohne Grenze stünden Termine aus einem
    // Jahr in der Liste und die dringenden gingen darin unter; überfällige
    // bleiben dagegen ohne Altersgrenze stehen, denn eine Frist verfällt
    // nicht dadurch, dass sie lange gerissen ist.
    const HORIZONT_TAGE = 21;
    let projectDates = [];
    if(weekLabel && req.user.personId){
      const grenzeIso = isoPlusTageLokal(HORIZONT_TAGE);
      const heuteIso = heuteIsoLokal();

      // Projekte, denen die Person in irgendeiner Rolle zugeordnet ist.
      // state 1 = Hauptverantwortung, 2 = Unterstützung; 0 kommt in der
      // Tabelle nicht vor (cycleAssign() löscht den Eintrag stattdessen),
      // wird aber sicherheitshalber ausgeschlossen.
      const meineProjekte = db.prepare(`
        SELECT DISTINCT a.project_id AS projectId
        FROM project_assign a
        WHERE a.week_label = ? AND a.person_id = ? AND a.state IN (1, 2)
      `).all(weekLabel, req.user.personId).map(r => r.projectId);

      if(meineProjekte.length > 0){
        const platzhalter = meineProjekte.map(() => '?').join(',');
        const eigeneTodoIds = new Set(tasks.map(t => t.todoId));

        // Aufgaben: offen, mit Fälligkeit, in den eigenen Projekten.
        const todos = db.prepare(`
          SELECT t.todo_id AS id, t.title AS titel, t.end AS datum, t.progress AS progress,
                 t.project_id AS projectId, p.nr AS projectNr, p.name AS projectName
          FROM plan_todos t
          JOIN projects p ON p.week_label = t.week_label AND p.project_id = t.project_id
          WHERE t.week_label = ? AND t.done = 0 AND t.end != ''
            AND t.project_id IN (${platzhalter})
        `).all(weekLabel, ...meineProjekte);

        // Meilensteine: sie tragen kein Erledigt-Kennzeichen, deshalb zählt
        // allein das Datum.
        const milestones = db.prepare(`
          SELECT m.milestone_id AS id, m.name AS titel, m.date AS datum,
                 m.project_id AS projectId, p.nr AS projectNr, p.name AS projectName
          FROM plan_milestones m
          JOIN projects p ON p.week_label = m.week_label AND p.project_id = m.project_id
          WHERE m.week_label = ? AND m.date != ''
            AND m.project_id IN (${platzhalter})
        `).all(weekLabel, ...meineProjekte);

        // Zuständige je Aufgabe nachladen — getrennt, weil ein JOIN über
        // plan_todo_assignees die Aufgabenzeile bei mehreren Zuständigen
        // vervielfachen würde (gleiches Muster wie bei den Tags oben).
        const namen = {};
        db.prepare(`
          SELECT a.todo_id AS todoId, a.project_id AS projectId, a.person_id AS personId
          FROM plan_todo_assignees a
          WHERE a.week_label = ? AND a.project_id IN (${platzhalter})
        `).all(weekLabel, ...meineProjekte).forEach(r => {
          const key = r.projectId + '|' + r.todoId;
          (namen[key] || (namen[key] = [])).push(r.personId);
        });
        // Namen der Zuständigen: aus week_people der Sitzung, NICHT aus
        // loadGlobalPeople(). Die globale Liste enthält nur Personen mit
        // Benutzerkonto; die Teilnehmerliste einer Sitzung führt dagegen
        // jede Person, die dort mitarbeitet. Ohne diesen Weg stand in der
        // Terminliste "p_ap" statt "Alexander Plum".
        const personenNamen = {};
        db.prepare(`
          SELECT person_id AS id, name, full_name AS fullName
          FROM week_people WHERE week_label = ?
        `).all(weekLabel).forEach(w => {
          personenNamen[w.id] = w.fullName || w.name || w.id;
        });

        projectDates = todos.map(t => ({
          art: 'aufgabe',
          id: t.id,
          titel: t.titel,
          datum: t.datum,
          progress: t.progress,
          projectNr: t.projectNr,
          projectName: t.projectName,
          zustaendig: (namen[t.projectId + '|' + t.id] || [])
            .map(pid => personenNamen[pid] || pid),
          selbst: eigeneTodoIds.has(t.id),
        })).concat(milestones.map(m => ({
          art: 'meilenstein',
          id: m.id,
          titel: m.titel,
          datum: m.datum,
          projectNr: m.projectNr,
          projectName: m.projectName,
          zustaendig: [],
          selbst: false,
        })));

        projectDates = projectDates
          // Was bereits unter "Meine Aufgaben" steht, hier nicht wiederholen.
          .filter(d => !d.selbst)
          .filter(d => d.datum <= grenzeIso)
          .sort((a, b) => (a.datum < b.datum ? -1 : (a.datum > b.datum ? 1 : 0)));

        // Der Server liefert auch gleich die Einordnung mit, damit Browser
        // und PDF/Export nicht jeweils eigene Stichtage rechnen.
        projectDates.forEach(d => {
          d.ueberfaellig = d.datum < heuteIso;
          d.heute = d.datum === heuteIso;
        });
      }
    }

    const pendingReviews = db.prepare(`
      SELECT f.id AS fileId, f.project_nr AS projectNr,
             f.project_name AS projectName, f.original_filename AS originalFilename,
             COALESCE(NULLIF(u.display_name, ''), u.username) AS uploadedBy,
             f.created_at AS createdAt
      FROM project_file_reviewers r
      JOIN project_files f ON f.id = r.file_id
      LEFT JOIN users u ON u.id = f.uploaded_by_user_id
      WHERE r.user_id = ? AND r.decision = 'pending'
      ORDER BY f.created_at DESC
    `).all(req.user.id);

    // Abwesenheiten der Kollegen: firmenweite Personenliste (loadGlobalPeople(),
    // keine Abteilungstrennung mehr), gefiltert auf Abwesenheiten, die heute
    // laufen oder innerhalb der nächsten 14 Tage beginnen — der Aufrufer selbst
    // wird per person_id ausgeschlossen. Sortiert nach Startdatum; `current`
    // markiert eine heute laufende Abwesenheit fürs Hervorheben im Dashboard.
    const todayIso = heuteIsoLokal();
    const horizonIso = isoPlusTageLokal(14);
    const colleagueAbsences = [];
    loadGlobalPeople().forEach(person => {
      if(person.id && person.id === req.user.personId) return;
      (person.absences || []).forEach(a => {
        if(!a.start || !a.end) return;
        if(a.end < todayIso || a.start > horizonIso) return;
        colleagueAbsences.push({
          personId: person.id,
          name: person.fullName || person.name,
          type: a.type,
          note: a.note,
          start: a.start,
          end: a.end,
          current: a.start <= todayIso && todayIso <= a.end,
        });
      });
    });
    colleagueAbsences.sort((a, b) => (a.start < b.start ? -1 : (a.start > b.start ? 1 : 0)));

    res.json({ ok: true, weekLabel, tasks, projectDates, pendingReviews, colleagueAbsences });
  } catch(err){
    console.error('Dashboard-Fehler', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// ---------- Datei-Upload, Freigabe, Kommentare ----------
//
// Dateien werden über project_nr allein mit Projekten verknüpft, NICHT über
// (week_label, project_id) — project_id wird bei jedem Wochen-Klon neu
// generiert (cloneWeek(), public/js/weeks.js), project_nr bleibt über den
// gesamten Projektlebenszyklus stabil und identifiziert seit der Zusammen-
// führung gleichnummeriger Projekte über ehemalige Abteilungsgrenzen hinweg
// eindeutig das eine logische Projekt. Ein Projekt ohne project_nr kann daher
// keine Dateien erhalten.
//
// Sicherheitskritisch: original_filename/project_nr/project_name/filename
// (Client-Eingaben) werden NIEMALS in einen Dateisystempfad eingesetzt. Der
// Pfad setzt sich ausschließlich aus einer serverseitig erzeugten UUID
// (stored_name) zusammen; original_filename dient nur als Anzeige-/
// Download-Header-Wert.

// UPLOADS_DIR: Basisverzeichnis für hochgeladene Projektdateien
// ein Unterordner (lazy angelegt, siehe uploadDirFor()).
const UPLOADS_DIR = path.join(SESSIONS_DIR, 'uploads');

// uploadDirFor: liefert (und legt bei Bedarf an) das Upload-Verzeichnis einer
// Abteilung. Parameter department MUSS bereits gegen VALID_DEPARTMENTS
// geprüft sein (nur dann sind ausschließlich 'ELT'/'HLS'/'ARCH' möglich).
function uploadDirFor(){
  if(!fs.existsSync(UPLOADS_DIR)) fs.mkdirSync(UPLOADS_DIR, { recursive: true });
  return UPLOADS_DIR;
}

// extractFileExtension: extrahiert per Whitelist-Regex eine kurze
// alphanumerische Dateiendung aus einem (nicht vertrauenswürdigen)
// Dateinamen. Kein Treffer (z. B. kein Punkt, zu lange/exotische Endung) →
// leerer String, kein Fehler.
function extractFileExtension(filename){
  const m = String(filename || '').match(/\.([a-zA-Z0-9]{1,8})$/);
  return m ? m[1].toLowerCase() : '';
}

// serializeFileRow: wandelt eine project_files-Zeile (ggf. mit JOIN-Feldern)
// in die öffentliche Repräsentation für GET /project-files um.
function serializeFileRow(row){
  return {
    id: row.id,
    projectNr: row.project_nr,
    projectName: row.project_name,
    originalFilename: row.original_filename,
    contentType: row.content_type,
    sizeBytes: row.size_bytes,
    status: row.status,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    uploadedBy: row.uploadedBy,
    // Uploader-ID mitgeben: Der Client kann den "Löschen"-Knopf sonst nicht
    // an dieselbe Regel binden, die der Server durchsetzt (Uploader, solange
    // die Datei offen ist — oder Admin).
    uploadedByUserId: row.uploaded_by_user_id,
  };
}

// Route: Liste aller aktiven Benutzer (ohne Passwortfelder, ohne inaktive
// Konten) — für die Prüfer-Auswahl beim Datei-Upload. Bewusst für JEDEN
// eingeloggten Benutzer zugänglich (requireAuth, nicht requireRole('admin')),
// da jeder Uploader Prüfer auswählen muss; getrennt von GET /admin/users
// (admin-only, zeigt zusätzlich inaktive Konten + mehr Felder).
app.get('/users', requireAuth, (req, res) => {
  try {
    const rows = db.prepare('SELECT id, username, display_name, email, person_id, department, initials FROM users WHERE active = 1 ORDER BY display_name, username').all();
    const rolesByUser = loadUserRolesByUserId();
    res.json({ ok: true, users: rows.map(r => ({
      id: r.id, username: r.username, displayName: r.display_name, email: r.email,
      personId: r.person_id, department: r.department, initials: r.initials,
      roles: rolesByUser[r.id] || [],
    })) });
  } catch(err){
    console.error('Benutzerliste fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Abwesenheit für einen Benutzer anlegen. Bewusst NICHT admin-only und
// bewusst auch für FREMDE Konten erlaubt: In der Praxis trägt die
// Projektleitung Urlaube ihres Teams mit ein. Wer den Eintrag angelegt hat,
// wird in created_by festgehalten — das Löschen ist dadurch nachvollziehbar
// eingeschränkt (siehe DELETE-Route unten).
app.post('/users/:id/absences', requireAuth, (req, res) => {
  const id = Number(req.params.id);
  const user = db.prepare('SELECT id FROM users WHERE id = ?').get(id);
  if(!user) return res.status(404).json({ ok: false, error: 'Benutzer nicht gefunden.' });
  const { start, end, type, note } = req.body || {};
  if(!start || !String(start).trim() || !end || !String(end).trim()){
    return res.status(400).json({ ok: false, error: 'Start- und Enddatum erforderlich.' });
  }
  try {
    const absenceId = crypto.randomUUID();
    db.prepare('INSERT INTO user_absences (user_id, id, start, end, type, note, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)')
      .run(id, absenceId, String(start), String(end), String(type || ''), String(note || ''), req.user.id);
    res.json({ ok: true, absence: { id: absenceId, start: String(start), end: String(end), type: String(type || ''), note: String(note || '') } });
  } catch(err){
    console.error('Abwesenheit anlegen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Abwesenheit löschen (idempotent — auch ok, wenn nichts gelöscht wurde).
// Erlaubt für: die betroffene Person selbst, wer den Eintrag angelegt hat, und
// Administratoren. Ohne diese Prüfung konnte jedes angemeldete Konto fremde
// Urlaube entfernen und damit unbemerkt die Ressourcenplanung verfälschen.
app.delete('/users/:id/absences/:absenceId', requireAuth, (req, res) => {
  try {
    const targetUserId = Number(req.params.id);
    const row = db.prepare('SELECT created_by FROM user_absences WHERE user_id = ? AND id = ?')
      .get(targetUserId, req.params.absenceId);
    if(row){
      const istAdmin = req.user.role === 'admin';
      const istBetroffener = req.user.id === targetUserId;
      const istErsteller = row.created_by && row.created_by === req.user.id;
      if(!istAdmin && !istBetroffener && !istErsteller){
        return res.status(403).json({
          ok: false,
          error: 'Diese Abwesenheit wurde von einer anderen Person eingetragen. Nur sie selbst, die betroffene Person oder ein Administrator kann sie entfernen.'
        });
      }
    }
    db.prepare('DELETE FROM user_absences WHERE user_id = ? AND id = ?').run(targetUserId, req.params.absenceId);
    res.json({ ok: true });
  } catch(err){
    console.error('Abwesenheit löschen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Datei-Upload für ein Projekt. Roher Binär-Body (wie /import), Metadaten
// als Query-Parameter, da der Body ausschließlich die Datei selbst enthält.
app.post('/project-files', requireAuth, express.raw({ type: '*/*', limit: '200mb' }), (req, res) => {
  try {
    const projectNr = String(req.query.projectNr || '');
    if(!projectNr) return res.status(400).json({ ok: false, error: 'Projekt hat keine Projektnummer.' });
    const projectName = String(req.query.projectName || '');

    const buf = req.body;
    if(!Buffer.isBuffer(buf) || buf.length === 0){
      return res.status(400).json({ ok: false, error: 'Keine Datei empfangen' });
    }

    let filename = '';
    try { filename = decodeURIComponent(String(req.query.filename || '')); }
    catch(e){ filename = String(req.query.filename || ''); }
    const ext = extractFileExtension(filename);

    // Reviewer-Parameter: Komma-getrennte IDs -> Zahlen -> nur existierende,
    // aktive Benutzer -> Uploader (kein Self-Review) entfernen.
    const rawReviewerIds = String(req.query.reviewers || '')
      .split(',')
      .map(s => Number(s.trim()))
      .filter(n => Number.isInteger(n) && n > 0);
    const activeUsers = db.prepare('SELECT id FROM users WHERE active = 1').all().map(r => r.id);
    const activeSet = new Set(activeUsers);
    const reviewerIds = Array.from(new Set(rawReviewerIds))
      .filter(id => activeSet.has(id) && id !== req.user.id);
    if(reviewerIds.length === 0){
      return res.status(400).json({ ok: false, error: 'Mindestens ein Prüfer erforderlich.' });
    }

    const id = crypto.randomUUID();
    const storedName = id + (ext ? '.' + ext : '');
    const dir = uploadDirFor();
    fs.writeFileSync(path.join(dir, storedName), buf);

    const contentType = req.headers['content-type'] || 'application/octet-stream';
    const now = new Date().toISOString();

    db.exec('BEGIN');
    try {
      db.prepare(`INSERT INTO project_files
          (id, project_nr, project_name, original_filename, stored_name, content_type, size_bytes, uploaded_by_user_id, status, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?, ?)`)
        .run(id, projectNr, projectName, filename || storedName, storedName, contentType, buf.length, req.user.id, now, now);
      const insReviewer = db.prepare(`INSERT INTO project_file_reviewers (file_id, user_id, decision, decided_at, note) VALUES (?, ?, 'pending', '', '')`);
      reviewerIds.forEach(uid => insReviewer.run(id, uid));
      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      // Bereits geschriebene Datei bei Fehler wieder entfernen (best effort).
      fs.unlink(path.join(dir, storedName), () => {});
      throw err;
    }

    console.log('Datei hochgeladen:', projectNr, '-', filename || storedName);
    res.json({
      ok: true,
      file: {
        id, projectNr, projectName,
        originalFilename: filename || storedName,
        contentType, sizeBytes: buf.length, status: 'pending', createdAt: now,
        uploadedBy: req.user.displayName,
      }
    });
  } catch(err){
    console.error('Datei-Upload fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Liste aller Dateien eines Projekts (project_nr), inkl. Uploader-Name,
// Prüferstatus je Prüfer und Kommentaranzahl.
app.get('/project-files', requireAuth, (req, res) => {
  try {
    const projectNr = String(req.query.projectNr || '');
    const rows = db.prepare(`
      SELECT f.*, COALESCE(NULLIF(u.display_name, ''), u.username) AS uploadedBy
      FROM project_files f
      LEFT JOIN users u ON u.id = f.uploaded_by_user_id
      WHERE f.project_nr = ?
      ORDER BY f.created_at DESC
    `).all(projectNr);
    const reviewerStmt = db.prepare(`
      SELECT r.user_id AS userId, COALESCE(NULLIF(u.display_name, ''), u.username) AS displayName, r.decision AS decision
      FROM project_file_reviewers r
      LEFT JOIN users u ON u.id = r.user_id
      WHERE r.file_id = ?
    `);
    const commentCountStmt = db.prepare('SELECT COUNT(*) AS c FROM project_file_comments WHERE file_id = ?');
    const files = rows.map(row => {
      const out = serializeFileRow(row);
      out.reviewers = reviewerStmt.all(row.id);
      out.commentCount = commentCountStmt.get(row.id).c;
      return out;
    });
    res.json({ ok: true, files });
  } catch(err){
    console.error('Dateiliste fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Datei-Download. Der Dateisystempfad besteht ausschließlich aus der
// (server-seitig erzeugten) stored_name-UUID — original_filename fließt NUR
// in den Content-Disposition-Header ein (zweites res.download()-Argument),
// niemals in den Pfad.
app.get('/project-files/:id/download', requireAuth, (req, res) => {
  try {
    const row = db.prepare('SELECT * FROM project_files WHERE id = ?').get(req.params.id);
    if(!row) return res.status(404).json({ ok: false, error: 'Datei nicht gefunden.' });
    const filePath = path.join(UPLOADS_DIR, row.stored_name);
    res.download(filePath, row.original_filename, (err) => {
      if(err && !res.headersSent){
        console.error('Datei-Download fehlgeschlagen:', err);
        res.status(404).json({ ok: false, error: 'Datei nicht gefunden.' });
      }
    });
  } catch(err){
    console.error('Datei-Download fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Freigabe-Entscheidung eines Prüfers. Nur möglich, solange der
// Gesamtstatus der Datei noch nicht terminal (approved/rejected) ist, und nur
// für Benutzer, die tatsächlich als Prüfer für diese Datei eingetragen sind.
// Neuberechnung des Gesamtstatus: eine Ablehnung blockiert sofort; erst wenn
// ALLE Prüfer zugestimmt haben, gilt die Datei als freigegeben.
app.post('/project-files/:id/review', requireAuth, (req, res) => {
  try {
    const fileRow = db.prepare('SELECT * FROM project_files WHERE id = ?').get(req.params.id);
    if(!fileRow) return res.status(404).json({ ok: false, error: 'Datei nicht gefunden.' });

    const { decision, note } = req.body || {};
    if(decision !== 'approved' && decision !== 'rejected'){
      return res.status(400).json({ ok: false, error: 'Ungültige Entscheidung.' });
    }

    const reviewerRow = db.prepare('SELECT * FROM project_file_reviewers WHERE file_id = ? AND user_id = ?').get(fileRow.id, req.user.id);
    if(!reviewerRow) return res.status(403).json({ ok: false, error: 'Sie sind für diese Datei nicht als Prüfer eingetragen.' });

    if(fileRow.status === 'approved' || fileRow.status === 'rejected'){
      return res.status(409).json({ ok: false, error: 'Über diese Datei wurde bereits abschließend entschieden.' });
    }

    const now = new Date().toISOString();
    db.prepare('UPDATE project_file_reviewers SET decision = ?, decided_at = ?, note = ? WHERE file_id = ? AND user_id = ?')
      .run(decision, now, String(note || ''), fileRow.id, req.user.id);

    const allDecisions = db.prepare('SELECT decision FROM project_file_reviewers WHERE file_id = ?').all(fileRow.id).map(r => r.decision);
    let newStatus = 'pending';
    if(allDecisions.some(d => d === 'rejected')) newStatus = 'rejected';
    else if(allDecisions.length > 0 && allDecisions.every(d => d === 'approved')) newStatus = 'approved';

    db.prepare('UPDATE project_files SET status = ?, updated_at = ? WHERE id = ?').run(newStatus, now, fileRow.id);
    console.log('Datei-Freigabe:', fileRow.id, '-', req.user.username, '->', decision, '(Gesamtstatus:', newStatus + ')');
    res.json({ ok: true, status: newStatus });
  } catch(err){
    console.error('Freigabe-Entscheidung fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Kommentare einer Datei lesen (chronologisch aufsteigend).
app.get('/project-files/:id/comments', requireAuth, (req, res) => {
  try {
    const rows = db.prepare(`
      SELECT c.id AS id, c.user_id AS userId, COALESCE(NULLIF(u.display_name, ''), u.username) AS displayName,
             c.body AS body, c.created_at AS createdAt
      FROM project_file_comments c
      LEFT JOIN users u ON u.id = c.user_id
      WHERE c.file_id = ?
      ORDER BY c.created_at ASC
    `).all(req.params.id);
    res.json({ ok: true, comments: rows });
  } catch(err){
    console.error('Kommentare laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Kommentar zu einer Datei hinzufügen. Jeder eingeloggte Benutzer darf
// kommentieren, nicht nur die eingetragenen Prüfer.
app.post('/project-files/:id/comments', requireAuth, (req, res) => {
  try {
    const fileRow = db.prepare('SELECT id FROM project_files WHERE id = ?').get(req.params.id);
    if(!fileRow) return res.status(404).json({ ok: false, error: 'Datei nicht gefunden.' });
    const body = String((req.body || {}).body || '').trim();
    if(!body) return res.status(400).json({ ok: false, error: 'Kommentartext erforderlich.' });
    const id = crypto.randomUUID();
    const now = new Date().toISOString();
    db.prepare('INSERT INTO project_file_comments (id, file_id, user_id, body, created_at) VALUES (?, ?, ?, ?, ?)')
      .run(id, fileRow.id, req.user.id, body, now);
    res.json({ ok: true, comment: { id, userId: req.user.id, displayName: req.user.displayName, body, createdAt: now } });
  } catch(err){
    console.error('Kommentar anlegen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Datei löschen. Erlaubt für den Uploader, solange die Datei noch
// 'pending' ist, oder jederzeit für Admins. Löscht zuerst die DB-Zeile
// (inkl. abhängiger Prüfer-/Kommentar-Zeilen — explizit statt sich auf eine
// FK-CASCADE zu verlassen, da project_file_reviewers/project_file_comments
// keine deklarierten FOREIGN KEY-Constraints auf project_files haben), dann
// best-effort die physische Datei.
app.delete('/project-files/:id', requireAuth, (req, res) => {
  try {
    const row = db.prepare('SELECT * FROM project_files WHERE id = ?').get(req.params.id);
    if(!row) return res.status(404).json({ ok: false, error: 'Datei nicht gefunden.' });

    const isOwnerAndPending = (req.user.id === row.uploaded_by_user_id) && row.status === 'pending';
    const isAdmin = req.user.role === 'admin';
    if(!isOwnerAndPending && !isAdmin){
      return res.status(403).json({ ok: false, error: 'Keine Berechtigung, diese Datei zu löschen.' });
    }

    db.exec('BEGIN');
    try {
      db.prepare('DELETE FROM project_file_comments WHERE file_id = ?').run(row.id);
      db.prepare('DELETE FROM project_file_reviewers WHERE file_id = ?').run(row.id);
      db.prepare('DELETE FROM project_files WHERE id = ?').run(row.id);
      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      throw err;
    }
    fs.unlink(path.join(UPLOADS_DIR, row.stored_name), (err) => {
      if(err) console.error('Physische Datei konnte nicht gelöscht werden:', err);
    });
    console.log('Datei gelöscht:', row.id, '-', row.original_filename);
    res.json({ ok: true });
  } catch(err){
    console.error('Datei löschen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// ---------- Protokolle (Besprechungsprotokolle je Projekt) ----------
//
// Verknüpft über project_nr (siehe Tabellendefinition oben), nicht über
// week_label/project_id. Speichermuster: eigene Routen, aber "ganzes Objekt
// ersetzen" wie saveWeekRelational()/deleteWeekData() (Zeilen löschen, neu
// einfügen, in einer Transaktion) statt vieler kleiner Einzel-Feld-Routen —
// Punkte und Teilnehmer werden ohnehin gemeinsam in einer Sitzung bearbeitet.

// loadProtokollDetail: baut ein volles Protokoll (Kopfdaten + Teilnehmer +
// Punkte) für die Antwort von GET/POST/PUT zusammen. Parameter: id.
// Rückgabe: Objekt oder null, wenn nicht gefunden.
function loadProtokollDetail(id){
  const row = db.prepare(`SELECT p.*, u.display_name AS verfasser_name
    FROM project_protokolle p LEFT JOIN users u ON u.id = p.verfasser_user_id
    WHERE p.id = ?`).get(id);
  if(!row) return null;
  const teilnehmer = db.prepare('SELECT * FROM protokoll_teilnehmer WHERE protokoll_id = ? ORDER BY array_order').all(row.id)
    .map(t => ({ id: t.id, name: t.name, firma: t.firma, anwesend: !!t.anwesend }));
  const punkte = db.prepare('SELECT * FROM protokoll_punkte WHERE protokoll_id = ? ORDER BY array_order').all(row.id)
    .map(p => ({ id: p.id, nummer: p.nummer, gruppe: p.gruppe, neu: !!p.neu, thema: p.thema, text: p.text,
      zustaendig: p.zustaendig, termin: p.termin, status: p.status || 'offen', erstelltAm: p.erstellt_am || '' }));
  return {
    id: row.id, projectNr: row.project_nr, projectName: row.project_name, nummer: row.nummer,
    datum: row.datum, betreff: row.betreff, ort: row.ort, verfasserName: row.verfasser_name || '',
    weekLabel: row.week_label, quelleDateiname: row.quelle_dateiname || '',
    createdAt: row.created_at, updatedAt: row.updated_at,
    teilnehmer, punkte
  };
}

// Route: Liste aller Protokolle eines Projekts (Zusammenfassung je Protokoll
// für die Listenansicht — offene/erledigte Punkte, Teilnehmerzahl —, nicht
// die vollen Punkte/Teilnehmer, das liefert erst GET /:id).
app.get('/project-protokolle', requireAuth, (req, res) => {
  try {
    const projectNr = String(req.query.projectNr || '');
    if(!projectNr) return res.status(400).json({ ok: false, error: 'Projekt hat keine Projektnummer.' });
    const rows = db.prepare(`SELECT p.*, u.display_name AS verfasser_name
      FROM project_protokolle p LEFT JOIN users u ON u.id = p.verfasser_user_id
      WHERE p.project_nr = ? ORDER BY p.nummer DESC`).all(projectNr);
    const protokolle = rows.map(row => {
      const punkte = db.prepare('SELECT status FROM protokoll_punkte WHERE protokoll_id = ?').all(row.id);
      const teilnehmerCount = db.prepare('SELECT COUNT(*) AS c FROM protokoll_teilnehmer WHERE protokoll_id = ?').get(row.id).c;
      const offenCount = punkte.filter(p => p.status === 'offen').length;
      return {
        id: row.id, nummer: row.nummer, datum: row.datum, betreff: row.betreff, ort: row.ort,
        verfasserName: row.verfasser_name || '', createdAt: row.created_at,
        offenCount, erledigtCount: punkte.length - offenCount, teilnehmerCount
      };
    });
    res.json({ ok: true, protokolle });
  } catch(err){
    console.error('Protokolle laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

app.get('/project-protokolle/:id', requireAuth, (req, res) => {
  try {
    const protokoll = loadProtokollDetail(req.params.id);
    if(!protokoll) return res.status(404).json({ ok: false, error: 'Protokoll nicht gefunden.' });
    res.json({ ok: true, protokoll });
  } catch(err){
    console.error('Protokoll laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: neues Protokoll anlegen. nummer wird serverseitig vergeben
// (MAX(nummer)+1 je project_nr, sonst 1); Teilnehmer kommen vorbelegt vom
// Client (siehe js/plan/plan-protokoll.js); offene Punkte des zuletzt
// angelegten Protokolls desselben Projekts werden KOPIERT (neue id, gleiche
// stamm_id/nummer, neu=0) — das "Fortschreiben". Im allerersten Protokoll
// eines Projekts gibt es nichts zu kopieren, dort sind zwangsläufig alle
// später hinzugefügten Punkte neu.
// legeProtokollAn: die gemeinsame Anlege-Logik für ein neues Protokoll —
// genutzt sowohl von POST /project-protokolle (manuelles Anlegen) als auch
// von POST /project-protokolle/import (Datei-Import, siehe unten). Beide
// Wege sollen ein Protokoll auf exakt dieselbe Art in die Sitzungsfolge
// einreihen: nummer fortlaufend je project_nr, offene Punkte des zuletzt
// angelegten Protokolls desselben Projekts kopiert (das "Fortschreiben",
// siehe CLAUDE.md "### Protokolle"). Der Import hängt danach zusätzlich
// erkannte Punkte an (`zusatzPunkte`) — beim manuellen Anlegen ist dieses
// Array einfach leer.
// Parameter: { projectNr, projectName, weekLabel, datum, betreff, ort,
// verfasserUserId, teilnehmer, zusatzPunkte, quelleDateiname,
// quelleStoredName } — die letzten beiden bleiben leer für ein normal
// angelegtes Protokoll. Läuft in einer eigenen Transaktion. Rückgabe: die
// neue Protokoll-`id`. Wirft bei einem Fehler (Aufrufer fangen und in eine
// HTTP-Antwort übersetzen).
// VALID_PROTOKOLL_PUNKT_STATUS: der Status eines Punkts — 'offen' (läuft
// weiter, wird fortgeschrieben), 'erledigt' (abgeschlossen) oder 'info'
// (rein informativ, keine eigene Handlung nötig — z. B. eine bereits
// zur Kenntnis genommene Mitteilung). Nur 'offen' wird beim Anlegen des
// nächsten Protokolls fortgeschrieben (legeProtokollAn()); sowohl
// 'erledigt' als auch 'info' laufen nicht weiter mit, aus demselben Grund:
// beides ist nichts mehr, worauf jemand noch reagieren muss.
const VALID_PROTOKOLL_PUNKT_STATUS = new Set(['offen', 'erledigt', 'info']);
// PROTOKOLL_PUNKT_MARKER: erkennt einen Zeilenanfang wie "1.1", "TOP 3",
// "Punkt 2.4" oder "1." als Beginn eines neuen Punkts; der Rest der Zeile
// wird dessen Thema. Ein Regex-Satz für mehrere gängige Schreibweisen, kein
// Anspruch auf Vollständigkeit — siehe erkennePunkteAusText().
const PROTOKOLL_PUNKT_MARKER = /^\s*(?:top|punkt)?\s*(\d+(?:\.\d+)*)[.\):]?\s+(.+)$/i;
// PROTOKOLL_LABEL_MUSTER: Label-Muster, die INNERHALB eines erkannten
// Punkttexts nach Zuständig/Termin suchen — nur bei eindeutigem Treffer
// übernommen (siehe erkennePunkteAusText()).
// Nicht-gierig bis zum nächsten Satzende, Zeilenende ODER dem nächsten
// bekannten Label — sonst reißt z. B. "Zuständig: Firma Muster. Termin: …"
// (beides auf einer Zeile, ohne Komma dazwischen) den Termin-Teil mit in den
// Zuständig-Treffer hinein. Ein Punkt gilt nur als Satzende, wenn ihm keine
// Ziffer folgt — sonst würde ein Datum wie "15.10.2026" beim ersten Punkt
// abgeschnitten.
const PROTOKOLL_LABEL_STOP = '(?=[,;]|\\.(?!\\d)|$|\\s*(?:zust[äa]ndig|verantwortlich|termin|frist)\\s*:)';
const PROTOKOLL_LABEL_MUSTER = [
  { feld: 'zustaendig', regex: new RegExp('(?:zust[äa]ndig|verantwortlich)\\s*:\\s*([^,;]+?)' + PROTOKOLL_LABEL_STOP, 'i') },
  { feld: 'termin', regex: new RegExp('(?:termin|frist)\\s*:\\s*([^,;]+?)' + PROTOKOLL_LABEL_STOP, 'i') }
];

// erkennePunkteAusText: reines String-Processing ohne I/O (kein Netzwerk,
// keine KI) — teilt einen aus PDF/Word extrahierten Freitext heuristisch in
// Protokoll-Punkte auf. Siehe CLAUDE.md "### Protokolle" für die Begründung
// jeder Regel. Erkennt PROTOKOLL_PUNKT_MARKER-Zeilen als neue Punkte, alles
// dazwischen wird deren Text; findet sich darin ein PROTOKOLL_LABEL_MUSTER,
// wird der Wert herausgelöst statt im Fließtext zu bleiben. `gruppe` wird
// bewusst NIE geraten — jeder erkannte Punkt landet in "Ohne Gruppe", der
// Nutzer ordnet in der ohnehin vorhandenen Detailansicht selbst zu. Findet
// sich im ganzen Text kein einziger Marker (keine erkennbare Nummerierung),
// greift ein Fallback: jeder nicht-leere Absatz (durch Leerzeile getrennt)
// wird ein eigener Punkt — liefert immer ein Grundgerüst statt eines leeren
// Protokolls. `nummer` hier ist rein informationell (die im Quelldokument
// gefundene Nummer) — angezeigt wird sie ohnehin nie aus der Datenbank,
// sondern immer frisch aus Gruppe+Reihenfolge errechnet
// (berechneProtokollNummern(), public/js/plan/plan-protokoll.js).
// Parameter: text (String). Rückgabe: Array von
// { nummer, gruppe, thema, text, zustaendig, termin, erledigt }.
function erkennePunkteAusText(text){
  const zeilen = String(text || '').split(/\r?\n/).map(z => z.trim());

  const treffer = [];
  let aktuell = null;
  zeilen.forEach(zeile => {
    if(!zeile) return;
    const m = zeile.match(PROTOKOLL_PUNKT_MARKER);
    if(m){
      if(aktuell) treffer.push(aktuell);
      aktuell = { nummer: m[1], thema: m[2].trim(), textZeilen: [], zustaendig: '', termin: '', erledigt: false };
    } else if(aktuell){
      aktuell.textZeilen.push(zeile);
    }
  });
  if(aktuell) treffer.push(aktuell);

  if(treffer.length === 0){
    return String(text || '').split(/\r?\n\s*\r?\n/)
      .map(a => a.replace(/\s+/g, ' ').trim())
      .filter(Boolean)
      .map(a => ({
        nummer: '', gruppe: '', thema: a.slice(0, 60), text: a.length > 60 ? a : '',
        zustaendig: '', termin: '', erledigt: false
      }));
  }

  return treffer.map(t => {
    // Labels wie "Zuständig:"/"Termin:" stehen bei kurzen Punkten oft in
    // derselben Zeile wie das Thema (ein Word-Absatz je Punkt), nicht in
    // einer eigenen Folgezeile — deshalb wird über Thema UND die
    // nachfolgenden Zeilen gemeinsam gesucht, nicht nur über Letztere.
    let thema = t.thema;
    let restText = t.textZeilen.join(' ').replace(/\s+/g, ' ').trim();
    PROTOKOLL_LABEL_MUSTER.forEach(({ feld, regex }) => {
      let lm = thema.match(regex);
      if(lm){
        t[feld] = lm[1].trim();
        thema = thema.replace(lm[0], '').trim();
        return;
      }
      lm = restText.match(regex);
      if(lm){
        t[feld] = lm[1].trim();
        restText = restText.replace(lm[0], '').trim();
      }
    });
    // Entfernen eines Labels aus der Mitte des Themas kann einen
    // verwaisten Satzpunkt zurücklassen ("…abzuschließen. ." statt
    // "…abzuschließen.") — am Ende auf einen einzigen Punkt einkürzen.
    thema = thema.replace(/[.\s]+$/, m => (m.indexOf('.') !== -1 ? '.' : '')).trim();
    const erledigt = /\berledigt\b|\bgeschlossen\b/i.test(restText) || /\berledigt\b|\bgeschlossen\b/i.test(thema);
    return { nummer: t.nummer, gruppe: '', thema, text: restText,
      zustaendig: t.zustaendig, termin: t.termin, erledigt };
  });
}

// ---------- KI-gestützte Erkennung (ChatGPT/Claude) für den Protokoll-Import ----------
//
// Alternative zu erkennePunkteAusText() oben: statt starrer Regex-Muster
// liest ein Sprachmodell den Text und liefert dieselbe Punkt-Form zurück.
// Bewusst zusätzlich zur Heuristik, nicht statt ihr — siehe CLAUDE.md
// "### Protokolle": ohne hinterlegten Schlüssel oder bei jedem Fehler
// (Netzwerk, falscher Schlüssel, Zeitüberschreitung, unerwartete Antwort)
// fällt der Aufrufer (POST /project-protokolle/import) automatisch auf
// erkennePunkteAusText() zurück — der Import funktioniert immer, auch ohne
// Internetzugang des Servers. Beide Anbieter laufen über das in Node bereits
// global verfügbare fetch() — keine neue npm-Abhängigkeit.

const KI_MAX_TEXT_LAENGE = 40000; // ~10k Tokens; deckelt Kosten/Dauer bei
  // einer versehentlich riesigen Datei, reicht für jedes normale Protokoll.
const KI_TIMEOUT_MS_CLOUD = 45000;
// Ein lokal laufender Server (LM Studio, Ollama) rechnet auf der eigenen
// Maschine, oft ohne oder mit begrenzter GPU-Beschleunigung — eine einzelne
// Antwort kann mehrere Minuten dauern, weit jenseits dessen, was bei einer
// Cloud-API schon ein Hänger wäre. Mit dem kurzen, einheitlichen Timeout
// brach die Anfrage bei Erik vorzeitig ab ("This operation was aborted"),
// obwohl LM Studio nachweislich weiterrechnete und fertig geworden wäre
// ("läuft aber durch"). Ein kurzer, einheitlicher Timeout hätte lokale
// Modelle damit praktisch unbenutzbar gemacht. Ursprünglich 300000 (5 min),
// dann 900000 (15 min) — beide brachen bei Erik immer noch ab, vor allem
// seit der Prompt jetzt für jeden erkannten Punkt echten Fließtext erzeugt
// (also deutlich mehr Tokens generiert als die frühere, kaputte Fassung,
// die oft fast leer blieb): bei ~12.000 generierten Tokens auf seiner
// Hardware lief LM Studio selbst nachweislich sauber bis zum Ende durch
// (vom eigenen "Fertig"-Zustand im LM-Studio-Fenster bestätigt), nur die
// eigene AbortController-Schranke hier griff vorher. Auf 30 Minuten
// angehoben, mit entsprechendem Headroom bei LOKAL_DISPATCHER (unten).
// Der Import zeigt währenddessen den Ladehinweis im Dialog
// (#protokollImportLoading, plan-protokoll.js), das lange Warten ist also
// sichtbar, kein scheinbares Hängenbleiben.
const KI_TIMEOUT_MS_LOKAL = 1800000;

// LOKAL_DISPATCHER: Node's globales fetch() läuft intern über undici, dessen
// eigener Standard-Agent Verbindungen nach headersTimeout/bodyTimeout = 5
// Minuten von sich aus kappt — UNABHÄNGIG vom eigenen AbortController unten.
// Solange KI_TIMEOUT_MS_LOKAL ebenfalls bei 5 Minuten lag, fiel das nicht
// auf: beide Zeitgeber liefen praktisch gleichzeitig ab, und man sah immer
// den eigenen, sauberen "This operation was aborted"-Abbruch. Nach dem
// Anheben auf 15 Minuten griff undicis eigenes 5-Minuten-Limit als Erstes —
// bei Erik reproduzierbar als "fetch failed", obwohl LM Studio selbst
// nachweislich sauber durchlief. Ein eigener Agent mit angehobenem Limit
// ist die einzige Möglichkeit, das zu umgehen: fetch() selbst bietet dafür
// keine Option, nur das (nicht standardisierte, aber von Node übernommene)
// `dispatcher`-Feld in den Request-Optionen. Nur für lokale Ziele
// verwendet — eine Cloud-API bleibt beim kurzen KI_TIMEOUT_MS_CLOUD (45s),
// weit unter undicis Vorgabewert, dort besteht das Problem gar nicht.
const LOKAL_DISPATCHER = new UndiciAgent({
  headersTimeout: KI_TIMEOUT_MS_LOKAL + 10000,
  bodyTimeout: KI_TIMEOUT_MS_LOKAL + 10000
});

// KI_SYSTEM_PROMPT wurde NICHT nach Stil-/Rechtschreibkonvention geglättet,
// sondern bewusst wortgleich zu der Fassung übernommen, die gegen ein
// echtes, mehrstufig nummeriertes Besprechungsprotokoll (PromanX/Phenix-
// Format: "01" Kapitel, "01.0" Unterkapitel, "01.0 01.03" der eigentliche
// Punkt) tatsächlich funktioniert hat — gegen Eriks lokales LM-Studio-Modell
// erwies sich das Ergebnis als erstaunlich instabil gegenüber minimalen
// Formulierungsänderungen: dieselbe Aufgabe lieferte je nach Umformulierung
// zwischen 0 und 11 erkannten Punkten, mal mit vollem Text, mal mit
// durchgehend LEEREM "text" (reproduzierbar bei temperature:0, also keine
// bloße Zufallsstreuung). Ursache war u. a. eine vorangestellte, allgemein
// gehaltene "erfinde nichts, lass im Zweifel leer"-Anweisung, die das
// Modell offenbar dazu brachte, aus Vorsicht auch "text" leer zu lassen,
// obwohl der Inhalt eindeutig im Dokument stand — das genaue Gegenteil des
// gemeldeten Fehlers ("kopiert die Texte nicht mit, erstellt nur die
// Überschriften"). Eine zweite, von Erik angestoßene Runde ergänzte GENAU
// EINE weitere, chirurgisch kurze Anweisung — mehrere eigenständige Punkte
// unter derselben Überschrift einzeln auszugeben, UND mehrere aufeinander-
// folgende, je datierte Textabsätze desselben Punkts (eine im Quelldokument
// selbst geführte Revisionshistorie über mehrere Besprechungen) zu einem
// zusammenhängenden "text" zusammenzuführen, statt sie als eigene, von
// ihrem Thema abgetrennte Punkte auszugeben — und bestand denselben
// Stabilitätstest (reales Dokument, temperature:0): korrekt erkannte
// Punkte mit vollständig zusammengeführtem Text statt verwaister, themenloser
// Fragmente. Jede WEITERE Ergänzung darüber hinaus (z. B. eine explizite
// Teilnehmer-Anleitung) hat in denselben Tests dagegen zuverlässig wieder
// zur Regression geführt — deshalb bleibt "teilnehmer" bewusst ungeleitet,
// das Schema allein genügt dafür. Ein eigener Blindgang dabei: gegen einen
// künstlich verkürzten Testausschnitt OHNE die echte Teilnehmer-/Verteiler-
// Tabelle des Dokuments geriet das Modell beim Versuch, "teilnehmer" trotzdem
// zu befüllen, in eine Wiederholungsschleife (tausendfach dasselbe Namens-
// Fragment, bis das Token-Limit die Antwort mitten im JSON abschnitt) — mit
// der echten Tabelle (wie jeder reale Import sie tatsächlich schickt) trat
// das nicht wieder auf, lief sogar spürbar schneller durch. frequency_penalty/
// presence_penalty unten bleiben trotzdem als zusätzliches Sicherheitsnetz
// gegen genau solche Wiederholungsschleifen bestehen.
const KI_SYSTEM_PROMPT = 'Extrahiere ALLE Besprechungspunkte aus diesem Protokoll-Text, auch wenn es viele sind. '
  + 'Das Dokument ist mehrstufig nummeriert (z.B. "01" Kapitel, "01.0" Unterkapitel, "01.0 01.03" der echte Punkt mit Text). '
  + 'Nur Zeilen mit eigenem Fließtext darunter werden ein Punkt - reine Kapitelüberschriften ohne Text NICHT. '
  + 'Stehen unter einer Überschrift mehrere eigene Punkte, gib jeden einzeln aus. Folgen auf einen Punkt weitere '
  + 'Absätze ohne neue eigene Nummer, gehören sie zu diesem Punkt dazu - auch wenn ein spaeterer Absatz einen '
  + 'anderen Status zeigt, bleibt es EIN Punkt mit dem Status des letzten Absatzes. '
  + '"status" ist offen, erledigt oder info - info wenn der Text den Punkt selbst so kennzeichnet (z.B. das Wort '
  + '"Info" direkt beim Punkt) oder der Punkt rein informativ ohne eigene Handlung ist, erledigt nur bei '
  + 'ausdruecklicher Erledigt-/Geschlossen-Kennzeichnung, sonst offen. '
  + 'Gib fuer jeden Punkt nummer, thema, text, zustaendig, termin, status, gruppe (leer lassen wenn unklar) zurueck. Erfinde nichts.';

// readUserAiKey/writeUserAiKey: Schlüssel+URL liegen PERSÖNLICH je Konto in
// user_ai_keys (siehe CREATE TABLE weiter oben bei den übrigen User-Kind-
// Tabellen), nicht firmenweit in app_settings — jeder Nutzer verwaltet
// seinen eigenen Zugang unter Einstellungen → Mein Konto
// (js/settings/settings.js), genau wie E-Mail und Passwort. Klartext in der
// SQLite-Datei, wie jeder andere Wert dort auch.
function readUserAiKey(userId, provider){
  const row = db.prepare('SELECT api_key, api_url FROM user_ai_keys WHERE user_id = ? AND provider = ?').get(userId, provider);
  return { apiKey: row ? row.api_key : '', apiUrl: row ? row.api_url : '' };
}

function writeUserAiKey(userId, provider, patch){
  const bestehend = readUserAiKey(userId, provider);
  const apiKey = Object.prototype.hasOwnProperty.call(patch, 'apiKey') ? String(patch.apiKey || '') : bestehend.apiKey;
  const apiUrl = Object.prototype.hasOwnProperty.call(patch, 'apiUrl') ? String(patch.apiUrl || '') : bestehend.apiUrl;
  db.prepare(`INSERT INTO user_ai_keys (user_id, provider, api_key, api_url) VALUES (?, ?, ?, ?)
    ON CONFLICT(user_id, provider) DO UPDATE SET api_key = excluded.api_key, api_url = excluded.api_url`)
    .run(userId, provider, apiKey, apiUrl);
}

// istLoopbackHost: gemeinsame Grundlage für istGueltigeAiUrl() (URL-Prüfung
// beim Speichern) und fetchMitTimeout() (Timeout-Wahl beim Aufruf) — beide
// müssen dieselbe Vorstellung von "läuft auf der eigenen Maschine" teilen.
function istLoopbackHost(hostname){
  return hostname === 'localhost' || hostname === '127.0.0.1' || hostname === '::1' || hostname === '[::1]';
}

// istGueltigeAiUrl: https wird grundsätzlich verlangt (der Schlüssel reist
// sonst im Klartext über ein Netz, das nicht die eigene Maschine ist) — mit
// EINER Ausnahme: http auf dem Loopback. Ein lokal laufender Server wie
// LM Studio oder Ollama bietet praktisch nie ein TLS-Zertifikat für
// localhost an; ihn auszuschließen hätte den naheliegendsten Anwendungsfall
// einer eigenen API-URL — ein kostenloses, lokales Modell statt eines
// kostenpflichtigen Cloud-Zugangs — unmöglich gemacht. Der Klartext
// verlässt dabei nie die eigene Maschine, das Risiko besteht also nicht.
// Rückgabe: bool.
function istGueltigeAiUrl(url){
  let parsed;
  try { parsed = new URL(url); } catch(e){ return false; }
  if(parsed.protocol === 'https:') return true;
  return parsed.protocol === 'http:' && istLoopbackHost(parsed.hostname);
}

async function fetchMitTimeout(url, options){
  let istLokal = false;
  try { istLokal = istLoopbackHost(new URL(url).hostname); } catch(e){ /* ungültige URL: Vorgabe bleibt Cloud-Timeout */ }
  const controller = new AbortController();
  const timer = setTimeout(() => controller.abort(), istLokal ? KI_TIMEOUT_MS_LOKAL : KI_TIMEOUT_MS_CLOUD);
  try {
    const fetchOptions = Object.assign({}, options, { signal: controller.signal });
    if(istLokal) fetchOptions.dispatcher = LOKAL_DISPATCHER;
    return await fetch(url, fetchOptions);
  } finally {
    clearTimeout(timer);
  }
}

// normalisierePunktAusKi: eine KI-Antwortzeile in dieselbe Form wie ein
// erkennePunkteAusText()-Punkt bringen — beide Aufrufer (POST-Route,
// legeProtokollAn) müssen nicht wissen, woher ein Punkt stammt.
function normalisierePunktAusKi(p){
  return {
    nummer: String((p && p.nummer) || ''),
    gruppe: String((p && p.gruppe) || ''),
    thema: String((p && p.thema) || ''),
    text: String((p && p.text) || ''),
    zustaendig: String((p && p.zustaendig) || ''),
    termin: String((p && p.termin) || ''),
    status: VALID_PROTOKOLL_PUNKT_STATUS.has(p && p.status) ? p.status : 'offen'
  };
}

// normalisiereTeilnehmerAusKi: Gegenstück zu normalisierePunktAusKi() für
// das zweite KI_PUNKT_SCHEMA-Feld. "anwesend" defaultet — anders als
// "erledigt" oben — auf true, nicht auf false: eine erkannte Person gilt als
// anwesend, außer der Prompt weist das Modell ausdrücklich an, sie als
// entschuldigt/abwesend zu kennzeichnen (dieselbe Grundannahme wie beim
// manuellen "+ Teilnehmer" in der Detailansicht, js/plan/plan-protokoll.js).
function normalisiereTeilnehmerAusKi(t){
  return {
    name: String((t && t.name) || ''),
    firma: String((t && t.firma) || ''),
    anwesend: !(t && t.anwesend === false)
  };
}

const KI_PUNKT_SCHEMA = {
  type: 'object',
  properties: {
    punkte: {
      type: 'array',
      items: {
        type: 'object',
        properties: {
          nummer: { type: 'string' },
          gruppe: { type: 'string' },
          thema: { type: 'string' },
          text: { type: 'string' },
          zustaendig: { type: 'string' },
          termin: { type: 'string' },
          status: { type: 'string', enum: ['offen', 'erledigt', 'info'] }
        },
        required: ['nummer', 'gruppe', 'thema', 'text', 'zustaendig', 'termin', 'status'],
        additionalProperties: false
      }
    },
    teilnehmer: {
      type: 'array',
      items: {
        type: 'object',
        properties: {
          name: { type: 'string' },
          firma: { type: 'string' },
          anwesend: { type: 'boolean' }
        },
        required: ['name', 'firma', 'anwesend'],
        additionalProperties: false
      }
    }
  },
  required: ['punkte', 'teilnehmer'],
  additionalProperties: false
};

const KI_DEFAULT_URL = {
  chatgpt: 'https://api.openai.com/v1/chat/completions',
  claude: 'https://api.anthropic.com/v1/messages'
};

// normalisiereAiUrl: wer eine eigene API-URL hinterlegt, tippt bei einem
// lokalen Server wie LM Studio/Ollama erfahrungsgemäß nur die Basisadresse
// (z. B. "http://localhost:1234"), nicht den vollen Completions-Pfad —
// beide Tools bewerben sich selbst über genau diese kurze Adresse. Ohne
// diese Ergänzung landet die Anfrage dort auf "/" statt auf
// "/v1/chat/completions" (bzw. "/v1/messages" bei Claude): der lokale
// Server antwortet trotzdem mit HTTP 200, aber mit einem Text, der keine
// gültige Chat-Completion ist — reproduzierbar mit Eriks eigenem Fehler
// ("Received request: POST to /" in LM Studios Log). Nur ein FEHLENDER
// oder leerer Pfad wird ergänzt; ein ausdrücklich angegebener Pfad (z. B.
// ein Firmen-Proxy mit eigener Route) bleibt unangetastet.
function normalisiereAiUrl(apiUrl, defaultUrl){
  if(!apiUrl) return defaultUrl;
  try {
    const parsed = new URL(apiUrl);
    if(!parsed.pathname || parsed.pathname === '/'){
      parsed.pathname = new URL(defaultUrl).pathname;
      return parsed.toString();
    }
    return apiUrl;
  } catch(e){
    return apiUrl;
  }
}

async function erkennePunkteMitChatGPT(text, apiKey, apiUrl){
  const zielUrl = normalisiereAiUrl(apiUrl, KI_DEFAULT_URL.chatgpt);
  let istLokal = false;
  try { istLokal = istLoopbackHost(new URL(zielUrl).hostname); } catch(e){ /* ungültige URL: bleibt false */ }

  const body = {
    model: 'gpt-4o-mini',
    messages: [
      { role: 'system', content: KI_SYSTEM_PROMPT },
      { role: 'user', content: text }
    ],
    response_format: { type: 'json_schema', json_schema: { name: 'protokoll_punkte', strict: true, schema: KI_PUNKT_SCHEMA } },
    // Ohne eigenes max_tokens bricht mancher lokale Server (anders als die
    // echte OpenAI-API, die hier großzügig ist) die Antwort bei einem langen
    // Protokoll mit vielen Punkten vorzeitig ab, sobald ein internes
    // Standardlimit erreicht ist — die JSON-Antwort bleibt dabei gültig,
    // enthält aber nur die ersten paar Punkte statt aller.
    max_tokens: 16000,
    // temperature:0 macht die Erkennung reproduzierbar — bei Streuung (dem
    // Standardwert) lieferte ein und dieselbe Anfrage an Eriks lokales
    // Modell je nach Lauf 0 bis 11 erkannte Punkte, was Fehlersuche und
    // Nutzervertrauen gleichermaßen unmöglich macht. Für diese Aufgabe
    // (Fakten aus einem Text ziehen) ist Kreativität ohnehin unerwünscht.
    temperature: 0,
    // Sicherheitsnetz gegen Wiederholungsschleifen: bei einem Testlauf ohne
    // echte Teilnehmerdaten im Text geriet Eriks lokales Modell beim
    // Befüllen von "teilnehmer" in eine Endlosschleife (dasselbe
    // Namens-Fragment tausendfach), bis max_tokens die Antwort mitten im
    // JSON abschnitt — mit echten Teilnehmerdaten (wie jeder reale Import
    // sie liefert) trat das nicht auf, aber diese beiden Parameter machen
    // eine erneute Schleife unabhängig vom Dokumentinhalt unwahrscheinlicher.
    frequency_penalty: 0.4,
    presence_penalty: 0.2
  };
  // Manche lokale Server laden standardmäßig ein Reasoning-Modell (z. B.
  // Qwen3 in LM Studio), das vor jeder Antwort ausführlich "denkt" — für
  // diese Aufgabe direkt gegen ein echtes LM-Studio-Setup gemessen: 260 von
  // 264 Tokens (98,5 %) fürs Denken, 15s statt 0,4s für eine triviale
  // Anfrage; bei einem langen Besprechungsprotokoll mit vielen Punkten
  // reicht das leicht über jeden vertretbaren Timeout hinaus, obwohl das
  // Modell am Ende korrekt "durchläuft". reasoning_effort:'none' schaltet
  // das Denken ab (ebenfalls live verifiziert — mit dem echten System-
  // Prompt und Schema unten korrekt und in Sekunden statt Minuten). Nur
  // für lokale Ziele gesetzt: die echte OpenAI-API für gpt-4o-mini (kein
  // Reasoning-Modell) kennt den Parameter nicht und soll ihn nie sehen.
  if(istLokal) body.reasoning_effort = 'none';

  const res = await fetchMitTimeout(zielUrl, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json', 'Authorization': 'Bearer ' + apiKey },
    body: JSON.stringify(body)
  });
  if(!res.ok){
    const body = await res.text().catch(() => '');
    throw new Error('ChatGPT antwortete mit HTTP ' + res.status + (body ? ': ' + body.slice(0, 200) : ''));
  }
  const data = await res.json();
  const content = data && data.choices && data.choices[0] && data.choices[0].message && data.choices[0].message.content;
  if(!content) throw new Error('ChatGPT lieferte keine Antwort.');
  let parsed;
  try { parsed = JSON.parse(content); } catch(e){ throw new Error('ChatGPT-Antwort war kein gültiges JSON.'); }
  if(!Array.isArray(parsed.punkte)) throw new Error('ChatGPT-Antwort hatte nicht die erwartete Form.');
  return {
    punkte: parsed.punkte.map(normalisierePunktAusKi),
    teilnehmer: Array.isArray(parsed.teilnehmer) ? parsed.teilnehmer.map(normalisiereTeilnehmerAusKi) : []
  };
}

async function erkennePunkteMitClaude(text, apiKey, apiUrl){
  const res = await fetchMitTimeout(normalisiereAiUrl(apiUrl, KI_DEFAULT_URL.claude), {
    method: 'POST',
    headers: { 'Content-Type': 'application/json', 'x-api-key': apiKey, 'anthropic-version': '2023-06-01' },
    body: JSON.stringify({
      model: 'claude-haiku-4-5-20251001',
      // wie beim ChatGPT-Pfad: ein langes Protokoll mit vielen Punkten
      // braucht mehr als das ursprüngliche Minimum, sonst bricht die
      // Antwort vor dem letzten Punkt ab.
      max_tokens: 8192,
      // Wie beim ChatGPT-Pfad: reproduzierbares Ergebnis statt Streuung
      // zwischen Läufen (siehe Begründung bei KI_SYSTEM_PROMPT oben).
      temperature: 0,
      system: KI_SYSTEM_PROMPT,
      messages: [{ role: 'user', content: text }],
      tools: [{
        name: 'protokoll_punkte',
        description: 'Die aus dem Text erkannten Besprechungspunkte.',
        input_schema: KI_PUNKT_SCHEMA
      }],
      tool_choice: { type: 'tool', name: 'protokoll_punkte' }
    })
  });
  if(!res.ok){
    const body = await res.text().catch(() => '');
    throw new Error('Claude antwortete mit HTTP ' + res.status + (body ? ': ' + body.slice(0, 200) : ''));
  }
  const data = await res.json();
  const toolUse = Array.isArray(data.content) ? data.content.find(b => b.type === 'tool_use') : null;
  if(!toolUse || !toolUse.input || !Array.isArray(toolUse.input.punkte)){
    throw new Error('Claude-Antwort hatte nicht die erwartete Form.');
  }
  return {
    punkte: toolUse.input.punkte.map(normalisierePunktAusKi),
    teilnehmer: Array.isArray(toolUse.input.teilnehmer) ? toolUse.input.teilnehmer.map(normalisiereTeilnehmerAusKi) : []
  };
}

async function erkennePunkteMitKI(text, userId, provider){
  const { apiKey, apiUrl } = readUserAiKey(userId, provider);
  if(!apiKey) throw new Error('Kein API-Schlüssel hinterlegt.');
  const gekuerzterText = text.length > KI_MAX_TEXT_LAENGE ? text.slice(0, KI_MAX_TEXT_LAENGE) : text;
  if(provider === 'chatgpt') return erkennePunkteMitChatGPT(gekuerzterText, apiKey, apiUrl);
  if(provider === 'claude') return erkennePunkteMitClaude(gekuerzterText, apiKey, apiUrl);
  throw new Error('Unbekannter Anbieter: ' + provider);
}

function legeProtokollAn(opts){
  const {
    projectNr, projectName, weekLabel, datum, betreff, ort, verfasserUserId,
    teilnehmer, zusatzPunkte, quelleDateiname, quelleStoredName
  } = opts;

  const id = crypto.randomUUID();
  const now = new Date().toISOString();
  let nummer = 1;

  db.exec('BEGIN');
  try {
    const maxRow = db.prepare('SELECT MAX(nummer) AS m FROM project_protokolle WHERE project_nr = ?').get(projectNr);
    nummer = (maxRow && maxRow.m) ? maxRow.m + 1 : 1;

    db.prepare(`INSERT INTO project_protokolle
        (id, project_nr, project_name, nummer, datum, betreff, ort, verfasser_user_id, week_label,
         quelle_dateiname, quelle_stored_name, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
      .run(id, projectNr, projectName, nummer, String(datum), String(betreff || ''), String(ort || ''),
        verfasserUserId, weekLabel, String(quelleDateiname || ''), String(quelleStoredName || ''), now, now);

    const insTeilnehmer = db.prepare(`INSERT INTO protokoll_teilnehmer
      (id, protokoll_id, name, firma, anwesend, array_order) VALUES (?, ?, ?, ?, ?, ?)`);
    (Array.isArray(teilnehmer) ? teilnehmer : []).forEach((t, idx) => {
      const name = String((t && t.name) || '').trim();
      if(!name) return;
      insTeilnehmer.run(crypto.randomUUID(), id, name, String((t && t.firma) || ''), (t && t.anwesend) ? 1 : 0, idx);
    });

    const insPunkt = db.prepare(`INSERT INTO protokoll_punkte
        (id, protokoll_id, stamm_id, nummer, gruppe, neu, thema, text, zustaendig, termin, erledigt, erstellt_am, status, array_order)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
    let order = 0;

    const prevRow = db.prepare(`SELECT id FROM project_protokolle
      WHERE project_nr = ? AND id != ? ORDER BY nummer DESC LIMIT 1`).get(projectNr, id);
    if(prevRow){
      // Nur status='offen' läuft weiter — 'erledigt' UND 'info' sind beide
      // kein offener Punkt mehr, aus demselben Grund (siehe
      // VALID_PROTOKOLL_PUNKT_STATUS oben).
      const offenePunkte = db.prepare(`SELECT * FROM protokoll_punkte
        WHERE protokoll_id = ? AND status = 'offen' ORDER BY array_order`).all(prevRow.id);
      // erstellt_am wandert UNVERÄNDERT mit (wie stamm_id) — ein fortgeschriebener
      // Punkt wurde hier nicht neu erstellt, nur übernommen.
      offenePunkte.forEach(p => {
        insPunkt.run(crypto.randomUUID(), id, p.stamm_id, p.nummer, p.gruppe, 0, p.thema, p.text, p.zustaendig, p.termin, 0,
          p.erstellt_am || '', 'offen', order++);
      });
    }

    // Zusätzlich erkannte/übergebene Punkte (Import) — immer neu=1, frische
    // stamm_id, hinter den fortgeschriebenen Punkten einsortiert. erstellt_am
    // ist das Datum DIESES Protokolls — hier entstehen diese Punkte zum
    // ersten Mal im System, unabhängig davon, ob das importierte Dokument
    // selbst älter ist. status kommt bei der KI-Erkennung direkt vom Modell
    // (normalisierePunktAusKi() hat ihn schon gegen VALID_PROTOKOLL_PUNKT_STATUS
    // geprüft); die Textmuster-Heuristik (erkennePunkteAusText()) kennt
    // weiterhin nur das ältere, boolesche erledigt (kein 'info' — sie liest
    // reinen Regex-Textmustern, keine Bedeutung) — dafür hier der Rückfall
    // auf das alte Verhalten, falls kein gültiger status mitkommt.
    (Array.isArray(zusatzPunkte) ? zusatzPunkte : []).forEach(p => {
      const status = VALID_PROTOKOLL_PUNKT_STATUS.has(p.status) ? p.status : (p.erledigt ? 'erledigt' : 'offen');
      insPunkt.run(crypto.randomUUID(), id, crypto.randomUUID(), String(p.nummer || ''), String(p.gruppe || ''), 1,
        String(p.thema || ''), String(p.text || ''), String(p.zustaendig || ''), String(p.termin || ''),
        status === 'erledigt' ? 1 : 0, String(datum), status, order++);
    });

    db.exec('COMMIT');
  } catch(err){
    db.exec('ROLLBACK');
    throw err;
  }

  console.log('Protokoll angelegt:', projectNr, '- Nr.', nummer);
  return id;
}

app.post('/project-protokolle', requireAuth, (req, res) => {
  try {
    const projectNr = String(req.query.projectNr || '');
    if(!projectNr) return res.status(400).json({ ok: false, error: 'Projekt hat keine Projektnummer.' });
    const { datum, betreff, ort, teilnehmer } = req.body || {};
    if(!datum) return res.status(400).json({ ok: false, error: 'Datum erforderlich.' });

    const id = legeProtokollAn({
      projectNr, projectName: String(req.query.projectName || ''), weekLabel: String(req.query.weekLabel || ''),
      datum, betreff, ort, verfasserUserId: req.user.id, teilnehmer, zusatzPunkte: []
    });
    res.json({ ok: true, protokoll: loadProtokollDetail(id) });
  } catch(err){
    console.error('Protokoll anlegen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: externes Protokoll hochladen und automatisch in Punkte umwandeln.
// Raw-Binär-Body wie POST /project-files. Extrahiert den Text (PDF via
// pdf-parse, Word via mammoth), teilt ihn heuristisch in Punkte auf
// (erkennePunkteAusText()) und legt darüber ein ganz normales Protokoll an
// (legeProtokollAn() — dieselbe Fortschreiben-Logik wie beim manuellen
// Anlegen; die erkannten Punkte kommen zusätzlich dazu, als neu=1). Die
// Original-Datei bleibt unter UPLOADS_DIR gespeichert und über
// GET /project-protokolle/:id/quelldatei abrufbar — Erkennung ist ein
// Entwurf, keine Garantie, siehe CLAUDE.md "### Protokolle".
app.post('/project-protokolle/import', requireAuth, express.raw({ type: '*/*', limit: '50mb' }), async (req, res) => {
  try {
    const projectNr = String(req.query.projectNr || '');
    if(!projectNr) return res.status(400).json({ ok: false, error: 'Projekt hat keine Projektnummer.' });
    if(!req.query.datum) return res.status(400).json({ ok: false, error: 'Datum erforderlich.' });

    const buf = req.body;
    if(!Buffer.isBuffer(buf) || buf.length === 0){
      return res.status(400).json({ ok: false, error: 'Keine Datei empfangen.' });
    }

    let filename = '';
    try { filename = decodeURIComponent(String(req.query.filename || '')); }
    catch(e){ filename = String(req.query.filename || ''); }
    const ext = extractFileExtension(filename);

    let text = '';
    if(ext === 'pdf'){
      text = await extrahierePdfText(buf);
    } else if(ext === 'docx'){
      const result = await mammoth.extractRawText({ buffer: buf });
      text = result.value || '';
    } else {
      return res.status(400).json({ ok: false, error: 'Nur PDF- und Word-Dateien (.pdf, .docx) werden unterstützt.' });
    }

    // Teilnehmer kommen NUR aus der KI-Erkennung (siehe KI_SYSTEM_PROMPT) —
    // erkennePunkteAusText() ist reines Regex und kann keine Teilnehmerliste
    // von Fließtext unterscheiden, deshalb bleibt teilnehmerAusDokument beim
    // Textmuster-Pfad und bei jedem KI-Fehlschlag (Rückfall) leer; die
    // Person trägt sie dann wie schon bisher von Hand in der Detailansicht
    // nach. Bewusst KEIN Merge mit dem letzten Protokoll dieses Projekts
    // (anders als neuesProtokoll()'s Vorbelegung) — der Import soll genau
    // zeigen, was im hochgeladenen Dokument steht, nicht zwei Quellen
    // vermischen.
    let zusatzPunkte, teilnehmerAusDokument = [], erkennungsart = 'textmuster', erkennungshinweis = '';
    const gewuenschterAnbieter = req.query.erkennung;
    if(gewuenschterAnbieter === 'chatgpt' || gewuenschterAnbieter === 'claude'){
      try {
        const ergebnis = await erkennePunkteMitKI(text, req.user.id, gewuenschterAnbieter);
        zusatzPunkte = ergebnis.punkte;
        teilnehmerAusDokument = ergebnis.teilnehmer;
        erkennungsart = gewuenschterAnbieter;
      } catch(err){
        console.warn('KI-Erkennung fehlgeschlagen, Rückfall auf Textmuster:', err.message);
        erkennungshinweis = err.message;
        zusatzPunkte = erkennePunkteAusText(text);
      }
    } else {
      zusatzPunkte = erkennePunkteAusText(text);
    }

    const storedName = crypto.randomUUID() + (ext ? '.' + ext : '');
    fs.writeFileSync(path.join(uploadDirFor(), storedName), buf);

    const id = legeProtokollAn({
      projectNr, projectName: String(req.query.projectName || ''), weekLabel: String(req.query.weekLabel || ''),
      datum: req.query.datum, betreff: req.query.betreff, ort: req.query.ort,
      verfasserUserId: req.user.id, teilnehmer: teilnehmerAusDokument, zusatzPunkte,
      quelleDateiname: filename || storedName, quelleStoredName: storedName
    });

    console.log('Protokoll importiert:', projectNr, '-', zusatzPunkte.length, 'Punkte,', teilnehmerAusDokument.length, 'Teilnehmer erkannt aus', filename, '(' + erkennungsart + ')');
    res.json({ ok: true, protokoll: loadProtokollDetail(id), erkannteAnzahl: zusatzPunkte.length,
      erkannteTeilnehmerAnzahl: teilnehmerAusDokument.length, erkennungsart, erkennungshinweis });
  } catch(err){
    console.error('Protokoll-Import fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Original-Datei eines importierten Protokolls herunterladen —
// Gegenstück zu GET /project-files/:id/download. 404, wenn das Protokoll
// nicht importiert wurde (quelle_stored_name leer, siehe legeProtokollAn()).
app.get('/project-protokolle/:id/quelldatei', requireAuth, (req, res) => {
  try {
    const row = db.prepare('SELECT quelle_dateiname, quelle_stored_name FROM project_protokolle WHERE id = ?').get(req.params.id);
    if(!row || !row.quelle_stored_name){
      return res.status(404).json({ ok: false, error: 'Keine Quelldatei vorhanden.' });
    }
    res.download(path.join(UPLOADS_DIR, row.quelle_stored_name), row.quelle_dateiname || row.quelle_stored_name);
  } catch(err){
    console.error('Quelldatei-Download fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Protokoll speichern — ersetzt Teilnehmer- und Punkte-Zeilen komplett
// (löschen + neu einfügen, wie deleteWeekData()/serializeWeek()). nummer,
// gruppe, thema, text, zustaendig, termin, erledigt sind reine Anzeige-/
// Bearbeitungsfelder und werden immer vom Client übernommen. stamm_id/neu
// dagegen NIE vom Client vertraut: für einen Punkt mit bekannter id werden
// sie aus der bisherigen Zeile übernommen, ein Punkt ohne id ist neu
// (frische stamm_id, neu=1).
app.put('/project-protokolle/:id', requireAuth, (req, res) => {
  try {
    const row = db.prepare('SELECT * FROM project_protokolle WHERE id = ?').get(req.params.id);
    if(!row) return res.status(404).json({ ok: false, error: 'Protokoll nicht gefunden.' });
    const { datum, betreff, ort, teilnehmer, punkte } = req.body || {};
    if(!datum) return res.status(400).json({ ok: false, error: 'Datum erforderlich.' });

    const now = new Date().toISOString();
    db.exec('BEGIN');
    try {
      db.prepare('UPDATE project_protokolle SET datum = ?, betreff = ?, ort = ?, updated_at = ? WHERE id = ?')
        .run(String(datum), String(betreff || ''), String(ort || ''), now, row.id);

      db.prepare('DELETE FROM protokoll_teilnehmer WHERE protokoll_id = ?').run(row.id);
      const insTeilnehmer = db.prepare(`INSERT INTO protokoll_teilnehmer
        (id, protokoll_id, name, firma, anwesend, array_order) VALUES (?, ?, ?, ?, ?, ?)`);
      (Array.isArray(teilnehmer) ? teilnehmer : []).forEach((t, idx) => {
        const name = String((t && t.name) || '').trim();
        if(!name) return;
        insTeilnehmer.run(crypto.randomUUID(), row.id, name, String((t && t.firma) || ''), (t && t.anwesend) ? 1 : 0, idx);
      });

      const bestehende = new Map();
      db.prepare('SELECT id, stamm_id, neu, erstellt_am FROM protokoll_punkte WHERE protokoll_id = ?').all(row.id)
        .forEach(p => bestehende.set(p.id, p));

      db.prepare('DELETE FROM protokoll_punkte WHERE protokoll_id = ?').run(row.id);
      const insPunkt = db.prepare(`INSERT INTO protokoll_punkte
          (id, protokoll_id, stamm_id, nummer, gruppe, neu, thema, text, zustaendig, termin, erledigt, erstellt_am, status, array_order)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
      // erstellt_am ist wie stamm_id/neu serverseitig maßgeblich, nie vom
      // Client übernommen: ein bekannter Punkt behält sein ursprüngliches
      // Datum, ein neu hinzugefügter bekommt das Datum DIESES Protokolls
      // (der gerade gespeicherte `datum`-Wert oben) — der Moment, in dem er
      // zum ersten Mal gespeichert wird. status wird dagegen VOM CLIENT
      // übernommen (offen/erledigt/info, über ein <select> gewählt) — anders
      // als neu/stamm_id/erstellt_am ist das kein Herkunfts-, sondern ein
      // jederzeit frei änderbares Inhaltsfeld, wie thema oder zustaendig.
      // erledigt bleibt als reine Ableitung von status bestehen (siehe
      // migrateAddProtokollPunktStatus()).
      (Array.isArray(punkte) ? punkte : []).forEach((p, idx) => {
        const bekannt = p && p.id ? bestehende.get(p.id) : null;
        const pid = bekannt ? bekannt.id : crypto.randomUUID();
        const stammId = bekannt ? bekannt.stamm_id : crypto.randomUUID();
        const neu = bekannt ? bekannt.neu : 1;
        const erstelltAm = bekannt ? (bekannt.erstellt_am || '') : String(datum);
        const status = VALID_PROTOKOLL_PUNKT_STATUS.has(p && p.status) ? p.status : 'offen';
        insPunkt.run(pid, row.id, stammId, String((p && p.nummer) || ''), String((p && p.gruppe) || ''), neu,
          String((p && p.thema) || ''), String((p && p.text) || ''), String((p && p.zustaendig) || ''),
          String((p && p.termin) || ''), status === 'erledigt' ? 1 : 0, erstelltAm, status, idx);
      });

      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      throw err;
    }

    res.json({ ok: true, protokoll: loadProtokollDetail(row.id) });
  } catch(err){
    console.error('Protokoll speichern fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Protokoll löschen — Auth genügt (kein admin-only), dieselbe
// Berechtigungsstufe wie das Löschen einer Aufgabe/eines Meilensteins.
// Idempotent wie DELETE /users/:id/absences/:absenceId (kein Fehler, wenn
// bereits gelöscht).
app.delete('/project-protokolle/:id', requireAuth, (req, res) => {
  try {
    const row = db.prepare('SELECT id FROM project_protokolle WHERE id = ?').get(req.params.id);
    if(!row) return res.json({ ok: true });
    db.exec('BEGIN');
    try {
      db.prepare('DELETE FROM protokoll_punkte WHERE protokoll_id = ?').run(row.id);
      db.prepare('DELETE FROM protokoll_teilnehmer WHERE protokoll_id = ?').run(row.id);
      db.prepare('DELETE FROM project_protokolle WHERE id = ?').run(row.id);
      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      throw err;
    }
    console.log('Protokoll gelöscht:', row.id);
    res.json({ ok: true });
  } catch(err){
    console.error('Protokoll löschen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// ---------- Aufgaben-Vorlagen je Leistungsphase (lph_templates) ----------

// VALID_LPHS: gültige Leistungsphasen-Schlüssel (muss zu LPHS in
// public/js/globals.js passen). Dient nur der Eingabeprüfung der
// Vorlagen-Routen — die Fortschrittswerte in project_lph werden bewusst NICHT
// gegen diese Liste geprüft (dort sind historisch gewachsene Schlüssel aus
// Altdaten möglich).
const VALID_LPHS = new Set(['LPH 1','LPH 2','LPH 3','LPH 4','LPH 5','LPH 6','LPH 7','LPH 8','LPH 9','W+M']);

// serializeTemplateRow: DB-Zeile -> öffentliche JSON-Form.
function serializeTemplateRow(row){
  return {
    id: row.id,
    lph: row.lph,
    title: row.title,
    offsetDays: row.offset_days,
    durationDays: row.duration_days,
    sortOrder: row.sort_order,
  };
}

// Route: alle Vorlagen lesen (jeder angemeldete Nutzer darf Vorlagen
// einfügen, daher nur requireAuth — nur das Pflegen ist Admin-Sache).
app.get('/lph-templates', requireAuth, (req, res) => {
  try {
    const rows = db.prepare('SELECT * FROM lph_templates ORDER BY lph, sort_order, title').all();
    res.json({ ok: true, templates: rows.map(serializeTemplateRow) });
  } catch(err){
    console.error('Vorlagen laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: neue Vorlagen-Aufgabe anlegen. Admin-only (firmenweite Konfiguration).
app.post('/lph-templates', requireRole('admin'), (req, res) => {
  try {
    const { lph, title, offsetDays, durationDays } = req.body || {};
    if(!VALID_LPHS.has(String(lph))){
      return res.status(400).json({ ok: false, error: 'Unbekannte Leistungsphase.' });
    }
    const cleanTitle = String(title || '').trim();
    if(!cleanTitle) return res.status(400).json({ ok: false, error: 'Titel fehlt.' });
    const offset = Number.isFinite(Number(offsetDays)) ? Math.max(0, Math.trunc(Number(offsetDays))) : 0;
    const duration = Number.isFinite(Number(durationDays)) ? Math.max(1, Math.trunc(Number(durationDays))) : 1;
    // sort_order: ans Ende der jeweiligen Leistungsphase anhängen
    const maxRow = db.prepare('SELECT MAX(sort_order) AS m FROM lph_templates WHERE lph = ?').get(String(lph));
    const sortOrder = (maxRow && Number.isFinite(maxRow.m) ? maxRow.m : -1) + 1;
    const id = crypto.randomUUID();
    db.prepare(`INSERT INTO lph_templates (id, lph, title, offset_days, duration_days, sort_order)
      VALUES (?, ?, ?, ?, ?, ?)`).run(id, String(lph), cleanTitle, offset, duration, sortOrder);
    res.json({ ok: true, template: serializeTemplateRow({
      id, lph: String(lph), title: cleanTitle, offset_days: offset, duration_days: duration, sort_order: sortOrder
    })});
  } catch(err){
    console.error('Vorlage anlegen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Vorlagen-Aufgabe löschen. Admin-only. Idempotent (bereits gelöscht
// ist kein Fehler). Bereits eingefügte TODOs bleiben unberührt (siehe
// Tabellen-Kommentar bei lph_templates).
app.delete('/lph-templates/:id', requireRole('admin'), (req, res) => {
  try {
    db.prepare('DELETE FROM lph_templates WHERE id = ?').run(String(req.params.id));
    res.json({ ok: true });
  } catch(err){
    console.error('Vorlage löschen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// ---------- Honorar-Konfiguration (Anteile je LPH + Stundensatz) ----------

// ---------- Firmenweite Einstellungen (app_settings) ----------
//
// APP_SETTING_DEFS ist die EINE Stelle, an der ein Schlüssel definiert wird:
// Vorgabewert, Typ und Prüfung. readAppSettings() liefert daraus stets ein
// vollständiges Objekt (fehlende oder unbrauchbare Zeilen fallen auf die
// Vorgabe zurück), PUT /app-settings schreibt nur bekannte Schlüssel.
//
// Alle Werte hier waren vorher Konstanten im Code — teils im Server
// (Backups, Sitzungsdauer, Anmeldebremse), teils im Frontend (Aufwands-
// vorlage, Ampel-Schwellen, Balkenskala, Arbeitsstunden je Tag). Sie sind
// nicht deshalb hierher gewandert, weil sie sich oft ändern, sondern weil
// sie die Zahlen ALLER Nutzer verschieben und ihre Pflege damit eine
// Administrationsaufgabe ist, kein Eingriff in den Quelltext.

// DEFAULT_AUFWAND_VORLAGE: firmenweite Gewichtung je Leistungsphase und
// Rolle (0,0–1,0). Sie greift für jedes Projekt, dessen Aufwandsmaske für
// diese Phase/Rolle keinen eigenen Wert trägt — also für die meisten. LPH 9
// steht bewusst auf 0: die Faktoren sind fachlich nicht abgestimmt, und ein
// erfundener Wert flösse unbemerkt in die Auslastung ein.
const DEFAULT_AUFWAND_VORLAGE = {
  'LPH 1': { 'Planung': 0.4, 'Projektleitung': 0.2, 'Bauüberwachung': 0 },
  'LPH 2': { 'Planung': 0.4, 'Projektleitung': 0.2, 'Bauüberwachung': 0 },
  'LPH 3': { 'Planung': 1.0, 'Projektleitung': 0.3, 'Bauüberwachung': 0 },
  'LPH 4': { 'Planung': 0.0, 'Projektleitung': 0.3, 'Bauüberwachung': 0 },
  'LPH 5': { 'Planung': 1.0, 'Projektleitung': 0.4, 'Bauüberwachung': 0 },
  'LPH 6': { 'Planung': 0.2, 'Projektleitung': 0.7, 'Bauüberwachung': 0.3 },
  'LPH 7': { 'Planung': 0.1, 'Projektleitung': 0.3, 'Bauüberwachung': 0.5 },
  'LPH 8': { 'Planung': 0,   'Projektleitung': 0.2, 'Bauüberwachung': 1.0 },
  'LPH 9': { 'Planung': 0,   'Projektleitung': 0,   'Bauüberwachung': 0 }
};

// num / int: Prüf-Helfer für APP_SETTING_DEFS. Beide liefern null, wenn der
// Wert unbrauchbar ist — readAppSettings() nimmt dann die Vorgabe, und
// PUT /app-settings antwortet mit 400.
function settingNumber(min, max){
  return v => {
    const n = Number(v);
    return Number.isFinite(n) && n >= min && n <= max ? n : null;
  };
}
function settingInt(min, max){
  return v => {
    const n = Number(v);
    return Number.isInteger(n) && n >= min && n <= max ? n : null;
  };
}

const APP_SETTING_DEFS = {
  // Honorar/Terminplan
  stundensatz:            { def: 135,  parse: settingNumber(1, 100000),
                            label: 'Stundensatz (€)' },
  arbeitsstundenProTag:   { def: 8,    parse: settingNumber(1, 24),
                            label: 'Arbeitsstunden je Person und Werktag' },
  // Auslastung
  vollzeitStundenProWoche:{ def: 40,   parse: settingNumber(1, 80),
                            label: 'Wochenstunden einer vollen Stelle' },
  auslastungSchwelleGelb: { def: 0.75, parse: settingNumber(0.05, 5),
                            label: 'Ampel gelb ab' },
  auslastungSchwelleRot:  { def: 1.0,  parse: settingNumber(0.05, 5),
                            label: 'Ampel rot ab' },
  aufwandVorlage:         { def: DEFAULT_AUFWAND_VORLAGE, json: true, parse: parseAufwandVorlage,
                            label: 'Aufwand je Leistungsphase und Rolle' },
  // Die Auslastung rechnet in STUNDEN: offener Honorarwert der amtierenden
  // Phase geteilt durch den Stundensatz, verteilt auf die verbleibenden
  // Wochen. Projekte ohne hinterlegtes Honorar — im Bestand die Mehrheit —
  // brauchen dafür einen Ersatz: Ihre Größe wird als Zahl zwischen 0 und 1
  // am Projekt gepflegt und mit diesem Referenzhonorar multipliziert. 1,0
  // entspricht also einem Projekt in Höhe des Referenzwerts.
  referenzHonorar:        { def: 250000, parse: settingNumber(1000, 100000000),
                            label: 'Referenzhonorar für die Projektgröße' },
  // Verteilzeitraum: Über wie viele Wochen die offenen Stunden verteilt
  // werden, solange das Projekt keinen Terminplan hat, aus dem sich ein
  // Ende ergibt. Ohne einen solchen Horizont wäre die Wochenlast nicht
  // bestimmt — dieselben Reststunden bedeuten über vier Wochen etwas ganz
  // anderes als über vierzig.
  verteilzeitraumWochen:  { def: 12,   parse: settingInt(1, 260),
                            label: 'Verteilzeitraum ohne Terminplan (Wochen)' },
  // Sitzungen
  autoLockAktiv:          { def: 1,    parse: settingInt(0, 1),
                            label: 'Ältere Sitzungen automatisch sperren' },
  // Feiertage: das Bundesland bestimmt, welche gesetzlichen Feiertage
  // vorgeschlagen werden (holidaysForYear()). Gemerkt, damit es nicht bei
  // jedem Jahr neu gewählt werden muss.
  feiertagBundesland:     { def: 'NW', parse: parseBundesland,
                            label: 'Bundesland für die Feiertage' },
  // Betrieb
  maxBackups:             { def: DEFAULT_MAX_BACKUPS, parse: settingInt(1, 500),
                            label: 'Aufbewahrte automatische Backups' },
  sessionTage:            { def: DEFAULT_SESSION_TAGE, parse: settingInt(1, 365),
                            label: 'Gültigkeit einer Anmeldung (Tage)' },
  loginMaxVersuche:       { def: DEFAULT_LOGIN_MAX_ATTEMPTS, parse: settingInt(1, 100),
                            label: 'Fehlversuche bis zur Anmeldesperre' },
  loginSperreMinuten:     { def: DEFAULT_LOGIN_BLOCK_MINUTEN, parse: settingInt(1, 1440),
                            label: 'Dauer der Anmeldesperre (Minuten)' },
};

// parseAufwandVorlage: prüft die Aufwands-Matrix. Erlaubt sind nur bekannte
// Leistungsphasen und Rollen, Werte von 0 bis 1. Nicht genannte Phasen oder
// Rollen fehlen anschließend schlicht — das Frontend behandelt eine fehlende
// Angabe als Faktor 1,0 (voller Aufwand), damit Auslastung nicht
// stillschweigend verschwindet. Rückgabe: geprüftes Objekt oder null.
function parseAufwandVorlage(value){
  let obj = value;
  if(typeof obj === 'string'){
    try { obj = JSON.parse(obj); } catch(e){ return null; }
  }
  if(!obj || typeof obj !== 'object' || Array.isArray(obj)) return null;
  const out = {};
  for(const [lph, roles] of Object.entries(obj)){
    if(!VALID_LPHS.has(lph)) return null;
    if(!roles || typeof roles !== 'object' || Array.isArray(roles)) return null;
    const cleanRoles = {};
    for(const [role, v] of Object.entries(roles)){
      if(!VALID_DISCIPLINE_ROLES.has(role)) return null;
      const n = Number(v);
      if(!Number.isFinite(n) || n < 0 || n > 1) return null;
      cleanRoles[role] = n;
    }
    out[lph] = cleanRoles;
  }
  return out;
}

// parseBundesland: prüft ein Bundesland-Kürzel gegen GERMAN_STATES (weiter
// unten definiert; die Prüfung läuft erst zur Anfragezeit).
// Rückgabe: Kürzel oder null.
function parseBundesland(value){
  const v = String(value || '').toUpperCase();
  return GERMAN_STATES[v] ? v : null;
}

// readAppSettings: liefert ALLE firmenweiten Einstellungen als fertiges
// Objekt. Eine fehlende Zeile ist der Normalfall (die Vorgabe gilt); eine
// unbrauchbare Zeile — etwa aus einem älteren Stand — fällt ebenfalls auf die
// Vorgabe zurück, statt die Rechnung zu vergiften.
// Keine Parameter. Rückgabe: Objekt über alle Schlüssel von APP_SETTING_DEFS.
function readAppSettings(){
  const rows = db.prepare('SELECT key, value FROM app_settings').all();
  const stored = {};
  rows.forEach(r => { stored[r.key] = r.value; });

  const out = {};
  for(const [key, def] of Object.entries(APP_SETTING_DEFS)){
    if(!Object.prototype.hasOwnProperty.call(stored, key)){ out[key] = def.def; continue; }
    const parsed = def.parse(stored[key]);
    out[key] = parsed === null ? def.def : parsed;
  }
  return out;
}

// readAppSetting: ein einzelner Wert (Kurzform für die Aufrufer im Server).
// Parameter: key. Rückgabe: geprüfter Wert bzw. Vorgabe.
function readAppSetting(key){
  const def = APP_SETTING_DEFS[key];
  if(!def) return undefined;
  const row = db.prepare('SELECT value FROM app_settings WHERE key = ?').get(key);
  if(!row) return def.def;
  const parsed = def.parse(row.value);
  return parsed === null ? def.def : parsed;
}

// Route: alle firmenweiten Einstellungen lesen. Auth, nicht admin-only — das
// Frontend RECHNET damit (Auslastung, Terminplan) und braucht sie deshalb bei
// jedem Konto. Geändert werden dürfen sie nur von Administratoren, siehe PUT.
app.get('/app-settings', requireAuth, (req, res) => {
  try {
    res.json({ ok: true, settings: readAppSettings() });
  } catch(err){
    console.error('Einstellungen laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// GET/PUT /my-ai-settings: die beiden KI-API-Schlüssel des ANGEMELDETEN
// Kontos (req.user.id, nie eine vom Client mitgeschickte Kennung — exakt
// wie /change-email/POST). Jeder Nutzer verwaltet seinen eigenen Zugang
// unter Einstellungen → Mein Konto; kein admin-only mehr, siehe CLAUDE.md
// "### Protokolle". GET liefert je Anbieter, OB ein Schlüssel gesetzt ist
// (nie der Schlüssel selbst, wie ein Passwort) sowie die — nicht geheime —
// apiUrl zum Weiterbearbeiten.
app.get('/my-ai-settings', requireAuth, (req, res) => {
  try {
    const chatgpt = readUserAiKey(req.user.id, 'chatgpt');
    const claude = readUserAiKey(req.user.id, 'claude');
    res.json({
      ok: true,
      chatgpt: { configured: !!chatgpt.apiKey, apiUrl: chatgpt.apiUrl },
      claude: { configured: !!claude.apiKey, apiUrl: claude.apiUrl }
    });
  } catch(err){
    console.error('KI-Einstellungen laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

app.put('/my-ai-settings', requireAuth, (req, res) => {
  try {
    const body = (req.body && typeof req.body === 'object') ? req.body : {};
    for(const provider of ['chatgpt', 'claude']){
      const patch = body[provider];
      if(!patch || typeof patch !== 'object') continue;
      if(Object.prototype.hasOwnProperty.call(patch, 'apiUrl') && patch.apiUrl && !istGueltigeAiUrl(patch.apiUrl)){
        return res.status(400).json({ ok: false, error: 'Die API-URL muss eine gültige https-Adresse sein (oder http auf localhost/127.0.0.1 für einen lokalen Server wie LM Studio/Ollama).' });
      }
      writeUserAiKey(req.user.id, provider, patch);
    }
    const chatgpt = readUserAiKey(req.user.id, 'chatgpt');
    const claude = readUserAiKey(req.user.id, 'claude');
    res.json({
      ok: true,
      chatgpt: { configured: !!chatgpt.apiKey, apiUrl: chatgpt.apiUrl },
      claude: { configured: !!claude.apiKey, apiUrl: claude.apiUrl }
    });
  } catch(err){
    console.error('KI-Einstellungen speichern fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// POST /my-ai-settings/test: "Neu verbinden" — prüft die GESPEICHERTE
// Konfiguration des aufrufenden Kontos für einen Anbieter, indem
// tatsächlich eine kurze, echte Anfrage über erkennePunkteMitKI() läuft
// (dieselbe Funktion, die auch der Protokoll-Import nutzt — deckt damit
// automatisch Timeout-Wahl, reasoning_effort-Behandlung für lokale Ziele
// und URL-Normalisierung mit ab, ohne eigenen Testpfad pflegen zu müssen).
app.post('/my-ai-settings/test', requireAuth, async (req, res) => {
  try {
    const provider = req.body && req.body.provider;
    if(provider !== 'chatgpt' && provider !== 'claude'){
      return res.status(400).json({ ok: false, error: 'Unbekannter Anbieter.' });
    }
    await erkennePunkteMitKI('1. Testpunkt. Zustaendig: Test. Termin: 01.01.2026', req.user.id, provider);
    res.json({ ok: true });
  } catch(err){
    // Ein fehlgeschlagener Verbindungstest ist ein normales, erwartetes
    // Ergebnis, kein Serverfehler — deshalb 200 mit ok:false statt 4xx/5xx,
    // der Client unterscheidet Erfolg/Fehlschlag einheitlich über "ok".
    res.json({ ok: false, error: err.message || String(err) });
  }
});

// Route: firmenweite Einstellungen schreiben. Admin-only. Teilweise Updates
// (nur die im Rumpf genannten Schlüssel werden geschrieben); unbekannte
// Schlüssel und unbrauchbare Werte führen zu 400, bevor irgendetwas
// geschrieben wird — sonst bliebe die Hälfte einer Maske liegen.
app.put('/app-settings', requireRole('admin'), (req, res) => {
  try {
    const body = (req.body && typeof req.body === 'object') ? req.body : {};
    const writes = [];
    for(const [key, value] of Object.entries(body)){
      const def = APP_SETTING_DEFS[key];
      if(!def) return res.status(400).json({ ok:false, error: 'Unbekannte Einstellung: ' + key });
      const parsed = def.parse(value);
      if(parsed === null) return res.status(400).json({ ok:false, error: 'Unbrauchbarer Wert für „' + def.label + '".' });
      writes.push([key, def.json ? JSON.stringify(parsed) : String(parsed)]);
    }
    // Gelb darf nicht über Rot liegen — sonst zeigte die Ampel nie Gelb.
    const merged = Object.assign(readAppSettings(), Object.fromEntries(
      writes.map(([k, v]) => [k, APP_SETTING_DEFS[k].parse(v)])
    ));
    if(merged.auslastungSchwelleGelb > merged.auslastungSchwelleRot){
      return res.status(400).json({ ok:false, error: 'Die gelbe Schwelle muss unter der roten liegen.' });
    }

    db.exec('BEGIN');
    try {
      const stmt = db.prepare(`INSERT INTO app_settings (key, value) VALUES (?, ?)
        ON CONFLICT(key) DO UPDATE SET value = excluded.value`);
      writes.forEach(([k, v]) => stmt.run(k, v));
      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      throw err;
    }
    res.json({ ok: true, settings: readAppSettings() });
  } catch(err){
    console.error('Einstellungen speichern fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// ---------- Feiertage ----------

// Gesetzliche Feiertage lassen sich vollstaendig BERECHNEN: Ein Teil liegt auf
// festen Kalendertagen, der Rest haengt am Osterdatum. Deshalb holt diese
// Anwendung sie nicht von einem Netzdienst — das haette eine Abhaengigkeit
// von fremder Verfuegbarkeit und einem Internetzugang des Servers bedeutet,
// den es im Buero-Netz nicht zwingend gibt. Berechnet ist das Ergebnis
// jederzeit gleich, funktioniert offline und braucht kein weiteres Paket
// (das Projekt kommt bewusst mit express und cors aus).
//
// Die errechneten Tage werden NICHT automatisch in die Tabelle geschrieben.
// Ein Administrator laesst sie sich vorschlagen und uebernimmt sie bewusst —
// Feiertage verschieben jeden gerechneten Terminplan, das soll nicht
// unbemerkt im Hintergrund passieren.

// GERMAN_STATES: die 16 Bundeslaender mit ihrem amtlichen Kuerzel.
const GERMAN_STATES = {
  BW: 'Baden-Württemberg',      BY: 'Bayern',
  BE: 'Berlin',                 BB: 'Brandenburg',
  HB: 'Bremen',                 HH: 'Hamburg',
  HE: 'Hessen',                 MV: 'Mecklenburg-Vorpommern',
  NI: 'Niedersachsen',          NW: 'Nordrhein-Westfalen',
  RP: 'Rheinland-Pfalz',        SL: 'Saarland',
  SN: 'Sachsen',                ST: 'Sachsen-Anhalt',
  SH: 'Schleswig-Holstein',     TH: 'Thüringen',
};

// easterSunday: Ostersonntag eines Jahres nach der gregorianischen
// Osterformel (Gauss/Meeus). Alle beweglichen Feiertage haengen daran.
// Parameter: year (Zahl). Rueckgabe: Date (UTC-Mitternacht).
function easterSunday(year){
  const a = year % 19;
  const b = Math.floor(year / 100);
  const c = year % 100;
  const d = Math.floor(b / 4);
  const e = b % 4;
  const f = Math.floor((b + 8) / 25);
  const g = Math.floor((b - f + 1) / 3);
  const h = (19 * a + b - d - g + 15) % 30;
  const i = Math.floor(c / 4);
  const k = c % 4;
  const l = (32 + 2 * e + 2 * i - h - k) % 7;
  const m = Math.floor((a + 11 * h + 22 * l) / 451);
  const month = Math.floor((h + l - 7 * m + 114) / 31);
  const day = ((h + l - 7 * m + 114) % 31) + 1;
  return new Date(Date.UTC(year, month - 1, day));
}

// isoFromDate / shiftDays: kleine Datums-Helfer fuer die Berechnung. Gerechnet
// wird durchgehend in UTC, damit keine Sommerzeit-Umstellung einen Tag
// verschiebt.
function isoFromDate(d){ return d.toISOString().slice(0, 10); }
function shiftDays(d, n){ return new Date(d.getTime() + n * 86400000); }

// bussUndBettag: Mittwoch vor dem 23. November (nur Sachsen).
// Parameter: year. Rueckgabe: Date.
function bussUndBettag(year){
  let d = new Date(Date.UTC(year, 10, 22));   // 22.11.
  while(d.getUTCDay() !== 3) d = shiftDays(d, -1);
  return d;
}

// holidaysForYear: die gesetzlichen Feiertage eines Jahres in einem
// Bundesland. Nicht enthalten sind die gemeindeabhaengigen Faelle
// (Fronleichnam in Teilen von Sachsen und Thueringen, Mariä Himmelfahrt in
// ueberwiegend katholischen Gemeinden Bayerns) — die haengen vom Wohnort ab
// und lassen sich nicht aus dem Bundesland ableiten; sie koennen von Hand
// ergaenzt werden.
// Parameter: year (Zahl), state (Kuerzel aus GERMAN_STATES).
// Rueckgabe: Array von { date, name }, nach Datum sortiert.
function holidaysForYear(year, state){
  const ostern = easterSunday(year);
  const fest = (m, d) => isoFromDate(new Date(Date.UTC(year, m - 1, d)));
  const ab = n => isoFromDate(shiftDays(ostern, n));

  // Jeder Eintrag: [Datum, Bezeichnung, Bundeslaender oder null = bundesweit]
  const alle = [
    [fest(1, 1),   'Neujahr',                      null],
    [fest(1, 6),   'Heilige Drei Könige',          ['BW','BY','ST']],
    [fest(3, 8),   'Internationaler Frauentag',    ['BE','MV']],
    [ab(-2),       'Karfreitag',                   null],
    [ab(0),        'Ostersonntag',                 ['BB']],
    [ab(1),        'Ostermontag',                  null],
    [fest(5, 1),   'Tag der Arbeit',               null],
    [ab(39),       'Christi Himmelfahrt',          null],
    [ab(49),       'Pfingstsonntag',               ['BB']],
    [ab(50),       'Pfingstmontag',                null],
    [ab(60),       'Fronleichnam',                 ['BW','BY','HE','NW','RP','SL']],
    [fest(8, 15),  'Mariä Himmelfahrt',            ['SL']],
    [fest(9, 20),  'Weltkindertag',                ['TH']],
    [fest(10, 3),  'Tag der Deutschen Einheit',    null],
    [fest(10, 31), 'Reformationstag',              ['BB','HB','HH','MV','NI','SH','SN','ST','TH']],
    [fest(11, 1),  'Allerheiligen',                ['BW','BY','NW','RP','SL']],
    [isoFromDate(bussUndBettag(year)), 'Buß- und Bettag', ['SN']],
    [fest(12, 25), '1. Weihnachtstag',             null],
    [fest(12, 26), '2. Weihnachtstag',             null],
  ];

  return alle
    .filter(([, , laender]) => laender === null || laender.includes(state))
    .map(([date, name]) => ({ date, name }))
    .sort((a, b) => a.date.localeCompare(b.date));
}

// Route: gesetzliche Feiertage eines Jahres vorschlagen. Auth (lesend, die
// Berechnung verraet nichts). Jeder Eintrag traegt zusaetzlich `vorhanden`,
// damit die Maske zeigen kann, was schon hinterlegt ist.
app.get('/holidays/suggest', requireAuth, (req, res) => {
  try {
    const year = Number(req.query.year);
    const state = String(req.query.state || readAppSetting('feiertagBundesland'));
    if(!Number.isInteger(year) || year < 1970 || year > 2200){
      return res.status(400).json({ ok:false, error:'Jahr muss zwischen 1970 und 2200 liegen.' });
    }
    if(!GERMAN_STATES[state]){
      return res.status(400).json({ ok:false, error:'Unbekanntes Bundesland: ' + state });
    }
    const vorhanden = new Set(db.prepare('SELECT date FROM holidays').all().map(r => r.date));
    const holidays = holidaysForYear(year, state).map(h => ({
      date: h.date, name: h.name, vorhanden: vorhanden.has(h.date),
    }));
    res.json({ ok:true, year, state, stateName: GERMAN_STATES[state], holidays, states: GERMAN_STATES });
  } catch(err){
    console.error('Feiertage berechnen fehlgeschlagen:', err);
    res.status(500).json({ ok:false, error: String(err) });
  }
});

// Route: berechnete Feiertage uebernehmen. Admin-only. Bereits hinterlegte
// Tage bleiben unveraendert — ein von Hand angepasster Name (etwa ein
// Brueckentag, den jemand auf denselben Tag gelegt hat) soll nicht
// ueberschrieben werden. Die Antwort meldet beides getrennt.
app.post('/holidays/import', requireRole('admin'), (req, res) => {
  try {
    const year = Number((req.body || {}).year);
    const state = String((req.body || {}).state || readAppSetting('feiertagBundesland'));
    if(!Number.isInteger(year) || year < 1970 || year > 2200){
      return res.status(400).json({ ok:false, error:'Jahr muss zwischen 1970 und 2200 liegen.' });
    }
    if(!GERMAN_STATES[state]){
      return res.status(400).json({ ok:false, error:'Unbekanntes Bundesland: ' + state });
    }
    const liste = holidaysForYear(year, state);
    const stmt = db.prepare('INSERT OR IGNORE INTO holidays (date, name) VALUES (?, ?)');
    let angelegt = 0;
    db.exec('BEGIN');
    try {
      liste.forEach(h => { angelegt += stmt.run(h.date, h.name).changes; });
      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      throw err;
    }
    res.json({ ok:true, angelegt, uebersprungen: liste.length - angelegt, gesamt: liste.length });
  } catch(err){
    console.error('Feiertage übernehmen fehlgeschlagen:', err);
    res.status(500).json({ ok:false, error: String(err) });
  }
});


// Route: Feiertage lesen. Auth — das Frontend rechnet damit in Werktagen.
app.get('/holidays', requireAuth, (req, res) => {
  try {
    const rows = db.prepare('SELECT date, name FROM holidays ORDER BY date').all();
    // Die Bundesländer kommen gleich mit: Der Server ist die eine Stelle, an
    // der die Liste steht, und die Pflege-Maske braucht sie sofort — sonst
    // hätte ihre Auswahl bis zum ersten Vorschlag nur einen Eintrag.
    res.json({
      ok: true,
      holidays: rows.map(r => ({ date: r.date, name: r.name })),
      states: GERMAN_STATES,
    });
  } catch(err){
    console.error('Feiertage laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Feiertag anlegen oder umbenennen. Admin-only. Das Datum ist der
// Primärschlüssel, ein zweites Anlegen desselben Tages ersetzt also nur den
// Namen — das ist gewollt und erspart ein eigenes PATCH.
app.post('/holidays', requireRole('admin'), (req, res) => {
  try {
    const { date, name } = req.body || {};
    if(typeof date !== 'string' || !/^\d{4}-\d{2}-\d{2}$/.test(date)){
      return res.status(400).json({ ok:false, error:'Datum im Format JJJJ-MM-TT erforderlich.' });
    }
    const d = new Date(date + 'T00:00:00Z');
    if(Number.isNaN(d.getTime()) || d.toISOString().slice(0,10) !== date){
      return res.status(400).json({ ok:false, error:'Kein gültiges Datum.' });
    }
    const cleanName = String(name || '').trim().slice(0, 120);
    db.prepare(`INSERT INTO holidays (date, name) VALUES (?, ?)
      ON CONFLICT(date) DO UPDATE SET name = excluded.name`).run(date, cleanName);
    res.json({ ok: true, holiday: { date, name: cleanName } });
  } catch(err){
    console.error('Feiertag anlegen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Feiertag löschen. Admin-only, idempotent.
app.delete('/holidays/:date', requireRole('admin'), (req, res) => {
  try {
    db.prepare('DELETE FROM holidays WHERE date = ?').run(String(req.params.date));
    res.json({ ok: true });
  } catch(err){
    console.error('Feiertag löschen fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});


// readStundensatz: liest den firmenweiten Stundensatz (Vorgabe und Prüfung
// stehen in APP_SETTING_DEFS).
function readStundensatz(){
  // Ein Schlüssel, eine Definition: der Stundensatz steht seit der
  // Einführung von APP_SETTING_DEFS dort (mit derselben Vorgabe 135) und
  // wird weiterhin über /honorar-config gepflegt, weil er fachlich zur
  // Honorar-Maske gehört.
  return readAppSetting('stundensatz');
}

// Route: Honorar-Konfiguration lesen — Anteile je Leistungsphase und
// Stundensatz. Auth (nicht admin-only): jeder angemeldete Nutzer darf einen
// Terminplan rechnen lassen, nur das Pflegen der Sätze ist Admin-Sache.
app.get('/honorar-config', requireAuth, (req, res) => {
  try {
    const rows = db.prepare('SELECT lph, percent FROM lph_honorar_anteile').all();
    const anteile = {};
    rows.forEach(r => { anteile[r.lph] = r.percent; });
    res.json({ ok: true, anteile, stundensatz: readStundensatz() });
  } catch(err){
    console.error('Honorar-Konfiguration laden fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: Honorar-Konfiguration schreiben. Admin-only (firmenweite Vorgabe).
// `anteile` ist ein vollständiger Ersatz (delete + insert in einer
// Transaktion, gleiches Muster wie die Rollen in PATCH /admin/users/:id):
// eine im Objekt fehlende Leistungsphase gilt damit als "kein Anteil".
app.put('/honorar-config', requireRole('admin'), (req, res) => {
  try {
    const { anteile, stundensatz } = req.body || {};
    if(anteile !== undefined && (typeof anteile !== 'object' || anteile === null)){
      return res.status(400).json({ ok: false, error: 'anteile muss ein Objekt sein.' });
    }
    if(stundensatz !== undefined){
      const v = Number(stundensatz);
      if(!Number.isFinite(v) || v <= 0){
        return res.status(400).json({ ok: false, error: 'Stundensatz muss größer als 0 sein.' });
      }
    }
    // Vor dem Schreiben prüfen, damit eine einzelne unbrauchbare Zeile nicht
    // die halbe Konfiguration überschreibt.
    const clean = [];
    if(anteile){
      for(const [lph, pct] of Object.entries(anteile)){
        if(!VALID_LPHS.has(lph)){
          return res.status(400).json({ ok: false, error: 'Unbekannte Leistungsphase: ' + lph });
        }
        const v = Number(pct);
        if(!Number.isFinite(v) || v < 0 || v > 100){
          return res.status(400).json({ ok: false, error: 'Anteil für ' + lph + ' muss zwischen 0 und 100 liegen.' });
        }
        if(v > 0) clean.push([lph, v]);
      }
    }

    db.exec('BEGIN');
    try {
      if(anteile){
        db.prepare('DELETE FROM lph_honorar_anteile').run();
        const ins = db.prepare('INSERT INTO lph_honorar_anteile (lph, percent) VALUES (?, ?)');
        clean.forEach(([lph, v]) => ins.run(lph, v));
      }
      if(stundensatz !== undefined){
        db.prepare(`INSERT INTO app_settings (key, value) VALUES ('stundensatz', ?)
          ON CONFLICT(key) DO UPDATE SET value = excluded.value`).run(String(Number(stundensatz)));
      }
      db.exec('COMMIT');
    } catch(err){
      db.exec('ROLLBACK');
      throw err;
    }

    const rows = db.prepare('SELECT lph, percent FROM lph_honorar_anteile').all();
    const out = {};
    rows.forEach(r => { out[r.lph] = r.percent; });
    res.json({ ok: true, anteile: out, stundensatz: readStundensatz() });
  } catch(err){
    console.error('Honorar-Konfiguration speichern fehlgeschlagen:', err);
    res.status(500).json({ ok: false, error: String(err) });
  }
});


// Route: DB-Snapshot-Download. Erzeugt einen konsistenten Snapshot der
// SQLite-Datenbank (auch mit WAL) und stellt ihn als .db-Datei zum Download
// bereit (Backup für Archivierung/Wiederherstellung, z. B. per "DB Backup"-
// Button im Menü). Die temporäre Datei wird nach dem Senden gelöscht.
// NUR ADMINS: Der Snapshot enthält die komplette Datenbank und damit auch
// user_sessions.token im Klartext. Mit einem fremden Token könnte sich sonst
// jedes Mitglied als Administrator ausgeben (Rechteausweitung) — deshalb ist
// diese Route strenger abgesichert als der Rest der fachlichen Daten-Routen.
app.get('/backup', requireRole('admin'), (req, res) => {
  const tmp = path.join(os.tmpdir(), 'projektuebersicht_backup_' + Date.now() + '.db');
  try {
    createDbSnapshot(tmp);
    const filename = 'projektuebersicht_backup_' + new Date().toISOString().slice(0, 10) + '.db';
    res.download(tmp, filename, () => fs.unlink(tmp, () => {}));
    console.log('DB-Backup-Download:', filename);
  } catch(err){
    console.error('DB-Backup fehlgeschlagen:', err);
    fs.unlink(tmp, () => {});
    if(!res.headersSent) res.status(500).json({ ok: false, error: String(err) });
  }
});

// Route: DB-Import (Backup-Wiederherstellung). Erwartet eine .db-Datei aus
// GET /backup bzw. "DB Backup" als rohen Binär-Body (application/octet-stream);
// validiert das Schema und ersetzt den gesamten Datenbestand. VOR dem Import
// wird der Zustand vor dem Import als frisches Backup gesichert, damit ein
// versehentlicher Restore rückgängig gemacht werden kann.
app.post('/import', requireRole('admin'), express.raw({ type: '*/*', limit: '100mb' }), (req, res) => {
  const buf = req.body;
  if(!Buffer.isBuffer(buf) || buf.length === 0){
    return res.status(400).json({ ok: false, error: 'Keine Datei empfangen' });
  }
  const tmp = path.join(os.tmpdir(), 'projektuebersicht_import_' + Date.now() + '.db');
  try {
    fs.writeFileSync(tmp, buf);
    // Zustand VOR dem Restore sichern (danach wäre der Snapshot bereits der
    // importierte Stand und die Wiederherstellung unmöglich).
    runAutoBackup();
    restoreDbFromBackup(tmp);
    console.log('Datenbank aus Backup wiederhergestellt (' + buf.length + ' Bytes)');
    return res.json({ ok: true });
  } catch(err){
    console.error('Datenbank-Import fehlgeschlagen:', err);
    if(!res.headersSent) res.status(500).json({ ok: false, error: String(err && err.message ? err.message : err) });
  } finally {
    fs.unlink(tmp, () => {});
  }
});

// Route: Healthcheck-Endpunkt für Docker/Überwachung
app.get('/health', (req,res)=> res.json({ok:true}));

// Route: Liefert die Hauptseite Test.html
app.get('/', (req,res)=>{
  res.sendFile(path.join(__dirname, 'public', 'Test.html'));
});

// Gültige Tab-Slugs für Deep-Link-Routen (muss zu TAB_SLUGS in js/globals.js passen)
// 'Projekte' bleibt bewusst gültig, obwohl der gleichnamige Bereich in der
// Projektliste aufgegangen ist: Weitergegebene Links sollen nicht mit 404
// enden. Die Oberfläche leitet den Abschnitt auf die Projektliste um
// (TAB_BY_SLUG in public/js/globals.js) und erzeugt ihn selbst nicht mehr.
const VALID_TAB_SLUGS = new Set(['Projektübersicht','Wochenbericht','Auslastung','Projekte','Projektbeteiligte','Planung','MeineAufgaben','Einstellungen','Admin']);
// Slug der Projektplan-Seite (muss zu PLAN_SLUG in js/globals.js passen)
const PLAN_SLUG = 'Projektplan';

// Route: alte Direktlinks auf die entfallene Datei admin-users.html. Sie war
// bis zum Umbau eine eigene Seite mit nachgebauter Kopfzeile; die
// Benutzerverwaltung ist heute ein Abschnitt des Bereichs „Admin" auf der
// Hauptseite, damit die Seitenleiste stehen bleibt.
app.get('/admin-users.html', (req, res) => {
  res.redirect(301, '/Admin#benutzer');
});

// Route: alte Direktlinks auf die entfallene Datei project-plan.html. Sie
// war bis zum Umbau ein eigenes Dokument; heute ist der Projektbereich der
// Bereich #plan der Hauptseite. Die Parameter (weekLabel, projNr, projName)
// bleiben erhalten, damit ein alter Link weiterhin dasselbe Projekt öffnet.
app.get('/project-plan.html', (req, res) => {
  const params = new URLSearchParams(req.query);
  const projName = params.get('projName') || '';
  params.delete('projName');
  const qs = params.toString();
  res.redirect(301, '/' + PLAN_SLUG + '/' + encodeURIComponent(projName) + (qs ? '?' + qs : ''));
});

// Route: Deep-Links je Tab, z. B. "/Auslastung" oder "/Projektübersicht"
// (seit Entfernung der Abteilungstrennung nur noch ein Pfad-Segment, vorher
// "/<Abteilung>/<Tab>"). Liefert wie "/" die Hauptseite; unbekannte Tabs
// werden abgelehnt (404), damit keine Test.html auf beliebigen Pfaden
// ausgeliefert wird.
app.get('/:tab', (req, res) => {
  const { tab } = req.params;
  if(tab === PLAN_SLUG){
    // Projektbereich eines Projekts, z. B. "/Projektplan?weekLabel=..." —
    // seit dem Umbau kein eigenes Dokument mehr, sondern der Bereich #plan
    // der Hauptseite (applyUrlRoute() in public/js/ui.js wertet die Adresse
    // clientseitig aus).
    return res.sendFile(path.join(__dirname, 'public', 'Test.html'));
  }
  if(!VALID_TAB_SLUGS.has(tab)) return res.status(404).send('Nicht gefunden');
  res.sendFile(path.join(__dirname, 'public', 'Test.html'));
});

// Route: Projektbereich mit Projektname im Pfad, z. B.
// "/Projektplan/Gymnasium%20Hemer?weekLabel=..." – das Pfad-Segment dient nur
// der lesbaren Adresse, die Identifikation erfolgt über weekLabel/projNr.
app.get('/Projektplan/:projName', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'Test.html'));
});

// Route: Weiterleitung alter, noch abteilungspräfixierter Projektplan-Links
// ("/HLS/Projektplan/Foo?weekLabel=...") auf das neue Schema, damit alte
// Lesezeichen nicht brechen. Muss VOR der generischen 2-Segment-Weiterleitung
// stehen (3 Segmente sind eindeutig).
app.get('/:legacyDept/Projektplan/:projName', (req, res) => {
  if(!VALID_DEPARTMENTS.has(req.params.legacyDept)) return res.status(404).send('Nicht gefunden');
  const qs = req.url.includes('?') ? req.url.slice(req.url.indexOf('?')) : '';
  res.redirect(301, '/Projektplan/' + encodeURIComponent(req.params.projName) + qs);
});

// Route: Weiterleitung alter zweisegmentiger Deep-Links ("/ELT/Auslastung")
// auf das neue einsegmentige Schema. Muss NACH "/Projektplan/:projName"
// stehen, sonst würde z.B. "/Projektplan/Foo" hier fälschlich als
// Abteilung="Projektplan" interpretiert.
app.get('/:legacyDept/:tab', (req, res) => {
  if(!VALID_DEPARTMENTS.has(req.params.legacyDept)) return res.status(404).send('Nicht gefunden');
  const qs = req.url.includes('?') ? req.url.slice(req.url.indexOf('?')) : '';
  res.redirect(301, '/' + encodeURIComponent(req.params.tab) + qs);
});

// Serverstart mit Fehlerbehandlung (z.B. Port bereits belegt)
const server = app.listen(PORT, () => {
  console.log(`Projektübersicht server running on http://localhost:${PORT}`);
  // Automatisches Datenbank-Backup beim Start (sessions/backups/)
  runAutoBackup();
  // Automatischer Sitzungswechsel: einmal jetzt (holt einen Wochenwechsel
  // nach, der while der Server nicht lief passiert ist) und danach stündlich
  // erneut — mehr als genug Auflösung für einen Wechsel, der genau einmal
  // pro Woche eintritt. Muss NACH allen anderen Definitionen dieser Datei
  // stehen (braucht u. a. APP_SETTING_DEFS/readAppSettings(), die erst
  // weiter unten im Datei-Durchlauf definiert werden) — deshalb hier im
  // app.listen()-Rückruf statt in der früheren Migrations-/Bootstrap-
  // Reihenfolge weiter oben.
  ensureCurrentWeekRollover();
  setInterval(ensureCurrentWeekRollover, 60 * 60 * 1000);
});
server.on('error', (err) => {
  console.error('Server konnte nicht starten:', err.message);
  if(err.code === 'EADDRINUSE'){
    console.error(`Port ${PORT} ist bereits belegt. Bitte einen anderen PORT verwenden, z.B. PORT=3004 npm start`);
  }
  process.exit(1);
});

// Sauberes Beenden auf Strg+C/Cmd+C (SIGINT) oder `kill` (SIGTERM): schließt
// zuerst den HTTP-Server (keine neuen Verbindungen mehr, laufende werden zu
// Ende bedient) und danach die Datenbank (sauberer WAL-Checkpoint statt
// eines abrupten Abbruchs mitten im Schreiben). Ohne diesen Handler würde
// Node ebenfalls beenden — kein anderer Code hier überschreibt das
// Standardverhalten —, aber lautlos und ohne den Checkpoint.
function shutdown(signal){
  console.log(`\n${signal} empfangen, Server wird beendet…`);
  // Sicherheitsnetz: eine offene Keep-Alive-Verbindung kann server.close()
  // unbegrenzt blockieren (bekannte Node-Eigenheit) — nach 3s notfalls hart
  // beenden, damit der Prozess in jedem Fall nur bis zum Tastendruck läuft.
  const notbremse = setTimeout(() => process.exit(0), 3000);
  notbremse.unref();
  server.close(() => {
    try { db.close(); } catch(e){ /* bereits geschlossen */ }
    console.log('Server beendet.');
    process.exit(0);
  });
}
process.on('SIGINT', () => shutdown('SIGINT'));
process.on('SIGTERM', () => shutdown('SIGTERM'));