// Third-party REST API (v1) for the NextJobs.
//
// Mounted under /api/v1 in server.js. All endpoints except /health require an
// API key sent in the X-API-Key header. The key is per-user: it is the
// API_TOKEN the user saved on /einstellungen. The auth step resolves the key to
// the owning user, then runs every request inside that user's context
// (lib/context.js) so all queries + file paths are scoped to that user — the
// third-party software only ever sees and creates the user's own data.

const express = require('express');
const path = require('path');
const fs = require('fs');
const blacklist = require('./blacklist');
const { LABEL_OPTIONS, parseLabels, serializeLabels } = require('./labels');
const { normalizeDokumente, standardDokumente } = require('./documents');
const { userContext, currentUserId } = require('./context');
const config = require('./config');

const CONFIG_PREFIX = 'cfg:';

// Replace the stored labels JSON string with a real array on outgoing rows.
function withLabels(row) {
  if (row) row.labels = parseLabels(row.labels);
  return row;
}

const STATUS_OPTIONS = [
  'Entwurf', 'Gesendet', 'Eingangsbestätigung',
  'In Bearbeitung', 'Interessiert', 'Telefonat', 'Warten auf Rückmeldung', 'Warten auf meine Antwort',
  'Vorstellungsgespräch', 'Vertragsverhandlung',
  'Absage', 'Absage von meiner Seite', 'Einstellung', 'Keine Rückmeldung',
];

function createExternalApi(deps) {
  const {
    dbGet,
    dbAll,
    dbRun,
    sanitizeInput,
    attachVerlauf,
    findDuplicateApplications,
    syncCurrentStatus,
    runGeneration,
    anhaengeDir,
    emailAnhaengeDir,
    userStorageDir,
  } = deps;

  const router = express.Router();

  // The current user's id, resolved from the per-request context set below.
  const uid = () => currentUserId();

  // --- API key auth --------------------------------------------------
  // /health is public so monitoring tools can probe availability. Every other
  // endpoint resolves the X-API-Key to the user who owns that token (stored in
  // app_state as cfg:API_TOKEN), then wraps the rest of the request in that
  // user's context so all queries/files are scoped to them. A missing or
  // unknown key yields 401.
  router.use(async (req, res, next) => {
    if (req.path === '/health') return next();
    const provided = req.get('X-API-Key');
    if (!provided) {
      return res.status(401).json({ error: 'Ungültiger oder fehlender API-Key (Header: X-API-Key).' });
    }
    try {
      // Resolve the token to its owning user (the user whose cfg:API_TOKEN
      // matches). An empty token is never a valid key — otherwise every user who
      // has saved their settings once (which stores API_TOKEN as '') would be
      // matched by an empty header.
      const matches = await dbAll(
        `SELECT u.id, u.username, u.is_admin
           FROM app_state a JOIN users u ON u.id = a.user_id
          WHERE a.key = ? AND a.value = ? AND a.value != ''`,
        [CONFIG_PREFIX + 'API_TOKEN', provided]
      );
      // Fail closed on an ambiguous token: if two users somehow share a value we
      // must not silently pick one of them and hand the caller that user's data.
      // (Saving a token that another user already uses is rejected in the UI.)
      if (matches.length !== 1) {
        if (matches.length > 1) {
          console.error('API auth: Token ist mehreren Benutzern zugeordnet — Zugriff verweigert.');
        }
        return res.status(401).json({ error: 'Ungültiger oder fehlender API-Key (Header: X-API-Key).' });
      }
      const user = matches[0];
      req.user = user;
      // Warm this user's cfg rows: config.get() is synchronous and reads from
      // the per-user cache, so without this an API request could see the user
      // as unconfigured (e.g. no Ollama key) purely because nothing had loaded
      // their rows yet in this process.
      await config.ensureLoaded(user.id);
      // Run the remainder of the request inside this user's context so
      // currentUserId() / config.get() / the scoped helpers all resolve here.
      userContext.run(user, next);
    } catch (err) {
      console.error('API auth error:', err);
      res.status(401).json({ error: 'Ungültiger oder fehlender API-Key (Header: X-API-Key).' });
    }
  });

  // --- helpers -------------------------------------------------------
  async function getApplication(id) {
    return dbGet('SELECT * FROM bewerbungen WHERE id = ? AND user_id = ?', [id, uid()]);
  }

  // --- System --------------------------------------------------------
  router.get('/health', (req, res) => {
    res.json({ status: 'ok', api: 'bewerbungs-tracker/v1' });
  });

  // --- Applications --------------------------------------------------
  router.get('/applications', async (req, res) => {
    try {
      const { month, year, status, art, search, label } = req.query;
      const limit = Math.min(Math.max(parseInt(req.query.limit, 10) || 100, 1), 500);
      const offset = Math.max(parseInt(req.query.offset, 10) || 0, 0);

      const where = ['user_id = ?'];
      const params = [uid()];
      if (label && LABEL_OPTIONS.includes(label)) {
        // labels is a JSON array string; match the quoted label token.
        where.push('labels LIKE ?');
        params.push(`%"${label}"%`);
      }
      if (month) {
        where.push('strftime("%m", datum) = ?');
        params.push(String(month).padStart(2, '0'));
      }
      if (year) {
        where.push('strftime("%Y", datum) = ?');
        params.push(String(year));
      }
      if (status) {
        where.push('status = ?');
        params.push(status);
      }
      if (art) {
        where.push('art = ?');
        params.push(art);
      }
      if (search) {
        where.push('(firma LIKE ? OR stelle LIKE ?)');
        const term = `%${search}%`;
        params.push(term, term);
      }

      const clause = `WHERE ${where.join(' AND ')}`;
      const applications = await dbAll(
        `SELECT * FROM bewerbungen ${clause} ORDER BY datum DESC, created_at DESC LIMIT ? OFFSET ?`,
        [...params, limit, offset]
      );
      await attachVerlauf(applications);
      applications.forEach(withLabels);
      res.json(applications);
    } catch (error) {
      console.error('API list applications error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.post('/applications', async (req, res) => {
    try {
      const b = req.body || {};
      const { firma, stelle } = b;
      if (!firma || !stelle) {
        return res.status(400).json({ error: 'firma und stelle sind erforderlich.' });
      }
      const datum = b.datum || new Date().toISOString().split('T')[0];

      const force = b.force === true || b.force === 'true';
      if (!force) {
        const dups = await findDuplicateApplications({ firma, stelle });
        if (dups.length) {
          return res.status(409).json({
            duplicate: true,
            matches: dups,
            error: 'Es gibt bereits eine Bewerbung für dieselbe Firma und Stelle.',
          });
        }
      }

      const result = await dbRun(
        `INSERT INTO bewerbungen
           (user_id, datum, firma, stelle, art, status, notizen, interne_notizen, ort,
            stellenbeschreibung, quelle_url, llm_notizen, labels, generierung_status)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'nicht_gestartet')`,
        [
          uid(),
          datum,
          sanitizeInput(firma),
          sanitizeInput(stelle),
          sanitizeInput(b.art || ''),
          sanitizeInput(b.status || ''),
          sanitizeInput(b.notizen || ''),
          sanitizeInput(b.interne_notizen || ''),
          sanitizeInput(b.ort || ''),
          sanitizeInput(b.stellenbeschreibung || ''),
          sanitizeInput(b.quelle_url || ''),
          sanitizeInput(b.llm_notizen || ''),
          serializeLabels(b.labels),
        ]
      );

      if (b.status && String(b.status).trim()) {
        await dbRun(
          'INSERT INTO status_verlauf (user_id, bewerbung_id, datum, status, kommentar) VALUES (?, ?, ?, ?, ?)',
          [uid(), result.lastID, datum, sanitizeInput(b.status), sanitizeInput(b.kommentar || '')]
        );
      }

      const application = await dbGet('SELECT * FROM bewerbungen WHERE id = ? AND user_id = ?', [result.lastID, uid()]);
      res.json({ success: true, application: withLabels(application) });
    } catch (error) {
      console.error('API create application error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.get('/applications/:id', async (req, res) => {
    try {
      const application = await getApplication(req.params.id);
      if (!application) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });
      await attachVerlauf([application]);
      res.json(withLabels(application));
    } catch (error) {
      console.error('API get application error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.put('/applications/:id', async (req, res) => {
    try {
      const { id } = req.params;
      const existing = await getApplication(id);
      if (!existing) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });

      const b = req.body || {};
      if (!b.firma || !b.stelle || !b.datum) {
        return res.status(400).json({ error: 'datum, firma und stelle sind erforderlich.' });
      }

      await dbRun(
        `UPDATE bewerbungen SET
           datum = ?, firma = ?, stelle = ?, art = ?, status = ?, notizen = ?,
           interne_notizen = ?, ort = ?, stellenbeschreibung = ?, quelle_url = ?,
           llm_notizen = ?, labels = ?, updated_at = CURRENT_TIMESTAMP
         WHERE id = ? AND user_id = ?`,
        [
          b.datum,
          sanitizeInput(b.firma),
          sanitizeInput(b.stelle),
          sanitizeInput(b.art ?? existing.art),
          sanitizeInput(b.status ?? existing.status),
          sanitizeInput(b.notizen ?? existing.notizen),
          sanitizeInput(b.interne_notizen ?? existing.interne_notizen),
          sanitizeInput(b.ort ?? existing.ort),
          sanitizeInput(b.stellenbeschreibung ?? existing.stellenbeschreibung),
          sanitizeInput(b.quelle_url ?? existing.quelle_url),
          sanitizeInput(b.llm_notizen ?? existing.llm_notizen),
          Object.prototype.hasOwnProperty.call(b, 'labels')
            ? serializeLabels(b.labels)
            : (existing.labels || '[]'),
          id,
          uid(),
        ]
      );

      const application = await dbGet('SELECT * FROM bewerbungen WHERE id = ? AND user_id = ?', [id, uid()]);
      res.json({ success: true, application: withLabels(application) });
    } catch (error) {
      console.error('API update application error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.delete('/applications/:id', async (req, res) => {
    try {
      const { id } = req.params;
      const existing = await getApplication(id);
      if (!existing) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });

      await dbRun('DELETE FROM status_verlauf WHERE bewerbung_id = ? AND user_id = ?', [id, uid()]);
      await dbRun('DELETE FROM bewerbungen WHERE id = ? AND user_id = ?', [id, uid()]);
      res.json({ success: true });
    } catch (error) {
      console.error('API delete application error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Timeline ------------------------------------------------------
  router.get('/applications/:id/timeline', async (req, res) => {
    try {
      const { id } = req.params;
      if (!(await getApplication(id))) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });
      const verlauf = await dbAll(
        'SELECT * FROM status_verlauf WHERE bewerbung_id = ? AND user_id = ? ORDER BY date(datum) ASC, id ASC',
        [id, uid()]
      );
      res.json(verlauf);
    } catch (error) {
      console.error('API list timeline error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.post('/applications/:id/timeline', async (req, res) => {
    try {
      const { id } = req.params;
      if (!(await getApplication(id))) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });

      const { status, datum, kommentar } = req.body || {};
      if (!status || !STATUS_OPTIONS.includes(status)) {
        return res.status(400).json({ error: `status erforderlich (eine von: ${STATUS_OPTIONS.join(', ')})` });
      }
      const day = datum || new Date().toISOString().split('T')[0];

      const result = await dbRun(
        'INSERT INTO status_verlauf (user_id, bewerbung_id, datum, status, kommentar) VALUES (?, ?, ?, ?, ?)',
        [uid(), id, day, status, sanitizeInput(kommentar || '')]
      );
      await syncCurrentStatus(id);

      const entry = await dbGet('SELECT * FROM status_verlauf WHERE id = ? AND user_id = ?', [result.lastID, uid()]);
      res.json(entry);
    } catch (error) {
      console.error('API add timeline error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.delete('/applications/:id/timeline/:eintragId', async (req, res) => {
    try {
      const { id, eintragId } = req.params;
      const entry = await dbGet(
        'SELECT id FROM status_verlauf WHERE id = ? AND bewerbung_id = ? AND user_id = ?',
        [eintragId, id, uid()]
      );
      if (!entry) return res.status(404).json({ error: 'Verlaufseintrag nicht gefunden' });

      await dbRun('DELETE FROM status_verlauf WHERE id = ? AND user_id = ?', [eintragId, uid()]);
      await syncCurrentStatus(id);
      res.json({ success: true });
    } catch (error) {
      console.error('API delete timeline error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Attachments ---------------------------------------------------
  router.get('/applications/:id/attachments', async (req, res) => {
    try {
      const { id } = req.params;
      if (!(await getApplication(id))) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });
      const anhaenge = await dbAll(
        'SELECT id, bewerbung_id, name, dateiname, mime, created_at FROM anhaenge WHERE bewerbung_id = ? AND user_id = ? ORDER BY id ASC',
        [id, uid()]
      );
      res.json(anhaenge);
    } catch (error) {
      console.error('API list attachments error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.get('/applications/:id/attachments/:attachmentId', async (req, res) => {
    try {
      const { id, attachmentId } = req.params;
      const anhang = await dbGet(
        'SELECT * FROM anhaenge WHERE id = ? AND bewerbung_id = ? AND user_id = ?',
        [attachmentId, id, uid()]
      );
      if (!anhang) return res.status(404).json({ error: 'Anhang nicht gefunden' });

      const file = path.join(userStorageDir(anhaengeDir), anhang.pfad);
      if (!fs.existsSync(file)) return res.status(404).json({ error: 'Datei nicht auf Festplatte vorhanden' });

      res.download(file, anhang.dateiname || anhang.name || path.basename(anhang.pfad));
    } catch (error) {
      console.error('API download attachment error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Emails --------------------------------------------------------
  router.get('/applications/:id/emails', async (req, res) => {
    try {
      const { id } = req.params;
      if (!(await getApplication(id))) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });

      const emails = await dbAll(
        'SELECT * FROM emails WHERE bewerbung_id = ? AND user_id = ? ORDER BY datetime(email_date) ASC, id ASC',
        [id, uid()]
      );
      if (emails.length) {
        const eIds = emails.map((e) => e.id);
        const atts = await dbAll(
          `SELECT id, email_id, name, mime FROM email_anhaenge WHERE user_id = ? AND email_id IN (${eIds.map(() => '?').join(',')})`,
          [uid(), ...eIds]
        );
        const byEmail = {};
        atts.forEach((a) => { (byEmail[a.email_id] = byEmail[a.email_id] || []).push(a); });
        emails.forEach((e) => { e.anhaenge = byEmail[e.id] || []; });
      }
      res.json(emails);
    } catch (error) {
      console.error('API list emails error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.get('/emails/:emailId/attachments/:attachmentId', async (req, res) => {
    try {
      const { emailId, attachmentId } = req.params;
      const anhang = await dbGet(
        'SELECT * FROM email_anhaenge WHERE id = ? AND email_id = ? AND user_id = ?',
        [attachmentId, emailId, uid()]
      );
      if (!anhang) return res.status(404).json({ error: 'Anhang nicht gefunden' });

      const file = path.join(userStorageDir(emailAnhaengeDir), anhang.pfad);
      if (!fs.existsSync(file)) return res.status(404).json({ error: 'Datei nicht auf Festplatte vorhanden' });

      res.download(file, anhang.name || path.basename(anhang.pfad));
    } catch (error) {
      console.error('API download email attachment error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Generation ----------------------------------------------------
  router.post('/applications/:id/generate', async (req, res) => {
    try {
      const { id } = req.params;
      const bewerbung = await getApplication(id);
      if (!bewerbung) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });

      if (typeof (req.body || {}).llm_notizen !== 'undefined') {
        await dbRun('UPDATE bewerbungen SET llm_notizen = ? WHERE id = ? AND user_id = ?', [req.body.llm_notizen || '', id, uid()]);
      }

      // Drop existing generated attachments + their files before re-generating.
      const alte = await dbAll('SELECT * FROM anhaenge WHERE bewerbung_id = ? AND user_id = ?', [id, uid()]);
      const dir = userStorageDir(anhaengeDir);
      for (const a of alte) {
        fs.promises.unlink(path.join(dir, a.pfad)).catch(() => {});
      }
      await dbRun('DELETE FROM anhaenge WHERE bewerbung_id = ? AND user_id = ?', [id, uid()]);
      await dbRun(
        "UPDATE bewerbungen SET generierung_status = 'ausstehend', generierung_fehler = NULL WHERE id = ? AND user_id = ?",
        [id, uid()]
      );

      // Optional: IDs of static attachments (basis_anhaenge) to enclose. Omitted
      // means the user's preselection from the settings (GEN_ANLAGEN_DEFAULT,
      // default: none); an empty array means explicitly none. Also reflected in
      // the cover letter's "Anlagen" list.
      const anlagenIds = Array.isArray((req.body || {}).anlagen)
        ? req.body.anlagen.map((v) => parseInt(v, 10)).filter((n) => !Number.isNaN(n))
        : config.anlagenDefaultIds();
      // Optional: which documents to produce (["anschreiben"], ["lebenslauf"] or
      // both). Omitted / empty falls back to the user's preselection from the
      // settings (GEN_DOKUMENTE_DEFAULT, default: both).
      const gewuenscht = (req.body || {}).dokumente;
      const dokumente = gewuenscht == null ? standardDokumente() : normalizeDokumente(gewuenscht);
      await dbRun('UPDATE bewerbungen SET generierung_dokumente = ? WHERE id = ? AND user_id = ?', [dokumente.join(','), id, uid()]);
      runGeneration(id, { anlagenIds, dokumente });
      res.status(202).json({ success: true, dokumente });
    } catch (error) {
      console.error('API generate error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.get('/applications/:id/generation-status', async (req, res) => {
    try {
      const { id } = req.params;
      const bewerbung = await dbGet(
        'SELECT id, generierung_status, generierung_fehler FROM bewerbungen WHERE id = ? AND user_id = ?',
        [id, uid()]
      );
      if (!bewerbung) return res.status(404).json({ error: 'Bewerbung nicht gefunden' });

      const anhaenge = await dbAll(
        'SELECT id, bewerbung_id, name, dateiname, mime, created_at FROM anhaenge WHERE bewerbung_id = ? AND user_id = ? ORDER BY id ASC',
        [id, uid()]
      );
      res.json({ status: bewerbung.generierung_status, fehler: bewerbung.generierung_fehler, anhaenge });
    } catch (error) {
      console.error('API generation status error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Settings ------------------------------------------------------
  router.get('/settings', async (req, res) => {
    try {
      // A user who never saved their personal details has no settings row; that
      // is a valid state, so answer with an empty object instead of no body.
      const settings = await dbGet('SELECT * FROM settings WHERE user_id = ?', [uid()]);
      res.json(settings || {});
    } catch (error) {
      console.error('API get settings error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // Personal details, same set of fields the web UI writes (Persönliche Angaben).
  // Only the keys present in the body are changed; omitted keys keep their value,
  // so a client can patch a single field without wiping the rest.
  const SETTINGS_FIELDS = ['name', 'adresse', 'kundennummer', 'email', 'telefon', 'ort', 'webseite', 'geburtsdatum'];

  router.put('/settings', async (req, res) => {
    try {
      const b = req.body || {};
      const keys = SETTINGS_FIELDS.filter((k) => typeof b[k] !== 'undefined');
      if (!keys.length) {
        return res.status(400).json({ error: `Mindestens eines dieser Felder erforderlich: ${SETTINGS_FIELDS.join(', ')}` });
      }
      // Upsert, not UPDATE: a user without a settings row would otherwise match
      // zero rows and the write would be silently dropped.
      const werte = keys.map((k) => sanitizeInput(String(b[k] ?? '')));
      await dbRun(
        `INSERT INTO settings (user_id, ${keys.join(', ')})
         VALUES (?, ${keys.map(() => '?').join(', ')})
         ON CONFLICT(user_id) DO UPDATE SET
           ${keys.map((k) => `${k} = excluded.${k}`).join(', ')}`,
        [uid(), ...werte]
      );
      const row = await dbGet('SELECT * FROM settings WHERE user_id = ?', [uid()]);
      res.json({ success: true, settings: row || {} });
    } catch (error) {
      console.error('API save settings error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Statistics & export -------------------------------------------
  router.get('/statistics', async (req, res) => {
    try {
      const totalCount = await dbGet('SELECT COUNT(*) as count FROM bewerbungen WHERE user_id = ?', [uid()]);
      const byArt = await dbAll(
        `SELECT art, COUNT(*) as count FROM bewerbungen
          WHERE user_id = ? AND art IS NOT NULL AND art != ''
          GROUP BY art ORDER BY count DESC`,
        [uid()]
      );
      const byStatus = await dbAll(
        `SELECT status, COUNT(*) as count FROM bewerbungen
          WHERE user_id = ? AND status IS NOT NULL AND status != ''
          GROUP BY status ORDER BY count DESC`,
        [uid()]
      );
      res.json({
        total: totalCount ? totalCount.count : 0,
        byArt,
        byStatus,
      });
    } catch (error) {
      console.error('API statistics error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.get('/export', async (req, res) => {
    try {
      const { month, year } = req.query;
      let query = 'SELECT * FROM bewerbungen WHERE user_id = ? ORDER BY datum DESC';
      const params = [uid()];
      if (month && year) {
        query = 'SELECT * FROM bewerbungen WHERE user_id = ? AND strftime("%m", datum) = ? AND strftime("%Y", datum) = ? ORDER BY datum DESC';
        params.push(String(month).padStart(2, '0'), String(year));
      } else if (month) {
        query = 'SELECT * FROM bewerbungen WHERE user_id = ? AND strftime("%m", datum) = ? ORDER BY datum DESC';
        params.push(String(month).padStart(2, '0'));
      } else if (year) {
        query = 'SELECT * FROM bewerbungen WHERE user_id = ? AND strftime("%Y", datum) = ? ORDER BY datum DESC';
        params.push(String(year));
      }

      const applications = await dbAll(query, params);
      await attachVerlauf(applications);
      // Internal notes never leave the tracker via export.
      applications.forEach((a) => { delete a.interne_notizen; });
      res.json(applications);
    } catch (error) {
      console.error('API export error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Templates -----------------------------------------------------
  router.get('/templates', async (req, res) => {
    try {
      const docs = await dbAll('SELECT * FROM basis_dokumente WHERE user_id = ? ORDER BY id ASC', [uid()]);
      res.json(docs);
    } catch (error) {
      console.error('API list templates error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Job offers (ingested by third-party software) -----------------
  // POST upserts by (user_id, quelle, external_id): re-sending the same offer
  // updates it instead of creating a duplicate. `quelle` defaults to
  // "drittanbieter".
  router.get('/joboffers', async (req, res) => {
    try {
      const rows = await dbAll(
        'SELECT * FROM jobangebote WHERE user_id = ? ORDER BY created_at DESC, id DESC',
        [uid()]
      );
      rows.forEach(withLabels);
      res.json(rows);
    } catch (error) {
      console.error('API list job offers error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // --- Blacklist (must be registered BEFORE /joboffers/:id) ----------
  // Blocks offers from ever (re)appearing. See lib/blacklist for the match
  // rules. A deleted offer is auto-blacklisted so it can never return.
  router.get('/joboffers/blacklist', async (req, res) => {
    try {
      const rows = await dbAll(
        'SELECT * FROM jobangebote_blacklist WHERE user_id = ? ORDER BY created_at DESC, id DESC',
        [uid()]
      );
      res.json(rows);
    } catch (error) {
      console.error('API list blacklist error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.post('/joboffers/blacklist', async (req, res) => {
    try {
      const b = req.body || {};
      const entry = blacklist.buildManualEntry({
        typ: b.typ,
        wert: b.wert != null ? sanitizeInput(String(b.wert)) : '',
        firma: sanitizeInput(b.firma || ''),
        stelle: sanitizeInput(b.stelle || ''),
        ort: sanitizeInput(b.ort || ''),
        grund: sanitizeInput(b.grund || ''),
      });
      if (!entry) {
        return res.status(400).json({
          error: `Ungültiger Blacklist-Eintrag. typ muss eine von ${blacklist.TYPES.join(', ')} sein und einen passenden Wert haben.`,
        });
      }
      const cols = blacklist.COLUMNS;
      const result = await dbRun(
        `INSERT INTO jobangebote_blacklist (user_id, ${cols.join(', ')}) VALUES (?, ${cols.map(() => '?').join(', ')})`,
        [uid(), ...cols.map((c) => (entry[c] === undefined ? null : entry[c]))]
      );
      const row = await dbGet('SELECT * FROM jobangebote_blacklist WHERE id = ? AND user_id = ?', [result.lastID, uid()]);
      res.status(201).json({ success: true, entry: row });
    } catch (error) {
      console.error('API create blacklist entry error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.delete('/joboffers/blacklist/:id', async (req, res) => {
    try {
      const row = await dbGet('SELECT id FROM jobangebote_blacklist WHERE id = ? AND user_id = ?', [req.params.id, uid()]);
      if (!row) return res.status(404).json({ error: 'Blacklist-Eintrag nicht gefunden' });
      await dbRun('DELETE FROM jobangebote_blacklist WHERE id = ? AND user_id = ?', [req.params.id, uid()]);
      res.json({ success: true });
    } catch (error) {
      console.error('API delete blacklist entry error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.get('/joboffers/:id', async (req, res) => {
    try {
      const row = await dbGet('SELECT * FROM jobangebote WHERE id = ? AND user_id = ?', [req.params.id, uid()]);
      if (!row) return res.status(404).json({ error: 'Jobangebot nicht gefunden' });
      res.json(withLabels(row));
    } catch (error) {
      console.error('API get job offer error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  router.post('/joboffers', async (req, res) => {
    try {
      const b = req.body || {};
      if (!b.firma || !b.stelle) {
        return res.status(400).json({ error: 'firma und stelle sind erforderlich.' });
      }

      // Reject blacklisted offers outright — they must never reappear.
      const blacklistRows = await dbAll('SELECT * FROM jobangebote_blacklist WHERE user_id = ?', [uid()]);
      const blocked = blacklist.matchBlacklist(blacklistRows, b);
      if (blocked) {
        return res.status(409).json({
          blacklisted: true,
          matched_by: blocked.typ,
          blacklist_entry: blocked,
          error: 'Dieses Jobangebot steht auf der Blacklist und wird nicht (erneut) aufgenommen.',
        });
      }

      const quelle = sanitizeInput(b.quelle || 'drittanbieter');
      const externalId = b.external_id != null ? sanitizeInput(String(b.external_id)) : null;
      const urlNorm = blacklist.normalizeUrl(b.quelle_url || '') || null;
      const anzeigeDatum = sanitizeInput(b.anzeige_datum || '');
      const kontaktEmail = sanitizeInput(b.kontakt_email || '');
      // Company slug: stored as supplied by the client; derived from firma only
      // when omitted. This is the key the blacklist matches on (see below).
      const firmaSlug = b.firma_slug != null && String(b.firma_slug).trim() !== ''
        ? sanitizeInput(String(b.firma_slug))
        : (blacklist.firmaSlug(b.firma) || null);
      // Column order matches the UPDATE/INSERT statements below (firma_slug last).
      const fields = [
        sanitizeInput(b.firma),
        sanitizeInput(b.stelle),
        sanitizeInput(b.ort || ''),
        sanitizeInput(b.adresse || ''),
        sanitizeInput(b.ansprechpartner || ''),
        sanitizeInput(b.gehalt || ''),
        sanitizeInput(b.beschreibung || ''),
        sanitizeInput(b.quelle_url || ''),
        sanitizeInput(b.art || ''),
        anzeigeDatum,
        kontaktEmail,
        sanitizeInput(b.status || 'offen'),
        serializeLabels(b.labels),
        urlNorm,
        firmaSlug,
      ];

      // De-dup: prefer a (user_id, quelle, external_id) match, else the same
      // normalized URL — so the same posting never lands twice, even with a
      // new id.
      let existing = null;
      if (externalId) {
        existing = await dbGet(
          'SELECT id FROM jobangebote WHERE user_id = ? AND quelle = ? AND external_id = ?',
          [uid(), quelle, externalId]
        );
      }
      if (!existing && urlNorm) {
        existing = await dbGet(
          'SELECT id FROM jobangebote WHERE user_id = ? AND url_norm = ? ORDER BY id ASC LIMIT 1',
          [uid(), urlNorm]
        );
      }

      if (existing) {
        await dbRun(
          `UPDATE jobangebote SET firma = ?, stelle = ?, ort = ?, adresse = ?, ansprechpartner = ?, gehalt = ?,
             beschreibung = ?, quelle_url = ?, art = ?, anzeige_datum = ?, kontakt_email = ?, status = ?,
             labels = ?, url_norm = ?, firma_slug = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ?`,
          [...fields, existing.id, uid()]
        );
        const row = await dbGet('SELECT * FROM jobangebote WHERE id = ? AND user_id = ?', [existing.id, uid()]);
        return res.json({ success: true, action: 'updated', joboffer: withLabels(row) });
      }

      const result = await dbRun(
        `INSERT INTO jobangebote
           (user_id, external_id, quelle, firma, stelle, ort, adresse, ansprechpartner, gehalt, beschreibung, quelle_url, art, anzeige_datum, kontakt_email, status, labels, url_norm, firma_slug)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [uid(), externalId, quelle, ...fields]
      );
      const row = await dbGet('SELECT * FROM jobangebote WHERE id = ? AND user_id = ?', [result.lastID, uid()]);
      res.status(201).json({ success: true, action: 'created', joboffer: withLabels(row) });
    } catch (error) {
      console.error('API create job offer error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  // Delete a job offer. By default it is auto-blacklisted first so it can never
  // be ingested again; pass ?blacklist=false to hard-delete without blocking.
  router.delete('/joboffers/:id', async (req, res) => {
    try {
      const row = await dbGet('SELECT * FROM jobangebote WHERE id = ? AND user_id = ?', [req.params.id, uid()]);
      if (!row) return res.status(404).json({ error: 'Jobangebot nicht gefunden' });

      const skipBlacklist = req.query.blacklist === 'false' || req.query.blacklist === '0';
      let blacklisted = false;
      if (!skipBlacklist) {
        const rows = await dbAll('SELECT * FROM jobangebote_blacklist WHERE user_id = ?', [uid()]);
        if (!blacklist.matchBlacklist(rows, row)) {
          const cols = blacklist.COLUMNS;
          const entry = blacklist.buildAutoEntry(row, 'Jobangebot gelöscht (REST-API)');
          await dbRun(
            `INSERT INTO jobangebote_blacklist (user_id, ${cols.join(', ')}) VALUES (?, ${cols.map(() => '?').join(', ')})`,
            [uid(), ...cols.map((c) => (entry[c] === undefined ? null : entry[c]))]
          );
        }
        blacklisted = true;
      }
      await dbRun('DELETE FROM jobangebote WHERE id = ? AND user_id = ?', [req.params.id, uid()]);
      res.json({ success: true, blacklisted });
    } catch (error) {
      console.error('API delete job offer error:', error);
      res.status(500).json({ error: 'Serverfehler' });
    }
  });

  return router;
}

module.exports = { createExternalApi };