import { TextEncoder } from "util"

type WorksheetRow = Array<unknown>

export type WorksheetConfig = {
  name: string
  headers: string[]
  rows: WorksheetRow[]
  columnWidths?: Array<number | null | undefined>
  freezePane?: {
    rowSplit?: number
    columnSplit?: number
  }
  autoFilter?: boolean
}

type ZipEntry = {
  path: string
  data: Buffer
}

const XML_DECLARATION = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'

function escapeXml(value: string): string {
  return value
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    // remove invalid control characters (XML 1.0)
    .replace(/[\x00-\x08\x0B\x0C\x0E-\x1F]/g, "")
}

function columnRef(index: number): string {
  let col = ""
  let n = index + 1
  while (n > 0) {
    const remainder = (n - 1) % 26
    col = String.fromCharCode(65 + remainder) + col
    n = Math.floor((n - 1) / 26)
  }
  return col
}

function formatCell(value: unknown, colIndex: number, rowNumber: number, styleIndex?: number): string {
  const ref = `${columnRef(colIndex)}${rowNumber}`
  const styleAttr = styleIndex !== undefined ? ` s="${styleIndex}"` : ""
  if (value === null || value === undefined || value === "") {
    return `<c r="${ref}"${styleAttr}/>`
  }

  if (value instanceof Date) {
    return `<c r="${ref}"${styleAttr} t="inlineStr"><is><t xml:space="preserve">${escapeXml(value.toISOString())}</t></is></c>`
  }

  if (typeof value === "number" && Number.isFinite(value)) {
    return `<c r="${ref}"${styleAttr}><v>${value}</v></c>`
  }

  if (typeof value === "boolean") {
    return `<c r="${ref}"${styleAttr} t="b"><v>${value ? 1 : 0}</v></c>`
  }

  let text: string
  if (typeof value === "string") {
    text = value
  } else {
    text = JSON.stringify(value)
  }
  return `<c r="${ref}"${styleAttr} t="inlineStr"><is><t xml:space="preserve">${escapeXml(text)}</t></is></c>`
}

type WorksheetStyles = {
  header: number
  body: number
}

function buildWorksheetXml(config: WorksheetConfig, styles: WorksheetStyles): string {
  const totalRows = config.rows.length + 1
  const rows: string[] = []
  const headerRow = config.headers.map((header, idx) => formatCell(header, idx, 1, styles.header)).join("")
  rows.push(`<row r="1">${headerRow}</row>`)

  config.rows.forEach((rowData, rowIdx) => {
    const actualRow = rowIdx + 2
    const cells = config.headers
      .map((_, colIdx) => formatCell(rowData[colIdx], colIdx, actualRow, styles.body))
      .join("")
    rows.push(`<row r="${actualRow}">${cells}</row>`)
  })

  const hasCustomWidths = Array.isArray(config.columnWidths) && config.columnWidths.some((width) => typeof width === "number")
  const colsXml = hasCustomWidths
    ? `<cols>${config.headers
        .map((_, idx) => {
          const width = config.columnWidths?.[idx]
          if (typeof width !== "number" || Number.isNaN(width)) {
            return `<col min="${idx + 1}" max="${idx + 1}"/>`
          }
          return `<col min="${idx + 1}" max="${idx + 1}" width="${width}" customWidth="1"/>`
        })
        .join("")}</cols>`
    : ""

  let sheetViews = ""
  const rowSplit = config.freezePane?.rowSplit ?? 0
  const columnSplit = config.freezePane?.columnSplit ?? 0
  if (rowSplit > 0 || columnSplit > 0) {
    const attributes: string[] = []
    if (columnSplit > 0) attributes.push(`xSplit="${columnSplit}"`)
    if (rowSplit > 0) attributes.push(`ySplit="${rowSplit}"`)
    const topLeftColumn = columnSplit > 0 ? columnRef(columnSplit) : "A"
    const topLeftRow = rowSplit > 0 ? rowSplit + 1 : 1
    const activePane =
      rowSplit > 0 && columnSplit > 0
        ? "bottomRight"
        : rowSplit > 0
          ? "bottomLeft"
          : "topRight"
    const pane = `<pane ${attributes.join(" ")} topLeftCell="${topLeftColumn}${topLeftRow}" activePane="${activePane}" state="frozen"/>`
    sheetViews = `<sheetViews><sheetView workbookViewId="0">${pane}</sheetView></sheetViews>`
  }

  const usedRangeColumn = config.headers.length > 0 ? columnRef(config.headers.length - 1) : "A"
  const dimensionXml =
    config.headers.length > 0 && totalRows > 0
      ? `<dimension ref="A1:${usedRangeColumn}${totalRows}"/>`
      : ""

  const autoFilter =
    config.autoFilter && config.headers.length > 0 && totalRows > 1
      ? `<autoFilter ref="A1:${usedRangeColumn}${totalRows}"/>`
      : ""

  return [
    XML_DECLARATION,
    '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">',
    dimensionXml,
    sheetViews,
    '  <sheetFormatPr defaultRowHeight="15"/>',
    colsXml,
    "  <sheetData>",
    rows.map((row) => `    ${row}`).join("\n"),
    "  </sheetData>",
    autoFilter,
    "</worksheet>",
  ].join("\n")
}

const CRC32_TABLE = (() => {
  const table = new Uint32Array(256)
  for (let i = 0; i < 256; i += 1) {
    let c = i
    for (let k = 0; k < 8; k += 1) {
      c = (c & 1) !== 0 ? 0xedb88320 ^ (c >>> 1) : c >>> 1
    }
    table[i] = c >>> 0
  }
  return table
})()

function crc32(buffer: Buffer): number {
  let crc = 0 ^ -1
  for (let i = 0; i < buffer.length; i += 1) {
    crc = (crc >>> 8) ^ CRC32_TABLE[(crc ^ buffer[i]!) & 0xff]
  }
  return (crc ^ -1) >>> 0
}

function toDosTime(date: Date): { time: number; date: number } {
  const year = date.getFullYear()
  const month = date.getMonth() + 1
  const day = date.getDate()
  const hours = date.getHours()
  const minutes = date.getMinutes()
  const seconds = Math.floor(date.getSeconds() / 2)
  const dosTime = (hours << 11) | (minutes << 5) | seconds
  const dosDate = ((year - 1980) << 9) | (month << 5) | day
  return { time: dosTime, date: dosDate }
}

function writeUInt16LE(value: number): Buffer {
  const buffer = Buffer.alloc(2)
  buffer.writeUInt16LE(value & 0xffff, 0)
  return buffer
}

function writeUInt32LE(value: number): Buffer {
  const buffer = Buffer.alloc(4)
  buffer.writeUInt32LE(value >>> 0, 0)
  return buffer
}

function encodeZip(entries: ZipEntry[]): Buffer {
  const encoder = new TextEncoder()
  const localParts: Buffer[] = []
  const centralParts: Buffer[] = []
  let offset = 0
  const now = new Date()
  const { time: dosTime, date: dosDate } = toDosTime(now)

  entries.forEach((entry) => {
    const filenameBytes = encoder.encode(entry.path)
    const crc = crc32(entry.data)
    const compressedSize = entry.data.length
    const uncompressedSize = entry.data.length

    const localHeader = Buffer.concat([
      writeUInt32LE(0x04034b50),
      writeUInt16LE(20),
      writeUInt16LE(0),
      writeUInt16LE(0), // store (no compression)
      writeUInt16LE(dosTime),
      writeUInt16LE(dosDate),
      writeUInt32LE(crc),
      writeUInt32LE(compressedSize),
      writeUInt32LE(uncompressedSize),
      writeUInt16LE(filenameBytes.length),
      writeUInt16LE(0),
      Buffer.from(filenameBytes),
    ])

    localParts.push(localHeader, entry.data)

    const centralHeader = Buffer.concat([
      writeUInt32LE(0x02014b50),
      writeUInt16LE(20),
      writeUInt16LE(20),
      writeUInt16LE(0),
      writeUInt16LE(0),
      writeUInt16LE(dosTime),
      writeUInt16LE(dosDate),
      writeUInt32LE(crc),
      writeUInt32LE(compressedSize),
      writeUInt32LE(uncompressedSize),
      writeUInt16LE(filenameBytes.length),
      writeUInt16LE(0),
      writeUInt16LE(0),
      writeUInt16LE(0),
      writeUInt16LE(0),
      writeUInt32LE(0),
      writeUInt32LE(offset),
      Buffer.from(filenameBytes),
    ])

    centralParts.push(centralHeader)

    offset += localHeader.length + entry.data.length
  })

  const centralDirectory = Buffer.concat(centralParts)
  const endOfCentralDirectory = Buffer.concat([
    writeUInt32LE(0x06054b50),
    writeUInt16LE(0),
    writeUInt16LE(0),
    writeUInt16LE(entries.length),
    writeUInt16LE(entries.length),
    writeUInt32LE(centralDirectory.length),
    writeUInt32LE(offset),
    writeUInt16LE(0),
  ])

  return Buffer.concat([...localParts, centralDirectory, endOfCentralDirectory])
}

export function buildXlsxWorkbook(sheets: WorksheetConfig[]): Buffer {
  if (sheets.length === 0) {
    throw new Error("Workbook requires at least one sheet")
  }

  const styles: WorksheetStyles = {
    body: 0,
    header: 1,
  }
  const now = new Date()
  const timestamp = now.toISOString()
  const workbookRels: string[] = []
  const sheetEntries: ZipEntry[] = []

  const sheetRefs = sheets.map((sheet, index) => {
    const sheetId = index + 1
    const relId = `rId${sheetId}`
    const worksheetXml = buildWorksheetXml(sheet, styles)
    sheetEntries.push({
      path: `xl/worksheets/sheet${sheetId}.xml`,
      data: Buffer.from(worksheetXml, "utf8"),
    })
    workbookRels.push(
      `<Relationship Id="${relId}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet${sheetId}.xml"/>`,
    )
    return `<sheet name="${escapeXml(sheet.name)}" sheetId="${sheetId}" r:id="${relId}"/>`
  })

  const workbookXml = [
    XML_DECLARATION,
    '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">',
    "  <sheets>",
    sheetRefs.map((sheet) => `    ${sheet}`).join("\n"),
    "  </sheets>",
    "</workbook>",
  ].join("\n")

  workbookRels.push(
    '<Relationship Id="rIdStyles" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>',
  )

  const workbookRelsXml = [
    XML_DECLARATION,
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">',
    workbookRels.map((rel) => `  ${rel}`).join("\n"),
    "</Relationships>",
  ].join("\n")

  const stylesXml = [
    XML_DECLARATION,
    '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">',
    '  <fonts count="2">',
    '    <font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font>',
    '    <font><b/><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font>',
    "  </fonts>",
    '  <fills count="3">',
    '    <fill><patternFill patternType="none"/></fill>',
    '    <fill><patternFill patternType="gray125"/></fill>',
    '    <fill><patternFill patternType="solid"><fgColor rgb="FFE2E8F0"/><bgColor indexed="64"/></patternFill></fill>',
    "  </fills>",
    '  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>',
    '  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>',
    '  <cellXfs count="2">',
    '    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>',
    '    <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/>',
    "  </cellXfs>",
    '  <cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>',
    "</styleSheet>",
  ].join("\n")

  const corePropsXml = [
    XML_DECLARATION,
    '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">',
    "  <dc:creator>Raven</dc:creator>",
    "  <cp:lastModifiedBy>Raven</cp:lastModifiedBy>",
    `  <dcterms:created xsi:type="dcterms:W3CDTF">${escapeXml(timestamp)}</dcterms:created>`,
    `  <dcterms:modified xsi:type="dcterms:W3CDTF">${escapeXml(timestamp)}</dcterms:modified>`,
    "</cp:coreProperties>",
  ].join("\n")

  const appPropsXml = [
    XML_DECLARATION,
    '<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">',
    "  <Application>Raven</Application>",
    "  <DocSecurity>0</DocSecurity>",
    "  <ScaleCrop>false</ScaleCrop>",
    "  <HeadingPairs>",
    '    <vt:vector size="2" baseType="variant">',
    '      <vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant>',
    `      <vt:variant><vt:i4>${sheets.length}</vt:i4></vt:variant>`,
    "    </vt:vector>",
    "  </HeadingPairs>",
    "  <TitlesOfParts>",
    `    <vt:vector size="${sheets.length}" baseType="lpstr">`,
    sheets.map((sheet) => `      <vt:lpstr>${escapeXml(sheet.name)}</vt:lpstr>`).join("\n"),
    "    </vt:vector>",
    "  </TitlesOfParts>",
    "</Properties>",
  ].join("\n")

  const contentTypes = [
    XML_DECLARATION,
    '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">',
    '  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>',
    '  <Default Extension="xml" ContentType="application/xml"/>',
    '  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>',
    '  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>',
    '  <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>',
    '  <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>',
    ...sheets.map(
      (_sheet, index) =>
        `  <Override PartName="/xl/worksheets/sheet${index + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`,
    ),
    "</Types>",
  ].join("\n")

  const rootRelsXml = [
    XML_DECLARATION,
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">',
    '  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>',
    '  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>',
    '  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>',
    "</Relationships>",
  ].join("\n")

  const entries: ZipEntry[] = [
    { path: "[Content_Types].xml", data: Buffer.from(contentTypes, "utf8") },
    { path: "_rels/.rels", data: Buffer.from(rootRelsXml, "utf8") },
    { path: "docProps/core.xml", data: Buffer.from(corePropsXml, "utf8") },
    { path: "docProps/app.xml", data: Buffer.from(appPropsXml, "utf8") },
    { path: "xl/workbook.xml", data: Buffer.from(workbookXml, "utf8") },
    { path: "xl/_rels/workbook.xml.rels", data: Buffer.from(workbookRelsXml, "utf8") },
    { path: "xl/styles.xml", data: Buffer.from(stylesXml, "utf8") },
    ...sheetEntries,
  ]

  return encodeZip(entries)
}
