import { escapeLikePattern } from "../../../lib/html.ts";
import { getDb } from "../index.ts";
import type { WikiRow } from "../types.ts";
import { WIKI_NOT_MODERATED } from "./moderation.ts";

export interface WikiWithNoteCount extends WikiRow {
	note_count: number;
	/** For display only. Null when the owner's profile has never been cached. */
	owner_handle: string | null;
	/** For URLs. Handle when known, DID otherwise — never null, never empty. */
	owner_ref: string;
	owner_avatar: string | null;
}

export type WikiSort = "updated" | "created";

// profile_cache is lazily populated, so an owner who has never been visited joins
// to NULL. COALESCE here, not in the views, so a missing handle can't reach a URL
// builder as "" and render /@/slug. The DID form 301s to the canonical handle.
const OWNER_COLUMNS = `pc.handle AS owner_handle,
	       COALESCE(NULLIF(pc.handle, ''), w.did) AS owner_ref,
	       pc.avatar AS owner_avatar`;

export const WIKI_WITH_COUNTS = `
	SELECT w.*, COUNT(n.slug) as note_count,
	       ${OWNER_COLUMNS}
	FROM wikis w
	LEFT JOIN notes n ON n.wiki_at_uri = w.at_uri
	LEFT JOIN profile_cache pc ON pc.did = w.did`;

export function listPublicWikisPaginated(options: {
	query?: string;
	language?: string;
	sort: WikiSort;
	limit: number;
	offset: number;
}): { wikis: WikiWithNoteCount[]; total: number } {
	const db = getDb();
	const orderCol = options.sort === "created" ? "w.created_at" : "w.updated_at";

	const conditions = ["w.visibility = 'public'", WIKI_NOT_MODERATED];
	const params: (string | number)[] = [];

	if (options.language) {
		conditions.push("w.language = ?");
		params.push(options.language);
	}

	if (options.query) {
		const pattern = `%${escapeLikePattern(options.query)}%`;
		conditions.push(
			"(w.name LIKE ? ESCAPE '\\' OR w.slug LIKE ? ESCAPE '\\' OR w.description LIKE ? ESCAPE '\\')",
		);
		params.push(pattern, pattern, pattern);
	}

	const where = conditions.join(" AND ");

	const total = (
		db
			.query(`SELECT COUNT(*) as count FROM wikis w WHERE ${where}`)
			.get(...params) as { count: number }
	).count;

	const wikis = db
		.query(
			`${WIKI_WITH_COUNTS}
			 WHERE ${where}
			 GROUP BY w.at_uri
			 ORDER BY ${orderCol} DESC
			 LIMIT ? OFFSET ?`,
		)
		.all(...params, options.limit, options.offset) as WikiWithNoteCount[];

	return { wikis, total };
}

export function getWikiLanguages(): string[] {
	const db = getDb();
	const rows = db
		.query(
			`SELECT DISTINCT w.language FROM wikis w
			 WHERE w.visibility = 'public' AND w.language != ''
			   AND ${WIKI_NOT_MODERATED}
			 ORDER BY w.language`,
		)
		.all() as { language: string }[];
	return rows.map((r) => r.language);
}

interface SitemapWikiEntry {
	slug: string;
	owner_ref: string;
	updated_at: string;
}

interface SitemapNoteEntry {
	wiki_slug: string;
	owner_ref: string;
	note_slug: string;
	updated_at: string;
}

// Public wikis only — private wikis must never appear in the sitemap.
export function listPublicWikisForSitemap(): SitemapWikiEntry[] {
	const db = getDb();
	return db
		.query(
			`SELECT w.slug, COALESCE(NULLIF(pc.handle, ''), w.did) AS owner_ref, w.updated_at
			 FROM wikis w
			 LEFT JOIN profile_cache pc ON pc.did = w.did
			 WHERE w.visibility = 'public'
			   AND ${WIKI_NOT_MODERATED}
			 ORDER BY w.updated_at DESC`,
		)
		.all() as SitemapWikiEntry[];
}

// Falls back to note.created_at when current_note has no row (e.g. firehose race).
export function listPublicNotesForSitemap(): SitemapNoteEntry[] {
	const db = getDb();
	return db
		.query(
			`SELECT n.wiki_slug, COALESCE(NULLIF(pc.handle, ''), w.did) AS owner_ref, n.slug as note_slug,
			        COALESCE(c.updated_at, n.created_at) as updated_at
			 FROM notes n
			 INNER JOIN wikis w ON w.at_uri = n.wiki_at_uri AND w.visibility = 'public'
			 LEFT JOIN profile_cache pc ON pc.did = w.did
			 LEFT JOIN current_note c ON c.note_at_uri = n.at_uri
			 WHERE ${WIKI_NOT_MODERATED}
			 ORDER BY updated_at DESC`,
		)
		.all() as SitemapNoteEntry[];
}

export function getWiki(did: string, slug: string): WikiRow | null {
	const db = getDb();
	return (
		(db
			.query("SELECT * FROM wikis WHERE did = ? AND slug = ?")
			.get(did, slug) as WikiRow) ?? null
	);
}

// Every DID whose handle can appear in a listing link (`owner_ref`). Deliberately
// not filtered on visibility: a private wiki's owner still renders on their profile.
export function listWikiOwnerDids(): string[] {
	const db = getDb();
	const rows = db.query("SELECT DISTINCT did FROM wikis").all() as {
		did: string;
	}[];
	return rows.map((row) => row.did);
}

export function listOwnedWikis(
	did: string,
	options: { publicOnly?: boolean } = {},
): WikiWithNoteCount[] {
	const db = getDb();
	const visibilityClause = options.publicOnly
		? " AND w.visibility = 'public'"
		: "";
	return db
		.query(
			`${WIKI_WITH_COUNTS}
			 WHERE w.did = ?${visibilityClause}
			   AND ${WIKI_NOT_MODERATED}
			 GROUP BY w.at_uri
			 ORDER BY w.updated_at DESC`,
		)
		.all(did) as WikiWithNoteCount[];
}

export function listCollaboratingWikis(
	did: string,
	options: { publicOnly?: boolean } = {},
): WikiWithNoteCount[] {
	const db = getDb();
	const visibilityClause = options.publicOnly
		? " AND w.visibility = 'public'"
		: "";
	return db
		.query(
			`${WIKI_WITH_COUNTS}
			 INNER JOIN memberships m ON m.wiki_at_uri = w.at_uri AND m.did = ?
			 WHERE w.did != ?${visibilityClause}
			   AND ${WIKI_NOT_MODERATED}
			 GROUP BY w.at_uri
			 ORDER BY w.updated_at DESC`,
		)
		.all(did, did) as WikiWithNoteCount[];
}

export function getWikiByAtUri(atUri: string): WikiRow | null {
	const db = getDb();
	return (
		(db.query("SELECT * FROM wikis WHERE at_uri = ?").get(atUri) as WikiRow) ??
		null
	);
}

export function insertWiki(
	slug: string,
	did: string,
	name: string,
	visibility: string,
	atUri: string,
	createdAt: string,
	language = "en",
	description = "",
): void {
	const db = getDb();
	db.run(
		`INSERT INTO wikis (slug, did, name, visibility, language, description, at_uri, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`,
		[slug, did, name, visibility, language, description, atUri, createdAt],
	);
}

export function upsertWiki(
	slug: string,
	did: string,
	name: string,
	visibility: string,
	atUri: string,
	createdAt: string,
	language = "en",
	description = "",
): void {
	const db = getDb();
	db.run(
		`INSERT INTO wikis (slug, did, name, visibility, language, description, at_uri, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
     ON CONFLICT(did, slug) DO UPDATE SET
       name = excluded.name,
       visibility = excluded.visibility,
       language = excluded.language,
       description = excluded.description,
       updated_at = datetime('now')`,
		[slug, did, name, visibility, language, description, atUri, createdAt],
	);
}

export function setWikiSidebar(
	did: string,
	slug: string,
	sidebar: string | null,
): void {
	const db = getDb();
	db.run(
		`UPDATE wikis
		 SET sidebar = ?, updated_at = datetime('now')
		 WHERE did = ? AND slug = ?`,
		[sidebar, did, slug],
	);
}

export function setWikiHomeSlug(
	did: string,
	slug: string,
	homeSlug: string | null,
): void {
	const db = getDb();
	db.run(
		`UPDATE wikis
		 SET home_slug = ?, updated_at = datetime('now')
		 WHERE did = ? AND slug = ?`,
		[homeSlug, did, slug],
	);
}

export function setWikiTheme(
	did: string,
	slug: string,
	themeMode: "reader" | "enforce",
	theme: string,
): void {
	const db = getDb();
	db.run(
		`UPDATE wikis
		 SET theme_mode = ?, theme = ?, updated_at = datetime('now')
		 WHERE did = ? AND slug = ?`,
		[themeMode, theme, did, slug],
	);
}

export function deleteWikiByAtUri(atUri: string): void {
	const db = getDb();
	const wiki = db
		.query("SELECT did, slug FROM wikis WHERE at_uri = ?")
		.get(atUri) as { did: string; slug: string } | null;
	if (!wiki) return;

	// Backlinks aren't an FK target (source_note_uri), so clear them before the wiki delete cascades.
	db.transaction(() => {
		const noteUris = db
			.query("SELECT at_uri FROM notes WHERE wiki_at_uri = ?")
			.all(atUri) as { at_uri: string }[];
		for (const n of noteUris) {
			db.run("DELETE FROM backlinks WHERE source_note_uri = ?", [n.at_uri]);
		}
		db.run("DELETE FROM wikis WHERE at_uri = ?", [atUri]);
	})();
}