/**
 * Retrieve a Google Access Token using the service account credentials stored in the admin option.
 *
 * @return mixed Access token string on success, false on failure.
 */
function get_google_access_token() {
    $credentials_json = get_option('decree_ai_google_credentials', '');
    if (empty($credentials_json)) {
        return false;
    }
    $credentials = json_decode($credentials_json, true);
    if (!$credentials || !isset($credentials['client_email'], $credentials['private_key'], $credentials['token_uri'])) {
        return false;
    }

    $client_email = $credentials['client_email'];
    $private_key = $credentials['private_key'];
    $token_uri = $credentials['token_uri'];

    $now = time();
    $header = ['alg' => 'RS256', 'typ' => 'JWT'];
    $payload = [
        'iss' => $client_email,
        'scope' => 'https://www.googleapis.com/auth/spreadsheets',
        'aud' => $token_uri,
        'exp' => $now + 3600,
        'iat' => $now
    ];
    $base64UrlEncode = function ($data) {
        return rtrim(strtr(base64_encode(json_encode($data)), '+/', '-_'), '=');
    };
    $header_encoded = $base64UrlEncode($header);
    $payload_encoded = $base64UrlEncode($payload);
    $signature_input = $header_encoded . '.' . $payload_encoded;

    $signature = '';
    openssl_sign($signature_input, $signature, $private_key, 'SHA256');
    $signature_encoded = rtrim(strtr(base64_encode($signature), '+/', '-_'), '=');

    $jwt = $signature_input . '.' . $signature_encoded;

    $post_fields = http_build_query([
        'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
        'assertion' => $jwt
    ]);

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $token_uri);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $post_fields);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);

    $responseData = json_decode($response, true);
    if (isset($responseData['access_token'])) {
        return $responseData['access_token'];
    }
    return false;
}

/**
 * Append query details (timestamp, current user ID, query, answer, Pinecone context)
 * to the designated Google Sheet.
 *
 * @param string $query           The text submitted by the user.
 * @param string $answer          The response from OpenAI.
 * @param string $pinecone_context The context returned from Pinecone.
 * @return mixed The result from the Sheets API call.
 */
function append_to_google_sheet($query, $answer, $pinecone_context) {
    $timestamp = date('c');
    $user_id = get_current_user_id();
    $values = [[ $timestamp, $user_id, $query, $answer, $pinecone_context ]];
    $body = json_encode([ 'values' => $values ]);

    $spreadsheetId = '1QpNFetkT-_HlxuxZQrWtzUIIVQTwiFtaMIJJAjvsUwk';
    $range = 'Sheet1!A:E';
    $url = 'https://sheets.googleapis.com/v4/spreadsheets/' . $spreadsheetId . '/values/' . $range . ':append?valueInputOption=USER_ENTERED';

    $accessToken = get_google_access_token();
    if (!$accessToken) {
        return false;
    }

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json',
        'Authorization: Bearer ' . $accessToken
    ]);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $body);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $result = curl_exec($ch);
    curl_close($ch);

    return $result;
}

/**
 * Append query details with vote information to the designated Google Sheet.
 *
 * @param string $query            The text submitted by the user.
 * @param string $answer           The response from OpenAI.
 * @param string $pinecone_context The context returned from Pinecone.
 * @param string $vote             'Thumbs Up' or 'Thumbs Down'.
 * @return mixed The result from the Sheets API call.
 */
function append_vote_to_google_sheet($query, $answer, $pinecone_context, $vote) {
    $timestamp = date('c');
    $user_id = get_current_user_id();
    $values = [[ $timestamp, $user_id, $query, $answer, $pinecone_context, $vote ]];
    $body = json_encode([ 'values' => $values ]);

    $spreadsheetId = '1QpNFetkT-_HlxuxZQrWtzUIIVQTwiFtaMIJJAjvsUwk';
    $range = 'Sheet1!A:F';
    $url = 'https://sheets.googleapis.com/v4/spreadsheets/' . $spreadsheetId . '/values/' . $range . ':append?valueInputOption=USER_ENTERED';

    $accessToken = get_google_access_token();
    if (!$accessToken) {
        return false;
    }

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json',
        'Authorization: Bearer ' . $accessToken
    ]);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $body);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $result = curl_exec($ch);
    curl_close($ch);

    return $result;
}<?xml version="1.0"?>
<oembed><version>1.0</version><provider_name>Riyadh Al-Balushi</provider_name><provider_url>https://riyadh.om</provider_url><author_name>admin</author_name><author_url>https://riyadh.om/author/admin/</author_url><title>Twitter Usename Room214 Infringes Trademark?</title><type>rich</type><width>600</width><height>338</height><html>&lt;blockquote class="wp-embedded-content" data-secret="DdCyN34aGt"&gt;&lt;a href="https://riyadh.om/2008/twitter-trademark-room-21/"&gt;Twitter Usename Room214 Infringes Trademark?&lt;/a&gt;&lt;/blockquote&gt;&lt;iframe sandbox="allow-scripts" security="restricted" src="https://riyadh.om/2008/twitter-trademark-room-21/embed/#?secret=DdCyN34aGt" width="600" height="338" title="&#x201C;Twitter Usename Room214 Infringes Trademark?&#x201D; &#x2014; Riyadh Al-Balushi" data-secret="DdCyN34aGt" frameborder="0" marginwidth="0" marginheight="0" scrolling="no" class="wp-embedded-content"&gt;&lt;/iframe&gt;&lt;script&gt;
/*! This file is auto-generated */
!function(d,l){"use strict";l.querySelector&amp;&amp;d.addEventListener&amp;&amp;"undefined"!=typeof URL&amp;&amp;(d.wp=d.wp||{},d.wp.receiveEmbedMessage||(d.wp.receiveEmbedMessage=function(e){var t=e.data;if((t||t.secret||t.message||t.value)&amp;&amp;!/[^a-zA-Z0-9]/.test(t.secret)){for(var s,r,n,a=l.querySelectorAll('iframe[data-secret="'+t.secret+'"]'),o=l.querySelectorAll('blockquote[data-secret="'+t.secret+'"]'),c=new RegExp("^https?:$","i"),i=0;i&lt;o.length;i++)o[i].style.display="none";for(i=0;i&lt;a.length;i++)s=a[i],e.source===s.contentWindow&amp;&amp;(s.removeAttribute("style"),"height"===t.message?(1e3&lt;(r=parseInt(t.value,10))?r=1e3:~~r&lt;200&amp;&amp;(r=200),s.height=r):"link"===t.message&amp;&amp;(r=new URL(s.getAttribute("src")),n=new URL(t.value),c.test(n.protocol))&amp;&amp;n.host===r.host&amp;&amp;l.activeElement===s&amp;&amp;(d.top.location.href=t.value))}},d.addEventListener("message",d.wp.receiveEmbedMessage,!1),l.addEventListener("DOMContentLoaded",function(){for(var e,t,s=l.querySelectorAll("iframe.wp-embedded-content"),r=0;r&lt;s.length;r++)(t=(e=s[r]).getAttribute("data-secret"))||(t=Math.random().toString(36).substring(2,12),e.src+="#?secret="+t,e.setAttribute("data-secret",t)),e.contentWindow.postMessage({message:"ready",secret:t},"*")},!1)))}(window,document);
&lt;/script&gt;
</html></oembed>