­­­­­­­­­­­­­­­­­­
<!DOCTYPE html>
<html>
<?php
/**
 * OMODA / iCaur Lead Fetcher + MySQL Daily Full Refresh
 *
 * Method:
 * 1. Get OAuth token
 * 2. Fetch all leads from CRM using FetchXML + pagination
 * 3. Start MySQL transaction
 * 4. TRUNCATE table
 * 5. INSERT all fresh rows
 *
 * Recommended cron:
 * 0 2 * * * /usr/local/bin/php /home/USERNAME/public_html/path/fetch_icaur_leads.php
 */

ini_set('display_errors', 1);
error_reporting(E_ALL);
date_default_timezone_set('Asia/Riyadh');

/* =========================
   CONFIG
========================= */

$config = [
    // OAuth / CRM
    'token_url'      => 'https://saadfs.omodaglobal.com:8443/adfs/oauth2/token',
    'crm_base_url'   => 'https://sacrm.omodaglobal.com:8081/api/data/v9.1/leads',
    'client_id'      => 'c17a27a2-52d7-45f5-90a2-9239d6d16295',
    'client_secret'  => '48Emmkc4Nrkcj4sSauThxXFTmt3tW1a54mA55xpU',
    'resource'       => 'https://sacrm.omodaglobal.com:8081/api/data/v9.1/',
    'username'       => 'OMODASA\leadapi',
    'password'       => 'OmodasaP@ssw0rd',

    // Fetch settings
    'brand_name'     => 'iCaur',
    'last_x_days'    => 124,
    'page_size'      => 100,
    'timeout'        => 120,

    // MySQL
    'db_host'        => 'localhost',
    'db_name'        => 'balubaid_honda_native_forms_leads',
    'db_user'        => 'balubaid_honda_native_forms',
    'db_pass'        => 'Vision@2050',
    'db_charset'     => 'utf8mb4',

    // Table
    'table_name'     => 'omoda_leads_daily',
];

/* =========================
   HELPERS
========================= */

function httpRequest($url, $method = 'GET', $headers = [], $body = null, $timeout = 120)
{
    $ch = curl_init();

    curl_setopt_array($ch, [
        CURLOPT_URL            => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_CUSTOMREQUEST  => $method,
        CURLOPT_HTTPHEADER     => $headers,
        CURLOPT_TIMEOUT        => $timeout,
        CURLOPT_CONNECTTIMEOUT => 30,
        CURLOPT_SSL_VERIFYPEER => true,
        CURLOPT_SSL_VERIFYHOST => 2,
    ]);

    if ($body !== null) {
        curl_setopt($ch, CURLOPT_POSTFIELDS, $body);
    }

    $response  = curl_exec($ch);
    $curlErrNo = curl_errno($ch);
    $curlError = curl_error($ch);
    $httpCode  = curl_getinfo($ch, CURLINFO_HTTP_CODE);

    curl_close($ch);

    if ($curlErrNo) {
        throw new Exception("cURL error ({$curlErrNo}): {$curlError}");
    }

    return [
        'http_code' => $httpCode,
        'body'      => $response,
    ];
}

function getDbConnection(array $config)
{
    $dsn = "mysql:host={$config['db_host']};dbname={$config['db_name']};charset={$config['db_charset']}";
    $pdo = new PDO($dsn, $config['db_user'], $config['db_pass'], [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    return $pdo;
}

function getAccessToken(array $config)
{
    $postFields = http_build_query([
        'grant_type'    => 'password',
        'client_id'     => $config['client_id'],
        'client_secret' => $config['client_secret'],
        'resource'      => $config['resource'],
        'username'      => $config['username'],
        'password'      => $config['password'],
    ]);

    $response = httpRequest(
        $config['token_url'],
        'POST',
        [
            'Content-Type: application/x-www-form-urlencoded',
            'Accept: application/json',
        ],
        $postFields,
        $config['timeout']
    );

    if ($response['http_code'] < 200 || $response['http_code'] >= 300) {
        throw new Exception("Token request failed. HTTP {$response['http_code']} Response: " . $response['body']);
    }

    $json = json_decode($response['body'], true);

    if (!is_array($json) || empty($json['access_token'])) {
        throw new Exception("Access token not found in response: " . $response['body']);
    }

    return $json['access_token'];
}


function syncLeadsDirectToDb(PDO $pdo, array $config)
{
    $token = getAccessToken($config);
    $page = 1;
    $totalInserted = 0;

    $table = $config['table_name'];

    $pdo->exec("TRUNCATE TABLE `{$table}`");

    $sql = "
    INSERT INTO `{$table}` (
        leadid,
        brand_name,
        createdon,
        modifiedon,
        statuscode,
        statecode,
        new_number,
        new_sourcetype,
        raw_json,
        synced_at
    ) VALUES (
        :leadid,
        :brand_name,
        :createdon,
        :modifiedon,
        :statuscode,
        :statecode,
        :new_number,
        :new_sourcetype,
        :raw_json,
        :synced_at
    )
    ON DUPLICATE KEY UPDATE
        brand_name = VALUES(brand_name),
        createdon = VALUES(createdon),
        modifiedon = VALUES(modifiedon),
        statuscode = VALUES(statuscode),
        statecode = VALUES(statecode),
        new_number = VALUES(new_number),
        new_sourcetype = VALUES(new_sourcetype),
        raw_json = VALUES(raw_json),
        synced_at = VALUES(synced_at)
";

    $stmt = $pdo->prepare($sql);

    while (true) {
        $result = fetchLeadsPage($config, $token, $page);
        $rows = isset($result['value']) && is_array($result['value']) ? $result['value'] : [];
        $count = count($rows);

        if ($count === 0) {
            break;
        }

        foreach ($rows as $lead) {
            if (empty($lead['leadid'])) {
                continue;
            }

            $stmt->execute([
                ':leadid'         => $lead['leadid'],
                ':brand_name'     => $config['brand_name'],
                ':createdon'      => normalizeDateTime($lead['createdon'] ?? null),
                ':modifiedon'     => normalizeDateTime($lead['modifiedon'] ?? null),
                ':statuscode'     => isset($lead['statuscode']) ? (string)$lead['statuscode'] : null,
                ':statecode'      => isset($lead['statecode']) ? (string)$lead['statecode'] : null,
                ':new_number'     => isset($lead['new_number']) ? (string)$lead['new_number'] : null,
                ':new_sourcetype' => isset($lead['new_sourcetype']) ? (string)$lead['new_sourcetype'] : null,
                ':raw_json'       => json_encode($lead, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                ':synced_at'      => date('Y-m-d H:i:s'),
            ]);

            $totalInserted++;
        }

        unset($result, $rows);
        gc_collect_cycles();

        if ($count < $config['page_size']) {
            break;
        }

        $page++;
    }

    return $totalInserted;
}

function buildFetchXml($page, $count, $brandName, $lastXDays)
{
    $page = (int)$page;
    $count = (int)$count;
    $lastXDays = (int)$lastXDays;

    return "
<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' returntotalrecordcount='true' page='{$page}' count='{$count}' no-lock='false'>
  <entity name='lead'>
    <attribute name='leadid'/>
    <attribute name='createdon'/>
    <attribute name='modifiedon'/>
    <attribute name='statuscode'/>
    <attribute name='statecode'/>
    <attribute name='ownerid'/>
    <attribute name='campaignid'/>
    <attribute name='new_number'/>
    <attribute name='new_sourcetype'/>
    <attribute name='new_paymenttype'/>
    <attribute name='new_product_id'/>
    <attribute name='new_carversion_id'/>
    <attribute name='new_city_id'/>
    <attribute name='new_carbookingcount'/>
    

    <order attribute='createdon' descending='true'/>

    <filter type='and'>
      <condition attribute='modifiedon' operator='last-x-days' value='{$lastXDays}'/>
    </filter>
  </entity>
</fetch>";
}

function fetchLeadsPage(array $config, $accessToken, $page = 1)
{
    $fetchXml = buildFetchXml(
        $page,
        $config['page_size'],
        $config['brand_name'],
        $config['last_x_days']
    );

    $url = $config['crm_base_url'] . '?fetchXml=' . rawurlencode($fetchXml);

    $response = httpRequest(
        $url,
        'GET',
        [
            'Authorization: Bearer ' . $accessToken,
            'Prefer: odata.include-annotations="*"',
            'Accept: application/json',
        ],
        null,
        $config['timeout']
    );

    if ($response['http_code'] < 200 || $response['http_code'] >= 300) {
        throw new Exception("Lead request failed. HTTP {$response['http_code']} Response: " . $response['body']);
    }

    $json = json_decode($response['body'], true);

    if (!is_array($json)) {
        throw new Exception("Invalid JSON response from lead API: " . $response['body']);
    }

    return $json;
}

function fetchAllLeads(array $config)
{
    $token = getAccessToken($config);

    $page = 1;
    $allLeads = [];

    while (true) {
        $result = fetchLeadsPage($config, $token, $page);
        $rows   = isset($result['value']) && is_array($result['value']) ? $result['value'] : [];
        $count  = count($rows);

        if ($count > 0) {
            $allLeads = array_merge($allLeads, $rows);
        }

        if ($count < $config['page_size']) {
            break;
        }

        $page++;
    }

    return $allLeads;
}

function normalizeDateTime($value)
{
    if (empty($value)) {
        return null;
    }

    $ts = strtotime($value);
    if ($ts === false) {
        return null;
    }

    return date('Y-m-d H:i:s', $ts);
}

function truncateAndInsertLeads(PDO $pdo, array $config, array $leads)
{
    $table = $config['table_name'];
    $syncedAt = date('Y-m-d H:i:s');

    $pdo->exec("TRUNCATE TABLE `{$table}`");

    if (empty($leads)) {
        return 0;
    }

    $sql = "
        INSERT INTO `{$table}` (
            leadid,
            brand_name,
            createdon,
            modifiedon,
            statuscode,
            statecode,
            ownerid,
            campaignid,
            new_number,
            new_sourcetype,
            new_paymenttype,
            new_product_id,
            new_carversion_id,
            new_city_id,
            raw_json,
            synced_at
        ) VALUES (
            :leadid,
            :brand_name,
            :createdon,
            :modifiedon,
            :statuscode,
            :statecode,
            :ownerid,
            :campaignid,
            :new_number,
            :new_sourcetype,
            :new_paymenttype,
            :new_product_id,
            :new_carversion_id,
            :new_city_id,
            :raw_json,
            :synced_at
        )
    ";

    $stmt = $pdo->prepare($sql);

    $inserted = 0;

    foreach ($leads as $lead) {
        $leadId = isset($lead['leadid']) ? trim((string)$lead['leadid']) : '';

        if ($leadId === '') {
            continue;
        }

        $brandName = null;
        if (isset($lead['brand.new_name'])) {
            $brandName = $lead['brand.new_name'];
        } elseif (isset($lead['brand_x002e_new_name'])) {
            $brandName = $lead['brand_x002e_new_name'];
        } else {
            $brandName = $config['brand_name'] ?? null;
        }

        $stmt->execute([
            ':leadid'           => $leadId,
            ':brand_name'       => $brandName,
            ':createdon'        => normalizeDateTime($lead['createdon'] ?? null),
            ':modifiedon'       => normalizeDateTime($lead['modifiedon'] ?? null),
            ':statuscode'       => isset($lead['statuscode']) ? (string)$lead['statuscode'] : null,
            ':statecode'        => isset($lead['statecode']) ? (string)$lead['statecode'] : null,
            ':ownerid'          => isset($lead['_ownerid_value']) ? (string)$lead['_ownerid_value'] : (isset($lead['ownerid']) ? (string)$lead['ownerid'] : null),
            ':campaignid'       => isset($lead['_campaignid_value']) ? (string)$lead['_campaignid_value'] : (isset($lead['campaignid']) ? (string)$lead['campaignid'] : null),
            ':new_number'       => isset($lead['new_number']) ? (string)$lead['new_number'] : null,
            ':new_sourcetype'   => isset($lead['new_sourcetype']) ? (string)$lead['new_sourcetype'] : null,
            ':new_paymenttype'  => isset($lead['new_paymenttype']) ? (string)$lead['new_paymenttype'] : null,
            ':new_product_id'   => isset($lead['_new_product_id_value']) ? (string)$lead['_new_product_id_value'] : (isset($lead['new_product_id']) ? (string)$lead['new_product_id'] : null),
            ':new_carversion_id'=> isset($lead['_new_carversion_id_value']) ? (string)$lead['_new_carversion_id_value'] : (isset($lead['new_carversion_id']) ? (string)$lead['new_carversion_id'] : null),
            ':new_city_id'      => isset($lead['_new_city_id_value']) ? (string)$lead['_new_city_id_value'] : (isset($lead['new_city_id']) ? (string)$lead['new_city_id'] : null),
            ':raw_json'         => json_encode($lead, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            ':synced_at'        => $syncedAt,
        ]);

        $inserted++;
    }

    return $inserted;
}

/* =========================
   RUN
========================= */

header('Content-Type: application/json; charset=utf-8');

try {
    $pdo = getDbConnection($config);

    $pdo->beginTransaction();

    try {
        $inserted = syncLeadsDirectToDb($pdo, $config);
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }

    echo json_encode([
        'ok' => true,
        'total_inserted' => $inserted,
        'synced_at' => date('Y-m-d H:i:s')
    ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

} catch (Throwable $e) {
    echo json_encode([
        'ok' => false,
        'error' => $e->getMessage()
    ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
}