-- Create a weekly task to populate suggested creators for all users
CREATE OR REPLACE TASK AGG_USER_SUGGESTED_CREATORS_WEEKLY_INSERT
  WAREHOUSE = SUNO_PROD_LARGE_DATA
  SCHEDULE = 'USING CRON 0 2 * * MON UTC'  -- Run every Monday at 2 AM UTC
  COMMENT = 'Weekly task to generate suggested creators for all active users'
AS
DECLARE
  current_week_start DATE DEFAULT DATE_TRUNC('WEEK', CURRENT_DATE());
  result STRING;
BEGIN
  -- Call the stored procedure to handle all the logic
  CALL AGG_USER_SUGGESTED_CREATORS_WEEKLY_PROC(TO_VARCHAR(:current_week_start, 'YYYY-MM-DD')) INTO :result;
  
  -- Log the results
  SYSTEM$LOG('INFO', 'Weekly suggested creators task completed: ' || :result);

  call TASK_MONITOR_INSERT_PROC('AGG_USER_SUGGESTED_CREATORS_WEEKLY_INSERT', :start_time, :row_count, 'SUCCESS', 'WEEKLY');
  
EXCEPTION
  WHEN OTHER THEN
    -- Log any errors
    SYSTEM$LOG('ERROR', 'Weekly suggested creators task failed: ' || SQLERRM);
    RAISE;
END;
