벤더 코멘트 처리

1 files changed, 247 insertions, 3 deletions

diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index b2005537..acdec3d2 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -1,7 +1,7 @@
 // db/schema/vendors.ts
 import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core";
 import { items } from "./items";
-import {  eq} from "drizzle-orm";
+import { sql, eq} from "drizzle-orm";
 
 export const vendors = pgTable("vendors", {
   id: serial("id").primaryKey(),
@@ -20,9 +20,11 @@ export const vendors = pgTable("vendors", {
       "IN_REVIEW",        // 심사 중
       "REJECTED",         // 심사 거부됨
       "IN_PQ",            // PQ 진행 중
-      "PQ_SUBMITTED",        // PQ 제출
+      "PQ_SUBMITTED",     // PQ 제출
       "PQ_FAILED",        // PQ 실패
+      "PQ_APPROVED",      // PQ 통과, 승인됨
       "APPROVED",         // PQ 통과, 승인됨
+      "READY_TO_SEND",         // PQ 통과, 승인됨
       "ACTIVE",           // 활성 상태 (실제 거래 중)
       "INACTIVE",         // 비활성 상태 (일시적)
       "BLACKLISTED",      // 거래 금지 상태
@@ -115,4 +117,246 @@ export type VendorWithAttachments = Vendor & {
   attachmentsList?: VendorAttach[];
 }
 
-export type VendorItemsView = typeof vendorItemsView.$inferSelect
\ No newline at end of file
+export type VendorItemsView = typeof vendorItemsView.$inferSelect
+
+export const vendorInvestigations = pgTable("vendor_investigations", {
+  id: serial("id").primaryKey(),
+
+  // 어떤 벤더에 대한 실사인지 참조
+  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+
+  // 실사 상태
+  // 예: "PLANNED" | "IN_PROGRESS" | "COMPLETED" | "CANCELED"
+  investigationStatus: varchar("investigation_status", {
+    length: 50,
+    enum: [
+      "PLANNED",
+      "IN_PROGRESS",
+      "COMPLETED",
+      "CANCELED",
+    ],
+  })
+    .notNull()
+    .default("PLANNED"),
+
+  // 실사 일정 시작일 / 종료일
+  scheduledStartAt: timestamp("scheduled_start_at"),
+  scheduledEndAt: timestamp("scheduled_end_at"),
+
+  // 실제 실사 완료일
+  completedAt: timestamp("completed_at"),
+
+  // 실사 내용이나 특이사항
+  investigationNotes: text("investigation_notes"),
+
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 타입 정의
+export type VendorInvestigation = typeof vendorInvestigations.$inferSelect;
+
+export const vendorInvestigationAttachments = pgTable(
+  "vendor_investigation_attachments",
+  {
+    id: serial("id").primaryKey(),
+    // 어떤 실사 (investigation)에 대한 첨부파일인지
+    investigationId: integer("investigation_id")
+      .notNull()
+      .references(() => vendorInvestigations.id, { onDelete: "cascade" }),
+
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    filePath: varchar("file_path", { length: 1024 }).notNull(),
+
+    // 첨부파일 종류 (예: 보고서, 사진, 기타 등 구분)
+    attachmentType: varchar("attachment_type", { length: 50 }).default("REPORT"),
+
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  }
+);
+
+// 첨부파일 테이블 타입
+export type VendorInvestigationAttachment =
+  typeof vendorInvestigationAttachments.$inferSelect;
+
+/**
+ * A view that joins vendor_investigations + vendors,
+ * and also embeds contacts & possibleItems as JSON arrays.
+ */
+export const vendorInvestigationsView = pgView(
+  "vendor_investigations_view"
+).as((qb) => {
+  return qb
+    .select({
+      // Investigation fields
+      investigationId: vendorInvestigations.id,
+      investigationStatus: vendorInvestigations.investigationStatus,
+      scheduledStartAt: vendorInvestigations.scheduledStartAt,
+      scheduledEndAt: vendorInvestigations.scheduledEndAt,
+      completedAt: vendorInvestigations.completedAt,
+      investigationNotes: vendorInvestigations.investigationNotes,
+      investigationCreatedAt: vendorInvestigations.createdAt,
+      investigationUpdatedAt: vendorInvestigations.updatedAt,
+      
+      // Vendor fields
+      vendorId: vendorInvestigations.vendorId,
+      vendorName: vendors.vendorName,
+      vendorCode: vendors.vendorCode,
+      vendorTaxId: vendors.taxId,
+      vendorStatus: vendors.status,
+      vendorCountry: vendors.country,
+      vendorEmail: vendors.email,
+      vendorPhone: vendors.phone,
+      vendorWebsite: vendors.website,
+
+      // JSON-aggregated contacts
+      contacts: sql<string>`(
+        SELECT COALESCE(
+          json_agg(json_build_object(
+            'contactName', c.contact_name,
+            'contactEmail', c.contact_email,
+            'contactPhone', c.contact_phone,
+            'contactPosition', c.contact_position,
+            'isPrimary', c.is_primary,
+            'contactCreatedAt', c.created_at,
+            'contactUpdatedAt', c.updated_at
+          )),
+          '[]'::json
+        )
+        FROM vendor_contacts c
+        WHERE c.vendor_id = ${vendors.id}
+      )`.as("contacts"),
+      
+      // JSON-aggregated possible items with itemName from items table
+      possibleItems: sql<string>`(
+        SELECT COALESCE(
+          json_agg(json_build_object(
+            'itemCode', pi.item_code,
+            'itemName', i.item_name,
+            'itemCreatedAt', pi.created_at,
+            'itemUpdatedAt', pi.updated_at
+          )),
+          '[]'::json
+        )
+        FROM vendor_possible_items pi
+        LEFT JOIN items i ON pi.item_code = i.item_code
+        WHERE pi.vendor_id = ${vendors.id}
+      )`.as("possibleItems"),
+    })
+    .from(vendorInvestigations)
+    .leftJoin(
+      vendors,
+      eq(vendorInvestigations.vendorId, vendors.id)
+    )
+})
+
+
+export const vendorCandidates = pgTable("vendor_candidates", {
+  id: serial("id").primaryKey(),
+  companyName: varchar("company_name", { length: 255 }).notNull(),
+  contactEmail: varchar("contact_email", { length: 255 }).notNull(),
+  contactPhone: varchar("contact_phone", { length: 50 }),
+  country: varchar("country", { length: 100 }),
+  // 웹 크롤링 등으로 얻은 상태나 분류
+  source: varchar("source", { length: 100 }),        // 수집 출처
+  status: varchar("status", {
+    length: 30,
+    enum: [
+      "COLLECTED",  // 단순 데이터 수집 완료
+      "INVITED",    // 초청 메일 발송
+      "DISCARDED",  // 불필요, 검토 후 배제됨
+    ],
+  })
+    .notNull()
+    .default("COLLECTED"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export type VendorCandidates = typeof vendorCandidates.$inferSelect;
+
+
+export const vendorDetailView = pgView("vendor_detail_view").as((qb) => {
+  return qb
+    .select({
+      // 벤더 기본 정보
+      id: vendors.id,
+      vendorName: vendors.vendorName,
+      vendorCode: vendors.vendorCode,
+      taxId: vendors.taxId,
+      address: vendors.address,
+      country: vendors.country,
+      phone: vendors.phone,
+      email: vendors.email,
+      website: vendors.website,
+      status: vendors.status,
+      representativeName: vendors.representativeName,
+      representativeBirth: vendors.representativeBirth,
+      representativeEmail: vendors.representativeEmail,
+      representativePhone: vendors.representativePhone,
+      corporateRegistrationNumber: vendors.corporateRegistrationNumber,
+      creditAgency: vendors.creditAgency,
+      creditRating: vendors.creditRating,
+      cashFlowRating: vendors.cashFlowRating,
+      createdAt: vendors.createdAt,
+      updatedAt: vendors.updatedAt,
+      
+      // 연락처 정보 (수정된 버전)
+      contacts: sql<string>`
+        (SELECT COALESCE(
+          json_agg(
+            json_build_object(
+              'id', c.id,
+              'contactName', c.contact_name,
+              'contactPosition', c.contact_position,
+              'contactEmail', c.contact_email,
+              'contactPhone', c.contact_phone,
+              'isPrimary', c.is_primary
+            )
+          ),
+          '[]'::json
+        )
+        FROM vendor_contacts c
+        WHERE c.vendor_id = vendors.id)
+      `.as("contacts"),
+      
+      // 첨부파일 정보 (수정된 버전)
+      attachments: sql<string>`
+        (SELECT COALESCE(
+          json_agg(
+            json_build_object(
+              'id', a.id,
+              'fileName', a.file_name,
+              'filePath', a.file_path,
+              'attachmentType', a.attachment_type,
+              'createdAt', a.created_at
+            )
+            ORDER BY a.attachment_type, a.created_at DESC
+          ),
+          '[]'::json
+        )
+        FROM vendor_attachments a
+        WHERE a.vendor_id = vendors.id)
+      `.as("attachments"),
+      
+      // 첨부파일 수 (수정된 버전)
+      attachmentCount: sql<number>`
+        (SELECT COUNT(*)
+        FROM vendor_attachments a
+        WHERE a.vendor_id = vendors.id)
+      `.as("attachment_count"),
+      
+      // 연락처 수 (수정된 버전)
+      contactCount: sql<number>`
+        (SELECT COUNT(*) 
+        FROM vendor_contacts c
+        WHERE c.vendor_id = vendors.id)
+      `.as("contact_count")
+    })
+    .from(vendors)
+});
+
+// 타입 정의
+export type VendorDetailView = typeof vendorDetailView.$inferSelect;
\ No newline at end of file