import { config } from "dotenv";
config({ override: true });
import { logger } from "@/lib/logger";
import { createClient } from "@libsql/client";
import Database from "better-sqlite3";
import { BetterSQLite3Database, drizzle } from "drizzle-orm/better-sqlite3";
import { drizzle as drizzleLibSQL, LibSQLDatabase } from "drizzle-orm/libsql";
import {
  drizzle as drizzlePg,
  NodePgDatabase,
} from "drizzle-orm/node-postgres";
import { existsSync, mkdirSync } from "fs";
import { dirname } from "path";
import pg from "pg";
import * as schema from "./schema";

// Database driver types
export type DatabaseDriver =
  | "sqlite"
  | "postgres"
  | "mysql"
  | "libsql"
  | "turso";

let db:
  | BetterSQLite3Database<typeof schema>
  | LibSQLDatabase<typeof schema>
  | NodePgDatabase<typeof schema>
  | null = null;

/**
 * Infer database driver from connection URL when no explicit driver is set.
 * Keeps `DATABASE_DRIVER` authoritative when present (production safety) but
 * makes `file:./test.db` work in CI without extra config.
 */
function inferDriverFromUrl(url: string): DatabaseDriver {
  if (url.startsWith("file:")) return "sqlite";
  if (url.startsWith("libsql:") || url.startsWith("https:")) return "libsql";
  if (url.startsWith("postgres://") || url.startsWith("postgresql://")) return "postgres";
  return "postgres";
}

/**
 * Get database configuration from environment
 */
function getDbConfig(): { driver: DatabaseDriver; url: string } {
  // process.env wins when explicitly set (CI runners, scripts, systemd),
  // then Astro/Vite import.meta.env (loaded from .env), then a sensible default.
  // This way CI can set `DATABASE_URL=file:./test.db` without a .env override.
  const envDriver =
    process.env.DATABASE_DRIVER || import.meta.env?.DATABASE_DRIVER;
  const envUrl =
    process.env.DATABASE_URL || import.meta.env?.DATABASE_URL;

  // Default to PostgreSQL since all schemas use pgTable
  const url =
    envUrl || "postgresql://opencodehub:opencodehub@localhost:5432/opencodehub";
  const driver = (envDriver || inferDriverFromUrl(url)) as DatabaseDriver;

  return { driver, url };
}

function isTruthy(value: string | undefined): boolean {
  return value === "1" || value?.toLowerCase() === "true";
}

function shouldRejectUnauthorized(value: string | undefined): boolean {
  if (value === undefined) return true;
  return !["0", "false"].includes(value.toLowerCase());
}

/**
 * Get or create database connection
 */
export function getDatabase():
  | BetterSQLite3Database<typeof schema>
  | LibSQLDatabase<typeof schema>
  | NodePgDatabase<typeof schema> {
  if (db) return db;

  const { driver, url } = getDbConfig();

  // Support for Turso/LibSQL
  if (driver === "libsql" || driver === "turso") {
    const authToken =
      import.meta.env?.DATABASE_AUTH_TOKEN || process.env.DATABASE_AUTH_TOKEN;
    const client = createClient({
      url,
      authToken,
    });
    db = drizzleLibSQL(client, { schema });
    logger.info({ driver: "libsql", url }, "Database connected (LibSQL)");
    return db;
  }

  // Support for PostgreSQL
  if (driver === "postgres") {
    const sslEnabled =
      isTruthy(process.env.DATABASE_SSL) || /[?&]sslmode=require/i.test(url);
    const rejectUnauthorized = shouldRejectUnauthorized(
      process.env.DATABASE_SSL_REJECT_UNAUTHORIZED,
    );

    const cleanUrl = url.replace(
      /([?&])sslmode=require(&|$)/i,
      (_, prefix, suffix) => (suffix === "&" ? prefix : ""),
    );
    const pool = new pg.Pool({
      connectionString: cleanUrl,
      ssl: sslEnabled ? { rejectUnauthorized } : undefined,
      max: parseInt(process.env.DATABASE_POOL_SIZE || "10", 10),
    });
    db = drizzlePg(pool, { schema });
    logger.info(
      {
        driver: "postgres",
        sslEnabled,
        rejectUnauthorized: sslEnabled ? rejectUnauthorized : undefined,
      },
      "Database connected (PostgreSQL)",
    );
    return db;
  }

  // Currently only SQLite, LibSQL and Postgres are fully supported
  if (driver === "mysql") {
    logger.warn(
      { driver, url },
      "MySQL driver validation pending. Falling back to SQLite.",
    );
  }

  const dbPath =
    driver === "sqlite" || driver === "mysql" ? url : "./data/opencodehub.db";

  // Ensure directory exists for local SQLite
  if (!url.includes("://")) {
    const dir = dirname(dbPath);
    if (!existsSync(dir)) {
      mkdirSync(dir, { recursive: true });
    }
  }

  const sqlite = new Database(dbPath);
  sqlite.pragma("journal_mode = WAL");
  sqlite.pragma("foreign_keys = ON");

  db = drizzle(sqlite, { schema });
  logger.info({ driver: "sqlite", path: dbPath }, "Database connected");

  return db;
}

/**
 * Get current database driver type
 */
export function getDriverType(): DatabaseDriver {
  const { driver } = getDbConfig();
  return driver;
}

/**
 * Check if database is connected
 */
export function isDatabaseConnected(): boolean {
  return db !== null;
}

/**
 * Close database connection
 */
export async function closeDatabase(): Promise<void> {
  if (db) {
    if ("close" in db) {
      // Handle SQLite close
      // @ts-ignore
      db.close();
    }
    // For PG pool, we might need to close the pool if we had access to it,
    // but Drizzle doesn't expose it directly on the db instance easily without type casting.
    // In serverless/long-running app, closing might not be strictly necessary unless ensuring graceful shutdown.

    logger.info("Database connection closed");
    db = null;
  }
}

/**
 * Reset database connection cache (used during setup wizard)
 */
export function resetDatabase(): void {
  db = null;
}

// Export schema and types
export { schema };
export type Database =
  | BetterSQLite3Database<typeof schema>
  | LibSQLDatabase<typeof schema>
  | NodePgDatabase<typeof schema>;