================================================================================
SPEEDots Database Update Script
Run this SQL on your MySQL server to update the database schema
================================================================================

USE speedots;

================================================================================
1. UPDATE published_games TABLE
   Add new columns for content type and payment processing
================================================================================

ALTER TABLE published_games
ADD COLUMN content_type VARCHAR(20) DEFAULT 'game'
AFTER category;

ALTER TABLE published_games
ADD COLUMN vendor_paypal_email VARCHAR(255)
AFTER user_email;

ALTER TABLE published_games
ADD COLUMN vendor_payout_percentage DECIMAL(5,2) DEFAULT 85.00
AFTER vendor_paypal_email;

ALTER TABLE published_games
ADD COLUMN platform_fee_percentage DECIMAL(5,2) DEFAULT 15.00
AFTER vendor_payout_percentage;

ALTER TABLE published_games
ADD INDEX idx_content_type (content_type);

================================================================================
2. CREATE receipts TABLE
   Tracks all game purchases (free and paid) with unique tokens
================================================================================

CREATE TABLE IF NOT EXISTS receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receipt_token VARCHAR(64) UNIQUE NOT NULL,
    game_id INT NOT NULL,
    user_email VARCHAR(255) NOT NULL,
    game_title VARCHAR(255),
    game_developer VARCHAR(255),
    amount_paid DECIMAL(10,2) DEFAULT 0.00,
    is_free BOOLEAN DEFAULT FALSE,
    transaction_id VARCHAR(255),
    claimed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    status VARCHAR(50) DEFAULT 'active',
    INDEX idx_receipt_token (receipt_token),
    INDEX idx_user_email (user_email),
    INDEX idx_game_id (game_id),
    INDEX idx_status (status),
    INDEX idx_claimed_at (claimed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

================================================================================
3. CREATE purchases TABLE
   Tracks detailed purchase transactions
================================================================================

CREATE TABLE IF NOT EXISTS purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    buyer_email VARCHAR(255) NOT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    vendor_amount DECIMAL(10,2) NOT NULL,
    platform_fee DECIMAL(10,2) NOT NULL,
    transaction_id VARCHAR(255),
    status VARCHAR(50) DEFAULT 'completed',
    purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_game_id (game_id),
    INDEX idx_buyer_email (buyer_email),
    INDEX idx_status (status),
    INDEX idx_purchased_at (purchased_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

================================================================================
4. UPDATE EXISTING DATA
   Set default values for new columns in existing records
================================================================================

UPDATE published_games
SET content_type = 'game'
WHERE content_type IS NULL OR content_type = '';

UPDATE published_games
SET vendor_payout_percentage = 85.00,
    platform_fee_percentage = 15.00
WHERE vendor_payout_percentage IS NULL OR vendor_payout_percentage = 0;

================================================================================
FINISHED
================================================================================

All updates complete. Your database now has:
- content_type column (game vs application)
- Vendor payment tracking (PayPal email, payout percentages)
- receipts table (unique tokens for all purchases)
- purchases table (detailed transaction tracking)

Next steps:
1. Upload speedstore_new.php to your server
2. Test with: py test_server.py
3. Try submitting a game from the launcher