code.gs

/**
 * 🚀 Avid Operations: Unified CAP & Performance Hub
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("🚀 Avid Operations")
    .addItem("📊 1. Sync CAP Central (From Master)", "runCAPAnalysis")
    .addSeparator()
    .addItem("📝 2. Generate Weekly Performance Summary", "generateSummaryReport")
    .addToUi();
}

/**
 * ⚡ PART 1: CAP ANALYSIS (Master List -> CAP Central)
 */
function runCAPAnalysis() {
  // Added the new Sheet ID to your list
  const externalSheetIds = [
    "1vF4wTJWwCg29UyN-X4evSrbC4A_cMXaOEYM99_Hk9-4",
    "10PE2FlUpS0U65LdXZnyFtZ6MZsli9Evw4mhu8qOIHHA"
  ];
  processCAPAnalysis(externalSheetIds);
}

function processCAPAnalysis(externalIds) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("CAP List");
  const dbSheet = ss.getSheetByName("Database"); 

  if (!sourceSheet || !dbSheet) {
    ss.toast("Source sheets missing!", "❌");
    return;
  }

  const TARGET_IPH = 132.12;
  const TARGET_QUAL = 0.97;

  // 1. Database Lookup
  const dbData = dbSheet.getDataRange().getValues();
  const dbHeaders = dbData[0].map(h => h.toString().trim());
  const dbEmailIdx = dbHeaders.indexOf("CF Email id");
  const dbTeamIdx = dbHeaders.indexOf("Team"); 
  const teamLookup = {};
  dbData.slice(1).forEach(row => {
    const email = (row[dbEmailIdx] || "").toString().trim().toLowerCase();
    if (email) teamLookup[email] = row[dbTeamIdx] || "No Team Assigned";
  });

  // 2. Data Processing (Avid ABN Filter + Intelligence)
  const allData = sourceSheet.getDataRange().getValues();
  const headers = allData[0].map(h => h.toString().trim());
  const data = allData.slice(1);
  const getCol = (name) => headers.indexOf(name);
  
  const idx = {
    email: getCol("E-mail Address"), reason: getCol("Reason for CAP"),
    ws: getCol("Workstream"), sync: getCol("Sync Status"),
    notif: getCol("Notification Email Sent"), rec: getCol("Recomendation"),
    t1: getCol("Throughput Week 1"), q1: getCol("Quality Week 1"),
    t2: getCol("Throughput Week 2"), q2: getCol("Quality Week 2")
  };

  const buckets = { "Notification": [], "Week 1": [], "Week 2": [] };

  data.forEach(row => {
    if ((row[idx.ws] || "").toString().trim().toLowerCase() !== "avidxchange-abn") return;
    if ((row[idx.rec] || "").toString().trim() !== "" || /CAP Final|CW Resigned|Discontinued/i.test(row[idx.sync])) return;

    const email = (row[idx.email] || "").toString().trim().toLowerCase();
    const syncVal = (row[idx.sync] || "").toString().trim();
    const notifDate = row[idx.notif] ? formatDt(row[idx.notif]) : "";
    
    let weekKey = "";
    const hasW1 = (row[idx.t1] !== "" && row[idx.t1] !== null);
    const hasW2 = (row[idx.t2] !== "" && row[idx.t2] !== null);

    if (syncVal === "") {
      if (hasW2) weekKey = "Week 2";
      else if (hasW1) weekKey = "Week 1";
      else weekKey = "Notification";
    } else {
      weekKey = extractLatestWeek(syncVal);
      if (weekKey === "Week 3" || weekKey === "Week 4") weekKey = "Week 2"; 
    }

    const displaySync = syncVal === "" ? `Auto-detected: ${weekKey}` : syncVal;
    let lastCommDate = extractLastDate(syncVal) || notifDate;

    const rowPayload = [
      row[idx.email], teamLookup[email] || "Unknown", row[idx.reason], 
      row[idx.t1], row[idx.q1], row[idx.t2], row[idx.q2], 
      calculateAvg([row[idx.t1], row[idx.t2]], false),
      calculateAvg([row[idx.q1], row[idx.q2]], true),
      lastCommDate, weekKey || "Notification", displaySync
    ];

    if (buckets[weekKey || "Notification"]) buckets[weekKey || "Notification"].push(rowPayload);
  });

  const outputHeaders = [
    "E-mail Address", "Team", "Reason for CAP", 
    "Throughput Week 1", "Quality Week 1", "Throughput Week 2", "Quality Week 2", 
    "Average Throughput", "Average Quality", "Last Communication Date", "Latest Status", "Sync Status"
  ];

  ["local", ...externalIds].forEach(id => {
    try {
      let targetSs = (id === "local") ? ss : SpreadsheetApp.openById(id);
      let targetSheet = targetSs.getSheetByName("CAP Central") || targetSs.insertSheet("CAP Central");
      
      targetSheet.clear();
      targetSheet.clearFormats();
      
      targetSheet.getRange(1, 1, 1, outputHeaders.length).setValues([outputHeaders])
        .setFontWeight("bold").setBackground("#cfe2f3").setWrap(true).setVerticalAlignment("middle");
      targetSheet.getRange(1, 8, 1, 2).setBackground("#9fc5e8");

      let cursor = 2;
      const categories = [
        { key: "Notification", label: "Notification", color: "#fff2cc" },
        { key: "Week 1", label: "Week 1", color: "#d9ead3" },
        { key: "Week 2", label: "Week 2", color: "#d9ead3" }
      ];

      categories.forEach(cat => {
        const rows = buckets[cat.key];
        targetSheet.getRange(cursor, 1).setValue(`${cat.label} (${rows.length} CWs)`).setFontWeight("bold").setBackground(cat.color);
        cursor++;
        if (rows.length > 0) {
          targetSheet.getRange(cursor, 1, rows.length, outputHeaders.length).setValues(rows);
          cursor += rows.length;
        }
        cursor++;
      });

      targetSheet.setFrozenRows(1);
      targetSheet.setColumnWidth(1, 280);
      targetSheet.setColumnWidth(3, 220);
      targetSheet.setColumnWidths(4, 6, 95);
      
      applyCAPFormatting(targetSheet, outputHeaders.length, TARGET_IPH, TARGET_QUAL);
    } catch (e) { console.error("Error on ID " + id + ": " + e.message); }
  });
  ss.toast("Avid ABN CAP Analysis Complete!", "✅");
}

/**
 * ⚡ PART 2: WEEKLY SUMMARY (Weekly Data + CAP Status -> Summary)
 */
/**
 * 📊 Summary Tool: Merges Weekly Data with CAP Status + Added Category Headers
 */
/**
 * 📊 Summary Tool: Clean Data Block with Column F Grouping & Specific Sorting
 */
function generateSummaryReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const weeklySheet = ss.getSheetByName("Weekly Data"); 
  const capSheet = ss.getSheetByName("CAP Central");
  const summarySheet = ss.getSheetByName("Summary") || ss.insertSheet("Summary");

  if (!weeklySheet || !capSheet) {
    ss.toast("Required sheets missing!", "❌");
    return;
  }

  const TARGET_IPH = 132.12;
  const TARGET_QUAL = 0.97;

  // 1. Map CAP Statuses
  const capData = capSheet.getDataRange().getValues();
  const capLookup = {}; 
  capData.slice(1).forEach(row => {
    const email = row[0] ? row[0].toString().trim().toLowerCase() : "";
    if (email) capLookup[email] = row[10] || "";
  });

  // 2. Define Groups
  const groups = { meeting: [], missingQual: [], missingTPT: [], missingAll: [], onCAP: [] };

  const weeklyData = weeklySheet.getDataRange().getValues();
  const wHeaders = weeklyData[0];
  const getWCol = (name) => wHeaders.indexOf(name);
  const wIdx = { email: getWCol("Email"), team: getWCol("Team"), iph: getWCol("Production IPH"), errorRate: getWCol("Total Error Rate") };

  weeklyData.slice(1).forEach(row => {
    const email = row[wIdx.email] ? row[wIdx.email].toString().trim().toLowerCase() : "";
    if (!email) return;

    const throughput = parseFloat(row[wIdx.iph]) || 0;
    const errorVal = (typeof row[wIdx.errorRate] === 'string' && row[wIdx.errorRate].includes('%')) ? 
                   parseFloat(row[wIdx.errorRate]) / 100 : (parseFloat(row[wIdx.errorRate]) || 0);
    const quality = 1 - errorVal;
    const existingCapStatus = capLookup[email];
    
    const meetsIPH = throughput >= TARGET_IPH;
    const meetsQual = quality >= TARGET_QUAL;

    let finalStatus = "";
    let targetGroup = "";

    if (!existingCapStatus || existingCapStatus === "") {
      if (meetsIPH && meetsQual) { finalStatus = "Not on CAP - Meeting Targets"; targetGroup = "meeting"; }
      else if (!meetsQual && meetsIPH) { finalStatus = "Not on CAP - Missing Quality"; targetGroup = "missingQual"; }
      else if (meetsQual && !meetsIPH) { finalStatus = "Not on CAP - Missing Throughput"; targetGroup = "missingTPT"; }
      else { finalStatus = "Not on CAP - Missing All Metrics"; targetGroup = "missingAll"; }
    } else {
      finalStatus = existingCapStatus;
      targetGroup = "onCAP";
    }

    groups[targetGroup].push([row[wIdx.email], row[wIdx.team], throughput, quality, finalStatus]);
  });

  // 3. INTERNAL SORTING LOGIC
  const sortData = (arr, isCapSection = false) => {
    return arr.sort((a, b) => {
      if (isCapSection) {
        // Order: Notification (5) -> Week 1 (6) -> Week 2 (7)
        const getPriority = (status) => status.includes("Notification") ? 1 : status.includes("Week 1") ? 2 : 3;
        const pA = getPriority(a[4]);
        const pB = getPriority(b[4]);
        if (pA !== pB) return pA - pB;
      }
      // Then sort by Quality (Index 3) DESC, then IPH (Index 2) DESC
      if (b[3] !== a[3]) return b[3] - a[3];
      return b[2] - a[2];
    });
  };

  // 4. Reconstruct Output
  summarySheet.clear();
  summarySheet.clearFormats();
  
  const headers = [["Email", "Team", "Throughput", "Quality", "CAP Status", "Section Info"]];
  summarySheet.getRange(1, 1, 1, 6).setValues(headers).setFontWeight("bold").setBackground("#cfe2f3");

  let cursor = 2;
  const sections = [
    { label: "🎯 MEETING ALL TARGETS", data: sortData(groups.meeting) },
    { label: "⚠️ MISSING QUALITY", data: sortData(groups.missingQual) },
    { label: "⚠️ MISSING THROUGHPUT", data: sortData(groups.missingTPT) },
    { label: "🚨 MISSING ALL METRICS", data: sortData(groups.missingAll) },
    { label: "🛠️ ACTIVE CAP PROGRAM", data: sortData(groups.onCAP, true) }
  ];

  sections.forEach(sec => {
    if (sec.data.length > 0) {
      // Print Section Name in Column F of the first row of that section
      summarySheet.getRange(cursor, 6).setValue(`${sec.label} (${sec.data.length})`)
        .setFontWeight("bold").setFontColor("#666666");
      
      summarySheet.getRange(cursor, 1, sec.data.length, 5).setValues(sec.data);
      cursor += sec.data.length;
    }
  });

  // 5. Formatting
  const lastRow = summarySheet.getLastRow();
  if (lastRow > 1) {
    summarySheet.getRange(2, 3, lastRow - 1, 1).setNumberFormat("0.00");
    summarySheet.getRange(2, 4, lastRow - 1, 1).setNumberFormat("0.0%");
  }
  
  applySummaryFormatting(summarySheet, 5, TARGET_IPH, TARGET_QUAL);
  
  summarySheet.setColumnWidth(1, 280);
  summarySheet.setColumnWidth(5, 250);
  summarySheet.setColumnWidth(6, 250);
  ss.toast("Clean Summary Generated!", "📊");
}

/**
 * 🎨 FORMATTING HELPERS
 */
function applyCAPFormatting(sheet, lastCol, TARGET_IPH, TARGET_QUAL) {
  formatColumns(sheet, lastCol, TARGET_IPH, TARGET_QUAL);
}

function applySummaryFormatting(sheet, lastCol, TARGET_IPH, TARGET_QUAL) {
  formatColumns(sheet, lastCol, TARGET_IPH, TARGET_QUAL);
}

function formatColumns(sheet, lastCol, TARGET_IPH, TARGET_QUAL) {
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const rules = [];
  const teamColors = { "Bamboo": "#c9daf8", "Lewis": "#d9d2e9", "Charis": "#fce5cd", "Rajat": "#d0e0e3", "Vincent": "#ead1dc", "Manoj": "#f3f3f3", "Siddhartha": "#b7e1cd", "Samuel": "#fff2cc", "Ram": "#d9ead3", "beth/mary": "#ead1dc" };

  headers.forEach((h, i) => {
    const colIdx = i + 1;
    const colRange = sheet.getRange(2, colIdx, lastRow - 1, 1);

    if (h.includes("Throughput")) {
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThanOrEqualTo(TARGET_IPH).setBackground("#b7e1cd").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenNumberBetween(115, TARGET_IPH - 0.01).setBackground("#fff2cc").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(115).whenCellNotEmpty().setBackground("#f4cccc").setRanges([colRange]).build());
    }
    if (h.includes("Quality")) {
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThanOrEqualTo(TARGET_QUAL).setBackground("#b7e1cd").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenNumberBetween(0.95, TARGET_QUAL - 0.01).setBackground("#fff2cc").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(0.95).whenCellNotEmpty().setBackground("#f4cccc").setRanges([colRange]).build());
    }
    if (h === "Team") {
      Object.keys(teamColors).forEach(name => {
        rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo(name).setBackground(teamColors[name]).setRanges([colRange]).build());
      });
    }
    if (h === "CAP Status" || h === "Sync Status") {
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Week 2").setBackground("#ea9999").setFontColor("#ffffff").setBold(true).setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Week 1").setBackground("#f9cb9c").setFontColor("#783f04").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Missing All Metrics").setBackground("#f4cccc").setFontColor("#990000").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Meeting Targets").setBackground("#b7e1cd").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Notification").setBackground("#fff2cc").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Missing Quality").setBackground("#fff2cc").setRanges([colRange]).build());
      rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextContains("Missing Throughput").setBackground("#fff2cc").setRanges([colRange]).build());
    }
  });
  sheet.setConditionalFormatRules(rules);
}

/**
 * 🛠️ UTILITIES
 */
function extractLatestWeek(t) { if (!t) return null; const m = t.toString().match(/Week\s*([1-4])/gi); return m ? `Week ${Math.max(...m.map(x => parseInt(x.match(/\d/)[0])))}` : null; }
function extractLastDate(t) { if (!t) return ""; const d = t.toString().match(/\d{1,2}\/\d{1,2}\/\d{2,4}/g); return d ? d[d.length - 1] : ""; }
function formatDt(d) { return d instanceof Date ? Utilities.formatDate(d, Session.getScriptTimeZone(), "MM/dd/yyyy") : d; }
function calculateAvg(arr, isQ) { const v = arr.map(x => (typeof x === 'string' && x.includes('%')) ? parseFloat(x)/100 : parseFloat(x)).filter(x => !isNaN(x)); if (!v.length) return ""; return v.reduce((a, b) => a + b, 0) / v.length; }