// 1. Extract submitted form values with fallback for manual testing
let studentName = 'Participant';
let studentEmail = '';
let selectedTrack = 'Google Workspace & Cloud Automation';

if (e && e.namedValues) {
  // Triggered by linked Google Form response
  studentName = e.namedValues['Full Name'] ? e.namedValues['Full Name'][0] : 'Participant';
  studentEmail = e.namedValues['Email'] ? e.namedValues['Email'][0] : '';
  selectedTrack = e.namedValues['Track'] ? e.namedValues['Track'][0] : selectedTrack;
} else {
  // Fallback for direct execution from Apps Script editor
  console.log('No event object detected. Using active admin email for testing...');
  studentEmail = PropertiesService.getScriptProperties().getProperty('ADMIN_EMAIL') || Session.getActiveUser().getEmail();
  studentName = 'Test Student (Editor Run)';
}

// 2. Validate email syntax before sending
if (!studentEmail || !studentEmail.includes('@')) {
  console.warn('Invalid or empty recipient email: "' + studentEmail + '". Aborting dispatch.');
  return;
}

// 3. Assemble responsive HTML email using Figtree font and solid brand colors
const htmlBody = [
  '<div style="font-family: 'Figtree', Arial, sans-serif; max-width: 600px; margin: 0 auto; border: 1px solid #cbd5e1; border-radius: 8px; overflow: hidden; background-color: #ffffff;">',
  '  <div style="background-color: #02569B; color: #ffffff; padding: 24px; text-align: center;">',
  '    <h2 style="margin: 0; font-size: 22px;">GDG - Google Workspace Training</h2>',
  '    <p style="margin: 8px 0 0 0; font-size: 14px; opacity: 0.9;">Registration Confirmation & Day V Roadmap</p>',
  '  </div>',
  '  <div style="padding: 24px; color: #1e293b; line-height: 1.6;">',
  '    <p style="font-size: 16px; margin-top: 0;">Hello <strong>' + studentName + '</strong>,</p>',
  '    <p>Your workshop submission has been securely recorded in our system.</p>',
  '    <div style="background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px; padding: 16px; margin: 20px 0;">',
  '      <p style="margin: 0 0 8px 0; font-size: 14px; font-weight: bold; color: #02569B;">Session Summary:</p>',
  '      <ul style="margin: 0; padding-left: 20px; font-size: 14px; color: #334155;">',
  '        <li><strong>Track:</strong> ' + selectedTrack + '</li>',
  '        <li><strong>Format:</strong> 5-Day Intensive Community Workshop</li>',
  '        <li><strong>Automated Module:</strong> Day V - Google Apps Script Deep Dive</li>',
  '      </ul>',
  '    </div>',
  '    <p style="font-size: 14px; color: #64748b;">Visit <a href="https://script.google.com" style="color: #0284c7; text-decoration: none;">script.google.com</a> before our live session.</p>',
  '    <hr style="border: none; border-top: 1px solid #e2e8f0; margin: 24px 0;" />',
  '    <p style="font-size: 12px; color: #94a3b8; margin-bottom: 0; text-align: center;">GDG Afrique Francophone | GDG Pointe-Noire | GDG on Campus CUEA</p>',
  '  </div>',
  '</div>'
].join('\n');

// 4. Dispatch email with plain text fallback via GmailApp
const emailSubject = 'Confirmation: Registration Received | GDG Workspace Training 2026';
const plainTextFallback = 'Hello ' + studentName + ',\n\nYour registration for GDG - Google Workspace Training (' + selectedTrack + ') has been recorded!\n\nSee you at the Day V Google Apps Script session!';

GmailApp.sendEmail(studentEmail, emailSubject, plainTextFallback, {
  htmlBody: htmlBody,
  name: 'GDG Workspace Automation Lead'
});

console.log('Confirmation email successfully dispatched to: ' + studentEmail);

/**
 * Practice 1: Automated Form Submission Email Receipt
 * Service: FormApp + GmailApp + SpreadsheetApp
 * Author: Georges Byona (@glosings0n)
 */

function handleWorkshopFormSubmission(e) {
  try {
    let studentName = 'Participant';
    let studentEmail = '';
    let selectedTrack = 'Google Workspace & Cloud Automation';

    // 1. Handle execution both via Event Trigger and Direct Testing
    if (e && e.namedValues) {
      studentName = e.namedValues['Full Name'] ? e.namedValues['Full Name'][0] : 'Participant';
      studentEmail = e.namedValues['Email'] ? e.namedValues['Email'][0] : '';
      selectedTrack = e.namedValues['Track'] ? e.namedValues['Track'][0] : selectedTrack;
    } else {
      console.log('No event object detected. Using active admin email for testing...');
      const props = PropertiesService.getScriptProperties();
      studentEmail = props.getProperty('ADMIN_EMAIL') || Session.getActiveUser().getEmail();
      studentName = 'Test Student (Editor Run)';
    }

    // 2. Validate email syntax
    if (!studentEmail || !studentEmail.includes('@')) {
      console.warn('Invalid or empty recipient email: "' + studentEmail + '". Aborting dispatch.');
      return;
    }

    const emailSubject = 'Confirmation: Registration Received | GDG Workspace Training 2026';
    
    // 3. Assemble responsive HTML template with Figtree font
    const htmlBody = [
      '<div style="font-family: 'Figtree', Arial, sans-serif; max-width: 600px; margin: 0 auto; border: 1px solid #cbd5e1; border-radius: 8px; overflow: hidden; background-color: #ffffff;">',
      '  <div style="background-color: #02569B; color: #ffffff; padding: 24px; text-align: center;">',
      '    <h2 style="margin: 0; font-size: 22px;">GDG - Google Workspace Training</h2>',
      '    <p style="margin: 8px 0 0 0; font-size: 14px; opacity: 0.9;">Registration Confirmation & Day V Roadmap</p>',
      '  </div>',
      '  <div style="padding: 24px; color: #1e293b; line-height: 1.6;">',
      '    <p style="font-size: 16px; margin-top: 0;">Hello <strong>' + studentName + '</strong>,</p>',
      '    <p>We are delighted to confirm that your workshop submission has been securely recorded in our system.</p>',
      '    <div style="background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px; padding: 16px; margin: 20px 0;">',
      '      <p style="margin: 0 0 8px 0; font-size: 14px; font-weight: bold; color: #02569B;">Session Summary:</p>',
      '      <ul style="margin: 0; padding-left: 20px; font-size: 14px; color: #334155;">',
      '        <li><strong>Registered Track:</strong> ' + selectedTrack + '</li>',
      '        <li><strong>Format:</strong> 5-Day Intensive Community Workshop</li>',
      '        <li><strong>Automated Module:</strong> Day V - Google Apps Script Deep Dive</li>',
      '      </ul>',
      '    </div>',
      '    <p style="font-size: 14px; color: #64748b;">Please ensure you have active access to <a href="https://script.google.com" style="color: #0284c7; text-decoration: none;">script.google.com</a> before our live hands-on session.</p>',
      '    <hr style="border: none; border-top: 1px solid #e2e8f0; margin: 24px 0;" />',
      '    <p style="font-size: 12px; color: #94a3b8; margin-bottom: 0; text-align: center;">Organized with GDG Afrique Francophone, GDG Pointe-Noire & GDG on Campus CUEA.<br>Powered by LosingTek & FlutterFire Summer Camp.</p>',
      '  </div>',
      '</div>'
    ].join('\n');

    const plainTextFallback = 'Hello ' + studentName + ',\n\nYour registration for GDG - Google Workspace Training has been confirmed!\nTrack: ' + selectedTrack + '\n\nSee you at the Day V Google Apps Script automation session!';

    // 4. Send email via GmailApp
    GmailApp.sendEmail(studentEmail, emailSubject, plainTextFallback, {
      htmlBody: htmlBody,
      name: 'GDG Workspace Automation Lead'
    });

    console.log('Confirmation email successfully dispatched to: ' + studentEmail);
  } catch (err) {
    console.error('Failed to execute handleWorkshopFormSubmission: ' + err.message);
  }
}

// 1. Locate or create a dedicated Contact Group
const groupName = 'GDG Workspace Training 2026';
let contactGroup = ContactsApp.getContactGroup(groupName);

if (!contactGroup) {
  contactGroup = ContactsApp.createContactGroup(groupName);
  console.log('Created new Contact Group: "' + groupName + '"');
} else {
  console.log('Found existing Contact Group: "' + groupName + '"');
}

// 2. Read student rows in a single batch call from Google Sheets
const spreadsheetId = PropertiesService.getScriptProperties().getProperty('SPREADSHEET_ID');
const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('Attendance_Master');
const data = sheet.getDataRange().getValues(); // 2D array

// 3. Check for existing contacts before creating a duplicate
const existingContacts = ContactsApp.getContactsByEmailAddress(email);

let contact;
if (existingContacts.length > 0) {
  contact = existingContacts[0];
  console.log('Contact already exists for ' + email + '. Updating details...');
} else {
  const nameParts = fullName.trim().split(' ');
  const firstName = nameParts[0] || 'Student';
  const lastName = nameParts.slice(1).join(' ') || '';
  
  contact = ContactsApp.createContact(firstName, lastName, email);
  console.log('Created new contact: ' + firstName + ' ' + lastName);
}

// 4. Attach contextual notes and assign to the target Contact Group
contact.setNotes('Participant ID: ' + studentId + ' | Track: ' + track + ' | GDG Workspace Training 2026');

const currentGroups = contact.getContactGroups();
const alreadyInGroup = currentGroups.some(g => g.getName() === groupName);

if (!alreadyInGroup) {
  contact.addToGroup(contactGroup);
  console.log('Added ' + contact.getFullName() + ' to group "' + groupName + '".');
}

/**
 * Practice 2: Batch Student Ingestion into Google Contacts
 * Service: ContactsApp + SpreadsheetApp
 * Author: Georges Byona (@glosings0n)
 */

function syncStudentsToGoogleContacts() {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');

    if (!spreadsheetId) {
      throw new Error('SPREADSHEET_ID not found in ScriptProperties. Run setupWorkshopEnvironment first.');
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const data = sheet.getDataRange().getValues();

    if (data.length <= 1) {
      console.log('No student rows found in Attendance_Master.');
      return;
    }

    // 1. Locate or create the targeted Contact Group
    const groupName = 'GDG Workspace Training 2026';
    let contactGroup = ContactsApp.getContactGroup(groupName);

    if (!contactGroup) {
      contactGroup = ContactsApp.createContactGroup(groupName);
      console.log('Created new Contact Group: "' + groupName + '"');
    } else {
      console.log('Using existing Contact Group: "' + groupName + '"');
    }

    let createdCount = 0;
    let existingCount = 0;

    // 2. Iterate student records (skip header at index 0)
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const studentId = row[0];
      const fullName = row[1];
      const email = row[2];
      const track = row[3];

      if (!email || !email.includes('@')) {
        continue;
      }

      // 3. Deduplication check
      const existingContacts = ContactsApp.getContactsByEmailAddress(email);

      let contact;
      if (existingContacts.length > 0) {
        contact = existingContacts[0];
        existingCount++;
      } else {
        const nameParts = fullName.trim().split(' ');
        const firstName = nameParts[0] || 'Student';
        const lastName = nameParts.slice(1).join(' ') || '';

        contact = ContactsApp.createContact(firstName, lastName, email);
        createdCount++;
      }

      // 4. Update custom note and attach to group
      contact.setNotes('Participant ID: ' + studentId + ' | Track: ' + track + ' | GDG Workspace Training 2026');
      
      const currentGroups = contact.getContactGroups();
      const alreadyInGroup = currentGroups.some(g => g.getName() === groupName);

      if (!alreadyInGroup) {
        contact.addToGroup(contactGroup);
      }
    }

    console.log('=== CONTACT SYNCHRONIZATION COMPLETE ===');
    console.log('Newly Created Contacts: ' + createdCount);
    console.log('Existing Contacts Synced: ' + existingCount);
    console.log('Total Group Members:     ' + contactGroup.getContacts().length);
  } catch (err) {
    console.error('Error in syncStudentsToGoogleContacts: ' + err.message);
  }
}

// 1. Read the complete attendance table into memory with a single network call
const props = PropertiesService.getScriptProperties();
const spreadsheetId = props.getProperty('SPREADSHEET_ID');
const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('Attendance_Master');

const values = sheet.getDataRange().getValues(); // 2D matrix

// 2. Iterate attendance columns (Days 1 to 5) and compute percentage
const attendanceRateUpdates = [];
let cumulativeRate = 0;
let eligibleCount = 0;
let atRiskCount = 0;

for (let i = 1; i < values.length; i++) {
  const row = values[i];
  let daysPresent = 0;

  // Columns 4 to 8 correspond to Day 1 through Day 5
  for (let dayCol = 4; dayCol <= 8; dayCol++) {
    if (row[dayCol] === 'P') {
      daysPresent++;
    }
  }

  const ratePercentage = Math.round((daysPresent / 5) * 100);
  cumulativeRate += ratePercentage;
  attendanceRateUpdates.push([ratePercentage + '%']);

  if (ratePercentage >= 80) eligibleCount++;
  else if (ratePercentage < 75) atRiskCount++;
}

// 3. Write all computed rates back to Column J in a single atomic batch
// sheet.getRange(startRow, startCol, numRows, numCols)
sheet.getRange(2, 10, attendanceRateUpdates.length, 1).setValues(attendanceRateUpdates);
console.log('Atomically updated ' + attendanceRateUpdates.length + ' attendance rates in sheet.');

// 4. Construct an executive HTML report with Figtree font and status pills
const averageCohortRate = Math.round(cumulativeRate / (values.length - 1));

const htmlReport = [
  '<div style="font-family: 'Figtree', Arial, sans-serif; max-width: 650px; margin: 0 auto; border: 1px solid #cbd5e1; border-radius: 8px; overflow: hidden; background-color: #ffffff;">',
  '  <div style="background-color: #02569B; color: #ffffff; padding: 20px;">',
  '    <h2 style="margin: 0; font-size: 20px;">Student Attendance & Progression Audit</h2>',
  '    <p style="margin: 6px 0 0 0; font-size: 13px; opacity: 0.9;">GDG - Google Workspace Training (Day V Audit)</p>',
  '  </div>',
  '  <div style="padding: 20px; color: #1e293b;">',
  '    <div style="display: table; width: 100%; margin-bottom: 20px;">',
  '      <div style="display: table-cell; width: 33%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
  '        <div style="font-size: 22px; font-weight: bold; color: #0284c7;">' + averageCohortRate + '%</div>',
  '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">Average Rate</div>',
  '      </div>',
  '      <div style="display: table-cell; width: 33%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
  '        <div style="font-size: 22px; font-weight: bold; color: #15803d;">' + eligibleCount + '</div>',
  '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">Certificate Ready</div>',
  '      </div>',
  '      <div style="display: table-cell; width: 33%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
  '        <div style="font-size: 22px; font-weight: bold; color: #b91c1c;">' + atRiskCount + '</div>',
  '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">At Risk (<75%)</div>',
  '      </div>',
  '    </div>',
  '    <p style="font-size: 13px; color: #475569;">Detailed roster and breakdown are available in the attached Google Sheet.</p>',
  '  </div>',
  '</div>'
].join('\n');

// 5. Deliver the digest to course instructors
const adminEmail = props.getProperty('ADMIN_EMAIL') || Session.getActiveUser().getEmail();
const emailSubject = 'Attendance Audit Digest: ' + averageCohortRate + '% Cohort Avg | GDG Workspace Training';

GmailApp.sendEmail(adminEmail, emailSubject, 'Cohort Attendance Audit: ' + averageCohortRate + '% Average.', {
  htmlBody: htmlReport,
  name: 'GDG Attendance Auditor'
});

console.log('Audit digest delivered to: ' + adminEmail);

/**
 * Practice 3: Student Attendance Audit & Teacher Digest Report
 * Service: SpreadsheetApp + GmailApp
 * Author: Georges Byona (@glosings0n)
 */

function auditStudentAttendanceAndNotify() {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');
    const adminEmail = props.getProperty('ADMIN_EMAIL') || Session.getActiveUser().getEmail();

    if (!spreadsheetId) {
      throw new Error('SPREADSHEET_ID missing from ScriptProperties.');
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const values = sheet.getDataRange().getValues();

    if (values.length <= 1) {
      console.log('No data to audit.');
      return;
    }

    const attendanceRateUpdates = [];
    let totalStudents = values.length - 1;
    let perfectAttendanceCount = 0;
    let atRiskCount = 0;
    let eligibleCount = 0;
    let cumulativeRate = 0;

    let tableRowsHtml = '';

    // Loop through students (indices: Day 1 = col 4, Day 5 = col 8, Rate = col 9)
    for (let i = 1; i < values.length; i++) {
      const row = values[i];
      const studentId = row[0];
      const fullName = row[1];
      const email = row[2];
      const track = row[3];

      let daysPresent = 0;
      for (let dayCol = 4; dayCol <= 8; dayCol++) {
        if (row[dayCol] === 'P') {
          daysPresent++;
        }
      }

      const ratePercentage = Math.round((daysPresent / 5) * 100);
      cumulativeRate += ratePercentage;
      attendanceRateUpdates.push([ratePercentage + '%']);

      let statusBadge = '';
      if (ratePercentage >= 80) {
        eligibleCount++;
        if (ratePercentage === 100) perfectAttendanceCount++;
        statusBadge = '<span style="background-color: #dcfce7; color: #15803d; padding: 4px 8px; border-radius: 4px; font-weight: bold; font-size: 12px;">Eligible</span>';
      } else if (ratePercentage >= 75) {
        statusBadge = '<span style="background-color: #fef9c3; color: #a16207; padding: 4px 8px; border-radius: 4px; font-weight: bold; font-size: 12px;">Borderline</span>';
      } else {
        atRiskCount++;
        statusBadge = '<span style="background-color: #fee2e2; color: #b91c1c; padding: 4px 8px; border-radius: 4px; font-weight: bold; font-size: 12px;">At Risk</span>';
      }

      tableRowsHtml += [
        '<tr style="border-bottom: 1px solid #e2e8f0; font-size: 13px;">',
        '  <td style="padding: 10px; font-family: monospace;">' + studentId + '</td>',
        '  <td style="padding: 10px; font-weight: 500;">' + fullName + '</td>',
        '  <td style="padding: 10px; color: #64748b;">' + track + '</td>',
        '  <td style="padding: 10px; text-align: center; font-weight: bold;">' + ratePercentage + '%</td>',
        '  <td style="padding: 10px; text-align: center;">' + statusBadge + '</td>',
        '</tr>'
      ].join('\n');
    }

    // Single atomic batch write for all attendance rates (Column J, Row 2 onwards)
    sheet.getRange(2, 10, attendanceRateUpdates.length, 1).setValues(attendanceRateUpdates);
    console.log('Updated attendance rate for ' + attendanceRateUpdates.length + ' students in sheet.');

    const averageCohortRate = Math.round(cumulativeRate / totalStudents);

    // Build Executive HTML Email Digest for Instructors using Figtree font
    const emailSubject = 'Attendance Audit Digest: ' + averageCohortRate + '% Cohort Avg | GDG Workspace Training';
    const htmlReport = [
      '<div style="font-family: 'Figtree', Arial, sans-serif; max-width: 650px; margin: 0 auto; border: 1px solid #cbd5e1; border-radius: 8px; overflow: hidden; background-color: #ffffff;">',
      '  <div style="background-color: #02569B; color: #ffffff; padding: 20px;">',
      '    <h2 style="margin: 0; font-size: 20px;">Student Attendance & Progression Audit</h2>',
      '    <p style="margin: 6px 0 0 0; font-size: 13px; opacity: 0.9;">GDG - Google Workspace Training (Day V Audit)</p>',
      '  </div>',
      '  <div style="padding: 20px; color: #1e293b;">',
      '    <div style="display: table; width: 100%; margin-bottom: 20px;">',
      '      <div style="display: table-cell; width: 25%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
      '        <div style="font-size: 22px; font-weight: bold; color: #02569B;">' + totalStudents + '</div>',
      '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">Total Cohort</div>',
      '      </div>',
      '      <div style="display: table-cell; width: 25%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
      '        <div style="font-size: 22px; font-weight: bold; color: #0284c7;">' + averageCohortRate + '%</div>',
      '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">Average Rate</div>',
      '      </div>',
      '      <div style="display: table-cell; width: 25%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
      '        <div style="font-size: 22px; font-weight: bold; color: #15803d;">' + eligibleCount + '</div>',
      '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">Certificate Ready</div>',
      '      </div>',
      '      <div style="display: table-cell; width: 25%; text-align: center; padding: 12px; background-color: #f8fafc; border: 1px solid #e2e8f0; border-radius: 6px;">',
      '        <div style="font-size: 22px; font-weight: bold; color: #b91c1c;">' + atRiskCount + '</div>',
      '        <div style="font-size: 11px; color: #64748b; text-transform: uppercase;">At Risk (<75%)</div>',
      '      </div>',
      '    </div>',
      '    <table style="width: 100%; border-collapse: collapse; margin-top: 10px;">',
      '      <thead>',
      '        <tr style="background-color: #f1f5f9; text-align: left; font-size: 12px; color: #475569;">',
      '          <th style="padding: 10px;">ID</th>',
      '          <th style="padding: 10px;">Student Name</th>',
      '          <th style="padding: 10px;">Track</th>',
      '          <th style="padding: 10px; text-align: center;">Rate</th>',
      '          <th style="padding: 10px; text-align: center;">Status</th>',
      '        </tr>',
      '      </thead>',
      '      <tbody>',
      tableRowsHtml,
      '      </tbody>',
      '    </table>',
      '  </div>',
      '</div>'
    ].join('\n');

    GmailApp.sendEmail(adminEmail, emailSubject, 'Cohort Attendance Audit Completed: ' + averageCohortRate + '% Average.', {
      htmlBody: htmlReport,
      name: 'GDG Attendance Auditor'
    });

    console.log('Audit digest successfully sent to instructor at: ' + adminEmail);
  } catch (err) {
    console.error('Error in auditStudentAttendanceAndNotify: ' + err.message);
  }
}

// 1. Identify candidates eligible for certification (Attendance >= 80%)
const rateString = String(row[9]).replace('%', '');
const rateNumber = parseInt(rateString, 10) || 0;
const certificateStatus = row[11];

if (rateNumber >= 80 && certificateStatus !== 'ISSUED') {
  console.log('Generating certificate for eligible student: ' + fullName + ' (' + studentId + ')...');
}

// 2. Generate a unique cryptographic verification identifier
const uniqueVerificationId = 'GW-2026-' + Utilities.getUuid().substring(0, 8).toUpperCase();
console.log('Assigned verification code: ' + uniqueVerificationId);

// 3. Duplicate template presentation to isolate concurrent edits
const tempPresentationName = 'Temp_Cert_' + studentId;
const tempPresentationFile = templateFile.makeCopy(tempPresentationName, certsFolder);
const tempPresentation = SlidesApp.openById(tempPresentationFile.getId());
const targetSlide = tempPresentation.getSlides()[0];

// 4. Perform vector text substitutions and export to high-resolution PDF
targetSlide.replaceAllText('{{NAME}}', fullName);
targetSlide.replaceAllText('{{TRACK}}', track);
targetSlide.replaceAllText('{{DATE}}', todayFormatted);
targetSlide.replaceAllText('{{CERT_ID}}', uniqueVerificationId);

tempPresentation.saveAndClose();

// Export as PDF blob
const pdfBlob = tempPresentationFile.getAs(MimeType.PDF);
pdfBlob.setName('Certificate_' + studentId + '_' + fullName.replace(/\s+/g, '_') + '.pdf');

// 5. Store PDF in Drive, update Sheet, and send email with PDF attachment
const finalPdfFile = certsFolder.createFile(pdfBlob);
finalPdfFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
const pdfUrl = finalPdfFile.getUrl();

sheet.getRange(i + 1, 12).setValue('ISSUED');
sheet.getRange(i + 1, 13).setValue(pdfUrl);

GmailApp.sendEmail(email, 'Congratulations ' + fullName + ': Your GDG Certificate is Ready!', 'Your certificate is attached.', {
  htmlBody: emailBody,
  attachments: [pdfBlob],
  name: 'GDG Certification Office'
});

// 6. Delete temporary Google Slides working copy to keep Drive clean
tempPresentationFile.setTrashed(true);

/**
 * Practice 4: Vector E-Certificate Factory & PDF Dispatch
 * Service: SlidesApp + DriveApp + GmailApp + SpreadsheetApp
 * Author: Georges Byona (@glosings0n)
 */

function generateAndDeliverECertificates() {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');
    const templateId = props.getProperty('SLIDES_TEMPLATE_ID');
    const workshopFolderId = props.getProperty('WORKSHOP_FOLDER_ID');

    if (!spreadsheetId || !templateId || !workshopFolderId) {
      throw new Error('Required IDs missing from ScriptProperties. Run setupWorkshopEnvironment first.');
    }

    const workshopFolder = DriveApp.getFolderById(workshopFolderId);
    
    // Locate or create a dedicated sub-folder for issued PDF certificates
    const certFolderName = 'Issued_Certificates_PDF';
    const subFolders = workshopFolder.getFoldersByName(certFolderName);
    let certsFolder;
    if (subFolders.hasNext()) {
      certsFolder = subFolders.next();
    } else {
      certsFolder = workshopFolder.createFolder(certFolderName);
      certsFolder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const data = sheet.getDataRange().getValues();

    const templateFile = DriveApp.getFileById(templateId);
    const todayFormatted = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMMM dd, yyyy');

    let issuedCount = 0;

    // Scan students (columns: Rate = 9, Status = 11, URL = 12)
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const studentId = row[0];
      const fullName = row[1];
      const email = row[2];
      const track = row[3];
      const rateString = String(row[9]).replace('%', '');
      const rateNumber = parseInt(rateString, 10) || 0;
      const certificateStatus = row[11];

      // Criteria: Attendance >= 80% and not yet issued
      if (rateNumber >= 80 && certificateStatus !== 'ISSUED') {
        console.log('Generating certificate for: ' + fullName + ' (' + studentId + ')...');

        // Generate unique cryptographic-style verification ID
        const uniqueVerificationId = 'GW-2026-' + Utilities.getUuid().substring(0, 8).toUpperCase();

        // 1. Duplicate Slide template to avoid concurrency conflicts
        const tempPresentationName = 'Temp_Cert_' + studentId;
        const tempPresentationFile = templateFile.makeCopy(tempPresentationName, certsFolder);
        const tempPresentation = SlidesApp.openById(tempPresentationFile.getId());
        const targetSlide = tempPresentation.getSlides()[0];

        // 2. Perform vector text substitutions
        targetSlide.replaceAllText('{{NAME}}', fullName);
        targetSlide.replaceAllText('{{TRACK}}', track);
        targetSlide.replaceAllText('{{DATE}}', todayFormatted);
        targetSlide.replaceAllText('{{CERT_ID}}', uniqueVerificationId);

        tempPresentation.saveAndClose();

        // 3. Export presentation slide to PDF blob
        const pdfBlob = tempPresentationFile.getAs(MimeType.PDF);
        pdfBlob.setName('Certificate_' + studentId + '_' + fullName.replace(/\s+/g, '_') + '.pdf');

        // 4. Save PDF to certificates folder
        const finalPdfFile = certsFolder.createFile(pdfBlob);
        finalPdfFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
        const pdfUrl = finalPdfFile.getUrl();

        // 5. Clean up temporary slide presentation
        tempPresentationFile.setTrashed(true);

        // 6. Update Google Sheet records
        sheet.getRange(i + 1, 12).setValue('ISSUED');
        sheet.getRange(i + 1, 13).setValue(pdfUrl);

        // 7. Dispatch congratulations email with attached PDF using Figtree font
        const emailSubject = 'Congratulations ' + fullName + ': Your GDG Workspace Certificate is Ready!';
        const emailBody = [
          '<div style="font-family: 'Figtree', Arial, sans-serif; max-width: 600px; margin: 0 auto; border: 1px solid #cbd5e1; border-radius: 8px; padding: 24px; color: #1e293b;">',
          '  <h2 style="color: #02569B; margin-top: 0;">Certificate of Achievement Ready!</h2>',
          '  <p>Dear <strong>' + fullName + '</strong>,</p>',
          '  <p>Congratulations on completing the 5-day curriculum of <strong>GDG - Google Workspace Training</strong> with an attendance rate of <strong>' + rateNumber + '%</strong>.</p>',
          '  <p>Your verified certificate is attached to this email in PDF format.<br><strong>Verification ID:</strong> <code style="background-color: #f1f5f9; padding: 2px 6px; border-radius: 4px;">' + uniqueVerificationId + '</code></p>',
          '  <p>You can also view or download your certificate directly on Google Drive:<br><a href="' + pdfUrl + '" style="display: inline-block; margin-top: 12px; background-color: #00B0FF; color: #070a13; padding: 10px 20px; border-radius: 6px; text-decoration: none; font-weight: bold;">View Certificate on Drive</a></p>',
          '  <hr style="border: none; border-top: 1px solid #e2e8f0; margin: 24px 0;" />',
          '  <p style="font-size: 12px; color: #64748b;">GDG Afrique Francophone | GDG Pointe-Noire | GDG on Campus CUEA.<br>Powered by LosingTek & FlutterFire Summer Camp.</p>',
          '</div>'
        ].join('\n');

        GmailApp.sendEmail(email, emailSubject, 'Congratulations ' + fullName + '! Your certificate is attached.', {
          htmlBody: emailBody,
          attachments: [pdfBlob],
          name: 'GDG Certification Office'
        });

        issuedCount++;
      }
    }

    console.log('=== CERTIFICATE RUN COMPLETED ===');
    console.log('Total Certificates Issued & Delivered: ' + issuedCount);
  } catch (err) {
    console.error('Error in generateAndDeliverECertificates: ' + err.message);
  }
}

// 1. Identify students requiring academic remediation
const rateString = String(row[9]).replace('%', '');
const rateNumber = parseInt(rateString, 10) || 0;
const examScore = parseInt(row[10], 10) || 0;

if (rateNumber < 75 || examScore < 70) {
  console.log('Remedial review required for: ' + fullName + ' (' + email + ')');
}

// 2. Compute non-overlapping 30-minute meeting slots starting tomorrow at 14:00
const baseDate = new Date();
baseDate.setDate(baseDate.getDate() + 1); // Tomorrow
baseDate.setHours(14, 0, 0, 0); // 2:00 PM

const slotStart = new Date(baseDate.getTime() + slotOffsetMinutes * 60000);
const slotEnd = new Date(slotStart.getTime() + 30 * 60000); // 30-minute duration

slotOffsetMinutes += 40; // 30 min session + 10 min break

// 3. Book the Calendar event with guest invitation and description
const eventTitle = 'Office Hours: 1-on-1 Mentoring | ' + fullName + ' (' + studentId + ')';
const eventDescription = [
  'Hello ' + fullName + ',',
  '',
  'You have been scheduled for a personalized 1-on-1 review session for the GDG - Google Workspace Training (' + track + ').',
  'We will review your missed sessions and finalize your Day V Google Apps Script project.',
  '',
  'Format: 30 minutes via Google Meet',
  'Instructor: Georges Byona (@glosings0n)',
  'Community: GDG Afrique Francophone / GDG Pointe-Noire / GDG CUEA'
].join('\n');

const event = calendar.createEvent(eventTitle, slotStart, slotEnd, {
  guests: email,
  description: eventDescription,
  sendInvites: true
});

// 4. Set visual event color tag to Cyan
event.setColor(CalendarApp.EventColor.CYAN);
console.log('Booked office hours for ' + fullName + ' at ' + slotStart.toLocaleTimeString());

/**
 * Practice 5: Schedule Mentoring Office Hours with Auto Google Meet Links
 * Service: CalendarApp + SpreadsheetApp
 * Author: Georges Byona (@glosings0n)
 */

function scheduleMentoringSessionsWithMeet() {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');

    if (!spreadsheetId) {
      throw new Error('SPREADSHEET_ID missing from ScriptProperties.');
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const data = sheet.getDataRange().getValues();

    const calendar = CalendarApp.getDefaultCalendar();

    // 1. Start scheduling for tomorrow at 14:00 (2:00 PM)
    const baseDate = new Date();
    baseDate.setDate(baseDate.getDate() + 1);
    baseDate.setHours(14, 0, 0, 0);

    let slotOffsetMinutes = 0;
    let scheduledCount = 0;

    // 2. Iterate student records
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const studentId = row[0];
      const fullName = row[1];
      const email = row[2];
      const track = row[3];
      const rateString = String(row[9]).replace('%', '');
      const rateNumber = parseInt(rateString, 10) || 0;
      const examScore = parseInt(row[10], 10) || 0;

      // Identify students who require 1-on-1 office hours
      if (rateNumber < 75 || examScore < 70) {
        const slotStart = new Date(baseDate.getTime() + slotOffsetMinutes * 60000);
        const slotEnd = new Date(slotStart.getTime() + 30 * 60000); // 30-minute block

        const eventTitle = 'Office Hours: 1-on-1 Mentoring | ' + fullName + ' (' + studentId + ')';
        const eventDescription = [
          'Hello ' + fullName + ',',
          '',
          'You have been scheduled for a personalized 1-on-1 review session for the GDG - Google Workspace Training (' + track + ').',
          'We will review your missed sessions and finalize your Day V Google Apps Script project.',
          '',
          'Format: 30 minutes via Google Meet',
          'Instructor: Georges Byona (@glosings0n)',
          'Community: GDG Afrique Francophone / GDG Pointe-Noire / GDG CUEA'
        ].join('\n');

        // Create the event with guest invitation
        const event = calendar.createEvent(eventTitle, slotStart, slotEnd, {
          guests: email,
          description: eventDescription,
          sendInvites: true
        });

        // Set color tag to Highlight (Cyan/Blue in Calendar)
        event.setColor(CalendarApp.EventColor.CYAN);

        console.log('Scheduled office hours for ' + fullName + ' (' + email + ') at ' + slotStart.toLocaleTimeString());

        slotOffsetMinutes += 40; // 30 min session + 10 min break
        scheduledCount++;
      }
    }

    console.log('=== CALENDAR SCHEDULING COMPLETE ===');
    console.log('Total Remedial Office Hour Sessions Booked: ' + scheduledCount);
  } catch (err) {
    console.error('Error in scheduleMentoringSessionsWithMeet: ' + err.message);
  }
}

// 1. Instantiate the Google Doc and relocate it to the workshop folder
const docTitle = 'GDG_Workspace_Training_2026_Final_Report';
const doc = DocumentApp.create(docTitle);
const docFile = DriveApp.getFileById(doc.getId());

const workshopFolder = DriveApp.getFolderById(workshopFolderId);
workshopFolder.addFile(docFile);
DriveApp.getRootFolder().removeFile(docFile);

// 2. Configure page header, footer with dynamic page numbering, and Figtree font
const header = doc.addHeader();
const headerP = header.appendParagraph('GDG - Google Workspace Training 2026 | Official Retrospective');
headerP.setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
headerP.setFontFamily('Figtree').setFontSize(9).setForegroundColor('#64748b');

const footer = doc.addFooter();
const footerP = footer.appendParagraph('Page ');
footerP.appendPageNumber();
footerP.appendParagraphText(' | Author: Georges Byona (@glosings0n)');
footerP.setAlignment(DocumentApp.HorizontalAlignment.CENTER);
footerP.setFontFamily('Figtree').setFontSize(9).setForegroundColor('#94a3b8');

// 3. Append styled headings, summary paragraphs, and a formatted KPI metrics table
const body = doc.getBody();

const titleP = body.appendParagraph('Executive Summary & Evaluation Report');
titleP.setHeading(DocumentApp.ParagraphHeading.HEADING1);
titleP.setFontFamily('Figtree').setFontSize(22).setBold(true).setForegroundColor('#02569B');

body.appendParagraph(
  'This document summarizes the outcomes of the 5-Day Google Workspace Training curriculum ' +
  'delivered across GDG Afrique Francophone, GDG Pointe-Noire (Congo-Brazzaville), and GDG on Campus CUEA (Nairobi, Kenya).'
).setFontFamily('Figtree').setFontSize(11).setLineSpacing(1.3);

const tableData = [
  ['Evaluation Metric', 'Recorded Value', 'Benchmark Target', 'Compliance Status'],
  ['Total Enrolled Participants', String(total), '>= 8 per session', 'OPTIMAL'],
  ['Average Examination Score', avgScore + '/100', '>= 75/100', avgScore >= 75 ? 'SURPASSED' : 'REQUIRES ATTENTION'],
  ['Graduation & E-Certificate Rate', certPercentage + '% (' + certified + '/' + total + ')', '>= 70%', 'ACHIEVED']
];

const table = body.appendTable(tableData);
const headerRow = table.getRow(0);
for (let c = 0; c < 4; c++) {
  headerRow.getCell(c).setBackgroundColor('#02569B');
  headerRow.getCell(c).getChild(0).asParagraph().setFontFamily('Figtree').setFontColor('#FFFFFF').setBold(true);
}

// 4. Export static PDF archive and configure Drive sharing governance
doc.saveAndClose();

const pdfBlob = docFile.getAs(MimeType.PDF);
pdfBlob.setName(docTitle + '.pdf');
const pdfFile = workshopFolder.createFile(pdfBlob);

// Make files accessible via link while protecting edit permissions
docFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
pdfFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

console.log('Archived PDF Report URL: ' + pdfFile.getUrl());

/**
 * Practice 6: Programmatic Workshop Evaluation Doc & Drive Sharing
 * Service: DocumentApp + DriveApp + SpreadsheetApp
 * Author: Georges Byona (@glosings0n)
 */

function generateWorkshopEvaluationDoc() {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');
    const workshopFolderId = props.getProperty('WORKSHOP_FOLDER_ID');

    if (!spreadsheetId || !workshopFolderId) {
      throw new Error('Required IDs missing from ScriptProperties.');
    }

    const workshopFolder = DriveApp.getFolderById(workshopFolderId);
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const data = sheet.getDataRange().getValues();

    const docTitle = 'GDG_Workspace_Training_2026_Final_Report';
    const doc = DocumentApp.create(docTitle);
    const docFile = DriveApp.getFileById(doc.getId());

    // Relocate file into dedicated workshop folder
    workshopFolder.addFile(docFile);
    DriveApp.getRootFolder().removeFile(docFile);

    const body = doc.getBody();

    // 1. Configure Header and Footer with Figtree font
    const header = doc.addHeader();
    const headerP = header.appendParagraph('GDG - Google Workspace Training 2026 | Official Retrospective');
    headerP.setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
    headerP.setFontFamily('Figtree').setFontSize(9).setForegroundColor('#64748b');

    const footer = doc.addFooter();
    const footerP = footer.appendParagraph('Page ');
    footerP.appendPageNumber();
    footerP.appendParagraphText(' | Author: Georges Byona (@glosings0n)');
    footerP.setAlignment(DocumentApp.HorizontalAlignment.CENTER);
    footerP.setFontFamily('Figtree').setFontSize(9).setForegroundColor('#94a3b8');

    // 2. Add Main Document Titles
    const titleP = body.appendParagraph('Executive Summary & Evaluation Report');
    titleP.setHeading(DocumentApp.ParagraphHeading.HEADING1);
    titleP.setFontFamily('Figtree').setFontSize(22).setBold(true).setForegroundColor('#02569B');

    const subtitleP = body.appendParagraph('Cohort Performance, Attendance Metrics & Certification Outcomes');
    subtitleP.setHeading(DocumentApp.ParagraphHeading.SUBTITLE);
    subtitleP.setFontFamily('Figtree').setFontSize(13).setForegroundColor('#475569');

    body.appendHorizontalRule();

    // 3. Add Context Paragraph
    body.appendParagraph(
      'This document summarizes the outcomes of the 5-Day Google Workspace Training curriculum ' +
      'delivered across GDG Afrique Francophone, GDG Pointe-Noire (Congo-Brazzaville), and GDG on Campus CUEA (Nairobi, Kenya). ' +
      'All attendance records and certifications were processed automatically via Google Apps Script.'
    ).setFontFamily('Figtree').setFontSize(11).setLineSpacing(1.3);

    // 4. Calculate Aggregate Metrics
    let total = data.length - 1;
    let certified = 0;
    let cumulativeScore = 0;

    for (let i = 1; i < data.length; i++) {
      if (data[i][11] === 'ISSUED') certified++;
      cumulativeScore += (parseInt(data[i][10], 10) || 0);
    }

    const avgScore = Math.round(cumulativeScore / total);
    const certPercentage = Math.round((certified / total) * 100);

    // 5. Insert Formatted KPI Table
    body.appendParagraph('Key Performance Indicators')
      .setHeading(DocumentApp.ParagraphHeading.HEADING2)
      .setFontFamily('Figtree')
      .setFontSize(15)
      .setForegroundColor('#02569B');

    const tableData = [
      ['Evaluation Metric', 'Recorded Value', 'Benchmark Target', 'Compliance Status'],
      ['Total Enrolled Participants', String(total), '>= 8 per session', 'OPTIMAL'],
      ['Average Examination Score', avgScore + '/100', '>= 75/100', avgScore >= 75 ? 'SURPASSED' : 'REQUIRES ATTENTION'],
      ['Graduation & E-Certificate Rate', certPercentage + '% (' + certified + '/' + total + ')', '>= 70%', certPercentage >= 70 ? 'ACHIEVED' : 'INCOMPLETE'],
      ['Automated Infrastructure', 'Google Apps Script (V8)', 'Zero-Server Stack', 'VERIFIED']
    ];

    const table = body.appendTable(tableData);
    
    // Style Table Header Row
    const headerRow = table.getRow(0);
    for (let c = 0; c < 4; c++) {
      headerRow.getCell(c).setBackgroundColor('#02569B');
      headerRow.getCell(c).getChild(0).asParagraph().setFontFamily('Figtree').setFontColor('#FFFFFF').setBold(true);
    }

    // 6. Export PDF Archival Copy
    doc.saveAndClose();
    const pdfBlob = docFile.getAs(MimeType.PDF);
    pdfBlob.setName(docTitle + '.pdf');
    const pdfFile = workshopFolder.createFile(pdfBlob);

    // 7. Configure Granular Sharing Governance
    docFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    pdfFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

    console.log('=== EVALUATION DOC GENERATION COMPLETE ===');
    console.log('Google Doc Editable URL: ' + docFile.getUrl());
    console.log('Archived PDF Report URL: ' + pdfFile.getUrl());
  } catch (err) {
    console.error('Error in generateWorkshopEvaluationDoc: ' + err.message);
  }
}

// 1. HTTP GET Entry Point for Web App requests
function doGet(e) {
  const htmlOutput = HtmlService.createTemplateFromFile('Index')
    .evaluate()
    .setTitle('GDG Workspace Training | Student Portal')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1.0')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  
  return htmlOutput;
}

// 2. Remote Procedure Call (RPC) to safely extract and format sheet data
function getDashboardData() {
  const props = PropertiesService.getScriptProperties();
  const spreadsheetId = props.getProperty('SPREADSHEET_ID');
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('Attendance_Master');
  const values = sheet.getDataRange().getValues();

  const students = [];
  let eligibleCount = 0;
  let totalScore = 0;

  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const rateNum = parseInt(String(row[9]).replace('%', ''), 10) || 0;
    const score = parseInt(row[10], 10) || 0;
    if (rateNum >= 80) eligibleCount++;
    totalScore += score;

    students.push({
      id: row[0],
      name: row[1],
      email: row[2],
      track: row[3],
      rate: rateNum,
      score: score,
      status: row[11],
      certUrl: row[12]
    });
  }

  return {
    success: true,
    totalStudents: students.length,
    eligibleStudents: eligibleCount,
    averageScore: students.length ? Math.round(totalScore / students.length) : 0,
    students: students
  };
}

<!-- 3. Google Fonts Figtree & JetBrains Mono Imports in Index.html -->
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=Figtree:ital,wght@0,300..900;1,300..900&family=JetBrains+Mono:wght@400;600&display=swap" rel="stylesheet">
<style>
  body {
    font-family: 'Figtree', -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
    margin: 0;
    padding: 24px;
    background-color: #070a13;
    color: #f8fafc;
  }
  .card-num {
    font-family: 'JetBrains Mono', monospace;
    font-size: 28px;
    font-weight: bold;
    color: #00B0FF;
  }
</style>

// 4. Asynchronous Client-Side Call via google.script.run
google.script.run
  .withSuccessHandler(renderDashboard)
  .withFailureHandler(handleError)
  .getDashboardData();

function renderDashboard(res) {
  document.getElementById('stat-total').innerText = res.totalStudents;
  document.getElementById('stat-eligible').innerText = res.eligibleStudents;
  document.getElementById('stat-score').innerText = res.averageScore + '/100';
  displayStudentList(res.students);
}

/**
 * Practice 7: Web App Server Backend
 * Service: HtmlService + SpreadsheetApp
 * Author: Georges Byona (@glosings0n)
 */

// Entry point when visiting the Web App URL in a web browser
function doGet(e) {
  const htmlOutput = HtmlService.createTemplateFromFile('Index')
    .evaluate()
    .setTitle('GDG Workspace Training | Student Portal')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1.0')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  
  return htmlOutput;
}

// Remote Procedure Call (RPC) callable from frontend JavaScript
function getDashboardData() {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');

    if (!spreadsheetId) {
      return { error: 'Spreadsheet ID not initialized in ScriptProperties.' };
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const values = sheet.getDataRange().getValues();

    const students = [];
    let eligibleCount = 0;
    let totalScore = 0;

    for (let i = 1; i < values.length; i++) {
      const row = values[i];
      const rateNum = parseInt(String(row[9]).replace('%', ''), 10) || 0;
      const score = parseInt(row[10], 10) || 0;
      const status = row[11];
      const certUrl = row[12];

      if (rateNum >= 80) eligibleCount++;
      totalScore += score;

      students.push({
        id: row[0],
        name: row[1],
        email: row[2],
        track: row[3],
        rate: rateNum,
        score: score,
        status: status,
        certUrl: certUrl
      });
    }

    return {
      success: true,
      totalStudents: students.length,
      eligibleStudents: eligibleCount,
      averageScore: students.length ? Math.round(totalScore / students.length) : 0,
      students: students
    };
  } catch (err) {
    return { error: err.message };
  }
}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <!-- Google Fonts: Figtree & JetBrains Mono -->
  <link rel="preconnect" href="https://fonts.googleapis.com">
  <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
  <link href="https://fonts.googleapis.com/css2?family=Figtree:ital,wght@0,300..900;1,300..900&family=JetBrains+Mono:wght@400;600&display=swap" rel="stylesheet">
  <style>
    body {
      font-family: 'Figtree', -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
      margin: 0;
      padding: 24px;
      background-color: #070a13;
      color: #f8fafc;
    }
    .container {
      max-width: 900px;
      margin: 0 auto;
    }
    .header {
      border-bottom: 1px solid #202b46;
      padding-bottom: 16px;
      margin-bottom: 24px;
      display: flex;
      justify-content: space-between;
      align-items: center;
    }
    h1 { margin: 0; font-size: 22px; color: #00B0FF; }
    .subtitle { margin: 4px 0 0 0; color: #94a3b8; font-size: 14px; }
    
    .stats-grid {
      display: grid;
      grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
      gap: 16px;
      margin-bottom: 24px;
    }
    .card {
      background: #0f1424;
      border: 1px solid #202b46;
      border-radius: 8px;
      padding: 16px;
      text-align: center;
    }
    .card-num { font-size: 28px; font-weight: bold; color: #00B0FF; font-family: 'JetBrains Mono', monospace; }
    .card-label { font-size: 12px; color: #94a3b8; text-transform: uppercase; margin-top: 4px; }
    
    .search-box {
      width: 100%;
      padding: 12px 16px;
      background: #0f1424;
      border: 1px solid #202b46;
      border-radius: 6px;
      color: #ffffff;
      font-family: 'Figtree', sans-serif;
      font-size: 14px;
      box-sizing: border-box;
      margin-bottom: 20px;
    }
    
    table {
      width: 100%;
      border-collapse: collapse;
      background: #0f1424;
      border: 1px solid #202b46;
      border-radius: 8px;
      overflow: hidden;
    }
    th {
      background: #171d32;
      color: #94a3b8;
      font-size: 12px;
      text-align: left;
      padding: 12px;
    }
    td {
      padding: 12px;
      border-top: 1px solid #202b46;
      font-size: 13px;
    }
    .badge {
      display: inline-block;
      padding: 3px 8px;
      border-radius: 4px;
      font-size: 11px;
      font-weight: bold;
    }
    .badge-issued { background: rgba(34, 197, 94, 0.2); color: #4ade80; border: 1px solid #22c55e; }
    .badge-pending { background: rgba(234, 179, 8, 0.2); color: #facc15; border: 1px solid #eab308; }
    .btn-cert {
      color: #00B0FF;
      text-decoration: none;
      font-weight: bold;
    }
    .loading { text-align: center; padding: 40px; color: #94a3b8; }
  </style>
</head>
<body>
  <div class="container">
    <div class="header">
      <div>
        <h1>GDG Workspace Training Portal</h1>
        <p class="subtitle">Real-time Student Dashboard & Certificate Verification</p>
      </div>
    </div>

    <div class="stats-grid">
      <div class="card">
        <div class="card-num" id="stat-total">--</div>
        <div class="card-label">Total Students</div>
      </div>
      <div class="card">
        <div class="card-num" id="stat-eligible">--</div>
        <div class="card-label">Certificate Eligible</div>
      </div>
      <div class="card">
        <div class="card-num" id="stat-score">--</div>
        <div class="card-label">Average Exam Score</div>
      </div>
    </div>

    <input type="text" id="search-input" class="search-box" placeholder="Search by name, student ID, or track..." oninput="filterTable()">

    <div id="table-container">
      <div class="loading" id="loading-msg">Connecting to Google Sheets backend...</div>
    </div>
  </div>

  <script>
    let allStudents = [];

    google.script.run
      .withSuccessHandler(renderDashboard)
      .withFailureHandler(handleError)
      .getDashboardData();

    function renderDashboard(res) {
      if (res.error) {
        document.getElementById('table-container').innerHTML = '<div style="color: #ef4444;">Error: ' + res.error + '</div>';
        return;
      }

      document.getElementById('stat-total').innerText = res.totalStudents;
      document.getElementById('stat-eligible').innerText = res.eligibleStudents;
      document.getElementById('stat-score').innerText = res.averageScore + '/100';

      allStudents = res.students;
      displayStudentList(allStudents);
    }

    function displayStudentList(students) {
      if (students.length === 0) {
        document.getElementById('table-container').innerHTML = '<div class="loading">No matching records found.</div>';
        return;
      }

      let html = '<table><thead><tr>' +
        '<th>ID</th><th>Name</th><th>Track</th><th>Attendance</th><th>Exam</th><th>Status</th><th>Certificate</th>' +
        '</tr></thead><tbody>';

      students.forEach(s => {
        const badgeClass = s.status === 'ISSUED' ? 'badge-issued' : 'badge-pending';
        const certAction = s.certUrl ? '<a class="btn-cert" href="' + s.certUrl + '" target="_blank">View PDF →</a>' : '<span style="color:#64748b;">In Progress</span>';
        
        html += '<tr>' +
          '<td style="font-family: 'JetBrains Mono', monospace;">' + s.id + '</td>' +
          '<td style="font-weight: 500;">' + s.name + '</td>' +
          '<td style="color: #94a3b8;">' + s.track + '</td>' +
          '<td style="text-align: center;">' + s.rate + '%</td>' +
          '<td style="text-align: center;">' + s.score + '</td>' +
          '<td><span class="badge ' + badgeClass + '">' + s.status + '</span></td>' +
          '<td>' + certAction + '</td>' +
          '</tr>';
      });

      html += '</tbody></table>';
      document.getElementById('table-container').innerHTML = html;
    }

    function filterTable() {
      const q = document.getElementById('search-input').value.toLowerCase();
      const filtered = allStudents.filter(s => 
        s.name.toLowerCase().includes(q) || 
        s.id.toLowerCase().includes(q) || 
        s.track.toLowerCase().includes(q)
      );
      displayStudentList(filtered);
    }

    function handleError(err) {
      document.getElementById('table-container').innerHTML = '<div style="color: #ef4444;">RPC Failure: ' + err.message + '</div>';
    }
  </script>
</body>
</html>

// 1. Purge pre-existing triggers to guarantee execution idempotency
const functionName = 'auditStudentAttendanceAndNotify';
const currentTriggers = ScriptApp.getProjectTriggers();

currentTriggers.forEach(trigger => {
  if (trigger.getHandlerFunction() === functionName) {
    ScriptApp.deleteTrigger(trigger);
    console.log('Removed duplicate trigger for: ' + functionName);
  }
});

// 2. Programmatically schedule daily execution at 18:00 (6:00 PM)
ScriptApp.newTrigger(functionName)
  .timeBased()
  .everyDays(1)
  .atHour(18)
  .create();

console.log('Trigger installed: ' + functionName + ' will run daily at 18:00.');

// 3. Serialize cohort metrics and dispatch an HTTP POST request
const payload = {
  text: '*GDG Workspace Training Cohort Update*\n' +
        '• *Total Students:* ' + total + '\n' +
        '• *Certificate Eligible:* ' + certified + '\n' +
        '• *At Risk (<75%):* ' + atRisk + '\n' +
        '• *Automated via:* Google Apps Script V8 (Day V Workshop)'
};

const options = {
  method: 'post',
  contentType: 'application/json',
  payload: JSON.stringify(payload),
  muteHttpExceptions: true
};

const response = UrlFetchApp.fetch(webhookUrl, options);
console.log('Webhook dispatched. Status Code: ' + response.getResponseCode());

/**
 * Practice 8: Programmatic Time Triggers & Outbound Webhook Integration
 * Service: ScriptApp + UrlFetchApp
 * Author: Georges Byona (@glosings0n)
 */

// 1. Programmatically install a nightly time-driven trigger
function installDailyAuditTrigger() {
  const functionName = 'auditStudentAttendanceAndNotify';
  
  // Clean up any existing trigger for the same function to avoid duplicate runs
  const currentTriggers = ScriptApp.getProjectTriggers();
  currentTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === functionName) {
      ScriptApp.deleteTrigger(trigger);
      console.log('Deleted pre-existing trigger for: ' + functionName);
    }
  });

  // Schedule to execute daily at 18:00 (6:00 PM)
  ScriptApp.newTrigger(functionName)
    .timeBased()
    .everyDays(1)
    .atHour(18)
    .create();

  console.log('=== AUTOMATIC TRIGGER INSTALLED ===');
  console.log('Function "' + functionName + '" will now run automatically every day at 18:00.');
}

// 2. Dispatch cohort summary to an external Slack / Discord Webhook
function broadcastCohortSummaryWebhook(webhookUrl) {
  try {
    const props = PropertiesService.getScriptProperties();
    const spreadsheetId = props.getProperty('SPREADSHEET_ID');

    if (!spreadsheetId) {
      throw new Error('SPREADSHEET_ID not found in ScriptProperties.');
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName('Attendance_Master');
    const data = sheet.getDataRange().getValues();

    let total = data.length - 1;
    let certified = 0;
    let atRisk = 0;

    for (let i = 1; i < data.length; i++) {
      const rateNum = parseInt(String(data[i][9]).replace('%', ''), 10) || 0;
      if (rateNum >= 80) certified++;
      if (rateNum < 75) atRisk++;
    }

    const payload = {
      text: '*GDG Workspace Training Cohort Update*\n' +
            '• *Total Students:* ' + total + '\n' +
            '• *Certificate Eligible:* ' + certified + '\n' +
            '• *At Risk (<75%):* ' + atRisk + '\n' +
            '• *Automated via:* Google Apps Script V8 (Day V Workshop)'
    };

    const options = {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };

    // If no webhook URL is passed, log payload to execution log
    if (!webhookUrl || !webhookUrl.startsWith('http')) {
      console.log('No external webhook URL provided. Payload preview:');
      console.log(JSON.stringify(payload, null, 2));
      return;
    }

    const response = UrlFetchApp.fetch(webhookUrl, options);
    console.log('Webhook successfully posted. Response Code: ' + response.getResponseCode());
  } catch (err) {
    console.error('Error in broadcastCohortSummaryWebhook: ' + err.message);
  }
}

{
  "timeZone": "Africa/Nairobi",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/presentations",
    "https://www.googleapis.com/auth/gmail.send",
    "https://www.googleapis.com/auth/contacts",
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/documents",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}