(박서영) 설계 document Numbering Rule 개발-최겸 업로드

2 files changed, 175 insertions, 0 deletions

diff --git a/db/schema/docu-list-rule.ts b/db/schema/docu-list-rule.ts
new file mode 100644
index 00000000..67d34d75
--- /dev/null
+++ b/db/schema/docu-list-rule.ts
@@ -0,0 +1,174 @@
+import { pgTable, serial, varchar, text, timestamp, boolean, integer } from "drizzle-orm/pg-core"
+import { relations } from "drizzle-orm"
+
+// ===== Code Groups 테이블 =====
+export const codeGroups = pgTable("code_groups", {
+  id: serial("id").primaryKey(),
+  groupId: varchar("group_id", { length: 50 }).notNull().unique(), // GROUP ID (Code_번호 형태)
+  description: varchar("description", { length: 100 }).notNull(), // Description (예: PROJECT NO, Phase)
+  codeFormat: varchar("code_format", { length: 50 }), // Code Format (예: AANNN)
+  expressions: text("expressions"), // Expressions (자동 생성된 정규식)
+  controlType: varchar("control_type", { length: 20 }).notNull(), // Control Type (Textbox, Combobox)
+  isActive: boolean("is_active").default(true),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== Document Classes 테이블 =====
+export const documentClasses = pgTable("document_classes", {
+  id: serial("id").primaryKey(),
+  code: varchar("code", { length: 50 }).notNull().unique(), // CODE (자동 생성)
+  value: varchar("value", { length: 100 }), // 사용자가 선택할 수 있는 값 (선택사항)
+  description: varchar("description", { length: 200 }).notNull(), // 값의 의미 설명
+  codeGroupId: integer("code_group_id").references(() => codeGroups.id), // 참조하는 Code Group ID
+  isActive: boolean("is_active").default(true),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== Document Class Options 테이블 =====
+export const documentClassOptions = pgTable("document_class_options_new", {
+  id: serial("id").primaryKey(),
+  documentClassId: integer("document_class_id").notNull().references(() => documentClasses.id),
+  optionValue: varchar("option_value", { length: 100 }).notNull(), // 하위 옵션 값 (예: "General", "Technical")
+  optionCode: varchar("option_code", { length: 50 }), // 하위 옵션 코드 (선택사항)
+  sortOrder: integer("sort_order").default(0), // 정렬 순서
+  isActive: boolean("is_active").default(true),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== ComboBox Settings 테이블 =====
+export const comboBoxSettings = pgTable("combo_box_settings", {
+  id: serial("id").primaryKey(),
+  codeGroupId: integer("code_group_id").notNull().references(() => codeGroups.id), // Code Group과 연결
+  code: varchar("code", { length: 50 }).notNull(), // CODE (예: 100, 201, 202)
+  description: varchar("description", { length: 200 }).notNull(), // Description (예: General, Feed Gas Reveive)
+  remark: text("remark"), // Remark (비고)
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== ComboBox Options 테이블 =====
+export const comboBoxOptions = pgTable("combo_box_options", {
+  id: serial("id").primaryKey(),
+  comboBoxSettingId: integer("combo_box_setting_id").notNull().references(() => comboBoxSettings.id),
+  code: varchar("code", { length: 50 }).notNull(), // CODE (예: 100, 201, 202)
+  description: varchar("description", { length: 200 }).notNull(), // Description (예: General, Feed Gas Reveive)
+  remark: text("remark"), // Remark (비고)
+  sortOrder: integer("sort_order").default(0), // 정렬 순서
+  isActive: boolean("is_active").default(true),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== Document Number Types 테이블 =====
+export const documentNumberTypes = pgTable("document_number_types", {
+  id: serial("id").primaryKey(),
+  name: varchar("name", { length: 100 }).notNull().unique(), // Number Type 이름 (예: Project No, SHI No, Vendor No)
+  description: varchar("description", { length: 200 }), // 설명
+  isActive: boolean("is_active").default(true),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== Document Number Type Configs 테이블 =====
+export const documentNumberTypeConfigs = pgTable("document_number_type_configs", {
+  id: serial("id").primaryKey(),
+  documentNumberTypeId: integer("document_number_type_id").notNull().references(() => documentNumberTypes.id),
+  codeGroupId: integer("code_group_id").references(() => codeGroups.id), // Code Group 또는 Document Class 중 하나만 선택
+  documentClassId: integer("document_class_id").references(() => documentClasses.id), // Code Group 또는 Document Class 중 하나만 선택
+  sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6)
+  description: varchar("description", { length: 200 }), // Description (예: [001] PROJECT NO)
+  remark: text("remark"), // Remark (비고)
+  isActive: boolean("is_active").default(true),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+// ===== 관계 정의 =====
+
+// Code Groups 관계
+export const codeGroupsRelations = relations(codeGroups, ({ many, one }) => ({
+  documentClasses: many(documentClasses), // Code Group에 속한 Document Classes
+  comboBoxSettings: many(comboBoxSettings), // Code Group에 속한 ComboBox Settings
+  documentNumberTypeConfigs: many(documentNumberTypeConfigs), // Code Group을 참조하는 Number Type Configs
+}))
+
+// Document Classes 관계
+export const documentClassesRelations = relations(documentClasses, ({ many, one }) => ({
+  documentClassOptions: many(documentClassOptions), // Document Class 하위 옵션들
+  codeGroup: one(codeGroups, {
+    fields: [documentClasses.codeGroupId],
+    references: [codeGroups.id],
+  }),
+  documentNumberTypeConfigs: many(documentNumberTypeConfigs), // Document Class를 참조하는 Number Type Configs
+}))
+
+// Document Class Options 관계
+export const documentClassOptionsRelations = relations(documentClassOptions, ({ one }) => ({
+  documentClass: one(documentClasses, {
+    fields: [documentClassOptions.documentClassId],
+    references: [documentClasses.id],
+  }),
+}))
+
+// ComboBox Settings 관계
+export const comboBoxSettingsRelations = relations(comboBoxSettings, ({ one, many }) => ({
+  codeGroup: one(codeGroups, {
+    fields: [comboBoxSettings.codeGroupId],
+    references: [codeGroups.id],
+  }),
+  comboBoxOptions: many(comboBoxOptions), // ComboBox Setting 하위 옵션들
+}))
+
+// ComboBox Options 관계
+export const comboBoxOptionsRelations = relations(comboBoxOptions, ({ one }) => ({
+  comboBoxSetting: one(comboBoxSettings, {
+    fields: [comboBoxOptions.comboBoxSettingId],
+    references: [comboBoxSettings.id],
+  }),
+}))
+
+// Document Number Types 관계
+export const documentNumberTypesRelations = relations(documentNumberTypes, ({ many }) => ({
+  configs: many(documentNumberTypeConfigs), // Number Type의 설정들
+}))
+
+// Document Number Type Configs 관계
+export const documentNumberTypeConfigsRelations = relations(documentNumberTypeConfigs, ({ one }) => ({
+  documentNumberType: one(documentNumberTypes, {
+    fields: [documentNumberTypeConfigs.documentNumberTypeId],
+    references: [documentNumberTypes.id],
+  }),
+  codeGroup: one(codeGroups, {
+    fields: [documentNumberTypeConfigs.codeGroupId],
+    references: [codeGroups.id],
+  }),
+  documentClass: one(documentClasses, {
+    fields: [documentNumberTypeConfigs.documentClassId],
+    references: [documentClasses.id],
+  }),
+}))
+
+// ===== 타입 정의 =====
+export type CodeGroup = typeof codeGroups.$inferSelect
+export type NewCodeGroup = typeof codeGroups.$inferInsert
+
+export type DocumentClass = typeof documentClasses.$inferSelect
+export type NewDocumentClass = typeof documentClasses.$inferInsert
+
+export type DocumentClassOption = typeof documentClassOptions.$inferSelect
+export type NewDocumentClassOption = typeof documentClassOptions.$inferInsert
+
+export type ComboBoxSetting = typeof comboBoxSettings.$inferSelect
+export type NewComboBoxSetting = typeof comboBoxSettings.$inferInsert
+
+export type ComboBoxOption = typeof comboBoxOptions.$inferSelect
+export type NewComboBoxOption = typeof comboBoxOptions.$inferInsert
+
+export type DocumentNumberType = typeof documentNumberTypes.$inferSelect
+export type NewDocumentNumberType = typeof documentNumberTypes.$inferInsert
+
+export type DocumentNumberTypeConfig = typeof documentNumberTypeConfigs.$inferSelect
+export type NewDocumentNumberTypeConfig = typeof documentNumberTypeConfigs.$inferInsert 
\ No newline at end of file
diff --git a/db/schema/index.ts b/db/schema/index.ts
index 5c6aad41..c48f0a8b 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -30,6 +30,7 @@ export * from './history';
 export * from './notification';
 export * from './templates';
 export * from './gtc';
+export * from './docu-list-rule';
 
 // 부서별 도메인 할당 관리
 export * from './departmentDomainAssignments';