Masterluke77andGitHub
6f48827f4d
* docs: prepare Vendoo 1.41.2 git-ignore boundary hotfix * fix: track native source modules with root-anchored runtime ignores
1553 lines
66 KiB
JavaScript
1553 lines
66 KiB
JavaScript
import Database from 'better-sqlite3';
|
|
import { readFileSync } from 'fs';
|
|
import { join } from 'path';
|
|
import { APP_ROOT, DB_PATH } from './runtime-paths.mjs';
|
|
|
|
const db = new Database(DB_PATH);
|
|
export { db };
|
|
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
const schema = readFileSync(join(APP_ROOT, 'db', 'schema.sql'), 'utf-8');
|
|
db.exec(schema);
|
|
|
|
const MIGRATIONS = [
|
|
['brand', 'TEXT'], ['category', 'TEXT'], ['size', 'TEXT'], ['color', 'TEXT'],
|
|
['condition', 'TEXT'], ['price', 'REAL'], ['status', "TEXT DEFAULT 'active'"], ['language', "TEXT DEFAULT 'de'"],
|
|
['sku', 'TEXT'], ['deleted_at', 'TEXT'], ['storage_location', 'TEXT'], ['description_html', 'TEXT'],
|
|
['created_by', 'INTEGER'], ['updated_by', 'INTEGER'],
|
|
];
|
|
|
|
for (const [col, type] of MIGRATIONS) {
|
|
try { db.exec(`ALTER TABLE listings ADD COLUMN ${col} ${type}`); } catch {}
|
|
}
|
|
try { db.exec(`CREATE TABLE IF NOT EXISTS templates (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, platform TEXT, seller_notes TEXT, language TEXT DEFAULT 'de', default_tags TEXT, created_at TEXT DEFAULT (datetime('now')))`); } catch {}
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS mobile_upload_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
user_id INTEGER,
|
|
listing_id INTEGER,
|
|
context TEXT NOT NULL DEFAULT 'generator',
|
|
files TEXT NOT NULL DEFAULT '[]',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_mobile_upload_token ON mobile_upload_sessions(token_hash)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_mobile_upload_expires ON mobile_upload_sessions(expires_at)`);
|
|
} catch {}
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS flux_images (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
prompt TEXT NOT NULL,
|
|
image_path TEXT NOT NULL UNIQUE,
|
|
width INTEGER NOT NULL DEFAULT 768,
|
|
height INTEGER NOT NULL DEFAULT 768,
|
|
seed INTEGER,
|
|
steps INTEGER NOT NULL DEFAULT 6,
|
|
style TEXT NOT NULL DEFAULT 'none',
|
|
edited_from INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_images_created ON flux_images(id DESC)`);
|
|
} catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN final_prompt TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN profile TEXT DEFAULT 'balanced'`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN duration_ms INTEGER`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN favorite INTEGER NOT NULL DEFAULT 0`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN job_id TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN variant_index INTEGER`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN prompt_mode TEXT DEFAULT 'precise'`); } catch {}
|
|
try { db.exec(`ALTER TABLE flux_images ADD COLUMN parameters_json TEXT`); } catch {}
|
|
try { db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_images_favorite ON flux_images(favorite, id DESC)`); } catch {}
|
|
try { db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_images_job ON flux_images(job_id, variant_index)`); } catch {}
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS image_edit_versions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
root_path TEXT NOT NULL,
|
|
source_path TEXT NOT NULL,
|
|
output_path TEXT NOT NULL UNIQUE,
|
|
context TEXT NOT NULL DEFAULT 'generator',
|
|
operations_json TEXT NOT NULL DEFAULT '{}',
|
|
width INTEGER,
|
|
height INTEGER,
|
|
format TEXT,
|
|
quality INTEGER,
|
|
file_size INTEGER,
|
|
user_id INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_image_edit_versions_root ON image_edit_versions(root_path, id DESC)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_image_edit_versions_output ON image_edit_versions(output_path)`);
|
|
} catch {}
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS flux_prompt_jobs (
|
|
id TEXT PRIMARY KEY,
|
|
prompt TEXT NOT NULL,
|
|
final_prompt TEXT NOT NULL,
|
|
width INTEGER NOT NULL,
|
|
height INTEGER NOT NULL,
|
|
steps INTEGER NOT NULL,
|
|
style TEXT NOT NULL DEFAULT 'none',
|
|
profile TEXT NOT NULL DEFAULT 'balanced',
|
|
prompt_mode TEXT NOT NULL DEFAULT 'precise',
|
|
assistant INTEGER NOT NULL DEFAULT 1,
|
|
variants INTEGER NOT NULL DEFAULT 1,
|
|
seed_base INTEGER NOT NULL,
|
|
seed_lock INTEGER NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
phase TEXT NOT NULL DEFAULT 'queued',
|
|
message TEXT,
|
|
error_message TEXT,
|
|
completed_variants INTEGER NOT NULL DEFAULT 0,
|
|
source_job_id TEXT,
|
|
settings_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
started_at TEXT,
|
|
completed_at TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE TABLE IF NOT EXISTS flux_prompt_job_variants (
|
|
id TEXT PRIMARY KEY,
|
|
job_id TEXT NOT NULL,
|
|
variant_index INTEGER NOT NULL,
|
|
seed INTEGER NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
phase TEXT NOT NULL DEFAULT 'queued',
|
|
message TEXT,
|
|
prompt_id TEXT,
|
|
image_id INTEGER,
|
|
error_message TEXT,
|
|
duration_ms INTEGER,
|
|
cancel_requested INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
started_at TEXT,
|
|
completed_at TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(job_id) REFERENCES flux_prompt_jobs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY(image_id) REFERENCES flux_images(id) ON DELETE SET NULL,
|
|
UNIQUE(job_id, variant_index)
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_jobs_status ON flux_prompt_jobs(status, created_at)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_job_variants_status ON flux_prompt_job_variants(status, created_at)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_job_variants_job ON flux_prompt_job_variants(job_id, variant_index)`);
|
|
} catch {}
|
|
|
|
try { db.exec(`ALTER TABLE flux_prompt_jobs ADD COLUMN archived_at TEXT`); } catch {}
|
|
try { db.exec(`CREATE INDEX IF NOT EXISTS idx_flux_jobs_archive ON flux_prompt_jobs(archived_at, created_at DESC)`); } catch {}
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS image_batch_profiles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
operations_json TEXT NOT NULL DEFAULT '{}',
|
|
filename_pattern TEXT NOT NULL DEFAULT '{name}-{profile}-{index}',
|
|
is_builtin INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE TABLE IF NOT EXISTS image_batch_jobs (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
phase TEXT NOT NULL DEFAULT 'queued',
|
|
source_count INTEGER NOT NULL DEFAULT 0,
|
|
profile_count INTEGER NOT NULL DEFAULT 0,
|
|
total_items INTEGER NOT NULL DEFAULT 0,
|
|
completed_items INTEGER NOT NULL DEFAULT 0,
|
|
failed_items INTEGER NOT NULL DEFAULT 0,
|
|
cancelled_items INTEGER NOT NULL DEFAULT 0,
|
|
pause_requested INTEGER NOT NULL DEFAULT 0,
|
|
cancel_requested INTEGER NOT NULL DEFAULT 0,
|
|
settings_json TEXT NOT NULL DEFAULT '{}',
|
|
error_message TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
started_at TEXT,
|
|
completed_at TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE TABLE IF NOT EXISTS image_batch_job_items (
|
|
id TEXT PRIMARY KEY,
|
|
job_id TEXT NOT NULL,
|
|
source_path TEXT NOT NULL,
|
|
profile_id INTEGER,
|
|
profile_key TEXT,
|
|
profile_name TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
phase TEXT NOT NULL DEFAULT 'queued',
|
|
settings_json TEXT NOT NULL DEFAULT '{}',
|
|
output_path TEXT,
|
|
error_message TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
started_at TEXT,
|
|
completed_at TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(job_id) REFERENCES image_batch_jobs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY(profile_id) REFERENCES image_batch_profiles(id) ON DELETE SET NULL
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_image_batch_jobs_status ON image_batch_jobs(status, created_at)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_image_batch_items_job ON image_batch_job_items(job_id, status, created_at)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_image_batch_items_output ON image_batch_job_items(output_path)`);
|
|
} catch {}
|
|
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS quality_reports (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
listing_id INTEGER NOT NULL,
|
|
score INTEGER NOT NULL DEFAULT 0,
|
|
grade TEXT NOT NULL DEFAULT 'E',
|
|
ready INTEGER NOT NULL DEFAULT 0,
|
|
blockers INTEGER NOT NULL DEFAULT 0,
|
|
warnings INTEGER NOT NULL DEFAULT 0,
|
|
recommendations INTEGER NOT NULL DEFAULT 0,
|
|
report_json TEXT NOT NULL DEFAULT '{}',
|
|
analyzed_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(listing_id) REFERENCES listings(id) ON DELETE CASCADE
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_quality_reports_listing ON quality_reports(listing_id, id DESC)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_quality_reports_score ON quality_reports(score, analyzed_at DESC)`);
|
|
} catch {}
|
|
|
|
const IMAGE_BATCH_BUILTINS = [
|
|
['ebay-main', 'eBay Hauptbild', 'Quadratisches, helles Hauptbild für eBay.', { resize: { width: 1600, height: 1600, fit: 'contain', background: '#ffffff' }, format: 'jpeg', quality: 92, sharpness: 18 }, '{name}-ebay-{index}'],
|
|
['vinted-portrait', 'Vinted Hochformat', 'Hochformat 4:5 für Vinted und mobile Feeds.', { resize: { width: 1350, height: 1688, fit: 'contain', background: '#ffffff' }, format: 'jpeg', quality: 90, sharpness: 14 }, '{name}-vinted-{index}'],
|
|
['kleinanzeigen', 'Kleinanzeigen', 'Großes, ausgewogenes Querformat.', { resize: { width: 1920, height: 1440, fit: 'contain', background: '#ffffff' }, format: 'jpeg', quality: 90, sharpness: 12 }, '{name}-kleinanzeigen-{index}'],
|
|
['etsy', 'Etsy Produktbild', 'Hochauflösendes 4:3-Produktbild.', { resize: { width: 2000, height: 1500, fit: 'contain', background: '#ffffff' }, format: 'jpeg', quality: 92, sharpness: 15 }, '{name}-etsy-{index}'],
|
|
['instagram-square', 'Instagram Quadrat', 'Quadratisches Social-Media-Format.', { resize: { width: 1080, height: 1080, fit: 'cover', background: '#ffffff' }, format: 'jpeg', quality: 90, sharpness: 12 }, '{name}-instagram-square-{index}'],
|
|
['instagram-portrait', 'Instagram Hochformat', '4:5-Format für Feed-Beiträge.', { resize: { width: 1080, height: 1350, fit: 'cover', background: '#ffffff' }, format: 'jpeg', quality: 90, sharpness: 12 }, '{name}-instagram-portrait-{index}'],
|
|
['webshop', 'Webshop WebP', 'Leichtes WebP für Shops und Webseiten.', { resize: { width: 1600, height: 1600, fit: 'contain', background: '#ffffff' }, format: 'webp', quality: 84, sharpness: 10 }, '{name}-webshop-{index}'],
|
|
['original-optimized', 'Original optimiert', 'Originalformat optimiert, ohne Vergrößerung.', { resize: { width: 2400, height: 2400, fit: 'inside', background: '#ffffff' }, format: 'jpeg', quality: 92, sharpness: 10 }, '{name}-optimiert-{index}'],
|
|
];
|
|
try {
|
|
const seedBatchProfile = db.prepare(`INSERT OR IGNORE INTO image_batch_profiles
|
|
(key, name, description, operations_json, filename_pattern, is_builtin) VALUES (?, ?, ?, ?, ?, 1)`);
|
|
for (const [key, name, description, operations, pattern] of IMAGE_BATCH_BUILTINS) {
|
|
seedBatchProfile.run(key, name, description, JSON.stringify(operations), pattern);
|
|
}
|
|
} catch {}
|
|
|
|
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS ai_model_generations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
listing_id INTEGER,
|
|
context TEXT NOT NULL DEFAULT 'generator',
|
|
preset TEXT NOT NULL,
|
|
variants INTEGER NOT NULL DEFAULT 3,
|
|
source_photos TEXT NOT NULL DEFAULT '[]',
|
|
generated_photos TEXT NOT NULL DEFAULT '[]',
|
|
prompt_summary TEXT,
|
|
provider TEXT,
|
|
provider_model TEXT,
|
|
moderation_notes TEXT,
|
|
safety_status TEXT NOT NULL DEFAULT 'pending',
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT
|
|
)`);
|
|
db.exec(`CREATE TABLE IF NOT EXISTS ai_model_photos (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
generation_id INTEGER NOT NULL,
|
|
listing_id INTEGER,
|
|
variant_index INTEGER NOT NULL DEFAULT 1,
|
|
preset TEXT NOT NULL,
|
|
image_path TEXT NOT NULL,
|
|
source_photo TEXT,
|
|
prompt TEXT,
|
|
provider TEXT,
|
|
provider_model TEXT,
|
|
moderation_result TEXT,
|
|
safety_status TEXT NOT NULL DEFAULT 'pending',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_ai_model_generations_listing ON ai_model_generations(listing_id)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_ai_model_photos_listing ON ai_model_photos(listing_id)`);
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS ai_model_jobs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
listing_id INTEGER,
|
|
context TEXT NOT NULL DEFAULT 'generator',
|
|
mode TEXT NOT NULL DEFAULT 'flatlay',
|
|
preset TEXT NOT NULL DEFAULT 'mixed',
|
|
variants INTEGER NOT NULL DEFAULT 1,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
progress INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT,
|
|
payload_json TEXT,
|
|
generation_id INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_ai_model_jobs_status ON ai_model_jobs(status)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_ai_model_jobs_listing ON ai_model_jobs(listing_id)`);
|
|
} catch {}
|
|
|
|
|
|
|
|
try { db.exec(`ALTER TABLE ai_model_jobs ADD COLUMN progress_message TEXT`); } catch {}
|
|
|
|
try { db.exec(`ALTER TABLE ai_model_generations ADD COLUMN provider TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE ai_model_generations ADD COLUMN provider_model TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE ai_model_photos ADD COLUMN provider TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE ai_model_photos ADD COLUMN provider_model TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE ai_model_generations ADD COLUMN mode TEXT`); } catch {}
|
|
try { db.exec(`ALTER TABLE ai_model_photos ADD COLUMN mode TEXT`); } catch {}
|
|
|
|
const defaultSettingStmt = db.prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)');
|
|
[
|
|
['ai_model_photos_enabled', '1'],
|
|
['ai_model_photo_default_variants', '1'],
|
|
['ai_model_photo_default_preset', 'mixed'],
|
|
['ai_model_photo_block_underwear', '1'],
|
|
['ai_model_photo_block_kids', '1'],
|
|
['ai_model_photo_provider', 'auto'],
|
|
['ai_model_photo_openrouter_free_only', '1'],
|
|
['ai_model_photo_openrouter_model', ''],
|
|
['ai_model_photo_openai_model', 'gpt-image-1'],
|
|
['ai_model_photo_default_mode', 'flatlay'],
|
|
['ai_model_photo_local_enabled', '1'],
|
|
['ai_model_photo_local_url', 'http://127.0.0.1:8188'],
|
|
['ai_model_photo_local_workflow_path', 'local-ai/workflows/vendoo_flux_schnell_api.json'],
|
|
['ai_model_photo_local_install_path', 'local-ai/comfyui'],
|
|
['ai_model_photo_local_require_token', '1'],
|
|
['ai_model_photo_use_source_reference', '1'],
|
|
['ai_model_photo_reference_strength', '70'],
|
|
['ai_model_photo_local_resolution', '768'],
|
|
['ai_model_photo_preserve_logos', '1'],
|
|
['flux_runtime_profile', 'auto'],
|
|
['flux_default_profile', 'balanced'],
|
|
['flux_prompt_assistant', '1'],
|
|
['flux_default_style', 'photorealistic'],
|
|
['flux_default_format', '768x768'],
|
|
['flux_default_variants', '1'],
|
|
['flux_seed_lock', '0'],
|
|
['flux_max_parallel_jobs', '1'],
|
|
].forEach(entry => { try { defaultSettingStmt.run(...entry); } catch {} });
|
|
|
|
// Slice 28: entfernt die aufgegebene lokale Try-on-Architektur vollständig aus bestehenden Datenbanken.
|
|
try { db.exec('DROP TABLE IF EXISTS vto_models'); } catch {}
|
|
try { db.prepare("DELETE FROM settings WHERE key LIKE 'ai_vto_%' OR key IN ('ai_model_photo_local_tryon_workflow_path','bfl_api_key')").run(); } catch {}
|
|
try { db.prepare("UPDATE settings SET value = 'flatlay' WHERE key = 'ai_model_photo_default_mode' AND value IN ('virtual_tryon','model')").run(); } catch {}
|
|
|
|
// Backfill SKUs for existing listings
|
|
const noSku = db.prepare("SELECT id FROM listings WHERE sku IS NULL ORDER BY id").all();
|
|
if (noSku.length) {
|
|
const upd = db.prepare("UPDATE listings SET sku = ? WHERE id = ?");
|
|
for (const row of noSku) {
|
|
upd.run(`VD-${String(row.id).padStart(4, '0')}`, row.id);
|
|
}
|
|
}
|
|
|
|
function parseJsonArray(value) {
|
|
if (Array.isArray(value)) return value;
|
|
if (value === null || value === undefined || value === '') return [];
|
|
try {
|
|
const parsed = JSON.parse(value);
|
|
return Array.isArray(parsed) ? parsed : [];
|
|
} catch {
|
|
return String(value).split(',').map(entry => entry.trim()).filter(Boolean);
|
|
}
|
|
}
|
|
|
|
function parseRow(row) {
|
|
if (!row) return null;
|
|
row.tags = parseJsonArray(row.tags);
|
|
row.photos = parseJsonArray(row.photos);
|
|
return row;
|
|
}
|
|
|
|
function generateSku() {
|
|
const row = db.prepare("SELECT MAX(CAST(SUBSTR(sku, 4) AS INTEGER)) as maxNum FROM listings WHERE sku LIKE 'VD-%'").get();
|
|
const next = (row?.maxNum || 0) + 1;
|
|
return `VD-${String(next).padStart(4, '0')}`;
|
|
}
|
|
|
|
export function createListing(data) {
|
|
const sku = data.sku || generateSku();
|
|
const stmt = db.prepare(`
|
|
INSERT INTO listings (platform, ai_provider, title, description, description_html, tags, photos, seller_notes, brand, category, size, color, condition, price, status, language, sku, storage_location, created_by, updated_by)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
const result = stmt.run(
|
|
data.platform, data.ai_provider, data.title, data.description,
|
|
data.description_html || null,
|
|
JSON.stringify(Array.isArray(data.tags) ? data.tags : []),
|
|
JSON.stringify(Array.isArray(data.photos) ? data.photos : []),
|
|
data.seller_notes || null,
|
|
data.brand || null, data.category || null, data.size || null, data.color || null,
|
|
data.condition || null, data.price || null, data.status || 'active', data.language || 'de', sku,
|
|
data.storage_location || null, data.created_by || null, data.updated_by || data.created_by || null
|
|
);
|
|
return getListing(result.lastInsertRowid);
|
|
}
|
|
|
|
export function getListing(id) {
|
|
return parseRow(db.prepare('SELECT * FROM listings WHERE id = ?').get(id));
|
|
}
|
|
|
|
export function getListings(query, platform, status) {
|
|
let sql = 'SELECT * FROM listings WHERE deleted_at IS NULL';
|
|
const params = [];
|
|
if (query) { sql += ' AND (title LIKE ? OR description LIKE ? OR brand LIKE ?)'; const like = `%${query}%`; params.push(like, like, like); }
|
|
if (platform) { sql += ' AND platform = ?'; params.push(platform); }
|
|
if (status) { sql += ' AND status = ?'; params.push(status); }
|
|
sql += ' ORDER BY created_at DESC';
|
|
return db.prepare(sql).all(...params).map(parseRow);
|
|
}
|
|
|
|
const LISTING_UPDATE_FIELDS = new Set([
|
|
'platform', 'ai_provider', 'title', 'description', 'description_html', 'tags', 'photos',
|
|
'seller_notes', 'brand', 'category', 'size', 'color', 'condition', 'price', 'status',
|
|
'language', 'sku', 'storage_location', 'updated_by',
|
|
]);
|
|
|
|
export function updateListing(id, data) {
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, val] of Object.entries(data || {})) {
|
|
if (!LISTING_UPDATE_FIELDS.has(key) || val === undefined) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(['tags', 'photos'].includes(key) ? JSON.stringify(Array.isArray(val) ? val : []) : val);
|
|
}
|
|
if (!fields.length) return getListing(id);
|
|
fields.push("updated_at = datetime('now')");
|
|
values.push(id);
|
|
db.prepare(`UPDATE listings SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
return getListing(id);
|
|
}
|
|
|
|
export function deleteListing(id) {
|
|
return softDeleteListing(id);
|
|
}
|
|
|
|
export function softDeleteListing(id) {
|
|
return db.prepare("UPDATE listings SET deleted_at = datetime('now') WHERE id = ?").run(id);
|
|
}
|
|
|
|
export function restoreListing(id) {
|
|
return db.prepare("UPDATE listings SET deleted_at = NULL WHERE id = ?").run(id);
|
|
}
|
|
|
|
export function getDeletedListings() {
|
|
return db.prepare("SELECT * FROM listings WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC").all().map(parseRow);
|
|
}
|
|
|
|
export function permanentlyDeleteListing(id) {
|
|
return db.prepare('DELETE FROM listings WHERE id = ?').run(id);
|
|
}
|
|
|
|
export function cleanupTrash(days = 30) {
|
|
return db.prepare("DELETE FROM listings WHERE deleted_at IS NOT NULL AND deleted_at < datetime('now', '-' || ? || ' days')").run(days);
|
|
}
|
|
|
|
export function getTrashCount() {
|
|
const row = db.prepare("SELECT COUNT(*) as count FROM listings WHERE deleted_at IS NOT NULL").get();
|
|
return row?.count || 0;
|
|
}
|
|
|
|
export function duplicateListing(id) {
|
|
const original = getListing(id);
|
|
if (!original) return null;
|
|
const data = {
|
|
platform: original.platform,
|
|
ai_provider: original.ai_provider,
|
|
title: (original.title || '') + ' (Kopie)',
|
|
description: original.description,
|
|
description_html: original.description_html,
|
|
tags: original.tags,
|
|
photos: original.photos,
|
|
seller_notes: original.seller_notes,
|
|
brand: original.brand,
|
|
category: original.category,
|
|
size: original.size,
|
|
color: original.color,
|
|
condition: original.condition,
|
|
price: original.price,
|
|
status: 'active',
|
|
language: original.language,
|
|
storage_location: original.storage_location,
|
|
};
|
|
return createListing(data);
|
|
}
|
|
|
|
|
|
function parseAiModelGeneration(row) {
|
|
if (!row) return null;
|
|
try { row.source_photos = JSON.parse(row.source_photos || '[]'); } catch { row.source_photos = []; }
|
|
try { row.generated_photos = JSON.parse(row.generated_photos || '[]'); } catch { row.generated_photos = []; }
|
|
return row;
|
|
}
|
|
|
|
function parseAiModelPhoto(row) {
|
|
if (!row) return null;
|
|
return row;
|
|
}
|
|
|
|
export function createAiModelGeneration(data) {
|
|
const stmt = db.prepare(`
|
|
INSERT INTO ai_model_generations (listing_id, context, mode, preset, variants, source_photos, generated_photos, prompt_summary, provider, provider_model, moderation_notes, safety_status, status)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
const result = stmt.run(
|
|
data.listing_id || null,
|
|
data.context || 'generator',
|
|
data.mode || 'model',
|
|
data.preset || 'mixed',
|
|
data.variants || 3,
|
|
JSON.stringify(Array.isArray(data.source_photos) ? data.source_photos : []),
|
|
JSON.stringify(Array.isArray(data.generated_photos) ? data.generated_photos : []),
|
|
data.prompt_summary || null,
|
|
data.provider || null,
|
|
data.provider_model || null,
|
|
data.moderation_notes || null,
|
|
data.safety_status || 'pending',
|
|
data.status || 'pending'
|
|
);
|
|
return getAiModelGeneration(result.lastInsertRowid);
|
|
}
|
|
|
|
export function updateAiModelGeneration(id, data = {}) {
|
|
const allowed = new Set(['listing_id', 'context', 'mode', 'preset', 'variants', 'source_photos', 'generated_photos', 'prompt_summary', 'provider', 'provider_model', 'moderation_notes', 'safety_status', 'status']);
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, value] of Object.entries(data)) {
|
|
if (!allowed.has(key) || value === undefined) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(['source_photos', 'generated_photos'].includes(key) ? JSON.stringify(Array.isArray(value) ? value : []) : value);
|
|
}
|
|
if (!fields.length) return getAiModelGeneration(id);
|
|
fields.push("updated_at = datetime('now')");
|
|
values.push(id);
|
|
db.prepare(`UPDATE ai_model_generations SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
return getAiModelGeneration(id);
|
|
}
|
|
|
|
export function getAiModelGeneration(id) {
|
|
return parseAiModelGeneration(db.prepare('SELECT * FROM ai_model_generations WHERE id = ?').get(id));
|
|
}
|
|
|
|
export function createAiModelPhoto(data) {
|
|
const stmt = db.prepare(`
|
|
INSERT INTO ai_model_photos (generation_id, listing_id, variant_index, mode, preset, image_path, source_photo, prompt, provider, provider_model, moderation_result, safety_status)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
const result = stmt.run(
|
|
data.generation_id,
|
|
data.listing_id || null,
|
|
data.variant_index || 1,
|
|
data.mode || 'model',
|
|
data.preset || 'mixed',
|
|
data.image_path,
|
|
data.source_photo || null,
|
|
data.prompt || null,
|
|
data.provider || null,
|
|
data.provider_model || null,
|
|
data.moderation_result || null,
|
|
data.safety_status || 'pending'
|
|
);
|
|
return getAiModelPhoto(result.lastInsertRowid);
|
|
}
|
|
|
|
export function getAiModelPhoto(id) {
|
|
return parseAiModelPhoto(db.prepare('SELECT * FROM ai_model_photos WHERE id = ?').get(id));
|
|
}
|
|
|
|
export function getAiModelPhotosForListing(listingId) {
|
|
return db.prepare('SELECT * FROM ai_model_photos WHERE listing_id = ? ORDER BY created_at DESC, variant_index ASC').all(listingId).map(parseAiModelPhoto);
|
|
}
|
|
|
|
export function getAiModelPhotosForGeneration(generationId) {
|
|
return db.prepare('SELECT * FROM ai_model_photos WHERE generation_id = ? ORDER BY variant_index ASC').all(generationId).map(parseAiModelPhoto);
|
|
}
|
|
|
|
export function getAiModelGenerationsForListing(listingId) {
|
|
return db.prepare("SELECT * FROM ai_model_generations WHERE listing_id = ? ORDER BY created_at DESC, id DESC").all(listingId).map(parseAiModelGeneration);
|
|
}
|
|
|
|
export function createAiModelJob(data) {
|
|
const stmt = db.prepare(`
|
|
INSERT INTO ai_model_jobs (listing_id, context, mode, preset, variants, status, progress, progress_message, error_message, payload_json, generation_id)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
const result = stmt.run(
|
|
data.listing_id || null,
|
|
data.context || 'generator',
|
|
data.mode || 'model',
|
|
data.preset || 'mixed',
|
|
data.variants || 1,
|
|
data.status || 'queued',
|
|
data.progress ?? 0,
|
|
data.progress_message || null,
|
|
data.error_message || null,
|
|
JSON.stringify(data.payload_json || {}),
|
|
data.generation_id || null
|
|
);
|
|
return getAiModelJob(result.lastInsertRowid);
|
|
}
|
|
|
|
export function getAiModelJob(id) {
|
|
const row = db.prepare('SELECT * FROM ai_model_jobs WHERE id = ?').get(id);
|
|
if (!row) return null;
|
|
try { row.payload_json = JSON.parse(row.payload_json || '{}'); } catch { row.payload_json = {}; }
|
|
return row;
|
|
}
|
|
|
|
export function updateAiModelJob(id, data = {}) {
|
|
const allowed = new Set(['listing_id','context','mode','preset','variants','status','progress','progress_message','error_message','payload_json','generation_id']);
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, value] of Object.entries(data)) {
|
|
if (!allowed.has(key) || value === undefined) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(key === 'payload_json' ? JSON.stringify(value || {}) : value);
|
|
}
|
|
if (!fields.length) return getAiModelJob(id);
|
|
fields.push("updated_at = datetime('now')");
|
|
values.push(id);
|
|
db.prepare(`UPDATE ai_model_jobs SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
return getAiModelJob(id);
|
|
}
|
|
|
|
export function getPendingAiModelJobs(limit = 10) {
|
|
return db.prepare("SELECT * FROM ai_model_jobs WHERE status IN ('queued','retry') ORDER BY id ASC LIMIT ?").all(limit).map(row => { try { row.payload_json = JSON.parse(row.payload_json || '{}'); } catch { row.payload_json = {}; } return row; });
|
|
}
|
|
|
|
export function getSettings() {
|
|
const rows = db.prepare('SELECT key, value FROM settings').all();
|
|
const settings = {};
|
|
for (const row of rows) settings[row.key] = row.value;
|
|
return settings;
|
|
}
|
|
|
|
export function updateSettings(settings) {
|
|
const stmt = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
|
|
const transaction = db.transaction((entries) => {
|
|
for (const [key, value] of entries) stmt.run(key, value);
|
|
});
|
|
transaction(Object.entries(settings));
|
|
return getSettings();
|
|
}
|
|
|
|
function parseFluxImage(row) {
|
|
if (!row) return null;
|
|
try { row.parameters = JSON.parse(row.parameters_json || '{}'); } catch { row.parameters = {}; }
|
|
row.favorite = Number(row.favorite || 0) === 1;
|
|
return row;
|
|
}
|
|
|
|
export function createFluxImage(data = {}) {
|
|
const prompt = String(data.prompt || '').trim().slice(0, 4000);
|
|
const imagePath = String(data.image_path || '').trim();
|
|
if (!prompt || !imagePath) throw new Error('Prompt und Bildpfad sind erforderlich.');
|
|
const existing = parseFluxImage(db.prepare('SELECT * FROM flux_images WHERE image_path = ?').get(imagePath));
|
|
if (existing) return existing;
|
|
const result = db.prepare(`INSERT INTO flux_images
|
|
(prompt, image_path, width, height, seed, steps, style, edited_from, final_prompt, profile, duration_ms, favorite, job_id, variant_index, prompt_mode, parameters_json)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).run(
|
|
prompt,
|
|
imagePath,
|
|
Math.max(256, Number(data.width) || 768),
|
|
Math.max(256, Number(data.height) || 768),
|
|
Number.isFinite(Number(data.seed)) ? Number(data.seed) : null,
|
|
Math.max(1, Math.min(50, Number(data.steps) || 6)),
|
|
String(data.style || 'none').slice(0, 40),
|
|
Number.isFinite(Number(data.edited_from)) ? Number(data.edited_from) : null,
|
|
String(data.final_prompt || '').trim().slice(0, 5000) || null,
|
|
String(data.profile || 'balanced').slice(0, 40),
|
|
Number.isFinite(Number(data.duration_ms)) ? Math.max(0, Math.round(Number(data.duration_ms))) : null,
|
|
data.favorite === true || Number(data.favorite) === 1 ? 1 : 0,
|
|
String(data.job_id || '').trim().slice(0, 80) || null,
|
|
Number.isFinite(Number(data.variant_index)) ? Math.max(1, Math.round(Number(data.variant_index))) : null,
|
|
String(data.prompt_mode || 'precise').slice(0, 40),
|
|
JSON.stringify(data.parameters && typeof data.parameters === 'object' ? data.parameters : {}),
|
|
);
|
|
return parseFluxImage(db.prepare('SELECT * FROM flux_images WHERE id = ?').get(result.lastInsertRowid));
|
|
}
|
|
|
|
export function getFluxImages(limit = 40, { favoritesOnly = false } = {}) {
|
|
const safeLimit = Math.max(1, Math.min(200, Number(limit) || 40));
|
|
const rows = favoritesOnly
|
|
? db.prepare('SELECT * FROM flux_images WHERE favorite = 1 ORDER BY id DESC LIMIT ?').all(safeLimit)
|
|
: db.prepare('SELECT * FROM flux_images ORDER BY id DESC LIMIT ?').all(safeLimit);
|
|
return rows.map(parseFluxImage);
|
|
}
|
|
|
|
export function paginateFluxImages({ page = 1, pageSize = 5, favoritesOnly = false } = {}) {
|
|
const safePageSize = Math.max(5, Math.min(15, Number(pageSize) || 5));
|
|
const requestedPage = Math.max(1, Number(page) || 1);
|
|
const where = favoritesOnly ? 'WHERE favorite = 1' : '';
|
|
const total = Number(db.prepare(`SELECT COUNT(*) AS count FROM flux_images ${where}`).get()?.count || 0);
|
|
const totalPages = Math.max(1, Math.ceil(total / safePageSize));
|
|
const safePage = Math.min(requestedPage, totalPages);
|
|
const rows = db.prepare(`SELECT * FROM flux_images ${where} ORDER BY id DESC LIMIT ? OFFSET ?`)
|
|
.all(safePageSize, (safePage - 1) * safePageSize);
|
|
return {
|
|
items: rows.map(parseFluxImage),
|
|
pagination: { page: safePage, page_size: safePageSize, total, total_pages: totalPages },
|
|
};
|
|
}
|
|
|
|
export function getFluxImage(id) {
|
|
return parseFluxImage(db.prepare('SELECT * FROM flux_images WHERE id = ?').get(Number(id)));
|
|
}
|
|
|
|
export function setFluxImageFavorite(id, favorite = true) {
|
|
db.prepare('UPDATE flux_images SET favorite = ? WHERE id = ?').run(favorite ? 1 : 0, Number(id));
|
|
return getFluxImage(id);
|
|
}
|
|
|
|
export function deleteFluxImage(id) {
|
|
const image = getFluxImage(id);
|
|
if (!image) return null;
|
|
db.prepare('DELETE FROM flux_images WHERE id = ?').run(Number(id));
|
|
return image;
|
|
}
|
|
|
|
|
|
function normalizeMediaPath(value) {
|
|
return String(value || '').replace(/\\/g, '/').replace(/^\/+/, '').trim();
|
|
}
|
|
|
|
export function listMediaLibraryItems({ source = 'all', search = '', favoritesOnly = false, sort = 'newest' } = {}) {
|
|
const map = new Map();
|
|
const add = (raw = {}) => {
|
|
const imagePath = normalizeMediaPath(raw.image_path);
|
|
if (!imagePath) return;
|
|
const existing = map.get(imagePath);
|
|
const sources = new Set([...(existing?.sources || []), ...(raw.sources || []), raw.source].filter(Boolean));
|
|
const priority = { batch: 5, flux: 4, edited: 3, ai: 2, listing: 1 };
|
|
const shouldReplace = !existing || (priority[raw.source] || 0) > (priority[existing.source] || 0);
|
|
const base = shouldReplace ? { ...existing, ...raw } : { ...raw, ...existing };
|
|
base.image_path = imagePath;
|
|
base.sources = [...sources];
|
|
base.favorite = Boolean(existing?.favorite || raw.favorite);
|
|
base.created_at = [existing?.created_at, raw.created_at].filter(Boolean).sort().reverse()[0] || null;
|
|
map.set(imagePath, base);
|
|
};
|
|
|
|
for (const row of db.prepare('SELECT * FROM flux_images ORDER BY id DESC').all()) {
|
|
add({ id: `flux:${row.id}`, source: 'flux', sources: ['flux'], flux_id: row.id, image_path: row.image_path,
|
|
title: row.prompt || 'FLUX-Bild', prompt: row.prompt || '', final_prompt: row.final_prompt || '', favorite: Number(row.favorite || 0) === 1,
|
|
width: row.width, height: row.height, seed: row.seed, job_id: row.job_id, variant_index: row.variant_index, created_at: row.created_at });
|
|
}
|
|
for (const row of db.prepare('SELECT * FROM image_edit_versions ORDER BY id DESC').all()) {
|
|
const isBatch = String(row.context || '') === 'batch';
|
|
add({ id: `${isBatch ? 'batch' : 'edited'}:${row.id}`, source: isBatch ? 'batch' : 'edited', sources: [isBatch ? 'batch' : 'edited'], edit_id: row.id, image_path: row.output_path,
|
|
title: isBatch ? 'Bildproduktion' : 'Bearbeitete Bildversion', root_path: row.root_path, width: row.width, height: row.height, format: row.format, created_at: row.created_at });
|
|
}
|
|
for (const row of db.prepare('SELECT * FROM ai_model_photos ORDER BY id DESC').all()) {
|
|
add({ id: `ai:${row.id}`, source: 'ai', sources: ['ai'], ai_id: row.id, image_path: row.image_path,
|
|
title: row.prompt || `AI-Bild ${row.variant_index || ''}`.trim(), prompt: row.prompt || '', listing_id: row.listing_id,
|
|
provider: row.provider_model || row.provider || '', preset: row.preset || '', created_at: row.created_at });
|
|
}
|
|
for (const row of db.prepare("SELECT id, title, photos, created_at FROM listings WHERE deleted_at IS NULL ORDER BY id DESC").all()) {
|
|
for (const imagePath of parseJsonArray(row.photos)) {
|
|
add({ id: `listing:${row.id}:${normalizeMediaPath(imagePath)}`, source: 'listing', sources: ['listing'], listing_id: row.id,
|
|
image_path: imagePath, title: row.title || `Listing ${row.id}`, created_at: row.created_at });
|
|
}
|
|
}
|
|
|
|
const needle = String(search || '').trim().toLowerCase();
|
|
const selectedSource = String(source || 'all').toLowerCase();
|
|
let items = [...map.values()].filter(item => {
|
|
if (selectedSource !== 'all' && !item.sources.includes(selectedSource)) return false;
|
|
if (favoritesOnly && !item.favorite) return false;
|
|
if (!needle) return true;
|
|
return [item.title, item.prompt, item.final_prompt, item.image_path, item.provider, item.preset]
|
|
.some(value => String(value || '').toLowerCase().includes(needle));
|
|
});
|
|
if (sort === 'oldest') items.sort((a, b) => String(a.created_at || '').localeCompare(String(b.created_at || '')));
|
|
else if (sort === 'name') items.sort((a, b) => String(a.title || a.image_path).localeCompare(String(b.title || b.image_path), 'de'));
|
|
else items.sort((a, b) => String(b.created_at || '').localeCompare(String(a.created_at || '')));
|
|
return items;
|
|
}
|
|
|
|
export function setMediaLibraryFavorite(imagePath, favorite = true) {
|
|
const normalized = normalizeMediaPath(imagePath);
|
|
const result = db.prepare('UPDATE flux_images SET favorite = ? WHERE image_path = ?').run(favorite ? 1 : 0, normalized);
|
|
return { ok: result.changes > 0, image_path: normalized, favorite: Boolean(favorite) };
|
|
}
|
|
|
|
export function getListingReferencedMediaPaths() {
|
|
const referenced = new Set();
|
|
for (const row of db.prepare("SELECT photos FROM listings WHERE deleted_at IS NULL").all()) {
|
|
for (const imagePath of parseJsonArray(row.photos)) referenced.add(normalizeMediaPath(imagePath));
|
|
}
|
|
return referenced;
|
|
}
|
|
|
|
export function removeMediaLibraryRecords(imagePaths = []) {
|
|
const normalized = [...new Set((imagePaths || []).map(normalizeMediaPath).filter(Boolean))];
|
|
const tx = db.transaction(() => {
|
|
for (const imagePath of normalized) {
|
|
db.prepare('DELETE FROM flux_images WHERE image_path = ?').run(imagePath);
|
|
db.prepare('DELETE FROM image_edit_versions WHERE output_path = ?').run(imagePath);
|
|
db.prepare('DELETE FROM ai_model_photos WHERE image_path = ?').run(imagePath);
|
|
}
|
|
});
|
|
tx();
|
|
return normalized;
|
|
}
|
|
|
|
function parseImageEditVersion(row) {
|
|
if (!row) return null;
|
|
try { row.operations = JSON.parse(row.operations_json || '{}'); } catch { row.operations = {}; }
|
|
return row;
|
|
}
|
|
|
|
export function getImageEditRoot(path) {
|
|
const normalized = String(path || '').replace(/\\/g, '/').replace(/^\/+/, '').trim();
|
|
if (!normalized) return null;
|
|
const row = db.prepare('SELECT root_path FROM image_edit_versions WHERE output_path = ? ORDER BY id DESC LIMIT 1').get(normalized);
|
|
return row?.root_path || null;
|
|
}
|
|
|
|
export function createImageEditVersion(data = {}) {
|
|
const rootPath = String(data.root_path || '').replace(/\\/g, '/').replace(/^\/+/, '').trim();
|
|
const sourcePath = String(data.source_path || '').replace(/\\/g, '/').replace(/^\/+/, '').trim();
|
|
const outputPath = String(data.output_path || '').replace(/\\/g, '/').replace(/^\/+/, '').trim();
|
|
if (!rootPath || !sourcePath || !outputPath) throw new Error('Bildversion benötigt Original-, Quell- und Zielpfad.');
|
|
const result = db.prepare(`INSERT INTO image_edit_versions
|
|
(root_path, source_path, output_path, context, operations_json, width, height, format, quality, file_size, user_id)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).run(
|
|
rootPath,
|
|
sourcePath,
|
|
outputPath,
|
|
String(data.context || 'generator').slice(0, 40),
|
|
JSON.stringify(data.operations && typeof data.operations === 'object' ? data.operations : {}),
|
|
Number.isFinite(Number(data.width)) ? Math.round(Number(data.width)) : null,
|
|
Number.isFinite(Number(data.height)) ? Math.round(Number(data.height)) : null,
|
|
String(data.format || '').slice(0, 20) || null,
|
|
Number.isFinite(Number(data.quality)) ? Math.round(Number(data.quality)) : null,
|
|
Number.isFinite(Number(data.file_size)) ? Math.round(Number(data.file_size)) : null,
|
|
Number.isFinite(Number(data.user_id)) ? Number(data.user_id) : null,
|
|
);
|
|
return parseImageEditVersion(db.prepare('SELECT * FROM image_edit_versions WHERE id = ?').get(result.lastInsertRowid));
|
|
}
|
|
|
|
export function getImageEditVersions(path, limit = 50) {
|
|
const normalized = String(path || '').replace(/\\/g, '/').replace(/^\/+/, '').trim();
|
|
if (!normalized) return { root_path: null, versions: [] };
|
|
const rootPath = getImageEditRoot(normalized) || normalized;
|
|
const safeLimit = Math.max(1, Math.min(200, Number(limit) || 50));
|
|
const rows = db.prepare('SELECT * FROM image_edit_versions WHERE root_path = ? ORDER BY id DESC LIMIT ?').all(rootPath, safeLimit).map(parseImageEditVersion);
|
|
return { root_path: rootPath, versions: rows };
|
|
}
|
|
|
|
|
|
export function createMobileUploadSession(data) {
|
|
const stmt = db.prepare(`
|
|
INSERT INTO mobile_upload_sessions (token_hash, user_id, listing_id, context, files, status, expires_at)
|
|
VALUES (?, ?, ?, ?, '[]', 'active', ?)
|
|
`);
|
|
const result = stmt.run(
|
|
data.token_hash,
|
|
data.user_id || null,
|
|
data.listing_id || null,
|
|
data.context || 'generator',
|
|
data.expires_at
|
|
);
|
|
return getMobileUploadSessionById(result.lastInsertRowid, data.user_id || null);
|
|
}
|
|
|
|
function parseMobileUploadSession(row) {
|
|
if (!row) return null;
|
|
try { row.files = JSON.parse(row.files || '[]'); } catch { row.files = []; }
|
|
return row;
|
|
}
|
|
|
|
export function getMobileUploadSessionById(id, userId = null) {
|
|
const row = userId
|
|
? db.prepare('SELECT * FROM mobile_upload_sessions WHERE id = ? AND (user_id = ? OR user_id IS NULL)').get(id, userId)
|
|
: db.prepare('SELECT * FROM mobile_upload_sessions WHERE id = ?').get(id);
|
|
return parseMobileUploadSession(row);
|
|
}
|
|
|
|
export function getMobileUploadSessionByTokenHash(tokenHash) {
|
|
return parseMobileUploadSession(db.prepare('SELECT * FROM mobile_upload_sessions WHERE token_hash = ?').get(tokenHash));
|
|
}
|
|
|
|
export function appendMobileUploadFiles(id, filenames) {
|
|
const current = getMobileUploadSessionById(id);
|
|
if (!current) return null;
|
|
const merged = [...new Set([...(current.files || []), ...(filenames || [])])].slice(0, 10);
|
|
db.prepare("UPDATE mobile_upload_sessions SET files = ?, updated_at = datetime('now') WHERE id = ?")
|
|
.run(JSON.stringify(merged), id);
|
|
return getMobileUploadSessionById(id);
|
|
}
|
|
|
|
export function updateMobileUploadSession(id, patch = {}) {
|
|
const allowed = new Set(['status', 'listing_id']);
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, value] of Object.entries(patch)) {
|
|
if (!allowed.has(key)) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(value);
|
|
}
|
|
if (!fields.length) return getMobileUploadSessionById(id);
|
|
fields.push("updated_at = datetime('now')");
|
|
values.push(id);
|
|
db.prepare(`UPDATE mobile_upload_sessions SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
return getMobileUploadSessionById(id);
|
|
}
|
|
|
|
export function deleteMobileUploadSession(id, userId = null) {
|
|
if (userId) return db.prepare('DELETE FROM mobile_upload_sessions WHERE id = ? AND (user_id = ? OR user_id IS NULL)').run(id, userId);
|
|
return db.prepare('DELETE FROM mobile_upload_sessions WHERE id = ?').run(id);
|
|
}
|
|
|
|
export function cleanupMobileUploadSessions() {
|
|
return db.prepare("DELETE FROM mobile_upload_sessions WHERE expires_at < datetime('now') OR status = 'closed'").run();
|
|
}
|
|
|
|
// --- Seed example templates ---
|
|
const tplCount = db.prepare("SELECT COUNT(*) as c FROM templates").get();
|
|
if (tplCount.c === 0) {
|
|
const seedTpl = db.prepare('INSERT INTO templates (name, platform, seller_notes, language, default_tags) VALUES (?, ?, ?, ?, ?)');
|
|
seedTpl.run('Sneaker & Schuhe', 'vinted',
|
|
'Originalverpackung vorhanden. Versand mit DHL, gut gepolstert. Tierfreier Nichtraucherhaushalt. Bei Fragen gerne melden!',
|
|
'de', JSON.stringify(['#sneaker', '#schuhe', '#nike', '#adidas', '#secondhand']));
|
|
seedTpl.run('Vintage Kleidung', 'vinted',
|
|
'Vintage-Stück in gepflegtem Zustand. Maße siehe Beschreibung. Versand innerhalb von 1-2 Werktagen. Privatverkauf, keine Rücknahme.',
|
|
'de', JSON.stringify(['#vintage', '#retro', '#secondhand', '#fashion', '#preloved']));
|
|
seedTpl.run('Elektronik & Zubehör', 'ebay-de',
|
|
'Funktioniert einwandfrei, wurde getestet. Originalzubehör dabei, sofern nicht anders angegeben. Versand als versichertes Paket.',
|
|
'de', JSON.stringify(['Elektronik', 'Technik', 'Zubehör', 'gebraucht', 'getestet']));
|
|
}
|
|
|
|
// --- Templates ---
|
|
export function createTemplate(data) {
|
|
const stmt = db.prepare('INSERT INTO templates (name, platform, seller_notes, language, default_tags) VALUES (?, ?, ?, ?, ?)');
|
|
const result = stmt.run(data.name, data.platform || null, data.seller_notes || '', data.language || 'de', JSON.stringify(data.default_tags || []));
|
|
return getTemplate(result.lastInsertRowid);
|
|
}
|
|
|
|
export function getTemplate(id) {
|
|
const row = db.prepare('SELECT * FROM templates WHERE id = ?').get(id);
|
|
if (!row) return null;
|
|
row.default_tags = JSON.parse(row.default_tags || '[]');
|
|
return row;
|
|
}
|
|
|
|
export function getTemplates() {
|
|
return db.prepare('SELECT * FROM templates ORDER BY name').all().map(r => {
|
|
r.default_tags = JSON.parse(r.default_tags || '[]');
|
|
return r;
|
|
});
|
|
}
|
|
|
|
export function updateTemplate(id, data) {
|
|
db.prepare('UPDATE templates SET name = ?, platform = ?, seller_notes = ?, language = ?, default_tags = ? WHERE id = ?')
|
|
.run(data.name, data.platform || null, data.seller_notes || '', data.language || 'de', JSON.stringify(data.default_tags || []), id);
|
|
return getTemplate(id);
|
|
}
|
|
|
|
export function deleteTemplate(id) {
|
|
return db.prepare('DELETE FROM templates WHERE id = ?').run(id);
|
|
}
|
|
|
|
// --- Publish Log ---
|
|
try { db.exec(`CREATE TABLE IF NOT EXISTS publish_log (id INTEGER PRIMARY KEY AUTOINCREMENT, listing_id INTEGER NOT NULL, platform TEXT NOT NULL, status TEXT DEFAULT 'pending', external_id TEXT, external_url TEXT, published_at TEXT, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE)`); } catch {}
|
|
|
|
export function createPublishLog(listingId, platform, status, externalId, externalUrl) {
|
|
const stmt = db.prepare('INSERT INTO publish_log (listing_id, platform, status, external_id, external_url, published_at) VALUES (?, ?, ?, ?, ?, datetime(?))');
|
|
stmt.run(listingId, platform, status || 'pending', externalId || null, externalUrl || null, status === 'published' ? 'now' : null);
|
|
}
|
|
|
|
export function updatePublishLog(id, data) {
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, val] of Object.entries(data)) {
|
|
if (val === undefined) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(val);
|
|
}
|
|
if (!fields.length) return;
|
|
if (data.status === 'published') { fields.push("published_at = datetime('now')"); }
|
|
values.push(id);
|
|
db.prepare(`UPDATE publish_log SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
}
|
|
|
|
export function getPublishLogs(listingId) {
|
|
return db.prepare('SELECT * FROM publish_log WHERE listing_id = ? ORDER BY created_at DESC').all(listingId);
|
|
}
|
|
|
|
export function getPublishStatus(listingIds) {
|
|
if (!listingIds.length) return {};
|
|
const placeholders = listingIds.map(() => '?').join(',');
|
|
const rows = db.prepare(`SELECT listing_id, platform, status FROM publish_log WHERE listing_id IN (${placeholders})`).all(...listingIds);
|
|
const result = {};
|
|
for (const r of rows) {
|
|
if (!result[r.listing_id]) result[r.listing_id] = {};
|
|
result[r.listing_id][r.platform] = r.status;
|
|
}
|
|
return result;
|
|
}
|
|
|
|
// --- eBay Queue ---
|
|
try { db.exec(`CREATE TABLE IF NOT EXISTS ebay_queue (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
listing_id INTEGER NOT NULL,
|
|
status TEXT DEFAULT 'pending',
|
|
ebay_item_id TEXT,
|
|
ebay_url TEXT,
|
|
error TEXT,
|
|
retries INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT,
|
|
FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
|
|
)`); } catch {}
|
|
|
|
export function addToEbayQueue(listingIds) {
|
|
const stmt = db.prepare('INSERT OR IGNORE INTO ebay_queue (listing_id) VALUES (?)');
|
|
const existing = db.prepare('SELECT listing_id FROM ebay_queue WHERE listing_id = ? AND status IN (?, ?)');
|
|
const added = [];
|
|
for (const id of listingIds) {
|
|
const ex = existing.get(id, 'pending', 'uploading');
|
|
if (!ex) {
|
|
stmt.run(id);
|
|
added.push(id);
|
|
}
|
|
}
|
|
return added;
|
|
}
|
|
|
|
export function getEbayQueue(status) {
|
|
let sql = 'SELECT q.*, l.title, l.sku, l.platform FROM ebay_queue q JOIN listings l ON q.listing_id = l.id';
|
|
if (status) { sql += ' WHERE q.status = ?'; return db.prepare(sql + ' ORDER BY q.created_at').all(status); }
|
|
return db.prepare(sql + ' ORDER BY q.created_at DESC').all();
|
|
}
|
|
|
|
export function updateEbayQueue(id, data) {
|
|
const fields = ["updated_at = datetime('now')"];
|
|
const values = [];
|
|
for (const [key, val] of Object.entries(data)) {
|
|
if (val === undefined) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(val);
|
|
}
|
|
values.push(id);
|
|
db.prepare(`UPDATE ebay_queue SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
}
|
|
|
|
export function removeFromEbayQueue(id) {
|
|
db.prepare('DELETE FROM ebay_queue WHERE id = ?').run(id);
|
|
}
|
|
|
|
export function clearEbayQueue(status) {
|
|
if (status) db.prepare('DELETE FROM ebay_queue WHERE status = ?').run(status);
|
|
else db.prepare('DELETE FROM ebay_queue').run();
|
|
}
|
|
|
|
// --- Scheduled Publishes ---
|
|
try { db.exec(`CREATE TABLE IF NOT EXISTS scheduled_publish (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
listing_id INTEGER NOT NULL,
|
|
platform TEXT NOT NULL,
|
|
scheduled_at TEXT NOT NULL,
|
|
status TEXT DEFAULT 'scheduled',
|
|
error TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
|
|
)`); } catch {}
|
|
|
|
export function schedulePublish(listingId, platform, scheduledAt) {
|
|
const existing = db.prepare('SELECT id FROM scheduled_publish WHERE listing_id = ? AND platform = ? AND status = ?').get(listingId, platform, 'scheduled');
|
|
if (existing) {
|
|
db.prepare('UPDATE scheduled_publish SET scheduled_at = ? WHERE id = ?').run(scheduledAt, existing.id);
|
|
return existing.id;
|
|
}
|
|
const r = db.prepare('INSERT INTO scheduled_publish (listing_id, platform, scheduled_at) VALUES (?, ?, ?)').run(listingId, platform, scheduledAt);
|
|
return r.lastInsertRowid;
|
|
}
|
|
|
|
export function getScheduledPublishes(status) {
|
|
let sql = 'SELECT s.*, l.title, l.sku, l.price FROM scheduled_publish s JOIN listings l ON s.listing_id = l.id';
|
|
if (status) { sql += ' WHERE s.status = ?'; return db.prepare(sql + ' ORDER BY s.scheduled_at').all(status); }
|
|
return db.prepare(sql + ' ORDER BY s.scheduled_at').all();
|
|
}
|
|
|
|
export function getDueScheduled() {
|
|
return db.prepare("SELECT s.*, l.title, l.sku, l.platform as listing_platform FROM scheduled_publish s JOIN listings l ON s.listing_id = l.id WHERE s.status = 'scheduled' AND s.scheduled_at <= datetime('now') ORDER BY s.scheduled_at").all();
|
|
}
|
|
|
|
export function updateScheduledPublish(id, data) {
|
|
const fields = [];
|
|
const values = [];
|
|
for (const [key, val] of Object.entries(data)) {
|
|
if (val === undefined) continue;
|
|
fields.push(`${key} = ?`);
|
|
values.push(val);
|
|
}
|
|
if (!fields.length) return;
|
|
values.push(id);
|
|
db.prepare(`UPDATE scheduled_publish SET ${fields.join(', ')} WHERE id = ?`).run(...values);
|
|
}
|
|
|
|
export function removeScheduledPublish(id) {
|
|
db.prepare('DELETE FROM scheduled_publish WHERE id = ?').run(id);
|
|
}
|
|
|
|
// --- Inventory / Storage ---
|
|
export function getStorageLocations() {
|
|
return db.prepare(`
|
|
SELECT storage_location, COUNT(*) as count,
|
|
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
|
|
SUM(CASE WHEN status = 'sold' THEN 1 ELSE 0 END) as sold,
|
|
SUM(CASE WHEN status = 'reserved' THEN 1 ELSE 0 END) as reserved,
|
|
COALESCE(SUM(CASE WHEN status = 'active' THEN price ELSE 0 END), 0) as total_value
|
|
FROM listings
|
|
WHERE deleted_at IS NULL AND storage_location IS NOT NULL AND storage_location != ''
|
|
GROUP BY storage_location ORDER BY storage_location
|
|
`).all();
|
|
}
|
|
|
|
export function getListingsByLocation(location) {
|
|
return db.prepare(
|
|
"SELECT * FROM listings WHERE deleted_at IS NULL AND storage_location = ? ORDER BY created_at DESC"
|
|
).all(location).map(parseRow);
|
|
}
|
|
|
|
export function getUnassignedListings() {
|
|
return db.prepare(
|
|
"SELECT * FROM listings WHERE deleted_at IS NULL AND (storage_location IS NULL OR storage_location = '') ORDER BY created_at DESC"
|
|
).all().map(parseRow);
|
|
}
|
|
|
|
export function bulkUpdateLocation(ids, location) {
|
|
const stmt = db.prepare("UPDATE listings SET storage_location = ?, updated_at = datetime('now') WHERE id = ?");
|
|
const tx = db.transaction(() => { for (const id of ids) stmt.run(location, id); });
|
|
tx();
|
|
return ids.length;
|
|
}
|
|
|
|
// --- CSV Export ---
|
|
export function getListingsForExport(platform, status) {
|
|
return getListings(null, platform, status);
|
|
}
|
|
|
|
// --- Studio Flow Dashboard ---
|
|
function listingCompleteness(listing) {
|
|
const checks = [
|
|
[listing.title, 14],
|
|
[listing.description, 16],
|
|
[Array.isArray(listing.photos) && listing.photos.length > 0, 18],
|
|
[listing.brand, 8],
|
|
[listing.category, 12],
|
|
[listing.condition, 10],
|
|
[Number(listing.price) > 0, 10],
|
|
[listing.size, 4],
|
|
[listing.color, 4],
|
|
[Array.isArray(listing.tags) && listing.tags.length > 0, 4],
|
|
];
|
|
return checks.reduce((score, [value, weight]) => score + (value ? weight : 0), 0);
|
|
}
|
|
|
|
function sqliteDateToIso(value) {
|
|
if (!value) return null;
|
|
if (value.includes('T')) return value;
|
|
return `${value.replace(' ', 'T')}Z`;
|
|
}
|
|
|
|
export function getDashboardWorkflow(limitPerStage = 3) {
|
|
const listings = getListings();
|
|
const publishRows = db.prepare('SELECT * FROM publish_log ORDER BY created_at DESC').all();
|
|
const scheduleRows = db.prepare("SELECT * FROM scheduled_publish WHERE status = 'scheduled' ORDER BY scheduled_at").all();
|
|
const queueRows = db.prepare('SELECT * FROM ebay_queue ORDER BY created_at DESC').all();
|
|
|
|
const latestPublish = new Map();
|
|
for (const row of publishRows) {
|
|
if (!latestPublish.has(row.listing_id)) latestPublish.set(row.listing_id, row);
|
|
}
|
|
const activeSchedule = new Map(scheduleRows.map(row => [row.listing_id, row]));
|
|
const latestQueue = new Map();
|
|
for (const row of queueRows) {
|
|
if (!latestQueue.has(row.listing_id)) latestQueue.set(row.listing_id, row);
|
|
}
|
|
|
|
const stages = {
|
|
captured: [],
|
|
ai_review: [],
|
|
ready: [],
|
|
scheduled: [],
|
|
published: [],
|
|
sold: [],
|
|
};
|
|
|
|
const itemById = new Map();
|
|
for (const listing of listings) {
|
|
const completeness = listingCompleteness(listing);
|
|
const publish = latestPublish.get(listing.id) || null;
|
|
const schedule = activeSchedule.get(listing.id) || null;
|
|
const queue = latestQueue.get(listing.id) || null;
|
|
|
|
let stage = 'ready';
|
|
if (listing.status === 'sold') stage = 'sold';
|
|
else if (schedule) stage = 'scheduled';
|
|
else if (publish?.status === 'published' || queue?.status === 'published') stage = 'published';
|
|
else if (completeness < 55) stage = 'captured';
|
|
else if (completeness < 85) stage = 'ai_review';
|
|
|
|
const item = {
|
|
id: listing.id,
|
|
title: listing.title || 'Unbenanntes Listing',
|
|
sku: listing.sku || `VD-${String(listing.id).padStart(4, '0')}`,
|
|
platform: listing.platform,
|
|
aiProvider: listing.ai_provider,
|
|
photos: listing.photos || [],
|
|
price: listing.price,
|
|
status: listing.status,
|
|
storageLocation: listing.storage_location,
|
|
brand: listing.brand,
|
|
category: listing.category,
|
|
size: listing.size,
|
|
color: listing.color,
|
|
condition: listing.condition,
|
|
completeness,
|
|
stage,
|
|
scheduledAt: schedule?.scheduled_at || null,
|
|
publishStatus: publish?.status || queue?.status || null,
|
|
queueStatus: queue?.status || null,
|
|
createdAt: listing.created_at,
|
|
updatedAt: listing.updated_at,
|
|
};
|
|
stages[stage].push(item);
|
|
itemById.set(listing.id, item);
|
|
}
|
|
|
|
const attention = [];
|
|
const addAttention = (listingId, type, message, action, severity = 'warning') => {
|
|
const item = itemById.get(listingId);
|
|
if (!item) return;
|
|
attention.push({
|
|
id: `${listingId}:${type}`,
|
|
listingId,
|
|
type,
|
|
message,
|
|
action,
|
|
severity,
|
|
title: item.title,
|
|
sku: item.sku,
|
|
photo: item.photos?.[0] || null,
|
|
createdAt: item.updatedAt || item.createdAt,
|
|
});
|
|
};
|
|
|
|
for (const row of queueRows.filter(row => row.status === 'failed')) {
|
|
addAttention(row.listing_id, 'publish_failed', row.error || 'eBay-Veröffentlichung fehlgeschlagen', 'Erneut versuchen', 'danger');
|
|
}
|
|
for (const row of publishRows.filter(row => row.status === 'failed')) {
|
|
addAttention(row.listing_id, 'publish_failed', 'Veröffentlichung fehlgeschlagen', 'Prüfen', 'danger');
|
|
}
|
|
for (const item of itemById.values()) {
|
|
if (!Number(item.price)) addAttention(item.id, 'missing_price', 'Preis fehlt', 'Preis setzen');
|
|
if (!item.storageLocation && item.status !== 'sold') addAttention(item.id, 'missing_location', 'Lagerort fehlt', 'Lagerort setzen');
|
|
if (item.completeness < 85) addAttention(item.id, 'low_confidence', `Listing-Qualität ${item.completeness}%`, 'Prüfen');
|
|
}
|
|
|
|
const attentionPriority = { danger: 0, warning: 1, info: 2 };
|
|
attention.sort((a, b) => {
|
|
const severity = (attentionPriority[a.severity] ?? 9) - (attentionPriority[b.severity] ?? 9);
|
|
if (severity) return severity;
|
|
return String(b.createdAt || '').localeCompare(String(a.createdAt || ''));
|
|
});
|
|
|
|
const activity = [];
|
|
for (const listing of listings.slice(0, 12)) {
|
|
activity.push({
|
|
type: listing.status === 'sold' ? 'sold' : 'created',
|
|
listingId: listing.id,
|
|
text: listing.status === 'sold'
|
|
? `${listing.title || listing.sku} wurde als verkauft markiert`
|
|
: `${listing.title || listing.sku} wurde erfasst`,
|
|
at: sqliteDateToIso(listing.updated_at || listing.created_at),
|
|
});
|
|
}
|
|
for (const row of publishRows.slice(0, 12)) {
|
|
const item = itemById.get(row.listing_id);
|
|
if (!item) continue;
|
|
const verbs = { published: 'veröffentlicht', failed: 'konnte nicht veröffentlicht werden', copied: 'für Smart Copy vorbereitet', pending: 'zur Veröffentlichung vorgemerkt' };
|
|
activity.push({
|
|
type: row.status || 'published',
|
|
listingId: row.listing_id,
|
|
text: `${item.title} wurde auf ${row.platform} ${verbs[row.status] || row.status}`,
|
|
at: sqliteDateToIso(row.published_at || row.created_at),
|
|
});
|
|
}
|
|
for (const row of scheduleRows.slice(0, 8)) {
|
|
const item = itemById.get(row.listing_id);
|
|
if (!item) continue;
|
|
activity.push({
|
|
type: 'scheduled',
|
|
listingId: row.listing_id,
|
|
text: `${item.title} wurde für ${row.platform} geplant`,
|
|
at: sqliteDateToIso(row.created_at),
|
|
});
|
|
}
|
|
activity.sort((a, b) => String(b.at || '').localeCompare(String(a.at || '')));
|
|
|
|
const orderedStages = {};
|
|
for (const [key, items] of Object.entries(stages)) {
|
|
orderedStages[key] = {
|
|
count: items.length,
|
|
items: items.slice(0, Math.max(1, Number(limitPerStage) || 3)),
|
|
};
|
|
}
|
|
|
|
return {
|
|
stages: orderedStages,
|
|
attention: attention.slice(0, 6),
|
|
attentionCount: attention.length,
|
|
activity: activity.slice(0, 8),
|
|
totals: {
|
|
listings: listings.length,
|
|
active: listings.filter(item => item.status === 'active').length,
|
|
sold: listings.filter(item => item.status === 'sold').length,
|
|
inventoryValue: listings.reduce((sum, item) => sum + (item.status !== 'sold' ? Number(item.price) || 0 : 0), 0),
|
|
},
|
|
};
|
|
}
|
|
|
|
export function getDashboardAnalytics(days = 30) {
|
|
const periodDays = Math.min(Math.max(Number(days) || 30, 7), 365);
|
|
const listings = getListings();
|
|
const publishRows = db.prepare('SELECT * FROM publish_log ORDER BY created_at DESC').all();
|
|
const queueRows = db.prepare('SELECT * FROM ebay_queue ORDER BY created_at DESC').all();
|
|
const scheduleRows = db.prepare('SELECT * FROM scheduled_publish ORDER BY created_at DESC').all();
|
|
const locations = getStorageLocations();
|
|
|
|
const now = new Date();
|
|
const start = new Date(now);
|
|
start.setHours(0, 0, 0, 0);
|
|
start.setDate(start.getDate() - (periodDays - 1));
|
|
const weekStart = new Date(now);
|
|
weekStart.setDate(weekStart.getDate() - 7);
|
|
const todayKey = now.toISOString().slice(0, 10);
|
|
|
|
const soldListings = listings.filter(item => item.status === 'sold');
|
|
const dateOf = item => {
|
|
const raw = item.updated_at || item.created_at;
|
|
if (!raw) return null;
|
|
const parsed = new Date(String(raw).replace(' ', 'T') + (String(raw).includes('Z') ? '' : 'Z'));
|
|
return Number.isNaN(parsed.getTime()) ? null : parsed;
|
|
};
|
|
|
|
const timelineMap = new Map();
|
|
for (let index = 0; index < periodDays; index++) {
|
|
const date = new Date(start);
|
|
date.setDate(start.getDate() + index);
|
|
const key = date.toISOString().slice(0, 10);
|
|
timelineMap.set(key, { date: key, revenue: 0, sales: 0 });
|
|
}
|
|
|
|
const platformRevenue = new Map();
|
|
const categoryRevenue = new Map();
|
|
for (const listing of soldListings) {
|
|
const date = dateOf(listing);
|
|
const price = Number(listing.price) || 0;
|
|
if (date) {
|
|
const key = date.toISOString().slice(0, 10);
|
|
const bucket = timelineMap.get(key);
|
|
if (bucket) {
|
|
bucket.revenue += price;
|
|
bucket.sales += 1;
|
|
}
|
|
}
|
|
const platform = listing.platform || 'Sonstige';
|
|
platformRevenue.set(platform, (platformRevenue.get(platform) || 0) + price);
|
|
const category = listing.category ? String(listing.category).split('>').pop().trim() : 'Sonstiges';
|
|
categoryRevenue.set(category, (categoryRevenue.get(category) || 0) + price);
|
|
}
|
|
|
|
const successes = publishRows.filter(row => row.status === 'published').length;
|
|
const failures = publishRows.filter(row => row.status === 'failed').length + queueRows.filter(row => row.status === 'failed').length;
|
|
const publishAttempts = successes + failures;
|
|
const quality = listings.length
|
|
? Math.round(listings.reduce((sum, item) => sum + listingCompleteness(item), 0) / listings.length)
|
|
: 0;
|
|
|
|
const workflow = getDashboardWorkflow(6);
|
|
const recentActivity = workflow.activity.slice(0, 6);
|
|
const notifications = workflow.attention.slice(0, 6);
|
|
const mobileUploads = db.prepare("SELECT COUNT(*) AS count FROM mobile_upload_sessions WHERE created_at >= datetime('now', '-24 hours')").get()?.count || 0;
|
|
|
|
const topSellers = [...soldListings]
|
|
.sort((a, b) => (Number(b.price) || 0) - (Number(a.price) || 0))
|
|
.slice(0, 5)
|
|
.map(item => ({
|
|
id: item.id,
|
|
title: item.title,
|
|
sku: item.sku,
|
|
platform: item.platform,
|
|
revenue: Number(item.price) || 0,
|
|
sales: 1,
|
|
status: item.status,
|
|
photo: item.photos?.[0] || null,
|
|
size: item.size,
|
|
color: item.color,
|
|
}));
|
|
|
|
return {
|
|
periodDays,
|
|
generatedAt: new Date().toISOString(),
|
|
kpis: {
|
|
revenueToday: soldListings.filter(item => String(item.updated_at || item.created_at || '').startsWith(todayKey)).reduce((sum, item) => sum + (Number(item.price) || 0), 0),
|
|
sales7Days: soldListings.filter(item => { const date = dateOf(item); return date && date >= weekStart; }).length,
|
|
activeListings: listings.filter(item => !['sold', 'draft'].includes(item.status)).length,
|
|
drafts: listings.filter(item => item.status === 'draft').length,
|
|
inventoryValue: listings.filter(item => item.status !== 'sold').reduce((sum, item) => sum + (Number(item.price) || 0), 0),
|
|
publishSuccessRate: publishAttempts ? Math.round((successes / publishAttempts) * 1000) / 10 : 100,
|
|
quality,
|
|
},
|
|
timeline: [...timelineMap.values()],
|
|
platformRevenue: [...platformRevenue.entries()].map(([platform, revenue]) => ({ platform, revenue })).sort((a, b) => b.revenue - a.revenue),
|
|
categoryRevenue: [...categoryRevenue.entries()].map(([category, revenue]) => ({ category, revenue })).sort((a, b) => b.revenue - a.revenue).slice(0, 6),
|
|
focus: {
|
|
ready: workflow.stages?.ready?.count || 0,
|
|
priceChecks: workflow.attention.filter(item => item.type === 'missing_price').length,
|
|
failedPublishes: failures,
|
|
mobileUploads,
|
|
activeLocations: locations.filter(item => Number(item.count) > 0).length,
|
|
},
|
|
topSellers,
|
|
activity: recentActivity,
|
|
notifications,
|
|
system: {
|
|
healthy: true,
|
|
queuePending: queueRows.filter(item => item.status === 'pending').length,
|
|
scheduled: scheduleRows.filter(item => item.status === 'scheduled').length,
|
|
},
|
|
};
|
|
}
|
|
|
|
// --- Publishing Hardening 1.29.0 ---
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS publishing_jobs (
|
|
id TEXT PRIMARY KEY,
|
|
listing_id INTEGER NOT NULL,
|
|
platform TEXT NOT NULL,
|
|
mode TEXT NOT NULL DEFAULT 'api',
|
|
action TEXT NOT NULL DEFAULT 'publish',
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
phase TEXT NOT NULL DEFAULT 'queued',
|
|
idempotency_key TEXT NOT NULL UNIQUE,
|
|
external_id TEXT,
|
|
external_url TEXT,
|
|
offer_id TEXT,
|
|
retries INTEGER NOT NULL DEFAULT 0,
|
|
max_retries INTEGER NOT NULL DEFAULT 3,
|
|
next_attempt_at TEXT,
|
|
claimed_by TEXT,
|
|
claimed_at TEXT,
|
|
error_code TEXT,
|
|
error_message TEXT,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
result_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
started_at TEXT,
|
|
completed_at TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(listing_id) REFERENCES listings(id) ON DELETE CASCADE
|
|
)`);
|
|
db.exec(`CREATE TABLE IF NOT EXISTS publishing_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
job_id TEXT,
|
|
listing_id INTEGER NOT NULL,
|
|
platform TEXT NOT NULL,
|
|
level TEXT NOT NULL DEFAULT 'info',
|
|
event_type TEXT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
details_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(job_id) REFERENCES publishing_jobs(id) ON DELETE SET NULL,
|
|
FOREIGN KEY(listing_id) REFERENCES listings(id) ON DELETE CASCADE
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_publishing_jobs_status ON publishing_jobs(status, next_attempt_at, created_at)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_publishing_jobs_listing ON publishing_jobs(listing_id, platform, created_at DESC)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_publishing_events_job ON publishing_events(job_id, id DESC)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_publishing_events_listing ON publishing_events(listing_id, id DESC)`);
|
|
} catch {}
|
|
|
|
for (const [column, type] of [
|
|
['offer_id', 'TEXT'], ['sync_status', 'TEXT'], ['last_synced_at', 'TEXT'], ['ended_at', 'TEXT'], ['updated_at', 'TEXT']
|
|
]) {
|
|
try { db.exec(`ALTER TABLE publish_log ADD COLUMN ${column} ${type}`); } catch {}
|
|
}
|
|
try { db.exec(`CREATE INDEX IF NOT EXISTS idx_publish_log_external ON publish_log(platform, external_id)`); } catch {}
|
|
|
|
// --- Operations Center 1.30.0 ---
|
|
try {
|
|
db.exec(`CREATE TABLE IF NOT EXISTS operation_backups (
|
|
id TEXT PRIMARY KEY,
|
|
kind TEXT NOT NULL DEFAULT 'manual',
|
|
label TEXT,
|
|
file_name TEXT NOT NULL,
|
|
file_path TEXT NOT NULL UNIQUE,
|
|
file_size INTEGER NOT NULL DEFAULT 0,
|
|
sha256 TEXT,
|
|
status TEXT NOT NULL DEFAULT 'created',
|
|
verified_at TEXT,
|
|
options_json TEXT NOT NULL DEFAULT '{}',
|
|
manifest_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_operation_backups_created ON operation_backups(created_at DESC)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_operation_backups_kind ON operation_backups(kind, created_at DESC)`);
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS operation_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS update_operations (
|
|
id TEXT PRIMARY KEY,
|
|
version_from TEXT NOT NULL,
|
|
version_to TEXT NOT NULL,
|
|
source_name TEXT,
|
|
archive_path TEXT NOT NULL,
|
|
sha256 TEXT,
|
|
status TEXT NOT NULL DEFAULT 'staged',
|
|
details_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
applied_at TEXT,
|
|
error_message TEXT
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_update_operations_created ON update_operations(created_at DESC)`);
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS system_migrations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
migration_key TEXT NOT NULL UNIQUE,
|
|
app_version TEXT NOT NULL,
|
|
description TEXT,
|
|
applied_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS extension_clients (
|
|
client_id TEXT PRIMARY KEY,
|
|
browser TEXT,
|
|
version TEXT,
|
|
platform TEXT,
|
|
server_url TEXT,
|
|
user_agent TEXT,
|
|
capabilities_json TEXT NOT NULL DEFAULT '{}',
|
|
first_seen TEXT NOT NULL DEFAULT (datetime('now')),
|
|
last_seen TEXT NOT NULL DEFAULT (datetime('now'))
|
|
)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_extension_clients_seen ON extension_clients(last_seen DESC)`);
|
|
|
|
db.prepare(`INSERT OR IGNORE INTO system_migrations (migration_key, app_version, description)
|
|
VALUES ('operations-center-1.30.0', '1.30.0', 'Backup, Restore, Update Center und Extension-Diagnose')`).run();
|
|
} catch {}
|