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

requireAdmin();

$page_title = 'System Analytics & Health';

// Create email_logs table if it doesn't exist
$conn->query("CREATE TABLE IF NOT EXISTS email_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipient VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body LONGTEXT,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    error_message TEXT,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_recipient (recipient),
    INDEX idx_created (created_at)
)");

// Get system analytics
$analytics = [
    'total_patients' => $conn->query("SELECT COUNT(*) as count FROM patients")->fetch_assoc()['count'],
    'total_doctors' => $conn->query("SELECT COUNT(*) as count FROM doctors")->fetch_assoc()['count'],
    'total_appointments' => $conn->query("SELECT COUNT(*) as count FROM appointments")->fetch_assoc()['count'],
    'completed_appointments' => $conn->query("SELECT COUNT(*) as count FROM appointments WHERE status = 'completed'")->fetch_assoc()['count'],
    'total_revenue' => $conn->query("SELECT COALESCE(SUM(consultation_fee), 0) as total FROM appointments WHERE status = 'completed'")->fetch_assoc()['total'],
    'pending_verifications' => $conn->query("SELECT COUNT(*) as count FROM doctors WHERE verification_status = 'pending'")->fetch_assoc()['count'],
    'total_support_tickets' => $conn->query("SELECT COUNT(*) as count FROM support_tickets")->fetch_assoc()['count'],
    'open_support_tickets' => $conn->query("SELECT COUNT(*) as count FROM support_tickets WHERE status = 'open'")->fetch_assoc()['count'],
];

// Email sending statistics
$email_stats = [
    'total_sent' => $conn->query("SELECT COUNT(*) as count FROM email_logs WHERE status = 'sent'")->fetch_assoc()['count'],
    'pending' => $conn->query("SELECT COUNT(*) as count FROM email_logs WHERE status = 'pending'")->fetch_assoc()['count'],
    'failed' => $conn->query("SELECT COUNT(*) as count FROM email_logs WHERE status = 'failed'")->fetch_assoc()['count'],
];

// System health checks
$db_status = 'Connected';
$db_size_result = $conn->query("SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS db_size FROM information_schema.TABLES WHERE table_schema = '" . DB_NAME . "'");
$db_size = $db_size_result->fetch_assoc()['db_size'] ?? 0;

// Recent activities
$recent_registrations = $conn->query("SELECT full_name, created_at FROM patients ORDER BY created_at DESC LIMIT 5")->fetch_all(MYSQLI_ASSOC);
$recent_appointments = $conn->query("SELECT p.full_name as patient, d.full_name as doctor, a.appointment_date, a.status FROM appointments a JOIN patients p ON a.patient_id = p.id JOIN doctors d ON a.doctor_id = d.id ORDER BY a.created_at DESC LIMIT 5")->fetch_all(MYSQLI_ASSOC);

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-line text-primary"></i> System Analytics & Health
                    </h1>
                </div>

                <!-- Key Metrics -->
                <div class="row mb-4">
                    <div class="col-md-3 mb-3">
                        <div class="card bg-primary text-white">
                            <div class="card-body">
                                <div class="d-flex justify-content-between">
                                    <div>
                                        <h5><?= number_format($analytics['total_patients']) ?></h5>
                                        <small>Total Patients</small>
                                    </div>
                                    <i class="fas fa-users fa-2x opacity-50"></i>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3 mb-3">
                        <div class="card bg-success text-white">
                            <div class="card-body">
                                <div class="d-flex justify-content-between">
                                    <div>
                                        <h5><?= number_format($analytics['total_doctors']) ?></h5>
                                        <small>Total Doctors</small>
                                    </div>
                                    <i class="fas fa-user-md fa-2x opacity-50"></i>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3 mb-3">
                        <div class="card bg-info text-white">
                            <div class="card-body">
                                <div class="d-flex justify-content-between">
                                    <div>
                                        <h5><?= number_format($analytics['total_appointments']) ?></h5>
                                        <small>Total Appointments</small>
                                    </div>
                                    <i class="fas fa-calendar fa-2x opacity-50"></i>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3 mb-3">
                        <div class="card bg-warning text-dark">
                            <div class="card-body">
                                <div class="d-flex justify-content-between">
                                    <div>
                                        <h5>PKR <?= number_format($analytics['total_revenue']) ?></h5>
                                        <small>Total Revenue</small>
                                    </div>
                                    <i class="fas fa-money-bill fa-2x opacity-50"></i>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- System Status & Health -->
                <div class="row mb-4">
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0"><i class="fas fa-stethoscope"></i> System Health</h5>
                            </div>
                            <div class="card-body">
                                <table class="table table-sm">
                                    <tr>
                                        <td><strong>Database Status:</strong></td>
                                        <td><span class="badge bg-success"><?= $db_status ?></span></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Database Size:</strong></td>
                                        <td><?= $db_size ?> MB</td>
                                    </tr>
                                    <tr>
                                        <td><strong>PHP Version:</strong></td>
                                        <td><?= phpversion() ?></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Server Time:</strong></td>
                                        <td><?= date('Y-m-d H:i:s') ?></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Memory Limit:</strong></td>
                                        <td><?= ini_get('memory_limit') ?></td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0"><i class="fas fa-envelope"></i> Email Status</h5>
                            </div>
                            <div class="card-body">
                                <table class="table table-sm">
                                    <tr>
                                        <td><strong>Emails Sent:</strong></td>
                                        <td><?= number_format($email_stats['total_sent']) ?></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Pending Emails:</strong></td>
                                        <td><span class="badge bg-warning"><?= $email_stats['pending'] ?></span></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Failed Emails:</strong></td>
                                        <td><span class="badge bg-danger"><?= $email_stats['failed'] ?></span></td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Pending Actions -->
                <div class="row mb-4">
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0"><i class="fas fa-exclamation-circle"></i> Pending Actions</h5>
                            </div>
                            <div class="card-body">
                                <table class="table table-sm">
                                    <tr>
                                        <td><strong>Pending Doctor Verifications:</strong></td>
                                        <td>
                                            <span class="badge bg-warning"><?= $analytics['pending_verifications'] ?></span>
                                            <a href="doctor-verification.php" class="ms-2">View</a>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td><strong>Open Support Tickets:</strong></td>
                                        <td>
                                            <span class="badge bg-warning"><?= $analytics['open_support_tickets'] ?></span>
                                            <a href="support.php" class="ms-2">View</a>
                                        </td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0"><i class="fas fa-chart-pie"></i> Appointment Statistics</h5>
                            </div>
                            <div class="card-body">
                                <table class="table table-sm">
                                    <tr>
                                        <td><strong>Total Appointments:</strong></td>
                                        <td><?= number_format($analytics['total_appointments']) ?></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Completed:</strong></td>
                                        <td><?= number_format($analytics['completed_appointments']) ?></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Completion Rate:</strong></td>
                                        <td>
                                            <?php 
                                                $rate = $analytics['total_appointments'] > 0 ? 
                                                    round(($analytics['completed_appointments'] / $analytics['total_appointments']) * 100, 1) : 0;
                                                echo $rate . '%';
                                            ?>
                                        </td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Recent Activity -->
                <div class="row">
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0"><i class="fas fa-user-plus"></i> Recent Patient Registrations</h5>
                            </div>
                            <div class="table-responsive">
                                <table class="table table-sm mb-0">
                                    <tbody>
                                        <?php foreach ($recent_registrations as $patient): ?>
                                            <tr>
                                                <td><?= htmlspecialchars($patient['full_name']) ?></td>
                                                <td><small class="text-muted"><?= date('M d, h:i A', strtotime($patient['created_at'])) ?></small></td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0"><i class="fas fa-calendar-check"></i> Recent Appointments</h5>
                            </div>
                            <div class="table-responsive">
                                <table class="table table-sm mb-0">
                                    <tbody>
                                        <?php foreach ($recent_appointments as $apt): ?>
                                            <tr>
                                                <td>
                                                    <small><?= htmlspecialchars($apt['patient']) ?> → Dr. <?= htmlspecialchars($apt['doctor']) ?></small><br>
                                                    <small class="text-muted"><?= date('M d, h:i A', strtotime($apt['appointment_date'])) ?></small>
                                                </td>
                                                <td><span class="badge bg-primary"><?= ucfirst($apt['status']) ?></span></td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>

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