<?php
/**
 * Enterprise Indexes Migration Runner (Web Version)
 * Adds performance indexes for high-concurrency operations (50+ users)
 * Access via: http://yourdomain.com/public/run_enterprise_indexes_migration.php
 */

// Bootstrap the application
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../config/database.php';

// Security: Only allow access from admin users or localhost
$allowedIPs = ['127.0.0.1', '::1'];
$remoteIP = $_SERVER['REMOTE_ADDR'] ?? '';

if (!in_array($remoteIP, $allowedIPs) && !isset($_GET['token'])) {
    http_response_code(403);
    die('Access denied. This script can only be run from localhost or with a valid token.');
}

/**
 * Check if an index exists on a table
 */
function indexExists(string $table, string $index): bool {
    $stmt = db()->prepare("
        SELECT COUNT(*) 
        FROM information_schema.statistics 
        WHERE table_schema = DATABASE() 
          AND table_name = ? 
          AND index_name = ?
    ");
    $stmt->execute([$table, $index]);
    return $stmt->fetchColumn() > 0;
}

/**
 * Create index if it doesn't exist
 */
function createIndexIfNotExists(string $table, string $index, string $definition): array {
    if (indexExists($table, $index)) {
        return [
            'status' => 'skipped',
            'message' => "Index $index already exists on $table"
        ];
    }

    try {
        db()->exec("ALTER TABLE $table ADD INDEX $index $definition");
        return [
            'status' => 'success',
            'message' => "Created index $index on $table"
        ];
    } catch (PDOException $e) {
        return [
            'status' => 'error',
            'message' => "Error creating index $index on $table: " . substr($e->getMessage(), 0, 100)
        ];
    }
}

// Set content type
header('Content-Type: text/html; charset=utf-8');

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Enterprise Indexes Migration</title>
    <style>
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            margin-bottom: 10px;
        }
        .subtitle {
            color: #666;
            margin-bottom: 30px;
        }
        .log {
            background: #f9f9f9;
            border: 1px solid #ddd;
            border-radius: 4px;
            padding: 15px;
            margin: 20px 0;
            font-family: 'Courier New', monospace;
            font-size: 13px;
            max-height: 400px;
            overflow-y: auto;
        }
        .log-entry {
            padding: 3px 0;
            border-bottom: 1px solid #eee;
        }
        .log-entry:last-child {
            border-bottom: none;
        }
        .success { color: #22c55e; }
        .skipped { color: #f59e0b; }
        .error { color: #ef4444; }
        .summary {
            background: #e0f2fe;
            border: 1px solid #0284c7;
            border-radius: 4px;
            padding: 15px;
            margin: 20px 0;
        }
        .summary h2 {
            margin-top: 0;
            color: #0369a1;
        }
        .stats {
            display: grid;
            grid-template-columns: repeat(3, 1fr);
            gap: 15px;
            margin-top: 15px;
        }
        .stat-box {
            background: white;
            padding: 15px;
            border-radius: 4px;
            text-align: center;
        }
        .stat-value {
            font-size: 24px;
            font-weight: bold;
            color: #0369a1;
        }
        .stat-label {
            font-size: 12px;
            color: #666;
            margin-top: 5px;
        }
        .btn {
            display: inline-block;
            padding: 10px 20px;
            background: #0284c7;
            color: white;
            text-decoration: none;
            border-radius: 4px;
            margin-right: 10px;
        }
        .btn:hover {
            background: #0369a1;
        }
        .btn-secondary {
            background: #64748b;
        }
        .btn-secondary:hover {
            background: #475569;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Enterprise Indexes Migration</h1>
        <p class="subtitle">Adding performance indexes for high-concurrency operations (50+ users)</p>

        <?php
        try {
            echo '<div class="log">';

            $successCount = 0;
            $skippedCount = 0;
            $errorCount = 0;

            // Sales table indexes
            $result = createIndexIfNotExists('sales', 'idx_tenant_created', '(tenant_id, created_at)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('sales', 'idx_tenant_status', '(tenant_id, status)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('sales', 'idx_tenant_customer', '(tenant_id, customer_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('sales', 'idx_tenant_location', '(tenant_id, location_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('sales', 'idx_sale_no', '(sale_no)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('sales', 'idx_payment_status', '(payment_status)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Sale items indexes
            $result = createIndexIfNotExists('sale_items', 'idx_sale_product', '(sale_id, product_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('sale_items', 'idx_tenant_product', '(tenant_id, product_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Stock table indexes (critical for POS operations)
            $result = createIndexIfNotExists('stock', 'idx_tenant_product_location', '(tenant_id, product_id, location_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('stock', 'idx_tenant_batch', '(tenant_id, batch_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('stock', 'idx_quantity', '(quantity)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Products table indexes
            $result = createIndexIfNotExists('products', 'idx_tenant_category', '(tenant_id, category_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('products', 'idx_tenant_sku', '(tenant_id, sku)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('products', 'idx_tenant_active', '(tenant_id, is_active)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('products', 'idx_name', '(name)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Purchases table indexes
            $result = createIndexIfNotExists('purchases', 'idx_tenant_created', '(tenant_id, created_at)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('purchases', 'idx_tenant_supplier', '(tenant_id, supplier_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('purchases', 'idx_payment_status', '(payment_status)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('purchases', 'idx_status', '(status)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Purchase items indexes
            $result = createIndexIfNotExists('purchase_items', 'idx_purchase_product', '(purchase_id, product_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Customers table indexes
            $result = createIndexIfNotExists('customers', 'idx_tenant_name', '(tenant_id, name)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('customers', 'idx_tenant_phone', '(tenant_id, phone)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Suppliers table indexes
            $result = createIndexIfNotExists('suppliers', 'idx_tenant_name', '(tenant_id, name)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Categories table indexes
            $result = createIndexIfNotExists('categories', 'idx_tenant_name', '(tenant_id, name)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Locations table indexes
            $result = createIndexIfNotExists('locations', 'idx_tenant_active', '(tenant_id, is_active)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Users table indexes
            $result = createIndexIfNotExists('users', 'idx_tenant_email', '(tenant_id, email)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('users', 'idx_tenant_role', '(tenant_id, role_id)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Settings table indexes
            $result = createIndexIfNotExists('settings', 'idx_tenant_key', '(tenant_id, setting_key)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            // Composite indexes for common query patterns
            $result = createIndexIfNotExists('sales', 'idx_tenant_status_created', '(tenant_id, status, created_at)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            $result = createIndexIfNotExists('stock', 'idx_tenant_product_loc_qty', '(tenant_id, product_id, location_id, quantity)');
            echo '<div class="log-entry ' . $result['status'] . '">' . htmlspecialchars($result['message']) . '</div>';
            if ($result['status'] === 'success') $successCount++; elseif ($result['status'] === 'skipped') $skippedCount++; else $errorCount++;

            echo '</div>';

            echo '<div class="summary">';
            echo '<h2>Migration Complete</h2>';
            echo '<div class="stats">';
            echo '<div class="stat-box">';
            echo '<div class="stat-value">' . $successCount . '</div>';
            echo '<div class="stat-label">Created</div>';
            echo '</div>';
            echo '<div class="stat-box">';
            echo '<div class="stat-value">' . $skippedCount . '</div>';
            echo '<div class="stat-label">Skipped</div>';
            echo '</div>';
            echo '<div class="stat-box">';
            echo '<div class="stat-value">' . $errorCount . '</div>';
            echo '<div class="stat-label">Errors</div>';
            echo '</div>';
            echo '</div>';

            if ($errorCount === 0) {
                echo '<p style="margin-top: 15px; color: #22c55e;">✓ Enterprise indexes migration completed successfully! Your database is now optimized for 50+ concurrent users.</p>';
            } else {
                echo '<p style="margin-top: 15px; color: #ef4444;">⚠ Some indexes failed to create. Check the errors above.</p>';
            }
            echo '</div>';

            echo '<div style="margin-top: 20px;">';
            echo '<a href="' . APP_URL . '/dashboard" class="btn">Return to Dashboard</a>';
            echo '<a href="' . APP_URL . '/public/run_enterprise_indexes_migration.php" class="btn btn-secondary">Run Again</a>';
            echo '</div>';

        } catch (Exception $e) {
            echo '<div class="summary" style="background: #fee2e2; border-color: #dc2626;">';
            echo '<h2 style="color: #dc2626;">Error</h2>';
            echo '<p>' . htmlspecialchars($e->getMessage()) . '</p>';
            echo '</div>';
        }
        ?>
    </div>
</body>
</html>
