-- Insert or update on conflict (upsert)
INSERT INTO user_stats (user_id, login_count, last_seen)
VALUES ($1, 1, now())
ON CONFLICT (user_id) DO UPDATE
  SET login_count = user_stats.login_count + 1,
      last_seen   = EXCLUDED.last_seen;  -- EXCLUDED = the rejected row

-- Insert and ignore duplicates
INSERT INTO events (id, payload)
VALUES ($1, $2)
ON CONFLICT (id) DO NOTHING;

-- Returning the affected rows (useful for getting auto-generated IDs)
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING id, created_at;

#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>

/* Compile: gcc db.c -lpq -o db
   Requires: libpq-dev package (apt install libpq-dev)  */

PGconn *db_connect(const char *connstr) {
    PGconn *conn = PQconnectdb(connstr);
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection failed: %s\n", PQerrorMessage(conn));
        PQfinish(conn);
        return NULL;
    }
    return conn;
}

int main(void) {
    /* Connection string: can also use env vars PGHOST, PGPORT, PGUSER etc */
    PGconn *conn = db_connect(
        "host=localhost port=5432 dbname=myapp user=myuser password=secret"
    );
    if (!conn) return 1;

    printf("Connected to PostgreSQL %s\n", PQparameterStatus(conn, "server_version"));
    PQfinish(conn);
    return 0;
}

/* Always use prepared statements — never string-concatenate user input */

int get_user_by_id(PGconn *conn, int user_id) {
    /* Prepare once, execute many times */
    PGresult *prep = PQprepare(conn,
        "get_user",                         /* statement name */
        "SELECT id, name, email FROM users WHERE id = $1",
        1,                                   /* number of params */
        NULL                                 /* param types (NULL = infer) */
    );
    if (PQresultStatus(prep) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Prepare failed: %s\n", PQresultErrorMessage(prep));
        PQclear(prep);
        return -1;
    }
    PQclear(prep);

    /* Execute: pass parameter as string (libpq converts) */
    char id_str[16];
    snprintf(id_str, sizeof(id_str), "%d", user_id);
    const char *params[] = { id_str };

    PGresult *res = PQexecPrepared(conn,
        "get_user",   /* statement name */
        1,             /* nParams */
        params,        /* paramValues */
        NULL,          /* paramLengths (NULL = text mode, use strlen) */
        NULL,          /* paramFormats (NULL = all text) */
        0              /* resultFormat: 0=text, 1=binary */
    );

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Query failed: %s\n", PQresultErrorMessage(res));
        PQclear(res);
        return -1;
    }

    int rows = PQntuples(res);
    int cols = PQnfields(res);
    printf("Got %d rows, %d cols\n", rows, cols);

    for (int r = 0; r < rows; r++) {
        for (int c = 0; c < cols; c++) {
            printf("  %s = %s\n",
                   PQfname(res, c),
                   PQgetisnull(res, r, c) ? "(null)" : PQgetvalue(res, r, c));
        }
    }

    PQclear(res);
    return rows;
}

int transfer_funds(PGconn *conn, int from_id, int to_id, int amount_cents) {
    /* BEGIN */
    PGresult *r = PQexec(conn, "BEGIN");
    if (PQresultStatus(r) != PGRES_COMMAND_OK) { PQclear(r); return -1; }
    PQclear(r);

    /* Debit — lock the row first with FOR UPDATE */
    char from_str[16], to_str[16], amt_str[16];
    snprintf(from_str, sizeof(from_str), "%d", from_id);
    snprintf(to_str,   sizeof(to_str),   "%d", to_id);
    snprintf(amt_str,  sizeof(amt_str),  "%d", amount_cents);

    const char *debit_params[] = { amt_str, from_str };
    r = PQexecParams(conn,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",
        2, NULL, debit_params, NULL, NULL, 0);

    if (PQresultStatus(r) != PGRES_COMMAND_OK ||
        strcmp(PQcmdTuples(r), "1") != 0) {  /* 0 rows = insufficient balance */
        PQclear(r);
        PQexec(conn, "ROLLBACK");
        return -1;
    }
    PQclear(r);

    /* Credit */
    const char *credit_params[] = { amt_str, to_str };
    r = PQexecParams(conn,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        2, NULL, credit_params, NULL, NULL, 0);

    if (PQresultStatus(r) != PGRES_COMMAND_OK) {
        PQclear(r);
        PQexec(conn, "ROLLBACK");
        return -1;
    }
    PQclear(r);

    /* COMMIT */
    r = PQexec(conn, "COMMIT");
    int ok = PQresultStatus(r) == PGRES_COMMAND_OK;
    PQclear(r);
    return ok ? 0 : -1;
}

/* Production uses PgBouncer. This illustrates the concept. */

#define POOL_SIZE 8

typedef struct {
    PGconn  *conn;
    int      in_use;
    pthread_mutex_t lock;
} conn_slot_t;

static conn_slot_t pool[POOL_SIZE];
static pthread_mutex_t pool_lock = PTHREAD_MUTEX_INITIALIZER;

void pool_init(const char *connstr) {
    for (int i = 0; i < POOL_SIZE; i++) {
        pool[i].conn   = PQconnectdb(connstr);
        pool[i].in_use = 0;
        pthread_mutex_init(&pool[i].lock, NULL);
    }
}

PGconn *pool_acquire(void) {
    pthread_mutex_lock(&pool_lock);
    for (int i = 0; i < POOL_SIZE; i++) {
        if (!pool[i].in_use && PQstatus(pool[i].conn) == CONNECTION_OK) {
            pool[i].in_use = 1;
            pthread_mutex_unlock(&pool_lock);
            return pool[i].conn;
        }
    }
    pthread_mutex_unlock(&pool_lock);
    return NULL;  /* pool exhausted */
}

void pool_release(PGconn *conn) {
    pthread_mutex_lock(&pool_lock);
    for (int i = 0; i < POOL_SIZE; i++) {
        if (pool[i].conn == conn) {
            pool[i].in_use = 0;
            break;
        }
    }
    pthread_mutex_unlock(&pool_lock);
}