/**
 * `oke db` CLI wrappers.
 */

import { describe, expect, test } from "bun:test";
import { mkdir, mkdtemp, readdir, readFile, rename, rm, writeFile } from "node:fs/promises";
import { tmpdir } from "node:os";
import { join, resolve } from "node:path";
import { connectPglite } from "../drivers/pglite.ts";
import { createFx } from "../kernel/fx.ts";
import { defineSeed, type SeedFn } from "../elements/store/seed.ts";
import { EXIT_OK, EXIT_RUNTIME, EXIT_USAGE } from "./exit.ts";
import {
  createHintsForRenameOrCreate,
  dbCli,
  emitAbstractSchemaPrestep,
  executeSeedDef,
  formatKitError,
  runSeedFns,
  runDb,
  runGenerate,
  runPush,
  runSeed,
} from "./db.ts";

/** Repo public entry — absolute import so temp apps need no install. */
const OKE_INDEX = resolve(import.meta.dir, "../index.ts");
const CONFIG_MOD = resolve(import.meta.dir, "../config/index.ts");
const DB_MOD = resolve(import.meta.dir, "./db.ts");

/**
 * Run `oke db generate|migrate` in a fresh Bun process.
 *
 * drizzle-kit's in-process `generate` caches the schema module — a second
 * generate in the same process after rewriting the schema file returns
 * `no_changes`. Real CLI invocations are one process each; the adversarial
 * multi-migration test must match that.
 *
 * Temp projects live under {@link import.meta.dir} so `bunx drizzle-kit`
 * (migrate's default) walks up to the repo `node_modules`.
 */
async function runDbFresh(
  sub: "generate" | "migrate",
  cwd: string,
): Promise<{ readonly code: number; readonly out: string }> {
  const proc = Bun.spawn(
    [
      "bun",
      "-e",
      `import { runDb } from ${JSON.stringify(DB_MOD)};
const cwd = ${JSON.stringify(cwd)};
const prev = process.cwd();
process.chdir(cwd);
try {
  process.exit(await runDb(${JSON.stringify(sub)}, { cwd, skipEmit: true }));
} finally {
  process.chdir(prev);
}
`,
    ],
    { cwd, stdout: "pipe", stderr: "pipe", env: process.env as Record<string, string> },
  );
  const [stdout, stderr, code] = await Promise.all([
    new Response(proc.stdout).text(),
    new Response(proc.stderr).text(),
    proc.exited,
  ]);
  return { code, out: `${stdout}${stderr}` };
}

async function migrationFolders(drizzleDir: string): Promise<string[]> {
  return (await readdir(drizzleDir, { withFileTypes: true }))
    .filter((d) => d.isDirectory())
    .map((d) => d.name)
    .sort();
}

describe("oke db", () => {
  test("help lists subcommands", async () => {
    const lines: string[] = [];
    const orig = console.log;
    console.log = (msg?: unknown) => {
      lines.push(String(msg ?? ""));
    };
    try {
      const code = await dbCli(["--help"]);
      expect(code).toBe(EXIT_OK);
      expect(lines.join("\n")).toContain("push");
      expect(lines.join("\n")).toContain("generate");
      expect(lines.join("\n")).toContain("migrate");
      expect(lines.join("\n")).toContain("seed");
      expect(lines.join("\n")).toContain("oke schema generate");
    } finally {
      console.log = orig;
    }
  });

  test("push reports ok / no_changes via injectable", async () => {
    const out: string[] = [];
    const code = await runPush("/tmp/drizzle.config.ts", (t) => out.push(t), {
      pushFn: async () => ({ status: "no_changes" }),
      skipEmit: true,
    });
    expect(code).toBe(EXIT_OK);
    expect(out.join("")).toContain("no_changes");
  });

  test("generate surfaces migration_path", async () => {
    const out: string[] = [];
    const code = await runGenerate("/tmp/drizzle.config.ts", (t) => out.push(t), {
      generateFn: async () => ({
        status: "ok",
        migration_path: "/tmp/drizzle/0001_init.sql",
      }),
      skipEmit: true,
    });
    expect(code).toBe(EXIT_OK);
    expect(out.join("")).toContain("0001_init.sql");
  });

  test("formatKitError joins code message and sql", () => {
    expect(formatKitError({ code: "query_error", message: "boom", sql: "CREATE TABLE t" })).toBe(
      " — query_error — boom — CREATE TABLE t",
    );
    expect(formatKitError(undefined)).toBe("");
  });

  test("missing_hints exits runtime", async () => {
    const out: string[] = [];
    const code = await runDb("push", {
      pushFn: async () => ({
        status: "missing_hints",
        unresolved: [{ sql: "DROP TABLE leftover" }],
      }),
      write: (t) => out.push(t),
      skipEmit: true,
    });
    expect(code).toBe(EXIT_RUNTIME);
    expect(out.join("")).toContain("missing_hints");
    expect(out.join("")).toContain("DROP TABLE leftover");
  });

  test("createHintsForRenameOrCreate maps rename_or_create only", () => {
    expect(
      createHintsForRenameOrCreate([
        { type: "rename_or_create", kind: "table", entity: ["public", "activity"] },
        {
          type: "confirm_data_loss",
          kind: "table",
          entity: ["public", "old"],
          reason: "non_empty",
        },
      ]),
    ).toEqual([{ type: "create", kind: "table", entity: ["public", "activity"] }]);
  });

  test("push retries rename_or_create as create", async () => {
    const hints: unknown[] = [];
    const out: string[] = [];
    const code = await runPush("/tmp/drizzle.config.ts", (t) => out.push(t), {
      skipEmit: true,
      pushFn: async (opts) => {
        hints.push(opts.hints);
        if (!opts.hints) {
          return {
            status: "missing_hints",
            unresolved: [{ type: "rename_or_create", kind: "table", entity: ["public", "notes"] }],
          };
        }
        return { status: "ok" };
      },
    });
    expect(code).toBe(EXIT_OK);
    expect(hints[1]).toEqual([{ type: "create", kind: "table", entity: ["public", "notes"] }]);
    expect(out.join("")).toContain("creating 1 new object");
    expect(out.join("")).toContain("oke db push: ok");
  });

  test("push does not retry confirm_data_loss-only missing_hints", async () => {
    let calls = 0;
    const code = await runPush("/tmp/drizzle.config.ts", () => {}, {
      skipEmit: true,
      pushFn: async () => {
        calls += 1;
        return {
          status: "missing_hints",
          unresolved: [{ type: "confirm_data_loss", kind: "table", entity: ["public", "notes"] }],
        };
      },
    });
    expect(code).toBe(EXIT_RUNTIME);
    expect(calls).toBe(1);
  });

  test("explain does not auto-create on missing_hints", async () => {
    let calls = 0;
    const code = await runPush("/tmp/drizzle.config.ts", () => {}, {
      skipEmit: true,
      explain: true,
      pushFn: async () => {
        calls += 1;
        return {
          status: "missing_hints",
          unresolved: [{ type: "rename_or_create", kind: "table", entity: ["public", "notes"] }],
        };
      },
    });
    expect(code).toBe(EXIT_RUNTIME);
    expect(calls).toBe(1);
  });

  test("resolveDrizzleConfigPath uses db.config from oke.config", async () => {
    const dir = await mkdtemp(join(tmpdir(), "oke-db-"));
    await writeFile(
      join(dir, "oke.config.ts"),
      `import { defineConfig } from ${JSON.stringify(CONFIG_MOD)};
export default defineConfig({ db: { config: "custom.drizzle.ts" } });
`,
    );
    const { resolveDrizzleConfigPath } = await import("./db.ts");
    const path = await resolveDrizzleConfigPath(dir);
    expect(path.endsWith("custom.drizzle.ts")).toBe(true);
  });
});

describe("oke db emit — live plugged plugin tables", () => {
  /**
   * End-to-end: real oke().plug() app + schema.decl + runDb("push") must write
   * the plugin's field.* table into schema.generated.ts. Not a unit merge call.
   */
  test("push emits plugin field.* table into schema.generated.ts", async () => {
    const dir = await mkdtemp(join(tmpdir(), "oke-db-plugin-emit-"));
    await mkdir(join(dir, "src"), { recursive: true });

    await writeFile(
      join(dir, "oke.config.ts"),
      `import { defineConfig } from ${JSON.stringify(CONFIG_MOD)};
export default defineConfig({
  drivers: { store: { sql: { dev: "postgres", test: "pglite", prod: "postgres" } } },
  db: { declare: "src/schema.decl.ts", generated: "src/schema.generated.ts" },
});
`,
    );

    await writeFile(
      join(dir, "src", "schema.decl.ts"),
      `import { store, field, id } from ${JSON.stringify(OKE_INDEX)};
export const notes = store.schema.table("notes", {
  id: field.text().primaryKey().defaultFn(id),
  title: field.text().notNull(),
});
`,
    );

    await writeFile(
      join(dir, "src", "app.ts"),
      `import { oke, plugin, field, id } from ${JSON.stringify(OKE_INDEX)};

const audit = plugin("audit", { version: "1.0.0" }).table("audit_events", {
  id: field.text().primaryKey().defaultFn(id),
  actorId: field.text().notNull().pii(),
  action: field.text().notNull(),
});

export const app = oke({ name: "plugin-emit" }).plug(audit);
`,
    );

    await writeFile(
      join(dir, "drizzle.config.ts"),
      `import { defineConfig } from "drizzle-kit";
export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.generated.ts",
  out: "./drizzle",
  dbCredentials: { url: "postgres://127.0.0.1:5432/oke" },
});
`,
    );

    const out: string[] = [];
    const code = await runDb("push", {
      cwd: dir,
      write: (t) => out.push(t),
      pushFn: async () => ({ status: "no_changes" }),
      // Real emit path — do not skip; kit is mocked after emit.
    });

    expect(code).toBe(EXIT_OK);
    expect(out.join("")).toMatch(/emitted.*schema\.generated\.ts/);

    const generated = await readFile(join(dir, "src", "schema.generated.ts"), "utf8");
    expect(generated).toContain('pgTable("notes"');
    expect(generated).toContain('pgTable("audit_events"');
    expect(generated).toContain('text("actor_id")');
    expect(generated).toContain('text("action")');
    expect(generated).toContain(".notNull()");
  });

  test("emitAbstractSchemaPrestep loads plugin tables from app entry alone", async () => {
    const dir = await mkdtemp(join(tmpdir(), "oke-db-plugin-only-"));
    await mkdir(join(dir, "src"), { recursive: true });

    await writeFile(
      join(dir, "oke.config.ts"),
      `import { defineConfig } from ${JSON.stringify(CONFIG_MOD)};
export default defineConfig({
  drivers: { store: { sql: { test: "pglite" } } },
});
`,
    );

    await writeFile(
      join(dir, "src", "app.ts"),
      `import { oke, plugin, field } from ${JSON.stringify(OKE_INDEX)};
export const app = oke({ name: "plugin-only" }).plug(
  plugin("metrics", { version: "1.0.0" }).table("metrics_daily", {
    day: field.text().primaryKey(),
    hits: field.integer().notNull().default(0),
  }),
);
`,
    );

    const { loadOkeConfig } = await import("./load-config.ts");
    const loaded = await loadOkeConfig(dir);
    const emitted = await emitAbstractSchemaPrestep(dir, loaded.config, () => {}, "test");
    expect(emitted).toBe(true);

    const generated = await readFile(join(dir, "src", "db", "drizzle", "metrics_daily.ts"), "utf8");
    expect(generated).toContain('pgTable("metrics_daily"');
    // Postgres dialect maps abstract integer → bigint (32-bit INTEGER is too narrow).
    expect(generated).toContain('bigint("hits", { mode: "number" })');
  });
});

describe("oke db multi-migration catch-up", () => {
  /**
   * Real drizzle-kit generate/migrate through `oke db` — proves multi-file
   * versioned migrations and that re-migrate / environment lag only apply
   * previously-unapplied files (drizzle-kit `drizzle.__drizzle_migrations`
   * history). The CLI wrapper must not interfere with that bookkeeping.
   */
  test("second migrate skips applied files; lag DB catches up with only the new one", async () => {
    const dir = await mkdtemp(join(import.meta.dir, ".tmp-db-mig-"));
    const pgdata = join(dir, "app.pglite");
    async function migrationNames(): Promise<string[]> {
      const conn = await connectPglite({ url: pgdata });
      try {
        const rows = await conn.query(`SELECT name FROM drizzle.__drizzle_migrations ORDER BY id`);
        return rows.map((r) => String(r.name));
      } finally {
        await conn.close();
      }
    }
    async function noteColumns(): Promise<string[]> {
      const conn = await connectPglite({ url: pgdata });
      try {
        const rows = await conn.query(
          `SELECT column_name AS name FROM information_schema.columns
           WHERE table_schema = 'public' AND table_name = 'notes'`,
        );
        return rows.map((r) => String(r.name));
      } finally {
        await conn.close();
      }
    }
    async function notesTableExists(): Promise<boolean> {
      const conn = await connectPglite({ url: pgdata });
      try {
        const rows = await conn.query(`SELECT to_regclass('public.notes') AS reg`);
        return rows[0]?.reg != null;
      } finally {
        await conn.close();
      }
    }

    try {
      await mkdir(join(dir, "src"), { recursive: true });
      await writeFile(
        join(dir, "src", "schema.ts"),
        `import { pgTable, text } from "drizzle-orm/pg-core";
export const notes = pgTable("notes", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
});
`,
      );
      await writeFile(
        join(dir, "drizzle.config.ts"),
        `import { defineConfig } from "drizzle-kit";
export default defineConfig({
  dialect: "postgresql",
  driver: "pglite",
  schema: "./src/schema.ts",
  out: "./drizzle",
  dbCredentials: { url: ${JSON.stringify(pgdata)} },
});
`,
      );

      const gen1 = await runDbFresh("generate", dir);
      expect(gen1.code).toBe(EXIT_OK);
      expect(gen1.out).toMatch(/oke db generate: wrote /);
      const migsAfterFirst = await migrationFolders(join(dir, "drizzle"));
      expect(migsAfterFirst.length).toBe(1);
      const initialMig = migsAfterFirst[0];
      expect(initialMig).toBeDefined();
      if (initialMig === undefined) throw new Error("expected initial migration folder");

      const mig1 = await runDbFresh("migrate", dir);
      expect(mig1.code).toBe(EXIT_OK);
      expect(mig1.out).toContain("oke db migrate: applied");

      expect(await notesTableExists()).toBe(true);
      expect(await migrationNames()).toEqual([initialMig]);

      await writeFile(
        join(dir, "src", "schema.ts"),
        `import { integer, pgTable, text } from "drizzle-orm/pg-core";
export const notes = pgTable("notes", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  pinned: integer("pinned").notNull().default(0),
});
`,
      );
      // Distinct timestamp folder names (drizzle-kit uses YYYYMMDDHHmmss).
      await Bun.sleep(1100);

      const gen2 = await runDbFresh("generate", dir);
      expect(gen2.code).toBe(EXIT_OK);
      expect(gen2.out).toMatch(/oke db generate: wrote /);
      const migs = await migrationFolders(join(dir, "drizzle"));
      expect(migs.length).toBe(2);
      const firstMig = migs[0];
      const secondMig = migs[1];
      expect(firstMig).toBeDefined();
      expect(secondMig).toBeDefined();
      if (firstMig === undefined || secondMig === undefined) {
        throw new Error("expected two migration folders");
      }
      expect(firstMig).toBe(initialMig);

      const mig2 = await runDbFresh("migrate", dir);
      expect(mig2.code).toBe(EXIT_OK);

      expect(await migrationNames()).toEqual([firstMig, secondMig]);
      expect(await noteColumns()).toContain("pinned");

      // Idempotent re-migrate: history unchanged, no error.
      const migNoop = await runDbFresh("migrate", dir);
      expect(migNoop.code).toBe(EXIT_OK);
      expect(await migrationNames()).toEqual([firstMig, secondMig]);

      // Staging/prod lag: fresh DB with only the first migration applied,
      // then both files present — catch up applies only the second.
      await rm(pgdata, { recursive: true, force: true });
      const stash = join(dir, "_stash_second");
      await rm(stash, { recursive: true, force: true });
      await rename(join(dir, "drizzle", secondMig), stash);

      const lagFirst = await runDbFresh("migrate", dir);
      expect(lagFirst.code).toBe(EXIT_OK);
      expect(await migrationNames()).toEqual([firstMig]);
      expect(await noteColumns()).not.toContain("pinned");

      await rename(stash, join(dir, "drizzle", secondMig));
      const lagCatchUp = await runDbFresh("migrate", dir);
      expect(lagCatchUp.code).toBe(EXIT_OK);
      expect(await migrationNames()).toEqual([firstMig, secondMig]);
      expect(await noteColumns()).toContain("pinned");
    } finally {
      await rm(dir, { recursive: true, force: true });
    }
  }, 120_000);
});

describe("oke db seed", () => {
  function trackingDef(order: string[]) {
    const a: SeedFn = async function a() {
      order.push("a");
    };
    const b: SeedFn = async function b() {
      order.push("b");
    };
    const dev: SeedFn = async function seedDev() {
      order.push("dev");
    };
    const prod: SeedFn = async function seedProd() {
      order.push("prod");
    };
    return defineSeed({
      essential: [a, b],
      dev,
      prod,
    });
  }

  async function seedWith(
    env: "dev" | "test" | "prod",
    extras: Parameters<typeof runSeed>[0] = {},
  ) {
    const order: string[] = [];
    const out: string[] = [];
    const code = await runSeed({
      env,
      seedDef: trackingDef(order),
      createFx: async () => ({
        fx: createFx({ flow: "oke.db.seed.test" }),
        stop: async () => {},
      }),
      write: (t) => out.push(t),
      force: true,
      ...extras,
    });
    return { code, order, out: out.join("") };
  }

  test("essential array order is execution order", async () => {
    const { code, order, out } = await seedWith("dev");
    expect(code).toBe(EXIT_OK);
    expect(order.slice(0, 2)).toEqual(["a", "b"]);
    expect(out).toContain("oke db seed: essential a");
    expect(out).toContain("oke db seed: ok");
  });

  test("dev runs essential + dev, never prod", async () => {
    const { order } = await seedWith("dev");
    expect(order).toEqual(["a", "b", "dev"]);
  });

  test("prod runs essential + prod, never dev", async () => {
    const { order } = await seedWith("prod");
    expect(order).toEqual(["a", "b", "prod"]);
  });

  test("test runs essential only", async () => {
    const { order, out } = await seedWith("test");
    expect(order).toEqual(["a", "b"]);
    expect(out).toContain("essential only");
  });

  test("prod without --force requires confirmEnv", async () => {
    const out: string[] = [];
    let asked = false;
    const code = await runSeed({
      env: "prod",
      force: false,
      stdinIsTTY: true,
      seedDef: defineSeed({ essential: async () => {} }),
      createFx: async () => ({
        fx: createFx({ flow: "oke.db.seed.test" }),
        stop: async () => {},
      }),
      confirmEnv: async () => {
        asked = true;
        return false;
      },
      write: (t) => out.push(t),
    });
    expect(asked).toBe(true);
    expect(code).toBe(EXIT_USAGE);
    expect(out.join("")).toContain("cancelled");
  });

  test("prod with --force skips confirm", async () => {
    let asked = false;
    const { code } = await seedWith("prod", {
      force: true,
      confirmEnv: async () => {
        asked = true;
        return false;
      },
    });
    expect(asked).toBe(false);
    expect(code).toBe(EXIT_OK);
  });

  test("dev confirm proceeds when confirmEnv returns true", async () => {
    const out: string[] = [];
    const order: string[] = [];
    const code = await runSeed({
      env: "dev",
      force: false,
      stdinIsTTY: true,
      seedDef: trackingDef(order),
      createFx: async () => ({
        fx: createFx({ flow: "oke.db.seed.test" }),
        stop: async () => {},
      }),
      confirmEnv: async (env, target) => {
        expect(env).toBe("dev");
        expect(target.length).toBeGreaterThan(0);
        return true;
      },
      write: (t) => out.push(t),
    });
    expect(code).toBe(EXIT_OK);
    expect(order).toEqual(["a", "b", "dev"]);
    expect(out.join("")).toContain("oke db seed:");
  });

  test("runSeedFns ignores void index upserts when tallying", async () => {
    const out: string[] = [];
    const fx = {
      store: (ref: unknown) =>
        ref === "sql"
          ? { upsert: async () => ({ status: "changed" as const }) }
          : { upsert: async () => undefined },
    } as unknown as ReturnType<typeof createFx>;
    await runSeedFns(
      "dev",
      [
        async function volume(seedFx) {
          const sql = seedFx.store("sql" as never) as unknown as {
            upsert: () => Promise<unknown>;
          };
          const index = seedFx.store("index" as never) as unknown as {
            upsert: () => Promise<unknown>;
          };
          await sql.upsert();
          await index.upsert();
        },
      ],
      fx,
      (t) => out.push(t),
    );
    expect(out.join("")).toContain("dev volume — upserted 0 · changed 1 · already-existed 0");
  });

  test("runSeedFns tallies kv.set as keys", async () => {
    const out: string[] = [];
    const fx = {
      store: () => ({
        set: async () => undefined,
      }),
    } as unknown as ReturnType<typeof createFx>;
    await runSeedFns(
      "dev",
      [
        async function volume(seedFx) {
          const kv = seedFx.store("kv" as never) as unknown as {
            set: (key: string, value: unknown) => Promise<void>;
          };
          await kv.set("ENG-12", { title: "SSO" });
          await kv.set("ENG-14", { title: "Polish" });
        },
      ],
      fx,
      (t) => out.push(t),
    );
    expect(out.join("")).toContain(
      "dev volume — upserted 0 · changed 0 · already-existed 0 · keys 2",
    );
  });

  test("executeSeedDef is the category source of truth", async () => {
    const order: string[] = [];
    const out: string[] = [];
    const fx = createFx({ flow: "x" });
    await executeSeedDef(trackingDef(order), "prod", fx, (t) => out.push(t));
    expect(order).toEqual(["a", "b", "prod"]);
  });
});