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

1 files changed, 135 insertions, 0 deletions

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