­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); /* ========================= Helpers ========================= */ function getIncomingPayload(): array { $raw = file_get_contents('php://input'); if ($raw !== false && trim($raw) !== '') { $j = json_decode($raw, true); if (is_array($j)) return $j; } return $_POST ?: []; } function apiLog(PDO $pdo, array $row): void { $sql = " INSERT INTO autozone_api_logs (log_type, leadid, mobile, endpoint, request_payload, http_code, response_body, is_success, error_message, opportunity_id, agent_id, agent_name, agent_mobile, branch_name, branch_address, branch_map_link, status_message) VALUES (:log_type, :leadid, :mobile, :endpoint, :request_payload, :http_code, :response_body, :is_success, :error_message, :opportunity_id, :agent_id, :agent_name, :agent_mobile, :branch_name, :branch_address, :branch_map_link, :status_message) "; $stmt = $pdo->prepare($sql); $stmt->execute([ ':log_type' => $row['log_type'] ?? null, ':leadid' => $row['leadid'] ?? null, ':mobile' => $row['mobile'] ?? null, ':endpoint' => $row['endpoint'] ?? null, ':request_payload' => isset($row['request_payload']) ? json_encode($row['request_payload'], JSON_UNESCAPED_UNICODE) : null, ':http_code' => $row['http_code'] ?? null, ':response_body' => $row['response_body'] ?? null, ':is_success' => (int)($row['is_success'] ?? 0), ':error_message' => $row['error_message'] ?? null, ':opportunity_id' => $row['opportunity_id'] ?? null, ':agent_id' => $row['agent_id'] ?? null, ':agent_name' => $row['agent_name'] ?? null, ':agent_mobile' => $row['agent_mobile'] ?? null, ':branch_name' => $row['branch_name'] ?? null, ':branch_address' => $row['branch_address'] ?? null, ':branch_map_link' => $row['branch_map_link'] ?? null, ':status_message' => $row['status_message'] ?? null, ]); } function extractLabel($input): string { // Guard: null / empty / non-scalar if ($input === null || $input === '' || is_array($input) || is_object($input)) { return ''; } // Force string & trim $clean = trim((string)$input); if ($clean === '') { return ''; } // Remove surrounding curly braces only $clean = trim($clean, "{}"); // Split on first colon only (safer) $parts = explode(':', $clean, 2); return isset($parts[0]) ? trim($parts[0]) : ''; } function getLookupValue($lookup_value,$type) { // Database connection parameters $servername = "localhost"; $username = "balubaid_honda_native_forms"; $password = "Vision@2050"; $dbname = "balubaid_honda_native_forms_leads"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Prepare SQL query to prevent SQL injection $stmt = $conn->prepare("SELECT result_value FROM autozone_lookup_values WHERE lookup_value = ? and type = ?"); $stmt->bind_param("ss", $lookup_value,$type); // "s" means string type // Execute the query $stmt->execute(); $stmt->bind_result($result_value); // Fetch the result if ($stmt->fetch()) { $stmt->close(); $conn->close(); return $result_value; } else { $stmt->close(); $conn->close(); return null; // Return null if no match is found } } function convert2english($string) { $newNumbers = range(0, 9); $arabic = ['٠','١','٢','٣','٤','٥','٦','٧','٨','٩']; $persian = ['۰','۱','۲','۳','۴','۵','۶','۷','۸','۹']; $string = str_replace($arabic, $newNumbers, (string)$string); return str_replace($persian, $newNumbers, $string); } function normalizeSaudiMobile($input) { $number = preg_replace('/\D+/', '', (string)$input); if (strpos($number, '966') === 0) $number = substr($number, 3); if (strpos($number, '0') === 0) $number = substr($number, 1); return preg_match('/^5\d{8}$/', $number) ? $number : null; } function normalizeMobile(?string $mobile): string { return preg_replace('/[^\d+]/', '', trim((string)$mobile)) ?? ''; } function metaTimeToRiyadh(?string $value): ?string { if (!$value) return null; $dt = new DateTime(trim($value)); $dt->setTimezone(new DateTimeZone('Asia/Riyadh')); return $dt->format('Y-m-d H:i:s'); } function analyzeStringAmount(?string $input): array { if ($input === null || trim($input) === '') return ['has_amount'=>'No','amount'=>0]; $normalized = convert2english(trim($input)); preg_match_all('/\d+(?:\.\d+)?/', $normalized, $matches); $amount = 0; foreach (($matches[0] ?? []) as $num) $amount = max($amount, (float)$num); return ['has_amount'=>($amount>0?'Yes':'No'),'amount'=>($amount>0?$amount:0)]; } /* ---- scoring ---- */ function computeLeadScore(array $d): array { $score = 0; $pref = trim((string)($d['preftime'] ?? '')); if (mb_strpos($pref, 'فورًا') !== false || stripos($pref, 'immediately') !== false) $score += 30; elseif ($pref !== '') $score += 15; $salary = trim((string)($d['salary'] ?? '')); if ( $salary === 'Between 8,000 to 10,000' || $salary === 'More than 10,000' ) { $score += 25; } elseif ($salary !== '') { $score += 5; } $hasOb = ($d['isobligation'] ?? 'No'); $amt = (float)($d['obligation_amount'] ?? 0); if ($hasOb === 'Yes') { if ($amt >= 5000) $score -= 25; elseif ($amt >= 2000) $score -= 15; else $score -= 5; } else { $score += 10; } $bank = trim((string)($d['bank'] ?? '')); if ($bank !== '') { $preferredBanks = ['National Commercial Bank','AlRajhi Bank','Riyad Bank','Arab National Bank','SABB Bank','Saudi Fransi Bank']; $hit = false; foreach ($preferredBanks as $b) { if (mb_stripos($bank, $b) !== false) { $hit = true; break; } } $score += $hit ? 15 : 5; } $score = max(0, min(100, $score)); return ['score'=>$score, 'temp'=>($score >= 60 ? 'HOT' : 'COLD')]; } /* ---------------- Agent Lookup API ---------------- INPUT: branch name OUTPUT needed: agent_name, agent_mobile, agent_party_id, branch_address, branch_name, branch_lat, branch_lng, branch_map_url --------------------------------------------------- */ function lookupAgentByBranch(string $branchName): array { // ✅ Replace with your real endpoint $endpoint = 'https://autozoneksa.com/oraclecxleads/assignapi/'; $payload = ['branch_code' => $branchName]; $ch = curl_init($endpoint); curl_setopt_array($ch, [ CURLOPT_POST => true, CURLOPT_POSTFIELDS => json_encode($payload, JSON_UNESCAPED_UNICODE), CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => 20, CURLOPT_CONNECTTIMEOUT => 10, CURLOPT_HTTPHEADER => ['Content-Type: application/json','Accept: application/json'], ]); $resp = curl_exec($ch); $http = curl_getinfo($ch, CURLINFO_HTTP_CODE); $err = curl_error($ch); curl_close($ch); $json = (is_string($resp) && $resp !== '') ? json_decode($resp, true) : null; $success = ($err === '' && $http >= 200 && $http < 300 && is_array($json)); // ✅ Adjust mapping once you paste real response return [ 'success' => $success, 'endpoint' => $endpoint, 'payload' => $payload, 'http_code' => $http, 'response_raw' => $resp, 'error' => $err ?: null, 'agent_name' => $json['branch_details']['sales_consultant_name_en'] ?? ($json['agent']['name'] ?? null), 'agent_name_ar' => $json['branch_details']['sales_consultant_name_ar'] ?? ($json['agent']['name'] ?? null), 'agent_mobile' => $json['branch_details']['sales_consultant_number'] ?? ($json['agent']['mobile'] ?? null), 'agent_party_id' => $json['branch_details']['sales_consulant_fusion_id'] ?? ($json['agent']['party_id'] ?? null), 'branch_name' => $json['branch_details']['long_name_en'] ?? ($json['branch']['name'] ?? null), 'branch_name_ar' => $json['branch_details']['long_name_ar'] ?? ($json['branch']['name'] ?? null), 'branch_address' => $json['branch_details']['address_en'] ?? ($json['branch']['address'] ?? null), 'branch_address_ar' => $json['branch_details']['address_ar'] ?? ($json['branch']['address'] ?? null), 'branch_lat' => $json['branch_details']['lat'] ?? ($json['branch']['lat'] ?? null), 'branch_lng' => $json['branch_details']['long'] ?? ($json['branch']['lng'] ?? null), 'branch_map_url' => $json['branch_details']['branch_url'] ?? ($json['branch']['map_url'] ?? null), ]; } $_POST['mobile'] = "0".normalizeSaudiMobile(convert2english($_POST['mobile'])); $_POST['paymethod'] = getLookupValue(extractLabel($_POST['paymethod']),'paymethod'); $_POST['purchasetime'] = getLookupValue(extractLabel($_POST['purchasetime']),'purtime'); $_POST['gender'] = getLookupValue(extractLabel($_POST['gender']),'gender'); $_POST['salary1'] = getLookupValue(extractLabel($_POST['salary']),'salary1'); $_POST['salary'] = getLookupValue(extractLabel($_POST['salary']),'salary'); $_POST['isobligation'] = analyzeStringAmount($_POST['obligation'])['has_amount']; $_POST['obligation_amount'] = analyzeStringAmount($_POST['obligation'])['amount']; $_POST['model'] = extractLabel($_POST['adname']); $_POST['preftime'] = getLookupValue(extractLabel($_POST['preftime']),'preftime'); $_POST['bank'] = getLookupValue(extractLabel($_POST['bank']),'bank'); $_POST['channel'] = getLookupValue(extractLabel($_POST['channel']),'channel'); $_POST['city'] = getLookupValue(extractLabel($_POST['branch']),'city'); $_POST['branchcode'] = getLookupValue(extractLabel($_POST['branch']),'branchcode'); $_POST['branch'] = getLookupValue(extractLabel($_POST['branch']),'branch'); //$_POST['fullname'] = $_POST['fname']." ".$_POST['lname']; //print_r($_POST); $data = $_POST; /* ========================= MAIN ========================= */ try { $data = getIncomingPayload(); // normalize mobile to 05xxxxxxxx if (isset($data['mobile'])) { $norm = normalizeSaudiMobile(convert2english((string)$data['mobile'])); if ($norm) $data['mobile'] = "0".$norm; } // required $mobile = normalizeMobile($data['mobile'] ?? ''); $leadid = (string)($data['leadid'] ?? ''); $createdAt = metaTimeToRiyadh($data['createdat'] ?? null); if ($mobile === '' || $leadid === '') { http_response_code(400); echo json_encode(['status'=>'error','message'=>'Missing required fields: mobile or leadid'], JSON_UNESCAPED_UNICODE); exit; } // obligations parse (from string) $ob = analyzeStringAmount($data['obligation'] ?? null); $data['isobligation'] = $ob['has_amount']; $data['obligation_amount'] = $ob['amount']; // full name $data['fullname'] = trim(($data['fname'] ?? '').' '.($data['lname'] ?? '')); // score $sc = computeLeadScore([ 'preftime' => $data['preftime'] ?? '', 'salary' => $data['salary1'] ?? '', 'isobligation' => $data['isobligation'] ?? 'No', 'obligation_amount' => $data['obligation_amount'] ?? 0, 'bank' => $data['bank'] ?? '', ]); $data['lead_score'] = $sc['score']; $data['lead_temp'] = $sc['temp']; /* --------- local dedupe + store --------- */ $pdo->beginTransaction(); $dup = $pdo->prepare(" SELECT leadid FROM autozone_leads WHERE mobile = :m AND COALESCE(createdat, created_at) >= (NOW() - INTERVAL 15 DAY) ORDER BY COALESCE(createdat, created_at) DESC LIMIT 1 "); $dup->execute([':m'=>$mobile]); if ($dup->fetch()) { $pdo->rollBack(); echo json_encode(['status'=>'duplicate','message'=>'Mobile duplicate within 15 days'], JSON_UNESCAPED_UNICODE); exit; } $ins = $pdo->prepare(" INSERT INTO autozone_leads ( leadid, adid, adname, adsquadename, cmpname, strategy, channel, formname, fname, email, mobile, branch, city, paymethod, purchasetime, preftime, model, gender, obligations, isobligations, obligationsamount, salary, salary1, bank, lead_score, lead_temp, createdat ) VALUES ( :leadid, :adid, :adname, :adsquadename, :cmpname, :strategy, :channel, :formname, :fname, :email, :mobile, :branch, :city, :paymethod, :purchasetime, :preftime, :model, :gender, :obligations, :isobligations, :obligationsamount, :salary, :salary1, :bank, :lead_score, :lead_temp, :createdat ) "); $ins->execute([ ':leadid' => $leadid, ':adid' => $data['adid'] ?? null, ':adname' => $data['adname'] ?? null, ':adsquadename' => $data['adsquadename'] ?? null, ':cmpname' => $data['cmpname'] ?? null, ':strategy' => $data['strategy'] ?? null, ':channel' => $data['channel'] ?? null, ':formname' => $data['formname'] ?? null, ':fname' => $data['fullname'] ?? null, ':email' => $data['email'] ?? null, ':mobile' => $mobile, ':branch' => $data['branch'] ?? null, ':city' => $data['city'] ?? null, ':paymethod' => $data['paymethod'] ?? null, ':purchasetime' => $data['purchasetime'] ?? null, ':preftime' => $data['preftime'] ?? null, ':model' => $data['model'] ?? ($data['adname'] ?? null), ':gender' => $data['gender'] ?? null, ':obligations' => $data['obligation'] ?? null, ':isobligations' => $data['isobligation'] ?? null, ':obligationsamount' => $data['obligation_amount'] ?? null, ':salary' => $data['salary'] ?? null, ':salary1' => $data['salary1'] ?? null, ':bank' => $data['bank'] ?? null, ':lead_score' => $data['lead_score'], ':lead_temp' => $data['lead_temp'], ':createdat' => $createdAt ]); $pdo->commit(); /* --------- Build ORACLE wrapper payload --------- Matches your create_oracle_lead() and create_oracle_lead_n_convert() expectations */ $oracleData = [ 'mobile' => $mobile, // MUST be 05xxxxxxxx per your functions 'full_name' => $data['fullname'] ?: 'NA', 'bank' => (string)($data['bank'] ?? ''), // your oracle code uses this as SalaryType_c 'salary_group' => (string)($data['salary'] ?? ''), 'lead_campaign' => (string)($data['cmpname'] ?? ''), 'city' => (string)($data['city'] ?? ''), 'business_unit' => (string)($data['business_unit'] ?? 'Autozone'), 'voi' => (string)($data['model'] ?? $data['adname'] ?? ''), 'lead_channel' => (string)($data['channel'] ?? ''), 'branch' => (string)($data['branch'] ?? ''), 'request_type' => (string)($data['paymethod'] ?? ''), 'plan_to_buy' => (string)($data['purchasetime'] ?? '') ]; /* --------- COLD vs HOT --------- */ $agentInfo = null; $oracleResult = null; if ($data['lead_temp'] === 'HOT') { // 1) Lookup agent by branch $agentInfo = lookupAgentByBranch((string)($data['branchcode'] ?? '')); apiLog($pdo, [ 'log_type' => 'opportunity', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => $agentInfo['endpoint'], 'request_payload' => $agentInfo['payload'], 'http_code' => $agentInfo['http_code'], 'response_body' => $agentInfo['response_raw'], 'is_success' => (int)$agentInfo['success'], 'error_message' => $agentInfo['error'], 'agent_id' => $agentInfo['agent_party_id'], 'agent_name' => $agentInfo['agent_name'], 'agent_mobile' => $agentInfo['agent_mobile'], 'branch_name' => $agentInfo['branch_name'], 'branch_address' => $agentInfo['branch_address'], 'branch_map_link' => $agentInfo['branch_map_url'], 'status_message' => $agentInfo['success'] ? 'Agent lookup success' : 'Agent lookup failed', ]); // 2) If agent lookup ok -> Create lead + convert to opportunity (your wrapper) if ($agentInfo['success'] && !empty($agentInfo['agent_party_id'])) { $oracleResult = create_oracle_lead_n_convert($oracleData, (string)$agentInfo['agent_party_id']); apiLog($pdo, [ 'log_type' => 'opportunity', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'create_oracle_lead_n_convert', 'request_payload' => ['oracleData'=>$oracleData,'agent_party_id'=>$agentInfo['agent_party_id']], 'http_code' => 200, 'response_body' => json_encode($oracleResult, JSON_UNESCAPED_UNICODE), 'is_success' => 1, 'opportunity_id' => is_array($oracleResult) ? ($oracleResult['Convert_Lead_Log'] ?? null) : null, 'agent_id' => $agentInfo['agent_party_id'], 'agent_name' => $agentInfo['agent_name'], 'agent_mobile' => $agentInfo['agent_mobile'], 'branch_name' => $agentInfo['branch_name'], 'branch_address' => $agentInfo['branch_address'], 'branch_map_link' => $agentInfo['branch_map_url'], 'status_message' => 'Oracle lead + opportunity created' ]); } else { // HOT but no agent -> fallback: create lead only (still safe) $oracleResult = create_oracle_lead($oracleData, null); apiLog($pdo, [ 'log_type' => 'lead', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'create_oracle_lead (fallback)', 'request_payload' => ['oracleData'=>$oracleData], 'http_code' => 200, 'response_body' => json_encode($oracleResult, JSON_UNESCAPED_UNICODE), 'is_success' => 1, 'status_message' => 'HOT fallback: lead created only (agent lookup failed)' ]); } } else { // COLD -> create lead only $oracleResult = create_oracle_lead($oracleData, null); apiLog($pdo, [ 'log_type' => 'lead', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'create_oracle_lead', 'request_payload' => ['oracleData'=>$oracleData], 'http_code' => 200, 'response_body' => json_encode($oracleResult, JSON_UNESCAPED_UNICODE), 'is_success' => 1, 'status_message' => 'Oracle lead created (cold lead)' ]); } echo json_encode([ 'status' => 'ok', 'local_leadid' => $leadid, 'mobile' => $mobile, 'lead_score' => $data['lead_score'], 'lead_temp' => $data['lead_temp'], 'oracle_result' => $oracleResult, 'agent_info' => $agentInfo ], JSON_UNESCAPED_UNICODE); } catch (Throwable $e) { if ($pdo->inTransaction()) $pdo->rollBack(); http_response_code(500); echo json_encode(['status'=>'error','message'=>'Server error','detail'=>$e->getMessage()], JSON_UNESCAPED_UNICODE); }