/**
 * WhereParcel - Google Sheets Tracking Macro
 *
 * API Endpoints:
 *   POST /v2/webhooks/register                  - Register tracking items (max 100/request)
 *   GET  /v2/webhooks/subscriptions/:requestId  - Fetch results
 *
 * Auth: Bearer {apiKey}:{secretKey}
 *
 * Setup:
 *   1. Open your Google Sheet
 *   2. Extensions > Apps Script
 *   3. Paste this code > Save
 *   4. Reload the spreadsheet - "WhereParcel" menu appears
 *   5. WhereParcel > Setup All (first time only)
 *   6. Enter API keys in Settings sheet
 *
 * Version: 1.0.0
 */

// ── Constants ──
const API_BASE_URL = 'https://api.whereparcel.com';
const BATCH_SIZE = 100;
const MACRO_VERSION = '1.0.0';
const VERSION_URL = 'https://www.whereparcel.com/downloads/version.txt';
const CODE_GS_URL = 'https://www.whereparcel.com/downloads/Code.gs';

const SETTINGS_SHEET = 'Settings';
const CARRIER_SHEET = 'Carrier Codes';
const INSTRUCTIONS_SHEET = 'Instructions';

// Settings cell addresses
const CELL = {
  API_KEY: 'B4',
  SECRET_KEY: 'B5',
  TARGET_SHEET: 'B8',
  CARRIER_COL: 'B9',
  TRACKING_COL: 'B10',
  RESULT_COL: 'B11',
  TARGET_MODE: 'B14',
  RANGE_MODE: 'B15',
  START_ROW: 'B16',
  END_ROW: 'B17',
};
const HISTORY_START_ROW = 32;

// Result column offsets
const RES = {
  STATUS: 0,
  STATUS_TEXT: 1,
  IS_DELIVERED: 2,
  RECEIVER: 3,
  PRODUCT: 4,
  DATE_DELIVERED: 5,
  DATE_LAST: 6,
  ERROR: 7,
  COL_COUNT: 8,
};


// ═══════════════════════════════════════
// Menu
// ═══════════════════════════════════════
// Simple onEdit: only handles unchecking (no UrlFetchApp needed)
// Real logic runs via installable trigger (onEditInstallable)
function onEdit(e) {
  if (!e || !e.range) return;
  if (e.range.getSheet().getName() !== SETTINGS_SHEET) return;
  if (e.value !== 'TRUE') return;
  // Uncheck immediately - actual function runs in installable trigger
  e.range.setValue(false);
}

// Installable trigger: runs with full authorization (UrlFetchApp allowed)
function onEditInstallable(e) {
  if (!e || !e.range) return;
  if (e.range.getSheet().getName() !== SETTINGS_SHEET) return;
  // Only react to TRUE (before onEdit unchecks it, value is still TRUE here)
  if (e.value !== 'TRUE') return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  if (col === 2) {
    if (row === 20) trackRegister();
    else if (row === 21) trackResults();
    else if (row === 22) clearResults();
  }
  if (col === 8 && row === 22) {
    setupAll(); // Reset Setup button
  }
}

function installTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // Remove existing installable triggers to avoid duplicates
  ScriptApp.getProjectTriggers().forEach(function(t) {
    const fn = t.getHandlerFunction();
    if (fn === 'onEditInstallable' || fn === 'onOpenInstallable') {
      ScriptApp.deleteTrigger(t);
    }
  });
  ScriptApp.newTrigger('onEditInstallable').forSpreadsheet(ss).onEdit().create();
  ScriptApp.newTrigger('onOpenInstallable').forSpreadsheet(ss).onOpen().create();
}

// Installable onOpen: checks for updates once per day.
// If a new version is found, shows a notification in the About section of Settings sheet.
function onOpenInstallable(e) {
  try {
    const cache = CacheService.getDocumentCache();
    if (cache.get('UPDATE_CHECKED')) return; // Already checked today

    const resp = UrlFetchApp.fetch(VERSION_URL, { muteHttpExceptions: true });
    if (resp.getResponseCode() !== 200) return;
    const serverVersion = resp.getContentText().trim();

    cache.put('UPDATE_CHECKED', '1', 86400); // Cache for 24 hours

    if (serverVersion !== MACRO_VERSION) {
      const wsS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SETTINGS_SHEET);
      if (wsS) {
        wsS.getRange('G23')
          .setValue('⚠ v' + serverVersion + ' available — WhereParcel > Check for Updates')
          .setFontColor('#c05000')
          .setFontWeight('bold')
          .setFontSize(9);
      }
    }
  } catch (err) {
    // Silently fail — don't interrupt the user's workflow
  }
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('WhereParcel')
    .addItem('Register Tracking', 'trackRegister')
    .addItem('Fetch Results', 'trackResults')
    .addSeparator()
    .addItem('Clear Results', 'clearResults')
    .addSeparator()
    .addItem('Setup All', 'setupAll')
    .addSeparator()
    .addItem('Check for Updates', 'checkVersion')
    .addToUi();

}


// ═══════════════════════════════════════
// Check Version / Auto Update
// ═══════════════════════════════════════
function checkVersion() {
  const ui = SpreadsheetApp.getUi();

  // Fetch latest version from server
  let serverVersion;
  try {
    const resp = UrlFetchApp.fetch(VERSION_URL, { muteHttpExceptions: true });
    if (resp.getResponseCode() !== 200) throw new Error('HTTP ' + resp.getResponseCode());
    serverVersion = resp.getContentText().trim();
  } catch (e) {
    ui.alert('Check for Updates', 'Could not reach the server. Please check your internet connection.\n\nError: ' + e.message, ui.ButtonSet.OK);
    return;
  }

  // Already up to date
  if (serverVersion === MACRO_VERSION) {
    ui.alert('Check for Updates', 'You are running the latest version (v' + MACRO_VERSION + ').', ui.ButtonSet.OK);
    return;
  }

  // New version available
  const result = ui.alert(
    'Update Available',
    'A new version is available!\n\n' +
    '  Current version: v' + MACRO_VERSION + '\n' +
    '  Latest version:  v' + serverVersion + '\n\n' +
    'Click YES to auto-update now.\n' +
    'Click NO to update manually (download from whereparcel.com).',
    ui.ButtonSet.YES_NO
  );

  if (result === ui.Button.YES) {
    doAutoUpdate(serverVersion);
  }
}

function doAutoUpdate(serverVersion) {
  const ui = SpreadsheetApp.getUi();

  // Fetch new script code
  let newCode;
  try {
    const resp = UrlFetchApp.fetch(CODE_GS_URL, { muteHttpExceptions: true });
    if (resp.getResponseCode() !== 200) throw new Error('HTTP ' + resp.getResponseCode());
    newCode = resp.getContentText();
  } catch (e) {
    ui.alert('Update Failed', 'Could not download the update.\n\nError: ' + e.message, ui.ButtonSet.OK);
    return;
  }

  // Use Apps Script REST API to replace script content
  const scriptId = ScriptApp.getScriptId();
  const token = ScriptApp.getOAuthToken();
  const url = 'https://script.googleapis.com/v1/projects/' + scriptId + '/content';

  // Fetch current content to preserve manifest (appsscript.json)
  let manifest = null;
  try {
    const getResp = UrlFetchApp.fetch(url, {
      method: 'get',
      headers: { Authorization: 'Bearer ' + token },
      muteHttpExceptions: true,
    });
    if (getResp.getResponseCode() === 200) {
      const content = JSON.parse(getResp.getContentText());
      const jsonFile = (content.files || []).find(function(f) { return f.name === 'appsscript'; });
      if (jsonFile) manifest = jsonFile;
    }
  } catch (e) { /* ignore - will use default manifest */ }

  const files = [
    {
      name: 'Code',
      type: 'SERVER_JS',
      source: newCode,
    },
  ];
  if (manifest) files.push(manifest);

  try {
    const putResp = UrlFetchApp.fetch(url, {
      method: 'put',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + token },
      payload: JSON.stringify({ files: files }),
      muteHttpExceptions: true,
    });

    if (putResp.getResponseCode() === 200) {
      ui.alert(
        'Update Complete',
        'Successfully updated to v' + serverVersion + '!\n\n' +
        'Please reload this spreadsheet to activate the new version.\n' +
        '(Close the tab and reopen, or press F5)',
        ui.ButtonSet.OK
      );
    } else {
      const errBody = putResp.getContentText();
      // If API access is denied, guide user to enable it
      if (putResp.getResponseCode() === 403 || errBody.indexOf('accessNotConfigured') !== -1) {
        showManualUpdateDialog(serverVersion);
      } else {
        throw new Error('HTTP ' + putResp.getResponseCode() + ': ' + errBody);
      }
    }
  } catch (e) {
    ui.alert('Update Failed', 'Auto-update failed.\n\nError: ' + e.message + '\n\nPlease update manually from whereparcel.com/tools/spreadsheet-tracker', ui.ButtonSet.OK);
  }
}

function showManualUpdateDialog(serverVersion) {
  const html = HtmlService.createHtmlOutput(
    '<style>body{font-family:Arial,sans-serif;font-size:13px;padding:12px;color:#2d3748}' +
    'h3{margin:0 0 8px;color:#2b6cb0}ol{padding-left:18px;margin:8px 0}' +
    'li{margin:5px 0}a{color:#2b6cb0}</style>' +
    '<h3>Manual Update Required (v' + serverVersion + ')</h3>' +
    '<p>Auto-update needs the Apps Script API enabled. To update manually:</p>' +
    '<ol>' +
    '<li>Open <b>Extensions &gt; Apps Script</b></li>' +
    '<li>Select all code (Ctrl+A)</li>' +
    '<li>Delete it</li>' +
    '<li><a href="https://www.whereparcel.com/downloads/Code.gs" target="_blank">Download new Code.gs</a> and paste</li>' +
    '<li>Press <b>Ctrl+S</b> to save</li>' +
    '<li>Reload the spreadsheet</li>' +
    '</ol>' +
    '<p style="color:#718096;font-size:11px">To enable auto-update: <a href="https://console.cloud.google.com/apis/library/script.googleapis.com" target="_blank">Enable Apps Script API</a> in your GCP project</p>'
  ).setWidth(420).setHeight(260);
  SpreadsheetApp.getUi().showModalDialog(html, 'Update Available');
}


// ═══════════════════════════════════════
// Settings Helpers
// ═══════════════════════════════════════
function getSetting(cellAddr) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName(SETTINGS_SHEET);
  if (!ws) throw new Error('Settings sheet not found. Please run Setup All first.');
  return String(ws.getRange(cellAddr).getValue()).trim();
}

function colLetterToNum(letter) {
  const s = letter.toUpperCase().trim();
  if (!s) return 0;
  let result = 0;
  for (let i = 0; i < s.length; i++) {
    const ch = s.charCodeAt(i);
    if (ch < 65 || ch > 90) return 0;
    result = result * 26 + (ch - 64);
  }
  return result;
}

function statusToText(status) {
  const map = {
    pending: 'Pending',
    in_transit: 'In Transit',
    out_for_delivery: 'Out for Delivery',
    delivered: 'Delivered',
    failed: 'Failed',
    returned: 'Returned',
    cancelled: 'Cancelled',
    unknown: 'Unknown',
  };
  return map[String(status).toLowerCase()] || status;
}


// ═══════════════════════════════════════
// Carrier Code Normalization
// ═══════════════════════════════════════
function normalizeCarrierCode(raw) {
  const s = String(raw).trim();

  // Already has dot notation
  if (s.includes('.')) return s;

  const lower = s.toLowerCase();
  const aliases = {
    // Korea
    'cj': 'kr.cj', 'cj logistics': 'kr.cj',
    'lotte': 'kr.lotte', 'lotte logistics': 'kr.lotte',
    'hanjin': 'kr.hanjin', 'logen': 'kr.logen',
    'kdexp': 'kr.kdexp', 'kyungdong': 'kr.kdexp',
    'daesin': 'kr.daesin', 'cvsnet': 'kr.cvsnet',
    'epost': 'kr.epost', 'chunil': 'kr.chunil', 'honam': 'kr.honam',
    // Korean names
    'cj대한통운': 'kr.cj', '대한통운': 'kr.cj', 'cj택배': 'kr.cj',
    '롯데택배': 'kr.lotte', '롯데': 'kr.lotte',
    '한진택배': 'kr.hanjin', '한진': 'kr.hanjin',
    '로젠택배': 'kr.logen', '로젠': 'kr.logen',
    '우체국택배': 'kr.post', '우체국': 'kr.post',
    '경동택배': 'kr.kdexp', '경동': 'kr.kdexp',
    '대신택배': 'kr.daesin', '대신': 'kr.daesin',
    '편의점택배': 'kr.cvsnet',
    '일양로지스': 'kr.ilyanglogis', '일양': 'kr.ilyanglogis',
    '천일택배': 'kr.chunil', '천일': 'kr.chunil',
    '호남택배': 'kr.honam', '호남': 'kr.honam',
    '건영택배': 'kr.kunyoung', '건영': 'kr.kunyoung',
    '합동택배': 'kr.hdexp', '합동': 'kr.hdexp',
    '홈픽택배': 'kr.homepick', '홈픽': 'kr.homepick',
    // US
    'ups': 'us.ups', 'usps': 'us.usps', 'fedex': 'us.fedex',
    'amazon': 'us.amazon', 'amazon logistics': 'us.amazon',
    'ontrac': 'us.ontrac', 'lasership': 'us.lasership',
    // Japan
    'yamato': 'jp.yamato', 'sagawa': 'jp.sagawa', 'japan post': 'jp.post',
    // International
    'dhl': 'intl.dhl', 'dhl express': 'intl.dhl',
    'tnt': 'intl.tnt', 'aramex': 'intl.aramex',
    'ems': 'intl.ems', 'cainiao': 'intl.cainiao', 'gls': 'intl.gls',
    // UK
    'royal mail': 'gb.royalmail', 'royalmail': 'gb.royalmail',
    'evri': 'gb.evri', 'yodel': 'gb.yodel', 'parcelforce': 'gb.parcelforce',
    // Germany
    'hermes': 'de.hermes', 'dpd': 'de.dpd', 'deutsche post': 'de.post',
    // Spain
    'correos': 'es.correos', 'seur': 'es.seur', 'mrw': 'es.mrw',
    'nacex': 'es.nacex', 'ctt express': 'es.cttExpress', 'cttexpress': 'es.cttExpress',
    // Canada
    'canada post': 'ca.post', 'purolator': 'ca.purolator',
    // Australia
    'australia post': 'au.post', 'auspost': 'au.post', 'startrack': 'au.startrack',
    // China
    'sf express': 'cn.sf', 'sf': 'cn.sf',
    // India
    'bluedart': 'in.bluedart', 'delhivery': 'in.delhivery',
    // Singapore
    'singpost': 'sg.post', 'ninja van': 'sg.ninjavan', 'ninjavan': 'sg.ninjavan',
    // Philippines
    'lbc': 'ph.lbc', 'lbc express': 'ph.lbc',
    'j&t': 'ph.jt', 'j&t express': 'ph.jt', 'jt express': 'ph.jt',
  };

  return aliases[lower] || s;
}


// ═══════════════════════════════════════
// HTTP Helpers
// ═══════════════════════════════════════
function httpPost(url, apiKey, secretKey, body) {
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'Authorization': 'Bearer ' + apiKey + ':' + secretKey },
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
  });
  const code = response.getResponseCode();
  const text = response.getContentText();
  try {
    return JSON.parse(text);
  } catch (e) {
    return { _httpError: true, _statusCode: code, _body: text.substring(0, 300) };
  }
}

function httpGet(url, apiKey, secretKey) {
  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: { 'Authorization': 'Bearer ' + apiKey + ':' + secretKey },
    muteHttpExceptions: true,
  });
  const code = response.getResponseCode();
  const text = response.getContentText();
  try {
    return JSON.parse(text);
  } catch (e) {
    return { _httpError: true, _statusCode: code, _body: text.substring(0, 300) };
  }
}


// ═══════════════════════════════════════
// History
// ═══════════════════════════════════════
function saveHistory(requestId, count, status) {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SETTINGS_SHEET);
  const lastRow = Math.max(ws.getLastRow() + 1, HISTORY_START_ROW);
  ws.getRange(lastRow, 1).setValue(requestId);
  ws.getRange(lastRow, 2).setValue(count);
  ws.getRange(lastRow, 3).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss'));
  ws.getRange(lastRow, 4).setValue(status);
}


// ═══════════════════════════════════════
// Register Tracking
// ═══════════════════════════════════════
function trackRegister() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Read settings
  const apiKey = getSetting(CELL.API_KEY);
  const secretKey = getSetting(CELL.SECRET_KEY);
  const targetSheetName = getSetting(CELL.TARGET_SHEET);

  if (!apiKey || !secretKey) {
    ui.alert('Missing Settings', 'Please enter your API Key and Secret Key in the Settings sheet.', ui.ButtonSet.OK);
    return;
  }

  const ws = ss.getSheetByName(targetSheetName);
  if (!ws) {
    ui.alert('Missing Sheet', "Sheet '" + targetSheetName + "' was not found.", ui.ButtonSet.OK);
    return;
  }

  const courierCol = colLetterToNum(getSetting(CELL.CARRIER_COL));
  const trackingCol = colLetterToNum(getSetting(CELL.TRACKING_COL));
  const resultCol = colLetterToNum(getSetting(CELL.RESULT_COL));

  if (!courierCol || !trackingCol || !resultCol) {
    ui.alert('Invalid Settings', 'Column settings are invalid. Please enter letters (A~Z).', ui.ButtonSet.OK);
    return;
  }

  const targetMode = getSetting(CELL.TARGET_MODE);
  const rangeMode = getSetting(CELL.RANGE_MODE);

  // Determine range
  let startRow, endRow;

  if (rangeMode === 'All') {
    startRow = 1;
    endRow = ws.getLastRow();
  } else if (rangeMode === 'Specific Range') {
    const sRow = getSetting(CELL.START_ROW);
    const eRow = getSetting(CELL.END_ROW);
    startRow = sRow ? parseInt(sRow) : 1;
    endRow = eRow ? parseInt(eRow) : ws.getLastRow();
  } else {
    // Row Range (Prompt) mode - ask user for row range
    const lastDataRow = ws.getLastRow();
    const response = ui.prompt(
      'Select Row Range',
      'Enter the row range to track (e.g. "2-10" or "5" for a single row).\n' +
      'Tracking sheet has data up to row ' + lastDataRow + '.',
      ui.ButtonSet.OK_CANCEL
    );
    if (response.getSelectedButton() !== ui.Button.OK) return;
    const input = response.getResponseText().trim();
    if (!input) {
      startRow = 1;
      endRow = lastDataRow;
    } else if (input.includes('-')) {
      const parts = input.split('-');
      startRow = parseInt(parts[0]) || 1;
      endRow = parseInt(parts[1]) || lastDataRow;
    } else {
      startRow = parseInt(input) || 1;
      endRow = startRow;
    }
  }

  if (endRow < startRow) {
    ui.alert('Info', 'No data found in the selected range.', ui.ButtonSet.OK);
    return;
  }

  // Collect items
  const items = [];
  let totalSkipped = 0;

  for (let r = startRow; r <= endRow; r++) {
    const carrier = normalizeCarrierCode(String(ws.getRange(r, courierCol).getValue()).trim());
    let tracking = ws.getRange(r, trackingCol).getValue();

    // Handle numeric values (prevent scientific notation)
    if (typeof tracking === 'number') {
      tracking = tracking.toFixed(0);
    }
    tracking = String(tracking).replace(/-/g, '').replace(/ /g, '').trim();

    if (carrier && tracking) {
      if (targetMode === 'Untracked Only') {
        const existing = String(ws.getRange(r, resultCol).getValue()).trim();
        if (existing) { totalSkipped++; continue; }
      }
      items.push({ carrier, trackingNumber: tracking, clientId: String(r) });
    }
  }

  if (items.length === 0) {
    let msg = 'No data to track.';
    if (totalSkipped > 0) msg += '\n(' + totalSkipped + ' rows skipped - already have results)';
    ui.alert('Info', msg, ui.ButtonSet.OK);
    return;
  }

  // Confirm
  let confirmMsg = items.length + ' items will be registered.';
  if (totalSkipped > 0) confirmMsg += '\n(' + totalSkipped + ' rows with results skipped)';
  confirmMsg += '\n\nThis may incur usage charges. Continue?';
  if (ui.alert('Register Tracking', confirmMsg, ui.ButtonSet.YES_NO) !== ui.Button.YES) return;

  // Register in batches
  const totalBatches = Math.ceil(items.length / BATCH_SIZE);
  let registeredCount = 0;

  for (let batch = 0; batch < totalBatches; batch++) {
    const batchItems = items.slice(batch * BATCH_SIZE, (batch + 1) * BATCH_SIZE);

    SpreadsheetApp.getActiveSpreadsheet().toast(
      'Registering batch ' + (batch + 1) + '/' + totalBatches + '...',
      'WhereParcel', 10);

    let response;
    try {
      response = httpPost(
        API_BASE_URL + '/v2/webhooks/register',
        apiKey, secretKey,
        { trackingItems: batchItems, recurring: false }
      );
    } catch (httpErr) {
      saveHistory('ERROR', batchItems.length, 'HTTP error: ' + httpErr.message);
      continue;
    }

    if (response._httpError) {
      saveHistory('ERROR', batchItems.length, 'HTTP ' + response._statusCode + ': ' + response._body);
      continue;
    }

    const requestId = (response.data && response.data.requestId) || response.requestId;
    if (requestId) {
      saveHistory(requestId, batchItems.length, 'Registered');
      registeredCount += batchItems.length;
    } else {
      const errMsg = response.message || response.error || JSON.stringify(response).substring(0, 200);
      saveHistory('ERROR', batchItems.length, errMsg);
    }

    // Delay between batches
    if (batch < totalBatches - 1) Utilities.sleep(1000);
  }

  ui.alert('Registration Complete',
    registeredCount + ' items registered!\n' +
    totalBatches + ' batch(es), requestId(s) saved in the Settings sheet.\n\n' +
    'Please wait a moment, then run "Fetch Results".',
    ui.ButtonSet.OK);
}


// ═══════════════════════════════════════
// Fetch Results
// ═══════════════════════════════════════
function trackResults() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const apiKey = getSetting(CELL.API_KEY);
  const secretKey = getSetting(CELL.SECRET_KEY);
  const targetSheetName = getSetting(CELL.TARGET_SHEET);

  if (!apiKey || !secretKey) {
    ui.alert('Missing Settings', 'Please enter your API Key and Secret Key.', ui.ButtonSet.OK);
    return;
  }

  const ws = ss.getSheetByName(targetSheetName);
  if (!ws) {
    ui.alert('Missing Sheet', "Sheet '" + targetSheetName + "' was not found.", ui.ButtonSet.OK);
    return;
  }

  const resultCol = colLetterToNum(getSetting(CELL.RESULT_COL));
  if (!resultCol) {
    ui.alert('Invalid Settings', 'Result start column is invalid.', ui.ButtonSet.OK);
    return;
  }

  const wsSetting = ss.getSheetByName(SETTINGS_SHEET);
  const lastHistRow = wsSetting.getLastRow();

  if (lastHistRow < HISTORY_START_ROW) {
    ui.alert('Info', 'No registration history found.\nPlease run "Register Tracking" first.', ui.ButtonSet.OK);
    return;
  }

  let totalUpdated = 0, totalPending = 0, totalErrors = 0;
  let processedBatches = 0;

  for (let histRow = HISTORY_START_ROW; histRow <= lastHistRow; histRow++) {
    const reqId = String(wsSetting.getRange(histRow, 1).getValue()).trim();
    const status = String(wsSetting.getRange(histRow, 4).getValue()).trim();

    if (!reqId || reqId === 'ERROR' || status === 'Done') continue;

    processedBatches++;
    SpreadsheetApp.getActiveSpreadsheet().toast('Fetching results... ' + reqId, 'WhereParcel', 10);

    let response;
    try {
      response = httpGet(
        API_BASE_URL + '/v2/webhooks/subscriptions/' + reqId,
        apiKey, secretKey
      );
    } catch (httpErr) {
      wsSetting.getRange(histRow, 4).setValue('Error: ' + httpErr.message);
      totalErrors++;
      continue;
    }

    if (response._httpError) {
      wsSetting.getRange(histRow, 4).setValue('HTTP ' + response._statusCode + ': ' + response._body);
      totalErrors++;
      continue;
    }

    // Check for error
    if (response.success === false || response.isSuccess === false) {
      const apiErr = response.message || response.error || 'Unknown error';
      wsSetting.getRange(histRow, 4).setValue('Error: ' + apiErr);
      totalErrors++;
      continue;
    }

    // Parse trackingItems (support both { trackingItems: [] } and { data: { trackingItems: [] } })
    const responseData = response.data || response;
    const trackingItems = responseData.trackingItems || responseData.items || [];
    if (trackingItems.length === 0) {
      wsSetting.getRange(histRow, 4).setValue('Partial: ' + JSON.stringify(response).substring(0, 200));
      continue;
    }

    let allDone = true;

    for (const item of trackingItems) {
      const clientId = item.clientId;
      if (!clientId || isNaN(clientId)) continue;

      const targetRow = parseInt(clientId);
      const currentStatus = item.status || '';

      // Check for error
      let itemError = '';
      if (typeof item.error === 'string') {
        itemError = item.error;
      } else if (item.error && item.error.message) {
        itemError = item.error.message;
      }

      const td = item.trackingData || null;

      if (itemError) {
        ws.getRange(targetRow, resultCol + RES.STATUS).setValue(currentStatus);
        ws.getRange(targetRow, resultCol + RES.ERROR).setValue(itemError)
          .setFontColor('#c80000');
        totalErrors++;

      } else if (td) {
        const deliveryStatus = td.deliveryStatus || currentStatus;

        ws.getRange(targetRow, resultCol + RES.STATUS).setValue(deliveryStatus);
        ws.getRange(targetRow, resultCol + RES.STATUS_TEXT).setValue(statusToText(deliveryStatus));
        ws.getRange(targetRow, resultCol + RES.IS_DELIVERED).setValue(deliveryStatus === 'delivered' ? 'Y' : 'N');

        // Receiver
        if (td.to && td.to.name) {
          ws.getRange(targetRow, resultCol + RES.RECEIVER).setValue(td.to.name);
        }

        // Date delivered (from first event if delivered)
        if (deliveryStatus === 'delivered' && td.events && td.events.length > 0) {
          ws.getRange(targetRow, resultCol + RES.DATE_DELIVERED).setValue(td.events[0].timestamp || '');
        }

        // Last progress
        ws.getRange(targetRow, resultCol + RES.DATE_LAST).setValue(td.lastUpdated || '');

        // Color coding
        const colors = {
          delivered: '#008000',
          in_transit: '#0064c8',
          out_for_delivery: '#0064c8',
          pending: '#b47800',
          failed: '#c80000',
          returned: '#c80000',
          cancelled: '#c80000',
        };
        const color = colors[deliveryStatus] || null;
        if (color) {
          ws.getRange(targetRow, resultCol + RES.STATUS).setFontColor(color);
          ws.getRange(targetRow, resultCol + RES.STATUS_TEXT).setFontColor(color);
        }

        totalUpdated++;

      } else {
        // No tracking data yet
        if (currentStatus) {
          ws.getRange(targetRow, resultCol + RES.STATUS).setValue(currentStatus);
        }
        allDone = false;
        totalPending++;
      }
    }

    wsSetting.getRange(histRow, 4).setValue(allDone ? 'Done' : 'Partial');
  }

  if (processedBatches === 0) {
    ui.alert('Info', 'No pending registrations to process.', ui.ButtonSet.OK);
  } else {
    let msg = 'Results fetched!\n\n';
    msg += '  Updated: ' + totalUpdated + ' items\n';
    if (totalPending > 0) msg += '  Pending: ' + totalPending + ' items (try again shortly)\n';
    if (totalErrors > 0) msg += '  Errors: ' + totalErrors + ' items\n';
    ui.alert('Fetch Results', msg, ui.ButtonSet.OK);
  }
}


// ═══════════════════════════════════════
// Clear Results
// ═══════════════════════════════════════
function clearResults() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const targetSheetName = getSetting(CELL.TARGET_SHEET);
  const ws = ss.getSheetByName(targetSheetName);
  if (!ws) {
    ui.alert('Missing Sheet', "Sheet '" + targetSheetName + "' was not found.", ui.ButtonSet.OK);
    return;
  }

  const resultCol = colLetterToNum(getSetting(CELL.RESULT_COL));
  const courierCol = colLetterToNum(getSetting(CELL.CARRIER_COL));
  if (!resultCol || !courierCol) {
    ui.alert('Invalid Settings', 'Column settings are invalid.', ui.ButtonSet.OK);
    return;
  }

  const lastRow = ws.getLastRow();
  if (lastRow < 1) {
    ui.alert('Info', 'No data found.', ui.ButtonSet.OK);
    return;
  }

  if (ui.alert('Clear Results', 'This will clear all result columns. Continue?', ui.ButtonSet.YES_NO) !== ui.Button.YES) return;

  ws.getRange(1, resultCol, lastRow, RES.COL_COUNT).clearContent();
  ws.getRange(1, resultCol, lastRow, RES.COL_COUNT).setFontColor(null);

  ui.alert('Done', 'Results have been cleared.', ui.ButtonSet.OK);
}


// ═══════════════════════════════════════
// Setup All
// ═══════════════════════════════════════
function setupAll() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // ── Delete old sheets (except Tracking) ──
  const sheetsToReset = [SETTINGS_SHEET, CARRIER_SHEET, INSTRUCTIONS_SHEET];
  for (const name of sheetsToReset) {
    const old = ss.getSheetByName(name);
    if (old) ss.deleteSheet(old);
  }

  // ── Settings Sheet ──
  const wsS = ss.insertSheet(SETTINGS_SHEET, 0);
  wsS.setColumnWidth(1, 140);
  wsS.setColumnWidth(2, 360);
  wsS.setColumnWidth(3, 100);
  wsS.setColumnWidth(4, 100);
  wsS.setColumnWidth(5, 260);
  wsS.setColumnWidth(7, 320);

  // Title
  wsS.getRange('A1').setValue('WhereParcel Tracking Settings').setFontSize(14).setFontWeight('bold').setFontColor('#2D3748');

  // API Settings
  wsS.getRange('A3').setValue('[ API Settings ]').setFontWeight('bold').setFontColor('#2B6CB0');
  wsS.getRange('A4').setValue('API Key').setFontWeight('bold').setHorizontalAlignment('right');
  wsS.getRange('B4').setValue('wp_test_public_demo_key_do_not_use_in_production').setBackground('#FFFFF0');
  wsS.getRange('E4').setValue('Demo key - get yours at whereparcel.com').setFontColor('#718096').setFontSize(9);
  wsS.getRange('A5').setValue('Secret Key').setFontWeight('bold').setHorizontalAlignment('right');
  wsS.getRange('B5').setValue('sk_test_public_demo_secret_do_not_use_in_production').setBackground('#FFFFF0');
  wsS.getRange('E5').setValue('Demo key - get yours at whereparcel.com').setFontColor('#718096').setFontSize(9);

  // Sheet Settings
  wsS.getRange('A7').setValue('[ Sheet Settings ]').setFontWeight('bold').setFontColor('#2B6CB0');
  const sheetSettings = [
    [8, 'Target Sheet', 'Tracking', 'Sheet name containing your data'],
    [9, 'Carrier Col', 'B', 'Column letter (A, B, C...)'],
    [10, 'Tracking # Col', 'C', 'Column letter (A, B, C...)'],
    [11, 'Result Start Col', 'D', 'Results fill from this column onward'],
  ];
  for (const [row, label, value, hint] of sheetSettings) {
    wsS.getRange(row, 1).setValue(label).setFontWeight('bold').setHorizontalAlignment('right');
    wsS.getRange(row, 2).setValue(value).setBackground('#FFFFF0');
    wsS.getRange(row, 5).setValue(hint).setFontColor('#718096').setFontSize(9);
  }

  // Execution Options
  wsS.getRange('A13').setValue('[ Execution Options ]').setFontWeight('bold').setFontColor('#2B6CB0');
  wsS.getRange('A14').setValue('Target').setFontWeight('bold').setHorizontalAlignment('right');
  wsS.getRange('B14').setValue('Track All').setBackground('#FFFFF0');
  const targetRule = SpreadsheetApp.newDataValidation().requireValueInList(['Track All', 'Untracked Only']).build();
  wsS.getRange('B14').setDataValidation(targetRule);
  wsS.getRange('A15').setValue('Range').setFontWeight('bold').setHorizontalAlignment('right');
  wsS.getRange('B15').setValue('Row Range (Prompt)').setBackground('#FFFFF0');
  const rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(['Row Range (Prompt)', 'All', 'Specific Range']).build();
  wsS.getRange('B15').setDataValidation(rangeRule);
  wsS.getRange('A16').setValue('Start Row').setFontWeight('bold').setHorizontalAlignment('right');
  wsS.getRange('B16').setBackground('#FFFFF0');
  wsS.getRange('E16').setValue('For Specific Range only (blank = first row)').setFontColor('#718096').setFontSize(9);
  wsS.getRange('A17').setValue('End Row').setFontWeight('bold').setHorizontalAlignment('right');
  wsS.getRange('B17').setBackground('#FFFFF0');
  wsS.getRange('E17').setValue('For Specific Range only (blank = last row)').setFontColor('#718096').setFontSize(9);

  // Macros section header
  wsS.getRange('A19').setValue('[ Run Macros ]').setFontWeight('bold').setFontColor('#2B6CB0');

  // Button rows: col A = label (styled), col B = checkbox
  const btnDefs = [
    [20, 'Register Tracking', '#2B6CB0'],
    [21, 'Fetch Results',     '#2B6CB0'],
    [22, 'Clear Results',     '#718096'],
  ];
  wsS.setColumnWidth(2, 40);
  for (const [row, label, color] of btnDefs) {
    wsS.getRange(row, 1)
      .setValue('  ▶  ' + label)
      .setBackground(color)
      .setFontColor('#FFFFFF')
      .setFontWeight('bold')
      .setFontSize(10);
    wsS.getRange(row, 2).insertCheckboxes();
  }

  // About section (column G)
  wsS.getRange('G19').setValue('[ About ]').setFontWeight('bold').setFontColor('#2B6CB0');
  wsS.getRange('G20').setValue('WhereParcel Tracking for Google Sheets').setFontColor('#718096').setFontSize(9);
  wsS.getRange('G21').setValue('Version: v' + MACRO_VERSION).setFontColor('#718096').setFontSize(9);
  wsS.getRange('G21:H21').merge(); // span so version text isn't clipped
  wsS.setColumnWidth(8, 40);

  // Reset Setup (rarely used — placed near About, not with main buttons)
  wsS.getRange('G22')
    .setValue('  ▶  Reset Setup')
    .setBackground('#718096')
    .setFontColor('#FFFFFF')
    .setFontWeight('bold')
    .setFontSize(10);
  wsS.getRange('H22').insertCheckboxes();

  // Note: first-time warning is shown via onOpen() (checks if trigger is installed),
  // not here — so it automatically disappears after Setup All runs.

  // History header
  wsS.getRange('A30').setValue('[ Registration History ]').setFontWeight('bold').setFontColor('#2B6CB0');
  const histHeaders = ['requestId', 'Count', 'Registered At', 'Status'];
  for (let i = 0; i < histHeaders.length; i++) {
    wsS.getRange(31, i + 1).setValue(histHeaders[i])
      .setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#2D3748')
      .setHorizontalAlignment('center');
  }

  // ── Tracking Sheet (create only if not exists) ──
  const trackingSheetName = wsS.getRange(CELL.TARGET_SHEET).getValue() || 'Tracking';
  let wsT = ss.getSheetByName(trackingSheetName);
  if (!wsT) {
    wsT = ss.insertSheet(trackingSheetName, 1);
    const headers = ['No', 'Carrier Code', 'Tracking Number', 'Status', 'Status Text', 'Is Delivered', 'Receiver', 'Product', 'Date Delivered', 'Last Progress', 'Error'];
    const widths = [50, 120, 180, 110, 130, 100, 110, 110, 160, 160, 220];
    for (let i = 0; i < headers.length; i++) {
      wsT.getRange(1, i + 1).setValue(headers[i])
        .setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#2D3748')
        .setHorizontalAlignment('center');
      wsT.setColumnWidth(i + 1, widths[i]);
    }
    wsT.setFrozenRows(1);

    // Sample data
    const samples = [
      [1, 'us.ups'], [2, 'us.usps'], [3, 'us.fedex'], [4, 'kr.cj'],
      [5, 'jp.yamato'], [6, 'de.dhl'], [7, 'gb.royalmail'], [8, 'intl.dhl'],
    ];
    for (let i = 0; i < samples.length; i++) {
      wsT.getRange(i + 2, 1).setValue(samples[i][0]).setHorizontalAlignment('center');
      wsT.getRange(i + 2, 2).setValue(samples[i][1]).setHorizontalAlignment('center');
    }
  }

  // ── Carrier Codes Sheet ──
  const wsC = ss.insertSheet(CARRIER_SHEET);
  wsC.setColumnWidth(1, 170);
  wsC.setColumnWidth(2, 270);
  wsC.setColumnWidth(3, 140);

  ['Carrier Code', 'Carrier Name', 'Country'].forEach((h, i) => {
    wsC.getRange(1, i + 1).setValue(h)
      .setFontWeight('bold').setFontColor('#FFFFFF').setBackground('#2D3748');
  });

  const carriers = [
    ['Australia (AU)', [['au.post','Australia Post'],['au.startrack','StarTrack'],['au.toll','Toll Group'],['au.aramex','Aramex Australia'],['au.couriersplease','CouriersPlease'],['au.sendle','Sendle'],['au.tnt','TNT Australia'],['au.hunter','Hunter Express'],['au.fastway','Fastway Australia']]],
    ['Canada (CA)', [['ca.post','Canada Post'],['ca.fedex','FedEx Canada'],['ca.purolator','Purolator'],['ca.ups','UPS Canada'],['ca.canpar','Canpar Express'],['ca.intelcom','Intelcom'],['ca.loomis','Loomis Express'],['ca.ics','ICS Courier'],['ca.dicom','Dicom'],['ca.gobolt','GoBolt']]],
    ['China (CN)', [['cn.sf','SF Express']]],
    ['Germany (DE)', [['de.dhl','DHL Germany'],['de.hermes','Hermes Germany'],['de.dpd','DPD Germany'],['de.gls','GLS Germany'],['de.ups','UPS Germany'],['de.post','Deutsche Post'],['de.tnt','TNT Germany'],['de.fedex','FedEx Germany'],['de.amazon','Amazon Logistics Germany'],['de.transoFlex','trans-o-flex'],['de.go','GO! Express & Logistics'],['de.ilg','ILG'],['de.nightstar','Night Star Express']]],
    ['Spain (ES)', [['es.correos','Correos'],['es.seur','SEUR'],['es.mrw','MRW'],['es.nacex','Nacex'],['es.gls','GLS Spain'],['es.dhl','DHL Spain'],['es.fedex','FedEx Spain'],['es.ups','UPS Spain'],['es.celeritas','Celeritas'],['es.cttExpress','CTT Express'],['es.tipsa','Tipsa'],['es.envialia','Envialia']]],
    ['United Kingdom (GB)', [['gb.royalmail','Royal Mail'],['gb.parcelforce','Parcelforce Worldwide'],['gb.dpd','DPD UK'],['gb.hermes','Hermes UK'],['gb.evri','Evri'],['gb.yodel','Yodel'],['gb.dhl','DHL UK'],['gb.ups','UPS UK'],['gb.fedex','FedEx UK'],['gb.tnt','TNT UK'],['gb.royalmail.special','Royal Mail Special Delivery'],['gb.amazon','Amazon Logistics UK'],['gb.collectplus','CollectPlus'],['gb.myhermes','myHermes']]],
    ['Hong Kong (HK)', [['hk.post','Hongkong Post'],['hk.sf','SF Express HK'],['hk.kerry','Kerry Express HK'],['hk.dhl','DHL Hong Kong'],['hk.lalamove','Lalamove HK'],['hk.sfintl','S.F. International']]],
    ['India (IN)', [['in.post','India Post'],['in.bluedart','BlueDart'],['in.delhivery','Delhivery'],['in.dtdc','DTDC'],['in.ecom','Ecom Express'],['in.ekart','Ekart (Flipkart)'],['in.xpressbees','XpressBees'],['in.professional','Professional Couriers'],['in.gati','Gati'],['in.shadowfax','Shadowfax']]],
    ['Ireland (IE)', [['ie.anpost','An Post'],['ie.dpd','DPD Ireland'],['ie.fastway','Fastway Ireland'],['ie.gls','GLS Ireland'],['ie.dhl','DHL Ireland'],['ie.ups','UPS Ireland']]],
    ['Italy (IT)', [['it.tnt','TNT Italy'],['it.brt','BRT (Bartolini)'],['it.poste','Poste Italiane'],['it.gls','GLS Italy'],['it.dhl','DHL Italy'],['it.sda','SDA Express'],['it.fedex','FedEx Italy'],['it.ups','UPS Italy'],['it.dpd','DPD Italy'],['it.nexive','Nexive']]],
    ['Japan (JP)', [['jp.yamato','Yamato Transport'],['jp.sagawa','Sagawa Express'],['jp.post','Japan Post'],['jp.nittsu','Nippon Express'],['jp.ems','EMS Japan'],['jp.epacket','ePacket'],['jp.seino','Seino Transportation'],['jp.fukuyama','Fukuyama Transporting'],['jp.tonami','Tonami Transportation'],['jp.seino.express','Seino Super Express'],['jp.kix','KIX Airport Express']]],
    ['South Korea (KR)', [['kr.post','Korea Post'],['kr.cj','CJ Logistics'],['kr.lotte','Lotte Global Logistics'],['kr.hanjin','Hanjin Express'],['kr.logen','Logen'],['kr.kdexp','Kyungdong Express'],['kr.daesin','Daesin Express'],['kr.cvsnet','CVSnet'],['kr.epost','ePost'],['kr.hdexp','Hadong Express'],['kr.homepick','Homepick'],['kr.ilyanglogis','Ilyang Logistics'],['kr.kunyoung','Kunyoung'],['kr.slx','SLX Express'],['kr.gsmnton','GS Postbox'],['kr.swgexp','Seongwon Global Cargo'],['kr.chunil','Chunil Express'],['kr.honam','Honam Express'],['kr.kgbls','KGB Express'],['kr.gigaepost','Giga Express'],['kr.wonpower','Wonpower Logistics']]],
    ['New Zealand (NZ)', [['nz.post','NZ Post'],['nz.courierpost','CourierPost'],['nz.aramex','Aramex NZ'],['nz.dhl','DHL New Zealand'],['nz.posthaste','Post Haste'],['nz.castle','Castle Parcels']]],
    ['Philippines (PH)', [['ph.lbc','LBC Express'],['ph.jt','J&T Express PH'],['ph.ninjavan','Ninja Van PH'],['ph.2go','2GO Express'],['ph.post','PHLPost'],['ph.flash','Flash Express PH'],['ph.grab','Grab Express PH']]],
    ['Singapore (SG)', [['sg.post','SingPost'],['sg.ninjavan','Ninja Van SG'],['sg.jt','J&T Express SG'],['sg.qxpress','Qxpress'],['sg.taqbin','Ta-Q-Bin SG'],['sg.dhl','DHL Singapore'],['sg.janio','Janio Asia']]],
    ['Turkey (TR)', [['tr.yurtici','Yurtici Kargo'],['tr.ptt','PTT (Turkey Post)'],['tr.aras','Aras Kargo'],['tr.mng','MNG Kargo'],['tr.surat','Surat Kargo'],['tr.trendyol','Trendyol Express'],['tr.hepjet','HepsiJet']]],
    ['South Africa (ZA)', [['za.courierguy','The Courier Guy'],['za.post','South African Post Office'],['za.ram','RAM Hand-to-Hand'],['za.aramex','Aramex South Africa'],['za.dawnwing','Dawn Wing'],['za.pargo','Pargo'],['za.dhl','DHL South Africa']]],
    ['United States (US)', [['us.usps','USPS'],['us.fedex','FedEx'],['us.ups','UPS'],['us.dhl','DHL'],['us.dhl.express','DHL Express'],['us.dhl.ecommerce','DHL eCommerce'],['us.ontrac','OnTrac'],['us.ca.ontrac','OnTrac (California)'],['us.lasership','LaserShip'],['us.ny.lasership','LaserShip (New York)'],['us.lso','Lone Star Overnight'],['us.tx.lso','Lone Star Overnight (Texas)'],['us.amazon','Amazon Logistics'],['us.eastern','Eastern Connection'],['us.gso','Golden State Overnight'],['us.courier','Courier Express'],['us.dynamex','Dynamex'],['us.pilot','Pilot Freight Services']]],
    ['International (INTL)', [['intl.dhl','DHL Express'],['intl.fedex','FedEx'],['intl.ups','UPS'],['intl.cainiao','Cainiao'],['intl.sf-express','SF Express International'],['intl.4px','4PX Express'],['intl.yunexpress','Yunexpress'],['intl.gls','GLS'],['intl.purolator','Purolator International'],['intl.kerry','Kerry Express International'],['intl.ems','EMS'],['intl.usps','USPS International'],['intl.postnl','PostNL International'],['intl.singapore-post','Singapore Post International'],['intl.china-post','China Post International'],['intl.korea-post','Korea Post International'],['intl.deutsche-post','Deutsche Post International'],['intl.tnt','TNT Express'],['intl.aramex','Aramex'],['intl.chronopost','Chronopost International'],['intl.yanwen','Yanwen'],['intl.sagawa','Sagawa Global'],['intl.bluedart','BlueDart'],['intl.auspost','Australia Post International'],['intl.royal-mail','Royal Mail International'],['intl.canada-post','Canada Post International'],['intl.la-poste','La Poste International'],['intl.swiss-post','Swiss Post International']]],
  ];

  let row = 2;
  for (const [section, list] of carriers) {
    const countryCode = section.split('(')[1].replace(')', '');
    wsC.getRange(row, 1, 1, 3).merge().setValue('-- ' + section + ' --')
      .setFontWeight('bold').setFontColor('#2B6CB0').setBackground('#EDF2F7');
    row++;
    for (const [code, name] of list) {
      wsC.getRange(row, 1).setValue(code);
      wsC.getRange(row, 2).setValue(name);
      wsC.getRange(row, 3).setValue(countryCode).setFontColor('#718096');
      row++;
    }
  }

  // ── Instructions Sheet ──
  const wsU = ss.insertSheet(INSTRUCTIONS_SHEET);
  wsU.setColumnWidth(1, 600);
  const instructions = [
    { text: '[ WhereParcel Tracking - Google Sheets Instructions ]', bold: true },
    {},
    { text: '1. Setup (first time only) — REQUIRED after copying this spreadsheet' },
    { text: '   - A warning banner appears at the top of the Settings sheet' },
    { text: '   - Go to WhereParcel menu > Setup All  ← use the menu (not the button)' },
    { text: '   - This creates Settings, Tracking, Carrier Codes sheets' },
    { text: '   - Installs triggers so checkbox buttons work; warning disappears automatically' },
    {},
    { text: '2. Enter API Keys' },
    { text: '   - Enter your API Key and Secret Key in the Settings sheet' },
    { text: '   - Demo keys are pre-filled for testing' },
    { text: '   - Get production keys at https://whereparcel.com' },
    {},
    { text: '3. Register Tracking' },
    { text: '   - Enter carrier codes and tracking numbers in the Tracking sheet' },
    { text: '   - WhereParcel menu > Register Tracking' },
    { text: '   - Items are batched automatically (max 100 per batch)' },
    {},
    { text: '4. Fetch Results' },
    { text: '   - Wait 5-10 seconds after registration' },
    { text: '   - WhereParcel menu > Fetch Results' },
    { text: '   - If results show as "Partial", wait and try again' },
    {},
    { text: '5. Clear Results' },
    { text: '   - WhereParcel menu > Clear Results' },
    {},
    { text: '6. Check for Updates' },
    { text: '   - WhereParcel menu > Check for Updates' },
    { text: '   - Auto-updates when Apps Script API is enabled in GCP' },
    { text: '   - Otherwise, shows manual update instructions' },
    {},
    { text: '[ Important Notes ]', bold: true },
    { text: '- Do not add or delete data rows between registration and fetching results' },
    { text: '- Usage charges may apply - avoid unnecessary repeated registrations' },
    { text: '- Carrier codes are listed in the "Carrier Codes" sheet' },
    { text: '- Type "ups" instead of "us.ups" - codes are auto-normalized' },
    {},
    { text: '[ Adding to Your Own Spreadsheet ]', bold: true },
    { text: '- Extensions > Apps Script' },
    { text: '- Paste the Code.gs content > Save' },
    { text: '- Reload the spreadsheet - "WhereParcel" menu appears' },
    { text: '- Run Setup All from the menu' },
    {},
    { text: '[ Demo Key Warning ]', bold: true },
    { text: '- The default keys are demo keys with limited usage' },
    { text: '- Get your production keys at https://whereparcel.com' },
  ];
  for (let i = 0; i < instructions.length; i++) {
    const line = instructions[i];
    if (!line.text) continue;
    const cell = wsU.getRange(i + 1, 1).setValue(line.text);
    if (line.bold) {
      cell.setFontWeight('bold').setFontSize(12).setFontColor('#2D3748');
    }
  }

  // Install trigger for checkbox buttons
  installTrigger();

  // Activate Settings sheet
  wsS.activate();

  SpreadsheetApp.getUi().alert('Setup Complete',
    'Settings, Tracking, Carrier Codes, and Instructions sheets have been created!\n\n' +
    'Use the "WhereParcel" menu at the top to register and fetch tracking results.',
    SpreadsheetApp.getUi().ButtonSet.OK);
}

// Embeds the first-time warning into the Settings sheet as spreadsheet data.
// Run this ONCE after setupAll() before sharing the template copy link.
// When users copy this sheet and run setupAll(), the sheet is rebuilt — warning gone.
function embedFirstTimeWarning() {
  const wsS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SETTINGS_SHEET);
  if (!wsS) {
    SpreadsheetApp.getUi().alert('Settings sheet not found. Run Setup All first.');
    return;
  }
  wsS.getRange('A2:F2').merge()
    .setValue('⚠  First time?  →  Run  WhereParcel > Setup All  from the menu above.  The buttons below will not work until you do.')
    .setBackground('#FFF3CD')
    .setFontColor('#7B341E')
    .setFontSize(11)
    .setFontWeight('bold')
    .setWrap(true)
    .setVerticalAlignment('middle')
    .setHorizontalAlignment('left')
    .setBorder(true, true, true, true, null, null, '#ED8936', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  wsS.setRowHeight(2, 44);
  SpreadsheetApp.getActiveSpreadsheet().toast('Warning banner added. Now share the copy link.', 'Template Ready', 5);
}