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 projectCategory: text("project_category").notNull().default("other"),
29 projectDescription: text("project_description").notNull().default(""),
30 projectBannerUrl: text("project_banner_url").notNull().default(""),
31 hackatimeVersion: text("hackatime_version").notNull().default("v1"),
32 lastTakeUploadDate: text("last_take_upload_date")
33 .notNull()
34 .default(TakesConfig.START_DATE.toISOString()),
35 isUploading: boolean("is_uploading").default(false).notNull(),
36 repoLink: text("repo_link"),
37 demoLink: text("demo_link"),
38 createdAt: text("created_at")
39 .$defaultFn(() => new Date().toISOString())
40 .notNull(),
41});
42
43export async function setupTriggers(pool: Pool) {
44 await pool.query(`
45 CREATE INDEX IF NOT EXISTS idx_takes_user_id ON takes(user_id);
46
47 CREATE OR REPLACE FUNCTION update_user_total_time()
48 RETURNS TRIGGER AS $$
49 BEGIN
50 IF TG_OP = 'INSERT' THEN
51 UPDATE users
52 SET total_takes_time = COALESCE(total_takes_time, 0) + NEW.elapsed_time,
53 last_take_upload_date = NEW.created_at
54 WHERE id = NEW.user_id;
55 RETURN NEW;
56 ELSIF TG_OP = 'DELETE' THEN
57 UPDATE users
58 SET total_takes_time = COALESCE(total_takes_time, 0) - OLD.elapsed_time
59 WHERE id = OLD.user_id;
60 RETURN OLD;
61 ELSIF TG_OP = 'UPDATE' THEN
62 UPDATE users
63 SET total_takes_time = COALESCE(total_takes_time, 0) - OLD.elapsed_time + NEW.elapsed_time,
64 last_take_upload_date = CASE WHEN NEW.created_at > OLD.created_at THEN NEW.created_at ELSE users.last_take_upload_date END
65 WHERE id = NEW.user_id;
66 RETURN NEW;
67 END IF;
68
69 RETURN NULL; -- Default return for unexpected operations
70
71 EXCEPTION WHEN OTHERS THEN
72 RAISE NOTICE 'Error updating user total time: %', SQLERRM;
73 RETURN NULL;
74 END;
75 $$ LANGUAGE plpgsql;
76
77 DROP TRIGGER IF EXISTS update_user_total_time_trigger ON takes;
78
79 CREATE TRIGGER update_user_total_time_trigger
80 AFTER INSERT OR UPDATE OR DELETE ON takes
81 FOR EACH ROW
82 EXECUTE FUNCTION update_user_total_time();
83 `);
84}