-- Script used to create a stored procedure that the MC Application uses to execute queries.
-- The stored procedure runs as the owner, which is the `MONTE_CARLO_APP_ROLE` role, configured
-- to have access to the databases that can be monitored.
-- The application is then granted access to the stored procedure and the database where it is defined.

-- Configuration
-- Role name to use to execute queries, can be changed to any other name
set mcd_agent_role_name='MCD_AGENT_ROLE';
-- Database name used to create the stored procedure, can be changed to any other name
set mcd_helper_db_name='MCD_AGENT_HELPER';

-- Set role for grants
USE ROLE ACCOUNTADMIN;

-- Create the role Monte Carlo will use to execute queries
CREATE ROLE IF NOT EXISTS identifier($mcd_agent_role_name);

-- Grant the new role to ACCOUNTADMIN
GRANT ROLE identifier($mcd_agent_role_name) TO ROLE ACCOUNTADMIN;

-- Grant privileges to allow access to query history
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE identifier($mcd_agent_role_name);

-- Create a database to define the helper stored procedure
CREATE DATABASE IF NOT EXISTS identifier($mcd_helper_db_name);
GRANT OWNERSHIP ON DATABASE identifier($mcd_helper_db_name) TO ROLE identifier($mcd_agent_role_name);

USE ROLE identifier($mcd_agent_role_name);
USE DATABASE identifier($mcd_helper_db_name);
CREATE SCHEMA IF NOT EXISTS MCD_AGENT;
USE SCHEMA MCD_AGENT;

CREATE OR REPLACE PROCEDURE MCD_AGENT_EXECUTE_QUERY(query STRING)
    RETURNS TABLE()
    LANGUAGE SQL
    EXECUTE AS OWNER
AS
BEGIN
    LET rs RESULTSET := (EXECUTE IMMEDIATE :query);
    RETURN TABLE(rs);
END;