/**
 * 诊断 DATABASE_URL 与驱动/SSL 配置是否匹配。
 *
 * 覆盖两类常见问题：
 * 1. **驱动不匹配**：@neondatabase/serverless 的 neon-http 走 Neon 的 HTTP 代理，
 *    无法连接普通 Postgres（需改用 pg）。
 * 2. **SSL 不匹配**：本地 Postgres 默认不开 SSL，若连接串带 sslmode=require
 *    会报 "The server does not support SSL connections"。
 *
 * 用法：node scripts/db-diagnose.mjs
 */

import { config } from "dotenv";
import net from "node:net";
import pg from "pg";

config({ path: ".env.local" });

const url = process.env.DATABASE_URL;
if (!url) {
	console.error("缺少 DATABASE_URL");
	process.exit(1);
}

let parsed;
try {
	parsed = new URL(url);
} catch {
	console.error(`DATABASE_URL 不是合法 URL: ${url}`);
	process.exit(1);
}

const host = parsed.hostname;
const port = Number(parsed.port || 5432);
const database = parsed.pathname.replace(/^\//, "");
const sslmode = parsed.searchParams.get("sslmode");
const isNeonHost = /\.neon\.tech$/i.test(host);
const isLocal = /^(localhost|127\.0\.0\.1|::1)$/.test(host);

console.log("── DATABASE_URL 解析 ─────────────────");
console.log(`  host:     ${host}`);
console.log(`  port:     ${port}`);
console.log(`  database: ${database}`);
console.log(`  sslmode:  ${sslmode ?? "(未指定)"}`);
console.log(`  Neon 托管域名: ${isNeonHost ? "是" : "否"}`);
console.log(`  本地地址: ${isLocal ? "是" : "否"}\n`);

/** 探测 TCP 端口是否可连接 */
function probeTcp(hostname, tcpPort, timeoutMs = 3000) {
	return new Promise((resolve) => {
		const socket = net.createConnection({ host: hostname, port: tcpPort });
		const done = (result) => {
			socket.destroy();
			resolve(result);
		};

		socket.setTimeout(timeoutMs);
		socket.once("connect", () => done(true));
		socket.once("timeout", () => done(false));
		socket.once("error", () => done(false));
	});
}

const tcpReachable = await probeTcp(host, port);

console.log("── 网络连通性 ────────────────────────");
console.log(`  TCP ${host}:${port} 可达: ${tcpReachable ? "是" : "否"}\n`);

// 依次尝试「按 sslmode」与「不启用 SSL」，判断是否为 SSL 配置问题
async function tryConnect(useSsl) {
	const client = new pg.Client({
		connectionString: url,
		ssl: useSsl ? { rejectUnauthorized: false } : undefined,
		connectionTimeoutMillis: 5000,
	});

	try {
		await client.connect();
		await client.query("select 1");
		await client.end();
		return { ok: true };
	} catch (error) {
		await client.end().catch(() => {});
		return { ok: false, error: error.message };
	}
}

console.log("── 实际连接测试 ──────────────────────");
const withConfiguredSsl = await tryConnect(
	sslmode === "disable" ? false : sslmode ? true : !isLocal,
);
console.log(
	`  按当前 sslmode 连接: ${withConfiguredSsl.ok ? "✓ 成功" : `✗ ${withConfiguredSsl.error}`}`,
);

let oppositeResult = null;
if (!withConfiguredSsl.ok) {
	oppositeResult = await tryConnect(!(sslmode === "disable"));
	console.log(
		`  改用相反 SSL 设置:  ${oppositeResult.ok ? "✓ 成功" : `✗ ${oppositeResult.error}`}`,
	);
}
console.log("");

console.log("── 结论 ──────────────────────────────");
if (withConfiguredSsl.ok) {
	console.log("  ✓ 配置正确，数据库可用。");
} else if (oppositeResult?.ok) {
	console.log("  ✗ SSL 配置与服务器不匹配。");
	console.log(
		isLocal
			? "    本地 Postgres 默认不启用 SSL，请把连接串改为 ?sslmode=disable"
			: "    该服务器要求 SSL，请在连接串中加上 ?sslmode=require",
	);
} else if (!tcpReachable) {
	console.log("  ✗ 端口不可达：请确认数据库已启动、主机与端口正确。");
} else {
	console.log("  ✗ 连接失败，且与 SSL 无关，请检查用户名/密码/数据库名。");
	console.log("  → 若使用 neon-http 驱动连接普通 Postgres，需改用 pg 驱动。");
}