-- =============================================================================
-- ROSTER_DATA_AUDIT.sql  —  READ-ONLY roster / registration drift audit
-- =============================================================================
-- Created: July 28, 2026   (rev 2 — corrections from review pass applied)
-- Purpose: Count and sample every known mismatch between Registration.team /
--          Registration.group_id and Team.players, before rosters are built.
--          Companion doc: ROSTER_RECONCILIATION_PLAN.md
--
-- THIS SCRIPT CHANGES NOTHING. Every statement is a SELECT or a SHOW COLUMNS.
-- There is no INSERT / UPDATE / DELETE / ALTER / CREATE / SET anywhere in this
-- file. Safe to run on PROD, but run it on TEST first.
--
-- HOW TO RUN
--   On TEST (from the site root, where drush works):
--     drush sql:cli < ROSTER_DATA_AUDIT.sql > roster_audit_TEST_2026-07-28.txt 2>&1
--   On LOCAL (ddev):
--     ddev mysql < ROSTER_DATA_AUDIT.sql > roster_audit_LOCAL_2026-07-28.txt 2>&1
--   Section 1 alone gives the headline numbers; sections 2-5 give the rows behind
--   them. If you only want the summary, run sections 0 and 1.
--
-- STATUS VOCABULARY (Registration.status allowed values)
--   live   = paid, active           <- what the module *mostly* treats as real
--   dead   = cancelled, expired
--   limbo  = pending, waitlist      <- reported separately; the codebase
--                                      disagrees with itself about these
--
-- Note on "live": there are three competing definitions in the code today —
-- no filter at all (GroupController::getGroupSize and 6 other season queries),
-- paid+active (OrderCompleteSubscriber, Season capacity), and paid-only
-- (all of TeamBalancerService). This audit uses paid+active and reports the
-- limbo rows separately so you can see whether the disagreement matters here.
--
-- READING THE SUMMARY: the classes in section 1 OVERLAP BY DESIGN and the count
-- column MUST NOT BE SUMMED. One bad row can appear in several classes (a
-- cancelled tournament registration pointing at a deleted team is TF and TI, and
-- shows up again as a TA roster row). Each class answers "how many rows have
-- this specific defect", not "how many rows are bad in total".
--
-- TABLE / COLUMN REFERENCE (verified against the entity definitions and the
-- ddev db snapshot; section 0.0 re-verifies all seven tables at run time)
--   ccsoccer_registration : id, player, season, tournament, registration_type,
--                           team, commerce_order, status, group_id, invited_by,
--                           invitation_status, is_captain, ccsoccer_pool,
--                           cancellation_date, created, changed
--   team                  : id, season, tournament, name, captain, co_captain,
--                           group_id, status, max_roster_size, team_paid, weight
--   team__players         : entity_id, delta, players_target_id, deleted, langcode
--   ccsoccer_invitation   : id, inviter, invitee_email, invitee, season,
--                           group_id, team, token, status, notified, created
--   season                : id, name, league, max_group_size, max_players,
--                           groups_locked, active, registration_visible
--   tournament            : id, name, max_roster_size, max_teams, status,
--                           active, registration_visible
--   league                : id, name, max_group_size
--
-- KNOWN OUTPUT LIMIT: the GROUP_CONCAT columns truncate at group_concat_max_len
-- (1024 chars by default). Only affects the "all registrations for this player"
-- detail columns, and only for a player with an implausible number of rows.
-- =============================================================================


-- =============================================================================
-- SECTION 0 — INVENTORY AND SANITY CHECKS
-- Run this first. If 0.0 shows a column this script does not expect, stop and
-- fix the script rather than trusting the numbers below it.
-- =============================================================================

SELECT '=== 0.0 SCHEMA CHECK — ccsoccer_registration ===' AS section;
SHOW COLUMNS FROM ccsoccer_registration;

SELECT '=== 0.0 SCHEMA CHECK — team ===' AS section;
SHOW COLUMNS FROM team;

SELECT '=== 0.0 SCHEMA CHECK — team__players ===' AS section;
SHOW COLUMNS FROM team__players;

SELECT '=== 0.0 SCHEMA CHECK — ccsoccer_invitation ===' AS section;
SHOW COLUMNS FROM ccsoccer_invitation;

SELECT '=== 0.0 SCHEMA CHECK — season ===' AS section;
SHOW COLUMNS FROM season;

SELECT '=== 0.0 SCHEMA CHECK — tournament ===' AS section;
SHOW COLUMNS FROM tournament;

SELECT '=== 0.0 SCHEMA CHECK — league ===' AS section;
SHOW COLUMNS FROM league;


SELECT '=== 0.1 SEASONS ===' AS section;
SELECT
  s.id,
  s.name,
  l.name                                            AS league,
  s.active,
  s.registration_visible,
  s.groups_locked,
  s.max_players,
  s.max_group_size                                  AS season_group_cap_override,
  l.max_group_size                                  AS league_group_cap,
  COALESCE(NULLIF(s.max_group_size, 0), NULLIF(l.max_group_size, 0), 3) AS effective_group_cap
FROM season s
LEFT JOIN league l ON l.id = s.league
ORDER BY s.id;


SELECT '=== 0.2 TOURNAMENTS ===' AS section;
SELECT
  t.id,
  t.name,
  t.status,
  t.active,
  t.registration_visible,
  t.max_teams,
  t.max_roster_size AS tournament_default_roster_cap
FROM tournament t
ORDER BY t.id;


SELECT '=== 0.3 REGISTRATION STATUS DISTRIBUTION BY CONTAINER ===' AS section;
SELECT
  r.registration_type,
  r.season      AS season_id,
  r.tournament  AS tournament_id,
  COALESCE(s.name, tn.name, '(no container)') AS container,
  r.status,
  COUNT(*)                                    AS rows_count,
  SUM(CASE WHEN r.team IS NOT NULL AND r.team <> 0 THEN 1 ELSE 0 END)          AS with_team_ref,
  SUM(CASE WHEN r.group_id IS NOT NULL AND r.group_id <> '' THEN 1 ELSE 0 END) AS with_group_id
FROM ccsoccer_registration r
LEFT JOIN season s      ON s.id  = r.season
LEFT JOIN tournament tn ON tn.id = r.tournament
GROUP BY r.registration_type, r.season, r.tournament, s.name, tn.name, r.status
ORDER BY r.registration_type, container, r.status;


SELECT '=== 0.4 registration_type SANITY (open item from SESSION_HANDOFF — expect 0 rows) ===' AS section;
SELECT id, player, registration_type, season, tournament, team, group_id, status
FROM ccsoccer_registration
WHERE registration_type NOT IN ('season', 'tournament')
   OR registration_type IS NULL
ORDER BY id;


SELECT '=== 0.5 CONTAINER-REFERENCE SANITY (type vs which ref is populated) ===' AS section;
SELECT
  CASE
    WHEN (r.season IS NULL OR r.season = 0) AND (r.tournament IS NULL OR r.tournament = 0) THEN 'A. neither season nor tournament set'
    WHEN  r.season IS NOT NULL AND r.season <> 0 AND r.tournament IS NOT NULL AND r.tournament <> 0 THEN 'B. BOTH season and tournament set'
    WHEN  r.registration_type = 'season'     AND (r.season IS NULL OR r.season = 0)         THEN 'C. type=season but no season ref'
    WHEN  r.registration_type = 'tournament' AND (r.tournament IS NULL OR r.tournament = 0) THEN 'D. type=tournament but no tournament ref'
    ELSE 'E. consistent'
  END      AS finding,
  COUNT(*) AS rows_count
FROM ccsoccer_registration r
GROUP BY finding
ORDER BY finding;


-- =============================================================================
-- SECTION 1 — SUMMARY: ONE COUNT PER MISMATCH CLASS
-- Classes overlap; DO NOT SUM the count column. Details in sections 2-5.
--
-- TA / TB / TC / "correct" ARE mutually exclusive and together cover every
-- distinct (team, player) entry in team__players for a tournament team, so
-- those four alone can be reconciled against the roster-entry total.
-- =============================================================================

SELECT '=== 1. MISMATCH SUMMARY (classes overlap — do not sum) ===' AS section;

SELECT 'TA' AS class, 'roster entries' AS unit, 'TOURNAMENT ghost: on Team.players, NO live registration for the tournament' AS description, COUNT(*) AS n
FROM (
  SELECT DISTINCT tp.entity_id AS team_id, tp.players_target_id AS uid
  FROM team__players tp
  JOIN team t ON t.id = tp.entity_id
  WHERE tp.deleted = 0 AND t.tournament IS NOT NULL AND t.tournament <> 0
    AND NOT EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active'))
) d

UNION ALL
SELECT 'TB', 'roster entries', 'TOURNAMENT: on Team.players, live registration exists but its team is NULL', COUNT(*)
FROM (
  SELECT DISTINCT tp.entity_id AS team_id, tp.players_target_id AS uid
  FROM team__players tp
  JOIN team t ON t.id = tp.entity_id
  WHERE tp.deleted = 0 AND t.tournament IS NOT NULL AND t.tournament <> 0
    AND NOT EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
        AND r.team = t.id)
    AND EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
        AND (r.team IS NULL OR r.team = 0))
) d

UNION ALL
SELECT 'TC', 'roster entries', 'TOURNAMENT: on team X Team.players, every live registration points at another team', COUNT(*)
FROM (
  SELECT DISTINCT tp.entity_id AS team_id, tp.players_target_id AS uid
  FROM team__players tp
  JOIN team t ON t.id = tp.entity_id
  WHERE tp.deleted = 0 AND t.tournament IS NOT NULL AND t.tournament <> 0
    AND NOT EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
        AND r.team = t.id)
    AND NOT EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
        AND (r.team IS NULL OR r.team = 0))
    AND EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active'))
) d

UNION ALL
SELECT 'T-OK', 'roster entries', 'TOURNAMENT: roster entry with a matching live registration (control total)', COUNT(*)
FROM (
  SELECT DISTINCT tp.entity_id AS team_id, tp.players_target_id AS uid
  FROM team__players tp
  JOIN team t ON t.id = tp.entity_id
  WHERE tp.deleted = 0 AND t.tournament IS NOT NULL AND t.tournament <> 0
    AND EXISTS (
      SELECT 1 FROM ccsoccer_registration r
      WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
        AND r.team = t.id)
) d

UNION ALL
SELECT 'TD', 'registrations', 'TOURNAMENT: live Registration.team set, but player NOT on that team Team.players', COUNT(DISTINCT r.id)
FROM ccsoccer_registration r
JOIN team t ON t.id = r.team
WHERE r.registration_type = 'tournament'
  AND r.status IN ('paid', 'active')
  AND t.tournament = r.tournament
  AND NOT EXISTS (
    SELECT 1 FROM team__players tp
    WHERE tp.entity_id = t.id AND tp.deleted = 0 AND tp.players_target_id = r.player)

UNION ALL
SELECT 'TE', 'team+player pairs', 'TOURNAMENT: same player listed more than once in one team Team.players', COUNT(*)
FROM (
  SELECT tp.entity_id, tp.players_target_id
  FROM team__players tp
  JOIN team t ON t.id = tp.entity_id
  WHERE tp.deleted = 0 AND t.tournament IS NOT NULL AND t.tournament <> 0
  GROUP BY tp.entity_id, tp.players_target_id
  HAVING COUNT(*) > 1
) dup

UNION ALL
SELECT 'TF', 'registrations', 'TOURNAMENT: cancelled/expired registration still carrying team and/or group_id', COUNT(*)
FROM ccsoccer_registration r
WHERE r.registration_type = 'tournament'
  AND r.status IN ('cancelled', 'expired')
  AND ((r.team IS NOT NULL AND r.team <> 0) OR (r.group_id IS NOT NULL AND r.group_id <> ''))

UNION ALL
SELECT 'TG', 'registrations', 'TOURNAMENT: group_id set but matches no team in that tournament (points at nothing)', COUNT(*)
FROM ccsoccer_registration r
WHERE r.registration_type = 'tournament'
  AND r.group_id IS NOT NULL AND r.group_id <> ''
  AND NOT EXISTS (
    SELECT 1 FROM team t
    WHERE t.group_id = r.group_id AND t.tournament = r.tournament)

UNION ALL
SELECT 'TH', 'registrations', 'TOURNAMENT: group_id resolves to a team OTHER than Registration.team', COUNT(DISTINCT r.id)
FROM ccsoccer_registration r
JOIN team gt ON gt.group_id = r.group_id AND gt.tournament = r.tournament
WHERE r.registration_type = 'tournament'
  AND r.group_id IS NOT NULL AND r.group_id <> ''
  AND r.team IS NOT NULL AND r.team <> 0
  AND gt.id <> r.team

UNION ALL
SELECT 'TI', 'registrations', 'ANY: Registration.team references a team id that no longer exists', COUNT(*)
FROM ccsoccer_registration r
WHERE r.team IS NOT NULL AND r.team <> 0
  AND NOT EXISTS (SELECT 1 FROM team t WHERE t.id = r.team)

UNION ALL
SELECT 'TJ', 'teams', 'TOURNAMENT: captain and/or co_captain not present on own Team.players', COUNT(*)
FROM team t
WHERE t.tournament IS NOT NULL AND t.tournament <> 0
  AND (
    (t.captain IS NOT NULL AND t.captain <> 0
       AND NOT EXISTS (SELECT 1 FROM team__players tp WHERE tp.entity_id = t.id AND tp.deleted = 0 AND tp.players_target_id = t.captain))
    OR
    (t.co_captain IS NOT NULL AND t.co_captain <> 0
       AND NOT EXISTS (SELECT 1 FROM team__players tp WHERE tp.entity_id = t.id AND tp.deleted = 0 AND tp.players_target_id = t.co_captain))
  )

UNION ALL
SELECT 'TK', 'player+tournament pairs', 'TOURNAMENT: player holds MORE THAN ONE live registration for the same tournament', COUNT(*)
FROM (
  SELECT r.player, r.tournament
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
    AND r.tournament IS NOT NULL AND r.tournament <> 0
  GROUP BY r.player, r.tournament
  HAVING COUNT(*) > 1
) d

UNION ALL
SELECT 'TL', 'registrations', 'TOURNAMENT: Registration.team points at a team in a different tournament (or a season team)', COUNT(*)
FROM ccsoccer_registration r
JOIN team t ON t.id = r.team
WHERE r.registration_type = 'tournament'
  AND r.team IS NOT NULL AND r.team <> 0
  AND NOT (t.tournament <=> r.tournament)

UNION ALL
SELECT 'TM', 'player+tournament pairs', 'TOURNAMENT: player holds BOTH a dead AND a live registration for the same tournament (the population every bare reset() trips on)', COUNT(*)
FROM (
  SELECT r.player, r.tournament
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'tournament'
    AND r.tournament IS NOT NULL AND r.tournament <> 0
  GROUP BY r.player, r.tournament
  HAVING SUM(CASE WHEN r.status IN ('paid', 'active')       THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN r.status IN ('cancelled', 'expired') THEN 1 ELSE 0 END) > 0
) d

UNION ALL
SELECT 'SA', 'teams', 'SEASON: season team has a non-empty Team.players (model violation — expect 0)', COUNT(*)
FROM (
  SELECT t.id
  FROM team t
  JOIN team__players tp ON tp.entity_id = t.id AND tp.deleted = 0
  WHERE t.season IS NOT NULL AND t.season <> 0
  GROUP BY t.id
) d

UNION ALL
SELECT 'SB', 'registrations', 'SEASON: cancelled/expired registration still carrying group_id and/or invited_by and/or team', COUNT(*)
FROM ccsoccer_registration r
WHERE r.registration_type = 'season'
  AND r.status IN ('cancelled', 'expired')
  AND ((r.group_id IS NOT NULL AND r.group_id <> '')
    OR (r.invited_by IS NOT NULL AND r.invited_by <> 0)
    OR (r.team IS NOT NULL AND r.team <> 0))

UNION ALL
SELECT 'SC', 'groups', 'SEASON: group_id with ZERO live members left (group points at nothing)', COUNT(*)
FROM (
  SELECT r.season, r.group_id
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'season' AND r.group_id IS NOT NULL AND r.group_id <> ''
  GROUP BY r.season, r.group_id
  HAVING SUM(CASE WHEN r.status IN ('paid', 'active') THEN 1 ELSE 0 END) = 0
) d

UNION ALL
SELECT 'SD', 'groups', 'SEASON: group has live members and a manager row, but that manager row is NOT live', COUNT(*)
FROM (
  SELECT r.season, r.group_id
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'season' AND r.group_id IS NOT NULL AND r.group_id <> ''
  GROUP BY r.season, r.group_id
  HAVING SUM(CASE WHEN r.status IN ('paid', 'active') THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN (r.invited_by IS NULL OR r.invited_by = 0) THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN (r.invited_by IS NULL OR r.invited_by = 0) AND r.status IN ('paid', 'active') THEN 1 ELSE 0 END) = 0
) d

UNION ALL
SELECT 'SE', 'groups', 'SEASON: group has zero manager rows, or more than one manager row (any status)', COUNT(*)
FROM (
  SELECT r.season, r.group_id
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'season' AND r.group_id IS NOT NULL AND r.group_id <> ''
  GROUP BY r.season, r.group_id
  HAVING SUM(CASE WHEN (r.invited_by IS NULL OR r.invited_by = 0) THEN 1 ELSE 0 END) <> 1
) d

UNION ALL
SELECT 'SF', 'groups', 'SEASON: group counts as FULL only because of dead/limbo rows (getGroupSize inflated)', COUNT(*)
FROM (
  SELECT r.season, r.group_id,
         COUNT(*) AS rows_all,
         SUM(CASE WHEN r.status IN ('paid', 'active') THEN 1 ELSE 0 END) AS rows_live,
         COALESCE(NULLIF(s.max_group_size, 0), NULLIF(l.max_group_size, 0), 3) AS cap,
         (SELECT COUNT(*) FROM ccsoccer_invitation i
           WHERE i.group_id = r.group_id AND i.season = r.season AND i.status = 'pending') AS pending_invites
  FROM ccsoccer_registration r
  JOIN season s ON s.id = r.season
  LEFT JOIN league l ON l.id = s.league
  WHERE r.registration_type = 'season' AND r.group_id IS NOT NULL AND r.group_id <> ''
  GROUP BY r.season, r.group_id, s.max_group_size, l.max_group_size
) d
WHERE (d.rows_all + d.pending_invites) >= d.cap
  AND (d.rows_live + d.pending_invites) <  d.cap

UNION ALL
SELECT 'SG', 'registrations', 'SEASON: Registration.team points at a team belonging to a different season', COUNT(*)
FROM ccsoccer_registration r
JOIN team t ON t.id = r.team
WHERE r.registration_type = 'season'
  AND r.team IS NOT NULL AND r.team <> 0
  AND NOT (t.season <=> r.season)

UNION ALL
SELECT 'SH', 'player+season pairs', 'SEASON: player holds MORE THAN ONE live registration for the same season', COUNT(*)
FROM (
  SELECT r.player, r.season
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'season' AND r.status IN ('paid', 'active')
    AND r.season IS NOT NULL AND r.season <> 0
  GROUP BY r.player, r.season
  HAVING COUNT(*) > 1
) d

UNION ALL
SELECT 'SI', 'registrations', 'SEASON: legacy team_ prefixed group_id, or group_id colliding with a team.group_id', COUNT(*)
FROM ccsoccer_registration r
WHERE r.registration_type = 'season'
  AND r.group_id IS NOT NULL AND r.group_id <> ''
  AND (r.group_id LIKE 'team!_%' ESCAPE '!'
       OR EXISTS (SELECT 1 FROM team t WHERE t.group_id = r.group_id))

UNION ALL
SELECT 'SJ', 'player+season pairs', 'SEASON: player holds BOTH a dead AND a live registration for the same season (the population every bare reset() trips on)', COUNT(*)
FROM (
  SELECT r.player, r.season
  FROM ccsoccer_registration r
  WHERE r.registration_type = 'season'
    AND r.season IS NOT NULL AND r.season <> 0
  GROUP BY r.player, r.season
  HAVING SUM(CASE WHEN r.status IN ('paid', 'active')       THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN r.status IN ('cancelled', 'expired') THEN 1 ELSE 0 END) > 0
) d

UNION ALL
SELECT 'IA', 'invitations', 'INVITATION: pending season invite whose group has no live member left', COUNT(*)
FROM ccsoccer_invitation i
WHERE i.status = 'pending'
  AND i.group_id IS NOT NULL AND i.group_id <> ''
  AND (i.team IS NULL OR i.team = 0)
  AND NOT EXISTS (
    SELECT 1 FROM ccsoccer_registration r
    WHERE r.group_id = i.group_id
      AND r.season = i.season
      AND r.registration_type = 'season'
      AND r.status IN ('paid', 'active'))

UNION ALL
SELECT 'IB', 'invitations', 'INVITATION: pending team invite whose team no longer exists', COUNT(*)
FROM ccsoccer_invitation i
WHERE i.status = 'pending'
  AND i.team IS NOT NULL AND i.team <> 0
  AND NOT EXISTS (SELECT 1 FROM team t WHERE t.id = i.team)

UNION ALL
SELECT 'IC', 'invitations', 'INVITATION: pending invite where invitee ALREADY holds a live registration there (double-reserved slot)', COUNT(*)
FROM ccsoccer_invitation i
WHERE i.status = 'pending'
  AND i.invitee IS NOT NULL AND i.invitee <> 0
  AND EXISTS (
    SELECT 1 FROM ccsoccer_registration r
    WHERE r.player = i.invitee
      AND r.status IN ('paid', 'active')
      AND (r.season = i.season
           OR r.tournament = (SELECT t2.tournament FROM team t2 WHERE t2.id = i.team)))

ORDER BY class;


-- =============================================================================
-- SECTION 2 — TOURNAMENT CAPACITY IMPACT (the headline table)
-- This is what makes "Team is full" wrong and the pre-publish size check
-- unreliable. isFull() counts raw Team.players rows; players_with_live_reg is
-- what the roster would be after ghosts are removed. isFullIncludingPending()
-- (used for uninvited joiners) additionally reserves a slot per pending invite.
-- =============================================================================

SELECT '=== 2.1 PER-TEAM CAPACITY: coded count vs real count ===' AS section;
SELECT
  x.tournament,
  x.team_id,
  x.team_name,
  x.team_status,
  x.max_roster,
  x.players_raw,
  x.players_distinct,
  x.players_with_live_reg,
  x.players_distinct - x.players_with_live_reg AS ghost_players,
  x.live_regs_pointing_here,
  x.pending_invites,
  CASE WHEN x.max_roster IS NULL OR x.max_roster = 0 THEN 'no limit'
       WHEN x.players_raw >= x.max_roster           THEN 'FULL (as coded today)'
       ELSE 'has room' END                          AS isfull_today,
  CASE WHEN x.max_roster IS NULL OR x.max_roster = 0 THEN 'no limit'
       WHEN x.players_with_live_reg >= x.max_roster  THEN 'full'
       ELSE 'has room' END                          AS isfull_after_cleanup,
  CASE WHEN x.max_roster IS NULL OR x.max_roster = 0 THEN 'no limit'
       WHEN x.players_raw + x.pending_invites >= x.max_roster THEN 'FULL incl pending (as coded today)'
       ELSE 'has room incl pending' END             AS isfullincludingpending_today
FROM (
  SELECT
    tr.name   AS tournament,
    t.id      AS team_id,
    t.name    AS team_name,
    t.status  AS team_status,
    COALESCE(NULLIF(t.max_roster_size, 0), tr.max_roster_size) AS max_roster,
    (SELECT COUNT(*)                             FROM team__players tp WHERE tp.entity_id = t.id AND tp.deleted = 0) AS players_raw,
    (SELECT COUNT(DISTINCT tp.players_target_id) FROM team__players tp WHERE tp.entity_id = t.id AND tp.deleted = 0) AS players_distinct,
    (SELECT COUNT(DISTINCT tp.players_target_id)
       FROM team__players tp
       JOIN ccsoccer_registration r
         ON r.player = tp.players_target_id AND r.tournament = t.tournament
        AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
      WHERE tp.entity_id = t.id AND tp.deleted = 0)                                       AS players_with_live_reg,
    (SELECT COUNT(DISTINCT r.player) FROM ccsoccer_registration r
      WHERE r.team = t.id AND r.registration_type = 'tournament'
        AND r.tournament = t.tournament AND r.status IN ('paid', 'active'))               AS live_regs_pointing_here,
    (SELECT COUNT(*) FROM ccsoccer_invitation i
      WHERE i.team = t.id AND i.status = 'pending')                                       AS pending_invites
  FROM team t
  JOIN tournament tr ON tr.id = t.tournament
  WHERE t.tournament IS NOT NULL AND t.tournament <> 0
) x
ORDER BY x.tournament, ghost_players DESC, x.team_name;


-- =============================================================================
-- SECTION 3 — TOURNAMENT DETAIL ROWS
-- Player names come from scalar subqueries, not joins, so no row is duplicated
-- by a multi-langcode name field.
-- =============================================================================

SELECT '=== 3.1 TA — GHOST ROSTER MEMBERS (on Team.players, no live registration) ===' AS section;
SELECT
  tr.name  AS tournament,
  t.id     AS team_id,
  t.name   AS team_name,
  tp.players_target_id AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = tp.players_target_id AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = tp.players_target_id AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', tp.players_target_id)) AS player,
  CASE
    WHEN EXISTS (SELECT 1 FROM ccsoccer_registration r2
                  WHERE r2.player = tp.players_target_id AND r2.tournament = t.tournament
                    AND r2.status IN ('cancelled', 'expired'))
      THEN 'TA1 cancelled/expired registration exists'
    WHEN EXISTS (SELECT 1 FROM ccsoccer_registration r3
                  WHERE r3.player = tp.players_target_id AND r3.tournament = t.tournament)
      THEN 'TA2 only pending/waitlist registration'
    ELSE 'TA3 no registration at all for this tournament'
  END AS subclass,
  (SELECT GROUP_CONCAT(CONCAT(r4.id, ':', r4.status, ':', r4.registration_type) ORDER BY r4.id SEPARATOR ' ')
     FROM ccsoccer_registration r4
    WHERE r4.player = tp.players_target_id AND r4.tournament = t.tournament) AS all_regs,
  (SELECT FROM_UNIXTIME(MAX(r5.cancellation_date))
     FROM ccsoccer_registration r5
    WHERE r5.player = tp.players_target_id AND r5.tournament = t.tournament
      AND r5.status = 'cancelled')                                           AS last_cancelled_on,
  CASE WHEN t.captain    = tp.players_target_id THEN 'CAPTAIN'
       WHEN t.co_captain = tp.players_target_id THEN 'CO-CAPTAIN'
       ELSE '' END AS role
FROM team__players tp
JOIN team t            ON t.id  = tp.entity_id
LEFT JOIN tournament tr ON tr.id = t.tournament
WHERE tp.deleted = 0
  AND t.tournament IS NOT NULL AND t.tournament <> 0
  AND NOT EXISTS (
    SELECT 1 FROM ccsoccer_registration r
    WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
      AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active'))
ORDER BY subclass, tournament, t.name, uid
LIMIT 300;


SELECT '=== 3.2 TB/TC — ON ROSTER BUT NO LIVE REGISTRATION POINTS BACK AT THIS TEAM ===' AS section;
SELECT
  tr.name AS tournament,
  t.id    AS roster_team_id,
  t.name  AS roster_team_name,
  tp.players_target_id AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = tp.players_target_id AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = tp.players_target_id AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', tp.players_target_id)) AS player,
  CASE WHEN EXISTS (
         SELECT 1 FROM ccsoccer_registration r
         WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
           AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
           AND (r.team IS NULL OR r.team = 0))
       THEN 'TB live registration has no team'
       ELSE 'TC live registration points at a different team' END AS subclass,
  (SELECT GROUP_CONCAT(CONCAT(r6.id, ':', r6.status, ':team=', COALESCE(r6.team, 'NULL')) ORDER BY r6.id SEPARATOR ' | ')
     FROM ccsoccer_registration r6
    WHERE r6.player = tp.players_target_id AND r6.tournament = t.tournament
      AND r6.registration_type = 'tournament' AND r6.status IN ('paid', 'active')) AS live_regs,
  CASE WHEN t.captain    = tp.players_target_id THEN 'CAPTAIN'
       WHEN t.co_captain = tp.players_target_id THEN 'CO-CAPTAIN'
       ELSE '' END AS role
FROM team__players tp
JOIN team t             ON t.id  = tp.entity_id
LEFT JOIN tournament tr ON tr.id = t.tournament
WHERE tp.deleted = 0
  AND t.tournament IS NOT NULL AND t.tournament <> 0
  AND NOT EXISTS (
    SELECT 1 FROM ccsoccer_registration r
    WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
      AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
      AND r.team = t.id)
  AND EXISTS (
    SELECT 1 FROM ccsoccer_registration r
    WHERE r.player = tp.players_target_id AND r.tournament = t.tournament
      AND r.registration_type = 'tournament' AND r.status IN ('paid', 'active'))
ORDER BY subclass, tournament, t.name, uid
LIMIT 300;


SELECT '=== 3.3 TD/TL — LIVE REGISTRATION NAMES A TEAM THAT DOES NOT LIST THE PLAYER ===' AS section;
SELECT
  COALESCE(tr.name, '(team not in this tournament)') AS tournament,
  t.id    AS team_id,
  t.name  AS team_name,
  t.tournament AS team_tournament_id,
  r.tournament AS registration_tournament_id,
  r.id    AS registration_id,
  r.status,
  r.invitation_status,
  r.is_captain,
  r.ccsoccer_pool,
  r.group_id,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = r.player AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player,
  CASE WHEN NOT (t.tournament <=> r.tournament) THEN 'TL team is in a different tournament'
       ELSE 'TD missing from Team.players' END AS subclass
FROM ccsoccer_registration r
JOIN team t             ON t.id  = r.team
LEFT JOIN tournament tr ON tr.id = t.tournament
WHERE r.registration_type = 'tournament'
  AND r.status IN ('paid', 'active')
  AND (
    NOT (t.tournament <=> r.tournament)
    OR NOT EXISTS (
      SELECT 1 FROM team__players tp
      WHERE tp.entity_id = t.id AND tp.deleted = 0 AND tp.players_target_id = r.player)
  )
ORDER BY subclass, tournament, t.name, uid
LIMIT 300;


SELECT '=== 3.4 TE — DUPLICATE Team.players ENTRIES ===' AS section;
SELECT
  tr.name AS tournament,
  t.id    AS team_id,
  t.name  AS team_name,
  tp.players_target_id AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = tp.players_target_id AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = tp.players_target_id AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', tp.players_target_id)) AS player,
  COUNT(*)     AS delta_rows,
  COUNT(*) - 1 AS excess_rows
FROM team__players tp
JOIN team t             ON t.id  = tp.entity_id
LEFT JOIN tournament tr ON tr.id = t.tournament
WHERE tp.deleted = 0 AND t.tournament IS NOT NULL AND t.tournament <> 0
GROUP BY tr.name, t.id, t.name, tp.players_target_id
HAVING COUNT(*) > 1
ORDER BY delta_rows DESC, tournament, t.name
LIMIT 100;


SELECT '=== 3.5 TF — CANCELLED TOURNAMENT REGISTRATIONS STILL CARRYING team / group_id ===' AS section;
SELECT
  COALESCE(tr.name, '(no tournament)') AS tournament,
  r.id    AS registration_id,
  r.status,
  FROM_UNIXTIME(r.cancellation_date) AS cancelled_on,
  r.team  AS still_points_at_team,
  t.name  AS team_name,
  r.group_id,
  r.invited_by,
  r.is_captain,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = r.player AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player,
  CASE WHEN EXISTS (SELECT 1 FROM team__players tp
                     WHERE tp.entity_id = r.team AND tp.deleted = 0 AND tp.players_target_id = r.player)
       THEN 'YES — also still on Team.players' ELSE 'no' END AS still_on_roster
FROM ccsoccer_registration r
LEFT JOIN tournament tr ON tr.id = r.tournament
LEFT JOIN team t        ON t.id  = r.team
WHERE r.registration_type = 'tournament'
  AND r.status IN ('cancelled', 'expired')
  AND ((r.team IS NOT NULL AND r.team <> 0) OR (r.group_id IS NOT NULL AND r.group_id <> ''))
ORDER BY tournament, uid
LIMIT 300;


SELECT '=== 3.6 TG/TH — TOURNAMENT group_id POINTING AT NOTHING OR AT THE WRONG TEAM ===' AS section;
SELECT
  COALESCE(tr.name, '(no tournament)') AS tournament,
  r.id    AS registration_id,
  r.status,
  r.group_id,
  r.team  AS registration_team_id,
  t.name  AS registration_team_name,
  (SELECT gt.id   FROM team gt WHERE gt.group_id = r.group_id AND gt.tournament = r.tournament LIMIT 1) AS group_id_resolves_to_team,
  (SELECT gt.name FROM team gt WHERE gt.group_id = r.group_id AND gt.tournament = r.tournament LIMIT 1) AS group_id_team_name,
  (SELECT COUNT(*) FROM team gt WHERE gt.group_id = r.group_id) AS teams_sharing_this_group_id,
  CASE WHEN NOT EXISTS (SELECT 1 FROM team gt WHERE gt.group_id = r.group_id AND gt.tournament = r.tournament)
         THEN 'TG group_id matches no team in this tournament'
       ELSE 'TH group_id team <> Registration.team' END AS subclass,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = r.player AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player
FROM ccsoccer_registration r
LEFT JOIN tournament tr ON tr.id = r.tournament
LEFT JOIN team t        ON t.id  = r.team
WHERE r.registration_type = 'tournament'
  AND r.group_id IS NOT NULL AND r.group_id <> ''
  AND (
    NOT EXISTS (SELECT 1 FROM team gt WHERE gt.group_id = r.group_id AND gt.tournament = r.tournament)
    OR (r.team IS NOT NULL AND r.team <> 0
        AND EXISTS (SELECT 1 FROM team gt WHERE gt.group_id = r.group_id AND gt.tournament = r.tournament AND gt.id <> r.team))
  )
ORDER BY subclass, tournament, uid
LIMIT 300;


SELECT '=== 3.7 TI — Registration.team POINTS AT A DELETED TEAM ===' AS section;
SELECT
  r.id AS registration_id,
  r.registration_type,
  r.status,
  r.season,
  r.tournament,
  r.team AS dangling_team_id,
  r.group_id,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = r.player AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player
FROM ccsoccer_registration r
WHERE r.team IS NOT NULL AND r.team <> 0
  AND NOT EXISTS (SELECT 1 FROM team t WHERE t.id = r.team)
ORDER BY r.id
LIMIT 300;


SELECT '=== 3.8 TJ — CAPTAIN / CO-CAPTAIN NOT ON OWN ROSTER ===' AS section;
SELECT
  tr.name  AS tournament,
  t.id     AS team_id,
  t.name   AS team_name,
  t.status AS team_status,
  t.captain AS captain_uid,
  CASE WHEN t.captain IS NULL OR t.captain = 0 THEN 'no captain'
       WHEN EXISTS (SELECT 1 FROM team__players tp WHERE tp.entity_id = t.id AND tp.deleted = 0 AND tp.players_target_id = t.captain) THEN 'on roster'
       ELSE 'MISSING from Team.players' END AS captain_on_roster,
  (SELECT GROUP_CONCAT(CONCAT(r.id, ':', r.status) ORDER BY r.id SEPARATOR ' ')
     FROM ccsoccer_registration r WHERE r.player = t.captain AND r.tournament = t.tournament) AS captain_regs,
  t.co_captain AS co_captain_uid,
  CASE WHEN t.co_captain IS NULL OR t.co_captain = 0 THEN 'no co-captain'
       WHEN EXISTS (SELECT 1 FROM team__players tp WHERE tp.entity_id = t.id AND tp.deleted = 0 AND tp.players_target_id = t.co_captain) THEN 'on roster'
       ELSE 'MISSING from Team.players' END AS co_captain_on_roster,
  (SELECT GROUP_CONCAT(CONCAT(r.id, ':', r.status) ORDER BY r.id SEPARATOR ' ')
     FROM ccsoccer_registration r WHERE r.player = t.co_captain AND r.tournament = t.tournament) AS co_captain_regs
FROM team t
LEFT JOIN tournament tr ON tr.id = t.tournament
WHERE t.tournament IS NOT NULL AND t.tournament <> 0
ORDER BY tournament, t.name;


SELECT '=== 3.9 TK — PLAYERS WITH MULTIPLE LIVE TOURNAMENT REGISTRATIONS (reset() hazard) ===' AS section;
SELECT
  r.tournament AS tournament_id,
  (SELECT tr.name FROM tournament tr WHERE tr.id = r.tournament) AS tournament,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = r.player AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player,
  COUNT(*) AS live_registrations,
  GROUP_CONCAT(CONCAT(r.id, ':', r.status, ':team=', COALESCE(r.team, 'NULL')) ORDER BY r.id SEPARATOR ' | ') AS rows_detail
FROM ccsoccer_registration r
WHERE r.registration_type = 'tournament' AND r.status IN ('paid', 'active')
  AND r.tournament IS NOT NULL AND r.tournament <> 0
GROUP BY r.tournament, r.player
HAVING COUNT(*) > 1
ORDER BY live_registrations DESC, tournament_id
LIMIT 100;


SELECT '=== 3.10 TM/SJ — PLAYERS HOLDING BOTH A DEAD AND A LIVE REGISTRATION ===' AS section;
-- This is the population that every bare reset() in the module resolves wrongly:
-- loadByProperties() returns both rows keyed by id, and reset() takes the LOWEST,
-- which is the older (cancelled) one. Six confirmed bugs so far. If this comes
-- back 0 for all three containers, CF4 cannot bite on current data.
SELECT
  r.registration_type,
  COALESCE(
    (SELECT s.name  FROM season s      WHERE s.id  = r.season),
    (SELECT tr.name FROM tournament tr WHERE tr.id = r.tournament)) AS container,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l.field_last_name_value  FROM user__field_last_name  l WHERE l.entity_id = r.player AND l.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player,
  COUNT(*) AS total_rows,
  SUM(CASE WHEN r.status IN ('paid', 'active')       THEN 1 ELSE 0 END) AS live_rows,
  SUM(CASE WHEN r.status IN ('cancelled', 'expired') THEN 1 ELSE 0 END) AS dead_rows,
  MIN(r.id) AS lowest_id_what_reset_takes,
  MAX(CASE WHEN r.status IN ('paid', 'active') THEN r.id END) AS newest_live_id_what_it_should_take,
  GROUP_CONCAT(CONCAT(r.id, ':', r.status, ':team=', COALESCE(r.team, 'NULL')) ORDER BY r.id SEPARATOR ' | ') AS rows_detail
FROM ccsoccer_registration r
GROUP BY r.registration_type, r.season, r.tournament, r.player
HAVING SUM(CASE WHEN r.status IN ('paid', 'active')       THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN r.status IN ('cancelled', 'expired') THEN 1 ELSE 0 END) > 0
ORDER BY r.registration_type, container, uid
LIMIT 200;


-- =============================================================================
-- SECTION 4 — SEASON DETAIL ROWS
-- =============================================================================

SELECT '=== 4.1 SA — SEASON TEAMS WITH NON-EMPTY Team.players (expect 0 rows) ===' AS section;
SELECT
  s.name  AS season,
  t.id    AS team_id,
  t.name  AS team_name,
  COUNT(*) AS player_rows
FROM team t
LEFT JOIN season s    ON s.id = t.season
JOIN team__players tp ON tp.entity_id = t.id AND tp.deleted = 0
WHERE t.season IS NOT NULL AND t.season <> 0
GROUP BY s.name, t.id, t.name
ORDER BY player_rows DESC
LIMIT 100;


SELECT '=== 4.2 SEASON GROUP INTEGRITY — every group, all counts ===' AS section;
SELECT
  d.season_id,
  d.season,
  d.group_id,
  d.cap                                            AS group_cap,
  d.rows_all,
  d.rows_live,
  d.rows_dead,
  d.rows_limbo,
  d.pending_invites,
  d.rows_all  + d.pending_invites                  AS getgroupsize_today,
  d.rows_live + d.pending_invites                  AS getgroupsize_after_cleanup,
  d.manager_rows_all,
  d.manager_rows_live,
  CASE WHEN d.rows_live = 0                                    THEN 'SC group has no live members'
       WHEN d.manager_rows_all = 0                             THEN 'SE no manager row'
       WHEN d.manager_rows_all > 1                             THEN 'SE multiple manager rows'
       WHEN d.manager_rows_live = 0                            THEN 'SD manager row is dead'
       WHEN (d.rows_all  + d.pending_invites) >= d.cap
        AND (d.rows_live + d.pending_invites) <  d.cap         THEN 'SF falsely full'
       WHEN d.rows_dead > 0                                    THEN 'SB carries dead rows'
       ELSE 'ok' END                               AS finding
FROM (
  SELECT
    r.season    AS season_id,
    (SELECT s2.name FROM season s2 WHERE s2.id = r.season) AS season,
    r.group_id  AS group_id,
    COALESCE(NULLIF(s.max_group_size, 0), NULLIF(l.max_group_size, 0), 3) AS cap,
    COUNT(*)                                                                     AS rows_all,
    SUM(CASE WHEN r.status IN ('paid', 'active')       THEN 1 ELSE 0 END)        AS rows_live,
    SUM(CASE WHEN r.status IN ('cancelled', 'expired') THEN 1 ELSE 0 END)        AS rows_dead,
    SUM(CASE WHEN r.status IN ('pending', 'waitlist')  THEN 1 ELSE 0 END)        AS rows_limbo,
    SUM(CASE WHEN (r.invited_by IS NULL OR r.invited_by = 0) THEN 1 ELSE 0 END)  AS manager_rows_all,
    SUM(CASE WHEN (r.invited_by IS NULL OR r.invited_by = 0)
                  AND r.status IN ('paid', 'active')   THEN 1 ELSE 0 END)        AS manager_rows_live,
    (SELECT COUNT(*) FROM ccsoccer_invitation i
      WHERE i.group_id = r.group_id AND i.season = r.season AND i.status = 'pending') AS pending_invites
  FROM ccsoccer_registration r
  LEFT JOIN season s ON s.id = r.season
  LEFT JOIN league l ON l.id = s.league
  WHERE r.registration_type = 'season'
    AND r.group_id IS NOT NULL AND r.group_id <> ''
  GROUP BY r.season, r.group_id, s.max_group_size, l.max_group_size
) d
ORDER BY CASE WHEN d.rows_live = 0 THEN 0 ELSE 1 END, d.season_id, d.group_id;


SELECT '=== 4.3 SB — CANCELLED/EXPIRED SEASON REGISTRATIONS STILL IN A GROUP OR ON A TEAM ===' AS section;
SELECT
  COALESCE(s.name, '(no season)') AS season,
  r.id   AS registration_id,
  r.status,
  FROM_UNIXTIME(r.cancellation_date) AS cancelled_on,
  r.group_id,
  CASE WHEN (r.invited_by IS NULL OR r.invited_by = 0) THEN 'MANAGER' ELSE 'member' END AS group_role,
  r.invited_by,
  r.invitation_status,
  r.team AS still_points_at_team,
  t.name AS team_name,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l2.field_last_name_value FROM user__field_last_name  l2 WHERE l2.entity_id = r.player AND l2.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player,
  (SELECT COUNT(*) FROM ccsoccer_registration r2
    WHERE r2.group_id = r.group_id AND r2.season = r.season
      AND r2.status IN ('paid', 'active')) AS live_members_left_in_group
FROM ccsoccer_registration r
LEFT JOIN season s ON s.id = r.season
LEFT JOIN team t   ON t.id = r.team
WHERE r.registration_type = 'season'
  AND r.status IN ('cancelled', 'expired')
  AND ((r.group_id IS NOT NULL AND r.group_id <> '')
    OR (r.invited_by IS NOT NULL AND r.invited_by <> 0)
    OR (r.team IS NOT NULL AND r.team <> 0))
ORDER BY season, group_role, uid
LIMIT 300;


SELECT '=== 4.4 SG/SI — SEASON team / group_id REFERENCE PROBLEMS ===' AS section;
SELECT
  COALESCE(s.name, '(no season)') AS season,
  r.id   AS registration_id,
  r.status,
  r.season AS registration_season_id,
  r.team,
  t.season AS team_belongs_to_season,
  r.group_id,
  CASE
    WHEN r.team IS NOT NULL AND r.team <> 0 AND NOT (t.season <=> r.season)
      THEN 'SG team belongs to another season (or is a tournament team)'
    WHEN r.group_id LIKE 'team!_%' ESCAPE '!'
      THEN 'SI legacy team_ group_id format'
    ELSE 'SI group_id collides with a team.group_id'
  END AS finding,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l2.field_last_name_value FROM user__field_last_name  l2 WHERE l2.entity_id = r.player AND l2.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player
FROM ccsoccer_registration r
LEFT JOIN season s ON s.id = r.season
LEFT JOIN team t   ON t.id = r.team
WHERE r.registration_type = 'season'
  AND (
    (r.team IS NOT NULL AND r.team <> 0 AND NOT (t.season <=> r.season))
    OR (r.group_id LIKE 'team!_%' ESCAPE '!')
    OR (r.group_id IS NOT NULL AND r.group_id <> ''
        AND EXISTS (SELECT 1 FROM team t2 WHERE t2.group_id = r.group_id))
  )
ORDER BY finding, season, uid
LIMIT 300;


SELECT '=== 4.5 SH — PLAYERS WITH MULTIPLE LIVE SEASON REGISTRATIONS ===' AS section;
SELECT
  r.season AS season_id,
  (SELECT s2.name FROM season s2 WHERE s2.id = r.season) AS season,
  r.player AS uid,
  COALESCE(NULLIF(CONCAT_WS(' ',
    (SELECT f.field_first_name_value FROM user__field_first_name f WHERE f.entity_id = r.player AND f.deleted = 0 LIMIT 1),
    (SELECT l2.field_last_name_value FROM user__field_last_name  l2 WHERE l2.entity_id = r.player AND l2.deleted = 0 LIMIT 1)
  ), ''), CONCAT('uid ', r.player)) AS player,
  COUNT(*) AS live_registrations,
  GROUP_CONCAT(CONCAT(r.id, ':', r.status, ':grp=', COALESCE(r.group_id, 'NULL')) ORDER BY r.id SEPARATOR ' | ') AS rows_detail
FROM ccsoccer_registration r
WHERE r.registration_type = 'season' AND r.status IN ('paid', 'active')
  AND r.season IS NOT NULL AND r.season <> 0
GROUP BY r.season, r.player
HAVING COUNT(*) > 1
ORDER BY live_registrations DESC, season_id
LIMIT 100;


SELECT '=== 4.6 SEASON ROSTER STATE — registrations per season team ===' AS section;
SELECT
  COALESCE(s.name, '(no season)') AS season,
  t.id   AS team_id,
  t.name AS team_name,
  SUM(CASE WHEN r.status IN ('paid', 'active')       THEN 1 ELSE 0 END) AS live_players,
  SUM(CASE WHEN r.status = 'paid'                    THEN 1 ELSE 0 END) AS paid_only_balancer_sees,
  SUM(CASE WHEN r.status IN ('cancelled', 'expired') THEN 1 ELSE 0 END) AS dead_players_still_assigned,
  SUM(CASE WHEN r.status IN ('pending', 'waitlist')  THEN 1 ELSE 0 END) AS limbo_players
FROM team t
LEFT JOIN season s ON s.id = t.season
LEFT JOIN ccsoccer_registration r ON r.team = t.id AND r.registration_type = 'season'
WHERE t.season IS NOT NULL AND t.season <> 0
GROUP BY s.name, t.id, t.name
ORDER BY season, t.name;


SELECT '=== 4.7 UNASSIGNED LIVE SEASON REGISTRATIONS (workbench population) ===' AS section;
SELECT
  r.season AS season_id,
  (SELECT s2.name FROM season s2 WHERE s2.id = r.season) AS season,
  r.status,
  SUM(CASE WHEN r.group_id IS NULL OR r.group_id = ''       THEN 1 ELSE 0 END) AS ungrouped,
  SUM(CASE WHEN r.group_id IS NOT NULL AND r.group_id <> '' THEN 1 ELSE 0 END) AS grouped,
  COUNT(*) AS total_unassigned_to_a_team
FROM ccsoccer_registration r
WHERE r.registration_type = 'season'
  AND r.status IN ('paid', 'active')
  AND (r.team IS NULL OR r.team = 0)
GROUP BY r.season, r.status
ORDER BY season_id, r.status;


-- =============================================================================
-- SECTION 5 — INVITATIONS THAT RESERVE CAPACITY
-- Pending invitations are counted as occupied slots by getGroupSize() and by
-- Team::getRosterStats(), so a stale one holds a spot indefinitely (E5).
-- Problem rows sort FIRST.
-- =============================================================================

SELECT '=== 5.1 PENDING INVITATION HEALTH (problems first) ===' AS section;
SELECT
  i.id AS invitation_id,
  COALESCE(s.name, tr.name, '(no container)') AS container,
  i.group_id,
  i.team AS team_id,
  t.name AS team_name,
  i.inviter,
  i.invitee AS invitee_uid,
  i.invitee_email,
  FROM_UNIXTIME(i.created)  AS invited_on,
  FROM_UNIXTIME(i.notified) AS last_notified,
  DATEDIFF(NOW(), FROM_UNIXTIME(i.created)) AS days_outstanding,
  CASE
    WHEN i.team IS NOT NULL AND i.team <> 0
         AND NOT EXISTS (SELECT 1 FROM team t2 WHERE t2.id = i.team)
      THEN 'IB team no longer exists'
    WHEN (i.team IS NULL OR i.team = 0)
         AND i.group_id IS NOT NULL AND i.group_id <> ''
         AND NOT EXISTS (SELECT 1 FROM ccsoccer_registration r
                          WHERE r.group_id = i.group_id AND r.season = i.season
                            AND r.registration_type = 'season'
                            AND r.status IN ('paid', 'active'))
      THEN 'IA group has no live member'
    WHEN i.invitee IS NOT NULL AND i.invitee <> 0
         AND EXISTS (SELECT 1 FROM ccsoccer_registration r
                      WHERE r.player = i.invitee
                        AND r.status IN ('paid', 'active')
                        AND (r.season = i.season
                             OR r.tournament = (SELECT t2.tournament FROM team t2 WHERE t2.id = i.team)))
      THEN 'IC invitee already registered here'
    ELSE 'ok'
  END AS finding
FROM ccsoccer_invitation i
LEFT JOIN season s      ON s.id  = i.season
LEFT JOIN team t        ON t.id  = i.team
LEFT JOIN tournament tr ON tr.id = t.tournament
WHERE i.status = 'pending'
ORDER BY CASE WHEN finding = 'ok' THEN 1 ELSE 0 END, finding, days_outstanding DESC
LIMIT 300;


SELECT '=== 5.2 INVITATION STATUS DISTRIBUTION ===' AS section;
SELECT
  CASE WHEN i.team IS NOT NULL AND i.team <> 0 THEN 'tournament (team invite)' ELSE 'season (group invite)' END AS kind,
  i.status,
  COUNT(*) AS n
FROM ccsoccer_invitation i
GROUP BY kind, i.status
ORDER BY kind, i.status;


SELECT '=== AUDIT COMPLETE — nothing was modified ===' AS section;