-- Update payment_requests table to include payment_method_id and other missing columns
-- This migration updates the existing table structure to match the new requirements

-- Add payment_method_id column
ALTER TABLE payment_requests 
ADD COLUMN payment_method_id INT AFTER user_id;

-- Add currency column
ALTER TABLE payment_requests 
ADD COLUMN currency VARCHAR(10) DEFAULT 'USD' AFTER amount;

-- Add reference_number column
ALTER TABLE payment_requests 
ADD COLUMN reference_number VARCHAR(100) AFTER currency;

-- Add proof_file column
ALTER TABLE payment_requests 
ADD COLUMN proof_file VARCHAR(255) AFTER reference_number;

-- Add user_notes column
ALTER TABLE payment_requests 
ADD COLUMN user_notes TEXT AFTER proof_file;

-- Update status enum to include new values
ALTER TABLE payment_requests 
MODIFY COLUMN status ENUM('pending', 'processing', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending';

-- Add foreign key constraint for payment_method_id
ALTER TABLE payment_requests 
ADD CONSTRAINT fk_payment_requests_payment_method_id 
FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE CASCADE;

-- Add index for payment_method_id
ALTER TABLE payment_requests 
ADD INDEX idx_payment_method_id (payment_method_id);

-- Add index for reference_number
ALTER TABLE payment_requests 
ADD INDEX idx_reference_number (reference_number);
