-- ============================================================================
-- Single-account, multi-role Collaboration Clean Rooms demo
-- ============================================================================
-- This example demonstrates a Snowflake Data Clean Rooms collaboration using
-- a single Snowflake account. Three custom RBAC roles simulate three parties
-- with different privilege levels, demonstrating granular access control.
--
-- COLLABORATION_CREATOR: Creates the collaboration, registers data offerings
--   in custom and default registries, creates custom registries, and links
--   data offerings into the collaboration. Grants object-level privileges
--   to the other roles.
--
-- TEMPLATE_CREATOR: Registers templates and adds them to the collaboration
--   via template requests. Has read and register access to custom registries.
--
-- EU_SALES_TEAM: Can view collaboration resources and run analyses, but
--   cannot register or link any resources. Has read privileges on one custom
--   registry.
--
-- EMEA_SALES_TEAM: Same privileges as EU_SALES_TEAM. Views collaboration
--   resources and runs an analysis. Has read privileges on the other custom
--   registry.
--
-- None of the custom roles inherit SAMOOHA_APP_ROLE. SAMOOHA_APP_ROLE is
-- used only to create sample databases. ACCOUNTADMIN is used to create
-- roles and grant account-level privileges.
--
-- For more information, see:
--   docs.snowflake.com/user-guide/cleanrooms/overview
--   docs.snowflake.com/user-guide/cleanrooms/registries
--   docs.snowflake.com/user-guide/cleanrooms/roles
-- ============================================================================


-- ============================================================================
-- SETUP: Create roles and grant privileges.
-- ============================================================================

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS COLLABORATION_CREATOR;
CREATE ROLE IF NOT EXISTS TEMPLATE_CREATOR;
CREATE ROLE IF NOT EXISTS EU_SALES_TEAM;
CREATE ROLE IF NOT EXISTS EMEA_SALES_TEAM;

-- Grant warehouse access to all roles.
GRANT USAGE ON WAREHOUSE APP_WH TO ROLE COLLABORATION_CREATOR;
GRANT USAGE ON WAREHOUSE APP_WH TO ROLE TEMPLATE_CREATOR;
GRANT USAGE ON WAREHOUSE APP_WH TO ROLE EU_SALES_TEAM;
GRANT USAGE ON WAREHOUSE APP_WH TO ROLE EMEA_SALES_TEAM;

-- COLLABORATION_CREATOR needs these manual account-level privileges,
-- which are required by the CREATE COLLABORATION DCR privilege.
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE COLLABORATION_CREATOR;
GRANT CREATE APPLICATION ON ACCOUNT TO ROLE COLLABORATION_CREATOR;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE COLLABORATION_CREATOR;
GRANT CREATE LISTING ON ACCOUNT TO ROLE COLLABORATION_CREATOR;
GRANT CREATE SHARE ON ACCOUNT TO ROLE COLLABORATION_CREATOR;
GRANT IMPORT SHARE ON ACCOUNT TO ROLE COLLABORATION_CREATOR;
GRANT MANAGE SHARE TARGET ON ACCOUNT TO ROLE COLLABORATION_CREATOR;

-- TEMPLATE_CREATOR needs IMPORT SHARE for the VIEW COLLABORATIONS privilege.
GRANT IMPORT SHARE ON ACCOUNT TO ROLE TEMPLATE_CREATOR;

-- Grant roles to the current user so you can switch between them.
SET current_username = CURRENT_USER();
GRANT ROLE COLLABORATION_CREATOR TO USER IDENTIFIER($current_username);
GRANT ROLE TEMPLATE_CREATOR TO USER IDENTIFIER($current_username);
GRANT ROLE EU_SALES_TEAM TO USER IDENTIFIER($current_username);
GRANT ROLE EMEA_SALES_TEAM TO USER IDENTIFIER($current_username);

-- Grant DCR account-level privileges using GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE.
-- This procedure requires the ACCOUNTADMIN role.

-- COLLABORATION_CREATOR: create collaborations, create registries,
-- and register data offerings.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE(
  'CREATE COLLABORATION', 'COLLABORATION_CREATOR');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE(
  'CREATE REGISTRY', 'COLLABORATION_CREATOR');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE(
  'REGISTER DATA OFFERING', 'COLLABORATION_CREATOR');

-- TEMPLATE_CREATOR: register templates, view registered templates,
-- and view collaborations.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE(
  'REGISTER TEMPLATE', 'TEMPLATE_CREATOR');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE(
  'VIEW REGISTERED TEMPLATES', 'TEMPLATE_CREATOR');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_ACCOUNT_TO_ROLE(
  'VIEW COLLABORATIONS', 'TEMPLATE_CREATOR');

-- EU_SALES_TEAM and EMEA_SALES_TEAM receive no account-level DCR privileges.
-- Object-level READ and RUN are granted later by COLLABORATION_CREATOR.

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

-- Use SAMOOHA_APP_ROLE to create sample databases, then transfer ownership
-- to COLLABORATION_CREATOR.
USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE APP_WH;

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

-- Potential customers: all rows from CUSTOMERS.
CREATE OR REPLACE TABLE CREATOR_DB.DATA_SCH.POTENTIAL_CUSTOMERS_DATA AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

-- EU customers: 300 rows from CUSTOMERS.
CREATE OR REPLACE TABLE CREATOR_DB.DATA_SCH.CUSTOMERS_EU_DATA AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
  LIMIT 300;

-- EMEA customers: 300 rows from CUSTOMERS_2.
CREATE OR REPLACE TABLE CREATOR_DB.DATA_SCH.CUSTOMERS_EMEA_DATA AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS_2
  LIMIT 300;

-- Transfer ownership of the database, schema, and tables to COLLABORATION_CREATOR.
GRANT OWNERSHIP ON DATABASE CREATOR_DB TO ROLE COLLABORATION_CREATOR COPY CURRENT GRANTS;
GRANT OWNERSHIP ON SCHEMA CREATOR_DB.DATA_SCH TO ROLE COLLABORATION_CREATOR COPY CURRENT GRANTS;
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA CREATOR_DB.DATA_SCH TO ROLE COLLABORATION_CREATOR COPY CURRENT GRANTS;


-- ===============================================================================
-- COLLABORATION_CREATOR: Create registries, register data, create collaboration.
-- ===============================================================================

USE ROLE COLLABORATION_CREATOR;
USE WAREHOUSE APP_WH;
USE SECONDARY ROLES NONE;

-- Create two custom registries for regional data offerings.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.CREATE_REGISTRY(
  'DATA_REGISTRY_EU', 'DATA OFFERING');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.CREATE_REGISTRY(
  'DATA_REGISTRY_EMEA', 'DATA OFFERING');

-- Register potential_customers data offering in the default registry.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: potential_customers
  datasets:
    - alias: potential_customers
      data_object_fqn: CREATOR_DB.DATA_SCH.POTENTIAL_CUSTOMERS_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
        age_band:
          category: passthrough
  $$
);

SET potential_customers_data_offering_id = '<potential_customers_data_offering_id>';

-- Register the EU data offering in the DATA_REGISTRY_EU custom registry.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  'DATA_REGISTRY_EU',
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: customers_eu
  datasets:
    - alias: eu_customers
      data_object_fqn: CREATOR_DB.DATA_SCH.CUSTOMERS_EU_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
        age_band:
          category: passthrough
  $$
);

SET eu_data_offering_id = '<eu_data_offering_id>';

-- Register the EMEA data offering in the DATA_REGISTRY_EMEA custom registry.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  'DATA_REGISTRY_EMEA',
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: customers_emea
  datasets:
    - alias: emea_customers
      data_object_fqn: CREATOR_DB.DATA_SCH.CUSTOMERS_EMEA_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
        age_band:
          category: passthrough
  $$
);

SET emea_data_offering_id = '<emea_data_offering_id>';

-- Create and join the collaboration. COLLABORATION_CREATOR is the owner,
-- analysis runner, and data provider. The potential_customers data offering
-- is hard-coded in the collaboration spec; the EU and EMEA offerings are
-- linked afterward.
-- 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: three_role_demo
  owner: creator
  collaborator_identifier_aliases:
    creator: <account_data_sharing_id>
  analysis_runners:
    creator:
      data_providers:
        creator:
          data_offerings:
            - id: <potential_customers_data_offering_id>
      templates: []
  $$ ,
  '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);

-- Enable auto-approval for template requests. In a single-account scenario,
-- requests affecting your own account are self-approved, but enabling
-- auto-approval ensures all future requests are approved automatically.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.ENABLE_TEMPLATE_AUTO_APPROVAL(
  $collaboration_name
);

-- Link the EU and EMEA data offerings into the collaboration.
-- Share only with yourself, because you are the only collaborator in the collaboration.
-- In a real-world collaboration, you would grant access to the appropriate account aliases.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
  $collaboration_name, $eu_data_offering_id, ['creator']);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
  $collaboration_name, $emea_data_offering_id, ['creator']);

-- Verify that all three data offerings are now in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);


-- ============================================================================
-- COLLABORATION_CREATOR: Grant object-level privileges to the other roles.
-- ============================================================================

-- GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE must be called by the role that created
-- the object. COLLABORATION_CREATOR created the collaboration and registries.

-- TEMPLATE_CREATOR: READ to view collaboration resources, UPDATE to add
-- template requests and manage update requests.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'READ', 'COLLABORATION', $collaboration_name, 'TEMPLATE_CREATOR');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'UPDATE', 'COLLABORATION', $collaboration_name, 'TEMPLATE_CREATOR');

-- EU_SALES_TEAM: READ to view collaboration resources, RUN to run analyses,
-- and READ access to the DATA_REGISTRY_EU custom registry only.
-- In this example the registry restriction doesn't matter, because after a
-- resource is linked into a collaboration, each user can see all the
-- resources that are assigned to them in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'READ', 'COLLABORATION', $collaboration_name, 'EU_SALES_TEAM');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'RUN', 'COLLABORATION', $collaboration_name, 'EU_SALES_TEAM');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'READ', 'REGISTRY', 'DATA_REGISTRY_EU', 'EU_SALES_TEAM');

-- EMEA_SALES_TEAM: READ to view collaboration resources, RUN to run analyses,
-- and READ access to the DATA_REGISTRY_EMEA custom registry only.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'READ', 'COLLABORATION', $collaboration_name, 'EMEA_SALES_TEAM');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'RUN', 'COLLABORATION', $collaboration_name, 'EMEA_SALES_TEAM');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.ADMIN.GRANT_PRIVILEGE_ON_OBJECT_TO_ROLE(
  'READ', 'REGISTRY', 'DATA_REGISTRY_EMEA', 'EMEA_SALES_TEAM');


-- ============================================================================
-- TEMPLATE_CREATOR: Register templates and add them to the collaboration.
-- ============================================================================

USE ROLE TEMPLATE_CREATOR;
USE WAREHOUSE APP_WH;
USE SECONDARY ROLES NONE;

-- View the collaboration and its current data offerings.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_COLLABORATIONS();

-- 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_template
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 returns a static value without accessing any data.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: static_value_template
version: V1
type: sql_analysis
description: Returns a static value without accessing any data offerings.
template:
  SELECT 1 AS col_a, 2 AS col_b, 3 AS col_c;
$$
);

SET no_data_query_id = '<no_data_query_id>';

-- View registered templates to confirm both templates were registered.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_TEMPLATES();

-- Add both templates to the collaboration for the 'creator' analysis runner.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.ADD_TEMPLATE_REQUEST(
  $collaboration_name, $age_band_template_id, ['creator']);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.ADD_TEMPLATE_REQUEST(
  $collaboration_name, $no_data_query_id, ['creator']);

-- Verify the templates were added to the collaboration.
-- The add template requests are auto-approved for two reasons: requests to share a
-- template with yourself are auto-approved; also, you set ENABLE_TEMPLATE_AUTO_APPROVAL
-- for the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);

-- Attempt to run the static value template. This call FAILS because TEMPLATE_CREATOR
-- was granted READ and UPDATE on the collaboration, which allows registering and
-- adding templates, but was not granted the RUN privilege needed to run analyses.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: This analysis should fail due to insufficient privileges.
  template: '<static_value_template_name_and_version>'
  $$
);
-- Expected error: SQL compilation error: Unknown user-defined function SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN.


-- ============================================================================
-- EU_SALES_TEAM: View resources, run analyses, and try to register a template.
-- ============================================================================

USE ROLE EU_SALES_TEAM;
USE WAREHOUSE APP_WH;
USE SECONDARY ROLES NONE;

-- View the templates available in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);

-- View the registered data offerings from both custom registries. Only DATA_REGISTRY_EU is accessible
-- to EU_SALES_TEAM. However, the data offerings already linked in to their analysis runner entry in
-- the collaboration are visible in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS('DATA_REGISTRY_EU');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS('DATA_REGISTRY_EMEA');

-- View the data offerings available in the collaboration.
-- Use the TEMPLATE_VIEW_NAME column values in the `view_mappings.source_tables` list in the
-- analysis request.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);

-- Run the age_band_count_template with two data offerings.
-- 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 static template that failed for the TEMPLATE_CREATOR.
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: 'static_value_template_V1' # '<static_value_template>'
  $$
);

-- Attempt to register a template in the default registry. This call FAILS
-- because EU_SALES_TEAM was not granted the REGISTER TEMPLATE privilege.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: unauthorized_template
version: V1
type: sql_analysis
description: This registration should fail due to insufficient privileges.
template:
  SELECT COUNT(*) FROM IDENTIFIER({{ source_table[0] }});
$$
);
-- Expected error: SQL compilation error: Unknown user-defined function SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE.


-- ============================================================================
-- EMEA_SALES_TEAM: View resources and run an analysis.
-- ============================================================================

USE ROLE EMEA_SALES_TEAM;
USE WAREHOUSE APP_WH;
USE SECONDARY ROLES NONE;

-- View the templates available in the collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);

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

-- Run the age_band_count_template with two data offerings.
-- Replace placeholders with the template name/version and view names from
-- VIEW_TEMPLATES and VIEW_DATA_OFFERINGS.

-- View the registered data offerings from both custom registries. Only DATA_REGISTRY_EMEA is accessible
-- to EMEA_SALES_TEAM.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS('DATA_REGISTRY_EU');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS('DATA_REGISTRY_EMEA');

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


-- ============================================================================
-- CLEANUP: Delete the collaboration, registries, database, and roles.
-- ============================================================================

USE ROLE COLLABORATION_CREATOR;

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

-- Drop the custom registries and their contents.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.DROP_REGISTRY('DATA_REGISTRY_EU');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.DROP_REGISTRY('DATA_REGISTRY_EMEA');

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

-- Drop the custom roles.
USE ROLE ACCOUNTADMIN;
DROP ROLE IF EXISTS EMEA_SALES_TEAM;
DROP ROLE IF EXISTS EU_SALES_TEAM;
DROP ROLE IF EXISTS TEMPLATE_CREATOR;
DROP ROLE IF EXISTS COLLABORATION_CREATOR;
