// One-time Kepr 2 import (§28), deleted after cutover; the only bucket writer.
// --dry-run writes nothing.
import { dirname, 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); throws when `key` holds other bytes. */
async function put(key: string, body: string | Uint8Array): Promise<void> {
	const bytes = typeof body === "string" ? new TextEncoder().encode(body) : body;
	const had = (await bucket.get(key)) ?? (dry ? null : await write(key, bytes));
	console.log(had ? "have" : dry ? "would put" : "put", key);
	if (had && !Bun.deepEquals(had, bytes)) throw new Error(`${key} holds other bytes`);
}
// Bun's S3 client can't write conditionally, so a presigned PUT does;
// a 412 returns the racer's bytes.
async function write(key: string, bytes: Uint8Array): Promise<Uint8Array | null> {
	if (folder) return Bun.write(join(folder, key), bytes).then(() => null);
	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 || res.status === 412) return res.ok ? null : bucket.get(key);
	throw new Error(`put ${key}: ${res.status}`);
}
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;
}
const errors: string[] = [],
	writes: [string, string | Uint8Array][] = [];
kepr.run("BEGIN");
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.
// Kepr 2 wrote the operator's replies as "operator" (NULL before accounts);
// a third account's reply has no author kind here.
for (const { replies, ...i } 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.closed_save ~ '^[0-9a-f]{12}$' THEN i.closed_save
			ELSE ''
		END AS fixed_in,
		CASE
			WHEN i.status IN ('resolved', 'wontfix', 'closed') THEN COALESCE(i.closed_at, i.filed_at)
		END AS closed_at,
		(
			SELECT COALESCE(
				json_agg(
					json_build_object(
						'author',
						CASE
							WHEN e.account_id IS NULL
								OR e.account_id = 'operator'
								OR o.tier = 'operator'
								THEN 'operator'
							WHEN e.account_id = i.account_id THEN 'reporter'
						END,
						'body', e.body,
						'created_at', e.created_at
					)
					ORDER BY e.created_at, e.id
				),
				'[]'
			)
			FROM issue_entries e
			LEFT JOIN accounts o ON o.id = e.account_id
			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", i, "ON CONFLICT DO NOTHING");
	if (replies.some((r) => !r.author)) errors.push(`${i.repo}#${i.number}: third-party reply`);
	else {
		for (const r of id ? replies : []) {
			const name = r.author === "reporter" ? i.reporter_name : "";
			insert("issue_entries", { issue_id: id, ...r, name }, "");
		}
	}
}
// Kepr 2 counted Nix fetches as installs (kept 90 days).
// Its archive downloads have no home in Kepr 3.
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");
}
// Only what Kepr 2 served: successful and partial builds, a file stored as
// the repo's name as <repo>-<platform>, older uploads named by platform;
// builds off hid a repo's. Save, notes: 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(
			concat_ws(
				'/',
				'releases',
				a.repo_id,
				a.version,
				a.platform,
				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 path = join(env("KEPR2_DATA"), stored[n] ?? "");
			const file = Bun.file(path);
			const bytes = await ((await file.exists()) ? file : Bun.file(dirname(path))).bytes();
			if (bytesToHex(blake3(bytes)) !== f.blake3) throw new Error(`${f.platform}: hash mismatch`);
			writes.push([`${prefix}/${f.platform}/${f.name}`, bytes]);
		}
		writes.push([`${prefix}/release.json`, JSON.stringify(record)]);
		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) {
		errors.push(`${prefix}: ${error instanceof Error ? error.message : error}`);
	}
}
// Fail before any write (kepr.db is not yet written); writes never overwrite, so a re-run finishes one.
if (errors.length > 0) throw new Error(`not imported, nothing written:\n${errors.join("\n")}`);
for (const [repo, version] of newest) writes.push([`${repo}/releases/latest`, version]);
for (const [key, body] of writes) await put(key, body);
kepr.run(dry ? "ROLLBACK" : "END");
await pg.close();