/**
 * 見える化家計調査票 セットアップスクリプト
 * 
 * 1. 問診票シートから「入力シート」を自動生成する
 * 2. 入力シートから「集計表シート（ダッシュボード）」を自動生成する
 */

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('📝 家計調査票メニュー')
    .addItem('1. 問診票から入力シートを作成', 'generateInputSheetFromIntake')
    .addItem('2. 集計表を最新化する', 'generateSummarySheet')
    .addToUi();
}

/**
 * 問診票シートのチェックボックスを読み取り、入力シートを動的に生成する
 */
function generateInputSheetFromIntake(isFromMenu = true) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const intakeSheet = ss.getSheetByName('問診票');

  if (!intakeSheet) {
    if (isFromMenu) SpreadsheetApp.getUi().alert('エラー', '「問診票」シートが見つかりません。シート名を確認してください。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  let inputSheet = ss.getSheetByName('入力シート');
  const existingDataMap = new Map();
  let isUpdate = false;

  if (inputSheet) {
    if (isFromMenu) {
      const response = SpreadsheetApp.getUi().alert("確認", "既存の「入力シート」の入力内容（金額や回数）を引き継いだまま、問診票のチェック状態を反映して項目を更新しますか？", SpreadsheetApp.getUi().ButtonSet.YES_NO);
      if (response !== SpreadsheetApp.getUi().Button.YES) return;
    }

    isUpdate = true;
    // 既存データを退避
    const existingValues = inputSheet.getDataRange().getValues();
    if (existingValues.length > 1) {
      for (let i = 1; i < existingValues.length; i++) {
        const row = existingValues[i];
        const target = row[0];
        const mainCat = row[1];
        const subCat = row[2];
        const itemName = row[3];
        const userMemo = row[4];
        const price = row[5];
        const times = row[6];

        // ユニークキーは対象者、大項目、カテゴリ、自動生成項目名で固定（ユーザーが書き換えないD列までの情報）
        const key = `${target}_${mainCat}_${subCat}_${itemName}`;
        existingDataMap.set(key, { userMemo: userMemo, price: price, times: times });
      }
    }
    inputSheet.clear();
  } else {
    inputSheet = ss.insertSheet('入力シート');
  }

  // デフォルト項目（全家庭共通）: [対象者, 大項目, カテゴリ, 項目名（自動生成）, メモ（自由記入）, 単価, 回数/年]
  let dataRows = [
    ["世帯主", "収入", "給与", "毎月の手取り", "", "", 12],
    ["共通", "固定費", "光熱費", "電気代（平均）", "", "", 12],
    ["共通", "固定費", "光熱費", "ガス代（平均）", "", "", 12],
    ["共通", "固定費", "光熱費", "水道代（平均）", "", "", 6],
    ["共通", "変動費", "食費", "スーパー等", "", "", 12],
    ["共通", "変動費", "外食", "外食・テイクアウト", "", "", 12],
    ["共通", "変動費", "日用品", "ドラッグストア等", "", "", 12],
    ["共通", "変動費", "美容衣服", "美容院・服代", "", "", 12],
    ["共通", "資産", "銀行", "普通預金残高", "", "", 1]
  ];

  // 問診票の読み取り (C列:チェックボックス, D列:数値)
  const isChecked = (row) => intakeSheet.getRange(`C${row}`).getValue() === true;
  const getNum = (row) => parseInt(intakeSheet.getRange(`D${row}`).getValue()) || 1;

  // STEP 1
  if (isChecked(5)) {
    let num = getNum(5);
    for (let i = 1; i <= num; i++) {
      dataRows.push(["共通", "固定費", "教育費", `学校・保育料等（子ども${i}）`, "", "", 12]);
      dataRows.push(["共通", "変動費", "子ども費", `生活・洋服代等（子ども${i}）`, "", "", 12]);
    }
  }
  if (isChecked(7)) dataRows.push(["世帯主", "収入", "賞与", "ボーナス", "", "", 2]);
  if (isChecked(8)) dataRows.push(["配偶者", "収入", "給与", "毎月の手取り", "", "", 12]);
  if (isChecked(9)) dataRows.push(["配偶者", "収入", "賞与", "ボーナス", "", "", 2]);
  if (isChecked(10)) {
    let num = getNum(10);
    for (let i = 1; i <= num; i++) {
      dataRows.push(["家族", "収入", "児童手当", `児童手当（子ども${i}）`, "", "", 12]);
    }
  }

  // STEP 2
  if (isChecked(13)) {
    let num = getNum(13);
    for (let i = 1; i <= num; i++) dataRows.push(["共通", "変動費", "お小遣い", `お小遣い（${i}人目）`, "", "", 12]);
  }
  if (isChecked(15)) {
    let num = getNum(15);
    for (let i = 1; i <= num; i++) dataRows.push(["共通", "固定費", "習い事", `習い事・塾（${i}つ目）`, "", "", 12]);
  }

  // STEP 3
  if (isChecked(18)) {
    let num = getNum(18);
    for (let i = 1; i <= num; i++) dataRows.push(["共通", "固定費", "通信費", `スマホ代（${i}台目）`, "", "", 12]);
  }
  if (isChecked(19)) dataRows.push(["共通", "固定費", "通信費", "自宅ネット回線", "", "", 12]);
  if (isChecked(21)) {
    let num = getNum(21);
    for (let i = 1; i <= num; i++) dataRows.push(["共通", "固定費", "サブスク", `サブスク（${i}つ目）`, "", "", 12]);
  }
  if (isChecked(23)) {
    let num = getNum(23);
    for (let i = 1; i <= num; i++) dataRows.push(["共通", "固定費", "保険料", `保険料（${i}つ目）`, "", "", 12]);
  }

  // STEP 4
  if (isChecked(26)) {
    dataRows.push(["共通", "固定費", "住宅費用", "家賃・管理費", "", "", 12]);
    dataRows.push(["共通", "固定費", "住宅費用", "賃賃の更新料", "", "", 0.5]);
  }
  if (isChecked(27)) {
    dataRows.push(["共通", "固定費", "住宅費用", "住宅ローン月額", "", "", 12]);
    dataRows.push(["共通", "固定費", "住宅費用", "固定資産税", "", "", 1]);
    dataRows.push(["共通", "固定費", "住宅費用", "修繕積立金・その他", "", "", 12]);
  }
  if (isChecked(29)) {
    let num = getNum(29);
    for (let i = 1; i <= num; i++) {
      dataRows.push(["共通", "変動費", "車", `ガソリン代・ETC（${i}台目）`, "", "", 12]);
      dataRows.push(["共通", "固定費", "車", `自動車税（${i}台目）`, "", "", 1]);
      dataRows.push(["共通", "変動費", "車", `車検・メンテ代（${i}台目）`, "", "", 1]);
      dataRows.push(["共通", "固定費", "車", `自動車保険（${i}台目）`, "", "", 12]);
    }
  }
  if (isChecked(30)) dataRows.push(["共通", "固定費", "住宅費用", "駐車場代", "", "", 12]);

  // STEP 5
  if (isChecked(33)) dataRows.push(["共通", "固定費", "ローン", "奨学金・各種ローン返済", "", "", 12]);
  if (isChecked(34)) dataRows.push(["共通", "変動費", "ペット", "ペットのエサ代・病院代など", "", "", 12]);
  if (isChecked(35)) {
    let num = getNum(35);
    for (let i = 1; i <= num; i++) {
      dataRows.push(["共通", "貯蓄", "投資・運用", `積立・投資（${i}つ目）`, "", "", 12]);
    }
  }
  if (isChecked(36)) {
    let num = getNum(36);
    for (let i = 1; i <= num; i++) {
      dataRows.push(["共通", "資産", "預金・投資", `資産残高（${i}つ目）`, "", "", 1]);
    }
  }

  // 収入を上部に固め、さらに対象者でソートする
  const orderMain = { "収入": 1, "固定費": 2, "変動費": 3, "貯蓄": 4, "資産": 5 };
  const orderTarget = { "世帯主": 1, "配偶者": 2, "家族": 3, "共通": 4 };
  dataRows.sort((a, b) => {
    if (orderMain[a[1]] !== orderMain[b[1]]) {
      return (orderMain[a[1]] || 99) - (orderMain[b[1]] || 99);
    }
    // 同じ大項目の場合は、対象者でソート
    return (orderTarget[a[0]] || 99) - (orderTarget[b[0]] || 99);
  });

  // 既存データが存在すればマージする
  if (isUpdate && existingDataMap.size > 0) {
    for (let i = 0; i < dataRows.length; i++) {
      const row = dataRows[i];
      const key = `${row[0]}_${row[1]}_${row[2]}_${row[3]}`;
      if (existingDataMap.has(key)) {
        const saved = existingDataMap.get(key);
        row[4] = saved.userMemo; // メモを引き継ぐ
        row[5] = saved.price;    // 単価を引き継ぐ
        row[6] = saved.times;    // 回数を引き継ぐ
      }
    }
  }

  // ヘッダー書き込み
  const headers = ["対象者", "大項目", "カテゴリ", "項目名（自動生成）", "メモ（自由記入）", "単価", "回数/年（※実態に合わせて変更）", "年間金額"];
  inputSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  inputSheet.getRange(1, 1, 1, headers.length).setBackground("#4a86e8").setFontColor("white").setFontWeight("bold");

  // データ書き込み
  if (dataRows.length > 0) {
    inputSheet.getRange(2, 1, dataRows.length, 7).setValues(dataRows);
  }

  // 年間金額（H列）に数式を設定（=F2*G2）
  const lastRow = Math.max(2, dataRows.length + 1);
  const formulaRange = inputSheet.getRange(2, 8, lastRow - 1, 1);
  formulaRange.setFormulaR1C1("=IF(ISBLANK(R[0]C[-2]), 0, R[0]C[-2] * R[0]C[-1])");

  // 見た目の調整
  inputSheet.setColumnWidth(1, 100); // 対象者
  inputSheet.setColumnWidth(2, 100); // 大項目
  inputSheet.setColumnWidth(3, 130); // カテゴリ
  inputSheet.setColumnWidth(4, 200); // 項目名（自動生成）
  inputSheet.setColumnWidth(5, 150); // メモ（自由記入）
  inputSheet.setColumnWidth(6, 100); // 単価
  inputSheet.setColumnWidth(7, 220); // 回数/年
  inputSheet.setColumnWidth(8, 120); // 年間金額
  inputSheet.getRange("F2:H" + lastRow).setNumberFormat("#,##0");
  inputSheet.getRange("D2:E" + lastRow).setWrap(true); // 長いテキストは折り返して全体表示

  // 枠線を引く
  inputSheet.getRange(1, 1, lastRow, 8).setBorder(true, true, true, true, true, true);

  // 入力シートをアクティブにする
  ss.setActiveSheet(inputSheet);

  if (isFromMenu) {
    if (isUpdate) {
      SpreadsheetApp.getUi().alert(
        '完了',
        '入力シートを最新化しました！\n入力されていた金額や回数、メモはそのまま引き継がれています。',
        SpreadsheetApp.getUi().ButtonSet.OK
      );
    } else {
      SpreadsheetApp.getUi().alert(
        '完了',
        'あなた専用の入力シートを作成しました！\n\n【重要】\n単価を入力し、「回数/年」がご家庭の実態と合っているか確認・変更してください。\n（例：ボーナスが年1回なら2→1に変更、水道代が毎月請求なら6→12に変更など）',
        SpreadsheetApp.getUi().ButtonSet.OK
      );
    }
  }
}

/**
 * 入力シートから集計表シートを生成する
 */
function generateSummarySheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheet = ss.getSheetByName('入力シート');

  if (!inputSheet) {
    SpreadsheetApp.getUi().alert('エラー', '「入力シート」が見つかりません。先に作成してください。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  let summarySheet = ss.getSheetByName('家計調査票(集計)');
  if (!summarySheet) {
    summarySheet = ss.insertSheet('家計調査票(集計)');
  } else {
    summarySheet.clear();
  }

  const data = inputSheet.getDataRange().getValues();
  if (data.length <= 1) return;

  // ヘッダー除外
  const rows = data.slice(1);

  // データの集計用オブジェクト
  let summary = {
    収入: { 世帯主: 0, 配偶者: 0, 家族: 0, 共通: 0, 詳細: [] },
    固定費: { 詳細: [], 合計: 0 },
    変動費: { 詳細: [], 合計: 0 },
    貯蓄: { 詳細: [], 合計: 0 },
    資産: { 詳細: [], 合計: 0 }
  };

  rows.forEach(row => {
    let [target, mainCat, subCat, itemName, userMemo, price, times, annual] = row;
    annual = Number(annual) || 0;
    if (annual === 0) return; // 0円のものは集計に含めない

    // メモ欄に記入があれば「項目名 (メモ)」の形で出力、なければ項目名のみ
    const memo = userMemo ? `${itemName} (${userMemo})` : itemName;

    if (mainCat === "収入") {
      if (summary.収入[target] !== undefined) {
        summary.収入[target] += annual;
      } else {
        summary.収入.共通 += annual;
      }
      summary.収入.詳細.push({ target, subCat, memo, annual });
    } else if (summary[mainCat]) {
      summary[mainCat].合計 += annual;
      summary[mainCat].詳細.push({ target, subCat, memo, annual });
    }
  });

  // === 描画ロジック ===
  // 左カラム（固定費・変動費）と右カラム（収入・貯蓄・資産）に分けて描画
  let leftRow = 6;
  let rightRow = 6;

  // --- タイロール ---
  summarySheet.getRange("B2:H2").merge().setValue("家計調査票（現在地・年間予測）").setFontSize(16).setFontWeight("bold").setHorizontalAlignment("center");
  summarySheet.getRange("B2:H2").setBackground("#eeeeee");

  // --- サマリー (年間支出合計・年間収支予測) ---
  const totalExp = summary.固定費.合計 + summary.変動費.合計;
  const totalInc = summary.収入.世帯主 + summary.収入.配偶者 + summary.収入.家族 + summary.収入.共通;
  const balance = totalInc - totalExp - summary.貯蓄.合計;

  // 年間支出 合計 (B4:C4 を結合、D4 に金額)
  summarySheet.getRange("B4:C4").merge().setValue("年間支出 合計").setFontWeight("bold").setBackground("#ea9999");
  summarySheet.getRange("D4").setValue(totalExp).setNumberFormat("#,##0").setFontWeight("bold").setBackground("#ea9999");
  summarySheet.getRange("B4:D4").setBorder(true, true, true, true, true, true);

  // 年間収支予測 (F4:G4 を結合、H4 に金額)
  summarySheet.getRange("F4:G4").merge().setValue("年間収支予測 (収入 - 支出 - 貯蓄)").setFontWeight("bold").setBackground("#ffe599");
  summarySheet.getRange("H4").setValue(balance).setNumberFormat("#,##0").setFontWeight("bold").setBackground("#ffe599");
  summarySheet.getRange("F4:H4").setBorder(true, true, true, true, true, true);

  // ヘルパー関数：ブロックを描画して次の行番号を返す
  function drawBlock(sheet, startRow, startCol, title, total, details, isIncome) {
    let r = startRow;
    // ヘッダー
    sheet.getRange(r, startCol, 1, 3).merge().setValue(title).setBackground("#4a86e8").setFontColor("white").setFontWeight("bold");
    r++;

    // 詳細
    if (details.length === 0) {
      sheet.getRange(r, startCol).setValue("データなし");
      r++;
    } else {
      details.forEach(d => {
        sheet.getRange(r, startCol).setValue(isIncome ? d.target : d.subCat);
        sheet.getRange(r, startCol + 1).setValue(d.memo);
        sheet.getRange(r, startCol + 2).setValue(d.annual).setNumberFormat("#,##0");
        r++;
      });
    }

    // 合計
    sheet.getRange(r, startCol, 1, 2).merge().setValue(title + " 合計").setFontWeight("bold").setBackground("#f3f3f3");
    sheet.getRange(r, startCol + 2).setValue(total).setNumberFormat("#,##0").setFontWeight("bold").setBackground("#f3f3f3");

    // 罫線
    sheet.getRange(startRow, startCol, (r - startRow) + 1, 3).setBorder(true, true, true, true, true, true);
    return r + 2;
  }

  // --- 左カラム：支出 ---
  leftRow = drawBlock(summarySheet, leftRow, 2, "■ 固定費", summary.固定費.合計, summary.固定費.詳細, false);
  leftRow = drawBlock(summarySheet, leftRow, 2, "■ 変動費", summary.変動費.合計, summary.変動費.詳細, false);

  // 支出合計は上部に移動したため、ここでは何もしない

  // --- 右カラム：収入・貯蓄・資産 ---
  rightRow = drawBlock(summarySheet, rightRow, 6, "■ 収入", totalInc, summary.収入.詳細, true);
  rightRow = drawBlock(summarySheet, rightRow, 6, "■ 貯蓄（年間フロー）", summary.貯蓄.合計, summary.貯蓄.詳細, false);
  rightRow = drawBlock(summarySheet, rightRow, 6, "■ 資産（現在のストック）", summary.資産.合計, summary.資産.詳細, false);

  // 収支予測は上部に移動したため、ここでは何もしない

  // 見た目の調整
  summarySheet.setColumnWidth(1, 20); // 余白
  summarySheet.setColumnWidth(2, 100);
  summarySheet.setColumnWidth(3, 250); // C列 (項目名＋メモ)
  summarySheet.setColumnWidth(4, 100);
  summarySheet.setColumnWidth(5, 40); // 隙間
  summarySheet.setColumnWidth(6, 100);
  summarySheet.setColumnWidth(7, 250); // G列 (項目名＋メモ)
  summarySheet.setColumnWidth(8, 100);

  ss.setActiveSheet(summarySheet);
  SpreadsheetApp.getUi().alert('完了', '美しい家計調査票（集計表）が完成しました！\n右下の「年間収支予測」がプラスになっているか確認しましょう。', SpreadsheetApp.getUi().ButtonSet.OK);
}


/**
 * チェックボックスがクリックされたときに自動実行されるトリガー
 * スマホ・タブレットからの実行に対応するため、メニュー以外からも実行できるようにする
 */
function onEdit(e) {
  if (!e) return;
  const sheet = e.source.getActiveSheet();
  
  // 問診票シートのみで動作
  if (sheet.getName() === '問診票') {
    // チェックボックスがTRUE（オン）になった時だけ
    if (e.value === "TRUE") {
      // 編集されたセル（チェックボックス）の右隣のテキストを取得
      const textCell = sheet.getRange(e.range.getRow(), e.range.getColumn() - 1);
      const text = textCell.getValue();
      
      // 左隣のテキストが「完了」という文字を含んでいるか判定
      if (String(text).indexOf("完了") !== -1) {
        // メッセージ出力用の右隣のセル(D列)を取得
        const statusCell = sheet.getRange(e.range.getRow(), e.range.getColumn() + 1);
        
        // 実行中のステータスを表示
        statusCell.setValue("🔄 入力シートを自動生成中...");
        SpreadsheetApp.flush(); // 画面を更新して文字を表示させる
        
        // UIを出さないモード(isFromMenu = false)で実行
        generateInputSheetFromIntake(false);
        
        // チェックを外して完了メッセージに変更
        e.range.setValue(false);
        statusCell.setValue("✅ 生成完了！");
      }
    }
  }
}