<?php 


require_once('../wp-load.php');

require_once('global-vars.php');



function write_to_csv_write_log($message) {

    // Get WordPress root directory

    $log_file = ABSPATH . 'user_wise_csv_write_log.txt';



    // Format message with timestamp

    $log_message = "[" . date("Y-m-d H:i:s") . "] " . $message . PHP_EOL;



    // Write to log file (append mode)

    file_put_contents($log_file, $log_message, FILE_APPEND | LOCK_EX);

}



function convert_time_to_sheet_duration($time_str) {

    list($hours, $minutes, $seconds) = explode(':', $time_str);

    $duration = ($hours / 24) + ($minutes / 1440) + ($seconds / 86400);

    return $duration;

}



function convert_time_to_sheet_duration_duration_row($time_str) {

    list($hours, $minutes, $seconds) = explode(':', $time_str);



    // Ensure two digits + milliseconds

    $formatted_time = sprintf("%02d:%02d:%02d.000", $hours, $minutes, $seconds);



    // Return as plain text (so Google Sheets won't auto-format it)

    return $formatted_time;

}



function calculateExtraHours($totalDuration) {
    // Convert HH:MM to minutes
    list($hours, $minutes) = explode(':', $totalDuration);
    $totalMinutes = ($hours * 60) + $minutes;
 
    // Minimum required time = 8 hours 30 minutes
    $baseMinutes = (8 * 60) + 30;
 
    // If below minimum, no extra
    if ($totalMinutes <= $baseMinutes) {
        return 0;
    }
 
    // Extra minutes worked
    $extraMinutes = $totalMinutes - $baseMinutes;
 
    // Calculate full hours
    $fullHours = floor($extraMinutes / 60);
 
    // Remaining minutes after full hours
    $remainingMinutes = $extraMinutes % 60;
 
    // Round up if remaining minutes >= 50
    if ($remainingMinutes >= 50) {
        $fullHours++;
    }
 
    return $fullHours;
}





include 'googleapiclient/vendor/autoload.php';

putenv('GOOGLE_APPLICATION_CREDENTIALS=/home/storetra12/public_html/hubstaff-operations/googleapiclient/new.json');



$client = new Google_Client();

$client->useApplicationDefaultCredentials();

$client->setScopes([Google_Service_Sheets::SPREADSHEETS]);

$service = new Google_Service_Sheets($client);

$spreadsheetId = '1c9OJrH3gfYdU0eZgWyIH71Ym3qrjn4MCMUM79tH4RCc';

$spreadsheet = $service->spreadsheets->get($spreadsheetId);

$sheets = $spreadsheet->getSheets();



// Your Google Sheet ID and range

$range = 'Total Hours!A2'; // Starting cell



global $wpdb;

$hubstaff_user_wise = $wpdb->prefix . 'hubstaff_user_wise_record';

// $today = date('2025-11-15');
$today = date('Y-m-d');

$query = "
    SELECT 
        member_name,
        record_date,
        SUM(TIME_TO_SEC(hours)) AS total_seconds
    FROM $hubstaff_user_wise
    WHERE record_date = '$today'
    GROUP BY member_name, record_date
    ORDER BY record_date ASC
";

// $query = "
//     SELECT 
//         member_name,
//         record_date,
//         SUM(TIME_TO_SEC(hours)) AS total_seconds
//     FROM $hubstaff_user_wise
//     WHERE csv_entry = 'y'
//     GROUP BY member_name, record_date
//     ORDER BY record_date ASC
// ";


$rows = $wpdb->get_results($query);

// Final simplified array
$final_array = [];

if (!empty($rows)) {

  foreach ($rows as $row) {

      // Total seconds
      $seconds = (int) $row->total_seconds;

      // Convert to HH:MM:SS
      $hours   = floor($seconds / 3600);
      $minutes = floor(($seconds % 3600) / 60);
      $sec     = $seconds % 60;

      $time_formatted = sprintf("%02d:%02d:%02d", $hours, $minutes, $sec);
      $extra_hour = calculateExtraHours($time_formatted);

      $final_array[] = [
          $row->record_date,
          $row->member_name,
          $time_formatted,
          $extra_hour
      ];
  }

}


$body = new Google_Service_Sheets_ValueRange([

  'values' => $final_array

]);



$params = [

  'valueInputOption' => 'RAW' // or 'USER_ENTERED'

];



// // Write to sheet

$result = $service->spreadsheets_values->append(

  $spreadsheetId,

  $range,

  $body,

  $params

);

// $updates = $result->getUpdates();

// if ($updates) {

//     $updatedCells = $updates->getUpdatedCells();

//     echo "Updated cells: " . $updatedCells;

// }



// echo $result->getUpdatedCells() . " cells updated.\n";