"use server"

import db from '@/db/db'
import * as schema from '@/db/schema'
import { revalidatePath } from 'next/cache'
import ExcelJS from 'exceljs'
import { sql } from 'drizzle-orm'

export async function getSapTableCounts(tableNames: string[]) {
  const result: Record<string, number> = {}
  for (const name of tableNames) {
    const table = (schema as any)[camelFromTable(name)]
    if (!table) {
      result[name] = 0
      continue
    }
    const rows = await db.select({ c: sql<number>`count(*)` }).from(table)
    result[name] = Number(rows?.[0]?.c ?? 0)
  }
  return result
}

// 단일 테이블 카운트 조회 함수
export async function getSingleTableCount(tableName: string): Promise<number> {
  const table = (schema as any)[camelFromTable(tableName)]
  if (!table) return 0
  
  const rows = await db.select({ c: sql<number>`count(*)` }).from(table)
  return Number(rows?.[0]?.c ?? 0)
}

export async function exportTemplate(tableName: string): Promise<Buffer> {
  const table = (schema as any)[camelFromTable(tableName)]
  if (!table) throw new Error(`Unknown table: ${tableName}`)

  const workbook = new ExcelJS.Workbook()
  const sheet = workbook.addWorksheet('template')

  const columns = Object.keys(table)
    .filter((k) => !k.startsWith('_') && k !== 'getSQL' && k !== '[$$schema]')
    .filter((k) => k !== 'id')

  sheet.addRow(columns)
  const buf = await workbook.xlsx.writeBuffer()
  return Buffer.from(buf)
}

export async function importExcel(tableName: string, file: File) {
  const table = (schema as any)[camelFromTable(tableName)]
  if (!table) throw new Error(`Unknown table: ${tableName}`)

  const workbook = new ExcelJS.Workbook()
  const arrayBuffer = await file.arrayBuffer()
  await workbook.xlsx.load(arrayBuffer)
  const sheet = workbook.worksheets[0]
  const rawHeader = (sheet.getRow(1).values as any[]).slice(1) as string[]

  // 1) 스키마 컬럼 수집 및 정규화 맵 구성 (Drizzle 내부 메타데이터 제외)
  const schemaKeys = Object.keys(table)
    .filter((k) => {
      // Drizzle ORM 내부 메타데이터 및 시스템 컬럼 제외
      return !k.startsWith('_') && 
             k !== 'getSQL' && 
             k !== '[$$schema]' && 
             k !== 'id' && 
             k !== 'enableRLS' &&
             k !== '$inferSelect' &&
             k !== '$inferInsert' &&
             typeof (table as any)[k] !== 'function'
    })
  const normalize = (s: string) => (s || '')
    .replace(/\./g, '')
    .replace(/\s+/g, '')
    .replace(/[\-/]/g, '')
    .toLowerCase()
  const normalizedToSchemaKey = new Map<string, string>()
  for (const key of schemaKeys) normalizedToSchemaKey.set(normalize(key), key)

  // 2) 엑셀 헤더 정규화 후 스키마 매핑
  const mappedHeader: (string | null)[] = rawHeader.map((h) => {
    const key = normalizedToSchemaKey.get(normalize(String(h)))
    return key ?? null
  })

  const unknownHeaders = rawHeader
    .map((h, idx) => ({ h, idx }))
    .filter(({ idx }) => mappedHeader[idx] === null)
    .map(({ h }) => String(h))

  const mappedColumns = mappedHeader.filter(Boolean) as string[]
  
  // EVCP 스타일 헤더 검증
  const headerErrors: string[] = []
  
  // 1) 누락된 스키마 컬럼 확인 (필수 컬럼이 파일에 없는 경우)
  schemaKeys.forEach(schemaKey => {
    if (!mappedColumns.includes(schemaKey)) {
      headerErrors.push(`필수 컬럼 "${schemaKey}"가 파일에 없습니다`)
    }
  })
  
  // 2) 예상치 못한 컬럼 확인 (파일에 있지만 스키마에 없는 컬럼)
  unknownHeaders.forEach(header => {
    headerErrors.push(`예상치 못한 컬럼 "${header}"가 파일에 있습니다`)
  })
  
  // 헤더 검증 실패 시 에러 반환
  if (headerErrors.length > 0) {
    return {
      success: false,
      message: `헤더 검증 실패: ${headerErrors.join(', ')}`,
    }
  }
  
  // PK 컬럼이 매핑되었는지 확인
  const primaryKeys = getPrimaryKeysFromSchema(tableName)
  if (primaryKeys.length > 0) {
    const mappedPKs = primaryKeys.filter(pk => mappedColumns.includes(pk))
    if (mappedPKs.length === 0) {
      return {
        success: false,
        message: `필수 컬럼(Primary Key)이 없습니다. 필요한 PK: [${primaryKeys.join(', ')}]`,
      }
    }
  }

  const rows: any[] = []
  const rowNumbers: number[] = []
  sheet.eachRow((row, rowNumber) => {
    if (rowNumber === 1) return
    const values = (row.values as any[]).slice(1)
    const obj: Record<string, any> = {}
    mappedHeader.forEach((schemaKey, idx) => {
      if (!schemaKey) return
      const v = values[idx]
      obj[schemaKey] = normalizeCellValue(schemaKey, v, table)
    })
    // 전체가 비어있는 행은 제외
    if (Object.values(obj).some((v) => v !== null && v !== '')) {
      rows.push(obj)
      rowNumbers.push(rowNumber)
    }
  })

  if (rows.length === 0) {
    return {
      success: false,
      message: `데이터가 없습니다. 또는 모든 행이 빈 값이었습니다. unknownHeaders=${JSON.stringify(unknownHeaders)}`,
    }
  }

  // 3) 사전 검증: 문자열 길이 제한 초과 등
  const columnMeta: Record<string, { maxLength?: number; dataType?: string }> = {}
  for (const key of schemaKeys) {
    const col: any = (table as any)[key]
    const maxLength = col?.config?.length ?? col?._?.config?.length
    const dataType = col?.dataType ?? col?.columnType ?? col?._?.dataType
    columnMeta[key] = { maxLength, dataType }
  }

  const validationErrors: Array<{ row: number; column: string; reason: string; value?: any; length?: number; max?: number }> = []
  rows.forEach((r, idx) => {
    for (const [k, v] of Object.entries(r)) {
      const meta = columnMeta[k] || {}
      if (v == null) continue
      const s = String(v)
      if (meta.maxLength && s.length > meta.maxLength) {
        validationErrors.push({ row: rowNumbers[idx], column: k, reason: 'length_exceeded', value: s, length: s.length, max: meta.maxLength })
      }
    }
  })

  if (validationErrors.length > 0) {
    return { success: false, message: '유효성 검사 실패 (길이 초과 등)', unknownHeaders, errors: validationErrors }
  }

  try {
    const primaryKeys = getPrimaryKeysFromSchema(tableName)
    
    if (primaryKeys.length > 0 && rows.length > 0) {
      // UPSERT 전 기존 데이터 개수 확인
      const beforeCount = await getSingleTableCount(tableName)
      
      // 모든 PK 값이 존재하는 행만 처리
      const validRows = rows.filter(row => {
        const pkValues = primaryKeys.map(pk => row[pk]).filter(val => val != null && val !== '')
        return pkValues.length === primaryKeys.length
      })
      
      let actualProcessedCount = 0
      
      if (validRows.length > 0) {
        if (primaryKeys.length === 1) {
          // 단일 PK: Drizzle의 onConflictDoUpdate 사용
          const pkColumn = primaryKeys[0]
          const targetColumn = (table as any)[pkColumn]
          
          if (targetColumn) {
            const firstRow = validRows[0]
            const updateFields = Object.keys(firstRow)
              .filter(key => key !== pkColumn && key !== 'id')
              .reduce((acc, key) => {
                acc[key] = sql`excluded.${sql.identifier(key)}`
                return acc
              }, {} as Record<string, any>)
            
            // 배치 내 중복 PK 제거 (마지막 행만 유지)
            const deduplicatedRows = validRows.reduce((acc, row) => {
              const pkValue = row[pkColumn]
              acc[pkValue] = row // 같은 PK면 마지막 행으로 덮어씀
              return acc
            }, {} as Record<string, any>)
            
            const uniqueRows = Object.values(deduplicatedRows)
            actualProcessedCount = uniqueRows.length
            console.log(`[DEBUG] 단일 PK 중복 제거: ${validRows.length}행 -> ${uniqueRows.length}행`)
            
            // 배치 처리 (500개씩)
            const chunkSize = 500
            for (let i = 0; i < uniqueRows.length; i += chunkSize) {
              const chunk = uniqueRows.slice(i, i + chunkSize)
              await db.insert(table)
                .values(chunk as any)
                .onConflictDoUpdate({
                  target: targetColumn,
                  set: updateFields,
                })
            }
          }
        } else {
          // 복합 PK: 진짜 UPSERT 사용
          const processedCount = await handleCompositeKeyProperUpsert(table, tableName, validRows, primaryKeys)
          actualProcessedCount = processedCount
        }
      }
      
      // UPSERT 후 실제 데이터 개수 확인
      const afterCount = await getSingleTableCount(tableName)
      
      // 실제 추가된 행 수 vs 업데이트된 행 수 계산 (중복 제거 후 개수 사용)
      const actualInserted = afterCount - beforeCount
      const actualUpdated = actualProcessedCount - actualInserted
      const skipped = rows.length - validRows.length
      const duplicatesRemoved = validRows.length - actualProcessedCount
      
      revalidatePath('/sap-import')
      return { 
        success: true, 
        processed: actualProcessedCount,
        inserted: actualInserted,
        updated: actualUpdated,
        skipped: skipped,
        duplicatesRemoved: duplicatesRemoved,
        total: rows.length,
        unknownHeaders,
        primaryKeys,
        upsertMode: true
      }
    } else {
      // PK가 정의되지 않은 테이블은 기존 INSERT 방식
      await db.insert(table).values(rows as any)
      revalidatePath('/sap-import')
      return { success: true, inserted: rows.length, unknownHeaders, upsertMode: false }
    }
  } catch (e: any) {
    return { success: false, message: e?.message ?? 'DB 입력 실패', unknownHeaders }
  }
}

function camelFromTable(name: string) {
  // TB_SAP_MaterialInfo -> tbSAPMaterialInfo (export된 심볼명)
  const parts = name.split('_')
  const camel = parts
    .map((p, i) => (i === 0 ? p.toLowerCase() : p[0] + p.slice(1)))
    .join('')
  return camel
}

/**
 * 스키마에서 PK 코멘트(// pk)를 분석하여 Primary Key 컬럼들을 추출
 */
function getPrimaryKeysFromSchema(tableName: string): string[] {
  const pkMap: Record<string, string[]> = {
    // 단일 PK 테이블들
    'TB_SAP_EquipInfo': ['Equipment'],
    'TB_SAP_Order': ['Order'],
    'TB_SAP_OrderNotice': ['Notification'],
    'TB_SAP_OrderBreakdown': ['Order'], // pk 코멘트 확인됨
    'TB_SAP_MainternanceBOM': ['Date', 'Order', 'Component'], // 복합 PK
    'TB_SAP_MaterialRepair': ['Order'],
    
    // 복합 PK 테이블들
    'TB_SAP_OrderConfirm': ['Confirmation', 'Count'],
    'TB_SAP_MaterialInfo': ['Plnt', 'Material', 'Sloc'], // 복합 PK
    'TB_SAP_MaterialStock': ['Material', 'Batch', 'StorageLo'], // 복합 PK
    'TB_SAP_MaterialRelease': ['PostingGIdate', 'Plant', 'StorageLoc', 'MaterialDocument', 'DocumentYear'], // 복합 PK
    'TB_SAP_MaterialReceiving': ['PostingGRDate', 'Plant', 'StorageLoc', 'MatDoc', 'Year'], // 복합 PK
    
    // 새로 추가된 테이블들
    'Technical_Guidance_Investment': ['FUMNR', 'FUMIT'], // 복합 PK
    'Technical_Guidance_Order': ['AUFNR', 'VORNR'], // 복합 PK
  }
  
  return pkMap[tableName] || []
}

/**
 * 복합 PK를 위한 안전한 UPSERT (ON CONFLICT DO UPDATE)
 */
async function handleCompositeKeyProperUpsert(
  table: any, 
  tableName: string, 
  rows: any[], 
  primaryKeys: string[]
): Promise<number> {
  // 복합 PK 컬럼들을 가져오기
  const targetColumns = primaryKeys.map(pk => (table as any)[pk]).filter(Boolean)
  
  if (targetColumns.length !== primaryKeys.length) {
    throw new Error(`[${tableName}] PK 컬럼을 찾을 수 없습니다: ${primaryKeys.join(', ')}`)
  }
  
  // UPDATE할 필드들 (PK와 id 제외)
  const firstRow = rows[0]
  const updateFields = Object.keys(firstRow)
    .filter(key => !primaryKeys.includes(key) && key !== 'id')
    .reduce((acc, key) => {
      acc[key] = sql`excluded.${sql.identifier(key)}`
      return acc
    }, {} as Record<string, any>)
  
  // 배치 내 중복 PK 제거 (마지막 행만 유지)
  const deduplicatedRows = rows.reduce((acc, row) => {
    const pkKey = primaryKeys.map(pk => row[pk]).join('|')
    acc[pkKey] = row // 같은 PK면 마지막 행으로 덮어씀
    return acc
  }, {} as Record<string, any>)
  
  const uniqueRows = Object.values(deduplicatedRows)
  console.log(`[DEBUG] 중복 제거: ${rows.length}행 -> ${uniqueRows.length}행`)
  
  // 배치 처리 (500개씩)
  const chunkSize = 500
  for (let i = 0; i < uniqueRows.length; i += chunkSize) {
    const chunk = uniqueRows.slice(i, i + chunkSize)
    await db.insert(table)
      .values(chunk as any)
      .onConflictDoUpdate({
        target: targetColumns,
        set: updateFields,
      })
  }
  
  return uniqueRows.length
}


// 날짜/숫자/문자 값을 컬럼 특성에 맞게 문자열로 정규화
function normalizeCellValue(column: string, v: unknown, table?: any) {
  if (v == null) return null
  
  // 빈 문자열 처리 (real, integer, bigint 타입에서는 null로 변환)
  const s = String(v).trim()
  if (s === '') {
    return null
  }
  
  // 헤더 행 감지: 컬럼명과 동일한 값이 오면 null 처리 (헤더가 데이터로 잘못 인식된 경우)
  if (s === column) {
    return null
  }
  
  // 컬럼 타입 확인
  const columnDef = table?.[column]
  const actualDataType = columnDef?.config?.columnType
  
  // 숫자 타입 컬럼인지 확인 (bigint, integer 등)
  const isNumericColumn = actualDataType?.includes('BigInt') || actualDataType?.includes('Integer')
  
  if (isNumericColumn) {
    // 숫자로 변환 가능한지 확인
    const numericValue = s.replace(/[,\s]/g, '') // 쉼표와 공백 제거
    if (!/^\d+$/.test(numericValue)) {
      return null // 숫자가 아니면 null
    }
    return numericValue
  }
  
  // Date 객체거나 날짜처럼 보이는 문자열/숫자는 yyyy-MM-dd로
  if (v instanceof Date) {
    return formatDate10(v)
  }
  
  // 시간 형식 처리 (예: "9시 42분 0초" -> "09:42:00")
  const timeFormatMatch = s.match(/^(\d{1,2})시\s*(\d{1,2})분\s*(\d{1,2})초$/)
  if (timeFormatMatch) {
    const hour = String(Number(timeFormatMatch[1])).padStart(2, '0')
    const minute = String(Number(timeFormatMatch[2])).padStart(2, '0')
    const second = String(Number(timeFormatMatch[3])).padStart(2, '0')
    return `${hour}:${minute}:${second}`
  }
  
  // 쉼표가 포함된 숫자 처리 (예: "117,733" -> "117733")
  const commaNumberMatch = s.match(/^"?([0-9,]+)"?$/)
  if (commaNumberMatch) {
    return commaNumberMatch[1].replace(/,/g, '')
  }
  
  // 엑셀에서 날짜가 숫자(시리얼)로 올 수 있음
  if (typeof v === 'number' && v > 20000 && v < 80000) {
    // Excel serial date (roughly)
    const epoch = new Date(1899, 11, 30)
    const d = new Date(epoch.getTime() + v * 86400000)
    return formatDate10(d)
  }
  
  // 날짜 패턴들: 2025-06-09, 2025/06/09, 2025.06.09
  const dateMatch = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})$/)
  if (dateMatch) {
    const y = Number(dateMatch[1])
    const mm = String(Number(dateMatch[2])).padStart(2, '0')
    const dd = String(Number(dateMatch[3])).padStart(2, '0')
    return `${y}-${mm}-${dd}`
  }
  
  return s
}

function formatDate10(d: Date) {
  const y = d.getFullYear()
  const m = String(d.getMonth() + 1).padStart(2, '0')
  const day = String(d.getDate()).padStart(2, '0')
  return `${y}-${m}-${day}` // 10자
}