summaryrefslogtreecommitdiff
path: root/app/api
diff options
context:
space:
mode:
Diffstat (limited to 'app/api')
-rw-r--r--app/api/auth/[...nextauth]/route.ts258
-rw-r--r--app/api/auth/first-auth/route.ts112
-rw-r--r--app/api/auth/send-sms/route.ts20
-rw-r--r--app/api/auth/verify-mfa/route.ts21
-rw-r--r--app/api/files/[...path]/route.ts244
-rw-r--r--app/api/ocr/utils/tableExtraction.ts648
-rw-r--r--app/api/vendors/route.ts248
7 files changed, 1354 insertions, 197 deletions
diff --git a/app/api/auth/[...nextauth]/route.ts b/app/api/auth/[...nextauth]/route.ts
index f5d49f77..2b168746 100644
--- a/app/api/auth/[...nextauth]/route.ts
+++ b/app/api/auth/[...nextauth]/route.ts
@@ -1,5 +1,4 @@
-// Updated NextAuth configuration with dynamic session timeout from database
-
+// auth/config.ts - 업데이트된 NextAuth 설정
import NextAuth, {
NextAuthOptions,
Session,
@@ -9,15 +8,18 @@ import NextAuth, {
import { JWT } from "next-auth/jwt"
import CredentialsProvider from 'next-auth/providers/credentials'
import { SAMLProvider } from './saml/provider'
-import { getUserById } from '@/lib/users/repository'
+import { getUserByEmail, getUserById } from '@/lib/users/repository'
import { authenticateWithSGips, verifyExternalCredentials } from '@/lib/users/auth/verifyCredentails'
import { verifyOtpTemp } from '@/lib/users/verifyOtp'
import { getSecuritySettings } from '@/lib/password-policy/service'
+import { verifySmsToken } from '@/lib/users/auth/passwordUtil'
+import { SessionRepository } from '@/lib/users/session/repository'
+import { loginSessions } from '@/db/schema'
// 인증 방식 타입 정의
type AuthMethod = 'otp' | 'email' | 'sgips' | 'saml'
-// 모듈 보강 선언 (인증 방식 추가)
+// 모듈 보강 선언 (기존과 동일)
declare module "next-auth" {
interface Session {
user: {
@@ -30,7 +32,8 @@ declare module "next-auth" {
domain?: string | null
reAuthTime?: number | null
authMethod?: AuthMethod
- sessionExpiredAt?: number | null // 세션 만료 시간 추가
+ sessionExpiredAt?: number | null
+ dbSessionId?: string | null // DB 세션 ID 추가
}
}
@@ -42,6 +45,7 @@ declare module "next-auth" {
domain?: string | null
reAuthTime?: number | null
authMethod?: AuthMethod
+ dbSessionId?: string | null
}
}
@@ -54,11 +58,12 @@ declare module "next-auth/jwt" {
domain?: string | null
reAuthTime?: number | null
authMethod?: AuthMethod
- sessionExpiredAt?: number | null // 세션 만료 시간 추가
+ sessionExpiredAt?: number | null
+ dbSessionId?: string | null
}
}
-// 보안 설정 캐시 (성능 최적화)
+// 보안 설정 캐시 (기존과 동일)
let securitySettingsCache: {
data: any | null
lastFetch: number
@@ -69,7 +74,6 @@ let securitySettingsCache: {
ttl: 5 * 60 * 1000 // 5분 캐시
}
-// 보안 설정을 가져오는 함수 (캐시 적용)
async function getCachedSecuritySettings() {
const now = Date.now()
@@ -80,7 +84,6 @@ async function getCachedSecuritySettings() {
securitySettingsCache.lastFetch = now
} catch (error) {
console.error('Failed to fetch security settings:', error)
- // 기본값 사용
securitySettingsCache.data = {
sessionTimeoutMinutes: 480 // 8시간 기본값
}
@@ -90,11 +93,28 @@ async function getCachedSecuritySettings() {
return securitySettingsCache.data
}
+// 클라이언트 IP 추출 헬퍼
+function getClientIP(req: any): string {
+ const forwarded = req.headers['x-forwarded-for']
+ const realIP = req.headers['x-real-ip']
+
+ if (forwarded) {
+ return forwarded.split(',')[0].trim()
+ }
+
+ if (realIP) {
+ return realIP
+ }
+
+ return req.ip || req.connection?.remoteAddress || '127.0.0.1'
+}
+
export const authOptions: NextAuthOptions = {
providers: [
- // OTP provider
+ // OTP 로그인 (기존 유지)
CredentialsProvider({
- name: 'Credentials',
+ id: 'credentials-otp',
+ name: 'OTP',
credentials: {
email: { label: 'Email', type: 'text' },
code: { label: 'OTP code', type: 'text' },
@@ -107,9 +127,7 @@ export const authOptions: NextAuthOptions = {
return null
}
- // 보안 설정에서 세션 타임아웃 가져오기
const securitySettings = await getCachedSecuritySettings()
- const sessionTimeoutMs = securitySettings.sessionTimeoutMinutes * 60 * 1000
const reAuthTime = Date.now()
return {
@@ -125,61 +143,101 @@ export const authOptions: NextAuthOptions = {
}
},
}),
-
- // ID/패스워드 provider (S-Gips와 일반 이메일 구분)
+
+ // MFA 완료 후 최종 인증 (DB 연동 버전)
CredentialsProvider({
- id: 'credentials-password',
- name: 'Username Password',
+ id: 'credentials-mfa',
+ name: 'MFA Verification',
credentials: {
- username: { label: "Username", type: "text" },
- password: { label: "Password", type: "password" },
- provider: { label: "Provider", type: "text" },
+ userId: { label: 'User ID', type: 'text' },
+ smsToken: { label: 'SMS Token', type: 'text' },
+ tempAuthKey: { label: 'Temp Auth Key', type: 'text' },
},
async authorize(credentials, req) {
- if (!credentials?.username || !credentials?.password) {
- return null;
+ if (!credentials?.userId || !credentials?.smsToken || !credentials?.tempAuthKey) {
+ console.error('MFA credentials missing')
+ return null
}
-
+
try {
- let authResult;
- const isSSgips = credentials.provider === 'sgips';
-
- if (isSSgips) {
- authResult = await authenticateWithSGips(
- credentials.username,
- credentials.password
- );
- } else {
- authResult = await verifyExternalCredentials(
- credentials.username,
- credentials.password
- );
+ // DB에서 임시 인증 정보 확인
+ const tempAuth = await SessionRepository.getTempAuthSession(credentials.tempAuthKey)
+ if (!tempAuth || tempAuth.userId !== credentials.userId) {
+ console.error('Temp auth expired or not found')
+ return null
}
-
- if (authResult.success && authResult.user) {
- return {
- id: authResult.user.id,
- name: authResult.user.name,
- email: authResult.user.email,
- imageUrl: authResult.user.imageUrl ?? null,
- companyId: authResult.user.companyId,
- techCompanyId: authResult.user.techCompanyId,
- domain: authResult.user.domain,
- reAuthTime: Date.now(),
- authMethod: isSSgips ? 'sgips' as AuthMethod : 'email' as AuthMethod,
- };
+
+ // SMS 토큰 검증
+ const smsVerificationResult = await verifySmsToken(Number(credentials.userId), credentials.smsToken)
+ if (!smsVerificationResult || !smsVerificationResult.success) {
+ console.error('SMS token verification failed')
+ return null
}
- return null;
+ // 사용자 정보 조회
+ const user = await getUserById(Number(credentials.userId))
+ if (!user) {
+ console.error('User not found after MFA verification')
+ return null
+ }
+
+ // 임시 인증 정보를 사용됨으로 표시
+ await SessionRepository.markTempAuthSessionAsUsed(credentials.tempAuthKey)
+
+ // 보안 설정 및 세션 정보 설정
+ const securitySettings = await getCachedSecuritySettings()
+ const reAuthTime = Date.now()
+ const sessionExpiredAt = new Date(reAuthTime + (securitySettings.sessionTimeoutMinutes * 60 * 1000))
+
+ // DB에 로그인 세션 생성
+ const ipAddress = getClientIP(req)
+ const userAgent = req.headers?.['user-agent']
+ const dbSession = await SessionRepository.createLoginSession({
+ userId: String(user.id),
+ ipAddress,
+ userAgent,
+ authMethod: tempAuth.authMethod,
+ sessionExpiredAt,
+ })
+
+ console.log(`MFA completed for user ${user.email} (${tempAuth.authMethod})`)
+
+ return {
+ id: String(user.id),
+ email: user.email,
+ imageUrl: user.imageUrl ?? null,
+ name: user.name,
+ companyId: user.companyId,
+ techCompanyId: user.techCompanyId as number | undefined,
+ domain: user.domain,
+ reAuthTime,
+ authMethod: tempAuth.authMethod as AuthMethod,
+ dbSessionId: dbSession.id,
+ }
+
} catch (error) {
- console.error("Authentication error:", error);
- return null;
+ console.error('MFA authorization error:', error)
+ return null
}
+ },
+ }),
+
+ // 1차 인증용 프로바이더 (기존 유지)
+ CredentialsProvider({
+ id: 'credentials-first-auth',
+ name: 'First Factor Authentication',
+ credentials: {
+ username: { label: "Username", type: "text" },
+ password: { label: "Password", type: "password" },
+ provider: { label: "Provider", type: "text" },
+ },
+ async authorize(credentials, req) {
+ return null
}
}),
- // SAML Provider
+ // SAML Provider (기존 유지)
SAMLProvider({
id: "credentials-saml",
name: "SAML SSO",
@@ -199,18 +257,15 @@ export const authOptions: NextAuthOptions = {
session: {
strategy: 'jwt',
- // JWT 기본 maxAge는 30일로 설정하되, 실제 세션 만료는 콜백에서 처리
maxAge: 30 * 24 * 60 * 60, // 30일
},
callbacks: {
- // JWT 콜백 - 세션 타임아웃 설정 (만료 체크는 session 콜백에서)
async jwt({ token, user, account, trigger, session }) {
- // 보안 설정 가져오기
const securitySettings = await getCachedSecuritySettings()
const sessionTimeoutMs = securitySettings.sessionTimeoutMinutes * 60 * 1000
- // 최초 로그인 시
+ // 최초 로그인 시 (MFA 완료 후)
if (user) {
const reAuthTime = Date.now()
token.id = user.id
@@ -223,34 +278,44 @@ export const authOptions: NextAuthOptions = {
token.reAuthTime = reAuthTime
token.authMethod = user.authMethod
token.sessionExpiredAt = reAuthTime + sessionTimeoutMs
+ token.dbSessionId = user.dbSessionId
}
- // 인증 방식 결정 (account 정보 기반)
- if (account && !token.authMethod) {
+ // SAML 인증 시 DB 세션 생성
+ if (account && account.provider === 'credentials-saml' && token.id) {
const reAuthTime = Date.now()
- if (account.provider === 'credentials-saml') {
+ const sessionExpiredAt = new Date(reAuthTime + sessionTimeoutMs)
+
+ try {
+ const dbSession = await SessionRepository.createLoginSession({
+ userId: token.id,
+ ipAddress: '0.0.0.0', // SAML의 경우 IP 추적 제한적
+ authMethod: 'saml',
+ sessionExpiredAt,
+ })
+
token.authMethod = 'saml'
token.reAuthTime = reAuthTime
token.sessionExpiredAt = reAuthTime + sessionTimeoutMs
- } else if (account.provider === 'credentials') {
- // OTP는 이미 user.authMethod에서 설정됨
- if (!token.sessionExpiredAt) {
- token.sessionExpiredAt = (token.reAuthTime || Date.now()) + sessionTimeoutMs
- }
- } else if (account.provider === 'credentials-password') {
- // credentials-password는 이미 user.authMethod에서 설정됨
- if (!token.sessionExpiredAt) {
- token.sessionExpiredAt = (token.reAuthTime || Date.now()) + sessionTimeoutMs
- }
+ token.dbSessionId = dbSession.id
+ } catch (error) {
+ console.error('Failed to create SAML session:', error)
}
}
- // 세션 업데이트 시 (재인증 시간 업데이트)
+ // 세션 업데이트 시
if (trigger === "update" && session) {
if (session.reAuthTime !== undefined) {
token.reAuthTime = session.reAuthTime
- // 재인증 시간 업데이트 시 세션 만료 시간도 연장
token.sessionExpiredAt = session.reAuthTime + sessionTimeoutMs
+
+ // DB 세션 업데이트
+ if (token.dbSessionId) {
+ await SessionRepository.updateLoginSession(token.dbSessionId, {
+ lastActivityAt: new Date(),
+ sessionExpiredAt: new Date(session.reAuthTime + sessionTimeoutMs)
+ })
+ }
}
if (session.user) {
@@ -263,14 +328,18 @@ export const authOptions: NextAuthOptions = {
return token
},
- // Session 콜백 - 세션 만료 체크 및 정보 포함
async session({ session, token }: { session: Session; token: JWT }) {
// 세션 만료 체크
if (token.sessionExpiredAt && Date.now() > token.sessionExpiredAt) {
console.log(`Session expired for user ${token.email}. Expired at: ${new Date(token.sessionExpiredAt)}`)
- // 만료된 세션 처리 - 빈 세션 반환하여 로그아웃 유도
+
+ // DB 세션 만료 처리
+ if (token.dbSessionId) {
+ await SessionRepository.logoutSession(token.dbSessionId)
+ }
+
return {
- expires: new Date(0).toISOString(), // 즉시 만료
+ expires: new Date(0).toISOString(),
user: null as any
}
}
@@ -287,12 +356,12 @@ export const authOptions: NextAuthOptions = {
reAuthTime: token.reAuthTime as number | null,
authMethod: token.authMethod as AuthMethod,
sessionExpiredAt: token.sessionExpiredAt as number | null,
+ dbSessionId: token.dbSessionId as string | null,
}
}
return session
},
- // Redirect 콜백
async redirect({ url, baseUrl }) {
if (url.startsWith("/")) {
return `${baseUrl}${url}`;
@@ -309,18 +378,45 @@ export const authOptions: NextAuthOptions = {
error: '/auth/error',
},
- // 디버깅을 위한 이벤트 로깅
events: {
async signIn({ user, account, profile }) {
const securitySettings = await getCachedSecuritySettings()
console.log(`User ${user.email} signed in via ${account?.provider} (authMethod: ${user.authMethod}), session timeout: ${securitySettings.sessionTimeoutMinutes} minutes`);
+
+ // 이미 MFA에서 DB 세션이 생성된 경우가 아니라면 여기서 생성
+ if (account?.provider !== 'credentials-mfa' && user.id) {
+ try {
+ // 기존 활성 세션 확인
+ const existingSession = await SessionRepository.getActiveSessionByUserId(user.id)
+ if (!existingSession) {
+ const sessionExpiredAt = new Date(Date.now() + (securitySettings.sessionTimeoutMinutes * 60 * 1000))
+
+ await SessionRepository.createLoginSession({
+ userId: user.id,
+ ipAddress: '0.0.0.0', // signIn 이벤트에서는 IP 접근 제한적
+ authMethod: user.authMethod || 'unknown',
+ sessionExpiredAt,
+ })
+ }
+ } catch (error) {
+ console.error('Failed to create session in signIn event:', error)
+ }
+ }
},
+
async signOut({ session, token }) {
console.log(`User ${session?.user?.email || token?.email} signed out`);
+
+ // DB에서 세션 로그아웃 처리
+ const userId = session?.user?.id || token?.id
+ const dbSessionId = session?.user?.dbSessionId || token?.dbSessionId
+
+ if (dbSessionId) {
+ await SessionRepository.logoutSession(dbSessionId)
+ } else if (userId) {
+ // dbSessionId가 없는 경우 사용자의 모든 활성 세션 로그아웃
+ await SessionRepository.logoutAllUserSessions(userId)
+ }
}
}
}
-
-const handler = NextAuth(authOptions)
-export { handler as GET, handler as POST }
-
diff --git a/app/api/auth/first-auth/route.ts b/app/api/auth/first-auth/route.ts
new file mode 100644
index 00000000..18f44904
--- /dev/null
+++ b/app/api/auth/first-auth/route.ts
@@ -0,0 +1,112 @@
+// /api/auth/first-auth/route.ts
+// 1차 인증 처리 API 엔드포인트
+
+import { NextRequest, NextResponse } from 'next/server'
+import { authHelpers } from '../[...nextauth]/route'
+
+// 요청 데이터 타입
+interface FirstAuthRequest {
+ username: string
+ password: string
+ provider: 'email' | 'sgips'
+}
+
+// 응답 데이터 타입
+interface FirstAuthResponse {
+ success: boolean
+ tempAuthKey?: string
+ userId?: string
+ email?: string
+ error?: string
+}
+
+export async function POST(request: NextRequest): Promise<NextResponse<FirstAuthResponse>> {
+ try {
+ // 요청 데이터 파싱
+ const body: FirstAuthRequest = await request.json()
+ const { username, password, provider } = body
+
+ // 입력 검증
+ if (!username || !password || !provider) {
+ return NextResponse.json(
+ {
+ success: false,
+ error: '필수 입력값이 누락되었습니다.'
+ },
+ { status: 400 }
+ )
+ }
+
+ if (!['email', 'sgips'].includes(provider)) {
+ return NextResponse.json(
+ {
+ success: false,
+ error: '지원하지 않는 인증 방식입니다.'
+ },
+ { status: 400 }
+ )
+ }
+
+ // 레이트 리미팅 (옵셔널)
+ // const rateLimitResult = await rateLimit.check(request, `first-auth:${username}`)
+ // if (!rateLimitResult.success) {
+ // return NextResponse.json(
+ // {
+ // success: false,
+ // error: '너무 많은 시도입니다. 잠시 후 다시 시도해주세요.'
+ // },
+ // { status: 429 }
+ // )
+ // }
+
+ // 1차 인증 수행
+ const authResult = await authHelpers.performFirstAuth(username, password, provider)
+
+ if (!authResult.success) {
+ // 인증 실패 응답
+ let errorMessage = '인증에 실패했습니다.'
+
+ if (provider === 'sgips') {
+ errorMessage = 'S-Gips 계정 정보가 올바르지 않습니다.'
+ } else {
+ errorMessage = '이메일 또는 비밀번호가 올바르지 않습니다.'
+ }
+
+ return NextResponse.json(
+ {
+ success: false,
+ error: authResult.error || errorMessage
+ },
+ { status: 401 }
+ )
+ }
+
+ // 1차 인증 성공 응답
+ return NextResponse.json({
+ success: true,
+ tempAuthKey: authResult.tempAuthKey,
+ userId: authResult.userId,
+ email: authResult.email
+ })
+
+ } catch (error) {
+ console.error('First auth API error:', error)
+
+ // 에러 응답
+ return NextResponse.json(
+ {
+ success: false,
+ error: '서버 오류가 발생했습니다. 잠시 후 다시 시도해주세요.'
+ },
+ { status: 500 }
+ )
+ }
+}
+
+// GET 요청은 지원하지 않음
+export async function GET() {
+ return NextResponse.json(
+ { error: 'Method not allowed' },
+ { status: 405 }
+ )
+} \ No newline at end of file
diff --git a/app/api/auth/send-sms/route.ts b/app/api/auth/send-sms/route.ts
index 3d51d445..6b9eb114 100644
--- a/app/api/auth/send-sms/route.ts
+++ b/app/api/auth/send-sms/route.ts
@@ -4,7 +4,7 @@ import { NextRequest, NextResponse } from 'next/server';
import { z } from 'zod';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';
-import { getUserById } from '@/lib/users/repository';
+import { getUserByEmail, getUserById } from '@/lib/users/repository';
import { generateAndSendSmsToken } from '@/lib/users/auth/passwordUtil';
const sendSmsSchema = z.object({
@@ -13,20 +13,14 @@ const sendSmsSchema = z.object({
export async function POST(request: NextRequest) {
try {
- // 세션 확인
- const session = await getServerSession(authOptions);
- if (!session?.user?.id) {
- return NextResponse.json(
- { error: '인증이 필요합니다' },
- { status: 401 }
- );
- }
const body = await request.json();
const { userId } = sendSmsSchema.parse(body);
+ console.log(userId, "userId")
+
// 본인 확인
- if (session.user.id !== userId) {
+ if (!userId) {
return NextResponse.json(
{ error: '권한이 없습니다' },
{ status: 403 }
@@ -42,8 +36,12 @@ export async function POST(request: NextRequest) {
);
}
+ console.log(user, "user")
+
+
+
// SMS 전송
- const result = await generateAndSendSmsToken(parseInt(userId), user.phone);
+ const result = await generateAndSendSmsToken(Number(userId), user.phone);
if (result.success) {
return NextResponse.json({
diff --git a/app/api/auth/verify-mfa/route.ts b/app/api/auth/verify-mfa/route.ts
index f9d1b51e..dea06164 100644
--- a/app/api/auth/verify-mfa/route.ts
+++ b/app/api/auth/verify-mfa/route.ts
@@ -5,6 +5,7 @@ import { z } from 'zod';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';
import { verifySmsToken } from '@/lib/users/auth/passwordUtil';
+import { getUserByEmail } from '@/lib/users/repository';
const verifyMfaSchema = z.object({
userId: z.string(),
@@ -25,16 +26,32 @@ export async function POST(request: NextRequest) {
const body = await request.json();
const { userId, token } = verifyMfaSchema.parse(body);
+
+ console.log(userId)
+
+
+
// 본인 확인
- if (session.user.id !== userId) {
+ if (session.user.email !== userId) {
return NextResponse.json(
{ error: '권한이 없습니다' },
{ status: 403 }
);
}
+ const user = await getUserByEmail(userId);
+ if (!user || !user.phone) {
+ return NextResponse.json(
+ { error: '전화번호가 등록되지 않았습니다' },
+ { status: 400 }
+ );
+ }
+
+ const userIdfromUsers = user.id
+
+
// MFA 토큰 검증
- const result = await verifySmsToken(parseInt(userId), token);
+ const result = await verifySmsToken(userIdfromUsers, token);
if (result.success) {
return NextResponse.json({
diff --git a/app/api/files/[...path]/route.ts b/app/api/files/[...path]/route.ts
index f92dd1d8..e03187e3 100644
--- a/app/api/files/[...path]/route.ts
+++ b/app/api/files/[...path]/route.ts
@@ -1,74 +1,216 @@
// app/api/files/[...path]/route.ts
-import { NextRequest, NextResponse } from 'next/server'
-import { readFile } from 'fs/promises'
-import { join } from 'path'
-import { stat } from 'fs/promises'
+// /nas_evcp 경로에서 파일을 서빙하는 API (다운로드 강제 기능 추가)
+
+import { NextRequest, NextResponse } from "next/server";
+import { promises as fs } from "fs";
+import path from "path";
+
+const nasPath = process.env.NAS_PATH || "/evcp_nas"
+
+// MIME 타입 매핑
+const getMimeType = (filePath: string): string => {
+ const ext = path.extname(filePath).toLowerCase();
+ const mimeTypes: Record<string, string> = {
+ '.pdf': 'application/pdf',
+ '.doc': 'application/msword',
+ '.docx': 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
+ '.xls': 'application/vnd.ms-excel',
+ '.xlsx': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
+ '.jpg': 'image/jpeg',
+ '.jpeg': 'image/jpeg',
+ '.png': 'image/png',
+ '.gif': 'image/gif',
+ '.txt': 'text/plain',
+ '.zip': 'application/zip',
+ };
+
+ return mimeTypes[ext] || 'application/octet-stream';
+};
+
+// 보안: 허용된 디렉토리 체크
+const isAllowedPath = (requestedPath: string): boolean => {
+ const allowedPaths = [
+ 'basicContract',
+ 'basicContract/template',
+ 'basicContract/signed',
+ 'vendorFormReportSample',
+ 'vendorFormData',
+ ];
+
+ return allowedPaths.some(allowed =>
+ requestedPath.startsWith(allowed) || requestedPath === allowed
+ );
+};
export async function GET(
request: NextRequest,
{ params }: { params: { path: string[] } }
) {
try {
+ // 요청된 파일 경로 구성
+ const requestedPath = params.path.join('/');
+
+ console.log(`📂 파일 요청: ${requestedPath}`);
+
+ // ✅ 다운로드 강제 여부 확인
+ const url = new URL(request.url);
+ const forceDownload = url.searchParams.get('download') === 'true';
+
+ console.log(`📥 다운로드 강제 모드: ${forceDownload}`);
+
+ // 보안 체크: 허용된 경로인지 확인
+ if (!isAllowedPath(requestedPath)) {
+ console.log(`❌ 허용되지 않은 경로: ${requestedPath}`);
+ return new NextResponse('Forbidden', { status: 403 });
+ }
+
+ // 경로 트래버설 공격 방지
+ if (requestedPath.includes('..') || requestedPath.includes('~')) {
+ console.log(`❌ 위험한 경로 패턴: ${requestedPath}`);
+ return new NextResponse('Bad Request', { status: 400 });
+ }
- const path = request.nextUrl.searchParams.get("path");
+ // 환경에 따른 파일 경로 설정
+ let filePath: string;
+
+ if (process.env.NODE_ENV === 'production') {
+ // ✅ 프로덕션: NAS 경로 사용
+ filePath = path.join(nasPath, requestedPath);
+ } else {
+ // 개발: public 폴더
+ filePath = path.join(process.cwd(), 'public', requestedPath);
+ }
+ console.log(`📁 실제 파일 경로: ${filePath}`);
- // 경로 파라미터에서 파일 경로 조합
- const filePath = join(process.cwd(), 'uploads', ...params.path)
-
// 파일 존재 여부 확인
try {
- await stat(filePath)
- } catch (error) {
- return NextResponse.json(
- { error: 'File not found' },
- { status: 404 }
- )
+ await fs.access(filePath);
+ } catch {
+ console.log(`❌ 파일 없음: ${filePath}`);
+ return new NextResponse('File not found', { status: 404 });
}
-
+
+ // 파일 통계 정보 가져오기
+ const stats = await fs.stat(filePath);
+ if (!stats.isFile()) {
+ console.log(`❌ 파일이 아님: ${filePath}`);
+ return new NextResponse('Not a file', { status: 400 });
+ }
+
// 파일 읽기
- const fileBuffer = await readFile(filePath)
+ const fileBuffer = await fs.readFile(filePath);
- // 파일 확장자에 따른 MIME 타입 설정
- const fileName = params.path[params.path.length - 1]
- const fileExtension = fileName.split('.').pop()?.toLowerCase()
+ // MIME 타입 결정
+ const mimeType = getMimeType(filePath);
+ const fileName = path.basename(filePath);
+
+ console.log(`✅ 파일 서빙 성공: ${fileName} (${stats.size} bytes)`);
+
+ // ✅ Content-Disposition 헤더 결정
+ const contentDisposition = forceDownload
+ ? `attachment; filename="${fileName}"` // 강제 다운로드
+ : `inline; filename="${fileName}"`; // 브라우저에서 열기
+
+ // Range 요청 처리 (큰 파일의 부분 다운로드 지원)
+ const range = request.headers.get('range');
- let contentType = 'application/octet-stream'
+ if (range) {
+ const parts = range.replace(/bytes=/, "").split("-");
+ const start = parseInt(parts[0], 10);
+ const end = parts[1] ? parseInt(parts[1], 10) : stats.size - 1;
+ const chunksize = (end - start) + 1;
+ const chunk = fileBuffer.slice(start, end + 1);
+
+ return new NextResponse(chunk, {
+ status: 206,
+ headers: {
+ 'Content-Range': `bytes ${start}-${end}/${stats.size}`,
+ 'Accept-Ranges': 'bytes',
+ 'Content-Length': chunksize.toString(),
+ 'Content-Type': mimeType,
+ 'Content-Disposition': contentDisposition, // ✅ 다운로드 모드 적용
+ },
+ });
+ }
+
+ // 일반 파일 응답
+ return new NextResponse(fileBuffer, {
+ headers: {
+ 'Content-Type': mimeType,
+ 'Content-Length': stats.size.toString(),
+ 'Content-Disposition': contentDisposition, // ✅ 다운로드 모드 적용
+ 'Cache-Control': 'public, max-age=31536000', // 1년 캐시
+ 'ETag': `"${stats.mtime.getTime()}-${stats.size}"`,
+ // ✅ 추가 보안 헤더
+ 'X-Content-Type-Options': 'nosniff',
+ },
+ });
+
+ } catch (error) {
+ console.error('❌ 파일 서빙 오류:', error);
+ return new NextResponse('Internal Server Error', { status: 500 });
+ }
+}
+
+// HEAD 요청 지원 (파일 정보만 확인)
+export async function HEAD(
+ request: NextRequest,
+ { params }: { params: { path: string[] } }
+) {
+ try {
+ const requestedPath = params.path.join('/');
- if (fileExtension) {
- const mimeTypes: Record<string, string> = {
- 'pdf': 'application/pdf',
- 'doc': 'application/msword',
- 'docx': 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
- 'xls': 'application/vnd.ms-excel',
- 'xlsx': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
- 'ppt': 'application/vnd.ms-powerpoint',
- 'pptx': 'application/vnd.openxmlformats-officedocument.presentationml.presentation',
- 'txt': 'text/plain',
- 'csv': 'text/csv',
- 'png': 'image/png',
- 'jpg': 'image/jpeg',
- 'jpeg': 'image/jpeg',
- 'gif': 'image/gif',
- }
-
- contentType = mimeTypes[fileExtension] || contentType
+ // ✅ HEAD 요청에서도 다운로드 강제 여부 확인
+ const url = new URL(request.url);
+ const forceDownload = url.searchParams.get('download') === 'true';
+
+ if (!isAllowedPath(requestedPath)) {
+ return new NextResponse(null, { status: 403 });
}
- // 다운로드 설정
- const headers = new Headers()
- headers.set('Content-Type', contentType)
- headers.set('Content-Disposition', `attachment; filename="${fileName}"`)
+ if (requestedPath.includes('..') || requestedPath.includes('~')) {
+ return new NextResponse(null, { status: 400 });
+ }
+
+ let filePath: string;
- return new NextResponse(fileBuffer, {
- status: 200,
- headers,
- })
+ if (process.env.NODE_ENV === 'production') {
+ filePath = path.join(nasPath, requestedPath);
+ } else {
+ filePath = path.join(process.cwd(), 'public', requestedPath);
+ }
+
+ try {
+ const stats = await fs.stat(filePath);
+ if (!stats.isFile()) {
+ return new NextResponse(null, { status: 400 });
+ }
+
+ const mimeType = getMimeType(filePath);
+ const fileName = path.basename(filePath);
+
+ // ✅ HEAD 요청에서도 Content-Disposition 헤더 적용
+ const contentDisposition = forceDownload
+ ? `attachment; filename="${fileName}"` // 강제 다운로드
+ : `inline; filename="${fileName}"`; // 브라우저에서 열기
+
+ return new NextResponse(null, {
+ headers: {
+ 'Content-Type': mimeType,
+ 'Content-Length': stats.size.toString(),
+ 'Content-Disposition': contentDisposition, // ✅ 다운로드 모드 적용
+ 'Last-Modified': stats.mtime.toUTCString(),
+ 'ETag': `"${stats.mtime.getTime()}-${stats.size}"`,
+ 'X-Content-Type-Options': 'nosniff',
+ },
+ });
+ } catch {
+ return new NextResponse(null, { status: 404 });
+ }
+
} catch (error) {
- console.error('Error downloading file:', error)
- return NextResponse.json(
- { error: 'Failed to download file' },
- { status: 500 }
- )
+ console.error('File HEAD error:', error);
+ return new NextResponse(null, { status: 500 });
}
} \ No newline at end of file
diff --git a/app/api/ocr/utils/tableExtraction.ts b/app/api/ocr/utils/tableExtraction.ts
index 720e5a5f..0a727f84 100644
--- a/app/api/ocr/utils/tableExtraction.ts
+++ b/app/api/ocr/utils/tableExtraction.ts
@@ -69,37 +69,107 @@ export async function extractTablesFromOCR (ocrResult: any): Promise<ExtractedRo
function isRelevantTable (table: OCRTable): boolean {
const headers = table.cells.filter(c => c.rowIndex < 3).map(getCellText).join(' ').toLowerCase();
- return /\bno\b|번호/.test(headers) && /identification|식별|ident|id/.test(headers);
+ console.log(`🔍 Checking table relevance. Headers: "${headers}"`);
+
+ // 기존 조건
+ const hasNoColumn = /\bno\b|번호/.test(headers);
+ const hasIdentification = /identification|식별|ident|id/.test(headers);
+
+ console.log(`📝 Has NO column: ${hasNoColumn}`);
+ console.log(`📝 Has Identification: ${hasIdentification}`);
+
+ // 기본 조건
+ if (hasNoColumn && hasIdentification) {
+ console.log(`✅ Table passes strict criteria`);
+ return true;
+ }
+
+ // 완화된 조건들
+ const relaxedConditions = [
+ // 조건 1: 테이블에 여러 열이 있고 숫자나 식별자 패턴이 보이는 경우
+ table.cells.length > 10 && /\d+/.test(headers),
+
+ // 조건 2: joint, tag, weld 등 관련 키워드가 있는 경우
+ /joint|tag|weld|type|date/.test(headers),
+
+ // 조건 3: 식별번호 패턴이 보이는 경우 (하이픈이 포함된 문자열)
+ headers.includes('-') && headers.length > 20,
+
+ // 조건 4: 한국어 관련 키워드
+ /용접|조인트|태그/.test(headers)
+ ];
+
+ const passedConditions = relaxedConditions.filter(Boolean).length;
+ console.log(`📊 Relaxed conditions passed: ${passedConditions}/${relaxedConditions.length}`);
+
+ if (passedConditions >= 1) {
+ console.log(`✅ Table passes relaxed criteria`);
+ return true;
+ }
+
+ console.log(`❌ Table does not meet any criteria`);
+ return false;
}
-
/* -------------------------------------------------------------------------- */
/* 표 해석 */
/* -------------------------------------------------------------------------- */
function extractTableData (table: OCRTable, imgIdx: number, tblIdx: number): ExtractedRow[] {
+ console.log(`🔧 Starting extractTableData for table ${imgIdx}-${tblIdx}`);
+
const grid = buildGrid(table);
+ console.log(`📊 Grid size: ${grid.length} rows x ${grid[0]?.length || 0} columns`);
+
const headerRowIdx = findHeaderRow(grid);
- if (headerRowIdx === -1) return [];
+ console.log(`📍 Header row index: ${headerRowIdx}`);
- const format = detectFormat(grid[headerRowIdx]);
- const mapping = mapColumns(grid[headerRowIdx]);
+ if (headerRowIdx === -1) {
+ console.log(`❌ No header row found`);
+ return [];
+ }
+
+ const format = detectFormat(grid[headerRowIdx]);
+ const mapping = mapColumns(grid[headerRowIdx]);
+
+ console.log(`📋 Detected format: ${format}`);
+ console.log(`🗂️ Column mapping:`, mapping);
const seen = new Set<string>();
const data: ExtractedRow[] = [];
for (let r = headerRowIdx + 1; r < grid.length; r++) {
const row = grid[r];
- if (isBlankRow(row)) continue;
+
+ if (isBlankRow(row)) {
+ console.log(`⏭️ Row ${r}: blank, skipping`);
+ continue;
+ }
+
+ console.log(`🔍 Processing row ${r}: [${row.join(' | ')}]`);
const parsed = buildRow(row, format, mapping, tblIdx, r);
- if (!parsed || !isValidRow(parsed)) continue;
+ if (!parsed) {
+ console.log(`❌ Row ${r}: failed to parse`);
+ continue;
+ }
+
+ if (!isValidRow(parsed)) {
+ console.log(`❌ Row ${r}: invalid (no: "${parsed.no}", id: "${parsed.identificationNo}")`);
+ continue;
+ }
const key = `${parsed.no}-${parsed.identificationNo}`;
- if (seen.has(key)) continue;
+ if (seen.has(key)) {
+ console.log(`⚠️ Row ${r}: duplicate key "${key}", skipping`);
+ continue;
+ }
+
seen.add(key);
-
data.push(parsed);
+ console.log(`✅ Row ${r}: added (${JSON.stringify(parsed)})`);
}
+
+ console.log(`🎯 Table ${imgIdx}-${tblIdx}: extracted ${data.length} valid rows`);
return data;
}
@@ -108,18 +178,39 @@ function extractTableData (table: OCRTable, imgIdx: number, tblIdx: number): Ext
/* -------------------------------------------------------------------------- */
function buildGrid (table: OCRTable): string[][] {
+ console.log(`🔧 Building grid from ${table.cells.length} cells`);
+
const maxR = Math.max(...table.cells.map(c => c.rowIndex + c.rowSpan - 1));
const maxC = Math.max(...table.cells.map(c => c.columnIndex + c.columnSpan - 1));
+
+ console.log(`📊 Grid dimensions: ${maxR + 1} rows x ${maxC + 1} columns`);
+
const grid = Array.from({ length: maxR + 1 }, () => Array(maxC + 1).fill(''));
- table.cells.forEach(cell => {
+ // 셀별 상세 정보 출력
+ table.cells.forEach((cell, idx) => {
const txt = getCellText(cell);
+ console.log(`📱 Cell ${idx}: (${cell.rowIndex},${cell.columnIndex}) span(${cell.rowSpan},${cell.columnSpan}) = "${txt}"`);
+
for (let r = cell.rowIndex; r < cell.rowIndex + cell.rowSpan; r++) {
for (let c = cell.columnIndex; c < cell.columnIndex + cell.columnSpan; c++) {
- grid[r][c] = grid[r][c] ? `${grid[r][c]} ${txt}` : txt;
+ const oldValue = grid[r][c];
+ const newValue = oldValue ? `${oldValue} ${txt}` : txt;
+ grid[r][c] = newValue;
+
+ if (oldValue) {
+ console.log(`🔄 Grid[${r}][${c}]: "${oldValue}" → "${newValue}"`);
+ }
}
}
});
+
+ // 최종 그리드 출력
+ console.log(`📋 Final grid:`);
+ grid.forEach((row, r) => {
+ console.log(` Row ${r}: [${row.map(cell => `"${cell}"`).join(', ')}]`);
+ });
+
return grid;
}
@@ -128,13 +219,52 @@ function getCellText (cell: TableCell): string {
}
function findHeaderRow (grid: string[][]): number {
+ console.log(`🔍 Finding header row in grid with ${grid.length} rows`);
+
+ for (let i = 0; i < Math.min(5, grid.length); i++) {
+ const rowText = grid[i].join(' ').toLowerCase();
+ console.log(`📝 Row ${i}: "${rowText}"`);
+
+ // 기존 엄격한 조건
+ if (/\bno\b|번호/.test(rowText) && /identification|식별|ident/.test(rowText)) {
+ console.log(`✅ Row ${i}: Strict match`);
+ return i;
+ }
+
+ // 완화된 조건들
+ const relaxedMatches = [
+ // 1. NO 컬럼 + 다른 관련 키워드
+ (/\bno\b|번호/.test(rowText) && /joint|tag|type|weld|date/.test(rowText)),
+
+ // 2. ID/식별 + 다른 관련 키워드
+ (/identification|식별|ident|id/.test(rowText) && /joint|tag|no|type/.test(rowText)),
+
+ // 3. 용접 관련 키워드가 여러 개
+ (rowText.match(/joint|tag|type|weld|date|no|id|식별|번호|용접/g)?.length >= 3),
+
+ // 4. 첫 번째 행이고 여러 단어가 있는 경우
+ (i === 0 && rowText.split(/\s+/).filter(w => w.length > 1).length >= 3)
+ ];
+
+ if (relaxedMatches.some(Boolean)) {
+ console.log(`✅ Row ${i}: Relaxed match`);
+ return i;
+ }
+
+ console.log(`❌ Row ${i}: No match`);
+ }
+
+ // 최후의 수단: 첫 번째 비어있지 않은 행
for (let i = 0; i < Math.min(3, grid.length); i++) {
- const t = grid[i].join(' ').toLowerCase();
- if (/\bno\b|번호/.test(t) && /identification|식별|ident/.test(t)) return i;
+ if (grid[i].some(cell => cell.trim().length > 0)) {
+ console.log(`⚠️ Using row ${i} as fallback header`);
+ return i;
+ }
}
+
+ console.log(`❌ No header row found`);
return -1;
}
-
/* -------------------------------------------------------------------------- */
/* Column Mapping */
/* -------------------------------------------------------------------------- */
@@ -146,19 +276,153 @@ function detectFormat (header: string[]): 'format1' | 'format2' {
function mapColumns (header: string[]): ColumnMapping {
const mp: ColumnMapping = { no: -1, identification: -1, tagNo: -1, jointNo: -1, jointType: -1, weldingDate: -1 };
+
+ console.log(`🗂️ Smart mapping columns from header: [${header.map(h => `"${h}"`).join(', ')}]`);
+ // === STEP 1: 기존 개별 컬럼 매핑 ===
header.forEach((h, i) => {
- const t = h.toLowerCase();
- if (/^no\.?$/.test(t) && !/ident|tag|joint/.test(t)) mp.no = i;
- else if (/identification|ident/.test(t)) mp.identification = i;
- else if (/tag.*no/.test(t)) mp.tagNo = i;
- else if (/joint.*no/.test(t)) mp.jointNo = i;
- else if (/joint.*type/.test(t) || (/^type$/.test(t) && mp.jointType === -1)) mp.jointType = i;
- else if (/welding|date/.test(t)) mp.weldingDate = i;
+ const t = h.toLowerCase().trim();
+ console.log(`📋 Column ${i}: "${h}" → "${t}"`);
+
+ if (mp.no === -1 && (/^no\.?$/i.test(t) || /^번호$/i.test(t) || /^순번$/i.test(t))) {
+ mp.no = i;
+ console.log(`✅ NO column (individual) mapped to index ${i}`);
+ }
+
+ if (mp.identification === -1 && (/identification.*no/i.test(t) || /식별.*번호/i.test(t))) {
+ mp.identification = i;
+ console.log(`✅ Identification column (individual) mapped to index ${i}`);
+ }
+
+ if (mp.tagNo === -1 && (/tag.*no/i.test(t) || /태그.*번호/i.test(t))) {
+ mp.tagNo = i;
+ console.log(`✅ Tag No column (individual) mapped to index ${i}`);
+ }
+
+ if (mp.jointNo === -1 && (/joint.*no/i.test(t) || /조인트.*번호/i.test(t) || /oint.*no/i.test(t))) {
+ mp.jointNo = i;
+ console.log(`✅ Joint No column (individual) mapped to index ${i}`);
+ }
+
+ if (mp.jointType === -1 && (/joint.*type/i.test(t) || /^type$/i.test(t) || /형태/i.test(t))) {
+ mp.jointType = i;
+ console.log(`✅ Joint Type column (individual) mapped to index ${i}`);
+ }
+
+ if (mp.weldingDate === -1 && (/welding.*date/i.test(t) || /weld.*date/i.test(t) || /^date$/i.test(t) || /날짜/i.test(t))) {
+ mp.weldingDate = i;
+ console.log(`✅ Welding Date column (individual) mapped to index ${i}`);
+ }
+ });
+
+ // === STEP 2: 실용적 추론 ===
+ console.log(`🤖 Starting practical column inference...`);
+
+ // NO 컬럼이 매핑되지 않았다면, 첫 번째 컬럼을 NO로 추정
+ if (mp.no === -1) {
+ mp.no = 0;
+ console.log(`🔮 NO column inferred as index 0 (first column)`);
+ }
+
+ // Identification 컬럼 찾기 - "identification" 키워드가 포함된 컬럼 중에서
+ if (mp.identification === -1) {
+ for (let i = 0; i < header.length; i++) {
+ const text = header[i].toLowerCase();
+ if (text.includes('identification') || text.includes('식별')) {
+ mp.identification = i;
+ console.log(`🆔 Identification column found at index ${i}`);
+ break;
+ }
+ }
+ }
+
+ // Tag No 컬럼 찾기 - "tag" 키워드가 포함된 컬럼 중에서
+ if (mp.tagNo === -1) {
+ for (let i = 0; i < header.length; i++) {
+ const text = header[i].toLowerCase();
+ if (text.includes('tag') && !text.includes('no')) {
+ mp.tagNo = i;
+ console.log(`🏷️ Tag column found at index ${i}`);
+ break;
+ }
+ }
+ }
+
+ // Joint No 컬럼 찾기
+ if (mp.jointNo === -1) {
+ for (let i = 0; i < header.length; i++) {
+ const text = header[i].toLowerCase();
+ if (text.includes('joint') || text.includes('oint')) {
+ mp.jointNo = i;
+ console.log(`🔗 Joint column found at index ${i}`);
+ break;
+ }
+ }
+ }
+
+ // === STEP 3: 패턴 기반 추론 (마지막 수단) ===
+ console.log(`🎯 Pattern-based fallback mapping...`);
+
+ // 전체 헤더에서 실제 식별번호 패턴이 있는 컬럼 찾기
+ if (mp.identification === -1) {
+ for (let i = 0; i < header.length; i++) {
+ const text = header[i];
+ // 하이픈이 포함된 긴 문자열이 있는 컬럼
+ if (text.includes('-') && text.length > 15) {
+ mp.identification = i;
+ console.log(`🆔 Identification inferred at index ${i} (contains ID pattern)`);
+ break;
+ }
+ }
+ }
+
+ // 숫자 패턴이 있는 컬럼을 Tag No로 추정
+ if (mp.tagNo === -1) {
+ for (let i = 1; i < header.length; i++) { // 첫 번째 컬럼 제외
+ const text = header[i];
+ // 7-8자리 숫자가 있는 컬럼
+ if (/\d{7,8}/.test(text)) {
+ mp.tagNo = i;
+ console.log(`🏷️ Tag No inferred at index ${i} (contains number pattern)`);
+ break;
+ }
+ }
+ }
+
+ // === STEP 4: 기본값 설정 ===
+ console.log(`🔧 Setting default values for unmapped columns...`);
+
+ // 여전히 매핑되지 않은 중요한 컬럼들에 대해 순서 기반 추정
+ const essentialColumns = [
+ { key: 'identification', currentValue: mp.identification, defaultIndex: 1 },
+ { key: 'tagNo', currentValue: mp.tagNo, defaultIndex: 2 },
+ { key: 'jointNo', currentValue: mp.jointNo, defaultIndex: 3 },
+ { key: 'jointType', currentValue: mp.jointType, defaultIndex: 4 },
+ { key: 'weldingDate', currentValue: mp.weldingDate, defaultIndex: Math.min(5, header.length - 1) }
+ ];
+
+ essentialColumns.forEach(col => {
+ if ((col.currentValue as number) === -1 && col.defaultIndex < header.length) {
+ (mp as any)[col.key] = col.defaultIndex;
+ console.log(`🔧 ${col.key} set to default index ${col.defaultIndex}`);
+ }
});
+
+ console.log(`🎯 Final optimized column mapping:`, mp);
+
+ // === STEP 5: 매핑 품질 검증 ===
+ const mappedCount = Object.values(mp).filter(v => v !== -1).length;
+ const totalColumns = Object.keys(mp).length;
+ const mappingQuality = mappedCount / totalColumns;
+
+ console.log(`📊 Mapping quality: ${mappedCount}/${totalColumns} (${(mappingQuality * 100).toFixed(1)}%)`);
+
+ if (mappingQuality < 0.5) {
+ console.warn(`⚠️ Low mapping quality detected. Consider manual adjustment.`);
+ }
+
return mp;
}
-
/* -------------------------------------------------------------------------- */
/* Row Extraction */
/* -------------------------------------------------------------------------- */
@@ -170,71 +434,351 @@ function buildRow (
tblIdx: number,
rowIdx: number
): ExtractedRow | null {
+ console.log(`🔨 Building row from: [${row.map(r => `"${r}"`).join(', ')}]`);
+ console.log(`📋 Using mapping:`, mp);
+ console.log(`📄 Format: ${format}`);
+
const out: ExtractedRow = {
- no: mp.no >= 0 ? clean(row[mp.no]) : '',
+ no: '',
identificationNo: '',
tagNo: '',
jointNo: '',
- jointType: mp.jointType >= 0 ? clean(row[mp.jointType]) : '',
+ jointType: '',
weldingDate: '',
confidence: 0,
sourceTable: tblIdx,
sourceRow: rowIdx,
};
- if (mp.weldingDate >= 0) out.weldingDate = clean(row[mp.weldingDate]);
- else {
- const idx = row.findIndex(col => /\d{4}[.\-/]\d{1,2}[.\-/]\d{1,2}/.test(col));
- if (idx >= 0) out.weldingDate = clean(row[idx]);
+ // === STEP 1: 매핑된 컬럼에서 기본 추출 ===
+
+ // NO 컬럼 추출
+ if (mp.no >= 0 && mp.no < row.length) {
+ const rawNo = clean(row[mp.no]);
+ // NO 필드에서 첫 번째 숫자 패턴 추출
+ const noMatch = rawNo.match(/\b(\d{2,4})\b/);
+ out.no = noMatch ? noMatch[1] : rawNo;
+ console.log(`📍 NO from column ${mp.no}: "${out.no}" (raw: "${rawNo}")`);
+ }
+
+ // Joint Type, Welding Date는 기존대로
+ if (mp.jointType >= 0 && mp.jointType < row.length) {
+ out.jointType = clean(row[mp.jointType]);
+ console.log(`🔗 Joint Type from column ${mp.jointType}: "${out.jointType}"`);
}
+ if (mp.weldingDate >= 0 && mp.weldingDate < row.length) {
+ out.weldingDate = clean(row[mp.weldingDate]);
+ console.log(`📅 Welding Date from column ${mp.weldingDate}: "${out.weldingDate}"`);
+ }
+
+ // === STEP 2: Format별 데이터 추출 ===
+
if (format === 'format2') {
- if (mp.identification >= 0) out.identificationNo = clean(row[mp.identification]);
- if (mp.jointNo >= 0) out.jointNo = clean(row[mp.jointNo]);
- if (mp.tagNo >= 0) out.tagNo = clean(row[mp.tagNo]);
+ console.log(`📄 Processing Format 2 (separate columns)`);
+
+ if (mp.identification >= 0 && mp.identification < row.length) {
+ out.identificationNo = clean(row[mp.identification]);
+ console.log(`🆔 Identification from column ${mp.identification}: "${out.identificationNo}"`);
+ }
+
+ if (mp.jointNo >= 0 && mp.jointNo < row.length) {
+ out.jointNo = clean(row[mp.jointNo]);
+ console.log(`🔗 Joint No from column ${mp.jointNo}: "${out.jointNo}"`);
+ }
+
+ if (mp.tagNo >= 0 && mp.tagNo < row.length) {
+ out.tagNo = clean(row[mp.tagNo]);
+ console.log(`🏷️ Tag No from column ${mp.tagNo}: "${out.tagNo}"`);
+ }
} else {
- const combined = mp.identification >= 0 ? row[mp.identification] : '';
- const parsed = parseIdentificationData(combined);
+ console.log(`📄 Processing Format 1 (combined identification column)`);
+
+ let combinedText = '';
+
+ // 매핑된 identification 컬럼에서 텍스트 가져오기
+ if (mp.identification >= 0 && mp.identification < row.length) {
+ combinedText = row[mp.identification];
+ console.log(`🆔 Combined text from column ${mp.identification}: "${combinedText}"`);
+ }
+
+ const parsed = parseIdentificationData(combinedText);
out.identificationNo = parsed.identificationNo;
- out.jointNo = parsed.jointNo;
- out.tagNo = parsed.tagNo;
+ out.jointNo = parsed.jointNo;
+ out.tagNo = parsed.tagNo;
+
+ console.log(`📊 Parsed from identification column:`, parsed);
}
+ // === STEP 3: 적극적 패턴 매칭으로 누락된 필드 채우기 ===
+ console.log(`🔍 Aggressive pattern matching for missing fields...`);
+
+ const allText = row.join(' ');
+ console.log(`📝 Full row text: "${allText}"`);
+
+ // NO 필드가 비어있다면 첫 번째 컬럼에서 숫자 패턴 찾기
+ if (!out.no && row.length > 0) {
+ const firstCol = clean(row[0]);
+ const noPatterns = [
+ /\b(\d{3})\b/g, // 3자리 숫자
+ /\b(\d{2,4})\b/g, // 2-4자리 숫자
+ /^(\d+)/ // 맨 앞 숫자
+ ];
+
+ for (const pattern of noPatterns) {
+ const matches = firstCol.match(pattern);
+ if (matches && matches.length > 0) {
+ out.no = matches[0].replace(/\D/g, ''); // 숫자만 추출
+ console.log(`📍 NO found via pattern in first column: "${out.no}"`);
+ break;
+ }
+ }
+ }
+
+ // Identification No 패턴 찾기 (하이픈이 포함된 긴 문자열)
+ if (!out.identificationNo) {
+ const idPatterns = [
+ /[A-Za-z0-9]+-[A-Za-z0-9]+-[A-Za-z0-9\-]+/g,
+ /-\d+[A-Za-z0-9]+-[A-Za-z0-9]+-[A-Za-z0-9]+/g,
+ /\b[A-Z]\d+[A-Z]-\d+-\d+-[A-Z]+-\d+-[A-Z0-9]+-[A-Z]-[A-Z0-9]+\b/g
+ ];
+
+ for (const pattern of idPatterns) {
+ const matches = allText.match(pattern);
+ if (matches && matches.length > 0) {
+ out.identificationNo = matches[0];
+ console.log(`🆔 Identification found via pattern: "${out.identificationNo}"`);
+ break;
+ }
+ }
+ }
+
+ // Tag No 패턴 찾기 (7-8자리 숫자)
+ if (!out.tagNo) {
+ const tagMatches = allText.match(/\b\d{7,8}\b/g);
+ if (tagMatches && tagMatches.length > 0) {
+ out.tagNo = tagMatches[0];
+ console.log(`🏷️ Tag found via pattern: "${out.tagNo}"`);
+ }
+ }
+
+ // Joint No 패턴 찾기 (짧은 영숫자 조합)
+ if (!out.jointNo) {
+ const jointPatterns = [
+ /\b[A-Z]{2,4}\d*\b/g, // 대문자+숫자 조합
+ /\b[A-Za-z0-9]{2,6}\b/g // 일반적인 짧은 조합
+ ];
+
+ for (const pattern of jointPatterns) {
+ const matches = allText.match(pattern);
+ if (matches) {
+ const candidates = matches.filter(m =>
+ m !== out.no &&
+ m !== out.tagNo &&
+ m !== out.identificationNo &&
+ m.length >= 2 && m.length <= 6 &&
+ !/^(no|tag|joint|type|date|welding|project|samsung|class)$/i.test(m)
+ );
+
+ if (candidates.length > 0) {
+ out.jointNo = candidates[0];
+ console.log(`🔗 Joint found via pattern: "${out.jointNo}"`);
+ break;
+ }
+ }
+ }
+ }
+
+ // Welding Date 패턴 찾기
+ if (!out.weldingDate) {
+ const datePatterns = [
+ /\d{4}[.\-/]\d{1,2}[.\-/]\d{1,2}/g,
+ /\d{4}\.\d{2}\.\d{2}/g
+ ];
+
+ for (const pattern of datePatterns) {
+ const matches = allText.match(pattern);
+ if (matches && matches.length > 0) {
+ out.weldingDate = matches[0];
+ console.log(`📅 Date found via pattern: "${out.weldingDate}"`);
+ break;
+ }
+ }
+ }
+
+ // === STEP 4: 품질 검증 및 후처리 ===
+
+ // 추출된 값들 정리
+ Object.keys(out).forEach(key => {
+ const value = (out as any)[key];
+ if (typeof value === 'string' && value) {
+ (out as any)[key] = value.replace(/^[^\w]+|[^\w]+$/g, '').trim();
+ }
+ });
+
out.confidence = scoreRow(out);
+
+ console.log(`📊 Final extracted row:`, out);
+ console.log(`🎯 Row confidence: ${out.confidence}`);
+
+ // 최소한의 데이터가 있는지 검증
+ const hasAnyData = !!(out.no || out.identificationNo || out.tagNo || out.jointNo);
+
+ if (!hasAnyData) {
+ console.log(`⚠️ No meaningful data extracted from row`);
+ return null;
+ }
+
return out;
}
-
/* -------------------------------------------------------------------------- */
/* Format‑1 셀 파싱 */
/* -------------------------------------------------------------------------- */
function parseIdentificationData (txt: string): { identificationNo: string; jointNo: string; tagNo: string } {
+ console.log(`🔍 Parsing identification data from: "${txt}"`);
+
const cleaned = clean(txt);
- if (!cleaned) return { identificationNo: '', jointNo: '', tagNo: '' };
+ if (!cleaned) {
+ console.log(`❌ Empty input text`);
+ return { identificationNo: '', jointNo: '', tagNo: '' };
+ }
+ console.log(`🧹 Cleaned text: "${cleaned}"`);
+
+ const result = { identificationNo: '', jointNo: '', tagNo: '' };
+
+ // 1. Identification No 추출 (하이픈이 2개 이상 포함된 패턴)
+ const idPatterns = [
+ /[A-Za-z0-9]+-[A-Za-z0-9]+-[A-Za-z0-9\-]+/g, // 기본 패턴
+ /-\d+[A-Za-z0-9]+-[A-Za-z0-9]+-[A-Za-z0-9]+/g, // 앞에 하이픈이 있는 경우
+ /\b[A-Za-z0-9]{2,}-[A-Za-z0-9]{2,}-[A-Za-z0-9]{2,}\b/g // 더 엄격한 패턴
+ ];
+
+ for (const pattern of idPatterns) {
+ const matches = cleaned.match(pattern);
+ if (matches && matches.length > 0) {
+ // 가장 긴 매치를 선택
+ result.identificationNo = matches.reduce((a, b) => a.length >= b.length ? a : b);
+ console.log(`🆔 Found identification: "${result.identificationNo}"`);
+ break;
+ }
+ }
+
+ // 2. Tag No 추출 (7-8자리 숫자)
+ const tagPatterns = [
+ /\btag[:\s]*(\d{7,8})\b/i, // "tag: 1234567" 형태
+ /\b(\d{7,8})\b/g // 단순 7-8자리 숫자
+ ];
+
+ for (const pattern of tagPatterns) {
+ const matches = cleaned.match(pattern);
+ if (matches) {
+ if (pattern.source.includes('tag')) {
+ result.tagNo = matches[1] || matches[0];
+ } else {
+ // 모든 7-8자리 숫자를 찾아서 가장 적절한 것 선택
+ const candidates = matches.filter(m => m && m.length >= 7 && m.length <= 8);
+ if (candidates.length > 0) {
+ result.tagNo = candidates[0];
+ }
+ }
+ if (result.tagNo) {
+ console.log(`🏷️ Found tag: "${result.tagNo}"`);
+ break;
+ }
+ }
+ }
+
+ // 3. Joint No 추출 (나머지 토큰 중에서)
const tokens = cleaned.split(/\s+/).map(clean).filter(Boolean);
-
- // Identification 후보: 하이픈이 2개 이상 포함된 토큰 가운데 가장 긴 것
- const idCand = tokens.filter(t => t.split('-').length >= 3).sort((a, b) => b.length - a.length);
- const identificationNo = idCand[0] || '';
-
- const residual = tokens.filter(t => t !== identificationNo);
- if (!residual.length) return { identificationNo, jointNo: '', tagNo: '' };
-
- residual.sort((a, b) => a.length - b.length);
- const jointNo = residual[0] || '';
- const tagNo = residual[residual.length - 1] || '';
-
- return { identificationNo, jointNo, tagNo };
+ console.log(`📝 All tokens: [${tokens.join(', ')}]`);
+
+ // 이미 사용된 토큰들 제외
+ const usedTokens = new Set([result.identificationNo, result.tagNo]);
+ const remainingTokens = tokens.filter(token =>
+ !usedTokens.has(token) &&
+ !result.identificationNo.includes(token) &&
+ !result.tagNo.includes(token) &&
+ token.length > 1 &&
+ !/^(tag|joint|no|identification|식별|번호)$/i.test(token)
+ );
+
+ console.log(`🔄 Remaining tokens for joint: [${remainingTokens.join(', ')}]`);
+
+ if (remainingTokens.length > 0) {
+ // 가장 짧고 알파벳+숫자 조합인 토큰을 Joint No로 선택
+ const jointCandidates = remainingTokens
+ .filter(token => /^[A-Za-z0-9]+$/.test(token) && token.length >= 2 && token.length <= 8)
+ .sort((a, b) => a.length - b.length);
+
+ if (jointCandidates.length > 0) {
+ result.jointNo = jointCandidates[0];
+ console.log(`🔗 Found joint: "${result.jointNo}"`);
+ } else if (remainingTokens.length > 0) {
+ // 후보가 없으면 가장 짧은 토큰 사용
+ result.jointNo = remainingTokens.reduce((a, b) => a.length <= b.length ? a : b);
+ console.log(`🔗 Found joint (fallback): "${result.jointNo}"`);
+ }
+ }
+
+ // 4. 결과 검증 및 정리
+ Object.keys(result).forEach(key => {
+ const value = (result as any)[key];
+ if (value && typeof value === 'string') {
+ (result as any)[key] = value.replace(/^[^\w]+|[^\w]+$/g, ''); // 앞뒤 특수문자 제거
+ }
+ });
+
+ console.log(`📊 Final parsed result:`, result);
+ return result;
}
-
/* -------------------------------------------------------------------------- */
/* Helpers */
/* -------------------------------------------------------------------------- */
const clean = (s: string = '') => s.replace(/[\r\n\t]+/g, ' ').replace(/\s+/g, ' ').trim();
const isBlankRow = (row: string[]) => row.every(c => !clean(c));
-const isValidRow = (r: ExtractedRow) => !!(r.no || r.identificationNo);
+function isValidRow (r: ExtractedRow): boolean {
+ console.log(`✅ Validating row: no="${r.no}", id="${r.identificationNo}", tag="${r.tagNo}", joint="${r.jointNo}"`);
+
+ // Level 1: 기존 엄격한 조건
+ if (r.no && r.no.trim() || r.identificationNo && r.identificationNo.trim()) {
+ console.log(`✅ Level 1 validation passed (has no or identification)`);
+ return true;
+ }
+
+ // Level 2: 완화된 조건 - 주요 필드 중 2개 이상
+ const mainFields = [
+ r.no?.trim(),
+ r.identificationNo?.trim(),
+ r.tagNo?.trim(),
+ r.jointNo?.trim()
+ ].filter(Boolean);
+
+ if (mainFields.length >= 2) {
+ console.log(`✅ Level 2 validation passed (${mainFields.length} main fields present)`);
+ return true;
+ }
+
+ // Level 3: 더 관대한 조건 - 어떤 필드든 하나라도 의미있는 값
+ const allFields = [
+ r.no?.trim(),
+ r.identificationNo?.trim(),
+ r.tagNo?.trim(),
+ r.jointNo?.trim(),
+ r.jointType?.trim(),
+ r.weldingDate?.trim()
+ ].filter(field => field && field.length > 1); // 1글자 이상
+
+ if (allFields.length >= 1) {
+ console.log(`✅ Level 3 validation passed (${allFields.length} fields with meaningful content)`);
+ return true;
+ }
+
+ console.log(`❌ Validation failed - no meaningful content found`);
+ return false;
+}
function scoreRow (r: ExtractedRow): number {
const w: Record<keyof ExtractedRow, number> = {
diff --git a/app/api/vendors/route.ts b/app/api/vendors/route.ts
new file mode 100644
index 00000000..7c7dbb84
--- /dev/null
+++ b/app/api/vendors/route.ts
@@ -0,0 +1,248 @@
+// app/api/vendors/route.ts
+import { NextRequest, NextResponse } from 'next/server'
+import { unstable_noStore } from 'next/cache'
+import { revalidateTag } from 'next/cache'
+import { randomUUID } from 'crypto'
+import * as fs from 'fs/promises'
+import * as path from 'path'
+import { eq } from 'drizzle-orm'
+import { PgTransaction } from 'drizzle-orm/pg-core'
+
+import db from '@/db/db'
+import { users, vendors, vendorContacts, vendorAttachments } from '@/db/schema'
+import { insertVendor } from '@/lib/vendors/repository'
+import { getErrorMessage } from '@/lib/handle-error'
+
+// Types
+interface CreateVendorData {
+ vendorName: string
+ vendorCode?: string
+ address?: string
+ country?: string
+ phone?: string
+ email: string
+ website?: string
+ status?: string
+ taxId: string
+ vendorTypeId: number
+ items?: string
+ representativeName?: string
+ representativeBirth?: string
+ representativeEmail?: string
+ representativePhone?: string
+ corporateRegistrationNumber?: string
+}
+
+interface ContactData {
+ contactName: string
+ contactPosition?: string
+ contactEmail: string
+ contactPhone?: string
+ isPrimary?: boolean
+}
+
+// File attachment types
+const FILE_TYPES = {
+ BUSINESS_REGISTRATION: 'BUSINESS_REGISTRATION',
+ ISO_CERTIFICATION: 'ISO_CERTIFICATION',
+ CREDIT_REPORT: 'CREDIT_REPORT',
+ BANK_ACCOUNT_COPY: 'BANK_ACCOUNT_COPY'
+} as const
+
+type FileType = typeof FILE_TYPES[keyof typeof FILE_TYPES]
+
+async function storeVendorFiles(
+ tx: PgTransaction<any, any, any>,
+ vendorId: number,
+ files: File[],
+ attachmentType: FileType
+) {
+ const vendorDir = path.join(
+ process.cwd(),
+ "public",
+ "vendors",
+ String(vendorId)
+ )
+ await fs.mkdir(vendorDir, { recursive: true })
+
+ for (const file of files) {
+ // Convert file to buffer
+ const ab = await file.arrayBuffer()
+ const buffer = Buffer.from(ab)
+
+ // Generate a unique filename
+ const uniqueName = `${randomUUID()}-${file.name}`
+ const relativePath = path.join("vendors", String(vendorId), uniqueName)
+ const absolutePath = path.join(process.cwd(), "public", relativePath)
+
+ // Write to disk
+ await fs.writeFile(absolutePath, buffer)
+
+ // Insert attachment record
+ await tx.insert(vendorAttachments).values({
+ vendorId,
+ fileName: file.name,
+ filePath: "/" + relativePath.replace(/\\/g, "/"),
+ attachmentType,
+ })
+ }
+}
+
+export async function POST(request: NextRequest) {
+ unstable_noStore()
+
+ try {
+ const formData = await request.formData()
+
+ // Parse vendor data and contacts from JSON strings
+ const vendorDataString = formData.get('vendorData') as string
+ const contactsString = formData.get('contacts') as string
+
+ if (!vendorDataString || !contactsString) {
+ return NextResponse.json(
+ { error: 'Missing vendor data or contacts' },
+ { status: 400 }
+ )
+ }
+
+ const vendorData: CreateVendorData = JSON.parse(vendorDataString)
+ const contacts: ContactData[] = JSON.parse(contactsString)
+
+ // Extract files by type
+ const businessRegistrationFiles = formData.getAll('businessRegistration') as File[]
+ const isoCertificationFiles = formData.getAll('isoCertification') as File[]
+ const creditReportFiles = formData.getAll('creditReport') as File[]
+ const bankAccountFiles = formData.getAll('bankAccount') as File[]
+
+ // Validate required files
+ if (businessRegistrationFiles.length === 0) {
+ return NextResponse.json(
+ { error: '사업자등록증을 업로드해주세요.' },
+ { status: 400 }
+ )
+ }
+
+ if (isoCertificationFiles.length === 0) {
+ return NextResponse.json(
+ { error: 'ISO 인증서를 업로드해주세요.' },
+ { status: 400 }
+ )
+ }
+
+ if (creditReportFiles.length === 0) {
+ return NextResponse.json(
+ { error: '신용평가보고서를 업로드해주세요.' },
+ { status: 400 }
+ )
+ }
+
+ if (vendorData.country !== "KR" && bankAccountFiles.length === 0) {
+ return NextResponse.json(
+ { error: '대금지급 통장사본을 업로드해주세요.' },
+ { status: 400 }
+ )
+ }
+
+ // Check for existing email
+ const existingUser = await db
+ .select({ id: users.id })
+ .from(users)
+ .where(eq(users.email, vendorData.email))
+ .limit(1)
+
+ if (existingUser.length > 0) {
+ return NextResponse.json(
+ {
+ error: `이미 등록된 이메일입니다. 다른 이메일을 사용해주세요. (Email ${vendorData.email} already exists in the system)`
+ },
+ { status: 400 }
+ )
+ }
+
+ // Check for existing taxId
+ const existingVendor = await db
+ .select({ id: vendors.id })
+ .from(vendors)
+ .where(eq(vendors.taxId, vendorData.taxId))
+ .limit(1)
+
+ if (existingVendor.length > 0) {
+ return NextResponse.json(
+ {
+ error: `이미 등록된 사업자등록번호입니다. (Tax ID ${vendorData.taxId} already exists in the system)`
+ },
+ { status: 400 }
+ )
+ }
+
+ // Create vendor and handle files in transaction
+ await db.transaction(async (tx) => {
+ // Insert the vendor
+ const [newVendor] = await insertVendor(tx, {
+ vendorName: vendorData.vendorName,
+ vendorCode: vendorData.vendorCode || null,
+ address: vendorData.address || null,
+ country: vendorData.country || null,
+ phone: vendorData.phone || null,
+ email: vendorData.email,
+ website: vendorData.website || null,
+ status: vendorData.status ?? "PENDING_REVIEW",
+ taxId: vendorData.taxId,
+ vendorTypeId: vendorData.vendorTypeId,
+ items: vendorData.items || null,
+
+ // Representative info
+ representativeName: vendorData.representativeName || null,
+ representativeBirth: vendorData.representativeBirth || null,
+ representativeEmail: vendorData.representativeEmail || null,
+ representativePhone: vendorData.representativePhone || null,
+ corporateRegistrationNumber: vendorData.corporateRegistrationNumber || null,
+ representativeWorkExpirence: vendorData.representativeWorkExpirence || false,
+
+ })
+
+ // Store files by type
+ if (businessRegistrationFiles.length > 0) {
+ await storeVendorFiles(tx, newVendor.id, businessRegistrationFiles, FILE_TYPES.BUSINESS_REGISTRATION)
+ }
+
+ if (isoCertificationFiles.length > 0) {
+ await storeVendorFiles(tx, newVendor.id, isoCertificationFiles, FILE_TYPES.ISO_CERTIFICATION)
+ }
+
+ if (creditReportFiles.length > 0) {
+ await storeVendorFiles(tx, newVendor.id, creditReportFiles, FILE_TYPES.CREDIT_REPORT)
+ }
+
+ if (bankAccountFiles.length > 0) {
+ await storeVendorFiles(tx, newVendor.id, bankAccountFiles, FILE_TYPES.BANK_ACCOUNT_COPY)
+ }
+
+ // Insert contacts
+ for (const contact of contacts) {
+ await tx.insert(vendorContacts).values({
+ vendorId: newVendor.id,
+ contactName: contact.contactName,
+ contactPosition: contact.contactPosition || null,
+ contactEmail: contact.contactEmail,
+ contactPhone: contact.contactPhone || null,
+ isPrimary: contact.isPrimary ?? false,
+ })
+ }
+ })
+
+ revalidateTag("vendors")
+
+ return NextResponse.json(
+ { message: '벤더 등록이 완료되었습니다.' },
+ { status: 201 }
+ )
+
+ } catch (error) {
+ console.error('Vendor creation error:', error)
+ return NextResponse.json(
+ { error: getErrorMessage(error) },
+ { status: 500 }
+ )
+ }
+} \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 03:56:25 +0000