setup.iss

[Setup]
AppName=MailAlertServer
AppVersion=2.0.0
AppPublisher=MailAlertServer
DefaultDirName={autopf}\MailAlertServer
DefaultGroupName=MailAlertServer
OutputBaseFilename=MailAlertServerSetup
Compression=lzma
SolidCompression=yes
PrivilegesRequired=admin
ArchitecturesInstallIn64BitMode=x64compatible

[Files]
Source: "bin\Release\net8.0\win-x64\publish\*"; DestDir: "{app}"; Excludes: "config.txt"; Flags: ignoreversion recursesubdirs
Source: "setup_db.sql"; DestDir: "{tmp}"; Flags: deleteafterinstall

[UninstallRun]
Filename: "sc.exe"; Parameters: "stop MailAlertServer"; Flags: runhidden; RunOnceId: "StopService"
Filename: "sc.exe"; Parameters: "delete MailAlertServer"; Flags: runhidden; RunOnceId: "DeleteService"

[Code]
var
  DBServerPage: TInputQueryWizardPage;
  AuthMethodPage: TInputOptionWizardPage;
  AdminCredPage: TInputQueryWizardPage;
  DBNamePage: TInputQueryWizardPage;
  AppCredPage: TInputQueryWizardPage;
  IntervalPage: TInputQueryWizardPage;

procedure InitializeWizard;
begin
  DBServerPage := CreateInputQueryPage(wpSelectDir,
    'Database Server', 'Specify the SQL Server instance',
    'Enter the SQL Server instance name or IP address that will host the MailAlertServer database.');
  DBServerPage.Add('Server:', False);
  DBServerPage.Values[0] := 'localhost';

  AuthMethodPage := CreateInputOptionPage(DBServerPage.ID,
    'Setup Authentication Method', 'Database setup credentials',
    'Select the authentication method for the administrator account used during setup.' + #13#10 +
    'This account must have rights to create databases, tables, logins, and users.',
    True, False);
  AuthMethodPage.Add('Windows Authentication (current user)');
  AuthMethodPage.Add('SQL Server Authentication');
  AuthMethodPage.SelectedValueIndex := 0;

  AdminCredPage := CreateInputQueryPage(AuthMethodPage.ID,
    'Administrator Credentials', 'SQL Server administrator login',
    'Enter the SQL Server administrator credentials for database setup.');
  AdminCredPage.Add('Username:', False);
  AdminCredPage.Add('Password:', True);
  AdminCredPage.Values[0] := 'sa';

  DBNamePage := CreateInputQueryPage(AdminCredPage.ID,
    'Database Settings', 'Application database',
    'Enter the name for the MailAlertServer database.' + #13#10 +
    'It will be created if it does not exist.');
  DBNamePage.Add('Database Name:', False);
  DBNamePage.Values[0] := 'MailAlertDB';

  AppCredPage := CreateInputQueryPage(DBNamePage.ID,
    'Application Credentials', 'Runtime database user',
    'Enter credentials for the SQL Server login that MailAlertServer will use at runtime.' + #13#10 +
    'This login and database user will be created if they do not exist.');
  AppCredPage.Add('Username:', False);
  AppCredPage.Add('Password:', True);
  AppCredPage.Values[0] := 'mailalert_user';

  IntervalPage := CreateInputQueryPage(AppCredPage.ID,
    'Service Settings', 'Alert check interval',
    'Specify how often the service should check for alert conditions.');
  IntervalPage.Add('Interval (seconds):', False);
  IntervalPage.Values[0] := '60';
end;

function ShouldSkipPage(PageID: Integer): Boolean;
begin
  Result := False;
  if PageID = AdminCredPage.ID then
    Result := (AuthMethodPage.SelectedValueIndex = 0);
end;

function NextButtonClick(CurPageID: Integer): Boolean;
var
  IntervalVal: Integer;
begin
  Result := True;

  if CurPageID = DBServerPage.ID then
  begin
    if Trim(DBServerPage.Values[0]) = '' then
    begin
      MsgBox('Please enter a server name or IP address.', mbError, MB_OK);
      Result := False;
    end;
  end;

  if CurPageID = AdminCredPage.ID then
  begin
    if (Trim(AdminCredPage.Values[0]) = '') or (Trim(AdminCredPage.Values[1]) = '') then
    begin
      MsgBox('Please enter both an administrator username and password.', mbError, MB_OK);
      Result := False;
    end;
  end;

  if CurPageID = DBNamePage.ID then
  begin
    if Trim(DBNamePage.Values[0]) = '' then
    begin
      MsgBox('Please enter a database name.', mbError, MB_OK);
      Result := False;
    end;
  end;

  if CurPageID = AppCredPage.ID then
  begin
    if (Trim(AppCredPage.Values[0]) = '') or (Trim(AppCredPage.Values[1]) = '') then
    begin
      MsgBox('Please enter both a username and password for the application user.', mbError, MB_OK);
      Result := False;
    end;
  end;

  if CurPageID = IntervalPage.ID then
  begin
    IntervalVal := StrToIntDef(Trim(IntervalPage.Values[0]), 0);
    if IntervalVal < 1 then
    begin
      MsgBox('Please enter a valid interval in seconds (minimum 1).', mbError, MB_OK);
      Result := False;
    end;
  end;
end;

function FindSqlCmd(): String;
var
  ResultCode: Integer;
  Output: AnsiString;
  ProgramFiles: String;
begin
  Result := '';

  // Try to find sqlcmd via the where command (checks PATH)
  if Exec('cmd.exe', '/c where sqlcmd.exe > "' + ExpandConstant('{tmp}\sqlcmd_path.txt') + '" 2>nul',
          '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
  begin
    if ResultCode = 0 then
    begin
      if LoadStringFromFile(ExpandConstant('{tmp}\sqlcmd_path.txt'), Output) then
      begin
        Result := Trim(String(Output));
        if Pos(#13, Result) > 0 then
          Result := Copy(Result, 1, Pos(#13, Result) - 1);
        if FileExists(Result) then
          Exit;
      end;
    end;
  end;

  // Check common installation paths
  ProgramFiles := ExpandConstant('{commonpf}');

  if FileExists(ProgramFiles + '\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE') then
  begin
    Result := ProgramFiles + '\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE';
    Exit;
  end;

  if FileExists(ProgramFiles + '\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.EXE') then
  begin
    Result := ProgramFiles + '\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.EXE';
    Exit;
  end;

  if FileExists(ProgramFiles + '\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE') then
  begin
    Result := ProgramFiles + '\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE';
    Exit;
  end;
end;

function RunDatabaseSetup(): Boolean;
var
  SqlCmdPath: String;
  Params: String;
  SqlFile: String;
  ResultCode: Integer;
begin
  Result := False;
  SqlCmdPath := FindSqlCmd();

  if SqlCmdPath = '' then
  begin
    MsgBox('sqlcmd.exe was not found.' + #13#10 + #13#10 +
           'Please install the SQL Server Command Line Utilities (sqlcmd) and try again.' + #13#10 +
           'You can download it from the Microsoft website as part of the ODBC Driver for SQL Server.',
           mbError, MB_OK);
    Exit;
  end;

  SqlFile := ExpandConstant('{tmp}\setup_db.sql');

  // Build sqlcmd parameters
  if AuthMethodPage.SelectedValueIndex = 0 then
    Params := '-S "' + DBServerPage.Values[0] + '" -E'
  else
    Params := '-S "' + DBServerPage.Values[0] + '" -U "' + AdminCredPage.Values[0] + '" -P "' + AdminCredPage.Values[1] + '"';

  Params := Params +
    ' -v DbName="' + DBNamePage.Values[0] + '"' +
    ' AppUser="' + AppCredPage.Values[0] + '"' +
    ' AppPass="' + AppCredPage.Values[1] + '"' +
    ' -i "' + SqlFile + '"' +
    ' -b';

  if not Exec(SqlCmdPath, Params, '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
  begin
    MsgBox('Failed to execute sqlcmd.exe.', mbError, MB_OK);
    Exit;
  end;

  if ResultCode <> 0 then
  begin
    MsgBox('Database setup failed with exit code ' + IntToStr(ResultCode) + '.' + #13#10 + #13#10 +
           'Please verify:' + #13#10 +
           '  - The SQL Server instance is running and accessible' + #13#10 +
           '  - The administrator credentials are correct' + #13#10 +
           '  - The administrator has sufficient permissions',
           mbError, MB_OK);
    Exit;
  end;

  Result := True;
end;

procedure WriteConfigFile();
var
  ConfigPath: String;
  Lines: TStringList;
begin
  ConfigPath := ExpandConstant('{app}\config.txt');
  Lines := TStringList.Create;
  try
    Lines.Add('Server=' + DBServerPage.Values[0]);
    Lines.Add('Database=' + DBNamePage.Values[0]);
    Lines.Add('Username=' + AppCredPage.Values[0]);
    Lines.Add('Password=' + AppCredPage.Values[1]);
    Lines.Add('Interval=' + IntervalPage.Values[0]);
    Lines.Add('TrustServerCertificate=True');
    Lines.SaveToFile(ConfigPath);
  finally
    Lines.Free;
  end;
end;

function InstallService(): Boolean;
var
  ExePath: String;
  ResultCode: Integer;
begin
  Result := False;
  ExePath := ExpandConstant('{app}\MailAlertServer.exe');

  // Create the Windows service
  if not Exec('sc.exe', 'create MailAlertServer binPath= "' + ExePath + '" start= auto DisplayName= "Mail Alert Server"',
              '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
  begin
    MsgBox('Failed to create the Windows service.', mbError, MB_OK);
    Exit;
  end;

  // Set description
  Exec('sc.exe', 'description MailAlertServer "Monitors database conditions and sends email alert notifications."',
       '', SW_HIDE, ewWaitUntilTerminated, ResultCode);

  // Start the service
  if not Exec('sc.exe', 'start MailAlertServer',
              '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
  begin
    MsgBox('The service was installed but could not be started.' + #13#10 +
           'You may need to start it manually from the Services console.',
           mbInformation, MB_OK);
  end;

  Result := True;
end;

function PrepareToInstall(var NeedsRestart: Boolean): String;
var
  ResultCode: Integer;
begin
  Result := '';
  // Stop existing service if running (ignore errors for fresh installs)
  Exec('sc.exe', 'stop MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  if ResultCode = 0 then
    Sleep(3000);
  // Delete existing service if present (will be recreated after install)
  Exec('sc.exe', 'delete MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
end;

procedure CurStepChanged(CurStep: TSetupStep);
begin
  if CurStep = ssPostInstall then
  begin
    if not RunDatabaseSetup() then
    begin
      MsgBox('Database setup was not completed successfully.' + #13#10 +
             'You will need to run the setup_db.sql script manually using sqlcmd.' + #13#10 +
             'The service will be installed but may not function until the database is configured.',
             mbInformation, MB_OK);
    end;

    WriteConfigFile();
    InstallService();
  end;
end;