import { createHash } from "node:crypto";
import { readFileSync } from "node:fs";
import { dirname, join } from "node:path";
import { fileURLToPath } from "node:url";
import { createDb } from "./client.js";

const migrationsFolder = join(
	dirname(fileURLToPath(import.meta.url)),
	"migrations",
);

type Journal = { entries: { tag: string; when: number }[] };

function loadMigrations() {
	const journal = JSON.parse(
		readFileSync(join(migrationsFolder, "meta/_journal.json"), "utf8"),
	) as Journal;
	return journal.entries.map((entry) => {
		const body = readFileSync(
			join(migrationsFolder, `${entry.tag}.sql`),
			"utf8",
		);
		return {
			folderMillis: entry.when,
			hash: createHash("sha256").update(body).digest("hex"),
			statements: body.split("--> statement-breakpoint"),
		};
	});
}

export async function migrateDb(url: string): Promise<void> {
	const { client } = createDb(url, 1);
	try {
		// ponytail: drizzle migrate() always CREATE SCHEMA, which OVH CloudDB
		// denies (CREATE on DATABASE). Journal table in public is enough.
		await client.unsafe(`
			CREATE TABLE IF NOT EXISTS public.__drizzle_migrations (
				id SERIAL PRIMARY KEY,
				hash text NOT NULL,
				created_at bigint
			)
		`);
		const last = await client`
			SELECT created_at FROM public.__drizzle_migrations
			ORDER BY created_at DESC
			LIMIT 1
		`;
		const lastAt = last[0] ? Number(last[0].created_at) : 0;
		await client.begin(async (tx) => {
			for (const migration of loadMigrations()) {
				if (lastAt >= migration.folderMillis) continue;
				for (const stmt of migration.statements) {
					await tx.unsafe(stmt);
				}
				await tx`
					INSERT INTO public.__drizzle_migrations (hash, created_at)
					VALUES (${migration.hash}, ${migration.folderMillis})
				`;
			}
		});
	} finally {
		await client.end();
	}
}

const executedDirectly = process.argv[1]
	?.replaceAll("\\", "/")
	.endsWith("/db/migrate.ts");

if (executedDirectly) {
	const url = process.env.DATABASE_URL;
	if (!url) {
		throw new Error("DATABASE_URL is required");
	}
	await migrateDb(url);
}