~20250428 작업사항

1 files changed, 34 insertions, 18 deletions

diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts
index 36810e50..ea6180ea 100644
--- a/db/schema/vendorData.ts
+++ b/db/schema/vendorData.ts
@@ -10,9 +10,10 @@ import {
     uniqueIndex,
     primaryKey,
     foreignKey,
-    pgView
+    pgView,
+    boolean
 } from "drizzle-orm/pg-core"
-import { and, eq} from "drizzle-orm";
+import { relations, and, eq, sql} from "drizzle-orm";
 
 import { contractItems } from "./contract"
 import { projects } from "./projects" // projects 테이블 임포트 가정
@@ -25,6 +26,10 @@ export const forms = pgTable("forms", {
         .references(() => contractItems.id, { onDelete: "cascade" }),
     formCode: varchar("form_code", { length: 100 }).notNull(),
     formName: varchar("form_name", { length: 255 }).notNull(),
+    // source: varchar("source", { length: 255 }),
+    // 새로 추가된 칼럼: eng와 im
+    eng: boolean("eng").default(false).notNull(),
+    im: boolean("im").default(false).notNull(),
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
 }, (table) => {
@@ -71,18 +76,22 @@ export const formEntries = pgTable("form_entries", {
 export const tags = pgTable("tags", {
     id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
     contractItemId: integer("contract_item_id")
-        .notNull()
-        .references(() => contractItems.id, { onDelete: "cascade" }),
+      .notNull()
+      .references(() => contractItems.id, { onDelete: "cascade" }),
     formId: integer("form_id")
-        .references(() => forms.id, { onDelete: "set null" }),
+      .references(() => forms.id, { onDelete: "set null" }),
     tagNo: varchar("tag_no", { length: 100 }).notNull(),
     tagType: varchar("tag_type", { length: 50 }).notNull(),
     class: varchar("class", { length: 100 }).notNull(),
     description: text("description"),
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
-})
-
+  }, (table) => {
+    return {
+      contractItemTagNoUnique: unique("contract_item_tag_no_unique").on(table.contractItemId, table.tagNo),
+    };
+  });
+  
 // tagTypes에 projectId 추가 및 복합 기본키 생성
 export const tagTypes = pgTable("tag_types", {
     code: varchar("code", { length: 50 }).notNull(),
@@ -122,10 +131,10 @@ export const tagSubfields = pgTable("tag_subfields", {
             table.attributesId
         ),
         // tagTypes 참조를 위한 복합 FK (tagTypeCode, projectId)
-        tagTypeRef: foreignKey({
-            columns: [table.tagTypeCode, table.projectId],
-            foreignColumns: [tagTypes.code, tagTypes.projectId]
-        }).onDelete("cascade")
+        // tagTypeRef: foreignKey({
+        //     columns: [table.tagTypeCode, table.projectId],
+        //     foreignColumns: [tagTypes.code, tagTypes.projectId]
+        // }).onDelete("cascade")
     };
 });
 
@@ -187,18 +196,17 @@ export const tagClasses = pgTable("tag_classes", {
 // tagTypeClassFormMappings에 projectId 추가
 export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings", {
     id: serial("id").primaryKey(),
-    projectId: integer("project_id")
-        .notNull()
-        .references(() => projects.id, { onDelete: "cascade" }),
+    projectId: integer("project_id").notNull(),  // Remove the reference here
     tagTypeLabel: varchar("tag_type_label", { length: 255 }).notNull(),
     classLabel: varchar("class_label", { length: 255 }).notNull(),
     formCode: varchar("form_code", { length: 50 }).notNull(),
     formName: varchar("form_name", { length: 255 }).notNull(),
+    ep: varchar("ep", { length: 255 }),
+    remark: varchar("remark", { length: 255 }),
     createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
     updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
 }, (table) => {
     return {
-        // 매핑은 프로젝트 내에서 유니크해야 함
         uniqMappingInProject: unique("uniq_mapping_in_project").on(
             table.projectId,
             table.tagTypeLabel,
@@ -206,13 +214,21 @@ export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings",
             table.formCode
         )
     };
-})
+});
+
+export const tagTypeClassFormMappingsRelations = relations(tagTypeClassFormMappings, ({ one }) => ({
+    project: one(projects, {
+        fields: [tagTypeClassFormMappings.projectId],
+        references: [projects.id],
+    }),
+}));
 
 // view_tag_subfields에도 projectId 추가
 export const viewTagSubfields = pgView("view_tag_subfields").as((qb) => {
     return qb
       .select({
-        // id: tagSubfields.id,
+
+        id: sql<number>`${tagSubfields.id}`.as("id"),
         // projectId: tagSubfields.projectId,
         tagTypeCode: tagSubfields.tagTypeCode,
         tagTypeDescription: tagTypes.description,
@@ -224,7 +240,7 @@ export const viewTagSubfields = pgView("view_tag_subfields").as((qb) => {
         createdAt: tagSubfields.createdAt,
         updatedAt: tagSubfields.updatedAt,
         // 프로젝트 관련 정보 추가
-        projectId: projects.id,
+        projectId: sql<number>`${projects.id}`.as("project_id"),  // Explicitly alias projects.id
         projectCode: projects.code,
         projectName: projects.name
       })