/**
 * Author: Martin Alvarez-Espinar (ePSI Platform)
 * 
 * Script to update the scoreboard visualizations from a Google Spreadsheet to a FusionTable 
 * The source (1): https://docs.google.com/spreadsheets/d/1P3jXcDgFQml4e8MKdvcq9Gzii-BmKf5fo0XCpr4Wmf8/edit
 * The fusion table (2): https://www.google.com/fusiontables/DataSource?docid=1vcvLk9vJYZhHu9W_Mb5R7HdHiEZx6M0i2xy8kJA
 *
 * The scripts takes a range of cells with the overall information of all countries (in 1) and makes a copy of values
 * into the fusiontable (2). 
 *
 * This script uses oAuth2.0 in order to authenticate and get access to the services.
 * This script requires the library OAuth2 for Apps Script [https://github.com/googlesamples/apps-script-oauth2], so 
 * first, it needs to be installed: "Resources > Libraries..." -> "Find a Library" text box, enter the project key 
 * "MswhXl8fVhTFUH_Q3UOJbXvxhMjh3Sh48" and click the "Select" button. Choose a version in the dropdown box and "Save".
 * 
 */


// Disclaimer to show when the spreasheet is loaded and needs authentication    
var HTML_OAUTH_DIALOG ='<h3>PSI Scoreboard Update</h3>\
      <p>In order to update the visualization of the ePSI Scoreboard you have to grant this application.</p>\
      <p style="text-align:center"><a href="<?= authorizationUrl ?>" onclick="google.script.host.close()">Authorize me</a></p>';

// the ID of the FusionTable where the scoreboard is represented / visualised
var tableIDFusion = '1vcvLk9vJYZhHu9W_Mb5R7HdHiEZx6M0i2xy8kJA';
 
// Constants in ScriptProperties:
// FUSIONTABLES_API_KEY <- API Key for FusionTables service
// PROJECT_KEY <- Key for the project (Spreadsheet)
// CLIENT_ID            <- Client ID for oAuth2.0
// CLIENT_SECRET        <- Client Secret for oAuth2.0
// It's under script properties due to this script is shared with several people

// the name of the range of cells used in the script to update the values
// This range must include all columns including headers (Country, Type, score, indicators)
// + All values
var rangeName = 'FusionTableUpdate'; 
// The key of the project (to use the API)
var PROJECT_KEY = @@@THE_PROJECT_KEY_HERE@@@;

var TITLE_PROJECT = 'PSI Scoreboard Update';
// Constants for OAUTH2
var AUTHORIZE_URL = 'https://accounts.google.com/o/oauth2/auth'; //step 1. we can actually start directly here if that is necessary
var TOKEN_URL = 'https://accounts.google.com/o/oauth2/token'; //step 2. after we get the callback, go get token
var REDIRECT_URL= 'https://script.google.com/macros/d/'+PROJECT_KEY+'/usercallback';

// Creates and return the service to use the FusionTables service based on OAuth 2.0
function getFusionTablesService() {
  return OAuth2.createService('fusiontables')
      // Set the endpoint URLs, which are the same for all Google services.
      .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')
      // Set the client ID and secret, from the Google Developers Console.
      .setClientId(ScriptProperties.getProperty('CLIENT_ID'))
      .setClientSecret(ScriptProperties.getProperty('CLIENT_SECRET'))
      // Set the name of the callback function in the script referenced
      // above that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')
      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())
      // Set the scopes to request (space-separated for Google services).
      .setScope('https://www.googleapis.com/auth/fusiontables')
      // Below are Google-specific OAuth2 parameters.
      // Sets the login hint, which will prevent the account chooser screen
      // from being shown to users logged in with multiple accounts.
      .setParam('login_hint', Session.getActiveUser().getEmail())
      // Forces the approval prompt every time. This is useful for testing,
      // but not desirable in a production application.
      .setParam('approval_prompt', 'force');
}

/**
 * Main function.
 * When the spreadsheet is open it creates menu buttons
 **/
function onOpen() {
    var ui = SpreadsheetApp.getUi();
    // OAuth launcher dialog to update the data
    showAuthDialog();
    // checks if the user is has access to the FusionTable
    var fusionTablesService = getFusionTablesService();
    ui.createMenu('ePSI Scoreboard')
      .addItem('Update Visualizations', 'updateScoreboard')
      .addToUi();
};

// Shows the dialog with the link to get the authorization
function showAuthDialog() {
    var fusionTablesService = getFusionTablesService();
    // The auth Url
    var authorizationUrl = fusionTablesService.getAuthorizationUrl();
    if (!fusionTablesService.hasAccess()) {
        // Creates and lauches the dialog with a button to initiate the oauth process
        var template = HtmlService.createTemplate(HTML_OAUTH_DIALOG);
        template.authorizationUrl = authorizationUrl;
        var page= template.evaluate();
        SpreadsheetApp.getUi().showModalDialog(page, 'ePSI Scoreboard Update - Authorization' ); 
    } else {
        Logger.log("Already logged in");
    }
}

// Callback function that will be run after the oauth process 
function authCallback(request) {
    var fusionTablesService = getFusionTablesService();
    var isAuthorized = fusionTablesService.handleCallback(request);
    if (isAuthorized) {
        return HtmlService.createHtmlOutput('Success! You can close this tab and submit the changes.');
    } else {
        return HtmlService.createHtmlOutput('Denied. You can close this tab and contact with the administrator');
    }
}
 
/**
 * Checks if the app has access to the fusion table service. 
 * If not, shows a sidebar for authentification.
 * If granted, updates the fusion table
 **/
function updateScoreboard() {
    var fusionTablesService = getFusionTablesService();
    if (!fusionTablesService.hasAccess()) {
        showAuthDialog();    
    } else {
        updateData(tableIDFusion);
    }
};
 
// query fusion API post
function queryFusionTables(query) {
    // location to send the infomation to
    var prefix = "https://www.googleapis.com/fusiontables/v1/query?key=";
    var suffix = ScriptProperties.getProperty('FUSIONTABLES_API_KEY') + '&';
    var URL = prefix + suffix;
    var fusionTablesService = getFusionTablesService();
    // sends the the authentication and the query in url format
    var response = UrlFetchApp.fetch(URL, {
        method: "post",
        headers: {
            Authorization: 'Bearer ' + fusionTablesService.getAccessToken()
        },
        payload: "sql=" + query
    });
    
    return response.getContentText();
};
 
// puts all the current information in the spreadsheet into a query
function updateData(tableID) {
    //find sheets with ranges that will be sent
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var range = ss.getRangeByName(rangeName);
    var data = range.getValues();
    
    // format data
    for (var i in data) {
        for (var j in data[i]) {
            if (isNaN(data[i][j])) {
                data[i][j] = data[i][j].replace(/'/g, "\\'");
            }
        }
    }
  
    /*    
    The range called "FusionTableUpdate" includes the following columns:
       - Country, Type, Overall score, indicators
    
       "country" will be used to update the table and "type" (position 1 in the array) will be dismissed
    */
  
    var headers = data[0];

    /*
    the query to update the Fussion Table:
	    UPDATE <table_id>
	    SET <column_name> = <value> {, <column_name> = <value> }*
	    WHERE ROWID = <row_id>
    */
  
    for (var i = 1; i < data.length; ++i) {
      var query = "UPDATE " + tableID + " SET ";

      if (typeof (data[i][0]) == "string" && data[i][0] == "") {
        continue;
      }
      var dataRow = data[i];
      // gets all data for each country (row)
      for (var j = 1; j < dataRow.length; ++j) {
         // Skip the "type" column
         if ((typeof (headers[j]) == "string") && ((headers[j]) == "Type")) {
	   continue;
	 }
         
         // if it is not the first column (+ Type column), adds a comma.
         if (j>2) { 
           query += ", ";
         }
         
       query += "\'" + headers[j] + "\' = " + dataRow[j] + " "; 
      }
      
      // Gets the ROWID for this country
      // SELECT ROWID FROM <table_id> WHERE Country = 'name of the country'
      var select = "SELECT ROWID FROM "+ tableID +" WHERE Country = \'"+ dataRow[0] +"\';"; 
      var results = Utilities.jsonParse(queryFusionTables(encodeURIComponent(select)));  
 
      // if the country is not found, this row is skiped
      if (!results.rows) {
        SpreadsheetApp.getActiveSpreadsheet().toast( dataRow[0] + " skipped" );
        continue;
      }
      query += "WHERE ROWID = \'" + results.rows[0][0]  + "\';";
      var updateMsg = queryFusionTables(encodeURIComponent(query));
      var rowsUpdatedCounter = updateMsg.split(/\n/).length - 2;
      SpreadsheetApp.getActiveSpreadsheet().toast( rowsUpdatedCounter 
        + " cells in " + dataRow[0] + " updated" ,"PSI Scoreboard Update", 2);    
    }
  	
    SpreadsheetApp.getActiveSpreadsheet().toast( "Update finished" ,"PSI Scoreboard Update", 5);
    return;
};