1 files changed, 43 insertions, 3 deletions
diff --git a/db/schema/pq.ts b/db/schema/pq.ts
index 76d15d5d..9aac7b89 100644
--- a/db/schema/pq.ts
+++ b/db/schema/pq.ts
@@ -1,9 +1,10 @@
 import {
     pgTable, serial, integer, varchar, text,
-    timestamp, uniqueIndex
+    timestamp, uniqueIndex, pgView
 } from "drizzle-orm/pg-core";
-import { vendors } from "./vendors";
+import { vendorTypes, vendors } from "./vendors";
 import { projects } from "./projects";
+import { sql } from "drizzle-orm";
 
 export const pqCriterias = pgTable("pq_criterias", {
     id: serial("id").primaryKey(),
@@ -81,7 +82,7 @@ export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
 export type PqCriterias = typeof pqCriterias.$inferSelect
 
 
-// 벤더와 프로젝트 PQ 요청 연결 테이블
+// 협력업체와 프로젝트 PQ 요청 연결 테이블
 export const vendorProjectPQs = pgTable("vendor_project_pqs", {
   id: serial("id").primaryKey(),
   
@@ -144,3 +145,42 @@ export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
   createdAt: timestamp("created_at").defaultNow().notNull(),
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
+
+
+export const projectApprovedVendors = pgView("project_approved_vendors").as((qb) => {
+  return qb
+    .select({
+      vendor_id: vendors.id,
+      vendor_name: vendors.vendorName,
+      vendor_code: vendors.vendorCode,
+      tax_id: vendors.taxId,
+      vendor_email: vendors.email,
+      vendor_phone: vendors.phone,
+      vendor_status: vendors.status,
+      // vendor_type_code: vendorTypes.code,
+      vendor_type_name_ko: vendorTypes.nameKo,
+      vendor_type_name_en: vendorTypes.nameEn,
+      project_code: projects.code,
+      project_name: projects.name,
+      project_type: projects.type,
+      // pq_status: vendorProjectPQs.status,
+      submitted_at: vendorProjectPQs.submittedAt,
+      approved_at: vendorProjectPQs.approvedAt
+    })
+    .from(vendors)
+    .innerJoin(
+      vendorProjectPQs,
+      sql`${vendorProjectPQs.vendorId} = ${vendors.id}`
+    )
+    .innerJoin(
+      projects,
+      sql`${vendorProjectPQs.projectId} = ${projects.id}`
+    )
+    .leftJoin(
+      vendorTypes,
+      sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
+    )
+    .where(sql`${vendorProjectPQs.status} = 'APPROVED'`);
+});
+
+export type ProjectApprovedVendors = typeof projectApprovedVendors.$inferSelect