path: root/app/api/ocr/enhanced/route.ts

// 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();
}