diff options
Diffstat (limited to 'db/schema/gtc.ts')
| -rw-r--r-- | db/schema/gtc.ts | 598 |
1 files changed, 596 insertions, 2 deletions
diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts index 281b6af9..a217f681 100644 --- a/db/schema/gtc.ts +++ b/db/schema/gtc.ts @@ -8,11 +8,12 @@ import { pgEnum, boolean, index, - uniqueIndex, pgView + uniqueIndex, pgView, jsonb ,decimal, json } from "drizzle-orm/pg-core" import { relations ,sql, eq} from "drizzle-orm" import { projects } from "./projects" import { users } from "./users" +import { vendors } from "./vendors" // GTC 구분 enum export const gtcTypeEnum = pgEnum("gtc_type", ["standard", "project"]) @@ -28,6 +29,8 @@ import { users } from "./users" projectId: integer("project_id").references(() => projects.id, { onDelete: "cascade" }), + + title:varchar("title", { length: 255 }), // 리비전 번호 revision: integer("revision").notNull().default(0), @@ -118,6 +121,7 @@ import { users } from "./users" type: gtcDocuments.type, projectId: gtcDocuments.projectId, revision: gtcDocuments.revision, + title: gtcDocuments.title, fileName: gtcDocuments.fileName, filePath: gtcDocuments.filePath, fileSize: gtcDocuments.fileSize, @@ -275,4 +279,594 @@ import { users } from "./users" export type GtcDocumentView = typeof gtcDocumentsView.$inferSelect; -
\ No newline at end of file + // 협의 상태 enum +export const reviewStatusEnum = pgEnum("review_status", [ + "draft", // 초안 + "pending", // 협의 대기 + "reviewing", // 협의 중 + "approved", // 승인됨 + "rejected", // 거부됨 + "revised" // 수정됨 +]) + + + // 템플릿 파일 관리 테이블 +export const gtcTemplates = pgTable("gtc_templates", { + id: serial("id").primaryKey(), + + documentId: integer("document_id") + .references(() => gtcDocuments.id, { onDelete: "cascade" }) + .notNull(), + + name: varchar("name", { length: 255 }).notNull(), + description: text("description"), + version: varchar("version", { length: 50 }).default("1.0").notNull(), + + // 파일 정보 + originalFileName: varchar("original_file_name", { length: 255 }).notNull(), + filePath: varchar("file_path", { length: 500 }).notNull(), + fileSize: integer("file_size"), + + // PDFTron 변수 메타데이터 + variableMetadata: jsonb("variable_metadata"), + + isActive: boolean("is_active").default(true).notNull(), + isDefault: boolean("is_default").default(false).notNull(), + + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + createdById: integer("created_by_id") + .references(() => users.id, { onDelete: "set null" }) + .notNull(), + + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + updatedById: integer("updated_by_id") + .references(() => users.id, { onDelete: "set null" }), + +}, (table) => { + return { + nameIdx: index("gtc_templates_name_idx").on(table.name), + isDefaultIdx: index("gtc_templates_is_default_idx").on(table.isDefault), + } +}) + +// 표준(마스터) GTC 조항 테이블 +export const gtcClauses = pgTable("gtc_clauses", { + id: serial("id").primaryKey(), + + // GTC 문서 참조 + documentId: integer("document_id") + .references(() => gtcDocuments.id, { onDelete: "cascade" }) + .notNull(), + + // 계층구조 + parentId: integer("parent_id"), + + + // 채번 + itemNumber: varchar("item_number", { length: 50 }).notNull(), + + // 분류 (수기입력) + category: varchar("category", { length: 100 }), + + // 소제목 + subtitle: varchar("subtitle", { length: 500 }).notNull(), + + // 상세항목 + content: text("content"), + + // 순서 + sortOrder: decimal("sort_order", { precision: 10, scale: 2 }) + .default('0') + .notNull(), + + // 계층 정보 + depth: integer("depth").default(0).notNull(), + fullPath: varchar("full_path", { length: 200 }), + + // PDFTron 변수명들 + images: json("images"), // ✅ 이미지 JSON 배열 추가 + + + isActive: boolean("is_active").default(true).notNull(), + + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + createdById: integer("created_by_id") + .references(() => users.id, { onDelete: "set null" }) + .notNull(), + + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + updatedById: integer("updated_by_id") + .references(() => users.id, { onDelete: "set null" }), + + editReason: text("edit_reason"), + +}, (table) => { + return { + documentItemNumberIdx: uniqueIndex("gtc_clauses_document_item_number_idx") + .on(table.documentId, table.parentId, table.itemNumber), + documentIdx: index("gtc_clauses_document_idx").on(table.documentId), + parentIdx: index("gtc_clauses_parent_idx").on(table.parentId), + fullPathIdx: index("gtc_clauses_full_path_idx").on(table.fullPath), + } +}) + +// 벤더별 GTC 문서 테이블 +export const gtcVendorDocuments = pgTable("gtc_vendor_documents", { + id: serial("id").primaryKey(), + + // 표준 GTC 문서 참조 + baseDocumentId: integer("base_document_id") + .references(() => gtcDocuments.id, { onDelete: "cascade" }) + .notNull(), + + // 벤더 참조 + vendorId: integer("vendor_id") + .references(() => vendors.id, { onDelete: "cascade" }) + .notNull(), + + // 벤더별 문서 정보 + name: varchar("name", { length: 255 }).notNull(), + description: text("description"), + version: varchar("version", { length: 50 }).default("1.0").notNull(), + + // 협의 상태 + reviewStatus: reviewStatusEnum("review_status").default("draft").notNull(), + + // 협의 일정 + negotiationStartDate: timestamp("negotiation_start_date", { withTimezone: true }), + negotiationEndDate: timestamp("negotiation_end_date", { withTimezone: true }), + approvalDate: timestamp("approval_date", { withTimezone: true }), + + // 최종 파일 (협의 완료 후 생성된 파일) + finalFileName: varchar("final_file_name", { length: 255 }), + finalFilePath: varchar("final_file_path", { length: 500 }), + finalFileSize: integer("final_file_size"), + + isActive: boolean("is_active").default(true).notNull(), + + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + createdById: integer("created_by_id") + .references(() => users.id, { onDelete: "set null" }) + .notNull(), + + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + updatedById: integer("updated_by_id") + .references(() => users.id, { onDelete: "set null" }), + +}, (table) => { + return { + // 벤더별 기본문서 유니크 (벤더당 하나의 기본문서에 대해 하나의 협의문서) + baseDocumentVendorIdx: uniqueIndex("gtc_vendor_documents_base_vendor_idx") + .on(table.baseDocumentId, table.vendorId), + vendorIdx: index("gtc_vendor_documents_vendor_idx").on(table.vendorId), + baseDocumentIdx: index("gtc_vendor_documents_base_document_idx").on(table.baseDocumentId), + reviewStatusIdx: index("gtc_vendor_documents_review_status_idx").on(table.reviewStatus), + } +}) + +// 벤더별 GTC 조항 테이블 (표준 조항을 참조하되 내용 수정 가능) +export const gtcVendorClauses = pgTable("gtc_vendor_clauses", { + id: serial("id").primaryKey(), + + // 벤더 문서 참조 + vendorDocumentId: integer("vendor_document_id") + .references(() => gtcVendorDocuments.id, { onDelete: "cascade" }) + .notNull(), + + // 표준 조항 참조 (기준이 되는 마스터 조항) + baseClauseId: integer("base_clause_id") + .references(() => gtcClauses.id, { onDelete: "cascade" }) + .notNull(), + + // 계층구조 (벤더별 문서 내에서) + parentId: integer("parent_id"), + + // 벤더별로 수정된 내용 (null이면 표준 내용 사용) + modifiedItemNumber: varchar("modified_item_number", { length: 50 }), + modifiedCategory: varchar("modified_category", { length: 100 }), + modifiedSubtitle: varchar("modified_subtitle", { length: 500 }), + modifiedContent: text("modified_content"), + + // 순서 (벤더별 문서 내에서) + sortOrder: decimal("sort_order", { precision: 10, scale: 2 }) + .default('0') + .notNull(), + + // 계층 정보 + depth: integer("depth").default(0).notNull(), + fullPath: varchar("full_path", { length: 200 }), + + // 수정 여부 플래그들 + isNumberModified: boolean("is_number_modified").default(false).notNull(), + isCategoryModified: boolean("is_category_modified").default(false).notNull(), + isSubtitleModified: boolean("is_subtitle_modified").default(false).notNull(), + isContentModified: boolean("is_content_modified").default(false).notNull(), + + // 협의 관련 + reviewStatus: reviewStatusEnum("review_status").default("draft").notNull(), + negotiationNote: text("negotiation_note"), // 협의 과정 메모 + + // 활성/비활성 (특정 조항을 벤더와의 협의에서 제외할 수 있음) + isActive: boolean("is_active").default(true).notNull(), + isExcluded: boolean("is_excluded").default(false).notNull(), // 벤더 협의에서 제외 + + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + createdById: integer("created_by_id") + .references(() => users.id, { onDelete: "set null" }) + .notNull(), + + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + updatedById: integer("updated_by_id") + .references(() => users.id, { onDelete: "set null" }), + + editReason: text("edit_reason"), + +}, (table) => { + return { + // 벤더 문서별 기본 조항 유니크 + vendorDocumentBaseClauseIdx: uniqueIndex("gtc_vendor_clauses_vendor_base_idx") + .on(table.vendorDocumentId, table.baseClauseId), + vendorDocumentIdx: index("gtc_vendor_clauses_vendor_document_idx").on(table.vendorDocumentId), + baseClauseIdx: index("gtc_vendor_clauses_base_clause_idx").on(table.baseClauseId), + parentIdx: index("gtc_vendor_clauses_parent_idx").on(table.parentId), + reviewStatusIdx: index("gtc_vendor_clauses_review_status_idx").on(table.reviewStatus), + } +}) + +// 협의 이력 테이블 +export const gtcNegotiationHistory = pgTable("gtc_negotiation_history", { + id: serial("id").primaryKey(), + + // 벤더 조항 참조 + vendorClauseId: integer("vendor_clause_id") + .references(() => gtcVendorClauses.id, { onDelete: "cascade" }) + .notNull(), + + // 협의 정보 + action: varchar("action", { length: 50 }).notNull(), // "created", "modified", "approved", "rejected", "commented" + previousStatus: reviewStatusEnum("previous_status"), + newStatus: reviewStatusEnum("new_status"), + + // 변경 내용 + changedFields: jsonb("changed_fields"), // 어떤 필드가 변경되었는지 + comment: text("comment"), + + // 첨부파일 + attachments: jsonb("attachments"), + + // 협의 당사자 + actorType: varchar("actor_type", { length: 20 }).notNull(), // "internal", "vendor" + actorId: integer("actor_id").references(() => users.id, { onDelete: "set null" }), + actorName: varchar("actor_name", { length: 100 }), // 외부 벤더인 경우 + actorEmail: varchar("actor_email", { length: 255 }), + + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + +}, (table) => { + return { + vendorClauseIdx: index("gtc_negotiation_history_vendor_clause_idx").on(table.vendorClauseId), + actionIdx: index("gtc_negotiation_history_action_idx").on(table.action), + createdAtIdx: index("gtc_negotiation_history_created_at_idx").on(table.createdAt), + } +}) + +// 관계 정의 +export const vendorsRelations = relations(vendors, ({ many, one }) => ({ + vendorDocuments: many(gtcVendorDocuments) + +})) + +export const gtcTemplatesRelations = relations(gtcTemplates, ({ one }) => ({ + createdBy: one(users, { + fields: [gtcTemplates.createdById], + references: [users.id], + }), + updatedBy: one(users, { + fields: [gtcTemplates.updatedById], + references: [users.id], + }), +})) + +export const gtcClausesRelations = relations(gtcClauses, ({ one, many }) => ({ + document: one(gtcDocuments, { + fields: [gtcClauses.documentId], + references: [gtcDocuments.id], + }), + parent: one(gtcClauses, { + fields: [gtcClauses.parentId], + references: [gtcClauses.id], + relationName: "clauseHierarchy", + }), + children: many(gtcClauses, { + relationName: "clauseHierarchy", + }), + vendorClauses: many(gtcVendorClauses), + createdBy: one(users, { + fields: [gtcClauses.createdById], + references: [users.id], + }), + updatedBy: one(users, { + fields: [gtcClauses.updatedById], + references: [users.id], + }), +})) + +export const gtcVendorDocumentsRelations = relations(gtcVendorDocuments, ({ one, many }) => ({ + baseDocument: one(gtcDocuments, { + fields: [gtcVendorDocuments.baseDocumentId], + references: [gtcDocuments.id], + }), + vendor: one(vendors, { + fields: [gtcVendorDocuments.vendorId], + references: [vendors.id], + }), + vendorClauses: many(gtcVendorClauses), + createdBy: one(users, { + fields: [gtcVendorDocuments.createdById], + references: [users.id], + }), + updatedBy: one(users, { + fields: [gtcVendorDocuments.updatedById], + references: [users.id], + }), +})) + +export const gtcVendorClausesRelations = relations(gtcVendorClauses, ({ one, many }) => ({ + vendorDocument: one(gtcVendorDocuments, { + fields: [gtcVendorClauses.vendorDocumentId], + references: [gtcVendorDocuments.id], + }), + baseClause: one(gtcClauses, { + fields: [gtcVendorClauses.baseClauseId], + references: [gtcClauses.id], + }), + parent: one(gtcVendorClauses, { + fields: [gtcVendorClauses.parentId], + references: [gtcVendorClauses.id], + relationName: "vendorClauseHierarchy", + }), + children: many(gtcVendorClauses, { + relationName: "vendorClauseHierarchy", + }), + negotiationHistory: many(gtcNegotiationHistory), + createdBy: one(users, { + fields: [gtcVendorClauses.createdById], + references: [users.id], + }), + updatedBy: one(users, { + fields: [gtcVendorClauses.updatedById], + references: [users.id], + }), +})) + +export const gtcNegotiationHistoryRelations = relations(gtcNegotiationHistory, ({ one }) => ({ + vendorClause: one(gtcVendorClauses, { + fields: [gtcNegotiationHistory.vendorClauseId], + references: [gtcVendorClauses.id], + }), + actor: one(users, { + fields: [gtcNegotiationHistory.actorId], + references: [users.id], + }), +})) + +// 타입 정의 +export type Vendor = typeof vendors.$inferSelect +export type NewVendor = typeof vendors.$inferInsert + +export type GtcTemplate = typeof gtcTemplates.$inferSelect +export type NewGtcTemplate = typeof gtcTemplates.$inferInsert + +export type GtcClause = typeof gtcClauses.$inferSelect +export type NewGtcClause = typeof gtcClauses.$inferInsert + +export type GtcVendorDocument = typeof gtcVendorDocuments.$inferSelect +export type NewGtcVendorDocument = typeof gtcVendorDocuments.$inferInsert + +export type GtcVendorClause = typeof gtcVendorClauses.$inferSelect +export type NewGtcVendorClause = typeof gtcVendorClauses.$inferInsert + +export type GtcNegotiationHistory = typeof gtcNegotiationHistory.$inferSelect +export type NewGtcNegotiationHistory = typeof gtcNegotiationHistory.$inferInsert + +// 표준 GTC 조항 상세보기용 뷰 (계층구조 포함) +export const gtcClausesTreeView = pgView('gtc_clauses_tree_view').as((qb) => + qb + .select({ + // GTC 조항 기본 필드 + id: gtcClauses.id, + documentId: gtcClauses.documentId, + parentId: gtcClauses.parentId, + itemNumber: gtcClauses.itemNumber, + category: gtcClauses.category, + subtitle: gtcClauses.subtitle, + content: gtcClauses.content, + sortOrder: gtcClauses.sortOrder, + depth: gtcClauses.depth, + fullPath: gtcClauses.fullPath, + images: gtcClauses.images, + + isActive: gtcClauses.isActive, + createdAt: gtcClauses.createdAt, + createdById: gtcClauses.createdById, + updatedAt: gtcClauses.updatedAt, + updatedById: gtcClauses.updatedById, + editReason: gtcClauses.editReason, + + // 문서 정보 + documentType: gtcDocuments.type, + documentFileName: gtcDocuments.fileName, + documentRevision: gtcDocuments.revision, + projectId: gtcDocuments.projectId, + + // 작성자/수정자 정보 + createdByName: sql<string>`created_by_user.name`.as('created_by_name'), + createdByEmail: sql<string>`created_by_user.email`.as('created_by_email'), + updatedByName: sql<string>`updated_by_user.name`.as('updated_by_name'), + updatedByEmail: sql<string>`updated_by_user.email`.as('updated_by_email'), + + // 부모 정보 + parentItemNumber: sql<string>`parent_clause.item_number`.as('parent_item_number'), + parentSubtitle: sql<string>`parent_clause.subtitle`.as('parent_subtitle'), + + // 자식 수 + childrenCount: sql<number>` + ( + SELECT count(*) + FROM gtc_clauses children + WHERE children.parent_id = ${gtcClauses.id} + AND children.is_active = true + ) + `.as('children_count'), + + // 같은 부모 아래 형제 수 + siblingsCount: sql<number>` + ( + SELECT count(*) + FROM gtc_clauses siblings + WHERE siblings.parent_id = ${gtcClauses.parentId} + AND siblings.is_active = true + ) + `.as('siblings_count'), + + // 수정 이력이 있는지 여부 + hasEditHistory: sql<boolean>` + ${gtcClauses.createdById} != ${gtcClauses.updatedById} OR + ${gtcClauses.createdAt} != ${gtcClauses.updatedAt} + `.as('has_edit_history'), + }) + .from(gtcClauses) + .leftJoin(gtcDocuments, sql`${gtcClauses.documentId} = ${gtcDocuments.id}`) + .leftJoin( + sql`users created_by_user`, + sql`${gtcClauses.createdById} = created_by_user.id` + ) + .leftJoin( + sql`users updated_by_user`, + sql`${gtcClauses.updatedById} = updated_by_user.id` + ) + .leftJoin( + sql`gtc_clauses parent_clause`, + sql`${gtcClauses.parentId} = parent_clause.id` + ) +) + +export type GtcClauseTreeView = typeof gtcClausesTreeView.$inferSelect + +// 벤더별 조항 상세보기용 뷰 +export const gtcVendorClausesView = pgView('gtc_vendor_clauses_view').as((qb) => + qb + .select({ + // 벤더 조항 정보 + id: gtcVendorClauses.id, + vendorDocumentId: gtcVendorClauses.vendorDocumentId, + baseClauseId: gtcVendorClauses.baseClauseId, + parentId: gtcVendorClauses.parentId, + + // 실제 사용될 값들 (수정된 값이 있으면 수정값, 없으면 표준값) + effectiveItemNumber: sql<string>` + COALESCE(${gtcVendorClauses.modifiedItemNumber}, ${gtcClauses.itemNumber}) + `.as('effective_item_number'), + + effectiveCategory: sql<string>` + COALESCE(${gtcVendorClauses.modifiedCategory}, ${gtcClauses.category}) + `.as('effective_category'), + + effectiveSubtitle: sql<string>` + COALESCE(${gtcVendorClauses.modifiedSubtitle}, ${gtcClauses.subtitle}) + `.as('effective_subtitle'), + + effectiveContent: sql<string>` + COALESCE(${gtcVendorClauses.modifiedContent}, ${gtcClauses.content}) + `.as('effective_content'), + + // 수정 정보 + isNumberModified: gtcVendorClauses.isNumberModified, + isCategoryModified: gtcVendorClauses.isCategoryModified, + isSubtitleModified: gtcVendorClauses.isSubtitleModified, + isContentModified: gtcVendorClauses.isContentModified, + + // 표준 조항 정보 + baseItemNumber: gtcClauses.itemNumber, + baseCategory: gtcClauses.category, + baseSubtitle: gtcClauses.subtitle, + baseContent: gtcClauses.content, + + // 벤더 정보 + vendorId: gtcVendorDocuments.vendorId, + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName, + + // 문서 정보 + baseDocumentId: gtcVendorDocuments.baseDocumentId, + documentType: gtcDocuments.type, + documentFileName: gtcDocuments.fileName, + + // 협의 상태 + reviewStatus: gtcVendorClauses.reviewStatus, + negotiationNote: gtcVendorClauses.negotiationNote, + isExcluded: gtcVendorClauses.isExcluded, + + // 계층 정보 + sortOrder: gtcVendorClauses.sortOrder, + depth: gtcVendorClauses.depth, + fullPath: gtcVendorClauses.fullPath, + + // 수정 여부 (하나라도 수정되었는지) + hasModifications: sql<boolean>` + ${gtcVendorClauses.isNumberModified} OR + ${gtcVendorClauses.isCategoryModified} OR + ${gtcVendorClauses.isSubtitleModified} OR + ${gtcVendorClauses.isContentModified} + `.as('has_modifications'), + + + createdAt: gtcVendorClauses.createdAt, + updatedAt: gtcVendorClauses.updatedAt, + }) + .from(gtcVendorClauses) + .leftJoin(gtcClauses, sql`${gtcVendorClauses.baseClauseId} = ${gtcClauses.id}`) + .leftJoin(gtcVendorDocuments, sql`${gtcVendorClauses.vendorDocumentId} = ${gtcVendorDocuments.id}`) + .leftJoin(vendors, sql`${gtcVendorDocuments.vendorId} = ${vendors.id}`) + .leftJoin(gtcDocuments, sql`${gtcVendorDocuments.baseDocumentId} = ${gtcDocuments.id}`) +) + +export type GtcVendorClauseView = typeof gtcVendorClausesView.$inferSelect + +// 벤더별 PDFTron 미리보기용 데이터 타입 +export type GtcVendorClauseForPreview = { + id: number + itemNumber: string + subtitle: string + content: string | null + depth: number + fullPath: string | null + sortOrder: number + parentId: number | null + vendorCode: string + hasModifications: boolean + children?: GtcVendorClauseForPreview[] + variables: { + number: string + subtitle: string + content: string| null + } +}
\ No newline at end of file |
