<?php
// Include the database connection
include 'db_connection.php';

// Fetch data for analytics
$totalQuery = "SELECT COUNT(*) as count, SUM(amount) as total FROM assets";
$totalResult = $conn->query($totalQuery);
$totalData = $totalResult->fetch_assoc();

$monthlyQuery = "SELECT 
    YEAR(date) as year, 
    MONTH(date) as month, 
    COUNT(*) as count, 
    SUM(amount) as total 
    FROM assets 
    GROUP BY YEAR(date), MONTH(date) 
    ORDER BY year DESC, month DESC 
    LIMIT 6";
$monthlyResult = $conn->query($monthlyQuery);

$recentQuery = "SELECT * FROM assets ORDER BY date DESC LIMIT 5";
$recentResult = $conn->query($recentQuery);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Asset Analytics</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        :root {
            --primary: #3a57e8;
            --secondary: #6c757d;
            --success: #1cbb8c;
            --info: #17a2b8;
            --warning: #f5b759;
            --danger: #fc5b69;
        }
        
        body {
            font-family: 'Poppins', sans-serif;
            background-color: #f5f7ff;
            color: #333;
        }
        
        .dashboard-container {
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
        }
        
        .header {
            background: linear-gradient(135deg, var(--primary) 0%, #2039c5 100%);
            color: white;
            padding: 20px;
            border-radius: 10px;
            margin-bottom: 25px;
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.1);
        }
        
        .card {
            border-radius: 10px;
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.08);
            margin-bottom: 20px;
            border: none;
        }
        
        .card-header {
            background: linear-gradient(135deg, var(--primary) 0%, #2039c5 100%);
            color: white;
            border-radius: 10px 10px 0 0 !important;
            font-weight: 600;
            padding: 15px 20px;
        }
        
        .stats-card {
            text-align: center;
            padding: 20px;
            border-radius: 10px;
            background: white;
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.08);
            margin-bottom: 20px;
        }
        
        .stats-number {
            font-size: 24px;
            font-weight: 700;
            color: var(--primary);
        }
        
        .stats-label {
            color: var(--secondary);
            font-size: 14px;
        }
        
        .chart-container {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.08);
            margin-bottom: 20px;
        }
    </style>
</head>
<body>
    <div class="dashboard-container">
        <!-- Header -->
        <div class="header">
            <div class="row align-items-center">
                <div class="col-md-8">
                    <h2><i class="fas fa-chart-line me-2"></i>Asset Analytics</h2>
                    <p class="mb-0">Insights and trends for your assets</p>
                </div>
                <div class="col-md-4 text-md-end">
                    <a href="add_asset.php" class="btn btn-light"><i class="fas fa-arrow-left me-1"></i> Back to Assets</a>
                </div>
            </div>
        </div>
        
        <!-- Stats Cards -->
        <div class="row">
            <div class="col-md-4">
                <div class="stats-card">
                    <i class="fas fa-cubes fa-2x text-primary mb-3"></i>
                    <div class="stats-number"><?php echo number_format($totalData['count']); ?></div>
                    <div class="stats-label">Total Assets</div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stats-card">
                    <i class="fas fa-dollar-sign fa-2x text-primary mb-3"></i>
                    <div class="stats-number">$<?php echo number_format($totalData['total'], 2); ?></div>
                    <div class="stats-label">Total Value</div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="stats-card">
                    <i class="fas fa-calendar-alt fa-2x text-primary mb-3"></i>
                    <div class="stats-number">$<?php 
                        $avg = $totalData['count'] > 0 ? $totalData['total'] / $totalData['count'] : 0;
                        echo number_format($avg, 2); 
                    ?></div>
                    <div class="stats-label">Average Value per Asset</div>
                </div>
            </div>
        </div>
        
        <!-- Charts -->
        <div class="row">
            <div class="col-md-8">
                <div class="chart-container">
                    <h4 class="mb-4">Monthly Asset Value</h4>
                    <canvas id="monthlyChart" height="250"></canvas>
                </div>
            </div>
            <div class="col-md-4">
                <div class="chart-container">
                    <h4 class="mb-4">Recent Assets</h4>
                    <ul class="list-group">
                        <?php while ($row = $recentResult->fetch_assoc()): ?>
                        <li class="list-group-item d-flex justify-content-between align-items-center">
                            <?php echo htmlspecialchars($row['description']); ?>
                            <span class="badge bg-primary rounded-pill">$<?php echo number_format($row['amount'], 2); ?></span>
                        </li>
                        <?php endwhile; ?>
                    </ul>
                </div>
            </div>
        </div>
        
        <!-- Monthly Data Table -->
        <div class="card">
            <div class="card-header">
                <h5 class="mb-0"><i class="fas fa-table me-2"></i>Monthly Breakdown</h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-hover">
                        <thead>
                            <tr>
                                <th>Month</th>
                                <th>Assets Added</th>
                                <th>Total Value</th>
                                <th>Average Value</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            $monthlyResult->data_seek(0); // Reset pointer
                            while ($row = $monthlyResult->fetch_assoc()): 
                                $monthName = date('F Y', mktime(0, 0, 0, $row['month'], 1, $row['year']));
                                $avgValue = $row['count'] > 0 ? $row['total'] / $row['count'] : 0;
                            ?>
                            <tr>
                                <td><?php echo $monthName; ?></td>
                                <td><?php echo $row['count']; ?></td>
                                <td>$<?php echo number_format($row['total'], 2); ?></td>
                                <td>$<?php echo number_format($avgValue, 2); ?></td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Monthly Chart
        const monthlyCtx = document.getElementById('monthlyChart').getContext('2d');
        const monthlyChart = new Chart(monthlyCtx, {
            type: 'bar',
            data: {
                labels: [
                    <?php 
                    $monthlyResult->data_seek(0); // Reset pointer
                    while ($row = $monthlyResult->fetch_assoc()): 
                        $monthName = date('M Y', mktime(0, 0, 0, $row['month'], 1, $row['year']));
                        echo "'" . $monthName . "',";
                    endwhile; 
                    ?>
                ].reverse(),
                datasets: [{
                    label: 'Monthly Asset Value',
                    data: [
                        <?php 
                        $monthlyResult->data_seek(0); // Reset pointer
                        while ($row = $monthlyResult->fetch_assoc()): 
                            echo $row['total'] . ",";
                        endwhile; 
                        ?>
                    ].reverse(),
                    backgroundColor: 'rgba(58, 87, 232, 0.7)',
                    borderColor: 'rgba(58, 87, 232, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return '$' + value.toLocaleString();
                            }
                        }
                    }
                },
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return 'Value: $' + context.raw.toLocaleString();
                            }
                        }
                    }
                }
            }
        });
    </script>
</body>
</html>

<?php
// Close connection
$conn->close();
?>