// One-time Kepr 2 import (§28), deleted after cutover; the only bucket writer. Env: KEPR2_DATABASE_URL,
// KEPR2_DATA, KEPR_DATA, KOH_FOLDER or KOH_S3_* (write key at run time only). --dry-run writes nothing.
import { join } from "node:path";
import { blake3 } from "@noble/hashes/blake3.js";
import { bytesToHex } from "@noble/hashes/utils.js";
import { SQL } from "bun";
import { openBucket } from "../src/bucket/reader.ts";
import { checkRelease } from "../src/bucket/repo.ts";
import { compareVersions } from "../src/releases/releases.ts";
import { openKeprDb } from "../src/server/db.ts";

type Row = Record<string, string | number | null>;
const env = (name: string) => process.env[name] ?? "";
const dry = process.argv.includes("--dry-run");
const pg = new SQL(env("KEPR2_DATABASE_URL"));
const kepr = openKeprDb(env("KEPR_DATA"));
const folder = env("KOH_FOLDER");
const VARS = "ENDPOINT BUCKET REGION ACCESS_KEY_ID SECRET_ACCESS_KEY".split(" ");
const [endpoint, name, region, accessKeyId, secretAccessKey] = VARS.map((v) => env(`KOH_S3_${v}`));
const s3 = { endpoint, region, accessKeyId, secretAccessKey };
const bucket = openBucket(folder ? { kind: "folder", path: folder } : { kind: "s3", name, ...s3 });
/** put_if_absent (§9.1): true when `key` holds `bytes` after; a key holding other bytes is kept. */
async function put(key: string, body: string | Uint8Array): Promise<boolean> {
	const bytes = typeof body === "string" ? new TextEncoder().encode(body) : body;
	const had = await bucket.get(key);
	const same = had === null || Bun.deepEquals(had, bytes);
	console.log(had === null ? (dry ? "would put" : "put") : same ? "have" : "differs, kept:", key);
	if (had !== null || dry) return same;
	if (folder) return Bun.write(join(folder, key), bytes).then(() => true);
	// Bun's S3 client has no conditional write, so a presigned PUT carries it.
	const url = new Bun.S3Client({ ...s3, bucket: name }).presign(key, { method: "PUT" });
	const res = await fetch(url, { method: "PUT", body: bytes, headers: { "If-None-Match": "*" } });
	if (!res.ok) throw new Error(`put ${key}: ${res.status}`);
	return true;
}
function insert(table: string, row: Row, tail: string): number | null {
	const cols = Object.keys(row);
	const sql = `INSERT INTO ${table} (${cols}) VALUES (${cols.map((c) => `$${c}`)}) ${tail} RETURNING rowid AS id`;
	const id = kepr.query<{ id: number }, [Row]>(sql).get(row)?.id ?? null;
	console.log(table, JSON.stringify(row).slice(0, 80), id ? "set" : "have");
	return id;
}
kepr.run("BEGIN");
// Kepr 3 keeps one category and has no description field; Kepr 2 had no homepage.
const upsert = "ON CONFLICT DO UPDATE SET public = excluded.public, category = excluded.category";
for (const repo of await pg<Row[]>`
	SELECT name, (private = 0)::int AS public, trim(split_part(COALESCE(categories, ''), ',', 1)) AS category FROM repos`)
	insert("repos", repo, upsert);
// Kepr 2's four statuses become two (Phase 5); "enhancement" is an idea. Any account's reply but the
// reporter's own is the operator's, since §23's thread has no other author.
for (const { replies, ...issue } of await pg<(Row & { replies: Row[] })[]>`
	SELECT i.repo, i.number, i.title, i.description AS body, CASE WHEN i.type IN ('bug', 'question') THEN i.type ELSE 'idea' END AS type,
		COALESCE(i.filed_ver, '') AS version, COALESCE(NULLIF(i.reporter_name, ''), '@' || a.username, '') AS reporter_name,
		COALESCE(NULLIF(i.contact_email, ''), i.contact, '') AS contact_email, i.filed_at,
		CASE WHEN i.status IN ('resolved', 'wontfix', 'closed') THEN 'closed' ELSE 'open' END AS status,
		CASE WHEN i.status IN ('resolved', 'wontfix', 'closed') THEN COALESCE(i.closed_at, i.filed_at) END AS closed_at,
		CASE WHEN i.closed_save ~ '^[0-9a-f]{12}$' THEN i.closed_save ELSE '' END AS fixed_in,
		(SELECT COALESCE(json_agg(json_build_object('author', CASE WHEN e.account_id = i.account_id THEN 'reporter' ELSE 'operator' END,
			'body', e.body, 'created_at', e.created_at) ORDER BY e.created_at, e.id), '[]') FROM issue_entries e WHERE e.issue_id = i.id AND e.entry_type = 'reply' AND e.body IS NOT NULL) AS replies
	FROM issues i LEFT JOIN accounts a ON a.id = i.account_id ORDER BY i.repo, i.number`) {
	const id = insert("issues", issue, "ON CONFLICT DO NOTHING");
	for (const reply of id ? replies : []) insert("issue_entries", { issue_id: id, ...reply }, "");
}
// Kepr 2 counted Nix fetches as installs (kept 90 days) and never counted release downloads.
for (const count of await pg<Row[]>`
	SELECT repo_id AS repo, COUNT(*) AS count FROM engagement_events WHERE event_type = 'nix_install' AND repo_id IS NOT NULL GROUP BY repo_id`)
	insert("installs", count, "ON CONFLICT DO NOTHING");
// As Kepr 2 served: successful and partial builds, a file stored under the repo's name as <repo>-<platform>,
// older uploads' files named by platform; builds off hid a repo's. Save, notes, time: the newest build's.
type Release = { repo: string; version: string; hidden: boolean; record: Row; stored: string[] };
const newest = new Map<string, string>();
for (const { repo, version, hidden, record, stored } of await pg<Release[]>`
	SELECT a.repo_id AS repo, a.version, bool_or(r.builds_enabled = 0) AS hidden, json_agg(a.filename ORDER BY a.platform) AS stored,
		json_build_object('format', 1, 'repo', a.repo_id, 'version', a.version, 'save', (array_agg(b.save_id ORDER BY b.started_at DESC))[1],
			'published_at', max(COALESCE(b.finished_at, a.built_at)), 'machine', 'kepr2', 'notes', COALESCE((array_agg(b.release_notes ORDER BY b.started_at DESC))[1], ''),
			'files', json_agg(json_build_object('platform', a.platform, 'name', CASE WHEN a.filename = a.repo_id THEN a.repo_id || '-' || a.platform ELSE a.filename END,
				'size', a.size_bytes, 'blake3', a.blake3, 'sha256', a.sha256) ORDER BY a.platform)) AS record
	FROM release_artifacts a JOIN build_runs b ON b.id = a.build_run JOIN repos r ON r.name = a.repo_id WHERE b.status IN ('success', 'partial') GROUP BY a.repo_id, a.version ORDER BY a.repo_id, a.version`) {
	const prefix = `${repo}/releases/${version}`;
	try {
		for (const [n, f] of checkRelease(repo, version, record).files.entries()) {
			const dir = join(env("KEPR2_DATA"), "releases", repo, version, f.platform);
			const path = join(dir, stored[n] ?? "");
			const bytes = await Bun.file((await Bun.file(path).exists()) ? path : dir).bytes();
			const kepr2s = bytesToHex(blake3(bytes)) === f.blake3;
			if (!kepr2s || !(await put(`${prefix}/${f.platform}/${f.name}`, bytes)))
				throw new Error(`${f.platform}: not Kepr 2's file, or another is there`);
		}
		if (!(await put(`${prefix}/release.json`, JSON.stringify(record)))) continue;
		if (hidden) insert("release_settings", { repo, version, hidden: 1 }, "ON CONFLICT DO NOTHING");
		if (compareVersions(version, newest.get(repo) ?? "") > 0) newest.set(repo, version);
	} catch (error) {
		console.log(`skip ${prefix}:`, error instanceof Error ? error.message : error);
	}
}
for (const [repo, version] of newest) await put(`${repo}/releases/latest`, version);
kepr.run(dry ? "ROLLBACK" : "COMMIT");
await pg.close();