-- This is the consumer-side example code for provider-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 provider template';
SET template_name = 'basic_template';

-- Install the clean room.
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<provider locator>');

-- Link your data into the clean room.
CALL samooha_by_snowflake_local_db.consumer.link_datasets(
  $cleanroom_name, ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']);

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

-- Set column policies.
CALL samooha_by_snowflake_local_db.consumer.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'
   ]);

-- Take a look at the template.
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);
CALL samooha_by_snowflake_local_db.consumer.view_template_definition($cleanroom_name, $template_name);
CALL samooha_by_snowflake_local_db.consumer.get_arguments_from_template($cleanroom_name, $template_name);

-- Run the template.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  $template_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
  OBJECT_CONSTRUCT(
  'consumer_id', 'c.HASHED_EMAIL',
  'provider_id', 'p.HASHED_EMAIL',
  'where_clause','c.status = $$MEMBER$$ AND c.age_band > 30'
  )
);

-- Clean up when done.
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);
