-- Lets see these functions in action and see what result they bring.
show functions like 'CURRENT%';

-- since they are functions like sum() or avg(), they have to be used within select statement
select  
    current_region(),  
    current_version(), 
    current_user(),    
    current_role(),    
    current_database(), 
    current_schema(),
    current_schemas(),
    current_warehouse();
    
 -- Now set them and see how to change it via SQL without using the UI features
    use role sysadmin; 
    use database my_db;
    use schema my_schema;
    use warehouse sap_load_wh;
    

-- they can also be used to set a variable value
-- you don't need to have select keyword in this case.
    
    set my_wh = current_warehouse();

-- Now you can print them using variable name like this
    select $my_wh;

-- if you have to set them for multiple variables together, you can also follow this
    set (my_db, my_schema, my_wh) = (current_database(), current_schema(), current_warehouse());
    
    select $my_db, $my_schema, $my_wh;
    
-- what about current_schemas and how to slipt it
    set my_search_path = current_schemas();
    
    select $my_search_path;
    select * from table(strtok_split_to_table($my_search_path,',')) f;
    -- we have a better way to do it

USE ROLE securityadmin;

CREATE ROLE admin01; -- who can create all objects
CREATE ROLE analyst01; -- analyst who can query objects
CREATE ROLE developer01; -- developer role

-- make sure these roles are accessible upward
GRANT ROLE developer01 TO ROLE admin01;
GRANT ROLE analyst01 TO ROLE admin01;
GRANT ROLE admin01 TO ROLE sysadmin;


--make sure role is mapped to user
GRANT ROLE developer01 TO USER ADMIN; 
GRANT ROLE analyst01 TO USER ADMIN; 
GRANT ROLE admin01 TO USER ADMIN; 

--give grants
USE ROLE SYSADMIN;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE admin01;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE admin01;
-- GRANT CREATE DATABASE ON ACCOUNT TO ROLE dev_db_admin WITH GRANT OPTION;

-- now use new role
USE ROLE admin01;

-- create a db and schema
CREATE database db01;
CREATE SCHEMA db01.schema01;
CREATE WAREHOUSE wh01 
  WITH WAREHOUSE_SIZE = 'XLARGE' 
  WAREHOUSE_TYPE = 'STANDARD' 
  AUTO_SUSPEND = 300 
  AUTO_RESUME = TRUE 
  MIN_CLUSTER_COUNT = 1 
  MAX_CLUSTER_COUNT = 1 
  SCALING_POLICY = 'STANDARD' 
  COMMENT = 'this is load wh';

-- grant roles to other roles.
GRANT USAGE ON WAREHOUSE wh01 TO ROLE analyst01;
GRANT USAGE ON WAREHOUSE wh01 TO ROLE developer01;
GRANT USAGE ON database db01 TO ROLE analyst01;
GRANT USAGE ON database db01 TO ROLE developer01;
GRANT USAGE ON SCHEMA db01.schema01 TO ROLE analyst01;
GRANT USAGE ON SCHEMA db01.schema01 TO ROLE developer01;

GRANT create table ON SCHEMA db01.schema01 TO ROLE developer01;

GRANT SELECT ON ALL TABLES IN SCHEMA db01.schema01 TO ROLE analyst01;
GRANT SELECT ON FUTURE TABLES IN SCHEMA db01.schema01 TO ROLE analyst01;


use role developer01;
use schema db01.schema01
create OR replace table t01 (c1 string);
insert into t01 (c1) values ('1');

use role analyst01;
use schema db01.schema01
select * from t01;

use role sysadmin;
use schema my_db.my_schema;
select current_schema(), current_database(), current_role();


create or replace table my_logging_01(
  log_id integer AUTOINCREMENT not null,
  user_name string,
  role_name string,
  db_name string,
  schema_name string,
  wh_name string
);

-- lets check what happens when we insert
insert into my_logging_01 (user_name,role_name,db_name,schema_name,wh_name) 
    values (
            current_user(),
            current_role(),
            current_database(),
            current_schema(),
            current_warehouse());
            
select * from my_logging_01;

-- create a task
create or replace task my_log_task_001
  warehouse = 'compute_wh'
  schedule  = '1 minute'
as
   insert into my_logging_01(user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());
   

-- start the task
alter task my_log_task_001 resume;

-- check the information schema for 
select *  from table(information_schema.task_history()) where name ='MY_LOG_TASK_001' order by scheduled_time;
select * from my_logging_01;

-- check your context
use role sysadmin;
use schema my_db.my_schema;
select current_schema(), current_database(), current_role();

-- lets create a table 
create or replace table logging_sp(
  log_id integer AUTOINCREMENT not null,
  user_name string,
  role_name string,
  db_name string,
  schema_name string,
  wh_name string
);

-- lets check what happens when we insert
insert into logging_sp (user_name,role_name,db_name,schema_name,wh_name) 
    values (
            current_user(),
            current_role(),
            current_database(),
            current_schema(),
            current_warehouse());
            
select * from logging_sp;

-- define a stored procedure with caller's role
create or replace procedure load_sp_caller()
  returns boolean 
  language javascript
  execute as caller
  as
  $$
  sql_insert_command = `insert into logging_sp_01 (user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());` 
  result_insert = snowflake.execute(
        { 
        sqlText: sql_insert_command
        
        }); 
    return result_insert;
  $$
;

-- define a stored procedure with owner's role
create or replace procedure load_sp_owner()
  returns boolean 
  language javascript
  execute as owner
  as
  $$
  sql_insert_command = `insert into logging_sp_01 (user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());` 
  result_insert = snowflake.execute(
        { 
        sqlText: sql_insert_command
        
        }); 
    return result_insert;
  $$
;

-- create a task and call the stored procedure
create or replace task my_task_with_sp_owner
  warehouse = 'compute_wh'
  schedule  = '1 minute'
as
   call load_sp_owner();
   
-- resume the task
alter task my_task_with_sp_owner resume;

-- create a task with caller stored procedure
create or replace task my_task_with_sp_caller
  warehouse = 'compute_wh'
  schedule  = '1 minute'
as
   call load_sp_caller();

--resume the task
alter task my_task_with_sp_caller resume;

select *  from table(information_schema.task_history()) 
  where name in ('MY_TASK_WITH_SP_OWNER','MY_TASK_WITH_SP_CALLER')
  order by scheduled_time;