<?php
/**
 * Verification Script: Check Streak Column Status
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once 'includes/config.php';

?>
<!DOCTYPE html>
<html>
<head>
    <title>Streak Column Status Check</title>
    <style>
        body { font-family: system-ui; padding: 20px; background: #f5f5f5; }
        .container { max-width: 800px; margin: 0 auto; background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .success { color: #10b981; font-weight: bold; }
        .error { color: #ef4444; font-weight: bold; }
        .info { background: #e0f2fe; padding: 15px; border-radius: 6px; margin: 10px 0; }
        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
        th, td { padding: 10px; text-align: left; border-bottom: 1px solid #ddd; }
        th { background: #f8fafc; font-weight: 600; }
        code { background: #f1f5f9; padding: 2px 6px; border-radius: 4px; font-size: 0.9em; }
        .btn { display: inline-block; padding: 10px 20px; background: #3b82f6; color: white; text-decoration: none; border-radius: 6px; margin: 10px 5px; }
        .btn:hover { background: #2563eb; }
    </style>
</head>
<body>
    <div class="container">
        <h1>🔍 Streak Tracking Status</h1>
        
<?php
try {
    $pdo = getDBConnection();
    
    echo "<h2>1. Database Column Check</h2>";
    
    // Check if column exists
    $checkStmt = $pdo->query("
        SELECT 
            COLUMN_NAME,
            DATA_TYPE,
            IS_NULLABLE,
            COLUMN_DEFAULT,
            COLUMN_COMMENT
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'user_stats' 
        AND COLUMN_NAME = 'streak_updated_date'
    ");
    $column = $checkStmt->fetch(PDO::FETCH_ASSOC);
    
    if ($column) {
        echo "<p class='success'>✓ Column 'streak_updated_date' EXISTS in user_stats table</p>";
        echo "<table>";
        echo "<tr><th>Property</th><th>Value</th></tr>";
        foreach ($column as $key => $value) {
            echo "<tr><td><strong>$key</strong></td><td>" . htmlspecialchars($value ?? 'NULL') . "</td></tr>";
        }
        echo "</table>";
    } else {
        echo "<p class='error'>✗ Column 'streak_updated_date' DOES NOT EXIST</p>";
        echo "<p>You need to add it with this SQL:</p>";
        echo "<pre>ALTER TABLE user_stats ADD COLUMN streak_updated_date DATE DEFAULT NULL;</pre>";
    }
    
    echo "<h2>2. User Stats Overview</h2>";
    
    // Get all users with their streak data
    $usersStmt = $pdo->query("
        SELECT 
            u.id,
            u.phone,
            u.full_name,
            us.consecutive_days,
            us.streak_updated_date,
            us.points,
            us.today_completed
        FROM users u
        LEFT JOIN user_stats us ON u.id = us.user_id
        ORDER BY us.consecutive_days DESC
        LIMIT 10
    ");
    $users = $usersStmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($users)) {
        echo "<table>";
        echo "<tr>";
        echo "<th>User ID</th>";
        echo "<th>Name</th>";
        echo "<th>Streak Days</th>";
        echo "<th>Last Updated</th>";
        echo "<th>Points</th>";
        echo "<th>Today Tasks</th>";
        echo "</tr>";
        
        foreach ($users as $user) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($user['id']) . "</td>";
            echo "<td>" . htmlspecialchars($user['full_name'] ?? 'N/A') . "</td>";
            echo "<td><strong>" . htmlspecialchars($user['consecutive_days'] ?? '0') . "</strong></td>";
            echo "<td>" . htmlspecialchars($user['streak_updated_date'] ?? 'Never') . "</td>";
            echo "<td>" . htmlspecialchars($user['points'] ?? '0') . "</td>";
            echo "<td>" . htmlspecialchars($user['today_completed'] ?? '0') . "</td>";
            echo "</tr>";
        }
        
        echo "</table>";
    } else {
        echo "<p>No users found in the database.</p>";
    }
    
    echo "<h2>3. Status Summary</h2>";
    echo "<div class='info'>";
    echo "<p><strong>✅ Migration Status:</strong> Column exists, ready to use!</p>";
    echo "<p><strong>📋 How it works:</strong></p>";
    echo "<ul>";
    echo "<li>When a user completes their last daily task (8/8), the streak is updated in <code>save_recording.php</code></li>";
    echo "<li>The <code>streak_updated_date</code> column prevents duplicate increments on page reloads</li>";
    echo "<li>If last update was yesterday, streak increments by 1</li>";
    echo "<li>If last update was older than yesterday, streak resets to 1</li>";
    echo "<li>If last update was today, no change (prevents duplicates)</li>";
    echo "</ul>";
    echo "</div>";
    
    echo "<h2>4. Next Steps</h2>";
    echo "<p>The fix is already in place! Test it by:</p>";
    echo "<ol>";
    echo "<li>Complete all 8 daily tasks</li>";
    echo "<li>Observe the streak counter after task 8/8</li>";
    echo "<li>Reload the page multiple times - streak should stay the same</li>";
    echo "<li>Come back tomorrow and complete tasks again - streak should increment by 1</li>";
    echo "</ol>";
    
} catch (PDOException $e) {
    echo "<h2 class='error'>Database Error</h2>";
    echo "<pre style='color: red;'>";
    echo htmlspecialchars($e->getMessage());
    echo "</pre>";
}
?>
        
        <div style="margin-top: 30px; padding-top: 20px; border-top: 2px solid #e5e7eb;">
            <a href="chat.php" class="btn">← Back to Chat</a>
            <a href="daily_tasks.php" class="btn" style="background: #10b981;">View Daily Tasks</a>
        </div>
    </div>
</body>
</html>
