import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { dirname, join } from "node:path";
import { drizzle } from "drizzle-orm/bun-sqlite";
import * as schema from "./schema.ts";

export type DbHandle = {
	db: Database;
	drizzle: ReturnType<typeof drizzle<typeof schema>>;
};

export type Result<T, E = string> =
	| { ok: true; value: T }
	| { ok: false; error: E };

function ok<T>(value: T): Result<T, never> {
	return { ok: true, value };
}

function err<E>(error: E): Result<never, E> {
	return { ok: false, error };
}

/**
 * Create context tables: episodic_entries, semantic_entities, dependency_edges.
 */
function createContextTables(db: Database): void {
	db.exec(`
		CREATE TABLE IF NOT EXISTS episodic_entries (
			id TEXT PRIMARY KEY,
			content TEXT NOT NULL,
			summary TEXT,
			relevance REAL,
			access_count INTEGER,
			created_at TEXT NOT NULL,
			last_accessed_at TEXT,
			type TEXT NOT NULL
		);

		CREATE TABLE IF NOT EXISTS semantic_entities (
			id TEXT PRIMARY KEY,
			file_path TEXT NOT NULL,
			name TEXT NOT NULL,
			kind TEXT NOT NULL,
			start_line INTEGER NOT NULL,
			end_line INTEGER NOT NULL,
			updated_at TEXT NOT NULL
		);

		CREATE TABLE IF NOT EXISTS dependency_edges (
			id TEXT PRIMARY KEY,
			source_file TEXT NOT NULL,
			target_file TEXT NOT NULL,
			weight REAL,
			type TEXT NOT NULL
		);
	`);
}

/**
 * Create cache tables: cache_entries.
 */
function createCacheTables(db: Database): void {
	db.exec(`
		CREATE TABLE IF NOT EXISTS cache_entries (
			id TEXT PRIMARY KEY,
			key TEXT NOT NULL UNIQUE,
			value TEXT NOT NULL,
			prompt_version TEXT,
			context_hash TEXT,
			model TEXT,
			created_at TEXT NOT NULL,
			ttl INTEGER
		);
	`);
}

/**
 * Create feedback tables: feedback, prompt_versions, external_review_findings.
 */
function createFeedbackTables(db: Database): void {
	db.exec(`
		CREATE TABLE IF NOT EXISTS feedback (
			id TEXT PRIMARY KEY,
			prompt_hash TEXT NOT NULL,
			command TEXT NOT NULL,
			accepted INTEGER NOT NULL,
			context TEXT,
			created_at TEXT NOT NULL
		);

		CREATE TABLE IF NOT EXISTS prompt_versions (
			id TEXT PRIMARY KEY,
			task TEXT NOT NULL,
			hash TEXT NOT NULL,
			content TEXT NOT NULL,
			version INTEGER NOT NULL,
			accept_rate REAL,
			usage_count INTEGER,
			created_at TEXT NOT NULL
		);

		CREATE TABLE IF NOT EXISTS external_review_findings (
			id TEXT PRIMARY KEY,
			pr_number INTEGER NOT NULL,
			pr_repo TEXT NOT NULL,
			file_path TEXT,
			line INTEGER,
			reviewer TEXT NOT NULL,
			reviewer_kind TEXT NOT NULL,
			category TEXT NOT NULL,
			body TEXT NOT NULL,
			diff_at_review TEXT,
			ingested_at INTEGER NOT NULL,
			state TEXT NOT NULL DEFAULT 'active',
			source_id TEXT NOT NULL,
			UNIQUE(pr_repo, pr_number, source_id)
		);

		CREATE INDEX IF NOT EXISTS idx_external_findings_file
			ON external_review_findings(file_path);
		CREATE INDEX IF NOT EXISTS idx_external_findings_pr
			ON external_review_findings(pr_repo, pr_number);
		CREATE INDEX IF NOT EXISTS idx_external_findings_category
			ON external_review_findings(category);
	`);

	// Add workflow columns (nullable — backward compatible)
	try {
		db.exec(`ALTER TABLE feedback ADD COLUMN workflow_step TEXT`);
	} catch {
		// Column already exists — safe to ignore
	}
	try {
		db.exec(`ALTER TABLE feedback ADD COLUMN workflow_id TEXT`);
	} catch {
		// Column already exists — safe to ignore
	}
}

/**
 * Create stats tables: commit_snapshots.
 */
function createStatsTables(db: Database): void {
	db.exec(`
		CREATE TABLE IF NOT EXISTS commit_snapshots (
			id TEXT PRIMARY KEY,
			timestamp TEXT NOT NULL,
			branch TEXT NOT NULL,
			commit_hash TEXT NOT NULL,
			verify_duration_ms INTEGER NOT NULL,
			total_duration_ms INTEGER NOT NULL,
			context_tokens INTEGER NOT NULL,
			context_budget INTEGER NOT NULL,
			context_utilization REAL NOT NULL,
			cache_hits INTEGER NOT NULL,
			cache_misses INTEGER NOT NULL,
			findings_total INTEGER NOT NULL,
			findings_errors INTEGER NOT NULL,
			findings_warnings INTEGER NOT NULL,
			tools_run INTEGER NOT NULL,
			syntax_passed INTEGER NOT NULL,
			pipeline_passed INTEGER NOT NULL,
			skipped INTEGER NOT NULL DEFAULT 0
		);
	`);

	// Migration: add skipped column to existing tables
	try {
		db.exec(
			"ALTER TABLE commit_snapshots ADD COLUMN skipped INTEGER NOT NULL DEFAULT 0",
		);
	} catch {
		// Column already exists — safe to ignore
	}

	db.exec(`
		CREATE TABLE IF NOT EXISTS tool_usage (
			id TEXT PRIMARY KEY,
			tool TEXT NOT NULL,
			input_hash TEXT NOT NULL,
			duration_ms INTEGER NOT NULL,
			cache_hit INTEGER NOT NULL DEFAULT 0,
			timestamp TEXT NOT NULL,
			workflow_id TEXT
		);
	`);
}

/**
 * Initialise a SQLite database at the given path, creating parent directories as needed.
 * Accepts an optional table creator function; if omitted no tables are created.
 * Returns a Result containing the raw Database and the Drizzle ORM instance.
 * Never throws — all errors are returned as Err values.
 */
export function initDatabase(
	dbPath: string,
	tableCreator?: (db: Database) => void,
): Result<DbHandle> {
	try {
		mkdirSync(dirname(dbPath), { recursive: true });
		const db = new Database(dbPath, { create: true });
		db.exec("PRAGMA journal_mode=WAL;");
		if (tableCreator) {
			tableCreator(db);
		}
		const orm = drizzle(db, { schema });
		return ok({ db, drizzle: orm });
	} catch (e) {
		return err(e instanceof Error ? e.message : String(e));
	}
}

/**
 * Open the context database (.maina/context/index.db).
 * Contains: episodic_entries, semantic_entities, dependency_edges.
 */
export function getContextDb(mainaDir: string): Result<DbHandle> {
	return initDatabase(
		join(mainaDir, "context", "index.db"),
		createContextTables,
	);
}

/**
 * Open the cache database (.maina/cache/cache.db).
 * Contains: cache_entries.
 */
export function getCacheDb(mainaDir: string): Result<DbHandle> {
	return initDatabase(join(mainaDir, "cache", "cache.db"), createCacheTables);
}

/**
 * Open the feedback database (.maina/feedback.db).
 * Contains: feedback, prompt_versions.
 */
export function getFeedbackDb(mainaDir: string): Result<DbHandle> {
	return initDatabase(join(mainaDir, "feedback.db"), createFeedbackTables);
}

/**
 * Open the stats database (.maina/stats.db).
 * Contains: commit_snapshots.
 */
export function getStatsDb(mainaDir: string): Result<DbHandle> {
	return initDatabase(join(mainaDir, "stats.db"), createStatsTables);
}