/*
==============================================================================
Snowflake Cortex AISQL Developer Guide -- Full Runnable Script
7Rivers | Beyond the Dashboard, Part Two
==============================================================================

This script contains every SQL example from the blog post, in order:
environment setup, then one section per AISQL function, each with the
same worked examples described in the post.

BEFORE RUNNING:
  1. Upload the attached sample files to your Snowflake stage (created below):
       - support_tickets.csv
       - product_reviews.csv
       - sales_call_notes.csv
       - vendor_supply_agreement.pdf
     Upload via Snowsight's stage browser, or PUT them with SnowSQL.
  2. Your role needs SNOWFLAKE.CORTEX_USER (or AI_FUNCTIONS_USER) and the
     account-level USE AI FUNCTIONS privilege. See the grant statement below.

Run top to bottom the first time; after that, feel free to jump around,
each section only depends on the environment setup above it.

Note: this version omits AI_TRANSCRIBE. That function needs an audio/video
file in the stage, and no sample recording is bundled with this package.
See the AI_TRANSCRIBE reference if you want to add it with your own file:
  https://docs.snowflake.com/en/sql-reference/functions/ai_transcribe
==============================================================================
*/


-- ===========================================================================
-- PREREQUISITES
-- ===========================================================================

-- ------------------------------------------------------------
-- Before you start: privileges
-- ------------------------------------------------------------
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE DEVELOPER_ROLE;


-- ===========================================================================
-- ENVIRONMENT SETUP
-- ===========================================================================

-- ------------------------------------------------------------
-- Database, schema, and warehouse
-- ------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS SEVENRIVERS_DEMOS;
CREATE SCHEMA IF NOT EXISTS SEVENRIVERS_DEMOS.AI_SQL_LAB;
USE SCHEMA SEVENRIVERS_DEMOS.AI_SQL_LAB;

CREATE WAREHOUSE IF NOT EXISTS AISQL_WH
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

USE WAREHOUSE AISQL_WH;


-- ------------------------------------------------------------
-- An internal stage for files
-- ------------------------------------------------------------
CREATE STAGE IF NOT EXISTS FILE_STAGE
  DIRECTORY = (ENABLE = TRUE)
  COMMENT = 'Landing zone for CSVs and contract PDFs';


-- ------------------------------------------------------------
-- File format and structured tables
-- ------------------------------------------------------------
CREATE FILE FORMAT IF NOT EXISTS CSV_STANDARD
  TYPE = 'CSV'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  SKIP_HEADER = 1
  PARSE_HEADER = FALSE;

CREATE OR REPLACE TABLE SUPPORT_TICKETS (
    ticket_id       INT,
    customer_name   STRING,
    channel         STRING,
    product_name    STRING,
    language        STRING,
    created_at      TIMESTAMP_NTZ,
    body_text       STRING
);

CREATE OR REPLACE TABLE PRODUCT_REVIEWS (
    review_id       INT,
    product_name    STRING,
    rating          INT,
    language        STRING,
    review_date     DATE,
    review_text     STRING
);

CREATE OR REPLACE TABLE SALES_CALL_NOTES (
    call_id         INT,
    rep_name        STRING,
    account_name    STRING,
    call_date       DATE,
    notes_text      STRING
);


-- ------------------------------------------------------------
-- Loading the sample data
-- ------------------------------------------------------------
COPY INTO SUPPORT_TICKETS
  FROM @FILE_STAGE/support_tickets.csv
  FILE_FORMAT = (FORMAT_NAME = CSV_STANDARD);

COPY INTO PRODUCT_REVIEWS
  FROM @FILE_STAGE/product_reviews.csv
  FILE_FORMAT = (FORMAT_NAME = CSV_STANDARD);

COPY INTO SALES_CALL_NOTES
  FROM @FILE_STAGE/sales_call_notes.csv
  FILE_FORMAT = (FORMAT_NAME = CSV_STANDARD);


-- ===========================================================================
-- THE FUNCTIONS, ONE BY ONE
-- ===========================================================================

-- ------------------------------------------------------------
-- AI_CLASSIFY: sorting text, images, or documents into your categories
-- ------------------------------------------------------------

-- Example 1: route a support ticket to the right queue
SELECT
    ticket_id,
    product_name,
    body_text,
    AI_CLASSIFY(
        body_text,
        [
            {'label': 'billing', 'description': 'duplicate charges, refunds, or pricing disputes'},
            {'label': 'shipping', 'description': 'delivery delays or shipping cost issues'},
            {'label': 'product_defect', 'description': 'broken, defective, or malfunctioning items'},
            {'label': 'sizing_fit', 'description': 'items running large or small'},
            {'label': 'praise', 'description': 'positive feedback with no issue to resolve'}
        ],
        {'task_description': 'Classify the primary reason a customer contacted support'}
    ):labels[0]::STRING AS issue_type
FROM SUPPORT_TICKETS;


-- Example 2: tag reviews with every theme that applies
SELECT
    review_id,
    product_name,
    rating,
    AI_CLASSIFY(
        review_text,
        ['pricing', 'product_quality', 'shipping', 'sizing_fit', 'customer_support'],
        {'output_mode': 'multi'}
    ):labels AS review_topics
FROM PRODUCT_REVIEWS
WHERE rating <= 3;


-- ------------------------------------------------------------
-- AI_SENTIMENT: overall and aspect-level sentiment
-- ------------------------------------------------------------

-- Example 1: overall sentiment against star rating
SELECT
    review_id,
    product_name,
    rating,
    AI_SENTIMENT(review_text):categories[0]:sentiment::STRING AS text_sentiment
FROM PRODUCT_REVIEWS;


-- Example 2: aspect sentiment on sales call notes
SELECT
    account_name,
    call_date,
    AI_SENTIMENT(
        notes_text,
        ['pricing', 'competitor_mentions', 'product_feedback', 'relationship_health']
    ) AS call_sentiment
FROM SALES_CALL_NOTES
ORDER BY account_name, call_date;


-- ------------------------------------------------------------
-- AI_FILTER: a semantic WHERE clause
-- ------------------------------------------------------------

-- Example 1: find every ticket expressing billing frustration, however it's phrased
SELECT ticket_id, customer_name, body_text
FROM SUPPORT_TICKETS
WHERE AI_FILTER(
    PROMPT('In the following support ticket, is the customer frustrated about being billed incorrectly or charged twice? {0}', body_text)
);


-- Example 2: semantic JOIN against a competitor watchlist
WITH competitors AS (
    SELECT 'RivalGear' AS competitor_name
    UNION ALL SELECT 'TrekPeak'
    UNION ALL SELECT 'SummitPro'
)
SELECT r.review_id, r.review_text, c.competitor_name
FROM PRODUCT_REVIEWS r
JOIN competitors c
    ON AI_FILTER(PROMPT('Does this review compare the product to {1}? Review: {0}', r.review_text, c.competitor_name));


-- ------------------------------------------------------------
-- AI_AGG: synthesize a column with your own instruction
-- ------------------------------------------------------------

-- Example 1: root-cause synthesis across low-rated reviews
SELECT
    product_name,
    AI_AGG(
        review_text,
        'Identify the two or three most common product frictions mentioned across these reviews and suggest a likely root cause for each.'
    ) AS root_cause_summary
FROM PRODUCT_REVIEWS
WHERE rating <= 3
GROUP BY product_name;


-- Example 2: surface competitive pressure per account
SELECT
    account_name,
    AI_AGG(
        notes_text,
        'These are chronological sales call notes for one retail account. Identify any emerging competitive threats or pricing pressure mentioned, and note whether the risk seems to be increasing or decreasing over time.'
    ) AS account_risk_summary
FROM SALES_CALL_NOTES
GROUP BY account_name;


-- ------------------------------------------------------------
-- AI_SUMMARIZE_AGG: a general-purpose summary across rows
-- ------------------------------------------------------------

-- Example 1: a weekly executive summary of support volume
SELECT
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS ticket_count,
    AI_SUMMARIZE_AGG(body_text) AS weekly_ticket_summary
FROM SUPPORT_TICKETS
GROUP BY 1
ORDER BY 1;


-- Example 2: a per-account recap for QBR prep
SELECT
    account_name,
    COUNT(*) AS call_count,
    AI_SUMMARIZE_AGG(notes_text) AS account_recap
FROM SALES_CALL_NOTES
GROUP BY account_name;


-- ------------------------------------------------------------
-- AI_TRANSLATE: normalize language without leaving SQL
-- ------------------------------------------------------------

-- Example 1: normalize every review to English for unified reporting
SELECT
    review_id,
    language AS source_language,
    review_text AS original_text,
    AI_TRANSLATE(review_text, '', 'en') AS review_text_en
FROM PRODUCT_REVIEWS
WHERE language <> 'en';


-- Example 2: localize an outbound reply before it's sent
SELECT
    t.ticket_id,
    t.language,
    AI_TRANSLATE(
        'Thank you for reaching out. We are processing a replacement for your order and you will receive tracking within two business days.',
        'en',
        t.language
    ) AS localized_reply
FROM SUPPORT_TICKETS t
WHERE t.language <> 'en';


-- ------------------------------------------------------------
-- AI_REDACT: strip PII before it lands anywhere sensitive
-- ------------------------------------------------------------

-- Example 1: redact before writing to an analytics-facing table
CREATE OR REPLACE TABLE SUPPORT_TICKETS_REDACTED AS
SELECT
    ticket_id,
    channel,
    product_name,
    created_at,
    AI_REDACT(body_text) AS body_text_redacted
FROM SUPPORT_TICKETS;


-- Example 2: detect mode with a selective allowlist
SELECT
    ticket_id,
    body_text,
    AI_REDACT(
        body_text,
        ['NAME', 'EMAIL', 'PHONE_NUMBER'],
        mode => 'detect'
    ) AS pii_spans
FROM SUPPORT_TICKETS
WHERE ticket_id IN (1003, 1007);


-- ------------------------------------------------------------
-- AI_EXTRACT: pull structured fields out of text or files
-- ------------------------------------------------------------

-- Example 1: pull key terms out of the vendor contract
SELECT AI_EXTRACT(
    file => TO_FILE('@FILE_STAGE', 'vendor_supply_agreement.pdf'),
    responseFormat => {
        'supplier_name': 'What is the name of the supplier in this agreement?',
        'effective_date': 'What is the effective date of the agreement?',
        'term_length': 'What is the length of the contract term?',
        'annual_contract_value': 'What is the estimated total annual contract value?'
    },
    scores => TRUE
);


-- Example 2: extract the pricing table as structured rows
SELECT AI_EXTRACT(
    file => TO_FILE('@FILE_STAGE', 'vendor_supply_agreement.pdf'),
    responseFormat => {
        'schema': {
            'type': 'object',
            'properties': {
                'pricing_table': {
                    'description': 'Materials pricing table',
                    'type': 'object',
                    'column_ordering': ['material', 'unit_price', 'annual_minimum'],
                    'properties': {
                        'material': {'description': 'Material', 'type': 'array'},
                        'unit_price': {'description': 'Unit Price (per yard)', 'type': 'array'},
                        'annual_minimum': {'description': 'Annual Minimum (yards)', 'type': 'array'}
                    }
                }
            }
        }
    }
);


-- Turning that into an actual result set
WITH extracted AS (
    SELECT AI_EXTRACT(
        file => TO_FILE('@FILE_STAGE', 'vendor_supply_agreement.pdf'),
        responseFormat => {
            'schema': {
                'type': 'object',
                'properties': {
                    'pricing_table': {
                        'description': 'Materials pricing table',
                        'type': 'object',
                        'column_ordering': ['material', 'unit_price', 'annual_minimum'],
                        'properties': {
                            'material': {'description': 'Material', 'type': 'array'},
                            'unit_price': {'description': 'Unit Price (per yard)', 'type': 'array'},
                            'annual_minimum': {'description': 'Annual Minimum (yards)', 'type': 'array'}
                        }
                    }
                }
            }
        }
    ):response:pricing_table AS pricing_table
)
SELECT
    idx.index AS row_num,
    pricing_table:material[idx.index]::STRING AS material,
    pricing_table:unit_price[idx.index]::STRING AS unit_price,
    pricing_table:annual_minimum[idx.index]::STRING AS annual_minimum
FROM extracted,
LATERAL FLATTEN(input => pricing_table:material) idx
ORDER BY row_num;


-- ------------------------------------------------------------
-- AI_PARSE_DOCUMENT: turn a staged file into clean text
-- ------------------------------------------------------------

-- Example 1: parse the contract in LAYOUT mode and feed it back into AI_EXTRACT
WITH parsed AS (
    SELECT AI_PARSE_DOCUMENT(
        TO_FILE('@FILE_STAGE', 'vendor_supply_agreement.pdf'),
        {'mode': 'LAYOUT'}
    ):content::STRING AS contract_markdown
)
SELECT
    contract_markdown,
    AI_EXTRACT(
        text => contract_markdown,
        responseFormat => {'termination_notice': 'How many days notice is required to terminate for convenience?'}
    ) AS termination_terms
FROM parsed;


-- Example 2: batch-parse every PDF in the stage, split by page
SELECT
    relative_path,
    AI_PARSE_DOCUMENT(
        TO_FILE('@FILE_STAGE', relative_path),
        {'mode': 'OCR', 'page_split': TRUE}
    ):pages AS pages
FROM DIRECTORY(@FILE_STAGE)
WHERE relative_path ILIKE '%.pdf';


-- ------------------------------------------------------------
-- AI_COMPLETE: general-purpose generation, with structured output when you need it
-- ------------------------------------------------------------

-- Example 1: draft a first-pass reply to a ticket
SELECT
    ticket_id,
    AI_COMPLETE(
        'claude-sonnet-5',
        CONCAT(
            'Write a short, empathetic first-response reply, under 80 words, to this support ticket. ',
            'Do not promise a specific refund amount. Ticket: ', body_text
        )
    ) AS suggested_reply
FROM SUPPORT_TICKETS
WHERE ticket_id = 1008;


-- Example 2: structured triage in a single call
SELECT
    ticket_id,
    AI_COMPLETE(
        model => 'claude-sonnet-5',
        prompt => CONCAT('Triage this support ticket: ', body_text),
        response_format => TYPE OBJECT(
            severity STRING,
            recommended_action STRING,
            requires_refund BOOLEAN
        )
    ) AS triage
FROM SUPPORT_TICKETS;


-- ------------------------------------------------------------
-- AI_EMBED and AI_SIMILARITY: semantic search and near-duplicate detection
-- ------------------------------------------------------------

-- Example 1: find past calls most similar to a new situation
SELECT
    call_id,
    account_name,
    notes_text,
    AI_SIMILARITY(
        notes_text,
        'Buyer is comparing our pricing to a competitor and considering shifting order volume.'
    ) AS similarity_to_new_call
FROM SALES_CALL_NOTES
ORDER BY similarity_to_new_call DESC
LIMIT 5;


-- Example 2: precompute embeddings once, then reuse them for near-duplicate detection
ALTER TABLE PRODUCT_REVIEWS ADD COLUMN IF NOT EXISTS review_embedding VECTOR(FLOAT, 1024);

UPDATE PRODUCT_REVIEWS
SET review_embedding = AI_EMBED('snowflake-arctic-embed-l-v2.0', review_text)
WHERE review_embedding IS NULL;

SELECT
    a.review_id AS review_a,
    b.review_id AS review_b,
    VECTOR_COSINE_SIMILARITY(a.review_embedding, b.review_embedding) AS similarity
FROM PRODUCT_REVIEWS a
JOIN PRODUCT_REVIEWS b
    ON a.review_id < b.review_id
   AND a.product_name = b.product_name
WHERE VECTOR_COSINE_SIMILARITY(a.review_embedding, b.review_embedding) > 0.80
ORDER BY similarity DESC;