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

export type SkillRow = {
	id: string
	name: string
	description: string
	created_at: string
	updated_at: string
}

export type SkillFileRow = {
	skill_id: string
	path: string
	content: string
	updated_at: string
}

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

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

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

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

	// Legacy document store — left intact after migration (copy-first).
	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 skill_history version_id 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 }
}

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],
	)
	return rows[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 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<SkillsIndexRow>(
			`SELECT id, name, description, files_json, created_at, updated_at
			 FROM skills_index WHERE id = ?`,
			[id],
		)
		const row = rows[0]
		if (!row) return null
		return {
			id: row.id,
			name: row.name,
			description: row.description,
			created_at: row.created_at,
			updated_at: row.updated_at,
		}
	}
	const rows = await sqlRows<SkillRow>(
		`SELECT id, name, description, created_at, updated_at FROM skills WHERE id = ?`,
		[id],
	)
	return 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 skill_list for details.`,
		)
	}
	return skill
}

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

export async function getFile(
	skillId: string,
	path: string,
): Promise<SkillFileRow | null> {
	await ensureSchema()
	const rows = await sqlRows<SkillFileRow>(
		`SELECT skill_id, path, content, updated_at
		 FROM skill_files
		 WHERE skill_id = ? AND path = ?`,
		[skillId, path],
	)
	return 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[]
	created_at: string
	updated_at: 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.created_at,
			input.updated_at,
		],
	)
}

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()
	return sqlRows<SkillsIndexRow>(
		`SELECT id, name, description, files_json, created_at, updated_at
		 FROM skills_index
		 ORDER BY id ASC`,
	)
}

export async function insertSkillRevision(input: {
	id?: number
	commit_oid: string
	skill_id: string
	path: string
	replaced_at: string
	content_length: 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.commit_oid,
				input.skill_id,
				input.path,
				input.replaced_at,
				input.content_length,
			],
		)
		return input.id
	}
	await sqlRun(
		`INSERT INTO skill_revisions
			(commit_oid, skill_id, path, replaced_at, content_length)
		 VALUES (?, ?, ?, ?, ?)`,
		[
			input.commit_oid,
			input.skill_id,
			input.path,
			input.replaced_at,
			input.content_length,
		],
	)
	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.commit_oid, input.skill_id, input.path],
	)
	return Number(rows[0]?.id)
}

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

export async function listSkillRevisions(input: {
	skill_id: string
	path?: string
	limit: number
}): Promise<SkillRevisionRow[]> {
	await ensureSchema()
	if (input.path) {
		return sqlRows<SkillRevisionRow>(
			`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.skill_id, input.path, input.limit],
		)
	}
	return sqlRows<SkillRevisionRow>(
		`SELECT id, commit_oid, skill_id, path, replaced_at, content_length
		 FROM skill_revisions
		 WHERE skill_id = ?
		 ORDER BY id DESC
		 LIMIT ?`,
		[input.skill_id, input.limit],
	)
}

/** 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<SkillRow>(
		`SELECT id, name, description, created_at, updated_at FROM skills ORDER BY id ASC`,
	)
	const files = await sqlRows<SkillFileRow>(
		`SELECT skill_id, path, content, updated_at FROM skill_files ORDER BY skill_id ASC, path ASC`,
	)
	const versions = await sqlRows<SkillVersionRow>(
		`SELECT id, skill_id, path, content, replaced_at FROM skill_versions ORDER BY id ASC`,
	)
	return { skills, files, versions }
}