/**
 * Every SQL statement a 2.8 install's Postgres or MySQL database runs on its
 * way to 2.9.0, in the order it runs them: core's drizzle migrations added
 * since release-2.8.0-tag, then each bundled plugin's own migrations in the
 * order the loader activates them. The data copies that run after that
 * (src/backend/upgrade/) are TypeScript, not SQL, and are covered by the
 * upgrade test and scripts/upgrade-check.sh.
 *
 * Snapshot-tested by scripts/upgrade-sql.test.ts, so any change to the
 * upgrade path shows up as a diff in review.
 *
 * Usage: node scripts/lib/upgrade-sql.mjs postgres|mysql
 */

import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";

const ROOT = path.resolve(
  path.dirname(fileURLToPath(import.meta.url)),
  "../..",
);

/** The last core migration release-2.8.0-tag shipped, per dialect. */
export const LAST_28_MIGRATION = {
  postgres: "0024_flimsy_forge",
  mysql: "0023_even_beast",
};

/** Plugin ids in the order the loader activates them: dependencies first. */
export function pluginActivationOrder(root = ROOT) {
  const dir = path.join(root, "plugins");
  const manifests = new Map(
    fs
      .readdirSync(dir, { withFileTypes: true })
      .filter(
        (entry) =>
          entry.isDirectory() &&
          fs.existsSync(path.join(dir, entry.name, "manifest.json")),
      )
      .map((entry) => [
        entry.name,
        JSON.parse(
          fs.readFileSync(path.join(dir, entry.name, "manifest.json"), "utf8"),
        ),
      ])
      .sort(([a], [b]) => a.localeCompare(b)),
  );

  const order = [];
  const done = new Set();
  const visit = (id) => {
    if (done.has(id) || !manifests.has(id)) return;
    done.add(id);
    const manifest = manifests.get(id);
    for (const dependency of Object.keys(manifest.dependencies ?? {})) {
      visit(dependency);
    }
    for (const dependency of Object.keys(manifest.optionalDependencies ?? {})) {
      visit(dependency);
    }
    order.push(id);
  };
  for (const id of manifests.keys()) visit(id);
  return order;
}

function section(title, sql) {
  return `-- ==== ${title} ====\n${sql.trim()}\n`;
}

export function buildUpgradeSql(dialect, root = ROOT) {
  if (!(dialect in LAST_28_MIGRATION)) {
    throw new Error(`unsupported dialect "${dialect}"`);
  }
  const parts = [
    `-- Termix 2.8.0 to 2.9.0 on ${dialect}, generated by scripts/lib/upgrade-sql.mjs\n`,
  ];

  const coreDir = path.join(root, "drizzle", dialect);
  const journal = JSON.parse(
    fs.readFileSync(path.join(coreDir, "meta", "_journal.json"), "utf8"),
  );
  const tags = journal.entries.map((entry) => entry.tag);
  const last = tags.indexOf(LAST_28_MIGRATION[dialect]);
  if (last === -1) {
    throw new Error(
      `${LAST_28_MIGRATION[dialect]} is not in the ${dialect} journal`,
    );
  }
  for (const tag of tags.slice(last + 1)) {
    parts.push(
      section(
        `core ${tag}`,
        fs.readFileSync(path.join(coreDir, `${tag}.sql`), "utf8"),
      ),
    );
  }

  for (const id of pluginActivationOrder(root)) {
    const dir = path.join(root, "plugins", id, "migrations", dialect);
    if (!fs.existsSync(dir)) continue;
    for (const file of fs
      .readdirSync(dir)
      .filter((name) => name.endsWith(".sql"))
      .sort()) {
      parts.push(
        section(
          `plugin ${id} ${file}`,
          fs.readFileSync(path.join(dir, file), "utf8"),
        ),
      );
    }
  }

  return parts.join("\n").replace(/\r\n/g, "\n");
}

if (
  process.argv[1] &&
  fileURLToPath(import.meta.url) === path.resolve(process.argv[1])
) {
  process.stdout.write(buildUpgradeSql(process.argv[2] ?? "postgres"));
}