- name: Update apt package index
  apt:
    update_cache: yes

- name: Install necessary dependencies
  apt:
    name:
      - wget
      - ca-certificates
    state: present

- name: Add PostgreSQL GPG key
  apt_key:
    url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
    state: present

- name: Add PostgreSQL APT repository
  apt_repository:
    repo: "deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main"
    state: present
    update_cache: yes

- name: Install PostgreSQL 16 and its dependencies
  apt:
    name:
      - postgresql-16
      - postgresql-client-16
      - postgresql-contrib
      - python3-psycopg2
    state: present

- name: Install python3-psycopg2
  apt:
    name:
      - python3-psycopg2
    state: present

- name: Ensure PostgreSQL is running and enabled on boot
  systemd:
    name: postgresql
    state: started
    enabled: yes

- name: Copy PostgreSQL configuration
  template:
    src: postgresql.conf.j2
    dest: /etc/postgresql/16/main/postgresql.conf
  notify:
    - Restart PostgreSQL 16

- name: Create a database user
  become_user: postgres
  postgresql_user:
    name: testuser
    password: testpassword
    state: present

- name: Create a database
  become_user: postgres
  postgresql_db:
    name: testdb
    owner: testuser
    state: present

- name: Grant privileges to the user on the database
  become_user: postgres
  postgresql_privs:
    database: testdb
    roles: testuser
    objs: ALL_IN_SCHEMA
    privs: "ALL"
    state: present