var SPREADSHEET_ID = "1hAy1UTsveZgXrn-jh--CIi_0DjbLHGWsLrvEpUFSbUM"; 
const TEMPLATE_ID = '1meEi_o8SkIcjuhHRcV6lcqjJ2WfuVLXomYUzsCmY-Yo'; 
const FOLDER_ID = '16LTPfF6KncoUfM2q68AtmMSkjp4ECG5m'; 

// 1. Fungsi GET untuk sinkronisasi tabel Real-time
function doGet(e) {
  var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheets()[0];
  var data = sheet.getDataRange().getValues();
  if (data.length <= 1) {
    return ContentService.createTextOutput(JSON.stringify({ status: 'success', data: [] }))
      .setMimeType(ContentService.MimeType.JSON);
  }
  var headers = data[0];
  var rows = data.slice(1).map(function(row) {
    var obj = {};
    headers.forEach(function(header, idx) {
      obj[header.toString().toLowerCase().trim()] = row[idx];
    });
    return obj;
  });
  return ContentService.createTextOutput(JSON.stringify({ status: 'success', data: rows }))
    .setMimeType(ContentService.MimeType.JSON);
}

// 2. Fungsi POST untuk menerima pengisian Formulir
function doPost(e) {
  try {
    var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheets()[0];
    var postData = JSON.parse(e.postData.contents);
    
    var timestamp = new Date();
    var email = postData.email || '';
    var nama = postData.nama || '';
    var instansi = postData.instansi || '';
    var status = "Proses"; // Status Awal
    
    // Pastikan header ada
    if (sheet.getLastRow() === 0) {
      sheet.appendRow(['Timestamp', 'Email', 'Nama', 'Instansi', 'Status', 'Link Sertifikat']);
    }
    
    sheet.appendRow([timestamp, email, nama, instansi, status, '']);
    
    return ContentService.createTextOutput(JSON.stringify({
      status: 'success',
      message: 'Data ' + nama + ' berhasil terkirim!',
      data: { email: email, nama: nama, instansi: instansi, timestamp: timestamp }
    })).setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService.createTextOutput(JSON.stringify({
      status: 'error',
      message: err.toString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

// 3. Fungsi Eksekutor Pembuatan Sertifikat (Dapat dijadwalkan via Trigger)
function buatDanKirimSertifikat() {
  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheets()[0];
  const data = sheet.getDataRange().getValues();
  const targetFolder = DriveApp.getFolderById(FOLDER_ID); 
  
  for (let i = 1; i < data.length; i++) {
    const email = data[i][1];  // Kolom B
    const nama = data[i][2];   // Kolom C
    const status = data[i][4]; // Kolom E
    
    if (nama !== "" && email !== "" && status !== "Terkirim") {
      const templateFile = DriveApp.getFileById(TEMPLATE_ID);
      const fileCopy = templateFile.makeCopy('Temp - ' + nama);
      const copyId = fileCopy.getId();
      
      const slide = SlidesApp.openById(copyId);
      slide.replaceAllText('<>', nama);
      slide.saveAndClose(); 
      
      const pdfBlob = DriveApp.getFileById(copyId).getAs(MimeType.PDF);
      const pdfFile = targetFolder.createFile(pdfBlob);
      pdfFile.setName('Sertifikat - ' + nama + '.pdf'); 
      const pdfUrl = pdfFile.getUrl();
      
      const subjectEmail = 'Sertifikat Penghargaan - ' + nama;
      const bodyEmail = 'Halo ' + nama + ',\n\nTerima kasih telah berpartisipasi. Terlampir adalah sertifikat Anda.\n\nSalam,\nPanitia';
      
      GmailApp.sendEmail(email, subjectEmail, bodyEmail, {
        attachments: [pdfFile.getAs(MimeType.PDF)],
        name: 'Panitia Acara' 
      });
      
      DriveApp.getFileById(copyId).setTrashed(true);
      
      sheet.getRange(i + 1, 5).setValue("Terkirim"); // Update E
      sheet.getRange(i + 1, 6).setValue(pdfUrl);     // Update F
      
      Utilities.sleep(1000);
    }
  }
}