// Catalog import: parse xlsx → validate → preview rows → execute upsert
// Uses ExcelJS for parsing
import type { AssessmentMainCategory, AssessmentSubCategory } from './assessment-types'

// Column indices in the xlsx (0-based, matching export template)
const COL_INDEX    = 0
const COL_MAIN     = 1
const COL_SUB      = 2
const COL_QUESTION = 3
const COL_A1       = 4
const COL_A2       = 5
const COL_A3       = 6
const COL_A4       = 7
const COL_ACTIVE   = 8

export interface ImportRow {
  rowNum: number
  index_number: number | null
  main_category_code: string
  sub_category_code: string
  question_text: string
  answer_text_1: string
  answer_text_2: string
  answer_text_3: string
  answer_text_4: string
  is_active: boolean
  main_category_id: string | null
  sub_category_id: string | null
  status: 'new' | 'update' | 'error'
  errors: string[]
}

function cellStr(v: unknown): string {
  if (v === null || v === undefined) return ''
  return String(v).trim()
}

function parseActive(v: unknown): boolean {
  if (v === null || v === undefined || v === '') return true
  const s = String(v).toLowerCase().trim()
  return s !== 'nein' && s !== 'false' && s !== '0' && s !== 'no'
}

export async function parseCatalogFile(
  file: File,
  mainCats: AssessmentMainCategory[],
  subCats: AssessmentSubCategory[],
  existingIndexes: Set<number>,
): Promise<ImportRow[]> {
  const ExcelJS = (await import('exceljs')).default
  const ab      = await file.arrayBuffer()
  const ejWb    = new ExcelJS.Workbook()
  await ejWb.xlsx.load(ab)
  const ws = ejWb.worksheets[0]

  if (!ws) throw new Error('Keine Arbeitsmappe gefunden in der Datei.')

  // Build a 2-D array of cell values (header row included), mimicking sheet_to_json({header:1})
  const raw: unknown[][] = []
  ws.eachRow({ includeEmpty: false }, (row) => {
    const cols: unknown[] = []
    row.eachCell({ includeEmpty: true }, (cell, colNum) => {
      cols[colNum - 1] = cell.value ?? ''
    })
    raw.push(cols)
  })
  if (raw.length < 2) throw new Error('Die Datei enthält keine Datenzeilen.')

  const mainByCode = new Map(mainCats.map((m) => [m.code.toUpperCase(), m]))
  const subByCode  = new Map(subCats.map((s) => [s.code.toUpperCase(), s]))

  // Track indexes seen in this file for duplicate detection
  const fileIndexes = new Map<number, number>() // index_number → first rowNum

  const result: ImportRow[] = []

  for (let i = 1; i < raw.length; i++) {
    const cols      = raw[i] as unknown[]
    const rowNum    = i + 1  // Excel row (1-based, header=1, data starts at 2)

    // Skip completely empty rows
    const allEmpty = cols.every((c) => cellStr(c) === '')
    if (allEmpty) continue

    const errors: string[] = []

    // Parse index
    const rawIdx     = cols[COL_INDEX]
    const indexNum   = rawIdx !== '' ? Number(rawIdx) : NaN
    const indexValid = !isNaN(indexNum) && Number.isInteger(indexNum) && indexNum > 0

    if (!indexValid) {
      errors.push(`Index ungültig: "${rawIdx}" (muss eine positive ganze Zahl sein)`)
    }

    const mainCode   = cellStr(cols[COL_MAIN]).toUpperCase()
    const subCode    = cellStr(cols[COL_SUB]).toUpperCase()
    const question   = cellStr(cols[COL_QUESTION])
    const a1         = cellStr(cols[COL_A1])
    const a2         = cellStr(cols[COL_A2])
    const a3         = cellStr(cols[COL_A3])
    const a4         = cellStr(cols[COL_A4])
    const isActive   = parseActive(cols[COL_ACTIVE])

    if (!mainCode) errors.push('Hauptkategorie fehlt.')
    if (!subCode)  errors.push('Unterkategorie fehlt.')
    if (!question) errors.push('Frage fehlt.')
    if (!a1)       errors.push('Antwort 1 fehlt.')
    if (!a2)       errors.push('Antwort 2 fehlt.')
    if (!a3)       errors.push('Antwort 3 fehlt.')
    if (!a4)       errors.push('Antwort 4 fehlt.')

    // Category lookups
    const mainCat = mainByCode.get(mainCode)
    const subCat  = subByCode.get(subCode)

    if (mainCode && !mainCat) {
      errors.push(`Hauptkategorie "${mainCode}" nicht bekannt.`)
    }
    if (subCode && !subCat) {
      errors.push(`Unterkategorie "${subCode}" nicht bekannt.`)
    }
    if (mainCat && subCat && subCat.main_category_id !== mainCat.id) {
      errors.push(`Unterkategorie "${subCode}" gehört nicht zu "${mainCode}".`)
    }

    // Duplicate index within file
    if (indexValid) {
      if (fileIndexes.has(indexNum)) {
        errors.push(`Index ${indexNum} ist in dieser Datei doppelt (zuerst in Zeile ${fileIndexes.get(indexNum)}).`)
      } else {
        fileIndexes.set(indexNum, rowNum)
      }
    }

    const status: ImportRow['status'] =
      errors.length > 0              ? 'error'
      : existingIndexes.has(indexNum) ? 'update'
      : 'new'

    result.push({
      rowNum,
      index_number:       indexValid ? indexNum : null,
      main_category_code: mainCode,
      sub_category_code:  subCode,
      question_text:      question,
      answer_text_1:      a1,
      answer_text_2:      a2,
      answer_text_3:      a3,
      answer_text_4:      a4,
      is_active:          isActive,
      main_category_id:   mainCat?.id ?? null,
      sub_category_id:    subCat?.id  ?? null,
      status,
      errors,
    })
  }

  return result
}