import type { Database } from "bun:sqlite";
import { createHash, randomBytes } from "node:crypto";

export const ISSUE_TYPES = ["bug", "idea", "question"] as const;
export type IssueType = (typeof ISSUE_TYPES)[number];

export type Issue = {
	id: number;
	number: number;
	title: string;
	body: string;
	type: IssueType;
	version: string;
	reporter_name: string;
	filed_at: number;
	status: "open" | "closed";
	fixed_in: string;
	token_hash: string | null;
};

export type Entry = {
	id: number;
	author: "reporter" | "operator";
	body: string;
	created_at: number;
};

export type NewIssue = {
	repo: string;
	title: string;
	body: string;
	type: IssueType;
	version: string;
	reporterName: string;
	contactEmail: string;
	ipHash: string;
	filedAt: number;
};

// Everything the public may see; the contact email and IP hash stay out of every public read.
const PUBLIC_COLUMNS =
	"id, number, title, body, type, version, reporter_name, filed_at, status, fixed_in, token_hash";

export function hashToken(token: string): string {
	return createHash("sha256").update(token).digest("hex");
}

/** Files an issue under the repo's next number and returns it with its follow-up token (§23). */
export function fileIssue(db: Database, issue: NewIssue): { number: number; token: string } {
	const token = randomBytes(32).toString("hex");
	const number = db.transaction(() => {
		const row = db
			.query<{ next: number }, [string]>(
				"SELECT COALESCE(MAX(number), 0) + 1 AS next FROM issues WHERE repo = ?",
			)
			.get(issue.repo) as { next: number };
		db.query(
			`INSERT INTO issues (repo, number, title, body, type, version, reporter_name, contact_email,
				filed_at, ip_hash, token_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		).run(
			issue.repo,
			row.next,
			issue.title,
			issue.body,
			issue.type,
			issue.version,
			issue.reporterName,
			issue.contactEmail,
			issue.filedAt,
			issue.ipHash,
			hashToken(token),
		);
		return row.next;
	})();
	return { number, token };
}

/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */
export function filingsSince(db: Database, ipHash: string, since: number): number {
	const row = db
		.query<{ n: number }, [string, number]>(
			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",
		)
		.get(ipHash, since);
	return row?.n ?? 0;
}

/** A repo's visible issues with one status, newest first. */
export function listIssues(db: Database, repo: string, status: "open" | "closed"): Issue[] {
	return db
		.query<Issue, [string, string]>(
			`SELECT ${PUBLIC_COLUMNS} FROM issues WHERE repo = ? AND status = ? AND hidden = 0
			ORDER BY number DESC`,
		)
		.all(repo, status);
}

export function issueCounts(db: Database, repo: string): { open: number; closed: number } {
	const rows = db
		.query<{ status: "open" | "closed"; n: number }, [string]>(
			"SELECT status, COUNT(*) AS n FROM issues WHERE repo = ? AND hidden = 0 GROUP BY status",
		)
		.all(repo);
	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;
	return { open: count("open"), closed: count("closed") };
}

/** A visible issue by its number; a hidden one is as missing as an unknown one. */
export function findIssue(db: Database, repo: string, number: number): Issue | null {
	return db
		.query<Issue, [string, number]>(
			`SELECT ${PUBLIC_COLUMNS} FROM issues WHERE repo = ? AND number = ? AND hidden = 0`,
		)
		.get(repo, number);
}

export function threadEntries(db: Database, issueId: number): Entry[] {
	return db
		.query<Entry, [number]>(
			"SELECT id, author, body, created_at FROM issue_entries WHERE issue_id = ? ORDER BY id",
		)
		.all(issueId);
}

export function addEntry(
	db: Database,
	issueId: number,
	author: Entry["author"],
	body: string,
	at: number,
): number {
	return Number(
		db
			.query("INSERT INTO issue_entries (issue_id, author, body, created_at) VALUES (?, ?, ?, ?)")
			.run(issueId, author, body, at).lastInsertRowid,
	);
}

/** Issues anyone can see, across every public repo: the profile's count (§21.2). */
export function publicIssueCount(db: Database): number {
	return (
		db
			.query<{ n: number }, []>(
				"SELECT COUNT(*) AS n FROM issues i JOIN repos r ON r.name = i.repo WHERE r.public = 1 AND i.hidden = 0",
			)
			.get()?.n ?? 0
	);
}