Kai Meredith

// From syncSquareToSheet()
// Logic: Batch processing with Idempotency & Staff Recovery

function syncSquareToSheet() {
  const props = PropertiesService.getScriptProperties();
  const nowIso = new Date().toISOString();
  // Resume from last sync cursor
  const beginIso = props.getProperty(SYNC_CURSOR_KEY) || isoDaysAgo_(30);

  const payments = fetchPaymentsUpdatedSince_(beginIso, nowIso);
  
  // Batch Fetch related Orders to minimize API calls (N+1 problem)
  const orderIds = unique_(payments.map(p => p.order_id));
  const ordersById = batchRetrieveOrders_(orderIds);
  
  // Heuristic: Pre-fetch booking info to resolve missing staff
  const bookingInfo = prefetchBookingInfo_(ordersById);

  payments.forEach(p => {
    // Transform raw API data into payroll row
    const row = buildProcessedRow_(
        p, ordersById[p.order_id], catalogInfo, staffById, 
        customersById, commissionData, bookingInfo
    );
    // Upsert logic (Update if exists, Append if new)
    if (paymentRowIndex[p.id]) {
      updates.push({row: paymentRowIndex[p.id], values: row});
    } else {
      appends.push(row);
    }
  });

  props.setProperty(SYNC_CURSOR_KEY, nowIso);
}

// From syncIdempotent()
// Logic: Batching requests to respect API limits & prevent duplicates

function syncIdempotent(sheetName, tableId, uniqueFieldName) {
  // 1. Fetch all existing Airtable records to build a Lookup Map
  const existingRecords = fetchAllAirtableRecords(tableId);
  const recordMap = new Map();
  existingRecords.forEach(r => recordMap.set(r.fields[uniqueFieldName], r.id));

  // 2. Diff Local Data vs Remote Data
  const {hdr, data} = read(sheetName);
  const toInsert = [];
  const toUpdate = [];

  data.forEach((row) => {
    const uniqueVal = row[uniqueColIndex];
    const fields = mapRowToFields(row);

    if (recordMap.has(uniqueVal)) {
      toUpdate.push({ id: recordMap.get(uniqueVal), fields: fields });
    } else {
      toInsert.push({ fields: fields });
    }
  });

  // 3. Execute Batched API Calls (Max 10 per request)
  while (toInsert.length > 0) {
    const batch = toInsert.splice(0, 10);
    airtablePOST(tableId, { records: batch });
    Utilities.sleep(250); // Rate limit backoff
  }
}

// From processLead()
// Logic: State machine for drip campaign execution

function processLead(rowIndex, weekStage) {
  // 1. Validate Data Integrity
  if (!hasRequiredFields(row) || row[IS_PAUSED_INDEX]) return;

  // 2. Construct Payload for Direct Mail API
  const payload = buildPayload(row);
  const webhookUrl = props.getProperty(weekStage + '_WEBHOOK');

  // 3. Execute External Action
  const response = sendToPostalytics(webhookUrl, payload, authHeader);

  if (response.getResponseCode() === 200) {
    // 4. Update State Machine
    const nextWeek = new Date(now.getTime() + (7 * 24 * 60 * 60 * 1000));
    const stageNum = parseInt(weekStage.replace('WEEK_', ''));
    
    // Update Stage, Progress Bar, and Next Trigger Date
    sheet.getRange(rowIndex, 2).setValue('Week ' + stageNum);
    sheet.getRange(rowIndex, 3).setValue(generateProgressBar(stageNum));
    sheet.getRange(rowIndex, 5).setValue(nextWeek);
  }
}

// Logic: Watch for Status Change -> Execute Action

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  // Only trigger on 'Pipeline Progress' column changes
  if (sheet.getName() === 'Application' && range.getColumn() === PIPELINE_COL) {
    const status = e.value;
    const row = range.getRow();
    const candidate = getCandidateData(row);

    if (status === 'Invite to Interview') {
       // Check Quiet Hours before sending SMS
       if (isQuietHours()) {
         scheduleMessageForMorning(candidate);
       } else {
         sendTwilioSMS(candidate.phone, getTemplate('INVITE_SMS'));
       }
    }
  }
}