(대표님) 20250709 변경사항 (약 18시 30분까지)

2 files changed, 161 insertions, 84 deletions

diff --git a/db/schema/ocr.ts b/db/schema/ocr.ts
index 12382aa4..f45fb523 100644
--- a/db/schema/ocr.ts
+++ b/db/schema/ocr.ts
@@ -49,6 +49,8 @@ export const ocrRows = pgTable('ocr_rows', {
   sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
   rowIndex: integer('row_index').notNull(), // 테이블 내에서 행 순서
   reportNo: varchar('report_no', { length: 100 }), // Report No. (예: SN2661FT20250526)
+  fileName: varchar('file_name', { length: 255 }), 
+  inspectionDate: varchar('inspection_date', { length: 255 }), 
   no: varchar('no', { length: 50 }),
   identificationNo: varchar('identification_no', { length: 100 }),
   tagNo: varchar('tag_no', { length: 100 }),
diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts
index 16ae87d9..b7c70e72 100644
--- a/db/schema/vendorData.ts
+++ b/db/schema/vendorData.ts
@@ -3,17 +3,17 @@ import {
     text,
     varchar,
     timestamp,
-    integer, 
-    unique, 
-    serial, 
-    jsonb, 
+    integer,
+    unique,
+    serial,
+    jsonb,
     uniqueIndex,
     primaryKey,
     foreignKey,
     pgView,
-    boolean, index
+    boolean, index ,json
 } from "drizzle-orm/pg-core"
-import { relations, and, eq, sql} from "drizzle-orm";
+import { relations, and, eq, sql } from "drizzle-orm";
 
 import { contractItems } from "./contract"
 import { projects } from "./projects" // projects 테이블 임포트 가정
@@ -76,22 +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) => {
+}, (table) => {
     return {
-      contractItemTagNoUnique: unique("contract_item_tag_no_unique").on(table.contractItemId, table.tagNo),
+        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(),
@@ -254,37 +254,37 @@ export const tagTypeClassFormMappingsRelations = relations(tagTypeClassFormMappi
 // view_tag_subfields에도 projectId 추가
 export const viewTagSubfields = pgView("view_tag_subfields").as((qb) => {
     return qb
-      .select({
-
-        id: sql<number>`${tagSubfields.id}`.as("id"),
-        // projectId: tagSubfields.projectId,
-        tagTypeCode: tagSubfields.tagTypeCode,
-        tagTypeDescription: tagTypes.description,
-        attributesId: tagSubfields.attributesId,
-        attributesDescription: tagSubfields.attributesDescription,
-        expression: tagSubfields.expression,
-        delimiter: tagSubfields.delimiter,
-        sortOrder: tagSubfields.sortOrder,
-        createdAt: tagSubfields.createdAt,
-        updatedAt: tagSubfields.updatedAt,
-        // 프로젝트 관련 정보 추가
-        projectId: sql<number>`${projects.id}`.as("project_id"),  // Explicitly alias projects.id
-        projectCode: projects.code,
-        projectName: projects.name
-      })
-      .from(tagSubfields)
-      .innerJoin(
-        tagTypes,
-        and(
-          eq(tagSubfields.tagTypeCode, tagTypes.code),
-          eq(tagSubfields.projectId, tagTypes.projectId)
+        .select({
+
+            id: sql<number>`${tagSubfields.id}`.as("id"),
+            // projectId: tagSubfields.projectId,
+            tagTypeCode: tagSubfields.tagTypeCode,
+            tagTypeDescription: tagTypes.description,
+            attributesId: tagSubfields.attributesId,
+            attributesDescription: tagSubfields.attributesDescription,
+            expression: tagSubfields.expression,
+            delimiter: tagSubfields.delimiter,
+            sortOrder: tagSubfields.sortOrder,
+            createdAt: tagSubfields.createdAt,
+            updatedAt: tagSubfields.updatedAt,
+            // 프로젝트 관련 정보 추가
+            projectId: sql<number>`${projects.id}`.as("project_id"),  // Explicitly alias projects.id
+            projectCode: projects.code,
+            projectName: projects.name
+        })
+        .from(tagSubfields)
+        .innerJoin(
+            tagTypes,
+            and(
+                eq(tagSubfields.tagTypeCode, tagTypes.code),
+                eq(tagSubfields.projectId, tagTypes.projectId)
+            )
+        )
+        .innerJoin(
+            projects,
+            eq(tagSubfields.projectId, projects.id)
         )
-      )
-      .innerJoin(
-        projects,
-        eq(tagSubfields.projectId, projects.id)
-      )
-  });
+});
 
 // 타입 정의 업데이트
 export type Tag = typeof tags.$inferSelect
@@ -299,55 +299,130 @@ export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect
 export const vendorDataReportTemps = pgTable("vendor_data_report_temps", {
     id: serial("id").primaryKey(),
     contractItemId: integer("contract_item_id")
-      .notNull()
-      .references(() => contractItems.id, { onDelete: "cascade" }),
+        .notNull()
+        .references(() => contractItems.id, { onDelete: "cascade" }),
     formId: integer("form_id")
-      .notNull()
-      .references(() => forms.id, { onDelete: "cascade" }),
+        .notNull()
+        .references(() => forms.id, { onDelete: "cascade" }),
     fileName: varchar("file_name", { length: 255 }).notNull(),
     filePath: varchar("file_path", { length: 1024 }).notNull(),
     createdAt: timestamp("created_at", { withTimezone: true })
-    .defaultNow()
-    .notNull(),
+        .defaultNow()
+        .notNull(),
     updatedAt: timestamp("updated_at", { withTimezone: true })
-    .defaultNow()
-    .notNull(),
+        .defaultNow()
+        .notNull(),
 });
-  
+
 export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect;
 
 
 export const formListsView = pgView("form_lists_view").as((qb) => {
     return qb
-      .select({
-        // Primary identifiers
-        id: sql<number>`${tagTypeClassFormMappings.id}`.as("id"),
-        projectId: sql<number>`${tagTypeClassFormMappings.projectId}`.as("project_id"),
-        
-        // Project information
-        projectCode: sql<string>`${projects.code}`.as("project_code"),
-        projectName: sql<string>`${projects.name}`.as("project_name"),
-        
-        // Form information
-        tagTypeLabel: sql<string>`${tagTypeClassFormMappings.tagTypeLabel}`.as("tag_type_label"),
-        classLabel: sql<string>`${tagTypeClassFormMappings.classLabel}`.as("class_label"),
-        formCode: sql<string>`${tagTypeClassFormMappings.formCode}`.as("form_code"),
-        formName: sql<string>`${tagTypeClassFormMappings.formName}`.as("form_name"),
-        
-        // Additional fields
-        ep: sql<string | null>`${tagTypeClassFormMappings.ep}`.as("ep"),
-        remark: sql<string | null>`${tagTypeClassFormMappings.remark}`.as("remark"),
-        
-        // Timestamps
-        createdAt: sql<Date>`${tagTypeClassFormMappings.createdAt}`.as("created_at"),
-        updatedAt: sql<Date>`${tagTypeClassFormMappings.updatedAt}`.as("updated_at"),
-      })
-      .from(tagTypeClassFormMappings)
-      .innerJoin(
-        projects, 
-        eq(tagTypeClassFormMappings.projectId, projects.id)
-      );
-  });
-  
-  
-  export type FormListsView = typeof formListsView.$inferSelect;
+        .select({
+            // Primary identifiers
+            id: sql<number>`${tagTypeClassFormMappings.id}`.as("id"),
+            projectId: sql<number>`${tagTypeClassFormMappings.projectId}`.as("project_id"),
+
+            // Project information
+            projectCode: sql<string>`${projects.code}`.as("project_code"),
+            projectName: sql<string>`${projects.name}`.as("project_name"),
+
+            // Form information
+            tagTypeLabel: sql<string>`${tagTypeClassFormMappings.tagTypeLabel}`.as("tag_type_label"),
+            classLabel: sql<string>`${tagTypeClassFormMappings.classLabel}`.as("class_label"),
+            formCode: sql<string>`${tagTypeClassFormMappings.formCode}`.as("form_code"),
+            formName: sql<string>`${tagTypeClassFormMappings.formName}`.as("form_name"),
+
+            // Additional fields
+            ep: sql<string | null>`${tagTypeClassFormMappings.ep}`.as("ep"),
+            remark: sql<string | null>`${tagTypeClassFormMappings.remark}`.as("remark"),
+
+            // Timestamps
+            createdAt: sql<Date>`${tagTypeClassFormMappings.createdAt}`.as("created_at"),
+            updatedAt: sql<Date>`${tagTypeClassFormMappings.updatedAt}`.as("updated_at"),
+        })
+        .from(tagTypeClassFormMappings)
+        .innerJoin(
+            projects,
+            eq(tagTypeClassFormMappings.projectId, projects.id)
+        );
+});
+
+
+export type FormListsView = typeof formListsView.$inferSelect;
+
+
+// TemplateItem 스키마 정의
+export const templateItems = pgTable("template_items", {
+    id: serial("id").primaryKey(),
+
+    // tag_type_class_form_mappings 테이블과의 관계
+    formMappingId: integer("form_mapping_id").notNull().references(() => tagTypeClassFormMappings.id),
+
+    // TemplateItem 기본 필드들
+    tmplId: varchar("tmpl_id", { length: 255 }).notNull(),
+    name: varchar("name", { length: 255 }).notNull(),
+    tmplType: varchar("tmpl_type", { length: 100 }).notNull(),
+
+    // 복잡한 객체들을 JSON으로 저장
+    sprLstSetup: json("spr_lst_setup").$type<{
+        ACT_SHEET: string;
+        HIDN_SHEETS: Array<string>;
+        CONTENT?: string;
+        DATA_SHEETS: Array<{
+            SHEET_NAME: string;
+            REG_TYPE_ID: string;
+            MAP_CELL_ATT: Array<{
+                ATT_ID: string;
+                IN: string;
+            }>;
+        }>;
+    }>().notNull(),
+
+    grdLstSetup: json("grd_lst_setup").$type<{
+        REG_TYPE_ID: string;
+        SPR_ITM_IDS: Array<string>;
+        ATTS: Array<any>;
+    }>().notNull(),
+
+    sprItmLstSetup: json("spr_itm_lst_setup").$type<{
+        ACT_SHEET: string;
+        HIDN_SHEETS: Array<string>;
+        CONTENT?: string;
+        DATA_SHEETS: Array<{
+            SHEET_NAME: string;
+            REG_TYPE_ID: string;
+            MAP_CELL_ATT: Array<{
+                ATT_ID: string;
+                IN: string;
+            }>;
+        }>;
+    }>().notNull(),
+
+    // 메타데이터
+    description: text("description"), // 템플릿 설명
+    isActive: boolean("is_active").default(true).notNull(), // 활성/비활성 상태
+
+    // 타임스탬프
+    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => {
+    return {
+        // 같은 form mapping에서 동일한 tmpl_id는 중복될 수 없음
+        uniqTmplInFormMapping: unique("uniq_tmpl_in_form_mapping").on(
+            table.formMappingId,
+            table.tmplId
+        ),
+
+        // 템플릿 이름도 form mapping 내에서 유니크
+        uniqNameInFormMapping: unique("uniq_name_in_form_mapping").on(
+            table.formMappingId,
+            table.name
+        )
+    };
+});
+
+// TypeScript 타입 추출
+export type TemplateItem = typeof templateItems.$inferSelect;
+export type NewTemplateItem = typeof templateItems.$inferInsert;
\ No newline at end of file