// BizFetch の Google スプレッドシートのアドオン（設計 docs/research/2026-10-08_ui_rebuild_design.md 11・11.2 節）。
// サイドバーが段ごとに呼ぶ（評価を始める → 次の段へ進む）。照合の決まり（行の識別子、対象 ID の隠し列、評価の番号での新旧、結果不明の照合）はここに置く。
var BIZFETCH_API_BASE_URL = 'https://app.bizfetch.net/api/v1';
var SETTINGS_KEY = 'bizfetch.settings.v1';
var API_KEY_PROPERTY = 'bizfetch.api_key';
var CONTINUATION_KEY = 'bizfetch.continuation.v1';
var HEADER_ROW = 1;

// 隠し列と書き込みの状態の値は、裏での書き込み（worker/src/sinks/sheets.ts）と同じもの。
var SYSTEM_HEADERS = {
  rowId: 'BizFetch行ID',
  objectId: 'BizFetch対象ID',
  status: '書き込みの状態',
  judgedAt: '評価の日時',
  reflectedAt: '書き込みの日時',
  runSeq: '評価の番号',
};

// common.md 7 節の共通項目と会社の取得データ。語は CSV の見出し（worker/src/api/condition-csv.ts の csvLabels）と同じ。
// 評価の日時・書き込みの日時・評価の番号・書き込みの状態は、決まった列（SYSTEM_HEADERS）に必ず書く。
var OUTPUT_GROUPS = [
  { title: '判定の結果', fields: [
    { key: 'identifier', label: '番号' },
    { key: 'display_name', label: '名前' },
    { key: 'overall', label: '総合の適合度' },
    { key: 'overall_reason', label: '判定できなかった理由' },
    { key: 'judgment_items', label: '判定項目ごとの結果' },
    { key: 'filters', label: '絞り込みの結果' },
    { key: 'scope', label: '判定できた項目' },
    { key: 'profile_version', label: '条件の版' },
    { key: 'source_updated_at', label: '公開情報の時点' },
    { key: 'detail_url', label: '内訳の URL' },
    { key: 'attributions', label: '出典' },
    { key: 'marks', label: '候補の選択' },
  ] },
  { title: '会社の公開情報の項目', fields: [
    { key: 'corporate_number', label: '法人番号' },
    { key: 'company_name', label: '会社名' },
    { key: 'corporate_kind', label: '法人の種類' },
    { key: 'head_office', label: '所在地' },
    { key: 'established_at', label: '設立年月日' },
    { key: 'years_since_establishment', label: '設立からの年数' },
    { key: 'industry', label: '業種' },
    { key: 'employees', label: '従業員数（人）' },
    { key: 'capital', label: '資本金（円）' },
    { key: 'office_count', label: '事業所数' },
    { key: 'invoice_registration', label: 'インボイス登録' },
    { key: 'latest_change', label: '直近の変更' },
    { key: 'closed', label: '閉鎖' },
  ] },
];
var OUTPUT_FIELDS = OUTPUT_GROUPS.reduce(function (all, group) { return all.concat(group.fields); }, []);
var PURPOSES = { sales: '営業対象を探す', partner_check: '取引先を確認する', company_check: '企業情報を確認する', subsidy: '補助金を探す', tender: '入札案件を探す', disclosure_ip: '開示・知財情報を探す' };
var COMPANY_PURPOSES = ['sales', 'partner_check', 'company_check'];
var INVALID_NUMBER = '法人番号は 13 桁の数字です。番号を直してから、もう一度評価してください。';
var EVALUATION_FAILED = '評価を終えられませんでした。時間をおいて、もう一度評価してください。';
// 反映を受け付けなかった理由（API の reason）の文。kintone のプラグインと同じ（kintone-plugin/src/workflow.js）。
var REASONS = {
  old_run: 'より新しい評価を書き込み済みです。',
  already_succeeded: '同じ評価を書き込み済みです。',
  already_created: '書き込み済みです。',
  already_requested: '反映を頼んだまま書き込めていません。「反映を待っている分を反映」で書き込みます。',
  reflection_ineligible: '評価の途中で条件が保存されたか、毎日のチェックが止まったため、この評価は書き込みません。もう一度「BizFetch で評価」を押してください。',
  sink_not_active: 'この連携先は、条件の毎日のチェックに結び付いていません。BizFetch の条件の画面の「書き出す」で、この連携先の「毎日のチェックで反映する」を入れてください。',
  object_not_in_run: 'この評価に含まれない会社です。',
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('サイドバーを開く', 'showSidebar')
    .addItem('書き出す項目を選ぶ', 'showOutputDialog')
    .addToUi();
}

function onInstall() {
  onOpen();
}

function showSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('BizFetch'),
  );
}

function showOutputDialog() {
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutputFromFile('Dialog').setWidth(600).setHeight(640),
    '書き出す項目を選ぶ',
  );
}

function getSidebarState() {
  var settings = loadSettings_();
  var continuation = loadContinuation_();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = Math.max(sheet.getLastColumn(), 1);
  var system = [SYSTEM_HEADERS.rowId, SYSTEM_HEADERS.objectId, settings.statusColumn, settings.judgedAtColumn, settings.reflectedAtColumn, settings.runSeqColumn];
  var headers = sheet.getRange(HEADER_ROW, 1, 1, lastColumn).getDisplayValues()[0]
    .map(cleanString_).filter(function (header) { return header && system.indexOf(header) === -1; });
  return {
    apiKeyConfigured: Boolean(getApiKey_()),
    continuation: continuation ? { runId: continuation.runId || null, objectSetId: continuation.objectSetId, stage: continuation.stage, startedAt: continuation.startedAt } : null,
    outputGroups: OUTPUT_GROUPS,
    outputFields: OUTPUT_FIELDS,
    headers: headers,
    settings: settings,
  };
}

function saveSettings(input) {
  input = input || {};
  var previous = loadSettings_();
  var settings = {
    sinkId: cleanString_(input.sinkId || previous.sinkId),
    profileId: cleanString_(input.profileId || previous.profileId),
    template: cleanString_(input.template || previous.template),
    profileName: cleanString_(input.profileName || previous.profileName),
    nameColumn: cleanString_(input.nameColumn),
    corporateNumberColumn: cleanString_(input.corporateNumberColumn),
    userDataColumns: normalizeUserDataColumns_(input.userDataColumns),
    outputMappings: normalizeOutputMappings_(input.outputMappings),
    statusColumn: cleanString_(input.statusColumn) || SYSTEM_HEADERS.status,
    judgedAtColumn: cleanString_(input.judgedAtColumn) || SYSTEM_HEADERS.judgedAt,
    reflectedAtColumn: cleanString_(input.reflectedAtColumn) || SYSTEM_HEADERS.reflectedAt,
    runSeqColumn: cleanString_(input.runSeqColumn) || SYSTEM_HEADERS.runSeq,
  };
  if (!settings.nameColumn && !settings.corporateNumberColumn) {
    throw new Error('会社名の列か法人番号の列を選んでください。');
  }
  if (previous.sinkId && previous.profileId && (previous.sinkId !== settings.sinkId || previous.profileId !== settings.profileId)) {
    apiRequest_('DELETE', '/sinks/' + encodeURIComponent(previous.sinkId) + '/records?profile_id=' + encodeURIComponent(previous.profileId));
    clearContinuation_();
  }
  PropertiesService.getUserProperties().setProperty(SETTINGS_KEY, JSON.stringify(settings));
  if (cleanString_(input.apiKey)) {
    PropertiesService.getUserProperties().setProperty(API_KEY_PROPERTY, cleanString_(input.apiKey));
  }
  return getSidebarState();
}

/** 「確かめる」: 入れたキー（無ければ保存済みのキー）で条件と連携先を確かめる。成り立たない欄は errors に入れて返す。 */
function verifyConnection(input) {
  input = input || {};
  var apiKey = cleanString_(input.apiKey) || getApiKey_();
  var profileId = cleanString_(input.profileId);
  var sinkId = cleanString_(input.sinkId);
  var errors = {};
  if (!apiKey) errors.apiKey = 'API キーを入れてください。';
  if (!profileId) errors.profileId = '条件 ID を入れてください。';
  if (Object.keys(errors).length) return { ok: false, errors: errors };
  var profile;
  try {
    profile = apiRequestWithKey_('GET', '/profiles/' + encodeURIComponent(profileId), undefined, apiKey).body.profile;
  } catch (error) {
    var message = error && error.message ? error.message : String(error);
    if (/見つかりません|not.?found/i.test(message)) return { ok: false, errors: { profileId: 'この条件 ID の条件が見つかりません。BizFetch の条件の画面のアドレスを確かめてください。' } };
    return { ok: false, errors: { apiKey: message } };
  }
  var purpose = PURPOSES[profile.template_key] || profile.template_key;
  if (COMPANY_PURPOSES.indexOf(profile.template_key) === -1) {
    return { ok: false, errors: { profileId: 'この条件の目的（' + purpose + '）は、スプレッドシートの行を評価できません。営業対象を探す・取引先を確認する・企業情報を確認するの条件を選んでください。' } };
  }
  var sinkText = '連携先は保存のときに作ります。';
  if (sinkId) {
    var sinks = apiRequestWithKey_('GET', '/sinks', undefined, apiKey).body.sinks || [];
    var sink = sinks.filter(function (item) { return item.id === sinkId; })[0];
    if (!sink) return { ok: false, errors: { sinkId: 'この連携先 ID の連携先が見つかりません。空にすると新しく作ります。' } };
    if (sink.kind !== 'sheets' || !sink.config || sink.config.sheet_id !== currentSheetId_()) {
      return { ok: false, errors: { sinkId: 'この連携先は、このシートのものではありません。空にすると、このシートの連携先を新しく作ります。' } };
    }
    sinkText = '連携先はこのシートのものです。';
  }
  return { ok: true, profile: { id: profileId, name: profile.name, template: profile.template_key, purpose: purpose }, text: 'つながりました。条件「' + profile.name + '」（' + purpose + '）。' + sinkText };
}

/** サイドバーの「保存」: 確かめてから設定を保存し、連携先が無ければこのシートの連携先を作る。 */
function saveAndConnect(input) {
  input = input || {};
  var errors = {};
  if (!cleanString_(input.nameColumn) && !cleanString_(input.corporateNumberColumn)) errors.columns = '会社名の列か法人番号の列を選んでください。';
  var checked = verifyConnection(input);
  if (!checked.ok) Object.keys(checked.errors).forEach(function (key) { errors[key] = checked.errors[key]; });
  if (Object.keys(errors).length) return { ok: false, errors: errors };
  var previous = loadSettings_();
  var settings = {
    sinkId: cleanString_(input.sinkId),
    profileId: checked.profile.id,
    template: checked.profile.template,
    profileName: checked.profile.name,
    nameColumn: cleanString_(input.nameColumn),
    corporateNumberColumn: cleanString_(input.corporateNumberColumn),
    userDataColumns: normalizeUserDataColumns_((input.userColumns || []).map(function (column) { return { column: column, factName: column }; })),
    outputMappings: previous.outputMappings,
    statusColumn: previous.statusColumn,
    judgedAtColumn: previous.judgedAtColumn,
    reflectedAtColumn: previous.reflectedAtColumn,
    runSeqColumn: previous.runSeqColumn,
  };
  if (previous.sinkId && previous.profileId && (previous.sinkId !== settings.sinkId || previous.profileId !== settings.profileId)) {
    apiRequest_('DELETE', '/sinks/' + encodeURIComponent(previous.sinkId) + '/records?profile_id=' + encodeURIComponent(previous.profileId));
    clearContinuation_();
  }
  PropertiesService.getUserProperties().setProperty(SETTINGS_KEY, JSON.stringify(settings));
  if (cleanString_(input.apiKey)) PropertiesService.getUserProperties().setProperty(API_KEY_PROPERTY, cleanString_(input.apiKey));
  if (!settings.sinkId) createSink();
  return { ok: true, text: checked.text, state: getSidebarState() };
}

/** 書き出す項目のダイアログの「保存」。決まった 4 つの列と、項目ごとの列の見出し。 */
function saveOutputMappings(input) {
  input = input || {};
  var settings = loadSettings_();
  var errors = {};
  var given = Array.isArray(input.outputMappings) ? input.outputMappings : [];
  given.forEach(function (mapping) { if (!cleanString_(mapping.column)) errors[cleanString_(mapping.field)] = '列の見出しを入れてください。'; });
  var mappings = normalizeOutputMappings_(given.filter(function (mapping) { return cleanString_(mapping.column); }));
  var system = {
    statusColumn: cleanString_(input.statusColumn), judgedAtColumn: cleanString_(input.judgedAtColumn),
    reflectedAtColumn: cleanString_(input.reflectedAtColumn), runSeqColumn: cleanString_(input.runSeqColumn),
  };
  Object.keys(system).forEach(function (key) { if (!system[key]) errors[key] = '列の見出しを入れてください。'; });
  var used = mappings.map(function (mapping) { return mapping.column; }).concat(Object.keys(system).map(function (key) { return system[key]; }))
    .concat([settings.nameColumn, settings.corporateNumberColumn, SYSTEM_HEADERS.rowId, SYSTEM_HEADERS.objectId]).filter(Boolean);
  var twice = used.filter(function (column, index) { return used.indexOf(column) !== index; });
  mappings.forEach(function (mapping) { if (twice.indexOf(mapping.column) !== -1) errors[mapping.field] = '同じ見出しの列に 2 つの項目は書けません。'; });
  Object.keys(system).forEach(function (key) { if (system[key] && twice.indexOf(system[key]) !== -1) errors[key] = '同じ見出しの列に 2 つの項目は書けません。'; });
  if (Object.keys(errors).length) return { ok: false, errors: errors };
  settings.outputMappings = mappings;
  Object.keys(system).forEach(function (key) { settings[key] = system[key]; });
  PropertiesService.getUserProperties().setProperty(SETTINGS_KEY, JSON.stringify(settings));
  return { ok: true, state: getSidebarState() };
}

function clearApiKey() {
  PropertiesService.getUserProperties().deleteProperty(API_KEY_PROPERTY);
  return getSidebarState();
}

function createSink() {
  var settings = requireSettings_({ sink: false, profile: false });
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var columns = ensureColumns_(sheet, settings);
  var fieldMapping = {};
  settings.outputMappings.forEach(function (mapping) { fieldMapping[mapping.field] = mapping.column; });
  fieldMapping.judged_at = settings.judgedAtColumn;
  fieldMapping.reflected_at = settings.reflectedAtColumn;
  fieldMapping.run_seq = settings.runSeqColumn;
  fieldMapping.reflection_status = settings.statusColumn;
  var writeFields = settings.outputMappings.map(function (mapping) { return mapping.field; });
  ['judged_at', 'reflected_at', 'run_seq', 'reflection_status'].forEach(function (field) {
    if (writeFields.indexOf(field) === -1) writeFields.push(field);
  });
  var response = apiRequest_('POST', '/sinks', {
    kind: 'sheets',
    config: {
      sheet_id: currentSheetId_(),
      row_id_column: SYSTEM_HEADERS.rowId,
      status_column: settings.statusColumn,
      write_fields: writeFields,
      field_mapping: fieldMapping,
      background: false,
    },
  }).body;
  if (!response.sink || !response.sink.id) {
    throw new Error('連携先の作成応答に sink.id がありません。');
  }
  settings.sinkId = response.sink.id;
  PropertiesService.getUserProperties().setProperty(SETTINGS_KEY, JSON.stringify(settings));
  return getSidebarState();
}

/** 選んだ範囲を、評価できる200行ずつ送る。続きには行番号だけを置く。 */
function evaluateSelection() {
  var existing = loadContinuation_();
  if (existing && existing.stage !== 'identification') throw new Error('前の評価の続きが残っています。「評価を続ける」を押して終えてから、もう一度押してください。');
  var settings = requireSettings_({ sink: true, profile: true });
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  if (!range) throw new Error('評価する行を選んでください。');
  var from = Math.max(range.getRow(), HEADER_ROW + 1), to = Math.min(range.getLastRow(), sheet.getLastRow());
  if (to < from) throw new Error('見出しの行のほかの行を選んでください。');
  var state = { mode: 'records', sheetId: sheet.getSheetId(), stage: 'identification', startedAt: new Date(nowMs_()).toISOString(), from: from, to: to, segFrom: from, segTo: from - 1, written: 0, failed: [], failedMore: 0, skippedRows: { empty: [], invalid: [], more: 0 }, total: to - from + 1 };
  return beginSegment_(state, settings, sheet);
}

function segmentItems_(state, settings, sheet, next) {
  var columns = ensureColumns_(sheet, settings), items = [], skipped = { empty: [], invalid: [] };
  var end = next ? state.to : state.segTo;
  for (var row = state.segFrom; row <= end; row += 1) {
    var name = settings.nameColumn ? cellDisplay_(sheet, row, columns.byHeader[settings.nameColumn]) : '';
    var number = settings.corporateNumberColumn ? corporateNumber_(cellDisplay_(sheet, row, columns.byHeader[settings.corporateNumberColumn])) : '';
    if (!name && !number) skipped.empty.push(row);
    else if (number && !/^\d{13}$/.test(number)) skipped.invalid.push(row);
    else {
      var item = { input_record_ref: ensureRowId_(sheet, row, columns.rowId), user_facts: {} };
      if (number) item.corporate_number = number; else item.name = name;
      settings.userDataColumns.forEach(function (mapping) { item.user_facts[mapping.factName] = cellDisplay_(sheet, row, columns.byHeader[mapping.column]); });
      items.push(item);
    }
    if (next) state.segTo = row;
    if (next && items.length === 200) break;
  }
  if (next) {
    state.skippedRows.empty = state.skippedRows.empty.concat(skipped.empty);
    state.skippedRows.invalid = state.skippedRows.invalid.concat(skipped.invalid);
    var capped = capSkipped_(state.skippedRows);
    capped.more += state.skippedRows.more || 0;
    state.skippedRows = capped;
  }
  return items;
}

function putSegment_(state, settings, sheet, items) {
  return apiRequest_('PUT', '/sinks/' + encodeURIComponent(settings.sinkId) + '/records', { profile_id: settings.profileId, items: items || segmentItems_(state, settings, sheet, false) }).body;
}

function beginSegment_(state, settings, sheet) {
  var items = segmentItems_(state, settings, sheet, true);
  if (!items.length) return completedRecords_(state);
  var set = putSegment_(state, settings, sheet, items);
  state.objectSetId = set.object_set.id;
  state.activationId = set.activation.id;
  state.stage = 'identification';
  saveContinuation_(state);
  return advance_(state, set);
}

/** サイドバーの3秒ごとの呼び出しで、いまの段を進める。 */
function continueEvaluation() {
  var state = loadContinuation_();
  return state ? advance_(state, null) : { status: 'idle' };
}

function advance_(state, created) {
  var settings = requireSettings_({ sink: true, profile: true }), sheet = sheetById_(state.sheetId);
  if (!sheet) { clearContinuation_(); throw new Error('評価を始めたシートが見つかりません。シートを消したか名前を変えたときは、もう一度評価してください。'); }
  if (state.stage === 'identification') {
    if (state.segTo < state.segFrom) return beginSegment_(state, settings, sheet);
    var set = created || putSegment_(state, settings, sheet);
    var counts = unresolvedCounts_(set);
    if (counts) return { status: 'unresolved', objectSetId: state.objectSetId, counts: counts, link: appOrigin_() + '/object-sets/' + encodeURIComponent(state.objectSetId) };
    var objectIds = (set.items || []).map(function (item) { return item.object_id; }).filter(function (id, index, ids) { return ids.indexOf(id) === index; });
    if (!objectIds.length) return finish_(state, settings, { id: state.runId }, []);
    var started = apiRequest_('POST', '/runs', { profile_id: settings.profileId, object_set_id: state.objectSetId, activation_id: state.activationId, purpose: 'visible_first', object_ids: objectIds });
    var run = started.body.run;
    if (!run || !run.id) throw new Error('評価を始められませんでした。');
    state.stage = 'run'; state.runId = run.id; state.runSeq = run.seq;
    saveContinuation_(state);
    if (started.status === 200 && Array.isArray(started.body.judgments)) return finish_(state, settings, run, started.body.judgments);
    return { status: 'evaluating', runId: run.id, done: 0, total: state.total };
  }
  var progress = apiRequest_('GET', '/runs/' + encodeURIComponent(state.runId)).body;
  var status = progress.status || (progress.run && progress.run.status);
  if (status === 'done' || status === 'completed' || status === 'succeeded') return finish_(state, settings, progress.run || { id: state.runId, seq: state.runSeq }, fetchAllPages_('/runs/' + encodeURIComponent(state.runId) + '/judgments', 'judgments'));
  if (status === 'failed' || status === 'gave_up') { clearContinuation_(); throw new Error('評価を終えられませんでした。BizFetch の条件の画面で状態を確かめてください。'); }
  return { status: 'evaluating', runId: state.runId, done: Number(progress.done) || 0, total: Number(progress.total) || state.total };
}

function finish_(state, settings, run, judgments) {
  var sheet = sheetById_(state.sheetId), columns = ensureColumns_(sheet, settings);
  var reread = segmentItems_(state, settings, sheet, false);
  var items = reread.length ? putSegment_(state, settings, sheet, reread).items : [];
  var outcome = applyJudgments_(sheet, settings, columns, run, judgments, items);
  state.written += outcome.written;
  state.background = (state.background || 0) + (outcome.background || 0);
  outcome.failed.forEach(function (failure) {
    if (state.failed.length < 100) state.failed.push([Number(String(failure.ref).split(' ')[0]) || state.segFrom, failure.code || 'unknown']);
    else state.failedMore += 1;
  });
  if (state.segTo >= state.to) return completedRecords_(state);
  state.segFrom = state.segTo + 1; state.segTo = state.segFrom - 1; state.stage = 'identification';
  delete state.runId; delete state.runSeq;
  saveContinuation_(state);
  return { status: 'evaluating', done: state.segFrom - state.from, total: state.total };
}

function completedRecords_(state) {
  clearContinuation_();
  var failed = skippedFailed_(state.skippedRows).concat(state.failed.map(function (entry) { return { ref: entry[0] + ' 行目', reason: entry[1] === 'evaluation_failed' ? EVALUATION_FAILED : entry[1] === 'record_missing' ? '評価した行が見つかりません。' : entry[1] === 'mismatch' ? '行の会社名・法人番号が、評価した会社と違います。行を直してから、もう一度評価してください。' : entry[1] === 'unknown' ? '書き込めたか確かめられませんでした。「反映を待っている分を反映」で確かめ直します。' : reasonText_(entry[1]) }; }));
  if (state.failedMore) failed.push({ ref: 'ほか ' + state.failedMore + ' 行', reason: '書き込めなかった行です。' });
  return { status: 'completed', runId: state.runId, written: state.written, failed: failed, background: state.background || 0, total: state.total };
}

function sheetById_(id) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i += 1) if (sheets[i].getSheetId() === id) return sheets[i];
  return null;
}

/** 法人番号の空白とハイフンを除き、全角の数字を半角にする（kintone-plugin/src/workflow.js と同じ扱い）。 */
function corporateNumber_(value) {
  return cleanString_(value).normalize('NFKC').replace(/[\s\-‐‑–—―]/g, '');
}

var SKIPPED_LIMIT = 300;
var SKIPPED_REASONS = { empty: '会社名も法人番号も入っていません。', invalid: INVALID_NUMBER };

function capSkipped_(skipped) {
  var empty = skipped.empty.slice(0, SKIPPED_LIMIT), invalid = skipped.invalid.slice(0, SKIPPED_LIMIT);
  return { empty: empty, invalid: invalid, more: skipped.empty.length + skipped.invalid.length - empty.length - invalid.length };
}

/** 外した行を、行の順に結果の形にする。上限を超えた分は件数だけを 1 行で出す。 */
function skippedFailed_(skipped) {
  if (!skipped) return [];
  var capped = skipped.more == null ? capSkipped_(skipped) : skipped;
  var rows = capped.empty.map(function (row) { return [row, 'empty']; }).concat(capped.invalid.map(function (row) { return [row, 'invalid']; }));
  rows.sort(function (a, b) { return a[0] - b[0]; });
  var failed = rows.map(function (r) { return { ref: r[0] + ' 行目', reason: SKIPPED_REASONS[r[1]] }; });
  if (capped.more > 0) failed.push({ ref: 'ほか ' + capped.more + ' 行', reason: '会社名も法人番号も無いか、法人番号が 13 桁でない行です。' });
  return failed;
}

/** 会社を決める状態（利用者の画面の対象の一覧と同じ分け方。worker/src/web/data/object-set.ts）ごとの、決まっていない行の数。 */
function unresolvedCounts_(set) {
  var counts = {};
  (set.identifications || []).forEach(function (item) {
    var status = String(item.status || (item.corporate_number || item.object_key ? 'chosen' : 'pending'));
    var state = status === 'chosen' || status === 'source_unavailable' || status === 'not_found' ? status
      : status === 'candidates' ? (item.needs_refinement ? 'too_many' : 'candidates') : 'pending';
    if (state !== 'chosen') counts[state] = (counts[state] || 0) + 1;
  });
  return Object.keys(counts).length ? counts : null;
}

function reasonText_(reason) {
  return REASONS[reason] || '反映を受け付けませんでした。';
}

function applyJudgments_(sheet, settings, columns, run, judgments, objectSetItems) {
  var judgmentsByObject = indexBy_(judgments, 'object_id');
  var requests = [];
  var errored = [];
  objectSetItems.forEach(function (item) {
    var objectId = item.object_id || (item.object && item.object.id);
    if (!objectId || !item.input_record_ref || !judgmentsByObject[objectId]) return;
    // 評価できなかった会社は書かない（前の結果を空で上書きしない）。
    if (judgmentsByObject[objectId].error) {
      var errorRow = findRowById_(sheet, columns.rowId, item.input_record_ref);
      errored.push({ ref: errorRow ? errorRow + ' 行目' : (judgmentsByObject[objectId].display_name || '行'), reason: EVALUATION_FAILED, code: 'evaluation_failed' });
      return;
    }
    requests.push({ input_record_ref: item.input_record_ref, object_id: objectId, kind: 'append' });
  });
  if (!requests.length) return { written: 0, failed: errored };
  var response = apiRequest_('POST', '/sinks/' + encodeURIComponent(settings.sinkId) + '/reflections', {
    run_id: run.id,
    items: requests,
  }).body;
  var outcome = { written: 0, failed: errored.concat(rejected_(sheet, columns, response)) };
  var reflections = reflectionResults_(response);
  var sink = (apiRequest_('GET', '/sinks').body.sinks || []).filter(function (entry) { return entry.id === settings.sinkId; })[0];
  for (var start = 0; start < reflections.length; start += 20) {
    var group = reflections.slice(start, start + 20);
    var leases = sink.has_secret ? apiRequest_('POST', '/sinks/' + encodeURIComponent(settings.sinkId) + '/reflections/lease', { reflection_ids: group.map(function (reflection) { return reflection.id; }) }).body.results : [];
    var leasedAt = nowMs_();
    group.forEach(function (reflection) {
      if (reflection.status === 'succeeded') return;
      var lease = leases.filter(function (entry) { return entry.id === reflection.id; })[0];
      if (sink.has_secret && (!lease.leased || nowMs_() - leasedAt >= 90000)) {
        if (lease.reason === 'reflection_ineligible') { var row = findRowById_(sheet, columns.rowId, reflection.input_record_ref); outcome.failed.push({ ref: row + ' 行目', reason: reasonText_(lease.reason), code: lease.reason }); }
        else if (lease.reason !== 'not_requested') outcome.background = (outcome.background || 0) + 1;
        return;
      }
      if (lease) REPORT_LOCKS[reflection.id] = lease.lock_token;
      try { count_(outcome, applyOneAppend_(sheet, settings, columns, reflection, judgmentsByObject[reflection.object_id], run)); }
      finally { delete REPORT_LOCKS[reflection.id]; }
    });
  }
  return outcome;
}

/** 受け付けられなかった反映の要求を、行と理由にする（書き込み済みのものと quiet の理由は数えない）。 */
function rejected_(sheet, columns, response, quiet) {
  var skip = ['already_requested', 'already_succeeded', 'already_created'].concat(quiet || []);
  return (response.results || []).filter(function (result) {
    return !result.accepted && skip.indexOf(result.reason) === -1;
  }).map(function (result) {
    var row = result.request ? findRowById_(sheet, columns.rowId, result.request.input_record_ref) : null;
    return { ref: row ? row + ' 行目' : '候補', reason: reasonText_(result.reason), code: result.reason };
  });
}

function count_(outcome, result) {
  if (!result) return;
  if (result.ok) outcome.written += 1;
  else if (result.reason) outcome.failed.push({ ref: result.ref, reason: result.reason, code: result.code });
}

function applyOneAppend_(sheet, settings, columns, reflection, judgment, run) {
  var row = findRowById_(sheet, columns.rowId, reflection.input_record_ref);
  if (judgment.error) {
    reportReflection_(reflection.id, 'failed', null, '評価できなかった会社です。');
    return { ref: row ? row + ' 行目' : (judgment.display_name || '行'), reason: EVALUATION_FAILED, code: 'evaluation_failed' };
  }
  if (!row) {
    reportReflection_(reflection.id, 'failed', null, 'record_missing');
    return { ref: '見つからない行', code: 'record_missing', reason: '評価した行が見つかりません（行を消したか、隠し列の BizFetch行ID を消したときに起きます）。' };
  }
  if (!targetMatches_(sheet, row, columns, settings, reflection.object_id, judgment)) {
    setStatusSafely_(sheet, row, columns.status, '失敗');
    reportReflection_(reflection.id, 'failed', null, '行の会社が評価した会社と一致しません。');
    return { ref: row + ' 行目', code: 'mismatch', reason: '行の会社名・法人番号が、評価した会社と違います。行を直してから、もう一度評価してください。' };
  }
  var runSeq = judgment.run_seq != null ? judgment.run_seq : run.seq;
  var currentRunSeq = sheet.getRange(row, columns.runSeq).getValue();
  if (currentRunSeq !== '' && Number(currentRunSeq) >= Number(runSeq)) {
    reportReflection_(reflection.id, 'failed', null, '新しい実行の結果が既に反映されています。');
    return { ref: row + ' 行目', code: 'old_run', reason: REASONS.old_run };
  }
  try {
    writeJudgment_(sheet, row, settings, columns, judgment, runSeq);
    reportReflection_(reflection.id, 'succeeded');
    return { ok: true };
  } catch (_error) {
    setStatusSafely_(sheet, row, columns.status, '結果不明');
    reportReflection_(reflection.id, 'unknown', reflection.input_record_ref, '書き込み結果を確認できません。');
    return { ref: row + ' 行目', code: 'unknown', reason: '書き込めたか確かめられませんでした。「反映を待っている分を反映」で確かめ直します。' };
  }
}

/** 「候補を書き出す」: 保存した候補を、同じ会社の行を探して更新し、無ければ下に足す。 */
function importCandidates() {
  var settings = requireSettings_({ sink: true, profile: true });
  var profileId = settings.profileId;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var columns = ensureColumns_(sheet, settings);

  reconcileUnknownCreates_(sheet, settings, columns);
  var marks = fetchAllPages_('/marks?profile_id=' + encodeURIComponent(profileId)
    + '&sink_id=' + encodeURIComponent(settings.sinkId) + '&kind=candidate', 'marks');
  var marksByObject = {};
  var results = [];
  var outcome = { written: 0, failed: [] };
  marks.forEach(function (mark) {
    if (!mark.judgment) return;
    marksByObject[mark.object_id] = mark;
    var response = apiRequest_('POST', '/sinks/' + encodeURIComponent(settings.sinkId) + '/reflections', {
      run_id: mark.judgment.run_id,
      items: [{input_record_ref: 'candidate:' + profileId + ':' + mark.object_id,
        object_id: mark.object_id, kind: 'create'}],
    }).body;
    rejected_(sheet, columns, response, ['old_run']).forEach(function (failure) {
      outcome.failed.push({ ref: mark.judgment.display_name || failure.ref, reason: failure.reason });
    });
    results = results.concat(reflectionResults_(response));
  });
  results.forEach(function (reflection) {
    if (reflection.status === 'succeeded') return;
    var mark = marksByObject[reflection.object_id];
    var judgment = mark && mark.judgment;
    if (!judgment) {
      reportReflection_(reflection.id, 'failed', null, '候補の評価の結果がありません。');
      return;
    }
    count_(outcome, applyCandidate_(sheet, settings, columns, reflection, judgment));
  });
  return { status: 'completed', written: outcome.written, failed: outcome.failed, total: marks.length };
}

function applyCandidate_(sheet, settings, columns, reflection, judgment) {
  if (judgment.error) {
    reportReflection_(reflection.id, 'failed', null, '評価できなかった会社です。');
    return { ref: judgment.display_name || '候補', reason: EVALUATION_FAILED, code: 'evaluation_failed' };
  }
  var runSeq = judgment.run_seq != null ? judgment.run_seq : reflection.run_seq;
  var existingRow = findCandidateRow_(sheet, columns, reflection.object_id);
  var currentSeq = existingRow ? sheet.getRange(existingRow, columns.runSeq).getValue() : '';
  if (currentSeq !== '' && Number(currentSeq) >= Number(runSeq)) {
    reportReflection_(reflection.id, 'failed', null, '新しい評価ではありません。');
    return null;
  }
  var row = existingRow || sheet.getLastRow() + 1;
  var rowId = existingRow ? String(sheet.getRange(row, columns.rowId).getValue() || reflection.input_record_ref) : reflection.input_record_ref;
  try {
    if (!existingRow || !sheet.getRange(row, columns.rowId).getValue()) sheet.getRange(row, columns.rowId).setValue(rowId);
    sheet.getRange(row, columns.objectId).setValue(reflection.object_id);
    writeCandidateIdentity_(sheet, row, settings, columns, judgment);
    writeJudgment_(sheet, row, settings, columns, judgment, runSeq);
    reportReflection_(reflection.id, 'succeeded', rowId);
    return { ok: true };
  } catch (_error) {
    setStatusSafely_(sheet, row, columns.status, '結果不明');
    reportReflection_(reflection.id, 'unknown', rowId, '作成結果を確認できません。');
    return { ref: row + ' 行目', code: 'unknown', reason: '書き込めたか確かめられませんでした。「反映を待っている分を反映」で確かめ直します。' };
  }
}

/** 「反映を待っている分を反映」: BizFetch 側で反映を待っている分を書く。結果不明は行の識別子・対象・評価の番号が合うときだけ確定する。 */
function importPending() {
  var settings = requireSettings_({ sink: true, profile: false });
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var columns = ensureColumns_(sheet, settings);
  var pending = fetchAllPages_(
    '/sinks/' + encodeURIComponent(settings.sinkId) + '/pending',
    'items',
  );
  var outcome = { written: 0, failed: [] };
  pending.forEach(function (entry) {
    var reflection = entry.reflection || entry;
    var judgment = entry.judgment || reflection.judgment;
    if (reflection.kind === 'create') {
      if (reflection.status === 'unknown') { if (reconcileOneUnknownCreate_(sheet, columns, entry)) outcome.written += 1; }
      else if (judgment) count_(outcome, applyCandidate_(sheet, settings, columns, reflection, judgment));
      else {
        reportReflection_(reflection.id, 'failed', null, '候補の評価の結果がありません。');
        outcome.failed.push({ ref: '候補', reason: '候補の評価の結果がありません。' });
      }
      return;
    }
    if (!judgment) {
      reportReflection_(reflection.id, 'failed', null, '反映を待っている分に評価の結果がありません。');
      outcome.failed.push({ ref: '行', reason: '評価の結果がありません。' });
      return;
    }
    var run = entry.run || { id: reflection.run_id, seq: reflection.run_seq };
    if (reflection.status === 'unknown') {
      var row = findRowById_(sheet, columns.rowId, reflection.input_record_ref);
      var rowRunSeq = row ? sheet.getRange(row, columns.runSeq).getValue() : '';
      var rowObjectId = row ? sheet.getRange(row, columns.objectId).getValue() : '';
      if (row && String(rowObjectId) === String(reflection.object_id)
          && String(rowRunSeq) === String(reflection.run_seq || judgment.run_seq || run.seq)) {
        setStatusSafely_(sheet, row, columns.status, '反映済み');
        reportReflection_(reflection.id, 'succeeded', null, null, entry.reconciliation);
        outcome.written += 1;
      } else {
        if (row) setStatusSafely_(sheet, row, columns.status, '失敗');
        reportReflection_(reflection.id, 'failed', null, '行 ID と評価の番号を照合できません。', entry.reconciliation);
        outcome.failed.push({ ref: row ? row + ' 行目' : '見つからない行', reason: '書き込めたかを確かめられなかった行が、評価と合いません。もう一度評価してください。' });
      }
      return;
    }
    count_(outcome, applyOneAppend_(sheet, settings, columns, reflection, judgment, run));
  });
  return { status: 'completed', written: outcome.written, failed: outcome.failed, total: pending.length };
}

function reconcileUnknownCreates_(sheet, settings, columns) {
  var pending = fetchAllPages_(
    '/sinks/' + encodeURIComponent(settings.sinkId) + '/pending',
    'items',
  );
  pending.forEach(function (entry) {
    var reflection = entry.reflection || entry;
    if (reflection.kind === 'create' && reflection.status === 'unknown') {
      reconcileOneUnknownCreate_(sheet, columns, entry);
    }
  });
}

function reconcileOneUnknownCreate_(sheet, columns, entry) {
  var reflection = entry.reflection || entry;
  var reconciliation = entry.reconciliation || { created_ref: reflection.created_ref, run_seq: reflection.run_seq };
  var row = findCandidateRow_(sheet, columns, reflection.object_id);
  if (row && String(sheet.getRange(row, columns.runSeq).getValue()) === String(reconciliation.run_seq)
      && (!reconciliation.created_ref || String(sheet.getRange(row, columns.rowId).getValue()) === String(reconciliation.created_ref))) {
    var rowId = String(sheet.getRange(row, columns.rowId).getValue());
    setStatusSafely_(sheet, row, columns.status, '反映済み');
    reportReflection_(reflection.id, 'succeeded', rowId, null, reconciliation);
    return true;
  }
  reportReflection_(reflection.id, 'failed', null, '作成先の対象と実行番号を照合できません。', reconciliation);
  return false;
}

var REPORT_LOCKS = {};
function reportReflection_(reflectionId, status, createdRef, error, reconciliation) {
  var body = { status: status, reporter: 'addon' };
  if (REPORT_LOCKS[reflectionId]) body.lock_token = REPORT_LOCKS[reflectionId];
  if (createdRef) body.created_ref = createdRef;
  if (error) body.error = error;
  if (reconciliation) body.reconciliation = reconciliation;
  apiRequest_('POST', '/reflections/' + encodeURIComponent(reflectionId) + '/report', body);
}

function reflectionResults_(response) {
  return (response.results || []).filter(function (result) {
    return result.reflection && (result.accepted || result.reason === 'already_requested');
  }).map(function (result) {
    return Object.assign({}, result.request || {}, result.reflection);
  });
}

function writeJudgment_(sheet, row, settings, columns, judgment, runSeq) {
  settings.outputMappings.forEach(function (mapping) {
    var value = outputValue_(judgment, mapping.field);
    sheet.getRange(row, columns.byHeader[mapping.column]).setValue(value);
  });
  sheet.getRange(row, columns.objectId).setValue(judgment.object_id);
  sheet.getRange(row, columns.judgedAt).setValue(judgment.evaluated_at || '');
  sheet.getRange(row, columns.reflectedAt).setValue(new Date(nowMs_()).toISOString());
  sheet.getRange(row, columns.runSeq).setValue(runSeq == null ? '' : runSeq);
  sheet.getRange(row, columns.status).setValue('反映済み');
}

function writeCandidateIdentity_(sheet, row, settings, columns, judgment) {
  if (settings.nameColumn) {
    sheet.getRange(row, columns.byHeader[settings.nameColumn]).setValue(
      getPath_(judgment, 'acquired.name') || judgment.display_name || '',
    );
  }
  if (settings.corporateNumberColumn) {
    sheet.getRange(row, columns.byHeader[settings.corporateNumberColumn]).setValue(
      getPath_(judgment, 'acquired.corporate_number') || judgment.identifier || '',
    );
  }
}

function outputValue_(judgment, field) {
  var allowed = OUTPUT_FIELDS.some(function (option) { return option.key === field; });
  if (!allowed) throw new Error('許可されていない書き出し項目です: ' + field);
  var aliases = {
    judgment_items: 'items', scope: 'scope_composed', judged_at: 'evaluated_at',
    corporate_number: 'acquired.法人番号', company_name: 'acquired.商号', corporate_kind: 'acquired.法人種別',
    head_office: 'acquired.本店所在地', established_at: 'acquired.設立年月日', years_since_establishment: 'acquired.設立からの年数',
    industry: 'acquired.業種', employees: 'acquired.従業員数', capital: 'acquired.資本金', office_count: 'acquired.事業所数',
    invoice_registration: 'acquired.インボイス登録', latest_change: 'acquired.直近の変更', closed: 'acquired.閉鎖',
  };
  var value = getPath_(judgment, aliases[field] || field);
  if (field === 'attributions') {
    return (value || []).map(function (item) {
      return [item.text || item.notice || item.source, item.url].filter(Boolean).join(' ');
    }).join('\n');
  }
  if (field === 'judgment_items') return formatJudgmentItems_(value);
  if (field === 'filters') return formatFilters_(judgment.items);
  if (field === 'detail_url') return judgment.detail_url ? appOrigin_() + judgment.detail_url : '';
  if (value != null && typeof value === 'object') return JSON.stringify(value);
  return value == null ? '' : value;
}

// 判定項目の結果の読める文（worker/src/sinks/text.ts・kintone-plugin/src/records.js と同じ形）。保存した状態の値は語に言い換える。
var STATE_LABELS = { 事実なし: '情報なし', 更新できていない: '取得できていない', 条件未指定: '条件なし', 判定できない条件: '判定できない' };

function resultText_(item) {
  if (item.state && item.state !== '問えた') return STATE_LABELS[item.state] || String(item.state);
  return [
    item.type === 'score' && item.value != null ? '適合度 ' + formatPercent_(item.value) : '',
    item.result ? String(item.result) : '',
    item.confirmation ? '要確認' : '',
  ].filter(Boolean).join('・') || '確認できない';
}

/** 判定項目ごとの結果を 1 項目 1 行で（外す条件の項目は除く）。 */
function formatJudgmentItems_(items) {
  var judgments = items && items.judgments ? items.judgments : {};
  return Object.keys(judgments).filter(function (key) { return !(judgments[key] || {}).required; }).map(function (key) {
    var item = judgments[key] || {};
    return (item.name || key) + ': ' + resultText_(item);
  }).join('\n');
}

/** 外す条件の結果を 1 条件 1 行で（条件どおり・外れ・不明）。 */
function formatFilters_(items) {
  var judgments = items && items.judgments ? items.judgments : {};
  return Object.keys(judgments).filter(function (key) { return (judgments[key] || {}).required; }).map(function (key) {
    var item = judgments[key];
    return (item.name || key) + ': ' + (item.result === '満たす' ? '条件どおり' : item.result === '満たさない' ? '条件外' : '不明');
  }).join('\n');
}

function formatPercent_(value) {
  return String(Math.round(Number(value) * 1000) / 10) + '%';
}

function targetMatches_(sheet, row, columns, settings, objectId, judgment) {
  void objectId;
  var corporateNumber = settings.corporateNumberColumn
    ? cellDisplay_(sheet, row, columns.byHeader[settings.corporateNumberColumn])
    : '';
  var name = settings.nameColumn ? cellDisplay_(sheet, row, columns.byHeader[settings.nameColumn]) : '';
  var judgmentNumber = getPath_(judgment, 'acquired.corporate_number') || judgment.identifier;
  // 行と会社の組は、この行から作った BizFetch の対象の一覧が決めたもの。法人番号が両方にあるときだけ照らし、
  // 会社名だけの行は書き方の違い（「(株)」・略称など）で拒まない（サーバーは会社名の揺れを吸収して会社を決める）。
  if (corporateNumber && judgmentNumber) return normalizeIdentifier_(corporateNumber_(corporateNumber)) === normalizeIdentifier_(judgmentNumber);
  return Boolean(name || corporateNumber);
}

function ensureColumns_(sheet, settings) {
  var required = [
    SYSTEM_HEADERS.rowId,
    SYSTEM_HEADERS.objectId,
    settings.statusColumn,
    settings.judgedAtColumn,
    settings.reflectedAtColumn,
    settings.runSeqColumn,
  ];
  if (settings.nameColumn) required.push(settings.nameColumn);
  if (settings.corporateNumberColumn) required.push(settings.corporateNumberColumn);
  settings.userDataColumns.forEach(function (item) { required.push(item.column); });
  settings.outputMappings.forEach(function (item) { required.push(item.column); });
  var lastColumn = Math.max(sheet.getLastColumn(), 1);
  var headers = sheet.getRange(HEADER_ROW, 1, 1, lastColumn).getDisplayValues()[0];
  required.forEach(function (header) {
    if (!header) return;
    if (headers.indexOf(header) === -1) {
      headers.push(header);
      sheet.getRange(HEADER_ROW, headers.length).setValue(header);
    }
  });
  var byHeader = {};
  headers.forEach(function (header, index) { if (header) byHeader[header] = index + 1; });
  sheet.hideColumns(byHeader[SYSTEM_HEADERS.rowId]);
  sheet.hideColumns(byHeader[SYSTEM_HEADERS.objectId]);
  return {
    byHeader: byHeader,
    rowId: byHeader[SYSTEM_HEADERS.rowId],
    objectId: byHeader[SYSTEM_HEADERS.objectId],
    status: byHeader[settings.statusColumn],
    judgedAt: byHeader[settings.judgedAtColumn],
    reflectedAt: byHeader[settings.reflectedAtColumn],
    runSeq: byHeader[settings.runSeqColumn],
  };
}

function ensureRowId_(sheet, row, column) {
  var cell = sheet.getRange(row, column);
  var value = cleanString_(cell.getValue());
  if (!value) {
    value = Utilities.getUuid();
    cell.setValue(value);
  }
  return value;
}

function findRowById_(sheet, column, rowId) {
  var lastRow = sheet.getLastRow();
  if (lastRow <= HEADER_ROW) return null;
  var values = sheet.getRange(HEADER_ROW + 1, column, lastRow - HEADER_ROW, 1).getValues();
  for (var index = 0; index < values.length; index += 1) {
    if (String(values[index][0]) === String(rowId)) return index + HEADER_ROW + 1;
  }
  return null;
}

function findCandidateRow_(sheet, columns, objectId) {
  var lastRow = sheet.getLastRow();
  if (lastRow <= HEADER_ROW) return null;
  var objects = sheet.getRange(HEADER_ROW + 1, columns.objectId, lastRow - HEADER_ROW, 1).getValues();
  for (var index = 0; index < objects.length; index += 1) {
    if (String(objects[index][0]) === String(objectId)) return index + HEADER_ROW + 1;
  }
  return null;
}

function fetchAllPages_(path, key) {
  var offset = 0;
  var limit = 200;
  var all = [];
  while (true) {
    var separator = path.indexOf('?') === -1 ? '?' : '&';
    var body = apiRequest_('GET', path + separator + 'offset=' + offset + '&limit=' + limit).body;
    var values = body[key] || [];
    all = all.concat(values);
    var pagination = body.pagination || { offset: offset, limit: limit, total: all.length };
    if (!values.length || all.length >= Number(pagination.total)) break;
    offset += values.length;
  }
  return all;
}

function apiRequest_(method, path, payload) {
  var apiKey = getApiKey_();
  if (!apiKey) throw new Error('BizFetch の API キーを設定してください。');
  return apiRequestWithKey_(method, path, payload, apiKey);
}

function apiRequestWithKey_(method, path, payload, apiKey) {
  var options = {
    method: method.toLowerCase(),
    headers: { Authorization: 'Bearer ' + apiKey },
    contentType: 'application/json',
    muteHttpExceptions: true,
  };
  if (payload !== undefined) options.payload = JSON.stringify(payload);
  var response;
  try {
    response = UrlFetchApp.fetch(BIZFETCH_API_BASE_URL + path, options);
  } catch (_error) {
    throw new Error('BizFetch に接続できませんでした。時間をおいてもう一度押してください。');
  }
  var status = response.getResponseCode();
  var text = response.getContentText();
  var body;
  try {
    body = text ? JSON.parse(text) : {};
  } catch (_error) {
    throw new Error('BizFetch から読めない応答が返りました（' + status + '）。時間をおいてもう一度押してください。');
  }
  if (status < 200 || status >= 300) {
    var apiError = body && body.error ? body.error : {};
    var message = safeErrorMessage_(apiError.message || (status === 401 ? 'API キーが使えません。設定で API キーを入れ直してください。' : 'BizFetch の呼び出しに失敗しました（' + status + '）。'), apiKey);
    if (apiError.request_id) message += '（問い合わせ番号 ' + apiError.request_id + '）';
    throw new Error(message);
  }
  return { status: status, body: body };
}

function safeErrorMessage_(message, apiKey) {
  var safe = cleanString_(message);
  if (apiKey) safe = safe.split(apiKey).join('[REDACTED]');
  return safe.replace(/Bearer\s+[^\s]+/gi, 'Bearer [REDACTED]');
}

function requireSettings_(requirements) {
  var settings = loadSettings_();
  if (!getApiKey_()) throw new Error('設定で BizFetch の API キーを入れてください。');
  if (requirements.sink && !settings.sinkId) throw new Error('設定を保存して、連携先を作ってください。');
  if (requirements.profile && !settings.profileId) throw new Error('設定で条件 ID を入れてください。');
  if (!settings.nameColumn && !settings.corporateNumberColumn) throw new Error('設定で会社名の列か法人番号の列を選んでください。');
  return settings;
}

function loadSettings_() {
  var raw = PropertiesService.getUserProperties().getProperty(SETTINGS_KEY);
  var stored = raw ? JSON.parse(raw) : {};
  return {
    sinkId: cleanString_(stored.sinkId),
    profileId: cleanString_(stored.profileId),
    template: cleanString_(stored.template),
    profileName: cleanString_(stored.profileName),
    nameColumn: cleanString_(stored.nameColumn),
    corporateNumberColumn: cleanString_(stored.corporateNumberColumn),
    userDataColumns: normalizeUserDataColumns_(stored.userDataColumns),
    outputMappings: normalizeOutputMappings_(stored.outputMappings),
    statusColumn: cleanString_(stored.statusColumn) || SYSTEM_HEADERS.status,
    judgedAtColumn: cleanString_(stored.judgedAtColumn) || SYSTEM_HEADERS.judgedAt,
    reflectedAtColumn: cleanString_(stored.reflectedAtColumn) || SYSTEM_HEADERS.reflectedAt,
    runSeqColumn: cleanString_(stored.runSeqColumn) || SYSTEM_HEADERS.runSeq,
  };
}

function getApiKey_() {
  return PropertiesService.getUserProperties().getProperty(API_KEY_PROPERTY) || '';
}

function saveContinuation_(state) {
  while (state.failed && state.failed.length > 100) { state.failed.pop(); state.failedMore = (state.failedMore || 0) + 1; }
  function bytes() { return encodeURIComponent(JSON.stringify(state)).replace(/%[0-9A-F]{2}|[^%]/g, 'x').length; }
  while (bytes() > 8192) {
    if (state.failed && state.failed.length) { state.failed.pop(); state.failedMore = (state.failedMore || 0) + 1; }
    else if (state.skippedRows && (state.skippedRows.invalid.length || state.skippedRows.empty.length)) { (state.skippedRows.invalid.length ? state.skippedRows.invalid : state.skippedRows.empty).pop(); state.skippedRows.more = (state.skippedRows.more || 0) + 1; }
    else throw new Error('続きの情報を保存できません。');
  }
  PropertiesService.getUserProperties().setProperty(CONTINUATION_KEY, JSON.stringify(state));
}

function loadContinuation_() {
  var raw = PropertiesService.getUserProperties().getProperty(CONTINUATION_KEY);
  return raw ? JSON.parse(raw) : null;
}

function clearContinuation_() {
  PropertiesService.getUserProperties().deleteProperty(CONTINUATION_KEY);
}

function normalizeOutputMappings_(mappings) {
  var allowed = {};
  OUTPUT_FIELDS.forEach(function (field) { allowed[field.key] = true; });
  return (Array.isArray(mappings) ? mappings : []).map(function (mapping) {
    var field = cleanString_(mapping.field);
    var column = cleanString_(mapping.column);
    if (!allowed[field]) throw new Error('書き出せない項目です（common.md 7節にない項目）: ' + field);
    if (!column) throw new Error('書き出す列の見出しを入れてください。');
    return { field: field, column: column };
  });
}

function normalizeUserDataColumns_(mappings) {
  return (Array.isArray(mappings) ? mappings : []).map(function (mapping) {
    var column = cleanString_(mapping.column);
    var factName = cleanString_(mapping.factName);
    if (!column || !factName) throw new Error('判定に使う列を選び直してください。');
    return { column: column, factName: factName };
  });
}

function getPath_(object, path) {
  return path.split('.').reduce(function (value, key) {
    return value == null ? undefined : value[key];
  }, object);
}

function indexBy_(values, key) {
  var result = {};
  values.forEach(function (value) { result[value[key]] = value; });
  return result;
}

function cellDisplay_(sheet, row, column) {
  if (!column) return '';
  return cleanString_(sheet.getRange(row, column).getDisplayValue());
}

function cleanString_(value) {
  return value == null ? '' : String(value).trim();
}

function normalizeIdentifier_(value) {
  return cleanString_(value).replace(/[^0-9A-Za-z]/g, '').toLowerCase();
}

function normalizeName_(value) {
  return cleanString_(value).replace(/[\s　]/g, '').toLowerCase();
}

function setStatusSafely_(sheet, row, column, status) {
  try {
    sheet.getRange(row, column).setValue(status);
  } catch (_error) {
    // 報告経路を優先し、秘密や元の例外文をログへ出さない。
  }
}

function nowMs_() {
  if (typeof BizFetchTestHooks !== 'undefined' && BizFetchTestHooks.now) {
    return BizFetchTestHooks.now();
  }
  return Date.now();
}

function currentSheetId_() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return spreadsheet.getId() + ':' + spreadsheet.getActiveSheet().getSheetId();
}

function appOrigin_() {
  return BIZFETCH_API_BASE_URL.replace(/\/api\/v1\/?$/, '');
}