用 GAS 做出一套可運轉的社團管理流程

/**
 * 國小社團選課與營運管理平台 - 整合版 Apps Script
 *
 * 此檔由 tools/build-integrated-gs.js 自動產生。
 * 用途：方便直接貼到 Apps Script 編輯器測試。
 * 正式開發請修改 src/ 內的模組檔，再重新執行 npm run build:integrated。
 */

/**
 * Source: src/constants/SheetSchemas.gs
 */
const ClubSheetNames = Object.freeze({
  BASIC_INFO: '00_基本資料',
  SYSTEM_SETTINGS: '00_系統設定',
  STUDENTS: '01_學生名冊',
  CLUBS: '02_社團課程表',
  REGISTRATIONS: '03_社團報名資料',
  ADMISSIONS: '04_錄取名單',
  WAITLIST: '05_候補名單',
  PAYMENTS: '06_繳費管理',
  ATTENDANCE: '07_出席紀錄',
  OUTCOMES: '08_成果資料',
  DASHBOARD: '09_統計儀表板',
  ANNOUNCEMENTS: '10_公告輸出',
  PRE_LAUNCH_CHECKLIST: '11_承辦檢查表',
  LOGS: '99_系統日誌'
});

const AssignmentModes = Object.freeze({
  FIRST_COME: 'FIRST_COME',
  LOTTERY: 'LOTTERY',
  PREFERENCE: 'PREFERENCE'
});

const BasicInfoKeys = Object.freeze({
  SCHOOL_NAME: 'schoolName',
  ACADEMIC_YEAR: 'academicYear',
  SEMESTER: 'semester',
  ORGANIZER_UNIT: 'organizerUnit',
  ORGANIZER_NAME: 'organizerName',
  ORGANIZER_EMAIL: 'organizerEmail',
  ORGANIZER_PHONE: 'organizerPhone',
  REGISTRATION_FORM_TITLE: 'registrationFormTitle',
  REGISTRATION_FORM_DESCRIPTION: 'registrationFormDescription',
  ANNOUNCEMENT_SIGNATURE: 'announcementSignature',
  SCHOOL_WEBSITE: 'schoolWebsite'
});

const SystemSettingKeys = Object.freeze({
  REGISTRATION_START_AT: 'registrationStartAt',
  REGISTRATION_END_AT: 'registrationEndAt',
  ASSIGNMENT_MODE: 'assignmentMode',
  ROOT_FOLDER_ID: 'rootFolderId',
  CALENDAR_ID: 'calendarId',
  INTERNAL_GOOGLE_DOMAIN: 'internalGoogleDomain',
  REGISTRATION_FORM_ID: 'registrationFormId',
  REGISTRATION_FORM_URL: 'registrationFormUrl',
  REGISTRATION_FORM_EDIT_URL: 'registrationFormEditUrl',
  FORM_ACCEPTING_RESPONSES: 'formAcceptingResponses',
  ATTENDANCE_FORM_ID: 'attendanceFormId',
  ATTENDANCE_FORM_URL: 'attendanceFormUrl',
  ATTENDANCE_FORM_EDIT_URL: 'attendanceFormEditUrl',
  ATTENDANCE_FORM_ACCEPTING_RESPONSES: 'attendanceFormAcceptingResponses',
  PUBLISH_MASKED_NAME_ONLY: 'publishMaskedNameOnly'
});

const SheetSchemas = Object.freeze({
  [ClubSheetNames.BASIC_INFO]: {
    headers: ['設定鍵', '設定值', '說明', '更新時間'],
    seedRows: [
      [BasicInfoKeys.SCHOOL_NAME, '', '學校名稱，例如：水月國小', ''],
      [BasicInfoKeys.ACADEMIC_YEAR, '', '學年度，例如：115', ''],
      [BasicInfoKeys.SEMESTER, '', '學期，例如：第一學期', ''],
      [BasicInfoKeys.ORGANIZER_UNIT, '', '承辦單位，例如：學務處', ''],
      [BasicInfoKeys.ORGANIZER_NAME, '', '承辦人姓名', ''],
      [BasicInfoKeys.ORGANIZER_EMAIL, '', '承辦人 Email', ''],
      [BasicInfoKeys.ORGANIZER_PHONE, '', '承辦人電話', ''],
      [BasicInfoKeys.REGISTRATION_FORM_TITLE, '', '報名表標題', ''],
      [BasicInfoKeys.REGISTRATION_FORM_DESCRIPTION, '', '報名表說明', ''],
      [BasicInfoKeys.ANNOUNCEMENT_SIGNATURE, '', '公告署名', ''],
      [BasicInfoKeys.SCHOOL_WEBSITE, '', '學校網站', '']
    ]
  },
  [ClubSheetNames.SYSTEM_SETTINGS]: {
    headers: ['設定鍵', '設定值', '說明', '更新時間'],
    seedRows: [
      [SystemSettingKeys.REGISTRATION_START_AT, '', '報名開始時間', ''],
      [SystemSettingKeys.REGISTRATION_END_AT, '', '報名截止時間', ''],
      [SystemSettingKeys.ASSIGNMENT_MODE, AssignmentModes.PREFERENCE, '分發模式：FIRST_COME、LOTTERY、PREFERENCE', ''],
      [SystemSettingKeys.ROOT_FOLDER_ID, '', 'Drive 根資料夾 ID', ''],
      [SystemSettingKeys.CALENDAR_ID, '', 'Google Calendar ID', ''],
      [SystemSettingKeys.INTERNAL_GOOGLE_DOMAIN, '', '校內 Google Workspace 網域，例如：school.edu.tw', ''],
      [SystemSettingKeys.REGISTRATION_FORM_ID, '', '報名表 Google Form ID', ''],
      [SystemSettingKeys.REGISTRATION_FORM_URL, '', '報名表公開填寫連結', ''],
      [SystemSettingKeys.REGISTRATION_FORM_EDIT_URL, '', '報名表編輯連結', ''],
      [SystemSettingKeys.FORM_ACCEPTING_RESPONSES, 'FALSE', '表單是否收件：TRUE、FALSE', ''],
      [SystemSettingKeys.ATTENDANCE_FORM_ID, '', '簽到表 Google Form ID', ''],
      [SystemSettingKeys.ATTENDANCE_FORM_URL, '', '簽到表公開填寫連結', ''],
      [SystemSettingKeys.ATTENDANCE_FORM_EDIT_URL, '', '簽到表編輯連結', ''],
      [SystemSettingKeys.ATTENDANCE_FORM_ACCEPTING_RESPONSES, 'FALSE', '簽到表是否收件：TRUE、FALSE', ''],
      [SystemSettingKeys.PUBLISH_MASKED_NAME_ONLY, 'TRUE', '對外公告只顯示遮蔽姓名', '']
    ]
  },
  [ClubSheetNames.STUDENTS]: {
    headers: ['學號', '學生Google帳號', '姓名', '遮蔽姓名', '班級', '座號', '年級', '家長Email', '聯絡電話', '狀態', '更新時間']
  },
  [ClubSheetNames.CLUBS]: {
    headers: ['社團ID', '社團名稱', '指導老師', '上課地點', '星期', '節次', '上課時間', '招收人數', '社團費用', '招收年級', '開課狀態', '成果資料夾ID', '更新時間']
  },
  [ClubSheetNames.REGISTRATIONS]: {
    headers: ['報名ID', '表單回應ID', '報名Google帳號', '時間戳記', '學號', '學生姓名', '遮蔽姓名', '班級', '座號', '家長Email', '聯絡電話', '第一志願社團ID', '第二志願社團ID', '第三志願社團ID', '驗證狀態', '驗證訊息', '報名狀態']
  },
  [ClubSheetNames.ADMISSIONS]: {
    headers: ['錄取ID', '分發批次ID', '分發模式', '社團ID', '社團名稱', '學號', '學生姓名', '遮蔽姓名', '家長Email', '聯絡電話', '錄取志願', '錄取狀態', '建立時間']
  },
  [ClubSheetNames.WAITLIST]: {
    headers: ['候補ID', '分發批次ID', '分發模式', '社團ID', '社團名稱', '學號', '學生姓名', '遮蔽姓名', '家長Email', '聯絡電話', '候補志願', '候補序號', '候補狀態', '建立時間']
  },
  [ClubSheetNames.PAYMENTS]: {
    headers: ['繳費ID', '學號', '學生姓名', '社團ID', '社團名稱', '應繳金額', '已繳金額', '繳費狀態', '備註']
  },
  [ClubSheetNames.ATTENDANCE]: {
    headers: ['出席ID', '表單回應ID', '時間戳記', '社團ID', '社團名稱', '學號', '學生姓名', '簽到Google帳號', '日期', '出席狀態', '點名老師', '驗證狀態', '驗證訊息', '備註']
  },
  [ClubSheetNames.OUTCOMES]: {
    headers: ['成果ID', '社團ID', '社團名稱', '類型', '標題', 'Drive檔案ID', 'Drive連結', '是否公開', '建立時間']
  },
  [ClubSheetNames.DASHBOARD]: {
    headers: ['指標', '數值', '更新時間']
  },
  [ClubSheetNames.ANNOUNCEMENTS]: {
    headers: ['公告ID', '公告類型', '標題', '內容', '建立時間', '建立者', '備註']
  },
  [ClubSheetNames.PRE_LAUNCH_CHECKLIST]: {
    headers: ['檢查項目', '狀態', '說明', '建議處理方式', '檢查時間']
  },
  [ClubSheetNames.LOGS]: {
    headers: ['時間', '等級', '動作', '訊息', '執行者', '詳細資料']
  }
});


/**
 * Source: src/domain/Privacy.gs
 */
function maskStudentName_(name) {
  const normalizedName = String(name || '').trim();

  if (!normalizedName) {
    return '';
  }

  if (normalizedName.length === 1) {
    return 'O';
  }

  if (normalizedName.length === 2) {
    return normalizedName.charAt(0) + 'O';
  }

  return normalizedName.charAt(0) + 'O' + normalizedName.charAt(normalizedName.length - 1);
}


/**
 * Source: src/domain/Auth.gs
 */
function normalizeEmail_(email) {
  return String(email || '').trim().toLowerCase();
}

function normalizeGoogleDomain_(domain) {
  return String(domain || '').trim().toLowerCase().replace(/^@+/, '');
}

function isEmailInDomain_(email, domain) {
  const normalizedEmail = normalizeEmail_(email);
  const normalizedDomain = normalizeGoogleDomain_(domain);

  if (!normalizedEmail || !normalizedDomain) {
    return false;
  }

  return normalizedEmail.endsWith('@' + normalizedDomain);
}

function validateInternalGoogleEmail_(email, domain, issues, label) {
  const normalizedEmail = normalizeEmail_(email);
  const normalizedDomain = normalizeGoogleDomain_(domain);

  if (!normalizedEmail) {
    issues.push(label + '缺少 Google 帳號');
    return;
  }

  if (!isValidEmail_(normalizedEmail)) {
    issues.push(label + 'Google 帳號格式可能錯誤');
    return;
  }

  if (normalizedDomain && !isEmailInDomain_(normalizedEmail, normalizedDomain)) {
    issues.push(label + 'Google 帳號不是校內網域：' + normalizedDomain);
  }
}


/**
 * Source: src/domain/ClubCode.gs
 */
function generateNextClubId_(existingIds) {
  let maxSequence = 0;

  existingIds.forEach(function(id) {
    const match = String(id || '').trim().match(/^CLUB-(\d{3})$/);
    if (match) {
      maxSequence = Math.max(maxSequence, Number(match[1]));
    }
  });

  return 'CLUB-' + String(maxSequence + 1).padStart(3, '0');
}


/**
 * Source: src/domain/Assignment.gs
 */
function runAssignment_(registrations, clubs, mode, seed) {
  const normalizedMode = String(mode || 'PREFERENCE').trim();
  const clubMap = buildAssignmentClubMap_(clubs);
  const admittedStudentIds = {};
  const admissions = [];

  getAssignmentCandidates_(registrations, normalizedMode, seed).forEach(function(candidate) {
    if (admittedStudentIds[candidate.studentId]) {
      return;
    }

    const club = clubMap[candidate.clubId];
    if (!club || club.remainingCapacity <= 0) {
      return;
    }

    club.remainingCapacity -= 1;
    admittedStudentIds[candidate.studentId] = true;
    admissions.push({
      registrationId: candidate.registrationId,
      studentId: candidate.studentId,
      studentName: candidate.studentName,
      maskedName: candidate.maskedName,
      parentEmail: candidate.parentEmail,
      parentPhone: candidate.parentPhone,
      clubId: candidate.clubId,
      clubName: club.clubName,
      choiceRank: candidate.choiceRank
    });
  });

  const waitlist = buildAssignmentWaitlist_(registrations, clubMap, admittedStudentIds, normalizedMode, seed);

  return {
    mode: normalizedMode,
    admissions: admissions,
    waitlist: waitlist
  };
}

function buildAssignmentClubMap_(clubs) {
  const clubMap = {};

  clubs.forEach(function(club) {
    const clubId = String(club.clubId || '').trim();
    if (!clubId) return;

    const capacity = Math.max(0, Number(club.capacity || 0));
    clubMap[clubId] = {
      clubId: clubId,
      clubName: String(club.clubName || '').trim(),
      capacity: capacity,
      remainingCapacity: capacity
    };
  });

  return clubMap;
}

function getAssignmentCandidates_(registrations, mode, seed) {
  if (mode === 'FIRST_COME') {
    return buildSingleChoiceCandidates_(registrations, 1).sort(compareByTimestamp_);
  }

  if (mode === 'LOTTERY') {
    return buildSingleChoiceCandidates_(registrations, 1).sort(function(a, b) {
      return compareByLottery_(a, b, seed);
    });
  }

  return [1, 2, 3].flatMap(function(choiceRank) {
    return buildSingleChoiceCandidates_(registrations, choiceRank).sort(compareByTimestamp_);
  });
}

function buildSingleChoiceCandidates_(registrations, choiceRank) {
  return registrations.map(function(registration) {
    const clubId = registration.choices[choiceRank - 1];
    if (!clubId) {
      return null;
    }

    return {
      registrationId: registration.registrationId,
      timestamp: registration.timestamp,
      studentId: registration.studentId,
      studentName: registration.studentName,
      maskedName: registration.maskedName,
      parentEmail: registration.parentEmail,
      parentPhone: registration.parentPhone,
      clubId: clubId,
      choiceRank: choiceRank
    };
  }).filter(function(candidate) {
    return candidate !== null;
  });
}

function buildAssignmentWaitlist_(registrations, clubMap, admittedStudentIds, mode, seed) {
  const waitlistByClub = {};

  registrations.forEach(function(registration) {
    if (admittedStudentIds[registration.studentId]) {
      return;
    }

    const firstChoiceClubId = registration.choices[0];
    if (!firstChoiceClubId || !clubMap[firstChoiceClubId]) {
      return;
    }

    if (!waitlistByClub[firstChoiceClubId]) {
      waitlistByClub[firstChoiceClubId] = [];
    }

    waitlistByClub[firstChoiceClubId].push({
      registrationId: registration.registrationId,
      timestamp: registration.timestamp,
      studentId: registration.studentId,
      studentName: registration.studentName,
      maskedName: registration.maskedName,
      parentEmail: registration.parentEmail,
      parentPhone: registration.parentPhone,
      clubId: firstChoiceClubId,
      clubName: clubMap[firstChoiceClubId].clubName,
      choiceRank: 1
    });
  });

  return Object.keys(waitlistByClub).flatMap(function(clubId) {
    const rows = waitlistByClub[clubId].sort(function(a, b) {
      if (mode === 'LOTTERY') {
        return compareByLottery_(a, b, seed);
      }
      return compareByTimestamp_(a, b);
    });

    return rows.map(function(row, index) {
      return Object.assign({}, row, { waitlistOrder: index + 1 });
    });
  });
}

function compareByTimestamp_(a, b) {
  const aTime = new Date(a.timestamp || 0).getTime();
  const bTime = new Date(b.timestamp || 0).getTime();

  if (aTime !== bTime) {
    return aTime - bTime;
  }

  return String(a.registrationId || '').localeCompare(String(b.registrationId || ''));
}

function compareByLottery_(a, b, seed) {
  const aScore = deterministicLotteryScore_(seed, a.studentId + '|' + a.clubId);
  const bScore = deterministicLotteryScore_(seed, b.studentId + '|' + b.clubId);

  if (aScore !== bScore) {
    return aScore - bScore;
  }

  return compareByTimestamp_(a, b);
}

function deterministicLotteryScore_(seed, value) {
  const input = String(seed || '') + '|' + String(value || '');
  let hash = 2166136261;

  for (let index = 0; index < input.length; index += 1) {
    hash ^= input.charCodeAt(index);
    hash += (hash << 1) + (hash << 4) + (hash << 7) + (hash << 8) + (hash << 24);
  }

  return hash >>> 0;
}


/**
 * Source: src/repositories/SheetRepository.gs
 */
function getActiveSpreadsheet_() {
  return SpreadsheetApp.getActiveSpreadsheet();
}

function getOrCreateSheet_(sheetName) {
  const spreadsheet = getActiveSpreadsheet_();
  return spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);
}

function ensureSheetSchema_(sheetName, schema) {
  const sheet = getOrCreateSheet_(sheetName);
  const headers = schema.headers || [];

  if (headers.length === 0) {
    return sheet;
  }

  const headerRange = sheet.getRange(1, 1, 1, headers.length);
  const currentHeaders = headerRange.getValues()[0];
  const hasAnyHeader = currentHeaders.some(function(value) {
    return String(value || '').trim() !== '';
  });

  if (!hasAnyHeader) {
    headerRange.setValues([headers]);
    headerRange.setFontWeight('bold');
    sheet.setFrozenRows(1);
  } else {
    const mismatch = headers.some(function(header, index) {
      return currentHeaders[index] !== header;
    });

    if (mismatch) {
      migrateSheetToSchema_(sheet, headers);
    }
  }

  if (schema.seedRows && schema.seedRows.length > 0) {
    appendMissingSeedRows_(sheet, schema.seedRows, headers.length);
  }

  sheet.autoResizeColumns(1, headers.length);
  return sheet;
}

function appendMissingSeedRows_(sheet, seedRows, columnCount) {
  const lastRow = sheet.getLastRow();
  const existingKeys = {};

  if (lastRow >= 2) {
    sheet.getRange(2, 1, lastRow - 1, 1).getValues().forEach(function(row) {
      const key = String(row[0] || '').trim();
      if (key) {
        existingKeys[key] = true;
      }
    });
  }

  const rowsToAppend = seedRows.filter(function(row) {
    const key = String(row[0] || '').trim();
    return key && !existingKeys[key];
  }).map(function(row) {
    return row.concat(new Array(Math.max(0, columnCount - row.length)).fill('')).slice(0, columnCount);
  });

  if (rowsToAppend.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rowsToAppend.length, columnCount).setValues(rowsToAppend);
  }
}

function migrateSheetToSchema_(sheet, expectedHeaders) {
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const currentHeaders = sheet.getRange(1, 1, 1, lastColumn).getValues()[0].map(function(header) {
    return String(header || '').trim();
  });
  const extraHeaders = currentHeaders.filter(function(header) {
    return header && expectedHeaders.indexOf(header) === -1;
  });
  const finalHeaders = expectedHeaders.concat(extraHeaders);
  const existingRows = lastRow >= 2 ? sheet.getRange(2, 1, lastRow - 1, lastColumn).getValues() : [];
  const migratedRows = existingRows.map(function(row) {
    return finalHeaders.map(function(header) {
      const oldIndex = currentHeaders.indexOf(header);
      return oldIndex >= 0 ? row[oldIndex] : '';
    });
  });

  sheet.clearContents();
  sheet.getRange(1, 1, 1, finalHeaders.length).setValues([finalHeaders]);
  sheet.getRange(1, 1, 1, finalHeaders.length).setFontWeight('bold');

  if (migratedRows.length > 0) {
    sheet.getRange(2, 1, migratedRows.length, finalHeaders.length).setValues(migratedRows);
  }

  sheet.setFrozenRows(1);
}

function ensureAllSheetSchemas_() {
  Object.keys(SheetSchemas).forEach(function(sheetName) {
    ensureSheetSchema_(sheetName, SheetSchemas[sheetName]);
  });
}

function readKeyValueSheet_(sheetName) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();
  const values = {};

  if (lastRow < 2) {
    return values;
  }

  const rows = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  rows.forEach(function(row) {
    const key = String(row[0] || '').trim();
    if (key) {
      values[key] = row[1];
    }
  });

  return values;
}

function writeKeyValue_(sheetName, key, value) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();

  if (lastRow >= 2) {
    const keys = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
    for (let index = 0; index < keys.length; index += 1) {
      if (String(keys[index][0] || '').trim() === key) {
        sheet.getRange(index + 2, 2).setValue(value);
        sheet.getRange(index + 2, 4).setValue(new Date());
        return;
      }
    }
  }

  sheet.appendRow([key, value, '', new Date()]);
}

function getHeaderMap_(sheet) {
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  const headerMap = {};

  headers.forEach(function(header, index) {
    const normalizedHeader = String(header || '').trim();
    if (normalizedHeader) {
      headerMap[normalizedHeader] = index + 1;
    }
  });

  return headerMap;
}

function requireColumn_(headerMap, headerName) {
  const column = headerMap[headerName];
  if (!column) {
    throw new Error('找不到必要欄位：' + headerName);
  }
  return column;
}

function clearRowsByColumnValue_(sheetName, columnHeader, targetValue) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return 0;
  }

  const headerMap = getHeaderMap_(sheet);
  const targetColumn = requireColumn_(headerMap, columnHeader);
  let deletedCount = 0;

  for (let rowIndex = lastRow; rowIndex >= 2; rowIndex -= 1) {
    const value = String(sheet.getRange(rowIndex, targetColumn).getValue() || '').trim();
    if (value === targetValue) {
      sheet.deleteRow(rowIndex);
      deletedCount += 1;
    }
  }

  return deletedCount;
}

function hasRowWithColumnValue_(sheetName, columnHeader, targetValue) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return false;
  }

  const headerMap = getHeaderMap_(sheet);
  const targetColumn = requireColumn_(headerMap, columnHeader);
  const values = sheet.getRange(2, targetColumn, lastRow - 1, 1).getValues();

  return values.some(function(row) {
    return String(row[0] || '').trim() === String(targetValue || '').trim();
  });
}

function appendRowByHeaders_(sheetName, valuesByHeader) {
  const sheet = getOrCreateSheet_(sheetName);
  const headerMap = getHeaderMap_(sheet);
  const lastColumn = sheet.getLastColumn();
  const row = new Array(lastColumn).fill('');

  Object.keys(valuesByHeader).forEach(function(header) {
    const column = headerMap[header];
    if (column) {
      row[column - 1] = valuesByHeader[header];
    }
  });

  sheet.appendRow(row);
}

function replaceSheetRowsByHeaders_(sheetName, rowsByHeader) {
  const sheet = getOrCreateSheet_(sheetName);
  const headerMap = getHeaderMap_(sheet);
  const lastColumn = sheet.getLastColumn();
  const lastRow = sheet.getLastRow();

  if (lastRow >= 2) {
    sheet.getRange(2, 1, lastRow - 1, lastColumn).clearContent();
  }

  if (!rowsByHeader || rowsByHeader.length === 0) {
    return 0;
  }

  const rows = rowsByHeader.map(function(valuesByHeader) {
    const row = new Array(lastColumn).fill('');
    Object.keys(valuesByHeader).forEach(function(header) {
      const column = headerMap[header];
      if (column) {
        row[column - 1] = valuesByHeader[header];
      }
    });
    return row;
  });

  sheet.getRange(2, 1, rows.length, lastColumn).setValues(rows);
  return rows.length;
}

function deleteRowsWhere_(sheetName, predicate) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return 0;
  }

  const headerMap = getHeaderMap_(sheet);
  const lastColumn = sheet.getLastColumn();
  const values = sheet.getRange(2, 1, lastRow - 1, lastColumn).getValues();
  let deletedCount = 0;

  for (let offset = values.length - 1; offset >= 0; offset -= 1) {
    if (predicate(rowToObject_(values[offset], headerMap))) {
      sheet.deleteRow(offset + 2);
      deletedCount += 1;
    }
  }

  return deletedCount;
}

function readSheetObjects_(sheetName) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  if (lastRow < 2 || lastColumn < 1) {
    return [];
  }

  const headerMap = getHeaderMap_(sheet);
  return sheet.getRange(2, 1, lastRow - 1, lastColumn).getValues().map(function(row) {
    return rowToObject_(row, headerMap);
  });
}

function rowToObject_(row, headerMap) {
  const object = {};
  Object.keys(headerMap).forEach(function(header) {
    object[header] = row[headerMap[header] - 1];
  });
  return object;
}

function appendSystemLog_(level, action, message, details) {
  const sheet = getOrCreateSheet_(ClubSheetNames.LOGS);
  const actor = Session.getActiveUser().getEmail();
  sheet.appendRow([
    new Date(),
    level,
    action,
    message,
    actor,
    details ? JSON.stringify(details) : ''
  ]);
}


/**
 * Source: src/repositories/ConfigRepository.gs
 */
function getBasicInfo_() {
  return readKeyValueSheet_(ClubSheetNames.BASIC_INFO);
}

function getSystemSettings_() {
  return readKeyValueSheet_(ClubSheetNames.SYSTEM_SETTINGS);
}

function getAssignmentMode_() {
  const settings = getSystemSettings_();
  const mode = String(settings[SystemSettingKeys.ASSIGNMENT_MODE] || AssignmentModes.PREFERENCE).trim();
  const allowedModes = Object.keys(AssignmentModes).map(function(key) {
    return AssignmentModes[key];
  });

  if (allowedModes.indexOf(mode) === -1) {
    throw new Error('分發模式設定錯誤：' + mode);
  }

  return mode;
}

function updateSystemSetting_(key, value) {
  writeKeyValue_(ClubSheetNames.SYSTEM_SETTINGS, key, value);
}

function validateBasicInfo_() {
  const basicInfo = getBasicInfo_();
  const requiredKeys = [
    BasicInfoKeys.SCHOOL_NAME,
    BasicInfoKeys.ACADEMIC_YEAR,
    BasicInfoKeys.SEMESTER,
    BasicInfoKeys.ORGANIZER_UNIT,
    BasicInfoKeys.ORGANIZER_NAME,
    BasicInfoKeys.ORGANIZER_EMAIL,
    BasicInfoKeys.REGISTRATION_FORM_TITLE,
    BasicInfoKeys.REGISTRATION_FORM_DESCRIPTION
  ];

  const missingKeys = requiredKeys.filter(function(key) {
    return String(basicInfo[key] || '').trim() === '';
  });

  return {
    valid: missingKeys.length === 0,
    missingKeys: missingKeys
  };
}


/**
 * Source: src/adapters/FormAdapter.gs
 */
function createRegistrationFormFromBasicInfo_(basicInfo, clubOptions) {
  const title = String(basicInfo[BasicInfoKeys.REGISTRATION_FORM_TITLE] || '').trim();
  const description = buildRegistrationFormDescription_(basicInfo);
  const form = FormApp.create(title);
  const choiceValues = clubOptions.map(function(club) {
    return formatClubChoiceValue_(club);
  });

  form.setDescription(description);
  applyInternalGoogleAccountFormSettings_(form, true);
  form.setDestination(FormApp.DestinationType.SPREADSHEET, getActiveSpreadsheet_().getId());
  form.setAcceptingResponses(false);

  form.addTextItem()
    .setTitle('學號')
    .setRequired(true);

  form.addTextItem()
    .setTitle('學生姓名')
    .setRequired(true);

  form.addTextItem()
    .setTitle('班級')
    .setRequired(true);

  form.addTextItem()
    .setTitle('座號')
    .setRequired(true);

  form.addTextItem()
    .setTitle('家長Email')
    .setRequired(true);

  form.addTextItem()
    .setTitle('聯絡電話')
    .setRequired(false);

  form.addListItem()
    .setTitle('第一志願社團')
    .setChoiceValues(choiceValues)
    .setRequired(true);

  form.addListItem()
    .setTitle('第二志願社團')
    .setChoiceValues(choiceValues)
    .setRequired(false);

  form.addListItem()
    .setTitle('第三志願社團')
    .setChoiceValues(choiceValues)
    .setRequired(false);

  return {
    id: form.getId(),
    url: form.getPublishedUrl(),
    editUrl: form.getEditUrl()
  };
}

function createAttendanceFormFromBasicInfo_(basicInfo, clubOptions) {
  const schoolName = String(basicInfo[BasicInfoKeys.SCHOOL_NAME] || '').trim();
  const academicYear = String(basicInfo[BasicInfoKeys.ACADEMIC_YEAR] || '').trim();
  const semester = String(basicInfo[BasicInfoKeys.SEMESTER] || '').trim();
  const title = [schoolName, academicYear + '學年度', semester, '社團簽到表'].filter(function(part) {
    return String(part || '').trim() !== '';
  }).join(' ');
  const form = FormApp.create(title || '社團簽到表');
  const choiceValues = clubOptions.map(function(club) {
    return formatClubChoiceValue_(club);
  });

  form.setDescription('請使用校內 Google 帳號登入後簽到。');
  applyInternalGoogleAccountFormSettings_(form, false);
  form.setDestination(FormApp.DestinationType.SPREADSHEET, getActiveSpreadsheet_().getId());
  form.setAcceptingResponses(false);

  form.addListItem()
    .setTitle('社團')
    .setChoiceValues(choiceValues)
    .setRequired(true);

  form.addTextItem()
    .setTitle('學號')
    .setRequired(true);

  form.addTextItem()
    .setTitle('備註')
    .setRequired(false);

  return {
    id: form.getId(),
    url: form.getPublishedUrl(),
    editUrl: form.getEditUrl()
  };
}

function applyInternalGoogleAccountFormSettings_(form, limitOneResponsePerUser) {
  form.setCollectEmail(true);

  if (typeof form.setRequireLogin === 'function') {
    try {
      form.setRequireLogin(true);
    } catch (error) {
      appendSystemLog_('WARN', 'applyInternalGoogleAccountFormSettings', '表單無法啟用同網域登入限制', {
        message: error.message
      });
    }
  }

  if (limitOneResponsePerUser && typeof form.setLimitOneResponsePerUser === 'function') {
    form.setLimitOneResponsePerUser(true);
  }
}

function formatClubChoiceValue_(club) {
  return club.clubId + '｜' + club.clubName;
}

function parseClubChoiceValue_(value) {
  return String(value || '').split('｜')[0].trim();
}

function getFormResponseRespondentEmail_(formResponse) {
  if (formResponse && typeof formResponse.getRespondentEmail === 'function') {
    return normalizeEmail_(formResponse.getRespondentEmail());
  }

  return '';
}

function buildRegistrationFormDescription_(basicInfo) {
  return [
    String(basicInfo[BasicInfoKeys.SCHOOL_NAME] || '').trim(),
    String(basicInfo[BasicInfoKeys.ACADEMIC_YEAR] || '').trim() + '學年度 ' + String(basicInfo[BasicInfoKeys.SEMESTER] || '').trim(),
    '',
    String(basicInfo[BasicInfoKeys.REGISTRATION_FORM_DESCRIPTION] || '').trim(),
    '',
    '承辦單位：' + String(basicInfo[BasicInfoKeys.ORGANIZER_UNIT] || '').trim(),
    '承辦人：' + String(basicInfo[BasicInfoKeys.ORGANIZER_NAME] || '').trim(),
    'Email：' + String(basicInfo[BasicInfoKeys.ORGANIZER_EMAIL] || '').trim(),
    '電話：' + String(basicInfo[BasicInfoKeys.ORGANIZER_PHONE] || '').trim()
  ].join('\n');
}


/**
 * Source: src/adapters/TriggerAdapter.gs
 */
function installRegistrationFormSubmitTrigger_(formId) {
  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (trigger.getHandlerFunction() === 'handleRegistrationFormSubmit') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('handleRegistrationFormSubmit')
    .forForm(FormApp.openById(formId))
    .onFormSubmit()
    .create();
}

function installAttendanceFormSubmitTrigger_(formId) {
  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (trigger.getHandlerFunction() === 'handleAttendanceFormSubmit') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('handleAttendanceFormSubmit')
    .forForm(FormApp.openById(formId))
    .onFormSubmit()
    .create();
}

function setRegistrationFormAcceptingResponses_(acceptingResponses) {
  const settings = getSystemSettings_();
  const formId = String(settings[SystemSettingKeys.REGISTRATION_FORM_ID] || '').trim();

  if (!formId) {
    throw new Error('尚未建立報名表，找不到 registrationFormId。');
  }

  const form = FormApp.openById(formId);
  applyInternalGoogleAccountFormSettings_(form, true);
  form.setAcceptingResponses(Boolean(acceptingResponses));
  updateSystemSetting_(SystemSettingKeys.FORM_ACCEPTING_RESPONSES, acceptingResponses ? 'TRUE' : 'FALSE');
}

function setAttendanceFormAcceptingResponses_(acceptingResponses) {
  const settings = getSystemSettings_();
  const formId = String(settings[SystemSettingKeys.ATTENDANCE_FORM_ID] || '').trim();

  if (!formId) {
    throw new Error('尚未建立簽到表，找不到 attendanceFormId。');
  }

  const form = FormApp.openById(formId);
  applyInternalGoogleAccountFormSettings_(form, false);
  form.setAcceptingResponses(Boolean(acceptingResponses));
  updateSystemSetting_(SystemSettingKeys.ATTENDANCE_FORM_ACCEPTING_RESPONSES, acceptingResponses ? 'TRUE' : 'FALSE');
}


/**
 * Source: src/usecases/ErrorHandling.gs
 */
function handlePublicFunctionError_(action, userMessage, error, details) {
  const safeDetails = details || {};
  safeDetails.errorMessage = error && error.message ? error.message : String(error);
  safeDetails.stack = error && error.stack ? error.stack : '';

  try {
    appendSystemLog_('ERROR', action, userMessage, safeDetails);
  } catch (logError) {
    console.error(logError);
  }

  try {
    SpreadsheetApp.getUi().alert(userMessage + '\n\n請查看「99_系統日誌」的 ERROR 紀錄，或將錯誤時間提供給資訊協助人員。');
  } catch (uiError) {
    console.error(uiError);
  }

  return {
    success: false,
    message: userMessage,
    error: safeDetails.errorMessage
  };
}

function getActiveUserEmail_() {
  try {
    return Session.getActiveUser().getEmail();
  } catch (error) {
    return '';
  }
}


/**
 * Source: src/usecases/InitializeSemester.gs
 */
function initializeSemester() {
  try {
    ensureAllSheetSchemas_();
    appendSystemLog_('INFO', 'initializeSemester', '已建立或確認所有必要工作表', {});

    SpreadsheetApp.getUi().alert('初始化完成。請先填寫 00_基本資料，再產生報名表。');
  } catch (error) {
    return handlePublicFunctionError_('initializeSemester', '初始化新學期時發生問題，工作表尚未完整建立。', error);
  }
}

function checkBasicInfoBeforeRegistrationForm() {
  const result = validateBasicInfo_();

  if (!result.valid) {
    throw new Error('00_基本資料尚未填完整，缺少：' + result.missingKeys.join(', '));
  }

  return true;
}


/**
 * Source: src/usecases/StudentRoster.gs
 */
function refreshStudentMaskedNames() {
  try {
  ensureAllSheetSchemas_();

  const sheet = getOrCreateSheet_(ClubSheetNames.STUDENTS);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('學生名冊沒有資料。');
    return { updatedCount: 0 };
  }

  const headerMap = getHeaderMap_(sheet);
  const nameColumn = requireColumn_(headerMap, '姓名');
  const maskedNameColumn = requireColumn_(headerMap, '遮蔽姓名');
  const updatedAtColumn = requireColumn_(headerMap, '更新時間');
  let updatedCount = 0;

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const name = sheet.getRange(rowIndex, nameColumn).getValue();
    const maskedName = maskStudentName_(name);
    if (maskedName) {
      sheet.getRange(rowIndex, maskedNameColumn).setValue(maskedName);
      sheet.getRange(rowIndex, updatedAtColumn).setValue(new Date());
      updatedCount += 1;
    }
  }

  appendSystemLog_('INFO', 'refreshStudentMaskedNames', '已更新學生遮蔽姓名', { updatedCount: updatedCount });
  SpreadsheetApp.getUi().alert('已更新學生遮蔽姓名：' + updatedCount + ' 筆');

  return { updatedCount: updatedCount };
  } catch (error) {
    return handlePublicFunctionError_('refreshStudentMaskedNames', '更新學生遮蔽姓名時發生問題。請確認學生名冊欄位完整。', error);
  }
}

function validateStudentRoster() {
  try {
  ensureAllSheetSchemas_();

  const sheet = getOrCreateSheet_(ClubSheetNames.STUDENTS);
  const lastRow = sheet.getLastRow();
  const issues = [];

  if (lastRow < 2) {
    issues.push('學生名冊沒有資料。');
    showStudentRosterValidationResult_(issues);
    return { valid: false, issues: issues };
  }

  const headerMap = getHeaderMap_(sheet);
  const internalGoogleDomain = getInternalGoogleDomain_();
  const columns = {
    studentId: requireColumn_(headerMap, '學號'),
    googleEmail: requireColumn_(headerMap, '學生Google帳號'),
    name: requireColumn_(headerMap, '姓名'),
    maskedName: requireColumn_(headerMap, '遮蔽姓名'),
    className: requireColumn_(headerMap, '班級'),
    seatNumber: requireColumn_(headerMap, '座號'),
    grade: requireColumn_(headerMap, '年級'),
    parentEmail: requireColumn_(headerMap, '家長Email')
  };
  const seenStudentIds = {};
  const seenGoogleEmails = {};

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const studentId = String(sheet.getRange(rowIndex, columns.studentId).getValue() || '').trim();
    const googleEmail = normalizeEmail_(sheet.getRange(rowIndex, columns.googleEmail).getValue());
    const name = String(sheet.getRange(rowIndex, columns.name).getValue() || '').trim();
    const maskedName = String(sheet.getRange(rowIndex, columns.maskedName).getValue() || '').trim();
    const className = String(sheet.getRange(rowIndex, columns.className).getValue() || '').trim();
    const seatNumber = String(sheet.getRange(rowIndex, columns.seatNumber).getValue() || '').trim();
    const grade = String(sheet.getRange(rowIndex, columns.grade).getValue() || '').trim();
    const parentEmail = String(sheet.getRange(rowIndex, columns.parentEmail).getValue() || '').trim();

    if (!studentId) issues.push('第 ' + rowIndex + ' 列缺少學號。');
    if (!googleEmail) issues.push('第 ' + rowIndex + ' 列缺少學生Google帳號。');
    if (!name) issues.push('第 ' + rowIndex + ' 列缺少姓名。');
    if (!maskedName) issues.push('第 ' + rowIndex + ' 列缺少遮蔽姓名。');
    if (!className) issues.push('第 ' + rowIndex + ' 列缺少班級。');
    if (!seatNumber) issues.push('第 ' + rowIndex + ' 列缺少座號。');
    if (!grade) issues.push('第 ' + rowIndex + ' 列缺少年級。');

    if (studentId) {
      if (seenStudentIds[studentId]) {
        issues.push('第 ' + rowIndex + ' 列學號重複：' + studentId + '。');
      }
      seenStudentIds[studentId] = true;
    }

    if (googleEmail) {
      if (!isValidEmail_(googleEmail)) {
        issues.push('第 ' + rowIndex + ' 列學生Google帳號格式可能錯誤：' + googleEmail + '。');
      } else if (internalGoogleDomain && !isEmailInDomain_(googleEmail, internalGoogleDomain)) {
        issues.push('第 ' + rowIndex + ' 列學生Google帳號不是校內網域：' + internalGoogleDomain + '。');
      }

      if (seenGoogleEmails[googleEmail]) {
        issues.push('第 ' + rowIndex + ' 列學生Google帳號重複：' + googleEmail + '。');
      }
      seenGoogleEmails[googleEmail] = true;
    }

    if (parentEmail && !isValidEmail_(parentEmail)) {
      issues.push('第 ' + rowIndex + ' 列家長Email格式可能錯誤：' + parentEmail + '。');
    }
  }

  appendSystemLog_('INFO', 'validateStudentRoster', '已檢查學生名冊', {
    valid: issues.length === 0,
    issueCount: issues.length
  });
  showStudentRosterValidationResult_(issues);

  return { valid: issues.length === 0, issues: issues };
  } catch (error) {
    return handlePublicFunctionError_('validateStudentRoster', '檢查學生名冊時發生問題。請確認學生名冊欄位完整，且系統設定可讀取。', error);
  }
}

function showStudentRosterValidationResult_(issues) {
  const ui = SpreadsheetApp.getUi();

  if (issues.length === 0) {
    ui.alert('學生名冊檢查通過。');
    return;
  }

  ui.alert('學生名冊檢查發現 ' + issues.length + ' 項問題：\n\n' + issues.slice(0, 20).join('\n'));
}

function isValidEmail_(email) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(String(email || '').trim());
}


/**
 * Source: src/usecases/ClubManagement.gs
 */
function fillMissingClubIds() {
  try {
  ensureAllSheetSchemas_();

  const sheet = getOrCreateSheet_(ClubSheetNames.CLUBS);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('社團課程表沒有資料。');
    return { updatedCount: 0 };
  }

  const headerMap = getHeaderMap_(sheet);
  const clubIdColumn = requireColumn_(headerMap, '社團ID');
  const clubNameColumn = requireColumn_(headerMap, '社團名稱');
  const updatedAtColumn = requireColumn_(headerMap, '更新時間');
  const existingIds = sheet.getRange(2, clubIdColumn, lastRow - 1, 1).getValues().map(function(row) {
    return row[0];
  });
  let updatedCount = 0;

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const clubName = String(sheet.getRange(rowIndex, clubNameColumn).getValue() || '').trim();
    const currentId = String(sheet.getRange(rowIndex, clubIdColumn).getValue() || '').trim();

    if (clubName && !currentId) {
      const nextId = generateNextClubId_(existingIds);
      sheet.getRange(rowIndex, clubIdColumn).setValue(nextId);
      sheet.getRange(rowIndex, updatedAtColumn).setValue(new Date());
      existingIds.push(nextId);
      updatedCount += 1;
    }
  }

  appendSystemLog_('INFO', 'fillMissingClubIds', '已補齊社團ID', { updatedCount: updatedCount });
  SpreadsheetApp.getUi().alert('已補齊社團ID：' + updatedCount + ' 筆');

  return { updatedCount: updatedCount };
  } catch (error) {
    return handlePublicFunctionError_('fillMissingClubIds', '補齊社團ID時發生問題。請確認社團課程表欄位完整。', error);
  }
}


/**
 * Source: src/usecases/CreateRegistrationForm.gs
 */
function createRegistrationFormDraft() {
  try {
    ensureAllSheetSchemas_();
    checkBasicInfoBeforeRegistrationForm();

    const basicInfo = getBasicInfo_();
    const clubOptions = readClubsForRegistrationForm_();

    if (clubOptions.length === 0) {
      throw new Error('沒有可放入報名表的社團。請先建立社團、補齊社團ID，並確認開課狀態不是停開。');
    }

    const formResult = createRegistrationFormFromBasicInfo_(basicInfo, clubOptions);

    updateSystemSetting_(SystemSettingKeys.REGISTRATION_FORM_ID, formResult.id);
    updateSystemSetting_(SystemSettingKeys.REGISTRATION_FORM_URL, formResult.url);
    updateSystemSetting_(SystemSettingKeys.REGISTRATION_FORM_EDIT_URL, formResult.editUrl);
    updateSystemSetting_(SystemSettingKeys.FORM_ACCEPTING_RESPONSES, 'FALSE');
    installRegistrationFormSubmitTrigger_(formResult.id);

    appendSystemLog_('INFO', 'createRegistrationFormDraft', '已產生報名表草稿', formResult);

    SpreadsheetApp.getUi().alert(
      '報名表草稿已建立，預設未開放收件。\n\n編輯連結：\n' + formResult.editUrl
    );

    return formResult;
  } catch (error) {
    return handlePublicFunctionError_('createRegistrationFormDraft', '產生報名表草稿時發生問題。請確認基本資料與社團課程表已填妥。', error);
  }
}

function readClubsForRegistrationForm_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.CLUBS);
  const lastRow = sheet.getLastRow();
  const clubs = [];

  if (lastRow < 2) {
    return clubs;
  }

  const headerMap = getHeaderMap_(sheet);
  const clubIdColumn = requireColumn_(headerMap, '社團ID');
  const clubNameColumn = requireColumn_(headerMap, '社團名稱');
  const statusColumn = requireColumn_(headerMap, '開課狀態');

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const clubId = String(sheet.getRange(rowIndex, clubIdColumn).getValue() || '').trim();
    const clubName = String(sheet.getRange(rowIndex, clubNameColumn).getValue() || '').trim();
    const status = String(sheet.getRange(rowIndex, statusColumn).getValue() || '').trim();

    if (clubId && clubName && status !== '停開') {
      clubs.push({ clubId: clubId, clubName: clubName });
    }
  }

  return clubs;
}


/**
 * Source: src/usecases/RegistrationResponses.gs
 */
function syncRegistrationResponsesFromForm() {
  try {
    ensureAllSheetSchemas_();

    const settings = getSystemSettings_();
    const formId = String(settings[SystemSettingKeys.REGISTRATION_FORM_ID] || '').trim();

    if (!formId) {
      throw new Error('尚未建立報名表，找不到 registrationFormId。');
    }

    const form = FormApp.openById(formId);
    let createdCount = 0;

    form.getResponses().forEach(function(response) {
      if (writeRegistrationFormResponse_(response)) {
        createdCount += 1;
      }
    });

    appendSystemLog_('INFO', 'syncRegistrationResponsesFromForm', '已同步報名表回應', {
      createdCount: createdCount
    });
    SpreadsheetApp.getUi().alert('已同步報名表回應：' + createdCount + ' 筆');

    return { createdCount: createdCount };
  } catch (error) {
    return handlePublicFunctionError_('syncRegistrationResponsesFromForm', '同步報名表回應時發生問題。請確認報名表已建立且目前帳號有權限讀取表單。', error);
  }
}

function openRegistrationFormForResponses() {
  try {
    setRegistrationFormAcceptingResponses_(true);
    appendSystemLog_('INFO', 'openRegistrationFormForResponses', '已開放報名表收件', {});
    SpreadsheetApp.getUi().alert('報名表已開放收件。');
  } catch (error) {
    return handlePublicFunctionError_('openRegistrationFormForResponses', '開放報名表收件時發生問題。請確認報名表已建立且目前帳號有編輯權限。', error);
  }
}

function closeRegistrationFormForResponses() {
  try {
    setRegistrationFormAcceptingResponses_(false);
    appendSystemLog_('INFO', 'closeRegistrationFormForResponses', '已關閉報名表收件', {});
    SpreadsheetApp.getUi().alert('報名表已關閉收件。');
  } catch (error) {
    return handlePublicFunctionError_('closeRegistrationFormForResponses', '關閉報名表收件時發生問題。請確認報名表已建立且目前帳號有編輯權限。', error);
  }
}

function writeRegistrationFormResponse_(formResponse) {
  ensureAllSheetSchemas_();

  const responseId = getFormResponseId_(formResponse);
  if (responseId && hasRowWithColumnValue_(ClubSheetNames.REGISTRATIONS, '表單回應ID', responseId)) {
    return false;
  }

  const values = getFormResponseValues_(formResponse);
  const registrationId = generateRegistrationId_(formResponse);

  appendRowByHeaders_(ClubSheetNames.REGISTRATIONS, {
    '報名ID': registrationId,
    '表單回應ID': responseId,
    '報名Google帳號': getFormResponseRespondentEmail_(formResponse),
    '時間戳記': formResponse.getTimestamp(),
    '學號': values['學號'],
    '學生姓名': values['學生姓名'],
    '班級': values['班級'],
    '座號': values['座號'],
    '家長Email': values['家長Email'],
    '聯絡電話': values['聯絡電話'],
    '第一志願社團ID': parseClubChoiceValue_(values['第一志願社團']),
    '第二志願社團ID': parseClubChoiceValue_(values['第二志願社團']),
    '第三志願社團ID': parseClubChoiceValue_(values['第三志願社團']),
    '驗證狀態': '待檢查',
    '驗證訊息': '',
    '報名狀態': '待檢查'
  });

  appendSystemLog_('INFO', 'writeRegistrationFormResponse', '已寫入報名表回應', {
    registrationId: registrationId,
    responseId: responseId
  });

  return true;
}

function getFormResponseValues_(formResponse) {
  const values = {};

  formResponse.getItemResponses().forEach(function(itemResponse) {
    values[itemResponse.getItem().getTitle()] = itemResponse.getResponse();
  });

  return values;
}

function getFormResponseId_(formResponse) {
  if (typeof formResponse.getId === 'function') {
    return formResponse.getId();
  }

  return Utilities.base64EncodeWebSafe(
    String(formResponse.getTimestamp().getTime()) + '|' + JSON.stringify(getFormResponseValues_(formResponse))
  );
}

function generateRegistrationId_(formResponse) {
  return 'REG-' + Utilities.formatDate(formResponse.getTimestamp(), Session.getScriptTimeZone(), 'yyyyMMddHHmmssSSS');
}


/**
 * Source: src/usecases/RegistrationValidation.gs
 */
function validateRegistrations() {
  try {
  ensureAllSheetSchemas_();

  const registrationSheet = getOrCreateSheet_(ClubSheetNames.REGISTRATIONS);
  const lastRow = registrationSheet.getLastRow();

  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('社團報名資料沒有資料。');
    return { validCount: 0, invalidCount: 0 };
  }

  const registrationHeaderMap = getHeaderMap_(registrationSheet);
  const registrationColumns = getRegistrationColumns_(registrationHeaderMap);
  const studentIndex = buildStudentIndex_();
  const clubIndex = buildClubIndex_();
  const internalGoogleDomain = getInternalGoogleDomain_();
  const seenStudentIds = {};
  let validCount = 0;
  let invalidCount = 0;

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const studentId = String(registrationSheet.getRange(rowIndex, registrationColumns.studentId).getValue() || '').trim();
    const firstChoiceClubId = String(registrationSheet.getRange(rowIndex, registrationColumns.firstChoiceClubId).getValue() || '').trim();
    const secondChoiceClubId = String(registrationSheet.getRange(rowIndex, registrationColumns.secondChoiceClubId).getValue() || '').trim();
    const thirdChoiceClubId = String(registrationSheet.getRange(rowIndex, registrationColumns.thirdChoiceClubId).getValue() || '').trim();
    const parentEmail = String(registrationSheet.getRange(rowIndex, registrationColumns.parentEmail).getValue() || '').trim();
    const registrationGoogleEmail = normalizeEmail_(registrationSheet.getRange(rowIndex, registrationColumns.registrationGoogleEmail).getValue());
    const issues = [];
    const student = studentIndex[studentId];

    if (!studentId) {
      issues.push('缺少學號');
    } else if (!student) {
      issues.push('學生名冊查無此學號');
    } else if (seenStudentIds[studentId]) {
      issues.push('重複報名');
    }

    validateInternalGoogleEmail_(registrationGoogleEmail, internalGoogleDomain, issues, '報名');

    if (student && !student.googleEmail) {
      issues.push('學生名冊未填學生Google帳號');
    } else if (student && student.googleEmail !== registrationGoogleEmail) {
      issues.push('報名Google帳號與學生名冊不符');
    }

    if (!parentEmail) {
      issues.push('缺少家長Email');
    } else if (!isValidEmail_(parentEmail)) {
      issues.push('家長Email格式可能錯誤');
    }

    validateClubChoice_(firstChoiceClubId, '第一志願', clubIndex, issues, true);
    validateClubChoice_(secondChoiceClubId, '第二志願', clubIndex, issues, false);
    validateClubChoice_(thirdChoiceClubId, '第三志願', clubIndex, issues, false);
    validateDuplicateChoices_([firstChoiceClubId, secondChoiceClubId, thirdChoiceClubId], issues);
    validateChoiceScheduleConflict_([firstChoiceClubId, secondChoiceClubId, thirdChoiceClubId], clubIndex, issues);

    if (student) {
      registrationSheet.getRange(rowIndex, registrationColumns.studentName).setValue(student.name);
      registrationSheet.getRange(rowIndex, registrationColumns.maskedName).setValue(student.maskedName || maskStudentName_(student.name));
      registrationSheet.getRange(rowIndex, registrationColumns.className).setValue(student.className);
      registrationSheet.getRange(rowIndex, registrationColumns.seatNumber).setValue(student.seatNumber);
    }

    if (studentId) {
      seenStudentIds[studentId] = true;
    }

    if (issues.length === 0) {
      registrationSheet.getRange(rowIndex, registrationColumns.validationStatus).setValue('通過');
      registrationSheet.getRange(rowIndex, registrationColumns.validationMessage).setValue('');
      registrationSheet.getRange(rowIndex, registrationColumns.registrationStatus).setValue('有效');
      validCount += 1;
    } else {
      registrationSheet.getRange(rowIndex, registrationColumns.validationStatus).setValue('失敗');
      registrationSheet.getRange(rowIndex, registrationColumns.validationMessage).setValue(issues.join('；'));
      registrationSheet.getRange(rowIndex, registrationColumns.registrationStatus).setValue('無效');
      invalidCount += 1;
    }
  }

  appendSystemLog_('INFO', 'validateRegistrations', '已驗證社團報名資料', {
    validCount: validCount,
    invalidCount: invalidCount
  });
  SpreadsheetApp.getUi().alert('報名資料驗證完成。\n通過：' + validCount + ' 筆\n失敗：' + invalidCount + ' 筆');

  return { validCount: validCount, invalidCount: invalidCount };
  } catch (error) {
    return handlePublicFunctionError_('validateRegistrations', '驗證報名資料時發生問題。請確認學生名冊、社團課程表與報名資料欄位完整。', error);
  }
}

function getRegistrationColumns_(headerMap) {
  return {
    studentId: requireColumn_(headerMap, '學號'),
    registrationGoogleEmail: requireColumn_(headerMap, '報名Google帳號'),
    studentName: requireColumn_(headerMap, '學生姓名'),
    maskedName: requireColumn_(headerMap, '遮蔽姓名'),
    className: requireColumn_(headerMap, '班級'),
    seatNumber: requireColumn_(headerMap, '座號'),
    parentEmail: requireColumn_(headerMap, '家長Email'),
    parentPhone: requireColumn_(headerMap, '聯絡電話'),
    firstChoiceClubId: requireColumn_(headerMap, '第一志願社團ID'),
    secondChoiceClubId: requireColumn_(headerMap, '第二志願社團ID'),
    thirdChoiceClubId: requireColumn_(headerMap, '第三志願社團ID'),
    validationStatus: requireColumn_(headerMap, '驗證狀態'),
    validationMessage: requireColumn_(headerMap, '驗證訊息'),
    registrationStatus: requireColumn_(headerMap, '報名狀態')
  };
}

function buildStudentIndex_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.STUDENTS);
  const lastRow = sheet.getLastRow();
  const index = {};

  if (lastRow < 2) {
    return index;
  }

  const headerMap = getHeaderMap_(sheet);
  const columns = {
    studentId: requireColumn_(headerMap, '學號'),
    googleEmail: requireColumn_(headerMap, '學生Google帳號'),
    name: requireColumn_(headerMap, '姓名'),
    maskedName: requireColumn_(headerMap, '遮蔽姓名'),
    className: requireColumn_(headerMap, '班級'),
    seatNumber: requireColumn_(headerMap, '座號'),
    status: requireColumn_(headerMap, '狀態')
  };

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const studentId = String(sheet.getRange(rowIndex, columns.studentId).getValue() || '').trim();
    if (!studentId) continue;

    index[studentId] = {
      studentId: studentId,
      googleEmail: normalizeEmail_(sheet.getRange(rowIndex, columns.googleEmail).getValue()),
      name: String(sheet.getRange(rowIndex, columns.name).getValue() || '').trim(),
      maskedName: String(sheet.getRange(rowIndex, columns.maskedName).getValue() || '').trim(),
      className: String(sheet.getRange(rowIndex, columns.className).getValue() || '').trim(),
      seatNumber: sheet.getRange(rowIndex, columns.seatNumber).getValue(),
      status: String(sheet.getRange(rowIndex, columns.status).getValue() || '').trim()
    };
  }

  return index;
}

function getInternalGoogleDomain_() {
  const settings = getSystemSettings_();
  return normalizeGoogleDomain_(settings[SystemSettingKeys.INTERNAL_GOOGLE_DOMAIN]);
}

function buildClubIndex_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.CLUBS);
  const lastRow = sheet.getLastRow();
  const index = {};

  if (lastRow < 2) {
    return index;
  }

  const headerMap = getHeaderMap_(sheet);
  const columns = {
    clubId: requireColumn_(headerMap, '社團ID'),
    clubName: requireColumn_(headerMap, '社團名稱'),
    weekday: requireColumn_(headerMap, '星期'),
    period: requireColumn_(headerMap, '節次'),
    status: requireColumn_(headerMap, '開課狀態')
  };

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const clubId = String(sheet.getRange(rowIndex, columns.clubId).getValue() || '').trim();
    if (!clubId) continue;

    index[clubId] = {
      clubId: clubId,
      clubName: String(sheet.getRange(rowIndex, columns.clubName).getValue() || '').trim(),
      weekday: String(sheet.getRange(rowIndex, columns.weekday).getValue() || '').trim(),
      period: String(sheet.getRange(rowIndex, columns.period).getValue() || '').trim(),
      status: String(sheet.getRange(rowIndex, columns.status).getValue() || '').trim()
    };
  }

  return index;
}

function validateClubChoice_(clubId, label, clubIndex, issues, required) {
  if (!clubId) {
    if (required) {
      issues.push(label + '缺少社團ID');
    }
    return;
  }

  if (!clubIndex[clubId]) {
    issues.push(label + '查無社團ID：' + clubId);
  }
}

function validateDuplicateChoices_(clubIds, issues) {
  const seenClubIds = {};

  clubIds.filter(function(clubId) {
    return String(clubId || '').trim() !== '';
  }).forEach(function(clubId) {
    if (seenClubIds[clubId]) {
      issues.push('志願社團重複：' + clubId);
    }
    seenClubIds[clubId] = true;
  });
}

function validateChoiceScheduleConflict_(clubIds, clubIndex, issues) {
  const seenSchedules = {};

  clubIds.forEach(function(clubId) {
    const club = clubIndex[clubId];
    if (!club) return;

    const scheduleKey = club.weekday + '|' + club.period;
    if (scheduleKey === '|') return;

    if (seenSchedules[scheduleKey]) {
      issues.push('志願社團衝堂：' + seenSchedules[scheduleKey] + '、' + club.clubName);
    }
    seenSchedules[scheduleKey] = club.clubName;
  });
}


/**
 * Source: src/usecases/AssignmentDraft.gs
 */
function generateAssignmentDraft() {
  try {
  ensureAllSheetSchemas_();
  const mode = getAssignmentMode_();
  const batchId = generateAssignmentBatchId_();
  const registrations = readValidRegistrationsForAssignment_();
  const clubs = readClubsForAssignment_();

  if (registrations.length === 0) {
    SpreadsheetApp.getUi().alert('沒有可分發的有效報名資料。請先執行「驗證報名資料」。');
    return { batchId: batchId, mode: mode, admissions: 0, waitlist: 0 };
  }

  if (clubs.length === 0) {
    SpreadsheetApp.getUi().alert('沒有可分發的社團資料。請先建立社團並補齊社團ID。');
    return { batchId: batchId, mode: mode, admissions: 0, waitlist: 0 };
  }

  const result = runAssignment_(registrations, clubs, mode, batchId);
  clearRowsByColumnValue_(ClubSheetNames.ADMISSIONS, '錄取狀態', '試算');
  clearRowsByColumnValue_(ClubSheetNames.WAITLIST, '候補狀態', '試算');
  writeAssignmentDraft_(batchId, result);

  appendSystemLog_('INFO', 'generateAssignmentDraft', '已產生分發試算', {
    batchId: batchId,
    mode: mode,
    admissions: result.admissions.length,
    waitlist: result.waitlist.length
  });

  SpreadsheetApp.getUi().alert(
    '分發試算完成。\n批次：' + batchId + '\n模式：' + mode + '\n錄取：' + result.admissions.length + ' 筆\n候補：' + result.waitlist.length + ' 筆'
  );

  return {
    batchId: batchId,
    mode: mode,
    admissions: result.admissions.length,
    waitlist: result.waitlist.length
  };
  } catch (error) {
    return handlePublicFunctionError_('generateAssignmentDraft', '產生分發試算時發生問題。請先確認報名資料已通過驗證，且社團招收人數已填妥。', error);
  }
}

function readValidRegistrationsForAssignment_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.REGISTRATIONS);
  const lastRow = sheet.getLastRow();
  const registrations = [];

  if (lastRow < 2) {
    return registrations;
  }

  const headerMap = getHeaderMap_(sheet);
  const columns = getRegistrationColumns_(headerMap);
  const registrationIdColumn = requireColumn_(headerMap, '報名ID');
  const timestampColumn = requireColumn_(headerMap, '時間戳記');

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const validationStatus = String(sheet.getRange(rowIndex, columns.validationStatus).getValue() || '').trim();
    const registrationStatus = String(sheet.getRange(rowIndex, columns.registrationStatus).getValue() || '').trim();

    if (validationStatus !== '通過' || registrationStatus !== '有效') {
      continue;
    }

    registrations.push({
      registrationId: String(sheet.getRange(rowIndex, registrationIdColumn).getValue() || ('ROW-' + rowIndex)).trim(),
      timestamp: sheet.getRange(rowIndex, timestampColumn).getValue(),
      studentId: String(sheet.getRange(rowIndex, columns.studentId).getValue() || '').trim(),
      studentName: String(sheet.getRange(rowIndex, columns.studentName).getValue() || '').trim(),
      maskedName: String(sheet.getRange(rowIndex, columns.maskedName).getValue() || '').trim(),
      parentEmail: String(sheet.getRange(rowIndex, columns.parentEmail).getValue() || '').trim(),
      parentPhone: String(sheet.getRange(rowIndex, columns.parentPhone).getValue() || '').trim(),
      choices: [
        String(sheet.getRange(rowIndex, columns.firstChoiceClubId).getValue() || '').trim(),
        String(sheet.getRange(rowIndex, columns.secondChoiceClubId).getValue() || '').trim(),
        String(sheet.getRange(rowIndex, columns.thirdChoiceClubId).getValue() || '').trim()
      ]
    });
  }

  return registrations;
}

function readClubsForAssignment_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.CLUBS);
  const lastRow = sheet.getLastRow();
  const clubs = [];

  if (lastRow < 2) {
    return clubs;
  }

  const headerMap = getHeaderMap_(sheet);
  const clubIdColumn = requireColumn_(headerMap, '社團ID');
  const clubNameColumn = requireColumn_(headerMap, '社團名稱');
  const capacityColumn = requireColumn_(headerMap, '招收人數');
  const statusColumn = requireColumn_(headerMap, '開課狀態');

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const clubId = String(sheet.getRange(rowIndex, clubIdColumn).getValue() || '').trim();
    const clubName = String(sheet.getRange(rowIndex, clubNameColumn).getValue() || '').trim();
    const status = String(sheet.getRange(rowIndex, statusColumn).getValue() || '').trim();

    if (!clubId || !clubName || status === '停開') {
      continue;
    }

    clubs.push({
      clubId: clubId,
      clubName: clubName,
      capacity: Number(sheet.getRange(rowIndex, capacityColumn).getValue() || 0)
    });
  }

  return clubs;
}

function writeAssignmentDraft_(batchId, result) {
  const now = new Date();
  const admissionSheet = getOrCreateSheet_(ClubSheetNames.ADMISSIONS);
  const waitlistSheet = getOrCreateSheet_(ClubSheetNames.WAITLIST);

  if (result.admissions.length > 0) {
    const admissionRows = result.admissions.map(function(admission, index) {
      return [
        'ADM-' + batchId + '-' + String(index + 1).padStart(4, '0'),
        batchId,
        result.mode,
        admission.clubId,
        admission.clubName,
        admission.studentId,
        admission.studentName,
        admission.maskedName,
        admission.parentEmail,
        admission.parentPhone,
        admission.choiceRank,
        '試算',
        now
      ];
    });
    admissionSheet.getRange(admissionSheet.getLastRow() + 1, 1, admissionRows.length, admissionRows[0].length).setValues(admissionRows);
  }

  if (result.waitlist.length > 0) {
    const waitlistRows = result.waitlist.map(function(waitlist, index) {
      return [
        'WAI-' + batchId + '-' + String(index + 1).padStart(4, '0'),
        batchId,
        result.mode,
        waitlist.clubId,
        waitlist.clubName,
        waitlist.studentId,
        waitlist.studentName,
        waitlist.maskedName,
        waitlist.parentEmail,
        waitlist.parentPhone,
        waitlist.choiceRank,
        waitlist.waitlistOrder,
        '試算',
        now
      ];
    });
    waitlistSheet.getRange(waitlistSheet.getLastRow() + 1, 1, waitlistRows.length, waitlistRows[0].length).setValues(waitlistRows);
  }
}

function generateAssignmentBatchId_() {
  return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss');
}


/**
 * Source: src/usecases/AssignmentPublish.gs
 */
function publishAssignmentDraft() {
  try {
  ensureAllSheetSchemas_();

  const admissionCount = updateStatusByCurrentValue_(ClubSheetNames.ADMISSIONS, '錄取狀態', '試算', '正式');
  const waitlistCount = updateStatusByCurrentValue_(ClubSheetNames.WAITLIST, '候補狀態', '試算', '候補中');

  appendSystemLog_('INFO', 'publishAssignmentDraft', '已發布分發試算', {
    admissions: admissionCount,
    waitlist: waitlistCount
  });

  SpreadsheetApp.getUi().alert(
    '分發發布完成。\n正式錄取：' + admissionCount + ' 筆\n候補中：' + waitlistCount + ' 筆'
  );

  return {
    admissions: admissionCount,
    waitlist: waitlistCount
  };
  } catch (error) {
    return handlePublicFunctionError_('publishAssignmentDraft', '發布分發結果時發生問題。請先確認已產生分發試算。', error);
  }
}

function updateStatusByCurrentValue_(sheetName, statusHeader, fromStatus, toStatus) {
  const sheet = getOrCreateSheet_(sheetName);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return 0;
  }

  const headerMap = getHeaderMap_(sheet);
  const statusColumn = requireColumn_(headerMap, statusHeader);
  let updatedCount = 0;

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const status = String(sheet.getRange(rowIndex, statusColumn).getValue() || '').trim();
    if (status === fromStatus) {
      sheet.getRange(rowIndex, statusColumn).setValue(toStatus);
      updatedCount += 1;
    }
  }

  return updatedCount;
}


/**
 * Source: src/usecases/PaymentManagement.gs
 */
function generatePaymentsFromAdmissions() {
  try {
  ensureAllSheetSchemas_();

  const admissions = readOfficialAdmissionsForPayments_();
  const clubFees = readClubFees_();
  const existingPaymentKeys = readExistingPaymentKeys_();
  const paymentRows = [];
  const now = new Date();

  admissions.forEach(function(admission) {
    const key = admission.studentId + '|' + admission.clubId;
    if (existingPaymentKeys[key]) {
      return;
    }

    paymentRows.push([
      'PAY-' + Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyyMMddHHmmss') + '-' + String(paymentRows.length + 1).padStart(4, '0'),
      admission.studentId,
      admission.studentName,
      admission.clubId,
      admission.clubName,
      clubFees[admission.clubId] || 0,
      0,
      (clubFees[admission.clubId] || 0) > 0 ? '未繳' : '免繳',
      ''
    ]);
    existingPaymentKeys[key] = true;
  });

  if (paymentRows.length > 0) {
    const paymentSheet = getOrCreateSheet_(ClubSheetNames.PAYMENTS);
    paymentSheet.getRange(paymentSheet.getLastRow() + 1, 1, paymentRows.length, paymentRows[0].length).setValues(paymentRows);
  }

  appendSystemLog_('INFO', 'generatePaymentsFromAdmissions', '已依正式錄取產生繳費資料', {
    created: paymentRows.length
  });

  SpreadsheetApp.getUi().alert('已產生繳費資料：' + paymentRows.length + ' 筆');

  return { created: paymentRows.length };
  } catch (error) {
    return handlePublicFunctionError_('generatePaymentsFromAdmissions', '產生繳費資料時發生問題。請先確認分發結果已發布，且社團費用欄位可讀取。', error);
  }
}

function readOfficialAdmissionsForPayments_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.ADMISSIONS);
  const lastRow = sheet.getLastRow();
  const admissions = [];

  if (lastRow < 2) {
    return admissions;
  }

  const headerMap = getHeaderMap_(sheet);
  const columns = {
    clubId: requireColumn_(headerMap, '社團ID'),
    clubName: requireColumn_(headerMap, '社團名稱'),
    studentId: requireColumn_(headerMap, '學號'),
    studentName: requireColumn_(headerMap, '學生姓名'),
    status: requireColumn_(headerMap, '錄取狀態')
  };

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const status = String(sheet.getRange(rowIndex, columns.status).getValue() || '').trim();
    if (status !== '正式') {
      continue;
    }

    admissions.push({
      clubId: String(sheet.getRange(rowIndex, columns.clubId).getValue() || '').trim(),
      clubName: String(sheet.getRange(rowIndex, columns.clubName).getValue() || '').trim(),
      studentId: String(sheet.getRange(rowIndex, columns.studentId).getValue() || '').trim(),
      studentName: String(sheet.getRange(rowIndex, columns.studentName).getValue() || '').trim()
    });
  }

  return admissions;
}

function readClubFees_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.CLUBS);
  const lastRow = sheet.getLastRow();
  const fees = {};

  if (lastRow < 2) {
    return fees;
  }

  const headerMap = getHeaderMap_(sheet);
  const clubIdColumn = requireColumn_(headerMap, '社團ID');
  const feeColumn = requireColumn_(headerMap, '社團費用');

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const clubId = String(sheet.getRange(rowIndex, clubIdColumn).getValue() || '').trim();
    if (clubId) {
      fees[clubId] = Number(sheet.getRange(rowIndex, feeColumn).getValue() || 0);
    }
  }

  return fees;
}

function readExistingPaymentKeys_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.PAYMENTS);
  const lastRow = sheet.getLastRow();
  const keys = {};

  if (lastRow < 2) {
    return keys;
  }

  const headerMap = getHeaderMap_(sheet);
  const studentIdColumn = requireColumn_(headerMap, '學號');
  const clubIdColumn = requireColumn_(headerMap, '社團ID');

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const studentId = String(sheet.getRange(rowIndex, studentIdColumn).getValue() || '').trim();
    const clubId = String(sheet.getRange(rowIndex, clubIdColumn).getValue() || '').trim();
    if (studentId && clubId) {
      keys[studentId + '|' + clubId] = true;
    }
  }

  return keys;
}


/**
 * Source: src/usecases/AttendanceManagement.gs
 */
function createAttendanceFormDraft() {
  try {
    ensureAllSheetSchemas_();
    checkBasicInfoBeforeRegistrationForm();

    const basicInfo = getBasicInfo_();
    const clubOptions = readClubsForAttendanceForm_();

    if (clubOptions.length === 0) {
      throw new Error('沒有可放入簽到表的社團。請先建立社團、補齊社團ID，並確認開課狀態不是停開。');
    }

    const formResult = createAttendanceFormFromBasicInfo_(basicInfo, clubOptions);

    updateSystemSetting_(SystemSettingKeys.ATTENDANCE_FORM_ID, formResult.id);
    updateSystemSetting_(SystemSettingKeys.ATTENDANCE_FORM_URL, formResult.url);
    updateSystemSetting_(SystemSettingKeys.ATTENDANCE_FORM_EDIT_URL, formResult.editUrl);
    updateSystemSetting_(SystemSettingKeys.ATTENDANCE_FORM_ACCEPTING_RESPONSES, 'FALSE');
    installAttendanceFormSubmitTrigger_(formResult.id);

    appendSystemLog_('INFO', 'createAttendanceFormDraft', '已產生簽到表草稿', formResult);

    SpreadsheetApp.getUi().alert(
      '簽到表草稿已建立，預設未開放收件。\n\n編輯連結：\n' + formResult.editUrl
    );

    return formResult;
  } catch (error) {
    return handlePublicFunctionError_('createAttendanceFormDraft', '產生簽到表草稿時發生問題。請確認基本資料與社團課程表已填妥。', error);
  }
}

function openAttendanceFormForResponses() {
  try {
    setAttendanceFormAcceptingResponses_(true);
    appendSystemLog_('INFO', 'openAttendanceFormForResponses', '已開放簽到表收件', {});
    SpreadsheetApp.getUi().alert('簽到表已開放收件。');
  } catch (error) {
    return handlePublicFunctionError_('openAttendanceFormForResponses', '開放簽到表收件時發生問題。請確認簽到表已建立且目前帳號有編輯權限。', error);
  }
}

function closeAttendanceFormForResponses() {
  try {
    setAttendanceFormAcceptingResponses_(false);
    appendSystemLog_('INFO', 'closeAttendanceFormForResponses', '已關閉簽到表收件', {});
    SpreadsheetApp.getUi().alert('簽到表已關閉收件。');
  } catch (error) {
    return handlePublicFunctionError_('closeAttendanceFormForResponses', '關閉簽到表收件時發生問題。請確認簽到表已建立且目前帳號有編輯權限。', error);
  }
}

function syncAttendanceResponsesFromForm() {
  try {
  ensureAllSheetSchemas_();

  const settings = getSystemSettings_();
  const formId = String(settings[SystemSettingKeys.ATTENDANCE_FORM_ID] || '').trim();

  if (!formId) {
    throw new Error('尚未建立簽到表，找不到 attendanceFormId。');
  }

  const form = FormApp.openById(formId);
  let createdCount = 0;

  form.getResponses().forEach(function(response) {
    if (writeAttendanceFormResponse_(response)) {
      createdCount += 1;
    }
  });

  appendSystemLog_('INFO', 'syncAttendanceResponsesFromForm', '已同步簽到表回應', {
    createdCount: createdCount
  });
  SpreadsheetApp.getUi().alert('已同步簽到表回應：' + createdCount + ' 筆');

  return { createdCount: createdCount };
  } catch (error) {
    return handlePublicFunctionError_('syncAttendanceResponsesFromForm', '同步簽到表回應時發生問題。請確認簽到表已建立且目前帳號有權限讀取表單。', error);
  }
}

function writeAttendanceFormResponse_(formResponse) {
  ensureAllSheetSchemas_();

  const responseId = getFormResponseId_(formResponse);
  if (responseId && hasRowWithColumnValue_(ClubSheetNames.ATTENDANCE, '表單回應ID', responseId)) {
    return false;
  }

  const values = getFormResponseValues_(formResponse);
  const timestamp = formResponse.getTimestamp();
  const attendanceId = generateAttendanceId_(timestamp);
  const clubId = parseClubChoiceValue_(values['社團']);

  appendRowByHeaders_(ClubSheetNames.ATTENDANCE, {
    '出席ID': attendanceId,
    '表單回應ID': responseId,
    '時間戳記': timestamp,
    '社團ID': clubId,
    '學號': values['學號'],
    '簽到Google帳號': getFormResponseRespondentEmail_(formResponse),
    '日期': toAttendanceDate_(timestamp),
    '出席狀態': '出席',
    '點名老師': '',
    '驗證狀態': '待檢查',
    '驗證訊息': '',
    '備註': values['備註']
  });

  appendSystemLog_('INFO', 'writeAttendanceFormResponse', '已寫入簽到表回應', {
    attendanceId: attendanceId,
    responseId: responseId
  });

  return true;
}

function validateAttendanceRecords() {
  try {
  ensureAllSheetSchemas_();

  const sheet = getOrCreateSheet_(ClubSheetNames.ATTENDANCE);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('出席紀錄沒有資料。');
    return { validCount: 0, invalidCount: 0 };
  }

  const headerMap = getHeaderMap_(sheet);
  const columns = getAttendanceColumns_(headerMap);
  const studentIndex = buildStudentIndex_();
  const clubIndex = buildClubIndex_();
  const officialAdmissionIndex = buildOfficialAdmissionIndex_();
  const internalGoogleDomain = getInternalGoogleDomain_();
  let validCount = 0;
  let invalidCount = 0;

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const studentId = String(sheet.getRange(rowIndex, columns.studentId).getValue() || '').trim();
    const clubId = String(sheet.getRange(rowIndex, columns.clubId).getValue() || '').trim();
    const attendanceGoogleEmail = normalizeEmail_(sheet.getRange(rowIndex, columns.attendanceGoogleEmail).getValue());
    const timestamp = sheet.getRange(rowIndex, columns.timestamp).getValue();
    const status = String(sheet.getRange(rowIndex, columns.attendanceStatus).getValue() || '').trim();
    const issues = [];
    const student = studentIndex[studentId];
    const club = clubIndex[clubId];

    if (!studentId) {
      issues.push('缺少學號');
    } else if (!student) {
      issues.push('學生名冊查無此學號');
    }

    if (!clubId) {
      issues.push('缺少社團ID');
    } else if (!club) {
      issues.push('社團課程表查無此社團ID');
    }

    validateInternalGoogleEmail_(attendanceGoogleEmail, internalGoogleDomain, issues, '簽到');

    if (student && !student.googleEmail) {
      issues.push('學生名冊未填學生Google帳號');
    } else if (student && student.googleEmail !== attendanceGoogleEmail) {
      issues.push('簽到Google帳號與學生名冊不符');
    }

    if (student && club && !officialAdmissionIndex[studentId + '|' + clubId]) {
      issues.push('此學生未正式錄取該社團');
    }

    if (status && ['出席', '遲到', '請假', '缺席'].indexOf(status) === -1) {
      issues.push('出席狀態不正確：' + status);
    }

    if (student) {
      sheet.getRange(rowIndex, columns.studentName).setValue(student.name);
    }
    if (club) {
      sheet.getRange(rowIndex, columns.clubName).setValue(club.clubName);
    }
    if (!status) {
      sheet.getRange(rowIndex, columns.attendanceStatus).setValue('出席');
    }
    if (!sheet.getRange(rowIndex, columns.date).getValue() && timestamp) {
      sheet.getRange(rowIndex, columns.date).setValue(toAttendanceDate_(timestamp));
    }

    if (issues.length === 0) {
      sheet.getRange(rowIndex, columns.validationStatus).setValue('通過');
      sheet.getRange(rowIndex, columns.validationMessage).setValue('');
      validCount += 1;
    } else {
      sheet.getRange(rowIndex, columns.validationStatus).setValue('失敗');
      sheet.getRange(rowIndex, columns.validationMessage).setValue(issues.join('；'));
      invalidCount += 1;
    }
  }

  appendSystemLog_('INFO', 'validateAttendanceRecords', '已驗證出席紀錄', {
    validCount: validCount,
    invalidCount: invalidCount
  });
  SpreadsheetApp.getUi().alert('出席紀錄驗證完成。\n通過：' + validCount + ' 筆\n失敗：' + invalidCount + ' 筆');

  return { validCount: validCount, invalidCount: invalidCount };
  } catch (error) {
    return handlePublicFunctionError_('validateAttendanceRecords', '驗證出席紀錄時發生問題。請確認錄取名單、學生名冊、社團課程表與出席紀錄欄位完整。', error);
  }
}

function readClubsForAttendanceForm_() {
  return readClubsForRegistrationForm_();
}

function getAttendanceColumns_(headerMap) {
  return {
    attendanceId: requireColumn_(headerMap, '出席ID'),
    responseId: requireColumn_(headerMap, '表單回應ID'),
    timestamp: requireColumn_(headerMap, '時間戳記'),
    clubId: requireColumn_(headerMap, '社團ID'),
    clubName: requireColumn_(headerMap, '社團名稱'),
    studentId: requireColumn_(headerMap, '學號'),
    studentName: requireColumn_(headerMap, '學生姓名'),
    attendanceGoogleEmail: requireColumn_(headerMap, '簽到Google帳號'),
    date: requireColumn_(headerMap, '日期'),
    attendanceStatus: requireColumn_(headerMap, '出席狀態'),
    teacher: requireColumn_(headerMap, '點名老師'),
    validationStatus: requireColumn_(headerMap, '驗證狀態'),
    validationMessage: requireColumn_(headerMap, '驗證訊息'),
    note: requireColumn_(headerMap, '備註')
  };
}

function buildOfficialAdmissionIndex_() {
  const sheet = getOrCreateSheet_(ClubSheetNames.ADMISSIONS);
  const lastRow = sheet.getLastRow();
  const index = {};

  if (lastRow < 2) {
    return index;
  }

  const headerMap = getHeaderMap_(sheet);
  const columns = {
    clubId: requireColumn_(headerMap, '社團ID'),
    studentId: requireColumn_(headerMap, '學號'),
    status: requireColumn_(headerMap, '錄取狀態')
  };

  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
    const status = String(sheet.getRange(rowIndex, columns.status).getValue() || '').trim();
    if (status !== '正式') {
      continue;
    }

    const studentId = String(sheet.getRange(rowIndex, columns.studentId).getValue() || '').trim();
    const clubId = String(sheet.getRange(rowIndex, columns.clubId).getValue() || '').trim();
    if (studentId && clubId) {
      index[studentId + '|' + clubId] = true;
    }
  }

  return index;
}

function generateAttendanceId_(timestamp) {
  return 'ATT-' + Utilities.formatDate(timestamp || new Date(), Session.getScriptTimeZone(), 'yyyyMMddHHmmssSSS');
}

function toAttendanceDate_(value) {
  const date = value instanceof Date ? value : new Date(value || new Date());
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}


/**
 * Source: src/usecases/AnnouncementManagement.gs
 */
function generateAdmissionAnnouncement() {
  try {
    ensureAllSheetSchemas_();

    const basicInfo = getBasicInfo_();
    const settings = getSystemSettings_();
    const admissions = readOfficialAdmissionRows_();

    if (admissions.length === 0) {
      SpreadsheetApp.getUi().alert('目前沒有「正式」錄取資料。請先發布分發結果。');
      return { created: 0 };
    }

    const title = buildSemesterLabel_(basicInfo) + '社團錄取公告';
    const content = buildAdmissionAnnouncementContent_(basicInfo, settings, admissions, title);
    const announcementId = appendAnnouncementOutput_('錄取公告', title, content, '依 04_錄取名單產生');

    appendSystemLog_('INFO', 'generateAdmissionAnnouncement', '已產生錄取公告', {
      announcementId: announcementId,
      count: admissions.length
    });
    SpreadsheetApp.getUi().alert('錄取公告已產生，請查看「10_公告輸出」。');

    return { created: 1, announcementId: announcementId };
  } catch (error) {
    return handlePublicFunctionError_('generateAdmissionAnnouncement', '產生錄取公告時發生問題，公告尚未完成。', error);
  }
}

function generateWaitlistAnnouncement() {
  try {
    ensureAllSheetSchemas_();

    const basicInfo = getBasicInfo_();
    const waitlist = readActiveWaitlistRows_();

    if (waitlist.length === 0) {
      SpreadsheetApp.getUi().alert('目前沒有「候補中」資料。請先發布分發結果。');
      return { created: 0 };
    }

    const title = buildSemesterLabel_(basicInfo) + '社團候補公告';
    const content = buildWaitlistAnnouncementContent_(basicInfo, waitlist, title);
    const announcementId = appendAnnouncementOutput_('候補公告', title, content, '依 05_候補名單產生');

    appendSystemLog_('INFO', 'generateWaitlistAnnouncement', '已產生候補公告', {
      announcementId: announcementId,
      count: waitlist.length
    });
    SpreadsheetApp.getUi().alert('候補公告已產生，請查看「10_公告輸出」。');

    return { created: 1, announcementId: announcementId };
  } catch (error) {
    return handlePublicFunctionError_('generateWaitlistAnnouncement', '產生候補公告時發生問題，公告尚未完成。', error);
  }
}

function generateClassAdmissionList() {
  try {
    ensureAllSheetSchemas_();

    const basicInfo = getBasicInfo_();
    const admissions = readOfficialAdmissionRows_();

    if (admissions.length === 0) {
      SpreadsheetApp.getUi().alert('目前沒有「正式」錄取資料。請先發布分發結果。');
      return { created: 0 };
    }

    const studentIndex = buildStudentIndex_();
    const clubDetailIndex = buildClubDetailIndex_();
    const rows = admissions.map(function(admission) {
      const student = studentIndex[admission.studentId] || {};
      const club = clubDetailIndex[admission.clubId] || {};
      return {
        className: student.className || '',
        seatNumber: student.seatNumber || '',
        maskedName: admission.maskedName || maskStudentName_(admission.studentName),
        clubName: admission.clubName,
        classTime: club.classTime || '',
        location: club.location || '',
        fee: club.fee || 0
      };
    }).sort(function(a, b) {
      return String(a.className).localeCompare(String(b.className), 'zh-Hant') ||
        Number(a.seatNumber || 0) - Number(b.seatNumber || 0) ||
        String(a.clubName).localeCompare(String(b.clubName), 'zh-Hant');
    });

    const title = buildSemesterLabel_(basicInfo) + '班級版社團錄取名單';
    const content = buildClassAdmissionListContent_(rows);
    const announcementId = appendAnnouncementOutput_('班級版錄取名單', title, content, '依班級、座號排序');

    appendSystemLog_('INFO', 'generateClassAdmissionList', '已產生班級版錄取名單', {
      announcementId: announcementId,
      count: rows.length
    });
    SpreadsheetApp.getUi().alert('班級版錄取名單已產生，請查看「10_公告輸出」。');

    return { created: 1, announcementId: announcementId };
  } catch (error) {
    return handlePublicFunctionError_('generateClassAdmissionList', '產生班級版錄取名單時發生問題，名單尚未完成。', error);
  }
}

function buildAdmissionAnnouncementContent_(basicInfo, settings, admissions, title) {
  const publishMaskedOnly = String(settings[SystemSettingKeys.PUBLISH_MASKED_NAME_ONLY] || 'TRUE').toUpperCase() !== 'FALSE';
  const grouped = groupRowsByKey_(admissions, 'clubName');
  const lines = [
    String(basicInfo[BasicInfoKeys.SCHOOL_NAME] || '').trim(),
    buildSemesterLabel_(basicInfo),
    title,
    ''
  ];

  Object.keys(grouped).sort(function(a, b) {
    return a.localeCompare(b, 'zh-Hant');
  }).forEach(function(clubName) {
    lines.push('社團名稱：' + clubName);
    grouped[clubName].forEach(function(admission, index) {
      const displayName = publishMaskedOnly ? admission.maskedName : admission.studentName;
      lines.push(String(index + 1) + '. ' + (displayName || admission.maskedName || '未填姓名'));
    });
    lines.push('');
  });

  lines.push('繳費提醒：請依學校通知於指定期限內完成繳費，逾期未繳者可能由候補學生遞補。');
  lines.push('承辦單位：' + String(basicInfo[BasicInfoKeys.ORGANIZER_UNIT] || '').trim());
  lines.push('承辦人：' + String(basicInfo[BasicInfoKeys.ORGANIZER_NAME] || '').trim());
  lines.push('署名：' + String(basicInfo[BasicInfoKeys.ANNOUNCEMENT_SIGNATURE] || basicInfo[BasicInfoKeys.ORGANIZER_UNIT] || '').trim());

  return lines.join('\n');
}

function buildWaitlistAnnouncementContent_(basicInfo, waitlist, title) {
  const grouped = groupRowsByKey_(waitlist, 'clubName');
  const lines = [
    String(basicInfo[BasicInfoKeys.SCHOOL_NAME] || '').trim(),
    buildSemesterLabel_(basicInfo),
    title,
    ''
  ];

  Object.keys(grouped).sort(function(a, b) {
    return a.localeCompare(b, 'zh-Hant');
  }).forEach(function(clubName) {
    lines.push('社團名稱：' + clubName);
    grouped[clubName].sort(function(a, b) {
      return Number(a.waitlistOrder || 0) - Number(b.waitlistOrder || 0);
    }).forEach(function(row) {
      lines.push('候補序號 ' + row.waitlistOrder + '：' + row.maskedName);
    });
    lines.push('');
  });

  lines.push('候補遞補將依候補序號通知，請留意學校公告或承辦單位通知。');
  lines.push('承辦單位：' + String(basicInfo[BasicInfoKeys.ORGANIZER_UNIT] || '').trim());
  lines.push('署名：' + String(basicInfo[BasicInfoKeys.ANNOUNCEMENT_SIGNATURE] || basicInfo[BasicInfoKeys.ORGANIZER_UNIT] || '').trim());

  return lines.join('\n');
}

function buildClassAdmissionListContent_(rows) {
  const lines = ['班級\t座號\t遮蔽姓名\t社團名稱\t上課時間\t上課地點\t社團費用'];
  rows.forEach(function(row) {
    lines.push([
      row.className,
      row.seatNumber,
      row.maskedName,
      row.clubName,
      row.classTime,
      row.location,
      row.fee
    ].join('\t'));
  });
  return lines.join('\n');
}

function readOfficialAdmissionRows_() {
  return readSheetObjects_(ClubSheetNames.ADMISSIONS).filter(function(row) {
    return String(row['錄取狀態'] || '').trim() === '正式';
  }).map(function(row) {
    return {
      clubId: String(row['社團ID'] || '').trim(),
      clubName: String(row['社團名稱'] || '').trim(),
      studentId: String(row['學號'] || '').trim(),
      studentName: String(row['學生姓名'] || '').trim(),
      maskedName: String(row['遮蔽姓名'] || '').trim(),
      status: String(row['錄取狀態'] || '').trim()
    };
  });
}

function readActiveWaitlistRows_() {
  return readSheetObjects_(ClubSheetNames.WAITLIST).filter(function(row) {
    return String(row['候補狀態'] || '').trim() === '候補中';
  }).map(function(row) {
    return {
      clubId: String(row['社團ID'] || '').trim(),
      clubName: String(row['社團名稱'] || '').trim(),
      studentId: String(row['學號'] || '').trim(),
      maskedName: String(row['遮蔽姓名'] || '').trim(),
      waitlistOrder: Number(row['候補序號'] || 0),
      status: String(row['候補狀態'] || '').trim()
    };
  });
}

function buildClubDetailIndex_() {
  const index = {};
  readSheetObjects_(ClubSheetNames.CLUBS).forEach(function(row) {
    const clubId = String(row['社團ID'] || '').trim();
    if (!clubId) {
      return;
    }
    index[clubId] = {
      clubId: clubId,
      clubName: String(row['社團名稱'] || '').trim(),
      location: String(row['上課地點'] || '').trim(),
      weekday: String(row['星期'] || '').trim(),
      period: String(row['節次'] || '').trim(),
      classTime: String(row['上課時間'] || '').trim(),
      capacity: Number(row['招收人數'] || 0),
      fee: Number(row['社團費用'] || 0),
      outcomeFolderId: String(row['成果資料夾ID'] || '').trim()
    };
  });
  return index;
}

function appendAnnouncementOutput_(type, title, content, note) {
  const announcementId = 'ANN-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMddHHmmss');
  appendRowByHeaders_(ClubSheetNames.ANNOUNCEMENTS, {
    '公告ID': announcementId,
    '公告類型': type,
    '標題': title,
    '內容': content,
    '建立時間': new Date(),
    '建立者': getActiveUserEmail_(),
    '備註': note || ''
  });
  return announcementId;
}

function buildSemesterLabel_(basicInfo) {
  return [
    String(basicInfo[BasicInfoKeys.ACADEMIC_YEAR] || '').trim() ? String(basicInfo[BasicInfoKeys.ACADEMIC_YEAR]).trim() + '學年度' : '',
    String(basicInfo[BasicInfoKeys.SEMESTER] || '').trim()
  ].filter(function(part) {
    return part;
  }).join(' ');
}

function groupRowsByKey_(rows, key) {
  const grouped = {};
  rows.forEach(function(row) {
    const value = String(row[key] || '未命名').trim() || '未命名';
    if (!grouped[value]) {
      grouped[value] = [];
    }
    grouped[value].push(row);
  });
  return grouped;
}


/**
 * Source: src/usecases/DashboardManagement.gs
 */
function updateStatisticsDashboard() {
  try {
    ensureAllSheetSchemas_();

    const now = new Date();
    const students = readSheetObjects_(ClubSheetNames.STUDENTS);
    const clubs = readSheetObjects_(ClubSheetNames.CLUBS);
    const registrations = readSheetObjects_(ClubSheetNames.REGISTRATIONS);
    const admissions = readSheetObjects_(ClubSheetNames.ADMISSIONS);
    const waitlist = readSheetObjects_(ClubSheetNames.WAITLIST);
    const payments = readSheetObjects_(ClubSheetNames.PAYMENTS);
    const attendance = readSheetObjects_(ClubSheetNames.ATTENDANCE);
    const dashboardRows = [];

    const activeClubs = clubs.filter(function(row) {
      return String(row['社團名稱'] || '').trim() && String(row['開課狀態'] || '').trim() !== '停開';
    });
    const capacityByClub = {};
    const clubNameById = {};
    let totalCapacity = 0;

    activeClubs.forEach(function(row) {
      const clubId = String(row['社團ID'] || '').trim();
      const clubName = String(row['社團名稱'] || '').trim();
      const capacity = Number(row['招收人數'] || 0);
      if (clubId) {
        capacityByClub[clubId] = capacity;
        clubNameById[clubId] = clubName;
      }
      totalCapacity += capacity;
    });

    const validRegistrations = registrations.filter(function(row) {
      return String(row['報名狀態'] || '').trim() === '有效';
    });
    const invalidRegistrations = registrations.filter(function(row) {
      return String(row['報名狀態'] || '').trim() === '無效' || String(row['驗證狀態'] || '').trim() === '失敗';
    });
    const officialAdmissions = admissions.filter(function(row) {
      return String(row['錄取狀態'] || '').trim() === '正式';
    });
    const activeWaitlist = waitlist.filter(function(row) {
      return String(row['候補狀態'] || '').trim() === '候補中';
    });
    const admittedStudentIds = uniqueValues_(officialAdmissions.map(function(row) {
      return String(row['學號'] || '').trim();
    }));
    const validRegisteredStudentIds = uniqueValues_(validRegistrations.map(function(row) {
      return String(row['學號'] || '').trim();
    }));
    const unadmittedCount = Math.max(0, validRegisteredStudentIds.length - admittedStudentIds.length);

    pushDashboardRow_(dashboardRows, '學生名冊總人數', students.length, now);
    pushDashboardRow_(dashboardRows, '開課社團數', activeClubs.length, now);
    pushDashboardRow_(dashboardRows, '總招收名額', totalCapacity, now);
    pushDashboardRow_(dashboardRows, '報名總筆數', registrations.length, now);
    pushDashboardRow_(dashboardRows, '有效報名筆數', validRegistrations.length, now);
    pushDashboardRow_(dashboardRows, '無效報名筆數', invalidRegistrations.length, now);
    pushDashboardRow_(dashboardRows, '錄取總人數', officialAdmissions.length, now);
    pushDashboardRow_(dashboardRows, '候補總人數', activeWaitlist.length, now);
    pushDashboardRow_(dashboardRows, '未錄取人數', unadmittedCount, now);

    Object.keys(clubNameById).sort().forEach(function(clubId) {
      const admittedCount = countRowsByColumnValue_(officialAdmissions, '社團ID', clubId);
      const waitlistCount = countRowsByColumnValue_(activeWaitlist, '社團ID', clubId);
      pushDashboardRow_(dashboardRows, '各社團錄取人數 - ' + clubNameById[clubId], admittedCount, now);
      pushDashboardRow_(dashboardRows, '各社團候補人數 - ' + clubNameById[clubId], waitlistCount, now);
      pushDashboardRow_(dashboardRows, '各社團剩餘名額 - ' + clubNameById[clubId], Math.max(0, Number(capacityByClub[clubId] || 0) - admittedCount), now);
    });

    appendPaymentDashboardRows_(dashboardRows, payments, now);
    appendAttendanceDashboardRows_(dashboardRows, attendance, clubNameById, now);

    const writtenCount = replaceSheetRowsByHeaders_(ClubSheetNames.DASHBOARD, dashboardRows);

    appendSystemLog_('INFO', 'updateStatisticsDashboard', '已更新統計儀表板', {
      metrics: writtenCount
    });
    SpreadsheetApp.getUi().alert('統計儀表板已更新：' + writtenCount + ' 個指標。');

    return { updated: writtenCount };
  } catch (error) {
    return handlePublicFunctionError_('updateStatisticsDashboard', '更新統計儀表板時發生問題，統計資料尚未完成。', error);
  }
}

function appendPaymentDashboardRows_(dashboardRows, payments, now) {
  let totalDue = 0;
  let totalPaid = 0;
  let unpaidCount = 0;
  let partialPaidCount = 0;
  let completedPaidCount = 0;

  payments.forEach(function(row) {
    const due = Number(row['應繳金額'] || 0);
    const paid = Number(row['已繳金額'] || 0);
    const status = String(row['繳費狀態'] || '').trim();
    totalDue += due;
    totalPaid += paid;

    if (due > 0 && (status === '未繳' || paid <= 0)) {
      unpaidCount += 1;
    } else if (due > 0 && (status === '部分繳費' || (paid > 0 && paid < due))) {
      partialPaidCount += 1;
    } else if (status === '已繳' || status === '已完成' || (due > 0 && paid >= due) || due === 0) {
      completedPaidCount += 1;
    }
  });

  pushDashboardRow_(dashboardRows, '應繳總額', totalDue, now);
  pushDashboardRow_(dashboardRows, '已繳總額', totalPaid, now);
  pushDashboardRow_(dashboardRows, '未繳人數', unpaidCount, now);
  pushDashboardRow_(dashboardRows, '部分繳費人數', partialPaidCount, now);
  pushDashboardRow_(dashboardRows, '已完成繳費人數', completedPaidCount, now);
}

function appendAttendanceDashboardRows_(dashboardRows, attendance, clubNameById, now) {
  const presentCount = attendance.filter(function(row) {
    return String(row['出席狀態'] || '').trim() === '出席';
  }).length;
  const leaveCount = attendance.filter(function(row) {
    return String(row['出席狀態'] || '').trim() === '請假';
  }).length;
  const absentCount = attendance.filter(function(row) {
    return String(row['出席狀態'] || '').trim() === '缺席';
  }).length;

  pushDashboardRow_(dashboardRows, '總出席紀錄數', attendance.length, now);
  pushDashboardRow_(dashboardRows, '正常出席數', presentCount, now);
  pushDashboardRow_(dashboardRows, '請假數', leaveCount, now);
  pushDashboardRow_(dashboardRows, '缺席數', absentCount, now);

  Object.keys(clubNameById).sort().forEach(function(clubId) {
    const clubRows = attendance.filter(function(row) {
      return String(row['社團ID'] || '').trim() === clubId;
    });
    const attendedRows = clubRows.filter(function(row) {
      const status = String(row['出席狀態'] || '').trim();
      return status === '出席' || status === '遲到';
    });
    const rate = clubRows.length > 0 ? attendedRows.length / clubRows.length : 0;
    pushDashboardRow_(dashboardRows, '各社團出席率 - ' + clubNameById[clubId], Utilities.formatString('%.1f%%', rate * 100), now);
  });
}

function pushDashboardRow_(rows, metric, value, now) {
  rows.push({
    '指標': metric,
    '數值': value,
    '更新時間': now
  });
}

function uniqueValues_(values) {
  const seen = {};
  return values.filter(function(value) {
    if (!value || seen[value]) {
      return false;
    }
    seen[value] = true;
    return true;
  });
}

function countRowsByColumnValue_(rows, header, targetValue) {
  return rows.filter(function(row) {
    return String(row[header] || '').trim() === String(targetValue || '').trim();
  }).length;
}


/**
 * Source: src/usecases/PreLaunchChecklist.gs
 */
function runPreLaunchChecklist() {
  try {
    ensureAllSheetSchemas_();

    const now = new Date();
    const basicInfo = getBasicInfo_();
    const settings = getSystemSettings_();
    const students = readSheetObjects_(ClubSheetNames.STUDENTS);
    const clubs = readSheetObjects_(ClubSheetNames.CLUBS);
    const checklistRows = [];
    const basicInfoResult = validateBasicInfo_();
    const rosterIssues = collectStudentRosterIssues_();
    const clubRowsWithName = clubs.filter(function(row) {
      return String(row['社團名稱'] || '').trim();
    });
    const missingClubIdCount = clubRowsWithName.filter(function(row) {
      return !String(row['社團ID'] || '').trim();
    }).length;
    const missingCapacityCount = clubRowsWithName.filter(function(row) {
      return Number(row['招收人數'] || 0) <= 0;
    }).length;
    const registrationFormId = String(settings[SystemSettingKeys.REGISTRATION_FORM_ID] || '').trim();
    const formLinkResult = checkRegistrationFormDestination_(registrationFormId);

    pushChecklistRow_(checklistRows, '基本資料是否完整', basicInfoResult.valid, basicInfoResult.valid ? '00_基本資料已填寫必要欄位。' : '缺少：' + basicInfoResult.missingKeys.join(', '), '請補齊 00_基本資料的必要設定。', now);
    pushChecklistRow_(checklistRows, '報名開始與截止時間是否設定', hasValue_(settings[SystemSettingKeys.REGISTRATION_START_AT]) && hasValue_(settings[SystemSettingKeys.REGISTRATION_END_AT]), '目前開始時間：' + displayValue_(settings[SystemSettingKeys.REGISTRATION_START_AT]) + '；截止時間：' + displayValue_(settings[SystemSettingKeys.REGISTRATION_END_AT]), '請在 00_系統設定填寫 registrationStartAt 與 registrationEndAt。', now);
    pushChecklistRow_(checklistRows, '校內 Google 網域是否設定', hasValue_(settings[SystemSettingKeys.INTERNAL_GOOGLE_DOMAIN]), '目前設定：' + displayValue_(settings[SystemSettingKeys.INTERNAL_GOOGLE_DOMAIN]), '請填寫 internalGoogleDomain，例如 school.edu.tw。', now);
    pushChecklistRow_(checklistRows, '學生名冊是否有資料', students.length > 0, '目前學生名冊筆數：' + students.length, '請匯入或貼上學生名冊資料。', now);
    pushChecklistRow_(checklistRows, '學生名冊是否通過驗證', rosterIssues.length === 0, rosterIssues.length === 0 ? '學生名冊基本格式通過。' : '發現 ' + rosterIssues.length + ' 項問題。前 5 項：' + rosterIssues.slice(0, 5).join('；'), '請執行「檢查學生名冊」並依提示修正。', now);
    pushChecklistRow_(checklistRows, '社團課程表是否有資料', clubRowsWithName.length > 0, '目前社團課程表筆數：' + clubRowsWithName.length, '請建立社團課程資料。', now);
    pushChecklistRow_(checklistRows, '社團 ID 是否完整', missingClubIdCount === 0 && clubRowsWithName.length > 0, '缺少社團ID筆數：' + missingClubIdCount, '請執行「補齊社團ID」。', now);
    pushChecklistRow_(checklistRows, '社團是否有招收人數', missingCapacityCount === 0 && clubRowsWithName.length > 0, '招收人數未填或小於等於 0 筆數：' + missingCapacityCount, '請在 02_社團課程表填寫每個社團的招收人數。', now);
    pushChecklistRow_(checklistRows, '報名表是否已建立', Boolean(registrationFormId), registrationFormId ? 'registrationFormId 已設定。' : '尚未找到 registrationFormId。', '請執行「產生報名表草稿」。', now);
    pushChecklistRow_(checklistRows, '報名表是否已連結到目前試算表', formLinkResult.valid, formLinkResult.message, '請重新產生報名表草稿，或手動將表單回應目的地連結到目前試算表。', now);

    const writtenCount = replaceSheetRowsByHeaders_(ClubSheetNames.PRE_LAUNCH_CHECKLIST, checklistRows);
    const failedCount = checklistRows.filter(function(row) {
      return row['狀態'] !== '通過';
    }).length;

    appendSystemLog_('INFO', 'runPreLaunchChecklist', '已執行上線前檢查', {
      total: writtenCount,
      failed: failedCount
    });
    SpreadsheetApp.getUi().alert(
      failedCount === 0
        ? '上線前檢查完成，所有項目通過。'
        : '上線前檢查完成，仍有 ' + failedCount + ' 項需要處理。請查看「11_承辦檢查表」。'
    );

    return { checked: writtenCount, failed: failedCount };
  } catch (error) {
    return handlePublicFunctionError_('runPreLaunchChecklist', '執行上線前檢查時發生問題，檢查表尚未完成。', error);
  }
}

function pushChecklistRow_(rows, item, passed, description, suggestion, now) {
  rows.push({
    '檢查項目': item,
    '狀態': passed ? '通過' : '需處理',
    '說明': description,
    '建議處理方式': passed ? '無需處理。' : suggestion,
    '檢查時間': now
  });
}

function collectStudentRosterIssues_() {
  const students = readSheetObjects_(ClubSheetNames.STUDENTS);
  const internalGoogleDomain = getInternalGoogleDomain_();
  const issues = [];
  const seenStudentIds = {};
  const seenGoogleEmails = {};

  if (students.length === 0) {
    return ['學生名冊沒有資料'];
  }

  students.forEach(function(row, index) {
    const rowNumber = index + 2;
    const studentId = String(row['學號'] || '').trim();
    const googleEmail = normalizeEmail_(row['學生Google帳號']);
    const name = String(row['姓名'] || '').trim();
    const maskedName = String(row['遮蔽姓名'] || '').trim();
    const className = String(row['班級'] || '').trim();
    const seatNumber = String(row['座號'] || '').trim();
    const grade = String(row['年級'] || '').trim();

    if (!studentId) issues.push('第 ' + rowNumber + ' 列缺少學號');
    if (!googleEmail) issues.push('第 ' + rowNumber + ' 列缺少學生Google帳號');
    if (!name) issues.push('第 ' + rowNumber + ' 列缺少姓名');
    if (!maskedName) issues.push('第 ' + rowNumber + ' 列缺少遮蔽姓名');
    if (!className) issues.push('第 ' + rowNumber + ' 列缺少班級');
    if (!seatNumber) issues.push('第 ' + rowNumber + ' 列缺少座號');
    if (!grade) issues.push('第 ' + rowNumber + ' 列缺少年級');

    if (studentId) {
      if (seenStudentIds[studentId]) issues.push('第 ' + rowNumber + ' 列學號重複：' + studentId);
      seenStudentIds[studentId] = true;
    }

    if (googleEmail) {
      if (!isValidEmail_(googleEmail)) {
        issues.push('第 ' + rowNumber + ' 列學生Google帳號格式可能錯誤：' + googleEmail);
      } else if (internalGoogleDomain && !isEmailInDomain_(googleEmail, internalGoogleDomain)) {
        issues.push('第 ' + rowNumber + ' 列學生Google帳號不是校內網域：' + internalGoogleDomain);
      }

      if (seenGoogleEmails[googleEmail]) issues.push('第 ' + rowNumber + ' 列學生Google帳號重複：' + googleEmail);
      seenGoogleEmails[googleEmail] = true;
    }
  });

  return issues;
}

function checkRegistrationFormDestination_(formId) {
  if (!formId) {
    return { valid: false, message: '尚未建立報名表。' };
  }

  try {
    const form = FormApp.openById(formId);
    const spreadsheetId = getActiveSpreadsheet_().getId();
    const destinationId = typeof form.getDestinationId === 'function' ? form.getDestinationId() : '';

    if (!destinationId) {
      return { valid: false, message: '報名表尚未設定回應目的地。' };
    }

    return {
      valid: destinationId === spreadsheetId,
      message: destinationId === spreadsheetId ? '報名表回應目的地已連結到目前試算表。' : '報名表回應目的地不是目前試算表。'
    };
  } catch (error) {
    return { valid: false, message: '無法開啟報名表，請確認 registrationFormId 是否正確。' };
  }
}

function hasValue_(value) {
  return String(value || '').trim() !== '';
}

function displayValue_(value) {
  return hasValue_(value) ? String(value).trim() : '未設定';
}


/**
 * Source: src/usecases/OutcomeManagement.gs
 */
function createClubOutcomeFolders() {
  try {
    ensureAllSheetSchemas_();

    const settings = getSystemSettings_();
    const basicInfo = getBasicInfo_();
    const rootFolderId = String(settings[SystemSettingKeys.ROOT_FOLDER_ID] || '').trim();

    if (!rootFolderId) {
      SpreadsheetApp.getUi().alert('尚未設定 rootFolderId。請先在「00_系統設定」填寫 Drive 根資料夾 ID。');
      return { created: 0, updated: 0 };
    }

    const rootFolder = DriveApp.getFolderById(rootFolderId);
    const parentFolderName = buildOutcomeParentFolderName_(basicInfo);
    const parentFolder = getOrCreateChildFolder_(rootFolder, parentFolderName);
    const sheet = getOrCreateSheet_(ClubSheetNames.CLUBS);
    const lastRow = sheet.getLastRow();

    if (lastRow < 2) {
      SpreadsheetApp.getUi().alert('社團課程表沒有資料，尚未建立成果資料夾。');
      return { created: 0, updated: 0 };
    }

    const headerMap = getHeaderMap_(sheet);
    const columns = {
      clubId: requireColumn_(headerMap, '社團ID'),
      clubName: requireColumn_(headerMap, '社團名稱'),
      status: requireColumn_(headerMap, '開課狀態'),
      outcomeFolderId: requireColumn_(headerMap, '成果資料夾ID'),
      updatedAt: requireColumn_(headerMap, '更新時間')
    };
    let updatedCount = 0;

    for (let rowIndex = 2; rowIndex <= lastRow; rowIndex += 1) {
      const clubId = String(sheet.getRange(rowIndex, columns.clubId).getValue() || '').trim();
      const clubName = String(sheet.getRange(rowIndex, columns.clubName).getValue() || '').trim();
      const status = String(sheet.getRange(rowIndex, columns.status).getValue() || '').trim();

      if (!clubId || !clubName || status === '停開') {
        continue;
      }

      const folderName = clubId + '_' + clubName;
      const folder = getOrCreateChildFolder_(parentFolder, folderName);
      sheet.getRange(rowIndex, columns.outcomeFolderId).setValue(folder.getId());
      sheet.getRange(rowIndex, columns.updatedAt).setValue(new Date());
      updatedCount += 1;
    }

    appendSystemLog_('INFO', 'createClubOutcomeFolders', '已建立或確認社團成果資料夾', {
      parentFolderId: parentFolder.getId(),
      updated: updatedCount
    });
    SpreadsheetApp.getUi().alert('社團成果資料夾已建立或確認完成，已回寫資料夾 ID：' + updatedCount + ' 筆。');

    return { parentFolderId: parentFolder.getId(), updated: updatedCount };
  } catch (error) {
    return handlePublicFunctionError_('createClubOutcomeFolders', '建立社團成果資料夾時發生問題，請確認 rootFolderId 與 Drive 權限。', error);
  }
}

function updateOutcomeRegistry() {
  try {
    ensureAllSheetSchemas_();

    const clubDetails = buildClubDetailIndex_();
    const existingFileIds = readExistingOutcomeFileIds_();
    const rowsToAppend = [];
    const now = new Date();

    Object.keys(clubDetails).forEach(function(clubId) {
      const club = clubDetails[clubId];
      if (!club.outcomeFolderId) {
        return;
      }

      let folder;
      try {
        folder = DriveApp.getFolderById(club.outcomeFolderId);
      } catch (error) {
        appendSystemLog_('WARN', 'updateOutcomeRegistry', '無法開啟社團成果資料夾', {
          clubId: clubId,
          folderId: club.outcomeFolderId,
          message: error.message
        });
        return;
      }

      const files = folder.getFiles();
      while (files.hasNext()) {
        const file = files.next();
        const fileId = file.getId();
        if (existingFileIds[fileId]) {
          continue;
        }

        rowsToAppend.push({
          '成果ID': 'OUT-' + Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyyMMddHHmmss') + '-' + String(rowsToAppend.length + 1).padStart(4, '0'),
          '社團ID': clubId,
          '社團名稱': club.clubName,
          '類型': 'Drive檔案',
          '標題': file.getName(),
          'Drive檔案ID': fileId,
          'Drive連結': file.getUrl(),
          '是否公開': 'FALSE',
          '建立時間': now
        });
        existingFileIds[fileId] = true;
      }
    });

    rowsToAppend.forEach(function(row) {
      appendRowByHeaders_(ClubSheetNames.OUTCOMES, row);
    });

    appendSystemLog_('INFO', 'updateOutcomeRegistry', '已更新成果資料清單', {
      created: rowsToAppend.length
    });
    SpreadsheetApp.getUi().alert('成果資料清單已更新，新增：' + rowsToAppend.length + ' 筆。');

    return { created: rowsToAppend.length };
  } catch (error) {
    return handlePublicFunctionError_('updateOutcomeRegistry', '更新成果資料清單時發生問題，請確認成果資料夾 ID 與 Drive 權限。', error);
  }
}

function buildOutcomeParentFolderName_(basicInfo) {
  const academicYear = String(basicInfo[BasicInfoKeys.ACADEMIC_YEAR] || '').trim() || '未設定學年度';
  const semester = String(basicInfo[BasicInfoKeys.SEMESTER] || '').trim() || '未設定學期';
  return academicYear + '_' + semester + '_社團成果';
}

function getOrCreateChildFolder_(parentFolder, folderName) {
  const iterator = parentFolder.getFoldersByName(folderName);
  if (iterator.hasNext()) {
    return iterator.next();
  }
  return parentFolder.createFolder(folderName);
}

function readExistingOutcomeFileIds_() {
  const existing = {};
  readSheetObjects_(ClubSheetNames.OUTCOMES).forEach(function(row) {
    const fileId = String(row['Drive檔案ID'] || '').trim();
    if (fileId) {
      existing[fileId] = true;
    }
  });
  return existing;
}


/**
 * Source: src/usecases/DemoData.gs
 */
function seedDemoData() {
  try {
    ensureAllSheetSchemas_();
    clearDemoData_(false);
    seedDemoBasicSettingsIfBlank_();

    const students = buildDemoStudents_();
    const clubs = buildDemoClubs_();
    const registrations = buildDemoRegistrations_(students);

    students.forEach(function(row) {
      appendRowByHeaders_(ClubSheetNames.STUDENTS, row);
    });
    clubs.forEach(function(row) {
      appendRowByHeaders_(ClubSheetNames.CLUBS, row);
    });
    registrations.forEach(function(row) {
      appendRowByHeaders_(ClubSheetNames.REGISTRATIONS, row);
    });

    appendSystemLog_('INFO', 'seedDemoData', '已建立測試資料', {
      students: students.length,
      clubs: clubs.length,
      registrations: registrations.length
    });
    SpreadsheetApp.getUi().alert('測試資料已建立：30 位學生、6 個社團、' + registrations.length + ' 筆報名資料。');

    return {
      students: students.length,
      clubs: clubs.length,
      registrations: registrations.length
    };
  } catch (error) {
    return handlePublicFunctionError_('seedDemoData', '建立測試資料時發生問題，測試資料尚未完成。', error);
  }
}

function clearDemoData() {
  try {
    ensureAllSheetSchemas_();
    const result = clearDemoData_(true);
    return result;
  } catch (error) {
    return handlePublicFunctionError_('clearDemoData', '清除測試資料時發生問題，請查看系統日誌。', error);
  }
}

function clearDemoData_(showAlert) {
  const result = {
    students: deleteRowsWhere_(ClubSheetNames.STUDENTS, function(row) {
      return isDemoStudentId_(row['學號']);
    }),
    clubs: deleteRowsWhere_(ClubSheetNames.CLUBS, function(row) {
      return isDemoClubId_(row['社團ID']);
    }),
    registrations: deleteRowsWhere_(ClubSheetNames.REGISTRATIONS, function(row) {
      return isDemoRegistrationId_(row['報名ID']) || isDemoStudentId_(row['學號']);
    }),
    admissions: deleteRowsWhere_(ClubSheetNames.ADMISSIONS, function(row) {
      return isDemoStudentId_(row['學號']) || isDemoClubId_(row['社團ID']);
    }),
    waitlist: deleteRowsWhere_(ClubSheetNames.WAITLIST, function(row) {
      return isDemoStudentId_(row['學號']) || isDemoClubId_(row['社團ID']);
    }),
    payments: deleteRowsWhere_(ClubSheetNames.PAYMENTS, function(row) {
      return isDemoStudentId_(row['學號']) || isDemoClubId_(row['社團ID']);
    }),
    attendance: deleteRowsWhere_(ClubSheetNames.ATTENDANCE, function(row) {
      return isDemoStudentId_(row['學號']) || isDemoClubId_(row['社團ID']);
    }),
    outcomes: deleteRowsWhere_(ClubSheetNames.OUTCOMES, function(row) {
      return isDemoClubId_(row['社團ID']);
    })
  };

  appendSystemLog_('INFO', 'clearDemoData', '已清除測試資料', result);

  if (showAlert) {
    SpreadsheetApp.getUi().alert('測試資料已清除。系統設定、基本資料與工作表欄位保留不變。');
  }

  return result;
}

function seedDemoBasicSettingsIfBlank_() {
  const basicInfo = getBasicInfo_();
  const settings = getSystemSettings_();

  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.SCHOOL_NAME, basicInfo[BasicInfoKeys.SCHOOL_NAME], '水月國小');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.ACADEMIC_YEAR, basicInfo[BasicInfoKeys.ACADEMIC_YEAR], '115');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.SEMESTER, basicInfo[BasicInfoKeys.SEMESTER], '第一學期');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.ORGANIZER_UNIT, basicInfo[BasicInfoKeys.ORGANIZER_UNIT], '學務處');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.ORGANIZER_NAME, basicInfo[BasicInfoKeys.ORGANIZER_NAME], '測試承辦人');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.ORGANIZER_EMAIL, basicInfo[BasicInfoKeys.ORGANIZER_EMAIL], 'club-admin@demo.school.edu.tw');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.REGISTRATION_FORM_TITLE, basicInfo[BasicInfoKeys.REGISTRATION_FORM_TITLE], '水月國小社團報名測試表');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.REGISTRATION_FORM_DESCRIPTION, basicInfo[BasicInfoKeys.REGISTRATION_FORM_DESCRIPTION], '此為社團報名平台示範測試資料。');
  writeKeyValueIfBlank_(ClubSheetNames.BASIC_INFO, BasicInfoKeys.ANNOUNCEMENT_SIGNATURE, basicInfo[BasicInfoKeys.ANNOUNCEMENT_SIGNATURE], '水月國小學務處');
  writeKeyValueIfBlank_(ClubSheetNames.SYSTEM_SETTINGS, SystemSettingKeys.INTERNAL_GOOGLE_DOMAIN, settings[SystemSettingKeys.INTERNAL_GOOGLE_DOMAIN], 'demo.school.edu.tw');
  writeKeyValueIfBlank_(ClubSheetNames.SYSTEM_SETTINGS, SystemSettingKeys.REGISTRATION_START_AT, settings[SystemSettingKeys.REGISTRATION_START_AT], '2026-08-01 08:00');
  writeKeyValueIfBlank_(ClubSheetNames.SYSTEM_SETTINGS, SystemSettingKeys.REGISTRATION_END_AT, settings[SystemSettingKeys.REGISTRATION_END_AT], '2026-08-07 17:00');
  writeKeyValueIfBlank_(ClubSheetNames.SYSTEM_SETTINGS, SystemSettingKeys.PUBLISH_MASKED_NAME_ONLY, settings[SystemSettingKeys.PUBLISH_MASKED_NAME_ONLY], 'TRUE');
}

function writeKeyValueIfBlank_(sheetName, key, currentValue, defaultValue) {
  if (String(currentValue || '').trim() === '') {
    writeKeyValue_(sheetName, key, defaultValue);
  }
}

function buildDemoStudents_() {
  const familyNames = ['王', '陳', '林', '黃', '張', '李', '吳', '劉', '蔡', '楊'];
  const givenNames = ['小明', '美玲', '柏翰', '品妤', '家豪', '雅婷', '承恩', '子晴', '宇翔', '怡安', '冠廷', '庭瑄', '宥辰', '欣妤', '哲宇', '語彤', '俊傑', '宜庭', '昱翔', '佳穎', '彥廷', '詩涵', '皓宇', '采潔', '冠宇', '心怡', '秉宏', '郁婷', '睿哲', '若晴'];

  return givenNames.map(function(givenName, index) {
    const number = index + 1;
    const studentId = 'DEMO-S' + String(number).padStart(3, '0');
    const name = familyNames[index % familyNames.length] + givenName;
    const grade = 3 + (index % 3);
    const classNumber = 1 + (index % 3);
    const className = grade + '年' + classNumber + '班';
    const googleEmail = number === 29 ? 'demo29@gmail.com' : 'demo' + String(number).padStart(2, '0') + '@demo.school.edu.tw';

    return {
      '學號': studentId,
      '學生Google帳號': googleEmail,
      '姓名': name,
      '遮蔽姓名': maskStudentName_(name),
      '班級': className,
      '座號': number,
      '年級': grade,
      '家長Email': 'parent' + String(number).padStart(2, '0') + '@example.com',
      '聯絡電話': '09' + String(10000000 + number).slice(-8),
      '狀態': '在學',
      '更新時間': new Date()
    };
  });
}

function buildDemoClubs_() {
  const now = new Date();
  return [
    ['DEMO-CLUB-001', '桌球社', '王老師', '活動中心', '三', '5-6', '週三 13:30-15:00', 5, 1200, '3-6', '開課'],
    ['DEMO-CLUB-002', '美術社', '陳老師', '美術教室', '三', '5-6', '週三 13:30-15:00', 4, 1500, '3-6', '開課'],
    ['DEMO-CLUB-003', '足球社', '林老師', '操場', '二', '5-6', '週二 13:30-15:00', 4, 800, '3-6', '開課'],
    ['DEMO-CLUB-004', '程式設計社', '黃老師', '電腦教室', '四', '5-6', '週四 13:30-15:00', 3, 1800, '4-6', '開課'],
    ['DEMO-CLUB-005', '直笛社', '張老師', '音樂教室', '五', '5-6', '週五 13:30-15:00', 3, 900, '3-6', '開課'],
    ['DEMO-CLUB-006', '科學實驗社', '李老師', '自然教室', '一', '5-6', '週一 13:30-15:00', 2, 1600, '4-6', '開課']
  ].map(function(values) {
    return {
      '社團ID': values[0],
      '社團名稱': values[1],
      '指導老師': values[2],
      '上課地點': values[3],
      '星期': values[4],
      '節次': values[5],
      '上課時間': values[6],
      '招收人數': values[7],
      '社團費用': values[8],
      '招收年級': values[9],
      '開課狀態': values[10],
      '成果資料夾ID': '',
      '更新時間': now
    };
  });
}

function buildDemoRegistrations_(students) {
  const rows = [];
  const baseDate = new Date(2026, 7, 1, 8, 0, 0);

  students.forEach(function(student, index) {
    const number = index + 1;
    const timestamp = new Date(baseDate.getTime() + index * 60000);
    const choices = getDemoChoices_(number);
    const googleEmail = (number === 6 || number === 7) ? 'wrong' + number + '@gmail.com' : student['學生Google帳號'];

    rows.push(buildDemoRegistrationRow_('DEMO-REG-' + String(number).padStart(3, '0'), timestamp, student, googleEmail, choices));
  });

  rows.push(buildDemoRegistrationRow_('DEMO-REG-DUP-001', new Date(baseDate.getTime() + 31 * 60000), students[0], students[0]['學生Google帳號'], ['DEMO-CLUB-003', 'DEMO-CLUB-004', 'DEMO-CLUB-005']));
  rows.push(buildDemoRegistrationRow_('DEMO-REG-DUP-002', new Date(baseDate.getTime() + 32 * 60000), students[1], students[1]['學生Google帳號'], ['DEMO-CLUB-004', 'DEMO-CLUB-005', 'DEMO-CLUB-006']));

  return rows;
}

function buildDemoRegistrationRow_(registrationId, timestamp, student, googleEmail, choices) {
  return {
    '報名ID': registrationId,
    '表單回應ID': registrationId + '-RESPONSE',
    '報名Google帳號': googleEmail,
    '時間戳記': timestamp,
    '學號': student['學號'],
    '學生姓名': student['姓名'],
    '遮蔽姓名': student['遮蔽姓名'],
    '班級': student['班級'],
    '座號': student['座號'],
    '家長Email': student['家長Email'],
    '聯絡電話': student['聯絡電話'],
    '第一志願社團ID': choices[0],
    '第二志願社團ID': choices[1],
    '第三志願社團ID': choices[2],
    '驗證狀態': '待檢查',
    '驗證訊息': '',
    '報名狀態': '待檢查'
  };
}

function getDemoChoices_(number) {
  if (number >= 8 && number <= 10) {
    return ['DEMO-CLUB-001', 'DEMO-CLUB-002', 'DEMO-CLUB-003'];
  }

  const patterns = [
    ['DEMO-CLUB-001', 'DEMO-CLUB-003', 'DEMO-CLUB-005'],
    ['DEMO-CLUB-001', 'DEMO-CLUB-004', 'DEMO-CLUB-006'],
    ['DEMO-CLUB-002', 'DEMO-CLUB-004', 'DEMO-CLUB-005'],
    ['DEMO-CLUB-003', 'DEMO-CLUB-005', 'DEMO-CLUB-006'],
    ['DEMO-CLUB-004', 'DEMO-CLUB-003', 'DEMO-CLUB-005'],
    ['DEMO-CLUB-005', 'DEMO-CLUB-003', 'DEMO-CLUB-006'],
    ['DEMO-CLUB-006', 'DEMO-CLUB-003', 'DEMO-CLUB-005']
  ];

  return patterns[number % patterns.length];
}

function isDemoStudentId_(studentId) {
  return String(studentId || '').indexOf('DEMO-S') === 0;
}

function isDemoClubId_(clubId) {
  return String(clubId || '').indexOf('DEMO-CLUB-') === 0;
}

function isDemoRegistrationId_(registrationId) {
  return String(registrationId || '').indexOf('DEMO-REG') === 0;
}


/**
 * Source: src/triggers/RegistrationFormSubmit.gs
 */
function handleRegistrationFormSubmit(e) {
  if (!e || !e.response) {
    throw new Error('缺少 Google Form submit event response。');
  }

  writeRegistrationFormResponse_(e.response);
}


/**
 * Source: src/triggers/AttendanceFormSubmit.gs
 */
function handleAttendanceFormSubmit(e) {
  if (!e || !e.response) {
    throw new Error('缺少 Google Form submit event response。');
  }

  writeAttendanceFormResponse_(e.response);
}


/**
 * Source: src/Menu.gs
 */
function onOpen() {
  buildClubManagementMenu_();
}

function buildClubManagementMenu_() {
  SpreadsheetApp.getUi()
    .createMenu('社團管理平台')
    .addItem('初始化新學期', 'initializeSemester')
    .addSeparator()
    .addItem('檢查基本資料', 'showBasicInfoCheck')
    .addItem('查看分發模式', 'showAssignmentMode')
    .addItem('執行上線前檢查', 'runPreLaunchChecklist')
    .addSeparator()
    .addItem('更新學生遮蔽姓名', 'refreshStudentMaskedNames')
    .addItem('檢查學生名冊', 'validateStudentRoster')
    .addItem('補齊社團ID', 'fillMissingClubIds')
    .addSeparator()
    .addItem('產生報名表草稿', 'createRegistrationFormDraft')
    .addItem('開放報名表收件', 'openRegistrationFormForResponses')
    .addItem('關閉報名表收件', 'closeRegistrationFormForResponses')
    .addItem('同步報名表回應', 'syncRegistrationResponsesFromForm')
    .addItem('驗證報名資料', 'validateRegistrations')
    .addItem('產生分發試算', 'generateAssignmentDraft')
    .addItem('發布分發結果', 'publishAssignmentDraft')
    .addItem('產生繳費資料', 'generatePaymentsFromAdmissions')
    .addItem('產生錄取公告', 'generateAdmissionAnnouncement')
    .addItem('產生候補公告', 'generateWaitlistAnnouncement')
    .addItem('產生班級版錄取名單', 'generateClassAdmissionList')
    .addItem('更新統計儀表板', 'updateStatisticsDashboard')
    .addSeparator()
    .addItem('產生簽到表草稿', 'createAttendanceFormDraft')
    .addItem('開放簽到表收件', 'openAttendanceFormForResponses')
    .addItem('關閉簽到表收件', 'closeAttendanceFormForResponses')
    .addItem('同步簽到表回應', 'syncAttendanceResponsesFromForm')
    .addItem('驗證簽到紀錄', 'validateAttendanceRecords')
    .addSeparator()
    .addItem('建立社團成果資料夾', 'createClubOutcomeFolders')
    .addItem('更新成果資料清單', 'updateOutcomeRegistry')
    .addSeparator()
    .addItem('建立 Demo 測試資料', 'seedDemoData')
    .addItem('清除 Demo 測試資料', 'clearDemoData')
    .addToUi();
}

function showBasicInfoCheck() {
  const result = validateBasicInfo_();
  const ui = SpreadsheetApp.getUi();

  if (result.valid) {
    ui.alert('00_基本資料已填寫完整。');
    return;
  }

  ui.alert('00_基本資料尚未填完整，缺少：' + result.missingKeys.join(', '));
}

function showAssignmentMode() {
  const mode = getAssignmentMode_();
  SpreadsheetApp.getUi().alert('目前分發模式：' + mode);
}


/**
 * Source: src/Main.gs
 */
function setupClubManagementPlatform() {
  initializeSemester();
}