import { NextRequest, NextResponse } from 'next/server'
import db from '@/db/db'
import { revisions, issueStages } from '@/db/schema'
import { eq, and, sql, desc } from 'drizzle-orm'

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url)
    const documentId = searchParams.get('documentId')
    
    if (!documentId) {
      return NextResponse.json(
        { error: 'documentId is required' },
        { status: 400 }
      )
    }

    // 해당 document의 모든 issueStages와 연결된 revisions에서 최대 serialNo 조회
    const maxSerialResult = await db
      .select({
        maxSerialNo: sql<number>`
          GREATEST(
            COALESCE(MAX(CAST(r.serial_no AS INTEGER)), 0),
            COALESCE(MAX(CAST(r.register_serial_no_max AS INTEGER)), 0)
          )
        `.as('max_serial_no')
      })
      .from(revisions.as('r'))
      .innerJoin(
        issueStages.as('is'),
        eq(revisions.issueStageId, issueStages.id)
      )
      .where(eq(issueStages.documentId, parseInt(documentId)))

    const maxSerialNo = maxSerialResult[0]?.maxSerialNo || 0
    
    return NextResponse.json({ 
      maxSerialNo,
      nextSerialNo: maxSerialNo + 1,
      documentId: documentId
    })
  } catch (error) {
    console.error('Error fetching max serial no:', error)
    return NextResponse.json(
      { error: 'Failed to fetch max serial number' },
      { status: 500 }
    )
  }
}