-- Example provider code for consumer-run provider 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 = 'consumer_run_provider_activation_template';

-- Replace with consumer account information
SET consumer_locator = <CONSUMER_LOCATOR>;
SET consumer_account_name = <ORG_NAME>.<ACCOUNT_NAME>;

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', 'samooha_by_snowflake_local_db.library.temp_public_key']);

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

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

-- If you do not have the clean rooms UI installed, uncomment and run this procedure.
-- Whether or not the consumer has the UI installed does not matter.
--CALL samooha_by_snowflake_local_db.provider.setup_provider_activation_share_mount_task(15);

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

-- View results
SET segment_name = 'my_consumer_run_provider_activation_segment';
SELECT * FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_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.PROVIDER_ACTIVATION_SUMMARY WHERE segment = $segment_name);

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