#!/usr/bin/env node

// Generates Kysely database types from the SQLite schema.
import { createHash } from "node:crypto";
import fs from "node:fs";
import path from "node:path";
import process from "node:process";
import type { DatabaseSync } from "node:sqlite";
import { fileURLToPath } from "node:url";
import { isDirectRunUrl } from "./lib/direct-run.mjs";

const SCHEMAS = ["openclaw-state", "openclaw-agent"];
const REPO_ROOT = fileURLToPath(new URL("../", import.meta.url));

function toInterfaceName(tableName: string): string {
  return tableName
    .split("_")
    .map((part) => `${part.slice(0, 1).toUpperCase()}${part.slice(1)}`)
    .join("");
}

function columnBaseType(columnTypeLocal: string): string {
  const normalized = columnTypeLocal.toUpperCase();
  if (normalized.includes("BLOB")) {
    return "Uint8Array";
  }
  if (
    normalized.includes("INT") ||
    normalized.includes("REAL") ||
    normalized.includes("FLOA") ||
    normalized.includes("DOUB") ||
    normalized.includes("NUM") ||
    normalized.includes("DEC")
  ) {
    return "number";
  }
  return "string";
}

function columnType(column: Record<string, unknown>, primaryKeyColumnCount: number): string {
  const sqliteType = typeof column.type === "string" ? column.type : "";
  const baseType = columnBaseType(sqliteType);
  const generatedPrimaryKey =
    primaryKeyColumnCount === 1 &&
    Number(column.pk) > 0 &&
    sqliteType.toUpperCase().includes("INT");
  const generated = column.dflt_value != null || generatedPrimaryKey;
  const nullable = Number(column.notnull) !== 1 && !generatedPrimaryKey;
  const valueType = nullable ? `${baseType} | null` : baseType;
  return generated ? `Generated<${valueType}>` : valueType;
}

function quoteSqliteIdentifier(identifier: string): string {
  return `"${identifier.replaceAll('"', '""')}"`;
}

function generateTypes(db: DatabaseSync): string {
  const tables = db
    .prepare(
      "SELECT name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name;",
    )
    .all()
    .map((row) => String(row.name));
  const lines = [
    "/**",
    " * This file was generated by kysely-codegen.",
    " * Please do not edit it manually.",
    " */",
    "",
    'import type { ColumnType } from "kysely";',
    "",
    "export type Generated<T> =",
    "  T extends ColumnType<infer S, infer I, infer U>",
    "    ? ColumnType<S, I | undefined, U>",
    "    : ColumnType<T, T | undefined, T>;",
    "",
  ];

  const interfaces: Array<{ interfaceName: string; table: string }> = [];
  for (const table of tables) {
    const interfaceName = toInterfaceName(table);
    interfaces.push({ interfaceName, table });
    lines.push(`export interface ${interfaceName} {`);
    const columns = db
      .prepare(`PRAGMA table_xinfo(${quoteSqliteIdentifier(table)});`)
      .all()
      .filter((column) => Number(column.hidden) === 0)
      .toSorted((left, right) => String(left.name).localeCompare(String(right.name), "en"));
    const primaryKeyColumnCount = columns.filter((column) => Number(column.pk) > 0).length;
    for (const column of columns) {
      lines.push(`  ${String(column.name)}: ${columnType(column, primaryKeyColumnCount)};`);
    }
    lines.push("}", "");
  }

  lines.push("export interface DB {");
  for (const { interfaceName, table } of interfaces) {
    lines.push(`  ${table}: ${interfaceName};`);
  }
  lines.push("}", "");
  return lines.join("\n");
}

async function generateKyselyTypes(schemaSource: string): Promise<string> {
  const { DatabaseSync } = await import("node:sqlite");
  const db = new DatabaseSync(":memory:");
  try {
    db.exec(schemaSource);
    return generateTypes(db);
  } finally {
    db.close();
  }
}

export async function ensureKyselyTypes(
  cwd = REPO_ROOT,
  verify = false,
  options: { allowPartialCheckout?: boolean } = {},
): Promise<void> {
  const outputDir = path.join(cwd, ".artifacts/kysely");
  const stampFile = path.join(outputDir, "inputs.sha256");
  const inputs = SCHEMAS.map((name) => ({
    name,
    input: path.join(cwd, "src/state", `${name}-schema.sql`),
    output: path.join(outputDir, `${name}-db.generated.ts`),
  }));
  const available = inputs.filter((schema) => fs.existsSync(schema.input));
  const partial = !verify && options.allowPartialCheckout === true;
  if (partial) {
    // Sparse lint must not consume a previous projection whose source is now omitted.
    for (const schema of inputs.filter((input) => !available.includes(input))) {
      fs.rmSync(schema.output, { force: true });
    }
  }
  // Packaged installs and dependency-only Docker stages have no source schemas.
  if (!verify && available.length === 0) {
    if (partial) {
      fs.rmSync(stampFile, { force: true });
    }
    return;
  }
  const schemas = (partial ? available : inputs).map((schema) => ({
    name: schema.name,
    source: fs.readFileSync(schema.input, "utf8"),
    output: schema.output,
  }));
  const fingerprint = () => {
    const hash = createHash("sha256").update(fs.readFileSync(fileURLToPath(import.meta.url)));
    for (const schema of schemas) {
      hash.update(schema.name).update("\0").update(schema.source).update("\0");
      hash.update(fs.existsSync(schema.output) ? fs.readFileSync(schema.output) : "missing");
    }
    return hash.digest("hex");
  };
  if (!verify && fs.existsSync(stampFile) && fs.readFileSync(stampFile, "utf8") === fingerprint()) {
    return;
  }
  for (const schema of schemas) {
    const source = await generateKyselyTypes(schema.source);
    const existing = fs.existsSync(schema.output)
      ? fs.readFileSync(schema.output, "utf8")
      : undefined;
    if (verify) {
      if (existing !== source) {
        throw new Error(`${schema.output} is out of date. Run pnpm db:kysely:gen.`);
      }
    } else if (existing !== source) {
      fs.mkdirSync(outputDir, { recursive: true });
      const temporary = `${schema.output}.${process.pid}.tmp`;
      fs.writeFileSync(temporary, source);
      fs.renameSync(temporary, schema.output);
    }
  }
  if (!verify) {
    fs.writeFileSync(stampFile, fingerprint());
  }
}

if (isDirectRunUrl(process.argv[1], import.meta.url)) {
  await ensureKyselyTypes(
    REPO_ROOT,
    process.argv.includes("--verify") || process.argv.includes("--check"),
  );
}