-- ============================================================
-- Analytics Queries
-- All queries accept date range parameters: @start_date, @end_date
-- Usage: SET @start_date = '2025-01-01'; SET @end_date = '2025-01-31';
-- ============================================================

-- 1. Total pageviews by date
SELECT DATE(server_timestamp) AS day, COUNT(*) AS pageviews
FROM pageviews
WHERE server_timestamp BETWEEN @start_date AND @end_date
  AND type = 'pageview'
GROUP BY day
ORDER BY day;

-- 2. Top pages by view count
SELECT url, COUNT(*) AS views
FROM pageviews
WHERE server_timestamp BETWEEN @start_date AND @end_date
  AND type = 'pageview'
GROUP BY url
ORDER BY views DESC
LIMIT 20;

-- 3. Pageviews by hour of day
SELECT HOUR(server_timestamp) AS hour_of_day, COUNT(*) AS pageviews
FROM pageviews
WHERE server_timestamp BETWEEN @start_date AND @end_date
  AND type = 'pageview'
GROUP BY hour_of_day
ORDER BY hour_of_day;

-- 4. Unique sessions per day
SELECT DATE(server_timestamp) AS day, COUNT(DISTINCT session_id) AS unique_sessions
FROM pageviews
WHERE server_timestamp BETWEEN @start_date AND @end_date
GROUP BY day
ORDER BY day;

-- 5. Top referrers
SELECT
    CASE
        WHEN referrer IS NULL OR referrer = '' THEN '(direct)'
        ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '/', 3), '://', -1)
    END AS referrer_domain,
    COUNT(*) AS visits
FROM sessions
WHERE start_time BETWEEN @start_date AND @end_date
  AND referrer IS NOT NULL
GROUP BY referrer_domain
ORDER BY visits DESC
LIMIT 20;

-- 6. Direct vs referred traffic
SELECT
    CASE WHEN referrer IS NULL OR referrer = '' THEN 'Direct' ELSE 'Referred' END AS source,
    COUNT(*) AS sessions_count
FROM sessions
WHERE start_time BETWEEN @start_date AND @end_date
GROUP BY source;

-- 7. Average load time by page
SELECT url,
    ROUND(AVG(load_time)) AS avg_load_ms,
    ROUND(AVG(ttfb)) AS avg_ttfb_ms,
    COUNT(*) AS samples
FROM performance
WHERE server_timestamp BETWEEN @start_date AND @end_date
GROUP BY url
ORDER BY avg_load_ms DESC
LIMIT 20;

-- 8. Web Vitals percentiles (p50, p75, p95)
SELECT
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY lcp), 2) AS lcp_p50,
    ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY lcp), 2) AS lcp_p75,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY lcp), 2) AS lcp_p95,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY cls), 4) AS cls_p50,
    ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY cls), 4) AS cls_p75,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY inp), 2) AS inp_p50,
    ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY inp), 2) AS inp_p75
FROM performance
WHERE server_timestamp BETWEEN @start_date AND @end_date
  AND lcp IS NOT NULL;

-- 9. Slowest pages by load time
SELECT url,
    ROUND(AVG(load_time)) AS avg_load_ms,
    MAX(load_time) AS max_load_ms,
    COUNT(*) AS samples
FROM performance
WHERE server_timestamp BETWEEN @start_date AND @end_date
GROUP BY url
HAVING samples >= 5
ORDER BY avg_load_ms DESC
LIMIT 10;

-- 10. Error frequency by message
SELECT error_message, COUNT(*) AS occurrences,
    MIN(server_timestamp) AS first_seen,
    MAX(server_timestamp) AS last_seen
FROM errors
WHERE server_timestamp BETWEEN @start_date AND @end_date
GROUP BY error_message
ORDER BY occurrences DESC
LIMIT 20;

-- 11. Error trend over time
SELECT DATE(server_timestamp) AS day, COUNT(*) AS error_count
FROM errors
WHERE server_timestamp BETWEEN @start_date AND @end_date
GROUP BY day
ORDER BY day;

-- 12. Top error-producing pages
SELECT url, COUNT(*) AS error_count
FROM errors
WHERE server_timestamp BETWEEN @start_date AND @end_date
GROUP BY url
ORDER BY error_count DESC
LIMIT 10;

-- 13. Sessions per day
SELECT DATE(start_time) AS day, COUNT(*) AS session_count
FROM sessions
WHERE start_time BETWEEN @start_date AND @end_date
GROUP BY day
ORDER BY day;

-- 14. Average session duration and pages per session
SELECT
    ROUND(AVG(duration_seconds)) AS avg_duration_sec,
    ROUND(AVG(page_count), 1) AS avg_pages
FROM sessions
WHERE start_time BETWEEN @start_date AND @end_date;

-- 15. Bounce rate (single-page sessions)
SELECT
    COUNT(*) AS total_sessions,
    SUM(CASE WHEN page_count = 1 THEN 1 ELSE 0 END) AS bounced,
    ROUND(SUM(CASE WHEN page_count = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS bounce_rate_pct
FROM sessions
WHERE start_time BETWEEN @start_date AND @end_date;

-- 16. Dashboard summary (overview cards)
SELECT
    (SELECT COUNT(*) FROM pageviews WHERE server_timestamp BETWEEN @start_date AND @end_date AND type = 'pageview') AS total_pageviews,
    (SELECT COUNT(DISTINCT session_id) FROM pageviews WHERE server_timestamp BETWEEN @start_date AND @end_date) AS total_sessions,
    (SELECT ROUND(AVG(load_time)) FROM performance WHERE server_timestamp BETWEEN @start_date AND @end_date) AS avg_load_time_ms,
    (SELECT COUNT(*) FROM errors WHERE server_timestamp BETWEEN @start_date AND @end_date) AS total_errors;

-- 17. Browser breakdown (simplified)
SELECT
    CASE
        WHEN user_agent LIKE '%Firefox%' THEN 'Firefox'
        WHEN user_agent LIKE '%Edg/%' THEN 'Edge'
        WHEN user_agent LIKE '%Chrome%' THEN 'Chrome'
        WHEN user_agent LIKE '%Safari%' THEN 'Safari'
        ELSE 'Other'
    END AS browser,
    COUNT(*) AS pageviews
FROM pageviews
WHERE server_timestamp BETWEEN @start_date AND @end_date
  AND type = 'pageview'
GROUP BY browser
ORDER BY pageviews DESC;

-- 18. Viewport size distribution
SELECT
    CASE
        WHEN viewport_width < 768 THEN 'Mobile (<768px)'
        WHEN viewport_width < 1024 THEN 'Tablet (768-1023px)'
        WHEN viewport_width < 1440 THEN 'Desktop (1024-1439px)'
        ELSE 'Large (1440px+)'
    END AS device_class,
    COUNT(*) AS pageviews
FROM pageviews
WHERE server_timestamp BETWEEN @start_date AND @end_date
  AND type = 'pageview'
  AND viewport_width IS NOT NULL
GROUP BY device_class
ORDER BY pageviews DESC;
