/**
 * Code.gs - スプレッドシートで条件指定 → BigQueryでセグメント生成 → b-dash/yappli連携
 *
 * 想定スプレッドシート（シート名: 'Segment'）：
 *   A1: 'セグメント名'   B1: '高額購入者'
 *   A2: '条件1'         B2: '累計購入金額 >= 100000'
 *   A3: '条件2'         B3: '購入回数 >= 5'
 *   A4: '結合ロジック'   B4: 'AND'
 *   ...条件は何行でも追加可（A列が「条件N」のセル）
 *
 * 使い方：
 *   1) スプレッドシートを開く
 *   2) 拡張機能 > Apps Script を開き、Code.gs と Config.gs を貼り付け
 *   3) スプレッドシートをリロードすると「セグメント連携」メニューが追加される
 *   4) 「① セグメント生成（SQLプレビュー）」でBigQuery SQLを確認
 *   5) 「② BigQueryで実行」で実際のクエリを発行
 *   6) 「③ b-dashへ送信」「④ yappliへ送信」で連携
 */

// =========================================================
// メニュー登録
// =========================================================
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('セグメント連携')
    .addItem('① SQLプレビュー', 'menuPreviewSQL')
    .addItem('② BigQueryで実行', 'menuRunBigQuery')
    .addSeparator()
    .addItem('③ b-dashへ送信', 'menuSendToBdash')
    .addItem('④ yappliへ送信', 'menuSendToYappli')
    .addSeparator()
    .addItem('🚀 ワンクリック実行（②→③④）', 'menuRunAll')
    .addToUi();
}

// =========================================================
// シート読み込み: セグメント定義を構造化
// =========================================================
function readSegmentDefinition_() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Segment')
          || SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sh.getRange('A1:B' + sh.getLastRow()).getValues();
  const obj = { segmentName: '', combineLogic: 'AND', conditions: [] };
  for (let i = 0; i < values.length; i++) {
    const k = String(values[i][0] || '').trim();
    const v = String(values[i][1] || '').trim();
    if (!k) continue;
    if (k === 'セグメント名') obj.segmentName = v;
    else if (k === '結合ロジック') obj.combineLogic = (v.toUpperCase() === 'OR' ? 'OR' : 'AND');
    else if (k.indexOf('条件') === 0 && v) obj.conditions.push(v);
  }
  return obj;
}

// =========================================================
// 条件文字列パース (例: "累計購入金額 >= 100000")
// =========================================================
const FIELD_LABEL_TO_COL = {
  '顧客コード':'customer_code','お客様名称':'customer_name','年齢':'age','性別':'gender',
  '住所':'address','住宅形式':'housing_type','見込区分':'prospect_category',
  '見込発生日':'prospect_created_date','面談結果':'meeting_result',
  '契約日':'contract_date','契約日（受任日）':'contract_date','納品日':'delivery_date',
  '売上金額':'sales_amount','売上金額（受任時）':'sales_amount_contract',
  '売上金額（納品時）':'sales_amount_delivery','累計購入金額':'total_purchase_amount',
  '購入回数':'purchase_count','最終購入日':'last_purchase_date','続柄':'relation',
  '命日':'death_date','49日法要':'memorial_49day_date','寺院／宗派':'temple_sect',
  '接触場所':'contact_channel','紹介先企業名':'referral_company_name','紹介日':'referral_date',
  '担当店名':'store_name','エリア名称':'area_name'
};
const NUMERIC_COLS = ['age','sales_amount','sales_amount_contract','sales_amount_delivery',
                      'total_purchase_amount','purchase_count'];
const DATE_COLS = ['prospect_created_date','contract_date','delivery_date','last_purchase_date',
                   'death_date','memorial_49day_date','referral_date'];

function parseConditionLine_(line) {
  const m = line.match(/^(.+?)\s*(>=|<=|!=|=|<>|>|<)\s*(.+)$/);
  if (!m) return null;
  const labelRaw = m[1].trim();
  let op = m[2]; if (op === '<>') op = '!=';
  const valRaw = m[3].trim().replace(/^['"]|['"]$/g, '');
  const col = FIELD_LABEL_TO_COL[labelRaw] || labelRaw;
  return { column: col, operator: op, value: valRaw };
}

function fmtVal_(v, col) {
  if (v === '' || v == null) return 'NULL';
  if (NUMERIC_COLS.indexOf(col) >= 0) {
    const n = Number(String(v).replace(/[^0-9.\-]/g, ''));
    return isFinite(n) ? String(n) : 'NULL';
  }
  if (DATE_COLS.indexOf(col) >= 0) return "DATE '" + v + "'";
  return "'" + String(v).replace(/'/g, "''") + "'";
}

// =========================================================
// SQL生成
// =========================================================
function buildSegmentSQL_(def) {
  const conds = def.conditions.map(parseConditionLine_).filter(Boolean);
  const whereParts = conds.map(function (c) {
    return '\`' + c.column + '\` ' + c.operator + ' ' + fmtVal_(c.value, c.column);
  });
  const whereSql = whereParts.join(def.combineLogic === 'OR' ? '\n  OR ' : '\n  AND ');
  const safe = (def.segmentName || 'unnamed').replace(/[^A-Za-z0-9_]/g, '_');
  return [
    '-- セグメント: ' + def.segmentName,
    '-- 結合ロジック: ' + def.combineLogic,
    'WITH segment_' + safe + ' AS (',
    '  SELECT customer_code, customer_name, email, phone_number,',
    '         total_purchase_amount, purchase_count, last_purchase_date,',
    '         CURRENT_TIMESTAMP() AS segmented_at,',
    "         '" + (def.segmentName||'').replace(/'/g, "''") + "' AS segment_name",
    '  FROM \`' + CONFIG.BQ_PROJECT + '.' + CONFIG.BQ_DATASET + '.' + CONFIG.BQ_TABLE + '\`',
    '  WHERE',
    '  ' + whereSql,
    ')',
    'SELECT * FROM segment_' + safe + ' ORDER BY total_purchase_amount DESC;'
  ].join('\n');
}

// =========================================================
// メニュー実行
// =========================================================
function menuPreviewSQL() {
  const def = readSegmentDefinition_();
  const sql = buildSegmentSQL_(def);
  showHtml_('生成SQL', sql);
}

function menuRunBigQuery() {
  const def = readSegmentDefinition_();
  const sql = buildSegmentSQL_(def);
  // 実環境では BigQuery.Jobs.query を使用
  // const result = BigQuery.Jobs.query({ query: sql, useLegacySql: false }, CONFIG.BQ_PROJECT);
  // 本サンプルではAPIゲートウェイ経由で実行する想定
  const res = UrlFetchApp.fetch(CONFIG.BQ_PROXY_ENDPOINT, {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify({ sql: sql, segmentName: def.segmentName }),
    muteHttpExceptions: true
  });
  showHtml_('BigQuery実行結果', res.getContentText());
  CacheService.getScriptCache().put('LAST_SEGMENT_RESULT', res.getContentText(), 600);
}

function menuSendToBdash() {
  const cache = CacheService.getScriptCache().get('LAST_SEGMENT_RESULT');
  if (!cache) { SpreadsheetApp.getUi().alert('先に「BigQueryで実行」を行ってください'); return; }
  const result = JSON.parse(cache);
  const res = UrlFetchApp.fetch(CONFIG.BDASH_ENDPOINT, {
    method: 'post', contentType: 'application/json',
    headers: { 'Authorization': 'Bearer ' + CONFIG.BDASH_API_KEY },
    payload: JSON.stringify({ segmentName: result.segmentName, rows: result.rows }),
    muteHttpExceptions: true
  });
  SpreadsheetApp.getUi().alert('b-dash送信:\n' + res.getContentText());
}

function menuSendToYappli() {
  const cache = CacheService.getScriptCache().get('LAST_SEGMENT_RESULT');
  if (!cache) { SpreadsheetApp.getUi().alert('先に「BigQueryで実行」を行ってください'); return; }
  const result = JSON.parse(cache);
  const res = UrlFetchApp.fetch(CONFIG.YAPPLI_ENDPOINT, {
    method: 'post', contentType: 'application/json',
    headers: { 'Authorization': 'Bearer ' + CONFIG.YAPPLI_API_KEY },
    payload: JSON.stringify({ segmentName: result.segmentName, rows: result.rows }),
    muteHttpExceptions: true
  });
  SpreadsheetApp.getUi().alert('yappli送信:\n' + res.getContentText());
}

function menuRunAll() {
  menuRunBigQuery();
  Utilities.sleep(500);
  menuSendToBdash();
  menuSendToYappli();
}

function showHtml_(title, content) {
  const html = HtmlService.createHtmlOutput(
    '<pre style="white-space:pre-wrap;font-family:Menlo,monospace;font-size:12px;line-height:1.6">' +
    String(content).replace(/&/g,'&amp;').replace(/</g,'&lt;') +
    '</pre>'
  ).setWidth(900).setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html, title);
}