#!/usr/bin/env bash

# Fix All
# This script will fix the data for all *.mbox files stored in a
# directory. Run this script inside the directory that you would like to
# fix data in.

RUN_DIR="$(pwd)"
SCRIPTS_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && cd .. && pwd )"

# Database credentials are taken from ~/.my.cnf
DATABASE="iaa_vehicles"

# Go through the files again -- this time, the loop will happen
# chronologically.
for file in *.mbox
do
  out_dir="${file%.mbox}"
  mkdir -p "$out_dir/extracted"
  mkdir -p "$out_dir/clean"

  # Extract the messages from this file
  echo "Extracting files"
  git mailsplit -o"$out_dir" "$file" 2>&1 >/dev/null
  cd "$out_dir"
  munpack 0* 2>&1 >/dev/null
  
  # Rename the files from the encoded-word filename given through MIME
  for f in "="*
  do
    mv "$f" "extracted/$(echo "$f" | sed 's/=Xutf-8XBX//g' | sed 's/X=//g' | base64 -d)"
  done

  ######################################################################
  ### CLEANING FILES ###################################################
  echo "Cleaning files"
  cd "extracted"
  if [[ -e "Recommendation Engine BIDDER ID Bidding Data - Automated.csv" ]]
  then
    "$SCRIPTS_DIR/fix_emp_bids/fix_emp_bids.sh" "Recommendation Engine BIDDER ID Bidding Data - Automated.csv"
    cat "Recommendation Engine BIDDER ID Bidding Data - Automated.clean.csv" | sed 's/,,/,NULL,/g' | sed 's/,$/,NULL/g' > "../clean/emp_bids.csv"
  fi
  if [[ -e "Recommendation Engine Bidding Data - Automated2.csv" ]]
  then
    "$SCRIPTS_DIR/fix_bids/fix_bids.sh" "Recommendation Engine Bidding Data - Automated2.csv"
    cat "Recommendation Engine Bidding Data - Automated2.clean.csv" | sed 's/,,/,NULL,/g' | sed 's/,$/,NULL/g' > "../clean/bids.csv"
  fi
  if [[ -e "Recommendation Engine PreBid Data.csv" ]]
  then
    "$SCRIPTS_DIR/fix_prebids/fix_prebids.sh" "Recommendation Engine PreBid Data.csv"
    cat "Recommendation Engine PreBid Data.clean.csv" | sed 's/,,/,NULL,/g' | sed 's/,$/,NULL/g' > "../clean/prebids.csv"
  fi
  if [[ -e "Recommendation Engine Sold Data - Automated.csv" ]]
  then
    "$SCRIPTS_DIR/fix_sold/fix_sold.sh" "Recommendation Engine Sold Data - Automated.csv"
    cat "Recommendation Engine Sold Data - Automated.clean.csv" | sed 's/,,/,NULL,/g' | sed 's/,$/,NULL/g' > "../clean/sold_vehicles.csv"
  fi
  if [[ -e "ASAP Buyer ID_Bidder ID.csv" ]]
  then
    echo "asap_buyer_id,employee_id" > "../clean/emp_ids.csv"
    cat "ASAP Buyer ID_Bidder ID.csv" | tr -d '\r' | grep -v -e '^$' | tail -n +2 >> "../clean/emp_ids.csv"
  fi
  if [[ -e "Buyer Email Addresses.csv" ]]
  then
    "$SCRIPTS_DIR/fix_buyers/fix_buyers.sh" "Buyer Email Addresses.csv"
    cat "Buyer Email Addresses.clean.csv" | sed 's/,,/,NULL,/g' | sed 's/,$/,NULL/g' > "../clean/buyers.csv"
  fi
  if [[ -e "Recommendation Engine Set for Sale Data - Automated.csv" ]]
  then
    "$SCRIPTS_DIR/fix_setforsale/fix_setforsale.sh" "Recommendation Engine Set for Sale Data - Automated.csv"
    cat "Recommendation Engine Set for Sale Data - Automated.clean.csv" | sed 's/,,/,NULL,/g' | sed 's/,$/,NULL/g' > "../clean/setforsale_vehicles.csv"
  fi

  ######################################################################
  ### IMPORTING INTO DATABASE ##########################################
  cd "../clean"
  if [[ -e "emp_bids.csv" ]]
  then
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --replace --local "$DATABASE" "emp_bids.csv"
  fi
  if [[ -e "bids.csv" ]]
  then
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --replace --local "$DATABASE" "bids.csv"
  fi
  if [[ -e "prebids.csv" ]]
  then
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --replace --local "$DATABASE" "prebids.csv"
  fi
  if [[ -e "sold_vehicles.csv" ]]
  then
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --columns 'stock_number,asap_buyer_id,branch_name,buyer_country_description,model_year,vehicle_segment,make,model,primary_damage_description,buyer_type_description,fiscal_month_id,provider_group_name,region_name,sale_title_type_category,auction_date,acv,vehicle_class,count_of_run_and_drive,loss_type_description,gross_return,provider_type,sold_model_year_age,vehicle_type' --replace --local "$DATABASE" "sold_vehicles.csv"
  fi
  if [[ -e "emp_ids.csv" ]]
  then
    # Truncate the employee IDs-to-buyer IDs relationship table, since
    # this only works on a list of all the currently-active buyers.
    echo "TRUNCATE TABLE emp_ids;" | mysql "$DATABASE"
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --columns 'asap_buyer_id,employee_id' --replace --local "$DATABASE" "emp_ids.csv"
  fi
  if [[ -e "buyers.csv" ]]
  then
    # Truncate the buyer's table, since this is a complete list of the
    # currently-active buyers.
    echo "TRUNCATE TABLE buyers;" | mysql "$DATABASE"
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --replace --local "$DATABASE" "buyers.csv"
  fi
  if [[ -e "setforsale_vehicles.csv" ]]
  then
    mysqlimport --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='"' --ignore-lines=1 --columns 'stock_number,provider_group_name,model_year,make,vehicle_segment,model,branch_name,auction_date,primary_damage_description,fiscal_month_id,region_name,sale_title_type_category,vehicle_class,count_of_run_and_drive,loss_type_description,provider_type,acv,vehicle_type,destination_branch' --replace --local "$DATABASE" "setforsale_vehicles.csv"
  fi

  cd "$RUN_DIR"
done

# Do we have a fullStockInfo.json file somewhere inside our directory?
# We can import that automatically, too.
if [[ -e "fullStockInfo.json" ]]
then
  echo "Converting fullStockInfo.json to TSV..."
  "$SCRIPTS_DIR/../ddr2csv/convert.sh" "fullStockInfo.json" "ddr.tsv"

  if [[ "0" != "$?" ]]
  then
    echo "Failed to convert fullStockInfo.json to TSV" >&2
    exit 1
  fi

  # Import the DDR data into the database
  mysqlimport --fields-terminated-by='\t' --ignore-lines=1 --columns "$(head -n 1 ddr.tsv | tr '\t' ',')" --replace --local "$DATABASE" "ddr.tsv"
fi

echo "Done"