@ECHO OFF

SETLOCAL

ECHO Initializing environment
REM overwrite log
CALL:SETTINGS > log.log 2>&1

ECHO Parsing shape files
REM append to log
CALL:PARSE_SHAPE_FILES >> log.log 2>&1

ECHO Dropping database
REM append to log
CALL:DROP_DB >> log.log 2>&1

ECHO Initializing database
REM append to log
CALL:SETUP_DB >> log.log 2>&1


REM ||                              SET ENVIRONMENT                                   ||
REM ------------------------------------------------------------------------------------
:SETTINGS

REM Only these two should be changed, the rest of the settings should remain intact
SET pguser=postgres
SET pgpassword=postgres


REM Path to where shapefile loader
SET SHP2PGSQL=C:\Program Files\PostgreSQL\9.5\bin\shp2pgsql.exe

REM Path to interactive PSQL terminal
SET PSQL=C:\Program Files\PostgreSQL\9.5\bin\psql.exe

REM Do not alter these. Points to the path for shapefile import folder
REM and sql file folder where Import_shapefiles.bat is located.
REM Folder path for sql command files

SET SHP_FILE_PATH=%~dp0\SHP_FILES
SET TEMP_SQL_FILE_PATH=%~dp0\TEMP_SQL_FILES
SET LOG_PATH=%~dp0\SQL_LOGS

REM Postgresql: server host name, server port number, server username, server password
REM database name and schema, SRID for shape files, name of the geometry column

SET pghost=localhost
SET pgschema=public

REM DB for our project
SET pgdb=nnaat
SET pgport=5432
SET pgsrid=3044
SET pggeom=geom
SET pgencoding="LATIN1"

REM Create directories if not existant
IF NOT EXIST "%TEMP_SQL_FILE_PATH%" MKDIR %TEMP_SQL_FILE_PATH%
IF NOT EXIST "%LOG_PATH%" MKDIR %LOG_PATH%
IF NOT EXIST "%SHP_FILE_PATH%" MKDIR %SHP_FILE_PATH%


REM "Remove previously generated SQL files
FOR %%f in (%TEMP_SQL_FILE_PATH%\*.sql) do del %%f

REM Remove any previous log files for sql, keep 'em after this run to be able to read after run
FOR %%f in (%LOG_PATH%\*.log) do del %%f

EXIT /B



REM ||                          PARSE SHAPEFILES INTO SQL                             ||
REM ------------------------------------------------------------------------------------
:PARSE_SHAPE_FILES

REM Use shapefiles to create sql statements using shp2pgsql
FOR %%f in (%SHP_FILE_PATH%\*.shp) do "%SHP2PGSQL%" -s %pgsrid% -d -g %pggeom% -D -i -I -W %pgencoding% %%f %pgschema%.%%~nf > %TEMP_SQL_FILE_PATH%\%%~nf.sql

EXIT /B



REM ||                               DROP DATABASE                                    ||
REM ------------------------------------------------------------------------------------
:DROP_DB

REM Cleanup previous versions of the database
"%PSQL%" -h %pghost% -p %pgport% -U %pguser% -d postgres -a -f %~dp0cleanup-db.sql -v dbname=%pgdb%

EXIT /B



REM ||                              CREATE DATABASE                                   ||
REM ------------------------------------------------------------------------------------
:SETUP_DB

REM setup database
"%PSQL%" -h %pghost% -p %pgport% -U %pguser% -d postgres -a -f %~dp0setup-db.sql -v dbname=%pgdb% -v dbowner=%pguser%

EXIT /B



ENDLOCAL