CREATE OR REPLACE PROCEDURE dim_user_proc(p_date VARCHAR, p_hour NUMBER)
    RETURNS STRING
    LANGUAGE SQL
    AS
    $$
    BEGIN
        MERGE INTO dim_user du
        USING (
            WITH

                -- Web events by user-country
                user_country_web AS (
                    SELECT
                        user_uid
                        , country
                        , COUNT(*) as n_events
                    FROM
                        suno_prod.prod.web_user_event
                    WHERE 1=1
                        AND p_date BETWEEN DATE(:p_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
                        suno_prod.prod.app_event
                    WHERE 1=1
                        AND p_date BETWEEN DATE(:p_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
                , user_country_web_earliest_country AS (
                    SELECT
                        user_uid
                        , country
                        , MIN(client_timestamp) as earliest_event_timestamp
                    FROM
                        suno_prod.prod.web_user_event
                    GROUP BY 1, 2
                )

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

                -- Combine web and mobile events by user-country
                , 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
                )

                -- Will be used later after we have sufficient session state data
                , 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
                        suno_prod.prod.USER_SESSION_STATE
                    WHERE 1=1
                        AND p_date BETWEEN DATE(:p_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
                        suno_prod.prod.USER_SESSION_STATE
                    WHERE 1=1
                        AND p_date BETWEEN DATE(:p_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
                        suno_prod.prod.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
                    , ruc.earliest_event_timestamp AS earliest_frontend_event_timestamp
                FROM
                    suno_prod.prod.rds_discord_info di
                INNER JOIN
                    ranked_auth_user rau
                    ON di.user_id = rau.id AND rau.row_num = 1
                LEFT JOIN
                    suno_prod.prod.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.birthday = source.birthday
                , du.earliest_frontend_event_timestamp = source.earliest_frontend_event_timestamp
        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
                , birthday
                , earliest_frontend_event_timestamp
            ) 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.birthday
                , source.earliest_frontend_event_timestamp
            );

        MERGE INTO dim_user as target
        USING (
            select 
                a.user_uid as user_uid
            from dim_user 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;
        
    RETURN 'SUCCESS';

    END;
    $$;
