// server.js
const fs = require('fs');
const path = require('path');
const express = require('express');
const chokidar = require('chokidar');
const { XMLParser } = require('fast-xml-parser');
const { PrismaClient } = require('@prisma/client');
const cors = require('cors');
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const cookieParser = require('cookie-parser');
const crypto = require('crypto');
const https = require('https');

require('dotenv').config();

const WATCH_PATH = process.argv[2] || process.env.WATCH_PATH || './data';
const FRONTEND_ORIGIN = process.env.FRONTEND_ORIGIN || 'https://sekt.local';
const API_BIND = process.env.API_BIND || '0.0.0.0'; // nicht FRONTEND-Host
const API_PORT = process.env.API_PORT || 3001;

const originUrl  = new URL(FRONTEND_ORIGIN);

const ALLOWED_FEATURES = ['DOWNLOADS'];

const prisma = new PrismaClient();
const app = express();

const nodemailer = require('nodemailer');

const puppeteer = require('puppeteer');

const { Buffer } = require('buffer');

const { Agent, fetch } = require('undici');

const { PDFDocument } = require('pdf-lib');

const mailer = nodemailer.createTransport({
  host : process.env.SMTP_HOST,
  port : parseInt(process.env.SMTP_PORT || '587', 10),
  secure: false,
  auth : {
    user: process.env.SMTP_USER,
    pass: process.env.SMTP_PASS,
  },
});

const allowedHosts = [originUrl.hostname, 'localhost', '10.0.1.25', '10.0.3.6', 'kennzeichen.tegdssd.de', 'sekt.tegdssd.de', 'kennzeichen.local', 'sekt.local'];

app.use(
  cors({
    origin: (origin, cb) => {
      if (!origin) return cb(null, true);
      const { hostname } = new URL(origin);
      if (allowedHosts.includes(hostname)) return cb(null, true);
      cb(new Error('Nicht erlaubter Origin'));
    },
    credentials: true,
  })
);



app.use(cookieParser());
app.use(express.json());
app.set('trust proxy', 1);

// === SSE-Clientverwaltung ===
const sseClients = new Map();
let lastResetTimestamp = new Date();

// === Funktionen ===

function pushLogout(userId, reason = 'expired') {
  const set = sseClients.get(userId);
  if (!set) return;

  const payload = `event: logout\ndata: ${JSON.stringify({ reason })}\n\n`;
  for (const res of set) res.write(payload);
}

async function lookupImage(filename) {
  if (!filename) return null;
  const full = await findImageFile(filename);
  return full && fs.existsSync(full) ? full : null;
}

function capitalize(str) {
  return str.charAt(0).toUpperCase() + str.slice(1).toLowerCase();
}

function localDateKey(d) {
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, '0');
  const day = String(d.getDate()).padStart(2, '0');
  return `${y}-${m}-${day}`; // YYYY-MM-DD in lokaler Zeit
}

function createUnsubscribeToken(ruleId, email) {
  const secret = process.env.UNSUBSCRIBE_SECRET || 'fallback_secret';
  const base = ruleId != null ? `${ruleId}:${email}` : `all:${email}`;
  return crypto.createHmac('sha256', secret).update(base).digest('hex');
}

async function sendNotificationMail(rule, rec) {
  const toList = rule.recipients.map(r => r.email.trim()).filter(Boolean);
  if (!toList.length) {
    console.warn(`🔔 Regel ${rule.id}: keine Empfänger – Mail übersprungen.`);
    return { sent: false, reason: 'noRecipients' };
  }

  const snapPath = await lookupImage(rec.imageFile);
  const platePath = await lookupImage(rec.plateFile);

  const attachments = [];
  if (snapPath) attachments.push({ filename: path.basename(snapPath), path: snapPath });
  if (platePath) attachments.push({ filename: path.basename(platePath), path: platePath });

  let successes = [], failures = [];

  for (const email of toList) {
    // Regel-Token: löschen + neu anlegen
    await prisma.unsubscribeToken.deleteMany({
      where: { ruleId: rule.id, email }
    });

    const tokenRecord = await prisma.unsubscribeToken.create({
      data: {
        ruleId: rule.id,
        email,
        token: '', // wird gleich gesetzt
        expiresAt: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
      }
    });

    const unsubscribeSig = createUnsubscribeToken(tokenRecord.ruleId, tokenRecord.email);

    await prisma.unsubscribeToken.update({
      where: { id: tokenRecord.id },
      data: { token: unsubscribeSig },
    });

    // Globaler Token: löschen + neu anlegen
    await prisma.unsubscribeToken.deleteMany({
      where: { ruleId: null, email }
    });

    const globalTokenRecord = await prisma.unsubscribeToken.create({
      data: {
        ruleId: null,
        email,
        token: '', // wird gleich gesetzt
        expiresAt: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
      }
    });

    const globalSig = createUnsubscribeToken(null, globalTokenRecord.email);

    await prisma.unsubscribeToken.update({
      where: { id: globalTokenRecord.id },
      data: { token: globalSig },
    });

    const unsubscribeLink       = new URL(`/api/unsubscribe?id=${tokenRecord.id}&sig=${unsubscribeSig}`, FRONTEND_ORIGIN).toString();
    const globalUnsubscribeLink = new URL(`/api/unsubscribe?id=${globalTokenRecord.id}&sig=${globalSig}`, FRONTEND_ORIGIN).toString();

    const html = `
      <p>Hallo ${rule.user.username},</p>
      <p>es gibt einen neuen Treffer für deine Regel #${rule.id}:</p>
      <ul>
        <li>Kennzeichen: <b>${rec.licenseFormatted ?? rec.license}</b></li>
        ${rec.brand ? `<li>Marke: ${rec.brand}</li>` : ''}
        ${rec.model ? `<li>Modell: ${rec.model}</li>` : ''}
        <li>Kamera: ${rec.cameraName || '–'}</li>
        <li>Zeit: ${new Date(rec.timestampLocal).toLocaleString('de-DE')}</li>
      </ul>
      <p style="color:#888; font-size: 10px">
        Diese Mail wurde automatisch generiert. Bitte nicht antworten.<br>
        Du möchtest keine E-Mails mehr zu <strong>dieser Regel</strong>?<br>
        <a href="${unsubscribeLink}">Hier austragen</a><br><br>
        Du möchtest dich <strong>von allen Benachrichtigungen</strong> abmelden?<br>
        <a href="${globalUnsubscribeLink}">Alle Regeln abbestellen</a><br><br>
        Die Links sind 30 Tage gültig.
      </p>
    `;

    try {
      const info = await mailer.sendMail({
        from: process.env.MAIL_FROM,
        to: email,
        subject: `Kennzeichentreffer: ${rec.licenseFormatted ?? rec.license}`,
        html,
        attachments
      });

      if (info.rejected.length === 0) {
        successes.push(email);
        console.log(`✅ Mail an ${email} versendet`);
      } else {
        failures.push(email);
        console.warn(`⚠️ Mail an ${email} abgelehnt: ${info.response}`);
      }
    } catch (err) {
      console.error(`❌ Fehler beim Mailversand an ${email}:`, err);
      failures.push(email);
    }
  }

  return { sent: failures.length === 0, accepted: successes, rejected: failures };
}

function timeStringToMinutes(time) {
  if (!time) return null;
  const [h, m] = time.split(':').map(Number);
  if (isNaN(h) || isNaN(m)) return null;
  return h * 60 + m;
}


function matchesRule(rule, rec) {
  if (!rule.enabled) return false;

  if (rule.plates) {
    const re = new RegExp(rule.plates, 'i');
    if (!re.test(rec.license)) return false;
  }
  if (rule.brand  &&  !(rec.brand  || '').toLowerCase().includes(rule.brand.toLowerCase()))  return false;
  if (rule.model  &&  !(rec.model  || '').toLowerCase().includes(rule.model.toLowerCase()))  return false;
  if (rule.camera &&  (rec.cameraName !== rule.camera))                                       return false;

  if (rule.timeFrom && rule.timeTo) {
    const nowMinutes = new Date(rec.timestampLocal).getHours() * 60 + new Date(rec.timestampLocal).getMinutes();
    const from = timeStringToMinutes(rule.timeFrom);
    const to   = timeStringToMinutes(rule.timeTo);

    if (from != null && to != null) {
      if (from <= to) {
        if (nowMinutes < from || nowMinutes > to) return false;      // z. B. 08:00–17:00
      } else {
        if (nowMinutes > to && nowMinutes < from) return false;      // über Mitternacht, z. B. 22:00–06:00
      }
    }
  }
  return true;
}

async function verifyToken(req, res, next) {
  let token;

  if (req.cookies?.token) token = req.cookies.token;
  if (!token && req.headers.authorization?.startsWith('Bearer ')) {
    token = req.headers.authorization.substring(7);
  }
  if (!token && req.headers.cookie) {
    const cookies = req.headers.cookie.split(';');
    for (const cookie of cookies) {
      const [name, value] = cookie.trim().split('=');
      if (name === 'token') { token = value; break; }
    }
  }
  if (!token) return res.status(401).json({ error: 'Kein Token' });

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);

    const user = await prisma.user.findUnique({
      where:  { id: decoded.id },
      include:{ cameraAccess: true, features: { select: { feature: true } } },
    });
    if (!user) return res.status(401).json({ error: 'Benutzer nicht gefunden' });

    // Non-Admins: Ablauf prüfen
    if (!user.isAdmin && user.expiresAt && new Date(user.expiresAt) < new Date()) {
      pushLogout(user.id, 'expired');
      return res
        .clearCookie('token', { httpOnly: true, secure: true, sameSite: 'none' })
        .status(403).json({ error: 'Der Zugang ist abgelaufen.', logout: true });
    }

    const canDownload = user.isAdmin || user.features.some(f => f.feature === 'DOWNLOADS');

    req.user = {
      id:           user.id,
      username:     user.username,
      isAdmin:      user.isAdmin,
      cameraAccess: user.cameraAccess ?? [],
      canDownload,
    };

    // Leises Refresh, wenn < 2 Min Restlaufzeit
    const expMs = typeof decoded.exp === 'number' ? decoded.exp * 1000 : 0;
    if (expMs - Date.now() < 2 * 60 * 1000) {
      const maxAgeMs = 60 * 60 * 1000;
      const newToken = jwt.sign(
        { id: user.id, username: user.username, isAdmin: user.isAdmin, canDownload },
        process.env.JWT_SECRET,
        { expiresIn: maxAgeMs / 1000 }
      );
      res.cookie('token', newToken, {
        httpOnly: true,
        secure:   true,
        sameSite: 'none',
        maxAge:   maxAgeMs,
      });
    }

    next();
  } catch (err) {
    console.error('❌ Token-Fehler:', err);
    return res.status(401).json({ error: 'Ungültiger Token' });
  }
}

function buildCameraAccessFilter(cameraAccess, isAdmin = false) {
  if (isAdmin) return {}; // 🔓 Admins dürfen alles sehen
  
  if (!cameraAccess || cameraAccess.length === 0) {
    // Prisma WHERE-Filter, der garantiert keine Ergebnisse liefert
    return { cameraName: '__NO_ACCESS__' };
  }

  return {
    OR: cameraAccess.map(access => {
      const cond = { cameraName: access.camera };
      if (access.from || access.to) {
        cond.timestampLocal = {};
        if (access.from) cond.timestampLocal.gte = new Date(access.from);
        if (access.to) cond.timestampLocal.lte = new Date(access.to);
      }
      return cond;
    }),
  };
}

async function findAllXmlFiles(dir) {
  console.log('📄 Starte XML-Import...');

  function walk(d) {
    try {
      const entries = fs.readdirSync(d, { withFileTypes: true });
      for (const entry of entries) {
        const fullPath = path.join(d, entry.name);
        if (entry.isDirectory()) {
          walk(fullPath);
        } else if (entry.isFile() && entry.name.endsWith('_Info.xml')) {
          enqueueFile(fullPath);
        }
      }
    } catch (err) {
      console.error(`❌ Fehler beim Lesen von ${d}:`, err.message);
    }
  }

  walk(dir);
  console.log('✅ XML-Import gestartet.');
}

function enqueueFile(filePath) {
  fileQueue.push(filePath);
  if (!processing) processQueue();
}

async function processQueue() {
  processing = true;
  while (fileQueue.length > 0) {
    await processFile(fileQueue.shift());
  }
  processing = false;
}

async function findImageFile(filename) {
  const jpgFilename = filename.replace(/\.webp$/, '.jpg');

  function searchInDir(dir) {
    try {
      const entries = fs.readdirSync(dir, { withFileTypes: true });
      for (const entry of entries) {
        const fullPath = path.join(dir, entry.name);
        if (entry.isDirectory()) {
          const found = searchInDir(fullPath);
          if (found) return found;
        } else if (entry.isFile() && entry.name === jpgFilename) {
          return fullPath;
        }
      }
    } catch (err) {
      console.error('❌ Fehler beim Lesen des Verzeichnisses:', err);
    }
    return null;
  }

  return searchInDir(WATCH_PATH);
}

async function processFile(filePath) {
  try {
    const xml = fs.readFileSync(filePath, 'utf-8');
    const json = parser.parse(xml);
    const data = json?.ReturnRecognitionData?.StandardMessage?.RecognitionData;
    if (!data?.License) return;

    const baseName = path.basename(filePath).replace('_Info.xml', '');
    const dir = path.dirname(filePath);
    const jpgPath = path.join(dir, `${baseName}.jpg`);
    const platePath = path.join(dir, `${baseName}_plate.jpg`);

    const timestampUTC = new Date(
      data.DateUTC.Year, data.DateUTC.Month - 1, data.DateUTC.Day,
      data.TimeUTC.Hour, data.TimeUTC.Min, data.TimeUTC.Sec, data.TimeUTC.Msec
    );

    const timestampLocal = new Date(
      data.DateLocal.Year, data.DateLocal.Month - 1, data.DateLocal.Day,
      data.TimeLocal.Hour, data.TimeLocal.Min, data.TimeLocal.Sec, data.TimeLocal.Msec
    );

    const exists = await prisma.recognition.findFirst({
      where: { license: String(data.License), timestampUTC, timestampLocal }
    });
    if (exists) return;

    const saved = await prisma.recognition.create({
      data: {
        license: String(data.License) || null,
        licenseFormatted: String(data.LicenseFormatted) || null,
        country: data.Country || null,
        confidence: parseInt(data.Confidence || '0', 10),
        timestampUTC,
        timestampLocal,
        cameraName: String(data.CameraName) || null,
        classification: String(data.MMR?.Classification) || null,
        direction: String(data.Direction) || null,
        directionDegrees: parseInt(data.DirectionDegrees || '0', 10),
        imageFile: fs.existsSync(jpgPath) ? `${baseName}.jpg` : null,
        plateFile: fs.existsSync(platePath) ? `${baseName}_plate.jpg` : null,
        brand: data.MMR?.Make ? String(data.MMR.Make) : null || null,
        model: data.MMR?.Model ? String(data.MMR.Model) : null || null,
        brandmodelconfidence: parseInt(data.MMR?.MakeModelConfidence || '0', 10),
      },
    });

    
    const rules = await prisma.notificationRule.findMany({
      where : { enabled: true },
      include: {
        recipients: true,
        user: { select: { username: true } }
      },
    });

    for (const rule of rules) {
      if (matchesRule(rule, saved)) {
        const url = saved.imageFile
          ? new URL(`/images/${saved.imageFile}`, FRONTEND_ORIGIN).toString()
          : null;
        sendNotificationMail(rule, saved, url).catch(console.error);
      }
    }

    broadcastSSE(saved);
    console.log(`✅ Erfasst: ${saved.license}`);
  } catch (err) {
    console.error(`❌ Fehler bei ${filePath}:`, err.message);
  }
}

function generateSecurePassword(length = 12) {
  const chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!#$%()*+,-./:;=?@[]_{|}~';
  const bytes = crypto.randomBytes(length);
  const password = Array.from(bytes, (byte) => chars[byte % chars.length]).join('');
  return password;
}

// Token-Cleanup (z. B. beim Start oder via Intervall)
async function cleanUpExpiredTokens() {
  const result = await prisma.unsubscribeToken.deleteMany({
    where: {
      expiresAt: { lt: new Date() }
    }
  });
  console.log(`🧹 ${result.count} abgelaufene Tokens entfernt`);
}




function isoDateLocal(date) {
  // Offset in Millisekunden (negativ in CEST)
  const offsetMs = date.getTimezoneOffset() * 60_000;
  return new Date(date.getTime() - offsetMs).toISOString().slice(0, 10);
}

function broadcastSSE(data) {
  const payload = `event: new-recognition\ndata: ${JSON.stringify(data)}\n\n`;

  sseClients.forEach((resSet, userId) => {
    prisma.user.findUnique({
      where: { id: userId },
      include: { cameraAccess: true },
    }).then(user => {
      if (!user) return;

      const isAllowed = user.cameraAccess.some(access => {
        if (access.camera !== data.cameraName) return false;

        const ts = new Date(data.timestampLocal).getTime();
        const from = access.from ? new Date(access.from).getTime() : -Infinity;
        const to   = access.to   ? new Date(access.to).getTime()   : Infinity;

        return ts >= from && ts <= to;
      });

      if (user.isAdmin || isAllowed) {
        for (const res of resSet) res.write(payload);
      }
    }).catch(err => {
      console.error(`❌ Fehler beim Laden von User ${userId}:`, err);
    });
  });
}

// === REST-API-Endpunkte ===

// === POST ===

// ✅ /api/login
app.post('/api/login', async (req, res) => {
  const { username, password } = req.body;

  const user = await prisma.user.findUnique({ where: { username } });
  if (!user) return res.status(401).json({ error: 'Ungültiger Benutzername oder Passwort' });

  const isValidPw = await bcrypt.compare(password, user.passwordHash);
  if (!isValidPw) return res.status(401).json({ error: 'Ungültiger Benutzername oder Passwort' });

  if (!user.isAdmin && user.expiresAt && new Date(user.expiresAt) <= new Date()) {
    return res.status(403).json({ error: 'Der Zugang ist abgelaufen.' });
  }

  // lastLogin best-effort
  const now = new Date();
  try { await prisma.user.update({ where: { id: user.id }, data: { lastLogin: now } }); } catch {}

  // Features laden
  const feats = await prisma.userFeature.findMany({ where: { userId: user.id }, select: { feature: true } });
  const features = feats.map(f => f.feature);

  // Admins: DOWNLOADS garantieren (DB + Antwort)
  if (user.isAdmin && !features.includes('DOWNLOADS')) {
    await prisma.userFeature.upsert({
      where:  { userId_feature_unique: { userId: user.id, feature: 'DOWNLOADS' } },
      update: {},
      create: { userId: user.id, feature: 'DOWNLOADS' },
    });
    features.push('DOWNLOADS');
  }

  const canDownload = user.isAdmin || features.includes('DOWNLOADS');

  const maxAgeMs = 60 * 60 * 1000; // 1h
  const token = jwt.sign(
    { id: user.id, username: user.username, isAdmin: user.isAdmin, canDownload },
    process.env.JWT_SECRET,
    { expiresIn: maxAgeMs / 1000 }
  );

  res.cookie('token', token, {
    httpOnly: true,
    secure: true,
    sameSite: 'none',
    maxAge: maxAgeMs,
  });

  res.json({
    success: true,
    token,
    user: {
      id: user.id,
      username: user.username,
      isAdmin: user.isAdmin,
      tokenExpiresAt: Date.now() + maxAgeMs,
      expiresAt: user.expiresAt ? (user.expiresAt.toISOString?.() ?? user.expiresAt) : null,
      lastLogin: now.toISOString(),
      features,
    },
  });
});

// ✅ /api/refresh-token
app.post('/api/refresh-token', verifyToken, async (req, res) => {
  const { id, username, isAdmin } = req.user;

  // canDownload frisch gegen DB
  let canDownload = !!isAdmin;
  if (!canDownload) {
    canDownload = await prisma.userFeature.count({
      where: { userId: id, feature: 'DOWNLOADS' }
    }) > 0;
  }

  const maxAgeMs  = 60 * 60 * 1000;
  const newToken  = jwt.sign({ id, username, isAdmin, canDownload }, process.env.JWT_SECRET, {
    expiresIn: maxAgeMs / 1000,
  });

  res.cookie('token', newToken, {
    httpOnly: true,
    secure:   true,
    sameSite: 'none',
    maxAge:   maxAgeMs,
  });

  res.json({ success: true, tokenExpiresAt: Date.now() + maxAgeMs });
});

app.post('/api/logout', (req, res) => {
  res.clearCookie('token', {
    httpOnly: true,
    secure: true,
    sameSite: 'none',
  });
  res.json({ success: true });
});

app.post('/api/notifications', verifyToken, async (req, res) => {
  const {
    plates, brand, model, camera,
    timeFrom, timeTo,
    emails                      // Array<string>
  } = req.body;

  if (!Array.isArray(emails) || emails.length === 0) {
    return res.status(400).json({ error: 'Mindestens eine Empfänger-E-Mail angeben.' });
  }

  try {
    const raw = await prisma.notificationRule.create({
      data: {
        userId    : req.user.id,
        plates,
        brand,
        model,
        camera,
        timeFrom,
        timeTo,
        recipients: { create: emails.map(e => ({ email: e.trim() })) }
      },
      include: { recipients: { select: { email: true } } }
    });

    const rule = {
      id             : raw.id,
      licensePattern : raw.plates,
      brand          : raw.brand,
      model          : raw.model,
      camera         : raw.camera,
      timeFrom       : raw.timeFrom,
      timeTo         : raw.timeTo,
      recipients     : raw.recipients.map(r => r.email)
    };

    res.status(201).json(rule);

  } catch (err) {
    console.error('❌ Fehler beim Anlegen der NotificationRule:', err);
    res.status(500).json({ error: 'Konnte Regel nicht speichern.' });
  }
});

// /api/admin/create-user
app.post('/api/admin/create-user', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) {
    return res.status(403).json({ error: 'Nicht autorisiert' });
  }

  const { username, expiresAt, cameraAccess, features } = req.body;

  const newPassword  = generateSecurePassword(12);
  const passwordHash = await bcrypt.hash(newPassword, 10);

  // nur erlaubte Features übernehmen
  const validFeatures = Array.isArray(features)
    ? features.filter(f => ALLOWED_FEATURES.includes(f))
    : [];

  try {
    const user = await prisma.user.create({
      data: {
        username,
        passwordHash,
        expiresAt: expiresAt ? new Date(expiresAt) : null,

        // Kamerazugriffe
        cameraAccess: {
          create: (cameraAccess?.map(a => ({
            camera: a.camera,
            from  : a.from ? new Date(a.from) : null,
            to    : a.to   ? new Date(a.to)   : null,
          })) ?? []),
        },

        // 🔑 HIER: Features gleich mit anlegen
        ...(validFeatures.length
          ? {
              features: {
                create: validFeatures.map(f => ({ feature: f })),
              },
            }
          : {}),
      },
      include: {
        cameraAccess: true,
        features: { select: { feature: true } },
      },
    });

    // Antwort vereinheitlichen (Features als String-Array)
    res.json({
      success: true,
      user: {
        ...user,
        features: user.features.map(f => f.feature),
      },
      newPassword,
    });
  } catch (err) {
    console.error('Fehler beim Erstellen des Benutzers:', err);
    res.status(500).json({ error: 'Fehler beim Erstellen des Benutzers' });
  }
});


// Zuweisen
app.post('/api/admin/features/grant', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) return res.status(403).json({ error: 'Nicht autorisiert' });

  const { userId, feature } = req.body;
  if (!userId || !ALLOWED_FEATURES.includes(feature)) {
    return res.status(400).json({ error: 'Ungültige Parameter' });
  }

  await prisma.userFeature.upsert({
    where: { userId_feature_unique: { userId, feature } },
    update: {},
    create: { userId, feature },
  });

  res.json({ success: true });
});

// ✅ /api/admin/features/revoke
app.post('/api/admin/features/revoke', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) return res.status(403).json({ error: 'Nicht autorisiert' });

  const { userId, feature } = req.body;
  if (!userId || !ALLOWED_FEATURES.includes(feature)) {
    return res.status(400).json({ error: 'Ungültige Parameter' });
  }

  const target = await prisma.user.findUnique({ where: { id: userId }, select: { isAdmin: true } });
  if (target?.isAdmin && feature === 'DOWNLOADS') {
    return res.status(400).json({ error: 'Admins müssen das Feature "DOWNLOADS" behalten.' });
  }

  await prisma.userFeature.deleteMany({ where: { userId, feature } });

  // ❗ Sofortige Wirkung erzwingen
  pushLogout(userId, 'features-changed');

  res.json({ success: true });
});


app.post('/api/admin/reset-password/:id', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) {
    return res.status(403).json({ error: 'Nicht autorisiert' });
  }

  const userId = req.params.id;
  const newPassword = generateSecurePassword(12);
  const passwordHash = await bcrypt.hash(newPassword, 10);

  try {
    await prisma.user.update({
      where: { id: userId },
      data: { passwordHash },
    });

    res.json({ success: true, newPassword });
  } catch (err) {
    console.error('Fehler beim Zurücksetzen des Passworts:', err);
    res.status(500).json({ error: 'Passwort konnte nicht zurückgesetzt werden' });
  }
});

app.post('/api/recognitions/reset-count', (req, res) => {
  lastResetTimestamp = new Date();
  res.status(200).json({ success: true });
});

app.post('/api/admin/block-user/:id', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) {
    return res.status(403).json({ error: 'Nicht autorisiert' });
  }

  const userId = req.params.id;

  if (userId === String(req.user.id)) {
    return res.status(400).json({ error: 'Du kannst dich nicht selbst sperren.' });
  }

  try {
    await prisma.user.update({
      where: { id: userId },
      data:  { expiresAt: new Date() },
    });

    pushLogout(userId, 'blocked');

    res.json({ success: true });
  } catch (err) {
    console.error('❌ Fehler beim Sperren des Benutzers:', err);
    res.status(500).json({ error: 'Zugang konnte nicht gesperrt werden' });
  }
});

app.post('/api/recognitions/export', verifyToken, async (req, res) => {
  try {
    const { format = 'csv', filters = {}, selection, fields } = req.body;
    const { search = '', direction = '', timestampFrom, timestampTo, camera = '' } = filters;

    // ---- Felder-Whitelist und Defaults -----------------------------------
    const ALLOWED = [
      'id','license','licenseFormatted','country','brand','model',
      'confidence','timestampLocal','cameraName','direction','directionDegrees'
    ];
    const DEFAULT_FIELDS = [
      'licenseFormatted','country','brand','model',
      'confidence','timestampLocal','cameraName','direction','directionDegrees'
    ];
    
    // In der Route, nach dem Whitelisten:
    const fieldsFiltered = Array.isArray(fields) && fields.length
      ? fields.filter((f) => ALLOWED.includes(f))
      : DEFAULT_FIELDS;

    // NEU: direction erzwingt directionDegrees
    const fieldsToUse = pairDirectionFields(fieldsFiltered);

    // ---- Hilfen -----------------------------------------------------------
    const normalize = (s) => (s || '').toString().trim().toLowerCase().replace(/[-\s]+/g, '');
    const searchNorm = normalize(search);

    const timeFilter = {};
    if (timestampFrom) timeFilter.gte = new Date(timestampFrom);
    if (timestampTo) { const t = new Date(timestampTo); t.setHours(23,59,59,999); timeFilter.lte = t; }

    const user = req.user;

    const accessFilters = user.cameraAccess.map(access => {
      const tf = {};
      if (access.from) tf.gte = access.from;
      if (access.to)   tf.lte = access.to;
      return { cameraName: access.camera, ...(Object.keys(tf).length ? { timestampLocal: tf } : {}) };
    });

    const searchFilters = [];
    if (searchNorm) {
      searchFilters.push(
        { license: { contains: searchNorm } },
        { brand  : { contains: searchNorm } },
        { model  : { contains: searchNorm } },
      );
    }

    const baseWhere = {
      AND: [
        ...(!user.isAdmin ? [{ OR: accessFilters }] : []),
        ...(searchFilters.length ? [{ OR: searchFilters }] : []),
        ...(Object.keys(timeFilter).length ? [{ timestampLocal: timeFilter }] : []),
        ...(direction === 'towards' || direction === 'away' ? [{ direction: { equals: capitalize(direction) } }] : []),
        ...(camera ? [{ cameraName: camera }] : []),
      ],
    };

    let where = baseWhere;
    if (selection?.mode === 'selected') {
      where = { AND: [baseWhere, { id: { in: selection.ids || [] } }] };
    } else if (selection?.mode === 'selected-all-except') {
      where = { AND: [baseWhere, { id: { notIn: selection.exceptIds || [] } }] };
    }

    // ---- Daten holen (Superset) ------------------------------------------
    const rows = await prisma.recognition.findMany({
      where,
      orderBy: { timestampLocal: 'desc' },
      select: {
        id: true, license: true, licenseFormatted: true, country: true,
        brand: true, model: true, confidence: true, timestampLocal: true,
        cameraName: true, direction: true, directionDegrees: true,
        imageFile: true, plateFile: true,
      },
      take: 2000,
    });
    
    const clientJobId = req.body?.clientJobId || null;

    // ---- Dispatch je Format ----------------------------------------------
    if (format === 'pdf') {
      return await exportAsPdf(rows, fieldsToUse, req, res, { clientJobId });
    }

    if (format === 'json') {
      return exportAsJson(rows, fieldsToUse, res);
    }
    // default CSV
    return exportAsCsv(rows, fieldsToUse, res);

  } catch (err) {
    console.error('❌ Export error:', err);
    res.status(500).json({ error: 'Export fehlgeschlagen' });
  }
});

/* ============================== HELPERS ============================== */

// Einheitlicher Export-Dateiname: export_YYYY-MM-DD_HH-mm.<ext>
function buildExportFilename(ext = 'csv', d = new Date()) {
  const pad = (n) => String(n).padStart(2, '0');
  const yyyy = d.getFullYear();
  const mm   = pad(d.getMonth() + 1);
  const dd   = pad(d.getDate());
  const hh   = pad(d.getHours());
  const mi   = pad(d.getMinutes());
  return `export_${yyyy}-${mm}-${dd}_${hh}-${mi}.${ext}`;
}

// ---- Helper: Datei -> Data-URL ----
async function fileToDataUrl(absPath, mime = 'image/jpeg') {
  const buf = await fs.promises.readFile(absPath);
  return `data:${mime};base64,${buf.toString('base64')}`;
}

// ---- Helper: HTTP -> Data-URL mit eigener CA (für Logo-Fallback) ----
async function fetchAsDataUrl(url, cookie = '', bearer = '') {
  let dispatcher = undefined;
  try {
    const ca = fs.readFileSync(path.resolve(__dirname, 'certs', 'myRoot.crt'));
    dispatcher = new Agent({ connect: { ca } });
  } catch { /* wenn CA fehlt, versucht er es ohne */ }

  const headers = {};
  if (cookie) headers['Cookie'] = cookie;
  if (bearer) headers['Authorization'] = `Bearer ${bearer}`;

  const resp = await fetch(url, { dispatcher, headers });
  if (!resp.ok) throw new Error(`HTTP ${resp.status} for ${url}`);
  const buf  = Buffer.from(await resp.arrayBuffer());
  const mime = resp.headers.get('content-type') || 'image/png';
  return `data:${mime};base64,${buf.toString('base64')}`;
}

// ---- Helper: Bild der Erkennung -> Data-URL (bevorzugt Filesystem) ----
async function fullPhotoDataUrlForRow(r) {
  if (r.imageFile) {
    try {
      const abs = await findImageFile(r.imageFile); // deine vorhandene Funktion
      if (abs && fs.existsSync(abs)) return await fileToDataUrl(abs, 'image/jpeg');
    } catch {}
  }
  // Fallback: per HTTP holen (inkl. Token/Cookies) und inline konvertieren
  try {
    const tokenFromCookie = req.cookies?.token || null;
    const tokenFromAuth = req.headers.authorization?.startsWith('Bearer ')
      ? req.headers.authorization.slice(7)
      : null;
    const token = tokenFromCookie || tokenFromAuth || '';
    const url = r.imageFile
      ? `${BASE}/images/${r.imageFile}`
      : `${BASE}/assets/img/placeholder.jpg`;
    return await fetchAsDataUrl(url, req.headers.cookie || '', token);
  } catch {
    // letzter Fallback: „leeres“ 1x1 PNG
    return 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mP8/x8AAoMBgVOWk5EAAAAASUVORK5CYII=';
  }
}

async function platePhotoDataUrlForRow(r) {
  if (r.plateFile) {
    try {
      const abs = await findImageFile(r.plateFile); // deine vorhandene Funktion
      if (abs && fs.existsSync(abs)) return await fileToDataUrl(abs, 'image/jpeg');
    } catch {}
  }
  // Fallback: per HTTP holen (inkl. Token/Cookies) und inline konvertieren
  try {
    const tokenFromCookie = req.cookies?.token || null;
    const tokenFromAuth = req.headers.authorization?.startsWith('Bearer ')
      ? req.headers.authorization.slice(7)
      : null;
    const token = tokenFromCookie || tokenFromAuth || '';
    const url = r.plateFile
      ? `${BASE}/images/${r.plateFile}`
      : `${BASE}/assets/img/placeholder.jpg`;
    return await fetchAsDataUrl(url, req.headers.cookie || '', token);
  } catch {
    // letzter Fallback: „leeres“ 1x1 PNG
    return 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mP8/x8AAoMBgVOWk5EAAAAASUVORK5CYII=';
  }
}

function emitToUser(userId, event, payload) {
  const set = sseClients.get(String(userId));
  if (!set) return;
  const msg = `event: ${event}\ndata: ${JSON.stringify(payload)}\n\n`;
  for (const res of set) res.write(msg);
}


function pairDirectionFields(fields) {
  if (!Array.isArray(fields)) return fields;
  const hasDir = fields.includes('direction');
  const hasDeg = fields.includes('directionDegrees');
  if (hasDir && !hasDeg) {
    const out = [];
    for (const f of fields) {
      out.push(f);
      if (f === 'direction') out.push('directionDegrees'); // direkt danach einsortieren
    }
    return out;
  }
  return fields;
}

// CSV -----------------------------------------------------------------
function exportAsCsv(rows, fieldsToUse, res) {
  const header = fieldsToUse;
  const csv = [
    header.join(';'),
    ...rows.map(r =>
      header.map(key => {
        const v = r[key];
        const s = v instanceof Date ? v.toISOString() : (v ?? '');
        return `"${String(s).replace(/"/g, '""')}"`;
      }).join(';')
    ),
  ].join('\n');

  res.setHeader('Content-Type', 'text/csv; charset=utf-8');
  const filename = buildExportFilename('csv');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"; filename*=UTF-8''${encodeURIComponent(filename)}`);
  return res.send(csv);
}

// JSON ----------------------------------------------------------------
function exportAsJson(rows, fieldsToUse, res) {
  const slim = rows.map(r => {
    const obj = {};
    for (const k of fieldsToUse) obj[k] = r[k];
    return obj;
  });
  res.setHeader('Content-Type', 'application/json; charset=utf-8');
  const filename = buildExportFilename('json');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"; filename*=UTF-8''${encodeURIComponent(filename)}`);
  return res.send(JSON.stringify(slim));
}

// PDF -----------------------------------------------------------------
// Erwartet emitToUser(userId, event, payload) als globale Helper-Funktion
// sowie findImageFile, fileToDataUrl, fetchAsDataUrl und puppeteer.

async function exportAsPdf(rows, fieldsToUse, req, res, { clientJobId } = {}) {
  const userId = String(req.user?.id || '');
  const total = rows.length;
  const CHUNK_SIZE = 40;
  const now = new Date();

  // ===== Progress-Helfer ==================================================
  let done = 0; // bereits "verarbeitete" Datensätze (für Progress)

  const safeEmit = (payload) => {
    try {
      if (typeof emitToUser === 'function') {
        emitToUser(userId, 'export-progress', payload);
      }
    } catch {}
  };

  const ping = (stage, overrideProgress) => {
    if (!clientJobId) return;
    const computed = total > 0 ? Math.round((done / total) * 98) : 1;
    const progress = Math.max(1, Math.min(99, overrideProgress ?? computed));
    safeEmit({ jobId: clientJobId, stage, done, total, progress });
  };

  // ===== Utilities ========================================================
  const escapeHtml = (s) =>
    String(s ?? '')
      .replace(/&/g, '&amp;').replace(/</g, '&lt;')
      .replace(/>/g, '&gt;').replace(/"/g, '&quot;')
      .replace(/'/g, '&#39;');

  const fmtDate = (d) => {
    try {
      return new Intl.DateTimeFormat('de-DE', { dateStyle: 'medium', timeStyle: 'short' }).format(new Date(d));
    } catch { return new Date(d).toISOString(); }
  };

  const BASE = process.env.FRONTEND_ORIGIN || 'http://localhost:3000';

  async function fileToDataUrl(absPath, mime = 'image/jpeg') {
    const buf = await fs.promises.readFile(absPath);
    return `data:${mime};base64,${buf.toString('base64')}`;
  }

  async function fetchAsDataUrl(url, cookie = '', bearer = '') {
    let dispatcher;
    try {
      const ca = fs.readFileSync(path.resolve(__dirname, 'certs', 'myRoot.crt'));
      dispatcher = new Agent({ connect: { ca } });
    } catch {}
    const headers = {};
    if (cookie) headers['Cookie'] = cookie;
    if (bearer) headers['Authorization'] = `Bearer ${bearer}`;
    const resp = await fetch(url, { dispatcher, headers });
    if (!resp.ok) throw new Error(`HTTP ${resp.status} for ${url}`);
    const buf  = Buffer.from(await resp.arrayBuffer());
    const mime = resp.headers.get('content-type') || 'image/png';
    return `data:${mime};base64,${buf.toString('base64')}`;
  }

  async function findImageFile(filename) {
    if (!filename) return null;
    const jpgFilename = filename.replace(/\.webp$/, '.jpg');
    function searchInDir(dir) {
      try {
        const entries = fs.readdirSync(dir, { withFileTypes: true });
        for (const e of entries) {
          const full = path.join(dir, e.name);
          if (e.isDirectory()) { const f = searchInDir(full); if (f) return f; }
          else if (e.isFile() && e.name === jpgFilename) return full;
        }
      } catch {}
      return null;
    }
    return searchInDir(WATCH_PATH);
  }

  async function fullPhotoDataUrlForRow(r) {
    if (r.imageFile) {
      const abs = await findImageFile(r.imageFile);
      if (abs && fs.existsSync(abs)) return await fileToDataUrl(abs, 'image/jpeg');
    }
    try {
      const tokenFromCookie = req.cookies?.token || null;
      const tokenFromAuth = req.headers.authorization?.startsWith('Bearer ') ? req.headers.authorization.slice(7) : null;
      const token = tokenFromCookie || tokenFromAuth || '';
      const url = r.imageFile ? `${BASE}/images/${r.imageFile}` : `${BASE}/assets/img/placeholder.jpg`;
      return await fetchAsDataUrl(url, req.headers.cookie || '', token);
    } catch {
      return 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mP8/x8AAoMBgVOWk5EAAAAASUVORK5CYII=';
    }
  }

  async function platePhotoDataUrlForRow(r) {
    if (r.plateFile) {
      const abs = await findImageFile(r.plateFile);
      if (abs && fs.existsSync(abs)) return await fileToDataUrl(abs, 'image/jpeg');
    }
    try {
      const tokenFromCookie = req.cookies?.token || null;
      const tokenFromAuth = req.headers.authorization?.startsWith('Bearer ') ? req.headers.authorization.slice(7) : null;
      const token = tokenFromCookie || tokenFromAuth || '';
      const url = r.plateFile ? `${BASE}/images/${r.plateFile}` : `${BASE}/assets/img/placeholder.jpg`;
      return await fetchAsDataUrl(url, req.headers.cookie || '', token);
    } catch {
      return 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mP8/x8AAoMBgVOWk5EAAAAASUVORK5CYII=';
    }
  }

  const labelOf = {
    id:'ID', license:'Kennzeichen (roh)', licenseFormatted:'Kennzeichen',
    country:'Land', brand:'Marke', model:'Modell', confidence:'Treffsicherheit',
    timestampLocal:'Zeit', cameraName:'Kamera', direction:'Richtung', directionDegrees:'Richtung (°)',
  };
  const fieldsForPdf = fieldsToUse.filter(f => f !== 'directionDegrees');
  const valueFor = (r, k) => {
    if (k === 'timestampLocal') return fmtDate(r.timestampLocal);
    if (k === 'confidence' && r.confidence != null) return `${r.confidence}%`;
    if (k === 'licenseFormatted') return r.licenseFormatted || r.license || '-';
    if (k === 'direction') return r.directionDegrees != null ? `${r.direction ?? '-'} (${r.directionDegrees}°)` : (r.direction ?? '-');
    return r[k] ?? '-';
  };

  // ---- Logo --------------------------------------------------------------
  let logoSrc;
  try {
    const svg = path.resolve(__dirname, 'assets', 'img', 'logo.svg');
    const png = path.resolve(__dirname, 'assets', 'img', 'logo.png');
    if (fs.existsSync(svg)) {
      logoSrc = await fileToDataUrl(svg, 'image/svg+xml');
    } else if (fs.existsSync(png)) {
      logoSrc = await fileToDataUrl(png, 'image/png');
    } else {
      const cookie = req.headers.cookie || '';
      const bearer = req.cookies?.token || '';
      try {
        logoSrc = await fetchAsDataUrl(`${BASE}/assets/img/logo.svg`, cookie, bearer);
      } catch {
        logoSrc = await fetchAsDataUrl(`${BASE}/assets/img/logo.png`, cookie, bearer);
      }
    }
  } catch {
    logoSrc = 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAoAAAAKCAQAAACENnwnAAAAIUlEQVR42mP8z8Dwn4GBgYHhP2NgYGBg+P///w8GAAxwBvUQ8ZqjAAAAAElFTkSuQmCC';
  }

  const headerHtml = `
    <header class="head">
      <div class="head-left">
        <p><b><span>PP Düsseldorf</span></b></p>
        <p><b><span>Dir. GE / Spezialeinheiten</span></b></p>
        <p><b><span>Technische Einsatzgruppe</span></b></p>
      </div>
      <div class="head-logo"><img src="${escapeHtml(logoSrc)}" alt="Logo" /></div>
    </header>
  `;

  const minTs = rows.length ? new Date(Math.min(...rows.map(r => new Date(r.timestampLocal).getTime()))) : null;
  const maxTs = rows.length ? new Date(Math.max(...rows.map(r => new Date(r.timestampLocal).getTime()))) : null;
  const rangeHtml = (minTs && maxTs)
    ? `<div><span class="muted">Von:</span> ${escapeHtml(fmtDate(minTs))}</div>
       <div><span class="muted">Bis:</span> ${escapeHtml(fmtDate(maxTs))}</div>` : '–';

  const coverHtml = `
    <section class="page cover">
      ${headerHtml}
      <div class="cover-inner">
        <h1>Kennzeichenerfassung – Export</h1>
        <dl class="meta meta--stack">
          <div class="row"><dt>Erstellt von</dt><dd>${escapeHtml(req.user?.username || 'Unbekannt')}</dd></div>
          <div class="row"><dt>Erstellt am</dt><dd>${escapeHtml(fmtDate(now))}</dd></div>
          <div class="row"><dt>Anzahl Einträge</dt><dd>${rows.length.toLocaleString('de-DE')}</dd></div>
          <div class="row"><dt>Zeitraum</dt><dd class="dd--stack">${rangeHtml}</dd></div>
        </dl>
      </div>
    </section>
  `;

  const style = `
    @page { size: A4; margin: 14mm 12mm 16mm; }
    body { font-family: system-ui, -apple-system, Segoe UI, Roboto, Inter, Arial, sans-serif; color:#111; }
    .head{ display:flex; justify-content:space-between; align-items:center; gap:12mm; width:100%; max-width:160mm; margin:0 auto 12mm; }
    .head-left p{ margin:0; }
    .head-logo img{ height:14mm; object-fit:contain; display:block; border:0; }
    .cover .head{ max-width:150mm; }
    .page{ page-break-after: always; display:flex; flex-direction:column; align-items:center; }
    .cover{ justify-content:center; }
    .cover-inner{ max-width:160mm; text-align:center; }
    .cover h1{ font-size:22pt; margin:6mm 0 10mm; }
    .center{ width:100%; display:flex; justify-content:center; }
    .meta{ display:grid; grid-template-columns:45mm 1fr; gap:6px 12px; font-size:12pt; margin:0 auto; }
    .meta dt{ color:#555; font-weight:600; text-align:left; } .meta dd{ margin:0; text-align:left; }
    .meta.meta--stack{ display:grid; grid-template-columns:1fr; gap:10px; justify-content:center; }
    .meta.meta--stack .row{ display:grid; grid-template-columns:45mm 1fr; column-gap:14px; align-items:start; }
    .meta .muted{ color:#666; }
    .photo-wrap{ width:100%; display:flex; flex-direction:column; align-items:center; gap:4mm; }
    .photo{ max-width:160mm; max-height:100mm; object-fit:contain; border-radius:6px; border:1px solid #ddd; }
    .details{ width:100%; margin-top:10mm; }
    h2{ margin:0 0 6mm; font-size:16pt; }
    table{ width:100%; border-collapse:collapse; font-size:11pt; }
    th,td{ padding:6px 0; vertical-align:top; text-align:left; }
    th{ width:42mm; color:#444; font-weight:600; padding-right:8mm; }
    tr+tr td, tr+tr th{ border-top:1px solid #eee; }
  `;

  // 🔹 pro Zeile pingen + kurz yielden
  async function buildPagesHtml(subRows) {
    const chunks = [];
    for (const r of subRows) {
      const [fullPhotoSrc, platePhotoSrc] = await Promise.all([
        fullPhotoDataUrlForRow(r),
        platePhotoDataUrlForRow(r),
      ]);

      const detailRows = fieldsForPdf.map(k => `
        <tr><th>${escapeHtml(labelOf[k])}</th><td>${escapeHtml(String(valueFor(r,k)))}</td></tr>
      `).join('');

      chunks.push(`
        ${headerHtml}
        <section class="page">
          <h2 class="center">Erkennung #${r.id}</h2>
          <div class="photo-wrap">
            <img class="photo" src="${escapeHtml(platePhotoSrc)}" alt="Plate ${escapeHtml(r.licenseFormatted || r.license || String(r.id))}" />
            <img class="photo" src="${escapeHtml(fullPhotoSrc)}" alt="Full ${escapeHtml(r.licenseFormatted || r.license || String(r.id))}" />
          </div>
          <div class="details"><table><tbody>${detailRows}</tbody></table></div>
        </section>
      `);

      // 🔹 hier: done+1 und sofort Progress senden
      done += 1;
      ping(`Seite ${done}/${total}`);

      // 🔹 Event-Loop freigeben, damit SSE rausfliegt
      await new Promise(resolve => setImmediate(resolve));
    }
    return chunks.join('\n');
  }

  function wrapHtml(body) {
    return `<!doctype html><html lang="de"><head>
      <meta charset="utf-8" /><meta name="viewport" content="width=device-width, initial-scale=1" />
      <title>Kennzeichenerfassung Export</title><style>${style}</style>
    </head><body>${body}</body></html>`;
  }

  // ===== Progress-Start ===================================================
  ping('starte…', 1);
  ping('PDF wird aufgebaut…');

  // ---- CHUNKED RENDERING -------------------------------------------------
  const browser = await puppeteer.launch({
    headless: true,
    args: ['--no-sandbox','--disable-setuid-sandbox','--disable-dev-shm-usage','--no-zygote','--no-first-run','--no-default-browser-check'],
  });
  const page = await browser.newPage();
  page.setDefaultTimeout(60_000);
  page.setDefaultNavigationTimeout(60_000);

  const partBuffers = [];

  for (let i = 0; i < rows.length; i += CHUNK_SIZE) {
    const sub = rows.slice(i, i + CHUNK_SIZE);
    const pagesHtml = await buildPagesHtml(sub);    // 🔹 pings passieren hier pro Zeile
    const withCover = i === 0 ? coverHtml : '';
    const html = wrapHtml(`${withCover}${pagesHtml}`);

    await page.setContent(html, { waitUntil: 'domcontentloaded' });
    await page.emulateMediaType('screen');

    const buf = await page.pdf({
      format: 'A4',
      printBackground: true,
      margin: { top: '14mm', right: '12mm', bottom: '16mm', left: '12mm' },
    });
    partBuffers.push(buf);

    // 🔹 zusätzlicher Stage-Hinweis (ohne Progress-Override)
    ping(`Teil ${Math.floor(i / CHUNK_SIZE) + 1} gerendert`);
    await new Promise(resolve => setImmediate(resolve));
  }

  await page.close();
  await browser.close();

  // ---- PDFs zusammenführen ----------------------------------------------
  ping('PDF wird zusammengeführt…', Math.min(99, (total > 0 ? Math.round((done / total) * 98) + 1 : 98)));

  const merged = await PDFDocument.create();
  for (const b of partBuffers) {
    const src = await PDFDocument.load(b);
    const pages = await merged.copyPages(src, src.getPageIndices());
    pages.forEach(p => merged.addPage(p));
  }
  const mergedBytes = await merged.save();

  // Final
  ping('bereit zum Download', 99);

  const filename = buildExportFilename('pdf', now);

  res.setHeader('Content-Type', 'application/pdf');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"; filename*=UTF-8''${encodeURIComponent(filename)}`);
  return res.send(Buffer.from(mergedBytes));
}



// === PUT ===

app.put('/api/notifications/:id', verifyToken, async (req, res) => {
  const id = parseInt(req.params.id, 10);

  /* ── gehört diese Regel dem anfragenden User? ───────────────────── */
  const exists = await prisma.notificationRule.findUnique({
    where : { id },
    select: { userId: true },
  });
  if (!exists || exists.userId !== req.user.id) {
    return res.status(404).json({ error: 'Not found' });
  }

  /* ── Body zerlegen: „emails“ darf nicht direkt ins Update ───────── */
  const {
    emails,                // Array<string> oder undefined
    plates,
    brand,
    model,
    camera,
    timeFrom,
    timeTo,
  } = req.body;

  try {
    /* 1. Hauptfelder updaten ---------------------------------------- */
    await prisma.notificationRule.update({
      where: { id },
      data : {
        plates,
        brand,
        model,
        camera,
        timeFrom: timeFrom,
        timeTo: timeTo,
      },
    });

    /* 2. Recipients komplett ersetzen ------------------------------- */
    if (Array.isArray(emails)) {
      // (a) alte Empfänger entfernen
      await prisma.notificationRecipient.deleteMany({ where: { ruleId: id } });

      // (b) neue Empfänger anlegen
      if (emails.length) {
        await prisma.notificationRecipient.createMany({
          data: emails.map((e) => ({ ruleId: id, email: e.trim() })),
        });
      }
    } else {
      // kein E-Mail-Array => aktuelle Regel direkt zurückgeben
      const rule = await prisma.notificationRule.findUnique({
        where  : { id },
        include: { recipients: { select: { email: true } } },
      });
      return res.json({
        id             : rule.id,
        licensePattern : rule.plates,
        brand          : rule.brand,
        model          : rule.model,
        camera         : rule.camera,
        timeFrom       : rule.timeFrom,
        timeTo         : rule.timeTo,
        recipients     : rule.recipients.map((r) => r.email),
      });
    }

    /* 3. Aktuelle Regel zurückgeben --------------------------------- */
    const rule = await prisma.notificationRule.findUnique({
      where  : { id },
      include: { recipients: { select: { email: true } } },
    });

    res.json({
      id             : rule.id,
      licensePattern : rule.plates,
      brand          : rule.brand,
      model          : rule.model,
      camera         : rule.camera,
      timeFrom       : rule.timeFrom,
      timeTo         : rule.timeTo,
      recipients     : rule.recipients.map((r) => r.email),
    });

  } catch (err) {
    console.error('❌ Fehler beim Aktualisieren der NotificationRule:', err);
    res.status(500).json({ error: 'Update fehlgeschlagen' });
  }
});

// ✅ /api/admin/update-user/:id
app.put('/api/admin/update-user/:id', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) return res.status(403).json({ error: 'Nicht autorisiert' });

  const userId = req.params.id;
  const { username, expiresAt, cameraAccess, features } = req.body;

  try {
    // Update Basisdaten
    await prisma.user.update({
      where: { id: userId },
      data : { username, expiresAt: expiresAt ? new Date(expiresAt) : null },
    });

    // Kamera-Zugriffe ersetzen
    await prisma.cameraAccess.deleteMany({ where: { userId } });
    if (Array.isArray(cameraAccess) && cameraAccess.length) {
      await prisma.cameraAccess.createMany({
        data: cameraAccess.map(a => ({
          camera: a.camera,
          from  : a.from ? new Date(a.from) : null,
          to    : a.to   ? new Date(a.to)   : null,
          userId,
        })),
      });
    }

    // Features neu setzen + Entzug erkennen
    if (Array.isArray(features)) {
      const before = await prisma.userFeature.findMany({
        where: { userId },
        select: { feature: true },
      });
      const hadDownloads = before.some(f => f.feature === 'DOWNLOADS');

      await prisma.userFeature.deleteMany({ where: { userId } });
      const toCreate = features
        .filter(f => ALLOWED_FEATURES.includes(f))
        .map(f => ({ userId, feature: f }));
      if (toCreate.length) await prisma.userFeature.createMany({ data: toCreate });

      // Admins müssen DOWNLOADS behalten
      const isTargetAdmin = await prisma.user.findUnique({ where: { id: userId }, select: { isAdmin: true } });
      if (isTargetAdmin?.isAdmin) {
        await prisma.userFeature.upsert({
          where:  { userId_feature_unique: { userId, feature: 'DOWNLOADS' } },
          update: {},
          create: { userId, feature: 'DOWNLOADS' },
        });
      }

      // Nachher prüfen
      const after = await prisma.userFeature.findMany({
        where: { userId },
        select: { feature: true },
      });
      const hasDownloadsNow = after.some(f => f.feature === 'DOWNLOADS');

      // ❗ Bei Entzug sofort ausloggen (JWT wird ungültig gemacht, weil Client /api/logout aufruft)
      if (hadDownloads && !hasDownloadsNow) {
        pushLogout(userId, 'features-changed');
      }
    }

    // Sofortige Abmeldung, falls Zugang jetzt abgelaufen
    if (expiresAt && new Date(expiresAt) <= new Date()) {
      pushLogout(userId, 'expired');
    }

    res.json({ success: true });
  } catch (err) {
    console.error('❌ Fehler beim Aktualisieren des Benutzers:', err);
    res.status(500).json({ error: 'Aktualisierung fehlgeschlagen' });
  }
});

// === DELETE ===

app.delete('/api/notifications/:id', verifyToken, async (req, res) => {
  await prisma.notificationRule.delete({
    where: { id: parseInt(req.params.id, 10), userId: req.user.id }
  });
  res.status(204).end();
});


app.delete('/api/admin/delete-user/:id', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) {
    return res.status(403).json({ error: 'Nicht autorisiert' });
  }

  const userId = req.params.id

  try {
    // Zuerst Kamera-Zugriffe löschen (wegen Foreign Key Constraints)
    await prisma.cameraAccess.deleteMany({
      where: { userId },
    });

    // Danach Benutzer löschen
    await prisma.user.delete({
      where: { id: userId },
    });

    res.json({ success: true });
  } catch (err) {
    console.error('❌ Fehler beim Löschen des Benutzers:', err);
    res.status(500).json({ error: 'Benutzer konnte nicht gelöscht werden' });
  }
});


// === GET ===

app.get('/api/protected', verifyToken, (req, res) => {
  if (!req.user.isAdmin) return res.status(403).json({ error: 'Kein Zugriff' });
  res.json({ data: 'Admin-Daten' });
});

app.get('/api/notifications', verifyToken, async (req, res) => {
  const rows = await prisma.notificationRule.findMany({
    where  : { userId: req.user.id },
    orderBy: { createdAt: 'desc' },
    include: { recipients: { select: { email: true } } }
  });

  const data = rows.map(r => ({
    id             : r.id,
    licensePattern : r.plates,
    brand          : r.brand,
    model          : r.model,
    camera         : r.camera,
    timeFrom       : r.timeFrom,
    timeTo         : r.timeTo,
    recipients     : r.recipients.map(x => x.email)
  }));

  res.json(data);
});

app.get('/api/unsubscribe', async (req, res) => {
  const id  = req.query.id?.toString();     // UUID (Token-ID)
  const sig = req.query.sig?.toString();    // HMAC-Signatur
  const redirectTo = new URL('/unsubscribe', FRONTEND_ORIGIN).toString();

  const setStatus = (status, ruleId = '') => {
    res.cookie('unsubscribeStatus', status, {
      maxAge: 5 * 1000,
      path: '/',
      secure: true,
      sameSite: 'none',
    });

    res.cookie('unsubscribeRule', ruleId.toString(), {
      maxAge: 5 * 1000,
      path: '/',
      secure: true,
      sameSite: 'none',
    });
  };

  if (!id || !sig) {
    setStatus('error');
    return res.redirect(302, redirectTo);
  }

  try {
    const record = await prisma.unsubscribeToken.findUnique({ where: { id } });
    if (!record) {
      setStatus('invalid');
      return res.redirect(302, redirectTo);
    }

    const expectedSig = createUnsubscribeToken(record.ruleId, record.email);
    const isValid = sig === expectedSig;
    if (!isValid) {
      setStatus('invalid');
      return res.redirect(302, redirectTo);
    }

    const ruleId = record.ruleId;

    /* ───────────────────────────── Rule-spezifische Austragung ───────────────────────────── */
    if (ruleId !== null) {
      const result = await prisma.$transaction(async (tx) => {
        // Empfänger (nur diese E-Mail) bei dieser Regel entfernen
        const del = await tx.notificationRecipient.deleteMany({
          where: { ruleId, email: record.email },
        });

        // Prüfen, ob die Regel danach noch Empfänger hat
        const remaining = await tx.notificationRecipient.count({ where: { ruleId } });

        if (remaining === 0) {
          // Aufräumen: erst Tokens für diese Regel löschen, dann die Regel selbst
          await tx.unsubscribeToken.deleteMany({ where: { ruleId } });
          await tx.notificationRule.delete({ where: { id: ruleId } });
          // Falls der aktuelle Token noch existiert (kann in deleteMany enthalten sein), ignorieren wir das bewusst
          return { removed: del.count, prunedRule: true };
        } else {
          // Nur den benutzten Token löschen
          await tx.unsubscribeToken.delete({ where: { id: record.id } });
          return { removed: del.count, prunedRule: false };
        }
      });

      setStatus(result.removed > 0 ? 'success' : 'notfound', ruleId);
      return res.redirect(302, redirectTo);
    }

    /* ───────────────────────────── Globale Austragung ─────────────────────────────
       Entfernt diese E-Mail aus allen Regeln und löscht alle Regeln, die danach
       keine Empfänger mehr haben. Räumt anschließend alle Tokens dieser E-Mail auf.
    ─────────────────────────────────────────────────────────────────────────────── */
    const outcome = await prisma.$transaction(async (tx) => {
      // 1) Alle Empfänger-Einträge dieser E-Mail entfernen
      const del = await tx.notificationRecipient.deleteMany({ where: { email: record.email } });

      // 2) Alle Regeln ohne Empfänger finden …
      const emptyRules = await tx.notificationRule.findMany({
        where: { recipients: { none: {} } },
        select: { id: true },
      });
      const emptyIds = emptyRules.map(r => r.id);

      // … und löschen (vorher zugehörige Tokens weg)
      if (emptyIds.length) {
        await tx.unsubscribeToken.deleteMany({ where: { ruleId: { in: emptyIds } } });
        await tx.notificationRule.deleteMany({ where: { id: { in: emptyIds } } });
      }

      // 3) Alle Tokens dieser E-Mail entfernen (inkl. des aktuell genutzten)
      await tx.unsubscribeToken.deleteMany({ where: { email: record.email } });

      return { removed: del.count, prunedCount: emptyIds.length };
    });

    setStatus(outcome.removed > 0 ? 'success' : 'notfound', 'all');
    return res.redirect(302, redirectTo);

  } catch (err) {
    console.error('❌ Fehler bei /api/unsubscribe:', err);
    setStatus('error');
    return res.redirect(302, redirectTo);
  }
});

app.get('/api/cameras', verifyToken, async (req, res) => {
  try {
    let cameraNames = [];

    if (req.user.isAdmin) {
      const cameras = await prisma.recognition.findMany({
        where: { cameraName: { not: null } },
        distinct: ['cameraName'],
        select: { cameraName: true },
      });

      cameraNames = cameras
        .map((entry) => entry.cameraName)
        .filter(Boolean);
    } else {
      cameraNames = req.user.cameraAccess
        .map((access) => access.camera)
        .filter(Boolean);
    }

    const uniqueSorted = [...new Set(cameraNames)].sort((a, b) => a.localeCompare(b));

    res.json({ cameras: uniqueSorted });
  } catch (err) {
    console.error('❌ Fehler bei /api/cameras:', err);
    res.status(500).json({ error: 'Kameras konnten nicht geladen werden' });
  }
});

app.get('/api/me', async (req, res) => {
  const token = req.cookies.token;
  if (!token) return res.status(401).json({ error: 'Nicht eingeloggt' });

  try {
    const secret = process.env.JWT_SECRET;
    if (!secret) throw new Error('JWT_SECRET fehlt');

    const raw = jwt.verify(token, secret);         // kann string oder object sein
    if (typeof raw !== 'object' || raw === null) {
      throw new Error('Ungültiger Token-Payload');
    }

    // raw ist nun ein Objekt – Felder defensiv auslesen
    const id = String(raw.id);
    const username = String(raw.username);
    const isAdmin = Boolean(raw.isAdmin);
    const exp = typeof raw.exp === 'number' ? raw.exp : undefined;

    const dbUser = await prisma.user.findUnique({
      where: { id },                               // User.id ist String (cuid)
      select: {
        lastLogin: true,
        expiresAt: true,
        features: { select: { feature: true } },   // Enum-Werte holen
      },
    });

    res.json({
      id,
      username,
      isAdmin,
      tokenExpiresAt: exp ? exp * 1000 : undefined,
      lastLogin: dbUser?.lastLogin ? dbUser.lastLogin.toISOString() : null,
      expiresAt: dbUser?.expiresAt ? dbUser.expiresAt.toISOString() : null,
      features: dbUser?.features?.map(f => f.feature) ?? [], // -> ['DOWNLOADS']
    });
  } catch (err) {
    console.error('GET /api/me error:', err);
    res.status(401).json({ error: 'Ungültiger Token' });
  }
});

app.get('/api/recognitions', verifyToken, async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const skip = (page - 1) * limit;
    //const search = req.query.search?.toString().trim().toLowerCase().replace(/\s+/g, '');
    const searchRaw = req.query.search?.toString() ?? '';
    const searchRawLower = searchRaw.trim().toLowerCase();
    // Entfernt Bindestriche UND Whitespaces (robuster):
    const searchNoSep = searchRawLower.replace(/[^a-z0-9]+/g, '');
    const direction = req.query.direction?.toString();
    const camera    = req.query.camera?.toString()?.trim();

    let timestampFrom = req.query.timestampFrom ? new Date(req.query.timestampFrom) : null;
    let timestampTo = req.query.timestampTo ? new Date(req.query.timestampTo) : null;

    if (timestampTo) {
      timestampTo.setHours(23, 59, 59, 999);
    }

    // Nutzer & Kamera-Zugriffsrechte laden
    const user = req.user;

    if (!user || (!user.isAdmin && !user.cameraAccess.length)) {
      return res.json({ data: [], page: 1, totalPages: 1, totalCount: 0 });
    }

    // Filter nach erlaubten Kameras + Zeiträumen
    const accessFilters = user.cameraAccess.map(access => {
      const timeFilter = {};
      if (access.from) timeFilter.gte = access.from;
      if (access.to) timeFilter.lte = access.to;

      return {
        cameraName: access.camera,
        ...(Object.keys(timeFilter).length ? { timestampLocal: timeFilter } : {}),
      };
    });

    // Suchfilter
    const searchFilters = [];
    if (searchNoSep) {
      // 1) Auf "license" ohne Trennzeichen suchen (DB-Feld ohne "-")
      searchFilters.push({ license: { contains: searchNoSep } });

      // 2) Optional zusätzlich auf licenseFormatted MIT Trennzeichen suchen
      //    (falls der Nutzer "D-AB" tippt und dein Feld so gespeichert ist)
      searchFilters.push({ licenseFormatted: { contains: searchRawLower } });

      // 3) Brand/Model normal (Groß-/Kleinschreibung ignorieren)
      searchFilters.push(
        { brand: { contains: searchRawLower } },
        { model: { contains: searchRawLower } },
      );
    }

    // Globaler Zeitfilter (aus Filterleiste)
    const globalTimeFilter = {};
    if (timestampFrom) globalTimeFilter.gte = timestampFrom;
    if (timestampTo) globalTimeFilter.lte = timestampTo;

    // Kombinierter Prisma-Filter
    const where = {
      AND: [
        ...(!user.isAdmin ? [{ OR: accessFilters }] : []),
        ...(searchFilters.length ? [{ OR: searchFilters }] : []),
        ...(Object.keys(globalTimeFilter).length ? [{ timestampLocal: globalTimeFilter }] : []),
        ...(direction === 'towards' || direction === 'away'
            ? [{ direction: { equals: capitalize(direction) } }]
            : []),
        ...(camera ? [{ cameraName: camera }] : []),
      ],
    };

    const [entries, totalCount] = await Promise.all([
      prisma.recognition.findMany({
        where,
        orderBy: { timestampLocal: 'desc' },
        skip,
        take: limit,
      }),
      prisma.recognition.count({ where }),
    ]);

    res.json({
      data: entries,
      page,
      totalPages: Math.ceil(totalCount / limit),
      totalCount,
    });
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});

app.get('/api/recognitions/stream', verifyToken, (req, res) => {
  // 1) Verifizierter User steckt jetzt in req.user
  const uid = String(req.user.id);
  const username = String(req.user.username);

  res.status(200);
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.setHeader('Access-Control-Allow-Origin', FRONTEND_ORIGIN);
  res.setHeader('Access-Control-Allow-Credentials', 'true');
  res.flushHeaders();

  /* --- Map pflegen ------------------------------------------------------ */
  if (!sseClients.has(uid)) sseClients.set(uid, new Set());
  sseClients.get(uid).add(res);

  console.log(`🟢 SSE-Client (${username}) verbunden`);
  
  res.write(': connected\n\n');

  /* --- Ping — egal ob du ihn brauchst oder nicht ------------------------ */
  const ping = setInterval(() => {
    res.write(`event: ping\ndata: ${Date.now()}\n\n`);
  }, 30_000);

  /* --- Aufräumen -------------------------------------------------------- */
  req.on('close', () => {
    clearInterval(ping);
    sseClients.get(uid).delete(res);
    if (sseClients.get(uid).size === 0) sseClients.delete(uid);
    console.log(`🔴 SSE-Client (${username}) getrennt`);
  });
});

app.get('/api/recognitions/count', verifyToken, async (req, res) => {
  const where = buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin);
  const count = await prisma.recognition.count({ where });
  res.json({ count });
});

app.get('/api/recognitions/new-count', verifyToken, async (req, res) => {
  const count = await prisma.recognition.count({
    where: {
      createdAt: { gt: lastResetTimestamp },
      ...buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin),
    },
  });
  res.json({ count });
});

app.get('/api/recognitions/by-camera', verifyToken, async (req, res) => {
  try {
    const isAdmin = req.user?.isAdmin;
    const filter = isAdmin
      ? {}
      : buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin);
      
    const result = await prisma.recognition.findMany({ where: filter, select: { cameraName: true } });

    const counts = result.reduce((acc, { cameraName }) => {
      if (!cameraName) return acc;
      acc[cameraName] = (acc[cameraName] || 0) + 1;
      return acc;
    }, {});

    const labels = Object.keys(counts);
    const series = Object.values(counts);

    res.json({ labels, series });
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions/by-camera:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});


app.get('/api/recognitions/dates', verifyToken, async (req, res) => {
  try {
    const cameraFilter = req.query.camera?.toString();

    // Admins: kein Filter notwendig
    const isAdmin = req.user?.isAdmin;
    const filter = isAdmin
      ? {}
      : buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin);

    const recognitions = await prisma.recognition.findMany({
      where: cameraFilter
        ? { cameraName: cameraFilter, ...filter }
        : filter,
      select: {
        cameraName: true,
        timestampLocal: true,
      },
    });

    if (cameraFilter) {
      const timestamps = recognitions.map(r => r.timestampLocal);
      if (timestamps.length === 0) {
        return res.json([]);
      }

      const startDate = isoDateLocal(new Date(Math.min(...timestamps.map(t => t.getTime()))));
      const endDate   = isoDateLocal(new Date(Math.max(...timestamps.map(t => t.getTime()))));

      return res.json([
        {
          camera: cameraFilter,
          startDate,
          endDate,
        }
      ]);
    } else {
      const grouped = new Map();

      for (const entry of recognitions) {
        const camera = entry.cameraName || 'Unbekannt';
        if (!grouped.has(camera)) grouped.set(camera, []);
        grouped.get(camera).push(entry.timestampLocal);
      }

      const result = Array.from(grouped.entries()).map(([camera, timestamps]) => {
        const startDate = isoDateLocal(new Date(Math.min(...timestamps.map(t => t.getTime()))));
        const endDate   = isoDateLocal(new Date(Math.max(...timestamps.map(t => t.getTime()))));
        return { camera, startDate, endDate };
      });

      res.json(result);
    }
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions/dates:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});


app.get('/api/recognitions/counts', verifyToken, async (req, res) => {
  try {
    const days = parseInt(req.query.days) || 7;

    const today = new Date();
    today.setHours(0, 0, 0, 0);            // lokales 00:00

    const fromDate = new Date(today);
    fromDate.setDate(fromDate.getDate() - (days - 1));

    const entries = await prisma.recognition.findMany({
      where: {
        timestampLocal: {
          gte: fromDate,
          lt: new Date(today.getTime() + 24 * 60 * 60 * 1000), // bis Ende heute
        },
        ...buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin),
      },
      select: { timestampLocal: true },
    });

    const countsMap = new Map();
    for (const { timestampLocal } of entries) {
      const key = localDateKey(timestampLocal);    // ❗ lokal statt ISO/UTC
      countsMap.set(key, (countsMap.get(key) || 0) + 1);
    }

    const counts = [];
    for (let i = 0; i < days; i++) {
      const d = new Date(fromDate);
      d.setDate(fromDate.getDate() + i);
      const key = localDateKey(d);                 // ❗ gleiche Logik
      counts.push({ date: key, count: countsMap.get(key) || 0 });
    }

    res.json(counts);
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions/counts:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});

// /api/recognitions/top10plates
app.get('/api/recognitions/top10plates', verifyToken, async (req, res) => {
  try {
    // Admins sehen alles; sonst Kamera-/Zeitfenster einschränken
    const accessFilter = buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin);

    // 1) In der DB zählen (Top 10)
    const grouped = await prisma.recognition.groupBy({
      by: ['license'],
      where: {
        license: { not: '' },
        // optional: wenn NULL ausgeschlossen werden soll
        // license: { notIn: ['', null] as any },
        ...(req.user.isAdmin ? {} : accessFilter),
      },
      _count: { license: true },
      orderBy: { _count: { license: 'desc' } },
      take: 10,
    });

    // Falls keine Daten, direkt leere Antwort
    if (!grouped.length) {
      return res.json({ data: [] });
    }

    // 2) Für diese Top-Kennzeichen die *neueste* Marke/Modell holen
    const topPlates = grouped.map(g => g.license);

    const latestMeta = await prisma.recognition.findMany({
      where: {
        license: { in: topPlates },
        ...(req.user.isAdmin ? {} : accessFilter),
      },
      select: { license: true, brand: true, model: true, timestampLocal: true },
      orderBy: { timestampLocal: 'desc' }, // neueste zuerst
    });

    // 3) Pro Kennzeichen die erste (neueste) Meta-Zeile nehmen
    const metaByPlate = new Map();
    for (const r of latestMeta) {
      if (!metaByPlate.has(r.license)) {
        metaByPlate.set(r.license, {
          brand: r.brand ?? null,
          model: r.model ?? null,
        });
      }
      // danach ignorieren wir ältere Einträge für dasselbe Kennzeichen
    }

    // 4) Antwort zusammenbauen (Reihenfolge bereits nach Count desc)
    const data = grouped.map(g => ({
      plate: g.license,
      count: g._count.license,
      brand: metaByPlate.get(g.license)?.brand ?? null,
      model: metaByPlate.get(g.license)?.model ?? null,
    }));

    res.json({ data });
  } catch (error) {
    console.error('❌ Fehler bei /api/recognitions/top10plates:', error);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});


app.get('/api/recognitions/top10brands', verifyToken, async (req, res) => {
  try {
    const result = await prisma.recognition.findMany({
      where: { 
        brand: { not: null },
        ...buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin)
      },
      select: { brand: true },
    });

    const brandCounts = {};

    for (const { brand } of result) {
      if (!brand) continue;
      brandCounts[brand] = (brandCounts[brand] || 0) + 1;
    }

    // Sortieren nach Häufigkeit (absteigend)
    const sorted = Object.entries(brandCounts)
      .sort((a, b) => b[1] - a[1])
      .slice(0, 10); // Nur Top 10

    const labels = sorted.map(([brand]) => brand);
    const series = sorted.map(([_, count]) => count);

    res.json({ labels, series });
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions/top10brands:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});

app.get('/api/recognitions/countries', verifyToken, async (req, res) => {
  try {
    const result = await prisma.recognition.findMany({
      where: {
        country: { not: null },
        ...buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin)
      },
      select: { country: true }
    });

    const counts = result.reduce((acc, { country }) => {
      if (!country) return acc;
      acc[country] = (acc[country] || 0) + 1;
      return acc;
    }, {});

    const labels = Object.keys(counts);
    const series = Object.values(counts);

    res.json({ labels, series });
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions/countries:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});

app.get('/api/recognitions/by-hour', verifyToken, async (req, res) => {
  try {
    const result = await prisma.recognition.findMany({
      where: buildCameraAccessFilter(req.user.cameraAccess, req.user.isAdmin),
      select: {
        timestampLocal: true,
      },
    });

    const hourCounts = Array(24).fill(0);

    for (const entry of result) {
      const hour = entry.timestampLocal.getHours();
      hourCounts[hour]++;
    }

    const labels = Array.from({ length: 24 }, (_, i) => {
      const pad = (n) => n.toString().padStart(2, '0');
      return `${pad(i)}:00–${pad((i + 1) % 24)}:00`;
    });

    res.json({ labels, series: hourCounts });
  } catch (err) {
    console.error('❌ Fehler bei /api/recognitions/by-hour:', err);
    res.status(500).json({ error: 'Interner Serverfehler' });
  }
});

app.get('/images/:filename', verifyToken, async (req, res) => {
  const filename = req.params.filename;
  const imagePath = await findImageFile(filename);

  if (!imagePath || !fs.existsSync(imagePath)) {
    return res.status(404).send('Bild nicht gefunden.');
  }

  try {
    const data = await fs.promises.readFile(imagePath);
    res.setHeader('Content-Type', 'image/jpeg');
    res.setHeader('Cache-Control', 'public, max-age=31536000');
    res.send(data);
  } catch (err) {
    console.error('❌ Fehler beim Lesen der Datei:', err);
    res.status(500).send('Fehler beim Laden des Bildes.');
  }
});

app.get('/api/admin/users', verifyToken, async (req, res) => {
  if (!req.user?.isAdmin) {
    return res.status(403).json({ error: 'Nicht autorisiert' });
  }

  try {
    const rows = await prisma.user.findMany({
      orderBy: { username: 'asc' },
      select: {
        id: true,
        username: true,
        isAdmin: true,
        expiresAt: true,
        lastLogin: true,
        cameraAccess: {
          select: {
            id: true,
            camera: true,
            from: true,
            to: true,
          },
        },
        features: { select: { feature: true } },
      },
    });
    
    // Normalisieren (ISO-Strings oder null) – passend zu deinem Frontend-Typ UserWithAccess
    const users = rows.map((u) => ({
      id: u.id,
      username: u.username,
      isAdmin: u.isAdmin,
      expiresAt: u.expiresAt ? u.expiresAt.toISOString() : null,
      lastLogin: u.lastLogin ? u.lastLogin.toISOString() : null,
      cameraAccess: u.cameraAccess.map((c) => ({
        id: c.id,
        camera: c.camera,
        from: c.from ? c.from.toISOString() : null,
        to: c.to ? c.to.toISOString() : null,
      })),
      features: u.features.map(f => f.feature),
    }));
    
    res.json({ users });
  } catch (err) {
    console.error('Fehler beim Laden der Benutzer:', err);
    res.status(500).json({ error: 'Fehler beim Laden der Benutzer' });
  }
});

// === HTTPS-Server starten ===
const keyPath  = path.resolve(__dirname, 'certs', 'myRoot.key');
const certPath = path.resolve(__dirname, 'certs', 'myRoot.crt');

const sslOptions = {
  key:  fs.readFileSync(keyPath),
  cert: fs.readFileSync(certPath),
};

https.createServer(sslOptions, app).listen(API_PORT, API_BIND, () => {
  console.log(`✅ HTTPS-Server läuft auf https://${API_BIND}:${API_PORT}`);
});

// === XML-Parser und Dateiüberwachung ===
const parser = new XMLParser({ ignoreAttributes: false });
const fileQueue = [];
let processing = false;

// === Watcher starten ===
chokidar.watch(WATCH_PATH, {
  usePolling: true,
  interval: 1000,
  binaryInterval: 3000,
  alwaysStat: true,
  ignored: /^\./,
  persistent: true,
  ignoreInitial: true,
  depth: 99,
}).on('add', (filePath) => {
  if (filePath.endsWith('_Info.xml')) enqueueFile(filePath);
});

// === Initial-Import ===

cleanUpExpiredTokens();
setInterval(cleanUpExpiredTokens, 24 * 60 * 60 * 1000); // alle 24 Stunden
findAllXmlFiles(WATCH_PATH);