- name: hits
  label: Hits
  icon: pulse
  chart: line
  query: |
    SELECT {bucket} AS bucket,
           COUNT(*) AS value
    FROM logs
    GROUP BY bucket
    ORDER BY bucket

- name: error_rate
  label: Error Rate (%)
  icon: file-alert
  chart: line
  query: |
    SELECT {bucket} AS bucket,
           SUM(CASE WHEN status BETWEEN 400 AND 599 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS value
    FROM logs
    GROUP BY bucket
    ORDER BY bucket

- name: cache_status_breakdown
  label: Cache Status
  icon: archive
  chart: polarArea
  query: |
    SELECT cache_status AS bucket,
           COUNT(*) AS value
    FROM logs
    GROUP BY cache_status
    ORDER BY value DESC
  colors:
    - "#3273dc"
    - "#23d160"
    - "#ffdd57"
    - "#ff3860"
    - "#7957d5"
    - "#363636"

- name: domain_traffic
  label: Top Domains
  icon: globe
  chart: table
  per_domain: false
  query: |
    SELECT host AS bucket,
           COUNT(*) AS value
    FROM logs
    GROUP BY host
    ORDER BY value DESC

- name: bytes_sent
  label: Bytes Sent
  icon: upload
  chart: line
  query: |
    SELECT {bucket} AS bucket,
           SUM(bytes_sent) AS value
    FROM logs
    GROUP BY bucket
    ORDER BY bucket

- name: top_paths
  label: Top Paths
  icon: map
  chart: table
  query: |
    SELECT path AS bucket,
           COUNT(*) AS value
    FROM (
        SELECT substr(substr(request, instr(request, ' ') + 1), 1,
                      instr(substr(request, instr(request, ' ') + 1), ' ') - 1) AS path
        FROM logs
    )
    GROUP BY path
    ORDER BY value DESC
    LIMIT 20

- name: user_agents
  label: User Agents
  icon: user
  chart: table
  query: |
    SELECT user_agent AS bucket,
           COUNT(*) AS value
    FROM logs
    GROUP BY user_agent
    ORDER BY value DESC
    LIMIT 20

- name: referrers
  label: Referrers
  icon: link
  chart: table
  query: |
    SELECT referer AS bucket,
           COUNT(*) AS value
    FROM logs
    GROUP BY referer
    ORDER BY value DESC
    LIMIT 20

- name: status_distribution
  label: HTTP Statuses
  icon: server
  chart: pie
  query: |
    SELECT CASE
             WHEN status BETWEEN 200 AND 299 THEN '2xx'
             WHEN status BETWEEN 300 AND 399 THEN '3xx'
             WHEN status BETWEEN 400 AND 499 THEN '4xx'
             ELSE '5xx'
           END AS bucket,
           COUNT(*) AS value
    FROM logs
    GROUP BY bucket
    ORDER BY bucket
  colors:
    - "#48c78e"
    - "#209cee"
    - "#ffdd57"
    - "#f14668"