initial commit

1 files changed, 147 insertions, 0 deletions

diff --git a/db/schema/users.ts b/db/schema/users.ts
new file mode 100644
index 00000000..843ee2f3
--- /dev/null
+++ b/db/schema/users.ts
@@ -0,0 +1,147 @@
+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 }),
+
+});
+
+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;
\ No newline at end of file