a fun bot for the hc slack
dunkirk.sh
1import { db } from "./db";
2import { users as usersTable, takes as takesTable } from "./schema";
3import { sql, eq, and, ne } from "drizzle-orm";
4
5/**
6 * Finds and corrects any drift between the computed user total time and the stored value.
7 * This helps ensure time calculations remain accurate even if triggers fail or data gets out of sync.
8 */
9export async function validateAndFixUserTotals() {
10 try {
11 console.log("Validating user totals...");
12
13 // First, get the calculated totals per user
14 const calculatedTotals = await db
15 .select({
16 userId: takesTable.userId,
17 calculatedTotal:
18 sql<number>`COALESCE(SUM(${takesTable.elapsedTime}), 0)::integer`.as(
19 "calculated_total",
20 ),
21 })
22 .from(takesTable)
23 .groupBy(takesTable.userId);
24
25 // Convert to a map for easier lookup
26 const totalsMap = new Map(
27 calculatedTotals.map((item) => [item.userId, item.calculatedTotal]),
28 );
29
30 // Get all users
31 const allUsers = await db
32 .select({
33 id: usersTable.id,
34 storedTotal: usersTable.totalTakesTime,
35 })
36 .from(usersTable);
37
38 // Find users with drift
39 const driftedUsers = allUsers
40 .filter((user) => {
41 const calculatedTotal = totalsMap.get(user.id) || 0;
42 return user.storedTotal !== calculatedTotal;
43 })
44 .map((user) => ({
45 id: user.id,
46 storedTotal: user.storedTotal,
47 calculatedTotal: totalsMap.get(user.id) || 0,
48 }));
49
50 if (driftedUsers.length === 0) {
51 console.log("✅ All user totals are in sync");
52 return { fixed: 0, errors: [] };
53 }
54
55 console.log(
56 `❌ Found ${driftedUsers.length} users with incorrect totals`,
57 );
58
59 // Fix each drifted user
60 const errors: string[] = [];
61 let fixed = 0;
62
63 for (const user of driftedUsers) {
64 try {
65 await db
66 .update(usersTable)
67 .set({ totalTakesTime: user.calculatedTotal })
68 .where(eq(usersTable.id, user.id));
69
70 console.log(
71 `Fixed user ${user.id}: ${user.storedTotal} → ${user.calculatedTotal}`,
72 );
73 fixed++;
74 } catch (error) {
75 const errorMsg = `Failed to fix user ${user.id}: ${error}`;
76 console.error(errorMsg);
77 errors.push(errorMsg);
78 }
79 }
80
81 console.log(`✅ Fixed ${fixed} user totals`);
82
83 return { fixed, errors };
84 } catch (error) {
85 console.error("Error validating user totals:", error);
86 throw error;
87 }
88}