import type { Database } from "bun:sqlite";
import { normalizeGitRemote } from "../utils/git-remote";

/**
 * One-time (idempotent) normalization pass over `project_roots.git_remote`.
 *
 * Discovery reads remotes through `getGitRemote`, which already normalizes,
 * but older ingesters and direct writes could persist raw values
 * (`https://….git`, `git@github.com:owner/repo`, mixed case, trailing slash).
 * Matching mostly survives because most comparators re-normalize at compare
 * time, but any that skip it — and the AI mapping prompt, which is fed the
 * stored value verbatim — see the raw form and group less accurately.
 *
 * This canonicalizes every stored remote to `github.com/owner/repo` form so
 * the persisted value is the comparable value. It runs alongside
 * `deduplicateProjectRootsByPath` in `initScribeDb`.
 *
 * Idempotent: rows already in canonical form are left untouched, so a second
 * pass rewrites nothing. Values that `normalizeGitRemote` cannot canonicalize
 * (it returns null only for empty/whitespace) are left as-is rather than
 * nulling potentially meaningful data.
 *
 * @returns the number of rows rewritten.
 */
export function backfillProjectRootGitRemotes(sqlite: Database): number {
  const rows = sqlite
    .prepare(
      "SELECT id, git_remote AS gitRemote FROM project_roots WHERE git_remote IS NOT NULL",
    )
    .all() as Array<{ id: string; gitRemote: string | null }>;

  const update = sqlite.prepare(
    "UPDATE project_roots SET git_remote = ? WHERE id = ?",
  );

  let normalized = 0;
  const run = sqlite.transaction(() => {
    for (const row of rows) {
      const canonical = normalizeGitRemote(row.gitRemote);
      if (canonical && canonical !== row.gitRemote) {
        update.run(canonical, row.id);
        normalized++;
      }
    }
  });
  run();
  return normalized;
}