<?php
// Set execution limits for large API pagination sets
set_time_limit(300);
ini_set('memory_limit', '256M');

// ==========================================
// CONFIGURATION & CREDENTIALS
// ==========================================
$api_token = '3a55451872ef2a363ab5de3a5525b23466479eca'; // Replace with actual token
$originating_system = 'northstar';

$db_host = 'localhost';
$db_name = 'bonstdata3db';
$db_user = 'bonstdata3user';
$db_pass = 'Lb3@e9#23rhCVnYQ';
$db_table = 'bonstlistingsnew3';

// ==========================================
// DATE CALCULATION
// ==========================================
// Yesterday's date in YYYY-MM-DD format
$yesterday_date = date('Y-m-d', strtotime('yesterday'));

// Filter ModificationTimestamp starting from yesterday midnight UTC
$yesterday_start_iso = $yesterday_date . 'T00:00:00Z';

echo "--------------------------------------------------\n";
echo "Audit Report for Date: {$yesterday_date}\n";
echo "--------------------------------------------------\n";

// ==========================================
// 1. FETCH & COLLECT LISTING IDs FROM MLSGRID
// ==========================================
$initial_url = "https://api.mlsgrid.com/v2/Property?" . http_build_query([
    '$filter' => "OriginatingSystemName eq '{$originating_system}' and ModificationTimestamp ge {$yesterday_start_iso}",
    '$select' => 'ListingId,OriginalEntryTimestamp,ModificationTimestamp'
]);

$mlsgrid_ids = [];
$total_scanned = 0;
$next_url = $initial_url;

echo "Querying MLSGrid API...\n";

while ($next_url) {
    $ch = curl_init();
    curl_setopt_array($ch, [
        CURLOPT_URL => $next_url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '', // Handles gzipped API responses
        CURLOPT_HTTPHEADER => [
            "Authorization: Bearer {$api_token}",
            "Accept-Encoding: gzip,deflate",
            "Accept: application/json"
        ]
    ]);

    $response = curl_exec($ch);
    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($http_code !== 200 || !$response) {
        die("API Request Failed with HTTP Status Code: {$http_code}\nResponse: {$response}\n");
    }

    $data = json_decode($response, true);

    if (isset($data['value']) && is_array($data['value'])) {
        foreach ($data['value'] as $item) {
            $total_scanned++;
            // Check if OriginalEntryTimestamp matches yesterday's date (YYYY-MM-DD)
            if (!empty($item['OriginalEntryTimestamp'])) {
                $entry_date = substr($item['OriginalEntryTimestamp'], 0, 10);
                if ($entry_date === $yesterday_date) {
                    $mlsgrid_ids[] = (string)$item['ListingId'];
                }
            }
        }
    }

    // Follow pagination link if present
    $next_url = $data['@odata.nextLink'] ?? null;
}

$mlsgrid_count = count($mlsgrid_ids);

echo "✓ Total Modified Listings Scanned from MLSGrid: {$total_scanned}\n";
echo "✓ MLSGrid Listings with OriginalEntryTimestamp = {$yesterday_date}: {$mlsgrid_count}\n";

// ==========================================
// 2. QUERY LOCAL DATABASE FOR LISTING IDs
// ==========================================
$db_ids = [];

try {
    $dsn = "mysql:host={$db_host};dbname={$db_name};charset=utf8mb4";
    $pdo = new PDO($dsn, $db_user, $db_pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    // Fetch all ListingIds matching yesterday's date
    $sql = "SELECT ListingId 
            FROM {$db_table} 
            WHERE DATE(OriginalEntryTimestamp) = :yesterday";
            
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':yesterday' => $yesterday_date]);
    
    // Convert DB results into a plain array of string IDs
    $raw_db_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $db_ids = array_map('strval', $raw_db_ids);

    $db_count = count($db_ids);
    echo "✓ Local DB Listings (OriginalEntryTimestamp = {$yesterday_date}): {$db_count}\n";

} catch (PDOException $e) {
    die("Database Error: " . $e->getMessage() . "\n");
}

// ==========================================
// 3. COMPARISON & DISCREPANCY ANALYSIS
// ==========================================
echo "--------------------------------------------------\n";
$difference = $db_count - $mlsgrid_count;

if ($difference === 0) {
    echo "STATUS: MATCH ✓ (Both sources report {$db_count} new listings)\n";
} else {
    echo "STATUS: MISMATCH ⚠️\n";
    echo "Difference: " . abs($difference) . " record(s) " . ($difference > 0 ? "more in DB" : "missing in DB") . "\n";
    
    // Find missing and extra IDs
    $missing_in_db = array_diff($mlsgrid_ids, $db_ids);
    $extra_in_db   = array_diff($db_ids, $mlsgrid_ids);

    if (!empty($missing_in_db)) {
        echo "\n[!] Listing ID(s) present in MLSGrid but MISSING in local DB (" . count($missing_in_db) . "):\n";
        foreach ($missing_in_db as $id) {
            echo "  - ListingId: {$id}\n";
        }
    }

    if (!empty($extra_in_db)) {
        echo "\n[!] Listing ID(s) present in local DB but NOT in MLSGrid count (" . count($extra_in_db) . "):\n";
        foreach ($extra_in_db as $id) {
            echo "  + ListingId: {$id}\n";
        }
    }
}
echo "--------------------------------------------------\n";