CREATE OR REPLACE PROCEDURE AGG_USER_SUGGESTED_CREATORS_WEEKLY_PROC(
    P_DATE VARCHAR
)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    total_users_processed NUMBER DEFAULT 0;
    users_with_suggestions NUMBER DEFAULT 0;
    current_week_start DATE;
    latest_engagement_date DATE;
    latest_user_date VARCHAR;
BEGIN
    -- Convert string date to DATE
    current_week_start := TO_DATE(P_DATE, 'YYYY-MM-DD');

    DELETE FROM AGG_USER_SUGGESTED_CREATORS_WEEKLY
    WHERE p_date = :current_week_start;
    
    -- Get the latest engagement date to limit data
    SELECT MAX(p_date) INTO latest_engagement_date
    FROM SUNO_PROD.PROD.AGG_USER_TO_USER_ENGAGEMENT_WEEKLY;
    
    -- Get the latest user data date for optimization
    SELECT MAX(p_date) INTO latest_user_date
    FROM SUNO_PROD.PROD.dim_user;
    
    ------------------------------------------------------------------
    -- Materialise heavy intermediate datasets to cut repeated scans
    ------------------------------------------------------------------

    -- 1️⃣  Engagement rows for the latest week
    CREATE OR REPLACE TEMP TABLE TMP_RECENT_ENGAGEMENT AS
    SELECT
        user_id,
        engage_user_id,
        total_score,
        relationship
    FROM SUNO_PROD.PROD.AGG_USER_TO_USER_ENGAGEMENT_WEEKLY
    WHERE p_date = :latest_engagement_date
      AND user_id        IS NOT NULL
      AND engage_user_id IS NOT NULL
      AND total_score    > 0;

    -- 2️⃣  Top-40 creators a user follows / is mutual with
    CREATE OR REPLACE TEMP TABLE TMP_TOP_CREATORS AS
    SELECT
        user_id,
        engage_user_id  AS top_creator_id,
        total_score     AS user_to_creator_score
    FROM TMP_RECENT_ENGAGEMENT
    WHERE relationship IN ('following', 'mutual')
    QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_score DESC) <= 40;

    -- 3️⃣  Latest geo row for ALL users with location data (not just engaged users)
    -- FIXED: Remove 180-day restriction to include users with recent engagement but older dim_user records
    CREATE OR REPLACE TEMP TABLE TMP_USER_GEO AS
    SELECT user_id, recent_city, recent_country
    FROM (
        SELECT
            d.user_id,
            d.recent_city,
            d.recent_country,
            ROW_NUMBER() OVER (PARTITION BY d.user_id ORDER BY d.p_date DESC) AS rn
        FROM SUNO_PROD.PROD.dim_user d
        WHERE ((d.recent_city    IS NOT NULL AND d.recent_city    <> '')
            OR (d.recent_country IS NOT NULL AND d.recent_country <> ''))
    )
    QUALIFY rn = 1;

    -- 4️⃣  Pre-compute friends-of-friends with early filtering
    CREATE OR REPLACE TEMP TABLE TMP_FRIENDS_OF_FRIENDS AS
    WITH friends_of_friends_raw AS (
        SELECT 
            t40.user_id AS original_user_id,
            t40.top_creator_id AS intermediate_creator_id,
            re2.engage_user_id AS suggested_creator_id,
            re2.total_score AS intermediate_to_suggestion_score,
            t40.user_to_creator_score,
            (t40.user_to_creator_score * re2.total_score / 100.0) AS path_strength_score
        FROM TMP_TOP_CREATORS t40   
        JOIN TMP_RECENT_ENGAGEMENT re2 
            ON t40.top_creator_id = re2.user_id
            AND re2.relationship IN ('following', 'mutual')  -- Only meaningful connections
        WHERE re2.engage_user_id <> t40.user_id  -- Don't suggest yourself
    )
    SELECT 
        original_user_id,
        intermediate_creator_id,
        suggested_creator_id,
        intermediate_to_suggestion_score,
        user_to_creator_score,
        path_strength_score
    FROM friends_of_friends_raw
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY original_user_id, intermediate_creator_id 
        ORDER BY intermediate_to_suggestion_score DESC
    ) <= 20;  -- Top 20 per creator, not 5

    -- Add clustering for better join performance
    ALTER TABLE TMP_RECENT_ENGAGEMENT CLUSTER BY (user_id, engage_user_id);
    ALTER TABLE TMP_TOP_CREATORS CLUSTER BY (user_id);
    ALTER TABLE TMP_USER_GEO CLUSTER BY (user_id);
    ALTER TABLE TMP_FRIENDS_OF_FRIENDS CLUSTER BY (original_user_id);

    -- 5️⃣  Pre-compute top creators by location - include ALL active users, not just engaged ones
    -- FIXED VERSION: Simplified TMP_LOCATION_CREATORS generation
    -- Replace the problematic section in your procedure with this

    -- 5️⃣  FIXED: Pre-compute top creators by location - simplified and robust
    CREATE OR REPLACE TEMP TABLE TMP_LOCATION_CREATORS AS
    WITH all_user_locations AS (
        -- Get latest location data for ALL users - FIXED: Remove 180-day restriction
        SELECT 
            d.user_id,
            d.recent_city,
            d.recent_country
        FROM SUNO_PROD.PROD.dim_user d
        WHERE ((d.recent_city IS NOT NULL AND d.recent_city <> '')
            OR (d.recent_country IS NOT NULL AND d.recent_country <> ''))
        QUALIFY ROW_NUMBER() OVER (PARTITION BY d.user_id ORDER BY d.p_date DESC) = 1
    ),
    user_engagement_scores AS (
        -- Pre-compute total engagement score per user for efficient ordering
        SELECT 
            engage_user_id AS user_id,
            SUM(total_score) AS total_incoming_engagement
        FROM TMP_RECENT_ENGAGEMENT
        GROUP BY engage_user_id
    ),
    city_creators AS (
        -- ENGAGEMENT-ORDERED: Order creators by engagement score for better recommendations
        SELECT 
            ul.recent_city AS location_key,
            'city' AS location_type,
            ul.user_id AS creator_id,
            ROW_NUMBER() OVER (
                PARTITION BY ul.recent_city 
                ORDER BY COALESCE(ues.total_incoming_engagement, 0) DESC, ul.user_id
            ) as rn
        FROM all_user_locations ul
        LEFT JOIN user_engagement_scores ues ON ul.user_id = ues.user_id
        WHERE ul.recent_city IS NOT NULL AND ul.recent_city <> ''
    ),
    country_creators AS (
        -- ENGAGEMENT-ORDERED: Order creators by engagement score for better recommendations
        SELECT 
            ul.recent_country AS location_key,
            'country' AS location_type,
            ul.user_id AS creator_id,
            ROW_NUMBER() OVER (
                PARTITION BY ul.recent_country 
                ORDER BY COALESCE(ues.total_incoming_engagement, 0) DESC, ul.user_id
            ) as rn
        FROM all_user_locations ul
        LEFT JOIN user_engagement_scores ues ON ul.user_id = ues.user_id
        WHERE ul.recent_country IS NOT NULL AND ul.recent_country <> ''
    ),
    limited_creators AS (
        -- Take more creators per location to ensure sufficient suggestions
        SELECT location_key, location_type, creator_id, rn FROM city_creators WHERE rn <= 50
        UNION ALL
        SELECT location_key, location_type, creator_id, rn FROM country_creators WHERE rn <= 50
    )
    SELECT 
        location_key,
        location_type,
        ARRAY_AGG(creator_id) WITHIN GROUP (ORDER BY rn) AS creator_list  -- Preserve engagement-based order
    FROM limited_creators
    GROUP BY location_key, location_type
    HAVING COUNT(*) >= 5;  -- Only include locations with at least 5 creators

    ALTER TABLE TMP_LOCATION_CREATORS CLUSTER BY (location_key, location_type);

    ------------------------------------------------------------------
    -- Filter to only users with engagements - don't give recommendations to users without engagement activity
    ------------------------------------------------------------------
    CREATE OR REPLACE TEMP TABLE TMP_ACTIVE_USERS AS
    WITH engaged_users AS (
        -- Users who have outgoing engagements (they engage with others)
        SELECT DISTINCT user_id
        FROM TMP_RECENT_ENGAGEMENT
        
        UNION
        
        -- Users who have incoming engagements (others engage with them)
        SELECT DISTINCT engage_user_id AS user_id
        FROM TMP_RECENT_ENGAGEMENT
    )
    SELECT user_id
    FROM engaged_users;

    ALTER TABLE TMP_ACTIVE_USERS CLUSTER BY (user_id);

    ------------------------------------------------------------------
    -- Start main aggregation logic
    ------------------------------------------------------------------
    INSERT INTO AGG_USER_SUGGESTED_CREATORS_WEEKLY (
        user_id,
        suggested_creators,
        suggestion_degrees,
        p_date
    )
    WITH engaged_not_following AS (
        -- Users engaged with creators but not following/mutual
        SELECT 
            user_id AS original_user_id,
            engage_user_id AS suggested_creator_id,
            total_score
        FROM TMP_RECENT_ENGAGEMENT
        WHERE relationship IN ('followed', 'none')  -- Engaged but not following/mutual
          AND total_score > 0  -- Has meaningful engagement
    ),
    reverse_connection AS (
        -- Users who engage with the target user but aren't followed back (reverse of 1st degree)
        SELECT 
            engage_user_id AS original_user_id,  -- The user being engaged with
            user_id AS suggested_creator_id,     -- The user doing the engaging
            total_score
        FROM TMP_RECENT_ENGAGEMENT
        WHERE relationship IN ('followed', 'none')  -- Target user is NOT following the engager back
          AND total_score > 0  -- Has meaningful engagement
    ),
    friends_of_friends_suggestions AS (
        -- Use pre-computed friends-of-friends data
        SELECT 
            original_user_id,
            suggested_creator_id,
            user_to_creator_score AS user_to_intermediate_score,
            intermediate_to_suggestion_score,
            path_strength_score
        FROM TMP_FRIENDS_OF_FRIENDS
    ),
    target_users_for_geo AS (
        -- Engage users who have geo data (already materialised)
        SELECT * FROM TMP_USER_GEO
    ),
    geo_based_raw AS (
        -- More aggressive location-based recommendations - include ALL users with geo data
        -- City matches
        SELECT 
            users.user_id AS original_user_id,
            creators.value AS suggested_creator_id,
            150 AS geo_score,
            'city' as geo_type,
            ROW_NUMBER() OVER (PARTITION BY users.user_id ORDER BY creators.index) as geo_rank
        FROM target_users_for_geo users
        JOIN TMP_LOCATION_CREATORS loc
            ON users.recent_city = loc.location_key 
            AND loc.location_type = 'city'
            AND users.recent_city IS NOT NULL 
            AND users.recent_city <> ''
        , LATERAL FLATTEN(input => loc.creator_list) creators
        WHERE users.user_id != creators.value
        
        UNION ALL
        
        -- Country matches - always include with lower score
        SELECT 
            users.user_id AS original_user_id,
            creators.value AS suggested_creator_id,
            100 AS geo_score,  -- Fixed lower score for country matches
            'country' as geo_type,
            ROW_NUMBER() OVER (PARTITION BY users.user_id ORDER BY creators.index) + 1000 as geo_rank
        FROM target_users_for_geo users
        JOIN TMP_LOCATION_CREATORS loc
            ON users.recent_country = loc.location_key 
            AND loc.location_type = 'country'
            AND users.recent_country IS NOT NULL 
            AND users.recent_country <> ''
        , LATERAL FLATTEN(input => loc.creator_list) creators
        WHERE users.user_id != creators.value
    ),
    geo_based_suggestions AS (
        -- Use pre-computed location creators - be more generous with geo suggestions
        SELECT 
            original_user_id,
            suggested_creator_id,
            geo_score
        FROM geo_based_raw
        WHERE geo_rank <= 50  -- Much more generous - top 50 from each location type
    ),
    prioritized_suggestions AS (
        -- Combine all suggestion types with string degrees
        SELECT 
            original_user_id, 
            suggested_creator_id,
            total_score as priority_score,
            '1st_degree_connection' as degree
        FROM engaged_not_following
        UNION ALL
        SELECT 
            original_user_id, 
            suggested_creator_id,
            path_strength_score as priority_score,
            '2nd_degree_connection' as degree
        FROM friends_of_friends_suggestions
        UNION ALL
        SELECT 
            original_user_id, 
            suggested_creator_id,
            total_score as priority_score,
            'reverse_connection' as degree
        FROM reverse_connection
        UNION ALL
        SELECT 
            original_user_id, 
            suggested_creator_id,
            geo_score as priority_score,
            'geo_based' as degree
        FROM geo_based_suggestions
    ),
    top_suggestions AS (
        -- Rank suggestions with proper type prioritization to ensure we fill up to 20
        SELECT 
            original_user_id,
            suggested_creator_id,
            priority_score,
            degree,
            ROW_NUMBER() OVER (
                PARTITION BY original_user_id 
                ORDER BY 
                    CASE degree 
                        WHEN '1st_degree_connection' THEN 1
                        WHEN '2nd_degree_connection' THEN 2
                        WHEN 'reverse_connection' THEN 3
                        WHEN 'geo_based' THEN 4
                    END,
                    priority_score DESC
            ) as suggestion_rank
        FROM prioritized_suggestions
    ),

    step3_filtered AS (
        -- Step 3: Filter out existing following/mutual relationships for non-geo suggestion types (optimized)
        -- Keep geo suggestions regardless of existing relationships (location-based diversity)
        SELECT DISTINCT
            ts.original_user_id,
            ts.suggested_creator_id,
            :current_week_start AS p_date,
            ts.degree
        FROM top_suggestions ts
        WHERE ts.suggestion_rank <= 300  -- Combine limited_suggestions logic here
          AND (
              ts.degree = 'geo_based'  -- Always include geo suggestions
              OR NOT EXISTS (
                  SELECT 1 FROM TMP_RECENT_ENGAGEMENT re_existing
                  WHERE ts.original_user_id = re_existing.user_id 
                    AND ts.suggested_creator_id = re_existing.engage_user_id
                    AND re_existing.relationship IN ('following', 'mutual')
              )
          )
    ),
    top_20_valid_suggestions AS (
        -- Preserve the type-based ranking to ensure geo suggestions fill remaining slots
        SELECT 
            s3.original_user_id,
            s3.suggested_creator_id,
            s3.p_date,
            s3.degree,
            ts.suggestion_rank as final_rn  -- Use original ranking instead of re-ranking
        FROM step3_filtered s3
        JOIN top_suggestions ts 
            ON s3.original_user_id = ts.original_user_id 
            AND s3.suggested_creator_id = ts.suggested_creator_id
    ),
    filtered_top_20 AS (
        -- Ensure we fill up to 20 suggestions per user with proper type distribution
        SELECT 
            original_user_id,
            suggested_creator_id,
            p_date,
            degree,
            final_rn,
            ROW_NUMBER() OVER (
                PARTITION BY original_user_id 
                ORDER BY final_rn
            ) as final_rank
        FROM top_20_valid_suggestions
    ),
    balanced_suggestions AS (
        -- Ensure we get up to 20 suggestions, with explicit geo backfill when needed
        SELECT 
            original_user_id,
            suggested_creator_id,
            p_date,
            degree,
            final_rn
        FROM filtered_top_20
        WHERE final_rank <= 20  -- Take top 20 of each type in priority order
    ),
    geo_backfill AS (
        -- Add additional geo suggestions if we have fewer than 10 total suggestions per user
        -- More aggressive backfill to ensure minimum coverage
        SELECT 
            bs.original_user_id,
            COUNT(*) as current_count,
            GREATEST(20 - COUNT(*), 0) as slots_needed,  -- Ensure at least 20 suggestions
            GREATEST(20 - COUNT(*), 0) as slots_available  -- Up to 20 total
        FROM balanced_suggestions bs
        GROUP BY bs.original_user_id
        HAVING COUNT(*) < 10  -- Users with fewer than 10 suggestions need aggressive geo backfill
    ),
    additional_geo AS (
        -- Get additional geo suggestions for users who need more (optimized with anti-join)
        SELECT 
            ps.original_user_id,
            ps.suggested_creator_id,
            ps.priority_score,
            ps.degree,
            ROW_NUMBER() OVER (PARTITION BY ps.original_user_id ORDER BY ps.priority_score DESC) + 100 as final_rn  -- Ensure they come after existing
        FROM prioritized_suggestions ps
        JOIN geo_backfill gb ON ps.original_user_id = gb.original_user_id
        LEFT JOIN balanced_suggestions bs2 
            ON ps.original_user_id = bs2.original_user_id 
            AND ps.suggested_creator_id = bs2.suggested_creator_id
        WHERE ps.degree = 'geo_based'
          AND bs2.suggested_creator_id IS NULL  -- Anti-join instead of NOT EXISTS
        QUALIFY ROW_NUMBER() OVER (PARTITION BY ps.original_user_id ORDER BY ps.priority_score DESC) <= gb.slots_available
    ),
    emergency_geo_backfill AS (
        -- For users with very few suggestions, aggressively add more geo suggestions (optimized)
        WITH flattened_creators AS (
            SELECT 
                tug.user_id,
                creators.value as suggested_creator_id,
                creators.index,
                gb.current_count
            FROM TMP_USER_GEO tug
            JOIN geo_backfill gb ON tug.user_id = gb.original_user_id
            JOIN TMP_LOCATION_CREATORS loc
                ON (tug.recent_city = loc.location_key AND loc.location_type = 'city')
                OR (tug.recent_country = loc.location_key AND loc.location_type = 'country')
            , LATERAL FLATTEN(input => loc.creator_list) creators
            WHERE gb.current_count < 8  -- More generous threshold - users with < 8 suggestions
              AND tug.user_id != creators.value
        )
        SELECT 
            fc.user_id as original_user_id,
            fc.suggested_creator_id,
            50 as priority_score,  -- Lower priority than city matches
            'geo_based' as degree,
            ROW_NUMBER() OVER (PARTITION BY fc.user_id ORDER BY fc.index) + 200 as final_rn
        FROM flattened_creators fc
        LEFT JOIN balanced_suggestions bs3 
            ON fc.user_id = bs3.original_user_id 
            AND fc.suggested_creator_id = bs3.suggested_creator_id
        LEFT JOIN additional_geo ag
            ON fc.user_id = ag.original_user_id 
            AND fc.suggested_creator_id = ag.suggested_creator_id
        WHERE bs3.suggested_creator_id IS NULL  -- Anti-join instead of NOT EXISTS
          AND ag.suggested_creator_id IS NULL   -- Anti-join instead of NOT EXISTS
        QUALIFY ROW_NUMBER() OVER (PARTITION BY fc.user_id ORDER BY fc.index) <= GREATEST(10 - fc.current_count, 0)  -- Fill up to 10 total
    ),
    final_combined AS (
        -- Combine regular suggestions with geo backfill and emergency backfill
        SELECT original_user_id, suggested_creator_id, p_date, degree, final_rn FROM balanced_suggestions
        UNION ALL
        SELECT original_user_id, suggested_creator_id, :current_week_start as p_date, degree, final_rn FROM additional_geo
        UNION ALL
        SELECT original_user_id, suggested_creator_id, :current_week_start as p_date, degree, final_rn FROM emergency_geo_backfill
    ),
    unique_suggestions AS (
        -- Ensure each suggested creator appears only once per user (keep the highest priority)
        SELECT 
            original_user_id,
            suggested_creator_id,
            p_date,
            degree,
            final_rn,
            ROW_NUMBER() OVER (
                PARTITION BY original_user_id, suggested_creator_id 
                ORDER BY degree ASC, final_rn ASC  -- First degree wins, then best rank
            ) as dup_rank
        FROM final_combined
    ),
    deduplicated_suggestions AS (
        -- Keep only one instance of each suggested creator per user with degree info, limit to 20
        SELECT 
            original_user_id,
            suggested_creator_id,
            p_date,
            degree,
            final_rn,
            ROW_NUMBER() OVER (PARTITION BY original_user_id ORDER BY final_rn) as final_position
        FROM unique_suggestions
        WHERE dup_rank = 1
    ),
    final_limited AS (
        -- Ensure exactly 20 suggestions per user maximum
        SELECT 
            original_user_id,
            suggested_creator_id,
            p_date,
            degree,
            final_rn
        FROM deduplicated_suggestions
        WHERE final_position <= 20  -- Hard limit to 20 suggestions
    ),
    -- Create temp table for final results to optimize ARRAY_AGG
    final_results_temp AS (
        SELECT 
            original_user_id AS user_id,
            p_date,
            suggested_creator_id,
            degree,
            final_rn
        FROM final_limited
    )
    SELECT 
        frt.user_id,
        ARRAY_AGG(frt.suggested_creator_id) WITHIN GROUP (ORDER BY frt.final_rn) AS suggested_creators,
        ARRAY_AGG(frt.degree) WITHIN GROUP (ORDER BY frt.final_rn) AS suggestion_degrees,
        frt.p_date
    FROM final_results_temp frt
    JOIN TMP_ACTIVE_USERS au ON frt.user_id = au.user_id  -- Only include active users
    GROUP BY frt.user_id, frt.p_date
    HAVING COUNT(*) > 0;  -- Only users with suggestions
    
    RETURN 'SUCCESS: Latest data from ' || :latest_engagement_date;
    
END;
$$
