1 files changed, 49 insertions, 0 deletions
diff --git a/app/api/revisions/max-serial-no/route.ts b/app/api/revisions/max-serial-no/route.ts
new file mode 100644
index 00000000..b202956a
--- /dev/null
+++ b/app/api/revisions/max-serial-no/route.ts
@@ -0,0 +1,49 @@
+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 }
+    )
+  }
+}
+\ No newline at end of file