import { packageStorage } from 'kody:runtime'
import { parseJsonArraySafe } from './util.ts'

export type SqlResult = {
	columns?: string[]
	rows?: Array<Record<string, unknown>>
	rowCount?: number
}

export type StashNote = {
	id: string
	rawText: string
	summary: string
	kind: string
	tags: string[]
	entities: string[]
	dueAt: string | null
	archivedAt: string | null
	createdAt: string
	updatedAt: string
}

function storageError(cause?: unknown): Error {
	const detail = cause instanceof Error ? ` (${cause.message})` : ''
	return new Error(
		`@kody/stash needs this package's own packageStorage()${detail}. Fork https://kody.codes/@kody/stash into your account so notes land in your bucket, or call packages.invoke({ kodyId: 'stash', exportName: 'recent' }) so the export runs in the package runtime. A static import of the live @kody/stash listing shares the platform package bucket, not yours.`,
	)
}

function getStore() {
	let store: ReturnType<typeof packageStorage>
	try {
		store = packageStorage()
	} catch (error) {
		throw storageError(error)
	}
	if (!store || typeof store.sql !== 'function') {
		throw storageError()
	}
	return store
}

export async function sql(
	query: string,
	sqlParams: Array<string | number | boolean | null> = [],
): Promise<SqlResult> {
	return (await getStore().sql(query, sqlParams)) as SqlResult
}

export async function ensureSchema(): Promise<void> {
	await sql(
		'CREATE TABLE IF NOT EXISTS notes (id TEXT PRIMARY KEY, raw_text TEXT NOT NULL, summary TEXT NOT NULL, kind TEXT NOT NULL, tags_json TEXT NOT NULL, entities_json TEXT NOT NULL, due_at TEXT, archived_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)',
	)
	await sql('CREATE INDEX IF NOT EXISTS notes_created_at_idx ON notes(created_at)')
	await sql('CREATE INDEX IF NOT EXISTS notes_kind_idx ON notes(kind)')
}

export function toNote(row: Record<string, unknown>): StashNote {
	return {
		id: String(row.id ?? ''),
		rawText: String(row.raw_text ?? ''),
		summary: String(row.summary ?? ''),
		kind: String(row.kind ?? 'note'),
		tags: parseJsonArraySafe(row.tags_json),
		entities: parseJsonArraySafe(row.entities_json),
		dueAt: typeof row.due_at === 'string' ? row.due_at : null,
		archivedAt: typeof row.archived_at === 'string' ? row.archived_at : null,
		createdAt: String(row.created_at ?? ''),
		updatedAt: String(row.updated_at ?? ''),
	}
}