<?php
/**
 * Most Prescribed Remedies Report
 * Analysis of remedy usage and effectiveness
 */

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

requireLogin();

$pdo = getDB();
$userId = $_SESSION['user_id'];
$period = $_GET['period'] ?? 'all';
$dateRange = getDateRange($period);

// Get remedy statistics
$stmt = $pdo->prepare("SELECT r.id, r.name, r.abbreviation,
                      COUNT(c.id) as prescription_count,
                      SUM(CASE WHEN c.status = 'closed' THEN 1 ELSE 0 END) as successful_count,
                      COUNT(DISTINCT c.patient_mobile) as unique_patients
                      FROM remedies r
                      LEFT JOIN cases c ON r.id = c.prescribed_remedy_id AND c.user_id = ?
                      WHERE c.created_at BETWEEN ? AND ? OR c.id IS NULL
                      GROUP BY r.id
                      HAVING prescription_count > 0
                      ORDER BY prescription_count DESC
                      LIMIT 50");
$stmt->execute([$userId, $dateRange['start'], $dateRange['end']]);
$remedies = $stmt->fetchAll();

// Calculate totals
$totalPrescriptions = array_sum(array_column($remedies, 'prescription_count'));
$topRemedy = !empty($remedies) ? $remedies[0] : null;

// Get monthly remedy trend for top 5 remedies
$top5RemedyIds = array_slice(array_column($remedies, 'id'), 0, 5);
$monthlyTrend = [];
if (!empty($top5RemedyIds)) {
    $placeholders = implode(',', array_fill(0, count($top5RemedyIds), '?'));
    $stmt = $pdo->prepare("SELECT r.name, DATE_FORMAT(c.created_at, '%Y-%m') as month, COUNT(*) as count
                          FROM cases c
                          JOIN remedies r ON c.prescribed_remedy_id = r.id
                          WHERE c.user_id = ? AND c.prescribed_remedy_id IN ($placeholders)
                          AND c.created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
                          GROUP BY r.name, DATE_FORMAT(c.created_at, '%Y-%m')
                          ORDER BY month ASC");
    $params = array_merge([$userId], $top5RemedyIds);
    $stmt->execute($params);
    $trendData = $stmt->fetchAll();
    
    foreach ($trendData as $td) {
        $monthlyTrend[$td['name']][$td['month']] = $td['count'];
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Remedy Analytics - 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);
        }
        .remedy-item {
            padding: 10px;
            border-bottom: 1px solid #eee;
        }
        .progress-bar-custom { height: 8px; border-radius: 4px; }
        @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="remedies.php"><i class="fas fa-capsules"></i> <span>Remedies</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-capsules"></i> Most Prescribed Remedies</h4>
                <div>
                    <a href="export.php?type=remedies&<?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>
        
        <!-- Period Filter -->
        <div class="stats-card">
            <form method="GET" class="row g-3">
                <div class="col-md-3">
                    <label>Period</label>
                    <select name="period" class="form-control" onchange="this.form.submit()">
                        <option value="week" <?php echo $period == 'week' ? 'selected' : ''; ?>>Last 7 Days</option>
                        <option value="month" <?php echo $period == 'month' ? 'selected' : ''; ?>>Last 30 Days</option>
                        <option value="year" <?php echo $period == 'year' ? 'selected' : ''; ?>>Last 12 Months</option>
                        <option value="all" <?php echo $period == 'all' ? 'selected' : ''; ?>>All Time</option>
                    </select>
                </div>
            </form>
        </div>
        
        <!-- Top Remedy Stats -->
        <?php if($topRemedy): ?>
        <div class="stats-card text-center bg-success text-white">
            <h5>Most Prescribed Remedy</h5>
            <h2><?php echo escape($topRemedy['name']); ?></h2>
            <p><?php echo $topRemedy['prescription_count']; ?> prescriptions | <?php echo $topRemedy['unique_patients']; ?> patients</p>
            <a href="../materia-medica/view.php?id=<?php echo $topRemedy['id']; ?>" class="btn btn-light">View Materia Medica</a>
        </div>
        <?php endif; ?>
        
        <div class="row">
            <div class="col-md-6">
                <div class="stats-card">
                    <h5><i class="fas fa-chart-bar"></i> Prescription Distribution</h5>
                    <canvas id="remediesChart" height="300"></canvas>
                </div>
            </div>
            <div class="col-md-6">
                <div class="stats-card">
                    <h5><i class="fas fa-chart-line"></i> Top 5 Remedies Trend</h5>
                    <canvas id="trendChart" height="300"></canvas>
                </div>
            </div>
        </div>
        
        <!-- Remedies List -->
        <div class="stats-card">
            <h5><i class="fas fa-list"></i> All Prescribed Remedies</h5>
            <div class="table-responsive">
                <table class="table table-hover">
                    <thead>
                        <tr><th>#</th><th>Remedy</th><th>Prescriptions</th><th>Unique Patients</th><th>Success Rate</th><th>Action</th></tr>
                    </thead>
                    <tbody>
                        <?php $rank = 1; foreach($remedies as $remedy): 
                            $percentage = $totalPrescriptions > 0 ? round(($remedy['prescription_count'] / $totalPrescriptions) * 100, 1) : 0;
                            $successRate = $remedy['prescription_count'] > 0 ? round(($remedy['successful_count'] / $remedy['prescription_count']) * 100, 1) : 0;
                        ?>
                        <tr>
                            <td><?php echo $rank++; ?></td>
                            <td><strong><?php echo escape($remedy['name']); ?></strong><br><small class="text-muted"><?php echo $remedy['abbreviation']; ?></small></td>
                            <td>
                                <?php echo $remedy['prescription_count']; ?>
                                <div class="progress mt-1" style="height: 5px;">
                                    <div class="progress-bar bg-primary" style="width: <?php echo $percentage; ?>%"></div>
                                </div>
                            </td>
                            <td><?php echo $remedy['unique_patients']; ?></td>
                            <td>
                                <span class="text-<?php echo $successRate >= 70 ? 'success' : ($successRate >= 40 ? 'warning' : 'danger'); ?>">
                                    <?php echo $successRate; ?>%
                                </span>
                                <div class="progress mt-1" style="height: 5px;">
                                    <div class="progress-bar bg-<?php echo $successRate >= 70 ? 'success' : ($successRate >= 40 ? 'warning' : 'danger'); ?>" style="width: <?php echo $successRate; ?>%"></div>
                                </div>
                            </td>
                            <td>
                                <a href="../materia-medica/view.php?id=<?php echo $remedy['id']; ?>" class="btn btn-sm btn-info">View</a>
                                <a href="../cases/list.php?remedy=<?php echo urlencode($remedy['name']); ?>" class="btn btn-sm btn-primary">Cases</a>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
        // Remedies Chart
        const remediesCtx = document.getElementById('remediesChart').getContext('2d');
        new Chart(remediesCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode(array_column(array_slice($remedies, 0, 10), 'name')); ?>,
                datasets: [{
                    label: 'Prescriptions',
                    data: <?php echo json_encode(array_column(array_slice($remedies, 0, 10), 'prescription_count')); ?>,
                    backgroundColor: '#2ecc71',
                    borderRadius: 5
                }]
            },
            options: { responsive: true, maintainAspectRatio: true, indexAxis: 'y' }
        });
        
        // Trend Chart
        const trendCtx = document.getElementById('trendChart').getContext('2d');
        const trendDatasets = [];
        const colors = ['#2ecc71', '#3498db', '#f39c12', '#e74c3c', '#9b59b6'];
        let idx = 0;
        <?php foreach($monthlyTrend as $remedyName => $data): ?>
            trendDatasets.push({
                label: '<?php echo addslashes($remedyName); ?>',
                data: <?php echo json_encode(array_values($data)); ?>,
                borderColor: colors[<?php echo $idx++; ?>],
                fill: false,
                tension: 0.3
            });
        <?php endforeach; ?>
        
        new Chart(trendCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode(array_keys(array_values($monthlyTrend)[0] ?? [])); ?>,
                datasets: trendDatasets
            },
            options: { responsive: true, maintainAspectRatio: true }
        });
    </script>
</body>
</html>