/*
   If you want to use a network policy or modify an exsiting one, uncomment the one (create or alter) that aligns with your needs.
   Our IP addresses are 52.2.68.68/32, 52.54.227.22/32
   */

   --create network policy openbridge_ip_whitelist ALLOWED_IP_LIST = ('52.2.68.68/32', '52.54.227.22/32');
   --alter network policy <your_current_network_policy_name> SET {[ALLOWED_IP_LIST] = ('52.2.68.68/32', '52.54.227.22/32')]};

-- Use the ACCOUNTADMIN role to ensure sufficient privileges
USE ROLE ACCOUNTADMIN;
create network policy openbridge_ip_whitelist ALLOWED_IP_LIST = ('52.2.68.68/32', '52.54.227.22/32');

CREATE OR REPLACE NETWORK RULE openbridge_ip_rule 
  TYPE = IPV4 
  MODE = INGRESS 
  VALUE_LIST = (
    '52.2.68.68/32', 
    '52.54.227.22/32'
  );

CREATE OR REPLACE NETWORK POLICY openbridge_access_policy
  ALLOWED_NETWORK_RULE_LIST = ('openbridge_ip_rule');

-- Check allowed IPs
DESC NETWORK POLICY openbridge_access_policy;

-- Verify account-level enforcement
SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

-- Set variables (Replace placeholders with your actual values)
SET openbridge_role = 'YOUR_OPENBRIDGE_ROLE';          -- e.g., 'BUXZZ_ROLE'
SET openbridge_username = 'YOUR_OPENBRIDGE_USER';      -- e.g., 'BUXZZ_USER'
SET openbridge_password = 'YOUR_PASSWORD';             -- e.g., '12BUXZZ!@'
SET openbridge_warehouse = 'YOUR_OPENBRIDGE_WAREHOUSE';-- e.g., 'BUXZZ_WAREHOUSE'
SET openbridge_database = 'YOUR_OPENBRIDGE_DATABASE';  -- e.g., 'BUXZZ_DATABASE'
SET openbridge_schema = 'YOUR_OPENBRIDGE_SCHEMA';      -- e.g., 'BUXZZ_SCHEMA'
SET openbridge_namespace = $openbridge_database || '.' || $openbridge_schema;

-- Use the ACCOUNTADMIN role to ensure sufficient privileges
USE ROLE ACCOUNTADMIN;

-- Step 1: Setup
-- Create the Openbridge role if it doesn't exist
CREATE ROLE IF NOT EXISTS IDENTIFIER($openbridge_role);

-- Create the Openbridge user if it doesn't exist
CREATE USER IF NOT EXISTS IDENTIFIER($openbridge_username)
  PASSWORD = $openbridge_password
  DEFAULT_ROLE = $openbridge_role
  DEFAULT_WAREHOUSE = $openbridge_warehouse
  DEFAULT_NAMESPACE = $openbridge_namespace; -- Use pre-built namespace

-- Grant the role to the user
GRANT ROLE IDENTIFIER($openbridge_role) TO USER IDENTIFIER($openbridge_username);

-- Assign the current user's name to a variable
SET current_username = CURRENT_USER();

-- Grant the role to your current user (for testing purposes)
GRANT ROLE IDENTIFIER($openbridge_role) TO USER IDENTIFIER($current_username);

-- Create the warehouse if it doesn't exist
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($openbridge_warehouse)
  WAREHOUSE_SIZE = 'XSMALL'
  WAREHOUSE_TYPE = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Grant usage on the warehouse to the role
GRANT USAGE ON WAREHOUSE IDENTIFIER($openbridge_warehouse) TO ROLE IDENTIFIER($openbridge_role);

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS IDENTIFIER($openbridge_database);

-- Grant usage on the database to the role (without CREATE SCHEMA privilege)
GRANT USAGE ON DATABASE IDENTIFIER($openbridge_database) TO ROLE IDENTIFIER($openbridge_role);

-- Use the new database
USE DATABASE IDENTIFIER($openbridge_database);

-- Schema Creation Options: 
-- Option 1: Standard Schema (Use when CDP/Data History is needed) 
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($openbridge_schema); 
-- Option 2: TRANSIENT Schema (Use when CDP/Data History is NOT needed - reduces storage costs) 
-- CREATE TRANSIENT SCHEMA IF NOT EXISTS IDENTIFIER($openbridge_schema);


-- Grant privileges on the schema to the role
GRANT USAGE ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);
GRANT CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE FILE FORMAT ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);

-- Step 2: Switch to the Openbridge role for testing
USE ROLE IDENTIFIER($openbridge_role);

-- Set the context to use the Openbridge warehouse, database, and schema
USE WAREHOUSE IDENTIFIER($openbridge_warehouse);
USE DATABASE IDENTIFIER($openbridge_database);
USE SCHEMA IDENTIFIER($openbridge_schema);

-- Step 3: Test Operations

-- Test 1: Create a table
CREATE OR REPLACE TABLE test_table (
  id INT AUTOINCREMENT PRIMARY KEY,
  name STRING,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Test 2: Insert data into the table
INSERT INTO test_table (name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

-- Test 3: Query data from the table
SELECT * FROM test_table;

-- Test 4: Update data in the table
UPDATE test_table
SET name = 'Alice Updated'
WHERE name = 'Alice';

-- Test 5: Delete data from the table
DELETE FROM test_table
WHERE name = 'Bob';

-- Test 6: Drop the table
DROP TABLE test_table;