diff options
Diffstat (limited to 'db/schema/ocr.ts')
| -rw-r--r-- | db/schema/ocr.ts | 135 |
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 |