#!/bin/bash

domain=mydomain.net
spprouters=`dig _spp._udp.$domain srv +short | awk '{print $NF}' | sed 's/\.$//'`

dir="$(mktemp -d)"

production_dir=/var/www/widgets/htdocs/cdr
mkdir -p $production_dir
echo "Working folder set to: $dir"

#grab data off remote servers
for server in $spprouters
do    
    echo "*****Grab SQL Data from Remote Server: $server *****"
    # Dump data to csv.  Need elevated priv to do copy command
    psql -U master testdb -h $server -t -c "copy (SELECT CASE WHEN DATE_TRUNC('MONTH', ABSTIME(start_ts)) = DATE_TRUNC('MONTH', CURRENT_DATE) THEN 'current' ELSE DATE_TRUNC('MONTH', ABSTIME(start_ts))::DATE::TEXT END, 
    id, 
    status, 
    class, 
    dir, 
    volid, 
    f_user, 
    t_user, 
    ABSTIME(start_ts),
    ABSTIME(end_ts), 
    elapsed, 
    callid, f_uri, t_uri, lcr_tag 
    FROM widgets) to '/tmp/"$server".db' With CSV DELIMITER ',';"    
    scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $server:/tmp/"$server.db" "$dir/"
    
    first_time=1
    #process data
    for tmpfile in $dir/$server.db
    do
        echo "Evaluate $tmpfile"
        number_of_records=$(cat $tmpfile | wc -l)
        echo "number of records in $tmpfile.db file is: $number_of_records"
        if [ "$number_of_records" -gt 0 ]; then
            echo "==== Processing $tmpfile ===="
            while IFS=, read -r date_for_filename rec_id rec_id_onwards; do
                    #any CDR that's current will stay in the db.  Don't add to inventory for deletion.
                    if ! [ "$date_for_filename" == "current" ]
                    then
                        echo "$rec_id" >> $dir/$server.records_to_delete.txt;
                    else
                        if [ $first_time == 1 ]
                        then
                            #delete the existing .current.csv file if this first time trying to write to it this cycle.       
                            echo "tested first time and its true"          
                            tmpname="${server#*.}" # take file name and strip off server id
                            rm $production_dir/$tmpname.current.csv
                            first_time=0
                        fi
                    fi
                    echo "$rec_id,$rec_id_onwards" >> $dir/$server.$date_for_filename.csv;	
            done < $tmpfile
                
            mv $tmpfile $tmpfile.done
            
            #clean up any trailing , in records_to_delete file
            sed '$s/,$//' $dir/$server.records_to_delete.txt > /dev/null

            #copy records to delete file to respective servers.  faster than running from widgets.
            scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $dir/$server.records_to_delete.txt $server:/tmp/

            db_count_pre_prune=$(psql -U master testdb -h $server -t -c "SELECT COUNT(*) FROM widgets")
            echo "$db_count_pre_prune records in db before pruning db"

            # trigger the deletion on remote machine.
            deletion_status=$(psql -U master testdb -h $server -c "CREATE TEMP TABLE tmp_cdr (id int); COPY tmp_cdr FROM '/tmp/$server.records_to_delete.txt'; DELETE FROM widgets USING tmp_cdr WHERE widgets.id = tmp_cdr.id;")
            echo "$deletion_status is results of deletion attempt"

            db_count_post_prune=$(psql -U master testdb -h $server -t -c "SELECT COUNT(*) FROM widgets")

            echo "$db_count_post_prune $server " >> "$dir/audit.txt"
        else
            echo "No records to process on $server"
        fi
    done
done

#validate remaining records in db.	
total_current_csv_records="$(wc -l $dir/*current.csv |awk '{print $1}' | tail -1)"
echo "$total_current_csv_records = number of records is *current.csv files"
total_in_db=$(cat $dir/audit.txt | awk '{print $1}' | awk '{ sum+=$1 } END { print sum }')
echo "$total_in_db = number of records remaining in db"
if ! [ "$total_in_db" == "$total_current_csv_records" ]
then
    echo "$(date) - whoops! Expecting $total_current_csv_records records (combined) in servers. But actual count is $total_in_db." >> $production_dir/cdr-prune.exceptions
fi

cd $dir
for csvfile in *.csv
do      	  
    echo "$csvfile is the csvfile"
    tmpname="${csvfile#*.}" # take file name and strip off server id
    cat $csvfile >> $production_dir/$tmpname
done

#Clean up
rm -r $dir