dashboard_module.php

<?php
require_once 'config.php';
require_once 'language_loader.php';
require_once 'src/SecurityService.php';

// Authentication Check
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
if (!isset($_SESSION['user_is_logged_in']) || $_SESSION['user_is_logged_in'] !== true) {
    header("Location: login.php");
    exit();
}

// Initialize Security Service
$sec = new SecurityService($pdo, ENCRYPTION_KEY);
$user_id = $_SESSION['user_id'];

// Fetch User Name for Page Title
$user_stmt = $pdo->prepare("SELECT full_name FROM users WHERE id = ?");
$user_stmt->execute([$user_id]);
$user_full_name = $sec->decrypt($user_stmt->fetchColumn());

// -----------------------------------------------------------------------------
// LOGIC
// -----------------------------------------------------------------------------

// 1. Get available years for filter
try {
    $stmtYears = $pdo->prepare("SELECT DISTINCT YEAR(log_date) as y FROM rt_history WHERE user_id = :user_id ORDER BY y DESC");
    $stmtYears->execute(['user_id' => $user_id]);
    $years = $stmtYears->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $e) {
    // If table doesn't exist or error, handle gracefully
    $years = [];
    $error_message = "Database error: " . $e->getMessage();
}

$selectedYear = isset($_GET['year']) ? intval($_GET['year']) : date('Y');
if (empty($years) && !in_array($selectedYear, $years)) {
    // If no data, just show selected year
    $years[] = $selectedYear;
}

// Target Hours
$targetHours = 1225;

// 2. Aggregations

// A. Total Hours (Productivity > -2)
// Productivity -2 is "Distraction" and excluded from work totals.
$sqlTotal = "
    SELECT SUM(duration_seconds) as total_seconds
    FROM rt_history
    WHERE YEAR(log_date) = :year
    AND productivity > -2
    AND user_id = :user_id
";
$stmtTotal = $pdo->prepare($sqlTotal);
$stmtTotal->execute(['year' => $selectedYear, 'user_id' => $user_id]);
$totalSeconds = $stmtTotal->fetchColumn() ?: 0;
$totalHours = $totalSeconds / 3600;

$remainingHours = max(0, $targetHours - $totalHours);
$progressPercent = min(100, ($totalHours / $targetHours) * 100);

// B. Monthly Breakdown (Bar Chart)
$sqlMonthly = "
    SELECT MONTH(log_date) as m, SUM(duration_seconds) as seconds
    FROM rt_history
    WHERE YEAR(log_date) = :year
    AND productivity > -2
    AND user_id = :user_id
    GROUP BY m
    ORDER BY m ASC
";
$stmtMonthly = $pdo->prepare($sqlMonthly);
$stmtMonthly->execute(['year' => $selectedYear, 'user_id' => $user_id]);
$monthlyDataRaw = $stmtMonthly->fetchAll(PDO::FETCH_KEY_PAIR); // [Month => Seconds]

// Format for Chart.js (Ensure all 12 months exist)
$monthsLabels = [];
$monthlyDataHours = [];
for ($i = 1; $i <= 12; $i++) {
    $dateObj = DateTime::createFromFormat('!m', $i);
    $monthsLabels[] = $dateObj->format('F'); // Full month name
    $sec = isset($monthlyDataRaw[$i]) ? $monthlyDataRaw[$i] : 0;
    $monthlyDataHours[] = round($sec / 3600, 1);
}

// C. Work Type Split (Focus vs Admin) (Doughnut Chart)
// Focus: Prod > 0
// Admin: Prod <= 0 AND Prod > -2
$sqlType = "
    SELECT
        CASE WHEN productivity > 0 THEN 'Focus' ELSE 'Admin' END as work_type,
        SUM(duration_seconds) as seconds
    FROM rt_history
    WHERE YEAR(log_date) = :year
    AND productivity > -2
    AND user_id = :user_id
    GROUP BY work_type
";
$stmtType = $pdo->prepare($sqlType);
$stmtType->execute(['year' => $selectedYear, 'user_id' => $user_id]);
$typeDataRaw = $stmtType->fetchAll(PDO::FETCH_KEY_PAIR); // ['Focus' => 123, 'Admin' => 456]

$focusSeconds = $typeDataRaw['Focus'] ?? 0;
$adminSeconds = $typeDataRaw['Admin'] ?? 0;
$totalWorkSeconds = $focusSeconds + $adminSeconds;

// Avoid division by zero
$focusPercent = $totalWorkSeconds > 0 ? round(($focusSeconds / $totalWorkSeconds) * 100, 1) : 0;
$adminPercent = $totalWorkSeconds > 0 ? round(($adminSeconds / $totalWorkSeconds) * 100, 1) : 0;

// D. Top 25 Applications (Table)
$sqlTopApps = "
    SELECT activity, category, SUM(duration_seconds) as seconds
    FROM rt_history
    WHERE YEAR(log_date) = :year
    AND productivity > -2
    AND user_id = :user_id
    GROUP BY activity, category
    ORDER BY seconds DESC
    LIMIT 25
";
$stmtTopApps = $pdo->prepare($sqlTopApps);
$stmtTopApps->execute(['year' => $selectedYear, 'user_id' => $user_id]);
$topApps = $stmtTopApps->fetchAll();

// Helper for H:i format
function formatSecondsToHi($seconds) {
    $hours = floor($seconds / 3600);
    $mins = floor(($seconds % 3600) / 60);
    return sprintf("%02d:%02d", $hours, $mins);
}
?>
<!DOCTYPE html>
<html lang="nl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo htmlspecialchars($lang['nav_timesheet']) . ' - ' . htmlspecialchars($user_full_name); ?></title>
    <link rel="apple-touch-icon" sizes="180x180" href="apple-touch-icon.png">
    <link rel="icon" type="image/png" sizes="32x32" href="favicon-32x32.png">
    <link rel="icon" type="image/png" sizes="16x16" href="favicon-16x16.png">
    <link rel="manifest" href="site.webmanifest">
    <link rel="stylesheet" href="style.css">
</head>
<body class="bg-slate-50 text-slate-800 flex flex-col min-h-screen">
    <?php require_once 'header.inc.php'; ?>

    <!--
        VIEW
        Uses Tailwind CSS classes consistent with style.css
    -->
    <div class="w-full max-w-[1600px] mx-auto px-4 mt-8 pb-12 flex-1">

        <!-- Header & Filter -->
        <div class="flex flex-col md:flex-row justify-between items-center mb-8">
            <h1 class="text-3xl font-bold text-gray-800">
                <?php echo isset($lang['nav_timesheet']) ? $lang['nav_timesheet'] : 'Timesheet'; ?>
                <span class="text-gray-500 text-xl font-normal">/ <?php echo $selectedYear; ?></span>
            </h1>

            <form action="dashboard_module.php" method="GET" class="mt-4 md:mt-0">
                <label for="year" class="mr-2 font-medium text-gray-700">Year:</label>
                <select name="year" id="year" onchange="this.form.submit()" class="border border-gray-300 rounded-md px-3 py-1 focus:outline-none focus:ring-2 focus:ring-blue-500">
                    <?php foreach($years as $y): ?>
                        <option value="<?php echo $y; ?>" <?php echo $y == $selectedYear ? 'selected' : ''; ?>>
                            <?php echo $y; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </form>
        </div>

        <?php if (isset($error_message)): ?>
            <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded relative mb-6" role="alert">
                <strong class="font-bold">Error:</strong>
                <span class="block sm:inline"><?php echo htmlspecialchars($error_message); ?></span>
            </div>
        <?php endif; ?>

        <!-- KPI Cards -->
        <div class="grid grid-cols-1 md:grid-cols-3 gap-6 mb-8">
            <!-- Total Hours -->
            <div class="bg-white rounded-xl shadow-md p-6 border-l-4 border-blue-500">
                <div class="text-gray-500 text-sm font-uppercase tracking-wide mb-1">Total Hours</div>
                <div class="text-3xl font-bold text-gray-800"><?php echo number_format($totalHours, 1); ?> <span class="text-lg text-gray-400 font-normal">hrs</span></div>
                <div class="mt-2 text-sm text-gray-600">
                    Focus: <span class="font-semibold text-green-600"><?php echo number_format($focusSeconds/3600, 1); ?>h</span> |
                    Admin: <span class="font-semibold text-yellow-600"><?php echo number_format($adminSeconds/3600, 1); ?>h</span>
                </div>
            </div>

            <!-- Remaining -->
            <div class="bg-white rounded-xl shadow-md p-6 border-l-4 <?php echo $remainingHours == 0 ? 'border-green-500' : 'border-red-500'; ?>">
                <div class="text-gray-500 text-sm font-uppercase tracking-wide mb-1">Remaining for Target</div>
                <div class="text-3xl font-bold text-gray-800"><?php echo number_format($remainingHours, 1); ?> <span class="text-lg text-gray-400 font-normal">hrs</span></div>
                <div class="mt-2 text-sm text-gray-600">
                    Target: <?php echo $targetHours; ?> hours/year (Urencriterium)
                </div>
            </div>

            <!-- Progress -->
            <div class="bg-white rounded-xl shadow-md p-6 border-l-4 border-purple-500">
                <div class="text-gray-500 text-sm font-uppercase tracking-wide mb-1">Progress</div>
                <div class="flex items-center">
                    <div class="text-3xl font-bold text-gray-800 mr-4"><?php echo number_format($progressPercent, 1); ?>%</div>
                    <div class="w-full bg-gray-200 rounded-full h-2.5 dark:bg-gray-700">
                        <div class="bg-purple-600 h-2.5 rounded-full" style="width: <?php echo $progressPercent; ?>%"></div>
                    </div>
                </div>
                <div class="mt-2 text-sm text-gray-600">
                    <?php echo ($progressPercent >= 100) ? 'Goal Reached!' : 'Keep going!'; ?>
                </div>
            </div>
        </div>

        <!-- Charts Row -->
        <div class="grid grid-cols-1 lg:grid-cols-3 gap-8 mb-8">

            <!-- Monthly Chart (2/3 width) -->
            <div class="lg:col-span-2 bg-white rounded-xl shadow-md p-6">
                <h3 class="text-lg font-bold text-gray-700 mb-4">Total Hours per Month</h3>
                <div class="relative h-64 w-full">
                    <canvas id="monthlyChart"></canvas>
                </div>
            </div>

            <!-- Type Chart (1/3 width) -->
            <div class="bg-white rounded-xl shadow-md p-6">
                <h3 class="text-lg font-bold text-gray-700 mb-4">Work Type Split</h3>
                <div class="relative h-64 w-full flex justify-center">
                    <canvas id="typeChart"></canvas>
                </div>
            </div>
        </div>

        <!-- Top Applications Table -->
        <div class="bg-white rounded-xl shadow-md p-6">
            <h3 class="text-lg font-bold text-gray-700 mb-4">Top 25 Applications / Websites</h3>
            <div class="overflow-x-auto">
                <table class="min-w-full leading-normal">
                    <thead>
                        <tr>
                            <th class="px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">
                                Rank
                            </th>
                            <th class="px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">
                                Activity
                            </th>
                            <th class="px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider">
                                Category
                            </th>
                            <th class="px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-right text-xs font-semibold text-gray-600 uppercase tracking-wider">
                                Duration (H:i)
                            </th>
                             <th class="px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-right text-xs font-semibold text-gray-600 uppercase tracking-wider">
                                Hours
                            </th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (count($topApps) > 0): ?>
                            <?php $rank = 1; foreach ($topApps as $app): ?>
                            <tr>
                                <td class="px-5 py-2 border-b border-gray-200 bg-white text-sm text-gray-500">
                                    <?php echo $rank++; ?>
                                </td>
                                <td class="px-5 py-2 border-b border-gray-200 bg-white text-sm font-medium text-gray-900">
                                    <?php echo htmlspecialchars($app['activity']); ?>
                                </td>
                                <td class="px-5 py-2 border-b border-gray-200 bg-white text-sm text-gray-500">
                                    <span class="relative inline-block px-3 py-1 font-semibold text-blue-900 leading-tight">
                                        <span aria-hidden="true" class="absolute inset-0 bg-blue-200 opacity-50 rounded-full"></span>
                                        <span class="relative"><?php echo htmlspecialchars($app['category']); ?></span>
                                    </span>
                                </td>
                                <td class="px-5 py-2 border-b border-gray-200 bg-white text-sm text-right text-gray-900 font-mono">
                                    <?php echo formatSecondsToHi($app['seconds']); ?>
                                </td>
                                <td class="px-5 py-2 border-b border-gray-200 bg-white text-sm text-right text-gray-500">
                                    <?php echo number_format($app['seconds'] / 3600, 2); ?>h
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="5" class="px-5 py-4 border-b border-gray-200 bg-white text-sm text-center text-gray-500">
                                    No data found for this year.
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Chart.js Scripts -->
    <script>
        // Data from PHP
        const monthsLabels = <?php echo json_encode($monthsLabels); ?>;
        const monthlyData = <?php echo json_encode($monthlyDataHours); ?>;
        const focusHours = <?php echo number_format($focusSeconds / 3600, 2); ?>;
        const adminHours = <?php echo number_format($adminSeconds / 3600, 2); ?>;

        // 1. Monthly Bar Chart
        const ctxMonthly = document.getElementById('monthlyChart').getContext('2d');
        new Chart(ctxMonthly, {
            type: 'bar',
            data: {
                labels: monthsLabels,
                datasets: [{
                    label: 'Hours Worked',
                    data: monthlyData,
                    backgroundColor: 'rgba(59, 130, 246, 0.7)', // Blue-500
                    borderColor: 'rgba(59, 130, 246, 1)',
                    borderWidth: 1,
                    borderRadius: 4
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: { display: false },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.raw + ' hours';
                            }
                        }
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        title: { display: true, text: 'Hours' }
                    }
                }
            }
        });

        // 2. Work Type Doughnut Chart
        const ctxType = document.getElementById('typeChart').getContext('2d');
        new Chart(ctxType, {
            type: 'doughnut',
            data: {
                labels: ['Focus Work', 'General/Admin'],
                datasets: [{
                    data: [focusHours, adminHours],
                    backgroundColor: [
                        'rgba(16, 185, 129, 0.8)', // Green-500
                        'rgba(245, 158, 11, 0.8)'  // Amber-500
                    ],
                    borderColor: [
                        'rgba(16, 185, 129, 1)',
                        'rgba(245, 158, 11, 1)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: { position: 'bottom' },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                let label = context.label || '';
                                let value = context.raw || 0;
                                let total = <?php echo ($totalHours > 0 ? $totalHours : 1); ?>;
                                let pct = Math.round((value / total) * 100);
                                return label + ': ' + value + 'h (' + pct + '%)';
                            }
                        }
                    }
                }
            }
        });
    </script>

    <?php require_once 'footer.inc.php'; ?>
    <script src="script.js"></script>
</body>
</html>