================================================================================
SPEEDots Database - Add product_id Column to receipts Table
================================================================================

Run this SQL command in your MySQL database to add the product_id column:

USE speedots;

-- Add product_id column to receipts table
ALTER TABLE receipts
ADD COLUMN product_id VARCHAR(255) UNIQUE AFTER receipt_token;

-- Add index for faster lookups
ALTER TABLE receipts
ADD INDEX idx_product_id (product_id);

================================================================================
VERIFICATION
================================================================================

-- Check if column was added successfully
DESCRIBE receipts;

-- Or use this query to see the specific column
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'speedots'
  AND TABLE_NAME = 'receipts'
  AND COLUMN_NAME = 'product_id';

-- View current structure of receipts table
SHOW CREATE TABLE receipts;

================================================================================
EXPECTED STRUCTURE AFTER UPDATE
================================================================================

The receipts table should now have these columns:
- id (INT PRIMARY KEY AUTO_INCREMENT)
- receipt_token (VARCHAR(64) UNIQUE)
- product_id (VARCHAR(255) UNIQUE) ← NEW COLUMN
- game_id (INT)
- user_email (VARCHAR(255))
- game_title (VARCHAR(255))
- game_developer (VARCHAR(255))
- amount_paid (DECIMAL(10,2))
- is_free (BOOLEAN)
- transaction_id (VARCHAR(255))
- claimed_at (TIMESTAMP)
- expires_at (TIMESTAMP NULL)
- status (VARCHAR(50))

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

1. The UNIQUE constraint ensures each product_id can only be used once
2. The AFTER receipt_token clause places the column right after receipt_token
3. Existing records will have NULL for product_id (this is okay)
4. New receipts will automatically populate product_id
5. If column already exists, you'll get an error (safe to ignore)

================================================================================
IF YOU GET AN ERROR
================================================================================

If you get "Duplicate column name 'product_id'" error, it means the column
already exists. Run this to check:

SELECT product_id FROM receipts LIMIT 5;

If that works, the column exists and you're good to go!

If you still get errors when creating receipts, the issue might be with
the UNIQUE constraint. Try this alternate version:

-- Remove UNIQUE constraint if it's causing issues
ALTER TABLE receipts
DROP INDEX product_id;

-- Re-add without UNIQUE (allows duplicates, but shouldn't happen)
ALTER TABLE receipts
ADD INDEX idx_product_id (product_id);

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