<?php
/**
 * Manage Subscriptions - List View
 * Display all subscriptions with search, filter, and pagination
 */

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

requireAdmin();

$pdo = getDB();
$message = '';
$error = '';

// Handle bulk actions
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['bulk_action'])) {
    $selectedIds = $_POST['selected_ids'] ?? [];
    $bulkAction = $_POST['bulk_action'];
    
    if (!empty($selectedIds)) {
        $placeholders = implode(',', array_fill(0, count($selectedIds), '?'));
        
        if ($bulkAction == 'cancel') {
            $stmt = $pdo->prepare("UPDATE subscriptions SET is_active = 0 WHERE id IN ($placeholders)");
            $stmt->execute($selectedIds);
            $message = count($selectedIds) . " subscriptions cancelled.";
        } elseif ($bulkAction == 'activate') {
            $stmt = $pdo->prepare("UPDATE subscriptions SET is_active = 1 WHERE id IN ($placeholders)");
            $stmt->execute($selectedIds);
            $message = count($selectedIds) . " subscriptions activated.";
        } elseif ($bulkAction == 'delete') {
            $stmt = $pdo->prepare("DELETE FROM subscriptions WHERE id IN ($placeholders)");
            $stmt->execute($selectedIds);
            $message = count($selectedIds) . " subscriptions deleted.";
        }
    }
}

// Get filter parameters
$search = $_GET['search'] ?? '';
$status = $_GET['status'] ?? 'all';
$plan = $_GET['plan'] ?? 'all';
$page = (int)($_GET['page'] ?? 1);
$limit = ITEMS_PER_PAGE;
$offset = ($page - 1) * $limit;

// Build query
$whereConditions = ["1=1"];
$params = [];

if (!empty($search)) {
    $whereConditions[] = "(u.full_name LIKE ? OR u.email LIKE ?)";
    $searchTerm = "%$search%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

if ($status == 'active') {
    $whereConditions[] = "s.is_active = 1";
} elseif ($status == 'expired') {
    $whereConditions[] = "s.is_active = 1 AND s.plan_type != 'unlimited' AND s.end_date < CURDATE()";
} elseif ($status == 'inactive') {
    $whereConditions[] = "s.is_active = 0";
}

if ($plan != 'all') {
    $whereConditions[] = "s.plan_type = ?";
    $params[] = $plan;
}

$whereClause = implode(" AND ", $whereConditions);

// Get total count for pagination
$countSql = "SELECT COUNT(DISTINCT s.id) as total 
             FROM subscriptions s
             JOIN users u ON s.user_id = u.id
             WHERE $whereClause";
$stmt = $pdo->prepare($countSql);
$stmt->execute($params);
$totalSubscriptions = $stmt->fetch()['total'];
$totalPages = ceil($totalSubscriptions / $limit);

// Get subscriptions list
$sql = "SELECT s.*, u.full_name, u.email, u.username,
        DATEDIFF(s.end_date, CURDATE()) as days_remaining,
        CASE 
            WHEN s.plan_type = 'unlimited' THEN 'Lifetime'
            WHEN s.end_date < CURDATE() THEN 'Expired'
            WHEN DATEDIFF(s.end_date, CURDATE()) <= 7 THEN 'Expiring Soon'
            ELSE 'Active'
        END as status_label
        FROM subscriptions s
        JOIN users u ON s.user_id = u.id
        WHERE $whereClause
        ORDER BY s.created_at DESC
        LIMIT ? OFFSET ?";

$params[] = $limit;
$params[] = $offset;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$subscriptions = $stmt->fetchAll();

// Get statistics
$stmt = $pdo->query("SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN is_active = 1 AND plan_type = 'unlimited' THEN 1 ELSE 0 END) as unlimited,
                    SUM(CASE WHEN is_active = 1 AND plan_type != 'unlimited' AND 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 = 0 THEN 1 ELSE 0 END) as cancelled,
                    SUM(CASE WHEN plan_type = '1month' THEN 1 ELSE 0 END) as plan_1month,
                    SUM(CASE WHEN plan_type = '6month' THEN 1 ELSE 0 END) as plan_6month,
                    SUM(CASE WHEN plan_type = '1year' THEN 1 ELSE 0 END) as plan_1year,
                    SUM(CASE WHEN plan_type = 'unlimited' THEN 1 ELSE 0 END) as plan_unlimited,
                    SUM(amount) as total_revenue
                    FROM subscriptions");
$stats = $stmt->fetch();

// Get plans for filter
$stmt = $pdo->query("SELECT DISTINCT plan_type FROM subscriptions");
$plans = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Manage Subscriptions - 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">
    <link href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <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: 15px;
            margin-bottom: 20px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            text-align: center;
        }
        .stats-number {
            font-size: 28px;
            font-weight: bold;
        }
        .status-badge {
            padding: 4px 10px;
            border-radius: 20px;
            font-size: 11px;
            font-weight: bold;
        }
        .status-active { background: #d4edda; color: #155724; }
        .status-expired { background: #f8d7da; color: #721c24; }
        .status-expiring { background: #fff3cd; color: #856404; }
        .status-cancelled { background: #e2e3e5; color: #383d41; }
        .status-lifetime { background: #d1ecf1; color: #0c5460; }
    </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 active" href="index.php">
                        <i class="fas fa-credit-card"></i> Subscriptions
                    </a>
                    <a class="nav-link" href="plans.php">
                        <i class="fas fa-tags"></i> Plans
                    </a>
                    <a class="nav-link" href="reports.php">
                        <i class="fas fa-chart-line"></i> Reports
                    </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" 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-credit-card"></i> Manage Subscriptions</h4>
                        <div>
                            <a href="add.php" class="btn btn-primary btn-sm">
                                <i class="fas fa-plus"></i> Add Subscription
                            </a>
                            <a href="reports.php" class="btn btn-info btn-sm">
                                <i class="fas fa-chart-line"></i> Reports
                            </a>
                        </div>
                    </div>
                </div>
                
                <div class="p-4">
                    <!-- Alert Messages -->
                    <?php if ($message): ?>
                        <div class="alert alert-success alert-dismissible fade show">
                            <i class="fas fa-check-circle"></i> <?php echo $message; ?>
                            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                        </div>
                    <?php endif; ?>
                    
                    <?php if ($error): ?>
                        <div class="alert alert-danger alert-dismissible fade show">
                            <i class="fas fa-exclamation-triangle"></i> <?php echo $error; ?>
                            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                        </div>
                    <?php endif; ?>
                    
                    <!-- Statistics Cards -->
                    <div class="row mb-4">
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo $stats['total']; ?></div>
                                <div class="text-muted">Total Subscriptions</div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number text-success"><?php echo $stats['active']; ?></div>
                                <div class="text-muted">Active</div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number text-warning"><?php echo $stats['unlimited']; ?></div>
                                <div class="text-muted">Lifetime</div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="stats-card">
                                <div class="stats-number"><?php echo formatCurrency($stats['total_revenue']); ?></div>
                                <div class="text-muted">Total Revenue</div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Filter Section -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <form method="GET" action="" class="row g-3">
                                <div class="col-md-4">
                                    <input type="text" class="form-control" name="search" 
                                           placeholder="Search doctor..." 
                                           value="<?php echo escape($search); ?>">
                                </div>
                                <div class="col-md-2">
                                    <select class="form-control" name="status">
                                        <option value="all" <?php echo $status == 'all' ? 'selected' : ''; ?>>All Status</option>
                                        <option value="active" <?php echo $status == 'active' ? 'selected' : ''; ?>>Active</option>
                                        <option value="expired" <?php echo $status == 'expired' ? 'selected' : ''; ?>>Expired</option>
                                        <option value="inactive" <?php echo $status == 'inactive' ? 'selected' : ''; ?>>Cancelled</option>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <select class="form-control" name="plan">
                                        <option value="all" <?php echo $plan == 'all' ? 'selected' : ''; ?>>All Plans</option>
                                        <?php foreach($plans as $p): ?>
                                            <option value="<?php echo $p['plan_type']; ?>" <?php echo $plan == $p['plan_type'] ? 'selected' : ''; ?>>
                                                <?php echo ucfirst($p['plan_type']); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <button type="submit" class="btn btn-primary w-100">
                                        <i class="fas fa-search"></i> Filter
                                    </button>
                                </div>
                                <div class="col-md-2">
                                    <a href="reports.php?export=csv&<?php echo http_build_query($_GET); ?>" class="btn btn-success w-100">
                                        <i class="fas fa-download"></i> Export CSV
                                    </a>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    <!-- Subscriptions Table -->
                    <div class="card">
                        <div class="card-header">
                            <div class="d-flex justify-content-between align-items-center">
                                <h5 class="mb-0">Subscriptions List</h5>
                                <span class="badge bg-primary"><?php echo $totalSubscriptions; ?> Records</span>
                            </div>
                        </div>
                        <div class="card-body">
                            <form method="POST" id="bulkForm">
                                <div class="table-responsive">
                                    <table class="table table-bordered table-hover">
                                        <thead class="table-light">
                                            <tr>
                                                <th width="30">
                                                    <input type="checkbox" id="selectAll">
                                                </th>
                                                <th>ID</th>
                                                <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>
                                                <th width="120">Actions</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php if (empty($subscriptions)): ?>
                                                <tr>
                                                    <td colspan="11" class="text-center text-muted py-4">
                                                        <i class="fas fa-credit-card fa-2x mb-2 d-block"></i>
                                                        No subscriptions found
                                                     </td>
                                                </tr>
                                            <?php else: ?>
                                                <?php foreach($subscriptions as $sub): ?>
                                                <tr>
                                                    <td>
                                                        <input type="checkbox" name="selected_ids[]" value="<?php echo $sub['id']; ?>" class="sub-checkbox">
                                                     </td>
                                                    <td><?php echo $sub['id']; ?></td>
                                                    <td>
                                                        <strong><?php echo escape($sub['full_name']); ?></strong><br>
                                                        <small class="text-muted">@<?php echo $sub['username']; ?></small>
                                                     </td>
                                                    <td>
                                                        <span class="badge bg-<?php 
                                                            echo $sub['plan_type'] == 'unlimited' ? 'info' : 
                                                                ($sub['plan_type'] == '1year' ? 'primary' : 
                                                                ($sub['plan_type'] == '6month' ? 'success' : 'secondary')); 
                                                        ?>">
                                                            <?php echo ucfirst($sub['plan_type']); ?>
                                                        </span>
                                                     </td>
                                                    <td><?php echo formatCurrency($sub['amount']); ?></td>
                                                    <td><?php echo date(DISPLAY_DATE_FORMAT, strtotime($sub['start_date'])); ?></td>
                                                    <td>
                                                        <?php echo $sub['end_date'] ? date(DISPLAY_DATE_FORMAT, strtotime($sub['end_date'])) : 'Lifetime'; ?>
                                                     </td>
                                                    <td>
                                                        <?php if ($sub['plan_type'] == 'unlimited'): ?>
                                                            <span class="badge bg-info">∞</span>
                                                        <?php elseif ($sub['days_remaining'] !== null): ?>
                                                            <?php if ($sub['days_remaining'] > 0): ?>
                                                                <span class="badge bg-<?php echo $sub['days_remaining'] <= 7 ? 'warning' : 'success'; ?>">
                                                                    <?php echo $sub['days_remaining']; ?> days
                                                                </span>
                                                            <?php else: ?>
                                                                <span class="badge bg-danger">Expired</span>
                                                            <?php endif; ?>
                                                        <?php else: ?>
                                                            <span class="badge bg-secondary">-</span>
                                                        <?php endif; ?>
                                                     </td>
                                                    <td>
                                                        <?php
                                                        $statusClass = '';
                                                        $statusText = '';
                                                        if ($sub['plan_type'] == 'unlimited') {
                                                            $statusClass = 'status-lifetime';
                                                            $statusText = 'Lifetime';
                                                        } elseif (!$sub['is_active']) {
                                                            $statusClass = 'status-cancelled';
                                                            $statusText = 'Cancelled';
                                                        } elseif ($sub['end_date'] && strtotime($sub['end_date']) < time()) {
                                                            $statusClass = 'status-expired';
                                                            $statusText = 'Expired';
                                                        } elseif ($sub['end_date'] && $sub['days_remaining'] <= 7) {
                                                            $statusClass = 'status-expiring';
                                                            $statusText = 'Expiring Soon';
                                                        } else {
                                                            $statusClass = 'status-active';
                                                            $statusText = 'Active';
                                                        }
                                                        ?>
                                                        <span class="status-badge <?php echo $statusClass; ?>">
                                                            <?php echo $statusText; ?>
                                                        </span>
                                                     </td>
                                                    <td><?php echo date(DISPLAY_DATE_FORMAT, strtotime($sub['created_at'])); ?></td>
                                                    <td>
                                                        <div class="btn-group btn-group-sm">
                                                            <a href="edit.php?id=<?php echo $sub['id']; ?>" class="btn btn-primary" title="Edit">
                                                                <i class="fas fa-edit"></i>
                                                            </a>
                                                            <?php if ($sub['is_active'] && $sub['plan_type'] != 'unlimited' && (!$sub['end_date'] || strtotime($sub['end_date']) >= time())): ?>
                                                                <a href="renew.php?id=<?php echo $sub['id']; ?>" class="btn btn-success" title="Renew">
                                                                    <i class="fas fa-sync-alt"></i>
                                                                </a>
                                                            <?php endif; ?>
                                                            <a href="cancel.php?id=<?php echo $sub['id']; ?>" 
                                                               class="btn btn-warning" title="Cancel"
                                                               onclick="return confirm('Cancel this subscription?')">
                                                                <i class="fas fa-ban"></i>
                                                            </a>
                                                        </div>
                                                     </td>
                                                 </tr>
                                                <?php endforeach; ?>
                                            <?php endif; ?>
                                        </tbody>
                                    </table>
                                </div>
                                
                                <!-- Bulk Actions -->
                                <?php if (!empty($subscriptions)): ?>
                                <div class="row mt-3">
                                    <div class="col-md-6">
                                        <div class="input-group">
                                            <select name="bulk_action" class="form-control w-auto" style="width: auto;">
                                                <option value="">Bulk Actions</option>
                                                <option value="activate">Activate Selected</option>
                                                <option value="cancel">Cancel Selected</option>
                                                <option value="delete">Delete Selected</option>
                                            </select>
                                            <button type="submit" class="btn btn-secondary" onclick="return confirmBulkAction()">
                                                Apply
                                            </button>
                                        </div>
                                    </div>
                                    <div class="col-md-6">
                                        <?php echo pagination($page, $totalPages, 'index.php?' . http_build_query(array_merge($_GET, ['page' => 'PAGE']))); ?>
                                    </div>
                                </div>
                                <?php endif; ?>
                            </form>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script src="../../assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <script>
        // Select all checkbox functionality
        document.getElementById('selectAll')?.addEventListener('change', function() {
            const checkboxes = document.querySelectorAll('.sub-checkbox');
            checkboxes.forEach(cb => cb.checked = this.checked);
        });
        
        function confirmBulkAction() {
            const action = document.querySelector('[name="bulk_action"]').value;
            const checked = document.querySelectorAll('.sub-checkbox:checked').length;
            
            if (!action) {
                alert('Please select an action');
                return false;
            }
            
            if (checked === 0) {
                alert('Please select at least one subscription');
                return false;
            }
            
            return confirm(`Are you sure you want to ${action} ${checked} subscription(s)?`);
        }
    </script>
</body>
</html>