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

// This script will log database operations to track what's happening to your data
?>
<!DOCTYPE html>
<html>
<head>
    <title>Database Persistence Tracker</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
    <div class="container mt-4">
        <div class="card">
            <div class="card-header bg-primary text-white">
                <h5><i class="fas fa-database"></i> Database Persistence Investigation</h5>
            </div>
            <div class="card-body">
                <?php
                echo "<h6>Current Date/Time: " . date('Y-m-d H:i:s') . "</h6>";
                echo "<p><strong>Database:</strong> {$conn->get_server_info()}</p>";
                echo "<p><strong>Connected to:</strong> " . $db . "</p>";
                
                // Check current products
                $products = $conn->query("SELECT COUNT(*) as total FROM products")->fetch_assoc();
                echo "<p><strong>Current Products:</strong> {$products['total']}</p>";
                
                // Check products by date
                echo "<h6>Products by Creation Date:</h6>";
                $by_date = $conn->query("
                    SELECT DATE(created_at) as date, COUNT(*) as count 
                    FROM products 
                    GROUP BY DATE(created_at) 
                    ORDER BY date DESC 
                    LIMIT 10
                ");
                
                echo "<table class='table table-sm'>";
                echo "<tr><th>Date</th><th>Products Added</th></tr>";
                while ($row = $by_date->fetch_assoc()) {
                    $is_today = ($row['date'] == date('Y-m-d'));
                    $class = $is_today ? 'table-success' : '';
                    echo "<tr class='$class'><td>{$row['date']}</td><td>{$row['count']}</td></tr>";
                }
                echo "</table>";
                
                // Check recent products (last 7 days)
                echo "<h6>Recent Products (Last 7 days):</h6>";
                $recent = $conn->query("
                    SELECT id, name, barcode, DATE(created_at) as date, TIME(created_at) as time
                    FROM products 
                    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                    ORDER BY created_at DESC
                ");
                
                if ($recent->num_rows > 0) {
                    echo "<table class='table table-sm'>";
                    echo "<tr><th>ID</th><th>Name</th><th>Barcode</th><th>Date</th><th>Time</th></tr>";
                    while ($row = $recent->fetch_assoc()) {
                        $is_today = ($row['date'] == date('Y-m-d'));
                        $class = $is_today ? 'table-success' : '';
                        echo "<tr class='$class'>";
                        echo "<td>{$row['id']}</td>";
                        echo "<td>" . htmlspecialchars($row['name']) . "</td>";
                        echo "<td>{$row['barcode']}</td>";
                        echo "<td>{$row['date']}</td>";
                        echo "<td>{$row['time']}</td>";
                        echo "</tr>";
                    }
                    echo "</table>";
                } else {
                    echo "<p class='text-warning'>No products added in the last 7 days!</p>";
                }
                
                // Database status checks
                echo "<h6>Database Status:</h6>";
                echo "<ul>";
                
                // Check autocommit
                $autocommit = $conn->query("SELECT @@autocommit")->fetch_row()[0];
                echo "<li>Autocommit: " . ($autocommit ? "ENABLED" : "DISABLED") . "</li>";
                
                // Check table engine
                $engine = $conn->query("SHOW TABLE STATUS WHERE Name = 'products'")->fetch_assoc()['Engine'];
                echo "<li>Products table engine: $engine</li>";
                
                // Check if we're in a transaction
                $in_transaction = $conn->query("SELECT @@session.transaction_isolation")->fetch_row()[0];
                echo "<li>Transaction isolation: $in_transaction</li>";
                
                echo "</ul>";
                
                // Log this check
                $log_entry = date('Y-m-d H:i:s') . " - Products: {$products['total']}\n";
                file_put_contents('database_check.log', $log_entry, FILE_APPEND | LOCK_EX);
                ?>
                
                <div class="alert alert-info">
                    <h6>What to do next:</h6>
                    <ol>
                        <li>Bookmark this page and check it daily</li>
                        <li>Add a new product through the normal interface</li>
                        <li>Check this page immediately after adding</li>
                        <li>Check again tomorrow to see if it's still there</li>
                        <li>The log file will track product counts over time</li>
                    </ol>
                </div>
                
                <a href="products/" class="btn btn-primary">Add New Product</a>
                <a href="?" class="btn btn-secondary">Refresh Check</a>
            </div>
        </div>
    </div>
</body>
</html>