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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
|
import { pgTable, serial, varchar, text, timestamp, boolean, integer, unique } from "drizzle-orm/pg-core"
import { relations } from "drizzle-orm"
import { projects } from "./projects"
// ===== Code Groups 테이블 =====
export const codeGroups = pgTable("code_groups", {
id: serial("id").primaryKey(),
projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
groupId: varchar("group_id", { length: 50 }).notNull(), // 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(),
}, (table) => {
return {
// 같은 프로젝트 내에서 groupId는 유니크해야 함
uniqueProjectGroupId: unique("unique_project_group_id").on(
table.projectId,
table.groupId
),
}
})
// ===== Document Classes 테이블 =====
export const documentClasses = pgTable("document_classes", {
id: serial("id").primaryKey(),
projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
code: varchar("code", { length: 50 }).notNull(), // 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(),
}, (table) => {
return {
// 같은 프로젝트 내에서 code는 유니크해야 함
uniqueProjectCode: unique("unique_project_code").on(
table.projectId,
table.code
),
// 같은 프로젝트 내에서 value는 유니크해야 함 (null 제외)
uniqueProjectValue: unique("unique_project_value").on(
table.projectId,
table.value
),
}
})
// ===== 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 }), // 하위 옵션 코드 (선택사항)
sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6)
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
),
// 같은 documentClassId 내에서 sdq는 유니크해야 함
uniqueDocumentClassSdq: unique("unique_document_class_sdq").on(
table.documentClassId,
table.sdq
),
}
})
// ===== 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 (비고)
sdq: integer("sdq").notNull(), // 순서 번호 (1, 2, 3, 4, 5, 6)
createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
return {
// 같은 codeGroupId 내에서 code는 유니크해야 함
uniqueCodeGroupCode: unique("unique_code_group_code").on(
table.codeGroupId,
table.code
),
// 같은 codeGroupId 내에서 sdq는 유니크해야 함
uniqueCodeGroupSdq: unique("unique_code_group_sdq").on(
table.codeGroupId,
table.sdq
),
}
})
// ===== Document Number Types 테이블 =====
export const documentNumberTypes = pgTable("document_number_types", {
id: serial("id").primaryKey(),
projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
name: varchar("name", { length: 100 }).notNull(), // 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(),
}, (table) => {
return {
// 같은 프로젝트 내에서 name은 유니크해야 함
uniqueProjectName: unique("unique_project_name").on(
table.projectId,
table.name
),
}
})
// ===== 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, { onDelete: "cascade" }),
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(),
}, (table) => {
return {
// 같은 documentNumberTypeId 내에서 sdq는 유니크해야 함
uniqueDocumentNumberTypeSdq: unique("unique_document_number_type_sdq").on(
table.documentNumberTypeId,
table.sdq
),
}
})
// ===== 관계 정의 =====
// Projects 관계
export const projectsRelations = relations(projects, ({ many }) => ({
codeGroups: many(codeGroups),
documentClasses: many(documentClasses),
documentNumberTypes: many(documentNumberTypes),
}))
// Code Groups 관계
export const codeGroupsRelations = relations(codeGroups, ({ many, one }) => ({
project: one(projects, {
fields: [codeGroups.projectId],
references: [projects.id],
}),
comboBoxSettings: many(comboBoxSettings), // Code Group에 속한 ComboBox Settings
documentNumberTypeConfigs: many(documentNumberTypeConfigs), // Code Group을 참조하는 Number Type Configs
}))
// Document Classes 관계
export const documentClassesRelations = relations(documentClasses, ({ many, one }) => ({
project: one(projects, {
fields: [documentClasses.projectId],
references: [projects.id],
}),
documentClassOptions: many(documentClassOptions), // Document Class 하위 옵션들
}))
// 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, one }) => ({
project: one(projects, {
fields: [documentNumberTypes.projectId],
references: [projects.id],
}),
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
|
