-- First MERGE: Insert/Update user data
MERGE INTO {{ dim_user_table_name }} du
USING (
    WITH
        -- Web events by user-country
        user_country_web AS (
            SELECT
                user_uid,
                country,
                COUNT(*) as n_events
            FROM
                web_user_event
            WHERE 1=1
                AND p_date BETWEEN DATE('{{ partition_start_date }}') - INTERVAL '60 days' AND Date(CURRENT_TIMESTAMP() - INTERVAL '1 hour')
            GROUP BY 1, 2
        ),

        -- Mobile events by user-country
        user_country_mobile AS (
            SELECT
                user_id as user_uid,
                country,
                COUNT(*) as n_events
            FROM
                app_event
            WHERE 1=1
                AND p_date BETWEEN DATE('{{ partition_start_date }}') - INTERVAL '60 days' AND Date(CURRENT_TIMESTAMP() - INTERVAL '1 hour')
            GROUP BY 1, 2
        ),

        -- Combine web and mobile events by user-country
        user_country_events AS (
            SELECT user_uid, country, n_events FROM user_country_web
            UNION ALL
            SELECT user_uid, country, n_events FROM user_country_mobile
        ),

        -- Web events by user-country - earliest
        user_country_web_earliest_country AS (
            SELECT
                user_uid,
                country,
                MIN(client_timestamp) as earliest_event_timestamp
            FROM
                web_user_event
            GROUP BY 1, 2
        ),

        -- Mobile events by user-country - earliest
        user_country_mobile_earliest_country AS (
            SELECT
                user_id as user_uid,
                country,
                MIN(client_timestamp) as earliest_event_timestamp
            FROM
                app_event
            GROUP BY 1, 2
        ),

        -- Combine web and mobile events by user-country - earliest
        user_country_sign_up_events AS (
            SELECT user_uid, country, earliest_event_timestamp FROM user_country_web_earliest_country
            UNION ALL
            SELECT user_uid, country, earliest_event_timestamp FROM user_country_mobile_earliest_country
        ),

        -- Get the earliest event for each user across web and mobile
        ranked_user_sign_up_country AS (
            SELECT * FROM (
                SELECT
                    user_uid,
                    country,
                    earliest_event_timestamp,
                    ROW_NUMBER() OVER (PARTITION BY user_uid ORDER BY earliest_event_timestamp ASC) AS row_num
                FROM user_country_sign_up_events
            ) WHERE row_num = 1
        ),

        -- User country info from session state
        user_country_info AS (
            SELECT
                user_id,
                country,
                COUNT(*) as country_frequency,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as rn
            FROM
                user_session_state
            WHERE 1=1
                AND p_date BETWEEN DATE('{{ partition_start_date }}') - INTERVAL '60 days' AND Date(CURRENT_TIMESTAMP() - INTERVAL '1 hour')
            GROUP BY 1, 2
        ),

        user_top_country AS (
            SELECT
                user_id,
                country
            FROM user_country_info
            WHERE rn = 1
        ),

        user_city_info AS (
            SELECT
                user_id,
                city,
                COUNT(*) as city_frequency,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as rn
            FROM
                user_session_state
            WHERE 1=1
                AND p_date BETWEEN DATE('{{ partition_start_date }}') - INTERVAL '60 days' AND Date(CURRENT_TIMESTAMP() - INTERVAL '1 hour')
            GROUP BY 1, 2
        ),

        user_top_city AS (
            SELECT
                user_id,
                city
            FROM user_city_info
            WHERE rn = 1
        ),

        ranked_auth_user AS (
            SELECT
                au.id,
                au.email,
                au.date_joined,
                au.is_staff,
                ROW_NUMBER() OVER (PARTITION BY au.id ORDER BY au.date_joined ASC) AS row_num
            FROM
                rds_auth_user au
        )

        SELECT
            -- Primary identifiers
            di.user_id,
            di.uid AS user_uid,
            di.clerk_id,
            -- Account information
            rau.email,
            di.phone_number,
            di.handle,
            di.display_name,
            -- User metadata
            rau.date_joined AS user_joined_at,
            di.registered_platform,
            ruc.country AS signup_country,
            ucr.country AS recent_country,
            uct.city AS recent_city,
            rau.is_staff OR REGEXP_LIKE(rau.email, '.*@suno\.(com|ai)$') AS is_staff,
            -- Subscription information
            di.subscription_status IN ('active', 'past_due') AS has_active_subscription,
            up.name AS current_subscription_tier,
            di.stripe_customer_id,
            di.stripe_subscription_id,
            -- TODO: Fill in after we have subscription SOT in place
            NULL AS revcat_customer_id,
            NULL AS revcat_subscription_id,
            di.birthday,
            TO_DATE(rau.date_joined) AS p_date,
            HOUR(rau.date_joined::TIMESTAMP) AS p_hour,
            ruc.earliest_event_timestamp AS earliest_frontend_event_timestamp
        FROM
            rds_discord_info di
        INNER JOIN
            ranked_auth_user rau
            ON di.user_id = rau.id AND rau.row_num = 1
        LEFT JOIN
            rds_usage_plan up
            ON di.subscription_plan_id = up.id
        LEFT JOIN
            user_top_country ucr
            ON di.user_id = ucr.user_id
        LEFT JOIN
            user_top_city uct
            ON di.user_id = uct.user_id
        LEFT JOIN
            ranked_user_sign_up_country ruc
            ON di.uid = ruc.user_uid
) AS source
ON du.user_id = source.user_id
WHEN MATCHED THEN
    UPDATE SET
        du.user_uid = source.user_uid,
        du.clerk_id = source.clerk_id,
        du.email = source.email,
        du.phone_number = source.phone_number,
        du.handle = source.handle,
        du.display_name = source.display_name,
        du.user_joined_at = source.user_joined_at,
        du.registered_platform = source.registered_platform,
        du.signup_country = source.signup_country,
        du.recent_country = COALESCE(source.recent_country, du.recent_country),
        du.recent_city = COALESCE(source.recent_city, du.recent_city),
        du.is_staff = source.is_staff,
        du.has_active_subscription = source.has_active_subscription,
        du.current_subscription_tier = source.current_subscription_tier,
        du.stripe_customer_id = source.stripe_customer_id,
        du.stripe_subscription_id = source.stripe_subscription_id,
        du.revcat_customer_id = source.revcat_customer_id,
        du.revcat_subscription_id = source.revcat_subscription_id,
        du.p_date = source.p_date,
        du.p_hour = source.p_hour,
        du.birthday = source.birthday
WHEN NOT MATCHED THEN
    INSERT (
        user_id,
        user_uid,
        clerk_id,
        email,
        phone_number,
        handle,
        display_name,
        user_joined_at,
        registered_platform,
        signup_country,
        recent_country,
        recent_city,
        is_staff,
        has_active_subscription,
        current_subscription_tier,
        stripe_customer_id,
        stripe_subscription_id,
        revcat_customer_id,
        revcat_subscription_id,
        p_date,
        p_hour,
        birthday
    ) VALUES (
        source.user_id,
        source.user_uid,
        source.clerk_id,
        source.email,
        source.phone_number,
        source.handle,
        source.display_name,
        source.user_joined_at,
        source.registered_platform,
        source.signup_country,
        source.recent_country,
        source.recent_city,
        source.is_staff,
        source.has_active_subscription,
        source.current_subscription_tier,
        source.stripe_customer_id,
        source.stripe_subscription_id,
        source.revcat_customer_id,
        source.revcat_subscription_id,
        source.p_date,
        source.p_hour,
        source.birthday
    );
MERGE INTO {{ dim_user_table_name }} as target
USING (
    SELECT 
        a.user_uid as user_uid
    FROM {{ dim_user_table_name }} as a            
    LEFT JOIN rds_discord_info as b ON a.user_uid = b.uid
    WHERE b.uid IS NULL
) AS source
ON source.user_uid = target.user_uid
WHEN MATCHED THEN
    UPDATE SET
        target.is_deleted = true;
