#!/bin/bash

###############################################################################

# Script by T. L. Ford, 9/2020.
# http://www.Cattail.Nu
# Send email if you have comments/corrections.
# T@Cattail.Nu
# http://www.Cattail.Nu/tap_forms/tap_forms_sqlite3_port/index.html

###############################################################################

# Notes:

# This script may fail - it needs more testing on more varied databases.

# Dates are imported as text and will need post-import processing.
# A way to do this correctly (SQLite3 stores them in several ways)
# is on the potential to-do list. I'm also planning a way to
# automate linking forms on field=field keys.

# BLOBs / Picture data are usually unsuitable for CSV files.
# You can still use the script to create the Form and Fields.

# If you have used non-standard table or field names in your database,
# you will need to modify the resulting JS.  (i.e. [field name], [table name])

# This does not support multi-field primary keys.

# If you have an apostrophe ' inside a field default value, you will
# need to edit the code for that line, adding a \: 'Jane\'s box';

# You can play with debugging the script and seeing the steps by
# commenting out the rm commands at the end of the loop: 
# search for: clean up - comment with # to debug

###############################################################################


# sed test
if ! commandDirectory="$(type -p "sed")" &> /dev/null || [[ -z $commandDirectory ]]; then
  echo "Requires sed."
  exit;
fi

# awk test
if ! commandDirectory="$(type -p "awk")" &> /dev/null || [[ -z $commandDirectory ]]; then
  echo "Requires awk."
  exit;
fi

# sqlite3 test
if ! commandDirectory="$(type -p "sqlite3")" &> /dev/null || [[ -z $commandDirectory ]]; then
  echo "Requires sqlite3."
  exit;
fi

# argument test
if [ "$#" -ne 1 ]; then
  echo "Please provide the sqlite3 data file."
  echo "USAGE:\n"
  echo "port.bash /full/path/data.db"
	exit
fi

db=$1
a='*'
set -f

c="sqlite3 -header -csv $db \".tables\" > tbls.tlf"
eval $c

# put the tables on their own line
cat tbls.tlf | tr ' ' '\n' | sed "s/\ //g; /^$/d" > tbls2.tlf

# first part of JS
echo "function build_forms() {" > build_forms_js.txt
echo "	var frm;" >> build_forms_js.txt
echo "	var fld;" >> build_forms_js.txt
echo "" >> build_forms_js.txt

while IFS="" read -r p || [ -n "$p" ]
do
  #printf '%s\n' "$p"

	# csv files
	c="sqlite3 -header -csv $db \"select $a from $p;\" > $p.csv"
	eval $c

	# schema
	c="sqlite3 -header $db \".schema $p\" > $p.schema"
	eval $c

	# joins lines, splits at ;
	cat  $p.schema | tr '\n' ',' | awk -F ';' '{for (i=1;i<=NF;i++)print $i}' > $p.schema_split

	# ditches index statements
	# replaces "'s with ''s
	cat $p.schema_split | grep -i 'create table' | tr '"' "'" > $p.create_table


	# extract the fields
	# weird ~'s due to matching first ( and last ).
#	cat $p.schema_lowercase | tr '()' '~~' | sed 's/~/~~/' | rev | sed 's/~/~~~/' | rev | sed 's/.*~~\(.*\)~~~.*/\1/' > $p.field_string
	cat $p.create_table | sed 's/(/~~/' | rev | sed 's/)/~~~/' | rev | sed 's/.*~~\(.*\)~~~.*/\1/' > $p.field_string


	# put each field on its own line
	cat $p.field_string | awk -F, '{for (i=1;i<=NF;i++)print $i}' > $p.long_fields

	# trim whitespace
	cat $p.long_fields | awk '{$1=$1};1' > $p.fields


	# table/form JS
	echo "	frm = document.getFormNamed('$p');" >> build_forms_js.txt
	echo "	if ( frm ) {" >> build_forms_js.txt
	echo "		console.log('form $p already exists');" >> build_forms_js.txt
	echo "	} else {" >> build_forms_js.txt
	echo "		frm = document.createNewFormNamed('$p');" >> build_forms_js.txt

	# build field JS
	# \047 '
	# \011 TAB
	awk -F '\\s' '
	BEGIN {  } 
	{ 
		# build data type JS
		print "\011\011// "$0;

		lc = tolower($0);

		if ( lc ~ /real/ || lc ~ /double/ || lc ~ /float/ || lc ~ /floating\\ point/ || lc ~ /numeric/ || lc ~ /decimal/ ) {
			print "\011\011fld = frm.addNewFieldNamedWithType(\047" $1 "\047, \047number\047);";
		}
		else if ( lc ~ /char/ || lc ~ /text/ || lc ~ /var/ || lc ~ /native/ || lc ~ /clob/ ) { 
			print "\011\011fld = frm.addNewFieldNamedWithType(\047" $1 "\047, \047text\047);";
		}
		else if ( lc ~ /int/ || lc ~ /boolean/ ) {
			print "\011\011fld = frm.addNewFieldNamedWithType(\047" $1 "\047, \047number\047);";
		}
		else if ( lc ~ /blob/ ) {
			print "\011\011fld = frm.addNewFieldNamedWithType(\047" $1 "\047, \047number\047);";
		}
		else if ( lc ~ /date/ ) {
			print "\011\011fld = frm.addNewFieldNamedWithType(\047" $1 "\047, \047text\047);";
		}

		if ( lc ~ /default/ ) {
			if ( lc ~ /\047/ ) {
				match($0,/\047.*\047/);
				print "\011\011fld.defaultValue = " substr($0,RSTART,RLENGTH) ";";
			} else {
				print "\011\011fld.defaultValue = " $(NF) ";";
			}
		}

		if ( ( lc ~ /primary/ && !(lc ~ /primary \\)/) ) || lc ~ /not\\ null/ ) {
			#print "\011\011fld.required = true;";
		}


	}
	END {  } 

	' $p.fields > $p.commands

	# add field JS
	cat $p.commands >> build_forms_js.txt

	# end table/form JS
	echo "		document.saveAllChanges();" >> build_forms_js.txt
	echo "	}" >> build_forms_js.txt
	echo "" >> build_forms_js.txt

	# clean up - comment with # to debug
	rm $p.commands
	rm $p.field_string
	rm $p.long_fields
	rm $p.schema
	rm $p.schema_split
	rm $p.fields
	rm $p.create_table

done < tbls2.tlf

# last part of JS
echo "}" >> build_forms_js.txt
echo "" >> build_forms_js.txt
echo "build_forms();" >> build_forms_js.txt

# clean up
rm tbls.tlf
rm tbls2.tlf
