/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.apache.drill.exec.store.googlesheets.utils;

import com.google.api.client.auth.oauth2.AuthorizationCodeFlow;
import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.auth.oauth2.DataStoreCredentialRefreshListener;
import com.google.api.client.auth.oauth2.StoredCredential;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.client.util.store.DataStore;
import com.google.api.services.drive.Drive;
import com.google.api.services.drive.model.File;
import com.google.api.services.drive.model.FileList;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.Sheets.Spreadsheets.Values.BatchGet;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddSheetRequest;
import com.google.api.services.sheets.v4.model.AppendValuesResponse;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.DeleteSheetRequest;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.Sheet;
import com.google.api.services.sheets.v4.model.SheetProperties;
import com.google.api.services.sheets.v4.model.Spreadsheet;
import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import org.apache.commons.lang3.StringUtils;
import org.apache.drill.common.Typifier;
import org.apache.drill.common.exceptions.UserException;
import org.apache.drill.common.expression.SchemaPath;
import org.apache.drill.exec.record.metadata.SchemaBuilder;
import org.apache.drill.exec.record.metadata.TupleMetadata;
import org.apache.drill.exec.store.googlesheets.DrillDataStore;
import org.apache.drill.exec.store.googlesheets.GoogleSheetsColumn;
import org.apache.drill.exec.store.googlesheets.GoogleSheetsStoragePluginConfig;
import org.apache.drill.exec.store.googlesheets.columns.GoogleSheetsColumnRange;
import org.apache.drill.exec.util.Utilities;
import org.apache.parquet.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import static com.google.api.client.util.Strings.isNullOrEmpty;


public class GoogleSheetsUtils {

  private static final Logger logger = LoggerFactory.getLogger(GoogleSheetsUtils.class);
  private static final int SAMPLE_SIZE = 5;
  private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
  private static final String UNKNOWN_HEADER = "field_";
  private static final String APPLICATION_NAME = "Drill";

  /**
   * Represents the possible data types found in a GoogleSheets document
   */
  public enum DATA_TYPES {
    /**
     * Represents a field before the datatype is known
     */
    UNKNOWN,
    /**
     * A numeric data type, either a float or an int.  These are all
     * converted to Doubles when projected.
     */
    NUMERIC,
    /**
     * A string data type
     */
    VARCHAR,
    /**
     * A field containing a date
     */
    DATE,
    /**
     * A field containing a time
     */
    TIME,
    /**
     * A field containing timestamps.
     */
    TIMESTAMP,
    /**
     * A field containing a list of strings.  Only used for implicit columns.
     */
    VARCHAR_REPEATED
  }

  /**
   * Creates an authorized {@link Credential} for use in GoogleSheets queries.
   * @param config The {@link GoogleSheetsStoragePluginConfig} to be authorized
   * @param dataStore A {@link DrillDataStore} containing the user's tokens
   * @param queryUser The current query user's ID.  This should be set to anonymous if user translation is disabled.
   * @return A validated {@link Credential} object.
   * @throws IOException If anything goes wrong
   * @throws GeneralSecurityException If the credentials are invalid
   */
  public static Credential authorize(GoogleSheetsStoragePluginConfig config,
                                     DataStore<StoredCredential> dataStore,
                                     String queryUser) throws IOException, GeneralSecurityException {
    GoogleClientSecrets clientSecrets = config.getSecrets();
    GoogleAuthorizationCodeFlow flow;
    List<String> scopes = Collections.singletonList(SheetsScopes.SPREADSHEETS);

    if (dataStore == null) {
      logger.debug("Datastore is null");
      throw UserException.connectionError()
        .message("The DrillDataStore is null.  This should not happen.")
        .build(logger);
    } else if (dataStore.getDataStoreFactory() == null) {
      logger.debug("Datastore factory is null");
      throw UserException.connectionError()
        .message("The DrillDataStoreFactory is null.  This should not happen.")
        .build(logger);
    }

    flow = new GoogleAuthorizationCodeFlow.Builder
      (GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, clientSecrets, scopes)
        .setDataStoreFactory(dataStore.getDataStoreFactory())
        .setAccessType("offline")
        .build();

    return loadCredential(queryUser, flow, dataStore);
  }

  public static Credential loadCredential(String userId, GoogleAuthorizationCodeFlow flow, DataStore<StoredCredential> credentialDataStore) {
    // No requests need to be performed when userId is not specified.
    if (isNullOrEmpty(userId)) {
      return null;
    }

    if (credentialDataStore == null) {
      return null;
    }
    Credential credential = newCredential(userId, flow, credentialDataStore);
    StoredCredential stored = ((DrillDataStore<StoredCredential>)credentialDataStore).getStoredCredential();
    if (stored == null) {
      return null;
    }
    credential.setAccessToken(stored.getAccessToken());
    credential.setRefreshToken(stored.getRefreshToken());
    credential.setExpirationTimeMilliseconds(stored.getExpirationTimeMilliseconds());

    return credential;
  }

  /**
   * Returns a new credential instance based on the given user ID.
   *
   * @param userId user ID or {@code null} if not using a persisted credential store
   */
  private static Credential newCredential(String userId, AuthorizationCodeFlow flow, DataStore<StoredCredential> credentialDataStore) {
    Credential.Builder builder =
      new Credential.Builder(flow.getMethod())
        .setTransport(flow.getTransport())
        .setJsonFactory(flow.getJsonFactory())
        .setTokenServerEncodedUrl(flow.getTokenServerEncodedUrl())
        .setClientAuthentication(flow.getClientAuthentication())
        .setRequestInitializer(flow.getRequestInitializer())
        .setClock(flow.getClock());

    if (credentialDataStore != null) {
      builder.addRefreshListener(
        new DataStoreCredentialRefreshListener(userId, credentialDataStore));
    }
    builder.getRefreshListeners().addAll(flow.getRefreshListeners());
    return builder.build();
  }


  public static Sheets getSheetsService(GoogleSheetsStoragePluginConfig config,
                                        DataStore<StoredCredential> dataStore,
                                        String queryUser)
    throws IOException, GeneralSecurityException {
    Credential credential = GoogleSheetsUtils.authorize(config, dataStore, queryUser);
    return new Sheets.Builder(
      GoogleNetHttpTransport.newTrustedTransport(), GsonFactory.getDefaultInstance(), credential)
      .setApplicationName(APPLICATION_NAME)
      .build();
  }

   /** Returns an authenticated {@link Drive} service.
   * @param config The {@link GoogleSheetsStoragePluginConfig} for the plugin
   * @param dataStore A {@link DrillDataStore} for the stored credentials
   * @param queryUser A {@link String} containing the current query user.
   * @return An authenticated {@link Drive} service.
   * @throws IOException If anything goes wrong throw an IOException
   * @throws GeneralSecurityException If the creds are invalid or
   */
  public static Drive getDriveService(GoogleSheetsStoragePluginConfig config,
                                      DataStore<StoredCredential> dataStore,
                                      String queryUser) throws IOException, GeneralSecurityException {
    Credential credential = GoogleSheetsUtils.authorize(config, dataStore, queryUser);
    return new Drive.Builder(
      GoogleNetHttpTransport.newTrustedTransport(), GsonFactory.getDefaultInstance(), credential)
      .setApplicationName(APPLICATION_NAME)
      .build();
  }

  /**
   * In GoogleSheets, the file is uniquely identified by a non-human readable token. The Sheets SDK does
   * not provide a means to map tokens to file names.  To do this, we need to use the Google Drive SDK.
   *
   * Google Drive's concept of folders is more similar to that of S3 which doesn't really have folders or
   * directories. So the user is not able to include any file paths in the query string.
   *
   * More importantly however, is that Google Drive allows duplicate file names, even within the same directory.
   * Thus, it is entirely possible to have an entire directory of files with the same name. The tokens for these files
   * will be different, but the human-readable names can be the same.  This creates an obvious problem for Drill as we
   * need unique file names to include in a query.
   *
   * @param driveService An authenticated {@link Drive} service.
   * @return A {@link HashMap} containing the tokens as keys and the file names as values.
   * @throws IOException If anything goes wrong, throw an IOException.
   */
  public static Map<String,String> getTokenToNameMap(Drive driveService) throws IOException {
    Map<String, String> sheetMapping = new HashMap<>();
    String pageToken = null;

    do {
      FileList result = driveService.files().list()
        .setQ("mimeType='application/vnd.google-apps.spreadsheet'")
        .setSpaces("drive")
        .setPageToken(pageToken)
        .execute();

      for (File file : result.getFiles()) {
        sheetMapping.put(file.getId(), file.getName());
      }

      pageToken = result.getNextPageToken();
    } while (pageToken != null);

    return sheetMapping;
  }

  /**
   * Google Sheets tokens are strings of length 44 that contain upper and lower case letters, numbers and underscores.
   * This function will attempt to identify file tokens.
   * <p>
   * Given that Google's spec for file IDs is not officially published, and can change at any time, we will keep the
   * validation as light as possible to prevent future issues, in the event Google changes their file Id structure.
   * @param id A {@link String} containing an unknown identifier
   * @return True if the string is a file probable file token, false if not.
   */
  public static boolean isProbableFileToken(String id) {
    logger.debug("Checking token {}", id);
    if (StringUtils.isEmpty(id)) {
      return false;
    } else if (id.length() != 44) {
      return false;
    } else {
      Pattern pattern = Pattern.compile("[0-9][a-zA-Z0-9_-]{43}");
      return pattern.matcher(id).find();
    }
  }

  /**
   * Returns a list of the titles of the available spreadsheets within a given Google sheet.
   * @param service The Google Sheets service
   * @param sheetID The sheetID for the Google sheet.  This can be obtained from the URL of your Google sheet
   * @return A list of spreadsheet names within a given Google Sheet
   * @throws IOException If the Google sheet is unreachable or invalid.
   */
  public static List<Sheet> getTabList(Sheets service, String sheetID) throws IOException {
    logger.debug("Getting tabs for: {}", sheetID);
    Spreadsheet spreadsheet = service.spreadsheets().get(sheetID).execute();
    return spreadsheet.getSheets();
  }

  /**
   * Converts a column index to A1 notation. Google sheets has a limitation of approx 18k
   * columns, but that is not enforced here. The column index must be greater than zero or
   * the function will return null.
   *
   * References code found here:
   * <a href="https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter">Stack Overflow Article</a>
   * @param column The column index for the desired column. Must be greater than zero
   * @return The A1 representation of the column index.
   */
  public static String columnToLetter(int column) {
    if (column <= 0) {
      return null;
    }

    int temp;
    StringBuilder letter = new StringBuilder();
    while (column > 0) {
      temp = (column - 1) % 26;
      letter.insert(0, (char) (temp + 65));
      column = (column - temp - 1) / 26;
    }
    return letter.toString();
  }

  /**
   * Given a column reference in A1 notation, this function will
   * return the column numeric index. GoogleSheets has a limit of approx
   * 18k columns, but that is not enforced here.
   *
   * References code found here:
   * <a href="https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter">Stack Overflow Article</a>
   * @param letter The desired column in A1 notation
   * @return The index of the supplied column
   */
  public static int letterToColumnIndex(String letter) {
    // Make sure the letters are all upper case.
    letter = letter.toUpperCase();
    int column = 0;
    int length = letter.length();
    for (int i = 0; i < length; i++) {
      column += (Character.codePointAt(letter, i) - 64) * (int)Math.pow(26, length - i - 1);
    }
    return column;
  }

  /**
   * This function will be used to build the schema for Drill.  As Google sheets does
   * @param service An authenticated Google Sheets Service
   * @param sheetID The Sheet ID for the Google Sheet (Can be found in the Sheet URL)
   * @param tabName The tab name of the actual spreadsheet you want to query
   * @return A nested list of the first five rows of the dataset.
   * @throws IOException If the request fails, throw an IOException
   */
  public static List<List<Object>> getFirstRows (Sheets service, String sheetID, String tabName) throws IOException {
    String range = tabName + "!1:" + SAMPLE_SIZE;
    return service.spreadsheets().values().get(sheetID, range).execute().getValues();
  }

  /**
   * Returns a 2D table of Objects representing the given range in A1 notation. Note that this
   * function cannot be used with multiple ranges.  If you are trying to retrieve multiple groups
   * of columns, you must use the getBatchData function.
   * @param service The Authenticated GoogleSheets service
   * @param sheetID The GoogleSheet ID.  This can be found in the Sheet URL
   * @param range The range in A1 notation.
   * @return  A 2D table of Objects representing the given range.
   * @throws IOException If the request fails, throw an IOException.
   */
  public static List<List<Object>> getDataFromRange(Sheets service, String sheetID, String range) throws IOException {
    return service.spreadsheets().values().get(sheetID, range).execute().getValues();
  }

  /**
   * Finds a {@link Sheet} from a list of tabs with a given title.  If the sheet is not present,
   * the function will throw a User Exception.
   * @param tabName The name of the desired sheet.
   * @param tabList A {@link List} of {@link Sheet} objects
   * @return The desired Sheet.
   */
  public static Sheet getSheetFromTabList(String tabName, List<Sheet> tabList) {
    for (Sheet sheet : tabList) {
      if (sheet.getProperties().getTitle().contentEquals(tabName)) {
        return sheet;
      }
    }

    throw UserException.dataReadError()
        .message("Could not find sheet " + tabName)
        .build(logger);
  }

  /**
   * This function is used to get data when projection is pushed down to Google Sheets.
   * @param service The Authenticated GoogleSheets service
   * @param sheetID The GoogleSheet ID.  This can be found in the Sheet URL
   * @param ranges The list of ranges
   * @throws IOException If anything goes wrong, IOException will be thrown
   */
  public static List<List<Object>> getBatchData(Sheets service, String sheetID, List<String> ranges) throws IOException {
    logger.debug("Getting ranges: {}", ranges);
    BatchGet request = service.spreadsheets().values().batchGet(sheetID).setRanges(ranges);
    List<ValueRange> response = request.execute().getValueRanges();

    List<List<Object>> results  = new ArrayList<>();
    // In Google's infinite wisdom when designing this API, the results
    // are returned in a completely different fashion than when projection is not
    // pushed down to Google Sheets. Specifically, if you use the regular values() to retrieve
    // values from a GoogleSheet, you get a List of rows.  Whereas if you use the BatchGet,
    // you get a list of columns, sort of.  Except these columns are embedded in a bunch of
    // other debris from which you must extract the actual data.
    //
    // It should be noted that the GoogleSheets API does not accept multiple ranges in the
    // request, so it is necessary to use the batch request.
    for (int rowIndex = 0; rowIndex < ((ArrayList<?>) response.get(0).get("values")).size(); rowIndex++) {
      List<Object> row = new ArrayList<>();
      for (int colIndex = 0; colIndex < response.size(); colIndex++) {
        try {
          Object value = ((ArrayList<?>) ((ArrayList<?>) response.get(colIndex).get("values")).get(rowIndex)).get(0);
          row.add(value);
        } catch (IndexOutOfBoundsException | NullPointerException e) {
          row.add(null);
        }
      }
      results.add(row);
    }
    return results;
  }

  /**
   *
   * @param sampleData This represents a sample of the first few rows of data which will be used to build the schema.
   * @param projectedColumns A list of projected columns
   * @param allTextMode If true, the columns will all be of the VARCHAR type
   * @return A map of the column name and {@link GoogleSheetsColumn} column for every projected column.
   */
  public static Map<String, GoogleSheetsColumn> getColumnMap(List<List<Object>> sampleData, List<SchemaPath> projectedColumns, boolean allTextMode) {
    // For now, we assume that the column headers are in the first row
    int emptyColumnCount = 0;
    List<String> headers = new ArrayList<>();
    Map<String, DATA_TYPES> dataTypes = new HashMap<>();

    for (Object rawHeader : sampleData.get(0)) {
      String header = (String) rawHeader;

      // If the header row is empty, assign a value of `field_n` where n is the unknown header count.
      if (Strings.isNullOrEmpty(header)) {
        header = UNKNOWN_HEADER + emptyColumnCount;
        emptyColumnCount++;
      }
      headers.add(header);
      if (allTextMode) {
        dataTypes.put(header, DATA_TYPES.VARCHAR);
      } else {
        dataTypes.put(header, DATA_TYPES.UNKNOWN);
      }
    }

    if (!allTextMode) {
      for (int rowIndex = 1; rowIndex < sampleData.size(); rowIndex++) {
        for (int colIndex = 0; colIndex < sampleData.get(rowIndex).size(); colIndex++) {
          updateDataType(headers.get(colIndex), dataTypes, sampleData.get(rowIndex).get(colIndex).toString());
        }
      }
    }
    // At this point, we have inferred the columns.  We will return a list of {@link GoogleSheetsColumn} which
    // we will need later for projection pushdown and other schema creation activities.
    Map<String, GoogleSheetsColumn> columnMap = new LinkedHashMap<>();
    int colCount = 0;
    for (String header: headers) {
      // When building the schema map, we only want to include projected columns.  This will be important later
      // when we build the range request.
      if (Utilities.isStarQuery(projectedColumns) || isProjected(projectedColumns, header)) {
        GoogleSheetsColumn column = new GoogleSheetsColumn(header, dataTypes.get(header), headers.indexOf(header), colCount);
        columnMap.put(header, column);
        colCount++;
      }
    }
    return columnMap;
  }

  public static List<GoogleSheetsColumnRange> getProjectedRanges(String sheetName, Map<String, GoogleSheetsColumn> columnMap) {
    List<GoogleSheetsColumnRange> projectedRanges = new ArrayList<>();
    int lastIndex = -1;
    int currentIndex;
    GoogleSheetsColumnRange currentRange = new GoogleSheetsColumnRange(sheetName);
    for (GoogleSheetsColumn column : columnMap.values()) {
      // Exclude metadata columns
      if (column.isMetadata()) {
        continue;
      }
      currentIndex = column.getColumnIndex();

      // Edge case for first range
      if (currentRange.getStartColIndex() == null) {
        currentRange = currentRange.setStartIndex(currentIndex);
      }

      // End the range and create a new one.
      if (currentIndex != (lastIndex + 1) && lastIndex != -1) {
        currentRange.setEndIndex(lastIndex);
        projectedRanges.add(currentRange);
        currentRange = new GoogleSheetsColumnRange(sheetName)
          .setStartIndex(currentIndex);
      }
      lastIndex = currentIndex;
    }
    currentRange = currentRange.setEndIndex(lastIndex);
    projectedRanges.add(currentRange);
    return projectedRanges;
  }

  /**
   * Returns true if the column is projected, false if not.
   * @param projectedColumns A list of projected columns AKA the haystack.
   * @param columnName The column name AKA the needle
   * @return True if the needle is in the haystack, false if not.
   */
  public static boolean isProjected(List<SchemaPath> projectedColumns, String columnName) {
    // Star queries project everything, so return true.  Technically this
    // might not always be correct, in the case that a query projects a non-existent column.
    if (Utilities.isStarQuery(projectedColumns)) {
      return true;
    }
    for (SchemaPath path : projectedColumns) {
      if (path.getAsNamePart().getName().contains(columnName)) {
        return true;
      }
    }
    return false;
  }

  /**
   * Builds a Drill Schema from a Map of GoogleSheetsColumns.
   * @param columnMap A map of {@link GoogleSheetsColumn} containing the schema info.
   * @return A populated {@link TupleMetadata} schema
   */
  public static TupleMetadata buildSchema(Map<String, GoogleSheetsColumn> columnMap) {
    SchemaBuilder builder = new SchemaBuilder();
    for (GoogleSheetsColumn column : columnMap.values()) {
      builder.addNullable(column.getColumnName(), column.getDrillDataType());
    }
    return builder.build();
  }

  /**
   * Infers the datatype of an unknown string.
   * @param data An input String of unknown type.
   * @return The {@link DATA_TYPES} of the unknown string.
   */
  public static DATA_TYPES inferDataType (String data) {
    Entry<Class, String> result = Typifier.typify(data);
    String dataType = result.getKey().getSimpleName();

    // If the string is empty, return UNKNOWN
    if (StringUtils.isEmpty(data)) {
      return DATA_TYPES.UNKNOWN;
    } else if (dataType.equalsIgnoreCase("Double")) {
      return DATA_TYPES.NUMERIC;
    } else if(dataType.equalsIgnoreCase("LocalDateTime")) {
      return DATA_TYPES.TIMESTAMP;
    } else if (dataType.equalsIgnoreCase("LocalDate")) {
      return DATA_TYPES.DATE;
    } else if (dataType.equalsIgnoreCase("LocalTime")) {
      return DATA_TYPES.TIME;
    } else {
      return DATA_TYPES.VARCHAR;
    }
  }

  public static void updateDataType(String columnName, Map<String, DATA_TYPES> dataTypesMap, String value) {
    if (StringUtils.isEmpty(value)) {
      return;
    }

    // Get the data type of the unknown value
    DATA_TYPES probableDataType = inferDataType(value);
    DATA_TYPES columnDataType = dataTypesMap.get(columnName);

    // If the column data type matches the new value's data type, make no changes
    if (probableDataType == columnDataType) {
      return;
    }

    // If the column was unknown assign it the data type that we found
    if (columnDataType == DATA_TYPES.UNKNOWN) {
      dataTypesMap.put(columnName, probableDataType);
    } else if (columnDataType == DATA_TYPES.NUMERIC && probableDataType == DATA_TYPES.VARCHAR) {
      // If we have a column that is thought to be numeric, we will continue to consider it numeric unless
      // we encounter a string, at which point we will convert it to a String.
      dataTypesMap.put(columnName, DATA_TYPES.VARCHAR);
    }
  }

  /**
   * Adds a new tab to an existing GoogleSheet document.
   *
   * @param service   An authenticated GoogleSheet service
   * @param sheetName The GoogleSheet name of the document
   * @param tabName   The name of the tab you wish to add to the GoogleSheet document
   * @throws IOException Throws an IOException if anything goes wrong.
   */
  public static void addTabToGoogleSheet(Sheets service, String sheetName, String tabName)
    throws IOException {
    List<Request> requests = new ArrayList<>();
    requests.add(new Request()
      .setAddSheet(new AddSheetRequest().setProperties(new SheetProperties()
        .setTitle(tabName)
        .setIndex(0))));
    BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);
    service.spreadsheets().batchUpdate(sheetName, body).execute();
  }

  /**
   * Removes a sheet from an existing GoogleSheets document.  This method should only be used if the GoogleSheets
   * document has more than one tab.
   * @param service An authenticated GoogleSheet {@link Sheets}
   * @param fileToken The File token of the GoogleSheet containing the sheet to be deleted
   * @param deletedTab  A {@link Sheet} which will be removed
   * @throws IOException If anything goes wrong.
   */
  public static void removeTabFromGoogleSheet(Sheets service, String fileToken, Sheet deletedTab) throws IOException {
    List<Request> requests = new ArrayList<>();
    requests.add(new Request()
        .setDeleteSheet(new DeleteSheetRequest().setSheetId(deletedTab.getProperties().getSheetId()))
    );

    BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);
    service.spreadsheets().batchUpdate(fileToken, body).execute();
  }

  /**
   * Accepts a list of data and writes this data to a GoogleSheet document.
   * @param service An authenticated GoogleSheet service
   * @param sheetID The SheetID.  This can be obtained from the URL of the GoogleSheet Document
   * @param tabName The tab name within the aforementioned GoogleSheet
   * @param data A list of rows of the data to be inserted.
   * @throws IOException If anything goes wrong, throw an IO exception
   */
  public static void writeDataToGoogleSheet(Sheets service, String sheetID, String tabName, List<List<Object>> data)
    throws IOException {
    String range = tabName + "!A1";
    ValueRange body = new ValueRange()
      .setValues(data)
      .setMajorDimension("ROWS");

    UpdateValuesResponse result = service.spreadsheets().values().update(sheetID, range, body)
        .setValueInputOption("RAW")
        .execute();
  }

  /**
   * Accepts a list of data and writes this data to a GoogleSheet document.
   * @param service An authenticated GoogleSheet service
   * @param sheetID The SheetID.  This can be obtained from the URL of the GoogleSheet Document
   * @param tabName The tab name within the aforementioned GoogleSheet
   * @param data A list of rows of the data to be inserted.
   * @throws IOException If anything goes wrong, throw an IO exception
   */
  public static void appendDataToGoogleSheet(Sheets service, String sheetID, String tabName, List<List<Object>> data)
      throws IOException {
    String range = tabName + "!A1";
    ValueRange body = new ValueRange()
        .setValues(data)
        .setMajorDimension("ROWS");

    AppendValuesResponse result = service.spreadsheets().values().append(sheetID, range, body)
        .setValueInputOption("RAW")
        .execute();
  }
}