<?php

namespace App\Console\Commands\GoogleSheet;

ini_set('memory_limit', '512M');

use App\Setting;
use Google_Service_Sheets_BatchUpdateSpreadsheetRequest;
use Illuminate\Console\Command;
use Google_Client;
use Google_Service_Sheets;
use Google_Service_Sheets_ValueRange;
use Google\Service\Sheets\ClearValuesRequest;

use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Route;
/**
 *
 */
class UpdateGoogleSheet extends Command
{

    protected $signature = 'update:google-sheet';

    protected $description = 'Update Yajny Google Sheet Daily Data Studio ';

    protected $batchSize= 5000;

    protected $startPage= 1;
    protected $firstClear= false;

    protected $totalRecords= 0;
    protected $rowToStartFrom= 0;
    protected $inittotal= 0;

    protected $apiUrl=  ''  ;

    protected $spreadsheetId = '';
    protected $sheetName = ''; // Replace with your sheet name


    public function __construct()
    {
        parent::__construct();
        $this->apiUrl= route('daily.data.studio');
        $this->spreadsheetId=  Setting::where('setting_key','daily_data_spreadsheet_Id')->value('setting_value');
        $this->sheetName=  Setting::where('setting_key','daily_data_sheet_name')->value('setting_value');
        $this->rowToStartFrom=  Setting::where('setting_key','row_to_start_from')->value('setting_value');
        $this->inittotal = $this->rowToStartFrom;


    }

    /**
     * @throws \Google\Exception
     */
    public function handle()
    {
        $startTime = microtime(true);

        // Load the Google API Client and authenticate
        $client = new Google_Client();
        $client->setAuthConfig('public/yajny-data-daily-cc00faca04c6.json');
        $client->addScope(Google_Service_Sheets::SPREADSHEETS);
        $service = new Google_Service_Sheets($client);
        // Your code to update the Google Sheet here
        // Load the Google API Client and authenticate (same as before)


        $params = [
            'valueInputOption' => 'RAW',
        ];
        if(! $this->firstClear){
            // Clear rows starting from the specific row
            $clearRange = $this->sheetName . '!' . 'A' . $this->rowToStartFrom . ':Z'; // Adjust the columns as needed
            $clearRequest = new ClearValuesRequest();
            $service->spreadsheets_values->clear($this->spreadsheetId, $clearRange, $clearRequest);
            $this->firstClear=true;
        }


        do {
            $dataResult = $this->fetchData($this->startPage,  $this->batchSize);

            if (!empty($dataResult)) {
                if (count($dataResult) > 0) {

                    // Create the request to update the Google Sheet, starting from the specific row
                    $requestBody = new Google_Service_Sheets_ValueRange([
                        'values' => $dataResult,
                    ]);

                    if($this->inittotal==0){
                        $this->rowToStartFrom+= $this->totalRecords;
                    }else{
                        $this->rowToStartFrom+= $this->totalRecords;
                        $this->inittotal=0;
                    }

                    $range = $this->sheetName . '!' . 'A' . $this->rowToStartFrom ; // Start from the specific row


                    // Push the data to the specific row
                    $result = $service->spreadsheets_values->update($this->spreadsheetId, $range, $requestBody, $params);

                    if ($result->getUpdatedCells() > 0) {

                        $this->totalRecords = count($dataResult);
                        $this->startPage++;
                        $this->firstClear=true;
                        $this->info('Google Sheet updated successfully  rowToStartFrom:'.$this->rowToStartFrom .'  inittotal:'.$this->inittotal.'  totalRecords:'.$this->totalRecords .'');

                        $columnToFormatDate  = ['T', 'U' ];
                        $spreadsheet = $service->spreadsheets->get($this->spreadsheetId);
                        $sheetId = null;
                        foreach ($spreadsheet->getSheets() as $sheet) {
                            if ($sheet->properties->title === $this->sheetName) {
                                $sheetId = $sheet->properties->sheetId;
                                break;
                            }
                        }
                        $requests = [];

                        foreach ($columnToFormatDate as $columnToFormat) {
                            // Define the range to format for each column
                            $rangeToFormat = $this->sheetName . '!' . $columnToFormat . '2:' . $columnToFormat; // Start from row 2

                            $columnIndex = $this->columnLetterToIndex($columnToFormat);
                            $endColumnIndex = $this->columnLetterToIndex($columnToFormat) + 1;
                            // Create a request to format the specified column
                            $requests[] = [
                                'repeatCell' => [
                                    'range' => [
                                        'sheetId' => $sheetId, // Main sheet
                                        'startRowIndex' => 1, // Starting from row 2 (1-based index)
                                        'endRowIndex' => 5, // Adjust this as needed
                                        'startColumnIndex' => $columnIndex, // Format the entire column
                                        'endColumnIndex' => $endColumnIndex,
                                    ],
                                    'cell' => [
                                        'userEnteredFormat' => [
                                            'numberFormat' => [
                                                'type' => 'DATE',
                                                'pattern' => 'MM/DD/YYYY', // Customize the format as needed
                                            ],
                                        ],
                                    ],
                                    'fields' => 'userEnteredFormat.numberFormat',
                                ],
                            ];

                        }

                        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                            'requests' => $requests
                        ]);
                        $response = $service->spreadsheets->batchUpdate($this->spreadsheetId, $batchUpdateRequest);

                        if ($response) {
                            $this->info("Columns " . implode(', ', $columnToFormatDate) . " in sheet '$this->sheetName' formatted successfully.");
                        } else {
                            $this->info("Error formatting columns " . implode(', ', $columnToFormatDate) . " in sheet '$this->sheetName'.");
                        }
                    } else {
                        $this->info('Failed to update data in Google Sheets.');
                    }

                } else {
                    $this->info("Failed to update no data found to update.");
                    die();
                }
            }
        }while (count($dataResult) === $this->batchSize);
        $endTime = microtime(true);
        $executionTime = $endTime - $startTime;
        Log::channel("separated_file")->info("The Script".class_basename($this)."   Took : $executionTime");
    }

    /**
     * @param $startPage
     * @param $batchSize
     * @return mixed
     */
    public function fetchData($startPage, $batchSize){
        $apiUrl = "https://api.yajny.com/googledatatest". '?page=' . $startPage . '&limit=' . $batchSize;

        $curlOptions = [
            CURLOPT_SSL_VERIFYHOST => 0,
            CURLOPT_SSL_VERIFYPEER => false,
        ];
        $ch = curl_init();
        $url = $apiUrl; // Replace with your API endpoint

// Set cURL options
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt_array($ch, $curlOptions);

// Execute the cURL request
        $response = curl_exec($ch);
        curl_close($ch);
        $hh=json_decode($response);

        // Prepare the data for Google Sheets
        $values = [];

        foreach ($hh->data  as  $item) {
            $values[] = [
                $item->transaction_id ?? '' ,
                $item->click_id  ?? '' ,
                $item->user_id ?? '',
                $item->user_name ?? '',
                $item->is_merchant,
                $item->network ?? '',
                $item->store ?? '',
                $item->order_id ?? '',
                (double)$item->order_amount ,
                (double)$item->cashback_percent   ,
                (double)$item->cashback   ,
                (double)$item->local_order_amount   ,
                (double)$item->local_cashback ,
                $item->currency ?? '',
                $item->currency_rate,
                $item->country ?? '',
                $item->platform ?? '',
                $item->status ,
                $item->utm ?? '', /**/
                date("n/j/Y", strtotime($item->transaction_time))  ,
                date("n/j/Y", strtotime($item->created_at)),
                $item->orders
            ];
        }
        $this->info("fetching data from database completed successfully.");

     return  $values;
    }


    public function columnLetterToIndex($columnLetter) {
        $index = 0;
        $length = strlen($columnLetter);
        for ($i = 0; $i < $length; $i++) {
            $char = $columnLetter[$i];
            $index = $index * 26 + ord($char) - ord('A') + 1;
        }
        return $index - 1; // Convert to 0-based index
    }


    public function formatToPrice($price)
    {
        return   number_format($price, 2);
    }
}