setup.iss

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

[Files]
; MailAlertServer files
Source: "publish\MailAlertServer\*"; DestDir: "{app}\MailAlertServer"; Flags: ignoreversion recursesubdirs
; MailRequestServer files
Source: "publish\MailRequestServer\*"; DestDir: "{app}\MailRequestServer"; Flags: ignoreversion recursesubdirs
; Database setup script
Source: "setup_db.sql"; DestDir: "{tmp}"; Flags: deleteafterinstall

[UninstallRun]
Filename: "sc.exe"; Parameters: "stop MailAlertServer"; Flags: runhidden; RunOnceId: "StopAlertService"
Filename: "sc.exe"; Parameters: "stop MailRequestServer"; Flags: runhidden; RunOnceId: "StopRequestService"
Filename: "sc.exe"; Parameters: "delete MailAlertServer"; Flags: runhidden; RunOnceId: "DeleteAlertService"
Filename: "sc.exe"; Parameters: "delete MailRequestServer"; Flags: runhidden; RunOnceId: "DeleteRequestService"

[Code]
var
  DBServerPage: TInputQueryWizardPage;
  AuthMethodPage: TInputOptionWizardPage;
  AdminCredPage: TInputQueryWizardPage;
  DBNamePage: TInputQueryWizardPage;
  AppCredPage: TInputQueryWizardPage;
  SMTPPage: 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 AlertMailService 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 shared database used by both services.' + #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 both services 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';

  SMTPPage := CreateInputQueryPage(AppCredPage.ID,
    'SMTP Configuration', 'Email server settings',
    'Enter the SMTP server settings for sending email.' + #13#10 +
    'These settings will be stored in the MailConfig table.');
  SMTPPage.Add('SMTP Server:', False);
  SMTPPage.Add('SMTP Port:', False);
  SMTPPage.Add('SMTP Username:', False);
  SMTPPage.Add('SMTP Password:', True);
  SMTPPage.Add('Sender Name:', False);
  SMTPPage.Add('Sender Email Address:', False);
  SMTPPage.Values[0] := 'smtp.example.com';
  SMTPPage.Values[1] := '587';

  IntervalPage := CreateInputQueryPage(SMTPPage.ID,
    'Service Settings', 'Alert check interval',
    'Specify how often the MailAlertServer 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 = SMTPPage.ID then
  begin
    if (Trim(SMTPPage.Values[0]) = '') or (Trim(SMTPPage.Values[1]) = '') or
       (Trim(SMTPPage.Values[5]) = '') then
    begin
      MsgBox('SMTP Server, Port, and Sender Email Address are required.', 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;

function InsertSmtpConfig(): Boolean;
var
  SqlCmdPath: String;
  Params: String;
  SqlFile: String;
  SqlText: String;
  ResultCode: Integer;
begin
  Result := False;
  SqlCmdPath := FindSqlCmd();
  if SqlCmdPath = '' then
    Exit;

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

  SqlText :=
    'USE [' + DBNamePage.Values[0] + ']' + #13#10 +
    'GO' + #13#10 +
    'IF NOT EXISTS (SELECT * FROM MailConfig)' + #13#10 +
    'INSERT INTO MailConfig (ServerURL, ServerPort, Username, Password, SenderName, SenderAddress, ServiceCommand)' + #13#10 +
    'VALUES (''' + SMTPPage.Values[0] + ''', ' + SMTPPage.Values[1] + ', ''' + SMTPPage.Values[2] + ''', ''' + SMTPPage.Values[3] + ''', ''' + SMTPPage.Values[4] + ''', ''' + SMTPPage.Values[5] + ''', '''');' + #13#10 +
    'GO';

  SaveStringToFile(SqlFile, SqlText, False);

  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 + ' -i "' + SqlFile + '" -b';

  if Exec(SqlCmdPath, Params, '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
    Result := (ResultCode = 0);

  DeleteFile(SqlFile);
end;

procedure WriteAlertConfigFile();
var
  ConfigPath: String;
  Lines: TStringList;
begin
  ConfigPath := ExpandConstant('{app}\MailAlertServer\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;

procedure WriteRequestConfigFile();
var
  ConfigPath: String;
  Lines: TStringList;
begin
  ConfigPath := ExpandConstant('{app}\MailRequestServer\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.SaveToFile(ConfigPath);
  finally
    Lines.Free;
  end;
end;

function InstallServices(): Boolean;
var
  AlertExePath: String;
  RequestExePath: String;
  RequestConfigPath: String;
  ResultCode: Integer;
begin
  Result := False;
  AlertExePath := ExpandConstant('{app}\MailAlertServer\MailAlertServer.exe');
  RequestExePath := ExpandConstant('{app}\MailRequestServer\MailRequestServer.exe');
  RequestConfigPath := ExpandConstant('{app}\MailRequestServer\config.txt');

  // Create MailAlertServer service
  if not Exec('sc.exe', 'create MailAlertServer binPath= "' + AlertExePath + '" start= auto DisplayName= "Mail Alert Server"',
              '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
  begin
    MsgBox('Failed to create the MailAlertServer Windows service.', mbError, MB_OK);
    Exit;
  end;
  Exec('sc.exe', 'description MailAlertServer "Monitors database conditions and sends email alert notifications."',
       '', SW_HIDE, ewWaitUntilTerminated, ResultCode);

  // Create MailRequestServer service
  if not Exec('sc.exe', 'create MailRequestServer binPath= "\"' + RequestExePath + '\" ConfigFile=\"' + RequestConfigPath + '\"" start= auto DisplayName= "Mail Request Server"',
              '', SW_HIDE, ewWaitUntilTerminated, ResultCode) then
  begin
    MsgBox('Failed to create the MailRequestServer Windows service.', mbError, MB_OK);
    Exit;
  end;
  Exec('sc.exe', 'description MailRequestServer "Processes queued email requests from the database and sends them via SMTP."',
       '', SW_HIDE, ewWaitUntilTerminated, ResultCode);

  // Start both services
  Exec('sc.exe', 'start MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  Exec('sc.exe', 'start MailRequestServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);

  Result := True;
end;

function PrepareToInstall(var NeedsRestart: Boolean): String;
var
  ResultCode: Integer;
begin
  Result := '';
  // Stop and remove existing services if running (ignore errors for fresh installs)
  Exec('sc.exe', 'stop MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  Exec('sc.exe', 'stop MailRequestServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  if ResultCode = 0 then
    Sleep(3000);
  Exec('sc.exe', 'delete MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  Exec('sc.exe', 'delete MailRequestServer', '', 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 services will be installed but may not function until the database is configured.',
             mbInformation, MB_OK);
    end
    else
    begin
      if not InsertSmtpConfig() then
      begin
        MsgBox('SMTP configuration could not be inserted into the database.' + #13#10 +
               'You will need to manually insert a row into the MailConfig table.',
               mbInformation, MB_OK);
      end;
    end;

    WriteAlertConfigFile();
    WriteRequestConfigFile();
    InstallServices();
  end;
end;

procedure CurUninstallStepChanged(CurUninstallStep: TUninstallStep);
var
  ResultCode: Integer;
begin
  if CurUninstallStep = usUninstall then
  begin
    Exec('sc.exe', 'stop MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
    Exec('sc.exe', 'stop MailRequestServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
    Sleep(3000);
    Exec('sc.exe', 'delete MailAlertServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
    Exec('sc.exe', 'delete MailRequestServer', '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  end;
end;