GuDingZiChan/backend/scripts/sheet2PublicSupplementUtils.js

const { resolveStorageLocationMatch } = require('./locationNormalization')

const SHEET2_LOCATION_OVERRIDES = {
  山东: '济南',
  运营中心: '常州-天安',
  客服中心: '常州-天安',
  人力行政中心: '常州-天安',
  庙西宿舍: '常州-仓库',
  财务中心: '常州-天安',
  天安17楼: '常州-天安',
  运营中心B: '常州-天安',
  供应链中心: '常州-天安',
  '2楼': '常州-天安',
  湖塘摄影棚: '影棚',
  睢宁仓: '苏北仓库',
  横林二仓仓库: '常州-仓库',
  横山桥: '常州-仓库',
  客服二部: '常州-天安',
  苏北仓: '苏北仓库',
  仓储中心: '常州-仓库',
  横林二仓二楼会议室: '常州-仓库',
  横林二仓二楼张总办公室: '常州-仓库',
  红星美凯龙: '常州-美凯龙',
  天安三楼: '常州-天安',
  '1楼 客服四部': '常州-天安',
  '3楼 智管信息中心': '常州-天安',
  常州运c: '常州-天安',
  二楼: '常州-天安',
  分销外贸: '常州-天安',
  佛山顺德仓: '佛山-顺德仓库',
  河北仓: '河北-仓库',
  横林二仓采购办公室: '常州-仓库',
  横林二仓仓库办公室: '常州-仓库',
  横林二仓二楼顾总办公室: '常州-仓库',
  横林二仓二楼贾总办公室: '常州-仓库',
  横林二仓二楼朱总办公室: '常州-仓库',
  横林二仓一楼仓储办公室: '常州-仓库',
  客服六部: '常州-天安',
  三楼: '常州-天安',
  山东曹县: '山东-曹县',
  事业一部: '常州-天安',
  天安10号楼三楼机房: '常州-天安',
  天安10号楼四楼厨房: '常州-天安',
  天安10号楼一楼机房: '常州-天安',
  天安四楼会议室: '常州-天安',
  运营八部: '常州-天安',
  运营十部: '常州-天安',
  总经理室: '常州-天安'
}

const SHEET2_LOCATION_PREFIXES = ['仓储一部', '仓储二部', '仓储三部']

const normalizeText = (value) =>
  String(value == null ? '' : value)
    .replace(/\r/g, ' ')
    .replace(/\n/g, ' ')
    .replace(/\s+/g, ' ')
    .trim()

const normalizeLookupKey = (value) => normalizeText(value).toUpperCase()

const stripSheet2LocationPrefix = (rawValue) => {
  const normalized = normalizeText(rawValue)
  if (!normalized) {
    return {
      rawValue: '',
      strippedValue: '',
      prefix: '',
      changed: false
    }
  }

  for (const prefix of SHEET2_LOCATION_PREFIXES) {
    const marker = `${prefix} `
    if (normalized.startsWith(marker)) {
      const strippedValue = normalizeText(normalized.slice(marker.length))
      return {
        rawValue: normalized,
        strippedValue,
        prefix,
        changed: strippedValue !== normalized
      }
    }
  }

  return {
    rawValue: normalized,
    strippedValue: normalized,
    prefix: '',
    changed: false
  }
}

const resolveSheet2LocationMatch = (rawValue, locationByName) => {
  const normalized = normalizeText(rawValue)
  if (!normalized) {
    return {
      rawValue: '',
      normalizedValue: '',
      match: null,
      rule: ''
    }
  }

  const directOverride = SHEET2_LOCATION_OVERRIDES[normalized]
  if (directOverride) {
    const match = locationByName.get(normalizeLookupKey(directOverride)) || null
    return {
      rawValue: normalized,
      normalizedValue: directOverride,
      match,
      rule: 'sheet2-confirmed-override'
    }
  }

  const directMatch = resolveStorageLocationMatch(normalized, locationByName)
  if (directMatch.match) {
    return directMatch
  }

  const stripped = stripSheet2LocationPrefix(normalized)
  if (!stripped.changed) {
    return directMatch
  }

  const strippedOverride = SHEET2_LOCATION_OVERRIDES[stripped.strippedValue]
  if (strippedOverride) {
    const match = locationByName.get(normalizeLookupKey(strippedOverride)) || null
    return {
      rawValue: normalized,
      normalizedValue: strippedOverride,
      match,
      rule: 'sheet2-prefix-override'
    }
  }

  const strippedMatch = resolveStorageLocationMatch(stripped.strippedValue, locationByName)
  if (strippedMatch.match) {
    return {
      rawValue: normalized,
      normalizedValue: strippedMatch.normalizedValue,
      match: strippedMatch.match,
      rule: 'sheet2-prefix-alias'
    }
  }

  return {
    rawValue: normalized,
    normalizedValue: stripped.strippedValue,
    match: null,
    rule: 'sheet2-prefix-stripped'
  }
}

const mergeCandidateRowsWithSourceCodes = (candidateRows, sourceRows, assetCodes) => {
  const normalizedCodes = [...new Set((assetCodes || []).map((code) => normalizeText(code).toUpperCase()).filter(Boolean))]
  if (!normalizedCodes.length) {
    return {
      rows: [...candidateRows],
      missingCodes: [],
      duplicateSourceCodes: []
    }
  }

  const sourceRowsByCode = new Map()
  for (const row of sourceRows || []) {
    const code = normalizeText(row.资产编码).toUpperCase()
    if (!code) {
      continue
    }
    if (!sourceRowsByCode.has(code)) {
      sourceRowsByCode.set(code, [])
    }
    sourceRowsByCode.get(code).push(row)
  }

  const existingCodes = new Set(
    (candidateRows || []).map((row) => normalizeText(row.资产编码).toUpperCase()).filter(Boolean)
  )

  const rows = [...candidateRows]
  const missingCodes = []
  const duplicateSourceCodes = []

  for (const code of normalizedCodes) {
    if (existingCodes.has(code)) {
      continue
    }

    const sourceCodeRows = sourceRowsByCode.get(code) || []
    if (!sourceCodeRows.length) {
      missingCodes.push(code)
      continue
    }

    if (sourceCodeRows.length > 1) {
      duplicateSourceCodes.push({
        code,
        rows: sourceCodeRows.map((row) => ({
          ...row,
          来源工作表: normalizeText(row.来源工作表) || 'Sheet2'
        }))
      })
      continue
    }

    const sourceRow = sourceCodeRows[0]

    rows.push({
      ...sourceRow,
      来源工作表: normalizeText(sourceRow.来源工作表) || 'Sheet2'
    })
    existingCodes.add(code)
  }

  return {
    rows,
    missingCodes,
    duplicateSourceCodes
  }
}

module.exports = {
  SHEET2_LOCATION_OVERRIDES,
  SHEET2_LOCATION_PREFIXES,
  mergeCandidateRowsWithSourceCodes,
  normalizeText,
  normalizeLookupKey,
  resolveSheet2LocationMatch,
  stripSheet2LocationPrefix
}