<?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[cite: 1]
$originating_system = 'northstar';[cite: 1]

$db_host = 'localhost';[cite: 1]
$db_name = 'bonstdata3db';[cite: 1]
$db_user = 'bonstdata3user';[cite: 1]
$db_pass = 'Lb3@e9#23rhCVnYQ';[cite: 1]
$db_table_listings = 'bonstlistingsnew3';
$db_table_media    = 'bonstmedia3';

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

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

echo "--------------------------------------------------\n";[cite: 1]
echo "Audit Report for Date: {$yesterday_date}\n";[cite: 1]
echo "--------------------------------------------------\n";[cite: 1]

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

$mlsgrid_ids = [];[cite: 1]
$total_scanned = 0;[cite: 1]
$next_url = $initial_url;[cite: 1]

echo "Querying MLSGrid API...\n";[cite: 1]

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

    $response = curl_exec($ch);[cite: 1]
    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);[cite: 1]
    curl_close($ch);[cite: 1]

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

    $data = json_decode($response, true);[cite: 1]

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

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

$mlsgrid_count = count($mlsgrid_ids);[cite: 1]

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

// ==========================================
// CONNECT TO DATABASE
// ==========================================
try {
    $dsn = "mysql:host={$db_host};dbname={$db_name};charset=utf8mb4";[cite: 1]
    $pdo = new PDO($dsn, $db_user, $db_pass, [[cite: 1]
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,[cite: 1]
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC[cite: 1]
    ]);
} catch (PDOException $e) {
    die("Database Connection Error: " . $e->getMessage() . "\n");[cite: 1]
}

// ==========================================
// 2. QUERY LOCAL DATABASE FOR LISTING IDs (bonstlistingsnew3)
// ==========================================
$db_ids = [];[cite: 1]

try {
    $sql = "SELECT ListingId 
            FROM {$db_table_listings} 
            WHERE DATE(OriginalEntryTimestamp) = :yesterday";[cite: 1]
            
    $stmt = $pdo->prepare($sql);[cite: 1]
    $stmt->execute([':yesterday' => $yesterday_date]);[cite: 1]
    
    $raw_db_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);[cite: 1]
    $db_ids = array_map('strval', $raw_db_ids);[cite: 1]

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

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

// ==========================================
// 3. QUERY LOCAL DATABASE FOR MEDIA IDs (bonstmedia3)
// ==========================================
$media_ids = [];

try {
    // DISTINCT ensures unique ResourceRecordIDs since each image has a separate row
    $sql_media = "SELECT DISTINCT ResourceRecordID 
                  FROM {$db_table_media} 
                  WHERE DATE(OriginalEntryTimestamp) = :yesterday";
            
    $stmt_media = $pdo->prepare($sql_media);
    $stmt_media->execute([':yesterday' => $yesterday_date]);
    
    $raw_media_ids = $stmt_media->fetchAll(PDO::FETCH_COLUMN);
    $media_ids = array_map('strval', $raw_media_ids);

    $media_count = count($media_ids);
    echo "✓ Local DB Media Properties (OriginalEntryTimestamp = {$yesterday_date}): {$media_count}\n";

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

// ==========================================
// 4. COMPARISON & DISCREPANCY ANALYSIS
// ==========================================
echo "--------------------------------------------------\n";[cite: 1]
echo "--- LISTINGS VS MLSGRID ---\n";

$diff_listings = $db_count - $mlsgrid_count;[cite: 1]

if ($diff_listings === 0) {
    echo "STATUS: MATCH ✓ (Listings table and MLSGrid both have {$db_count} records)\n";
} else {
    echo "STATUS: MISMATCH ⚠️\n";[cite: 1]
    echo "Difference: " . abs($diff_listings) . " record(s) " . ($diff_listings > 0 ? "more in DB" : "missing in DB") . "\n";[cite: 1]
    
    $missing_in_db = array_diff($mlsgrid_ids, $db_ids);[cite: 1]
    $extra_in_db   = array_diff($db_ids, $mlsgrid_ids);[cite: 1]

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

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

echo "--------------------------------------------------\n";
echo "--- MEDIA VS MLSGRID ---\n";

$diff_media = $media_count - $mlsgrid_count;

if ($diff_media === 0) {
    echo "STATUS: MATCH ✓ (Media table and MLSGrid both have {$media_count} unique property IDs)\n";
} else {
    echo "STATUS: MISMATCH ⚠️\n";
    echo "Difference: " . abs($diff_media) . " record(s) " . ($diff_media > 0 ? "more in Media table" : "missing in Media table") . "\n";
    
    $missing_in_media = array_diff($mlsgrid_ids, $media_ids);
    $extra_in_media   = array_diff($media_ids, $mlsgrid_ids);

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

    if (!empty($extra_in_media)) {
        echo "\n[!] ResourceRecordID(s) in Media DB but NOT in MLSGrid (" . count($extra_in_media) . "):\n";
        foreach ($extra_in_media as $id) {
            echo "  + ResourceRecordID: {$id}\n";
        }
    }
}
echo "--------------------------------------------------\n";[cite: 1]