@echo off
setlocal EnableExtensions EnableDelayedExpansion
REM ===========================================================================
REM  PHOA headless MySQL 8.0 database setup
REM  Copyright (c) 2025-2026 Jorg Jemelka / Competition Software / eHeuristics.
REM
REM  What it does, in order:
REM    1. If MySQL Server 8.0 is installed but NOT yet configured (no service),
REM       initialize a fresh data directory, register + start the MySQL80
REM       Windows service, and set a known root password.
REM    2. Apply schema_phoa_water.sql: create the phoa_water database and the
REM       phoa_water application account the ingest service logs in with.
REM
REM  If a MySQL service is already present, step 1 is skipped and only the
REM  schema is applied.
REM
REM  Usage:  db_setup.bat [ROOT_PASSWORD] [SCHEMA_PATH]
REM    ROOT_PASSWORD  MySQL root@localhost password (default: PHOAadmin123)
REM    SCHEMA_PATH    schema file (default: schema_phoa_water.sql next to this)
REM
REM  Run elevated (Administrator): it registers a Windows service.
REM  MySQL is bound to localhost only; the default password is safe for a
REM  single-machine install. Change it if the server is ever exposed on a LAN.
REM ===========================================================================

set "BASEDIR=%ProgramFiles%\MySQL\MySQL Server 8.0"
set "MYSQLD=%BASEDIR%\bin\mysqld.exe"
set "MYSQL=%BASEDIR%\bin\mysql.exe"
set "PDROOT=%ProgramData%\MySQL\MySQL Server 8.0"
set "DATADIR=%PDROOT%\Data"
set "INIFILE=%PDROOT%\my.ini"
set "SVC=MySQL80"

set "ROOTPW=%~1"
if "%ROOTPW%"=="" set "ROOTPW=PHOAadmin123"
set "SCHEMA=%~2"
if "%SCHEMA%"=="" set "SCHEMA=%~dp0schema_phoa_water.sql"

if not exist "%MYSQLD%" (
  echo [PHOA] MySQL Server 8.0 not found at "%BASEDIR%".
  echo [PHOA] Install MySQL Community Server 8.0.30 first, then re-run this script.
  exit /b 2
)
if not exist "%SCHEMA%" (
  echo [PHOA] Schema file not found: "%SCHEMA%"
  exit /b 2
)

REM --- If a MySQL service already exists, skip init and just apply schema. -----
sc query "%SVC%" >nul 2>&1 && goto :have_service
sc query "MySQL" >nul 2>&1 && ( set "SVC=MySQL" & goto :have_service )

echo [PHOA] No MySQL service found - performing a fresh headless initialize...
if exist "%DATADIR%\mysql" (
  echo [PHOA] A data directory already exists at "%DATADIR%" but no service is
  echo [PHOA] registered. Not touching existing data. Register/repair MySQL
  echo [PHOA] first, then re-run this script to apply the schema.
  exit /b 3
)

if not exist "%PDROOT%" mkdir "%PDROOT%"
if not exist "%INIFILE%" (
  echo [PHOA] Writing "%INIFILE%"...
  > "%INIFILE%" echo [mysqld]
  >>"%INIFILE%" echo basedir=%BASEDIR:\=/%
  >>"%INIFILE%" echo datadir=%DATADIR:\=/%
  >>"%INIFILE%" echo port=3306
)

echo [PHOA] Initializing data directory (insecure: empty root password)...
"%MYSQLD%" --defaults-file="%INIFILE%" --initialize-insecure
if errorlevel 1 ( echo [PHOA] mysqld --initialize-insecure failed & exit /b 1 )

echo [PHOA] Registering the %SVC% Windows service...
"%MYSQLD%" --install %SVC% --defaults-file="%INIFILE%"
if errorlevel 1 ( echo [PHOA] service install failed & exit /b 1 )

echo [PHOA] Starting %SVC%...
net start %SVC%
if errorlevel 1 ( echo [PHOA] net start %SVC% failed & exit /b 1 )

echo [PHOA] Setting root password...
"%MYSQL%" -u root --skip-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '%ROOTPW%'; FLUSH PRIVILEGES;"
if errorlevel 1 echo [PHOA] Note: could not set root password (root may already have one).
goto :apply

:have_service
echo [PHOA] MySQL service "%SVC%" is present.
sc query "%SVC%" | find "RUNNING" >nul 2>&1 || net start "%SVC%"

:apply
echo [PHOA] Applying schema: "%SCHEMA%"
REM Try the known root password first; fall back to an insecure (empty) root.
"%MYSQL%" -u root -p%ROOTPW% < "%SCHEMA%" 2>nul
if not errorlevel 1 goto :ok
"%MYSQL%" -u root --skip-password < "%SCHEMA%" 2>nul
if not errorlevel 1 goto :ok
echo [PHOA] Schema apply failed. If the root account has a different password, run:
echo         "%MYSQL%" -u root -p ^< "%SCHEMA%"
exit /b 1

:ok
echo [PHOA] Done. Database "phoa_water" and app user "phoa_water" are ready.
exit /b 0