USE ROLE samooha_app_role;
USE WAREHOUSE app_wh;
SET provider_account_locator = '<PROVIDER_LOCATOR>';
SET CLEANROOM_1 = 'multiprovider room 1';
SET CLEANROOM_2 = 'multiprovider room 2';
SET template_name = 'multiprovider_template';

-- Install and configure both clean rooms.

-- Install clean room 1.
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($CLEANROOM_1, $provider_account_locator);

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

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

-- Ask to add a custom template.
CALL samooha_by_snowflake_local_db.consumer.create_template_request(
  $CLEANROOM_1,
  $template_name,
  $$
SELECT COUNT (C.{{ group_by_col | sqlsafe }}), C.{{ group_by_col | sqlsafe }} 
  FROM {{ my_table[0] | sqlsafe}} AS C
    INNER JOIN {{ source_table[0] | sqlsafe }} AS P ON C.{{ join_col | sqlsafe }} = P.{{ join_col | sqlsafe }}
    INNER JOIN {{ source_table[1] | sqlsafe }} AS P2 ON P2.{{ join_col | sqlsafe }} = P.{{ join_col | sqlsafe }}
  GROUP BY C.{{ group_by_col | sqlsafe }};
  $$
);

-- Install clean room 2.
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($CLEANROOM_2, $provider_account_locator);

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

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

-- Request to add a custom template.
CALL samooha_by_snowflake_local_db.consumer.create_template_request(
   $CLEANROOM_2,
  $template_name,
  $$
SELECT COUNT (C.{{ group_by_col | sqlsafe }}), C.{{ group_by_col | sqlsafe }}
  FROM {{ my_table[0] | sqlsafe}} AS C
    INNER JOIN {{ source_table[0] | sqlsafe }} AS P ON C.{{ join_col | sqlsafe }} = P.{{ join_col | sqlsafe }}
    INNER JOIN {{ source_table[1] | sqlsafe }} AS P2 ON P2.{{ join_col | sqlsafe }} = P.{{ join_col | sqlsafe }}
  GROUP BY C.{{ group_by_col | sqlsafe }};
  $$
);

-- SWITCH TO PROVIDER to approve template requests.

-- List approved template requests.
CALL samooha_by_snowflake_local_db.consumer.list_template_requests($CLEANROOM_1);
CALL samooha_by_snowflake_local_db.consumer.list_template_requests($CLEANROOM_2);

-- When the provider approves, add the column policy
CALL samooha_by_snowflake_local_db.consumer.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.consumer.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']);

-- Start the multi-provider request flow.

-- Request running multi-clean-room analysis, providing all information for the query.
-- Save the returned request ID to use in the execution step.
CALL samooha_by_snowflake_local_db.consumer.prepare_multiprovider_flow(
    [$CLEANROOM_1, $CLEANROOM_2],
    $template_name,
    object_construct(
        'source_table', [
            CONCAT($CLEANROOM_1, '.SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'), 
            CONCAT($CLEANROOM_2, '.SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS')
        ],
        'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
        'group_by_col', 'age_band',
        'join_col','hashed_email'
        )
);

-- SWITCH TO PROVIDER to approve this request.

-- When the provider has approved the request, you can run the query.
CALL samooha_by_snowflake_local_db.consumer.execute_multiprovider_flow([$CLEANROOM_1, $CLEANROOM_2], <REQUEST_ID>);

-- Rerun the query with new values for group_by_col, join_col, or tables, and they will be automatically approved.
-- To query a different set of clean rooms, or add other template variables, call prepare_multiprovider_flow,
-- have the provider approve, and then call execute_multiprovider_flow.

-- Clean up
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($CLEANROOM_1);
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($CLEANROOM_2);
