import { packageStorage } from 'kody:runtime'
import type { OutcomeRecord, SweepRecord } from './shared.ts'

function storage() {
	return packageStorage()
}

async function sql(query: string, bindings: unknown[] = []) {
	return await storage().sql(query, bindings)
}

function rowsOf(result: { rows?: unknown[] } | unknown[] | null | undefined) {
	if (!result) return []
	if (Array.isArray(result)) return result
	return Array.isArray(result.rows) ? result.rows : []
}

export async function initSchema() {
	await sql(`
		CREATE TABLE IF NOT EXISTS config (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		)
	`)
	await sql(`
		CREATE TABLE IF NOT EXISTS sweeps (
			day TEXT PRIMARY KEY,
			scanned_at TEXT NOT NULL,
			eligible_count INTEGER NOT NULL,
			issue_numbers_json TEXT NOT NULL,
			spawned INTEGER NOT NULL,
			agent_id TEXT,
			agent_url TEXT,
			skipped TEXT,
			dry_run INTEGER NOT NULL
		)
	`)
	await sql(`
		CREATE TABLE IF NOT EXISTS outcomes (
			agent_id TEXT PRIMARY KEY,
			day TEXT NOT NULL,
			outcome TEXT NOT NULL,
			summary TEXT NOT NULL,
			pr_url TEXT,
			completed_at TEXT NOT NULL
		)
	`)
}

export async function isEnabled() {
	await initSchema()
	const rows = rowsOf(await sql('SELECT value FROM config WHERE key = ?', ['enabled']))
	const value = String((rows[0] as { value?: string } | undefined)?.value ?? 'true')
	return value !== 'false'
}

export async function setEnabled(enabled: boolean) {
	await initSchema()
	await sql(
		`INSERT INTO config (key, value) VALUES (?, ?)
		 ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
		['enabled', enabled ? 'true' : 'false'],
	)
}

export async function getSweep(day: string) {
	await initSchema()
	const rows = rowsOf(await sql('SELECT * FROM sweeps WHERE day = ?', [day]))
	return (rows[0] as SweepRecord | undefined) ?? null
}

export async function upsertSweep(record: SweepRecord) {
	await initSchema()
	await sql(
		`INSERT INTO sweeps (
			day, scanned_at, eligible_count, issue_numbers_json, spawned,
			agent_id, agent_url, skipped, dry_run
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(day) DO UPDATE SET
			scanned_at = excluded.scanned_at,
			eligible_count = excluded.eligible_count,
			issue_numbers_json = excluded.issue_numbers_json,
			spawned = excluded.spawned,
			agent_id = excluded.agent_id,
			agent_url = excluded.agent_url,
			skipped = excluded.skipped,
			dry_run = excluded.dry_run`,
		[
			record.day,
			record.scanned_at,
			record.eligible_count,
			record.issue_numbers_json,
			record.spawned,
			record.agent_id,
			record.agent_url,
			record.skipped,
			record.dry_run,
		],
	)
}

export async function listRecentSweeps(limit = 7) {
	await initSchema()
	return rowsOf(
		await sql('SELECT * FROM sweeps ORDER BY day DESC LIMIT ?', [limit]),
	) as Array<SweepRecord>
}

export async function latestLiveSweep() {
	await initSchema()
	const rows = rowsOf(
		await sql(
			`SELECT * FROM sweeps
			 WHERE dry_run = 0 AND spawned = 1 AND agent_id IS NOT NULL
			 ORDER BY scanned_at DESC LIMIT 1`,
		),
	)
	return (rows[0] as SweepRecord | undefined) ?? null
}

export async function recordOutcome(record: OutcomeRecord) {
	await initSchema()
	await sql(
		`INSERT INTO outcomes (
			agent_id, day, outcome, summary, pr_url, completed_at
		) VALUES (?, ?, ?, ?, ?, ?)
		ON CONFLICT(agent_id) DO UPDATE SET
			outcome = excluded.outcome,
			summary = excluded.summary,
			pr_url = excluded.pr_url,
			completed_at = excluded.completed_at`,
		[
			record.agent_id,
			record.day,
			record.outcome,
			record.summary,
			record.pr_url,
			record.completed_at,
		],
	)
}

export async function listRecentOutcomes(limit = 10) {
	await initSchema()
	return rowsOf(
		await sql('SELECT * FROM outcomes ORDER BY completed_at DESC LIMIT ?', [limit]),
	) as Array<OutcomeRecord>
}