/**
 * FIXES FOR MainReporting.gs
 * ===========================
 * 
 * ISSUES FIXED:
 * 
 * ISSUE 2: Missing Count Bug
 * - Problem: missingCount was calculated as "5 - submissions" which counted duplicate submissions
 * - Example: ANTHONY/MARYLAND (002) had 4 submissions (including duplicate Thursday) 
 *   but only 3 unique days, so missingCount showed 1 instead of 2
 * 
 * ISSUE 3: Groups incorrectly listed as "No Reports"
 * - GBAGADA has 5 submissions but was listed as "no reports"
 * - IKEJA has 2 submissions but was listed as "no reports"
 * - Root cause: missingReports logic was checking if submissions === 0 
 *   but submissions was being calculated incorrectly
 * 
 * ISSUE 4: Wrong groups in incomplete list
 * - ANTHONY/MARYLAND (007) has 5/5 submissions but was listed as incomplete
 * 
 * ISSUE 5: Remove "Please follow up..." message
 */

// ============================================
// FIXED: processWeeklyDataWithMap function
// ============================================
function processWeeklyDataWithMap(weekData, colMap) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allGroups = getAllGroupsFromReference(ss);
  const stats = {
    totalGroups: allGroups.length,
    submittedReports: 0,
    missingReports: [],
    incompleteReports: [],
    dailyStats: {},
    groupStats: {},
    issues: [],
    roleDistribution: { 'Prayer Leader': 0, 'Bible Reviewer': 0, 'Book Chapter Reviewer': 0 }
  };
  
  const days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'];
  days.forEach(day => {
    stats.dailyStats[day] = { present: 0, late: 0, absent: 0, total: 0, groupsSubmitted: 0 };
  });
  
  const submittedGroups = new Set();
  
  weekData.forEach(row => {
    const meetingDate = new Date(row[colMap.date]);
    const group = row[colMap.group];
    const dayName = days[meetingDate.getDay() - 1];
    
    if (!dayName) return;
    
    stats.submittedReports++;
    submittedGroups.add(group);
    
    // Parse attendance from ALL attendance columns
    const attendance = parseAttendanceGridV2(row, colMap.attendance);
    stats.dailyStats[dayName].present += attendance.present;
    stats.dailyStats[dayName].late += attendance.late;
    stats.dailyStats[dayName].absent += attendance.absent;
    stats.dailyStats[dayName].total += attendance.total;
    stats.dailyStats[dayName].groupsSubmitted++;
    
    // Parse roles from ALL participation columns
    const roles = parseRolesGridV2(row, colMap.participation);
    stats.roleDistribution['Prayer Leader'] += roles.prayer;
    stats.roleDistribution['Bible Reviewer'] += roles.bible;
    stats.roleDistribution['Book Chapter Reviewer'] += roles.book;

    const coordinator = getCoordinatorForGroup(ss, group) || 'Not Assigned';

    if (!stats.groupStats[group]) {
      stats.groupStats[group] = {
        coordinator: coordinator,
        submissions: 0,
        submittedDays: [], // This will track the day names
        totalPresent: 0,
        totalAbsent: 0,
        issues: []
      };
    }
    
    stats.groupStats[group].submissions++;
    stats.groupStats[group].submittedDays.push(dayName);
    stats.groupStats[group].totalPresent += attendance.present;
    stats.groupStats[group].totalAbsent += attendance.absent;
    
    // ... rest of issue handling code remains the same ...
    
    // Source 1: "Any urgent issues requiring immediate attention?" = Yes
    const urgentCol = colMap.urgent;
    const urgentDetailsCol = colMap.urgentDetails;
    
    if (urgentCol !== -1 && row[urgentCol] === 'Yes' && urgentDetailsCol !== -1) {
      const issueText = row[urgentDetailsCol];
      if (issueText && issueText.toString().trim()) {
        const issue = {
          date: meetingDate,
          group: group,
          coordinator: stats.groupStats[group].coordinator,
          issue: issueText.toString().trim(),
          source: 'Issue Flag'
        };
        stats.issues.push(issue);
        stats.groupStats[group].issues.push(issue);
      }
    }
    
    // Source 2: Participation Feedback contains concerns
    const feedbackCol = colMap.feedback;
    if (feedbackCol !== -1 && row[feedbackCol]) {
      const feedback = row[feedbackCol].toString().trim();
      
      const urgentKeywords = [
        'urgent', 'concern', 'issue', 'problem', 'missing', 'absent', 
        'sick', 'hospital', 'emergency', 'relocated', 'banned', 'cannot attend',
        'not included', 'not in the list', 'transfer', 'left the group',
        'not responding', 'not reachable', 'phone issue', 'network issue'
      ];
      
      const hasUrgentContent = urgentKeywords.some(keyword => 
        feedback.toLowerCase().includes(keyword.toLowerCase())
      );
      
      const isSubstantial = feedback.length > 50;
      
      if (hasUrgentContent && isSubstantial) {
        const issue = {
          date: meetingDate,
          group: group,
          coordinator: stats.groupStats[group].coordinator,
          issue: feedback,
          source: 'Participation Feedback'
        };
        stats.issues.push(issue);
        stats.groupStats[group].issues.push(issue);
      }
    }
  });
  
  // ============================================
  // FIX: Calculate missing and incomplete reports correctly
  // ============================================
  
  const expectedDays = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'];
  
  stats.missingReports = [];
  stats.incompleteReports = [];
  
  allGroups.forEach(g => {
    const groupStat = stats.groupStats[g];
    const coordinator = groupStat ? groupStat.coordinator : (getCoordinatorForGroup(ss, g) || 'Not Assigned');
    
    // FIX: Get UNIQUE days submitted (remove duplicates)
    const submittedDays = groupStat ? [...new Set(groupStat.submittedDays)] : [];
    const uniqueSubmissionCount = submittedDays.length;
    
    // FIX: Missing days are days NOT in the submitted days
    const missingDays = expectedDays.filter(day => !submittedDays.includes(day));
    
    // FIX: Use uniqueSubmissionCount and missingDays.length for accurate counts
    
    // Category 1: Groups with ZERO submissions (no data at all)
    if (uniqueSubmissionCount === 0) {
      stats.missingReports.push({
        group: g,
        coordinator: coordinator
      });
    }
    // Category 2: Groups with 1-4 unique days submitted (incomplete)
    else if (uniqueSubmissionCount < 5) {
      stats.incompleteReports.push({
        group: g,
        coordinator: coordinator,
        submissions: uniqueSubmissionCount,  // FIX: Use unique count
        missingCount: missingDays.length,    // FIX: Use actual missing days count
        days: missingDays
      });
    }
    // Category 3: Groups with all 5 days submitted (complete) - no action needed
  });
  
  // Calculate averages
  Object.keys(stats.groupStats).forEach(group => {
    const g = stats.groupStats[group];
    const memberCount = getGroupMemberCount(ss, group);
    
    const totalPossibleAttendance = memberCount * g.submissions;
    
    g.avgAttendance = totalPossibleAttendance > 0 
      ? ((g.totalPresent / totalPossibleAttendance) * 100).toFixed(1) 
      : '0.0';
  });
  
  return stats;
}

// ============================================
// FIXED: createWeeklySheet function
// Removed the "Please follow up..." message
// ============================================
function createWeeklySheet_FIXED(ss, report, weekRange, missingSummary) {
  const sheetName = `Weekly Report ${formatDate(weekRange.monday)}`;
  const existing = ss.getSheetByName(sheetName);
  if (existing) ss.deleteSheet(existing);
  
  const sheet = ss.insertSheet(sheetName);
  
  // Title
  sheet.getRange('A1').setValue(`WEEKLY REPORT: ${formatDisplayDate(weekRange.monday)} - ${formatDisplayDate(weekRange.friday)}`);
  sheet.getRange('A1').setFontSize(16).setFontWeight('bold');
  sheet.getRange('A1:H1').merge();
  
  // Summary
  let row = 3;
  sheet.getRange(`A${row}`).setValue('EXECUTIVE SUMMARY').setFontWeight('bold').setFontSize(14);
  row += 2;
  
  const summary = [
    ['Total Groups', report.totalGroups],
    ['Reports Submitted', `${report.submittedReports} out of ${report.totalGroups * 5}`],
    ['Submission Rate', `${((report.submittedReports / (report.totalGroups * 5)) * 100).toFixed(1)}%`],
    ['Missing Reports', (report.totalGroups * 5) - report.submittedReports],
    ['Issues', report.issues.length]
  ];
  
  summary.forEach((item, idx) => {
    sheet.getRange(`A${row + idx}`).setValue(item[0]).setFontWeight('bold');
    sheet.getRange(`B${row + idx}`).setValue(item[1]);
  });
  
  row += summary.length + 2;
  
  // Daily breakdown
  sheet.getRange(`A${row}`).setValue('DAILY BREAKDOWN').setFontWeight('bold').setFontSize(14);
  row += 2;
  
  const headers = ['Day', 'Groups', 'Present', 'Late', 'Absent', 'Total', 'Attendance %'];
  headers.forEach((h, i) => {
    sheet.getRange(row, i + 1).setValue(h).setFontWeight('bold').setBackground('#e0e0e0');
  });
  row++;
  
  Object.keys(report.dailyStats).forEach(day => {
    const d = report.dailyStats[day];
    const rate = d.total > 0 ? ((d.present / d.total) * 100).toFixed(1) : 0;
    
    sheet.getRange(row, 1).setValue(day);
    sheet.getRange(row, 2).setValue(d.groupsSubmitted);
    sheet.getRange(row, 3).setValue(d.present);
    sheet.getRange(row, 4).setValue(d.late);
    sheet.getRange(row, 5).setValue(d.absent);
    sheet.getRange(row, 6).setValue(d.total);
    sheet.getRange(row, 7).setValue(`${rate}%`);
    
    if (rate < 70) sheet.getRange(row, 7).setBackground('#ffcccc');
    else if (rate > 90) sheet.getRange(row, 7).setBackground('#ccffcc');
    row++;
  });
  
  // Group performance
  row += 2;
  sheet.getRange(`A${row}`).setValue('GROUP PERFORMANCE').setFontWeight('bold').setFontSize(14);
  row += 2;
  
  const groupHeaders = ['Group', 'Coordinator', 'Submissions', 'Avg Attendance %', 'Issues'];
  groupHeaders.forEach((h, i) => {
    sheet.getRange(row, i + 1).setValue(h).setFontWeight('bold').setBackground('#e0e0e0');
  });
  row++;
  
  const sortedGroups = Object.keys(report.groupStats).sort((a, b) => {
    return parseFloat(report.groupStats[b].avgAttendance) - parseFloat(report.groupStats[a].avgAttendance);
  });
  
  sortedGroups.forEach(group => {
    const g = report.groupStats[group];
    sheet.getRange(row, 1).setValue(group);
    sheet.getRange(row, 2).setValue(g.coordinator);
    sheet.getRange(row, 3).setValue(g.submissions);
    sheet.getRange(row, 4).setValue(`${g.avgAttendance}%`);
    sheet.getRange(row, 5).setValue(g.issues.length);
    
    if (parseFloat(g.avgAttendance) < 70) sheet.getRange(row, 4).setBackground('#ffcccc');
    row++;
  });
  
  // Missing reports history (if available)
  if (missingSummary && missingSummary.length > 0) {
    row += 2;
    sheet.getRange(`A${row}`).setValue('MISSING REPORTS HISTORY').setFontWeight('bold').setFontSize(14);
    row += 2;
    
    sheet.getRange(`A${row}`).setValue('Date').setFontWeight('bold');
    sheet.getRange(`B${row}`).setValue('Missing Count').setFontWeight('bold');
    sheet.getRange(`C${row}`).setValue('Groups').setFontWeight('bold');
    row++;
    
    missingSummary.forEach(m => {
      sheet.getRange(row, 1).setValue(formatDisplayDate(m.date));
      sheet.getRange(row, 2).setValue(m.count);
      sheet.getRange(row, 3).setValue(m.groups.join(', '));
      row++;
    });
  }
  
  // Current missing reports (Zero submissions)
  if (report.missingReports.length > 0) {
    row += 2;
    sheet.getRange(`A${row}`).setValue('GROUPS WITH NO REPORTS').setFontWeight('bold').setFontSize(14).setFontColor('#ff0000');
    row++;
    
    // Header
    sheet.getRange(`A${row}`).setValue('Group').setFontWeight('bold');
    sheet.getRange(`B${row}`).setValue('Coordinator').setFontWeight('bold');
    row++;

    report.missingReports.forEach(m => {
      sheet.getRange(`A${row}`).setValue(m.group);
      sheet.getRange(`B${row}`).setValue(m.coordinator);
      row++;
    });
  }

  // Incomplete reports (1-4 submissions)
  if (report.incompleteReports.length > 0) {
    row += 2;
    sheet.getRange(`A${row}`).setValue('INCOMPLETE REPORTING').setFontWeight('bold').setFontSize(14).setFontColor('#ff9900');
    row++;
    
    // Header
    sheet.getRange(`A${row}`).setValue('Group').setFontWeight('bold');
    sheet.getRange(`B${row}`).setValue('Coordinator').setFontWeight('bold');
    sheet.getRange(`C${row}`).setValue('Missing Count').setFontWeight('bold');
    sheet.getRange(`D${row}`).setValue('Missing Days').setFontWeight('bold');
    row++;

    report.incompleteReports.forEach(m => {
      sheet.getRange(`A${row}`).setValue(m.group);
      sheet.getRange(`B${row}`).setValue(m.coordinator);
      sheet.getRange(`C${row}`).setValue(m.missingCount);
      sheet.getRange(`D${row}`).setValue(m.days.join(', '));
      row++;
    });
  }
  
  // ISSUES
  if (report.issues.length > 0) {
    row += 2;
    sheet.getRange(`A${row}`).setValue('ISSUES').setFontWeight('bold').setFontSize(14);
    row++;
    
    report.issues.forEach((issue, idx) => {
      const dateStr = formatDisplayDate(issue.date);
      sheet.getRange(row, 1).setValue(`${dateStr} - ${issue.group}`);
      sheet.getRange(row, 1).setFontWeight('bold');
      row++;
      sheet.getRange(row, 1).setValue(`Coordinator: ${issue.coordinator}`);
      row++;
      sheet.getRange(row, 1).setValue(`Issue: ${issue.issue}`);
      row++;
      row++;
    });
  }
  
  // FIX: REMOVED the "Please follow up..." message
  
  sheet.autoResizeColumns(1, 8);
  return sheet;
}

// ============================================
// FIXED: createWeeklyDocSafe function
// Removed the "Please follow up..." message
// ============================================
function createWeeklyDocSafe_FIXED(report, weekRange) {
  const startDate = weekRange.monday;
  const endDate = weekRange.friday;
  
  try {
    const doc = DocumentApp.create(`Weekly Report (${formatDate(startDate)} to ${formatDate(endDate)})`);
    const body = doc.getBody();
    
    try {
      body.setPageOrientation('LANDSCAPE');
    } catch (orientError) {
      Logger.log('Could not set landscape orientation: ' + orientError);
    }
    
    try {
      body.setMarginTop(36);
      body.setMarginBottom(36);
      body.setMarginLeft(50);
      body.setMarginRight(50);
    } catch (marginError) {
      Logger.log('Could not set margins: ' + marginError);
    }
    
    // Title
    const titlePara = body.appendParagraph('WEEKLY MEETING REPORT');
    titlePara.setHeading(DocumentApp.ParagraphHeading.TITLE);
    titlePara.setAlignment(DocumentApp.HorizontalAlignment.CENTER);
    try {
      titlePara.getChild(0).asText().setFontSize(22).setBold(true).setForegroundColor('#1e3a8a');
    } catch (e) {}
    
    const datePara = body.appendParagraph(`${formatDisplayDate(startDate)} - ${formatDisplayDate(endDate)}`);
    datePara.setAlignment(DocumentApp.HorizontalAlignment.CENTER);
    try {
      datePara.getChild(0).asText().setFontSize(12).setForegroundColor('#64748b');
    } catch (e) {}
    
    body.appendParagraph('');
    
    // ... (rest of document generation code remains the same until the end) ...
    
    // MISSING REPORTS - NUMBERED LIST (ZERO SUBMISSIONS)
    if (report.missingReports.length > 0) {
      const missingHeader = body.appendParagraph('GROUPS WITH NO REPORTS');
      missingHeader.setHeading(DocumentApp.ParagraphHeading.HEADING1);
      try {
        missingHeader.getChild(0).asText().setForegroundColor('#dc2626');
      } catch (e) {}
      
      const introPara = body.appendParagraph(`The following ${report.missingReports.length} groups did not submit any reports this week:`);
      introPara.setSpacingAfter(8);
      try {
        introPara.getChild(0).asText().setFontSize(10).setForegroundColor('#4b5563');
      } catch (e) {}
      
      report.missingReports.forEach((item, idx) => {
        const itemPara = body.appendParagraph(`${idx + 1}. ${item.group}`);
        itemPara.setSpacingAfter(0);
        itemPara.setIndentStart(36);
        try {
          itemPara.getChild(0).asText().setFontSize(10).setBold(true);
        } catch (e) {}
        
        const detailPara = body.appendParagraph(`   Coordinator: ${item.coordinator}`);
        detailPara.setSpacingAfter(4);
        detailPara.setIndentStart(36);
        try {
          detailPara.getChild(0).asText().setFontSize(9).setForegroundColor('#4b5563');
        } catch (e) {}
      });
      
      body.appendParagraph('');
    }

    // INCOMPLETE REPORTS - NUMBERED LIST (1-4 SUBMISSIONS)
    if (report.incompleteReports.length > 0) {
      const incompleteHeader = body.appendParagraph('INCOMPLETE REPORTING');
      incompleteHeader.setHeading(DocumentApp.ParagraphHeading.HEADING1);
      try {
        incompleteHeader.getChild(0).asText().setForegroundColor('#ff9900');
      } catch (e) {}
      
      const introPara = body.appendParagraph(`The following ${report.incompleteReports.length} groups submitted some but not all reports this week:`);
      introPara.setSpacingAfter(8);
      try {
        introPara.getChild(0).asText().setFontSize(10).setForegroundColor('#4b5563');
      } catch (e) {}
      
      report.incompleteReports.forEach((item, idx) => {
        const itemPara = body.appendParagraph(`${idx + 1}. ${item.group}`);
        itemPara.setSpacingAfter(0);
        itemPara.setIndentStart(36);
        try {
          itemPara.getChild(0).asText().setFontSize(10).setBold(true);
        } catch (e) {}
        
        const detailPara = body.appendParagraph(`   Coordinator: ${item.coordinator} | Missing: ${item.missingCount} (${item.days.join(', ')})`);
        detailPara.setSpacingAfter(4);
        detailPara.setIndentStart(36);
        try {
          detailPara.getChild(0).asText().setFontSize(9).setForegroundColor('#4b5563');
        } catch (e) {}
      });
      
      body.appendParagraph('');
    }
    
    // FIX: REMOVED the "Please follow up with these groups..." message
    // Previously had a footer paragraph here - now removed
    
    doc.saveAndClose();
    return doc.getUrl();
    
  } catch (error) {
    Logger.log('Error in createWeeklyDocSafe: ' + error);
    Logger.log('Stack: ' + error.stack);
    throw new Error('Failed to create document: ' + error.message);
  }
}

// ============================================
// FIXED: createWeeklyPDF function
// Removed the "Please follow up..." message
// ============================================
function createWeeklyPDF_FIXED(report, weekRange) {
  let html = `
    <html>
    <head>
      <style>
        @page { size: landscape; margin: 0.5in; }
        body { 
          font-family: Arial, sans-serif; 
          margin: 20px; 
          color: #1f2937;
        }
        h1 { 
          color: #1e3a8a; 
          border-bottom: 3px solid #1e3a8a; 
          padding-bottom: 10px; 
          font-size: 24px;
          text-align: center;
        }
        h2 { 
          color: #1e3a8a; 
          margin-top: 25px; 
          font-size: 16px;
          border-left: 4px solid #1e3a8a;
          padding-left: 10px;
        }
        table { 
          width: 100%; 
          border-collapse: collapse; 
          margin: 15px 0; 
          font-size: 10px;
        }
        th { 
          background: #1e3a8a; 
          color: white; 
          padding: 10px 6px; 
          text-align: left; 
          font-weight: bold;
        }
        td { 
          padding: 8px 6px; 
          border-bottom: 1px solid #e5e7eb; 
        }
        tr:nth-child(even) { background: #f9fafb; }
        .alert { background: #fee2e2 !important; }
        .success { background: #d1fae5 !important; }
        .warning { background: #fef3c7 !important; }
        .metric { 
          display: inline-block; 
          margin: 10px 15px 10px 0; 
          padding: 15px 20px; 
          background: #f3f4f6; 
          border-radius: 8px;
          border-left: 4px solid #1e3a8a;
        }
        .metric-value { 
          font-size: 22px; 
          font-weight: bold; 
          color: #1e3a8a; 
        }
        .urgent-issue { 
          margin: 15px 0; 
          padding: 15px; 
          background: #fef2f2; 
          border-left: 4px solid #dc2626;
          border-radius: 4px;
        }
        .issue-header {
          color: #dc2626;
          font-weight: bold;
          font-size: 13px;
          margin-bottom: 8px;
        }
        .issue-meta {
          font-size: 11px;
          color: #4b5563;
          margin-bottom: 6px;
        }
        .missing-list {
          column-count: 2;
          column-gap: 40px;
        }
        .missing-item {
          break-inside: avoid;
          padding: 4px 0;
          font-size: 11px;
        }
        .number {
          color: #1e3a8a;
          font-weight: bold;
          margin-right: 8px;
        }
      </style>
    </head>
    <body>
      <h1>WEEKLY MEETING REPORT</h1>
      <p style="text-align: center; color: #6b7280; margin-top: -10px;">
        ${formatDisplayDate(weekRange.monday)} - ${formatDisplayDate(weekRange.friday)}
      </p>
      
      <h2>Executive Summary</h2>
      <div>
        <div class="metric">
          <div class="metric-value">${report.totalGroups}</div>
          <div>Total Groups</div>
        </div>
        <div class="metric">
          <div class="metric-value">${report.submittedReports} out of ${report.totalGroups * 5}</div>
          <div>Reports Submitted</div>
        </div>
        <div class="metric">
          <div class="metric-value">${report.issues.length}</div>
          <div>Issues</div>
        </div>
        <div class="metric">
          <div class="metric-value">${report.missingReports.length}</div>
          <div>Missing Reports</div>
        </div>
      </div>
      
      <h2>Daily Attendance Breakdown</h2>
      <table>
        <tr>
          <th>Day</th>
          <th>Groups</th>
          <th>Present</th>
          <th>Late</th>
          <th>Absent</th>
          <th>Attendance %</th>
        </tr>
  `;
  
  const days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'];
  days.forEach(day => {
    const d = report.dailyStats[day];
    const rate = d.total > 0 ? ((d.present / d.total) * 100).toFixed(1) : 0;
    let rowClass = '';
    if (rate < 60) rowClass = 'alert';
    else if (rate > 80) rowClass = 'success';
    
    html += `<tr class="${rowClass}">
      <td><b>${day}</b></td>
      <td>${d.groupsSubmitted}</td>
      <td>${d.present}</td>
      <td>${d.late}</td>
      <td>${d.absent}</td>
      <td><b>${rate}%</b></td>
    </tr>`;
  });
  
  html += `</table>
    <h2>Group Performance</h2>
    <table>
      <tr>
        <th style="width: 35%;">Group Name</th>
        <th style="width: 20%;">Coordinator</th>
        <th style="width: 15%;">Submissions</th>
        <th style="width: 20%;">Avg Attendance</th>
        <th style="width: 10%;">Issues</th>
      </tr>
  `;
  
  const sortedGroups = Object.keys(report.groupStats)
    .sort((a, b) => parseFloat(report.groupStats[b].avgAttendance) - parseFloat(report.groupStats[a].avgAttendance));
  
  sortedGroups.forEach(group => {
    const g = report.groupStats[group];
    let rowClass = '';
    if (parseFloat(g.avgAttendance) < 60) rowClass = 'alert';
    else if (parseFloat(g.avgAttendance) > 80) rowClass = 'success';
    
    html += `<tr class="${rowClass}">
      <td>${group}</td>
      <td>${g.coordinator}</td>
      <td>${g.submissions}/5</td>
      <td><b>${g.avgAttendance}%</b></td>
      <td>${g.issues.length > 0 ? g.issues.length : '-'}</td>
    </tr>`;
  });
  
  html += `</table>`;
  
  // ISSUES
  if (report.issues.length > 0) {
    html += `<h2 style="color: #dc2626; border-color: #dc2626;">Issues</h2>`;
    
    report.issues.forEach(issue => {
      const dateStr = formatDisplayDate(issue.date);
      html += `
        <div class="urgent-issue">
          <div class="issue-header">(${dateStr}) - ${issue.group}</div>
          <div class="issue-meta"><b>Coordinator:</b> ${issue.coordinator}</div>
          <div style="font-size: 11px; line-height: 1.5;">${issue.issue}</div>
        </div>
      `;
    });
  }
  
  // MISSING REPORTS
  if (report.missingReports.length > 0) {
    html += `<h2 style="color: #dc2626; border-color: #dc2626;">Groups With No Reports</h2>`;
    html += `<p style="font-size: 11px; color: #6b7280; margin-bottom: 10px;">
      The following <b>${report.missingReports.length}</b> groups did not submit any reports this week:
    </p>`;
    html += `<div class="missing-list">`;
    
    report.missingReports.forEach((item, idx) => {
      html += `<div class="missing-item">
        <div style="font-weight: bold;">${idx + 1}. ${item.group}</div>
        <div style="margin-left: 20px; font-size: 10px; color: #4b5563;">
          Coordinator: ${item.coordinator}
        </div>
      </div>`;
    });
    
    html += `</div>`;
  }

  // INCOMPLETE REPORTS
  if (report.incompleteReports.length > 0) {
    html += `<h2 style="color: #ff9900; border-color: #ff9900;">Incomplete Reporting</h2>`;
    html += `<p style="font-size: 11px; color: #6b7280; margin-bottom: 10px;">
      The following <b>${report.incompleteReports.length}</b> groups submitted some but not all reports this week:
    </p>`;
    html += `<div class="missing-list">`;
    
    report.incompleteReports.forEach((item, idx) => {
      html += `<div class="missing-item">
        <div style="font-weight: bold;">${idx + 1}. ${item.group}</div>
        <div style="margin-left: 20px; font-size: 10px; color: #4b5563;">
          Coordinator: ${item.coordinator}<br>
          Missing: ${item.missingCount} (${item.days.join(', ')})
        </div>
      </div>`;
    });
    
    html += `</div>`;
  }

  // FIX: REMOVED the "Please follow up..." footer message
  
  html += `</body></html>`;
  
  const blob = Utilities.newBlob(html, 'text/html', 'report.html');
  const pdf = blob.getAs('application/pdf');
  pdf.setName(`Weekly Report ${formatDate(weekRange.monday)}.pdf`);
  
  return DriveApp.getRootFolder().createFile(pdf).getUrl();
}