<?php
date_default_timezone_set('Asia/Colombo');
include 'config.php';

echo "=== DETAILED RECENT PRODUCT INVESTIGATION ===\n\n";

// Check current date/time settings
echo "Current PHP Date/Time: " . date('Y-m-d H:i:s') . "\n";
echo "Database Timezone Setting: ";
$tz_result = $conn->query("SELECT @@session.time_zone, NOW() as db_time");
$tz_row = $tz_result->fetch_assoc();
echo $tz_row['time_zone'] . " (DB Time: " . $tz_row['db_time'] . ")\n\n";

// Check ALL products from the last 10 days (not just 7)
echo "=== ALL PRODUCTS FROM LAST 10 DAYS ===\n";
$recent = $conn->query("
    SELECT id, name, barcode, created_at, 
           DATE(created_at) as date_only,
           TIME(created_at) as time_only
    FROM products 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 10 DAY)
    ORDER BY created_at DESC
");

if ($recent->num_rows > 0) {
    printf("%-5s %-20s %-15s %-12s %-10s\n", "ID", "Name", "Barcode", "Date", "Time");
    echo str_repeat("-", 70) . "\n";
    
    while ($row = $recent->fetch_assoc()) {
        printf("%-5s %-20s %-15s %-12s %-10s\n", 
            $row['id'], 
            substr($row['name'], 0, 20),
            $row['barcode'],
            $row['date_only'],
            $row['time_only']
        );
    }
} else {
    echo "❌ NO PRODUCTS found in the last 10 days!\n";
}

// Check by specific dates you mentioned
echo "\n=== CHECKING SPECIFIC DATES ===\n";
$dates_to_check = [
    '2025-10-08', // Today
    '2025-10-07', // Yesterday  
    '2025-10-06', // Two days ago (when you said you added)
    '2025-10-05', // Three days ago
    '2025-10-04'  // Four days ago
];

foreach ($dates_to_check as $date) {
    $count_result = $conn->query("SELECT COUNT(*) as count FROM products WHERE DATE(created_at) = '$date'");
    $count = $count_result->fetch_assoc()['count'];
    echo "$date: $count products\n";
}

// Check for products without created_at timestamps
echo "\n=== PRODUCTS WITHOUT TIMESTAMPS ===\n";
$no_timestamp = $conn->query("SELECT COUNT(*) as count FROM products WHERE created_at IS NULL OR created_at = '0000-00-00 00:00:00'");
$no_ts_count = $no_timestamp->fetch_assoc()['count'];
echo "Products without valid timestamps: $no_ts_count\n";

if ($no_ts_count > 0) {
    echo "These products might be the ones you added recently but they don't have proper timestamps!\n";
    $no_ts_products = $conn->query("SELECT id, name, barcode FROM products WHERE created_at IS NULL OR created_at = '0000-00-00 00:00:00' LIMIT 10");
    
    printf("%-5s %-30s %-15s\n", "ID", "Name", "Barcode");
    echo str_repeat("-", 55) . "\n";
    
    while ($row = $no_ts_products->fetch_assoc()) {
        printf("%-5s %-30s %-15s\n", 
            $row['id'], 
            substr($row['name'], 0, 30),
            $row['barcode']
        );
    }
}

// Check if there are products with very recent IDs but old timestamps
echo "\n=== CHECKING ID vs TIMESTAMP MISMATCH ===\n";
$latest_ids = $conn->query("SELECT id, name, barcode, created_at FROM products ORDER BY id DESC LIMIT 10");

echo "Latest 10 products by ID:\n";
printf("%-5s %-20s %-15s %-20s\n", "ID", "Name", "Barcode", "Created At");
echo str_repeat("-", 70) . "\n";

while ($row = $latest_ids->fetch_assoc()) {
    printf("%-5s %-20s %-15s %-20s\n", 
        $row['id'], 
        substr($row['name'], 0, 20),
        $row['barcode'],
        $row['created_at']
    );
}

echo "\n=== INVESTIGATION COMPLETE ===\n";
echo "Look for products with recent IDs but old timestamps - those might be your missing items!\n";
?>