const XLSX = require('xlsx');

// 📘 อ่านไฟล์ Excel
const excel = XLSX.readFile('input.xlsx');
const sheet = excel.Sheets[excel.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet);

// ⚙️ ฟังก์ชันลบตัวเลขและจุดนำหน้า เช่น "33.เงินรายได้" → "เงินรายได้"
const cleanSource = (text) => {
  if (!text) return '';
  return text.toString().replace(/^\d+\.\s*/, '').trim();
};

// 🧮 เตรียมข้อมูลรวมตาม "แผนกวิชา"
const pivot = {};

data.forEach(row => {
  const plan = row['แผนกวิชา'] || 'ไม่ระบุแผนกวิชา';
  const source = cleanSource(row['แหล่งงบประมาณ']);
  const budget = Number(row['งบประมาณ']) || 0;

  if (!source || !plan) return;

  if (!pivot[plan]) pivot[plan] = {};
  if (!pivot[plan][source]) pivot[plan][source] = 0;

  pivot[plan][source] += budget;
});

// 🧾 ดึงชื่อคอลัมน์ (แหล่งงบทั้งหมด)
const allSources = Array.from(
  new Set(Object.values(pivot).flatMap(obj => Object.keys(obj)))
);

// 📊 ฟังก์ชัน format ตัวเลข (comma + 2 ทศนิยม)
const formatNumber = (num) =>
  num ? num.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 }) : '';

// 🪄 สร้างตารางผลลัพธ์
const result = [];
let grandTotalAll = 0;
const columnTotals = {};

// รวมแต่ละแผนแผนกวิชา
Object.entries(pivot).forEach(([plan, sources]) => {
  const row = { 'แผนกวิชา': plan };
  let rowTotal = 0;

  allSources.forEach(source => {
    const val = sources[source] || 0;
    row[source] = val ? formatNumber(val) : '';
    rowTotal += val;
    columnTotals[source] = (columnTotals[source] || 0) + val;
  });

  grandTotalAll += rowTotal;
  row['รวมทั้งหมด'] = formatNumber(rowTotal);
  result.push(row);
});

// ➕ เพิ่มแถวสุดท้าย “รวมทั้งหมด”
const totalRow = { 'แผนกวิชา': 'รวมทั้งหมด' };
allSources.forEach(source => {
  totalRow[source] = columnTotals[source] ? formatNumber(columnTotals[source]) : '';
});
totalRow['รวมทั้งหมด'] = formatNumber(grandTotalAll);
result.push(totalRow);

// ✨ เขียนเป็นไฟล์ Excel ใหม่
const newSheet = XLSX.utils.json_to_sheet(result);
const newBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(newBook, newSheet, 'Summary');
XLSX.writeFile(newBook, 'output.xlsx');

console.log('✅ สร้างตารางสรุปตามแผนกวิชา พร้อมผลรวมเสร็จแล้ว → output.xlsx');