Patternflow

// ── Community queries: patterns — the feed, one pattern, its comments, likes, ports and performances ──
// One of the files server/queries.ts is assembled from (2026-09). Bodies are
// unchanged from the single 1,364-line file they came out of.

import { and, asc, desc, eq, inArray, or, sql } from "drizzle-orm";
import { getDb } from "../db";
import { comments, featuredPatterns, likes, patternHeaders, patternPerformances, patterns, user } from "../schema";
import { authorFields, deckCount, forkCount, hasCpp, likeCount } from "./shared";

export function newId(): string {
  return crypto.randomUUID().replace(/-/g, "").slice(0, 16);
}

/** Feed ordering. All of them are all-time; add a time window once volume
 *  justifies it.
 *
 *  "decks" was deliberately left out while decks were a side feature — with a
 *  handful of them it would have reordered the wall on almost no signal. The
 *  redesign makes a deck the thing the community is FOR (a handful of public slots a
 *  person, a curated shelf on the decks page), so the signal is now the
 *  scarcest one on the site and earns its tab. */
/* "liked" is the odd one out: a filter wearing a sort's clothes. It shows only
 * what the viewer has liked, newest first, and it exists because a like was
 * write-only — you could press it and never find the pattern again. It replaces
 * a separate "Saved" list that lived in localStorage, so it was per-browser and
 * gone the moment you cleared site data. A like was already server-side and
 * per-account; the list was the only part missing. */
/* "old" is newest turned around. At almost two hundred patterns the early work
 * sat a long scroll down a newest-first wall, which is a way of saying nobody
 * saw it; the marquee picker needed the same view to reach it, and had been
 * asking for it with a separate order=asc flag that only this sort honoured.
 * One sort value instead of a flag means one URL for it (?sort=old) on the
 * wall, the picker and the API alike. */
export const FEED_SORTS = ["new", "old", "top", "forks", "decks", "liked"] as const;

export type FeedSort = (typeof FEED_SORTS)[number];

export function parseFeedSort(raw: string | undefined): FeedSort {
  return FEED_SORTS.includes(raw as FeedSort) ? (raw as FeedSort) : "new";
}

export type FeedItem = {
  id: string;
  title: string;
  code: string;
  parentId: string | null;
  createdAt: Date;
  username: string | null;
  displayUsername: string | null;
  likeCount: number;
  forkCount: number;
  hasCpp: boolean;
  /** "public" | "private" — always "public" in the feed itself. */
  visibility: string;
  /** Distinct other people whose public decks carry this pattern. */
  deckCount: number;
};

const feedColumns = {
  id: patterns.id,
  title: patterns.title,
  code: patterns.code,
  parentId: patterns.parentId,
  createdAt: patterns.createdAt,
  visibility: patterns.visibility,
  ...authorFields,
  likeCount,
  forkCount,
  hasCpp,
  deckCount,
};

type FeedRow = Omit<FeedItem, "hasCpp"> & { hasCpp: number | boolean };

function toFeedItems(rows: FeedRow[]): FeedItem[] {
  // SQLite has no boolean type — the `IS NOT NULL` expression comes back as 0/1.
  return rows.map((row) => ({ ...row, hasCpp: Boolean(row.hasCpp) }));
}

// The feed shows public patterns only. Private rows live in the same table, so
// EVERY listing query needs this — a miss here is a leak (#255).
const feedVisible = eq(patterns.visibility, "public");

// The hardware filter and the card chip must agree, so both read `hasCpp`.
const hardwareReady = sql`${hasCpp} = 1`;

/** Longest search the feed will run. Titles cap at 80 too (cleanTitle). */
const FEED_QUERY_MAX = 80;

// LIKE's wildcards are the search's own characters to a person typing — a
// title with "100%" or a handle like "made_by" in it has to match itself, not
// everything. '!' is the escape, not backslash, so the pattern reads the same
// in JS, in SQL and in a log line — and '!' itself is escaped like the others.
function likeContains(text: string) {
  return `%${text.replace(/[!%_]/g, (ch) => `!${ch}`)}%`;
}

/**
 * The text search behind the wall's search box and the marquee picker: a
 * substring of the title or the author's handle, or — written as "@name" — of
 * the handle alone. Case-insensitive the way SQLite's LIKE is (ASCII only),
 * which is what handles are.
 *
 * Only ever ANDed onto feedVisible by the callers below, so a search can
 * narrow the public set but never widen it: a private title that matches is
 * still not there.
 *
 * Returns undefined for "no search", which drizzle's and() drops — so an empty
 * box is the same query the feed ran before search existed.
 */
function feedSearch(raw: string | null | undefined) {
  const term = (raw ?? "").trim().slice(0, FEED_QUERY_MAX);
  const byAuthor = term.startsWith("@");
  const needle = (byAuthor ? term.slice(1) : term).trim();
  if (!needle) return undefined;
  const pattern = likeContains(needle);
  const handle = or(
    sql`${user.username} LIKE ${pattern} ESCAPE '!'`,
    sql`${user.displayUsername} LIKE ${pattern} ESCAPE '!'`,
  );
  return byAuthor ? handle : or(sql`${patterns.title} LIKE ${pattern} ESCAPE '!'`, handle);
}

/**
 * "Liked" is a subset, not an order: the viewer's own likes, still under
 * feedVisible like everything else. Undefined for every other sort, which
 * drizzle's and() drops.
 */
function likedBy(sort: FeedSort, viewerId: string | null) {
  return sort === "liked"
    ? sql`EXISTS (SELECT 1 FROM ${likes} WHERE ${likes.patternId} = ${patterns.id} AND ${likes.userId} = ${viewerId})`
    : undefined;
}

/**
 * How many patterns match the current view — the wall's "N of M", and what
 * tells its infinite scroll to keep going. Takes the same sort and viewer as
 * listFeed, because "Liked" narrows the set: counting the whole wall there
 * stopped the scroll after the first batch (or never, the other way round).
 */
export async function countFeed(
  hardwareOnly = false,
  {
    q,
    sort = "new",
    viewerId = null,
  }: { q?: string | null; sort?: FeedSort; viewerId?: string | null } = {},
): Promise<number> {
  // Mirrors listFeed: signed out, the liked list is empty.
  if (sort === "liked" && !viewerId) return 0;
  const search = feedSearch(q);
  const base = getDb().select({ count: sql<number>`COUNT(*)` }).from(patterns);
  // The author join is only needed to search handles; without a search the
  // count stays the single-table query it always was.
  const rows = await (search ? base.innerJoin(user, eq(patterns.userId, user.id)) : base).where(
    and(feedVisible, likedBy(sort, viewerId), hardwareOnly ? hardwareReady : undefined, search),
  );
  return rows[0]?.count ?? 0;
}

export async function listFeed({
  sort = "new",
  hardwareOnly = false,
  limit = 60,
  offset = 0,
  viewerId = null,
  q = null,
}: {
  sort?: FeedSort;
  hardwareOnly?: boolean;
  limit?: number;
  offset?: number;
  /** Required by `sort: "liked"` — whose likes to list. */
  viewerId?: string | null;
  /** Title or handle substring; "@name" searches handles only. */
  q?: string | null;
} = {}): Promise<FeedItem[]> {
  const db = getDb();

  // A signed-out visitor has no likes, and answering with the whole wall
  // would be worse than answering with nothing: the tab says "the ones you
  // liked". Empty is the honest reply.
  if (sort === "liked" && !viewerId) return [];
  // Every ordering but "old" falls back to newest-first so results are stable
  // when the primary key ties (which it does constantly while counts are near zero).
  const order =
    sort === "top"
      ? [desc(likeCount), desc(patterns.createdAt)]
      : sort === "forks"
        ? [desc(forkCount), desc(patterns.createdAt)]
        : sort === "decks"
          ? [desc(deckCount), desc(patterns.createdAt)]
          : // Oldest first is how the early work the newest-first wall has
            // long since buried gets seen again. The id breaks ties so
            // offsets page through equal timestamps without repeats.
            sort === "old"
            ? [asc(patterns.createdAt), asc(patterns.id)]
            : [desc(patterns.createdAt)];

  // Still subject to feedVisible, like every other listing. Having liked
  // something is not a standing right to keep reading it: if the author takes
  // it private afterwards, it leaves your list too. The alternative would turn
  // a like into a way to hold a copy of work somebody withdrew.
  const likedByViewer = likedBy(sort, viewerId);

  const rows = await db
    .select(feedColumns)
    .from(patterns)
    .innerJoin(user, eq(patterns.userId, user.id))
    .where(and(feedVisible, likedByViewer, hardwareOnly ? hardwareReady : undefined, feedSearch(q)))
    .orderBy(...order)
    .limit(limit)
    .offset(offset);
  return toFeedItems(rows);
}

/**
 * The marquee's patterns, in the order a moderator put them.
 *
 * Anything that has stopped being public since it was featured drops out
 * silently rather than 404-ing on the front page — the same `feedVisible` rule
 * every other listing obeys.
 */
export async function listFeatured(limit = 8): Promise<FeedItem[]> {
  const rows = await getDb()
    .select(feedColumns)
    .from(featuredPatterns)
    .innerJoin(patterns, eq(featuredPatterns.patternId, patterns.id))
    .innerJoin(user, eq(patterns.userId, user.id))
    .where(feedVisible)
    .orderBy(featuredPatterns.position, desc(featuredPatterns.createdAt))
    .limit(limit);
  return toFeedItems(rows);
}

/** Ids only — for the admin page's "already in the marquee" marks. */
export async function listFeaturedIds(): Promise<string[]> {
  const rows = await getDb()
    .select({ patternId: featuredPatterns.patternId })
    .from(featuredPatterns)
    .orderBy(featuredPatterns.position);
  return rows.map((row) => row.patternId);
}

export async function getPattern(id: string) {
  const db = getDb();
  const rows = await db
    .select({
      id: patterns.id,
      userId: patterns.userId,
      title: patterns.title,
      description: patterns.description,
      code: patterns.code,
      codeCpp: patterns.codeCpp,
      cppModeratedAt: patterns.cppModeratedAt,
      license: patterns.license,
      madeOn: patterns.madeOn,
      madeHow: patterns.madeHow,
      parentId: patterns.parentId,
      visibility: patterns.visibility,
      hiddenAt: patterns.hiddenAt,
      pinnedHeaderId: patterns.pinnedHeaderId,
      pinnedPerformanceId: patterns.pinnedPerformanceId,
      createdAt: patterns.createdAt,
      updatedAt: patterns.updatedAt,
      ...authorFields,
      likeCount,
      forkCount,
    })
    .from(patterns)
    .innerJoin(user, eq(patterns.userId, user.id))
    .where(eq(patterns.id, id))
    .limit(1);
  return rows[0] ?? null;
}

/**
 * Parent info for the "forked from" link — and for the two things a fork owes
 * its parent: the upstream credit baked into the source, and the licence its
 * own choice has to stay compatible with. Carries visibility so interaction
 * routes (like, comment, fork) can refuse private patterns without loading
 * the whole row.
 */
export async function getPatternStub(id: string) {
  const db = getDb();
  const rows = await db
    .select({
      id: patterns.id,
      title: patterns.title,
      userId: patterns.userId,
      license: patterns.license,
      visibility: patterns.visibility,
      ...authorFields,
    })
    .from(patterns)
    .innerJoin(user, eq(patterns.userId, user.id))
    .where(eq(patterns.id, id))
    .limit(1);
  return rows[0] ?? null;
}

export async function listComments(patternId: string) {
  const db = getDb();
  return db
    .select({
      id: comments.id,
      userId: comments.userId,
      body: comments.body,
      createdAt: comments.createdAt,
      editedAt: comments.editedAt,
      ...authorFields,
    })
    .from(comments)
    .innerJoin(user, eq(comments.userId, user.id))
    .where(eq(comments.patternId, patternId))
    .orderBy(comments.createdAt);
}

/** Which of these patterns the viewer has already liked (empty when signed
 *  out). Pass the page's pattern ids to keep it one small indexed IN query —
 *  omitted, it returns the viewer's whole liked set. */
export async function likedPatternIds(
  userId: string | null | undefined,
  patternIds?: string[],
): Promise<Set<string>> {
  if (!userId) return new Set();
  if (patternIds && patternIds.length === 0) return new Set();
  const rows = await getDb()
    .select({ patternId: likes.patternId })
    .from(likes)
    .where(
      patternIds
        ? and(eq(likes.userId, userId), inArray(likes.patternId, patternIds))
        : eq(likes.userId, userId),
    );
  return new Set(rows.map((row) => row.patternId));
}

export async function hasLiked(userId: string, patternId: string): Promise<boolean> {
  const rows = await getDb()
    .select({ patternId: likes.patternId })
    .from(likes)
    .where(and(eq(likes.userId, userId), eq(likes.patternId, patternId)))
    .limit(1);
  return rows.length > 0;
}

export async function countLikes(patternId: string): Promise<number> {
  const rows = await getDb()
    .select({ count: sql<number>`COUNT(*)` })
    .from(likes)
    .where(eq(likes.patternId, patternId));
  return rows[0]?.count ?? 0;
}

/**
 * A profile is the complete archive only to its owner. Everyone else sees the
 * public rows.
 */
export async function listPatternsByUser(
  userId: string,
  viewerId: string | null = null,
): Promise<FeedItem[]> {
  const db = getDb();
  const rows = await db
    .select(feedColumns)
    .from(patterns)
    .innerJoin(user, eq(patterns.userId, user.id))
    .where(
      viewerId === userId
        ? eq(patterns.userId, userId)
        : and(eq(patterns.userId, userId), feedVisible),
    )
    .orderBy(desc(patterns.createdAt));
  return toFeedItems(rows);
}

// ── Community ports ──────────────────────────────────────────────────────────

/** A pattern's ports, oldest first — the order resolution reads them in. */
export async function listPatternPorts(patternId: string) {
  return getDb()
    .select({
      id: patternHeaders.id,
      userId: patternHeaders.userId,
      codeCpp: patternHeaders.codeCpp,
      note: patternHeaders.note,
      stale: sql<number>`${patternHeaders.stale}`,
      moderatedAt: patternHeaders.moderatedAt,
      createdAt: patternHeaders.createdAt,
      ...authorFields,
    })
    .from(patternHeaders)
    .innerJoin(user, eq(patternHeaders.userId, user.id))
    .where(eq(patternHeaders.patternId, patternId))
    .orderBy(patternHeaders.createdAt)
    .then((rows) =>
      // SQLite booleans come back 0/1 — normalise once, here.
      rows.map((row) => ({ ...row, stale: Boolean(row.stale) })),
    );
}

/** A pattern's recordings, oldest first — the order resolvePerformance reads. */
export async function listPatternPerformances(patternId: string) {
  return getDb()
    .select({
      id: patternPerformances.id,
      userId: patternPerformances.userId,
      performanceJson: patternPerformances.performanceJson,
      note: patternPerformances.note,
      createdAt: patternPerformances.createdAt,
      ...authorFields,
    })
    .from(patternPerformances)
    .innerJoin(user, eq(patternPerformances.userId, user.id))
    .where(eq(patternPerformances.patternId, patternId))
    .orderBy(patternPerformances.createdAt);
}

/** Ownership check for deleting a recording — and the pin's validation. */
export async function getPerformanceStub(id: string) {
  const rows = await getDb()
    .select({
      id: patternPerformances.id,
      userId: patternPerformances.userId,
      patternId: patternPerformances.patternId,
    })
    .from(patternPerformances)
    .where(eq(patternPerformances.id, id))
    .limit(1);
  return rows[0] ?? null;
}

/** Ownership check for deleting a port — and the pin's validation, which
 *  needs to know the port belongs to the pattern and is still live. */
export async function getPortStub(id: string) {
  const rows = await getDb()
    .select({
      id: patternHeaders.id,
      userId: patternHeaders.userId,
      patternId: patternHeaders.patternId,
      stale: sql<number>`${patternHeaders.stale}`,
    })
    .from(patternHeaders)
    .where(eq(patternHeaders.id, id))
    .limit(1);
  const row = rows[0];
  return row ? { ...row, stale: Boolean(row.stale) } : null;
}