<?php
session_start();
require_once("../config/config.php");

$allowed_roles = ['store manager', 'admin', 'project manager'];
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['role'], $allowed_roles)) {
    header("Location: ../login.php");
    exit();
}

// ====================== KEY METRICS ======================
$totalDPR = $conn->query("SELECT COUNT(*) as total FROM dpr_master")->fetch_assoc()['total'];

$thisMonthDPR = $conn->query("SELECT COUNT(*) as total FROM dpr_master 
    WHERE MONTH(report_date) = MONTH(CURRENT_DATE) AND YEAR(report_date) = YEAR(CURRENT_DATE)")
    ->fetch_assoc()['total'];

$activeProjects = $conn->query("SELECT COUNT(DISTINCT project_id) as total FROM dpr_master WHERE project_id IS NOT NULL")
    ->fetch_assoc()['total'];

$avgProgress = $conn->query("
    SELECT ROUND(AVG(progress_count), 1) as avg 
    FROM (SELECT COUNT(*) as progress_count FROM dpr_progress GROUP BY dpr_id) as sub
")->fetch_assoc()['avg'] ?? 0;

// Top 5 Projects
$topProjects = $conn->query("
    SELECT p.project_name, p.project_code, COUNT(d.id) as dpr_count
    FROM dpr_master d
    JOIN project_master p ON d.project_id = p.id
    GROUP BY d.project_id
    ORDER BY dpr_count DESC LIMIT 5
");

// ====================== MONTHLY TREND (Last 12 Months) ======================
$monthlyTrend = $conn->query("
    SELECT 
        DATE_FORMAT(report_date, '%Y-%m') as month,
        COUNT(*) as dpr_count
    FROM dpr_master
    WHERE report_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(report_date, '%Y-%m')
    ORDER BY month ASC
");

$months = [];
$counts = [];
while ($row = $monthlyTrend->fetch_assoc()) {
    $months[] = date('M Y', strtotime($row['month'] . '-01'));
    $counts[] = (int)$row['dpr_count'];
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>DPR Dashboard - Analytics</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
    
    <style>
        :root {
            --primary: #a855f7;
            --accent: #c084fc;
            --glass: rgba(255, 255, 255, 0.10);
            --glass-border: rgba(255, 255, 255, 0.20);
        }
        * { margin:0; padding:0; box-sizing:border-box; }
        body {
            font-family: 'Inter', system-ui, sans-serif;
            background: linear-gradient(135deg, rgba(0,0,0,0.78), rgba(0,0,0,0.92)), url('../background.jpeg') center/cover fixed;
            color: #f1f5f9;
            padding: 20px 15px;
        }
        .container { max-width: 1350px; margin: auto; }
        .glass-container {
            background: var(--glass);
            backdrop-filter: blur(24px);
            border-radius: 32px;
            border: 1px solid var(--glass-border);
            box-shadow: 0 25px 80px rgba(0,0,0,0.65);
            overflow: hidden;
        }
        .header {
            background: linear-gradient(90deg, rgba(168,85,247,0.35), rgba(147,51,234,0.25));
            padding: 40px;
            text-align: center;
        }
        .header h1 { font-size: 2.6rem; font-weight: 700; }

        .stats-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(260px, 1fr));
            gap: 20px;
            padding: 40px;
        }
        .stat-card {
            background: rgba(255,255,255,0.08);
            border-radius: 20px;
            padding: 25px;
            border: 1px solid rgba(255,255,255,0.15);
            text-align: center;
        }
        .stat-card h3 { font-size: 2.8rem; margin: 10px 0; color: #c4b5fd; }
        .stat-card p { opacity: 0.8; font-size: 1.1rem; }

        .charts-grid {
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 25px;
            padding: 0 40px 40px;
        }
        .chart-container {
            background: rgba(0,0,0,0.25);
            border-radius: 20px;
            padding: 25px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin: 0 40px 40px;
            background: rgba(0,0,0,0.25);
            border-radius: 16px;
            overflow: hidden;
        }
        th, td { padding: 16px; text-align: left; }
        th { background: rgba(168,85,247,0.3); }

        .btn {
            padding: 12px 28px;
            background: linear-gradient(135deg, var(--primary), var(--accent));
            color: white;
            border: none;
            border-radius: 12px;
            font-weight: 700;
            cursor: pointer;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 8px;
        }

        @media (max-width: 992px) {
            .charts-grid { grid-template-columns: 1fr; }
        }
    </style>
</head>
<body>

<div class="container">
    <div class="glass-container">
        <div class="header">
            <h1><i class="fas fa-chart-line"></i> DPR Dashboard - Analytics</h1>
        </div>

        <!-- Key Stats -->
        <div class="stats-grid">
            <div class="stat-card">
                <i class="fas fa-file-alt" style="font-size:2.5rem; opacity:0.7;"></i>
                <h3><?= number_format($totalDPR) ?></h3>
                <p>Total DPRs</p>
            </div>
            <div class="stat-card">
                <i class="fas fa-calendar-month" style="font-size:2.5rem; opacity:0.7;"></i>
                <h3><?= number_format($thisMonthDPR) ?></h3>
                <p>This Month</p>
            </div>
            <div class="stat-card">
                <i class="fas fa-project-diagram" style="font-size:2.5rem; opacity:0.7;"></i>
                <h3><?= number_format($activeProjects) ?></h3>
                <p>Active Projects</p>
            </div>
            <div class="stat-card">
                <i class="fas fa-chart-bar" style="font-size:2.5rem; opacity:0.7;"></i>
                <h3><?= number_format($avgProgress, 1) ?></h3>
                <p>Avg Progress Items / DPR</p>
            </div>
        </div>

        <!-- Charts -->
        <div class="charts-grid">
            <!-- Monthly Trend Chart -->
            <div class="chart-container">
                <h3 style="margin-bottom:20px; color:#c4b5fd;">Monthly DPR Trend (Last 12 Months)</h3>
                <canvas id="monthlyTrendChart" height="200"></canvas>
            </div>

            <!-- Top Projects -->
            <div class="chart-container">
                <h3 style="margin-bottom:20px; color:#c4b5fd;">Top 5 Projects by DPR Count</h3>
                <canvas id="topProjectsChart" height="200"></canvas>
            </div>
        </div>

        <!-- Top Projects Table -->
        <h2 style="padding:0 40px 15px; color:#c4b5fd;">Top Performing Projects</h2>
        <table>
            <thead>
                <tr>
                    <th>Project Name</th>
                    <th>Project Code</th>
                    <th style="text-align:center;">Total DPRs</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $topProjects->fetch_assoc()): ?>
                <tr>
                    <td><strong><?= htmlspecialchars($row['project_name']) ?></strong></td>
                    <td><?= htmlspecialchars($row['project_code']) ?></td>
                    <td style="text-align:center; font-size:1.3rem; font-weight:700;"><?= $row['dpr_count'] ?></td>
                </tr>
                <?php endwhile; ?>
            </tbody>
        </table>

        <div style="text-align:center; padding:30px 40px;">
            <a href="add_dpr.php" class="btn">
                <i class="fas fa-plus"></i> Create New DPR
            </a>
            <a href="dpr_list.php" class="btn" style="background:rgba(255,255,255,0.15); margin-left:15px;">
                <i class="fas fa-list"></i> View All DPRs
            </a>
        </div>
    </div>
</div>

<script>
// Monthly Trend Line Chart
const monthlyCtx = document.getElementById('monthlyTrendChart');
new Chart(monthlyCtx, {
    type: 'line',
    data: {
        labels: <?= json_encode($months) ?>,
        datasets: [{
            label: 'DPR Count',
            data: <?= json_encode($counts) ?>,
            borderColor: '#c084fc',
            backgroundColor: 'rgba(192, 132, 252, 0.2)',
            borderWidth: 3,
            tension: 0.4,
            pointBackgroundColor: '#a855f7',
            pointRadius: 5
        }]
    },
    options: {
        responsive: true,
        plugins: {
            legend: { display: false },
            tooltip: { mode: 'index', intersect: false }
        },
        scales: {
            y: { beginAtZero: true, grid: { color: 'rgba(255,255,255,0.1)' } },
            x: { grid: { color: 'rgba(255,255,255,0.1)' } }
        }
    }
});

// Top Projects Bar Chart
const topCtx = document.getElementById('topProjectsChart');
new Chart(topCtx, {
    type: 'bar',
    data: {
        labels: [<?= json_encode(array_column($topProjects->fetch_all(MYSQLI_ASSOC), 'project_name')) ?>],
        datasets: [{
            label: 'DPR Count',
            data: [<?= json_encode(array_column($topProjects->fetch_all(MYSQLI_ASSOC), 'dpr_count')) ?>],
            backgroundColor: '#a855f7',
            borderColor: '#c084fc',
            borderWidth: 1,
            borderRadius: 8
        }]
    },
    options: {
        responsive: true,
        plugins: { legend: { display: false } },
        scales: {
            y: { beginAtZero: true, grid: { color: 'rgba(255,255,255,0.1)' } }
        }
    }
});
</script>

</body>
</html>

<?php $conn->close(); ?>