summaryrefslogtreecommitdiff
path: root/app/api/ocr/enhanced/route.ts
diff options
context:
space:
mode:
Diffstat (limited to 'app/api/ocr/enhanced/route.ts')
-rw-r--r--app/api/ocr/enhanced/route.ts577
1 files changed, 577 insertions, 0 deletions
diff --git a/app/api/ocr/enhanced/route.ts b/app/api/ocr/enhanced/route.ts
new file mode 100644
index 00000000..14a81399
--- /dev/null
+++ b/app/api/ocr/enhanced/route.ts
@@ -0,0 +1,577 @@
+// app/api/ocr/rotation-enhanced/route.ts
+// DB 저장 기능이 추가된 회전 감지 및 테이블 추출 API
+
+import { NextRequest, NextResponse } from 'next/server';
+import { v4 as uuidv4 } from 'uuid';
+import db from '@/db/db'; // DB 인스턴스 import
+import {
+ ocrSessions,
+ ocrTables,
+ ocrRows,
+ ocrRotationAttempts,
+ type NewOcrSession,
+ type NewOcrTable,
+ type NewOcrRow,
+ type NewOcrRotationAttempt,
+ type BaseExtractedRow,
+ type ExtractedRow
+} from '@/db/schema/ocr';
+import { extractTablesFromOCR, analyzeOCRQuality } from '../utils/tableExtraction';
+import {
+ rotateImageBase64,
+ enhanceImageQuality,
+ convertPDFToImage,
+ needsRotation
+} from '../utils/imageRotation';
+
+interface RotationTestResult {
+ rotation: number;
+ confidence: number;
+ tablesFound: number;
+ textQuality: number;
+ keywordCount: number;
+ extractedTables?: ExtractedRow[][];
+}
+
+interface ProcessingResult {
+ success: boolean;
+ sessionId: string;
+ metadata: {
+ totalTables: number;
+ totalRows: number;
+ processingTime: number;
+ fileName: string;
+ fileSize: number;
+ bestRotation: number;
+ imageEnhanced: boolean;
+ pdfConverted: boolean;
+ };
+ error?: string;
+ warnings?: string[];
+}
+
+export async function POST(request: NextRequest) {
+ const startTime = Date.now();
+ const warnings: string[] = [];
+ let sessionId: string | null = null;
+
+ try {
+ console.log('🔄 Starting rotation-enhanced OCR processing...');
+
+ const formData = await request.formData();
+ const file = formData.get('file') as File;
+
+ if (!file) {
+ return NextResponse.json({
+ success: false,
+ error: 'No file provided'
+ }, { status: 400 });
+ }
+
+ console.log(`📁 Processing file: ${file.name} (${(file.size / 1024 / 1024).toFixed(2)} MB)`);
+
+ // 파일 검증
+ if (!file.type.includes('pdf') && !file.type.includes('image')) {
+ return NextResponse.json({
+ success: false,
+ error: 'Invalid file type. Only PDF and image files are supported.'
+ }, { status: 400 });
+ }
+
+ if (file.size > 10 * 1024 * 1024) { // 10MB 제한
+ return NextResponse.json({
+ success: false,
+ error: 'File size too large. Maximum size is 10MB.'
+ }, { status: 400 });
+ }
+
+ // 파일을 Buffer로 변환
+ const buffer = await file.arrayBuffer();
+ const fileBuffer = Buffer.from(buffer);
+ const fileformat = file.type.includes('pdf') ? 'pdf' : 'image';
+
+ let base64: string;
+ let imageEnhanced = false;
+ let pdfConverted = false;
+
+ // PDF vs 이미지 처리
+ if (fileformat === 'pdf') {
+ console.log('📄 Converting PDF to high-quality image...');
+ base64 = await convertPDFToImage(fileBuffer, 0);
+ pdfConverted = true;
+ } else {
+ base64 = fileBuffer.toString('base64');
+
+ // 이미지 품질 개선
+ console.log('🎨 Enhancing image quality...');
+ base64 = await enhanceImageQuality(base64);
+ imageEnhanced = true;
+ }
+
+ // 회전 테스트 수행
+ console.log('🔍 Testing multiple rotations...');
+ const rotationResults = await tryMultipleRotations(base64, file.name);
+
+ // 최적 회전 각도 선택
+ const bestResult = findBestRotation(rotationResults);
+ console.log(`✅ Best rotation found: ${bestResult.rotation}° (score: ${bestResult.score?.toFixed(2)})`);
+
+ if (bestResult.rotation !== 0) {
+ warnings.push(`Document was auto-rotated ${bestResult.rotation}° for optimal OCR results`);
+ }
+
+ // 최적 결과 사용
+ const finalTables = bestResult.extractedTables || [];
+ const totalRows = finalTables.reduce((sum, table) => sum + table.length, 0);
+ const processingTime = Date.now() - startTime;
+
+ // 🗃️ DB에 저장
+ console.log('💾 Saving results to database...');
+ sessionId = await saveToDatabase({
+ file,
+ fileformat,
+ processingTime,
+ bestRotation: bestResult.rotation,
+ finalTables,
+ totalRows,
+ rotationResults,
+ imageEnhanced,
+ pdfConverted,
+ warnings
+ });
+
+ console.log(`⏱️ Processing completed in ${(processingTime / 1000).toFixed(1)}s, saved as session ${sessionId}`);
+
+ const result: ProcessingResult = {
+ success: true,
+ sessionId,
+ metadata: {
+ totalTables: finalTables.length,
+ totalRows,
+ processingTime,
+ fileName: file.name,
+ fileSize: file.size,
+ bestRotation: bestResult.rotation,
+ imageEnhanced,
+ pdfConverted
+ },
+ warnings: warnings.length > 0 ? warnings : undefined
+ };
+
+ return NextResponse.json(result);
+
+ } catch (error) {
+ console.error('❌ OCR processing error:', error);
+
+ const processingTime = Date.now() - startTime;
+
+ // 에러 발생시에도 세션 저장 (실패 기록)
+ if (!sessionId) {
+ try {
+ const errorFile = await request.formData().then(fd => fd.get('file') as File);
+ if (errorFile) {
+ sessionId = await saveErrorToDatabase({
+ file: errorFile,
+ processingTime,
+ error: error instanceof Error ? error.message : 'Unknown error occurred'
+ });
+ }
+ } catch (dbError) {
+ console.error('Failed to save error to database:', dbError);
+ }
+ }
+
+ return NextResponse.json({
+ success: false,
+ sessionId: sessionId || null,
+ error: error instanceof Error ? error.message : 'Unknown error occurred',
+ metadata: {
+ processingTime,
+ totalTables: 0,
+ totalRows: 0,
+ fileName: '',
+ fileSize: 0,
+ bestRotation: 0,
+ imageEnhanced: false,
+ pdfConverted: false
+ }
+ }, { status: 500 });
+ }
+}
+
+// DB 저장 함수
+async function saveToDatabase({
+ file,
+ fileformat,
+ processingTime,
+ bestRotation,
+ finalTables,
+ totalRows,
+ rotationResults,
+ imageEnhanced,
+ pdfConverted,
+ warnings
+}: {
+ file: File;
+ fileformat: string;
+ processingTime: number;
+ bestRotation: number;
+ finalTables: ExtractedRow[][];
+ totalRows: number;
+ rotationResults: (RotationTestResult & { score?: number })[];
+ imageEnhanced: boolean;
+ pdfConverted: boolean;
+ warnings: string[];
+}): Promise<string> {
+
+ return await db.transaction(async (tx) => {
+ // 1. 세션 저장
+ const sessionData: NewOcrSession = {
+ fileName: file.name,
+ fileSize: file.size,
+ fileType: fileformat,
+ processingTime,
+ bestRotation,
+ totalTables: finalTables.length,
+ totalRows,
+ imageEnhanced,
+ pdfConverted,
+ success: true,
+ warnings: warnings.length > 0 ? warnings : null,
+ };
+
+ const [session] = await tx.insert(ocrSessions).values(sessionData).returning({ id: ocrSessions.id });
+ const sessionId = session.id;
+
+ // 2. 테이블들 저장
+ const tableIds: string[] = [];
+ for (let tableIndex = 0; tableIndex < finalTables.length; tableIndex++) {
+ const table = finalTables[tableIndex];
+
+ const tableData: NewOcrTable = {
+ sessionId,
+ tableIndex,
+ rowCount: table.length,
+ };
+
+ const [savedTable] = await tx.insert(ocrTables).values(tableData).returning({ id: ocrTables.id });
+ tableIds.push(savedTable.id);
+
+ // 3. 각 테이블의 행들 저장
+ if (table.length > 0) {
+ const rowsData: NewOcrRow[] = table.map((row, rowIndex) => ({
+ tableId: savedTable.id,
+ sessionId,
+ rowIndex,
+ reportNo: row.reportNo || null,
+ no: row.no || null,
+ identificationNo: row.identificationNo || null,
+ tagNo: row.tagNo || null,
+ jointNo: row.jointNo || null,
+ jointType: row.jointType || null,
+ weldingDate: row.weldingDate || null,
+ confidence: row.confidence.toString(),
+ sourceTable: row.sourceTable,
+ sourceRow: row.sourceRow,
+ }));
+
+ await tx.insert(ocrRows).values(rowsData);
+ }
+ }
+
+ // 4. 회전 시도 결과들 저장
+ const rotationAttemptsData: NewOcrRotationAttempt[] = rotationResults.map((result) => {
+ const extractedRowsCount = result.extractedTables?.reduce((sum, table) => sum + table.length, 0) || 0;
+
+ return {
+ sessionId,
+ rotation: result.rotation,
+ confidence: result.confidence.toString(),
+ tablesFound: result.tablesFound,
+ textQuality: result.textQuality.toString(),
+ keywordCount: result.keywordCount,
+ score: result.score?.toString() || '0',
+ extractedRowsCount,
+ };
+ });
+
+ if (rotationAttemptsData.length > 0) {
+ await tx.insert(ocrRotationAttempts).values(rotationAttemptsData);
+ }
+
+ console.log(`✅ Successfully saved session ${sessionId} with ${finalTables.length} tables and ${totalRows} rows`);
+ return sessionId;
+ });
+}
+
+// 에러 저장 함수
+async function saveErrorToDatabase({
+ file,
+ processingTime,
+ error
+}: {
+ file: File;
+ processingTime: number;
+ error: string;
+}): Promise<string> {
+
+ const sessionData: NewOcrSession = {
+ fileName: file.name,
+ fileSize: file.size,
+ fileType: file.type.includes('pdf') ? 'pdf' : 'image',
+ processingTime,
+ bestRotation: 0,
+ totalTables: 0,
+ totalRows: 0,
+ imageEnhanced: false,
+ pdfConverted: false,
+ success: false,
+ errorMessage: error,
+ };
+
+ const [session] = await db.insert(ocrSessions).values(sessionData).returning({ id: ocrSessions.id });
+ console.log(`💾 Error session saved: ${session.id}`);
+ return session.id;
+}
+
+// OCR 결과에서 Report No 추출
+function extractReportNo(ocrResult: any): string {
+ try {
+ console.log('📊 Extracting Report No from OCR result...');
+
+ // OCR 결과에서 tables와 fields 모두 확인
+ const allTexts: string[] = [];
+
+ // tables에서 텍스트 추출
+ if (ocrResult.images?.[0]?.tables) {
+ for (const table of ocrResult.images[0].tables) {
+ if (table.cells) {
+ for (const cell of table.cells) {
+ if (cell.cellTextLines) {
+ for (const textLine of cell.cellTextLines) {
+ if (textLine.cellWords) {
+ for (const word of textLine.cellWords) {
+ if (word.inferText) {
+ allTexts.push(word.inferText.trim());
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+
+ // fields에서 텍스트 추출
+ if (ocrResult.images?.[0]?.fields) {
+ for (const field of ocrResult.images[0].fields) {
+ if (field.inferText) {
+ allTexts.push(field.inferText.trim());
+ }
+ }
+ }
+
+ // Report No. 패턴을 찾기
+ let reportNo = '';
+ let foundReportNoLabel = false;
+
+ for (let i = 0; i < allTexts.length; i++) {
+ const text = allTexts[i];
+
+ // "Report", "No.", "Report No." 등의 패턴 찾기
+ if (text.toLowerCase().includes('report') &&
+ (text.toLowerCase().includes('no') ||
+ (i + 1 < allTexts.length && allTexts[i + 1].toLowerCase().includes('no')))) {
+ foundReportNoLabel = true;
+ console.log(`📋 Found Report No label at index ${i}: "${text}"`);
+ continue;
+ }
+
+ // Report No 라벨 다음에 오는 값이나 특정 패턴 (예: SN2661FT20250526) 찾기
+ if (foundReportNoLabel || /^[A-Z]{2}\d{4}[A-Z]{2}\d{8}$/.test(text)) {
+ // Report No 형식 패턴 체크 (예: SN2661FT20250526)
+ if (/^[A-Z]{2}\d{4}[A-Z]{2}\d{8}$/.test(text) ||
+ /^[A-Z0-9]{10,20}$/.test(text)) {
+ reportNo = text;
+ console.log(`✅ Found Report No: "${reportNo}"`);
+ break;
+ }
+ }
+ }
+
+ // 패턴이 없으면 더 관대한 검색
+ if (!reportNo) {
+ // Report나 No 근처의 영숫자 조합 찾기
+ for (let i = 0; i < allTexts.length; i++) {
+ const text = allTexts[i];
+ if ((text.toLowerCase().includes('report') || text.toLowerCase().includes('no')) &&
+ i + 1 < allTexts.length) {
+ const nextText = allTexts[i + 1];
+ if (/^[A-Z0-9]{6,}$/.test(nextText)) {
+ reportNo = nextText;
+ console.log(`✅ Found Report No (fallback): "${reportNo}"`);
+ break;
+ }
+ }
+ }
+ }
+
+ // 기본값 설정
+ if (!reportNo) {
+ console.warn('⚠️ Could not extract Report No, using default');
+ reportNo = 'UNKNOWN';
+ }
+
+ return reportNo;
+
+ } catch (error) {
+ console.error('❌ Error extracting Report No:', error);
+ return 'UNKNOWN';
+ }
+}
+
+// 여러 회전 각도 테스트
+async function tryMultipleRotations(base64: string, filename: string): Promise<(RotationTestResult & { score?: number })[]> {
+ const rotations = [0, 90, 180, 270];
+ const results: RotationTestResult[] = [];
+
+ console.log('🔍 Testing rotations with server-side image processing...');
+
+ for (const rotation of rotations) {
+ try {
+ console.log(` Testing ${rotation}° rotation...`);
+
+ // 🔄 서버 사이드에서 실제 이미지 회전
+ const rotatedBase64 = await rotateImageBase64(base64, rotation);
+
+ // OCR API 호출
+ const ocrResult = await callOCRAPI(rotatedBase64, 'jpg', `${rotation}_${filename}`);
+
+ console.log(ocrResult)
+
+ // Report No 추출
+ const reportNo = extractReportNo(ocrResult);
+ console.log(` ${rotation}°: Report No = "${reportNo}"`);
+
+ const analysis = analyzeOCRQuality(ocrResult);
+ const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][]; // 기존 함수 호출
+
+ // reportNo를 각 행에 추가
+ const extractedTables: ExtractedRow[][] = rawTables.map(table =>
+ table.map(row => ({
+ ...row,
+ reportNo // reportNo 필드 추가
+ }))
+ );
+
+ const result: RotationTestResult = {
+ rotation,
+ confidence: analysis.confidence,
+ tablesFound: analysis.tablesFound,
+ textQuality: analysis.textQuality,
+ keywordCount: analysis.keywordCount,
+ extractedTables
+ };
+
+ results.push(result);
+
+ const extractedRows = extractedTables.reduce((sum, table) => sum + table.length, 0);
+ console.log(` ${rotation}°: confidence=${(analysis.confidence * 100).toFixed(1)}%, tables=${analysis.tablesFound}, quality=${(analysis.textQuality * 100).toFixed(1)}%, keywords=${analysis.keywordCount}, rows=${extractedRows}`);
+
+ } catch (error) {
+ console.warn(` ${rotation}°: Failed -`, error instanceof Error ? error.message : 'Unknown error');
+ results.push({
+ rotation,
+ confidence: 0,
+ tablesFound: 0,
+ textQuality: 0,
+ keywordCount: 0,
+ extractedTables: []
+ });
+ }
+ }
+
+ return results.map(result => ({ ...result, score: 0 })); // score는 findBestRotation에서 계산
+}
+
+// 최적 회전 각도 찾기
+function findBestRotation(results: (RotationTestResult & { score?: number })[]): RotationTestResult & { score?: number } {
+ console.log('🎯 Analyzing rotation results...');
+
+ const scoredResults = results.map(result => {
+ const extractedRows = result.extractedTables?.reduce((sum, table) => sum + table.length, 0) || 0;
+
+ // 개선된 점수 계산 알고리즘
+ let score = 0;
+
+ // 1. OCR 신뢰도 (25%)
+ score += result.confidence * 0.25;
+
+ // 2. 발견된 테이블 수 (25%)
+ score += Math.min(result.tablesFound / 3, 1) * 0.25; // 최대 3개 테이블까지 점수
+
+ // 3. 텍스트 품질 (20%)
+ score += result.textQuality * 0.20;
+
+ // 4. 키워드 개수 (15%)
+ score += Math.min(result.keywordCount / 10, 1) * 0.15; // 최대 10개 키워드까지 점수
+
+ // 5. 추출된 행 수 (15%)
+ score += Math.min(extractedRows / 20, 1) * 0.15; // 최대 20행까지 점수
+
+ // 보너스: 실제 데이터가 추출된 경우
+ if (extractedRows > 0) {
+ score += 0.1; // 10% 보너스
+ }
+
+ console.log(` ${result.rotation}°: score=${score.toFixed(3)} (conf=${(result.confidence * 100).toFixed(1)}%, tables=${result.tablesFound}, quality=${(result.textQuality * 100).toFixed(1)}%, keywords=${result.keywordCount}, rows=${extractedRows})`);
+
+ return { ...result, score };
+ });
+
+ const bestResult = scoredResults.reduce((best, current) =>
+ current.score > best.score ? current : best
+ );
+
+ console.log(`🏆 Winner: ${bestResult.rotation}° with score ${bestResult.score?.toFixed(3)}`);
+
+ return bestResult;
+}
+
+// OCR API 호출
+async function callOCRAPI(base64: string, format: string, filename: string, rotation?: number): Promise<any> {
+ const ocrBody = {
+ version: "V2",
+ requestId: uuidv4(),
+ timestamp: Math.floor(Date.now() / 1000),
+ lang: "ko",
+ images: [{
+ format,
+ url: null,
+ data: base64,
+ name: filename,
+ ...(rotation !== undefined && rotation !== 0 && { rotation })
+ }],
+ enableTableDetection: true
+ };
+
+ const response = await fetch(
+ "https://n4a9z5rb5r.apigw.ntruss.com/custom/v1/35937/41dcc5202686a37ee5f4fae85bd07f599e3627e9c00d33bf6e469f409fe0ca62/general",
+ {
+ method: 'POST',
+ headers: {
+ 'Content-Type': 'application/json',
+ 'X-OCR-SECRET': process.env.OCR_SECRET_KEY || '',
+ },
+ body: JSON.stringify(ocrBody),
+ signal: AbortSignal.timeout(60000) // 60초 타임아웃
+ }
+ );
+
+ if (!response.ok) {
+ throw new Error(`OCR API error: ${response.status} ${response.statusText}`);
+ }
+
+ return response.json();
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 12:48:50 +0000