import { env } from "cloudflare:workers";
import migrationSql from "../drizzle/0000_condemned_legion.sql?raw";
import claimMigrationSql from "../drizzle/0001_mushy_vance_astro.sql?raw";
import claimOwnershipMigrationSql from "../drizzle/0002_wild_ravenous.sql?raw";
import provisioningMigrationSql from "../drizzle/0003_medical_rocket_racer.sql?raw";
import provisioningFenceMigrationSql from "../drizzle/0004_loud_prodigy.sql?raw";
import feedbackMigrationSql from "../drizzle/0005_pale_corsair.sql?raw";
import serviceIncidentMigrationSql from "../drizzle/0006_clever_shocker.sql?raw";
import accountLifecycleMigrationSql from "../drizzle/0007_zippy_nomad.sql?raw";
import provisionerLivenessMigrationSql from "../drizzle/0008_far_justice.sql?raw";
import maintenanceJobMigrationSql from "../drizzle/0009_flat_robbie_robertson.sql?raw";
import betaAccessRequestMigrationSql from "../drizzle/0010_windy_toxin.sql?raw";
import betaAccessRequestTimeIndexMigrationSql from "../drizzle/0011_next_thunderball.sql?raw";
import sharedRelayControlPlaneMigrationSql from "../drizzle/0012_shared_relay_control_plane.sql?raw";
import relayMigrationFencingMigrationSql from "../drizzle/0013_relay_migration_fencing.sql?raw";
import relayTenantPurgeMigrationSql from "../drizzle/0014_relay_tenant_purge.sql?raw";
import phoneHandoffIdempotencyMigrationSql from "../drizzle/0015_phone_handoff_idempotency.sql?raw";
import phoneHandoffActivationMigrationSql from "../drizzle/0016_phone_handoff_activation.sql?raw";
import provisioningInvariantMigrationSql from "../drizzle/9000_provisioning_invariants.sql?raw";
import provisioningSlugMigrationSql from "../drizzle/9001_provisioning_slug_backfill.sql?raw";
import readyCredentialReconciliationMigrationSql from "../drizzle/9002_ready_credential_reconciliation.sql?raw";
import { LAUNCH_GATE_SEEDS } from "./launch-gates.ts";

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

function getDatabase(): D1Database {
  if (!env.DB) {
    throw new Error("Cloudflare D1 binding `DB` is unavailable.");
  }
  return env.DB;
}

function migrationStatements(sql: string): string[] {
  return sql
    .split("--> statement-breakpoint")
    .map((statement) => statement.trim())
    .filter(Boolean);
}

async function applyTrackedMigration(
  db: D1Database,
  id: string,
  sql: string,
): Promise<void> {
  const applied = await db
    .prepare("SELECT id FROM cloud_schema_migrations WHERE id = ?")
    .bind(id)
    .first<{ id: string }>();
  if (applied) return;

  const now = new Date().toISOString();
  try {
    await db.batch([
      ...migrationStatements(sql).map((statement) => db.prepare(statement)),
      db
        .prepare(
          `INSERT INTO cloud_schema_migrations (id, applied_at)
           VALUES (?, ?)`,
        )
        .bind(id, now),
    ]);
  } catch (error) {
    const racedMigration = await db
      .prepare("SELECT id FROM cloud_schema_migrations WHERE id = ?")
      .bind(id)
      .first<{ id: string }>();
    if (!racedMigration) throw error;
  }
}

async function migrateSchema(db: D1Database): Promise<void> {
  let accountsTable = await db
    .prepare(
      "SELECT name FROM sqlite_schema WHERE type = 'table' AND name = 'accounts'",
    )
    .first<{ name: string }>();

  if (!accountsTable) {
    try {
      await db.batch(
        migrationStatements(migrationSql).map((statement) => db.prepare(statement)),
      );
    } catch (error) {
      accountsTable = await db
        .prepare(
          "SELECT name FROM sqlite_schema WHERE type = 'table' AND name = 'accounts'",
        )
        .first<{ name: string }>();
      if (!accountsTable) throw error;
    }
  }

  const ledgerTable = await db
    .prepare(
      "SELECT name FROM sqlite_schema WHERE type = 'table' AND name = 'cloud_schema_migrations'",
    )
    .first<{ name: string }>();
  const claimMigration = ledgerTable
    ? await db
        .prepare("SELECT id FROM cloud_schema_migrations WHERE id = ?")
        .bind("0001_mushy_vance_astro")
        .first<{ id: string }>()
    : null;

  if (!claimMigration) {
    const statements = migrationStatements(claimMigrationSql);
    const applicableStatements = ledgerTable ? statements.slice(1) : statements;
    const now = new Date().toISOString();
    try {
      await db.batch([
        ...applicableStatements.map((statement) => db.prepare(statement)),
        db
          .prepare(
            `INSERT OR IGNORE INTO cloud_schema_migrations (id, applied_at)
             VALUES ('0000_condemned_legion', ?), ('0001_mushy_vance_astro', ?)`,
          )
          .bind(now, now),
      ]);
    } catch (error) {
      const racedMigration = await db
        .prepare("SELECT id FROM cloud_schema_migrations WHERE id = ?")
        .bind("0001_mushy_vance_astro")
        .first<{ id: string }>();
      if (!racedMigration) throw error;
    }
  }

  await applyTrackedMigration(
    db,
    "0002_wild_ravenous",
    claimOwnershipMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0003_medical_rocket_racer",
    provisioningMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0004_loud_prodigy",
    provisioningFenceMigrationSql,
  );
  await applyTrackedMigration(db, "0005_pale_corsair", feedbackMigrationSql);
  await applyTrackedMigration(
    db,
    "0006_clever_shocker",
    serviceIncidentMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0007_zippy_nomad",
    accountLifecycleMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0008_far_justice",
    provisionerLivenessMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0009_flat_robbie_robertson",
    maintenanceJobMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0010_windy_toxin",
    betaAccessRequestMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0011_next_thunderball",
    betaAccessRequestTimeIndexMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "9000_provisioning_invariants",
    provisioningInvariantMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "9001_provisioning_slug_backfill",
    provisioningSlugMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "9002_ready_credential_reconciliation",
    readyCredentialReconciliationMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0012_shared_relay_control_plane",
    sharedRelayControlPlaneMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0013_relay_migration_fencing",
    relayMigrationFencingMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0014_relay_tenant_purge",
    relayTenantPurgeMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0015_phone_handoff_idempotency",
    phoneHandoffIdempotencyMigrationSql,
  );
  await applyTrackedMigration(
    db,
    "0016_phone_handoff_activation",
    phoneHandoffActivationMigrationSql,
  );
  await db.prepare("PRAGMA optimize").run();
}

async function seedCatalogAndGates(db: D1Database): Promise<void> {
  const now = new Date().toISOString();
  const gateStatements = LAUNCH_GATE_SEEDS.map(([key, priority, category, title]) =>
    db
      .prepare(
        `INSERT OR IGNORE INTO launch_gates
          (key, priority, category, title, status, notes, updated_at)
         VALUES (?, ?, ?, ?, 'blocked', '', ?)`,
      )
      .bind(key, priority, category, title, now),
  );
  const gateReconciliationStatements = LAUNCH_GATE_SEEDS.map(
    ([key, priority, category, title]) =>
      db
        .prepare(
          `UPDATE launch_gates
           SET priority = ?, category = ?, title = ?, updated_at = ?
           WHERE key = ?
             AND (priority <> ? OR category <> ? OR title <> ?)`,
        )
        .bind(priority, category, title, now, key, priority, category, title),
  );
  const gateReconciliationAuditStatements = LAUNCH_GATE_SEEDS.map(
    ([key, priority, category, title]) =>
      db
        .prepare(
          `INSERT OR IGNORE INTO audit_events
            (id, actor_id, action, target_type, target_id, reason,
             before_json, after_json, correlation_id, created_at)
           SELECT 'audit_reclassify_' || key || '_free_beta_v1',
                  'system:bootstrap', 'launch_gate.reclassified',
                  'launch_gate', key,
                  '免费公测门禁与未来收费门禁分离',
                  json_object('priority', priority, 'category', category, 'title', title),
                  json_object('priority', ?, 'category', ?, 'title', ?),
                  'corr_reclassify_' || key || '_free_beta_v1', ?
           FROM launch_gates
           WHERE key = ?
             AND (priority <> ? OR category <> ? OR title <> ?)`,
        )
        .bind(priority, category, title, now, key, priority, category, title),
  );

  await db.batch([
    db
      .prepare(
        `INSERT OR IGNORE INTO price_versions
          (id, product_code, billing_period, unit_slots, amount_minor, currency,
           tax_mode, quote_ttl_seconds, status, effective_from, created_by)
         VALUES (?, 'host_slot', 'month', 1, 1000, 'CNY', 'undecided', 900,
                 'retired', ?, 'system:seed')`,
      )
      .bind("price_host_month_v1", now),
    db
      .prepare(
        `INSERT OR IGNORE INTO price_versions
          (id, product_code, billing_period, unit_slots, amount_minor, currency,
           tax_mode, quote_ttl_seconds, status, effective_from, created_by)
         VALUES (?, 'host_slot', 'year', 1, 10000, 'CNY', 'undecided', 900,
                 'retired', ?, 'system:seed')`,
      )
      .bind("price_host_year_v1", now),
    db
      .prepare(
        `INSERT OR IGNORE INTO beta_programs
          (id, state, capacity_slots, starts_at, ends_at, grace_days, created_by)
         VALUES (?, 'active', NULL, ?, NULL, 0, 'system:seed')`,
      )
      .bind("beta_public_v1", now),
    ...gateStatements,
    ...gateReconciliationAuditStatements,
    ...gateReconciliationStatements,
    db
      .prepare(
        `INSERT OR IGNORE INTO audit_events
          (id, actor_id, action, target_type, target_id, reason,
           before_json, after_json, correlation_id, created_at)
         SELECT 'audit_defer_paid_catalog_v1', 'system:bootstrap',
                'price_catalog.retired', 'price_catalog', 'host_slot',
                '免费公测阶段暂缓收费决策，保留历史价格但撤销发布状态',
                '{"status":"published"}', '{"status":"retired"}',
                'corr_defer_paid_catalog_v1', ?
         WHERE EXISTS (
           SELECT 1 FROM price_versions
           WHERE product_code = 'host_slot' AND status = 'published'
         )`,
      )
      .bind(now),
    db.prepare(
      `UPDATE price_versions
       SET status = 'retired'
       WHERE product_code = 'host_slot' AND status = 'published'`,
    ),
    db
      .prepare(
        `INSERT OR IGNORE INTO audit_events
          (id, actor_id, action, target_type, target_id, reason,
           before_json, after_json, correlation_id, created_at)
         SELECT 'audit_repair_p0_' || key, 'system:bootstrap',
                'launch_gate.repaired', 'launch_gate', key,
                '旧版 P0 状态缺少有效证据或使用了不适用，启动时恢复为阻止',
                '{"status":"legacy_invalid"}', '{"status":"blocked"}',
                'corr_repair_p0_' || key, ?
         FROM launch_gates
         WHERE priority = 'P0' AND (
           status = 'not_applicable'
           OR (status = 'passed' AND (
             trim(COALESCE(owner, '')) = ''
             OR trim(COALESCE(notes, '')) = ''
             OR evidence_url IS NULL
             OR evidence_url NOT LIKE 'https://%'
           ))
         )`,
      )
      .bind(now),
    db
      .prepare(
        `UPDATE launch_gates
         SET status = 'blocked', reviewed_at = ?, updated_at = ?
         WHERE priority = 'P0' AND (
           status = 'not_applicable'
           OR (status = 'passed' AND (
             trim(COALESCE(owner, '')) = ''
             OR trim(COALESCE(notes, '')) = ''
             OR evidence_url IS NULL
             OR evidence_url NOT LIKE 'https://%'
           ))
         )`,
      )
      .bind(now, now),
  ]);
}

export async function ensureDatabase(): Promise<void> {
  if (!schemaPromise) {
    schemaPromise = (async () => {
      const db = getDatabase();
      await migrateSchema(db);
      await seedCatalogAndGates(db);
    })().catch((error) => {
      schemaPromise = null;
      throw error;
    });
  }

  await schemaPromise;
}

export function getD1(): D1Database {
  return getDatabase();
}