-- 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 provider template';
SET consumer_locator = '<consumer locator>';
SET consumer_account_name = '<consumer data sharing account identifier>';
SET basic_template_name = 'basic_template';
    
-- 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']);

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

-- Share with the consumer when the clean room is published.
CALL samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, $consumer_locator, $consumer_account_name);

-- Add a custom template.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $basic_template_name, 
    $$
    SELECT
      COUNT(*) AS total_count
    FROM IDENTIFIER({{ my_table[0] }}) AS c 
      INNER JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON IDENTIFIER({{ consumer_id | join_policy }}) = IDENTIFIER({{ provider_id | join_policy }})
    {% if where_clause %}
    WHERE {{ where_clause | sqlsafe | column_policy }}
    {% endif %};
  $$
);

-- Set column policies. Column policies are tied to a specific template and table
-- so you must add the template first.
CALL samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name,
  [$basic_template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:STATUS',
   $basic_template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:AGE_BAND',
   $basic_template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:REGION_CODE',
   $basic_template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:DAYS_ACTIVE',
   $basic_template_name || ':SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS:DEVICE_TYPE'
   ]);
    
-- 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);

-- Clean up afterward.
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);
