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 {}
