-- ============================================================================
-- Single-user Collaboration Clean Rooms demo
-- ============================================================================
-- This example demonstrates a basic Snowflake Data Clean Rooms collaboration
-- using a single Snowflake account and a single role: SAMOOHA_APP_ROLE.
-- One user acts as the owner, data provider, and analysis runner.
--
-- The user creates two sample datasets, registers two data offerings and two
-- templates, then creates a collaboration with one data offering and one template each.
--  After the collaboration is created, the user links the remaining data offering and
-- template, then runs an analysis with each template. Finally, the code
-- cleans up all resources used.
--
-- For more information, see:
--   docs.snowflake.com/user-guide/cleanrooms/overview
--   docs.snowflake.com/user-guide/cleanrooms/spec-reference
-- ============================================================================


-- ============================================================================
-- SETUP: Create sample databases and data.
-- ============================================================================

USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE APP_WH;

-- You can't use secondary roles with most collaboration procedures.
USE SECONDARY ROLES NONE;

CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.DATA_SCH;

-- Dataset 1: 300 rows from CUSTOMERS.
CREATE OR REPLACE TABLE DEMO_DB.DATA_SCH.CUSTOMERS_1 AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
  LIMIT 300;

-- Dataset 2: 300 rows from CUSTOMERS_2.
CREATE OR REPLACE TABLE DEMO_DB.DATA_SCH.CUSTOMERS_2 AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS_2
  LIMIT 300;


-- ============================================================================
-- Register data offerings and templates.
-- ============================================================================

-- Register the first data offering.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: customers_1
  datasets:
    - alias: customers_1
      data_object_fqn: DEMO_DB.DATA_SCH.CUSTOMERS_1
      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
        age_band:
          category: passthrough
  $$
);

SET data_offering_1_id = '<data_offering_1_id>';

-- Register the second data offering.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: customers_2
  datasets:
    - alias: customers_2
      data_object_fqn: DEMO_DB.DATA_SCH.CUSTOMERS_2
      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
        age_band:
          category: passthrough
  $$
);

SET data_offering_2_id = '<data_offering_2_id>';

-- Register a template that joins two tables on hashed_email and returns
-- a count of rows grouped by age_band.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: age_band_count
version: V1
type: sql_analysis
description: Joins two tables on hashed_email and returns age_band with row counts.
template:
  SELECT t1.age_band, COUNT(t1.age_band) AS age_band_count
    FROM IDENTIFIER({{ source_table[0] }}) AS t1
      JOIN IDENTIFIER({{ source_table[1] }}) AS t2
      ON t1.hashed_email_b64_encoded = t2.hashed_email_b64_encoded
    GROUP BY t1.age_band;
$$
);

SET age_band_template_id = '<age_band_template_id>';

-- Register a template that joins two tables on hashed_email and returns
-- a count of rows grouped by status.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: status_count
version: V1
type: sql_analysis
description: Joins two tables on hashed_email and returns status with row counts.
template:
  SELECT t1.status, COUNT(t1.status) AS status_count
    FROM IDENTIFIER({{ source_table[0] }}) AS t1
      JOIN IDENTIFIER({{ source_table[1] }}) AS t2
      ON t1.hashed_email_b64_encoded = t2.hashed_email_b64_encoded
    GROUP BY t1.status;
$$
);

SET status_template_id = '<status_template_id>';

-- Confirm that both data offerings and both templates are registered.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS();
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_TEMPLATES();


-- ============================================================================
-- Create the collaboration with one data offering and one template.
-- ============================================================================

-- Replace <account_data_sharing_id> with:
--   SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.INITIALIZE(
  $$
  api_version: 2.0.0
  spec_type: collaboration
  name: single_user_demo
  owner: me
  collaborator_identifier_aliases:
    me: <account_data_sharing_id>
  analysis_runners:
    me:
      data_providers:
        me:
          data_offerings:
            - id: <data_offering_1_id>
      templates:
        - id: <age_band_template_id>
  $$,
  'APP_WH'
);

SET collaboration_name = '<collaboration_name>';

-- Verify that the owner has joined. Repeat until status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);


-- ============================================================================
-- Link the remaining data offering and template into the collaboration.
-- ============================================================================

-- Link the second data offering.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
  $collaboration_name, $data_offering_2_id, ['me']);

-- Add the status_count template to the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.ADD_TEMPLATE_REQUEST(
  $collaboration_name, $status_template_id, ['me']);


-- ============================================================================
-- List resources and run analyses.
-- ============================================================================

-- List all data offerings in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);

-- List all templates in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);

-- Run the age_band_count template.
-- Replace placeholders with the template name/version and view names from
-- VIEW_TEMPLATES and VIEW_DATA_OFFERINGS.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: Count matching rows grouped by age_band.
  template: '<age_band_count_template_name_and_version>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<data_offering_view_1>'
        - '<data_offering_view_2>'
  $$
);

-- Run the status_count template.
-- Replace placeholders with the template name/version and view names from
-- VIEW_TEMPLATES and VIEW_DATA_OFFERINGS.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: Count matching rows grouped by status.
  template: '<status_count_template_name_and_version>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<data_offering_view_1>'
        - '<data_offering_view_2>'
  $$
);


-- ============================================================================
-- CLEANUP: Delete the collaboration, registered resources, and sample data.
-- ============================================================================

-- Teardown is a multi-step process. Call TEARDOWN, then wait for GET_STATUS
-- to report LOCAL_DROP_PENDING, then call TEARDOWN again.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- When GET_STATUS reports LOCAL_DROP_PENDING, call TEARDOWN again to complete.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);

-- Unregister the data offerings and templates from the default registry.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_DATA_OFFERING($data_offering_1_id);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_DATA_OFFERING($data_offering_2_id);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_TEMPLATE($age_band_template_id);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.UNREGISTER_TEMPLATE($status_template_id);

-- Drop the sample database.
DROP DATABASE IF EXISTS DEMO_DB;
