@echo off

set yes=%1

call :check_gsqlcmd
call :check_gsqlcmd5
call :get_application_label
call :check_sources
call :check_create_database

echo.
echo This tool will install %label% using the following credentials:

call :check_connection

call :check_savetodb_framework7
call :check_savetodb_framework
call :check_savetodb_developer_framework
call :check_savetodb_administrator_framework
call :check_change_tracking_framework
call :check_database_help_framework

call :confirm_install_operations
call :execute_install_operations

pause

goto :EOF


:confirm_install_operations

echo.
echo The tool will execute the following operations:
echo.
if .%create_database%.==.1. echo Create  %database_filename%

call :echo_file savetodb-framework-install-en.sql               Install 1 %savetodb_framework_installed%
call :echo_file savetodb-framework-install.sql                  Install 1 %savetodb_framework_installed%
call :echo_file savetodb-developer-framework-install.sql        Install 1 %savetodb_developer_framework_installed%
call :echo_file savetodb-administrator-framework-install.sql    Install 1 %savetodb_administrator_framework_installed%
call :echo_file change-tracking-framework-install.sql           Install 1 %change_tracking_framework_installed%
call :echo_file database-help-framework-install.sql             Install 1 %database_help_framework_installed%
call :echo_file application-install.sql                         Install 1 0
call :echo_file application-install-translation.sql             Install 1 0
call :echo_file application-install-help.sql                    Install 1 0
call :echo_file master-install.sql                              Install 1 0
call :echo_file application-grants.sql                          Install 1 0
call :echo_file application-install-sample-data.sql             Install 1 0
call :echo_file application-install-change-tracking.sql         Install 1 0
call :echo_file application-install-database-help.sql           Install 1 0

echo.

if .%yes%.==./Y. (
    echo Press Y to continue INSTALLING %label% or N to cancel [Y,N]?Y
    goto :EOF
)

choice /C yn /M "Press Y to continue INSTALLING %label% or N to cancel"

if errorlevel 2 (
    echo.
    echo Operation cancelled.
    exit
)

goto :EOF


:execute_install_operations

call :create_database
call :execute_file  savetodb-framework-install-en.sql               0 1 %savetodb_framework_installed%
call :execute_file  savetodb-framework-install.sql                  0 1 %savetodb_framework_installed%
call :execute_file  savetodb-developer-framework-install.sql        1 1 %savetodb_developer_framework_installed%
call :execute_file  savetodb-administrator-framework-install.sql    1 1 %savetodb_administrator_framework_installed%
call :execute_file  change-tracking-framework-install.sql           1 1 %change_tracking_framework_installed%
call :execute_file  database-help-framework-install.sql             1 1 %database_help_framework_installed%
call :execute_file  application-install.sql                         0 1 0
call :execute_file  application-install-translation.sql             1 1 0 /notransaction
call :execute_file  application-install-help.sql                    1 %database_help_framework_installed% 0 /notransaction
call :execute_file  master-install.sql                              1 1 0
call :execute_file  application-grants.sql                          0 0 %last_executed%
call :execute_file  application-install-sample-data.sql             0 1 0 /notransaction
call :execute_file  application-install-change-tracking.sql         1 %change_tracking_framework_installed% 0
call :execute_file  application-install-database-help.sql           1 %database_help_framework_installed% 0

echo.

goto :EOF



:execute_file

set last_executed=1

if .%1.==.. goto :EOF

set file=?

if exist sources\%1 set file=sources\%1
if exist %1         set file=%1

if .%file%.==.?. goto :EOF

if .%3.==.%4. goto :EOF

if .%2.==.1. (
    echo.

    if .%yes%.==./Y. (
        echo Press Y to execute %1 or N to skip [Y,N]?Y
    ) else (
        choice /C yn /M "Press Y to execute %1 or N to skip"
        if errorlevel 2 (
            set last_executed=0
            goto :EOF
        )
    )
)

gsqlcmd execute setup %file% /inputcodepage=65001 %5 %6

if errorlevel 1 (
    echo.
    echo Fatal error of executing %file%
    pause
    exit
)

if .%1.==.savetodb-framework-install-en.sql.            set savetodb_framework_installed=1
if .%1.==.savetodb-framework-install.sql.               set savetodb_framework_installed=1
if .%1.==.savetodb-developer-framework-install.sql.     set savetodb_developer_framework_installed=1
if .%1.==.savetodb-administrator-framework-install.sql. set savetodb_administrator_framework_installed=1
if .%1.==.change-tracking-framework-install.sql.        set change_tracking_framework_installed=1
if .%1.==.database-help-framework-install.sql.          set database_help_framework_installed=1

goto :EOF


:echo_file

if .%1.==.. goto :EOF

set file=?

if exist sources\%1 set file=sources\%1
if exist %1         set file=%1

if .%file%.==.?. goto :EOF

if .%3.==.%4. (
    echo Skip    %file%
) else (
    echo %~2 %file%
)

goto :EOF


:check_gsqlcmd

gsqlcmd connections>nul 2>nul

if errorlevel 1 (
    echo.
    echo gsqlcmd not found. Download it here: https://www.gsqlcmd.com
    pause
    exit
)

goto :EOF


:check_gsqlcmd5

gsqlcmd version 5.0>nul 2>nul

if errorlevel 1 (
    echo.
    echo gsqlcmd 5.0+ required.
    echo.
    echo Download the latest version from: https://www.gsqlcmd.com
    pause
    exit
)

goto :EOF


:get_application_label

set label=?

if exist label.txt @for /F "delims==" %%i in (label.txt) do set label=%%i

if not "%label%"=="?" goto :EOF

if exist sources\application-install.sql (
    for %%i in (.) do set label=%%~nxi
) else if exist sources\application-install.sql (
    for %%i in (.) do set label=%%~nxi
) else (
    if exist sources\savetodb-framework-install-en.sql              set label=SaveToDB Framework 7
    if exist sources\savetodb-framework-install.sql                 set label=SaveToDB Framework
    if exist sources\savetodb-developer-framework-install.sql       set label=SaveToDB Developer Framework
    if exist sources\savetodb-administrator-framework-install.sql   set label=SaveToDB Administrator Framework
    if exist sources\change-tracking-framework-install.sql          set label=Change Tracking Framework
    if exist sources\database-help-framework-install.sql            set label=Database Help Framework

    if exist savetodb-framework-install-en.sql                      set label=SaveToDB Framework 7
    if exist savetodb-framework-install.sql                         set label=SaveToDB Framework
    if exist savetodb-developer-framework-install.sql               set label=SaveToDB Developer Framework
    if exist savetodb-administrator-framework-install.sql           set label=SaveToDB Administrator Framework
    if exist change-tracking-framework-install.sql                  set label=Change Tracking Framework
    if exist database-help-framework-install.sql                    set label=Database Help Framework

    if not "%label%"=="?" goto :EOF

    for %%i in (.) do set label=%%~nxi
)

goto :EOF


:check_connection

echo.
gsqlcmd show setup
echo.

if .%yes%.==./Y. (
    echo Press Y to continue or N to change the credentials [Y,N]?Y
) else (
    choice /C yn /M "Press Y to continue or N to change the credentials"

    if errorlevel 2 (
        gsqlcmd localconfig
        exit
    )
)

goto :EOF


:check_sources

if exist application-install.sql                        goto :EOF
if exist savetodb-framework-install-en.sql              goto :EOF
if exist savetodb-framework-install.sql                 goto :EOF
if exist savetodb-developer-framework-install.sql       goto :EOF
if exist savetodb-administrator-framework-install.sql   goto :EOF
if exist change-tracking-framework-install.sql          goto :EOF
if exist database-help-framework-install.sql            goto :EOF

if not exist sources (
    echo.
    echo The sources subfolder does not exist.
    pause
    exit
)

if exist sources\application-install.sql                        goto :EOF
if exist sources\savetodb-framework-install-en.sql              goto :EOF
if exist sources\savetodb-framework-install.sql                 goto :EOF
if exist sources\savetodb-developer-framework-install.sql       goto :EOF
if exist sources\savetodb-administrator-framework-install.sql   goto :EOF
if exist sources\change-tracking-framework-install.sql          goto :EOF
if exist sources\database-help-framework-install.sql            goto :EOF

echo.
echo Cannot find any file to execute in the sources subfolder.
pause
exit


:check_create_database

set create_database=0
set database_filename=?

for /F %%i in ('gsqlcmd show setup servertype') do set servertype=%%i

if .%servertype%.==.SqlServerCE. (
    for /F %%i in ('gsqlcmd show setup filename') do set database_filename=%%i
)
if .%servertype%.==.SQLite. (
    for /F %%i in ('gsqlcmd show setup filename') do set database_filename=%%i
)

if .%database_filename%.==.?. goto :EOF

if exist %database_filename% goto :EOF

set create_database=1

goto :EOF


:create_database

if not .%create_database%.==.1. goto :EOF

if .%database_filename%.==.?.   goto :EOF

if exist %database_filename%    goto :EOF

gsqlcmd setup CreateDatabase

goto :EOF


:check_savetodb_framework7

set savetodb_framework_required=0
set savetodb_framework_installed=0

if exist sources\savetodb-framework-install-en.sql  set savetodb_framework_required=1
if exist savetodb-framework-install-en.sql          set savetodb_framework_required=1

if %savetodb_framework_required%==0 goto :EOF

if .%create_database%.==.1. goto :EOF

call :get_savetodb_framework7_check_cmd

for /F %%i in ('%cmd%') do set tablecount=%%i

if .%tablecount%.==.Execution.  goto GSQLCMD_ERROR
if .%tablecount%.==.Unknown.    goto GSQLCMD_ERROR

if not .%tablecount%.==.6.      goto :EOF

set savetodb_framework_installed=1

goto :EOF


:get_savetodb_framework7_check_cmd

for /F %%i in ('gsqlcmd show setup servertype') do set servertype=%%i

set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('dbo01') AND t.TABLE_NAME IN ('ColumnTranslation', 'ObjectTranslation', 'TableFormats', 'EventHandlers', 'ParameterValues', 'QueryList')" /noheaders

if .%servertype%.==.SqlServer. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('dbo01') AND t.TABLE_NAME IN ('ColumnTranslation', 'ObjectTranslation', 'TableFormats', 'EventHandlers', 'ParameterValues', 'QueryList')" /noheaders
    goto :EOF
)
if .%servertype%.==.Oracle. (
    set cmd=gsqlcmd setup "SELECT CAST(COUNT(*) AS CHAR) AS tablecount FROM SYS.ALL_OBJECTS t WHERE t.OWNER = 'SAVETODB_DEV' AND t.OBJECT_NAME IN ('COLUMN_TRANSLATION', 'OBJECT_TRANSLATION', 'EVENT_HANDLERS', 'PARAMETER_VALUES', 'TABLE_FORMATS', 'QUERY_LIST')" /noheaders
    goto :EOF
)
if .%servertype%.==.DB2. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM SYSCAT.TABLES t WHERE t.TABSCHEMA = 'SAVETODB_DEV' AND t.TABNAME IN ('COLUMN_TRANSLATION', 'OBJECT_TRANSLATION', 'EVENT_HANDLERS', 'PARAMETER_VALUES', 'TABLE_FORMATS', 'QUERY_LIST')" /noheaders
    goto :EOF
)
if .%servertype%.==.MySql. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('savetodb_dev') AND t.TABLE_NAME IN ('column_translation', 'object_translation', 'table_formats', 'event_handlers', 'parameter_values', 'query_list')" /noheaders
    goto :EOF
)
if .%servertype%.==.SqlServerCE. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME IN ('ColumnTranslation', 'ObjectTranslation', 'TableFormats', 'EventHandlers', 'ParameterValues', 'QueryList')" /noheaders
    goto :EOF
)
if .%servertype%.==.NuoDB. (
    set cmd=gsqlcmd setup "SELECT CAST(COUNT(*) AS INTEGER) AS tablecount FROM SYSTEM.TABLES t WHERE t.SCHEMA = 'SAVETODB_DEV' AND t.TABLENAME IN ('COLUMN_TRANSLATION', 'OBJECT_TRANSLATION', 'EVENT_HANDLERS', 'PARAMETER_VALUES', 'TABLE_FORMATS', 'QUERY_LIST')" /noheaders
    goto :EOF
)
if .%servertype%.==.PostgreSQL. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM information_schema.tables t WHERE t.table_schema IN ('savetodb_dev') AND t.table_name IN ('column_translation', 'object_translation', 'table_formats', 'event_handlers', 'parameter_values', 'query_list')" /noheaders
    goto :EOF
)
if .%servertype%.==.SQLite. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM sqlite_master t WHERE t.name IN ('ColumnTranslation', 'ObjectTranslation', 'TableFormats', 'EventHandlers', 'ParameterValues', 'QueryList')" /noheaders
    goto :EOF
)

echo Unknown server type: %servertype%
exit


:check_savetodb_framework

set savetodb_framework_required=0
set savetodb_framework_installed=0

if exist sources\savetodb-framework-install.sql set savetodb_framework_required=1
if exist savetodb-framework-install.sql         set savetodb_framework_required=1

if %savetodb_framework_required%==0 goto :EOF

if .%create_database%.==.1. goto :EOF

call :get_savetodb_framework_check_cmd

for /F %%i in ('%cmd%') do set tablecount=%%i

if .%tablecount%.==.Execution.  goto GSQLCMD_ERROR
if .%tablecount%.==.Unknown.    goto GSQLCMD_ERROR

if not .%tablecount%.==.4.      goto :EOF

set savetodb_framework_installed=1

goto :EOF


:get_savetodb_framework_check_cmd

for /F %%i in ('gsqlcmd show setup servertype') do set servertype=%%i

set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('xls') AND t.TABLE_NAME IN ('objects', 'handlers', 'translations', 'formats')" /noheaders

if .%servertype%.==.SqlServer. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('xls') AND t.TABLE_NAME IN ('objects', 'handlers', 'translations', 'formats')" /noheaders
    goto :EOF
)
if .%servertype%.==.Oracle. (
    set cmd=gsqlcmd setup "SELECT CAST(COUNT(*) AS CHAR) AS tablecount FROM SYS.ALL_OBJECTS t WHERE t.OWNER = 'XLS' AND t.OBJECT_NAME IN ('OBJECTS', 'HANDLERS', 'FORMATS', 'TRANSLATIONS')" /noheaders
    goto :EOF
)
if .%servertype%.==.DB2. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM SYSCAT.TABLES t WHERE t.TABSCHEMA = 'XLS' AND t.TABNAME IN ('OBJECTS', 'HANDLERS', 'FORMATS', 'TRANSLATIONS')" /noheaders
    goto :EOF
)
if .%servertype%.==.MySql. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('xls') AND t.TABLE_NAME IN ('objects', 'handlers', 'translations', 'formats')" /noheaders
    goto :EOF
)
if .%servertype%.==.SqlServerCE. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME IN ('objects', 'handlers', 'translations', 'formats')" /noheaders
    goto :EOF
)
if .%servertype%.==.NuoDB. (
    set cmd=gsqlcmd setup "SELECT CAST(COUNT(*) AS INTEGER) AS tablecount FROM SYSTEM.TABLES t WHERE t.SCHEMA = 'XLS' AND t.TABLENAME IN ('OBJECTS', 'HANDLERS', 'FORMATS', 'TRANSLATIONS')" /noheaders
    goto :EOF
)
if .%servertype%.==.PostgreSQL. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM information_schema.tables t WHERE t.table_schema IN ('xls') AND t.table_name IN ('objects', 'handlers', 'translations', 'formats')" /noheaders
    goto :EOF
)
if .%servertype%.==.SQLite. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM sqlite_master t WHERE t.name IN ('objects', 'handlers', 'translations', 'formats')" /noheaders
    goto :EOF
)
if .%servertype%.==.Snowflake. (
    set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('XLS') AND t.TABLE_NAME IN ('OBJECTS', 'HANDLERS', 'TRANSLATIONS', 'FORMATS')" /noheaders
    goto :EOF
)

echo Unknown server type: %servertype%
exit


:check_savetodb_developer_framework

set savetodb_developer_framework_required=0
set savetodb_developer_framework_installed=0

if exist sources\savetodb-developer-framework-install.sql   set savetodb_developer_framework_required=1
if exist savetodb-developer-framework-install.sql           set savetodb_developer_framework_required=1

if %savetodb_developer_framework_required%==0 goto :EOF

if .%create_database%.==.1. goto :EOF

set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.VIEWS t WHERE t.TABLE_SCHEMA IN ('xls') AND t.TABLE_NAME IN ('view_all_translations')" /noheaders

for /F %%i in ('%cmd%') do set tablecount=%%i

if .%tablecount%.==.Execution.  goto GSQLCMD_ERROR
if .%tablecount%.==.Unknown.    goto GSQLCMD_ERROR

if not .%tablecount%.==.1.      goto :EOF

set savetodb_developer_framework_installed=1

goto :EOF


:check_savetodb_administrator_framework

set savetodb_administrator_framework_required=0
set savetodb_administrator_framework_installed=0

if exist sources\savetodb-administrator-framework-install.sql   set savetodb_administrator_framework_required=1
if exist savetodb-administrator-framework-install.sql           set savetodb_administrator_framework_required=1

if %savetodb_administrator_framework_required%==0 goto :EOF

if .%create_database%.==.1. goto :EOF

set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.ROUTINES t WHERE t.ROUTINE_SCHEMA IN ('xls') AND t.ROUTINE_NAME IN ('usp_role_members')" /noheaders

for /F %%i in ('%cmd%') do set tablecount=%%i

if .%tablecount%.==.Execution.  goto GSQLCMD_ERROR
if .%tablecount%.==.Unknown.    goto GSQLCMD_ERROR

if not .%tablecount%.==.1.      goto :EOF

set savetodb_administrator_framework_installed=1

goto :EOF


:check_change_tracking_framework

set change_tracking_framework_required=0
set change_tracking_framework_installed=0

if exist sources\change-tracking-framework-install.sql set change_tracking_framework_required=1
if exist change-tracking-framework-install.sql         set change_tracking_framework_required=1

if %change_tracking_framework_required%==0 goto :EOF

if .%create_database%.==.1. goto :EOF

set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('logs') AND t.TABLE_NAME IN ('base_tables', 'change_logs', 'tables', 'handlers')" /noheaders

for /F %%i in ('%cmd%') do set tablecount=%%i

if .%tablecount%.==.Execution.  goto GSQLCMD_ERROR
if .%tablecount%.==.Unknown.    goto GSQLCMD_ERROR

if not .%tablecount%.==.4.      goto :EOF

set change_tracking_framework_installed=1

goto :EOF


:check_database_help_framework

set database_help_framework_required=0
set database_help_framework_installed=0

if exist sources\database-help-framework-install.sql set database_help_framework_required=1
if exist database-help-framework-install.sql         set database_help_framework_required=1

if %database_help_framework_required%==0 goto :EOF

if .%create_database%.==.1. goto :EOF

set cmd=gsqlcmd setup "SELECT COUNT(*) AS tablecount FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA IN ('doc') AND t.TABLE_NAME IN ('help', 'history', 'objects', 'handlers')" /noheaders

for /F %%i in ('%cmd%') do set tablecount=%%i

if .%tablecount%.==.Execution.  goto GSQLCMD_ERROR
if .%tablecount%.==.Unknown.    goto GSQLCMD_ERROR

if not .%tablecount%.==.4.      goto :EOF

set database_help_framework_installed=1

goto :EOF


:GSQLCMD_ERROR

%cmd%
pause
exit

:END
