/**
 * v5.17 (Use font color for declaration lock)
 * 定数定義
 */
const ANNICT_API_TOKEN = 'eMDFpHVaI2Fw00NPAp1Lz3cvB4QhXFmPuMTECLGQt-0';
const ANNICT_GRAPHQL_ENDPOINT = 'https://api.annict.com/graphql';
const ID_COPY_TARGET_SHEET_NAME = 'DATE';
const SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();
const url = PropertiesService.getScriptProperties().getProperty('PYTHON_API_URL');
const ALLOWED_SHEET_NAMES = ['2025', '2024', 'DATE'];
const DECLARED_COLOR = "#ff0000"; // 宣言済みの色（赤）
const DEFAULT_COLOR = "#000000"; // デフォルトの文字色（黒）

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const sheetName = sheet.getName();
  const column = range.getColumn();
  const row = range.getRow();
  const editedValue = String(range.getValue()).trim();

  if (ALLOWED_SHEET_NAMES.includes(sheetName) && sheetName !== ID_COPY_TARGET_SHEET_NAME &&
      column === 9 &&
      row >= 3
  ) {
    const annictId = editedValue;

    if (annictId.length > 0 && !isNaN(annictId)) {
      const existingCColumnValue = sheet.getRange(row, 3).getValue();
      if (!existingCColumnValue || String(existingCColumnValue).trim() === "" || String(existingCColumnValue).trim() === "情報が見つかりませんでした" || String(existingCColumnValue).trim() === "一致する新規情報が見つかりませんでした") {
        processSheetRowWithId(sheet, row, annictId);

        const today = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyy/MM/dd");
        const lastRowInB = sheet.getLastRow();
        let bColumnValues = [];
        if (lastRowInB >= 3) {
          bColumnValues = sheet.getRange("B3:B" + lastRowInB).getValues().flat().map(date =>
            date instanceof Date ? Utilities.formatDate(date, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyy/MM/dd") : String(date)
          );
        }

        if (!bColumnValues.includes(today)) {
          sheet.getRange(row, 2).setValue(today);
          Logger.log(`[onEdit] 行 ${row} のB列に本日(${today})を記入しました。`);
        } else {
          Logger.log(`[onEdit] B列に本日(${today})が既に存在するため、日付の記入をスキップしました。`);
        }

        SpreadsheetApp.flush();
      } else {
        Logger.log(`[onEdit] 行 ${row} のC列には既に情報があるため、API呼び出しをスキップしました。`);
      }
      copyIdsFromLargestNumberRowToDate(sheet);
    }
    else if (annictId.length === 0) {
      sheet.getRange(row, 3).clearContent();
      sheet.getRange(row, 10, 1, 4).clearContent();
      sheet.getRange(row, 2).clearContent();
      SpreadsheetApp.flush();

      const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(ID_COPY_TARGET_SHEET_NAME);
      if (dateSheet) {
        const lastRowDate = dateSheet.getLastRow();
        const startRowDate = 2; 
        if (lastRowDate >= startRowDate) {
          dateSheet.getRange(startRowDate, 1, lastRowDate - startRowDate + 1, dateSheet.getLastColumn()).clearContent();
          Logger.log(`[onEdit] I列クリアに伴い、DATEシートの全データ（2行目から最終行まで）をクリアしました。`);
        }
      }
    }
  }

  if (column === 4 && row >= 2 && ALLOWED_SHEET_NAMES.includes(sheetName)) {
    if (editedValue.length > 0) { 
      showSparklineDialog();
    } else { 
      clearSparklinesFromDateSheet();
      Logger.log(`[onEdit] D列のセルがクリアされたため、DATEシートのスパークラインをクリアしました。`);
    }
  }
}

function showSparklineDialog() {
  const htmlOutput = HtmlService.createHtmlOutputFromFile('dialog')
      .setWidth(520)
      .setHeight(520);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, '誰が見る？');
}

function insertSparklineC() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「DATE」という名前のシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const sparklineFormula = '=SPARKLINE({1,0})';
  const SPARKLINE_CHECK_FORMULA = sparklineFormula.toUpperCase();
  const startRow = 2;
  const lastRow = dateSheet.getLastRow();
  let targetRow = startRow;

  const rangeToScan = dateSheet.getRange(startRow, 3, Math.max(1, lastRow - startRow + 1), 3);
  const valuesToScan = rangeToScan.getValues();
  const formulasToScan = rangeToScan.getFormulas();

  for (let i = 0; i < valuesToScan.length; i++) {
    const rowValues = valuesToScan[i];
    const rowFormulas = formulasToScan[i];
    const hasCDataOrSparkline = (String(rowValues[0]).trim() !== '' || String(rowFormulas[0]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasDDataOrSparkline = (String(rowValues[1]).trim() !== '' || String(rowFormulas[1]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasEDataOrSparkline = (String(rowValues[2]).trim() !== '' || String(rowFormulas[2]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    if (!hasCDataOrSparkline && !hasDDataOrSparkline && !hasEDataOrSparkline) {
      targetRow = startRow + i;
      break; 
    }
    if (i === valuesToScan.length - 1) { 
      targetRow = lastRow + 1;
    }
  }
  dateSheet.getRange(targetRow, 3).setFormula(sparklineFormula);
  SpreadsheetApp.flush();
  Logger.log(`DATEシートのC${targetRow}に${sparklineFormula}を記入しました。`);
  return `C列に記入しました。(行 ${targetRow})`;
}

function insertSparklineD() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「DATE」という名前のシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const sparklineFormula = '=SPARKLINE({1,0})';
  const SPARKLINE_CHECK_FORMULA = sparklineFormula.toUpperCase();
  const startRow = 2; 
  const lastRow = dateSheet.getLastRow();
  let targetRow = startRow; 

  const rangeToScan = dateSheet.getRange(startRow, 3, Math.max(1, lastRow - startRow + 1), 3); 
  const valuesToScan = rangeToScan.getValues();
  const formulasToScan = rangeToScan.getFormulas();

  for (let i = 0; i < valuesToScan.length; i++) {
    const rowValues = valuesToScan[i];
    const rowFormulas = formulasToScan[i];
    const hasCDataOrSparkline = (String(rowValues[0]).trim() !== '' || String(rowFormulas[0]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasDDataOrSparkline = (String(rowValues[1]).trim() !== '' || String(rowFormulas[1]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasEDataOrSparkline = (String(rowValues[2]).trim() !== '' || String(rowFormulas[2]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    if (!hasCDataOrSparkline && !hasDDataOrSparkline && !hasEDataOrSparkline) {
      targetRow = startRow + i;
      break;
    }
    if (i === valuesToScan.length - 1) {
      targetRow = lastRow + 1;
    }
  }
  dateSheet.getRange(targetRow, 4).setFormula(sparklineFormula);
  SpreadsheetApp.flush();
  Logger.log(`DATEシートのD${targetRow}に${sparklineFormula}を記入しました。`);
  return `D列に記入しました。(行 ${targetRow})`;
}

function insertSparklineE() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「DATE」という名前のシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const sparklineFormula = '=SPARKLINE({1,0})';
  const SPARKLINE_CHECK_FORMULA = sparklineFormula.toUpperCase();
  const startRow = 2; 
  const lastRow = dateSheet.getLastRow();
  let targetRow = startRow; 

  const rangeToScan = dateSheet.getRange(startRow, 3, Math.max(1, lastRow - startRow + 1), 3); 
  const valuesToScan = rangeToScan.getValues();
  const formulasToScan = rangeToScan.getFormulas();

  for (let i = 0; i < valuesToScan.length; i++) {
    const rowValues = valuesToScan[i];
    const rowFormulas = formulasToScan[i];
    const hasCDataOrSparkline = (String(rowValues[0]).trim() !== '' || String(rowFormulas[0]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasDDataOrSparkline = (String(rowValues[1]).trim() !== '' || String(rowFormulas[1]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasEDataOrSparkline = (String(rowValues[2]).trim() !== '' || String(rowFormulas[2]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    if (!hasCDataOrSparkline && !hasDDataOrSparkline && !hasEDataOrSparkline) {
      targetRow = startRow + i;
      break;
    }
    if (i === valuesToScan.length - 1) {
      targetRow = lastRow + 1;
    }
  }
  dateSheet.getRange(targetRow, 5).setFormula(sparklineFormula);
  SpreadsheetApp.flush();
  Logger.log(`DATEシートのE${targetRow}に${sparklineFormula}を記入しました。`);
  return `E列に記入しました。(行 ${targetRow})`;
}

function insertSparklineDE() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「DATE」という名前のシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const sparklineFormula = '=SPARKLINE({1,0})';
  const SPARKLINE_CHECK_FORMULA = sparklineFormula.toUpperCase();
  const startRow = 2; 
  const lastRow = dateSheet.getLastRow();
  let targetRow = startRow; 

  const rangeToScan = dateSheet.getRange(startRow, 3, Math.max(1, lastRow - startRow + 1), 3); 
  const valuesToScan = rangeToScan.getValues();
  const formulasToScan = rangeToScan.getFormulas();

  for (let i = 0; i < valuesToScan.length; i++) {
    const rowValues = valuesToScan[i];
    const rowFormulas = formulasToScan[i];
    const hasCDataOrSparkline = (String(rowValues[0]).trim() !== '' || String(rowFormulas[0]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasDDataOrSparkline = (String(rowValues[1]).trim() !== '' || String(rowFormulas[1]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasEDataOrSparkline = (String(rowValues[2]).trim() !== '' || String(rowFormulas[2]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    if (!hasCDataOrSparkline && !hasDDataOrSparkline && !hasEDataOrSparkline) {
      targetRow = startRow + i;
      break;
    }
    if (i === valuesToScan.length - 1) {
      targetRow = lastRow + 1;
    }
  }
  dateSheet.getRange(targetRow, 4).setFormula(sparklineFormula); 
  dateSheet.getRange(targetRow, 5).setFormula(sparklineFormula); 
  SpreadsheetApp.flush();
  Logger.log(`DATEシートのD${targetRow}とE${targetRow}に${sparklineFormula}を記入しました。`);
  return `D列とE列に記入しました。(行 ${targetRow})`;
}

function insertSparklineCE() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「DATE」という名前のシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const sparklineFormula = '=SPARKLINE({1,0})';
  const SPARKLINE_CHECK_FORMULA = sparklineFormula.toUpperCase();
  const startRow = 2; 
  const lastRow = dateSheet.getLastRow();
  let targetRow = startRow; 

  const rangeToScan = dateSheet.getRange(startRow, 3, Math.max(1, lastRow - startRow + 1), 3); 
  const valuesToScan = rangeToScan.getValues();
  const formulasToScan = rangeToScan.getFormulas();

  for (let i = 0; i < valuesToScan.length; i++) {
    const rowValues = valuesToScan[i];
    const rowFormulas = formulasToScan[i];
    const hasCDataOrSparkline = (String(rowValues[0]).trim() !== '' || String(rowFormulas[0]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasDDataOrSparkline = (String(rowValues[1]).trim() !== '' || String(rowFormulas[1]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasEDataOrSparkline = (String(rowValues[2]).trim() !== '' || String(rowFormulas[2]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    if (!hasCDataOrSparkline && !hasDDataOrSparkline && !hasEDataOrSparkline) {
      targetRow = startRow + i;
      break;
    }
    if (i === valuesToScan.length - 1) {
      targetRow = lastRow + 1;
    }
  }
  dateSheet.getRange(targetRow, 3).setFormula(sparklineFormula); 
  dateSheet.getRange(targetRow, 5).setFormula(sparklineFormula); 
  SpreadsheetApp.flush();
  Logger.log(`DATEシートのC${targetRow}とE${targetRow}に${sparklineFormula}を記入しました。`);
  return `C列とE列に記入しました。(行 ${targetRow})`;
}

function insertSparklineCD() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「DATE」という名前のシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const sparklineFormula = '=SPARKLINE({1,0})';
  const SPARKLINE_CHECK_FORMULA = sparklineFormula.toUpperCase();
  const startRow = 2; 
  const lastRow = dateSheet.getLastRow();
  let targetRow = startRow; 

  const rangeToScan = dateSheet.getRange(startRow, 3, Math.max(1, lastRow - startRow + 1), 3); 
  const valuesToScan = rangeToScan.getValues();
  const formulasToScan = rangeToScan.getFormulas();

  for (let i = 0; i < valuesToScan.length; i++) {
    const rowValues = valuesToScan[i];
    const rowFormulas = formulasToScan[i];
    const hasCDataOrSparkline = (String(rowValues[0]).trim() !== '' || String(rowFormulas[0]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasDDataOrSparkline = (String(rowValues[1]).trim() !== '' || String(rowFormulas[1]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    const hasEDataOrSparkline = (String(rowValues[2]).trim() !== '' || String(rowFormulas[2]).toUpperCase() === SPARKLINE_CHECK_FORMULA);
    if (!hasCDataOrSparkline && !hasDDataOrSparkline && !hasEDataOrSparkline) {
      targetRow = startRow + i;
      break;
    }
    if (i === valuesToScan.length - 1) {
      targetRow = lastRow + 1;
    }
  }
  dateSheet.getRange(targetRow, 3).setFormula(sparklineFormula); 
  dateSheet.getRange(targetRow, 4).setFormula(sparklineFormula); 
  SpreadsheetApp.flush();
  Logger.log(`DATEシートのC${targetRow}とD${targetRow}に${sparklineFormula}を記入しました。`);
  return `C列とD列に記入しました。(行 ${targetRow})`;
}

function closeDialog() {
}

function processSheetRowWithId(sheet, row, annictId) {
  const work = searchAnnictAnimeByIdGraphQL(annictId);

  if (work) {
    const outputData = extractWorkData(work);
    sheet.getRange(row, 3).setValue(outputData[0]); 
    sheet.getRange(row, 10, 1, 4).setValues([outputData.slice(1)]); 
    Logger.log(`[processSheetRowWithId] ID "${annictId}" の作品情報が見つかりました: ${work.title}`);
  } else {
    sheet.getRange(row, 3).setValue("情報が見つかりませんでした"); 
    sheet.getRange(row, 10, 1, 4).clearContent(); 
    Logger.log(`[processSheetRowWithId] ID "${annictId}" のアニメ情報が見つかりませんでした。`);
  }
}

function copyIdsFromLargestNumberRowToDate(sourceSheet) {
  SpreadsheetApp.flush();

  const targetSheet = SPREADSHEET.getSheetByName(ID_COPY_TARGET_SHEET_NAME);
  if (!targetSheet) {
    Logger.log(`[copyIdsFromLargestNumberRowToDate] ターゲットシート「${ID_COPY_TARGET_SHEET_NAME}」が見つかりません。`);
    return;
  }

  const lastRow = sourceSheet.getLastRow();
  if (lastRow < 3) {
    Logger.log(`[copyIdsFromLargestNumberRowToDate] コピー元シート「${sourceSheet.getName()}」にデータがありません (3行目以降)。`);
    targetSheet.getRange("A2:B").clearContent();
    return;
  }

  const aColumnRange = sourceSheet.getRange(3, 1, lastRow - 2, 1); 
  const aColumnData = aColumnRange.getValues();

  let maxNumber = -Infinity;
  let startRowToCopy = -1; 

  for (let i = 0; i < aColumnData.length; i++) {
    const cellValue = aColumnData[i][0];
    const currentRowAbsolute = i + 3; 

    if (typeof cellValue === 'number' && !isNaN(cellValue) && cellValue > maxNumber) {
      maxNumber = cellValue;
      startRowToCopy = currentRowAbsolute;
    }
  }

  if (startRowToCopy === -1) {
    Logger.log(`[copyIdsFromLargestNumberRowToDate] コピー元シート「${sourceSheet.getName()}」のA列に有効な数字が見つかりませんでした。`);
    targetSheet.getRange("A2:B").clearContent();
    return;
  }

  const dataToCopy = [];
  const sourceRangeToCopy = sourceSheet.getRange(startRowToCopy, 3, lastRow - startRowToCopy + 1, 7); 
  const sourceData = sourceRangeToCopy.getValues();

  sourceData.forEach(row => {
    const title = String(row[0] || '').trim(); 
    const annictId = String(row[6] || '').trim(); 

    if (annictId !== "" && !isNaN(annictId) && title !== "") { 
      dataToCopy.push([title, annictId]); 
    } else if (annictId !== "" || title !== "") { 
      Logger.log(`[copyIdsFromLargestNumberRowToDate] 無効なデータが見つかりました（C列: ${title}, I列: ${annictId}）。スキップします。`);
    }
  });

  targetSheet.getRange("A2:B").clearContent(); 

  if (dataToCopy.length > 0) {
    targetSheet.getRange(2, 1, dataToCopy.length, 2).setValues(dataToCopy);
    Logger.log(`[copyIdsFromLargestNumberToDate] ${dataToCopy.length} 件のタイトルとIDを「${ID_COPY_TARGET_SHEET_NAME}」シートにコピーしました。`);
  } else {
    Logger.log(`[copyIdsFromLargestNumberToDate] コピー元シート「${sourceSheet.getName()}」のC列とI列に、A列最大値の行以降でコピーする有効なタイトルとIDが見つかりませんでした。`);
  }
}

function clearSparklinesFromDateSheet() {
  const dateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATE');
  if (!dateSheet) {
    Logger.log("エラー: 「DATE」という名前のシートが見つかりませんでした。スパークラインのクリアをスキップします。");
    return;
  }

  const startRow = 2; 
  const lastRow = dateSheet.getLastRow();
  const SPARKLINE_CHECK_FORMULA = '=SPARKLINE({1,0})'.toUpperCase(); 

  if (lastRow < startRow) {
    Logger.log("DATEシートにクリアするスパークラインのデータがありません。");
    return;
  }

  const rangeToScan = dateSheet.getRange(startRow, 3, lastRow - startRow + 1, 3); 
  const formulasToScan = rangeToScan.getFormulas(); 

  const cellsToClear = []; 

  for (let i = 0; i < formulasToScan.length; i++) {
    const rowFormulas = formulasToScan[i];
    for (let j = 0; j < rowFormulas.length; j++) {
      const cellFormula = String(rowFormulas[j]).toUpperCase();
      if (cellFormula === SPARKLINE_CHECK_FORMULA) {
        const actualRow = startRow + i; 
        const actualCol = 3 + j; 
        cellsToClear.push(dateSheet.getRange(actualRow, actualCol));
      }
    }
  }

  if (cellsToClear.length > 0) {
    for (const range of cellsToClear) {
      range.clearContent();
    }
    SpreadsheetApp.flush(); 
    Logger.log(`${cellsToClear.length} 個のスパークラインをDATEシートからクリアしました。`);
  } else {
    Logger.log("DATEシートにクリア対象のスパークラインは見つかりませんでした。");
  }
}


function getMaxSessionFromSheet(sheetName) {
  const sheet = SPREADSHEET.getSheetByName(sheetName);
  if (!sheet) {
    return null;
  }
  const lastRow = sheet.getLastRow();
  if (lastRow < 3) {
    return null;
  }

  const aColumnValues = sheet.getRange("A3:A" + lastRow).getValues();
  let maxSession = 0;
  let startRow = -1;

  for (let i = 0; i < aColumnValues.length; i++) {
    const id = Number(aColumnValues[i][0]);
    if (!isNaN(id) && id > maxSession) {
      maxSession = id;
      startRow = i + 3;
    }
  }

  if (startRow === -1) {
    return null;
  }

  const titles = [];
  if (maxSession > 0) {
    const firstTitleCell = sheet.getRange(startRow, 3).getValue();
    if (firstTitleCell === '') {
      titles.push("開催待ち");
    } else {
      const titleValues = sheet.getRange(startRow, 3, lastRow - startRow + 1, 1).getValues();
      for (const row of titleValues) {
        const title = row[0];
        if (title === '') {
          break;
        }
        titles.push(title);
      }
    }
  }
  return { session: maxSession, titles: titles };
}


function doGet(e) {
  const action = e.parameter.action;

  if (action === 'getAllSessions') {
    // const SCRIPT_VERSION = "5.31";
    // const debug_logs = [`Running version ${SCRIPT_VERSION} at ${new Date().toISOString()}`];
    
    // let debugSheet = SPREADSHEET.getSheetByName("Debug");
    // if (!debugSheet) {
    //   debugSheet = SPREADSHEET.insertSheet("Debug");
    // }
    // debugSheet.clear();
    // const writeDebug = () => {
    //   if(debugSheet) {
    //     debugSheet.getRange(1, 1, debug_logs.length, 1).setValues(debug_logs.map(l => [l]));
    //   }
    // }

    try {
      const sheetNames = ["2025", "2024"];
      const allSessionsMap = new Map();
      // debug_logs.push("Sheet names to process: " + JSON.stringify(sheetNames));

      for (const sheetName of sheetNames) {
        try {
            const sheet = SPREADSHEET.getSheetByName(sheetName);
            if (!sheet) {
                // debug_logs.push(`Sheet '${sheetName}' not found. Skipping.`);
                continue;
            }
            
            const lastRow = sheet.getLastRow();
            if (lastRow < 3) {
                // debug_logs.push(`Sheet '${sheetName}' has no data below row 2. Skipping.`);
                continue;
            }

            // debug_logs.push(`Processing sheet '${sheetName}'. Last row: ${lastRow}`);
            const dataRange = sheet.getRange("A3:I" + lastRow);
            const values = dataRange.getValues();
            
            let currentSessionNum = -1;

            values.forEach((row, index) => {
              const sessionNumCell = row[0];
              const title = row[2];
              const workId = row[8];

              const parsedSessionNum = Number(sessionNumCell);
              if (!isNaN(parsedSessionNum) && String(sessionNumCell).trim() !== '') {
                  currentSessionNum = parsedSessionNum;
              }

              if (currentSessionNum !== -1 && title && workId) {
                const sessionKey = `${sheetName}-${currentSessionNum}`; 
                if (!allSessionsMap.has(sessionKey)) {
                  allSessionsMap.set(sessionKey, {
                    session: currentSessionNum,
                    works: []
                  });
                }
                allSessionsMap.get(sessionKey).works.push({ title: title, workId: workId });
              }
            });
            // debug_logs.push(`Finished processing sheet '${sheetName}'. Found ${allSessionsMap.size} total unique sessions so far.`);

        } catch (sheetError) {
            // debug_logs.push(`Error processing sheet ${sheetName}: ${sheetError.toString()}`);
        }
      }

      const sessionsArray = Array.from(allSessionsMap.values()).sort((a, b) => b.session - a.session);
      // debug_logs.push("Successfully processed all sheets. Returning " + sessionsArray.length + " sessions.");
      // writeDebug();
      return createJsonResponse({ sessions: sessionsArray });

    } catch (error) {
      // debug_logs.push("FATAL ERROR in getAllSessions: " + error.toString());
      // writeDebug();
      return createJsonResponse({ error: error.message }, false);
    }
  }

  if (action === 'getMembers') {
    try {
      const memberSheet = SPREADSHEET.getSheetByName("member");
      if (!memberSheet) {
        return createJsonResponse({ error: "Sheet 'member' not found."}, false);
      }
      const lastRow = memberSheet.getLastRow();
      if (lastRow < 2) {
        return createJsonResponse({ members: [] });
      }
      const members = memberSheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
      return createJsonResponse({ members: members });
    } catch (error) {
      return createJsonResponse({ error: error.message }, false);
    }
  }

  if (action === 'getMaxSessionNumber') {
    try {
      const data2025 = getMaxSessionFromSheet("2025");
      const data2024 = getMaxSessionFromSheet("2024");

      let allTitles = [];
      let primarySession = 0;

      if (data2025 && data2025.titles.length > 0) {
        primarySession = data2025.session;
        const titlesWithSession = data2025.titles.map(title => `[${data2025.session}ふさ目] ${title}`);
        allTitles.push(...titlesWithSession);
      }

      if (data2024 && data2024.titles.length > 0) {
        if (primarySession === 0) {
          primarySession = data2024.session;
        }
        const titlesWithSession = data2024.titles.map(title => `[${data2024.session}ふさ目] ${title}`);
        allTitles.push(...titlesWithSession);
      }

      return createJsonResponse({ maxSession: primarySession, titles: allTitles });

    } catch (error) {
      return createJsonResponse({ error: error.message }, false);
    }
  }

  if (action === 'getSessionStatus') {
    try {
      const sheetNames = ["2025", "2024"];
      let maxSession = 0;
      let sessionRow = -1;
      let sessionSheet = null;
      let lastRow = 0;

      // First, find the sheet and session with the highest number overall
      for (const sheetName of sheetNames) {
        const sheet = SPREADSHEET.getSheetByName(sheetName);
        if (!sheet) continue;

        const currentLastRow = sheet.getLastRow();
        if (currentLastRow < 3) continue;

        const aColumnValues = sheet.getRange("A3:A" + currentLastRow).getValues();
        for (let i = 0; i < aColumnValues.length; i++) {
          const id = Number(aColumnValues[i][0]);
          if (!isNaN(id) && id > maxSession) {
            maxSession = id;
            sessionSheet = sheet;
            lastRow = currentLastRow;
          }
        }
      }

      if (sessionSheet) {
        const aColumnValues = sessionSheet.getRange("A3:A" + lastRow).getValues();
        for (let i = 0; i < aColumnValues.length; i++) {
            if (Number(aColumnValues[i][0]) === maxSession) {
                sessionRow = i + 3;
                break;
            }
        }
      }

      if (sessionRow !== -1) {
        const sessionCell = sessionSheet.getRange(sessionRow, 1);
        const isDeclared = sessionCell.getFontColor() !== DEFAULT_COLOR;
        
        const works = [];
        const dataRange = sessionSheet.getRange(sessionRow, 3, lastRow - sessionRow + 1, 7); // C to I
        const dataValues = dataRange.getValues();

        for (const row of dataValues) {
          const title = row[0]; // Relative to C, so C is 0
          const workId = row[6]; // Relative to C, so I is 6
          if (title === '' || !workId) {
            break;
          }
          works.push({ title: title, workId: workId });
        }
        
        const titles = works.map(work => work.title);

        return createJsonResponse({ isDeclared: isDeclared, session: maxSession, titles: titles, works: works });
      } else {
        // No sessions found in any sheet
        return createJsonResponse({ isDeclared: false, session: 0, titles: [], works: [] });
      }

    } catch (error) {
      return createJsonResponse({ error: error.message }, false);
    }
  }

  return createJsonResponse({ error: 'Invalid action' }, false);
}

function createJsonResponse(data, isSuccess = true) {
  const response = isSuccess ? { success: true, data: data } : { success: false, error: data.error };
  const output = JSON.stringify(response);
  return ContentService.createTextOutput(output)
    .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  const debugSheet = SPREADSHEET.getSheetByName("Debug");
  try {
    if (debugSheet) {
      debugSheet.appendRow([new Date(), JSON.stringify(e.parameter), e.postData ? e.postData.type : 'N/A', e.postData ? e.postData.contents : 'N/A']);
    }
  } catch (e) {}

  try {
    let params = e.parameter;

    if (e.postData && (!params || Object.keys(params).length === 0)) {
      if (e.postData.type === 'application/json') {
        params = JSON.parse(e.postData.contents);
      } else { 
        const contents = e.postData.contents;
        params = contents.split('&').reduce((acc, part) => {
          const [key, value] = part.split('=', 2);
          if (key) {
            acc[decodeURIComponent(key.replace(/\+/g, ' '))] = decodeURIComponent((value || '').replace(/\+/g, ' '));
          }
          return acc;
        }, {});
      }
    }

    const action = params.action;
    const type = params.type;

    if (action === 'addWork') {
      const workId = params.workId;
      const proposers = JSON.parse(params.proposer || '[]');
      const viewers = JSON.parse(params.viewers || '[]');
      return handleAddWork(workId, proposers, viewers);
    }
    
    if (action === 'favoriteCharacter') {
      const characterName = params.characterName;
      const animeTitles = JSON.parse(params.animeTitles || '[]');
      const currentUser = params.currentUser;
      const allowOverwrite = params.allowOverwrite === 'true';
      return recordFavoriteCharacter(characterName, animeTitles, currentUser, allowOverwrite);
    }

    if (action === 'declareSession') {
      return handleDeclareSession();
    }

    if (type === "get_viewer_status") {
      return handleGetViewerStatus();
    }

    return ContentService.createTextOutput(
        JSON.stringify({ success: false, message: "Invalid or unrecognized action/type.", params: params }) 
    ).setMimeType(ContentService.MimeType.JSON);

  } catch (err) {
    if (debugSheet) {
      debugSheet.appendRow([new Date(), 'ERROR', err.toString(), err.stack, 'Raw Request Body:', e.postData ? e.postData.contents : 'N/A']);
    }
    return ContentService.createTextOutput(
      JSON.stringify({ success: false, message: err.message, debugInfo: { rawContents: e.postData ? e.postData.contents : 'N/A' } })
    ).setMimeType(ContentService.MimeType.JSON);
  }
}

function handleDeclareSession() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(15000)) {
    return createJsonResponse({ message: "現在他のユーザーが処理中です。しばらくしてから再度お試しください。" }, false);
  }

  try {
    const sheet = SPREADSHEET.getSheetByName("2025");
    if (!sheet) {
      return createJsonResponse({ message: "Sheet '2025' not found."}, false);
    }

    const lastRow = sheet.getLastRow();
    if (lastRow < 3) {
      return createJsonResponse({ message: "No data found in sheet '2025'." }, false);
    }

    const aColumnRange = sheet.getRange(3, 1, lastRow - 2, 1);
    const aColumnValues = aColumnRange.getValues();
    let maxId = -1;
    let startRow = -1;

    for (let i = 0; i < aColumnValues.length; i++) {
      const id = aColumnValues[i][0];
      if (typeof id === 'number' && id > maxId) {
        maxId = id;
        startRow = i + 3;
      }
    }

    if (startRow === -1) {
      return createJsonResponse({ message: "No valid session ID found in column A." }, false);
    }

    const sessionCell = sheet.getRange(startRow, 1);
    if (sessionCell.getFontColor() !== DEFAULT_COLOR) {
      return createJsonResponse({ message: `「${maxId}ふさ目」は既に開催宣言されています。` }, false);
    }

    const firstTitleCell = sheet.getRange(startRow, 3).getValue();
    if (firstTitleCell === '') {
      return createJsonResponse({ message: `「${maxId}ふさ目」には開催宣言できる作品が登録されていません。` }, false);
    }

    sessionCell.setFontColor(DECLARED_COLOR);
    SpreadsheetApp.flush();

    const allData = sheet.getRange(startRow, 1, lastRow - startRow + 1, 7).getValues();
    const allFormulas = sheet.getRange(startRow, 1, lastRow - startRow + 1, 7).getFormulas();
    
    const sentMessages = [];

    for (let i = 0; i < allData.length; i++) {
      const currentRowData = allData[i];
      const currentRowFormulas = allFormulas[i];
      const aColumnValue = currentRowData[0];
      const cColumnValue = currentRowData[2];

      if (cColumnValue === '') {
        break;
      }
      
      const viewers = [];
      const sparklineFormula = '=SPARKLINE({1,0})';
      if (currentRowFormulas[4].toUpperCase() !== sparklineFormula) viewers.push('いいの');
      if (currentRowFormulas[5].toUpperCase() !== sparklineFormula) viewers.push('おおつか');
      if (currentRowFormulas[6].toUpperCase() !== sparklineFormula) viewers.push('たかだ');

      let viewerText = viewers.length > 0 ? (viewers.length === 3 ? '全員' : viewers.sort().join('＆')) : '';
      const titlePart = viewerText ? `${viewerText} ${cColumnValue}` : cColumnValue;
      let messageToSend = aColumnValue ? `${aColumnValue}ふさ目開催\n${titlePart}` : titlePart;

      sendDataToPython({
        type: "session_declaration",
        message: messageToSend,
        a_column_data: aColumnValue,
        c_column_data: cColumnValue,
        viewers: viewers,
        timestamp: new Date().toISOString()
      });
      
      sentMessages.push(cColumnValue);
    }

    if (sentMessages.length > 0) {
      return createJsonResponse({ message: `${sentMessages.length}件の開催宣言を送信しました。`, details: sentMessages });
    } else {
      sessionCell.setFontColor(DEFAULT_COLOR);
      return createJsonResponse({ message: "送信対象のデータが見つかりませんでした。" }, false);
    }
  } finally {
    lock.releaseLock();
  }
}


function handleGetViewerStatus() {
    const statusResult = getAnimeViewerStatusForMultipleUsers();
    updateTestSheetMaxId();
    return createJsonResponse({ message: "視聴ステータスを更新しました。", details: statusResult });
}

function handleAddWork(workId, proposers, viewers) {
  const sheetName = "2025";
  const sheet = SPREADSHEET.getSheetByName(sheetName);
  const spreadsheetTimeZone = SPREADSHEET.getSpreadsheetTimeZone();

  const lastRow = sheet.getLastRow();
  const iColumnValues = sheet.getRange("I1:I" + (lastRow || 1)).getValues();
  let lastRowWithId = 0;
  for (let i = iColumnValues.length - 1; i >= 0; i--) {
    if (iColumnValues[i][0] !== "") {
      lastRowWithId = i + 1;
      break;
    }
  }
  const newRow = lastRowWithId + 1;

  sheet.getRange(newRow, 9).setValue(workId);

  const work = searchAnnictAnimeByIdGraphQL(workId);
  if (work) {
    const extractedData = extractWorkData(work);
    sheet.getRange(newRow, 3).setValue(extractedData[0]);
    sheet.getRange(newRow, 10, 1, 4).setValues([extractedData.slice(1)]);
  } else {
    sheet.getRange(newRow, 3).setValue("情報が見つかりませんでした");
  }

  const proposerText = getProposerText(proposers);
  sheet.getRange(newRow, 4).setValue(proposerText);

  const today = new Date();
  const todayString = Utilities.formatDate(today, spreadsheetTimeZone, "yyyy/MM/dd");
  if (!sheet.getRange("B1:B" + sheet.getLastRow()).getDisplayValues().flat().includes(todayString)) {
      sheet.getRange(newRow, 2).setValue(today);
  }

  copyIdsFromLargestNumberRowToDate(sheet);

  insertSparklinesForViewers(viewers, newRow); 

  return createJsonResponse({ workId: workId, title: work ? work.title : "情報なし" });
}

function recordFavoriteCharacter(characterName, animeTitles, currentUser, allowOverwrite) {
  const sheetName = '2025キャラ部門';
  const sheet = SPREADSHEET.getSheetByName(sheetName);
  if (!sheet) {
    return createJsonResponse({ message: `Sheet '${sheetName}' not found.` }, false);
  }

  const userColumnMap = {
    'いいの': 4,
    'おおつか': 6,
    'たかだ': 8
  };

  const targetColumn = userColumnMap[currentUser];
  if (!targetColumn) {
    return createJsonResponse({ message: `Invalid user: ${currentUser}` }, false);
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 1) return createJsonResponse({ message: "Sheet is empty, skipped." }); 

  const titleRange = sheet.getRange(1, 2, lastRow, 1);
  const titleValues = titleRange.getValues();

  animeTitles.forEach(animeTitle => {
    let targetRowIndex = -1;
    for (let i = 0; i < titleValues.length; i++) {
      if (titleValues[i][0] == animeTitle) {
        targetRowIndex = i + 1;
        break;
      }
    }

    if (targetRowIndex !== -1) {
      const existingValue = sheet.getRange(targetRowIndex, targetColumn).getValue();
      if (existingValue && !allowOverwrite) {
      } else {
        sheet.getRange(targetRowIndex, targetColumn).setValue(characterName);
      }
    }
  });

  return createJsonResponse({});
}

function getProposerText(proposers) {
  if (!proposers || proposers.length === 0) return "";
  const sortedProposers = proposers.sort();
  const text = sortedProposers.join('＆'); 
  if (text === "いいの＆おおつか＆たかだ") return "全員";
  return text;
}

function insertSparklinesForViewers(viewers, targetRow2025) { 
  if (!viewers || viewers.length === 0) return; 

  const sortedViewers = viewers.sort().join('&');
  const sparklineFormula = '=SPARKLINE({1,0})';

  const dateSheet = SPREADSHEET.getSheetByName(ID_COPY_TARGET_SHEET_NAME);
  if (dateSheet) {
    const columnsToMarkDate = [];
    switch (sortedViewers) {
      case "いいの": columnsToMarkDate.push(4, 5); break;
      case "おおつか": columnsToMarkDate.push(3, 5); break;
      case "たかだ": columnsToMarkDate.push(3, 4); break;
      case "いいの&おおつか": columnsToMarkDate.push(5); break;
      case "いいの&たかだ": columnsToMarkDate.push(4); break;
      case "おおつか&たかだ": columnsToMarkDate.push(3); break;
    }

    if (columnsToMarkDate.length > 0) {
      const startRow = 2;
      const lastRow = dateSheet.getLastRow();
      let targetRow = lastRow + 1;
      if (lastRow >= startRow) {
        const range = dateSheet.getRange(startRow, 3, lastRow - startRow + 1, 3);
        const values = range.getValues();
        const formulas = range.getFormulas();
        for (let i = 0; i < values.length; i++) {
          if (!values[i].some(v => String(v).trim() !== '') && !formulas[i].some(f => f)) {
            targetRow = startRow + i;
            break;
          }
        }
      }
      columnsToMarkDate.forEach(col => dateSheet.getRange(targetRow, col).setFormula(sparklineFormula));
    }
  }

  const sheet2025 = SPREADSHEET.getSheetByName('2025');
  if (sheet2025 && targetRow2025) {
    const columnsToMark2025 = [];
    switch (sortedViewers) {
      case "いいの": columnsToMark2025.push(6, 7); break;
      case "おおつか": columnsToMark2025.push(5, 7); break;
      case "たかだ": columnsToMark2025.push(5, 6); break;
      case "いいの&おおつか": columnsToMark2025.push(7); break;
      case "いいの&たかだ": columnsToMark2025.push(6); break;
      case "おおつか&たかだ": columnsToMark2025.push(5); break;
    }

    if (columnsToMark2025.length > 0) {
      columnsToMark2025.forEach(col => sheet2025.getRange(targetRow2025, col).setFormula(sparklineFormula));
    }
  }

  SpreadsheetApp.flush();
}

function searchAnnictAnimeByIdGraphQL(annictId) {
  const query = `query SearchWorkById($id: Int!) { searchWorks(annictIds: [$id]) { nodes { annictId, title, media, seasonYear, seasonName, episodesCount } } }`;
  const variables = { id: parseInt(annictId, 10) };
  const options = { method: 'post', contentType: 'application/json', headers: { Authorization: `Bearer ${ANNICT_API_TOKEN}` }, payload: JSON.stringify({ query, variables }), muteHttpExceptions: true };
  try {
    const response = UrlFetchApp.fetch(ANNICT_GRAPHQL_ENDPOINT, options);
    const data = JSON.parse(response.getContentText());
    return data.data.searchWorks.nodes[0] || null;
  } catch (e) { return null; }
}

function extractWorkData(work) {
  return [ work.title || '', work.media || '', work.seasonYear || '', work.seasonName || '', work.episodesCount || '' ];
}

function sendDataToPython(payload) {
  const debugSheet = SPREADSHEET.getSheetByName("Debug");
  const logToDebug = (message) => {
    if (debugSheet) {
      debugSheet.appendRow([new Date(), 'sendDataToPython', message]);
    }
    Logger.log(message);
  };

  const options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload),
    'muteHttpExceptions': true
  };

  try {
    logToDebug(`Sending payload: ${JSON.stringify(payload)}`);
    const response = UrlFetchApp.fetch(url, options);
    logToDebug(`Response Code: ${response.getResponseCode()}, Response Body: ${response.getContentText()}`);
  } catch (e) {
    logToDebug(`Error sending data to Python API: ${e.toString()}`);
  }
}

function getAnimeViewerStatusForMultipleUsers() {
  const debugSheet = SPREADSHEET.getSheetByName("Debug");
  const logToDebug = (message) => {
    if (debugSheet) {
      debugSheet.appendRow([new Date(), 'getAnimeViewerStatus', message]);
    }
    Logger.log(message);
  };

  const sheetName = PropertiesService.getScriptProperties().getProperty('SHEET_NAME');
  if (!sheetName) {
    logToDebug("エラー: スクリプトプロパティ 'SHEET_NAME' が設定されていません。");
    return { success: false, message: "エラー: スクリプトプロパティ 'SHEET_NAME' が設定されていません。" };
  }
  const sheet = SPREADSHEET.getSheetByName(sheetName);
  if (!sheet) {
    logToDebug(`エラー: 指定されたシート「${sheetName}」が見つかりませんでした。`);
    return { success: false, message: `エラー: 指定されたシート「${sheetName}」が見つかりませんでした。` };
  }

  const userTokens = [
    { token: PropertiesService.getScriptProperties().getProperty('ANNICT_TOKEN_USER1'), outputColumn: 3, name: sheet.getRange("C1").getValue() },
    { token: PropertiesService.getScriptProperties().getProperty('ANNICT_TOKEN_USER2'), outputColumn: 4, name: sheet.getRange("D1").getValue() },
    { token: PropertiesService.getScriptProperties().getProperty('ANNICT_TOKEN_USER3'), outputColumn: 5, name: sheet.getRange("E1").getValue() }
  ];

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    logToDebug("データシートにIDが見つかりませんでした。");
    return { success: true, message: "データシートにIDが見つかりませんでした。" };
  }

  const dataRange = sheet.getRange(2, 1, lastRow - 1, Math.max(...userTokens.map(u => u.outputColumn)));
  const values = dataRange.getValues();
  const formulas = dataRange.getFormulas();
  const graphqlQuery = `query GetViewerStatus($id: Int!) { searchWorks(annictIds: [$id]) { nodes { annictId, title, viewerStatusState } } }`;
  const results = {};

  userTokens.forEach((user, userIndex) => {
    const ACCESS_TOKEN = user.token;
    const outputColumn = user.outputColumn;
    const userName = user.name || `ユーザー${userIndex + 1}`;
    results[userName] = { processedCount: 0, skippedWatched: 0, skippedSparkline: 0, errors: 0 };

    if (!ACCESS_TOKEN) {
      logToDebug(`ユーザー「${userName}」のトークンが設定されていません。`);
      for (let i = 0; i < values.length; i++) sheet.getRange(2 + i, outputColumn).setValue("トークン未設定");
      results[userName].message = "トークン未設定";
      return;
    }

    for (let i = 0; i < values.length; i++) {
      const animeTitle = values[i][0];
      const animeId = values[i][1];
      const existingStatusValue = values[i][outputColumn - 1];
      const existingStatusFormula = formulas[i][outputColumn - 1];

      if (typeof animeId !== 'number' || !Number.isInteger(animeId) || animeId <= 0) continue;
      if (existingStatusFormula.toUpperCase().includes('=SPARKLINE(')) {
        results[userName].skippedSparkline++;
        continue;
      }
      if (String(existingStatusValue).toLowerCase() === 'watched') {
        results[userName].skippedWatched++;
        continue;
      }

      const headers = { "Authorization": `Bearer ${ACCESS_TOKEN}`, "Content-Type": "application/json" };
      const variables = { "id": animeId };
      const payload = { query: graphqlQuery, variables: variables };
      const options = { "method": "post", "headers": headers, "payload": JSON.stringify(payload), "muteHttpExceptions": true };
      
      logToDebug(`Requesting for ${userName}, Anime ID: ${animeId}`);

      try {
        const response = UrlFetchApp.fetch(ANNICT_GRAPHQL_ENDPOINT, options);
        const responseCode = response.getResponseCode();
        const responseText = response.getContentText();
        
        logToDebug(`Response for ${userName}, Anime ID: ${animeId}, Code: ${responseCode}, Body: ${responseText}`);

        if (responseCode >= 400) {
            sheet.getRange(2 + i, outputColumn).setValue("API/GraphQLエラー");
            results[userName].errors++;
            continue;
        }

        const result = JSON.parse(responseText);
        if (result && result.errors) {
            logToDebug(`GraphQL Error for ${userName}, Anime ID: ${animeId}: ${JSON.stringify(result.errors)}`);
            sheet.getRange(2 + i, outputColumn).setValue("API/GraphQLエラー");
            results[userName].errors++;
        } else {
            const viewerStatus = result.data?.searchWorks?.nodes[0]?.viewerStatusState;
            sheet.getRange(2 + i, outputColumn).setValue(viewerStatus || "ステータスなし");
            if(viewerStatus) results[userName].processedCount++;
            if (viewerStatus && viewerStatus.toLowerCase() === 'watched') {
                sendDataToPython({ type: "watched_status_update", user_name: userName, anime_title: animeTitle, message: `${userName} ${animeTitle} 観了`, timestamp: new Date().toISOString() });
            }
        }
      } catch (e) {
        logToDebug(`Catastrophic Error for ${userName}, Anime ID: ${animeId}: ${e.toString()}`);
        sheet.getRange(2 + i, outputColumn).setValue(`スクリプトエラー`);
        results[userName].errors++;
      }
    }
  });
  return { success: true, message: "処理が完了しました。", details: results };
}

function updateTestSheetMaxId() {
  const dateSheet = SPREADSHEET.getSheetByName(ID_COPY_TARGET_SHEET_NAME);
  const testSheet = SPREADSHEET.getSheetByName('2025');
  if (!dateSheet || !testSheet) return;

  const lastRowDate = dateSheet.getLastRow();
  if (lastRowDate < 2) return;

  if (!dateSheet.getRange(2, 2, lastRowDate - 1, 1).getValues().some(row => row[0] && !isNaN(row[0]))) return;

  const statusRange = dateSheet.getRange(2, 3, lastRowDate - 1, 3);
  const statusValues = statusRange.getValues();
  const statusFormulas = statusRange.getFormulas();
  const SPARKLINE_FORMULA = '=SPARKLINE({1,0})';

  const allCompleted = statusValues.every((row, i) => {
      return row.every((cell, j) => 
          String(cell).toLowerCase() === 'watched' || 
          String(statusFormulas[i][j]).toUpperCase() === SPARKLINE_FORMULA
      );
  });

  if (allCompleted) {
    const testLastRow = testSheet.getLastRow();
    let maxId = 0;
    let maxIdRow = -1;

    if (testLastRow >= 3) {
      const testAColumnRange = testSheet.getRange(3, 1, testLastRow - 2, 1);
      const testAColumnValues = testAColumnRange.getValues();
      for (let i = 0; i < testAColumnValues.length; i++) {
        const idValue = testAColumnValues[i][0];
        if (typeof idValue === 'number' && idValue > maxId) {
          maxId = idValue;
          maxIdRow = i + 3;
        }
      }
    }

    if (maxIdRow !== -1) {
      testSheet.getRange(maxIdRow, 1).setFontColor(DEFAULT_COLOR);
    }

    const newIdCell = testSheet.getRange(testLastRow + 1, 1);
    newIdCell.setValue(maxId + 1);
    newIdCell.setFontColor(DEFAULT_COLOR);

    dateSheet.getRange(2, 1, lastRowDate - 1, dateSheet.getLastColumn()).clearContent();
    sendDataToPython({ type: "status_update_complete", message: "そして次の曲が始まるのです", timestamp: new Date().toISOString() });
  }
  SpreadsheetApp.flush();
}