import postgres from "postgres";
import { drizzle } from "drizzle-orm/postgres-js";
import * as schema from "./schema";

export type Database = ReturnType<typeof drizzle<typeof schema>>;

let sqlClient: postgres.Sql | undefined;
let dbInstance: Database | undefined;

/** RDS / managed Postgres usually rejects "no encryption". Enable via
 * `?sslmode=require` on DATABASE_URL, or `DATABASE_SSL=true`. */
export function wantsDatabaseSsl(url = process.env.DATABASE_URL ?? ""): boolean {
  const lower = url.toLowerCase();
  if (lower.includes("sslmode=disable") || process.env.DATABASE_SSL === "false") return false;
  return (
    lower.includes("sslmode=require") ||
    lower.includes("sslmode=verify") ||
    lower.includes("sslmode=no-verify") ||
    lower.includes("ssl=true") ||
    process.env.DATABASE_SSL === "true" ||
    process.env.DATABASE_SSL === "require"
  );
}

/**
 * Shared SSL options for `postgres` / `pg` / pg-boss.
 * rejectUnauthorized:false is common for RDS when the app does not ship the
 * AWS CA bundle; the connection is still encrypted.
 */
export function databaseSslOption(url = process.env.DATABASE_URL ?? ""): { rejectUnauthorized: false } | undefined {
  return wantsDatabaseSsl(url) ? { rejectUnauthorized: false } : undefined;
}

/**
 * Recent `pg` treats `sslmode=require` in the URL as verify-full and **replaces**
 * any `ssl: { rejectUnauthorized: false }` you pass alongside `connectionString`.
 * Strip SSL query params, then set ssl explicitly for node-postgres consumers
 * (pg-boss, `pg.Pool`, etc.).
 */
export function stripSslQueryParams(connectionString: string): string {
  try {
    const parsed = new URL(connectionString);
    for (const key of [...parsed.searchParams.keys()]) {
      const lower = key.toLowerCase();
      if (lower === "uselibpqcompat" || lower.startsWith("ssl")) {
        parsed.searchParams.delete(key);
      }
    }
    return parsed.toString();
  } catch {
    return connectionString
      .replace(/([?&])(ssl[^&=]*=[^&]*|uselibpqcompat=[^&]*)/gi, "$1")
      .replace(/\?&/, "?")
      .replace(/[?&]$/, "");
  }
}

/** Config for pg-boss / node-postgres when DATABASE_URL may include sslmode=. */
export function nodePostgresConnection(connectionString: string): {
  connectionString: string;
  ssl?: { rejectUnauthorized: false };
} {
  const ssl = databaseSslOption(connectionString);
  return {
    connectionString: ssl ? stripSslQueryParams(connectionString) : connectionString,
    ...(ssl ? { ssl } : {}),
  };
}

export function getSql(): postgres.Sql {
  if (!sqlClient) {
    const url = process.env.DATABASE_URL ?? "postgres://slackwsh_app:slackwsh_app@localhost:5432/slackwsh";
    const ssl = databaseSslOption(url);
    sqlClient = postgres(url, {
      max: Number(process.env.DATABASE_POOL_MAX ?? 10),
      ...(ssl ? { ssl } : {}),
    });
  }
  return sqlClient;
}

export function getDb(): Database {
  if (!dbInstance) {
    dbInstance = drizzle(getSql(), { schema });
  }
  return dbInstance;
}