import ExcelJS from 'exceljs'

const saveBlobAsFile = (blob: Blob, fileName: string): void => {
  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = fileName
  a.rel = 'noopener'
  document.body.appendChild(a)
  a.click()
  a.remove()
  URL.revokeObjectURL(url)
}

/**
 * 导出 Excel 所需的业务数据结构。
 * - 该结构尽量与表格展示字段保持 1:1，对应关系更直观
 * - 数值字段建议统一使用“亿”为单位（与表格右侧单位一致）
 */
export interface ExportFormData {
  /**
   * 导出文件与表格标题。
   * - 同时用于生成文件名：`${title}.xlsx`
   */
  title: string

  /**
   * 表格日期显示文本（建议格式：YYYY-MM-DD）。
   */
  date: string

  // 顶部当前可用余额
  currentAvailableBalance: number

  // 今日大额收付款报备情况
  reportAmountIncome: number
  reportAmountPay: number

  unverifiedWriteOffIncome: number
  unverifiedWriteOffPay: number

  // 未核销业务流水
  unliquidatedBusinessIncome: number
  unliquidatedBusinessPay: number

  // 日初可用资金（红字）
  initialAvailableFunds: number

  // 今日预计调拨金额
  expectedTransferIncome: number
  expectedTransferPay: number
  expectedTransferDiff: number

  // 跨境人民币头寸清算账户预留金额
  reserveAmount: number
}

/**
 * 示例数据：页面点击“导出Excel”时使用该对象生成报表。
 * 实际接入时可替换为接口返回结果或表单输入值。
 */
export const exportFormDataDemo: ExportFormData = {
  title: '跨境人民币头寸压算表',
  date: '2026-02-02',

  currentAvailableBalance: 0,

  reportAmountIncome: 0,
  reportAmountPay: 0,

  unverifiedWriteOffIncome: 0,
  unverifiedWriteOffPay: 0,

  unliquidatedBusinessIncome: 0,
  unliquidatedBusinessPay: 0,

  initialAvailableFunds: 0,

  expectedTransferIncome: 2,
  expectedTransferPay: 2,
  expectedTransferDiff: 0,

  reserveAmount: 0
}

/**
 * Logo 插入说明（重点）
 * 1) 只允许插入“静态图片”：PNG / JPG(JPEG)，不插入 GIF 等动图
 * 2) 图片来自 public 目录（打包时会被静态托管），通过 fetch 拉取后转 base64 传给 ExcelJS
 * 3) 通过缓存避免每次点击“导出Excel”都重复请求与转换，提升稳定性
 *
 * public 目录下的静态资源会映射到站点根路径：
 * - 文件：frontEnd/public/logo.jpg
 * - URL： /logo.jpg
 */
const LOGO_PUBLIC_PATH = '/logo.jpg'

/**
 * 仅允许的静态图片扩展名。
 * - ExcelJS 的 addImage 需要显式的 extension 参数
 */
export type StaticLogoExtension = 'png' | 'jpeg'

/**
 * 静态 Logo 的内存表示。
 * - base64：不包含 data: 前缀，仅为纯 base64 内容
 * - extension：与图片真实格式一致
 */
export type StaticLogo = { base64: string; extension: StaticLogoExtension }

/**
 * Logo 缓存：避免重复 fetch 与 base64 转换，提升导出稳定性与性能。
 */
let cachedStaticLogo: StaticLogo | null = null

/**
 * 将 Blob 转换为纯 base64 字符串（去掉 dataURL 头部）。
 * @param blob 通过 fetch 获取到的图片二进制
 * @returns 纯 base64 字符串；解析失败时返回空字符串
 */
const blobToBase64 = (blob: Blob): Promise<string> => {
  return new Promise((resolve, reject) => {
    const reader = new FileReader()
    reader.onload = () => {
      const result = reader.result
      if (typeof result !== 'string') {
        resolve('')
        return
      }

      const commaIndex = result.indexOf(',')
      resolve(commaIndex >= 0 ? result.slice(commaIndex + 1) : result)
    }
    reader.onerror = () => reject(new Error('读取图片失败'))
    reader.readAsDataURL(blob)
  })
}

/**
 * 根据 content-type 与 URL 后缀推断图片扩展名。
 * - 优先使用 content-type（更可靠）
 * - content-type 缺失时再回退到 URL 后缀
 * @param contentType response header: content-type
 * @param urlPath 图片 URL 路径（例如 /logo.jpg）
 * @returns 可支持的 extension；不支持时返回 null
 */
const guessLogoExtension = (contentType: string, urlPath: string): StaticLogoExtension | null => {
  const ct = contentType.toLowerCase()
  if (ct.includes('image/png')) return 'png'
  if (ct.includes('image/jpeg') || ct.includes('image/jpg')) return 'jpeg'

  const lowerPath = urlPath.toLowerCase()
  if (lowerPath.endsWith('.png')) return 'png'
  if (lowerPath.endsWith('.jpg') || lowerPath.endsWith('.jpeg')) return 'jpeg'
  return null
}

/**
 * 获取“可插入 Excel 的静态 Logo”。
 * - 从 public 目录拉取图片（站点根路径映射）
 * - 过滤 GIF 等动图格式，避免 ExcelJS 插入异常
 * - 首次成功后写入内存缓存，后续直接复用
 * @returns Logo 信息；不可用或失败时返回 null
 */
export const getStaticLogo = async (): Promise<StaticLogo | null> => {
  if (cachedStaticLogo) return cachedStaticLogo

  try {
    const res = await fetch(LOGO_PUBLIC_PATH)
    if (!res.ok) return null

    const contentType = res.headers.get('content-type') ?? ''
    const extension = guessLogoExtension(contentType, LOGO_PUBLIC_PATH)
    if (!extension) return null

    if (contentType.toLowerCase().includes('image/gif')) return null

    const blob = await res.blob()
    if (blob.type.toLowerCase().includes('image/gif')) return null

    const base64 = await blobToBase64(blob)
    if (!base64) return null

    cachedStaticLogo = { base64, extension }
    return cachedStaticLogo
  } catch {
    return null
  }
}



export interface ExportBankExcelOptions {
  /**
   * 需要插入到工作表中的静态 Logo（可选）。
   * - 为 null/undefined 时回退到文字 Logo
   */
  logo?: StaticLogo | null
}

/**
 * 生成并下载“跨境人民币头寸压算表”Excel。
 * @param data 业务数据
 * @param options 可选配置（例如 Logo）
 */
export async function exportBankExcel(data: ExportFormData, options: ExportBankExcelOptions = {}): Promise<void> {
  const workbook = new ExcelJS.Workbook()
  const worksheet = workbook.addWorksheet('跨境人民币头寸压算表', {
    views: [{ showGridLines: true }]
  })

  // 页面设置
  worksheet.pageSetup = {
    paperSize: 9,
    orientation: 'landscape',
    fitToPage: true,
    fitToWidth: 1,
    fitToHeight: 0,
    margins: {
      left: 0.3,
      right: 0.3,
      top: 0.3,
      bottom: 0.3,
      header: 0.1,
      footer: 0.1
    }
  }

  // 列宽说明（重点）
  // 1) ExcelJS 的 column.width 单位是“字符宽度”（大致相当于默认字体下能容纳多少个字符），不是像素
  // 2) 合并单元格（mergeCells）并不会单独有“合并后的宽度”，它的可视宽度 = 被合并列宽之和
  // 3) 因此要调整某个合并区域（例如 A3:B3）的显示效果，应优先调整 A 列和 B 列的宽度
  //
  // 下面给一组更适配当前表格内容的默认列宽（可直接改这里达到你想要的效果）
  const COLUMN_WIDTHS = {
    A: 28,
    B: 16,
    C: 18,
    D: 18,
    E: 14
  } as const

  const applyColumnWidths = (): void => {
    worksheet.columns = [
      { width: COLUMN_WIDTHS.A },
      { width: COLUMN_WIDTHS.B },
      { width: COLUMN_WIDTHS.C },
      { width: COLUMN_WIDTHS.D },
      { width: COLUMN_WIDTHS.E }
    ]
  }

  applyColumnWidths()

  // 行高说明
  // 1) row.height 单位是“点”（points），会直接影响打印/分页效果
  // 2) 建议用配置表统一管理，避免散落在代码中导致后续维护不稳定
  const ROW_HEIGHTS: Record<number, number> = {
    1: 34,
    2: 26,
    3: 24,
    4: 24,
    5: 24,
    6: 24,
    7: 24,
    8: 24,
    9: 24,
    10: 24,
    11: 20
  }

  for (const [rowIndex, height] of Object.entries(ROW_HEIGHTS)) {
    worksheet.getRow(Number(rowIndex)).height = height
  }

  const BORDER_THIN: Partial<ExcelJS.Borders> = {
    top: { style: 'thin', color: { argb: '000000' } },
    left: { style: 'thin', color: { argb: '000000' } },
    bottom: { style: 'thin', color: { argb: '000000' } },
    right: { style: 'thin', color: { argb: '000000' } }
  }

  const BORDER_MEDIUM: Partial<ExcelJS.Borders> = {
    top: { style: 'medium', color: { argb: '000000' } },
    left: { style: 'medium', color: { argb: '000000' } },
    bottom: { style: 'medium', color: { argb: '000000' } },
    right: { style: 'medium', color: { argb: '000000' } }
  }

  const FONT_NORMAL: Partial<ExcelJS.Font> = {
    name: 'Microsoft YaHei',
    size: 12,
    color: { argb: '333333' }
  }

  const FONT_TITLE: Partial<ExcelJS.Font> = {
    name: 'Microsoft YaHei',
    size: 22,
    color: { argb: '333333' }
  }

  const FONT_DATE: Partial<ExcelJS.Font> = {
    name: 'Microsoft YaHei',
    size: 12,
    color: { argb: '666666' }
  }

  const FONT_RED_BOLD: Partial<ExcelJS.Font> = {
    name: 'Microsoft YaHei',
    size: 14,
    bold: true,
    color: { argb: 'C00000' }
  }

  const FONT_RED_BANK: Partial<ExcelJS.Font> = {
    name: 'Microsoft YaHei',
    size: 18,
    bold: true,
    color: { argb: 'C00000' }
  }

  const ALIGN_CENTER: Partial<ExcelJS.Alignment> = {
    vertical: 'middle',
    horizontal: 'center',
    wrapText: true
  }

  const ALIGN_RIGHT: Partial<ExcelJS.Alignment> = {
    vertical: 'middle',
    horizontal: 'right'
  }

  const PINK_FILL: ExcelJS.Fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'F3D4DC' }
  }

  const BLUE_FILL: ExcelJS.Fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: '18B6E6' }
  }

  const CYAN_FILL: ExcelJS.Fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: '00B7E8' }
  }

  const LIGHT_BLUE_FILL: ExcelJS.Fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFDCE6F1' }
  }

  const GREEN_FILL: ExcelJS.Fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: '8EBB43' }
  }

  /**
   * 设置单个单元格的值与样式。
   * @param address 单元格地址（例如 A1、C3）
   * @param value 单元格值
   * @param options 样式选项（字体/对齐/填充/边框/数字格式）
   */
  const setCell = (
    address: string,
    value: string | number,
    options?: {
      font?: Partial<ExcelJS.Font>
      alignment?: Partial<ExcelJS.Alignment>
      fill?: ExcelJS.Fill
      border?: Partial<ExcelJS.Borders>
      numFmt?: string
    }
  ): void => {
    const cell = worksheet.getCell(address)
    cell.value = value

    if (options?.font) {
      cell.font = options.font as ExcelJS.Font
    }
    if (options?.alignment) {
      cell.alignment = options.alignment as ExcelJS.Alignment
    }
    if (options?.fill) {
      cell.fill = options.fill
    }
    if (options?.border) {
      cell.border = options.border as ExcelJS.Borders
    }
    if (options?.numFmt) {
      cell.numFmt = options.numFmt
    }
  }

  /**
   * 给指定区域批量设置边框。
   * @param startRow 起始行（从 1 开始）
   * @param endRow 结束行（包含）
   * @param startCol 起始列（A=1）
   * @param endCol 结束列（包含）
   * @param border 边框样式，默认细边框
   */
  const setRangeBorder = (
    startRow: number,
    endRow: number,
    startCol: number,
    endCol: number,
    border: Partial<ExcelJS.Borders> = BORDER_THIN
  ): void => {
    for (let r = startRow; r <= endRow; r++) {
      for (let c = startCol; c <= endCol; c++) {
        worksheet.getCell(r, c).border = border as ExcelJS.Borders
      }
    }
  }

  /**
   * 给指定区域设置“外边框加粗、内部细线”的效果。
   * @param startRow 起始行（从 1 开始）
   * @param endRow 结束行（包含）
   * @param startCol 起始列（A=1）
   * @param endCol 结束列（包含）
   */
  const setOuterMediumBorder = (
    startRow: number,
    endRow: number,
    startCol: number,
    endCol: number
  ): void => {
    for (let r = startRow; r <= endRow; r++) {
      for (let c = startCol; c <= endCol; c++) {
        const cell = worksheet.getCell(r, c)
        const border: Partial<ExcelJS.Borders> = {
          top: { style: 'thin', color: { argb: '000000' } },
          left: { style: 'thin', color: { argb: '000000' } },
          bottom: { style: 'thin', color: { argb: '000000' } },
          right: { style: 'thin', color: { argb: '000000' } }
        }

        if (r === startRow) border.top = { style: 'medium', color: { argb: '000000' } }
        if (r === endRow) border.bottom = { style: 'medium', color: { argb: '000000' } }
        if (c === startCol) border.left = { style: 'medium', color: { argb: '000000' } }
        if (c === endCol) border.right = { style: 'medium', color: { argb: '000000' } }

        cell.border = border as ExcelJS.Borders
      }
    }
  }

  /**
   * 统一数值兜底，避免 undefined/null 导致单元格为空或计算报错。
   */
  const formatAmount = (value: number | null | undefined): number => value ?? 0

  // ========== 顶部区域 ==========
  worksheet.mergeCells('A1:B2')
  worksheet.mergeCells('C1:E1')

  const logo = options.logo

  if (logo?.base64) {
    const imageId = workbook.addImage({
      base64: logo.base64,
      extension: logo.extension
    })
    worksheet.addImage(imageId, {
      tl: { col: 0.15, row: 0.25 },
      ext: { width: 240, height: 52 }
    })
  } else {
    setCell('A1', '北京银行\nBANK OF BEIJING', {
      font: FONT_RED_BANK,
      alignment: {
        vertical: 'middle',
        horizontal: 'center',
        wrapText: true
      }
    })
  }

  // 顶部粉色标题块
  for (const addr of ['C1', 'D1', 'E1', 'C2', 'D2', 'E2']) {
    worksheet.getCell(addr).fill = PINK_FILL
  }

  setCell('C1', data.title, {
    font: FONT_TITLE,
    alignment: ALIGN_CENTER,
    fill: PINK_FILL
  })

  setCell('D2', data.date, {
    font: FONT_DATE,
    alignment: ALIGN_RIGHT,
    fill: PINK_FILL
  })
  setCell('E2', '备注', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    fill: PINK_FILL
  })

  // ========== 主体表格 ==========
  // A3:B3
  worksheet.mergeCells('A3:B3')
  setCell('A3', '跨境人民币头寸账户当前可用余额', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // C3:D3
  worksheet.mergeCells('C3:D3')
  setCell('C3', formatAmount(data.currentAvailableBalance), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })

  // E3
  setCell('E3', '单位：亿元', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  worksheet.mergeCells('A4:B4')
  setCell('A4', '', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  worksheet.mergeCells('A5:A6')
  setCell('A5', '今日大额收付款报备情况', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })
  setCell('B5', '报备金额', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })
  setCell('B6', '未核销金额', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // C4
  setCell('C4', '收入', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // D4
  setCell('D4', '支付', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // E4
  setCell('E4', '轧差', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // C5 / D5 / E5
  setCell('C5', formatAmount(data.reportAmountIncome), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })
  setCell('D5', formatAmount(data.reportAmountPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })
  setCell('E5', formatAmount(data.reportAmountIncome - data.reportAmountPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    fill: BLUE_FILL,
    numFmt: '0.00'
  })

  // C6 / D6 / E6
  setCell('C6', formatAmount(data.unverifiedWriteOffIncome), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })
  setCell('D6', formatAmount(data.unverifiedWriteOffPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })
  setCell('E6', formatAmount(data.unverifiedWriteOffIncome - data.unverifiedWriteOffPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    fill: CYAN_FILL,
    numFmt: '0.00'
  })

  // A7:B7
  worksheet.mergeCells('A7:B7')
  setCell('A7', '未核销业务流水', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  setCell('C7', formatAmount(data.unliquidatedBusinessIncome), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })
  setCell('D7', formatAmount(data.unliquidatedBusinessPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })
  setCell('E7', formatAmount(data.unliquidatedBusinessIncome - data.unliquidatedBusinessPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    fill: GREEN_FILL,
    numFmt: '0.00'
  })

  // A8:B8
  worksheet.mergeCells('A8:B8')
  setCell('A8', '日初可用资金', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // C8:E8
  worksheet.mergeCells('C8:E8')
  setCell('C8', formatAmount(data.initialAvailableFunds), {
    font: FONT_RED_BOLD,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })

  worksheet.mergeCells('A9:E9')
  setCell('A9', '', {
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // A10:B10
  worksheet.mergeCells('A10:B10')
  setCell('A10', '今日预计调拨金额', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  setCell('C10', formatAmount(data.expectedTransferIncome), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    fill: LIGHT_BLUE_FILL,
    numFmt: data.expectedTransferIncome % 1 === 0 ? '0' : '0.00'
  })

  setCell('D10', formatAmount(data.expectedTransferPay), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    fill: LIGHT_BLUE_FILL,
    numFmt: data.expectedTransferPay % 1 === 0 ? '0' : '0.00'
  })

  setCell('E10', formatAmount(data.expectedTransferDiff), {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    fill: BLUE_FILL,
    numFmt: '0.00'
  })

  // A11:B11
  worksheet.mergeCells('A11:B11')
  setCell('A11', '跨境人民币头寸清算账户预留金额', {
    font: FONT_NORMAL,
    alignment: ALIGN_CENTER,
    border: BORDER_THIN
  })

  // C11:E11
  worksheet.mergeCells('C11:E11')
  setCell('C11', formatAmount(data.reserveAmount), {
    font: {
      name: 'Microsoft YaHei',
      size: 14,
      bold: true,
      color: { argb: '000000' }
    },
    alignment: ALIGN_CENTER,
    border: BORDER_THIN,
    numFmt: '0.00'
  })

  // 主体所有区域边框
  setRangeBorder(3, 11, 1, 5, BORDER_THIN)

  // 外边框加粗，更接近图里效果
  setOuterMediumBorder(3, 11, 1, 5)

  // 分隔线：行 8 红字区域略强调
  for (let c = 1; c <= 5; c++) {
    worksheet.getCell(8, c).border = {
      top: { style: 'medium', color: { argb: '000000' } },
      left: c === 1 ? { style: 'medium', color: { argb: '000000' } } : { style: 'thin', color: { argb: '000000' } },
      bottom: { style: 'medium', color: { argb: '000000' } },
      right: c === 5 ? { style: 'medium', color: { argb: '000000' } } : { style: 'thin', color: { argb: '000000' } }
    } as ExcelJS.Borders
  }

  // 如果想让顶部标题区也更像图片，可给 A1:B2 加一点边距感
  worksheet.getCell('A1').alignment = {
    vertical: 'middle',
    horizontal: 'center',
    wrapText: true
  }

  // 导出
  const buffer = await workbook.xlsx.writeBuffer()
  const blob = new Blob([buffer], {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  })
  saveBlobAsFile(blob, `${data.title}.xlsx`)
}

export async function exportBankExcelWithDefaultLogo(data: ExportFormData = exportFormDataDemo): Promise<void> {
  const logo = await getStaticLogo()
  await exportBankExcel(data, { logo })
}