#!/usr/bin/env bash
#
# This file generates to stdout sql for postgres
# foreign keys are printed for standard output 3
#
# Parameter:
# The mdb file name to process (ex: /home/nirgal/kod/friends/friends-migration/db.in/india/eft/ImpexBibitData.mdb )
#
# Environment variables:
# TMP: (ex: /home/nirgal/tmp/china/ImpExpBitBit )
#
# Author: Nirgal Vourgère <jvourger@greenpeace.org>
# Permission is granted to copy, modify, resell that script, no guaranties.

set -e
#set -x

if [ ${#@} != 1 ]; then
	echo You must give exactly one parameter: the MDB file name to process. >&2
	exit 1
fi
MDB="$1"
if [ ! -r "$MDB" ]; then
	echo "Can't read file $MDB">&2
	exit 1
fi

SCHEMA=`basename "$MDB" .mdb`

TMP="$TMP/$SCHEMA"
mkdir -p "$TMP"

echo "-- Fragment generated by $0 for $1" > "$TMP/import.sql"
echo "--" `date --rfc-3339=seconds` >> "$TMP/import.sql"
echo "-- Fragment generated by $0 for $1" >&3
echo "--" `date --rfc-3339=seconds` >&3

# Create schema
echo "Processing SCHEMA $SCHEMA">&2
echo "CREATE SCHEMA \"$SCHEMA\";" >> "$TMP/import.sql"
echo "SET search_path='$SCHEMA';" >> "$TMP/import.sql"
echo "SET search_path='$SCHEMA';" >&3


# Get the schema, without the foreign keys nor indexes
mdb-schema --no-drop-table --not-null --default-values --not-empty --indexes --no-relations "$MDB" postgres  | grep -v '^CREATE INDEX' | grep -v '^CREATE UNIQUE INDEX' >> "$TMP/import.sql" # key the primary keys
# Print indexes on standard output 3
mdb-schema --no-drop-table --no-not-null --no-default-values --no-not-empty --indexes --no-relations "$MDB" postgres | grep '^CREATE INDEX' >&3 || true
mdb-schema --no-drop-table --no-not-null --no-default-values --no-not-empty --indexes --no-relations "$MDB" postgres | grep '^CREATE UNIQUE INDEX' >&3 || true
# Print foreign keys on standard output 3
mdb-schema --no-drop-table --no-not-null --no-default-values --no-not-empty --no-indexes --relations "$MDB" postgres | grep REFERENCES >&3 || true


# Generate CSV files and matching COPY sql statements
mdb-tables -1 "$MDB" | while read table; do
	cleantable=`echo $table|sed -e "s/\///g"` # remove / from table names
	echo generating $cleantable.csv>&2
	# Add leading 0 to dates before 1000AD so that years always are 4 digits long
	mdb-export -X \\ -D "{postgresdate}%Y-%m-%d %H:%M:%S" "$MDB" "$table" | sed -re 's/\{postgresdate\}([0-9]{2})-/00\1-/g' | sed -re 's/\{postgresdate\}([0-9]{3})-/0\1-/g' | sed -re 's/\{postgresdate\}//g' > "$TMP/$cleantable.csv"
	squotedcleantable=`echo $cleantable|sed -e "s/'/''/g"`
	echo "COPY \"$table\" FROM '$TMP/$squotedcleantable.csv' WITH DELIMITER ',' CSV HEADER QUOTE AS '\"' ESCAPE AS E'\\\\' ;" >> "$TMP/import.sql"
done

# Update the sequences
echo "-- Generated sequence next val updates from default column values"  >> "$TMP/import.sql"
cat "$TMP/import.sql" | while read line
do
	if [[ "$line" =~ ^CREATE\ TABLE\ \"(.*)\" ]]
	then
		#echo $line
		current_table=${BASH_REMATCH[1]}
	fi
	if [[ $line =~ [\ \t]*\"([^\"]*)\".*[\ \t]*SERIAL ]]
	then
		#echo $line
		current_field=${BASH_REMATCH[1]}
		echo "SELECT pg_catalog.setval('\"${current_table}_${current_field}_seq\"', (SELECT MAX(\"$current_field\") FROM \"$current_table\"), true);" >> "$TMP/import.sql"
	fi
done

# Add OIDS for tables with a primary key over several columns
echo "-- Adding OIDS for tables with a primary key over several columns" >> "$TMP/import.sql"
for table in `grep "PRIMARY KEY .*," "$TMP/import.sql" | cut -d '"' -f 2`; do
	echo "ALTER TABLE \"$table\" SET WITH OIDS;" >> "$TMP/import.sql"
done

# Add OIDS for tables that have no primary key
echo "-- Adding OIDS for tables with no primary keys" >> "$TMP/import.sql"
mdb-tables -1 "$MDB" | while read table; do
	if ! grep -q "ALTER TABLE \"$table\" ADD CONSTRAINT .* PRIMARY KEY" "$TMP/import.sql"; then
		echo "ALTER TABLE \"$table\" SET WITH OIDS;" >> "$TMP/import.sql"
	fi
done

# print the SQL result
cat "$TMP/import.sql"