// ============================================================
// WEB APP — Spark Dashboard API
// Outreach Tracker column map:
// 1=Company, 2=Prospect, 3=Title, 4=Email, 5=Phone
// 6=FollowUpDate, 7=Status, 8=Priority
// 9=Msg1, 10=SentDate1, 11=Done1, 12=Outcome1
// 13=Msg2, 14=SentDate2, 15=Done2, 16=Outcome2
// 17=Msg3, 18=SentDate3, 19=Done3, 20=Outcome3
// 21=Msg4, 22=SentDate4, 23=Done4, 24=Outcome4
// 25=EmailMsg, 26=SentDate5, 27=Done5, 28=Outcome5
// 29=Notes
// ============================================================
var WA_SHEET_ID   = "1lwURFOdgGZAgaMs8ATAqZknzizX_BQqdcOv2-9HMNCo";
var WA_API_KEY    = "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";
var WA_SERPER     = "2773649c4bb0415d0172a52bffbbdb27fb05b09c";
var WA_GEMINI     = "https://openrouter.ai/api/v1/chat/completions";
var WA_SERPER_URL = "https://google.serper.dev/search";

var WA_MODELS = [
  "google/gemini-2.5-flash",
  "google/gemini-2.0-flash-001",
  "google/gemini-2.0-flash-lite-001"
];

function doGet(e) {
  var action = e && e.parameter && e.parameter.action ? e.parameter.action : "ping";
  var response;
  try {
    if      (action==="ping")             response={status:"ok",message:"Spark API running"};
    else if (action==="all")              response=getAllData();
    else if (action==="dashboard")        response=getDashboardData();
    else if (action==="companies")        response=getCompaniesData();
    else if (action==="company")          response=getCompanyDetail(e.parameter.name||"");
    else if (action==="pipeline")         response=getPipelineData();
    else if (action==="activity")         response=getActivityData();
    else if (action==="tasks")            response=getTasksData();
    else if (action==="analytics")        response=getAnalyticsData();
    else if (action==="markDone")         response=markProspectDone({prospectName:e.parameter.prospectName||"",companyName:e.parameter.companyName||"",step:e.parameter.step||"1"});
    else if (action==="saveOutcome")      response=saveOutcome({prospectName:e.parameter.prospectName||"",companyName:e.parameter.companyName||"",step:e.parameter.step||"1",outcome:e.parameter.outcome||""});
    else if (action==="saveNotes")        response=saveNotes({prospectName:e.parameter.prospectName||"",companyName:e.parameter.companyName||"",notes:e.parameter.notes||""});
    else if (action==="scheduleFollowUp") response=scheduleFollowUp({prospectName:e.parameter.prospectName||"",companyName:e.parameter.companyName||"",date:e.parameter.date||"",type:e.parameter.type||"Follow Up"});
    else if (action==="closeProspect")    response=closeProspect({prospectName:e.parameter.prospectName||"",companyName:e.parameter.companyName||""});
    else if (action==="uploadCompany")    response=processCompanyImageUrl(e.parameter.imageUrl||"");
    else if (action==="uploadProspect")   response=processProspectImageUrl(e.parameter.imageUrl||"");
    else                                  response={error:"Unknown action: "+action};
  } catch(err) { response={error:err.message}; }
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}

// ============================================================
// ALL DATA — single call returns everything for fast frontend
// ============================================================
function getAllData() {
  // Read sheets once and pass to all functions
  var ss = SpreadsheetApp.openById(WA_SHEET_ID);
  var rs = ss.getSheets()[0];
  var os = ss.getSheetByName("Outreach Tracker");
  var as = ss.getSheetByName("Activity Report");
  var rd = rs.getDataRange().getValues();
  var od = os ? os.getDataRange().getValues() : [];
  var ad = as ? as.getDataRange().getValues() : [];

  return {
    dashboard:  buildDashboard(rd, od, ad),
    companies:  buildCompanies(rd, od),
    pipeline:   buildPipeline(od),
    tasks:      buildTasks(od),
    analytics:  buildAnalytics(rd, od, ad),
    activity:   buildActivity(ad)
  };
}

// ============================================================
// LOCAL HELPERS
// ============================================================
function waClean(text) {
  if (!text) return "";
  var bad=["sorry","still gathering","come back later","account iq","gathering data","please come back"];
  var lower=String(text).toLowerCase().replace(/[\u2018\u2019]/g,"'");
  for(var i=0;i<bad.length;i++){if(lower.indexOf(bad[i])>-1)return "";}
  if(text.trim().length<30)return "";
  return text;
}
function waSap(v){return String(v||"").trim()==="Yes"?"Yes":"No";}
function waNorm(name){
  if(!name)return "";
  return String(name).toLowerCase().replace(/[|،,\-–—]/g," ").replace(/\s+/g," ").trim();
}
function waWorkDays(startDate,days){
  var date=new Date(startDate);if(isNaN(date.getTime()))date=new Date();
  var added=0;while(added<days){date.setDate(date.getDate()+1);var d=date.getDay();if(d!==5&&d!==6)added++;}
  return date;
}
function waParseDate(v){
  if(!v&&v!==0)return null;
  if(v instanceof Date)return isNaN(v.getTime())?null:v;
  if(typeof v==="number"){var ms=(v-25569)*86400*1000;var d=new Date(ms);return isNaN(d.getTime())?null:d;}
  if(typeof v==="string"&&v.trim()!==""){var p=new Date(v);return isNaN(p.getTime())?null:p;}
  return null;
}
function waFindProspectRow(sheet,companyName,prospectName){
  var data=sheet.getDataRange().getValues();
  for(var i=1;i<data.length;i++){if(waNorm(data[i][0])===companyName&&String(data[i][1]).trim().toLowerCase()===prospectName)return i+1;}
  for(var j=1;j<data.length;j++){if(String(data[j][1]).trim().toLowerCase()===prospectName)return j+1;}
  return -1;
}
function waFindCompanyRow(sheet,companyName){
  if(!companyName)return 0;
  var lastRow=sheet.getLastRow();var target=waNorm(companyName);
  var tw=target.split(" ").filter(function(w){return w.length>2;});
  for(var i=2;i<=lastRow;i++){
    var name=sheet.getRange(i,1).getValue();if(!name)continue;
    var cell=waNorm(name);
    if(cell===target)return i;
    if(cell.indexOf(target)>-1||target.indexOf(cell)>-1)return i;
    var cw=cell.split(" ").filter(function(w){return w.length>2;});
    if(tw.filter(function(w){return cw.indexOf(w)>-1;}).length>=2)return i;
  }
  return 0;
}
function waCleanJson(raw){
  var c=raw.replace(/^```json\s*/i,"").replace(/^```\s*/i,"").replace(/```\s*$/i,"").trim();
  var r="",inS=false,esc=false;
  for(var i=0;i<c.length;i++){
    var ch=c[i];
    if(esc){r+=ch;esc=false;continue;}
    if(ch==="\\"){esc=true;r+=ch;continue;}
    if(ch==='"'){inS=!inS;r+=ch;continue;}
    if(inS){var code=ch.charCodeAt(0);if(code<0x20){r+=(code===0x0A||code===0x0D||code===0x09)?" ":"";}else r+=ch;}
    else r+=ch;
  }
  return r;
}
function waCleanPainPoints(pp){
  if(!pp)return "Not found";
  var c=waClean(pp);if(!c)return "Not found";
  var junk=["pain point analysis","end-user category","end user category","automotive","recycled lead","no specific","no pain points","10.3","figure","table","chapter","market research","market analysis"];
  var lower=c.toLowerCase();
  if(junk.some(function(j){return lower.indexOf(j)>-1;})||c.trim().length<40)return "Not found";
  return c;
}
function waCall(payload){
  payload.models=WA_MODELS;payload.route="fallback";
  try{
    var res=UrlFetchApp.fetch(WA_GEMINI,{method:"post",contentType:"application/json",headers:{"Authorization":"Bearer "+WA_API_KEY,"HTTP-Referer":"https://google.com"},payload:JSON.stringify(payload),muteHttpExceptions:true});
    if(res.getResponseCode()!==200){Logger.log("waCall error: "+res.getContentText());return null;}
    var json=JSON.parse(res.getContentText());
    var content=json.choices[0].message.content;
    return JSON.parse(waCleanJson(content));
  }catch(e){Logger.log("waCall failed: "+e.message);return null;}
}

// ============================================================
// GEMINI VISION
// ============================================================
function waExtractCompany(imageData,mimeType){
  var prompt='Extract data from this LinkedIn screenshot. Return ONLY a JSON object with these exact keys:\n- "company_name": string\n- "headcount": string\n- "headquarter": string\n- "industry": string\n- "strategic_priorities": string\n- "business_challenges": string\n\nNo arrays. No nested objects. All values plain strings. No markdown.';
  return waCall({messages:[{role:"user",content:[{type:"text",text:prompt},{type:"image_url",image_url:{url:"data:"+mimeType+";base64,"+imageData}}]}]});
}
function waExtractProspect(imageData,mimeType){
  var prompt="Extract the following fields from this LinkedIn or Sales Navigator prospect screenshot. Return ONLY a valid JSON object with these exact keys: name, title, company_name, email, phone, profile_summary. For phone: ONLY return a phone number if explicitly visible as digits. If not visible return empty string. Do NOT guess. If a field is not visible use empty string. No markdown, only JSON.";
  return waCall({messages:[{role:"user",content:[{type:"text",text:prompt},{type:"image_url",image_url:{url:"data:"+mimeType+";base64,"+imageData}}]}]});
}

// ============================================================
// WEB RESEARCH
// ============================================================
function waSearch(query){
  var res=UrlFetchApp.fetch(WA_SERPER_URL,{method:"post",contentType:"application/json",headers:{"X-API-KEY":WA_SERPER},payload:JSON.stringify({q:query,num:5}),muteHttpExceptions:true});
  if(res.getResponseCode()!==200)return [];
  var json=JSON.parse(res.getContentText());
  return (json.organic||[]).map(function(r){return{title:r.title||"",snippet:r.snippet||"",link:r.link||""};});
}
function waResearch(companyName,needStrategic,needChallenges,needBasicInfo){
  var queries=[
    '"'+companyName+'" SAP S4HANA OR SuccessFactors OR "SAP ERP" OR "SAP Ariba" OR site:partner-finder.sap.com',
    '"'+companyName+'" business challenges strategic priorities pain points headcount headquarters 2024 OR 2025'
  ];
  var allResults=[];
  for(var i=0;i<queries.length;i++){var r=waSearch(queries[i]);if(r&&r.length>0)allResults=allResults.concat(r);Utilities.sleep(400);}
  if(allResults.length===0)return{company_overview:"No research found",pain_points:"Not found",sap_partner:"No",sap_user:"No",sap_solutions:"",partner_name:"",source_links:"researched",strategic_priorities:"",business_challenges:"",headcount:"",headquarter:"",industry:""};
  var resultsText=allResults.map(function(r,i){return "["+(i+1)+"] Title: "+r.title+"\nSnippet: "+r.snippet+"\nURL: "+r.link;}).join("\n\n");
  var extraKeys=(needStrategic||needChallenges)?'\n- "strategic_priorities": Key strategic priorities separated by semicolons. Empty if not found.\n- "business_challenges": Key business challenges separated by semicolons. Empty if not found.':'\n- "strategic_priorities": ""\n- "business_challenges": ""';
  var basicKeys=needBasicInfo?'\n- "headcount": Approximate employees. Empty if not found.\n- "headquarter": City and country. Empty if not found.\n- "industry": Primary industry. Empty if not found.':'\n- "headcount": ""\n- "headquarter": ""\n- "industry": ""';
  var prompt='You are a B2B research analyst. Analyze these search results about "'+companyName+'" and return ONLY a valid JSON object:\n- "company_overview": 2-3 sentence summary.\n- "pain_points": Specific business challenges. Write "Not found" if nothing found. Never copy LinkedIn placeholders.\n- "sap_partner": MUST be "Yes" or "No" only.\n- "sap_user": MUST be "Yes" or "No" only.\n- "sap_solutions": Comma-separated SAP products. Empty if none.\n- "partner_name": SAP partner name. Empty if not.\n- "source_links": Trusted URLs. Write "researched" if none.'+extraKeys+basicKeys+'\n\nRules: sap_partner and sap_user MUST only be "Yes" or "No". Be conservative. Never invent. No markdown.\n\nSearch Results:\n'+resultsText;
  return waCall({messages:[{role:"user",content:prompt}],response_format:{type:"json_object"}});
}

// ============================================================
// GENERATE MESSAGES
// ============================================================
function waMessages(companyName,prospect,companyOverview,painPoints,sapSolutions){
  var fn=prospect.name?prospect.name.split(" ")[0]:"there";
  var prompt="Generate 5 personalized outreach messages for a LinkedIn prospect. Return ONLY a valid JSON object with keys: message1, message2, message3, message4, email_message.\n\nPROSPECT INFO:\nFirst Name: "+fn+"\nFull Name: "+(prospect.name||"")+"\nTitle: "+(prospect.title||"")+"\nProfile Summary: "+(prospect.profile_summary||"")+"\n\nCOMPANY INFO:\nCompany: "+(companyName||"")+"\nOverview: "+(companyOverview||"")+"\nPain Points: "+(painPoints||"")+"\nSAP Solutions Used: "+(sapSolutions||"")+"\n\nABOUT BLUEPRINT TECHNOLOGIES:\nBlueprint Technologies is an award-winning SAP Cloud Transformation partner with 15+ years experience, 300+ SAP consultants, presence in India, Dubai, KSA, USA and Canada, serving 120+ global customers. We specialize in S/4HANA migration, ECC to S/4HANA cloud, SAP SuccessFactors HCM, SAP BTP, and iRPA automation. Our BDC (Blueprint Digital Core) is our flagship S/4HANA accelerator that delivers 40% faster go-live.\n\nmessage1 — WARM OPENER (~100 words): Start with 'Hi "+fn+", thanks for connecting!' Then one genuine compliment, one Blueprint intro sentence, one pain point reference. End with soft CTA.\nmessage2 — BDC FOCUS (~100 words): S/4HANA insight, explain BDC, 2 key benefits. End with call invite.\nmessage3 — AI ON SAP BTP (~100 words): AI insight, Blueprint AI solutions BRiGHT and BRiDGE, 2 concrete benefits. End with call invite.\nmessage4 — CASUAL FOLLOW UP (~70 words): Light check-in, zero pressure.\nemail_message — PROFESSIONAL EMAIL (~200 words): Subject line first, 4 paragraphs.\n\nRULES: Address by first name "+fn+". Never use 'I hope this finds you well'. Return ONLY the JSON object.";
  return waCall({messages:[{role:"user",content:prompt}]});
}

// ============================================================
// WRITE RESEARCH TO SHEET
// ============================================================
function waWriteResearch(sheet,lastRow,research,mS,mC,mH,mHQ,mI){
  var pp=waCleanPainPoints(research.pain_points||"");
  sheet.getRange(lastRow,7).setValue(research.company_overview||"");
  sheet.getRange(lastRow,8).setValue(pp);
  sheet.getRange(lastRow,9).setValue(waSap(research.sap_partner));
  sheet.getRange(lastRow,10).setValue(waSap(research.sap_user));
  sheet.getRange(lastRow,11).setValue(research.sap_solutions||"");
  sheet.getRange(lastRow,12).setValue(research.partner_name||"");
  sheet.getRange(lastRow,13).setValue(research.source_links||"researched");
  if(mS&&research.strategic_priorities)sheet.getRange(lastRow,5).setValue(waClean(research.strategic_priorities));
  if(mC&&research.business_challenges) sheet.getRange(lastRow,6).setValue(waClean(research.business_challenges));
  if(mH&&research.headcount)           sheet.getRange(lastRow,2).setValue(research.headcount);
  if(mHQ&&research.headquarter)        sheet.getRange(lastRow,3).setValue(research.headquarter);
  if(mI&&research.industry)            sheet.getRange(lastRow,4).setValue(research.industry);
  return pp;
}

// ============================================================
// UPLOAD COMPANY
// ============================================================
function processCompanyImageUrl(imageUrl){
  try{
    if(!imageUrl)return{error:"No image URL provided"};
    var res=UrlFetchApp.fetch(imageUrl,{muteHttpExceptions:true});
    if(res.getResponseCode()!==200)return{error:"Could not fetch image"};
    var base64=Utilities.base64Encode(res.getContent());
    var mimeType=res.getHeaders()['Content-Type']||'image/jpeg';
    var extracted=waExtractCompany(base64,mimeType);
    if(!extracted||!extracted.company_name)return{error:"Could not extract company data"};
    var ss=SpreadsheetApp.openById(WA_SHEET_ID);
    var sheet=ss.getSheets()[0];
    var data=sheet.getDataRange().getValues();
    for(var i=1;i<data.length;i++){if(waNorm(data[i][0])===waNorm(extracted.company_name))return{error:"Company already exists: "+extracted.company_name};}
    sheet.appendRow([extracted.company_name||"",extracted.headcount||"",extracted.headquarter||"",extracted.industry||"",waClean(extracted.strategic_priorities||""),waClean(extracted.business_challenges||""),"","","","","","",""]);
    var lastRow=sheet.getLastRow();
    var rawS=sheet.getRange(lastRow,5).getValue();var rawC=sheet.getRange(lastRow,6).getValue();
    var mS=!rawS||!waClean(rawS);var mC=!rawC||!waClean(rawC);
    if(rawS&&!waClean(rawS))sheet.getRange(lastRow,5).setValue("");
    if(rawC&&!waClean(rawC))sheet.getRange(lastRow,6).setValue("");
    var mH=!sheet.getRange(lastRow,2).getValue();
    var mHQ=!sheet.getRange(lastRow,3).getValue();
    var mI=!sheet.getRange(lastRow,4).getValue();
    var research=waResearch(extracted.company_name,mS,mC,mH||mHQ||mI);
    if(research)waWriteResearch(sheet,lastRow,research,mS,mC,mH,mHQ,mI);
    return{success:true,company:extracted.company_name};
  }catch(err){return{error:err.message};}
}

// ============================================================
// UPLOAD PROSPECT
// ============================================================
function processProspectImageUrl(imageUrl){
  try{
    if(!imageUrl)return{error:"No image URL provided"};
    var res=UrlFetchApp.fetch(imageUrl,{muteHttpExceptions:true});
    if(res.getResponseCode()!==200)return{error:"Could not fetch image"};
    var base64=Utilities.base64Encode(res.getContent());
    var mimeType=res.getHeaders()['Content-Type']||'image/jpeg';
    var prospect=waExtractProspect(base64,mimeType);
    if(!prospect||!prospect.name)return{error:"Could not extract prospect info"};
    var ss=SpreadsheetApp.openById(WA_SHEET_ID);
    var researchSheet=ss.getSheets()[0];
    var outreachSheet=ss.getSheetByName("Outreach Tracker");
    if(!outreachSheet)return{error:"Outreach Tracker not found"};
    var od=outreachSheet.getDataRange().getValues();
    for(var i=1;i<od.length;i++){
      if(waNorm(od[i][0])===waNorm(prospect.company_name)&&String(od[i][1]).trim().toLowerCase()===prospect.name.trim().toLowerCase())
        return{error:"Prospect already exists: "+prospect.name};
    }
    if(od.filter(function(r){return waNorm(r[0])===waNorm(prospect.company_name);}).length>=6)return{error:"Maximum 6 prospects reached"};
    var companyRow=prospect.company_name?waFindCompanyRow(researchSheet,prospect.company_name):0;
    var companyOverview="",painPoints="",sapSolutions="";
    if(companyRow>0){companyOverview=researchSheet.getRange(companyRow,7).getValue();painPoints=researchSheet.getRange(companyRow,8).getValue();sapSolutions=researchSheet.getRange(companyRow,11).getValue();}
    if(prospect.company_name&&companyRow===0){
      var rd=researchSheet.getDataRange().getValues();var exists=false;
      for(var ri=1;ri<rd.length;ri++){if(waNorm(rd[ri][0])===waNorm(prospect.company_name)){exists=true;break;}}
      if(!exists){
        researchSheet.appendRow([prospect.company_name,"","","","","","","","","","","",""]);
        Utilities.sleep(500);
        var lastRow=researchSheet.getLastRow();
        var research=waResearch(prospect.company_name,true,true,true);
        if(research){painPoints=waWriteResearch(researchSheet,lastRow,research,true,true,true,true,true);companyOverview=research.company_overview||"";sapSolutions=research.sap_solutions||"";}
      }
    }
    var messages=waMessages(prospect.company_name,prospect,companyOverview,painPoints,sapSolutions);
    if(!messages)return{error:"Could not generate messages"};
    var score=0;
    if(sapSolutions)score+=3;if(painPoints&&painPoints!=="Not found")score+=2;
    if(sapSolutions&&sapSolutions.toLowerCase().indexOf("s/4hana")>-1)score+=3;
    var priority=score>=7?"High":score>=4?"Medium":"Low";
    outreachSheet.appendRow([
      prospect.company_name||"",prospect.name||"",prospect.title||"",prospect.email||"",
      prospect.phone||"","","Active",priority,
      messages.message1||"","","No","",
      messages.message2||"","","No","",
      messages.message3||"","","No","",
      messages.message4||"","","No","",
      messages.email_message||"","","No","",""
    ]);
    return{success:true,prospect:prospect.name,company:prospect.company_name};
  }catch(err){return{error:err.message};}
}

// ============================================================
// WRITE ACTIONS
// ============================================================
function markProspectDone(body){
  var ss=SpreadsheetApp.openById(WA_SHEET_ID);
  var outreachSheet=ss.getSheetByName("Outreach Tracker");
  if(!outreachSheet)return{error:"Outreach Tracker not found"};
  var pName=String(body.prospectName).trim().toLowerCase();
  var cName=waNorm(body.companyName);
  var step=parseInt(body.step);
  var doneColMap={1:11,2:15,3:19,4:23,5:27};
  var sentColMap={1:10,2:14,3:18,4:22,5:26};
  var doneCol=doneColMap[step];var sentCol=sentColMap[step];
  if(!doneCol)return{error:"Invalid step: "+step};
  var targetRow=waFindProspectRow(outreachSheet,cName,pName);
  if(targetRow===-1)return{error:"Prospect not found: "+body.prospectName};
  var today=new Date();
  outreachSheet.getRange(targetRow,doneCol).setValue("Yes");
  outreachSheet.getRange(targetRow,sentCol).setValue(today);
  var nextFollowUp=waWorkDays(today,5);
  outreachSheet.getRange(targetRow,6).setValue(nextFollowUp);
  outreachSheet.getRange(targetRow,6).setBackground("#90EE90");
  try{logActivityReport(ss,targetRow);}catch(e){}
  return{success:true};
}
function saveOutcome(body){
  var ss=SpreadsheetApp.openById(WA_SHEET_ID);
  var outreachSheet=ss.getSheetByName("Outreach Tracker");
  if(!outreachSheet)return{error:"Outreach Tracker not found"};
  var pName=String(body.prospectName||"").trim().toLowerCase();
  var cName=waNorm(body.companyName||"");
  var step=parseInt(body.step);
  var outcomeColMap={1:12,2:16,3:20,4:24,5:28};
  var outcomeCol=outcomeColMap[step];
  if(!outcomeCol)return{error:"Invalid step"};
  var targetRow=waFindProspectRow(outreachSheet,cName,pName);
  if(targetRow===-1)return{error:"Prospect not found"};
  outreachSheet.getRange(targetRow,outcomeCol).setValue(String(body.outcome||"").trim());
  return{success:true};
}
function saveNotes(body){
  var ss=SpreadsheetApp.openById(WA_SHEET_ID);
  var outreachSheet=ss.getSheetByName("Outreach Tracker");
  if(!outreachSheet)return{error:"Outreach Tracker not found"};
  var pName=String(body.prospectName||"").trim().toLowerCase();
  var cName=waNorm(body.companyName||"");
  var targetRow=waFindProspectRow(outreachSheet,cName,pName);
  if(targetRow===-1)return{error:"Prospect not found"};
  outreachSheet.getRange(targetRow,29).setValue(String(body.notes||"").trim());
  return{success:true};
}
function scheduleFollowUp(body){
  var ss=SpreadsheetApp.openById(WA_SHEET_ID);
  var outreachSheet=ss.getSheetByName("Outreach Tracker");
  if(!outreachSheet)return{error:"Outreach Tracker not found"};
  var pName=String(body.prospectName||"").trim().toLowerCase();
  var cName=waNorm(body.companyName||"");
  var dateStr=String(body.date||"");var type=String(body.type||"Follow Up");
  var step=parseInt(body.step||"1");
  if(!dateStr)return{error:"No date provided"};
  var scheduledDate=new Date(dateStr);
  if(isNaN(scheduledDate.getTime()))return{error:"Invalid date"};
  var targetRow=waFindProspectRow(outreachSheet,cName,pName);
  if(targetRow===-1)return{error:"Prospect not found"};
  outreachSheet.getRange(targetRow,6).setValue(scheduledDate);
  outreachSheet.getRange(targetRow,6).setBackground(type==="Meeting Scheduled"?"#90EE90":"#FFE4B5");
  outreachSheet.getRange(targetRow,7).setValue(type==="Meeting Scheduled"?"Meeting":"Follow Up Scheduled");
  // Also save outcome text to the correct outcome column
  var outcomeColMap={1:12,2:16,3:20,4:24,5:28};
  var outcomeCol=outcomeColMap[step];
  if(outcomeCol)outreachSheet.getRange(targetRow,outcomeCol).setValue(type);
  return{success:true,date:dateStr,type:type};
}
function closeProspect(body){
  var ss=SpreadsheetApp.openById(WA_SHEET_ID);
  var outreachSheet=ss.getSheetByName("Outreach Tracker");
  if(!outreachSheet)return{error:"Outreach Tracker not found"};
  var pName=String(body.prospectName||"").trim().toLowerCase();
  var cName=waNorm(body.companyName||"");
  var step=parseInt(body.step||"1");
  var targetRow=waFindProspectRow(outreachSheet,cName,pName);
  if(targetRow===-1)return{error:"Prospect not found"};
  outreachSheet.getRange(targetRow,7).setValue("Not Interested");
  outreachSheet.getRange(targetRow,6).setBackground("#FFB3B3");
  // Save outcome text to the correct outcome column
  var outcomeColMap={1:12,2:16,3:20,4:24,5:28};
  var outcomeCol=outcomeColMap[step]||12;
  outreachSheet.getRange(targetRow,outcomeCol).setValue("Not Interested");
  return{success:true};
}

// ============================================================
// DATA BUILDERS — used by both getAllData() and individual endpoints
// ============================================================
function buildDashboard(rd,od,ad){
  var totalCo=Math.max(0,rd.length-1);var sapUsers=0;
  for(var i=1;i<rd.length;i++){if(rd[i][9]==="Yes")sapUsers++;}
  var totalPr=Math.max(0,od.length-1);
  var today=new Date();today.setHours(0,0,0,0);var due=0;
  for(var j=1;j<od.length;j++){
    var row=od[j];var st=row[6];
    if(st==="Responded"||st==="Converted"||st==="Not Interested"||st==="Paused")continue;
    var fd=waParseDate(row[5]);if(fd){fd.setHours(0,0,0,0);if(fd<=today)due++;}
  }
  var recentCo=[];
  for(var k=Math.max(1,rd.length-5);k<rd.length;k++){
    var r=rd[k];if(!r[0])continue;
    var cnt=0;for(var p=1;p<od.length;p++){if(waNorm(od[p][0])===waNorm(r[0]))cnt++;}
    recentCo.push({name:r[0],industry:r[3],headquarter:r[2],headcount:r[1],sap_user:r[9],sap_solutions:r[10],prospects:cnt});
  }
  recentCo.reverse();
  var duePr=[];
  for(var d=1;d<od.length;d++){
    var oRow=od[d];var ost=oRow[6];
    if(ost==="Responded"||ost==="Converted"||ost==="Not Interested"||ost==="Paused")continue;
    var fdu=waParseDate(oRow[5]);
    if(fdu){fdu.setHours(0,0,0,0);if(fdu<=today){var step=1;if(oRow[10]==="Yes")step=2;if(oRow[14]==="Yes")step=3;if(oRow[18]==="Yes")step=4;if(oRow[22]==="Yes")step=5;duePr.push({name:oRow[1],title:oRow[2],company:oRow[0],step:step,priority:oRow[7]});}}
  }
  var recentAct=[];
  for(var a=Math.max(1,ad.length-6);a<ad.length;a++){
    var ar=ad[a];if(!ar[2])continue;
    recentAct.push({date:ar[0]?Utilities.formatDate(new Date(ar[0]),Session.getScriptTimeZone(),"dd MMM"):"",day:ar[1],prospect:ar[2],title:ar[3],company:ar[4],attempt:ar[5],type:ar[6],offered:ar[7],outcome:ar[8]});
  }
  recentAct.reverse();
  return{metrics:{totalCompanies:totalCo,sapUsers:sapUsers,hitRate:totalCo>0?Math.round((sapUsers/totalCo)*100):0,totalProspects:totalPr,followUpsDue:due},recentCompanies:recentCo,dueTodayProspects:duePr,recentActivity:recentAct};
}

function buildCompanies(rd,od){
  var companies=[];
  for(var i=1;i<rd.length;i++){
    var r=rd[i];if(!r[0])continue;
    var cnt=0;for(var p=1;p<od.length;p++){if(waNorm(od[p][0])===waNorm(r[0]))cnt++;}
    companies.push({name:r[0],headcount:r[1],headquarter:r[2],industry:r[3],strategic:r[4],challenges:r[5],overview:r[6],pain_points:r[7],sap_partner:r[8],sap_user:r[9],sap_solutions:r[10],partner_name:r[11],source_links:r[12],prospects:cnt});
  }
  for(var j=1;j<od.length;j++){
    var oc=od[j][0];if(!oc)continue;
    var found=companies.some(function(c){return waNorm(c.name)===waNorm(oc);});
    if(!found){var cnt2=0;for(var k=1;k<od.length;k++){if(waNorm(od[k][0])===waNorm(oc))cnt2++;}companies.push({name:oc,headcount:"",headquarter:"",industry:"",strategic:"",challenges:"",overview:"",pain_points:"",sap_user:"No",sap_solutions:"",prospects:cnt2});}
  }
  return{companies:companies};
}

function buildPipeline(od){
  var today=new Date();today.setHours(0,0,0,0);var prospects=[];
  for(var j=1;j<od.length;j++){
    var o=od[j];if(!o[1])continue;
    var step=1;if(o[10]==="Yes")step=2;if(o[14]==="Yes")step=3;if(o[18]==="Yes")step=4;if(o[22]==="Yes")step=5;if(o[26]==="Yes")step=6;
    var fuDate=waParseDate(o[5]);var isOverdue=false;var fuStr="";
    if(fuDate){var fd3=new Date(fuDate);fd3.setHours(0,0,0,0);isOverdue=fd3<=today;fuStr=Utilities.formatDate(new Date(o[5]),Session.getScriptTimeZone(),"dd MMM");}
    prospects.push({name:o[1],title:o[2],company:o[0],phone:o[4]||"",followUpDate:fuStr,status:o[6],priority:o[7],currentStep:step,isOverdue:isOverdue});
  }
  return{prospects:prospects};
}

function buildTasks(od){
  var today=new Date();today.setHours(0,0,0,0);
  var stepLabels=["Message 1","Follow-up 1 (BDC)","Follow-up 2 (AI/BTP)","Follow-up 3","Email"];
  var tasks=[];
  for(var i=1;i<od.length;i++){
    var o=od[i];if(!o[1])continue;
    var st=o[6];
    if(st==="Responded"||st==="Converted"||st==="Not Interested"||st==="Paused")continue;
    var step=0;
    if(o[10]!=="Yes")step=1;else if(o[14]!=="Yes")step=2;else if(o[18]!=="Yes")step=3;else if(o[22]!=="Yes")step=4;else if(o[26]!=="Yes")step=5;else continue;
    var fuDate=waParseDate(o[5]);if(!fuDate)continue;
    var fd=new Date(fuDate);fd.setHours(0,0,0,0);if(fd>today)continue;
    var daysOverdue=Math.floor((today-fd)/(1000*60*60*24));
    var msgColMap={1:8,2:12,3:16,4:20,5:24};var outcomeColMap={1:11,2:15,3:19,4:23,5:27};
    tasks.push({rowIndex:i+1,prospectName:o[1],title:o[2],email:o[3],phone:o[4]||"",notes:o[28]||"",company:o[0],priority:o[7],step:step,stepLabel:stepLabels[step-1],message:o[msgColMap[step]]||"",daysOverdue:daysOverdue,isOverdue:daysOverdue>0,outcome:o[outcomeColMap[step]]||"",followUpDate:Utilities.formatDate(new Date(o[5]),Session.getScriptTimeZone(),"dd MMM")});
  }
  tasks.sort(function(a,b){return b.daysOverdue-a.daysOverdue;});
  return{tasks:tasks};
}

function buildActivity(ad){
  var activity=[];
  for(var i=1;i<ad.length;i++){
    var r=ad[i];if(!r[2])continue;
    activity.push({date:r[0]?Utilities.formatDate(new Date(r[0]),Session.getScriptTimeZone(),"dd MMM"):"",day:r[1],prospect:r[2],title:r[3],company:r[4],attempt:r[5],type:r[6],offered:r[7],outcome:r[8]});
  }
  activity.reverse();return{activity:activity};
}

function buildAnalytics(rd,od,ad){
  var sc={1:0,2:0,3:0,4:0,5:0};
  // Done columns (1-based): 11,15,19,23,27 → 0-based indices: 10,14,18,22,26
  var doneIdx={1:10,2:14,3:18,4:22,5:26};
  var totalSent=0,totalPr=0,responded=0;
  for(var i=1;i<od.length;i++){
    var o=od[i];if(!o[1])continue;totalPr++;
    if(o[6]==="Responded"||o[6]==="Converted"||o[6]==="Meeting")responded++;
    // Also count any prospect with at least one outcome filled in
    var hasOutcome=o[11]||o[15]||o[19]||o[23]||o[27];
    if(hasOutcome&&o[6]!=="Responded"&&o[6]!=="Converted"&&o[6]!=="Meeting")responded++;
    for(var s=1;s<=5;s++){if(o[doneIdx[s]]==="Yes"){sc[s]++;totalSent++;}}
  }
  var wd={};
  for(var a=1;a<ad.length;a++){var ar=ad[a];if(!ar[0])continue;var d=new Date(ar[0]);if(isNaN(d.getTime()))continue;var day=d.getDay();var diff=d.getDate()-day+(day===0?-6:1);var ws=new Date(d.setDate(diff));ws.setHours(0,0,0,0);var key=Utilities.formatDate(ws,Session.getScriptTimeZone(),"dd MMM");wd[key]=(wd[key]||0)+1;}
  var outcomes={positive:0,negative:0,noReply:0};
  var posW=["interested","yes","reply","replied","responded","meeting","call","demo","positive","great","good","follow up","scheduled"];
  var negW=["not interested","no","unsubscribe","remove","stop","negative"];
  for(var k=1;k<ad.length;k++){var oc=String(ad[k][8]||"").toLowerCase();if(!oc){outcomes.noReply++;continue;}if(posW.some(function(w){return oc.indexOf(w)>-1;}))outcomes.positive++;else if(negW.some(function(w){return oc.indexOf(w)>-1;}))outcomes.negative++;else outcomes.noReply++;}
  var ca={};for(var cx=1;cx<ad.length;cx++){var co=ad[cx][4];if(!co)continue;ca[co]=(ca[co]||0)+1;}
  var topCo=Object.keys(ca).map(function(n){return{name:n,count:ca[n]};}).sort(function(a,b){return b.count-a.count;}).slice(0,5);
  var bestStep=1,bestCount=0;for(var bs=1;bs<=5;bs++){if(sc[bs]>bestCount){bestCount=sc[bs];bestStep=bs;}}
  var stepNames=["Message 1","Follow-up 1","Follow-up 2","Follow-up 3","Email"];
  // Count messages with outcomes (0-based indices: col12=11, col16=15, col20=19, col24=23, col28=27)
  var msgsWithOutcome=0;
  var outcomeIdx={1:11,2:15,3:19,4:23,5:27};
  for(var mi=1;mi<od.length;mi++){
    var mo=od[mi];if(!mo[1])continue;
    for(var ms=1;ms<=5;ms++){if(mo[doneIdx[ms]]==="Yes"&&String(mo[outcomeIdx[ms]]||"").trim()!==""){msgsWithOutcome++;}}
  }
  var msgResponseRate=totalSent>0?Math.round((msgsWithOutcome/totalSent)*100):0;
  return{summary:{totalSent:totalSent,totalProspects:totalPr,totalCompanies:Math.max(0,rd.length-1),responseRate:totalPr>0?Math.round((responded/totalPr)*100):0,msgResponseRate:msgResponseRate,msgsWithOutcome:msgsWithOutcome,bestStep:stepNames[bestStep-1],bestStepCount:bestCount},stepBreakdown:[{label:"Message 1",count:sc[1]},{label:"Follow-up 1",count:sc[2]},{label:"Follow-up 2",count:sc[3]},{label:"Follow-up 3",count:sc[4]},{label:"Email",count:sc[5]}],outcomes:outcomes,weeklyData:wd,topCompanies:topCo};
}

// ============================================================
// INDIVIDUAL ENDPOINTS — still available for targeted refreshes
// ============================================================
function getDashboardData(){var ss=SpreadsheetApp.openById(WA_SHEET_ID);var rs=ss.getSheets()[0];var os=ss.getSheetByName("Outreach Tracker");var as=ss.getSheetByName("Activity Report");return buildDashboard(rs.getDataRange().getValues(),os?os.getDataRange().getValues():[],as?as.getDataRange().getValues():[]);}
function getCompaniesData(){var ss=SpreadsheetApp.openById(WA_SHEET_ID);var rs=ss.getSheets()[0];var os=ss.getSheetByName("Outreach Tracker");return buildCompanies(rs.getDataRange().getValues(),os?os.getDataRange().getValues():[]);}
function getPipelineData(){var ss=SpreadsheetApp.openById(WA_SHEET_ID);var os=ss.getSheetByName("Outreach Tracker");if(!os)return{prospects:[]};return buildPipeline(os.getDataRange().getValues());}
function getTasksData(){var ss=SpreadsheetApp.openById(WA_SHEET_ID);var os=ss.getSheetByName("Outreach Tracker");if(!os)return{tasks:[]};return buildTasks(os.getDataRange().getValues());}
function getActivityData(){var ss=SpreadsheetApp.openById(WA_SHEET_ID);var as=ss.getSheetByName("Activity Report");if(!as)return{activity:[]};return buildActivity(as.getDataRange().getValues());}
function getAnalyticsData(){var ss=SpreadsheetApp.openById(WA_SHEET_ID);var rs=ss.getSheets()[0];var os=ss.getSheetByName("Outreach Tracker");var as=ss.getSheetByName("Activity Report");return buildAnalytics(rs.getDataRange().getValues(),os?os.getDataRange().getValues():[],as?as.getDataRange().getValues():[]);}

function getCompanyDetail(companyName){
  var ss=SpreadsheetApp.openById(WA_SHEET_ID);
  var rs=ss.getSheets()[0];var os=ss.getSheetByName("Outreach Tracker");
  var rd=rs.getDataRange().getValues();var od=os?os.getDataRange().getValues():[];
  var company=null;
  for(var i=1;i<rd.length;i++){
    if(waNorm(rd[i][0])===waNorm(companyName)){
      var r=rd[i];
      company={name:r[0],headcount:r[1],headquarter:r[2],industry:r[3],strategic:r[4],challenges:r[5],overview:r[6],pain_points:r[7],sap_partner:r[8],sap_user:r[9],sap_solutions:r[10],partner_name:r[11],source_links:r[12]};
      break;
    }
  }
  if(!company)company={name:companyName,headcount:"",headquarter:"",industry:"",strategic:"",challenges:"",overview:"",pain_points:"",sap_user:"No",sap_solutions:"",source_links:""};
  var prospects=[];var today=new Date();today.setHours(0,0,0,0);
  for(var j=1;j<od.length;j++){
    var o=od[j];if(!o[1])continue;
    var rc=waNorm(o[0]);var tc=waNorm(companyName);
    if(rc!==tc&&rc.indexOf(tc)===-1&&tc.indexOf(rc)===-1)continue;
    var step=1;
    if(o[10]==="Yes")step=2;if(o[14]==="Yes")step=3;if(o[18]==="Yes")step=4;if(o[22]==="Yes")step=5;if(o[26]==="Yes")step=6;
    var fuDate=waParseDate(o[5]);var isOverdue=false;var fuStr="";
    if(fuDate){var fd2=new Date(fuDate);fd2.setHours(0,0,0,0);isOverdue=fd2<=today;fuStr=Utilities.formatDate(new Date(o[5]),Session.getScriptTimeZone(),"dd MMM");}
    prospects.push({
      name:o[1],title:o[2],email:o[3],phone:o[4]||"",notes:o[28]||"",
      followUpDate:fuStr,status:o[6],priority:o[7],currentStep:step,isOverdue:isOverdue,
      touchpoints:[
        {label:"Message 1",  done:o[10]==="Yes",sent:o[9] ?Utilities.formatDate(new Date(o[9]), Session.getScriptTimeZone(),"dd MMM"):"",outcome:o[11]||""},
        {label:"Follow-up 1",done:o[14]==="Yes",sent:o[13]?Utilities.formatDate(new Date(o[13]),Session.getScriptTimeZone(),"dd MMM"):"",outcome:o[15]||""},
        {label:"Follow-up 2",done:o[18]==="Yes",sent:o[17]?Utilities.formatDate(new Date(o[17]),Session.getScriptTimeZone(),"dd MMM"):"",outcome:o[19]||""},
        {label:"Follow-up 3",done:o[22]==="Yes",sent:o[21]?Utilities.formatDate(new Date(o[21]),Session.getScriptTimeZone(),"dd MMM"):"",outcome:o[23]||""},
        {label:"Email",      done:o[26]==="Yes",sent:o[25]?Utilities.formatDate(new Date(o[25]),Session.getScriptTimeZone(),"dd MMM"):"",outcome:o[27]||""}
      ],
      messages:{message1:o[8],message2:o[12],message3:o[16],message4:o[20],email:o[24]}
    });
  }
  return{company:company,prospects:prospects};
}