import { NextRequest, NextResponse } from 'next/server'
import db from '@/db/db'
import { revisions, issueStages } from '@/db/schema/vendorDocu'
import { eq, sql } from 'drizzle-orm'
import { alias } from 'drizzle-orm/pg-core'
import { debugLog, debugError, debugSuccess } from '@/lib/debug-utils'

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url)
    const documentId = searchParams.get('documentId')

    debugLog('1. Input documentId:', documentId)

    if (!documentId) {
      debugLog('2. documentId is missing, returning 400')
      return NextResponse.json(
        { error: 'documentId is required' },
        { status: 400 }
      )
    }

    const parsedDocumentId = parseInt(documentId)
    debugLog('3. Parsed documentId:', parsedDocumentId)

    // 해당 document의 모든 issueStages와 연결된 revisions에서 최대 serialNo 조회
    const r = alias(revisions, 'r')
    const is = alias(issueStages, 'is')

    debugLog('4. Created aliases - r:', r, 'is:', is)

    const maxSerialResult = await db
      .select({
        maxSerial: sql<number>`
          COALESCE(MAX(CAST(${r.serialNo} AS INTEGER)), 0)
        `,
        maxRegisterSerial: sql<number>`
          COALESCE(MAX(CAST(${r.registerSerialNoMax} AS INTEGER)), 0)
        `
      })
      .from(r)
      .innerJoin(
        is,
        eq(r.issueStageId, is.id)
      )
      .where(eq(is.documentId, parsedDocumentId))

    debugLog('5. Query result:', maxSerialResult)
    debugLog('6. Query result length:', maxSerialResult.length)
    debugLog('7. First result item:', maxSerialResult[0])

    const maxSerialValue = maxSerialResult[0]?.maxSerial || 0
    const maxRegisterSerialValue = maxSerialResult[0]?.maxRegisterSerial || 0

    debugLog('8. maxSerial value:', maxSerialValue)
    debugLog('9. maxRegisterSerial value:', maxRegisterSerialValue)

    const maxSerialNo = Math.max(maxSerialValue, maxRegisterSerialValue)

    debugSuccess('10. Final maxSerialNo:', maxSerialNo)
    debugSuccess('11. Next serialNo:', maxSerialNo + 1)

    return NextResponse.json({
      maxSerialNo,
      nextSerialNo: maxSerialNo + 1,
      documentId: documentId,
      debug: {
        parsedDocumentId,
        queryResult: maxSerialResult,
        maxSerialValue,
        maxRegisterSerialValue
      }
    })
  } catch (error) {
    debugError('Error fetching max serial no:', error)
    debugError('Error stack:', error?.stack)
    return NextResponse.json(
      { error: 'Failed to fetch max serial number', details: error?.message },
      { status: 500 }
    )
  }
}