<?php
// Database setup script for Advanced User Management
include '../includes/db.php';

try {
    echo "Starting database setup for Advanced User Management...\n\n";
    
    // Read and execute SQL file
    $sql_content = file_get_contents('setup_advanced_users.sql');
    $statements = explode(';', $sql_content);
    
    $executed = 0;
    $errors = 0;
    
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (empty($statement) || substr($statement, 0, 2) === '--') {
            continue;
        }
        
        try {
            $pdo->exec($statement);
            $executed++;
            echo "✓ Executed: " . substr($statement, 0, 50) . "...\n";
        } catch (Exception $e) {
            $errors++;
            echo "✗ Error: " . substr($statement, 0, 50) . "... - " . $e->getMessage() . "\n";
        }
    }
    
    echo "\n=== Setup Summary ===\n";
    echo "Successfully executed: $executed statements\n";
    echo "Errors: $errors statements\n";
    
    if ($errors === 0) {
        echo "\n🎉 Database setup completed successfully!\n";
        
        // Check if tables were created
        echo "\n=== Verifying Tables ===\n";
        $tables = ['user_profiles', 'user_groups', 'user_group_memberships', 'group_event_access', 'user_permissions'];
        
        foreach ($tables as $table) {
            try {
                $result = $pdo->query("SELECT COUNT(*) FROM $table");
                echo "✓ Table '$table' exists and accessible\n";
            } catch (Exception $e) {
                echo "✗ Table '$table' error: " . $e->getMessage() . "\n";
            }
        }
        
        // Create initial super admin profile if needed
        echo "\n=== Creating Initial Profiles ===\n";
        try {
            // Find users without profiles
            $users_without_profiles = $pdo->query("
                SELECT u.id, u.username, u.role 
                FROM users u 
                LEFT JOIN user_profiles p ON u.id = p.user_id 
                WHERE p.id IS NULL
            ")->fetchAll(PDO::FETCH_ASSOC);
            
            foreach ($users_without_profiles as $user) {
                $full_name = ucwords(str_replace('_', ' ', $user['username']));
                $email = $user['username'] . '@example.com'; // Default email
                
                $stmt = $pdo->prepare("
                    INSERT INTO user_profiles (user_id, full_name, email, created_at) 
                    VALUES (?, ?, ?, NOW())
                ");
                $stmt->execute([$user['id'], $full_name, $email]);
                
                echo "✓ Created profile for user: {$user['username']} ({$user['role']})\n";
            }
            
            echo "\n✅ Setup completed successfully! You can now access the advanced user management panel.\n";
            echo "URL: /v2/admin/user_management.php\n";
            
        } catch (Exception $e) {
            echo "Warning: Could not create initial profiles - " . $e->getMessage() . "\n";
        }
        
    } else {
        echo "\n❌ Setup completed with errors. Please check the error messages above.\n";
    }
    
} catch (Exception $e) {
    echo "Fatal error: " . $e->getMessage() . "\n";
}
?>
