Adding Blog Comments, Likes, and a Belated Welcome Email

CREATE TABLE IF NOT EXISTS blog_comments (
  id         SERIAL PRIMARY KEY,
  slug       VARCHAR(500) NOT NULL,
  comment    VARCHAR(2000) NOT NULL,
  reaction   VARCHAR(10) NOT NULL DEFAULT 'up',
  email      VARCHAR(320) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_blog_comments_slug
  ON blog_comments (slug);

export async function GET(request: NextRequest) {
  const slug = request.nextUrl.searchParams.get("slug");

  if (!slug) {
    return NextResponse.json(
      { error: "Missing slug parameter." },
      { status: 400 }
    );
  }

  const sql = getDb();

  const comments = await sql`
    SELECT id, slug, comment, reaction, email, created_at
    FROM blog_comments
    WHERE slug = ${slug}
    ORDER BY created_at DESC
  `;

  const thumbsResult = await sql`
    SELECT COUNT(*)::int AS thumbs_up
    FROM blog_comments
    WHERE slug = ${slug} AND reaction = 'up'
  `;

  return NextResponse.json({
    comments,
    thumbsUp: thumbsResult[0]?.thumbs_up ?? 0,
  });
}

export async function POST(request: NextRequest) {
  const body = await request.json();
  const slug: string = (body.slug ?? "").trim();
  const comment: string = (body.comment ?? "").trim();
  const reaction: string = (body.reaction ?? "up").trim();
  const email: string = (body.email ?? "").toLowerCase().trim();

  // 1. Validate required fields
  if (!slug) {
    return NextResponse.json(
      { error: "Missing post slug." }, { status: 400 }
    );
  }

  // 2. Length validation
  if (!comment || comment.length < 2) {
    return NextResponse.json(
      { error: "Comment must be at least 2 characters." }, { status: 400 }
    );
  }
  if (comment.length > 2000) {
    return NextResponse.json(
      { error: "Comment must be 2000 characters or less." }, { status: 400 }
    );
  }

  // 3. Reaction validation
  if (!["up", "down"].includes(reaction)) {
    return NextResponse.json(
      { error: "Reaction must be 'up' or 'down'." }, { status: 400 }
    );
  }

  // 4. Email format validation
  if (!email || !isValidEmail(email)) {
    return NextResponse.json(
      { error: "Please enter a valid email address." }, { status: 400 }
    );
  }

  // 5. Subscriber verification
  const sql = getDb();
  const subscriber = await sql`
    SELECT id FROM subscribers
    WHERE email = ${email} AND active = TRUE
    LIMIT 1
  `;

  if (subscriber.length === 0) {
    return NextResponse.json(
      { error: "You must be a subscriber to comment. Subscribe above and try again!" },
      { status: 403 }
    );
  }

  // 6. Insert the comment
  const result = await sql`
    INSERT INTO blog_comments (slug, comment, reaction, email)
    VALUES (${slug}, ${comment}, ${reaction}, ${email})
    RETURNING id, slug, comment, reaction, email, created_at
  `;

  return NextResponse.json({ ok: true, comment: result[0] });
}

export async function DELETE(request: NextRequest, { params }: Props) {
  if (!verifyApiAuth(request)) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const { id } = await params;
  const commentId = parseInt(id, 10);

  if (isNaN(commentId) || commentId < 1) {
    return NextResponse.json(
      { error: "Invalid comment ID." }, { status: 400 }
    );
  }

  const sql = getDb();
  const result = await sql`
    DELETE FROM blog_comments WHERE id = ${commentId}
    RETURNING id
  `;

  if (result.length === 0) {
    return NextResponse.json(
      { error: "Comment not found." }, { status: 404 }
    );
  }

  return NextResponse.json({ ok: true, deleted: commentId });
}

"use client";

import { useState, useEffect, useCallback } from "react";
import { ThumbsUp, ThumbsDown, Loader2, MessageSquare, Send } from "lucide-react";

export function BlogComments({ slug, onThumbsUpCount }: BlogCommentsProps) {
  const [comments, setComments] = useState<Comment[]>([]);
  const [thumbsUp, setThumbsUp] = useState(0);
  const [loading, setLoading] = useState(true);

  // Form state
  const [comment, setComment] = useState("");
  const [reaction, setReaction] = useState<"up" | "down">("up");
  const [email, setEmail] = useState("");
  const [submitting, setSubmitting] = useState(false);
  const [submitStatus, setSubmitStatus] = useState<
    "idle" | "success" | "error"
  >("idle");

  // ... fetch and submit handlers
}

function maskEmail(email: string): string {
  const [local, domain] = email.split("@");
  if (!domain) return email;
  const visible = local.slice(0, 2);
  return `${visible}***@${domain}`;
}

export function BlogPostThumbsUp({ slug }: BlogPostThumbsUpProps) {
  const [count, setCount] = useState<number | null>(null);

  useEffect(() => {
    fetch(`/api/comments?slug=${encodeURIComponent(slug)}`)
      .then((r) => r.json())
      .then((d) => setCount(d.thumbsUp ?? 0))
      .catch(() => {});
  }, [slug]);

  if (count === null || count === 0) return null;

  return (
    <>
      <span>&middot;</span>
      <span className="inline-flex items-center gap-1">
        <ThumbsUp className="h-3.5 w-3.5 text-green-400" />
        {count}
      </span>
    </>
  );
}

const html = `
  <h1>A Belated Welcome - and a Thank You</h1>
  <p>
    Hey! I'm Chris, and I owe you a proper welcome. When you first
    subscribed to CryptoFlex, you should have received a welcome
    email, but that didn't happen, and I'm sorry about that.
  </p>
  <p>
    I'm still learning and building this site as I go, and I heard
    the feedback. The welcome email is now set up properly for new
    subscribers, but I didn't want to leave you hanging without one.
    So here it is - better late than never!
  </p>
  ...
`;

curl -X POST https://cryptoflexllc.com/api/send-welcome-blast `
  -H "Authorization: Bearer $CRON_SECRET"

curl.exe -L https://cryptoflexllc.com/api/send-welcome-blast `
  -H 'Authorization: Bearer d2030c...'

// Before: blocked at the edge
export async function POST(request: NextRequest) { ... }

// After: reaches the application
export async function GET(request: NextRequest) { ... }

< x-vercel-mitigated: deny

{
  "src": "/api/(?!analytics|subscribe|unsubscribe|cron/weekly-digest|comments|subscribers)(.*)",
  "mitigate": { "action": "deny" }
}

/api/send-welcome-blast          → BLOCKED

/api/subscribers/send-welcome-blast  → ALLOWED

curl.exe -sL https://www.cryptoflexllc.com/api/subscribers/send-welcome-blast `
  -H 'Authorization: Bearer d2030c...'

{"ok":true,"sent":12,"total":12,"errors":[]}

rm src/app/api/subscribers/send-welcome-blast/route.ts

async function sendConfirmationEmail(recipientEmail: string): Promise<void> {
  const transporter = nodemailer.createTransport({
    host: "smtp.gmail.com",
    port: 465,
    secure: true,
    auth: { user: gmailUser, pass: gmailPass },
  });

  const latestPosts = getAllPosts().slice(0, 5);
  const unsubLink = unsubscribeUrl(recipientEmail);

  // Build branded HTML with logo, welcome message,
  // newsletter schedule, and latest 5 posts
  const html = buildWelcomeHtml(latestPosts, unsubLink);

  await transporter.sendMail({
    from: `"CryptoFlex LLC" <${gmailUser}>`,
    to: recipientEmail,
    subject: "Welcome to CryptoFlex! Thanks for Subscribing",
    html,
    headers: {
      "List-Unsubscribe": `<${unsubLink}>`,
      "List-Unsubscribe-Post": "List-Unsubscribe=One-Click",
    },
  });
}

sendConfirmationEmail(email).catch((err) =>
  console.error("Confirmation email error:", err)
);