---
- name: Create SQL Server data directories
  ansible.windows.win_file:
    path: "{{ item }}"
    state: directory
  loop:
    - "{{ mssql_data_dir }}"
    - "{{ mssql_log_dir }}"
    - "{{ mssql_backup_dir }}"
    - "{{ mssql_tempdb_dir }}"
    - "{{ mssql_tempdb_log_dir }}"

- name: Install SQL Server 2022
  block:
    - name: Run SQL Server 2022 setup
      ansible.windows.win_powershell:
        script: |
          $ErrorActionPreference = 'Stop'

          $sapwd = '{{ mssql_sa_password | replace("'", "''") }}'

          $proc = Start-Process `
            -FilePath '{{ mssql_iso_drive }}\setup.exe' `
            -ArgumentList @(
              '/Action=Install',
              '/QUIET',
              '/IACCEPTSQLSERVERLICENSETERMS',
              '/FEATURES={{ mssql_features }}',
              '/INSTANCENAME={{ mssql_instance_name }}',
              '/SQLCOLLATION={{ mssql_collation }}',
              '/SECURITYMODE={{ mssql_security_mode }}',
              "/SAPWD=$sapwd",
              '/SQLSYSADMINACCOUNTS="{{ mssql_sysadmin_accounts | join('" "') }}"',
              '/SQLSVCACCOUNT="{{ mssql_sql_svc_account }}"',
              '/AGTSVCACCOUNT="{{ mssql_agt_svc_account }}"',
              '/AGTSVCSTARTUPTYPE=Automatic',
              '/BROWSERSVCSTARTUPTYPE=Automatic',
              '/TCPENABLED={{ mssql_tcp_enabled }}',
              '/SQLUSERDBDIR="{{ mssql_data_dir }}"',
              '/SQLUSERDBLOGDIR="{{ mssql_log_dir }}"',
              '/SQLBACKUPDIR="{{ mssql_backup_dir }}"',
              '/SQLTEMPDBDIR="{{ mssql_tempdb_dir }}"',
              '/SQLTEMPDBLOGDIR="{{ mssql_tempdb_log_dir }}"',
              '/ASSVCACCOUNT="{{ mssql_as_svc_account }}"',
              '/ASSERVERMODE={{ mssql_as_server_mode }}',
              '/ASSYSADMINACCOUNTS="{{ mssql_sysadmin_accounts | join('" "') }}"',
              '/ISSVCACCOUNT="{{ mssql_is_svc_account }}"',
              '/UPDATEENABLED=False'
            ) `
            -Wait -PassThru

          if ($proc.ExitCode -notin @(0, 3010)) {
            throw "SQL Server setup failed with exit code: $($proc.ExitCode)"
          }

          $Ansible.Result = $proc.ExitCode
      register: mssql_install_result
      timeout: 3600

    - name: Report install exit code
      ansible.builtin.debug:
        msg: "SQL Server installed (exit code {{ mssql_install_result.result }}{% if mssql_install_result.result == 3010 %} — reboot required{% endif %})"

  rescue:
    - name: Display setup summary log
      ansible.windows.win_powershell:
        script: |
          $log = Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Filter 'Summary*.txt' `
                   -Recurse -ErrorAction SilentlyContinue |
                 Sort-Object LastWriteTime | Select-Object -Last 1
          if ($log) {
            Write-Output "=== $($log.FullName) ==="
            Get-Content $log.FullName | Select-Object -Last 100
          } else {
            Write-Output "No summary log found."
          }
      register: setup_log_content

    - name: Show log
      ansible.builtin.debug:
        msg: "{{ setup_log_content.output }}"

    - name: Fail
      ansible.builtin.fail:
        msg: "SQL Server installation failed — see log output above."

- name: Ensure SQL Server service is started
  ansible.windows.win_service:
    name: "{{ 'MSSQLSERVER' if mssql_instance_name == 'MSSQLSERVER' else 'MSSQL$' + mssql_instance_name }}"
    state: started
    start_mode: auto

- name: Ensure SQL Server Agent is started
  ansible.windows.win_service:
    name: "{{ 'SQLSERVERAGENT' if mssql_instance_name == 'MSSQLSERVER' else 'SQLAgent$' + mssql_instance_name }}"
    state: started
    start_mode: auto

- name: Open SQL Server firewall port
  community.windows.win_firewall_rule:
    name: "SQL Server ({{ mssql_instance_name }}) TCP {{ mssql_tcp_port }}"
    localport: "{{ mssql_tcp_port }}"
    action: allow
    direction: in
    protocol: tcp
    state: present
    enabled: true

- name: Configure SQL Server memory limits
  ansible.windows.win_powershell:
    script: |
      $ErrorActionPreference = 'Stop'

      $sqlcmd = Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Filter 'sqlcmd.exe' `
                  -Recurse -ErrorAction SilentlyContinue |
                Where-Object { $_.FullName -notlike '*\x86\*' } |
                Sort-Object LastWriteTime -Descending |
                Select-Object -First 1 -ExpandProperty FullName
      if (-not $sqlcmd) { throw 'sqlcmd.exe not found under C:\Program Files\Microsoft SQL Server' }

      $instance = if ('{{ mssql_instance_name }}' -eq 'MSSQLSERVER') { '.' } `
                  else { '.\{{ mssql_instance_name }}' }

      $queries = @(
        "EXEC sp_configure 'show advanced options', 1; RECONFIGURE;",
        "EXEC sp_configure 'max server memory (MB)', {{ mssql_max_memory_mb }}; RECONFIGURE;",
        "EXEC sp_configure 'min server memory (MB)', {{ mssql_min_memory_mb }}; RECONFIGURE;"
      )
      foreach ($q in $queries) {
        & $sqlcmd -S $instance -E -Q $q | Out-Null
      }
      $Ansible.Result = "max={{ mssql_max_memory_mb }} MB, min={{ mssql_min_memory_mb }} MB"