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

requireAdmin();

$page_title = 'Reports & Analytics';

// Generate reports based on date range
$start_date = $_GET['start_date'] ?? date('Y-m-01'); // First day of current month
$end_date = $_GET['end_date'] ?? date('Y-m-d'); // Today

// Patient registration stats
$patient_stats = $conn->prepare("
    SELECT 
        DATE(created_at) as date,
        COUNT(*) as registrations
    FROM patients 
    WHERE DATE(created_at) BETWEEN ? AND ?
    GROUP BY DATE(created_at)
    ORDER BY date DESC
");
$patient_stats->bind_param('ss', $start_date, $end_date);
$patient_stats->execute();
$patient_data = $patient_stats->get_result()->fetch_all(MYSQLI_ASSOC);

// Appointment stats
$appointment_stats = $conn->prepare("
    SELECT 
        status,
        COUNT(*) as count,
        SUM(consultation_fee) as total_revenue
    FROM appointments 
    WHERE DATE(created_at) BETWEEN ? AND ?
    GROUP BY status
");
$appointment_stats->bind_param('ss', $start_date, $end_date);
$appointment_stats->execute();
$appointment_data = $appointment_stats->get_result()->fetch_all(MYSQLI_ASSOC);

// Doctor verification stats
$doctor_stats = $conn->prepare("
    SELECT 
        verification_status,
        COUNT(*) as count
    FROM doctors 
    WHERE DATE(created_at) BETWEEN ? AND ?
    GROUP BY verification_status
");
$doctor_stats->bind_param('ss', $start_date, $end_date);
$doctor_stats->execute();
$doctor_data = $doctor_stats->get_result()->fetch_all(MYSQLI_ASSOC);

// Top doctors by appointments
$top_doctors = $conn->prepare("
    SELECT 
        d.full_name,
        s.name as specialization,
        COUNT(a.id) as appointment_count,
        SUM(a.consultation_fee) as total_earnings,
        AVG(r.rating) as avg_rating
    FROM doctors d
    LEFT JOIN appointments a ON d.id = a.doctor_id AND DATE(a.created_at) BETWEEN ? AND ?
    LEFT JOIN specializations s ON d.specialization_id = s.id
    LEFT JOIN reviews r ON d.id = r.doctor_id
    WHERE d.verification_status = 'approved'
    GROUP BY d.id
    HAVING appointment_count > 0
    ORDER BY appointment_count DESC
    LIMIT 10
");
$top_doctors->bind_param('ss', $start_date, $end_date);
$top_doctors->execute();
$top_doctors_data = $top_doctors->get_result()->fetch_all(MYSQLI_ASSOC);

// Overall summary
$summary = [
    'total_patients' => $conn->query("SELECT COUNT(*) as count FROM patients WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'")->fetch_assoc()['count'],
    'total_doctors' => $conn->query("SELECT COUNT(*) as count FROM doctors WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'")->fetch_assoc()['count'],
    'total_appointments' => $conn->query("SELECT COUNT(*) as count FROM appointments WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'")->fetch_assoc()['count'],
    'total_revenue' => $conn->query("SELECT COALESCE(SUM(consultation_fee), 0) as total FROM appointments WHERE status = 'completed' AND DATE(created_at) BETWEEN '$start_date' AND '$end_date'")->fetch_assoc()['total']
];

include 'header.php';
?>

                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
                    <h1 class="h2">
                        <i class="fas fa-chart-bar text-primary"></i> Reports & Analytics
                    </h1>
                </div>
            
            <!-- Date Range Filter -->
            <div class="card mb-4">
                <div class="card-body">
                    <form method="GET" class="row g-3 align-items-end">
                        <div class="col-md-4">
                            <label class="form-label">Start Date</label>
                            <input type="date" name="start_date" class="form-control" value="<?= htmlspecialchars($start_date) ?>">
                        </div>
                        <div class="col-md-4">
                            <label class="form-label">End Date</label>
                            <input type="date" name="end_date" class="form-control" value="<?= htmlspecialchars($end_date) ?>">
                        </div>
                        <div class="col-md-4">
                            <button type="submit" class="btn btn-primary">
                                <i class="fas fa-search"></i> Generate Report
                            </button>
                            <button type="button" class="btn btn-success" onclick="window.print()">
                                <i class="fas fa-print"></i> Print
                            </button>
                        </div>
                    </form>
                </div>
            </div>

            <!-- Summary Stats -->
            <div class="row mb-4">
                <div class="col-md-3">
                    <div class="card bg-primary text-white">
                        <div class="card-body">
                            <div class="d-flex justify-content-between">
                                <div>
                                    <h4><?= number_format($summary['total_patients']) ?></h4>
                                    <p>New Patients</p>
                                </div>
                                <i class="fas fa-users fa-2x"></i>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="card bg-success text-white">
                        <div class="card-body">
                            <div class="d-flex justify-content-between">
                                <div>
                                    <h4><?= number_format($summary['total_doctors']) ?></h4>
                                    <p>New Doctors</p>
                                </div>
                                <i class="fas fa-user-md fa-2x"></i>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="card bg-info text-white">
                        <div class="card-body">
                            <div class="d-flex justify-content-between">
                                <div>
                                    <h4><?= number_format($summary['total_appointments']) ?></h4>
                                    <p>Appointments</p>
                                </div>
                                <i class="fas fa-calendar fa-2x"></i>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="card bg-warning text-white">
                        <div class="card-body">
                            <div class="d-flex justify-content-between">
                                <div>
                                    <h4>Rs. <?= number_format($summary['total_revenue']) ?></h4>
                                    <p>Revenue</p>
                                </div>
                                <i class="fas fa-money-bill fa-2x"></i>
                            </div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="row">
                <!-- Appointment Status Chart -->
                <div class="col-md-6 mb-4">
                    <div class="card">
                        <div class="card-header">
                            <h6><i class="fas fa-chart-pie"></i> Appointment Status Distribution</h6>
                        </div>
                        <div class="card-body">
                            <canvas id="appointmentChart" width="400" height="200"></canvas>
                        </div>
                    </div>
                </div>

                <!-- Patient Registration Trend -->
                <div class="col-md-6 mb-4">
                    <div class="card">
                        <div class="card-header">
                            <h6><i class="fas fa-chart-line"></i> Patient Registration Trend</h6>
                        </div>
                        <div class="card-body">
                            <canvas id="patientChart" width="400" height="200"></canvas>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Top Doctors -->
            <div class="card mb-4">
                <div class="card-header">
                    <h6><i class="fas fa-trophy"></i> Top Performing Doctors</h6>
                </div>
                <div class="card-body">
                    <?php if (empty($top_doctors_data)): ?>
                        <div class="text-center text-muted">
                            <i class="fas fa-user-md fa-3x mb-3"></i>
                            <p>No appointment data available for the selected period</p>
                        </div>
                    <?php else: ?>
                        <div class="table-responsive">
                            <table class="table table-striped">
                                <thead>
                                    <tr>
                                        <th>Doctor Name</th>
                                        <th>Specialization</th>
                                        <th>Appointments</th>
                                        <th>Total Earnings</th>
                                        <th>Average Rating</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($top_doctors_data as $index => $doctor): ?>
                                        <tr>
                                            <td>
                                                <div class="d-flex align-items-center">
                                                    <span class="badge bg-<?= $index < 3 ? 'warning' : 'secondary' ?> me-2">
                                                        #<?= $index + 1 ?>
                                                    </span>
                                                    Dr. <?= htmlspecialchars($doctor['full_name']) ?>
                                                </div>
                                            </td>
                                            <td><?= htmlspecialchars($doctor['specialization']) ?></td>
                                            <td>
                                                <span class="badge bg-primary"><?= $doctor['appointment_count'] ?></span>
                                            </td>
                                            <td>Rs. <?= number_format($doctor['total_earnings']) ?></td>
                                            <td>
                                                <?php if ($doctor['avg_rating']): ?>
                                                    <span class="text-warning">
                                                        <?= str_repeat('★', floor($doctor['avg_rating'])) ?>
                                                    </span>
                                                    <?= number_format($doctor['avg_rating'], 1) ?>
                                                <?php else: ?>
                                                    <span class="text-muted">No reviews</span>
                                                <?php endif; ?>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    <?php endif; ?>
                </div>
            </div>

            <!-- Detailed Reports -->
            <div class="row">
                <div class="col-md-6">
                    <div class="card">
                        <div class="card-header">
                            <h6><i class="fas fa-calendar-check"></i> Appointment Details</h6>
                        </div>
                        <div class="card-body">
                            <table class="table table-sm">
                                <?php foreach ($appointment_data as $apt): ?>
                                    <tr>
                                        <td><?= ucfirst($apt['status']) ?></td>
                                        <td><span class="badge bg-primary"><?= $apt['count'] ?></span></td>
                                        <td class="text-end">Rs. <?= number_format($apt['total_revenue']) ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            </table>
                        </div>
                    </div>
                </div>

                <div class="col-md-6">
                    <div class="card">
                        <div class="card-header">
                            <h6><i class="fas fa-user-md"></i> Doctor Verification Status</h6>
                        </div>
                        <div class="card-body">
                            <table class="table table-sm">
                                <?php foreach ($doctor_data as $doc): ?>
                                    <tr>
                                        <td><?= ucfirst($doc['verification_status']) ?></td>
                                        <td><span class="badge bg-<?= $doc['verification_status'] === 'approved' ? 'success' : ($doc['verification_status'] === 'pending' ? 'warning' : 'danger') ?>"><?= $doc['count'] ?></span></td>
                                    </tr>
                                <?php endforeach; ?>
                            </table>
                        </div>
                    </div>
                </div>
            </div>

<?php include 'footer.php'; ?>