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

-- Use the speedots database
USE speedots;

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

-- Add content_type column (game vs application)
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS content_type VARCHAR(20) DEFAULT 'game'
AFTER category;

-- Add vendor payment columns
ALTER TABLE published_games
ADD COLUMN IF NOT EXISTS vendor_paypal_email VARCHAR(255)
AFTER user_email;

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

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

-- Add index on content_type for faster filtering
ALTER TABLE published_games
ADD INDEX IF NOT EXISTS 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 COMMENT 'Unique 64-character token',
    game_id INT NOT NULL COMMENT 'References published_games.id',
    user_email VARCHAR(255) NOT NULL COMMENT 'Owner email',
    game_title VARCHAR(255) COMMENT 'Game name (denormalized for speed)',
    game_developer VARCHAR(255) COMMENT 'Developer name',
    amount_paid DECIMAL(10,2) DEFAULT 0.00 COMMENT '$0.00 for free games',
    is_free BOOLEAN DEFAULT FALSE COMMENT 'TRUE if free game',
    transaction_id VARCHAR(255) COMMENT 'PayPal transaction ID (if paid)',
    claimed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When user claimed/bought',
    expires_at TIMESTAMP NULL COMMENT 'Optional expiration date',
    status VARCHAR(50) DEFAULT 'active' COMMENT 'active, revoked, expired',

    -- Indexes for fast lookups
    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
COMMENT='Tracks all game purchases with unique receipt tokens';

-- ============================================================================
-- 3. CREATE purchases TABLE (if not exists - for legacy compatibility)
-- Tracks detailed purchase transactions
-- ============================================================================

CREATE TABLE IF NOT EXISTS purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL COMMENT 'References published_games.id',
    buyer_email VARCHAR(255) NOT NULL COMMENT 'Buyer email',
    amount_paid DECIMAL(10,2) NOT NULL COMMENT 'Total amount paid',
    vendor_amount DECIMAL(10,2) NOT NULL COMMENT 'Amount vendor receives (85%)',
    platform_fee DECIMAL(10,2) NOT NULL COMMENT 'Platform fee (15%)',
    transaction_id VARCHAR(255) COMMENT 'Payment processor transaction ID',
    status VARCHAR(50) DEFAULT 'completed' COMMENT 'completed, pending, refunded',
    purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Indexes
    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
COMMENT='Detailed purchase transaction records';

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

-- Set content_type to 'game' for all existing records
UPDATE published_games
SET content_type = 'game'
WHERE content_type IS NULL OR content_type = '';

-- Set default payment percentages for existing records
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;

-- ============================================================================
-- 5. VERIFY INSTALLATION
-- Query to check if all updates were successful
-- ============================================================================

-- Check published_games schema
SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    COLUMN_DEFAULT,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'speedots'
  AND TABLE_NAME = 'published_games'
ORDER BY ORDINAL_POSITION;

-- Check receipts table exists
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'speedots'
  AND TABLE_NAME = 'receipts';

-- Check purchases table exists
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'speedots'
  AND TABLE_NAME = 'purchases';

-- ============================================================================
-- 6. SAMPLE QUERIES (COMMENTED OUT - FOR REFERENCE)
-- ============================================================================

-- Get all games with their payment info
-- SELECT
--     id, title, developer, price, content_type,
--     vendor_paypal_email, vendor_payout_percentage, platform_fee_percentage
-- FROM published_games
-- WHERE status = 'approved'
-- ORDER BY created_at DESC;

-- Get all receipts for a user
-- SELECT * FROM receipts
-- WHERE user_email = 'user@example.com'
-- AND status = 'active'
-- ORDER BY claimed_at DESC;

-- Get revenue for a specific vendor
-- SELECT
--     game_developer,
--     vendor_paypal_email,
--     COUNT(*) as total_sales,
--     SUM(amount_paid) as total_revenue,
--     SUM(CASE WHEN is_free = 1 THEN 1 ELSE 0 END) as free_claims,
--     SUM(CASE WHEN is_free = 0 THEN 1 ELSE 0 END) as paid_sales
-- FROM receipts
-- WHERE game_developer = 'Developer Name'
-- AND status = 'active'
-- GROUP BY game_developer, vendor_paypal_email;

-- Get all games by content type
-- SELECT content_type, COUNT(*) as count
-- FROM published_games
-- WHERE status = 'approved'
-- GROUP BY content_type;

-- Verify a receipt token
-- SELECT * FROM receipts
-- WHERE receipt_token = 'your_64_character_token_here'
-- AND status = 'active';

-- Get user's owned games
-- SELECT
--     r.receipt_token,
--     r.game_title,
--     r.game_developer,
--     r.amount_paid,
--     r.is_free,
--     r.claimed_at,
--     g.content_type
-- FROM receipts r
-- LEFT JOIN published_games g ON r.game_id = g.id
-- WHERE r.user_email = 'user@example.com'
-- AND r.status = 'active'
-- ORDER BY r.claimed_at DESC;

-- ============================================================================
-- 7. CLEANUP (OPTIONAL - UNCOMMENT IF NEEDED)
-- ============================================================================

-- Remove test data
-- DELETE FROM receipts WHERE user_email LIKE '%test%';
-- DELETE FROM purchases WHERE buyer_email LIKE '%test%';

-- Reset download counters
-- UPDATE published_games SET downloads = 0;

-- ============================================================================
-- END OF SCRIPT
-- ============================================================================

-- Display success message
SELECT '✓ Database update completed successfully!' as Status;
SELECT '✓ published_games table updated with payment fields' as Update1;
SELECT '✓ receipts table created for token system' as Update2;
SELECT '✓ purchases table created for transaction tracking' as Update3;
SELECT 'Next: Upload speedstore_new.php to your server' as NextStep;
