-- Inventory forecasting collaboration: publisher worksheet
-- Run this worksheet from the publisher's account.
-- The publisher is the collaboration owner and data provider. The publisher registers
-- a data offering, an analysis template, and an activation template, then creates the
-- collaboration for the advertiser to join.

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

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


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

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
$$
api_version: 2.0.0
spec_type: data_offering
name: publisher_sales_history
version: V0
description: >
  Publisher historical sales data including daily units sold and promotion
  status by product and store.
datasets:
  - alias: sales_history
    data_object_fqn: <database.schema.INVENTORY_PUBLISHER_SALES_HISTORY>
    allowed_analyses: template_only
    object_class: custom
    schema_and_template_policies:
      PRODUCT_ID:
        category: join_custom
      STORE_ID:
        category: join_custom
      SALES_DATE:
        category: passthrough
      UNITS_SOLD:
        category: passthrough
      WAS_ON_PROMOTION:
        category: passthrough
$$);

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


--------------------------------------------------------------
-- Register the analysis template.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: inventory_forecast_analysis
version: V0
type: sql_analysis
description: >
  Inventory Demand Forecast (by Promotion).
  Forecasts product demand by combining advertiser promotion plans with
  publisher historical sales data. Returns forecast demand and recommended
  restock quantities by product and store.
methodology: >
  Calculates historical baseline and promotional sales averages from the
  publisher's data. Joins this with the advertiser's upcoming promotion
  schedule to forecast total demand for a given period and recommend
  restock quantities.

parameters:
  - name: forecast_days
    description: Number of days to forecast (e.g., 30).
    type: integer
    default: 30
    required: true
  - name: promo_duration_days
    description: How many days the upcoming promotion will run.
    type: integer
    default: 7
    required: true
  - name: publisher_join_key_product
    description: Publisher column name for product ID (join-policy column).
    type: string
    required: true
  - name: publisher_join_key_store
    description: Publisher column name for store ID (join-policy column).
    type: string
    required: true
  - name: publisher_units_sold_col
    description: Publisher column for daily units sold.
    type: string
    required: true
  - name: publisher_promo_flag_col
    description: Publisher Boolean column indicating whether a day was a promotion day.
    type: string
    required: true
  - name: advertiser_join_key_product
    description: Advertiser column name for product ID (join-policy column).
    type: string
    required: true
  - name: advertiser_join_key_store
    description: Advertiser column name for store ID (join-policy column).
    type: string
    required: true
  - name: advertiser_inventory_col
    description: Advertiser column for current inventory level.
    type: string
    required: true
  - name: advertiser_promo_date_col
    description: Advertiser column for the upcoming promotion date.
    type: string
    required: true

template: |
  WITH publisher_sales_agg AS (
      SELECT
          {{ publisher_join_key_product | sqlsafe }} AS PRODUCT_ID,
          {{ publisher_join_key_store | sqlsafe }} AS STORE_ID,
          AVG(
              CASE
                  WHEN {{ publisher_promo_flag_col | sqlsafe }} = FALSE
                  THEN {{ publisher_units_sold_col | sqlsafe }}
                  ELSE NULL
              END
          ) AS avg_daily_sales_baseline,
          AVG(
              CASE
                  WHEN {{ publisher_promo_flag_col | sqlsafe }} = TRUE
                  THEN {{ publisher_units_sold_col | sqlsafe }}
                  ELSE NULL
              END
          ) AS avg_daily_sales_promo
      FROM IDENTIFIER({{ source_table[0] }})
      GROUP BY 1, 2
  ),
  advertiser_promo_plan AS (
      SELECT
          {{ advertiser_join_key_product | sqlsafe }} AS PRODUCT_ID,
          {{ advertiser_join_key_store | sqlsafe }} AS STORE_ID,
          {{ advertiser_inventory_col | sqlsafe }} AS current_inventory,
          {{ advertiser_promo_date_col | sqlsafe }} AS upcoming_promo_date
      FROM IDENTIFIER({{ source_table[1] }})
  ),
  forecast AS (
      SELECT
          c.PRODUCT_ID,
          c.STORE_ID,
          c.current_inventory,
          c.upcoming_promo_date,
          COALESCE(p.avg_daily_sales_baseline, 0) AS historical_baseline_sales,
          COALESCE(p.avg_daily_sales_promo, p.avg_daily_sales_baseline, 0) AS historical_promo_sales,
          (
              (historical_baseline_sales * ({{ forecast_days | sqlsafe }} - {{ promo_duration_days | sqlsafe }})) +
              (historical_promo_sales * {{ promo_duration_days | sqlsafe }})
          ) AS forecasted_demand_units
      FROM advertiser_promo_plan c
      LEFT JOIN publisher_sales_agg p
          ON c.PRODUCT_ID = p.PRODUCT_ID
          AND c.STORE_ID = p.STORE_ID
  )
  SELECT
      PRODUCT_ID,
      STORE_ID,
      current_inventory,
      upcoming_promo_date,
      historical_baseline_sales,
      historical_promo_sales,
      CAST(forecasted_demand_units AS INT) AS forecasted_demand,
      GREATEST(0, CAST(forecasted_demand - current_inventory AS INT)) AS recommended_restock_units
  FROM forecast
  ORDER BY recommended_restock_units DESC;
$$);

-- Save the analysis template ID returned by the procedure.
SET analysis_template_id = '<analysis_template_id>';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_TEMPLATES();


--------------------------------------------------------------
-- Register the activation template.
--------------------------------------------------------------

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
$$
api_version: 2.0.0
spec_type: template
name: activation_inventory_forecast
version: V0
type: sql_activation
description: >
  Activation template for inventory forecasting. Saves forecast results
  to the publisher's account, including recommended restock quantities
  by product and store.
methodology: >
  Runs the same forecast calculation as the analysis template and writes
  the results to an activation table for the designated collaborator.

parameters:
  - name: forecast_days
    description: Number of days to forecast (e.g., 30).
    type: integer
    default: 30
    required: true
  - name: promo_duration_days
    description: How many days the upcoming promotion will run.
    type: integer
    default: 7
    required: true
  - name: publisher_join_key_product
    description: Publisher column name for product ID (join-policy column).
    type: string
    required: true
  - name: publisher_join_key_store
    description: Publisher column name for store ID (join-policy column).
    type: string
    required: true
  - name: publisher_units_sold_col
    description: Publisher column for daily units sold.
    type: string
    required: true
  - name: publisher_promo_flag_col
    description: Publisher Boolean column indicating whether a day was a promotion day.
    type: string
    required: true
  - name: advertiser_join_key_product
    description: Advertiser column name for product ID (join-policy column).
    type: string
    required: true
  - name: advertiser_join_key_store
    description: Advertiser column name for store ID (join-policy column).
    type: string
    required: true
  - name: advertiser_inventory_col
    description: Advertiser column for current inventory level.
    type: string
    required: true
  - name: advertiser_promo_date_col
    description: Advertiser column for the upcoming promotion date.
    type: string
    required: true

template: |
  BEGIN
      CREATE OR REPLACE TABLE cleanroom.activation_data_inventory_forecast_results AS
      WITH publisher_sales_agg AS (
          SELECT
              {{ publisher_join_key_product | sqlsafe }} AS PRODUCT_ID,
              {{ publisher_join_key_store | sqlsafe }} AS STORE_ID,
              AVG(
                  CASE
                      WHEN {{ publisher_promo_flag_col | sqlsafe }} = FALSE
                      THEN {{ publisher_units_sold_col | sqlsafe }}
                      ELSE NULL
                  END
              ) AS avg_daily_sales_baseline,
              AVG(
                  CASE
                      WHEN {{ publisher_promo_flag_col | sqlsafe }} = TRUE
                      THEN {{ publisher_units_sold_col | sqlsafe }}
                      ELSE NULL
                  END
              ) AS avg_daily_sales_promo
          FROM IDENTIFIER({{ source_table[0] }})
          GROUP BY 1, 2
      ),
      advertiser_promo_plan AS (
          SELECT
              {{ advertiser_join_key_product | sqlsafe }} AS PRODUCT_ID,
              {{ advertiser_join_key_store | sqlsafe }} AS STORE_ID,
              {{ advertiser_inventory_col | sqlsafe }} AS current_inventory,
              {{ advertiser_promo_date_col | sqlsafe }} AS upcoming_promo_date
          FROM IDENTIFIER({{ source_table[1] }})
      ),
      forecast AS (
          SELECT
              c.PRODUCT_ID,
              c.STORE_ID,
              c.current_inventory,
              c.upcoming_promo_date,
              COALESCE(p.avg_daily_sales_baseline, 0) AS historical_baseline_sales,
              COALESCE(p.avg_daily_sales_promo, p.avg_daily_sales_baseline, 0) AS historical_promo_sales,
              (
                  (historical_baseline_sales * ({{ forecast_days | sqlsafe }} - {{ promo_duration_days | sqlsafe }})) +
                  (historical_promo_sales * {{ promo_duration_days | sqlsafe }})
              ) AS forecasted_demand_units
          FROM advertiser_promo_plan c
          LEFT JOIN publisher_sales_agg p
              ON c.PRODUCT_ID = p.PRODUCT_ID
              AND c.STORE_ID = p.STORE_ID
      )
      SELECT
          PRODUCT_ID,
          STORE_ID,
          current_inventory,
          upcoming_promo_date,
          historical_baseline_sales,
          historical_promo_sales,
          CAST(forecasted_demand_units AS INT) AS forecasted_demand,
          GREATEST(0, CAST(forecasted_demand - current_inventory AS INT)) AS recommended_restock_units
      FROM forecast
      ORDER BY recommended_restock_units DESC;

      RETURN 'inventory_forecast_results';
  END;
$$);

-- Save the activation template ID returned by the procedure.
SET activation_template_id = '<activation_template_id>';


--------------------------------------------------------------
-- Create the collaboration.
--------------------------------------------------------------

-- The publisher is the owner and a data provider. The advertiser is the
-- analysis runner with access to the publisher's data, both templates,
-- and can activate results to the publisher.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.INITIALIZE(
$$
api_version: 2.0.0
spec_type: collaboration
name: inventory_forecasting_collab
owner: publisher
collaborator_identifier_aliases:
  publisher: <publisher_data_sharing_id>
  advertiser: <advertiser_data_sharing_id>
analysis_runners:
  advertiser:
    data_providers:
      publisher:
        data_offerings:
          - id: <publisher_data_offering_id>
      advertiser:
        data_offerings: []
    templates:
      - id: <analysis_template_id>
      - id: <activation_template_id>
    activation_destinations:
      snowflake_collaborators:
        - publisher
$$,
'APP_WH'
);

SET collaboration_name = 'inventory_forecasting_collab';

-- INITIALIZE automatically joins the owner. Wait until status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_COLLABORATIONS();


--------------------------------------------------------------
-- SWITCH TO ADVERTISER to join, link data, run analysis, and activate results.
--------------------------------------------------------------

-- After the advertiser runs the activation template, the publisher can
-- view and process the activation results.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_ACTIVATIONS($collaboration_name);

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

-- Read the activation results. The table name is returned by PROCESS_ACTIVATION.
-- Filter by the segment name specified by the advertiser.
SELECT * FROM <activation_results_table>
  WHERE segment = 'inventory_forecast_activation_1';


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

-- Optionally delete the collaboration to clean up all resources.
-- This is a multi-step process; call GET_STATUS to check for 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);
-- CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
