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
	);
}

/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */
export type OperatorIssue = Issue & { repo: string; hidden: number; contact_email: string };
export type OperatorFilter = "open" | "closed" | "hidden";

const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email`;

/** Every repo's issues, private repos' included: open or closed ones not hidden, or the hidden. */
export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {
	const where = filter === "hidden" ? "hidden = 1" : "hidden = 0 AND status = ?";
	const query = db.query<OperatorIssue, string[]>(
		`SELECT ${OPERATOR_COLUMNS}
FROM issues
WHERE ${where}
ORDER BY filed_at DESC, id DESC`,
	);
	return filter === "hidden" ? query.all() : query.all(filter);
}

export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {
	return db
		.query<OperatorIssue, [string, number]>(
			`SELECT ${OPERATOR_COLUMNS}
FROM issues
WHERE repo = ? AND number = ?`,
		)
		.get(repo, number);
}

/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */
export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {
	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(
		closedAt === null ? "open" : "closed",
		closedAt,
		fixedIn,
		id,
	);
}

export function setHidden(db: Database, id: number, hidden: boolean): void {
	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);
}

export function setType(db: Database, id: number, type: IssueType): void {
	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);
}