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