setup.sql

-- ============================================================================
-- DATA BIRDS — Snowflake Environment Setup
-- ============================================================================
-- Run this script ONCE as ACCOUNTADMIN before the event.
-- Creates: warehouse, database, schema, tables, stage, role, user.
-- ============================================================================

USE ROLE ACCOUNTADMIN;

-- ────────────────────────────────────────────────────────────
-- 1. WAREHOUSE
-- ────────────────────────────────────────────────────────────
CREATE WAREHOUSE IF NOT EXISTS DATA_BIRDS_WH
    WAREHOUSE_SIZE    = 'SMALL'
    AUTO_SUSPEND      = 120
    AUTO_RESUME       = TRUE
    INITIALLY_SUSPENDED = FALSE
    COMMENT = 'Snowflake Summit — Data Birds Booth Activity';

-- ────────────────────────────────────────────────────────────
-- 2. DATABASE & SCHEMA
-- ────────────────────────────────────────────────────────────
CREATE DATABASE IF NOT EXISTS DATA_BIRDS_DB
    COMMENT = 'Data Birds: Snowflake-powered interactive flock';

USE DATABASE DATA_BIRDS_DB;

CREATE SCHEMA IF NOT EXISTS AVIARY
    COMMENT = 'All Data Birds objects live here';

USE SCHEMA AVIARY;

-- ────────────────────────────────────────────────────────────
-- 3. FLOCK TABLE — Every submitted bird becomes a row.
-- ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS FLOCK (
    BIRD_ID         VARCHAR(64)   NOT NULL,
    ARTIST_NAME     VARCHAR(256)  NOT NULL,
    ORIGIN_CITY     VARCHAR(256),
    LATITUDE        FLOAT,
    LONGITUDE       FLOAT,
    BIRD_DESCRIPTION VARCHAR(1024),
    -- Cortex-generated fields
    SPECIES         VARCHAR(256),
    FLIGHT_STYLE    VARCHAR(256),
    SPEED_KMH       FLOAT,
    ALTITUDE        VARCHAR(64),
    PERSONALITY     VARCHAR(512),
    FUN_FACT        VARCHAR(1024),
    -- Image reference (local filename)
    IMAGE_FILENAME  VARCHAR(512),
    -- Image bytes as a browser-ready data URL so a master display can show
    -- birds submitted from any booth laptop.
    IMAGE_DATA_URL  VARCHAR,
    -- CSS animation data (generated by Cortex)
    ANIMATION_JSON  VARIANT,
    -- Timestamps
    CREATED_AT      TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
COMMENT = 'Every bird in the flock — one row per attendee submission';

-- Existing event databases may have been created before IMAGE_DATA_URL existed.
ALTER TABLE FLOCK ADD COLUMN IF NOT EXISTS IMAGE_DATA_URL VARCHAR;

-- ────────────────────────────────────────────────────────────
-- 4. FLOCK STATS — Dynamic Table for real-time aggregation
-- ────────────────────────────────────────────────────────────
CREATE OR REPLACE DYNAMIC TABLE FLOCK_STATS
    TARGET_LAG = '1 minute'
    WAREHOUSE  = DATA_BIRDS_WH
AS
SELECT
    COUNT(*)                                    AS TOTAL_BIRDS,
    COUNT(DISTINCT ORIGIN_CITY)                 AS UNIQUE_CITIES,
    MODE(SPECIES)                               AS MOST_COMMON_SPECIES,
    MODE(FLIGHT_STYLE)                          AS MOST_COMMON_FLIGHT_STYLE,
    ROUND(AVG(SPEED_KMH), 1)                   AS AVG_FLOCK_SPEED,
    MAX(CREATED_AT)                             AS LATEST_SUBMISSION
FROM FLOCK;

-- ────────────────────────────────────────────────────────────
-- 5. HOURLY SUBMISSIONS — For forecasting & anomaly detection
-- ────────────────────────────────────────────────────────────
CREATE OR REPLACE DYNAMIC TABLE HOURLY_SUBMISSIONS
    TARGET_LAG = '1 minute'
    WAREHOUSE  = DATA_BIRDS_WH
AS
SELECT
    DATE_TRUNC('HOUR', CREATED_AT)  AS HOUR,
    COUNT(*)                        AS SUBMISSION_COUNT,
    ROUND(AVG(SPEED_KMH), 1)       AS AVG_SPEED
FROM FLOCK
GROUP BY 1;

-- ────────────────────────────────────────────────────────────
-- 6. ROLE & PRIVILEGES
-- ────────────────────────────────────────────────────────────
CREATE ROLE IF NOT EXISTS DATA_BIRDS_ROLE
    COMMENT = 'Role for Data Birds booth app';

GRANT USAGE ON WAREHOUSE DATA_BIRDS_WH TO ROLE DATA_BIRDS_ROLE;
GRANT USAGE ON DATABASE DATA_BIRDS_DB TO ROLE DATA_BIRDS_ROLE;
GRANT USAGE ON SCHEMA DATA_BIRDS_DB.AVIARY TO ROLE DATA_BIRDS_ROLE;
GRANT ALL PRIVILEGES ON SCHEMA DATA_BIRDS_DB.AVIARY TO ROLE DATA_BIRDS_ROLE;

-- Table access
GRANT ALL ON TABLE DATA_BIRDS_DB.AVIARY.FLOCK TO ROLE DATA_BIRDS_ROLE;
GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA DATA_BIRDS_DB.AVIARY TO ROLE DATA_BIRDS_ROLE;
GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA DATA_BIRDS_DB.AVIARY TO ROLE DATA_BIRDS_ROLE;

-- Cortex AI access
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE DATA_BIRDS_ROLE;

-- ML Functions (for forecasting/anomaly detection on the dashboard)
GRANT CREATE SNOWFLAKE.ML.FORECAST ON SCHEMA DATA_BIRDS_DB.AVIARY TO ROLE DATA_BIRDS_ROLE;
GRANT CREATE SNOWFLAKE.ML.ANOMALY_DETECTION ON SCHEMA DATA_BIRDS_DB.AVIARY TO ROLE DATA_BIRDS_ROLE;

GRANT ROLE DATA_BIRDS_ROLE TO ROLE SYSADMIN;

-- ────────────────────────────────────────────────────────────
-- 7. BOOTH USER — keypair auth, no password
-- ────────────────────────────────────────────────────────────
-- The booth shares ONE Snowflake user across every laptop, authenticated
-- with a shared RSA private key. The current public key is hard-coded below;
-- the matching rsa_key.p8 is distributed to each booth laptop out-of-band
-- and is git-ignored. Never commit rsa_key.p8 or rsa_key.pub.
CREATE USER IF NOT EXISTS DATA_BIRDS_USER
    DEFAULT_ROLE        = DATA_BIRDS_ROLE
    DEFAULT_WAREHOUSE   = DATA_BIRDS_WH
    DEFAULT_NAMESPACE   = DATA_BIRDS_DB.AVIARY
    COMMENT = 'Booth user for Data Birds app — keypair auth only';

-- Rotate this public key by re-running with a freshly-generated rsa_key.pub
-- after the event. Snowflake supports two active public keys per user
-- (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) for zero-downtime rotation.
ALTER USER DATA_BIRDS_USER SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArLzjEjn6iHv56knPnj4wgvBzkLLxSIEd7+mkSCyIz8h4eOl1Hw/QOZFKvnNmD7ILYbhEbPqyPTbfZWoWWsSH3e1lXHzvyL6epT9R/LlgIphb3DX3Znv0vr8h0dGo4KXOnKmMFuhToBIvycMFag63oB2B5OnRzZ55HCHVY9wo1nBv5alQDhPYe+myIpuL8KZ3YkIGz2SGDJB/qMYdkpD5djMtqDmgXxyYFiLrIk3P+uFMYhjts5Pn4oiYDH2oNVDRSQIYSBj4qZqj3Rr3bqd3TtZBXc9jOz/zNTKP6qwzKySr+odPjeHHDtkJ+RsFJFZW/FnrOapi2lz0yl41MxkaqwIDAQAB';

GRANT ROLE DATA_BIRDS_ROLE TO USER DATA_BIRDS_USER;

-- ============================================================================
-- ✅ SETUP COMPLETE
-- ============================================================================