grain.social/grain

Switch repository

Loading…

import type { BaseContext } from "$hatk";

/** SQL condition that excludes orphaned replies (parent comment was deleted). */
export const NOT_ORPHANED = `(c.reply_to IS NULL OR EXISTS (
  SELECT 1 FROM "social.grain.comment" p WHERE p.uri = c.reply_to
))`;

/** Count non-orphaned comments grouped by subject URI. */
export async function countComments(
  db: BaseContext["db"],
  subjectUris: string[],
): Promise<Map<string, number>> {
  if (subjectUris.length === 0) return new Map();
  const placeholders = subjectUris.map((_, i) => `$${i + 1}`).join(",");
  const rows = (await db.query(
    `SELECT c.subject, COUNT(*) as count FROM "social.grain.comment" c
     LEFT JOIN _repos r ON r.did = c.did
     WHERE c.subject IN (${placeholders}) AND (r.status IS NULL OR r.status NOT IN ('takendown', 'deactivated', 'deleted'))
       AND ${NOT_ORPHANED}
     GROUP BY c.subject`,
    subjectUris,
  )) as { subject: string; count: number }[];
  const m = new Map<string, number>();
  for (const r of rows) m.set(r.subject, Number(r.count));
  return m;
}