<?php
/**
 * Monthly Case Trend Report
 * Year-over-year case analysis
 */

require_once '../../config/config.php';
require_once '../../includes/functions.php';

requireLogin();

$pdo = getDB();
$userId = $_SESSION['user_id'];
$year = $_GET['year'] ?? date('Y');
$compareYear = $_GET['compare_year'] ?? date('Y') - 1;

// Get monthly data for selected year
$stmt = $pdo->prepare("SELECT 
                      MONTH(created_at) as month,
                      COUNT(*) as case_count,
                      SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) as closed_count,
                      SUM(CASE WHEN prescribed_remedy_id IS NOT NULL THEN 1 ELSE 0 END) as prescription_count
                      FROM cases 
                      WHERE user_id = ? AND YEAR(created_at) = ?
                      GROUP BY MONTH(created_at)");
$stmt->execute([$userId, $year]);
$yearData = $stmt->fetchAll();

// Get monthly data for comparison year
$stmt = $pdo->prepare("SELECT 
                      MONTH(created_at) as month,
                      COUNT(*) as case_count
                      FROM cases 
                      WHERE user_id = ? AND YEAR(created_at) = ?
                      GROUP BY MONTH(created_at)");
$stmt->execute([$userId, $compareYear]);
$compareData = $stmt->fetchAll();

// Fill missing months
$monthsData = [];
$compareMonthsData = [];
for ($i = 1; $i <= 12; $i++) {
    $found = false;
    foreach ($yearData as $yd) {
        if ($yd['month'] == $i) {
            $monthsData[$i] = $yd;
            $found = true;
            break;
        }
    }
    if (!$found) {
        $monthsData[$i] = ['month' => $i, 'case_count' => 0, 'closed_count' => 0, 'prescription_count' => 0];
    }
    
    $foundComp = false;
    foreach ($compareData as $cd) {
        if ($cd['month'] == $i) {
            $compareMonthsData[$i] = $cd['case_count'];
            $foundComp = true;
            break;
        }
    }
    if (!$foundComp) {
        $compareMonthsData[$i] = 0;
    }
}

// Calculate totals
$totalCases = array_sum(array_column($monthsData, 'case_count'));
$totalClosed = array_sum(array_column($monthsData, 'closed_count'));
$totalPrescriptions = array_sum(array_column($monthsData, 'prescription_count'));
$avgCasesPerMonth = round($totalCases / 12, 1);
$bestMonth = array_reduce($monthsData, function($carry, $item) {
    return (!$carry || $item['case_count'] > $carry['case_count']) ? $item : $carry;
}, null);
$monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Monthly Case Trend - Reports</title>
    <link href="../../assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="../../assets/vendor/fontawesome/css/all.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        body { background: #f0f2f5; }
        .sidebar {
            min-height: 100vh;
            background: linear-gradient(135deg, #1a472a 0%, #0d2818 100%);
            position: fixed;
            left: 0;
            top: 0;
            width: 260px;
        }
        .sidebar .nav-link {
            color: rgba(255,255,255,0.8);
            padding: 12px 20px;
            margin: 5px 10px;
            border-radius: 8px;
        }
        .sidebar .nav-link:hover { background: rgba(255,255,255,0.1); }
        .sidebar .nav-link.active { background: #2ecc71; }
        .sidebar .nav-link i { margin-right: 10px; }
        .main-content { margin-left: 260px; padding: 20px; }
        .top-navbar {
            background: white;
            border-radius: 12px;
            padding: 15px 25px;
            margin-bottom: 25px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
        }
        .stats-card {
            background: white;
            border-radius: 12px;
            padding: 20px;
            margin-bottom: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
        }
        .stats-number { font-size: 28px; font-weight: bold; }
        @media (max-width: 768px) { .sidebar { width: 70px; } .sidebar .nav-link span { display: none; } .main-content { margin-left: 70px; } }
    </style>
</head>
<body>
    <div class="sidebar">
        <div class="text-center py-4"><h4 class="text-white mb-0">🏥</h4></div>
        <nav class="nav flex-column">
            <a class="nav-link" href="../dashboard.php"><i class="fas fa-tachometer-alt"></i> <span>Dashboard</span></a>
            <a class="nav-link" href="../cases/index.php"><i class="fas fa-folder-open"></i> <span>Cases</span></a>
            <a class="nav-link" href="../repertory/index.php"><i class="fas fa-book"></i> <span>Repertory</span></a>
            <a class="nav-link" href="../materia-medica/index.php"><i class="fas fa-capsules"></i> <span>Materia Medica</span></a>
            <a class="nav-link active" href="index.php"><i class="fas fa-chart-line"></i> <span>Reports</span></a>
            <a class="nav-link" href="monthly.php"><i class="fas fa-calendar-alt"></i> <span>Monthly Trend</span></a>
            <a class="nav-link" href="../profile.php"><i class="fas fa-user"></i> <span>Profile</span></a>
            <hr><a class="nav-link" href="../../logout.php"><i class="fas fa-sign-out-alt"></i> <span>Logout</span></a>
        </nav>
    </div>
    
    <div class="main-content">
        <div class="top-navbar">
            <div class="d-flex justify-content-between">
                <h4 class="mb-0"><i class="fas fa-calendar-alt"></i> Monthly Case Trend</h4>
                <div>
                    <a href="export.php?type=monthly&<?php echo http_build_query($_GET); ?>" class="btn btn-success btn-sm">
                        <i class="fas fa-download"></i> Export CSV
                    </a>
                    <a href="index.php" class="btn btn-secondary btn-sm">Back</a>
                </div>
            </div>
        </div>
        
        <!-- Year Selection -->
        <div class="stats-card">
            <form method="GET" class="row g-3">
                <div class="col-md-4">
                    <label>Select Year</label>
                    <select name="year" class="form-control" onchange="this.form.submit()">
                        <?php for($y = date('Y'); $y >= 2020; $y--): ?>
                            <option value="<?php echo $y; ?>" <?php echo $year == $y ? 'selected' : ''; ?>><?php echo $y; ?></option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div class="col-md-4">
                    <label>Compare with Year</label>
                    <select name="compare_year" class="form-control" onchange="this.form.submit()">
                        <option value="">No Comparison</option>
                        <?php for($y = date('Y'); $y >= 2020; $y--): ?>
                            <option value="<?php echo $y; ?>" <?php echo $compareYear == $y ? 'selected' : ''; ?>><?php echo $y; ?></option>
                        <?php endfor; ?>
                    </select>
                </div>
            </form>
        </div>
        
        <!-- Summary Stats -->
        <div class="row">
            <div class="col-md-3">
                <div class="stats-card text-center">
                    <div class="stats-number"><?php echo $totalCases; ?></div>
                    <div class="text-muted">Total Cases (<?php echo $year; ?>)</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stats-card text-center">
                    <div class="stats-number"><?php echo $avgCasesPerMonth; ?></div>
                    <div class="text-muted">Avg Cases/Month</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stats-card text-center">
                    <div class="stats-number"><?php echo $totalClosed; ?></div>
                    <div class="text-muted">Closed Cases</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stats-card text-center">
                    <div class="stats-number"><?php echo $totalPrescriptions; ?></div>
                    <div class="text-muted">Prescriptions</div>
                </div>
            </div>
        </div>
        
        <!-- Best Month -->
        <?php if($bestMonth && $bestMonth['case_count'] > 0): ?>
        <div class="stats-card text-center bg-info text-white">
            <h5>Best Month in <?php echo $year; ?></h5>
            <h2><?php echo $monthNames[$bestMonth['month'] - 1]; ?></h2>
            <p><?php echo $bestMonth['case_count']; ?> cases | <?php echo $bestMonth['closed_count']; ?> closed | <?php echo $bestMonth['prescription_count']; ?> prescriptions</p>
        </div>
        <?php endif; ?>
        
        <!-- Charts -->
        <div class="row">
            <div class="col-md-12">
                <div class="stats-card">
                    <h5><i class="fas fa-chart-line"></i> Monthly Case Comparison</h5>
                    <canvas id="comparisonChart" height="300"></canvas>
                </div>
            </div>
        </div>
        
        <div class="row">
            <div class="col-md-12">
                <div class="stats-card">
                    <h5><i class="fas fa-table"></i> Monthly Breakdown</h5>
                    <div class="table-responsive">
                        <table class="table table-bordered">
                            <thead class="table-light">
                                <tr>
                                    <th>Month</th>
                                    <th>Cases</th>
                                    <th>Closed</th>
                                    <th>Prescriptions</th>
                                    <th>Closure Rate</th>
                                    <th>Prescription Rate</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($monthsData as $month): 
                                    $closureRate = $month['case_count'] > 0 ? round(($month['closed_count'] / $month['case_count']) * 100, 1) : 0;
                                    $prescriptionRate = $month['case_count'] > 0 ? round(($month['prescription_count'] / $month['case_count']) * 100, 1) : 0;
                                ?>
                                <tr>
                                    <td><strong><?php echo $monthNames[$month['month'] - 1]; ?></strong></td>
                                    <td><?php echo $month['case_count']; ?></td>
                                    <td><?php echo $month['closed_count']; ?></td>
                                    <td><?php echo $month['prescription_count']; ?></td>
                                    <td>
                                        <div class="progress" style="height: 20px;">
                                            <div class="progress-bar bg-<?php echo $closureRate >= 70 ? 'success' : ($closureRate >= 40 ? 'warning' : 'danger'); ?>" style="width: <?php echo $closureRate; ?>%">
                                                <?php echo $closureRate; ?>%
                                            </div>
                                        </div>
                                    </td>
                                    <td>
                                        <div class="progress" style="height: 20px;">
                                            <div class="progress-bar bg-info" style="width: <?php echo $prescriptionRate; ?>%">
                                                <?php echo $prescriptionRate; ?>%
                                            </div>
                                        </div>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
        const ctx = document.getElementById('comparisonChart').getContext('2d');
        const datasets = [{
            label: '<?php echo $year; ?>',
            data: <?php echo json_encode(array_column($monthsData, 'case_count')); ?>,
            borderColor: '#2ecc71',
            backgroundColor: 'rgba(46,204,113,0.1)',
            tension: 0.3,
            fill: true
        }];
        
        <?php if($compareYear): ?>
        datasets.push({
            label: '<?php echo $compareYear; ?>',
            data: <?php echo json_encode(array_values($compareMonthsData)); ?>,
            borderColor: '#3498db',
            backgroundColor: 'rgba(52,152,219,0.1)',
            tension: 0.3,
            fill: true
        });
        <?php endif; ?>
        
        new Chart(ctx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($monthNames); ?>,
                datasets: datasets
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    tooltip: { mode: 'index', intersect: false },
                    legend: { position: 'top' }
                }
            }
        });
    </script>
</body>
</html>