-- Количество запросов за период
SELECT 
    DATE(created_at) as date,
    COUNT(*) as queries,
    COUNT(DISTINCT user_id) as unique_users
FROM audit_log
WHERE action = 'knowledge_query'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- Популярные запросы
SELECT 
    details->>'query' as query,
    COUNT(*) as count
FROM audit_log
WHERE action = 'knowledge_query'
  AND created_at > NOW() - INTERVAL '7 days'
GROUP BY details->>'query'
ORDER BY count DESC
LIMIT 20;

-- Распределение по ролям
SELECT 
    details->>'user_role' as role,
    COUNT(*) as queries
FROM audit_log
WHERE action = 'knowledge_query'
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY details->>'user_role';

-- Среднее время ответа
SELECT 
    DATE(created_at) as date,
    AVG((details->>'response_time_ms')::int) as avg_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (
        ORDER BY (details->>'response_time_ms')::int
    ) as p95_ms
FROM audit_log
WHERE action = 'knowledge_query'
  AND created_at > NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at);

-- Распределение по релевантности
SELECT 
    CASE 
        WHEN (details->>'top_score')::float >= 0.85 THEN 'high'
        WHEN (details->>'top_score')::float >= 0.70 THEN 'medium'
        ELSE 'low'
    END as relevance,
    COUNT(*) as count
FROM audit_log
WHERE action = 'knowledge_query'
  AND details->>'top_score' IS NOT NULL
  AND created_at > NOW() - INTERVAL '30 days'
GROUP BY relevance;

-- Запросы, не нашедшие информацию
SELECT 
    details->>'query' as query,
    COUNT(*) as count
FROM audit_log
WHERE action = 'knowledge_query'
  AND (details->>'chunks_retrieved')::int = 0
  AND created_at > NOW() - INTERVAL '7 days'
GROUP BY details->>'query'
ORDER BY count DESC
LIMIT 20;

-- Документы по категориям
SELECT 
    category,
    COUNT(*) as count,
    SUM(file_size_bytes) / 1024 / 1024 as total_mb
FROM documents
WHERE status = 'indexed'
GROUP BY category
ORDER BY count DESC;

-- Документы по access_level
SELECT 
    access_level,
    COUNT(*) as count
FROM documents
WHERE status = 'indexed'
GROUP BY access_level;

-- Документы по brand_id
SELECT 
    brand_id,
    COUNT(*) as count
FROM documents
WHERE status = 'indexed'
GROUP BY brand_id;

-- Загрузки за период
SELECT 
    DATE(created_at) as date,
    COUNT(*) as uploads,
    COUNT(*) FILTER (WHERE status = 'indexed') as indexed,
    COUNT(*) FILTER (WHERE status = 'quarantine') as quarantined
FROM documents
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at);

-- Статистика модерации
SELECT 
    DATE(moderated_at) as date,
    COUNT(*) FILTER (WHERE status = 'approved') as approved,
    COUNT(*) FILTER (WHERE status = 'rejected') as rejected
FROM documents
WHERE moderated_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(moderated_at);

-- Причины попадания в карантин
SELECT 
    error_type,
    COUNT(*) as count
FROM quarantine
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY error_type
ORDER BY count DESC;

📊 Knowledge Analytics — Неделя 02/2026

ИСПОЛЬЗОВАНИЕ:
- Всего запросов: 1,234
- Уникальных пользователей: 18
- Запросов в день: 176 (среднее)

КАЧЕСТВО:
- Среднее время ответа: 1.8 сек
- P95 время ответа: 3.2 сек
- Высокая релевантность: 78%

КОНТЕНТ:
- Всего документов: 156
- Загружено за неделю: 12
- На модерации: 3
- В карантине: 1

ТОП ЗАПРОСЫ:
1. "размерная сетка" — 89
2. "состав ткани" — 67
3. "регламент возврата" — 45