1 files changed, 380 insertions, 0 deletions
diff --git a/lib/techsales-rfq/repository.ts b/lib/techsales-rfq/repository.ts
new file mode 100644
index 00000000..260eef19
--- /dev/null
+++ b/lib/techsales-rfq/repository.ts
@@ -0,0 +1,380 @@
+/* eslint-disable @typescript-eslint/no-explicit-any */
+
+import {
+  techSalesRfqs,
+  techSalesVendorQuotations,
+  items,
+  vendors,
+  users
+} from "@/db/schema";
+import {
+    asc,
+    desc, count, SQL, sql
+} from "drizzle-orm";
+import { PgTransaction } from "drizzle-orm/pg-core";
+
+
+export type NewTechSalesRfq = typeof techSalesRfqs.$inferInsert;
+/**
+ * 기술영업 RFQ 생성
+ * ID 및 생성일 리턴
+ */
+export async function insertTechSalesRfq(
+  tx: PgTransaction<any, any, any>,
+  data: NewTechSalesRfq
+) {
+  return tx
+  .insert(techSalesRfqs)
+  .values(data)
+  .returning({ id: techSalesRfqs.id, createdAt: techSalesRfqs.createdAt });
+}
+
+/**
+ * 단건/복수 조회 시 공통으로 사용 가능한 SELECT 함수 예시
+ *  - 트랜잭션(tx)을 받아서 사용하도록 구현
+ */
+export async function selectTechSalesRfqs(
+  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()
+    .from(techSalesRfqs)
+    .where(where ?? undefined)
+    .orderBy(...(orderBy ?? []))
+    .offset(offset)
+    .limit(limit);
+}
+/** 총 개수 count */
+export async function countTechSalesRfqs(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx.select({ count: count() }).from(techSalesRfqs).where(where);
+  return res[0]?.count ?? 0;
+}
+
+/**
+ * RFQ 정보 직접 조인 조회 (뷰 대신 테이블 조인 사용) 
+ */
+export async function selectTechSalesRfqsWithJoin(
+  tx: PgTransaction<any, any, any>,
+  params: {
+    where?: any;
+    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
+    offset?: number;
+    limit?: number;
+  }
+) {
+  const { where, orderBy, offset = 0, limit = 10 } = params;
+
+  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
+  const query = tx.select({
+    // RFQ 기본 정보
+    id: techSalesRfqs.id,
+    rfqCode: techSalesRfqs.rfqCode,
+    itemId: techSalesRfqs.itemId,
+    itemName: items.itemName,
+    materialCode: techSalesRfqs.materialCode,
+    
+    // 날짜 및 상태 정보
+    dueDate: techSalesRfqs.dueDate,
+    rfqSendDate: techSalesRfqs.rfqSendDate,
+    status: techSalesRfqs.status,
+    
+    // 담당자 및 비고
+    picCode: techSalesRfqs.picCode,
+    remark: techSalesRfqs.remark,
+    cancelReason: techSalesRfqs.cancelReason,
+    
+    // 생성/수정 정보
+    createdAt: techSalesRfqs.createdAt,
+    updatedAt: techSalesRfqs.updatedAt,
+    
+    // 사용자 정보
+    createdBy: techSalesRfqs.createdBy,
+    createdByName: sql<string>`created_user.name`,
+    updatedBy: techSalesRfqs.updatedBy,
+    updatedByName: sql<string>`updated_user.name`,
+    sentBy: techSalesRfqs.sentBy,
+    sentByName: sql<string | null>`sent_user.name`,
+    
+    // 프로젝트 정보 (스냅샷)
+    projectSnapshot: techSalesRfqs.projectSnapshot,
+    seriesSnapshot: techSalesRfqs.seriesSnapshot,
+    
+    // 프로젝트 핵심 정보
+    pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
+    projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
+    sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
+    projMsrm: sql<number>`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`,
+    ptypeNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`,
+    
+    // 첨부파일 개수
+    attachmentCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_attachments 
+      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
+    )`,
+    
+    // 벤더 견적 개수
+    quotationCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+    )`,
+  })
+  .from(techSalesRfqs)
+  .leftJoin(items, sql`${techSalesRfqs.itemId} = ${items.id}`)
+  .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`)
+  .leftJoin(sql`${users} AS updated_user`, sql`${techSalesRfqs.updatedBy} = updated_user.id`)
+  .leftJoin(sql`${users} AS sent_user`, sql`${techSalesRfqs.sentBy} = sent_user.id`);
+
+  // where 조건 적용
+  const queryWithWhere = where ? query.where(where) : query;
+  
+  // orderBy 적용
+  const queryWithOrderBy = orderBy?.length 
+    ? queryWithWhere.orderBy(...orderBy)
+    : queryWithWhere.orderBy(desc(techSalesRfqs.createdAt));
+  
+  // offset과 limit 적용 후 실행
+  return queryWithOrderBy.offset(offset).limit(limit);
+}
+
+/**
+ * RFQ 개수 직접 조회 (뷰 대신 테이블 조인 사용)
+ */
+export async function countTechSalesRfqsWithJoin(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx
+    .select({ count: count() })
+    .from(techSalesRfqs)
+    .leftJoin(items, sql`${techSalesRfqs.itemId} = ${items.id}`)
+    .where(where ?? undefined);
+  return res[0]?.count ?? 0;
+}
+
+/**
+ * 벤더 견적서 직접 조인 조회 (뷰 대신 테이블 조인 사용)
+ */
+export async function selectTechSalesVendorQuotationsWithJoin(
+  tx: PgTransaction<any, any, any>,
+  params: {
+    where?: any;
+    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
+    offset?: number;
+    limit?: number;
+  }
+) {
+  const { where, orderBy, offset = 0, limit = 10 } = params;
+
+  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
+  const query = tx.select({
+    // 견적 기본 정보
+    id: techSalesVendorQuotations.id,
+    rfqId: techSalesVendorQuotations.rfqId,
+    rfqCode: techSalesRfqs.rfqCode,
+    vendorId: techSalesVendorQuotations.vendorId,
+    vendorName: vendors.vendorName,
+    vendorCode: vendors.vendorCode,
+    
+    // 견적 상세 정보
+    totalPrice: techSalesVendorQuotations.totalPrice,
+    currency: techSalesVendorQuotations.currency,
+    validUntil: techSalesVendorQuotations.validUntil,
+    status: techSalesVendorQuotations.status,
+    remark: techSalesVendorQuotations.remark,
+    rejectionReason: techSalesVendorQuotations.rejectionReason,
+    
+    // 날짜 정보
+    submittedAt: techSalesVendorQuotations.submittedAt,
+    acceptedAt: techSalesVendorQuotations.acceptedAt,
+    createdAt: techSalesVendorQuotations.createdAt,
+    updatedAt: techSalesVendorQuotations.updatedAt,
+    
+    // 생성/수정 사용자
+    createdBy: techSalesVendorQuotations.createdBy,
+    createdByName: sql<string | null>`created_user.name`,
+    updatedBy: techSalesVendorQuotations.updatedBy,
+    updatedByName: sql<string | null>`updated_user.name`,
+    
+    // 프로젝트 정보
+    materialCode: techSalesRfqs.materialCode,
+    itemId: techSalesRfqs.itemId,
+    itemName: items.itemName,
+    
+    // 프로젝트 핵심 정보
+    pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
+    projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
+    sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
+    
+    // 첨부파일 개수
+    attachmentCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_rfq_comment_attachments 
+      WHERE tech_sales_rfq_comment_attachments.quotation_id = ${techSalesVendorQuotations.id}
+    )`,
+  })
+  .from(techSalesVendorQuotations)
+  .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
+  .leftJoin(vendors, sql`${techSalesVendorQuotations.vendorId} = ${vendors.id}`)
+  .leftJoin(items, sql`${techSalesRfqs.itemId} = ${items.id}`)
+  .leftJoin(sql`${users} AS created_user`, sql`${techSalesVendorQuotations.createdBy} = created_user.id`)
+  .leftJoin(sql`${users} AS updated_user`, sql`${techSalesVendorQuotations.updatedBy} = updated_user.id`);
+
+  // where 조건 적용
+  const queryWithWhere = where ? query.where(where) : query;
+  
+  // orderBy 적용
+  const queryWithOrderBy = orderBy?.length 
+    ? queryWithWhere.orderBy(...orderBy)
+    : queryWithWhere.orderBy(desc(techSalesVendorQuotations.createdAt));
+  
+  // offset과 limit 적용 후 실행
+  return queryWithOrderBy.offset(offset).limit(limit);
+}
+
+/**
+ * 벤더 견적서 개수 직접 조회 (뷰 대신 테이블 조인 사용)
+ */
+export async function countTechSalesVendorQuotationsWithJoin(
+  tx: PgTransaction<any, any, any>,
+  where?: any
+) {
+  const res = await tx
+    .select({ count: count() })
+    .from(techSalesVendorQuotations)
+    .leftJoin(techSalesRfqs, sql`${techSalesVendorQuotations.rfqId} = ${techSalesRfqs.id}`)
+    .leftJoin(vendors, sql`${techSalesVendorQuotations.vendorId} = ${vendors.id}`)
+    .where(where ?? undefined);
+  return res[0]?.count ?? 0;
+}
+
+/**
+ * RFQ 대시보드 데이터 직접 조인 조회 (뷰 대신 테이블 조인 사용)
+ */
+export async function selectTechSalesDashboardWithJoin(
+  tx: PgTransaction<any, any, any>,
+  params: {
+    where?: any;
+    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc> | SQL<unknown>)[];
+    offset?: number;
+    limit?: number;
+  }
+) {
+  const { where, orderBy, offset = 0, limit = 10 } = params;
+
+  // 별칭 방식을 변경하여 select, join, where, orderBy 등을 순차적으로 수행
+  const query = tx.select({
+    // RFQ 기본 정보
+    id: techSalesRfqs.id,
+    rfqCode: techSalesRfqs.rfqCode,
+    status: techSalesRfqs.status,
+    dueDate: techSalesRfqs.dueDate,
+    rfqSendDate: techSalesRfqs.rfqSendDate,
+    materialCode: techSalesRfqs.materialCode,
+    
+    // 아이템 정보
+    itemId: techSalesRfqs.itemId,
+    itemName: items.itemName,
+    
+    // 프로젝트 정보
+    pspid: sql<string>`${techSalesRfqs.projectSnapshot}->>'pspid'`,
+    projNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'projNm'`,
+    sector: sql<string>`${techSalesRfqs.projectSnapshot}->>'sector'`,
+    projMsrm: sql<number>`(${techSalesRfqs.projectSnapshot}->>'projMsrm')::int`,
+    ptypeNm: sql<string>`${techSalesRfqs.projectSnapshot}->>'ptypeNm'`,
+    
+    // 벤더 견적 통계
+    vendorCount: sql<number>`(
+      SELECT COUNT(DISTINCT vendor_id) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+    )`,
+    
+    quotationCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+    )`,
+    
+    submittedQuotationCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+      AND tech_sales_vendor_quotations.status = 'Submitted'
+    )`,
+    
+    minPrice: sql<string | null>`(
+      SELECT MIN(total_price) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+      AND tech_sales_vendor_quotations.status = 'Submitted'
+    )`,
+    
+    maxPrice: sql<string | null>`(
+      SELECT MAX(total_price) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+      AND tech_sales_vendor_quotations.status = 'Submitted'
+    )`,
+    
+    avgPrice: sql<string | null>`(
+      SELECT AVG(total_price) 
+      FROM tech_sales_vendor_quotations 
+      WHERE tech_sales_vendor_quotations.rfq_id = ${techSalesRfqs.id}
+      AND tech_sales_vendor_quotations.status = 'Submitted'
+    )`,
+    
+    // 첨부파일 통계
+    attachmentCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_attachments 
+      WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
+    )`,
+    
+    // 코멘트 통계
+    commentCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_rfq_comments 
+      WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id}
+    )`,
+    
+    unreadCommentCount: sql<number>`(
+      SELECT COUNT(*) 
+      FROM tech_sales_rfq_comments 
+      WHERE tech_sales_rfq_comments.rfq_id = ${techSalesRfqs.id}
+      AND tech_sales_rfq_comments.is_read = false
+    )`,
+    
+    // 생성/수정 정보
+    createdAt: techSalesRfqs.createdAt,
+    updatedAt: techSalesRfqs.updatedAt,
+    createdByName: sql<string>`created_user.name`,
+  })
+  .from(techSalesRfqs)
+  .leftJoin(items, sql`${techSalesRfqs.itemId} = ${items.id}`)
+  .leftJoin(sql`${users} AS created_user`, sql`${techSalesRfqs.createdBy} = created_user.id`);
+
+  // where 조건 적용
+  const queryWithWhere = where ? query.where(where) : query;
+  
+  // orderBy 적용
+  const queryWithOrderBy = orderBy?.length 
+    ? queryWithWhere.orderBy(...orderBy)
+    : queryWithWhere.orderBy(desc(techSalesRfqs.updatedAt));
+  
+  // offset과 limit 적용 후 실행
+  return queryWithOrderBy.offset(offset).limit(limit);
+}
+