1 files changed, 128 insertions, 0 deletions

diff --git a/lib/users/repository.ts b/lib/users/repository.ts
new file mode 100644
index 00000000..78d1668b
--- /dev/null
+++ b/lib/users/repository.ts
@@ -0,0 +1,128 @@
+// lib/users/repository.ts
+import db from '@/db/db';
+import { users, otps, type User, Role, roles, userRoles } from '@/db/schema/users';
+import { Otp } from '@/types/user';
+import { eq,and ,asc} from 'drizzle-orm';
+
+// 모든 사용자 조회
+export const getAllUsers = async (): Promise<User[]> => {
+  const usersRes = await db.select().from(users).execute();
+  return usersRes
+};
+
+export async function getRoleAssignedUsers(roleId: number) {
+  const rows = await db
+    .select()
+    .from(userRoles)
+    .where(eq(userRoles.roleId, roleId))
+  return rows.map((r) => r.userId)  // [1, 2, 5, ...]
+}
+
+// ID로 사용자 조회
+export const getUserById = async (id: number): Promise<User | null> => {
+  const usersRes = await db.select().from(users).where(eq(users.id, id)).execute();
+  if (usersRes.length === 0) return null;
+
+  const user = usersRes[0];
+  return user
+};
+
+// Email로 사용자 조회
+export const getUserByEmail = async (email: string): Promise<User | null> => {
+  const usersRes = await db.select().from(users).where(eq(users.email, email)).execute();
+  if (usersRes.length === 0) return null;
+
+  const user = usersRes[0];
+  return user
+};
+
+
+// 새 사용자 생성
+export const createUser = async (name: string, email: string): Promise<User> => {
+  const usersRes = await db.insert(users).values({ name, email }).returning();
+  const user = usersRes[0];
+  return user
+};
+
+// 사용자 업데이트
+export const updateUser = async (id: number, data: Partial<User>): Promise<User | null> => {
+  const usersRes = await db.update(users).set(data).where(eq(users.id, id)).returning();
+  if (usersRes.length === 0) return null;
+  const user = usersRes[0];
+  return user
+};
+
+// 사용자 삭제
+export const deleteUser = async (id: number): Promise<boolean> => {
+  const result = await db.delete(users).where(eq(users.id, id)).execute();
+  return (result.rowCount ?? 0) > 0; // null일 경우 0으로 처리
+};
+
+
+// 새 otp 생성
+export const createOtp = async ( email: string, code:string, createdAt:Date, otpToken:string, otpExpires:Date ): Promise<Otp> => {
+  const otp = await db.insert(otps).values({ email, code, createdAt, otpToken,otpExpires  }).returning();
+  return otp[0]
+};
+
+
+export const findOtpByEmail = async (email: string): Promise<Otp | null> => {
+  const [otpRecord] = await db
+    .select()
+    .from(otps)
+    .where(eq(otps.email, email))
+
+  return otpRecord ?? null
+}
+
+export const updateOtp = async (
+  email: string,
+  code: string,
+  createdAt: Date,
+  otpToken: string,
+  otpExpires: Date
+): Promise<Otp> => {
+  const rows = await db
+    .update(otps)
+    .set({
+      code,
+      createdAt,
+      otpToken,
+      otpExpires,
+    })
+    .where(eq(otps.email, email))
+    .returning();
+
+  return rows[0];
+};
+
+// Email 및 토큰으로 opt 조회
+export const getOtpByEmailAndToken = async (email: string, token:string): Promise<Otp | null> => {
+  const opts = await db.select().from(otps).where(eq(otps.email, email)).execute();
+  if (opts.length === 0) return null;
+
+  const otp = opts[0];
+  return otp
+};
+
+
+export const getOtpByEmailAndCode = async (
+  email: string,
+  code: string
+): Promise<Otp | null> => {
+
+  console.log(email, code, "db")
+
+  const [otp] = await db
+    .select()
+    .from(otps)
+    .where(
+      and(eq(otps.email, email), eq(otps.code, code))
+    );
+
+  return otp ?? null;
+};
+
+export async function findAllRoles(): Promise<Role[]> {
+  return db.select().from(roles).where(eq(roles.domain ,'evcp')).orderBy(asc(roles.name));
+}
\ No newline at end of file