/**
 * Apps Script handoff sample for a focused Google Sheets sync pass.
 * Sanitized example only; replace sheet names, columns, and endpoint details.
 */

const OPS_CONFIG = {
  sourceSheet: 'Incoming',
  targetSheet: 'Clean',
  statusSheet: 'Sync Status',
  uniqueKeyColumn: 'email',
};

function runFocusedSheetSync() {
  const ss = SpreadsheetApp.getActive();
  const source = ss.getSheetByName(OPS_CONFIG.sourceSheet);
  const target = ss.getSheetByName(OPS_CONFIG.targetSheet);
  const status = ss.getSheetByName(OPS_CONFIG.statusSheet);

  if (!source || !target || !status) {
    throw new Error('Missing one required sheet: Incoming, Clean, or Sync Status.');
  }

  const rows = readRows_(source);
  const existing = readExistingKeys_(target, OPS_CONFIG.uniqueKeyColumn);
  const cleaned = [];
  const rejected = [];

  for (const row of rows) {
    const email = normalizeEmail_(row.email);
    if (!email) {
      rejected.push({ reason: 'missing_email', row });
      continue;
    }

    if (existing.has(email)) {
      rejected.push({ reason: 'duplicate_email', row });
      continue;
    }

    cleaned.push([
      new Date(),
      email,
      String(row.name || '').trim(),
      String(row.source || 'manual').trim(),
    ]);
    existing.add(email);
  }

  if (cleaned.length) {
    target.getRange(target.getLastRow() + 1, 1, cleaned.length, cleaned[0].length).setValues(cleaned);
  }

  writeStatus_(status, {
    imported: cleaned.length,
    rejected: rejected.length,
    checkedAt: new Date(),
  });
}

function readRows_(sheet) {
  const values = sheet.getDataRange().getValues();
  const headers = values.shift().map((value) => String(value).trim().toLowerCase());
  return values
    .filter((row) => row.some((cell) => cell !== ''))
    .map((row) => Object.fromEntries(headers.map((key, index) => [key, row[index]])));
}

function readExistingKeys_(sheet, keyName) {
  const rows = readRows_(sheet);
  return new Set(rows.map((row) => normalizeEmail_(row[keyName])).filter(Boolean));
}

function normalizeEmail_(value) {
  const email = String(value || '').trim().toLowerCase();
  return /^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email) ? email : '';
}

function writeStatus_(sheet, result) {
  sheet.appendRow([
    result.checkedAt,
    result.imported,
    result.rejected,
    result.rejected ? 'review rejected rows before rerun' : 'ok',
  ]);
}