"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 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) 스키마 컬럼 수집 및 정규화 맵 구성
  const schemaKeys = Object.keys(table)
    .filter((k) => !k.startsWith('_') && k !== 'getSQL' && k !== '[$$schema]' && k !== 'id')
  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[]
  if (mappedColumns.length === 0) {
    return {
      success: false,
      message: `헤더가 테이블 컬럼과 일치하지 않습니다. unknownHeaders=${JSON.stringify(unknownHeaders)}`,
    }
  }

  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)
    })
    // 전체가 비어있는 행은 제외
    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 {
    // @ts-expect-error drizzle insert
    await db.insert(table).values(rows)
    revalidatePath('/sap-import')
    return { success: true, inserted: rows.length, unknownHeaders }
  } 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
}

// 날짜/숫자/문자 값을 컬럼 특성에 맞게 문자열로 정규화
function normalizeCellValue(column: string, v: unknown) {
  if (v == null) return null
  // Date 객체거나 날짜처럼 보이는 문자열/숫자는 yyyy-MM-dd로
  if (v instanceof Date) {
    return formatDate10(v)
  }
  // 엑셀에서 날짜가 숫자(시리얼)로 올 수 있음
  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)
  }
  const s = String(v).trim()
  // 간단한 날짜 패턴들: 2025-06-09, 2025/06/09, 2025.06.09
  const m = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})$/)
  if (m) {
    const y = Number(m[1])
    const mm = String(Number(m[2])).padStart(2, '0')
    const dd = String(Number(m[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자
}