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 rec musics ~ss ~sv database f acc statement =
  match Sqlite3.step statement with
  | Sqlite3.Rc.ROW -> (
      (* let headers = Sqlite3.row_names statement in
      let () = Array.iteri (Printf.eprintf "%u - %s\n%!") headers in *)
      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
      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 (List.rev acc)
  | rc ->
      let err = Sqlite3.errmsg database in
      Error (rc, err)

let musics database f =
  let ss = '\x01' in
  let sv = '\x02' in
  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 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;|}
      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
  in
  (*  let _ = Printf.eprintf "%s\n" request in*)
  (*  let _ = Sqlite3.exec database @@ "EXPLAIN QUERY PLAN " ^ request in*)
  let statement = Sqlite3.prepare database request in
  musics ~ss ~sv database f [] statement

let rec albums ~ss database f acc statement =
  match Sqlite3.step statement with
  | Sqlite3.Rc.ROW -> (
      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
      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 (List.rev acc)
  | rc ->
      let err = Sqlite3.errmsg database in
      Error (rc, err)

let albums database f =
  let ss = '\x01' in
  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 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;;
  |}
      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
  in
  let statement = Sqlite3.prepare database request 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)*)