================================================================================
SPEEDots Database - ALTER TABLE Commands
Run these SQL commands to add missing columns to existing published_games table
================================================================================

USE speedots;

-- Add local_game_id column (maps server game to local client game)
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS local_game_id VARCHAR(255);

-- Add executable_name column (filename of the .exe)
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS executable_name VARCHAR(255);

-- Add executable_path column (full path to .exe for launching)
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS executable_path TEXT;

-- Add screenshot_name column (filename of screenshot)
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS screenshot_name VARCHAR(255);

-- Add screenshot_path column (full path to screenshot)
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS screenshot_path TEXT;

-- Add index on local_game_id for faster lookups
ALTER TABLE published_games
ADD INDEX IF NOT EXISTS idx_local_game_id (local_game_id);

================================================================================
VERIFICATION QUERIES
================================================================================

-- Check if all columns were added successfully
DESCRIBE published_games;

-- Or use this query to see specific columns
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'speedots'
  AND TABLE_NAME = 'published_games'
  AND COLUMN_NAME IN ('local_game_id', 'executable_name', 'executable_path',
                      'screenshot_name', 'screenshot_path')
ORDER BY ORDINAL_POSITION;

-- Check existing data (will show NULL for old records)
SELECT id, title, developer, vendor_paypal_email, executable_path, local_game_id
FROM published_games
ORDER BY id DESC
LIMIT 10;

================================================================================
NOTES
================================================================================

1. These ALTER TABLE commands are safe to run on existing databases
2. IF NOT EXISTS prevents errors if columns already exist
3. Old records will have NULL values for these new columns
4. New submissions will populate all fields automatically
5. The vendor_paypal_email column should already exist from database_updates.sql

================================================================================
WHAT THESE COLUMNS DO
================================================================================

local_game_id:
  - Links the server's game record to the local game ID on the publisher's computer
  - Used to track which local game corresponds to which published game

executable_name:
  - Just the filename (e.g., "MyGame.exe")
  - Used for display purposes

executable_path:
  - Full path to the game executable (e.g., "C:\Games\MyGame\MyGame.exe")
  - CRITICAL: This is what allows games to be launched from the Games tab
  - Without this, games cannot be played

screenshot_name:
  - Just the filename of the screenshot/cover image

screenshot_path:
  - Full path to the screenshot file
  - Used for displaying game covers in the store

================================================================================
TROUBLESHOOTING
================================================================================

If games still show as NULL after running these commands:

1. Old games in database will remain NULL - this is expected
2. You need to RE-PUBLISH games to populate these fields
3. Or manually update with SQL:

   UPDATE published_games
   SET executable_path = 'C:\path\to\game.exe',
       executable_name = 'game.exe',
       vendor_paypal_email = 'vendor@email.com'
   WHERE id = YOUR_GAME_ID;

4. For new submissions, make sure speedstore.php on server has been updated
5. Check client submission logs to verify data is being sent

================================================================================