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

-- Drop existing indexes if recreating

-- ============================================
-- Table: users (با indexes بهینه)
-- ============================================
CREATE TABLE users (
    user_id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    referrer_id VARCHAR(36) DEFAULT NULL,
    referral_code VARCHAR(20) NOT NULL UNIQUE,
    phone_number VARCHAR(20) DEFAULT NULL,
    status ENUM('pending', 'active', 'suspended') DEFAULT 'pending',
    role ENUM('user', 'admin', 'system') DEFAULT 'user',
    
    -- Tree Structure (Materialized Path)
    path VARCHAR(1000) DEFAULT NULL,
    depth INT DEFAULT 0,
    position INT DEFAULT NULL,
    
    -- Cached Counters (for performance)
    direct_referrals INT DEFAULT 0,
    total_descendants INT DEFAULT 0,
    is_tree_complete BOOLEAN DEFAULT FALSE,
    
    -- Financial
    total_points DECIMAL(15,2) DEFAULT 0.00,
    pending_withdrawal DECIMAL(15,2) DEFAULT 0.00,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    activated_at TIMESTAMP NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (referrer_id) REFERENCES users(user_id) ON DELETE SET NULL,
    
    -- Optimized Indexes
    INDEX idx_status (status),
    INDEX idx_referrer (referrer_id),
    INDEX idx_path (path(255)),
    INDEX idx_depth (depth),
    INDEX idx_created (created_at),
    INDEX idx_active_users (status, created_at),
    INDEX idx_tree_lookup (referrer_id, position, status),
    INDEX idx_descendants (total_descendants),
    INDEX idx_complete_tree (is_tree_complete, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 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;

-- ============================================
-- Insert Default Data
-- ============================================

-- Seed User
-- Seed User (Root of Tree)
INSERT INTO users (user_id, email, password_hash, referral_code, status, role, activated_at, depth, path, position, referrer_id, direct_referrals, total_descendants, is_tree_complete) 
VALUES ('seed', 'seed@system.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'SEED', 'active', 'system', NOW(), 0, 'seed', NULL, NULL, 0, 0, FALSE);

-- Admin User (password: admin123)
INSERT INTO users (user_id, email, password_hash, referrer_id, referral_code, status, role, activated_at, depth, path, position, direct_referrals, total_descendants, is_tree_complete) 
VALUES ('admin', 'admin@mlm.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', NULL, 'ADMIN', 'active', 'admin', NOW(), 0, 'admin', NULL, 0, 0, FALSE);

-- 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$$
CREATE TRIGGER after_user_activate
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.status = 'active' AND OLD.status != 'active' THEN
        -- Update ancestor counts using path
        UPDATE users 
        SET total_descendants = total_descendants + 1
        WHERE NEW.path LIKE CONCAT(path, '%') AND user_id != NEW.user_id;
        
        -- Update direct referrals
        IF NEW.referrer_id IS NOT NULL THEN
            UPDATE users 
            SET direct_referrals = direct_referrals + 1 
            WHERE user_id = NEW.referrer_id;
        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 Tables for optimization
-- ============================================
ANALYZE TABLE users;
ANALYZE TABLE transactions;
ANALYZE TABLE payments;
ANALYZE TABLE withdrawals;
ANALYZE TABLE rewards;
