-- Payment System Tables for LMS
-- Add comprehensive payment processing, gateway settings, and transaction management

-- Payment gateway settings table
CREATE TABLE IF NOT EXISTS payment_gateway_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    gateway_name VARCHAR(50) NOT NULL, -- 'paypal', 'paynow'
    gateway_type ENUM('sandbox', 'live') DEFAULT 'sandbox',
    api_key VARCHAR(255) NULL,
    api_secret VARCHAR(255) NULL,
    merchant_id VARCHAR(255) NULL,
    merchant_email VARCHAR(255) NULL,
    webhook_url VARCHAR(500) NULL,
    return_url VARCHAR(500) NULL,
    cancel_url VARCHAR(500) NULL,
    additional_settings JSON NULL, -- gateway-specific settings
    is_active BOOLEAN DEFAULT FALSE,
    test_mode BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_gateway_type (gateway_name, gateway_type),
    INDEX idx_gateway_active (gateway_name, is_active)
);

-- Discount codes table
CREATE TABLE IF NOT EXISTS discount_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    description TEXT NULL,
    discount_type ENUM('percentage', 'fixed_amount') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    max_discount_amount DECIMAL(10,2) NULL, -- for percentage discounts
    min_purchase_amount DECIMAL(10,2) DEFAULT 0.00,
    usage_limit INT NULL, -- NULL for unlimited
    usage_count INT DEFAULT 0,
    valid_from TIMESTAMP NULL,
    valid_until TIMESTAMP NULL,
    applicable_items JSON NULL, -- specific courses/exams, NULL for all
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_code (code),
    INDEX idx_active (is_active),
    INDEX idx_valid_dates (valid_from, valid_until)
);

-- Payment transactions table
CREATE TABLE IF NOT EXISTS payment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id VARCHAR(255) UNIQUE NOT NULL, -- gateway transaction ID
    reference_number VARCHAR(255) UNIQUE NOT NULL, -- internal reference
    gateway_name VARCHAR(50) NOT NULL,
    gateway_type ENUM('sandbox', 'live') DEFAULT 'sandbox',
    user_id INT NOT NULL,
    item_type ENUM('course', 'exam', 'subscription') NOT NULL,
    item_id INT NOT NULL, -- course_id or exam_id
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    discount_code VARCHAR(50) NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    final_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled', 'refunded', 'partially_refunded') DEFAULT 'pending',
    payment_method VARCHAR(50) NULL, -- credit_card, paypal, paynow, etc.
    gateway_response JSON NULL,
    gateway_metadata JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    notes TEXT NULL,
    processed_at TIMESTAMP NULL,
    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 (discount_code) REFERENCES discount_codes(code) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_item (item_type, item_id),
    INDEX idx_status (status),
    INDEX idx_gateway (gateway_name, gateway_type),
    INDEX idx_created_at (created_at)
);

-- Refunds table
CREATE TABLE IF NOT EXISTS payment_refunds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    refund_transaction_id VARCHAR(255) UNIQUE NULL, -- gateway refund ID
    reference_number VARCHAR(255) UNIQUE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    reason TEXT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    gateway_response JSON NULL,
    processed_by INT NULL,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_transaction (transaction_id),
    INDEX idx_status (status),
    INDEX idx_processed_at (processed_at)
);

-- Payment methods table (for storing user payment methods)
CREATE TABLE IF NOT EXISTS user_payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    gateway_name VARCHAR(50) NOT NULL,
    payment_method_id VARCHAR(255) NOT NULL, -- gateway's payment method ID
    payment_type VARCHAR(50) NOT NULL, -- credit_card, paypal_account, etc.
    last_four VARCHAR(4) NULL, -- last 4 digits of card
    expiry_month INT NULL,
    expiry_year INT NULL,
    card_brand VARCHAR(50) NULL,
    billing_address JSON NULL,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    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,
    UNIQUE KEY unique_gateway_method (user_id, gateway_name, payment_method_id),
    INDEX idx_user (user_id),
    INDEX idx_default (user_id, is_default)
);

-- Payment logs table for debugging and audit
CREATE TABLE IF NOT EXISTS payment_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NULL,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    level ENUM('info', 'warning', 'error') DEFAULT 'info',
    message TEXT NOT NULL,
    context JSON NULL, -- additional data
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_transaction (transaction_id),
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_level (level),
    INDEX idx_created_at (created_at)
);

-- Payment analytics table
CREATE TABLE IF NOT EXISTS payment_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    gateway_name VARCHAR(50) NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    total_transactions INT DEFAULT 0,
    successful_transactions INT DEFAULT 0,
    failed_transactions INT DEFAULT 0,
    total_amount DECIMAL(15,2) DEFAULT 0.00,
    refunded_amount DECIMAL(15,2) DEFAULT 0.00,
    net_amount DECIMAL(15,2) DEFAULT 0.00,
    average_transaction DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_analytics (date, gateway_name, currency),
    INDEX idx_date (date),
    INDEX idx_gateway (gateway_name)
);

-- Insert default PayPal sandbox settings
INSERT INTO payment_gateway_settings (gateway_name, gateway_type, api_key, api_secret, merchant_email, is_active, test_mode) VALUES
('paypal', 'sandbox', 'YOUR_PAYPAL_SANDBOX_CLIENT_ID', 'YOUR_PAYPAL_SANDBOX_CLIENT_SECRET', 'sb-seller@example.com', FALSE, TRUE),
('paypal', 'live', 'YOUR_PAYPAL_LIVE_CLIENT_ID', 'YOUR_PAYPAL_LIVE_CLIENT_SECRET', 'seller@example.com', FALSE, FALSE);

-- Insert default Paynow settings (Zimbabwe)
INSERT INTO payment_gateway_settings (gateway_name, gateway_type, api_key, api_secret, merchant_id, is_active, test_mode) VALUES
('paynow', 'sandbox', 'YOUR_PAYNOW_SANDBOX_INTEGRATION_ID', 'YOUR_PAYNOW_SANDBOX_INTEGRATION_KEY', 'YOUR_PAYNOW_SANDBOX_ID', FALSE, TRUE),
('paynow', 'live', 'YOUR_PAYNOW_LIVE_INTEGRATION_ID', 'YOUR_PAYNOW_LIVE_INTEGRATION_KEY', 'YOUR_PAYNOW_LIVE_ID', FALSE, FALSE);

-- Insert sample discount codes
INSERT INTO discount_codes (code, description, discount_type, discount_value, usage_limit, valid_until, is_active, created_by) VALUES
('WELCOME10', 'Welcome discount for new students', 'percentage', 10.00, 100, DATE_ADD(NOW(), INTERVAL 1 YEAR), TRUE, 1),
('EARLYBIRD20', 'Early bird discount', 'percentage', 20.00, 50, DATE_ADD(NOW(), INTERVAL 3 MONTH), TRUE, 1),
('STUDENT50', 'Student discount', 'fixed_amount', 50.00, NULL, DATE_ADD(NOW(), INTERVAL 6 MONTH), TRUE, 1);

-- Create indexes for better performance
CREATE INDEX idx_payment_transactions_reference ON payment_transactions(reference_number);
CREATE INDEX idx_payment_transactions_created_status ON payment_transactions(created_at, status);
CREATE INDEX idx_payment_refunds_reference ON payment_refunds(reference_number);
CREATE INDEX idx_discount_codes_usage ON discount_codes(usage_count, usage_limit);
CREATE INDEX idx_payment_logs_action_created ON payment_logs(action, created_at);