app-261004/scripts/seed-posts.mjs

/**
 * 把 content/posts/ 下的 Markdown 文件导入数据库。
 *
 * 用途：一次性把现有的文件式内容迁移进 posts 表，
 * 让数据库成为唯一内容源。
 *
 * 行为：
 * - 幂等：按 slug upsert（存在则更新，不存在则插入）
 * - 解析 YAML frontmatter 并做与 API 相同的校验
 * - `draft: true` 映射为 status='draft'
 * - 打印每条记录的处理结果
 *
 * 用法：
 *   node scripts/seed-posts.mjs            # 导入 / 更新
 *   node scripts/seed-posts.mjs --dry-run  # 只解析并打印，不写库
 */

import { readdirSync, readFileSync, existsSync } from "node:fs";
import { join, extname } from "node:path";
import pg from "pg";
import matter from "gray-matter";
import { config } from "dotenv";

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

const DRY_RUN = process.argv.includes("--dry-run");
const POSTS_DIR = join(process.cwd(), "content", "posts");

if (!existsSync(POSTS_DIR)) {
	console.error(`内容目录不存在: ${POSTS_DIR}`);
	process.exit(1);
}

/** 估算阅读时长（与 lib/validation/post.ts 保持一致） */
function estimateReadingMinutes(content) {
	const chineseCharacters = content.match(/[\u4e00-\u9fa5]/g)?.length ?? 0;
	const latinWords =
		content.replace(/[\u4e00-\u9fa5]/g, " ").match(/[A-Za-z0-9]+/g)?.length ??
		0;
	return Math.max(1, Math.round(chineseCharacters / 350 + latinWords / 200));
}

/** 从 frontmatter 解析出可写入数据库的记录 */
function parseFile(fileName) {
	const slug = fileName.replace(/\.(mdx|md)$/i, "");
	const raw = readFileSync(join(POSTS_DIR, fileName), "utf8");
	const { data, content } = matter(raw);

	const problems = [];
	const title = typeof data.title === "string" ? data.title.trim() : "";
	if (!title) problems.push("缺少 title");

	let date = "";
	if (data.date instanceof Date && !Number.isNaN(data.date.getTime())) {
		date = data.date.toISOString().slice(0, 10);
	} else if (typeof data.date === "string" && data.date.trim()) {
		date = data.date.trim();
	}
	if (!date || Number.isNaN(new Date(date).getTime())) {
		problems.push("缺少或非法的 date");
	}

	const summary = typeof data.summary === "string" ? data.summary.trim() : "";
	if (!summary) problems.push("缺少 summary");

	if (!/^[a-z0-9]+(-[a-z0-9]+)*$/.test(slug)) {
		problems.push(`slug "${slug}" 不符合 ^[a-z0-9]+(-[a-z0-9]+)*$（数据库 CHECK 约束会拒绝）`);
	}

	if (problems.length > 0) {
		return { slug, error: problems.join("; ") };
	}

	return {
		slug,
		title,
		summary,
		content,
		tags: Array.isArray(data.tags) ? data.tags.map(String) : [],
		status: data.draft === true ? "draft" : "published",
		publishedAt: new Date(`${date}T00:00:00Z`),
		cover: typeof data.cover === "string" ? data.cover : null,
		description:
			typeof data.description === "string" ? data.description : null,
		readingMinutes: estimateReadingMinutes(content),
	};
}

const files = readdirSync(POSTS_DIR).filter((f) =>
	[".md", ".mdx"].includes(extname(f).toLowerCase()),
);

if (files.length === 0) {
	console.log("content/posts/ 下没有 Markdown 文件");
	process.exit(0);
}

console.log(`发现 ${files.length} 个内容文件${DRY_RUN ? "（dry-run，不写库）" : ""}\n`);

const records = [];
for (const file of files) {
	const parsed = parseFile(file);
	if (parsed.error) {
		console.error(`✗ ${file}: ${parsed.error}`);
		process.exitCode = 1;
		continue;
	}
	records.push(parsed);
}

if (process.exitCode === 1) {
	console.error("\n存在解析失败的文件，已中止导入。");
	process.exit(1);
}

if (DRY_RUN) {
	for (const r of records) {
		console.log(
			`  ${r.status === "draft" ? "[草稿]" : "[已发布]"} ${r.slug}  日期=${r.publishedAt
				.toISOString()
				.slice(0, 10)}  标签=${r.tags.join(",") || "-"}  ${r.readingMinutes}分钟`,
		);
	}
	process.exit(0);
}

const client = new pg.Client({
	connectionString: process.env.DATABASE_URL,
	ssl: /localhost|127\.0\.0\.1/.test(process.env.DATABASE_URL ?? "")
		? undefined
		: { rejectUnauthorized: false },
});

await client.connect();

let inserted = 0;
let updated = 0;

try {
	for (const r of records) {
		// upsert：以 slug 为冲突键
		const result = await client.query(
			`insert into posts
			   (slug, title, summary, content, tags, status, published_at,
			    cover, description, reading_minutes, created_at, updated_at)
			 values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10, now(), now())
			 on conflict (slug) do update set
			   title = excluded.title,
			   summary = excluded.summary,
			   content = excluded.content,
			   tags = excluded.tags,
			   status = excluded.status,
			   published_at = excluded.published_at,
			   cover = excluded.cover,
			   description = excluded.description,
			   reading_minutes = excluded.reading_minutes,
			   updated_at = now()
			 returning (xmax = 0) as is_insert`,
			[
				r.slug,
				r.title,
				r.summary,
				r.content,
				r.tags,
				r.status,
				r.status === "published" ? r.publishedAt : null,
				r.cover,
				r.description,
				r.readingMinutes,
			],
		);

		const isInsert = result.rows[0].is_insert;
		if (isInsert) inserted++;
		else updated++;

		console.log(
			`  ${isInsert ? "+ 新增" : "~ 更新"} ${r.slug} (${r.status}, ${r.readingMinutes} 分钟)`,
		);
	}

	const total = await client.query("select count(*)::int as n from posts");
	console.log(`\n完成：新增 ${inserted}，更新 ${updated}，表内共 ${total.rows[0].n} 行`);
} catch (error) {
	console.error("\n导入失败:", error.message);
	process.exitCode = 1;
} finally {
	await client.end().catch(() => {});
}