­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ bool, 'http_code'=>int, 'error'=>string|null, 'raw_response'=>string|null, ...] */ require_once 'oracle_functions.php'; /* ------------------ DB CONFIG ------------------ */ $dbHost = "localhost"; $dbName = "balubaid_honda_native_forms_leads"; $dbUser = "balubaid_honda_native_forms"; $dbPass = "Vision@2050"; /* ------------------ External APIs ------------------ */ define('ASSIGN_API_ENDPOINT', 'https://autozoneksa.com/oraclecxleads/assignapi/'); /* SMS Provider (Infinito) */ define('SMS_API_URL', 'https://api.goinfinito.me/unified/v2/send'); define('SMS_BASIC_USER', 'balubaidy8r60j1czfbbh6td'); define('SMS_BASIC_PASS', 'lpfl8lzwmgsntg9uddaaoe74lz2yjsmg'); define('SMS_SENDER', 'AUTOZONE'); // from define('SMS_CODING', 2); // 2 = UCS2 for Arabic /* WhatsApp (placeholder) */ define('WHATSAPP_ENDPOINT', 'https://YOUR-WHATSAPP-PROVIDER/send'); // replace later $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ] ); /* ========================================================= Helpers ========================================================= */ function oracleWasConverted(array $ids, $oracleResult): bool { // primary: Convert_Lead_Log field if (!empty($ids['convert_log']) && strcasecmp(trim((string)$ids['convert_log']), 'Successful') === 0) { return true; } // fallback: direct key check (if ids extraction changes) $v = arr_get($oracleResult, ['Convert_Lead_Log'], null); return is_string($v) && strcasecmp(trim($v), 'Successful') === 0; } 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 arr_get($arr, array $keys, $default = null) { $cur = $arr; foreach ($keys as $k) { if (!is_array($cur) || !array_key_exists($k, $cur)) return $default; $cur = $cur[$k]; } return $cur; } function isTruthy($v): bool { if (is_bool($v)) return $v; $s = strtolower(trim((string)$v)); return in_array($s, ['1','true','yes','y','success','ok'], true); } 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 { if ($input === null || $input === '' || is_array($input) || is_object($input)) return ''; $clean = trim((string)$input); if ($clean === '') return ''; $clean = trim($clean, "{}"); $parts = explode(':', $clean, 2); return isset($parts[0]) ? trim($parts[0]) : ''; } function convert2english($string): string { $newNumbers = range(0, 9); $arabic = ['٠','١','٢','٣','٤','٥','٦','٧','٨','٩']; $persian = ['۰','۱','۲','۳','۴','۵','۶','۷','۸','۹']; $string = str_replace($arabic, $newNumbers, (string)$string); return str_replace($persian, $newNumbers, $string); } function normalizeSaudiMobile($input): ?string { $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; try { $dt = new DateTime(trim($value)); $dt->setTimezone(new DateTimeZone('Asia/Riyadh')); return $dt->format('Y-m-d H:i:s'); } catch (Throwable $e) { return null; } } 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)]; } function computeLeadScore(array $d): array { $score = 0; $pref = trim((string)($d['preftime'] ?? '')); if (mb_strpos($pref, 'فورًا') !== false || stripos($pref, 'immediately') !== false || stripos($pref, 'Immediately') !== false) $score += 30; elseif ($pref !== '') $score += 15; $salary = trim((string)($d['salary'] ?? '')); if ($salary === '8,000 & Above' || $salary === '15,000 & Above' || $salary === '20,000 & Above' || $salary === '10,000 & Above' || $salary === 'Cash') $score += 25; elseif ($salary !== '') $score += 5; $hasOb = ($d['isobligation'] ?? 'No'); $amt = (float)($d['obligation_amount'] ?? 0); if ($hasOb === 'Yes') { if ($amt >= 5000) $score -= 15; elseif ($amt >= 2000) $score -= 10; else $score -= 2; } 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; } $gender = trim((string)($d['gender'] ?? '')); if (mb_strpos($gender, 'Female - أنثى') !== false || stripos($gender,'female') !== false || stripos($gender,'أنثى') !== false) $score += 10; elseif ($gender !== '') $score += 2; $score = max(0, min(100, $score)); return ['score'=>$score, 'temp'=>($score >= 50 ? 'HOT' : 'COLD')]; } /** * Uses your DB table: autozone_lookup_values */ function getLookupValue(PDO $pdo, ?string $lookup_value, string $type): ?string { $lookup_value = trim((string)$lookup_value); if ($lookup_value === '') return null; static $cache = []; $key = $type . '|' . $lookup_value; if (array_key_exists($key, $cache)) return $cache[$key]; $stmt = $pdo->prepare(" SELECT result_value FROM autozone_lookup_values WHERE lookup_value = :v AND type = :t LIMIT 1 "); $stmt->execute([':v' => $lookup_value, ':t' => $type]); $row = $stmt->fetch(PDO::FETCH_ASSOC); $cache[$key] = $row['result_value'] ?? null; return $cache[$key]; } /* ---------------- Agent Lookup API ---------------- */ function lookupAgentByBranch(string $branchCode): array { $endpoint = ASSIGN_API_ENDPOINT; $payload = ['branch_code' => $branchCode]; $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)); 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'] ?? null, 'branch_address' => $json['branch_details']['address_en'] ?? ($json['branch']['address'] ?? null), 'branch_address_ar' => $json['branch_details']['address_ar'] ?? null, 'branch_map_url' => $json['branch_details']['branch_url'] ?? ($json['branch']['map_url'] ?? null), ]; } /* ---------------- Duplicate storage ---------------- */ function storeDuplicateLead(PDO $pdo, string $leadid, string $mobile, string $reason, array $payload): void { $stmt = $pdo->prepare(" INSERT INTO autozone_duplicate_leads (leadid, mobile, reason, payload, created_at) VALUES (:leadid, :mobile, :reason, :payload, NOW()) "); $stmt->execute([ ':leadid' => ($leadid !== '' ? $leadid : null), ':mobile' => $mobile, ':reason' => $reason, ':payload' => json_encode($payload, JSON_UNESCAPED_UNICODE), ]); } /* ---------------- Update autozone_leads with new fields ---------------- */ function updateLead(PDO $pdo, string $leadid, array $fields): void { $allowed = [ 'branch_code', 'agent_party_id','agent_name','agent_mobile', 'branch_name_en','branch_name_ar','branch_address_en','branch_address_ar','branch_map_url', 'lead_type','oracle_sync_status','oracle_lead_id','oracle_opportunity_id','oracle_person_party_id', 'oracle_status_message','assigned_at','updated_at' ]; $set = []; $params = [':leadid' => $leadid]; foreach ($fields as $k => $v) { if (!in_array($k, $allowed, true)) continue; $set[] = "`$k` = :$k"; $params[":$k"] = $v; } $set[] = "`updated_at` = NOW()"; $sql = "UPDATE autozone_leads SET " . implode(", ", $set) . " WHERE leadid = :leadid LIMIT 1"; $stmt = $pdo->prepare($sql); $stmt->execute($params); } /* ========================================================= NEW: Robust Oracle ID detection (recursive) ========================================================= */ function findAnyId($value, array $possibleKeys): ?string { if (!is_array($value)) return null; $stack = [$value]; while (!empty($stack)) { $cur = array_pop($stack); foreach ($cur as $k => $v) { if (is_string($k)) { foreach ($possibleKeys as $pk) { if (strcasecmp($k, $pk) === 0) { if (is_scalar($v) && trim((string)$v) !== '') { return trim((string)$v); } } } } if (is_array($v)) { $stack[] = $v; } } } return null; } function oracleExtractIds($oracleResult): array { if (!is_array($oracleResult)) { return ['lead_id'=>null,'opp_id'=>null,'person_party_id'=>null,'convert_log'=>null]; } // LeadNumberId is what your API returns (not LeadId) $leadId = findAnyId($oracleResult, [ 'LeadNumberId','LeadNumberID', 'LeadId','lead_id','leadId','id','leadID' ]); // You don't get OpportunityId from this API, but keep it for future compatibility $oppId = findAnyId($oracleResult, [ 'OpportunityId','opportunity_id','opportunityId','OpportunityID' ]); $ppId = findAnyId($oracleResult, [ 'PersonPartyId','person_party_id','personPartyId','partyId','PartyId' ]); $convertLog = findAnyId($oracleResult, ['Convert_Lead_Log','convert_lead_log','ConvertLeadLog']); return ['lead_id'=>$leadId, 'opp_id'=>$oppId, 'person_party_id'=>$ppId, 'convert_log'=>$convertLog]; } function oracleIsOk($oracleResult, array $ids): bool { $ok = isTruthy(arr_get($oracleResult, ['success'], null)) || isTruthy(arr_get($oracleResult, ['Success'], null)) || isTruthy(arr_get($oracleResult, ['status'], null)); // sometimes "status":"success" // If we have any strong IDs, treat as success (covers missing/false success flag) if (!$ok && (!empty($ids['lead_id']) || !empty($ids['opp_id']) || !empty($ids['person_party_id']))) { $ok = true; } // If error explicitly exists and NO IDs, treat as failure $err = arr_get($oracleResult, ['error'], null) ?? arr_get($oracleResult, ['Error'], null) ?? null; if ($err && empty($ids['lead_id']) && empty($ids['opp_id']) && empty($ids['person_party_id'])) { return false; } return $ok; } function oracleHttpCode($oracleResult): int { $h = arr_get($oracleResult, ['http_code'], null) ?? arr_get($oracleResult, ['httpCode'], null) ?? arr_get($oracleResult, ['http'], null); return (int)($h ?? 200); } function oracleErrorMsg($oracleResult): ?string { $e = arr_get($oracleResult, ['error'], null) ?? arr_get($oracleResult, ['error_message'], null) ?? arr_get($oracleResult, ['message'], null) ?? arr_get($oracleResult, ['ErrorMessage'], null); $e = is_string($e) ? trim($e) : ''; return $e !== '' ? $e : null; } /* ========================================================= SMS (Infinito) ========================================================= */ function generateRandomBasedOnTime(): string { $date = date('YmdHis'); $microtime = microtime(); [$microseconds, $seconds] = explode(' ', $microtime); $microseconds = str_replace('.', '', $microseconds); $combined = $date . $seconds . $microseconds; $random = abs(crc32($combined) + mt_rand(0, 9999)); return (string)$random; } function buildSmsPayloadInfinito(string $toE164, string $text, string $from = SMS_SENDER): array { $rand = generateRandomBasedOnTime(); return [ 'apiver' => '1.0', 'sms' => [ 'ver' => '2.0', 'dlr' => ['url' => ''], 'messages' => [[ 'udh' => '0', 'coding' => SMS_CODING, 'text' => $text, 'property' => 0, 'id' => $rand, 'addresses' => [[ 'from' => $from, // IMPORTANT: Keep real receiver: 'to' => $toE164, 'seq' => $rand, 'tag' => $from ]] ]] ] ]; } function normalizeToE164Saudi(string $mobile05): string { $m = preg_replace('/\D+/', '', $mobile05); $m = ltrim($m, '0'); // 5xxxxxxxx return '966' . $m; } function sendSmsInfinito(string $mobile05, string $message): array { $to = normalizeToE164Saudi($mobile05); $payload = buildSmsPayloadInfinito($to, $message, SMS_SENDER); $ch = curl_init(); $headers = ["Content-Type:application/json", "Accept:application/json"]; curl_setopt($ch, CURLOPT_URL, SMS_API_URL); curl_setopt($ch, CURLOPT_HTTPHEADER, $headers); curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_BASIC); curl_setopt($ch, CURLOPT_USERPWD, SMS_BASIC_USER . ":" . SMS_BASIC_PASS); curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); curl_setopt($ch, CURLOPT_MAXREDIRS, 2); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($payload, JSON_UNESCAPED_UNICODE)); $response = curl_exec($ch); $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE); $err = curl_error($ch); curl_close($ch); $success = ($err === '' && $httpCode >= 200 && $httpCode < 300); $providerId = null; $j = (is_string($response) && $response !== '') ? json_decode($response, true) : null; if (is_array($j)) { $providerId = $j['message_id'] ?? $j['messageId'] ?? $j['id'] ?? arr_get($j, ['sms','messages',0,'id']) ?? null; } return [ 'success' => $success, 'http' => $httpCode, 'resp' => (string)$response, 'err' => $err ?: null, 'provider_id' => $providerId ]; } function sendWhatsAppPlaceholder(string $mobile05, string $message): array { return ['success'=>false,'http'=>0,'resp'=>'WhatsApp not configured','err'=>null,'provider_id'=>null]; } function buildAssignmentMessage(array $agentInfo): string { $agent = trim((string)($agentInfo['agent_name_ar'] ?: $agentInfo['agent_name'] ?: '')); $agentmbl = trim((string)($agentInfo['agent_mobile'] ?: '')); $branch = trim((string)($agentInfo['branch_name_ar'] ?: $agentInfo['branch_name'] ?: '')); $addr = trim((string)($agentInfo['branch_address_ar'] ?: $agentInfo['branch_address'] ?: '')); $map = trim((string)($agentInfo['branch_map_url'] ?: '')); $lines = []; $lines[] = "تم استلام طلبك وتم تحويله للمستشار المختص ✅"; if ($agent !== '') $lines[] = "المستشار: {$agent}"; if ($agentmbl !== '') $lines[] = "رقم جوال مستشار: {$agentmbl}"; if ($branch !== '') $lines[] = "الفرع: {$branch}"; if ($addr !== '') $lines[] = "العنوان: {$addr}"; if ($map !== '') $lines[] = "الموقع: {$map}"; $lines[] = "للاستفسارات: 8007606000"; return implode("\n", $lines); } /* ---------------- Map & normalize incoming fields ---------------- */ function mapAndNormalize(PDO $pdo, array $data): array { $rawMobile = (string)($data['mobile'] ?? ''); $norm = normalizeSaudiMobile(convert2english($rawMobile)); $data['mobile'] = $norm ? ('0' . $norm) : ''; $data['paymethod'] = getLookupValue($pdo, extractLabel($data['paymethod'] ?? null), 'paymethod'); $data['purchasetime'] = getLookupValue($pdo, extractLabel($data['purchasetime'] ?? null), 'purtime'); $data['gender'] = getLookupValue($pdo, extractLabel($data['gender'] ?? null), 'gender'); $salaryLabel = extractLabel($data['salary'] ?? null); $data['salary1'] = getLookupValue($pdo, $salaryLabel, 'salary1'); $data['salary'] = getLookupValue($pdo, $salaryLabel, 'salary'); $data['preftime'] = getLookupValue($pdo, extractLabel($data['preftime'] ?? null), 'preftime'); $data['bank'] = getLookupValue($pdo, extractLabel($data['bank'] ?? null), 'bank'); $data['channel'] = getLookupValue($pdo, extractLabel($data['channel'] ?? null), 'channel'); $branchLabel = extractLabel($data['branch'] ?? null); $data['city'] = getLookupValue($pdo, $branchLabel, 'city'); $data['branchcode'] = getLookupValue($pdo, $branchLabel, 'branchcode'); $data['branch'] = getLookupValue($pdo, $branchLabel, 'branch'); $data['obligation'] = getLookupValue($pdo, $data['obligation'] ?? null, 'obligation'); $data['model'] = extractLabel($data['adname'] ?? null); $fname = trim((string)($data['fname'] ?? '')); $lname = trim((string)($data['lname'] ?? '')); $data['fullname'] = trim($fname . ' ' . $lname); if ($data['fullname'] === '') { $data['fullname'] = trim((string)($data['full_name'] ?? '')); } $ob = analyzeStringAmount($data['obligation'] ?? null); $data['isobligation'] = $ob['has_amount']; $data['obligation_amount'] = $ob['amount']; $data['createdat_riyadh'] = metaTimeToRiyadh($data['createdat'] ?? null); $sc = computeLeadScore([ 'preftime' => $data['preftime'] ?? '', 'salary' => $data['salary1'] ?? '', 'gender' => $data['gender'] ?? '', 'isobligation' => $data['isobligation'] ?? 'No', 'obligation_amount' => $data['obligation_amount'] ?? 0, 'bank' => $data['bank'] ?? '', ]); $data['lead_score'] = $sc['score']; $data['lead_temp'] = $sc['temp']; return $data; } /* ========================================================= MAIN ========================================================= */ try { $data = getIncomingPayload(); $data = mapAndNormalize($pdo, $data); $mobile = normalizeMobile($data['mobile'] ?? ''); $leadid = (string)($data['leadid'] ?? ''); $createdAt = $data['createdat_riyadh'] ?: date('Y-m-d H:i:s'); if ($mobile === '' || $leadid === '') { http_response_code(400); echo json_encode(['status'=>'error','message'=>'Missing required fields: mobile or leadid'], JSON_UNESCAPED_UNICODE); exit; } /* ---- Dedupe check (15 days) ---- */ $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()) { storeDuplicateLead($pdo, $leadid, $mobile, 'Mobile duplicate within 15 days', $data); apiLog($pdo, [ 'log_type' => 'duplicate', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'local_dedupe', 'request_payload' => $data, 'http_code' => 200, 'response_body' => 'Duplicate stored', 'is_success' => 1, 'status_message' => 'Duplicate stored (not inserted into autozone_leads, not pushed to Oracle)' ]); $pdo->commit(); echo json_encode([ 'status' => 'duplicate', 'message' => 'Duplicate stored', 'leadid' => $leadid, 'mobile' => $mobile ], JSON_UNESCAPED_UNICODE); exit; } /* ---- Insert into autozone_leads (PENDING sync) ---- */ $ins = $pdo->prepare(" INSERT INTO autozone_leads ( leadid, adid, adname, adsquadename, cmpname, strategy, channel, formname, fname, email, mobile, branch, branch_code, city, paymethod, purchasetime, preftime, model, gender, obligations, isobligations, obligationsamount, salary, salary1, bank, lead_score, lead_temp, createdat, oracle_sync_status, updated_at ) VALUES ( :leadid, :adid, :adname, :adsquadename, :cmpname, :strategy, :channel, :formname, :fname, :email, :mobile, :branch, :branch_code, :city, :paymethod, :purchasetime, :preftime, :model, :gender, :obligations, :isobligations, :obligationsamount, :salary, :salary1, :bank, :lead_score, :lead_temp, :createdat, 'PENDING', NOW() ) "); $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, ':branch_code' => $data['branchcode'] ?? 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'] ?? 0, ':lead_temp' => $data['lead_temp'] ?? 'COLD', ':createdat' => $createdAt ]); $pdo->commit(); /* ---- Build oracle payload ---- */ $oracleData = [ 'mobile' => $mobile, 'full_name' => ($data['fullname'] ?: 'NA'), 'bank' => (string)($data['bank'] ?? ''), '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'] ?? '') ]; $agentInfo = null; $oracleResult = null; $smsResult = null; $waResult = null; /* ---- HOT vs COLD ---- */ if (($data['lead_temp'] ?? 'COLD') === 'HOT') { // 1) Lookup agent $agentInfo = lookupAgentByBranch((string)($data['branchcode'] ?? '')); apiLog($pdo, [ 'log_type' => 'agent_lookup', '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'] ?? 0), 'error_message' => $agentInfo['error'], 'agent_id' => $agentInfo['agent_party_id'] ?? null, 'agent_name' => $agentInfo['agent_name'] ?? null, 'agent_mobile' => $agentInfo['agent_mobile'] ?? null, 'branch_name' => $agentInfo['branch_name'] ?? null, 'branch_address' => $agentInfo['branch_address'] ?? null, 'branch_map_link' => $agentInfo['branch_map_url'] ?? null, 'status_message' => ($agentInfo['success'] ?? false) ? 'Agent lookup success' : 'Agent lookup failed', ]); updateLead($pdo, $leadid, [ 'branch_code' => (string)($data['branchcode'] ?? ''), 'agent_party_id' => $agentInfo['agent_party_id'] ?? null, 'agent_name' => $agentInfo['agent_name'] ?? null, 'agent_mobile' => $agentInfo['agent_mobile'] ?? null, 'branch_name_en' => $agentInfo['branch_name'] ?? null, 'branch_name_ar' => $agentInfo['branch_name_ar'] ?? null, 'branch_address_en' => $agentInfo['branch_address'] ?? null, 'branch_address_ar' => $agentInfo['branch_address_ar'] ?? null, 'branch_map_url' => $agentInfo['branch_map_url'] ?? null, 'assigned_at' => date('Y-m-d H:i:s'), ]); // 2) Convert if agent ok, else fallback lead if (($agentInfo['success'] ?? false) && !empty($agentInfo['agent_party_id'])) { $oracleResult = create_oracle_lead_n_convert($oracleData, (string)$agentInfo['agent_party_id']); $ids = oracleExtractIds($oracleResult); $oracleLeadId = $ids['lead_id']; $oracleOppId = $ids['opp_id']; $personPartyId = $ids['person_party_id']; $oracleOk = oracleIsOk($oracleResult, $ids); $converted = oracleWasConverted($ids, $oracleResult); // IMPORTANT: treat conversion success as "opportunity" $leadType = $converted ? 'opportunity' : 'lead'; $syncStatus = $oracleOk ? 'SUCCESS' : 'FAILED'; $syncMsg = ($oracleOk && $converted) ? 'Assigned + converted to opportunity in Oracle' : (($oracleOk) ? 'Assigned + synced to Oracle (lead created)' : 'Oracle conversion failed'); apiLog($pdo, [ 'log_type' => 'oracle_convert', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'create_oracle_lead_n_convert', 'request_payload' => ['oracleData'=>$oracleData,'agent_party_id'=>$agentInfo['agent_party_id']], 'http_code' => oracleHttpCode($oracleResult), 'response_body' => json_encode($oracleResult, JSON_UNESCAPED_UNICODE), 'is_success' => (int)$oracleOk, 'error_message' => oracleErrorMsg($oracleResult), 'opportunity_id' => $oracleOppId, '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' => $syncMsg ]); updateLead($pdo, $leadid, [ 'lead_type' => $leadType, 'oracle_sync_status' => $syncStatus, 'oracle_lead_id' => $oracleLeadId, 'oracle_opportunity_id' => $oracleOppId, 'oracle_person_party_id' => $personPartyId, 'oracle_status_message' => $syncMsg, ]); // Send SMS ONLY after successful assignment + successful opportunity creation if (!empty($agentInfo['agent_party_id']) && $oracleOk && $leadType === 'opportunity') { $msg = buildAssignmentMessage($agentInfo); $smsResult = sendSmsInfinito($mobile, $msg); apiLog($pdo, [ 'log_type' => 'sms', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => SMS_API_URL, 'request_payload' => ['to'=>normalizeToE164Saudi($mobile),'from'=>SMS_SENDER,'message'=>$msg], 'http_code' => $smsResult['http'] ?? null, 'response_body' => (string)($smsResult['resp'] ?? ''), 'is_success' => (int)($smsResult['success'] ?? 0), 'error_message' => $smsResult['err'] ?? null, 'agent_id' => $agentInfo['agent_party_id'] ?? null, 'agent_name' => $agentInfo['agent_name'] ?? null, 'agent_mobile' => $agentInfo['agent_mobile'] ?? null, 'branch_name' => $agentInfo['branch_name'] ?? null, 'branch_address' => $agentInfo['branch_address'] ?? null, 'branch_map_link' => $agentInfo['branch_map_url'] ?? null, 'status_message' => ($smsResult['success'] ?? false) ? 'SMS sent' : 'SMS failed', ]); $waResult = sendWhatsAppPlaceholder($mobile, $msg); apiLog($pdo, [ 'log_type' => 'whatsapp', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'WHATSAPP_PLACEHOLDER', 'request_payload' => ['to'=>$mobile,'message'=>$msg], 'http_code' => $waResult['http'] ?? 0, 'response_body' => $waResult['resp'] ?? '', 'is_success' => (int)($waResult['success'] ?? 0), 'error_message' => $waResult['err'] ?? null, 'status_message' => 'WhatsApp not configured' ]); } } else { // HOT fallback lead only $oracleResult = create_oracle_lead($oracleData, null); $ids = oracleExtractIds($oracleResult); $oracleLeadId = $ids['lead_id']; $oracleOppId = $ids['opp_id']; $personPartyId = $ids['person_party_id']; $oracleOk = oracleIsOk($oracleResult, $ids); $syncStatus = $oracleOk ? 'SUCCESS' : 'FAILED'; $syncMsg = $oracleOk ? 'HOT fallback: lead created (no agent)' : 'HOT fallback: oracle lead failed'; apiLog($pdo, [ 'log_type' => 'oracle_lead_fallback', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'create_oracle_lead (fallback)', 'request_payload' => ['oracleData'=>$oracleData], 'http_code' => oracleHttpCode($oracleResult), 'response_body' => json_encode($oracleResult, JSON_UNESCAPED_UNICODE), 'is_success' => (int)$oracleOk, 'error_message' => oracleErrorMsg($oracleResult), 'status_message' => $syncMsg ]); updateLead($pdo, $leadid, [ 'lead_type' => 'lead', 'oracle_sync_status' => $syncStatus, 'oracle_lead_id' => $oracleLeadId, 'oracle_opportunity_id' => $oracleOppId, 'oracle_person_party_id' => $personPartyId, 'oracle_status_message' => $syncMsg, ]); } } else { // COLD lead only $oracleResult = create_oracle_lead($oracleData, null); $ids = oracleExtractIds($oracleResult); $oracleLeadId = $ids['lead_id']; $oracleOppId = $ids['opp_id']; $personPartyId = $ids['person_party_id']; $oracleOk = oracleIsOk($oracleResult, $ids); $syncStatus = $oracleOk ? 'SUCCESS' : 'FAILED'; $syncMsg = $oracleOk ? 'Cold lead synced to Oracle' : 'Cold lead: oracle sync failed'; apiLog($pdo, [ 'log_type' => 'oracle_lead', 'leadid' => $leadid, 'mobile' => $mobile, 'endpoint' => 'create_oracle_lead', 'request_payload' => ['oracleData'=>$oracleData], 'http_code' => oracleHttpCode($oracleResult), 'response_body' => json_encode($oracleResult, JSON_UNESCAPED_UNICODE), 'is_success' => (int)$oracleOk, 'error_message' => oracleErrorMsg($oracleResult), 'status_message' => $syncMsg ]); updateLead($pdo, $leadid, [ 'lead_type' => 'lead', 'oracle_sync_status' => $syncStatus, 'oracle_lead_id' => $oracleLeadId, 'oracle_opportunity_id' => $oracleOppId, 'oracle_person_party_id' => $personPartyId, 'oracle_status_message' => $syncMsg, ]); } 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, 'sms' => $smsResult, 'whatsapp' => $waResult ], JSON_UNESCAPED_UNICODE); } catch (Throwable $e) { if ($pdo instanceof PDO && $pdo->inTransaction()) $pdo->rollBack(); http_response_code(500); echo json_encode([ 'status' => 'error', 'message' => 'Server error', 'detail' => $e->getMessage() ], JSON_UNESCAPED_UNICODE); }