USE ROLE SAMOOHA_APP_ROLE;
USE SECONDARY ROLES NONE;
USE WAREHOUSE wh_xs;

SET purchases_row_count = 1000;

CREATE OR REPLACE DATABASE SNOWFLAKE_DCR_DEMO_DB;
CREATE OR REPLACE SCHEMA SNOWFLAKE_DCR_DEMO_DB.DEMO;

CREATE OR REPLACE TABLE SNOWFLAKE_DCR_DEMO_DB.DEMO.PURCHASES (
    row_num INT,
    hashed_email VARCHAR,
    purchase_date DATE,
    platform_location VARCHAR,
    purchase_amount DECIMAL(10, 2)
);

INSERT INTO SNOWFLAKE_DCR_DEMO_DB.DEMO.PURCHASES
SELECT
    SEQ4() AS row_num,
    SHA2(CONCAT('member_', SEQ4()::VARCHAR, '_dcr@snowflake.com')) AS hashed_email,
    DATEADD(DAY, UNIFORM(0, 6, RANDOM()), CURRENT_DATE()) AS purchase_date,
    CASE UNIFORM(0, 2, RANDOM())
        WHEN 0 THEN 'mobile'
        WHEN 1 THEN 'desktop'
        ELSE 'ctv'
    END AS platform_location,
    ROUND(UNIFORM(1, 100, RANDOM()) + UNIFORM(0, 99, RANDOM()) / 100.0, 2) AS purchase_amount
FROM TABLE(GENERATOR(ROWCOUNT => $purchases_row_count));

SELECT * FROM SNOWFLAKE_DCR_DEMO_DB.DEMO.PURCHASES;

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v0
    name: SF_DEMO_PURCHASES
    datasets:
      - alias: purchases
        data_object_fqn: SNOWFLAKE_DCR_DEMO_DB.DEMO.PURCHASES
        allowed_analyses: template_only
        schema_and_template_policies:
          hashed_email:
            category: join_standard
            column_type: hashed_email_sha256
          purchase_date:
            category: passthrough
          platform_location:
            category: passthrough
          purchase_amount:
            category: passthrough
    $$
);

call samooha_by_snowflake_local_db.registry.view_registered_data_offerings();



SET collaboration_name = 'SF_DEMO_TRIPLE_ACCOUNT_OVERLAP';
SET owner_identifier = 'ORGNAME.ACCOUNT_NAME_1';

call samooha_by_snowflake_local_db.collaboration.review($collaboration_name, $owner_identifier);
call samooha_by_snowflake_local_db.collaboration.get_status($collaboration_name);
call samooha_by_snowflake_local_db.collaboration.join($collaboration_name);
call samooha_by_snowflake_local_db.collaboration.get_status($collaboration_name);

-- // VIEW_TEMPLATES shows what templates each collaborator can execute
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);
-- // VIEW_DATA_OFFERINGS shows what datasets each collaborator can access
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);

-- // This will error (expected - ADVERTISER does not have access)
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    template: SF_DEMO_AUDIENCE_OVERLAP_v0
    template_configuration:
      view_mappings:
        source_tables:
          - ADVERTISER.SF_DEMO_PURCHASES_v0.purchases
          - PUBLISHER.SF_DEMO_EXPOSURES_v0.exposures
    $$
);

-- // This will succeed 
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
    $collaboration_name,
    $$
    api_version: 2.0.0
    spec_type: analysis
    template: SF_DEMO_STATE_SPEND_ANALYSIS_v0
    template_configuration:
      view_mappings:
        source_tables:
          - ADVERTISER.SF_DEMO_PURCHASES_v0.purchases
          - PUBLISHER.SF_DEMO_EXPOSURES_v0.exposures
          - SPINE.SF_DEMO_CUSTOMER_SPINE_v0.customer_spine
    $$
);
