# Pola panggilan berulang dan executemany di psycopg 3.
import psycopg

conn = psycopg.connect()
cur = conn.cursor()

cur.execute("""
    CREATE TABLE item_pesanan (
        id      integer PRIMARY KEY,
        pesanan integer NOT NULL,
        produk  text NOT NULL,
        jumlah  integer NOT NULL
    )
""")
conn.commit()

data = [
    (1, 101, "Beras 5 kg", 2),
    (2, 101, "Gula 1 kg", 1),
    (3, 101, "Kopi sachet", 12),
    (4, 102, "Teh celup", 3),
    (5, 102, "Minyak goreng 1 L", 1),
    (6, 102, "Sabun mandi", 4),
]

# Teks INSERT yang sama dipanggil enam kali dengan parameter berbeda.
# Mulai panggilan berikutnya psycopg mengirimkannya sebagai prepared
# statement: parse sudah selesai, pengiriman berikutnya hanya parameter.
for baris in data:
    cur.execute(
        "INSERT INTO item_pesanan (id, pesanan, produk, jumlah) "
        "VALUES (%s, %s, %s, %s)",
        baris,
    )
conn.commit()

cur.execute("SELECT count(*) FROM item_pesanan")
print("baris setelah 6 kali execute:", cur.fetchone()[0])

cur.execute("SELECT name, statement FROM pg_prepared_statements ORDER BY name")
print("statement yang tersimpan di server:")
for nama, teks in cur.fetchall():
    print(" ", nama, "|", teks)

# executemany mengirim banyak baris lewat satu panggilan (pipeline).
cur.executemany(
    "INSERT INTO item_pesanan (id, pesanan, produk, jumlah) "
    "VALUES (%s, %s, %s, %s)",
    [
        (7, 102, "Sampo sachet", 10),
        (8, 102, "Deterjen 800 g", 1),
    ],
)
conn.commit()

cur.execute(
    "SELECT pesanan, count(*) AS item, sum(jumlah) AS unit "
    "FROM item_pesanan GROUP BY pesanan ORDER BY pesanan"
)
for pesanan, item, unit in cur.fetchall():
    print(f"pesanan {pesanan}: {item} item, total {unit} unit")

cur.close()
conn.close()