/**
 * SQLite-backed CacheStore — replaces @danypops/web-spider's JSON-file
 * DiskCache as the daemon's sole page cache adapter. Implements the same
 * ICache<string, SpideredPage> port DiskCache implements, so `get`/`set`/
 * `has`/`delete`/`values` are drop-in compatible; `list`/`search` are the
 * new bounded query shapes `cache.list`/`cache.search` need.
 *
 * Large-image spill-to-file behavior is preserved from DiskCache: images
 * whose base64 length exceeds `inlineImageThreshold` are written to
 * `<imagesDir>/<sha1(src)><ext>` and only `filePath` is persisted in SQLite;
 * `get()`/`values()` hydrate them back to base64 on read.
 */
import type { Database } from "bun:sqlite";
import { createHash } from "node:crypto";
import { existsSync, mkdirSync, readFileSync, rmSync, writeFileSync } from "node:fs";
import { extname, join } from "node:path";
import { canonicalizeUrl, type ImageRef, type SpideredPage, searchPages } from "@danypops/web-spider";
import {
	CACHE_DEFAULT_INLINE_IMAGE_THRESHOLD,
	CACHE_DEFAULT_MAX_ENTRIES,
	CACHE_DEFAULT_TTL_MS,
	CACHE_LIST_DEFAULT_LIMIT,
	CACHE_LIST_MAX_LIMIT,
	CACHE_SEARCH_DEFAULT_LIMIT,
	CACHE_SEARCH_SNIPPET_RADIUS,
} from "../constants.ts";
import { leanOutput } from "../format.ts";
import type { CacheStore } from "./cache-store.ts";
import type {
	CachedPageListFilter,
	CachedPageListResult,
	CachedPageSearchResult,
	CachedPageSortField,
	CachedPageSortOrder,
	CategoryAssignmentResult,
	CategoryListResult,
	CategoryRenameResult,
} from "./page.ts";

export interface SQLiteCacheStoreOptions {
	/** Time-to-live in ms applied on every set(). Default 30 min. */
	ttlMs?: number;
	/** Max page rows. Oldest-by-fetchedAt evicted first once exceeded. Default 500. */
	maxSize?: number;
	/** Directory large images spill to. Default: a sibling of the SQLite file. */
	imagesDir: string;
	/** Base64 length threshold for inline vs. file storage. Default 32 KB. */
	inlineImageThreshold?: number;
}

interface PageRow {
	id: number;
	url: string;
	canonical_url: string | null;
	domain: string;
	title: string;
	description: string;
	author: string;
	published_at: string;
	lang: string;
	tags: string;
	word_count: number;
	reading_time_minutes: number;
	headings: string;
	links: string;
	markdown: string;
	response_content_type: string | null;
	content_ok: number | null;
	content_warning: string | null;
	pdf_info: string | null;
	js_rendered: number;
	open_graph: string | null;
	twitter_card: string | null;
	json_ld: string | null;
	via_strategy: string | null;
	fetched_at: number;
	expires_at: number;
}

/**
 * cache.list's row shape — enough columns to build format.ts's leanOutput()
 * without a chunks/images join (headings/links/tags are inline JSON columns
 * on `pages` already; markdown/chunks/images are deliberately not selected).
 */
interface PageListRow {
	url: string;
	title: string;
	description: string;
	author: string;
	published_at: string;
	tags: string;
	word_count: number;
	headings: string;
	links: string;
	js_rendered: number;
}

/** Builds just enough of a SpideredPage for leanOutput() — the fields it doesn't read are left blank/empty. */
function toLeanInput(row: PageListRow): SpideredPage {
	return {
		url: row.url,
		domain: "",
		fetchedAt: "",
		title: row.title,
		description: row.description,
		author: row.author,
		publishedAt: row.published_at,
		lang: "",
		tags: JSON.parse(row.tags) as string[],
		wordCount: row.word_count,
		readingTimeMinutes: 0,
		headings: JSON.parse(row.headings) as SpideredPage["headings"],
		chunks: [],
		links: JSON.parse(row.links) as SpideredPage["links"],
		markdown: "",
		...(row.js_rendered ? { jsRendered: true } : {}),
	};
}

interface ChunkRow {
	id: string;
	idx: number;
	heading: string;
	text: string;
	word_count: number;
	content_type: string;
}

interface ImageRow {
	src: string;
	mime_type: string;
	alt: string;
	base64: string | null;
	file_path: string | null;
}

/** Normalizes a URL to a stable cache key — same canonicalization every cache in this project shares (see @danypops/web-spider's canonicalizeUrl). */
const SORT_COLUMNS: Record<CachedPageSortField, string> = {
	fetchedAt: "fetched_at",
	publishedAt: "published_at",
	url: "url",
	domain: "domain",
};

/** Column/order come from an allowlist, never string-interpolated from raw input -- SQL has no parameter placeholder for identifiers. */
function resolveOrderBy(sortBy: CachedPageSortField | undefined, sortOrder: CachedPageSortOrder | undefined): string {
	if (sortBy !== undefined && !(sortBy in SORT_COLUMNS)) throw new Error(`invalid sortBy: ${sortBy}`);
	if (sortOrder !== undefined && sortOrder !== "asc" && sortOrder !== "desc") throw new Error(`invalid sortOrder: ${sortOrder}`);
	const column = SORT_COLUMNS[sortBy ?? "fetchedAt"];
	const order = (sortOrder ?? "desc").toUpperCase();
	return `${column} ${order}`;
}

export function pageKey(url: string): string {
	return canonicalizeUrl(url);
}

export class SQLiteCacheStore implements CacheStore {
	private readonly ttlMs: number;
	private readonly maxSize: number;
	private readonly imagesDir: string;
	private readonly inlineImageThreshold: number;

	constructor(
		private readonly db: Database,
		options: SQLiteCacheStoreOptions,
	) {
		this.ttlMs = options.ttlMs ?? CACHE_DEFAULT_TTL_MS;
		this.maxSize = options.maxSize ?? CACHE_DEFAULT_MAX_ENTRIES;
		this.imagesDir = options.imagesDir;
		this.inlineImageThreshold = options.inlineImageThreshold ?? CACHE_DEFAULT_INLINE_IMAGE_THRESHOLD;
	}

	// ── ICache<string, SpideredPage> ──────────────────────────────────────────

	get(url: string): SpideredPage | undefined {
		const row = this.db.query("SELECT * FROM pages WHERE url_key = ? AND expires_at > ?").get(pageKey(url), Date.now()) as PageRow | null;
		if (!row) return undefined;
		return this.hydratePage(row);
	}

	has(url: string): boolean {
		return this.get(url) !== undefined;
	}

	set(url: string, page: SpideredPage): void {
		const key = pageKey(url);
		const now = Date.now();
		const expiresAt = now + this.ttlMs;
		const tx = this.db.transaction(() => {
			const row = this.db
				.query(`
				INSERT INTO pages (
					url_key, url, canonical_url, domain, title, description, author, published_at, lang,
					tags, word_count, reading_time_minutes, headings, links, markdown, response_content_type,
					content_ok, content_warning, pdf_info, js_rendered, open_graph, twitter_card, json_ld,
					via_strategy, fetched_at, expires_at
				) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				ON CONFLICT(url_key) DO UPDATE SET
					url = excluded.url, canonical_url = excluded.canonical_url, domain = excluded.domain,
					title = excluded.title, description = excluded.description, author = excluded.author,
					published_at = excluded.published_at, lang = excluded.lang, tags = excluded.tags,
					word_count = excluded.word_count, reading_time_minutes = excluded.reading_time_minutes,
					headings = excluded.headings, links = excluded.links, markdown = excluded.markdown,
					response_content_type = excluded.response_content_type, content_ok = excluded.content_ok,
					content_warning = excluded.content_warning, pdf_info = excluded.pdf_info,
					js_rendered = excluded.js_rendered, open_graph = excluded.open_graph,
					twitter_card = excluded.twitter_card, json_ld = excluded.json_ld,
					via_strategy = excluded.via_strategy, fetched_at = excluded.fetched_at, expires_at = excluded.expires_at
				RETURNING id
			`)
				.get(
					key,
					page.url,
					page.canonicalUrl ?? null,
					page.domain,
					page.title,
					page.description,
					page.author,
					page.publishedAt,
					page.lang,
					JSON.stringify(page.tags),
					page.wordCount,
					page.readingTimeMinutes,
					JSON.stringify(page.headings),
					JSON.stringify(page.links),
					page.markdown,
					page.contentType ?? null,
					page.contentOk === undefined ? null : page.contentOk ? 1 : 0,
					page.contentWarning ?? null,
					page.pdf ? JSON.stringify(page.pdf) : null,
					page.jsRendered ? 1 : 0,
					page.openGraph ? JSON.stringify(page.openGraph) : null,
					page.twitterCard ? JSON.stringify(page.twitterCard) : null,
					page.jsonLd ? JSON.stringify(page.jsonLd) : null,
					page.viaStrategy ?? null,
					now,
					expiresAt,
				) as { id: number };

			this.db.query("DELETE FROM chunks WHERE page_id = ?").run(row.id);
			for (const chunk of page.chunks) {
				this.db
					.query(`
					INSERT INTO chunks (id, page_id, idx, heading, text, word_count, content_type)
					VALUES (?, ?, ?, ?, ?, ?, ?)
				`)
					.run(chunk.id, row.id, chunk.index, chunk.heading, chunk.text, chunk.wordCount, chunk.contentType);
			}

			this.removePageImageFiles([row.id]);
			this.db.query("DELETE FROM images WHERE page_id = ?").run(row.id);
			for (const image of this.spill(page.images ?? [])) {
				this.db
					.query(`
					INSERT INTO images (page_id, src, mime_type, alt, base64, file_path)
					VALUES (?, ?, ?, ?, ?, ?)
				`)
					.run(row.id, image.src, image.mimeType, image.alt, image.base64 ?? null, image.filePath ?? null);
			}

			this.evict();
		});
		tx.immediate();
	}

	delete(url: string): void {
		const key = pageKey(url);
		const row = this.db.query("SELECT id FROM pages WHERE url_key = ?").get(key) as { id: number } | null;
		if (!row) return;
		this.removePageImageFiles([row.id]);
		this.db.query("DELETE FROM pages WHERE id = ?").run(row.id);
	}

	values(): SpideredPage[] {
		const rows = this.db
			.query("SELECT * FROM pages WHERE expires_at > ? ORDER BY fetched_at DESC LIMIT ?")
			.all(Date.now(), this.maxSize) as PageRow[];
		return rows.map((row) => this.hydratePage(row));
	}

	// ── Bounded query operations ──────────────────────────────────────────────

	list(filter: CachedPageListFilter = {}): CachedPageListResult {
		const now = Date.now();
		const total = (this.db.query("SELECT COUNT(*) AS n FROM pages WHERE expires_at > ?").get(now) as { n: number }).n;

		const conditions = ["expires_at > ?"];
		const parameters: Array<string | number> = [now];
		if (filter.grep?.trim()) {
			const pattern = `%${filter.grep.trim().toLowerCase()}%`;
			conditions.push("(LOWER(url) LIKE ? OR LOWER(title) LIKE ? OR LOWER(domain) LIKE ? OR LOWER(description) LIKE ?)");
			parameters.push(pattern, pattern, pattern, pattern);
		}
		if (filter.domain?.trim()) {
			conditions.push("LOWER(domain) = LOWER(?)");
			parameters.push(filter.domain.trim());
		}
		if (filter.tag?.trim()) {
			conditions.push("EXISTS (SELECT 1 FROM json_each(tags) WHERE LOWER(value) = LOWER(?))");
			parameters.push(filter.tag.trim());
		}
		if (filter.category?.trim()) {
			conditions.push(
				"EXISTS (SELECT 1 FROM page_categories pc JOIN categories c ON c.id = pc.category_id WHERE pc.page_id = pages.id AND LOWER(c.name) = LOWER(?))",
			);
			parameters.push(filter.category.trim());
		}
		if (filter.fetchedAfter !== undefined) {
			conditions.push("fetched_at >= ?");
			parameters.push(filter.fetchedAfter);
		}
		if (filter.fetchedBefore !== undefined) {
			conditions.push("fetched_at <= ?");
			parameters.push(filter.fetchedBefore);
		}
		if (filter.publishedAfter !== undefined) {
			conditions.push("published_at >= ?");
			parameters.push(filter.publishedAfter);
		}
		if (filter.publishedBefore !== undefined) {
			conditions.push("published_at <= ?");
			parameters.push(filter.publishedBefore);
		}
		const where = `WHERE ${conditions.join(" AND ")}`;
		const filtered = (this.db.query(`SELECT COUNT(*) AS n FROM pages ${where}`).get(...parameters) as { n: number }).n;

		const offset = Math.max(0, Math.floor(filter.offset ?? 0));
		const limit = Math.max(1, Math.min(CACHE_LIST_MAX_LIMIT, Math.floor(filter.limit ?? CACHE_LIST_DEFAULT_LIMIT)));
		const orderBy = resolveOrderBy(filter.sortBy, filter.sortOrder);
		const rows = this.db
			.query(`
			SELECT url, title, description, author, published_at, tags, word_count, headings, links, js_rendered
			FROM pages
			${where}
			ORDER BY ${orderBy}
			LIMIT ? OFFSET ?
		`)
			.all(...parameters, limit, offset) as PageListRow[];

		return { total, filtered, offset, limit, pages: rows.map((row) => leanOutput(toLeanInput(row))) };
	}

	search(query: string, opts: { topN?: number; snippetRadius?: number } = {}): CachedPageSearchResult {
		const pages = this.values();
		if (!query.trim() || pages.length === 0) {
			return { query, pagesSearched: pages.length, hits: [] };
		}
		const hits = searchPages(pages, query, {
			topN: opts.topN ?? CACHE_SEARCH_DEFAULT_LIMIT,
			snippetRadius: opts.snippetRadius ?? CACHE_SEARCH_SNIPPET_RADIUS,
		});
		return {
			query,
			pagesSearched: pages.length,
			hits: hits.map((hit) => {
				const page = pages.find((p) => p.url === hit.url);
				const text = hit.chunkId ? (page?.chunks.find((c) => c.id === hit.chunkId)?.text ?? hit.snippet) : hit.snippet;
				return { url: hit.url, title: page?.title ?? "", score: hit.score, heading: hit.heading, text };
			}),
		};
	}

	pruneExpired(now: number): number {
		// Collect ids first (not just a COUNT) so their spilled image files can be
		// removed before the rows disappear -- the ON DELETE CASCADE only cleans
		// up the images table rows, never the files on disk.
		const idsToRemove = (this.db.query("SELECT id FROM pages WHERE expires_at <= ?").all(now) as Array<{ id: number }>).map((r) => r.id);
		this.removePageImageFiles(idsToRemove);
		this.db.query("DELETE FROM pages WHERE expires_at <= ?").run(now);
		return idsToRemove.length;
	}

	// ── Categories ─────────────────────────────────────────────────────────────────────

	private requirePageId(url: string): number {
		const row = this.db.query("SELECT id FROM pages WHERE url_key = ? AND expires_at > ?").get(pageKey(url), Date.now()) as {
			id: number;
		} | null;
		if (!row) throw new Error(`page not cached: ${url}`);
		return row.id;
	}

	private findCategoryIdByName(name: string): number | undefined {
		const row = this.db.query("SELECT id FROM categories WHERE LOWER(name) = LOWER(?)").get(name.trim()) as { id: number } | null;
		return row?.id;
	}

	private getOrCreateCategoryId(name: string): number {
		const existing = this.findCategoryIdByName(name);
		if (existing !== undefined) return existing;
		const row = this.db.query("INSERT INTO categories (name) VALUES (?) RETURNING id").get(name.trim()) as { id: number };
		return row.id;
	}

	assignCategory(url: string, category: string): CategoryAssignmentResult {
		const trimmed = category.trim();
		if (!trimmed) throw new Error("category must not be empty");
		const pageId = this.requirePageId(url);
		const categoryId = this.getOrCreateCategoryId(trimmed);
		// INSERT OR IGNORE -- assigning an already-assigned category is a no-op, not an error.
		this.db.query("INSERT OR IGNORE INTO page_categories (page_id, category_id) VALUES (?, ?)").run(pageId, categoryId);
		const name = (this.db.query("SELECT name FROM categories WHERE id = ?").get(categoryId) as { name: string }).name;
		return { url, category: name, categoryId };
	}

	removeCategory(url: string, category: string): void {
		const pageId = this.requirePageId(url);
		const categoryId = this.findCategoryIdByName(category);
		if (categoryId === undefined) return; // idempotent -- nothing to remove
		this.db.query("DELETE FROM page_categories WHERE page_id = ? AND category_id = ?").run(pageId, categoryId);
	}

	renameCategory(category: string, newName: string): CategoryRenameResult {
		const trimmedNew = newName.trim();
		if (!trimmedNew) throw new Error("newName must not be empty");
		const categoryId = this.findCategoryIdByName(category);
		if (categoryId === undefined) throw new Error(`category not found: ${category}`);

		const collision = this.findCategoryIdByName(trimmedNew);
		if (collision !== undefined && collision !== categoryId) {
			// Merge: repoint every association from the old id to the surviving id, then drop the old row.
			// INSERT OR IGNORE avoids a primary-key collision when a page already has both categories assigned.
			const tx = this.db.transaction(() => {
				this.db
					.query(
						"INSERT OR IGNORE INTO page_categories (page_id, category_id) SELECT page_id, ? FROM page_categories WHERE category_id = ?",
					)
					.run(collision, categoryId);
				this.db.query("DELETE FROM categories WHERE id = ?").run(categoryId);
			});
			tx.immediate();
			return { categoryId: collision, name: trimmedNew, merged: true };
		}

		this.db.query("UPDATE categories SET name = ? WHERE id = ?").run(trimmedNew, categoryId);
		return { categoryId, name: trimmedNew, merged: false };
	}

	listCategories(): CategoryListResult {
		const rows = this.db
			.query(`
			SELECT c.id AS id, c.name AS name, COUNT(pc.page_id) AS page_count
			FROM categories c
			LEFT JOIN page_categories pc ON pc.category_id = c.id
			GROUP BY c.id
			ORDER BY c.name ASC
		`)
			.all() as Array<{ id: number; name: string; page_count: number }>;
		return { categories: rows.map((row) => ({ id: row.id, name: row.name, pageCount: row.page_count })) };
	}

	categoriesForUrl(url: string): string[] {
		const row = this.db.query("SELECT id FROM pages WHERE url_key = ? AND expires_at > ?").get(pageKey(url), Date.now()) as {
			id: number;
		} | null;
		if (!row) return [];
		const rows = this.db
			.query(`
			SELECT c.name AS name FROM page_categories pc JOIN categories c ON c.id = pc.category_id
			WHERE pc.page_id = ? ORDER BY c.name ASC
		`)
			.all(row.id) as Array<{ name: string }>;
		return rows.map((r) => r.name);
	}

	close(): void {
		this.db.close();
	}

	// ── Eviction ───────────────────────────────────────────────────────────────

	private evict(): void {
		const idsToEvict = (
			this.db
				.query("SELECT id FROM pages WHERE id NOT IN (SELECT id FROM pages ORDER BY fetched_at DESC LIMIT ?)")
				.all(this.maxSize) as Array<{ id: number }>
		).map((r) => r.id);
		this.removePageImageFiles(idsToEvict);
		this.db.query("DELETE FROM pages WHERE id NOT IN (SELECT id FROM pages ORDER BY fetched_at DESC LIMIT ?)").run(this.maxSize);
	}

	/** Removes the on-disk spilled image files for the given page ids -- the images table's own rows are cleaned up separately (either an explicit DELETE FROM images, or an ON DELETE CASCADE when the page row itself is removed), but nothing else ever removes the files a page's images were spilled to. Must be called before the page/images rows disappear. */
	private removePageImageFiles(pageIds: number[]): void {
		if (pageIds.length === 0) return;
		const placeholders = pageIds.map(() => "?").join(",");
		const rows = this.db
			.query(`SELECT file_path FROM images WHERE page_id IN (${placeholders}) AND file_path IS NOT NULL`)
			.all(...pageIds) as Array<{ file_path: string }>;
		for (const row of rows) {
			try {
				rmSync(row.file_path, { force: true });
			} catch {
				/* best-effort */
			}
		}
	}

	// ── Image spill / hydrate (ported from DiskCache) ──────────────────────────

	private imageFilename(src: string): string {
		const hash = createHash("sha1").update(src).digest("hex");
		const ext = extname(src.split("?")[0] ?? "") || ".bin";
		return `${hash}${ext}`;
	}

	private spill(images: ImageRef[]): ImageRef[] {
		if (images.length === 0) return images;
		if (!existsSync(this.imagesDir)) mkdirSync(this.imagesDir, { recursive: true });
		return images.map((image) => {
			if (!image.base64 || image.base64.length <= this.inlineImageThreshold) return image;
			const filePath = join(this.imagesDir, this.imageFilename(image.src));
			writeFileSync(filePath, Buffer.from(image.base64, "base64"));
			const { base64: _omit, ...rest } = image;
			return { ...rest, filePath };
		});
	}

	private hydrateImages(rows: ImageRow[]): ImageRef[] {
		return rows.map((row) => {
			if (row.base64) return { src: row.src, mimeType: row.mime_type, alt: row.alt, base64: row.base64 };
			if (row.file_path && existsSync(row.file_path)) {
				try {
					return {
						src: row.src,
						mimeType: row.mime_type,
						alt: row.alt,
						base64: readFileSync(row.file_path).toString("base64"),
						filePath: row.file_path,
					};
				} catch {
					return { src: row.src, mimeType: row.mime_type, alt: row.alt, filePath: row.file_path };
				}
			}
			return { src: row.src, mimeType: row.mime_type, alt: row.alt, ...(row.file_path ? { filePath: row.file_path } : {}) };
		});
	}

	private hydratePage(row: PageRow): SpideredPage {
		const chunkRows = this.db
			.query("SELECT id, idx, heading, text, word_count, content_type FROM chunks WHERE page_id = ? ORDER BY idx")
			.all(row.id) as ChunkRow[];
		const imageRows = this.db
			.query("SELECT src, mime_type, alt, base64, file_path FROM images WHERE page_id = ?")
			.all(row.id) as ImageRow[];
		return {
			url: row.url,
			domain: row.domain,
			fetchedAt: new Date(row.fetched_at).toISOString(),
			...(row.canonical_url ? { canonicalUrl: row.canonical_url } : {}),
			title: row.title,
			description: row.description,
			author: row.author,
			publishedAt: row.published_at,
			lang: row.lang,
			tags: JSON.parse(row.tags) as string[],
			wordCount: row.word_count,
			readingTimeMinutes: row.reading_time_minutes,
			headings: JSON.parse(row.headings) as SpideredPage["headings"],
			chunks: chunkRows.map((c) => ({
				id: c.id,
				index: c.idx,
				heading: c.heading,
				text: c.text,
				wordCount: c.word_count,
				contentType: c.content_type as SpideredPage["chunks"][number]["contentType"],
			})),
			links: JSON.parse(row.links) as SpideredPage["links"],
			...(imageRows.length > 0 ? { images: this.hydrateImages(imageRows) } : {}),
			markdown: row.markdown,
			...(row.response_content_type ? { contentType: row.response_content_type } : {}),
			...(row.content_ok !== null ? { contentOk: row.content_ok === 1 } : {}),
			...(row.content_warning ? { contentWarning: row.content_warning as SpideredPage["contentWarning"] } : {}),
			...(row.pdf_info ? { pdf: JSON.parse(row.pdf_info) as NonNullable<SpideredPage["pdf"]> } : {}),
			...(row.js_rendered ? { jsRendered: true } : {}),
			...(row.open_graph ? { openGraph: JSON.parse(row.open_graph) as Record<string, string> } : {}),
			...(row.twitter_card ? { twitterCard: JSON.parse(row.twitter_card) as Record<string, string> } : {}),
			...(row.json_ld ? { jsonLd: JSON.parse(row.json_ld) as unknown[] } : {}),
			...(row.via_strategy ? { viaStrategy: row.via_strategy } : {}),
		};
	}
}