-- Example provider code for provider-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 activation';
SET template_name = 'activation_provider_activation_template';
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.
-- Whenever data is pushed to the provider's account, you must link the samooha_by_snowflake_local_db.library.temp_public_key table
-- to enable decryption of results in the provider's account.
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']);
CALL samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

-- 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. If you later modify it, the consumer must call consumer.enable_templates_for_provider_run and 
-- consumer.approve_provider_activation_consent`` for the changes to take effect.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $template_name, 
    $$
BEGIN
  CREATE OR REPLACE TABLE cleanroom.temp_result_data AS
    SELECT COUNT(c.status) 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 'temp_result_data';
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);

-- Enable provider run analysis. Important: This must be called after the consumer is added but before the consumer installs the clean room.
CALL samooha_by_snowflake_local_db.provider.enable_provider_run_analysis($cleanroom_name, [$consumer_locator]);

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

-- SWITCH TO CONSUMER to install and configure the clean room.

-- See if consumer has installed the clean room yet. Query will return a row if they have.
SELECT * FROM snowflake.data_sharing_usage.application_state WHERE consumer_account_locator = $consumer_locator AND CONTAINS(package_name, UPPER(REPLACE($cleanroom_name, ' ', '_')));

-- After the consumer has installed the clean room, request to run the template.
CALL samooha_by_snowflake_local_db.provider.request_provider_activation_consent($cleanroom_name, $template_name);

-- SWITCH TO CONSUMER to approve the activation request.

-- You need to mount request logs in order to share consumer data for export.
-- If the consumer is in a different cloud hosting region than you, enable cross-cloud auto-fulfillment instead,
-- which mounts the request logs as well.
CALL samooha_by_snowflake_local_db.provider.mount_request_logs_for_all_consumers($cleanroom_name);

-- Provider activation is performed by calling submit_analysis_request on a template registered as an analysis template.
-- Save the returned request ID or run the query shown next to get the ID
-- Pass in temp public key table in the provider table list.
SET segment_name = 'my_segment';
CALL samooha_by_snowflake_local_db.provider.submit_analysis_request(
  $cleanroom_name,
  $consumer_locator,
  $template_name,
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS', 'samooha_by_snowflake_local_db.library.temp_public_key'], -- Provider tables
  ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'], -- Consumer tables
    object_construct(
      'dcr_internal_segment_name', $segment_name,
      'c_join_col', 'c.HASHED_EMAIL',
      'p_join_col', 'p.HASHED_EMAIL'
    )
);
SET request_id = <REQUEST_ID>; -- Request ID returned by previous call.

-- Check request status. It can take up to 2 minutes for this to pick up the request ID after the initial request.
CALL samooha_by_snowflake_local_db.provider.check_analysis_status(
    $cleanroom_name,
    $request_id,
    $consumer_locator
);

-- You can see all requests for this clean room and consumer with the following query.
-- SELECT * FROM  samooha_cleanroom_requests_<CLEANROOM_NAME>_<CONSUMER_LOCATOR>.shared.provider_analysis_status;

-- If query succeeds, view results.
-- Results are always appended to the same table.

-- Raw results (20 rows).
SELECT * 
  FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY 
  WHERE segment = $segment_name 
  ORDER BY timestamp desc LIMIT 20;

-- Parsed results (20 rows).
SELECT 
item:"AGE_BAND",
item:"STATUS",
item:"ITEM_COUNT"
FROM (SELECT parse_json(user_id) AS item FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY WHERE segment = $segment_name) ORDER BY item:"AGE_BAND", item:"STATUS" ASC LIMIT 20 ;

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