setTimezone(new DateTimeZone('Asia/Riyadh')); return $dt->format('Y-m-d H:i:s'); } catch (Throwable $e) { return null; } } /* ------------------ Lookup Helper (MySQLi) ------------------ */ function getLookupValue(string $lookup_value, string $type): ?string { $servername = "localhost"; $username = "balubaid_changan_forms_leads"; $password = "Vision@2050"; $dbname = "balubaid_changan_forms_leads"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) return null; $sql = "SELECT result_value FROM mitsubishi_lookup_values WHERE lookup_value = ? AND type = ? LIMIT 1"; $stmt = $conn->prepare($sql); if (!$stmt) { $conn->close(); return null; } $stmt->bind_param("ss", $lookup_value, $type); $stmt->execute(); $stmt->bind_result($result_value); $out = null; if ($stmt->fetch()) $out = $result_value; $stmt->close(); $conn->close(); return $out; } /* ------------------ Optional: Submit to Honda Endpoint ------------------ */ function submitHondaServiceLead(array $data, array $options = []): array { $endpoint = $options['endpoint'] ?? 'https://honda.core3.agency/service_request/get_service.php?confirm=@CRM1'; $timeout = (int)($options['timeout'] ?? 30); $postData = [ 'your-city' => $data['your-city'] ?? '', 'phonenumber' => $data['phonenumber'] ?? '', 'your-car' => $data['your-car'] ?? '', 'your-email' => $data['your-email'] ?? '', 'your-name' => $data['your-name'] ?? '', 'source' => $data['source'] ?? '', 'gender' => $data['gender'] ?? '', 'formid' => $data['formid'] ?? '', 'pay-method' => $data['pay-method'] ?? '', 'salary' => $data['salary'] ?? '', 'campaign' => $data['campaign'] ?? '', 'PurTime' => $data['PurTime'] ?? '', 'PrefTime' => $data['PrefTime'] ?? '', ]; $ch = curl_init($endpoint); curl_setopt_array($ch, [ CURLOPT_POST => true, CURLOPT_POSTFIELDS => http_build_query($postData), CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => $timeout, CURLOPT_CONNECTTIMEOUT => 10, CURLOPT_HTTPHEADER => [ 'Content-Type: application/x-www-form-urlencoded', ], ]); $response = curl_exec($ch); $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE); $curlError = curl_error($ch); curl_close($ch); return [ 'success' => ($curlError === '' && $httpCode >= 200 && $httpCode < 300), 'http_code' => $httpCode, 'response' => $response, 'error' => $curlError ?: null, 'payload' => $postData, ]; } /* ------------------ DB CONFIG (PDO) ------------------ */ $dbHost = "localhost"; $dbName = "balubaid_changan_forms_leads"; $dbUser = "balubaid_changan_forms_leads"; $dbPass = "Vision@2050"; try { $dsn = "mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4"; $pdo = new PDO($dsn, $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); } catch (Throwable $e) { respondJson([ 'success' => false, 'message' => 'DB connection failed.', 'error' => $e->getMessage(), ], 500); } /* ------------------ INPUT: accept form POST, or JSON body ------------------ */ $raw = file_get_contents('php://input'); if (!empty($raw) && stripos($_SERVER['CONTENT_TYPE'] ?? '', 'application/json') !== false) { $json = json_decode($raw, true); if (is_array($json)) { $_POST = array_merge($_POST, $json); } } $data = $_POST; /* ------------------ Normalize + Lookups ------------------ */ // mobile $normMobile9 = normalizeSaudiMobile(convert2english($data['mobile'] ?? '')); if ($normMobile9 !== null) { $data['mobile'] = "0" . $normMobile9; // 05xxxxxxxx } // Lookups (safe fallbacks if null) $data['paymethod'] = getLookupValue(extractLabel($data['paymethod'] ?? ''), 'paymethod') ?? ($data['paymethod'] ?? null); $data['purchasetime'] = getLookupValue(extractLabel($data['purchasetime'] ?? ''), 'purtime') ?? ($data['purchasetime'] ?? null); $data['gender'] = getLookupValue(extractLabel($data['gender'] ?? ''), 'gender') ?? ($data['gender'] ?? null); $data['salary'] = getLookupValue(extractLabel($data['salary'] ?? ''), 'salary') ?? ($data['salary'] ?? null); $data['city'] = getLookupValue(extractLabel($data['city'] ?? ''), 'city') ?? ($data['city'] ?? null); $data['model'] = getLookupValue(extractLabel($data['adname'] ?? ''), 'model') ?? ($data['model'] ?? ($data['adname'] ?? null)); $data['preftime'] = getLookupValue(extractLabel($data['preftime'] ?? ''), 'preftime') ?? ($data['preftime'] ?? null); $data['branch'] = getLookupValue(extractLabel($data['branch'] ?? ''), 'branch') ?? ($data['branch'] ?? null); // fullname $data['fullname'] = trim((string)($data['fname'] ?? '') . ' ' . (string)($data['lname'] ?? '')); /* ------------------ Required Fields ------------------ */ $mobile = normalizeMobile($data['mobile'] ?? ''); $leadId = (string)($data['leadid'] ?? ''); $createdAt = metaTimeToRiyadh($data['createdat'] ?? null); if ($mobile === '' || $leadId === '') { respondJson([ 'success' => false, 'message' => 'Missing required fields: mobile or leadid', 'received' => [ 'mobile' => $mobile, 'leadid' => $leadId, ] ], 400); } /* ------------------ Main Flow ------------------ */ try { $pdo->beginTransaction(); // 1) Check duplicate within last 15 days $dupCheckSql = " SELECT leadid, createdat FROM mitsubishi_leads WHERE mobile = :mobile AND ( (createdat IS NOT NULL AND createdat >= (NOW() - INTERVAL 15 DAY)) OR (createdat IS NULL AND created_at >= (NOW() - INTERVAL 15 DAY)) ) ORDER BY COALESCE(createdat, created_at) DESC LIMIT 1 "; $dupStmt = $pdo->prepare($dupCheckSql); $dupStmt->execute([':mobile' => $mobile]); $existing = $dupStmt->fetch(); if ($existing) { // 2) Insert into duplicate table $insDupSql = " INSERT INTO mitsubishi_duplicate_leads ( leadid, mobile, original_lead_id, original_createdat, reason, adid, adname, adsquadename, cmpname, strategy, channel, formname, fname, email, branch, city, paymethod, purchasetime, preftime, model, gender, salary, leadpreferred, createdat ) VALUES ( :leadid, :mobile, :original_lead_id, :original_createdat, :reason, :adid, :adname, :adsquadename, :cmpname, :strategy, :channel, :formname, :fname, :email, :branch, :city, :paymethod, :purchasetime, :preftime, :model, :gender, :salary, :leadpreferred, :createdat ) ON DUPLICATE KEY UPDATE dumped_at = CURRENT_TIMESTAMP, reason = VALUES(reason) "; $insDup = $pdo->prepare($insDupSql); $insDup->execute([ ':leadid' => $leadId, ':mobile' => $mobile, ':original_lead_id' => $existing['leadid'] ?? null, ':original_createdat' => $existing['createdat'] ?? null, ':reason' => 'Mobile duplicate within 15 days', ':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'] ?: ($data['fname'] ?? null), ':email' => $data['email'] ?? null, ':branch' => $data['branch'] ?? null, ':city' => $data['city'] ?? null, ':paymethod' => $data['paymethod'] ?? null, ':purchasetime' => $data['purchasetime'] ?? null, ':preftime' => $data['preftime'] ?? null, ':model' => $data['model'] ?? null, ':gender' => $data['gender'] ?? null, ':salary' => $data['salary'] ?? null, ':leadpreferred'=> $data['leadpreferred'] ?? null, ':createdat' => $createdAt, ]); $pdo->commit(); respondJson([ 'success' => true, 'status' => 'duplicate', 'message' => 'Duplicate lead (mobile exists within 15 days). Dumped to duplicate table.', 'leadid' => $leadId, 'mobile' => $mobile, 'original' => [ 'leadid' => $existing['leadid'] ?? null, 'createdat' => $existing['createdat'] ?? null, ], ]); } // 3) Insert/upsert into main table $insMainSql = " INSERT INTO mitsubishi_leads ( leadid, adid, adname, adsquadename, cmpname, strategy, channel, formname, fname, email, mobile, branch, city, paymethod, purchasetime, preftime, model, gender, salary, leadpreferred, createdat ) VALUES ( :leadid, :adid, :adname, :adsquadename, :cmpname, :strategy, :channel, :formname, :fname, :email, :mobile, :branch, :city, :paymethod, :purchasetime, :preftime, :model, :gender, :salary, :leadpreferred, :createdat ) ON DUPLICATE KEY UPDATE adname = VALUES(adname), cmpname = VALUES(cmpname), mobile = VALUES(mobile), branch = VALUES(branch), city = VALUES(city), paymethod = VALUES(paymethod), purchasetime = VALUES(purchasetime), salary = VALUES(salary), gender = VALUES(gender), preftime = VALUES(preftime), model = VALUES(model), leadpreferred = VALUES(leadpreferred), createdat = VALUES(createdat), updated_at = CURRENT_TIMESTAMP "; $insMain = $pdo->prepare($insMainSql); $insMain->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'] ?: ($data['fname'] ?? 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'] ?? null, ':gender' => $data['gender'] ?? null, ':salary' => $data['salary'] ?? null, ':leadpreferred' => $data['leadpreferred'] ?? null, ':createdat' => $createdAt, ]); $pdo->commit(); // Build payload for Honda endpoint (optional) $payloadToHonda = [ 'your-city' => $data['city'] ?? null, 'phonenumber' => $data['mobile'] ?? null, // keep 05xxxxxxx format 'your-car' => $data['model'] ?? null, 'your-email' => $data['email'] ?? null, 'your-name' => $data['fullname'] ?? null, 'source' => $data['channel'] ?? null, 'gender' => $data['gender'] ?? null, 'formid' => '565845759', 'pay-method' => $data['paymethod'] ?? null, 'salary' => $data['salary'] ?? null, 'campaign' => $data['cmpname'] ?? null, 'PurTime' => $data['purchasetime'] ?? null, 'PrefTime' => $data['preftime'] ?? null, ]; // If you want to submit to Honda now, uncomment: // $hondaSubmit = submitHondaServiceLead($payloadToHonda); // Otherwise keep null to avoid extra latency / failures $hondaSubmit = null; respondJson([ 'success' => true, 'status' => 'stored', 'message' => 'Lead stored successfully in main table.', 'leadid' => $leadId, 'mobile' => $mobile, 'createdat_riyadh' => $createdAt, 'normalized' => [ 'city' => $data['city'] ?? null, 'branch' => $data['branch'] ?? null, 'model' => $data['model'] ?? null, 'paymethod' => $data['paymethod'] ?? null, 'purchasetime' => $data['purchasetime'] ?? null, 'salary' => $data['salary'] ?? null, 'gender' => $data['gender'] ?? null, 'preftime' => $data['preftime'] ?? null, 'fullname' => $data['fullname'] ?? null, 'cdate' => date('m/d/Y'), ], 'payload_to_honda' => $payloadToHonda, 'honda_submit' => $hondaSubmit, ]); } catch (Throwable $e) { if ($pdo->inTransaction()) $pdo->rollBack(); respondJson([ 'success' => false, 'message' => 'Server error while saving lead.', 'error' => $e->getMessage(), ], 500); }