-- Diagnostic Queries for Empty Bet List Results

-- 1. Check if there are ANY sports bets with the required status
SELECT COUNT(*) as total_sports_bets,
       COUNT(CASE WHEN status IN ('OPEN', 'PENDING', 'open', 'matched', 'Match') THEN 1 END) as open_status_count,
       COUNT(CASE WHEN result_status = 'pending' THEN 1 END) as pending_result_count,
       COUNT(CASE WHEN status IN ('OPEN', 'PENDING', 'open', 'matched', 'Match') AND result_status = 'pending' THEN 1 END) as matching_both
FROM "SportsBet";

-- 2. Check if there are ANY casino bets with the required status
SELECT COUNT(*) as total_casino_bets,
       COUNT(CASE WHEN status IN ('pending', 'OPEN', 'open') THEN 1 END) as open_status_count,
       COUNT(CASE WHEN result_status IS NULL THEN 1 END) as null_result_count,
       COUNT(CASE WHEN status IN ('pending', 'OPEN', 'open') AND result_status IS NULL THEN 1 END) as matching_both
FROM "casino_bets";

-- 3. Check users with parent_staff_id in the allowed list
SELECT parent_staff_id, COUNT(*) as user_count
FROM "users"
WHERE parent_staff_id IN (2,27,3,28,29,5)
GROUP BY parent_staff_id;

-- 4. Check if those users have ANY bets (sports)
SELECT u.parent_staff_id, COUNT(sb.id) as bet_count
FROM "users" u
LEFT JOIN "SportsBet" sb ON u.user_id = sb.user_id
WHERE u.parent_staff_id IN (2,27,3,28,29,5)
GROUP BY u.parent_staff_id;

-- 5. Check if those users have ANY bets (casino)
SELECT u.parent_staff_id, COUNT(cb.id) as bet_count
FROM "users" u
LEFT JOIN "casino_bets" cb ON u.user_id = cb.user_id
WHERE u.parent_staff_id IN (2,27,3,28,29,5)
GROUP BY u.parent_staff_id;

-- 6. Check sports bets with status and date filters (without parent_staff_id filter)
SELECT COUNT(*) as count
FROM "SportsBet" sb
WHERE sb.status IN ('OPEN', 'PENDING', 'open', 'matched', 'Match') 
  AND sb.result_status = 'pending' 
  AND sb.created_at BETWEEN '2024-01-01T00:00:00.000Z' AND '2025-12-31T22:59:59.999Z';

-- 7. Check casino bets with status and date filters (without parent_staff_id filter)
SELECT COUNT(*) as count
FROM "casino_bets" cb
WHERE cb.status IN ('pending', 'OPEN', 'open') 
  AND cb.result_status IS NULL 
  AND cb.created_at BETWEEN '2024-01-01T00:00:00.000Z' AND '2025-12-31T22:59:59.999Z';

-- 8. Check what parent_staff_id values exist for users who have bets
SELECT DISTINCT u.parent_staff_id, COUNT(sb.id) as sports_bet_count
FROM "users" u
INNER JOIN "SportsBet" sb ON u.user_id = sb.user_id
WHERE sb.status IN ('OPEN', 'PENDING', 'open', 'matched', 'Match') 
  AND sb.result_status = 'pending'
GROUP BY u.parent_staff_id
ORDER BY sports_bet_count DESC;

-- 9. Check what parent_staff_id values exist for users who have casino bets
SELECT DISTINCT u.parent_staff_id, COUNT(cb.id) as casino_bet_count
FROM "users" u
INNER JOIN "casino_bets" cb ON u.user_id = cb.user_id
WHERE cb.status IN ('pending', 'OPEN', 'open') 
  AND cb.result_status IS NULL
GROUP BY u.parent_staff_id
ORDER BY casino_bet_count DESC;

-- 10. Sample of recent sports bets with user info
SELECT sb.id, sb.user_id, u.username, u.parent_staff_id, sb.status, sb.result_status, sb.created_at
FROM "SportsBet" sb
LEFT JOIN "users" u ON sb.user_id = u.user_id
ORDER BY sb.created_at DESC
LIMIT 10;

-- 11. Sample of recent casino bets with user info
SELECT cb.id, cb.user_id, u.username, u.parent_staff_id, cb.status, cb.result_status, cb.created_at
FROM "casino_bets" cb
LEFT JOIN "users" u ON cb.user_id = u.user_id
ORDER BY cb.created_at DESC
LIMIT 10;
