# Email Enrollment Issues - Critical Analysis

## Issue Summary
Mary Ward was enrolled via email link in Event 2931 despite:
1. ❌ Not being in the correct group membership for that event
2. ❌ Event being at capacity (48 spots) - she should have been waitlisted

## Audit Log Evidence
```
Timestamp: 2025-10-09 09:17:10
Event ID: 2931
User: WARD, MARY (ID: 6645)
Action: MEMBER_ADDED
Source: Email Confirmation
IP: 212.129.84.101
Status: PENDING
Referer: Email Link
```

## Root Cause Analysis

### Problem 1: NO Group Membership Validation
**File: event-enroll-confirm.php (Line 71-73)**
```php
if ($confirm === 'yes' && $EventId > 0 && $UserId > 0 && !$alreadyEnrolled && !empty($userName) && !empty($eventName)) {
    // ❌ NO GROUP MEMBERSHIP CHECK!
    $enrollSql = "INSERT INTO EventUsers (EventId, UserId, guest, waitlist, createdAt, updatedAt) 
                  VALUES (?, ?, 0, 0, NOW(), NOW())";
```

**What's Missing:**
- NO check if UserId is in the event's assigned group (GroupNumber)
- Anyone with ANY valid UserId can enroll via email link
- Email link format: `event-enroll-confirm.php?EventId=2931&UserId=6645`

**What Should Happen:**
```php
// Get event's group number
$groupStmt = $conn->prepare("SELECT GroupNumber FROM Events WHERE id = ?");
$groupStmt->bind_param("i", $EventId);
$groupStmt->execute();
$groupStmt->bind_result($eventGroupNumber);
$groupStmt->fetch();
$groupStmt->close();

// Check if user is in the event's group
$memberStmt = $conn->prepare("SELECT COUNT(*) FROM InviteGroupUsers WHERE id = ? AND groupNo = ?");
$memberStmt->bind_param("ii", $UserId, $eventGroupNumber);
$memberStmt->execute();
$memberStmt->bind_result($isMember);
$memberStmt->fetch();
$memberStmt->close();

if (!$isMember) {
    // REJECT enrollment - user not in event's group
    $enrollmentStatus = 'not_in_group';
    exit();
}
```

### Problem 2: NO Capacity/Waitlist Check
**File: event-enroll-confirm.php (Line 73)**
```php
// ❌ HARDCODED waitlist = 0 (always enrolled as participant, never waitlisted)
$enrollSql = "INSERT INTO EventUsers (EventId, UserId, guest, waitlist, createdAt, updatedAt) 
              VALUES (?, ?, 0, 0, NOW(), NOW())";
```

**The Problem:**
- `waitlist` is hardcoded to `0` (participant)
- NO capacity check before enrollment
- If event has 48 spots and 49 people enroll, #49 gets enrolled as participant instead of waitlist

**What Should Happen:**
```php
// Check event capacity
include "event-capacity-helper.php";
$capacity = checkEventCapacity($conn, $EventId);
$waitlistStatus = $capacity['waitlist_status']; // Returns 1 if over capacity, 0 if space available

// Use the calculated waitlist status
$enrollSql = "INSERT INTO EventUsers (EventId, UserId, guest, waitlist, createdAt, updatedAt) 
              VALUES (?, ?, 0, ?, NOW(), NOW())";
$enrollStmt->bind_param("iii", $EventId, $UserId, $waitlistStatus);
```

### Problem 3: Same Issue in event-enrol-me.php
**File: event-enrol-me.php (Line 66)**
```php
// ❌ SAME PROBLEMS: No group check, hardcoded waitlist = 0
$enrollSql = "INSERT INTO EventUsers (EventId, UserId, guest, waitlist) VALUES (?, ?, 0, 0)";
```

This file is even worse - it's labeled as having "no security controls" (line 7).

## How Mary Ward Was Able to Enroll

### Scenario: Email Link Enrollment
1. **Email Sent**: System sent email to all group members (or possibly all users?)
2. **Link Clicked**: Mary clicked link like: `event-enroll-confirm.php?EventId=2931&UserId=6645`
3. **No Validation**: System didn't check:
   - ❌ Is Mary in the event's group? (She's in "All" group, not "RollUpFriday")
   - ❌ Is event at capacity? (Event has 48 spots, she was probably #49+)
4. **Direct Enrollment**: System enrolled her immediately as participant (waitlist=0)

### Why This is Dangerous
- **Anyone with a valid UserId** can be enrolled by manipulating the URL
- **No group boundary enforcement** via email links
- **No capacity enforcement** - unlimited enrollments possible
- **Security vulnerability** - URL manipulation attack

## Comparison: Secure vs Insecure Enrollment

### Secure Enrollment (event-member-form.php)
```php
// ✅ Gets event's group number (line 35-40)
$groupStmt = $conn->prepare("SELECT GroupNumber FROM Events WHERE id = ?");

// ✅ Only shows members in that group in dropdown (line 48-62)
$userStmt = $conn->prepare("
    SELECT Users.id, Users.name, Users.email
    FROM Users
    JOIN InviteGroupUsers ON Users.id = InviteGroupUsers.id
    WHERE InviteGroupUsers.groupNo = ?
");

// ✅ Checks capacity and sets waitlist status (line 134-135)
$capacity = checkEventCapacity($conn, $EventId);
$waitlistStatus = $capacity['waitlist_status'];
```

### Insecure Enrollment (email links)
```php
// ❌ NO group membership check
// ❌ NO capacity check
// ❌ Hardcoded waitlist = 0
$enrollSql = "INSERT INTO EventUsers (EventId, UserId, guest, waitlist) VALUES (?, ?, 0, 0)";
```

## Impact Assessment

### Events Affected
- Any event using email enrollment links
- Particularly "Men" and "Women" events where wrong-gender members could enroll
- Events with capacity limits where waitlist should apply

### Users Affected
- Members receiving email invites for events they shouldn't access
- Waitlisted members who should be participants
- Participants bumped to waitlist incorrectly

## Required Fixes

### Fix 1: Add Group Membership Validation
Both `event-enroll-confirm.php` and `event-enrol-me.php` must check:
```php
// Validate user is in event's group
$groupCheckSql = "SELECT COUNT(*) as count FROM InviteGroupUsers igu
                  JOIN Events e ON igu.groupNo = e.GroupNumber
                  WHERE igu.id = ? AND e.id = ?";
$groupStmt = $conn->prepare($groupCheckSql);
$groupStmt->bind_param("ii", $UserId, $EventId);
$groupStmt->execute();
$result = $groupStmt->get_result();
$row = $result->fetch_assoc();
$groupStmt->close();

if ($row['count'] == 0) {
    $enrollmentStatus = 'not_in_event_group';
    // Exit or show error
}
```

### Fix 2: Add Capacity/Waitlist Check
```php
// Include capacity helper
include "event-capacity-helper.php";

// Check capacity and get waitlist status
$capacity = checkEventCapacity($conn, $EventId);
$waitlistStatus = $capacity['waitlist_status'];

// Use calculated waitlist status
$enrollSql = "INSERT INTO EventUsers (EventId, UserId, guest, waitlist, createdAt, updatedAt) 
              VALUES (?, ?, 0, ?, NOW(), NOW())";
$enrollStmt->bind_param("iii", $EventId, $UserId, $waitlistStatus);
```

### Fix 3: Update Audit Log
```php
// Update audit log with proper status based on waitlist
$auditStatus = $waitlistStatus ? 'WAITLISTED' : 'SUCCESS';
$auditStmt = $conn->prepare("UPDATE EventAuditLog SET Status = ? WHERE EventId = ? AND UserId = ? AND Action = 'MEMBER_ADDED' ORDER BY Timestamp DESC LIMIT 1");
```

## Email System Investigation

### Question: How Did Mary Get the Email Link?
Need to check:
1. **Email sending logic** - Who receives event invites?
2. **Recipient list generation** - Based on what criteria?
3. **Group filtering** - Are emails filtered by event group?

Files to check:
- `event-mail-build.php` - Email composition
- `event-mail-send.php` - Email sending
- Email recipient selection logic

### Hypothesis
The email system may be:
- Sending to ALL members instead of just event group members
- Sending to wrong group members
- Not filtering by event's assigned group

## Immediate Actions Required

1. ✅ **Identified**: Mary Ward enrolled via email link without validation
2. ⚠️ **Fix Email Enrollment**: Add group membership + capacity checks
3. ⚠️ **Fix Email Sending**: Ensure emails only go to event group members
4. ⚠️ **Remove Mary Ward**: From event 2931 (if still enrolled)
5. ⚠️ **Audit All Events**: Check for other incorrect enrollments via email links
6. ⚠️ **Test Fixes**: Verify group validation and waitlist logic work correctly

## Testing Checklist

After fixes applied, test:
- [ ] User NOT in event group tries to enroll via email link → Should be rejected
- [ ] User IS in event group, event has space → Should enroll as participant
- [ ] User IS in event group, event at capacity → Should enroll as waitlisted
- [ ] User already enrolled tries again → Should show "already enrolled"
- [ ] Invalid UserId in email link → Should show error
- [ ] Invalid EventId in email link → Should show error