#!/bin/bash

### IMAP must be enabled on the GMail for business e-mail account
### See here for additional information: https://support.google.com/a/answer/105694?hl=en

export IFS='
'

### Function definitions

function checkDirExists(){
	if [ ! -d ${1} ]; then
		echo "Creating ${1}..."
		mkdir -p "${1}"
	fi
}

function checkInstalled(){
	echo -n "Checking for package ${1} - ${2}..."
	which "$1" >/dev/null 2>&1
	if [ $? -ne 0 ]; then
		echo " not installed."
		echo "Do you have sudo access on this server?"
		read ANSWER
		case "${ANSWER}" in
			"Y"|"y"|"yes"|"Yes"|"YES"):
			  sudo yum install -y "${1}"
			  ;;
			*)
			  echo "You'll have to get ${1} - ${2} installed on the server to continue..."
			  exit;
			  ;;
		esac
	else
		echo " good!"
	fi
}

function createConfigFile(){
	touch ${1}
	chmod 600 ${1}
}

function createGetMailRC(){
	echo "This will configure the getmail program to retrieve e-mail from GMail"
	if [ "${IMAPSERVERNAME}x" == "x" ]; then
		echo -n "Server name [hit enter for default of imap.gmail.com]: "
		read IMAPSERVERNAME
		if [ "${IMAPSERVERNAME}x" == "x" ]; then
			IMAPSERVERNAME="imap.gmail.com"
		fi
		insertConfigVar IMAPSERVERNAME "${IMAPSERVERNAME}" "${CONFIGFILE}"
	fi
	if [ "${USERNAME}x" == "x" ]; then
		echo -n "E-mail address associated with the GMail account: "
		read USERNAME
		while [ "${USERNAME}x" == "x" -o "${USERNAME}quit" == "quit" ]; do
			echo "Ah ah ahhh, you didn't say the magic word!"
			sleep 2
			echo -n "Ahem, seriously though... what's the e-mail address (type quit to exit)? "
			read USERNAME
		done
		if [ "${USERNAME}quit" == "quit" ]; then
			exit 255
		fi
		insertConfigVar USERNAME "${USERNAME}" "${CONFIGFILE}"
	fi
	if [ "${PASSWORD}x" == "x" ]; then
		echo -n "Password for the GMail account: "
		read -s PASSWORD
		while [ "${PASSWORD}x" == "x" -o "${PASSWORD}quit" == "quit" ]; do
			echo "It's not going to work without a password (type quit to exit)..."
			echo -n "Password for the GMail account: "
			read -s PASSWORD
		done
		if [ "${PASSWORD}quit" == "quit" ]; then
			exit 255
		fi
		insertConfigVar PASSWORD "${PASSWORD}" "${CONFIGFILE}"
	fi
	touch "${GETMAILRC}"
	if [ ! -s "${GETMAILRC}" ]; then
		cat > "${GETMAILRC}" <<- EOF
		# Configuration file to retrieve messages over secure IMAP
		# and send them to procmail

		[retriever]
		type=SimpleIMAPSSLRetriever
		server=${IMAPSERVERNAME}
		username=${USERNAME}
		password=${PASSWORD}

		[destination]
		type=Maildir
		path=${REPORTDIR}/

		[options]
		verbose=0
		read_all=false
		delete=false
		delete_after=0
		delete_bigger_than=0
		max_bytes_per_session=0
		max_message_size=0
		max_messages_per_session=0
		delivered_to=false
		received=false
		message_log=~/getmail.log
		message_log_syslog=false
		message_log_verbose=true
		EOF
	fi
}

function checkMySQL(){
	QUERY="SELECT 1 FROM ${MYSQLDB}.${MYSQLTABLE}";
	doDBQuery "${QUERY}"
	if [ $? -eq 0 ]; then
		echo 'MySQL tables look good, continuing.';
	else
		echo -e "\nMySQL encountered an error for ${QUERY}!  Run MySQL setup?";
		read ANSWER
		case "${ANSWER}" in
			"Y"|"y"|"yes"|"Yes"|"YES"):
			  doMySQLSetup
			  ;;
			*)
			  echo "MySQL seems to be misconfigured.  Check the config file for errors or typos."
			  exit;
			  ;;
		esac
	fi
}

function createMySQLDB(){
	doDBQuery "CREATE DATABASE IF NOT EXISTS ${MYSQLDB};"
}

function createMySQLTable(){
	doDBQuery "CREATE TABLE IF NOT EXISTS ${MYSQLDB}.${1} (
	  insertion_order varchar(255) COLLATE utf8_bin NOT NULL,
	  insertion_order_id varchar(255) COLLATE utf8_bin NOT NULL,
	  line_item varchar(255) COLLATE utf8_bin NOT NULL,
	  line_item_id varchar(255) COLLATE utf8_bin NOT NULL,
	  site varchar(255) COLLATE utf8_bin NOT NULL,
	  site_id varchar(255) COLLATE utf8_bin NOT NULL,
	  impressions int(32) NOT NULL,
	  clicks int(32) NOT NULL,
	  total_conversions int(32) NOT NULL,
	  revenue_adv_currency decimal(9,6) DEFAULT NULL,
	  click_rate decimal(9,6) DEFAULT NULL,
	  daterange varchar(255) COLLATE utf8_bin DEFAULT NULL,
	  INDEX insertion_order_id (insertion_order_id) USING BTREE,
	  INDEX line_item_id (line_item_id) USING BTREE,
	  INDEX site_id (site_id) USING BTREE,
	  INDEX site (site) USING BTREE,
	  PRIMARY KEY makeitsonum1 (insertion_order_id,line_item_id,site_id,daterange)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;"
}

function doDBQuery(){
	mysql --user="${MYSQLUSER}" --password="${MYSQLPW}" --host="${MYSQLHOST}" -e "${1}">/dev/null 2>&1
	if [ "$?" -ne 0 ]; then
		return 1;
	else
		return 0;
	fi
}

function doMySQLSetup(){
	echo "Note, you should have created a user in MySQL by now and assigned it permissions to the values you are about to enter."
	echo -e "E.g. 
\t#MariaDB [(none)]> create user '${MYSQLUSER}'@'${MYSQLHOST}' identified by '${MYSQLPW}';
\t#Query OK, 0 rows affected (0.00 sec)
\t#MariaDB [(none)]> grant all on ${MYSQLDB}.${MYSQLTABLE} to '${MYSQLUSER}'@'$(uname -n)';
\t#Query OK, 0 rows affected (0.00 sec)"
	echo -n "MySQL server hostname [hit enter for localhost]: "
	read MYSQLHOST
	if [ "${MYSQLHOST}x" == "x" ]; then
		MYSQLHOST='localhost'
	fi
	insertConfigVar MYSQLHOST "${MYSQLHOST}" "${CONFIGFILE}"
	echo -n "MySQL user name: "
	read MYSQLUSER
	while [ "${MYSQLUSER}x" == "x" -o "${MYSQLUSER}quit" == "quit" ]; do
		echo "We really need a MySQL user defined (type quit to exit)... "
	done
	insertConfigVar MYSQLUSER "${MYSQLUSER}" "${CONFIGFILE}"
	echo -n "MySQL password: "
	read -s MYSQLPW
	while [ "${MYSQLPW}x" == "x" -o "${MYSQLPW}quit" == "quit" ]; do
		echo "We really need a MySQL password defined (type quit to exit)... "
	done
	insertConfigVar MYSQLPW "${MYSQLPW}" "${CONFIGFILE}"
	echo -en "\nMySQL database name [hit enter and a default value of 'elancedev' will be assigned]: "
	read MYSQLDB
	if [ "${MYSQLDB}x" == "x" ]; then
		MYSQLDB='elancedev'
	fi
	insertConfigVar MYSQLDB "${MYSQLDB}" "${CONFIGFILE}"
	echo -n "MySQL table name [hit enter and a default value of 'reports' will be assigned]: "
	read MYSQLTABLE
	if [ "${MYSQLTABLE}x" == "x" ]; then
		MYSQLTABLE='reports'
	fi
	insertConfigVar MYSQLTABLE "${MYSQLTABLE}" "${CONFIGFILE}"
	echo -n "Create the default table and database now?"
	read ANSWER
	case "${ANSWER}" in
		"Y"|"y"|"yes"|"Yes"|"YES"):
		  createMySQLTable "${MYSQLTABLE}"
		  createMySQLDB "${MYSQLDB}"
		  ;;
		*)
		  echo "You'll have to get ${1} - ${2} installed on the server to continue..."
		  exit;
		  ;;
	esac
	echo "Okay, got some values.  Checking MySQL again."
	checkMySQL
	if [ $? -eq 0 ]; then
		echo "MySQL looks good."
	else
		echo "MySQL still not good.  :("
	fi
}

function insertConfigVar(){
	grep -q "^${1}=" "${3}"
	if [ $? -eq 0 ]; then
		sed -i "s/^${1}=.*$/${1}=\"${2}\"/g" ${3}
	else
		echo "${1}=\"${2}\"">>"${CONFIGFILE}"
	fi
}

function isFirstRun(){
	checkInstalled getmail "Getmail (retrieves email)"
	checkInstalled ripmime "Ripmime (removes attachments)"
	#checkDirExists "${HOME}/.getmail"
	#checkDirExists ${REPORTDIR}
	for DIR in "${REPORTDIR}/processed" "${REPORTDIR}" "${REPORTDIR}/cur" "${REPORTDIR}/new" "${REPORTDIR}/tmp" "${HOME}/.getmail"; do
		checkDirExists "${DIR}"
	done
	if [ -s "${CONFIGFILE}" -a -s "${GETMAILRC}" ]; then
		echo "Config file exists, using those values."
		source "${CONFIGFILE}"
	else
		echo "Did not find a config file... running setup."
		createConfigFile "${CONFIGFILE}"
		createGetMailRC
	fi
	checkMySQL
}

function processCSV(){
	if [ ! -f "${1}" ]; then
		echo "Something bad happened... ${1} isn't there!"
	fi
	#MariaDB [elancedev]> describe reports;
	#+----------------------+--------------+------+-----+---------+-------+
	#| Field                | Type         | Null | Key | Default | Extra |
	#+----------------------+--------------+------+-----+---------+-------+
	#| insertion_order      | varchar(255) | NO   |     | NULL    |       |
	#| insertion_order_id   | varchar(255) | NO   | MUL | NULL    |       |
	#| line_item            | varchar(255) | NO   |     | NULL    |       |
	#| line_item_id         | varchar(255) | NO   | MUL | NULL    |       |
	#| site                 | varchar(255) | NO   | MUL | NULL    |       |
	#| site_id              | varchar(255) | NO   | MUL | NULL    |       |
	#| impressions          | int(32)      | NO   |     | NULL    |       |
	#| clicks               | int(32)      | NO   |     | NULL    |       |
	#| total_conversions    | int(32)      | NO   |     | NULL    |       |
	#| revenue_adv_currency | decimal(9,6) | YES  |     | NULL    |       |
	#| click_rate           | decimal(9,6) | YES  |     | NULL    |       |
	#| daterange            | varchar(255) | YES  |     | NULL    |       |
	#+----------------------+--------------+------+-----+---------+-------+
	echo -n "Processing ${1}... line "; 
	LINENUM=0
	TMPCSV="${REPORTDIR}/tmp/$(basename ${1})"
	cat "${1}" | tr -dc '[\011\012\015\040-\176]' | strings | egrep -v '^(\,{2,}|Report Time|Date Range|Group By|Filter by)'> "${TMPCSV}" 2>/dev/null
	while read LINE; do
		((LINENUM++))
		if [ ${LINENUM} -eq 1 ]; then
			#we assume these are the column names, we can skip those
			continue;
		fi
		COL1INSORD=$(echo "${LINE}" | awk -F, '{print $1}')
		COL2INSORDID=$(echo "${LINE}" | awk -F, '{print $2}')
		#COL3=$(echo "${LINE}" | awk -F, '{print $3}')
		#COL4=$(echo "${LINE}" | awk -F, '{print $4}')
		COL5LITEM=$(echo "${LINE}" | awk -F, '{print $5}')
		COL6LITEMID=$(echo "${LINE}" | awk -F, '{print $6}')
		#COL7=$(echo "${LINE}" | awk -F, '{print $7}')
		#COL8=$(echo "${LINE}" | awk -F, '{print $8}')
		#COL9=$(echo "${LINE}" | awk -F, '{print $9}')
		COL10SITE=$(echo "${LINE}" | awk -F, '{print $10}')
		COL11SITEID=$(echo "${LINE}" | awk -F, '{print $11}')
		#COL12=$(echo "${LINE}" | awk -F, '{print $12}')
		#COL13=$(echo "${LINE}" | awk -F, '{print $13}')
		#COL14=$(echo "${LINE}" | awk -F, '{print $14}')
		COL15IMPR=$(echo "${LINE}" | awk -F, '{print $15}')
		COL16CLKS=$(echo "${LINE}" | awk -F, '{print $16}')
		COL17TCON=$(echo "${LINE}" | awk -F, '{print $17}')
		#COL18=$(echo "${LINE}" | awk -F, '{print $18}')
		#COL19=$(echo "${LINE}" | awk -F, '{print $19}')
		COL20RADVC=$(echo "${LINE}" | awk -F, '{print $20}')
		#COL21=$(echo "${LINE}" | awk -F, '{print $21}')
		COL22CTR=$(echo "${LINE}" | awk -F, '{print $22}')
		#echo "$COL1 $COL2 $COL3"; exit
		DATERANGE=$(strings -e s ${1} | awk -F, '/Date Range:/ {print $2}' | sed 's/,{2,}//g')
		QUERY="REPLACE INTO ${MYSQLDB}.${MYSQLTABLE} (insertion_order,insertion_order_id,line_item,line_item_id,site,site_id,impressions,clicks,total_conversions,revenue_adv_currency,click_rate,daterange) VALUES ('${COL1INSORD}','${COL2INSORDID}','${COL5LITEM}','${COL6LITEMID}','${COL10SITE}','${COL11SITEID}','${COL15IMPR}','${COL16CLKS}','${COL17TCON}','${COL20RADVC}','${COL22CTR}','${DATERANGE}');"
		doDBQuery "${QUERY}"
		echo -n "${LINENUM}, "
	done<"${TMPCSV}"
	\rm "${TMPCSV}"
	\mv "${1}" "${REPORTDIR}/processed/$(basename ${1})"
	echo "done!"
	echo "Done processing ${CSV}, archived to ${REPORTDIR}/processed/$(basename ${1})"
	LINENUM=0
}

### Main section

### Some variables
REPORTDIR="${HOME}/reports-to-mysql"
CONFIGFILE="${REPORTDIR}/.config"
GETMAILRC=${HOME}/.getmail/getmailrc

isFirstRun

GETMAIL=$(which getmail)
RIPMIME=$(which ripmime)

"${GETMAIL}"

SOMEMAILDIR="${REPORTDIR}"/new
AWK=$(which awk)
HEAD=$(which head)
EMAILCOUNT=0
CSVCOUNT=0
for EMAIL in $(ls -1 "${SOMEMAILDIR}"); do
	((EMAILCOUNT++))
	SUBJECT=$(${AWK} '/^Subject:/ {$1=""; print $0}' "${SOMEMAILDIR}/${EMAIL}" | ${HEAD} -1)
	DATE=$(${AWK} '/^Date:/ {$1=""; print $0}' "${SOMEMAILDIR}/${EMAIL}" | ${HEAD} -1)
	FROM=$(${AWK} '/^From:/ {$1=""; print $0}'  "${SOMEMAILDIR}/${EMAIL}" | ${HEAD} -1)
	TO=$(${AWK} '/^To:/ {$1==""; print $0}' "${SOMEMAILDIR}/${EMAIL}" | ${HEAD} -1)
	echo "Processing e-mail on ${DATE} with subject \"${SUBJECT}\", to ${TO}, from ${FROM}..."
	"${RIPMIME}" -i "${SOMEMAILDIR}/${EMAIL}" -d "${SOMEMAILDIR}"
	mv "${SOMEMAILDIR}"/*csv "${REPORTDIR}">/dev/null 2>&1
	rm "${SOMEMAILDIR}/${EMAIL}"
done
for CSV in $(ls -1 ${REPORTDIR}/*.csv 2>/dev/null); do
	((CSVCOUNT++))
	processCSV "${CSV}"
done
\rm "${SOMEMAILDIR}/"* >/dev/null 2>&1
if [ ${EMAILCOUNT} -eq 0 -a ${CSVCOUNT} -eq 0 ]; then
	echo "No new e-mails have arrived in ${USERNAME}, and no ad hoc uploads found in ${REPORTDIR}."
elif [ ${EMAILCOUNT} -eq 0 -a ${CSVCOUNT} -gt 0 ]; then
	echo "Processed ${CSVCOUNT} ad hoc uploads."
elif [ ${EMAILCOUNT} -gt 0 -a ${CSVCOUNT} -eq 0 ]; then
	echo "Processed ${EMAILCOUNT} e-mails, but none had .csv attachments."
elif [ ${EMAILCOUNT} -gt 0 -a ${CSVCOUNT} -gt 0 ]; then
	echo "Processed ${CSVCOUNT} .csv files from ${EMAILCOUNT} emails."
fi