217 lines
16 KiB
JavaScript
217 lines
16 KiB
JavaScript
import sharp from 'sharp';
|
||
import { existsSync } from 'fs';
|
||
import { resolve, sep } from 'path';
|
||
import { db, getListing, getListings } from './db.mjs';
|
||
import { calculateFees } from './fees.mjs';
|
||
|
||
const PLATFORM_PROFILES = {
|
||
'ebay-de': { name: 'eBay', titleMin: 12, titleMax: 80, descriptionMin: 80, recommendedPhotos: 4 },
|
||
vinted: { name: 'Vinted', titleMin: 8, titleMax: 100, descriptionMin: 45, recommendedPhotos: 3 },
|
||
'ebay-ka': { name: 'Kleinanzeigen', titleMin: 10, titleMax: 65, descriptionMin: 60, recommendedPhotos: 4 },
|
||
etsy: { name: 'Etsy', titleMin: 20, titleMax: 140, descriptionMin: 120, recommendedPhotos: 5 },
|
||
};
|
||
|
||
function clamp(value, min, max) { return Math.max(min, Math.min(max, value)); }
|
||
function normalizeText(value) { return String(value || '').replace(/\s+/g, ' ').trim(); }
|
||
function wordTokens(value) {
|
||
return new Set(normalizeText(value).toLowerCase().replace(/[^a-z0-9äöüß ]/gi, ' ').split(/\s+/).filter(token => token.length > 2));
|
||
}
|
||
function jaccard(a, b) {
|
||
const left = wordTokens(a); const right = wordTokens(b);
|
||
if (!left.size || !right.size) return 0;
|
||
let intersection = 0;
|
||
for (const token of left) if (right.has(token)) intersection += 1;
|
||
return intersection / (left.size + right.size - intersection);
|
||
}
|
||
function gradeFor(score) {
|
||
if (score >= 92) return 'A';
|
||
if (score >= 82) return 'B';
|
||
if (score >= 70) return 'C';
|
||
if (score >= 55) return 'D';
|
||
return 'E';
|
||
}
|
||
function issue({ code, severity = 'recommendation', category, title, detail, points = 0, field = null, action = null }) {
|
||
return { code, severity, category, title, detail, points, field, action };
|
||
}
|
||
function safeParseJson(value, fallback) {
|
||
try { const parsed = JSON.parse(value); return parsed ?? fallback; } catch { return fallback; }
|
||
}
|
||
|
||
function findDuplicates(listing, allListings) {
|
||
const candidates = [];
|
||
const title = normalizeText(listing.title);
|
||
for (const other of allListings) {
|
||
if (Number(other.id) === Number(listing.id)) continue;
|
||
const similarity = jaccard(title, other.title);
|
||
const sameSku = listing.sku && other.sku && String(listing.sku).toLowerCase() === String(other.sku).toLowerCase();
|
||
if (sameSku || similarity >= 0.72) {
|
||
candidates.push({ id: other.id, title: other.title, sku: other.sku, similarity: Math.round(similarity * 100), same_sku: !!sameSku });
|
||
}
|
||
}
|
||
return candidates.sort((a, b) => Number(b.same_sku) - Number(a.same_sku) || b.similarity - a.similarity).slice(0, 5);
|
||
}
|
||
|
||
function inspectCore(listing, allListings) {
|
||
const issues = [];
|
||
const title = normalizeText(listing.title);
|
||
const description = normalizeText(listing.description);
|
||
const photos = Array.isArray(listing.photos) ? listing.photos.filter(Boolean) : [];
|
||
const tags = Array.isArray(listing.tags) ? listing.tags.filter(Boolean) : [];
|
||
const profile = PLATFORM_PROFILES[listing.platform] || { name: listing.platform || 'Allgemein', titleMin: 10, titleMax: 100, descriptionMin: 60, recommendedPhotos: 3 };
|
||
|
||
if (!title) issues.push(issue({ code: 'missing_title', severity: 'error', category: 'Inhalt', title: 'Titel fehlt', detail: 'Ohne Titel kann der Artikel nicht zuverlässig veröffentlicht werden.', points: 20, field: 'title' }));
|
||
else {
|
||
if (title.length < profile.titleMin) issues.push(issue({ code: 'title_too_short', severity: 'warning', category: 'Inhalt', title: 'Titel ist sehr kurz', detail: `Für das ${profile.name}-Profil werden mindestens ${profile.titleMin} Zeichen empfohlen.`, points: 6, field: 'title', action: 'ai_title' }));
|
||
if (title.length > profile.titleMax) issues.push(issue({ code: 'title_too_long', severity: 'error', category: 'Plattform', title: 'Titel ist zu lang', detail: `Das aktive Vendoo-Profil sieht höchstens ${profile.titleMax} Zeichen vor.`, points: 10, field: 'title', action: 'ai_title' }));
|
||
if (/\b(l@@k|mega|hammer|wow|!!!)\b/i.test(title) || /!{2,}/.test(title)) issues.push(issue({ code: 'title_spam', severity: 'recommendation', category: 'Inhalt', title: 'Titel wirkt werblich', detail: 'Sachliche Titel sind leichter lesbar und wirken vertrauenswürdiger.', points: 2, field: 'title', action: 'ai_title' }));
|
||
}
|
||
|
||
if (!description) issues.push(issue({ code: 'missing_description', severity: 'error', category: 'Inhalt', title: 'Beschreibung fehlt', detail: 'Zustand, Lieferumfang und relevante Besonderheiten sollten beschrieben werden.', points: 16, field: 'description', action: 'ai_description' }));
|
||
else {
|
||
if (description.length < profile.descriptionMin) issues.push(issue({ code: 'description_too_short', severity: 'warning', category: 'Inhalt', title: 'Beschreibung ist knapp', detail: `Für das ${profile.name}-Profil werden mindestens ${profile.descriptionMin} Zeichen empfohlen.`, points: 6, field: 'description', action: 'ai_description' }));
|
||
if (!/[.!?]$/.test(description)) issues.push(issue({ code: 'description_unfinished', severity: 'recommendation', category: 'Inhalt', title: 'Beschreibung wirkt unvollständig', detail: 'Ein sauberer Abschluss verbessert die Lesbarkeit.', points: 1, field: 'description' }));
|
||
}
|
||
|
||
if (!photos.length) issues.push(issue({ code: 'missing_photos', severity: 'error', category: 'Bilder', title: 'Keine Bilder vorhanden', detail: 'Mindestens ein aussagekräftiges Originalbild ist erforderlich.', points: 20, field: 'photos' }));
|
||
else if (photos.length < profile.recommendedPhotos) issues.push(issue({ code: 'few_photos', severity: 'warning', category: 'Bilder', title: 'Wenige Bilder', detail: `${profile.recommendedPhotos} oder mehr Perspektiven werden für dieses Profil empfohlen.`, points: clamp((profile.recommendedPhotos - photos.length) * 2, 2, 7), field: 'photos' }));
|
||
|
||
if (!(Number(listing.price) > 0)) issues.push(issue({ code: 'missing_price', severity: 'error', category: 'Preis', title: 'Preis fehlt', detail: 'Ein positiver Verkaufspreis ist erforderlich.', points: 12, field: 'price' }));
|
||
if (!normalizeText(listing.condition)) issues.push(issue({ code: 'missing_condition', severity: 'error', category: 'Metadaten', title: 'Zustand fehlt', detail: 'Der Zustand ist für Käufer und Plattformformulare wichtig.', points: 8, field: 'condition' }));
|
||
if (!normalizeText(listing.category)) issues.push(issue({ code: 'missing_category', severity: 'error', category: 'Metadaten', title: 'Kategorie fehlt', detail: 'Eine passende Kategorie verbessert Auffindbarkeit und Publishing.', points: 8, field: 'category' }));
|
||
if (!normalizeText(listing.brand)) issues.push(issue({ code: 'missing_brand', severity: 'recommendation', category: 'Metadaten', title: 'Marke nicht angegeben', detail: 'Marke ergänzen, sofern sie am Produkt sicher erkennbar ist.', points: 3, field: 'brand' }));
|
||
if (!normalizeText(listing.color)) issues.push(issue({ code: 'missing_color', severity: 'recommendation', category: 'Metadaten', title: 'Farbe nicht angegeben', detail: 'Eine Farbangabe erleichtert Suche und Filterung.', points: 2, field: 'color' }));
|
||
if (!tags.length) issues.push(issue({ code: 'missing_tags', severity: 'recommendation', category: 'Reichweite', title: 'Keine Suchbegriffe', detail: 'Passende Tags oder Suchbegriffe können die Auffindbarkeit verbessern.', points: 3, field: 'tags', action: 'ai_tags' }));
|
||
if (!normalizeText(listing.sku)) issues.push(issue({ code: 'missing_sku', severity: 'warning', category: 'Organisation', title: 'SKU fehlt', detail: 'Eine eindeutige Artikelnummer erleichtert Lager und Publishing.', points: 4, field: 'sku' }));
|
||
if (!normalizeText(listing.storage_location)) issues.push(issue({ code: 'missing_storage', severity: 'recommendation', category: 'Organisation', title: 'Kein Lagerort', detail: 'Ein Lagerort verhindert Suchaufwand nach dem Verkauf.', points: 2, field: 'storage_location' }));
|
||
|
||
const duplicates = findDuplicates(listing, allListings);
|
||
if (duplicates.some(item => item.same_sku)) issues.push(issue({ code: 'duplicate_sku', severity: 'error', category: 'Duplikate', title: 'Doppelte SKU möglich', detail: 'Mindestens ein anderer Artikel verwendet dieselbe SKU.', points: 10, field: 'sku' }));
|
||
else if (duplicates.length) issues.push(issue({ code: 'possible_duplicate', severity: 'warning', category: 'Duplikate', title: 'Ähnlicher Artikel gefunden', detail: 'Titel und Produktdaten ähneln mindestens einem bestehenden Artikel.', points: 5 }));
|
||
|
||
const penalty = issues.reduce((sum, entry) => sum + Number(entry.points || 0), 0);
|
||
const score = clamp(100 - penalty, 0, 100);
|
||
const blockers = issues.filter(entry => entry.severity === 'error');
|
||
return {
|
||
listing_id: listing.id,
|
||
score,
|
||
grade: gradeFor(score),
|
||
blockers: blockers.length,
|
||
warnings: issues.filter(entry => entry.severity === 'warning').length,
|
||
recommendations: issues.filter(entry => entry.severity === 'recommendation').length,
|
||
ready: blockers.length === 0 && score >= 70,
|
||
platform: { id: listing.platform || '', name: profile.name, profile },
|
||
issues,
|
||
duplicates,
|
||
facts: {
|
||
title_length: title.length,
|
||
description_length: description.length,
|
||
photo_count: photos.length,
|
||
tag_count: tags.length,
|
||
},
|
||
listing: {
|
||
id: listing.id, title: listing.title, sku: listing.sku, platform: listing.platform, status: listing.status,
|
||
price: listing.price, photo: photos[0] || null, updated_at: listing.updated_at, created_at: listing.created_at,
|
||
},
|
||
};
|
||
}
|
||
|
||
async function inspectImages(report, listing, uploadRoot) {
|
||
const details = [];
|
||
for (const photo of (listing.photos || []).slice(0, 12)) {
|
||
const relative = String(photo || '').replace(/^[/\\]+/, '');
|
||
const absolute = resolve(uploadRoot, relative);
|
||
if (absolute === uploadRoot || !absolute.startsWith(uploadRoot + sep) || !existsSync(absolute)) {
|
||
details.push({ path: relative, missing: true });
|
||
report.issues.push(issue({ code: 'photo_missing_file', severity: 'error', category: 'Bilder', title: 'Bilddatei fehlt', detail: `Die Datei ${relative || 'unbekannt'} wurde nicht gefunden.`, points: 8, field: 'photos' }));
|
||
continue;
|
||
}
|
||
try {
|
||
const meta = await sharp(absolute).metadata();
|
||
const width = Number(meta.width || 0); const height = Number(meta.height || 0);
|
||
const megapixels = Math.round((width * height / 1_000_000) * 10) / 10;
|
||
details.push({ path: relative, width, height, megapixels, format: meta.format || '' });
|
||
if (Math.min(width, height) < 700) report.issues.push(issue({ code: 'photo_low_resolution', severity: 'warning', category: 'Bilder', title: 'Niedrige Bildauflösung', detail: `${relative}: ${width} × ${height} px.`, points: 3, field: 'photos' }));
|
||
if (width === height && width < 1000) report.issues.push(issue({ code: 'photo_small_square', severity: 'recommendation', category: 'Bilder', title: 'Kleines quadratisches Bild', detail: `${relative} ist quadratisch, aber relativ klein.`, points: 1, field: 'photos' }));
|
||
} catch {
|
||
details.push({ path: relative, unreadable: true });
|
||
report.issues.push(issue({ code: 'photo_unreadable', severity: 'error', category: 'Bilder', title: 'Bild nicht lesbar', detail: `${relative} konnte nicht analysiert werden.`, points: 6, field: 'photos' }));
|
||
}
|
||
}
|
||
report.images = details;
|
||
const penalty = report.issues.reduce((sum, entry) => sum + Number(entry.points || 0), 0);
|
||
report.score = clamp(100 - penalty, 0, 100);
|
||
report.grade = gradeFor(report.score);
|
||
report.blockers = report.issues.filter(entry => entry.severity === 'error').length;
|
||
report.warnings = report.issues.filter(entry => entry.severity === 'warning').length;
|
||
report.recommendations = report.issues.filter(entry => entry.severity === 'recommendation').length;
|
||
report.ready = report.blockers === 0 && report.score >= 70;
|
||
return report;
|
||
}
|
||
|
||
function persistReport(report) {
|
||
const result = db.prepare(`INSERT INTO quality_reports (listing_id, score, grade, ready, blockers, warnings, recommendations, report_json, analyzed_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`).run(
|
||
report.listing_id, report.score, report.grade, report.ready ? 1 : 0, report.blockers, report.warnings, report.recommendations, JSON.stringify(report)
|
||
);
|
||
return Number(result.lastInsertRowid);
|
||
}
|
||
|
||
export function getLatestQualityReport(listingId) {
|
||
const row = db.prepare('SELECT * FROM quality_reports WHERE listing_id = ? ORDER BY id DESC LIMIT 1').get(listingId);
|
||
if (!row) return null;
|
||
const report = safeParseJson(row.report_json, null);
|
||
return report ? { ...report, report_id: row.id, analyzed_at: row.analyzed_at } : null;
|
||
}
|
||
|
||
export async function analyzeListingQuality(listingId, { uploadRoot, persist = true } = {}) {
|
||
const listing = getListing(listingId);
|
||
if (!listing) return null;
|
||
const allListings = getListings();
|
||
let report = inspectCore(listing, allListings);
|
||
if (uploadRoot) report = await inspectImages(report, listing, uploadRoot);
|
||
const fee = calculateFees(listing.platform, Number(listing.price) || 0);
|
||
report.financials = { price: Number(listing.price) || 0, fees: fee.fees || 0, estimated_net: fee.net || 0, note: fee.note || '', breakdown: fee.breakdown || '' };
|
||
report.analyzed_at = new Date().toISOString();
|
||
if (persist) report.report_id = persistReport(report);
|
||
return report;
|
||
}
|
||
|
||
export function listQualityCenter({ query = '', status = 'all', grade = 'all', page = 1, pageSize = 10 } = {}) {
|
||
const allListings = getListings();
|
||
const allReports = allListings.map(listing => {
|
||
const latest = getLatestQualityReport(listing.id);
|
||
const listingChangedAt = Date.parse(String(listing.updated_at || listing.created_at || '').replace(' ', 'T') + 'Z') || 0;
|
||
const analyzedAt = Date.parse(String(latest?.analyzed_at || '').replace(' ', 'T') + 'Z') || 0;
|
||
return latest && analyzedAt >= listingChangedAt ? latest : inspectCore(listing, allListings);
|
||
});
|
||
let reports = [...allReports];
|
||
const q = normalizeText(query).toLowerCase();
|
||
if (q) reports = reports.filter(item => [item.listing?.title, item.listing?.sku, item.platform?.name].some(value => String(value || '').toLowerCase().includes(q)));
|
||
if (status === 'ready') reports = reports.filter(item => item.ready);
|
||
if (status === 'blocked') reports = reports.filter(item => item.blockers > 0);
|
||
if (status === 'review') reports = reports.filter(item => !item.ready && item.blockers === 0);
|
||
if (grade !== 'all') reports = reports.filter(item => item.grade === grade);
|
||
reports.sort((a, b) => a.score - b.score || String(b.listing?.updated_at || b.listing?.created_at || '').localeCompare(String(a.listing?.updated_at || a.listing?.created_at || '')));
|
||
const safeSize = [5, 10, 15, 25].includes(Number(pageSize)) ? Number(pageSize) : 10;
|
||
const total = reports.length;
|
||
const totalPages = Math.max(1, Math.ceil(total / safeSize));
|
||
const safePage = clamp(Number(page) || 1, 1, totalPages);
|
||
const items = reports.slice((safePage - 1) * safeSize, safePage * safeSize);
|
||
const summary = {
|
||
total: allReports.length,
|
||
ready: allReports.filter(report => report.ready).length,
|
||
blocked: allReports.filter(report => report.blockers > 0).length,
|
||
average_score: allReports.length ? Math.round(allReports.reduce((sum, report) => sum + report.score, 0) / allReports.length) : 0,
|
||
};
|
||
return { items, pagination: { page: safePage, page_size: safeSize, total, total_pages: totalPages }, summary };
|
||
}
|
||
|
||
export function getQualityHistory(listingId, limit = 12) {
|
||
return db.prepare('SELECT id, score, grade, ready, blockers, warnings, recommendations, analyzed_at FROM quality_reports WHERE listing_id = ? ORDER BY id DESC LIMIT ?').all(listingId, Math.min(Math.max(Number(limit) || 12, 1), 50));
|
||
}
|
||
|
||
export function compareQualityReports(listingId) {
|
||
const rows = db.prepare('SELECT id, score, grade, analyzed_at FROM quality_reports WHERE listing_id = ? ORDER BY id DESC LIMIT 2').all(listingId);
|
||
return { latest: rows[0] || null, previous: rows[1] || null, delta: rows.length > 1 ? rows[0].score - rows[1].score : 0 };
|
||
}
|