-- =====================================================
-- BinaryPro Mobile App - Database Migration
-- Run this SQL to add mobile app support tables/columns
-- =====================================================

-- 1. Add Google Play columns to deposits table
ALTER TABLE deposits 
    ADD COLUMN google_order_id VARCHAR(255) NULL,
    ADD COLUMN google_purchase_token TEXT NULL,
    ADD COLUMN payment_platform ENUM('web', 'android', 'ios') DEFAULT 'web';

-- 2. Create app_devices table for FCM push notifications
CREATE TABLE IF NOT EXISTS app_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_token VARCHAR(255) NOT NULL,
    device_type ENUM('android', 'ios') DEFAULT 'android',
    device_model VARCHAR(100) NULL,
    app_version VARCHAR(20) NULL,
    os_version VARCHAR(20) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_device_token (device_token),
    INDEX idx_user_active (user_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Create push_notifications table
CREATE TABLE IF NOT EXISTS push_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    data JSON NULL,
    type ENUM('trade_result', 'deposit', 'withdrawal', 'promo', 'system') NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    is_sent TINYINT(1) DEFAULT 0,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_read (user_id, is_read),
    INDEX idx_user_sent (user_id, is_sent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Add index on google_order_id for duplicate checking
ALTER TABLE deposits ADD INDEX idx_google_order (google_order_id);
