-- Query: Active host breakdown: split vs single-type
-- Result variable: host_breakdown

-- Get all hosts with >2 total messages on Dec 11
WITH all_active_hosts AS (
    SELECT
        c.host_id,
        COUNT(*) AS total_tool_messages
    FROM hostagentspg.public.tool_messages AS tm
    INNER JOIN
        hostagentspg.public.conversations AS c
        ON tm.conversation_id = c.id
    WHERE tm.tool_timestamp::DATE = '2025-12-11'
    GROUP BY c.host_id
    HAVING COUNT(*) > 2
),

-- Hosts that had >2 in at least one conversation type
hosts_in_type_breakdown AS (
    SELECT DISTINCT host_id
    FROM active_hosts_by_type
)

SELECT
    COUNT(
        CASE WHEN htb.host_id IS NULL THEN 1 END
    ) AS hosts_with_split_messages,
    COUNT(
        CASE WHEN htb.host_id IS NOT NULL THEN 1 END
    ) AS hosts_with_single_type_dominant,
    COUNT(*) AS total_active_hosts
FROM all_active_hosts AS aah
LEFT JOIN hosts_in_type_breakdown AS htb ON aah.host_id = htb.host_id