國中小班級版一週三報 GAS 程式碼

/**
 * 一週一報三端分流 GAS MVP
 *
 * 版本：MVP 0.4
 * 使用方式：
 * 1. 建立 Google Sheet
 * 2. 擴充功能 → Apps Script
 * 3. 貼上本檔
 * 4. 儲存並重新整理試算表
 * 5. 使用「一週一報三端分流」選單操作
 *
 * 核心功能：
 * - 初始化週報平台工作表
 * - 建立本週週報資料列
 * - 建立本週照片資料夾
 * - 產生三端文案：班網完整版、LINE 短提醒版、FB 故事版
 * - 產生 Google Docs 週報
 * - 提供 LINE / FB 文案複製側邊欄
 * - 產生班網發布素材與 HTML 貼上版
 * - 使用 Google Chat Webhook 發布週報摘要到協作平台
 * - 提供本週發布面板 Sidebar
 * - 標記發布狀態
 * - 產生精簡歷週索引
 *
 * 命名說明：
 * - onOpen 是 Google Apps Script 指定的簡單觸發器名稱。
 * - 其他自訂函式皆使用 OWTR_ 前綴，降低與其他 GAS 程式碼命名衝突的機率。
 */

const OWTR_CONFIG = {
  TIMEZONE: "Asia/Taipei",

  SHEETS: {
    DASHBOARD: "00_週報中控台",
    WEEKLY_DATA: "01_週報資料",
    COPY_OUTPUT: "02_三端文案",
    PUBLISH_LOG: "03_發布紀錄",
    SETTINGS: "04_設定",
    INDEX: "05_歷週索引",
    WEBSITE_MATERIALS: "06_班網發布素材",
  },

  DEFAULT_SETTINGS: [
    ["設定項目", "設定值", "說明"],
    ["班級名稱", "六年甲班", "顯示於週報標題"],
    ["導師名稱", "王老師", "顯示於週報署名"],
    ["學校名稱", "○○國小", "顯示於週報內容"],
    ["班級網站網址", "", "Google Sites 或班級網站網址"],
    ["FB粉專網址", "", "班級粉專網址，可空白"],
    ["Drive根資料夾ID", "", "可空白；空白時會自動建立一個根資料夾"],
    ["Google Chat Webhook URL", "", "協作平台 Google Chat 空間的 Webhook URL"],
    ["是否啟用協作平台發布", "否", "填「是」才允許一鍵發布到協作平台"],
    ["協作平台名稱", "Google Chat", "顯示於中控台與發布紀錄"],
    ["是否啟用AI", "否", "MVP 版暫不啟用 AI"],
  ],

  PLACEHOLDERS: {
    learningFocus: "國語：\n數學：\n自然／社會：\n其他：",
    activities: "本週班級活動或學校活動：",
    highlights: "學生亮點或值得鼓勵的表現：",
    reminders: "下週考試、繳交、活動、服裝或物品提醒：",
    parentTasks: "需要家長協助、簽名、填表或準備事項：",
  },

  STATUS: {
    NOT_CREATED: "⚪ 尚未建立",
    DRAFT: "🟡 草稿中",
    COPY_READY: "🟢 已產生文案",
    DOC_READY: "🔵 已產生 Docs",
    COLLAB_READY: "🟣 已發布協作平台",
    COMPLETED: "✅ 已完成三端發布",
  },
};

/**
 * 試算表開啟時建立選單
 */
function onOpen() {
  OWTR_onOpen();
}

function OWTR_onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("一週一報三端分流")
    .addItem("1. 初始化平台", "OWTR_initPlatform")
    .addItem("重新整理中控台", "OWTR_refreshDashboardView")
    .addSeparator()
    .addItem("2. 建立本週週報範本", "OWTR_createCurrentWeekReport")
    .addItem("建立下一週週報範本", "OWTR_createNextWeekReport")
    .addItem("3. 建立本週照片資料夾", "OWTR_createPhotoFolderForActiveWeek")
    .addSeparator()
    .addItem("開啟本週發布面板", "OWTR_showWeeklyPublishPanel")
    .addSeparator()
    .addItem("4. 產生三端文案", "OWTR_generateThreeChannelCopies")
    .addItem("5. 產生 Google Docs 週報", "OWTR_generateGoogleDocForActiveWeek")
    .addItem("產生班網發布素材", "OWTR_generateWebsitePublishMaterials")
    .addItem("一鍵發布到協作平台", "OWTR_publishToCollaborationPlatform")
    .addSeparator()
    .addItem("複製 LINE 文案", "OWTR_showLineCopySidebar")
    .addItem("複製 FB 文案", "OWTR_showFbCopySidebar")
    .addSeparator()
    .addItem("6. 標記班網已發布", "OWTR_markWebsitePublished")
    .addItem("7. 標記 LINE 已發布", "OWTR_markLinePublished")
    .addItem("8. 標記 FB 已發布", "OWTR_markFbPublished")
    .addSeparator()
    .addItem("9. 更新歷週索引", "OWTR_updateWeeklyIndex")
    .addToUi();
}

/**
 * =========================
 * 初始化平台
 * =========================
 */

function OWTR_initPlatform() {
  OWTR_runAction_("初始化平台", function () {
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    OWTR_setupDashboardSheet_(ss);
    OWTR_setupWeeklyDataSheet_(ss);
    OWTR_setupCopyOutputSheet_(ss);
    OWTR_setupPublishLogSheet_(ss);
    OWTR_setupSettingsSheet_(ss);
    OWTR_setupIndexSheet_(ss);
    OWTR_setupWebsiteMaterialsSheet_(ss);

    SpreadsheetApp.getUi().alert(
      "初始化完成。請先到「04_設定」確認班級資料，再執行「建立本週週報範本」。",
    );
  });
}

function OWTR_refreshDashboardView() {
  OWTR_runAction_("重新整理中控台", function () {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const activeWeekId = OWTR_getActiveWeekId_() || OWTR_getLatestWeekId_();

    OWTR_ensureMvp03Structure_(ss);
    OWTR_setupDashboardSheet_(ss);
    OWTR_setupIndexSheet_(ss);

    if (activeWeekId) {
      OWTR_refreshDashboardForWeek_(activeWeekId);
      OWTR_updateWeeklyIndex_();
    }

    OWTR_alert_("中控台已重新整理，不會清空週報資料與三端文案。");
  });
}

function OWTR_setupDashboardSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.DASHBOARD);
  sheet.clear();

  const values = [
    ["一週一報三端分流｜班級週報中控台", "", "", "", "", ""],
    [
      "操作提示",
      "建立本週週報 → 填寫週報資料 → 產生三端文案 → 產生 Docs / 班網素材 → 手動發布三端或一鍵發布協作平台",
      "",
      "",
      "",
      "",
    ],
    ["", "", "", "", "", ""],
    ["目前週次ID", "", "", "總狀態", OWTR_CONFIG.STATUS.NOT_CREATED, ""],
    ["日期區間", "", "", "Google Docs", "尚未建立", ""],
    ["班級網站", "尚未設定", "", "照片資料夾", "尚未建立", ""],
    ["", "", "", "", "", ""],
    ["流程狀態", "", "", "", "", ""],
    ["草稿", "⚪ 尚未建立", "三端文案", "⚪ 尚未產生", "Google Docs", "⚪ 尚未產生"],
    ["三端發布", "⚪ 尚未完成", "協作平台", "⚪ 尚未發布", "", ""],
    ["", "", "", "", "", ""],
    ["發布狀態", "", "", "", "", ""],
    ["班級網站", "尚未發布", "", "", "", ""],
    ["LINE提醒", "尚未發布", "", "", "", ""],
    ["FB粉專", "尚未發布", "", "", "", ""],
    ["協作平台", "尚未發布", "", "", "", ""],
    ["", "", "", "", "", ""],
    ["本週流程步驟", "", "", "", "", ""],
    ["1", "建立週報範本", "⚪ 尚未完成", "下一步", "建立或確認本週週報資料列", ""],
    ["2", "填寫週報資料", "⚪ 尚未完成", "", "到 01_週報資料補齊內容", ""],
    ["3", "產生三端文案", "⚪ 尚未完成", "", "在發布面板或選單產生文案", ""],
    ["4", "建立 Docs / 照片資料夾", "⚪ 尚未完成", "", "在發布面板一鍵建立缺少項目", ""],
    ["5", "複製並發布三端", "⚪ 尚未完成", "", "從發布面板複製班網 / LINE / FB 文案", ""],
    ["6", "發布協作平台", "⚪ 尚未完成", "", "送出 Google Chat 協作通知", ""],
  ];

  sheet.getRange(1, 1, values.length, 6).setValues(values);
  sheet.getRange("A1:F1").merge();
  sheet.getRange("B2:F2").merge();
  sheet.getRange("A8:F8").merge();
  sheet.getRange("A12:F12").merge();
  sheet.getRange("A18:F18").merge();

  sheet.getRange("A1")
    .setFontSize(18)
    .setFontWeight("bold")
    .setFontColor("#ffffff")
    .setHorizontalAlignment("center")
    .setBackground("#1f4e79");
  sheet.getRange("A2:F2").setBackground("#eaf3f8");
  sheet.getRange("A4:A6").setFontWeight("bold");
  sheet.getRange("D4:D6").setFontWeight("bold");
  sheet.getRange("A8:F8").setFontWeight("bold").setBackground("#d9eaf7");
  sheet.getRange("A12:F12").setFontWeight("bold").setBackground("#d9eaf7");
  sheet.getRange("A18:F18").setFontWeight("bold").setBackground("#d9eaf7");
  sheet.getRange("A9:F10").setBackground("#f8fafc");
  sheet.getRange("A13:B16").setBackground("#f8fafc");
  sheet.getRange("A19:F24").setBackground("#f8fafc");
  sheet.getRange("E4").setFontWeight("bold");
  sheet.setColumnWidth(1, 150);
  sheet.setColumnWidth(2, 240);
  sheet.setColumnWidth(3, 140);
  sheet.setColumnWidth(4, 150);
  sheet.setColumnWidth(5, 240);
  sheet.setColumnWidth(6, 40);
  sheet.setFrozenRows(1);
}

function OWTR_setupWeeklyDataSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.WEEKLY_DATA);
  sheet.clear();

  const headers = [
    "週次ID",
    "日期區間",
    "本週學習重點",
    "本週班級活動",
    "學生亮點",
    "下週重要提醒",
    "家長配合事項",
    "照片資料夾URL",
    "建立時間",
    "最後更新時間",
  ];

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  OWTR_styleHeader_(sheet, headers.length);
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, headers.length, 180);
  sheet.setColumnWidth(3, 260);
  sheet.setColumnWidth(4, 260);
  sheet.setColumnWidth(5, 260);
  sheet.setColumnWidth(6, 260);
  sheet.setColumnWidth(7, 260);
}

function OWTR_setupCopyOutputSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.COPY_OUTPUT);
  sheet.clear();

  const headers = [
    "週次ID",
    "班網完整版",
    "LINE短提醒版",
    "FB故事版",
    "產生時間",
    "是否確認",
  ];

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  OWTR_styleHeader_(sheet, headers.length);
  sheet.setFrozenRows(1);
  sheet.setColumnWidth(1, 150);
  sheet.setColumnWidth(2, 420);
  sheet.setColumnWidth(3, 360);
  sheet.setColumnWidth(4, 420);
}

function OWTR_setupPublishLogSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.PUBLISH_LOG);
  sheet.clear();

  const headers = [
    "週次ID",
    "班級網站",
    "LINE",
    "FB",
    "協作平台",
    "Google Docs URL",
    "發布備註",
    "最後更新時間",
  ];

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  OWTR_styleHeader_(sheet, headers.length);
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, headers.length, 180);
  sheet.setColumnWidth(6, 360);
}

function OWTR_setupSettingsSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.SETTINGS);
  sheet.clear();

  sheet
    .getRange(1, 1, OWTR_CONFIG.DEFAULT_SETTINGS.length, 3)
    .setValues(OWTR_CONFIG.DEFAULT_SETTINGS);
  OWTR_styleHeader_(sheet, 3);
  sheet.setFrozenRows(1);
  sheet.setColumnWidth(1, 180);
  sheet.setColumnWidth(2, 360);
  sheet.setColumnWidth(3, 420);
}

function OWTR_setupIndexSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.INDEX);
  sheet.clear();

  const headers = [
    "週次ID",
    "日期區間",
    "摘要",
    "Google Docs",
    "照片資料夾",
    "班級網站",
    "發布狀態",
    "最後更新時間",
  ];

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  OWTR_styleHeader_(sheet, headers.length);
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, headers.length, 180);
  sheet.setColumnWidth(3, 420);
  sheet.setColumnWidth(4, 180);
  sheet.setColumnWidth(5, 180);
  sheet.setColumnWidth(6, 180);
  sheet.setColumnWidth(7, 240);
  sheet.setColumnWidth(8, 180);
}

function OWTR_setupWebsiteMaterialsSheet_(ss) {
  const sheet = OWTR_getOrCreateSheet_(ss, OWTR_CONFIG.SHEETS.WEBSITE_MATERIALS);
  sheet.clear();

  const headers = [
    "週次ID",
    "班網標題",
    "摘要",
    "純文字版",
    "HTML貼上版",
    "Google Docs連結",
    "照片資料夾連結",
    "產生時間",
  ];

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  OWTR_styleHeader_(sheet, headers.length);
  sheet.setFrozenRows(1);
  sheet.setColumnWidth(1, 150);
  sheet.setColumnWidth(2, 260);
  sheet.setColumnWidth(3, 360);
  sheet.setColumnWidth(4, 520);
  sheet.setColumnWidth(5, 520);
  sheet.setColumnWidth(6, 240);
  sheet.setColumnWidth(7, 240);
  sheet.setColumnWidth(8, 180);
}

function OWTR_ensureMvp03Structure_(ss) {
  OWTR_ensureSettingsRows_(ss);
  OWTR_ensurePublishLogColumns_(ss);
  OWTR_ensureWebsiteMaterialsSheet_(ss);
}

function OWTR_ensureSettingsRows_(ss) {
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.SETTINGS);
  if (!sheet) return;

  const lastRow = sheet.getLastRow();
  const existingKeys =
    lastRow >= 2
      ? sheet.getRange(2, 1, lastRow - 1, 1).getValues().map(function (row) {
          return row[0];
        })
      : [];

  const missingRows = OWTR_CONFIG.DEFAULT_SETTINGS.slice(1).filter(function (row) {
    return existingKeys.indexOf(row[0]) === -1;
  });

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

function OWTR_ensurePublishLogColumns_(ss) {
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);
  if (!sheet) return;

  const requiredHeaders = [
    "週次ID",
    "班級網站",
    "LINE",
    "FB",
    "協作平台",
    "Google Docs URL",
    "發布備註",
    "最後更新時間",
  ];
  const lastCol = Math.max(sheet.getLastColumn(), 1);
  const currentHeaders = sheet.getRange(1, 1, 1, lastCol).getValues()[0];

  if (currentHeaders.indexOf("協作平台") === -1) {
    sheet.insertColumnAfter(4);
  }

  sheet.getRange(1, 1, 1, requiredHeaders.length).setValues([requiredHeaders]);

  const lastRow = sheet.getLastRow();
  if (lastRow >= 2) {
    const collaborationRange = sheet.getRange(2, 5, lastRow - 1, 1);
    const values = collaborationRange.getValues().map(function (row) {
      return [row[0] || "尚未發布"];
    });
    collaborationRange.setValues(values);
  }

  OWTR_styleHeader_(sheet, requiredHeaders.length);
  sheet.setColumnWidths(1, requiredHeaders.length, 180);
  sheet.setColumnWidth(6, 360);
}

function OWTR_ensureWebsiteMaterialsSheet_(ss) {
  let sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEBSITE_MATERIALS);
  if (!sheet) {
    OWTR_setupWebsiteMaterialsSheet_(ss);
    return;
  }

  const headers = [
    "週次ID",
    "班網標題",
    "摘要",
    "純文字版",
    "HTML貼上版",
    "Google Docs連結",
    "照片資料夾連結",
    "產生時間",
  ];

  if (sheet.getLastRow() === 0 || sheet.getRange(1, 1).getValue() !== "週次ID") {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }

  OWTR_styleHeader_(sheet, headers.length);
  sheet.setFrozenRows(1);
}

/**
 * =========================
 * 建立週報
 * =========================
 */

function OWTR_createCurrentWeekReport() {
  OWTR_runAction_("建立本週週報範本", function () {
    const result = OWTR_createWeekReportByInfo_(OWTR_getCurrentWeekInfo_());
    if (!result) return;

    OWTR_alert_(
      result.existed
        ? "本週週報已存在：" + result.weekId
        : "已建立本週週報範本：" + result.weekId + "\n請到「01_週報資料」填寫內容。",
    );
  });
}

function OWTR_createNextWeekReport() {
  OWTR_runAction_("建立下一週週報範本", function () {
    const result = OWTR_createWeekReportByInfo_(OWTR_getNextWeekInfo_());
    if (!result) return;

    OWTR_alert_(
      result.existed
        ? "下一週週報已存在：" + result.weekId
        : "已建立下一週週報範本：" + result.weekId + "\n請到「01_週報資料」先填入可預備的提醒事項。",
    );
  });
}

function OWTR_createWeekReportByInfo_(weekInfo) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEEKLY_DATA);
  if (!sheet) {
    OWTR_alert_("請先執行「初始化平台」。");
    return null;
  }

  const weekId = weekInfo.weekId;
  const dateRange = weekInfo.dateRange;

  if (OWTR_findRowByWeekId_(sheet, weekId) > 0) {
    OWTR_refreshDashboardForWeek_(weekId);
    return { weekId: weekId, dateRange: dateRange, existed: true };
  }

  const now = new Date();
  const placeholders = OWTR_CONFIG.PLACEHOLDERS;

  sheet.appendRow([
    weekId,
    dateRange,
    placeholders.learningFocus,
    placeholders.activities,
    placeholders.highlights,
    placeholders.reminders,
    placeholders.parentTasks,
    "",
    now,
    now,
  ]);

  OWTR_ensurePublishLogRow_(weekId);
  OWTR_refreshDashboardForWeek_(weekId);
  OWTR_updateWeeklyIndex_();

  return { weekId: weekId, dateRange: dateRange, existed: false };
}

function OWTR_getCurrentWeekInfo_() {
  return OWTR_getWeekInfoForDate_(new Date());
}

function OWTR_getNextWeekInfo_() {
  const nextWeek = new Date();
  nextWeek.setDate(nextWeek.getDate() + 7);
  return OWTR_getWeekInfoForDate_(nextWeek);
}

function OWTR_getWeekInfoForDate_(today) {
  const timezone = OWTR_CONFIG.TIMEZONE;

  const day = today.getDay();
  const diffToMonday = day === 0 ? -6 : 1 - day;

  const monday = new Date(today);
  monday.setDate(today.getDate() + diffToMonday);

  const friday = new Date(monday);
  friday.setDate(monday.getDate() + 4);

  const year = Utilities.formatDate(today, timezone, "yyyy");
  const weekNumber = OWTR_getWeekNumber_(today);

  const weekId = year + "-W" + String(weekNumber).padStart(2, "0");
  const dateRange =
    Utilities.formatDate(monday, timezone, "yyyy/MM/dd") +
    "–" +
    Utilities.formatDate(friday, timezone, "yyyy/MM/dd");

  return {
    weekId,
    dateRange,
  };
}

function OWTR_getWeekNumber_(date) {
  const target = new Date(date.valueOf());
  const dayNumber = (date.getDay() + 6) % 7;
  target.setDate(target.getDate() - dayNumber + 3);

  const firstThursday = new Date(target.getFullYear(), 0, 4);
  const firstDayNumber = (firstThursday.getDay() + 6) % 7;
  firstThursday.setDate(firstThursday.getDate() - firstDayNumber + 3);

  return 1 + Math.round((target - firstThursday) / (7 * 24 * 60 * 60 * 1000));
}

/**
 * =========================
 * 照片資料夾
 * =========================
 */

function OWTR_createPhotoFolderForActiveWeek() {
  OWTR_runAction_("建立本週照片資料夾", function () {
    const activeWeekId = OWTR_getActiveWeekId_();
    if (!activeWeekId) {
      OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
      return;
    }

    const beforeData = OWTR_getWeeklyDataByWeekId_(activeWeekId);
    const hadFolder = beforeData && beforeData.photoFolderUrl;
    const folderUrl = OWTR_createPhotoFolderForWeek_(activeWeekId);
    if (!folderUrl) return;

    OWTR_refreshDashboardForWeek_(activeWeekId);
    OWTR_updateWeeklyIndex_();

    OWTR_alert_((hadFolder ? "本週照片資料夾已存在：\n" : "已建立本週照片資料夾：\n") + folderUrl);
  });
}

function OWTR_createPhotoFolderForWeek_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const weeklySheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEEKLY_DATA);

  if (!weeklySheet) {
    OWTR_alert_("請先執行「初始化平台」。");
    return "";
  }

  const row = OWTR_findRowByWeekId_(weeklySheet, weekId);
  if (row <= 0) {
    OWTR_alert_("找不到週報資料：" + weekId);
    return "";
  }

  const existingUrl = weeklySheet.getRange(row, 8).getValue();
  if (existingUrl) {
    return existingUrl;
  }

  const settings = OWTR_getSettings_();
  const className = settings["班級名稱"] || "班級";
  const rootFolder = OWTR_getOrCreateRootFolder_(settings);

  const folderName = weekId + "_" + className + "_週報照片";
  const folder = rootFolder.createFolder(folderName);
  const folderUrl = folder.getUrl();

  weeklySheet.getRange(row, 8).setValue(folderUrl);
  weeklySheet.getRange(row, 10).setValue(new Date());

  return folderUrl;
}

function OWTR_getOrCreateRootFolder_(settings) {
  const rootFolderId = settings["Drive根資料夾ID"];

  if (rootFolderId) {
    try {
      return DriveApp.getFolderById(rootFolderId);
    } catch (e) {
      OWTR_alert_(
        "「Drive根資料夾ID」無法讀取，系統將自動建立新的根資料夾。\n\n原始錯誤：" +
          e.message,
      );
    }
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const folderName = ss.getName() + "_一週一報資料夾";
  const folder = DriveApp.createFolder(folderName);

  const settingsSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.SETTINGS);
  const row = OWTR_findSettingsRow_("Drive根資料夾ID");
  if (settingsSheet && row > 0) {
    settingsSheet.getRange(row, 2).setValue(folder.getId());
  }

  return folder;
}

/**
 * =========================
 * 產生三端文案
 * =========================
 */

function OWTR_generateThreeChannelCopies() {
  OWTR_runAction_("產生三端文案", function () {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const weeklySheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEEKLY_DATA);
    const outputSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.COPY_OUTPUT);

    if (!weeklySheet || !outputSheet) {
      OWTR_alert_("請先執行「初始化平台」。");
      return;
    }

    const weekId = OWTR_getActiveWeekId_();
    if (!weekId) {
      OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
      return;
    }

    const data = OWTR_getWeeklyDataByWeekId_(weekId);
    if (!data) {
      OWTR_alert_("找不到週報資料：" + weekId);
      return;
    }

    const settings = OWTR_getSettings_();
    const warnings = OWTR_getIncompleteFieldWarnings_(data);

    const websiteCopy = OWTR_buildWebsiteCopy_(data, settings);
    const lineCopy = OWTR_buildLineCopy_(data, settings);
    const fbCopy = OWTR_buildFbCopy_(data, settings);

    const existingRow = OWTR_findRowByWeekId_(outputSheet, weekId);
    const now = new Date();

    if (existingRow > 0) {
      outputSheet
        .getRange(existingRow, 2, 1, 5)
        .setValues([[websiteCopy, lineCopy, fbCopy, now, "否"]]);
    } else {
      outputSheet.appendRow([weekId, websiteCopy, lineCopy, fbCopy, now, "否"]);
    }

    OWTR_refreshDashboardForWeek_(weekId);
    OWTR_updateWeeklyIndex_();

    let message = "已產生三端文案。請到「02_三端文案」查看。";
    if (warnings.length > 0) {
      message +=
        "\n\n提醒：下列欄位仍是預設提示文字或尚未填寫，建議補齊後再正式發布：\n- " +
        warnings.join("\n- ");
    }
    OWTR_alert_(message);
  });
}

function OWTR_buildWebsiteCopy_(data, settings) {
  const className = settings["班級名稱"] || "班級";
  const teacherName = settings["導師名稱"] || "導師";
  const classWebsiteUrl = settings["班級網站網址"] || "";

  return [
    "【" + className + "｜" + data.weekId + " 班級週報】",
    "",
    "日期區間：" + data.dateRange,
    "",
    "一、本週學習重點",
    OWTR_normalizeText_(data.learningFocus),
    "",
    "二、本週班級活動",
    OWTR_normalizeText_(data.activities),
    "",
    "三、學生亮點",
    OWTR_normalizeText_(data.highlights),
    "",
    "四、下週重要提醒",
    OWTR_normalizeText_(data.reminders),
    "",
    "五、家長配合事項",
    OWTR_normalizeText_(data.parentTasks),
    "",
    "六、照片與相關連結",
    data.photoFolderUrl ? "照片資料夾：" + data.photoFolderUrl : "本週尚未建立照片資料夾。",
    classWebsiteUrl ? "班級網站：" + classWebsiteUrl : "",
    "",
    teacherName + " 敬上",
  ]
    .filter(function (line, index, lines) {
      return line !== "" || lines[index - 1] !== "";
    })
    .join("\n")
    .trim();
}

function OWTR_buildLineCopy_(data, settings) {
  const className = settings["班級名稱"] || "班級";
  const websiteUrl = settings["班級網站網址"] || "";

  return [
    "【" + className + " 本週提醒｜" + data.weekId + "】",
    "",
    "一、本週重點",
    OWTR_compactText_(data.learningFocus),
    "",
    "二、下週提醒",
    OWTR_compactText_(data.reminders),
    "",
    "三、家長配合",
    OWTR_compactText_(data.parentTasks),
    "",
    websiteUrl
      ? "完整週報請看班級網站：\n" + websiteUrl
      : "完整週報請見班級網站或老師後續公告。",
  ].join("\n");
}

function OWTR_buildFbCopy_(data, settings) {
  const className = settings["班級名稱"] || "班級";

  return [
    "【" + className + " 班級故事｜" + data.weekId + "】",
    "",
    "這一週，孩子們在學習與班級活動中，持續累積屬於自己的成長。",
    "",
    "本週學習重點：",
    OWTR_compactText_(data.learningFocus),
    "",
    "班級活動與生活觀察：",
    OWTR_compactText_(data.activities),
    "",
    "特別想記錄的是：",
    OWTR_compactText_(data.highlights),
    "",
    "學習不只是完成課本進度，也是在一次次練習、合作與表達中，慢慢長出面對事情的能力。",
    "",
    data.photoFolderUrl ? "本週照片紀錄：\n" + data.photoFolderUrl : "",
  ]
    .join("\n")
    .trim();
}

function OWTR_getIncompleteFieldWarnings_(data) {
  const fields = [
    ["本週學習重點", "learningFocus", data.learningFocus],
    ["本週班級活動", "activities", data.activities],
    ["學生亮點", "highlights", data.highlights],
    ["下週重要提醒", "reminders", data.reminders],
    ["家長配合事項", "parentTasks", data.parentTasks],
  ];

  return fields
    .filter(function (field) {
      return OWTR_isPlaceholderOrBlank_(field[1], field[2]);
    })
    .map(function (field) {
      return field[0];
    });
}

function OWTR_isPlaceholderOrBlank_(key, value) {
  const text = String(value || "").trim();
  const placeholder = String(OWTR_CONFIG.PLACEHOLDERS[key] || "").trim();
  return text === "" || text === placeholder;
}

/**
 * =========================
 * 產生 Google Docs 週報
 * =========================
 */

function OWTR_generateGoogleDocForActiveWeek() {
  OWTR_runAction_("產生 Google Docs 週報", function () {
    const weekId = OWTR_getActiveWeekId_();

    if (!weekId) {
      OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
      return;
    }

    const docUrl = OWTR_createGoogleDocForWeek_(weekId);
    if (!docUrl) return;

    OWTR_refreshDashboardForWeek_(weekId);
    OWTR_updateWeeklyIndex_();

    OWTR_alert_("已產生 Google Docs 週報：\n" + docUrl);
  });
}

function OWTR_createGoogleDocForWeek_(weekId) {
  const existingDocUrl = OWTR_getPublishInfo_(weekId).docUrl;
  if (existingDocUrl) return existingDocUrl;

    const data = OWTR_getWeeklyDataByWeekId_(weekId);
    if (!data) {
      OWTR_alert_("找不到週報資料：" + weekId);
      return "";
    }

    const settings = OWTR_getSettings_();
    const className = settings["班級名稱"] || "班級";
    const title = className + "_" + weekId + "_班級週報";

    const doc = DocumentApp.create(title);
    const body = doc.getBody();
    body.clear();
    body.setMarginTop(54);
    body.setMarginBottom(54);
    body.setMarginLeft(54);
    body.setMarginRight(54);

    OWTR_buildFormalDocBody_(body, data, settings);
    OWTR_buildFormalDocFooter_(doc, settings);

    doc.saveAndClose();

    const docUrl = doc.getUrl();
    OWTR_updatePublishLogDocUrl_(weekId, docUrl);

    return docUrl;
}

function OWTR_buildFormalDocBody_(body, data, settings) {
  const className = settings["班級名稱"] || "班級";
  const teacherName = settings["導師名稱"] || "導師";
  const schoolName = settings["學校名稱"] || "";
  const classWebsiteUrl = settings["班級網站網址"] || "";

  body
    .appendParagraph(className + " 班級週報")
    .setHeading(DocumentApp.ParagraphHeading.TITLE)
    .setAlignment(DocumentApp.HorizontalAlignment.CENTER);

  const dateParagraph = body
    .appendParagraph(data.weekId + "｜" + data.dateRange)
    .setAlignment(DocumentApp.HorizontalAlignment.CENTER);
  OWTR_setParagraphTextColor_(dateParagraph, "#666666");

  if (schoolName) {
    const schoolParagraph = body
      .appendParagraph(schoolName)
      .setAlignment(DocumentApp.HorizontalAlignment.CENTER);
    OWTR_setParagraphTextColor_(schoolParagraph, "#666666");
  }

  body.appendHorizontalRule();

  OWTR_appendDocSection_(body, "一、本週學習重點", data.learningFocus);
  OWTR_appendDocSection_(body, "二、本週班級活動", data.activities);
  OWTR_appendDocSection_(body, "三、學生亮點", data.highlights);
  OWTR_appendDocSection_(body, "四、下週重要提醒", data.reminders);
  OWTR_appendDocSection_(body, "五、家長配合事項", data.parentTasks);

  body
    .appendParagraph("六、照片與相關連結")
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  if (data.photoFolderUrl) {
    const photoParagraph = body.appendParagraph("照片資料夾：");
    photoParagraph.appendText(data.photoFolderUrl).setLinkUrl(data.photoFolderUrl);
  } else {
    body.appendParagraph("本週尚未建立照片資料夾。");
  }

  if (classWebsiteUrl) {
    const siteParagraph = body.appendParagraph("班級網站：");
    siteParagraph.appendText(classWebsiteUrl).setLinkUrl(classWebsiteUrl);
  }

  body.appendParagraph("");
  body.appendHorizontalRule();
  body
    .appendParagraph(teacherName + " 敬上")
    .setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
  const generatedDateParagraph = body
    .appendParagraph(
      Utilities.formatDate(new Date(), OWTR_CONFIG.TIMEZONE, "yyyy/MM/dd"),
    )
    .setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
  OWTR_setParagraphTextColor_(generatedDateParagraph, "#666666");
}

function OWTR_appendDocSection_(body, heading, text) {
  body.appendParagraph(heading).setHeading(DocumentApp.ParagraphHeading.HEADING2);

  const lines = OWTR_normalizeText_(text)
    .split("\n")
    .map(function (line) {
      return line.trim();
    })
    .filter(function (line) {
      return line !== "";
    });

  lines.forEach(function (line) {
    body.appendParagraph(line).setLineSpacing(1.15);
  });
}

function OWTR_buildFormalDocFooter_(doc, settings) {
  const footer = doc.addFooter();
  const className = settings["班級名稱"] || "班級";
  const teacherName = settings["導師名稱"] || "導師";
  const footerParagraph = footer
    .appendParagraph(className + "｜" + teacherName)
    .setAlignment(DocumentApp.HorizontalAlignment.CENTER);
  OWTR_setParagraphTextColor_(footerParagraph, "#888888");
}

/**
 * =========================
 * 班網發布素材
 * =========================
 */

function OWTR_generateWebsitePublishMaterials() {
  OWTR_runAction_("產生班網發布素材", function () {
    const weekId = OWTR_getActiveWeekId_();
    if (!weekId) {
      OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
      return;
    }

    const result = OWTR_generateWebsitePublishMaterialsForWeek_(weekId);
    if (!result) return;

    OWTR_alert_("已產生班網發布素材，請到「06_班網發布素材」查看。");
  });
}

function OWTR_generateWebsitePublishMaterialsForWeek_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  OWTR_ensureWebsiteMaterialsSheet_(ss);

  const data = OWTR_getWeeklyDataByWeekId_(weekId);
  if (!data) {
    OWTR_alert_("找不到週報資料：" + weekId);
    return null;
  }

  const settings = OWTR_getSettings_();
  const publishInfo = OWTR_getPublishInfo_(weekId);
  const websiteCopy = OWTR_getWebsiteCopyForWeek_(weekId) || OWTR_buildWebsiteCopy_(data, settings);
  const title = OWTR_buildWebsiteMaterialTitle_(data, settings);
  const summary = OWTR_makeWeeklySummary_(data);
  const html = OWTR_buildWebsiteHtml_(title, summary, websiteCopy, publishInfo.docUrl, data.photoFolderUrl);
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEBSITE_MATERIALS);
  const existingRow = OWTR_findRowByWeekId_(sheet, weekId);
  const rowValues = [
    weekId,
    title,
    summary,
    websiteCopy,
    html,
    publishInfo.docUrl || "",
    data.photoFolderUrl || "",
    new Date(),
  ];

  let row = existingRow;
  if (existingRow > 0) {
    sheet.getRange(existingRow, 1, 1, rowValues.length).setValues([rowValues]);
  } else {
    sheet.appendRow(rowValues);
    row = sheet.getLastRow();
  }

  OWTR_setLinkCell_(sheet, row, 6, "開啟 Docs", publishInfo.docUrl, "尚未建立");
  OWTR_setLinkCell_(sheet, row, 7, "開啟照片資料夾", data.photoFolderUrl, "尚未建立");

  return {
    title: title,
    summary: summary,
    plainText: websiteCopy,
    html: html,
    docUrl: publishInfo.docUrl || "",
    photoFolderUrl: data.photoFolderUrl || "",
  };
}

function OWTR_getWebsiteCopyForWeek_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const outputSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.COPY_OUTPUT);
  if (!outputSheet) return "";

  const row = OWTR_findRowByWeekId_(outputSheet, weekId);
  if (row <= 0) return "";

  return outputSheet.getRange(row, 2).getValue();
}

function OWTR_buildWebsiteMaterialTitle_(data, settings) {
  const className = settings["班級名稱"] || "班級";
  return className + "｜" + data.weekId + " 班級週報";
}

function OWTR_buildWebsiteHtml_(title, summary, plainText, docUrl, photoFolderUrl) {
  const paragraphs = OWTR_escapeHtml_(plainText)
    .split(/\n{2,}/)
    .map(function (block) {
      return "<p>" + block.replace(/\n/g, "<br>") + "</p>";
    })
    .join("\n");

  const links = [];
  if (docUrl) {
    links.push('<li><a href="' + OWTR_escapeHtml_(docUrl) + '">Google Docs 週報</a></li>');
  }
  if (photoFolderUrl) {
    links.push('<li><a href="' + OWTR_escapeHtml_(photoFolderUrl) + '">本週照片資料夾</a></li>');
  }

  return [
    '<article class="weekly-report">',
    "  <h1>" + OWTR_escapeHtml_(title) + "</h1>",
    '  <p class="weekly-report-summary">' + OWTR_escapeHtml_(summary) + "</p>",
    '  <div class="weekly-report-body">',
    paragraphs,
    "  </div>",
    links.length > 0 ? '  <ul class="weekly-report-links">' + links.join("") + "</ul>" : "",
    "</article>",
  ]
    .filter(function (line) {
      return line !== "";
    })
    .join("\n");
}

/**
 * =========================
 * 協作平台發布
 * =========================
 */

function OWTR_publishToCollaborationPlatform() {
  OWTR_runAction_("一鍵發布到協作平台", function () {
    const weekId = OWTR_getActiveWeekId_();
    if (!weekId) {
      OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
      return;
    }

    const settings = OWTR_getSettings_();
    const isEnabled = String(settings["是否啟用協作平台發布"] || "").trim() === "是";
    const webhookUrl = String(settings["Google Chat Webhook URL"] || "").trim();
    const platformName = String(settings["協作平台名稱"] || "Google Chat").trim();

    if (!isEnabled) {
      OWTR_alert_("尚未啟用協作平台發布。請到「04_設定」將「是否啟用協作平台發布」填為「是」。");
      return;
    }

    if (!webhookUrl) {
      OWTR_alert_("尚未設定 Google Chat Webhook URL。請先到「04_設定」填入 Webhook URL。");
      return;
    }

    if (!OWTR_hasThreeChannelCopies_(weekId)) {
      OWTR_alert_("三端文案尚未產生。請先執行「產生三端文案」，確認內容後再一鍵發布到協作平台。");
      return;
    }

    let data = OWTR_getWeeklyDataByWeekId_(weekId);
    if (!data) {
      OWTR_alert_("找不到週報資料：" + weekId);
      return;
    }

    let publishInfo = OWTR_getPublishInfo_(weekId);
    let docUrl = publishInfo.docUrl;
    if (!docUrl) {
      const shouldCreateDoc = OWTR_confirm_(
        "Google Docs 尚未建立",
        "是否立即建立 Google Docs 週報後再發布到協作平台？",
      );
      if (!shouldCreateDoc) return;
      docUrl = OWTR_createGoogleDocForWeek_(weekId);
      if (!docUrl) return;
    }

    let photoFolderUrl = data.photoFolderUrl;
    if (!photoFolderUrl) {
      const shouldCreateFolder = OWTR_confirm_(
        "照片資料夾尚未建立",
        "是否立即建立本週照片資料夾後再發布到協作平台？",
      );
      if (!shouldCreateFolder) return;
      photoFolderUrl = OWTR_createPhotoFolderForWeek_(weekId);
      if (!photoFolderUrl) return;
      data = OWTR_getWeeklyDataByWeekId_(weekId);
    }

    const materials = OWTR_generateWebsitePublishMaterialsForWeek_(weekId);
    if (!materials) return;

    publishInfo = OWTR_getPublishInfo_(weekId);
    const message = OWTR_buildGoogleChatMessage_(
      weekId,
      data,
      settings,
      publishInfo,
      docUrl,
      photoFolderUrl,
    );

    OWTR_sendGoogleChatWebhook_(webhookUrl, message);
    OWTR_updatePublishLogCollaborationStatus_(weekId, platformName);
    OWTR_refreshDashboardForWeek_(weekId);
    OWTR_updateWeeklyIndex_();

    OWTR_alert_("已發布週報摘要到「" + platformName + "」，並更新發布紀錄。");
  });
}

function OWTR_buildGoogleChatMessage_(weekId, data, settings, publishInfo, docUrl, photoFolderUrl) {
  const className = settings["班級名稱"] || "班級";
  const platformName = settings["協作平台名稱"] || "Google Chat";
  const lines = [
    "【" + className + " 週報協作通知｜" + weekId + "】",
    "日期區間：" + data.dateRange,
    "",
    "摘要：",
    OWTR_makeWeeklySummary_(data),
    "",
    "Google Docs：",
    docUrl || "尚未建立",
    "",
    "照片資料夾：",
    photoFolderUrl || "尚未建立",
    "",
    "三端發布狀態：",
    "班網：" + (publishInfo.websiteStatus || "尚未發布"),
    "LINE：" + (publishInfo.lineStatus || "尚未發布"),
    "FB：" + (publishInfo.fbStatus || "尚未發布"),
    "",
    "協作平台：" + platformName,
  ];

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

function OWTR_sendGoogleChatWebhook_(webhookUrl, message) {
  const response = UrlFetchApp.fetch(webhookUrl, {
    method: "post",
    contentType: "application/json; charset=UTF-8",
    payload: JSON.stringify({ text: message }),
    muteHttpExceptions: true,
  });
  const responseCode = response.getResponseCode();

  if (responseCode < 200 || responseCode >= 300) {
    throw new Error(
      "Google Chat Webhook 發送失敗，HTTP " +
        responseCode +
        "：" +
        response.getContentText(),
    );
  }
}

function OWTR_updatePublishLogCollaborationStatus_(weekId, platformName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);
  if (!sheet) {
    OWTR_alert_("找不到「03_發布紀錄」，請先執行「初始化平台」。");
    return;
  }

  OWTR_ensurePublishLogRow_(weekId);

  const row = OWTR_findRowByWeekId_(sheet, weekId);
  const nowText = Utilities.formatDate(
    new Date(),
    OWTR_CONFIG.TIMEZONE,
    "yyyy/MM/dd HH:mm",
  );
  sheet.getRange(row, 5).setValue("已發布到 " + platformName + " " + nowText);
  sheet.getRange(row, 8).setValue(new Date());
}

/**
 * =========================
 * 本週發布面板
 * =========================
 */

function OWTR_showWeeklyPublishPanel() {
  OWTR_runAction_("開啟本週發布面板", function () {
    const html = HtmlService.createHtmlOutput(OWTR_buildWeeklyPublishPanelHtml_())
      .setTitle("本週發布面板");
    SpreadsheetApp.getUi().showSidebar(html);
  });
}

function OWTR_getWeeklyPublishPanelData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  OWTR_ensureMvp03Structure_(ss);

  const weekId = OWTR_getActiveWeekId_() || OWTR_getLatestWeekId_();
  const settings = OWTR_getSettings_();
  const emptyData = {
    hasWeek: false,
    weekId: "",
    dateRange: "",
    overallStatus: OWTR_CONFIG.STATUS.NOT_CREATED,
    nextAction: "請先建立本週週報範本。",
    classWebsiteUrl: settings["班級網站網址"] || "",
    platformName: settings["協作平台名稱"] || "Google Chat",
    copiesReady: false,
    docReady: false,
    photoReady: false,
    materialsReady: false,
    links: {
      docUrl: "",
      photoFolderUrl: "",
      classWebsiteUrl: settings["班級網站網址"] || "",
    },
    publishInfo: OWTR_getPublishInfo_(""),
    copies: {
      websitePlain: "",
      websiteHtml: "",
      line: "",
      fb: "",
      collaborationPreview: "",
    },
  };

  if (!weekId) return emptyData;

  const weeklyData = OWTR_getWeeklyDataByWeekId_(weekId);
  if (!weeklyData) return emptyData;

  const publishInfo = OWTR_getPublishInfo_(weekId);
  const copies = OWTR_getThreeChannelCopiesForWeek_(weekId);
  const copiesReady = Boolean(copies.website || copies.line || copies.fb);
  const websiteSummary = OWTR_makeWeeklySummary_(weeklyData);
  const websiteTitle = OWTR_buildWebsiteMaterialTitle_(weeklyData, settings);
  const websitePlain =
    copies.website || OWTR_buildWebsiteCopy_(weeklyData, settings);
  const websiteHtml = OWTR_buildWebsiteHtml_(
    websiteTitle,
    websiteSummary,
    websitePlain,
    publishInfo.docUrl,
    weeklyData.photoFolderUrl,
  );
  const collaborationPreview = OWTR_buildGoogleChatMessage_(
    weekId,
    weeklyData,
    settings,
    publishInfo,
    publishInfo.docUrl,
    weeklyData.photoFolderUrl,
  );
  const overallStatus = OWTR_getOverallStatus_(publishInfo, copiesReady);

  return {
    hasWeek: true,
    weekId: weekId,
    dateRange: weeklyData.dateRange,
    overallStatus: overallStatus,
    nextAction: OWTR_getNextActionText_(
      weeklyData,
      publishInfo,
      copiesReady,
    ),
    classWebsiteUrl: settings["班級網站網址"] || "",
    platformName: settings["協作平台名稱"] || "Google Chat",
    copiesReady: copiesReady,
    docReady: Boolean(publishInfo.docUrl),
    photoReady: Boolean(weeklyData.photoFolderUrl),
    materialsReady: copiesReady,
    links: {
      docUrl: publishInfo.docUrl || "",
      photoFolderUrl: weeklyData.photoFolderUrl || "",
      classWebsiteUrl: settings["班級網站網址"] || "",
    },
    publishInfo: publishInfo,
    copies: {
      websitePlain: websitePlain,
      websiteHtml: websiteHtml,
      line: copies.line || "",
      fb: copies.fb || "",
      collaborationPreview: collaborationPreview,
    },
  };
}

function OWTR_getThreeChannelCopiesForWeek_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const outputSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.COPY_OUTPUT);
  if (!outputSheet) {
    return { website: "", line: "", fb: "" };
  }

  const row = OWTR_findRowByWeekId_(outputSheet, weekId);
  if (row <= 0) {
    return { website: "", line: "", fb: "" };
  }

  const values = outputSheet.getRange(row, 1, 1, 6).getValues()[0];
  return {
    website: values[1] || "",
    line: values[2] || "",
    fb: values[3] || "",
  };
}

function OWTR_getNextActionText_(weeklyData, publishInfo, copiesReady) {
  if (!weeklyData) return "請先建立本週週報範本。";
  if (OWTR_getIncompleteFieldWarnings_(weeklyData).length > 0) {
    return "請先補齊 01_週報資料中仍是提示文字的欄位。";
  }
  if (!copiesReady) return "請產生三端文案。";
  if (!publishInfo.docUrl) return "請建立 Google Docs 週報。";
  if (!weeklyData.photoFolderUrl) return "請建立本週照片資料夾。";
  if (!OWTR_isPublished_(publishInfo.websiteStatus)) {
    return "請複製班網素材，貼到班級網站後標記班網已發布。";
  }
  if (!OWTR_isPublished_(publishInfo.lineStatus)) {
    return "請複製 LINE 文案並標記 LINE 已發布。";
  }
  if (!OWTR_isPublished_(publishInfo.fbStatus)) {
    return "請複製 FB 文案並標記 FB 已發布。";
  }
  if (!OWTR_isPublished_(publishInfo.collaborationStatus)) {
    return "可發布到協作平台，讓共備或行政協作空間同步掌握狀態。";
  }
  return "本週發布流程已完成。";
}

function OWTR_buildWeeklyPublishPanelHtml_() {
  return [
    "<!doctype html>",
    "<html>",
    "<head>",
    '<base target="_top">',
    "<style>",
    "body{margin:0;background:#f6f8fb;color:#182230;font-family:Arial,'Noto Sans TC',sans-serif;font-size:13px;}",
    ".app{padding:14px;}",
    ".topbar{display:flex;align-items:flex-start;justify-content:space-between;gap:10px;margin-bottom:12px;}",
    "h1{font-size:18px;line-height:1.25;margin:0;color:#102a43;}",
    ".muted{color:#667085;font-size:12px;line-height:1.45;}",
    ".status{display:inline-flex;align-items:center;justify-content:center;border:1px solid #d6dee8;border-radius:8px;background:#fff;padding:6px 8px;font-weight:700;color:#25364b;white-space:nowrap;}",
    ".notice{border:1px solid #f6d58f;background:#fff8e5;border-radius:8px;padding:10px;margin:10px 0;color:#7a4b00;line-height:1.5;}",
    ".panel{background:#fff;border:1px solid #d9e2ec;border-radius:8px;padding:12px;margin:10px 0;box-shadow:0 1px 2px rgba(16,42,67,.04);}",
    ".panel h2{font-size:15px;margin:0 0 8px;color:#102a43;display:flex;justify-content:space-between;gap:8px;align-items:center;}",
    ".grid{display:grid;grid-template-columns:1fr 1fr;gap:8px;margin:10px 0;}",
    ".field{border:1px solid #e1e8f0;background:#fbfcfe;border-radius:8px;padding:8px;min-width:0;}",
    ".field label{display:block;color:#667085;font-size:11px;margin-bottom:4px;}",
    ".field div,.field a{font-size:12px;line-height:1.35;word-break:break-word;}",
    "a{color:#1155cc;text-decoration:underline;}",
    "textarea{box-sizing:border-box;width:100%;min-height:130px;border:1px solid #cbd5e1;border-radius:8px;padding:10px;font-size:12px;line-height:1.5;background:#fff;color:#111827;resize:vertical;}",
    "textarea.html{min-height:170px;font-family:Menlo,Consolas,monospace;font-size:11px;}",
    ".actions{display:grid;grid-template-columns:1fr;gap:8px;margin-top:8px;}",
    ".two{grid-template-columns:1fr 1fr;}",
    "button{border:1px solid #b8c7d9;border-radius:8px;background:#fff;color:#17324d;font-weight:700;font-size:12px;line-height:1.2;padding:9px 10px;cursor:pointer;}",
    "button.primary{background:#1f4e79;border-color:#1f4e79;color:#fff;}",
    "button.success{background:#087443;border-color:#087443;color:#fff;}",
    "button.warn{background:#9a5b00;border-color:#9a5b00;color:#fff;}",
    "button:disabled{opacity:.55;cursor:wait;}",
    ".pill{font-size:11px;border-radius:999px;padding:4px 7px;background:#eef2f7;color:#344054;white-space:nowrap;}",
    ".ok{background:#dcfce7;color:#166534;}",
    ".missing{background:#fff7d6;color:#854d0e;}",
    "#toast{position:sticky;bottom:0;margin-top:10px;background:#102a43;color:#fff;border-radius:8px;padding:9px 10px;display:none;}",
    "</style>",
    "</head>",
    "<body>",
    '<div class="app">',
    '<div class="topbar"><div><h1>本週發布面板</h1><div class="muted" id="weekMeta">讀取中...</div></div><div class="status" id="overallStatus">...</div></div>',
    '<div id="root"></div>',
    '<div id="toast"></div>',
    "</div>",
    "<script>",
    "var state=null;",
    "function esc(v){return String(v||'').replace(/[&<>\"']/g,function(c){return {'&':'&amp;','<':'&lt;','>':'&gt;','\"':'&quot;',\"'\":'&#39;'}[c];});}",
    "function link(label,url){return url?'<a href=\"'+esc(url)+'\" target=\"_blank\" rel=\"noopener\">'+esc(label)+'</a>':'<span class=\"muted\">尚未建立</span>';}",
    "function pill(ok,text){return '<span class=\"pill '+(ok?'ok':'missing')+'\">'+esc(text)+'</span>';}",
    "function toast(msg){var el=document.getElementById('toast');el.textContent=msg;el.style.display='block';setTimeout(function(){el.style.display='none';},2600);}",
    "function setBusy(on){Array.prototype.forEach.call(document.querySelectorAll('button'),function(btn){btn.disabled=on;});}",
    "function copyFrom(id,label){var el=document.getElementById(id);if(!el){return;} el.focus();el.select();var value=el.value;if(navigator.clipboard){navigator.clipboard.writeText(value).then(function(){toast(label+'已複製。');}).catch(function(){document.execCommand('copy');toast(label+'已選取，請使用快捷鍵複製。');});}else{document.execCommand('copy');toast(label+'已選取，請使用快捷鍵複製。');}}",
    "function run(fn){setBusy(true);var runner=google.script.run.withSuccessHandler(function(){setBusy(false);load();}).withFailureHandler(function(err){setBusy(false);toast(err&&err.message?err.message:String(err));});if(fn==='OWTR_createCurrentWeekReport'){runner.OWTR_createCurrentWeekReport();}else if(fn==='OWTR_createNextWeekReport'){runner.OWTR_createNextWeekReport();}else if(fn==='OWTR_generateThreeChannelCopies'){runner.OWTR_generateThreeChannelCopies();}else if(fn==='OWTR_generateGoogleDocForActiveWeek'){runner.OWTR_generateGoogleDocForActiveWeek();}else if(fn==='OWTR_createPhotoFolderForActiveWeek'){runner.OWTR_createPhotoFolderForActiveWeek();}else if(fn==='OWTR_markWebsitePublished'){runner.OWTR_markWebsitePublished();}else if(fn==='OWTR_markLinePublished'){runner.OWTR_markLinePublished();}else if(fn==='OWTR_markFbPublished'){runner.OWTR_markFbPublished();}else if(fn==='OWTR_publishToCollaborationPlatform'){runner.OWTR_publishToCollaborationPlatform();}else{setBusy(false);toast('未知操作：'+fn);}}",
    "function load(){google.script.run.withSuccessHandler(function(data){state=data;render(data);}).withFailureHandler(function(err){toast(err&&err.message?err.message:String(err));}).OWTR_getWeeklyPublishPanelData();}",
    "function render(data){document.getElementById('weekMeta').textContent=data.hasWeek?(data.weekId+'｜'+data.dateRange):'尚未建立週報';document.getElementById('overallStatus').textContent=data.overallStatus||'尚未建立';var html='';if(!data.hasWeek){html+='<div class=\"notice\">尚未建立本週週報範本。請先建立本週範本，或建立下一週範本。</div><div class=\"actions\"><button class=\"primary\" onclick=\"run(\\'OWTR_createCurrentWeekReport\\')\">建立本週週報範本</button><button onclick=\"run(\\'OWTR_createNextWeekReport\\')\">建立下一週週報範本</button></div>';document.getElementById('root').innerHTML=html;return;}html+='<div class=\"notice\"><strong>下一步：</strong>'+esc(data.nextAction)+'</div>';html+='<div class=\"grid\"><div class=\"field\"><label>Google Docs</label><div>'+link('開啟 Docs',data.links.docUrl)+'</div></div><div class=\"field\"><label>照片資料夾</label><div>'+link('開啟照片資料夾',data.links.photoFolderUrl)+'</div></div><div class=\"field\"><label>班級網站</label><div>'+link('開啟班級網站',data.links.classWebsiteUrl)+'</div></div><div class=\"field\"><label>文案狀態</label><div>'+(data.copiesReady?pill(true,'已產生三端文案'):pill(false,'尚未產生文案'))+'</div></div></div>';if(!data.copiesReady){html+='<div class=\"notice\">尚未產生三端文案。請先產生文案，再進行複製與發布。</div><div class=\"actions\"><button class=\"primary\" onclick=\"run(\\'OWTR_generateThreeChannelCopies\\')\">產生三端文案</button></div>';}if(!data.docReady||!data.photoReady){html+='<div class=\"panel\"><h2>發布前準備 <span class=\"pill missing\">可補齊</span></h2><div class=\"actions two\">';if(!data.docReady){html+='<button onclick=\"run(\\'OWTR_generateGoogleDocForActiveWeek\\')\">建立 Google Docs</button>';}if(!data.photoReady){html+='<button onclick=\"run(\\'OWTR_createPhotoFolderForActiveWeek\\')\">建立照片資料夾</button>';}html+='</div></div>';}html+=sectionWebsite(data);html+=sectionLine(data);html+=sectionFb(data);html+=sectionCollab(data);document.getElementById('root').innerHTML=html;}",
    "function sectionWebsite(data){return '<div class=\"panel\"><h2>班網版 '+pill(data.copiesReady,'純文字 / HTML')+'</h2><label class=\"muted\">純文字版</label><textarea id=\"websitePlain\">'+esc(data.copies.websitePlain)+'</textarea><label class=\"muted\">HTML 貼上版</label><textarea class=\"html\" id=\"websiteHtml\">'+esc(data.copies.websiteHtml)+'</textarea><div class=\"actions two\"><button onclick=\"copyFrom(\\'websitePlain\\',\\'班網純文字\\')\">複製純文字</button><button onclick=\"copyFrom(\\'websiteHtml\\',\\'班網HTML\\')\">複製HTML</button><button class=\"success\" onclick=\"run(\\'OWTR_markWebsitePublished\\')\">標記班網已發布</button></div><div class=\"muted\">目前狀態：'+esc(data.publishInfo.websiteStatus)+'</div></div>';}",
    "function sectionLine(data){return '<div class=\"panel\"><h2>LINE版 '+pill(Boolean(data.copies.line),'複製發布')+'</h2><textarea id=\"lineCopy\">'+esc(data.copies.line)+'</textarea><div class=\"actions two\"><button onclick=\"copyFrom(\\'lineCopy\\',\\'LINE文案\\')\">複製LINE文案</button><button class=\"success\" onclick=\"run(\\'OWTR_markLinePublished\\')\">標記LINE已發布</button></div><div class=\"muted\">目前狀態：'+esc(data.publishInfo.lineStatus)+'</div></div>';}",
    "function sectionFb(data){return '<div class=\"panel\"><h2>FB版 '+pill(Boolean(data.copies.fb),'複製發布')+'</h2><textarea id=\"fbCopy\">'+esc(data.copies.fb)+'</textarea><div class=\"actions two\"><button onclick=\"copyFrom(\\'fbCopy\\',\\'FB文案\\')\">複製FB文案</button><button class=\"success\" onclick=\"run(\\'OWTR_markFbPublished\\')\">標記FB已發布</button></div><div class=\"muted\">目前狀態：'+esc(data.publishInfo.fbStatus)+'</div></div>';}",
    "function sectionCollab(data){return '<div class=\"panel\"><h2>協作平台 '+pill(Boolean(data.publishInfo.collaborationStatus&&data.publishInfo.collaborationStatus.indexOf('已發布')===0),esc(data.platformName))+'</h2><textarea id=\"collabPreview\">'+esc(data.copies.collaborationPreview)+'</textarea><div class=\"actions\"><button class=\"primary\" onclick=\"run(\\'OWTR_publishToCollaborationPlatform\\')\">發布到協作平台</button></div><div class=\"muted\">目前狀態：'+esc(data.publishInfo.collaborationStatus)+'</div></div>';}",
    "load();",
    "</script>",
    "</body>",
    "</html>",
  ].join("");
}

/**
 * =========================
 * 複製文案側邊欄
 * =========================
 */

function OWTR_showLineCopySidebar() {
  OWTR_runAction_("開啟 LINE 文案複製側邊欄", function () {
    OWTR_showCopySidebar_("LINE");
  });
}

function OWTR_showFbCopySidebar() {
  OWTR_runAction_("開啟 FB 文案複製側邊欄", function () {
    OWTR_showCopySidebar_("FB");
  });
}

function OWTR_showCopySidebar_(channelName) {
  const weekId = OWTR_getActiveWeekId_();
  if (!weekId) {
    OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
    return;
  }

  const copy = OWTR_getCopyForSidebar_(weekId, channelName);
  if (!copy) {
    OWTR_alert_("尚未產生三端文案，請先執行「產生三端文案」。");
    return;
  }

  const title = channelName + " 文案";
  const html = HtmlService.createHtmlOutput(
    OWTR_buildCopySidebarHtml_(title, weekId, copy),
  ).setTitle("複製 " + title);

  SpreadsheetApp.getUi().showSidebar(html);
}

function OWTR_getCopyForSidebar_(weekId, channelName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const outputSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.COPY_OUTPUT);
  if (!outputSheet) return "";

  const row = OWTR_findRowByWeekId_(outputSheet, weekId);
  if (row <= 0) return "";

  if (channelName === "LINE") return outputSheet.getRange(row, 3).getValue();
  if (channelName === "FB") return outputSheet.getRange(row, 4).getValue();
  return "";
}

function OWTR_buildCopySidebarHtml_(title, weekId, copy) {
  const escapedTitle = OWTR_escapeHtml_(title);
  const escapedWeekId = OWTR_escapeHtml_(weekId);
  const escapedCopy = OWTR_escapeHtml_(copy);

  return [
    "<!doctype html>",
    "<html>",
    "<head>",
    '<base target="_top">',
    "<style>",
    "body{font-family:Arial,'Noto Sans TC',sans-serif;margin:0;padding:16px;background:#f8fafc;color:#1f2937;}",
    "h1{font-size:18px;margin:0 0 4px;}",
    ".meta{font-size:12px;color:#6b7280;margin-bottom:12px;}",
    "textarea{box-sizing:border-box;width:100%;height:360px;border:1px solid #cbd5e1;border-radius:8px;padding:12px;font-size:14px;line-height:1.5;resize:vertical;background:#fff;color:#111827;}",
    "button{width:100%;margin-top:12px;border:0;border-radius:8px;background:#1f4e79;color:#fff;font-size:15px;font-weight:700;padding:11px 12px;cursor:pointer;}",
    "button:active{transform:translateY(1px);}",
    ".hint{font-size:12px;color:#6b7280;margin-top:10px;line-height:1.5;}",
    "#status{font-size:13px;margin-top:10px;color:#047857;min-height:18px;}",
    "</style>",
    "</head>",
    "<body>",
    "<h1>" + escapedTitle + "</h1>",
    '<div class="meta">' + escapedWeekId + "</div>",
    '<textarea id="copyText">' + escapedCopy + "</textarea>",
    '<button onclick="copyText()">複製文案</button>',
    '<div id="status"></div>',
    '<div class="hint">若瀏覽器阻擋自動複製，請點進文字框後使用 Ctrl+C 或 Command+C。</div>',
    "<script>",
    "function copyText(){",
    "var textarea=document.getElementById('copyText');",
    "textarea.focus();",
    "textarea.select();",
    "var status=document.getElementById('status');",
    "if(navigator.clipboard&&window.isSecureContext){",
    "navigator.clipboard.writeText(textarea.value).then(function(){status.textContent='已複製到剪貼簿。';}).catch(function(){document.execCommand('copy');status.textContent='已選取文案，請使用快捷鍵複製。';});",
    "}else{",
    "document.execCommand('copy');status.textContent='已選取文案，請使用快捷鍵複製。';",
    "}",
    "}",
    "</script>",
    "</body>",
    "</html>",
  ].join("");
}

/**
 * =========================
 * 發布狀態
 * =========================
 */

function OWTR_markWebsitePublished() {
  OWTR_runAction_("標記班網已發布", function () {
    OWTR_markPublished_("班級網站");
  });
}

function OWTR_markLinePublished() {
  OWTR_runAction_("標記 LINE 已發布", function () {
    OWTR_markPublished_("LINE");
  });
}

function OWTR_markFbPublished() {
  OWTR_runAction_("標記 FB 已發布", function () {
    OWTR_markPublished_("FB");
  });
}

function OWTR_markPublished_(channelName) {
  const weekId = OWTR_getActiveWeekId_();

  if (!weekId) {
    OWTR_alert_("目前沒有週次ID，請先建立本週週報範本。");
    return;
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);
  if (!sheet) {
    OWTR_alert_("找不到「03_發布紀錄」，請先執行「初始化平台」。");
    return;
  }

  OWTR_ensurePublishLogRow_(weekId);

  const row = OWTR_findRowByWeekId_(sheet, weekId);
  const nowText = Utilities.formatDate(
    new Date(),
    OWTR_CONFIG.TIMEZONE,
    "yyyy/MM/dd HH:mm",
  );

  let col = 0;
  if (channelName === "班級網站") col = 2;
  if (channelName === "LINE") col = 3;
  if (channelName === "FB") col = 4;

  if (col === 0) {
    OWTR_alert_("未知的發布管道：" + channelName);
    return;
  }

  sheet.getRange(row, col).setValue("已發布 " + nowText);
  sheet.getRange(row, 8).setValue(new Date());

  OWTR_refreshDashboardForWeek_(weekId);
  OWTR_updateWeeklyIndex_();

  OWTR_alert_(channelName + " 已標記發布。");
}

function OWTR_ensurePublishLogRow_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);

  if (!sheet) return;

  const row = OWTR_findRowByWeekId_(sheet, weekId);
  if (row > 0) return;

  sheet.appendRow([
    weekId,
    "尚未發布",
    "尚未發布",
    "尚未發布",
    "尚未發布",
    "",
    "",
    new Date(),
  ]);
}

function OWTR_updatePublishLogDocUrl_(weekId, docUrl) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);
  if (!sheet) {
    OWTR_alert_("找不到「03_發布紀錄」，請先執行「初始化平台」。");
    return;
  }

  OWTR_ensurePublishLogRow_(weekId);

  const row = OWTR_findRowByWeekId_(sheet, weekId);
  sheet.getRange(row, 6).setValue(docUrl);
  sheet.getRange(row, 8).setValue(new Date());
}

/**
 * =========================
 * 歷週索引
 * =========================
 */

function OWTR_updateWeeklyIndex() {
  OWTR_runAction_("更新歷週索引", function () {
    OWTR_updateWeeklyIndex_();
    OWTR_alert_("歷週索引已更新。");
  });
}

function OWTR_updateWeeklyIndex_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const indexSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.INDEX);
  const weeklySheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEEKLY_DATA);
  const logSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);

  if (!indexSheet || !weeklySheet || !logSheet) return;

  const lastRow = weeklySheet.getLastRow();
  indexSheet
    .getRange(2, 1, Math.max(indexSheet.getMaxRows() - 1, 1), 8)
    .clearContent()
    .clearFormat();
  OWTR_styleHeader_(indexSheet, 8);

  if (lastRow < 2) return;

  const weeklyValues = weeklySheet.getRange(2, 1, lastRow - 1, 10).getValues();
  const settings = OWTR_getSettings_();
  const classWebsiteUrl = settings["班級網站網址"] || "";
  const rows = [];
  const linkRows = [];

  weeklyValues.forEach(function (row) {
    const weekId = row[0];
    if (!weekId) return;

    const data = {
      weekId: weekId,
      dateRange: row[1],
      learningFocus: row[2],
      activities: row[3],
      highlights: row[4],
      reminders: row[5],
      parentTasks: row[6],
      photoFolderUrl: row[7],
      createdAt: row[8],
      updatedAt: row[9],
    };

    const logRow = OWTR_findRowByWeekId_(logSheet, weekId);
    let docUrl = "";
    let publishStatus = "尚未發布";
    let lastUpdated = data.updatedAt || "";

    if (logRow > 0) {
      const logValues = logSheet.getRange(logRow, 1, 1, 8).getValues()[0];
      docUrl = logValues[5];
      publishStatus = OWTR_buildPublishSummary_(
        logValues[1],
        logValues[2],
        logValues[3],
        logValues[4],
      );
      lastUpdated = logValues[7] || lastUpdated;
    }

    rows.push([
      weekId,
      data.dateRange,
      OWTR_makeWeeklySummary_(data),
      docUrl ? "開啟 Docs" : "尚未建立",
      data.photoFolderUrl ? "開啟照片資料夾" : "尚未建立",
      classWebsiteUrl ? "開啟班級網站" : "尚未設定",
      publishStatus,
      lastUpdated,
    ]);
    linkRows.push({
      docUrl: docUrl,
      photoFolderUrl: data.photoFolderUrl,
      classWebsiteUrl: classWebsiteUrl,
    });
  });

  if (rows.length > 0) {
    indexSheet.getRange(2, 1, rows.length, 8).setValues(rows);
    rows.forEach(function (row, index) {
      const sheetRow = index + 2;
      OWTR_setLinkCell_(indexSheet, sheetRow, 4, "開啟 Docs", linkRows[index].docUrl, "尚未建立");
      OWTR_setLinkCell_(
        indexSheet,
        sheetRow,
        5,
        "開啟照片資料夾",
        linkRows[index].photoFolderUrl,
        "尚未建立",
      );
      OWTR_setLinkCell_(
        indexSheet,
        sheetRow,
        6,
        "開啟班級網站",
        linkRows[index].classWebsiteUrl,
        "尚未設定",
      );
    });
  }
}

function OWTR_makeWeeklySummary_(data) {
  const source = [
    OWTR_stripPlaceholder_(data.learningFocus, "learningFocus"),
    OWTR_stripPlaceholder_(data.activities, "activities"),
    OWTR_stripPlaceholder_(data.highlights, "highlights"),
  ]
    .filter(function (text) {
      return text !== "";
    })
    .join(" / ");

  return OWTR_truncateText_(source || "尚未填寫摘要", 180);
}

function OWTR_buildPublishSummary_(websiteStatus, lineStatus, fbStatus, collaborationStatus) {
  return [
    "班網：" + (websiteStatus || "尚未發布"),
    "LINE：" + (lineStatus || "尚未發布"),
    "FB：" + (fbStatus || "尚未發布"),
    "協作：" + (collaborationStatus || "尚未發布"),
  ].join("\n");
}

/**
 * =========================
 * 中控台更新
 * =========================
 */

function OWTR_refreshDashboardForWeek_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName(OWTR_CONFIG.SHEETS.DASHBOARD);
  if (!dashboard) return;

  const data = weekId ? OWTR_getWeeklyDataByWeekId_(weekId) : null;
  const settings = OWTR_getSettings_();
  const classWebsiteUrl = settings["班級網站網址"] || "";
  const publishInfo = OWTR_getPublishInfo_(weekId);
  const hasCopies = OWTR_hasThreeChannelCopies_(weekId);

  if (!data) {
    dashboard.getRange("B4").setValue("");
    dashboard.getRange("B5").setValue("");
    OWTR_setStatusCell_(dashboard, "E4", OWTR_CONFIG.STATUS.NOT_CREATED);
    dashboard.getRange("D10").setValue("⚪ 尚未發布");
    dashboard.getRange("B16").setValue("尚未發布");
    OWTR_updateDashboardFlowSteps_(dashboard, null, publishInfo, hasCopies);
    OWTR_setLinkCell_(dashboard, 5, 5, "開啟 Google Docs", "", "尚未建立");
    OWTR_setLinkCell_(dashboard, 6, 2, "開啟班級網站", classWebsiteUrl, "尚未設定");
    OWTR_setLinkCell_(dashboard, 6, 5, "開啟照片資料夾", "", "尚未建立");
    return;
  }

  dashboard.getRange("B4").setValue(data.weekId);
  dashboard.getRange("B5").setValue(data.dateRange);

  const status = OWTR_getOverallStatus_(publishInfo, hasCopies);
  OWTR_setStatusCell_(dashboard, "E4", status);
  OWTR_setLinkCell_(dashboard, 5, 5, "開啟 Google Docs", publishInfo.docUrl, "尚未建立");
  OWTR_setLinkCell_(dashboard, 6, 2, "開啟班級網站", classWebsiteUrl, "尚未設定");
  OWTR_setLinkCell_(dashboard, 6, 5, "開啟照片資料夾", data.photoFolderUrl, "尚未建立");

  dashboard.getRange("B9").setValue(OWTR_CONFIG.STATUS.DRAFT);
  dashboard
    .getRange("D9")
    .setValue(hasCopies ? OWTR_CONFIG.STATUS.COPY_READY : "⚪ 尚未產生");
  dashboard
    .getRange("F9")
    .setValue(publishInfo.docUrl ? OWTR_CONFIG.STATUS.DOC_READY : "⚪ 尚未產生");
  dashboard
    .getRange("B10")
    .setValue(status === OWTR_CONFIG.STATUS.COMPLETED ? status : "⚪ 尚未完成");
  dashboard
    .getRange("D10")
    .setValue(
      OWTR_isPublished_(publishInfo.collaborationStatus)
        ? OWTR_CONFIG.STATUS.COLLAB_READY
        : "⚪ 尚未發布",
    );

  dashboard.getRange("B13").setValue(publishInfo.websiteStatus || "尚未發布");
  dashboard.getRange("B14").setValue(publishInfo.lineStatus || "尚未發布");
  dashboard.getRange("B15").setValue(publishInfo.fbStatus || "尚未發布");
  dashboard.getRange("B16").setValue(publishInfo.collaborationStatus || "尚未發布");
  OWTR_updateDashboardFlowSteps_(dashboard, data, publishInfo, hasCopies);
}

function OWTR_updateDashboardFlowSteps_(dashboard, data, publishInfo, hasCopies) {
  const hasWeek = Boolean(data);
  const fieldsDone = hasWeek && OWTR_getIncompleteFieldWarnings_(data).length === 0;
  const docAndPhotoDone = hasWeek && Boolean(publishInfo.docUrl) && Boolean(data.photoFolderUrl);
  const threeChannelDone =
    OWTR_isPublished_(publishInfo.websiteStatus) &&
    OWTR_isPublished_(publishInfo.lineStatus) &&
    OWTR_isPublished_(publishInfo.fbStatus);
  const collaborationDone = OWTR_isPublished_(publishInfo.collaborationStatus);
  const rows = [
    hasWeek,
    fieldsDone,
    hasCopies,
    docAndPhotoDone,
    threeChannelDone,
    collaborationDone,
  ].map(function (done) {
    return [done ? "✅ 已完成" : "⚪ 待處理"];
  });

  dashboard.getRange(19, 3, rows.length, 1).setValues(rows);
  dashboard
    .getRange("E19")
    .setValue(hasWeek ? OWTR_getNextActionText_(data, publishInfo, hasCopies) : "請先建立本週週報範本");
}

function OWTR_getOverallStatus_(publishInfo, hasCopies) {
  const isCompleted =
    OWTR_isPublished_(publishInfo.websiteStatus) &&
    OWTR_isPublished_(publishInfo.lineStatus) &&
    OWTR_isPublished_(publishInfo.fbStatus);

  if (isCompleted) return OWTR_CONFIG.STATUS.COMPLETED;
  if (OWTR_isPublished_(publishInfo.collaborationStatus)) {
    return OWTR_CONFIG.STATUS.COLLAB_READY;
  }
  if (publishInfo.docUrl) return OWTR_CONFIG.STATUS.DOC_READY;
  if (hasCopies) return OWTR_CONFIG.STATUS.COPY_READY;
  return OWTR_CONFIG.STATUS.DRAFT;
}

function OWTR_setStatusCell_(sheet, a1Notation, status) {
  const range = sheet.getRange(a1Notation);
  range.setValue(status).setFontWeight("bold").setHorizontalAlignment("center");

  const colorMap = {};
  colorMap[OWTR_CONFIG.STATUS.NOT_CREATED] = "#f3f4f6";
  colorMap[OWTR_CONFIG.STATUS.DRAFT] = "#fff7d6";
  colorMap[OWTR_CONFIG.STATUS.COPY_READY] = "#dcfce7";
  colorMap[OWTR_CONFIG.STATUS.DOC_READY] = "#dbeafe";
  colorMap[OWTR_CONFIG.STATUS.COLLAB_READY] = "#ede9fe";
  colorMap[OWTR_CONFIG.STATUS.COMPLETED] = "#d1fae5";

  range.setBackground(colorMap[status] || "#f3f4f6");
}

function OWTR_setLinkCell_(sheet, row, col, label, url, emptyText) {
  const range = sheet.getRange(row, col);
  range.clearContent();

  if (!url) {
    range
      .setValue(emptyText || "尚未設定")
      .setFontColor("#6b7280")
      .setFontLine("none");
    return;
  }

  const richText = SpreadsheetApp.newRichTextValue()
    .setText(label)
    .setLinkUrl(url)
    .build();
  range
    .setRichTextValue(richText)
    .setFontColor("#1155cc")
    .setFontLine("underline");
}

function OWTR_getPublishInfo_(weekId) {
  const emptyInfo = {
    websiteStatus: "尚未發布",
    lineStatus: "尚未發布",
    fbStatus: "尚未發布",
    collaborationStatus: "尚未發布",
    docUrl: "",
  };
  if (!weekId) return emptyInfo;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const logSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.PUBLISH_LOG);
  if (!logSheet) return emptyInfo;

  const row = OWTR_findRowByWeekId_(logSheet, weekId);
  if (row <= 0) return emptyInfo;

  const values = logSheet.getRange(row, 1, 1, 8).getValues()[0];
  return {
    websiteStatus: values[1] || "尚未發布",
    lineStatus: values[2] || "尚未發布",
    fbStatus: values[3] || "尚未發布",
    collaborationStatus: values[4] || "尚未發布",
    docUrl: values[5] || "",
  };
}

function OWTR_hasThreeChannelCopies_(weekId) {
  if (!weekId) return false;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const outputSheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.COPY_OUTPUT);
  if (!outputSheet) return false;

  return OWTR_findRowByWeekId_(outputSheet, weekId) > 0;
}

function OWTR_isPublished_(status) {
  return String(status || "").indexOf("已發布") === 0;
}

/**
 * =========================
 * 資料讀取工具
 * =========================
 */

function OWTR_getActiveWeekId_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName(OWTR_CONFIG.SHEETS.DASHBOARD);
  if (!dashboard) return "";

  return dashboard.getRange("B4").getValue() || dashboard.getRange("B3").getValue();
}

function OWTR_getLatestWeekId_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEEKLY_DATA);
  if (!sheet || sheet.getLastRow() < 2) return "";

  return sheet.getRange(sheet.getLastRow(), 1).getValue();
}

function OWTR_getWeeklyDataByWeekId_(weekId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.WEEKLY_DATA);
  if (!sheet) return null;

  const row = OWTR_findRowByWeekId_(sheet, weekId);
  if (row <= 0) return null;

  const values = sheet.getRange(row, 1, 1, 10).getValues()[0];

  return {
    weekId: values[0],
    dateRange: values[1],
    learningFocus: values[2],
    activities: values[3],
    highlights: values[4],
    reminders: values[5],
    parentTasks: values[6],
    photoFolderUrl: values[7],
    createdAt: values[8],
    updatedAt: values[9],
  };
}

function OWTR_getSettings_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.SETTINGS);
  const settings = {};

  if (!sheet) return settings;

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return settings;

  const values = sheet.getRange(2, 1, lastRow - 1, 2).getValues();

  values.forEach(function (row) {
    const key = row[0];
    const value = row[1];
    if (key) settings[key] = value;
  });

  return settings;
}

function OWTR_findSettingsRow_(settingName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(OWTR_CONFIG.SHEETS.SETTINGS);
  if (!sheet) return -1;

  const lastRow = sheet.getLastRow();
  if (lastRow < 1) return -1;

  const values = sheet.getRange(1, 1, lastRow, 1).getValues();

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] === settingName) {
      return i + 1;
    }
  }

  return -1;
}

function OWTR_findRowByWeekId_(sheet, weekId) {
  if (!sheet || !weekId) return -1;

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return -1;

  const values = sheet.getRange(2, 1, lastRow - 1, 1).getValues();

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] === weekId) {
      return i + 2;
    }
  }

  return -1;
}

/**
 * =========================
 * 共用工具
 * =========================
 */

function OWTR_getOrCreateSheet_(ss, sheetName) {
  let sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
  }

  return sheet;
}

function OWTR_styleHeader_(sheet, colCount) {
  const range = sheet.getRange(1, 1, 1, colCount);
  range.setFontWeight("bold");
  range.setBackground("#d9eaf7");
  range.setHorizontalAlignment("center");
}

function OWTR_setParagraphTextColor_(paragraph, color) {
  paragraph.editAsText().setForegroundColor(color);
  return paragraph;
}

function OWTR_normalizeText_(text) {
  if (!text) return "尚未填寫。";
  return String(text).trim();
}

function OWTR_compactText_(text) {
  if (!text) return "尚未填寫。";

  return String(text)
    .split("\n")
    .map(function (line) {
      return line.trim();
    })
    .filter(function (line) {
      return line !== "";
    })
    .join("\n");
}

function OWTR_stripPlaceholder_(text, key) {
  if (OWTR_isPlaceholderOrBlank_(key, text)) return "";
  return OWTR_compactText_(text);
}

function OWTR_truncateText_(text, maxLength) {
  const normalized = String(text || "").replace(/\s+/g, " ").trim();
  if (normalized.length <= maxLength) return normalized;
  return normalized.slice(0, maxLength - 1) + "…";
}

function OWTR_escapeHtml_(value) {
  return String(value || "")
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
}

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

function OWTR_confirm_(title, message) {
  const ui = SpreadsheetApp.getUi();
  return ui.alert(title, message, ui.ButtonSet.YES_NO) === ui.Button.YES;
}

function OWTR_runAction_(actionName, callback) {
  try {
    OWTR_ensureMvp03Structure_(SpreadsheetApp.getActiveSpreadsheet());
    callback();
  } catch (e) {
    Logger.log(e && e.stack ? e.stack : e);
    OWTR_alert_(
      actionName +
        "失敗。\n\n請確認已完成初始化、目前週次存在，且授權 Drive / Docs / Spreadsheet 權限。\n\n錯誤訊息：" +
        (e && e.message ? e.message : e),
    );
  }
}