1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
import { integer, pgTable, varchar,timestamp,pgEnum ,pgView, text, primaryKey} from "drizzle-orm/pg-core";
import { eq , sql} from "drizzle-orm";
import { vendors } from "./vendors";
export const userDomainEnum = pgEnum("user_domain", ["evcp", "partners"]);
export const users = pgTable("users", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
name: varchar("name", { length: 255 }).notNull(),
email: varchar("email", { length: 255 }).notNull().unique(),
companyId: integer("company_id")
.references(() => vendors.id, { onDelete: "set null" }),
domain: userDomainEnum("domain").notNull().default("partners"),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
imageUrl: varchar("image_url", { length: 1024 }),
language: varchar("language", { length: 10 }).default("en"), // 언어 필드 추가 (기본값: 영어)
});
export const otps = pgTable('otps', {
email: varchar('email', { length: 256 }).notNull().primaryKey(),
code: varchar('code', { length: 6 }).notNull(),
createdAt: timestamp('created_at').defaultNow().notNull(),
otpToken: varchar({ length: 512 }).notNull(), // 이제 null 불가능
otpExpires: timestamp('otp_expires').notNull(), // null 불가능
});
export const permissions = pgTable("permissions", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
permissionKey: text("permission_key").notNull(),
description: text("description"),
createdAt: timestamp("created_at").default(sql`now()`),
});
export const roles = pgTable("roles", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
name: text("name").notNull(), // admin, manager
domain: userDomainEnum("domain").notNull(),
companyId: integer("company_id")
.references(() => vendors.id, { onDelete: "cascade" }),
description: text("description").default("").notNull(),
createdAt: timestamp("created_at").default(sql`now()`),
});
export const rolePermissions = pgTable("role_permissions", {
roleId: integer("role_id")
.references(() => roles.id, { onDelete: "cascade" })
.notNull(),
permissionId: integer("permission_id")
.references(() => permissions.id, { onDelete: "cascade" })
.notNull(),
}, (table) => {
return [{
pk: primaryKey({ columns: [table.roleId, table.permissionId] }),
pkWithCustomName: primaryKey({ name: 'rolePermissions_pk', columns: [table.roleId, table.permissionId] }),
}];
});
export const userRoles = pgTable("user_roles", {
userId: integer("user_id")
.references(() => users.id, { onDelete: "cascade" })
.notNull(),
roleId: integer("role_id")
.references(() => roles.id, { onDelete: "cascade" })
.notNull(),
}, (table) => {
return [{
pk: primaryKey({ columns: [table.userId, table.roleId] }),
pkWithCustomName: primaryKey({ name: 'userRoles_pk', columns: [table.userId, table.roleId] }),
}];
});
export type User = typeof users.$inferSelect
export type UserRole = typeof userRoles.$inferSelect
export type Role = typeof roles.$inferSelect
export type UserDomainType = (typeof userDomainEnum.enumValues)[number];
export const userView = pgView("user_view").as((qb) => {
return qb
.select({
// 1) userId: number
user_id: sql<number>`${users.id}`.as("user_id"),
// 2) userName: string
user_name: sql<string>`${users.name}`.as("user_name"),
// 3) userEmail: string
user_email: sql<string>`${users.email}`.as("user_email"),
user_domain: sql<string>`${users.domain}`.as("user_domain"),
user_image: sql<string>`${users.imageUrl}`.as("user_image"),
// 4) companyId: number | null
company_id: sql<number | null>`${vendors.id}`.as("company_id"),
// 5) companyName: string | null
company_name: sql<string | null>`${vendors.vendorName}`.as("company_name"),
// 6) roles: string[]
// Drizzle가 이 배열을 제대로 추론하려면 sql<string[]> 붙여야 함
roles: sql<string[]>`
array_agg(${roles.name})
`.as("roles"),
// 7) createdAt: Date
created_at: sql<Date>`${users.createdAt}`.as("created_at"),
})
.from(users)
.leftJoin(vendors, eq(users.companyId, vendors.id))
.leftJoin(userRoles, eq(users.id, userRoles.userId))
.leftJoin(roles, eq(userRoles.roleId, roles.id))
// array_agg를 쓰려면 GROUP BY 필요
.groupBy(
users.id,
vendors.id
// user_roles 테이블은 array_agg로 묶이므로 groupBy 필요 X
);
});
export const roleView = pgView("role_view").as((qb) => {
return qb
.select({
id: sql<number >`${roles.id}`.as("id"),
name: sql<string>`${roles.name}`.as("name"),
description: sql<string>`${roles.description}`.as("description"),
domain: sql<string>`${roles.domain}`.as("domain"),
created_at: sql<Date>`${roles.createdAt}`.as("created_at"),
// company
company_id: sql<number | null>`${vendors.id}`.as("company_id"),
company_name: sql<string | null>`${vendors.vendorName}`.as("company_name"),
// userCount
user_count: sql<number>`COUNT(${users.id})`.as("user_count"),
})
.from(roles)
.leftJoin(userRoles, eq(userRoles.roleId, roles.id))
.leftJoin(users, eq(users.id, userRoles.userId))
.leftJoin(vendors, eq(roles.companyId, vendors.id))
.groupBy(roles.id, vendors.id) // user_count(집계) 위해 groupBy
})
export type UserView = typeof userView.$inferSelect;
export type RoleView = typeof roleView.$inferSelect;
export type RolePermission = typeof rolePermissions.$inferSelect;
|
