import { pgTable, serial, integer, varchar, text, timestamp, uniqueIndex, pgView, boolean, jsonb, decimal } from "drizzle-orm/pg-core"; import { vendorTypes, vendors } from "./vendors"; import { projects } from "./projects"; import { sql, eq } from "drizzle-orm"; import { users } from "./users"; // --------------------------------------------------------------------------- // PQ Lists – 관리자가 생성하는 PQ 목록 정의 (GENERAL / PROJECT / NON_INSPECTION) // --------------------------------------------------------------------------- export const pqLists = pgTable("pq_lists", { id: serial("id").primaryKey(), // 목록 명칭 – "General PQ", "Project PQ – EPC-1234" 등 name: varchar("name", { length: 255 }).notNull(), // GENERAL | PROJECT | NON_INSPECTION type: varchar("type", { length: 20 }).notNull(), // 프로젝트 PQ의 경우 연결될 프로젝트 projectId: integer("project_id").references(() => projects.id), // 삭제 플래그 (실제 데이터는 보존) isDeleted: boolean("is_deleted").notNull().default(false), // 프로젝트 PQ 사용 기한 (선택) validTo: timestamp("valid_to"), // 생성자 createdBy: integer("created_by").references(() => users.id), // 수정자 updatedBy: integer("updated_by").references(() => users.id), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); export type PqList = typeof pqLists.$inferSelect; 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"), // (선택) "GENERAL", "Quality Management System" 등 큰 분류 groupName: varchar("group_name", { length: 255 }), subGroupName: varchar("sub_group_name", { length: 255 }), // 해당 항목이 속하는 PQ 목록 (General/Project/Non-Inspection) pqListId: integer("pq_list_id") .notNull() .references(() => pqLists.id, { onDelete: "cascade", onUpdate: "cascade", }), // 협력업체 입력 형식 – TEXT, FILE, EMAIL, PHONE, NUMBER 등 inputFormat: varchar("input_format", { length: 50 }) .notNull() .default("TEXT"), 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(), fileName: varchar("file_name", { length: 255 }).notNull(), originalFileName: varchar("original_file_name", { length: 255 }), 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(), }); 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(), }) export type PqCriterias = typeof pqCriterias.$inferSelect // 협력업체와 프로젝트 PQ 요청 연결 테이블 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" or "NON_INSPECTION" status: varchar("status", { length: 20 }).notNull().default("REQUESTED"), dueDate: timestamp("due_date"), agreements: jsonb("agreements").notNull().default({}), // ✅ 체크 항목들을 JSON으로 저장 // PQ 대상품목 - [{ itemCode: string, itemName: string }, ...] pqItems: jsonb("pq_items"), 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"), shiComment: text("shi_comment"), vendorReply: text("vendor_reply"), 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: vendorPQSubmissions.submittedAt, approved_at: vendorPQSubmissions.approvedAt }) .from(vendors) .innerJoin( vendorPQSubmissions, sql`${vendorPQSubmissions.vendorId} = ${vendors.id}` ) .innerJoin( projects, sql`${vendorPQSubmissions.projectId} = ${projects.id}` ) .leftJoin( vendorTypes, sql`${vendors.vendorTypeId} = ${vendorTypes.id}` ) .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", // 취소됨 "RESULT_SENT", // 실사결과발송 - 구매담당자가 Vendor측으로 실사결과를 발송한 상태 ], }) .notNull() .default("PLANNED"), // 실사 주소 investigationAddress: text("investigation_address"), // 실사 방법 investigationMethod: varchar("investigation_method", { length: 100, enum: [ "PURCHASE_SELF_EVAL", // 구매자체평가 "DOCUMENT_EVAL", // 서류평가 "PRODUCT_INSPECTION", // 제품검사평가 "SITE_VISIT_EVAL" // 방문실사평가 ], }), // 실사 일정 시작일 / 종료일 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"), // 구매 담당자 추가 의견 (실사 결과 발송 시 사용) purchaseComment: text("purchase_comment"), 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(), originalFileName: varchar("original_file_name", { length: 255 }), 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, 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, // PQ 정보 pqItems: vendorPQSubmissions.pqItems, // User names and emails instead of just IDs requesterName: sql`requester.name`.as("requesterName"), requesterEmail: sql`requester.email`.as("requesterEmail"), qmManagerName: sql`qm_manager.name`.as("qmManagerName"), qmManagerEmail: sql`qm_manager.email`.as("qmManagerEmail"), // File attachment status hasAttachments: sql`( 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`) ) .leftJoin( vendorPQSubmissions, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id) ) }) // 방문실사 요청 테이블 export const siteVisitRequests = pgTable("site_visit_requests", { id: serial("id").primaryKey(), // 어떤 실사(investigation)에 대한 방문실사 요청인지 investigationId: integer("investigation_id") .notNull() .references(() => vendorInvestigations.id, { onDelete: "cascade" }), // 요청자 requesterId: integer("requester_id") .references(() => users.id), // 실사 기간 (W/D 기준) inspectionDuration: decimal("inspection_duration", { precision: 4, scale: 1 }), // 실사 요청일 (시작일, 종료일) requestedStartDate: timestamp("requested_start_date"), requestedEndDate: timestamp("requested_end_date"), // SHI 실사참석 예정부문 (JSON으로 저장) shiAttendees: jsonb("shi_attendees").notNull().default({}), // 협력업체 요청정보 및 자료 (JSON으로 저장) vendorRequests: jsonb("vendor_requests").notNull().default({}), // 추가 요청사항 additionalRequests: text("additional_requests"), // 상태 status: varchar("status", { length: 20 }).notNull().default("REQUESTED"), // 메일 발송일 sentAt: timestamp("sent_at"), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); // 방문실사 요청 첨부파일 export const siteVisitRequestAttachments = pgTable("site_visit_request_attachments", { id: serial("id").primaryKey(), siteVisitRequestId: integer("site_visit_request_id") .notNull() .references(() => siteVisitRequests.id, { onDelete: "cascade" }), // 협력업체가 제출한 첨부파일인 경우 vendor_site_visit_info와 연결 vendorSiteVisitInfoId: integer("vendor_site_visit_info_id") .references(() => vendorSiteVisitInfo.id, { onDelete: "cascade" }), fileName: varchar("file_name", { length: 255 }).notNull(), originalFileName: varchar("original_file_name", { length: 255 }), filePath: varchar("file_path", { length: 1024 }).notNull(), fileSize: integer("file_size"), mimeType: varchar("mime_type", { length: 100 }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); // 협력업체 방문실사 정보 테이블 export const vendorSiteVisitInfo = pgTable("vendor_site_visit_info", { id: serial("id").primaryKey(), // 방문실사 요청과 연결 siteVisitRequestId: integer("site_visit_request_id") .notNull() .references(() => siteVisitRequests.id, { onDelete: "cascade" }), // 공장 정보 factoryName: varchar("factory_name", { length: 255 }).notNull(), factoryLocation: varchar("factory_location", { length: 255 }).notNull(), factoryAddress: text("factory_address").notNull(), // 담당자 정보 factoryPicName: varchar("factory_pic_name", { length: 255 }).notNull(), factoryPicPhone: varchar("factory_pic_phone", { length: 50 }).notNull(), factoryPicEmail: varchar("factory_pic_email", { length: 255 }).notNull(), // 방문 정보 factoryDirections: text("factory_directions"), accessProcedure: text("access_procedure"), // 첨부파일 여부 hasAttachments: boolean("has_attachments").notNull().default(false), // 기타 정보 otherInfo: text("other_info"), // 제출 정보 submittedAt: timestamp("submitted_at").defaultNow().notNull(), submittedBy: integer("submitted_by") .notNull() .references(() => users.id), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }); // 타입 정의 export type SiteVisitRequest = typeof siteVisitRequests.$inferSelect; export type NewSiteVisitRequest = typeof siteVisitRequests.$inferInsert; export type SiteVisitRequestAttachment = typeof siteVisitRequestAttachments.$inferSelect; export type NewSiteVisitRequestAttachment = typeof siteVisitRequestAttachments.$inferInsert; export type VendorSiteVisitInfo = typeof vendorSiteVisitInfo.$inferSelect; export type NewVendorSiteVisitInfo = typeof vendorSiteVisitInfo.$inferInsert;