#!/bin/bash

# ========================================
# Database Index Application Script
# ========================================

echo "🚀 Starting Database Optimization Process..."
echo ""

# Step 1: Check database connection
echo "1️⃣  Checking database connection..."
php artisan db:show 2>/dev/null && echo "✓ Database connected" || echo "✗ Database connection failed"
echo ""

# Step 2: Backup database (optional but recommended)
echo "2️⃣  Creating database backup..."
echo "⚠️  Make sure to backup your database before proceeding!"
read -p "Have you backed up your database? (y/n): " backup_confirm
if [ "$backup_confirm" != "y" ]; then
    echo "❌ Please backup your database first"
    exit 1
fi
echo ""

# Step 3: Check current indexes
echo "3️⃣  Checking current indexes..."
php artisan tinker --execute="
\$indexes = DB::select('SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = \"products\" AND INDEX_NAME LIKE \"idx_%\" GROUP BY INDEX_NAME');
echo '📊 Current indexes on products table: ' . count(\$indexes) . PHP_EOL;
foreach(\$indexes as \$idx) {
    echo '  - ' . \$idx->INDEX_NAME . PHP_EOL;
}
"
echo ""

# Step 4: Run migration
echo "4️⃣  Running index migration..."
php artisan migrate --path=database/migrations/2024_12_09_000001_add_performance_indexes_to_products.php
if [ $? -eq 0 ]; then
    echo "✓ Migration completed successfully"
else
    echo "✗ Migration failed - check logs above"
    exit 1
fi
echo ""

# Step 5: Verify indexes were created
echo "5️⃣  Verifying indexes..."
php artisan tinker --execute="
\$indexes = DB::select('SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = \"products\" AND INDEX_NAME LIKE \"idx_%\" GROUP BY INDEX_NAME');
echo '📊 Total indexes created: ' . count(\$indexes) . PHP_EOL;
foreach(\$indexes as \$idx) {
    echo '  ✓ ' . \$idx->INDEX_NAME . PHP_EOL;
}
"
echo ""

# Step 6: Analyze tables
echo "6️⃣  Analyzing tables for optimization..."
php artisan tinker --execute="
DB::statement('ANALYZE TABLE products');
DB::statement('ANALYZE TABLE shops');
DB::statement('ANALYZE TABLE users');
DB::statement('ANALYZE TABLE product_quantity_list');
DB::statement('ANALYZE TABLE wishlists');
echo '✓ Table analysis completed' . PHP_EOL;
"
echo ""

# Step 7: Test query performance
echo "7️⃣  Testing query performance..."
echo "Running performance test query..."
php artisan tinker --execute="
\$start = microtime(true);
\$products = DB::table('products')
    ->where('published', 3)
    ->whereNotNull('product_published_date')
    ->orderBy('product_published_date', 'desc')
    ->limit(20)
    ->get();
\$duration = round((microtime(true) - \$start) * 1000, 2);
echo '⏱️  Query executed in: ' . \$duration . 'ms' . PHP_EOL;
echo '📦 Products fetched: ' . count(\$products) . PHP_EOL;
if (\$duration < 50) {
    echo '✅ Excellent performance!' . PHP_EOL;
} elseif (\$duration < 200) {
    echo '✓ Good performance' . PHP_EOL;
} else {
    echo '⚠️  Performance needs improvement' . PHP_EOL;
}
"
echo ""

echo "✅ Database optimization completed!"
echo ""
echo "📝 Next steps:"
echo "   1. Test the API endpoint"
echo "   2. Monitor query performance"
echo "   3. Check application logs"
echo "   4. Run: mysql -u [user] -p [database] < database_index_test.sql (for detailed analysis)"
echo ""