diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/index.ts | 12 | ||||
| -rw-r--r-- | db/schema/pq.ts | 103 | ||||
| -rw-r--r-- | db/schema/vendorData.ts | 2 | ||||
| -rw-r--r-- | db/schema/vendorDocu.ts | 161 | ||||
| -rw-r--r-- | db/schema/vendors.ts | 250 |
5 files changed, 420 insertions, 108 deletions
diff --git a/db/schema/index.ts b/db/schema/index.ts new file mode 100644 index 00000000..714f058b --- /dev/null +++ b/db/schema/index.ts @@ -0,0 +1,12 @@ +export * from './companies'; +export * from './contract'; +export * from './items'; +export * from './pq'; +export * from './projects'; +export * from './rfq'; +export * from './users'; + +export * from './vendorData'; +export * from './vendorDocu'; +export * from './vendors'; +export * from './tasks'; diff --git a/db/schema/pq.ts b/db/schema/pq.ts index 59ec8f07..76d15d5d 100644 --- a/db/schema/pq.ts +++ b/db/schema/pq.ts @@ -3,6 +3,7 @@ import { timestamp, uniqueIndex } from "drizzle-orm/pg-core"; import { vendors } from "./vendors"; +import { projects } from "./projects"; export const pqCriterias = pgTable("pq_criterias", { id: serial("id").primaryKey(), @@ -17,21 +18,34 @@ export const pqCriterias = pgTable("pq_criterias", { updatedAt: timestamp("updated_at").defaultNow().notNull(), }); -export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", { +export const pqCriteriasExtension = pgTable("pq_criterias_extension", { id: serial("id").primaryKey(), - vendorId: integer("vendor_id") - .notNull() - .references(() => vendors.id, { onDelete: "cascade" }), - - criteriaId: integer("criteria_id") - .notNull() - .references(() => pqCriterias.id, { onDelete: "cascade" }), - - answer: text("answer"), - + + // 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(), @@ -65,3 +79,68 @@ export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", { 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() + .references(() => vendors.id, { + onDelete: "cascade", + onUpdate: "cascade", + }), + + // projects 테이블 FK + projectId: integer("project_id") + .notNull() + .references(() => projects.id, { + 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(), +}); + +// 기존 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(), +}); diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts index 92a92c8e..03248fb8 100644 --- a/db/schema/vendorData.ts +++ b/db/schema/vendorData.ts @@ -28,7 +28,7 @@ export const forms = pgTable("forms", { } }) -export const rfqAttachments = pgTable("form_templates", { +export const formTemplates = pgTable("form_templates", { id: serial("id").primaryKey(), formId: integer("form_id").references(() => forms.id), fileName: varchar("file_name", { length: 255 }).notNull(), diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index 2e6ba5a1..ce498b94 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -1,5 +1,5 @@ -import { pgTable, integer, varchar, timestamp, date ,pgView,uniqueIndex ,jsonb} from "drizzle-orm/pg-core" -import { eq , sql} from "drizzle-orm"; +import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb } from "drizzle-orm/pg-core" +import { eq, sql } from "drizzle-orm"; import { projects } from "./projects"; import { vendors } from "./vendors"; import { contracts } from "./contract"; @@ -76,94 +76,75 @@ export const issueStages = pgTable( } ); - export const revisions = pgTable( - "revisions", - { - id: integer("id").primaryKey().generatedAlwaysAsIdentity(), - issueStageId: integer("issue_stage_id").notNull(), - revision: varchar("revision", { length: 50 }).notNull(), - // 새로운 필드: 업로더 타입 (업체 또는 고객사) - uploaderType: varchar("uploader_type", { length: 20 }).notNull().default("vendor"), - // 선택적: 업로더 ID 또는 이름 - uploaderId: integer("uploader_id"), - uploaderName: varchar("uploader_name", { length: 100 }), - // 선택적: 추가 메타데이터 - comment: varchar("comment", { length: 500 }), - status: varchar("status", { length: 50 }), - approvedDate: date("approved_date"), - createdAt: timestamp("created_at").defaultNow().notNull(), - updatedAt: timestamp("updated_at").defaultNow().notNull(), - }, - (table) => { - return { - // "issue_stage_id + revision" 조합을 유니크로 묶음 (유지) - uniqueStageRev: uniqueIndex("unique_stage_rev").on( - table.issueStageId, - table.revision - ), - } - } - ) - export const documentAttachments = pgTable( - "document_attachments", - { - id: integer("id").primaryKey().generatedAlwaysAsIdentity(), - revisionId: integer("revision_id") - .notNull() - .references(() => revisions.id, { onDelete: "cascade" }), - 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(), +export const revisions = pgTable( + "revisions", + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + issueStageId: integer("issue_stage_id").notNull(), + revision: varchar("revision", { length: 50 }).notNull(), + // 새로운 필드: 업로더 타입 (업체 또는 고객사) + uploaderType: varchar("uploader_type", { length: 20 }).notNull().default("vendor"), + // 선택적: 업로더 ID 또는 이름 + uploaderId: integer("uploader_id"), + uploaderName: varchar("uploader_name", { length: 100 }), + // 선택적: 추가 메타데이터 + comment: varchar("comment", { length: 500 }), + status: varchar("status", { length: 50 }), + approvedDate: date("approved_date"), + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + }, + (table) => { + return { + // "issue_stage_id + revision" 조합을 유니크로 묶음 (유지) + uniqueStageRev: uniqueIndex("unique_stage_rev").on( + table.issueStageId, + table.revision + ), } - ) - + } +) +export const documentAttachments = pgTable( + "document_attachments", + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + revisionId: integer("revision_id") + .notNull() + .references(() => revisions.id, { onDelete: "cascade" }), + 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(), + } +) - // export const vendorDocumentsView = pgTable("vendor_documents", { - // vendorId: integer("vendor_id").notNull(), - // id: integer("id").notNull(), - // docNumber: varchar("doc_number", { length: 100 }).notNull(), - // title: varchar("title", { length: 255 }).notNull(), - - // // 새로 추가된 컬럼들 (contractId, contractNo, contractName, status) - // contractId: integer("contract_id").notNull(), - // contractNo: varchar("contract_no", { length: 100 }).notNull(), - // contractName: varchar("contract_name", { length: 255 }).notNull(), - // status: varchar("status", { length: 50 }).notNull(), - - // createdAt: timestamp("created_at", { withTimezone: true }), - // updatedAt: timestamp("updated_at", { withTimezone: true }), - - // }) +export const vendorDocumentsView = pgView("vendor_documents_view", { + // Match the columns in your SELECT statement + id: integer("id").notNull(), + docNumber: varchar("doc_number", { length: 100 }).notNull(), + title: varchar("title", { length: 255 }).notNull(), + status: varchar("status", { length: 50 }).notNull(), + issuedDate: date("issued_date"), + contractId: integer("contract_id").notNull(), - export const vendorDocumentsView = pgView("vendor_documents_view", { - // Match the columns in your SELECT statement - id: integer("id").notNull(), - docNumber: varchar("doc_number", { length: 100 }).notNull(), - title: varchar("title", { length: 255 }).notNull(), - status: varchar("status", { length: 50 }).notNull(), - issuedDate: date("issued_date"), - - contractId: integer("contract_id").notNull(), - - latestStageId: integer("latest_stage_id"), // possibly can be null - latestStageName: varchar("latest_stage_name", { length: 100 }), - latestStagePlanDate: date("latest_stage_plan_date"), - latestStageActualDate: date("latest_stage_actual_date"), - - latestRevisionId: integer("latest_revision_id"), - latestRevision: varchar("latest_revision", { length: 50 }), - latestRevisionUploaderType: varchar("latest_revision_uploader_type", { length: 20 }), - latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }), - - attachmentCount: integer("attachment_count"), - - createdAt: timestamp("created_at", { withTimezone: true }).notNull(), - updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), - }).as(sql` + latestStageId: integer("latest_stage_id"), // possibly can be null + latestStageName: varchar("latest_stage_name", { length: 100 }), + latestStagePlanDate: date("latest_stage_plan_date"), + latestStageActualDate: date("latest_stage_actual_date"), + + latestRevisionId: integer("latest_revision_id"), + latestRevision: varchar("latest_revision", { length: 50 }), + latestRevisionUploaderType: varchar("latest_revision_uploader_type", { length: 20 }), + latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }), + + attachmentCount: integer("attachment_count"), + + createdAt: timestamp("created_at", { withTimezone: true }).notNull(), + updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), +}).as(sql` SELECT d.id, d.doc_number, @@ -232,8 +213,6 @@ export const issueStages = pgTable( JOIN contracts c ON d.contract_id = c.id `); - - // 문서 + 스테이지 리스트 뷰 export const documentStagesView = pgView("document_stages_view", { documentId: integer("document_id").notNull(), @@ -279,7 +258,5 @@ export const documentStagesView = pgView("document_stages_view", { FROM documents d `); - - export type VendorDocumentsView = typeof vendorDocumentsView.$inferSelect - export type DocumentStagesView = typeof documentStagesView.$inferSelect -
\ No newline at end of file +export type VendorDocumentsView = typeof vendorDocumentsView.$inferSelect +export type DocumentStagesView = typeof documentStagesView.$inferSelect 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 |