/** Excel sheet names: no : \ / ? * [ ], at most 31 characters, unique ignoring case. */
export function sheetNames(titles: string[]): string[] {
  const used = new Set<string>()
  return titles.map((title, i) => {
    let base = title
      .replace(/[:\\/?*[\]]/g, ' ')
      .replace(/\s+/g, ' ')
      .trim()
      .replace(/^'+|'+$/g, '')
    if (!base || base.toLowerCase() === 'history') base = `Sheet${i + 1}`
    base = base.slice(0, 31).trim()
    let name = base
    for (let n = 2; used.has(name.toLowerCase()); n++) {
      const suffix = ` (${n})`
      name = base.slice(0, 31 - suffix.length).trim() + suffix
    }
    used.add(name.toLowerCase())
    return name
  })
}

/** Rows as arrays in column order, from arrays or objects keyed by column. */
export function rowArrays(columns: string[], rows: unknown[]): unknown[][] {
  return rows.map((row) => {
    if (Array.isArray(row)) return row
    if (row && typeof row === 'object') {
      const record = row as Record<string, unknown>
      return columns.map((c) => record[c])
    }
    return [row]
  })
}

/** A file name from a title and the time: letters, digits, dashes, ending .xlsx. */
export function defaultFilename(title: string, now: Date): string {
  const slug =
    title
      .replace(/[^A-Za-z0-9]+/g, '-')
      .replace(/^-|-$/g, '')
      .slice(0, 60)
      .replace(/-$/, '') || 'export'
  const p = (n: number) => String(n).padStart(2, '0')
  const stamp = `${now.getFullYear()}${p(now.getMonth() + 1)}${p(now.getDate())}-${p(now.getHours())}${p(now.getMinutes())}${p(now.getSeconds())}`
  return `${slug}-${stamp}.xlsx`
}

/** A user-given file name made safe for Windows and macOS, ending .xlsx. */
export function cleanFilename(name: string): string {
  const base = name
    .split(/[\\/]/)
    .pop()!
    .replace(/[<>:"|?*\x00-\x1f]/g, '-')
    .replace(/\.xlsx$/i, '')
    .trim()
  return `${base || 'export'}.xlsx`
}