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

echo "=== DATABASE CONNECTION VERIFICATION ===\n\n";

// Show current connection details
echo "Database Host: " . $conn->host_info . "\n";
echo "Database Name: " . $conn->query("SELECT DATABASE() as db_name")->fetch_assoc()['db_name'] . "\n";
echo "Connection ID: " . $conn->thread_id . "\n";
echo "Server Version: " . $conn->server_info . "\n\n";

// Show actual product count
$count_result = $conn->query("SELECT COUNT(*) as total FROM products");
$total = $count_result->fetch_assoc()['total'];
echo "ACTUAL Total Products: $total\n";

// Show highest product ID
$max_id_result = $conn->query("SELECT MAX(id) as max_id FROM products");
$max_id = $max_id_result->fetch_assoc()['max_id'];
echo "ACTUAL Highest Product ID: $max_id\n\n";

// Test if we can INSERT a product right now
echo "=== TESTING LIVE INSERT ===\n";
$test_name = "LIVE TEST " . date('H:i:s');
$test_barcode = "TEST" . time();

$stmt = $conn->prepare("INSERT INTO products (name, category, price, stock, barcode) VALUES (?, 'TEST', 1.00, 1, ?)");
if ($stmt) {
    $stmt->bind_param('ss', $test_name, $test_barcode);
    if ($stmt->execute()) {
        $new_id = $conn->insert_id;
        echo "✅ INSERT SUCCESSFUL: New ID = $new_id\n";
        
        // Verify it exists
        $verify = $conn->prepare("SELECT * FROM products WHERE id = ?");
        $verify->bind_param('i', $new_id);
        $verify->execute();
        $result = $verify->get_result();
        
        if ($result->num_rows > 0) {
            echo "✅ VERIFICATION SUCCESSFUL: Product found in database\n";
            $product = $result->fetch_assoc();
            echo "   Name: {$product['name']}\n";
            echo "   Barcode: {$product['barcode']}\n";
            echo "   Created: {$product['created_at']}\n";
        } else {
            echo "❌ VERIFICATION FAILED: Product not found after insert!\n";
        }
        
        // Clean up test product
        $conn->query("DELETE FROM products WHERE id = $new_id");
        echo "Test product cleaned up.\n";
        
    } else {
        echo "❌ INSERT FAILED: " . $stmt->error . "\n";
    }
} else {
    echo "❌ PREPARE FAILED: " . $conn->error . "\n";
}

echo "\n=== COMPARISON WITH PREVIOUS REPORT ===\n";
echo "Previous report showed: 138 products, ID 248\n";
echo "Current investigation shows: $total products, ID $max_id\n";

if ($total != 138 || $max_id != 248) {
    echo "❌ MISMATCH DETECTED! You are looking at DIFFERENT DATABASES!\n";
    echo "The web interface and this script are connecting to different databases.\n";
} else {
    echo "✅ Same database confirmed.\n";
}
?>