1 files changed, 232 insertions, 0 deletions
diff --git a/lib/rfqs-ship/repository.ts b/lib/rfqs-ship/repository.ts
new file mode 100644
index 00000000..24d09ec3
--- /dev/null
+++ b/lib/rfqs-ship/repository.ts
@@ -0,0 +1,232 @@
+// src/lib/tasks/repository.ts
+import db from "@/db/db";
+import { items } from "@/db/schema/items";
+import { rfqItems, rfqs, RfqWithItems, rfqsView, type Rfq,VendorResponse, vendorResponses, RfqViewWithItems } from "@/db/schema/rfq";
+import { users } from "@/db/schema/users";
+import {
+  eq,
+  inArray,
+  not,
+  asc,
+  desc,
+  and,
+  ilike,
+  gte,
+  lte,
+  count,
+  gt, sql
+} from "drizzle-orm";
+import { PgTransaction } from "drizzle-orm/pg-core";
+import { RfqType } from "./validations";
+export type NewRfq = typeof rfqs.$inferInsert
+export type NewRfqItem = typeof rfqItems.$inferInsert
+
+/**
+ * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
+ *  - 트랜잭션(tx)을 받아서 사용하도록 구현
+ */
+export async function selectRfqs(
+  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;
+
+  return tx
+    .select({
+      rfqId: rfqsView.id,
+      id: rfqsView.id,
+      rfqCode: rfqsView.rfqCode,
+      description: rfqsView.description,
+      projectCode: rfqsView.projectCode,
+      projectName: rfqsView.projectName,
+      dueDate: rfqsView.dueDate,
+      status: rfqsView.status,
+      // createdBy → user 이메일
+      createdBy: rfqsView.createdBy,          // still the numeric user ID
+      createdByEmail: rfqsView.userEmail,        // string
+
+      createdAt: rfqsView.createdAt,
+      updatedAt: rfqsView.updatedAt,
+      // ====================
+      // 1) itemCount via subselect
+      // ====================
+      itemCount:rfqsView.itemCount,
+      attachCount: rfqsView.attachmentCount,
+
+      // user info
+      // userId: users.id,
+      userEmail: rfqsView.userEmail,
+      userName: rfqsView.userName,
+    })
+    .from(rfqsView)
+    .where(where ?? undefined)
+    .orderBy(...(orderBy ?? []))
+    .offset(offset)
+    .limit(limit);
+}
+/** 총 개수 count */
+export async function countRfqs(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx.select({ count: count() }).from(rfqsView).where(where);
+  return res[0]?.count ?? 0;
+}
+
+/** 단건 Insert 예시 */
+export async function insertRfq(
+  tx: PgTransaction<any, any, any>,
+  data: NewRfq // DB와 동일한 insert 가능한 타입
+) {
+  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
+  return tx
+    .insert(rfqs)
+    .values(data)
+    .returning({ id: rfqs.id, createdAt: rfqs.createdAt });
+}
+
+/** 복수 Insert 예시 */
+export async function insertRfqs(
+  tx: PgTransaction<any, any, any>,
+  data: Rfq[]
+) {
+  return tx.insert(rfqs).values(data).onConflictDoNothing();
+}
+
+/** 단건 삭제 */
+export async function deleteRfqById(
+  tx: PgTransaction<any, any, any>,
+  rfqId: number
+) {
+  return tx.delete(rfqs).where(eq(rfqs.id, rfqId));
+}
+
+/** 복수 삭제 */
+export async function deleteRfqsByIds(
+  tx: PgTransaction<any, any, any>,
+  ids: number[]
+) {
+  return tx.delete(rfqs).where(inArray(rfqs.id, ids));
+}
+
+/** 전체 삭제 */
+export async function deleteAllRfqs(
+  tx: PgTransaction<any, any, any>,
+) {
+  return tx.delete(rfqs);
+}
+
+/** 단건 업데이트 */
+export async function updateRfq(
+  tx: PgTransaction<any, any, any>,
+  rfqId: number,
+  data: Partial<Rfq>
+) {
+  return tx
+    .update(rfqs)
+    .set(data)
+    .where(eq(rfqs.id, rfqId))
+    .returning({ status: rfqs.status });
+}
+
+// /** 복수 업데이트 */
+export async function updateRfqs(
+  tx: PgTransaction<any, any, any>,
+  ids: number[],
+  data: Partial<Rfq>
+) {
+  return tx
+    .update(rfqs)
+    .set(data)
+    .where(inArray(rfqs.id, ids))
+    .returning({ status: rfqs.status, dueDate: rfqs.dueDate });
+}
+
+
+// 모든 task 조회
+export const getAllRfqs = async (): Promise<Rfq[]> => {
+  const datas = await db.select().from(rfqs).execute();
+  return datas
+};
+
+
+export async function groupByStatus(
+  tx: PgTransaction<any, any, any>,
+  rfqType: RfqType = RfqType.PURCHASE
+) {
+  return tx
+    .select({
+      status: rfqs.status,
+      count: count(),
+    })
+    .from(rfqs)
+    .where(eq(rfqs.rfqType, rfqType)) // rfqType으로 필터링 추가
+    .groupBy(rfqs.status)
+    .having(gt(count(), 0));
+}
+
+export async function insertRfqItem(
+  tx: PgTransaction<any, any, any>,
+  data: NewRfqItem
+) {
+  return tx.insert(rfqItems).values(data).returning();
+}
+
+export const getRfqById = async (id: number): Promise<RfqViewWithItems | null> => {
+  // 1) RFQ 단건 조회
+  const rfqsRes = await db
+    .select()
+    .from(rfqsView)
+    .where(eq(rfqsView.id, id))
+    .limit(1);
+
+  if (rfqsRes.length === 0) return null;
+  const rfqRow = rfqsRes[0];
+
+  // 2) 해당 RFQ 아이템 목록 조회
+  const itemsRes = await db
+    .select()
+    .from(rfqItems)
+    .where(eq(rfqItems.rfqId, id));
+
+  // itemsRes: RfqItem[]
+
+  // 3) RfqWithItems 형태로 반환
+  const result: RfqViewWithItems = {
+    ...rfqRow,
+    lines: itemsRes,
+  };
+
+  return result;
+};
+
+/** 단건 업데이트 */
+export async function updateRfqVendor(
+  tx: PgTransaction<any, any, any>,
+  rfqVendorId: number,
+  data: Partial<VendorResponse>
+) {
+  return tx
+    .update(vendorResponses)
+    .set(data)
+    .where(eq(vendorResponses.id, rfqVendorId))
+    .returning({ status: vendorResponses.responseStatus });
+}
+
+/** 복수 업데이트 */
+export async function updateRfqVendors(
+  tx: PgTransaction<any, any, any>,
+  ids: number[],
+  data: Partial<VendorResponse>
+) {
+  return tx
+    .update(vendorResponses)
+    .set(data)
+    .where(inArray(vendorResponses.id, ids))
+    .returning({ status: vendorResponses.responseStatus });
+}