import * as XLSX from 'xlsx'
import type { ConsoleDataType } from '../api/consoleData'
import { parseTestDuration } from './testDataDuration.ts'

export const consoleImportColumns = {
  REALTIME: { recordNo: '记录编号', batteryNo: '电池', cycleNo: '循环序号', stepNo: '工步序号', stepName: '工步名称', sequenceId: '记录序号', relativeTimeSeconds: '记录时间(h:min:s.ms)', voltage: '电压(V)', current: '电流(mA)', capacity: '容量(mAh)', energy: '能量(mWh)', recordedAt: '绝对时间', temperature: '温度(℃)', positivePotential: '正电位(V)', negativePotential: '负电位(V)' },
  STEP: { recordNo: '记录编号', batteryNo: '电池', cycleNo: '循环序号', stepNo: '工步序号', stepName: '工步名称', stepDuration: '工步时间(h:min:s.ms)', capacity: '容量(mAh)', energy: '能量(mWh)', startVoltage: '起始电压(V)', endVoltage: '终止电压(V)', chargeMedianVoltage: '充电中值电压(V)', dischargeMedianVoltage: '放电中值电压(V)' },
  CYCLE: { recordNo: '记录编号', batteryNo: '电池', cycleNo: '循环序号', chargeCapacity: '充电容量(mAh)', dischargeCapacity: '放电容量(mAh)', efficiency: '充放电效率(%)', chargeEnergy: '充电能量(mWh)', dischargeEnergy: '放电能量(mWh)' },
} satisfies Record<ConsoleDataType, Record<string, string>>

export interface ValidatedImportRow {
  sourceRow: number
  raw: Record<string, unknown>
  errors: string[]
  data: Record<string, string | number | null>
}

const sheetNames = { REALTIME: ['实时', '实时模板', 'record'], STEP: ['工步', '工步模板', 'step'], CYCLE: ['循环', '循环模板', 'cycle'] }
export const consoleSheetLabels: Record<ConsoleDataType, string> = { REALTIME: '实时', STEP: '工步', CYCLE: '循环' }
export interface ValidatedImportSheet {
  type: ConsoleDataType
  rows: ValidatedImportRow[]
  errors: string[]
}

/** 创建包含三个标准列头的空工作簿，不生成测量数据。 */
export function createConsoleImportWorkbook(selectedType?: ConsoleDataType): XLSX.WorkBook {
  const workbook = XLSX.utils.book_new()
  for (const type of selectedType ? [selectedType] : Object.keys(consoleSheetLabels) as ConsoleDataType[]) {
    XLSX.utils.book_append_sheet(workbook, XLSX.utils.aoa_to_sheet([Object.values(consoleImportColumns[type])]), consoleSheetLabels[type])
  }
  // 单页汇总没有绝对时间，辅助页只供匹配，不隐式导入实时数据。
  if (selectedType && selectedType !== 'REALTIME') XLSX.utils.book_append_sheet(workbook, XLSX.utils.aoa_to_sheet([Object.values(consoleImportColumns.REALTIME)]), 'record')
  return workbook
}

/** 三表独立校验并汇总错误；一键导入不使用单页的任意名称回退规则。 */
export function parseConsoleImportWorkbook(workbook: XLSX.WorkBook, batteries: string[], selectedBattery = ''): ValidatedImportSheet[] {
  const sheets = (Object.keys(consoleSheetLabels) as ConsoleDataType[]).map(type => {
    const result: ValidatedImportSheet = { type, rows: [], errors: [] }
    const names = workbook.SheetNames.filter(name => sheetNames[type].includes(name) && workbook.Sheets[name])
    try {
      if (!names.length) throw new Error(`缺少${consoleSheetLabels[type]}工作表`)
      if (names.length > 1) throw new Error(`存在多个${consoleSheetLabels[type]}工作表，无法确定导入来源`)
      result.rows = parseConsoleImport(workbook, type, batteries, selectedBattery)
      if (!result.rows.length) result.errors.push('工作表没有可导入的数据')
    } catch (error) { result.errors.push(error instanceof Error ? error.message : '工作表解析失败') }
    return result
  })
  const batterySets = sheets.map(sheet => [...new Set(sheet.rows.map(row => row.data.batteryNo))].sort().join('\u0000'))
  sheets.forEach((sheet, index) => {
    if (sheet.rows.length && sheets[0]!.rows.length && batterySets[index] !== batterySets[0]) sheet.errors.push('列「电池号」：三个 Sheet 的电池号集合不一致')
  })
  return sheets
}
const partyHeaders: Record<string, string> = {
  recordedAt: '绝对时间', stepName: '工步名称', current: '电流(mA)', capacity: '容量(mAh)', energy: '能量(mWh)',
  stepDuration: '工步时间(h:min:s.ms)', chargeCapacity: '充电容量(mAh)', dischargeCapacity: '放电容量(mAh)',
  chargeEnergy: '充电能量(mWh)', dischargeEnergy: '放电能量(mWh)',
}

// 无时区文本与 Excel 日期都按香港设备时区解释，禁止依赖浏览器所在时区。
function deviceTime(value: unknown, date1904 = false): number {
  if (typeof value === 'number' && value > 0 && value < 2958466) {
    const text = XLSX.utils.format_cell({ t: 'n', v: value + (date1904 ? 1462 : 0), z: 'yyyy-mm-dd hh:mm:ss.000' })
    return deviceTime(text)
  }
  const text = String(value ?? '').trim()
  if (/^\d{13}$/.test(text)) return Number(text)
  const match = /^(\d{4})[-/](\d{1,2})[-/](\d{1,2})[ T](\d{1,2}):(\d{2}):(\d{2})(?:\.(\d{1,3}))?$/.exec(text)
  if (!match) return NaN
  const [year, month, day, hour, minute, second] = match.slice(1, 7).map(Number) as [number, number, number, number, number, number]
  const local = new Date(Date.UTC(year, month - 1, day, hour, minute, second, Number((match[7] || '').padEnd(3, '0'))))
  if (local.getUTCFullYear() !== year || local.getUTCMonth() !== month - 1 || local.getUTCDate() !== day || hour > 23 || minute > 59 || second > 59) return NaN
  return local.getTime() - 8 * 3600000
}

/** 将当前子页工作表转换为待提交记录；错误保留真实 Excel 行号和列名。 */
export function parseConsoleImport(workbook: XLSX.WorkBook, type: ConsoleDataType, batteries: string[], selectedBattery = ''): ValidatedImportRow[] {
  const name = workbook.SheetNames.find(name => sheetNames[type].includes(name))
    || (workbook.SheetNames.length === 1 ? workbook.SheetNames[0] : undefined)
  if (!name || !workbook.Sheets[name]) throw new Error(`缺少${sheetNames[type][0]}工作表`)
  const sheet = workbook.Sheets[name]!
  const table = XLSX.utils.sheet_to_json<unknown[]>(sheet, { header: 1, raw: true, defval: '', blankrows: true })
  const headers = (table[0] || []).map(value => String(value).trim())
  const party = name === sheetNames[type][2]
  const legacyRealtime = type === 'REALTIME' && !headers.includes('绝对时间') && (headers.includes('设备时间') || headers.includes('记录时间(h:min:s.ms)'))
  const legacySourceNumber = headers.includes('电池号') && (headers.includes('设备时间') || legacyRealtime)
  const date1904 = !!workbook.Workbook?.WBProps?.date1904
  const endTimes = new Map<string, unknown>()
  const invalidEndTimes = new Set<string>()
  const batteryFor = (row: Record<string, unknown>) => String(row['电池'] || row['电池号'] || selectedBattery || (batteries.length === 1 ? batteries[0] : '')).trim()
  const timeKey = (row: Record<string, unknown>) => JSON.stringify([batteryFor(row), String(row['循环序号']), ...(type === 'STEP' ? [String(row['工步序号'])] : [])])
  const recordNames = workbook.SheetNames.filter(name => sheetNames.REALTIME.includes(name) && workbook.Sheets[name])
  if (type !== 'REALTIME' && recordNames.length > 1) throw new Error('存在多个实时辅助工作表，无法确定时间来源')
  if (type !== 'REALTIME' && recordNames.length === 1) {
    // 汇总表没有绝对时间，只借用匹配实时记录的末次时间，不导入其他 Sheet 的行。
    const records = XLSX.utils.sheet_to_json<Record<string, unknown>>(workbook.Sheets[recordNames[0]!]!, { raw: true })
    for (const record of records) {
      const key = timeKey(record)
      const time = deviceTime(record['绝对时间'], date1904)
      if (!Number.isFinite(time)) invalidEndTimes.add(key)
      if (Number.isFinite(time) && (!endTimes.has(key) || time > deviceTime(endTimes.get(key), date1904))) endTimes.set(key, record['绝对时间'])
    }
    // 组内有未知时间就无法可靠确定末次记录，不能静默退到更早的有效行。
    for (const key of invalidEndTimes) endTimes.delete(key)
  }
  // 旧标准模板按原列头单位读取，避免对 A/Ah/Wh 再次缩放。
  const headerFor = (field: string, standardHeader: string) => {
    if (field === 'recordedAt' && legacyRealtime) return headers.includes('设备时间') ? '设备时间' : '记录时间(h:min:s.ms)'
    if (party) return partyHeaders[field] || standardHeader
    if (field === 'batteryNo' && headers.includes('电池号')) return '电池号'
    if (field === 'stepName' && headers.includes('工步')) return '工步'
    if (field === 'stepDuration' && headers.includes('工步时间')) return '工步时间'
    if (field === 'recordedAt' && headers.includes('设备时间')) return '设备时间'
    const legacyHeader = standardHeader.replace(/\(m(A|Ah|Wh)\)/, '($1)')
    return headers.includes(standardHeader) ? standardHeader : headers.includes(legacyHeader) ? legacyHeader : standardHeader
  }
  const columns: Record<string, string> = { ...consoleImportColumns[type], ...(type !== 'REALTIME' ? { recordedAt: '绝对时间' } : {}) }
  const missing = Object.entries(columns).filter(([field, header]) => {
    // 中值只读取对应列；缺列可为空，不借用旧峰值或谷值。
    if (['recordNo', 'batteryNo'].includes(field)) return false
    if (field === 'relativeTimeSeconds' && legacyRealtime) return false
    if (party && ['temperature', 'positivePotential', 'negativePotential', 'chargeMedianVoltage', 'dischargeMedianVoltage'].includes(field)) return false
    if (type !== 'REALTIME' && field === 'recordedAt') return false
    return !headers.includes(headerFor(field, header))
  }).map(([field, header]) => headerFor(field, header))
  if (missing.length) throw new Error(`第1行，列「${missing.join('、')}」：模板字段缺失`)
  return table.slice(1).flatMap((cells, index) => {
    if (cells.every(value => value === '' || value === null)) return []
    const sourceRow = index + 2 + XLSX.utils.decode_range(sheet['!ref'] || 'A1').s.r
    const raw = Object.fromEntries(headers.map((header, index) => [header, cells[index] ?? '']))
    const data: ValidatedImportRow['data'] = { sourceRow }
    const errors: string[] = []
    for (const [field, standardHeader] of Object.entries(columns)) {
      const header = headerFor(field, standardHeader)
      let value: unknown = raw[header]
      if (field === 'batteryNo') value = batteryFor(raw)
      // 系统记录编号只供定位修订，导入不能拿它代替设备/文件的来源编号。
      if (field === 'recordNo') value = type === 'REALTIME' ? raw['记录序号'] : type === 'STEP' ? `step:${raw['循环序号']}:${raw['工步序号']}` : `cycle:${raw['循环序号']}`
      // 旧标准模板的编号曾表示来源，保留其重复导入身份；新模板编号不参与去重。
      if (field === 'recordNo' && legacySourceNumber && raw['记录编号']) value = raw['记录编号']
      if (type !== 'REALTIME' && field === 'recordedAt' && !value) value = endTimes.get(timeKey(raw))
      if (field === 'stepDuration' || field === 'relativeTimeSeconds') {
        const cell = sheet[XLSX.utils.encode_cell({ r: sourceRow - 1, c: headers.indexOf(header) })]
        if (typeof value === 'number' && cell?.z && /[hs]/i.test(cell.z)) value = XLSX.utils.format_cell(cell)
        value = field === 'relativeTimeSeconds' && legacyRealtime ? null : parseTestDuration(value)
      }
      const text = String(value ?? '').trim()
      const fail = (reason: string) => errors.push(`第${sourceRow}行，列「${header}」：${reason}`)
      if (['recordNo', 'batteryNo', 'stepName'].includes(field)) {
        data[field] = text
        if (!text) fail('不能为空')
        else if (field === 'batteryNo' && !batteries.includes(text)) fail('不属于当前测试')
      } else if (field === 'recordedAt') {
        const time = deviceTime(value, date1904)
        data.recordedAt = Number.isFinite(time) ? time : null
        data.deviceTimeRaw = text
        data.adapterTimezoneId = 'Asia/Hong_Kong'
        if (!Number.isFinite(time)) fail('绝对设备时间无效')
      } else if (['cycleNo', 'stepNo', 'sequenceId'].includes(field)) {
        data[field] = Number(text)
        if (!text || !Number.isSafeInteger(data[field]) || Number(data[field]) < 1) fail('必须是正整数')
      } else {
        // 测量值仅来自文件；缺列、空格或占位符保持空值，不生成温度或电位等测试值。
        const number = !text || text === '-' ? null : Number(text)
        data[field] = number
        if (number !== null && !Number.isFinite(number)) fail('必须是数字或空值')
        else if (number !== null && /\(mA\)|\(mAh\)|\(mWh\)/.test(header)) data[field] = Number((number / 1000).toFixed(12))
      }
    }
    return [{ sourceRow, raw, data, errors }]
  })
}