database.slim

doctype html
html
  head
    title Database Viewer - #{@source}
    style
      | * { margin: 0; padding: 0; box-sizing: border-box; }
      | body { font-family: 'JetBrains Mono', monospace; background: #2b2b2b; color: #a9b7c6; line-height: 1.4; font-size: 14px; }
      | .container { max-width: 1400px; margin: 0 auto; padding: 20px; }
      | .header-blocks { display: flex; gap: 20px; margin-bottom: 20px; }
      | .header-block { background: #3c3f41; border: 1px solid #555; padding: 20px; border-radius: 4px; flex: 1; }
      | .header-block h1 { color: #ffc66d; font-size: 20px; margin-bottom: 8px; }
      | .header-block p { color: #808080; font-size: 13px; }
      | .section { background: #3c3f41; border: 1px solid #555; padding: 16px; border-radius: 4px; margin-bottom: 16px; }
      | .section-title { color: #6a9955; font-weight: bold; margin-bottom: 12px; font-size: 16px; }
      | .btn { display: inline-block; padding: 6px 12px; background: #4b4d4f; color: #a9b7c6; text-decoration: none; border-radius: 3px; font-size: 12px; border: 1px solid #555; margin-right: 8px; }
      | .btn:hover { background: #5a5d5f; border-color: #666; }
      | .btn-primary { background: #6a9955; color: #fff; border-color: #7bb366; }
      | .btn-primary:hover { background: #7bb366; border-color: #8cc477; }
      | .nav { display: flex; gap: 8px; flex-wrap: wrap; }
      | .stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(140px, 1fr)); gap: 12px; margin-bottom: 20px; }
      | .stat-box { background: linear-gradient(135deg, #313335 0%, #2b2b2b 100%); border: 1px solid #464749; padding: 12px; border-radius: 6px; text-align: center; transition: all 0.3s ease; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
      | .stat-box:hover { transform: translateY(-2px); box-shadow: 0 4px 8px rgba(0,0,0,0.2); border-color: #6a9955; }
      | .stat-number { font-size: 24px; font-weight: bold; color: #6897bb; margin-bottom: 4px; text-shadow: 0 1px 2px rgba(0,0,0,0.3); }
      | .stat-desc { font-size: 11px; color: #808080; text-transform: uppercase; letter-spacing: 0.5px; font-weight: 500; }
      | .table-container { overflow-x: auto; border: 1px solid #555; border-radius: 4px; background: #2b2b2b; }
      | .table-container table { width: 100%; border-collapse: collapse; font-size: 12px; table-layout: fixed; }
      | .table-container th, .table-container td { word-wrap: break-word; overflow: hidden; text-overflow: ellipsis; }
      | .table-container th { background: #3c3f41; color: #ffc66d; font-weight: bold; padding: 8px 12px; text-align: left; border-bottom: 1px solid #555; font-size: 11px; text-transform: uppercase; letter-spacing: 0.5px; }
      | .table-container td { padding: 6px 12px; border-bottom: 1px solid #464749; color: #a9b7c6; font-family: 'Courier New', monospace; font-size: 11px; }
      | .table-container tr:hover { background: #313335; }
      | .table-container tr:nth-child(even) { background: #2b2b2b; }
      | .table-container tr:nth-child(even):hover { background: #313335; }
      | .main-layout { display: flex; gap: 20px; margin-top: 20px; }
      | .sidebar { width: 280px; flex-shrink: 0; }
      | .content { flex: 1; }
      | .table-list { background: #3c3f41; border: 1px solid #555; border-radius: 4px; overflow: hidden; }
      | .table-list-header { background: #4b4d4f; padding: 12px 16px; border-bottom: 1px solid #555; color: #ffc66d; font-weight: bold; font-size: 14px; }
      | .table-item { display: block; text-decoration: none; padding: 10px 16px; border-bottom: 1px solid #464749; cursor: pointer; transition: background 0.2s ease; color: #a9b7c6; font-size: 13px; }
      | .table-item:hover { background: #313335; }
      | .table-item.active { background: #6a9955; color: #fff; }
      | .table-item .table-name { font-weight: bold; margin-bottom: 4px; display: block; }
      | .table-item .table-count { font-size: 11px; color: #808080; display: block; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
      | .table-item.active .table-count { color: #e0e0e0; }
      | .pagination { display: flex; justify-content: space-between; align-items: center; margin-top: 16px; padding: 12px 16px; background: #3c3f41; border: 1px solid #555; border-radius: 4px; }
      | .pagination-info { color: #808080; font-size: 12px; }
      | .pagination-controls { display: flex; gap: 8px; }
      | .pagination-btn { padding: 4px 8px; background: #4b4d4f; color: #a9b7c6; border: 1px solid #555; border-radius: 3px; font-size: 11px; cursor: pointer; text-decoration: none; }
      | .pagination-btn:hover { background: #5a5d5f; border-color: #666; }
      | .table-header { margin-bottom: 12px; }
      | .table-title { color: #6a9955; font-weight: bold; font-size: 16px; }
      | .btn-home { display: inline-flex; align-items: center; justify-content: center; width: 80px; height: 36px; border-radius: 18px; background: #6a9955; color: #fff; text-decoration: none; border: 1px solid #7bb366; font-size: 14px; transition: all 0.3s ease; gap: 4px; }
      | .btn-home:hover { background: #7bb366; border-color: #8cc477; transform: scale(1.05); }
      | .btn-vacuum { display: inline-flex; align-items: center; justify-content: center; padding: 8px 16px; border-radius: 4px; background: #ffc66d; color: #2b2b2b; text-decoration: none; border: 1px solid #e6b85a; font-size: 12px; font-weight: bold; transition: all 0.3s ease; gap: 6px; cursor: pointer; }
      | .btn-vacuum:hover { background: #ffd87d; border-color: #f0c050; transform: translateY(-1px); }
      | .btn-vacuum:disabled { opacity: 0.5; cursor: not-allowed; }
      | .vacuum-title { color: #ffc66d; font-weight: bold; margin-bottom: 8px; font-size: 14px; }
      | .vacuum-desc { color: #808080; font-size: 11px; margin-bottom: 12px; line-height: 1.4; }
      | .btn-validate { display: inline-flex; align-items: center; justify-content: center; padding: 8px 16px; border-radius: 4px; background: #c75450; color: #fff; text-decoration: none; border: 1px solid #d76460; font-size: 12px; font-weight: bold; transition: all 0.3s ease; gap: 6px; cursor: pointer; }
      | .btn-validate:hover { background: #d76460; border-color: #e77470; transform: translateY(-1px); }
      | .btn-validate:disabled { opacity: 0.5; cursor: not-allowed; }
      | .btn-stop-validate { display: inline-flex; align-items: center; justify-content: center; padding: 8px 16px; border-radius: 4px; background: #808080; color: #fff; text-decoration: none; border: 1px solid #999; font-size: 12px; font-weight: bold; transition: all 0.3s ease; gap: 6px; cursor: pointer; margin-left: 8px; }
      | .btn-stop-validate:hover { background: #999; border-color: #aaa; transform: translateY(-1px); }
      | .btn-stop-validate:disabled { opacity: 0.5; cursor: not-allowed; }
      | .validate-title { color: #c75450; font-weight: bold; margin-bottom: 8px; font-size: 14px; }
      | .validate-desc { color: #808080; font-size: 11px; margin-bottom: 12px; line-height: 1.4; }
      | .validate-stats { background: #313335; border: 1px solid #464749; border-radius: 4px; padding: 12px; margin-top: 12px; }
      | .validate-stats-title { color: #ffc66d; font-weight: bold; margin-bottom: 8px; font-size: 12px; }
      | .validate-stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(100px, 1fr)); gap: 8px; }
      | .validate-stat-item { display: flex; flex-direction: column; gap: 4px; }
      | .validate-stat-label { color: #808080; font-size: 10px; text-transform: uppercase; letter-spacing: 0.5px; }
      | .validate-stat-value { color: #6897bb; font-size: 14px; font-weight: bold; font-family: 'Courier New', monospace; }
      | .validate-progress { margin-top: 8px; }
      | .validate-progress-bar { width: 100%; height: 8px; background: #2b2b2b; border-radius: 4px; overflow: hidden; border: 1px solid #464749; }
      | .validate-progress-fill { height: 100%; background: linear-gradient(90deg, #6a9955 0%, #7bb366 100%); transition: width 0.3s ease; }
      | .modal-overlay { display: none; position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.7); z-index: 1000; align-items: center; justify-content: center; }
      | .modal-overlay.show { display: flex; }
      | .modal-content { background: #3c3f41; border: 1px solid #555; border-radius: 6px; padding: 24px; max-width: 400px; box-shadow: 0 4px 16px rgba(0,0,0,0.4); }
      | .modal-title { color: #ffc66d; font-size: 16px; font-weight: bold; margin-bottom: 12px; }
      | .modal-message { color: #a9b7c6; font-size: 13px; margin-bottom: 20px; }
      | .modal-buttons { display: flex; gap: 8px; justify-content: flex-end; }
      | .json-box { background: #2b2b2b; border: 1px solid #464749; border-radius: 4px; padding: 12px; overflow-x: auto; max-height: 400px; overflow-y: auto; }
      | .json-box pre { margin: 0; font-family: 'JetBrains Mono', 'Courier New', monospace; font-size: 12px; color: #a9b7c6; white-space: pre; line-height: 1.5; }
      | .json-missing { color: #808080; font-size: 12px; font-style: italic; }
      | .json-path { color: #808080; font-size: 11px; margin-bottom: 8px; font-family: 'Courier New', monospace; }

  body
    .modal-overlay id="modal-overlay"
      .modal-content
        .modal-title id="modal-title"
        .modal-message id="modal-message"
        .modal-buttons
          a.btn onclick="closeModal()" Cancel
          a.btn.btn-primary onclick="confirmModal()" id="modal-confirm" OK
    .container
      .header-blocks
        .header-block
          h1 Database Viewer: #{@source}
          p Target: #{@route[:target]}
        .header-block
          .vacuum-title 🗄️ Database Maintenance
          .vacuum-desc VACUUM optimizes database files by reclaiming unused space and defragmenting data. This operation runs in background for all databases and may take several minutes for large databases.
          button.btn-vacuum onclick="runVacuum()" 🧹 Start VACUUM
        - if @validation_enabled
          .header-block
            .validate-title 🔍 Tile Validation & Cleanup
            .validate-desc Validates cached tiles and removes invalid (corrupted or transparent) tiles from the database. This process may take a long time for large databases.
            button.btn-validate id="btn-validate" onclick="startValidation()" 🧹 Start Validation
            button.btn-stop-validate id="btn-stop-validate" onclick="stopValidation()" style="display: none;" ⏹ Stop
            #validate-stats.validate-stats style="display: none;"
              .validate-stats-title Validation Progress
              .validate-stats-grid
                .validate-stat-item
                  .validate-stat-label Processed
                  .validate-stat-value id="validate-processed" 0
                .validate-stat-item
                  .validate-stat-label Invalid
                  .validate-stat-value id="validate-invalid" 0
                .validate-stat-item
                  .validate-stat-label Valid
                  .validate-stat-value id="validate-valid" 0
                .validate-stat-item
                  .validate-stat-label Total
                  .validate-stat-value id="validate-total" 0
              .validate-progress
                .validate-progress-bar
                  .validate-progress-fill id="validate-progress" style="width: 0%;"

      .section
        .section-title Navigation
        .nav
          a.btn-home href=url_for("/") title="Home" 🏠 Home
          - ROUTES.each do |name, route|
            a.btn class="#{name.to_s == @source ? 'btn-primary' : ''}" href=url_for("/db?source=#{name}") #{name}

      .section
        .section-title Statistics
        - storage_files = route_storage_files(@route)
        .stats-grid
          .stat-box
            .stat-number = @route[:db][:tiles].count
            .stat-desc Cached Tiles
          .stat-box
            .stat-number = @route[:db][:misses].count
            .stat-desc Cache Misses
          .stat-box
            .stat-number = format_bytes(get_tiles_size(@route))
            .stat-desc Cache Size

      .section
        .section-title Storage Files
        - if storage_files.empty?
          .json-missing mbtiles_file is not configured.
        - else
          .table-container
            table
              thead
                tr
                  th File
                  th Path
                  th Size
                  th Status
              tbody
                - storage_files.each do |file|
                  tr
                    td = file[:name]
                    td = file[:path]
                    td = format_bytes(file[:size])
                    td = file[:exists] ? 'exists' : 'missing'

      .main-layout
        .sidebar
          .table-list
            .table-list-header Tables
            - current_table = params[:table] || 'tiles'
            - tables = [{ name: 'tiles', count: @route[:db][:tiles].count, title: 'Tiles' }, { name: 'misses', count: @route[:db][:misses].count, title: 'Misses' }, { name: 'metadata', count: @route[:db][:metadata].count, title: 'Metadata' }, { name: 'tile_scan_progress', count: (@route[:db].table_exists?(:tile_scan_progress) ? @route[:db][:tile_scan_progress].count : 0), title: 'Autoscan Progress' }]
            - tables.each do |table|
              a.table-item class="#{table[:name] == current_table ? 'active' : ''}" href=url_for("/db?source=#{@source}&table=#{table[:name]}")
                .table-name = table[:title]
                .table-count = "#{table[:count]} records"

        .content
          - page, per_page = (params[:page] || 1).to_i, 50
          - offset = (page - 1) * per_page
          - table_data = { 'tiles' => { title: 'Tiles Table', cols: ['Zoom Level', 'Tile Column', 'Tile Row', 'Data Size', 'Generated', 'Updated At'], data: @route[:db][:tiles].limit(per_page).offset(offset), format: ->(r) { [r[:zoom_level], r[:tile_column], r[:tile_row], "#{(r[:tile_data].bytesize / 1024.0).round(1)} KB", r[:generated], r[:updated_at] ? Time.parse(r[:updated_at].to_s).strftime("%Y-%m-%d %H:%M:%S") : 'N/A'] } }, 'misses' => { title: 'Misses Table', cols: ['Zoom Level', 'Tile Column', 'Tile Row', 'Timestamp', 'Status', 'Reason', 'Details', 'Response Size'], data: @route[:db][:misses].order(Sequel.desc(:ts)).limit(per_page).offset(offset), format: ->(r) { [r[:zoom_level], r[:tile_column], r[:tile_row], Time.at(r[:ts]).strftime("%Y-%m-%d %H:%M:%S"), r[:status] || 'N/A', r[:reason] || 'N/A', r[:details] || 'N/A', r[:response_body] ? "#{(r[:response_body].bytesize / 1024.0).round(1)} KB" : 'N/A'] } }, 'metadata' => { title: 'Metadata Table', cols: ['Name', 'Value'], data: @route[:db][:metadata].limit(per_page).offset(offset), format: ->(r) { [r[:name], r[:value]] } }, 'tile_scan_progress' => { title: 'Autoscan Progress Table', cols: ['Source', 'Zoom Level', 'Last X', 'Last Y', 'Tiles Today', 'Last Scan Date', 'Status'], data: (@route[:db].table_exists?(:tile_scan_progress) ? @route[:db][:tile_scan_progress].order(:zoom_level).limit(per_page).offset(offset) : []), format: ->(r) { [r[:source], r[:zoom_level], r[:last_x], r[:last_y], r[:tiles_today], r[:last_scan_date], r[:status]] } } }
          - current_data = table_data[current_table]
          - total_count = current_table == 'tile_scan_progress' && !@route[:db].table_exists?(:tile_scan_progress) ? 0 : @route[:db][current_table.to_sym].count
          - total_pages = (total_count.to_f / per_page).ceil
          
          .section
            .table-header
              .table-title = current_data[:title]
            .table-container
              table
                thead
                  tr
                    - current_data[:cols].each do |col|
                      th = col
                tbody
                  - current_data[:data].each do |record|
                    tr
                      - current_data[:format].call(record).each do |cell|
                        td = cell
            .pagination
              .pagination-info
                | Showing #{offset + 1}-#{[offset + per_page, total_count].min} of #{total_count} records
              .pagination-controls
                - if page > 1
                  a.pagination-btn href=url_for("/db?source=#{@source}&table=#{current_table}&page=#{page-1}") ← Previous
                - if page < total_pages
                  a.pagination-btn href=url_for("/db?source=#{@source}&table=#{current_table}&page=#{page+1}") Next →

      - json_path = progress_json_path(@route)
      - json_content = json_path && File.exist?(json_path) ? File.read(json_path) : nil
      .section
        .section-title Progress JSON
        - if json_content
          .json-path = json_path
          .json-box
            pre = JSON.pretty_generate(JSON.parse(json_content)) rescue json_content
        - else
          .json-path = json_path || '(mbtiles_file not configured)'
          .json-missing File not found — progress has not been saved to JSON yet.

javascript:
  const BASE_PATH = '#{base_path}';
  const SOURCE = '#{@source}';
  let validateStatusInterval = null;
  let modalCallback = null;
  
  const $ = id => document.getElementById(id);
  
  const showModal = (title, message, onConfirm) => {
    $('modal-title').textContent = title;
    $('modal-message').textContent = message;
    modalCallback = onConfirm;
    $('modal-overlay').classList.add('show');
  };
  
  const closeModal = () => {
    $('modal-overlay').classList.remove('show');
    modalCallback = null;
  };
  
  const confirmModal = () => {
    if (modalCallback) modalCallback();
    closeModal();
  };
  
  const runVacuum = async () => {
    const btn = document.querySelector('.btn-vacuum');
    const originalText = btn.innerHTML;
    const setState = (text, disabled = false) => {
      btn.innerHTML = text;
      btn.disabled = disabled;
      btn.style.opacity = disabled ? '0.7' : '1';
    };
    
    setState('Running...', true);
    try {
      const { status } = await fetch(`${BASE_PATH}/admin/vacuum`).then(r => r.json());
      setState(status === 'success' ? 'Started' : 'Failed');
      setTimeout(() => setState(originalText), status === 'success' ? 2000 : 3000);
    } catch (error) {
      setState('Failed');
      console.error('VACUUM failed:', error);
      setTimeout(() => setState(originalText), 3000);
    }
  };
  
  const startValidation = () => showModal('Confirm Validation', 'Start tile validation and cleanup process? This will check all cached tiles and remove invalid ones. The process may take a long time.', performStartValidation);
  
  const performStartValidation = async () => {
    const btnStart = $('btn-validate');
    const originalText = btnStart.innerHTML;
    btnStart.disabled = true;
    btnStart.innerHTML = 'Starting...';
    
    try {
      const response = await fetch(`${BASE_PATH}/admin/validate/${SOURCE}/start`, { method: 'POST' });
      const data = await response.json();
      
      if (response.ok && data.success) {
        btnStart.style.display = 'none';
        $('btn-stop-validate').style.display = 'inline-flex';
        $('validate-stats').style.display = 'block';
        startValidationStatusPolling();
      } else {
        throw new Error(data.error || 'Failed to start validation');
      }
    } catch (error) {
      btnStart.innerHTML = originalText;
      btnStart.disabled = false;
      alert(`Failed to start validation: ${error.message || 'Unknown error occurred'}`);
      console.error('Validation start failed:', error);
    }
  };
  
  const stopValidation = () => showModal('Confirm Stop', 'Stop the validation process? The current progress will be saved.', performStopValidation);
  
  const performStopValidation = async () => {
    const btnStop = $('btn-stop-validate');
    const originalText = btnStop.innerHTML;
    btnStop.disabled = true;
    btnStop.innerHTML = 'Stopping...';
    
    try {
      const response = await fetch(`${BASE_PATH}/admin/validate/${SOURCE}/stop`, { method: 'POST' });
      const data = await response.json();
      
      if (response.ok && data.success) {
        stopValidationStatusPolling();
        $('btn-validate').style.display = 'inline-flex';
        $('btn-validate').disabled = false;
        btnStop.style.display = 'none';
        $('validate-stats').style.display = 'none';
      } else {
        throw new Error(data.error || 'Failed to stop validation');
      }
    } catch (error) {
      btnStop.disabled = false;
      btnStop.innerHTML = originalText;
      alert(`Failed to stop validation: ${error.message || 'Unknown error occurred'}`);
      console.error('Validation stop failed:', error);
    }
  };
  
  const updateValidationStats = status => {
    $('validate-processed').textContent = status.processed.toLocaleString();
    $('validate-invalid').textContent = status.invalid.toLocaleString();
    $('validate-valid').textContent = status.valid.toLocaleString();
    $('validate-total').textContent = status.total.toLocaleString();
    $('validate-progress').style.width = `${status.total > 0 ? (status.processed / status.total) * 100 : 0}%`;
  };
  
  const startValidationStatusPolling = () => {
    if (validateStatusInterval) clearInterval(validateStatusInterval);
    
    const pollStatus = async () => {
      try {
        const status = await fetch(`${BASE_PATH}/admin/validate/${SOURCE}/status`).then(r => r.json());
        updateValidationStats(status);
        
        if (!status.running) {
          stopValidationStatusPolling();
          $('btn-validate').style.display = 'inline-flex';
          $('btn-validate').disabled = false;
          $('btn-stop-validate').style.display = 'none';
          setTimeout(() => { $('validate-stats').style.display = 'none'; }, status.processed > 0 ? 5000 : 0);
        }
      } catch (error) {
        console.error('Failed to fetch validation status:', error);
      }
    };
    
    pollStatus();
    validateStatusInterval = setInterval(pollStatus, 2000);
  };
  
  const stopValidationStatusPolling = () => {
    if (validateStatusInterval) {
      clearInterval(validateStatusInterval);
      validateStatusInterval = null;
    }
  };
  
  document.addEventListener('DOMContentLoaded', () => {
    const btnValidate = $('btn-validate');
    if (btnValidate) {
      fetch(`${BASE_PATH}/admin/validate/${SOURCE}/status`)
        .then(r => r.json())
        .then(status => {
          if (status.running) {
            btnValidate.style.display = 'none';
            $('btn-stop-validate').style.display = 'inline-flex';
            $('validate-stats').style.display = 'block';
            updateValidationStats(status);
            startValidationStatusPolling();
          }
        })
        .catch(err => console.error('Failed to check validation status:', err));
    }
  });