import { env } from 'cloudflare:workers';

let ready = false;

export async function ensureSchema() {
  if (ready) return;
  if (!env.DB) throw new Error('Datenbank ist nicht verfügbar.');

  await env.DB.batch([
    env.DB.prepare(`CREATE TABLE IF NOT EXISTS projects (
      id TEXT PRIMARY KEY NOT NULL,
      name TEXT NOT NULL,
      project_number TEXT NOT NULL DEFAULT '',
      location TEXT NOT NULL DEFAULT '',
      active INTEGER NOT NULL DEFAULT 1,
      created_by TEXT NOT NULL,
      created_at TEXT NOT NULL
    )`),
    env.DB.prepare(`CREATE TABLE IF NOT EXISTS reports (
      id TEXT PRIMARY KEY NOT NULL,
      project_id TEXT NOT NULL REFERENCES projects(id),
      user_id TEXT NOT NULL,
      user_email TEXT NOT NULL,
      user_name TEXT NOT NULL,
      report_date TEXT NOT NULL,
      workers INTEGER NOT NULL,
      start_time TEXT NOT NULL,
      end_time TEXT NOT NULL,
      weather TEXT NOT NULL DEFAULT '',
      temperature TEXT NOT NULL DEFAULT '',
      work_original TEXT NOT NULL,
      work_german TEXT NOT NULL DEFAULT '',
      obstacles_original TEXT NOT NULL DEFAULT '',
      obstacles_german TEXT NOT NULL DEFAULT '',
      materials_original TEXT NOT NULL DEFAULT '',
      materials_german TEXT NOT NULL DEFAULT '',
      translation_status TEXT NOT NULL DEFAULT 'pending',
      created_at TEXT NOT NULL
    )`),
    env.DB.prepare(`CREATE TABLE IF NOT EXISTS photos (
      id TEXT PRIMARY KEY NOT NULL,
      report_id TEXT NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
      object_key TEXT NOT NULL,
      file_name TEXT NOT NULL,
      content_type TEXT NOT NULL,
      size INTEGER NOT NULL,
      created_at TEXT NOT NULL
    )`),
    env.DB.prepare('CREATE INDEX IF NOT EXISTS idx_projects_active_name ON projects(active, name)'),
    env.DB.prepare('CREATE INDEX IF NOT EXISTS idx_reports_project_date ON reports(project_id, report_date)'),
    env.DB.prepare('CREATE INDEX IF NOT EXISTS idx_reports_user_date ON reports(user_id, report_date)'),
    env.DB.prepare('CREATE INDEX IF NOT EXISTS idx_photos_report ON photos(report_id)'),
  ]);
  await env.DB.prepare('PRAGMA optimize').run();
  ready = true;
}