Source file query.ml

(**********************************************************************************************)
(*                                                                                            *)
(* This file is part of shakuhachi: a music collection manager                                *)
(* Copyright (C) 2025 Yves Ndiaye                                                             *)
(*                                                                                            *)
(* shakuhachi is free software: you can redistribute it and/or modify it under the terms      *)
(* of the GNU General Public License as published by the Free Software Foundation,            *)
(* either version 3 of the License, or (at your option) any later version.                    *)
(*                                                                                            *)
(* shakuhachi is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;    *)
(* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR           *)
(* PURPOSE.  See the GNU General Public License for more details.                             *)
(* You should have received a copy of the GNU General Public License along with shakuhachi.   *)
(* If not, see <http://www.gnu.org/licenses/>.                                                *)
(*                                                                                            *)
(**********************************************************************************************)

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 _ = 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)*)