1import { sqliteTable as table } from "drizzle-orm/sqlite-core";
2import * as t from "drizzle-orm/sqlite-core";
3import type { User, ChatRoom, Message, UserStatus, MessageType, Session } from "../types/chat";
4import { v4 as uuidv4 } from "uuid";
5import { eq } from "drizzle-orm";
6
7
8export const users = table(
9 "users",
10 {
11 id: t.text("id").primaryKey().$default(() => uuidv4()),
12 password: t.text("password").notNull(),
13 nickname: t.text("nickname").notNull(),
14 status: t.text("status").notNull().$type<UserStatus>().default("offline"),
15 avatarUrl: t.text("avatar_url"),
16 createdAt: t.integer("created_at").notNull(),
17 lastSeen: t.integer("last_seen"),
18 }
19);
20
21export const sessions = table(
22 "sessions",
23 {
24 id: t.text("id").primaryKey(),
25 userId: t.text("user_id").references(() => users.id).notNull(),
26 expiresAt: t.integer("expires_at").notNull(),
27 createdAt: t.integer("created_at").notNull(),
28 }
29);
30
31export const chatRooms = table(
32 "chat_rooms",
33 {
34 id: t.text("id").primaryKey().$default(() => uuidv4()),
35 name: t.text("name"),
36 type: t.text("type").$type<"direct" | "group">().notNull().default("direct"),
37 createdAt: t.integer("created_at").notNull(),
38 }
39);
40
41export const messages = table(
42 "messages",
43 {
44 id: t.text("id").primaryKey().$default(() => uuidv4()),
45 chatRoomId: t.text("chat_room_id").references(() => chatRooms.id).notNull(),
46 senderId: t.text("sender_id").references(() => users.id).notNull(),
47 content: t.text("content").notNull(),
48 type: t.text("type").$type<MessageType>().notNull().default("chat"),
49 timestamp: t.integer("timestamp").notNull(),
50 }
51);
52
53// Validate schema types against interfaces
54export type Users = typeof users.$inferSelect;
55export type ChatRooms = typeof chatRooms.$inferSelect;
56export type Messages = typeof messages.$inferSelect;
57export type Sessions = typeof sessions.$inferSelect;
58// These type assertions will fail if the schema doesn't match the interfaces
59export type _UsersValidation = Omit<User, keyof Users> & Omit<Users, keyof User>;
60export type _ChatRoomsValidation = Omit<ChatRoom, keyof ChatRooms> & Omit<ChatRooms, keyof ChatRoom>;
61export type _MessagesValidation = Omit<Message, keyof Messages> & Omit<Messages, keyof Message>;
62export type _SessionsValidation = Omit<Session, keyof Sessions> & Omit<Sessions, keyof Session>;
63
64export const userView = t.sqliteView("user_view").as((qb) =>
65 qb.select({
66 id: users.id,
67 nickname: users.nickname,
68 status: users.status,
69 avatarUrl: users.avatarUrl,
70 lastSeen: users.lastSeen
71 }).from(users)
72);
73
74export const chatRoomView = t.sqliteView("chat_room_view").as((qb) =>
75 qb.select({
76 id: chatRooms.id,
77 name: chatRooms.name,
78 type: chatRooms.type,
79 createdAt: chatRooms.createdAt
80 }).from(chatRooms)
81);
82
83export const messageView = t.sqliteView("message_view").as((qb) =>
84 qb.select({
85 id: messages.id,
86 content: messages.content,
87 type: messages.type,
88 timestamp: messages.timestamp,
89 sender: users.nickname,
90 chatRoom: chatRooms.name
91 })
92 .from(messages)
93 .leftJoin(users, eq(messages.senderId, users.id))
94 .leftJoin(chatRooms, eq(messages.chatRoomId, chatRooms.id))
95);
96
97// Create a schema object with all our tables and views
98const schema = {
99 users,
100 sessions,
101 chatRooms,
102 messages,
103 userView,
104 chatRoomView,
105 messageView
106};
107
108
109export default schema;
110