-- This code demonstrates running both a provider-run and a consumer-run analysis in a clean room.
-- Both analyses use code uploaded by the provider and consumer.
-- This is the provider code.
-- For more information, see docs.snowflake.com/user-guide/cleanrooms/demo-flows/custom-code.

USE WAREHOUSE app_wh;
USE ROLE SAMOOHA_APP_ROLE;

SET cleanroom_name = 'Custom consumer UDF';
SET basic_template_name = 'say_hello';
SET consumer_locator = '<CONSUMER_LOCATOR>';
SET consumer_account_name = '<CONSUMER_DATA_SHARING_ID>';
    
--Create the clean room.
CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link data.
CALL samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

-- Share with consumer (when it's published).
CALL samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, $consumer_locator, $consumer_account_name);

-- Enable provider-run analysis. This must be done before the consumer installs the clean room.
CALL samooha_by_snowflake_local_db.provider.enable_provider_run_analysis(
  $cleanroom_name,
  [$consumer_locator]
);

-- Publish the clean room.
CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '0');
CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($cleanroom_name);

-- SWITCH TO CONSUMER TO INSTALL THE CLEAN ROOM

-- Look for template requests. Approve the request from the consumer.
-- It takes a few minutes for the request to propagate.
CALL samooha_by_snowflake_local_db.provider.list_pending_template_requests($cleanroom_name);
CALL samooha_by_snowflake_local_db.provider.approve_template_request($cleanroom_name, '<QUERY_ID>');

-- Add the template that you plan to run. This template calls the consumer's function.
-- Note how you must alias the projected column, because all columns in a provider-run template
-- must have explicit names.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $basic_template_name, 
    $$
    SELECT
      cleanroom.say_hello(c.hashed_first_name) AS hashed_firstname from IDENTIFIER({{ my_table[0]}}) as c LIMIT 10;
  $$
);

-- You can see that both your template and the consumer's template were added to the clean room.
CALL samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

-- SWITCH TO CONSUMER TO RUN THEIR TEMPLATE AND APPROVE YOUR TEMPLATE.

-- NOW RUN YOUR OWN TEMPLATE.---

-- Must be run before any provider-run queries to get access to the consumer's data.
CALL samooha_by_snowflake_local_db.provider.mount_request_logs_for_all_consumers($cleanroom_name);

-- Save the returned request ID.
CALL samooha_by_snowflake_local_db.provider.submit_analysis_request(
    $cleanroom_name, 
    $consumer_locator,
    $basic_template_name, 
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], 
    ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], 
    {}
    );

-- When the status is COMPLETE, you can get the result.
CALL samooha_by_snowflake_local_db.provider.check_analysis_status(
    $cleanroom_name,
    '<REQUEST_ID>', 
    $consumer_locator
);

CALL samooha_by_snowflake_local_db.provider.get_analysis_result(
    $cleanroom_name,
    '<REQUEST_ID>',
    $consumer_locator
);

-- Clean up afterward.
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);
