1 files changed, 166 insertions, 0 deletions
diff --git a/lib/tasks/repository.ts b/lib/tasks/repository.ts
new file mode 100644
index 00000000..2e71ee20
--- /dev/null
+++ b/lib/tasks/repository.ts
@@ -0,0 +1,166 @@
+// src/lib/tasks/repository.ts
+import db  from "@/db/db";
+import { tasks, type Task } from "@/db/schema/tasks";
+import {
+  eq,
+  inArray,
+  not,
+  asc,
+  desc,
+  and,
+  ilike,
+  gte,
+  lte,
+  count,
+  gt,
+} from "drizzle-orm";
+import { PgTransaction } from "drizzle-orm/pg-core";
+export type NewTask = typeof tasks.$inferInsert
+
+/**
+ * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
+ *  - 트랜잭션(tx)을 받아서 사용하도록 구현
+ */
+export async function selectTasks(
+    tx: PgTransaction<any, any, any>,
+    params: {
+      where?: any; // drizzle-orm의 조건식 (and, eq...) 등
+      orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
+      offset?: number;
+      limit?: number;
+    }
+  ) {
+    const { where, orderBy, offset = 0, limit = 10 } = params;
+  
+    return tx
+      .select()
+      .from(tasks)
+      .where(where)
+      .orderBy(...(orderBy ?? []))
+      .offset(offset)
+      .limit(limit);
+  }
+/** 총 개수 count */
+export async function countTasks(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx.select({ count: count() }).from(tasks).where(where);
+  return res[0]?.count ?? 0;
+}
+
+/** 단건 Insert 예시 */
+export async function insertTask(
+  tx: PgTransaction<any, any, any>,
+  data: NewTask // DB와 동일한 insert 가능한 타입
+) {
+  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
+  return tx
+    .insert(tasks)
+    .values(data)
+    .returning({ id: tasks.id, createdAt: tasks.createdAt });
+}
+
+/** 복수 Insert 예시 */
+export async function insertTasks(
+  tx: PgTransaction<any, any, any>,
+  data: Task[]
+) {
+  return tx.insert(tasks).values(data).onConflictDoNothing();
+}
+
+/** (방금 생성된 Task를 제외한) 가장 오래된 Task 하나 조회 */
+export async function selectOldestTaskExcept(
+    tx: PgTransaction<any, any, any>,
+  excludeId: string
+) {
+  return tx
+    .select({ id: tasks.id, createdAt: tasks.createdAt })
+    .from(tasks)
+    .where(not(eq(tasks.id, excludeId)))
+    .orderBy(asc(tasks.createdAt))
+    .limit(1);
+}
+
+/** 단건 삭제 */
+export async function deleteTaskById(
+    tx: PgTransaction<any, any, any>,
+  taskId: string
+) {
+  return tx.delete(tasks).where(eq(tasks.id, taskId));
+}
+
+/** 복수 삭제 */
+export async function deleteTasksByIds(
+    tx: PgTransaction<any, any, any>,
+  ids: string[]
+) {
+  return tx.delete(tasks).where(inArray(tasks.id, ids));
+}
+
+/** 전체 삭제 */
+export async function deleteAllTasks(
+    tx: PgTransaction<any, any, any>,
+) {
+  return tx.delete(tasks);
+}
+
+/** 단건 업데이트 */
+export async function updateTask(
+    tx: PgTransaction<any, any, any>,
+  taskId: string,
+  data: Partial<Task>
+) {
+  return tx
+    .update(tasks)
+    .set(data)
+    .where(eq(tasks.id, taskId))
+    .returning({ status: tasks.status, priority: tasks.priority });
+}
+
+/** 복수 업데이트 */
+export async function updateTasks(
+    tx: PgTransaction<any, any, any>,
+  ids: string[],
+  data: Partial<Task>
+) {
+  return tx
+    .update(tasks)
+    .set(data)
+    .where(inArray(tasks.id, ids))
+    .returning({ status: tasks.status, priority: tasks.priority });
+}
+
+/** status 기준 groupBy */
+export async function groupByStatus(
+    tx: PgTransaction<any, any, any>,
+) {
+  return tx
+    .select({
+      status: tasks.status,
+      count: count(),
+    })
+    .from(tasks)
+    .groupBy(tasks.status)
+    .having(gt(count(), 0));
+}
+
+/** priority 기준 groupBy */
+export async function groupByPriority(
+    tx: PgTransaction<any, any, any>,
+) {
+  return tx
+    .select({
+      priority: tasks.priority,
+      count: count(),
+    })
+    .from(tasks)
+    .groupBy(tasks.priority)
+    .having(gt(count(), 0));
+}
+
+// 모든 task 조회
+export const getAllTasks = async (): Promise<Task[]> => {
+  const users = await db.select().from(tasks).execute();
+  return users
+};