-- This is the consumer-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 consumer template';
SET template_name = 'basic_consumer_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']);

-- Request permission to add a custom template.
CALL samooha_by_snowflake_local_db.consumer.create_template_request(
  $cleanroom_name, 
  $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 %};
  $$);

-- SWITCH TO THE PROVIDER TO APPROVE THE REQUEST

-- Check the status of the request. When it is APPROVED, the template appears in the template list for the clean room.
CALL samooha_by_snowflake_local_db.consumer.list_template_requests($cleanroom_name);
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

-- Optionally set column properties on the template (not shown here).

-- 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);
