@echo off
setlocal EnableDelayedExpansion

set result=online
set default=default_file
set skip=6
set message=
set message2=
if exist Dlg_List.lst del Dlg_List.lst

if not "%1"=="" set /a "skip=5+%1"

sqlcmd -S .\SURFCAM -Q "SELECT name, state_desc FROM [master].[sys].[DATABASES]" -o "%tempfile%"
if %errorlevel% neq 0 goto instance_not_found

set database=SURFCAM_v1
set datafile=%folder%\%database%_Data.mdf
set logfile=%folder%\%database%_Log.ldf
findstr /i /c:"%database% " "%tempfile%" > nul
if %errorlevel% equ 1 goto reconnect

set cnt=0
for /f "tokens=1,2" %%a in (%tempfile%) do (
if /i "%%b"=="Error:" goto instance_not_found
if /i "%%b"=="rows" goto test
set /a "cnt+=1"
if !cnt! gtr 6 sqlcmd -S .\SURFCAM -Q "SELECT * FROM [%%a].[dbo].[TABLE_CATALOG]" > nul
)

:test
sqlcmd -S .\SURFCAM -Q "SELECT name, state_desc FROM [master].[sys].[DATABASES]" -o "%tempfile%"

:test_next
for /f "tokens=1,2 skip=%skip%" %%a in (%tempfile%) do (
if /i "%%b"=="rows" goto end
if /i "%%b"=="ONLINE" echo %%a>>Dlg_List.lst
set database=%%a
set state=%%b
goto found
)
goto end

:found
set datafile=%folder%\%database%_Data.mdf
set logfile=%folder%\%database%_Log.ldf
REM if not exist "%datafile%" goto no_data_file
REM if not exist "%logfile%" goto no_log_file
if /i "%state%"=="ONLINE" goto %result%
if /i "%state%"=="RECOVERY_PENDING" goto reconnect
if /i "%state%"=="EMERGENCY"        goto reconnect
if /i "%result%"=="done" goto unsucces
goto status

:reconnect
echo %database% database found in %state% status. Reconnecting...
sqlcmd -S .\SURFCAM -Q "ALTER DATABASE [%database%] SET EMERGENCY" > nul
sqlcmd -S .\SURFCAM -Q "ALTER DATABASE [%database%] SET MULTI_USER" > nul
sqlcmd -S .\SURFCAM -Q "EXEC sp_detach_db '%database%'" > nul
sqlcmd -S .\SURFCAM -Q "CREATE DATABASE [%database%] ON (FILENAME = N'%datafile%'), (FILENAME = N'%logfile%') FOR ATTACH"
set result=done
goto test

:next
if not "%1"=="" goto end
set /a "skip+=1"
set result=online
set default=default_file
echo.
goto test_next

:no_data_file
echo SURFCAM Tool database file "%datafile%" not found.
set message2=SURFCAM tietokantatiedostoa~"%datafile%"~ei l”ydy.~
goto %default%

:no_log_file
echo SURFCAM Tool database log-file "%logfile%" not found.
set message2=%message2%SURFCAM tietokantatiedostoa~"%logfile%"~ei l”ydy.~
goto %default%

:default_file
echo Create the default Tool database files?
start /w wrun msgbox exclamation yesno defbutton2 "Virhe" "%message2%~Haluatko luoda oletus tietokantatiedostot?"
set message2=
call "%temp%\Dlg_ret.bat"
if "%Dlg_Ret1%"=="MB_NO" goto no_default_file
if not exist "%folder%\*.*" md "%folder%"
copy "..\ToolLibrary\SURFCAM_v1_Data.mdf" "%datafile%" > nul
copy "..\ToolLibrary\SURFCAM_v1_Log.ldf"  "%logfile%" > nul
set default=couldnt_create_default_file
echo.
goto reconnect

:couldnt_create_default_file
echo.
echo Couldn't create the default Tool database files. Reconnection can't be made.
set message=Ei voitu luoda oletus tietokantatiedostoja.~Yhteytt„ ei voitu korjata.~
goto next

:no_default_file
echo.
echo Reconnection can't be made.
set message=%message%%database% ty”kalutietokannan yhteytt„ ei voitu korjata.~
goto next

:done
echo.
echo %database% database Reconnected.
set message=%message%%database% ty”kalutietokanta yhdistetty.~
goto next

:online
if "%1"=="" goto next
echo %database% database is already connected. No action required.
set message=%message%%database% ty”kalutietokanta on jo yhdistetty.~Toimia ei tarvita.~
goto next

:status
echo %database% database found in %state% status. Reconnection can't be made.
set message=%message%%database% ty”kalutietokanta on %state% tilassa.~Yhteytt„ ei voitu korjata.~
goto next

:unsucces
echo %database% database found in %state% status, but Reconnection can't be made.
set message=%message%%database% ty”kalutietokanta on %state% tilassa.~Yhteytt„ ei voitu korjata.~
goto next

:instance_not_found
set server=
echo SURFCAM instance not found.
start /w wrun msgbox exclamation ok defbutton1 "Virhe" "SURFCAM SQL instanssia ei l”ydy!"
goto :eof

:end
if not "%message%"=="" start /w wrun msgbox information ok defbutton1 "Ty”kalutietokanta" "%message%"
if not "%database%"=="" goto :eof
echo SURFCAM database not found.
start /w wrun msgbox exclamation ok defbutton1 "Virhe" "SURFCAM ty”kalutietokantaa ei l”ydy!"
