-- Set your date range
SET @start_date = '2025-04-01';
SET @end_date = '2025-05-01';

-- Get all active students first
WITH active_students AS (
    SELECT DISTINCT student_id
    FROM (
        SELECT qs.student_id
        FROM quiz_summary qs
        JOIN user u ON u.id = qs.student_id
        WHERE u.type = 'student'
          AND qs.submit = 1
          AND qs.submit_at BETWEEN @start_date AND @end_date

        UNION

        SELECT ts.student_id
        FROM tutor_session ts
        JOIN user u ON u.id = ts.student_id
        WHERE u.type = 'student'
          AND ts.status = 'completed'
          AND ts.created_at BETWEEN @start_date AND @end_date

        UNION

        SELECT tsp.participant_id AS student_id
        FROM tutor_session_participant tsp
        JOIN tutor_session ts ON ts.id = tsp.session_id
        JOIN user u ON u.id = tsp.participant_id
        WHERE u.type = 'student'
          AND ts.status = 'completed'
          AND ts.created_at BETWEEN @start_date AND @end_date

        UNION

        SELECT fl.user_id AS student_id
        FROM file_log fl
        JOIN user u ON u.id = fl.user_id
        WHERE u.type = 'student'
          AND fl.type = 'video'
          AND fl.created_at BETWEEN @start_date AND @end_date

        UNION

        SELECT ful.user_id AS student_id
        FROM feature_user_logger ful
        JOIN user u ON u.id = ful.user_id
        WHERE u.type = 'student'
          AND ful.created_at BETWEEN @start_date AND @end_date
    ) AS all_activities
),

-- Main students = active students with no parent record
main_students AS (
    SELECT u.id AS user_id
    FROM user u
    JOIN active_students a ON a.student_id = u.id
    LEFT JOIN parents p ON p.student_id = u.id
    WHERE p.id IS NULL
),

-- Main parents = parents whose child is in the active_students set
main_parents AS (
    SELECT DISTINCT u.id AS user_id
    FROM user u
    JOIN parents p ON p.parent_id = u.id
    JOIN active_students a ON a.student_id = p.student_id
)

-- Final result: count + optional list
SELECT 
    (SELECT COUNT(*) FROM main_students) AS total_main_students,
    (SELECT COUNT(*) FROM main_parents) AS total_main_parents;