#!/bin/bash

# CREATE OR REPLACE VDS "$scratch"."pds_list" AS
# SELECT TABLE_SCHEMA || '.' || TABLE_NAME from INFORMATION_SCHEMA."TABLES" where TABLE_TYPE ='TABLE' 

USERNAME=dremio
PASSWORD=dremio123
DREMIO_BASE_PATH=http://localhost:9047

SPACE="space1"
SQL1="CREATE OR REPLACE VDS $SPACE."pds_list" AS SELECT TABLE_SCHEMA || '.' || TABLE_NAME from INFORMATION_SCHEMA.\\\"TABLES\\\" where TABLE_TYPE ='TABLE'"
SQL2="select * from $SPACE.pds_list"
VIEWS_FILE=".views_info"
DS_PATH_FILE=".dspath_info"
REFRESH_FILE="refresh_command.sql"
DELAY=5

read -p "Username [$USERNAME]? " name
USERNAME=${name:-$USERNAME}

read -sp "Password or Personal Access Token (hit return to use the default)? "  password
PASSWORD=${password:-$PASSWORD}
echo

read -p "Dremio base path [$DREMIO_BASE_PATH]? " base_path
DREMIO_BASE_PATH=${base_path:-$DREMIO_BASE_PATH}

read -p "Please enter a space in which this script will create the VDS 'pds_list' [$SPACE]? " space
SPACE=${space:-$SPACE}

DREMIO_AUTH_TOKEN=_dremio$(curl $DREMIO_BASE_PATH/apiv2/login -k -H 'Content-Type: application/json' -d"{\"userName\":\"$USERNAME\",\"password\":\"$PASSWORD\"}" -s | jq -r ".token")
echo
echo Token: $DREMIO_AUTH_TOKEN

#
# main functions / code
#

SQL_REQ=$(curl -s -H 'Content-Type: application/json' -H "Authorization: $DREMIO_AUTH_TOKEN" -X POST "$DREMIO_BASE_PATH/api/v3/sql" -d '{"sql": "'"$SQL1"'"}')

# Runs an SQL query to find all the views. Run the query, get the job ID and then parse the results out to json
# 1 Run SQL job
SQL_REQ=$(curl -s -H 'Content-Type: application/json' -H "Authorization: $DREMIO_AUTH_TOKEN" -X POST "$DREMIO_BASE_PATH/api/v3/sql" -d '{"sql": "'"$SQL2"'"}')

# 2 Find SQL job ID
SQL_ID=$(echo $SQL_REQ | jq -r '.id')

# 3 Delay to allow SQL to run (its not instant!)
echo Waiting $DELAY seconds for query to complete
sleep $DELAY

# 4 Use id to find job results (pulls the paths from the VDS we setup)
curl -s -H 'Content-Type: application/json' -H "Authorization: $DREMIO_AUTH_TOKEN" -X GET "$DREMIO_BASE_PATH/api/v3/job/$SQL_ID/results" | jq . > $VIEWS_FILE

# 5 Populate the paths into a file
jq '.rows[]."EXPR$0"' $VIEWS_FILE > $DS_PATH_FILE

# generate refresh sql commands 
cat /dev/null > $REFRESH_FILE
while read DS_PATH
do
  DS_PATH_ENC=$( echo $DS_PATH | sed -E "s/\ /%20/g" )
  echo -e "ALTER PDS $DS_PATH_ENC FORGET METADATA;" | sed -E "s/\./\"\.\"/g" >> $REFRESH_FILE
  echo -e "ALTER PDS $DS_PATH_ENC REFRESH METADATA;" | sed -E "s/\./\"\.\"/g" >> $REFRESH_FILE
done < $DS_PATH_FILE

echo "Output in $REFRESH_FILE"