import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { join } from "node:path";

// §19: only what the pages and the sync loop read 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 '',
	sort_order      INTEGER NOT NULL DEFAULT 0,
	install1        TEXT NOT NULL DEFAULT '',
	install2        TEXT NOT NULL DEFAULT '',
	install3        TEXT NOT NULL DEFAULT '',
	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)
);
CREATE TABLE IF NOT EXISTS installs (
	repo  TEXT PRIMARY KEY,
	count INTEGER NOT NULL DEFAULT 0
);
`;

export type RepoSettings = {
	name: string;
	category: string;
	homepage: string;
	installLines: string[];
};

type RepoRow = {
	name: string;
	category: string;
	homepage: string;
	install1: string;
	install2: string;
	install3: string;
};

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

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

export function recordSync(db: Database, name: string, at: number, error: string | null): void {
	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(
		at,
		error,
		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, install1, install2, install3 FROM repos WHERE name = ? AND public = 1",
		)
		.get(name);
	if (!row) return null;
	const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");
	return { name: row.name, category: row.category, homepage: row.homepage, installLines };
}