-- ============================================
-- MLM System - Optimized Schema for 100K+ Users
-- ============================================

-- Drop existing indexes if recreating

-- ============================================
-- Table: transactions (Partitioned by month)
-- ============================================
CREATE TABLE transactions (
    transaction_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    transaction_type ENUM('direct', 'level_3', 'level_5', 'level_7', 'withdrawal', 'bonus') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    reference_id VARCHAR(36) DEFAULT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    INDEX idx_user (user_id),
    INDEX idx_type (transaction_type),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_user_type (user_id, transaction_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: payments
-- ============================================
CREATE TABLE payments (
    payment_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    wallet_address VARCHAR(255) NOT NULL,
    network ENUM('BSC', 'TRON', 'ETH') DEFAULT 'BSC',
    tx_hash VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'confirmed', 'failed') DEFAULT 'pending',
    confirmed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_tx_hash (tx_hash),
    INDEX idx_created (created_at),
    UNIQUE INDEX idx_unique_tx (tx_hash, network)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: withdrawals
-- ============================================
CREATE TABLE withdrawals (
    withdrawal_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    fee DECIMAL(15,2) DEFAULT 0.00,
    net_amount DECIMAL(15,2) NOT NULL,
    wallet_address VARCHAR(255) NOT NULL,
    network ENUM('BSC', 'TRON', 'ETH') DEFAULT 'BSC',
    tx_hash VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'completed', 'rejected') DEFAULT 'pending',
    admin_note TEXT,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_pending (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: rewards (Cached rewards)
-- ============================================
CREATE TABLE rewards (
    reward_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    reward_type ENUM('direct', 'level_3', 'level_5', 'level_7') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    from_user_id VARCHAR(36) DEFAULT NULL,
    depth INT DEFAULT 0,
    status ENUM('pending', 'paid') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (from_user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    
    INDEX idx_user (user_id),
    INDEX idx_type (reward_type),
    INDEX idx_status (status),
    INDEX idx_from (from_user_id),
    INDEX idx_user_rewards (user_id, reward_type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: settings
-- ============================================
CREATE TABLE settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    description VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: queue_jobs (برای async processing)
-- ============================================
CREATE TABLE queue_jobs (
    job_id VARCHAR(36) PRIMARY KEY,
    job_type ENUM('placement', 'reward', 'activation', 'withdrawal') NOT NULL,
    payload JSON NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    
    INDEX idx_status (status),
    INDEX idx_type (job_type),
    INDEX idx_pending (status, created_at),
    INDEX idx_processing (status, job_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: cache (برای caching queries)
-- ============================================
CREATE TABLE cache (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value MEDIUMTEXT NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default Settings
INSERT INTO settings (setting_key, setting_value, description) VALUES
('reward.direct.position_1', '100', 'First direct referral reward'),
('reward.direct.position_2', '75', 'Second direct referral reward'),
('reward.direct.position_3', '50', 'Third direct referral reward'),
('reward.level_3', '10', 'Level 3 completion reward'),
('reward.level_5', '5', 'Level 5 completion reward'),
('reward.level_7', '2', 'Level 7 completion reward'),
('withdrawal.minWithdrawal', '10', 'Minimum withdrawal amount'),
('withdrawal.maxWithdrawal', '10000', 'Maximum withdrawal amount'),
('withdrawal.withdrawalFee', '0.02', 'Withdrawal fee percentage'),
('system_wallet_address', '', 'System wallet for payments'),
('payment.network', 'BSC', 'Default payment network'),
('email.smtpHost', 'smtp.gmail.com', 'SMTP host'),
('email.smtpPort', '587', 'SMTP port'),
('email.fromEmail', 'noreply@mlm.local', 'From email address'),
('email.fromName', 'MLM System', 'From name'),
('cache.ttl', '3600', 'Default cache TTL in seconds'),
('queue.enabled', '1', 'Enable queue processing');

-- ============================================
-- Optimized Triggers
-- ============================================

DELIMITER $$

-- Trigger: Update descendants count (optimized)
DROP TRIGGER IF EXISTS after_user_activate$$
        END IF;
        
        -- Queue reward calculation
        INSERT INTO queue_jobs (job_id, job_type, payload, status)
        VALUES (UUID(), 'reward', JSON_OBJECT('user_id', NEW.user_id), 'pending');
    END IF;
END$$

DELIMITER ;

ANALYZE TABLE transactions;
ANALYZE TABLE payments;
ANALYZE TABLE withdrawals;
ANALYZE TABLE rewards;
