#!/bin/bash

. paths.sh
PGDATA=pgdata_imdb
pg_ctl -D $PGDATA stop
pkill -U $PGUSER -9 postgres || true
rm -rf logfile.log

sleep 1
pg_ctl -w -D $PGDATA -l logfile.log start

filenum=0
TESTS_MAX_NUM=100

threshold=0.1
results_num=7
never_executed=0

# Clear learning data.
psql -c "DROP EXTENSION IF EXISTS AQO;"
psql -c "CREATE EXTENSION IF NOT EXISTS AQO;"

psql -c "ALTER SYSTEM SET aqo.mode = 'learn';"
psql -c "ALTER SYSTEM SET aqo.force_collect_stat = 'false';"
psql -c "SELECT pg_reload_conf();"

iters=()
hashes=()

for file in ../imdb/queries/*.sql
do
  # Get filename
  short_file=$(basename "$file")
  echo -n "EXPLAIN ANALYZE VERBOSE " > test.sql
  cat $file >> test.sql
  result=$(psql -f test.sql)

  hash=$(echo "$result" | grep "Query hash:" | awk '{print $3}' | sed 's/,//')
  echo "Query $short_file. hash: $hash"
  for (( i=0; i<TESTS_MAX_NUM; i++ ))
  do
    err1=0
    psql -f $file >> /dev/null
    err=$(psql -qtc "SELECT err_aqo FROM aqo_status($hash);")
    err1=$(sed -E 's/([+-]?[0-9.]+)[eE]\+?(-?)([0-9]+)/(\1*10^\2\3)/g' <<< $err)
	res=$(echo "$err1<0" | bc -l)

	if [ $res -eq "1" ]
	then
      echo "AQO rejected $short_file for reason of 'Never executed' nodes are exists in the Plan."
      never_executed=$((never_executed+1))
      break;
    fi

	if [[ $i -lt $results_num ]]
	then
	  echo "- TEST $filenum, shot $i [required], Time: $(date +"%T") err: $err1"
	  continue;
	fi

	nstat=$(psql -qtc "SELECT array_length(cardinality_error_with_aqo,1)
						FROM aqo_query_stat aqs
						WHERE aqs.query_hash = $hash;")

    # ################## Check convergence #####################################
	sign="0"
	for (( j=0; j<$results_num; j++ ))
    do
      err=$(psql -qtc "SELECT cardinality_error_with_aqo[$(( nstat-j ))]
						FROM aqo_query_stat aqs
						WHERE aqs.query_hash = $hash;")
	  err1=$(sed -E 's/([+-]?[0-9.]+)[eE]\+?(-?)([0-9]+)/(\1*10^\2\3)/g' <<< $err)
	  res=$(echo "$err1<$threshold" | bc -l)

	  if [ $res -eq "0" ]
	  then
	    sign="1"
	    echo "- TEST $filenum, shot $i Convergence was not reached j: $j err: $err1"
	    break;
	  fi
    done
    # ####################### End of convergence checking ######################

 	if [ $sign -eq "0" ]
	then
	  iters=("${iters[@]}" $i)
 	  hashes=("${hashes[@]}" $hash)
	  echo "Test $filenum ($short_file) converged at $i steps err: $err1, hash: $hash, i: $i"
	  break;
	fi
  done
  filenum=$((filenum+1))
done

echo "Print statistics"
for ((t=0; t<${#iters[@]}; t++ )); do
  echo "$t $((iters[t])) $((hashes[t]))"
done

echo "Tests cases with 'Never Executed' nodes: $never_executed"
pg_ctl -D $PGDATA stop