-- This is the provider side code for the basic consumer-run data analysis example.
-- For context and more information, see docs.snowflake.com/user-guide/cleanrooms/demo-flows/basic-flow-data-analysis

USE WAREHOUSE app_wh;
USE ROLE SAMOOHA_APP_ROLE;

SET cleanroom_name = 'Consumer analysis';
CALL samooha_by_snowflake_local_db.provider.describe_cleanroom($cleanroom_name);
CALL samooha_by_snowflake_local_db.provider.view_cleanrooms();

-- Create a clean room accessible only within this org.
CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link in 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',
   'SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:HASHED_PHONE']);
CALL samooha_by_snowflake_local_db.provider.view_join_policy($cleanroom_name);

-- Add a template that is already installed in all accounts.
-- To learn about this template, see
-- docs.snowflake.com/en/user-guide/cleanrooms/demo-flows/basic-flow-overlap
SET template_name = 'prod_overlap_analysis';
CALL samooha_by_snowflake_local_db.provider.add_templates(
  $cleanroom_name,
  [$template_name]);
CALL samooha_by_snowflake_local_db.provider.view_template_definition($cleanroom_name, $template_name);


-- Restrict which columns of your data can be used in the template.
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:DAYS_ACTIVE',
  $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:REGION_CODE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

-- Set who can access the clean room.
-- Provide your consumer's account locator and data sharing account ID where indicated.
CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  '<CONSUMER_LOCATOR>',
  '<CONSUMER_ORG_NAME>.<CONSUMER_ACCOUNT_NAME>');
  
-- Set the default clean room version.
CALL samooha_by_snowflake_local_db.provider.set_default_release_directive(
  $cleanroom_name,
  'V1_0', '0');

-- Publish the clean room. This can take a minute or two.
CALL samooha_By_snowflake_local_db.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);

-- SWITCH TO CONSUMER TO INSTALL AND RUN THE CLEAN ROOM

-- When finished, clean up your resources.
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);
