import type { Database } from "bun:sqlite";

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

/** A row of the public list: the issue and its visible replies. */
export type ListedIssue = Issue & { replies: number };

/** One reply. "reporter" is anyone from the public, under the name they chose ('' is anonymous). */
export type Entry = {
	id: number;
	author: "reporter" | "operator";
	name: string;
	body: string;
	created_at: number;
	hidden: number;
	reply_to: number | null;
};

export type NewEntry = {
	author: Entry["author"];
	body: string;
	at: number;
	name?: string;
	ipHash?: string;
	replyTo?: number | null;
};

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";

/** Files an issue under the repo's next number (§23). */
export function fileIssue(db: Database, issue: NewIssue): { id: number; number: number } {
	// Immediate: it reads the next number before writing, and the sync's thread writes kepr.db too.
	return 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 };
			const { lastInsertRowid } = db
				.query(
					`INSERT INTO issues (
repo, number, title, body, type, version, reporter_name, contact_email, filed_at, ip_hash
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
				)
				.run(
					issue.repo,
					row.next,
					issue.title,
					issue.body,
					issue.type,
					issue.version,
					issue.reporterName,
					issue.contactEmail,
					issue.filedAt,
					issue.ipHash,
				);
			return { id: Number(lastInsertRowid), number: row.next };
		})
		.immediate();
}

/** 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;
}

/** Replies posted from one hashed IP since a time, hidden ones included. */
export function repliesSince(db: Database, ipHash: string, since: number): number {
	const row = db
		.query<{ n: number }, [string, number]>(
			"SELECT COUNT(*) AS n FROM issue_entries WHERE ip_hash = ? AND created_at > ?",
		)
		.get(ipHash, since);
	return row?.n ?? 0;
}

/** A repo's visible issues with one status, newest first, each counting its visible replies. */
export function listIssues(db: Database, repo: string, status: "open" | "closed"): ListedIssue[] {
	return db
		.query<ListedIssue, [string, string]>(
			`SELECT ${PUBLIC_COLUMNS}, (
	SELECT COUNT(*) FROM issue_entries e WHERE e.issue_id = issues.id AND e.hidden = 0
) AS replies
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);
}

/** An issue's visible replies, oldest first. */
export function threadEntries(db: Database, issueId: number): Entry[] {
	return operatorEntries(db, issueId).filter((entry) => !entry.hidden);
}

/** Every reply in an issue's thread, hidden ones included, for the console. */
export function operatorEntries(db: Database, issueId: number): Entry[] {
	return db
		.query<Entry, [number]>(
			`SELECT id, author, name, body, created_at, hidden, reply_to
FROM issue_entries
WHERE issue_id = ?
ORDER BY id`,
		)
		.all(issueId);
}

/** Hides or shows one entry of one issue; false when the issue has no such entry. */
export function setEntryHidden(
	db: Database,
	issueId: number,
	entryId: number,
	hidden: boolean,
): boolean {
	return (
		db
			.query("UPDATE issue_entries SET hidden = ? WHERE id = ? AND issue_id = ?")
			.run(hidden ? 1 : 0, entryId, issueId).changes === 1
	);
}

export function addEntry(db: Database, issueId: number, entry: NewEntry): number {
	return Number(
		db
			.query(
				`INSERT INTO issue_entries (issue_id, author, name, body, created_at, ip_hash, reply_to)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
			)
			.run(
				issueId,
				entry.author,
				entry.name ?? "",
				entry.body,
				entry.at,
				entry.ipHash ?? "",
				entry.replyTo ?? null,
			).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;
	/** 1 when the reporter spoke last: the filing, or their newest visible follow-up (§24). */
	awaiting: number;
};
export type OperatorFilter = "open" | "awaiting" | "closed" | "hidden";

const REPORTER_SPOKE_LAST = `COALESCE((
	SELECT author FROM issue_entries e
	WHERE e.issue_id = issues.id AND e.hidden = 0
	ORDER BY e.id DESC
	LIMIT 1
), 'reporter') = 'reporter'`;
const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email,
${REPORTER_SPOKE_LAST} AS awaiting`;
const OPERATOR_WHERE: Record<OperatorFilter, string> = {
	open: "hidden = 0 AND status = 'open'",
	awaiting: `hidden = 0 AND status = 'open' AND ${REPORTER_SPOKE_LAST}`,
	closed: "hidden = 0 AND status = 'closed'",
	hidden: "hidden = 1",
};

/** Every repo's issues under one console tab, private repos' included, newest first. */
export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {
	return db
		.query<OperatorIssue, []>(
			`SELECT ${OPERATOR_COLUMNS}
FROM issues
WHERE ${OPERATOR_WHERE[filter]}
ORDER BY filed_at DESC, id DESC`,
		)
		.all();
}

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

// A list of issue ids bound as one JSON parameter, so the statement stays fixed whatever its length.
const IDS = "id IN (SELECT value FROM json_each(?))";

/** How many of these ids are issues; the console refuses a bulk action naming one that isn't. */
export function countIssues(db: Database, ids: number[]): number {
	return (
		db
			.query<{ n: number }, [string]>(`SELECT COUNT(*) AS n FROM issues WHERE ${IDS}`)
			.get(JSON.stringify(ids))?.n ?? 0
	);
}

/**
 * Closes or reopens many issues at once (§24). Only those not already so change, so a closed
 * issue keeps its time and "fixed in" save; those that changed are returned.
 */
export function setManyClosed(
	db: Database,
	ids: number[],
	closedAt: number | null,
): Pick<OperatorIssue, "id" | "repo" | "number" | "title" | "hidden">[] {
	return db
		.query<
			Pick<OperatorIssue, "id" | "repo" | "number" | "title" | "hidden">,
			[string, number | null, string]
		>(
			`UPDATE issues
		SET status = ?1, closed_at = ?2, fixed_in = ''
		WHERE id IN (SELECT value FROM json_each(?3)) AND status != ?1
		RETURNING id, repo, number, title, hidden`,
		)
		.all(closedAt === null ? "open" : "closed", closedAt, JSON.stringify(ids));
}

/** Each reply of an issue that came with an address, and whether that address is blocked. */
export function replyAddresses(db: Database, issueId: number): { id: number; blocked: number }[] {
	return db
		.query<{ id: number; blocked: number }, [number]>(
			`SELECT e.id, b.id IS NOT NULL AS blocked
FROM issue_entries e
LEFT JOIN blocked_reporters b ON b.ip_hash = e.ip_hash
WHERE e.issue_id = ? AND e.ip_hash != ''`,
		)
		.all(issueId);
}

export function setManyHidden(db: Database, ids: number[], hidden: boolean): void {
	db.query(`UPDATE issues SET hidden = ? WHERE ${IDS}`).run(hidden ? 1 : 0, JSON.stringify(ids));
}

export type BlockedReporter = { id: number; repo: string; number: number; blocked_at: number };

/**
 * Blocks the address an issue was filed from, or one of its replies was posted from (§23); the
 * posts themselves are untouched. Returns the block's id. A post with no address (imported from
 * Kepr 2, or the operator's) blocks nothing and returns null.
 */
export function blockReporter(
	db: Database,
	issueId: number,
	now: number,
	entryId: number | null = null,
): number | null {
	return (
		db
			.query<{ id: number }, [number | null, number, number | null, number]>(
				`INSERT INTO blocked_reporters (ip_hash, repo, number, blocked_at)
				SELECT hash, repo, number, blocked_at FROM (
					SELECT CASE WHEN ?1 IS NULL THEN i.ip_hash ELSE e.ip_hash END AS hash,
					i.repo, i.number, ?2 AS blocked_at
					FROM issues i
					LEFT JOIN issue_entries e ON e.id = ?3 AND e.issue_id = i.id
					WHERE i.id = ?4
				)
				WHERE hash != ''
				ON CONFLICT (ip_hash) DO UPDATE SET ip_hash = excluded.ip_hash
				RETURNING id`,
			)
			.get(entryId, now, entryId, issueId)?.id ?? null
	);
}

/** Lifts a block; false when there was none with that id. */
export function unblockReporter(db: Database, id: number): boolean {
	return db.query("DELETE FROM blocked_reporters WHERE id = ?").run(id).changes > 0;
}

export function isBlocked(db: Database, ipHash: string): boolean {
	return db.query("SELECT 1 FROM blocked_reporters WHERE ip_hash = ?").get(ipHash) !== null;
}

/** Whether the address an issue came from is blocked. */
export function reporterBlocked(db: Database, issueId: number): boolean {
	return (
		db
			.query(
				`SELECT 1
				FROM blocked_reporters b
				JOIN issues i ON i.ip_hash = b.ip_hash
				WHERE i.id = ?`,
			)
			.get(issueId) !== null
	);
}

export function blockedReporters(db: Database): BlockedReporter[] {
	return db
		.query<BlockedReporter, []>(
			"SELECT id, repo, number, blocked_at FROM blocked_reporters ORDER BY blocked_at DESC, id DESC",
		)
		.all();
}