1 files changed, 94 insertions, 0 deletions
diff --git a/lib/roles/repository.ts b/lib/roles/repository.ts
new file mode 100644
index 00000000..99ffdf29
--- /dev/null
+++ b/lib/roles/repository.ts
@@ -0,0 +1,94 @@
+// repository.ts
+import { sql, and, eq, inArray ,desc,asc} from "drizzle-orm";
+import type { PgTransaction } from "drizzle-orm/pg-core";
+import { roles, users, userRoles, Role, roleView, RoleView } from "@/db/schema/users"; // 수정
+import db from "@/db/db"; 
+import { companies } from "@/db/schema/companies";
+
+export type NewRole = typeof roles.$inferInsert;       // User insert 시 필요한 타입
+
+
+// (A) SELECT roles + userCount
+export async function selectRolesWithUserCount(
+  tx: PgTransaction<any, any, any>,
+  params: {
+    where?: any
+    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]
+    offset?: number,
+    limit?: number,
+  }
+) {
+
+  const { where, orderBy, offset = 0, limit = 10 } = params
+
+  const query = tx
+    .select()
+    .from(roleView)
+    .where(where)
+    .orderBy(...(orderBy ?? []))
+    .offset(offset)
+    .limit(limit)
+
+  const rows = await query
+  return rows
+}
+// (B) countRoles
+export async function countRoles(
+tx: PgTransaction<any, any, any>,
+  where?: ReturnType<typeof and>
+) {
+  // COUNT(*) from roles
+  const [{ count }] = await tx
+    .select({ count: sql<number>`COUNT(*)`.as("count") })
+    .from(roles)
+    .where(where ?? undefined);
+
+  return count; // number
+}
+
+export async function insertRole(
+  tx: PgTransaction<any, any, any>,
+  data: NewRole
+) {
+  return tx.insert(roles).values(data).returning();
+}
+
+export const getRoleById = async (id: number): Promise<Role | null> => {
+  const roleFouned = await db.select().from(roles).where(eq(roles.id, id)).execute();
+  if (roleFouned.length === 0) return null;
+
+  const role = roleFouned[0];
+  return role
+};
+
+
+export async function updateRole(
+  tx: PgTransaction<any, any, any>,
+  roleId: number,
+  data: Partial<Role>
+) {
+  return tx
+    .update(roles)
+    .set(data)
+    .where(eq(roles.id, roleId))
+    .returning();
+}
+
+
+export async function deleteRolesByIds(
+  tx: PgTransaction<any, any, any>,
+  ids: number[]
+) {
+  return tx.delete(roles).where(inArray(roles.id, ids));
+}
+
+export async function deleteUserRolesByIds(
+  tx: PgTransaction<any, any, any>,
+  ids: number[]
+) {
+  return tx.delete(userRoles).where(inArray(userRoles.roleId, ids));
+}
+
+export async function findAllRoleView(domain?: "evcp" | "partners"): Promise<RoleView[]> {
+  return db.select().from(roleView).where(eq(roleView.domain,domain)).orderBy(asc(roleView.name));
+}
+\ No newline at end of file