-- Basic Snowflake Collaboration Data Clean Rooms example.
-- This file represents user "bob" in a two-collaborator clean room example.

-- Run this worksheet in a Snowflake account with access to the latest version of
-- Snowflake Data Clean Rooms.

-- This file  demonstrates the following actions:
-- * Joining a collaboration
-- * Registering and adding a template and a data offering to an existing collaboration.
-- * Running an analysis.

-- For more information, read docs.snowflake.com/user-guide/cleanrooms/v2/using

USE WAREHOUSE APP_WH;
USE ROLE SAMOOHA_APP_ROLE;

-- Secondary roles can't be active when calling join or link_data_offering.
USE SECONDARY ROLES NONE;

-- Create sample data.
CREATE DATABASE IF NOT EXISTS BOB_DB;
CREATE SCHEMA IF NOT EXISTS BOB_DB.BOB_SCH;
CREATE OR REPLACE TABLE BOB_DB.BOB_SCH.BOB_DATA AS SELECT * FROM samooha_sample_database.demo.customers_2 LIMIT 100;

-- See which collaborations you are invited to, or have joined.
CALL samooha_by_snowflake_local_db.collaboration.view_collaborations();

-- Use SOURCE_NAME column value from the response to view_collaborations().
SET collaboration_name = '<collaboration name>';

-- Use OWNER_ACCOUNT column value from the response to view_collaborations().
SET collaborator_data_sharing_id = '<collaborator_id>';

-- Review and join the collaboration.
-- Joining is asynchronous, so you must call get_status until the status is JOINED before
-- you can perform actions on the collaboration.
CALL samooha_by_snowflake_local_db.collaboration.review($collaboration_name, $collaborator_data_sharing_id);
CALL samooha_by_snowflake_local_db.collaboration.join($collaboration_name);
CALL samooha_by_snowflake_local_db.collaboration.get_status($collaboration_name);

-- Demonstrate the auto-approve flow.
-- Alice enabled auto-approve on her account, so this request will
-- be auto-approved, and the template will be added immediately.

-- Create a template.
CALL samooha_by_snowflake_local_db.registry.register_template(
    $$
    api_version: 2.0.0
    spec_type: template
    name: auto_approve_template
    version: V1
    type: sql_analysis
    description: test_description
    template:
      SELECT * FROM IDENTIFIER({{ SOURCE_TABLE[0] }}) LIMIT 10;
    $$
);
SET auto_approve_template = '<template_id>';

CALL samooha_by_snowflake_local_db.collaboration.add_template_request($collaboration_name, $auto_approve_template, ['alice', 'bob']);
CALL samooha_by_snowflake_local_db.collaboration.view_update_requests($collaboration_name);

-- SWITCH TO other account and request adding a template, and then come back to approve the request.

-- You haven't enabled template auto-approve, so you must approve the request before the template is added.
CALL samooha_by_snowflake_local_db.collaboration.view_update_requests($collaboration_name);
CALL samooha_by_snowflake_local_db.collaboration.approve_update_request(
  $collaboration_name,
  '<request_ID>'
);

-- SWITCH TO bob to see the request status.

-- Register your own data offering.
CALL samooha_by_snowflake_local_db.registry.register_data_offering(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v3
    name: bob_data
    datasets:
     - alias: my_customer_list
       data_object_fqn: BOB_DB.BOB_SCH.BOB_DATA
       object_class: custom
       allowed_analyses: template_only
       schema_and_template_policies:
         hashed_email:
           category: join_standard
           column_type: hashed_email_b64_encoded
         status:
           category: passthrough
    $$
);

SET my_data_id = '<data offering id>';

-- Share the data offering with yourself and alice.
CALL samooha_by_snowflake_local_db.collaboration.link_data_offering(
  $collaboration_name,
  $my_data_id,
  ['alice', 'bob']
);

CALL samooha_by_snowflake_local_db.collaboration.view_data_offerings(
  $collaboration_name
);

-- View templates that you can use in this collaboration. You can run only templates that list you in the
-- SHARED_WITH column.
CALL samooha_by_snowflake_local_db.collaboration.view_templates($collaboration_name);

-- Run an analysis with your template.
CALL samooha_by_snowflake_local_db.collaboration.run(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    description: <optional description of the analysis>
    template:  '<both_use_template>'
    template_configuration:
      view_mappings:
        source_tables:
          -  '<my_data_offering_view_name>'
          -  '<bob_data_offering_view_name>'
    $$
);
  
-- SWITCH TO other account to run an analysis.

-- Try running an analysis using alice-only template.
-- This will fail, because you aren't listed as an analysis
-- runner for this template.
CALL samooha_by_snowflake_local_db.collaboration.run(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: <optional description of the analysis>
  template: '<alice_only_template>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<my_data_offering_view_name>'
        - '<bob_data_offering_view_name>'
  $$
);

-- Clean up resources.
DROP DATABASE BOB_DB;
