(대표님 / 최겸) 스키마 변경사항 및 마이그레이션 커밋

1 files changed, 331 insertions, 78 deletions

diff --git a/db/schema/pq.ts b/db/schema/pq.ts
index 9aac7b89..c5f8c732 100644
--- a/db/schema/pq.ts
+++ b/db/schema/pq.ts
@@ -1,91 +1,90 @@
 import {
-    pgTable, serial, integer, varchar, text,
-    timestamp, uniqueIndex, pgView
+  pgTable, serial, integer, varchar, text,
+  timestamp, uniqueIndex, pgView
 } from "drizzle-orm/pg-core";
 import { vendorTypes, vendors } from "./vendors";
 import { projects } from "./projects";
-import { sql } from "drizzle-orm";
+import { sql, eq } from "drizzle-orm";
+import { users } from "./users";
 
 export const pqCriterias = pgTable("pq_criterias", {
-    id: serial("id").primaryKey(),
-    code: varchar("code", { length: 50 }).notNull(),        // 예: "1-1"
-    checkPoint: varchar("check_point", { length: 255 }).notNull(),
-    description: text("description"),
-    remarks: text("remarks"),
+  id: serial("id").primaryKey(),
+  code: varchar("code", { length: 50 }).notNull(),        // 예: "1-1"
+  checkPoint: varchar("check_point", { length: 255 }).notNull(),
+  description: text("description"),
+  remarks: text("remarks"),
 
-    // (선택) "GENERAL", "Quality Management System" 등 큰 분류
-    groupName: varchar("group_name", { length: 255 }),
-    createdAt: timestamp("created_at").defaultNow().notNull(),
-    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  // (선택) "GENERAL", "Quality Management System" 등 큰 분류
+  groupName: varchar("group_name", { length: 255 }),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
 
 export const pqCriteriasExtension = pgTable("pq_criterias_extension", {
-    id: serial("id").primaryKey(),
-  
-    // pq_criterias와 연결 (1:1 or 1:N 중 필요에 맞춰 사용)
-    pqCriteriaId: integer("pq_criteria_id")
-      .notNull()
-      .references(() => pqCriterias.id, {
-        onDelete: "cascade",
-        onUpdate: "cascade",
-      }),
-  
-    // projects 테이블에 대한 FK
-    projectId: integer("project_id")
-      .notNull()
-      .references(() => projects.id, {
-        onDelete: "cascade",
-        onUpdate: "cascade",
-      }),
-  
-    // 프로젝트별 PQ 시 필요한 추가 정보
-    contractInfo: text("contract_info"),
-    additionalRequirement: text("additional_requirement"),
-  
-    createdAt: timestamp("created_at").defaultNow().notNull(),
-    updatedAt: timestamp("updated_at").defaultNow().notNull(),
-  });
+  id: serial("id").primaryKey(),
+
+  // pq_criterias와 연결 (1:1 or 1:N 중 필요에 맞춰 사용)
+  pqCriteriaId: integer("pq_criteria_id")
+    .notNull()
+    .references(() => pqCriterias.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
 
+  // projects 테이블에 대한 FK
+  projectId: integer("project_id")
+    .notNull()
+    .references(() => projects.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
 
+  // 프로젝트별 PQ 시 필요한 추가 정보
+  contractInfo: text("contract_info"),
+  additionalRequirement: text("additional_requirement"),
+
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
 
 export const vendorCriteriaAttachments = pgTable("vendor_criteria_attachments", {
-    id: serial("id").primaryKey(),
-    vendorCriteriaAnswerId: integer("vendor_criteria_answer_id")
-        .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
-        .notNull(),
+  id: serial("id").primaryKey(),
+  vendorCriteriaAnswerId: integer("vendor_criteria_answer_id")
+    .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
+    .notNull(),
 
-    fileName: varchar("file_name", { length: 255 }).notNull(),
-    filePath: varchar("file_path", { length: 1024 }).notNull(),
-    fileType: varchar("file_type", { length: 50 }),
-    fileSize: integer("file_size"),
+  fileName: varchar("file_name", { length: 255 }).notNull(),
+  filePath: varchar("file_path", { length: 1024 }).notNull(),
+  fileType: varchar("file_type", { length: 50 }),
+  fileSize: integer("file_size"),
 
-    createdAt: timestamp("created_at").defaultNow().notNull(),
-    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
 
 export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
-    id: serial("id").primaryKey(),
-  
-    // Each log references a single vendorPqCriteriaAnswers row
-    vendorPqCriteriaAnswerId: integer("vendor_pq_criteria_answer_id")
-      .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
-      .notNull(),
-  
-    // The reviewer's comment text
-    reviewerComment: text("reviewer_comment").notNull(),
-  
-    // Optionally store the reviewer name or user ID, if you have it
-    reviewerName: text("reviewer_name"),
-  
-    createdAt: timestamp("created_at").defaultNow().notNull(),
-  })
+  id: serial("id").primaryKey(),
+
+  // Each log references a single vendorPqCriteriaAnswers row
+  vendorPqCriteriaAnswerId: integer("vendor_pq_criteria_answer_id")
+    .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
+    .notNull(),
+
+  // The reviewer's comment text
+  reviewerComment: text("reviewer_comment").notNull(),
+
+  // Optionally store the reviewer name or user ID, if you have it
+  reviewerName: text("reviewer_name"),
+
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+})
 export type PqCriterias = typeof pqCriterias.$inferSelect
 
 
 // 협력업체와 프로젝트 PQ 요청 연결 테이블
 export const vendorProjectPQs = pgTable("vendor_project_pqs", {
   id: serial("id").primaryKey(),
-  
+
   // vendors 테이블 FK
   vendorId: integer("vendor_id")
     .notNull()
@@ -93,7 +92,7 @@ export const vendorProjectPQs = pgTable("vendor_project_pqs", {
       onDelete: "cascade",
       onUpdate: "cascade",
     }),
-  
+
   // projects 테이블 FK
   projectId: integer("project_id")
     .notNull()
@@ -101,47 +100,92 @@ export const vendorProjectPQs = pgTable("vendor_project_pqs", {
       onDelete: "cascade",
       onUpdate: "cascade",
     }),
-  
+
   // 상태: REQUESTED(요청됨), IN_PROGRESS(진행중), SUBMITTED(제출됨), APPROVED(승인됨), REJECTED(거부됨)
   status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
-  
+
   // 메타데이터
   submittedAt: timestamp("submitted_at"),
   approvedAt: timestamp("approved_at"),
   rejectedAt: timestamp("rejected_at"),
   rejectReason: text("reject_reason"),
-  
+
   createdAt: timestamp("created_at").defaultNow().notNull(),
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
 
+export const vendorPQSubmissions = pgTable("vendor_pq_submissions", {
+  id: serial("id").primaryKey(),
+
+  pqNumber: varchar("pq_number", { length: 50 }).notNull().unique(),
+
+  requesterId: integer("requester_id")
+    .references(() => users.id),
+
+  vendorId: integer("vendor_id")
+    .notNull()
+    .references(() => vendors.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+
+  // null이면 일반 PQ, 값이 있으면 프로젝트 PQ
+  projectId: integer("project_id")
+    .references(() => projects.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+
+  // PQ 유형 구분을 명시적으로
+  type: varchar("type", { length: 20 }).notNull(), // "GENERAL" or "PROJECT"
+
+  status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
+
+  submittedAt: timestamp("submitted_at"),
+  approvedAt: timestamp("approved_at"),
+  rejectedAt: timestamp("rejected_at"),
+  rejectReason: text("reject_reason"),
+
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => {
+  return {
+    // 협력업체별로 일반 PQ는 하나만, 프로젝트 PQ는 프로젝트당 하나만
+    uniqueConstraint: uniqueIndex("unique_pq_submission").on(
+      table.vendorId,
+      table.projectId,
+      table.type
+    ),
+  };
+});
+
 // 기존 vendorPqCriteriaAnswers 테이블에 projectId 필드 추가
 export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
   id: serial("id").primaryKey(),
-  
+
   vendorId: integer("vendor_id")
     .notNull()
     .references(() => vendors.id, {
       onDelete: "cascade",
       onUpdate: "cascade",
     }),
-  
+
   criteriaId: integer("criteria_id")
     .notNull()
     .references(() => pqCriterias.id, {
       onDelete: "cascade",
       onUpdate: "cascade",
     }),
-  
+
   // 추가: 프로젝트 ID (null은 일반 PQ를 의미)
   projectId: integer("project_id")
     .references(() => projects.id, {
       onDelete: "cascade",
       onUpdate: "cascade",
     }),
-  
+
   answer: text("answer"),
-  
+
   createdAt: timestamp("created_at").defaultNow().notNull(),
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
@@ -164,23 +208,232 @@ export const projectApprovedVendors = pgView("project_approved_vendors").as((qb)
       project_name: projects.name,
       project_type: projects.type,
       // pq_status: vendorProjectPQs.status,
-      submitted_at: vendorProjectPQs.submittedAt,
-      approved_at: vendorProjectPQs.approvedAt
+      submitted_at: vendorPQSubmissions.submittedAt,
+      approved_at: vendorPQSubmissions.approvedAt
     })
     .from(vendors)
     .innerJoin(
-      vendorProjectPQs,
-      sql`${vendorProjectPQs.vendorId} = ${vendors.id}`
+      vendorPQSubmissions,
+      sql`${vendorPQSubmissions.vendorId} = ${vendors.id}`
     )
     .innerJoin(
       projects,
-      sql`${vendorProjectPQs.projectId} = ${projects.id}`
+      sql`${vendorPQSubmissions.projectId} = ${projects.id}`
     )
     .leftJoin(
       vendorTypes,
       sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
     )
-    .where(sql`${vendorProjectPQs.status} = 'APPROVED'`);
+    .where(sql`${vendorPQSubmissions.status} = 'APPROVED'`);
 });
 
 export type ProjectApprovedVendors = typeof projectApprovedVendors.$inferSelect
+
+
+
+
+export const vendorInvestigations = pgTable("vendor_investigations", {
+  id: serial("id").primaryKey(),
+
+  // 어떤 벤더에 대한 실사인지 참조
+  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+
+  // PQ 제출에 대한 참조
+  pqSubmissionId: integer("pq_submission_id")
+    .references(() => vendorPQSubmissions.id, {
+      onDelete: "set null",
+      onUpdate: "cascade",
+    }),
+
+  requesterId: integer("requester_id")
+    .references(() => users.id),
+
+  qmManagerId: integer("qm_manager_id")
+    .references(() => users.id),
+
+  // 실사 상태
+  investigationStatus: varchar("investigation_status", {
+    length: 50,
+    enum: [
+      "PLANNED",      // 계획됨
+      "IN_PROGRESS",  // 진행 중
+      "COMPLETED",    // 완료됨
+      "CANCELED",     // 취소됨
+    ],
+  })
+    .notNull()
+    .default("PLANNED"),
+
+  // 평가 유형
+  evaluationType: varchar("evaluation_type", {
+    length: 50,
+    enum: [
+      "SITE_AUDIT",    // 실사의뢰평가
+      "QM_SELF_AUDIT", // QM자체평가
+    ],
+  }),
+
+  // 실사 주소
+  investigationAddress: text("investigation_address"),
+
+  // 실사 방법
+  investigationMethod: varchar("investigation_method", { length: 100 }),
+
+  // 실사 일정 시작일 / 종료일
+  scheduledStartAt: timestamp("scheduled_start_at"),
+  scheduledEndAt: timestamp("scheduled_end_at"),
+
+
+  // 실사 예정일
+  forecastedAt: timestamp("forecasted_at"),
+
+  // 실사 의뢰일
+  requestedAt: timestamp("requested_at"),
+
+  // 실사 확정일
+  confirmedAt: timestamp("confirmed_at"),
+
+  // 실제 실사 완료일
+  completedAt: timestamp("completed_at"),
+
+  // 실사 평가점수
+  evaluationScore: integer("evaluation_score"),
+
+  // 실사 평가 결과
+  evaluationResult: varchar("evaluation_result", {
+    length: 50,
+    enum: [
+      "APPROVED",     // 승인
+      "SUPPLEMENT",   // 보완
+      "REJECTED",     // 불가
+    ],
+  }),
+
+  // 실사 내용이나 특이사항
+  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(),
+    
+    // 권장: 사용자 경험과 기능성을 위해 추가
+    fileSize: integer("file_size"), // bytes (nullable로 기존 데이터 호환)
+    mimeType: varchar("mime_type", { length: 100 }), // nullable로 기존 데이터 호환
+    
+    // 첨부파일 종류 (예: 보고서, 사진, 기타 등 구분)
+    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;
+export type NewVendorInvestigationAttachment = typeof vendorInvestigationAttachments.$inferInsert;
+
+// 파일 업로드 관련 유틸리티 타입
+export interface FileUploadResult {
+  id: number;
+  fileName: string;
+  originalFileName: string;
+  filePath: string;
+  fileSize: number;
+  mimeType: string;
+  fileType: string;
+  uploadedBy: number;
+  description?: string;
+}
+
+// 파일 다운로드용 타입
+export interface FileDownloadInfo {
+  fileName: string;
+  originalFileName: string;
+  filePath: string;
+  mimeType: string;
+  fileSize: number;
+}
+
+/**
+ * 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 - use investigationId as the primary identifier
+      investigationId: vendorInvestigations.id,
+      vendorId: vendorInvestigations.vendorId,
+      pqSubmissionId: vendorInvestigations.pqSubmissionId,
+      requesterId: vendorInvestigations.requesterId,
+      qmManagerId: vendorInvestigations.qmManagerId,
+      investigationStatus: vendorInvestigations.investigationStatus,
+      evaluationType: vendorInvestigations.evaluationType,
+      investigationAddress: vendorInvestigations.investigationAddress,
+      investigationMethod: vendorInvestigations.investigationMethod,
+      scheduledStartAt: vendorInvestigations.scheduledStartAt,
+      scheduledEndAt: vendorInvestigations.scheduledEndAt,
+      forecastedAt: vendorInvestigations.forecastedAt,
+      requestedAt: vendorInvestigations.requestedAt,
+      confirmedAt: vendorInvestigations.confirmedAt,
+      completedAt: vendorInvestigations.completedAt,
+      evaluationScore: vendorInvestigations.evaluationScore,
+      evaluationResult: vendorInvestigations.evaluationResult,
+      investigationNotes: vendorInvestigations.investigationNotes,
+      createdAt: vendorInvestigations.createdAt,
+      updatedAt: vendorInvestigations.updatedAt,
+      
+      // Essential vendor fields
+      vendorName: vendors.vendorName,
+      vendorCode: vendors.vendorCode,
+      
+      // User names and emails instead of just IDs
+      requesterName: sql<string>`requester.name`.as("requesterName"),
+      requesterEmail: sql<string>`requester.email`.as("requesterEmail"),
+      qmManagerName: sql<string>`qm_manager.name`.as("qmManagerName"),
+      qmManagerEmail: sql<string>`qm_manager.email`.as("qmManagerEmail"),
+      
+      // File attachment status
+      hasAttachments: sql<boolean>`(
+        CASE 
+          WHEN EXISTS (
+            SELECT 1 FROM vendor_investigation_attachments via 
+            WHERE via.investigation_id = ${vendorInvestigations.id}
+          ) 
+          THEN true 
+          ELSE false 
+        END
+      )`.as("hasAttachments"),
+    })
+    .from(vendorInvestigations)
+    .leftJoin(
+      vendors,
+      eq(vendorInvestigations.vendorId, vendors.id)
+    )
+    .leftJoin(
+      sql`users AS requester`,
+      eq(vendorInvestigations.requesterId, sql`requester.id`)
+    )
+    .leftJoin(
+      sql`users AS qm_manager`, 
+      eq(vendorInvestigations.qmManagerId, sql`qm_manager.id`)
+    )
+})
\ No newline at end of file