import { Database } from "bun:sqlite";
import { drizzle } from "drizzle-orm/bun-sqlite";
import { join } from "path";
import { homedir } from "os";
import * as schema from "./schema.ts";

/**
 * Database instance type for dependency injection
 */
export type DbInstance = ReturnType<typeof drizzle>;

let db: DbInstance | null = null;
let sqliteDb: Database | null = null;

/**
 * Get the database file path
 */
export function getDbPath(): string {
  const xdg = process.env.XDG_DATA_HOME ?? join(homedir(), ".local", "share");
  return join(xdg, "gitforest", "cache.db");
}

/**
 * Initialize the database connection and create tables if needed
 */
export async function initDb(): Promise<ReturnType<typeof drizzle>> {
  if (db) return db;

  const dbPath = getDbPath();
  const dbDir = join(dbPath, "..");

  // Ensure directory exists
  await Bun.$`mkdir -p ${dbDir}`.quiet();

  // Create SQLite connection
  sqliteDb = new Database(dbPath);

  // Enable WAL mode for better concurrent access
  sqliteDb.exec("PRAGMA journal_mode = WAL");

  // Create tables if they don't exist
  sqliteDb.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      path TEXT NOT NULL UNIQUE,
      type TEXT NOT NULL,
      project_marker TEXT,
      status_json TEXT,
      submodule_json TEXT,
      last_scanned INTEGER,
      last_modified INTEGER
    );

    CREATE TABLE IF NOT EXISTS remote_status (
      project_id TEXT PRIMARY KEY REFERENCES projects(id) ON DELETE CASCADE,
      last_fetched INTEGER,
      unpulled_commits INTEGER,
      remote_last_activity INTEGER,
      remote_url TEXT
    );

    CREATE TABLE IF NOT EXISTS github_repos (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      full_name TEXT NOT NULL UNIQUE,
      owner TEXT NOT NULL,
      description TEXT,
      html_url TEXT,
      ssh_url TEXT,
      clone_url TEXT,
      is_private INTEGER,
      is_archived INTEGER,
      is_fork INTEGER,
      pushed_at INTEGER,
      updated_at INTEGER,
      default_branch TEXT,
      language TEXT,
      size INTEGER,
      stargazers_count INTEGER,
      forks_count INTEGER,
      open_issues_count INTEGER,
      watchers_count INTEGER,
      topics TEXT,
      license TEXT,
      has_issues INTEGER,
      has_wiki INTEGER,
      has_discussions INTEGER,
      last_fetched INTEGER
    );

    CREATE TABLE IF NOT EXISTS config_cache (
      key TEXT PRIMARY KEY,
      value TEXT,
      updated_at INTEGER
    );

    CREATE INDEX IF NOT EXISTS idx_projects_path ON projects(path);
    CREATE INDEX IF NOT EXISTS idx_projects_type ON projects(type);
    CREATE INDEX IF NOT EXISTS idx_github_repos_owner ON github_repos(owner);
    CREATE INDEX IF NOT EXISTS idx_github_repos_fetched ON github_repos(last_fetched);
  `);

  // Lightweight ALTER for existing DBs created before lastModified existed.
  // SQLite ignores duplicate add via try/catch.
  try {
    sqliteDb.exec(`ALTER TABLE projects ADD COLUMN last_modified INTEGER`);
  } catch {
    // Column already exists; safe to ignore.
  }

  // Create Drizzle instance
  db = drizzle(sqliteDb, { schema });

  return db;
}

/**
 * Close the database connection
 */
export function closeDb(): void {
  if (sqliteDb) {
    sqliteDb.close();
    sqliteDb = null;
    db = null;
  }
}

/**
 * Get the database instance (must call initDb first)
 */
export function getDb(): ReturnType<typeof drizzle> {
  if (!db) {
    throw new Error("Database not initialized. Call initDb() first.");
  }
  return db;
}

/**
 * Clear all cached data
 */
export async function clearCache(): Promise<void> {
  try {
    const database = await initDb();

    // Clear every cache table; otherwise stale GitHub data survives a "cache clear".
    await Promise.all([
      database.delete(schema.projects).run(),
      database.delete(schema.remoteStatus).run(),
      database.delete(schema.githubRepos).run(),
      database.delete(schema.configCache).run(),
    ]);
  } catch (error) {
    throw new Error(`Failed to clear cache: ${error instanceof Error ? error.message : String(error)}`);
  }
}

export { schema };