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

SET exposures_row_count = 1000;
SET purchases_row_count = 1000;
SET customer_spine_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.EXPOSURES (
    row_num INT,
    hashed_email VARCHAR,
    ip_address VARCHAR,
    impression_date DATE,
    ad_group INT,
    campaign_id INT
);

INSERT INTO SNOWFLAKE_DCR_DEMO_DB.DEMO.EXPOSURES
SELECT
    SEQ4() AS row_num,
    SHA2(CONCAT('member_', SEQ4()::VARCHAR, '_dcr@snowflake.com')) AS hashed_email,
    CONCAT(
        UNIFORM(1, 255, RANDOM())::VARCHAR, '.',
        UNIFORM(1, 255, RANDOM())::VARCHAR, '.',
        UNIFORM(1, 255, RANDOM())::VARCHAR, '.',
        UNIFORM(1, 255, RANDOM())::VARCHAR
    ) AS ip_address,
    DATEADD(DAY, UNIFORM(-3, 3, RANDOM()), CURRENT_DATE()) AS impression_date,
    UNIFORM(0, 1, RANDOM()) AS ad_group,
    CASE WHEN ad_group = 0 THEN 100 ELSE 101 END AS campaign_id
FROM TABLE(GENERATOR(ROWCOUNT => $exposures_row_count));

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

CREATE OR REPLACE TABLE SNOWFLAKE_DCR_DEMO_DB.DEMO.CUSTOMER_SPINE (
    row_num INT,
    hashed_email VARCHAR,
    state VARCHAR,
    filing_type VARCHAR
);

INSERT INTO SNOWFLAKE_DCR_DEMO_DB.DEMO.CUSTOMER_SPINE
SELECT
    SEQ4() AS row_num,
    SHA2(CONCAT('member_', SEQ4()::VARCHAR, '_dcr@snowflake.com')) AS hashed_email,
    CASE UNIFORM(0, 1, RANDOM())
        WHEN 0 THEN 'NY'
        ELSE 'NJ'
    END AS state,
    CASE UNIFORM(0, 1, RANDOM())
        WHEN 0 THEN 'HEAD_OF_HOUSEHOLD'
        ELSE 'OTHER'
    END AS filing_type
FROM TABLE(GENERATOR(ROWCOUNT => $customer_spine_row_count));


SELECT *
FROM SNOWFLAKE_DCR_DEMO_DB.DEMO.EXPOSURES
LIMIT 10;

SELECT *
FROM SNOWFLAKE_DCR_DEMO_DB.DEMO.PURCHASES
LIMIT 10;

SELECT *
FROM SNOWFLAKE_DCR_DEMO_DB.DEMO.CUSTOMER_SPINE
LIMIT 10;

-- // ----------------------------------------------------------------------

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

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v0
    name: SF_DEMO_EXPOSURES
    datasets:
      - alias: exposures
        data_object_fqn: SNOWFLAKE_DCR_DEMO_DB.DEMO.EXPOSURES
        allowed_analyses: template_only
        schema_and_template_policies:
          hashed_email:
            category: join_standard
            column_type: hashed_email_sha256
          ip_address:
            category: join_standard
            column_type: ip_address
          impression_date:
            category: passthrough
          ad_group:
            category: passthrough
          campaign_id:
            category: passthrough
    $$
);

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.REGISTER_DATA_OFFERING(
    $$
    api_version: 2.0.0
    spec_type: data_offering
    version: v0
    name: SF_DEMO_CUSTOMER_SPINE
    datasets:
      - alias: customer_spine
        data_object_fqn: SNOWFLAKE_DCR_DEMO_DB.DEMO.CUSTOMER_SPINE
        allowed_analyses: template_only
        schema_and_template_policies:
          hashed_email:
            category: join_standard
            column_type: hashed_email_sha256
          state:
            category: passthrough
          filing_type:
            category: passthrough
    $$
);

call samooha_by_snowflake_local_db.registry.view_registered_data_offerings();

-- // ----------------------------------------------------------------------

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

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
    $$
    api_version: 2.0.0
    spec_type: template
    name: SF_DEMO_AUDIENCE_OVERLAP
    version: v0
    type: sql_analysis
    description: Audience overlap template that finds matching hashed emails between two datasets.
    methodology: Performs an inner join between two source tables on hashed_email_sha256 and returns distinct matches.

    template: |
        SELECT DISTINCT
            conv.hashed_email_sha256
        FROM IDENTIFIER({{ source_table[0] }}) conv
        INNER JOIN IDENTIFIER({{ source_table[1] }}) imps
            ON conv.hashed_email_sha256 = imps.hashed_email_sha256
    $$
);

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
    $$
    api_version: 2.0.0
    spec_type: template
    name: SF_DEMO_STATE_SPEND_ANALYSIS
    version: v0
    type: sql_analysis
    description: Analyzes total purchases and average spend amount per state and ad group by joining conversions, impressions, and customer spine.
    methodology: Performs a three-way inner join between purchases, exposures, and customer spine on hashed_email_sha256, then aggregates by state and ad_group.

    template: |
        SELECT
            spine.state,
            imps.ad_group,
            COUNT(*) AS total_purchases,
            AVG(conv.purchase_amount) AS avg_spend_amount
        FROM IDENTIFIER({{ source_table[0] }}) conv
        INNER JOIN IDENTIFIER({{ source_table[1] }}) imps
            ON conv.hashed_email_sha256 = imps.hashed_email_sha256
        INNER JOIN IDENTIFIER({{ source_table[2] }}) spine
            ON conv.hashed_email_sha256 = spine.hashed_email_sha256
        GROUP BY spine.state, imps.ad_group
    $$
);

call samooha_by_snowflake_local_db.registry.view_registered_templates();

-- // ----------------------------------------------------------------------
