<?php
/**
 * Enterprise Indexes Migration Runner
 * Adds performance indexes for high-concurrency operations (50+ users)
 */

require_once __DIR__ . '/config/config.php';
require_once __DIR__ . '/config/database.php';

/**
 * 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): bool {
    if (indexExists($table, $index)) {
        echo "- Index $index already exists on $table\n";
        return false;
    }

    try {
        db()->exec("ALTER TABLE $table ADD INDEX $index $definition");
        echo "✓ Created index $index on $table\n";
        return true;
    } catch (PDOException $e) {
        echo "✗ Error creating index $index on $table: " . substr($e->getMessage(), 0, 100) . "\n";
        return false;
    }
}

try {
    echo "=== Enterprise Indexes Migration ===\n\n";

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

    // Sales table indexes
    if (createIndexIfNotExists('sales', 'idx_tenant_created', '(tenant_id, created_at)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('sales', 'idx_tenant_status', '(tenant_id, status)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('sales', 'idx_tenant_customer', '(tenant_id, customer_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('sales', 'idx_tenant_location', '(tenant_id, location_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('sales', 'idx_sale_no', '(sale_no)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('sales', 'idx_payment_status', '(payment_status)')) $successCount++; else $skippedCount++;

    // Sale items indexes
    if (createIndexIfNotExists('sale_items', 'idx_sale_product', '(sale_id, product_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('sale_items', 'idx_tenant_product', '(tenant_id, product_id)')) $successCount++; else $skippedCount++;

    // Stock table indexes (critical for POS operations)
    if (createIndexIfNotExists('stock', 'idx_tenant_product_location', '(tenant_id, product_id, location_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('stock', 'idx_tenant_batch', '(tenant_id, batch_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('stock', 'idx_quantity', '(quantity)')) $successCount++; else $skippedCount++;

    // Products table indexes
    if (createIndexIfNotExists('products', 'idx_tenant_category', '(tenant_id, category_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('products', 'idx_tenant_sku', '(tenant_id, sku)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('products', 'idx_tenant_active', '(tenant_id, is_active)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('products', 'idx_name', '(name)')) $successCount++; else $skippedCount++;

    // Purchases table indexes
    if (createIndexIfNotExists('purchases', 'idx_tenant_created', '(tenant_id, created_at)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('purchases', 'idx_tenant_supplier', '(tenant_id, supplier_id)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('purchases', 'idx_payment_status', '(payment_status)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('purchases', 'idx_status', '(status)')) $successCount++; else $skippedCount++;

    // Purchase items indexes
    if (createIndexIfNotExists('purchase_items', 'idx_purchase_product', '(purchase_id, product_id)')) $successCount++; else $skippedCount++;

    // Customers table indexes
    if (createIndexIfNotExists('customers', 'idx_tenant_name', '(tenant_id, name)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('customers', 'idx_tenant_phone', '(tenant_id, phone)')) $successCount++; else $skippedCount++;

    // Suppliers table indexes
    if (createIndexIfNotExists('suppliers', 'idx_tenant_name', '(tenant_id, name)')) $successCount++; else $skippedCount++;

    // Categories table indexes
    if (createIndexIfNotExists('categories', 'idx_tenant_name', '(tenant_id, name)')) $successCount++; else $skippedCount++;

    // Locations table indexes
    if (createIndexIfNotExists('locations', 'idx_tenant_active', '(tenant_id, is_active)')) $successCount++; else $skippedCount++;

    // Users table indexes
    if (createIndexIfNotExists('users', 'idx_tenant_email', '(tenant_id, email)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('users', 'idx_tenant_role', '(tenant_id, role_id)')) $successCount++; else $skippedCount++;

    // Settings table indexes
    if (createIndexIfNotExists('settings', 'idx_tenant_key', '(tenant_id, setting_key)')) $successCount++; else $skippedCount++;

    // Composite indexes for common query patterns
    if (createIndexIfNotExists('sales', 'idx_tenant_status_created', '(tenant_id, status, created_at)')) $successCount++; else $skippedCount++;
    if (createIndexIfNotExists('stock', 'idx_tenant_product_loc_qty', '(tenant_id, product_id, location_id, quantity)')) $successCount++; else $skippedCount++;

    echo "\n=== Migration Complete ===\n";
    echo "Created: $successCount\n";
    echo "Skipped (already exists): $skippedCount\n";
    echo "Errors: $errorCount\n";

    if ($errorCount === 0) {
        echo "\n✓ Enterprise indexes migration completed successfully!\n";
        echo "Your database is now optimized for 50+ concurrent users.\n";
    } else {
        echo "\n⚠ Some indexes failed to create. Check the errors above.\n";
    }

} catch (Exception $e) {
    die("Error: " . $e->getMessage() . "\n");
}
