-- Query: New hosts last week: activity & plans
-- Result variable: new_hosts_last_week

-- New hosts added in last week (2026-01-05 to 2026-01-12)
-- with their activity levels and plan types

WITH new_hosts AS (
    SELECT
        h.id AS host_id,
        h.created_at,
        s.pricing_plan_name,
        COALESCE(s.pricing_plan_name, 'No Plan') AS plan_type
    FROM hostagentspg.public.hosts AS h
    LEFT JOIN hostagentspg.public.subscriptions AS s ON h.id = s.host_id
    WHERE
        h.created_at >= '2026-01-05'::DATE
        AND h.created_at < '2026-01-12'::DATE + INTERVAL '1 day'
),

host_activity AS (
    SELECT
        c.host_id,
        MAX(
            CASE WHEN tm.tool_timestamp::DATE = '2026-01-12' THEN 1 ELSE 0 END
        ) AS active_today,
        MAX(
            CASE WHEN tm.tool_timestamp >= '2026-01-06' THEN 1 ELSE 0 END
        ) AS active_7day,
        MAX(
            CASE WHEN tm.tool_timestamp >= '2025-12-14' THEN 1 ELSE 0 END
        ) AS active_30day
    FROM hostagentspg.public.tool_messages AS tm
    INNER JOIN
        hostagentspg.public.conversations AS c
        ON tm.conversation_id = c.id
    WHERE c.host_id IN (SELECT host_id FROM new_hosts)
    GROUP BY c.host_id
)

SELECT
    nh.host_id,
    nh.created_at,
    nh.plan_type,
    COALESCE(ha.active_today, 0) AS is_dau,
    COALESCE(ha.active_7day, 0) AS is_7day_active,
    COALESCE(ha.active_30day, 0) AS is_30day_active
FROM new_hosts AS nh
LEFT JOIN host_activity AS ha ON nh.host_id = ha.host_id
ORDER BY nh.created_at DESC