-- This workbook demonstrates the use of an inventory forecasting template. This workbook 
-- should be run from the provider'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).

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

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

-- 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 provider_table_full = '<fully_qualified_provider_table_name>';
SET consumer_locator = '<consumer_locator>';
SET consumer_data_sharing_id = '<consumer_data_sharing_id>';

CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link provider data into the clean room.
-- You must link TEMP_PUBLIC_KEY for the results to be activated to the provider account.
CALL samooha_by_snowflake_local_db.provider.link_datasets(
    $cleanroom_name, 
    [
      $provider_table_full,
      'samooha_by_snowflake_local_db.LIBRARY.TEMP_PUBLIC_KEY'
    ]
);

CALL samooha_by_snowflake_local_db.provider.set_join_policy(
    $cleanroom_name, 
    [
        $provider_table_full || ':PRODUCT_ID',
        $provider_table_full || ':STORE_ID'
    ]
);

-- Analysis template.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'inventory_forecast_analysis',
    $$
    DECLARE
        res RESULTSET;
    BEGIN
        
        CREATE OR REPLACE TABLE cleanroom.activation_data_inventory_forecast_results AS
        WITH provider_sales_agg AS (
           
            SELECT
                {{ provider_join_key_product | sqlsafe }} AS PRODUCT_ID,
                {{ provider_join_key_store | sqlsafe }} AS STORE_ID,
                AVG(
                    CASE 
                        WHEN {{ provider_promo_flag_col | sqlsafe }} = FALSE 
                        THEN {{ provider_units_sold_col | sqlsafe }} 
                        ELSE NULL 
                    END
                ) AS avg_daily_sales_baseline,
                AVG(
                    CASE 
                        WHEN {{ provider_promo_flag_col | sqlsafe }} = TRUE 
                        THEN {{ provider_units_sold_col | sqlsafe }} 
                        ELSE NULL 
                    END
                ) AS avg_daily_sales_promo
            FROM identifier({{ source_table[0] }}) -- Provider's history table
            GROUP BY 1, 2
        ),
        
        consumer_promo_plan AS (
           
            SELECT
                {{ consumer_join_key_product | sqlsafe }} AS PRODUCT_ID,
                {{ consumer_join_key_store | sqlsafe }} AS STORE_ID,
                {{ consumer_inventory_col | sqlsafe }} AS current_inventory,
                {{ consumer_promo_date_col | sqlsafe }} AS upcoming_promo_date
            FROM identifier({{ my_table[0] }}) -- Consumer's stock table
        ),
        
        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 consumer_promo_plan c
            LEFT JOIN provider_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;
        
        res := (SELECT * FROM cleanroom.activation_data_inventory_forecast_results);
        RETURN TABLE(res);
    END;
    $$
);

-- Add column policy.
CALL samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name, [
   'inventory_forecast_analysis:' || $provider_table_full || ':SALES_DATE',
   'inventory_forecast_analysis:' || $provider_table_full || ':UNITS_SOLD',
   'inventory_forecast_analysis:' || $provider_table_full || ':WAS_ON_PROMOTION'
]);

-- Activation template.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'activation_inventory_forecast',
    $$
    BEGIN
        RETURN 'inventory_forecast_results';
    END;
    $$
);


-- Define a user form, so the analysis and activation can be run in the clean rooms UI.
CALL samooha_by_snowflake_local_db.provider.add_ui_form_customizations(
    $cleanroom_name,
    'inventory_forecast_analysis',
    { 
        'display_name': 'Inventory Demand Forecast (by Promotion)',
        'description': 'Forecasts product demand by combining consumer promo plans with historical sales data.',
        'methodology': 'Calculates historical baseline and promotional sales averages from the provider. Joins this with the consumer''s upcoming promotion schedule to forecast total demand for a given period and recommend restock quantities.',
        'warehouse_hints': { 'warehouse_size': 'medium' },
        'render_table_dropdowns': {
            'render_consumer_table_dropdown': true,
            'render_provider_table_dropdown': true
        },
        'activation_template_name': 'activation_inventory_forecast',
        'enabled_activations': ['provider','consumer']
    },
    { 
        'forecast_days': {
            'display_name': 'Forecast Period (Days)',
            'description': 'Number of days to forecast (e.g., 30).',
            'group': 'Forecast Parameters',
            'order': 1,
            'type': 'integer',
            'default': 30
        },
        'promo_duration_days': {
            'display_name': 'Promotion Duration (Days)',
            'description': 'How many days the upcoming promotion will run.',
            'group': 'Forecast Parameters',
            'order': 2,
            'type': 'integer',
            'default': 7
        },
        
        'consumer_join_key_product': {
            'display_name': 'Consumer Product Key',
            'group': 'Consumer Table Configuration',
            'order': 3,
            'type': 'dropdown',
            'references': ['CONSUMER_JOIN_POLICY'],
            'consumer_parent_table_field': 'my_table'
        },
        'consumer_join_key_store': {
            'display_name': 'Consumer Store Key',
            'group': 'Consumer Table Configuration',
            'order': 4,
            'type': 'dropdown',
            'references': ['CONSUMER_JOIN_POLICY'],
            'consumer_parent_table_field': 'my_table'
        },
        'consumer_inventory_col': {
            'display_name': 'Current Inventory Column',
            'group': 'Consumer Table Configuration',
            'order': 5,
            'type': 'dropdown',
            'references': ['CONSUMER_COLUMN_POLICY'],
            'consumer_parent_table_field': 'my_table'
        },
        'consumer_promo_date_col': {
            'display_name': 'Upcoming Promotion Date Column',
            'group': 'Consumer Table Configuration',
            'order': 6,
            'type': 'dropdown',
            'references': ['CONSUMER_COLUMN_POLICY'],
            'consumer_parent_table_field': 'my_table'
        },
        'provider_join_key_product': {
            'display_name': 'Provider Product Key',
            'group': 'Provider Table Configuration',
            'order': 7,
            'type': 'dropdown',
            'references': ['PROVIDER_JOIN_POLICY'],
            'provider_parent_table_field': 'source_table'
        },
        'provider_join_key_store': {
            'display_name': 'Provider Store Key',
            'group': 'Provider Table Configuration',
            'order': 8,
            'type': 'dropdown',
            'references': ['PROVIDER_JOIN_POLICY'],
            'provider_parent_table_field': 'source_table'
        },
        'provider_sales_date_col': {
            'display_name': 'Provider Sales Date Column',
            'group': 'Provider Table Configuration',
            'order': 9,
            'type': 'dropdown',
            'references': ['PROVIDER_COLUMN_POLICY'],
            'provider_parent_table_field': 'source_table'
        },
        'provider_units_sold_col': {
            'display_name': 'Provider Units Sold Column',
            'group': 'Provider Table Configuration',
            'order': 10,
            'type': 'dropdown',
            'references': ['PROVIDER_COLUMN_POLICY'],
            'provider_parent_table_field': 'source_table'
        },
        'provider_promo_flag_col': {
            'display_name': 'Provider "Was on Promotion" Column (Boolean)',
            'group': 'Provider Table Configuration',
            'order': 11,
            'type': 'dropdown',
            'references': ['PROVIDER_COLUMN_POLICY'],
            'provider_parent_table_field': 'source_table'
        }
    },
    {
        'default_output_type': 'TABLE',
        'measure_columns': ['PRODUCT_ID', 'STORE_ID', 'RECOMMENDED_RESTOCK_UNITS', 'FORECASTED_DEMAND', 'CURRENT_INVENTORY']
    }
);

-- Share and publish the clean room.
CALL samooha_by_snowflake_local_db.provider.add_consumers($cleanroom_name, $consumer_locator, $consumer_data_sharing_id);
CALL samooha_by_snowflake_local_db.provider.enable_consumer_run_analysis($cleanroom_name, [$consumer_locator]);
CALL samooha_by_snowflake_local_db.provider.request_provider_activation_consent($cleanroom_name, 'activation_inventory_forecast');
CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', '0');
CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing($cleanroom_name);

-- SWITCH TO CONSUMER to install the clean room, run the analysis, and activate results.

-- When the consumer has activated results to your account, you can read them by running
-- the following query. Note that it can take several minutes for data to propagate from
-- the consumer account to the provider account.
SELECT *
   FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.PROVIDER_ACTIVATION_SUMMARY
   WHERE segment = '<segment_name_specified_by_consumer>';

-- Optionally delete the clean room at the end, to clean up all your resources.
-- CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name) ;
