vendoo/tools/verify-db-migrations.mjs

import { mkdtempSync, readFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join, resolve } from 'node:path';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';

const APP = resolve(fileURLToPath(new URL('..', import.meta.url)));
const BASE = process.argv[2] ? resolve(process.argv[2]) : null;

function execDbExecBlocks(db, sourcePath) {
  const text = readFileSync(sourcePath, 'utf8');
  const blocks = [...text.matchAll(/db\.exec\(`([\s\S]*?)`\)/g)];
  let applied = 0;
  for (const [, sql] of blocks) {
    if (sql.includes('${')) continue;
    try { db.exec(sql); applied += 1; } catch { /* additive ALTER blocks intentionally tolerate existing columns */ }
  }
  if (text.includes('operations-center-1.30.0')) {
    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();
  }
  return applied;
}

function columns(db, table) {
  return new Set(db.prepare(`PRAGMA table_info(${JSON.stringify(table)})`).all().map((row) => row.name));
}

function verify(db) {
  const requiredTables = new Set([
    'flux_images', 'flux_prompt_jobs', 'flux_prompt_job_variants', 'image_edit_versions',
    'image_batch_profiles', 'image_batch_jobs', 'image_batch_job_items', 'quality_reports',
    'publishing_jobs', 'publishing_events', 'operation_backups', 'operation_settings',
    'update_operations', 'system_migrations', 'extension_clients', 'users', 'sessions',
    'audit_log', 'resource_locks', 'theme_profiles', 'user_theme_preferences',
  ]);
  const existingTables = new Set(db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all().map((row) => row.name));
  const missingTables = [...requiredTables].filter((name) => !existingTables.has(name));
  const checks = {
    flux_images: ['favorite', 'job_id', 'variant_index', 'prompt_mode', 'parameters_json'],
    flux_prompt_jobs: ['archived_at'],
    image_edit_versions: ['root_path', 'source_path', 'output_path', 'context', 'operations_json', 'width', 'height', 'format', 'quality', 'file_size', 'user_id', 'created_at'],
    image_batch_jobs: ['name', 'status', 'phase', 'source_count', 'profile_count', 'total_items', 'completed_items', 'failed_items', 'cancelled_items', 'pause_requested', 'cancel_requested', 'settings_json', 'created_at', 'updated_at'],
    image_batch_job_items: ['job_id', 'source_path', 'profile_id', 'profile_key', 'profile_name', 'status', 'phase', 'settings_json', 'output_path', 'error_message', 'created_at', 'updated_at'],
    quality_reports: ['listing_id', 'score', 'grade', 'ready', 'blockers', 'warnings', 'recommendations', 'report_json', 'analyzed_at'],
    publishing_jobs: ['id', 'listing_id', 'platform', 'mode', 'action', 'status', 'phase', 'idempotency_key', 'external_id', 'external_url', 'offer_id', 'retries', 'max_retries', 'next_attempt_at', 'claimed_by', 'claimed_at', 'error_code', 'error_message', 'payload_json', 'result_json', 'created_at', 'started_at', 'completed_at', 'updated_at'],
    publishing_events: ['id', 'job_id', 'listing_id', 'platform', 'level', 'event_type', 'message', 'details_json', 'created_at'],
    publish_log: ['offer_id', 'sync_status', 'last_synced_at', 'ended_at', 'updated_at'],
    operation_backups: ['id', 'kind', 'label', 'file_name', 'file_path', 'file_size', 'sha256', 'status', 'verified_at', 'options_json', 'manifest_json', 'created_at'],
    operation_settings: ['key', 'value', 'updated_at'],
    update_operations: ['id', 'version_from', 'version_to', 'source_name', 'archive_path', 'sha256', 'status', 'details_json', 'created_at', 'applied_at', 'error_message'],
    system_migrations: ['id', 'migration_key', 'app_version', 'description', 'applied_at'],
    extension_clients: ['client_id', 'browser', 'version', 'platform', 'server_url', 'user_agent', 'capabilities_json', 'first_seen', 'last_seen'],
    users: ['id', 'email', 'name', 'password_hash', 'role', 'active', 'avatar_color', 'last_active_at', 'failed_login_count', 'locked_until', 'password_changed_at', 'created_at', 'invited_by'],
    sessions: ['id', 'user_id', 'token_hash', 'ip', 'user_agent', 'expires_at', 'last_seen_at', 'revoked_at', 'created_at'],
    audit_log: ['id', 'user_id', 'user_email', 'action', 'target_type', 'target_id', 'details', 'ip', 'created_at'],
    resource_locks: ['id', 'resource_type', 'resource_id', 'user_id', 'token_hash', 'acquired_at', 'renewed_at', 'expires_at', 'client_label'],
    theme_profiles: ['id', 'name', 'base_theme', 'values_json', 'created_by', 'created_at', 'updated_at'],
    user_theme_preferences: ['user_id', 'theme_id', 'density', 'reduced_motion', 'updated_at'],
  };
  const missingColumns = {};
  for (const [table, required] of Object.entries(checks)) {
    const existing = columns(db, table);
    const missing = required.filter((name) => !existing.has(name));
    if (missing.length) missingColumns[table] = missing.sort();
  }
  if (missingTables.length || Object.keys(missingColumns).length) {
    throw new Error(JSON.stringify({ missing_tables: missingTables.sort(), missing_columns: missingColumns }));
  }
}

const tempRoot = mkdtempSync(join(tmpdir(), 'vendoo-db-migrations-'));
let fresh;
let existing;
try {
  fresh = new DatabaseSync(join(tempRoot, 'fresh.db'));
  fresh.exec(readFileSync(join(APP, 'db/schema.sql'), 'utf8'));
  const freshBlocks = execDbExecBlocks(fresh, join(APP, 'lib/db.mjs'));
  verify(fresh);

  existing = new DatabaseSync(join(tempRoot, 'existing.db'));
  existing.exec(readFileSync(join(APP, 'db/schema.sql'), 'utf8'));
  if (BASE) execDbExecBlocks(existing, join(BASE, 'lib/db.mjs'));
  existing.prepare("INSERT INTO flux_images (prompt,image_path,width,height,seed,steps,style) VALUES ('Bestand','ai-generated/existing.png',768,768,42,4,'none')").run();
  const existingBlocks = execDbExecBlocks(existing, join(APP, 'lib/db.mjs'));
  verify(existing);
  const row = existing.prepare("SELECT prompt,image_path,favorite FROM flux_images WHERE image_path='ai-generated/existing.png'").get();
  if (!row || row.prompt !== 'Bestand' || row.image_path !== 'ai-generated/existing.png' || row.favorite !== 0) {
    throw new Error(`Bestandsdaten verändert: ${JSON.stringify(row)}`);
  }

  const insertJob = existing.prepare(`INSERT INTO flux_prompt_jobs
    (id,prompt,final_prompt,width,height,steps,style,profile,prompt_mode,assistant,variants,seed_base,seed_lock,status,phase,settings_json)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`);
  for (const [jobId, status] of [['job-complete', 'completed'], ['job-failed', 'failed'], ['job-active', 'running']]) {
    insertJob.run(jobId, 'Test', 'Test', 768, 768, 6, 'none', 'balanced', 'precise', 1, 1, 42, 0, status, status, '{}');
  }
  existing.prepare("UPDATE flux_prompt_jobs SET archived_at=datetime('now') WHERE id IN ('job-complete','job-failed','job-active') AND status NOT IN ('queued','running','cancelling')").run();
  const archived = new Set(existing.prepare('SELECT id FROM flux_prompt_jobs WHERE archived_at IS NOT NULL').all().map((item) => item.id));
  if (archived.size !== 2 || !archived.has('job-complete') || !archived.has('job-failed')) {
    throw new Error(`Archivschutz inkorrekt: ${JSON.stringify([...archived])}`);
  }

  existing.prepare("INSERT INTO listings (title, platform, ai_provider, photos, tags) VALUES ('Publishing Test', 'ebay-de', 'openai', '[]', '[]')").run();
  const listingId = existing.prepare('SELECT last_insert_rowid() AS id').get().id;
  existing.prepare("INSERT INTO publishing_jobs (id, listing_id, platform, mode, action, status, phase, idempotency_key) VALUES ('pub-job-1', ?, 'ebay-de', 'api', 'publish', 'queued', 'queued', 'key-1')").run(listingId);
  existing.prepare("INSERT INTO publishing_events (job_id, listing_id, platform, level, event_type, message) VALUES ('pub-job-1', ?, 'ebay-de', 'info', 'created', 'Auftrag erstellt')").run(listingId);
  let duplicateGuard = false;
  try {
    existing.prepare("INSERT INTO publishing_jobs (id, listing_id, platform, mode, action, status, phase, idempotency_key) VALUES ('pub-job-2', ?, 'ebay-de', 'api', 'publish', 'queued', 'queued', 'key-1')").run(listingId);
  } catch { duplicateGuard = true; }
  if (!duplicateGuard) throw new Error('Idempotency-Key verhindert keinen doppelten Auftrag');
  existing.prepare("UPDATE publishing_jobs SET status='running', phase='publish' WHERE id='pub-job-1'").run();
  existing.prepare("UPDATE publishing_jobs SET status='queued', phase='recovered' WHERE status='running'").run();
  const recovered = existing.prepare("SELECT status, phase FROM publishing_jobs WHERE id='pub-job-1'").get();
  if (recovered.status !== 'queued' || recovered.phase !== 'recovered') throw new Error(`Publishing-Recovery fehlgeschlagen: ${JSON.stringify(recovered)}`);

  existing.prepare("INSERT INTO operation_settings (key,value) VALUES ('auto_backup_enabled','1')").run();
  existing.prepare("INSERT INTO operation_backups (id,kind,file_name,file_path,status) VALUES ('backup-1','manual','test.zip','/tmp/test.zip','verified')").run();
  existing.prepare("INSERT INTO update_operations (id,version_from,version_to,archive_path,status) VALUES ('update-1','1.29.0','1.30.0','/tmp/update.zip','staged')").run();
  existing.prepare("INSERT INTO extension_clients (client_id,browser,version,platform) VALUES ('client-1','chrome','1.9.0','popup')").run();
  const migration = existing.prepare("SELECT app_version FROM system_migrations WHERE migration_key='operations-center-1.30.0'").get();
  if (!migration || migration.app_version !== '1.30.0') throw new Error(`Operations-Migration fehlt: ${JSON.stringify(migration)}`);
  const extension = existing.prepare("SELECT browser,version FROM extension_clients WHERE client_id='client-1'").get();
  if (!extension || extension.browser !== 'chrome' || extension.version !== '1.9.0') throw new Error(`Extension-Client fehlerhaft: ${JSON.stringify(extension)}`);

  console.log(JSON.stringify({
    ok: true,
    fresh_db_exec_blocks: freshBlocks,
    existing_db_exec_blocks: existingBlocks,
    existing_row_preserved: true,
    publishing_hardening: { tables: ['publishing_jobs', 'publishing_events'], idempotency_key_unique: true, restart_recovery: true, events_persisted: true },
    operations_center: { backup_tables: true, update_tables: true, extension_heartbeat: true, migration_recorded: true },
    tables: ['flux_images', 'flux_prompt_jobs', 'flux_prompt_job_variants', 'image_edit_versions', 'image_batch_profiles', 'image_batch_jobs', 'image_batch_job_items', 'quality_reports', 'publishing_jobs', 'publishing_events', 'operation_backups', 'operation_settings', 'update_operations', 'system_migrations', 'extension_clients', 'theme_profiles', 'user_theme_preferences'],
  }, null, 2));
} finally {
  try { fresh?.close(); } catch {}
  try { existing?.close(); } catch {}
  rmSync(tempRoot, { recursive: true, force: true });
}