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

echo "=== COMPREHENSIVE DATABASE ENVIRONMENT CHECK ===\n\n";

// Check all database-related environment variables
echo "=== DATABASE CONNECTION DETAILS ===\n";
echo "Host: " . $conn->host_info . "\n";
echo "Database: " . $conn->query("SELECT DATABASE() as db")->fetch_assoc()['db'] . "\n";
echo "User: " . $conn->query("SELECT USER() as user")->fetch_assoc()['user'] . "\n";
echo "Connection ID: " . $conn->thread_id . "\n";
echo "Server: " . $conn->server_info . "\n\n";

// Check if there are multiple databases with similar names
echo "=== CHECKING FOR MULTIPLE DATABASES ===\n";
$databases = $conn->query("SHOW DATABASES");
echo "Available databases:\n";
while ($row = $databases->fetch_assoc()) {
    $db_name = $row['Database'];
    if (strpos($db_name, 'cwktwqbhbk') !== false || strpos($db_name, 'textile') !== false || strpos($db_name, 'pos') !== false) {
        echo "  ★ $db_name (POTENTIAL MATCH)\n";
    } else {
        echo "    $db_name\n";
    }
}

// Check if the products table structure is consistent
echo "\n=== PRODUCTS TABLE STRUCTURE ===\n";
$structure = $conn->query("DESCRIBE products");
while ($row = $structure->fetch_assoc()) {
    echo "{$row['Field']} ({$row['Type']}) - {$row['Null']} - {$row['Key']}\n";
}

// Get detailed product statistics
echo "\n=== DETAILED PRODUCT STATISTICS ===\n";
$stats = $conn->query("
    SELECT 
        COUNT(*) as total_products,
        MAX(id) as highest_id,
        MIN(id) as lowest_id,
        MAX(created_at) as latest_created,
        MIN(created_at) as earliest_created
    FROM products
")->fetch_assoc();

foreach ($stats as $key => $value) {
    echo "$key: $value\n";
}

// Check for any gaps in ID sequence (might indicate data deletion/restoration)
echo "\n=== CHECKING ID SEQUENCE GAPS ===\n";
$gaps = $conn->query("
    SELECT 
        t1.id + 1 AS gap_start,
        MIN(t2.id) - 1 AS gap_end
    FROM products t1
    LEFT JOIN products t2 ON t1.id < t2.id
    WHERE t2.id IS NOT NULL
    GROUP BY t1.id
    HAVING gap_start < MIN(t2.id)
    LIMIT 5
");

if ($gaps->num_rows > 0) {
    echo "ID gaps found (might indicate deleted products):\n";
    while ($row = $gaps->fetch_assoc()) {
        echo "  Gap: {$row['gap_start']} to {$row['gap_end']}\n";
    }
} else {
    echo "No significant ID gaps found.\n";
}

// Test if we can see our own insertions
echo "\n=== TESTING INSERT VISIBILITY ===\n";
$test_name = "VISIBILITY_TEST_" . time();
$test_barcode = "VIS_" . time();

// Insert test product
$stmt = $conn->prepare("INSERT INTO products (name, category, price, stock, barcode) VALUES (?, 'TEST', 1.00, 1, ?)");
$stmt->bind_param('ss', $test_name, $test_barcode);
$stmt->execute();
$test_id = $conn->insert_id;
echo "Inserted test product ID: $test_id\n";

// Immediately check if we can see it
$check = $conn->prepare("SELECT * FROM products WHERE id = ?");
$check->bind_param('i', $test_id);
$check->execute();
$result = $check->get_result();

if ($result->num_rows > 0) {
    echo "✅ Can immediately see our own insert\n";
} else {
    echo "❌ CANNOT see our own insert - serious database issue!\n";
}

// Wait 2 seconds and check again (test for delayed replication)
sleep(2);
$check2 = $conn->prepare("SELECT * FROM products WHERE id = ?");
$check2->bind_param('i', $test_id);
$check2->execute();
$result2 = $check2->get_result();

if ($result2->num_rows > 0) {
    echo "✅ Still visible after 2 seconds\n";
} else {
    echo "❌ Product disappeared after 2 seconds!\n";
}

// Clean up
$conn->query("DELETE FROM products WHERE id = $test_id");

echo "\n=== RECOMMENDATION ===\n";
echo "Based on the mismatch between tracker (138/248) and investigation (137/204):\n";
echo "1. Check if your hosting has staging vs production environments\n";
echo "2. Verify all files use the same config.php\n";
echo "3. Check for database replication delays\n";
echo "4. Contact your hosting provider about multiple database instances\n";
?>