-- Note: takes ~8 min on Large warehouse
-- TODO: updated at column is the last time the user was updated
-- Alternative design: INSERT and then clean up in a separate step
CREATE OR REPLACE TABLE SUNO_PROD.PROD.BOT_HOURLY AS
WITH date_bounds AS (
    -- We'll select users with sign-up dates OR clip gens
    -- within this date range
    SELECT
        '2025-01-01'::DATE as lookback_start,
        DATEADD(hour, -2, CURRENT_TIMESTAMP()) as recent_cutoff,
        CURRENT_DATE() as current_date
),
base_users AS (
    -- Some basic user information for the user's who fall within
    -- the date range.
    SELECT DISTINCT
        u.user_id,
        u.user_uid,
        u.user_joined_at,
        COALESCE(u.registered_platform, 'likely web') as registered_platform,
        u.recent_country,
        u.signup_country,
        u.recent_city,
        u.email,
        u.birthday,
        u.clerk_id,
        u.phone_number,
        SPLIT_PART(u.email, '@', 1) as email_username,
        LENGTH(SPLIT_PART(u.email, '@', 1)) as email_username_length,
        SPLIT_PART(u.email, '@', 2) as email_domain,
        ROUND(
            REGEXP_COUNT(SPLIT_PART(u.email, '@', 1), '[aeiou]', 1, 'i') / 
            NULLIF(LENGTH(SPLIT_PART(u.email, '@', 1)), 0),
            2
        ) as email_vowel_percent,
        u.is_staff
    FROM SUNO_PROD.PROD.DIM_USER u
    LEFT JOIN SUNO_PROD.PROD.DIM_CLIP c
        ON u.user_id = c.user_id,
        date_bounds d
    -- Uncomment to debug a specific user
    --WHERE stripe_customer_id = 'cus_RxxGIkI4rCWtwr'
    WHERE
        (
            u.user_joined_at > d.lookback_start
            OR (
                c.created_at > d.lookback_start
                AND c.created_at < d.recent_cutoff
            )
        )
        AND u.user_id NOT IN (
            16810196,
            34796903
        )
        
),
health_metrics AS (
    -- Smoke test for the clip metrics dataset so that if ETL ever breaks on this table,
    -- we don't assume everyone with a lack of clip engagement is a bot
    SELECT 
        COUNT(DISTINCT metrics.user_id) as total_users,
        COUNT(DISTINCT CASE WHEN played > 0 THEN metrics.user_id END) as users_with_plays,
        ROUND(COUNT(DISTINCT CASE WHEN played > 0 THEN metrics.user_id END) / 
            NULLIF(COUNT(DISTINCT metrics.user_id), 0), 3) as plays_ratio,
        plays_ratio >= 0.7 as is_dataset_healthy
    FROM (
        SELECT 
            c.user_id,
            COUNT(DISTINCT clip_id) as clips,
            COUNT_IF(PLAY_CNT > 0) as played,
            COUNT_IF(PLAY_CNT = 0) as unplayed
        FROM SUNO_PROD.PROD.CLIP_INITIAL_METRICS c
        JOIN base_users bu ON c.user_id = bu.user_id,
             date_bounds d
        WHERE p_date = d.current_date
            AND CLIP_CREATED_AT < d.recent_cutoff
        GROUP BY c.user_id
    ) metrics
),
dim_user_health_metrics AS (
    SELECT
        DATE(user_joined_at) as user_joined_at,
        COUNT_IF(signup_country IS NULL) as missing_country,
        COUNT(DISTINCT user_id) as total_users,
        -- signup_country is a proxy for missing app and web events!!
        -- we use this proxy for performance reasons
        ROUND(COUNT_IF(signup_country IS NULL) / COUNT(DISTINCT user_id) * 100,2) as percent_missing_country
    FROM SUNO_PROD.PROD.DIM_USER u
    JOIN date_bounds d
    ON DATE(user_joined_at) BETWEEN d.lookback_start AND d.recent_cutoff
    GROUP BY 1
    ORDER BY 1
),
user_stats AS (
    SELECT
        MAX(COALESCE(total_followers, 0)) as total_followers,
        MAX(COALESCE(total_following, 0)) as total_following,
        MAX(total_clips_public) as total_clips_public,
        MAX(total_likes) as total_likes,
        MAX(total_playlists) as total_playlists,
        MAX(longest_login_streak) as longest_login_streak,
        MAX(longest_creation_streak) as longest_creation_streak,
        user_id
    FROM SUNO_PROD.PROD.RDS_USER_STATS
    GROUP BY user_id
),
daily_hour_activity AS (
    SELECT 
        user_id,
        created_at,
        DATE_TRUNC('day', created_at) as activity_date,
        EXTRACT(HOUR FROM created_at)::NUMBER as hour_of_day,
        CASE 
            WHEN created_at > DATEADD(hour, -24, CURRENT_TIMESTAMP()) THEN '24h'
            WHEN created_at > DATEADD(day, -7, CURRENT_TIMESTAMP()) THEN '7d'
            ELSE '30d'
        END as time_window,
        COUNT(*) as clips_in_hour
    FROM SUNO_PROD.PROD.DIM_CLIP
    WHERE created_at > DATEADD(day, -30, CURRENT_TIMESTAMP())
    GROUP BY 
        user_id,
        created_at,
        DATE_TRUNC('day', created_at),
        hour_of_day,
        time_window
),
hour_metrics AS (
    SELECT 
        user_id,
        time_window,
        hour_of_day,
        SUM(clips_in_hour) as clips_in_hour,
        ROW_NUMBER() OVER (PARTITION BY user_id, time_window ORDER BY SUM(clips_in_hour) DESC) as hour_rank
    FROM daily_hour_activity
    GROUP BY user_id, time_window, hour_of_day
),
window_metrics AS (
    SELECT
        user_id,
        time_window,
        MAX(CASE WHEN hour_rank = 1 THEN hour_of_day END) as most_active_hour,
        COUNT(DISTINCT hour_of_day) as unique_hours,
        LISTAGG(DISTINCT hour_of_day, ',') WITHIN GROUP (ORDER BY hour_of_day) as unique_hours_list,
        MAX(CASE WHEN hour_rank = 1 THEN clips_in_hour END) as clips_in_most_active_hour,
        SUM(clips_in_hour) as total_clips,
        SUM(CASE WHEN hour_rank > 1 THEN clips_in_hour ELSE 0 END) as clips_outside_most_active_hour,
        ROUND(100.0 * MAX(CASE WHEN hour_rank = 1 THEN clips_in_hour END) / NULLIF(SUM(clips_in_hour), 0), 2) as percent_in_most_active_hour
    FROM hour_metrics
    GROUP BY user_id, time_window
),
daily_activity AS (
    SELECT
        user_id,
        COUNT(DISTINCT CASE WHEN time_window IN ('24h', '7d') THEN activity_date END) as total_active_days_7d,
        COUNT(DISTINCT activity_date) as total_active_days_30d
    FROM daily_hour_activity
    GROUP BY user_id
),
session_counts AS (
    SELECT
        user_id,
        DATE_TRUNC('day', session_start_ts_utc) as day,
        COUNT(DISTINCT anonymous_id) as daily_anon_ids,
        COUNT(DISTINCT session_id) as daily_sessions
    FROM SUNO_PROD.PROD.USER_SESSION_STATE
    WHERE session_start_ts_utc > DATEADD(day, -30, CURRENT_TIMESTAMP())
    AND P_DATE > DATEADD(day, -30, CURRENT_TIMESTAMP())
    GROUP BY 1, 2
),
session_activity AS (
    SELECT
        user_id,
        COUNT(DISTINCT anonymous_id) as anon_ids_30d,
        COUNT(DISTINCT CASE WHEN session_start_ts_utc > DATEADD(day, -7, CURRENT_TIMESTAMP()) THEN anonymous_id END) as anon_ids_7d,
        COUNT(DISTINCT CASE WHEN session_start_ts_utc > DATEADD(day, -1, CURRENT_TIMESTAMP()) THEN anonymous_id END) as anon_ids_24h,
        MAX(daily_anon_ids) as max_anon_ids_24h,
        COUNT(DISTINCT session_id) as sessions_30d,
        COUNT(DISTINCT CASE WHEN session_start_ts_utc > DATEADD(day, -7, CURRENT_TIMESTAMP()) THEN session_id END) as sessions_7d,
        COUNT(DISTINCT CASE WHEN session_start_ts_utc > DATEADD(day, -1, CURRENT_TIMESTAMP()) THEN session_id END) as sessions_24h,
        MAX(daily_sessions) as max_sessions_24h
    FROM SUNO_PROD.PROD.USER_SESSION_STATE
    LEFT JOIN session_counts USING (user_id)
    WHERE session_start_ts_utc > DATEADD(day, -30, CURRENT_TIMESTAMP())
    GROUP BY 1
),
clip_timeseries AS (
    SELECT 
        wm.user_id,
        -- 24h metrics
        MAX(CASE WHEN time_window = '24h' THEN most_active_hour END) as most_active_clip_gen_hour_24h,
        COALESCE(MAX(CASE WHEN time_window = '24h' THEN unique_hours END), 0) as total_clip_gen_hours_24h,
        COALESCE(MAX(CASE WHEN time_window = '24h' THEN unique_hours_list END), '') as unique_clip_gen_hours_24h,
        COALESCE(MAX(CASE WHEN time_window = '24h' THEN clips_in_most_active_hour END), 0) as total_clip_gens_in_most_active_hour_24h,
        COALESCE(MAX(CASE WHEN time_window = '24h' THEN clips_outside_most_active_hour END), 0) as total_clip_gens_outside_most_active_hour_24h,
        COALESCE(MAX(CASE WHEN time_window = '24h' THEN percent_in_most_active_hour END), 0) as percent_clip_gens_in_most_active_hour_24h,
        
        -- 7d metrics
        MAX(CASE WHEN time_window = '7d' THEN most_active_hour END) as most_active_clip_gen_hour_7d,
        COALESCE(MAX(CASE WHEN time_window = '7d' THEN unique_hours END), 0) as total_clip_gen_hours_7d,
        COALESCE(MAX(CASE WHEN time_window = '7d' THEN unique_hours_list END), '') as unique_clip_gen_hours_7d,
        COALESCE(MAX(CASE WHEN time_window = '7d' THEN clips_in_most_active_hour END), 0) as total_clip_gens_in_most_active_hour_7d,
        COALESCE(MAX(CASE WHEN time_window = '7d' THEN clips_outside_most_active_hour END), 0) as total_clip_gens_outside_most_active_hour_7d,
        COALESCE(MAX(CASE WHEN time_window = '7d' THEN percent_in_most_active_hour END), 0) as percent_clip_gens_in_most_active_hour_7d,
        
        -- 30d metrics
        MAX(CASE WHEN time_window = '30d' THEN most_active_hour END) as most_active_clip_gen_hour_30d,
        COALESCE(MAX(CASE WHEN time_window = '30d' THEN unique_hours END), 0) as total_clip_gen_hours_30d,
        COALESCE(MAX(CASE WHEN time_window = '30d' THEN unique_hours_list END), '') as unique_clip_gen_hours_30d,
        COALESCE(MAX(CASE WHEN time_window = '30d' THEN clips_in_most_active_hour END), 0) as total_clip_gens_in_most_active_hour_30d,
        COALESCE(MAX(CASE WHEN time_window = '30d' THEN clips_outside_most_active_hour END), 0) as total_clip_gens_outside_most_active_hour_30d,
        COALESCE(MAX(CASE WHEN time_window = '30d' THEN percent_in_most_active_hour END), 0) as percent_clip_gens_in_most_active_hour_30d,
        
        -- Daily activity metrics
        COALESCE(da.total_active_days_7d, 0) as total_active_days_7d,
        COALESCE(da.total_active_days_30d, 0) as total_active_days_30d
    FROM window_metrics wm
    LEFT JOIN daily_activity da ON wm.user_id = da.user_id
    GROUP BY wm.user_id, da.total_active_days_7d, da.total_active_days_30d
    ORDER BY total_clip_gen_hours_30d DESC
),
subscription_plan_names AS (
    SELECT
        plan_id_rds,
        stripe_price_id, 
        plan_name_rds,
        plan_level,
        renewal_interval
    FROM SUNO_PROD.PROD.DIM_SUBSCRIPTION_PLAN
    -- For some reason the Student Pro Plan is listed twice with the same plan ID
    -- I need to revist this but for now manually dedupe :/
    WHERE stripe_price_id != 'price_1QXaAlE1icI7lnWd4c9zJ5V7'
    GROUP BY 1,2,3,4,5
),
language_counts AS (
    -- Number of languages per user
    SELECT
        c.user_id,
        MAX(c.created_at) as most_recent_clip_date,
        MIN(c.created_at) as oldest_clip_date,
        COUNT(DISTINCT clip_id) as clip_count,
        LISTAGG(DISTINCT CASE WHEN lyrics_language IS NOT NULL THEN lyrics_language END, ',') 
            WITHIN GROUP (ORDER BY CASE WHEN lyrics_language IS NOT NULL THEN lyrics_language END) as unique_languages,
        COUNT(DISTINCT CASE WHEN lyrics_language IS NOT NULL THEN lyrics_language END) as language_count,
        SUM(CASE WHEN p.id IS NULL AND c.user_tier IS NOT NULL THEN 1 ELSE 0 END) as invalid_user_tier_gens
    FROM SUNO_PROD.PROD.DIM_CLIP c
    JOIN base_users bu ON c.user_id = bu.user_id
    LEFT JOIN SUNO_PROD.PROD.RDS_USAGE_PLAN p
    ON c.user_tier = p.id
    GROUP BY c.user_id
),
early_clip_metrics AS (
    -- How many clips did the user generate in the first 24 hours after joining
    SELECT 
        c.user_id,
        u.user_joined_at,
        COUNT_IF(c.created_at <= DATEADD(hour, 1, u.user_joined_at)) as clips_first_hour,
        COUNT_IF(c.created_at <= DATEADD(hour, 6, u.user_joined_at)) as clips_first_6_hours,
        COUNT_IF(c.created_at <= DATEADD(hour, 24, u.user_joined_at)) as clips_first_24_hours,
        -- Add rates per hour for comparison
        ROUND(clips_first_hour / 1.0, 2) as clips_per_hour_first_hour,
        ROUND(clips_first_6_hours / 6.0, 2) as clips_per_hour_first_6_hours,
        ROUND(clips_first_24_hours / 24.0, 2) as clips_per_hour_first_24_hours
    FROM base_users u
    JOIN SUNO_PROD.PROD.DIM_CLIP c
        ON u.user_id = c.user_id
        AND c.created_at <= DATEADD(hour, 24, u.user_joined_at)  -- Only look at first 24 hours
    GROUP BY c.user_id, u.user_joined_at
),
-- need to improve performance before re-enabling this feature
-- early_web_event_metrics AS (
--     -- What did the user do in the first 24 hours after joining
--     SELECT
--         u.user_id,
--         u.user_joined_at,
--         COUNT_IF(w.server_timestamp <= DATEADD(hour, 1, u.user_joined_at)) as web_events_first_hour,
--         COUNT_IF(w.server_timestamp <= DATEADD(hour, 6, u.user_joined_at)) as web_events_first_6_hours,
--         COUNT_IF(w.server_timestamp <= DATEADD(hour, 24, u.user_joined_at)) as web_events_first_24_hours,
--         -- Add rates per hour for comparison
--         ROUND(web_events_first_hour / 1.0, 2) as web_events_per_hour_first_hour,
--         ROUND(web_events_first_6_hours / 6.0, 2) as web_events_per_hour_first_6_hours,
--         ROUND(web_events_first_24_hours / 24.0, 2) as web_events_per_hour_first_24_hours
--     FROM base_users u
--     JOIN SUNO_PROD.PROD.WEB_USER_EVENT w
--         ON u.user_uid = w.user_uid
--         AND w.server_timestamp <= DATEADD(hour, 24, u.user_joined_at)  -- Only look at first 24 hours
--     GROUP BY u.user_id, u.user_joined_at
-- ),
token_counts AS (
    -- Is the user missing browser tokens?
    SELECT 
        a.user_id,
        SUM(CASE WHEN a.properties:is_token_missing::boolean = TRUE THEN 1 ELSE 0 END) as token_missing_count,
        SUM(CASE WHEN a.properties:is_token_missing::boolean = FALSE THEN 1 ELSE 0 END) as token_present_count
    FROM SUNO_PROD.PROD.BK_NONREALTIME_EVENT a
    JOIN base_users bu ON a.user_id = bu.user_id,
         date_bounds d  -- Added date_bounds reference
    WHERE a.name = 'BrowserTokenCheck'
        AND a.properties:is_mobile IS NOT NULL
        AND a.p_date > d.lookback_start
    GROUP BY 1
),
ip_counts AS (
    -- The number of unique IPs for each user
    SELECT
        e.user_id,
        COUNT(DISTINCT CASE WHEN e.timestamp <= DATEADD(hour, 1, u.user_joined_at) 
            THEN properties:client_ip::string END) as ips_first_hour,
        COUNT(DISTINCT CASE WHEN e.timestamp <= DATEADD(hour, 6, u.user_joined_at) 
            THEN properties:client_ip::string END) as ips_first_6_hours,
        COUNT(DISTINCT CASE WHEN e.timestamp <= DATEADD(hour, 24, u.user_joined_at) 
            THEN properties:client_ip::string END) as ips_first_24_hours,
        COUNT(DISTINCT properties:client_ip::string) as ip_count,
        LISTAGG(DISTINCT properties:client_ip::string, ',') 
            WITHIN GROUP (ORDER BY properties:client_ip::string) as unique_ips
    FROM SUNO_PROD.PROD.BK_REALTIME_EVENT e
    JOIN base_users u ON e.user_id = u.user_id
    WHERE name = 'Clip_Generation_Request'
        AND properties:env::string = 'prod'
    GROUP BY e.user_id
),
latest_hpcatcha_scores AS (
    -- Third-party captcha scoring
    SELECT 
        h.user_id,
        h.score,
        h.ip_address,
        h.created_at
    FROM SUNO_PROD.PROD.RDS_USER_HCAPTCHA_HISTORY h
    JOIN base_users bu ON h.user_id = bu.user_id
    WHERE P_DATE IN (
        SELECT MAX(P_DATE)
        FROM SUNO_PROD.PROD.RDS_USER_HCAPTCHA_HISTORY
    )
    QUALIFY ROW_NUMBER() OVER (PARTITION BY h.user_id ORDER BY created_at DESC) = 1
),
clip_metrics AS (
    -- Behavioral metrics for clips
    SELECT
        bu.user_id,
        bu.user_joined_at,
        COUNT(DISTINCT(cm.clip_id)) as clips_with_metrics_count,
        COUNT_IF(cm.PLAY_CNT > 0) as play_count,
        COUNT_IF(cm.PLAY_CNT = 0) as unplay_count,
        ROUND(COUNT_IF(cm.PLAY_CNT = 0) / NULLIF(COUNT(DISTINCT(clip_id)), 0), 2) as unplayed_percent
    FROM base_users bu
    LEFT JOIN SUNO_PROD.PROD.CLIP_INITIAL_METRICS cm 
        ON bu.user_id = cm.user_id
        AND cm.p_date > bu.user_joined_at
        AND cm.CLIP_CREATED_AT < (SELECT recent_cutoff FROM date_bounds)
    LEFT JOIN SUNO_PROD.PROD.RDS_DISCORD_INFO di
        ON bu.user_id = di.user_id
        AND di.p_date > DATE(bu.user_joined_at)
    GROUP BY bu.user_id, bu.user_joined_at
),
cancellations AS (
    -- Payment history: charge cancellations
    SELECT
        s.id as subscription_id,
        s.customer_id,
        s.start_date,
        s.canceled_at as canceled_date,
        s.cancel_at as cancel_date,
        DATEDIFF(hour, s.start_date, s.canceled_at) as hours_until_cancel
    FROM GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.SUBSCRIPTIONS s
    WHERE cancel_at IS NOT NULL
        AND start_date IS NOT NULL
        AND canceled_at IS NOT NULL
        AND cancellation_details_reason = 'cancellation_requested'
),
disputes AS (
    -- Payment history: disputes
    SELECT COUNT(*) as total_disputes, charge_id
    FROM GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.DISPUTES
    GROUP BY 2 ORDER BY 1 DESC
),
charge_counts AS (
    -- Payment history: block charges, authorizations, geo info, etc
    SELECT 
        customer_id,
        COUNT(*) as total_charges,
        COALESCE(MAX(d.total_disputes), 0) as total_disputes,
        COUNT_IF(refunded) as refund_count,
        COUNT(DISTINCT(payment_method_id)) AS unique_payment_methods,
        COUNT(DISTINCT(card_fingerprint)) AS unique_card_fingerprints,
        COUNT(DISTINCT(card_address_zip)) AS unique_card_address_zip,
        COUNT(DISTINCT(currency)) AS unique_currencies,
        LISTAGG(DISTINCT currency, ', ') as currencies,
        SUM(amount_refunded) AS refund_total,
        COUNT_IF(outcome_type = 'authorized') as authorized_charge_count,
        COUNT_IF(outcome_type = 'manual_review') as manual_review_charge_count,
        COUNT_IF(outcome_type = 'issuer_declined') as issuer_declined_charge_count,
        COUNT_IF(outcome_type = 'invalid') as invalid_charge_count,
        COUNT_IF(outcome_type = 'blocked') as blocked_charge_count,
        COUNT_IF(status = 'failed') as failed_charge_count,
        COUNT_IF(status = 'succeeded') as successful_charge_count,
        LISTAGG(DISTINCT card_country, ', ') as card_countries,
        COUNT(DISTINCT card_country) as card_countries_count,
        LISTAGG(DISTINCT CASE WHEN status = 'succeeded' THEN card_country END, ', ') as authorized_countries,
        COUNT(DISTINCT CASE WHEN status = 'succeeded' THEN card_country END) as authorized_countries_count,
        LISTAGG(DISTINCT CASE WHEN status = 'failed' THEN card_country END, ', ') as unauthorized_countries,
        COUNT(DISTINCT CASE WHEN status = 'failed' THEN card_country END) as unauthorized_countries_count
    FROM GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.CHARGES c
    LEFT JOIN disputes d
    ON c.id = d.charge_id
    GROUP BY 1
),
charge_history AS (
    -- Calculate authorization / decline percents based on the payment history above
    SELECT 
        c.*,
        ROUND(100.0 * authorized_charge_count / total_charges, 2) as percent_authorized,
        ROUND(100.0 * (issuer_declined_charge_count + invalid_charge_count + blocked_charge_count) / total_charges, 2) as percent_declined
    FROM charge_counts c
    WHERE total_charges > 0
),
subscription_details AS (
    -- Subscription details and credit balances
    SELECT
        d.user_id,
        d.stripe_customer_id,
        d.subscription_cancel_at,
        d.subscription_anchor,
        d.subscription_status,
        d.extra_credits_balance,
        d.extra_credits_used,
        c.*,
        c2.*
    FROM charge_history c
    JOIN SUNO_PROD.PROD.RDS_DISCORD_INFO d
    ON c.customer_id = d.stripe_customer_id
    LEFT JOIN cancellations c2
    ON c2.subscription_id = d.stripe_subscription_id
    AND c2.customer_id = d.stripe_customer_id
),
plan_history AS (
    -- History of which Suno plans each user paid for
    SELECT
        c.customer_id,
        s.start_date as subscription_start_date,
        s.id as subscription_id,
        sp.plan_name_rds as plan,
        c.created as charge_date,
        c.id as charge_id,
        COALESCE(d.charge_id IS NOT NULL, FALSE) as is_disputed,
        sp.plan_level,
        sp.renewal_interval,
        ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY c.created DESC) as rn
    FROM GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.CHARGES c
    JOIN GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.INVOICES i 
        ON c.invoice_id = i.id
    JOIN GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.SUBSCRIPTIONS s 
        ON i.subscription_id = s.id
    JOIN subscription_plan_names sp 
        ON s.plan_id = sp.stripe_price_id
    LEFT JOIN GSWUDFY_STRIPE_US_EAST_2_SHARE_CCNTDMK_SUNO.STRIPE.DISPUTES d
        ON c.id = d.charge_id
    WHERE c.status = 'succeeded'
),
most_recent_plan_history AS (
    -- Most recent plan and charge information. Useful for quickly identifying
    -- potential charge IDs to refund for users with certain bot features
    SELECT 
        customer_id,
        subscription_id,
        MAX(subscription_start_date) as subscription_start_date,
        -- Most recent charge (rn = 1)
        MAX(CASE WHEN rn = 1 THEN plan END) as most_recent_plan,
        MAX(CASE WHEN rn = 1 THEN charge_date END) as most_recent_charge_date,
        MAX(CASE WHEN rn = 1 THEN charge_id END) as most_recent_charge_id,
        MAX(CASE WHEN rn = 1 THEN is_disputed END) as most_recent_charge_is_disputed,
        MAX(CASE WHEN rn = 1 THEN plan_level END) as most_recent_plan_level,
        MAX(CASE WHEN rn = 1 THEN renewal_interval END) as most_recent_renewal_interval
    FROM plan_history
    WHERE rn <= 1
    GROUP BY 1,2
    ORDER BY most_recent_charge_date DESC
),
copycats AS (
    SELECT DISTINCT(user_id) as user_id
    FROM SUNO_PROD.PROD.RDS_MODERATION_USERBOTFEATURES
    WHERE feature = 'copycat'
),
-- IMPORTANT: these domains are auto-synced to the bot model and Radar lists.
-- DO NOT INCLUDE ANY DOMAINS THAT WE SHOULD NOT TAKE ACTION ON.
block_domains AS (
    SELECT domain FROM (VALUES
        -- New additions
        ('addisonps.me'),
        ('aizf.uk'),
        ('aizle.uk'),
        ('anymorejk.com'),
        ('a.resultedi.com'),
        ('baofule.org'),
        ('bkle.uk'),
        ('brndm.life'),
        ('butingquan.com'),
        ('chaoniua.org'),
        ('chenjiakop.com'),
        ('chuiniubi.xyz'),
        ('commentsgs.com'),
        ('cotn.uk'),
        ('dabaodingzhi.com'),
        ('dje.go.kr'),
        ('docomo.ne.jp'),
        ('edu.sje.go.kr'),
        ('europhean.com'),
        ('everydaysk.org'),
        ('facaiao.org'),
        ('gclass.ice.go.kr'),
        ('g.cnees.kr'),
        ('gge.goe.go.kr'),
        ('ggm.goe.go.kr'),
        ('g.jbedu.kr'),
        ('gmaiillla.com'),
        ('gmaiilv.com'),
        ('gmaiils.org'),
        ('gokma.org'),
        ('goooglell.com'),
        ('googlb.org'),
        ('googlaa.org'),
        ('go.kr'),
        ('grandpabv.com'),
        ('guri.hs.kr'),
        ('haoshia.com'),
        ('haowena.art'),
        ('haoyuno.org'),
        ('hatap.ms.kr'),
        ('hdernm.com'),
        ('hendiao.org'),
        ('hongyunyj.com'),
        ('jixiangry.com'),
        ('joanjiaeuj.org'),
        ('junmoa.org'),
        ('keshiii.com'),
        ('lianzhanao.com'),
        ('lingeringp.com'),
        ('listagrupos.com'),
        ('kyunghwa.ms.kr'),
        ('louyouiu.com'),
        ('mailinfo24.com'),
        ('m2.ygjps.tyc.edu.tw'),
        ('maeyang.ms.kr'),
        ('moe-dl.edu.my'),
        ('m2.ygjps.tyc.edu.tw'),
        ('ncps.cc'),
        ('okl.junmoa.org'),
        ('orange.fr'),
        ('outrapeousp.com'),
        ('pinog.xyz'),
        ('pingooo.life'),
        ('rehearsalk.com'),
        ('rndrm.com'),
        ('rpilosj.com'),
        ('saha.ms.kr'),
        ('samsung.ms.kr'),
        ('sc.gyo6.net'),
        ('shinshonw.com'),
        ('songsendh.com'),
        ('sonline20.sen.go.kr'),
        ('shotgunsde.com'),
        ('taoxuent.com'),
        ('titk.uk'),
        ('qingtianle.us'),
        ('usernamek.org'),
        ('virtunel.com'),
        ('xianyuajp.org'),
        ('xintaitong.com'),
        ('xbhwa.com'),
        ('yezhureshuffle.com'),
        ('yipiji.com'),
        ('youngestsd.com'),
        ('yourselvoes.com'),
        ('yuyuhuahua.life'),
        ('zhenwena.cc'),
        ('zodiacalk.com')
    ) AS d(domain) -- IMPORTANT: See the warning at the top of this CTE
),
blocked_in_clerk_domains AS (
    SELECT domain FROM (VALUES
        ('bxd.hongyunyj.com'),
        ('znb.lianzhanao.com'),
        ('qwd.gmaiilv.com'),
        ('yzk.gmaiilv.com'),
        ('chaoniua.org'),
        ('plo.chaoniua.org'),
        ('fql.jixiangry.com'),
        ('haoyuno.org'),
        ('hendiao.org'),
        ('junmoa.org'),
        ('outlook.de'),
        ('outlook.in'),
        ('outlook.it'),
        ('outlook.jp'),
        ('outlook.kr'),
        ('outlook.ph'),
        ('outlook.sg'),
        ('zjfzjzd.org')
    ) AS d(domain)
),
allow_domains AS (
    SELECT domain FROM (VALUES
        ('gmail.com'),
        ('googlemail.com'),
        ('mail.ru'),
        ('icloud.com'),
        ('mail.ru'),
        ('privaterelay.appleid.com'),
        ('protonmail.com'),
        ('proton.me'),
        ('qq.com'),
        ('yahoo.co.jp'),
        ('yahoo.com')
    ) AS d(domain)
),
watch_domains AS (
    SELECT domain FROM (VALUES
        ('hotmail.com'),
        ('hotmail.de'),
        --('hotmail.fr'),
        ('live.com'),
        ('outlook.com'),
        ('outlook.de'),
        ('outlook.in'),
        ('outlook.it'),
        ('outlook.jp'),
        ('outlook.kr'),
        ('outlook.ph'),
        ('outlook.sg')
    ) AS d(domain)
),
endpoint_cutover_traffic AS (
    SELECT
        user_id,
        COUNT_IF(endpoint = 'v2' AND is_mobile=FALSE) as suspicious_endpoint_cutover_requests
    FROM PC_HEX_DB.PUBLIC.V2_CUTOVER_MAR_13_2025
    GROUP BY 1
),
-- start event stats
hourly_events AS (
    SELECT
        user_id,
        name as event_name,
        DATE_TRUNC('HOUR', timestamp) as event_hour,
        COUNT(*) as events_in_hour,
        COUNT(DISTINCT DATE(timestamp)) as unique_days,
        -- Track events during credit reset period (0-3 UTC)
        SUM(CASE WHEN EXTRACT(HOUR FROM timestamp) BETWEEN 0 AND 3 THEN 1 ELSE 0 END) as reset_period_events
    FROM SUNO_PROD.PROD.BK_NONREALTIME_EVENT
    WHERE p_date > DATEADD(day, -30, CURRENT_TIMESTAMP())
    AND name IN (
        'ClipLike',
        'ClipDownload',
        'ClipCommentCreate',
        'ClipCommentMention',
        'ClipCommentLikeReact',
        'ProfileFollow'
    )
    GROUP BY 1, 2, 3
    UNION ALL
    SELECT
        user_id,
        name as event_name,
        DATE_TRUNC('HOUR', timestamp) as event_hour,
        COUNT(*) as events_in_hour,
        COUNT(DISTINCT DATE(timestamp)) as unique_days,
        -- Track events during credit reset period (0-3 UTC)
        SUM(CASE WHEN EXTRACT(HOUR FROM timestamp) BETWEEN 0 AND 3 THEN 1 ELSE 0 END) as reset_period_events
    FROM SUNO_PROD.PROD.BK_REALTIME_EVENT
    WHERE p_date > DATEADD(day, -30, CURRENT_TIMESTAMP())
    AND name IN (
        'Clip_Generation_Request'
    )
    GROUP BY 1, 2, 3
    UNION ALL
    SELECT
        user_id,
        'ClipPlay' as event_name,
        DATE_TRUNC('HOUR', timestamp) as event_hour,
        COUNT(*) as events_in_hour,
        COUNT(DISTINCT DATE(timestamp)) as unique_days,
        SUM(CASE WHEN EXTRACT(HOUR FROM timestamp) BETWEEN 0 AND 3 THEN 1 ELSE 0 END) as reset_period_events
    FROM SUNO_PROD.PROD.BK_REALTIME_EVENT
    WHERE p_date > DATEADD(day, -30, CURRENT_TIMESTAMP())
    GROUP BY 1,2,3
),
daily_events AS (
    -- Get per-day stats for min/max/avg calculations
    SELECT
        user_id,
        event_name,
        DATE(event_hour) as event_date,
        SUM(events_in_hour) as events_per_day,
        SUM(reset_period_events) as reset_period_events_per_day
    FROM hourly_events
    GROUP BY 1, 2, 3
),
time_window_stats AS (
    SELECT
        user_id,
        event_name,
        -- Event counts per window
        COALESCE(SUM(CASE WHEN event_hour > DATEADD(day, -1, CURRENT_TIMESTAMP()) THEN events_in_hour END), 0) as events_24h,
        COALESCE(SUM(CASE WHEN event_hour > DATEADD(day, -7, CURRENT_TIMESTAMP()) THEN events_in_hour END), 0) as events_7d,
        COALESCE(SUM(CASE WHEN event_hour > DATEADD(day, -30, CURRENT_TIMESTAMP()) THEN events_in_hour END), 0) as events_30d,
        -- Events during reset period (0-3 UTC)
        COALESCE(SUM(CASE WHEN event_hour > DATEADD(day, -1, CURRENT_TIMESTAMP()) THEN reset_period_events END), 0) as reset_period_events_24h,
        COALESCE(SUM(CASE WHEN event_hour > DATEADD(day, -7, CURRENT_TIMESTAMP()) THEN reset_period_events END), 0) as reset_period_events_7d,
        COALESCE(SUM(CASE WHEN event_hour > DATEADD(day, -30, CURRENT_TIMESTAMP()) THEN reset_period_events END), 0) as reset_period_events_30d,
        -- Active days per window
        COUNT(DISTINCT CASE WHEN event_hour > DATEADD(day, -1, CURRENT_TIMESTAMP()) THEN DATE(event_hour) END) as active_days_24h,
        COUNT(DISTINCT CASE WHEN event_hour > DATEADD(day, -7, CURRENT_TIMESTAMP()) THEN DATE(event_hour) END) as active_days_7d,
        COUNT(DISTINCT CASE WHEN event_hour > DATEADD(day, -30, CURRENT_TIMESTAMP()) THEN DATE(event_hour) END) as active_days_30d,
        MAX(event_hour) as last_active_hour
    FROM hourly_events
    GROUP BY 1, 2
),
agg_events AS (
    SELECT 
        t.*,
        EXTRACT(HOUR FROM last_active_hour) as most_recent_hour,
        -- Daily stats
        MIN(d.events_per_day) as min_daily_events,
        MAX(d.events_per_day) as max_daily_events,
        AVG(d.events_per_day) as avg_daily_events,
        -- Reset period stats
        MIN(d.reset_period_events_per_day) as min_daily_reset_period_events,
        MAX(d.reset_period_events_per_day) as max_daily_reset_period_events,
        AVG(d.reset_period_events_per_day) as avg_daily_reset_period_events,
        -- Percentage of events during reset period
        ROUND(100.0 * reset_period_events_24h / NULLIF(events_24h, 0), 2) as reset_period_event_percent_24h,
        ROUND(100.0 * reset_period_events_7d / NULLIF(events_7d, 0), 2) as reset_period_event_percent_7d,
        ROUND(100.0 * reset_period_events_30d / NULLIF(events_30d, 0), 2) as reset_period_event_percent_30d
    FROM time_window_stats t
    LEFT JOIN daily_events d 
        ON t.user_id = d.user_id 
        AND t.event_name = d.event_name
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13
),
events_flattened AS (
    SELECT
        user_id,
        -- Clip Plays
        SUM(CASE WHEN event_name = 'ClipPlay' THEN events_24h ELSE 0 END) as clip_plays_24h,
        SUM(CASE WHEN event_name = 'ClipPlay' THEN events_7d ELSE 0 END) as clip_plays_7d,
        SUM(CASE WHEN event_name = 'ClipPlay' THEN events_30d ELSE 0 END) as clip_plays_30d,
        SUM(CASE WHEN event_name = 'ClipPlay' THEN active_days_7d ELSE 0 END) as clip_plays_active_days_7d,
        SUM(CASE WHEN event_name = 'ClipPlay' THEN reset_period_events_7d ELSE 0 END) as clip_plays_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ClipPlay' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ClipPlay' THEN events_7d ELSE 0 END), 0)
        , 2) as clip_plays_reset_period_percent_7d,
        
        -- Clip Generations
        SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN events_24h ELSE 0 END) as clip_gens_24h,
        SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN events_7d ELSE 0 END) as clip_gens_7d,
        SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN events_30d ELSE 0 END) as clip_gens_30d,
        SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN active_days_7d ELSE 0 END) as clip_gens_active_days_7d,
        SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN reset_period_events_7d ELSE 0 END) as clip_gens_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'Clip_Generation_Request' THEN events_7d ELSE 0 END), 0)
        , 2) as clip_gens_reset_period_percent_7d,
        
        -- Clip Likes
        SUM(CASE WHEN event_name = 'ClipLike' THEN events_24h ELSE 0 END) as clip_likes_24h,
        SUM(CASE WHEN event_name = 'ClipLike' THEN events_7d ELSE 0 END) as clip_likes_7d,
        SUM(CASE WHEN event_name = 'ClipLike' THEN events_30d ELSE 0 END) as clip_likes_30d,
        SUM(CASE WHEN event_name = 'ClipLike' THEN active_days_7d ELSE 0 END) as clip_likes_active_days_7d,
        SUM(CASE WHEN event_name = 'ClipLike' THEN reset_period_events_7d ELSE 0 END) as clip_likes_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ClipLike' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ClipLike' THEN events_7d ELSE 0 END), 0)
        , 2) as clip_likes_reset_period_percent_7d,
        
        -- Clip Downloads
        SUM(CASE WHEN event_name = 'ClipDownload' THEN events_24h ELSE 0 END) as clip_downloads_24h,
        SUM(CASE WHEN event_name = 'ClipDownload' THEN events_7d ELSE 0 END) as clip_downloads_7d,
        SUM(CASE WHEN event_name = 'ClipDownload' THEN events_30d ELSE 0 END) as clip_downloads_30d,
        SUM(CASE WHEN event_name = 'ClipDownload' THEN active_days_7d ELSE 0 END) as clip_downloads_active_days_7d,
        SUM(CASE WHEN event_name = 'ClipDownload' THEN reset_period_events_7d ELSE 0 END) as clip_downloads_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ClipDownload' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ClipDownload' THEN events_7d ELSE 0 END), 0)
        , 2) as clip_downloads_reset_period_percent_7d,
        
        -- Comments
        SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN events_24h ELSE 0 END) as comments_24h,
        SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN events_7d ELSE 0 END) as comments_7d,
        SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN events_30d ELSE 0 END) as comments_30d,
        SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN active_days_7d ELSE 0 END) as comments_active_days_7d,
        SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN reset_period_events_7d ELSE 0 END) as comments_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ClipCommentCreate' THEN events_7d ELSE 0 END), 0)
        , 2) as comments_reset_period_percent_7d,

        -- Profile Follows
        SUM(CASE WHEN event_name = 'ProfileFollow' THEN events_24h ELSE 0 END) as follows_24h,
        SUM(CASE WHEN event_name = 'ProfileFollow' THEN events_7d ELSE 0 END) as follows_7d,
        SUM(CASE WHEN event_name = 'ProfileFollow' THEN events_30d ELSE 0 END) as follows_30d,
        SUM(CASE WHEN event_name = 'ProfileFollow' THEN active_days_7d ELSE 0 END) as follows_active_days_7d,
        SUM(CASE WHEN event_name = 'ProfileFollow' THEN reset_period_events_7d ELSE 0 END) as follows_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ProfileFollow' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ProfileFollow' THEN events_7d ELSE 0 END), 0)
        , 2) as follows_reset_period_percent_7d,
    
        -- Comment Mentions
        SUM(CASE WHEN event_name = 'ClipCommentMention' THEN events_24h ELSE 0 END) as mentions_24h,
        SUM(CASE WHEN event_name = 'ClipCommentMention' THEN events_7d ELSE 0 END) as mentions_7d,
        SUM(CASE WHEN event_name = 'ClipCommentMention' THEN events_30d ELSE 0 END) as mentions_30d,
        SUM(CASE WHEN event_name = 'ClipCommentMention' THEN active_days_7d ELSE 0 END) as mentions_active_days_7d,
        SUM(CASE WHEN event_name = 'ClipCommentMention' THEN reset_period_events_7d ELSE 0 END) as mentions_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ClipCommentMention' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ClipCommentMention' THEN events_7d ELSE 0 END), 0)
        , 2) as mentions_reset_period_percent_7d,
    
        -- Comment Reactions
        SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN events_24h ELSE 0 END) as comment_reactions_24h,
        SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN events_7d ELSE 0 END) as comment_reactions_7d,
        SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN events_30d ELSE 0 END) as comment_reactions_30d,
        SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN active_days_7d ELSE 0 END) as comment_reactions_active_days_7d,
        SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN reset_period_events_7d ELSE 0 END) as comment_reactions_reset_period_events_7d,
        ROUND(100.0 * 
            SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN reset_period_events_7d ELSE 0 END) /
            NULLIF(SUM(CASE WHEN event_name = 'ClipCommentLikeReact' THEN events_7d ELSE 0 END), 0)
        , 2) as comment_reactions_reset_period_percent_7d,

    
        -- Most recent activity
        MAX(last_active_hour) as last_active_time
    FROM agg_events
    GROUP BY 1
    ORDER BY clip_gens_24h DESC
),
-- end event stats
-- carrier
mobile_carrier AS (
    SELECT
        identity as clerk_id,
        phone_intel:carrier::varchar as carrier
    FROM SUNO_PROD.PROD.HONEYPOT_EVENTS
    WHERE event_name = 'User Created'
    AND phone_intel IS NOT NULL 
    AND identity IS NOT NULL
    GROUP BY 1,2
),
-- end carrier
bot_signals AS (
    -- The final projection
    SELECT
        CURRENT_TIMESTAMP as created_at,
        cm.*,
        COALESCE(lc.invalid_user_tier_gens, 0) as clip_metrics_invalid_user_tier_gens,
        COALESCE(lc.clip_count - cm.clips_with_metrics_count, 0) as clip_metrics_missing_count,
        COALESCE(ecm.clips_first_hour, 0) as clips_first_hour,
        COALESCE(ecm.clips_first_6_hours, 0) as clips_first_6_hours,
        COALESCE(ecm.clips_first_24_hours, 0) as clips_first_24_hours,
        COALESCE(ecm.clips_per_hour_first_hour, 0) as clips_per_hour_first_hour,
        COALESCE(ecm.clips_per_hour_first_6_hours, 0) as clips_per_hour_first_6_hours,
        COALESCE(ecm.clips_per_hour_first_24_hours, 0) as clips_per_hour_first_24_hours,


        -- useful feature but disabled for performance reasons. will revisit
        -- COALESCE(we.web_events_first_hour, 0) as web_events_first_hour,
        -- COALESCE(we.web_events_first_6_hours, 0) as web_events_first_6_hours,
        -- COALESCE(we.web_events_first_24_hours, 0) as web_events_first_24_hours,
        -- COALESCE(we.web_events_per_hour_first_hour, 0) as web_events_per_hour_first_hour,
        -- COALESCE(we.web_events_per_hour_first_6_hours, 0) as web_events_per_hour_first_6_hours,
        -- COALESCE(we.web_events_per_hour_first_24_hours, 0) as web_events_per_hour_first_24_hours,

        hc.score as latest_hcaptcha_score,
        hc.ip_address as latest_hcaptcha_ip_address,
        COALESCE(lc.clip_count, 0) as clip_count,
        lc.oldest_clip_date,
        lc.most_recent_clip_date,
        DATEDIFF('second', bu.user_joined_at, lc.oldest_clip_date) as seconds_to_first_clip,
        DATEDIFF('minute', bu.user_joined_at, lc.oldest_clip_date) as minutes_to_first_clip,
        GREATEST(lc.most_recent_clip_date, bu.user_joined_at) as most_recent_activity_date,
        ic.ip_count,
        ic.unique_ips,
        ic.ips_first_hour,
        ic.ips_first_6_hours,
        ic.ips_first_24_hours,
        lc.unique_languages,
        lc.language_count,
        h.is_dataset_healthy as are_clip_metrics_healthy,
        hm.percent_missing_country < 60 as is_country_metric_healthy,
        bu.user_uid,
        bu.clerk_id,
        bu.phone_number,
        bu.is_staff,
        bu.recent_country,
        bu.signup_country,
        bu.recent_city,
        bu.registered_platform,
        bu.email,
        bu.email_username,
        -- email suspicion calculation
        CASE 
            WHEN bu.email_username IS NULL OR LENGTH(bu.email_username) < 6 THEN 0
            WHEN bu.email_domain = 'privaterelay.appleid.com' THEN 0
            ELSE
                CASE
                    -- First check for obvious bot patterns (improved regex)
                    WHEN REGEXP_LIKE(bu.email_username, '.*[^aeiou0-9\\.\\-_]{6,}.*', 'i') THEN 1.0
                    -- Check for random-looking patterns
                    WHEN REGEXP_LIKE(bu.email_username, '[a-z][0-9][a-z][0-9]', 'i') THEN 1.0  -- alternating letters/numbers
                    WHEN REGEXP_LIKE(bu.email_username, '[a-z]+[0-9]{2,3}$', 'i') THEN 0.2  -- name+short numbers
                    -- Check for excessive consecutive consonants (but allow common separators)
                    WHEN REGEXP_LIKE(bu.email_username, '[bcdfghjklmnpqrstvwxyz]{5,}', 'i') THEN 0.8
                    -- Check for random character sequences
                    WHEN REGEXP_LIKE(bu.email_username, '[a-z]{2}[0-9]{2}[a-z]{2}[0-9]{2}', 'i') THEN 0.9
                    ELSE LEAST(1.0,
                        (
                            (LEAST(LENGTH(bu.email_username), 20) / 20.0) * 0.3 +
                            (CASE 
                                WHEN bu.email_vowel_percent = 0 THEN 0.9
                                WHEN bu.email_vowel_percent < 0.15 THEN 0.8
                                ELSE 0.7 * (1 - LEAST(bu.email_vowel_percent, 0.4) / 0.4)
                            END) +
                            -- Add penalty for outlook.* domains
                            CASE
                                WHEN REGEXP_LIKE(bu.email_domain, 'outlook\\..*$') THEN 0.2
                                ELSE 0
                            END +
                            -- Add penalty for excessive periods or special characters
                            CASE
                                WHEN REGEXP_COUNT(bu.email_username, '\\.') > 2 THEN 0.1
                                WHEN REGEXP_COUNT(bu.email_username, '[^a-zA-Z0-9\\.\\-_]') > 1 THEN 0.2
                                ELSE 0
                            END
                        )
                    )
                END
        END as email_suspicion,
        bu.email_domain,
        -- Match any subdomain in the clear list
        EXISTS (
            SELECT 1 
            FROM allow_domains 
            WHERE email_domain LIKE '%.' || domain 
               OR email_domain = domain
        ) as email_domain_in_allow_list,
        -- Match any subdomain of block domains
        EXISTS (
            SELECT 1 
            FROM block_domains 
            WHERE email_domain LIKE '%.' || domain 
               OR email_domain = domain
        ) as email_domain_in_block_list,
        -- Match any subdomain of watch domains
        EXISTS (
            SELECT 1 
            FROM watch_domains 
            WHERE email_domain LIKE '%.' || domain 
               OR email_domain = domain
        ) AS email_domain_in_watch_list,
        bcd.domain IS NOT NULL as email_domain_in_clerk_block_list,
        bu.email_username_length,
        bu.email_vowel_percent,
        bj.status as bot_jail_status,
        CASE
            WHEN cm.clips_with_metrics_count > 50 AND cm.unplayed_percent > 0.9 THEN 'bot'
            WHEN cm.clips_with_metrics_count > 10 AND cm.unplayed_percent = 1 THEN 'bot'
            WHEN cm.clips_with_metrics_count > 20 AND cm.unplayed_percent > 0.9 THEN 'suspicious'
            ELSE 'ok'
        END as gen_flag,
        tc.token_missing_count,
        tc.token_present_count,
        ch.total_charges,
        ch.total_disputes,
        ch.refund_count,
        ch.unique_payment_methods,
        ch.unique_card_address_zip,
        ch.unique_currencies,
        ch.currencies,
        ch.refund_total,
        ch.authorized_charge_count,
        ch.manual_review_charge_count,
        ch.issuer_declined_charge_count,
        ch.invalid_charge_count,
        ch.blocked_charge_count,
        ch.failed_charge_count,
        ch.successful_charge_count,
        ch.card_countries,
        ch.card_countries_count,
        ch.authorized_countries,
        ch.authorized_countries_count,
        ch.unauthorized_countries,
        ch.unauthorized_countries_count,
        ch.percent_authorized,
        ch.percent_declined,
        -- ch.subscription_id,
        ch.stripe_customer_id,
        -- ch.start_date as subscription_start_date,
        ch.canceled_date as subscription_canceled_date,
        ch.cancel_date as subcription_cancel_date,
        ch.subscription_cancel_at,
        ch.subscription_anchor,
        ch.hours_until_cancel,
        ch.subscription_status,
        ch.extra_credits_balance,
        ch.extra_credits_used,
        mp.subscription_id,
        mp.subscription_start_date,
        mp.most_recent_plan,
        mp.most_recent_charge_date,
        mp.most_recent_charge_id,
        mp.most_recent_charge_is_disputed,
        mp.most_recent_plan_level,
        mp.most_recent_renewal_interval,
        cc.user_id IS NOT NULL AS is_copycat,
        ec.user_id IS NOT NULL as active_during_endpoint_cutover,
        COALESCE(ec.suspicious_endpoint_cutover_requests, 0) as suspicious_endpoint_cutover_requests,
        us.total_followers,
        us.total_following,
        us.total_clips_public,
        us.total_likes,
        us.total_playlists,
        us.longest_login_streak,
        us.longest_creation_streak,

        mobc.carrier as mobile_carrier,

        --  event metrics
        -- Clip Plays
        ef.clip_plays_24h,
        ef.clip_plays_7d,
        ef.clip_plays_30d,
        ef.clip_plays_active_days_7d,
        ef.clip_plays_reset_period_events_7d,
        ef.clip_plays_reset_period_percent_7d,
        
        -- Clip Generations
        ef.clip_gens_24h,
        ef.clip_gens_7d,
        ef.clip_gens_30d,
        ef.clip_gens_active_days_7d,
        ef.clip_gens_reset_period_events_7d,
        ef.clip_gens_reset_period_percent_7d,
        
        -- Clip Likes
        ef.clip_likes_24h,
        ef.clip_likes_7d,
        ef.clip_likes_30d,
        ef.clip_likes_active_days_7d,
        ef.clip_likes_reset_period_events_7d,
        ef.clip_likes_reset_period_percent_7d,
        
        -- Clip Downloads
        ef.clip_downloads_24h,
        ef.clip_downloads_7d,
        ef.clip_downloads_30d,
        ef.clip_downloads_active_days_7d,
        ef.clip_downloads_reset_period_events_7d,
        ef.clip_downloads_reset_period_percent_7d,
        
        -- Comments
        ef.comments_24h,
        ef.comments_7d,
        ef.comments_30d,
        ef.comments_active_days_7d,
        ef.comments_reset_period_events_7d,
        ef.comments_reset_period_percent_7d,
        
        -- Profile Follows
        ef.follows_24h,
        ef.follows_7d,
        ef.follows_30d,
        ef.follows_active_days_7d,
        ef.follows_reset_period_events_7d,
        ef.follows_reset_period_percent_7d,
        
        -- Comment Mentions
        ef.mentions_24h,
        ef.mentions_7d,
        ef.mentions_30d,
        ef.mentions_active_days_7d,
        ef.mentions_reset_period_events_7d,
        ef.mentions_reset_period_percent_7d,
        
        -- Comment Reactions
        ef.comment_reactions_24h,
        ef.comment_reactions_7d,
        ef.comment_reactions_30d,
        ef.comment_reactions_active_days_7d,
        ef.comment_reactions_reset_period_events_7d,
        ef.comment_reactions_reset_period_percent_7d,

        -- end event metrics
    
        -- 24 hour clip gen metrics
        ct.most_active_clip_gen_hour_24h,
        ct.total_clip_gen_hours_24h,
        ct.unique_clip_gen_hours_24h,
        ct.total_clip_gens_in_most_active_hour_24h,
        ct.total_clip_gens_outside_most_active_hour_24h,
        ct.percent_clip_gens_in_most_active_hour_24h,
        
        -- 7d metrics
        ct.most_active_clip_gen_hour_7d,
        ct.total_clip_gen_hours_7d,
        ct.unique_clip_gen_hours_7d,
        ct.total_clip_gens_in_most_active_hour_7d,
        ct.total_clip_gens_outside_most_active_hour_7d,
        ct.percent_clip_gens_in_most_active_hour_7d,
        ct.total_active_days_7d,
        
        -- 30d metrics
        ct.most_active_clip_gen_hour_30d,
        ct.total_clip_gen_hours_30d,
        ct.unique_clip_gen_hours_30d,
        ct.total_clip_gens_in_most_active_hour_30d,
        ct.total_clip_gens_outside_most_active_hour_30d,
        ct.percent_clip_gens_in_most_active_hour_30d,
        ct.total_active_days_30d,

        -- session activity metrics
        sa.anon_ids_30d,
        sa.anon_ids_7d,
        sa.anon_ids_24h,
        sa.max_anon_ids_24h,
        sa.sessions_30d,
        sa.sessions_7d,
        sa.sessions_24h,
        sa.max_sessions_24h,
        -- bot features. This is  where we determine who is a bot based on the current definition
        (COALESCE(lc.invalid_user_tier_gens, 0) > 0) as matches_invalid_user_tier_feature,
        (clips_with_metrics_count >= 50 AND COALESCE(unplayed_percent,0) >= 1.0) as matches_unplay_feature,
        (COALESCE(latest_hcaptcha_score, 0) >= 0.8) as matches_hcaptcha_feature,
        -- a proxy feature! used for performance reasons
        (
            signup_country IS NULL AND
            is_country_metric_healthy AND
            bu.user_joined_at <= DATEADD(DAY, -1, CURRENT_DATE)
        ) as matches_missing_geo_feature,
        -- disable non-proxy feature for now
        -- (COALESCE(web_events_first_24_hours, 0) = 0 AND registered_platform = 'web') as matches_missing_web_events_feature,
        (email_domain_in_block_list) as matches_email_domain_in_block_list_feature,
        (email_domain_in_watch_list) as matches_email_domain_in_watch_list_feature,
        (email_suspicion >= 0.825) as matches_email_suspicion_feature,
        (token_missing_count >= 5 ) as matches_token_missing_feature,
        (language_count >= 15) as matches_language_count_feature,
        (ips_first_24_hours >= 10) as matches_ips_first_24_hours_feature,
        (minutes_to_first_clip >= 2880) as matches_minutes_to_first_clip_feature,
        bot_jail_status = 'jailed' as matches_currently_jailed_feature,
        -- TODO: figure out how to better flag these.
        (signup_country = 'Nigeria' AND play_count = 0 AND clip_count >= 4 AND are_clip_metrics_healthy) as matches_nigeria_bot_feature,
        COALESCE(
            (
                -- these carriers have > 90% bots
                mobile_carrier LIKE 'ONVOY, LLC%' OR
                mobile_carrier IN ('IP HORIZON LLC')
            )
            AND play_count = 0
        , FALSE) as matches_spam_mobile_carrier_bot_feature,
        -- COALESCE(
        --     -- look for free bots...
        --     most_recent_plan IS NULL
        --     -- active in the time period right after credits reset...
        --     AND most_active_clip_gen_hour_7d BETWEEN 0 AND 3
        --     -- who have generated clips in the most active hour...
        --     AND total_clip_gens_in_most_active_hour_7d >= 6
        --     -- but not played them...
        --     AND play_count = 0
        --     -- with domain concentration...
        --     AND d.total > 20
        --     -- safeguard
        --     AND are_clip_metrics_healthy
        -- ), FALSE) as matches_midnight_bot_feature,
        ARRAY_CONSTRUCT_COMPACT(
            IFF(matches_invalid_user_tier_feature, 'invalid_user_tier', NULL),
            IFF(matches_unplay_feature, 'unplay', NULL),
            IFF(matches_hcaptcha_feature, 'hcaptcha', NULL),
            IFF(matches_missing_geo_feature, 'missing_geo', NULL),
            IFF(matches_email_domain_in_block_list_feature, 'email_domain_block', NULL),
            IFF(matches_email_suspicion_feature, 'email_suspicion', NULL),
            IFF(matches_token_missing_feature, 'token_missing', NULL),
            IFF(matches_language_count_feature, 'language_count', NULL),
            IFF(matches_ips_first_24_hours_feature, 'ips_24h', NULL),
            IFF(matches_currently_jailed_feature, 'currently_jailed', NULL),
            IFF(matches_nigeria_bot_feature, 'nigeria_bot', NULL),
            IFF(matches_spam_mobile_carrier_bot_feature, 'spam_mobile_carrier', NULL)
        ) as matching_features,
        prev_matching_features,
        ARRAY_SIZE(matching_features) > 0 as meets_bot_definition,
        prev_meets_bot_definition,
        prev_meets_bot_definition != meets_bot_definition as status_changed
    FROM base_users bu
    LEFT JOIN clip_metrics cm  ON cm.user_id = bu.user_id
    LEFT JOIN early_clip_metrics ecm ON bu.user_id = ecm.user_id
    -- LEFT JOIN early_web_event_metrics we ON bu.user_id = we.user_id
    CROSS JOIN health_metrics h
    LEFT JOIN dim_user_health_metrics hm ON DATE(bu.user_joined_at) = hm.user_joined_at
    LEFT JOIN SUNO_PROD.PROD.RDS_BOT_JAIL bj ON bu.user_id = bj.user_id
    LEFT JOIN language_counts lc ON bu.user_id = lc.user_id
    LEFT JOIN ip_counts ic ON bu.user_id = ic.user_id
    LEFT JOIN latest_hpcatcha_scores hc ON bu.user_id = hc.user_id
    LEFT JOIN token_counts tc ON bu.user_id = tc.user_id
    LEFT JOIN subscription_details ch ON bu.user_id = ch.user_id
    LEFT JOIN most_recent_plan_history mp ON ch.stripe_customer_id = mp.customer_id
    LEFT JOIN copycats cc ON bu.user_id = cc.user_id
    LEFT JOIN endpoint_cutover_traffic ec ON bu.user_id = ec.user_id
    LEFT JOIN user_stats us ON bu.user_id = us.user_id
    LEFT JOIN clip_timeseries ct ON bu.user_id = ct.user_id
    LEFT JOIN session_activity sa ON bu.user_id = sa.user_id
    LEFT JOIN blocked_in_clerk_domains bcd ON bu.email_domain = bcd.domain
    LEFT JOIN events_flattened ef ON bu.user_id = ef.user_id
    LEFT JOIN (
        SELECT 
            user_id,
            meets_bot_definition as prev_meets_bot_definition,
            matching_features as prev_matching_features
        FROM SUNO_PROD.PROD.BOT_HOURLY prev
        GROUP BY 1,2,3
    ) prev ON bu.user_id = prev.user_id
    LEFT JOIN mobile_carrier mobc ON bu.clerk_id = mobc.clerk_id
    ORDER BY cm.unplayed_percent DESC, cm.clips_with_metrics_count DESC
)
SELECT
    *,
    CASE
        WHEN
            matches_invalid_user_tier_feature OR
            matches_unplay_feature OR
            matches_hcaptcha_feature OR
            --matches_missing_geo_feature OR
            --matches_email_domain_in_block_list_feature OR
            --matches_email_domain_in_watch_list_feature OR
            matches_email_suspicion_feature OR
            matches_token_missing_feature OR
            matches_language_count_feature OR
            matches_ips_first_24_hours_feature OR
            matches_minutes_to_first_clip_feature OR
            matches_currently_jailed_feature OR
            matches_nigeria_bot_feature OR
            matches_spam_mobile_carrier_bot_feature
        THEN true
        ELSE false
    END as should_send_to_bot_model
FROM bot_signals;
