-- Create payment_methods table
CREATE TABLE IF NOT EXISTS payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type ENUM('wire_transfer', 'sepa', 'cryptocurrency', 'bank_account', 'cash_app', 'venmo', 'paypal', 'other') NOT NULL,
    description TEXT,
    instructions TEXT NOT NULL,
    account_details JSON,
    is_active BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,
    min_amount DECIMAL(10,2) DEFAULT 0,
    max_amount DECIMAL(10,2) DEFAULT 999999.99,
    processing_time VARCHAR(100),
    fees VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_is_active (is_active),
    INDEX idx_is_default (is_default)
);

-- Create payment_requests table (enhanced)
CREATE TABLE IF NOT EXISTS payment_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    payment_method_id INT NOT NULL,
    type ENUM('deposit', 'withdrawal') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    status ENUM('pending', 'processing', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending',
    reference_number VARCHAR(100),
    proof_file VARCHAR(255),
    admin_notes TEXT,
    user_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_type (type),
    INDEX idx_created_at (created_at)
);

-- Insert default payment methods
INSERT INTO payment_methods (name, type, description, instructions, account_details, is_default, processing_time, fees) VALUES
('Wire Transfer', 'wire_transfer', 'International bank wire transfers via SWIFT', 'Please include your username as reference when making the transfer. Processing time: 1-3 business days.', 
'{"bank_name": "Bank of America", "account_number": "1234567890", "routing_number": "021000021", "swift_code": "BOFAUS3N", "account_holder": "Wealth Crypto Nexus LLC", "bank_address": "123 Main St, New York, NY 10001"}', 
TRUE, '1-3 business days', '$15-25'),

('SEPA Transfer', 'sepa', 'European bank transfers via SEPA', 'Include your username in the transfer description for proper credit. Processing time: Same day to 1 business day.', 
'{"bank_name": "Deutsche Bank", "iban": "DE89370400440532013000", "bic": "COBADEFFXXX", "account_holder": "Wealth Crypto Nexus GmbH", "bank_address": "Taunusanlage 12, 60325 Frankfurt, Germany"}', 
FALSE, 'Same day - 1 business day', 'Free'),

('Bitcoin (BTC)', 'cryptocurrency', 'Cryptocurrency payments via Bitcoin', 'Send the exact amount to the provided wallet address. Ensure you include the reference number in the memo field.', 
'{"wallet_address": "bc1qxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh", "network": "Bitcoin", "memo_required": true}', 
FALSE, '10-60 minutes', 'Network fees'),

('Ethereum (ETH)', 'cryptocurrency', 'Cryptocurrency payments via Ethereum', 'Send the exact amount to the provided wallet address. Include the reference number in the memo field.', 
'{"wallet_address": "0x742d35Cc6634C0532925a3b8D4C9db96C4b4d8b6", "network": "Ethereum", "memo_required": true}', 
FALSE, '10-60 minutes', 'Network fees'),

('USDC Stablecoin', 'cryptocurrency', 'Stablecoin payments via USDC', 'Send the exact amount to the provided wallet address. Include the reference number in the memo field.', 
'{"wallet_address": "0x742d35Cc6634C0532925a3b8D4C9db96C4b4d8b6", "network": "Ethereum", "memo_required": true}', 
FALSE, '10-60 minutes', 'Network fees'),

('Direct Bank Transfer', 'bank_account', 'Direct bank account transfers', 'Link your bank account for instant transfers. Processing time: 3-5 business days.', 
'{"bank_name": "Chase Bank", "account_number": "0987654321", "routing_number": "021000021", "account_holder": "Wealth Crypto Nexus LLC"}', 
FALSE, '3-5 business days', 'Free'),

('Cash App', 'cash_app', 'Payments via Cash App', 'Send payment to our Cash App account. Include your username in the note field.', 
'{"cash_app_id": "$WealthCryptoNexus", "account_name": "Wealth Crypto Nexus"}', 
FALSE, 'Instant', 'Free'),

('Venmo', 'venmo', 'Payments via Venmo', 'Send payment to our Venmo account. Include your username in the note field.', 
'{"venmo_id": "@WealthCryptoNexus", "account_name": "Wealth Crypto Nexus"}', 
FALSE, 'Instant', 'Free'),

('PayPal', 'paypal', 'Payments via PayPal', 'Send payment to our PayPal account. Include your username in the note field.', 
'{"paypal_email": "payments@wealthcryptonexus.com", "account_name": "Wealth Crypto Nexus"}', 
FALSE, 'Instant', '2.9% + $0.30');
