/**
 Created by M.Merati

 Email: [email protected]
*/

// This example shows how to create the spreadsheet, update and read the values.

#include <Arduino.h>
#include
#if defined(ESP32) || defined(ARDUINO_RASPBERRY_PI_PICO_W)
#include <WiFi.h>
#elif defined(ESP8266)
#include <ESP8266WiFi.h>
#endif
#include <ESP_Google_Sheet_Client.h>
#include <stdlib.h>
#include "time.h"
const char* ntpServer = "pool.ntp.org";
const long gmtOffset_sec = 19800;
const int daylightOffset_sec = 0;
int SensValue = 0;

// For SD/SD_MMC mounting helper
#include <GS_SDHelper.h>

#define WIFI_SSID "Wokwi-GUEST"
#define WIFI_PASSWORD ""

#define PROJECT_ID "proud-spring-408607-445715"

// Service Account's client email
#define CLIENT_EMAIL "[email protected]" //"proud-spring-408607-445715@proud-spring-408607-445715.iam.gserviceaccount.com"

// Your email to share access to spreadsheet
#define USER_EMAIL "[email protected]"

char numberArray[20];

// Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCpJpmeI9RGW1tD\n3iTGsl1Vd6Y6UZiMRRQK0h9LeFE2gxcU8N7o329KnnBZCXBjnL81WY3JPtJF5VOu\nVgwKFBTyZB9XheQ2DeGqp5qcAOz2Nenk9tKNR1joaDxvuEICPRitM4kR8H1NC10S\nc+oqH1i2O2AnIMg9tI8I9Qiig7RnXHbsu3zUKZhjwqxicLRJ/HfkFLUlON5SceG7\nIkPhbRHdu98xuJ9F55ZcK8Z14J175uGfwS959j0rqc86m7vl2GIiM1lpHfAqfkR2\nXhw8lgn4pPHvS++M3T88Uj7Ak1MNNKaLJpwSpKQNUv1/GS24ybt5GPNGUwDOYEGB\n4/hgTL6NAgMBAAECggEAE4R6sT6dHckozLnzMGIgVt5Jde04VucZmu4H4mNtq4aQ\nwT/R2Lj/VHLXr85hA31wqq08qP3wsVP2xeTz2q3ahOV8oFK4tGwBn/la46lPrlEm\nPd8lCFw1LAvq/VEbfLqkAEg0BrfjHWK0SI7LdDTNvak4PQuRT11oK7ZqWqYryMk4\nfBZR0EQV2jSNyPz8LrSw31N24/XuJfINVFeIVEO/ZKUUmuFNr/wXfLkeHeGRk6Qe\n/tLurs1kiTOue4u3MHq2IUfhXVjrwMXrGYdqF8L7rFx/nZ+VpHVnZunEckpGJH2O\nJpiIF/CEN3NTJDoa6SkVEGMYAt0+9vZ7Ugxw8qRiWQKBgQDWoglerxpxj9JqV6kU\nTZl/cIuiOtVOzlVRvBc/tZIfzn3eJNxOh9YW9dmEnnCf1oUFMF8yL1KsLx9zLtlX\nTh7Qktqh4CcxQmP4uG7hcAS/xqWA1vUkKeL5nlaqAPJVDauNHnJGDhz862sx5YQY\n2Kd4gAFmOQGqBPtsKKlOIuTjawKBgQDJwHqIoMNiMnGUHlIuc6gdFIf7tii4Nk87\nWWcTNYjlc+gKqnxJqEYxCQAq/ZEvgU4AWwFjZxLDecp9HPNAojkKSB5U6YEZ+50K\n1GVSy5VNvP6P9x87X1qOUtsrzQKtvVQQJMbL8n/LJ1Zoj1D/XpxUZERKs1w+4QrX\nXJ9hFGXb5wKBgQCO+DqzloJP2RS5f5ECFDrGWCq7MuN57hmJK3h1GLawdzUCr50M\ntpEWlEeDfqLY5eWPskwHOtygE3eomzFtZX1DRD9citqwhl/fgsNhaePP9zuxmf6L\nS1eFHULvVVgW7dczVm+aWLZYh1zNTOx0PIU50FKeTCTeoxjzUBaPFNFQ5wKBgGSX\nV4Dvxvsa845KX+dROyzko7hY5oySZ4bL2Jy3QH5HABbxUe7Xu8ePGUpWCyGIcv+y\nYErThYN5IgCngpFjUNVTh+wGUJxH29EefTZ+0v5jkPju2d+cd2Ym4ahBG2jM0TEL\nZSMi0ZzVDDwt2NC6t29MXtBwBTpEskcWo1Cf/vlZAoGAU5zzs9Lvnl5FdvMxI1Hf\nn42KwFM6xidLgjMfKRTlqtJ8banCX9+l0AFOFGxpqxRjGY1FM1xPgWshJMT54RsC\nE9vLD1pAWVC1p4F7ibhUK7+T8B5C//dyOojF5QSDRDBi0oGJ18vgmvee8dfcb1yu\nwjSR26IGKDiecU1RUd5++H4=\n-----END PRIVATE KEY-----\n";

bool taskComplete = false;

#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
WiFiMulti multi;
#endif

void tokenStatusCallback(TokenInfo info);

void setup()
{

 Serial.begin(115200);
 Serial.println();
 Serial.println();

#if defined(ESP32) || defined(ESP8266)
 WiFi.setAutoReconnect(true);
#endif

#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
 multi.addAP(WIFI_SSID, WIFI_PASSWORD);
 multi.run();
#else
 WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
#endif

 Serial.print("Connecting to Wi-Fi");
 unsigned long ms = millis();
 while (WiFi.status() != WL_CONNECTED)
 {
 Serial.print(".");
 delay(300);
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
 if (millis() - ms > 10000)
 break;
#endif
 }
 Serial.println();
 Serial.print("Connected with IP: ");
 Serial.println(WiFi.localIP());
 Serial.println();

 // Set the callback for Google API access token generation status (for debug only)
 GSheet.setTokenCallback(tokenStatusCallback);

 // The WiFi credentials are required for Pico W
 // due to it does not have reconnect feature.
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
 GSheet.clearAP();
 GSheet.addAP(WIFI_SSID, WIFI_PASSWORD);
#endif

 // Set the seconds to refresh the auth token before expire (60 to 3540, default is 300 seconds)
 GSheet.setPrerefreshSeconds(10 * 60);

 // Begin the access token generation for Google API authentication
 GSheet.begin(CLIENT_EMAIL, PROJECT_ID, PRIVATE_KEY);

 configTime(gmtOffset_sec, daylightOffset_sec, ntpServer);
}

void loop()
{
 // Call ready() repeatedly in loop for authentication checking and processing
 bool ready = GSheet.ready();

 if (ready && !taskComplete)
 {
 FirebaseJson response;

 Serial.println("\nCreate spreadsheet...");
 Serial.println("------------------------");

 FirebaseJson spreadsheet;
 spreadsheet.set("properties/title", "DATA LOG TEST");
 spreadsheet.set("sheets/properties/gridProperties/rowCount", 100 );
 spreadsheet.set("sheets/properties/gridProperties/columnCount", 2);
 spreadsheet.set("sheets/properties/title", "DATA RECORD");
 

 String spreadsheetId, spreadsheetURL;
 bool success = false;

 // For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create

 success = GSheet.create(&response /* returned response */, &spreadsheet /* spreadsheet object */, USER_EMAIL /* your email that this spreadsheet shared to */);
 response.toString(Serial, true);
 Serial.println();
 

 if (success)
 {
 // Get the spreadsheet id from already created file.
 FirebaseJsonData result;
 response.get(result, FPSTR("spreadsheetId")); // parse or deserialize the JSON response
 if (result.success)
 spreadsheetId = result.to<const char *>();

 // Get the spreadsheet URL.
 result.clear();
 response.get(result, FPSTR("spreadsheetUrl")); // parse or deserialize the JSON response
 if (result.success)
 {
 spreadsheetURL = result.to<const char *>();
 Serial.println("\nThe spreadsheet URL");
 Serial.println(spreadsheetURL);
 }

 struct tm timeinfo;
 char timeStringBuff[50];
 String asString;
 char buffer[40];


 FirebaseJson valueRange;
 for (int counter = 0; counter < 10; counter++)
 {
 Serial.println("\nUpdate spreadsheet values...");
 Serial.println("------------------------------");
 if (!getLocalTime(&timeinfo)) {
 Serial.println("Failed to obtain time");
 return;
 }
 strftime(timeStringBuff, sizeof(timeStringBuff), "%A, %B %d %Y %H:%M:%S", &timeinfo);
 asString = timeStringBuff;
 asString.replace(" ", "-");
 SensValue = analogRead(34);
 itoa(SensValue, numberArray, 10);

 sprintf(buffer, "values/[%d]/[1]", counter);
 valueRange.set(buffer, numberArray);
 sprintf(buffer, "values/[%d]/[0]", counter);
 valueRange.set(buffer, asString);
 sprintf(buffer, "Sheet1!A%d:B%d", 1 + counter, 10 + counter);

 success = GSheet.values.update(&response /* returned response */, spreadsheetId /* spreadsheet Id to update */, "Sheet1!A1:B1000" /* range to update */, &valueRange /* data to update */);
 response.toString(Serial, true);
 Serial.println();
 //valueRange.clear();
 delay(5000);
 }

 // For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

 if (success)
 {

 Serial.println("\nGet spreadsheet values...");
 Serial.println("------------------------------");

 success = GSheet.values.get(&response /* returned response */, spreadsheetId /* spreadsheet Id to read */, "Sheet1!A1:B10" /* range to read */);
 response.toString(Serial, true);
 Serial.println();



#if defined(ESP32) || defined(ESP8266)
 Serial.println(ESP.getFreeHeap());
#elif defined(PICO_RP2040)
 Serial.println(rp2040.getFreeHeap());
#endif
 }
 }

 taskComplete = true;
 }
}

void tokenStatusCallback(TokenInfo info)
{
 if (info.status == esp_signer_token_status_error)
 {
 Serial.printf("Token info: type = %s, status = %s\n", GSheet.getTokenType(info).c_str(), GSheet.getTokenStatus(info).c_str());
 Serial.printf("Token error: %s\n", GSheet.getTokenError(info).c_str());
 }
 else
 {
 Serial.printf("Token info: type = %s, status = %s\n", GSheet.getTokenType(info).c_str(), GSheet.getTokenStatus(info).c_str());
 }
}