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