// Pure parser for Workshop-Erfassung Excel imports.
//
// Template columns: Station | Beobachtete CT (s) | Mögliche CT (s) | Bemerkung
//
// Station matching: case-insensitive trim against the project's process_steps.station_name.
// Unknown stations → Fehlerbericht.
// Already-captured stations → Duplikatwarnung with update option.
// Formula injection protection: cells starting with =, +, -, @ are rejected.
//
// Follows the pattern from lib/master-data/excel-service.ts.

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

/**
 * Sanitize a string cell value for formula injection.
 * Returns the value with a leading apostrophe if it starts with a formula prefix.
 * The return value signals that the cell is "escaped" — callers should check
 * whether the original value was a formula and add an error entry accordingly.
 */
export function sanitizeFormulaCell(value: string): string {
  if (FORMULA_PREFIXES.some((p) => value.startsWith(p))) {
    return "'" + value
  }
  return value
}

/** Returns true if a string is a formula-injection attempt. */
export function isFormulaValue(value: string): boolean {
  return FORMULA_PREFIXES.some((p) => value.startsWith(p))
}

/** Raw data extracted from one Excel row before validation. */
export interface WorkshopImportRow {
  stationName: string
  observedCtRaw: string
  possibleCtRaw: string
  comment: string
  rowIndex: number
}

/** Validated, matched row ready for database upsert. */
export interface ParsedWorkshopRow {
  action: 'create' | 'update' | 'skip'
  processStepId: string
  stationName: string
  observed_ct_sec: number | null
  possible_ct_sec: number | null
  comment: string | null
  rowIndex: number
}

export interface WorkshopImportResult {
  rows: ParsedWorkshopRow[]
  errors: string[]
}

function parseNum(raw: string): number | null {
  const trimmed = raw.trim()
  if (trimmed === '') return null
  const n = Number(trimmed.replace(',', '.'))
  return Number.isFinite(n) ? n : null
}

/**
 * Parse and validate raw import rows against the project's station list.
 *
 * @param rows         Raw rows extracted from the Excel file (header already skipped).
 * @param stations     Map of lower-cased station_name → process_step.id for the project.
 * @param existingStepIds  Set of process_step IDs that already have observation data.
 */
export function parseWorkshopImportRows(
  rows: WorkshopImportRow[],
  stations: Map<string, string>,
  existingStepIds: Set<string> = new Set(),
): WorkshopImportResult {
  const errors: string[] = []
  const parsed: ParsedWorkshopRow[] = []
  const seenStations = new Set<string>()

  for (const row of rows) {
    const { rowIndex } = row

    // Formula-injection check on station name
    const rawStation = row.stationName.trim()
    if (isFormulaValue(rawStation)) {
      errors.push(
        `Zeile ${rowIndex}: Stationsname enthält mögliche Formel-Injection ('${rawStation}') — Zeile übersprungen`,
      )
      continue
    }

    // Formula-injection check on CT fields
    if (isFormulaValue(row.observedCtRaw.trim())) {
      errors.push(
        `Zeile ${rowIndex}: 'Beobachtete CT' enthält mögliche Formel-Injection — Zeile übersprungen`,
      )
      continue
    }
    if (isFormulaValue(row.possibleCtRaw.trim())) {
      errors.push(
        `Zeile ${rowIndex}: 'Mögliche CT' enthält mögliche Formel-Injection — Zeile übersprungen`,
      )
      continue
    }

    // Station matching (case-insensitive trim)
    const normalizedName = rawStation.toLowerCase()
    const processStepId = stations.get(normalizedName)
    if (!processStepId) {
      errors.push(
        `Zeile ${rowIndex}: Station '${rawStation}' konnte nicht zugeordnet werden — Zeile übersprungen`,
      )
      continue
    }

    // Duplicate station within this import file
    if (seenStations.has(normalizedName)) {
      errors.push(
        `Zeile ${rowIndex}: Station '${rawStation}' ist in der Importdatei doppelt — Zeile übersprungen`,
      )
      continue
    }
    seenStations.add(normalizedName)

    // Parse numeric values
    const observed_ct_sec = parseNum(row.observedCtRaw)
    if (row.observedCtRaw.trim() !== '' && observed_ct_sec === null) {
      errors.push(
        `Zeile ${rowIndex}: 'Beobachtete CT' ist keine gültige Zahl ('${row.observedCtRaw}') — Zeile übersprungen`,
      )
      continue
    }

    const possible_ct_sec = parseNum(row.possibleCtRaw)
    if (row.possibleCtRaw.trim() !== '' && possible_ct_sec === null) {
      errors.push(
        `Zeile ${rowIndex}: 'Mögliche CT' ist keine gültige Zahl ('${row.possibleCtRaw}') — Zeile übersprungen`,
      )
      continue
    }

    const action: 'create' | 'update' = existingStepIds.has(processStepId)
      ? 'update'
      : 'create'

    parsed.push({
      action,
      processStepId,
      stationName: rawStation,
      observed_ct_sec,
      possible_ct_sec,
      comment: row.comment.trim() || null,
      rowIndex,
    })
  }

  return { rows: parsed, errors }
}