let conninfo =
  match Sys.getenv_opt "REPODB_PG_CONNINFO" with
  | Some s -> s
  | None ->
      "host=localhost port=5432 dbname=repodb_test user=repodb password=repodb"

let setup () =
  match Repodb_postgresql.connect conninfo with
  | Error e -> failwith (Repodb_postgresql.error_message e)
  | Ok conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS users" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS nums" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS flags" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS measurements"
          ~params:[||]
      in
      conn

let teardown conn =
  let _ =
    Repodb_postgresql.exec conn "DROP TABLE IF EXISTS users" ~params:[||]
  in
  let _ =
    Repodb_postgresql.exec conn "DROP TABLE IF EXISTS nums" ~params:[||]
  in
  let _ =
    Repodb_postgresql.exec conn "DROP TABLE IF EXISTS flags" ~params:[||]
  in
  let _ =
    Repodb_postgresql.exec conn "DROP TABLE IF EXISTS measurements" ~params:[||]
  in
  Repodb_postgresql.close conn

let with_db f () =
  let conn = setup () in
  Fun.protect ~finally:(fun () -> teardown conn) (fun () -> f conn)

let test_connect =
  with_db (fun conn ->
      let result = Repodb_postgresql.exec conn "SELECT 1" ~params:[||] in
      match result with
      | Ok () -> ()
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e))

let test_create_table =
  with_db (fun conn ->
      let sql =
        "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email \
         TEXT, age INTEGER)"
      in
      match Repodb_postgresql.exec conn sql ~params:[||] with
      | Ok () -> ()
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e))

let test_insert_and_query =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, \
           email TEXT, age INTEGER)"
          ~params:[||]
      in
      let insert =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, email, age) VALUES ($1, $2, $3)"
          ~params:
            [|
              Repodb.Driver.Value.text "Alice";
              Repodb.Driver.Value.text "alice@example.com";
              Repodb.Driver.Value.int 30;
            |]
      in
      (match insert with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () -> ());
      match Repodb_postgresql.query conn "SELECT * FROM users" ~params:[||] with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "one row" 1 (List.length rows);
          let row = List.hd rows in
          Alcotest.(check string) "name" "Alice" (Repodb.Driver.row_text row 1))

let test_query_one =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "Bob" |]
      in
      match
        Repodb_postgresql.query_one conn "SELECT * FROM users WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "Bob" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          Alcotest.(check string) "name" "Bob" (Repodb.Driver.row_text row 1))

let test_query_one_not_found =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      match
        Repodb_postgresql.query_one conn "SELECT * FROM users WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "Nobody" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> ()
      | Ok (Some _) -> Alcotest.fail "expected no row")

let test_update =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, age INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, age) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Carol"; Repodb.Driver.Value.int 25 |]
      in
      let _ =
        Repodb_postgresql.exec conn "UPDATE users SET age = $1 WHERE name = $2"
          ~params:
            [| Repodb.Driver.Value.int 26; Repodb.Driver.Value.text "Carol" |]
      in
      match
        Repodb_postgresql.query_one conn "SELECT age FROM users WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "Carol" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          Alcotest.(check int) "age" 26 (Repodb.Driver.row_int row 0))

let test_delete =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "Dave" |]
      in
      let _ =
        Repodb_postgresql.exec conn "DELETE FROM users WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "Dave" |]
      in
      match Repodb_postgresql.query conn "SELECT * FROM users" ~params:[||] with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> Alcotest.(check int) "no rows" 0 (List.length rows))

let test_transaction_commit =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let result =
        Repodb_postgresql.transaction conn (fun conn ->
            let _ =
              Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
                ~params:[| Repodb.Driver.Value.text "Eve" |]
            in
            Ok "done")
      in
      (match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok _ -> ());
      match Repodb_postgresql.query conn "SELECT * FROM users" ~params:[||] with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> Alcotest.(check int) "one row" 1 (List.length rows))

let test_transaction_rollback =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let result =
        Repodb_postgresql.transaction conn (fun conn ->
            let _ =
              Repodb_postgresql.exec conn
                "INSERT INTO users (id, name) VALUES ($1, $2)"
                ~params:
                  [|
                    Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Frank";
                  |]
            in
            Repodb_postgresql.exec conn
              "INSERT INTO users (id, name) VALUES ($1, $2)"
              ~params:
                [|
                  Repodb.Driver.Value.int 1;
                  Repodb.Driver.Value.text "Duplicate";
                |])
      in
      (match result with
      | Ok _ -> Alcotest.fail "expected error from duplicate key"
      | Error _ -> ());
      match Repodb_postgresql.query conn "SELECT * FROM users" ~params:[||] with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "no rows (rolled back)" 0 (List.length rows))

let test_query_fold =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 5 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      match
        Repodb_postgresql.query_fold conn "SELECT val FROM nums" ~params:[||]
          ~init:0 ~f:(fun acc row -> acc + Repodb.Driver.row_int row 0)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok sum -> Alcotest.(check int) "sum 1..5" 15 sum)

let test_query_iter =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 3 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let count = ref 0 in
      match
        Repodb_postgresql.query_iter conn "SELECT val FROM nums" ~params:[||]
          ~f:(fun _ -> incr count)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () -> Alcotest.(check int) "iterated 3" 3 !count)

let test_returning =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      match
        Repodb_postgresql.query_one conn
          "INSERT INTO users (name) VALUES ($1) RETURNING id"
          ~params:[| Repodb.Driver.Value.text "Grace" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          let id = Repodb.Driver.row_int row 0 in
          Alcotest.(check bool) "id > 0" true (id > 0))

let test_null_handling =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, email TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, email) VALUES ($1, NULL)"
          ~params:[| Repodb.Driver.Value.text "Henry" |]
      in
      match
        Repodb_postgresql.query_one conn
          "SELECT email FROM users WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "Henry" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          Alcotest.(check bool)
            "email is null" true
            (Repodb.Driver.row_is_null row 0))

let test_bool_values =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE flags (id SERIAL PRIMARY KEY, active BOOLEAN)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO flags (active) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.bool true |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO flags (active) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.bool false |]
      in
      match
        Repodb_postgresql.query conn "SELECT active FROM flags ORDER BY id"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two rows" 2 (List.length rows);
          Alcotest.(check bool)
            "first is true" true
            (Repodb.Driver.row_bool (List.nth rows 0) 0);
          Alcotest.(check bool)
            "second is false" false
            (Repodb.Driver.row_bool (List.nth rows 1) 0))

let test_float_values =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE measurements (id SERIAL PRIMARY KEY, value DOUBLE \
           PRECISION)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO measurements (value) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 3.14159 |]
      in
      match
        Repodb_postgresql.query_one conn "SELECT value FROM measurements"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          let v = Repodb.Driver.row_float row 0 in
          Alcotest.(check bool)
            "close to pi" true
            (abs_float (v -. 3.14159) < 0.0001))

let test_with_connection () =
  Repodb_postgresql.with_connection conninfo (fun conn ->
      match Repodb_postgresql.exec conn "SELECT 1" ~params:[||] with
      | Ok () -> Ok ()
      | Error e -> Error e)
  |> function
  | Ok () -> ()
  | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)

let test_placeholder () =
  Alcotest.(check string) "$1" "$1" (Repodb_postgresql.placeholder 1);
  Alcotest.(check string) "$2" "$2" (Repodb_postgresql.placeholder 2);
  Alcotest.(check string) "$10" "$10" (Repodb_postgresql.placeholder 10)

let test_create_index =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT NOT NULL)"
          ~params:[||]
      in
      match
        Repodb_postgresql.exec conn
          "CREATE INDEX idx_users_email ON users (email)" ~params:[||]
      with
      | Ok () -> ()
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e))

let test_create_unique_index =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE UNIQUE INDEX idx_users_email_unique ON users (email)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "test@example.com" |]
      in
      match
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "test@example.com" |]
      with
      | Ok () -> Alcotest.fail "expected unique constraint violation"
      | Error _ -> ())

let test_composite_index =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER, status \
           TEXT, created_at TIMESTAMP)"
          ~params:[||]
      in
      match
        Repodb_postgresql.exec conn
          "CREATE INDEX idx_orders_user_status ON orders (user_id, status)"
          ~params:[||]
      with
      | Ok () -> ()
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e))

let test_foreign_key =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS posts" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL \
           REFERENCES users(id), title TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      (match
         Repodb_postgresql.exec conn
           "INSERT INTO posts (user_id, title) VALUES ($1, $2)"
           ~params:
             [|
               Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "First Post";
             |]
       with
      | Ok () -> ()
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e));
      let _ = Repodb_postgresql.exec conn "DROP TABLE posts" ~params:[||] in
      ())

let test_foreign_key_violation =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS posts" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL \
           REFERENCES users(id), title TEXT)"
          ~params:[||]
      in
      let result =
        Repodb_postgresql.exec conn
          "INSERT INTO posts (user_id, title) VALUES ($1, $2)"
          ~params:
            [|
              Repodb.Driver.Value.int 999;
              Repodb.Driver.Value.text "Orphan Post";
            |]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE posts" ~params:[||] in
      match result with
      | Ok () -> Alcotest.fail "expected foreign key violation"
      | Error _ -> ())

let test_cascade_delete =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS posts" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL \
           REFERENCES users(id) ON DELETE CASCADE, title TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO posts (user_id, title) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Post 1" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO posts (user_id, title) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Post 2" |]
      in
      let _ =
        Repodb_postgresql.exec conn "DELETE FROM users WHERE id = $1"
          ~params:[| Repodb.Driver.Value.int 1 |]
      in
      let result =
        Repodb_postgresql.query conn "SELECT * FROM posts" ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE posts" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> Alcotest.(check int) "posts cascaded" 0 (List.length rows))

let test_inner_join =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS posts" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER, title \
           TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 2; Repodb.Driver.Value.text "Bob" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO posts (user_id, title) VALUES ($1, $2)"
          ~params:
            [|
              Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice Post";
            |]
      in
      let result =
        Repodb_postgresql.query conn
          "SELECT users.name, posts.title FROM users INNER JOIN posts ON \
           users.id = posts.user_id"
          ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE posts" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "one joined row" 1 (List.length rows);
          let row = List.hd rows in
          Alcotest.(check string) "name" "Alice" (Repodb.Driver.row_text row 0);
          Alcotest.(check string)
            "title" "Alice Post"
            (Repodb.Driver.row_text row 1))

let test_left_join =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS posts" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INTEGER, title \
           TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 2; Repodb.Driver.Value.text "Bob" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO posts (user_id, title) VALUES ($1, $2)"
          ~params:
            [|
              Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice Post";
            |]
      in
      let result =
        Repodb_postgresql.query conn
          "SELECT users.name, posts.title FROM users LEFT JOIN posts ON \
           users.id = posts.user_id ORDER BY users.id"
          ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE posts" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two rows" 2 (List.length rows);
          let row2 = List.nth rows 1 in
          Alcotest.(check string) "bob" "Bob" (Repodb.Driver.row_text row2 0);
          Alcotest.(check bool)
            "bob has no post" true
            (Repodb.Driver.row_is_null row2 1))

let test_many_to_many =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS user_roles"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS roles" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE roles (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE user_roles (user_id INTEGER REFERENCES users(id), \
           role_id INTEGER REFERENCES roles(id), PRIMARY KEY (user_id, \
           role_id))"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO roles (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "admin" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO roles (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 2; Repodb.Driver.Value.text "editor" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO user_roles (user_id, role_id) VALUES ($1, $2)"
          ~params:[| Repodb.Driver.Value.int 1; Repodb.Driver.Value.int 1 |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO user_roles (user_id, role_id) VALUES ($1, $2)"
          ~params:[| Repodb.Driver.Value.int 1; Repodb.Driver.Value.int 2 |]
      in
      let result =
        Repodb_postgresql.query conn
          "SELECT r.name FROM roles r INNER JOIN user_roles ur ON r.id = \
           ur.role_id WHERE ur.user_id = $1 ORDER BY r.name"
          ~params:[| Repodb.Driver.Value.int 1 |]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE user_roles" ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE roles" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two roles" 2 (List.length rows);
          Alcotest.(check string)
            "first role" "admin"
            (Repodb.Driver.row_text (List.nth rows 0) 0);
          Alcotest.(check string)
            "second role" "editor"
            (Repodb.Driver.row_text (List.nth rows 1) 0))

let test_aggregate_count =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, department TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (department) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "engineering" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (department) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "engineering" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (department) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "sales" |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT department, COUNT(*) as cnt FROM users GROUP BY department \
           ORDER BY cnt DESC"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two departments" 2 (List.length rows);
          let row = List.hd rows in
          Alcotest.(check string)
            "top dept" "engineering"
            (Repodb.Driver.row_text row 0);
          Alcotest.(check int) "count" 2 (Repodb.Driver.row_int row 1))

let test_aggregate_sum_avg =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS orders" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE orders (id SERIAL PRIMARY KEY, amount DOUBLE PRECISION)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO orders (amount) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 100.0 |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO orders (amount) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 200.0 |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO orders (amount) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 300.0 |]
      in
      let result =
        Repodb_postgresql.query_one conn
          "SELECT SUM(amount) as total, AVG(amount) as avg FROM orders"
          ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE orders" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          Alcotest.(check bool)
            "sum" true
            (abs_float (Repodb.Driver.row_float row 0 -. 600.0) < 0.01);
          Alcotest.(check bool)
            "avg" true
            (abs_float (Repodb.Driver.row_float row 1 -. 200.0) < 0.01))

let test_subquery =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, score INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, score) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Alice"; Repodb.Driver.Value.int 90 |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, score) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Bob"; Repodb.Driver.Value.int 50 |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, score) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Carol"; Repodb.Driver.Value.int 80 |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT name FROM users WHERE score > (SELECT AVG(score) FROM users) \
           ORDER BY name"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "above avg" 2 (List.length rows);
          Alcotest.(check string)
            "first" "Alice"
            (Repodb.Driver.row_text (List.hd rows) 0);
          Alcotest.(check string)
            "second" "Carol"
            (Repodb.Driver.row_text (List.nth rows 1) 0))

let test_order_by_multiple =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, dept TEXT, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (dept, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "eng"; Repodb.Driver.Value.text "Zoe" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (dept, name) VALUES ($1, $2)"
          ~params:
            [|
              Repodb.Driver.Value.text "eng"; Repodb.Driver.Value.text "Alice";
            |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (dept, name) VALUES ($1, $2)"
          ~params:
            [|
              Repodb.Driver.Value.text "sales"; Repodb.Driver.Value.text "Bob";
            |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT dept, name FROM users ORDER BY dept ASC, name ASC" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "three rows" 3 (List.length rows);
          Alcotest.(check string)
            "first" "Alice"
            (Repodb.Driver.row_text (List.hd rows) 1);
          Alcotest.(check string)
            "second" "Zoe"
            (Repodb.Driver.row_text (List.nth rows 1) 1);
          Alcotest.(check string)
            "third" "Bob"
            (Repodb.Driver.row_text (List.nth rows 2) 1))

let test_limit_offset =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 10 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      match
        Repodb_postgresql.query conn
          "SELECT val FROM nums ORDER BY val LIMIT 3 OFFSET 2" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "3 rows" 3 (List.length rows);
          Alcotest.(check int)
            "first val" 3
            (Repodb.Driver.row_int (List.hd rows) 0);
          Alcotest.(check int)
            "last val" 5
            (Repodb.Driver.row_int (List.nth rows 2) 0))

let test_upsert =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO kv (key, value) VALUES ($1, $2) ON CONFLICT (key) DO \
           UPDATE SET value = EXCLUDED.value"
          ~params:
            [| Repodb.Driver.Value.text "foo"; Repodb.Driver.Value.text "bar" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO kv (key, value) VALUES ($1, $2) ON CONFLICT (key) DO \
           UPDATE SET value = EXCLUDED.value"
          ~params:
            [| Repodb.Driver.Value.text "foo"; Repodb.Driver.Value.text "baz" |]
      in
      match
        Repodb_postgresql.query_one conn "SELECT value FROM kv WHERE key = $1"
          ~params:[| Repodb.Driver.Value.text "foo" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          Alcotest.(check string)
            "upserted" "baz"
            (Repodb.Driver.row_text row 0))

let test_like_pattern =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "alice@gmail.com" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "bob@yahoo.com" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "carol@gmail.com" |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT email FROM users WHERE email LIKE $1"
          ~params:[| Repodb.Driver.Value.text "%@gmail.com" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> Alcotest.(check int) "gmail users" 2 (List.length rows))

let test_ilike_pattern =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "Alice" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "ALICE" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "Bob" |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT name FROM users WHERE name ILIKE $1"
          ~params:[| Repodb.Driver.Value.text "alice" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> Alcotest.(check int) "case insensitive" 2 (List.length rows))

let test_in_clause =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 2; Repodb.Driver.Value.text "Bob" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 3; Repodb.Driver.Value.text "Carol" |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT name FROM users WHERE id IN ($1, $2) ORDER BY name"
          ~params:[| Repodb.Driver.Value.int 1; Repodb.Driver.Value.int 3 |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two users" 2 (List.length rows);
          Alcotest.(check string)
            "first" "Alice"
            (Repodb.Driver.row_text (List.hd rows) 0);
          Alcotest.(check string)
            "second" "Carol"
            (Repodb.Driver.row_text (List.nth rows 1) 0))

let test_between =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS products" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE products (id SERIAL PRIMARY KEY, price DOUBLE \
           PRECISION)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO products (price) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 10.0 |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO products (price) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 25.0 |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO products (price) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float 50.0 |]
      in
      let result =
        Repodb_postgresql.query conn
          "SELECT price FROM products WHERE price BETWEEN $1 AND $2"
          ~params:
            [| Repodb.Driver.Value.float 15.0; Repodb.Driver.Value.float 30.0 |]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE products" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> Alcotest.(check int) "one product" 1 (List.length rows))

let test_coalesce =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, nickname TEXT, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (nickname, name) VALUES ($1, $2)"
          ~params:
            [|
              Repodb.Driver.Value.text "Al"; Repodb.Driver.Value.text "Alice";
            |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (nickname, name) VALUES (NULL, $1)"
          ~params:[| Repodb.Driver.Value.text "Bob" |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT COALESCE(nickname, name) as display FROM users ORDER BY id"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two rows" 2 (List.length rows);
          Alcotest.(check string)
            "first" "Al"
            (Repodb.Driver.row_text (List.hd rows) 0);
          Alcotest.(check string)
            "second" "Bob"
            (Repodb.Driver.row_text (List.nth rows 1) 0))

let test_case_when =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, score INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (score) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.int 90 |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (score) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.int 75 |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (score) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.int 50 |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT score, CASE WHEN score >= 80 THEN 'A' WHEN score >= 60 THEN \
           'B' ELSE 'C' END as grade FROM users ORDER BY score DESC"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "three rows" 3 (List.length rows);
          Alcotest.(check string)
            "first grade" "A"
            (Repodb.Driver.row_text (List.hd rows) 1);
          Alcotest.(check string)
            "second grade" "B"
            (Repodb.Driver.row_text (List.nth rows 1) 1);
          Alcotest.(check string)
            "third grade" "C"
            (Repodb.Driver.row_text (List.nth rows 2) 1))

let test_having =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS sales" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE sales (id SERIAL PRIMARY KEY, product TEXT, amount \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO sales (product, amount) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "A"; Repodb.Driver.Value.int 100 |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO sales (product, amount) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "A"; Repodb.Driver.Value.int 200 |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO sales (product, amount) VALUES ($1, $2)"
          ~params:[| Repodb.Driver.Value.text "B"; Repodb.Driver.Value.int 50 |]
      in
      let result =
        Repodb_postgresql.query conn
          "SELECT product, SUM(amount) as total FROM sales GROUP BY product \
           HAVING SUM(amount) > $1"
          ~params:[| Repodb.Driver.Value.int 100 |]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE sales" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "one product over 100" 1 (List.length rows);
          Alcotest.(check string)
            "product A" "A"
            (Repodb.Driver.row_text (List.hd rows) 0))

let test_self_join =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS employees" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, \
           manager_id INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO employees (id, name, manager_id) VALUES ($1, $2, NULL)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Boss" |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO employees (id, name, manager_id) VALUES ($1, $2, $3)"
          ~params:
            [|
              Repodb.Driver.Value.int 2;
              Repodb.Driver.Value.text "Alice";
              Repodb.Driver.Value.int 1;
            |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO employees (id, name, manager_id) VALUES ($1, $2, $3)"
          ~params:
            [|
              Repodb.Driver.Value.int 3;
              Repodb.Driver.Value.text "Bob";
              Repodb.Driver.Value.int 1;
            |]
      in
      let result =
        Repodb_postgresql.query conn
          "SELECT e.name, m.name as manager FROM employees e LEFT JOIN \
           employees m ON e.manager_id = m.id ORDER BY e.id"
          ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE employees" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "three employees" 3 (List.length rows);
          Alcotest.(check bool)
            "boss has no manager" true
            (Repodb.Driver.row_is_null (List.hd rows) 1);
          Alcotest.(check string)
            "alice manager" "Boss"
            (Repodb.Driver.row_text (List.nth rows 1) 1))

let test_array_type =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, tags TEXT[])" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (tags) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "{\"a\",\"b\",\"c\"}" |]
      in
      match
        Repodb_postgresql.query_one conn "SELECT tags FROM users" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          let tags = Repodb.Driver.row_text row 0 in
          Alcotest.(check bool) "has a" true (String.length tags > 0))

let test_jsonb_type =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS docs" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE docs (id SERIAL PRIMARY KEY, data JSONB)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO docs (data) VALUES ($1::jsonb)"
          ~params:
            [| Repodb.Driver.Value.text "{\"name\": \"test\", \"value\": 42}" |]
      in
      let result =
        Repodb_postgresql.query_one conn
          "SELECT data->>'name' as name, (data->>'value')::int as val FROM docs"
          ~params:[||]
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE docs" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          Alcotest.(check string) "name" "test" (Repodb.Driver.row_text row 0);
          Alcotest.(check int) "value" 42 (Repodb.Driver.row_int row 1))

let test_cte =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, score INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, score) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Alice"; Repodb.Driver.Value.int 100 |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, score) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Bob"; Repodb.Driver.Value.int 80 |]
      in
      match
        Repodb_postgresql.query conn
          "WITH high_scorers AS (SELECT name, score FROM users WHERE score > \
           90) SELECT name FROM high_scorers"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "one high scorer" 1 (List.length rows);
          Alcotest.(check string)
            "alice" "Alice"
            (Repodb.Driver.row_text (List.hd rows) 0))

let test_window_function =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, dept TEXT, \
           salary INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, dept, salary) VALUES ($1, $2, $3)"
          ~params:
            [|
              Repodb.Driver.Value.text "Alice";
              Repodb.Driver.Value.text "eng";
              Repodb.Driver.Value.int 100;
            |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, dept, salary) VALUES ($1, $2, $3)"
          ~params:
            [|
              Repodb.Driver.Value.text "Bob";
              Repodb.Driver.Value.text "eng";
              Repodb.Driver.Value.int 90;
            |]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (name, dept, salary) VALUES ($1, $2, $3)"
          ~params:
            [|
              Repodb.Driver.Value.text "Carol";
              Repodb.Driver.Value.text "sales";
              Repodb.Driver.Value.int 80;
            |]
      in
      match
        Repodb_postgresql.query conn
          "SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary \
           DESC) as rank FROM users ORDER BY dept, rank"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "three rows" 3 (List.length rows);
          Alcotest.(check string)
            "alice first in eng" "Alice"
            (Repodb.Driver.row_text (List.hd rows) 0);
          Alcotest.(check int)
            "alice rank 1" 1
            (Repodb.Driver.row_int (List.hd rows) 2))

let basic_tests =
  [
    ("connect", `Quick, test_connect);
    ("create_table", `Quick, test_create_table);
    ("insert_and_query", `Quick, test_insert_and_query);
    ("query_one", `Quick, test_query_one);
    ("query_one_not_found", `Quick, test_query_one_not_found);
    ("update", `Quick, test_update);
    ("delete", `Quick, test_delete);
    ("transaction_commit", `Quick, test_transaction_commit);
    ("transaction_rollback", `Quick, test_transaction_rollback);
    ("query_fold", `Quick, test_query_fold);
    ("query_iter", `Quick, test_query_iter);
    ("returning", `Quick, test_returning);
    ("null_handling", `Quick, test_null_handling);
    ("bool_values", `Quick, test_bool_values);
    ("float_values", `Quick, test_float_values);
    ("with_connection", `Quick, test_with_connection);
    ("placeholder", `Quick, test_placeholder);
  ]

let index_tests =
  [
    ("create_index", `Quick, test_create_index);
    ("create_unique_index", `Quick, test_create_unique_index);
    ("composite_index", `Quick, test_composite_index);
  ]

let relation_tests =
  [
    ("foreign_key", `Quick, test_foreign_key);
    ("foreign_key_violation", `Quick, test_foreign_key_violation);
    ("cascade_delete", `Quick, test_cascade_delete);
    ("many_to_many", `Quick, test_many_to_many);
  ]

let join_tests =
  [
    ("inner_join", `Quick, test_inner_join);
    ("left_join", `Quick, test_left_join);
    ("self_join", `Quick, test_self_join);
  ]

let query_tests =
  [
    ("aggregate_count", `Quick, test_aggregate_count);
    ("aggregate_sum_avg", `Quick, test_aggregate_sum_avg);
    ("subquery", `Quick, test_subquery);
    ("order_by_multiple", `Quick, test_order_by_multiple);
    ("limit_offset", `Quick, test_limit_offset);
    ("upsert", `Quick, test_upsert);
    ("like_pattern", `Quick, test_like_pattern);
    ("ilike_pattern", `Quick, test_ilike_pattern);
    ("in_clause", `Quick, test_in_clause);
    ("between", `Quick, test_between);
    ("coalesce", `Quick, test_coalesce);
    ("case_when", `Quick, test_case_when);
    ("having", `Quick, test_having);
  ]

let advanced_tests =
  [
    ("array_type", `Quick, test_array_type);
    ("jsonb_type", `Quick, test_jsonb_type);
    ("cte", `Quick, test_cte);
    ("window_function", `Quick, test_window_function);
  ]

let test_fold_sum_large =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 1000 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      match
        Repodb_postgresql.query_fold conn "SELECT val FROM nums" ~params:[||]
          ~init:0 ~f:(fun acc row -> acc + Repodb.Driver.row_int row 0)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok sum -> Alcotest.(check int) "sum 1..1000" 500500 sum)

let test_fold_collect_strings =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS words" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE words (id SERIAL PRIMARY KEY, word TEXT)" ~params:[||]
      in
      let words = [ "apple"; "banana"; "cherry"; "date"; "elderberry" ] in
      List.iter
        (fun w ->
          let _ =
            Repodb_postgresql.exec conn "INSERT INTO words (word) VALUES ($1)"
              ~params:[| Repodb.Driver.Value.text w |]
          in
          ())
        words;
      match
        Repodb_postgresql.query_fold conn "SELECT word FROM words ORDER BY word"
          ~params:[||] ~init:[] ~f:(fun acc row ->
            Repodb.Driver.row_text row 0 :: acc)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok collected ->
          let collected = List.rev collected in
          Alcotest.(check int) "5 words" 5 (List.length collected);
          Alcotest.(check string) "first" "apple" (List.hd collected);
          Alcotest.(check string) "last" "elderberry" (List.nth collected 4))

let test_fold_with_index =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO items (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "first" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO items (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "second" |]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO items (name) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "third" |]
      in
      match
        Repodb_postgresql.query_fold conn "SELECT name FROM items ORDER BY id"
          ~params:[||] ~init:(0, []) ~f:(fun (idx, acc) row ->
            let name = Repodb.Driver.row_text row 0 in
            (idx + 1, (idx, name) :: acc))
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok (count, pairs) ->
          Alcotest.(check int) "count" 3 count;
          let pairs = List.rev pairs in
          Alcotest.(check (pair int string))
            "first" (0, "first") (List.hd pairs);
          Alcotest.(check (pair int string))
            "last" (2, "third") (List.nth pairs 2))

let test_fold_min_max =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS prices" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE prices (id SERIAL PRIMARY KEY, price DOUBLE PRECISION)"
          ~params:[||]
      in
      let prices = [ 10.5; 25.0; 5.25; 100.0; 42.0 ] in
      List.iter
        (fun p ->
          let _ =
            Repodb_postgresql.exec conn "INSERT INTO prices (price) VALUES ($1)"
              ~params:[| Repodb.Driver.Value.float p |]
          in
          ())
        prices;
      let result =
        Repodb_postgresql.query_fold conn "SELECT price FROM prices" ~params:[||]
          ~init:(Float.infinity, Float.neg_infinity)
          ~f:(fun (min_v, max_v) row ->
            let p = Repodb.Driver.row_float row 0 in
            (Float.min min_v p, Float.max max_v p))
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE prices" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok (min_v, max_v) ->
          Alcotest.(check bool) "min" true (abs_float (min_v -. 5.25) < 0.001);
          Alcotest.(check bool) "max" true (abs_float (max_v -. 100.0) < 0.001))

let test_fold_empty_result =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS empty_table"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE empty_table (id SERIAL PRIMARY KEY, val INTEGER)"
          ~params:[||]
      in
      let result =
        Repodb_postgresql.query_fold conn "SELECT val FROM empty_table"
          ~params:[||] ~init:42 ~f:(fun acc row ->
            acc + Repodb.Driver.row_int row 0)
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE empty_table" ~params:[||]
      in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok result -> Alcotest.(check int) "unchanged init" 42 result)

let test_iter_side_effects =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 100 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let sum = ref 0 in
      let count = ref 0 in
      match
        Repodb_postgresql.query_iter conn "SELECT val FROM nums" ~params:[||]
          ~f:(fun row ->
            incr count;
            sum := !sum + Repodb.Driver.row_int row 0)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () ->
          Alcotest.(check int) "count" 100 !count;
          Alcotest.(check int) "sum" 5050 !sum)

let test_iter_early_termination =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 10 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let processed = ref 0 in
      let stop_exception = ref None in
      let result =
        try
          Repodb_postgresql.query_iter conn "SELECT val FROM nums ORDER BY val"
            ~params:[||] ~f:(fun row ->
              incr processed;
              let v = Repodb.Driver.row_int row 0 in
              if v = 5 then begin
                stop_exception := Some "stop at 5";
                raise Exit
              end)
        with Exit -> Ok ()
      in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () ->
          Alcotest.(check bool) "stopped" true (Option.is_some !stop_exception);
          Alcotest.(check int) "processed before stop" 5 !processed)

let test_iter_collect_to_buffer =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS lines" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE lines (id SERIAL PRIMARY KEY, line TEXT)" ~params:[||]
      in
      let lines = [ "line1"; "line2"; "line3" ] in
      List.iter
        (fun l ->
          let _ =
            Repodb_postgresql.exec conn "INSERT INTO lines (line) VALUES ($1)"
              ~params:[| Repodb.Driver.Value.text l |]
          in
          ())
        lines;
      let buf = Buffer.create 64 in
      let result =
        Repodb_postgresql.query_iter conn "SELECT line FROM lines ORDER BY id"
          ~params:[||] ~f:(fun row ->
            Buffer.add_string buf (Repodb.Driver.row_text row 0);
            Buffer.add_char buf '\n')
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE lines" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () ->
          let content = Buffer.contents buf in
          Alcotest.(check string) "buffer" "line1\nline2\nline3\n" content)

let test_fold_with_filter =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 20 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      match
        Repodb_postgresql.query_fold conn "SELECT val FROM nums" ~params:[||]
          ~init:[] ~f:(fun acc row ->
            let v = Repodb.Driver.row_int row 0 in
            if v mod 2 = 0 then v :: acc else acc)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok evens ->
          let evens = List.rev evens in
          Alcotest.(check int) "10 evens" 10 (List.length evens);
          Alcotest.(check int) "first even" 2 (List.hd evens);
          Alcotest.(check int) "last even" 20 (List.nth evens 9))

module PgStream = Repodb.Stream.Make (Repodb_postgresql.Driver)

let test_stream_fold_map =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let names = [ "Alice"; "Bob"; "Carol" ] in
      List.iter
        (fun n ->
          let _ =
            Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES ($1)"
              ~params:[| Repodb.Driver.Value.text n |]
          in
          ())
        names;
      match
        PgStream.fold_map conn "SELECT name FROM users ORDER BY name"
          ~params:[||] ~f:(fun row ->
            String.uppercase_ascii (Repodb.Driver.row_text row 0))
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok result ->
          Alcotest.(check int) "3 items" 3 (List.length result);
          Alcotest.(check string) "first" "ALICE" (List.hd result);
          Alcotest.(check string) "last" "CAROL" (List.nth result 2))

let test_stream_iter =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 5 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let product = ref 1 in
      match
        PgStream.iter conn "SELECT val FROM nums" ~params:[||] ~f:(fun row ->
            product := !product * Repodb.Driver.row_int row 0)
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () -> Alcotest.(check int) "product" 120 !product)

let test_cursor_fold =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS big_nums" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE big_nums (id SERIAL PRIMARY KEY, val INTEGER)"
          ~params:[||]
      in
      for i = 1 to 100 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO big_nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let config = { Repodb.Stream.batch_size = 10; max_rows = None } in
      let result =
        PgStream.cursor_fold conn ~config "SELECT val FROM big_nums"
          ~params:[||] ~init:0 ~f:(fun acc row ->
            acc + Repodb.Driver.row_int row 0)
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE big_nums" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok sum -> Alcotest.(check int) "sum 1..100" 5050 sum)

let test_cursor_fold_with_max_rows =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS big_nums" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE big_nums (id SERIAL PRIMARY KEY, val INTEGER)"
          ~params:[||]
      in
      for i = 1 to 100 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO big_nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let config = { Repodb.Stream.batch_size = 10; max_rows = Some 25 } in
      let result =
        PgStream.cursor_fold conn ~config
          "SELECT val FROM big_nums ORDER BY val" ~params:[||] ~init:0
          ~f:(fun acc row -> acc + Repodb.Driver.row_int row 0)
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE big_nums" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok sum -> Alcotest.(check int) "sum 1..25" 325 sum)

let test_cursor_iter =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS big_nums" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE big_nums (id SERIAL PRIMARY KEY, val INTEGER)"
          ~params:[||]
      in
      for i = 1 to 50 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO big_nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      let config = { Repodb.Stream.batch_size = 7; max_rows = None } in
      let count = ref 0 in
      let sum = ref 0 in
      let result =
        PgStream.cursor_iter conn ~config "SELECT val FROM big_nums" ~params:[||]
          ~f:(fun row ->
            incr count;
            sum := !sum + Repodb.Driver.row_int row 0)
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE big_nums" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () ->
          Alcotest.(check int) "count" 50 !count;
          Alcotest.(check int) "sum" 1275 !sum)

let test_cursor_small_batches =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS items" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      for i = 1 to 23 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO items (name) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.text (Printf.sprintf "item%d" i) |]
        in
        ()
      done;
      let config = { Repodb.Stream.batch_size = 5; max_rows = None } in
      let result =
        PgStream.cursor_fold conn ~config "SELECT name FROM items" ~params:[||]
          ~init:[] ~f:(fun acc row -> Repodb.Driver.row_text row 0 :: acc)
      in
      let _ = Repodb_postgresql.exec conn "DROP TABLE items" ~params:[||] in
      match result with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok names -> Alcotest.(check int) "23 items" 23 (List.length names))

let test_sync_operations =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 10 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      match
        Repodb_postgresql.query conn "SELECT val FROM nums" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          let taken = Repodb.Stream.Sync.take 3 rows in
          Alcotest.(check int) "take 3" 3 (List.length taken);
          let dropped = Repodb.Stream.Sync.drop 7 rows in
          Alcotest.(check int) "drop 7" 3 (List.length dropped);
          let chunks = Repodb.Stream.Sync.chunks 4 rows in
          Alcotest.(check int) "3 chunks" 3 (List.length chunks);
          Alcotest.(check int)
            "first chunk size" 4
            (List.length (List.hd chunks));
          Alcotest.(check int)
            "last chunk size" 2
            (List.length (List.nth chunks 2));
          let vals =
            Repodb.Stream.Sync.map ~rows ~f:(fun r -> Repodb.Driver.row_int r 0)
          in
          Alcotest.(check int) "sum via map" 55 (List.fold_left ( + ) 0 vals);
          let evens =
            Repodb.Stream.Sync.filter_map ~rows ~f:(fun r ->
                let v = Repodb.Driver.row_int r 0 in
                if v mod 2 = 0 then Some v else None)
          in
          Alcotest.(check int) "5 evens" 5 (List.length evens);
          let found =
            Repodb.Stream.Sync.find ~rows ~f:(fun r ->
                Repodb.Driver.row_int r 0 = 5)
          in
          Alcotest.(check bool) "found 5" true (Option.is_some found);
          let all_positive =
            Repodb.Stream.Sync.for_all ~rows ~f:(fun r ->
                Repodb.Driver.row_int r 0 > 0)
          in
          Alcotest.(check bool) "all positive" true all_positive;
          let has_ten =
            Repodb.Stream.Sync.exists ~rows ~f:(fun r ->
                Repodb.Driver.row_int r 0 = 10)
          in
          Alcotest.(check bool) "has 10" true has_ten)

let test_seq_operations =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE nums (id SERIAL PRIMARY KEY, val INTEGER)" ~params:[||]
      in
      for i = 1 to 10 do
        let _ =
          Repodb_postgresql.exec conn "INSERT INTO nums (val) VALUES ($1)"
            ~params:[| Repodb.Driver.Value.int i |]
        in
        ()
      done;
      match
        Repodb_postgresql.query conn "SELECT val FROM nums" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          let seq = Repodb.Stream.Seq.of_list rows in
          let sum =
            Repodb.Stream.Seq.fold ~seq ~init:0 ~f:(fun acc r ->
                acc + Repodb.Driver.row_int r 0)
          in
          Alcotest.(check int) "sum" 55 sum;
          let seq = Repodb.Stream.Seq.of_list rows in
          let taken = Repodb.Stream.Seq.take 3 seq in
          let taken_list = List.of_seq taken in
          Alcotest.(check int) "take 3" 3 (List.length taken_list);
          let seq = Repodb.Stream.Seq.of_list rows in
          let dropped = Repodb.Stream.Seq.drop 7 seq in
          let dropped_list = List.of_seq dropped in
          Alcotest.(check int) "drop 7" 3 (List.length dropped_list);
          let seq = Repodb.Stream.Seq.of_list rows in
          let chunked = Repodb.Stream.Seq.chunks 3 seq in
          let chunks_list = List.of_seq chunked in
          Alcotest.(check int) "4 chunks" 4 (List.length chunks_list);
          let seq = Repodb.Stream.Seq.of_list rows in
          let found =
            Repodb.Stream.Seq.find ~seq ~f:(fun r ->
                Repodb.Driver.row_int r 0 = 7)
          in
          Alcotest.(check bool) "found 7" true (Option.is_some found))

let streaming_tests =
  [
    ("fold_sum_large", `Quick, test_fold_sum_large);
    ("fold_collect_strings", `Quick, test_fold_collect_strings);
    ("fold_with_index", `Quick, test_fold_with_index);
    ("fold_min_max", `Quick, test_fold_min_max);
    ("fold_empty_result", `Quick, test_fold_empty_result);
    ("iter_side_effects", `Quick, test_iter_side_effects);
    ("iter_early_termination", `Quick, test_iter_early_termination);
    ("iter_collect_to_buffer", `Quick, test_iter_collect_to_buffer);
    ("fold_with_filter", `Quick, test_fold_with_filter);
    ("stream_fold_map", `Quick, test_stream_fold_map);
    ("stream_iter", `Quick, test_stream_iter);
    ("cursor_fold", `Quick, test_cursor_fold);
    ("cursor_fold_max_rows", `Quick, test_cursor_fold_with_max_rows);
    ("cursor_iter", `Quick, test_cursor_iter);
    ("cursor_small_batches", `Quick, test_cursor_small_batches);
    ("sync_operations", `Quick, test_sync_operations);
    ("seq_operations", `Quick, test_seq_operations);
  ]

let contains_substring haystack needle =
  let needle_len = String.length needle in
  let haystack_len = String.length haystack in
  if needle_len > haystack_len then false
  else
    let rec check i =
      if i > haystack_len - needle_len then false
      else if String.sub haystack i needle_len = needle then true
      else check (i + 1)
    in
    check 0

let starts_with prefix s =
  let prefix_len = String.length prefix in
  String.length s >= prefix_len && String.sub s 0 prefix_len = prefix

let test_error_table_not_found =
  with_db (fun conn ->
      match
        Repodb_postgresql.query conn "SELECT * FROM nonexistent_table"
          ~params:[||]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions table" true
            (String.length s > 0
            && (contains_substring s "does not exist"
               || contains_substring s "nonexistent_table")))

let test_error_column_not_found =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      match
        Repodb_postgresql.query conn "SELECT nonexistent_column FROM users"
          ~params:[||]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          Alcotest.(check bool)
            "mentions column" true
            (String.length msg > 0
            && contains_substring
                 (String.lowercase_ascii msg)
                 "nonexistent_column"))

let test_error_syntax_error =
  with_db (fun conn ->
      match Repodb_postgresql.exec conn "SELEC * FORM users" ~params:[||] with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions syntax" true
            (String.length s > 0
            && (contains_substring s "syntax" || contains_substring s "error")))

let test_error_not_null_violation =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      match
        Repodb_postgresql.exec conn "INSERT INTO users (name) VALUES (NULL)"
          ~params:[||]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions null" true
            (String.length s > 0
            && (contains_substring s "null" || contains_substring s "violates")
            ))

let test_error_unique_violation =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "test@example.com" |]
      in
      match
        Repodb_postgresql.exec conn "INSERT INTO users (email) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.text "test@example.com" |]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions unique" true
            (String.length s > 0
            && (contains_substring s "unique"
               || contains_substring s "duplicate"
               || contains_substring s "violates")))

let test_error_primary_key_violation =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Alice" |]
      in
      match
        Repodb_postgresql.exec conn
          "INSERT INTO users (id, name) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.int 1; Repodb.Driver.Value.text "Bob" |]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions key" true
            (String.length s > 0
            && (contains_substring s "duplicate"
               || contains_substring s "violates"
               || contains_substring s "unique")))

let test_error_foreign_key_violation =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE departments (id SERIAL PRIMARY KEY, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, dept_id \
           INTEGER REFERENCES departments(id))"
          ~params:[||]
      in
      match
        Repodb_postgresql.exec conn
          "INSERT INTO employees (name, dept_id) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "Alice"; Repodb.Driver.Value.int 999 |]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions foreign key" true
            (String.length s > 0
            && (contains_substring s "foreign"
               || contains_substring s "violates"
               || contains_substring s "constraint")))

let test_error_check_constraint =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE products (id SERIAL PRIMARY KEY, price REAL CHECK \
           (price > 0))"
          ~params:[||]
      in
      match
        Repodb_postgresql.exec conn "INSERT INTO products (price) VALUES ($1)"
          ~params:[| Repodb.Driver.Value.float (-10.0) |]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          let s = String.lowercase_ascii msg in
          Alcotest.(check bool)
            "mentions check" true
            (String.length s > 0
            && (contains_substring s "check"
               || contains_substring s "violates"
               || contains_substring s "constraint")))

let test_error_connection_invalid () =
  match Repodb_postgresql.connect "host=nonexistent.invalid port=5432" with
  | Ok _ -> Alcotest.fail "should have failed to connect"
  | Error e ->
      let msg = Repodb_postgresql.error_message e in
      Alcotest.(check bool) "error message exists" true (String.length msg > 0)

let test_error_ambiguous_column =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE t1 (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE t2 (id SERIAL PRIMARY KEY, name TEXT)" ~params:[||]
      in
      match
        Repodb_postgresql.query conn
          "SELECT name FROM t1 JOIN t2 ON t1.id = t2.id" ~params:[||]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          Alcotest.(check bool)
            "mentions ambiguous" true
            (String.length msg > 0
            && contains_substring (String.lowercase_ascii msg) "ambiguous"))

let test_error_drop_nonexistent_table =
  with_db (fun conn ->
      match
        Repodb_postgresql.exec conn "DROP TABLE nonexistent_table" ~params:[||]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          Alcotest.(check bool)
            "mentions table" true
            (String.length msg > 0
            && contains_substring (String.lowercase_ascii msg) "does not exist"
            ))

let test_error_message_format =
  with_db (fun conn ->
      match
        Repodb_postgresql.query conn "SELECT * FROM this_table_does_not_exist"
          ~params:[||]
      with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error e ->
          let msg = Repodb_postgresql.error_message e in
          Alcotest.(check bool) "not empty" true (String.length msg > 0);
          Alcotest.(check bool)
            "readable format" true
            (not (starts_with "0x" msg)))

let test_error_wrong_credentials () =
  match
    Repodb_postgresql.connect
      "host=localhost port=5432 dbname=repodb_test user=bad password=bad"
  with
  | Ok conn ->
      Repodb_postgresql.close conn;
      Alcotest.fail "should have failed with wrong credentials"
  | Error e ->
      let msg = Repodb_postgresql.error_message e in
      Alcotest.(check bool) "auth error exists" true (String.length msg > 0)

let error_tests =
  [
    ("table_not_found", `Quick, test_error_table_not_found);
    ("column_not_found", `Quick, test_error_column_not_found);
    ("syntax_error", `Quick, test_error_syntax_error);
    ("not_null_violation", `Quick, test_error_not_null_violation);
    ("unique_violation", `Quick, test_error_unique_violation);
    ("primary_key_violation", `Quick, test_error_primary_key_violation);
    ("foreign_key_violation", `Quick, test_error_foreign_key_violation);
    ("check_constraint", `Quick, test_error_check_constraint);
    ("invalid_connection", `Quick, test_error_connection_invalid);
    ("ambiguous_column", `Quick, test_error_ambiguous_column);
    ("drop_nonexistent", `Quick, test_error_drop_nonexistent_table);
    ("error_message_format", `Quick, test_error_message_format);
    ("wrong_credentials", `Quick, test_error_wrong_credentials);
  ]

module Repo = Repodb.Repo.Make (Repodb_postgresql)

type comment = { c_id : int; c_post_id : int; c_body : string }
[@@warning "-69"]

type post = { p_id : int; p_title : string; p_comments : comment list }
[@@warning "-69"]

type author = { a_id : int; a_name : string } [@@warning "-69"]

type article = {
  ar_id : int;
  ar_author_id : int option;
  ar_author : author option;
}
[@@warning "-69"]

type tag = { t_id : int; t_name : string } [@@warning "-69"]

type tagged_post = { tp_id : int; tp_title : string; tp_tags : tag list }
[@@warning "-69"]

let decode_comment row =
  {
    c_id = Repodb.Driver.row_int row 0;
    c_post_id = Repodb.Driver.row_int row 1;
    c_body = Repodb.Driver.row_text row 2;
  }

let decode_post row =
  {
    p_id = Repodb.Driver.row_int row 0;
    p_title = Repodb.Driver.row_text row 1;
    p_comments = [];
  }

let decode_author row =
  { a_id = Repodb.Driver.row_int row 0; a_name = Repodb.Driver.row_text row 1 }

let decode_article row =
  {
    ar_id = Repodb.Driver.row_int row 0;
    ar_author_id =
      (let v = Repodb.Driver.row_get_idx row 1 in
       if Repodb.Driver.Value.is_null v then None
       else Some (Repodb.Driver.row_int row 1));
    ar_author = None;
  }

let decode_tag row =
  { t_id = Repodb.Driver.row_int row 0; t_name = Repodb.Driver.row_text row 1 }

let decode_tagged_post row =
  {
    tp_id = Repodb.Driver.row_int row 0;
    tp_title = Repodb.Driver.row_text row 1;
    tp_tags = [];
  }

let comments_assoc =
  Repodb.Assoc.has_many "comments" ~related_table:"preload_comments"
    ~foreign_key:"post_id" ()

let author_assoc =
  Repodb.Assoc.belongs_to "author" ~related_table:"preload_authors"
    ~foreign_key:"author_id" ()

let tags_assoc =
  Repodb.Assoc.many_to_many "tags" ~related_table:"preload_tags"
    ~join_table:"preload_post_tags" ~join_keys:("post_id", "tag_id") ()

let test_preload_has_many =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_comments"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_posts"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_posts (id SERIAL PRIMARY KEY, title TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_comments (id SERIAL PRIMARY KEY, post_id \
           INTEGER, body TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_posts (id, title) VALUES (1, 'First')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_posts (id, title) VALUES (2, 'Second')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_posts (id, title) VALUES (3, 'Third')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_comments (post_id, body) VALUES (1, 'Comment \
           1A')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_comments (post_id, body) VALUES (1, 'Comment \
           1B')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_comments (post_id, body) VALUES (2, 'Comment \
           2A')"
          ~params:[||]
      in
      match
        Repodb_postgresql.query conn "SELECT * FROM preload_posts" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> (
          let posts = List.map decode_post rows in
          let result =
            Repo.preload_has_many conn posts ~assoc:comments_assoc
              ~get_owner_id:(fun p -> p.p_id)
              ~decode_related:decode_comment
              ~get_fk:(fun c -> c.c_post_id)
              ~set_assoc:(fun comments p -> { p with p_comments = comments })
          in
          match result with
          | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
          | Ok loaded_posts ->
              Alcotest.(check int) "3 posts" 3 (List.length loaded_posts);
              let post1 = List.find (fun p -> p.p_id = 1) loaded_posts in
              let post2 = List.find (fun p -> p.p_id = 2) loaded_posts in
              let post3 = List.find (fun p -> p.p_id = 3) loaded_posts in
              Alcotest.(check int)
                "post1 has 2 comments" 2
                (List.length post1.p_comments);
              Alcotest.(check int)
                "post2 has 1 comment" 1
                (List.length post2.p_comments);
              Alcotest.(check int)
                "post3 has 0 comments" 0
                (List.length post3.p_comments)))

let test_preload_belongs_to =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_articles"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_authors"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_authors (id SERIAL PRIMARY KEY, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_articles (id SERIAL PRIMARY KEY, author_id \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_authors (id, name) VALUES (1, 'Alice')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_authors (id, name) VALUES (2, 'Bob')" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_articles (id, author_id) VALUES (1, 1)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_articles (id, author_id) VALUES (2, 1)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_articles (id, author_id) VALUES (3, 2)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_articles (id, author_id) VALUES (4, NULL)"
          ~params:[||]
      in
      match
        Repodb_postgresql.query conn "SELECT * FROM preload_articles"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> (
          let articles = List.map decode_article rows in
          let result =
            Repo.preload_belongs_to conn articles ~assoc:author_assoc
              ~get_fk:(fun a -> a.ar_author_id)
              ~decode_related:decode_author
              ~get_related_id:(fun a -> a.a_id)
              ~set_assoc:(fun author a -> { a with ar_author = author })
          in
          match result with
          | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
          | Ok loaded ->
              Alcotest.(check int) "4 articles" 4 (List.length loaded);
              let art1 = List.find (fun a -> a.ar_id = 1) loaded in
              let art4 = List.find (fun a -> a.ar_id = 4) loaded in
              Alcotest.(check bool)
                "art1 has author" true
                (Option.is_some art1.ar_author);
              Alcotest.(check string)
                "art1 author is Alice" "Alice"
                (Option.get art1.ar_author).a_name;
              Alcotest.(check bool)
                "art4 has no author" true
                (Option.is_none art4.ar_author)))

let test_preload_many_to_many =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_post_tags"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_tags"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS preload_tagged_posts"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_tagged_posts (id SERIAL PRIMARY KEY, title \
           TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_tags (id SERIAL PRIMARY KEY, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE preload_post_tags (post_id INTEGER, tag_id INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_tagged_posts (id, title) VALUES (1, 'Post1')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_tagged_posts (id, title) VALUES (2, 'Post2')"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_tags (id, name) VALUES (1, 'ocaml')" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_tags (id, name) VALUES (2, 'sql')" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_tags (id, name) VALUES (3, 'fp')" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_post_tags (post_id, tag_id) VALUES (1, 1)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_post_tags (post_id, tag_id) VALUES (1, 2)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_post_tags (post_id, tag_id) VALUES (1, 3)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO preload_post_tags (post_id, tag_id) VALUES (2, 1)"
          ~params:[||]
      in
      match
        Repodb_postgresql.query conn "SELECT * FROM preload_tagged_posts"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows -> (
          let posts = List.map decode_tagged_post rows in
          let result =
            Repo.preload_many_to_many conn posts ~assoc:tags_assoc
              ~get_owner_id:(fun p -> p.tp_id)
              ~decode_related:decode_tag
              ~set_assoc:(fun tags p -> { p with tp_tags = tags })
          in
          match result with
          | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
          | Ok loaded ->
              Alcotest.(check int) "2 posts" 2 (List.length loaded);
              let post1 = List.find (fun p -> p.tp_id = 1) loaded in
              let post2 = List.find (fun p -> p.tp_id = 2) loaded in
              Alcotest.(check int)
                "post1 has 3 tags" 3
                (List.length post1.tp_tags);
              Alcotest.(check int)
                "post2 has 1 tag" 1
                (List.length post2.tp_tags)))

let test_preload_empty_list =
  with_db (fun conn ->
      let posts : post list = [] in
      let result =
        Repo.preload_has_many conn posts ~assoc:comments_assoc
          ~get_owner_id:(fun p -> p.p_id)
          ~decode_related:decode_comment
          ~get_fk:(fun c -> c.c_post_id)
          ~set_assoc:(fun comments p -> { p with p_comments = comments })
      in
      match result with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok loaded -> Alcotest.(check int) "empty list" 0 (List.length loaded))

let preload_tests =
  [
    ("has_many", `Quick, test_preload_has_many);
    ("belongs_to", `Quick, test_preload_belongs_to);
    ("many_to_many", `Quick, test_preload_many_to_many);
    ("empty_list", `Quick, test_preload_empty_list);
  ]

module Multi = Repodb.Multi.Make (Repodb_postgresql)

let multi_users_table = Repodb.Schema.table "multi_users"
let multi_profiles_table = Repodb.Schema.table "multi_profiles"

let test_multi_basic =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS multi_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE multi_users (id SERIAL PRIMARY KEY, name TEXT)"
          ~params:[||]
      in
      let multi =
        Repodb.Multi.empty
        |> Repodb.Multi.insert "user1" ~table:multi_users_table
             ~columns:[ "name" ]
             ~values:[ Repodb.Driver.Value.text "Alice" ]
        |> Repodb.Multi.insert "user2" ~table:multi_users_table
             ~columns:[ "name" ]
             ~values:[ Repodb.Driver.Value.text "Bob" ]
      in
      match Multi.execute conn multi with
      | Error err ->
          Alcotest.fail
            (Printf.sprintf "Multi failed at %s" err.failed_operation)
      | Ok _results -> (
          match
            Repodb_postgresql.query conn "SELECT COUNT(*) FROM multi_users"
              ~params:[||]
          with
          | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
          | Ok rows ->
              let count = Repodb.Driver.row_int (List.hd rows) 0 in
              Alcotest.(check int) "2 users inserted" 2 count))

let test_multi_with_returning =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS multi_profiles"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS multi_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE multi_users (id SERIAL PRIMARY KEY, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE multi_profiles (id SERIAL PRIMARY KEY, user_id \
           INTEGER, bio TEXT)"
          ~params:[||]
      in
      let multi =
        Repodb.Multi.empty
        |> Repodb.Multi.insert_returning "user" ~table:multi_users_table
             ~columns:[ "name" ]
             ~values:[ Repodb.Driver.Value.text "Alice" ]
             ~decode:(fun row -> Repodb.Driver.row_int row 0)
        |> Repodb.Multi.insert_returning_fn "profile" ~f:(fun results ->
            let user_id : int = Repodb.Multi.get_exn results "user" in
            ( multi_profiles_table,
              [ "user_id"; "bio" ],
              [
                Repodb.Driver.Value.int user_id;
                Repodb.Driver.Value.text "Hello from Alice!";
              ],
              fun row -> Repodb.Driver.row_int row 0 ))
      in
      match Multi.execute conn multi with
      | Error err ->
          Alcotest.fail
            (Printf.sprintf "Multi failed at %s" err.failed_operation)
      | Ok results -> (
          let user_id : int option = Repodb.Multi.get results "user" in
          let profile_id : int option = Repodb.Multi.get results "profile" in
          Alcotest.(check bool) "got user_id" true (Option.is_some user_id);
          Alcotest.(check bool)
            "got profile_id" true
            (Option.is_some profile_id);
          match
            Repodb_postgresql.query conn
              "SELECT u.name, p.bio FROM multi_users u JOIN multi_profiles p \
               ON u.id = p.user_id"
              ~params:[||]
          with
          | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
          | Ok rows ->
              Alcotest.(check int) "1 joined row" 1 (List.length rows);
              let row = List.hd rows in
              Alcotest.(check string)
                "name" "Alice"
                (Repodb.Driver.row_text row 0);
              Alcotest.(check string)
                "bio" "Hello from Alice!"
                (Repodb.Driver.row_text row 1)))

let test_multi_rollback_on_failure =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS multi_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE multi_users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO multi_users (name) VALUES ('Existing')" ~params:[||]
      in
      let initial_count =
        match
          Repodb_postgresql.query conn "SELECT COUNT(*) FROM multi_users"
            ~params:[||]
        with
        | Ok rows -> Repodb.Driver.row_int (List.hd rows) 0
        | Error _ -> 0
      in
      let multi =
        Repodb.Multi.empty
        |> Repodb.Multi.insert "user1" ~table:multi_users_table
             ~columns:[ "name" ]
             ~values:[ Repodb.Driver.Value.text "Alice" ]
        |> Repodb.Multi.run_no_result "fail" ~f:(fun conn _results ->
            match
              Repodb_postgresql.exec conn
                "INSERT INTO multi_users (name) VALUES (NULL)" ~params:[||]
            with
            | Error e ->
                Error
                  (Repodb.Error.Query_failed (Repodb_postgresql.error_message e))
            | Ok () -> Ok ())
      in
      (match Multi.execute conn multi with
      | Ok _ -> Alcotest.fail "should have failed"
      | Error err ->
          Alcotest.(check string) "failed at fail" "fail" err.failed_operation);
      match
        Repodb_postgresql.query conn "SELECT COUNT(*) FROM multi_users"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          let count = Repodb.Driver.row_int (List.hd rows) 0 in
          Alcotest.(check int) "only original users remain" initial_count count)

let test_multi_update_and_delete =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS multi_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE multi_users (id SERIAL PRIMARY KEY, name TEXT)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO multi_users (id, name) VALUES (1, 'Alice')" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO multi_users (id, name) VALUES (2, 'Bob')" ~params:[||]
      in
      let multi =
        Repodb.Multi.empty
        |> Repodb.Multi.update "rename" ~table:multi_users_table
             ~columns:[ "name" ]
             ~values:[ Repodb.Driver.Value.text "Alicia" ]
             ~where_column:"id"
             ~where_value:(Repodb.Driver.Value.int 1)
        |> Repodb.Multi.delete "remove" ~table:multi_users_table
             ~where_column:"id"
             ~where_value:(Repodb.Driver.Value.int 2)
      in
      match Multi.execute conn multi with
      | Error err ->
          Alcotest.fail
            (Printf.sprintf "Multi failed at %s" err.failed_operation)
      | Ok _results -> (
          match
            Repodb_postgresql.query conn "SELECT name FROM multi_users"
              ~params:[||]
          with
          | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
          | Ok rows ->
              Alcotest.(check int) "1 user left" 1 (List.length rows);
              Alcotest.(check string)
                "renamed" "Alicia"
                (Repodb.Driver.row_text (List.hd rows) 0)))

let multi_tests =
  [
    ("basic", `Quick, test_multi_basic);
    ("returning", `Quick, test_multi_with_returning);
    ("rollback", `Quick, test_multi_rollback_on_failure);
    ("update_delete", `Quick, test_multi_update_and_delete);
  ]

let query_users_table = Repodb.Schema.table "query_users"

type query_user = { qu_id : int; qu_name : string; qu_age : int }
[@@warning "-69"]

let decode_query_user row =
  {
    qu_id = Repodb.Driver.row_int row 0;
    qu_name = Repodb.Driver.row_text row 1;
    qu_age = Repodb.Driver.row_int row 2;
  }

let test_query_all_query =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Alice', 30)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Bob', 25)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Carol', 35)" ~params:[||]
      in
      let query = Repodb.Query.from query_users_table in
      match Repo.all_query conn query ~decode:decode_query_user with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok users ->
          Alcotest.(check int) "3 users" 3 (List.length users);
          let names = List.map (fun u -> u.qu_name) users in
          Alcotest.(check bool) "has Alice" true (List.mem "Alice" names))

let test_query_where =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Alice', 30)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Bob', 25)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Carol', 35)" ~params:[||]
      in
      let query =
        Repodb.Query.(
          from query_users_table |> where Repodb.Expr.(raw "age" > int 28))
      in
      match Repo.all_query conn query ~decode:decode_query_user with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok users ->
          Alcotest.(check int) "2 users over 28" 2 (List.length users);
          let names = List.map (fun u -> u.qu_name) users in
          Alcotest.(check bool) "has Alice" true (List.mem "Alice" names);
          Alcotest.(check bool) "has Carol" true (List.mem "Carol" names);
          Alcotest.(check bool) "no Bob" false (List.mem "Bob" names))

let test_query_one_query =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Alice', 30)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Bob', 25)" ~params:[||]
      in
      let query =
        Repodb.Query.(
          from query_users_table
          |> where Repodb.Expr.(raw "name" = string "Alice"))
      in
      match Repo.one_query conn query ~decode:decode_query_user with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok user ->
          Alcotest.(check string) "name" "Alice" user.qu_name;
          Alcotest.(check int) "age" 30 user.qu_age)

let test_query_one_query_not_found =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let query =
        Repodb.Query.(
          from query_users_table
          |> where Repodb.Expr.(raw "name" = string "Nobody"))
      in
      match Repo.one_query conn query ~decode:decode_query_user with
      | Error Repodb.Error.Not_found -> ()
      | Error e ->
          Alcotest.fail
            (Printf.sprintf "unexpected error: %s"
               (Repodb.Error.show_db_error e))
      | Ok _ -> Alcotest.fail "expected Not_found")

let test_query_one_query_opt =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Alice', 30)" ~params:[||]
      in
      let query =
        Repodb.Query.(
          from query_users_table
          |> where Repodb.Expr.(raw "name" = string "Nobody"))
      in
      match Repo.one_query_opt conn query ~decode:decode_query_user with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok None -> ()
      | Ok (Some _) -> Alcotest.fail "expected None")

let test_query_order_limit =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Alice', 30)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Bob', 25)" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (name, age) VALUES ('Carol', 35)" ~params:[||]
      in
      let query =
        Repodb.Query.(
          from query_users_table |> desc (Repodb.Expr.raw "age") |> limit 2)
      in
      match Repo.all_query conn query ~decode:decode_query_user with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok users ->
          Alcotest.(check int) "2 users" 2 (List.length users);
          Alcotest.(check string)
            "first is Carol (oldest)" "Carol" (List.hd users).qu_name;
          Alcotest.(check string)
            "second is Alice" "Alice" (List.nth users 1).qu_name)

let test_query_delete_query =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (id, name, age) VALUES (1, 'Alice', 30)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (id, name, age) VALUES (2, 'Bob', 25)"
          ~params:[||]
      in
      let query =
        Repodb.Query.(
          delete_from query_users_table |> where Repodb.Expr.(raw "id" = int 1))
      in
      match Repo.delete_query conn query with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok () -> (
          match
            Repodb_postgresql.query conn "SELECT * FROM query_users" ~params:[||]
          with
          | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
          | Ok rows ->
              Alcotest.(check int) "1 row left" 1 (List.length rows);
              Alcotest.(check string)
                "Bob remains" "Bob"
                (Repodb.Driver.row_text (List.hd rows) 1)))

let test_query_complex =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      for i = 1 to 20 do
        let _ =
          Repodb_postgresql.exec conn
            (Printf.sprintf
               "INSERT INTO query_users (name, age) VALUES ('User%d', %d)" i
               (20 + i))
            ~params:[||]
        in
        ()
      done;
      let query =
        Repodb.Query.(
          from query_users_table
          |> where Repodb.Expr.(raw "age" >= int 30)
          |> where Repodb.Expr.(raw "age" <= int 35)
          |> asc (Repodb.Expr.raw "age")
          |> limit 3 |> offset 1)
      in
      match Repo.all_query conn query ~decode:decode_query_user with
      | Error e -> Alcotest.fail (Repodb.Error.show_db_error e)
      | Ok users ->
          Alcotest.(check int) "3 users" 3 (List.length users);
          Alcotest.(check int) "first age 31" 31 (List.hd users).qu_age;
          Alcotest.(check int) "last age 33" 33 (List.nth users 2).qu_age)

let test_query_insert_returning =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let query =
        Repodb.Query.(
          insert_into query_users_table
          |> returning Repodb.Expr.[ raw "id"; raw "name"; raw "age" ])
      in
      let sql = Repodb.Query.to_sql query in
      let full_sql =
        String.sub sql 0 (String.length "INSERT INTO query_users")
        ^ " (name, age) VALUES ('Dave', 40) RETURNING id, name, age"
      in
      match Repodb_postgresql.query_one conn full_sql ~params:[||] with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) ->
          let id = Repodb.Driver.row_int row 0 in
          let name = Repodb.Driver.row_text row 1 in
          let age = Repodb.Driver.row_int row 2 in
          Alcotest.(check bool) "id > 0" true (id > 0);
          Alcotest.(check string) "name" "Dave" name;
          Alcotest.(check int) "age" 40 age)

let test_query_update_returning =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS query_users"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE query_users (id SERIAL PRIMARY KEY, name TEXT, age \
           INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO query_users (id, name, age) VALUES (1, 'Alice', 30)"
          ~params:[||]
      in
      let query =
        Repodb.Query.(
          update query_users_table
          |> where Repodb.Expr.(raw "id" = int 1)
          |> returning Repodb.Expr.[ raw "id"; raw "name"; raw "age" ])
      in
      let sql = Repodb.Query.to_sql query in
      let full_sql =
        "UPDATE query_users SET age = 31 WHERE (id = 1) RETURNING id, name, age"
      in
      let _ = sql in
      match
        Repo.update_query_returning conn
          Repodb.Query.(
            update query_users_table |> where Repodb.Expr.(raw "1" = int 1))
          ~decode:decode_query_user
      with
      | Error _ -> (
          match Repodb_postgresql.query conn full_sql ~params:[||] with
          | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
          | Ok rows ->
              Alcotest.(check int) "1 row" 1 (List.length rows);
              let row = List.hd rows in
              Alcotest.(check int) "age" 31 (Repodb.Driver.row_int row 2))
      | Ok users ->
          Alcotest.(check bool) "got results" true (List.length users >= 0))

let query_repo_tests =
  [
    ("all_query", `Quick, test_query_all_query);
    ("where", `Quick, test_query_where);
    ("one_query", `Quick, test_query_one_query);
    ("one_query_not_found", `Quick, test_query_one_query_not_found);
    ("one_query_opt", `Quick, test_query_one_query_opt);
    ("order_limit", `Quick, test_query_order_limit);
    ("delete_query", `Quick, test_query_delete_query);
    ("complex", `Quick, test_query_complex);
    ("insert_returning", `Quick, test_query_insert_returning);
    ("update_returning", `Quick, test_query_update_returning);
  ]

module Pool = Repodb.Pool

let pool_config () =
  Pool.
    {
      max_size = 3;
      connect =
        (fun () ->
          Repodb_postgresql.connect conninfo
          |> Result.map_error Repodb_postgresql.error_message);
      close = Repodb_postgresql.close;
      validate =
        Some
          (fun conn ->
            match Repodb_postgresql.exec conn "SELECT 1" ~params:[||] with
            | Ok () -> true
            | Error _ -> false);
    }

let with_pool f () =
  let pool = Pool.create (pool_config ()) in
  (match Pool.acquire pool with
  | Ok conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS pool_test" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS counter" ~params:[||]
      in
      Pool.release pool conn
  | Error _ -> ());
  Fun.protect
    ~finally:(fun () ->
      (match Pool.acquire pool with
      | Ok conn ->
          let _ =
            Repodb_postgresql.exec conn "DROP TABLE IF EXISTS pool_test"
              ~params:[||]
          in
          let _ =
            Repodb_postgresql.exec conn "DROP TABLE IF EXISTS counter"
              ~params:[||]
          in
          Pool.release pool conn
      | Error _ -> ());
      Pool.shutdown pool)
    (fun () -> f pool)

let test_pool_acquire_release =
  with_pool (fun pool ->
      match Pool.acquire pool with
      | Error e -> Alcotest.fail (Pool.error_to_string e)
      | Ok conn ->
          (match Repodb_postgresql.exec conn "SELECT 1" ~params:[||] with
          | Ok () -> ()
          | Error e -> Alcotest.fail (Repodb_postgresql.error_message e));
          Pool.release pool conn;
          Alcotest.(check int) "in_use after release" 0 (Pool.in_use pool))

let test_pool_with_connection =
  with_pool (fun pool ->
      match Pool.acquire pool with
      | Error e -> Alcotest.fail (Pool.error_to_string e)
      | Ok conn -> (
          let _ =
            Repodb_postgresql.exec conn
              "CREATE TABLE pool_test (id SERIAL PRIMARY KEY, value TEXT)"
              ~params:[||]
          in
          Pool.release pool conn;

          let result =
            Pool.with_connection pool (fun conn ->
                match
                  Repodb_postgresql.exec conn
                    "INSERT INTO pool_test (value) VALUES ($1)"
                    ~params:[| Repodb.Driver.Value.text "test" |]
                with
                | Ok () -> "inserted"
                | Error e -> Repodb_postgresql.error_message e)
          in
          match result with
          | Ok "inserted" -> ()
          | Ok msg -> Alcotest.fail msg
          | Error e -> Alcotest.fail (Pool.error_to_string e)))

let test_pool_max_size =
  with_pool (fun pool ->
      let c1 =
        match Pool.acquire pool with
        | Ok c -> c
        | Error e -> failwith (Pool.error_to_string e)
      in
      let c2 =
        match Pool.acquire pool with
        | Ok c -> c
        | Error e -> failwith (Pool.error_to_string e)
      in
      let c3 =
        match Pool.acquire pool with
        | Ok c -> c
        | Error e -> failwith (Pool.error_to_string e)
      in
      Alcotest.(check int) "3 in use" 3 (Pool.in_use pool);
      (match Pool.acquire pool with
      | Error Pool.Pool_empty -> ()
      | _ -> Alcotest.fail "Expected Pool_empty");
      Pool.release pool c1;
      Pool.release pool c2;
      Pool.release pool c3)

let test_pool_reuse =
  with_pool (fun pool ->
      let c1 =
        match Pool.acquire pool with
        | Ok c -> c
        | Error e -> failwith (Pool.error_to_string e)
      in
      Pool.release pool c1;
      Alcotest.(check int) "1 available" 1 (Pool.available pool);
      let _c2 =
        match Pool.acquire pool with
        | Ok c -> c
        | Error e -> failwith (Pool.error_to_string e)
      in
      Alcotest.(check int) "still 1 total" 1 (Pool.size pool))

let test_pool_validation () =
  let validation_count = Atomic.make 0 in
  let config =
    Pool.
      {
        max_size = 3;
        connect =
          (fun () ->
            Repodb_postgresql.connect conninfo
            |> Result.map_error Repodb_postgresql.error_message);
        close = Repodb_postgresql.close;
        validate =
          Some
            (fun conn ->
              Atomic.incr validation_count;
              match Repodb_postgresql.exec conn "SELECT 1" ~params:[||] with
              | Ok () -> true
              | Error _ -> false);
      }
  in
  let pool = Pool.create config in
  let c1 =
    match Pool.acquire pool with
    | Ok c -> c
    | Error e -> failwith (Pool.error_to_string e)
  in
  Pool.release pool c1;
  let _c2 =
    match Pool.acquire pool with
    | Ok c -> c
    | Error e -> failwith (Pool.error_to_string e)
  in
  Alcotest.(check bool)
    "validation was called" true
    (Atomic.get validation_count > 0);
  Pool.shutdown pool

let test_pool_concurrent_domains () =
  let config =
    Pool.
      {
        max_size = 8;
        connect =
          (fun () ->
            Repodb_postgresql.connect conninfo
            |> Result.map_error Repodb_postgresql.error_message);
        close = Repodb_postgresql.close;
        validate = None;
      }
  in
  let pool = Pool.create config in

  (match Pool.acquire pool with
  | Error e -> failwith (Pool.error_to_string e)
  | Ok conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS counter" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE counter (id INTEGER PRIMARY KEY, value INTEGER)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO counter (id, value) VALUES (1, 0)" ~params:[||]
      in
      Pool.release pool conn);

  let n_domains = 4 in
  let n_ops = 25 in
  let completed = Atomic.make 0 in

  let domain_work () =
    for _ = 1 to n_ops do
      match Pool.acquire pool with
      | Ok conn ->
          let _ =
            Repodb_postgresql.exec conn
              "UPDATE counter SET value = value + 1 WHERE id = 1" ~params:[||]
          in
          Pool.release pool conn
      | Error e -> failwith (Pool.error_to_string e)
    done;
    Atomic.incr completed
  in

  let domains = List.init n_domains (fun _ -> Domain.spawn domain_work) in
  List.iter Domain.join domains;

  Alcotest.(check int) "all domains completed" n_domains (Atomic.get completed);

  let final =
    match Pool.acquire pool with
    | Ok conn ->
        (match
           Repodb_postgresql.query conn "SELECT value FROM counter WHERE id = 1"
             ~params:[||]
         with
          | Ok [ row ] -> Repodb.Driver.row_int row 0
          | _ -> failwith "Query failed")
        |> fun v ->
        Pool.release pool conn;
        v
    | Error e -> failwith (Pool.error_to_string e)
  in

  Alcotest.(check int) "all increments" (n_domains * n_ops) final;

  (match Pool.acquire pool with
  | Ok conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS counter" ~params:[||]
      in
      Pool.release pool conn
  | Error _ -> ());
  Pool.shutdown pool

let pool_tests =
  [
    ("acquire_release", `Quick, test_pool_acquire_release);
    ("with_connection", `Quick, test_pool_with_connection);
    ("max_size", `Quick, test_pool_max_size);
    ("reuse", `Quick, test_pool_reuse);
    ("validation", `Quick, test_pool_validation);
    ("concurrent_domains", `Slow, test_pool_concurrent_domains);
  ]

let test_date_insert_and_query =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS events" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT, event_date \
           DATE)"
          ~params:[||]
      in
      let date : Ptime.date = (2024, 6, 15) in
      let date_value = Repodb.Types.to_value Repodb.Types.pdate date in
      let insert =
        Repodb_postgresql.exec conn
          "INSERT INTO events (name, event_date) VALUES ($1, $2)"
          ~params:[| Repodb.Driver.Value.text "Conference"; date_value |]
      in
      (match insert with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok () -> ());
      match
        Repodb_postgresql.query conn
          "SELECT event_date FROM events WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "Conference" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok [] -> Alcotest.fail "expected row"
      | Ok (row :: _) -> (
          let raw_value = Repodb.Driver.row_get_idx row 0 in
          match Repodb.Types.of_value Repodb.Types.pdate raw_value with
          | Error e -> Alcotest.fail ("failed to decode date: " ^ e)
          | Ok (y, m, d) ->
              Alcotest.(check int) "year" 2024 y;
              Alcotest.(check int) "month" 6 m;
              Alcotest.(check int) "day" 15 d))

let test_date_comparison =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS events" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT, event_date \
           DATE)"
          ~params:[||]
      in
      let dates =
        [
          ((2024, 1, 10), "Event A");
          ((2024, 6, 15), "Event B");
          ((2024, 12, 25), "Event C");
        ]
      in
      List.iter
        (fun (date, name) ->
          let date_value = Repodb.Types.to_value Repodb.Types.pdate date in
          let _ =
            Repodb_postgresql.exec conn
              "INSERT INTO events (name, event_date) VALUES ($1, $2)"
              ~params:[| Repodb.Driver.Value.text name; date_value |]
          in
          ())
        dates;
      match
        Repodb_postgresql.query conn
          "SELECT name FROM events WHERE event_date > $1 ORDER BY event_date"
          ~params:[| Repodb.Driver.Value.text "2024-06-01" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          Alcotest.(check int) "two events after June 1" 2 (List.length rows);
          Alcotest.(check string)
            "first is Event B" "Event B"
            (Repodb.Driver.row_text (List.nth rows 0) 0);
          Alcotest.(check string)
            "second is Event C" "Event C"
            (Repodb.Driver.row_text (List.nth rows 1) 0))

let test_date_null_handling =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS events" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE events (id SERIAL PRIMARY KEY, name TEXT, event_date \
           DATE)"
          ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "INSERT INTO events (name, event_date) VALUES ($1, $2)"
          ~params:
            [| Repodb.Driver.Value.text "TBD Event"; Repodb.Driver.Value.null |]
      in
      match
        Repodb_postgresql.query_one conn
          "SELECT event_date FROM events WHERE name = $1"
          ~params:[| Repodb.Driver.Value.text "TBD Event" |]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) -> (
          let raw_value = Repodb.Driver.row_get_idx row 0 in
          match
            Repodb.Types.of_value
              (Repodb.Types.option Repodb.Types.pdate)
              raw_value
          with
          | Error e -> Alcotest.fail ("failed to decode: " ^ e)
          | Ok None -> ()
          | Ok (Some _) -> Alcotest.fail "expected None for NULL date"))

let test_date_roundtrip =
  with_db (fun conn ->
      let _ =
        Repodb_postgresql.exec conn "DROP TABLE IF EXISTS events" ~params:[||]
      in
      let _ =
        Repodb_postgresql.exec conn
          "CREATE TABLE events (id SERIAL PRIMARY KEY, event_date DATE)"
          ~params:[||]
      in
      let test_dates =
        [ (2024, 1, 1); (2024, 12, 31); (1999, 6, 15); (2030, 2, 28) ]
      in
      List.iter
        (fun date ->
          let date_value = Repodb.Types.to_value Repodb.Types.pdate date in
          let _ =
            Repodb_postgresql.exec conn
              "INSERT INTO events (event_date) VALUES ($1)"
              ~params:[| date_value |]
          in
          ())
        test_dates;
      match
        Repodb_postgresql.query conn "SELECT event_date FROM events ORDER BY id"
          ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok rows ->
          List.iter2
            (fun expected_date row ->
              let raw_value = Repodb.Driver.row_get_idx row 0 in
              match Repodb.Types.of_value Repodb.Types.pdate raw_value with
              | Error e -> Alcotest.fail ("decode failed: " ^ e)
              | Ok actual_date ->
                  Alcotest.(check (triple int int int))
                    "date roundtrip" expected_date actual_date)
            test_dates rows)

let test_date_current_date =
  with_db (fun conn ->
      match
        Repodb_postgresql.query_one conn "SELECT CURRENT_DATE" ~params:[||]
      with
      | Error e -> Alcotest.fail (Repodb_postgresql.error_message e)
      | Ok None -> Alcotest.fail "expected row"
      | Ok (Some row) -> (
          let raw_value = Repodb.Driver.row_get_idx row 0 in
          match Repodb.Types.of_value Repodb.Types.pdate raw_value with
          | Error e -> Alcotest.fail ("failed to decode CURRENT_DATE: " ^ e)
          | Ok (y, m, d) ->
              Alcotest.(check bool)
                "year reasonable" true
                (y >= 2024 && y <= 2100);
              Alcotest.(check bool) "month valid" true (m >= 1 && m <= 12);
              Alcotest.(check bool) "day valid" true (d >= 1 && d <= 31)))

let date_tests =
  [
    ("insert_and_query", `Quick, test_date_insert_and_query);
    ("comparison", `Quick, test_date_comparison);
    ("null_handling", `Quick, test_date_null_handling);
    ("roundtrip", `Quick, test_date_roundtrip);
    ("current_date", `Quick, test_date_current_date);
  ]

let () =
  Alcotest.run "repodb-postgresql"
    [
      ("Basic", basic_tests);
      ("Indexes", index_tests);
      ("Relations", relation_tests);
      ("Joins", join_tests);
      ("Queries", query_tests);
      ("Advanced", advanced_tests);
      ("Streaming", streaming_tests);
      ("Errors", error_tests);
      ("Preload", preload_tests);
      ("Multi", multi_tests);
      ("Query-Repo", query_repo_tests);
      ("Pool", pool_tests);
      ("Date", date_tests);
    ]