1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
import { pgTable, serial, varchar, text, timestamp, boolean, integer, unique } 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 }).unique(), // 사용자가 선택할 수 있는 값 (고유해야 함)
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),
description: varchar("description", { length: 100 }).notNull(), // 하위 옵션 설명 (예: "General", "Technical")
optionCode: varchar("option_code", { length: 50 }), // 하위 옵션 코드 (선택사항)
isActive: boolean("is_active").default(true),
createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
return {
// 같은 Document Class 내에서 optionCode는 유니크해야 함
uniqueDocumentClassOption: unique("unique_document_class_option").on(
table.documentClassId,
table.optionCode
),
}
})
// ===== 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(),
})
// ===== 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 참조
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 }) => ({
codeGroup: one(codeGroups, {
fields: [comboBoxSettings.codeGroupId],
references: [codeGroups.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],
}),
}))
// ===== 타입 정의 =====
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 DocumentNumberType = typeof documentNumberTypes.$inferSelect
export type NewDocumentNumberType = typeof documentNumberTypes.$inferInsert
export type DocumentNumberTypeConfig = typeof documentNumberTypeConfigs.$inferSelect
export type NewDocumentNumberTypeConfig = typeof documentNumberTypeConfigs.$inferInsert
|
