import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { join } from "node:path";
import { ANY_DEPTH } from "../mirror/store.ts";

// §19: only what the pages, the sync loop and issues use so far; later phases add their tables.
const SCHEMA = `
CREATE TABLE IF NOT EXISTS settings (
	key   TEXT PRIMARY KEY,
	value TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS repos (
	name            TEXT PRIMARY KEY,
	public          INTEGER NOT NULL DEFAULT 0,
	category        TEXT NOT NULL DEFAULT '',
	homepage        TEXT NOT NULL DEFAULT '',
	languages       TEXT NOT NULL DEFAULT '',
	issues_paused   INTEGER NOT NULL DEFAULT 0,
	hidden_paths    TEXT NOT NULL DEFAULT '',
	-- The hidden list last written to .kepr/hidden/<repo>; NULL while unknown.
	hidden_handed   TEXT,
	last_sync_at    INTEGER,
	last_sync_error TEXT
);
-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.
CREATE TABLE IF NOT EXISTS release_settings (
	repo    TEXT NOT NULL,
	version TEXT NOT NULL,
	hidden  INTEGER NOT NULL DEFAULT 0,
	notes   TEXT,
	PRIMARY KEY (repo, version)
);
-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.
CREATE TABLE IF NOT EXISTS downloads (
	repo     TEXT NOT NULL,
	version  TEXT NOT NULL,
	platform TEXT NOT NULL,
	count    INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (repo, version, platform)
);
-- §24's Downloads: the same downloads per repo and UTC day (YYYY-MM-DD), the newest 400 days.
CREATE TABLE IF NOT EXISTS download_days (
	repo  TEXT NOT NULL,
	day   TEXT NOT NULL,
	count INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (repo, day)
);
CREATE TABLE IF NOT EXISTS installs (
	repo  TEXT PRIMARY KEY,
	count INTEGER NOT NULL DEFAULT 0
);
-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact
-- email is the operator's alone.
CREATE TABLE IF NOT EXISTS issues (
	id            INTEGER PRIMARY KEY,
	repo          TEXT NOT NULL,
	number        INTEGER NOT NULL,
	title         TEXT NOT NULL,
	body          TEXT NOT NULL,
	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),
	version       TEXT NOT NULL DEFAULT '',
	reporter_name TEXT NOT NULL DEFAULT '',
	contact_email TEXT NOT NULL DEFAULT '',
	filed_at      INTEGER NOT NULL,
	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),
	closed_at     INTEGER,
	fixed_in      TEXT NOT NULL DEFAULT '',
	hidden        INTEGER NOT NULL DEFAULT 0,
	ip_hash       TEXT NOT NULL DEFAULT '',
	UNIQUE (repo, number)
);
CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);
-- §23: addresses blocked from filing, replying and following, by the hash the rate limit keeps,
-- with the issue they were blocked from.
CREATE TABLE IF NOT EXISTS blocked_reporters (
	id         INTEGER PRIMARY KEY,
	ip_hash    TEXT NOT NULL UNIQUE,
	repo       TEXT NOT NULL,
	number     INTEGER NOT NULL,
	blocked_at INTEGER NOT NULL
);
-- The thread after the first post. 'reporter' is anyone from the public, the issue's own reporter
-- or not, under the name they chose; reply_to is the reply this one answers.
CREATE TABLE IF NOT EXISTS issue_entries (
	id         INTEGER PRIMARY KEY,
	issue_id   INTEGER NOT NULL REFERENCES issues (id),
	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),
	name       TEXT NOT NULL DEFAULT '',
	body       TEXT NOT NULL,
	created_at INTEGER NOT NULL,
	hidden     INTEGER NOT NULL DEFAULT 0,
	ip_hash    TEXT NOT NULL DEFAULT '',
	reply_to   INTEGER
);
CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);
-- §23: screenshots and logs, on an issue itself (entry_id NULL) or on one of its replies.
CREATE TABLE IF NOT EXISTS issue_files (
	id         INTEGER PRIMARY KEY,
	issue_id   INTEGER NOT NULL REFERENCES issues (id),
	entry_id   INTEGER REFERENCES issue_entries (id),
	name       TEXT NOT NULL,
	type       TEXT NOT NULL,
	size       INTEGER NOT NULL,
	data       BLOB NOT NULL,
	ip_hash    TEXT NOT NULL DEFAULT '',
	created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS issue_files_issue ON issue_files (issue_id, id);
CREATE INDEX IF NOT EXISTS issue_files_ip ON issue_files (ip_hash, created_at);
-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.
CREATE TABLE IF NOT EXISTS sessions (
	token_hash TEXT PRIMARY KEY,
	expires_at INTEGER NOT NULL
);
-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.
CREATE TABLE IF NOT EXISTS repo_text (
	repo        TEXT PRIMARY KEY,
	description TEXT NOT NULL DEFAULT '',
	details     TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS connect_codes (
	code_hash   TEXT PRIMARY KEY,
	machine     TEXT NOT NULL,
	created_at  INTEGER NOT NULL,
	expires_at  INTEGER NOT NULL,
	used_at     INTEGER
);
-- §24: what Kepr tells the operator in the console, newest 200 kept.
CREATE TABLE IF NOT EXISTS notifications (
	id         INTEGER PRIMARY KEY,
	kind       TEXT NOT NULL,
	text       TEXT NOT NULL,
	href       TEXT NOT NULL,
	created_at INTEGER NOT NULL,
	read       INTEGER NOT NULL DEFAULT 0
);
-- §24: emails on their way out. An empty recipient is the operator, kepr.toml's address, sent the
-- subject and the link; an issue's follower (§23) is sent the body, unsubscribe as its header.
CREATE TABLE IF NOT EXISTS email_queue (
	id          INTEGER PRIMARY KEY,
	subject     TEXT NOT NULL,
	href        TEXT NOT NULL,
	recipient   TEXT NOT NULL DEFAULT '',
	body        TEXT NOT NULL DEFAULT '',
	unsubscribe TEXT NOT NULL DEFAULT '',
	status      TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),
	attempts    INTEGER NOT NULL DEFAULT 0,
	next_at     INTEGER NOT NULL,
	last_error  TEXT NOT NULL DEFAULT '',
	created_at  INTEGER NOT NULL,
	sent_at     INTEGER
);
-- §23: addresses following an issue by email; nothing is sent to one until it is confirmed.
CREATE TABLE IF NOT EXISTS issue_followers (
	id         INTEGER PRIMARY KEY,
	issue_id   INTEGER NOT NULL REFERENCES issues (id),
	email      TEXT NOT NULL,
	confirmed  INTEGER NOT NULL DEFAULT 0,
	ip_hash    TEXT NOT NULL DEFAULT '',
	created_at INTEGER NOT NULL,
	UNIQUE (issue_id, email)
);
CREATE INDEX IF NOT EXISTS issue_followers_ip ON issue_followers (ip_hash, created_at);
-- §23: follow requests as they arrive. A follower's stop link deletes their row above, so counting
-- only those rows would hand the hour's allowance back every time someone stopped the emails.
CREATE TABLE IF NOT EXISTS issue_follow_requests (
	ip_hash    TEXT NOT NULL,
	created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS issue_follow_requests_ip
	ON issue_follow_requests (ip_hash, created_at);
-- A repo renamed from the console (June, 2026-10-01): its old pages redirect to the new name.
CREATE TABLE IF NOT EXISTS renamed (
	old TEXT PRIMARY KEY,
	new TEXT NOT NULL
);
`;

/** What raises a notification (§24); each can be switched off. */
export const NOTIFICATION_KINDS = [
	"issue_filed",
	"issue_followup",
	"sync_failed",
	"backup_failed",
] as const;
export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];
const NOTIFICATIONS_KEPT = 200;

// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.
const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [
	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],
	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],
	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],
	["issue_entries", "name", "TEXT NOT NULL DEFAULT ''"],
	["issue_entries", "ip_hash", "TEXT NOT NULL DEFAULT ''"],
	["issue_entries", "reply_to", "INTEGER"],
	["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],
	["repos", "hidden_handed", "TEXT"],
	["email_queue", "recipient", "TEXT NOT NULL DEFAULT ''"],
	["email_queue", "body", "TEXT NOT NULL DEFAULT ''"],
	["email_queue", "unsubscribe", "TEXT NOT NULL DEFAULT ''"],
];
// Columns an older kepr.db still has; card order and install lines were removed (June, 2026-09-30).
const DROPPED_COLUMNS: [table: string, column: string][] = [
	["repos", "sort_order"],
	["repos", "install1"],
	["repos", "install2"],
	["repos", "install3"],
	// The private follow-up link, replaced by public replies (June, 2026-10-08).
	["issues", "token_hash"],
];

/** `description` and `details` are the operator's Markdown, or '' for the README's. */
export type RepoSettings = {
	name: string;
	category: string;
	homepage: string;
	/** The operator's shown languages (§24), or none for the detected one. */
	languages: string[];
	/** Filing is paused (§23); the list, threads and follow-ups carry on. */
	issuesPaused: boolean;
	description: string;
	details: string;
};

export type RepoText = "description" | "details";

type RepoRow = {
	name: string;
	category: string;
	homepage: string;
	languages: string;
	issues_paused: number;
	description: string | null;
	details: string | null;
};

export function openKeprDb(dataDir: string): Database {
	mkdirSync(dataDir, { recursive: true });
	const db = new Database(join(dataDir, "kepr.db"), { strict: true });
	db.run("PRAGMA journal_mode = WAL");
	db.run("PRAGMA busy_timeout = 5000");
	db.run(SCHEMA);
	// Every name below comes from the two lists above, never from input.
	const has = (table: string, column: string) =>
		db
			.query<{ name: string }, []>(`PRAGMA table_info(${table})`)
			.all()
			.some((existing) => existing.name === column);
	const named = has("issue_entries", "name");
	for (const [table, column, definition] of ADDED_COLUMNS) {
		if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
	}
	// Follow-ups came only from an issue's reporter, so they keep the name the issue shows.
	if (!named) {
		db.run(`UPDATE issue_entries SET name = (
	SELECT reporter_name FROM issues WHERE issues.id = issue_entries.issue_id
) WHERE author = 'reporter'`);
	}
	for (const [table, column] of DROPPED_COLUMNS) {
		if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);
	}
	db.run("CREATE INDEX IF NOT EXISTS issue_entries_ip ON issue_entries (ip_hash, created_at)");
	return db;
}

export function setting(db: Database, key: string): string | null {
	const row = db
		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")
		.get(key);
	return row?.value ?? null;
}

/** The name a repo was renamed to, following later renames, or null. */
export function renamedTo(db: Database, name: string): string | null {
	const query = db.query<{ new: string }, [string]>("SELECT new FROM renamed WHERE old = ?");
	let to: string | null = null;
	for (let row = query.get(name), hops = 0; row && hops < 5; row = query.get(row.new), hops++) {
		to = row.new;
	}
	return to;
}

export function isRegistered(db: Database, name: string): boolean {
	return db.query("SELECT 1 FROM repos WHERE name = ?").get(name) !== null;
}

/** Every repo starts private (§20); registering a known repo changes nothing. */
export function registerRepo(db: Database, name: string): void {
	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);
}

/**
 * Tells the operator, unless that kind is switched off, and queues an email when that kind is
 * emailed. The newest 200 notifications are kept.
 */
export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {
	const now = Math.floor(Date.now() / 1000);
	// Immediate: it reads before writing, and the sync's thread writes kepr.db too.
	db.transaction(() => {
		if (!setting(db, `notify_off_${kind}`)) {
			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(
				kind,
				text,
				href,
				now,
			);
			db.query(
				`DELETE FROM notifications
				WHERE id NOT IN (SELECT id FROM notifications ORDER BY id DESC LIMIT ?)`,
			).run(NOTIFICATIONS_KEPT);
		}
		if (setting(db, `email_on_${kind}`)) queueEmail(db, { subject: text, href });
	}).immediate();
}

export type Email = {
	subject: string;
	href: string;
	to?: string;
	text?: string;
	unsubscribe?: string;
};

/**
 * Queues an email (§24): to the operator, or to `to` with `text` as its body. Only the newest 200
 * are kept, and never by dropping one still waiting to go.
 */
export function queueEmail(db: Database, email: Email): void {
	const now = Math.floor(Date.now() / 1000);
	db.query(
		`INSERT INTO email_queue (subject, href, recipient, body, unsubscribe, next_at, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
	).run(
		email.subject,
		email.href,
		email.to ?? "",
		email.text ?? "",
		email.unsubscribe ?? "",
		now,
		now,
	);
	db.query(
		`DELETE FROM email_queue
		WHERE status != 'waiting' AND id NOT IN (SELECT id FROM email_queue ORDER BY id DESC LIMIT ?)`,
	).run(NOTIFICATIONS_KEPT);
}

/** Records a sync's outcome; a repo that starts failing tells the operator, once. */
export function recordSync(db: Database, name: string, at: number, error: string | null): void {
	const before = db
		.query<{ last_sync_error: string | null }, [string]>(
			"SELECT last_sync_error FROM repos WHERE name = ?",
		)
		.get(name);
	if (error && !before?.last_sync_error) {
		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);
	}
	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(
		at,
		error,
		name,
	);
}

export function publicRepoNames(db: Database): string[] {
	return db
		.query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")
		.all()
		.map((row) => row.name);
}

/** The settings of a public repo, or null when it is private or unknown: both must look missing. */
export function publicRepo(db: Database, name: string): RepoSettings | null {
	const row = db
		.query<RepoRow, [string]>(
			`SELECT name, category, homepage, languages, issues_paused, description, details
			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name
			WHERE name = ? AND public = 1`,
		)
		.get(name);
	if (!row) return null;
	return {
		name: row.name,
		category: row.category,
		homepage: row.homepage,
		languages: row.languages === "" ? [] : row.languages.split(", "),
		issuesPaused: row.issues_paused === 1,
		description: row.description ?? "",
		details: row.details ?? "",
	};
}

/**
 * What a sync removes from a repo: the paths the operator hid from it (June, 2026-09-30) and the
 * names ignored in every repo (June, 2026-10-01), both one per line in kepr.db.
 */
export function hiddenPaths(db: Database, repo: string): string[] {
	const row = db
		.query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")
		.get(repo);
	const paths = row?.hidden_paths ? row.hidden_paths.split("\n") : [];
	return [...paths, ...ignoredNames(db).map((name) => `${ANY_DEPTH}${name}`)];
}

/** Keeps a piece of Kepr's own state in settings, never shown as a setting; null removes it. */
export function keepState(db: Database, key: string, value: string | null): void {
	if (value === null) db.query("DELETE FROM settings WHERE key = ?").run(key);
	else db.query("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, value);
}

/** A repo's hidden paths unless they are known to be the list last handed to Koh, else null. */
export function hiddenToHand(db: Database, repo: string): string | null {
	const row = db
		.query<{ hidden_paths: string }, [string]>(
			`SELECT hidden_paths FROM repos
			WHERE name = ?
			AND (hidden_handed IS NULL OR hidden_paths != hidden_handed)`,
		)
		.get(repo);
	return row?.hidden_paths ?? null;
}

/** Records the list in the bucket, or null while a write is under way and its outcome unknown. */
export function recordHiddenHanded(db: Database, repo: string, list: string | null): void {
	db.query("UPDATE repos SET hidden_handed = ? WHERE name = ?").run(list, repo);
}

/** Names of files and folders hidden at any depth in every repo. */
export function ignoredNames(db: Database): string[] {
	return setting(db, "ignored")?.split("\n").filter(Boolean) ?? [];
}

/** A repo's own texts, public or not; '' where the README speaks. */
export function repoText(db: Database, repo: string): Record<RepoText, string> {
	return (
		db
			.query<Record<RepoText, string>, [string]>(
				"SELECT description, details FROM repo_text WHERE repo = ?",
			)
			.get(repo) ?? { description: "", details: "" }
	);
}

/** Sets one of a repo's texts; '' hands it back to the README. */
export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {
	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);
	// `field` is one of two column names, never input.
	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);
}