-- Lookalike audience modeling with ML Jobs: publisher worksheet
-- Run this worksheet from the publisher account.
--
-- The publisher provides user feature data to the clean room. The advertiser
-- brings their proprietary lookalike model, trains it on the publisher's
-- features, scores the publisher population, and activates results back.
--
-- The publisher registers their data offering, reviews and joins the
-- collaboration, and processes the activation results.

-- The publisher and advertiser must be in the same organization and same cloud
-- hosting region, unless Cross-Cloud Auto-Fulfillment is enabled.

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

-- Publisher and advertiser data can be generated by running the sample data
-- generator Python script linked from the documentation.

-- 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 publisher feature data offering.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
$$
api_version: 2.0.0
spec_type: data_offering
name: lal_mljobs_publisher_features
version: V0
description: Publisher user features for lookalike model training and scoring.
datasets:
  - alias: features
    data_object_fqn: <database.schema.LAL_PUBLISHER_FEATURES>
    allowed_analyses: template_only
    object_class: custom
    schema_and_template_policies:
      HASHED_EMAIL:
        category: join_custom
      STATUS:
        category: passthrough
      AGE_BAND:
        category: passthrough
      REGION_CODE:
        category: passthrough
      DAYS_ACTIVE:
        category: passthrough
$$);

SET publisher_data_offering_id = '<publisher_data_offering_id>';


--------------------------------------------------------------
-- Step 2: Review and join the collaboration.
--
-- Wait until the advertiser has created the collaboration
-- and it appears with status INVITED.
--------------------------------------------------------------

SET collaboration_name = 'lal_mljobs_collab';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.REVIEW($collaboration_name, '<advertiser_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 publisher data offering.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LINK_DATA_OFFERING(
    $collaboration_name,
    $publisher_data_offering_id,
    ['advertiser']
);


--------------------------------------------------------------
-- SWITCH TO ADVERTISER to run the ML jobs pipeline.
-- Continue here after the advertiser activates results.
--------------------------------------------------------------


--------------------------------------------------------------
-- Step 4: Process the activation results.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_ACTIVATIONS($collaboration_name);

-- When activation status is SHARED, process it to import the data.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.PROCESS_ACTIVATION($collaboration_name);

-- Query the activated lookalike audience.
-- Find the installed app name for this collaboration.
SHOW APPLICATIONS LIKE 'SFDCR_%';
SET installed_app_name = '<installed_app_name from SHOW APPLICATIONS>';

SELECT
    RECORDS:HASHED_EMAIL::STRING AS HASHED_EMAIL,
    RECORDS:SCORE::FLOAT AS SCORE
FROM IDENTIFIER($installed_app_name || '.ACTIVATION.SEGMENT_RECORDS')
WHERE SEGMENT_NAME = 'lal_mljobs_activation'
ORDER BY SCORE DESC;


--------------------------------------------------------------
-- Cleanup
--------------------------------------------------------------

-- CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.LEAVE($collaboration_name);
