/**
 * GemEgg 経営ダッシュボード - 数式版
 *
 * このスクリプトは経営ダッシュボードにセル関数式を設定します。
 * 値を直接入力するのではなく、数式を設定するため、
 * 元データが変更されると自動的にダッシュボードも更新されます。
 *
 * 設定:
 * - 期間: 2025-04 ~ 2025-10 (7ヶ月間, 固定)
 * - 実績範囲: G列 ~ M列
 * - 予算範囲: C列 ~ I列
 */

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

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🔷 GemEgg予実管理')
    .addItem('📊 経営ダッシュボードに数式を設定', 'setupDashboardFormulas')
    .addItem('🔄 ダッシュボードをクリア', 'clearDashboard')
    .addSeparator()
    .addItem('ℹ️ 使い方', 'showHelp')
    .addToUi();
}

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

// 固定範囲: 2025-04 ~ 2025-10 (7ヶ月)
const ACTUAL_START_COL = 'G';  // 実績入力シート: G列 (2025-04)
const ACTUAL_END_COL = 'M';    // 実績入力シート: M列 (2025-10)
const BUDGET_START_COL = 'C';  // 予算入力シート: C列 (2025-04)
const BUDGET_END_COL = 'I';    // 予算入力シート: I列 (2025-10)

// 実績入力シートの行番号
const ACTUAL_ROWS = {
  売上高: 9,
  売上総損益: 18,
  役員報酬: 20,
  給料手当: 21,
  法定福利費: 22,
  販管費計: 37,
  営業損益: 32,
  経常損益: 35
};

// 予算入力シートの行番号
const BUDGET_ROWS = {
  売上高: 6,
  販管費計: 44
};

// ============================================
// メイン関数
// ============================================

/**
 * 経営ダッシュボードに数式を設定
 */
function setupDashboardFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName('経営ダッシュボード');

  if (!dashboard) {
    SpreadsheetApp.getUi().alert('エラー', '経営ダッシュボードシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  try {
    // セクション1: 年間予算 vs 締月地点実績
    setupSection1(dashboard);

    // セクション2: 締月地点予算 vs 実績
    setupSection2(dashboard);

    // セクション3: 収益性指標
    setupSection3(dashboard);

    // セクション4: 効率性指標
    setupSection4(dashboard);

    // セクション5: キャッシュフロー関連
    setupSection5(dashboard);

    // セクション6: その他指標
    setupSection6(dashboard);

    // 数値フォーマットを設定
    applyNumberFormats(dashboard);

    SpreadsheetApp.getUi().alert('✅ 完了', '経営ダッシュボードに数式を設定しました。\n\n※ C29セル（現金残高）は手動で入力してください。', SpreadsheetApp.getUi().ButtonSet.OK);

  } catch (error) {
    SpreadsheetApp.getUi().alert('エラー', `数式設定中にエラーが発生しました:\n${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

/**
 * ダッシュボードをクリア
 */
function clearDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName('経営ダッシュボード');

  if (!dashboard) {
    SpreadsheetApp.getUi().alert('エラー', '経営ダッシュボードシートが見つかりません。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  const ui = SpreadsheetApp.getUi();
  const response = ui.alert('確認', 'ダッシュボードの全ての数式をクリアしますか？', ui.ButtonSet.YES_NO);

  if (response !== ui.Button.YES) return;

  // データ範囲をクリア（ヘッダーは残す）
  dashboard.getRange('C8:E11').clear();   // セクション1
  dashboard.getRange('C14:F17').clear();  // セクション2
  dashboard.getRange('C20:C22').clear();  // セクション3
  dashboard.getRange('C25:D26').clear();  // セクション4
  dashboard.getRange('C29:E33').clear();  // セクション5
  dashboard.getRange('C36:C37').clear();  // セクション6

  ui.alert('✅ 完了', 'ダッシュボードをクリアしました。', ui.ButtonSet.OK);
}

// ============================================
// セクション別設定関数
// ============================================

/**
 * セクション1: 年間予算 vs 締月地点実績
 *
 * 場所: C8:E11
 *
 * | 項目 | 年間予算 (C) | 締月地点実績 (D) | 達成率 (E) |
 * |------|-------------|-----------------|-----------|
 * | 売上高 (8) | 予算!O6 | SUM(実績!G9:M9) | D8/C8 |
 * | 売上総利益 (9) | 予算!O6 | SUM(実績!G18:M18) | D9/C9 |
 * | 営業利益 (10) | 予算!O6 - 予算!O44 | SUM(実績!G32:M32) | D10/C10 |
 * | 経常利益 (11) | C10 | SUM(実績!G35:M35) | D11/C11 |
 */
function setupSection1(dashboard) {
  console.log('セクション1: 年間予算 vs 締月地点実績');

  // C8: 年間予算売上
  dashboard.getRange('C8').setFormula('=予算入力!O6');

  // C9: 年間予算売上総利益 (売上原価が0のため売上高と同じ)
  dashboard.getRange('C9').setFormula('=予算入力!O6');

  // C10: 年間予算営業利益 (売上総利益 - 販管費)
  dashboard.getRange('C10').setFormula('=C9-予算入力!O44');

  // C11: 年間予算経常利益 (営業利益と同じ)
  dashboard.getRange('C11').setFormula('=C10');

  // D8: 締月地点実績売上 (2025-04 ~ 2025-10)
  dashboard.getRange('D8').setFormula(`=SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.売上高}:${ACTUAL_END_COL}${ACTUAL_ROWS.売上高})`);

  // D9: 締月地点実績売上総利益
  dashboard.getRange('D9').setFormula(`=SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.売上総損益}:${ACTUAL_END_COL}${ACTUAL_ROWS.売上総損益})`);

  // D10: 締月地点実績営業利益
  dashboard.getRange('D10').setFormula(`=SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.営業損益}:${ACTUAL_END_COL}${ACTUAL_ROWS.営業損益})`);

  // D11: 締月地点実績経常利益
  dashboard.getRange('D11').setFormula(`=SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.経常損益}:${ACTUAL_END_COL}${ACTUAL_ROWS.経常損益})`);

  // E8-E11: 達成率 (実績 / 予算)
  dashboard.getRange('E8').setFormula('=IFERROR(D8/C8,0)');
  dashboard.getRange('E9').setFormula('=IFERROR(D9/C9,0)');
  dashboard.getRange('E10').setFormula('=IFERROR(D10/C10,0)');
  dashboard.getRange('E11').setFormula('=IFERROR(D11/C11,0)');
}

/**
 * セクション2: 締月地点予算 vs 実績
 *
 * 場所: C14:F17
 *
 * | 項目 | 締月地点予算 (C) | 締月地点実績 (D) | 予実差 (E) | 予実比 (F) |
 * |------|-----------------|-----------------|-----------|-----------|
 * | 売上高 (14) | SUM(予算!C6:I6) | =D8 | D14-C14 | D14/C14 |
 * | 売上総利益 (15) | SUM(予算!C6:I6) | =D9 | D15-C15 | D15/C15 |
 * | 営業利益 (16) | C15-SUM(予算!C44:I44) | =D10 | D16-C16 | D16/C16 |
 * | 経常利益 (17) | C16 | =D11 | D17-C17 | D17/C17 |
 */
function setupSection2(dashboard) {
  console.log('セクション2: 締月地点予算 vs 実績');

  // C14: 締月地点予算売上
  dashboard.getRange('C14').setFormula(`=SUM(予算入力!${BUDGET_START_COL}${BUDGET_ROWS.売上高}:${BUDGET_END_COL}${BUDGET_ROWS.売上高})`);

  // C15: 締月地点予算売上総利益
  dashboard.getRange('C15').setFormula(`=SUM(予算入力!${BUDGET_START_COL}${BUDGET_ROWS.売上高}:${BUDGET_END_COL}${BUDGET_ROWS.売上高})`);

  // C16: 締月地点予算営業利益 (売上総利益 - 販管費)
  dashboard.getRange('C16').setFormula(`=C15-SUM(予算入力!${BUDGET_START_COL}${BUDGET_ROWS.販管費計}:${BUDGET_END_COL}${BUDGET_ROWS.販管費計})`);

  // C17: 締月地点予算経常利益
  dashboard.getRange('C17').setFormula('=C16');

  // D14-D17: 締月地点実績 (セクション1を参照)
  dashboard.getRange('D14').setFormula('=D8');
  dashboard.getRange('D15').setFormula('=D9');
  dashboard.getRange('D16').setFormula('=D10');
  dashboard.getRange('D17').setFormula('=D11');

  // E14-E17: 予実差 (実績 - 予算)
  dashboard.getRange('E14').setFormula('=D14-C14');
  dashboard.getRange('E15').setFormula('=D15-C15');
  dashboard.getRange('E16').setFormula('=D16-C16');
  dashboard.getRange('E17').setFormula('=D17-C17');

  // F14-F17: 予実比 (実績 / 予算)
  dashboard.getRange('F14').setFormula('=IFERROR(D14/C14,0)');
  dashboard.getRange('F15').setFormula('=IFERROR(D15/C15,0)');
  dashboard.getRange('F16').setFormula('=IFERROR(D16/C16,0)');
  dashboard.getRange('F17').setFormula('=IFERROR(D17/C17,0)');
}

/**
 * セクション3: 収益性指標
 *
 * 場所: C20:C22
 *
 * | 指標 | 計算式 |
 * |------|--------|
 * | 売上総利益率 (20) | D9/D8 |
 * | 営業利益率 (21) | D10/D8 |
 * | 経常利益率 (22) | D11/D8 |
 */
function setupSection3(dashboard) {
  console.log('セクション3: 収益性指標');

  // C20: 売上総利益率
  dashboard.getRange('C20').setFormula('=IFERROR(D9/D8,0)');

  // C21: 営業利益率
  dashboard.getRange('C21').setFormula('=IFERROR(D10/D8,0)');

  // C22: 経常利益率
  dashboard.getRange('C22').setFormula('=IFERROR(D11/D8,0)');
}

/**
 * セクション4: 効率性指標
 *
 * 場所: C25:D26
 *
 * | 指標 | 比率 (C) | 金額 (D) |
 * |------|---------|---------|
 * | 販管費率 (25) | D25/D8 | SUM(実績!G37:M37) |
 * | 人件費率 (26) | D26/D8 | SUM(実績!G20:M20)+SUM(実績!G21:M21)+SUM(実績!G22:M22) |
 */
function setupSection4(dashboard) {
  console.log('セクション4: 効率性指標');

  // D25: 販管費
  dashboard.getRange('D25').setFormula(`=SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.販管費計}:${ACTUAL_END_COL}${ACTUAL_ROWS.販管費計})`);

  // C25: 販管費率 (販管費 / 売上高)
  dashboard.getRange('C25').setFormula('=IFERROR(D25/D8,0)');

  // D26: 人件費 (役員報酬 + 給料手当 + 法定福利費)
  const personnelFormula =
    `=SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.役員報酬}:${ACTUAL_END_COL}${ACTUAL_ROWS.役員報酬})` +
    `+SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.給料手当}:${ACTUAL_END_COL}${ACTUAL_ROWS.給料手当})` +
    `+SUM(実績入力!${ACTUAL_START_COL}${ACTUAL_ROWS.法定福利費}:${ACTUAL_END_COL}${ACTUAL_ROWS.法定福利費})`;
  dashboard.getRange('D26').setFormula(personnelFormula);

  // C26: 人件費率 (人件費 / 売上高)
  dashboard.getRange('C26').setFormula('=IFERROR(D26/D8,0)');
}

/**
 * セクション5: キャッシュフロー関連
 *
 * 場所: C29:E33
 *
 * | 指標 | 値 (C) | 期間 (E) |
 * |------|--------|---------|
 * | 現金残高 (29) | 手動入力 | - |
 * | グロスバーンレート (30) | D25/7 | 7ヶ月 |
 * | ランウェイ(グロス) (31) | C29/C30 | - |
 * | ネットバーンレート (32) | D11/7 | - |
 * | ランウェイ(ネット) (33) | C29/ABS(C32) | - |
 */
function setupSection5(dashboard) {
  console.log('セクション5: キャッシュフロー関連');

  // C29: 現金残高 - 手動入力のため空白のまま
  // dashboard.getRange('C29').setValue(''); // 既存の値を保持

  // E30: 期間表示
  dashboard.getRange('E30').setValue('7ヶ月');

  // C30: グロスバーンレート (販管費 / 7ヶ月)
  dashboard.getRange('C30').setFormula('=IFERROR(D25/7,0)');

  // C31: ランウェイ(グロス) (現金残高 / グロスバーンレート)
  dashboard.getRange('C31').setFormula('=IFERROR(C29/C30,0)');

  // C32: ネットバーンレート (経常利益 / 7ヶ月)
  dashboard.getRange('C32').setFormula('=IFERROR(D11/7,0)');

  // C33: ランウェイ(ネット)
  dashboard.getRange('C33').setFormula('=IF(C32<0,C29/ABS(C32),"∞")');
}

/**
 * セクション6: その他指標
 *
 * 場所: C36:C37
 *
 * | 指標 | 計算式 |
 * |------|--------|
 * | 損益分岐点売上高 (36) | D25 (販管費を固定費として) |
 * | 損益分岐点比率 (37) | C36/D8 |
 */
function setupSection6(dashboard) {
  console.log('セクション6: その他指標');

  // C36: 損益分岐点売上高 (販管費を固定費として計算)
  dashboard.getRange('C36').setFormula('=D25');

  // C37: 損益分岐点比率 (損益分岐点売上高 / 実績売上高)
  dashboard.getRange('C37').setFormula('=IFERROR(C36/D8,0)');
}

/**
 * 数値フォーマットを適用
 */
function applyNumberFormats(dashboard) {
  console.log('数値フォーマットを適用');

  // 金額フォーマット (#,##0)
  dashboard.getRange('C8:D11').setNumberFormat('#,##0');
  dashboard.getRange('C14:E17').setNumberFormat('#,##0');
  dashboard.getRange('D25:D26').setNumberFormat('#,##0');
  dashboard.getRange('C29:C32').setNumberFormat('#,##0');
  dashboard.getRange('C36').setNumberFormat('#,##0');

  // パーセントフォーマット (0%)
  dashboard.getRange('E8:E11').setNumberFormat('0%');
  dashboard.getRange('F14:F17').setNumberFormat('0%');

  // パーセントフォーマット (0.0%)
  dashboard.getRange('C20:C22').setNumberFormat('0.0%');
  dashboard.getRange('C25:C26').setNumberFormat('0.0%');
  dashboard.getRange('C37').setNumberFormat('0.0%');

  // ランウェイ (月数) (0.0)
  dashboard.getRange('C31').setNumberFormat('0.0');
}

/**
 * 使い方を表示
 */
function showHelp() {
  const message =
    '【経営ダッシュボード - 数式版】\n\n' +
    '1. 「📊 経営ダッシュボードに数式を設定」を実行\n' +
    '   → 全てのセルに数式が設定されます\n\n' +
    '2. C29セル（現金残高）を手動で入力\n\n' +
    '3. 予算入力・実績入力シートのデータを更新すると\n' +
    '   自動的にダッシュボードも更新されます\n\n' +
    '【対象期間】\n' +
    '- 2025-04 ~ 2025-10 (7ヶ月間, 固定)\n\n' +
    '【注意事項】\n' +
    '- 予算入力シート: 販管費計は44行\n' +
    '- 実績入力シート: 販管費計は37行\n' +
    '- 期間を変更したい場合は、スクリプトの定数を変更してください';

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