/**
 * Live-snapshot runtime adapter — connect to a deployed backend like the
 * platform's builds and previews do, with nothing materialized on disk.
 *
 * Instead of pointing getDb() at a snapshot.db file, this dialect fetches the
 * backend's portable content snapshot (`GET /_emdash/api/snapshot`, frontend
 * service-account Bearer token) and loads it into an IN-MEMORY better-sqlite3
 * database. Queries run through a delegating handle; whenever the loaded
 * snapshot is older than `refreshMs` the handle re-fetches in the background
 * and atomically swaps in a fresh database (stale-while-revalidate). A
 * long-running `astro dev` therefore always renders the backend's current
 * content — publish in the admin, reload the page — with no pull step and no
 * snapshot file.
 *
 * Git-backed collections (`storage: "git"`) keep their entries in the site
 * repo itself as content/<collection>/<slug>.json — the backend only holds
 * their schema — so those entries are merged from the local working tree on
 * every (re)load, mirroring bin/snapshot-to-sqlite.mjs in the static-frontend
 * template. Local edits to git content show up on the next refresh too.
 *
 * Node-only (better-sqlite3), like the plain sqlite adapter.
 */

import { existsSync, readdirSync, readFileSync } from "node:fs";
import path from "node:path";

import BetterSqlite3 from "better-sqlite3";
import { type Dialect, SqliteDialect } from "kysely";

import type { SnapshotLiveConfig } from "./adapters.js";

type Db = InstanceType<typeof BetterSqlite3>;
type SqliteValue = string | number | bigint | Buffer | null;

interface SnapshotTableSchema {
	columns: string[];
	types?: Record<string, string>;
}

interface SnapshotPayload {
	tables: Record<string, Array<Record<string, unknown>>>;
	schema: Record<string, SnapshotTableSchema>;
	generatedAt?: string;
}

/**
 * Tables the public render path queries but the snapshot deliberately omits
 * (comments carry commenter PII and hydrate client-side; cron is runtime
 * bookkeeping). Empty stand-ins keep queries from throwing on them.
 */
const STUB_TABLES: Record<string, string> = {
	_emdash_comments:
		"id text primary key, collection text, content_id text, parent_id text, author_name text, author_email text, author_url text, author_user_id text, body text, status text, ip_hash text, user_agent text, moderation_metadata text, created_at text, updated_at text",
	_emdash_comment_reactions:
		"id text primary key, comment_id text, reaction text, voter_hash text, created_at text",
	_emdash_cron_tasks:
		"id text primary key, plugin_id text, task_name text, schedule text, is_oneshot integer, data text, next_run_at text, last_run_at text, status text, locked_at text, enabled integer, created_at text",
};

/** Field types stored as JSON strings in content tables. */
const TRAILING_SLASHES = /\/+$/;

const JSON_FIELD_TYPES = new Set([
	"portableText",
	"json",
	"multiSelect",
	"repeater",
	"media",
	"relation",
	"file",
]);

function toSqliteValue(value: unknown): SqliteValue {
	if (value === undefined || value === null) return null;
	if (typeof value === "boolean") return value ? 1 : 0;
	if (typeof value === "string" || typeof value === "number" || typeof value === "bigint") {
		return value;
	}
	if (Buffer.isBuffer(value)) return value;
	return JSON.stringify(value);
}

async function fetchSnapshot(
	backendUrl: string,
	token: string,
	includeDrafts: boolean,
): Promise<SnapshotPayload> {
	const url = `${backendUrl}/_emdash/api/snapshot${includeDrafts ? "?drafts=true" : ""}`;
	const res = await fetch(url, {
		headers: { Authorization: `Bearer ${token}`, "X-EmDash-Request": "1" },
	});
	if (!res.ok) {
		const detail = (await res.text().catch(() => "")).slice(0, 200);
		throw new Error(`snapshot fetch failed: ${res.status} ${detail}`);
	}
	const body: unknown = await res.json();
	const snap =
		body && typeof body === "object" && "data" in body
			? (body as { data: unknown }).data
			: body;
	const payload = snap as SnapshotPayload;
	if (!payload || typeof payload !== "object" || !payload.tables || !payload.schema) {
		throw new Error("snapshot response missing tables/schema");
	}
	return payload;
}

/**
 * Merge git-backed collection entries (content/<collection>/<slug>.json in the
 * site repo) into their ec_* tables. The backend holds only their schema.
 */
function mergeGitContent(
	db: Db,
	snap: SnapshotPayload,
	contentDir: string,
	includeDrafts: boolean,
): number {
	const collections = (snap.tables._emdash_collections ?? []).filter(
		(c) => c.storage === "git",
	);
	if (collections.length === 0) return 0;

	const fieldsByCollection = new Map<unknown, Array<Record<string, unknown>>>();
	for (const f of snap.tables._emdash_fields ?? []) {
		const list = fieldsByCollection.get(f.collection_id) ?? [];
		list.push(f);
		fieldsByCollection.set(f.collection_id, list);
	}

	let merged = 0;
	const insertAll = db.transaction(() => {
		for (const collection of collections) {
			const slugValue = typeof collection.slug === "string" ? collection.slug : "";
			if (!slugValue) continue;
			const table = `ec_${slugValue}`;
			const cols = snap.schema[table]?.columns;
			if (!cols) continue;
			const dir = path.join(contentDir, slugValue);
			if (!existsSync(dir)) continue;
			const fields = fieldsByCollection.get(collection.id) ?? [];
			const stmt = db.prepare(
				`INSERT OR REPLACE INTO "${table}" (${cols.map((c) => `"${c}"`).join(",")}) VALUES (${cols.map(() => "?").join(",")})`,
			);
			for (const file of readdirSync(dir)) {
				if (!file.endsWith(".json")) continue;
				let entry: Record<string, unknown>;
				try {
					entry = JSON.parse(readFileSync(path.join(dir, file), "utf8")) as Record<
						string,
						unknown
					>;
				} catch {
					continue;
				}
				const status = typeof entry.status === "string" ? entry.status : "published";
				if (status !== "published" && !includeDrafts) continue;
				const slug = typeof entry.slug === "string" ? entry.slug : file.slice(0, -5);
				const updatedAt =
					typeof entry.updatedAt === "string" ? entry.updatedAt : new Date().toISOString();
				const row: Record<string, unknown> = {
					id: entry.id ?? slug,
					slug,
					status,
					locale: entry.locale ?? "en",
					translation_group: entry.translationGroup ?? slug,
					created_at: entry.createdAt ?? updatedAt,
					updated_at: updatedAt,
					published_at: entry.publishedAt ?? updatedAt,
					version: 1,
				};
				const data = (entry.data ?? {}) as Record<string, unknown>;
				for (const field of fields) {
					const fieldSlug = typeof field.slug === "string" ? field.slug : "";
					if (!fieldSlug) continue;
					const value = data[fieldSlug];
					if (value === undefined) continue;
					row[fieldSlug] =
						JSON_FIELD_TYPES.has(String(field.type)) ||
						(value !== null && typeof value === "object")
							? JSON.stringify(value)
							: value;
				}
				stmt.run(cols.map((c) => toSqliteValue(row[c])));
				merged++;
			}
		}
	});
	insertAll();
	return merged;
}

/** Build an in-memory database from a snapshot payload + local git content. */
function buildDatabase(
	snap: SnapshotPayload,
	contentDir: string,
	includeDrafts: boolean,
): Db {
	const db = new BetterSqlite3(":memory:");
	db.pragma("foreign_keys = OFF");

	for (const [table, info] of Object.entries(snap.schema)) {
		const cols = info.columns
			.map((c) => `"${c}" ${info.types?.[c] ?? ""}`.trim())
			.join(", ");
		db.exec(`CREATE TABLE IF NOT EXISTS "${table}" (${cols})`);
	}
	for (const [table, cols] of Object.entries(STUB_TABLES)) {
		if (!snap.schema[table]) db.exec(`CREATE TABLE IF NOT EXISTS "${table}" (${cols})`);
	}

	const insertAll = db.transaction(() => {
		for (const [table, rows] of Object.entries(snap.tables)) {
			if (!Array.isArray(rows) || rows.length === 0) continue;
			const cols = snap.schema[table]?.columns;
			if (!cols) continue;
			const stmt = db.prepare(
				`INSERT OR IGNORE INTO "${table}" (${cols.map((c) => `"${c}"`).join(",")}) VALUES (${cols.map(() => "?").join(",")})`,
			);
			for (const row of rows) {
				stmt.run(cols.map((c) => toSqliteValue(row[c])));
			}
		}
	});
	insertAll();

	mergeGitContent(db, snap, contentDir, includeDrafts);
	return db;
}

/**
 * Create a live-snapshot dialect: in-memory SQLite, continuously refreshed
 * from the backend at `config.url`.
 */
export function createDialect(config: SnapshotLiveConfig): Dialect {
	const backendUrl = (config.url ?? "").replace(TRAILING_SLASHES, "");
	const token = config.token || process.env.EMDASH_API_TOKEN || "";
	if (!backendUrl) {
		throw new Error("snapshot-live: `url` (the backend origin) is required");
	}
	if (!token) {
		throw new Error(
			"snapshot-live: no API token — set EMDASH_API_TOKEN (or pass `token`); admins: <backend>/_emdash/api/settings/frontend-token",
		);
	}
	const includeDrafts = config.includeDrafts ?? false;
	const contentDir = path.resolve(config.contentDir ?? "content");
	const envRefresh = Number(process.env.EMDASH_LIVE_REFRESH_MS);
	const refreshMs =
		config.refreshMs ?? (Number.isFinite(envRefresh) && envRefresh !== 0 ? envRefresh : 2000);

	let current: Db | null = null;
	let loadedAt = 0;
	let inflight: Promise<void> | null = null;
	let announced = false;

	const load = async (): Promise<void> => {
		const snap = await fetchSnapshot(backendUrl, token, includeDrafts);
		const next = buildDatabase(snap, contentDir, includeDrafts);
		const prev = current;
		current = next;
		loadedAt = Date.now();
		if (prev) {
			// Delay closing so any statement still draining from the old handle
			// (e.g. a streamed query crossing ticks) finishes safely. Dev-only
			// memory cost, bounded by the refresh interval.
			const timer = setTimeout(() => {
				try {
					prev.close();
				} catch {
					// already closed
				}
			}, 30_000);
			timer.unref?.();
		}
		if (!announced) {
			announced = true;
			console.log(
				`[emdash] live content from ${backendUrl} (refresh ${refreshMs > 0 ? `${refreshMs}ms` : "off"}${includeDrafts ? ", drafts" : ""})`,
			);
		}
	};

	const ensureLoaded = async (): Promise<void> => {
		if (current) return;
		inflight ??= load().finally(() => {
			inflight = null;
		});
		await inflight;
		if (!current) throw new Error("snapshot-live: initial snapshot load failed");
	};

	const maybeRefresh = (): void => {
		if (refreshMs <= 0 || inflight || Date.now() - loadedAt < refreshMs) return;
		inflight = load()
			.catch((err: unknown) => {
				// Keep serving the last good snapshot; surface the failure once per attempt.
				const message = err instanceof Error ? err.message : String(err);
				console.warn(`[emdash] live snapshot refresh failed: ${message}`);
			})
			.finally(() => {
				inflight = null;
			});
	};

	const handle = {
		prepare(sql: string) {
			if (!current) throw new Error("snapshot-live: database not initialized");
			maybeRefresh(); // background; `current` stays valid for this call
			return current.prepare(sql);
		},
		close(): void {
			current?.close();
			current = null;
		},
	};

	return new SqliteDialect({
		database: async () => {
			await ensureLoaded();
			return handle;
		},
	});
}