-- Retail media measurement with HPO: RMN worksheet
-- Run this worksheet from the retail media network (RMN) account.
--
-- The RMN provides shopper transaction data to the clean room. The brand
-- brings their campaign exposure data and proprietary measurement model
-- with HPO. The RMN registers their data offering, reviews and joins the
-- collaboration, and links the data.

-- For details about ML Jobs, see the Snowflake documentation:
-- https://docs.snowflake.com/user-guide/cleanrooms/ml-jobs

-- Replace all values in angle brackets <...> with your own values.

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


--------------------------------------------------------------
-- Step 1: Register the RMN shopper transaction data offering.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
$$
api_version: 2.0.0
spec_type: data_offering
name: rmn_shopper_transactions
version: V0
description: Shopper purchase transactions for retail media measurement.
datasets:
  - alias: transactions
    data_object_fqn: <database.schema.SHOPPER_TRANSACTIONS>
    allowed_analyses: template_only
    object_class: custom
    schema_and_template_policies:
      USER_ID:
        category: join_custom
      PURCHASE_DATE:
        category: passthrough
      AMOUNT:
        category: passthrough
      CATEGORY:
        category: passthrough
$$);

SET rmn_data_offering_id = 'rmn_shopper_transactions_V0';


--------------------------------------------------------------
-- Step 2: Review and join the collaboration.
--------------------------------------------------------------

SET collaboration_name = 'rmn_measurement_collab';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.REVIEW($collaboration_name, '<brand_data_sharing_id>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.JOIN($collaboration_name);

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);


--------------------------------------------------------------
-- Step 3: Link the RMN data offering.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
    $collaboration_name,
    $rmn_data_offering_id,
    ['brand']
);


--------------------------------------------------------------
-- SWITCH TO BRAND to run the HPO + scoring pipeline.
--------------------------------------------------------------
