-- Example consumer 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';

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

-- Set activation policy
CALL samooha_by_snowflake_local_db.consumer.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']
    );

-- Run the activation
-- Use a different segment name for each run to distinguish results between runs.
-- Don't forget the TRUE last argument to specify that this is a consumer activation.
SET segment_name = 'my_activation_segment';
CALL samooha_by_snowflake_local_db.consumer.run_activation(
  $cleanroom_name,
  $segment_name,
  $template_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], 
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
  object_construct(
    'c_join_col', 'c.hashed_email',
    'p_join_col', 'p.hashed_email'
  ),
  TRUE);

-- View the data
SELECT * FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.CONSUMER_DIRECT_ACTIVATION_SUMMARY WHERE segment = $segment_name;
SELECT 
item:"AGE_BAND",
item:"ITEM_COUNT",
item:"STATUS"
FROM (SELECT parse_json(user_id) AS item FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.CONSUMER_DIRECT_ACTIVATION_SUMMARY WHERE segment = $segment_name);

 -- Clean up when done
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);
