(* Define record types for the query outputs *)
type post_summary = {
  id: int;
  title: string;
  content: string;
  date: string;
}

type post = {
  id: int;
  title: string;
  content: string;
  date: string;
  tags: string;
  url: string;
}

type comment = {
  id: int;
  content: string;
  date: string;
  tags: string;
  url: string;
}

module Query = struct
  let poasts =
    [%rapper
      get_many
        {sql|
          SELECT @int{id}, @string{title}, @string{content}, @string{date}
          FROM Posts
          ORDER BY id DESC LIMIT 100
        |sql}
        record_out]
  ;;

  let poast =
    [%rapper
      get_opt
        {sql|
          SELECT @int{id}, @string{title}, @string{content}, @string{date}, @string{tags}, @string{url}
          FROM Posts
          WHERE id = %int{post_id}
        |sql}
        record_out]
  ;;

  let comment =
    [%rapper
      get_opt
        {sql|
          SELECT @int{id},  @string{content}, @string{date}, @string{tags}, @string{url}
          FROM Comments
          WHERE id = %int{id}
        |sql}
        record_out]
  ;;

  let user_comments =
    [%rapper
      get_many
        {sql|
          SELECT @int{id},  @string{content}, @string{date}, @string{tags}, @string{url}
          FROM Comments
          WHERE author = %string{username}
        |sql}
        record_out]
  ;;

  let post_comments =
    [%rapper
      get_many
        {sql|
          SELECT @int{id},  @string{content}, @string{date}, @string{tags}, @string{url}
          FROM Comments
          WHERE post_id = %int{post_id}
        |sql}
        record_out]
  ;;

  let comment_children =
    [%rapper
      get_many
        {sql|
          SELECT @int{id},  @string{content}, @string{date}, @string{tags}, @string{url}
          FROM Comments
          WHERE parent= %int{post_id}
        |sql}
        record_out]
  ;;
end

let get_poasts conn = Query.poasts conn
let get_poast post_id conn = Query.poast ~post_id conn

(* SQLite performance pragmas - to be implemented *)
let sqlite_pragmas = [
  "PRAGMA journal_mode = WAL";
  "PRAGMA foreign_keys = ON";
  "PRAGMA cache_size = -8000";
  "PRAGMA temp_store = MEMORY";
  "PRAGMA synchronous = NORMAL";
  "PRAGMA mmap_size = 30000000000";
]

(* Example of how to execute raw SQL with Caqti *)
let init_connection conn =
  let module C = (val conn : Rapper_helper.CONNECTION) in
  (* Create a request: unit input -> unit output using Caqti infix operators *)
  let pragma_req sql = 
    let open Caqti_request.Infix in
    let open Caqti_type in
    (unit ->. unit) sql 
  in
  (* Execute each pragma *)
  List.fold_left (fun acc sql ->
    match acc with
    | Error e -> Error e
    | Ok () -> C.exec (pragma_req sql) ()
  ) (Ok ()) sqlite_pragmas