Aime/app/db.js

import mysql from 'mysql2/promise';

export const pool = mysql.createPool({
  host: 'localhost', user: 'callrec', password: 'callrec123',
  database: 'callrec', connectionLimit: 5, waitForConnections: true
});

// Idempotent migration: the LLM analysis columns (auto title lives in `title`).
// MariaDB supports ADD COLUMN IF NOT EXISTS.
export async function ensureSchema() {
  await pool.query(`ALTER TABLE recordings
    ADD COLUMN IF NOT EXISTS summary TEXT NULL,
    ADD COLUMN IF NOT EXISTS todos TEXT NULL,
    ADD COLUMN IF NOT EXISTS participants TEXT NULL,
    ADD COLUMN IF NOT EXISTS transcript_clean TEXT NULL,
    ADD COLUMN IF NOT EXISTS wav_file VARCHAR(255) NULL,
    ADD COLUMN IF NOT EXISTS speaker_names TEXT NULL,
    ADD COLUMN IF NOT EXISTS speaker_labels TEXT NULL,
    ADD COLUMN IF NOT EXISTS analysis_status VARCHAR(16) NOT NULL DEFAULT 'pending',
    ADD COLUMN IF NOT EXISTS analysis_started_at BIGINT NULL,
    ADD COLUMN IF NOT EXISTS analysis_ms INT NULL`);
  await pool.query(`ALTER TABLE words ADD COLUMN IF NOT EXISTS speaker VARCHAR(32) NULL`);
  await pool.query(`CREATE TABLE IF NOT EXISTS voiceprints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    dim INT NOT NULL,
    vec MEDIUMTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`);
  // n = number of samples averaged into this voiceprint (running centroid).
  await pool.query(`ALTER TABLE voiceprints ADD COLUMN IF NOT EXISTS n INT NOT NULL DEFAULT 1`);
  // Embedding model the vector came from: prints of different models are NOT
  // comparable (cosine across models is noise), so identification only ever
  // uses the rows of the model currently loaded. NULL = pre-2026-07-27 TitaNet.
  await pool.query(`ALTER TABLE voiceprints ADD COLUMN IF NOT EXISTS model VARCHAR(64) NULL`);
  await pool.query(`UPDATE voiceprints SET model='nemo_titanet_small' WHERE model IS NULL`);
  // Ein Mensch, mehrere Namen (seit 2026-08-07). Vorher war der NAME die Identität:
  // "Sascha" und "Sascha Wolf" waren zwei Einträge derselben Stimme (Kosinus 0,928),
  // konnten zwei verschiedene Cluster benennen und ließen die Diarisierung 4
  // Sprecher statt 2 zeigen. diar.js hat das mit einem Vornamens-Trick überdeckt —
  // der aber "Markus Ufer" und "Markus Witsch" (Kosinus 0,241, zwei Menschen)
  // fälschlich gleichsetzt. Identität ist deshalb jetzt explizit die PERSON.
  await pool.query(`CREATE TABLE IF NOT EXISTS persons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    display_name VARCHAR(128) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`);
  // Ein Name gehört zu genau EINER Person (PK auf name; die Standard-Kollation
  // utf8mb4_general_ci macht das absichtlich case-insensitiv, "sascha" = "Sascha").
  await pool.query(`CREATE TABLE IF NOT EXISTS person_names (
    name VARCHAR(128) NOT NULL PRIMARY KEY,
    person_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_person (person_id))`);
  // Abdrücke gehören der Person, nicht dem Namen — und es dürfen MEHRERE pro
  // Person sein (verschiedene Sessions/Headsets/Räume). Das ersetzt das alte
  // laufende Mittel: ein Zentroid über sehr unterschiedliche Aufnahmesituationen
  // verwischt gerade die Merkmale, auf die es ankommt (Spalte `n` bleibt für die
  // Altdaten stehen).
  await pool.query(`ALTER TABLE voiceprints ADD COLUMN IF NOT EXISTS person_id INT NULL`);
  await pool.query(`ALTER TABLE voiceprints ADD INDEX IF NOT EXISTS idx_person (person_id)`);
  // Domain glossary: known proper nouns / terms the ASR mis-hears (e.g. "Aime"
  // heard as "Amy"). Fed into the LLM correction step so soundalikes get mapped
  // to the correct spelling. `soundalikes` = optional comma-list of mis-hearings.
  await pool.query(`CREATE TABLE IF NOT EXISTS glossary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    term VARCHAR(190) NOT NULL UNIQUE,
    soundalikes TEXT NULL,
    enabled TINYINT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`);
  // Live speaker markers: names typed DURING the recording ("who is talking
  // right now"). t_ms = offset from recording start. After diarization each
  // marker is resolved to the acoustic cluster that was speaking around t_ms
  // (`speaker`), which names that cluster — ground truth instead of guessing.
  await pool.query(`CREATE TABLE IF NOT EXISTS markers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recording_id INT NOT NULL,
    t_ms INT NOT NULL,
    name VARCHAR(128) NOT NULL,
    speaker VARCHAR(32) NULL,
    conf VARCHAR(8) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX rec_idx (recording_id))`);
  // Trash instead of hard delete: `deleted_at` set = in the bin, rows and audio
  // files stay untouched until purgeExpired() passes TRASH_DAYS. Every listing
  // filters on it, so a binned recording is invisible everywhere else.
  // `title_locked` marks a title the USER typed — a later re-analysis must not
  // overwrite it with a fresh LLM guess (see updateAnalysis).
  await pool.query(`ALTER TABLE recordings
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS title_locked TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS doc_vec MEDIUMTEXT NULL`);
  // Kontextnotiz des Nutzers: was er direkt nach dem Stop über das Gespräch
  // notiert (wer war dabei, worum ging es, welche Eigennamen). Bewusst EIN
  // Freitextfeld statt Formularfeldern — die Box benutzen auch Menschen, die
  // kein Formular ausfüllen wollen; die Struktur (Namen, Begriffe) holt sich
  // die Analyse selbst aus der Prosa. Fließt als Hintergrundwissen in die
  // LLM-Analyse (llm.analyze -> contextBlock), NICHT als Gesprächsinhalt.
  await pool.query(`ALTER TABLE recordings
    ADD COLUMN IF NOT EXISTS context_note TEXT NULL,
    ADD COLUMN IF NOT EXISTS context_note_at TIMESTAMP NULL DEFAULT NULL`);
  // Search index. One row per passage (a few sentences of ONE speaker, see
  // search.js/buildPassages), carrying the time window so a hit can name the
  // exact minute and the player can jump there. `vec` = optional text embedding
  // for semantic ranking; NULL when the embedding model is not installed, in
  // which case search degrades to full-text only.
  await pool.query(`CREATE TABLE IF NOT EXISTS passages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recording_id INT NOT NULL,
    idx INT NOT NULL,
    speaker VARCHAR(32) NULL,
    speaker_name VARCHAR(128) NULL,
    t_start_ms INT NOT NULL,
    t_end_ms INT NOT NULL,
    text TEXT NOT NULL,
    vec MEDIUMTEXT NULL,
    INDEX rec_idx (recording_id),
    FULLTEXT KEY ft_text (text)) ENGINE=InnoDB`);
  // Projektansicht: Ordner (projects) > Unterreiter (project_tabs) > angepinnte
  // Aufnahmen (project_pins). Eine Aufnahme kann in beliebig vielen Projekten
  // hängen — ein Pin ist eine Referenz, kein Besitz, die Aufnahme selbst bleibt
  // in der normalen Liste unverändert.
  await pool.query(`CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`);
  // Beschriftung der Ordner-Ebene (tab_id 0). Die liegt am Projekt statt in
  // project_tabs, weil sie keine echte Reiter-Zeile ist — es gibt sie immer,
  // sie lässt sich nicht löschen und hat keine eigene id. NULL = „Ordner".
  await pool.query('ALTER TABLE projects ADD COLUMN IF NOT EXISTS root_tab_name VARCHAR(190) NULL');
  await pool.query(`CREATE TABLE IF NOT EXISTS project_tabs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX proj_idx (project_id))`);
  // tab_id = 0 heißt „direkt im Ordner“ (Ebene 1). Bewusst 0 statt NULL: in
  // MySQL/MariaDB ignoriert ein UNIQUE-Index NULL-Werte, ein und dieselbe
  // Aufnahme ließe sich sonst beliebig oft auf die Ordner-Ebene pinnen.
  await pool.query(`CREATE TABLE IF NOT EXISTS project_pins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    tab_id INT NOT NULL DEFAULT 0,
    recording_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_pin (project_id, tab_id, recording_id),
    INDEX rec_idx (recording_id))`);
}

// --- Projektansicht ----------------------------------------------------------
// Ordnerliste fürs Übersichtsgitter: pro Ordner die Zahl der Unterreiter und
// der (nicht gelöschten) Pins über alle Ebenen.
export async function listProjects() {
  const [rows] = await pool.query(`
    SELECT p.id, p.name, p.root_tab_name, p.created_at,
      (SELECT COUNT(*) FROM project_tabs t WHERE t.project_id = p.id) AS tabs,
      (SELECT COUNT(*) FROM project_pins pin JOIN recordings r ON r.id = pin.recording_id
         WHERE pin.project_id = p.id AND r.deleted_at IS NULL) AS pins
    FROM projects p ORDER BY p.name`);
  return rows;
}

export async function createProject(name) {
  const [res] = await pool.execute('INSERT INTO projects (name) VALUES (?)', [String(name).slice(0, 190)]);
  return { id: res.insertId, name };
}
export async function renameProject(id, name) {
  await pool.execute('UPDATE projects SET name=? WHERE id=?', [String(name).slice(0, 190), id]);
}
// Ordner-Ebene umbenennen. Leerer Name setzt auf die Vorgabe „Ordner" zurück.
export async function renameProjectRoot(id, name) {
  const n = String(name || '').trim().slice(0, 190);
  await pool.execute('UPDATE projects SET root_tab_name=? WHERE id=?', [n || null, id]);
  return n || null;
}
// Der Ordner verschwindet mitsamt Reitern und Pins — die Aufnahmen selbst
// bleiben unberührt (ein Pin ist nur eine Referenz).
export async function deleteProject(id) {
  await pool.query('DELETE FROM project_pins WHERE project_id=?', [id]);
  await pool.query('DELETE FROM project_tabs WHERE project_id=?', [id]);
  await pool.query('DELETE FROM projects WHERE id=?', [id]);
}

export async function createProjectTab(projectId, name) {
  const [res] = await pool.execute('INSERT INTO project_tabs (project_id, name) VALUES (?,?)',
    [projectId, String(name).slice(0, 190)]);
  return { id: res.insertId, project_id: projectId, name };
}
export async function renameProjectTab(id, name) {
  await pool.execute('UPDATE project_tabs SET name=? WHERE id=?', [String(name).slice(0, 190), id]);
}
export async function deleteProjectTab(id) {
  await pool.query('DELETE FROM project_pins WHERE tab_id=?', [id]);
  await pool.query('DELETE FROM project_tabs WHERE id=?', [id]);
}

// Ein Ordner mit allen Ebenen und den dort angepinnten Aufnahmen. Gelöschte
// (im Papierkorb liegende) Aufnahmen werden ausgeblendet, nicht entfernt: eine
// Wiederherstellung bringt den Pin ohne Zutun zurück.
export async function getProject(id) {
  const [pr] = await pool.query('SELECT id, name, root_tab_name, created_at FROM projects WHERE id=?', [id]);
  if (!pr.length) return null;
  const [tabs] = await pool.query(
    'SELECT id, name, created_at FROM project_tabs WHERE project_id=? ORDER BY id', [id]);
  const [pins] = await pool.query(`
    SELECT pin.id AS pin_id, pin.tab_id, r.id, r.title, r.duration_ms, r.created_at,
           r.status, r.analysis_status, r.summary
    FROM project_pins pin JOIN recordings r ON r.id = pin.recording_id
    WHERE pin.project_id=? AND r.deleted_at IS NULL
    ORDER BY r.created_at DESC`, [id]);
  return { ...pr[0], tabs, pins };
}

export async function pinRecording(projectId, tabId, recordingId) {
  await pool.execute(
    'INSERT IGNORE INTO project_pins (project_id, tab_id, recording_id) VALUES (?,?,?)',
    [projectId, tabId || 0, recordingId]);
}
export async function unpinRecording(pinId) {
  await pool.execute('DELETE FROM project_pins WHERE id=?', [pinId]);
}
// Einen Pin auf eine andere Ebene DESSELBEN Ordners schieben (Drag&Drop in der
// Projektansicht). Hängt die Aufnahme auf der Zielebene schon, entsteht kein
// zweiter Pin — der Quell-Pin verschwindet stattdessen (Zusammenführen statt
// Duplikat; ein UPDATE würde sonst an UNIQUE uniq_pin scheitern).
export async function movePin(pinId, projectId, tabId) {
  const [rows] = await pool.execute(
    'SELECT tab_id, recording_id FROM project_pins WHERE id=? AND project_id=?', [pinId, projectId]);
  if (!rows.length) return null;
  const src = rows[0];
  const dst = tabId || 0;
  // Zielreiter muss zu diesem Ordner gehören (0 = Ordner-Ebene, gibt es immer).
  if (dst) {
    const [t] = await pool.execute(
      'SELECT id FROM project_tabs WHERE id=? AND project_id=?', [dst, projectId]);
    if (!t.length) return null;
  }
  if (src.tab_id === dst) return { ok: true, moved: false, tab_id: dst };
  const [dup] = await pool.execute(
    'SELECT id FROM project_pins WHERE project_id=? AND tab_id=? AND recording_id=?',
    [projectId, dst, src.recording_id]);
  if (dup.length) {
    await pool.execute('DELETE FROM project_pins WHERE id=?', [pinId]);
    return { ok: true, moved: true, merged: true, tab_id: dst, pin_id: dup[0].id };
  }
  await pool.execute('UPDATE project_pins SET tab_id=? WHERE id=?', [dst, pinId]);
  return { ok: true, moved: true, tab_id: dst, pin_id: pinId };
}
// Wo hängt diese Aufnahme überall? Treibt die Markierung des 📌-Buttons in der
// Aufnahmenliste.
export async function listPinsForRecording(recId) {
  const [rows] = await pool.query(`
    SELECT pin.id AS pin_id, pin.project_id, p.name AS project_name,
           pin.tab_id, COALESCE(t.name, p.root_tab_name) AS tab_name
    FROM project_pins pin
    JOIN projects p ON p.id = pin.project_id
    LEFT JOIN project_tabs t ON t.id = pin.tab_id
    WHERE pin.recording_id=? ORDER BY p.name, t.name`, [recId]);
  return rows;
}
// Alle Pins auf einen Schlag (Liste: welche Aufnahme steckt in wie vielen
// Projekten) — spart einen Request pro Zeile.
export async function pinCounts() {
  const [rows] = await pool.query(
    'SELECT recording_id, COUNT(*) AS n FROM project_pins GROUP BY recording_id');
  const m = {};
  for (const r of rows) m[r.recording_id] = r.n;
  return m;
}

// --- live speaker markers ---------------------------------------------------
export async function addMarkers(recId, markers) {
  const list = (markers || []).filter(m => m && m.name);
  if (!list.length) return;
  await pool.query('INSERT INTO markers (recording_id, t_ms, name) VALUES ?',
    [list.map(m => [recId, Math.max(0, Math.round(m.t_ms || 0)), String(m.name).slice(0, 128)])]);
}
export async function listMarkers(recId) {
  const [rows] = await pool.query(
    'SELECT id, t_ms, name, speaker, conf FROM markers WHERE recording_id=? ORDER BY t_ms', [recId]);
  return rows;
}
export async function setMarkerSpeaker(id, speaker, conf) {
  await pool.execute('UPDATE markers SET speaker=?, conf=? WHERE id=?', [speaker || null, conf || null, id]);
}
export async function deleteMarker(id) { await pool.execute('DELETE FROM markers WHERE id=?', [id]); }
export async function addMarker(recId, tMs, name) {
  const [res] = await pool.execute('INSERT INTO markers (recording_id, t_ms, name) VALUES (?,?,?)',
    [recId, Math.max(0, Math.round(tMs || 0)), String(name).slice(0, 128)]);
  return res.insertId;
}

export async function listGlossary() {
  const [rows] = await pool.query('SELECT id, term, soundalikes, enabled, created_at FROM glossary ORDER BY term');
  return rows.map(r => ({ ...r, enabled: !!r.enabled }));
}
export async function addGlossary(term, soundalikes) {
  await pool.execute(
    'INSERT INTO glossary (term, soundalikes) VALUES (?,?) ON DUPLICATE KEY UPDATE soundalikes=VALUES(soundalikes), enabled=1',
    [term, soundalikes || null]);
  const [rows] = await pool.execute('SELECT id, term, soundalikes, enabled FROM glossary WHERE term=?', [term]);
  return rows[0] ? { ...rows[0], enabled: !!rows[0].enabled } : null;
}
export async function deleteGlossary(id) {
  await pool.execute('DELETE FROM glossary WHERE id=?', [id]);
}
export async function updateGlossary(id, term, soundalikes) {
  await pool.execute('UPDATE glossary SET term=?, soundalikes=? WHERE id=?', [term, soundalikes || null, id]);
  const [rows] = await pool.execute('SELECT id, term, soundalikes, enabled FROM glossary WHERE id=?', [id]);
  return rows[0] ? { ...rows[0], enabled: !!rows[0].enabled } : null;
}

// --- Personen (Identität) und ihre Stimmabdrücke ------------------------------
// Höchstzahl Abdrücke je Person und Modell. Mehrere Proben sind der Sinn der
// Sache (verschiedene Sessions), aber unbegrenzt wachsen darf es nicht: jede
// Probe kostet bei jeder Identifikation einen Kosinus. Älteste fallen raus.
const MAX_PRINTS_PER_PERSON = +(process.env.AIME_MAX_PRINTS || 8);

// Name → Person. Unbekannter Name legt Person + Namenseintrag an, damit jeder
// Weg (Marker, Sprecher-Popup, Enrollment) dieselbe Identität trifft.
export async function resolvePerson(name) {
  const n = String(name || '').trim();
  if (!n) return null;
  const [rows] = await pool.execute(
    `SELECT p.id, p.display_name FROM person_names pn JOIN persons p ON p.id = pn.person_id
     WHERE pn.name = ? LIMIT 1`, [n]);
  if (rows.length) return { id: rows[0].id, display_name: rows[0].display_name };
  const [res] = await pool.execute('INSERT INTO persons (display_name) VALUES (?)', [n]);
  await pool.execute('INSERT INTO person_names (name, person_id) VALUES (?,?)', [n, res.insertId]);
  return { id: res.insertId, display_name: n };
}

// Weiteren Namen an eine Person hängen ("Sascha" und "Sascha Wolf" = ein Mensch).
// Gehört der Name schon einer ANDEREN Person, werden die beiden zusammengeführt —
// das ist die Absicht des Nutzers, wenn er denselben Namen zweimal vergibt.
export async function addPersonName(personId, name) {
  const n = String(name || '').trim();
  if (!n) throw new Error('Name fehlt');
  const [rows] = await pool.execute('SELECT person_id FROM person_names WHERE name=?', [n]);
  if (rows.length) {
    if (rows[0].person_id === +personId) return { merged: false };
    await mergePersons(personId, rows[0].person_id);
    return { merged: true };
  }
  await pool.execute('INSERT INTO person_names (name, person_id) VALUES (?,?)', [n, personId]);
  return { merged: false };
}

// Der letzte Name einer Person darf nicht weg — sonst bleibt eine namenlose
// Identität mit Abdrücken zurück, die niemand mehr zuordnen kann.
export async function removePersonName(name) {
  const n = String(name || '').trim();
  const [rows] = await pool.execute('SELECT person_id FROM person_names WHERE name=?', [n]);
  if (!rows.length) return false;
  const [[{ c }]] = await pool.execute('SELECT COUNT(*) AS c FROM person_names WHERE person_id=?', [rows[0].person_id]);
  if (c <= 1) throw new Error('letzter Name einer Person — stattdessen Person löschen');
  await pool.execute('DELETE FROM person_names WHERE name=?', [n]);
  // War es der Anzeigename, rückt der älteste verbliebene Name nach.
  const [rest] = await pool.execute(
    'SELECT name FROM person_names WHERE person_id=? ORDER BY created_at LIMIT 1', [rows[0].person_id]);
  if (rest.length) await pool.execute('UPDATE persons SET display_name=? WHERE id=? AND display_name=?',
    [rest[0].name, rows[0].person_id, n]);
  return true;
}

export async function renamePerson(id, displayName) {
  const n = String(displayName || '').trim();
  if (!n) throw new Error('Name fehlt');
  await addPersonName(id, n).catch(() => {});
  await pool.execute('UPDATE persons SET display_name=? WHERE id=?', [n, id]);
}

// Namen und Abdrücke von `fromId` nach `intoId` umhängen, Quelle löschen.
export async function mergePersons(intoId, fromId) {
  if (+intoId === +fromId) return;
  await pool.execute('UPDATE person_names SET person_id=? WHERE person_id=?', [intoId, fromId]);
  await pool.execute('UPDATE voiceprints SET person_id=? WHERE person_id=?', [intoId, fromId]);
  await pool.execute('DELETE FROM persons WHERE id=?', [fromId]);
  await prunePrints(intoId);
}

export async function deletePerson(id) {
  await pool.execute('DELETE FROM voiceprints WHERE person_id=?', [id]);
  await pool.execute('DELETE FROM person_names WHERE person_id=?', [id]);
  await pool.execute('DELETE FROM persons WHERE id=?', [id]);
}

// Personen mit allen Namen und der Zahl ihrer Abdrücke (GUI: Stimmen-Tab).
export async function listPersons() {
  const [ps] = await pool.query('SELECT id, display_name, created_at FROM persons ORDER BY display_name');
  const [ns] = await pool.query('SELECT person_id, name FROM person_names ORDER BY created_at');
  const [vs] = await pool.query(
    'SELECT person_id, COUNT(*) AS prints, MAX(created_at) AS last_print FROM voiceprints WHERE person_id IS NOT NULL GROUP BY person_id');
  const byId = new Map(ps.map(p => [p.id, { ...p, names: [], prints: 0, last_print: null }]));
  for (const n of ns) byId.get(n.person_id)?.names.push(n.name);
  for (const v of vs) { const p = byId.get(v.person_id); if (p) { p.prints = v.prints; p.last_print = v.last_print; } }
  return [...byId.values()];
}

// Lowercase-Name → person_id. diar.js braucht das, um Live-Marker und Abdrücke
// auf DIESELBE Identität abzubilden (ein Marker "Sascha" und ein Abdruck von
// "Sascha Wolf" dürfen sich nicht als zwei Personen widersprechen).
export async function personNameMap() {
  const [rows] = await pool.query('SELECT name, person_id FROM person_names');
  const map = {};
  for (const r of rows) map[r.name.toLowerCase()] = r.person_id;
  return map;
}

async function prunePrints(personId) {
  const [rows] = await pool.execute(
    'SELECT id FROM voiceprints WHERE person_id=? ORDER BY created_at DESC, id DESC', [personId]);
  const drop = rows.slice(MAX_PRINTS_PER_PERSON).map(r => r.id);
  if (drop.length) await pool.query(`DELETE FROM voiceprints WHERE id IN (${drop.map(() => '?').join(',')})`, drop);
}

// Eine neue Stimmprobe für die Person hinter `name` speichern (kein Mitteln mehr,
// siehe MAX_PRINTS_PER_PERSON). Legt die Person bei Bedarf an.
export async function learnVoice(name, vec, model) {
  const person = await resolvePerson(name);
  if (!person) throw new Error('Name fehlt');
  const [res] = await pool.execute(
    'INSERT INTO voiceprints (name, person_id, dim, vec, n, model) VALUES (?,?,?,?,1,?)',
    [person.display_name, person.id, vec.length, JSON.stringify(vec), model || null]);
  await prunePrints(person.id);
  return { id: res.insertId, personId: person.id, name: person.display_name };
}

// model given → only the prints of that embedding model (what identification
// must use); omitted → all rows (GUI listing). `name` ist ab 2026-08-07 nur noch
// Anzeige — die Identität ist person_id (Fallback für Altzeilen ohne Person).
export async function listVoiceprints(model) {
  const sql = `SELECT v.id, v.person_id, v.dim, v.vec, v.model, v.created_at,
                      COALESCE(p.display_name, v.name) AS name
               FROM voiceprints v LEFT JOIN persons p ON p.id = v.person_id`;
  const [rows] = model
    ? await pool.query(`${sql} WHERE v.model=? ORDER BY name, v.created_at`, [model])
    : await pool.query(`${sql} ORDER BY name, v.created_at`);
  return rows.map(r => ({ ...r, vec: JSON.parse(r.vec) }));
}
export async function deleteVoiceprint(id) { await pool.query('DELETE FROM voiceprints WHERE id=?', [id]); }

export async function addRecording(r) {
  const [res] = await pool.execute(
    'INSERT INTO recordings (title, mp3_file, wav_file, device, duration_ms, transcript, language, status, analysis_status) VALUES (?,?,?,?,?,?,?,?,?)',
    [r.title, r.mp3_file, r.wav_file || null, r.device, r.duration_ms, r.transcript, r.language, r.status || 'done', 'pending']);
  return res.insertId;
}

export async function addWords(recId, words) {
  if (!words || !words.length) return;
  const values = words.map((w, i) => [recId, w.word, w.t_start_ms, w.t_end_ms, i, w.speaker || null]);
  await pool.query('INSERT INTO words (recording_id, word, t_start_ms, t_end_ms, idx, speaker) VALUES ?', [values]);
}

// Replace a recording's words wholesale (used when re-diarizing: the text is
// unchanged, only the speaker labels differ). Words are re-indexed by array order.
export async function replaceWords(recId, words) {
  await pool.query('DELETE FROM words WHERE recording_id=?', [recId]);
  await addWords(recId, words);
}

// Store the transcript + words after async (thermal-guarded) transcription and
// flip the row out of 'transcribing'. The wav + row were saved before whisper
// ran, so this just fills them in.
export async function setTranscript(id, transcript, words) {
  await pool.execute('UPDATE recordings SET transcript=?, status=? WHERE id=?', [transcript || '', 'done', id]);
  await replaceWords(id, words || []);
}

// Every in-flight job dies with the service: systemd SIGKILLs the whisper,
// spk_embed and rkllm children on stop, and nothing resumes them. The row
// however still reads 'pending' with a start stamp, so the GUI shows
// "KI-Analyse läuft…" and a timer counting up forever on work that no longer
// exists (observed: 2 h 34 min on a 29-minute recording after a deploy
// restart). Called once at boot — at that moment no job CAN legitimately be
// running — so the retry button appears instead of a phantom counter.
export async function failStaleJobs() {
  const [a] = await pool.query(
    "UPDATE recordings SET analysis_status='error' WHERE analysis_status='pending' AND analysis_started_at IS NOT NULL");
  const [t] = await pool.query(
    "UPDATE recordings SET status='error' WHERE status='transcribing'");
  return { analyses: a.affectedRows, transcriptions: t.affectedRows };
}

// Stamp the wall-clock start of a processing run (transcribe+diar+LLM). The
// GUI shows a live counter from here; updateAnalysis('done') freezes the total
// into analysis_ms. Called at every trigger (record-stop, retranscribe, reanalyze).
export async function markAnalysisStart(id) {
  await pool.execute('UPDATE recordings SET analysis_started_at=?, analysis_ms=NULL WHERE id=?', [Date.now(), id]);
}

// Update just the coarse recording status ('transcribing' | 'done' | 'error').
export async function setStatus(id, status) {
  await pool.execute('UPDATE recordings SET status=? WHERE id=?', [status, id]);
}

// Per-recording map { rawSpeaker -> displayName }. Manual edits (setSpeakerName)
// are authoritative; acoustic identification only fills blanks (mergeSpeakerNames).
async function getSpeakerNames(id) {
  const [rows] = await pool.query('SELECT speaker_names FROM recordings WHERE id=?', [id]);
  try { return JSON.parse((rows[0] && rows[0].speaker_names) || '{}') || {}; } catch { return {}; }
}
export async function setSpeakerName(id, speaker, name) {
  const m = await getSpeakerNames(id);
  if (name) m[speaker] = name; else delete m[speaker];   // empty name clears back to "Sprecher N"
  await pool.execute('UPDATE recordings SET speaker_names=? WHERE id=?', [JSON.stringify(m), id]);
}
// The label EVERY speaker carried in the text the LLM produced ({speaker_07:
// "Sprecher 5"}). Stored with the analysis so a later rename can swap the name
// into the finished summary/todos instead of re-running the 20-minute analysis.
export async function setSpeakerLabels(id, labels) {
  await pool.execute('UPDATE recordings SET speaker_labels=? WHERE id=?', [JSON.stringify(labels || {}), id]);
}
export async function getSpeakerLabels(id) {
  const [rows] = await pool.query('SELECT speaker_labels FROM recordings WHERE id=?', [id]);
  try { return JSON.parse((rows[0] && rows[0].speaker_labels) || '{}') || {}; } catch { return {}; }
}

// Seed names from acoustic identification WITHOUT clobbering existing (manual) ones.
export async function mergeSpeakerNames(id, map) {
  if (!map || !Object.keys(map).length) return;
  const m = await getSpeakerNames(id);
  let changed = false;
  for (const k of Object.keys(map)) if (!m[k] && map[k]) { m[k] = map[k]; changed = true; }
  if (changed) await pool.execute('UPDATE recordings SET speaker_names=? WHERE id=?', [JSON.stringify(m), id]);
}

// Store the LLM result. `todos` is an array → persisted as JSON text.
// A user-typed title (title_locked=1) survives every re-analysis: without the
// CASE the LLM would silently rename the recording back on the next run.
export async function updateAnalysis(id, { title, summary, todos, participants, transcriptClean, status }) {
  const st = status || 'done';
  await pool.execute(
    `UPDATE recordings SET title=CASE WHEN title_locked=1 THEN title ELSE COALESCE(?,title) END,
       summary=?, todos=?, participants=?, transcript_clean=?, analysis_status=?,
       analysis_ms=CASE WHEN ?='done' AND analysis_started_at IS NOT NULL THEN ?-analysis_started_at ELSE analysis_ms END
     WHERE id=?`,
    [title || null, summary || null, todos ? JSON.stringify(todos) : null,
     participants ? JSON.stringify(participants) : null, transcriptClean || null, st,
     st, Date.now(), id]);
}

// --- title rename ------------------------------------------------------------
// Empty/blank title unlocks again: the next analysis is then free to propose one.
export async function renameRecording(id, title) {
  const t = (title || '').trim().slice(0, 255);
  await pool.execute('UPDATE recordings SET title=?, title_locked=? WHERE id=?', [t || null, t ? 1 : 0, id]);
  const [rows] = await pool.query('SELECT id,title,title_locked FROM recordings WHERE id=?', [id]);
  return rows[0] || null;
}

// --- Kontextnotiz ------------------------------------------------------------
// Leerer Text löscht die Notiz wieder (dann fällt die Analyse auf reines
// Transkript zurück). Deckel bei CONTEXT_MAX: die Spalte ist TEXT (64 KB), aber
// die Notiz landet in JEDEM Chunk-Prompt — ein Roman darin verdrängt Transkript.
export const CONTEXT_MAX = 4000;
export async function setContextNote(id, note) {
  const n = (note || '').toString().trim().slice(0, CONTEXT_MAX);
  await pool.execute(
    'UPDATE recordings SET context_note=?, context_note_at=IF(?<>\'\', CURRENT_TIMESTAMP, NULL) WHERE id=?',
    [n || null, n, id]);
  const [rows] = await pool.query(
    'SELECT id,context_note,context_note_at,status,analysis_status FROM recordings WHERE id=?', [id]);
  return rows[0] || null;
}

// Nur die Notiz nachladen. Die Analyse liest sie so SPÄT wie möglich (kurz vor
// dem LLM-Aufruf statt beim Start): Sprecher-Erkennung und Whisper laufen davor
// minutenlang, und was der Nutzer in dieser Zeit noch tippt, soll mitzählen.
export async function getContextNote(id) {
  const [rows] = await pool.query('SELECT context_note FROM recordings WHERE id=?', [id]);
  return (rows[0] && rows[0].context_note) || '';
}

function parseJsonCols(row) {
  for (const c of ['todos', 'participants']) {
    if (row && typeof row[c] === 'string') {
      try { row[c] = JSON.parse(row[c]); } catch { row[c] = []; }
    }
  }
  return row;
}
const parseTodos = parseJsonCols;

const LIST_COLS = 'id,title,mp3_file,device,duration_ms,language,status,summary,todos,participants,analysis_status,analysis_started_at,analysis_ms,created_at';

export async function listRecordings() {
  const [rows] = await pool.query(
    `SELECT ${LIST_COLS} FROM recordings WHERE deleted_at IS NULL ORDER BY id DESC`);
  return rows.map(parseTodos);
}

export async function getRecording(id) {
  const [recRows] = await pool.query('SELECT * FROM recordings WHERE id=?', [id]);
  const rec = recRows[0];
  if (!rec) return null;
  const [words] = await pool.query(
    'SELECT word,t_start_ms,t_end_ms,idx,speaker FROM words WHERE recording_id=? ORDER BY idx', [id]);
  let speaker_names = {};
  try { speaker_names = JSON.parse(rec.speaker_names || '{}') || {}; } catch {}
  const markers = await listMarkers(id).catch(() => []);
  return { ...parseTodos(rec), speaker_names, words, markers };
}

// --- trash -------------------------------------------------------------------
// Deleting only stamps `deleted_at`; nothing is destroyed. Everything (words,
// markers, passages, wav/mp3) stays put so a restore is complete, not partial.
export const TRASH_DAYS = +(process.env.AIME_TRASH_DAYS || 30);

export async function deleteRecording(id) {
  await pool.execute('UPDATE recordings SET deleted_at=CURRENT_TIMESTAMP WHERE id=? AND deleted_at IS NULL', [id]);
}

export async function restoreRecording(id) {
  await pool.execute('UPDATE recordings SET deleted_at=NULL WHERE id=?', [id]);
}

// Binned recordings, newest first, with the days left before auto-purge.
export async function listTrash() {
  const [rows] = await pool.query(
    `SELECT ${LIST_COLS}, deleted_at,
       GREATEST(0, ? - TIMESTAMPDIFF(DAY, deleted_at, CURRENT_TIMESTAMP)) AS days_left
     FROM recordings WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC`, [TRASH_DAYS]);
  return rows.map(parseTodos);
}

// Hard delete. Returns the audio file names so the caller can unlink them —
// db.js deliberately does no filesystem work. Note this also clears the `words`
// rows, which the pre-trash delete leaked (they outlived their recording).
export async function purgeRecording(id) {
  const [rows] = await pool.query('SELECT mp3_file, wav_file FROM recordings WHERE id=?', [id]);
  const files = rows[0] ? [rows[0].mp3_file, rows[0].wav_file].filter(Boolean) : [];
  for (const t of ['markers', 'words', 'passages', 'project_pins'])
    await pool.query(`DELETE FROM ${t} WHERE recording_id=?`, [id]).catch(() => {});
  await pool.query('DELETE FROM recordings WHERE id=?', [id]);
  return files;
}

// Jede Audiodatei, auf die noch eine Zeile zeigt — Papierkorb eingeschlossen,
// dessen Dateien bleiben ja absichtlich liegen. Alles andere in REC_DIR ist
// verwaist (siehe sweepOrphans in server.js).
export async function allAudioFiles() {
  const [rows] = await pool.query(
    'SELECT mp3_file AS f FROM recordings WHERE mp3_file IS NOT NULL' +
    ' UNION SELECT wav_file FROM recordings WHERE wav_file IS NOT NULL');
  return rows.map(r => r.f);
}

// Ids whose retention window has run out. The caller purges them one by one so
// a single bad row cannot abort the whole sweep.
export async function expiredTrashIds() {
  const [rows] = await pool.query(
    'SELECT id FROM recordings WHERE deleted_at IS NOT NULL AND deleted_at < (CURRENT_TIMESTAMP - INTERVAL ? DAY)',
    [TRASH_DAYS]);
  return rows.map(r => r.id);
}