<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly
}

if ( ! class_exists( 'SftcyWordformConnectGooglesheet' ) ) {
	class SftcyWordformConnectGooglesheet {
		private static $initiated                          = false;
		public static $output 							   = [];
		public static $google_oauth_client_id              = null;
		public static $google_oauth_client_secret          = null;
		public static $google_oauth_redirect_uri           = null; 
		public static $configure_spinning_icon             = '<i class="fa-solid fa-gear fa-spin"></i>';

		public function __construct() {
			if ( ! self::$initiated ) {				
				self::initiate_hooks();
			}
		}

		/**
		 * Init Hooks
		 */
		private static function initiate_hooks() {
			add_action('init', [ __CLASS__, 'wordform_adding_ob_start_to_redirect_properly' ] );
			// Enqueue scripts
			add_action( 'admin_enqueue_scripts', array( __CLASS__, 'wordform_admin_connect_page_required_scripts' ) );
			
		    // Ajax : Calls
			add_action( 'wp_ajax_wordform_connect_google_auth', array( __CLASS__, 'wordform_connect_google_auth' ) );
			add_action( 'wp_ajax_wordform_save_google_auth_credentials_with_authentication', array( __CLASS__, 'wordform_save_google_auth_credentials_with_authentication' ) );	
			add_action( 'wp_ajax_wordform_connect_form_n_display_form_fields', array( __CLASS__, 'wordform_connect_form_n_display_form_fields' ) );
			add_action( 'wp_ajax_wordform_connect_create_googlesheet_with_form_fields', array( __CLASS__, 'wordform_connect_create_googlesheet_with_form_fields' ) );	
			add_action( 'wp_ajax_wordform_connect_reconnect_googlesheet_with_form_fields', array( __CLASS__, 'wordform_connect_reconnect_googlesheet_with_form_fields' ) );						
						
			self::$initiated = true;
		}
		
		/**
		 * 
		 */
		public static function wordform_adding_ob_start_to_redirect_properly() {
			ob_start();
		}
		
		/**
		 * WordForm Connect Page required scripts
		 */
		public static function wordform_admin_connect_page_required_scripts() {
			$screen              = get_current_screen();
			$admin_page          = isset( $_GET['page'] ) ? sanitize_text_field( $_GET['page'] ) : '';			
			$wordform_pages      = [ 'wordform-connects' ];
			if ( in_array( $admin_page, $wordform_pages ) ) {
				// Wordform Connects Page Google Script
				// wp_enqueue_script( 'wordform-admin-connects-page-google-platform-script', 'https://accounts.google.com/gsi/client', array( 'jquery', 'sc-wordform-admin-misc-script' ), SFTCY_WORDFORM_VERSION, true );
				// wp_enqueue_script( 'wordform-admin-connects-page-google-accounts-oauth2-script',  plugins_url( '../admin/js/google.accounts.oauth2.js', __FILE__ ), array( 'jquery', 'sc-wordform-admin-misc-script' ), SFTCY_WORDFORM_VERSION, true );										
				
				// Enqueue jQuery UI Accordion
				wp_enqueue_script( 'jquery-ui-accordion' );

				// Enqueue jQuery UI Tooltip
				wp_enqueue_script( 'jquery-ui-tooltip' );
			}			
		}

		/**
		 * Get Google credentials data
		 * Credentials are stored as option data varibale 'wordform_connect_google_credentials'		 
		 * @return array
		 */
		public static function get_google_credentials() {
			$managed_credentials = self::get_managed_google_credentials();
			if ( $managed_credentials ) {
				return $managed_credentials;
			}

			$credentials_info = get_option('wordform_connect_google_credentials', [] );
			$credentials = [];
			if ( isset( $credentials_info['clientID'] ) && ! empty( $credentials_info['clientID'] ) 
				  && isset( $credentials_info['clientSecret'] ) && ! empty( $credentials_info['clientSecret'] )
					&& isset( $credentials_info['clientRedirectUrl'] ) && ! empty( $credentials_info['clientRedirectUrl'] )  ) {
				$credentials['clientID'] = 	$credentials_info['clientID'];
				$credentials['clientSecret'] = 	$credentials_info['clientSecret'];
				$credentials['clientRedirectUrl'] = $credentials_info['clientRedirectUrl'];
				return $credentials;
			}
			return [];
		}

		/**
		 * Get centrally managed Google OAuth credentials when provided by the plugin owner.
		 * This enables one-click account connection for end users without manual client setup.
		 *
		 * @return array
		 */
		public static function get_managed_google_credentials() {
			$managed_credentials = apply_filters(
				'wordform_connect_google_managed_oauth_credentials',
				[
					'clientID'          => defined( 'WORDFORM_CONNECT_GOOGLE_CLIENT_ID' ) ? WORDFORM_CONNECT_GOOGLE_CLIENT_ID : '',
					'clientSecret'      => defined( 'WORDFORM_CONNECT_GOOGLE_CLIENT_SECRET' ) ? WORDFORM_CONNECT_GOOGLE_CLIENT_SECRET : '',
					'clientRedirectUrl' => SftcyWordformConnectBase::get_wordform_connect_oauth_redirect_url(),
				]
			);

			if ( isset( $managed_credentials['clientID'], $managed_credentials['clientSecret'], $managed_credentials['clientRedirectUrl'] ) && ! empty( $managed_credentials['clientID'] ) && ! empty( $managed_credentials['clientSecret'] ) && ! empty( $managed_credentials['clientRedirectUrl'] ) ) {
				return [
					'clientID'          => sanitize_text_field( $managed_credentials['clientID'] ),
					'clientSecret'      => sanitize_text_field( $managed_credentials['clientSecret'] ),
					'clientRedirectUrl' => esc_url_raw( $managed_credentials['clientRedirectUrl'] ),
				];
			}

			return [];
		}

		/**
		 * Check whether centrally managed Google OAuth credentials are available.
		 *
		 * @return bool
		 */
		public static function is_google_managed_oauth_available() {
			return ! empty( self::get_managed_google_credentials() );
		}

		/**
		 * Build Google OAuth authorization URL for one-click connect flow.
		 *
		 * @return string
		 */
		public static function get_google_authorization_uri() {
			$credentials = self::get_google_credentials();
			if ( ! $credentials ) {
				return '';
			}

			$redirect_params = [
				'response_type' => 'code',
				'client_id'     => $credentials['clientID'],
				'redirect_uri'  => $credentials['clientRedirectUrl'],
				'scope'         => 'https://www.googleapis.com/auth/spreadsheets',
				'access_type'   => 'offline',
				'prompt'        => 'consent',
				'state'         => 'googlesheet',
			];

			return 'https://accounts.google.com/o/oauth2/auth?' . http_build_query( $redirect_params );
		}

		/**
		 * Perform a Google API request through the WordPress HTTP layer.
		 *
		 * @param string       $method       Request method.
		 * @param string       $url          Request URL.
		 * @param string       $access_token OAuth access token.
		 * @param array|string $body         Request body.
		 * @param string       $content_type Content-Type header.
		 * @return array
		 */
		private static function google_sheet_remote_request( $method = 'POST', $url = '', $access_token = '', $body = null, $content_type = 'application/json; charset=utf-8' ) {
			$headers = array(
				'Accept' => 'application/json',
			);

			if ( ! empty( $access_token ) ) {
				$headers['Authorization'] = 'Bearer ' . $access_token;
			}

			if ( ! empty( $content_type ) ) {
				$headers['Content-Type'] = $content_type;
			}

			$args = array(
				'method'  => strtoupper( $method ),
				'timeout' => 20,
				'headers' => $headers,
			);

			if ( null !== $body ) {
				$args['body'] = $body;
			}

			$response = wp_remote_request( $url, $args );
			if ( is_wp_error( $response ) ) {
				return array(
					'status_code' => 0,
					'body'        => '',
					'json'        => array(),
					'error'       => $response->get_error_message(),
				);
			}

			$response_body = wp_remote_retrieve_body( $response );
			return array(
				'status_code' => (int) wp_remote_retrieve_response_code( $response ),
				'body'        => $response_body,
				'json'        => json_decode( $response_body, true ),
				'error'       => '',
			);
		}
		
		/**
		 * @param string $code - Google server sent back code value into redirect URI as parameter
		 * While Google redirect Uri has 'code' as parameter then fetch access token based on 'code'
		 * Get Google Access Token value
		 * Save access token and related information like refresh token, expire date time etc as options data variable 'wordform_connect_google_sheet_token'
		 * @return boolean true | false
		 */
		public static function wordform_connect_configure_google_authentication( $code = null ) {
			$credentials = self::get_google_credentials();
			if ( ! is_null( $code ) && array_filter( $credentials ) ) {				
				// Execute cURL request to retrieve the access token
				$params = array(
					'code' => $code,
					'client_id' => $credentials['clientID'],
					'client_secret' => $credentials['clientSecret'],					
					'redirect_uri' => $credentials['clientRedirectUrl'],
					'grant_type' => 'authorization_code'
				);
				$existing_token_info = get_option( 'wordform_connect_google_sheet_token', [] );
				$response = self::google_sheet_remote_request( 'POST', 'https://oauth2.googleapis.com/token', '', $params, 'application/x-www-form-urlencoded' );
				$response_json = isset( $response['json'] ) && is_array( $response['json'] ) ? $response['json'] : array();
				if ( ! empty( $response['error'] ) ) {
					SftcyWordformConnectBase::log_connection_configuration_event( 'googlesheet', 'oauth_callback', $response['error'], array(), 'error' );
					update_option( 'wordform_connect_google_sheet_last_auth_error', sanitize_text_field( $response['error'] ) );
					return false;
				}

				$access_token = isset( $response_json['access_token'] ) ? sanitize_text_field( (string) $response_json['access_token'] ) : '';
				if ( empty( $access_token ) ) {
					$error_message = '';
					if ( isset( $response_json['error_description'] ) && ! empty( $response_json['error_description'] ) ) {
						$error_message = sanitize_textarea_field( (string) $response_json['error_description'] );
					} elseif ( isset( $response_json['error'] ) && ! empty( $response_json['error'] ) ) {
						$error_message = sanitize_text_field( (string) $response_json['error'] );
					} else {
						$error_message = 'Google did not return a valid access token for Google Sheet.';
					}
					SftcyWordformConnectBase::log_connection_configuration_event( 'googlesheet', 'oauth_callback', $error_message, array(), 'error' );
					update_option( 'wordform_connect_google_sheet_last_auth_error', $error_message );
					return false;
				}
				$access_token_expires_in = isset( $response_json['expires_in'] ) ? absint( $response_json['expires_in'] ) : 0;    // return seconds like: [expires_in] => 3599
				$refresh_token = isset( $response_json['refresh_token'] ) && ! empty( $response_json['refresh_token'] )
					? sanitize_text_field( (string) $response_json['refresh_token'] )
					: ( isset( $existing_token_info['refresh_token'] ) ? $existing_token_info['refresh_token'] : '' );
				// If refresh_token not set in response then app should be in published mode - not in testing mode
				$refresh_token_expires_in = isset( $response_json['refresh_token_expires_in'] ) ? absint( $response_json['refresh_token_expires_in'] ) : 0;    				
				$current_timestamp = time();

				// // Number of seconds to add
				// $seconds = $access_token_expires_in;
				// // Current date/time
				// $currentDateTime = new DateTime("now");
				// // Add seconds
				// $currentDateTime->add(new DateInterval('PT' . $seconds . 'S'));
				// // Output result
				// echo "Future Date/Time: " . $currentDateTime->format('Y-m-d H:i:s');

				$access_token_expired_timestamp = $current_timestamp + $access_token_expires_in; // $response['expires_in'] shows value in seconds
				// $response['refresh_token_expires_in'] shows value in seconds - when $response['refresh_token_expires_in'] not set then refresh token NOT expiring - published the app - not in testing mode
				$refresh_token_expired_timestamp = $refresh_token_expires_in > 0? ( $current_timestamp + $refresh_token_expires_in ) : ''; 

				$token_info = array( 
					'access_token' => $access_token,
					'refresh_token' => $refresh_token,
					'access_token_expire_datetime' => date('Y-m-d H:i:s', $access_token_expired_timestamp ),
					'refresh_token_expire_datetime' => empty( $refresh_token_expired_timestamp )
						? ( isset( $existing_token_info['refresh_token_expire_datetime'] ) ? $existing_token_info['refresh_token_expire_datetime'] : '' )
						: date('Y-m-d H:i:s', $refresh_token_expired_timestamp ),
					'tokeninfo_updated_datetime' => date('Y-m-d H:i:s', $current_timestamp )
				);

				// echo '<pre>';
				// print_r( $token_info );
				// echo '</pre>';
				// exit;

				$token_saved = false !== update_option( 'wordform_connect_google_sheet_token', $token_info );
				if ( ! $token_saved ) {
					$existing_saved_token = get_option( 'wordform_connect_google_sheet_token', array() );
					$token_saved = isset( $existing_saved_token['access_token'] ) && $existing_saved_token['access_token'] === $token_info['access_token']
						&& ( isset( $existing_saved_token['refresh_token'] ) ? $existing_saved_token['refresh_token'] : '' ) === $token_info['refresh_token'];
				}

				if ( $token_saved ) {		
					delete_option( 'wordform_connect_google_sheet_last_auth_error' );
					// Keep configured Google Sheet Application info into DB
					if ( self::save_configured_google_sheet_application() ) {			
						return true;
					}
					else {
						SftcyWordformConnectBase::log_connection_configuration_event( 'googlesheet', 'oauth_callback', 'Google Sheet token was received, but WordForm could not save the configured application state.', array(), 'error' );
						return false;
					}
				}			  
				else {
					SftcyWordformConnectBase::log_connection_configuration_event( 'googlesheet', 'oauth_callback', 'Google Sheet token information could not be stored.', array(), 'error' );
					return false;
				}
			}
			SftcyWordformConnectBase::log_connection_configuration_event( 'googlesheet', 'oauth_callback', 'Google Sheet authorization code or credentials are missing.', array(), 'error' );
			return false;				
		}

		/**
		 * Keep record while configured Google Sheet
		 * If already record found then reconfiguring the google sheet application
		 * If exist then just update else add new record
		 * DB record field application_slug will be 'googlesheet'
		 * DB record field application_name will be 'Google Sheet'
		 * @return void
		 */
		public static function save_configured_google_sheet_application() {
			$application_slug = 'googlesheet';
			$application_name = 'Google Sheet';							
			$data = [ 'application_slug' => $application_slug, 'application_name' => $application_name ];
			global $wpdb;
			$table         = $wpdb->prefix . SftcyWordformBase::$sc_wordform_configured_application_tbl;
			$query_results = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE application_slug = %s LIMIT 1', array( $table, $application_slug ) ), ARRAY_A );							
			if ( $query_results ) {
				// return $query_results[0]['id'];
				$where = [ 'id' => $query_results[0]['id'] ];
				$configured_info = json_encode( ['reconfigured' => 'yes' ] );
				$data['configured_info'] = $configured_info;
				$data['configured_status'] = 'active'; // Though as default value 'active', if re-authentication requires based on refresh token expires then field value changes into 'inactive'
				$update_result = $wpdb->update( $table, $data, $where );
				// returns an integer or false depending on the outcome of the operation - 0 means the query was successful, but no rows were actually changed - false: If there was an error during the execution of the query
				if ( $update_result === false ) {
					return false;
				}
				else if ( $update_result === 0 || $update_result > 0 ) {
					return true;
				}
				else {
					return false;
				}
			}
			else {					
				$configured_info = json_encode( ['reconfigured' => 'no' ] );
				$data['configured_info'] = $configured_info;
				return $wpdb->insert( $table, $data );					
			}			
		}

		/**
		 * Ajax Call - When Click on Modal Configure / Reconfigure Button
		 * Store Google Credentials - Client ID, Secret, Redirect Uri
		 * Generate Google authentication redirect URI
		 * @return array
		 */
		public static function wordform_save_google_auth_credentials_with_authentication() {
			check_ajax_referer( 'scwordform_wpnonce', 'security' );
			// print_r( $_POST['params'] );
			self::$output = [];
			if ( isset( $_POST['params'] ) && array_filter( $_POST['params'] ) ) {
				$google_client_id = isset( $_POST['params']['googleClientId'] )? $_POST['params']['googleClientId'] : '';
				$google_client_secret = isset( $_POST['params']['googleClientSecret'] )? $_POST['params']['googleClientSecret'] : '';
				$google_redirect_url = isset( $_POST['params']['googleRedirectUrl'] )? $_POST['params']['googleRedirectUrl'] : '';				
				if ( ! empty( $google_client_id ) && ! empty( $google_client_secret ) && ! empty( $google_redirect_url ) ) {
						$google_credentials = [ 'clientID' => $google_client_id, 
						                        'clientSecret' => $google_client_secret, 
												'clientRedirectUrl' => $google_redirect_url ];

                    // Google redirect URL parameters
					$redirect_params = [
						'response_type' => 'code',
						'client_id' => $google_client_id,						
						'redirect_uri' => $google_redirect_url,
						'scope' => 'https://www.googleapis.com/auth/spreadsheets',
						'access_type' => 'offline',
						'prompt' => 'consent',
						'state' => 'googlesheet',
					];

					// Calling this URL with params  - Google returns response 'code' into redirect URL
					self::$output['googleAuthCodeURI'] = 'https://accounts.google.com/o/oauth2/auth?' . http_build_query( $redirect_params );

					if ( update_option( 'wordform_connect_google_credentials', $google_credentials ) ) {
						self::$output['status'] = 'success';
						self::$output['reason'] = 'Credentials updated success.';
						self::$output['comment'] = self::$configure_spinning_icon . ' Credentials added / updated successfully. Redirecting to Google for Authorization...';
					}
					else {
						self::$output['status'] = 'warning';
						self::$output['reason'] = 'Credentials did not change.';
						self::$output['comment'] = self::$configure_spinning_icon . ' No changes of Credentials. Redirecting to Google for Authorization...';
					}
				}
			}
			else {
				self::$output['status'] = 'fail';
				self::$output['reason'] = 'Post params are missing.';
				self::$output['comment'] = 'Required parameters Google Client ID, Secret are missing.';
			}			
			echo wp_json_encode( self::$output, JSON_HEX_AMP );
			wp_die();
		}

		/**
		 * Ajax Call - When Modal window opened with Connect Button
		 * Based on wordform_id get form fields information from DB
		 * process selected form fileds to display
		 * Also check same wordform_id and configured_application_tbl_id already exist in table 'scwordform_connects_data' or not
		 * If exist in DB record then selected wordform and application already connected
		 * 
		 * @return json object
		 * @since 1.0.0
		 */
		public static function wordform_connect_form_n_display_form_fields() {
			check_ajax_referer( 'scwordform_wpnonce', 'security' );
			// print_r( $_POST['params'] );
			// exit;
			self::$output = [];
			if ( isset( $_POST['params'] ) && array_filter( $_POST['params'] ) ) {
				$wordform_id = isset( $_POST['params']['wordFormID'] )? $_POST['params']['wordFormID'] : '';
				$application_slug = isset( $_POST['params']['applicationSlug'] )? $_POST['params']['applicationSlug'] : '';
				$wordform_name = isset( $_POST['params']['wordFormName'] )? $_POST['params']['wordFormName'] : '';

				$configured_application_id = isset( $_POST['params']['applicationID'] )? $_POST['params']['applicationID'] : '';
				$configured_application_name = isset( $_POST['params']['applicationName'] )? $_POST['params']['applicationName'] : '';
				

				if ( ! empty( $wordform_id ) && ! empty( $configured_application_id ) ) {

					$form_fields       = SftcyWordformBuildForm::wordform_get_field_names( $wordform_id );					
					// print_r( $form_fields );					
					if ( $form_fields && array_filter( $form_fields ) ) {
						self::$output['formFields'] = $form_fields;
						self::$output['formName'] = $wordform_name;
						self::$output['isConnected'] = self::is_wordform_with_application_connected( $configured_application_id, $wordform_id );
						self::$output['connectedApplicationSlug'] = $application_slug;
						self::$output['status'] = 'success';
						self::$output['reason'] = 'Form fields are available.';
						self::$output['comment'] = '';
					}
					else {
						self::$output['status'] = 'fail';
						self::$output['reason'] = 'Form fields are NOT available.';
						self::$output['comment'] = 'Form should have fields to connect.';
					}
				}
			}
			else {
				self::$output['status'] = 'fail';
				self::$output['reason'] = 'WordForm ID missing.';
				self::$output['comment'] = 'WordForm ID or Configured Application ID not found.';
			}			
			echo wp_json_encode( self::$output, JSON_HEX_AMP );
			wp_die();
		}

		/**
		 * Ajax Call
		 * While Click on 'Connect' Button
		 * First Create Google Sheet as the name of Wordform name
		 * Then create header column names
		 * @since 1.0.0
		 * @return json object
		 */
		public static function wordform_connect_create_googlesheet_with_form_fields() {
			check_ajax_referer( 'scwordform_wpnonce', 'security' );
			// print_r( $_POST['params'] );
			// exit;
			self::$output = [];
			if ( isset( $_POST['params'] ) && array_filter( $_POST['params'] ) ) {
				$wordform_id = isset( $_POST['params']['wordFormID'] )? $_POST['params']['wordFormID'] : '';
				$wordform_name = isset( $_POST['params']['wordFormName'] )? $_POST['params']['wordFormName'] : '';

				$configured_application_id = isset( $_POST['params']['applicationID'] )? $_POST['params']['applicationID'] : '';
				$configured_application_name = isset( $_POST['params']['applicationName'] )? $_POST['params']['applicationName'] : '';
				
				if ( ! empty( $wordform_id ) && ! empty( $configured_application_id ) ) {
					$form_fields       = SftcyWordformBuildForm::wordform_get_field_names( $wordform_id );					
					// print_r( $form_fields );											
					// exit();
					if ( $form_fields && array_filter( $form_fields ) ) {						
						self::$output['formFields'] = $form_fields;						
						self::$output['formName'] = $wordform_name;
						$access_token = self::get_access_token();
						// Create SpreadSheet
						if ( $access_token && isset( $access_token ) && ! empty( $access_token ) ) {	
							// SpreadSheet properties
							$data['properties'] = [ 
										'title'  => $wordform_name										
									];
							$json_data = json_encode( $data );
							$response = self::google_sheet_remote_request( 'POST', 'https://sheets.googleapis.com/v4/spreadsheets', $access_token, $json_data );
							$response_data = isset( $response['json'] ) && is_array( $response['json'] ) ? $response['json'] : array();
							// print_r( $response_data );
							if ( isset( $response_data['spreadsheetId'] ) && $response_data['spreadsheetId'] ) {								
								$spreadsheet_id = $response_data['spreadsheetId'];	
								$spreadsheet_url = $response_data['spreadsheetUrl'];
								$create_column_names_result = self::wordform_google_sheet_insert_append_columns_fields( $spreadsheet_id, $form_fields );
								if ( array_filter( $create_column_names_result  ) && isset( $create_column_names_result['status'] ) && $create_column_names_result['status'] == 'success' ) {
									self::$output['status'] = 'success';
									self::$output['spreadSheetID'] = $spreadsheet_id;
									self::$output['spreadSheetUrl'] = $spreadsheet_url;
									self::$output['reason'] = 'Created SpreadSheet ID: '. $spreadsheet_id .'';
									self::$output['comment'] = 'Created SpreadSheet Successfully.';
									// Create database record into 'scwordform_connects_data' Table
									$connection_info = [ 'spreadsheetId' => $spreadsheet_id, 'spreadsheetUrl' => $spreadsheet_url ];
									$insertdata = [ 
													'configured_application_tbl_id' => $configured_application_id,
													'wordform_id' => $wordform_id,
													'connection_info' => json_encode( $connection_info )
												  ];
									self::$output['connectedInfoStored'] = self::wordform_save_connected_application_info( $insertdata );			  	
								}
								else {
									self::$output['status'] = 'fail';									
									self::$output['reason'] = 'SpreadSheet Created ( ID: '. $spreadsheet_id .' ) but sheet header names creation failed.';
									self::$output['comment'] = 'Something went wrong during creating of Google SpreadSheet Header Names. Try again.';
								}
							}
							else {
								self::$output['status'] = 'fail';
								self::$output['errorDetail'] = isset( $response_data['error'] )? $response_data['error']['message'] : '';
								self::$output['reason'] = 'SpreadSheet Creation failed.';
								self::$output['comment'] = 'Something went wrong during creating of Google SpreadSheet. Try again.';
							}
						}
						else {
							self::$output['status'] = 'fail';
							self::$output['reason'] = 'Access Token Not Found.';
							self::$output['comment'] = 'Something went wrong. Please reconfigure the Connection.';
						}
					}
					else {
						self::$output['status'] = 'fail';
						self::$output['reason'] = 'Form fields are NOT available.';
						self::$output['comment'] = 'Form should have fields to connect.';
					}
				}
			}
			else {
				self::$output['status'] = 'fail';
				self::$output['reason'] = 'WordForm ID missing.';
				self::$output['comment'] = 'WordForm ID or Configured Application ID not found.';
			}			
			echo wp_json_encode( self::$output, JSON_HEX_AMP );
			wp_die();			
		}

		/**
		 * Ajax Call
		 * While Click on 'ReConnect' Button
		 * Overwrite previous Google Sheet header columns
		 * 
		 * @since 1.0.0
		 * @return json object
		 */
		public static function wordform_connect_reconnect_googlesheet_with_form_fields() {
			check_ajax_referer( 'scwordform_wpnonce', 'security' );
			// print_r( $_POST['params'] );
			// exit;
			self::$output = [];
			if ( isset( $_POST['params'] ) && array_filter( $_POST['params'] ) ) {
				$wordform_id = isset( $_POST['params']['wordFormID'] )? $_POST['params']['wordFormID'] : '';
				$wordform_name = isset( $_POST['params']['wordFormName'] )? $_POST['params']['wordFormName'] : '';

				$configured_application_id = isset( $_POST['params']['applicationID'] )? $_POST['params']['applicationID'] : '';
				$configured_application_name = isset( $_POST['params']['applicationName'] )? $_POST['params']['applicationName'] : '';
				
				if ( ! empty( $wordform_id ) && ! empty( $configured_application_id ) ) {					
					$form_fields    = SftcyWordformBuildForm::wordform_get_field_names( $wordform_id );					
					// print_r( $form_fields );											
					// exit();
					if ( $form_fields && array_filter( $form_fields ) ) {	
						$spreadsheet_id = self::wordform_get_spreadsheet_id( $configured_application_id, $wordform_id );					
						self::$output['formFields'] = $form_fields;						
						self::$output['formName'] = $wordform_name;
						if ( isset( $spreadsheet_id ) && $spreadsheet_id ) {																												
							$overwrite_column_names_result = self::wordform_google_sheet_overwrite_columns_fields( $spreadsheet_id, $form_fields );								
							if ( array_filter( $overwrite_column_names_result  ) && isset( $overwrite_column_names_result['status'] ) && $overwrite_column_names_result['status'] == 'success' ) {
								self::$output['status'] = 'success';
								self::$output['spreadSheetID'] = $spreadsheet_id;								
								self::$output['reason'] = 'Updated Data on SpreadSheet ID: '. $spreadsheet_id .'';
								self::$output['comment'] = 'Updated SpreadSheet Successfully.';
								// Create database record into 'scwordform_connects_data' Table
								// $connection_info = [ 'spreadsheetId' => $spreadsheet_id, 'spreadsheetUrl' => $spreadsheet_url ];
								// $insertdata = [ 
												// 'configured_application_tbl_id' => $configured_application_id,
												// 'wordform_id' => $wordform_id,
												// 'connection_info' => json_encode( $connection_info )
											//   ];
								// self::$output['connectedInfoStored'] = self::wordform_save_connected_application_info( $insertdata );			  	
							}
							else {
								self::$output['status'] = 'fail';		
								self::$output['errorDetail'] = isset( $overwrite_column_names_result['errorDetail'] )? $overwrite_column_names_result['errorDetail'] : '';							
								self::$output['reason'] = 'SpreadSheet ( ID: '. $spreadsheet_id .' ) updating header names failed.';
								self::$output['comment'] = 'Something went wrong during updating of Google SpreadSheet Header Names. Try again.';
							}
						}
						else {
								self::$output['status'] = 'fail';								
								self::$output['reason'] = 'SpreadSheet ID not found in DB.';
								self::$output['comment'] = 'Something went wrong, Google SpreadSheet Information required. Try again.';
						}
					}
					else {
						self::$output['status'] = 'fail';
						self::$output['reason'] = 'Form fields are NOT available.';
						self::$output['comment'] = 'Form should have fields to connect.';
					}
				}
			}
			else {
				self::$output['status'] = 'fail';
				self::$output['reason'] = 'WordForm ID missing.';
				self::$output['comment'] = 'WordForm ID or Configured Application ID not found.';
			}			
			echo wp_json_encode( self::$output, JSON_HEX_AMP );
			wp_die();			
		}


		/**
		 * Google Sheet API Call - Append
		 * @param string $spreadsheet_id
		 * @param Array $form_fields		 
		 * Append Header Columns or Fields Values into Google Sheet		 
		 * @since 1.0.0
		 * @return Array - Output Results
		 */
		public static function wordform_google_sheet_insert_append_columns_fields( $spreadsheet_id = '', $form_fields = [] ) {
			self::$output = [];
			self::$output['applicationSlug'] = 'googlesheet';
			$access_token = self::get_access_token();
			if ( $access_token && isset( $access_token ) && ! empty( $access_token ) ) {	
				// Process Columns or Fields Range : A1:D1 , A1:F1 - based on total columns or fields number	
				// A1 notation Ref: https://developers.google.com/workspace/sheets/api/guides/concepts#cell					
				$columns_number = count( $form_fields );
				$alphabet = range('A', 'Z');
				$end_column_letter = $alphabet[ $columns_number - 1 ]; // Find column ending letter based on number of fields
				$columns_range_notation = 'A1:' . $end_column_letter . '1';
				$append_api_endpoint_url = 'https://sheets.googleapis.com/v4/spreadsheets/'. $spreadsheet_id . '/values/'. $columns_range_notation . ':append?valueInputOption=RAW';						
				// var_dump( $append_api_endpoint_url );
				// exit();

				// Append Columns or Fields into SpreadSheet - Single Row Format: [ [ 'Name', 'Email Address', 'Your Message'] ] Multiple Rows Format: [ ['Catherine', 'crine@test.com', 'Japaan'], ['John', 'john@test.com', 'Singapore'] ]         
				$data['values'] = [ $form_fields ];       
				$data_json = json_encode( $data );                                     			
				$response = self::google_sheet_remote_request( 'POST', $append_api_endpoint_url, $access_token, $data_json );
				$response_data = isset( $response['json'] ) && is_array( $response['json'] ) ? $response['json'] : array();				
				if ( isset( $response_data['spreadsheetId'] ) && ! empty( $response_data['spreadsheetId'] ) ) {
					self::$output['status'] = 'success';
					self::$output['reason'] = 'Inserted or Appended data into Google Sheet.';
					self::$output['comment'] = 'Added data successfully.';
				}
				else {
					self::$output['status'] = 'fail';
					self::$output['errorDetail'] = isset( $response_data['error'] )? $response_data['error']['message'] : '';
					self::$output['reason'] = 'Google Server response error.Failed to insert or append data into Google Sheet.';
					self::$output['comment'] = 'something went wrong please try again.';
				}			
			}
			else {
				self::$output['status'] = 'fail';
				self::$output['reason'] = 'Access Token Not Found.';
				self::$output['comment'] = 'Something went wrong. Please reconfigure the Connection.';
			}
			return self::$output;			
		}

		/**
		 * Google Sheet API Call - Update / Overwrite
		 * @param string $spreadsheet_id
		 * @param Array $form_fields		 
		 * Update Header Columns or Fields Values into Google Sheet		 
		 * @since 1.0.0
		 * @return Array - Output Results
		 */
		public static function wordform_google_sheet_overwrite_columns_fields( $spreadsheet_id = '', $form_fields = [] ) {
			self::$output = [];
			$access_token = self::get_access_token();
			if ( $access_token && isset( $access_token ) && ! empty( $access_token ) ) {	
				// Process Columns or Fields Range : A1:D1 , A1:F1 - based on total columns or fields number	
				// Batch Update Ref: https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
				// A1 notation Ref: https://developers.google.com/workspace/sheets/api/guides/concepts#cell					
				$columns_number = count( $form_fields );
				$alphabet = range('A', 'Z');
				$end_column_letter = $alphabet[ $columns_number - 1 ]; // Find column ending letter based on number of fields
				$columns_range_notation = 'A1:' . $end_column_letter . '1';								
				// $columns_range_notation = 'A1:D1';
				$append_api_endpoint_url = 'https://sheets.googleapis.com/v4/spreadsheets/' . $spreadsheet_id . '/values:batchUpdate';
				// var_dump( $append_api_endpoint_url );
				// exit();

				// Clear First Row values - If earlier header columns was 5 fields and 'Reconnect' same form have 3 columns then earlier 2 columns should not kept - so before update clear first Row all fields
				self::$output['clearedFirstRowBeforeUpdate'] = self::wordform_curl_clear_first_row_googlesheet_fields( $spreadsheet_id, $access_token );				

				// Columns or Fields into SpreadSheet - Single Row Format: [ [ 'Name', 'Email Address', 'Your Message'] ] Multiple Rows Format: [ ['Catherine', 'crine@test.com', 'Japaan'], ['John', 'john@test.com', 'Singapore'] ]         
				$data['valueInputOption'] = 'RAW';				
				$data['data']['range'] = $columns_range_notation;  
				$data['data']['values'] = [ $form_fields ];  
				$data_json = json_encode( $data );             				                        			

				$response = self::google_sheet_remote_request( 'POST', $append_api_endpoint_url, $access_token, $data_json );
				$response_data = isset( $response['json'] ) && is_array( $response['json'] ) ? $response['json'] : array();				
				if ( isset( $response_data['spreadsheetId'] ) && ! empty( $response_data['spreadsheetId'] ) ) {
					self::$output['status'] = 'success';
					self::$output['reason'] = 'Updated data into Google Sheet.';
					self::$output['comment'] = 'Updated data successfully.';
				}
				else {
					self::$output['status'] = 'fail';
					self::$output['errorDetail'] = isset( $response_data['error'] )? $response_data['error']['message'] : '';
					self::$output['reason'] = 'Failed to insert or append data into Google Sheet.';
					self::$output['comment'] = 'something went wrong please try again.';
				}			
			}
			else {
				self::$output['status'] = 'fail';
				self::$output['reason'] = 'Access Token Not Found.';
				self::$output['comment'] = 'Something went wrong. Please reconfigure the Connection.';
			}
			return self::$output;			
		}		

		/**
		 * Clear header names or column names of first Row ( A1:Z1 )
		 * cURL clear first row all cell values
		 * @return boolean true | false
		 */
		public static function wordform_curl_clear_first_row_googlesheet_fields( $spreadsheet_id = null, $access_token = null ) {
			// Ref: https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/clear			
			$append_api_endpoint_url = 'https://sheets.googleapis.com/v4/spreadsheets/' . $spreadsheet_id . '/values/A1:Z1:clear';
			$response = self::google_sheet_remote_request( 'POST', $append_api_endpoint_url, $access_token, '' );
			$response_data = isset( $response['json'] ) && is_array( $response['json'] ) ? $response['json'] : array();				
			if ( isset( $response_data['spreadsheetId'] ) ) {
				return true;
			}
			return false;
		}

		/**
		 * Save connected Application information into DB 
		 * 'scwordform_connects_data' Table
		 */
		public static function wordform_save_connected_application_info( $data = [] ) {
			return SftcyWordformConnectBase::wordform_save_connected_application_info( $data );
		}

		/**
		 * Check selected Wordform already connected with the same Application
		 * @param integer $configured_application_tbl_id
		 * @param string $wordform_id
		 * @since 1.0.0
		 * @return boolean true | false
		 */
		public static function is_wordform_with_application_connected( $configured_application_tbl_id = 0, $wordform_id = '' ) {
			return SftcyWordformConnectBase::is_wordform_with_application_connected( $configured_application_tbl_id, $wordform_id );
		}

		/**
		 * Get applicated slug value based on configured application ID
		 * @param integer $configured_application_tbl_id
		 * @return string - application_slug value
		 */
		public static function wordform_get_application_slug_by_configured_application_id( $configured_application_tbl_id = 0 ) {
			return SftcyWordformConnectBase::wordform_get_application_slug_by_configured_application_id( $configured_application_tbl_id );
		}

		/**
		 * Get Google Sheet SpreadSheet ID of selected Wordform 
		 * @param integer $configured_application_tbl_id
		 * @param string $wordform_id
		 * @since 1.0.0
		 * @return boolean true | false
		 */
		public static function wordform_get_spreadsheet_id( $configured_application_tbl_id = 0, $wordform_id = '' ) {
			if ( $configured_application_tbl_id <= 0 || empty( $wordform_id) ) {
				return false;
			}
			global $wpdb;
			$table  = $wpdb->prefix . SftcyWordformBase::$sc_wordform_connects_tbl;
			$wordform_status = 'active';
			$connection_status = 'active';									
			$query_results = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE wordform_id = %s AND configured_application_tbl_id = %d LIMIT 1', array( $table, $wordform_id, $configured_application_tbl_id ) ), ARRAY_A );							
			// If wordform 'wordform_id' and application 'configured_application_tbl_id' record exist in 'scwordform_connects_data' table
			if ( $query_results ) {
				$connect_info_json = $query_results[0]['connection_info'];
				$connect_info_arr = json_decode( $connect_info_json, true );
				if ( isset( $connect_info_arr['spreadsheetId'] ) && ! empty( $connect_info_arr['spreadsheetId'] ) ) {
					return $connect_info_arr['spreadsheetId'];
				}
				else {
					return false;
				}	
			}
			else {													
				return false;
			}			
			return false;
		}

		
		/**
		 * Check if Access Token is expired or not		 
		 * @return boolean
		 */
		public static function is_google_access_token_expired() {
			$current_datetime = new DateTime('now');
			$token_info = get_option('wordform_connect_google_sheet_token', [] );
			if ( isset ( $token_info['access_token'] ) && isset( $token_info['access_token_expire_datetime'] ) ) {
				$access_token_expired_datetime = new DateTime( $token_info['access_token_expire_datetime'] );
				if ( $current_datetime < $access_token_expired_datetime ) {
					return false;
				}
				else {
					return true;
				}
			}
			return true;
		}

		/**
		 * Usually in testing mode refresh token expire in certain time like 7 days 
		 * but when app published mode then refresh token do not have any expire time - so NOT expiring 
		 * Check if Refresh Token is expired time is set or not
		 * If refresh token expire datetime empty then no need to re-authenticate - just fetch new access token as required using the old refresh token
		 * @return boolean
		 */
		public static function is_google_refresh_token_expired() {			
			$current_datetime = new DateTime('now');
			$token_info = get_option('wordform_connect_google_sheet_token', [] );
			// In testing mode when refresh token expire in certain time of period like 7 days - refresh_token expire datetime set and NOT empty - so check expired or not
			if ( isset ( $token_info['refresh_token'] ) && ! empty( $token_info['refresh_token'] ) && isset( $token_info['refresh_token_expire_datetime'] ) && ! empty( $token_info['refresh_token_expire_datetime'] ) ) {
				$refresh_token_expired_datetime = new DateTime( $token_info['refresh_token_expire_datetime'] );
				if ( $current_datetime < $refresh_token_expired_datetime ) {
					return false;
				}
				else {
					return true;
				}
			}
			// refresh_token does not have expire date time - usually app should be in published mode
			else if ( isset ( $token_info['refresh_token'] ) && ! empty( $token_info['refresh_token'] ) && isset( $token_info['refresh_token_expire_datetime'] ) && empty( $token_info['refresh_token_expire_datetime'] ) ) {
				return false; 
			}
			else {
				return true;
			}

			return true;
		}

		/**
		 * Check Re-Authentication required or not		 
		 * If refresh token expired then have to authenticate again - in case of published app ( not in testing mode ) refresh token usually not expiring
		 * Usually when Access Token expired then based on earlier or old refresh token we can get new Access Token
		 * If refresh token expired only in that case re-authentication required
		 * @return boolean
		 */
		public static function is_google_authencation_required() {
			$token_info = get_option('wordform_connect_google_sheet_token', [] );
			$has_valid_access_token = isset( $token_info['access_token'] ) && ! empty( $token_info['access_token'] ) && ! self::is_google_access_token_expired();
			$has_valid_refresh_token = isset( $token_info['refresh_token'] ) && ! empty( $token_info['refresh_token'] ) && ! self::is_google_refresh_token_expired();
			return ! $has_valid_access_token && ! $has_valid_refresh_token;
		}

		/**
		 * Get Access Token
		 * If Access Token is expired then fetch new Access Token using earlier Refresh Token
		 * @return string Access Token
		 */
		public static function get_access_token() {
			$token_info = get_option('wordform_connect_google_sheet_token', [] );
			// When Access Token not expired
			if ( isset( $token_info['access_token'] ) && ! empty( $token_info['access_token'] ) && ! self::is_google_access_token_expired() ) {
				return $token_info['access_token'];
			}
			// When Access Token expired - Fetch new Access Token
			else if ( isset( $token_info['access_token'] ) && ! empty( $token_info['access_token'] ) && self::is_google_access_token_expired() ) {
				$access_token = self::fetch_new_access_token();
				if ( $access_token && ! empty( $access_token )  ) {
					return $access_token;
				}
				else {
					return false;
				}
			}
			else if ( isset( $token_info['refresh_token'] ) && ! empty( $token_info['refresh_token'] ) && ! self::is_google_refresh_token_expired() ) {
				$access_token = self::fetch_new_access_token();
				if ( $access_token && ! empty( $access_token ) ) {
					return $access_token;
				}
			}
			return false;
		}

		/**
		 * Fetch new Access Token using earlier or old Refresh Token
		 * When Access Token expired then we need to fetch New Access Token using old Refresh Token
		 * @param string $refresh_token - old Refresh Token
		 * @return string - new Access Token
		 */
		public static function fetch_new_access_token() {
			$access_token = '';			
			$google_credentials = self::get_google_credentials();
			$token_info = get_option('wordform_connect_google_sheet_token', [] );
			if ( isset( $google_credentials['clientID'] ) && ! empty( $google_credentials['clientID'] ) 
				&& isset( $google_credentials['clientSecret'] ) && ! empty( $google_credentials['clientSecret'] ) 
					&& isset( $token_info['refresh_token'] ) && ! empty( $token_info['refresh_token'] ) ) {
					$response = self::google_sheet_remote_request(
						'POST',
						'https://oauth2.googleapis.com/token',
						'',
						array(
							'client_id'     => $google_credentials['clientID'],						
							'client_secret' => $google_credentials['clientSecret'],						
							'refresh_token' => $token_info['refresh_token'],
							'grant_type'    => 'refresh_token',
						),
						'application/x-www-form-urlencoded'
					);
					$response_refresh_token = isset( $response['json'] ) && is_array( $response['json'] ) ? $response['json'] : array();
					// echo '<pre>';
					// print_r( $response_refresh_token );
					// echo '</pre>';
					// exit();

					$access_token = isset( $response_refresh_token['access_token'] )? $response_refresh_token['access_token'] : '';
					if ( ! empty( $access_token ) ) {
						$access_token_expires_in = isset( $response_refresh_token['expires_in'] ) ? absint( $response_refresh_token['expires_in'] ) : 0;
						$refresh_token = isset( $token_info['refresh_token'] )? $token_info['refresh_token'] : '';
						$refresh_token_expires_in = isset( $response_refresh_token['refresh_token_expires_in'] ) ? absint( $response_refresh_token['refresh_token_expires_in'] ) : 0;
						$current_timestamp = time();
						$access_token_expired_timestamp = $current_timestamp + $access_token_expires_in; // $response['expires_in'] shows value in seconds -convert into datetime
						$refresh_token_expired_datetime = '';
						if ( $refresh_token_expires_in > 0 ) {
							$refresh_token_expired_datetime = date( 'Y-m-d H:i:s', $current_timestamp + $refresh_token_expires_in );
						} elseif ( isset( $token_info['refresh_token_expire_datetime'] ) ) {
							$refresh_token_expired_datetime = $token_info['refresh_token_expire_datetime'];
						}

						$new_token_info = [ 
										'access_token' => $access_token,
										'refresh_token' => $refresh_token,
										'access_token_expire_datetime' => date('Y-m-d H:i:s', $access_token_expired_timestamp ),
										'refresh_token_expire_datetime' => $refresh_token_expired_datetime,
										'tokeninfo_updated_datetime' => date('Y-m-d H:i:s', $current_timestamp )
						];
						update_option( 'wordform_connect_google_sheet_token', $new_token_info );
					}
			}
			return $access_token;
		}

		/**
		 * Check 'configured_status' of the application is active or inactive
		 * @return boolean
		 */
		public static function is_googlesheet_configured_status_active() {
			$application_slug = 'googlesheet';			
			$data = [ 'application_slug' => $application_slug ];
			global $wpdb;
			$table         = $wpdb->prefix . SftcyWordformBase::$sc_wordform_configured_application_tbl;
			$query_results = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE application_slug = %s LIMIT 1', array( $table, $application_slug ) ), ARRAY_A );							
			if ( $query_results && isset( $query_results[0]['configured_status'] ) && $query_results[0]['configured_status'] == 'active' )  {				
				return true;
			}
			else {
				return false;
			}
		}


	} // End class
}
