[BE] DB Migration — arcade_games & arcade_sessions tables

Create the new migration at database/migrations/XXXX_create_arcade_tables.php, using the Schema::create blocks given for arcade_games and arcade_sessions. Read the existing migrations for the editions and enrollments tables first, since both foreign keys point at them, and check whether the Edition and Enrollment models exist yet. Done means php artisan migrate and migrate:rollback both run cleanly, the UNIQUE(edition_id, slug) constraint rejects a duplicate insert, and no triggers are created.

🎮 Project Context: SINF Gamification & Arcade System

What is SINF and why are we building this?

Semana de Informática (SINF) is an annual tech conference organized by NIAEFEUP for university students. During the conference, attendees earn event tokens ("SINFrões" / points) by attending talks, workshops, and completing sponsor quests (scanning QR codes at company booths). Historically, these tokens were only spent at the physical conference store (/shop).

To massively increase student engagement throughout the days of the event, we are building a Gamification / Arcade & Prize Draw platform:

• Arcade (/arcade): Participants can wager their earned tokens on 5 mini-games (PvP Coin Flip, High/Low Dice, Bug Sweeper, Mastermind, and Binary Blitz).
• Prize Draws (/raffle): Participants can sink tokens into daily and final raffle pools to win hardware and sponsor prizes.
• Admin Management: Event organizers can configure multiplier payouts (payout_config), toggle games on/off, create raffle pools, and trigger weighted-random winner drawings directly inside the admin dashboard.

---

🛡️ Core Economy & Security Rules (MUST READ FOR ALL DEVS)

1. Points Storage: Points are stored as an unsigned integer in enrollments.points (one row per participant per edition).
2. PostgreSQL Trigger Caution: The database has existing triggers that modify points when rows are inserted/deleted from enrollment_slot and enrollment_product.
   • NEVER insert into or delete from those pivot tables for arcade games or raffles!
   • Directly mutating enrollments.points via PHP ($enrollment->decrement('points', $wager)) does not fire those triggers and is the only safe way to deduct/credit points.
3. No Negative Balance Constraint in DB: There is no CHECK (points >= 0) in PostgreSQL. All validation against negative balances must occur in PHP.
4. Mandatory Concurrency Protection: Our attendees are Computer Science students who understand network inspection and race conditions. All token adjustments must run inside DB::transaction() with $enrollment = Enrollment::lockForUpdate()->find(...).
5. Server Authority: Game outcomes, RNG (random_int()), secret state (bombs, codes), and time limits must be verified strictly server-side.

---

🗓️ 3-Week Timeline & Where Your Task Fits

• Week 1 (Foundation - Current Phase): Database tables (#458, #459), Eloquent models (#460, #461), ArcadeWagerService security core (#462), Coin Flip matchmaking backend (#463), Arcade lobby page (#464), and shared UI components (#465).
• Week 2 (Game Building): 5 developer pairs concurrently build the 5 games (#466–#470) on top of this foundation.
• Week 3 (Raffles, Admin & QA): Raffle purchase flow (#471–#473), Admin CRUD (#474–#477), Global Navbar state (#478), and Security Test Suite (#479).

---

🎯 Why Issue #458 is Critical

You are creating the database schema for the entire Arcade feature (arcade_games and arcade_sessions).

• arcade_games stores each game's parameters: wager limits, daily win caps, and admin-tunable multipliers in a payout_config JSONB column.
• arcade_sessions stores every wager, play attempt, audit record, and secret server-side state (such as hidden bomb locations or active player IDs for PvP games).
  All subsequent models, controllers, and games depend directly on this migration running cleanly.

---

Context & Objective

This is the first foundation issue for the Gamification / Arcade feature. All other arcade issues (models, controllers, frontend) depend on this migration being merged and running cleanly.

Two new tables are needed:

• arcade_games — one row per game type per edition (Coin Flip, High/Low, Bug Sweeper, Mastermind, Binary Blitz). Admins configure wager limits, toggle games on/off, and set payout multipliers through a payout_config JSONB column.
• arcade_sessions — one row per play attempt. Acts as both the audit log and the server-side state store for multi-step games (Bug Sweeper grid, Mastermind secret code, Binary Blitz questions). For the PvP Coin Flip game it also holds the second player's enrollment ID.

No PostgreSQL triggers should be added. Points mutations happen in PHP via DB::transaction() in the game controllers.

Files to Create / Modify

• Create: database/migrations/XXXX_create_arcade_tables.php

Technical Requirements

Use Schema::create() / Blueprint, not raw SQL DDL.

arcade_games

Schema::create('arcade_games', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Edition::class)->constrained()->cascadeOnDelete();
    $table->string('slug', 64);          // 'coin-flip' | 'high-low' | 'bug-sweeper' | 'mastermind' | 'binary-blitz'
    $table->string('name');
    $table->boolean('is_active')->default(true);
    $table->boolean('is_pvp')->default(false);   // true only for coin-flip
    $table->unsignedInteger('min_wager')->default(10);
    $table->unsignedInteger('max_wager')->default(500);
    $table->unsignedInteger('daily_win_cap')->nullable();  // null = unlimited
    $table->json('payout_config')->default('{}');  // admin-configurable, see docs
    $table->timestamps();
    $table->unique(['edition_id', 'slug']);
});

arcade_sessions

Schema::create('arcade_sessions', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Enrollment::class)->constrained()->cascadeOnDelete();
    $table->foreignIdFor(ArcadeGame::class)->constrained()->cascadeOnDelete();
    // PvP second player (nullable — only set for coin-flip after someone joins)
    $table->foreignId('challenger_enrollment_id')
          ->nullable()
          ->constrained('enrollments')
          ->nullOnDelete();
    $table->unsignedInteger('wager');
    // pending → matched → win | loss | cashout | expired
    $table->string('outcome', 16)->default('pending');
    $table->unsignedInteger('payout')->default(0);
    $table->json('server_state')->nullable();   // hashed/encrypted secrets never sent to FE
    $table->timestamp('started_at')->useCurrent();
    $table->timestamp('resolved_at')->nullable();
    $table->timestamps();
});

payout_config expected shapes per slug (for documentation in a migration comment)

slug	config keys
coin-flip	house_edge_pct (int, e.g. 10)
high-low	under_threshold, over_threshold, under_multiplier, over_multiplier, exact_multiplier
bug-sweeper	click_multipliers (array of 5 floats), bomb_count (int, default 3)
mastermind	max_attempts (int, default 5), attempt_multipliers (object "1"→6.0 … "5"→1.5)
binary-blitz	question_count, time_limit_seconds, win_multiplier, partial_multiplier, daily_win_cap

Acceptance Criteria

• php artisan migrate runs cleanly with no errors
• php artisan migrate:rollback drops both tables cleanly
• arcade_games table contains all columns as specified
• arcade_sessions table contains all columns as specified
• UNIQUE(edition_id, slug) constraint on arcade_games is enforced (test with a duplicate insert)
• challenger_enrollment_id foreign key uses nullOnDelete() (row stays if challenger is deleted)
• No PostgreSQL triggers are created

Dependencies

None — this is the first issue in the gamification feature.