-- This workbook demonstrates the use of an inventory forecasting template. This workbook 
-- should be run from the consumer's account.
-- This template helps publishers and advertisers forecast ad inventory availability within a secure data clean room.
-- Results are activated to the provider.

-- As coded here, the provider and consumer must be in the same organization and same cloud 
-- hosting region. To publish a clean room to use outside your organization, publish an 
-- EXTERNAL clean room.

-- This code provides a user form so that it can be run by a consumer using either the
-- clean rooms API or the UI.

-- For details about the features and use cases of this template, see the [Snowflake documentation
-- topic for this template](https://docs.snowflake.com/user-guide/cleanrooms/inventory-forecasting-template).

-- Sample provider and consumer data can be generated by running the sample data generator python script
-- linked from the documentation.

-- You can install and run the template either by using the API, as shown here, or by installing
-- and running it using the clean rooms UI.

-- Add values for placeholders in the code where indicated.

USE ROLE samooha_app_role;
USE WAREHOUSE app_wh;
SET cleanroom_name = 'E2E_INVENTORY_FORECASTING'; 

SET consumer_table = '<fully_qualified_consumer_table>';
SET provider_table = '<fully_qualified_provider_table>';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom(
    $cleanroom_name, 
    '<provider_account_locator>' 
);

CALL samooha_by_snowflake_local_db.consumer.link_datasets(
    $cleanroom_name, 
    [$consumer_table]
);

CALL samooha_by_snowflake_local_db.consumer.approve_provider_activation_consent($cleanroom_name,'activation_inventory_forecast');

-- Run the analysis.
CALL samooha_by_snowflake_local_db.consumer.run_analysis(
    $cleanroom_name,         
    'inventory_forecast_analysis',   
    [$consumer_table], 
    [$provider_table], 
    object_construct(
        'forecast_days', 30,
        'promo_duration_days', 7,
        'consumer_join_key_product', 'PRODUCT_ID',
        'consumer_join_key_store', 'STORE_ID',
        'consumer_inventory_col', 'CURRENT_INVENTORY',
        'consumer_promo_date_col', 'UPCOMING_PROMOTION_DATE',
        'provider_join_key_product', 'PRODUCT_ID',
        'provider_join_key_store', 'STORE_ID',
        'provider_sales_date_col', 'SALES_DATE',
        'provider_units_sold_col', 'UNITS_SOLD',
        'provider_promo_flag_col', 'WAS_ON_PROMOTION'
    )
);

-- Activate data to the provider. Assign this segment label, which helps the provider filter results to this query.
SET segment_name = 'inventory_forecast_activation_1';
CALL samooha_by_snowflake_local_db.consumer.run_activation(
    $cleanroom_name,
    $segment_name,
    'activation_inventory_forecast', 
    [$consumer_table],
    [$provider_table],
    object_construct(
        'forecast_days', 30,
        'promo_duration_days', 7,
        'consumer_join_key_product', 'PRODUCT_ID',
        'consumer_join_key_store', 'STORE_ID',
        'consumer_inventory_col', 'CURRENT_INVENTORY',
        'consumer_promo_date_col', 'UPCOMING_PROMOTION_DATE',
        'provider_join_key_product', 'PRODUCT_ID',
        'provider_join_key_store', 'STORE_ID',
        'provider_sales_date_col', 'SALES_DATE',
        'provider_units_sold_col', 'UNITS_SOLD',
        'provider_promo_flag_col', 'WAS_ON_PROMOTION'
    ),
    FALSE 
);
