1<?php
  2// --- DEBUG MODE ---
  3ini_set('display_errors', 0);
  4error_reporting(0);
  5
  6require(__DIR__ . '/../init.php'); 
  7use WHMCS\Database\Capsule;
  8
  9$webhookUrl = 'https://script.google.com/macros/s/AKfycbxY2NRBO-92mzAivW8C9w_MOo1T_dOG46zULIO06X7Gs_W-kfAoTaud5jinGeixhXiaLw/exec';
 10
 11try {
 12    $orders = Capsule::table('tblorders')
 13        ->join('tblcustomfieldsvalues', 'tblorders.userid', '=', 'tblcustomfieldsvalues.relid')
 14        ->join('tblclients', 'tblorders.userid', '=', 'tblclients.id')
 15        ->where('tblcustomfieldsvalues.fieldid', 34) 
 16        ->where(Capsule::raw('LENGTH(tblcustomfieldsvalues.value)'), '>', 10)
 17        ->where('tblorders.status', 'Active')
 18        ->where('tblorders.google_tracked', 0)
 19        ->select(
 20            'tblorders.id', 'tblorders.amount', 'tblorders.date', 
 21            'tblcustomfieldsvalues.value as click_id',
 22            'tblclients.email', 'tblclients.phonenumber'
 23        )
 24        ->get();
 25
 26    if ($orders->isEmpty()) die("No new orders.");
 27
 28    foreach ($orders as $order) {
 29        $idValue = trim($order->click_id);
 30        $gclid = $gbraid = $wbraid = "";
 31
 32        // ID Logic
 33        if (strpos($idValue, 'gbraid_') === 0 || (strlen($idValue) < 35 && strpos($idValue, '-') !== false)) {
 34            $gbraid = $idValue;
 35        } elseif (strpos($idValue, 'wbraid_') === 0) {
 36            $wbraid = $idValue;
 37        } else {
 38            $gclid = $idValue;
 39        }
 40
 41        // --- FIXED HASHING & TIME ---
 42        $rawEmail = strtolower(trim($order->email));
 43        $hashedEmail = !empty($rawEmail) ? hash('sha256', $rawEmail) : "";
 44
 45        $rawPhone = preg_replace('/[^\d]/', '', $order->phonenumber);
 46        $hashedPhone = !empty($rawPhone) ? hash('sha256', $rawPhone) : "";
 47
 48        // Ensure date format is exactly what Google wants
 49        $conversionTime = date('Y-m-d H:i:s', strtotime($order->date)) . ' -0600';
 50
 51        $sheetData = array(
 52            'gclid'    => $gclid,
 53            'gbraid'   => $gbraid,
 54            'wbraid'   => $wbraid,
 55            'name'     => 'GCLID - All records from GCLID', // Re-added this explicitly
 56            'time'     => $conversionTime,
 57            'value'    => $order->amount,
 58            'currency' => 'USD',
 59            'order_id' => $order->id,
 60            'email'    => $hashedEmail,
 61            'phone'    => $hashedPhone
 62        );
 63
 64        $ch = curl_init($webhookUrl);
 65        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($sheetData));
 66        curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
 67        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
 68        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); // Added to handle Apps Script redirects
 69        curl_exec($ch);
 70        curl_close($ch);
 71
 72        Capsule::table('tblorders')->where('id', $order->id)->update(['google_tracked' => 1]);
 73    }
 74} catch (\Exception $e) { echo $e->getMessage(); }