CycleCountSheetEmailer

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Inventory Tools")
    .addItem("Run Daily Count Now", "dailyInventoryCount")
    .addItem("Reset Count History", "resetCountHistory")
    .addToUi();
}

function dailyInventoryCount() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  createInstructionsSheet(ss);
  const emailSheet = getOrCreateSheet(ss, "Emails");
  const historySheet = getOrCreateSheet(ss, "CountHistory");
  const sendNowSheet = getOrCreateSheet(ss, "SendNow");
  const catalogSheetName = "AutoCatalogUpdate";

  const emailDataRaw = emailSheet.getRange("A:A").getValues().flat().filter(e => e);

  if (emailDataRaw.length < 1) {
    SpreadsheetApp.getUi().alert("Emails sheet is missing Source URL.");
    return;
  }

  const sourceUrl = emailDataRaw[0];
  const sourceSheetName = emailDataRaw[1] || ""; // optional sheet name
  const emailRecipients = emailDataRaw.slice(2);

  if (emailRecipients.length === 0) {
    SpreadsheetApp.getUi().alert("No email recipients found in Emails sheet.");
    return;
  }

  const sourceSs = SpreadsheetApp.openByUrl(sourceUrl);

  let sourceCatalogSheet;
  if (sourceSheetName) {
    sourceCatalogSheet = sourceSs.getSheetByName(sourceSheetName);
    if (!sourceCatalogSheet) {
      SpreadsheetApp.getUi().alert(`Sheet named '${sourceSheetName}' not found in source spreadsheet.`);
      return;
    }
  } else {
    sourceCatalogSheet = sourceSs.getSheets().find(sheet => !sheet.isSheetHidden());
    if (!sourceCatalogSheet) {
      SpreadsheetApp.getUi().alert("No visible sheet found in source spreadsheet.");
      return;
    }
  }

  // Refresh AutoCatalogUpdate sheet in current spreadsheet
  let catalogSheet = ss.getSheetByName(catalogSheetName);
  if (!catalogSheet) {
    catalogSheet = ss.insertSheet(catalogSheetName);
  } else {
    catalogSheet.clearContents();
  }
  const sourceData = sourceCatalogSheet.getDataRange().getValues();
  catalogSheet.getRange(1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);

  const catalogData = catalogSheet.getDataRange().getValues();
  const historyData = historySheet.getDataRange().getValues();
  const sendNowData = sendNowSheet.getRange("A:A").getValues().flat().filter(e => e);

  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");

  // Build history map: ID → LastCountedDate
  const historyMap = {};
  for (let i = 1; i < historyData.length; i++) {
    historyMap[historyData[i][0]] = historyData[i][1];
  }

  const itemsToCount = [];

  // If SendNow sheet has IDs, use those
  if (sendNowData.length > 0) {
    for (let i = 1; i < catalogData.length; i++) {
      const id = catalogData[i][0];
      if (!id) continue;
      if (sendNowData.includes(id)) {
        const imageUrl = catalogData[i][40] || ""; // AO = index 40
        const name = catalogData[i][2] || "";
        const variation = catalogData[i][5] || "";
        const price = catalogData[i][6] || "";
        const upc = catalogData[i][7] || "";
        const category = catalogData[i][24] || "";
        const inventory = catalogData[i][38] || "";

        const itemDescription = `
<b>${name}${variation ? ' - ' + variation : ''}</b><br>
Price: $${price}<br>
UPC: ${upc}<br>
Category: ${category}<br>
Inventory: ${inventory}
`;
        itemsToCount.push({ id, imageUrl, itemDescription });
      }
    }
  }

  // If no SendNow, fallback to uncounted up to 20
  if (itemsToCount.length === 0) {
    for (let i = 1; i < catalogData.length; i++) {
      const id = catalogData[i][0];
      if (!id) continue;

      if (!historyMap[id]) {
        const imageUrl = catalogData[i][40] || "";
        const name = catalogData[i][2] || "";
        const variation = catalogData[i][5] || "";
        const price = catalogData[i][6] || "";
        const upc = catalogData[i][7] || "";
        const category = catalogData[i][24] || "";
        const inventory = catalogData[i][38] || "";

        const itemDescription = `
<b>${name}${variation ? ' - ' + variation : ''}</b><br>
Price: $${price}<br>
UPC: ${upc}<br>
Category: ${category}<br>
Inventory: ${inventory}
`;
        itemsToCount.push({ id, imageUrl, itemDescription });
        if (itemsToCount.length >= 20) break;
      }
    }
  }

  if (itemsToCount.length === 0) {
    SpreadsheetApp.getUi().alert("No items to count today.");
    return;
  }

  const emailHtml = `
<p>Hello Team,</p>
<p>Here are the next ${itemsToCount.length} items to count today:</p>
<table border="1" cellpadding="4" cellspacing="0">
<tr><th>#</th><th>Image</th><th>Description</th></tr>
${itemsToCount.map((item, index) => `
<tr>
<td>${index + 1}</td>
<td><img src="${item.imageUrl}" width="50" /></td>
<td>${item.itemDescription}</td>
</tr>`).join("")}
</table>
<p>Please mark these as counted after you finish.</p>
<p>Thank you!</p>`;

  const subject = `Daily Inventory Count - ${today}`;

  emailRecipients.forEach(email => {
    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: emailHtml
    });
  });

  // Update history map
  for (const item of itemsToCount) {
    historyMap[item.id] = today;
  }

  const newHistory = [["ID", "LastCountedDate"]];
  for (const [id, date] of Object.entries(historyMap)) {
    newHistory.push([id, date]);
  }

  historySheet.clearContents();
  historySheet.getRange(1, 1, newHistory.length, newHistory[0].length).setValues(newHistory);

  // Clear SendNow sheet
  sendNowSheet.clearContents();

  SpreadsheetApp.getUi().alert(`Emailed ${emailRecipients.length} recipients with ${itemsToCount.length} items.`);
}

function resetCountHistory() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  createInstructionsSheet(ss);
  const historySheet = getOrCreateSheet(ss, "CountHistory");
  const catalogSheet = getOrCreateSheet(ss, "AutoCatalogUpdate");

  const catalogData = catalogSheet.getDataRange().getValues();

  const newHistory = [["ID", "LastCountedDate"]];
  for (let i = 1; i < catalogData.length; i++) {
    const id = catalogData[i][0];
    if (id) {
      newHistory.push([id, ""]);
    }
  }

  historySheet.clearContents();
  historySheet.getRange(1, 1, newHistory.length, newHistory[0].length).setValues(newHistory);

  SpreadsheetApp.getUi().alert("Count history has been reset.");
}

function createTrigger() {
  ScriptApp.newTrigger("dailyInventoryCount")
    .timeBased()
    .everyDays(1)
    .atHour(8) // adjust if desired
    .create();
}

// Utility: get or create sheet
function getOrCreateSheet(ss, sheetName) {
  let sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
  }
  return sheet;
}

// Utility: create Instructions sheet if missing
function createInstructionsSheet(ss) {
  let sheet = ss.getSheetByName("Instructions");
  if (!sheet) {
    sheet = ss.insertSheet("Instructions");
    const data = [
      "📋 Inventory Count Sheet - Instructions",
      "",
      "1️⃣ Fill in the `Emails` sheet:",
      "A1: URL of source catalog",
      "A2: Optional: sheet name (leave blank for first sheet)",
      "A3+: Email addresses of recipients",
      "",
      "2️⃣ Use the `SendNow` sheet to specify IDs you want counted next",
      "Leave `SendNow` empty to default to next 20 uncounted",
      "",
      "3️⃣ Use the `Inventory Tools` menu to:",
      "- Run daily count now",
      "- Reset count history"
    ];
    sheet.getRange(1, 1, data.length, 1).setValues(
      data.map(line => [line])
    );
  }
}