a fun bot for the hc slack
dunkirk.sh
1import { pgTable, text, integer, boolean } from "drizzle-orm/pg-core";
2import type { Pool } from "pg";
3import TakesConfig from "./config";
4
5// Define the takes table
6export const takes = pgTable("takes", {
7 id: text("id").primaryKey(),
8 userId: text("user_id").notNull(),
9 ts: text("ts").notNull(),
10 /* elapsed time in seconds */
11 elapsedTime: integer("elapsed_time").notNull().default(0),
12 createdAt: text("created_at")
13 .$defaultFn(() => new Date().toISOString())
14 .notNull(),
15 media: text("media").notNull().default("[]"), // array of media urls
16 multiplier: text("multiplier").notNull().default("1.0"),
17 notes: text("notes").notNull().default(""),
18});
19
20export const users = pgTable("users", {
21 id: text("id")
22 .primaryKey()
23 .$defaultFn(() => Bun.randomUUIDv7()),
24 /* total time in seconds */
25 totalTakesTime: integer("total_takes_time").default(0).notNull(),
26 hackatimeKeys: text("hackatime_keys").notNull().default("[]"),
27 projectName: text("project_name").notNull().default(""),
28 projectDescription: text("project_description").notNull().default(""),
29 projectBannerUrl: text("project_banner_url").notNull().default(""),
30 hackatimeVersion: text("hackatime_version").notNull().default("v1"),
31 lastTakeUploadDate: text("last_take_upload_date")
32 .notNull()
33 .default(TakesConfig.START_DATE.toISOString()),
34 repoLink: text("repo_link"),
35 demoLink: text("demo_link"),
36});
37
38export async function setupTriggers(pool: Pool) {
39 await pool.query(`
40 CREATE INDEX IF NOT EXISTS idx_takes_user_id ON takes(user_id);
41
42 CREATE OR REPLACE FUNCTION update_user_total_time()
43 RETURNS TRIGGER AS $$
44 BEGIN
45 IF TG_OP = 'INSERT' THEN
46 UPDATE users
47 SET total_takes_time = COALESCE(total_takes_time, 0) + NEW.elapsed_time,
48 last_take_upload_date = NEW.created_at
49 WHERE id = NEW.user_id;
50 RETURN NEW;
51 ELSIF TG_OP = 'DELETE' THEN
52 UPDATE users
53 SET total_takes_time = COALESCE(total_takes_time, 0) - OLD.elapsed_time
54 WHERE id = OLD.user_id;
55 RETURN OLD;
56 ELSIF TG_OP = 'UPDATE' THEN
57 UPDATE users
58 SET total_takes_time = COALESCE(total_takes_time, 0) - OLD.elapsed_time + NEW.elapsed_time,
59 last_take_upload_date = CASE WHEN NEW.created_at > OLD.created_at THEN NEW.created_at ELSE users.last_take_upload_date END
60 WHERE id = NEW.user_id;
61 RETURN NEW;
62 END IF;
63
64 RETURN NULL; -- Default return for unexpected operations
65
66 EXCEPTION WHEN OTHERS THEN
67 RAISE NOTICE 'Error updating user total time: %', SQLERRM;
68 RETURN NULL;
69 END;
70 $$ LANGUAGE plpgsql;
71
72 DROP TRIGGER IF EXISTS update_user_total_time_trigger ON takes;
73
74 CREATE TRIGGER update_user_total_time_trigger
75 AFTER INSERT OR UPDATE OR DELETE ON takes
76 FOR EACH ROW
77 EXECUTE FUNCTION update_user_total_time();
78 `);
79}