api-wipay/run_hardening_migration.php

<?php
/**
 * API Keys Hardening Migration Script
 * Run: php run_hardening_migration.php
 */

require __DIR__ . '/vendor/autoload.php';

// Load environment variables
if (file_exists(__DIR__ . '/.env')) {
    $lines = file(__DIR__ . '/.env', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos(trim($line), '#') === 0) {
            continue; // Skip comments
        }
        if (strpos($line, '=') !== false) {
            list($key, $value) = explode('=', $line, 2);
            $key = trim($key);
            $value = trim($value);
            $_ENV[$key] = $value;
            putenv("$key=$value");
        }
    }
}

use App\Config\Database;

try {
    $db = Database::getInstance();
    $connection = $db->getConnection();
    
    echo "🔒 Starting API Keys Hardening migration...\n\n";
    
    $tableName = 'api_keys';
    $columns = [
        'rate_limit_per_minute' => "INT DEFAULT 100 COMMENT 'Rate limit per minute (default: 100)'",
        'rate_limit_window' => "INT DEFAULT 60 COMMENT 'Rate limit window in seconds (default: 60)'",
        'enable_ip_whitelist' => "TINYINT(1) DEFAULT 0 COMMENT 'Enable IP whitelist (0=disabled, 1=enabled)'",
        'ip_whitelist' => "TEXT NULL COMMENT 'IP whitelist (comma-separated or JSON array)'",
        'expires_at' => "DATETIME NULL COMMENT 'API key expiration date (NULL = never expires)'",
        'last_used_at' => "DATETIME NULL COMMENT 'Last time API key was used'",
        'created_at' => "DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'API key creation date'",
        'updated_at' => "DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update date'"
    ];
    
    $indexes = [
        'idx_api_keys_expires_at' => 'expires_at',
        'idx_api_keys_is_active' => 'is_active',
        'idx_api_keys_last_used_at' => 'last_used_at'
    ];
    
    $successCount = 0;
    $errorCount = 0;
    
    // Add columns
    foreach ($columns as $columnName => $columnDef) {
        try {
            // Check if column exists
            $checkSql = "SELECT COUNT(*) as cnt FROM information_schema.COLUMNS 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = :table 
                        AND COLUMN_NAME = :column";
            $result = $db->fetchOne($checkSql, [
                'table' => $tableName,
                'column' => $columnName
            ]);
            
            if ($result && $result->cnt == 0) {
                // Column doesn't exist, add it
                // Remove COMMENT from column definition for ALTER TABLE
                $cleanDef = preg_replace('/\s+COMMENT\s+[\'"][^\'"]*[\'"]/i', '', $columnDef);
                $addSql = "ALTER TABLE `{$tableName}` ADD COLUMN `{$columnName}` {$cleanDef}";
                $connection->exec($addSql);
                echo "✅ Added column: {$tableName}.{$columnName}\n";
                $successCount++;
            } else {
                echo "⏭️  Column already exists: {$tableName}.{$columnName}\n";
            }
        } catch (\PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate column') !== false) {
                echo "⏭️  Column already exists: {$tableName}.{$columnName}\n";
            } else {
                echo "❌ Error adding column {$columnName}: " . $e->getMessage() . "\n";
                $errorCount++;
            }
        }
    }
    
    // Create indexes
    foreach ($indexes as $indexName => $columnName) {
        try {
            // Check if index exists
            $checkSql = "SELECT COUNT(*) as cnt FROM information_schema.STATISTICS 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = :table 
                        AND INDEX_NAME = :index";
            $result = $db->fetchOne($checkSql, [
                'table' => $tableName,
                'index' => $indexName
            ]);
            
            if ($result && $result->cnt == 0) {
                // Index doesn't exist, create it
                $createSql = "CREATE INDEX `{$indexName}` ON `{$tableName}` (`{$columnName}`)";
                $connection->exec($createSql);
                echo "✅ Created index: {$indexName} on {$tableName}({$columnName})\n";
                $successCount++;
            } else {
                echo "⏭️  Index already exists: {$indexName}\n";
            }
        } catch (\PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate key name') !== false || 
                strpos($e->getMessage(), 'already exists') !== false) {
                echo "⏭️  Index already exists: {$indexName}\n";
            } else {
                echo "❌ Error creating index {$indexName}: " . $e->getMessage() . "\n";
                $errorCount++;
            }
        }
    }
    
    echo "\n📊 Migration Summary:\n";
    echo "   ✅ Success: {$successCount}\n";
    echo "   ❌ Errors: {$errorCount}\n";
    
    if ($errorCount == 0) {
        echo "\n🎉 Hardening migration completed successfully!\n";
        echo "\n📋 Hardening Features Enabled:\n";
        echo "   ✅ Rate Limiting (default: 100 req/min)\n";
        echo "   ✅ IP Whitelist (optional, per API key)\n";
        echo "   ✅ API Key Expiration (optional, per API key)\n";
        echo "   ✅ Request Timestamp Validation (optional)\n";
        exit(0);
    } else {
        echo "\n⚠️  Migration completed with errors. Please review above.\n";
        exit(1);
    }
    
} catch (\Exception $e) {
    echo "❌ Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}