<?php
/**
 * Subscription Report
 * Detailed subscription analytics and expiring plans
 */

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

requireAdmin();

$pdo = getDB();
$status = $_GET['status'] ?? 'all';
$page = (int)($_GET['page'] ?? 1);
$limit = 50;
$offset = ($page - 1) * $limit;

// Build query
$where = "1=1";
if ($status == 'active') $where = "s.is_active = 1 AND (s.plan_type = 'unlimited' OR s.end_date >= CURDATE())";
elseif ($status == 'expired') $where = "s.is_active = 1 AND s.plan_type != 'unlimited' AND s.end_date < CURDATE()";
elseif ($status == 'expiring') $where = "s.is_active = 1 AND s.plan_type != 'unlimited' AND s.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)";
elseif ($status == 'cancelled') $where = "s.is_active = 0";

// Get subscriptions
$sql = "SELECT s.*, u.full_name, u.email, u.username,
        DATEDIFF(s.end_date, CURDATE()) as days_remaining
        FROM subscriptions s
        JOIN users u ON s.user_id = u.id
        WHERE $where
        ORDER BY s.created_at DESC
        LIMIT $limit OFFSET $offset";
$subscriptions = $pdo->query($sql)->fetchAll();

// Get statistics
$stats = $pdo->query("SELECT 
    COUNT(*) as total,
    SUM(CASE WHEN is_active = 1 AND (plan_type = 'unlimited' OR end_date >= CURDATE()) THEN 1 ELSE 0 END) as active,
    SUM(CASE WHEN is_active = 1 AND plan_type != 'unlimited' AND end_date < CURDATE() THEN 1 ELSE 0 END) as expired,
    SUM(CASE WHEN is_active = 1 AND plan_type != 'unlimited' AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as expiring,
    SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) as cancelled,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_amount
    FROM subscriptions")->fetch();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Subscription Report</title>
    <link href="../../assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="../../assets/vendor/fontawesome/css/all.min.css" rel="stylesheet">
    <style>
        .sidebar { min-height: 100vh; background: #2c3e50; }
        .sidebar .nav-link { color: #ecf0f1; padding: 12px 20px; }
        .sidebar .nav-link:hover { background: #34495e; }
        .sidebar .nav-link.active { background: #3498db; }
        .main-content { background: #f8f9fa; min-height: 100vh; }
        .top-navbar { background: white; padding: 15px 20px; }
        .stats-card { background: white; border-radius: 10px; padding: 15px; text-align: center; margin-bottom: 20px; }
        .stats-number { font-size: 28px; font-weight: bold; }
        .status-active { background: #d4edda; color: #155724; }
        .status-expired { background: #f8d7da; color: #721c24; }
        .status-expiring { background: #fff3cd; color: #856404; }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-2 p-0 sidebar">
                <div class="text-center py-4"><h4 class="text-white">🏥 Admin</h4></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 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> Profile</a>
                    <hr><a class="nav-link" href="../../logout.php"><i class="fas fa-sign-out-alt"></i> Logout</a>
                </nav>
            </div>
            
            <div class="col-md-10 main-content">
                <div class="top-navbar">
                    <div class="d-flex justify-content-between">
                        <h4 class="mb-0"><i class="fas fa-credit-card"></i> Subscription Report</h4>
                        <div>
                            <a href="?export=csv&<?php echo http_build_query($_GET); ?>" class="btn btn-success btn-sm">Export CSV</a>
                            <a href="index.php" class="btn btn-secondary btn-sm">Back</a>
                        </div>
                    </div>
                </div>
                
                <div class="p-4">
                    <!-- Filter Tabs -->
                    <ul class="nav nav-tabs mb-4">
                        <li class="nav-item"><a class="nav-link <?php echo $status == 'all' ? 'active' : ''; ?>" href="?status=all">All (<?php echo $stats['total']; ?>)</a></li>
                        <li class="nav-item"><a class="nav-link <?php echo $status == 'active' ? 'active' : ''; ?>" href="?status=active">Active (<?php echo $stats['active']; ?>)</a></li>
                        <li class="nav-item"><a class="nav-link <?php echo $status == 'expiring' ? 'active' : ''; ?>" href="?status=expiring">Expiring Soon (<?php echo $stats['expiring']; ?>)</a></li>
                        <li class="nav-item"><a class="nav-link <?php echo $status == 'expired' ? 'active' : ''; ?>" href="?status=expired">Expired (<?php echo $stats['expired']; ?>)</a></li>
                        <li class="nav-item"><a class="nav-link <?php echo $status == 'cancelled' ? 'active' : ''; ?>" href="?status=cancelled">Cancelled (<?php echo $stats['cancelled']; ?>)</a></li>
                    </ul>
                    
                    <!-- Stats Row -->
                    <div class="row mb-4">
                        <div class="col-md-3"><div class="stats-card"><div class="stats-number"><?php echo formatCurrency($stats['total_revenue']); ?></div><div>Total Revenue</div></div></div>
                        <div class="col-md-3"><div class="stats-card"><div class="stats-number"><?php echo formatCurrency($stats['avg_amount']); ?></div><div>Average Amount</div></div></div>
                        <div class="col-md-3"><div class="stats-card"><div class="stats-number"><?php echo $stats['active']; ?></div><div>Active Subscriptions</div></div></div>
                        <div class="col-md-3"><div class="stats-card"><div class="stats-number"><?php echo $stats['expiring']; ?></div><div>Expiring in 30 Days</div></div></div>
                    </div>
                    
                    <!-- Subscriptions Table -->
                    <div class="card">
                        <div class="card-body">
                            <div class="table-responsive">
                                <table class="table table-bordered">
                                    <thead><tr><th>Doctor</th><th>Plan</th><th>Amount</th><th>Start Date</th><th>End Date</th><th>Days Left</th><th>Status</th><th>Created</th></tr></thead>
                                    <tbody>
                                        <?php foreach($subscriptions as $sub): 
                                            $statusClass = '';
                                            $statusText = '';
                                            if (!$sub['is_active']) { $statusClass = 'status-cancelled'; $statusText = 'Cancelled'; }
                                            elseif ($sub['plan_type'] == 'unlimited') { $statusClass = 'status-active'; $statusText = 'Lifetime'; }
                                            elseif ($sub['end_date'] && strtotime($sub['end_date']) < time()) { $statusClass = 'status-expired'; $statusText = 'Expired'; }
                                            elseif ($sub['end_date'] && $sub['days_remaining'] <= 30) { $statusClass = 'status-expiring'; $statusText = 'Expiring Soon'; }
                                            else { $statusClass = 'status-active'; $statusText = 'Active'; }
                                        ?>
                                        <tr>
                                            <td><strong><?php echo escape($sub['full_name']); ?></strong><br><small><?php echo $sub['email']; ?></small></td>
                                            <td><?php echo ucfirst($sub['plan_type']); ?></td>
                                            <td><?php echo formatCurrency($sub['amount']); ?></td>
                                            <td><?php echo date('d-m-Y', strtotime($sub['start_date'])); ?></td>
                                            <td><?php echo $sub['end_date'] ? date('d-m-Y', strtotime($sub['end_date'])) : 'Lifetime'; ?></td>
                                            <td><?php echo $sub['days_remaining'] > 0 ? $sub['days_remaining'] . ' days' : '-'; ?></td>
                                            <td><span class="badge <?php echo $statusClass; ?>"><?php echo $statusText; ?></span></td>
                                            <td><?php echo date('d-m-Y', strtotime($sub['created_at'])); ?></td>
                                        </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>