feat: group streaks

- maintain completion streaks as a group
- join via a provided code, streaks increment when you guess correctly

5 files changed, 496 insertions, 21 deletions

diff --git a/server/daily.ts b/server/daily.ts
index f3eb9d8..6a9a83c 100644
--- a/server/daily.ts
+++ b/server/daily.ts
@@ -13,6 +13,7 @@ import {
   getDailySong,
   getDailyMusicVideo,
 } from "./shared";
+import { recordDailySolve, recordMvSolve } from "./db/groups";
 
 export const dailyRouter = Router();
 
@@ -69,6 +70,8 @@ dailyRouter.post("/guess", guessLimiter, (req, res) => {
     state?: unknown;
     sig?: unknown;
     guess?: unknown;
+    username?: unknown;
+    groupId?: unknown;
   };
 
   if (!verifySessionToken(body.sessionToken, today)) {
@@ -135,6 +138,28 @@ dailyRouter.post("/guess", guessLimiter, (req, res) => {
     guesses: [...state.guesses, nextGuess],
   };
 
+  const hasFinishedRound = nextState.didGuess || nextState.currentTry >= 6;
+
+  if (
+    hasFinishedRound &&
+    typeof body.groupId === "string" &&
+    body.groupId.trim().length > 0 &&
+    typeof body.username === "string" &&
+    body.username.trim().length > 0
+  ) {
+    try {
+      recordDailySolve(
+        body.groupId,
+        body.username,
+        today,
+        nextState.currentTry,
+        nextState.didGuess
+      );
+    } catch (error) {
+      console.error("Failed to record group daily solve", error);
+    }
+  }
+
   res.json({
     state: nextState,
     sig: signState(nextState),
@@ -149,6 +174,8 @@ dailyRouter.post("/guessMV", guessLimiter, (req, res) => {
     state?: unknown;
     sig?: unknown;
     guess?: unknown;
+    username?: unknown;
+    groupId?: unknown;
   };
 
   if (!verifySessionToken(body.sessionToken, today)) {
@@ -215,6 +242,28 @@ dailyRouter.post("/guessMV", guessLimiter, (req, res) => {
     guesses: [...state.guesses, nextGuess],
   };
 
+  const hasFinishedRound = nextState.didGuess || nextState.currentTry >= 6;
+
+  if (
+    hasFinishedRound &&
+    typeof body.groupId === "string" &&
+    body.groupId.trim().length > 0 &&
+    typeof body.username === "string" &&
+    body.username.trim().length > 0
+  ) {
+    try {
+      recordMvSolve(
+        body.groupId,
+        body.username,
+        today,
+        nextState.currentTry,
+        nextState.didGuess
+      );
+    } catch (error) {
+      console.error("Failed to record group MV solve", error);
+    }
+  }
+
   res.json({
     state: nextState,
     sig: signState(nextState),
diff --git a/server/db/groups.ts b/server/db/groups.ts
index 1060501..12e5362 100644
--- a/server/db/groups.ts
+++ b/server/db/groups.ts
@@ -1,27 +1,350 @@
-import { db } from "./index";
 import crypto from "node:crypto";
+import { getUtcDate } from "../shared";
+import { db } from "./index";
 
-export function createGroup(name: string) {
-    const id = crypto.randomUUID();
-    const joinCode = crypto
-        .randomBytes(3)
-        .toString("hex")
-        .toUpperCase();
+type GroupMode = "daily" | "mv";
+
+function previousUtcDate(date: string): string {
+  const value = new Date(`${date}T00:00:00Z`);
+  value.setUTCDate(value.getUTCDate() - 1);
+  return value.toISOString().slice(0, 10);
+}
 
-    db.prepare(`
-        INSERT INTO groups
-        (id, name, join_code, created_at)
-        VALUES (?, ?, ?, ?)
-    `).run(
-        id,
-        name,
-        joinCode,
-        Date.now()
-    );
+function getSolveTableName(mode: GroupMode): "daily_solves" | "mv_solves" {
+  return mode === "mv" ? "mv_solves" : "daily_solves";
+}
 
+function getStreakColumns(mode: GroupMode): {
+  currentStreak: "current_streak" | "mv_current_streak";
+  lastCompleted: "last_completed" | "mv_last_completed";
+} {
+  if (mode === "mv") {
     return {
-        id,
-        name,
-        joinCode
+      currentStreak: "mv_current_streak",
+      lastCompleted: "mv_last_completed",
     };
+  }
+
+  return {
+    currentStreak: "current_streak",
+    lastCompleted: "last_completed",
+  };
+}
+
+function isGroupSolvedOnDate(groupId: string, date: string, mode: GroupMode): boolean {
+  const tableName = getSolveTableName(mode);
+  const solvedCount = db
+    .prepare(
+      `
+      SELECT COUNT(DISTINCT username) as count
+      FROM ${tableName}
+      WHERE group_id = ? AND date = ?
+        AND solved = 1
+    `
+    )
+    .get(groupId, date) as { count: number };
+
+  return !!solvedCount && solvedCount.count >= 1;
+}
+
+function getStoredGroupStreak(
+  groupId: string,
+  mode: GroupMode
+): {
+  currentStreak: number;
+  lastCompleted: string | null;
+} {
+  const columns = getStreakColumns(mode);
+  const row = db
+    .prepare(
+      `
+      SELECT ${columns.currentStreak} as current_streak, ${columns.lastCompleted} as last_completed
+      FROM groups
+      WHERE id = ?
+    `
+    )
+    .get(groupId) as
+    | { current_streak: number; last_completed: string | null }
+    | undefined;
+
+  if (!row) {
+    return { currentStreak: 0, lastCompleted: null };
+  }
+
+  return {
+    currentStreak: row.current_streak,
+    lastCompleted: row.last_completed,
+  };
+}
+
+function getActiveGroupStreak(
+  groupId: string,
+  mode: GroupMode,
+  today = getUtcDate()
+): {
+  currentStreak: number;
+  lastCompleted: string | null;
+} {
+  const stored = getStoredGroupStreak(groupId, mode);
+  const columns = getStreakColumns(mode);
+
+  if (!stored.lastCompleted) {
+    if (stored.currentStreak !== 0) {
+      db.prepare(
+        `
+        UPDATE groups
+        SET ${columns.currentStreak} = 0
+        WHERE id = ?
+      `
+      ).run(groupId);
+    }
+
+    return { currentStreak: 0, lastCompleted: null };
+  }
+
+  const yesterday = previousUtcDate(today);
+  if (stored.lastCompleted === today || stored.lastCompleted === yesterday) {
+    return stored;
+  }
+
+  if (stored.currentStreak !== 0) {
+    db.prepare(
+      `
+      UPDATE groups
+      SET ${columns.currentStreak} = 0
+      WHERE id = ?
+    `
+    ).run(groupId);
+  }
+
+  return {
+    currentStreak: 0,
+    lastCompleted: stored.lastCompleted,
+  };
+}
+
+function updateGroupStreakForToday(
+  groupId: string,
+  mode: GroupMode,
+  today = getUtcDate()
+): {
+  currentStreak: number;
+  lastCompleted: string | null;
+} {
+  const stored = getActiveGroupStreak(groupId, mode, today);
+  const columns = getStreakColumns(mode);
+
+  if (!isGroupSolvedOnDate(groupId, today, mode)) {
+    return stored;
+  }
+
+  if (stored.lastCompleted === today) {
+    return stored;
+  }
+
+  const yesterday = previousUtcDate(today);
+  const nextStreak = stored.lastCompleted === yesterday ? stored.currentStreak + 1 : 1;
+
+  db.prepare(
+    `
+    UPDATE groups
+    SET ${columns.currentStreak} = ?, ${columns.lastCompleted} = ?
+    WHERE id = ?
+  `
+  ).run(nextStreak, today, groupId);
+
+  return {
+    currentStreak: nextStreak,
+    lastCompleted: today,
+  };
+}
+
+export function createGroup(name: string) {
+  const normalizedName = name.trim().slice(0, 64);
+  if (!normalizedName) {
+    throw new Error("Group name is required");
+  }
+
+  const existingGroup = db
+    .prepare(
+      `
+      SELECT id FROM groups WHERE name = ?
+    `
+    )
+    .get(normalizedName) as { id: string } | undefined;
+
+  if (existingGroup) {
+    throw new Error("Group already exists");
+  }
+
+  const id = crypto.randomUUID();
+  const joinCode = crypto.randomBytes(3).toString("hex").toUpperCase();
+
+  db.prepare(
+    `
+    INSERT INTO groups (id, name, join_code, created_at)
+    VALUES (?, ?, ?, ?)
+  `
+  ).run(id, normalizedName, joinCode, Date.now());
+
+  return {
+    id,
+    name: normalizedName,
+    joinCode,
+  };
+}
+
+export function getGroupByJoinCode(
+  joinCode: string
+): { id: string; name: string; joinCode: string } | null {
+  const normalizedCode = joinCode.trim().toUpperCase();
+
+  const group = db
+    .prepare(
+      `
+      SELECT id, name, join_code
+      FROM groups
+      WHERE join_code = ?
+    `
+    )
+    .get(normalizedCode) as
+    | { id: string; name: string; join_code: string }
+    | undefined;
+
+  if (!group) return null;
+
+  return {
+    id: group.id,
+    name: group.name,
+    joinCode: group.join_code,
+  };
+}
+
+export function recordGroupJoin(groupId: string, username: string) {
+  const normalizedUsername = username.trim().slice(0, 32);
+  if (!normalizedUsername) {
+    throw new Error("Username is required");
+  }
+
+  // Check if the user is already a member of the group
+  const existingJoin = db
+    .prepare(
+      `
+      SELECT group_id
+      FROM group_members
+      WHERE group_id = ? AND username = ?
+    `
+    )
+    .get(groupId, normalizedUsername) as { group_id: string } | undefined;
+  if (existingJoin) return;
+
+  // add the user to the group
+  db.prepare(
+    `
+    INSERT INTO group_members (group_id, username, joined_at)
+    VALUES (?, ?, ?)
+  `
+  ).run(groupId, normalizedUsername, Date.now());
+}
+
+function recordSolve(
+  tableName: "daily_solves" | "mv_solves",
+  groupId: string,
+  username: string,
+  date: string,
+  attempt: number,
+  guessed: boolean
+) {
+  const today = getUtcDate();
+  if (date !== today) {
+    throw new Error(`Only today's solves can be recorded. Expected ${today}, got ${date}.`);
+  }
+
+  const normalizedUsername = username.trim().slice(0, 32);
+  const membership = db
+    .prepare(
+      `
+      SELECT group_id
+      FROM group_members
+      WHERE group_id = ? AND username = ?
+    `
+    )
+    .get(groupId, normalizedUsername) as { group_id: string } | undefined;
+
+  if (!membership) return;
+
+  const boundedAttempt = Math.max(1, Math.min(6, Math.floor(attempt)));
+  db.prepare(
+    `
+    INSERT INTO ${tableName} (group_id, username, date, solved, attempts)
+    VALUES (?, ?, ?, ?, ?)
+    ON CONFLICT(group_id, username, date)
+    DO UPDATE SET solved = excluded.solved, attempts = excluded.attempts
+  `
+  ).run(groupId, normalizedUsername, date, guessed ? 1 : 0, boundedAttempt);
+
+  const mode: GroupMode = tableName === "mv_solves" ? "mv" : "daily";
+  updateGroupStreakForToday(groupId, mode, today);
+}
+
+export function recordDailySolve(
+  groupId: string,
+  username: string,
+  date: string,
+  attempt: number,
+  guessed: boolean
+) {
+  recordSolve("daily_solves", groupId, username, date, attempt, guessed);
+}
+
+export function recordMvSolve(
+  groupId: string,
+  username: string,
+  date: string,
+  attempt: number,
+  guessed: boolean
+) {
+  recordSolve("mv_solves", groupId, username, date, attempt, guessed);
+}
+
+export function getGroupDailyStatus(
+  groupId: string,
+  date: string,
+  mode: GroupMode = "daily"
+): {
+  groupId: string;
+  groupName: string;
+  currentStreak: number;
+  finishedUsers: string[];
+} | null {
+  const group = db
+    .prepare(
+      `
+      SELECT id, name
+      FROM groups
+      WHERE id = ?
+    `
+    )
+    .get(groupId) as { id: string; name: string } | undefined;
+  if (!group) return null;
+
+  const streak = getActiveGroupStreak(groupId, mode, date);
+  const solveTable = getSolveTableName(mode);
+  const finishedRows = db
+    .prepare(
+      `
+      SELECT username
+      FROM ${solveTable}
+      WHERE group_id = ? AND date = ?
+        AND (solved = 1 OR attempts >= 6)
+      ORDER BY username ASC
+    `
+    ).all(groupId, date) as Array<{ username: string }>;
+
+  return {
+    groupId: group.id,
+    groupName: group.name,
+    currentStreak: streak.currentStreak,
+    finishedUsers: finishedRows.map((row) => row.username),
+  };
 }
diff --git a/server/db/schema.ts b/server/db/schema.ts
index 8fc2532..eca4dcc 100644
--- a/server/db/schema.ts
+++ b/server/db/schema.ts
@@ -7,6 +7,8 @@ CREATE TABLE IF NOT EXISTS groups (
     join_code TEXT UNIQUE NOT NULL,
     current_streak INTEGER NOT NULL DEFAULT 0,
     last_completed TEXT,
+    mv_current_streak INTEGER NOT NULL DEFAULT 0,
+    mv_last_completed TEXT,
     created_at INTEGER NOT NULL
 );
 
diff --git a/server/group.ts b/server/group.ts
new file mode 100644
index 0000000..be71cc3
--- /dev/null
+++ b/server/group.ts
@@ -0,0 +1,99 @@
+import { Router } from "express";
+import {
+  createGroup,
+  getGroupDailyStatus,
+  getGroupByJoinCode,
+  recordGroupJoin,
+} from "./db/groups";
+import { getUtcDate } from "./shared";
+
+export const groupRouter = Router();
+
+groupRouter.post("/create-group", (req, res) => {
+  const body = req.body as {
+    name?: unknown;
+    username?: unknown;
+  };
+
+  if (typeof body.name !== "string" || body.name.trim().length === 0) {
+    res.status(400).json({ error: "Group name is required." });
+    return;
+  }
+
+  try {
+    const group = createGroup(body.name);
+
+    if (typeof body.username === "string" && body.username.trim()) {
+      recordGroupJoin(group.id, body.username);
+    }
+
+    res.json({
+      group: {
+        id: group.id,
+        name: group.name,
+        joinToken: group.joinCode,
+      },
+    });
+  } catch (error) {
+    res
+      .status(500)
+      .json({ error: "Group already exists, choose a different name" });
+  }
+});
+
+groupRouter.post("/join-group", (req, res) => {
+  const body = req.body as {
+    joinToken?: unknown;
+    username?: unknown;
+  };
+
+  // validate request
+  if (typeof body.joinToken !== "string" || body.joinToken.trim().length === 0) {
+    res.status(400).json({ error: "joinToken is required." });
+    return;
+  }
+  if (typeof body.username !== "string" || body.username.trim().length === 0) {
+    res.status(400).json({ error: "username is required." });
+    return;
+  }
+  const group = getGroupByJoinCode(body.joinToken);
+  if (!group) {
+    res.status(404).json({ error: "Group not found for that token." });
+    return;
+  }
+  try {
+    recordGroupJoin(group.id, body.username);
+  } catch {
+    res.status(500).json({ error: "Unable to join group." });
+    return;
+  }
+
+  res.json({
+    groupId: group.id,
+    groupName: group.name,
+    joinToken: group.joinCode,
+    username: body.username.trim(),
+  });
+});
+
+groupRouter.get("/group-status", (req, res) => {
+  const groupId = req.query.groupId;
+  const date = req.query.date;
+  const mode = req.query.mode;
+
+  if (typeof groupId !== "string" || groupId.trim().length === 0) {
+    res.status(400).json({ error: "groupId is required." });
+    return;
+  }
+
+  const targetDate = typeof date === "string" && date.trim() ? date : getUtcDate();
+  const normalizedMode = mode === "mv" ? "mv" : "daily";
+  const status = getGroupDailyStatus(groupId, targetDate, normalizedMode);
+
+  if (!status) {
+    res.status(404).json({ error: "Group not found." });
+    return;
+  }
+
+  res.json(status);
+});
diff --git a/server/index.ts b/server/index.ts
index b076de0..5f11652 100644
--- a/server/index.ts
+++ b/server/index.ts
@@ -3,7 +3,8 @@ import cors from "cors";
 import dotenv from "dotenv";
 import { dailyRouter } from "./daily";
 import { selectRouter } from "./select";
-import "./db/schema"
+import { groupRouter } from "./group";
+import "./db/schema";
 
 dotenv.config();
 
@@ -15,6 +16,7 @@ const SERVER_PORT = process.env.SERVER_PORT || 3001;
 
 app.use(dailyRouter);
 app.use(selectRouter);
+app.use(groupRouter);
 
 const enableDailyMusicVideo = process.env.ENABLE_DAILY_MUSIC_VIDEO === "true";