<?php
/**
 * Task Counter Diagnostic Tool
 * This script helps diagnose issues with the task counter
 */

require_once 'includes/auth.php';
requireLogin();

$pdo = getDBConnection();
$userId = $_SESSION['user_id'];

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Task Counter Debug</title>
    <style>
        body {
            font-family: 'Courier New', monospace;
            background: #1a1a1a;
            color: #00ff00;
            padding: 20px;
            line-height: 1.6;
        }
        .section {
            background: #2a2a2a;
            border: 1px solid #00ff00;
            padding: 20px;
            margin: 20px 0;
            border-radius: 5px;
        }
        h1, h2 {
            color: #00ff00;
            text-shadow: 0 0 10px #00ff00;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin: 10px 0;
        }
        th, td {
            border: 1px solid #00ff00;
            padding: 10px;
            text-align: left;
        }
        th {
            background: #333;
        }
        .error {
            color: #ff0000;
            font-weight: bold;
        }
        .success {
            color: #00ff00;
            font-weight: bold;
        }
        .warning {
            color: #ffaa00;
            font-weight: bold;
        }
        .info {
            color: #00aaff;
        }
        pre {
            background: #000;
            padding: 10px;
            border-radius: 5px;
            overflow-x: auto;
        }
    </style>
</head>
<body>
    <h1>🔍 Task Counter Diagnostic Report</h1>
    <p>User ID: <?= $userId ?></p>
    <p>Date: <?= date('Y-m-d H:i:s') ?></p>
    
    <?php
    // 1. Check table structures
    echo '<div class="section">';
    echo '<h2>1. Table Structure Check</h2>';
    
    // Check user_stats columns
    $stmt = $pdo->query("SHOW COLUMNS FROM user_stats");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo '<h3>user_stats columns:</h3>';
    echo '<table><tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>';
    foreach ($columns as $col) {
        echo '<tr>';
        echo '<td>' . htmlspecialchars($col['Field']) . '</td>';
        echo '<td>' . htmlspecialchars($col['Type']) . '</td>';
        echo '<td>' . htmlspecialchars($col['Null']) . '</td>';
        echo '<td>' . htmlspecialchars($col['Key']) . '</td>';
        echo '<td>' . htmlspecialchars($col['Default'] ?? 'NULL') . '</td>';
        echo '</tr>';
    }
    echo '</table>';
    
    $hasLastUpdated = false;
    $hasLastParticipation = false;
    foreach ($columns as $col) {
        if ($col['Field'] === 'last_updated') $hasLastUpdated = true;
        if ($col['Field'] === 'last_participation') $hasLastParticipation = true;
    }
    
    echo '<p>';
    echo $hasLastUpdated ? '<span class="success">✓ last_updated column exists</span>' : '<span class="error">✗ last_updated column missing</span>';
    echo '<br>';
    echo $hasLastParticipation ? '<span class="success">✓ last_participation column exists</span>' : '<span class="error">✗ last_participation column missing</span>';
    echo '</p>';
    
    // Check recordings columns
    $stmt = $pdo->query("SHOW COLUMNS FROM recordings");
    $recColumns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $hasFilepath = false;
    $hasFilePath = false;
    foreach ($recColumns as $col) {
        if ($col['Field'] === 'filepath') $hasFilepath = true;
        if ($col['Field'] === 'file_path') $hasFilePath = true;
    }
    
    echo '<h3>recordings path column:</h3>';
    echo '<p>';
    echo $hasFilepath ? '<span class="success">✓ filepath column exists</span>' : '<span class="warning">⚠ filepath column missing</span>';
    echo '<br>';
    echo $hasFilePath ? '<span class="success">✓ file_path column exists</span>' : '<span class="warning">⚠ file_path column missing</span>';
    echo '</p>';
    
    echo '</div>';
    
    // 2. Current user stats
    echo '<div class="section">';
    echo '<h2>2. Current User Stats</h2>';
    $stmt = $pdo->prepare("SELECT * FROM user_stats WHERE user_id = ?");
    $stmt->execute([$userId]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($stats) {
        echo '<table>';
        foreach ($stats as $key => $value) {
            echo '<tr><th>' . htmlspecialchars($key) . '</th><td>' . htmlspecialchars($value ?? 'NULL') . '</td></tr>';
        }
        echo '</table>';
    } else {
        echo '<p class="warning">No stats found for this user</p>';
    }
    echo '</div>';
    
    // 3. Today's tasks
    echo '<div class="section">';
    echo '<h2>3. Today\'s Tasks</h2>';
    $stmt = $pdo->prepare("
        SELECT ut.*, t.title, t.instruction_text, c.name as category_name
        FROM user_tasks ut
        JOIN tasks t ON ut.task_id = t.id
        JOIN task_categories c ON t.category_id = c.id
        WHERE ut.user_id = ? 
        AND DATE(ut.assigned_date) = CURDATE()
        ORDER BY ut.id
    ");
    $stmt->execute([$userId]);
    $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo '<p>Total tasks assigned today: <span class="info">' . count($tasks) . '</span></p>';
    
    if (!empty($tasks)) {
        echo '<table>';
        echo '<tr><th>ID</th><th>Category</th><th>Status</th><th>Assigned</th><th>Completed</th></tr>';
        foreach ($tasks as $task) {
            $statusClass = $task['status'] === 'completed' ? 'success' : 'warning';
            echo '<tr>';
            echo '<td>' . $task['id'] . '</td>';
            echo '<td>' . htmlspecialchars($task['category_name']) . '</td>';
            echo '<td class="' . $statusClass . '">' . $task['status'] . '</td>';
            echo '<td>' . $task['assigned_date'] . '</td>';
            echo '<td>' . ($task['completed_at'] ?? 'Not completed') . '</td>';
            echo '</tr>';
        }
        echo '</table>';
    } else {
        echo '<p class="warning">No tasks assigned for today</p>';
    }
    echo '</div>';
    
    // 4. Count verification
    echo '<div class="section">';
    echo '<h2>4. Count Verification</h2>';
    
    // Database counts
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
            SUM(CASE WHEN status = 'assigned' THEN 1 ELSE 0 END) as assigned
        FROM user_tasks 
        WHERE user_id = ? 
        AND DATE(assigned_date) = CURDATE()
    ");
    $stmt->execute([$userId]);
    $counts = $stmt->fetch(PDO::FETCH_ASSOC);
    
    echo '<table>';
    echo '<tr><th>Metric</th><th>Database Value</th><th>user_stats Value</th><th>Status</th></tr>';
    
    $dbCompleted = (int)$counts['completed'];
    $statsCompleted = (int)($stats['today_completed'] ?? 0);
    $match = $dbCompleted === $statsCompleted;
    
    echo '<tr>';
    echo '<td>Completed Tasks</td>';
    echo '<td class="info">' . $dbCompleted . '</td>';
    echo '<td class="info">' . $statsCompleted . '</td>';
    echo '<td class="' . ($match ? 'success' : 'error') . '">' . ($match ? '✓ Match' : '✗ Mismatch') . '</td>';
    echo '</tr>';
    
    echo '<tr>';
    echo '<td>Total Tasks</td>';
    echo '<td class="info">' . $counts['total'] . '</td>';
    echo '<td class="info">-</td>';
    echo '<td>-</td>';
    echo '</tr>';
    
    echo '<tr>';
    echo '<td>Pending Tasks</td>';
    echo '<td class="info">' . $counts['assigned'] . '</td>';
    echo '<td class="info">-</td>';
    echo '<td>-</td>';
    echo '</tr>';
    
    echo '</table>';
    
    if (!$match) {
        echo '<p class="error">⚠️ MISMATCH DETECTED: Database shows ' . $dbCompleted . ' completed tasks, but user_stats shows ' . $statsCompleted . '</p>';
        echo '<p class="warning">This may cause incorrect counter display.</p>';
    }
    echo '</div>';
    
    // 5. Recent recordings
    echo '<div class="section">';
    echo '<h2>5. Recent Recordings</h2>';
    $stmt = $pdo->prepare("
        SELECT r.*, t.title
        FROM recordings r
        LEFT JOIN tasks t ON r.task_id = t.id
        WHERE r.user_id = ?
        ORDER BY r.created_at DESC
        LIMIT 10
    ");
    $stmt->execute([$userId]);
    $recordings = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($recordings)) {
        echo '<table>';
        echo '<tr><th>ID</th><th>Task</th><th>Status</th><th>Created At</th></tr>';
        foreach ($recordings as $rec) {
            echo '<tr>';
            echo '<td>' . $rec['id'] . '</td>';
            echo '<td>' . htmlspecialchars($rec['title'] ?? 'N/A') . '</td>';
            echo '<td>' . $rec['status'] . '</td>';
            echo '<td>' . $rec['created_at'] . '</td>';
            echo '</tr>';
        }
        echo '</table>';
    } else {
        echo '<p class="warning">No recordings found</p>';
    }
    echo '</div>';
    
    // 6. Recommendations
    echo '<div class="section">';
    echo '<h2>6. Recommendations</h2>';
    
    if (!$hasLastUpdated) {
        echo '<p class="error">⚠️ Run the fix_task_counter.sql script to add the last_updated column</p>';
    }
    
    if (!$match) {
        echo '<p class="error">⚠️ Run the following SQL to fix the mismatch:</p>';
        echo '<pre>UPDATE user_stats 
SET today_completed = ' . $dbCompleted . ' 
WHERE user_id = ' . $userId . ';</pre>';
    }
    
    if (!$hasFilepath && !$hasFilePath) {
        echo '<p class="error">⚠️ recordings table needs either filepath or file_path column</p>';
    }
    
    if ($hasLastUpdated && $match && ($hasFilepath || $hasFilePath)) {
        echo '<p class="success">✓ All checks passed! The system should be working correctly.</p>';
    }
    
    echo '</div>';
    ?>
    
    <div class="section">
        <h2>7. Quick Fix</h2>
        <p>Click the button below to recalculate your task counter:</p>
        <form method="POST" action="">
            <button type="submit" name="recalculate" style="background: #00ff00; color: #000; padding: 10px 20px; border: none; border-radius: 5px; cursor: pointer; font-weight: bold;">
                Recalculate Task Counter
            </button>
        </form>
        
        <?php
        if (isset($_POST['recalculate'])) {
            try {
                // Recalculate today_completed
                $stmt = $pdo->prepare("
                    UPDATE user_stats 
                    SET today_completed = (
                        SELECT COUNT(*) 
                        FROM user_tasks 
                        WHERE user_id = ? 
                        AND status = 'completed' 
                        AND DATE(completed_at) = CURDATE()
                    )
                    WHERE user_id = ?
                ");
                $stmt->execute([$userId, $userId]);
                
                echo '<p class="success">✓ Counter recalculated successfully! Please refresh this page.</p>';
                echo '<meta http-equiv="refresh" content="2">';
            } catch (Exception $e) {
                echo '<p class="error">✗ Error: ' . htmlspecialchars($e->getMessage()) . '</p>';
            }
        }
        ?>
    </div>
    
    <div class="section">
        <p><a href="chat.php" style="color: #00ff00;">← Back to Chat</a></p>
    </div>
</body>
</html>
