-- Quick SQL Investigation Queries for Event 2994 and Member 980 (Soren Nordahl)
-- Run these queries directly in your database to investigate

-- 1. Event Details
SELECT 
    e.id as EventId,
    e.event as EventName,
    e.date as EventDate,
    e.groupType,
    e.GroupNumber,
    (SELECT groupName FROM InviteEventGroupsText WHERE groupNumber = e.GroupNumber) as GroupName
FROM Events e
WHERE e.id = 2994;

-- 2. Member Details
SELECT 
    id,
    name,
    gender,
    memberId,
    email
FROM Users
WHERE id = 980;

-- 3. Check if member is in event's group
SELECT 
    COUNT(*) as isMember
FROM InviteGroupUsers
WHERE id = 980 AND groupNo = (
    SELECT GroupNumber FROM Events WHERE id = 2994
);

-- 4. Member's group memberships
SELECT 
    igu.groupNo,
    igt.groupName
FROM InviteGroupUsers igu
LEFT JOIN InviteEventGroupsText igt ON igu.groupNo = igt.groupNumber
WHERE igu.id = 980;

-- 5. EventUsers entry (when member was added)
SELECT 
    eu.*,
    u.name as user_name
FROM EventUsers eu
LEFT JOIN Users u ON eu.UserId = u.id
WHERE eu.EventId = 2994 AND eu.UserId = 980;

-- 6. Audit log entries
SELECT *
FROM EventAuditLog
WHERE EventId = 2994 AND UserId = 980
ORDER BY Timestamp DESC;

-- 7. Other members added around the same time (within 1 hour)
SELECT 
    eu.*,
    u.name as user_name,
    u.gender
FROM EventUsers eu
LEFT JOIN Users u ON eu.UserId = u.id
WHERE eu.EventId = 2994
AND eu.createdAt BETWEEN 
    (SELECT DATE_SUB(createdAt, INTERVAL 1 HOUR) FROM EventUsers WHERE EventId = 2994 AND UserId = 980)
    AND
    (SELECT DATE_ADD(createdAt, INTERVAL 1 HOUR) FROM EventUsers WHERE EventId = 2994 AND UserId = 980)
ORDER BY eu.createdAt;

-- 8. Check for gender mismatch
SELECT 
    e.id as EventId,
    e.event as EventName,
    e.groupType as EventGroupType,
    u.id as UserId,
    u.name as UserName,
    u.gender as UserGender,
    CASE 
        WHEN (e.groupType = 'Men' AND u.gender = 'F') THEN 'GENDER MISMATCH'
        WHEN (e.groupType = 'Women' AND u.gender = 'M') THEN 'GENDER MISMATCH'
        ELSE 'OK'
    END as Status
FROM Events e
JOIN EventUsers eu ON e.id = eu.EventId
JOIN Users u ON eu.UserId = u.id
WHERE e.id = 2994 AND u.id = 980;

-- 9. Check for non-group member enrollment
SELECT 
    e.id as EventId,
    e.event as EventName,
    e.GroupNumber as EventGroupNumber,
    u.id as UserId,
    u.name as UserName,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM InviteGroupUsers igu 
            WHERE igu.id = u.id AND igu.groupNo = e.GroupNumber
        ) THEN 'IN GROUP'
        ELSE 'NOT IN GROUP'
    END as GroupMembershipStatus
FROM Events e
JOIN EventUsers eu ON e.id = eu.EventId
JOIN Users u ON eu.UserId = u.id
WHERE e.id = 2994 AND u.id = 980;

