Set up Partner Momentum

/**
 * PARTNER MOMENTUM — Apps Script Backend
 * Sheet tabs required: "Pipeline" (active/working cards) and "Archive" (auto-archived Declined cards)
 * Both tabs must use the exact column order below.
 */

// ---- Column order (Pipeline and Archive tabs share this schema) ----
var COLUMNS = [
  "ID", "OrgName", "ContactName", "ContactRole", "Stage",
  "LogoURL", "AskType", "AskAmount", "LastContactDate", "NextStep",
  "Priority", "Tags", "LinkedIn", "NotesHistory", "AddedAt", "StageChangedAt"
];

var ARCHIVE_AFTER_DAYS = 30;
var DECLINED_STAGE = "Declined";
var ARCHIVE_SHEET_NAME = "Archive";
var PIPELINE_SHEET_NAME = "Pipeline";

function doGet(e) { return handleRequest(e); }

function doPost(e) {
  var p = JSON.parse(e.postData.contents);
  return handleAction(p);
}

function handleRequest(e) {
  var p = e.parameter;
  return handleAction(p);
}

function handleAction(p) {
  var out = {};

  if (p.action === "headers") {
    out = { headers: COLUMNS };

  } else if (p.action === "read") {
    runArchiveSweep(); // catch overdue Declined cards on every load
    var sheet = getSheet(PIPELINE_SHEET_NAME);
    var rows = sheet.getDataRange().getValues();
    out = { rows: rows };

  } else if (p.action === "read_archive") {
    var archiveSheet = getSheet(ARCHIVE_SHEET_NAME);
    var rows = archiveSheet.getDataRange().getValues();
    out = { rows: rows };

  } else if (p.action === "append") {
    var sheet = getSheet(PIPELINE_SHEET_NAME);
    var row = JSON.parse(p.row);
    // AddedAt and StageChangedAt stamped server-side
    row[14] = new Date().toISOString();
    row[15] = new Date().toISOString();
    sheet.appendRow(row);
    out = { success: true };

  } else if (p.action === "update") {
    var sheet = getSheet(PIPELINE_SHEET_NAME);
    sheet.getRange(parseInt(p.row), parseInt(p.col)).setValue(p.value);
    out = { success: true };

  } else if (p.action === "move_stage") {
    // Dedicated action for stage changes so StageChangedAt always updates,
    // which is what drives the 30-day archive timer.
    var sheet = getSheet(PIPELINE_SHEET_NAME);
    var rowNum = parseInt(p.row);
    var stageCol = COLUMNS.indexOf("Stage") + 1;
    var stageChangedCol = COLUMNS.indexOf("StageChangedAt") + 1;
    sheet.getRange(rowNum, stageCol).setValue(p.newStage);
    sheet.getRange(rowNum, stageChangedCol).setValue(new Date().toISOString());
    out = { success: true };

  } else if (p.action === "clear") {
    var sheet = getSheet(PIPELINE_SHEET_NAME);
    sheet.getRange(parseInt(p.row), 1, 1, COLUMNS.length).clearContent();
    out = { success: true };

  } else if (p.action === "archive_sweep") {
    // Manual trigger, in addition to the automatic sweep on "read"
    var moved = runArchiveSweep();
    out = { success: true, archived: moved };

  } else if (p.action === "ai_suggest") {
    out = handleAiSuggest(p);

  } else {
    out = { error: "unknown action" };
  }

  return ContentService
    .createTextOutput(JSON.stringify(out))
    .setMimeType(ContentService.MimeType.JSON);
}

function getSheet(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
    sheet.appendRow(COLUMNS);
  }
  return sheet;
}

/**
 * Moves any Pipeline row in "Declined" stage whose StageChangedAt is older
 * than ARCHIVE_AFTER_DAYS into the Archive tab, then deletes it from Pipeline.
 * Runs automatically on "read" and can also be called manually or on a
 * time-driven trigger (Triggers > Add Trigger > archive_sweep > time-driven, daily).
 */
function runArchiveSweep() {
  var pipeline = getSheet(PIPELINE_SHEET_NAME);
  var archive = getSheet(ARCHIVE_SHEET_NAME);
  var data = pipeline.getDataRange().getValues();
  var stageCol = COLUMNS.indexOf("Stage");
  var stageChangedCol = COLUMNS.indexOf("StageChangedAt");
  var cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - ARCHIVE_AFTER_DAYS);

  var rowsToDelete = [];
  var movedCount = 0;

  for (var i = 1; i < data.length; i++) { // skip header row
    var row = data[i];
    if (row[stageCol] === DECLINED_STAGE) {
      var changedAt = new Date(row[stageChangedCol]);
      if (!isNaN(changedAt.getTime()) && changedAt < cutoff) {
        archive.appendRow(row);
        rowsToDelete.push(i + 1); // +1 for 1-indexed sheet rows
        movedCount++;
      }
    }
  }

  // Delete from bottom up so row numbers don't shift mid-loop
  for (var j = rowsToDelete.length - 1; j >= 0; j--) {
    pipeline.deleteRow(rowsToDelete[j]);
  }

  return movedCount;
}

// OpenAI model used for AI suggestions. Verify this is still current at
// platform.openai.com/docs/models before relying on it long-term; model
// names are updated by OpenAI independent of this script.
var OPENAI_MODEL = "gpt-4o";

/**
 * Three AI features, selected by p.type:
 *   "stage_guidance" — suggest the next move given stage, notes, last contact
 *   "draft_outreach" — draft a partnership pitch or funding ask from notes
 *   "fit_score"      — score how well a prospect matches the org's mission/ask
 *
 * Provider is selected by p.apiProvider: "anthropic" (default) or "openai".
 * p.apiKey should hold the key for whichever provider is selected.
 */
function handleAiSuggest(p) {
  var apiKey = p.apiKey;
  var type = p.type;
  var provider = p.apiProvider || "anthropic";

  if (!apiKey || !type) {
    return { error: "Missing apiKey or type" };
  }

  var prompt = buildAiPrompt(type, p);
  if (!prompt) {
    return { error: "Unknown AI suggestion type" };
  }

  if (provider === "openai") {
    return callOpenAi(apiKey, prompt, type);
  } else if (provider === "anthropic") {
    return callAnthropic(apiKey, prompt, type);
  } else {
    return { error: "Unknown apiProvider: " + provider };
  }
}

function callAnthropic(apiKey, prompt, type) {
  var payload = JSON.stringify({
    model: "claude-sonnet-5",
    max_tokens: 8000,
    messages: [{ role: "user", content: prompt }]
  });

  var options = {
    method: "post",
    contentType: "application/json",
    headers: {
      "x-api-key": apiKey,
      "anthropic-version": "2023-06-01"
    },
    payload: payload,
    muteHttpExceptions: true
  };

  try {
    var resp = UrlFetchApp.fetch("https://api.anthropic.com/v1/messages", options);
    var result = JSON.parse(resp.getContentText());
    if (result.content && result.content[0]) {
      return { suggestion: result.content[0].text, type: type, provider: "anthropic" };
    } else {
      return { error: result.error ? result.error.message : "No response from AI" };
    }
  } catch (err) {
    return { error: err.message };
  }
}

function callOpenAi(apiKey, prompt, type) {
  var payload = JSON.stringify({
    model: OPENAI_MODEL,
    max_tokens: 8000,
    messages: [{ role: "user", content: prompt }]
  });

  var options = {
    method: "post",
    contentType: "application/json",
    headers: {
      "Authorization": "Bearer " + apiKey
    },
    payload: payload,
    muteHttpExceptions: true
  };

  try {
    var resp = UrlFetchApp.fetch("https://api.openai.com/v1/chat/completions", options);
    var result = JSON.parse(resp.getContentText());
    if (result.choices && result.choices[0] && result.choices[0].message) {
      return { suggestion: result.choices[0].message.content, type: type, provider: "openai" };
    } else {
      return { error: result.error ? result.error.message : "No response from AI" };
    }
  } catch (err) {
    return { error: err.message };
  }
}

function buildAiPrompt(type, p) {
  var orgName = p.orgName || "this prospect";
  var stage = p.stage || "unknown stage";
  var notes = p.notes || "no notes on file";
  var lastContact = p.lastContactDate || "unknown";
  var askType = p.askType || "unspecified";

  if (type === "stage_guidance") {
    return "You are helping a nonprofit/partnerships professional manage a donor or partner pipeline. " +
      "Prospect: " + orgName + ". Current stage: " + stage + ". Last contact: " + lastContact + ". " +
      "Notes: " + notes + ". " +
      "Suggest one specific, concrete next action to move this relationship forward, tailored to their current stage. " +
      "Keep it to 2-3 sentences, no preamble.";

  } else if (type === "draft_outreach") {
    return "You are drafting outreach for a nonprofit/partnerships professional. " +
      "Prospect: " + orgName + ". Ask type: " + askType + ". Notes on the relationship: " + notes + ". " +
      "Draft a short, warm, specific outreach message (email length, not a letter) that references what's " +
      "actually in the notes rather than generic language. No em dashes. No preamble, just the draft.";

  } else if (type === "fit_score") {
    return "You are evaluating donor/partner fit for a nonprofit or organization. " +
      "Prospect: " + orgName + ". Notes: " + notes + ". " +
      "Based only on the notes provided, give a fit score from 1-5 and a one-sentence rationale. " +
      "If the notes don't contain enough information to judge fit, say so plainly instead of guessing. " +
      "Format: 'Score: X/5 — rationale.'";
  }

  return null;
}