#!/bin/bash

#
# Script checks mailbox, retrieves emails, decodes attachments and puts email+attachments data into the DB, also rars the attachments and puts them into DB also
# Supports ZIP, RAR, XLS, XLSX, CSV attachments
#
# v1.0  2019-08-16
#
# email retrieve based on https://outhereinthefield.wordpress.com/2015/06/14/scripting-gmail-download-and-saving-the-attachments-with-fetchmail-procmail-and-munpack/

. /usr/src/m2/framework/bash_functions.sh
. /usr/src/m2/framework/settings.sh
. /etc/profile.d/rvm.sh

HOME_DIR=/home/email2tariff
DIR=$HOME_DIR/Maildir
LOG=/var/log/m2/email2tariff.log

echo >> $LOG
date +%Y\-%0m\-%0d\ %0k\:%0M\:%0S >> $LOG

if grep -Fq '<kolmisoft knows>' /root/.fetchmailrc; then
    echo "Email account is not configured. Exiting" >> $LOG
    exit 0
fi

# fetchmail -v &>> $LOG #verbose mode for troubleshooting
fetchmail 2>&1 | sed "/^skipping message/d" >> $LOG

get_start_date() {
    local start_date=""
    start_date=$(get_confline "Email2tariff_start_date")
    if [ -z "$start_date" ]; then
        confline_date=$(date +'%Y-%m-%d %H:%M:%S' -d '12 hour ago');
        echo -e "Confline Email2tariff_start_date does not exist. Creating new with current date -12 hours \e[92m$start_date\e[0m" >> $LOG
        set_confline "Email2tariff_start_date" "$confline_date"
    fi

    echo "$start_date"
}

get_ssh_data() {
    local server_ip="$1"
    SSH_USERNAME=""
    SSH_PORT=""
    SSH_KEY=""

    read -r SSH_USERNAME SSH_PORT < <( MYSQL_PWD="$DB_PASSWORD" /usr/bin/mysql -h "$DB_HOST" -u "$DB_USERNAME" "$DB_NAME" -sNe "SELECT ssh_username, ssh_port FROM servers WHERE server_ip = '$server_ip'")
    if [[ -z $SSH_USERNAME ]]; then
        SSH_USERNAME=root
    fi

    if [[ -z $SSH_PORT ]]; then
        SSH_PORT=22
    fi

    SSH_KEY="/root/.ssh/id_rsa"

    if [[ -e "/var/www/.ssh/id_rsa" ]]; then
        SSH_KEY="/var/www/.ssh/id_rsa"
    elif [[ -e "/root/.ssh/id_rsa" ]]; then
        SSH_KEY="/root/.ssh/id_rsa"
    else
       echo  "Neither /var/www/.ssh/id_rsa nor /root/.ssh/id_rsa exists in the system" >> $LOG
       echo "Please configure keys to $DB_HOST as descrided here: http://wiki.kolmisoft.com/index.php/Configure_SSH_connection_between_servers" >> $LOG
    fi
}

# ----------- fake new file for testing --------------
#cp -fr $DIR/process/archive/1565868267.10514_0.localhost.localdomain $DIR/new/  #many attachments, xls xlsx csv zip rar
#cp -fr $DIR/process/archive/1565876718.98927_0.localhost.localdomain $DIR/new/  #one xls 6mb
#cp -fr $DIR/process/archive/* $DIR/new/


#just in case
rm -fr $DIR/process/extract/*
rm -fr "/tmp/e2t.rar"


mysql_connect_data_v2 > /dev/null




mv $DIR/new/* $DIR/process/landing/
cd $DIR/process/landing/
shopt -s nullglob

# process each received email


for i in *; do
    echo -e "\nProcessing $i" >> $LOG
    mkdir $DIR/process/extract/$i
    cp $i $DIR/process/extract/$i/
    echo "saving backup $i to archive"  >> $LOG
    mv $i $DIR/process/archive

    # check if needs be converted to utf-8
    OLD_ENCODING=`file -i $DIR/process/extract/$i/$i | cut -f 2 -d";" | cut -f 2 -d=`
    case $OLD_ENCODING in
        iso-8859-1)
            echo "File charset $OLD_ENCODING detected. Converting to UTF-8"  >> $LOG
            iconv -f $OLD_ENCODING -t UTF-8 $DIR/process/extract/$i/$i > $DIR/process/extract/$i/1.$i
            rm $DIR/process/extract/$i/$i
            mv $DIR/process/extract/$i/1.$i $DIR/process/extract/$i/$i
            echo "File converted"  >> $LOG
            ;;
        *)
           echo "File conversion skipped"  >> $LOG
           ;;
    esac

    ii="$DIR/process/extract/$i/$i"

    SOURCE=`sed '/^Content-Type:/q' $ii | sed "s/'/\\\\\'/g" | sed 's/"/\\\"/g'`
    si="/tmp/source_$filename" #tmp file to store email's source
    echo "$SOURCE" > $si

    # http://trac.kolmisoft.com/trac/ticket/17659 
    if [[ $ROCKY9 == 1 ]]; then
        rvm use ruby-3.2.2
    fi
    # For development VMs (#16710)
    ruby_v=`rvm current`
    if [[ $ruby_v == 'ruby-1.9.2-p318' ]]; then
        rvm use ruby-2.1.2@global
    fi

    EMAIL=`ruby "$HOME_DIR/email_source_parser.rb" "$ii"`
    FROM_NAME=`sed "/^=#=from_name=#=$/,/^=#=end_from_name=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d' | sed "s/'/\\\\\'/g" | sed 's/"/\\\"/g'`
    SUBJECT=`sed "/^=#=subject=#=$/,/^=#=end_subject=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d' | sed "s/'/\\\\\'/g" | sed 's/"/\\\"/g'`
    FROM_EMAIL=`sed "/^=#=from_email=#=$/,/^=#=end_from_email=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`
    REPLY_TO=`sed "/^=#=reply_to=#=$/,/^=#=end_reply_to=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`
    TO=`sed "/^=#=to=#=$/,/^=#=end_to=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`
    RECEIVED=`sed "/^=#=received=#=$/,/^=#=end_received=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`
    CC=`sed "/^=#=cc=#=$/,/^=#=end_cc=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`
    MESSAGE_PLAIN=`sed "/^=#=message_plain=#=$/,/^=#=end_message_plain=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d' | sed "s/'/\\\\\'/g" | sed 's/"/\\\"/g'`
    MESSAGE_PLAIN_CHARSET=`sed "/^=#=message_plain_charset=#=$/,/^=#=end_message_plain_charset=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`
    MESSAGE_HTML=`sed "/^=#=message_html=#=$/,/^=#=end_message_html=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d' | sed "s/'/\\\\\'/g" | sed 's/"/\\\"/g'`
    #MESSAGE_HTML=`sed 's/&/\&amp;/g; s/</\&lt;/g; s/>/\&gt;/g; s/"/\&quot;/g; s/'"'"'/\&#39;/g' <<< "$MESSAGE_HTML"`
    MESSAGE_HTML_CHARSET=`sed "/^=#=message_html_charset=#=$/,/^=#=end_message_html_charset=#=$/!d" <<< "$EMAIL" | sed '1,1d' | sed '$d'`

    echo -e "From: \e[92m$FROM_EMAIL\e[0m" >> $LOG
    echo -e "Reply To: \e[92m$REPLY_TO\e[0m" >> $LOG
    echo -e "From Name: \e[92m$FROM_NAME\e[0m" >> $LOG
    echo -e "Subject: \e[92m$SUBJECT\e[0m" >> $LOG
    echo -e "To: \e[92m$TO\e[0m" >> $LOG
    echo -e "Received: \e[92m$RECEIVED\e[0m" >> $LOG
    echo -e "Cc: \e[92m$CC\e[0m" >> $LOG
    #echo -e "Source: \e[92m$SOURCE\e[0m" >> $LOG
    echo -e "Message Plain: \e[92m$MESSAGE_PLAIN\e[0m" >> $LOG
    echo -e "Message Plain Charset: \e[92m$MESSAGE_PLAIN_CHARSET\e[0m" >> $LOG
    echo -e "Message HTML: \e[92m$MESSAGE_HTML\e[0m" >> $LOG
    echo -e "Message HTML Charset: \e[92m$MESSAGE_HTML_CHARSET\e[0m" >> $LOG

    start_date=$(get_start_date)

    # String comparison should be enough
    if [[ "$RECEIVED" < "$start_date" ]]; then
        echo -e "Message Received time \e[92m$RECEIVED\e[0m is earlier than Email2tariff_start_date \e[92m$start_date\e[0m" >> $LOG
        echo -e "Skipping this message" >> $LOG
        continue
    else
        echo -e "Message Received time \e[92m$RECEIVED\e[0m is later than Email2tariff_start_date \e[92m$start_date\e[0m" >> $LOG
        echo -e "Continuing to process message..." >> $LOG
    fi

    # put data into mysql and retrieve tariff_emails.id for future use for the attachments

    TRY_INSERT=0
    ATTEMPT=1
    FAILED=0
    TARIFF_EMAILS_ID=0

    while [ $TRY_INSERT -eq 0 ]
    do
        echo -e "INSERTING EMAIL TO DATABASE" >> $LOG
        echo -e "ATTEMPT: \e[92m$ATTEMPT\e[0m" >> $LOG
        RESULT=`mysql -s -N -h "$DB_HOST" -u "$DB_USERNAME" --default-character-set=utf8 --password=$DB_PASSWORD "$DB_NAME" -e "INSERT INTO tariff_emails (from_email, from_name, reply_to, delivered_to, received, subject, source, cc, message_plain, message_plain_charset, message_html, message_html_charset) VALUES ('$FROM_EMAIL','$FROM_NAME','$REPLY_TO','$TO','$RECEIVED','$SUBJECT','$SOURCE','$CC','$MESSAGE_PLAIN','$MESSAGE_PLAIN_CHARSET','$MESSAGE_HTML','$MESSAGE_HTML_CHARSET'); SELECT LAST_INSERT_ID();" 2>&1`
        if [ $? = 0 ]
        then
            echo -e "\e[92mINSERT SUCCESSFULL\e[0m" >> $LOG
            TARIFF_EMAILS_ID=`sed "/mysql:/d;/Warning:/d" <<< "$RESULT"`
            echo -e "tariff_emails.id: \e[92m$TARIFF_EMAILS_ID\e[0m" >> $LOG
            TRY_INSERT=1
        else
            echo -e "\e[91mINSERT FAILED\e[0m" >> $LOG
            echo -e "REASON: $RESULT" >> $LOG
            ATTEMPT=$((ATTEMPT+1))
            if [ $ATTEMPT -lt 4 ]
            then
                sleep 3
                continue
            fi
            echo >> $LOG
            echo -e "\e[91mFAILED TO INSERT EMAIL TO DATABASE\e[0m" >> $LOG
            echo >> $LOG
            TRY_INSERT=1
            FAILED=1
        fi
    done

    rm -fr $si

    # skip attachments if insertion failed and process next email
    if [ $FAILED -eq 1 ]
    then
        continue
    fi

    ruby "$HOME_DIR/link_attachment_downloader.rb" "$MESSAGE_PLAIN" "$DIR/process/extract/$i" "$LOG" "$DB_HOST" "$DB_USERNAME" "$DB_PASSWORD" "$DB_NAME"

    echo -e "\nUnpacking $i" >> $LOG
    # http://trac.kolmisoft.com/trac/ticket/17659
    if [[ $ROCKY9 == 1 ]]; then
        ATTACHMENT_LIST=`/usr/local/bin/ripmime -i $DIR/process/extract/$i/$i -d $DIR/process/extract/$i -q -v --name-by-type --paranoid --no-multiple-filenames`
    else
        ATTACHMENT_LIST=`ripmime -i $DIR/process/extract/$i/$i -d $DIR/process/extract/$i -q -v --name-by-type --paranoid --no-multiple-filenames`
    fi

    echo -e "Attachment list: \n\e[33m$ATTACHMENT_LIST\e[0m" >> $LOG

    # convert file extensions to lowercase
    for f in $DIR/process/extract/$i/*; do
      e="${f##*.}"
      b="${f%.*}"
      mv "$f" "${b}.${e,,}"
    done

    # un-zip
    for x in $DIR/process/extract/$i/*.zip; do
        A_FILENAME=`basename $x`
        echo -e "Extracting ZIP archive: \e[92m$A_FILENAME\e[0m" >> $LOG
        /usr/bin/unzip -j -o "$x" -d $DIR/process/extract/$i/ >> $LOG
        echo -e "Done" >> $LOG
    done

    # un-rar
    for x in $DIR/process/extract/$i/*.rar; do
        A_FILENAME=`basename $x`
        echo -e "Extracting RAR archive: \e[92m$A_FILENAME\e[0m" >> $LOG
        /usr/local/bin/unrar e -y "$x" $DIR/process/extract/$i/ | grep 'Extracting  '>> $LOG
        echo -e "Done" >> $LOG
    done

    # change spaces in file names with underscores
    for f in $DIR/process/extract/$i/*.{xls,xlsx,csv}; do
        mv "$f" "${f// /_}";
    done

    echo "Final file list:" >> $LOG
    for x in $DIR/process/extract/$i/*.{xls,xlsx,csv}; do
        A_FILE_FULL_NAME=`sed "s/'/\\\\\'/g" <<< "${x##*/}" | sed 's/"/\\\"/g'`
        A_FILE_NAME=`sed "s/'/\\\\\'/g" <<< "${A_FILE_FULL_NAME%.*}" | sed 's/"/\\\"/g'`
        A_EXTENSION="${A_FILE_FULL_NAME##*.}"
        A_TYPE=`file -b --mime-type "$x" | sed 's/\x27/ /g'`
        #A_XDG_TYPE=`xdg-mime query filetype "$x"`
        A_SIZE=`stat -c %s "$x"`
        echo -e "\e[92mFull name: $A_FILE_FULL_NAME\e[0m" >> $LOG
        echo -e "\e[92mName: $A_FILE_NAME\e[0m" >> $LOG
        echo -e "\e[92mExtension: $A_EXTENSION\e[0m" >> $LOG
        echo -e "\e[92mType: $A_TYPE\e[0m" >> $LOG
        #echo -e "\e[92mXDG Type: $A_XDG_TYPE\e[0m" >> $LOG
        echo -e "\e[92mSize: $A_SIZE bytes\e[0m" >> $LOG
        echo >> $LOG

        # archive (RAR) the file to save space (RAR makes smaller files than ZIP somehow...)
        TMP_RAR="/tmp/e2t.rar"
        /usr/local/bin/rar a -ep $TMP_RAR $x

        # If mysql is on external server copy attachment to it via scp

        if [ "$DB_HOST" != "localhost" ] && [ "$DB_HOST" != "127.0.0.1" ]
        then
            echo -e "MYSQL IS ON REMOTE SERVER. COPYING FILE TO REMOTE SERVER" >> $LOG

            # This will fill SSH_KEY, SSH_USERNAME and SSH_PORT
            get_ssh_data "$DB_HOST"

            COPY_TO_REMOTE=$(scp -B -i $SSH_KEY -P$SSH_PORT $TMP_RAR $SSH_USERNAME@$DB_HOST:/tmp 2>&1)
            if [ $? = 0 ]
            then
                echo -e "\e[92mFILE $TMP_RAR SUCCESSFULLY COPIED TO REMOTE SERVER.\e[0m" >> $LOG
            else
                echo -e "\e[91mFILE COPY TO REMOTE SERVER FAILED\e[0m" >> $LOG
                echo -e "REASON: $COPY_TO_REMOTE" >> $LOG
            fi
        else
            echo -e "MYSQL SERVER IS LOCAL. FILE WILL BE READ LOCALLY" >> $LOG
        fi

        echo >> $LOG

        #store attachment data into db
        TRY_INSERT=0
        ATTEMPT=1

        while [ $TRY_INSERT -eq 0 ]
        do
            echo -e "INSERTING ATTACHMENT $A_FILE_FULL_NAME TO DATABASE" >> $LOG
            echo -e "ATTEMPT: $ATTEMPT" >> $LOG
            RESULT=`mysql -s -N -h "$DB_HOST" -u "$DB_USERNAME" --default-character-set=utf8 --password=$DB_PASSWORD "$DB_NAME" -e "INSERT INTO tariff_attachments (tariff_email_id, file_full_name, file_name, file_extension, file_type, size_bytes, data, status) VALUES ('$TARIFF_EMAILS_ID','$A_FILE_FULL_NAME','$A_FILE_NAME','$A_EXTENSION','$A_TYPE','$A_SIZE', LOAD_FILE('$TMP_RAR'), 'waiting_for_analysis'); SELECT LAST_INSERT_ID();" 2>&1`
            if [ $? = 0 ]
            then
                echo -e "\e[92mINSERT SUCCESSFULL\e[0m" >> $LOG
                RESULT=`sed "/mysql:/d;/Warning:/d" <<< "$RESULT"`
                echo -e "ATTACMENT_ID: $RESULT" >> $LOG
                TRY_INSERT=1
            else
                echo -e "\e[91mINSERT FAILED\e[0m" >> $LOG
                echo -e "REASON: $RESULT" >> $LOG
                ATTEMPT=$((ATTEMPT+1))
                if [ $ATTEMPT -lt 4 ]
                then
                    sleep 3
                    continue
                fi
                echo >> $LOG
                echo -e "\e[91mFAILED TO INSERT $A_FILE_FULL_NAME TO DATABASE\e[0m" >> $LOG
                echo >> $LOG
                TRY_INSERT=1
            fi
        done

        # If MySQL is on external server delete attachment from it via ssh
        if [ "$DB_HOST" != "localhost" ] && [ "$DB_HOST" != "127.0.0.1" ]
        then
            echo -e "DELETING FROM REMOTER SERVER" >> $LOG
            DELETE_FROM_REMOTE=$(ssh -n -o BatchMode=yes -i $SSH_KEY -p$SSH_PORT $SSH_USERNAME@$DB_HOST "rm -f $TMP_RAR" 2>&1)
            if [ $? = 0 ]
            then
                echo -e "\e[92mFILE $TMP_RAR SUCCESSFULLY DELETED FROM REMOTE SERVER.\e[0m" >> $LOG
            else
                echo -e "\e[91mFILE DELETION FROM REMOTE SERVER FAILED\e[0m" >> $LOG
                echo -e "REASON: $DELETE_FROM_REMOTE" >> $LOG
            fi
        fi
        echo >> $LOG

        rm -f $TMP_RAR

    done

done

shopt -u nullglob

# cleaning
echo "finishing.." >> $LOG
mv -f $DIR/process/extract/* /$DIR/process/store/

echo "done!" >> $LOG