<?php
/**
 * Database Management - System Admin only
 */

require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/audit.php';
require_once __DIR__ . '/../../includes/db-table-stats.php';
require_once __DIR__ . '/../../includes/db-clean-data.php';
require_once __DIR__ . '/../../includes/csrf.php';

requireAuth();
requireSystemAdmin();

$pageTitle = 'Database Management';
$currentPage = 'system';

// Get table information (BASE TABLEs only; same discovery as system dashboard)
$tables = [];
try {
    $tables = nazarethListBaseTablesWithStats($pdo);
} catch (Throwable $e) {
    $tables = [];
}

// Handle POST before any output (CSRF + redirects)
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $postAction = $_POST['action'] ?? '';
    if (!nazareth_csrf_validate()) {
        header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Invalid security token. Refresh the page and try again.'));
        exit;
    }

    if ($postAction === 'optimize') {
        try {
            foreach ($tables as $table) {
                $pdo->exec('OPTIMIZE TABLE `' . str_replace('`', '', $table['name']) . '`');
            }
            header('Location: ' . BASE_PATH . '/system/database?toast=' . urlencode('Tables optimized successfully.'));
        } catch (PDOException $e) {
            header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Error optimizing tables: ' . $e->getMessage()));
        }
        exit;
    }

    if ($postAction === 'analyze') {
        try {
            foreach ($tables as $table) {
                $pdo->exec('ANALYZE TABLE `' . str_replace('`', '', $table['name']) . '`');
            }
            header('Location: ' . BASE_PATH . '/system/database?toast=' . urlencode('Tables analyzed successfully.'));
        } catch (PDOException $e) {
            header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Error analyzing tables: ' . $e->getMessage()));
        }
        exit;
    }

    if ($postAction === 'clean_data') {
        $phrase = trim((string) ($_POST['clean_data_phrase'] ?? ''));
        $pwd = (string) ($_POST['confirm_password'] ?? '');
        if ($phrase !== 'DELETE ALL DATA') {
            header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Type DELETE ALL DATA exactly to confirm.'));
            exit;
        }
        if (!nazareth_verify_current_user_password($pdo, $pwd)) {
            header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Incorrect password. No data was changed.'));
            exit;
        }
        try {
            $result = nazareth_clean_database_data($pdo, nazareth_db_clean_preserve_tables());
            if (!empty($result['skipped'])) {
                throw new PDOException(implode('; ', $result['skipped']));
            }
            logDataCleanup($_SESSION['user_id'], $result['truncated']);
            $msg = 'Data cleaned from ' . count($result['truncated']) . ' table(s). Users and settings kept. Structures preserved.';
            header('Location: ' . BASE_PATH . '/system/database?toast=' . urlencode($msg));
            exit;
        } catch (PDOException $e) {
            header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Error cleaning data: ' . $e->getMessage()));
            exit;
        }
    }

    header('Location: ' . BASE_PATH . '/system/database?toast_error=' . urlencode('Unknown action.'));
    exit;
}

require_once __DIR__ . '/../../includes/system-header.php';
?>

<div class="header">
    <h1>Database Management</h1>
    <div class="header-actions">
        <a href="<?= BASE_PATH ?>/system" class="link-muted">← Dashboard</a>
        <a href="<?= BASE_PATH ?>/logout">Logout</a>
    </div>
</div>

<!-- Summary Stats -->
<?php
$totalRows = array_sum(array_column($tables, 'rows'));
$totalSize = array_sum(array_column($tables, 'size'));
?>
<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 1rem; margin-bottom: 1.5rem;">
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Total Tables</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: #1e293b;"><?= count($tables) ?></div>
    </div>
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Total Records</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: #1e293b;"><?= number_format($totalRows) ?></div>
    </div>
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Total Size</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: #1e293b;"><?= number_format($totalSize / 1024 / 1024, 2) ?> MB</div>
    </div>
</div>

<div class="card">
    <h2 style="margin-top: 0; margin-bottom: 1rem;">Database Tables</h2>
    <div class="table-wrap">
        <table>
            <thead>
                <tr>
                    <th>Table Name</th>
                    <th>Engine</th>
                    <th style="text-align: right;">Rows</th>
                    <th style="text-align: right;">Size</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($tables as $table): ?>
                <tr>
                    <td>
                        <div style="display: flex; align-items: center; gap: 0.5rem;">
                            <svg width="16" height="16" fill="none" stroke="currentColor" viewBox="0 0 24 24" style="color: #94a3b8;">
                                <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 7v10c0 2.21 3.582 4 8 4s8-1.79 8-4V7M4 7c0 2.21 3.582 4 8 4s8-1.79 8-4M4 7c0-2.21 3.582-4 8-4s8 1.79 8 4m0 5c0 2.21-3.582 4-8 4s-8-1.79-8-4"/>
                            </svg>
                            <span style="font-weight: 500;"><?= htmlspecialchars($table['name']) ?></span>
                        </div>
                    </td>
                    <td>
                        <span style="padding: 0.25rem 0.5rem; background: #f1f5f9; border-radius: 4px; font-size: 0.8125rem;">
                            <?= htmlspecialchars($table['engine']) ?>
                        </span>
                    </td>
                    <td style="text-align: right; font-weight: 500;"><?= number_format($table['rows']) ?></td>
                    <td style="text-align: right; color: #64748b;"><?= number_format($table['size'] / 1024, 2) ?> KB</td>
                </tr>
                <?php endforeach; ?>
            </tbody>
            <tfoot>
                <tr style="background: #f8fafc; font-weight: 600;">
                    <td colspan="2">Total</td>
                    <td style="text-align: right;"><?= number_format($totalRows) ?></td>
                    <td style="text-align: right;"><?= number_format($totalSize / 1024, 2) ?> KB</td>
                </tr>
            </tfoot>
        </table>
    </div>
</div>

<div class="card" style="border: 1px solid #fecaca; background: #fef2f2;">
    <h2 style="margin-top: 0; margin-bottom: 1rem; color: #991b1b;">⚠️ Clean Data (Dangerous)</h2>
    <p class="text-muted" style="margin-bottom: 1rem; color: #991b1b;"><strong>Warning:</strong> Truncates <strong>all</strong> tables except <code>users</code> and <code>settings</code>. Structures are kept. This cannot be undone. Requires your password and exact confirmation phrase.</p>
    <form method="post" action="">
        <?= nazareth_csrf_field() ?>
        <input type="hidden" name="action" value="clean_data">
        <div class="form-group" style="margin-bottom: 0.75rem;">
            <label for="clean_data_phrase" style="display: block; font-weight: 500; margin-bottom: 0.25rem;">Type <code>DELETE ALL DATA</code> to confirm</label>
            <input type="text" id="clean_data_phrase" name="clean_data_phrase" required autocomplete="off" style="max-width: 20rem; width: 100%; padding: 0.5rem;">
        </div>
        <div class="form-group" style="margin-bottom: 0.75rem;">
            <label for="confirm_password_clean" style="display: block; font-weight: 500; margin-bottom: 0.25rem;">Your account password</label>
            <input type="password" id="confirm_password_clean" name="confirm_password" required autocomplete="current-password" style="max-width: 20rem; width: 100%; padding: 0.5rem;">
        </div>
        <button type="submit" class="btn" style="background: #ef4444; color: white;">Clean All Data</button>
    </form>
</div>

<div class="card">
    <h2 style="margin-top: 0; margin-bottom: 1rem;">Database Operations</h2>
    <p class="text-muted" style="margin-bottom: 1rem;">Database maintenance and optimization tools.</p>
    <div style="display: flex; flex-direction: column; gap: 0.75rem;">
        <form method="post" action="" onsubmit="return confirm('Optimize all tables? This may take a moment.');">
            <?= nazareth_csrf_field() ?>
            <input type="hidden" name="action" value="optimize">
            <button type="submit" class="btn btn-secondary">Optimize All Tables</button>
        </form>
        <form method="post" action="" onsubmit="return confirm('Analyze all tables? This may take a moment.');">
            <?= nazareth_csrf_field() ?>
            <input type="hidden" name="action" value="analyze">
            <button type="submit" class="btn btn-secondary">Analyze All Tables</button>
        </form>
    </div>
</div>

<?php require_once __DIR__ . '/../../includes/system-footer.php'; ?>
