---
- name: Native PostgreSQL delivery
  hosts: "{{ target_hosts }}"
  become: true
  gather_facts: true
  any_errors_fatal: true
  vars:
    postgresql_version_value: "{{ postgresql_version | default('16') }}"
    postgresql_instance_plan: "{{ postgresql_instances[inventory_hostname] | default({}) }}"
    postgresql_instance_id: "{{ postgresql_instance_plan.instance_id | default(instance_name) }}"
    postgresql_role: "{{ postgresql_instance_plan.role | default('standalone') }}"
    postgresql_data_root: "{{ data_root | default('/data/postgresql') }}"
    postgresql_data_dir: "{{ postgresql_instance_plan.data_dir | default(postgresql_data_root ~ '/' ~ postgresql_instance_id ~ '/data') }}"
    postgresql_conf_dir: "{{ postgresql_instance_plan.config_dir | default(postgresql_data_root ~ '/' ~ postgresql_instance_id ~ '/conf') }}"
    postgresql_log_dir: "{{ postgresql_instance_plan.log_dir | default(postgresql_data_root ~ '/' ~ postgresql_instance_id ~ '/log') }}"
    postgresql_run_dir: "/run/postgresql-xinfra/{{ postgresql_instance_id }}"
    postgresql_config_file: "{{ postgresql_conf_dir }}/postgresql.conf"
    postgresql_hba_file: "{{ postgresql_conf_dir }}/pg_hba.conf"
    postgresql_port_value: "{{ postgresql_instance_plan.port | default(postgresql_port | default(15432)) | int }}"
    postgresql_max_connections: "{{ max_connections | default(200, true) | int }}"
    postgresql_shared_buffers: "{{ ((memory_mb | default(4096) | int) * 25 / 100) | int }}MB"
    postgresql_effective_cache_size: "{{ ((memory_mb | default(4096) | int) * 70 / 100) | int }}MB"
    postgresql_max_wal_senders: "{{ (replica_count | default(0) | int) + 4 }}"
    postgresql_max_replication_slots: "{{ (replica_count | default(0) | int) + 2 }}"
    postgresql_replication_user: "{{ lookup('ansible.builtin.env', 'XINFRA_POSTGRES_REPLICATION_USER') | default('xinfra_replication', true) }}"
    postgresql_replication_password: "{{ lookup('ansible.builtin.env', 'XINFRA_POSTGRES_REPLICATION_PASSWORD') }}"
    postgresql_admin_password: "{{ lookup('ansible.builtin.env', 'XINFRA_POSTGRES_ADMIN_PASSWORD') }}"
    postgresql_primary_host: "{{ hostvars[ansible_play_hosts_all[0]].ansible_host | default(ansible_play_hosts_all[0]) }}"
    postgresql_primary_port: "{{ (postgresql_instances[ansible_play_hosts_all[0]].port | default(15432)) | int }}"
    postgresql_replication_slot: "{{ postgresql_instance_plan.replication_slot | default('xinfra_' ~ postgresql_instance_id | replace('-', '_')) }}"

  pre_tasks:
    - name: Validate PostgreSQL delivery parameters
      ansible.builtin.assert:
        that:
          - postgresql_version_value in ['15', '16']
          - topology | default('standalone') in ['standalone', 'primary_replica']
          - ansible_distribution == 'Ubuntu'
          - ansible_distribution_version is version('24.04', '>=')
          - ansible_architecture in ['x86_64', 'aarch64']
          - postgresql_instance_id is match('^[a-z0-9][a-z0-9-]{0,62}$')
          - (postgresql_port_value | int) >= 15432 and (postgresql_port_value | int) <= 15999
          - (memory_mb | default(4096) | int) >= 2048
          - (storage_gb | default(50) | int) >= 20
          - (postgresql_max_connections | int) >= 1
          - postgresql_admin_password | length >= 16
          - (topology | default('standalone') == 'standalone') or (postgresql_replication_password | length >= 16)
        fail_msg: "PostgreSQL parameters are outside the supported target-state whitelist"
      no_log: true

    - name: Check PostgreSQL port is free
      ansible.builtin.shell:
        cmd: |
          if ss -lntH 'sport = :{{ postgresql_port_value }}' | grep -q .; then
            systemctl is-active --quiet 'postgresql-xinfra@{{ postgresql_instance_id }}.service'
          fi
      register: postgresql_port_check
      failed_when: postgresql_port_check.rc != 0
      changed_when: false

    - name: Check target memory and data-root capacity
      ansible.builtin.assert:
        that:
          - (ansible_memtotal_mb | int) >= (memory_mb | default(4096) | int)
        fail_msg: "Target host does not have enough memory or storage for the PostgreSQL instance"

    - name: Ensure PostgreSQL data root exists
      ansible.builtin.file:
        path: "{{ postgresql_data_root }}"
        state: directory
        owner: postgres
        group: postgres
        mode: '0750'

    - name: Check PostgreSQL data filesystem capacity
      ansible.builtin.shell:
        cmd: "df -P -B1 {{ postgresql_data_root | quote }} | awk 'NR==2 {print $4}'"
      register: postgresql_root_available_bytes
      changed_when: false
      failed_when: >-
        postgresql_root_available_bytes.rc != 0 or
        (postgresql_root_available_bytes.stdout | trim | int) <
        (storage_gb | default(50) | int) * 1073741824

    - name: Install PostgreSQL packages
      ansible.builtin.apt:
        name:
          - "postgresql-{{ postgresql_version_value }}"
          - postgresql-client
        state: present
        update_cache: true
        cache_valid_time: 3600

    - name: Stop distribution-managed cluster
      ansible.builtin.systemd_service:
        name: "postgresql@{{ postgresql_version_value }}-main.service"
        state: stopped
        enabled: false
      failed_when: false

    - name: Create isolated PostgreSQL directories
      ansible.builtin.file:
        path: "{{ item }}"
        state: directory
        owner: postgres
        group: postgres
        mode: '0700'
      loop:
        - "{{ postgresql_data_root }}/{{ postgresql_instance_id }}"
        - "{{ postgresql_data_dir }}"
        - "{{ postgresql_conf_dir }}"
        - "{{ postgresql_log_dir }}"

    - name: Initialize primary or standalone data directory
      ansible.builtin.command:
        cmd: "/usr/lib/postgresql/{{ postgresql_version_value }}/bin/initdb -D {{ postgresql_data_dir }}"
        creates: "{{ postgresql_data_dir }}/PG_VERSION"
      become_user: postgres
      when: postgresql_role != 'replica'

    - name: Render PostgreSQL configuration
      ansible.builtin.template:
        src: templates/postgresql-instance.conf.j2
        dest: "{{ postgresql_config_file }}"
        owner: postgres
        group: postgres
        mode: '0600'
      no_log: "{{ postgresql_role == 'replica' }}"

    - name: Render pg_hba.conf
      ansible.builtin.copy:
        dest: "{{ postgresql_hba_file }}"
        owner: postgres
        group: postgres
        mode: '0600'
        content: |
          local all postgres peer
          local all all peer
          host all all 127.0.0.1/32 scram-sha-256
          {% for host in ansible_play_hosts_all %}
          {% set host_address = hostvars[host].ansible_host | default(host) %}
          host all all {{ host_address }}{% if host_address is match('^[0-9.]+$') %}/32{% endif %} scram-sha-256
          host replication {{ postgresql_replication_user }} {{ host_address }}{% if host_address is match('^[0-9.]+$') %}/32{% endif %} scram-sha-256
          {% endfor %}

    - name: Validate primary or standalone PostgreSQL configuration syntax
      ansible.builtin.command:
        argv:
          - "/usr/lib/postgresql/{{ postgresql_version_value }}/bin/postgres"
          - "-D"
          - "{{ postgresql_data_dir }}"
          - "-C"
          - "port"
          - "-c"
          - "config_file={{ postgresql_config_file }}"
          - "-c"
          - "hba_file={{ postgresql_hba_file }}"
      become_user: postgres
      changed_when: false
      when: postgresql_role != 'replica'

    - name: Install PostgreSQL systemd template
      ansible.builtin.copy:
        src: files/postgresql-xinfra@.service
        dest: /etc/systemd/system/postgresql-xinfra@.service
        owner: root
        group: root
        mode: '0644'
      register: postgresql_unit

    - name: Configure runtime unit environment
      ansible.builtin.file:
        dest: "/etc/systemd/system/postgresql-xinfra@{{ postgresql_instance_id }}.service.d"
        state: directory
        mode: '0755'

    - name: Write runtime unit environment
      ansible.builtin.copy:
        dest: "/etc/systemd/system/postgresql-xinfra@{{ postgresql_instance_id }}.service.d/version.conf"
        mode: '0644'
        content: |
          [Service]
          Environment=POSTGRESQL_VERSION={{ postgresql_version_value }}
          Environment=POSTGRESQL_DATA_DIR={{ postgresql_data_dir }}
          Environment=POSTGRESQL_CONFIG_FILE={{ postgresql_config_file }}
          Environment=POSTGRESQL_HBA_FILE={{ postgresql_hba_file }}

    - name: Reload systemd
      ansible.builtin.systemd_service:
        daemon_reload: true

    - name: Start PostgreSQL delivery instance
      ansible.builtin.systemd_service:
        name: "postgresql-xinfra@{{ postgresql_instance_id }}.service"
        state: started
        enabled: true
      when: postgresql_role != 'replica'

    - name: Wait for primary or standalone PostgreSQL TCP port
      ansible.builtin.wait_for:
        host: "{{ ansible_host | default(inventory_hostname) }}"
        port: "{{ postgresql_port_value }}"
        timeout: 60
      when: postgresql_role != 'replica'

    - name: Configure PostgreSQL administrator and replication roles
      ansible.builtin.shell:
        cmd: |
          /usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -v ON_ERROR_STOP=1 <<'SQL'
          ALTER ROLE postgres PASSWORD '{{ postgresql_admin_password | replace("'", "''") }}';
          {% if topology | default('standalone') == 'primary_replica' %}
          DO $do$BEGIN
            IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '{{ postgresql_replication_user }}') THEN
              CREATE ROLE {{ postgresql_replication_user }} WITH REPLICATION LOGIN PASSWORD '{{ postgresql_replication_password | replace("'", "''") }}';
            ELSE
              ALTER ROLE {{ postgresql_replication_user }} WITH REPLICATION LOGIN PASSWORD '{{ postgresql_replication_password | replace("'", "''") }}';
            END IF;
          END$do$;
          {% endif %}
          SQL
      become_user: postgres
      no_log: true
      when: postgresql_role in ['primary', 'standalone']

    - name: Create physical replication slots on primary
      ansible.builtin.shell:
        cmd: |
          /usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -v ON_ERROR_STOP=1 <<'SQL'
          {% for replica_index in range(1, ansible_play_hosts_all | length) %}
          SELECT pg_create_physical_replication_slot('xinfra_{{ cluster_name | replace("-", "_") }}_replica_{{ replica_index }}')
          WHERE NOT EXISTS (SELECT FROM pg_replication_slots WHERE slot_name = 'xinfra_{{ cluster_name | replace("-", "_") }}_replica_{{ replica_index }}');
          {% endfor %}
          SQL
      become_user: postgres
      changed_when: false
      when: postgresql_role == 'primary' and topology | default('standalone') == 'primary_replica'

    - name: Initialize replica from primary
      ansible.builtin.shell:
        cmd: >-
          PGPASSWORD={{ postgresql_replication_password | quote }}
          /usr/lib/postgresql/{{ postgresql_version_value }}/bin/pg_basebackup
          -h {{ postgresql_primary_host }} -p {{ postgresql_primary_port }}
          -U {{ postgresql_replication_user }} -D {{ postgresql_data_dir }}
          -Fp -Xs -P -R -S {{ postgresql_replication_slot }}
      args:
        creates: "{{ postgresql_data_dir }}/PG_VERSION"
      become_user: postgres
      no_log: true
      when: postgresql_role == 'replica'

    - name: Validate replica PostgreSQL configuration syntax
      ansible.builtin.command:
        argv:
          - "/usr/lib/postgresql/{{ postgresql_version_value }}/bin/postgres"
          - "-D"
          - "{{ postgresql_data_dir }}"
          - "-C"
          - "port"
          - "-c"
          - "config_file={{ postgresql_config_file }}"
          - "-c"
          - "hba_file={{ postgresql_hba_file }}"
      become_user: postgres
      changed_when: false
      no_log: true
      when: postgresql_role == 'replica'

    - name: Start PostgreSQL replica instance
      ansible.builtin.systemd_service:
        name: "postgresql-xinfra@{{ postgresql_instance_id }}.service"
        state: started
        enabled: true
      when: postgresql_role == 'replica'

    - name: Wait for PostgreSQL replica TCP port
      ansible.builtin.wait_for:
        host: "{{ ansible_host | default(inventory_hostname) }}"
        port: "{{ postgresql_port_value }}"
        timeout: 60
      when: postgresql_role == 'replica'

    - name: Verify PostgreSQL readiness
      ansible.builtin.command:
        cmd: "/usr/lib/postgresql/{{ postgresql_version_value }}/bin/pg_isready -h 127.0.0.1 -p {{ postgresql_port_value }}"
      changed_when: false

    - name: Verify primary or standalone role
      ansible.builtin.shell:
        cmd: >-
          test "$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc 'SELECT pg_is_in_recovery()')" = "f"
      become_user: postgres
      changed_when: false
      when: postgresql_role in ['primary', 'standalone']

    - name: Verify replica recovery role
      ansible.builtin.shell:
        cmd: >-
          test "$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc 'SELECT pg_is_in_recovery()')" = "t"
      become_user: postgres
      changed_when: false
      when: postgresql_role == 'replica'

    - name: Verify primary streaming replicas and replication slots
      ansible.builtin.shell:
        cmd: |
          streaming=$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc "SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'")
          slots=$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc "SELECT count(*) FROM pg_replication_slots WHERE slot_type = 'physical'")
          test "$streaming" -ge "{{ replica_count | default(0) | int }}"
          test "$slots" -ge "{{ replica_count | default(0) | int }}"
      become_user: postgres
      register: postgresql_replication_health
      until: postgresql_replication_health.rc == 0
      retries: 30
      delay: 2
      changed_when: false
      when: postgresql_role == 'primary' and topology | default('standalone') == 'primary_replica'

    - name: Verify delivered version, port and data directory
      ansible.builtin.shell:
        cmd: |
          set -euo pipefail
          actual_version=$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc "SHOW server_version")
          actual_port=$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc "SHOW port")
          actual_data=$(/usr/bin/psql -h {{ postgresql_run_dir }} -p {{ postgresql_port_value }} -U postgres -d postgres -Atc "SHOW data_directory")
          test "${actual_version%%.*}" = "{{ postgresql_version_value }}"
          test "$actual_port" = "{{ postgresql_port_value }}"
          test "$actual_data" = "{{ postgresql_data_dir }}"
        executable: /bin/bash
      become_user: postgres
      changed_when: false