import { env } from "@/lib/runtime-env";

let ready: Promise<void> | null = null;

export function ensureSchema() {
  ready ??= initialize();
  return ready;
}

async function initialize() {
  const ddl = [
    `CREATE TABLE IF NOT EXISTS quote_requests (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      reference VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL,
      email VARCHAR(180) NOT NULL, phone VARCHAR(30), company VARCHAR(140),
      profile_code VARCHAR(10) NOT NULL, sign_text VARCHAR(255) NOT NULL,
      letter_count INT NOT NULL DEFAULT 1, height_cm INT NOT NULL DEFAULT 30,
      width_cm INT NOT NULL, letter_spacing_cm INT NOT NULL DEFAULT 4,
      depth_mm INT NOT NULL, mounting VARCHAR(40) NOT NULL,
      facade_key VARCHAR(500), facade_name VARCHAR(255), facade_type VARCHAR(100),
      logo_key VARCHAR(500), logo_name VARCHAR(255), logo_type VARCHAR(100),
      font VARCHAR(50) NOT NULL DEFAULT 'grotesk',
      sign_color VARCHAR(16) NOT NULL DEFAULT '#f2eee7',
      light_color VARCHAR(16) NOT NULL DEFAULT '#ffb36b',
      position_x INT NOT NULL DEFAULT 50, position_y INT NOT NULL DEFAULT 45,
      visual_scale INT NOT NULL DEFAULT 100, night TINYINT(1) NOT NULL DEFAULT 1,
      estimated_price_cents INT NOT NULL, is_professional TINYINT(1) NOT NULL DEFAULT 0,
      status VARCHAR(30) NOT NULL DEFAULT 'new', archived TINYINT(1) NOT NULL DEFAULT 0,
      archived_at BIGINT, created_at BIGINT NOT NULL,
      INDEX quote_requests_email_idx (email),
      INDEX quote_requests_pipeline_idx (archived, status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS professional_applications (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      company VARCHAR(140) NOT NULL, contact_name VARCHAR(100) NOT NULL,
      email VARCHAR(180) NOT NULL, phone VARCHAR(30) NOT NULL,
      siret VARCHAR(20) NOT NULL, activity VARCHAR(100) NOT NULL,
      expected_volume VARCHAR(80), status VARCHAR(30) NOT NULL DEFAULT 'pending',
      access_token VARCHAR(128) UNIQUE, approved_at BIGINT, created_at BIGINT NOT NULL,
      INDEX professional_applications_siret_idx (siret)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS textile_quote_requests (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      reference VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL,
      email VARCHAR(180) NOT NULL, phone VARCHAR(30), company VARCHAR(140),
      profile_code VARCHAR(20) NOT NULL, width_cm INT NOT NULL, height_cm INT NOT NULL,
      quantity INT NOT NULL DEFAULT 1, finish VARCHAR(80) NOT NULL,
      installation VARCHAR(80) NOT NULL, visual_key VARCHAR(500),
      visual_name VARCHAR(255), visual_type VARCHAR(100),
      estimated_price_cents INT NOT NULL, is_professional TINYINT(1) NOT NULL DEFAULT 0,
      status VARCHAR(30) NOT NULL DEFAULT 'new', archived TINYINT(1) NOT NULL DEFAULT 0,
      archived_at BIGINT, created_at BIGINT NOT NULL,
      INDEX textile_quote_requests_email_idx (email),
      INDEX textile_quote_requests_pipeline_idx (archived, status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS neon_quote_requests (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      reference VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL,
      email VARCHAR(180) NOT NULL, phone VARCHAR(30), company VARCHAR(140),
      neon_text VARCHAR(255) NOT NULL, font VARCHAR(80) NOT NULL,
      width_cm INT NOT NULL, height_cm INT NOT NULL, tube_mm INT NOT NULL,
      color VARCHAR(40) NOT NULL, support VARCHAR(80) NOT NULL,
      installation VARCHAR(80) NOT NULL, quantity INT NOT NULL DEFAULT 1,
      design_key VARCHAR(500), design_name VARCHAR(255), design_type VARCHAR(100),
      estimated_price_cents INT NOT NULL, is_professional TINYINT(1) NOT NULL DEFAULT 0,
      status VARCHAR(30) NOT NULL DEFAULT 'new', archived TINYINT(1) NOT NULL DEFAULT 0,
      archived_at BIGINT, created_at BIGINT NOT NULL,
      INDEX neon_quote_requests_email_idx (email),
      INDEX neon_quote_requests_pipeline_idx (archived, status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS manual_projects (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      reference VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL,
      email VARCHAR(180) NOT NULL, phone VARCHAR(30), company VARCHAR(140),
      title VARCHAR(255) NOT NULL, description LONGTEXT NOT NULL,
      width_cm INT, height_cm INT, quantity INT NOT NULL DEFAULT 1,
      estimated_price_cents INT NOT NULL, is_professional TINYINT(1) NOT NULL DEFAULT 0,
      status VARCHAR(30) NOT NULL DEFAULT 'new', archived TINYINT(1) NOT NULL DEFAULT 0,
      archived_at BIGINT, created_at BIGINT NOT NULL,
      INDEX manual_projects_email_idx (email),
      INDEX manual_projects_pipeline_idx (archived, status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS pricing_profiles (
      code VARCHAR(20) NOT NULL PRIMARY KEY, factor_bp INT NOT NULL,
      base_price_cents INT NOT NULL DEFAULT 22000,
      rate_per_cm_letter_cents INT NOT NULL DEFAULT 450,
      professional_discount_bp INT NOT NULL DEFAULT 1800,
      price_mode VARCHAR(30) NOT NULL DEFAULT 'estimate',
      enabled TINYINT(1) NOT NULL DEFAULT 1, version INT NOT NULL DEFAULT 1,
      updated_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS sign_profile_catalog (
      code VARCHAR(20) NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL,
      short TEXT NOT NULL, image_url VARCHAR(500) NOT NULL,
      description LONGTEXT NOT NULL, ideal TEXT NOT NULL, spec_json LONGTEXT NOT NULL,
      active TINYINT(1) NOT NULL DEFAULT 1, approved TINYINT(1) NOT NULL DEFAULT 0,
      updated_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS textile_pricing_profiles (
      code VARCHAR(20) NOT NULL PRIMARY KEY, rate_per_m2_cents INT NOT NULL,
      base_price_cents INT NOT NULL DEFAULT 16000,
      professional_discount_bp INT NOT NULL DEFAULT 1800,
      enabled TINYINT(1) NOT NULL DEFAULT 1, version INT NOT NULL DEFAULT 1,
      updated_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS neon_pricing_rules (
      code VARCHAR(80) NOT NULL PRIMARY KEY, label VARCHAR(255) NOT NULL,
      section VARCHAR(80) NOT NULL, value INT NOT NULL, unit VARCHAR(30) NOT NULL,
      position INT NOT NULL DEFAULT 0, version INT NOT NULL DEFAULT 1,
      updated_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS commercial_quotes (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      request_type VARCHAR(20) NOT NULL, request_id BIGINT UNSIGNED NOT NULL,
      production_price_cents INT NOT NULL, delivery_price_cents INT NOT NULL DEFAULT 0,
      installation_price_cents INT NOT NULL DEFAULT 0, discount_cents INT NOT NULL DEFAULT 0,
      notes LONGTEXT, validity_days INT NOT NULL DEFAULT 30,
      public_token VARCHAR(128) UNIQUE, client_status VARCHAR(30) NOT NULL DEFAULT 'pending',
      client_name VARCHAR(140), client_note LONGTEXT, client_signature LONGTEXT,
      responded_at BIGINT, production_file_id BIGINT UNSIGNED, updated_at BIGINT NOT NULL,
      UNIQUE KEY commercial_quotes_request_unique (request_type, request_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS project_files (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      request_type VARCHAR(20) NOT NULL, request_id BIGINT UNSIGNED NOT NULL,
      label VARCHAR(255) NOT NULL, category VARCHAR(80) NOT NULL DEFAULT 'production',
      storage_key VARCHAR(500) NOT NULL UNIQUE, file_name VARCHAR(255) NOT NULL,
      content_type VARCHAR(100) NOT NULL, size_bytes BIGINT NOT NULL,
      factory_visible TINYINT(1) NOT NULL DEFAULT 0, share_token VARCHAR(128) NOT NULL UNIQUE,
      created_at BIGINT NOT NULL,
      INDEX project_files_request_idx (request_type, request_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS factory_access_links (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      request_type VARCHAR(20) NOT NULL, request_id BIGINT UNSIGNED NOT NULL,
      token VARCHAR(128) NOT NULL UNIQUE, enabled TINYINT(1) NOT NULL DEFAULT 1,
      created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL,
      UNIQUE KEY factory_access_request_unique (request_type, request_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS cms_content (
      \`key\` VARCHAR(190) NOT NULL PRIMARY KEY, label VARCHAR(255) NOT NULL,
      section VARCHAR(100) NOT NULL, draft_value LONGTEXT NOT NULL,
      published_value LONGTEXT NOT NULL, updated_at BIGINT NOT NULL,
      published_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS cms_revisions (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      content_key VARCHAR(190) NOT NULL, value LONGTEXT NOT NULL,
      action VARCHAR(50) NOT NULL, author_email VARCHAR(180), created_at BIGINT NOT NULL,
      INDEX cms_revisions_content_key_idx (content_key, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS cms_portfolio (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL, location VARCHAR(255) NOT NULL DEFAULT 'France',
      category VARCHAR(100) NOT NULL DEFAULT 'Enseigne', description LONGTEXT NOT NULL,
      image_url VARCHAR(500) NOT NULL, alt_text VARCHAR(255) NOT NULL,
      status VARCHAR(30) NOT NULL DEFAULT 'draft', featured TINYINT(1) NOT NULL DEFAULT 0,
      position INT NOT NULL DEFAULT 0, published_at BIGINT, updated_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS cms_references (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL, website_url VARCHAR(500) NOT NULL,
      logo_url VARCHAR(500) NOT NULL, alt_text VARCHAR(255) NOT NULL,
      status VARCHAR(30) NOT NULL DEFAULT 'draft', position INT NOT NULL DEFAULT 0,
      updated_at BIGINT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS admin_users (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      email VARCHAR(180) NOT NULL UNIQUE, display_name VARCHAR(100) NOT NULL,
      password_hash VARCHAR(255) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1,
      created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, last_login_at BIGINT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS email_logs (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      request_type VARCHAR(30), request_id BIGINT UNSIGNED,
      template VARCHAR(80) NOT NULL, recipient VARCHAR(180) NOT NULL,
      subject VARCHAR(255) NOT NULL, status VARCHAR(20) NOT NULL,
      message_id VARCHAR(255), error_message TEXT, created_at BIGINT NOT NULL,
      INDEX email_logs_project_idx (request_type, request_id, created_at),
      INDEX email_logs_status_idx (status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    `CREATE TABLE IF NOT EXISTS generator_fonts (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      slug VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL,
      css_family VARCHAR(180) NOT NULL, source_type VARCHAR(20) NOT NULL DEFAULT 'builtin',
      storage_key VARCHAR(500), original_file_name VARCHAR(255), content_type VARCHAR(100),
      sign_enabled TINYINT(1) NOT NULL DEFAULT 0, neon_enabled TINYINT(1) NOT NULL DEFAULT 0,
      handwritten TINYINT(1) NOT NULL DEFAULT 0, enabled TINYINT(1) NOT NULL DEFAULT 1,
      factor_bp INT NOT NULL DEFAULT 10000, position INT NOT NULL DEFAULT 0,
      created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL,
      INDEX generator_fonts_visibility_idx (enabled, sign_enabled, neon_enabled, position)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
  ];

  await env.DB.batch(ddl.map((sql) => env.DB.prepare(sql)));
  await env.DB.prepare("ALTER TABLE commercial_quotes ADD COLUMN IF NOT EXISTS production_file_id BIGINT UNSIGNED NULL AFTER responded_at").run();
}