#!/usr/local/bin/bash

if [ -d /usr/local/Cellar/mysql* ] && [ -f `which mysql` ]; then
  echo "mysql installed"
else
  echo "mysql doesnt installed"
  brew install mysql
fi

if [ -d /usr/local/Cellar/sphinx* ]; then
  echo "sphinx installed"
else
  echo "sphinx doesnt installed"
  brew install sphinx --mysql
fi

echo "Enter root user MySQL password:"
read ROOT_PASSWORD
read -e -p "Enter the NAME of the new database [bada]:" DB_NAME
[ -z "${DB_NAME}" ] && DB_NAME="bada"
echo "DROP DB IF EXISTS"
mysql -u root -p ${ROOT_PASSWORD} -e "DROP DATABASE IF EXISTS ${DB_NAME};"
read -e -p "Enter the database CHARACTER SET [UTF8]" CHARSET
[ -z "${CHARSET}" ] && CHARSET="utf8"
echo "Creating new database ${DB_NAME} ..."
mysql -u root -p ${ROOT_PASSWORD} -e "CREATE DATABASE ${DB_NAME} /*\!40100 DEFAULT CHARACTER SET ${CHARSET} */;"
echo "Database ${DB_NAME} successfully created."
echo "Showing existing databases ..."
mysql -u root -p ${ROOT_PASSWORD} -e "show databases;"
echo
read -e -p "Enter the NAME of the new database user! [VASA]" USERNAME
[ -z "${USERNAME}" ] && USERNAME="VASA"
mysql -u root -p ${ROOT_PASSWORD} -e "DROP USER '${USERNAME}'@'localhost';"
read -e -p "Enter the PASSWORD for the new database user! " USERPASS
echo "Creating new user ${USERNAME} ..."
mysql -uroot -p ${ROOT_PASSWORD} -e "CREATE USER ${USERNAME}@localhost IDENTIFIED BY '${USERPASS}';"
echo "User ${USERNAME} successfully created!"
echo
echo "Granting ALL privileges on ${DB_NAME} to ${USERNAME}!"
mysql -uroot -p ${ROOT_PASSWORD} -e "GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${USERNAME}'@'localhost';"
mysql -uroot -p ${ROOT_PASSWORD} -e "FLUSH PRIVILEGES;"

PR_FILE=`pwd`/processed_file.txt
TABLE_NAME=boyare
LOAD_SQL_FILE=load_text_data.sql
CREATE_TABLE_SQL=bae.sql

echo "Process file -- ${PR_FILE}"
echo
echo "Table name -- ${TABLE_NAME}"


cat ${CREATE_TABLE_SQL} > bae.sql <<EOF
CREATE TABLE if not exists ${DB_NAME}.${TABLE_NAME}
(id bigint primary key not null auto_increment,
context text not null,
response text not null)
EOF

cat ${CREATE_TABLE_SQL} | mysql -u ${USERNAME} -D ${DB_NAME}
echo created
mysql -u root -e "use $DB_NAME; show tables;"

if [ ! -f ${RR_FILE} ]; then
    touch ${PR_FILE};
else
  read -e -p "${PR_FILE} already exists, delete it? [Y/n]" TO_DELETE
  [ -z "${TO_DELETE}" ] &&  TO_DELETE="Y"
  [[ $TO_DELETE =~ ^[Yy]$ ]] && rm ${PR_FILE} && touch ${PR_FILE}
fi

while read -r line; do
  sed "s/@@ //g" >> $PR_FILE
done < short_text.txt

cat > $LOAD_SQL_FILE <<EOF
LOAD DATA INFILE '${PR_FILE}' INTO TABLE ${TABLE_NAME}
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
EOF

echo "File with sql -- ${LOAD_SQL_FILE}"

cat $LOAD_SQL_FILE | mysql -u root --database ${DB_NAME}