-- Test script to verify all metrics queries work correctly
-- Run this after sending test metrics with send-metrics.sh

\echo '===== CHECKING AVAILABLE TABLES ====='
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_name LIKE 'quickdid%'
ORDER BY table_name;

\echo ''
\echo '===== CHECKING TABLE STRUCTURES ====='
\echo 'Structure of quickdid.http.request.count table:'
\d "quickdid.http.request.count"

\echo ''
\echo 'Structure of quickdid.http.request.duration_ms table:'
\d "quickdid.http.request.duration_ms"

\echo ''
\echo '===== QUERY 1: Recent HTTP Request Counts ====='
SELECT 
    time,
    tags,
    tags->>'method' as method,
    tags->>'path' as path,
    tags->>'status' as status,
    value
FROM "quickdid.http.request.count"
WHERE time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC
LIMIT 10;

\echo ''
\echo '===== QUERY 2: HTTP Request Duration Statistics by Endpoint ====='
SELECT 
    time_bucket('1 minute', time) AS minute,
    tags->>'method' as method,
    tags->>'path' as path,
    tags->>'status' as status,
    COUNT(*) as request_count,
    AVG(mean) as avg_duration_ms,
    MAX(p99) as p99_duration_ms,
    MIN(mean) as min_duration_ms
FROM "quickdid.http.request.duration_ms"
WHERE time > NOW() - INTERVAL '1 hour'
  AND tags IS NOT NULL
GROUP BY minute, tags->>'method', tags->>'path', tags->>'status'
ORDER BY minute DESC
LIMIT 10;

\echo ''
\echo '===== QUERY 3: Rate Limiter Status Over Time ====='
SELECT 
    time,
    value as available_permits
FROM "quickdid.resolver.rate_limit.available_permits"
WHERE time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC
LIMIT 10;

\echo ''
\echo '===== QUERY 4: Resolver Performance Comparison ====='
SELECT 
    tags->>'resolver' as resolver_type,
    COUNT(*) as sample_count,
    AVG(mean) as avg_resolution_time_ms,
    MAX(p99) as p99_resolution_time_ms,
    MIN(mean) as min_resolution_time_ms
FROM "quickdid.resolver.resolution_time"
WHERE time > NOW() - INTERVAL '1 hour'
  AND tags->>'resolver' IS NOT NULL
GROUP BY tags->>'resolver'
ORDER BY avg_resolution_time_ms;

\echo ''
\echo '===== QUERY 5: Cache Hit Rate Analysis ====='
WITH cache_stats AS (
    SELECT 
        'hits' as metric_type,
        SUM(value) as total_count
    FROM "quickdid.cache.hit.count"
    WHERE time > NOW() - INTERVAL '1 hour'
    UNION ALL
    SELECT 
        'misses' as metric_type,
        SUM(value) as total_count
    FROM "quickdid.cache.miss.count"
    WHERE time > NOW() - INTERVAL '1 hour'
)
SELECT 
    SUM(CASE WHEN metric_type = 'hits' THEN total_count ELSE 0 END) as total_hits,
    SUM(CASE WHEN metric_type = 'misses' THEN total_count ELSE 0 END) as total_misses,
    CASE 
        WHEN SUM(total_count) > 0 THEN 
            ROUND(100.0 * SUM(CASE WHEN metric_type = 'hits' THEN total_count ELSE 0 END) / SUM(total_count), 2)
        ELSE 0 
    END as hit_rate_percentage
FROM cache_stats;

\echo ''
\echo '===== QUERY 6: Hypertable Information ====='
SELECT 
    hypertable_schema,
    hypertable_name,
    owner,
    num_dimensions,
    num_chunks,
    compression_enabled
FROM timescaledb_information.hypertables
WHERE hypertable_name LIKE 'quickdid%'
ORDER BY hypertable_name;

\echo ''
\echo '===== QUERY 7: HTTP Error Rate by Endpoint ====='
WITH status_counts AS (
    SELECT 
        time_bucket('5 minutes', time) as period,
        tags->>'path' as path,
        CASE 
            WHEN (tags->>'status')::int >= 400 THEN 'error'
            ELSE 'success'
        END as status_category,
        SUM(value) as request_count
    FROM "quickdid.http.request.count"
    WHERE time > NOW() - INTERVAL '1 hour'
    GROUP BY period, path, status_category
)
SELECT 
    period,
    path,
    SUM(CASE WHEN status_category = 'error' THEN request_count ELSE 0 END) as error_count,
    SUM(CASE WHEN status_category = 'success' THEN request_count ELSE 0 END) as success_count,
    CASE 
        WHEN SUM(request_count) > 0 THEN 
            ROUND(100.0 * SUM(CASE WHEN status_category = 'error' THEN request_count ELSE 0 END) / SUM(request_count), 2)
        ELSE 0 
    END as error_rate_percentage
FROM status_counts
GROUP BY period, path
HAVING SUM(request_count) > 0
ORDER BY period DESC, error_rate_percentage DESC;

\echo ''
\echo '===== TEST COMPLETED ====='