第四堂課｜運動會平台展示 Apps Script 一鍵複製

使用方式

先開啟第四堂課新版講義及完整操作步驟，確認操作主線。
在下方按「複製完整 Apps Script」。
到 Google Sheet 的「擴充功能 → Apps Script」，貼入 Code.gs 並儲存。
回到 Google Sheet 重新整理，從「運動會專案中控台」開始操作。
課堂定位

這份程式碼是第四堂課的運動會平台展示版，聚焦大型活動的 WBS 任務、RACI 權責、任務回報、問題追蹤、儀表板、AI 知識庫索引與結案報告草稿。
講義負責說明操作順序；本頁負責提供可完整複製的 Apps Script 程式碼，兩者應搭配使用。
完整 Apps Script 程式碼

// ===== constants.gs =====
var APP_VERSION = 'SPORTS_DAY_DEMO_PLATFORM';

const PROJECT_FILE_CATEGORIES = [
  '專案建案資料',
  '會議紀錄與決議',
  'WBS任務附件',
  '通知文件',
  '場地與動線資料',
  '活動照片',
  '成果報告',
  'AI知識庫素材',
  '其他'
];

var PROJECT_FILE_CATEGORY_CODES = [
  ['PROJECT', '專案建案資料'],
  ['MEETING', '會議紀錄與決議'],
  ['TASK', 'WBS任務附件'],
  ['NOTICE', '通知文件'],
  ['SITE', '場地與動線資料'],
  ['PHOTO', '活動照片'],
  ['RESULT', '成果報告'],
  ['AI', 'AI知識庫素材'],
  ['OTHER', '其他']
];

var SHEETS = {
  PROJECT_INFO: '專案資訊',
  SYSTEM_CODE_TABLE: '系統代碼表',
  PROJECT_CASES: '活動建案表',
  MASTER_WBS: 'WBS任務主表',
  RACI_MATRIX: 'RACI權責矩陣',
  MILESTONES: '里程碑',
  RISK_REGISTER: '風險登錄',
  ISSUE_TRACKER: '問題追蹤',
  DECISION_LOG: '決議紀錄',
  COMMUNICATION_PLAN: '溝通計畫',
  TASK_REPORTS: '任務回報',
  OUTCOME_SUBMISSIONS: '成果資料',
  AI_KNOWLEDGE_INDEX: 'AI知識庫索引',
  PROJECT_FOLDERS: '專案資料夾索引',
  NOTIFICATION_QUEUE: '通知佇列',
  TASK_REPORT_FORM_CONFIG: '任務回報表單設定',
  EMAIL_PREVIEW: 'Email預覽',
  DASHBOARD: '儀表板',
  AUDIT_LOG: '稽核紀錄',
  GENERATED_DOCS_PREVIEW: '文件預覽'
};

var SHEET_ORDER = [
  SHEETS.PROJECT_INFO,
  SHEETS.SYSTEM_CODE_TABLE,
  SHEETS.PROJECT_CASES,
  SHEETS.MASTER_WBS,
  SHEETS.RACI_MATRIX,
  SHEETS.MILESTONES,
  SHEETS.RISK_REGISTER,
  SHEETS.ISSUE_TRACKER,
  SHEETS.DECISION_LOG,
  SHEETS.COMMUNICATION_PLAN,
  SHEETS.TASK_REPORTS,
  SHEETS.OUTCOME_SUBMISSIONS,
  SHEETS.AI_KNOWLEDGE_INDEX,
  SHEETS.PROJECT_FOLDERS,
  SHEETS.NOTIFICATION_QUEUE,
  SHEETS.TASK_REPORT_FORM_CONFIG,
  SHEETS.EMAIL_PREVIEW,
  SHEETS.DASHBOARD,
  SHEETS.AUDIT_LOG,
  SHEETS.GENERATED_DOCS_PREVIEW
];

var HEADERS = {};

HEADERS[SHEETS.PROJECT_INFO] = [
  '設定鍵',
  '設定值',
  '說明'
];

HEADERS[SHEETS.SYSTEM_CODE_TABLE] = [
  '代碼',
  '顯示名稱'
];

HEADERS[SHEETS.PROJECT_CASES] = [
  '專案ID',
  '專案名稱',
  '活動類型',
  '主辦單位',
  '負責人',
  '開始日期',
  '結束日期',
  '狀態',
  '專案資料夾連結',
  '任務回報表單連結',
  '報名表單連結',
  '成果表單連結',
  '備註'
];

HEADERS[SHEETS.MASTER_WBS] = [
  '任務ID',
  'WBS編碼',
  '上層WBS',
  '階段',
  '主責處室',
  '主責人',
  '主責人Email',
  'R執行者Email',
  'A核定者Email',
  '協作處室',
  '任務名稱',
  '交付物',
  '完成判準',
  '開始日期',
  '截止日期',
  '預估工期',
  '前置任務ID',
  '狀態',
  '完成比例',
  '關鍵任務',
  '可延誤天數',
  '延誤影響',
  '風險等級',
  '任務回報表單連結',
  '預填回報連結',
  '任務選擇標籤',
  '最近回報時間',
  '最近回報人',
  '最近回報摘要',
  '回報次數',
  '卡關原因',
  '需要跨處室協助',
  '需要協助處室',
  '最近Email通知時間',
  'Email通知次數',
  '提醒狀態',
  '文件連結',
  'Calendar事件ID',
  '最近提醒時間',
  '最後更新者',
  '最後更新時間',
  '備註'
];

HEADERS[SHEETS.RACI_MATRIX] = [
  '任務ID',
  '任務名稱',
  'R執行者',
  'A核定者',
  'C諮詢者',
  'I知會者',
  '備註'
];

HEADERS[SHEETS.MILESTONES] = [
  '里程碑ID',
  '里程碑名稱',
  '期限',
  '完成條件',
  '關聯任務IDs',
  '狀態',
  '備註'
];

HEADERS[SHEETS.RISK_REGISTER] = [
  '風險ID',
  '風險類別',
  '風險描述',
  '發生機率',
  '影響程度',
  '風險等級',
  '觸發條件',
  '因應策略',
  '備援方案',
  '負責人',
  '關聯任務ID',
  '狀態',
  '備註'
];

HEADERS[SHEETS.ISSUE_TRACKER] = [
  '問題ID',
  '來源',
  '問題描述',
  '影響範圍',
  '優先順序',
  '處理人',
  '相關處室',
  '處理期限',
  '狀態',
  '關聯任務ID',
  '建立時間',
  '備註'
];

HEADERS[SHEETS.DECISION_LOG] = [
  '決議ID',
  '會議日期',
  '議題',
  '決議內容',
  '相關處室',
  '後續負責人',
  '關聯任務ID',
  '狀態',
  '文件連結',
  '備註'
];

HEADERS[SHEETS.COMMUNICATION_PLAN] = [
  '溝通ID',
  '溝通主題',
  '對象',
  '管道',
  '預定通知日期',
  '負責人',
  '訊息模板鍵',
  '狀態',
  '備註'
];

HEADERS[SHEETS.TASK_REPORTS] = [
  '回報ID',
  '回報時間',
  '任務選擇',
  '任務ID',
  '回報處室',
  '回報人',
  '回報人Email',
  '回報狀態',
  '完成比例',
  '回報說明',
  '卡關原因',
  '是否需要協助',
  '需要協助處室',
  '文件連結',
  '下次預計完成日期',
  '已同步',
  '同步時間',
  '同步結果',
  '錯誤訊息'
];

HEADERS[SHEETS.OUTCOME_SUBMISSIONS] = [
  '成果ID',
  '專案ID',
  '成果類型',
  '提交者',
  '提交時間',
  '檔案連結',
  '回饋',
  '審核狀態',
  '是否納入AI知識庫',
  '備註'
];

HEADERS[SHEETS.AI_KNOWLEDGE_INDEX] = [
  '知識ID',
  '專案ID',
  '資料來源',
  '標題',
  '摘要',
  '關聯檔案連結',
  'NotebookLM匯入狀態',
  '建議用途',
  'AI提示詞',
  '建立時間',
  '備註'
];

HEADERS[SHEETS.PROJECT_FOLDERS] = [
  '資料夾代碼',
  '專案ID',
  '資料夾名稱',
  '用途',
  '父層資料夾',
  '資料夾ID',
  '資料夾連結',
  '建立時間',
  '備註'
];

HEADERS[SHEETS.NOTIFICATION_QUEUE] = [
  '通知ID',
  '專案ID',
  '通知類型',
  '對象類型',
  '對象ID',
  '收件人',
  '主旨',
  '內容',
  '預定時間',
  '狀態',
  '寄送時間',
  '錯誤訊息',
  '備註'
];

HEADERS[SHEETS.TASK_REPORT_FORM_CONFIG] = [
  '設定鍵',
  '設定值',
  '說明'
];

HEADERS[SHEETS.EMAIL_PREVIEW] = [
  'Email預覽ID',
  '產生時間',
  'Email類型',
  '任務ID',
  '收件人',
  '主旨',
  '內容',
  '寄送狀態',
  '寄送時間',
  '錯誤訊息',
  '備註'
];

HEADERS[SHEETS.DASHBOARD] = [
  '區塊',
  '項目',
  '數值',
  '說明'
];

HEADERS[SHEETS.AUDIT_LOG] = [
  '紀錄ID',
  '時間',
  '操作者',
  '動作',
  '目標工作表',
  '目標ID',
  '舊值',
  '新值',
  '結果',
  '錯誤訊息'
];

HEADERS[SHEETS.GENERATED_DOCS_PREVIEW] = [
  '文件ID',
  '文件類型',
  '產生時間',
  '來源模板',
  '內容',
  '文件連結',
  '備註'
];

var FIELD_ALIASES = {
  '設定鍵': 'Key',
  '設定值': 'Value',
  '說明': 'Description',
  '代碼': 'Code',
  '顯示名稱': 'Display_Name',
  '專案ID': 'Project_ID',
  '專案名稱': 'Project_Name',
  '活動類型': 'Activity_Type',
  '主辦單位': 'Host_Unit',
  '開始日期': 'Start_Date',
  '結束日期': 'End_Date',
  '專案資料夾連結': 'Project_Folder_URL',
  '報名表單連結': 'Registration_Form_URL',
  '成果表單連結': 'Outcome_Form_URL',
  '任務ID': 'Task_ID',
  'WBS編碼': 'WBS_Code',
  '上層WBS': 'Parent_WBS',
  '階段': 'Stage',
  '主責處室': 'Office',
  '主責人': 'Owner',
  '主責人Email': 'Owner_Email',
  'R執行者Email': 'Responsible_Email',
  'A核定者Email': 'Accountable_Email',
  '協作處室': 'Support_Office',
  '任務名稱': 'Task_Name',
  '交付物': 'Deliverable',
  '完成判準': 'Acceptance_Criteria',
  '開始日期': 'Start_Date',
  '截止日期': 'Due_Date',
  '預估工期': 'Duration_Days',
  '前置任務ID': 'Dependency_ID',
  '狀態': 'Status',
  '完成比例': 'Progress_Percent',
  '關鍵任務': 'Critical_Flag',
  '可延誤天數': 'Slack_Days',
  '延誤影響': 'Impact_If_Delayed',
  '風險等級': 'Risk_Level',
  '任務回報表單連結': 'Report_Form_URL',
  '預填回報連結': 'Prefilled_Report_URL',
  '任務選擇標籤': 'Report_Choice_Label',
  '最近回報時間': 'Last_Report_At',
  '最近回報人': 'Last_Report_By',
  '最近回報摘要': 'Last_Report_Text',
  '回報次數': 'Report_Count',
  '卡關原因': 'Blocker',
  '需要跨處室協助': 'Need_Support',
  '需要協助處室': 'Need_Support_Office',
  '最近Email通知時間': 'Last_Email_Sent_At',
  'Email通知次數': 'Email_Count',
  '提醒狀態': 'Reminder_Status',
  '文件連結': 'Doc_Link',
  '報名ID': 'Registration_ID',
  '場次ID': 'Session_ID',
  '場次名稱': 'Session_Name',
  '姓名': 'Name',
  '單位': 'Unit',
  'Email': 'Email',
  '身分': 'Role',
  '報名狀態': 'Registration_Status',
  '錄取狀態': 'Admission_Status',
  '繳費狀態': 'Payment_Status',
  '簽到狀態': 'Attendance_Status',
  '簽到碼': 'Checkin_Code',
  '收費ID': 'Payment_ID',
  '費用項目': 'Fee_Item',
  '應收金額': 'Amount_Due',
  '已收金額': 'Amount_Paid',
  '付款方式': 'Payment_Method',
  '收款日期': 'Paid_At',
  '退費狀態': 'Refund_Status',
  '簽到ID': 'Attendance_ID',
  '簽到時間': 'Checked_In_At',
  '簽到方式': 'Checkin_Method',
  '成果ID': 'Outcome_ID',
  '成果類型': 'Outcome_Type',
  '提交者': 'Submitter',
  '提交時間': 'Submitted_At',
  '檔案連結': 'File_Link',
  '回饋': 'Feedback',
  '審核狀態': 'Review_Status',
  '是否納入AI知識庫': 'Include_In_AI_Knowledge',
  '知識ID': 'Knowledge_ID',
  '資料來源': 'Source_Type',
  '標題': 'Title',
  '摘要': 'Summary',
  '關聯檔案連結': 'Related_File_URL',
  'NotebookLM匯入狀態': 'NotebookLM_Status',
  '建議用途': 'Recommended_Use',
  'AI提示詞': 'AI_Prompt',
  '資料夾代碼': 'Folder_Code',
  '資料夾名稱': 'Folder_Name',
  '用途': 'Purpose',
  '父層資料夾': 'Parent_Folder',
  '資料夾ID': 'Folder_ID',
  '資料夾連結': 'Folder_URL',
  '模板ID': 'Template_ID',
  '模板名稱': 'Template_Name',
  '模組': 'Module',
  '排序': 'Sort_Order',
  '建議主責處室': 'Suggested_Office',
  '建議主責人': 'Suggested_Owner',
  '前置排序': 'Dependency_Order',
  '是否啟用': 'Active',
  '流程ID': 'Workflow_ID',
  '流程名稱': 'Workflow_Name',
  '適用活動類型': 'Applicable_Activity_Type',
  '觸發條件': 'Trigger_Condition',
  '輸入資料表': 'Input_Sheet',
  '輸出資料表': 'Output_Sheet',
  '下一步': 'Next_Step',
  '負責角色': 'Responsible_Role',
  'SLA天數': 'SLA_Days',
  '角色ID': 'Role_ID',
  '角色名稱': 'Role_Name',
  '成員Email': 'Member_Email',
  '適用專案ID': 'Applicable_Project_ID',
  '可查看資料表': 'Readable_Sheets',
  '可編輯資料表': 'Editable_Sheets',
  '可執行功能': 'Executable_Functions',
  '權限狀態': 'Permission_Status',
  '整合ID': 'Integration_ID',
  '整合項目': 'Integration_Name',
  '啟用狀態': 'Enabled_Status',
  '風險提醒': 'Risk_Note',
  '通知ID': 'Notification_ID',
  '通知類型': 'Notification_Type',
  '對象類型': 'Target_Type',
  '對象ID': 'Target_ID',
  '預定時間': 'Scheduled_At',
  '暫存ID': 'Inbox_ID',
  '來源工作表': 'Source_Sheet',
  '回應類型': 'Response_Type',
  '回應時間': 'Response_At',
  '識別鍵': 'Unique_Key',
  '目標資料表': 'Target_Sheet',
  '正規化狀態': 'Normalize_Status',
  '原始資料JSON': 'Raw_JSON',
  'Calendar事件ID': 'Calendar_Event_ID',
  '最近提醒時間': 'Last_Reminded_At',
  '最後更新者': 'Updated_By',
  '最後更新時間': 'Updated_At',
  '備註': 'Note',
  'R執行者': 'Responsible',
  'A核定者': 'Accountable',
  'C諮詢者': 'Consulted',
  'I知會者': 'Informed',
  '里程碑ID': 'Milestone_ID',
  '里程碑名稱': 'Milestone_Name',
  '期限': 'Due_Date',
  '完成條件': 'Completion_Criteria',
  '關聯任務IDs': 'Related_Task_IDs',
  '關聯任務ID': 'Related_Task_ID',
  '風險ID': 'Risk_ID',
  '風險類別': 'Risk_Category',
  '風險描述': 'Risk_Description',
  '發生機率': 'Probability',
  '影響程度': 'Impact',
  '觸發條件': 'Trigger',
  '因應策略': 'Response_Strategy',
  '備援方案': 'Contingency_Plan',
  '負責人': 'Owner',
  '問題ID': 'Issue_ID',
  '來源': 'Source',
  '問題描述': 'Issue_Text',
  '影響範圍': 'Impact',
  '優先順序': 'Priority',
  '處理人': 'Owner',
  '相關處室': 'Related_Office',
  '處理期限': 'Due_Date',
  '建立時間': 'Created_At',
  '決議ID': 'Decision_ID',
  '會議日期': 'Meeting_Date',
  '議題': 'Topic',
  '決議內容': 'Decision',
  '相關處室': 'Related_Office',
  '後續負責人': 'Owner',
  '溝通ID': 'Comm_ID',
  '溝通主題': 'Topic',
  '對象': 'Audience',
  '管道': 'Channel',
  '預定通知日期': 'Scheduled_Date',
  '訊息模板鍵': 'Message_Template_Key',
  '回報ID': 'Report_ID',
  '回報時間': 'Timestamp',
  '時間戳記': 'Timestamp',
  '任務選擇': 'Task_Choice_Label',
  '任務 ID': 'Task_ID',
  '回報處室': 'Office',
  '回報人': 'Reporter',
  '回報人Email': 'Reporter_Email',
  '回報人 Email': 'Reporter_Email',
  '回報狀態': 'Report_Status',
  '任務狀態': 'Report_Status',
  '回報說明': 'Report_Text',
  '相關文件連結': 'Doc_Link',
  '需要協助的處室': 'Need_Support_Office',
  '是否需要協助': 'Need_Support',
  '是否需要跨處室協助': 'Need_Support',
  '下次預計完成日期': 'Next_Date',
  '已同步': 'Processed',
  '同步時間': 'Processed_At',
  '同步結果': 'Process_Result',
  'Email預覽ID': 'Email_Preview_ID',
  'Email類型': 'Email_Type',
  '收件人': 'Recipient',
  '主旨': 'Subject',
  '寄送狀態': 'Send_Status',
  '寄送時間': 'Sent_At',
  '區塊': 'Section',
  '項目': 'Item',
  '數值': 'Value',
  '紀錄ID': 'Log_ID',
  '時間': 'Timestamp',
  '操作者': 'Actor',
  '動作': 'Action',
  '目標工作表': 'Target_Sheet',
  '目標ID': 'Target_ID',
  '舊值': 'Old_Value',
  '新值': 'New_Value',
  '結果': 'Result',
  '錯誤訊息': 'Error_Message',
  '文件ID': 'Doc_ID',
  '文件類型': 'Doc_Type',
  '產生時間': 'Generated_At',
  '來源模板': 'Source_Template',
  '內容': 'Content'
};

var OPTIONS = {
  STAGES: ['籌備期', '預演期', '正日', '善後期'],
  OFFICES: ['校長室', '教務處', '學務處', '總務處', '輔導室', '導師群', '健康中心', '家長會', '志工'],
  SUPPORT_UNITS: ['校長室', '教務處', '學務處', '總務處', '輔導室', '導師群', '健康中心', '家長會', '志工', '各處室', '裁判群', '工作人員', '導師代表', '行政團隊'],
  TASK_STATUSES: ['鎖定', '未開始', '進行中', '待確認', '已完成', '延宕'],
  MILESTONE_STATUSES: ['未開始', '進行中', '已完成', '延宕'],
  RISK_VALUES: ['高', '中', '低'],
  RISK_LEVELS: ['極高', '高', '中', '低'],
  RISK_STATUSES: ['監控中', '已處理', '已發生', '關閉'],
  DECISION_STATUSES: ['待確認', '已決議', '需再議'],
  ISSUE_STATUSES: ['新增', '處理中', '已排除', '轉決議'],
  REPORT_STATUSES: ['進行中', '待確認', '已完成', '卡關'],
  PROGRESS_VALUES: ['0%', '25%', '50%', '75%', '100%'],
  YES_NO: ['是', '否'],
  PROCESSED_VALUES: ['TRUE', 'FALSE'],
  REMINDER_STATUSES: ['未通知', '已通知', '到期提醒', '未回報提醒', '延宕提醒', '暫停提醒'],
  EMAIL_STATUSES: ['未寄送', '已寄送', '寄送失敗', '略過'],
  PRIORITIES: ['高', '中', '低'],
  COMM_CHANNELS: ['Email', 'Line', '校網', '紙本', '會議'],
  COMM_STATUSES: ['未開始', '草稿', '已發送', '需修正'],
  ACTIVITY_TYPES: ['校慶運動會', '教師研習', '寒暑假營隊', '校外教學', '教育優先區活動', '家長日活動', '公文專案管理', '教師社群計畫'],
  PROJECT_STATUSES: ['建案中', '規劃中', '執行中', '結案中', '已結案', '暫停'],
  REGISTRATION_STATUSES: ['新報名', '資料待補', '已確認', '已取消'],
  ADMISSION_STATUSES: ['正取', '備取', '未錄取', '待審核'],
  PAYMENT_STATUSES: ['免收費', '未繳', '部分繳費', '已繳', '需退費', '已退費'],
  ATTENDANCE_STATUSES: ['未簽到', '已簽到', '請假', '缺席'],
  REFUND_STATUSES: ['不需退費', '待退費', '已退費'],
  OUTCOME_TYPES: ['成果報告', '活動照片', '學員回饋', '簽到紀錄', '經費結算', '新聞稿', '其他'],
  REVIEW_STATUSES: ['未審核', '通過', '需補件', '不採用'],
  KNOWLEDGE_SOURCE_TYPES: PROJECT_FILE_CATEGORIES,
  NOTEBOOK_STATUSES: ['未匯入', '待匯入', '已匯入', '不匯入'],
  FOLDER_TYPES: PROJECT_FILE_CATEGORIES,
  PLATFORM_MODULES: ['建案', 'WBS', '任務回報', '報名', '收費', '簽到', '成果', '通知', 'AI知識庫', '結案'],
  WORKFLOW_STATUSES: ['啟用', '停用', '草稿'],
  PERMISSION_STATUSES: ['啟用', '停用'],
  INTEGRATION_NAMES: ['Google Forms', 'Google Drive', 'Google Calendar', 'Gmail', 'Gemini API', 'NotebookLM', 'Line Webhook'],
  ENABLED_STATUSES: ['啟用', '停用', '待設定'],
  NOTIFICATION_TYPES: ['任務通知', '錄取通知', '行前通知', '成果催收', '結案提醒', '系統提醒'],
  NOTIFICATION_STATUSES: ['待寄送', '已轉預覽', '已寄送', '寄送失敗', '取消'],
  TARGET_TYPES: ['任務', '報名者', '工作人員', '處室', '專案'],
  RESPONSE_TYPES: ['報名回應', '成果回應', '任務回報', '未知'],
  NORMALIZE_STATUSES: ['待處理', '已正規化', '略過', '錯誤'],
  BOOLEAN_VALUES: ['TRUE', 'FALSE']
};

var DATA_START_ROW = 2;

var FORM_TITLES = {
  TASK_CHOICE: '任務選擇',
  OFFICE: '回報處室',
  REPORTER: '回報人',
  REPORTER_EMAIL: '回報人 Email',
  STATUS: '任務狀態',
  PROGRESS: '完成比例',
  REPORT_TEXT: '回報說明',
  BLOCKER: '卡關原因',
  DOC_LINK: '相關文件連結',
  NEED_SUPPORT: '是否需要跨處室協助',
  SUPPORT_OFFICE: '需要協助的處室',
  NEXT_DATE: '下次預計完成日期'
};

var REPORT_CHOICE_SEPARATOR = '｜';

// ===== config.gs =====
var DEFAULT_PROJECT_INFO = [
  ['專案名稱', '115 學年度校慶運動會', '本次研習展示專案名稱'],
  ['專案ID', 'ACT-2026-001', '校慶運動會展示專案 ID'],
  ['活動類型', '校慶運動會', '可改為教師研習、營隊、校外教學等活動類型'],
  ['學校名稱', '範例國小', '可改成自己的學校名稱'],
  ['活動日期', '2026-11-14', '校慶正式日期'],
  ['預演日期', '2026-11-07', '預演日期'],
  ['專案管理者', '學務主任', '專案管理者'],
  ['主辦單位', '學務處', '活動主辦單位'],
  ['專案狀態', '執行中', '展示版建案狀態'],
  ['Calendar_ID', '', '講師展示 Calendar 同步時再填寫'],
  ['版本', 'SPORTS_DAY_DEMO_PLATFORM', '校慶運動會專案展示版，聚焦 WBS、任務回報、風險追蹤、通知與結案流程'],
  ['集合時間', '上午 7:50', '家長通知單集合時間'],
  ['服裝規定', '運動服與運動鞋', '家長通知單服裝說明'],
  ['雨備說明', '依學校雨備流程及最新公告辦理', '家長通知單雨備說明']
];

var DEFAULT_TASK_REPORT_FORM_CONFIG = [
  ['Form_ID', '', '任務回報表單 ID'],
  ['Form_URL', '', '任務回報表單網址'],
  ['Form_Edit_URL', '', '任務回報表單編輯網址'],
  ['Response_Sheet_Name', '任務回報', '回報資料寫入表'],
  ['Folder_ID', '', '專案資料夾 ID'],
  ['Folder_Name', '', '專案資料夾名稱'],
  ['Folder_URL', '', '專案資料夾網址'],
  ['Active', 'TRUE', '是否啟用任務回報表單'],
  ['Prefill_Task_Choice', 'TRUE', '是否產生任務選擇預填連結'],
  ['Created_At', '', '建立時間'],
  ['Last_Updated_At', '', '設定最後更新時間']
];

function getAppVersion() {
  return APP_VERSION;
}

// ===== utils.gs =====
function getActiveSpreadsheet_() {
  return SpreadsheetApp.getActiveSpreadsheet();
}

function getOrCreateSheet_(spreadsheet, sheetName) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  return sheet;
}

function getSheetOrThrow_(sheetName) {
  var sheet = getActiveSpreadsheet_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('找不到工作表：' + sheetName + '。請先執行「初始化專案」。');
  }
  return sheet;
}

function resetSheetWithHeaders_(sheet, headers) {
  sheet.clear();
  sheet.clearFormats();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, headers.length)
    .setFontWeight('bold')
    .setBackground('#1f4e79')
    .setFontColor('#ffffff');
  sheet.autoResizeColumns(1, headers.length);
}

function getHeaderMap_(sheet) {
  var lastColumn = sheet.getLastColumn();
  if (lastColumn < 1) {
    return {};
  }
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var map = {};
  headers.forEach(function(header, index) {
    if (header) {
      var headerText = String(header);
      map[headerText] = index + 1;
      if (typeof FIELD_ALIASES !== 'undefined' && FIELD_ALIASES[headerText]) {
        map[FIELD_ALIASES[headerText]] = index + 1;
      }
    }
  });
  return map;
}

function readObjects_(sheetName) {
  var sheet = getSheetOrThrow_(sheetName);
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  if (lastRow < DATA_START_ROW || lastColumn < 1) {
    return [];
  }
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var values = sheet.getRange(DATA_START_ROW, 1, lastRow - 1, lastColumn).getValues();
  return values.map(function(row, rowIndex) {
    var object = { _rowNumber: rowIndex + DATA_START_ROW };
    headers.forEach(function(header, index) {
      if (header) {
        var headerText = String(header);
        object[headerText] = row[index];
        if (typeof FIELD_ALIASES !== 'undefined' && FIELD_ALIASES[headerText]) {
          object[FIELD_ALIASES[headerText]] = row[index];
        }
      }
    });
    return object;
  }).filter(function(object) {
    return Object.keys(object).some(function(key) {
      return key !== '_rowNumber' && object[key] !== '' && object[key] !== null;
    });
  });
}

function writeRows_(sheetName, rows, clearData) {
  var sheet = getSheetOrThrow_(sheetName);
  var headers = HEADERS[sheetName];
  if (!headers) {
    throw new Error('未定義欄位：' + sheetName);
  }
  if (clearData && sheet.getLastRow() >= DATA_START_ROW) {
    sheet.getRange(DATA_START_ROW, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
  }
  if (!rows || rows.length === 0) {
    return;
  }
  var startRow = Math.max(sheet.getLastRow() + 1, DATA_START_ROW);
  sheet.getRange(startRow, 1, rows.length, headers.length).setValues(rows);
}

function normalizeDate_(value) {
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime())) {
    return new Date(value.getFullYear(), value.getMonth(), value.getDate());
  }
  if (typeof value === 'string' && value.trim()) {
    var parsed = new Date(value);
    if (!isNaN(parsed.getTime())) {
      return new Date(parsed.getFullYear(), parsed.getMonth(), parsed.getDate());
    }
  }
  return null;
}

function today_() {
  var now = new Date();
  return new Date(now.getFullYear(), now.getMonth(), now.getDate());
}

function formatDate_(value) {
  var date = normalizeDate_(value);
  if (!date) {
    return '';
  }
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

function makeId_(prefix) {
  return prefix + '-' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMddHHmmss') + '-' + Math.floor(Math.random() * 1000);
}

function getProjectValue_(key) {
  var displayKey = getProjectDisplayKey_(key);
  var rows = readObjects_(SHEETS.PROJECT_INFO);
  for (var i = 0; i < rows.length; i++) {
    if (rows[i].Key === key || rows[i].Key === displayKey) {
      return rows[i].Value;
    }
  }
  return '';
}

function getProjectDisplayKey_(key) {
  var map = {
    Project_Name: '專案名稱',
    Project_ID: '專案ID',
    Activity_Type: '活動類型',
    Host_Unit: '主辦單位',
    Project_Status: '專案狀態',
    School_Name: '學校名稱',
    Event_Date: '活動日期',
    Rehearsal_Date: '預演日期',
    Project_Manager: '專案管理者',
    Version: '版本',
    Notice_Gather_Time: '集合時間',
    Notice_Dress_Code: '服裝規定',
    Rain_Plan: '雨備說明'
  };
  return map[key] || key;
}

function showToast_(message, title) {
  getActiveSpreadsheet_().toast(message, title || '活動專案中控台', 6);
}

function alert_(message) {
  SpreadsheetApp.getUi().alert(message);
}

function joinNonEmpty_(items, separator) {
  return items.filter(function(item) {
    return item !== '' && item !== null && item !== undefined;
  }).join(separator || '、');
}

function validateCategory_(category) {
  var text = String(category || '').trim();
  if (PROJECT_FILE_CATEGORIES.indexOf(text) !== -1) {
    return text;
  }
  Logger.log('未定義的專案資料分類，已改為「其他」：' + text);
  return '其他';
}

function getActiveSpreadsheetFolder_() {
  var spreadsheet = getActiveSpreadsheet_();
  var spreadsheetFile = DriveApp.getFileById(spreadsheet.getId());
  var parents = spreadsheetFile.getParents();
  if (parents.hasNext()) {
    return parents.next();
  }
  return DriveApp.getRootFolder();
}

function moveFileToFolder_(fileId, targetFolder) {
  var file = DriveApp.getFileById(fileId);
  var rootRemovalWarning = '';

  targetFolder.addFile(file);

  try {
    DriveApp.getRootFolder().removeFile(file);
  } catch (error) {
    rootRemovalWarning = '無法從 root 移除檔案，可能是 Drive 權限或新版 Drive 架構限制：' + error.message;
    Logger.log(rootRemovalWarning);
  }

  return {
    fileId: file.getId(),
    fileName: file.getName(),
    fileUrl: file.getUrl(),
    folderId: targetFolder.getId(),
    folderName: targetFolder.getName(),
    folderUrl: targetFolder.getUrl(),
    rootRemovalWarning: rootRemovalWarning
  };
}

// ===== audit_service.gs =====
function writeAuditLog(action, targetSheet, targetId, oldValue, newValue, result, errorMessage) {
  var spreadsheet = getActiveSpreadsheet_();
  var sheet = spreadsheet.getSheetByName(SHEETS.AUDIT_LOG);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(SHEETS.AUDIT_LOG);
    resetSheetWithHeaders_(sheet, HEADERS[SHEETS.AUDIT_LOG]);
  }

  var actor = '';
  try {
    actor = Session.getActiveUser().getEmail();
  } catch (error) {
    actor = 'unknown';
  }

  var row = [
    makeId_('LOG'),
    new Date(),
    actor || 'unknown',
    action || '',
    targetSheet || '',
    targetId || '',
    oldValue || '',
    newValue || '',
    result || '成功',
    errorMessage || ''
  ];
  sheet.appendRow(row);
}

function writeFailureAuditLog_(action, targetSheet, targetId, error) {
  writeAuditLog(
    action,
    targetSheet,
    targetId,
    '',
    '',
    '失敗',
    error && error.message ? error.message : String(error)
  );
}

// ===== ui_menu.gs =====
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('運動會專案中控台')
    .addSubMenu(SpreadsheetApp.getUi().createMenu('專案初始化')
      .addItem('初始化運動會專案', 'initSportsDayProject')
      .addItem('匯入校慶 WBS 任務模板', 'importSportsDayWbsTemplate')
      .addItem('建立運動會專案資料夾', 'createAdvancedProjectFolderStructure'))
    .addSubMenu(SpreadsheetApp.getUi().createMenu('任務回報')
      .addItem('建立任務回報表單', 'createTaskReportForm')
      .addItem('更新任務回報表單選項', 'updateTaskReportFormChoices')
      .addItem('產生任務預填回報連結', 'generatePrefilledReportUrls')
      .addItem('新增示範卡關回報', 'appendDemoBlockedReport')
      .addItem('新增示範已完成回報', 'appendDemoCompletedReport')
      .addItem('處理任務回報', 'processTaskReports'))
    .addSubMenu(SpreadsheetApp.getUi().createMenu('進度與風險')
      .addItem('執行每日進度檢查', 'runDailyProgressCheck')
      .addItem('檢查任務依賴並解鎖', 'unlockDependentTasks')
      .addItem('更新風險等級', 'updateRiskLevels')
      .addItem('更新儀表板', 'updateDashboard'))
    .addSubMenu(SpreadsheetApp.getUi().createMenu('通知與文件')
      .addItem('產生任務通知 Email 預覽', 'generateTaskEmailPreview')
      .addItem('產生到期與延宕提醒 Email 預覽', 'sendDueAndOverdueReminders')
      .addItem('產生通知佇列', 'generateNotificationQueueMenu')
      .addItem('通知佇列轉 Email 預覽', 'exportNotificationQueueToEmailPreview')
      .addItem('產生家長通知單預覽', 'generateParentNoticePreview')
      .addItem('產生活動結案報告草稿', 'generateActivityClosureDraftPreview'))
    .addSubMenu(SpreadsheetApp.getUi().createMenu('成果與知識庫')
      .addItem('更新 AI 知識庫索引', 'refreshAiKnowledgeIndex'))
    .addSubMenu(SpreadsheetApp.getUi().createMenu('日曆同步')
      .addItem('同步任務期限到 Calendar（講師展示用）', 'syncTaskDueDatesToCalendar')
      .addItem('同步重要日期到 Calendar（講師展示用）', 'syncMilestonesToCalendar'))
    .addSeparator()
    .addItem('重置示範資料', 'resetDemoData')
    .addItem('關於本系統', 'showAboutDialog')
    .addToUi();
}

function showAboutDialog() {
  var message = [
    '校慶運動會專案管理展示版',
    '',
    '版本：' + APP_VERSION,
    '定位：以 Google Sheets + Apps Script 串起校慶運動會的 WBS 任務、回報表單、風險問題、通知文件、AI 知識庫與結案報告。',
    '',
    '建議操作順序：',
    '1. 初始化運動會專案',
    '2. 匯入校慶 WBS 任務模板',
    '3. 建立運動會專案資料夾',
    '4. 建立任務回報表單',
    '5. 產生任務預填回報連結',
    '6. 新增示範回報並處理任務回報',
    '7. 執行每日進度檢查與更新風險等級',
    '8. 產生通知與文件預覽',
    '9. 更新 AI 知識庫索引',
    '10. 產生活動結案報告草稿',
    '11. 更新儀表板並查看燈號'
  ].join('\n');
  alert_(message);
}

// ===== sheet_setup.gs =====
function initSportsDayProject() {
  try {
    var spreadsheet = getActiveSpreadsheet_();
    SHEET_ORDER.forEach(function(sheetName) {
      var sheet = getOrCreateSheet_(spreadsheet, sheetName);
      resetSheetWithHeaders_(sheet, HEADERS[sheetName]);
      applyBaseSheetFormatting_(sheetName, sheet);
    });

    writeRows_(SHEETS.PROJECT_INFO, DEFAULT_PROJECT_INFO, true);
    seedSystemCodeTable_();
    writeRows_(SHEETS.TASK_REPORT_FORM_CONFIG, DEFAULT_TASK_REPORT_FORM_CONFIG, true);
    seedEmptyDashboard_();
    applyDataValidations_();
    applyConditionalFormats_();
    reorderSheets_(spreadsheet);
    writeAuditLog('初始化專案', 'ALL', '', '', '建立 ' + SHEET_ORDER.length + ' 張運動會專案展示工作表', '成功', '');

    updateDashboard();
    alert_([
      '已完成初始化：',
      '- 建立 ' + SHEET_ORDER.length + ' 張運動會專案展示工作表',
      '- 建立欄位、下拉選單與基本條件格式',
      '- 建立專案資訊、任務回報表單設定與儀表板',
      '- 寫入稽核紀錄',
      '',
      '下一步請執行：匯入校慶 WBS 任務模板'
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('初始化專案', 'ALL', '', error);
    throw error;
  }
}

function applyBaseSheetFormatting_(sheetName, sheet) {
  var headers = HEADERS[sheetName];
  if (!headers) {
    return;
  }
  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, headers.length).setWrap(true);
  sheet.getRange(1, 1, Math.max(1, sheet.getMaxRows()), headers.length).setVerticalAlignment('middle');

  var widths = getColumnWidths_(sheetName);
  for (var i = 0; i < headers.length; i++) {
    sheet.setColumnWidth(i + 1, widths[headers[i]] || 130);
  }
}

function getColumnWidths_(sheetName) {
  var common = {
    Task_Name: 220,
    Deliverable: 220,
    Acceptance_Criteria: 240,
    Impact_If_Delayed: 240,
    Note: 220,
    Risk_Description: 260,
    Response_Strategy: 240,
    Contingency_Plan: 240,
    Issue_Text: 260,
    Decision: 260,
    Content: 520
  };
  if (sheetName === SHEETS.DASHBOARD) {
    common.Section = 160;
    common.Item = 260;
    common.Value = 180;
  } else if (sheetName === SHEETS.AI_KNOWLEDGE_INDEX) {
    common.Summary = 320;
    common.AI_Prompt = 420;
  } else if (sheetName === SHEETS.PROJECT_FOLDERS) {
    common.Folder_URL = 320;
  }
  return common;
}

function seedEmptyDashboard_() {
  var rows = [
    ['專案資訊', '專案名稱', '', ''],
    ['專案資訊', '活動日期', '', ''],
    ['燈號', '整體狀態', '🟢 正常', '依任務進度、風險狀態、協作溝通自動判斷'],
    ['燈號', '任務進度', '🟢 正常', '依延宕任務數與關鍵任務延宕數自動判斷'],
    ['燈號', '風險狀態', '🟢 正常', '依高風險與極高風險項目數自動判斷'],
    ['燈號', '協作溝通', '🟢 正常', '依待決議事項與問題追蹤狀態自動判斷'],
    ['任務總覽', '總任務數', 0, ''],
    ['任務總覽', '已完成任務數', 0, ''],
    ['任務總覽', '完成率', '0%', ''],
    ['任務總覽', '進行中任務數', 0, ''],
    ['任務總覽', '延宕任務數', 0, ''],
    ['任務總覽', '關鍵任務數', 0, ''],
    ['任務總覽', '關鍵任務延宕數', 0, ''],
    ['風險總覽', '高風險與極高風險項目', 0, ''],
    ['協商總覽', '待決議事項', 0, ''],
    ['問題追蹤', '未解決問題數', 0, ''],
    ['成果管理', '成果資料數', 0, ''],
    ['AI知識庫', '待匯入資料數', 0, ''],
    ['通知文件', '待處理通知數', 0, ''],
    ['教學提示', '下一步', '請執行「匯入校慶 WBS 任務模板」', '']
  ];
  writeRows_(SHEETS.DASHBOARD, rows, true);
}

function seedSystemCodeTable_() {
  writeRows_(SHEETS.SYSTEM_CODE_TABLE, PROJECT_FILE_CATEGORY_CODES, true);
}

function applyDataValidations_() {
  var spreadsheet = getActiveSpreadsheet_();
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Stage', OPTIONS.STAGES);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Office', OPTIONS.OFFICES);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Support_Office', OPTIONS.SUPPORT_UNITS);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Status', OPTIONS.TASK_STATUSES);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Progress_Percent', OPTIONS.PROGRESS_VALUES);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Critical_Flag', OPTIONS.BOOLEAN_VALUES);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Risk_Level', OPTIONS.RISK_LEVELS);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Need_Support', OPTIONS.YES_NO);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Need_Support_Office', OPTIONS.SUPPORT_UNITS, true);
  applyValidationByHeader_(spreadsheet, SHEETS.MASTER_WBS, 'Reminder_Status', OPTIONS.REMINDER_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.RACI_MATRIX, 'Consulted', OPTIONS.SUPPORT_UNITS, true);
  applyValidationByHeader_(spreadsheet, SHEETS.RACI_MATRIX, 'Informed', OPTIONS.SUPPORT_UNITS, true);

  applyValidationByHeader_(spreadsheet, SHEETS.MILESTONES, 'Status', OPTIONS.MILESTONE_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.RISK_REGISTER, 'Risk_Category', ['天候', '場地', '設備', '人力', '安全', '溝通', '文件', '時程', '跨處室']);
  applyValidationByHeader_(spreadsheet, SHEETS.RISK_REGISTER, 'Probability', OPTIONS.RISK_VALUES);
  applyValidationByHeader_(spreadsheet, SHEETS.RISK_REGISTER, 'Impact', OPTIONS.RISK_VALUES);
  applyValidationByHeader_(spreadsheet, SHEETS.RISK_REGISTER, 'Risk_Level', OPTIONS.RISK_LEVELS);
  applyValidationByHeader_(spreadsheet, SHEETS.RISK_REGISTER, 'Status', OPTIONS.RISK_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.ISSUE_TRACKER, 'Priority', OPTIONS.PRIORITIES);
  applyValidationByHeader_(spreadsheet, SHEETS.ISSUE_TRACKER, 'Related_Office', OPTIONS.SUPPORT_UNITS, true);
  applyValidationByHeader_(spreadsheet, SHEETS.ISSUE_TRACKER, 'Status', OPTIONS.ISSUE_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.DECISION_LOG, 'Status', OPTIONS.DECISION_STATUSES);
  applyValidationByHeader_(spreadsheet, SHEETS.COMMUNICATION_PLAN, 'Channel', OPTIONS.COMM_CHANNELS);
  applyValidationByHeader_(spreadsheet, SHEETS.COMMUNICATION_PLAN, 'Status', OPTIONS.COMM_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.TASK_REPORTS, 'Office', OPTIONS.OFFICES);
  applyValidationByHeader_(spreadsheet, SHEETS.TASK_REPORTS, 'Report_Status', OPTIONS.REPORT_STATUSES);
  applyValidationByHeader_(spreadsheet, SHEETS.TASK_REPORTS, 'Progress_Percent', OPTIONS.PROGRESS_VALUES);
  applyValidationByHeader_(spreadsheet, SHEETS.TASK_REPORTS, 'Need_Support', OPTIONS.YES_NO);
  applyValidationByHeader_(spreadsheet, SHEETS.TASK_REPORTS, 'Need_Support_Office', OPTIONS.SUPPORT_UNITS, true);
  applyValidationByHeader_(spreadsheet, SHEETS.TASK_REPORTS, 'Processed', OPTIONS.PROCESSED_VALUES);

  applyValidationByHeader_(spreadsheet, SHEETS.EMAIL_PREVIEW, 'Send_Status', OPTIONS.EMAIL_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.PROJECT_CASES, 'Activity_Type', OPTIONS.ACTIVITY_TYPES);
  applyValidationByHeader_(spreadsheet, SHEETS.PROJECT_CASES, 'Status', OPTIONS.PROJECT_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.OUTCOME_SUBMISSIONS, 'Outcome_Type', OPTIONS.OUTCOME_TYPES);
  applyValidationByHeader_(spreadsheet, SHEETS.OUTCOME_SUBMISSIONS, 'Review_Status', OPTIONS.REVIEW_STATUSES);
  applyValidationByHeader_(spreadsheet, SHEETS.OUTCOME_SUBMISSIONS, 'Include_In_AI_Knowledge', OPTIONS.YES_NO);

  applyValidationByHeader_(spreadsheet, SHEETS.AI_KNOWLEDGE_INDEX, 'Source_Type', PROJECT_FILE_CATEGORIES);
  applyValidationByHeader_(spreadsheet, SHEETS.AI_KNOWLEDGE_INDEX, 'NotebookLM_Status', OPTIONS.NOTEBOOK_STATUSES);

  applyValidationByHeader_(spreadsheet, SHEETS.PROJECT_FOLDERS, 'Purpose', PROJECT_FILE_CATEGORIES);

  applyValidationByHeader_(spreadsheet, SHEETS.NOTIFICATION_QUEUE, 'Notification_Type', OPTIONS.NOTIFICATION_TYPES);
  applyValidationByHeader_(spreadsheet, SHEETS.NOTIFICATION_QUEUE, 'Target_Type', OPTIONS.TARGET_TYPES);
  applyValidationByHeader_(spreadsheet, SHEETS.NOTIFICATION_QUEUE, 'Status', OPTIONS.NOTIFICATION_STATUSES);
}

function applyValidationByHeader_(spreadsheet, sheetName, headerName, options, allowInvalid) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    return;
  }
  var headerMap = getHeaderMap_(sheet);
  var column = headerMap[headerName];
  if (!column) {
    return;
  }
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(options, true)
    .setAllowInvalid(Boolean(allowInvalid))
    .build();
  sheet.getRange(DATA_START_ROW, column, Math.max(1, sheet.getMaxRows() - 1), 1).setDataValidation(rule);
}

function applyConditionalFormats_() {
  var spreadsheet = getActiveSpreadsheet_();
  applyMasterWbsConditionalFormats_(spreadsheet.getSheetByName(SHEETS.MASTER_WBS));
  applyRiskConditionalFormats_(spreadsheet.getSheetByName(SHEETS.RISK_REGISTER));
}

function applyMasterWbsConditionalFormats_(sheet) {
  if (!sheet) {
    return;
  }
  var headers = getHeaderMap_(sheet);
  var statusColumn = headers.Status;
  var criticalColumn = headers.Critical_Flag;
  if (!statusColumn) {
    return;
  }
  var rules = [];
  var dataRange = sheet.getRange(DATA_START_ROW, 1, Math.max(1, sheet.getMaxRows() - 1), sheet.getLastColumn());
  rules.push(SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$' + columnToLetter_(statusColumn) + DATA_START_ROW + '="已完成"')
    .setBackground('#d9ead3')
    .setRanges([dataRange])
    .build());
  rules.push(SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$' + columnToLetter_(statusColumn) + DATA_START_ROW + '="延宕"')
    .setBackground('#f4cccc')
    .setFontColor('#990000')
    .setRanges([dataRange])
    .build());
  rules.push(SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$' + columnToLetter_(statusColumn) + DATA_START_ROW + '="鎖定"')
    .setBackground('#eeeeee')
    .setFontColor('#666666')
    .setRanges([dataRange])
    .build());
  if (criticalColumn) {
    rules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=TO_TEXT($' + columnToLetter_(criticalColumn) + DATA_START_ROW + ')="TRUE"')
      .setBackground('#fce5cd')
      .setRanges([dataRange])
      .build());
  }
  sheet.setConditionalFormatRules(rules);
}

function applyRiskConditionalFormats_(sheet) {
  if (!sheet) {
    return;
  }
  var headers = getHeaderMap_(sheet);
  var levelColumn = headers.Risk_Level;
  if (!levelColumn) {
    return;
  }
  var dataRange = sheet.getRange(DATA_START_ROW, 1, Math.max(1, sheet.getMaxRows() - 1), sheet.getLastColumn());
  var rules = [
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$' + columnToLetter_(levelColumn) + DATA_START_ROW + '="極高"')
      .setBackground('#cc0000')
      .setFontColor('#ffffff')
      .setRanges([dataRange])
      .build(),
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$' + columnToLetter_(levelColumn) + DATA_START_ROW + '="高"')
      .setBackground('#f4cccc')
      .setRanges([dataRange])
      .build()
  ];
  sheet.setConditionalFormatRules(rules);
}

function columnToLetter_(column) {
  var temp = '';
  var letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function reorderSheets_(spreadsheet) {
  SHEET_ORDER.forEach(function(sheetName, index) {
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet) {
      spreadsheet.setActiveSheet(sheet);
      spreadsheet.moveActiveSheet(index + 1);
    }
  });
}

// ===== template_wbs.gs =====
function importSportsDayWbsTemplate() {
  try {
    var wbsRows = getSportsDayWbsRows_();
    if (!validateWbsTemplateOptions_(wbsRows)) {
      writeAuditLog('匯入 WBS 任務模板', SHEETS.MASTER_WBS, '', '', 'WBS 模板資料驗證失敗，停止匯入', '失敗', '');
      return;
    }

    var masterSheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    if (masterSheet.getLastRow() >= DATA_START_ROW) {
      var ui = SpreadsheetApp.getUi();
      var response = ui.alert(
        '匯入 WBS 任務模板',
        'WBS任務主表已有資料。是否清空現有示範資料後重新匯入？',
        ui.ButtonSet.YES_NO
      );
      if (response !== ui.Button.YES) {
        alert_('已取消匯入。');
        return;
      }
    }

    writeRows_(SHEETS.MASTER_WBS, wbsRows, true);
    seedRaciFromTasks();
    seedMilestones();
    seedTrainingRisks_();
    seedTrainingIssues_();
    seedTrainingDecisions_();
    seedCommunicationPlan_();
    updateTaskReportUrls_();
    refreshTaskReportFormAfterTaskChange_();
    updateRiskLevels();
    updateDashboard();

    writeAuditLog('匯入 WBS 任務模板', SHEETS.MASTER_WBS, '', '', '匯入校慶 WBS、RACI、里程碑與研習資料', '成功', '');
    alert_([
      'WBS 任務模板匯入完成：',
      '- WBS任務主表：' + wbsRows.length + ' 筆任務',
      '- RACI權責矩陣：已同步產生',
      '- 里程碑：已建立預設資料',
      '- Risk / Issue / Decision / Communication：已加入研習展示資料',
      '',
      '下一步可執行：更新儀表板'
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('匯入 WBS 任務模板', SHEETS.MASTER_WBS, '', error);
    throw error;
  }
}

function validateWbsTemplateOptions_(rows) {
  var headers = HEADERS[SHEETS.MASTER_WBS];
  var errors = [];
  var officeFields = ['Office'];
  var supportFields = ['Support_Office', 'Need_Support_Office'];

  rows.forEach(function(row) {
    var taskId = row[getHeaderIndexForInternalField_(headers, 'Task_ID')];
    var taskName = row[getHeaderIndexForInternalField_(headers, 'Task_Name')];

    officeFields.forEach(function(field) {
      var index = getHeaderIndexForInternalField_(headers, field);
      if (index === -1) {
        return;
      }
      var value = row[index];
      if (value && OPTIONS.OFFICES.indexOf(value) === -1) {
        errors.push({
          taskId: taskId,
          taskName: taskName,
          field: field,
          value: value,
          suggestion: '請使用正式處室清單：' + OPTIONS.OFFICES.join('、')
        });
      }
    });

    supportFields.forEach(function(field) {
      var index = getHeaderIndexForInternalField_(headers, field);
      if (index === -1) {
        return;
      }
      var value = row[index];
      var invalidValues = getInvalidSupportValues_(value);
      invalidValues.forEach(function(invalidValue) {
        errors.push({
          taskId: taskId,
          taskName: taskName,
          field: field,
          value: invalidValue,
          suggestion: '請使用協作單位清單：' + OPTIONS.SUPPORT_UNITS.join('、')
        });
      });
    });
  });

  if (errors.length > 0) {
    var message = errors.slice(0, 10).map(function(error) {
      return [
        'Task_ID：' + error.taskId,
        'Task_Name：' + error.taskName,
        '欄位：' + error.field,
        '不合法的值：' + error.value,
        '建議：' + error.suggestion
      ].join('\n');
    }).join('\n\n');
    if (errors.length > 10) {
      message += '\n\n其餘錯誤 ' + (errors.length - 10) + ' 筆未列出。';
    }
    alert_('WBS 模板資料驗證失敗：\n\n' + message);
    return false;
  }

  return true;
}

function getHeaderIndexForInternalField_(headers, internalFieldName) {
  for (var i = 0; i < headers.length; i++) {
    var header = headers[i];
    if (header === internalFieldName) {
      return i;
    }
    if (FIELD_ALIASES[header] === internalFieldName) {
      return i;
    }
  }
  return -1;
}

function getInvalidSupportValues_(value) {
  if (!value) {
    return [];
  }
  return String(value)
    .split(/[、,，]/)
    .map(function(item) { return item.trim(); })
    .filter(Boolean)
    .filter(function(item) { return OPTIONS.SUPPORT_UNITS.indexOf(item) === -1; });
}

function getSportsDayWbsRows_() {
  return [
    taskRow_('SPORTS-001', '1.1', '1.0', '籌備期', '學務處', '學務主任', '校長室', '成立校慶籌備小組', '籌備小組名單與分工表', '校長核定小組名單並公告分工', '2026-09-01', '2026-09-03', 3, '', '未開始', 'TRUE', 0, '後續所有籌備工作無法啟動', '高', ''),
    taskRow_('SPORTS-002', '1.2', '1.0', '籌備期', '學務處', '體育組長', '教務處', '確認活動日期與預演日期', '活動日期與預演日期確認紀錄', '日期寫入專案資訊並公告行政團隊', '2026-09-02', '2026-09-05', 4, 'SPORTS-001', '鎖定', 'TRUE', 0, '影響全校課務、場地與公告時程', '高', ''),
    taskRow_('SPORTS-003', '1.3', '1.0', '籌備期', '學務處', '資訊組長', '教務處', '建立校慶行政中控台', 'Google Sheets 中控台', '完成工作表、欄位與分享權限設定', '2026-09-03', '2026-09-06', 4, 'SPORTS-001', '鎖定', 'FALSE', 2, '任務追蹤分散在不同管道', '中', ''),
    taskRow_('SPORTS-004', '1.4', '1.0', '籌備期', '學務處', '學務主任', '各處室', '召開第一次籌備會議', '會議紀錄與待辦事項', '完成會議紀錄並轉入任務表', '2026-09-06', '2026-09-08', 3, 'SPORTS-002', '鎖定', 'TRUE', 0, '跨處室權責不清', '高', ''),

    taskRow_('SPORTS-005', '2.1', '2.0', '籌備期', '學務處', '體育組長', '教務處', '確認校慶主題與活動主軸', '活動主題文字', '主題經行政會議確認', '2026-09-08', '2026-09-12', 5, 'SPORTS-004', '鎖定', 'FALSE', 3, '影響邀請卡與活動設計', '中', ''),
    taskRow_('SPORTS-006', '2.2', '2.0', '籌備期', '學務處', '體育組長', '教務處', '規劃開幕典禮流程', '開幕典禮流程表', '含進場、致詞、表演與宣誓流程', '2026-09-10', '2026-09-18', 9, 'SPORTS-005', '鎖定', 'TRUE', 1, '影響預演、司儀稿與場地需求', '高', ''),
    taskRow_('SPORTS-007', '2.3', '2.0', '籌備期', '學務處', '體育組長', '導師群', '確認競賽項目', '競賽項目清單', '各年級競賽項目與規則定稿', '2026-09-12', '2026-09-20', 9, 'SPORTS-005', '鎖定', 'TRUE', 1, '影響賽程、裁判與器材準備', '高', ''),
    taskRow_('SPORTS-008', '2.4', '2.0', '籌備期', '教務處', '教學組長', '學務處', '規劃班級表演節目', '班級表演清單', '確認表演班級、時間與音樂需求', '2026-09-15', '2026-09-24', 10, 'SPORTS-005', '鎖定', 'FALSE', 2, '影響預演流程與音控準備', '中', ''),
    taskRow_('SPORTS-009', '2.5', '2.0', '籌備期', '學務處', '體育組長', '教務處', '完成活動流程總表', '活動流程總表', '流程總表經主任確認並公告', '2026-09-20', '2026-09-28', 9, 'SPORTS-006,SPORTS-007,SPORTS-008', '鎖定', 'TRUE', 0, '秩序冊與預演安排無法推進', '高', ''),

    taskRow_('SPORTS-010', '3.1', '3.0', '籌備期', '教務處', '教學組長', '學務處', '規劃預演課務調整', '預演課務調整表', '預演時段、授課調整與代課安排完成', '2026-09-25', '2026-10-02', 8, 'SPORTS-009', '鎖定', 'TRUE', 1, '預演無法順利執行', '高', ''),
    taskRow_('SPORTS-011', '3.2', '3.0', '籌備期', '教務處', '教學組長', '導師群', '公告班級集合與動線', '班級集合動線說明', '各班導師收到最新版集合動線', '2026-09-28', '2026-10-06', 9, 'SPORTS-009', '鎖定', 'FALSE', 2, '活動當日動線混亂', '中', ''),
    taskRow_('SPORTS-012', '3.3', '3.0', '籌備期', '導師群', '各班導師', '學務處', '確認各班參賽名單', '各班參賽名單', '名單收齊並回填至賽程資料', '2026-09-28', '2026-10-05', 8, 'SPORTS-007', '鎖定', 'TRUE', 1, '賽程編排與秩序冊資料延誤', '高', ''),
    taskRow_('SPORTS-013', '3.4', '3.0', '籌備期', '教務處', '註冊組長', '學務處', '整理導師通知事項', '導師通知事項清單', '導師群收到統一版本通知', '2026-10-01', '2026-10-08', 8, 'SPORTS-011', '鎖定', 'FALSE', 2, '通知版本不一致', '中', ''),

    taskRow_('SPORTS-014', '4.1', '4.0', '籌備期', '學務處', '體育組長', '導師群', '完成競賽規程', '競賽規程文件', '規程含比賽方式、裁判規則與注意事項', '2026-09-25', '2026-10-03', 9, 'SPORTS-007', '鎖定', 'TRUE', 1, '裁判與導師無法一致執行規則', '高', ''),
    taskRow_('SPORTS-015', '4.2', '4.0', '籌備期', '學務處', '生教組長', '導師群', '安排學生秩序與安全管理', '學生秩序安全分工表', '各區域安全管理人員已確認', '2026-10-01', '2026-10-10', 10, 'SPORTS-009', '鎖定', 'TRUE', 1, '活動日安全風險升高', '高', ''),
    taskRow_('SPORTS-016', '4.3', '4.0', '籌備期', '學務處', '體育組長', '志工', '安排裁判與工作人員', '裁判與工作人員名冊', '各比賽項目均有裁判與備援人力', '2026-10-01', '2026-10-12', 12, 'SPORTS-014', '鎖定', 'TRUE', 1, '競賽無法準時進行', '高', ''),
    taskRow_('SPORTS-017', '4.4', '4.0', '預演期', '學務處', '體育組長', '導師群', '完成進退場流程演練', '進退場演練紀錄', '預演時各班能依動線進退場', '2026-10-10', '2026-10-20', 11, 'SPORTS-011,SPORTS-015', '鎖定', 'TRUE', 0, '活動當日流程延誤', '高', ''),

    taskRow_('SPORTS-018', '5.1', '5.0', '籌備期', '總務處', '事務組長', '學務處', '完成場地配置圖', '場地配置圖', '司令台、班級區、家長區、醫護站位置定稿', '2026-09-25', '2026-10-03', 9, 'SPORTS-009', '鎖定', 'TRUE', 1, '帳棚、音響與動線無法安排', '高', ''),
    taskRow_('SPORTS-019', '5.2', '5.0', '籌備期', '總務處', '事務組長', '家長會', '確認帳棚桌椅數量', '帳棚桌椅需求表', '數量、位置、借用與搬運人力確認', '2026-10-01', '2026-10-10', 10, 'SPORTS-018', '鎖定', 'TRUE', 1, '貴賓、家長與工作區無法配置', '高', ''),
    taskRow_('SPORTS-020', '5.3', '5.0', '籌備期', '總務處', '事務組長', '學務處', '確認音響與電力配置', '音響電力配置表', '音控位置、電源、麥克風與備援設備確認', '2026-10-01', '2026-10-10', 10, 'SPORTS-018', '鎖定', 'TRUE', 0, '開幕、頒獎與表演受影響', '高', ''),
    taskRow_('SPORTS-021', '5.4', '5.0', '籌備期', '總務處', '事務組長', '健康中心', '規劃飲水與清潔', '飲水清潔分工表', '飲水點、垃圾回收與廁所清潔安排完成', '2026-10-05', '2026-10-15', 11, 'SPORTS-018', '鎖定', 'FALSE', 2, '環境品質與補水不足', '中', ''),
    taskRow_('SPORTS-022', '5.5', '5.0', '籌備期', '總務處', '總務主任', '學務處', '確認雨備場地與流程', '雨備流程表', '雨備場地、容量、切換時間與公告流程確認', '2026-10-05', '2026-10-15', 11, 'SPORTS-018', '鎖定', 'TRUE', 0, '天候變化時無法應變', '高', ''),

    taskRow_('SPORTS-023', '6.1', '6.0', '籌備期', '輔導室', '特教組長', '導師群', '盤點特殊學生支持需求', '特殊學生支持清單', '特殊需求、陪同人力與休息區安排完成', '2026-10-01', '2026-10-10', 10, 'SPORTS-011', '鎖定', 'FALSE', 2, '學生參與與安全照護不足', '中', ''),
    taskRow_('SPORTS-024', '6.2', '6.0', '籌備期', '健康中心', '護理師', '總務處', '配置醫護站', '醫護站配置與物資清單', '醫護站位置、物資與緊急通報流程確認', '2026-10-05', '2026-10-15', 11, 'SPORTS-018', '鎖定', 'TRUE', 1, '緊急事件處理延誤', '高', ''),
    taskRow_('SPORTS-025', '6.3', '6.0', '籌備期', '輔導室', '輔導主任', '家長會', '安排家長志工協助', '家長志工分工表', '志工名單、任務與報到時間確認', '2026-10-05', '2026-10-16', 12, 'SPORTS-004', '鎖定', 'FALSE', 2, '現場支援人力不足', '中', ''),

    taskRow_('SPORTS-026', '7.1', '7.0', '籌備期', '學務處', '生教組長', '教務處', '完成家長通知單', '家長通知單預覽', '通知內容含日期、集合、服裝、雨備與交通提醒', '2026-10-10', '2026-10-20', 11, 'SPORTS-009,SPORTS-022', '鎖定', 'TRUE', 0, '家長資訊不一致或公告延誤', '高', ''),
    taskRow_('SPORTS-027', '7.2', '7.0', '籌備期', '學務處', '活動組長', '校長室', '製作邀請卡與貴賓通知', '邀請卡與貴賓通知名單', '邀請對象、送達方式與回覆狀態確認', '2026-10-08', '2026-10-18', 11, 'SPORTS-005', '鎖定', 'FALSE', 2, '貴賓通知延誤', '中', ''),
    taskRow_('SPORTS-028', '7.3', '7.0', '籌備期', '學務處', '體育組長', '教務處', '完成秩序冊內容', '秩序冊 PDF 初稿', '賽程、名單、流程、工作分配完整', '2026-10-10', '2026-10-22', 13, 'SPORTS-009,SPORTS-012,SPORTS-014', '鎖定', 'TRUE', 0, '送印延誤且影響活動當日資訊', '高', ''),
    taskRow_('SPORTS-029', '7.4', '7.0', '籌備期', '學務處', '資訊組長', '教務處', '發布校網公告', '校網公告頁面', '公告含日期、流程、家長注意事項與雨備說明', '2026-10-20', '2026-10-25', 6, 'SPORTS-026', '鎖定', 'FALSE', 1, '家長無法取得正式資訊', '中', ''),
    taskRow_('SPORTS-030', '7.5', '7.0', '善後期', '學務處', '活動組長', '導師群', '規劃活動成果報告架構', '成果報告架構', '照片、活動亮點、檢討與建議欄位確認', '2026-10-20', '2026-10-30', 11, 'SPORTS-009', '鎖定', 'FALSE', 3, '活動後資料難以整理', '低', ''),

    taskRow_('SPORTS-031', '8.1', '8.0', '正日', '學務處', '體育組長', '各處室', '活動當日工作人員報到', '工作人員報到表', '工作人員完成報到並確認任務', '2026-11-14', '2026-11-14', 1, 'SPORTS-016,SPORTS-019,SPORTS-020', '鎖定', 'TRUE', 0, '活動當日任務無人執行', '高', ''),
    taskRow_('SPORTS-032', '8.2', '8.0', '正日', '學務處', '體育組長', '校長室', '執行開幕典禮', '開幕典禮完成紀錄', '依流程完成開幕、致詞、宣誓與表演', '2026-11-14', '2026-11-14', 1, 'SPORTS-006,SPORTS-017,SPORTS-020', '鎖定', 'TRUE', 0, '活動開場延誤', '高', ''),
    taskRow_('SPORTS-033', '8.3', '8.0', '正日', '學務處', '體育組長', '裁判群', '執行各項賽程', '賽程執行紀錄', '各項比賽依時間完成並記錄成績', '2026-11-14', '2026-11-14', 1, 'SPORTS-014,SPORTS-016', '鎖定', 'TRUE', 0, '賽程延誤影響整日流程', '高', ''),
    taskRow_('SPORTS-034', '8.4', '8.0', '正日', '學務處', '體育組長', '教務處', '執行頒獎流程', '頒獎紀錄', '獎項、名單與頒獎順序正確', '2026-11-14', '2026-11-14', 1, 'SPORTS-033', '鎖定', 'FALSE', 0, '獎項錯誤或流程延誤', '中', ''),
    taskRow_('SPORTS-035', '8.5', '8.0', '正日', '總務處', '事務組長', '各處室', '閉幕與場地復原', '場地復原檢查表', '場地、器材、垃圾與借用物品完成復原', '2026-11-14', '2026-11-14', 1, 'SPORTS-034', '鎖定', 'TRUE', 0, '影響後續上課與場地使用', '高', ''),

    taskRow_('SPORTS-036', '9.1', '9.0', '善後期', '總務處', '事務組長', '學務處', '完成場地設備歸還', '設備歸還紀錄', '借用物品、器材與場地清點完成', '2026-11-15', '2026-11-17', 3, 'SPORTS-035', '鎖定', 'FALSE', 2, '器材遺失或責任不清', '中', ''),
    taskRow_('SPORTS-037', '9.2', '9.0', '善後期', '總務處', '出納組長', '學務處', '完成經費核銷', '經費核銷資料', '憑證、請購與核銷資料送出', '2026-11-15', '2026-11-25', 11, 'SPORTS-035', '鎖定', 'FALSE', 3, '經費結案延誤', '中', ''),
    taskRow_('SPORTS-038', '9.3', '9.0', '善後期', '學務處', '活動組長', '導師群', '整理成果照片', '成果照片資料夾', '照片分類、授權確認與精選完成', '2026-11-15', '2026-11-24', 10, 'SPORTS-035', '鎖定', 'FALSE', 3, '成果報告素材不足', '低', ''),
    taskRow_('SPORTS-039', '9.4', '9.0', '善後期', '學務處', '學務主任', '各處室', '召開檢討會', '檢討會紀錄', '完成問題、改善建議與下年度提醒事項', '2026-11-20', '2026-11-28', 9, 'SPORTS-036,SPORTS-037,SPORTS-038', '鎖定', 'TRUE', 2, '經驗無法留存', '中', ''),
    taskRow_('SPORTS-040', '9.5', '9.0', '善後期', '學務處', '學務主任', '教務處', '建立下年度校慶模板', '下年度模板與改進清單', '完成可複製的任務模板與檢討摘要', '2026-11-25', '2026-12-05', 11, 'SPORTS-039', '鎖定', 'FALSE', 5, '隔年重新摸索', '低', '')
  ];
}

function taskRow_(taskId, wbsCode, parentWbs, stage, office, owner, supportOffice, taskName, deliverable, criteria, startDate, dueDate, duration, dependencyId, status, criticalFlag, slackDays, impact, riskLevel, note) {
  var ownerEmail = '';
  var responsibleEmail = '';
  var accountableEmail = '';
  return [
    taskId,
    wbsCode,
    parentWbs,
    stage,
    office,
    owner,
    ownerEmail,
    responsibleEmail,
    accountableEmail,
    supportOffice,
    taskName,
    deliverable,
    criteria,
    startDate,
    dueDate,
    duration,
    dependencyId,
    status,
    status === '已完成' ? '100%' : '0%',
    criticalFlag,
    slackDays,
    impact,
    riskLevel,
    '',
    '',
    '',
    '',
    '',
    '',
    0,
    '',
    '否',
    '',
    '',
    0,
    '未通知',
    '',
    '',
    '',
    '',
    new Date(),
    note || ''
  ];
}

function seedTrainingRisks_() {
  var rows = [
    ['RISK-001', '天候', '活動當日下雨，需切換雨備流程', '中', '高', '', '氣象預報降雨機率超過 60%', '活動前一天啟動雨備判斷會議', '改至室內或調整活動順序', '學務主任', 'SPORTS-022', '監控中', '研習展示用'],
    ['RISK-002', '設備', '音響或電力臨時故障', '低', '高', '', '預演時音控異常', '提前測試並準備備用設備', '借用備用麥克風與行動喇叭', '事務組長', 'SPORTS-020', '監控中', '研習展示用'],
    ['RISK-003', '溝通', '導師收到不同版本流程', '中', '中', '', '同時出現多份流程表', '統一由中控台與校網公告最新版', '主任群組公告最新版連結', '教學組長', 'SPORTS-013', '監控中', '研習展示用'],
    ['RISK-004', '人力', '裁判與工作人員臨時缺席', '中', '高', '', '活動前一天請假或未確認', '建立備援人力名單', '由學務處調度機動支援', '體育組長', 'SPORTS-016', '監控中', '研習展示用'],
    ['RISK-005', '文件', '秩序冊送印延誤', '中', '中', '', '秩序冊初稿未在期限前核定', '保留電子版公告方案', '活動當日以 QR Code 補充', '體育組長', 'SPORTS-028', '監控中', '研習展示用']
  ];
  writeRows_(SHEETS.RISK_REGISTER, rows, true);
}

function seedTrainingIssues_() {
  var rows = [
    ['ISSUE-001', '會議', '家長接送動線尚未確認', '影響活動當日校門口秩序', '高', '總務主任', '學務處', '2026-10-20', '新增', 'SPORTS-018', new Date(), '研習展示用'],
    ['ISSUE-002', '人工輸入', '部分班級參賽名單尚未繳交', '影響賽程編排與秩序冊', '中', '教學組長', '學務處', '2026-10-05', '處理中', 'SPORTS-012', new Date(), '研習展示用']
  ];
  writeRows_(SHEETS.ISSUE_TRACKER, rows, true);
}

function seedTrainingDecisions_() {
  var rows = [
    ['DEC-001', '2026-09-08', '活動主軸', '本年度以健康活力與班級合作為活動主軸', '學務處、教務處', '學務主任', 'SPORTS-005', '已決議', '', '研習展示用'],
    ['DEC-002', '2026-10-05', '雨備方案', '若活動前一日中午預報降雨機率過高，啟動雨備流程', '學務處、總務處', '校長室', 'SPORTS-022', '待確認', '', '研習展示用']
  ];
  writeRows_(SHEETS.DECISION_LOG, rows, true);
}

function seedCommunicationPlan_() {
  var rows = [
    ['COMM-001', '校慶流程公告', '全校教師', 'Email', '2026-10-20', '學務處', 'TEACHER_NOTICE', '未開始', '研習展示用'],
    ['COMM-002', '預演通知', '導師群', 'Line', '2026-10-17', '教務處', 'REHEARSAL_NOTICE', '草稿', '研習展示用'],
    ['COMM-003', '家長通知單', '家長', '紙本', '2026-10-25', '學務處', 'PARENT_NOTICE', '未開始', '研習展示用'],
    ['COMM-004', '雨備通知', '家長與導師', '校網', '2026-11-13', '學務處', 'RAIN_PLAN_NOTICE', '未開始', '研習展示用']
  ];
  writeRows_(SHEETS.COMMUNICATION_PLAN, rows, true);
}

// ===== raci_service.gs =====
function seedRaciFromTasks() {
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  var rows = tasks.map(function(task) {
    return [
      task.Task_ID,
      task.Task_Name,
      task.Owner,
      inferAccountable_(task.Office),
      inferConsulted_(task),
      inferInformed_(task),
      '由 WBS 模板同步產生，可於研習中討論修正'
    ];
  });
  writeRows_(SHEETS.RACI_MATRIX, rows, true);
  writeAuditLog('同步 RACI 預設資料', SHEETS.RACI_MATRIX, '', '', rows.length + ' 筆', '成功', '');
}

function inferAccountable_(office) {
  var map = {
    '校長室': '校長',
    '教務處': '教務主任',
    '學務處': '學務主任',
    '總務處': '總務主任',
    '輔導室': '輔導主任',
    '導師群': '教務主任',
    '健康中心': '學務主任',
    '家長會': '會長',
    '志工': '學務主任'
  };
  return map[office] || '專案管理者';
}

function inferConsulted_(task) {
  var consulted = [task.Support_Office];
  if (task.Critical_Flag === true || String(task.Critical_Flag).toUpperCase() === 'TRUE') {
    consulted.push('校長室');
  }
  return joinNonEmpty_(consulted, '、');
}

function inferInformed_(task) {
  if (task.Stage === '正日') {
    return '全校教師、導師群、家長會';
  }
  if (task.Office === '學務處') {
    return '主任群、導師群';
  }
  return '主任群';
}

// ===== milestone_service.gs =====
function seedMilestones() {
  var rows = [
    ['M1', '校慶籌備小組成立', '2026-09-03', '籌備小組名單與分工表完成', 'SPORTS-001', '未開始', ''],
    ['M2', '活動流程定稿', '2026-09-28', '活動流程總表完成並公告', 'SPORTS-006,SPORTS-007,SPORTS-008,SPORTS-009', '未開始', ''],
    ['M3', '競賽與表演項目確認', '2026-09-24', '競賽項目與表演節目完成確認', 'SPORTS-007,SPORTS-008', '未開始', ''],
    ['M4', '秩序冊送印', '2026-10-22', '秩序冊 PDF 定稿並可送印', 'SPORTS-012,SPORTS-014,SPORTS-028', '未開始', ''],
    ['M5', '預演完成', '2026-10-20', '進退場流程演練完成', 'SPORTS-010,SPORTS-011,SPORTS-017', '未開始', ''],
    ['M6', '校慶活動日', '2026-11-14', '校慶活動主要流程完成', 'SPORTS-031,SPORTS-032,SPORTS-033,SPORTS-034,SPORTS-035', '未開始', ''],
    ['M7', '結案檢討完成', '2026-12-05', '檢討會與下年度模板完成', 'SPORTS-039,SPORTS-040', '未開始', '']
  ];
  writeRows_(SHEETS.MILESTONES, rows, true);
  writeAuditLog('建立里程碑預設資料', SHEETS.MILESTONES, '', '', rows.length + ' 筆', '成功', '');
}

function updateMilestoneStatus() {
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  var taskStatusById = {};
  tasks.forEach(function(task) {
    taskStatusById[task.Task_ID] = task.Status;
  });

  var sheet = getSheetOrThrow_(SHEETS.MILESTONES);
  var milestones = readObjects_(SHEETS.MILESTONES);
  var headerMap = getHeaderMap_(sheet);
  var updates = [];
  milestones.forEach(function(milestone) {
    var relatedIds = String(milestone.Related_Task_IDs || '')
      .split(',')
      .map(function(id) { return id.trim(); })
      .filter(Boolean);
    var statuses = relatedIds.map(function(id) { return taskStatusById[id]; }).filter(Boolean);
    var nextStatus = '未開始';
    if (statuses.length > 0 && statuses.every(function(status) { return status === '已完成'; })) {
      nextStatus = '已完成';
    } else if (statuses.some(function(status) { return status === '延宕'; })) {
      nextStatus = '延宕';
    } else if (statuses.some(function(status) { return status === '進行中' || status === '待確認' || status === '已完成'; })) {
      nextStatus = '進行中';
    }
    updates.push({ row: milestone._rowNumber, status: nextStatus });
  });

  if (updates.length && headerMap.Status) {
    updates.forEach(function(update) {
      sheet.getRange(update.row, headerMap.Status).setValue(update.status);
    });
  }
  writeAuditLog('更新里程碑狀態', SHEETS.MILESTONES, '', '', updates.length + ' 筆', '成功', '');
}

// ===== risk_service.gs =====
function calculateRiskLevel(probability, impact) {
  var key = String(probability || '') + '|' + String(impact || '');
  var matrix = {
    '高|高': '極高',
    '高|中': '高',
    '高|低': '中',
    '中|高': '高',
    '中|中': '中',
    '中|低': '低',
    '低|高': '中',
    '低|中': '低',
    '低|低': '低'
  };
  return matrix[key] || '';
}

function updateRiskLevels() {
  try {
    var sheet = getSheetOrThrow_(SHEETS.RISK_REGISTER);
    var risks = readObjects_(SHEETS.RISK_REGISTER);
    var headerMap = getHeaderMap_(sheet);
    if (!headerMap.Risk_Level) {
      throw new Error('風險登錄缺少「風險等級」欄位。');
    }
    risks.forEach(function(risk) {
      var level = calculateRiskLevel(risk.Probability, risk.Impact);
      sheet.getRange(risk._rowNumber, headerMap.Risk_Level).setValue(level);
    });
    writeAuditLog('更新風險等級', SHEETS.RISK_REGISTER, '', '', risks.length + ' 筆', '成功', '');
    return risks.length;
  } catch (error) {
    writeFailureAuditLog_('更新風險等級', SHEETS.RISK_REGISTER, '', error);
    throw error;
  }
}

// ===== task_service.gs =====
function unlockDependentTasks(showResult) {
  if (showResult === undefined) {
    showResult = true;
  }
  try {
    var sheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    var tasks = readObjects_(SHEETS.MASTER_WBS);
    var headerMap = getHeaderMap_(sheet);
    var completed = {};
    tasks.forEach(function(task) {
      if (task.Status === '已完成') {
        completed[task.Task_ID] = true;
      }
    });

    var unlocked = [];
    tasks.forEach(function(task) {
      if (task.Status !== '鎖定' || !task.Dependency_ID) {
        return;
      }
      var dependencies = parseDependencyIds_(task.Dependency_ID);
      if (dependencies.length === 0) {
        return;
      }
      var allDone = dependencies.every(function(id) {
        return completed[id];
      });
      if (allDone) {
        sheet.getRange(task._rowNumber, headerMap.Status).setValue('未開始');
        sheet.getRange(task._rowNumber, headerMap.Updated_At).setValue(new Date());
        unlocked.push(task.Task_ID + ' ' + task.Task_Name);
      }
    });

    updateMilestoneStatus();
    refreshTaskReportFormAfterTaskChange_();
    updateDashboard();
    writeAuditLog('檢查任務依賴並解鎖', SHEETS.MASTER_WBS, '', '', unlocked.join('\n'), '成功', '');

    var message = unlocked.length
      ? '已解鎖任務：\n- ' + unlocked.join('\n- ')
      : '目前沒有符合條件的鎖定任務需要解鎖。';
    if (showResult) {
      alert_(message);
    }
  } catch (error) {
    writeFailureAuditLog_('檢查任務依賴並解鎖', SHEETS.MASTER_WBS, '', error);
    throw error;
  }
}

function runDailyProgressCheck() {
  try {
    var sheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    var tasks = readObjects_(SHEETS.MASTER_WBS);
    var headerMap = getHeaderMap_(sheet);
    var today = today_();
    var weekEnd = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 7);
    var delayed = [];
    var dueThisWeek = [];
    var criticalDelayed = [];
    var unreported = [];

    tasks.forEach(function(task) {
      var dueDate = normalizeDate_(task.Due_Date);
      if (!dueDate) {
        return;
      }
      var isDone = task.Status === '已完成';
      if (dueDate < today && !isDone && task.Status !== '延宕') {
        sheet.getRange(task._rowNumber, headerMap.Status).setValue('延宕');
        sheet.getRange(task._rowNumber, headerMap.Updated_At).setValue(new Date());
        if (headerMap.Reminder_Status) {
          sheet.getRange(task._rowNumber, headerMap.Reminder_Status).setValue(Number(task.Report_Count || 0) === 0 ? '未回報提醒' : '延宕提醒');
        }
        delayed.push(task.Task_ID + ' ' + task.Task_Name);
        if (isCritical_(task)) {
          criticalDelayed.push(task.Task_ID + ' ' + task.Task_Name);
        }
      } else if (dueDate < today && task.Status === '延宕' && isCritical_(task)) {
        criticalDelayed.push(task.Task_ID + ' ' + task.Task_Name);
      }
      if (dueDate >= today && dueDate <= weekEnd && !isDone) {
        dueThisWeek.push(task.Task_ID + ' ' + task.Task_Name);
        if (Number(task.Report_Count || 0) === 0 && task.Status !== '鎖定') {
          unreported.push(task.Task_ID + ' ' + task.Task_Name);
          if (headerMap.Reminder_Status) {
            sheet.getRange(task._rowNumber, headerMap.Reminder_Status).setValue('未回報提醒');
          }
        }
      }
    });

    updateMilestoneStatus();
    refreshTaskReportFormAfterTaskChange_();
    updateDashboard();
    writeAuditLog('執行每日進度檢查', SHEETS.MASTER_WBS, '', '', delayed.join('\n'), '成功', '');

    alert_([
      '每日進度檢查完成：',
      '- 本週到期任務：' + dueThisWeek.length + ' 件',
      '- 本週到期且尚未回報：' + unreported.length + ' 件',
      '- 新增延宕任務：' + delayed.length + ' 件',
      '- 關鍵任務延宕：' + criticalDelayed.length + ' 件',
      '',
      delayed.length ? '新增延宕：\n- ' + delayed.join('\n- ') : '沒有新增延宕任務。'
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('執行每日進度檢查', SHEETS.MASTER_WBS, '', error);
    throw error;
  }
}

function parseDependencyIds_(dependencyValue) {
  return String(dependencyValue || '')
    .split(',')
    .map(function(id) { return id.trim(); })
    .filter(Boolean);
}

function isCritical_(task) {
  return task.Critical_Flag === true || String(task.Critical_Flag).toUpperCase() === 'TRUE';
}

// ===== task_report_service.gs =====
function createTaskReportForm() {
  try {
    var spreadsheet = getActiveSpreadsheet_();
    var targetFolder = getActiveSpreadsheetFolder_();
    var form = FormApp.create('校慶運動會任務回報表');
    form.setDescription('請依照 Email 或行事曆提醒中的任務編號，回報目前任務進度。若任務已完成，請填寫完成狀態並附上相關文件連結。若任務卡關，請說明原因與需要協助的處室。');

    form.addListItem().setTitle(FORM_TITLES.TASK_CHOICE).setChoiceValues(['請先執行「更新任務回報表單選項」']).setRequired(true);
    form.addListItem().setTitle(FORM_TITLES.OFFICE).setChoiceValues(OPTIONS.OFFICES).setRequired(true);
    form.addTextItem().setTitle(FORM_TITLES.REPORTER).setRequired(true);
    form.addTextItem().setTitle(FORM_TITLES.REPORTER_EMAIL).setRequired(false);
    form.addMultipleChoiceItem().setTitle(FORM_TITLES.STATUS).setChoiceValues(OPTIONS.REPORT_STATUSES).setRequired(true);
    form.addMultipleChoiceItem().setTitle(FORM_TITLES.PROGRESS).setChoiceValues(OPTIONS.PROGRESS_VALUES).setRequired(true);
    form.addParagraphTextItem().setTitle(FORM_TITLES.REPORT_TEXT).setRequired(true);
    form.addParagraphTextItem().setTitle(FORM_TITLES.BLOCKER).setRequired(false);
    form.addTextItem().setTitle(FORM_TITLES.DOC_LINK).setRequired(false);
    form.addMultipleChoiceItem().setTitle(FORM_TITLES.NEED_SUPPORT).setChoiceValues(OPTIONS.YES_NO).setRequired(true);
    form.addCheckboxItem().setTitle(FORM_TITLES.SUPPORT_OFFICE).setChoiceValues(OPTIONS.SUPPORT_UNITS).setRequired(false);
    form.addDateItem().setTitle(FORM_TITLES.NEXT_DATE).setRequired(false);

    form.setDestination(FormApp.DestinationType.SPREADSHEET, spreadsheet.getId());
    var moveResult = moveFileToFolder_(form.getId(), targetFolder);

    updateTaskReportFormConfig_({
      formId: form.getId(),
      formUrl: form.getPublishedUrl(),
      formEditUrl: form.getEditUrl(),
      responseSheetName: SHEETS.TASK_REPORTS,
      folderId: moveResult.folderId,
      folderName: moveResult.folderName,
      folderUrl: moveResult.folderUrl,
      active: 'TRUE'
    });
    updateTaskReportFormChoices(false);
    generatePrefilledReportUrls(false);

    writeAuditLog('建立任務回報表單', SHEETS.TASK_REPORT_FORM_CONFIG, form.getId(), '', form.getPublishedUrl(), '成功', '');
    writeAuditLog(
      '移動表單到專案資料夾',
      SHEETS.TASK_REPORT_FORM_CONFIG,
      form.getId(),
      '',
      moveResult.folderName,
      moveResult.rootRemovalWarning ? '警告' : '成功',
      moveResult.rootRemovalWarning || ''
    );

    var warningText = moveResult.rootRemovalWarning
      ? '\n\n提醒：表單已加入專案資料夾，但系統無法確認是否已從 root 移除。這不影響表單填寫與回應收集。'
      : '';

    alert_([
      '任務回報表單已建立完成。',
      '',
      '表單名稱：校慶運動會任務回報表',
      '所在資料夾：' + moveResult.folderName,
      '表單填寫連結：',
      form.getPublishedUrl(),
      '表單編輯連結：',
      form.getEditUrl(),
      '',
      '系統已同步更新「任務選擇」下拉選單，並產生各任務的預填回報連結。',
      '',
      '提醒：表單回應將寫回目前中樞 Sheet。Google Forms 會自動建立一張表單回應工作表；研習版仍會以「任務回報」工作表作為處理回報的標準表。',
      warningText
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('建立任務回報表單', SHEETS.TASK_REPORT_FORM_CONFIG, '', error);
    throw error;
  }
}

function updateTaskReportFormChoices(showResult) {
  if (showResult === undefined) {
    showResult = true;
  }
  try {
    var formId = getTaskReportFormConfigValue_('Form_ID');
    if (!formId) {
      throw new Error('尚未建立任務回報表單，請先執行「建立任務回報表單」。');
    }
    var form = FormApp.openById(formId);
    var taskItem = getTaskChoiceListItem_(form);
    var choices = getReportableTasks_().map(buildReportChoiceLabel_);
    if (choices.length === 0) {
      choices = ['目前沒有可回報任務'];
    }
    taskItem.setChoiceValues(choices);
    writeReportChoiceLabelsToTasks_();
    writeAuditLog('更新任務回報表單選項', SHEETS.TASK_REPORT_FORM_CONFIG, formId, '', choices.length + ' 個選項', '成功', '');
    if (showResult) {
      alert_('任務回報表單選項已更新：' + choices.length + ' 個選項。');
    }
    return choices.length;
  } catch (error) {
    writeFailureAuditLog_('更新任務回報表單選項', SHEETS.TASK_REPORT_FORM_CONFIG, '', error);
    throw error;
  }
}

function generatePrefilledReportUrls(showResult) {
  if (showResult === undefined) {
    showResult = true;
  }
  try {
    var formId = getTaskReportFormConfigValue_('Form_ID');
    var formUrl = getTaskReportFormConfigValue_('Form_URL');
    if (!formId) {
      throw new Error('尚未建立任務回報表單，請先執行「建立任務回報表單」。');
    }
    updateTaskReportFormChoices(false);
    var form = FormApp.openById(formId);
    var taskItem = getTaskChoiceListItem_(form);
    var sheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    var tasks = readObjects_(SHEETS.MASTER_WBS);
    var headerMap = getHeaderMap_(sheet);
    var updated = 0;

    tasks.forEach(function(task) {
      if (!shouldIncludeTaskInReportChoices_(task.Status)) {
        setCellIfColumn_(sheet, headerMap, task._rowNumber, 'Report_Choice_Label', '');
        setCellIfColumn_(sheet, headerMap, task._rowNumber, 'Prefilled_Report_URL', '');
        if (formUrl) {
          setCellIfColumn_(sheet, headerMap, task._rowNumber, 'Report_Form_URL', formUrl);
        }
        return;
      }
      var label = task.Report_Choice_Label || buildReportChoiceLabel_(task);
      var prefilledUrl = buildPrefilledUrl_(form, taskItem, label);
      setCellIfColumn_(sheet, headerMap, task._rowNumber, 'Report_Form_URL', formUrl);
      setCellIfColumn_(sheet, headerMap, task._rowNumber, 'Report_Choice_Label', label);
      setCellIfColumn_(sheet, headerMap, task._rowNumber, 'Prefilled_Report_URL', prefilledUrl);
      updated += 1;
    });

    writeAuditLog('產生任務預填回報連結', SHEETS.MASTER_WBS, '', '', updated + ' 筆', '成功', '');
    if (showResult) {
      alert_('已產生任務預填回報連結：' + updated + ' 筆。');
    }
    return updated;
  } catch (error) {
    writeFailureAuditLog_('產生任務預填回報連結', SHEETS.MASTER_WBS, '', error);
    throw error;
  }
}

function getTaskReportFormUrl(taskId) {
  if (taskId) {
    var task = findTaskById_(taskId);
    if (task && task.Prefilled_Report_URL) {
      return task.Prefilled_Report_URL;
    }
  }
  var url = getTaskReportFormConfigValue_('Form_URL');
  if (!url) {
    return '';
  }
  return url;
}

function processTaskReports() {
  try {
    var taskSheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    var reportSheet = getSheetOrThrow_(SHEETS.TASK_REPORTS);
    var tasks = readObjects_(SHEETS.MASTER_WBS);
    var reports = readObjects_(SHEETS.TASK_REPORTS);
    var taskHeaderMap = getHeaderMap_(taskSheet);
    var reportHeaderMap = getHeaderMap_(reportSheet);
    var taskById = {};
    tasks.forEach(function(task) {
      taskById[String(task.Task_ID || '').trim()] = task;
    });

    var processed = 0;
    var completedTaskIds = [];
    var errors = [];

    reports.forEach(function(report) {
      if (String(report.Processed || '').toUpperCase() === 'TRUE') {
        return;
      }

      var taskId = parseTaskIdFromChoiceLabel_(report.Task_Choice_Label || report.Task_ID);
      var task = taskById[taskId];
      if (!task) {
        var errorMessage = taskId ? '找不到任務：' + taskId : '任務選擇欄位空白，無法解析任務 ID';
        markReportProcessed_(reportSheet, reportHeaderMap, report._rowNumber, '找不到任務', errorMessage);
        writeAuditLog('處理任務回報失敗', SHEETS.TASK_REPORTS, taskId || '', '', '', '失敗', errorMessage);
        errors.push(taskId);
        return;
      }
      setCellIfColumn_(reportSheet, reportHeaderMap, report._rowNumber, 'Task_ID', taskId);

      var reportStatus = report.Report_Status || '進行中';
      var nextStatus = mapReportStatusToTaskStatus_(reportStatus);
      var row = task._rowNumber;

      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Status', nextStatus);
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Progress_Percent', normalizeProgress_(report.Progress_Percent, reportStatus));
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Last_Report_At', report.Timestamp || new Date());
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Last_Report_By', report.Reporter || '');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Last_Report_Text', report.Report_Text || '');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Blocker', report.Blocker || '');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Need_Support', report.Need_Support || '否');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Need_Support_Office', report.Need_Support_Office || '');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Doc_Link', report.Doc_Link || task.Doc_Link || '');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Report_Count', Number(task.Report_Count || 0) + 1);
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Updated_By', report.Reporter || '');
      setCellIfColumn_(taskSheet, taskHeaderMap, row, 'Updated_At', new Date());

      if (reportStatus === '卡關') {
        appendIssueFromReport_(report, task);
      }
      if (reportStatus === '已完成') {
        completedTaskIds.push(taskId);
      }

      markReportProcessed_(reportSheet, reportHeaderMap, report._rowNumber, '成功', '');
      processed += 1;
    });

    if (completedTaskIds.length) {
      unlockDependentTasks(false);
    }
    updateDashboard();
    writeAuditLog('處理任務回報', SHEETS.TASK_REPORTS, '', '', '已處理 ' + processed + ' 筆，錯誤 ' + errors.length + ' 筆', '成功', '');
    alert_([
      '任務回報處理完成：',
      '- 成功處理：' + processed + ' 筆',
      '- 找不到任務：' + errors.length + ' 筆',
      completedTaskIds.length ? '- 已完成並觸發解鎖檢查：' + completedTaskIds.join('、') : '- 無已完成回報'
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('處理任務回報', SHEETS.TASK_REPORTS, '', error);
    throw error;
  }
}

function checkUnreportedTasks() {
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  var today = today_();
  return tasks.filter(function(task) {
    var dueDate = normalizeDate_(task.Due_Date);
    var reportCount = Number(task.Report_Count || 0);
    return reportCount === 0 && task.Status !== '已完成' && task.Status !== '鎖定' && (!dueDate || dueDate <= new Date(today.getFullYear(), today.getMonth(), today.getDate() + 7));
  });
}

function appendDemoTaskReport(taskId, reportStatus) {
  var task = findTaskById_(taskId);
  if (!task) {
    throw new Error('找不到任務：' + taskId);
  }
  var isBlocked = reportStatus === '卡關';
  var row = [
    makeId_('REPORT'),
    new Date(),
    buildReportChoiceLabel_(task),
    task.Task_ID,
    task.Office,
    '研習示範承辦人',
    '',
    reportStatus,
    reportStatus === '已完成' ? '100%' : '50%',
    isBlocked ? '音響設備已確認，但司令台旁電源不足。' : '已完成任務並確認相關資料。',
    isBlocked ? '需要確認是否可由總務處另拉延長線或調整司令台位置。' : '',
    isBlocked ? '是' : '否',
    isBlocked ? '學務處、總務處' : '',
    reportStatus === '已完成' ? 'https://example.com/demo-doc' : '',
    '',
    'FALSE',
    '',
    '',
    ''
  ];
  writeRows_(SHEETS.TASK_REPORTS, [row], false);
}

function appendDemoBlockedReport() {
  appendDemoTaskReport('SPORTS-020', '卡關');
  alert_('已新增一筆 SPORTS-020 的卡關示範回報，請接著執行「處理任務回報」。');
}

function appendDemoCompletedReport() {
  appendDemoTaskReport('SPORTS-020', '已完成');
  alert_('已新增一筆 SPORTS-020 的已完成示範回報，請接著執行「處理任務回報」。');
}

function updateTaskReportFormConfig_(config) {
  var sheet = getSheetOrThrow_(SHEETS.TASK_REPORT_FORM_CONFIG);
  var rows = readObjects_(SHEETS.TASK_REPORT_FORM_CONFIG);
  var headerMap = getHeaderMap_(sheet);
  var valueMap = {
    Form_ID: config.formId,
    Form_URL: config.formUrl,
    Form_Edit_URL: config.formEditUrl,
    Response_Sheet_Name: config.responseSheetName,
    Folder_ID: config.folderId,
    Folder_Name: config.folderName,
    Folder_URL: config.folderUrl,
    Active: config.active || 'TRUE',
    Created_At: new Date(),
    Last_Updated_At: new Date()
  };
  var descriptionMap = {
    Form_ID: '任務回報表單 ID',
    Form_URL: '任務回報表單填寫網址',
    Form_Edit_URL: '任務回報表單編輯網址',
    Response_Sheet_Name: '回報資料寫入表',
    Folder_ID: '專案資料夾 ID',
    Folder_Name: '專案資料夾名稱',
    Folder_URL: '專案資料夾網址',
    Active: '是否啟用任務回報表單',
    Created_At: '建立時間',
    Last_Updated_At: '設定最後更新時間'
  };
  var existingKeys = {};
  rows.forEach(function(row) {
    existingKeys[row.Key] = true;
    if (Object.prototype.hasOwnProperty.call(valueMap, row.Key)) {
      sheet.getRange(row._rowNumber, headerMap.Value).setValue(valueMap[row.Key]);
    }
  });
  Object.keys(valueMap).forEach(function(key) {
    if (!existingKeys[key]) {
      sheet.appendRow([key, valueMap[key], descriptionMap[key] || '']);
    }
  });
}

function getTaskReportFormConfigValue_(key) {
  var rows = readObjects_(SHEETS.TASK_REPORT_FORM_CONFIG);
  for (var i = 0; i < rows.length; i++) {
    if (rows[i].Key === key) {
      return rows[i].Value;
    }
  }
  return '';
}

function updateTaskReportUrls_() {
  var url = getTaskReportFormUrl('');
  if (!url) {
    return;
  }
  var sheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  var headerMap = getHeaderMap_(sheet);
  if (!headerMap.Report_Form_URL) {
    return;
  }
  tasks.forEach(function(task) {
    sheet.getRange(task._rowNumber, headerMap.Report_Form_URL).setValue(url);
  });
}

function refreshTaskReportFormAfterTaskChange_() {
  try {
    if (getTaskReportFormConfigValue_('Form_ID')) {
      updateTaskReportFormChoices(false);
      generatePrefilledReportUrls(false);
    }
  } catch (error) {
    writeAuditLog('更新任務回報表單選項', SHEETS.TASK_REPORT_FORM_CONFIG, '', '', '', '警告', error.message || String(error));
  }
}

function getTaskChoiceListItem_(form) {
  var items = form.getItems();
  for (var i = 0; i < items.length; i++) {
    if (items[i].getTitle() === FORM_TITLES.TASK_CHOICE) {
      return items[i].asListItem();
    }
  }
  throw new Error('找不到「' + FORM_TITLES.TASK_CHOICE + '」題目，無法更新選項或產生預填連結。');
}

function getReportableTasks_() {
  return readObjects_(SHEETS.MASTER_WBS).filter(function(task) {
    return shouldIncludeTaskInReportChoices_(task.Status);
  });
}

function shouldIncludeTaskInReportChoices_(status) {
  return ['未開始', '進行中', '待確認', '延宕', '卡關'].indexOf(String(status || '')) !== -1;
}

function buildReportChoiceLabel_(task) {
  return [
    task.Task_ID || '',
    task.Task_Name || '',
    task.Office || '',
    formatDate_(task.Due_Date)
  ].join(REPORT_CHOICE_SEPARATOR);
}

function writeReportChoiceLabelsToTasks_() {
  var sheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  var headerMap = getHeaderMap_(sheet);
  tasks.forEach(function(task) {
    if (!headerMap.Report_Choice_Label) {
      return;
    }
    var label = shouldIncludeTaskInReportChoices_(task.Status) ? buildReportChoiceLabel_(task) : '';
    sheet.getRange(task._rowNumber, headerMap.Report_Choice_Label).setValue(label);
  });
}

function buildPrefilledUrl_(form, taskItem, taskChoiceLabel) {
  var response = form.createResponse();
  var itemResponse = taskItem.createResponse(taskChoiceLabel);
  response.withItemResponse(itemResponse);
  return response.toPrefilledUrl();
}

function parseTaskIdFromChoiceLabel_(choiceLabel) {
  if (!choiceLabel) {
    return '';
  }
  var text = String(choiceLabel).trim();
  if (text.indexOf(REPORT_CHOICE_SEPARATOR) !== -1) {
    return text.split(REPORT_CHOICE_SEPARATOR)[0].trim();
  }
  return text;
}

function markReportProcessed_(sheet, headerMap, rowNumber, result, errorMessage) {
  setCellIfColumn_(sheet, headerMap, rowNumber, 'Processed', 'TRUE');
  setCellIfColumn_(sheet, headerMap, rowNumber, 'Processed_At', new Date());
  setCellIfColumn_(sheet, headerMap, rowNumber, 'Process_Result', result);
  setCellIfColumn_(sheet, headerMap, rowNumber, 'Error_Message', errorMessage || '');
}

function appendIssueFromReport_(report, task) {
  var row = [
    makeId_('ISSUE'),
    '任務回報',
    report.Blocker || '任務回報標示卡關',
    task.Impact_If_Delayed || '',
    '高',
    task.Owner || report.Reporter || '',
    report.Need_Support_Office || task.Support_Office || '',
    report.Next_Date || task.Due_Date || '',
    '新增',
    task.Task_ID,
    report.Timestamp || new Date(),
    report.Report_Text || ''
  ];
  writeRows_(SHEETS.ISSUE_TRACKER, [row], false);
}

function mapReportStatusToTaskStatus_(reportStatus) {
  if (reportStatus === '已完成') {
    return '已完成';
  }
  if (reportStatus === '待確認' || reportStatus === '卡關') {
    return '待確認';
  }
  return '進行中';
}

function normalizeProgress_(progress, reportStatus) {
  if (reportStatus === '已完成') {
    return '100%';
  }
  return progress || '0%';
}

function setCellIfColumn_(sheet, headerMap, rowNumber, fieldName, value) {
  if (headerMap[fieldName]) {
    sheet.getRange(rowNumber, headerMap[fieldName]).setValue(value);
  }
}

function findTaskById_(taskId) {
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  for (var i = 0; i < tasks.length; i++) {
    if (String(tasks[i].Task_ID) === String(taskId)) {
      return tasks[i];
    }
  }
  return null;
}

// ===== email_service.gs =====
function generateTaskEmailPreview() {
  try {
    refreshPrefilledUrlsIfPossible_();
    var tasks = readObjects_(SHEETS.MASTER_WBS);
    var candidates = tasks.filter(function(task) {
      return task.Status !== '已完成' && task.Status !== '取消';
    }).slice(0, 20);
    var rows = candidates.map(function(task) {
      var email = buildTaskEmail_(task, '派工通知');
      return [
        makeId_('EMAIL'),
        new Date(),
        '派工通知',
        task.Task_ID,
        getTaskRecipient_(task) || '未設定 Email',
        email.subject,
        email.body,
        '未寄送',
        '',
        '',
        '研習版預覽，不會自動寄送'
      ];
    });
    writeRows_(SHEETS.EMAIL_PREVIEW, rows, true);
    writeAuditLog('產生任務通知 Email 預覽', SHEETS.EMAIL_PREVIEW, '', '', rows.length + ' 筆', '成功', '');
    alert_('已產生 ' + rows.length + ' 筆 Email 預覽，請查看「Email預覽」工作表。');
  } catch (error) {
    writeFailureAuditLog_('產生任務通知 Email 預覽', SHEETS.EMAIL_PREVIEW, '', error);
    throw error;
  }
}

function sendTaskAssignmentEmails() {
  try {
    refreshPrefilledUrlsIfPossible_();
    var ui = SpreadsheetApp.getUi();
    var response = ui.alert(
      '寄送任務通知 Email（測試模式）',
      '研習版測試模式只會寄給目前登入使用者，不會寄給任務表中的承辦人。是否繼續？',
      ui.ButtonSet.YES_NO
    );
    if (response !== ui.Button.YES) {
      alert_('已取消寄送。');
      return;
    }

    var recipient = Session.getActiveUser().getEmail();
    if (!recipient) {
      alert_('無法取得目前使用者 Email，請改用 Email 預覽。');
      return;
    }

    var tasks = readObjects_(SHEETS.MASTER_WBS)
      .filter(function(task) { return task.Status !== '已完成' && task.Status !== '鎖定'; })
      .slice(0, 5);
    var taskSheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    var headerMap = getHeaderMap_(taskSheet);
    var sent = 0;

    tasks.forEach(function(task) {
      var email = buildTaskEmail_(task, '派工通知');
      MailApp.sendEmail(recipient, '[測試] ' + email.subject, email.body);
      sent += 1;
      setCellIfColumn_(taskSheet, headerMap, task._rowNumber, 'Last_Email_Sent_At', new Date());
      setCellIfColumn_(taskSheet, headerMap, task._rowNumber, 'Email_Count', Number(task.Email_Count || 0) + 1);
      setCellIfColumn_(taskSheet, headerMap, task._rowNumber, 'Reminder_Status', '已通知');
    });

    updateDashboard();
    writeAuditLog('寄送任務通知 Email（測試模式）', SHEETS.MASTER_WBS, '', '', '寄給 ' + recipient + '，共 ' + sent + ' 封', '成功', '');
    alert_('測試 Email 已寄出：' + sent + ' 封。收件人：' + recipient);
  } catch (error) {
    writeFailureAuditLog_('寄送任務通知 Email（測試模式）', SHEETS.MASTER_WBS, '', error);
    throw error;
  }
}

function sendDueAndOverdueReminders() {
  generateReminderEmailPreview_();
}

function generateReminderEmailPreview_() {
  refreshPrefilledUrlsIfPossible_();
  var tasks = readObjects_(SHEETS.MASTER_WBS);
  var today = today_();
  var threeDaysLater = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 3);
  var rows = [];
  tasks.forEach(function(task) {
    if (task.Status === '已完成' || task.Status === '鎖定') {
      return;
    }
    var dueDate = normalizeDate_(task.Due_Date);
    if (!dueDate) {
      return;
    }
    var type = '';
    if (dueDate < today) {
      type = Number(task.Report_Count || 0) === 0 ? '未回報提醒' : '延宕提醒';
    } else if (dueDate <= threeDaysLater) {
      type = '到期提醒';
    }
    if (!type) {
      return;
    }
    var email = buildTaskEmail_(task, type);
    rows.push([
      makeId_('EMAIL'),
      new Date(),
      type,
      task.Task_ID,
      getTaskRecipient_(task) || '未設定 Email',
      email.subject,
      email.body,
      '未寄送',
      '',
      '',
      '由到期與延宕提醒產生'
    ]);
  });
  writeRows_(SHEETS.EMAIL_PREVIEW, rows, true);
  writeAuditLog('產生到期與延宕提醒 Email 預覽', SHEETS.EMAIL_PREVIEW, '', '', rows.length + ' 筆', '成功', '');
  alert_('已產生 ' + rows.length + ' 筆提醒 Email 預覽。');
}

function buildTaskEmail_(task, type) {
  var formUrl = task.Prefilled_Report_URL || task.Report_Form_URL || getTaskReportFormUrl(task.Task_ID) || '尚未建立任務回報表單';
  var subjectPrefix = type === '派工通知' ? '【校慶任務派工】' : '【校慶任務提醒】';
  if (type === '未回報提醒') {
    subjectPrefix = '【校慶任務未回報】';
  } else if (type === '延宕提醒') {
    subjectPrefix = '【校慶任務延宕】';
  }

  var subject = subjectPrefix + task.Task_Name + '｜請於 ' + formatDate_(task.Due_Date) + ' 前回報';
  var body = [
    (task.Office || '承辦處室') + ' 您好：',
    '',
    '您有一項校慶運動會任務需要處理與回報。',
    '',
    '任務編號：' + (task.Task_ID || ''),
    '任務名稱：' + (task.Task_Name || ''),
    '主責處室：' + (task.Office || ''),
    '主責人：' + (task.Owner || ''),
    '交付物：' + (task.Deliverable || ''),
    '完成判準：' + (task.Acceptance_Criteria || ''),
    '截止日期：' + formatDate_(task.Due_Date),
    '是否關鍵任務：' + (isCritical_(task) ? '是' : '否'),
    '目前狀態：' + (task.Status || ''),
    '最近回報：' + (task.Last_Report_At ? formatDate_(task.Last_Report_At) : '尚未回報'),
    '',
    '請點選下方連結回報此任務：',
    formUrl,
    '',
    '若已完成，請回報「已完成」並附上文件連結。',
    '若尚未完成，請回報目前進度。',
    '若遇到困難，請選擇「卡關」並說明需要協助事項。',
    '',
    '校慶中控台系統'
  ].join('\n');
  return { subject: subject, body: body };
}

function getTaskRecipient_(task) {
  return task.Owner_Email || task.Responsible_Email || '';
}

function refreshPrefilledUrlsIfPossible_() {
  try {
    if (getTaskReportFormConfigValue_('Form_ID')) {
      updateTaskReportFormChoices(false);
      generatePrefilledReportUrls(false);
    }
  } catch (error) {
    writeAuditLog('更新預填回報連結', SHEETS.MASTER_WBS, '', '', '', '警告', error.message || String(error));
  }
}

// ===== dashboard_service.gs =====
function updateDashboard() {
  try {
    updateRiskLevels();
  } catch (error) {
    // 初始化早期可能尚未建立風險登錄，儀表板仍可先產生。
  }

  var projectName = getProjectValue_('Project_Name');
  var eventDate = getProjectValue_('Event_Date');
  var tasks = safeReadObjects_(SHEETS.MASTER_WBS);
  var risks = safeReadObjects_(SHEETS.RISK_REGISTER);
  var decisions = safeReadObjects_(SHEETS.DECISION_LOG);
  var milestones = safeReadObjects_(SHEETS.MILESTONES);
  var reports = safeReadObjects_(SHEETS.TASK_REPORTS);
  var issues = safeReadObjects_(SHEETS.ISSUE_TRACKER);
  var outcomes = safeReadObjects_(SHEETS.OUTCOME_SUBMISSIONS);
  var knowledgeItems = safeReadObjects_(SHEETS.AI_KNOWLEDGE_INDEX);
  var notifications = safeReadObjects_(SHEETS.NOTIFICATION_QUEUE);

  var totalTasks = tasks.length;
  var completedTasks = countBy_(tasks, function(task) { return task.Status === '已完成'; });
  var inProgressTasks = countBy_(tasks, function(task) { return task.Status === '進行中' || task.Status === '待確認'; });
  var delayedTasks = countBy_(tasks, function(task) { return task.Status === '延宕'; });
  var criticalTasks = countBy_(tasks, isCritical_);
  var criticalDelayed = countBy_(tasks, function(task) { return isCritical_(task) && task.Status === '延宕'; });
  var highRisks = risks.filter(function(risk) { return risk.Risk_Level === '高' || risk.Risk_Level === '極高'; });
  var pendingDecisions = decisions.filter(function(decision) { return decision.Status === '待確認' || decision.Status === '需再議'; });
  var openIssues = issues.filter(function(issue) { return issue.Status !== '已排除' && issue.Status !== '轉決議'; });
  var dueThisWeek = getDueThisWeek_(tasks);
  var redTasks = tasks.filter(function(task) { return task.Status === '延宕' && isCritical_(task); });
  var reportedTasks = tasks.filter(function(task) { return Number(task.Report_Count || 0) > 0; });
  var unreportedTasks = tasks.filter(function(task) { return task.Status !== '已完成' && task.Status !== '鎖定' && Number(task.Report_Count || 0) === 0; });
  var blockedTasks = tasks.filter(function(task) { return task.Blocker || task.Status === '待確認'; });
  var supportTasks = tasks.filter(function(task) { return task.Need_Support === '是'; });
  var overdueUnreported = getOverdueUnreportedTasks_(tasks);
  var reportsThisWeek = getReportsThisWeek_(reports);
  var approvedOutcomes = countBy_(outcomes, function(item) { return item.Review_Status === '通過'; });
  var aiIncludedOutcomes = countBy_(outcomes, function(item) { return item.Include_In_AI_Knowledge === '是'; });
  var pendingNotebookItems = countBy_(knowledgeItems, function(item) { return item.NotebookLM_Status === '待匯入' || item.NotebookLM_Status === '未匯入'; });
  var pendingNotifications = countBy_(notifications, function(item) { return item.Status === '待寄送'; });

  var rows = [
    ['專案資訊', '專案名稱', projectName || '', ''],
    ['專案資訊', '活動日期', eventDate || '', ''],
    ['燈號', '整體狀態', '🟢 正常', '依任務進度、風險狀態、協作溝通自動判斷'],
    ['燈號', '任務進度', '🟢 正常', '依延宕任務數與關鍵任務延宕數自動判斷'],
    ['燈號', '風險狀態', '🟢 正常', '依高風險與極高風險項目數自動判斷'],
    ['燈號', '協作溝通', '🟢 正常', '依待決議事項與問題追蹤狀態自動判斷'],
    ['任務總覽', '總任務數', totalTasks, ''],
    ['任務總覽', '已完成任務數', completedTasks, ''],
    ['任務總覽', '完成率', totalTasks ? Math.round(completedTasks / totalTasks * 100) + '%' : '0%', ''],
    ['任務總覽', '進行中任務數', inProgressTasks, ''],
    ['任務總覽', '延宕任務數', delayedTasks, ''],
    ['任務總覽', '關鍵任務數', criticalTasks, ''],
    ['任務總覽', '關鍵任務延宕數', criticalDelayed, ''],
    ['回報追蹤', '已有回報任務數', reportedTasks.length, ''],
    ['回報追蹤', '尚未回報任務數', unreportedTasks.length, unreportedTasks.slice(0, 10).map(function(task) { return task.Task_ID + ' ' + task.Task_Name + '｜' + task.Office + '｜' + formatDate_(task.Due_Date); }).join('\n')],
    ['回報追蹤', '本週新增回報', reportsThisWeek.length, reportsThisWeek.slice(0, 10).map(function(report) { return report.Task_ID + '｜' + report.Reporter + '｜' + report.Report_Status; }).join('\n')],
    ['回報追蹤', '卡關任務', blockedTasks.length, blockedTasks.slice(0, 10).map(function(task) { return task.Task_ID + ' ' + task.Task_Name + '｜' + (task.Blocker || '待確認'); }).join('\n')],
    ['回報追蹤', '需要跨處室協助', supportTasks.length, supportTasks.slice(0, 10).map(function(task) { return task.Task_ID + ' ' + task.Task_Name + '｜需協助：' + (task.Need_Support_Office || '未指定'); }).join('\n')],
    ['回報追蹤', '逾期且未回報任務', overdueUnreported.length, overdueUnreported.slice(0, 10).map(function(task) { return task.Task_ID + ' ' + task.Task_Name + '｜' + formatDate_(task.Due_Date) + '｜通知 ' + Number(task.Email_Count || 0) + ' 次'; }).join('\n')],
    ['成果管理', '成果資料數', outcomes.length, outcomes.slice(0, 10).map(function(item) { return item.Outcome_ID + '｜' + item.Outcome_Type + '｜' + item.Review_Status; }).join('\n')],
    ['成果管理', '成果通過件數', approvedOutcomes, outcomes.length ? '通過率 ' + Math.round(approvedOutcomes / outcomes.length * 100) + '%' : '尚無成果資料'],
    ['AI知識庫', '索引資料數', knowledgeItems.length, knowledgeItems.slice(0, 10).map(function(item) { return item.Knowledge_ID + '｜' + item.Title + '｜' + item.NotebookLM_Status; }).join('\n')],
    ['AI知識庫', '待匯入資料數', pendingNotebookItems, '含未匯入與待匯入項目'],
    ['AI知識庫', '成果納入知識庫件數', aiIncludedOutcomes, ''],
    ['通知文件', '待處理通知數', pendingNotifications, notifications.slice(0, 10).map(function(item) { return item.Notification_ID + '｜' + item.Notification_Type + '｜' + item.Recipient + '｜' + item.Status; }).join('\n')],
    ['風險總覽', '高風險與極高風險項目', highRisks.length, highRisks.map(function(risk) { return risk.Risk_ID + ' ' + risk.Risk_Description; }).join('\n')],
    ['協商總覽', '待決議事項', pendingDecisions.length, pendingDecisions.map(function(decision) { return decision.Decision_ID + ' ' + decision.Topic; }).join('\n')],
    ['問題追蹤', '未解決問題數', openIssues.length, openIssues.map(function(issue) { return issue.Issue_ID + ' ' + issue.Issue_Text; }).join('\n')],
    ['本週任務', '本週到期任務', dueThisWeek.length, dueThisWeek.map(function(task) { return task.Task_ID + ' ' + task.Task_Name + '（' + formatDate_(task.Due_Date) + '）'; }).join('\n')],
    ['紅燈任務', '關鍵且延宕', redTasks.length, redTasks.map(function(task) { return task.Task_ID + ' ' + task.Task_Name; }).join('\n')]
  ];

  buildOfficeProgressRows_(tasks).forEach(function(row) {
    rows.push(row);
  });
  milestones.forEach(function(milestone) {
    rows.push(['里程碑', milestone.Milestone_ID + ' ' + milestone.Milestone_Name, milestone.Status || '', milestone.Completion_Criteria || '']);
  });

  writeRows_(SHEETS.DASHBOARD, rows, true);
  formatDashboard_();
  updateDashboardSignalLights_();
  writeAuditLog('更新儀表板', SHEETS.DASHBOARD, '', '', rows.length + ' 列', '成功', '');
  showToast_('儀表板已更新。');
} 

function updateDashboardSignalLights_() {
  var sheet = getSheetOrThrow_(SHEETS.DASHBOARD);
  var rows = readObjects_(SHEETS.DASHBOARD);
  var headerMap = getHeaderMap_(sheet);
  if (!headerMap.Section || !headerMap.Item || !headerMap.Value || !headerMap.Description) {
    return;
  }
  var metrics = getDashboardMetricMap_(rows);
  var delayedTasks = Number(metrics['任務總覽|延宕任務數'] || 0);
  var criticalDelayedTasks = Number(metrics['任務總覽|關鍵任務延宕數'] || 0);
  var highRisks = Number(metrics['風險總覽|高風險與極高風險項目'] || 0);
  var pendingDecisions = Number(metrics['協商總覽|待決議事項'] || 0);
  var openIssues = Number(metrics['問題追蹤|未解決問題數'] || 0);
  var taskSignal = buildSignalLight_(
    criticalDelayedTasks > 0 || delayedTasks >= 3,
    delayedTasks > 0,
    '關鍵任務延宕 ' + criticalDelayedTasks + ' 件；延宕任務 ' + delayedTasks + ' 件'
  );
  var riskSignal = buildSignalLight_(
    highRisks >= 3,
    highRisks > 0,
    '高風險與極高風險項目 ' + highRisks + ' 件'
  );
  var collaborationSignal = buildSignalLight_(
    pendingDecisions >= 3 || openIssues >= 3,
    pendingDecisions > 0 || openIssues > 0,
    '待決議事項 ' + pendingDecisions + ' 件；未解決問題 ' + openIssues + ' 件'
  );
  var overallSignal = buildOverallSignalLight_([taskSignal, riskSignal, collaborationSignal]);
  var signalMap = {
    '整體狀態': overallSignal,
    '任務進度': taskSignal,
    '風險狀態': riskSignal,
    '協作溝通': collaborationSignal
  };
  rows.forEach(function(row) {
    if (row.Section !== '燈號') {
      return;
    }
    var signal = signalMap[row.Item];
    if (!signal) {
      return;
    }
    sheet.getRange(row._rowNumber, headerMap.Value).setValue(signal.label);
    sheet.getRange(row._rowNumber, headerMap.Description).setValue(signal.description);
    applyDashboardSignalRowStyle_(sheet, row._rowNumber, signal.level);
  });
}

function getDashboardMetricMap_(rows) {
  var map = {};
  rows.forEach(function(row) {
    map[String(row.Section || '') + '|' + String(row.Item || '')] = row.Value;
  });
  return map;
}

function buildSignalLight_(isRed, isYellow, description) {
  if (isRed) {
    return {
      level: 'red',
      label: '🔴 需立即處理',
      description: description
    };
  }
  if (isYellow) {
    return {
      level: 'yellow',
      label: '🟡 需要留意',
      description: description
    };
  }
  return {
    level: 'green',
    label: '🟢 正常',
    description: description
  };
}

function buildOverallSignalLight_(signals) {
  var hasRed = signals.some(function(signal) {
    return signal.level === 'red';
  });
  var hasYellow = signals.some(function(signal) {
    return signal.level === 'yellow';
  });
  if (hasRed) {
    return {
      level: 'red',
      label: '🔴 需立即處理',
      description: '任務、風險或協作燈號已有紅燈，建議優先處理阻塞項目。'
    };
  }
  if (hasYellow) {
    return {
      level: 'yellow',
      label: '🟡 需要留意',
      description: '任務、風險或協作燈號已有黃燈，建議追蹤近期變化。'
    };
  }
  return {
    level: 'green',
    label: '🟢 正常',
    description: '目前任務、風險與協作狀態未達警示門檻。'
  };
}

function applyDashboardSignalRowStyle_(sheet, rowNumber, level) {
  var range = sheet.getRange(rowNumber, 1, 1, sheet.getLastColumn());
  if (level === 'red') {
    range.setBackground('#f4cccc').setFontColor('#990000').setFontWeight('bold');
    return;
  }
  if (level === 'yellow') {
    range.setBackground('#fff2cc').setFontColor('#7f6000').setFontWeight('bold');
    return;
  }
  range.setBackground('#d9ead3').setFontColor('#274e13').setFontWeight('bold');
}

function safeReadObjects_(sheetName) {
  try {
    return readObjects_(sheetName);
  } catch (error) {
    return [];
  }
}

function getOverdueUnreportedTasks_(tasks) {
  var today = today_();
  return tasks.filter(function(task) {
    var dueDate = normalizeDate_(task.Due_Date);
    return dueDate && dueDate < today && task.Status !== '已完成' && Number(task.Report_Count || 0) === 0;
  });
}

function getReportsThisWeek_(reports) {
  var today = today_();
  var weekStart = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 7);
  return reports.filter(function(report) {
    var timestamp = normalizeDate_(report.Timestamp);
    return timestamp && timestamp >= weekStart;
  });
}

function countBy_(items, predicate) {
  return items.filter(predicate).length;
}

function sumNumberBy_(items, fieldName) {
  return items.reduce(function(sum, item) {
    var value = Number(item[fieldName] || 0);
    return sum + (isNaN(value) ? 0 : value);
  }, 0);
}

function getDueThisWeek_(tasks) {
  var today = today_();
  var weekEnd = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 7);
  return tasks.filter(function(task) {
    var dueDate = normalizeDate_(task.Due_Date);
    return dueDate && dueDate >= today && dueDate <= weekEnd && task.Status !== '已完成';
  });
}

function buildOfficeProgressRows_(tasks) {
  var officeMap = {};
  tasks.forEach(function(task) {
    var office = task.Office || '未指定';
    if (!officeMap[office]) {
      officeMap[office] = { total: 0, done: 0, delayed: 0 };
    }
    officeMap[office].total += 1;
    if (task.Status === '已完成') {
      officeMap[office].done += 1;
    }
    if (task.Status === '延宕') {
      officeMap[office].delayed += 1;
    }
  });
  return Object.keys(officeMap).sort().map(function(office) {
    var stats = officeMap[office];
    var rate = stats.total ? Math.round(stats.done / stats.total * 100) + '%' : '0%';
    return ['處室進度', office, rate, '任務 ' + stats.total + ' 件，已完成 ' + stats.done + ' 件，延宕 ' + stats.delayed + ' 件'];
  });
}

function formatDashboard_() {
  var sheet = getSheetOrThrow_(SHEETS.DASHBOARD);
  sheet.autoResizeColumns(1, Math.min(sheet.getLastColumn(), 4));
  sheet.getRange(1, 1, Math.max(1, sheet.getLastRow()), sheet.getLastColumn()).setWrap(true);
  if (sheet.getLastRow() >= DATA_START_ROW) {
    sheet.getRange(DATA_START_ROW, 1, sheet.getLastRow() - 1, 1).setFontWeight('bold').setBackground('#d9eaf7');
  }
}

// ===== document_service.gs =====
function generateParentNoticePreview() {
  try {
    var projectName = getProjectValue_('Project_Name') || '校慶運動會';
    var schoolName = getProjectValue_('School_Name') || '範例國小';
    var eventDate = getProjectValue_('Event_Date') || '';
    var gatherTime = getProjectValue_('Notice_Gather_Time') || '上午 7:50';
    var dressCode = getProjectValue_('Notice_Dress_Code') || '運動服與運動鞋';
    var rainPlan = getProjectValue_('Rain_Plan') || '依學校雨備流程及最新公告辦理';

    var template = [
      '親愛的家長您好：',
      '',
      '本校將於 {{活動日期}} 舉辦 {{活動名稱}}。',
      '當日學生集合時間為 {{集合時間}}，請穿著 {{服裝規定}}。',
      '若遇雨備情形，將依 {{雨備說明}} 辦理。',
      '',
      '敬請家長協助提醒孩子準時到校，並注意交通安全。',
      '{{學校名稱}} 敬上'
    ].join('\n');

    var content = template
      .replace('{{活動日期}}', eventDate)
      .replace('{{活動名稱}}', projectName)
      .replace('{{集合時間}}', gatherTime)
      .replace('{{服裝規定}}', dressCode)
      .replace('{{雨備說明}}', rainPlan)
      .replace('{{學校名稱}}', schoolName);

    var row = [
      makeId_('DOC'),
      '家長通知單',
      new Date(),
      'PARENT_NOTICE',
      content,
      '',
      '展示版預設只產生工作表預覽，不大量建立 Google Docs'
    ];
    writeRows_(SHEETS.GENERATED_DOCS_PREVIEW, [row], false);
    writeAuditLog('產生家長通知單預覽', SHEETS.GENERATED_DOCS_PREVIEW, row[0], '', '家長通知單預覽', '成功', '');
    alert_('已產生家長通知單預覽，請查看「文件預覽」工作表。');
  } catch (error) {
    writeFailureAuditLog_('產生家長通知單預覽', SHEETS.GENERATED_DOCS_PREVIEW, '', error);
    throw error;
  }
}

// ===== advanced_platform_service.gs =====
function setupAdvancedActivityDemo() {
  try {
    ensureAdvancedPlatformSheets_();
    seedSystemCodeTable_();
    seedAdvancedProjectCase_();
    seedAdvancedOutcomes_();
    buildAiKnowledgeIndex();
    generateNotificationQueue();
    updateDashboard();
    writeAuditLog('建立進階展示資料', 'ALL', '', '', '建案、成果、通知、AI知識庫索引', '成功', '');
    alert_([
      '進階展示資料已建立完成。',
      '',
      '已建立或更新：',
      '- 活動建案表',
      '- 成果資料',
      '- 通知佇列',
      '- AI知識庫索引',
      '- 儀表板與燈號',
      '',
      '下一步可執行：「建立專案資料夾架構」。'
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('建立進階展示資料', 'ALL', '', error);
    throw error;
  }
}

function createAdvancedProjectFolderStructure() {
  try {
    ensureAdvancedPlatformSheets_();
    var projectId = getAdvancedProjectId_();
    var projectName = getProjectValue_('Project_Name') || '活動型行政專案';
    var parentFolder = getActiveSpreadsheetFolder_();
    var rootFolder = getOrCreateChildFolder_(parentFolder, projectName + '_' + projectId);
    var folderSpecs = getAdvancedFolderSpecs_();
    var rows = folderSpecs.map(function(spec) {
      var folder = getOrCreateChildFolder_(rootFolder, spec.name);
      return [
        spec.code,
        projectId,
        spec.name,
        validateCategory_(spec.purpose),
        rootFolder.getName(),
        folder.getId(),
        folder.getUrl(),
        new Date(),
      '運動會專案展示版自動建立'
      ];
    });
    rows.unshift([
      '00_ROOT',
      projectId,
      rootFolder.getName(),
      validateCategory_('專案建案資料'),
      parentFolder.getName(),
      rootFolder.getId(),
      rootFolder.getUrl(),
      new Date(),
      '專案根資料夾'
    ]);
    writeRows_(SHEETS.PROJECT_FOLDERS, rows, true);
    updateProjectCaseField_('Project_Folder_URL', rootFolder.getUrl());
    writeAuditLog('建立專案資料夾架構', SHEETS.PROJECT_FOLDERS, projectId, '', rootFolder.getUrl(), '成功', '');
    alert_([
      '專案資料夾架構已建立。',
      '',
      '根資料夾：' + rootFolder.getName(),
      rootFolder.getUrl(),
      '',
      '已寫入「專案資料夾索引」。'
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('建立專案資料夾架構', SHEETS.PROJECT_FOLDERS, '', error);
    throw error;
  }
}

function createAdvancedRegistrationForm() {
  try {
    var projectId = getAdvancedProjectId_();
    var projectName = getProjectValue_('Project_Name') || '活動';
    var form = FormApp.create(projectName + '報名表');
    form.setDescription('進階展示用報名表。表單回應會寫回目前中控台試算表，正式版可再串接名冊正規化流程。');
    form.addTextItem().setTitle('姓名').setRequired(true);
    form.addTextItem().setTitle('單位').setRequired(true);
    form.addTextItem().setTitle('Email').setRequired(true);
    form.addListItem().setTitle('身分').setChoiceValues(['學生', '教師', '家長', '志工', '工作人員']).setRequired(true);
    form.addListItem().setTitle('場次名稱').setChoiceValues(['上午主場活動', '下午成果分享', '全日參與']).setRequired(true);
    form.addParagraphTextItem().setTitle('備註').setRequired(false);
    form.setDestination(FormApp.DestinationType.SPREADSHEET, getActiveSpreadsheet_().getId());

    var targetFolder = getAdvancedFolderByCode_('03_TASK') || getActiveSpreadsheetFolder_();
    var moveResult = moveFileToFolder_(form.getId(), targetFolder);
    updateProjectCaseField_('Registration_Form_URL', form.getPublishedUrl());
    writeAuditLog('建立報名表單', SHEETS.PROJECT_CASES, projectId, '', form.getPublishedUrl(), '成功', '');
    alert_([
      '報名表單已建立。',
      '',
      '表單填寫連結：',
      form.getPublishedUrl(),
      '',
      '表單編輯連結：',
      form.getEditUrl(),
      '',
      '所在資料夾：' + moveResult.folderName
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('建立報名表單', SHEETS.PROJECT_CASES, '', error);
    throw error;
  }
}

function createAdvancedOutcomeForm() {
  try {
    var projectId = getAdvancedProjectId_();
    var projectName = getProjectValue_('Project_Name') || '活動';
    var form = FormApp.create(projectName + '成果收件表');
    form.setDescription('進階展示用成果收件表。正式版可啟用檔案上傳與審核流程；展示版先收集連結與回饋。');
    form.addTextItem().setTitle('提交者').setRequired(true);
    form.addListItem().setTitle('成果類型').setChoiceValues(OPTIONS.OUTCOME_TYPES).setRequired(true);
    form.addTextItem().setTitle('檔案連結').setRequired(false);
    form.addParagraphTextItem().setTitle('回饋').setRequired(false);
    form.addMultipleChoiceItem().setTitle('是否納入AI知識庫').setChoiceValues(OPTIONS.YES_NO).setRequired(true);
    form.setDestination(FormApp.DestinationType.SPREADSHEET, getActiveSpreadsheet_().getId());

    var targetFolder = getAdvancedFolderByCode_('07_RESULT') || getActiveSpreadsheetFolder_();
    var moveResult = moveFileToFolder_(form.getId(), targetFolder);
    updateProjectCaseField_('Outcome_Form_URL', form.getPublishedUrl());
    writeAuditLog('建立成果收件表單', SHEETS.PROJECT_CASES, projectId, '', form.getPublishedUrl(), '成功', '');
    alert_([
      '成果收件表單已建立。',
      '',
      '表單填寫連結：',
      form.getPublishedUrl(),
      '',
      '表單編輯連結：',
      form.getEditUrl(),
      '',
      '所在資料夾：' + moveResult.folderName
    ].join('\n'));
  } catch (error) {
    writeFailureAuditLog_('建立成果收件表單', SHEETS.PROJECT_CASES, '', error);
    throw error;
  }
}

function appendDemoCheckins() {
  if (!SHEETS.ATTENDANCE_MANAGEMENT) {
    alert_('簽到管理已不屬於校慶運動會展示版選單流程。');
    return;
  }
  try {
    var attendanceSheet = getSheetOrThrow_(SHEETS.ATTENDANCE_MANAGEMENT);
    var headerMap = getHeaderMap_(attendanceSheet);
    var rows = readObjects_(SHEETS.ATTENDANCE_MANAGEMENT);
    var checked = 0;
    rows.forEach(function(row, index) {
      if (index > 4 || row.Attendance_Status === '已簽到') {
        return;
      }
      setCellIfColumn_(attendanceSheet, headerMap, row._rowNumber, 'Attendance_Status', '已簽到');
      setCellIfColumn_(attendanceSheet, headerMap, row._rowNumber, 'Checked_In_At', new Date());
      setCellIfColumn_(attendanceSheet, headerMap, row._rowNumber, 'Checkin_Method', '講師示範');
      checked += 1;
    });
    updateRosterAttendanceFromCheckins_();
    updateDashboard();
    writeAuditLog('新增示範簽到', SHEETS.ATTENDANCE_MANAGEMENT, '', '', checked + ' 筆', '成功', '');
    alert_('已新增示範簽到：' + checked + ' 筆。');
  } catch (error) {
    writeFailureAuditLog_('新增示範簽到', SHEETS.ATTENDANCE_MANAGEMENT, '', error);
    throw error;
  }
}

function buildAiKnowledgeIndex() {
  var projectId = getAdvancedProjectId_();
  var projectName = getProjectValue_('Project_Name') || '活動專案';
  var outcomes = safeReadObjects_(SHEETS.OUTCOME_SUBMISSIONS);
  var rows = buildAdvancedDemoKnowledgeRows_(projectId, projectName);
  var nextIndex = rows.length + 1;

  outcomes.forEach(function(outcome, index) {
    if (outcome.Include_In_AI_Knowledge !== '是') {
      return;
    }
    rows.push([
      'AIK-' + padNumber_(nextIndex + index, 3),
      projectId,
      classifyOutcomeCategory_(outcome.Outcome_Type),
      (outcome.Outcome_Type || '成果') + '｜' + (outcome.Submitter || ''),
      outcome.Feedback || '待補摘要',
      outcome.File_Link || '',
      '待匯入',
      '成果報告、新聞稿、下次活動改善建議',
      buildAiPrompt_('請根據此成果資料，整理亮點、問題與下次改善建議。'),
      new Date(),
      '由成果資料產生'
    ]);
  });

  writeRows_(SHEETS.AI_KNOWLEDGE_INDEX, rows, true);
  writeAuditLog('建立AI知識庫索引', SHEETS.AI_KNOWLEDGE_INDEX, projectId, '', rows.length + ' 筆', '成功', '');
  return rows.length;
}

function refreshAiKnowledgeIndex() {
  try {
    var count = buildAiKnowledgeIndex();
    updateDashboard();
    alert_('AI 知識庫索引已更新：' + count + ' 筆。');
  } catch (error) {
    writeFailureAuditLog_('更新AI知識庫索引', SHEETS.AI_KNOWLEDGE_INDEX, '', error);
    throw error;
  }
}

function buildAdvancedDemoKnowledgeRows_(projectId, projectName) {
  var specs = [
    ['專案建案資料', '活動企劃書', '活動目標、參與對象、時程與行政分工摘要。'],
    ['專案建案資料', '執行計畫', '跨處室任務、工作期程、資源需求與風險控管計畫。'],
    ['專案建案資料', '預算表', '活動經費、保險、物資與採購項目彙整。'],
    ['會議紀錄與決議', '籌備會議紀錄', '工作會議決議、待辦事項與責任歸屬摘要。'],
    ['場地與動線資料', '場地配置圖', '司令台、班級位置、比賽場地與動線規劃。'],
    ['場地與動線資料', '雨備動線', '雨天集合、撤場、家長接送與安全管制動線。'],
    ['WBS任務附件', '工作手冊', '各組工作規範、檢核表與現場注意事項。'],
    ['WBS任務附件', '裁判手冊', '賽程規則、裁判分工與紀錄方式。'],
    ['WBS任務附件', '分工表', '人員分組、責任區域與聯絡窗口。'],
    ['成果報告', '成果照片', '活動照片分類、檔案連結與照片說明。'],
    ['成果報告', '成果簡報', '活動亮點、流程照片與成果摘要。'],
    ['成果報告', '成果報告', '執行成果、問題檢討與後續建議。'],
    ['AI知識庫素材', '活動摘要', '供 AI 產生活動簡介與對外說明的摘要資料。'],
    ['AI知識庫素材', '會議摘要', '工作會議決議、待辦事項與責任歸屬摘要。'],
    ['AI知識庫素材', '成果摘要', '成果報告重點、照片說明與改善建議摘要。'],
    ['AI知識庫素材', '新聞稿草稿', '可供校網公告或新聞稿初稿使用的資料。']
  ];

  return specs.map(function(spec, index) {
    var category = validateCategory_(spec[0]);
    return [
      'AIK-' + padNumber_(index + 1, 3),
      projectId,
      category,
      projectName + '｜' + spec[1],
      spec[2],
      spec[1] === '活動企劃書' ? getProjectCaseUrl_() : '',
      '待匯入',
      'NotebookLM 資料來源、結案報告、新聞稿、下次活動改善建議',
      buildAiPrompt_('請根據「' + spec[1] + '」整理摘要、重點發現與後續建議。'),
      new Date(),
      '進階展示資料：' + spec[1]
    ];
  });
}

function classifyOutcomeCategory_(outcomeType) {
  var map = {
    成果報告: '成果報告',
    活動照片: '活動照片',
    學員回饋: '成果報告',
    簽到紀錄: '成果報告',
    經費結算: '專案建案資料',
    新聞稿: 'AI知識庫素材',
    其他: '其他'
  };
  return validateCategory_(map[outcomeType] || outcomeType || '其他');
}

function generateActivityClosureDraftPreview() {
  try {
    var projectName = getProjectValue_('Project_Name') || '活動專案';
    var eventDate = getProjectValue_('Event_Date') || '';
    var tasks = safeReadObjects_(SHEETS.MASTER_WBS);
    var issues = safeReadObjects_(SHEETS.ISSUE_TRACKER);
    var decisions = safeReadObjects_(SHEETS.DECISION_LOG);
    var outcomes = safeReadObjects_(SHEETS.OUTCOME_SUBMISSIONS);
    var risks = safeReadObjects_(SHEETS.RISK_REGISTER);
    var completedTasks = countBy_(tasks, function(task) { return task.Status === '已完成'; });
    var delayedTasks = countBy_(tasks, function(task) { return task.Status === '延宕'; });
    var openIssues = countBy_(issues, function(issue) { return issue.Status !== '已排除' && issue.Status !== '轉決議'; });
    var pendingDecisions = countBy_(decisions, function(decision) { return decision.Status === '待確認' || decision.Status === '需再議'; });
    var approvedOutcomes = countBy_(outcomes, function(item) { return item.Review_Status === '通過'; });
    var highRisks = risks.filter(function(risk) { return risk.Risk_Level === '高' || risk.Risk_Level === '極高'; });

    var content = [
      projectName + '｜活動結案報告草稿',
      '',
      '一、活動基本資料',
      '活動日期：' + eventDate,
      '主辦單位：' + (getProjectValue_('Host_Unit') || getProjectValue_('主辦單位') || ''),
      '',
      '二、執行摘要',
      '本次活動以 Google Sheets 中控台整合建案、WBS 任務、回報、風險問題、通知文件與成果資料。任務完成率為 ' + (tasks.length ? Math.round(completedTasks / tasks.length * 100) + '%' : '0%') + '，目前延宕任務 ' + delayedTasks + ' 件，待決議事項 ' + pendingDecisions + ' 件，未解決問題 ' + openIssues + ' 件。',
      '',
      '三、成果彙整',
      '成果資料共 ' + outcomes.length + ' 件，其中審核通過 ' + approvedOutcomes + ' 件。可納入 AI 知識庫的成果會同步列入「AI知識庫索引」。',
      '',
      '四、風險與改善建議',
      highRisks.length ? highRisks.map(function(risk) { return '- ' + risk.Risk_Description + '：' + risk.Response_Strategy; }).join('\n') : '目前無高風險或極高風險項目。',
      '',
      '五、AI後續處理提示詞',
      buildAiPrompt_('請根據本活動中控台資料，產生正式結案報告、新聞稿與下次活動改善建議。')
    ].join('\n');

    var row = [
      makeId_('DOC'),
      '活動結案報告草稿',
      new Date(),
      'ADVANCED_CLOSURE_DRAFT',
      content,
      '',
      '進階展示版：產生可貼入 Gemini 或 NotebookLM 的結案草稿'
    ];
    writeRows_(SHEETS.GENERATED_DOCS_PREVIEW, [row], false);
    writeAuditLog('產生活動結案報告草稿', SHEETS.GENERATED_DOCS_PREVIEW, row[0], '', '活動結案報告草稿', '成功', '');
    alert_('已產生活動結案報告草稿，請查看「文件預覽」工作表。');
  } catch (error) {
    writeFailureAuditLog_('產生活動結案報告草稿', SHEETS.GENERATED_DOCS_PREVIEW, '', error);
    throw error;
  }
}

function ensureAdvancedPlatformSheets_() {
  var spreadsheet = getActiveSpreadsheet_();
  SHEET_ORDER.forEach(function(sheetName) {
    var sheet = getOrCreateSheet_(spreadsheet, sheetName);
    if (sheet.getLastRow() === 0 || sheet.getLastColumn() === 0) {
      resetSheetWithHeaders_(sheet, HEADERS[sheetName]);
    } else {
      var existingHeaderCount = sheet.getLastColumn();
      if (existingHeaderCount < HEADERS[sheetName].length) {
        resetSheetWithHeaders_(sheet, HEADERS[sheetName]);
      }
    }
    applyBaseSheetFormatting_(sheetName, sheet);
  });
  applyDataValidations_();
  reorderSheets_(spreadsheet);
}

function seedAdvancedProjectCase_() {
  var projectId = getAdvancedProjectId_();
  var row = [
    projectId,
    getProjectValue_('Project_Name') || '115 學年度校慶運動會',
    getProjectValue_('Activity_Type') || '校慶運動會',
    getProjectValue_('Host_Unit') || '學務處',
    getProjectValue_('Project_Manager') || '學務主任',
    getProjectValue_('Event_Date') || '2026-11-14',
    getProjectValue_('Event_Date') || '2026-11-14',
    getProjectValue_('Project_Status') || '執行中',
    '',
    getTaskReportFormConfigValueSafe_('Form_URL'),
    '',
    '',
    '大型活動行政流平台進階展示資料'
  ];
  writeRows_(SHEETS.PROJECT_CASES, [row], true);
}

function seedAdvancedRoster_() {
  if (!SHEETS.ACTIVITY_ROSTER) {
    return;
  }
  var projectId = getAdvancedProjectId_();
  var rows = [
    ['REG-001', projectId, 'S-001', '上午主場活動', '王小明', '六年一班', 'student01@example.com', '學生', '已確認', '正取', '免收費', '未簽到', 'CHK001', '選手代表'],
    ['REG-002', projectId, 'S-001', '上午主場活動', '林小華', '五年二班', 'student02@example.com', '學生', '已確認', '正取', '免收費', '未簽到', 'CHK002', '班級表演'],
    ['REG-003', projectId, 'S-002', '下午成果分享', '陳老師', '教務處', 'teacher01@example.com', '教師', '已確認', '正取', '已繳', '未簽到', 'CHK003', '成果分享講師'],
    ['REG-004', projectId, 'S-001', '上午主場活動', '張家長', '家長會', 'parent01@example.com', '家長', '已確認', '正取', '已繳', '未簽到', 'CHK004', '志工支援'],
    ['REG-005', projectId, 'S-001', '上午主場活動', '黃志工', '社區志工隊', 'volunteer01@example.com', '志工', '已確認', '正取', '免收費', '未簽到', 'CHK005', '動線引導'],
    ['REG-006', projectId, 'S-002', '下午成果分享', '李主任', '學務處', 'director01@example.com', '工作人員', '已確認', '正取', '免收費', '未簽到', 'CHK006', '行政統籌'],
    ['REG-007', projectId, 'S-002', '下午成果分享', '吳老師', '輔導室', 'teacher02@example.com', '教師', '資料待補', '備取', '未繳', '未簽到', 'CHK007', '候補參與'],
    ['REG-008', projectId, 'S-001', '上午主場活動', '周家長', '三年三班', 'parent02@example.com', '家長', '已確認', '正取', '已繳', '未簽到', 'CHK008', '攝影協助']
  ];
  writeRows_(SHEETS.ACTIVITY_ROSTER, rows, true);
}

function seedAdvancedPayments_() {
  if (!SHEETS.PAYMENT_MANAGEMENT) {
    return;
  }
  var projectId = getAdvancedProjectId_();
  var rows = [
    ['PAY-001', projectId, 'REG-001', '王小明', '活動費', 0, 0, '免收費', '', '', '不需退費', ''],
    ['PAY-002', projectId, 'REG-002', '林小華', '活動費', 0, 0, '免收費', '', '', '不需退費', ''],
    ['PAY-003', projectId, 'REG-003', '陳老師', '研習材料費', 200, 200, '已繳', '現金', '2026-10-01', '不需退費', ''],
    ['PAY-004', projectId, 'REG-004', '張家長', '志工保險費', 100, 100, '已繳', '轉帳', '2026-10-02', '不需退費', ''],
    ['PAY-005', projectId, 'REG-005', '黃志工', '活動費', 0, 0, '免收費', '', '', '不需退費', ''],
    ['PAY-006', projectId, 'REG-006', '李主任', '活動費', 0, 0, '免收費', '', '', '不需退費', ''],
    ['PAY-007', projectId, 'REG-007', '吳老師', '研習材料費', 200, 0, '未繳', '', '', '不需退費', '備取待確認'],
    ['PAY-008', projectId, 'REG-008', '周家長', '志工保險費', 100, 100, '已繳', '現金', '2026-10-03', '不需退費', '']
  ];
  writeRows_(SHEETS.PAYMENT_MANAGEMENT, rows, true);
}

function seedAdvancedAttendance_() {
  if (!SHEETS.ATTENDANCE_MANAGEMENT || !SHEETS.ACTIVITY_ROSTER) {
    return;
  }
  var projectId = getAdvancedProjectId_();
  var rows = safeReadObjects_(SHEETS.ACTIVITY_ROSTER).map(function(person, index) {
    return [
      'ATT-' + padNumber_(index + 1, 3),
      projectId,
      person.Session_ID,
      person.Session_Name,
      person.Registration_ID,
      person.Name,
      person.Unit,
      person.Email,
      '未簽到',
      '',
      '',
      person.Note || ''
    ];
  });
  writeRows_(SHEETS.ATTENDANCE_MANAGEMENT, rows, true);
}

function seedAdvancedOutcomes_() {
  var projectId = getAdvancedProjectId_();
  var rows = [
    ['OUT-001', projectId, '成果報告', '學務主任', '2026-11-20', 'https://example.com/outcome-report', '活動流程完整，跨處室回報能即時追蹤問題。', '通過', '是', ''],
    ['OUT-002', projectId, '活動照片', '資訊組長', '2026-11-15', 'https://example.com/photos', '照片依典禮、競賽、頒獎分類。', '通過', '是', ''],
    ['OUT-003', projectId, '學員回饋', '教學組長', '2026-11-18', 'https://example.com/feedback', '多數回饋肯定流程清楚，建議下次加強雨備提醒。', '通過', '是', ''],
    ['OUT-004', projectId, '經費結算', '總務主任', '2026-11-22', 'https://example.com/budget', '待補發票與保險費明細。', '需補件', '否', ''],
    ['OUT-005', projectId, '新聞稿', '輔導主任', '2026-11-21', 'https://example.com/news', '可整理成校網公告與地方新聞稿。', '未審核', '是', '']
  ];
  writeRows_(SHEETS.OUTCOME_SUBMISSIONS, rows, true);
}

function updateRosterAttendanceFromCheckins_() {
  if (!SHEETS.ACTIVITY_ROSTER || !SHEETS.ATTENDANCE_MANAGEMENT) {
    return;
  }
  var rosterSheet = getSheetOrThrow_(SHEETS.ACTIVITY_ROSTER);
  var rosterHeaderMap = getHeaderMap_(rosterSheet);
  var attendanceByRegistrationId = {};
  safeReadObjects_(SHEETS.ATTENDANCE_MANAGEMENT).forEach(function(item) {
    attendanceByRegistrationId[item.Registration_ID] = item.Attendance_Status;
  });
  safeReadObjects_(SHEETS.ACTIVITY_ROSTER).forEach(function(person) {
    if (attendanceByRegistrationId[person.Registration_ID]) {
      setCellIfColumn_(rosterSheet, rosterHeaderMap, person._rowNumber, 'Attendance_Status', attendanceByRegistrationId[person.Registration_ID]);
    }
  });
}

function updateProjectCaseField_(fieldName, value) {
  var sheet = getSheetOrThrow_(SHEETS.PROJECT_CASES);
  var rows = readObjects_(SHEETS.PROJECT_CASES);
  if (!rows.length) {
    seedAdvancedProjectCase_();
    rows = readObjects_(SHEETS.PROJECT_CASES);
  }
  var headerMap = getHeaderMap_(sheet);
  setCellIfColumn_(sheet, headerMap, rows[0]._rowNumber, fieldName, value);
}

function getAdvancedFolderByCode_(folderCode) {
  var rows = safeReadObjects_(SHEETS.PROJECT_FOLDERS);
  for (var i = 0; i < rows.length; i++) {
    if (rows[i].Folder_Code === folderCode && rows[i].Folder_ID) {
      return DriveApp.getFolderById(rows[i].Folder_ID);
    }
  }
  return null;
}

function getAdvancedFolderSpecs_() {
  return [
    { code: '01_PROJECT', name: '01_專案建案資料', purpose: '專案建案資料' },
    { code: '02_MEETING', name: '02_會議紀錄與決議', purpose: '會議紀錄與決議' },
    { code: '03_TASK', name: '03_WBS任務附件', purpose: 'WBS任務附件' },
    { code: '04_NOTICE', name: '04_通知文件', purpose: '通知文件' },
    { code: '05_SITE', name: '05_場地與動線資料', purpose: '場地與動線資料' },
    { code: '06_PHOTO', name: '06_活動照片', purpose: '活動照片' },
    { code: '07_RESULT', name: '07_成果報告', purpose: '成果報告' },
    { code: '08_AI', name: '08_AI知識庫素材', purpose: 'AI知識庫素材' },
    { code: '09_OTHER', name: '09_其他', purpose: '其他' }
  ];
}

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

function getAdvancedProjectId_() {
  return getProjectValue_('Project_ID') || 'ACT-2026-001';
}

function getProjectCaseUrl_() {
  var spreadsheet = getActiveSpreadsheet_();
  var sheet = spreadsheet.getSheetByName(SHEETS.PROJECT_CASES);
  if (!sheet) {
    return spreadsheet.getUrl();
  }
  return spreadsheet.getUrl() + '#gid=' + sheet.getSheetId();
}

function getTaskReportFormConfigValueSafe_(key) {
  try {
    return getTaskReportFormConfigValue_(key);
  } catch (error) {
    return '';
  }
}

function buildAiPrompt_(instruction) {
  return [
    instruction,
    '',
    '請使用繁體中文，語氣正式但易讀。',
    '請依據中控台資料，不要自行補入無法驗證的事實。',
    '請輸出：摘要、重點發現、風險或問題、後續建議。'
  ].join('\n');
}

function padNumber_(number, width) {
  var text = String(number);
  while (text.length < width) {
    text = '0' + text;
  }
  return text;
}

// ===== full_platform_service.gs =====
// 完整行政流平台函式已不作為校慶運動會展示版入口使用。
function setupFullAdminWorkflowPlatform() {
  alert_('完整行政流平台入口已停用。請改用「初始化運動會專案」與「匯入校慶 WBS 任務模板」。');
}

function generateWbsFromTemplateCenter() {
  alert_('模板中心已不屬於校慶運動會展示版。請改用「匯入校慶 WBS 任務模板」。');
}

function syncRegistrationResponsesToRoster() {
  if (!SHEETS.ACTIVITY_ROSTER || !SHEETS.FORM_RESPONSE_INBOX) {
    alert_('報名回應同步已不屬於校慶運動會展示版選單流程。');
    return;
  }
  try {
    ensureAdvancedPlatformSheets_();
    var responseSheets = findFormResponseSheets_('registration');
    var projectId = getAdvancedProjectId_();
    var existingKeys = buildExistingKeySet_(safeReadObjects_(SHEETS.ACTIVITY_ROSTER), function(row) {
      return normalizeKey_(row.Email + '|' + row.Session_Name);
    });
    var rosterRows = [];
    var inboxRows = [];

    responseSheets.forEach(function(sheet) {
      var rows = readObjectsFromSheet_(sheet);
      rows.forEach(function(row) {
        var email = row.Email || row.Reporter_Email || '';
        var name = row.Name || row.Reporter || '';
        var sessionName = row.Session_Name || '預設場次';
        var key = normalizeKey_(email + '|' + sessionName);
        if (!email || !name || existingKeys[key]) {
          inboxRows.push(buildFormInboxRow_(sheet.getName(), '報名回應', row.Timestamp, key || name, SHEETS.ACTIVITY_ROSTER, existingKeys[key] ? '略過' : '錯誤', existingKeys[key] ? '' : '缺少姓名或 Email', row));
          return;
        }
        var registrationId = makeSequentialId_('REG', safeReadObjects_(SHEETS.ACTIVITY_ROSTER).length + rosterRows.length + 1);
        rosterRows.push([
          registrationId,
          projectId,
          'S-001',
          sessionName,
          name,
          row.Unit || '',
          email,
          row.Role || '學員',
          '新報名',
          '待審核',
          '未繳',
          '未簽到',
          'CHK' + padNumber_(safeReadObjects_(SHEETS.ACTIVITY_ROSTER).length + rosterRows.length + 1, 3),
          row.Note || ''
        ]);
        existingKeys[key] = true;
        inboxRows.push(buildFormInboxRow_(sheet.getName(), '報名回應', row.Timestamp, key, SHEETS.ACTIVITY_ROSTER, '已正規化', '', row));
      });
    });

    if (rosterRows.length) {
      writeRows_(SHEETS.ACTIVITY_ROSTER, rosterRows, false);
    }
    if (inboxRows.length) {
      writeRows_(SHEETS.FORM_RESPONSE_INBOX, inboxRows, false);
    }
    updateDashboard();
    writeAuditLog('同步報名表單回應', SHEETS.ACTIVITY_ROSTER, '', '', rosterRows.length + ' 筆', '成功', '');
    alert_('報名表單回應同步完成：新增 ' + rosterRows.length + ' 筆。');
  } catch (error) {
    writeFailureAuditLog_('同步報名表單回應', SHEETS.ACTIVITY_ROSTER, '', error);
    throw error;
  }
}

function syncOutcomeResponsesToSubmissions() {
  if (!SHEETS.FORM_RESPONSE_INBOX) {
    alert_('成果表單回應暫存已不屬於校慶運動會展示版選單流程。');
    return;
  }
  try {
    ensureAdvancedPlatformSheets_();
    var responseSheets = findFormResponseSheets_('outcome');
    var projectId = getAdvancedProjectId_();
    var existingKeys = buildExistingKeySet_(safeReadObjects_(SHEETS.OUTCOME_SUBMISSIONS), function(row) {
      return normalizeKey_(row.Submitter + '|' + row.File_Link + '|' + row.Submitted_At);
    });
    var outcomeRows = [];
    var inboxRows = [];

    responseSheets.forEach(function(sheet) {
      var rows = readObjectsFromSheet_(sheet);
      rows.forEach(function(row) {
        var submitter = row.Submitter || row.Name || '';
        var fileLink = row.File_Link || row.Doc_Link || '';
        var key = normalizeKey_(submitter + '|' + fileLink + '|' + row.Timestamp);
        if (!submitter || existingKeys[key]) {
          inboxRows.push(buildFormInboxRow_(sheet.getName(), '成果回應', row.Timestamp, key || submitter, SHEETS.OUTCOME_SUBMISSIONS, existingKeys[key] ? '略過' : '錯誤', existingKeys[key] ? '' : '缺少提交者', row));
          return;
        }
        var outcomeId = makeSequentialId_('OUT', safeReadObjects_(SHEETS.OUTCOME_SUBMISSIONS).length + outcomeRows.length + 1);
        outcomeRows.push([
          outcomeId,
          projectId,
          row.Outcome_Type || '成果報告',
          submitter,
          row.Timestamp || new Date(),
          fileLink,
          row.Feedback || '',
          '未審核',
          row.Include_In_AI_Knowledge || '是',
          row.Note || ''
        ]);
        existingKeys[key] = true;
        inboxRows.push(buildFormInboxRow_(sheet.getName(), '成果回應', row.Timestamp, key, SHEETS.OUTCOME_SUBMISSIONS, '已正規化', '', row));
      });
    });

    if (outcomeRows.length) {
      writeRows_(SHEETS.OUTCOME_SUBMISSIONS, outcomeRows, false);
    }
    if (inboxRows.length) {
      writeRows_(SHEETS.FORM_RESPONSE_INBOX, inboxRows, false);
    }
    buildAiKnowledgeIndex();
    updateDashboard();
    writeAuditLog('同步成果表單回應', SHEETS.OUTCOME_SUBMISSIONS, '', '', outcomeRows.length + ' 筆', '成功', '');
    alert_('成果表單回應同步完成：新增 ' + outcomeRows.length + ' 筆。');
  } catch (error) {
    writeFailureAuditLog_('同步成果表單回應', SHEETS.OUTCOME_SUBMISSIONS, '', error);
    throw error;
  }
}

function generateNotificationQueue() {
  var projectId = getAdvancedProjectId_();
  var projectName = getProjectValue_('Project_Name') || '活動專案';
  var tasks = safeReadObjects_(SHEETS.MASTER_WBS);
  var rows = [];

  tasks.filter(function(task) {
    return task.Status !== '已完成' && task.Status !== '鎖定';
  }).slice(0, 20).forEach(function(task) {
    rows.push(buildNotificationRow_(projectId, '任務通知', '任務', task.Task_ID, getTaskRecipient_(task) || '', '【任務通知】' + task.Task_Name, buildTaskEmail_(task, '派工通知').body, '待寄送', '由 WBS 產生'));
  });

  rows.push(buildNotificationRow_(projectId, '行前通知', '專案', projectId, getProjectValue_('Project_Manager_Email') || '', '【行前通知】' + projectName, buildSportsDayProjectNotice_(projectName), '待寄送', '展示版行前通知'));
  rows.push(buildNotificationRow_(projectId, '結案提醒', '專案', projectId, getProjectValue_('Project_Manager_Email') || '', '【結案提醒】' + projectName, buildSportsDayClosureReminder_(projectName), '待寄送', '展示版結案提醒'));

  writeRows_(SHEETS.NOTIFICATION_QUEUE, rows, true);
  writeAuditLog('產生通知佇列', SHEETS.NOTIFICATION_QUEUE, projectId, '', rows.length + ' 筆', '成功', '');
  return rows.length;
}

function buildSportsDayProjectNotice_(projectName) {
  return [
    projectName + ' 行前通知',
    '',
    '請依 WBS 任務主表確認各組交付物、場地動線、雨備流程與家長通知文件。',
    '若任務狀態為延宕或待確認，請先回報卡關原因並標記需要協助處室。'
  ].join('\n');
}

function buildSportsDayClosureReminder_(projectName) {
  return [
    projectName + ' 結案提醒',
    '',
    '請彙整成果照片、活動報告、問題改善建議與可納入 AI 知識庫的素材。',
    '完成後請產生活動結案報告草稿並更新儀表板燈號。'
  ].join('\n');
}

function generateNotificationQueueMenu() {
  try {
    var count = generateNotificationQueue();
    updateDashboard();
    alert_('通知佇列已產生：' + count + ' 筆。');
  } catch (error) {
    writeFailureAuditLog_('產生通知佇列', SHEETS.NOTIFICATION_QUEUE, '', error);
    throw error;
  }
}

function exportNotificationQueueToEmailPreview() {
  try {
    var queueSheet = getSheetOrThrow_(SHEETS.NOTIFICATION_QUEUE);
    var headerMap = getHeaderMap_(queueSheet);
    var queue = safeReadObjects_(SHEETS.NOTIFICATION_QUEUE);
    var rows = [];
    queue.forEach(function(item) {
      if (item.Status !== '待寄送') {
        return;
      }
      rows.push([
        makeId_('EMAIL'),
        new Date(),
        item.Notification_Type,
        item.Target_ID,
        item.Recipient || '未設定 Email',
        item.Subject,
        item.Content,
        '未寄送',
        '',
        '',
        '由通知佇列轉出'
      ]);
      setCellIfColumn_(queueSheet, headerMap, item._rowNumber, 'Status', '已轉預覽');
    });
    writeRows_(SHEETS.EMAIL_PREVIEW, rows, true);
    updateDashboard();
    writeAuditLog('通知佇列轉 Email 預覽', SHEETS.EMAIL_PREVIEW, '', '', rows.length + ' 筆', '成功', '');
    alert_('已轉出 Email 預覽：' + rows.length + ' 筆。');
  } catch (error) {
    writeFailureAuditLog_('通知佇列轉 Email 預覽', SHEETS.EMAIL_PREVIEW, '', error);
    throw error;
  }
}

function seedTemplateCenter_() {
  if (!SHEETS.TEMPLATE_CENTER) {
    return;
  }
  var rows = [
    ['TPL-SP-001', '校慶運動會', '校慶行政標準流程', '建案', 1, '籌備期', '完成活動建案', '活動建案表', '專案目標、日期、主辦單位、負責人確認', '學務處', '學務主任', '校長室', 2, '', 'TRUE', '中', '是', '平台預設模板'],
    ['TPL-SP-002', '校慶運動會', '校慶行政標準流程', 'WBS', 2, '籌備期', '建立 WBS 與 RACI', '任務主表與權責矩陣', '各組任務與責任角色完成', '學務處', '體育組長', '教務處', 3, 1, 'TRUE', '中', '是', ''],
    ['TPL-SP-003', '校慶運動會', '校慶行政標準流程', '報名', 3, '籌備期', '建立活動名冊與報名表', '報名表與活動名冊', '報名欄位、錄取狀態與簽到碼可用', '學務處', '訓育組長', '資訊組', 4, 2, 'FALSE', '中', '是', ''],
    ['TPL-SP-004', '校慶運動會', '校慶行政標準流程', '收費', 4, '籌備期', '確認收費與保險資料', '收費管理表', '應收、已收與退費狀態可追蹤', '總務處', '出納組長', '學務處', 5, 3, 'FALSE', '中', '是', ''],
    ['TPL-SP-005', '校慶運動會', '校慶行政標準流程', '簽到', 5, '正日', '執行活動簽到', '簽到管理表', '正取人員簽到狀態可追蹤', '學務處', '體育組長', '志工', 1, 3, 'TRUE', '高', '是', ''],
    ['TPL-SP-006', '校慶運動會', '校慶行政標準流程', '成果', 6, '善後期', '收集成果與照片', '成果資料表', '成果、照片、回饋可納入知識庫', '學務處', '資訊組長', '教務處', 5, 5, 'FALSE', '中', '是', ''],
    ['TPL-SP-007', '校慶運動會', '校慶行政標準流程', 'AI知識庫', 7, '善後期', '建立 AI 知識庫索引', 'AI知識庫索引', '成果資料已整理為 NotebookLM 匯入清單', '教務處', '資訊組長', '學務處', 2, 6, 'FALSE', '低', '是', ''],
    ['TPL-TR-001', '教師研習', '教師研習行政標準流程', '建案', 1, '籌備期', '完成研習建案', '研習建案資料', '主題、講師、時數、場地確認', '教務處', '教學組長', '總務處', 2, '', 'TRUE', '中', '是', ''],
    ['TPL-TR-002', '教師研習', '教師研習行政標準流程', '報名', 2, '籌備期', '建立研習報名表', '報名表與名冊', '可管理正取、備取與錄取通知', '教務處', '教學組長', '資訊組', 3, 1, 'FALSE', '中', '是', ''],
    ['TPL-TR-003', '教師研習', '教師研習行政標準流程', '成果', 3, '善後期', '收集研習回饋與成果', '回饋與成果資料', '可產生研習成果報告草稿', '教務處', '研習承辦人', '學務處', 5, 2, 'FALSE', '中', '是', '']
  ];
  writeRows_(SHEETS.TEMPLATE_CENTER, rows, true);
}

function seedWorkflowGenerator_() {
  if (!SHEETS.WORKFLOW_GENERATOR) {
    return;
  }
  var rows = [
    ['WF-001', '活動建案到資料夾', '校慶運動會', '新增活動建案', '活動建案表', '專案資料夾索引', '建立專案資料夾架構', '專案管理者', 1, '啟用', ''],
    ['WF-002', '活動建案到 WBS', '校慶運動會', '建案狀態為規劃中', '模板中心', 'WBS任務主表', '由模板中心產生 WBS', '專案管理者', 2, '啟用', ''],
    ['WF-003', '報名回應到名冊', '校慶運動會', '報名表單有新回應', 'Google Forms 回應表', '活動名冊', '同步報名表單回應', '承辦人', 1, '啟用', ''],
    ['WF-004', '名冊到通知佇列', '校慶運動會', '錄取狀態確認', '活動名冊', '通知佇列', '產生通知佇列', '承辦人', 1, '啟用', ''],
    ['WF-005', '成果到知識庫', '校慶運動會', '成果表單有新回應', '成果資料', 'AI知識庫索引', '更新 AI 知識庫索引', '資訊組長', 2, '啟用', ''],
    ['WF-006', '成果到結案報告', '校慶運動會', '成果審核通過', '成果資料', '文件預覽', '產生活動結案報告草稿', '專案管理者', 3, '啟用', '']
  ];
  writeRows_(SHEETS.WORKFLOW_GENERATOR, rows, true);
}

function seedRolePermissions_() {
  if (!SHEETS.ROLE_PERMISSIONS) {
    return;
  }
  var projectId = getAdvancedProjectId_();
  var rows = [
    ['ROLE-001', '平台管理者', Session.getActiveUser().getEmail() || '', 'ALL', '全部', '全部', '全部', '啟用', '目前登入者預設為平台管理者'],
    ['ROLE-002', '專案管理者', '', projectId, '全部', '活動建案表,WBS任務主表,儀表板,通知佇列', '建立完整行政流平台,由模板中心產生 WBS,更新儀表板', '啟用', ''],
    ['ROLE-003', '承辦人', '', projectId, '活動名冊,收費管理,簽到管理,成果資料', '活動名冊,簽到管理,成果資料', '同步報名表單回應,同步成果表單回應,新增示範簽到', '啟用', ''],
    ['ROLE-004', '主管檢視', '', projectId, '儀表板,WBS任務主表,風險登錄,問題追蹤,決議紀錄', '', '更新儀表板', '啟用', '']
  ];
  writeRows_(SHEETS.ROLE_PERMISSIONS, rows, true);
}

function seedIntegrationSettings_() {
  if (!SHEETS.INTEGRATION_SETTINGS) {
    return;
  }
  var rows = [
    ['INT-001', 'Google Forms', '啟用', 'Forms_Service', 'Apps Script FormApp', '建立任務、報名、成果表單', '表單回應會進入 Google 自動建立的回應表，需再正規化', ''],
    ['INT-002', 'Google Drive', '啟用', 'Drive_Service', 'DriveApp', '建立專案資料夾與索引', '需 Drive 授權', ''],
    ['INT-003', 'Google Calendar', '待設定', 'Calendar_ID', getProjectValue_('Calendar_ID') || '', '同步任務期限與里程碑', '大量學員版不建議全部啟用', ''],
    ['INT-004', 'Gmail', '待設定', 'Mail_Mode', 'Preview_First', '通知佇列轉 Email 預覽或測試寄送', '正式寄送前需確認收件人與測試模式', ''],
    ['INT-005', 'Gemini API', '停用', 'Gemini_API_Key', '', 'AI 摘要、新聞稿、結案報告', '需 API Key、配額與資料保護設計', '目前僅產生提示詞草稿'],
    ['INT-006', 'NotebookLM', '待設定', 'NotebookLM_Project_URL', '', 'AI 知識庫資料來源索引', '目前無完整 Apps Script 自動匯入流程，先人工匯入', ''],
    ['INT-007', 'Line Webhook', '停用', 'Line_Channel_Access_Token', '', 'Line 通知或回報入口', '需外部 webhook endpoint，不屬於純 GAS MVP', '']
  ];
  writeRows_(SHEETS.INTEGRATION_SETTINGS, rows, true);
}

function buildWbsRowsFromTemplates_(projectId, templates) {
  var baseDate = normalizeDate_(getProjectValue_('Event_Date')) || today_();
  var orderToTaskId = {};
  templates.forEach(function(template, index) {
    orderToTaskId[String(template.Sort_Order || index + 1)] = projectId + '-T' + padNumber_(index + 1, 3);
  });
  return templates.map(function(template, index) {
    var sortOrder = Number(template.Sort_Order || index + 1);
    var taskId = orderToTaskId[String(template.Sort_Order || sortOrder)];
    var dependencyId = template.Dependency_Order ? orderToTaskId[String(template.Dependency_Order)] || '' : '';
    var startDate = addDays_(baseDate, -Math.max(30 - sortOrder * 3, 0));
    var dueDate = addDays_(startDate, Number(template.Duration_Days || 1));
    return [
      taskId,
      String(sortOrder),
      '',
      template.Stage || '籌備期',
      template.Suggested_Office || '學務處',
      template.Suggested_Owner || '',
      '',
      '',
      '',
      template.Support_Office || '',
      template.Task_Name || '',
      template.Deliverable || '',
      template.Acceptance_Criteria || '',
      formatDate_(startDate),
      formatDate_(dueDate),
      Number(template.Duration_Days || 1),
      dependencyId,
      dependencyId ? '鎖定' : '未開始',
      '0%',
      template.Critical_Flag || 'FALSE',
      0,
      '由模板中心產生，請依活動實況調整。',
      template.Risk_Level || '中',
      '',
      '',
      '',
      '',
      '',
      '',
      0,
      '',
      '否',
      '',
      '',
      0,
      '未通知',
      '',
      '',
      '',
      '',
      '',
      template.Note || '模板：' + (template.Template_Name || '')
    ];
  });
}

function findFormResponseSheets_(type) {
  var spreadsheet = getActiveSpreadsheet_();
  var knownNames = {};
  SHEET_ORDER.forEach(function(name) {
    knownNames[name] = true;
  });
  return spreadsheet.getSheets().filter(function(sheet) {
    if (knownNames[sheet.getName()]) {
      return false;
    }
    var headers = getSheetHeaderTexts_(sheet);
    if (type === 'registration') {
      return containsAll_(headers, ['姓名', 'Email']) && (headers.indexOf('場次名稱') !== -1 || headers.indexOf('身分') !== -1);
    }
    if (type === 'outcome') {
      return headers.indexOf('提交者') !== -1 || headers.indexOf('成果類型') !== -1 || headers.indexOf('是否納入AI知識庫') !== -1;
    }
    return false;
  });
}

function readObjectsFromSheet_(sheet) {
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  if (lastRow < DATA_START_ROW || lastColumn < 1) {
    return [];
  }
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  return sheet.getRange(DATA_START_ROW, 1, lastRow - 1, lastColumn).getValues().map(function(row, rowIndex) {
    var object = { _rowNumber: rowIndex + DATA_START_ROW };
    headers.forEach(function(header, index) {
      var headerText = String(header || '');
      if (!headerText) {
        return;
      }
      object[headerText] = row[index];
      if (FIELD_ALIASES[headerText]) {
        object[FIELD_ALIASES[headerText]] = row[index];
      }
    });
    return object;
  });
}

function getSheetHeaderTexts_(sheet) {
  if (sheet.getLastColumn() < 1) {
    return [];
  }
  return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(function(value) {
    return String(value || '');
  });
}

function containsAll_(items, required) {
  return required.every(function(item) {
    return items.indexOf(item) !== -1;
  });
}

function buildExistingKeySet_(rows, keyFn) {
  var keys = {};
  rows.forEach(function(row) {
    var key = keyFn(row);
    if (key) {
      keys[key] = true;
    }
  });
  return keys;
}

function buildFormInboxRow_(sourceSheet, responseType, responseAt, uniqueKey, targetSheet, status, errorMessage, rawObject) {
  return [
    makeId_('INBOX'),
    sourceSheet,
    responseType,
    responseAt || new Date(),
    uniqueKey || '',
    targetSheet,
    status,
    errorMessage || '',
    JSON.stringify(rawObject || {}),
    new Date(),
    ''
  ];
}

function buildNotificationRow_(projectId, type, targetType, targetId, recipient, subject, content, status, note) {
  return [
    makeId_('NOTICE'),
    projectId,
    type,
    targetType,
    targetId || '',
    recipient || '',
    subject || '',
    content || '',
    new Date(),
    status || '待寄送',
    '',
    '',
    note || ''
  ];
}

function buildAdmissionNotice_(person, projectName) {
  return [
    (person.Name || '您好') + ' 您好：',
    '',
    '您已錄取「' + projectName + '」。',
    '場次：' + (person.Session_Name || ''),
    '單位：' + (person.Unit || ''),
    '',
    '後續行前通知將由活動專案中控台寄送。'
  ].join('\n');
}

function buildPreEventNotice_(person, projectName) {
  return [
    (person.Name || '您好') + ' 您好：',
    '',
    '提醒您即將參與「' + projectName + '」。',
    '場次：' + (person.Session_Name || ''),
    '簽到碼：' + (person.Checkin_Code || ''),
    '',
    '請依主辦單位公告時間完成簽到。'
  ].join('\n');
}

function buildOutcomeReminder_(person, projectName) {
  return [
    (person.Name || '您好') + ' 您好：',
    '',
    '感謝參與「' + projectName + '」。',
    '請於期限內提交成果、照片或回饋，供結案報告與知識庫整理使用。'
  ].join('\n');
}

function normalizeKey_(value) {
  return String(value || '').trim().toLowerCase();
}

function makeSequentialId_(prefix, number) {
  return prefix + '-' + padNumber_(number, 3);
}

function addDays_(date, days) {
  var result = new Date(date.getFullYear(), date.getMonth(), date.getDate());
  result.setDate(result.getDate() + Number(days || 0));
  return result;
}

// ===== reset_service.gs =====
function resetDemoData() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert(
    '重置示範資料',
    '這會清空並重新匯入 WBS、RACI、里程碑、風險、問題、決議、溝通、成果、知識庫、通知與文件預覽示範資料。是否繼續？',
    ui.ButtonSet.YES_NO
  );
  if (response !== ui.Button.YES) {
    alert_('已取消重置。');
    return;
  }

  try {
    clearDataRows_(SHEETS.MASTER_WBS);
    clearDataRows_(SHEETS.SYSTEM_CODE_TABLE);
    clearDataRows_(SHEETS.RACI_MATRIX);
    clearDataRows_(SHEETS.MILESTONES);
    clearDataRows_(SHEETS.RISK_REGISTER);
    clearDataRows_(SHEETS.ISSUE_TRACKER);
    clearDataRows_(SHEETS.DECISION_LOG);
    clearDataRows_(SHEETS.COMMUNICATION_PLAN);
    clearDataRows_(SHEETS.TASK_REPORTS);
    clearDataRows_(SHEETS.PROJECT_CASES);
    clearDataRows_(SHEETS.OUTCOME_SUBMISSIONS);
    clearDataRows_(SHEETS.AI_KNOWLEDGE_INDEX);
    clearDataRows_(SHEETS.NOTIFICATION_QUEUE);
    clearDataRows_(SHEETS.EMAIL_PREVIEW);
    clearDataRows_(SHEETS.GENERATED_DOCS_PREVIEW);

    writeRows_(SHEETS.MASTER_WBS, getSportsDayWbsRows_(), true);
    seedSystemCodeTable_();
    seedRaciFromTasks();
    seedMilestones();
    seedTrainingRisks_();
    seedTrainingIssues_();
    seedTrainingDecisions_();
    seedCommunicationPlan_();
    seedAdvancedProjectCase_();
    seedAdvancedOutcomes_();
    buildAiKnowledgeIndex();
    generateNotificationQueue();
    updateRiskLevels();
    updateDashboard();
    writeAuditLog('重置示範資料', 'ALL', '', '', '恢復校慶運動會展示資料', '成功', '');
    alert_('示範資料已重置完成，可重新進行校慶運動會展示流程。');
  } catch (error) {
    writeFailureAuditLog_('重置示範資料', 'ALL', '', error);
    throw error;
  }
}

function clearDataRows_(sheetName) {
  var sheet = getSheetOrThrow_(sheetName);
  var lastRow = sheet.getLastRow();
  if (lastRow >= DATA_START_ROW) {
    sheet.getRange(DATA_START_ROW, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
  }
}

// ===== calendar_service.gs =====
function syncMilestonesToCalendar() {
  try {
    var calendarId = getProjectValue_('Calendar_ID');
    if (!calendarId) {
      alert_('「專案資訊」中的 Calendar_ID 尚未設定。研習版建議此功能僅由講師展示使用。');
      return;
    }
    var calendar = CalendarApp.getCalendarById(calendarId);
    if (!calendar) {
      alert_('找不到指定 Calendar。請檢查「專案資訊」中的 Calendar_ID。');
      return;
    }
    var milestones = readObjects_(SHEETS.MILESTONES);
    var createdCount = 0;
    milestones.forEach(function(milestone) {
      var dueDate = normalizeDate_(milestone.Due_Date);
      if (!dueDate) {
        return;
      }
      calendar.createAllDayEvent('[校慶] ' + milestone.Milestone_Name, dueDate, {
        description: milestone.Completion_Criteria || ''
      });
      createdCount += 1;
    });
    writeAuditLog('同步重要日期到 Calendar', SHEETS.MILESTONES, '', '', createdCount + ' 筆', '成功', '');
    alert_('已同步 ' + createdCount + ' 筆里程碑到 Calendar。');
  } catch (error) {
    writeFailureAuditLog_('同步重要日期到 Calendar', SHEETS.MILESTONES, '', error);
    throw error;
  }
}

function syncTaskDueDatesToCalendar() {
  try {
    if (getTaskReportFormConfigValue_('Form_ID')) {
      updateTaskReportFormChoices(false);
      generatePrefilledReportUrls(false);
    }
    var calendarId = getProjectValue_('Calendar_ID');
    if (!calendarId) {
      alert_('「專案資訊」中的 Calendar_ID 尚未設定。研習版建議此功能僅由講師展示使用。');
      return;
    }
    var calendar = CalendarApp.getCalendarById(calendarId);
    if (!calendar) {
      alert_('找不到指定 Calendar。請檢查「專案資訊」中的 Calendar_ID。');
      return;
    }

    var taskSheet = getSheetOrThrow_(SHEETS.MASTER_WBS);
    var headerMap = getHeaderMap_(taskSheet);
    var tasks = readObjects_(SHEETS.MASTER_WBS).filter(function(task) {
      return task.Status !== '已完成' && task.Status !== '取消' && normalizeDate_(task.Due_Date);
    }).slice(0, 50);
    var createdCount = 0;

    tasks.forEach(function(task) {
      var dueDate = normalizeDate_(task.Due_Date);
      var event = calendar.createAllDayEvent('【校慶任務到期】' + task.Task_Name, dueDate, {
        description: [
          '任務編號：' + task.Task_ID,
          '主責處室：' + task.Office,
          '主責人：' + task.Owner,
          '交付物：' + task.Deliverable,
          '完成判準：' + task.Acceptance_Criteria,
          '截止日期：' + formatDate_(task.Due_Date),
          '',
          '任務回報表：',
          task.Prefilled_Report_URL || task.Report_Form_URL || getTaskReportFormUrl(task.Task_ID) || '尚未建立任務回報表單'
        ].join('\n')
      });
      if (headerMap.Calendar_Event_ID) {
        taskSheet.getRange(task._rowNumber, headerMap.Calendar_Event_ID).setValue(event.getId());
      }
      createdCount += 1;
    });

    writeAuditLog('同步任務期限到 Calendar', SHEETS.MASTER_WBS, '', '', createdCount + ' 筆', '成功', '');
    alert_('已同步 ' + createdCount + ' 筆任務期限到 Calendar。');
  } catch (error) {
    writeFailureAuditLog_('同步任務期限到 Calendar', SHEETS.MASTER_WBS, '', error);
    throw error;
  }
}