function onClickHandler() {
  var log = "";
  const startTime = new Date(); // Record start time
  log +=
    "<p>" +
    "Starting Attendance Process Execution at " +
    startTime.toLocaleTimeString() +
    "<p/>";
  try {
    const analyzeResult = analyzeAttendance();
    log +=
      "<p>" +
      "analyzeAttendance() returned: " +
      (analyzeResult !== undefined
        ? JSON.stringify(analyzeResult)
        : "undefined") +
      "<p/>";
    const summaryResult = generateAttendanceSummary();
    log +=
      "<p>" +
      "generateAttendanceSummary() returned: " +
      (summaryResult !== undefined
        ? JSON.stringify(summaryResult)
        : "undefined") +
      "<p/>";
    const leaveResult = applyLeaveRequests();
    log +=
      "<p>" +
      "applyLeaveRequests() returned: " +
      (leaveResult !== undefined ? JSON.stringify(leaveResult) : "undefined") +
      "<p/>";
    const formulaResult = addAttendanceStatisticsFormula();
    log +=
      "<p>" +
      "addAttendanceStatisticsFormula() returned: " +
      (formulaResult !== undefined
        ? JSON.stringify(formulaResult)
        : "undefined") +
      "<p/>";
  } catch (e) {
    log += "<p>" + "An error occurred during execution: " + e.message + "<p/>";
  }
  const endTime = new Date(); // Record end time
  const duration = (endTime - startTime) / 1000; // Calculate duration in seconds
  log +=
    "<p>" +
    "All Attendance Processes Completed at " +
    endTime.toLocaleTimeString() +
    " (Duration: " +
    duration.toFixed(2) +
    " seconds)" +
    "<p/>";
  const htmlOutput = HtmlService.createHtmlOutput(log)
    .setWidth(600)
    .setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, "執行紀錄");
}

function analyzeAttendance() {
  console.log("正在分析簽到表單");
  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("點名（回應）");
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  const tsCol = headers.indexOf("時間戳記");
  let statusCol = headers.indexOf("簽到狀態");

  if (statusCol === -1) {
    statusCol = headers.length;
    sheet.getRange(1, statusCol + 1).setValue("簽到狀態");
    headers.push("簽到狀態");
  }

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const timestamp = row[tsCol];

    if (!timestamp) continue;

    const ts = new Date(timestamp);
    const hour = ts.getHours();
    const minute = ts.getMinutes();
    const totalMinutes = hour * 60 + minute;

    let result = "非上課時間";
    const morningSessionStart = 9 * 60 + 30; // 9:30
    const morningSessionEnd = 12 * 60; // 12:00
    const afternoonSessionStart = 13 * 60 + 30; // 13:30
    const afternoonSessionEnd = 16 * 60; // 16:00

    if (
      totalMinutes >= morningSessionStart &&
      totalMinutes <= morningSessionEnd
    ) {
      const startTime = 10 * 60;
      const lateTime = startTime + 10; // 10:10
      const absentTime = startTime + 30; // 10:30

      if (totalMinutes <= lateTime) {
        result = "準時";
      } else if (totalMinutes <= absentTime) {
        result = "遲到";
      } else {
        result = "缺課一小時";
      }
    } else if (
      totalMinutes >= afternoonSessionStart &&
      totalMinutes <= afternoonSessionEnd
    ) {
      const startTime = 14 * 60;
      const lateTime = startTime + 10; // 14:30
      const absentTime = startTime + 30; // 15:00

      if (totalMinutes <= lateTime) {
        result = "準時";
      } else if (totalMinutes <= absentTime) {
        result = "遲到";
      } else {
        result = "缺課一小時";
      }
    }

    sheet.getRange(i + 1, statusCol + 1).setValue(result);
  }
  console.log("表單分析完成");
  return "表單分析完成";
}

function generateAttendanceSummary() {
  console.log("正在整理出缺席資料至匯總表");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("點名（回應）");
  const targetSheetName = "彙總表";

  if (!sourceSheet) {
    console.log("找不到來源工作表 '點名（回應）'，請確認工作表名稱是否正確。");
    return;
  }

  let targetSheet = ss.getSheetByName(targetSheetName);
  if (!targetSheet) {
    targetSheet = ss.insertSheet(targetSheetName);
  } else {
    targetSheet.clear();
  }

  const sessions = [
    "2025/7/14 上午",
    "2025/7/14 下午",
    "2025/7/16 上午",
    "2025/7/16 下午",
    "2025/7/18 上午",
    "2025/7/18 下午",
    "2025/7/21 上午",
    "2025/7/21 下午",
    "2025/7/23 上午",
    "2025/7/23 下午",
    "2025/7/25 上午",
    "2025/7/25 下午",
  ];

  const data = sourceSheet.getDataRange().getValues();
  const headers = data[0];
  const nameCol = headers.indexOf("姓名");
  const timeCol = headers.indexOf("時間戳記");
  const statusCol = headers.indexOf("簽到狀態");

  if (nameCol === -1 || timeCol === -1 || statusCol === -1) {
    console.log("來源工作表中缺少 '姓名'、'時間戳記' 或 '簽到狀態' 欄位。");
    return;
  }

  const studentMap = {};

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    // const name = row[nameCol];
    const name = row[nameCol].toString().trim();
    const timestamp = row[timeCol];
    const status = row[statusCol];

    if (!name || !timestamp || !status) continue;

    const tsDate = new Date(timestamp);
    const hour = tsDate.getHours();
    const minute = tsDate.getMinutes();
    const totalMinutes = hour * 60 + minute;

    let determinedSession = null;
    const morningSessionStart = 9 * 60 + 30;
    const morningSessionEnd = 12 * 60;
    const afternoonSessionStart = 13 * 60 + 30;
    const afternoonSessionEnd = 16 * 60;

    if (
      totalMinutes >= morningSessionStart &&
      totalMinutes <= morningSessionEnd
    ) {
      determinedSession = "上午";
    } else if (
      totalMinutes >= afternoonSessionStart &&
      totalMinutes <= afternoonSessionEnd
    ) {
      determinedSession = "下午";
    }

    if (!determinedSession) continue;

    const dateStr =
      Utilities.formatDate(tsDate, "Asia/Taipei", "yyyy/M/d") +
      " " +
      determinedSession;

    if (!sessions.includes(dateStr)) continue;

    if (!studentMap[name]) {
      studentMap[name] = {};
    }

    const existingStatus = studentMap[name][dateStr];
    if (status === "準時") {
      studentMap[name][dateStr] = status;
    } else if (!existingStatus || existingStatus === "遲到") {
      studentMap[name][dateStr] = status;
    }
  }
  const output = [["姓名", ...sessions]];
  const sortedStudents = Object.keys(studentMap).sort();

  for (const student of sortedStudents) {
    const row = [student];
    for (const session of sessions) {
      row.push(studentMap[student][session] || "未簽到");
    }
    output.push(row);
  }

  if (output.length > 1) {
    targetSheet
      .getRange(1, 1, output.length, output[0].length)
      .setValues(output);
    console.log("出缺席資料整理完成");
    return "出缺席資料整理完成";
  } else {
    targetSheet.getRange(1, 1).setValue("沒有找到符合條件的點名資料。");
    return "沒有找到符合條件的點名資料。";
  }
}

function applyLeaveRequests() {
  console.log("正在分析請假資料");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const leaveSheetName = "名單與請假";
  const summarySheetName = "彙總表";
  const leaveSheet = ss.getSheetByName(leaveSheetName);
  const summarySheet = ss.getSheetByName(summarySheetName);

  if (!leaveSheet) {
    console.log(`找不到名為「${leaveSheetName}」的工作表。`);
    return;
  }
  if (!summarySheet) {
    console.log(
      `找不到名為「${summarySheetName}」的工作表。請先執行 generateAttendanceSummary 函式產生彙總表。`
    );
    return;
  }
  const leaveData = leaveSheet.getDataRange().getValues();
  const summaryData = summarySheet.getDataRange().getValues();
  const leaveMap = {};
  const leaveHeaders = leaveData[1];
  const leaveNameColIndex = 1;

  for (let i = 2; i < leaveData.length; i++) {
    const studentName = leaveData[i][leaveNameColIndex];
    if (!studentName) continue; // 如果姓名為空，跳過此行

    if (!leaveMap[studentName]) {
      leaveMap[studentName] = {};
    }
    for (let j = 2; j < leaveHeaders.length; j++) {
      const session = leaveHeaders[j];
      const status = leaveData[i][j];
      if (status === "請假") {
        leaveMap[studentName][session] = "請假";
      }
    }
  }

  const summaryColMap = {};
  const summaryHeaders = summaryData[0];
  for (let i = 1; i < summaryHeaders.length; i++) {
    summaryColMap[summaryHeaders[i]] = i;
  }

  for (let i = 1; i < summaryData.length; i++) {
    const studentName = summaryData[i][0];
    const studentLeaveInfo = leaveMap[studentName];
    if (studentLeaveInfo) {
      for (const session in studentLeaveInfo) {
        if (summaryColMap.hasOwnProperty(session)) {
          const colIndex = summaryColMap[session];
          summaryData[i][colIndex] = "請假";
        }
      }
    }
  }

  summarySheet
    .getRange(1, 1, summaryData.length, summaryData[0].length)
    .setValues(summaryData);

  console.log("請假記錄已成功更新至「彙總表」。");
  return "請假記錄已成功更新至「彙總表」。";
}

function addAttendanceStatisticsFormula() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheetName = "彙總表";
  const summarySheet = ss.getSheetByName(summarySheetName);

  if (!summarySheet) {
    console.log(`找不到名為「${summarySheetName}」的工作表。`);
    return;
  }

  const lastRow = summarySheet.getLastRow();
  const targetStartCol = 14;

  if (lastRow < 2) {
    console.log("彙總表中沒有學生資料可供統計。");
    return;
  }

  const lastCol = summarySheet.getLastColumn();
  if (lastCol >= targetStartCol) {
    summarySheet
      .getRange(1, targetStartCol, lastRow, lastCol - targetStartCol + 1)
      .clearContent();
  }

  const newHeaders = ["出席時數", "請假時數", "缺席時數", "總計(出席+請假)"];
  const formulas = [];

  for (let i = 2; i <= lastRow; i++) {
    const studentRowRange = `B${i}:M${i}`;
    const presentFormula = `= 2 * COUNTIF(${studentRowRange}, "準時") + 2 * COUNTIF(${studentRowRange}, "遲到") + COUNTIF(${studentRowRange}, "缺課一小時")`;
    const leaveFormula = `= 2 * COUNTIF(${studentRowRange}, "請假")`;
    const absentFormula = `= 2 * COUNTIF(${studentRowRange}, "未簽到") + COUNTIF(${studentRowRange}, "缺課一小時") + COUNTBLANK(${studentRowRange})`;
    const totalFormula = `=N${i}+O${i}`;
    formulas.push([presentFormula, leaveFormula, absentFormula, totalFormula]);
  }

  summarySheet
    .getRange(1, targetStartCol, 1, newHeaders.length)
    .setValues([newHeaders]);
  if (formulas.length > 0) {
    summarySheet
      .getRange(2, targetStartCol, formulas.length, formulas[0].length)
      .setFormulas(formulas);
  }

  summarySheet.clearConditionalFormatRules();

  const formatRange = summarySheet.getRange(
    2,
    targetStartCol,
    lastRow - 1,
    newHeaders.length
  );

  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied("=$P2+$Q2<>24")
    .setBackground("#f4cccc")
    .setRanges([formatRange])
    .build();

  const rules = summarySheet.getConditionalFormatRules();
  rules.push(rule);
  summarySheet.setConditionalFormatRules(rules);

  console.log(
    "出席統計公式已填入，並已加入總數驗證規則。總數不為24的資料列將以紅色標示。"
  );
  return "出席統計公式已填入，並已加入總數驗證規則。總數不為24的資料列將以紅色標示。";
}