const SHEET_URL = "https://docs.google.com/spreadsheets/d/1PtyWgV6Zs3Mi1i7X7wEKdssKwk99plsgp4wwI4FJuyE/edit#gid=0";
const SHEET_NAME = "CRUDIII";

function doGet(e) {
  
  var ss = SpreadsheetApp.openById("1PtyWgV6Zs3Mi1i7X7wEKdssKwk99plsgp4wwI4FJuyE");
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();
  
  // Create/Add new record, using comma separated values
  if ( fn == 'CREATE' ) {
    var data = e.parameter.DATA.split(',');
    sh.appendRow(data);
    return ContentService.createTextOutput("New record created");
  }
  
  // Reads/Returns all data as a stringified JSON List
  else if ( fn == 'READ' ) {
    return ContentService.createTextOutput(JSON.stringify(rg));     
  }
  
  // Edit/Update existing record, requires index/row and current col1 to match
  else if ( fn == 'UPDATE' ) {
    var index = e.parameter.INDEX;  //index in list
    var col1 = e.parameter.COL1;  // current/existing value of col1 - it could be replaced...
    var data = e.parameter.DATA.split(','); //new data
    var range = sh.getRange((parseInt(index)+1),1,1,data.length);
    for (var i = 0; i < rg.length; i++ ) {
      if ( index != undefined && i == index && col1 == rg[i][0] ) {
        range.setValues([data]);
      } 
    }
    return ContentService.createTextOutput("Record updated");    
    }
  
  // deletes a single record (and its row) from sheet. Requires row index and col1 to match
  else if ( fn == 'DELETE' ) {
    var index = e.parameter.INDEX;  //index in list
    var col1 = e.parameter.COL1;  // current/existing value of col1 - it could be replaced...
    for (var i = 0; i < rg.length; i++ ) {
      if ( index != undefined && i == index && col1 == rg[i][0] ) {
        sh.deleteColumns(parseInt(index)+1).clearContents();
      } 
    }
    return ContentService.createTextOutput("Existing record deleted");    
   }
    
  // outputs results from SQL query of all data  
  else if ( fn == 'QUERY' ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var sql = e.parameter.SQL;
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }
  else if (e.parameter.fn == "Fetch Message") {
          return LoadData();
      
  } else if (e.parameter.fn == "Send SMS") {
          return UpdateStatus(e.parameter.row);
          
    } else if (e.parameter.fn == "Refresh") {
          return clearTheMessage();
   } 
}

function LoadData() {
      var ss = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME);     
      const [header, ...data] = ss.getDataRange().getDisplayValues();
      const PHONE = header.indexOf('Phone');
      const TEXT = header.indexOf('Text');
      const STATUS = header.indexOf('Status');
      const output = [];      
      data.forEach((row, index) => {
                   
                   if (row[STATUS] === '') {
        output.push([index + 1, row[PHONE], row[TEXT]]);
      }
    });
    
    const json = JSON.stringify(output);
    return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.TEXT);
}

function UpdateStatus(row) {
  var ss = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME);
   const [header] = ss.getRange('D1:1').getValues();
   const STATUS = header.indexOf('Status');
   var rowId = Number(row);
   ss.getRange(rowId + 1, STATUS + 1).setValue('SMS Sent');
   return ContentService.createTextOutput('').setMimeType(
           ContentService.MimeType.TEXT);
}

function clearTheMessage() {
  var sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME);
  var data = sheet.getDataRange().getDisplayValues();
  var range = [];
      data.forEach(function(e, i){
    if (e[0] == "SMS Sent") range.push("F" + (i + 1));
  });
  sheet.getRange("F2:F").clearContent();
}