import { join } from 'path';
import { mkdirSync } from 'fs';
import { Database } from 'bun:sqlite';
import type { DatabaseConnection } from '$shared/types/database/connection';

import { debug } from '$shared/utils/logger';
import { getClopenDir } from '../../utils/index.js';

export class DatabaseManager {
	private static instance: DatabaseManager | null = null;
	private db: DatabaseConnection | null = null;
	private readonly dbPath: string;

	private constructor() {
		this.dbPath = join(getClopenDir(), 'app.db');
	}

	static getInstance(): DatabaseManager {
		if (!DatabaseManager.instance) {
			DatabaseManager.instance = new DatabaseManager();
		}
		return DatabaseManager.instance;
	}

	async connect(): Promise<DatabaseConnection> {
		if (this.db) {
			return this.db;
		}

		debug.log('database', '🔗 Connecting to database...');

		try {
			// Ensure the data directory exists before SQLite touches the path.
			// This has to be reliable: on a first run the directory is missing, and
			// opening a database inside a missing directory fails in ways that
			// surface much later as "no such table". `mkdirSync` is atomic, creates
			// parents, and no-ops when the directory is already there.
			mkdirSync(getClopenDir(), { recursive: true });

			// Use Bun's native SQLite exclusively
			this.db = new Database(this.dbPath);

			// Configure database for optimal performance
			this.configurePragmas();

			debug.log('database', `✅ Connected to database at: ${this.dbPath}`);
			return this.db;

		} catch (error) {
			debug.error('database', '❌ Failed to connect to database:', error);
			throw error;
		}
	}

	private configurePragmas(): void {
		if (!this.db) return;

		debug.log('database', '⚙️  Configuring database pragmas...');

		// Bun SQLite uses exec for pragmas
		this.db.exec('PRAGMA journal_mode = WAL');
		this.db.exec('PRAGMA synchronous = NORMAL');
		this.db.exec('PRAGMA cache_size = 1000000');
		this.db.exec('PRAGMA temp_store = memory');
		this.db.exec('PRAGMA foreign_keys = ON');

		debug.log('database', '✅ Database pragmas configured');
	}

	getConnection(): DatabaseConnection {
		if (!this.db) {
			throw new Error('Database not connected. Call connect() first.');
		}
		return this.db;
	}

	isConnected(): boolean {
		return this.db !== null;
	}

	close(): void {
		if (this.db) {
			debug.log('database', '🔗 Closing database connection...');
			this.db.close();
			this.db = null;
			debug.log('database', '✅ Database connection closed');
		}
	}

	getPath(): string {
		return this.dbPath;
	}

	async resetDatabase(): Promise<void> {
		debug.log('database', '⚠️ Resetting database (dropping all tables)...');

		if (!this.db) {
			throw new Error('Database not connected');
		}

		// Disable foreign key checks to allow dropping in any order
		this.db.exec('PRAGMA foreign_keys = OFF');

		try {
			const tables = this.db.prepare(`
				SELECT name FROM sqlite_master
				WHERE type='table' AND name NOT LIKE 'sqlite_%'
			`).all() as { name: string }[];

			for (const table of tables) {
				debug.log('database', `🗑️ Dropping table: ${table.name}`);
				this.db.exec(`DROP TABLE IF EXISTS ${table.name}`);
			}

			debug.log('database', '✅ Database reset completed');
		} finally {
			this.db.exec('PRAGMA foreign_keys = ON');
		}
	}

	async vacuum(): Promise<void> {
		debug.log('database', '🧹 Running database vacuum...');
		
		if (!this.db) {
			throw new Error('Database not connected');
		}

		this.db.exec('VACUUM');
		debug.log('database', '✅ Database vacuum completed');
	}

	getDatabaseInfo(): object {
		if (!this.db) {
			throw new Error('Database not connected');
		}

		// Helper to get pragma values with Bun SQLite
		const getPragma = (name: string) => {
			const result = this.db!.query(`PRAGMA ${name}`).get() as any;
			return result ? Object.values(result)[0] : null;
		};
		
		return {
			path: this.dbPath,
			journalMode: getPragma('journal_mode'),
			synchronous: getPragma('synchronous'),
			cacheSize: getPragma('cache_size'),
			tempStore: getPragma('temp_store'),
			foreignKeys: getPragma('foreign_keys'),
			userVersion: getPragma('user_version'),
			pageSize: getPragma('page_size'),
			pageCount: getPragma('page_count'),
			freelistCount: getPragma('freelist_count')
		};
	}
}