<?php
require_once 'config/database.php';

try {
    $pdo = getDB();

    // Create payment_gateway_settings table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS payment_gateway_settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            gateway_name ENUM('paynow', 'paypal') NOT NULL,
            gateway_type ENUM('sandbox', 'live') NOT NULL DEFAULT 'sandbox',
            api_key VARCHAR(255),
            api_secret VARCHAR(255),
            merchant_id VARCHAR(255),
            merchant_email VARCHAR(255),
            webhook_url VARCHAR(500),
            return_url VARCHAR(500),
            cancel_url VARCHAR(500),
            additional_settings JSON,
            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_env (gateway_name, gateway_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create payment_transactions table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS payment_transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            transaction_id VARCHAR(100) NOT NULL UNIQUE,
            reference_number VARCHAR(100) NOT NULL UNIQUE,
            gateway_name VARCHAR(50) NOT NULL,
            gateway_type VARCHAR(20) NOT NULL,
            user_id INT NOT NULL,
            item_type ENUM('course', 'exam', 'cart') NOT NULL DEFAULT 'course',
            item_id INT NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            currency VARCHAR(3) NOT NULL DEFAULT 'USD',
            discount_code VARCHAR(50),
            discount_amount DECIMAL(10,2) DEFAULT 0.00,
            final_amount DECIMAL(10,2) NOT NULL,
            status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled', 'refunded') DEFAULT 'pending',
            gateway_response JSON,
            gateway_metadata JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            processed_at TIMESTAMP NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_transaction_status (status),
            INDEX idx_transaction_user (user_id),
            INDEX idx_transaction_reference (reference_number),
            INDEX idx_transaction_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create payment_logs table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS payment_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            transaction_id INT,
            action VARCHAR(100) NOT NULL,
            level ENUM('info', 'warning', 'error') DEFAULT 'info',
            message TEXT,
            context JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE,
            INDEX idx_logs_action (action),
            INDEX idx_logs_level (level),
            INDEX idx_logs_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create payment_analytics table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS payment_analytics (
            id INT AUTO_INCREMENT PRIMARY KEY,
            date DATE NOT NULL,
            gateway_name VARCHAR(50) NOT NULL,
            currency VARCHAR(3) NOT NULL,
            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,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_analytics_date_gateway (date, gateway_name, currency)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create payment_refunds table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS payment_refunds (
            id INT AUTO_INCREMENT PRIMARY KEY,
            transaction_id INT NOT NULL,
            refund_amount DECIMAL(10,2) NOT NULL,
            reason TEXT,
            status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
            gateway_refund_id VARCHAR(255),
            gateway_response JSON,
            processed_by INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            processed_at TIMESTAMP NULL,
            FOREIGN KEY (transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE,
            FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_refunds_transaction (transaction_id),
            INDEX idx_refunds_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create discount_codes table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS discount_codes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            code VARCHAR(50) NOT NULL UNIQUE,
            description TEXT,
            discount_type ENUM('percentage', 'fixed') NOT NULL,
            discount_value DECIMAL(10,2) NOT NULL,
            max_discount_amount DECIMAL(10,2),
            min_purchase_amount DECIMAL(10,2) DEFAULT 0.00,
            usage_limit INT,
            usage_count INT DEFAULT 0,
            valid_from DATETIME,
            valid_until DATETIME,
            is_active BOOLEAN DEFAULT TRUE,
            created_by INT,
            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 SET NULL,
            INDEX idx_discount_code (code),
            INDEX idx_discount_active (is_active),
            INDEX idx_discount_valid (valid_from, valid_until)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Insert default gateway settings
    $pdo->exec("
        INSERT IGNORE INTO payment_gateway_settings
        (gateway_name, gateway_type, api_key, api_secret, merchant_id, merchant_email, webhook_url, return_url, cancel_url, is_active, test_mode)
        VALUES
        ('paynow', 'sandbox', '', '', '', '', 'http://localhost/lms/api/webhooks/paynow.php', 'http://localhost/lms/receipt.php', 'http://localhost/lms/checkout.php', 0, 1),
        ('paynow', 'live', '', '', '', '', 'https://yourdomain.com/api/webhooks/paynow.php', 'https://yourdomain.com/receipt.php', 'https://yourdomain.com/checkout.php', 0, 0),
        ('paypal', 'sandbox', '', '', '', '', 'http://localhost/lms/api/webhooks/paypal.php', 'http://localhost/lms/receipt.php', 'http://localhost/lms/checkout.php', 0, 1),
        ('paypal', 'live', '', '', '', '', 'https://yourdomain.com/api/webhooks/paypal.php', 'https://yourdomain.com/receipt.php', 'https://yourdomain.com/checkout.php', 0, 0)
    ");

    echo "Payment tables migration applied successfully.";
} catch (PDOException $e) {
    echo "Error applying payment tables migration: " . $e->getMessage();
}
?>