#!/bin/bash
#
# Konvertiert survex aven.3d Exporte (z.b. aus Spelix) zu therion-Centreline (.th) Daten
#
# Aufruf: `spelix2therion source.3d [surveylist]`   -> erzeugt source.th
#         Ist surveylist leer, werden die Daten mitteld "surveys create" importiert,
#         falls nicht, werden leere Surveys für die Liste erzeugt.
#         surveylist ist eine leerzeichengetrennte Liste.
#
# Benötigt: therion, sqlite und sed
#
# @author beni@hallinger.org
# @version 0.2
#

# SQLITE Version
#sqlite="sqlite"  # 2.x
sqlite="sqlite3"  # 3.x


# Aufrufparameter checks
if [[ -z "$1" || ! -r $1 ]]; then
	echo "$(basename $0) $(grep '^# @version' $0 | sed 's/^.\+@version\s*//')"
	echo "Aufruf: $0 src-file.3d '[surveyliste]'"
	echo "Bsp:    \"$0 1546_7.3d '1 2 95'\""
	exit 1
fi
if [[ -n "$2" ]]; then
	surveys=$2
fi


for neededProgramm in "therion" "$sqlite" "sed"; do
	which $neededProgramm >/dev/null 2>/dev/null
	if [[ $? -gt 0 ]]; then
		echo "FEHLER: Programm '$neededProgramm' nicht gefunden!"
		exit 1
	fi
done



# Konvertierung aven.3d -> DXF
#tgt=$(basename $1 .3d)
#echo "Konvertiere $1 zu DXF"
#cad3d -D "$1" "/tmp/${tgt}.dxf"

# Konvertierung dxf in svx
#cd /tmp/
#sed 's/\([0-9]\),/\1./g' "${tgt}.dxf" > "${tgt}.glideok.dxf"  # korrekte Formatierung der Gleitkommazahlen "n.n" statt "n,n"
#caveconverter "${tgt}.glideok.dxf" "${tgt}.svx" d s
#sed 's/\([0-9]\),/\1./g' "${tgt}.svx" > "${tgt}.glideok.svx"  # korrekte Formatierung der Gleitkommazahlen "n.n" statt "n,n"
#cd -


#-----------------------------------------------------------
# Die Konvertierung wird in folgenden Schritten vorgenommen:
# 1. Import der 3d-Datei in Therion
# 2. Exportieren der Daten als therion SQL-Datenbank
# 3. Auswerten (Konvertieren zu therion .th) mittels sqlite
#

#
# Konvertierung aven.3d -> therion SQL
#
tgt=$(basename $1 .3d)
echo -n "Konvertiere $1 zu therion SQL... "

#Erzeugen eines temporären therion .th und .thc
if [[ -z $surveys ]]; then
	echo "import $1 -surveys create"                 >  "${tgt}.TMP.therion.import3d.th"
else
	echo "import $1 -surveys use"                    >  "${tgt}.TMP.therion.import3d.th"
	for s in $surveys; do
		echo "survey $s" >> "${tgt}.TMP.therion.import3d.th"
		echo "endsurvey" >> "${tgt}.TMP.therion.import3d.th"
	done
fi
echo "source ${tgt}.TMP.therion.import3d.th"     >  "${tgt}.TMP.therion.import3d.thc"
echo "export database -o ${tgt}.TMP.therion.sql" >> "${tgt}.TMP.therion.import3d.thc"
#echo "export model    -o ${tgt}.TMP.therion.3d"  >> "${tgt}.TMP.therion.import3d.thc"

# Therion export der SQL Datenstruktur (Dokumentiert im therion-Book, S. 60ff: Kap. "What we get? / SQL export")
# Jede Ebene im Survexfile erzeugt ein eigenes Survey, diese sind miteinander verknüpft.
therion "${tgt}.TMP.therion.import3d.thc" > "${tgt}.TMP.therion.exportSQL.program.log" 2>"${tgt}.TMP.therion.exportSQL.program.log"
rc=$?
mv therion.log "${tgt}.TMP.therion.exportSQL.compile.log"
if [[ $rc -gt 0 ]]; then 
	echo "Fehler! Bitte logs und temporäre Daten prüfen."
	ls -alh *.TMP.therion*
	exit 1
else
	echo "OK"
fi


#
# Setup sqlite Datenbank
# (SQL statemets wrapped in Transaktion für bessere Performance)
#
echo -n "Erzeuge SQLite Datenbank... "

# SQL-Statemets in Transaktion einbetten
sed -i '1s/^/BEGIN TRANSACTION;\n/' "${tgt}.TMP.therion.sql"
sed -i '$s/$/\nEND TRANSACTION;/'   "${tgt}.TMP.therion.sql"

# SQLite import
$sqlite "${tgt}.TMP.sqlite.db" <"${tgt}.TMP.therion.sql" > "${tgt}.TMP.sqlite.import.log" 2>> "${tgt}.TMP.sqlite.import.log"
rc=$?
if [[ $rc -gt 0 ]]; then 
	echo "Fehler! Bitte logs und temporäre Daten prüfen."
	ls -alh *.TMP.sqlite* *.log
	exit 1
else
	echo "OK"
fi


#
# Datenexport aus sqlite
#
echo "prepare SQL export querys" > "${tgt}.TMP.convert.th.log"

# Query liest alle Surveys mit Inhalt aus
read -r -d '' sql_allSurveys <<EOSQL
SELECT SURVEY.ID, SURVEY.FULL_NAME
FROM SURVEY, CENTRELINE
WHERE SURVEY.ID=CENTRELINE.SURVEY_ID AND CENTRELINE.LENGTH>0
EOSQL

# Query liest alle Centerlines einer survey aus
# (WHERE clause wird später ergänzt)
#read -r -d '' sql_CLofSurvey <<EOSQL
#SELECT SURVEY.FULL_NAME as "survey.name", CENTRELINE.ID as "centreline.id"
#FROM SURVEY
#JOIN CENTRELINE ON SURVEY.ID=CENTRELINE.SURVEY_ID
#EOSQL
read -r -d '' sql_CLofSurvey <<EOSQL
SELECT SURVEY.ID, CENTRELINE.ID
FROM SURVEY, CENTRELINE
WHERE SURVEY.ID=CENTRELINE.SURVEY_ID
EOSQL

# Query liest alle Messzüge einer Survey-Centerline aus
# (WHERE clause wird später ergänzt)
#read -r -d '' sql_allShots <<EOSQL
#SELECT srvy.id as "survey.id", srvy.FULL_NAME as "survey.name", stfrom.NAME as "from", stto.NAME as "to", SHOT.ADJ_LENGTH, SHOT.ADJ_BEARING, SHOT.ADJ_GRADIENT, SHOT.LENGTH, SHOT.BEARING, SHOT.GRADIENT, SHOT.FROM_ID, SHOT.TO_ID, cline.id as "centreline.id"
#FROM SHOT
#JOIN CENTRELINE cline  ON SHOT.CENTRELINE_ID=cline.ID
#JOIN SURVEY     srvy   ON cline.SURVEY_ID=srvy.ID
#JOIN STATION    stfrom ON SHOT.FROM_ID=stfrom.ID
#JOIN STATION    stto   ON SHOT.TO_ID=stto.ID
#EOSQL
read -r -d '' sql_allShots <<EOSQL
SELECT stfrom.NAME as "from", stto.NAME as "to", SHOT.ADJ_LENGTH, SHOT.ADJ_BEARING, SHOT.ADJ_GRADIENT, SHOT.LENGTH, SHOT.BEARING, SHOT.GRADIENT, SHOT.FROM_ID, SHOT.TO_ID
FROM SHOT, STATION stfrom, STATION stto
WHERE SHOT.FROM_ID=stfrom.ID AND SHOT.TO_ID=stto.ID
EOSQL


# Konvertierung in therion-centreline mittels therion-Template:
# Jedes erkannte survey durchgehen und eigene .th-Datei mit Template anlegen
echo "Konvertiere..."
echo "Executing conversion" >> "${tgt}.TMP.convert.th.log"
echo -e "\nDBG SQL Query (sql_allSurveys): '$sql_allSurveys'" >> "${tgt}.TMP.convert.th.log"
for sql_res in $($sqlite "${tgt}.TMP.sqlite.db" "$sql_allSurveys"); do
	survey_id=$(echo -n "$sql_res" | cut -d '|' -f 1)
	survey_name=$(echo -n "$sql_res" | cut -d '|' -f 2)
	echo "DBG: SQL RES (survey_id=$survey_id; survey_name=$survey_name): '$sql_res'" >> "${tgt}.TMP.convert.th.log"
	if [[ -z "$survey_name" ]]; then
		#echo "Achtung: Survey ohne Namen gefunden!"
		survey_name="unknown"
	fi

	tgt_dir="./aven2therion/"; [[ -d "$tgt_dir" ]] || mkdir "$tgt_dir"
	tgt_name="${survey_name}.th"
	tgt_th="$tgt_dir/$tgt_name"

	echo "  survey '$survey_name' ($tgt_th)"



	# HEADER
	cat << EOF > "$tgt_th"
encoding  utf-8
survey $survey_name

EOF
	sql_CLofSurvey_THIS="$sql_CLofSurvey AND SURVEY.ID='$survey_id'"
	echo -e "\nDBG SQL Query (sql_CLofSurvey_THIS): '$sql_CLofSurvey_THIS'" >> "${tgt}.TMP.convert.th.log"
	for sqlres_cls in $($sqlite "${tgt}.TMP.sqlite.db" "$sql_CLofSurvey_THIS"); do
	        survey_cl_id=$(echo -n "$sqlres_cls" | cut -d '|' -f 2)
		echo "DBG: SQL RES (survey_cl_id=$survey_cl_id): '$sqlres_cls'" >> "${tgt}.TMP.convert.th.log"
	
		# Centerline header
		now=$(date "+%Y-%m-%d %H:%M:%S")
		cat >>$tgt_th <<EO_CLHDR
centreline -id cl_${survey_name}_${survey_cl_id}
  # Centreline recovered at $now from survex aven 3d file ($1).
  # Loop corrections are already applied!
  copyright "2015" "Höhlenverein Hallstatt-Obertraun"

  # Vermessungsteam
  #date YYYY.MM.DD
  #team "Person Eins"
  #team "Person Zwei"

  # Erkundungsteam (optional)
  #explo-date YYYY.MM.DD
  #explo-team "Person Eins"
  #explo-team "Person Zwei"


  # Messdaten dieses Zugs
  data normal from to tape compass clino
EO_CLHDR

		# Centerline Daten
		sql_allShots_THIS="$sql_allShots AND SHOT.CENTRELINE_ID='$survey_cl_id'"
		echo -e "\nDBG SQL Query (sql_allShots_THIS): '$sql_allShots_THIS'" >> "${tgt}.TMP.convert.th.log"
	        for sqlres_cl_shots in $($sqlite "${tgt}.TMP.sqlite.db" "$sql_allShots_THIS"); do
			from=$(echo -n "$sqlres_cl_shots" | cut -d '|' -f 1)
			to=$(echo -n "$sqlres_cl_shots" | cut -d '|' -f 2)
			tape=$(echo -n "$sqlres_cl_shots" | cut -d '|' -f 3)
			compass=$(echo -n "$sqlres_cl_shots" | cut -d '|' -f 4)
			clino=$(echo -n "$sqlres_cl_shots" | cut -d '|' -f 5)
			echo "DBG: SQL RES (from=$from; to=$to; tape=$tape; compass=$compass; clino=$clino): '$sqlres_cl_shots'" >> "${tgt}.TMP.convert.th.log"

			# TODO: support shot flags, wenn in Datenbank gesetzt (vor allem duplicate/splay etc, aber auch fix)

			#echo -e "    $from\t$to\t$tape\t$compass\t$clino" >>$tgt_th
			printf "  %8s %8s %8s %8s %8s\n" $from $to $tape $compass $clino >>$tgt_th

		done


		# Centerline footer
		echo "" >> ${tgt_th}
	        echo "endcentreline" >> ${tgt_th}

	done


	# Survey footer
	echo "" >> ${tgt_th}
	echo "" >> ${tgt_th}
	echo "endsurvey" >> ${tgt_th}

done



# cleanup
rm -r *.TMP.*