-- Inventory forecasting collaboration: advertiser worksheet
-- Run this worksheet from the advertiser's account.
-- The advertiser is the analysis runner. The advertiser registers a data offering
-- with their current stock levels, joins the collaboration created by the publisher,
-- links their data, runs the analysis template, and activates results to the publisher.

-- 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 this template, see the Snowflake documentation:
-- https://docs.snowflake.com/user-guide/cleanrooms/collab-inventory-forecasting

-- Advertiser and publisher 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;


--------------------------------------------------------------
-- Register the advertiser's data offering.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
$$
api_version: 2.0.0
spec_type: data_offering
name: advertiser_stock_levels
version: V0
description: >
  Advertiser current inventory levels and upcoming promotion schedule
  by product and store.
datasets:
  - alias: stock_levels
    data_object_fqn: <database.schema.INVENTORY_ADVERTISER_STOCK_LEVELS>
    allowed_analyses: template_only
    object_class: custom
    schema_and_template_policies:
      PRODUCT_ID:
        category: join_custom
      STORE_ID:
        category: join_custom
      CURRENT_INVENTORY:
        category: passthrough
      UPCOMING_PROMOTION_DATE:
        category: passthrough
$$);

-- Save the data offering ID returned by the procedure.
SET advertiser_data_offering_id = '<advertiser_data_offering_id>';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS();


--------------------------------------------------------------
-- Review and join the collaboration.
--------------------------------------------------------------

SET collaboration_name = 'inventory_forecasting_collab';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.REVIEW($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.JOIN($collaboration_name, 'APP_WH');

-- Wait until status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);


--------------------------------------------------------------
-- Link the advertiser's data offering to the collaboration.
--------------------------------------------------------------

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


--------------------------------------------------------------
-- View available resources.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);


--------------------------------------------------------------
-- Run the analysis template.
--------------------------------------------------------------

-- Use the TEMPLATE_VIEW_NAME values from VIEW_DATA_OFFERINGS for the source_tables entries.
-- The template field requires the template ID from VIEW_TEMPLATES.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    name: inventory_forecast_run
    description: Forecast product demand for the next 30 days
    template: <analysis_template_id>
    template_configuration:
      view_mappings:
        source_tables:
          - publisher.<publisher_data_offering_id>.sales_history
          - advertiser.<advertiser_data_offering_id>.stock_levels
      arguments:
        forecast_days: 30
        promo_duration_days: 7
        publisher_join_key_product: PRODUCT_ID
        publisher_join_key_store: STORE_ID
        publisher_units_sold_col: UNITS_SOLD
        publisher_promo_flag_col: WAS_ON_PROMOTION
        advertiser_join_key_product: PRODUCT_ID
        advertiser_join_key_store: STORE_ID
        advertiser_inventory_col: CURRENT_INVENTORY
        advertiser_promo_date_col: UPCOMING_PROMOTION_DATE
    $$
);


--------------------------------------------------------------
-- Activate results to the publisher.
--------------------------------------------------------------

-- Run the activation template to save forecast results to the publisher's account.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    name: inventory_forecast_activation
    description: Activate inventory forecast results to the publisher
    template: <activation_template_id>
    template_configuration:
      view_mappings:
        source_tables:
          - publisher.<publisher_data_offering_id>.sales_history
          - advertiser.<advertiser_data_offering_id>.stock_levels
      arguments:
        forecast_days: 30
        promo_duration_days: 7
        publisher_join_key_product: PRODUCT_ID
        publisher_join_key_store: STORE_ID
        publisher_units_sold_col: UNITS_SOLD
        publisher_promo_flag_col: WAS_ON_PROMOTION
        advertiser_join_key_product: PRODUCT_ID
        advertiser_join_key_store: STORE_ID
        advertiser_inventory_col: CURRENT_INVENTORY
        advertiser_promo_date_col: UPCOMING_PROMOTION_DATE
      activation:
        snowflake_collaborator: publisher
        segment_name: inventory_forecast_activation_1
    $$
);


--------------------------------------------------------------
-- SWITCH TO PUBLISHER to view and process the activation results.
--------------------------------------------------------------

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