{% extends "admin/base.html" %} {% block title %}Database{% endblock %} {% block
content %}
<style>
    .db-stats {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
        gap: 1rem;
        margin-bottom: 1.5rem;
    }
    .db-stat {
        background: var(--bg-secondary);
        border-radius: var(--radius);
        padding: 1rem;
        text-align: center;
    }
    .db-stat-value {
        font-size: 1.5rem;
        font-weight: 600;
        color: var(--color-primary);
        font-family: var(--font-mono);
    }
    .db-stat-label {
        font-size: 0.75rem;
        color: var(--text-muted);
        text-transform: uppercase;
        letter-spacing: 0.5px;
        margin-top: 0.25rem;
    }
    .db-section {
        margin-bottom: 1.5rem;
    }
    .db-section h3 {
        margin-bottom: 1rem;
        display: flex;
        align-items: center;
        gap: 0.5rem;
    }
    .db-section h3 svg {
        color: var(--text-muted);
    }
    .db-table {
        width: 100%;
        border-collapse: collapse;
        font-size: 0.875rem;
    }
    .db-table th,
    .db-table td {
        padding: 0.625rem 0.75rem;
        text-align: left;
        border-bottom: 1px solid var(--border);
    }
    .db-table th {
        font-weight: 500;
        color: var(--text-muted);
        background: var(--bg-secondary);
        font-size: 0.75rem;
        text-transform: uppercase;
        letter-spacing: 0.5px;
    }
    .db-table tr:hover td {
        background: var(--bg-secondary);
    }
    .db-table .mono {
        font-family: var(--font-mono);
        font-size: 0.8rem;
    }
    .db-table .num {
        text-align: right;
        font-family: var(--font-mono);
    }
    .health-badge {
        display: inline-flex;
        align-items: center;
        gap: 0.375rem;
        padding: 0.25rem 0.75rem;
        border-radius: 999px;
        font-size: 0.75rem;
        font-weight: 500;
    }
    .health-badge.good {
        background: rgba(16, 185, 129, 0.1);
        color: #10b981;
    }
    .health-badge.warning {
        background: rgba(245, 158, 11, 0.1);
        color: #f59e0b;
    }
    .health-badge.error {
        background: rgba(239, 68, 68, 0.1);
        color: #ef4444;
    }
    .health-badge .dot {
        width: 6px;
        height: 6px;
        border-radius: 50%;
        background: currentColor;
    }
    .recommendations {
        list-style: none;
        padding: 0;
        margin: 0;
    }
    .recommendations li {
        display: flex;
        align-items: flex-start;
        gap: 0.75rem;
        padding: 0.75rem;
        background: var(--bg-secondary);
        border-radius: var(--radius);
        margin-bottom: 0.5rem;
        font-size: 0.875rem;
    }
    .recommendations li:last-child {
        margin-bottom: 0;
    }
    .recommendations .icon {
        flex-shrink: 0;
        margin-top: 0.125rem;
    }
    .recommendations .icon.info {
        color: var(--color-primary);
    }
    .recommendations .icon.warning {
        color: #f59e0b;
    }
    .compile-options {
        display: flex;
        flex-wrap: wrap;
        gap: 0.5rem;
    }
    .compile-option {
        font-family: var(--font-mono);
        font-size: 0.7rem;
        padding: 0.25rem 0.5rem;
        background: var(--bg-tertiary);
        border-radius: var(--radius-sm);
        color: var(--text-muted);
    }
    .action-buttons {
        display: flex;
        gap: 0.75rem;
        flex-wrap: wrap;
    }
    .action-btn {
        display: inline-flex;
        align-items: center;
        gap: 0.5rem;
        padding: 0.5rem 1rem;
        font-size: 0.875rem;
        background: var(--bg-secondary);
        border: 1px solid var(--border);
        border-radius: var(--radius);
        cursor: pointer;
        color: var(--text);
        transition: all 0.15s ease;
    }
    .action-btn:hover {
        background: var(--bg-tertiary);
        border-color: var(--color-primary);
    }
    .action-btn.primary {
        background: var(--color-primary);
        color: var(--color-primary-text, #fff);
        border-color: var(--color-primary);
    }
    .action-btn.primary:hover {
        opacity: 0.9;
    }
    /* Ensure Vacuum button visibility across all themes */
    .action-btn.vacuum-btn {
        background: #7c3aed;
        color: #fff;
        border-color: #7c3aed;
    }
    .action-btn.vacuum-btn:hover {
        background: #6d28d9;
        border-color: #6d28d9;
    }
    .action-btn svg {
        width: 16px;
        height: 16px;
    }
    .info-grid {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(280px, 1fr));
        gap: 1.5rem;
    }
    .info-item {
        display: flex;
        justify-content: space-between;
        padding: 0.5rem 0;
        border-bottom: 1px solid var(--border);
        font-size: 0.875rem;
    }
    .info-item:last-child {
        border-bottom: none;
    }
    .info-label {
        color: var(--text-muted);
    }
    .info-value {
        font-family: var(--font-mono);
        font-weight: 500;
    }
    .progress-bar {
        height: 8px;
        background: var(--bg-tertiary);
        border-radius: 4px;
        overflow: hidden;
        margin-top: 0.5rem;
    }
    .progress-bar-fill {
        height: 100%;
        border-radius: 4px;
        transition: width 0.3s ease;
    }
    .progress-bar-fill.good {
        background: #10b981;
    }
    .progress-bar-fill.warning {
        background: #f59e0b;
    }
    .progress-bar-fill.error {
        background: #ef4444;
    }
</style>

<div class="header">
    <h2>Database</h2>
    <div class="action-buttons">
        <form method="post" action="/admin/database" style="display: inline">
            <input type="hidden" name="action" value="analyze" />
            <button
                type="submit"
                class="action-btn"
                title="Update query planner statistics"
            >
                <svg
                    viewBox="0 0 24 24"
                    fill="none"
                    stroke="currentColor"
                    stroke-width="2"
                >
                    <path
                        d="M21 12a9 9 0 01-9 9m9-9a9 9 0 00-9-9m9 9H3m9 9a9 9 0 01-9-9m9 9c1.66 0 3-4.03 3-9s-1.34-9-3-9m0 18c-1.66 0-3-4.03-3-9s1.34-9 3-9m-9 9a9 9 0 019-9"
                    />
                </svg>
                Analyze
            </button>
        </form>
        <form
            method="post"
            action="/admin/database"
            style="display: inline"
            onsubmit="
                return confirm(
                    'VACUUM will rebuild the database file. This may take a moment for large databases. Continue?',
                );
            "
        >
            <input type="hidden" name="action" value="vacuum" />
            <button
                type="submit"
                class="action-btn vacuum-btn"
                title="Rebuild database to reclaim space"
            >
                <svg
                    viewBox="0 0 24 24"
                    fill="none"
                    stroke="currentColor"
                    stroke-width="2"
                >
                    <path
                        d="M4 4v5h.582m15.356 2A8.001 8.001 0 004.582 9m0 0H9m11 11v-5h-.581m0 0a8.003 8.003 0 01-15.357-2m15.357 2H15"
                    />
                </svg>
                Vacuum
            </button>
        </form>
    </div>
</div>

<!-- Health Overview -->
<div class="db-stats">
    <div class="db-stat">
        <div class="db-stat-value">{{ stats.file_size_human }}</div>
        <div class="db-stat-label">Database Size</div>
    </div>
    <div class="db-stat">
        <div class="db-stat-value">{{ stats.tables | length }}</div>
        <div class="db-stat-label">Tables</div>
    </div>
    <div class="db-stat">
        <div class="db-stat-value">{{ stats.indexes | length }}</div>
        <div class="db-stat-label">Indexes</div>
    </div>
    <div class="db-stat">
        <div class="db-stat-value">{{ stats.page_count }}</div>
        <div class="db-stat-label">Pages</div>
    </div>
    <div class="db-stat">
        <div class="db-stat-value">
            {% if stats.integrity_check == "ok" %}
            <span class="health-badge good"
                ><span class="dot"></span> Healthy</span
            >
            {% else %}
            <span class="health-badge error"
                ><span class="dot"></span> Issues</span
            >
            {% endif %}
        </div>
        <div class="db-stat-label">Integrity</div>
    </div>
    <div class="db-stat">
        <div class="db-stat-value">
            {{ analysis.fragmentation_percent | round(precision=1) }}%
        </div>
        <div class="db-stat-label">Fragmentation</div>
        <div class="progress-bar">
            <div
                class="progress-bar-fill {% if analysis.fragmentation_percent < 5 %}good{% elif analysis.fragmentation_percent < 15 %}warning{% else %}error{% endif %}"
                style="width: {% if analysis.fragmentation_percent > 100 %}100{% else %}{{ analysis.fragmentation_percent }}{% endif %}%"
            ></div>
        </div>
    </div>
</div>

<div class="info-grid">
    <!-- Database Info -->
    <div class="card db-section">
        <h3>
            <svg
                width="18"
                height="18"
                viewBox="0 0 24 24"
                fill="none"
                stroke="currentColor"
                stroke-width="2"
            >
                <ellipse cx="12" cy="5" rx="9" ry="3" />
                <path d="M21 12c0 1.66-4.03 3-9 3s-9-1.34-9-3" />
                <path d="M3 5v14c0 1.66 4.03 3 9 3s9-1.34 9-3V5" />
            </svg>
            Database Info
        </h3>
        <div class="info-item">
            <span class="info-label">SQLite Version</span>
            <span class="info-value">{{ stats.sqlite_version }}</span>
        </div>
        <div class="info-item">
            <span class="info-label">Journal Mode</span>
            <span class="info-value">{{ stats.journal_mode | upper }}</span>
        </div>
        <div class="info-item">
            <span class="info-label">Encoding</span>
            <span class="info-value">{{ stats.encoding }}</span>
        </div>
        <div class="info-item">
            <span class="info-label">Page Size</span>
            <span class="info-value">{{ stats.page_size }} bytes</span>
        </div>
        <div class="info-item">
            <span class="info-label">Cache Size</span>
            <span class="info-value">{{ stats.cache_size }} pages</span>
        </div>
        <div class="info-item">
            <span class="info-label">Auto Vacuum</span>
            <span class="info-value">{{ stats.auto_vacuum | upper }}</span>
        </div>
        <div class="info-item">
            <span class="info-label">Free Pages</span>
            <span class="info-value">{{ stats.freelist_count }}</span>
        </div>
        {% if stats.wal_checkpoint %}
        <div class="info-item">
            <span class="info-label">WAL Pages</span>
            <span class="info-value">{{ stats.wal_checkpoint.wal_pages }}</span>
        </div>
        {% endif %}
    </div>

    <!-- Recommendations -->
    <div class="card db-section">
        <h3>
            <svg
                width="18"
                height="18"
                viewBox="0 0 24 24"
                fill="none"
                stroke="currentColor"
                stroke-width="2"
            >
                <circle cx="12" cy="12" r="10" />
                <line x1="12" y1="16" x2="12" y2="12" />
                <line x1="12" y1="8" x2="12.01" y2="8" />
            </svg>
            Recommendations
        </h3>
        <ul class="recommendations">
            {% for rec in analysis.recommendations %}
            <li>
                {% if rec is containing("healthy") or rec is containing("No
                recommendations") %}
                <span class="icon info">
                    <svg
                        width="16"
                        height="16"
                        viewBox="0 0 24 24"
                        fill="none"
                        stroke="currentColor"
                        stroke-width="2"
                    >
                        <path d="M22 11.08V12a10 10 0 11-5.93-9.14" />
                        <polyline points="22 4 12 14.01 9 11.01" />
                    </svg>
                </span>
                {% else %}
                <span class="icon warning">
                    <svg
                        width="16"
                        height="16"
                        viewBox="0 0 24 24"
                        fill="none"
                        stroke="currentColor"
                        stroke-width="2"
                    >
                        <path
                            d="M10.29 3.86L1.82 18a2 2 0 001.71 3h16.94a2 2 0 001.71-3L13.71 3.86a2 2 0 00-3.42 0z"
                        />
                        <line x1="12" y1="9" x2="12" y2="13" />
                        <line x1="12" y1="17" x2="12.01" y2="17" />
                    </svg>
                </span>
                {% endif %}
                <span>{{ rec }}</span>
            </li>
            {% endfor %}
        </ul>
    </div>
</div>

<!-- Tables -->
<div class="card db-section">
    <h3>
        <svg
            width="18"
            height="18"
            viewBox="0 0 24 24"
            fill="none"
            stroke="currentColor"
            stroke-width="2"
        >
            <rect x="3" y="3" width="18" height="18" rx="2" ry="2" />
            <line x1="3" y1="9" x2="21" y2="9" />
            <line x1="3" y1="15" x2="21" y2="15" />
            <line x1="9" y1="3" x2="9" y2="21" />
        </svg>
        Tables ({{ stats.tables | length }})
    </h3>
    <table class="db-table">
        <thead>
            <tr>
                <th>Table Name</th>
                <th class="num">Row Count</th>
            </tr>
        </thead>
        <tbody>
            {% for table in stats.tables %}
            <tr>
                <td class="mono">{{ table.name }}</td>
                <td class="num">{{ table.row_count }}</td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
</div>

<!-- Indexes -->
<div class="card db-section">
    <h3>
        <svg
            width="18"
            height="18"
            viewBox="0 0 24 24"
            fill="none"
            stroke="currentColor"
            stroke-width="2"
        >
            <line x1="4" y1="9" x2="20" y2="9" />
            <line x1="4" y1="15" x2="20" y2="15" />
            <line x1="10" y1="3" x2="8" y2="21" />
            <line x1="16" y1="3" x2="14" y2="21" />
        </svg>
        Indexes ({{ stats.indexes | length }})
    </h3>
    <table class="db-table">
        <thead>
            <tr>
                <th>Index Name</th>
                <th>Table</th>
                <th>Columns</th>
                <th>Unique</th>
            </tr>
        </thead>
        <tbody>
            {% for index in stats.indexes %}
            <tr>
                <td class="mono">{{ index.name }}</td>
                <td class="mono">{{ index.table_name }}</td>
                <td class="mono">{{ index.columns }}</td>
                <td>
                    {% if index.is_unique %}<span class="health-badge good"
                        >Yes</span
                    >{% else %}No{% endif %}
                </td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
</div>

<!-- Compile Options -->
<div class="card db-section">
    <h3>
        <svg
            width="18"
            height="18"
            viewBox="0 0 24 24"
            fill="none"
            stroke="currentColor"
            stroke-width="2"
        >
            <polyline points="4 17 10 11 4 5" />
            <line x1="12" y1="19" x2="20" y2="19" />
        </svg>
        SQLite Compile Options
    </h3>
    <div class="compile-options">
        {% for option in stats.compile_options %}
        <span class="compile-option">{{ option }}</span>
        {% endfor %}
    </div>
</div>

{% endblock %}