#! /bin/bash

mkdir ./database_conversion/inserts
for f in `ls ./database_conversion/old_columns`; do
	db=$f
#	echo $db
	stmt="attach database './database/$db.sqlite' as old_$db;"
	echo $stmt >> ./database_conversion/inserts/$db.sql
stmt=""
	for t in `ls ./database_conversion/new_columns/$db`; do
		if [ ! -e ./database_conversion/old_columns/$db/$t ]
		then
			echo "*** NO Table $db/$t ***"
			set -f
			if [ "$t" == "curve" ]
			then
			stmt="delete from curve;
			insert into curve select * from old_rundown.bad_curve;
			insert into curve select * from old_rundown.good_curve; "
			fi
			if [ "$t" == "fastening_event" ]
			then
			stmt="delete from fastening_event;
			insert into fastening_event select * from old_rundown.bad_fastening_event;
			insert into fastening_event select * from old_rundown.good_fastening_event; "
			fi
			if [ "$t" == "bad_curve" ]
			then
			stmt="insert into bad_curve select * from old_rundown.curve where (select rundown_status from old_rundown.rundown where id = old_rundown.curve.rundown_id and old_rundown.rundown.rundown_status == 0) == 0;"
			fi
			if [ "$t" == "good_curve" ]
			then
			stmt="insert into good_curve select * from old_rundown.curve where (select rundown_status from old_rundown.rundown where id = old_rundown.curve.rundown_id and old_rundown.rundown.rundown_status == 1) == 1;"
			fi
			if [ "$t" == "bad_fastening_event" ]
			then
			stmt="insert into bad_fastening_event select * from old_rundown.fastening_event where (select rundown_status from old_rundown.rundown where id = old_rundown.fastening_event.rundown_id and old_rundown.rundown.rundown_status == 0) == 0;"
			fi
			if [ "$t" == "good_fastening_event" ]
			then
			stmt="insert into good_fastening_event select * from old_rundown.fastening_event where (select rundown_status from old_rundown.rundown where id = old_rundown.fastening_event.rundown_id and old_rundown.rundown.rundown_status == 1) == 1;"
			fi			
			
			echo $stmt >> ./database_conversion/inserts/$db.sql
			set +f
			continue
		fi
                echo "Processing Table $db/$t"
if [ "$t" == "version" ]
		then
break
fi

		if [ "$t" == "multi_spindle" ]
		then
		set -f
		stmt="delete from multi_spindle;
		insert into multi_spindle select * from old_configuration.multi_spindle group by spindle; "
		echo $stmt >> ./database_conversion/inserts/$db.sql
		set +f
		continue
		fi
		cols=''
		for c in `cat ./database_conversion/new_columns/$db/$t | awk -F',' '{print $1}'`; do
			cols=$cols','$c
		done
		cols=`echo $cols | sed 's/,//'`
		stmt="delete from $t; insert into $t ( $cols ) select "
		cols=''
		for c in `cat ./database_conversion/new_columns/$db/$t | awk -F',' '{print $1}'`; do
			old_c=`grep "^$c," ./database_conversion/old_columns/$db/$t | awk -F',' '{print $1}' | tr -d '\n'`
			if [ "$c" == "$old_c" ]
			then
				cols=$cols','$c
			else
				missing_c=`grep "^$c," ./database_conversion/new_columns/$db/$t | awk -F',' '{print $2}'`
				cols=$cols','$missing_c
echo "Missing column in $db/$t: New Column = $c, Old Column = $old_c, cols set to: $cols"
			fi
			
		done
		cols=`echo $cols | sed 's/,//'`
		stmt="$stmt$cols from old_$db.$t;"
		echo $stmt >> ./database_conversion/inserts/$db.sql
stmt=""
	done
done

