// Name: Excel Rows to Markdown Files
// Description: Convert an Excel file to one Markdown file per row named by ID with specified fields.
// Author: kenNash
// GitHub: kenNash

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

const excelPath = await selectFile("Select the Excel file (.xlsx or .xls)")
if (!excelPath) {
  notify("No file selected")
  exit()
}

const outputDir = await path({
  placeholder: "Select an output folder for the Markdown files",
  onlyDirs: true,
})

await ensureDir(outputDir)

const workbook = XLSX.readFile(excelPath)
const sheetName = workbook.SheetNames[0]
const worksheet = workbook.Sheets[sheetName]

type Row = Record<string, any>

const rows: Row[] = XLSX.utils.sheet_to_json<Row>(worksheet, {
  defval: "",
  raw: false,
})

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

const sanitizeFileName = (name: string) =>
  String(name)
    .trim()
    .replace(/[\\\/:*?"<>|]/g, "_")
    .replace(/\s+/g, " ")
    .slice(0, 255)

const stringify = (v: any) =>
  (v === null || v === undefined ? "" : String(v)).replace(/\r?\n/g, " ").trim()

let created = 0
for (let i = 0; i < rows.length; i++) {
  const row = rows[i] || {}
  const idRaw = row["ID"]
  const id = stringify(idRaw) || `row-${i + 1}`
  const fileName = sanitizeFileName(id) + ".md"
  const filePath = path.join(outputDir, fileName)

  const lines = COLUMNS.map(key => `${key}: ${stringify(row[key])}`)
  const content = `---\n${lines.join("\n")}\n---\n`

  await writeFile(filePath, content)
  created++
}

await notify(`Created ${created} file${created === 1 ? "" : "s"} in ${outputDir}`)
await revealInFinder(outputDir)