(대표님) 변경사항 0604 - OCR 관련 및 drizzle generated sqls

3 files changed, 183 insertions, 3 deletions

diff --git a/db/schema/index.ts b/db/schema/index.ts
index 309af050..5c0b3135 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -14,4 +14,5 @@ export * from './logs';
 export * from './basicContractDocumnet';
 export * from './procurementRFQ';
 export * from './setting';
-export * from './techSales';
\ No newline at end of file
+export * from './techSales';
+export * from './ocr';
\ No newline at end of file
diff --git a/db/schema/ocr.ts b/db/schema/ocr.ts
new file mode 100644
index 00000000..289eb297
--- /dev/null
+++ b/db/schema/ocr.ts
@@ -0,0 +1,135 @@
+// db/schema/ocr.ts
+import { 
+  pgTable, 
+  uuid, 
+  varchar, 
+  integer, 
+  decimal, 
+  timestamp, 
+  boolean, 
+  jsonb, 
+  text, 
+  serial 
+} from 'drizzle-orm/pg-core';
+import { relations } from 'drizzle-orm';
+
+// OCR 세션 테이블 (전체 처리 정보)
+export const ocrSessions = pgTable('ocr_sessions', {
+  id: uuid('id').defaultRandom().primaryKey(),
+  fileName: varchar('file_name', { length: 255 }).notNull(),
+  fileSize: integer('file_size').notNull(),
+  fileType: varchar('file_type', { length: 50 }).notNull(), // 'pdf' | 'image'
+  processingTime: integer('processing_time').notNull(), // milliseconds
+  bestRotation: integer('best_rotation').notNull().default(0),
+  totalTables: integer('total_tables').notNull().default(0),
+  totalRows: integer('total_rows').notNull().default(0),
+  imageEnhanced: boolean('image_enhanced').notNull().default(false),
+  pdfConverted: boolean('pdf_converted').notNull().default(false),
+  success: boolean('success').notNull().default(true),
+  errorMessage: text('error_message'),
+  warnings: jsonb('warnings').$type<string[]>(),
+  createdAt: timestamp('created_at').notNull().defaultNow(),
+  updatedAt: timestamp('updated_at').notNull().defaultNow(),
+});
+
+// 추출된 테이블 정보
+export const ocrTables = pgTable('ocr_tables', {
+  id: uuid('id').defaultRandom().primaryKey(),
+  sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
+  tableIndex: integer('table_index').notNull(), // 세션 내에서 테이블 순서
+  rowCount: integer('row_count').notNull().default(0),
+  createdAt: timestamp('created_at').notNull().defaultNow(),
+});
+
+// 추출된 행 데이터
+export const ocrRows = pgTable('ocr_rows', {
+  id: uuid('id').defaultRandom().primaryKey(),
+  tableId: uuid('table_id').notNull().references(() => ocrTables.id, { onDelete: 'cascade' }),
+  sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
+  rowIndex: integer('row_index').notNull(), // 테이블 내에서 행 순서
+  reportNo: varchar('report_no', { length: 100 }), // Report No. (예: SN2661FT20250526)
+  no: varchar('no', { length: 50 }),
+  identificationNo: varchar('identification_no', { length: 100 }),
+  tagNo: varchar('tag_no', { length: 100 }),
+  jointNo: varchar('joint_no', { length: 100 }),
+  jointType: varchar('joint_type', { length: 100 }),
+  weldingDate: varchar('welding_date', { length: 50 }),
+  confidence: decimal('confidence', { precision: 5, scale: 4 }), // 0.0000 ~ 1.0000
+  sourceTable: integer('source_table'),
+  sourceRow: integer('source_row'),
+  createdAt: timestamp('created_at').notNull().defaultNow(),
+});
+
+// 회전 시도 결과
+export const ocrRotationAttempts = pgTable('ocr_rotation_attempts', {
+  id: uuid('id').defaultRandom().primaryKey(),
+  sessionId: uuid('session_id').notNull().references(() => ocrSessions.id, { onDelete: 'cascade' }),
+  rotation: integer('rotation').notNull(), // 0, 90, 180, 270
+  confidence: decimal('confidence', { precision: 5, scale: 4 }), // OCR 신뢰도
+  tablesFound: integer('tables_found').notNull().default(0),
+  textQuality: decimal('text_quality', { precision: 5, scale: 4 }),
+  keywordCount: integer('keyword_count').notNull().default(0),
+  score: decimal('score', { precision: 5, scale: 4 }), // 계산된 점수
+  extractedRowsCount: integer('extracted_rows_count').notNull().default(0),
+  createdAt: timestamp('created_at').notNull().defaultNow(),
+});
+
+// Relations 정의
+export const ocrSessionsRelations = relations(ocrSessions, ({ many }) => ({
+  tables: many(ocrTables),
+  rows: many(ocrRows),
+  rotationAttempts: many(ocrRotationAttempts),
+}));
+
+export const ocrTablesRelations = relations(ocrTables, ({ one, many }) => ({
+  session: one(ocrSessions, {
+    fields: [ocrTables.sessionId],
+    references: [ocrSessions.id],
+  }),
+  rows: many(ocrRows),
+}));
+
+export const ocrRowsRelations = relations(ocrRows, ({ one }) => ({
+  session: one(ocrSessions, {
+    fields: [ocrRows.sessionId],
+    references: [ocrSessions.id],
+  }),
+  table: one(ocrTables, {
+    fields: [ocrRows.tableId],
+    references: [ocrTables.id],
+  }),
+}));
+
+export const ocrRotationAttemptsRelations = relations(ocrRotationAttempts, ({ one }) => ({
+  session: one(ocrSessions, {
+    fields: [ocrRotationAttempts.sessionId],
+    references: [ocrSessions.id],
+  }),
+}));
+
+// 타입 정의
+export type OcrSession = typeof ocrSessions.$inferSelect;
+export type OcrTable = typeof ocrTables.$inferSelect;
+export type OcrRow = typeof ocrRows.$inferSelect;
+export type OcrRotationAttempt = typeof ocrRotationAttempts.$inferSelect;
+
+export type NewOcrSession = typeof ocrSessions.$inferInsert;
+export type NewOcrTable = typeof ocrTables.$inferInsert;
+export type NewOcrRow = typeof ocrRows.$inferInsert;
+export type NewOcrRotationAttempt = typeof ocrRotationAttempts.$inferInsert;
+
+export interface BaseExtractedRow {
+  no: string;
+  identificationNo: string;
+  tagNo: string;
+  jointNo: string;
+  jointType: string;
+  weldingDate: string;
+  confidence: number;
+  sourceTable: number;
+  sourceRow: number;
+}
+
+export interface ExtractedRow extends BaseExtractedRow {
+  reportNo: string;
+}
\ No newline at end of file
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 8c144f35..ebae7630 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -15,14 +15,47 @@ export const documents = pgTable(
     contractId: integer("contract_id")
       .notNull()
       .references(() => contracts.id, { onDelete: "cascade" }),
+    
+    // 기본 문서 정보
     docNumber: varchar("doc_number", { length: 100 }).notNull(),
-    // ✅ 벤더용 문서 번호 추가 (옵셔널)
     vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
     title: varchar("title", { length: 255 }).notNull(),
     status: varchar("status", { length: 50 })
       .notNull()
       .default("ACTIVE"),
     issuedDate: date("issued_date"),
+    
+    // ✅ DOLCE 연동을 위한 새로운 필드들
+    drawingKind: varchar("drawing_kind", { length: 10 }), // B3, B4, B5
+    drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }), // 도면입수, 도면제출, GTT Deliverable, SHI Input Information
+    discipline: varchar("discipline", { length: 10 }), // DE, ME, etc.
+    
+    // ✅ 외부 시스템 연동 정보
+    externalDocumentId: varchar("external_document_id", { length: 100 }), // DOLCE 시스템의 문서 ID
+    externalSystemType: varchar("external_system_type", { length: 20 }), // DOLCE, SWP
+    externalSyncedAt: timestamp("external_synced_at"), // 마지막 동기화 시간
+    
+    // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
+    cGbn: varchar("c_gbn", { length: 50 }), // CGbn
+    dGbn: varchar("d_gbn", { length: 50 }), // DGbn  
+    degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
+    deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
+    jGbn: varchar("j_gbn", { length: 50 }), // JGbn
+    sGbn: varchar("s_gbn", { length: 50 }), // SGbn
+    
+    // ✅ DOLCE 응답의 추가 정보들
+    shiDrawingNo: varchar("shi_drawing_no", { length: 100 }), // SHI 도면 번호
+    manager: varchar("manager", { length: 100 }), // 담당자
+    managerENM: varchar("manager_enm", { length: 100 }), // 담당자 영문명  
+    managerNo: varchar("manager_no", { length: 50 }), // 담당자 번호
+    registerGroup: integer("register_group"), // 등록 그룹
+    registerGroupId: integer("register_group_id"), // 등록 그룹 ID
+    
+    // ✅ 생성자 정보 (DOLCE에서 가져온 정보)
+    createUserNo: varchar("create_user_no", { length: 50 }), // 생성자 번호
+    createUserId: varchar("create_user_id", { length: 100 }), // 생성자 ID (한글명)
+    createUserENM: varchar("create_user_enm", { length: 100 }), // 생성자 영문명
+    
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
   },
@@ -33,14 +66,24 @@ export const documents = pgTable(
         table.docNumber,
         table.status
       ),
-      // ✅ 벤더 문서 번호가 있는 경우 유니크 제약 조건 (옵셔널이므로 별도 인덱스)
       uniqueContractVendorDoc: uniqueIndex("unique_contract_vendor_doc").on(
         table.contractId,
         table.vendorDocNumber
       ).where(sql`${table.vendorDocNumber} IS NOT NULL`),
+      
+      // ✅ 외부 시스템 문서 ID 유니크 인덱스
+      uniqueExternalDoc: uniqueIndex("unique_external_doc").on(
+        table.contractId,
+        table.externalDocumentId,
+        table.externalSystemType
+      ).where(sql`${table.externalDocumentId} IS NOT NULL`),
+      
+      // ✅ drawingKind 인덱스 (자주 검색될 것 같음)
+      drawingKindIndex: index("drawing_kind_idx").on(table.drawingKind),
     }
   }
 )
+
 // 확장된 issueStages 테이블
 export const issueStages = pgTable(
   "issue_stages",
@@ -115,6 +158,7 @@ export const revisions = pgTable(
     reviewerId: integer("reviewer_id"),
     reviewerName: varchar("reviewer_name", { length: 100 }),
     reviewComments: varchar("review_comments", { length: 1000 }),
+    externalUploadId: varchar("external_upload_id", { length: 255 }),
     
     comment: varchar("comment", { length: 500 }),
     createdAt: timestamp("created_at").defaultNow().notNull(),