1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
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;
}
|
