import { packageStorage } from 'kody:runtime'
import { commitLooksStored } from './commit-complete.ts'
import type { BucketDelta, Commentary, FileKind, IngestedCommit, RepoSettings } from './types.ts'

type SqlBucket = {
	sql: (
		query: string,
		params?: Array<string | number | boolean | null>,
	) => Promise<{ rows: Array<Record<string, unknown>> }>
}

let schemaReady = false

export function bucket(): SqlBucket {
	return packageStorage() as unknown as SqlBucket
}

/** Platform SQL silently caps result sets (~1000 rows). Page past that. */
const SQL_PAGE_SIZE = 1000

export async function sqlAll(
	query: string,
	params: Array<string | number | boolean | null> = [],
): Promise<Array<Record<string, unknown>>> {
	const db = bucket()
	const rows: Array<Record<string, unknown>> = []
	let offset = 0
	for (;;) {
		const page = await db.sql(
			`${query} LIMIT ? OFFSET ?`,
			[...params, SQL_PAGE_SIZE, offset],
		)
		rows.push(...page.rows)
		if (page.rows.length < SQL_PAGE_SIZE) break
		offset += SQL_PAGE_SIZE
	}
	return rows
}

export async function ensureSchema(): Promise<void> {
	if (schemaReady) return
	const db = bucket()
	await db.sql(`
		CREATE TABLE IF NOT EXISTS settings (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		)
	`)
	await db.sql(`
		CREATE TABLE IF NOT EXISTS commits (
			sha TEXT PRIMARY KEY,
			committed_at TEXT NOT NULL,
			message TEXT NOT NULL,
			author_name TEXT NOT NULL,
			html_url TEXT NOT NULL,
			parent_sha TEXT,
			files_truncated INTEGER NOT NULL DEFAULT 0,
			skipped_files INTEGER NOT NULL DEFAULT 0,
			counted_files INTEGER NOT NULL DEFAULT 0,
			ingested_at TEXT NOT NULL
		)
	`)
	await db.sql(`
		CREATE TABLE IF NOT EXISTS commit_buckets (
			sha TEXT NOT NULL,
			language TEXT NOT NULL,
			kind TEXT NOT NULL,
			additions INTEGER NOT NULL,
			deletions INTEGER NOT NULL,
			PRIMARY KEY (sha, language, kind)
		)
	`)
	await db.sql(`
		CREATE TABLE IF NOT EXISTS commentaries (
			id TEXT PRIMARY KEY,
			sha TEXT NOT NULL,
			label TEXT NOT NULL,
			narrative TEXT NOT NULL,
			created_at TEXT NOT NULL
		)
	`)
	await db.sql(`
		CREATE TABLE IF NOT EXISTS generated_paths (
			path TEXT PRIMARY KEY,
			reason TEXT NOT NULL
		)
	`)
	await db.sql(
		`CREATE INDEX IF NOT EXISTS commits_committed_at ON commits (committed_at)`,
	)
	await db.sql(
		`CREATE INDEX IF NOT EXISTS commentaries_sha ON commentaries (sha)`,
	)
	schemaReady = true
}

export const DEFAULT_REPO: RepoSettings = {
	owner: 'kentcdodds',
	repo: 'kody',
	branch: 'main',
}

export async function getRepoSettings(): Promise<RepoSettings> {
	await ensureSchema()
	const raw = await getSetting('repo')
	if (!raw) return { ...DEFAULT_REPO }
	try {
		const parsed = JSON.parse(raw) as Partial<RepoSettings>
		return {
			owner: parsed.owner || DEFAULT_REPO.owner,
			repo: parsed.repo || DEFAULT_REPO.repo,
			branch: parsed.branch || DEFAULT_REPO.branch,
		}
	} catch {
		return { ...DEFAULT_REPO }
	}
}

export async function setRepoSettings(next: Partial<RepoSettings>): Promise<RepoSettings> {
	const current = await getRepoSettings()
	const merged = {
		owner: next.owner?.trim() || current.owner,
		repo: next.repo?.trim() || current.repo,
		branch: next.branch?.trim() || current.branch,
	}
	await setSetting('repo', JSON.stringify(merged))
	return merged
}

export async function getSetting(key: string): Promise<string | null> {
	await ensureSchema()
	const result = await bucket().sql(`SELECT value FROM settings WHERE key = ?`, [
		key,
	])
	const value = result.rows[0]?.value
	return typeof value === 'string' ? value : null
}

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

export async function listKnownGeneratedPaths(): Promise<Map<string, string>> {
	await ensureSchema()
	const rows = await sqlAll(`SELECT path, reason FROM generated_paths`)
	const map = new Map<string, string>()
	for (const row of rows) {
		if (typeof row.path === 'string' && typeof row.reason === 'string') {
			map.set(row.path, row.reason)
		}
	}
	return map
}

export async function rememberGeneratedPath(path: string, reason: string): Promise<void> {
	await ensureSchema()
	await bucket().sql(
		`INSERT INTO generated_paths (path, reason) VALUES (?, ?)
		 ON CONFLICT(path) DO UPDATE SET reason = excluded.reason`,
		[path, reason],
	)
}

export async function hasCommit(sha: string): Promise<boolean> {
	await ensureSchema()
	const result = await bucket().sql(
		`SELECT counted_files, skipped_files FROM commits WHERE sha = ?`,
		[sha],
	)
	if (result.rows.length === 0) return false
	const counted = Number(result.rows[0]?.counted_files ?? 0)
	const skipped = Number(result.rows[0]?.skipped_files ?? 0)
	const buckets = await bucket().sql(
		`SELECT COUNT(*) AS n FROM commit_buckets WHERE sha = ?`,
		[sha],
	)
	return commitLooksStored(counted, skipped, Number(buckets.rows[0]?.n ?? 0))
}

export async function listCompleteShas(): Promise<Set<string>> {
	await ensureSchema()
	const commits = await sqlAll(
		`SELECT sha, counted_files, skipped_files FROM commits ORDER BY sha ASC`,
	)
	const buckets = await sqlAll(
		`SELECT sha, COUNT(*) AS n FROM commit_buckets GROUP BY sha ORDER BY sha ASC`,
	)
	const bucketCount = new Map<string, number>()
	for (const row of buckets) {
		if (typeof row.sha === 'string') {
			bucketCount.set(row.sha, Number(row.n ?? 0))
		}
	}
	const complete = new Set<string>()
	for (const row of commits) {
		if (typeof row.sha !== 'string') continue
		if (
			commitLooksStored(
				Number(row.counted_files ?? 0),
				Number(row.skipped_files ?? 0),
				bucketCount.get(row.sha) ?? 0,
			)
		) {
			complete.add(row.sha)
		}
	}
	return complete
}

export async function listStoredShas(): Promise<Set<string>> {
	await ensureSchema()
	const rows = await sqlAll(`SELECT sha FROM commits ORDER BY sha ASC`)
	return new Set(
		rows
			.map((row) => row.sha)
			.filter((sha): sha is string => typeof sha === 'string'),
	)
}

export async function upsertCommit(
	commit: IngestedCommit,
	buckets: BucketDelta[],
): Promise<void> {
	await ensureSchema()
	const db = bucket()
	await db.sql(
		`INSERT INTO commits (
			sha, committed_at, message, author_name, html_url, parent_sha,
			files_truncated, skipped_files, counted_files, ingested_at
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(sha) DO UPDATE SET
			committed_at = excluded.committed_at,
			message = excluded.message,
			author_name = excluded.author_name,
			html_url = excluded.html_url,
			parent_sha = excluded.parent_sha,
			files_truncated = excluded.files_truncated,
			skipped_files = excluded.skipped_files,
			counted_files = excluded.counted_files,
			ingested_at = excluded.ingested_at`,
		[
			commit.sha,
			commit.committedAt,
			commit.message,
			commit.authorName,
			commit.htmlUrl,
			commit.parentSha,
			commit.filesTruncated ? 1 : 0,
			commit.skippedFiles,
			commit.countedFiles,
			new Date().toISOString(),
		],
	)
	await db.sql(`DELETE FROM commit_buckets WHERE sha = ?`, [commit.sha])
	for (const item of buckets) {
		await db.sql(
			`INSERT INTO commit_buckets (sha, language, kind, additions, deletions)
			 VALUES (?, ?, ?, ?, ?)
			 ON CONFLICT(sha, language, kind) DO UPDATE SET
				additions = excluded.additions,
				deletions = excluded.deletions`,
			[commit.sha, item.language, item.kind, item.additions, item.deletions],
		)
	}
}

export async function loadCommitRows(beforeInclusive?: string | null) {
	await ensureSchema()
	const commits = beforeInclusive
		? await sqlAll(
				`SELECT sha, committed_at, message, html_url
				 FROM commits
				 WHERE committed_at <= ?
				 ORDER BY committed_at ASC, sha ASC`,
				[beforeInclusive],
			)
		: await sqlAll(
				`SELECT sha, committed_at, message, html_url
				 FROM commits
				 ORDER BY committed_at ASC, sha ASC`,
			)
	// Bucket rows outnumber commits (~3×); must page or cumulative stacks truncate.
	const buckets = beforeInclusive
		? await sqlAll(
				`SELECT b.sha, b.language, b.kind, b.additions, b.deletions
				 FROM commit_buckets b
				 JOIN commits c ON c.sha = b.sha
				 WHERE c.committed_at <= ?
				 ORDER BY b.sha ASC, b.language ASC, b.kind ASC`,
				[beforeInclusive],
			)
		: await sqlAll(
				`SELECT sha, language, kind, additions, deletions
				 FROM commit_buckets
				 ORDER BY sha ASC, language ASC, kind ASC`,
			)
	return { commits, buckets }
}

export async function ingestSummary() {
	await ensureSchema()
	const count = await bucket().sql(`SELECT COUNT(*) AS n FROM commits`)
	const newest = await bucket().sql(
		`SELECT sha, committed_at FROM commits ORDER BY committed_at DESC, sha DESC LIMIT 1`,
	)
	const oldest = await bucket().sql(
		`SELECT sha, committed_at FROM commits ORDER BY committed_at ASC, sha ASC LIMIT 1`,
	)
	return {
		storedCommits: Number(count.rows[0]?.n ?? 0),
		newest: asEdge(newest.rows[0]),
		oldest: asEdge(oldest.rows[0]),
	}
}

export async function listCommentaries(): Promise<Commentary[]> {
	await ensureSchema()
	const rows = await sqlAll(
		`SELECT id, sha, label, narrative, created_at
		 FROM commentaries
		 ORDER BY created_at ASC`,
	)
	return rows.map((row) => ({
		id: String(row.id),
		sha: String(row.sha),
		label: String(row.label),
		narrative: String(row.narrative),
		createdAt: String(row.created_at),
	}))
}

export async function upsertCommentary(input: {
	id?: string
	sha: string
	label: string
	narrative: string
}): Promise<Commentary> {
	await ensureSchema()
	const id = input.id?.trim() || crypto.randomUUID()
	const createdAt = new Date().toISOString()
	await bucket().sql(
		`INSERT INTO commentaries (id, sha, label, narrative, created_at)
		 VALUES (?, ?, ?, ?, ?)
		 ON CONFLICT(id) DO UPDATE SET
			sha = excluded.sha,
			label = excluded.label,
			narrative = excluded.narrative`,
		[id, input.sha.trim(), input.label.trim(), input.narrative.trim(), createdAt],
	)
	return {
		id,
		sha: input.sha.trim(),
		label: input.label.trim(),
		narrative: input.narrative.trim(),
		createdAt,
	}
}

export async function deleteCommentary(id: string): Promise<boolean> {
	await ensureSchema()
	const existing = await bucket().sql(
		`SELECT id FROM commentaries WHERE id = ?`,
		[id],
	)
	if (existing.rows.length === 0) return false
	await bucket().sql(`DELETE FROM commentaries WHERE id = ?`, [id])
	return true
}

function asEdge(row?: Record<string, unknown>) {
	if (!row || typeof row.sha !== 'string' || typeof row.committed_at !== 'string') {
		return null
	}
	return { sha: row.sha, at: row.committed_at }
}

export function isFileKind(value: string): value is FileKind {
	return value === 'source' || value === 'tests' || value === 'docs'
}