import os
from concurrent.futures import ThreadPoolExecutor

import psycopg
from psycopg_pool import ConnectionPool

# Parameter koneksi mengikuti variabel environment PG* --
# konvensi libpq yang sama dengan psql.
KONEKSI = dict(
    host=os.environ.get("PGHOST", "localhost"),
    port=os.environ.get("PGPORT", "5432"),
    user=os.environ.get("PGUSER", "postgres"),
    dbname=os.environ.get("PGDATABASE", "toko_db"),
)

# Bahan latihan: tabel pencatat kunjungan halaman Toko Maju Jaya.
with psycopg.connect(**KONEKSI) as conn:
    conn.execute("DROP TABLE IF EXISTS kunjungan")
    conn.execute(
        "CREATE TABLE kunjungan ("
        " id integer PRIMARY KEY,"
        " halaman text NOT NULL)"
    )

# Pool dengan tepat satu koneksi: dua pinjaman berurutan ternyata
# mendarat di sesi server yang sama.
with ConnectionPool(kwargs=KONEKSI, min_size=1, max_size=1, open=True) as pool:
    with pool.connection() as conn:
        conn.execute("SET app.nama = 'pinjaman-pertama'")
        conn.execute(
            "INSERT INTO kunjungan (id, halaman) VALUES (1, '/beranda')"
        )
    with pool.connection() as conn:
        nilai = conn.execute(
            "SELECT current_setting('app.nama') AS nilai"
        ).fetchone()[0]
        print("Pinjaman 2 melihat app.nama =", nilai)

# Pool dengan dua koneksi melayani empat pekerja serentak;
# pekerja yang datang saat kedua koneksi dipakai menunggu di
# antrean pool, bukan membuka koneksi baru.
with ConnectionPool(kwargs=KONEKSI, min_size=2, max_size=2, open=True) as pool:
    def catat(nomor):
        with pool.connection() as conn:
            conn.execute(
                "INSERT INTO kunjungan (id, halaman) VALUES (%s, %s)",
                (nomor, f"/produk/{nomor}"),
            )

    with ThreadPoolExecutor(max_workers=4) as eksekutor:
        list(eksekutor.map(catat, [2, 3, 4, 5]))

    with pool.connection() as conn:
        total = conn.execute("SELECT count(*) FROM kunjungan").fetchone()[0]
        print("Kunjungan tercatat:", total)

    statistik = pool.get_stats()
    print(
        "Ukuran pool:",
        statistik["pool_size"],
        "dari maksimum",
        statistik["pool_max"],
    )