// ─────────────────────────────────────────────────────────────────
// RETAIL DISTRIBUTION AUDIT — Google Apps Script
// Paste into Extensions → Apps Script, Save, then run setupSheets()
// once. Then deploy as Web App for live data from the scorecard.
// ─────────────────────────────────────────────────────────────────

const RAW_SHEET  = "Raw Data";
const DASH_SHEET = "Dashboard";

// ── RECEIVE AUDIT DATA (called by scorecard Submit button) ────────
function doPost(e) {
  try {
    var ss   = SpreadsheetApp.getActiveSpreadsheet();
    var data = JSON.parse(e.postData.contents);

    // ── PHOTO UPLOAD ──────────────────────────────────────────────
    if (data.action === 'uploadPhoto') {
      try {
        // Log the attempt to a debug sheet so we can see what's arriving
        var dbg = ss.getSheetByName('Debug') || ss.insertSheet('Debug');
        if (dbg.getLastRow() === 0) dbg.appendRow(['Time','Action','Store','Section','DataSize','Status','Error']);
        var imgSize = data.imageData ? data.imageData.length : 0;
        
        var folder = getOrCreateFolder(data.store, data.date, data.section, data.country || 'SG');
        var blob   = Utilities.newBlob(
          Utilities.base64Decode(data.imageData),
          data.mimeType || 'image/jpeg',
          data.fileName || 'photo.jpg'
        );
        var file = folder.createFile(blob);
        file.setDescription(data.caption || '');
        file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
        
        dbg.appendRow([new Date().toISOString(), 'uploadPhoto', data.store, data.section, imgSize, 'SUCCESS', '']);
        
        return ContentService
          .createTextOutput(JSON.stringify({ status:'success', driveUrl: file.getUrl() }))
          .setMimeType(ContentService.MimeType.JSON);
      } catch(uploadErr) {
        var dbg2 = ss.getSheetByName('Debug') || ss.insertSheet('Debug');
        dbg2.appendRow([new Date().toISOString(), 'uploadPhoto', data.store || '', data.section || '', 
          data.imageData ? data.imageData.length : 0, 'FAILED', uploadErr.message]);
        return ContentService
          .createTextOutput(JSON.stringify({ status:'error', message: uploadErr.message }))
          .setMimeType(ContentService.MimeType.JSON);
      }
    }

    // ── SAVE COMMENT ─────────────────────────────────────────────
    if (data.action === 'saveComment') {
      var cSheet = ss.getSheetByName('Comments') || ss.insertSheet('Comments');
      if (cSheet.getLastRow() === 0) {
        cSheet.appendRow(['Timestamp','AuditKey','Store','Date','Section','CriterionID','Comment','Author','Resolved','ReplyTo']);
      }
      cSheet.appendRow([
        new Date().toISOString(),
        data.auditKey, data.store, data.auditDate,
        data.section, data.criterionId,
        data.comment, data.author,
        data.resolved || false,
        data.replyTo || ''
      ]);
      return ContentService
        .createTextOutput(JSON.stringify({ status:'success' }))
        .setMimeType(ContentService.MimeType.JSON);
    }

    // ── RESOLVE COMMENT ──────────────────────────────────────────
    if (data.action === 'resolveComment') {
      var cSheet = ss.getSheetByName('Comments');
      if (cSheet) {
        var rows = cSheet.getDataRange().getValues();
        for (var i = 1; i < rows.length; i++) {
          if (rows[i][1] === data.auditKey && rows[i][5] === data.criterionId && rows[i][7] === data.author) {
            cSheet.getRange(i+1, 9).setValue(true);
          }
        }
      }
      return ContentService
        .createTextOutput(JSON.stringify({ status:'success' }))
        .setMimeType(ContentService.MimeType.JSON);
    }

    // ── LOG PHOTO SUBMISSION (from CM upload page) ────────────────
    if (data.action === 'logPhotoSubmission') {
      var pLog = ss.getSheetByName('Photo Log') || ss.insertSheet('Photo Log');
      if (pLog.getLastRow() === 0) {
        pLog.appendRow(['Timestamp','Store','Date','Country','CM Name','Total Photos','Sec A','Sec B','Sec C','Sec D','Sec E','Sec F','Notes A','Notes B','Notes C','Notes D','Notes E','Notes F']);
      }
      var secs = data.sections || {};
      var notes = data.sectionNotes || {};
      pLog.appendRow([
        new Date().toISOString(), data.store, data.date, data.country, data.cmName,
        data.totalPhotos, secs.A||0, secs.B||0, secs.C||0, secs.D||0, secs.E||0, secs.F||0,
        notes.A||'', notes.B||'', notes.C||'', notes.D||'', notes.E||'', notes.F||''
      ]);
      return ContentService
        .createTextOutput(JSON.stringify({ status:'success' }))
        .setMimeType(ContentService.MimeType.JSON);
    }
    var sheet = ss.getSheetByName(RAW_SHEET) || ss.insertSheet(RAW_SHEET);
    var headers = ['Timestamp','Country','Store','Brand','Tier','Channel Manager','Auditor','Audit Date','Type',
      'Sec A %','Sec B %','Sec C %','Sec D %','Sec E %','Sec F %','Overall %','Grade','Passed','Total','Failed Items','Actions'];
    if (sheet.getLastRow() === 0) {
      sheet.appendRow(headers);
      sheet.getRange(1,1,1,headers.length).setBackground('#1a1a1a').setFontColor('#C8A84B').setFontWeight('bold');
      sheet.setFrozenRows(1);
    }
    var rowData = [
      new Date().toISOString(),
      data.countryLabel || data.country,
      data.store, data.brand, data.tier, data.cm,
      data.auditor, data.date, data.type,
      data.scoreA ?? '', data.scoreB ?? '', data.scoreC ?? '',
      data.scoreD ?? '', data.scoreE ?? '', data.scoreF ?? '',
      data.overall, data.grade, data.passed, data.total,
      data.failedItems, data.actions
    ];
    sheet.appendRow(rowData);

    // ── ALSO WRITE TO COUNTRY TAB ──────────────────────────────
    var countryCode = data.country || 'Other';
    var countryNames = { SG:'Singapore', MY:'Malaysia', HK:'Hong Kong', TH:'Thailand' };
    var countryTabName = countryNames[countryCode] || countryCode;
    var cSheet = ss.getSheetByName(countryTabName);
    if (!cSheet) {
      cSheet = ss.insertSheet(countryTabName);
      cSheet.appendRow(headers);
      cSheet.getRange(1,1,1,headers.length).setBackground('#1a1a1a').setFontColor('#C8A84B').setFontWeight('bold');
      cSheet.setFrozenRows(1);
    }
    cSheet.appendRow(rowData);

    // ── ALSO WRITE TO CM TAB ───────────────────────────────────
    if (data.cm && data.cm !== '—') {
      var cmTabName = 'CM — ' + data.cm;
      var cmSheet = ss.getSheetByName(cmTabName);
      if (!cmSheet) {
        cmSheet = ss.insertSheet(cmTabName);
        cmSheet.appendRow(headers);
        cmSheet.getRange(1,1,1,headers.length).setBackground('#1a1a1a').setFontColor('#C8A84B').setFontWeight('bold');
        cmSheet.setFrozenRows(1);
      }
      cmSheet.appendRow(rowData);
    }

    return ContentService
      .createTextOutput(JSON.stringify({ status:'success' }))
      .setMimeType(ContentService.MimeType.JSON);

  } catch(err) {
    return ContentService
      .createTextOutput(JSON.stringify({ status:'error', message: err.message }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // ── SEND TELEGRAM REPORT ─────────────────────────────────────
  if (e && e.parameter && e.parameter.action === 'sendReport') {
    return handleSendReport(e);
  }

  // ── RETURN AUDIT SCORES ───────────────────────────────────────
  if (e && e.parameter && e.parameter.action === 'getScores') {
    var sheet = ss.getSheetByName(RAW_SHEET);
    if (!sheet) return ContentService.createTextOutput('[]').setMimeType(ContentService.MimeType.JSON);
    var rows = sheet.getDataRange().getValues();
    var headers = rows[0];
    var data = rows.slice(1).map(function(row) {
      var obj = {};
      headers.forEach(function(h, i) { obj[h] = row[i]; });
      return {
        store: obj['Store'] || '', country: obj['Country'] || '',
        brand: obj['Brand'] || '', tier: obj['Tier'] || '',
        cm: obj['Channel Manager'] || '', auditor: obj['Auditor'] || '',
        date: obj['Audit Date'] ? Utilities.formatDate(new Date(obj['Audit Date']), 'GMT+8', 'yyyy-MM-dd') : '',
        overall: obj['Overall %'] || null, grade: obj['Grade'] || '',
        scoreA: obj['Sec A %'] || null, scoreB: obj['Sec B %'] || null,
        scoreC: obj['Sec C %'] || null, scoreD: obj['Sec D %'] || null,
        scoreE: obj['Sec E %'] || null, scoreF: obj['Sec F %'] || null,
      };
    }).filter(function(r) { return r.store; });
    return ContentService
      .createTextOutput(JSON.stringify(data))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // ── RETURN PHOTOS FOR AN AUDIT ───────────────────────────────
  if (e && e.parameter && e.parameter.action === 'getPhotos') {
    var photos = [];
    var key = e.parameter.auditKey || '';
    var section = e.parameter.section || '';
    // Parse store and date from auditKey (format: store|date)
    var parts = key.split('|');
    var filterStore = parts[0] || '';
    var filterDate  = parts[1] || '';
    // Search Drive folder: Retail Distribution Audits / store / date /
    try {
      var countryId = (e.parameter.country && COUNTRY_FOLDERS[e.parameter.country])
        ? COUNTRY_FOLDERS[e.parameter.country]
        : null;
      var searchRoots = countryId
        ? [DriveApp.getFolderById(countryId)]
        : Object.values(COUNTRY_FOLDERS).map(function(id) { try { return DriveApp.getFolderById(id); } catch(e) { return null; }}).filter(Boolean);

      searchRoots.forEach(function(countryFolder) {
        var yearFolders = countryFolder.getFolders();
        while (yearFolders.hasNext()) {
          var yf = yearFolders.next();
          var storeFolders2 = filterStore ? yf.getFoldersByName(filterStore) : yf.getFolders();
          while (storeFolders2.hasNext()) {
            var sf = storeFolders2.next();
            var weekFolders = sf.getFolders();
            while (weekFolders.hasNext()) {
              var wf = weekFolders.next();
              // Only search this week folder if date matches (week label contains date approx)
              var secFolders = section ? wf.getFoldersByName('Section ' + section) : wf.getFolders();
              while (secFolders.hasNext()) {
                var secF = secFolders.next();
                var secId = secF.getName().replace('Section ','');
                var files = secF.getFiles();
                while (files.hasNext()) {
                  var file = files.next();
                  // Filter by date in filename if provided
                  if (!filterDate || file.getName().indexOf(filterDate) !== -1 || wf.getName().indexOf(filterDate.substring(0,7)) !== -1) {
                    photos.push({
                      section: secId,
                      fileName: file.getName(),
                      driveUrl: file.getDownloadUrl(),
                      viewUrl: file.getUrl(),
                      caption: file.getDescription() || '',
                      auditKey: key,
                    });
                  }
                }
              }
            }
          }
        }
      });
    } catch(err) {}
    return ContentService
      .createTextOutput(JSON.stringify(photos))
      .setMimeType(ContentService.MimeType.JSON);
  }
  if (e && e.parameter && e.parameter.action === 'getComments') {
    var cSheet = ss.getSheetByName('Comments');
    if (!cSheet) return ContentService.createTextOutput('[]').setMimeType(ContentService.MimeType.JSON);
    var rows = cSheet.getDataRange().getValues();
    var key = e.parameter.auditKey;
    var comments = rows.slice(1)
      .filter(function(r) { return !key || r[1] === key; })
      .map(function(r) { return {
        timestamp: r[0], auditKey: r[1], store: r[2], auditDate: r[3],
        section: r[4], criterionId: r[5], comment: r[6],
        author: r[7], resolved: r[8], replyTo: r[9]
      }; });
    return ContentService
      .createTextOutput(JSON.stringify(comments))
      .setMimeType(ContentService.MimeType.JSON);
  }

  return ContentService
    .createTextOutput("Retail Distribution Audit endpoint is live.")
    .setMimeType(ContentService.MimeType.TEXT);
}

// ── DRIVE FOLDER HELPER ───────────────────────────────────────────
// Uses existing TC Acoustic audit Drive folders
var COUNTRY_FOLDERS = {
  'SG': '15hbjnuW2yzMMTdhw4tvEfvyOiRZKyoyF',
  'MY': '18nuTsVAhBhUvDc6Cc9aqaTdOx8PSjgBw',
  'HK': '1xXqpw_csYUuuxIVeEtvKBsfwU2YpGpuB',
  'TH': '192ZJlFmK8s2FEKAQKflz9FZ06XH70cJv',
};

function getOrCreateFolder(store, date, section, country) {
  // Match existing structure: Country > Year > Store > Week > Section
  var countryFolderId = COUNTRY_FOLDERS[country] || COUNTRY_FOLDERS['SG'];
  var countryFolder   = DriveApp.getFolderById(countryFolderId);

  // Year folder (e.g. "2026")
  var year = date ? date.substring(0, 4) : new Date().getFullYear().toString();
  var yearFolders = countryFolder.getFoldersByName(year);
  var yearFolder  = yearFolders.hasNext() ? yearFolders.next() : countryFolder.createFolder(year);

  // Store folder
  var storeFolders = yearFolder.getFoldersByName(store);
  var storeFolder  = storeFolders.hasNext() ? storeFolders.next() : yearFolder.createFolder(store);

  // Week folder (e.g. "W24 - 2026-03-30")
  var weekLabel = getWeekLabel(date);
  var weekFolders = storeFolder.getFoldersByName(weekLabel);
  var weekFolder  = weekFolders.hasNext() ? weekFolders.next() : storeFolder.createFolder(weekLabel);

  // Section subfolder
  var secLabel   = 'Section ' + section;
  var secFolders = weekFolder.getFoldersByName(secLabel);
  return secFolders.hasNext() ? secFolders.next() : weekFolder.createFolder(secLabel);
}

function getWeekLabel(dateStr) {
  // Returns e.g. "W13 - 2026-03-24"
  if (!dateStr) return 'Unknown Week';
  var d    = new Date(dateStr);
  var start = new Date(d);
  start.setDate(d.getDate() - d.getDay() + 1); // Monday of that week
  var pad  = function(n) { return n < 10 ? '0' + n : '' + n; };
  var mon  = start.getFullYear() + '-' + pad(start.getMonth()+1) + '-' + pad(start.getDate());
  // Calculate week number of year
  var oneJan = new Date(d.getFullYear(), 0, 1);
  var wk = Math.ceil((((d - oneJan) / 86400000) + oneJan.getDay() + 1) / 7);
  return 'W' + pad(wk) + ' - ' + mon;
}

// ── TELEGRAM BOT ──────────────────────────────────────────────────
// Replace with your BotFather token
var TELEGRAM_TOKEN = 'YOUR_BOT_TOKEN_HERE';
var TELEGRAM_API   = 'https://api.telegram.org/bot' + TELEGRAM_TOKEN;

// Send a Telegram message to a chat ID
function sendTelegram(chatId, message) {
  var url = TELEGRAM_API + '/sendMessage';
  var payload = {
    chat_id: chatId,
    text: message,
    parse_mode: 'Markdown'
  };
  var options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options);
  var result = JSON.parse(response.getContentText());
  return result.ok;
}

// Get CM chat ID from CM Contacts sheet
function getCMChatId(cmName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('CM Contacts');
  if (!sheet) return null;
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === cmName) return data[i][2]; // col C = Chat ID
  }
  return null;
}

// Called from dashboard "Send Report" button
// cmName: e.g. "Johnathan"
// weekLabel: e.g. "W13 - 2026-03-24"
function sendWeeklyReport(cmName, weekLabel) {
  var ss      = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = ss.getSheetByName('Raw Data');
  if (!rawSheet) return { success: false, error: 'No Raw Data sheet' };

  var chatId = getCMChatId(cmName);
  if (!chatId) return { success: false, error: 'No Telegram Chat ID for ' + cmName };

  // Pull all audits for this CM this week
  var rows    = rawSheet.getDataRange().getValues();
  var headers = rows[0];
  var audits  = rows.slice(1).filter(function(r) {
    return r[5] === cmName; // col F = CM
  });

  if (audits.length === 0) {
    return { success: false, error: 'No audits found for ' + cmName };
  }

  // Pull previous week's audits for comparison
  var prevWeekAudits = rows.slice(1).filter(function(r) {
    return r[5] === cmName && r[7] !== audits[0][7]; // different date
  });

  var prevByStore = {};
  prevWeekAudits.forEach(function(r) {
    if (!prevByStore[r[2]] || r[7] > prevByStore[r[2]][7]) prevByStore[r[2]] = r;
  });

  // Build message
  var totalAudits = audits.length;
  var avgScore    = Math.round(audits.reduce(function(s,r){ return s + (r[15]||0); }, 0) / totalAudits);
  var goldCount   = audits.filter(function(r){ return r[16] === 'Gold'; }).length;

  var msg = '📊 *Weekly Audit Report*\n';
  msg += '*' + cmName + '* | ' + weekLabel + '\n';
  msg += '━━━━━━━━━━━━━━━━━━━━\n\n';
  msg += '🏆 *Summary*\n';
  msg += totalAudits + ' stores audited | Avg: *' + avgScore + '%* | Gold: ' + goldCount + '/' + totalAudits + '\n\n';

  // Grade emoji
  function gradeEmoji(g) {
    return {Gold:'🥇',A:'🥈',B:'🅱️',C:'🅾️',D:'⚠️',E:'🔴'}[g] || '—';
  }

  // Per store details
  audits.forEach(function(r) {
    var store    = r[2];
    var score    = r[15];
    var grade    = r[16];
    var failed   = r[19] ? r[19].split(' | ') : [];
    var secScores = [r[9],r[10],r[11],r[12],r[13],r[14]];
    var secLabels = ['A','B','C','D','E','F'];

    // Trend vs previous week
    var trend = '';
    if (prevByStore[store]) {
      var diff = score - prevByStore[store][15];
      if (diff > 0) trend = ' ↑ +' + diff + '%';
      else if (diff < 0) trend = ' ↓ ' + diff + '%';
      else trend = ' → same';
    }

    msg += '🏪 *' + store + '*\n';
    msg += gradeEmoji(grade) + ' ' + score + '% — ' + grade + trend + '\n';

    // Section scores
    var secLine = secLabels.map(function(l,i) {
      return l + ': ' + (secScores[i] !== '' && secScores[i] !== null ? secScores[i] + '%' : '—');
    }).join(' | ');
    msg += '`' + secLine + '`\n';

    // Failed criteria
    if (failed.length > 0) {
      msg += '❌ *To improve:*\n';
      failed.slice(0,5).forEach(function(f) {
        msg += '  • ' + f.trim() + '\n';
      });
    }
    msg += '\n';
  });

  // Footer
  msg += '━━━━━━━━━━━━━━━━━━━━\n';
  msg += '_Sent from TC Retail Distribution Audit System_';

  var sent = sendTelegram(chatId, msg);
  return { success: sent, message: sent ? 'Report sent to ' + cmName : 'Failed to send' };
}

// Web App endpoint for sending report from dashboard
// Called via: doGet?action=sendReport&cm=Johnathan&week=W13%20-%202026-03-24
function handleSendReport(e) {
  var cmName    = e.parameter.cm || '';
  var weekLabel = e.parameter.week || '';
  var result    = sendWeeklyReport(cmName, weekLabel);
  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

// ── ONE-TIME SETUP (run manually once after pasting) ──────────────
function setupSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // ── RAW DATA SHEET ──────────────────────────────────────────────
  let raw = ss.getSheetByName(RAW_SHEET);
  if (!raw) raw = ss.insertSheet(RAW_SHEET);
  raw.clearContents();

  const rawHeaders = [
    "Timestamp","Country","Store","Brand","Tier","Channel Manager",
    "Auditor","Audit Date","Type",
    "Sec A %","Sec B %","Sec C %","Sec D %","Sec E %","Sec F %",
    "Overall %","Grade","Items Passed","Total Items",
    "Failed Items","Action Items"
  ];
  raw.getRange(1, 1, 1, rawHeaders.length).setValues([rawHeaders]);

  // Style header row
  const rawHeaderRange = raw.getRange(1, 1, 1, rawHeaders.length);
  rawHeaderRange.setBackground("#1a1a1a").setFontColor("#C8A84B")
    .setFontWeight("bold").setFontSize(10);
  raw.setFrozenRows(1);
  raw.setColumnWidth(1, 160);  // Timestamp
  raw.setColumnWidth(2, 100);  // Country
  raw.setColumnWidth(3, 220);  // Store
  raw.setColumnWidth(4, 80);   // Brand
  raw.setColumnWidth(5, 80);   // Tier
  raw.setColumnWidth(6, 120);  // CM
  raw.setColumnWidth(7, 100);  // Auditor
  raw.setColumnWidth(8, 100);  // Date
  raw.setColumnWidth(20, 300); // Failed Items
  raw.setColumnWidth(21, 200); // Actions

  // ── DASHBOARD SHEET ─────────────────────────────────────────────
  let dash = ss.getSheetByName(DASH_SHEET);
  if (!dash) dash = ss.insertSheet(DASH_SHEET, 0); // first tab
  else dash.clearContents();

  const D = dash;
  const raw_name = RAW_SHEET;

  function h1(row, col, text) {
    D.getRange(row, col).setValue(text)
      .setFontSize(14).setFontWeight("bold").setFontColor("#C8A84B");
  }
  function h2(row, col, text, color) {
    D.getRange(row, col).setValue(text)
      .setFontSize(10).setFontWeight("bold").setFontColor(color || "#888888");
  }
  function label(row, col, text) {
    D.getRange(row, col).setValue(text).setFontColor("#666666").setFontSize(10);
  }
  function formula(row, col, f) {
    D.getRange(row, col).setFormula(f).setFontColor("#E8E0D0").setFontSize(11).setFontWeight("bold");
  }
  function sectionBg(r1, c1, r2, c2, color) {
    D.getRange(r1, c1, r2-r1+1, c2-c1+1).setBackground(color);
  }

  // Title
  D.getRange("A1").setValue("RETAIL DISTRIBUTION AUDIT — DASHBOARD")
    .setFontSize(16).setFontWeight("bold").setFontColor("#C8A84B");
  D.getRange("A2").setFormula('="Last updated: "&TEXT(NOW(),"dd mmm yyyy HH:MM")')
    .setFontSize(9).setFontColor("#555555");

  // ── KEY STATS (row 4) ──────────────────────────────────────────
  h2(4, 1, "KEY STATS");
  const statLabels = ["Total Audits","Avg Score","Gold Stores","Gold Rate","Needs Attention (D/E)"];
  const statFormulas = [
    `=COUNTA('${raw_name}'!C2:C)`,
    `=IFERROR(TEXT(AVERAGE('${raw_name}'!P2:P),"0")&"%","—")`,
    `=COUNTIF('${raw_name}'!Q2:Q,"Gold")`,
    `=IFERROR(TEXT(COUNTIF('${raw_name}'!Q2:Q,"Gold")/COUNTA('${raw_name}'!Q2:Q),"0%"),"—")`,
    `=COUNTIF('${raw_name}'!Q2:Q,"D")+COUNTIF('${raw_name}'!Q2:Q,"E")`,
  ];
  statLabels.forEach((l, i) => {
    label(5, i+1, l);
    formula(6, i+1, statFormulas[i]);
    D.getRange(6, i+1).setFontSize(16).setFontColor("#C8A84B");
  });
  sectionBg(4, 1, 6, 5, "#141414");
  D.getRange("A4:E4").setBackground("#1A1A1A");

  // ── GRADE DISTRIBUTION (row 8) ────────────────────────────────
  h2(8, 1, "GRADE DISTRIBUTION");
  const grades = ["Gold","A","B","C","D","E"];
  const gradeColors = ["#C8A84B","#7aa8b8","#6aaa62","#b8965a","#e08030","#c06060"];
  grades.forEach((g, i) => {
    D.getRange(9, i+1).setValue(g).setFontWeight("bold").setFontColor(gradeColors[i]).setFontSize(13);
    D.getRange(10, i+1).setFormula(`=COUNTIF('${raw_name}'!Q2:Q,"${g}")`)
      .setFontSize(18).setFontWeight("bold").setFontColor(gradeColors[i]);
    D.getRange(11, i+1).setFormula(`=IFERROR(TEXT(COUNTIF('${raw_name}'!Q2:Q,"${g}")/COUNTA('${raw_name}'!Q2:Q),"0%"),"—")`)
      .setFontSize(10).setFontColor("#666666");
  });
  sectionBg(8, 1, 11, 6, "#141414");
  D.getRange("A8:F8").setBackground("#1A1A1A");

  // ── BY COUNTRY (row 13) ───────────────────────────────────────
  h2(13, 1, "BY COUNTRY");
  ["Country","Audits","Avg Score","Gold","Gold Rate"].forEach((h, i) => {
    D.getRange(14, i+1).setValue(h).setFontColor("#888888").setFontSize(9).setFontWeight("bold");
  });
  const countries = ["Singapore","Malaysia","Hong Kong","Thailand"];
  countries.forEach((c, i) => {
    const r = 15 + i;
    D.getRange(r, 1).setValue(c).setFontColor("#E8E0D0").setFontSize(10);
    D.getRange(r, 2).setFormula(`=COUNTIF('${raw_name}'!B2:B,"${c}")`).setFontColor("#888888").setFontSize(10);
    D.getRange(r, 3).setFormula(`=IFERROR(TEXT(AVERAGEIF('${raw_name}'!B2:B,"${c}",'${raw_name}'!P2:P),"0")&"%","—")`).setFontColor("#C8A84B").setFontSize(11).setFontWeight("bold");
    D.getRange(r, 4).setFormula(`=COUNTIFS('${raw_name}'!B2:B,"${c}",'${raw_name}'!Q2:Q,"Gold")`).setFontColor("#C8A84B").setFontSize(10);
    D.getRange(r, 5).setFormula(`=IFERROR(TEXT(COUNTIFS('${raw_name}'!B2:B,"${c}",'${raw_name}'!Q2:Q,"Gold")/COUNTIF('${raw_name}'!B2:B,"${c}"),"0%"),"—")`).setFontColor("#888888").setFontSize(10);
  });
  sectionBg(13, 1, 19, 5, "#141414");
  D.getRange("A13:E13").setBackground("#1A1A1A");

  // ── BY BRAND (col 7, row 13) ──────────────────────────────────
  h2(13, 7, "BY BRAND");
  ["Brand","Audits","Avg Score","Gold","Gold Rate"].forEach((h, i) => {
    D.getRange(14, 7+i).setValue(h).setFontColor("#888888").setFontSize(9).setFontWeight("bold");
  });
  const brands = ["Sonos","Marshall","Bowers & Wilkins"];
  brands.forEach((b, i) => {
    const r = 15 + i;
    D.getRange(r, 7).setValue(b).setFontColor("#E8E0D0").setFontSize(10);
    D.getRange(r, 8).setFormula(`=COUNTIF('${raw_name}'!D2:D,"${b}")`).setFontColor("#888888").setFontSize(10);
    D.getRange(r, 9).setFormula(`=IFERROR(TEXT(AVERAGEIF('${raw_name}'!D2:D,"${b}",'${raw_name}'!P2:P),"0")&"%","—")`).setFontColor("#C8A84B").setFontSize(11).setFontWeight("bold");
    D.getRange(r, 10).setFormula(`=COUNTIFS('${raw_name}'!D2:D,"${b}",'${raw_name}'!Q2:Q,"Gold")`).setFontColor("#C8A84B").setFontSize(10);
    D.getRange(r, 11).setFormula(`=IFERROR(TEXT(COUNTIFS('${raw_name}'!D2:D,"${b}",'${raw_name}'!Q2:Q,"Gold")/COUNTIF('${raw_name}'!D2:D,"${b}"),"0%"),"—")`).setFontColor("#888888").setFontSize(10);
  });
  sectionBg(13, 7, 19, 11, "#141414");
  D.getRange("G13:K13").setBackground("#1A1A1A");

  // ── BY CHANNEL MANAGER (row 21) ───────────────────────────────
  h2(21, 1, "BY CHANNEL MANAGER");
  ["Channel Manager","Country","Audits","Avg Score","Gold","Gold Rate"].forEach((h, i) => {
    D.getRange(22, i+1).setValue(h).setFontColor("#888888").setFontSize(9).setFontWeight("bold");
  });
  const cms = [
    ["Zhi Yong","Singapore"],["Johnathan","Singapore"],["Jerome","Singapore"],
    ["Goh Xinyi","Singapore"],["Sinclair","Singapore"],
    ["Adrian","Malaysia"],["Jin Nong","Malaysia"],
    ["Ginger Kiang","Hong Kong"],["Ricky","Hong Kong"],
    ["Ning","Thailand"],
  ];
  cms.forEach(([cm, country], i) => {
    const r = 23 + i;
    D.getRange(r, 1).setValue(cm).setFontColor("#E8E0D0").setFontSize(10);
    D.getRange(r, 2).setValue(country).setFontColor("#666666").setFontSize(10);
    D.getRange(r, 3).setFormula(`=COUNTIF('${raw_name}'!F2:F,"${cm}")`).setFontColor("#888888").setFontSize(10);
    D.getRange(r, 4).setFormula(`=IFERROR(TEXT(AVERAGEIF('${raw_name}'!F2:F,"${cm}",'${raw_name}'!P2:P),"0")&"%","—")`).setFontColor("#C8A84B").setFontSize(11).setFontWeight("bold");
    D.getRange(r, 5).setFormula(`=COUNTIFS('${raw_name}'!F2:F,"${cm}",'${raw_name}'!Q2:Q,"Gold")`).setFontColor("#C8A84B").setFontSize(10);
    D.getRange(r, 6).setFormula(`=IFERROR(TEXT(COUNTIFS('${raw_name}'!F2:F,"${cm}",'${raw_name}'!Q2:Q,"Gold")/COUNTIF('${raw_name}'!F2:F,"${cm}"),"0%"),"—")`).setFontColor("#888888").setFontSize(10);
  });
  sectionBg(21, 1, 33, 6, "#141414");
  D.getRange("A21:F21").setBackground("#1A1A1A");

  // ── SECTION AVERAGES (row 21, col 8) ─────────────────────────
  h2(21, 8, "SECTION AVERAGES (all audits)");
  const secCols = [["A",10],["B",11],["C",12],["D",13],["E",14],["F",15]];
  const secNames = ["Assets & Condition","Brand Identity","Cleanliness","Conquer & Visibility","Marketing Execution","Demo & Experience"];
  ["Section","Name","Avg %"].forEach((h, i) => {
    D.getRange(22, 8+i).setValue(h).setFontColor("#888888").setFontSize(9).setFontWeight("bold");
  });
  secCols.forEach(([sec, colIdx], i) => {
    const r = 23 + i;
    const colLetter = String.fromCharCode(64 + colIdx);
    D.getRange(r, 8).setValue(sec).setFontColor("#E8E0D0").setFontSize(10).setFontWeight("bold");
    D.getRange(r, 9).setValue(secNames[i]).setFontColor("#666666").setFontSize(10);
    D.getRange(r, 10).setFormula(`=IFERROR(TEXT(AVERAGE('${raw_name}'!${colLetter}2:${colLetter}),"0")&"%","—")`).setFontColor("#C8A84B").setFontSize(11).setFontWeight("bold");
  });
  sectionBg(21, 8, 29, 10, "#141414");
  D.getRange("H21:J21").setBackground("#1A1A1A");

  // ── RECENT AUDITS (row 35) ────────────────────────────────────
  h2(35, 1, "RECENT AUDITS (latest 50, auto-sorted newest first)");
  D.getRange("A35:U35").setBackground("#1A1A1A");
  const recentHeaders = ["Date","Country","Store","Brand","Tier","CM","Auditor","Overall %","Grade","A%","B%","C%","D%","E%","F%","Passed","Total","Failed Items","Actions"];
  recentHeaders.forEach((h, i) => {
    D.getRange(36, i+1).setValue(h).setFontColor("#888888").setFontSize(9).setFontWeight("bold");
  });
  // QUERY to pull latest 50, sorted by timestamp desc
  D.getRange("A37").setFormula(
    `=IFERROR(QUERY('${raw_name}'!A2:U,"SELECT H,B,C,D,E,F,G,P,Q,J,K,L,M,N,O,R,S,T,U ORDER BY A DESC LIMIT 50",0),"No data yet")`
  ).setFontColor("#E8E0D0").setFontSize(10);
  sectionBg(35, 1, 35, 19, "#1A1A1A");

  // Column widths for dashboard
  D.setColumnWidth(1, 160);
  D.setColumnWidth(2, 100);
  D.setColumnWidth(3, 220);
  D.setColumnWidth(4, 90);
  D.setColumnWidth(5, 80);
  D.setColumnWidth(6, 120);
  D.setColumnWidth(7, 10);
  D.setColumnWidth(8, 60);
  D.setColumnWidth(9, 200);
  D.setColumnWidth(10, 60);

  // Freeze first row of recent table
  D.setFrozenRows(1);

  // ── CM CONTACTS SHEET ──────────────────────────────────────────
  var contacts = ss.getSheetByName('CM Contacts') || ss.insertSheet('CM Contacts');
  contacts.clearContents();
  contacts.getRange(1,1,1,4).setValues([['CM Name','Country','Telegram Chat ID','Email']]);
  contacts.getRange(1,1,1,4).setBackground('#1a1a1a').setFontColor('#C8A84B').setFontWeight('bold');
  // Pre-fill CM names
  var cmList = [
    ['Sylvia','SG','',''],
    ['Zhi Yong','SG','',''],
    ['Johnathan','SG','',''],
    ['Jerome','SG','',''],
    ['Goh Xinyi','SG','',''],
    ['Sinclair','SG','',''],
    ['Adrian','MY','',''],
    ['Jin Nong','MY','',''],
    ['Ginger Kiang','HK','',''],
    ['Ricky','HK','',''],
    ['Ning','TH','',''],
  ];
  contacts.getRange(2,1,cmList.length,4).setValues(cmList);
  contacts.setColumnWidth(1,140);
  contacts.setColumnWidth(3,180);
  contacts.setFrozenRows(1);
  ss.setActiveSheet(dash);

  SpreadsheetApp.getUi().alert("✓ Setup complete! Both sheets are ready.\n\nNext: Deploy this script as a Web App and paste the URL into the scorecard Setup tab.");
}