-- Example provider code for consumer-run consumer activation.
-- See https://docs.snowflake.com/user-guide/cleanrooms/activation for details.

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

SET cleanroom_name = 'test consumer activation';
SET template_name = 'activation_hello_world_template';

-- Replace with the consumer account information
SET consumer_locator = <CONSUMER_LOCATOR>;

SET consumer_account_name = <ORG_NAME>.<ACCOUNT_NAME>;

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

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

-- Add the template
call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $template_name, 
    $$
BEGIN
  CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
    SELECT count(*) AS ITEM_COUNT, c.status, c.age_band 
    FROM IDENTIFIER({{ my_table[0] }}) AS c
    JOIN IDENTIFIER({{ source_table[0] }}) AS p 
    ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
    GROUP BY c.status, c.age_band 
    ORDER BY c.age_band;
    RETURN 'analysis_results';
END;
    $$);

-- Enable consumer activation
CALL samooha_by_snowflake_local_db.provider.enable_template_for_consumer_activation(
    $cleanroom_name, $template_name);

-- Set activation policy
call samooha_by_snowflake_local_db.provider.set_activation_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:HASHED_EMAIL']);

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

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

-- SWITCH TO CONSUMER to install the clean room and run the activation.

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