-- This is the provider-side example code for consumer-written templates.
-- For more information, see the documentation at docs.snowflake.com/user-guide/cleanrooms/demo-flows/custom-templates

USE WAREHOUSE APP_WH;
USE ROLE samooha_app_role;

SET cleanroom_name = 'Custom consumer template';
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 the join policy.
CALL samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name, 
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:hashed_email']);

-- Add consumers who can access the clean room when 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, and approve the consumer's request.
-- It takes a minute or two for the request to propagate from the consumer.
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 has been added.
CALL samooha_by_snowflake_local_db.provider.view_added_templates($cleanroom_name);

-- Take this from the TEMPLATE_NAME row in the pending requests result.
SET template_name = 'basic_consumer_template';

-- Set column policies. Column policies are tied to a specific template and table
-- so you must approve the template request 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);
