-- =====================
-- Analytics
-- =====================

-- =====================
-- User Analytics
-- =====================

-- name: AnalyticsUsersSummary :one
SELECT
    COUNT(*)::bigint AS total,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE)::bigint AS new_today,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '7 days')::bigint AS new_this_week,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days')::bigint AS new_this_month
FROM users;

-- name: AnalyticsUsersByRole :many
SELECT
    COALESCE(role, 'unknown') AS role,
    COUNT(*)::bigint AS count
FROM users
GROUP BY role
ORDER BY count DESC;

-- name: AnalyticsUsersByStatus :many
SELECT
    COALESCE(status, 'unknown') AS status,
    COUNT(*)::bigint AS count
FROM users
GROUP BY status
ORDER BY count DESC;

-- name: AnalyticsUsersByAgeGroup :many
SELECT
    COALESCE(age_group, 'unknown') AS age_group,
    COUNT(*)::bigint AS count
FROM users
GROUP BY age_group
ORDER BY count DESC;

-- name: AnalyticsUsersByKnowledgeLevel :many
SELECT
    COALESCE(knowledge_level, 'unknown') AS knowledge_level,
    COUNT(*)::bigint AS count
FROM users
GROUP BY knowledge_level
ORDER BY count DESC;

-- name: AnalyticsUsersByRegion :many
SELECT
    COALESCE(region, 'unknown') AS region,
    COUNT(*)::bigint AS count
FROM users
GROUP BY region
ORDER BY count DESC;

-- name: AnalyticsUserRegistrationsLast30Days :many
SELECT
    d.date,
    COUNT(u.id)::bigint AS count
FROM generate_series(
    CURRENT_DATE - INTERVAL '29 days',
    CURRENT_DATE,
    INTERVAL '1 day'
) AS d(date)
LEFT JOIN users u ON u.created_at::date = d.date
GROUP BY d.date
ORDER BY d.date;

-- =====================
-- Subscription Analytics
-- =====================

-- name: AnalyticsSubscriptionsSummary :one
SELECT
    COUNT(*)::bigint AS total,
    COUNT(*) FILTER (WHERE status = 'ACTIVE')::bigint AS active,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE)::bigint AS new_today,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '7 days')::bigint AS new_this_week,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days')::bigint AS new_this_month
FROM user_subscriptions;

-- name: AnalyticsSubscriptionsByStatus :many
SELECT
    COALESCE(status, 'unknown') AS status,
    COUNT(*)::bigint AS count
FROM user_subscriptions
GROUP BY status
ORDER BY count DESC;

-- name: AnalyticsRevenueByPlan :many
SELECT
    sp.name AS plan_name,
    sp.currency,
    COUNT(p.id)::bigint AS total_payments,
    COALESCE(SUM(p.amount), 0)::float8 AS total_revenue
FROM payments p
JOIN subscription_plans sp ON sp.id = p.plan_id
WHERE p.status = 'SUCCESS'
GROUP BY sp.name, sp.currency
ORDER BY total_revenue DESC;

-- name: AnalyticsNewSubscriptionsLast30Days :many
SELECT
    d.date,
    COUNT(us.id)::bigint AS count
FROM generate_series(
    CURRENT_DATE - INTERVAL '29 days',
    CURRENT_DATE,
    INTERVAL '1 day'
) AS d(date)
LEFT JOIN user_subscriptions us ON us.created_at::date = d.date
GROUP BY d.date
ORDER BY d.date;

-- =====================
-- Payment Analytics
-- =====================

-- name: AnalyticsPaymentsSummary :one
SELECT
    COALESCE(SUM(amount) FILTER (WHERE status = 'SUCCESS'), 0)::float8 AS total_revenue,
    COALESCE(AVG(amount) FILTER (WHERE status = 'SUCCESS'), 0)::float8 AS avg_value,
    COUNT(*)::bigint AS total_payments,
    COUNT(*) FILTER (WHERE status = 'SUCCESS')::bigint AS successful_payments
FROM payments;

-- name: AnalyticsPaymentsByStatus :many
SELECT
    COALESCE(status, 'unknown') AS status,
    COUNT(*)::bigint AS count,
    COALESCE(SUM(amount), 0)::float8 AS total_amount
FROM payments
GROUP BY status
ORDER BY count DESC;

-- name: AnalyticsPaymentsByMethod :many
SELECT
    COALESCE(payment_method, 'unknown') AS payment_method,
    COUNT(*)::bigint AS count,
    COALESCE(SUM(amount), 0)::float8 AS total_amount
FROM payments
WHERE status = 'SUCCESS'
GROUP BY payment_method
ORDER BY count DESC;

-- name: AnalyticsRevenueLast30Days :many
SELECT
    d.date,
    COALESCE(SUM(p.amount), 0)::float8 AS total_revenue
FROM generate_series(
    CURRENT_DATE - INTERVAL '29 days',
    CURRENT_DATE,
    INTERVAL '1 day'
) AS d(date)
LEFT JOIN payments p ON p.paid_at::date = d.date AND p.status = 'SUCCESS'
GROUP BY d.date
ORDER BY d.date;

-- =====================
-- Course Analytics
-- =====================

-- name: AnalyticsCourseCounts :one
SELECT
    (SELECT COUNT(*)::bigint FROM course_categories) AS total_categories,
    (SELECT COUNT(*)::bigint FROM courses) AS total_courses,
    (SELECT COUNT(*)::bigint FROM sub_courses) AS total_sub_courses,
    (SELECT COUNT(*)::bigint FROM sub_course_videos) AS total_videos;

-- =====================
-- Content Analytics
-- =====================

-- name: AnalyticsQuestionsCounts :one
SELECT
    (SELECT COUNT(*)::bigint FROM questions) AS total_questions,
    (SELECT COUNT(*)::bigint FROM question_sets) AS total_question_sets;

-- name: AnalyticsQuestionsByType :many
SELECT
    COALESCE(question_type, 'unknown') AS question_type,
    COUNT(*)::bigint AS count
FROM questions
GROUP BY question_type
ORDER BY count DESC;

-- name: AnalyticsQuestionSetsByType :many
SELECT
    COALESCE(set_type, 'unknown') AS set_type,
    COUNT(*)::bigint AS count
FROM question_sets
GROUP BY set_type
ORDER BY count DESC;

-- =====================
-- Notification Analytics
-- =====================

-- name: AnalyticsNotificationsSummary :one
SELECT
    COUNT(*)::bigint AS total,
    COUNT(*) FILTER (WHERE is_read = TRUE)::bigint AS read,
    COUNT(*) FILTER (WHERE is_read = FALSE)::bigint AS unread
FROM notifications;

-- name: AnalyticsNotificationsByChannel :many
SELECT
    COALESCE(channel, 'unknown') AS channel,
    COUNT(*)::bigint AS count
FROM notifications
GROUP BY channel
ORDER BY count DESC;

-- name: AnalyticsNotificationsByType :many
SELECT
    COALESCE(type, 'unknown') AS type,
    COUNT(*)::bigint AS count
FROM notifications
GROUP BY type
ORDER BY count DESC;

-- =====================
-- Issue Analytics
-- =====================

-- name: AnalyticsIssuesSummary :one
SELECT
    COUNT(*)::bigint AS total,
    COUNT(*) FILTER (WHERE status = 'resolved')::bigint AS resolved,
    CASE
        WHEN COUNT(*) > 0 THEN (COUNT(*) FILTER (WHERE status = 'resolved')::float8 / COUNT(*)::float8)
        ELSE 0::float8
    END AS resolution_rate
FROM reported_issues;

-- name: AnalyticsIssuesByStatus :many
SELECT
    COALESCE(status, 'unknown') AS status,
    COUNT(*)::bigint AS count
FROM reported_issues
GROUP BY status
ORDER BY count DESC;

-- name: AnalyticsIssuesByType :many
SELECT
    COALESCE(issue_type, 'unknown') AS issue_type,
    COUNT(*)::bigint AS count
FROM reported_issues
GROUP BY issue_type
ORDER BY count DESC;

-- =====================
-- Team Analytics
-- =====================

-- name: AnalyticsTeamSummary :one
SELECT
    COUNT(*)::bigint AS total_members
FROM team_members;

-- name: AnalyticsTeamByRole :many
SELECT
    COALESCE(team_role, 'unknown') AS team_role,
    COUNT(*)::bigint AS count
FROM team_members
GROUP BY team_role
ORDER BY count DESC;

-- name: AnalyticsTeamByStatus :many
SELECT
    COALESCE(status, 'unknown') AS status,
    COUNT(*)::bigint AS count
FROM team_members
GROUP BY status
ORDER BY count DESC;