diff options
Diffstat (limited to 'app/api/ocr/enhanced/route.ts')
| -rw-r--r-- | app/api/ocr/enhanced/route.ts | 1112 |
1 files changed, 757 insertions, 355 deletions
diff --git a/app/api/ocr/enhanced/route.ts b/app/api/ocr/enhanced/route.ts index 14a81399..06cea358 100644 --- a/app/api/ocr/enhanced/route.ts +++ b/app/api/ocr/enhanced/route.ts @@ -1,13 +1,15 @@ +// ============================================================================ // app/api/ocr/rotation-enhanced/route.ts -// DB 저장 기능이 추가된 회전 감지 및 테이블 추출 API +// 최적화된 OCR API - 통합 처리로 API 호출 최소화 +// ============================================================================ import { NextRequest, NextResponse } from 'next/server'; import { v4 as uuidv4 } from 'uuid'; -import db from '@/db/db'; // DB 인스턴스 import -import { - ocrSessions, - ocrTables, - ocrRows, +import db from '@/db/db'; +import { + ocrSessions, + ocrTables, + ocrRows, ocrRotationAttempts, type NewOcrSession, type NewOcrTable, @@ -17,20 +19,36 @@ import { type ExtractedRow } from '@/db/schema/ocr'; import { extractTablesFromOCR, analyzeOCRQuality } from '../utils/tableExtraction'; -import { - rotateImageBase64, - enhanceImageQuality, - convertPDFToImage, - needsRotation +import { + rotateImageBase64, + enhanceImageQuality, + convertPDFToImage, + needsRotation, + normalizeImageFormat, + validateBase64Image, + getPDFPageCount, + detectTextOrientation } from '../utils/imageRotation'; - -interface RotationTestResult { - rotation: number; +import { getServerSession } from "next-auth/next" +import { authOptions } from "@/app/api/auth/[...nextauth]/route" +import { sql } from 'drizzle-orm'; +import sharp from 'sharp'; + +interface PageRotationInfo { + pageIndex: number; + optimalRotation: number; confidence: number; - tablesFound: number; - textQuality: number; - keywordCount: number; - extractedTables?: ExtractedRow[][]; + needsRotationCheck: boolean; +} + +interface OptimizedProcessingResult { + success: boolean; + sessionId: string; + extractedTables: ExtractedRow[][]; + totalApiCalls: number; + processingTime: number; + pageRotations: PageRotationInfo[]; + warnings: string[]; } interface ProcessingResult { @@ -45,6 +63,16 @@ interface ProcessingResult { bestRotation: number; imageEnhanced: boolean; pdfConverted: boolean; + pagesProcessed?: number; + totalApiCalls: number; + pageDetails?: Array<{ + pageIndex: number; + rotation: number; + tablesFound: number; + rowsFound: number; + confidence: number; + processingTime: number; + }>; }; error?: string; warnings?: string[]; @@ -54,17 +82,29 @@ export async function POST(request: NextRequest) { const startTime = Date.now(); const warnings: string[] = []; let sessionId: string | null = null; + let file: File | null = null; try { - console.log('🔄 Starting rotation-enhanced OCR processing...'); - + console.log('🚀 === STARTING OPTIMIZED OCR PROCESSING ==='); + + const session = await getServerSession(authOptions); + if (!session?.user) { + return NextResponse.json( + { success: false, message: "인증이 필요합니다" }, + { status: 401 } + ) + } + + const userId = parseInt(session.user.id) + + // formData 한 번만 읽기 const formData = await request.formData(); - const file = formData.get('file') as File; - + file = formData.get('file') as File; + if (!file) { - return NextResponse.json({ - success: false, - error: 'No file provided' + return NextResponse.json({ + success: false, + error: 'No file provided' }, { status: 400 }); } @@ -72,16 +112,16 @@ export async function POST(request: NextRequest) { // 파일 검증 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.' + 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.' + return NextResponse.json({ + success: false, + error: 'File size too large. Maximum size is 10MB.' }, { status: 400 }); } @@ -90,54 +130,35 @@ export async function POST(request: NextRequest) { const fileBuffer = Buffer.from(buffer); const fileformat = file.type.includes('pdf') ? 'pdf' : 'image'; - let base64: string; - let imageEnhanced = false; - let pdfConverted = false; + let processingResult: OptimizedProcessingResult; - // PDF vs 이미지 처리 if (fileformat === 'pdf') { - console.log('📄 Converting PDF to high-quality image...'); - base64 = await convertPDFToImage(fileBuffer, 0); - pdfConverted = true; + console.log('📄 Processing PDF with optimized approach...'); + processingResult = await processPDFOptimized(fileBuffer, file.name); } 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`); + console.log('🖼️ Processing single image with optimized approach...'); + processingResult = await processImageOptimized(fileBuffer, file.name); } - // 최적 결과 사용 - const finalTables = bestResult.extractedTables || []; - const totalRows = finalTables.reduce((sum, table) => sum + table.length, 0); const processingTime = Date.now() - startTime; - // 🗃️ DB에 저장 + console.log(`\n📊 === PROCESSING SUMMARY ===`); + console.log(`Total API calls: ${processingResult.totalApiCalls} (optimized!)`); + console.log(`Total tables found: ${processingResult.extractedTables.length}`); + console.log(`Total rows extracted: ${processingResult.extractedTables.reduce((sum, table) => sum + table.length, 0)}`); + console.log(`Total processing time: ${(processingTime / 1000).toFixed(1)}s`); + + // DB에 저장 console.log('💾 Saving results to database...'); sessionId = await saveToDatabase({ + userId, file, fileformat, processingTime, - bestRotation: bestResult.rotation, - finalTables, - totalRows, - rotationResults, - imageEnhanced, - pdfConverted, - warnings + extractedTables: processingResult.extractedTables, + pageRotations: processingResult.pageRotations, + totalApiCalls: processingResult.totalApiCalls, + warnings: [...warnings, ...processingResult.warnings] }); console.log(`⏱️ Processing completed in ${(processingTime / 1000).toFixed(1)}s, saved as session ${sessionId}`); @@ -146,41 +167,48 @@ export async function POST(request: NextRequest) { success: true, sessionId, metadata: { - totalTables: finalTables.length, - totalRows, + totalTables: processingResult.extractedTables.length, + totalRows: processingResult.extractedTables.reduce((sum, table) => sum + table.length, 0), processingTime, fileName: file.name, fileSize: file.size, - bestRotation: bestResult.rotation, - imageEnhanced, - pdfConverted + bestRotation: processingResult.pageRotations[0]?.optimalRotation || 0, + imageEnhanced: fileformat === 'image', + pdfConverted: fileformat === 'pdf', + pagesProcessed: processingResult.pageRotations.length, + totalApiCalls: processingResult.totalApiCalls, + pageDetails: processingResult.pageRotations.map(p => ({ + pageIndex: p.pageIndex, + rotation: p.optimalRotation, + tablesFound: 0, // 통합 처리로 인해 페이지별 세부사항은 후처리에서 계산 + rowsFound: 0, + confidence: p.confidence, + processingTime: 0 + })) }, - warnings: warnings.length > 0 ? warnings : undefined + warnings: [...warnings, ...processingResult.warnings].length > 0 ? [...warnings, ...processingResult.warnings] : undefined }; - + return NextResponse.json(result); } catch (error) { console.error('❌ OCR processing error:', error); - + const processingTime = Date.now() - startTime; - - // 에러 발생시에도 세션 저장 (실패 기록) - if (!sessionId) { + + // 에러 발생시에도 세션 저장 + if (!sessionId && file) { 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' - }); - } + sessionId = await saveErrorToDatabase({ + file, + 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, @@ -189,117 +217,582 @@ export async function POST(request: NextRequest) { processingTime, totalTables: 0, totalRows: 0, - fileName: '', - fileSize: 0, + fileName: file?.name || '', + fileSize: file?.size || 0, bestRotation: 0, imageEnhanced: false, - pdfConverted: false + pdfConverted: false, + totalApiCalls: 0 } }, { status: 500 }); } } -// DB 저장 함수 +// PDF 최적화 처리 - 통합 방식 +async function processPDFOptimized( + fileBuffer: Buffer, + fileName: string +): Promise<OptimizedProcessingResult> { + console.log('🚀 === OPTIMIZED PDF PROCESSING ==='); + + const warnings: string[] = []; + + try { + // 1단계: 페이지 수 확인 + const pageCount = await getPDFPageCount(fileBuffer); + console.log(`📊 PDF contains ${pageCount} pages`); + + // 2단계: 각 페이지의 최적 회전각 결정 (OCR API 호출 없이) + const pageRotations = await determineOptimalRotations(fileBuffer, pageCount); + console.log('🧭 Optimal rotations determined:', pageRotations.map(p => `Page ${p.pageIndex + 1}: ${p.optimalRotation}°`)); + + // 3단계: 처리 방식 결정 + if (shouldProcessAsWhole(pageCount, fileBuffer.length)) { + console.log('📋 Processing as single combined document...'); + const result = await processAsWholePDF(fileBuffer, pageRotations, fileName); + return { + success: true, + sessionId: '', + extractedTables: result.extractedTables, + totalApiCalls: 1, // 🎉 단 1번의 API 호출! + processingTime: Date.now(), + pageRotations, + warnings: [...warnings, ...result.warnings] + }; + } else { + console.log('📄 Processing pages individually with optimal rotations...'); + const result = await processPageByPageOptimized(fileBuffer, pageRotations, fileName); + return { + success: true, + sessionId: '', + extractedTables: result.extractedTables, + totalApiCalls: pageCount, // 페이지 수만큼만 호출 + processingTime: Date.now(), + pageRotations, + warnings: [...warnings, ...result.warnings] + }; + } + + } catch (error) { + console.error('❌ PDF processing failed:', error); + throw error; + } +} + +// 이미지 최적화 처리 +async function processImageOptimized( + fileBuffer: Buffer, + fileName: string +): Promise<OptimizedProcessingResult> { + console.log('🖼️ === OPTIMIZED IMAGE PROCESSING ==='); + + const warnings: string[] = []; + + try { + let base64 = fileBuffer.toString('base64'); + + // 원본 이미지 검증 + const originalValidation = validateBase64Image(base64); + if (!originalValidation.isValid) { + throw new Error(`Invalid image file: ${originalValidation.error}`); + } + + console.log(`✅ Original image valid: ${originalValidation.size} bytes`); + + // 이미지 품질 개선 + console.log('🎨 Enhancing image quality...'); + base64 = await enhanceImageQuality(base64); + + // 최적 회전각 결정 (OCR API 호출 없이) + const optimalRotation = await detectTextOrientation(base64); + console.log(`🧭 Optimal rotation detected: ${optimalRotation}°`); + + // 최적 회전 적용 + if (optimalRotation !== 0) { + console.log(`🔄 Applying optimal rotation: ${optimalRotation}°...`); + base64 = await rotateImageBase64(base64, optimalRotation); + warnings.push(`Image was auto-rotated ${optimalRotation}° for optimal OCR results`); + } + + // 단 1번의 OCR API 호출 + console.log('🌐 Single OCR API call for optimized image...'); + const ocrResult = await callOCRAPI(base64, 'jpg', fileName); + + // 결과 처리 + const reportNo = extractReportNo(ocrResult); + const analysis = analyzeOCRQuality(ocrResult); + const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][]; + + const extractedTables: ExtractedRow[][] = rawTables.map(table => + table.map(row => ({ + ...row, + reportNo + })) + ); + + const pageRotations: PageRotationInfo[] = [{ + pageIndex: 0, + optimalRotation, + confidence: analysis.confidence, + needsRotationCheck: optimalRotation !== 0 + }]; + + return { + success: true, + sessionId: '', + extractedTables, + totalApiCalls: 1, // 단 1번의 API 호출! + processingTime: Date.now(), + pageRotations, + warnings + }; + + } catch (error) { + console.error('❌ Image processing failed:', error); + throw error; + } +} + +// 페이지별 최적 회전각 결정 (OCR API 없이) +async function determineOptimalRotations( + fileBuffer: Buffer, + pageCount: number +): Promise<PageRotationInfo[]> { + console.log('🔍 Determining optimal rotations without OCR calls...'); + + const rotations: PageRotationInfo[] = []; + + for (let pageIndex = 0; pageIndex < pageCount; pageIndex++) { + try { + console.log(` 📄 Analyzing page ${pageIndex + 1}...`); + + // 저해상도로 빠르게 변환 (150 DPI - 품질과 속도의 균형) + const lowResBase64 = await convertPDFToImage(fileBuffer, pageIndex, 150); + + // 회전 필요성 확인 + const needsRotationCheck = await needsRotation(lowResBase64); + + let optimalRotation = 0; + let confidence = 1.0; + + if (needsRotationCheck) { + console.log(` 🔄 Page ${pageIndex + 1} needs rotation analysis...`); + // 이미지 분석 기반 회전 감지 + optimalRotation = await detectTextOrientation(lowResBase64); + confidence = await calculateRotationConfidence(lowResBase64, optimalRotation); + } else { + console.log(` ✅ Page ${pageIndex + 1} appears correctly oriented`); + } + + rotations.push({ + pageIndex, + optimalRotation, + confidence, + needsRotationCheck + }); + + console.log(` 📊 Page ${pageIndex + 1}: ${optimalRotation}° rotation (confidence: ${(confidence * 100).toFixed(1)}%)`); + + } catch (error) { + console.warn(` ⚠️ Page ${pageIndex + 1}: rotation detection failed, using 0°`); + rotations.push({ + pageIndex, + optimalRotation: 0, + confidence: 0.5, + needsRotationCheck: false + }); + } + } + + return rotations; +} + +// 회전 신뢰도 계산 +async function calculateRotationConfidence( + base64: string, + rotation: number +): Promise<number> { + try { + const rotatedBase64 = await rotateImageBase64(base64, rotation); + + // 이미지 품질 지표들을 분석하여 신뢰도 계산 + const cleanBase64 = rotatedBase64.replace(/^data:image\/[a-z]+;base64,/, ''); + const buffer = Buffer.from(cleanBase64, 'base64'); + + const stats = await sharp(buffer) + .greyscale() + .stats(); + + const contrast = stats.channels[0].max - stats.channels[0].min; + const sharpness = stats.channels[0].stdev; + + // 대비와 선명도를 기반으로 신뢰도 계산 + const confidence = Math.min((contrast + sharpness) / 400, 1.0); + + return confidence; + + } catch (error) { + console.warn('Failed to calculate rotation confidence:', error); + return 0.5; + } +} + +// 처리 방식 결정 +function shouldProcessAsWhole(pageCount: number, fileSize: number): boolean { + // 조건: + // 1. 파일 크기가 8MB 이하 + // 2. 페이지 수가 5개 이하 + // 3. 모든 페이지가 동일한 회전각을 갖는 경우 우선 고려 + + const maxSizeForWhole = 8 * 1024 * 1024; // 8MB + const maxPagesForWhole = 5; + + const sizeOk = fileSize <= maxSizeForWhole; + const pagesOk = pageCount <= maxPagesForWhole; + + console.log(`📋 Processing decision: size(${sizeOk}) + pages(${pagesOk}) = ${sizeOk && pagesOk ? 'WHOLE' : 'INDIVIDUAL'}`); + + return sizeOk && pagesOk; +} + +// 전체 PDF를 하나로 처리 +async function processAsWholePDF( + fileBuffer: Buffer, + pageRotations: PageRotationInfo[], + fileName: string +): Promise<{ + extractedTables: ExtractedRow[][]; + warnings: string[]; +}> { + console.log('🔄 Creating single combined image with corrected rotations...'); + + const warnings: string[] = []; + + try { + // 각 페이지를 올바른 회전으로 변환하여 이미지 배열 생성 + const pageImages: string[] = []; + + for (const pageInfo of pageRotations) { + console.log(` 📄 Processing page ${pageInfo.pageIndex + 1} with ${pageInfo.optimalRotation}° rotation...`); + + // 고해상도로 변환 + let pageBase64 = await convertPDFToImage(fileBuffer, pageInfo.pageIndex, 300); + + // 필요시 회전 적용 + if (pageInfo.optimalRotation !== 0) { + pageBase64 = await rotateImageBase64(pageBase64, pageInfo.optimalRotation); + warnings.push(`Page ${pageInfo.pageIndex + 1} was rotated ${pageInfo.optimalRotation}° for optimal results`); + } + + pageImages.push(pageBase64); + } + + // 모든 페이지를 하나의 긴 이미지로 합치기 + console.log('🖼️ Combining all pages into single image...'); + const combinedBase64 = await combineImagesVertically(pageImages); + + // 한 번의 OCR API 호출 + console.log('🌐 Single OCR API call for entire combined document...'); + const ocrResult = await callOCRAPI(combinedBase64, 'jpg', `combined_${fileName}`); + + // 결과 처리 + const reportNo = extractReportNo(ocrResult); + const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][]; + + const extractedTables: ExtractedRow[][] = rawTables.map(table => + table.map(row => ({ + ...row, + reportNo + })) + ); + + console.log(`✅ Combined processing complete: ${extractedTables.length} tables found`); + + return { + extractedTables, + warnings + }; + + } catch (error) { + console.error('❌ Whole PDF processing failed:', error); + throw error; + } +} + +// 페이지별 최적화 처리 +async function processPageByPageOptimized( + fileBuffer: Buffer, + pageRotations: PageRotationInfo[], + fileName: string +): Promise<{ + extractedTables: ExtractedRow[][]; + warnings: string[]; +}> { + console.log('📄 Processing pages individually with predetermined rotations...'); + + const allTables: ExtractedRow[][] = []; + const warnings: string[] = []; + + for (const pageInfo of pageRotations) { + try { + console.log(` 📄 Processing page ${pageInfo.pageIndex + 1} (${pageInfo.optimalRotation}° rotation)...`); + + // 고해상도로 변환 + let pageBase64 = await convertPDFToImage(fileBuffer, pageInfo.pageIndex, 300); + + // 미리 결정된 회전각 적용 + if (pageInfo.optimalRotation !== 0) { + pageBase64 = await rotateImageBase64(pageBase64, pageInfo.optimalRotation); + warnings.push(`Page ${pageInfo.pageIndex + 1} was rotated ${pageInfo.optimalRotation}° for optimal results`); + } + + // 단 1번의 OCR API 호출 (회전 테스트 없이) + console.log(` 🌐 OCR API call for page ${pageInfo.pageIndex + 1}...`); + const ocrResult = await callOCRAPI(pageBase64, 'jpg', `${fileName}_page_${pageInfo.pageIndex + 1}`); + + // 결과 처리 + const reportNo = extractReportNo(ocrResult); + const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][]; + + const pageTables: ExtractedRow[][] = rawTables.map(table => + table.map(row => ({ + ...row, + reportNo + })) + ); + + allTables.push(...pageTables); + + console.log(` ✅ Page ${pageInfo.pageIndex + 1}: ${pageTables.length} tables found`); + + } catch (error) { + console.error(` ❌ Error processing page ${pageInfo.pageIndex + 1}:`, error); + warnings.push(`Page ${pageInfo.pageIndex + 1} processing failed: ${error instanceof Error ? error.message : 'Unknown error'}`); + } + } + + return { + extractedTables: allTables, + warnings + }; +} + +// 이미지들을 세로로 합치기 +async function combineImagesVertically(base64Images: string[]): Promise<string> { + console.log(`🖼️ Combining ${base64Images.length} images vertically...`); + + try { + if (base64Images.length === 0) { + throw new Error('No images to combine'); + } + + if (base64Images.length === 1) { + return base64Images[0]; + } + + // 각 이미지를 Sharp 객체로 변환 + const imageBuffers = base64Images.map(base64 => { + const cleanBase64 = base64.replace(/^data:image\/[a-z]+;base64,/, ''); + return Buffer.from(cleanBase64, 'base64'); + }); + + // 첫 번째 이미지의 메타데이터 확인 + const firstImage = sharp(imageBuffers[0]); + const firstMetadata = await firstImage.metadata(); + + if (!firstMetadata.width || !firstMetadata.height) { + throw new Error('Invalid first image metadata'); + } + + const targetWidth = firstMetadata.width; + let totalHeight = firstMetadata.height; + + // 나머지 이미지들의 높이 계산 (같은 너비로 리사이즈) + const resizedBuffers = [imageBuffers[0]]; + + for (let i = 1; i < imageBuffers.length; i++) { + const img = sharp(imageBuffers[i]); + const metadata = await img.metadata(); + + if (metadata.width && metadata.height) { + const aspectRatio = metadata.height / metadata.width; + const newHeight = Math.round(targetWidth * aspectRatio); + + const resizedBuffer = await img + .resize(targetWidth, newHeight) + .jpeg({ quality: 90 }) + .toBuffer(); + + resizedBuffers.push(resizedBuffer); + totalHeight += newHeight; + + console.log(` 📏 Image ${i + 1}: ${metadata.width}x${metadata.height} → ${targetWidth}x${newHeight}`); + } + } + + console.log(` 📊 Combined size will be: ${targetWidth}x${totalHeight}`); + + // 빈 캔버스 생성 + const combinedImage = sharp({ + create: { + width: targetWidth, + height: totalHeight, + channels: 3, + background: { r: 255, g: 255, b: 255 } + } + }); + + // 이미지들을 세로로 배치 + const composite = []; + let currentTop = 0; + + for (const buffer of resizedBuffers) { + composite.push({ + input: buffer, + top: currentTop, + left: 0 + }); + + const metadata = await sharp(buffer).metadata(); + currentTop += metadata.height || 0; + } + + const combinedBuffer = await combinedImage + .composite(composite) + .jpeg({ quality: 90 }) + .toBuffer(); + + const combinedBase64 = combinedBuffer.toString('base64'); + + console.log(`✅ Images combined successfully: ${combinedBuffer.length} bytes`); + + return combinedBase64; + + } catch (error) { + console.error('❌ Error combining images:', error); + throw new Error(`Failed to combine images: ${error instanceof Error ? error.message : 'Unknown error'}`); + } +} + +// DB 저장 함수 (업데이트됨) async function saveToDatabase({ + userId, file, fileformat, processingTime, - bestRotation, - finalTables, - totalRows, - rotationResults, - imageEnhanced, - pdfConverted, + extractedTables, + pageRotations, + totalApiCalls, warnings }: { + userId: number | string, file: File; fileformat: string; processingTime: number; - bestRotation: number; - finalTables: ExtractedRow[][]; - totalRows: number; - rotationResults: (RotationTestResult & { score?: number })[]; - imageEnhanced: boolean; - pdfConverted: boolean; + extractedTables: ExtractedRow[][]; + pageRotations: PageRotationInfo[]; + totalApiCalls: number; warnings: string[]; }): Promise<string> { - + return await db.transaction(async (tx) => { - // 1. 세션 저장 + + // 1단계: 필터링된 행 수를 미리 계산 + const filteredTables = extractedTables.map(table => + table.filter(row => (!row.no || row.no.length < 10) && row.no !== "No." && row.weldingDate.length < 15 && row.jointType.length < 5) + ); + const totalTablesFiltered = filteredTables.length; + const totalRowsFiltered = filteredTables.reduce((sum, t) => sum + t.length, 0); + + // 2단계: 세션 저장 const sessionData: NewOcrSession = { fileName: file.name, fileSize: file.size, fileType: fileformat, processingTime, - bestRotation, - totalTables: finalTables.length, - totalRows, - imageEnhanced, - pdfConverted, + bestRotation: pageRotations[0]?.optimalRotation || 0, + totalTables: totalTablesFiltered, + totalRows: totalRowsFiltered, + imageEnhanced: fileformat === 'image', + pdfConverted: fileformat === 'pdf', success: true, - warnings: warnings.length > 0 ? warnings : null, + warnings: warnings.length ? warnings : null, }; - const [session] = await tx.insert(ocrSessions).values(sessionData).returning({ id: ocrSessions.id }); - const sessionId = session.id; + const [sessionTable] = await tx.insert(ocrSessions) + .values(sessionData) + .returning({ id: ocrSessions.id }); - // 2. 테이블들 저장 - const tableIds: string[] = []; - for (let tableIndex = 0; tableIndex < finalTables.length; tableIndex++) { - const table = finalTables[tableIndex]; - - const tableData: NewOcrTable = { + const sessionId = sessionTable.id; + + // 3단계: 테이블 & 행 저장 + for (let tableIndex = 0; tableIndex < filteredTables.length; tableIndex++) { + const table = filteredTables[tableIndex]; + + const [savedTable] = await tx.insert(ocrTables).values({ sessionId, tableIndex, rowCount: table.length, - }; - - const [savedTable] = await tx.insert(ocrTables).values(tableData).returning({ id: ocrTables.id }); - tableIds.push(savedTable.id); + }).returning({ id: ocrTables.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, + 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, + userId: userId.toString() })); - await tx.insert(ocrRows).values(rowsData); + console.log(`💾 Inserting ${rowsData.length} rows for table ${tableIndex}`); + + await tx.insert(ocrRows) + .values(rowsData) + .onConflictDoUpdate({ + target: [ocrRows.reportNo, ocrRows.no,ocrRows.no,ocrRows.tagNo,ocrRows.jointNo,ocrRows.jointType], // 🚀 변경: 두 컬럼만! + set: { + identificationNo: sql`EXCLUDED.identification_no`, + tagNo: sql`EXCLUDED.tag_no`, + jointNo: sql`EXCLUDED.joint_no`, + jointType: sql`EXCLUDED.joint_type`, + weldingDate: sql`EXCLUDED.welding_date`, + confidence: sql`EXCLUDED.confidence`, + sourceTable: sql`EXCLUDED.source_table`, + sourceRow: sql`EXCLUDED.source_row`, + userId: sql`EXCLUDED.user_id`, + }, + }); + + console.log(`✅ Successfully saved/updated ${rowsData.length} rows for table ${tableIndex}`); } } - // 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, - }; - }); + // 4단계: 최적화된 회전 시도 결과 저장 + const rotationAttemptsData: NewOcrRotationAttempt[] = pageRotations.map((pageInfo) => ({ + sessionId, + rotation: pageInfo.optimalRotation, + confidence: pageInfo.confidence.toString(), + tablesFound: 0, // 통합 처리로 인해 개별 페이지별 테이블 수는 미상 + textQuality: pageInfo.confidence.toString(), + keywordCount: 0, + score: pageInfo.confidence.toString(), + extractedRowsCount: 0, + })); if (rotationAttemptsData.length > 0) { await tx.insert(ocrRotationAttempts).values(rotationAttemptsData); } - console.log(`✅ Successfully saved session ${sessionId} with ${finalTables.length} tables and ${totalRows} rows`); + console.log(`✅ Successfully saved optimized session ${sessionId} with ${totalTablesFiltered} tables and ${totalRowsFiltered} rows (${totalApiCalls} API calls)`); return sessionId; }); } @@ -314,7 +807,7 @@ async function saveErrorToDatabase({ processingTime: number; error: string; }): Promise<string> { - + const sessionData: NewOcrSession = { fileName: file.name, fileSize: file.size, @@ -337,241 +830,150 @@ async function saveErrorToDatabase({ // 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'; - } -} + const table = ocrResult.images?.[0]?.tables?.[0]; + if (!table?.cells?.length) return 'UNKNOWN'; -// 여러 회전 각도 테스트 -async function tryMultipleRotations(base64: string, filename: string): Promise<(RotationTestResult & { score?: number })[]> { - const rotations = [0, 90, 180, 270]; - const results: RotationTestResult[] = []; + const target = table.cells.find( + (c: any) => c.rowIndex === 0 && c.columnIndex === 3 + ); + if (!target) return 'UNKNOWN'; - console.log('🔍 Testing rotations with server-side image processing...'); + const reportNo = cellText(target).replace(/\s+/g, ''); + return reportNo || 'UNKNOWN'; - for (const rotation of rotations) { - try { - console.log(` Testing ${rotation}° rotation...`); - - // 🔄 서버 사이드에서 실제 이미지 회전 - const rotatedBase64 = await rotateImageBase64(base64, rotation); + } catch (e) { + console.error('extractReportNo 오류:', e); + return 'UNKNOWN'; + } - // OCR API 호출 - const ocrResult = await callOCRAPI(rotatedBase64, 'jpg', `${rotation}_${filename}`); + function cellText(cell: any): string { + return (cell.cellTextLines ?? []) + .flatMap((l: any) => + (l.cellWords ?? []).map((w: any) => (w.inferText ?? '').trim()) + ) + .join(' '); + } +} - 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 - }; +// OCR API 호출 (기존과 동일) +async function callOCRAPI(base64: string, format: string, filename: string, rotation?: number): Promise<any> { + console.log('🌐 === OCR API CALL DEBUG ==='); + console.log(`📁 Filename: ${filename}`); + console.log(`🔄 Rotation: ${rotation || 0}°`); + console.log(`📊 Format: ${format}`); + + const validation = validateBase64Image(base64); + if (!validation.isValid) { + throw new Error(`Invalid base64 data: ${validation.error}`); + } - 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: [] - }); - } + console.log(`✅ Base64 validation passed: ${validation.size} bytes`); + + if (validation.size! > 52428800) { + throw new Error(`Image too large: ${validation.size} bytes (max: 50MB)`); } - return results.map(result => ({ ...result, score: 0 })); // score는 findBestRotation에서 계산 -} + if (validation.size! < 1000) { + throw new Error(`Image too small: ${validation.size} bytes (min: 1KB)`); + } -// 최적 회전 각도 찾기 -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 ocrSecretKey = process.env.OCR_SECRET_KEY; + if (!ocrSecretKey) { + throw new Error('OCR_SECRET_KEY environment variable is not set'); + } - const bestResult = scoredResults.reduce((best, current) => - current.score > best.score ? current : best - ); + let processedBase64: string; + try { + console.log('🔧 Normalizing image format...'); + processedBase64 = await normalizeImageFormat(base64); + console.log('✅ Image format normalized to JPEG'); + } catch (error) { + console.warn('⚠️ Format normalization failed, using original:', error); + processedBase64 = base64.replace(/^data:image\/[a-z]+;base64,/, ''); + } - console.log(`🏆 Winner: ${bestResult.rotation}° with score ${bestResult.score?.toFixed(3)}`); - - return bestResult; -} + const requestId = uuidv4(); + const timestamp = Math.floor(Date.now() / 1000); -// 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), + requestId, + timestamp, lang: "ko", images: [{ - format, + format: "jpg", url: null, - data: base64, + data: processedBase64, name: filename, ...(rotation !== undefined && rotation !== 0 && { rotation }) }], enableTableDetection: true }; - const response = await fetch( - "https://n4a9z5rb5r.apigw.ntruss.com/custom/v1/35937/41dcc5202686a37ee5f4fae85bd07f599e3627e9c00d33bf6e469f409fe0ca62/general", - { + console.log('📤 Request details:', { + requestId, + timestamp, + dataSize: processedBase64.length, + enableTableDetection: ocrBody.enableTableDetection, + hasRotation: rotation !== undefined && rotation !== 0 + }); + + const apiUrl = "https://n4a9z5rb5r.apigw.ntruss.com/custom/v1/35937/41dcc5202686a37ee5f4fae85bd07f599e3627e9c00d33bf6e469f409fe0ca62/general"; + + try { + console.log('🚀 Sending OCR request...'); + + const response = await fetch(apiUrl, { method: 'POST', headers: { 'Content-Type': 'application/json', - 'X-OCR-SECRET': process.env.OCR_SECRET_KEY || '', + 'X-OCR-SECRET': ocrSecretKey, }, body: JSON.stringify(ocrBody), - signal: AbortSignal.timeout(60000) // 60초 타임아웃 + signal: AbortSignal.timeout(60000) + }); + + console.log(`📨 Response: ${response.status} ${response.statusText}`); + + if (!response.ok) { + const errorText = await response.text(); + console.error(`❌ OCR API Error: ${errorText}`); + + let parsedError; + try { + parsedError = JSON.parse(errorText); + } catch { + parsedError = { message: errorText }; + } + + if (parsedError.code === "0011") { + throw new Error(`Image data size error: ${parsedError.message} (actual size: ${validation.size} bytes)`); + } else if (response.status === 401) { + throw new Error('Authentication failed - check OCR_SECRET_KEY'); + } else if (response.status === 403) { + throw new Error('Access forbidden - check API permissions'); + } else if (response.status === 429) { + throw new Error('Rate limit exceeded - too many requests'); + } + + throw new Error(`OCR API error: ${response.status} ${response.statusText} - ${errorText}`); } - ); - if (!response.ok) { - throw new Error(`OCR API error: ${response.status} ${response.statusText}`); - } + const result = await response.json(); + console.log('✅ OCR API Success!'); + console.log('📊 Response structure:', { + hasImages: !!result.images, + imageCount: result.images?.length || 0, + firstImageHasTables: !!result.images?.[0]?.tables, + firstImageTableCount: result.images?.[0]?.tables?.length || 0, + firstImageHasFields: !!result.images?.[0]?.fields, + firstImageFieldCount: result.images?.[0]?.fields?.length || 0 + }); + + return result; - return response.json(); + } catch (error) { + console.error('💥 OCR API Call Failed:', error); + throw error; + } }
\ No newline at end of file |