VWO<>Snowflake

-- Set variables (must be uppercase)
SET VWO_ROLE = 'VWO_ROLE';
SET VWO_USERNAME = 'VWO_USER';
SET VWO_WAREHOUSE = 'VWO_WAREHOUSE';
SET VWO_DATABASE = 'VWO_DATABASE';
SET VWO_SCHEMA = 'VWO_SCHEMA';

-- Set user password
SET VWO_PASSWORD = 'secure_password';

BEGIN;

-- Create VWO role
USE ROLE securityadmin;
CREATE ROLE IF NOT EXISTS IDENTIFIER($VWO_ROLE);
GRANT ROLE IDENTIFIER($VWO_ROLE) TO ROLE SYSADMIN;

-- Create VWO user
CREATE USER IF NOT EXISTS IDENTIFIER($VWO_USERNAME)
PASSWORD = $VWO_PASSWORD
DEFAULT_ROLE = $VWO_ROLE
DEFAULT_WAREHOUSE = $VWO_WAREHOUSE;

GRANT ROLE IDENTIFIER($VWO_ROLE) TO USER IDENTIFIER($VWO_USERNAME);

-- Switch role to sysadmin for warehouse and database setup
USE ROLE sysadmin;

-- Create VWO warehouse
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($VWO_WAREHOUSE)
WAREHOUSE_SIZE = 'XSMALL'
WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;

-- Create VWO database
CREATE DATABASE IF NOT EXISTS IDENTIFIER($VWO_DATABASE);

-- Grant VWO warehouse access
GRANT USAGE
ON WAREHOUSE IDENTIFIER($VWO_WAREHOUSE)
TO ROLE IDENTIFIER($VWO_ROLE);

-- Grant VWO database access
GRANT OWNERSHIP
ON DATABASE IDENTIFIER($VWO_DATABASE)
TO ROLE IDENTIFIER($VWO_ROLE);

COMMIT;

BEGIN;

USE DATABASE IDENTIFIER($VWO_DATABASE);

-- Create schema for VWO data
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($VWO_SCHEMA);

COMMIT;

BEGIN;

-- Grant VWO schema access
GRANT OWNERSHIP
ON SCHEMA IDENTIFIER($VWO_SCHEMA)
TO ROLE IDENTIFIER($VWO_ROLE);

COMMIT;