// Name: Excel Rows to Markdown
// Description: Converts an Excel file into one Markdown file per row named by the ID column.
// Author: kenNash
// GitHub: kenNash

import "@johnlindquist/kit"
import * as XLSX from "xlsx"

const excelPath = await selectFile("Select the Excel file (.xlsx or .xls)")
const outDir = await path({
  placeholder: "Select output directory",
  onlyDirs: true,
})

const buffer = await readFile(excelPath)
const workbook = XLSX.read(buffer, { type: "buffer" })
const sheetName = workbook.SheetNames[0]
if (!sheetName) {
  await div(md(`# No sheets found in the workbook.`))
  exit()
}
const sheet = workbook.Sheets[sheetName]
const rows = XLSX.utils.sheet_to_json<Record<string, any>>(sheet, { defval: "" })

const fields = [
  "ID",
  "Saldo",
  "Data Operazione",
  "Data Valuta",
  "Entrata",
  "Uscita",
  "Descrizione",
  "Descrizione Completa",
  "Cat",
  "SubCat",
  "SubSubCat",
  "Fatt. / Ric.",
]

const sanitize = (s: any) =>
  String(s ?? "")
    .replace(/\r?\n+/g, " ")
    .trim()

const sanitizeFileName = (s: string) => {
  const trimmed = s.trim()
  const replaced = trimmed.replace(/[/\\?%*:|"<>]/g, "-").replace(/\s+/g, "-")
  return replaced || "untitled"
}

const uniqueFilePath = async (dir: string, baseName: string) => {
  let name = baseName.endsWith(".md") ? baseName.slice(0, -3) : baseName
  let candidate = path.join(dir, `${name}.md`)
  let i = 1
  while (await pathExists(candidate)) {
    candidate = path.join(dir, `${name}_${i}.md`)
    i++
  }
  return candidate
}

let written = 0
for (let i = 0; i < rows.length; i++) {
  const row = rows[i]
  const idVal = sanitize(row["ID"])
  if (!idVal) continue

  const fmLines = fields.map(key => `${key}: ${sanitize(row[key])}`)
  const content = `---\n${fmLines.join("\n")}\n---\n`

  const fileName = sanitizeFileName(idVal)
  const filePath = await uniqueFilePath(outDir, fileName + ".md")
  await writeFile(filePath, content)
  written++
}

await div(
  md(`
# Conversion complete
- Source: \`${path.basename(excelPath)}\`
- Output directory: \`${outDir}\`
- Files written: ${written}
`)
)