/**
 * NBS HubSpot → Google Sheets 同期スクリプト (Looker Studio データソース用)
 *
 * 目的: NBS Starter (245764753) の Contacts / Deals / Companies を
 *       Apps Script で取得し、Looker Studio が参照するスプシに書き込む。
 *
 * 実行方法:
 *   1. Apps Script editor (https://script.google.com) で新規プロジェクト作成
 *   2. このファイルを Code.gs としてペースト
 *   3. プロジェクト Settings → Script Properties に以下追加:
 *      - HUBSPOT_TOKEN: Private App Access Token (NBS Starter で発行)
 *      - SHEET_ID: 1VhGBUELKaV7-FpLES-pj0ikzGiG7X9CQWMtDGFcsWxQ
 *   4. 関数 `setupDailyTrigger` を 1 回手動実行 (権限承認 + Trigger 設定)
 *   5. 関数 `syncAllToSheets` を 1 回手動実行 (初回データ投入確認)
 *
 * 自動更新: 毎日 朝 7:00 (JST) に syncAllToSheets が実行される
 */

// ============================================================================
// 設定 (Script Properties から取得)
// ============================================================================

function getConfig() {
  const props = PropertiesService.getScriptProperties();
  return {
    HUBSPOT_TOKEN: props.getProperty('HUBSPOT_TOKEN'),
    SHEET_ID: props.getProperty('SHEET_ID'),
    HUBSPOT_BASE: 'https://api.hubapi.com',
    KINTONE_USER: props.getProperty('KINTONE_USER'),
    KINTONE_PASS: props.getProperty('KINTONE_PASS'),
    KINTONE_BASE: props.getProperty('KINTONE_BASE') || 'https://nbs.cybozu.com',
    KINTONE_LAST_SYNC: props.getProperty('KINTONE_LAST_SYNC'),
  };
}

function getKintoneTokenForApp(app) {
  return PropertiesService.getScriptProperties().getProperty(`KINTONE_TOKEN_${app}`);
}

// アプリ別 Token があればそれを優先、なければ Basic 認証フォールバック
// (新規アプリ 385/377/381 等で Token 発行作業を不要にする)
function getKintoneAuthHeader(app) {
  const props = PropertiesService.getScriptProperties();
  const token = props.getProperty(`KINTONE_TOKEN_${app}`);
  if (token) {
    return { 'X-Cybozu-API-Token': token };
  }
  const user = props.getProperty('KINTONE_USER');
  const pass = props.getProperty('KINTONE_PASS');
  if (user && pass) {
    const auth = Utilities.base64Encode(`${user}:${pass}`);
    return { 'X-Cybozu-Authorization': auth };
  }
  return null;
}

// ============================================================================
// 値マッピング (Looker Studio 計算フィールド代替)
// ============================================================================

const PRODUCT_LABELS = {
  hydrogen: "水素", hair_removal: "脱毛機", slimming: "痩身機",
  plagene: "PLAGENE", cosmetics: "化粧品", home_use: "家庭用", medical: "医療機器",
};
const METHOD_LABELS = {
  hp: "HP", tel: "TEL", line: "LINE", exhibition: "展示会",
};
const SOURCE_LABELS = {
  inquiry: "問合せ", referral: "紹介", exhibition: "展示会",
  repeat_purchase: "追加購入", dm: "DM", sns: "SNS",
  magazine: "情報誌", teleappo: "テレアポ", line: "LINE",
};
const TYPE_LABELS = {
  personal: "個人", corporate: "法人",
};
const PIPELINE_LABELS = {
  "2224114394": "直販",
  "default": "代理店",
};
const OWNER_ID_TO_NAME_JP = {
  "164145859": "西尾 和也",
  "164145856": "山本 幸",
  "164145857": "川村 麟太郎",
  "164145855": "江部 綾乃",
  "164145853": "久野 紘子",
  "164145858": "加藤 真央",
  "164145854": "佐藤 大介",
  "47296229": "栗田 一輝",
  "163543457": "山城 賢",
};

const STAGE_LABELS = {
  // 直販 stages
  "3563886294": "01_新規問合せ", "3563886295": "02_見込み20%",
  "3563886296": "03_検討中30%", "3563886297": "04_他社比較40%",
  "3563886298": "05_決裁待ち60%", "3563886299": "06_助成金70%",
  "3563886300": "07_支払80%", "3563886301": "08_納期90%",
  "3563886302": "09_成約", "3563886303": "10_失注",
  // 代理店 stages
  "3563696866": "01_新規問合せ", "3563696867": "02_見込み20%",
  "3563696868": "03_検討中30%", "3563696869": "04_他社比較40%",
  "3563886304": "05_決裁待ち60%", "3563696870": "06_助成金70%",
  "3563886305": "07_支払80%", "3563696871": "08_納期90%",
  "3563886306": "09_成約", "closedlost": "10_失注",
};

function lookup(map, v) {
  if (v === null || v === undefined || v === '') return '';
  return map[v] || v;
}

// Date オブジェクト or 文字列を yyyy-MM-dd ISO 形式に変換
function toIsoDate(val) {
  if (val === null || val === undefined || val === '') return '';
  if (val instanceof Date) {
    return Utilities.formatDate(val, 'Asia/Tokyo', 'yyyy-MM-dd');
  }
  const s = String(val);
  // 既に yyyy-MM-dd 形式
  if (/^\d{4}-\d{2}-\d{2}/.test(s)) return s.substring(0, 10);
  // Apps Script Date.toString 形式 (例: "Fri Apr 26 2026 00:00:00 GMT+0900")
  const d = new Date(s);
  if (!isNaN(d.getTime())) {
    return Utilities.formatDate(d, 'Asia/Tokyo', 'yyyy-MM-dd');
  }
  return s;
}

// 数値型として扱うプロパティ (Looker Studio で SUM/AVG 集計するため)
const NUMERIC_PROPERTIES = new Set([
  'amount', 'nbs_mikomi_pct', 'hs_object_id',
]);

function coerceValue(propName, val) {
  if (val === null || val === undefined || val === '') return '';
  if (NUMERIC_PROPERTIES.has(propName)) {
    const num = Number(val);
    return isNaN(num) ? val : num;
  }
  return String(val);
}

// Contacts → Deals の JOIN 用キャッシュ (inquiry_number → product/method/source)
let _inquiryCache = {};

function buildInquiryCache(contactRecords) {
  _inquiryCache = {};
  contactRecords.forEach(c => {
    const p = c.properties || {};
    const inq = p.nbs_inquiry_number;
    if (inq) {
      _inquiryCache[inq] = {
        product: p.nbs_inquiry_product || '',
        method: p.nbs_inquiry_method || '',
        source: p.nbs_inquiry_source || '',
      };
    }
  });
  Logger.log(`Inquiry cache built: ${Object.keys(_inquiryCache).length} entries`);
}

const WIN_STAGES = ["3563886302", "3563886306"];  // 直販 100% / 代理店 100%
const LOST_STAGES = ["3563886303", "closedlost"]; // 直販失注 / 代理店失注

// ============================================================================
// プロパティ定義 (NBS 92 カスタムプロパティ + 標準)
// ============================================================================

const CONTACT_PROPERTIES = [
  // 標準
  'firstname', 'lastname', 'email', 'phone',
  'createdate', 'lastmodifieddate',
  'lifecyclestage', 'hs_lead_status', 'hubspot_owner_id',
  // NBS 顧客識別
  'nbs_customer_number',
  'nbs_inquiry_number',
  'nbs_kintone_inquiry_id',
  // NBS 問合せ情報
  'nbs_inquiry_product',
  'nbs_inquiry_method',
  'nbs_inquiry_source',
  'nbs_inquiry_source_detail',
  'nbs_inquiry_date',
  'nbs_inquiry_content',
  // NBS 顧客属性
  'nbs_company_type',
  'nbs_salon_name',
  'nbs_name_kana',
  // NBS 紹介・流入
  'nbs_referral_person',
  'nbs_lead_generator',
];

const DEAL_PROPERTIES = [
  // 標準
  'dealname', 'dealstage', 'pipeline', 'amount',
  'createdate', 'closedate', 'hs_lastmodifieddate',
  'hubspot_owner_id', 'dealtype',
  // NBS 案件識別
  'nbs_deal_number',
  'nbs_inquiry_number',
  // NBS 案件詳細
  'nbs_demo_date',
  'nbs_demo_summary',
  'nbs_demo_category',
  'nbs_inquiry_trigger',
  'nbs_prospect_concerns',
  'nbs_decision_factors',
  'nbs_proposed_solution',
  'nbs_next_follow_up',
  'nbs_salon_concept',
  'nbs_hpb_sns',
  'nbs_lost_reason_detail',
  'nbs_mikomi_pct',
  // NBS Pipeline 区分
  'nbs_pipeline_type',
  'nbs_agent_type',
];

const COMPANY_PROPERTIES = [
  // 標準
  'name', 'phone', 'address', 'address2', 'state', 'zip',
  'createdate', 'hs_lastmodifieddate',
  'lifecyclestage', 'hubspot_owner_id',
  // NBS 顧客識別
  'nbs_customer_number',
  'nbs_salon_name',
  'nbs_legal_name',
  'nbs_legal_name_kana',
  'nbs_company_kana',
  'nbs_representative_name',
  // NBS 顧客属性
  'nbs_company_type',
  'nbs_agent_type',
  'nbs_kintone_record_id',
  'nbs_freee_id',
];

// ============================================================================
// HubSpot API 呼び出し (汎用)
// ============================================================================

function fetchAllRecords(objectType, properties) {
  const config = getConfig();
  const url = `${config.HUBSPOT_BASE}/crm/v3/objects/${objectType}/search`;
  let after = null;
  const all = [];
  let pageCount = 0;

  do {
    const response = UrlFetchApp.fetch(url, {
      method: 'post',
      headers: {
        'Authorization': `Bearer ${config.HUBSPOT_TOKEN}`,
        'Content-Type': 'application/json',
      },
      payload: JSON.stringify({
        filterGroups: [
          { filters: [{ propertyName: 'createdate', operator: 'HAS_PROPERTY' }] }
        ],
        properties: properties,
        sorts: [{ propertyName: 'createdate', direction: 'DESCENDING' }],
        limit: 100,
        after: after,
      }),
      muteHttpExceptions: true,
    });

    const status = response.getResponseCode();
    if (status >= 300) {
      Logger.log(`HubSpot API ${objectType} error: status=${status}`);
      Logger.log(response.getContentText().substring(0, 500));
      break;
    }

    const json = JSON.parse(response.getContentText());
    all.push(...(json.results || []));
    after = json.paging && json.paging.next ? json.paging.next.after : null;
    pageCount++;
    Logger.log(`${objectType}: page ${pageCount}, fetched ${all.length} records (total)`);
    Utilities.sleep(500); // レート制限対策 (Search API: 4 req/sec、Bandwidth quota 配慮)
  } while (after && pageCount < 200); // 最大 200 ページ = 20,000 records (1万件規模対応)

  return all;
}

// ============================================================================
// Sheet 書き込み
// ============================================================================

function writeToSheet(tabName, records, properties, enrichFn) {
  const config = getConfig();
  const ss = SpreadsheetApp.openById(config.SHEET_ID);
  let sheet = ss.getSheetByName(tabName);
  if (!sheet) {
    sheet = ss.insertSheet(tabName);
  }

  // 拡張列 (enrichFn でオブジェクト返却 → keys が列名、values が値)
  const enrichSample = enrichFn ? enrichFn({ properties: {} }) : {};
  const enrichKeys = Object.keys(enrichSample);

  // ヘッダー
  const headers = ['id', 'createdAt', 'updatedAt', ...properties, ...enrichKeys];

  // データ行 (数値型プロパティは Number 型で書き込み → Looker Studio で SUM 集計可能に)
  const rows = records.map(r => {
    const baseProps = properties.map(p => coerceValue(p, r.properties && r.properties[p]));
    const enriched = enrichFn ? enrichFn(r) : {};
    const enrichVals = enrichKeys.map(k => enriched[k] || '');
    return [r.id, r.createdAt || '', r.updatedAt || '', ...baseProps, ...enrichVals];
  });

  // クリア + 書き込み
  sheet.clear();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.setFrozenRows(1);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }

  // 列幅自動調整
  for (let i = 1; i <= headers.length; i++) {
    sheet.autoResizeColumn(i);
  }

  Logger.log(`✅ ${tabName}: wrote ${rows.length} rows × ${headers.length} cols`);
}

// ============================================================================
// 個別同期関数
// ============================================================================

function enrichContact(r) {
  const p = r.properties || {};
  return {
    '商材ラベル_JP': lookup(PRODUCT_LABELS, p.nbs_inquiry_product),
    '方法ラベル_JP': lookup(METHOD_LABELS, p.nbs_inquiry_method),
    'きっかけラベル_JP': lookup(SOURCE_LABELS, p.nbs_inquiry_source),
    '法人個人ラベル_JP': lookup(TYPE_LABELS, p.nbs_company_type),
  };
}

function enrichDeal(r) {
  const p = r.properties || {};
  const stage = p.dealstage;
  const inq = p.nbs_inquiry_number;
  const inqInfo = _inquiryCache[inq] || {};
  return {
    'Pipelineラベル_JP': lookup(PIPELINE_LABELS, p.pipeline),
    'Stageラベル_JP': lookup(STAGE_LABELS, stage),
    'WonFlag': WIN_STAGES.indexOf(stage) >= 0 ? 1 : 0,
    'LostFlag': LOST_STAGES.indexOf(stage) >= 0 ? 1 : 0,
    '営業担当者_JP': lookup(OWNER_ID_TO_NAME_JP, p.hubspot_owner_id),
    '商材ラベル_JP': lookup(PRODUCT_LABELS, inqInfo.product),
    '方法ラベル_JP': lookup(METHOD_LABELS, inqInfo.method),
    'きっかけラベル_JP': lookup(SOURCE_LABELS, inqInfo.source),
  };
}

function enrichCompany(r) {
  const p = r.properties || {};
  return {
    '法人個人ラベル_JP': lookup(TYPE_LABELS, p.nbs_company_type),
  };
}

function syncContactsToSheet() {
  Logger.log('=== Sync Contacts ===');
  const records = fetchAllRecords('contacts', CONTACT_PROPERTIES);
  buildInquiryCache(records);  // Deals JOIN 用キャッシュ生成
  writeToSheet('LookerStudio_Contacts', records, CONTACT_PROPERTIES, enrichContact);
}

function syncDealsToSheet() {
  Logger.log('=== Sync Deals ===');
  // Inquiry キャッシュ未生成なら、軽量取得 (inquiry_number / product / method / source のみ) で構築
  if (Object.keys(_inquiryCache).length === 0) {
    Logger.log('Inquiry cache empty, building from Contacts (lightweight fetch)...');
    const minimalContacts = fetchAllRecords('contacts', [
      'nbs_inquiry_number', 'nbs_inquiry_product',
      'nbs_inquiry_method', 'nbs_inquiry_source',
    ]);
    buildInquiryCache(minimalContacts);
    Utilities.sleep(2000); // レート制限回避
  }
  const records = fetchAllRecords('deals', DEAL_PROPERTIES);
  writeToSheet('LookerStudio_Deals', records, DEAL_PROPERTIES, enrichDeal);
}

function syncCompaniesToSheet() {
  Logger.log('=== Sync Companies ===');
  const records = fetchAllRecords('companies', COMPANY_PROPERTIES);
  writeToSheet('LookerStudio_Companies', records, COMPANY_PROPERTIES, enrichCompany);
}

// ============================================================================
// 一括同期 (Trigger 用)
// ============================================================================

function syncAllToSheets() {
  const startTime = new Date();
  Logger.log(`🚀 syncAllToSheets started at ${startTime.toISOString()}`);

  try {
    syncContactsToSheet();
  } catch (e) {
    Logger.log(`❌ Contacts sync failed: ${e.message}`);
  }

  try {
    syncDealsToSheet();
  } catch (e) {
    Logger.log(`❌ Deals sync failed: ${e.message}`);
  }

  try {
    syncCompaniesToSheet();
  } catch (e) {
    Logger.log(`❌ Companies sync failed: ${e.message}`);
  }

  // メタ情報を _SyncLog タブに記録
  const config = getConfig();
  const ss = SpreadsheetApp.openById(config.SHEET_ID);
  let logSheet = ss.getSheetByName('_SyncLog');
  if (!logSheet) {
    logSheet = ss.insertSheet('_SyncLog');
    logSheet.appendRow(['timestamp', 'duration_sec', 'status']);
  }
  const duration = (new Date() - startTime) / 1000;
  logSheet.appendRow([startTime.toISOString(), duration, 'OK']);

  Logger.log(`✅ syncAllToSheets completed in ${duration}s`);
}

// ============================================================================
// Phase 2 — Stage Snapshot (毎朝 _StageSnapshots に行追記)
// ============================================================================

function recordStageSnapshot() {
  Logger.log('=== Record Stage Snapshot ===');
  const config = getConfig();
  const dealsSheet = SpreadsheetApp.openById(config.SHEET_ID).getSheetByName('LookerStudio_Deals');
  if (!dealsSheet) {
    Logger.log('LookerStudio_Deals not found, skip');
    return;
  }
  const data = dealsSheet.getDataRange().getValues();
  if (data.length < 2) return;

  const headers = data[0];
  const idxPipeline = headers.indexOf('pipeline');
  const idxStageJp = headers.indexOf('Stageラベル_JP');
  const idxPipelineJp = headers.indexOf('Pipelineラベル_JP');
  if (idxPipeline < 0 || idxStageJp < 0 || idxPipelineJp < 0) {
    Logger.log('Required columns not found');
    return;
  }

  // pipeline_jp + stage_jp ごとに件数集計
  const counter = {};
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const pipelineJp = row[idxPipelineJp];
    const stageJp = row[idxStageJp];
    const key = `${pipelineJp}|${stageJp}`;
    counter[key] = (counter[key] || 0) + 1;
  }

  const today = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd');

  // _StageSnapshots に行追記
  const ss = SpreadsheetApp.openById(config.SHEET_ID);
  let snap = ss.getSheetByName('_StageSnapshots');
  if (!snap) {
    snap = ss.insertSheet('_StageSnapshots');
    snap.appendRow(['date', 'pipeline_jp', 'stage_jp', 'count']);
  }
  Object.keys(counter).forEach(key => {
    const [pj, sj] = key.split('|');
    snap.appendRow([today, pj, sj, counter[key]]);
  });
  Logger.log(`✅ ${Object.keys(counter).length} stage snapshot rows recorded for ${today}`);
}

// ============================================================================
// Cohort 分析 — Deal Current State (毎朝上書き)
// ============================================================================

function recordDealCurrentState() {
  Logger.log('=== Record Deal Current State ===');
  const config = getConfig();
  const dealsSheet = SpreadsheetApp.openById(config.SHEET_ID).getSheetByName('LookerStudio_Deals');
  if (!dealsSheet) return;

  const data = dealsSheet.getDataRange().getValues();
  if (data.length < 2) return;
  const headers = data[0];
  const idxId = headers.indexOf('id');
  const idxInq = headers.indexOf('nbs_inquiry_number');
  const idxStage = headers.indexOf('dealstage');
  const idxStageJp = headers.indexOf('Stageラベル_JP');
  const idxPipelineJp = headers.indexOf('Pipelineラベル_JP');
  const idxOwner = headers.indexOf('hubspot_owner_id');
  const idxAmount = headers.indexOf('amount');
  const idxWon = headers.indexOf('WonFlag');
  const idxLost = headers.indexOf('LostFlag');

  // Inquiry → date マップ (Contacts から)
  const contactsSheet = SpreadsheetApp.openById(config.SHEET_ID).getSheetByName('LookerStudio_Contacts');
  const inqDateMap = {};
  if (contactsSheet) {
    const cd = contactsSheet.getDataRange().getValues();
    const ch = cd[0];
    const cIdxInq = ch.indexOf('nbs_inquiry_number');
    const cIdxDate = ch.indexOf('nbs_inquiry_date');
    for (let i = 1; i < cd.length; i++) {
      const inq = cd[i][cIdxInq];
      const date = cd[i][cIdxDate];
      if (inq && date) inqDateMap[String(inq)] = toIsoDate(date);
    }
  }

  // Deal Current State 構築
  const ss = SpreadsheetApp.openById(config.SHEET_ID);
  let cur = ss.getSheetByName('_DealCurrentState');
  if (cur) cur.clear();
  else cur = ss.insertSheet('_DealCurrentState');

  const stateHeaders = ['deal_id', 'inquiry_number', 'inquiry_date', 'cohort_month',
                       'pipeline_jp', 'stage_jp', 'won_flag', 'lost_flag',
                       'active_flag', 'owner_id', 'amount', 'snapshot_date'];
  // 列全体を TEXT 型に固定 (Sheet による Date 自動変換を防ぐ)
  cur.getRange('C:C').setNumberFormat('@'); // inquiry_date
  cur.getRange('D:D').setNumberFormat('@'); // cohort_month
  cur.getRange('L:L').setNumberFormat('@'); // snapshot_date
  SpreadsheetApp.flush();
  cur.appendRow(stateHeaders);

  const today = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd');
  const rows = [];
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const inqNo = String(row[idxInq] || '');
    const inqDate = inqDateMap[inqNo] || '';
    // ISO 形式 (yyyy-MM-dd) なら先頭 7 文字 = yyyy-MM
    const cohortMonth = (inqDate && /^\d{4}-\d{2}/.test(inqDate)) ? inqDate.substring(0, 7) : '';
    const wonFlag = row[idxWon] || 0;
    const lostFlag = row[idxLost] || 0;
    const activeFlag = (wonFlag == 0 && lostFlag == 0) ? 1 : 0;
    rows.push([
      row[idxId], inqNo, inqDate, cohortMonth,
      row[idxPipelineJp], row[idxStageJp], wonFlag, lostFlag,
      activeFlag, row[idxOwner], row[idxAmount] || 0, today,
    ]);
  }
  if (rows.length > 0) {
    cur.getRange(2, 1, rows.length, stateHeaders.length).setValues(rows);
  }
  Logger.log(`✅ _DealCurrentState: ${rows.length} rows updated`);
}

// ============================================================================
// Cohort Milestones (毎朝 _CohortMilestones に追記、永続蓄積)
// ============================================================================

function recordCohortMilestones() {
  Logger.log('=== Record Cohort Milestones ===');
  const config = getConfig();
  const stateSheet = SpreadsheetApp.openById(config.SHEET_ID).getSheetByName('_DealCurrentState');
  if (!stateSheet) {
    Logger.log('_DealCurrentState not found');
    return;
  }
  const data = stateSheet.getDataRange().getValues();
  if (data.length < 2) return;
  const headers = data[0];
  const idxCohort = headers.indexOf('cohort_month');
  const idxWon = headers.indexOf('won_flag');
  const idxLost = headers.indexOf('lost_flag');
  const idxActive = headers.indexOf('active_flag');

  // cohort_month 別に集計 (Date オブジェクト対応)
  const cohorts = {};
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    let cm = row[idxCohort];
    if (!cm) continue;
    // Date オブジェクトの場合は yyyy-MM に変換 (Sheet 自動変換対策)
    if (cm instanceof Date) {
      cm = Utilities.formatDate(cm, 'Asia/Tokyo', 'yyyy-MM');
    } else {
      cm = String(cm).substring(0, 7);
    }
    // yyyy-MM 形式以外は skip
    if (!/^\d{4}-\d{2}$/.test(cm)) continue;
    if (!cohorts[cm]) cohorts[cm] = { total: 0, won: 0, lost: 0, active: 0 };
    cohorts[cm].total++;
    cohorts[cm].won += Number(row[idxWon]) || 0;
    cohorts[cm].lost += Number(row[idxLost]) || 0;
    cohorts[cm].active += Number(row[idxActive]) || 0;
  }

  const ss = SpreadsheetApp.openById(config.SHEET_ID);
  let cm = ss.getSheetByName('_CohortMilestones');
  const cohortHeaders = ['recorded_date', 'cohort_month', 'days_elapsed',
                         'total', 'won', 'lost', 'active', 'win_rate', 'active_rate'];
  if (!cm) {
    cm = ss.insertSheet('_CohortMilestones');
    cm.appendRow(cohortHeaders);
  } else if (cm.getLastRow() === 0) {
    // ヘッダーが消えている場合は再追加 (手動 clear などの対策)
    cm.appendRow(cohortHeaders);
  }
  // A 列 (recorded_date) と B 列 (cohort_month) を TEXT 型に固定 (1行目以降全体)
  cm.getRange('A:A').setNumberFormat('@');
  cm.getRange('B:B').setNumberFormat('@');

  const today = new Date();
  const todayStr = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy-MM-dd');
  const newRows = [];
  Object.keys(cohorts).sort().forEach(cohortMonth => {
    const c = cohorts[cohortMonth];
    const monthStart = new Date(`${cohortMonth}-01T00:00:00+09:00`);
    const daysElapsed = Math.floor((today - monthStart) / (1000 * 60 * 60 * 24));
    const winRate = c.total > 0 ? (c.won / c.total * 100).toFixed(2) : '0.00';
    const activeRate = c.total > 0 ? (c.active / c.total * 100).toFixed(2) : '0.00';
    newRows.push([todayStr, cohortMonth, daysElapsed,
                  c.total, c.won, c.lost, c.active, winRate, activeRate]);
  });
  if (newRows.length > 0) {
    const startRow = cm.getLastRow() + 1;
    cm.getRange(startRow, 1, newRows.length, cohortHeaders.length).setValues(newRows);
  }
  Logger.log(`✅ _CohortMilestones: ${newRows.length} cohort rows recorded for ${todayStr}`);
}

// ============================================================================
// Phase 1 — Kintone → HubSpot 増分同期
// ============================================================================

function fetchKintoneIncremental(app, sinceTimestamp) {
  const config = getConfig();
  const token = getKintoneTokenForApp(app);
  if (!token) {
    Logger.log(`❌ KINTONE_TOKEN_${app} not configured in Script Properties`);
    return [];
  }
  let query = '';
  if (sinceTimestamp) {
    const ts = String(sinceTimestamp).trim();
    // 妥当性チェック: yyyy-MM-ddTHH:mm:ss[+HHMM|+HH:MM|Z]
    const validTs = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}([+-]\d{2}:?\d{2}|Z)$/.test(ts);
    if (!validTs) {
      Logger.log(`⚠️ KINTONE_LAST_SYNC format invalid: "${ts}". Expected: 2026-05-01T00:00:00+0900. Aborting fetch to prevent corrupt query.`);
      throw new Error(`Invalid KINTONE_LAST_SYNC format: "${ts}"`);
    }
    query = `更新日時 > "${ts}" `;
  }
  query += 'order by $id asc';

  const all = [];
  let offset = 0;
  let page = 1;
  while (offset < 10000) {
    const fullQuery = query + ` limit 500 offset ${offset}`;
    const url = `${config.KINTONE_BASE}/k/v1/records.json?app=${app}&query=${encodeURIComponent(fullQuery)}&totalCount=true`;
    const response = UrlFetchApp.fetch(url, {
      headers: { 'X-Cybozu-API-Token': token },
      muteHttpExceptions: true,
    });
    const status = response.getResponseCode();
    if (status !== 200) {
      Logger.log(`Kintone app=${app} status=${status}: ${response.getContentText().substring(0, 300)}`);
      break;
    }
    const json = JSON.parse(response.getContentText());
    const records = json.results || json.records || [];
    if (page === 1) Logger.log(`  app=${app} totalCount=${json.totalCount}`);
    all.push(...records);
    if (records.length < 500) break;
    offset += 500;
    page++;
    Utilities.sleep(500);
  }
  return all;
}

function syncKintoneToHubSpot() {
  Logger.log('=== Sync Kintone → HubSpot (incremental) ===');
  const config = getConfig();
  const since = config.KINTONE_LAST_SYNC || ''; // 初回は空 = 全件 (上限 10000)
  let success = true;

  // 問合せ (382) → Contacts
  try {
    const inquiries = fetchKintoneIncremental(382, since);
    Logger.log(`  Kintone 問合せ 取得: ${inquiries.length} 件`);
    if (inquiries.length > 0) upsertContactsFromKintone(inquiries);
  } catch (e) {
    Logger.log(`❌ Inquiries fetch failed: ${e.message}`);
    success = false;
  }

  // 商談 (383) → Deals
  try {
    const dealsKintone = fetchKintoneIncremental(383, since);
    Logger.log(`  Kintone 商談 取得: ${dealsKintone.length} 件`);
    if (dealsKintone.length > 0) upsertDealsFromKintone(dealsKintone);
  } catch (e) {
    Logger.log(`❌ Deals fetch failed: ${e.message}`);
    success = false;
  }

  // エラー時は KINTONE_LAST_SYNC を更新しない (次回も同じ範囲を再試行)
  if (success) {
    const nowStr = Utilities.formatDate(new Date(), 'Asia/Tokyo', "yyyy-MM-dd'T'HH:mm:ssZ");
    PropertiesService.getScriptProperties().setProperty('KINTONE_LAST_SYNC', nowStr);
    Logger.log(`✅ KINTONE_LAST_SYNC = ${nowStr}`);
  } else {
    Logger.log(`⚠️ Skipped updating KINTONE_LAST_SYNC due to errors`);
  }
}

function upsertContactsFromKintone(records) {
  // 既存 nbs_kintone_inquiry_id で確認 → なければ email 重複確認 → 新規 create or 既存 update
  const config = getConfig();
  let created = 0;
  let skippedKintoneId = 0;
  let updatedByEmail = 0;
  let failed = 0;
  const errors = [];

  records.forEach((r, i) => {
    const kintoneId = String(r['$id']?.value || '');
    if (!kintoneId) { failed++; errors.push({ idx: i, reason: 'no kintoneId' }); return; }

    // 1) Kintone ID で既存確認
    const existingByKintone = searchHubSpotByProp('contacts', 'nbs_kintone_inquiry_id', kintoneId);
    if (existingByKintone) { skippedKintoneId++; return; }

    const props = buildContactPropsFromKintone(r);

    // 2) email 重複確認 (HubSpot Contacts は email ユニーク)
    if (props.email) {
      const existingByEmail = searchHubSpotByProp('contacts', 'email', props.email);
      if (existingByEmail) {
        // update: nbs_kintone_inquiry_id を上書きして紐付け
        const updateRes = UrlFetchApp.fetch(`${config.HUBSPOT_BASE}/crm/v3/objects/contacts/${existingByEmail.id}`, {
          method: 'patch',
          headers: { 'Authorization': `Bearer ${config.HUBSPOT_TOKEN}`, 'Content-Type': 'application/json' },
          payload: JSON.stringify({ properties: props }),
          muteHttpExceptions: true,
        });
        if (updateRes.getResponseCode() < 300) {
          updatedByEmail++;
        } else {
          failed++;
          errors.push({ kintoneId, status: updateRes.getResponseCode(), body: updateRes.getContentText().substring(0, 200) });
        }
        Utilities.sleep(150);
        return;
      }
    }

    // 3) 新規 create
    const res = UrlFetchApp.fetch(`${config.HUBSPOT_BASE}/crm/v3/objects/contacts`, {
      method: 'post',
      headers: { 'Authorization': `Bearer ${config.HUBSPOT_TOKEN}`, 'Content-Type': 'application/json' },
      payload: JSON.stringify({ properties: props }),
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() < 300) {
      created++;
    } else {
      failed++;
      errors.push({ kintoneId, status: res.getResponseCode(), body: res.getContentText().substring(0, 200) });
    }
    Utilities.sleep(150);
  });
  Logger.log(`  ✅ Contacts: created ${created} / updatedByEmail ${updatedByEmail} / skippedKintoneId ${skippedKintoneId} / failed ${failed}`);
  if (errors.length > 0) {
    Logger.log(`  ⚠️ Errors (max 5):`);
    errors.slice(0, 5).forEach(e => Logger.log(`    ${JSON.stringify(e)}`));
  }
}

function upsertDealsFromKintone(records) {
  const config = getConfig();
  let created = 0;
  let skipped = 0;
  let failed = 0;
  const errors = [];
  records.forEach(r => {
    const kintoneId = String(r['$id']?.value || '');
    if (!kintoneId) return;
    const existing = searchHubSpotByProp('deals', 'nbs_kintone_deal_id', kintoneId);
    if (existing) { skipped++; return; }
    const props = buildDealPropsFromKintone(r);
    const res = UrlFetchApp.fetch(`${config.HUBSPOT_BASE}/crm/v3/objects/deals`, {
      method: 'post',
      headers: { 'Authorization': `Bearer ${config.HUBSPOT_TOKEN}`, 'Content-Type': 'application/json' },
      payload: JSON.stringify({ properties: props }),
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() < 300) {
      created++;
    } else {
      failed++;
      errors.push({ kintoneId, status: res.getResponseCode(), body: res.getContentText().substring(0, 200) });
    }
    Utilities.sleep(150);
  });
  Logger.log(`  ✅ Deals: created ${created} / skipped ${skipped} / failed ${failed}`);
  if (errors.length > 0) {
    Logger.log(`  ⚠️ Errors (max 5):`);
    errors.slice(0, 5).forEach(e => Logger.log(`    ${JSON.stringify(e)}`));
  }
}

function searchHubSpotByProp(objectType, prop, value) {
  const config = getConfig();
  const res = UrlFetchApp.fetch(`${config.HUBSPOT_BASE}/crm/v3/objects/${objectType}/search`, {
    method: 'post',
    headers: { 'Authorization': `Bearer ${config.HUBSPOT_TOKEN}`, 'Content-Type': 'application/json' },
    payload: JSON.stringify({
      filterGroups: [{ filters: [{ propertyName: prop, operator: 'EQ', value: String(value) }] }],
      limit: 1,
    }),
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) return null;
  const json = JSON.parse(res.getContentText());
  return (json.results || [])[0] || null;
}

function buildContactPropsFromKintone(r) {
  const v = (f) => r[f]?.value || '';
  const namae = v('namae');
  const parts = namae.trim().split(/[\s\u3000]+/);
  const lastname = parts[0] || namae;
  const firstname = parts[1] || '';
  const props = {
    firstname, lastname,
    email: v('denshimeru'),
    phone: v('denwa') || v('keitaidenwa'),
    nbs_inquiry_number: v('toiawasebangou'),
    nbs_kintone_inquiry_id: v('$id'),
    nbs_customer_number: v('kokyakubangou'),
    nbs_salon_name: v('saronmei'),
    nbs_inquiry_date: v('toiawasebi'),
    nbs_inquiry_content: v('otoiawasenaiyou'),
    nbs_name_kana: v('hurigana'),
  };
  // enum マッピング
  const productMap = { '脱毛機': 'hair_removal', '痩身機': 'slimming', '水素': 'hydrogen', 'PLAGENE': 'plagene', '化粧品': 'cosmetics', '家庭用': 'home_use', '医療機器': 'medical' };
  const methodMap = { 'HP': 'hp', 'TEL': 'tel', 'LINE': 'line', '展示会': 'exhibition' };
  const sourceMap = { '問合せ': 'inquiry', '紹介': 'referral', '展示会': 'exhibition', '追加購入': 'repeat_purchase', 'DM': 'dm', 'SNS': 'sns', '情報誌': 'magazine', 'テレアポ': 'teleappo', 'LINE': 'line' };
  const typeMap = { '個人': 'personal', '法人': 'corporate' };
  if (productMap[v('toiawasesyurui')]) props.nbs_inquiry_product = productMap[v('toiawasesyurui')];
  if (methodMap[v('toiawasehouhou')]) props.nbs_inquiry_method = methodMap[v('toiawasehouhou')];
  if (sourceMap[v('kikkake')]) props.nbs_inquiry_source = sourceMap[v('kikkake')];
  if (typeMap[v('syurui')]) props.nbs_company_type = typeMap[v('syurui')];

  // Owner マッピング
  const ownerMap = {
    '西尾　和也': '164145859', '山本　幸': '164145856', '川村　麟太郎': '164145857',
    '江部　綾乃': '164145855', '久野　紘子': '164145853', '加藤　真央': '164145858',
    '佐藤　大介': '164145854',
  };
  const ownerName = v('eigyoutantousya');
  if (ownerMap[ownerName]) props.hubspot_owner_id = ownerMap[ownerName];

  // 空文字除外
  Object.keys(props).forEach(k => { if (props[k] === '') delete props[k]; });
  return props;
}

function buildDealPropsFromKintone(r) {
  const v = (f) => r[f]?.value || '';
  const dealNumber = v('syoudanbangou');
  const inquiryNumber = v('toiawasebangou');
  const customer = v('kaisyamei') || v('saronmei') || `#${v('kokyakubangou')}`;
  const purpose = v('mokuteki');
  let dealname = `商談 ${dealNumber} / ${customer}`;
  if (purpose) dealname += ` (${purpose})`;

  const bumon = v('bumon');
  const isAgent = bumon.indexOf('代理店') >= 0;
  const pipeline = isAgent ? 'default' : '2224114394';

  const mikomi = v('mikomi').replace('%', '').trim();
  const sutetasu = v('syoudansutetasu');
  const haiin = v('haiin');

  const stagesDirect = { 'default': '3563886294', '20': '3563886295', '30': '3563886296', '40': '3563886297', '60': '3563886298', '70': '3563886299', '80': '3563886300', '90': '3563886301', '100': '3563886302', 'lost': '3563886303' };
  const stagesAgent = { 'default': '3563696866', '20': '3563696867', '30': '3563696868', '40': '3563696869', '60': '3563886304', '70': '3563696870', '80': '3563886305', '90': '3563696871', '100': '3563886306', 'lost': 'closedlost' };
  const stages = isAgent ? stagesAgent : stagesDirect;

  let stage = stages.default;
  if (haiin || sutetasu.indexOf('失注') >= 0 || sutetasu.indexOf('Lost') >= 0) stage = stages.lost;
  else if (sutetasu.indexOf('即決') >= 0 || mikomi === '100') stage = stages['100'];
  else if (stages[mikomi]) stage = stages[mikomi];

  const props = {
    dealname, pipeline, dealstage: stage,
    nbs_deal_number: dealNumber,
    nbs_inquiry_number: inquiryNumber,
    nbs_kintone_deal_id: v('$id'),
    nbs_demo_date: v('demobi'),
    nbs_demo_summary: v('demogaiyou'),
    nbs_inquiry_trigger: v('kikkake'),
    nbs_prospect_concerns: v('kiduki_kadai'),
    nbs_decision_factors: v('juushishiteiruten'),
    nbs_proposed_solution: v('kaiketsusaku_jikkousaku'),
    nbs_next_follow_up: v('kongonooikake'),
    nbs_salon_concept: v('saronkonseputo'),
    nbs_hpb_sns: v('HPB_SNS'),
    nbs_lost_reason_detail: v('haiinsyousai'),
  };
  if (mikomi && ['20', '30', '40', '60', '70', '80', '90', '100'].indexOf(mikomi) >= 0) props.nbs_mikomi_pct = mikomi;
  const amount = parseFloat(v('yosokugoukeiti_zeinuki'));
  if (!isNaN(amount) && amount > 0) props.amount = amount;
  props.nbs_pipeline_type = isAgent ? 'agent' : 'direct';

  // Owner
  const ownerMap = {
    '西尾　和也': '164145859', '山本　幸': '164145856', '川村　麟太郎': '164145857',
    '江部　綾乃': '164145855', '久野　紘子': '164145853', '加藤　真央': '164145858',
    '佐藤　大介': '164145854',
  };
  const ownerName = v('eigyoutantousya');
  if (ownerMap[ownerName]) props.hubspot_owner_id = ownerMap[ownerName];

  Object.keys(props).forEach(k => { if (props[k] === '') delete props[k]; });
  return props;
}

// ============================================================================
// Phase 1.5 — Kintone 案件 (385) / 商品マスタ (377) / 研修内容 (381) → Sheet 直接書き出し
// (HubSpot Deal は商談ベース、Sheet は案件ベースで集計 — 「機械販売件数」を正しく出すため)
// ============================================================================

const KINTONE_APPS_PHASE15 = {
  CASES: 385,
  PRODUCTS: 377,
  TRAININGS: 381,
};

// 過去 2 期分のフィルタ基準日 (= HubSpot import と整合させる)
const PAST_2_PERIODS_FROM = '2024-06-01';

// $id 範囲分割で全件取得 (offset 10000 制限を回避)
// additionalFilter: 受注日や更新日でフィルタする追加 query 文字列 (例: '作成日時 >= "2024-06-01T00:00:00+0900"')
// 注意: $id は古いレコードから順に発番されるため、新しいデータでフィルタすると $id 小さい範囲は 0 件になる
//       → 連続 N 回 0 件で break する (= 古いデータゾーンを通過するため)
function fetchKintoneFullByIdRanges(app, maxId, chunk, additionalFilter) {
  const config = getConfig();
  const auth = getKintoneAuthHeader(app);
  if (!auth) {
    Logger.log(`❌ KINTONE_TOKEN_${app} も KINTONE_USER/PASS も Script Properties に未設定`);
    return [];
  }
  const all = [];
  let zeroStreak = 0;
  const ZERO_STREAK_BREAK = 10; // 連続 10 chunk 0 件で終了 (案件 30000/4000=8 chunks なので実質「全 chunk 試す」)
  for (let start = 0; start < maxId; start += chunk) {
    const end = start + chunk;
    const filterClause = additionalFilter ? ` and ${additionalFilter}` : '';
    const fullQuery = `$id >= ${start} and $id < ${end}${filterClause} order by $id asc`;
    let offset = 0;
    let chunkCount = 0;
    while (offset < 10000) {
      const url = `${config.KINTONE_BASE}/k/v1/records.json?app=${app}&query=${encodeURIComponent(fullQuery + ` limit 500 offset ${offset}`)}&totalCount=true`;
      const response = UrlFetchApp.fetch(url, {
        headers: auth,
        muteHttpExceptions: true,
      });
      const status = response.getResponseCode();
      if (status !== 200) {
        Logger.log(`Kintone app=${app} chunk=${start}-${end} status=${status}: ${response.getContentText().substring(0, 200)}`);
        break;
      }
      const json = JSON.parse(response.getContentText());
      const records = json.results || json.records || [];
      chunkCount += records.length;
      all.push(...records);
      if (records.length < 500) break;
      offset += 500;
      Utilities.sleep(300);
    }
    if (chunkCount === 0) {
      zeroStreak++;
      if (zeroStreak >= ZERO_STREAK_BREAK) {
        Logger.log(`  app=${app} 連続 ${ZERO_STREAK_BREAK} chunk 0 件 → 早期終了 (start=${start})`);
        break;
      }
    } else {
      zeroStreak = 0;
    }
  }
  Logger.log(`  app=${app} 全件取得: ${all.length} 件`);
  return all;
}

// USER_SELECT フィールドから営業担当者名を取り出す
function extractUserSelectName(field) {
  if (!field || !field.value || !Array.isArray(field.value) || field.value.length === 0) return '';
  return field.value[0].name || field.value[0].code || '';
}

// 全角スペース → 半角スペース
function normalizeOwnerName(name) {
  if (!name) return '';
  return String(name).replace(/　/g, ' ').trim();
}

// 案件レコード → SalesRecords (商品明細フラット形式・1 行 = 1 商品明細行)
function buildSalesRecordRows(cases) {
  const rows = [];
  cases.forEach(r => {
    const v = (f) => r[f]?.value || '';
    const caseNumber = v('ankenbangou');
    const inquiryNumber = v('toiawasebangou');
    const customerNumber = v('kokyakubangou');
    const department = v('bumon');
    const isDirect = String(department).indexOf('直販') >= 0 ? 1 : 0;
    const isCancelled = (r['kyanseruhuragu']?.value || []).indexOf('キャンセル') >= 0 ? 1 : 0;
    const orderDate = toIsoDate(v('jutyuubi'));
    const shipmentDate = toIsoDate(v('syukkabi'));
    const deliveryDate = toIsoDate(v('nouhinbi'));
    const trainingDate = toIsoDate(v('kensyuubi'));
    const cancelDate = toIsoDate(v('kyanserubi'));
    const machineType = v('ドロップダウン_3');
    const productType = v('ドロップダウン_4');
    const ownerName = normalizeOwnerName(extractUserSelectName(r['eigyoutantousya']));
    const purchaseEntry = v('kounyuunoiriguti');

    const meisai = r['meisai']?.value || [];
    if (meisai.length === 0) {
      rows.push([
        caseNumber, inquiryNumber, customerNumber, department, isDirect, isCancelled,
        orderDate, shipmentDate, deliveryDate, trainingDate, cancelDate,
        '', '', '', 0, 0, 0, 0, 10, 0,
        machineType, productType, ownerName, purchaseEntry, 0,
      ]);
      return;
    }

    meisai.forEach(line => {
      const lv = line.value || {};
      const productSearch = lv.hanbaisyouhinkensaku?.value || '';
      const productRecNo = lv.hanbaisyouhinRecNo?.value || '';
      const detail = lv.syousai?.value || '';
      const quantity = Number(lv.suuryou?.value || 0);
      const unitPrice = Number(lv.tanka?.value || 0);
      const amountExcl = Number(lv.kingaku?.value || 0);
      const amountIncl = Number(lv.zeikomikingaku?.value || 0);
      const taxRate = Number(lv.zeiritsu?.value || 0);
      const taxAmount = Number(lv.zeigaku?.value || 0);
      const isMainUnit = (String(detail).indexOf('本体一式') >= 0 || String(productSearch).indexOf('本体一式') >= 0) ? 1 : 0;

      rows.push([
        caseNumber, inquiryNumber, customerNumber, department, isDirect, isCancelled,
        orderDate, shipmentDate, deliveryDate, trainingDate, cancelDate,
        productSearch, detail, productRecNo, quantity, unitPrice, amountExcl, amountIncl, taxRate, taxAmount,
        machineType, productType, ownerName, purchaseEntry, isMainUnit,
      ]);
    });
  });
  return rows;
}

// 案件レコード → SalesByCase (1 案件 = 1 行集計)
function buildSalesByCaseRows(cases) {
  const rows = [];
  cases.forEach(r => {
    const v = (f) => r[f]?.value || '';
    const caseNumber = v('ankenbangou');
    const inquiryNumber = v('toiawasebangou');
    const customerNumber = v('kokyakubangou');
    const department = v('bumon');
    const isDirect = String(department).indexOf('直販') >= 0 ? 1 : 0;
    const isCancelled = (r['kyanseruhuragu']?.value || []).indexOf('キャンセル') >= 0 ? 1 : 0;
    const orderDate = toIsoDate(v('jutyuubi'));
    const orderMonth = orderDate ? orderDate.substring(0, 7) : '';
    const shipmentDate = toIsoDate(v('syukkabi'));
    const machineType = v('ドロップダウン_3');
    const productType = v('ドロップダウン_4');
    const ownerName = normalizeOwnerName(extractUserSelectName(r['eigyoutantousya']));
    const purchaseEntry = v('kounyuunoiriguti');

    const meisai = r['meisai']?.value || [];
    let totalExcl = 0, totalIncl = 0, mainUnitCount = 0;
    meisai.forEach(line => {
      const lv = line.value || {};
      totalExcl += Number(lv.kingaku?.value || 0);
      totalIncl += Number(lv.zeikomikingaku?.value || 0);
      const detail = String(lv.syousai?.value || '');
      const productSearch = String(lv.hanbaisyouhinkensaku?.value || '');
      if (detail.indexOf('本体一式') >= 0 || productSearch.indexOf('本体一式') >= 0) mainUnitCount++;
    });

    const isMachineSale = (mainUnitCount > 0 && isDirect === 1 && isCancelled === 0) ? 1 : 0;

    rows.push([
      caseNumber, inquiryNumber, customerNumber, department, isDirect, isCancelled,
      orderDate, orderMonth, shipmentDate, totalExcl, totalIncl, mainUnitCount, isMachineSale,
      machineType, productType, ownerName, purchaseEntry,
    ]);
  });
  return rows;
}

// 商品マスタ → 行
function buildProductRows(products) {
  const rows = [];
  products.forEach(r => {
    const v = (f) => r[f]?.value || '';
    const code = v('syouhinkodo');
    const name = v('hinmei');
    const codeName = v('syouhinkodo_hinmei');
    const price = Number(v('tanka') || 0);
    const taxRate = Number(v('zeiritsu') || 0);
    const unit = v('tani');
    const makerCode = v('mekakodo');
    const recordId = v('hanabisyouhinid') || v('$id');
    const isMainUnit = String(name).indexOf('本体一式') >= 0 ? 1 : 0;
    rows.push([recordId, code, name, codeName, price, unit, taxRate, makerCode, isMainUnit]);
  });
  return rows;
}

// 研修内容 → 行
function buildTrainingRows(trainings) {
  const rows = [];
  trainings.forEach(r => {
    const v = (f) => r[f]?.value || '';
    const trainingNumber = v('kensyuubangou');
    const caseNumber = v('ankenbangou');
    const customerNumber = v('kokyakubangou');
    const date = toIsoDate(v('hiduke'));
    const purpose = v('mokuteki');
    const status = v('kensyuusutetasu');
    const area = v('eria');
    const cityArea = v('eria_0');
    const salonName = v('saronmei_tantousyamei');
    const trainerMain = normalizeOwnerName(extractUserSelectName(r['kensyuutantousya']));
    const trainerEducator = normalizeOwnerName(extractUserSelectName(r['kensyuutantousya_0']));
    const ownerName = normalizeOwnerName(extractUserSelectName(r['eigyoutantousya']));
    rows.push([trainingNumber, caseNumber, customerNumber, date, purpose, status, area, cityArea, salonName, trainerMain, trainerEducator, ownerName]);
  });
  return rows;
}

// 汎用 Sheet 書き出し: clear + setNumberFormat (TEXT 型固定) + setValues
function writeRowsToSheet(sheetName, headers, rows, dateColumns) {
  const config = getConfig();
  const ss = SpreadsheetApp.openById(config.SHEET_ID);
  let sheet = ss.getSheetByName(sheetName);
  if (sheet) sheet.clear();
  else sheet = ss.insertSheet(sheetName);

  // 日付列を TEXT 型に固定 (Sheet による Date 自動変換を防ぐ)
  if (dateColumns && Array.isArray(dateColumns)) {
    dateColumns.forEach(col => {
      sheet.getRange(`${col}:${col}`).setNumberFormat('@');
    });
  }
  SpreadsheetApp.flush();

  sheet.appendRow(headers);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }
  Logger.log(`  ✅ ${sheetName}: ${rows.length} rows written`);
}

// Phase 1.5 メインエントリ — 毎日全件再 fetch + Sheet 全上書き
function syncKintoneToSalesSheets() {
  Logger.log('=== Phase 1.5: Kintone 案件・商品・研修 → Sheet 書き出し ===');

  // 1. 案件アプリ (385) — 過去 2 期分のみ (作成日時 >= 2024-06-01)
  // 注意: jutyuubi (受注日) は空欄のレコードが多いため、作成日時で代用
  // HubSpot の import 範囲と整合させ、宙ぶらりんデータを排除
  try {
    const filter = `作成日時 >= "${PAST_2_PERIODS_FROM}T00:00:00+0900"`;
    const cases = fetchKintoneFullByIdRanges(KINTONE_APPS_PHASE15.CASES, 30000, 4000, filter);
    Logger.log(`  Kintone 案件 (作成日時 >= ${PAST_2_PERIODS_FROM}): ${cases.length} 件取得`);
    const salesRecordHeaders = [
      'case_number', 'inquiry_number', 'customer_number', 'department', 'is_direct', 'is_cancelled',
      'order_date', 'shipment_date', 'delivery_date', 'training_date', 'cancel_date',
      'product_search', 'detail', 'product_recno', 'quantity', 'unit_price',
      'amount_excl', 'amount_incl', 'tax_rate', 'tax_amount',
      'machine_type', 'product_type', 'sales_owner', 'purchase_entry', 'is_main_unit',
    ];
    const salesByCaseHeaders = [
      'case_number', 'inquiry_number', 'customer_number', 'department', 'is_direct', 'is_cancelled',
      'order_date', 'order_month', 'shipment_date', 'total_amount_excl', 'total_amount_incl',
      'main_unit_count', 'is_machine_sale', 'machine_type', 'product_type', 'sales_owner', 'purchase_entry',
    ];
    const salesRecordRows = buildSalesRecordRows(cases);
    const salesByCaseRows = buildSalesByCaseRows(cases);
    writeRowsToSheet('LookerStudio_SalesRecords', salesRecordHeaders, salesRecordRows, ['G', 'H', 'I', 'J', 'K']);
    writeRowsToSheet('LookerStudio_SalesByCase', salesByCaseHeaders, salesByCaseRows, ['G', 'H', 'I']);
  } catch (e) {
    Logger.log(`❌ 案件 sync failed: ${e.message}`);
  }

  // 2. 商品マスタ (377) — マスタなので全件 (フィルタなし)
  try {
    const products = fetchKintoneFullByIdRanges(KINTONE_APPS_PHASE15.PRODUCTS, 5000, 4000, null);
    Logger.log(`  Kintone 商品マスタ: ${products.length} 件取得`);
    const productHeaders = ['record_id', 'product_code', 'product_name', 'code_name', 'unit_price', 'unit', 'tax_rate', 'maker_code', 'is_main_unit'];
    const productRows = buildProductRows(products);
    writeRowsToSheet('LookerStudio_Products', productHeaders, productRows, []);
  } catch (e) {
    Logger.log(`❌ 商品マスタ sync failed: ${e.message}`);
  }

  // 3. 研修内容 (381) — 過去 2 期分のみ (作成日時 >= 2024-06-01)
  // hiduke (日付) も空欄の可能性があるので作成日時で代用
  try {
    const filter = `作成日時 >= "${PAST_2_PERIODS_FROM}T00:00:00+0900"`;
    const trainings = fetchKintoneFullByIdRanges(KINTONE_APPS_PHASE15.TRAININGS, 10000, 4000, filter);
    Logger.log(`  Kintone 研修内容 (作成日時 >= ${PAST_2_PERIODS_FROM}): ${trainings.length} 件取得`);
    const trainingHeaders = ['training_number', 'case_number', 'customer_number', 'date', 'purpose', 'status', 'area', 'city_area', 'salon_name', 'trainer_main', 'trainer_educator', 'sales_owner'];
    const trainingRows = buildTrainingRows(trainings);
    writeRowsToSheet('LookerStudio_Trainings', trainingHeaders, trainingRows, ['D']);
  } catch (e) {
    Logger.log(`❌ 研修内容 sync failed: ${e.message}`);
  }

  Logger.log('=== Phase 1.5 完了 ===');
}

// 動作確認用 (案件アプリ・過去 2 期分のみ・作成日時ベース)
function testFetchCases() {
  const filter = `作成日時 >= "${PAST_2_PERIODS_FROM}T00:00:00+0900"`;
  const cases = fetchKintoneFullByIdRanges(KINTONE_APPS_PHASE15.CASES, 30000, 4000, filter);
  Logger.log(`案件 (作成日時 >= ${PAST_2_PERIODS_FROM}): ${cases.length} 件`);
  if (cases.length > 0) {
    const sample = cases[0];
    Logger.log(`サンプル[0] case_number: ${sample.ankenbangou?.value}, 顧客: ${sample.kokyakubangou?.value}, 作成日時: ${sample['作成日時']?.value}, 受注日: ${sample.jutyuubi?.value}, 部門: ${sample.bumon?.value}`);
    const meisai = sample['meisai']?.value || [];
    Logger.log(`meisai 行数: ${meisai.length}`);
    meisai.slice(0, 3).forEach((m, i) => {
      Logger.log(`  meisai[${i}]: 商品=${m.value.syousai?.value}, 数量=${m.value.suuryou?.value}, 単価=${m.value.tanka?.value}, 金額=${m.value.kingaku?.value}`);
    });
  }
}

function testFetchProducts() {
  const products = fetchKintoneFullByIdRanges(KINTONE_APPS_PHASE15.PRODUCTS, 5000, 4000, null);
  Logger.log(`商品マスタ: ${products.length} 件`);
  products.slice(0, 5).forEach((p, i) => {
    Logger.log(`[${i}] code=${p.syouhinkodo?.value}, name=${p.hinmei?.value}, price=${p.tanka?.value}`);
  });
}

// ============================================================================
// マスター: 全工程 (毎朝 1 回実行)
// ============================================================================

function syncAllAndAnalyze() {
  Logger.log('🚀 syncAllAndAnalyze started');
  try { syncKintoneToHubSpot(); } catch (e) { Logger.log(`❌ Kintone sync: ${e.message}`); }
  try { syncAllToSheets(); } catch (e) { Logger.log(`❌ HubSpot→Sheets: ${e.message}`); }
  try { syncKintoneToSalesSheets(); } catch (e) { Logger.log(`❌ Sales sheets sync: ${e.message}`); }
  try { recordStageSnapshot(); } catch (e) { Logger.log(`❌ Stage snapshot: ${e.message}`); }
  try { recordDealCurrentState(); } catch (e) { Logger.log(`❌ Current state: ${e.message}`); }
  try { recordCohortMilestones(); } catch (e) { Logger.log(`❌ Cohort milestones: ${e.message}`); }
  Logger.log('✅ syncAllAndAnalyze completed');
}

// ============================================================================
// Trigger 設定 (1 回だけ手動実行)
// ============================================================================

function setupDailyTrigger() {
  // 既存 Trigger 全削除 (syncAllToSheets / syncAllAndAnalyze 両方)
  ScriptApp.getProjectTriggers().forEach(t => {
    const fn = t.getHandlerFunction();
    if (fn === 'syncAllToSheets' || fn === 'syncAllAndAnalyze') {
      ScriptApp.deleteTrigger(t);
    }
  });

  // 毎日 7:00 (JST) に syncAllAndAnalyze 実行 (Kintone→HubSpot→Sheets→Snapshot→Cohort 全工程)
  ScriptApp.newTrigger('syncAllAndAnalyze')
    .timeBased()
    .everyDays(1)
    .atHour(7)
    .create();

  Logger.log('✅ Daily trigger set: syncAllAndAnalyze at 7:00 JST (Kintone→HubSpot→Sheets→Snapshot→Cohort)');
}

// ============================================================================
// 動作確認 (手動実行用)
// ============================================================================

function listOwners() {
  const config = getConfig();
  const url = `${config.HUBSPOT_BASE}/crm/v3/owners?limit=100&archived=false`;
  const response = UrlFetchApp.fetch(url, {
    headers: { 'Authorization': `Bearer ${config.HUBSPOT_TOKEN}` },
    muteHttpExceptions: true,
  });
  const status = response.getResponseCode();
  if (status !== 200) {
    Logger.log(`❌ Failed to list owners: status=${status}`);
    Logger.log(response.getContentText().substring(0, 500));
    return;
  }
  const json = JSON.parse(response.getContentText());
  const owners = json.results || [];
  Logger.log(`==== HubSpot Owners (NBS Starter): ${owners.length} owner(s) ====`);
  owners.forEach(o => {
    const email = o.email || '(no email)';
    const name = `${o.firstName || ''} ${o.lastName || ''}`.trim() || '(no name)';
    const archived = o.archived ? '[ARCHIVED]' : '';
    const userId = o.userId ? `userId=${o.userId}` : '(no userId)';
    Logger.log(`  ID=${o.id} ${userId} | ${email} | ${name} ${archived}`);
  });
  Logger.log(`---- copy-pasteable mapping JSON ----`);
  const map = {};
  owners.forEach(o => {
    if (o.email) map[o.email] = o.id;
  });
  Logger.log(JSON.stringify(map, null, 2));
}

function testHubSpotConnection() {
  const config = getConfig();
  Logger.log(`Token configured: ${config.HUBSPOT_TOKEN ? 'YES (' + config.HUBSPOT_TOKEN.substring(0, 12) + '...)' : 'NO'}`);
  Logger.log(`Sheet ID: ${config.SHEET_ID}`);

  const url = `${config.HUBSPOT_BASE}/crm/v3/objects/contacts?limit=1`;
  const response = UrlFetchApp.fetch(url, {
    headers: { 'Authorization': `Bearer ${config.HUBSPOT_TOKEN}` },
    muteHttpExceptions: true,
  });
  const status = response.getResponseCode();
  Logger.log(`HubSpot test: status=${status}`);
  if (status === 200) {
    const json = JSON.parse(response.getContentText());
    Logger.log(`✅ Connected. ${json.results.length} contact(s) returned.`);
  } else {
    Logger.log(`❌ Failed: ${response.getContentText().substring(0, 300)}`);
  }
}