--- Example of using Snowpark UDFs in a clean room: Consumer code                             ---
--- See https://docs.snowflake.com/user-guide/cleanrooms/demo-flows/snowpark for full details ---

-- Install the clean room.
USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE app_wh;
SET cleanroom_name = 'Snowpark Demo clean room';
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_ACCOUNT_LOCATOR>');
CALL samooha_by_snowflake_local_db.consumer.is_enabled($cleanroom_name);
CALL samooha_by_snowflake_local_db.consumer.link_datasets($cleanroom_name, ['<USERS_TABLE>']);

-- Run the analysis and save the data to a table inside the clean room.
-- This call returns a message with the ID of the results table, used in the next step.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,                -- Clean room name
  'prod_calculate_regression',    -- The template that calculates results.
  ['<USERS_TABLE>'],              -- Consumer tables for the analysis.
  ['<IMPRESSSIONS_TABLE>'],       -- Provider tables for the analysis.
  object_construct()              -- No additional template arguments in this example.
);

-- Insert the ID returned by the UDF in the previous call.
SET result_suffix = 'regression_results_<ID>';

-- Call the template that displays the results table.
-- The table requires an ID returned by the previous analysis.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,        -- Clean room name.
  'prod_get_results',     -- The template used to display stored results.
  [],                     -- Consumer and provider tables not needed;
  [],                     -- we're reading the stored results.
  object_construct(
      'results_suffix', $result_suffix  -- Table name suffix. Needed to identify the results table.
  )
);
