Source file query.ml

(*****************************************************************************)
(*                                                                           *)
(*  Copyright (C) 2026 Yves Ndiaye                                           *)
(*                                                                           *)
(* This Source Code Form is subject to the terms of the Mozilla Public       *)
(* License, v. 2.0. If a copy of the MPL was not distributed with this       *)
(* file, You can obtain one at https://mozilla.org/MPL/2.0/.                 *)
(*                                                                           *)
(*****************************************************************************)

include Util

let ss = '\x01'
let sv = '\x02'

let statement_musics_query ~rev ~ss ~sv database id =
  let group_concat =
    sql_participants_format ~ss ~id:MusicParticipant.p_artist_id
      ~role:MusicParticipant.p_role ~name:Artist.p_name
  in
  let group_properties =
    Printf.sprintf "group_concat(%s || '%c' || %s, '%c')" MusicProperty.p_key sv
      MusicProperty.p_value ss
  in
  (* Use left join to not exclude musics without tags. *)
  let having =
    Option.fold ~none:String.empty
      ~some:
        (Fun.const
        @@ Printf.sprintf "HAVING %s.%s = ?" MusicFile.table_name
             MusicFile.Column.id
        )
      id
  in
  let order_by =
    match rev with
    | None ->
        String.empty
    | Some rev ->
        let f =
          if rev then
            Printf.sprintf "ORDER BY %s.%s DESC"
          else
            Printf.sprintf "ORDER BY %s.%s ASC"
        in
        f MusicFile.table_name MusicFile.Column.id
  in
  let request =
    Printf.sprintf
      {|
SELECT DISTINCT %s.*, %s.*, 
%s name_roles,
%s properties
FROM %s 
LEFT JOIN %s ON %s = %s 
LEFT JOIN %s ON %s = %s 
LEFT JOIN %s ON %s = %s 
LEFT JOIN %s ON %s = %s
LEFT JOIN %s ON %s = %s
GROUP BY %s
%s
%s
;|}
      MusicFile.table_name Album.table_name group_concat group_properties
      MusicFile.table_name AlbumParticipant.table_name MusicFile.p_album_id
      AlbumParticipant.p_album_id Album.table_name MusicFile.p_album_id
      Album.p_id MusicParticipant.table_name MusicFile.p_id
      MusicParticipant.p_music_id Artist.table_name MusicParticipant.p_artist_id
      Artist.p_id MusicProperty.table_name MusicFile.p_id
      MusicProperty.p_music_id MusicFile.p_id having order_by
  in

  let statement = Sqlite3.prepare database request in
  let () =
    Option.iter
      (fun id ->
        let _ = Sqlite3.bind_int64 statement 1 id in
        ()
      )
      id
  in
  statement

let music_columns ~ss ~sv statement =
  let music_file, offset = MusicFile.columns statement 0 in
  let album, offset = Album.columns statement offset in
  let participants_blob = Sqlite3.column_text statement offset in
  let properties_blob = Sqlite3.column_text statement (offset + 1) in
  let participants = sql_participants ~ss participants_blob in
  let properties = sql_properties ~ss ~sv properties_blob in
  (music_file, album, participants, properties)

let rec musics ~ss ~sv database f acc statement =
  match Sqlite3.step statement with
  | Sqlite3.Rc.ROW -> (
      let music_file, album, participants, properties =
        music_columns ~ss ~sv statement
      in
      match f music_file album participants properties with
      | None ->
          musics ~ss ~sv database f acc statement
      | Some elt ->
          musics ~ss ~sv database f (elt :: acc) statement
    )
  | Sqlite3.Rc.DONE ->
      Ok acc
  | rc ->
      let err = Sqlite3.errmsg database in
      Error (rc, err)

let music database id f =
  let statement = statement_musics_query ~rev:None ~sv ~ss database (Some id) in
  Result.map (function [] -> None | t :: _ -> Some t)
  @@ musics ~ss ~sv database f [] statement

let musics ?(rev = false) database f =
  let statement =
    statement_musics_query ~rev:(Some (not rev)) ~ss ~sv database None
  in
  musics ~ss ~sv database f [] statement

(* ALBUMS *)

let statement_album ~rev ~ss database id =
  let group_concat =
    sql_participants_format ~ss ~id:AlbumParticipant.p_artist_id
      ~role:AlbumParticipant.p_role ~name:Artist.p_name
  in
  let album_stats =
    Album.Stats.column_query ~id:MusicFile.p_id
      ~play_count:MusicFile.p_play_count ~duration:MusicFile.p_duration
      ~size:MusicFile.p_size ~rating:MusicFile.p_rating
  in
  let having =
    Option.fold ~none:String.empty
      ~some:
        (Fun.const
        @@ Printf.sprintf "HAVING %s.%s = ?" Album.table_name Album.Column.id
        )
      id
  in
  let order_by =
    match rev with
    | None ->
        String.empty
    | Some rev ->
        let f =
          if rev then
            Printf.sprintf "ORDER BY %s.%s DESC"
          else
            Printf.sprintf "ORDER BY %s.%s ASC"
        in
        f Album.table_name Album.Column.id
  in
  let request =
    Printf.sprintf
      {|
    SELECT DISTINCT %s.*,
    %s,
    %s
    from %s 
    INNER JOIN %s ON %s = %s 
    INNER JOIN %s ON %s = %s
    INNER JOIN %s ON %s = %s
    GROUP BY %s
    %s
    %s
    ;;
  |}
      Album.table_name group_concat album_stats Album.table_name
      AlbumParticipant.table_name Album.p_id AlbumParticipant.p_album_id
      MusicFile.table_name MusicFile.p_album_id Album.p_id Artist.table_name
      Artist.p_id AlbumParticipant.p_artist_id Album.p_id having order_by
  in
  let statement = Sqlite3.prepare database request in
  let () =
    Option.iter
      (fun id ->
        let _ = Sqlite3.bind_int64 statement 1 id in
        ()
      )
      id
  in
  statement

let column_albums ~ss statement =
  let album, offset = Album.columns statement 0 in
  let participants_blob = Sqlite3.column_text statement offset in
  let participants = sql_participants ~ss participants_blob in
  let stats, _ = Album.Stats.columns statement (offset + 1) in
  (album, stats, participants)

let rec albums ~ss database f acc statement =
  match Sqlite3.step statement with
  | Sqlite3.Rc.ROW -> (
      let album, stats, participants = column_albums ~ss statement in
      match f album stats participants with
      | None ->
          albums ~ss database f acc statement
      | Some elt ->
          albums ~ss database f (elt :: acc) statement
    )
  | Sqlite3.Rc.DONE ->
      Ok acc
  | rc ->
      let err = Sqlite3.errmsg database in
      Error (rc, err)

let album database id f =
  let statement = statement_album ~rev:None ~ss database (Some id) in
  Result.map (function [] -> None | t :: _ -> Some t)
  @@ albums ~ss database f [] statement

let albums ?(rev = false) database f =
  let statement = statement_album ~rev:(Some (not rev)) ~ss database None in
  albums ~ss database f [] statement

let artists database f = Artist.filter_map database f

(*let genres_stats database fn =
  let request =
    Printf.sprintf
      {| 
    SELECT %s, COUNT(%s), COUNT(%s) FROM %s 
    INNER JOIN %s ON %s = %s 
    INNER JOIN %s ON %s = %s
    WHERE %s = 'GENRE' GROUP BY %s;
    |}
      MusicProperty.p_value MusicProperty.p_music_id Album.p_id
      MusicFile.table_name
      (* INNER JOIN *)
      MusicProperty.table_name MusicFile.p_id
      MusicProperty.p_music_id (* INNER JOIN *)
      Album.table_name Album.p_id MusicFile.p_album_id
      (* WHERE *)
      MusicProperty.p_key MusicProperty.p_value
  in
  let statement = Sqlite3.prepare database request in
  let result = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun data ->
        let genre = Sqlite3.Data.to_string_exn data.(0) in
        let music_count = Sqlite3.Data.to_int_exn data.(1) in
        let album_count = Sqlite3.Data.to_int_exn data.(2) in
        match fn genre music_count album_count with
        | Some t ->
            Queue.push t result
        | None ->
            ()
    )
  in
  match rc with
  | DONE | OK ->
      Ok (List.of_seq (Queue.to_seq result))
  | rc ->
      let errmsg = Sqlite3.errmsg database in
      Error (rc, errmsg)*)