function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("GAS 行政流工具")
    .addItem("① 建立飲料訂購表單", "建立飲料訂購表單")
    .addItem("② 建立飲料訂購工作台", "建立飲料訂購工作台")
    .addItem("③ 整理訂購明細", "整理訂購明細")
    .addItem("④ 統計品項數量", "統計品項數量")
    .addItem("⑤ 產生群組通知文字", "產生群組通知文字")
    .addSeparator()
    .addItem("⑥ 建立通用行政工作台與欄位對照", "建立通用行政工作台與欄位對照")
    .addItem("⑦ 整理通用明細", "整理通用明細")
    .addItem("⑧ 統計分類數量", "統計分類數量")
    .addItem("⑨ 產生通用通知文字", "產生通用通知文字")
    .addItem("⑩ 產生行政情境範例對照", "產生行政情境範例對照")
    .addToUi();
}

/** =========================================================================
 * 第一部分：飲料訂購情境
 * ========================================================================= */

function 建立飲料訂購表單() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const form = FormApp.create("飲料訂購表單");
  form.setDescription(
    "請填寫飲料訂購資料。這份表單用來練習行政收件：收件、整理、統計與通知。",
  );
  form.addTextItem().setTitle("姓名").setRequired(true);
  form
    .addListItem()
    .setTitle("飲料品項")
    .setChoiceValues(["紅茶", "綠茶", "奶茶", "拿鐵"])
    .setRequired(true);
  form
    .addMultipleChoiceItem()
    .setTitle("甜度")
    .setChoiceValues(["正常糖", "半糖", "微糖", "無糖"])
    .setRequired(true);
  form
    .addMultipleChoiceItem()
    .setTitle("冰塊")
    .setChoiceValues(["正常冰", "少冰", "微冰", "去冰"])
    .setRequired(true);
  form
    .addListItem()
    .setTitle("數量")
    .setChoiceValues(["1", "2", "3", "4", "5"])
    .setRequired(true);
  form.addParagraphTextItem().setTitle("備註").setRequired(false);
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());

  // 寫入基礎設定
  更新系統設定表(ss, {
    表單編輯網址: form.getEditUrl(),
    表單填寫網址: form.getPublishedUrl(),
  });

  SpreadsheetApp.getUi().alert(
    "飲料訂購表單已建立完成！\n\n填寫網址：\n" + form.getPublishedUrl(),
  );
}

function 建立飲料訂購工作台() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetConfigs = [
    {
      name: "訂購明細",
      headers: [
        "序號",
        "填寫時間",
        "姓名",
        "飲料品項",
        "甜度",
        "冰塊",
        "數量",
        "備註",
      ],
    },
    { name: "品項統計", headers: ["飲料品項", "杯數"] },
    { name: "通知文字", headers: ["產生時間", "通知內容"] },
    { name: "系統設定", headers: ["key", "value"] },
  ];

  初始化多個工作表(ss, sheetConfigs);
  更新系統設定表(ss, {}); // 初始化預設設定值

  SpreadsheetApp.getUi().alert("飲料訂購工作台建立完成！");
}

function 整理訂購明細() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const responseSheet = 尋找表單回覆工作表();
  const detailSheet = ss.getSheetByName("訂購明細");

  if (!responseSheet || !detailSheet) {
    SpreadsheetApp.getUi().alert(
      !responseSheet
        ? "找不到表單回覆工作表，請先建立表單並送出測試資料。"
        : "找不到「訂購明細」，請先執行建立飲料訂購工作台。",
    );
    return;
  }

  const data = responseSheet.getDataRange().getValues();
  if (data.length <= 1) {
    SpreadsheetApp.getUi().alert(
      "目前沒有表單回覆資料，請先填寫幾筆測試資料。",
    );
    return;
  }

  const output = data.slice(1).map((row, index) => [
    index + 1, // 序號
    row[0], // 時間
    row[1], // 姓名
    row[2], // 品項
    row[3], // 甜度
    row[4], // 冰塊
    Number(row[5]) || 0, // 數量
    row[6] || "", // 備註
  ]);

  清空並寫入資料(detailSheet, output);
  SpreadsheetApp.getUi().alert("訂購明細整理完成！");
}

function 統計品項數量() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const detailSheet = ss.getSheetByName("訂購明細");
  const statSheet = ss.getSheetByName("品項統計");

  if (!detailSheet || !statSheet) {
    SpreadsheetApp.getUi().alert(
      "找不到訂購明細或品項統計，請先建立飲料訂購工作台。",
    );
    return;
  }

  const data = detailSheet.getDataRange().getValues();
  if (data.length <= 1) {
    SpreadsheetApp.getUi().alert("訂購明細沒有資料，請先整理訂購明細。");
    return;
  }

  // 優化點：改為動態統計，不再綁死四大品項
  const totals = new Map();
  data.slice(1).forEach((row, index) => {
    const item = String(row[3] || "").trim();
    if (!item) return;

    const quantity = Number(row[6]);
    if (Number.isNaN(quantity)) {
      throw new Error(`訂購明細第 ${index + 2} 列的數量無法加總：${row[6]}`);
    }
    totals.set(item, (totals.get(item) || 0) + quantity);
  });

  const output = Array.from(totals.entries()).map(([item, qty]) => [item, qty]);
  const totalSum = output.reduce((sum, row) => sum + row[1], 0);
  output.push(["總計", totalSum]);

  清空並寫入資料(statSheet, output);
  SpreadsheetApp.getUi().alert("品項統計完成！");
}

function 產生群組通知文字() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const detailSheet = ss.getSheetByName("訂購明細");
  const statSheet = ss.getSheetByName("品項統計");
  const noticeSheet = ss.getSheetByName("通知文字");

  if (!detailSheet || !statSheet || !noticeSheet) {
    SpreadsheetApp.getUi().alert("找不到工作表，請確認工作台已完整建立。");
    return;
  }

  const settings = 取得系統設定();
  const processName = settings["流程名稱"] || "飲料訂購行政流";
  const noticeTitle = settings["通知標題"] || processName;
  const storeName = settings["店家名稱"] || "今天飲料店";
  const closeTime =
    settings["收單時間"] || settings["截止時間"] || "今天 11:00";

  const detailData = detailSheet.getDataRange().getValues();
  const statData = statSheet.getDataRange().getValues();
  if (detailData.length <= 1) {
    SpreadsheetApp.getUi().alert("訂購明細沒有資料，請先整理訂購明細。");
    return;
  }

  const statLines = statData
    .slice(1)
    .filter((row) => row[0] && row[0] !== "總計")
    .map((row) => `${row[0]}：${row[1]}杯`);
  const totalLine = statData.find((row) => row[0] === "總計");
  const totalStr = totalLine ? `\n總計杯數：${totalLine[1]}杯` : "";

  const detailLines = detailData
    .slice(1)
    .filter((row) => row[2])
    .map((row) => {
      const note = row[7] ? `（${row[7]}）` : "";
      return `${row[2]}：${row[3]}／${row[4]}／${row[5]}／${row[6]} 杯${note}`;
    });

  const message = `【${noticeTitle}】
店家名稱：${storeName}
收單時間：${closeTime}

品項統計：
${statLines.join("\n")}${totalStr}

訂購明細：
${detailLines.join("\n")}

確認提醒：請確認姓名、飲料、甜度、冰塊與數量是否正確。`;

  清空並寫入資料(noticeSheet, [[new Date(), message]], false); // 不覆寫標頭
  SpreadsheetApp.getUi().alert(
    `「${processName}」群組通知文字已產生於「通知文字」。`,
  );
}

/** =========================================================================
 * 第二部分：通用行政情境
 * ========================================================================= */

function 建立通用行政工作台與欄位對照() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetConfigs = [
    {
      name: "整理明細",
      headers: [
        "序號",
        "填寫時間",
        "name",
        "category",
        "option1",
        "option2",
        "quantity",
        "note",
      ],
    },
    { name: "分類統計", headers: ["分類", "數量"] },
    { name: "通知文字", headers: ["產生時間", "通知內容"] },
    { name: "系統設定", headers: ["key", "value"] },
    {
      name: "欄位對照",
      headers: ["後端角色", "表單欄位名稱", "是否必要", "說明"],
    },
  ];

  初始化多個工作表(ss, sheetConfigs);
  更新系統設定表(ss, {});

  // 寫入初始對照表資料
  const defaultMappings = [
    ["name", "姓名", "是", "訂購者、報名者或申請人"],
    ["category", "飲料品項", "是", "要統計或分類的主要欄位"],
    ["option1", "甜度", "否", "補充條件一"],
    ["option2", "冰塊", "否", "補充條件二"],
    ["quantity", "數量", "是", "可加總的數字欄位"],
    ["note", "備註", "否", "補充說明、特殊需求或附件連結"],
  ];
  ss.getSheetByName("欄位對照")
    .getRange(2, 1, defaultMappings.length, defaultMappings[0].length)
    .setValues(defaultMappings);
  ss.getSheetByName("欄位對照").autoResizeColumns(1, 4);

  SpreadsheetApp.getUi().alert("通用行政工作台與欄位對照建立完成！");
}

function 整理通用明細() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const responseSheet = 尋找表單回覆工作表();
  const detailSheet = ss.getSheetByName("整理明細");

  if (!responseSheet || !detailSheet) {
    SpreadsheetApp.getUi().alert(
      !responseSheet
        ? "找不到表單回覆工作表，請先建立表單並送出測試資料。"
        : "找不到「整理明細」，請先執行建立通用行政工作台與欄位對照。",
    );
    return;
  }

  const data = responseSheet.getDataRange().getValues();
  if (data.length <= 1) {
    SpreadsheetApp.getUi().alert(
      "目前沒有表單回覆資料，請先填寫幾筆測試資料。",
    );
    return;
  }

  const headerIndex = 建立欄位索引(data[0]);
  const fieldMap = 取得欄位對照();

  try {
    const output = data.slice(1).map((row, index) => {
      const rawQuantity = 依角色取值(row, headerIndex, fieldMap, "quantity");
      const quantity = Number(rawQuantity);
      if (rawQuantity === "" || Number.isNaN(quantity)) {
        throw new Error(`第 ${index + 2} 列的數量欄位無法加總：${rawQuantity}`);
      }

      return [
        index + 1,
        row[0],
        依角色取值(row, headerIndex, fieldMap, "name"),
        依角色取值(row, headerIndex, fieldMap, "category"),
        依角色取值(row, headerIndex, fieldMap, "option1"),
        依角色取值(row, headerIndex, fieldMap, "option2"),
        quantity,
        依角色取值(row, headerIndex, fieldMap, "note"),
      ];
    });

    清空並寫入資料(detailSheet, output);
    SpreadsheetApp.getUi().alert("通用明細整理完成！");
  } catch (error) {
    SpreadsheetApp.getUi().alert(error.message);
    throw error;
  }
}

function 統計分類數量() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const detailSheet = ss.getSheetByName("整理明細");
  const statSheet = ss.getSheetByName("分類統計");

  if (!detailSheet || !statSheet) {
    SpreadsheetApp.getUi().alert(
      "找不到整理明細或分類統計，請先建立通用行政工作台與欄位對照。",
    );
    return;
  }

  const data = detailSheet.getDataRange().getValues();
  if (data.length <= 1) {
    SpreadsheetApp.getUi().alert("整理明細沒有資料，請先整理通用明細。");
    return;
  }

  const headers = data[0].map((h) => String(h || "").trim());
  const categoryIndex = headers.indexOf("category");
  const quantityIndex = headers.indexOf("quantity");

  if (categoryIndex === -1 || quantityIndex === -1) {
    SpreadsheetApp.getUi().alert("整理明細缺少 category 或 quantity 欄位。");
    return;
  }

  const totals = new Map();
  data.slice(1).forEach((row, index) => {
    const category = String(row[categoryIndex] || "").trim();
    if (!category) return;

    const quantity = Number(row[quantityIndex]);
    if (Number.isNaN(quantity)) {
      throw new Error(
        `整理明細第 ${index + 2} 列的 quantity 無法加總：${row[quantityIndex]}`,
      );
    }
    totals.set(category, (totals.get(category) || 0) + quantity);
  });

  const output = Array.from(totals.entries()).map(([cat, qty]) => [cat, qty]);
  const totalSum = output.reduce((sum, row) => sum + row[1], 0);
  output.push(["總計", totalSum]);

  清空並寫入資料(statSheet, output);
  SpreadsheetApp.getUi().alert("分類統計完成！");
}

function 產生通用通知文字() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const detailSheet = ss.getSheetByName("整理明細");
  const statSheet = ss.getSheetByName("分類統計");
  const noticeSheet = ss.getSheetByName("通知文字");

  if (!detailSheet || !statSheet || !noticeSheet) {
    SpreadsheetApp.getUi().alert(
      "找不到必要工作表，請確認通用工作台已完整建立。",
    );
    return;
  }

  const settings = 取得系統設定();
  const processName = settings["流程名稱"] || "飲料訂購行政流";
  const noticeTitle = settings["通知標題"] || processName;
  const deadline = settings["截止時間"] || "今天 11:00";

  const detailData = detailSheet.getDataRange().getValues();
  const statData = statSheet.getDataRange().getValues();
  if (detailData.length <= 1) {
    SpreadsheetApp.getUi().alert("整理明細沒有資料，請先整理通用明細。");
    return;
  }

  const detailHeaders = detailData[0].map((h) => String(h || "").trim());
  const idx = (role) => detailHeaders.indexOf(role);

  const statLines = statData
    .slice(1)
    .filter((row) => row[0] && row[0] !== "總計")
    .map((row) => `${row[0]}：${row[1]}`);
  const totalLine = statData.find((row) => row[0] === "總計");
  const totalStr = totalLine ? `\n總計數量：${totalLine[1]}` : "";

  const detailLines = detailData
    .slice(1)
    .filter((row) => row[idx("name")])
    .map((row) => {
      const note = row[idx("note")] ? `（${row[idx("note")]}）` : "";
      return `${row[idx("name")]}：${row[idx("category")]}／${row[idx("option1")]}／${row[idx("option2")]}／${row[idx("quantity")]}${note}`;
    });

  const message = `【${noticeTitle}】
截止時間：${deadline}

分類統計：
${statLines.join("\n")}${totalStr}

明細：
${detailLines.join("\n")}

請確認以上資料是否正確。`;

  清空並寫入資料(noticeSheet, [[new Date(), message]], false);
  SpreadsheetApp.getUi().alert(
    `「${processName}」通知文字已產生於「通知文字」。`,
  );
}

function 產生行政情境範例對照() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet =
    ss.getSheetByName("行政情境範例對照") || ss.insertSheet("行政情境範例對照");

  const rows = [
    ["行政情境", "name", "category", "option1", "option2", "quantity", "note"],
    ["飲料訂購", "姓名", "飲料品項", "甜度", "冰塊", "數量", "備註"],
    [
      "社團報名",
      "學生姓名",
      "社團志願",
      "年級",
      "班級",
      "報名人數",
      "特殊需求",
    ],
    [
      "研習報名",
      "姓名",
      "研習場次",
      "服務單位",
      "職稱",
      "報名人數",
      "研習需求",
    ],
    [
      "設備借用",
      "借用人",
      "設備名稱",
      "借用日期",
      "歸還日期",
      "借用數量",
      "用途說明",
    ],
    ["成果收件", "繳交人", "成果類型", "班級", "繳交狀態", "件數", "附件連結"],
    [
      "值勤調查",
      "姓名",
      "值勤時段",
      "可支援日期",
      "任務類型",
      "可支援人數",
      "備註",
    ],
  ];

  sheet.clear();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  sheet.getRange(1, 1, 1, rows[0].length).setFontWeight("bold");
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, rows[0].length);

  SpreadsheetApp.getUi().alert("行政情境範例對照已建立完成！");
}

/** =========================================================================
 * 第三部分：共用工具函式 (Helper Functions)
 * ========================================================================= */

/**
 * 批次建立或重設多個工作表並寫入標頭
 */
function 初始化多個工作表(ss, configs) {
  configs.forEach((config) => {
    let sheet = ss.getSheetByName(config.name) || ss.insertSheet(config.name);
    sheet.clear();
    sheet
      .getRange(1, 1, 1, config.headers.length)
      .setValues([config.headers])
      .setFontWeight("bold");
    sheet.setFrozenRows(1);
    sheet.autoResizeColumns(1, config.headers.length);
  });
}

/**
 * 清空並寫入資料，自動調整欄寬
 * @param {Sheet} sheet - 目標工作表
 * @param {Array[]} data - 二維陣列資料
 * @param {boolean} clearHeader - 是否連同第一列標頭一起清除（預設 false，保留標頭）
 */
function 清空並寫入資料(sheet, data, clearHeader = false) {
  const startRow = clearHeader ? 1 : 2;
  const maxRows = sheet.getMaxRows();

  if (maxRows >= startRow) {
    sheet
      .getRange(startRow, 1, maxRows - startRow + 1, sheet.getLastColumn() || 1)
      .clearContent();
  }

  if (data.length > 0) {
    sheet.getRange(startRow, 1, data.length, data[0].length).setValues(data);
    sheet.autoResizeColumns(1, data[0].length);
  }
}

/**
 * 取得系統設定字典物件
 */
function 取得系統設定() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("系統設定");
  const settings = {};
  if (!sheet) return settings;

  sheet
    .getDataRange()
    .getValues()
    .slice(1)
    .forEach((row) => {
      const key = String(row[0] || "").trim();
      if (key && row[1] !== "") settings[key] = row[1];
    });
  return settings;
}

/**
 * 更新或初始化系統設定表
 */
function 更新系統設定表(ss, newConfigs = {}) {
  let sheet = ss.getSheetByName("系統設定") || ss.insertSheet("系統設定");
  if (sheet.getLastColumn() === 0) {
    sheet
      .getRange(1, 1, 1, 2)
      .setValues([["key", "value"]])
      .setFontWeight("bold");
    sheet.setFrozenRows(1);
  }

  const currentSettings = 取得系統設定();
  const defaultSettings = {
    流程名稱: currentSettings["流程名稱"] || "飲料訂購行政流",
    通知模式: currentSettings["通知模式"] || "群組通知",
    通知標題: currentSettings["通知標題"] || "飲料訂購行政流",
    店家名稱: currentSettings["店家名稱"] || "今天飲料店",
    收單時間: currentSettings["收單時間"] || "今天 11:00",
    截止時間:
      currentSettings["截止時間"] ||
      currentSettings["收單時間"] ||
      "今天 11:00",
    表單編輯網址: currentSettings["表單編輯網址"] || "",
    表單填寫網址: currentSettings["表單填寫網址"] || "",
  };

  // 融合新傳入的網址或其他設定
  const finalSettings = Object.assign(defaultSettings, newConfigs);
  const output = Object.entries(finalSettings);

  sheet.getRange(2, 1, sheet.getMaxRows() - 1, 2).clearContent();
  sheet.getRange(2, 1, output.length, 2).setValues(output);
  sheet.autoResizeColumns(1, 2);
}

function 建立欄位索引(headers) {
  const headerIndex = {};
  headers.forEach((header, index) => {
    const name = String(header || "").trim();
    if (name) headerIndex[name] = index;
  });
  return headerIndex;
}

function 取得欄位對照() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("欄位對照");
  if (!sheet)
    throw new Error("找不到「欄位對照」，請先建立通用行政工作台與欄位對照。");

  const data = sheet.getDataRange().getValues();
  const fieldMap = {};
  data.slice(1).forEach((row) => {
    const role = String(row[0] || "").trim();
    const fieldName = String(row[1] || "").trim();
    const required = String(row[2] || "").trim() === "是";
    const description = String(row[3] || "").trim();
    if (role) fieldMap[role] = { fieldName, required, description };
  });
  return fieldMap;
}

function 依角色取值(row, headerIndex, fieldMap, role) {
  const requiredRoles = ["name", "category", "quantity"];
  const config = fieldMap[role];

  if (!config) {
    if (requiredRoles.includes(role))
      throw new Error(`欄位對照缺少必要角色：${role}`);
    return "";
  }

  const fieldName = String(config.fieldName || "").trim();
  if (!fieldName) {
    if (config.required)
      throw new Error(`必要角色 ${role} 沒有設定表單欄位名稱。`);
    return "";
  }

  const index = headerIndex[fieldName];
  if (index === undefined) {
    if (config.required)
      throw new Error(
        `必要欄位不存在：欄位對照中的「${fieldName}」找不到對應的表單欄位。`,
      );
    return "";
  }

  const value = row[index];
  return value === null || value === undefined ? "" : value;
}

function 尋找表單回覆工作表() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  return (
    ss.getSheets().find((sheet) => sheet.getName().startsWith("表單回覆")) ||
    ss.getSheetByName("Form Responses 1")
  );
}