@kentcdodds/skills

import { packageStorage } from 'kody:runtime'

/**
 * Always resolve the registry bucket through `packageStorage()`: it is bound
 * to THIS package's bucket in every context — the package's own runtime, and
 * statically imported copies of this code running in ad hoc execute or other
 * packages. Ambient `storage` would silently point at the caller's bucket
 * (or nothing) in those foreign contexts. Called per-use, never cached at
 * module scope, to respect the runtime's late-binding contract.
 */
function bucket() {
	return packageStorage()
}

let schemaReady = false

/**
 * SQLite DDL/column names stay snake_case (existing packageStorage schema).
 * TypeScript row types and helper inputs use camelCase; map at the SQL boundary.
 */
export type SkillRow = {
	id: string
	name: string
	description: string
	createdAt: string
	updatedAt: string
}

export type SkillFileRow = {
	skillId: string
	path: string
	content: string
	updatedAt: string
}

export type SkillVersionRow = {
	id: number
	skillId: string
	path: string
	content: string
	replacedAt: string
}

export type SkillRevisionRow = {
	id: number
	commitOid: string
	skillId: string
	path: string
	replacedAt: string
	contentLength: number
}

export type SkillsIndexRow = {
	id: string
	name: string
	description: string
	filesJson: string
	createdAt: string
	updatedAt: string
}

type SqlSkillRow = {
	id: string
	name: string
	description: string
	created_at: string
	updated_at: string
}

type SqlSkillFileRow = {
	skill_id: string
	path: string
	content: string
	updated_at: string
}

type SqlSkillVersionRow = {
	id: number
	skill_id: string
	path: string
	content: string
	replaced_at: string
}

type SqlSkillRevisionRow = {
	id: number
	commit_oid: string
	skill_id: string
	path: string
	replaced_at: string
	content_length: number
}

type SqlSkillsIndexRow = {
	id: string
	name: string
	description: string
	files_json: string
	created_at: string
	updated_at: string
}

function mapSkillRow(row: SqlSkillRow): SkillRow {
	return {
		id: row.id,
		name: row.name,
		description: row.description,
		createdAt: row.created_at,
		updatedAt: row.updated_at,
	}
}

function mapSkillFileRow(row: SqlSkillFileRow): SkillFileRow {
	return {
		skillId: row.skill_id,
		path: row.path,
		content: row.content,
		updatedAt: row.updated_at,
	}
}

function mapSkillVersionRow(row: SqlSkillVersionRow): SkillVersionRow {
	return {
		id: Number(row.id),
		skillId: row.skill_id,
		path: row.path,
		content: row.content,
		replacedAt: row.replaced_at,
	}
}

function mapSkillRevisionRow(row: SqlSkillRevisionRow): SkillRevisionRow {
	return {
		id: Number(row.id),
		commitOid: row.commit_oid,
		skillId: row.skill_id,
		path: row.path,
		replacedAt: row.replaced_at,
		contentLength: Number(row.content_length),
	}
}

function mapSkillsIndexRow(row: SqlSkillsIndexRow): SkillsIndexRow {
	return {
		id: row.id,
		name: row.name,
		description: row.description,
		filesJson: row.files_json,
		createdAt: row.created_at,
		updatedAt: row.updated_at,
	}
}

export async function ensureSchema(): Promise<void> {
	if (schemaReady) return

	// Legacy document store — left intact after migration (copy-first).
	// Column names remain snake_case for existing packageStorage compatibility.
	await bucket().sql(`
		CREATE TABLE IF NOT EXISTS skills (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			description TEXT NOT NULL,
			created_at TEXT NOT NULL,
			updated_at TEXT NOT NULL
		)
	`)
	await bucket().sql(`
		CREATE TABLE IF NOT EXISTS skill_files (
			skill_id TEXT NOT NULL,
			path TEXT NOT NULL,
			content TEXT NOT NULL,
			updated_at TEXT NOT NULL,
			PRIMARY KEY (skill_id, path)
		)
	`)
	await bucket().sql(`
		CREATE TABLE IF NOT EXISTS skill_versions (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			skill_id TEXT NOT NULL,
			path TEXT NOT NULL,
			content TEXT NOT NULL,
			replaced_at TEXT NOT NULL
		)
	`)

	// Migration / backend pointer (legacy rows stay; this records the switch).
	await bucket().sql(`
		CREATE TABLE IF NOT EXISTS registry_meta (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		)
	`)

	// Fast list/search projection over the plain-repo durable home.
	await bucket().sql(`
		CREATE TABLE IF NOT EXISTS skills_index (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			description TEXT NOT NULL,
			files_json TEXT NOT NULL,
			created_at TEXT NOT NULL,
			updated_at TEXT NOT NULL
		)
	`)

	// Maps skillHistory versionId integers → git commits in the skills repo.
	await bucket().sql(`
		CREATE TABLE IF NOT EXISTS skill_revisions (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			commit_oid TEXT NOT NULL,
			skill_id TEXT NOT NULL,
			path TEXT NOT NULL,
			replaced_at TEXT NOT NULL,
			content_length INTEGER NOT NULL
		)
	`)

	schemaReady = true
}

export function nowIso(): string {
	return new Date().toISOString()
}

export async function sqlRows<T extends Record<string, unknown>>(
	query: string,
	params: Array<string | number | boolean | null> = [],
): Promise<T[]> {
	const result = await bucket().sql(query, params)
	return (result.rows ?? []) as T[]
}

export async function sqlRun(
	query: string,
	params: Array<string | number | boolean | null> = [],
): Promise<{ rowsWritten: number }> {
	const result = await bucket().sql(query, params)
	return { rowsWritten: result.rowsWritten ?? 0 }
}

/** Dual-read camelCase then legacy snake_case registry_meta keys. */
export async function getMeta(key: string): Promise<string | null> {
	await ensureSchema()
	const rows = await sqlRows<{ value: string }>(
		`SELECT value FROM registry_meta WHERE key = ?`,
		[key],
	)
	if (rows[0]?.value != null) return rows[0].value

	const legacyAliases: Record<string, string> = {
		durableHome: 'durable_home',
		legacyStore: 'legacy_store',
		migratedAt: 'migrated_at',
	}
	const legacy = legacyAliases[key]
	if (!legacy) return null
	const legacyRows = await sqlRows<{ value: string }>(
		`SELECT value FROM registry_meta WHERE key = ?`,
		[legacy],
	)
	return legacyRows[0]?.value ?? null
}

export async function setMeta(key: string, value: string): Promise<void> {
	await ensureSchema()
	await sqlRun(
		`INSERT INTO registry_meta (key, value) VALUES (?, ?)
		 ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
		[key, value],
	)
}

export async function upsertLegacySkill(row: SkillRow): Promise<void> {
	await ensureSchema()
	await sqlRun(
		`INSERT INTO skills (id, name, description, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?)
		 ON CONFLICT(id) DO UPDATE SET
			name = excluded.name,
			description = excluded.description,
			created_at = excluded.created_at,
			updated_at = excluded.updated_at`,
		[row.id, row.name, row.description, row.createdAt, row.updatedAt],
	)
}

export async function upsertLegacyFile(row: SkillFileRow): Promise<void> {
	await ensureSchema()
	await sqlRun(
		`INSERT INTO skill_files (skill_id, path, content, updated_at)
		 VALUES (?, ?, ?, ?)
		 ON CONFLICT(skill_id, path) DO UPDATE SET
			content = excluded.content,
			updated_at = excluded.updated_at`,
		[row.skillId, row.path, row.content, row.updatedAt],
	)
}

export async function listSkillIds(): Promise<string[]> {
	await ensureSchema()
	const backend = await getMeta('backend')
	if (backend === 'repo') {
		const rows = await sqlRows<{ id: string }>(
			`SELECT id FROM skills_index ORDER BY id ASC`,
		)
		return rows.map((row) => row.id)
	}
	const rows = await sqlRows<{ id: string }>(
		`SELECT id FROM skills ORDER BY id ASC`,
	)
	return rows.map((row) => row.id)
}

export async function getSkill(id: string): Promise<SkillRow | null> {
	await ensureSchema()
	const backend = await getMeta('backend')
	if (backend === 'repo') {
		const rows = await sqlRows<SqlSkillsIndexRow>(
			`SELECT id, name, description, files_json, created_at, updated_at
			 FROM skills_index WHERE id = ?`,
			[id],
		)
		const row = rows[0]
		if (!row) return null
		const mapped = mapSkillsIndexRow(row)
		return {
			id: mapped.id,
			name: mapped.name,
			description: mapped.description,
			createdAt: mapped.createdAt,
			updatedAt: mapped.updatedAt,
		}
	}
	const rows = await sqlRows<SqlSkillRow>(
		`SELECT id, name, description, created_at, updated_at FROM skills WHERE id = ?`,
		[id],
	)
	return rows[0] ? mapSkillRow(rows[0]) : null
}

export async function getSkillOrThrow(id: string): Promise<SkillRow> {
	const skill = await getSkill(id)
	if (!skill) {
		const ids = await listSkillIds()
		const available =
			ids.length > 0 ? ids.map((value) => `\`${value}\``).join(', ') : '(none)'
		throw new Error(
			`Skill not found: \`${id}\`. Valid skill ids: ${available}. Call skillList for details.`,
		)
	}
	return skill
}

export async function listFilesForSkill(
	skillId: string,
): Promise<SkillFileRow[]> {
	await ensureSchema()
	const rows = await sqlRows<SqlSkillFileRow>(
		`SELECT skill_id, path, content, updated_at
		 FROM skill_files
		 WHERE skill_id = ?
		 ORDER BY path ASC`,
		[skillId],
	)
	return rows.map(mapSkillFileRow)
}

export async function getFile(
	skillId: string,
	path: string,
): Promise<SkillFileRow | null> {
	await ensureSchema()
	const rows = await sqlRows<SqlSkillFileRow>(
		`SELECT skill_id, path, content, updated_at
		 FROM skill_files
		 WHERE skill_id = ? AND path = ?`,
		[skillId, path],
	)
	return rows[0] ? mapSkillFileRow(rows[0]) : null
}

export async function snapshotFile(
	skillId: string,
	path: string,
	content: string,
	replacedAt: string = nowIso(),
): Promise<void> {
	await ensureSchema()
	await sqlRun(
		`INSERT INTO skill_versions (skill_id, path, content, replaced_at)
		 VALUES (?, ?, ?, ?)`,
		[skillId, path, content, replacedAt],
	)
}

export async function upsertSkillsIndex(input: {
	id: string
	name: string
	description: string
	files: string[]
	createdAt: string
	updatedAt: string
}): Promise<void> {
	await ensureSchema()
	await sqlRun(
		`INSERT INTO skills_index (id, name, description, files_json, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?, ?)
		 ON CONFLICT(id) DO UPDATE SET
			name = excluded.name,
			description = excluded.description,
			files_json = excluded.files_json,
			created_at = excluded.created_at,
			updated_at = excluded.updated_at`,
		[
			input.id,
			input.name,
			input.description,
			JSON.stringify(input.files),
			input.createdAt,
			input.updatedAt,
		],
	)
}

export async function deleteSkillsIndex(id: string): Promise<void> {
	await ensureSchema()
	await sqlRun(`DELETE FROM skills_index WHERE id = ?`, [id])
}

export async function listSkillsIndex(): Promise<SkillsIndexRow[]> {
	await ensureSchema()
	const rows = await sqlRows<SqlSkillsIndexRow>(
		`SELECT id, name, description, files_json, created_at, updated_at
		 FROM skills_index
		 ORDER BY id ASC`,
	)
	return rows.map(mapSkillsIndexRow)
}

export async function insertSkillRevision(input: {
	id?: number
	commitOid: string
	skillId: string
	path: string
	replacedAt: string
	contentLength: number
}): Promise<number> {
	await ensureSchema()
	if (input.id != null) {
		await sqlRun(
			`INSERT OR REPLACE INTO skill_revisions
				(id, commit_oid, skill_id, path, replaced_at, content_length)
			 VALUES (?, ?, ?, ?, ?, ?)`,
			[
				input.id,
				input.commitOid,
				input.skillId,
				input.path,
				input.replacedAt,
				input.contentLength,
			],
		)
		return input.id
	}
	await sqlRun(
		`INSERT INTO skill_revisions
			(commit_oid, skill_id, path, replaced_at, content_length)
		 VALUES (?, ?, ?, ?, ?)`,
		[
			input.commitOid,
			input.skillId,
			input.path,
			input.replacedAt,
			input.contentLength,
		],
	)
	const rows = await sqlRows<{ id: number }>(
		`SELECT id FROM skill_revisions
		 WHERE commit_oid = ? AND skill_id = ? AND path = ?
		 ORDER BY id DESC LIMIT 1`,
		[input.commitOid, input.skillId, input.path],
	)
	return Number(rows[0]?.id)
}

export async function getSkillRevision(
	versionId: number,
): Promise<SkillRevisionRow | null> {
	await ensureSchema()
	const rows = await sqlRows<SqlSkillRevisionRow>(
		`SELECT id, commit_oid, skill_id, path, replaced_at, content_length
		 FROM skill_revisions WHERE id = ?`,
		[versionId],
	)
	return rows[0] ? mapSkillRevisionRow(rows[0]) : null
}

export async function listSkillRevisions(input: {
	skillId: string
	path?: string
	limit: number
}): Promise<SkillRevisionRow[]> {
	await ensureSchema()
	if (input.path) {
		const rows = await sqlRows<SqlSkillRevisionRow>(
			`SELECT id, commit_oid, skill_id, path, replaced_at, content_length
			 FROM skill_revisions
			 WHERE skill_id = ? AND path = ?
			 ORDER BY id DESC
			 LIMIT ?`,
			[input.skillId, input.path, input.limit],
		)
		return rows.map(mapSkillRevisionRow)
	}
	const rows = await sqlRows<SqlSkillRevisionRow>(
		`SELECT id, commit_oid, skill_id, path, replaced_at, content_length
		 FROM skill_revisions
		 WHERE skill_id = ?
		 ORDER BY id DESC
		 LIMIT ?`,
		[input.skillId, input.limit],
	)
	return rows.map(mapSkillRevisionRow)
}

/** Read-only dump of the legacy SQLite document store (for migration). */
export async function dumpLegacyStore(): Promise<{
	skills: SkillRow[]
	files: SkillFileRow[]
	versions: SkillVersionRow[]
}> {
	await ensureSchema()
	const skills = (
		await sqlRows<SqlSkillRow>(
			`SELECT id, name, description, created_at, updated_at FROM skills ORDER BY id ASC`,
		)
	).map(mapSkillRow)
	const files = (
		await sqlRows<SqlSkillFileRow>(
			`SELECT skill_id, path, content, updated_at FROM skill_files ORDER BY skill_id ASC, path ASC`,
		)
	).map(mapSkillFileRow)
	const versions = (
		await sqlRows<SqlSkillVersionRow>(
			`SELECT id, skill_id, path, content, replaced_at FROM skill_versions ORDER BY id ASC`,
		)
	).map(mapSkillVersionRow)
	return { skills, files, versions }
}