USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

SET CLEANROOM_1 = 'multiprovider room 1';
SET CLEANROOM_2 = 'multiprovider room 2';
SET consumer_locator = '<CONSUMER_LOCATOR>';
SET consumer_account_name = '<org_name>.<account_name>'; -- Account name, not account locator!

-- Create both clean rooms

-- Create clean room 1 ----------------

CALL samooha_by_snowflake_local_db.provider.cleanroom_init($CLEANROOM_1, 'INTERNAL');

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

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

-- Share with consumer
CALL samooha_by_snowflake_local_db.provider.add_consumers($CLEANROOM_1, $consumer_locator, $consumer_account_name);

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

-- Create clean room 2 ----------------

CALL samooha_by_snowflake_local_db.provider.cleanroom_init($CLEANROOM_2, 'INTERNAL');

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

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

-- Share with consumer
CALL samooha_by_snowflake_local_db.provider.add_consumers($CLEANROOM_2, $consumer_locator, $consumer_account_name);

-- Publish
CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($CLEANROOM_2, 'V1_0', '0');
CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($CLEANROOM_2);

-- SWITCH TO CONSUMER to install both clean rooms.

-- Check for, and approve, template requests.
-- Template requests can take a few minutes to propagate, so be patient; don't resend the consumer
-- request quickly, or you will get a long queue of requests in the end.
-- Throws an error if the consumer hasn't installed the clean room.
CALL samooha_by_snowflake_local_db.provider.list_pending_template_requests($CLEANROOM_1);
CALL samooha_by_snowflake_local_db.provider.approve_template_request($CLEANROOM_1, '<REQUEST_ID>');
SET template_name = 'multiprovider_template'; -- From pending template requests.

CALL samooha_by_snowflake_local_db.provider.list_pending_template_requests($CLEANROOM_2);
CALL samooha_by_snowflake_local_db.provider.approve_template_request($CLEANROOM_2, '<REQUEST_ID>');

-- Set column policies. Column policies are tied to a specific template and table so we needed to add the
-- template first.
call samooha_by_snowflake_local_db.provider.set_column_policy($CLEANROOM_1,
  [$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',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:device_type']);

call samooha_by_snowflake_local_db.provider.set_column_policy($CLEANROOM_2,
  [$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',
   $template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:device_type']);

-- Enable multiprovider flow requests. Combine your org and account name
-- with the clean room name, using . between each segment.
CALL samooha_by_snowflake_local_db.provider.enable_multiprovider_computation(
  $CLEANROOM_1,
  $consumer_locator,
  [CONCAT('<PROVIDER_ORG_NAME>.<PROVIDER_ACCOUNT_NAME>.', $CLEANROOM_2)]);
  
CALL samooha_by_snowflake_local_db.provider.enable_multiprovider_computation(
  $CLEANROOM_2,
  $consumer_locator,
  [CONCAT('<PROVIDER_ORG_NAME>.<PROVIDER_ACCOUNT_NAME>.', $CLEANROOM_1)]);

-- SWITCH TO CONSUMER to add column policies and start the multi-provider flow.

-- View, then approve the request in each clean room. After this, the consumer can execute the query as often as they like.
-- The request ID is the same for all providers for the same consumer request.
-- Note that if differential privacy is enabled for the consumer, template, or clean room, the consumer has
-- a privacy budget that can limit the number of times they can execute the query.
-- Pass in -1 for the request ID to auto-approve all requests from the specified consumer.
CALL samooha_by_snowflake_local_db.provider.view_multiprovider_requests($CLEANROOM_1, $consumer_locator);
CALL samooha_by_snowflake_local_db.provider.process_multiprovider_request($CLEANROOM_1, $consumer_locator, '<REQUEST_ID>');
CALL samooha_by_snowflake_local_db.provider.process_multiprovider_request($CLEANROOM_2, $consumer_locator, '<REQUEST_ID>');

-- SWITCH TO CONSUMER to run the query

-- You can view the flow request logs here. A request is added to the log when it has been approved, either manually or automatically,
-- or if the request fails for some reason.
SELECT * FROM samooha_cleanroom_<CLEANROOM_1>.admin.REQUEST_LOG_MULTIPROVIDER;
SELECT * FROM samooha_cleanroom_<CLEANROOM_2>.admin.REQUEST_LOG_MULTIPROVIDER;

-- To unapprove a given consumer, call this:
-- UPDATE samooha_cleanroom_multiprovider_room_1.admin.REQUEST_LOG_MULTIPROVIDER SET APPROVED=FALSE WHERE PARTY_ACCOUNT=$consumer_locator;

-- Clean up
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($CLEANROOM_1);
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($CLEANROOM_2);
