<?php
/**
 * ============================================================================
 * admin/database-manager.php - Database Management Console
 * Complete database administration interface
 * ============================================================================
 */

require_once __DIR__ . '/includes/base.php';

if (!hasAdminPermission('manage_server')) {
    setFlashMessage('Access denied. Server management permission required.', 'error');
    header('Location: ' . APP_URL . '/admin/index.php');
    exit();
}

$breadcrumbs = [['title' => 'Database Manager', 'url' => '']];

// Get database information
try {
    // Get all tables
    $sql = "SELECT 
                table_name,
                engine,
                table_rows,
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
                ROUND((data_length / 1024 / 1024), 2) AS data_mb,
                ROUND((index_length / 1024 / 1024), 2) AS index_mb,
                table_collation,
                create_time,
                update_time
            FROM information_schema.tables 
            WHERE table_schema = ?
            ORDER BY table_name";
    $stmt = $db->prepare($sql);
    $stmt->execute([DB_NAME]);
    $tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Database summary
    $sql = "SELECT 
                COUNT(*) as table_count,
                SUM(table_rows) as total_rows,
                SUM(data_length + index_length) / 1024 / 1024 AS total_size_mb
            FROM information_schema.tables 
            WHERE table_schema = ?";
    $stmt = $db->prepare($sql);
    $stmt->execute([DB_NAME]);
    $db_summary = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Database variables
    $sql = "SHOW VARIABLES LIKE '%version%'";
    $stmt = $db->query($sql);
    $db_vars = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
    
} catch (PDOException $e) {
    logError('Database manager error: ' . $e->getMessage());
    $tables = [];
    $db_summary = ['table_count' => 0, 'total_rows' => 0, 'total_size_mb' => 0];
    $db_vars = [];
}

renderAdminHeader('Database Manager', $breadcrumbs);
?>

<style>
.sql-editor {
    font-family: 'Courier New', monospace;
    font-size: 14px;
    min-height: 200px;
}
.query-result {
    max-height: 400px;
    overflow-y: auto;
}
.table-action-btn {
    padding: 0.25rem 0.5rem;
    font-size: 0.875rem;
}
</style>

<!-- Page Header -->
<div class="d-flex justify-content-between align-items-center mb-4">
    <div>
        <h2 class="mb-1 fw-bold">
            <i class="bi bi-database-fill-gear me-2"></i>Database Manager
        </h2>
        <p class="text-muted mb-0">Complete database administration and management</p>
    </div>
    <div class="d-flex gap-2">
        <button class="btn btn-outline-danger" onclick="showDangerZone()">
            <i class="bi bi-exclamation-triangle me-2"></i>Danger Zone
        </button>
    </div>
</div>

<!-- Alert Warning -->
<div class="alert alert-warning border-0 shadow-sm mb-4">
    <i class="bi bi-exclamation-triangle-fill me-2"></i>
    <strong>Warning:</strong> Direct database operations can cause data loss. Always backup before making changes.
</div>

<!-- Database Summary -->
<div class="row mb-4">
    <div class="col-md-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted mb-0">Database</h6>
                <h4 class="fw-bold mb-0"><?php echo DB_NAME; ?></h4>
                <small class="text-muted"><?php echo $db_vars['version'] ?? 'MySQL'; ?></small>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted mb-0">Total Tables</h6>
                <h4 class="fw-bold mb-0"><?php echo number_format($db_summary['table_count']); ?></h4>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted mb-0">Total Rows</h6>
                <h4 class="fw-bold mb-0"><?php echo number_format($db_summary['total_rows']); ?></h4>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted mb-0">Total Size</h6>
                <h4 class="fw-bold mb-0"><?php echo number_format($db_summary['total_size_mb'], 2); ?> MB</h4>
            </div>
        </div>
    </div>
</div>

<!-- SQL Query Console -->
<div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white py-3">
        <div class="d-flex justify-content-between align-items-center">
            <h5 class="mb-0 fw-bold">
                <i class="bi bi-terminal me-2"></i>SQL Query Console
            </h5>
            <div>
                <button class="btn btn-sm btn-outline-secondary" onclick="loadQueryTemplate('select')">
                    <i class="bi bi-file-earmark-code"></i> SELECT Template
                </button>
                <button class="btn btn-sm btn-primary" onclick="executeQuery()">
                    <i class="bi bi-play-fill"></i> Execute Query
                </button>
            </div>
        </div>
    </div>
    <div class="card-body">
        <textarea class="form-control sql-editor" id="sqlQuery" placeholder="Enter SQL query here...
Example: SELECT * FROM users LIMIT 10"></textarea>
        
        <div id="queryResult" class="mt-3" style="display: none;">
            <h6 class="fw-bold">Query Result:</h6>
            <div class="query-result border rounded p-3 bg-light">
                <div id="queryResultContent"></div>
            </div>
        </div>
    </div>
</div>

<!-- Database Tables -->
<div class="card border-0 shadow-sm">
    <div class="card-header bg-white py-3">
        <div class="d-flex justify-content-between align-items-center">
            <h5 class="mb-0 fw-bold">
                <i class="bi bi-table me-2"></i>Database Tables (<?php echo count($tables); ?>)
            </h5>
            <input type="text" class="form-control form-control-sm" style="width: 250px;" 
                   placeholder="Search tables..." onkeyup="filterTables(this.value)">
        </div>
    </div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-hover mb-0" id="tablesTable">
                <thead class="table-light">
                    <tr>
                        <th>Table Name</th>
                        <th>Engine</th>
                        <th class="text-end">Rows</th>
                        <th class="text-end">Size</th>
                        <th class="text-end">Data</th>
                        <th class="text-end">Index</th>
                        <th>Last Updated</th>
                        <th class="text-center">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($tables as $table): ?>
                    <tr>
                        <td class="fw-semibold">
                            <i class="bi bi-table me-2 text-primary"></i>
                            <?php echo htmlspecialchars($table['table_name']); ?>
                        </td>
                        <td><span class="badge bg-secondary"><?php echo $table['engine']; ?></span></td>
                        <td class="text-end"><?php echo number_format($table['table_rows']); ?></td>
                        <td class="text-end"><?php echo number_format($table['size_mb'], 2); ?> MB</td>
                        <td class="text-end"><?php echo number_format($table['data_mb'], 2); ?> MB</td>
                        <td class="text-end"><?php echo number_format($table['index_mb'], 2); ?> MB</td>
                        <td><?php echo $table['update_time'] ? date('Y-m-d H:i', strtotime($table['update_time'])) : '-'; ?></td>
                        <td class="text-center">
                            <div class="btn-group btn-group-sm" role="group">
                                <button class="btn btn-outline-primary table-action-btn" 
                                        onclick="browseTable('<?php echo $table['table_name']; ?>')">
                                    <i class="bi bi-eye"></i> Browse
                                </button>
                                <button class="btn btn-outline-success table-action-btn" 
                                        onclick="getTableStructure('<?php echo $table['table_name']; ?>')">
                                    <i class="bi bi-gear"></i> Structure
                                </button>
                                <button class="btn btn-outline-warning table-action-btn" 
                                        onclick="optimizeTable('<?php echo $table['table_name']; ?>')">
                                    <i class="bi bi-arrow-repeat"></i> Optimize
                                </button>
                                <button class="btn btn-outline-danger table-action-btn" 
                                        onclick="truncateTable('<?php echo $table['table_name']; ?>')">
                                    <i class="bi bi-trash"></i> Truncate
                                </button>
                            </div>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<!-- Table Data Modal -->
<div class="modal fade" id="tableDataModal" tabindex="-1">
    <div class="modal-dialog modal-xl">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title"><i class="bi bi-table me-2"></i><span id="modalTableName"></span></h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <div id="tableDataContent" class="table-responsive" style="max-height: 500px; overflow-y: auto;">
                    <div class="text-center py-5">
                        <div class="spinner-border text-primary" role="status"></div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
// Execute SQL Query
function executeQuery() {
    const query = $('#sqlQuery').val().trim();
    
    if (!query) {
        showToast('Please enter a SQL query', 'warning');
        return;
    }
    
    // Warn on dangerous queries
    const dangerousKeywords = ['DROP', 'DELETE', 'TRUNCATE', 'ALTER'];
    const upperQuery = query.toUpperCase();
    let isDangerous = false;
    
    dangerousKeywords.forEach(keyword => {
        if (upperQuery.includes(keyword)) {
            isDangerous = true;
        }
    });
    
    if (isDangerous) {
        Swal.fire({
            title: 'Dangerous Query Detected!',
            text: 'This query can modify or delete data. Are you sure?',
            icon: 'warning',
            showCancelButton: true,
            confirmButtonColor: '#dc2626',
            confirmButtonText: 'Yes, Execute',
            cancelButtonText: 'Cancel'
        }).then((result) => {
            if (result.isConfirmed) {
                runQuery(query);
            }
        });
    } else {
        runQuery(query);
    }
}

function runQuery(query) {
    showLoading();
    
    $.post(APP_URL + '/admin/actions/database-operations.php', {
        action: 'execute_query',
        query: query
    }).done(function(response) {
        hideLoading();
        
        if (response.success) {
            $('#queryResult').show();
            
            if (response.data && response.data.length > 0) {
                // Display results in table
                let html = '<table class="table table-sm table-bordered"><thead><tr>';
                
                // Headers
                Object.keys(response.data[0]).forEach(key => {
                    html += `<th>${key}</th>`;
                });
                html += '</tr></thead><tbody>';
                
                // Rows
                response.data.forEach(row => {
                    html += '<tr>';
                    Object.values(row).forEach(val => {
                        html += `<td>${val !== null ? val : '<em class="text-muted">NULL</em>'}</td>`;
                    });
                    html += '</tr>';
                });
                html += '</tbody></table>';
                html += `<small class="text-muted">${response.data.length} rows returned</small>`;
                
                $('#queryResultContent').html(html);
            } else {
                $('#queryResultContent').html(`
                    <div class="alert alert-success mb-0">
                        <i class="bi bi-check-circle me-2"></i>${response.message || 'Query executed successfully'}
                    </div>
                `);
            }
            
            showToast(response.message || 'Query executed successfully', 'success');
        } else {
            $('#queryResult').show();
            $('#queryResultContent').html(`
                <div class="alert alert-danger mb-0">
                    <i class="bi bi-x-circle me-2"></i><strong>Error:</strong> ${response.message}
                </div>
            `);
            showToast('Query failed: ' + response.message, 'error');
        }
    }).fail(function() {
        hideLoading();
        showToast('Connection error', 'error');
    });
}

// Browse Table
function browseTable(tableName) {
    $('#modalTableName').text(tableName + ' - Data');
    $('#tableDataModal').modal('show');
    
    $.post(APP_URL + '/admin/actions/database-operations.php', {
        action: 'browse_table',
        table: tableName,
        limit: 100
    }).done(function(response) {
        if (response.success && response.data) {
            let html = '<table class="table table-sm table-hover"><thead class="table-light"><tr>';
            
            // Headers
            Object.keys(response.data[0] || {}).forEach(key => {
                html += `<th>${key}</th>`;
            });
            html += '</tr></thead><tbody>';
            
            // Rows
            response.data.forEach(row => {
                html += '<tr>';
                Object.values(row).forEach(val => {
                    const displayVal = val !== null ? 
                        (String(val).length > 100 ? String(val).substring(0, 100) + '...' : val) : 
                        '<em class="text-muted">NULL</em>';
                    html += `<td>${displayVal}</td>`;
                });
                html += '</tr>';
            });
            html += '</tbody></table>';
            html += `<small class="text-muted">Showing ${response.data.length} rows (limit 100)</small>`;
            
            $('#tableDataContent').html(html);
        } else {
            $('#tableDataContent').html('<div class="alert alert-warning">No data found</div>');
        }
    });
}

// Get Table Structure
function getTableStructure(tableName) {
    $('#modalTableName').text(tableName + ' - Structure');
    $('#tableDataModal').modal('show');
    
    $.post(APP_URL + '/admin/actions/database-operations.php', {
        action: 'table_structure',
        table: tableName
    }).done(function(response) {
        if (response.success && response.data) {
            let html = '<table class="table table-sm table-bordered"><thead class="table-light"><tr>';
            html += '<th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th>';
            html += '</tr></thead><tbody>';
            
            response.data.forEach(col => {
                html += `<tr>
                    <td class="fw-semibold">${col.Field}</td>
                    <td><code>${col.Type}</code></td>
                    <td>${col.Null}</td>
                    <td>${col.Key ? '<span class="badge bg-primary">' + col.Key + '</span>' : '-'}</td>
                    <td>${col.Default !== null ? col.Default : '<em class="text-muted">NULL</em>'}</td>
                    <td>${col.Extra || '-'}</td>
                </tr>`;
            });
            html += '</tbody></table>';
            
            $('#tableDataContent').html(html);
        }
    });
}

// Optimize Table
function optimizeTable(tableName) {
    Swal.fire({
        title: `Optimize ${tableName}?`,
        text: 'This will optimize the table structure and indexes.',
        icon: 'question',
        showCancelButton: true,
        confirmButtonText: 'Yes, Optimize'
    }).then((result) => {
        if (result.isConfirmed) {
            showLoading();
            $.post(APP_URL + '/admin/actions/database-operations.php', {
                action: 'optimize_table',
                table: tableName
            }).done(function(response) {
                hideLoading();
                if (response.success) {
                    showToast(`Table ${tableName} optimized successfully`, 'success');
                    setTimeout(() => location.reload(), 1500);
                } else {
                    showToast('Optimization failed: ' + response.message, 'error');
                }
            });
        }
    });
}

// Truncate Table
function truncateTable(tableName) {
    Swal.fire({
        title: `Truncate ${tableName}?`,
        html: '<strong class="text-danger">WARNING: This will delete ALL data from this table!</strong><br>This action cannot be undone.',
        icon: 'error',
        showCancelButton: true,
        confirmButtonColor: '#dc2626',
        confirmButtonText: 'Yes, Delete All Data',
        input: 'text',
        inputPlaceholder: `Type "${tableName}" to confirm`,
        inputValidator: (value) => {
            if (value !== tableName) {
                return 'Table name does not match!';
            }
        }
    }).then((result) => {
        if (result.isConfirmed) {
            showLoading();
            $.post(APP_URL + '/admin/actions/database-operations.php', {
                action: 'truncate_table',
                table: tableName
            }).done(function(response) {
                hideLoading();
                if (response.success) {
                    showToast(`Table ${tableName} truncated successfully`, 'success');
                    setTimeout(() => location.reload(), 1500);
                } else {
                    showToast('Truncate failed: ' + response.message, 'error');
                }
            });
        }
    });
}

// Filter Tables
function filterTables(search) {
    const filter = search.toUpperCase();
    const rows = document.querySelectorAll('#tablesTable tbody tr');
    
    rows.forEach(row => {
        const tableName = row.cells[0].textContent;
        row.style.display = tableName.toUpperCase().includes(filter) ? '' : 'none';
    });
}

// Load Query Template
function loadQueryTemplate(type) {
    let template = '';
    
    switch(type) {
        case 'select':
            template = 'SELECT * FROM table_name LIMIT 10';
            break;
    }
    
    $('#sqlQuery').val(template);
}

// Danger Zone
function showDangerZone() {
    Swal.fire({
        title: 'Danger Zone',
        html: `
            <div class="text-start">
                <p class="text-danger fw-bold">⚠️ Advanced Operations - Use with Extreme Caution</p>
                <button class="btn btn-sm btn-outline-danger w-100 mb-2" onclick="repairAllTables()">
                    <i class="bi bi-tools"></i> Repair All Tables
                </button>
                <button class="btn btn-sm btn-outline-warning w-100 mb-2" onclick="analyzeAllTables()">
                    <i class="bi bi-graph-up"></i> Analyze All Tables
                </button>
                <button class="btn btn-sm btn-outline-primary w-100" onclick="checkAllTables()">
                    <i class="bi bi-check-circle"></i> Check All Tables
                </button>
            </div>
        `,
        showConfirmButton: false,
        showCancelButton: true,
        cancelButtonText: 'Close'
    });
}

function repairAllTables() {
    Swal.fire({
        title: 'Repair All Tables?',
        text: 'This will attempt to repair all database tables.',
        icon: 'warning',
        showCancelButton: true,
        confirmButtonText: 'Yes, Repair All'
    }).then((result) => {
        if (result.isConfirmed) {
            showLoading();
            $.post(APP_URL + '/admin/actions/database-operations.php', {
                action: 'repair_all'
            }).done(function(response) {
                hideLoading();
                showToast(response.message, response.success ? 'success' : 'error');
            });
        }
    });
}

function analyzeAllTables() {
    showLoading();
    $.post(APP_URL + '/admin/actions/database-operations.php', {
        action: 'analyze_all'
    }).done(function(response) {
        hideLoading();
        showToast(response.message, response.success ? 'success' : 'error');
    });
}

function checkAllTables() {
    showLoading();
    $.post(APP_URL + '/admin/actions/database-operations.php', {
        action: 'check_all'
    }).done(function(response) {
        hideLoading();
        showToast(response.message, response.success ? 'success' : 'error');
    });
}
</script>

<?php renderAdminFooter(); ?>