/**
 * GemEgg 予実管理 スプレッドシート
 * Google Apps Script 実装
 *
 * 使用方法:
 * 1. スプレッドシートで「拡張機能」>「Apps Script」を開く
 * 2. このコードを貼り付けて保存
 * 3. onOpen関数を実行（初回は権限承認が必要）
 * 4. スプレッドシートを再読み込み
 * 5. メニューから「GemEgg予実管理」を選択
 */

// ============================================
// メニュー設定
// ============================================

/**
 * スプレッドシートを開いた時にメニューを追加
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🔷 GemEgg予実管理')
    .addItem('📊 予実シートを更新', 'updateBudgetVsActual')
    .addSeparator()
    .addSubMenu(ui.createMenu('初期設定')
      .addItem('予算シートを初期化', 'initBudgetSheet')
      .addItem('実績シートを初期化', 'initActualSheet')
      .addItem('予実シートを初期化', 'initOutputSheet'))
    .addSeparator()
    .addSubMenu(ui.createMenu('自動化')
      .addItem('毎日自動更新を設定', 'createDailyTrigger')
      .addItem('毎月自動更新を設定', 'createMonthlyTrigger')
      .addItem('トリガーを全削除', 'deleteAllTriggers'))
    .addSeparator()
    .addItem('ℹ️ 使い方', 'showHelp')
    .addToUi();
}

// ============================================
// 定数
// ============================================

const MONTHS = [
  '2025-04', '2025-05', '2025-06', '2025-07', '2025-08', '2025-09',
  '2025-10', '2025-11', '2025-12', '2026-01', '2026-02', '2026-03'
];

const PL_ITEMS = [
  { level: 0, name: '売上高' },
  { level: 1, name: '売上高' },
  { level: 0, name: '売上高 計' },
  { level: 1, name: '売上原価' },
  { level: 0, name: '売上総損益金額' },
  { level: 1, name: '販売管理費' },
  { level: 2, name: '役員報酬' },
  { level: 2, name: '給料手当' },
  { level: 2, name: '法定福利費' },
  { level: 2, name: '福利厚生費' },
  { level: 2, name: '広告宣伝費' },
  { level: 2, name: '交際費' },
  { level: 2, name: '会議費' },
  { level: 2, name: '旅費交通費' },
  { level: 2, name: '通信費' },
  { level: 2, name: '販売促進費' },
  { level: 2, name: '消耗品費' },
  { level: 2, name: '水道光熱費' },
  { level: 2, name: '支払手数料' },
  { level: 2, name: '地代家賃' },
  { level: 2, name: '租税公課' },
  { level: 2, name: '支払報酬料' },
  { level: 2, name: '雑費' },
  { level: 1, name: '販売管理費 計' },
  { level: 0, name: '営業損益金額' },
  { level: 1, name: '営業外収益' },
  { level: 1, name: '営業外費用' },
  { level: 0, name: '経常損益金額' },
  { level: 1, name: '特別利益' },
  { level: 1, name: '特別損失' },
  { level: 0, name: '税引前当期純損益金額' },
  { level: 1, name: '法人税等' },
  { level: 0, name: '当期純損益金額' }
];

// ============================================
// メイン機能
// ============================================

/**
 * 予実シートを更新（メイン機能）
 */
function updateBudgetVsActual() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ui = SpreadsheetApp.getUi();

  const budgetSheet = ss.getSheetByName('予算入力');
  const actualSheet = ss.getSheetByName('実績入力');
  let outputSheet = ss.getSheetByName('予実出力');

  if (!budgetSheet) {
    ui.alert('エラー', '予算入力シートが見つかりません。\n初期設定メニューから作成してください。', ui.ButtonSet.OK);
    return;
  }

  if (!actualSheet) {
    ui.alert('エラー', '実績入力シートが見つかりません。\n初期設定メニューから作成してください。', ui.ButtonSet.OK);
    return;
  }

  if (!outputSheet) {
    outputSheet = ss.insertSheet('予実出力');
  }

  // 予算データ取得 (C5:N37 = 12ヶ月 x 33項目)
  const budgetData = budgetSheet.getRange('C5:N37').getValues();

  // 実績データ取得 (実績入力シートのCSVデータから)
  const actualData = parseActualData(actualSheet);

  // 予実出力シートを更新
  updateOutputSheet(outputSheet, budgetData, actualData);

  // 完了通知
  const timestamp = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss');
  ui.alert('✅ 更新完了', `予実シートを更新しました。\n\n更新日時: ${timestamp}`, ui.ButtonSet.OK);
}

/**
 * 実績データを解析
 */
function parseActualData(actualSheet) {
  const data = actualSheet.getRange('B6:T50').getValues();
  const result = {};

  // 項目名とデータのマッピング
  const itemMapping = {
    '売上高': { row: 3, name: '売上高' },
    '売上原価': { row: 6, name: '売上原価' },
    '売上総損益金額': { row: 12, name: '売上総損益' },
    '役員報酬': { row: 14, name: '役員報酬' },
    '給料手当': { row: 15, name: '給料手当' },
    '法定福利費': { row: 16, name: '法定福利費' },
    '福利厚生費': { row: 17, name: '福利厚生費' },
    '広告宣伝費': { row: 18, name: '広告宣伝費' },
    '交際費': { row: 19, name: '交際費' },
    '会議費': { row: 20, name: '会議費' },
    '旅費交通費': { row: 21, name: '旅費交通費' },
    '通信費': { row: 22, name: '通信費' },
    '消耗品費': { row: 24, name: '消耗品費' },
    '水道光熱費': { row: 25, name: '水道光熱費' },
    '支払手数料': { row: 26, name: '支払手数料' },
    '地代家賃': { row: 27, name: '地代家賃' },
    '租税公課': { row: 28, name: '租税公課' },
    '支払報酬料': { row: 29, name: '支払報酬料' },
    '雑費': { row: 30, name: '雑費' },
    '販売管理費 計': { row: 31, name: '販売管理費計' },
    '営業損益金額': { row: 32, name: '営業損益' },
    '経常損益金額': { row: 35, name: '経常損益' },
    '当期純損益金額': { row: 41, name: '当期純損益' }
  };

  // CSVデータから値を抽出
  data.forEach((row, index) => {
    const itemName = findItemName(row);
    if (itemName && itemMapping[itemName]) {
      result[itemName] = row.slice(5, 17).map(val => {
        if (!val) return 0;
        const num = typeof val === 'number' ? val : parseInt(String(val).replace(/,/g, ''), 10);
        return isNaN(num) ? 0 : num;
      });
    }
  });

  return result;
}

/**
 * 行から項目名を探す
 */
function findItemName(row) {
  for (let i = 0; i < 6; i++) {
    if (row[i] && String(row[i]).trim()) {
      return String(row[i]).trim();
    }
  }
  return null;
}

/**
 * 予実出力シートを更新
 */
function updateOutputSheet(sheet, budgetData, actualData) {
  // シートをクリア
  sheet.clear();

  // ヘッダー設定
  sheet.getRange('B1').setValue('予実管理').setFontSize(14).setFontWeight('bold');
  sheet.getRange('B3').setValue('単位：円');

  // 月ヘッダー
  let col = 3;
  MONTHS.forEach(month => {
    sheet.getRange(4, col).setValue(month);
    sheet.getRange(4, col, 1, 4).merge().setHorizontalAlignment('center')
      .setBackground('#d9e7fd');
    col += 4;
  });

  // サブヘッダー
  col = 2;
  sheet.getRange(5, col).setValue('項目').setFontWeight('bold');
  col++;
  MONTHS.forEach(() => {
    sheet.getRange(5, col).setValue('予算');
    sheet.getRange(5, col + 1).setValue('実績');
    sheet.getRange(5, col + 2).setValue('差異');
    sheet.getRange(5, col + 3).setValue('達成率');
    col += 4;
  });
  sheet.getRange(5, 3, 1, 48).setBackground('#4a86e8').setFontColor('white').setFontWeight('bold');

  // 項目データ
  PL_ITEMS.forEach((item, index) => {
    const row = 6 + index;
    const indent = '　'.repeat(item.level);
    sheet.getRange(row, 2).setValue(indent + item.name);

    // 月別データ
    col = 3;
    MONTHS.forEach((month, mIndex) => {
      const budget = budgetData[index] ? (budgetData[index][mIndex] || 0) : 0;
      const actual = actualData[item.name] ? (actualData[item.name][mIndex] || 0) : 0;
      const diff = actual - budget;
      const rate = budget !== 0 ? actual / budget : '';

      sheet.getRange(row, col).setValue(budget).setNumberFormat('#,##0');
      sheet.getRange(row, col + 1).setValue(actual).setNumberFormat('#,##0');
      sheet.getRange(row, col + 2).setValue(diff).setNumberFormat('#,##0');

      // 差異の色分け
      if (diff > 0) {
        sheet.getRange(row, col + 2).setBackground('#b7e1cd');
      } else if (diff < 0) {
        sheet.getRange(row, col + 2).setBackground('#f4c7c3');
      }

      if (rate !== '') {
        sheet.getRange(row, col + 3).setValue(rate).setNumberFormat('0%');
        if (rate >= 1) {
          sheet.getRange(row, col + 3).setBackground('#b7e1cd');
        } else if (rate < 0.8) {
          sheet.getRange(row, col + 3).setBackground('#f4c7c3');
        }
      }

      col += 4;
    });
  });

  // 列幅調整
  sheet.setColumnWidth(2, 180);
  for (let i = 3; i <= 50; i++) {
    sheet.setColumnWidth(i, 80);
  }
}

// ============================================
// 初期化機能
// ============================================

/**
 * 予算入力シートを初期化
 */
function initBudgetSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('予算入力');

  if (sheet) {
    const ui = SpreadsheetApp.getUi();
    const response = ui.alert('確認', '予算入力シートは既に存在します。初期化しますか？\n（データは削除されます）', ui.ButtonSet.YES_NO);
    if (response !== ui.Button.YES) return;
    sheet.clear();
  } else {
    sheet = ss.insertSheet('予算入力');
  }

  // ヘッダー
  sheet.getRange('B1').setValue('予算入力シート').setFontSize(14).setFontWeight('bold');
  sheet.getRange('B3').setValue('単位：円');

  // 月ヘッダー
  const headers = ['項目', ...MONTHS, '期間累計'];
  sheet.getRange(4, 2, 1, headers.length).setValues([headers])
    .setBackground('#4a86e8').setFontColor('white').setFontWeight('bold');

  // 項目
  PL_ITEMS.forEach((item, index) => {
    const indent = '　'.repeat(item.level);
    sheet.getRange(5 + index, 2).setValue(indent + item.name);

    // 期間累計の数式
    const row = 5 + index;
    sheet.getRange(row, 15).setFormula(`=SUM(C${row}:N${row})`);
  });

  // 列幅
  sheet.setColumnWidth(2, 180);

  SpreadsheetApp.getUi().alert('✅ 完了', '予算入力シートを初期化しました。', SpreadsheetApp.getUi().ButtonSet.OK);
}

/**
 * 実績入力シートを初期化
 */
function initActualSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('実績入力');

  if (sheet) {
    const ui = SpreadsheetApp.getUi();
    const response = ui.alert('確認', '実績入力シートは既に存在します。初期化しますか？\n（データは削除されます）', ui.ButtonSet.YES_NO);
    if (response !== ui.Button.YES) return;
    sheet.clear();
  } else {
    sheet = ss.insertSheet('実績入力');
  }

  sheet.getRange('B1').setValue('実績入力シート').setFontSize(14).setFontWeight('bold');
  sheet.getRange('B3').setValue('Money ForwardのCSVデータを下記に貼り付けてください：');
  sheet.getRange('B5').setValue('↓ CSVデータ開始行 ↓').setFontWeight('bold');

  // CSV貼り付けエリアの背景色
  sheet.getRange('B6:T50').setBackground('#fff2cc');

  SpreadsheetApp.getUi().alert('✅ 完了', '実績入力シートを初期化しました。\nB6セルにCSVデータを貼り付けてください。', SpreadsheetApp.getUi().ButtonSet.OK);
}

/**
 * 予実出力シートを初期化
 */
function initOutputSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('予実出力');

  if (sheet) {
    const ui = SpreadsheetApp.getUi();
    const response = ui.alert('確認', '予実出力シートは既に存在します。初期化しますか？', ui.ButtonSet.YES_NO);
    if (response !== ui.Button.YES) return;
    sheet.clear();
  } else {
    sheet = ss.insertSheet('予実出力');
  }

  sheet.getRange('B1').setValue('予実管理').setFontSize(14).setFontWeight('bold');
  sheet.getRange('B3').setValue('「予実シートを更新」を実行してください。');

  SpreadsheetApp.getUi().alert('✅ 完了', '予実出力シートを初期化しました。\nメニューから「予実シートを更新」を実行してください。', SpreadsheetApp.getUi().ButtonSet.OK);
}

// ============================================
// 自動化（トリガー）
// ============================================

/**
 * 毎日自動更新トリガーを設定
 */
function createDailyTrigger() {
  // 既存のトリガーを確認
  const triggers = ScriptApp.getProjectTriggers();
  const existingDaily = triggers.find(t =>
    t.getHandlerFunction() === 'autoUpdate' &&
    t.getEventType() === ScriptApp.EventType.CLOCK
  );

  if (existingDaily) {
    SpreadsheetApp.getUi().alert('既に毎日自動更新が設定されています。');
    return;
  }

  ScriptApp.newTrigger('autoUpdate')
    .timeBased()
    .everyDays(1)
    .atHour(9)
    .create();

  SpreadsheetApp.getUi().alert('✅ 設定完了', '毎日9時に自動更新するトリガーを設定しました。', SpreadsheetApp.getUi().ButtonSet.OK);
}

/**
 * 毎月自動更新トリガーを設定
 */
function createMonthlyTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  const existingMonthly = triggers.find(t =>
    t.getHandlerFunction() === 'autoUpdate' &&
    t.getEventType() === ScriptApp.EventType.CLOCK
  );

  if (existingMonthly) {
    SpreadsheetApp.getUi().alert('既に自動更新が設定されています。');
    return;
  }

  ScriptApp.newTrigger('autoUpdate')
    .timeBased()
    .onMonthDay(1)
    .atHour(10)
    .create();

  SpreadsheetApp.getUi().alert('✅ 設定完了', '毎月1日10時に自動更新するトリガーを設定しました。', SpreadsheetApp.getUi().ButtonSet.OK);
}

/**
 * 全トリガーを削除
 */
function deleteAllTriggers() {
  const triggers = ScriptApp.getProjectTriggers();

  if (triggers.length === 0) {
    SpreadsheetApp.getUi().alert('削除するトリガーがありません。');
    return;
  }

  const ui = SpreadsheetApp.getUi();
  const response = ui.alert('確認', `${triggers.length}個のトリガーを削除しますか？`, ui.ButtonSet.YES_NO);

  if (response === ui.Button.YES) {
    triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
    ui.alert('✅ 完了', '全てのトリガーを削除しました。', ui.ButtonSet.OK);
  }
}

/**
 * 自動更新（トリガーから呼び出される）
 */
function autoUpdate() {
  const ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  const budgetSheet = ss.getSheetByName('予算入力');
  const actualSheet = ss.getSheetByName('実績入力');
  let outputSheet = ss.getSheetByName('予実出力');

  if (!budgetSheet || !actualSheet) {
    console.log('シートが見つかりません');
    return;
  }

  if (!outputSheet) {
    outputSheet = ss.insertSheet('予実出力');
  }

  const budgetData = budgetSheet.getRange('C5:N37').getValues();
  const actualData = parseActualData(actualSheet);
  updateOutputSheet(outputSheet, budgetData, actualData);

  console.log('自動更新完了: ' + new Date().toISOString());
}

// ============================================
// ヘルプ
// ============================================

/**
 * 使い方を表示
 */
function showHelp() {
  const help = `
【GemEgg 予実管理 使い方】

1️⃣ 予算入力
   「予算入力」シートに月別予算を入力

2️⃣ 実績入力
   Money ForwardからCSVをダウンロード
   「実績入力」シートのB6セルに貼り付け

3️⃣ 予実更新
   メニュー「予実シートを更新」をクリック

📅 自動化
   「自動化」メニューからトリガー設定可能
   - 毎日9時に自動更新
   - 毎月1日10時に自動更新

❓ 問題がある場合
   各シートを「初期設定」メニューから再初期化
  `;

  SpreadsheetApp.getUi().alert('📖 使い方', help, SpreadsheetApp.getUi().ButtonSet.OK);
}