<?php
/**
 * Database Optimization & Indexing Script
 */

try {
    $pdo = require __DIR__ . '/../config/database.php';
    echo "Connected to database.\n";

    $queries = [
        // SEO Landings
        "CREATE INDEX IF NOT EXISTS idx_seo_landings_slug ON seo_landings(slug)",
        "CREATE INDEX IF NOT EXISTS idx_seo_landings_status ON seo_landings(status)",
        
        // Blog
        "CREATE INDEX IF NOT EXISTS idx_blog_posts_slug ON blog_posts(slug)",
        "CREATE INDEX IF NOT EXISTS idx_blog_posts_status ON blog_posts(status)",
        "CREATE INDEX IF NOT EXISTS idx_blog_posts_created ON blog_posts(created_at)",
        
        // Notifications
        "CREATE INDEX IF NOT EXISTS idx_notifications_user_status ON notifications(user_id, status)",
        "CREATE INDEX IF NOT EXISTS idx_notifications_created ON notifications(created_at)",
        
        // Email Queue
        "CREATE INDEX IF NOT EXISTS idx_email_queue_status ON email_queue(status)",
        "CREATE INDEX IF NOT EXISTS idx_email_queue_created ON email_queue(created_at)",
        
        // Applications
        "CREATE INDEX IF NOT EXISTS idx_applications_user ON applications(user_id)",
        "CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status)",
        
        // General Cleanup
        "ANALYZE",
        "VACUUM"
    ];

    foreach ($queries as $sql) {
        echo "Executing: $sql... ";
        $pdo->exec($sql);
        echo "OK\n";
    }

    echo "\nOptimization complete. Database performance improved.\n";

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