import ExcelJS from 'exceljs'

const BMW_BLUE_ARGB = 'FF0066B1'
const WHITE_ARGB = 'FFFFFFFF'
const GREY_ARGB = 'FFAAAAAA'

/** Characters that signal a spreadsheet formula injection attempt. */
const FORMULA_PREFIXES = ['=', '+', '-', '@'] as const

/**
 * Sanitize a string cell value against formula injection.
 * If the string starts with a formula-injection prefix (=, +, -, @), a leading
 * apostrophe is prepended so spreadsheet software treats it as plain text.
 *
 * Exported for unit testing.
 */
export function sanitizeMasterDataCell(value: string): string {
  if (FORMULA_PREFIXES.some((p) => value.startsWith(p))) {
    return "'" + value
  }
  return value
}

interface MasterDataRow {
  code: string
  label: string
  sort_order: number
  is_active: boolean
}

export async function downloadMasterDataExcel(
  typeLabel: string,
  rows: MasterDataRow[],
): Promise<void> {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet(typeLabel.substring(0, 31)) // Excel sheet name max 31 chars

  ws.columns = [
    { header: 'Code',        key: 'code',       width: 20 },
    { header: 'Bezeichnung', key: 'label',      width: 40 },
    { header: 'Sortierung',  key: 'sort_order', width: 12 },
    { header: 'Aktiv',       key: 'is_active',  width: 10 },
  ]

  ws.getRow(1).eachCell((cell) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: BMW_BLUE_ARGB } }
    cell.font = { color: { argb: WHITE_ARGB }, bold: true }
    cell.alignment = { vertical: 'middle' }
  })

  ws.views = [{ state: 'frozen', ySplit: 1 }]

  if (rows.length === 0) {
    const exRow = ws.addRow({
      code: 'BEISPIEL',
      label: 'Beispiel Bezeichnung',
      sort_order: 10,
      is_active: true,
    })
    exRow.eachCell((cell) => {
      cell.font = { color: { argb: GREY_ARGB }, italic: true }
    })
  } else {
    rows.forEach((r) => ws.addRow(r))
  }

  const buf = await wb.xlsx.writeBuffer()
  const blob = new Blob([buf], {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  })
  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = `${typeLabel.replace(/[^a-zA-Z0-9äöüÄÖÜß]/g, '_')}.xlsx`
  a.click()
  URL.revokeObjectURL(url)
}

export interface ImportPreviewRow {
  action: 'create' | 'update' | 'skip'
  code: string
  label: string
  sort_order: number
  is_active: boolean
  reason?: string
}

export async function parseMasterDataExcel(
  file: File,
  existingCodes: Set<string>,
): Promise<{ preview: ImportPreviewRow[]; errors: string[] }> {
  const buf = await file.arrayBuffer()
  const wb = new ExcelJS.Workbook()
  await wb.xlsx.load(buf)
  const ws = wb.worksheets[0]
  const errors: string[] = []
  const preview: ImportPreviewRow[] = []
  const seenCodes = new Set<string>()

  ws.eachRow((row, idx) => {
    if (idx === 1) return // skip header row

    // Prefer cell.result for formula cells to get the computed value.
    const cell1 = row.getCell(1)
    const rawCode = String(
      (cell1.type === ExcelJS.ValueType.Formula && cell1.result != null
        ? cell1.result
        : cell1.value) ?? '',
    ).trim()

    const cell2 = row.getCell(2)
    const rawLabel = String(
      (cell2.type === ExcelJS.ValueType.Formula && cell2.result != null
        ? cell2.result
        : cell2.value) ?? '',
    ).trim()

    // Formula-injection check: reject cells that start with injection prefixes.
    if (rawCode && FORMULA_PREFIXES.some((p) => rawCode.startsWith(p))) {
      errors.push(`Zeile ${idx}: Code '${rawCode}' enthält mögliche Formel-Injection — Zeile übersprungen`)
      return
    }
    if (rawLabel && FORMULA_PREFIXES.some((p) => rawLabel.startsWith(p))) {
      errors.push(`Zeile ${idx}: Bezeichnung '${rawLabel}' enthält mögliche Formel-Injection — Zeile übersprungen`)
      return
    }

    const code = sanitizeMasterDataCell(rawCode)
    const label = sanitizeMasterDataCell(rawLabel)

    const sortRaw = row.getCell(3).value
    const sort_order =
      typeof sortRaw === 'number'
        ? sortRaw
        : parseInt(String(sortRaw ?? idx * 10), 10) || idx * 10
    const isActiveRaw = row.getCell(4).value
    const is_active =
      isActiveRaw === false || isActiveRaw === 'false' || isActiveRaw === 0
        ? false
        : true

    if (!code) { errors.push(`Zeile ${idx}: Code fehlt`); return }
    if (!label) { errors.push(`Zeile ${idx}: Bezeichnung fehlt`); return }
    if (seenCodes.has(code)) { errors.push(`Zeile ${idx}: Code '${code}' doppelt`); return }
    seenCodes.add(code)

    preview.push({
      action: existingCodes.has(code) ? 'update' : 'create',
      code,
      label,
      sort_order,
      is_active,
    })
  })

  return { preview, errors }
}