#!/bin/bash

set +x

cat << EOF
**************************************************************************
This script creates a Snowflake connectivity with the following steps

1. Installs Perl and system ODBC drivers
2. Installs SF ODBC driver
3. Configure system-wide SF datasource

 
EOF

read -p "Would you like to continue? [ Y, N ] " -n 1 -r 
echo
echo "**************************************************************************"

if [[ ! $REPLY =~ ^[Yy]$ ]]
then
    echo "Exiting....."
    exit 1
fi

if [[ $UID != 0 ]]
then
    echo "!!! Script must be run as root. Exiting..."
    echo
    exit 1
fi


EPEL_REPO_PATH="/etc/yum.repos.d/epel.repo"
SF_REPO_PATH="/etc/yum.repos.d/snowflake-odbc.repo"
SF_VER=2.25.8
ODBC_PKGS="perl-DBD-ODBC.x86_64 snowflake-odbc.x86_64"
ODBC_CONFIG_PATH="/etc/odbc.ini"

# Step 1. Configuring yum repos
#
echo -n "Checking if [ epel ] repo configured ... "
if [[ ! -f $EPEL_REPO_PATH ]];
then
    cat << EOF
!!! NOT configured
!!! To configure EPEL either run [ amazon-linux-extras ] command or configure repo manually.
!!! Exiting.
EOF
    exit 1;
fi;
echo "[ YES ]"

echo -n "Checking if [ snowflake-odbc ] repo configured ... "
if [ ! -f $SF_REPO_PATH ];
then
    echo "[ NO ]"
    echo -n "Configuring [ snowflake-odbc] repo ... "
    cat << EOF > $SF_REPO_PATH
[snowflake-odbc]
name=snowflake-odbc
baseurl=https://sfc-repo.snowflakecomputing.com/odbc/linux/${SF_VER}/
gpgcheck=0
EOF
    echo "[ DONE ]"
fi

# Step 2. Installing drivers
#
echo "Installing ODBC drivers ... "
yum -y install $ODBC_PKGS

if [ $? -ne 0 ]
then
    read -p "Packet manager returned an error. Would you like to continue? [ Y, N ] " -n 1 -r 
    echo

    if [[ ! $REPLY =~ ^[Yy]$ ]]
    then
        echo "Exiting....."
        exit 1
    fi
fi
# Step 3. Configuring SF
#
if [[ -e $ODBC_CONFIG_PATH ]]
then
   cp $ODBC_CONFIG_PATH ${ODBC_CONFIG_PATH}.bak
fi

echo -n "Configuring SF datastore ... "
cat << EOF > $ODBC_CONFIG_PATH
[snowflake]
Description=SnowflakeDB
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=orchard.snowflakecomputing.com
PORT=443
SSL=on
ACCOUNT=orchard
EOF

echo "[ DONE ]";

# Step 4. Checking and creating a test script
#

echo "Checking Perl's DBI versions ..."
/usr/bin/env perl -MDBI -e 'DBI-> installed_versions;'

cat << 'EOF' > test_sf_connection.pl
#!/usr/bin/env perl
use DBI;
use MIME::Base64;

$user = '';
$pass = '';
$key_path = "/root/snowflake/key.private";
$key_auth_options = "AUTHENTICATOR=SNOWFLAKE_JWT;PRIV_KEY_FILE=$key_path";

$sf_warehouse = "DEV_OWS_WAREHOUSE";
$sf_role = "ROYALTYSHARE_DB_QA_SCHEMA_READWRITE";
$sf_schema = "DEV";

$dbh = DBI->connect("dbi:ODBC:DSN=snowflake;UID=$user;PWD=$pass;$key_auth_options") or die "Error: $DBI::errstr\n\n";
#print "*** Successfully connected ***";

$dbh->do("USE WAREHOUSE $sf_warehouse") or die "Error: $DBI::errstr\n\n";
$dbh->do("USE ROLE $sf_role") or die "Error: $DBI::errstr\n\n";
$dbh->do("USE ROYALTYSHARE") or die "Error: $DBI::errstr\n\n";
$dbh->do("USE SCHEMA $sf_schema") or die "Error: $DBI::errstr\n\n";

$sth = $dbh->prepare("SELECT value FROM rs_test WHERE name LIKE '%SF%'");
$sth->execute() or die "SQL Error: $DBI::errstr\n";
while ($row = $sth->fetchrow_hashref()) { print "*"x20 . " RESULT " . "*"x20 . "\n$row->{VALUE}\n" . "*"x48 ."\n"};

# DONE
EOF

echo "********************** FINISHED **********************";
cat << EOF
All set. Next steps:
- update test_sf_connection.pl with user credentials
- test SF connection
- debug with isql -v / snowsql
============================== DONE =============================
EOF