Snowflake destination

-- =====
-- Configuration variables
-- =====

SET role_name       = 'RENTA_ROLE';
SET user_name       = 'RENTA_USER';
SET user_password   = 'TempP@ssw0rd123';    -- IMPORTANT: Change before execution
SET warehouse_name  = 'RENTA_WAREHOUSE';
SET database_name   = 'RENTA_DATABASE';
SET schema_name     = 'RENTA';

-- =====
-- Role and user creation
-- =====

USE ROLE securityadmin;

-- Create Renta ELT role
CREATE ROLE IF NOT EXISTS IDENTIFIER($role_name)
  COMMENT = 'Role for Renta ELT service operations';

-- Grant role to SYSADMIN for role hierarchy
GRANT ROLE IDENTIFIER($role_name) TO ROLE SYSADMIN;

-- Create Renta ELT service user
CREATE USER IF NOT EXISTS IDENTIFIER($user_name)
  PASSWORD = $user_password
  DEFAULT_ROLE = $role_name
  DEFAULT_WAREHOUSE = $warehouse_name
  MUST_CHANGE_PASSWORD = FALSE
  COMMENT = 'Service account for Renta ELT operations';

-- Assign role to user
GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);

-- =====
-- Virtual warehouse creation
-- =====

USE ROLE sysadmin;

CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($warehouse_name)
  WITH 
    WAREHOUSE_SIZE = 'XSMALL'           -- Adjust size based on workload requirements
    AUTO_SUSPEND = 300                  -- Auto-suspend after 5 minutes of inactivity
    AUTO_RESUME = TRUE                  -- Auto-resume on query submission
    INITIALLY_SUSPENDED = TRUE          -- Start in suspended state
    COMMENT = 'Dedicated warehouse for Renta ELT operations';

-- Grant warehouse privileges to Renta role
GRANT USAGE ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE IDENTIFIER($role_name);
GRANT OPERATE ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE IDENTIFIER($role_name);

-- =====
-- Database and schema creation
-- =====

-- Switch to ACCOUNTADMIN for database creation
USE ROLE accountadmin;

-- Create Renta database
CREATE DATABASE IF NOT EXISTS IDENTIFIER($database_name)
  COMMENT = 'Database for Renta ELT data and transformations';

-- Transfer ownership to SYSADMIN for operational management
GRANT OWNERSHIP ON DATABASE IDENTIFIER($database_name) TO ROLE sysadmin COPY CURRENT GRANTS;

-- Switch back to SYSADMIN
USE ROLE sysadmin;

-- Set database context
SET use_db_stmt = 'USE DATABASE ' || $database_name;
EXECUTE IMMEDIATE $use_db_stmt;

-- Create main schema
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($schema_name)
  COMMENT = 'Main schema for Renta ELT tables and views';

-- =====
-- Permission grants
-- =====

-- Database-level permissions
GRANT USAGE ON DATABASE IDENTIFIER($database_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE SCHEMA ON DATABASE IDENTIFIER($database_name) TO ROLE IDENTIFIER($role_name);

-- Schema-level permissions for object creation
GRANT USAGE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE TABLE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE VIEW ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE STAGE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE FILE FORMAT ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE SEQUENCE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE FUNCTION ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE PROCEDURE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE PIPE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE STREAM ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE TASK ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);

-- Permissions on all existing tables in schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- Permissions on all existing views in schema
GRANT SELECT ON ALL VIEWS IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- Permissions on future tables (automatically apply to new tables)
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- Permissions on future views (automatically apply to new views)
GRANT SELECT ON FUTURE VIEWS IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- =====
-- Verification
-- =====

-- Display created objects
SHOW ROLES LIKE 'RENTA_ROLE';
SHOW USERS LIKE 'RENTA_USER';
SHOW WAREHOUSES LIKE 'RENTA_WAREHOUSE';
SHOW DATABASES LIKE 'RENTA_DATABASE';

-- Display granted permissions
SHOW GRANTS TO ROLE IDENTIFIER($role_name);

-- Verify schema exists
SHOW SCHEMAS IN DATABASE IDENTIFIER($database_name);