-- Transfer Table for TrustBeam Credit Bank
-- This table stores all transfer requests and their verification status

USE trustbeam_bank;

CREATE TABLE IF NOT EXISTS transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    beneficiary_account_name VARCHAR(100) NOT NULL,
    beneficiary_account_number VARCHAR(50) NOT NULL,
    bank_name VARCHAR(100) NOT NULL,
    bank_address TEXT NOT NULL,
    account_type ENUM('Savings', 'Current', 'Business', 'Premium') NOT NULL,
    swift_code VARCHAR(10) NOT NULL,
    iban VARCHAR(50) NOT NULL,
    transaction_pin VARCHAR(4) NOT NULL,
    note TEXT,
    transfer_status ENUM('Pending', 'IMF_Verified', 'SWIFT_Verified', 'COT_Verified', 'Completed', 'Failed', 'Cancelled') DEFAULT 'Pending',
    imf_verified_at TIMESTAMP NULL,
    swift_verified_at TIMESTAMP NULL,
    cot_verified_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key to users table
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    -- Indexes for better performance
    INDEX idx_user_id (user_id),
    INDEX idx_transfer_status (transfer_status),
    INDEX idx_reference_number (reference_number),
    INDEX idx_created_at (created_at)
);

-- Insert sample transfer data for testing
INSERT INTO transfers (
    user_id, amount, beneficiary_account_name, beneficiary_account_number,
    bank_name, bank_address, account_type, swift_code, iban,
    transaction_pin, note, reference_number
) VALUES 
(1, 1000.00, 'John Smith', 'ACC123456789', 'Chase Bank', '123 Main St, New York, NY 10001', 'Savings', 'CHASUS33', 'US12345678901234567890', '1234', 'Monthly transfer', 'TFR001'),
(2, 2500.00, 'Jane Doe', 'ACC987654321', 'Wells Fargo', '456 Oak Ave, San Francisco, CA 94102', 'Current', 'WFBIUS6S', 'US98765432109876543210', '5678', 'Business payment', 'TFR002'),
(3, 500.00, 'Mike Johnson', 'ACC555666777', 'Bank of America', '789 Pine Rd, Los Angeles, CA 90210', 'Savings', 'BOFAUS3N', 'US55566677788899900011', '9999', 'Personal transfer', 'TFR003');

-- Show the table structure
DESCRIBE transfers;

-- Show sample data
SELECT * FROM transfers LIMIT 3;
