1 files changed, 282 insertions, 0 deletions
diff --git a/lib/vendors/repository.ts b/lib/vendors/repository.ts
new file mode 100644
index 00000000..ff195932
--- /dev/null
+++ b/lib/vendors/repository.ts
@@ -0,0 +1,282 @@
+// src/lib/vendors/repository.ts
+
+import { and, eq, inArray, count, gt, AnyColumn, SQLWrapper, SQL} from "drizzle-orm";
+import { PgTransaction } from "drizzle-orm/pg-core";
+import { VendorContact, vendorContacts, vendorItemsView, vendorPossibleItems, vendors, type Vendor } from "@/db/schema/vendors";
+import db from '@/db/db';
+import { items } from "@/db/schema/items";
+import { rfqs,rfqItems, rfqEvaluations, vendorResponses } from "@/db/schema/rfq";
+import { sql } from "drizzle-orm";
+
+interface SelectVendorsOptions {
+  where?: any;
+  orderBy?: any[];
+  offset?: number;
+  limit?: number;
+}
+export declare function asc(column: AnyColumn | SQLWrapper): SQL;
+export declare function desc(column: AnyColumn | SQLWrapper): SQL;
+export type NewVendorContact = typeof vendorContacts.$inferInsert
+export type NewVendorItem = typeof vendorPossibleItems.$inferInsert
+
+/**
+ * 1) SELECT (목록 조회)
+ */
+export async function selectVendors(
+    tx: PgTransaction<any, any, any>,
+  { where, orderBy, offset, limit }: SelectVendorsOptions
+) {
+  return tx
+    .select()
+    .from(vendors)
+    .where(where ?? undefined)
+    .orderBy(...(orderBy ?? []))
+    .offset(offset ?? 0)
+    .limit(limit ?? 20);
+}
+
+/**
+ * 2) COUNT
+ */
+export async function countVendors(
+    tx: PgTransaction<any, any, any>,
+    where?: any
+  ) {
+    const res = await tx.select({ count: count() }).from(vendors).where(where);
+    return res[0]?.count ?? 0;
+  }
+  
+
+/**
+ * 3) INSERT (단일 벤더 생성)
+ *    - id/createdAt/updatedAt은 DB default 사용
+ *    - 반환값은 "생성된 레코드" 배열 ([newVendor])
+ */
+export async function insertVendor(
+  tx: PgTransaction<any, any, any>,
+  data: Omit<Vendor, "id" | "createdAt" | "updatedAt">
+) {
+  return tx.insert(vendors).values(data).returning();
+}
+
+/**
+ * 4) UPDATE (단일 벤더)
+ */
+export async function updateVendor(
+  tx: PgTransaction<any, any, any>,
+  id: string,
+  data: Partial<Vendor>
+) {
+  return tx
+    .update(vendors)
+    .set(data)
+    .where(eq(vendors.id, Number(id)))
+    .returning();
+}
+
+/**
+ * 5) UPDATE (복수 벤더)
+ *    - 여러 개의 id를 받아 일괄 업데이트
+ */
+export async function updateVendors(
+  tx: PgTransaction<any, any, any>,
+  ids: string[],
+  data: Partial<Vendor>
+) {
+  const numericIds = ids.map((i) => Number(i));
+  return tx
+    .update(vendors)
+    .set(data)
+    .where(inArray(vendors.id, numericIds))
+    .returning();
+}
+
+/** status 기준 groupBy */
+export async function groupByStatus(
+    tx: PgTransaction<any, any, any>,
+) {
+  return tx
+    .select({
+      status: vendors.status,
+      count: count(),
+    })
+    .from(vendors)
+    .groupBy(vendors.status)
+    .having(gt(count(), 0));
+}
+
+
+// ID로 사용자 조회
+export const getVendorById = async (id: number): Promise<Vendor | null> => {
+  const vendorsRes = await db.select().from(vendors).where(eq(vendors.id, id)).execute();
+  if (vendorsRes.length === 0) return null;
+
+  const vendor = vendorsRes[0];
+  return vendor
+};
+
+export const getVendorContactsById = async (id: number): Promise<VendorContact | null> => {
+  const contactsRes = await db.select().from(vendorContacts).where(eq(vendorContacts.vendorId, id)).execute();
+  if (contactsRes.length === 0) return null;
+
+  const contact = contactsRes[0];
+  return contact
+};
+
+export async function selectVendorContacts(
+  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(vendorContacts)
+    .where(where)
+    .orderBy(...(orderBy ?? []))
+    .offset(offset)
+    .limit(limit);
+}
+
+export async function countVendorContacts(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx.select({ count: count() }).from(vendorContacts).where(where);
+  return res[0]?.count ?? 0;
+}
+
+export async function insertVendorContact(
+  tx: PgTransaction<any, any, any>,
+  data: NewVendorContact // DB와 동일한 insert 가능한 타입
+) {
+  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
+  return tx
+    .insert(vendorContacts)
+    .values(data)
+    .returning({ id: vendorContacts.id, createdAt: vendorContacts.createdAt });
+}
+
+
+export async function selectVendorItems(
+  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({
+    // vendor_possible_items cols
+    vendorItemId: vendorItemsView.vendorItemId,
+    vendorId: vendorItemsView.vendorId,
+    itemCode: vendorItemsView.itemCode,
+    createdAt: vendorItemsView.createdAt,
+    updatedAt: vendorItemsView.updatedAt,
+    itemName: vendorItemsView.itemName,         
+    description: vendorItemsView.description,  
+  })
+  .from(vendorItemsView)
+  .where(where ?? undefined)
+  .orderBy(...(orderBy ?? []))
+  .offset(offset)
+  .limit(limit);
+}
+
+export async function countVendorItems(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx.select({ count: count() }).from(vendorItemsView).where(where);
+  return res[0]?.count ?? 0;
+}
+
+export async function insertVendorItem(
+  tx: PgTransaction<any, any, any>,
+  data: NewVendorItem // DB와 동일한 insert 가능한 타입
+) {
+  // returning() 사용 시 배열로 돌아오므로 [0]만 리턴
+  return tx
+    .insert(vendorPossibleItems)
+    .values(data)
+    .returning({ id: vendorPossibleItems.id, createdAt: vendorPossibleItems.createdAt });
+}
+
+export async function selectRfqHistory(
+  tx: PgTransaction<any, any, any>,
+  { where, orderBy, offset, limit }: SelectVendorsOptions
+) {
+  return tx
+      .select({
+          // RFQ 기본 정보
+          id: rfqs.id,
+          rfqCode: rfqs.rfqCode,
+
+          description: rfqs.description,
+          dueDate: rfqs.dueDate,
+          status: rfqs.status,
+          createdAt: rfqs.createdAt,
+          
+   
+          // Item 정보 (집계)
+          itemCount: sql<number>`count(distinct ${rfqItems.id})::integer`,
+          
+          // 평가 정보
+          tbeResult: sql<string>`
+              (select result from ${rfqEvaluations} 
+              where rfq_id = ${rfqs.id} 
+              and vendor_id = ${vendorResponses.vendorId} 
+              and eval_type = 'TBE' 
+              limit 1)`,
+          cbeResult: sql<string>`
+              (select result from ${rfqEvaluations} 
+              where rfq_id = ${rfqs.id} 
+              and vendor_id = ${vendorResponses.vendorId} 
+              and eval_type = 'CBE' 
+              limit 1)`
+      })
+      .from(rfqs)
+      .innerJoin(vendorResponses, eq(rfqs.id, vendorResponses.rfqId))
+
+      .leftJoin(rfqItems, eq(rfqs.id, rfqItems.rfqId))
+      .where(where ?? undefined)
+      .groupBy(
+          rfqs.id,
+          rfqs.rfqCode,
+      
+          rfqs.description,
+          rfqs.dueDate,
+          rfqs.status,
+          rfqs.createdAt,
+        
+          vendorResponses.vendorId,
+     
+      )
+      .orderBy(...(orderBy ?? []))
+      .offset(offset ?? 0)
+      .limit(limit ?? 20);
+}
+
+export async function countRfqHistory(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const [{ count }] = await tx
+      .select({
+          count: sql<number>`count(distinct ${rfqs.id})::integer`,
+      })
+      .from(rfqs)
+      .innerJoin(vendorResponses, eq(rfqs.id, vendorResponses.rfqId))
+      .where(where ?? undefined);
+
+  return count;
+}