{% extends "base.html" %}
{% load static %}

{% block title %}Database Performance Monitoring{% endblock %}

{% block extra_css %}
<style>
    .performance-card {
        background: linear-gradient(135deg, #2c3e50 0%, #34495e 100%);
        color: white;
        border-radius: 10px;
        padding: 20px;
        margin-bottom: 20px;
    }
    
    .metric-box {
        text-align: center;
        padding: 15px;
        background: rgba(255, 255, 255, 0.1);
        border-radius: 8px;
        margin: 5px;
    }
    
    .metric-box h4 {
        margin: 0;
        font-size: 1.8em;
        font-weight: bold;
    }
    
    .metric-box p {
        margin: 5px 0 0 0;
        opacity: 0.9;
    }
    
    .health-indicator {
        display: inline-block;
        width: 12px;
        height: 12px;
        border-radius: 50%;
        margin-right: 8px;
    }
    
    .health-healthy { background-color: #28a745; }
    .health-warning { background-color: #ffc107; }
    .health-error { background-color: #dc3545; }
    
    .query-table {
        font-size: 0.9em;
    }
    
    .query-snippet {
        font-family: 'Courier New', monospace;
        background: #f8f9fa;
        padding: 2px 4px;
        border-radius: 3px;
        max-width: 300px;
        overflow: hidden;
        text-overflow: ellipsis;
        white-space: nowrap;
    }
    
    .optimization-btn {
        margin: 5px;
    }
    
    .recommendation-item {
        border-left: 4px solid;
        padding: 10px 15px;
        margin-bottom: 10px;
        border-radius: 0 5px 5px 0;
    }
    
    .rec-high { border-left-color: #dc3545; background-color: #f8d7da; }
    .rec-medium { border-left-color: #ffc107; background-color: #fff3cd; }
    .rec-low { border-left-color: #17a2b8; background-color: #d1ecf1; }
    
    .performance-test-results {
        background: #f8f9fa;
        border-radius: 5px;
        padding: 15px;
        margin-top: 15px;
    }
    
    .grade-excellent { color: #28a745; font-weight: bold; }
    .grade-good { color: #17a2b8; font-weight: bold; }
    .grade-fair { color: #ffc107; font-weight: bold; }
    .grade-poor { color: #dc3545; font-weight: bold; }
    
    .real-time-stats {
        position: fixed;
        top: 20px;
        right: 20px;
        background: white;
        border: 1px solid #dee2e6;
        border-radius: 5px;
        padding: 10px;
        box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        z-index: 1000;
        min-width: 200px;
    }
    
    .auto-refresh-toggle {
        margin-bottom: 10px;
    }
</style>
{% endblock %}

{% block content %}
<div class="container-fluid">
    <!-- Real-time stats panel -->
    <div class="real-time-stats">
        <div class="auto-refresh-toggle">
            <div class="form-check form-switch">
                <input class="form-check-input" type="checkbox" id="autoRefresh">
                <label class="form-check-label" for="autoRefresh">Auto Refresh</label>
            </div>
        </div>
        <div id="realTimeStats">
            <small class="text-muted">Loading...</small>
        </div>
    </div>

    <div class="row">
        <div class="col-12">
            <h1><span>💾</span> Database Performance Monitoring</h1>
            <p class="text-muted">Monitor database performance, query optimization, and system health</p>
        </div>
    </div>

    <!-- Performance Overview -->
    <div class="row">
        <div class="col-12">
            <div class="performance-card">
                <h3><span>📈</span> Performance Overview</h3>
                <div class="row">
                    <div class="col-md-3">
                        <div class="metric-box">
                            <h4 id="cache-hits">{{ performance_stats.optimization_stats.cache_hits|default:0 }}</h4>
                            <p>Cache Hits</p>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="metric-box">
                            <h4 id="optimized-queries">{{ performance_stats.optimization_stats.optimized_queries|default:0 }}</h4>
                            <p>Optimized Queries</p>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="metric-box">
                            <h4 id="db-cache-ratio">{{ performance_stats.database_info.cache_hit_ratio|default:0|floatformat:1 }}%</h4>
                            <p>DB Cache Hit Ratio</p>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="metric-box">
                            <h4 id="active-connections">{{ performance_stats.database_info.active_connections|default:0 }}</h4>
                            <p>Active Connections</p>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="row">
        <!-- Database Health -->
        <div class="col-md-6">
            <div class="card">
                <div class="card-header">
                    <h4><i class="fas fa-heartbeat"></i> Database Health</h4>
                </div>
                <div class="card-body">
                    <div class="mb-3">
                        <span class="health-indicator health-{{ health_metrics.connection_health|lower }}"></span>
                        <strong>Connection:</strong> {{ health_metrics.connection_health }}
                    </div>
                    
                    <div class="mb-3">
                        <strong>Cache Hit Ratio:</strong> 
                        <span class="{% if health_metrics.cache_hit_ratio >= 95 %}text-success{% elif health_metrics.cache_hit_ratio >= 85 %}text-warning{% else %}text-danger{% endif %}">
                            {{ health_metrics.cache_hit_ratio|floatformat:1 }}%
                        </span>
                    </div>
                    
                    <div class="mb-3">
                        <strong>Lock Conflicts:</strong> 
                        <span class="{% if health_metrics.lock_conflicts == 0 %}text-success{% else %}text-warning{% endif %}">
                            {{ health_metrics.lock_conflicts }}
                        </span>
                    </div>
                    
                    {% if health_metrics.table_bloat %}
                    <div class="mb-3">
                        <strong>Table Bloat Issues:</strong>
                        <ul class="list-unstyled mt-2">
                            {% for bloat in health_metrics.table_bloat|slice:":3" %}
                            <li class="small">
                                {{ bloat.table }}: {{ bloat.bloat_ratio }}% bloat
                            </li>
                            {% endfor %}
                        </ul>
                    </div>
                    {% endif %}
                    
                    <div class="mt-3">
                        <strong>Database:</strong> 
                        {% if performance_stats.database_info.version %}
                            {{ performance_stats.database_info.version|truncatewords:3 }}
                        {% else %}
                            Unknown
                        {% endif %}
                    </div>
                </div>
            </div>
        </div>

        <!-- Optimization Actions -->
        <div class="col-md-6">
            <div class="card">
                <div class="card-header">
                    <h4><span>🛠️</span> Optimization Actions</h4>
                </div>
                <div class="card-body">
                    <form method="post" action="{% url 'run_database_optimization' %}" class="mb-3">
                        {% csrf_token %}
                        <input type="hidden" name="optimization_type" value="create_indexes">
                        <button type="submit" class="btn btn-primary optimization-btn">
                            <span>+</span> Create Indexes
                        </button>
                    </form>

                    <form method="post" action="{% url 'run_database_optimization' %}" class="mb-3">
                        {% csrf_token %}
                        <input type="hidden" name="optimization_type" value="optimize_queries">
                        <button type="submit" class="btn btn-info optimization-btn">
                            <i class="fas fa-rocket"></i> Optimize Queries
                        </button>
                    </form>

                    <form method="post" action="{% url 'run_database_optimization' %}" class="mb-3">
                        {% csrf_token %}
                        <input type="hidden" name="optimization_type" value="vacuum_analyze">
                        <button type="submit" class="btn btn-warning optimization-btn">
                            <i class="fas fa-broom"></i> VACUUM ANALYZE
                        </button>
                    </form>

                    <form method="post" action="{% url 'run_database_optimization' %}" class="mb-3">
                        {% csrf_token %}
                        <input type="hidden" name="optimization_type" value="clear_cache">
                        <button type="submit" class="btn btn-secondary optimization-btn">
                            <span>🗑️</span> Clear Cache
                        </button>
                    </form>

                    <hr>

                    <button type="button" class="btn btn-success" onclick="runPerformanceTest()">
                        <i class="fas fa-stopwatch"></i> Test Performance
                    </button>

                    <div id="performanceTestResults" class="performance-test-results" style="display: none;">
                        <!-- Performance test results will be loaded here -->
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Query Performance -->
    {% if query_performance.slow_queries %}
    <div class="row mt-4">
        <div class="col-12">
            <div class="card">
                <div class="card-header">
                    <h4><span>⏰</span> Slow Queries</h4>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-sm query-table">
                            <thead>
                                <tr>
                                    <th>Query</th>
                                    <th>Calls</th>
                                    <th>Mean Time (ms)</th>
                                    <th>Total Time (ms)</th>
                                    <th>Rows</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for query in query_performance.slow_queries|slice:":10" %}
                                <tr>
                                    <td><code class="query-snippet">{{ query.query }}</code></td>
                                    <td>{{ query.calls }}</td>
                                    <td class="{% if query.mean_time > 100 %}text-danger{% elif query.mean_time > 50 %}text-warning{% endif %}">
                                        {{ query.mean_time }}
                                    </td>
                                    <td>{{ query.total_time }}</td>
                                    <td>{{ query.rows }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    {% endif %}

    <!-- Table Statistics -->
    {% if query_performance.table_stats %}
    <div class="row mt-4">
        <div class="col-md-6">
            <div class="card">
                <div class="card-header">
                    <h4><i class="fas fa-table"></i> Table Statistics</h4>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Table</th>
                                    <th>Live Tuples</th>
                                    <th>Dead Tuples</th>
                                    <th>Updates</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for stat in query_performance.table_stats %}
                                <tr>
                                    <td><strong>{{ stat.table }}</strong></td>
                                    <td>{{ stat.live_tuples|default:0 }}</td>
                                    <td class="{% if stat.dead_tuples > 1000 %}text-warning{% endif %}">
                                        {{ stat.dead_tuples|default:0 }}
                                    </td>
                                    <td>{{ stat.updates|default:0 }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <!-- Index Usage -->
        <div class="col-md-6">
            <div class="card">
                <div class="card-header">
                    <h4><span>🔍</span> Index Usage</h4>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-sm">
                            <thead>
                                <tr>
                                    <th>Index</th>
                                    <th>Table</th>
                                    <th>Reads</th>
                                    <th>Fetches</th>
                                </tr>
                            </thead>
                            <tbody>
                                {% for idx in query_performance.index_usage|slice:":10" %}
                                <tr>
                                    <td class="small">{{ idx.index|truncatechars:20 }}</td>
                                    <td>{{ idx.table }}</td>
                                    <td>{{ idx.tuples_read|default:0 }}</td>
                                    <td>{{ idx.tuples_fetched|default:0 }}</td>
                                </tr>
                                {% endfor %}
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    {% endif %}

    <!-- Recommendations -->
    {% if recommendations %}
    <div class="row mt-4">
        <div class="col-12">
            <div class="card">
                <div class="card-header">
                    <h4><span>💡</span> Performance Recommendations</h4>
                </div>
                <div class="card-body">
                    {% for rec in recommendations %}
                    <div class="recommendation-item rec-{{ rec.priority|default:'low' }}">
                        <h6><strong>[{{ rec.priority|upper }}] {{ rec.title }}</strong></h6>
                        <p>{{ rec.description }}</p>
                        {% if rec.details %}
                        <details>
                            <summary class="small text-muted">Details</summary>
                            <pre class="small mt-2">{{ rec.details|pprint }}</pre>
                        </details>
                        {% endif %}
                    </div>
                    {% endfor %}
                </div>
            </div>
        </div>
    </div>
    {% endif %}

    <!-- Last Updated -->
    <div class="row mt-3">
        <div class="col-12">
            <p class="text-muted text-center">
                Last updated: <span id="lastUpdated">{{ last_updated|date:"Y-m-d H:i:s" }}</span>
            </p>
        </div>
    </div>
</div>
{% endblock %}

{% block extra_js %}
<script>
document.addEventListener('DOMContentLoaded', function() {
    let autoRefreshInterval;
    const autoRefreshCheckbox = document.getElementById('autoRefresh');
    
    // Auto-refresh functionality
    autoRefreshCheckbox.addEventListener('change', function() {
        if (this.checked) {
            autoRefreshInterval = setInterval(refreshStats, 30000); // 30 seconds
            refreshStats(); // Immediate refresh
        } else {
            clearInterval(autoRefreshInterval);
        }
    });
    
    function refreshStats() {
        // Update database statistics
        fetch('{% url "database_statistics_api" %}')
            .then(response => response.json())
            .then(data => {
                if (!data.error) {
                    // Update main metrics
                    if (data.optimization_stats) {
                        document.getElementById('cache-hits').textContent = data.optimization_stats.cache_hits || 0;
                        document.getElementById('optimized-queries').textContent = data.optimization_stats.optimized_queries || 0;
                    }
                    
                    if (data.database_info) {
                        document.getElementById('db-cache-ratio').textContent = 
                            (data.database_info.cache_hit_ratio || 0).toFixed(1) + '%';
                        document.getElementById('active-connections').textContent = 
                            data.database_info.active_connections || 0;
                    }
                    
                    // Update real-time stats
                    if (data.real_time) {
                        const realTimeStats = document.getElementById('realTimeStats');
                        realTimeStats.innerHTML = `
                            <div class="small">
                                <div><strong>Active:</strong> ${data.real_time.active_connections}</div>
                                <div><strong>Idle:</strong> ${data.real_time.idle_connections}</div>
                                <div><strong>Total:</strong> ${data.real_time.total_connections}</div>
                                <div class="text-muted mt-1">${new Date(data.real_time.timestamp).toLocaleTimeString()}</div>
                            </div>
                        `;
                    }
                    
                    document.getElementById('lastUpdated').textContent = 
                        new Date().toLocaleString();
                }
            })
            .catch(error => {
                console.error('Error refreshing database stats:', error);
                document.getElementById('realTimeStats').innerHTML = 
                    '<small class="text-danger">Error loading stats</small>';
            });
    }
    
    // Add confirmation to destructive actions
    document.querySelectorAll('form[action*="optimization"]').forEach(form => {
        form.addEventListener('submit', function(e) {
            const optimizationType = this.querySelector('input[name="optimization_type"]').value;
            
            if (optimizationType === 'vacuum_analyze' || optimizationType === 'clear_cache') {
                if (!confirm(`Are you sure you want to run ${optimizationType.replace('_', ' ')}? This may temporarily affect performance.`)) {
                    e.preventDefault();
                }
            }
        });
    });
    
    // Add loading states to buttons
    document.querySelectorAll('button[type="submit"]').forEach(button => {
        button.addEventListener('click', function() {
            const originalText = this.innerHTML;
            this.innerHTML = '<span>⟳</span> Processing...';
            this.disabled = true;
            
            // Re-enable after form submission
            setTimeout(() => {
                this.innerHTML = originalText;
                this.disabled = false;
            }, 5000);
        });
    });
});

function runPerformanceTest() {
    const resultsDiv = document.getElementById('performanceTestResults');
    const button = event.target;
    
    // Show loading state
    button.innerHTML = '<span>⟳</span> Testing...';
    button.disabled = true;
    resultsDiv.style.display = 'block';
    resultsDiv.innerHTML = '<div class="text-center"><span>⟳</span> Running performance tests...</div>';
    
    fetch('{% url "test_query_performance" %}', {
        method: 'POST',
        headers: {
            'X-CSRFToken': document.querySelector('[name=csrfmiddlewaretoken]').value,
            'Content-Type': 'application/json'
        }
    })
    .then(response => response.json())
    .then(data => {
        if (data.error) {
            resultsDiv.innerHTML = `<div class="alert alert-danger">Error: ${data.error}</div>`;
        } else {
            const results = data.test_results;
            let html = `
                <h6>Performance Test Results</h6>
                <div class="mb-2">
                    <strong>Overall Grade:</strong> 
                    <span class="grade-${results.performance_grade.toLowerCase()}">${results.performance_grade}</span>
                    <small class="text-muted">(${results.total_time.toFixed(2)}ms total)</small>
                </div>
                <div class="table-responsive">
                    <table class="table table-sm">
                        <thead>
                            <tr>
                                <th>Test</th>
                                <th>Operations</th>
                                <th>Total Time</th>
                                <th>Avg Time</th>
                            </tr>
                        </thead>
                        <tbody>
            `;
            
            results.tests.forEach(test => {
                html += `
                    <tr>
                        <td>${test.name}</td>
                        <td>${test.operations}</td>
                        <td>${test.time_ms.toFixed(2)}ms</td>
                        <td>${test.avg_time_ms.toFixed(2)}ms</td>
                    </tr>
                `;
            });
            
            html += `
                        </tbody>
                    </table>
                </div>
                <small class="text-muted">Test completed at ${new Date(data.timestamp).toLocaleString()}</small>
            `;
            
            resultsDiv.innerHTML = html;
        }
    })
    .catch(error => {
        resultsDiv.innerHTML = `<div class="alert alert-danger">Error running performance test: ${error}</div>`;
    })
    .finally(() => {
        button.innerHTML = '<i class="fas fa-stopwatch"></i> Test Performance';
        button.disabled = false;
    });
}
</script>
{% endblock %}