(박서영)docu-list-rule Project_code적용

1 files changed, 87 insertions, 6 deletions

diff --git a/db/schema/docu-list-rule.ts b/db/schema/docu-list-rule.ts
index 007646f9..ec20ab7a 100644
--- a/db/schema/docu-list-rule.ts
+++ b/db/schema/docu-list-rule.ts
@@ -1,10 +1,12 @@
 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(),
-  groupId: varchar("group_id", { length: 50 }).notNull().unique(), // GROUP ID (Code_번호 형태)
+  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 (자동 생성된 정규식)
@@ -12,18 +14,40 @@ export const codeGroups = pgTable("code_groups", {
   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(),
-  code: varchar("code", { length: 50 }).notNull().unique(), // CODE (자동 생성)
-  value: varchar("value", { length: 100 }).unique(), // 사용자가 선택할 수 있는 값 (고유해야 함)
+  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 테이블 =====
@@ -48,27 +72,47 @@ export const documentClassOptions = pgTable("document_class_options_new", {
 // ===== ComboBox Settings 테이블 =====
 export const comboBoxSettings = pgTable("combo_box_settings", {
   id: serial("id").primaryKey(),
+  projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
   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(),
+}, (table) => {
+  return {
+    // 같은 프로젝트 내에서 codeGroupId와 code 조합은 유니크해야 함
+    uniqueProjectCodeGroupCode: unique("unique_project_code_group_code").on(
+      table.projectId,
+      table.codeGroupId,
+      table.code
+    ),
+  }
 })
 
 // ===== 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)
+  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(),
+  projectId: integer("project_id").notNull().references(() => projects.id), // 프로젝트 ID 참조
   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)
@@ -77,12 +121,34 @@ export const documentNumberTypeConfigs = pgTable("document_number_type_configs",
   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 조합은 유니크해야 함
+    uniqueProjectNumberTypeSdq: unique("unique_project_number_type_sdq").on(
+      table.projectId,
+      table.documentNumberTypeId,
+      table.sdq
+    ),
+  }
 })
 
 // ===== 관계 정의 =====
 
+// Projects 관계
+export const projectsRelations = relations(projects, ({ many }) => ({
+  codeGroups: many(codeGroups),
+  documentClasses: many(documentClasses),
+  comboBoxSettings: many(comboBoxSettings),
+  documentNumberTypes: many(documentNumberTypes),
+  documentNumberTypeConfigs: many(documentNumberTypeConfigs),
+}))
+
 // Code Groups 관계
 export const codeGroupsRelations = relations(codeGroups, ({ many, one }) => ({
+  project: one(projects, {
+    fields: [codeGroups.projectId],
+    references: [projects.id],
+  }),
   documentClasses: many(documentClasses), // Code Group에 속한 Document Classes
   comboBoxSettings: many(comboBoxSettings), // Code Group에 속한 ComboBox Settings
   documentNumberTypeConfigs: many(documentNumberTypeConfigs), // Code Group을 참조하는 Number Type Configs
@@ -90,12 +156,15 @@ export const codeGroupsRelations = relations(codeGroups, ({ many, one }) => ({
 
 // Document Classes 관계
 export const documentClassesRelations = relations(documentClasses, ({ many, one }) => ({
+  project: one(projects, {
+    fields: [documentClasses.projectId],
+    references: [projects.id],
+  }),
   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 관계
@@ -108,6 +177,10 @@ export const documentClassOptionsRelations = relations(documentClassOptions, ({
 
 // ComboBox Settings 관계
 export const comboBoxSettingsRelations = relations(comboBoxSettings, ({ one }) => ({
+  project: one(projects, {
+    fields: [comboBoxSettings.projectId],
+    references: [projects.id],
+  }),
   codeGroup: one(codeGroups, {
     fields: [comboBoxSettings.codeGroupId],
     references: [codeGroups.id],
@@ -115,12 +188,20 @@ export const comboBoxSettingsRelations = relations(comboBoxSettings, ({ one }) =
 }))
 
 // Document Number Types 관계
-export const documentNumberTypesRelations = relations(documentNumberTypes, ({ many }) => ({
+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 }) => ({
+  project: one(projects, {
+    fields: [documentNumberTypeConfigs.projectId],
+    references: [projects.id],
+  }),
   documentNumberType: one(documentNumberTypes, {
     fields: [documentNumberTypeConfigs.documentNumberTypeId],
     references: [documentNumberTypes.id],