/**
 * SPX Pickup Route -> Google Sheet Web App
 * Target spreadsheet: Pickup_Route_Daily_Tracking_OLD
 *
 * One-time setup:
 * 1. Run setupSyncToken() and copy the token from the execution log.
 * 2. Deploy as Web app. Execute as: Me. Who has access: Anyone.
 * 3. Paste the /exec URL and token into the Daily Ops dashboard.
 */

const CONFIG = Object.freeze({
  spreadsheetId: '1f9sRtehjhH3iBlJv_1lKnHnebY4j5Nnf-5RZBoPmAa8',
  routeSheetId: 1691298984,
  trackingSheetId: 2002,
  routeSheet: 'Failed路线',
  trackingSheet: 'Failed单号',
  maxRouteRowsPerRequest: 500,
  maxTrackingRowsPerRequest: 1000,
});

function setupSyncToken() {
  const properties = PropertiesService.getScriptProperties();
  let token = properties.getProperty('SYNC_TOKEN');
  if (!token) {
    token = Utilities.getUuid().replace(/-/g, '') + Utilities.getUuid().replace(/-/g, '');
    properties.setProperty('SYNC_TOKEN', token);
  }
  console.log('把下面这串连接口令复制到 Daily Ops 看板：');
  console.log(token);
  return token;
}

function doGet() {
  return json_({ ok: true, service: 'SPX Pickup Route Web App', configured: Boolean(PropertiesService.getScriptProperties().getProperty('SYNC_TOKEN')) });
}

function doPost(e) {
  try {
    const payload = JSON.parse((e && e.postData && e.postData.contents) || '{}');
    verifyToken_(payload.syncToken);
    if (payload.action === 'ping') return ping_();
    if (payload.action !== 'syncPickupRoute') throw new Error('不支持的操作。');
    return syncPickupRoute_(payload);
  } catch (error) {
    return json_({ ok: false, error: error && error.message ? error.message : String(error) });
  }
}

function ping_() {
  const spreadsheet = SpreadsheetApp.openById(CONFIG.spreadsheetId);
  const routeSheet = requireSheet_(spreadsheet, CONFIG.routeSheetId, CONFIG.routeSheet);
  const trackingSheet = requireSheet_(spreadsheet, CONFIG.trackingSheetId, CONFIG.trackingSheet);
  return json_({
    ok: true,
    spreadsheet: spreadsheet.getName(),
    routeSheet: routeSheet.getName(),
    trackingSheet: trackingSheet.getName(),
    routeLastRow: lastDataRow_(routeSheet),
    trackingLastRow: lastDataRow_(trackingSheet),
  });
}

function syncPickupRoute_(payload) {
  const routeRows = validateRows_(payload.routeRows, false);
  const trackingRows = validateRows_(payload.trackingRows, true);
  const submissionId = text_(payload.submissionId, 128);
  if (!/^[a-f0-9]{64}$/.test(submissionId)) throw new Error('提交编号无效。');
  const cache = CacheService.getScriptCache();
  const cacheKey = 'PICKUP_DONE_' + submissionId;
  const cached = cache.get(cacheKey);
  if (cached) return cachedResult_(cached, routeRows, trackingRows);
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) throw new Error('已有另一批数据正在更新，请稍后重试。');
  try {
    const completed = cache.get(cacheKey);
    if (completed) return cachedResult_(completed, routeRows, trackingRows);
    const spreadsheet = SpreadsheetApp.openById(CONFIG.spreadsheetId);
    const routeSheet = requireSheet_(spreadsheet, CONFIG.routeSheetId, CONFIG.routeSheet);
    const trackingSheet = requireSheet_(spreadsheet, CONFIG.trackingSheetId, CONFIG.trackingSheet);

    const existingRoute = existingRouteKeys_(routeSheet, routeRows);
    const existingTracking = existingTrackingKeys_(trackingSheet, trackingRows);
    const newRoutes = uniqueNewRows_(routeRows, existingRoute, routeKey_);
    const newTracking = uniqueNewRows_(trackingRows, existingTracking, trackingKey_);

    if (newRoutes.length) appendRouteRows_(routeSheet, newRoutes);
    if (newTracking.length) appendTrackingRows_(trackingSheet, newTracking);
    const result = {
      ok: true,
      submissionId: submissionId,
      routeAdded: newRoutes.length,
      trackingAdded: newTracking.length,
      routeSkipped: routeRows.length - newRoutes.length,
      trackingSkipped: trackingRows.length - newTracking.length,
    };
    SpreadsheetApp.flush();
    try { cache.put(cacheKey, JSON.stringify(result), 21600); }
    catch (error) { /* Sheet-level deduplication remains the source of truth. */ }
    return json_(result);
  } finally {
    lock.releaseLock();
  }
}

function cachedResult_(stored, routeRows, trackingRows) {
  const previous = JSON.parse(stored);
  return json_({
    ok: true, submissionId: previous.submissionId, cached: true,
    routeAdded: 0, trackingAdded: 0,
    routeSkipped: routeRows.length, trackingSkipped: trackingRows.length,
  });
}

function verifyToken_(provided) {
  const expected = PropertiesService.getScriptProperties().getProperty('SYNC_TOKEN');
  if (!expected) throw new Error('Apps Script 尚未生成连接口令，请先运行 setupSyncToken()。');
  if (String(provided || '') !== expected) throw new Error('连接口令不正确。');
}

function validateRows_(value, tracking) {
  if (!Array.isArray(value)) throw new Error('上传记录格式不正确。');
  const max = tracking ? CONFIG.maxTrackingRowsPerRequest : CONFIG.maxRouteRowsPerRequest;
  if (value.length > max) throw new Error('单批记录超过上限。');
  return value.map(function (raw) {
    if (!raw || typeof raw !== 'object' || Array.isArray(raw)) throw new Error('上传记录格式不正确。');
    const result = text_(raw.result, 30);
    const date = text_(raw.date, 10);
    if (!/^20\d{2}-\d{2}-\d{2}$/.test(date)) throw new Error('记录日期格式不正确。');
    if (result !== 'FAILED PICKUP' && result !== 'NO PICKUP ATTEMPT') throw new Error('Pickup Result 不正确。');
    const row = {
      key: text_(raw.key, 900), date: date, sc: text_(raw.sc, 40), dsp: text_(raw.dsp, 120),
      serviceProvider: text_(raw.serviceProvider, 120), result: result, route: text_(raw.route, 180),
      driver: text_(raw.driver, 140), pointId: text_(raw.pointId, 120), store: text_(raw.store, 240),
      pickupAttemptTime: text_(raw.pickupAttemptTime, 40), businessHours: text_(raw.businessHours, 80),
      timeCheck: text_(raw.timeCheck, 40), failureReason: text_(raw.failureReason, 240),
      address: text_(raw.address, 500), tracking: text_(raw.tracking, 120),
      trackingStatus: text_(raw.trackingStatus, 40), affected: safeCount_(raw.affected),
    };
    if (!row.key || !row.sc || !row.dsp || !row.route || !row.pointId) throw new Error('记录缺少必要字段。');
    if (tracking && !row.tracking) throw new Error('单号记录缺少 Tracking Number。');
    return row;
  });
}

function existingRouteKeys_(sheet, submittedRows) {
  const last = lastDataRow_(sheet);
  const keys = new Set();
  if (last < 2) return keys;
  const dates = new Set(submittedRows.map(function (row) { return row.date; }));
  matchingDateRows_(sheet, last, dates, 7).forEach(function (row) {
    if (!row[0]) return;
    keys.add([dateKey_(row[0]), text_(row[1]), text_(row[3]), text_(row[4]), text_(row[6])].join('\u0001'));
  });
  return keys;
}

function existingTrackingKeys_(sheet, submittedRows) {
  const last = lastDataRow_(sheet);
  const keys = new Set();
  if (last < 2) return keys;
  const dates = new Set(submittedRows.map(function (row) { return row.date; }));
  matchingDateRows_(sheet, last, dates, 9).forEach(function (row) {
    if (!row[0]) return;
    keys.add([dateKey_(row[0]), text_(row[1]), text_(row[8])].join('\u0001'));
  });
  return keys;
}

function matchingDateRows_(sheet, last, dates, columns) {
  if (!dates.size || last < 2) return [];
  const dateCells = sheet.getRange(2, 1, last - 1, 1).getDisplayValues();
  const spans = [];
  let start = -1;
  for (let index = 0; index < dateCells.length; index += 1) {
    const matches = dates.has(dateKey_(dateCells[index][0]));
    if (matches && start < 0) start = index + 2;
    if ((!matches || index === dateCells.length - 1) && start >= 0) {
      const end = matches && index === dateCells.length - 1 ? index + 2 : index + 1;
      spans.push([start, end - start + 1]);
      start = -1;
    }
  }
  const rows = [];
  if (spans.length > 30) {
    sheet.getRange(2, 1, last - 1, columns).getValues().forEach(function (row) {
      if (dates.has(dateKey_(row[0]))) rows.push(row);
    });
    return rows;
  }
  spans.forEach(function (span) {
    sheet.getRange(span[0], 1, span[1], columns).getValues().forEach(function (row) { rows.push(row); });
  });
  return rows;
}

function uniqueNewRows_(rows, existing, keyBuilder) {
  const seen = new Set(existing);
  return rows.filter(function (row) {
    const key = keyBuilder(row);
    if (seen.has(key)) return false;
    seen.add(key);
    return true;
  });
}

function routeKey_(row) {
  return [row.date, row.sc, row.result, row.route, row.pointId].join('\u0001');
}

function trackingKey_(row) {
  return [row.date, row.sc, row.tracking].join('\u0001');
}

function appendRouteRows_(sheet, rows) {
  const startRow = lastDataRow_(sheet) + 1;
  ensureRows_(sheet, startRow + rows.length - 1);
  copyTemplate_(sheet, startRow, rows.length, 17);
  sheet.getRange(startRow, 1, rows.length, 12).setValues(rows.map(function (row) {
    return [toDate_(row.date), row.sc, row.dsp, row.result, row.route, row.driver, row.pointId, row.store,
      toDateTime_(row.pickupAttemptTime), row.businessHours, row.timeCheck, row.failureReason];
  }));
  sheet.getRange(startRow, 14, rows.length, 4).setValues(rows.map(function (row) {
    return [row.affected, row.address, row.serviceProvider, ''];
  }));
}

function appendTrackingRows_(sheet, rows) {
  const startRow = lastDataRow_(sheet) + 1;
  ensureRows_(sheet, startRow + rows.length - 1);
  copyTemplate_(sheet, startRow, rows.length, 18);
  sheet.getRange(startRow, 1, rows.length, 13).setValues(rows.map(function (row) {
    return [toDate_(row.date), row.sc, row.dsp, row.result, row.route, row.driver, row.pointId, row.store,
      row.tracking, toDateTime_(row.pickupAttemptTime), row.businessHours, row.timeCheck, row.failureReason];
  }));
  sheet.getRange(startRow, 15, rows.length, 4).setValues(rows.map(function (row) {
    return [row.address, row.trackingStatus, row.serviceProvider, ''];
  }));
}

function copyTemplate_(sheet, startRow, count, columns) {
  const source = sheet.getRange(2, 1, 1, columns);
  const target = sheet.getRange(startRow, 1, count, columns);
  source.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  source.copyTo(target, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
}

function lastDataRow_(sheet) {
  return Math.max(1, sheet.getLastRow());
}

function ensureRows_(sheet, requiredLastRow) {
  if (requiredLastRow > sheet.getMaxRows()) sheet.insertRowsAfter(sheet.getMaxRows(), requiredLastRow - sheet.getMaxRows());
}

function requireSheet_(spreadsheet, sheetId, name) {
  const sheets = spreadsheet.getSheets();
  for (let index = 0; index < sheets.length; index += 1) {
    if (sheets[index].getSheetId() === Number(sheetId)) return sheets[index];
  }
  const normalizedName = String(name || '').normalize('NFKC').trim();
  for (let index = 0; index < sheets.length; index += 1) {
    if (sheets[index].getName().normalize('NFKC').trim() === normalizedName) return sheets[index];
  }
  throw new Error('找不到工作表：' + name + '（Sheet ID: ' + sheetId + '）');
}

function toDate_(value) {
  const parts = String(value || '').match(/^(20\d{2})-(\d{2})-(\d{2})$/);
  return parts ? new Date(Number(parts[1]), Number(parts[2]) - 1, Number(parts[3]), 12, 0, 0) : '';
}

function toDateTime_(value) {
  if (!value) return '';
  const parts = String(value).match(/^(20\d{2})-(\d{2})-(\d{2})[ T](\d{2}):(\d{2})(?::(\d{2}))?/);
  return parts ? new Date(Number(parts[1]), Number(parts[2]) - 1, Number(parts[3]), Number(parts[4]), Number(parts[5]), Number(parts[6] || 0)) : value;
}

function dateKey_(value) {
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime())) {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  }
  const text = String(value || '').trim();
  let match = text.match(/^(20\d{2})-(\d{1,2})-(\d{1,2})$/);
  if (match) return [match[1], String(match[2]).padStart(2, '0'), String(match[3]).padStart(2, '0')].join('-');
  match = text.match(/^(\d{1,2})\/(\d{1,2})\/(20\d{2})$/);
  if (match) return [match[3], String(match[1]).padStart(2, '0'), String(match[2]).padStart(2, '0')].join('-');
  return text;
}

function text_(value, max) {
  if (value == null) return '';
  const text = String(value).trim();
  return max ? text.slice(0, max) : text;
}

function safeCount_(value) {
  const number = Number(value);
  return Number.isSafeInteger(number) && number >= 0 ? number : 0;
}

function json_(value) {
  return ContentService.createTextOutput(JSON.stringify(value)).setMimeType(ContentService.MimeType.JSON);
}