#!/bin/bash

# Script used to initialise the database for a new survey. It should be run
# with three arguments:
#
# ./create_survey.sh PROJECT_ID PROJECT_NAME EPSG_CODE
#
# In which:
# * PROJECT_ID is the (short and unique) ID by which the project will
# be known to the database.
# * PROJECT_NAME is a more descriptive name for human consumption.
# * EPSG_CODE is the EPSG code identifying the CRS for the grid data in the
# navigation files, e.g., 23031.
#
# In addition to this, certain other parameters may be controlled via
# environment variables:
#
# * TEMPLATE_SQL is the template code that will be used for the project schema.
#   Needless to say, the schema should be compatible with the rest of the codebase.
#   Defaults to the built-in schema found under etc/db/schema-template.sql
#
# * PGDATABASE is the name of the database in which to create the new survey. The
#   database itself is expected to have PostGIS version 3 or greater installed.
#   There is an SQL script in etc/db/ to create a new database if necessary.
#   Defaults to ‘dougal’.
#
# * CREATE_SURVEY_SQL is a variable that contains additional SQL statements to be
#   run after survey creation. Intended primarily to define the correct transformation
#   parameters for PostGIS until a better solution is found. Not defined by default.
#

PREFIX=${PREFIX:-"$(dirname "$0")/.."}

PROJECT_ID="$1"
PROJECT_NAME="$2"
EPSG_CODE="$3"

TEMPLATE_SQL="$PREFIX/etc/db/schema-template.sql"
PGUSER=${PGUSER:-postgres}
PGHOST=${PGHOST:-localhost}
PGDATABASE=${PGDATABASE:-dougal}
LAST_SURVEY_ID=$(psql -U $PGUSER -h $PGHOST -Atc '\dn survey_[0-9]+' "$PGDATABASE" |sed -rn 's/^survey_([0-9]+).*$/\1/p' |sort -n |tail -n 1)
NEXT_SURVEY_ID=$((LAST_SURVEY_ID+1))

SCHEMA_NAME="${SCHEMA_NAME:-survey_$NEXT_SURVEY_ID}"

[[ $# -ne 3 ]] && {
	echo "Wrong number of arguments"
	echo "Usage: $0 Project_ID Project_name EPSG_code"
	exit 1
}

echo "Creating new schema $SCHEMA_NAME with template $TEMPLATE_SQL"
sed -r "s/_SURVEY__TEMPLATE_/$SCHEMA_NAME/g;s/_EPSG__CODE_/$EPSG_CODE/g" <"$TEMPLATE_SQL" | psql -U $PGUSER -h $PGHOST $PGDATABASE

echo "Adding details to projects table"
echo "ID: $PROJECT_ID, Name: $PROJECT_NAME, Schema: $SCHEMA_NAME, EPSG: $EPSG_CODE"

psql -U $PGUSER -h $PGHOST \
	--variable pid="$PROJECT_ID" \
	--variable name="$PROJECT_NAME" \
	--variable schema="$SCHEMA_NAME" \
	$PGDATABASE <<< "INSERT INTO public.projects (pid, name, schema) VALUES (LOWER(:'pid'), :'name', :'schema');"


if [[ -n "$CREATE_SURVEY_SQL" ]]; then
# FIXME - Terrible way to do this, but we need to get the right transformations into Postgis somehow
# Example ( retrieved from https://epsg.io/23031-1613.sql )
# INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 23031, 'EPSG', 23031, '+proj=utm +zone=31 +ellps=intl +towgs84=-90.365,-101.13,-123.384,0.333,0.077,0.894,1.994 +units=m +no_defs ', 'PROJCS["ED50 / UTM zone 31N",GEOGCS["ED50",DATUM["European_Datum_1950",SPHEROID["International 1924",6378388,297,AUTHORITY["EPSG","7022"]],TOWGS84[-90.365,-101.13,-123.384,0.333,0.077,0.894,1.994],AUTHORITY["EPSG","6230"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4230"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",3],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","23031"]]');

	psql -U $PGUSER -h $PGHOST $PGDATABASE <<< "$CREATE_SURVEY_SQL"

fi

echo "Done"