redmine_ticket_portal/server.js

import express from "express";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { fetchCustomersFromSql } from "./db.js";

const __dirname = path.dirname(fileURLToPath(import.meta.url));

// --- Konfiguration (per Umgebungsvariablen, siehe .env.example) ---
const config = {
    port: Number(process.env.PORT) || 3000,
    redmineUrl: (process.env.REDMINE_URL || "https://redmine.unicon-gmbh.de/redmine").replace(/\/+$/, ""),
    redmineApiKey: process.env.REDMINE_API_KEY || "",
    projectId: Number(process.env.REDMINE_PROJECT_ID) || 1,
    trackerId: Number(process.env.REDMINE_TRACKER_ID) || 3,
    customerFieldId: Number(process.env.CUSTOMER_FIELD_ID) || 5,
    customersRefreshMs: Number(process.env.CUSTOMERS_REFRESH_MS) || 5 * 60 * 1000,
};

// Ohne API-Key läuft der Server im Trockenlauf: Tickets werden nur simuliert.
const dryRun = config.redmineApiKey === "";

// --- Kundenstammdaten aus dem WAWIS-SQL-Server (nur lesend, siehe db.js) ---
// gesperrte/inaktive Kunden werden für die Ticket-Anlage ausgeblendet
let customers = [];
let customersByNumber = new Map();

// Felder, die zusätzlich zu Nummer/Name über die erweiterte Suche durchsucht werden können
const ADVANCED_SEARCH_FIELDS = ["kugru", "kg_id", "sydat", "datlv", "fibuk"];

function applyCustomers(rows) {
    customers = rows
        .filter((row) => row.baktiv === "ja" && row.bsper !== "ja")
        .map((row) => ({
            number: String(row.debnr).trim(),
            name: String(row.debez || "").trim(),
            kugru: row.kugru,
            kg_id: row.kg_id,
            sydat: row.sydat,
            datlv: row.datlv,
            fibuk: row.fibuk,
        }))
        .filter((customer) => customer.number !== "" && customer.name !== "");
    customersByNumber = new Map(customers.map((customer) => [customer.number, customer]));
}

async function refreshCustomers() {
    const rows = await fetchCustomersFromSql();
    applyCustomers(rows);
    console.log(`${customers.length} aktive Kunden aus dem SQL-Server geladen`);
}

try {
    await refreshCustomers();
} catch (error) {
    console.error(`Kundendaten konnten nicht vom SQL-Server geladen werden: ${error.message}`);
    process.exit(1);
}

// Periodische Aktualisierung im Hintergrund; bei Fehler bleiben die alten Daten aktiv.
setInterval(() => {
    refreshCustomers().catch((error) => {
        console.error(`Aktualisierung der Kundendaten fehlgeschlagen, alte Daten bleiben aktiv: ${error.message}`);
    });
}, config.customersRefreshMs);

// --- Suche: Treffer am Wortanfang zuerst, dann Teilstring-Treffer ---
// extraFields (aus ADVANCED_SEARCH_FIELDS) erweitert die durchsuchten Werte um Felder aus der Abfrage
// Bei mehreren durch Leerzeichen getrennten Wörtern müssen alle Wörter im Treffer vorkommen (UND-Verknüpfung)
function searchCustomers(query, extraFields, limit) {
    const words = query.toLocaleLowerCase("de-DE").split(/\s+/).filter(Boolean);
    const startMatches = [];
    const partialMatches = [];

    for (const customer of customers) {
        const haystackParts = [customer.number, customer.name];
        for (const field of extraFields) {
            if (customer[field] !== undefined && customer[field] !== null) {
                haystackParts.push(String(customer[field]));
            }
        }
        const haystack = haystackParts.join(" ").toLocaleLowerCase("de-DE");
        if (!words.every((word) => haystack.includes(word))) continue;

        // Werte aller ausgewählten erweiterten Felder, unabhängig davon, welches Wort den Treffer ausgelöst hat
        const extraMatches = extraFields
            .map((field) => customer[field])
            .filter((value) => value !== undefined && value !== null && String(value).trim() !== "")
            .map((value) => String(value));

        const match = { number: customer.number, name: customer.name, extraMatches };
        const isStartMatch = words.every((word) => haystack.startsWith(word) || haystack.includes(` ${word}`));
        if (isStartMatch) { startMatches.push(match); } else { partialMatches.push(match); }
        if (startMatches.length >= limit) break;
    }

    return startMatches.concat(partialMatches).slice(0, limit);
}

// --- Redmine-Anbindung ---
async function createRedmineIssue(payload) {
    if (dryRun) {
        return { id: Math.floor(Math.random() * 9000) + 1000, dryRun: true };
    }

    const response = await fetch(`${config.redmineUrl}/issues.json`, {
        method: "POST",
        headers: {
            "Content-Type": "application/json",
            "X-Redmine-API-Key": config.redmineApiKey,
        },
        body: JSON.stringify({ issue: payload }),
    });

    if (response.status === 201) {
        const body = await response.json();
        return { id: body.issue.id, dryRun: false };
    }

    let details = `HTTP ${response.status}`;
    try {
        const errorBody = await response.json();
        if (Array.isArray(errorBody.errors)) { details = errorBody.errors.join(", "); }
    } catch { /* Antwort war kein JSON, Status reicht als Info */ }

    const redmineError = new Error(details);
    redmineError.statusCode = response.status;
    throw redmineError;
}

// --- HTTP-Server ---
const app = express();
app.use(express.json());
app.use(express.static(path.join(__dirname, "public")));

// GET /api/customers?search=... -> Trefferliste für das Autocomplete
app.get("/api/customers", (request, response) => {
    const query = String(request.query.search || "").trim();
    if (query.length < 2) {
        return response.json({ results: [], total: customers.length });
    }
    const extraFields = String(request.query.fields || "")
        .split(",")
        .map((field) => field.trim())
        .filter((field) => ADVANCED_SEARCH_FIELDS.includes(field));
    const matches = searchCustomers(query, extraFields, 12);
    const results = matches.map((customer) => ({
        number: customer.number,
        name: customer.name,
        extraMatches: customer.extraMatches,
    }));
    return response.json({ results, total: customers.length });
});

// POST /api/tickets -> validiert den Kundenbezug und legt das Ticket in Redmine an
app.post("/api/tickets", async (request, response) => {
    const { customerNumber, subject, description } = request.body || {};

    const customer = customersByNumber.get(String(customerNumber || "").trim());
    if (!customer) {
        return response.status(422).json({ error: "Unbekannte Kundennummer. Bitte einen Kunden aus der Vorschlagsliste wählen." });
    }
    if (!subject || String(subject).trim().length < 5) {
        return response.status(422).json({ error: "Der Betreff muss mindestens 5 Zeichen lang sein." });
    }

    const issuePayload = {
        project_id: config.projectId,
        tracker_id: config.trackerId,
        subject: String(subject).trim(),
        description: String(description || "").trim(),
        custom_fields: [{ id: config.customerFieldId, value: customer.number }],
    };

    try {
        const created = await createRedmineIssue(issuePayload);
        return response.status(201).json({
            issueId: created.id,
            issueUrl: `${config.redmineUrl}/issues/${created.id}`,
            customer,
            dryRun: created.dryRun,
        });
    } catch (error) {
        const statusCode = error.statusCode === 422 ? 422 : 502;
        console.error(`Ticket-Anlage fehlgeschlagen: ${error.message}`);
        return response.status(statusCode).json({ error: `Redmine hat das Ticket abgelehnt: ${error.message}` });
    }
});

// Zentraler Fehler-Handler, damit keine Stacktraces an den Client gehen
app.use((error, request, response, next) => {
    console.error(error);
    response.status(500).json({ error: "Interner Fehler. Details stehen im Server-Log." });
});

app.listen(config.port, () => {
    const mode = dryRun ? "TROCKENLAUF (kein REDMINE_API_KEY gesetzt, Tickets werden simuliert)" : `verbunden mit ${config.redmineUrl}`;
    console.log(`Ticket-Portal läuft auf http://localhost:${config.port} - Modus: ${mode}`);
});