-- Create merchant payment requests table
CREATE TABLE IF NOT EXISTS merchant_payment_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_invoice VARCHAR(100) NOT NULL UNIQUE,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20),
    transaction_id VARCHAR(100),
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP NULL,
    
    INDEX idx_merchant_invoice (merchant_invoice),
    INDEX idx_student_id (student_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Insert sample merchant numbers (in a real implementation, these would come from settings)
INSERT INTO settings (setting_key, setting_value) VALUES 
('merchant_bkash_number', '017XXXXXXXX'),
('merchant_nagad_number', '018XXXXXXXX'),
('merchant_rocket_number', '019XXXXXXXX')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);