-- This notebook is the provider side of the consumer-written UDF code. Run this notebook in a Snowflake account that acts
-- as the provider and creates the clean room. Where indicated, switch to the consumer account notebook to run the 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 consumer_locator = '<consumer locator>';
SET consumer_account_name = '<consumer data sharing account identifier>';
    
--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']);

-- Set join policy.
CALL samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name, 
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:hashed_email']);

-- Share with consumer (when it's published).
CALL samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, $consumer_locator, $consumer_account_name);
    
-- 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, '<REQUEST_ID>');

-- You can see that the template was added.
CALL samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

-- Take the template name from the TEMPLATE_NAME row in the pending requests result.
SET template_name = '<TEMPLATE_NAME>';

-- Set column policies. Column policies are tied to a specific template and table
-- so the consumer must add the template first.
CALL samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name,
  [$template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:STATUS',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:AGE_BAND',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:REGION_CODE',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:DAYS_ACTIVE',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:DEVICE_TYPE'
   ]);

-- SWITCH TO CONSUMER TO RUN THE TEMPLATE.
   
-- Clean up afterward.
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);
