import os from "node:os";
import path from "node:path";
import tls, { type ConnectionOptions } from "node:tls";
import { instrumentDrizzleClient } from "@kubiks/otel-drizzle";
import { getLogger } from "@logtape/logtape";
import { sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate as migrateDb } from "drizzle-orm/node-postgres/migrator";
import type { PoolConfig } from "pg";
import { record } from "~/otel";
import * as schema from "./schema";

const logger = getLogger();

const config: PoolConfig = {
	connectionString: process.env.POSTGRES_URL,
	host: process.env.PGHOST ?? "postgres",
	port: Number(process.env.PGPORT) || 5432,
	database: process.env.PGDATABASE ?? "kyoo",
	user: process.env.PGUSER ?? "kyoo",
	password: process.env.PGPASSWORD ?? "password",
	options: process.env.PGOPTIONS,
	application_name: process.env.PGAPPNAME ?? "kyoo.api",
};

async function parseSslConfig(): Promise<PoolConfig> {
	// Due to an upstream bug, if `ssl` is not falsey, an SSL connection will always be attempted. This means
	// that non-SSL connection options under `ssl` (which is incorrectly named) cannot be set unless SSL is enabled.
	if (!process.env.PGSSLMODE || process.env.PGSSLMODE === "disable") {
		config.ssl = false;
		return config;
	}

	// Despite this field's name, it is used to configure everything below the application layer.
	const ssl: ConnectionOptions = {};

	if (process.env.PGPASSFILE || !process.env.PGPASSWORD) {
		const file = Bun.file(
			process.env.PGPASSFILE ?? path.join(os.homedir(), ".pgpass"),
		);
		if (await file.exists()) {
			config.password = await file.text();
		}
	}

	// Handle https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLROOTCERT
	if (process.env.PGSSLROOTCERT === "system") {
		// Bun does not currently support getCACertificates. Until this is supported,
		// use the closest equivalent, which loads the bundled CA certs rather than the system CA certs.
		// ssl.ca = tls.getCACertificates("system");
		ssl.ca = [...tls.rootCertificates];

		if (!process.env.PGSSLMODE) {
			process.env.PGSSLMODE = "verify-full";
		}
		if (process.env.PGSSLMODE && process.env.PGSSLMODE !== "verify-full") {
			throw new Error(
				"PGSSLROOTCERT=system is only supported with PGSSLMODE=verify-full. See Postgres docs for details.",
			);
		}
	} else {
		const file = Bun.file(
			process.env.PGSSLROOTCERT ??
				path.join(os.homedir(), ".postgresql", "root.crt"),
		);
		if (await file.exists()) {
			ssl.ca = await file.text();
		}
	}

	// TODO support CRLs. This requires verifying the contents against symlink hashes prepared by `openssl c_rehash`,
	// as described in the postgres docs for PGSSLCRL and PGSSLCRLDIR. This isn't terribly common, so it's not currently
	// implemented.

	let file = Bun.file(
		process.env.PGSSLCERT ??
			path.join(os.homedir(), ".postgresql", "postgresql.crt"),
	);
	if (await file.exists()) {
		ssl.cert = await file.text();
	}

	file = Bun.file(
		process.env.PGSSLKEY ??
			path.join(os.homedir(), ".postgresql", "postgresql.key"),
	);
	if (await file.exists()) {
		ssl.key = await file.text();
	}

	if (process.env.PGSSLMODE) {
		switch (process.env.PGSSLMODE) {
			// Disable is handled above, gateing the configurating of any SSL options.
			// 'prefer' and 'allow' cannot be fully supported without multiple attempted
			// connections or upstream driver changes; the closest we can do with a
			// single connection is to attempt SSL with no certificate validation, so
			// an SSL-capable server still negotiates TLS while an SSL-disabled server
			// surfaces a connection error the operator can act on.
			case "allow":
			case "prefer":
				logger.info(
					"PGSSLMODE={mode} requested: attempting SSL without certificate validation",
					{ mode: process.env.PGSSLMODE },
				);
				ssl.checkServerIdentity = (_host, _cert) => {
					return undefined;
				};
				ssl.rejectUnauthorized = false;
				break;
			case "verify-ca":
				ssl.rejectUnauthorized = true;
				ssl.checkServerIdentity = (_host, _cert) => {
					return undefined;
				};
				break;
			case "verify-full":
				ssl.rejectUnauthorized = true;
				break;
			default:
				ssl.checkServerIdentity = (_host, _cert) => {
					return undefined;
				};
				ssl.rejectUnauthorized = false;
		}
	}

	config.ssl = ssl;
	return config;
}

const postgresConfig = await parseSslConfig();
// use this when using drizzle-kit since it can't parse await statements
// const postgresConfig = config;

export const dbRaw = drizzle({
	schema,
	connection: postgresConfig,
	casing: "snake_case",
});

export const db = drizzle({
	schema,
	connection: postgresConfig,
	casing: "snake_case",
});
instrumentDrizzleClient(db, {
	maxQueryTextLength: 100_000_000,
});

export const migrate = record("migrate", async () => {
	const APP_SCHEMA = "kyoo";
	logger.info("Connecting to postgres with {config}", {
		config: postgresConfig,
	});
	try {
		logger.info("Setting up database");
		await db.execute(
			sql.raw(`
				create schema if not exists ${APP_SCHEMA};
				create extension if not exists pg_trgm schema ${APP_SCHEMA};
				set pg_trgm.word_similarity_threshold = 0.4;
				alter database "${postgresConfig.database}" set pg_trgm.word_similarity_threshold = 0.4;
			`),
		);
	} catch (err: any) {
		logger.error("Setting up database failed: {error}", {
			error: err.message,
		});
	}
	await migrateDb(db, {
		migrationsSchema: APP_SCHEMA,
		migrationsFolder: "./drizzle",
	});
	logger.info("Database {database} migrated", {
		database: postgresConfig.database,
	});
});

export type Transaction =
	| typeof db
	| Parameters<Parameters<typeof db.transaction>[0]>[0];
