import { NextRequest, NextResponse } from 'next/server'
import ExcelJS from 'exceljs'
import db from '@/db/db'
import { ocrRows, users } from '@/db/schema'
import { eq, desc } from 'drizzle-orm'
import { PassThrough } from 'stream'

export async function GET(request: NextRequest) {
  try {
    // ExcelJS 워크북 생성 (스트리밍 모드)
    const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
      stream: new PassThrough(),
      useStyles: true,
      useSharedStrings: true
    })

    // 워크시트 추가
    const worksheet = workbook.addWorksheet('OCR Data', {
      properties: { defaultColWidth: 15 }
    })

    // 헤더 정의
    worksheet.columns = [
      { header: 'ID', key: 'id', width: 10 },
      { header: 'Table ID', key: 'tableId', width: 15 },
      { header: 'Session ID', key: 'sessionId', width: 20 },
      { header: 'Row Index', key: 'rowIndex', width: 12 },
      { header: 'Report No', key: 'reportNo', width: 15 },
      { header: 'File Name', key: 'fileName', width: 30 },
      { header: 'Inspection Date', key: 'inspectionDate', width: 15 },
      { header: 'No', key: 'no', width: 10 },
      { header: 'Identification No', key: 'identificationNo', width: 20 },
      { header: 'Tag No', key: 'tagNo', width: 15 },
      { header: 'Joint No', key: 'jointNo', width: 15 },
      { header: 'Joint Type', key: 'jointType', width: 15 },
      { header: 'Welding Date', key: 'weldingDate', width: 15 },
      { header: 'Confidence', key: 'confidence', width: 12 },
      { header: 'Source Table', key: 'sourceTable', width: 15 },
      { header: 'Source Row', key: 'sourceRow', width: 12 },
      { header: 'User Name', key: 'userName', width: 20 },
      { header: 'User Email', key: 'userEmail', width: 25 },
      { header: 'Created At', key: 'createdAt', width: 20 },
    ]

    // 헤더 스타일 적용
    worksheet.getRow(1).font = { bold: true }
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' }
    }

    // 청크 단위로 데이터 가져와서 스트리밍
    const CHUNK_SIZE = 1000
    let offset = 0
    let hasMore = true

    while (hasMore) {
      const chunk = await db
        .select({
          id: ocrRows.id,
          tableId: ocrRows.tableId,
          sessionId: ocrRows.sessionId,
          rowIndex: ocrRows.rowIndex,
          reportNo: ocrRows.reportNo,
          fileName: ocrRows.fileName,
          inspectionDate: ocrRows.inspectionDate,
          no: ocrRows.no,
          identificationNo: ocrRows.identificationNo,
          tagNo: ocrRows.tagNo,
          jointNo: ocrRows.jointNo,
          jointType: ocrRows.jointType,
          weldingDate: ocrRows.weldingDate,
          confidence: ocrRows.confidence,
          sourceTable: ocrRows.sourceTable,
          sourceRow: ocrRows.sourceRow,
          userId: ocrRows.userId,
          createdAt: ocrRows.createdAt,
          userName: users.name,
          userEmail: users.email,
        })
        .from(ocrRows)
        .leftJoin(users, eq(ocrRows.userId, users.id))
        .orderBy(desc(ocrRows.createdAt))
        .limit(CHUNK_SIZE)
        .offset(offset)

      if (chunk.length === 0) {
        hasMore = false
      } else {
        // 각 행을 워크시트에 추가
        for (const row of chunk) {
          worksheet.addRow({
            ...row,
            createdAt: row.createdAt ? new Date(row.createdAt).toLocaleString('ko-KR') : '',
            inspectionDate: row.inspectionDate ? new Date(row.inspectionDate).toLocaleDateString('ko-KR') : '',
            weldingDate: row.weldingDate ? new Date(row.weldingDate).toLocaleDateString('ko-KR') : '',
          }).commit() // commit()으로 메모리 즉시 해제
        }

        offset += chunk.length

        if (chunk.length < CHUNK_SIZE) {
          hasMore = false
        }
      }
    }

    // 워크시트 커밋
    worksheet.commit()

    // 스트림을 버퍼로 변환
    const buffer = await streamToBuffer(workbook)

    // 응답 헤더 설정
    const headers = new Headers()
    headers.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    headers.set('Content-Disposition', `attachment; filename="OCR_Export_${new Date().toISOString().split('T')[0]}.xlsx"`)

    return new NextResponse(buffer, { headers })

  } catch (error) {
    console.error('Export error:', error)
    return NextResponse.json({ error: 'Export failed' }, { status: 500 })
  }
}

// 스트림을 버퍼로 변환하는 헬퍼 함수
async function streamToBuffer(workbook: ExcelJS.stream.xlsx.WorkbookWriter): Promise<Buffer> {
  return new Promise((resolve, reject) => {
    const chunks: Buffer[] = []
    
    const stream = (workbook as any).stream as PassThrough
    
    stream.on('data', (chunk: Buffer) => chunks.push(chunk))
    stream.on('end', () => resolve(Buffer.concat(chunks)))
    stream.on('error', reject)
    
    workbook.commit()
  })
}