Ninjaserver/config/blacklist-db.js

/**
 * Datenbankbasierte Blacklist für unerwünschte Namen
 * Lädt und verwaltet Blacklist-Einträge aus der Datenbank
 */

const { checkWithCategoryThreshold, checkWithTrigramIndex, TrigramIndex, THRESHOLDS } = require('./levenshtein');

// Datenbankverbindung direkt hier implementieren
const { Pool } = require('pg');

const pool = new Pool({
    user: process.env.DB_USER || 'postgres',
    host: process.env.DB_HOST || 'localhost',
    database: process.env.DB_NAME || 'ninjaserver',
    password: process.env.DB_PASSWORD || 'password',
    port: process.env.DB_PORT || 5432,
});

// Trigram-Index für Performance-Optimierung
let trigramIndex = null;
let blacklistCache = null;
let lastCacheUpdate = 0;
const CACHE_TTL = 5 * 60 * 1000; // 5 Minuten

/**
 * Lädt alle Blacklist-Einträge aus der Datenbank mit Caching
 * @returns {Object} - Blacklist gruppiert nach Kategorien
 */
async function loadBlacklistFromDB() {
    const now = Date.now();

    // Verwende Cache falls verfügbar und nicht abgelaufen
    if (blacklistCache && (now - lastCacheUpdate) < CACHE_TTL) {
        return blacklistCache;
    }

    try {
        const result = await pool.query(
            'SELECT term, category FROM blacklist_terms ORDER BY category, term'
        );

        const blacklist = {
            historical: [],
            offensive: [],
            titles: [],
            brands: [],
            inappropriate: [],
            racial: [],
            religious: [],
            disability: [],
            leetspeak: [],
            cyberbullying: [],
            drugs: [],
            violence: []
        };

        // Erstelle neuen Trigram-Index
        trigramIndex = new TrigramIndex();

        result.rows.forEach(row => {
            if (blacklist[row.category]) {
                blacklist[row.category].push(row.term);
                // Füge zum Trigram-Index hinzu
                trigramIndex.addTerm(row.term, row.category);
            }
        });

        // Cache aktualisieren
        blacklistCache = blacklist;
        lastCacheUpdate = now;

        return blacklist;
    } catch (error) {
        console.error('Error loading blacklist from database:', error);
        // Fallback zur statischen Blacklist
        return getStaticBlacklist();
    }
}

/**
 * Statische Blacklist als Fallback
 */
function getStaticBlacklist() {
    return {
        historical: [
            'adolf', 'hitler', 'adolf hitler', 'adolfhittler',
            'mussolini', 'benito', 'benito mussolini',
            'stalin', 'joseph stalin', 'mao', 'mao zedong'
        ],
        offensive: [
            'satan', 'luzifer', 'teufel', 'devil',
            'hurensohn', 'wichser', 'fotze', 'arschloch',
            'idiot', 'dummkopf', 'trottel', 'schwachsinnig',
            'nazi', 'faschist', 'rassist'
        ],
        titles: [
            'lord', 'lady', 'sir', 'dame',
            'prinz', 'prinzessin', 'prince', 'princess',
            'könig', 'königin', 'king', 'queen',
            'doktor', 'professor', 'dr', 'prof'
        ],
        brands: [
            'mcdonald', 'coca cola', 'cocacola', 'pepsi',
            'nike', 'adidas', 'puma', 'reebok',
            'bmw', 'mercedes', 'audi', 'volkswagen'
        ],
        inappropriate: [
            'sex', 'porn', 'porno', 'fuck', 'shit',
            'bitch', 'whore', 'prostitute',
            'drug', 'cocaine', 'heroin', 'marijuana'
        ],
        racial: [],
        religious: [],
        disability: [],
        leetspeak: [],
        cyberbullying: [],
        drugs: [],
        violence: []
    };
}

/**
 * Prüft ob ein Name in der Blacklist steht (exakte Übereinstimmung)
 * @param {string} firstname - Vorname
 * @param {string} lastname - Nachname
 * @returns {Object} - {isBlocked: boolean, reason: string, category: string}
 */
async function checkNameAgainstBlacklist(firstname, lastname) {
    if (!firstname || !lastname) {
        return { isBlocked: false, reason: '', category: '' };
    }

    try {
        const blacklist = await loadBlacklistFromDB();
        const fullName = `${firstname.toLowerCase()} ${lastname.toLowerCase()}`;
        const firstNameOnly = firstname.toLowerCase();
        const lastNameOnly = lastname.toLowerCase();

        // Alle Blacklist-Einträge in einem Array sammeln
        const allBlacklistEntries = [];

        Object.entries(blacklist).forEach(([category, entries]) => {
            entries.forEach(entry => {
                allBlacklistEntries.push({
                    term: entry,
                    category: category,
                    reason: getCategoryReason(category)
                });
            });
        });

        // 1. Exakte Übereinstimmung prüfen
        for (const entry of allBlacklistEntries) {
            const term = entry.term.toLowerCase();

            // Vollständiger Name
            if (fullName.includes(term) || term.includes(fullName)) {
                return {
                    isBlocked: true,
                    reason: entry.reason,
                    category: entry.category,
                    matchedTerm: entry.term,
                    matchType: 'exact'
                };
            }

            // Vorname allein
            if (firstNameOnly.includes(term) || term.includes(firstNameOnly)) {
                return {
                    isBlocked: true,
                    reason: entry.reason,
                    category: entry.category,
                    matchedTerm: entry.term,
                    matchType: 'exact'
                };
            }

            // Nachname allein
            if (lastNameOnly.includes(term) || term.includes(lastNameOnly)) {
                return {
                    isBlocked: true,
                    reason: entry.reason,
                    category: entry.category,
                    matchedTerm: entry.term,
                    matchType: 'exact'
                };
            }
        }

        // 2. Levenshtein-Distanz prüfen (Fuzzy-Matching)
        // Verwende Trigram-Index für bessere Performance bei großen Blacklists
        let levenshteinResult;
        if (trigramIndex && Object.values(blacklist).flat().length > 100) {
            // Performance-optimierte Version für große Blacklists
            levenshteinResult = checkWithTrigramIndex(firstname, lastname, blacklist, trigramIndex);
        } else {
            // Standard-Version für kleine Blacklists
            for (const [category, entries] of Object.entries(blacklist)) {
                const categoryResult = checkWithCategoryThreshold(firstname, lastname, entries, category);
                if (categoryResult.hasSimilarTerms) {
                    levenshteinResult = categoryResult;
                    break; // Frühe Beendigung bei erstem Match
                }
            }
        }

        if (levenshteinResult && levenshteinResult.hasSimilarTerms) {
            const bestMatch = levenshteinResult.bestMatch;
            return {
                isBlocked: true,
                reason: `${getCategoryReason(bestMatch.category || 'unknown')} (ähnlich)`,
                category: bestMatch.category || 'unknown',
                matchedTerm: bestMatch.term,
                matchType: 'similar',
                similarity: bestMatch.distance,
                levenshteinDistance: bestMatch.levenshteinDistance
            };
        }

        return { isBlocked: false, reason: '', category: '' };
    } catch (error) {
        console.error('Error checking name against blacklist:', error);
        return { isBlocked: false, reason: '', category: '' };
    }
}

/**
 * Gibt eine benutzerfreundliche Begründung für die Kategorie zurück
 */
function getCategoryReason(category) {
    const reasons = {
        historical: 'Historisch belasteter Name',
        offensive: 'Beleidigender oder anstößiger Begriff',
        titles: 'Titel oder Berufsbezeichnung',
        brands: 'Markenname',
        inappropriate: 'Unpassender Begriff',
        racial: 'Rassistischer oder ethnisch beleidigender Begriff',
        religious: 'Religiös beleidigender oder blasphemischer Begriff',
        disability: 'Beleidigender Begriff bezüglich Behinderungen',
        leetspeak: 'Verschleierter beleidigender Begriff',
        cyberbullying: 'Cyberbullying oder Online-Belästigung',
        drugs: 'Drogenbezogener Begriff',
        violence: 'Gewalt- oder bedrohungsbezogener Begriff'
    };

    return reasons[category] || 'Unzulässiger Begriff';
}

/**
 * Fügt einen neuen Begriff zur Blacklist hinzu
 * @param {string} term - Der hinzuzufügende Begriff
 * @param {string} category - Die Kategorie
 * @param {string} createdBy - Wer hat den Begriff hinzugefügt
 */
async function addToBlacklist(term, category, createdBy = 'admin') {
    try {
        await pool.query(
            'INSERT INTO blacklist_terms (term, category, created_by) VALUES ($1, $2, $3) ON CONFLICT (term, category) DO NOTHING',
            [term.toLowerCase(), category, createdBy]
        );

        // Cache invalidieren
        invalidateCache();

        return true;
    } catch (error) {
        console.error('Error adding to blacklist:', error);
        throw error;
    }
}

/**
 * Entfernt einen Begriff aus der Blacklist
 * @param {string} term - Der zu entfernende Begriff
 * @param {string} category - Die Kategorie
 */
async function removeFromBlacklist(term, category) {
    try {
        const result = await pool.query(
            'DELETE FROM blacklist_terms WHERE term = $1 AND category = $2',
            [term.toLowerCase(), category]
        );

        // Cache invalidieren
        invalidateCache();

        return result.rowCount > 0;
    } catch (error) {
        console.error('Error removing from blacklist:', error);
        throw error;
    }
}

/**
 * Invalidiert den Blacklist-Cache
 */
function invalidateCache() {
    blacklistCache = null;
    trigramIndex = null;
    lastCacheUpdate = 0;
}

/**
 * Gibt die komplette Blacklist zurück (für Admin-Zwecke)
 */
async function getBlacklist() {
    return await loadBlacklistFromDB();
}

/**
 * Synchronisiert die statische Blacklist mit der Datenbank
 * (Nur für Initial-Setup oder Migration)
 */
async function syncStaticBlacklist() {
    const staticBlacklist = getStaticBlacklist();

    for (const [category, terms] of Object.entries(staticBlacklist)) {
        for (const term of terms) {
            try {
                await addToBlacklist(term, category, 'system');
            } catch (error) {
                console.error(`Error syncing term ${term} in category ${category}:`, error);
            }
        }
    }
}

module.exports = {
    checkNameAgainstBlacklist,
    addToBlacklist,
    removeFromBlacklist,
    getBlacklist,
    loadBlacklistFromDB,
    syncStaticBlacklist
};