[Astro's Comment] Phần 1 - Xây dựng hệ thống comment với Cloudflare D1 + Drizzle

{
  "main": "@astrojs/cloudflare/entrypoints/server",
  "name": "blog-thuanbui",
  "compatibility_flags": ["nodejs_compat"],
  "compatibility_date": "2026-03-17",
  "assets": {
    "directory": "./dist/client",
    "not_found_handling": "404-page",
  },
  "d1_databases": [
    {
      "binding": "COMMENTS_DB",
      "database_name": "blog-thuanbui-comments",
      "database_id": "7ffe7eee-9d2b-4958-befd-2404d710d903",
    },
  ],
}

import { defineConfig } from "astro/config";
import cloudflare from "@astrojs/cloudflare";

export default defineConfig({
  output: "server",
  adapter: cloudflare({
    persistState: true, // giữ local D1 data giữa các lần restart
  }),
});

{
  "dependencies": {
    "drizzle-orm": "latest",
    "@astrojs/cloudflare": "latest",
    "@astrojs/svelte": "latest"
  },
  "devDependencies": {
    "drizzle-kit": "latest",
    "@types/node": "latest"
  }
}

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const comments = sqliteTable("comments", {
  id: integer("id").primaryKey({ autoIncrement: true }),

  // Related post
  postSlug: text("post_slug").notNull(),

  // Commenter info
  authorName: text("author_name").notNull(),
  authorEmail: text("author_email").notNull(),
  authorUrl: text("author_url"), // optional website

  // Content
  content: text("content").notNull(),

  // Timestamp (ISO 8601 string since D1/SQLite has no native datetime)
  createdAt: text("created_at").notNull(),

  // Approval status (0 = pending, 1 = approved, 2 = deleted)
  // Default 0 — trusted emails are set to 1 directly in POST handler
  status: integer("status").notNull().default(0),

  // Reply support (nested comments, null = top-level)
  parentId: integer("parent_id"),

  // Discord message ID to update embed after approve/delete
  discordMessageId: text("discord_message_id"),

  // Original WordPress comment ID — only used when migrating from WP XML to resolve parent_id
  // After migration, this column is no longer used but kept for reference
  wpCommentId: integer("wp_comment_id"),

  // WordPress parent comment ID — used during WP XML migration to resolve nested comments
  // After migration, this column is no longer used but kept for reference
  wpParentId: integer("wp_parent_id"),
});

// Emails approved once → automatically approved for subsequent comments
export const trustedEmails = sqliteTable("trusted_emails", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  email: text("email").notNull().unique(),
  addedAt: text("added_at").notNull(),
});

export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;

import type { Config } from "drizzle-kit";

const { LOCAL_DB_PATH, DB_ID, D1_TOKEN, CF_ACCOUNT_ID } = process.env;

export default LOCAL_DB_PATH
  ? ({
      // Local dev with wrangler
      schema: "./src/db/schema.ts",
      out: "./migrations",
      dialect: "sqlite",
      dbCredentials: {
        url: LOCAL_DB_PATH,
      },
    } satisfies Config)
  : ({
      // Push migrations to D1 production
      schema: "./src/db/schema.ts",
      out: "./migrations",
      dialect: "sqlite",
      driver: "d1-http",
      dbCredentials: {
        databaseId: DB_ID!,
        token: D1_TOKEN!,
        accountId: CF_ACCOUNT_ID!,
      },
    } satisfies Config);

{
  "scripts": {
    "dev": "astro dev",
    "build": "astro build",

    "db:generate": "drizzle-kit generate",
    "db:migrate:local": "wrangler d1 migrations apply blog-thuanbui-comments --local",
    "db:migrate:prod": "wrangler d1 migrations apply blog-thuanbui-comments --remote",
    "db:studio:local": "LOCAL_DB_PATH=$(find .wrangler/state/v3/d1/miniflare-D1DatabaseObject -type f -name '*.sqlite' -print -quit) drizzle-kit studio"
  }
}

import type { APIContext } from "astro";
import { drizzle } from "drizzle-orm/d1";
import { eq, and, desc } from "drizzle-orm";
import { env } from "cloudflare:workers";
import { comments, trustedEmails } from "../../../db/schema";

export const prerender = false;

// ─── GET: Lấy danh sách comment của một bài ────────────────────────────────
export async function GET({ params }: APIContext) {
  const { slug } = params;
  if (!slug) {
    return new Response(JSON.stringify({ error: "Missing slug" }), {
      status: 400,
      headers: { "Content-Type": "application/json" },
    });
  }

  try {
    const db = drizzle(env.COMMENTS_DB, { schema: { comments } });

    const result = await db
      .select({
        id: comments.id,
        authorName: comments.authorName,
        authorUrl: comments.authorUrl,
        content: comments.content,
        createdAt: comments.createdAt,
        parentId: comments.parentId,
      })
      .from(comments)
      .where(and(eq(comments.postSlug, slug), eq(comments.status, 1)))
      .orderBy(desc(comments.createdAt));

    return new Response(JSON.stringify({ comments: result }), {
      status: 200,
      headers: { "Content-Type": "application/json" },
    });
  } catch (error) {
    console.error("GET comments error:", error);
    return new Response(JSON.stringify({ error: "Database error" }), {
      status: 500,
      headers: { "Content-Type": "application/json" },
    });
  }
}

// ─── POST: Thêm comment mới ────────────────────────────────────────────────
export async function POST({ params, request }: APIContext) {
  const { slug } = params;
  if (!slug) {
    return new Response(JSON.stringify({ error: "Missing slug" }), {
      status: 400,
      headers: { "Content-Type": "application/json" },
    });
  }

  let body: {
    authorName?: string;
    authorEmail?: string;
    authorUrl?: string;
    content?: string;
    parentId?: number;
  };

  try {
    body = await request.json();
  } catch {
    return new Response(JSON.stringify({ error: "Invalid JSON" }), {
      status: 400,
      headers: { "Content-Type": "application/json" },
    });
  }

  const { authorName, authorEmail, content, authorUrl, parentId } = body;

  if (!authorName?.trim() || !authorEmail?.trim() || !content?.trim()) {
    return new Response(
      JSON.stringify({ error: "Tên, email và nội dung là bắt buộc" }),
      { status: 400, headers: { "Content-Type": "application/json" } }
    );
  }

  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!emailRegex.test(authorEmail)) {
    return new Response(JSON.stringify({ error: "Email không hợp lệ" }), {
      status: 400,
      headers: { "Content-Type": "application/json" },
    });
  }

  if (content.trim().length > 5000) {
    return new Response(
      JSON.stringify({ error: "Comment quá dài (tối đa 5000 ký tự)" }),
      {
        status: 400,
        headers: { "Content-Type": "application/json" },
      }
    );
  }

  try {
    const db = drizzle(env.COMMENTS_DB);
    const normalizedEmail = authorEmail.trim().toLowerCase();

    // ── Kiểm tra email đã trusted chưa ──────────────────────────────────
    const trusted = await db
      .select()
      .from(trustedEmails)
      .where(eq(trustedEmails.email, normalizedEmail))
      .limit(1);

    const isTrusted = trusted.length > 0;
    const initialStatus = isTrusted ? 1 : 0;

    // ── Insert comment ───────────────────────────────────────────────────
    await db
      .insert(comments)
      .values({
        postSlug: slug,
        authorName: authorName.trim().slice(0, 100),
        authorEmail: normalizedEmail,
        authorUrl: authorUrl?.trim() || null,
        content: content.trim(),
        createdAt: new Date().toISOString(),
        status: initialStatus,
        parentId: parentId || null,
      })
      .returning();

    // ── Gửi Discord notification nếu pending (xem supplement guide) ─────
    // if (!isTrusted) { await sendCommentNotification(...) }

    return new Response(
      JSON.stringify({
        success: true,
        pending: !isTrusted,
        message: isTrusted
          ? "Bình luận đã được đăng."
          : "Bình luận của bạn đang chờ duyệt.",
      }),
      { status: 201, headers: { "Content-Type": "application/json" } }
    );
  } catch (error) {
    console.error("POST comment error:", error);
    return new Response(JSON.stringify({ error: "Database error" }), {
      status: 500,
      headers: { "Content-Type": "application/json" },
    });
  }
}

<CommentsIsland {slug} client:visible />

import { defineConfig } from "astro/config";
import cloudflare from "@astrojs/cloudflare";
import svelte from "@astrojs/svelte";

export default defineConfig({
  output: "server",
  adapter: cloudflare({
    persistState: true, // giữ local D1 data giữa các lần restart
  }),
  integrations: [svelte()],
});

<script lang="ts">
  import { onMount } from "svelte";
  import CommentItem from "./CommentItem.svelte";

  // Props — Svelte 5 runes
  let { slug }: { slug: string } = $props();

  // ── Types ──────────────────────────────────────────────────────────────
  interface Comment {
    id: number;
    authorName: string;
    authorUrl: string | null;
    content: string;
    createdAt: string;
    parentId: number | null;
  }

  type FormStatus = "idle" | "loading" | "success" | "pending" | "error";

  // ── State ──────────────────────────────────────────────────────────────
  let comments = $state<Comment[]>([]);
  let loadError = $state(false);
  let formStatus = $state<FormStatus>("idle");
  let formError = $state("");
  let replyTo = $state<{ id: number; name: string } | null>(null);

  let form = $state({
    authorName: "",
    authorEmail: "",
    authorUrl: "",
    content: "",
  });

  // ── Load comments on mount ─────────────────────────────────────────────
  onMount(async () => {
    try {
      const res = await fetch(`/api/comments/${encodeURIComponent(slug)}`);
      if (!res.ok) throw new Error("Failed to load comments");
      const data = (await res.json()) as { comments?: Comment[] };
      comments = data.comments ?? [];
    } catch {
      loadError = true;
    }
  });

  // ── Precomputed grouped structure: parentId -> children (avoids O(n²) filtering) ──
  let grouped = $derived(() => {
    const map = new Map<number | null, Comment[]>();
    for (const c of comments) {
      const key = c.parentId;
      if (!map.has(key)) map.set(key, []);
      map.get(key)!.push(c);
    }
    return map;
  });
  let topLevel = $derived(grouped().get(null) ?? []);

  // ── Helpers ────────────────────────────────────────────────────────────
  function formatDate(iso: string) {
    return new Date(iso).toLocaleDateString("vi-VN", {
      year: "numeric",
      month: "long",
      day: "numeric",
    });
  }

  function repliesFor(parentId: number) {
    return grouped().get(parentId) ?? [];
  }

  /** Defense-in-depth: only render href for safe http/https URLs */
  function isSafeUrl(url: string | null): boolean {
    if (!url) return false;
    try {
      const parsed = new URL(url);
      return parsed.protocol === "http:" || parsed.protocol === "https:";
    } catch {
      return false;
    }
  }

  // ── Submit ─────────────────────────────────────────────────────────────
  async function handleSubmit(e: SubmitEvent) {
    e.preventDefault();
    formStatus = "loading";
    formError = "";

    try {
      const res = await fetch(`/api/comments/${slug}`, {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({
          ...form,
          parentId: replyTo?.id ?? null,
        }),
      });

      const data = (await res.json()) as {
        error?: string;
        success?: boolean;
        pending?: boolean;
        message?: string;
      };
      if (!res.ok) throw new Error(data.error || "Something went wrong");

      formStatus = data.pending ? "pending" : "success";
      form = { authorName: "", authorEmail: "", authorUrl: "", content: "" };
      replyTo = null;

      // Reload comments if approved immediately
      if (!data.pending) {
        const refresh = await fetch(`/api/comments/${encodeURIComponent(slug)}`);
        if (refresh.ok) {
          const refreshData = (await refresh.json()) as {
            comments?: Comment[];
          };
          comments = refreshData.comments ?? [];
        }
        formStatus = "idle";
      }
    } catch (err) {
      formStatus = "error";
      formError = err instanceof Error ? err.message : "Something went wrong";
    }
  }
</script>

<!-- ── Comment list ──────────────────────────────────────────────────────── -->
<section id="comments" class="mt-12 border-t border-border pt-8">
  {#if loadError}
    <p class="text-sm text-muted-foreground">Unable to load comments.</p>
  {:else if comments.length === 0}
    <p class="text-sm text-muted-foreground">
      No comments yet. Be the first to comment!
    </p>
  {:else}
    <h2 class="mb-6 text-xl font-bold">{comments.length} Comments</h2>
    <ol class="list-none p-0 space-y-0">
      {#each topLevel as comment (comment.id)}
        <CommentItem
          {comment}
          {repliesFor}
          {replyTo}
          onReply={(id, name) => { replyTo = { id, name }; document.getElementById("comment-form")?.scrollIntoView({ behavior: "smooth", block: "start" }); }}
          {formatDate}
          {isSafeUrl}
        />
      {/each}
    </ol>
  {/if}

  <!-- ── Form ──────────────────────────────────────────────────────────── -->
  <div id="comment-form" class="mt-10">
    <h3 class="mb-4 text-lg font-bold">
      {replyTo ? `Reply to ${replyTo.name}` : "Leave a Comment"}
    </h3>

    {#if replyTo}
      <div
        class="mb-4 flex items-center gap-3 rounded border border-border bg-muted/30 px-4 py-2 text-sm"
      >
        <span class="text-muted-foreground"
          >Replying to <strong>{replyTo.name}</strong></span
        >
        <button
          onclick={() => (replyTo = null)}
          class="ml-auto text-xs text-muted-foreground hover:text-foreground bg-transparent border-none cursor-pointer"
        >
          Cancel
        </button>
      </div>
    {/if}

    {#if formStatus === "pending"}
      <div
        class="rounded border border-amber-200 bg-amber-50 px-4 py-3 text-sm text-amber-800 dark:border-amber-800 dark:bg-amber-950 dark:text-amber-300"
      >
        ⏳ Your comment is pending approval. It will be visible once approved.
      </div>
    {:else}
      {#if formStatus === "error"}
        <div
          class="mb-4 rounded border border-red-200 bg-red-50 px-4 py-3 text-sm text-red-800 dark:border-red-800 dark:bg-red-950 dark:text-red-300"
        >
          ❌ {formError}
        </div>
      {/if}

      <form onsubmit={handleSubmit} novalidate class="space-y-4">
        <div class="grid grid-cols-1 gap-4 sm:grid-cols-2">
          <div class="flex flex-col gap-1">
            <label for="authorName" class="text-sm font-medium">
              Name <span class="text-red-500">*</span>
            </label>
            <input
              id="authorName"
              type="text"
              bind:value={form.authorName}
              required
              maxlength="100"
              placeholder="Thuan Bui"
              disabled={formStatus === "loading"}
              class="rounded border border-border bg-background px-3 py-2 text-sm focus:outline-2 focus:outline-accent disabled:opacity-50"
            />
          </div>
          <div class="flex flex-col gap-1">
            <label for="authorEmail" class="text-sm font-medium">
              Email <span class="text-red-500">*</span>
            </label>
            <input
              id="authorEmail"
              type="email"
              bind:value={form.authorEmail}
              required
              placeholder="email@example.com"
              disabled={formStatus === "loading"}
              class="rounded border border-border bg-background px-3 py-2 text-sm focus:outline-2 focus:outline-accent disabled:opacity-50"
            />
            <small class="text-xs text-muted-foreground"
              >Not publicly displayed</small
            >
          </div>
        </div>

        <div class="flex flex-col gap-1">
          <label for="authorUrl" class="text-sm font-medium">
            Website <span class="text-muted-foreground">(optional)</span>
          </label>
          <input
            id="authorUrl"
            type="url"
            bind:value={form.authorUrl}
            placeholder="https://example.com"
            disabled={formStatus === "loading"}
            class="rounded border border-border bg-background px-3 py-2 text-sm focus:outline-2 focus:outline-accent disabled:opacity-50"
          />
        </div>

        <div class="flex flex-col gap-1">
          <label for="content" class="text-sm font-medium">
            Comment <span class="text-red-500">*</span>
          </label>
          <textarea
            id="content"
            bind:value={form.content}
            required
            rows="5"
            maxlength="5000"
            placeholder="Share your thoughts..."
            disabled={formStatus === "loading"}
            class="resize-y rounded border border-border bg-background px-3 py-2 text-sm focus:outline-2 focus:outline-accent disabled:opacity-50"
          ></textarea>
        </div>

        <button
          type="submit"
          disabled={formStatus === "loading"}
          class="rounded bg-accent px-5 py-2 text-sm font-semibold text-background hover:opacity-80 disabled:cursor-not-allowed disabled:opacity-50"
        >
          {formStatus === "loading" ? "Submitting..." : "Post Comment"}
        </button>
      </form>
    {/if}
  </div>
</section>

<script lang="ts">
  import CommentItem from "./CommentItem.svelte";

  interface Comment {
    id: number;
    authorName: string;
    authorUrl: string | null;
    content: string;
    createdAt: string;
    parentId: number | null;
  }

  let {
    comment,
    repliesFor,
    replyTo,
    onReply,
    formatDate,
    isSafeUrl,
  }: {
    comment: Comment;
    repliesFor: (parentId: number) => Comment[];
    replyTo: { id: number; name: string } | null;
    onReply: (id: number, name: string) => void;
    formatDate: (iso: string) => string;
    isSafeUrl: (url: string | null) => boolean;
  } = $props();

  let children = $derived(repliesFor(comment.id));
</script>

<li id="comment-{comment.id}" class={!comment.parentId ? "pt-6" : ""}>
  <div class="flex items-center gap-3 mb-2 text-sm text-muted-foreground">
    <strong class="text-foreground font-semibold">
      {#if isSafeUrl(comment.authorUrl)}
        <a
          href={comment.authorUrl}
          rel="noopener noreferrer"
          target="_blank"
          class="text-accent no-underline hover:underline"
        >
          {comment.authorName}
        </a>
      {:else}
        {comment.authorName}
      {/if}
    </strong>
    <time datetime={comment.createdAt}>{formatDate(comment.createdAt)}</time>
  </div>
  <p class="text-sm leading-relaxed text-muted-foreground break-words overflow-wrap-anywhere">{comment.content}</p>

  <button
    onclick={() => onReply(comment.id, comment.authorName)}
    class="mt-1 text-xs text-muted-foreground hover:text-foreground cursor-pointer bg-transparent border-none p-0"
  >
    Reply
  </button>

  <!-- Recursive replies -->
  {#if children.length > 0}
    <ol class="mt-4 list-none border-l-2 border-border pl-4 space-y-4">
      {#each children as child (child.id)}
        <CommentItem
          comment={child}
          {repliesFor}
          {replyTo}
          {onReply}
          {formatDate}
          {isSafeUrl}
        />
      {/each}
    </ol>
  {/if}
</li>

import CommentsIsland from "../components/CommentsIsland.svelte"

<article>
  <slot />
</article>
<!-- Island load sau khi page render — blog vẫn là static -->
<CommentsIsland {slug} client:load />

#!/usr/bin/env node
// scripts/import-wp-comments.mjs
// Run: node scripts/import-wp-comments.mjs --file wordpress.xml [--dry-run]
/* eslint-disable no-console */

import { readFileSync } from "fs";
import { execSync } from "child_process";
import { tmpdir } from "os";
import { join } from "path";

const args = process.argv.slice(2);
const fileArg = args.find((_, i) => args[i - 1] === "--file");
const isDryRun = args.includes("--dry-run");
const isRemote = args.includes("--remote"); // add this flag to push to production

if (!fileArg) {
  console.error(
    "Usage: node scripts/import-wp-comments.mjs --file wordpress.xml [--dry-run] [--remote]"
  );
  process.exit(1);
}

const DB_NAME = "blog-thuanbui-comments";

// ─── Parse XML manually (no external dependencies) ──────────────────
function getTagValue(xml, tag) {
  // Handle both CDATA and regular text
  const cdataPattern = new RegExp(
    `<${tag}[^>]*><!\\[CDATA\\[([\\s\\S]*?)\\]\\]></${tag}>`,
    "i"
  );
  const normalPattern = new RegExp(`<${tag}[^>]*>([^<]*)</${tag}>`, "i");

  const cdataMatch = xml.match(cdataPattern);
  if (cdataMatch) return cdataMatch[1].trim();

  const normalMatch = xml.match(normalPattern);
  if (normalMatch) return normalMatch[1].trim();

  return "";
}

// ─── Read and parse WordPress XML ───────────────────────────────────────────
console.log(`Reading file: ${fileArg}`);
const xmlContent = readFileSync(fileArg, "utf-8");

// Get all <item> (posts)
const itemMatches = [];
let searchPos = 0;
while (true) {
  const itemStart = xmlContent.indexOf("<item>", searchPos);
  if (itemStart === -1) break;
  const itemEnd = xmlContent.indexOf("</item>", itemStart);
  if (itemEnd === -1) break;
  itemMatches.push(xmlContent.slice(itemStart, itemEnd + 7));
  searchPos = itemEnd + 7;
}

console.log(`Found ${itemMatches.length} posts in XML`);

// ─── Parse comments from each post ──────────────────────────────────────
const allComments = [];
const allEmails = new Set();

for (const item of itemMatches) {
  const postSlug = getTagValue(item, "wp:post_name");
  const postStatus = getTagValue(item, "wp:status");
  const postType = getTagValue(item, "wp:post_type");

  // Only process published posts (post or page)
  if (postStatus !== "publish" || !["post", "page"].includes(postType))
    continue;
  if (!postSlug) continue;

  // Get all <wp:comment> in this item
  const commentMatches = [];
  let cPos = 0;
  const OPEN = "<wp:comment>";
  const CLOSE = "</wp:comment>";
  while (true) {
    const cStart = item.indexOf(OPEN, cPos);
    if (cStart === -1) break;
    const cEnd = item.indexOf(CLOSE, cStart);
    if (cEnd === -1) break;
    commentMatches.push(item.slice(cStart, cEnd + CLOSE.length));
    cPos = cEnd + CLOSE.length;
  }

  for (const commentXml of commentMatches) {
    const wpId = getTagValue(commentXml, "wp:comment_id");
    const authorName = getTagValue(commentXml, "wp:comment_author");
    const authorEmail = getTagValue(commentXml, "wp:comment_author_email");
    const authorUrl = getTagValue(commentXml, "wp:comment_author_url");
    const content = getTagValue(commentXml, "wp:comment_content");
    const dateGmt = getTagValue(commentXml, "wp:comment_date_gmt");
    const approved = getTagValue(commentXml, "wp:comment_approved");
    const parentId = getTagValue(commentXml, "wp:comment_parent");
    const commentType = getTagValue(commentXml, "wp:comment_type");

    // Skip pingback, trackback and spam
    if (commentType === "pingback" || commentType === "trackback") continue;
    if (approved !== "1") continue; // only import approved comments
    if (!authorName || !content) continue;

    const email = authorEmail || "noreply@migrated.local";
    if (email && email !== "noreply@migrated.local") {
      allEmails.add(email);
    }

    allComments.push({
      wpId: parseInt(wpId) || null,
      wpParentId: parseInt(parentId) || 0,
      postSlug,
      authorName,
      authorEmail: email,
      authorUrl: authorUrl || null,
      content,
      createdAt: dateGmt
        ? new Date(dateGmt + "Z").toISOString()
        : new Date().toISOString(),
      status: 1,
    });
  }
}

console.log(`Total comments to import: ${allComments.length}`);

if (isDryRun) {
  console.log("\nDRY RUN - Not actually inserting into database");
  console.log("First 5 comments:");
  allComments.slice(0, 5).forEach((c, i) => {
    console.log(
      `  ${i + 1}. [${c.postSlug}] ${c.authorName}: "${c.content.slice(0, 60)}..."`
    );
  });
  console.log(`\nRun again without --dry-run to actually import.`);
  process.exit(0);
}

// ─── Insert all comments, using wp_parent_id to resolve parent_id later ──
const CHUNK_SIZE = 50;
const chunks = [];
for (let i = 0; i < allComments.length; i += CHUNK_SIZE) {
  chunks.push(allComments.slice(i, i + CHUNK_SIZE));
}

console.log(`\nInsert ${chunks.length} batch...`);

let totalInserted = 0;

for (let i = 0; i < chunks.length; i++) {
  const chunk = chunks[i];
  const { writeFileSync } = await import("fs");
  const tmpFile = join(tmpdir(), `wp-import-batch-${i}.sql`);

  // wp_parent_id is temp column to resolve parent_id
  const sql = `INSERT INTO comments (post_slug, author_name, author_email, author_url, content, created_at, status, wp_comment_id, wp_parent_id) VALUES\n    ${chunk
    .map(c => {
      const escape = s => (s ? String(s).replace(/'/g, "''") : "");
      return `('${escape(c.postSlug)}', '${escape(c.authorName)}', '${escape(c.authorEmail)}', ${c.authorUrl ? `'${escape(c.authorUrl)}'` : "NULL"}, '${escape(c.content)}', '${c.createdAt}', ${c.status}, ${c.wpId ?? "NULL"}, ${c.wpParentId})`;
    })
    .join(",\n    ")};`;

  writeFileSync(tmpFile, sql, "utf-8");

  const remoteFlag = isRemote ? "--remote --yes" : "--local";
  const cmd = `npx wrangler d1 execute ${DB_NAME} ${remoteFlag} --file ${tmpFile}`;

  try {
    execSync(cmd, { stdio: "inherit" });
    totalInserted += chunk.length;
    console.log(
      `Batch ${i + 1}/${chunks.length} - ${totalInserted}/${allComments.length}`
    );
  } catch (err) {
    console.error(`Error batch ${i + 1}:`, err.message);
    process.exit(1);
  }
}

// ─── Resolve parent_id: child.wp_parent_id → parent.wp_comment_id ────────
console.log(`\nResolve parent_id for nested comments...`);

const { writeFileSync: ws2 } = await import("fs");
const resolveFile = join(tmpdir(), "wp-resolve-parents.sql");

// Join table with itself: child.wp_parent_id = parent.wp_comment_id → child.parent_id = parent.id
const resolveSql = `
UPDATE comments AS child
SET parent_id = (
  SELECT id FROM comments AS parent
  WHERE parent.wp_comment_id = child.wp_parent_id
  AND parent.wp_comment_id IS NOT NULL
  LIMIT 1
)
WHERE child.wp_parent_id IS NOT NULL
  AND child.wp_parent_id != 0
  AND child.parent_id IS NULL;
`;

ws2(resolveFile, resolveSql, "utf-8");

try {
  const remoteFlag = isRemote ? "--remote --yes" : "--local";
  execSync(
    `npx wrangler d1 execute ${DB_NAME} ${remoteFlag} --file ${resolveFile}`,
    { stdio: "inherit" }
  );
  console.log(`Resolve parent_id completed`);
} catch (err) {
  console.error("Error resolving parent_id:", err.message);
}

console.log(`\nMigration complete! ${totalInserted} comments migrated to D1.`);

// ─── Insert trusted emails ──────────────────────────────────────────────────
if (allEmails.size > 0) {
  console.log(`Inserting ${allEmails.size} trusted emails...`);
  const { writeFileSync: ws3 } = await import("fs");
  const emailsFile = join(tmpdir(), "wp-trusted-emails.sql");
  const now = new Date().toISOString();
  const emailsSql = `INSERT INTO trusted_emails (email, added_at) VALUES\n    ${Array.from(
    allEmails
  )
    .map(email => {
      const escape = s => (s ? String(s).replace(/'/g, "''") : "");
      return `('${escape(email)}', '${now}')`;
    })
    .join(",\n    ")};`;

  ws3(emailsFile, emailsSql, "utf-8");

  try {
    const remoteFlag = isRemote ? "--remote --yes" : "--local";
    execSync(
      `npx wrangler d1 execute ${DB_NAME} ${remoteFlag} --file ${emailsFile}`,
      { stdio: "inherit" }
    );
    console.log(`Trusted emails added.`);
  } catch (err) {
    console.error("Error inserting trusted emails:", err.message);
  }
}