<?php
/**
 * Admin Reports Dashboard
 * Overview of all system reports and analytics
 */

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

requireAdmin();

$pdo = getDB();
$period = $_GET['period'] ?? 'month';

// Get date range
$dateRange = getDateRange($period);

// Get summary statistics
$stmt = $pdo->query("SELECT 
                    (SELECT COUNT(*) FROM users WHERE role = 'doctor') as total_doctors,
                    (SELECT COUNT(*) FROM users WHERE role = 'doctor' AND is_active = 1) as active_doctors,
                    (SELECT COUNT(*) FROM cases) as total_cases,
                    (SELECT COUNT(*) FROM cases WHERE created_at BETWEEN '{$dateRange['start']}' AND '{$dateRange['end']}') as period_cases,
                    (SELECT COUNT(*) FROM subscriptions WHERE is_active = 1) as active_subscriptions,
                    (SELECT SUM(amount) FROM subscriptions WHERE is_active = 1) as monthly_revenue,
                    (SELECT COUNT(*) FROM rubrics) as total_rubrics,
                    (SELECT COUNT(*) FROM remedies) as total_remedies");
$summary = $stmt->fetch();

// Get monthly trends for charts
$stmt = $pdo->query("SELECT 
                    DATE_FORMAT(created_at, '%Y-%m') as month,
                    COUNT(*) as cases_count,
                    (SELECT COUNT(*) FROM subscriptions WHERE DATE_FORMAT(created_at, '%Y-%m') = month) as subscriptions_count
                    FROM cases 
                    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
                    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                    ORDER BY month ASC");
$monthlyTrends = $stmt->fetchAll();

// Get top doctors by cases
$stmt = $pdo->query("SELECT u.full_name, COUNT(c.id) as case_count 
                    FROM users u
                    LEFT JOIN cases c ON u.id = c.user_id
                    WHERE u.role = 'doctor'
                    GROUP BY u.id
                    ORDER BY case_count DESC
                    LIMIT 10");
$topDoctors = $stmt->fetchAll();

// Get top prescribed remedies
$stmt = $pdo->query("SELECT r.name, COUNT(c.id) as prescription_count 
                    FROM remedies r
                    JOIN cases c ON r.id = c.prescribed_remedy_id
                    GROUP BY r.id
                    ORDER BY prescription_count DESC
                    LIMIT 10");
$topRemedies = $stmt->fetchAll();

// Get subscription plan distribution
$stmt = $pdo->query("SELECT 
                    plan_type,
                    COUNT(*) as count,
                    SUM(amount) as total_amount
                    FROM subscriptions
                    WHERE is_active = 1
                    GROUP BY plan_type");
$planDistribution = $stmt->fetchAll();

// Get recent activities
$stmt = $pdo->query("SELECT 'case' as type, id, created_at, patient_name as name 
                    FROM cases 
                    ORDER BY created_at DESC LIMIT 5
                    UNION ALL
                    SELECT 'subscription' as type, id, created_at, CONCAT('Subscription for ', u.full_name) as name
                    FROM subscriptions s
                    JOIN users u ON s.user_id = u.id
                    ORDER BY created_at DESC LIMIT 5
                    ORDER BY created_at DESC LIMIT 10");
$recentActivities = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Reports Dashboard - Admin Panel</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>
        .sidebar {
            min-height: 100vh;
            background: #2c3e50;
        }
        .sidebar .nav-link {
            color: #ecf0f1;
            padding: 12px 20px;
            transition: all 0.3s;
        }
        .sidebar .nav-link:hover {
            background: #34495e;
            padding-left: 25px;
        }
        .sidebar .nav-link.active {
            background: #3498db;
        }
        .sidebar .nav-link i {
            margin-right: 10px;
            width: 20px;
        }
        .main-content {
            background: #f8f9fa;
            min-height: 100vh;
        }
        .top-navbar {
            background: white;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            padding: 15px 20px;
        }
        .stats-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            text-align: center;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            transition: transform 0.2s;
        }
        .stats-card:hover {
            transform: translateY(-5px);
        }
        .stats-number {
            font-size: 32px;
            font-weight: bold;
        }
        .report-link {
            text-decoration: none;
            display: block;
            padding: 15px;
            background: white;
            border-radius: 8px;
            margin-bottom: 10px;
            transition: all 0.2s;
        }
        .report-link:hover {
            background: #e3f2fd;
            transform: translateX(5px);
        }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <!-- Sidebar -->
            <div class="col-md-2 p-0 sidebar">
                <div class="text-center py-4">
                    <h4 class="text-white">🏥 Admin Panel</h4>
                    <small class="text-muted"><?php echo APP_VERSION; ?></small>
                </div>
                <nav class="nav flex-column">
                    <a class="nav-link" href="../dashboard.php">
                        <i class="fas fa-tachometer-alt"></i> Dashboard
                    </a>
                    <a class="nav-link" href="../doctors/index.php">
                        <i class="fas fa-user-md"></i> Doctors
                    </a>
                    <a class="nav-link" href="../subscriptions/index.php">
                        <i class="fas fa-credit-card"></i> Subscriptions
                    </a>
                    <a class="nav-link" href="../rubrics/index.php">
                        <i class="fas fa-tree"></i> Rubrics
                    </a>
                    <a class="nav-link" href="../remedies/index.php">
                        <i class="fas fa-capsules"></i> Remedies
                    </a>
                    <a class="nav-link" href="../settings/index.php">
                        <i class="fas fa-cog"></i> Settings
                    </a>
                    <a class="nav-link active" href="index.php">
                        <i class="fas fa-chart-line"></i> Reports
                    </a>
                    <a class="nav-link" href="../profile.php">
                        <i class="fas fa-user"></i> My Profile
                    </a>
                    <hr class="bg-secondary">
                    <a class="nav-link" href="../../logout.php">
                        <i class="fas fa-sign-out-alt"></i> Logout
                    </a>
                </nav>
            </div>
            
            <!-- Main Content -->
            <div class="col-md-10 main-content">
                <div class="top-navbar">
                    <div class="d-flex justify-content-between align-items-center">
                        <h4 class="mb-0"><i class="fas fa-chart-line"></i> Reports Dashboard</h4>
                        <div>
                            <a href="export.php" class="btn btn-success btn-sm">
                                <i class="fas fa-download"></i> Export All Reports
                            </a>
                        </div>
                    </div>
                </div>
                
                <div class="p-4">
                    <!-- Period Filter -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <form method="GET" class="row g-3">
                                <div class="col-md-3">
                                    <label>Report 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>
                    </div>
                    
                    <!-- Statistics Cards -->
                    <div class="row mb-4">
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo $summary['total_doctors']; ?></div>
                                <div class="text-muted">Total Doctors</div>
                                <small><?php echo $summary['active_doctors']; ?> Active</small>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo $summary['total_cases']; ?></div>
                                <div class="text-muted">Total Cases</div>
                                <small>+<?php echo $summary['period_cases']; ?> this period</small>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo $summary['active_subscriptions']; ?></div>
                                <div class="text-muted">Active Subscriptions</div>
                                <small><?php echo formatCurrency($summary['monthly_revenue']); ?> MRR</small>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo $summary['total_remedies']; ?></div>
                                <div class="text-muted">Remedies</div>
                                <small>in <?php echo $summary['total_rubrics']; ?> rubrics</small>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Charts Row -->
                    <div class="row mb-4">
                        <div class="col-md-8">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0"><i class="fas fa-chart-line"></i> Monthly Trends</h5>
                                </div>
                                <div class="card-body">
                                    <canvas id="trendsChart" height="250"></canvas>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0"><i class="fas fa-chart-pie"></i> Plan Distribution</h5>
                                </div>
                                <div class="card-body">
                                    <canvas id="planChart" height="200"></canvas>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Top Lists Row -->
                    <div class="row mb-4">
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0"><i class="fas fa-user-md"></i> Top Doctors by Cases</h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-sm">
                                            <thead>
                                                <tr><th>Doctor</th><th>Cases</th><th>%</th></tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                $totalCases = array_sum(array_column($topDoctors, 'case_count'));
                                                foreach($topDoctors as $doctor): 
                                                    $percentage = $totalCases > 0 ? round(($doctor['case_count'] / $totalCases) * 100, 1) : 0;
                                                ?>
                                                <tr>
                                                    <td><?php echo escape($doctor['full_name']); ?></td>
                                                    <td><?php echo $doctor['case_count']; ?></td>
                                                    <td>
                                                        <div class="progress" style="height: 20px;">
                                                            <div class="progress-bar bg-primary" style="width: <?php echo $percentage; ?>%">
                                                                <?php echo $percentage; ?>%
                                                            </div>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                         </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0"><i class="fas fa-capsules"></i> Top Prescribed Remedies</h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-sm">
                                            <thead>
                                                <tr><th>Remedy</th><th>Prescriptions</th><th>%</th></tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                $totalPrescriptions = array_sum(array_column($topRemedies, 'prescription_count'));
                                                foreach($topRemedies as $remedy): 
                                                    $percentage = $totalPrescriptions > 0 ? round(($remedy['prescription_count'] / $totalPrescriptions) * 100, 1) : 0;
                                                ?>
                                                <tr>
                                                    <td><?php echo escape($remedy['name']); ?></td>
                                                    <td><?php echo $remedy['prescription_count']; ?></td>
                                                    <td>
                                                        <div class="progress" style="height: 20px;">
                                                            <div class="progress-bar bg-success" style="width: <?php echo $percentage; ?>%">
                                                                <?php echo $percentage; ?>%
                                                            </div>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                         </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Quick Reports Links -->
                    <div class="row">
                        <div class="col-md-12">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0"><i class="fas fa-file-alt"></i> Detailed Reports</h5>
                                </div>
                                <div class="card-body">
                                    <div class="row">
                                        <div class="col-md-3">
                                            <a href="doctors.php" class="report-link">
                                                <i class="fas fa-user-md fa-2x float-end text-primary"></i>
                                                <strong>Doctor Activity Report</strong><br>
                                                <small class="text-muted">View doctor performance and case statistics</small>
                                            </a>
                                        </div>
                                        <div class="col-md-3">
                                            <a href="subscriptions.php" class="report-link">
                                                <i class="fas fa-credit-card fa-2x float-end text-success"></i>
                                                <strong>Subscription Report</strong><br>
                                                <small class="text-muted">Subscription analytics and expiring plans</small>
                                            </a>
                                        </div>
                                        <div class="col-md-3">
                                            <a href="revenue.php" class="report-link">
                                                <i class="fas fa-chart-line fa-2x float-end text-warning"></i>
                                                <strong>Revenue Report</strong><br>
                                                <small class="text-muted">Financial analytics and income trends</small>
                                            </a>
                                        </div>
                                        <div class="col-md-3">
                                            <a href="export.php" class="report-link">
                                                <i class="fas fa-download fa-2x float-end text-info"></i>
                                                <strong>Export Reports</strong><br>
                                                <small class="text-muted">Export data to CSV/Excel format</small>
                                            </a>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Recent Activities -->
                    <div class="row mt-4">
                        <div class="col-md-12">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0"><i class="fas fa-history"></i> Recent Activities</h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-sm">
                                            <thead>
                                                <tr><th>Time</th><th>Type</th><th>Description</th></tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($recentActivities as $activity): ?>
                                                <tr>
                                                    <td><?php echo date('d-m-Y H:i', strtotime($activity['created_at'])); ?>