#!/bin/bash
    host="localhost";
    port="3306";
    user="";
    pwd="";
    db="";
    IFS=$';';

    # find new filenames if dir exist
    if [ -d ftp/publisher ]; then
        getFsMagLink=($(find ftp/publisher -type f -newermt $(date +%Y-%m-%d) | grep -i $(date +%Y-%m-%d) > /tmp/neuLinks.tmp && cat /tmp/neuLinks.tmp | cut -d\/ -f5));
    fi

    # if getFsNames is not NULL store entry in db
    if [ -n $getFsMagLink ]; then
        getFsMagName=($(find ftp/publisher -type f -newermt $(date +%Y-%m-%d) | grep -i $(date +%Y-%m-%d) > /tmp/neuNames.tmp && cat /tmp/neuNames.tmp | cut -d\/ -f5 | cut -d. -f1));

        readarray -t nameArr <<<"$getFsMagName"
        count=${#nameArr[@]};
        for ((i=0;i<count;i++)); do



            #getting magazine Id
            magIdQuery="SELECT magazine.id AS id FROM publisher RIGHT JOIN magazine ON magazine.id_publisher = publisher.id WHERE magazine.ftp_dir = '${nameArr[$i]}'";
            getMagId=$(mysql -ss -N -h $host -P $port -u $user -p$pwd -D $db -Be "$magIdQuery");
            readarray -t magIdArr <<<"$getMagId"
            #getting magazine name
            magNameQuery="SELECT magazine.name AS name FROM publisher RIGHT JOIN magazine ON magazine.id_publisher = publisher.id WHERE magazine.ftp_dir = '${nameArr[$i]}'";
            getMagName=$(mysql -ss -N -h $host -P $port -u $user -p$pwd -D $db -Be "$magNameQuery");
            readarray -t magNameArr <<<"$getMagName"

            #getting mag filename
            readarray -t linkArr <<<"$getFsMagLink"

            #build magazine description
            magDescr=$magNameArr" - "$(date +%Y-%m-%d);

            #testoutputs
            echo "ID: "$magIdArr;
            echo "NAME: "$magNameArr;
            echo "MagLink: "${linkArr[$i]};
            echo "descr: "$magDescr;

            #setting up insert Query!
            insertQuery="INSERT INTO magazine_publication (id, id_magazine, date_of_publication, edition, description, link, cover) SELECT * FROM \
            (SELECT NULL, '$magIdArr', '$(date +%Y-%m-%d)', '30', '$magDescr', '${linkArr[$i]}', 'c:') AS tmp WHERE NOT EXISTS \
            (SELECT link FROM magazine_publication WHERE link ='${linkArr[$i]}') LIMIT 1;"

            #EXECUTE!!!
            insertMag=$(mysql -ss -N -h $host -P $port -u $user -p$pwd -D $db -Be "$insertQuery");
        done
    fi

    # cleanup
    #rm /tmp/neuNames.tmp;
    #rm /tmp/neuPubs.tmp;