<?php
$db = getDB();

// ---------- Determine action ----------
$action = $_GET['action'] ?? 'list';
if (!in_array($action, ['list', 'view'])) {
    $action = 'list';
}

// ---------- Common search / pagination ----------
$search = $_GET['search'] ?? '';
$pageNum = isset($_GET['p']) ? max(1, intval($_GET['p'])) : 1;
$limit = 50;
$offset = ($pageNum - 1) * $limit;

// ---------- View single user ----------
if ($action === 'view' && isset($_GET['id'])) {
    $userId = intval($_GET['id']);

    // Fetch user data
    $stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $user = $stmt->get_result()->fetch_assoc();

    if (!$user) {
        echo '<div class="alert alert-danger">User not found.</div>';
        $action = 'list';  // fallback to list
    } else {
        // Subscriptions
        $subs = $db->prepare("SELECT * FROM subscriptions WHERE user_id = ? ORDER BY created_at DESC");
        $subs->bind_param("i", $userId);
        $subs->execute();
        $subscriptions = $subs->get_result();

        // Recent payment transactions
        $txns = $db->prepare("SELECT * FROM payment_transactions WHERE user_id = ? ORDER BY created_at DESC LIMIT 10");
        $txns->bind_param("i", $userId);
        $txns->execute();
        $transactions = $txns->get_result();

        // Enrollments
        $enrolls = $db->prepare("SELECT e.*, c.title AS course_title FROM course_enrollments e JOIN courses c ON e.course_id = c.id WHERE e.user_id = ? ORDER BY e.enrolled_at DESC");
        $enrolls->bind_param("i", $userId);
        $enrolls->execute();
        $enrollments = $enrolls->get_result();

        // Lesson completions (last 10)
        $comps = $db->prepare("SELECT lc.*, l.title AS lesson_title, c.title AS course_title FROM lesson_completions lc JOIN lessons l ON lc.lesson_id = l.id JOIN courses c ON l.course_id = c.id WHERE lc.user_id = ? ORDER BY lc.completed_at DESC LIMIT 10");
        $comps->bind_param("i", $userId);
        $comps->execute();
        $completions = $comps->get_result();

        // Problem words (last 20)
        $prob = $db->prepare("SELECT pw.id, f.word, f.meaning_en FROM problem_words pw JOIN flashcards f ON pw.flashcard_id = f.id WHERE pw.user_id = ? ORDER BY pw.created_at DESC LIMIT 20");
        $prob->bind_param("i", $userId);
        $prob->execute();
        $problemWords = $prob->get_result();

        // Learned words count
        $lwStmt = $db->prepare("SELECT COUNT(*) FROM user_learned_words WHERE user_id = ?");
        $lwStmt->bind_param("i", $userId);
        $lwStmt->execute();
        $learnedCount = $lwStmt->get_result()->fetch_row()[0];

        // Daily activity (last 30 days)
        $actStmt = $db->prepare("SELECT activity_date, count FROM daily_activities WHERE user_id = ? AND activity_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) ORDER BY activity_date");
        $actStmt->bind_param("i", $userId);
        $actStmt->execute();
        $activities = $actStmt->get_result();
        ?>
        <h3>User Details: <?= htmlspecialchars($user['name']) ?></h3>
        <a href="?page=users" class="btn btn-secondary mb-3"><i class="fa fa-arrow-left"></i> Back to list</a>

        <div class="row">
            <!-- Profile card -->
            <div class="col-md-4 mb-4">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title">Profile</h5>
                        <table class="table table-sm">
                            <tr><th>ID</th><td><?= $user['id'] ?></td></tr>
                            <tr><th>Name</th><td><?= htmlspecialchars($user['name']) ?></td></tr>
                            <tr><th>Email</th><td><?= htmlspecialchars($user['email']) ?></td></tr>
                            <tr><th>Mobile</th><td><?= htmlspecialchars($user['mobile'] ?? '') ?></td></tr>
                            <tr><th>Joined</th><td><?= date('Y-m-d', strtotime($user['created_at'])) ?></td></tr>
                            <tr><th>Pro Status</th><td><?= $user['is_pro'] ? '<span class="badge bg-success">Pro</span>' : '<span class="badge bg-secondary">Free</span>' ?></td></tr>
                            <tr><th>Pro Expires</th><td><?= $user['pro_expires_at'] ? date('Y-m-d', strtotime($user['pro_expires_at'])) : 'N/A' ?></td></tr>
                            <tr><th>Streak</th><td><?= $user['streak_days'] ?> days (best: <?= $user['best_streak'] ?>)</td></tr>
                            <tr><th>Lessons Done</th><td><?= $user['total_lessons_done'] ?></td></tr>
                            <tr><th>Avg Exam Score</th><td><?= number_format($user['average_exam_score'], 2) ?>%</td></tr>
                        </table>
                    </div>
                </div>
            </div>

            <!-- Subscriptions & Transactions -->
            <div class="col-md-8 mb-4">
                <h5>Subscriptions</h5>
                <?php if ($subscriptions->num_rows > 0): ?>
                <table class="table table-sm table-striped">
                    <thead><tr><th>ID</th><th>Plan</th><th>Amount</th><th>Status</th><th>Start</th><th>End</th></tr></thead>
                    <tbody>
                    <?php while($s = $subscriptions->fetch_assoc()): ?>
                        <tr>
                            <td><?= $s['id'] ?></td>
                            <td><?= $s['plan_type'] ?></td>
                            <td><?= $s['amount'] ?> BDT</td>
                            <td><span class="badge bg-<?= $s['status'] == 'active' ? 'success' : ($s['status'] == 'expired' ? 'warning' : 'secondary') ?>"><?= $s['status'] ?></span></td>
                            <td><?= date('Y-m-d', strtotime($s['starts_at'])) ?></td>
                            <td><?= $s['ends_at'] ? date('Y-m-d', strtotime($s['ends_at'])) : 'N/A' ?></td>
                        </tr>
                    <?php endwhile; ?>
                    </tbody>
                </table>
                <?php else: ?>
                    <p>No subscriptions.</p>
                <?php endif; ?>

                <h5 class="mt-4">Recent Transactions</h5>
                <?php if ($transactions->num_rows > 0): ?>
                <table class="table table-sm table-striped">
                    <thead><tr><th>ID</th><th>Order ID</th><th>Amount</th><th>Status</th><th>Date</th></tr></thead>
                    <tbody>
                    <?php while($t = $transactions->fetch_assoc()): ?>
                        <tr>
                            <td><?= $t['id'] ?></td>
                            <td><?= htmlspecialchars($t['customer_order_id']) ?></td>
                            <td><?= $t['amount'] ?> BDT</td>
                            <td><span class="badge bg-<?= $t['status'] == 'success' ? 'success' : ($t['status'] == 'failed' ? 'danger' : 'secondary') ?>"><?= $t['status'] ?></span></td>
                            <td><?= date('Y-m-d', strtotime($t['transaction_date'])) ?></td>
                        </tr>
                    <?php endwhile; ?>
                    </tbody>
                </table>
                <?php else: ?>
                    <p>No transactions.</p>
                <?php endif; ?>
            </div>
        </div>

        <!-- Enrollments -->
        <h5>Course Enrollments</h5>
        <?php if ($enrollments->num_rows > 0): ?>
        <table class="table table-sm table-striped">
            <thead><tr><th>Course</th><th>Progress</th><th>Completed Lessons</th><th>Enrolled</th></tr></thead>
            <tbody>
            <?php while($e = $enrollments->fetch_assoc()): ?>
                <tr>
                    <td><?= htmlspecialchars($e['course_title']) ?></td>
                    <td><?= $e['progress'] ?>%</td>
                    <td><?= $e['completed_lessons'] ?></td>
                    <td><?= date('Y-m-d', strtotime($e['enrolled_at'])) ?></td>
                </tr>
            <?php endwhile; ?>
            </tbody>
        </table>
        <?php else: ?>
            <p>No enrollments.</p>
        <?php endif; ?>

        <!-- Recent Lesson Completions -->
        <h5>Recent Lesson Completions</h5>
        <?php if ($completions->num_rows > 0): ?>
        <table class="table table-sm table-striped">
            <thead><tr><th>Course</th><th>Lesson</th><th>Score</th><th>Completed</th></tr></thead>
            <tbody>
            <?php while($c = $completions->fetch_assoc()): ?>
                <tr>
                    <td><?= htmlspecialchars($c['course_title']) ?></td>
                    <td><?= htmlspecialchars($c['lesson_title']) ?></td>
                    <td><?= $c['exam_score'] ?>%</td>
                    <td><?= date('Y-m-d H:i', strtotime($c['completed_at'])) ?></td>
                </tr>
            <?php endwhile; ?>
            </tbody>
        </table>
        <?php else: ?>
            <p>No completions yet.</p>
        <?php endif; ?>

        <!-- Problem Words -->
        <h5>Problem Words (last 20)</h5>
        <?php if ($problemWords->num_rows > 0): ?>
        <table class="table table-sm table-striped">
            <thead><tr><th>Word</th><th>Meaning (EN)</th></tr></thead>
            <tbody>
            <?php while($pw = $problemWords->fetch_assoc()): ?>
                <tr>
                    <td><?= htmlspecialchars($pw['word']) ?></td>
                    <td><?= htmlspecialchars($pw['meaning_en']) ?></td>
                </tr>
            <?php endwhile; ?>
            </tbody>
        </table>
        <?php else: ?>
            <p>No problem words recorded.</p>
        <?php endif; ?>

        <!-- Activity Chart (simple table) -->
        <h5>Activity (past 30 days)</h5>
        <?php if ($activities->num_rows > 0): ?>
        <table class="table table-sm table-bordered">
            <thead><tr><th>Date</th><th>Count</th></tr></thead>
            <tbody>
            <?php while($a = $activities->fetch_assoc()): ?>
                <tr>
                    <td><?= $a['activity_date'] ?></td>
                    <td><?= $a['count'] ?></td>
                </tr>
            <?php endwhile; ?>
            </tbody>
        </table>
        <?php else: ?>
            <p>No activity recorded.</p>
        <?php endif;
    }
    return; // Exit early for view action
}

// ---------- USER LIST (default) ----------
?>
<h3>Users</h3>

<div class="d-flex justify-content-between mb-3">
    <form class="d-flex" method="get">
        <input type="hidden" name="page" value="users">
        <input type="text" name="search" class="form-control me-2" placeholder="Search name / email / mobile"
               value="<?= htmlspecialchars($search) ?>">
        <button type="submit" class="btn btn-outline-secondary"><i class="fa fa-search"></i></button>
    </form>
</div>

<?php
// Build WHERE
$where = [];
$params = [];
$types = '';

if (!empty($search)) {
    $where[] = "(name LIKE ? OR email LIKE ? OR mobile LIKE ?)";
    $param = "%$search%";
    $params[] = $param;
    $params[] = $param;
    $params[] = $param;
    $types .= 'sss';
}

$whereSQL = '';
if (!empty($where)) {
    $whereSQL = 'WHERE ' . implode(' AND ', $where);
}

// Count
$countSQL = "SELECT COUNT(*) FROM users $whereSQL";
$stmt = $db->prepare($countSQL);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$total = $stmt->get_result()->fetch_row()[0];
$totalPages = ceil($total / $limit);

// Fetch users
$sql = "SELECT id, name, email, mobile, is_pro, streak_days, total_lessons_done, total_words_learned, average_exam_score, created_at 
        FROM users $whereSQL ORDER BY id DESC LIMIT ? OFFSET ?";
$types .= 'ii';
$params[] = $limit;
$params[] = $offset;
$stmt = $db->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$users = $stmt->get_result();
?>

<div class="table-responsive">
    <table class="table table-striped table-hover">
        <thead class="table-dark">
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Mobile</th>
                <th>Pro</th>
                <th>Streak</th>
                <th>Lessons</th>
                
                <th>Avg Score</th>
                <th>Joined</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($users->num_rows > 0): ?>
            <?php while($u = $users->fetch_assoc()): ?>
            <tr>
                <td><?= $u['id'] ?></td>
                <td><?= htmlspecialchars($u['name']) ?></td>
                <td><?= htmlspecialchars($u['email']) ?></td>
                <td><?= htmlspecialchars($u['mobile'] ?? '') ?></td>
                <td><?= $u['is_pro'] ? '<span class="badge bg-success">Pro</span>' : '<span class="badge bg-secondary">Free</span>' ?></td>
                <td><?= $u['streak_days'] ?></td>
                <td><?= $u['total_lessons_done'] ?></td>
                
                <td><?= number_format($u['average_exam_score'], 1) ?>%</td>
                <td><?= date('Y-m-d', strtotime($u['created_at'])) ?></td>
                <td>
                    <a href="?page=users&action=view&id=<?= $u['id'] ?>" class="btn btn-sm btn-outline-info">
                        <i class="fa fa-eye"></i> View
                    </a>
                </td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="11" class="text-center">No users found.</td></tr>
        <?php endif; ?>
        </tbody>
    </table>
</div>

<!-- Pagination -->
<?php if ($totalPages > 1): ?>
<nav>
    <ul class="pagination">
        <?php for ($i = 1; $i <= $totalPages; $i++): ?>
            <li class="page-item <?= $i == $pageNum ? 'active' : '' ?>">
                <a class="page-link" href="?page=users&p=<?= $i ?>&search=<?= urlencode($search) ?>">
                    <?= $i ?>
                </a>
            </li>
        <?php endfor; ?>
    </ul>
</nav>
<?php endif; ?>