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

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

- name: cache_status_breakdown
  label: Cache Status
  icon: archive
  chart: polarArea
  bucket: cache_status
  bucket_label: Cache Status
  query: |
    SELECT cache_status AS cache_status,
           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
  bucket: domain
  bucket_label: Domain
  query: |
    SELECT host AS domain,
           COUNT(*) AS value
    FROM logs
    GROUP BY domain
    ORDER BY value DESC

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

- name: top_paths
  label: Top Paths
  icon: map
  chart: table
  bucket: path
  bucket_label: Path
  query: |
    SELECT path AS path,
           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
  bucket: user_agent
  bucket_label: User Agent
  query: |
    SELECT user_agent AS user_agent,
           COUNT(*) AS value
    FROM logs
    GROUP BY user_agent
    ORDER BY value DESC
    LIMIT 20

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

- name: status_distribution
  label: HTTP Statuses
  icon: server
  chart: pie
  bucket: status_group
  bucket_label: Status
  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 status_group,
           COUNT(*) AS value
    FROM logs
    GROUP BY status_group
    ORDER BY status_group
  colors:
    - "#48c78e"
    - "#209cee"
    - "#ffdd57"
    - "#f14668"