// ============================================================================
// app/api/ocr/enhanced/route.ts
// 최적화된 OCR API - 통합 처리로 API 호출 최소화
// ============================================================================

import { NextRequest, NextResponse } from 'next/server';
import { v4 as uuidv4 } from 'uuid';
import db from '@/db/db';
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,
  normalizeImageFormat,
  validateBase64Image,
  getPDFPageCount,
  detectTextOrientation
} from '../utils/imageRotation';
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;
  needsRotationCheck: boolean;
}

interface OptimizedProcessingResult {
  success: boolean;
  sessionId: string;
  extractedTables: ExtractedRow[][];
  totalApiCalls: number;
  processingTime: number;
  pageRotations: PageRotationInfo[];
  warnings: string[];
}

interface ProcessingResult {
  success: boolean;
  sessionId: string;
  metadata: {
    totalTables: number;
    totalRows: number;
    processingTime: number;
    fileName: string;
    fileSize: number;
    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[];
}

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 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();
    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 processingResult: OptimizedProcessingResult;

    if (fileformat === 'pdf') {
      console.log('📄 Processing PDF with optimized approach...');
      processingResult = await processPDFOptimized(fileBuffer, file.name);
    } else {
      console.log('🖼️ Processing single image with optimized approach...');
      processingResult = await processImageOptimized(fileBuffer, file.name);
    }

    const processingTime = Date.now() - startTime;

    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,
      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}`);

    const result: ProcessingResult = {
      success: true,
      sessionId,
      metadata: {
        totalTables: processingResult.extractedTables.length,
        totalRows: processingResult.extractedTables.reduce((sum, table) => sum + table.length, 0),
        processingTime,
        fileName: file.name,
        fileSize: file.size,
        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, ...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 && file) {
      try {
        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,
      error: error instanceof Error ? error.message : 'Unknown error occurred',
      metadata: {
        processingTime,
        totalTables: 0,
        totalRows: 0,
        fileName: file?.name || '',
        fileSize: file?.size || 0,
        bestRotation: 0,
        imageEnhanced: false,
        pdfConverted: false,
        totalApiCalls: 0
      }
    }, { status: 500 });
  }
}

// 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 inspectionDate = extractinspectionDate(ocrResult);
    const analysis = analyzeOCRQuality(ocrResult);
    const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][];

    const extractedTables: ExtractedRow[][] = rawTables.map(table =>
      table.map(row => ({
        ...row,
        reportNo,
        inspectionDate
      }))
    );

    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 inspectionDate = extractinspectionDate(ocrResult);
    const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][];

    const extractedTables: ExtractedRow[][] = rawTables.map(table =>
      table.map(row => ({
        ...row,
        reportNo,
        inspectionDate
      }))
    );

    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 inspectionDate = extractinspectionDate(ocrResult);
      const rawTables = await extractTablesFromOCR(ocrResult) as BaseExtractedRow[][];

      const pageTables: ExtractedRow[][] = rawTables.map(table =>
        table.map(row => ({
          ...row,
          reportNo,
          inspectionDate
        }))
      );

      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,
  extractedTables,
  pageRotations,
  totalApiCalls,
  warnings
}: {
  userId: number | string,
  file: File;
  fileformat: string;
  processingTime: number;
  extractedTables: ExtractedRow[][];
  pageRotations: PageRotationInfo[];
  totalApiCalls: number;
  warnings: string[];
}): Promise<string> {

  return await db.transaction(async (tx) => {

    // 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: pageRotations[0]?.optimalRotation || 0,
      totalTables: totalTablesFiltered,
      totalRows: totalRowsFiltered,
      imageEnhanced: fileformat === 'image',
      pdfConverted: fileformat === 'pdf',
      success: true,
      warnings: warnings.length ? warnings : null,
    };

    const [sessionTable] = await tx.insert(ocrSessions)
      .values(sessionData)
      .returning({ id: ocrSessions.id });

    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,
      }).returning({ id: ocrTables.id });

      if (table.length > 0) {
        const rowsData: NewOcrRow[] = table.map((row, rowIndex) => ({
          tableId: savedTable.id,
          fileName:file.name,
          sessionId,
          rowIndex,
          reportNo: row.reportNo ?? null,
          no: row.no ?? null,
          identificationNo: row.identificationNo ?? null,
          inspectionDate: row.inspectionDate ?? 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()
        }));

        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[] = 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 optimized session ${sessionId} with ${totalTablesFiltered} tables and ${totalRowsFiltered} rows (${totalApiCalls} API calls)`);
    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 {
    const table = ocrResult.images?.[0]?.tables?.[0];
    if (!table?.cells?.length) return 'UNKNOWN';

    const target = table.cells.find(
      (c: any) => c.rowIndex === 0 && c.columnIndex === 3
    );
    if (!target) return 'UNKNOWN';

    const reportNo = cellText(target).replace(/\s+/g, '');
    return reportNo || 'UNKNOWN';

  } catch (e) {
    console.error('extractReportNo 오류:', e);
    return 'UNKNOWN';
  }

  function cellText(cell: any): string {
    return (cell.cellTextLines ?? [])
      .flatMap((l: any) =>
        (l.cellWords ?? []).map((w: any) => (w.inferText ?? '').trim())
      )
      .join(' ');
  }
}

function extractinspectionDate(ocrResult: any): string {
  try {
    const table = ocrResult.images?.[0]?.tables?.[0];
    if (!table?.cells?.length) return 'UNKNOWN';

    const target = table.cells.find(
      (c: any) => c.rowIndex === 4 && c.columnIndex === 3
    );
    if (!target) return 'UNKNOWN';

    const reportNo = cellText(target).replace(/\s+/g, '');
    return reportNo || 'UNKNOWN';

  } catch (e) {
    console.error('extractinspectionDate 오류:', e);
    return 'UNKNOWN';
  }

  function cellText(cell: any): string {
    return (cell.cellTextLines ?? [])
      .flatMap((l: any) =>
        (l.cellWords ?? []).map((w: any) => (w.inferText ?? '').trim())
      )
      .join(' ');
  }
}

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

  console.log(`✅ Base64 validation passed: ${validation.size} bytes`);

  if (validation.size! > 52428800) {
    throw new Error(`Image too large: ${validation.size} bytes (max: 50MB)`);
  }

  if (validation.size! < 1000) {
    throw new Error(`Image too small: ${validation.size} bytes (min: 1KB)`);
  }

  const ocrSecretKey = process.env.OCR_SECRET_KEY;
  if (!ocrSecretKey) {
    throw new Error('OCR_SECRET_KEY environment variable is not set');
  }

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

  const requestId = uuidv4();
  const timestamp = Math.floor(Date.now() / 1000);

  const ocrBody = {
    version: "V2",
    requestId,
    timestamp,
    lang: "ko",
    images: [{
      format: "jpg",
      url: null,
      data: processedBase64,
      name: filename,
      ...(rotation !== undefined && rotation !== 0 && { rotation })
    }],
    enableTableDetection: true
  };

  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': ocrSecretKey,
      },
      body: JSON.stringify(ocrBody),
      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}`);
    }

    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;

  } catch (error) {
    console.error('💥 OCR API Call Failed:', error);
    throw error;
  }
}