-- Fierce Mobile Game Database Schema
-- Create database
CREATE DATABASE IF NOT EXISTS anemal_fierce;
USE anemal_fierce;

-- User sessions/playtime tracking
CREATE TABLE user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_end TIMESTAMP NULL,
    duration_minutes INT DEFAULT 0,
    earnings_generated DECIMAL(10, 2) DEFAULT 0.00,
    admob_revenue DECIMAL(10, 2) DEFAULT 0.00,
    race_completed BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Withdrawal requests table
CREATE TABLE withdrawal_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_type ENUM('bank', 'paypal', 'mobile_money', 'crypto') NOT NULL,
    payment_address TEXT NOT NULL,
    status ENUM('pending', 'approved', 'completed', 'rejected', 'cancelled') DEFAULT 'pending',
    admin_notes TEXT NULL,
    processed_by INT NULL,
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_date TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES admins(id) ON DELETE SET NULL
);

-- AdMob earnings tracking
CREATE TABLE admob_earnings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ad_type ENUM('banner', 'interstitial', 'rewarded', 'native') NOT NULL,
    revenue DECIMAL(10, 4) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    ad_unit_id VARCHAR(255),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- System settings
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Payment methods for users
CREATE TABLE user_payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    payment_type ENUM('bank', 'paypal', 'mobile_money', 'crypto') NOT NULL,
    payment_details JSON NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Activity logs
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    admin_id INT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES 
('earning_rate_per_minute', '0.10', 'Amount earned per minute of gameplay'),
('minimum_withdrawal_amount', '5.00', 'Minimum withdrawal amount'),
('maximum_withdrawal_amount', '1000.00', 'Maximum withdrawal amount per request'),
('withdrawal_fee_percentage', '2.5', 'Withdrawal fee percentage'),
('admob_user_share_percentage', '70', 'Percentage of AdMob revenue shared with users'),
('race_completion_bonus_percentage', '50', 'Bonus percentage for completing races'),
('daily_earning_limit', '50.00', 'Maximum earnings per user per day'),

-- AdMob Ad Unit IDs for Android
('admob_android_banner_id', 'ca-app-pub-3940256099942544/6300978111', 'Android Banner Ad Unit ID'),
('admob_android_interstitial_id', 'ca-app-pub-3940256099942544/1033173712', 'Android Interstitial Ad Unit ID'),
('admob_android_rewarded_id', 'ca-app-pub-3940256099942544/5224354917', 'Android Rewarded Ad Unit ID'),

-- AdMob Ad Unit IDs for iOS
('admob_ios_banner_id', 'ca-app-pub-3940256099942544/2934735716', 'iOS Banner Ad Unit ID'),
('admob_ios_interstitial_id', 'ca-app-pub-3940256099942544/4411468910', 'iOS Interstitial Ad Unit ID'),
('admob_ios_rewarded_id', 'ca-app-pub-3940256099942544/1712485313', 'iOS Rewarded Ad Unit ID');
CREATE INDEX idx_withdrawal_status ON withdrawal_requests(status);
CREATE INDEX idx_withdrawal_user_date ON withdrawal_requests(user_id, request_date);
CREATE INDEX idx_sessions_user_date ON user_sessions(user_id, session_start);
CREATE INDEX idx_admob_user_date ON admob_earnings(user_id, timestamp);
