diff options
Diffstat (limited to 'db/schema/pq.ts')
| -rw-r--r-- | db/schema/pq.ts | 409 |
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 |