setup.iss

[Setup]
AppName=MailRequestServer
AppVersion=2.0.0
AppPublisher=MailRequestServer
DefaultDirName={pf}\MailRequestServer
DefaultGroupName=MailRequestServer
OutputBaseFilename=MailRequestServerSetup
Compression=lzma
SolidCompression=yes
PrivilegesRequired=admin

[Files]
Source: "publish\*"; DestDir: "{app}"; Flags: recursesubdirs ignoreversion
Source: "install_tables.sql"; DestDir: "{tmp}"; Flags: deleteafterinstall

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

[Code]
var
  DBPage: TInputQueryWizardPage;
  AppUserPage: TInputQueryWizardPage;
  SMTPPage: TInputQueryWizardPage;

procedure InitializeWizard;
begin
  DBPage := CreateInputQueryPage(wpSelectDir,
    'Database Connection',
    'SQL Server admin credentials for setup.',
    'Enter connection details with rights to create databases, tables, and logins.');
  DBPage.Add('Server:', False);
  DBPage.Add('Database Name:', False);
  DBPage.Add('Admin Username:', False);
  DBPage.Add('Admin Password:', True);
  DBPage.Values[0] := 'localhost';
  DBPage.Values[1] := 'MailRequestDB';
  DBPage.Values[2] := 'sa';

  AppUserPage := CreateInputQueryPage(DBPage.ID,
    'Application Database User',
    'Credentials for the application database user.',
    'A SQL Server login and database user will be created with these credentials.' + #13#10 +
    'The application will use this account to access the mail tables.');
  AppUserPage.Add('Username:', False);
  AppUserPage.Add('Password:', True);
  AppUserPage.Values[0] := 'mailuser';

  SMTPPage := CreateInputQueryPage(AppUserPage.ID,
    'SMTP Configuration',
    'Email server settings for sending mail.',
    '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';
end;

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

  if CurPageID = DBPage.ID then
  begin
    if (DBPage.Values[0] = '') or (DBPage.Values[1] = '') or
       (DBPage.Values[2] = '') or (DBPage.Values[3] = '') then
    begin
      MsgBox('All database connection fields are required.', mbError, MB_OK);
      Result := False;
    end;
  end;

  if CurPageID = AppUserPage.ID then
  begin
    if (AppUserPage.Values[0] = '') or (AppUserPage.Values[1] = '') then
    begin
      MsgBox('Both username and password are required.', mbError, MB_OK);
      Result := False;
    end;
  end;

  if CurPageID = SMTPPage.ID then
  begin
    if (SMTPPage.Values[0] = '') or (SMTPPage.Values[1] = '') or
       (SMTPPage.Values[5] = '') then
    begin
      MsgBox('SMTP Server, Port, and Sender Email Address are required.', mbError, MB_OK);
      Result := False;
    end;
  end;
end;

function ExecSqlCmd(Server, Database, Username, Password, SqlText: String): Boolean;
var
  TmpFile: String;
  ResultCode: Integer;
begin
  TmpFile := ExpandConstant('{tmp}\sqlcmd_exec.sql');
  SaveStringToFile(TmpFile, SqlText, False);
  Result := Exec('sqlcmd',
    '-S "' + Server + '" -d "' + Database + '" -U "' + Username + '" -P "' + Password + '" -i "' + TmpFile + '"',
    '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  Result := Result and (ResultCode = 0);
  DeleteFile(TmpFile);
end;

procedure CurStepChanged(CurStep: TSetupStep);
var
  Server, Database, AdminUser, AdminPass: String;
  AppUser, AppPass: String;
  SmtpServer, SmtpPort, SmtpUser, SmtpPass, SenderName, SenderEmail: String;
  SqlQuery, ConfigContent, ConfigPath, AppDir: String;
  ResultCode: Integer;
begin
  if CurStep <> ssPostInstall then
    Exit;

  Server := DBPage.Values[0];
  Database := DBPage.Values[1];
  AdminUser := DBPage.Values[2];
  AdminPass := DBPage.Values[3];
  AppUser := AppUserPage.Values[0];
  AppPass := AppUserPage.Values[1];
  SmtpServer := SMTPPage.Values[0];
  SmtpPort := SMTPPage.Values[1];
  SmtpUser := SMTPPage.Values[2];
  SmtpPass := SMTPPage.Values[3];
  SenderName := SMTPPage.Values[4];
  SenderEmail := SMTPPage.Values[5];
  AppDir := ExpandConstant('{app}');
  ConfigPath := AppDir + '\config.txt';

  // Step 1: Create the database if it does not exist.
  SqlQuery :=
    'IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = ''' + Database + ''')' + #13#10 +
    'BEGIN' + #13#10 +
    '  CREATE DATABASE [' + Database + '];' + #13#10 +
    'END';
  if not ExecSqlCmd(Server, 'master', AdminUser, AdminPass, SqlQuery) then
  begin
    MsgBox('Failed to create database. Please verify the admin credentials and that sqlcmd is available on the system PATH.', mbError, MB_OK);
    Exit;
  end;

  // Step 2: Create the tables.
  if not Exec('sqlcmd',
    '-S "' + Server + '" -d "' + Database + '" -U "' + AdminUser + '" -P "' + AdminPass + '" -i "' + ExpandConstant('{tmp}\install_tables.sql') + '"',
    '', SW_HIDE, ewWaitUntilTerminated, ResultCode) or (ResultCode <> 0) then
  begin
    MsgBox('Failed to create database tables.', mbError, MB_OK);
    Exit;
  end;

  // Step 3: Create the application login and database user.
  SqlQuery :=
    'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ''' + AppUser + ''')' + #13#10 +
    '  CREATE LOGIN [' + AppUser + '] WITH PASSWORD = ''' + AppPass + ''', DEFAULT_DATABASE = [' + Database + '];' + #13#10 +
    'GO' + #13#10 +
    'USE [' + Database + '];' + #13#10 +
    'GO' + #13#10 +
    'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + AppUser + ''')' + #13#10 +
    '  CREATE USER [' + AppUser + '] FOR LOGIN [' + AppUser + '];' + #13#10 +
    'GO' + #13#10 +
    'EXEC sp_addrolemember ''db_datareader'', ''' + AppUser + ''';' + #13#10 +
    'EXEC sp_addrolemember ''db_datawriter'', ''' + AppUser + ''';';
  if not ExecSqlCmd(Server, 'master', AdminUser, AdminPass, SqlQuery) then
  begin
    MsgBox('Failed to create application database user.', mbError, MB_OK);
    Exit;
  end;

  // Step 4: Insert SMTP configuration into the MailConfig table.
  SqlQuery :=
    'IF NOT EXISTS (SELECT * FROM MailConfig)' + #13#10 +
    'INSERT INTO MailConfig (ServerURL, ServerPort, Username, Password, SenderName, SenderAddress, ServiceCommand)' + #13#10 +
    'VALUES (''' + SmtpServer + ''', ' + SmtpPort + ', ''' + SmtpUser + ''', ''' + SmtpPass + ''', ''' + SenderName + ''', ''' + SenderEmail + ''', '''');';
  if not ExecSqlCmd(Server, Database, AdminUser, AdminPass, SqlQuery) then
  begin
    MsgBox('Failed to insert SMTP configuration.', mbError, MB_OK);
    Exit;
  end;

  // Step 5: Write the config.txt file.
  ConfigContent :=
    'Server=' + Server + #13#10 +
    'Database=' + Database + #13#10 +
    'Username=' + AppUser + #13#10 +
    'Password=' + AppPass;
  SaveStringToFile(ConfigPath, ConfigContent, False);

  // Step 6: Install and start the Windows service.
  Exec('sc.exe', 'create MailRequestServer binPath= "\"' + AppDir + '\MailRequestServer.exe\" ConfigFile=\"' + ConfigPath + '\"" start= auto',
    '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
  Exec('sc.exe', 'start MailRequestServer',
    '', SW_HIDE, ewWaitUntilTerminated, ResultCode);
end;

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