init_db.sql

-- =============================================================================
--  Snowflake Summit 2026 — Postcard Activation
--  Database Initialization Script
--
--  Run this ONCE as ACCOUNTADMIN (creates user + role + grants).
--  It is idempotent (IF NOT EXISTS everywhere).
-- =============================================================================

USE ROLE ACCOUNTADMIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. Database & Schema
-- ─────────────────────────────────────────────────────────────────────────────
CREATE DATABASE IF NOT EXISTS SUMMIT_APP;

CREATE SCHEMA IF NOT EXISTS SUMMIT_APP.POSTCARDS;

USE SCHEMA SUMMIT_APP.POSTCARDS;

-- ─────────────────────────────────────────────────────────────────────────────
-- 2. Warehouse (XS, auto-suspend in 60 s so it doesn't idle)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE WAREHOUSE IF NOT EXISTS POSTCARD_WH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND   = 60
  AUTO_RESUME    = TRUE
  COMMENT        = 'Summit postcard activation warehouse';

-- ─────────────────────────────────────────────────────────────────────────────
-- 3. Target Table — postcard_entries
--
--  Concurrency note: Snowflake uses MVCC (multi-version concurrency control)
--  and does NOT row-lock on INSERT, so 6 simultaneous writers are safe.
--
--  GEOGRAPHY type stores the GeoJSON LineString for the flight path arc.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS SUMMIT_APP.POSTCARDS.postcard_entries (
    entry_id        NUMBER AUTOINCREMENT PRIMARY KEY,   -- surrogate key
    created_at      TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    dest_zip        VARCHAR(10)   NOT NULL,
    dest_city       VARCHAR(100),
    dest_state      VARCHAR(50),
    dest_lat        FLOAT,
    dest_lon        FLOAT,
    distance_miles  FLOAT,
    -- GeoJSON LineString: ORIGIN → DESTINATION (great-circle path for the arc)
    flight_path     GEOGRAPHY
);

-- ─────────────────────────────────────────────────────────────────────────────
-- 4. Quick sanity-check query  (run manually to verify the marketplace table)
-- ─────────────────────────────────────────────────────────────────────────────
-- SELECT ZIP, LAT, LON, NAME, STATE
-- FROM ZIP_CODES_DB.POSTALADMIN."zcr_usa_zip_centroids"
-- WHERE ZIP = '94103'   -- Moscone Center / SF
-- LIMIT 5;

-- ─────────────────────────────────────────────────────────────────────────────
-- 5. Helpful view — aggregated stats used by the Cortex AI context prompt
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW SUMMIT_APP.POSTCARDS.postcard_stats AS
SELECT
    COUNT(*)                                              AS total_postcards,
    ROUND(SUM(distance_miles), 0)                         AS total_miles,
    COUNT(DISTINCT dest_state)                            AS unique_states,
    COUNT(DISTINCT dest_zip)                              AS unique_zips,
    MAX(distance_miles)                                   AS max_distance_miles,
    -- Top destination state
    MODE(dest_state)                                      AS top_state,
    -- Top destination city
    MODE(dest_city || ', ' || dest_state)                 AS top_city
FROM SUMMIT_APP.POSTCARDS.postcard_entries
WHERE CAST(created_at AS DATE) = CURRENT_DATE();

-- ─────────────────────────────────────────────────────────────────────────────
-- 6. State leaderboard view (used by Cortex context)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW SUMMIT_APP.POSTCARDS.state_leaderboard AS
SELECT
    dest_state,
    COUNT(*)                   AS postcard_count,
    ROUND(AVG(distance_miles)) AS avg_distance_miles
FROM SUMMIT_APP.POSTCARDS.postcard_entries
WHERE CAST(created_at AS DATE) = CURRENT_DATE()
GROUP BY dest_state
ORDER BY postcard_count DESC;

-- ─────────────────────────────────────────────────────────────────────────────
-- 7. BOOTH ROLE & USER — keypair auth, no password
--
--  The booth shares ONE Snowflake user across every laptop, authenticated
--  with a shared RSA private key. The public key below is the booth's
--  current key; the matching private key (postcard_rsa.p8) is distributed
--  to booth laptops out-of-band via 1Password — it is NOT committed to the
--  repo (.gitignore enforces this). No password, no browser flow, no MFA.
--
--  To rotate after an event: regenerate the keypair, replace the public key
--  body below, re-run this script, then update the .p8 stored in 1Password.
--  Snowflake supports two active public keys per user (RSA_PUBLIC_KEY +
--  RSA_PUBLIC_KEY_2) for zero-downtime cutover.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE ROLE IF NOT EXISTS POSTCARD_ROLE
    COMMENT = 'Role for the booth postcard activation app';

GRANT USAGE   ON WAREHOUSE POSTCARD_WH                       TO ROLE POSTCARD_ROLE;
GRANT USAGE   ON DATABASE  SUMMIT_APP                         TO ROLE POSTCARD_ROLE;
GRANT USAGE   ON SCHEMA    SUMMIT_APP.POSTCARDS               TO ROLE POSTCARD_ROLE;
GRANT SELECT, INSERT       ON TABLE SUMMIT_APP.POSTCARDS.POSTCARD_ENTRIES TO ROLE POSTCARD_ROLE;
GRANT SELECT               ON VIEW  SUMMIT_APP.POSTCARDS.POSTCARD_STATS    TO ROLE POSTCARD_ROLE;
GRANT SELECT               ON VIEW  SUMMIT_APP.POSTCARDS.STATE_LEADERBOARD TO ROLE POSTCARD_ROLE;

-- Marketplace dataset (must already be installed as FREE_ZIPCODES_DB; see README step 4)
GRANT IMPORTED PRIVILEGES ON DATABASE FREE_ZIPCODES_DB TO ROLE POSTCARD_ROLE;

-- Cortex AI access (for the Q&A step)
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE POSTCARD_ROLE;

GRANT ROLE POSTCARD_ROLE TO ROLE SYSADMIN;

CREATE USER IF NOT EXISTS POSTCARD_USER
    DEFAULT_ROLE       = POSTCARD_ROLE
    DEFAULT_WAREHOUSE  = POSTCARD_WH
    DEFAULT_NAMESPACE  = SUMMIT_APP.POSTCARDS
    COMMENT            = 'Booth user for postcard activation — keypair auth only';

-- Rotate this public key by re-running with a freshly-generated postcard_rsa.pub.
ALTER USER POSTCARD_USER SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEApR6rhOKX1OqKlrW8i8OQAditkjBVeKM7hIdqE+7bXIHTVyOG9oY3gwtcT6p2CmrTU394GlYAFPNZ6/QTTNqUdldR4+rBv5LHmWqH5Obkt7m0AFbYxggQG1fUqTxmLJ+dqQRqUMn1OSuisVQNKyQUgximyEHvhSMBLfJ52/LR/re57UOjwtEWVIijer5LKKVy2QtXcuEvCyzrS2onzSVLhSI4xeGkvrCIld/LO/LnPIV4ftOFw0jZN5bEyVN4AJauVxhK0hh/VDWIXvtfz6KP9nlEtE5g/zKbzjuKPiflkUrpJugGFBz/zckrW/HSb+MY3NVgvBl4/mM0tjlkYVoeJwIDAQAB';

GRANT ROLE POSTCARD_ROLE TO USER POSTCARD_USER;

-- Done!
SELECT 'Database initialization complete ✓' AS status;