-- III online play — trimmed schema for the Godot client's first online -- slice. Adapted from reference/schema.sql, keeping only what -- games+players actually need for humans-vs-humans normal-mode matches -- with no accounts (guest seat tokens only). See PORT_PLAN.md's -- online-play plan, Phase 0, for the full column-by-column rationale — -- every "drop" listed there (ranked, friends, spectating, push, -- accounts, chat, killcam, stats) is simply absent here rather than -- commented out, to keep this file short and easy to read end to end. -- "ascension" was on that original drop list too, but has since been -- added back (see games.mode/ascension_runs/ascension_unlocked_json/ -- ascension_reward_json below) — requested directly by the user as a -- co-op online mode; see PORT_PLAN.md's own Ascension-co-op entry. -- "bans/custom-lives" was also on that drop list — added back as -- 'custom' mode (see games.mode/banned_cards_json below), requested -- directly by the user ("I want to start implementing Custom mode next -- to the Standard game. Where the host can chose witch how many lives -- each player starts And ban cards from the pool for this lobby"). -- "friends" was also on that drop list — added back below (see -- users.last_seen_at/friends/lobby_invites) as a real friends list, -- requested directly by the user ("let's build our own friends list -- first we'll see the rest later"). DROP TABLE IF EXISTS game_spectators; DROP TABLE IF EXISTS ascension_runs; DROP TABLE IF EXISTS lobby_invites; DROP TABLE IF EXISTS friends; DROP TABLE IF EXISTS players; DROP TABLE IF EXISTS games; DROP TABLE IF EXISTS users; CREATE TABLE games ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- Short human-typeable room code players share to join each other, -- e.g. "K7QX2M" — generated by lib/util.php's generate_room_code(). code CHAR(6) NOT NULL, status ENUM('lobby','active','finished') NOT NULL DEFAULT 'lobby', -- 'ascension' — requested directly by the user ("start -- implémentation Ascension for co op 1-4 online play same rules -- each player has his on build"), a co-op roguelike mode distinct -- from 'normal' free-for-all play: 1-4 humans share one board and -- fight the same enemy wave, each keeping their own independent -- hp/lives/unlocked-card build (see players.ascension_unlocked_json -- below). -- 'custom' — requested directly by the user ("Custom mode next to -- the Standard game... host can chose how many lives each player -- starts And ban cards from the pool for this lobby"): a real mode -- value (not 'normal' plus a sniffed config flag — that design was -- explicitly rejected), matching Ascension's own "extend the enum" -- precedent. Mechanically identical to 'normal' free-for-all play -- (same win condition, same combat, same client HUD — the Godot -- client needs zero gameplay branching for it), differing only in -- the seeded starting_lives value (see that column below) and the -- banned_cards_json deal-pool exclusion list (see below). HP always -- stays a fixed 3, same as 'normal' — only lives are configurable. -- Extends the enum rather than a separate boolean column — matches -- this schema's own established "extend the enum" pattern for -- mode-like flags on games. -- 'ranked' — requested directly by the user ("Let's start -- implementing a Ranked system With matchmaking in queues"), see -- PORT_PLAN.md's own Ranked-matchmaking entry. Same "zero client- -- side gameplay branching" precedent 'custom' already establishes — -- mechanically identical to 'normal' (fixed 3 lives, no banned -- cards, see queue_state.php's own match-creation step), differing -- only in HOW the game came to exist (matchmade from 4 queued -- accounts rather than a host-shared room code) and that every one -- of its 4 seats carries a real players.user_id (see that column's -- own doc) so submit_plan.php's own rating-resolution hook can find -- them once the match ends. -- 'draft' — requested directly by the user ("lets add builds in the -- profile Page and add draft games where players Who defined -- théorie build (chose 10 card for à build ) can fight"), see -- PORT_PLAN.md's own Profile-builds/Draft-matchmaking entry. Same -- "zero client-side gameplay branching" precedent 'custom'/'ranked' -- already establish — mechanically identical to 'normal' otherwise -- (fixed 3 lives, no banned cards), differing only in how each -- seat's own hand is dealt: every seat draws 5 random cards per -- turn from ITS OWN saved 10-card users.build_json (see that -- column's own doc) instead of the shared full-RARE_TYPES pool — -- see submit_plan.php's own $draftHandBySeat doc for the dealing -- mechanism, which was already fully implemented in this file's own -- dealHandFromBuild()/resolveTurnStateN() long before this mode -- value existed to actually trigger it. -- Migration for an existing DB (this file drops tables): ALTER -- TABLE games MODIFY COLUMN mode -- ENUM('normal','ascension','custom','ranked','draft') NOT NULL -- DEFAULT 'normal'; mode ENUM('normal','ascension','custom','ranked','draft') NOT NULL DEFAULT 'normal', turn SMALLINT UNSIGNED NOT NULL DEFAULT 1, round SMALLINT UNSIGNED NOT NULL DEFAULT 1, winner_seat TINYINT UNSIGNED NULL, -- Shared starting-lives count for every seat in a 'custom' lobby — -- host-configured at create_game.php time (see that file's own -- doc), applied once by start_game.php's own seat-seeding UPDATE. -- Read but otherwise unused for 'normal'/'ascension' (both already -- hardcode their own fixed lives count independent of this column). -- Lives are NEVER reset to this value again after match start — a -- round transition only resets hp/ap/position (see submit_plan.php's -- own round-over branch), never lives, matching how 'normal' mode's -- own lives mechanic already works (lives persist for the whole -- match, decreasing only on elimination). starting_lives TINYINT UNSIGNED NOT NULL DEFAULT 3, -- Host-configured deny-list for a 'custom' lobby — RARE_TYPES string -- values (e.g. "grenade","hook"), sanitized via hexgame.php's own -- sanitizeBannedCards() (real types only, deduped, capped so at -- least 5 types always remain dealable) before being stored here. -- NULL (or an empty JSON array) both mean "no bans" — the default -- for every 'normal'/'ascension' game, which never write this -- column at all. Set once at creation, never mutated afterward. -- Consumed by dealHand($exclude) at the opening-hand deal -- (start_game.php) and by resolveTurnStateN($bannedCards) at every -- later per-turn re-deal (submit_plan.php) — both parameters -- already existed in hexgame.php as unused reference-code carryover -- before this column gave them real data to consume. Migration for -- an existing DB (this file drops tables): ALTER TABLE games ADD -- COLUMN banned_cards_json JSON NULL AFTER starting_lives; banned_cards_json JSON NULL, map_theme VARCHAR(12) NOT NULL DEFAULT 'jungle', -- Standing hazard state, carried turn to turn — mirrors -- reference/hexgame.php's own $mines/$fires/$pendingBlasts/etc -- array params to resolveTurnStateN() one-for-one. Wiped to '[]' on -- round end by the same server-side logic hexgame.php's own -- round-reset already performs (see resolveTurnStateN's own -- roundOver handling) — this schema doesn't duplicate that logic, -- it just stores whatever the PHP resolver hands back. mines_json JSON NULL, fires_json JSON NULL, pending_blasts_json JSON NULL, missiles_json JSON NULL, portals_json JSON NULL, bubbles_json JSON NULL, reflects_json JSON NULL, sunrays_json JSON NULL, aura_doubles_json JSON NULL, trapdoors_json JSON NULL, rocks_json JSON NULL, springtraps_json JSON NULL, -- The most recently resolved turn, in the wire-adapted shape -- lib/wire.php produces (step_results + plans + winner_seat/draw/ -- round_over) — this is what game_state.php's own "resolution" -- field is read from when a client polls in behind the current -- turn. Overwritten every time submit_plan.php actually resolves a -- turn (never accumulated/appended — see PORT_PLAN.md's own -- "Phase 3" for why "resolution" is null unless the polling client -- is behind, not a growing log). last_resolution_json JSON NULL, -- Recent emote reactions (Phase 4) — {"seat":0,"emote":"🔥","ts":...} -- entries, trimmed by count/age server-side. Separate from -- crowd_emotes_json (below) since these have a real seat to key on. emotes_json JSON NULL, -- Anonymous reactions from eliminated players (Phase 4) — no seat -- field, same shape distinction reference/schema.sql's own -- crowd_emotes_json documents. crowd_emotes_json JSON NULL, -- "Haunt" — a purely cosmetic pebble-throw only eliminated seats and -- spectators can do (see PORT_PLAN.md's own "Haunt" plan). Persists -- for the whole MATCH, unlike every standing-hazard array above — -- not wiped on round transitions, since these are a permanent board -- decoration, not a hazard. {"q","r","seat":int|null,"ts"} entries — -- "seat" is the throwing eliminated player's own seat number (client -- resolves the actual color from that, via the same HpCoin. -- seat_color() every other seat-colored draw already uses), null for -- a spectator throw (no seat/wardrobe to color it by — client falls -- back to a fixed neutral "spectator" color instead). No trim/cap -- needed, unlike emotes_json — bounded naturally by "at most one -- entry per eliminated-seat-or-spectator per turn," a small number -- for any real match. pebbles_json JSON NULL, -- Once-per-TURN throw tracking for Haunt — {"turn":int,"seats": -- [...],"spectator_tokens":[...]}. throw_pebble.php resets both -- arrays the moment this stored "turn" no longer matches the game's -- own current `turn` column (same "compare a stored turn number, -- reset on mismatch" shape game_state.php's own last_resolution_json -- turn-comparison already uses). Kept as one small blob here rather -- than a new column on `players`/`game_spectators` — an eliminated -- seat has no per-turn-action column today, and a spectator is -- deliberately not a `players` row at all (see game_spectators' own -- doc), so neither table is a natural home for a single -- boolean-per-turn. pebble_turn_seats_json JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE players ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, game_id INT UNSIGNED NOT NULL, -- Seat 0 is always the creator — the only one allowed to start the -- game (start_game.php checks this). seat TINYINT UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL, -- Cosmetic identity — submitted at create/join time from the -- client's existing PlayerCosmetics autoload (no account-level -- persistence, matching this plan's own explicit "cosmetics -- submitted per-seat, not saved server-side" scoping). Same column -- shapes as reference/schema.sql's own players table. -- -- body/soul widened VARCHAR(16) -> VARCHAR(24) — requested directly -- by the user ("Let's add more bodyforms I'm thinking Emerad -- rounded corners Rectangle Triangle Hexagon Pentagon Octogone -- Inversed Triangle"), see PORT_PLAN.md's own body-shape-expansion -- entry. PlayerCosmetics.BodyShape widened from 2 to 9 values on the -- client; two of the new camelCase wire-format names -- ("invertedTriangle"/"roundedRectangle") sit at exactly 16 -- characters, leaving zero headroom under the old limit. Confirmed -- via AskUserQuestion: widen the column rather than pick awkward -- abbreviations to stay under 16. Migration for an existing DB -- (this file drops tables): ALTER TABLE players MODIFY COLUMN body -- VARCHAR(24) NOT NULL DEFAULT 'circle'; ALTER TABLE players MODIFY -- COLUMN soul VARCHAR(24) NOT NULL DEFAULT 'circle'; body VARCHAR(24) NOT NULL DEFAULT 'circle', color TINYINT UNSIGNED NULL, hat VARCHAR(16) NOT NULL DEFAULT 'none', marking VARCHAR(16) NOT NULL DEFAULT 'none', marking_tone VARCHAR(8) NOT NULL DEFAULT 'dark', mood VARCHAR(16) NOT NULL DEFAULT 'none', soul VARCHAR(24) NOT NULL DEFAULT 'circle', soul_color VARCHAR(12) NOT NULL DEFAULT 'yellow', bling_tl VARCHAR(16) NOT NULL DEFAULT 'none', bling_tl_front TINYINT(1) NOT NULL DEFAULT 0, bling_tr VARCHAR(16) NOT NULL DEFAULT 'none', bling_tr_front TINYINT(1) NOT NULL DEFAULT 0, bling_bl VARCHAR(16) NOT NULL DEFAULT 'none', bling_bl_front TINYINT(1) NOT NULL DEFAULT 0, bling_br VARCHAR(16) NOT NULL DEFAULT 'none', bling_br_front TINYINT(1) NOT NULL DEFAULT 0, bling_tl_color VARCHAR(12) NOT NULL DEFAULT 'gold', bling_tr_color VARCHAR(12) NOT NULL DEFAULT 'gold', bling_bl_color VARCHAR(12) NOT NULL DEFAULT 'gold', bling_br_color VARCHAR(12) NOT NULL DEFAULT 'gold', -- Secret per-seat bearer token — whoever holds it acts as this -- seat. No accounts/login in this plan's scope; this alone is the -- auth model (see lib/util.php's require_seat()). token CHAR(32) NOT NULL, pos_q TINYINT NOT NULL DEFAULT 0, pos_r TINYINT NOT NULL DEFAULT 0, -- This seat's fixed starting tile — every round-end respawn moves -- pos_q/pos_r back to these exact coordinates, not a fresh spot. spawn_q TINYINT NOT NULL DEFAULT 0, spawn_r TINYINT NOT NULL DEFAULT 0, hp TINYINT UNSIGNED NOT NULL DEFAULT 3, lives TINYINT UNSIGNED NOT NULL DEFAULT 3, action_points TINYINT UNSIGNED NOT NULL DEFAULT 0, -- Standing aura flags — persist across turns until genuinely -- consumed, mirroring PlayerState's own reflect_armed/dodge_armed/ -- etc fields on the Godot side exactly (see player_state.gd). faith_armed TINYINT(1) NOT NULL DEFAULT 0, duplicate_armed TINYINT(1) NOT NULL DEFAULT 0, hope_armed TINYINT(1) NOT NULL DEFAULT 0, dodge_armed TINYINT(1) NOT NULL DEFAULT 0, aura_pain_armed TINYINT(1) NOT NULL DEFAULT 0, reflect_armed TINYINT(1) NOT NULL DEFAULT 0, -- Empty during the lobby phase; dealt once the game starts, empty -- again once hp hits 0 (spectating not built yet, but the shape -- still makes sense: nothing left to plan with). hand_json JSON NOT NULL, plan_json JSON NULL, locked TINYINT(1) NOT NULL DEFAULT 0, -- Set when a player exits mid-match — their row stays (so turn -- resolution keeps working for everyone still connected) but is -- excluded from "is everyone locked in yet" checks going forward. left_game TINYINT(1) NOT NULL DEFAULT 0, -- is_bot/personality were originally deferred ("Phase 5 — bots in -- online lobbies," columns reserved but unused). Now genuinely used -- by Ascension co-op's own enemy seats (start_game.php auto-seats a -- fixed personality here, e.g. 'lila') — still unused by 'normal' -- mode, which has no bots today. is_bot TINYINT(1) NOT NULL DEFAULT 0, personality VARCHAR(12) NOT NULL DEFAULT 'bob', -- This seat's own permanently-unlocked card pool for an Ascension -- run — human seats only, NULL for 'normal' mode and for enemy bot -- seats (whose hand is dealt from a fixed BOT_BUILDS whitelist -- instead, see server/lib/bot.php). Mirrors the offline Godot -- implementation's MenuState.ascension_run["unlocked_cards"] -- exactly, just per-seat instead of per-run since each co-op player -- keeps their own build (requested directly by the user: "each -- player has his on build"). A JSON array of Action.Type strings, -- e.g. ["shoot_blast","charge"] — empty [] for a fresh run. ascension_unlocked_json JSON NULL, -- This seat's own pending choice while ascension_runs.reward_pending -- is set — {"revive": bool, "submitted": bool}. Requested directly -- by the user ("on the choice screen you can revive dead players -- instead of healing or restoring ap") — this slice's reward screen -- only ever offers Revive (no card/heal/regen pick yet, see -- PORT_PLAN.md's own Ascension-co-op entry for the full scope note), -- so this shape is intentionally minimal; a 'cards'/'rest' field -- gets added alongside a future card-unlock slice, not this one. ascension_reward_json JSON NULL, -- Which real account this seat belongs to, for Ranked matchmaking — -- requested directly by the user ("Let's start implementing a -- Ranked system"), see PORT_PLAN.md's own Ranked-matchmaking entry. -- NULL for every ordinary anonymous lobby (Host/Join/Quickplay, -- which never require an account at all — see this table's own -- `token` doc) — only ever set for a games.mode='ranked' seat, -- seeded directly by queue_state.php's own match-creation step from -- the queued account's own users.id. This is what lets -- submit_plan.php's own rating-resolution hook (see that file's own -- doc) map a finished ranked match's seats back to real accounts to -- adjust users.rating. The FK itself (fk_players_user) is added via -- a standalone ALTER TABLE further down this file, AFTER `users` is -- created — `players` is defined before `users` in this file's own -- table order, so an inline FOREIGN KEY here would reference a -- not-yet-existing table. Migration for an existing DB (this file -- drops tables): ALTER TABLE players ADD COLUMN user_id INT -- UNSIGNED NULL AFTER ascension_reward_json, ADD CONSTRAINT -- fk_players_user FOREIGN KEY (user_id) REFERENCES users(id) ON -- DELETE SET NULL; user_id INT UNSIGNED NULL, joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_token (token), UNIQUE KEY uniq_game_seat (game_id, seat), CONSTRAINT fk_players_game FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- One row per Ascension co-op game — the run state genuinely SHARED by -- the whole party (as opposed to ascension_unlocked_json/hp/lives on -- `players`, which is per-seat). Only exists for games.mode='ascension'; -- absent (no row) for 'normal' games, same "this table only applies to -- one mode" shape game_spectators takes for a different concern. CREATE TABLE ascension_runs ( game_id INT UNSIGNED NOT NULL, -- Current shared level — fixed at 1 for this slice (no level-2 -- content/boss rotation/enemy scaling yet, see PORT_PLAN.md's own -- Ascension-co-op entry), stored now so a future slice adding that -- doesn't need its own migration. level SMALLINT UNSIGNED NOT NULL DEFAULT 1, -- Set once resolveTurnStateN() reports levelCleared with at least -- one human at lives=0 — stalls submit_plan.php (refuses further -- plan submissions) until every living human has submitted a -- choice via submit_ascension_reward.php. Mirrors games.status's -- own "lobby/active/finished" state machine without adding a 4th -- top-level status value (reward-pick is a sub-phase of 'active', -- not its own status — same reasoning reference/ASCENSION_ONLINE_PLAN.md -- gives for the equivalent flag in the other codebase this was -- ported from). reward_pending TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (game_id), CONSTRAINT fk_ascension_runs_game FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Real player accounts (username+password, email optional), added for -- the Main Menu's own Online login flow — see PORT_PLAN.md's -- account-system plan. Separate from `players` on purpose: a `users` -- row is a persistent account, a `players` row is one seat in one -- specific match; `players.token` stays the per-match bearer secret -- exactly as before, `users.session_token` is a completely separate -- per-ACCOUNT bearer secret, regenerated on every login (single active -- session — no multi-device support yet, a straightforward future -- upgrade to a proper multi-row session table if ever needed). -- -- `display_name` doubles as the login username (unique, checked at -- login) as well as the name shown in online lobbies/matches — one -- field, not a separate username column, per the "use the username as -- the login and make email optional" request. `email` is now purely -- optional contact info, never used to authenticate. -- -- Deliberately built so a future Steam/Google Play Games identity can -- plug in later as an alternative login method without reshaping this -- table: `id` is the real account identity everything else keys off -- of, not `display_name`/`email` — a platform-login path would just be -- a different way of resolving to a `users.id`, e.g. a new nullable -- `steam_id`/`google_play_id` column added later, not a rewrite. CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(255) NULL, password_hash VARCHAR(255) NOT NULL, -- The login username AND the name shown in online lobbies/matches -- — same 20-char cap as players.name for consistency (see -- create_game.php's own validation). Unique so it can double as a -- login identifier. display_name VARCHAR(20) NOT NULL, -- Bearer secret for this account's current session — NULL means -- logged out everywhere. Nullable AND unique (MySQL treats -- multiple NULLs as distinct for a UNIQUE key, so any number of -- logged-out accounts can coexist without colliding). session_token CHAR(32) NULL, -- Synced wardrobe — same column shapes as players' own cosmetic -- columns (see above), all NULL-able: NULL means "this account has -- never saved a wardrobe yet," in which case the client keeps -- using whatever it already has locally rather than overwriting it -- with defaults. Copy-pasted shape rather than a shared/joined -- table on purpose — an account and a match-seat are different -- concerns, and forcing a join for every ordinary game-server read -- would be a needless coupling for no real benefit. -- -- body/soul widened VARCHAR(16) -> VARCHAR(24) — same body-shape- -- expansion reasoning as players.body/players.soul above (see that -- column's own doc for the full citation). Migration for an -- existing DB (this file drops tables): ALTER TABLE users MODIFY -- COLUMN body VARCHAR(24) NULL; ALTER TABLE users MODIFY COLUMN -- soul VARCHAR(24) NULL; body VARCHAR(24) NULL, color TINYINT UNSIGNED NULL, hat VARCHAR(16) NULL, marking VARCHAR(16) NULL, marking_tone VARCHAR(8) NULL, mood VARCHAR(16) NULL, soul VARCHAR(24) NULL, soul_color VARCHAR(12) NULL, bling_tl VARCHAR(16) NULL, bling_tl_front TINYINT(1) NULL, bling_tr VARCHAR(16) NULL, bling_tr_front TINYINT(1) NULL, bling_bl VARCHAR(16) NULL, bling_bl_front TINYINT(1) NULL, bling_br VARCHAR(16) NULL, bling_br_front TINYINT(1) NULL, bling_tl_color VARCHAR(12) NULL, bling_tr_color VARCHAR(12) NULL, bling_bl_color VARCHAR(12) NULL, bling_br_color VARCHAR(12) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Bumped by lib/util.php's own require_account() on EVERY account- -- authed request (me.php on launch, save_wardrobe.php on wardrobe -- edits, and every friends-list endpoint below) — requested directly -- by the user ("let's build our own friends list first"), see -- PORT_PLAN.md's own friends-list entry. NULL means "never made an -- authed request yet" (a brand-new or never-actually-used account), -- treated as offline the same as a timestamp older than -- lib/util.php's own ONLINE_WINDOW_SECONDS. A dedicated ping.php -- heartbeat covers the gap this alone doesn't reach (logged in, -- sitting in menus, not otherwise making any authed call) — see that -- endpoint's own doc. Migration for an existing DB (this file drops -- tables): ALTER TABLE users ADD COLUMN last_seen_at TIMESTAMP NULL -- AFTER created_at; last_seen_at TIMESTAMP NULL, -- Ranked matchmaking rating (simple ELO) — requested directly by the -- user ("Let's start implementing a Ranked system With matchmaking -- in queues"), see PORT_PLAN.md's own Ranked-matchmaking entry. -- Every account starts at 1000 (confirmed via AskUserQuestion, standard -- chess-style default); adjusted only by submit_plan.php's own -- rating-resolution hook, which runs exclusively for -- games.mode='ranked' matches that end with a real winner_seat (a -- draw leaves every rating untouched, also confirmed via -- AskUserQuestion). K=32 throughout — see submit_plan.php's own -- doc for the exact formula (winner scored against the average of -- the other 3 seats; every other seat scored individually against -- the winner). Migration for an existing DB (this file drops -- tables): ALTER TABLE users ADD COLUMN rating INT NOT NULL DEFAULT -- 1000 AFTER last_seen_at; rating INT NOT NULL DEFAULT 1000, -- Saved Draft-mode deck — requested directly by the user ("lets add -- builds in the profile Page and add draft games where players Who -- defined théorie build (chose 10 card for à build ) can fight"), -- see PORT_PLAN.md's own Profile-builds/Draft-matchmaking entry. -- NULL means "no build saved yet" (the default for every account — -- Draft's own draft_queue_join.php refuses to queue an account in -- this state, see that endpoint's own doc), otherwise a JSON array -- of exactly BUILD_SIZE (10, see hexgame.php's own const) distinct -- RARE_TYPES wire-format strings, validated by build.php before -- ever being written here. One build per account (confirmed via -- AskUserQuestion — not multiple named builds); saving a new one -- simply overwrites this column. Consumed by submit_plan.php's own -- $draftHandBySeat construction (decoded, passed to -- dealHandFromBuild() via resolveTurnStateN() — both already existed -- in this file as unused reference-code carryover before this column -- gave them real data to consume, same shape banned_cards_json's own -- doc describes for dealHand($exclude)/resolveTurnStateN($bannedCards)). -- Migration for an existing DB (this file drops tables): ALTER TABLE -- users ADD COLUMN build_json JSON NULL AFTER rating; build_json JSON NULL, PRIMARY KEY (id), UNIQUE KEY uniq_display_name (display_name), UNIQUE KEY uniq_session_token (session_token) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Deferred FK for players.user_id — see that column's own doc on the -- `players` table above for why this can't be an inline FOREIGN KEY -- there (this schema defines `players` before `users`). ON DELETE SET -- NULL, not CASCADE — deleting an account should never delete/corrupt a -- match other real accounts are still actively playing. ALTER TABLE players ADD CONSTRAINT fk_players_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL; -- Ranked matchmaking queue — one row per account currently waiting for -- a match, requested directly by the user ("Let's start implementing a -- Ranked system With matchmaking in queues"), see PORT_PLAN.md's own -- Ranked-matchmaking entry. Confirmed via AskUserQuestion: matches are -- resolved on ordinary client poll (queue_state.php), not a cron/ -- scheduled job — whichever queued client's own poll happens to notice -- 4 rows waiting atomically claims and matches them (see that -- endpoint's own doc for the full SELECT ... FOR UPDATE dance), rather -- than a separate always-running matchmaker process this app has no -- infrastructure for anywhere else. CREATE TABLE ranked_queue ( -- One queue slot per account — re-joining (queue_join.php) just -- refreshes joined_at via INSERT ... ON DUPLICATE KEY UPDATE rather -- than erroring or creating a second row. user_id INT UNSIGNED NOT NULL, -- Snapshotted at queue-join time rather than re-read live at match -- time — keeps a match's own average-rating calculation stable even -- if something else touched users.rating for one of these accounts -- while they sat queued (not expected in practice, since rating -- only ever changes via a FINISHED ranked match, and a queued -- account can't be IN a match — but a stored snapshot is a cheap -- guard against that class of race entirely rather than relying on -- that invariant holding forever). rating_at_queue INT UNSIGNED NOT NULL, joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Filled in atomically the instant this account's queue slot gets -- matched (see queue_state.php's own SELECT ... FOR UPDATE step) — -- the row itself IS the hand-off vehicle a later poll from this -- same account reads back and consumes (deletes), same "read once, -- server-side" shape MenuState.pending_join_code already uses -- client-side for a conceptually identical single-use hand-off. All -- three NULL simultaneously means "still waiting," all three set -- means "matched, ready to be picked up." matched_game_id INT UNSIGNED NULL, matched_seat TINYINT UNSIGNED NULL, matched_token CHAR(32) NULL, PRIMARY KEY (user_id), CONSTRAINT fk_ranked_queue_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_ranked_queue_game FOREIGN KEY (matched_game_id) REFERENCES games(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Draft matchmaking queue — structurally identical to ranked_queue -- above (same column shapes/semantics for joined_at/matched_*), just -- for games.mode='draft' instead of 'ranked' — requested directly by -- the user ("add draft games where players Who defined théorie build... -- can fight"), see PORT_PLAN.md's own Profile-builds/Draft-matchmaking -- entry. A SEPARATE table rather than reusing ranked_queue with a mode -- discriminator column — the 4-at-a-time atomic claim in -- draft_queue_state.php would otherwise need to filter by intended-mode -- on every claim, and a mixed queue would risk a player queued for one -- mode being claimed by the OTHER mode's own matchmaker if both ever -- read from the same table. No rating_at_queue column here — Draft has -- no rating/skill-matching concept at all (confirmed via -- AskUserQuestion: Draft is about BUILD access, not skill rating; -- Ranked already owns rating), it only requires "has a saved -- users.build_json" (enforced by draft_queue_join.php's own gate, not -- by anything in this table). CREATE TABLE draft_queue ( user_id INT UNSIGNED NOT NULL, joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, matched_game_id INT UNSIGNED NULL, matched_seat TINYINT UNSIGNED NULL, matched_token CHAR(32) NULL, PRIMARY KEY (user_id), CONSTRAINT fk_draft_queue_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_draft_queue_game FOREIGN KEY (matched_game_id) REFERENCES games(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Friend relationships between two accounts — requested directly by the -- user ("let's build our own friends list first we'll see the rest -- later"), adapted from reference/schema.sql's own `friends` table -- (lines ~193-207 there), which already solved this exact problem. -- Always stored with the SMALLER users.id first (user_low_id < -- user_high_id) so a request from A to B and one from B to A can never -- both exist as separate rows — the UNIQUE KEY below enforces this at -- the DB level, but every endpoint touching this table must itself sort -- the pair before reading/writing (see friend_request.php's own doc). -- requester_id records who actually SENT the request (independent of -- which id is "low"/"high") so the recipient's own UI can show -- Accept/Decline while the sender's shows "Pending." Declining/removing -- a friend just DELETEs the row outright — no 'declined' status is -- tracked, matching the reference design exactly (a fresh request can -- always be sent again later with no history to collide with). -- Migration for an existing DB (this file drops tables): run the -- CREATE TABLE below as-is. CREATE TABLE friends ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_low_id INT UNSIGNED NOT NULL, user_high_id INT UNSIGNED NOT NULL, requester_id INT UNSIGNED NOT NULL, status ENUM('pending','accepted') NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_pair (user_low_id, user_high_id), KEY idx_high (user_high_id), CONSTRAINT fk_friends_low FOREIGN KEY (user_low_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_friends_high FOREIGN KEY (user_high_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_friends_requester FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- "Invite a friend to my lobby" — requested directly by the user as -- part of the same friends-list feature (confirmed via AskUserQuestion: -- "include lobby invites in this pass too"), adapted from -- reference/schema.sql's own `lobby_invites` table (lines ~222-247 -- there). A lightweight, POLL-VISIBLE signal — the recipient's own -- FriendsMenu poll (lobby_invite_list.php) is what picks this up, not a -- push notification (this project has no push infrastructure at all). -- One row per invite; consumed (DELETEd) via the separate -- lobby_invite_dismiss.php the moment the recipient's client either -- Joins or explicitly Dismisses it — never accumulates un-actioned. -- Migration for an existing DB (this file drops tables): run the -- CREATE TABLE below as-is. CREATE TABLE lobby_invites ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, from_user_id INT UNSIGNED NOT NULL, to_user_id INT UNSIGNED NOT NULL, -- Denormalized off users.display_name at send time rather than -- joined at read time — cheap (one row per invite, never bulk- -- listed) and means the invite still reads sensibly even in the -- (currently impossible, but no reason to rely on it) case the -- sender's account is gone by the time the recipient polls. from_username VARCHAR(20) NOT NULL, -- Room code only, not a games.id FK — the invite should still show -- (and just fail gracefully on Join, "lobby no longer exists") even -- if the room is deleted/expires between sending and the recipient -- seeing it, same loose-reference precedent this schema's own -- established convention already follows elsewhere. game_code CHAR(6) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_to_user (to_user_id), CONSTRAINT fk_lobby_invites_from FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_lobby_invites_to FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- One row per spectator watching a live/lobby game — see PORT_PLAN.md's -- "Spectate a live online game" plan. Deliberately NOT a `players` row: -- a spectator has no hand/hp/position/plan and must never satisfy -- require_seat() (the one function every seat-scoped endpoint trusts -- for privacy) — this table exists precisely so a spectator can be -- authenticated (via its own `token`) without ever being mistaken for -- a real seat anywhere in the codebase. `stand_slot` is which of the 4 -- fixed board-side placements this spectator visually occupies — -- capped at 4 by watch_game.php itself (this table has no CHECK -- constraint enforcing that, same "app enforces the business rule, DB -- enforces the shape" split every other cap in this schema uses, e.g. -- MAX_SEATS in join_game.php/add_bot.php). CREATE TABLE game_spectators ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, game_id INT UNSIGNED NOT NULL, display_name VARCHAR(20) NOT NULL, token CHAR(32) NOT NULL, stand_slot TINYINT UNSIGNED NOT NULL, joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uniq_spectator_token (token), UNIQUE KEY uniq_game_stand (game_id, stand_slot), CONSTRAINT fk_spectators_game FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Self-cleaning, same reasoning as reference/schema.sql's own cron -- comment: run this periodically (O2switch supports cron) so lobby/ -- finished rows don't accumulate forever. -- DELETE FROM games WHERE status = 'finished' AND updated_at < NOW() - INTERVAL 1 DAY; -- DELETE FROM games WHERE status = 'lobby' AND updated_at < NOW() - INTERVAL 1 HOUR; -- (players rows cascade-delete with their game)