#!/bin/bash

clear;clear

PHP="$(whereis php | cut -d ':' -f 2 | cut -d ' ' -f 2)"

AWK="$(whereis awk | cut -d ':' -f 2 | cut -d ' ' -f 2)"

LS="$(whereis ls | cut -d ':' -f 2 | cut -d ' ' -f 2)"

RM="$(whereis rm | cut -d ':' -f 2 | cut -d ' ' -f 2)"

MKDIR="$(whereis mkdir | cut -d ':' -f 2 | cut -d ' ' -f 2)"

MYSQL="$(whereis mysql | cut -d ':' -f 2 | cut -d ' ' -f 2)"

ROOT_DIR="."

AWK_DIR="AWK"

PHP_DIR="PHP"

MYSQL_DIR="MYSQL"

CSV_DIR="CSV"

XML_DIR="XML"

SQL_DIR="SQL"

HTML_DIR="HTML"

LOG_DIR="$ROOT_DIR/LOG"

INPUT_DIR="$ROOT_DIR/IN"

GEN_DIR="$ROOT_DIR/OUT"

S_AWK_DIR="$ROOT_DIR/$AWK_DIR"

S_PHP_DIR="$ROOT_DIR/$PHP_DIR"

I_CSV_DIR="$INPUT_DIR/$CSV_DIR"

I_XML_DIR="$INPUT_DIR/$XML_DIR"

I_SQL_DIR="$INPUT_DIR/$SQL_DIR"

L_AWK_DIR="$LOG_DIR/$AWK_DIR"

L_PHP_DIR="$LOG_DIR/$PHP_DIR"

L_MYSQL_DIR="$LOG_DIR/$MYSQL_DIR"

G_SQL_DIR="$GEN_DIR/$SQL_DIR"

G_HTML_DIR="$GEN_DIR/$HTML_DIR"

USER_DEFINED_SQL_FILE="$I_SQL_DIR/user_defined.sql"

echo "Clean output and log dirs"

$RM -r $L_AWK_DIR  $L_PHP_DIR $L_MYSQL_DIR $G_SQL_DIR $G_HTML_DIR 2> /dev/null

$MKDIR -p $L_AWK_DIR  $L_PHP_DIR $L_MYSQL_DIR $G_SQL_DIR $G_HTML_DIR

echo "done"

AWK_EXT="awk"

PHP_EXT="php"

LOG_EXT="log"

SCRIPT_NAME="csv2sql"

SCRIPT_LOG="$SCRIPT_NAME.$LOG_EXT"

OUTPUT_FILE="01-${SCRIPT_NAME:0:3}.${SCRIPT_NAME:4:3}"

echo -n "Generating $OUTPUT_FILE"

$LS "$I_CSV_DIR/"*.csv | while read name; do
	table="${name:0:${#name}-4}";
	table="${table##*/}"
	"$S_AWK_DIR/$SCRIPT_NAME.${AWK_EXT}" "$name" -v TABLE_NAME="${table/ /_}" > "$G_SQL_DIR/$OUTPUT_FILE" 2> "$L_AWK_DIR/$SCRIPT_LOG"
done

if [ $? -eq 0 ]; then 
	echo ": done"

	SCRIPT_NAME="xml2sql"

	SCRIPT_LOG="$SCRIPT_NAME.$LOG_EXT"

	OUTPUT_FILE="02-${SCRIPT_NAME:0:3}.${SCRIPT_NAME:4:3}"

	echo -n "Generating $OUTPUT_FILE"

	"$S_AWK_DIR/$SCRIPT_NAME.${AWK_EXT}" $($LS $I_XML_DIR/*.xml) > "$G_SQL_DIR/$OUTPUT_FILE" 2> "$L_AWK_DIR/$SCRIPT_LOG"
else
	echo ": Failed to generate $OUTPIT_FILE" >&2
	exit 1
fi

if [ $? -eq 0 ]; then
	echo ": done"
	echo "Running SQL scripts"
	
	SCRIPT_LOG="$L_MYSQL_DIR/mysql.log"

	read -p "Database user name? " DB_USERNAME
	read -s -p "Database user password? " DB_PASSWORD
	DB_NAME="equipedi_ffe"

	echo ""

	$LS $G_SQL_DIR | while read sqlf; do
		echo "Running $sqlf" | tee -a  "$SCRIPT_LOG"
		
		$MYSQL --user="$DB_USERNAME" --password="$DB_PASSWORD" "$DB_NAME" < "$G_SQL_DIR/$sqlf" 2> "$SCRIPT_LOG"
	done
	
	echo "Running $USER_DEFINED_SQL_FILE" | tee -a  "$SCRIPT_LOG"
	
	$MYSQL --user="$DB_USERNAME" --password="$DB_PASSWORD" "$DB_NAME" < "$USER_DEFINED_SQL_FILE" 2> "$SCRIPT_LOG"
else
	echo ": Failed to generate $OUTPIT_FILE" >&2
	exit 2
fi

if [ $? -eq 0 ]; then 
	echo "done"
	SCRIPT_NAME="horaires"

	SCRIPT_LOG="$SCRIPT_NAME-error.$LOG_EXT"

	OUTPUT_FILE="horaire.html"

	echo "Generating $OUTPUT_FILE"

	$PHP $S_PHP_DIR/$SCRIPT_NAME.$PHP_EXT > $G_HTML_DIR/$OUTPUT_FILE 2>> $L_PHP_DIR/$SCRIPT_LOG;
else
	echo "Failed to run sql scripts" >&2
	exit 3
fi

echo "done"