import type { Database } from "bun:sqlite";
import type { Bucket } from "../bucket/reader.ts";
import {
	checkRelease,
	listReleaseVersions,
	type ReleaseFile,
	readReleasesLatest,
} from "../bucket/repo.ts";
import { releaseRecords } from "../mirror/queries.ts";
import { forgetRelease, replaceRelease } from "../mirror/store.ts";

/** A release as the public sees it: its notes are the operator's override when one is set (§24). */
export type Release = {
	version: string;
	save: string;
	publishedAt: number;
	notes: string;
	files: ReleaseFile[];
};

type ReleaseSetting = { version: string; hidden: number; notes: string | null };

/**
 * A repo's releases without the hidden ones, newest first by numeric dot-separated comparison (the
 * rule `koh update` uses, §12). Files are ordered by platform name, as Kepr 2 listed them.
 */
export function visibleReleases(kepr: Database, mirror: Database, repo: string): Release[] {
	const settings = new Map(
		kepr
			.query<ReleaseSetting, [string]>(
				"SELECT version, hidden, notes FROM release_settings WHERE repo = ?",
			)
			.all(repo)
			.map((row) => [row.version, row]),
	);
	return releaseRecords(mirror)
		.filter((record) => settings.get(record.version)?.hidden !== 1)
		.map((record) => ({
			version: record.version,
			save: record.save,
			publishedAt: record.published_at,
			notes: settings.get(record.version)?.notes ?? record.notes,
			files: record.files.toSorted((a, b) => (a.platform < b.platform ? -1 : 1)),
		}))
		.sort((a, b) => compareVersions(b.version, a.version));
}

export function compareVersions(a: string, b: string): number {
	const left = a.split(".").map(digits);
	const right = b.split(".").map(digits);
	for (let i = 0; i < Math.max(left.length, right.length); i++) {
		const [x, y] = [left[i] ?? "0", right[i] ?? "0"];
		// Compared as digit strings, never as floats, so long components keep their precision.
		if (x.length !== y.length) return x.length - y.length;
		if (x !== y) return x < y ? -1 : 1;
	}
	// Numerically equal (`1.0` and `1.0.0`): compare the text, so the order is still total.
	return a < b ? -1 : a > b ? 1 : 0;
}

// A component's leading digits without leading zeros ("0" when it has none), as parseInt reads it.
function digits(part: string): string {
	return (/^\d+/.exec(part)?.[0] ?? "0").replace(/^0+(?=\d)/, "");
}

/** §22.2's content types for a release file. */
export function releaseContentType(name: string): string {
	const lower = name.toLowerCase();
	if (lower.endsWith(".apk")) return "application/vnd.android.package-archive";
	if (lower.endsWith(".zip")) return "application/zip";
	return "application/octet-stream";
}

const DAYS_KEPT = 400;
const DAY_MS = 24 * 60 * 60 * 1000;

/** A UTC day as `download_days` keys it. */
export const utcDay = (ms: number): string => new Date(ms).toISOString().slice(0, 10);

export function countDownload(
	kepr: Database,
	repo: string,
	version: string,
	platform: string,
): void {
	const now = Date.now();
	kepr.transaction(() => {
		kepr
			.query(
				`INSERT INTO downloads (repo, version, platform, count) VALUES (?, ?, ?, 1)
				ON CONFLICT DO UPDATE SET count = count + 1`,
			)
			.run(repo, version, platform);
		kepr
			.query(
				`INSERT INTO download_days (repo, day, count) VALUES (?, ?, 1)
				ON CONFLICT DO UPDATE SET count = count + 1`,
			)
			.run(repo, utcDay(now));
		kepr.query("DELETE FROM download_days WHERE day < ?").run(utcDay(now - DAYS_KEPT * DAY_MS));
	})();
}

/** Downloads per UTC day from `since` on, for one repo or all of them (§24). */
export function downloadsByDay(kepr: Database, since: string, repo: string): Map<string, number> {
	const rows = kepr
		.query<{ day: string; n: number }, [string, string, string]>(
			`SELECT day, SUM(count) AS n
			FROM download_days
			WHERE day >= ? AND (? = '' OR repo = ?)
			GROUP BY day`,
		)
		.all(since, repo, repo);
	return new Map(rows.map((row) => [row.day, row.n]));
}

/** Each repo's downloads ever and since a day, most downloaded first. */
export function repoDownloads(kepr: Database, since: string) {
	return kepr
		.query<{ repo: string; total: number; recent: number }, [string]>(
			`SELECT d.repo,
				SUM(d.count) AS total,
				COALESCE((SELECT SUM(count) FROM download_days WHERE repo = d.repo AND day >= ?), 0)
					AS recent
			FROM downloads d
			GROUP BY d.repo
			ORDER BY total DESC, d.repo`,
		)
		.all(since);
}

/** The most downloaded files across every repo. */
export function topFiles(kepr: Database, limit: number) {
	return kepr
		.query<{ repo: string; version: string; platform: string; count: number }, [number]>(
			`SELECT repo, version, platform, count
			FROM downloads
			ORDER BY count DESC, repo, version, platform
			LIMIT ?`,
		)
		.all(limit);
}

/** A Nix tarball fetch (§22.2). */
export function countInstall(kepr: Database, repo: string): void {
	kepr
		.query(
			"INSERT INTO installs (repo, count) VALUES (?, 1) ON CONFLICT DO UPDATE SET count = count + 1",
		)
		.run(repo);
}

/** Download counts of a repo's files, keyed `<version>/<platform>`. */
export function downloadCounts(kepr: Database, repo: string): Map<string, number> {
	const rows = kepr
		.query<{ version: string; platform: string; count: number }, [string]>(
			"SELECT version, platform, count FROM downloads WHERE repo = ?",
		)
		.all(repo);
	return new Map(rows.map((row) => [`${row.version}/${row.platform}`, row.count]));
}

/** Downloads of every visible release of every public repo: the profile's count (§21.2). */
export function publicDownloadCount(kepr: Database): number {
	return (
		kepr
			.query<{ n: number }, []>(
				`SELECT COALESCE(SUM(d.count), 0) AS n FROM downloads d
				JOIN repos r ON r.name = d.repo AND r.public = 1
				LEFT JOIN release_settings s ON s.repo = d.repo AND s.version = d.version
				WHERE COALESCE(s.hidden, 0) = 0`,
			)
			.get()?.n ?? 0
	);
}

/** A release as the operator sees it (§24): hidden ones too, and the record's own notes. */
export type OperatorRelease = {
	version: string;
	recordNotes: string;
	notes: string;
	hidden: boolean;
	downloads: [platform: string, count: number][];
};

export function operatorReleases(
	kepr: Database,
	mirror: Database,
	repo: string,
): OperatorRelease[] {
	const counts = downloadCounts(kepr, repo);
	const settings = kepr
		.query<ReleaseSetting, [string]>(
			"SELECT version, hidden, notes FROM release_settings WHERE repo = ?",
		)
		.all(repo);
	return releaseRecords(mirror)
		.map((record) => {
			const setting = settings.find((row) => row.version === record.version);
			return {
				version: record.version,
				recordNotes: record.notes,
				notes: setting?.notes ?? record.notes,
				hidden: setting?.hidden === 1,
				downloads: record.files.map((file): [string, number] => [
					file.platform,
					counts.get(`${record.version}/${file.platform}`) ?? 0,
				]),
			};
		})
		.sort((a, b) => compareVersions(b.version, a.version));
}

/** Hides or shows a version and sets its displayed notes; null notes fall back to the record's. */
export function setRelease(
	kepr: Database,
	repo: string,
	version: string,
	hidden: boolean,
	notes: string | null,
): void {
	kepr
		.query(
			`INSERT INTO release_settings (repo, version, hidden, notes) VALUES (?, ?, ?, ?)
			ON CONFLICT DO UPDATE SET hidden = excluded.hidden, notes = excluded.notes`,
		)
		.run(repo, version, hidden ? 1 : 0, notes);
}

// Deletes run one at a time, so two can't each move `latest` from a reading the other outdated.
let deleting: Promise<unknown> = Promise.resolve();

/**
 * Deletes a build from the bucket (June, 2026-09-30), since Koh has no way to. `releases/latest`
 * moves first, to the newest other version with a record (or goes with the last), then the files,
 * then the record: until the record goes the version still exists (§11.2), so a failure part-way
 * leaves it whole and deleting it again finishes the job. Kepr's settings and counts for it go too.
 */
export function deleteRelease(
	bucket: Bucket,
	kepr: Database,
	mirror: Database,
	repo: string,
	version: string,
): Promise<void> {
	const run = deleting.then(() => removeRelease(bucket, kepr, mirror, repo, version));
	deleting = run.catch(() => {});
	return run;
}

async function removeRelease(
	bucket: Bucket,
	kepr: Database,
	mirror: Database,
	repo: string,
	version: string,
): Promise<void> {
	if ((await readReleasesLatest(bucket, repo)) === version) {
		const newest = (await listReleaseVersions(bucket, repo))
			.filter((other) => other !== version)
			.sort((a, b) => compareVersions(b, a))[0];
		const latest = `${repo}/releases/latest`;
		// ponytail: an unconditional write, as the reader has no put_if_match; a Koh publish landing
		// between the read above and this write loses `latest` until it offers again.
		if (newest) await bucket.put(latest, new TextEncoder().encode(newest), "text/plain");
		else await bucket.remove(latest);
	}
	const prefix = `${repo}/releases/${version}/`;
	const record = `${prefix}release.json`;
	for (const key of await bucket.list(prefix)) {
		if (key !== record) await bucket.remove(key);
	}
	await bucket.remove(record);
	forgetRelease(mirror, version);
	kepr.transaction(() => {
		for (const table of ["release_settings", "downloads"]) {
			// `table` is one of the two names above, never input.
			kepr.query(`DELETE FROM ${table} WHERE repo = ? AND version = ?`).run(repo, version);
		}
	})();
}

/** Why a single file can't be deleted: the last one goes with its version, never alone. */
export class LastReleaseFile extends Error {}

/**
 * Deletes one file of a build (2026-09-30). The record is rewritten without it first, so a failure
 * part-way leaves at most a file nothing lists; its count goes too. Runs in the same queue as whole
 * deletes, so neither reads a record the other is rewriting.
 */
export function deleteReleaseFile(
	bucket: Bucket,
	kepr: Database,
	mirror: Database,
	repo: string,
	version: string,
	platform: string,
): Promise<void> {
	const run = deleting.then(() => removeReleaseFile(bucket, kepr, mirror, repo, version, platform));
	deleting = run.catch(() => {});
	return run;
}

async function removeReleaseFile(
	bucket: Bucket,
	kepr: Database,
	mirror: Database,
	repo: string,
	version: string,
	platform: string,
): Promise<void> {
	const key = `${repo}/releases/${version}/release.json`;
	const bytes = await bucket.get(key);
	if (bytes === null) throw new Error(`${key} is missing`);
	// Rewritten from the raw JSON, so fields Kepr doesn't read survive the edit.
	const raw = JSON.parse(new TextDecoder().decode(bytes)) as { files: ReleaseFile[] };
	const file = raw.files.find((f) => f.platform === platform);
	if (file) {
		if (raw.files.length === 1) throw new LastReleaseFile("delete the version instead");
		raw.files = raw.files.filter((f) => f !== file);
		const json = `${JSON.stringify(raw, null, 2)}\n`;
		await bucket.put(key, new TextEncoder().encode(json), "application/json");
		replaceRelease(mirror, checkRelease(repo, version, raw));
		await bucket.remove(`${repo}/releases/${version}/${platform}/${file.name}`);
	}
	kepr
		.query("DELETE FROM downloads WHERE repo = ? AND version = ? AND platform = ?")
		.run(repo, version, platform);
}