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. *)
  (* use where since there is no longer a group by*)
  let where' =
    Option.fold ~none:String.empty
      ~some:
        (Fun.const
        @@ Printf.sprintf "WHERE %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 columns =
    List.map (Printf.sprintf "%s.%s" MusicFile.table_name) MusicFile.Column.all
    @ List.map (Printf.sprintf "%s.%s" Album.table_name) Album.Column.all
    @ [
        QueryMusic.MusicPartisQuery.p_roles;
        QueryMusic.MusicPropsQuery.p_properties;
      ]
  in
  let columns = String.concat "," columns in
  let request =
    Printf.sprintf
      {|
SELECT %s FROM %s
LEFT JOIN (%s) as %s ON %s = %s
LEFT JOIN (%s) as %s ON %s = %s
LEFT JOIN %s ON %s = %s
%s
%s
;|}
      columns MusicFile.table_name
      (* LEFT JOIN *)
      QueryMusic.MusicPropsQuery.(sql_table group_properties)
      QueryMusic.MusicPropsQuery.table_name
      QueryMusic.MusicPropsQuery.p_music_id MusicFile.p_id
      (* LEFT JOIN *)
      QueryMusic.MusicPartisQuery.(sql_table group_concat)
      QueryMusic.MusicPartisQuery.table_name
      QueryMusic.MusicPartisQuery.p_music_id MusicFile.p_id
      (* LEFT JOIN *)
      Album.table_name Album.p_id MusicFile.p_album_id
      (* No need of group by*)
      where' order_by
  in
  (*let () = Printf.eprintf "%s\n" request 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 =
    let module Amq = QueryAlbum.AlbumMusicQuery in
    Album.Stats.column_query ~id:Amq.p_music_id ~play_count:Amq.p_play_count
      ~duration:Amq.p_duration ~size:Amq.p_size ~rating:Amq.p_rating
      ~played_at:(Some Amq.p_played_at)
  in
  let p_album_id = QueryAlbum.AlbumMusicQuery.p_album_id in
  let having =
    Option.fold ~none:String.empty
      ~some:(Fun.const @@ Printf.sprintf "HAVING %s = ?" p_album_id)
      id
  in
  let order_by =
    match rev with
    | None ->
        String.empty
    | Some rev ->
        let f =
          if rev then
            Printf.sprintf "ORDER BY %s DESC"
          else
            Printf.sprintf "ORDER BY %s ASC"
        in
        f p_album_id
  in
  let columns = QueryAlbum.AlbumMusicQuery.p_albums in
  let columns = String.concat ", " columns in
  let request =
    Printf.sprintf
      {|
    SELECT DISTINCT %s, %s, %s from (%s) as %s
    INNER JOIN (%s) as %s ON %s = %s
    GROUP BY %s
    %s
    %s;|}
      columns QueryAlbum.AlbumParticipantQuery.p_roles album_stats
      (* FROM *)
      QueryAlbum.AlbumMusicQuery.sql_table QueryAlbum.AlbumMusicQuery.table_name
      (* INNER JOIN *)
      (QueryAlbum.AlbumParticipantQuery.sql_table group_concat)
      QueryAlbum.AlbumParticipantQuery.table_name
      (* ON *)
      QueryAlbum.AlbumMusicQuery.p_album_id
      QueryAlbum.AlbumParticipantQuery.p_album_id
      (* GROUP BY *)
      QueryAlbum.AlbumMusicQuery.p_album_id having order_by
  in
  (*  let () = Printf.printf "%s\n" request 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

(* ARTISTS *)

let statement_artist ~rev database id =
  let artist_stats =
    let module Amq = QueryArtist.ArtistMusicQuery in
    let music_count = Amq.p_music_id in
    let album_count = QueryArtist.ArtistAlbumsQuery.p_album_id in
    let duration = Amq.p_duration in
    let play_count = Amq.p_play_count in
    let size = Amq.p_size in
    let avg_rating = Amq.p_music_rating in
    let played_at = Amq.p_played_at in
    Artist.Stats.column_query ~music_count ~album_count ~duration ~play_count
      ~size ~avg_rating ~played_at
  in
  let order_key = QueryArtist.ArtistMusicQuery.p_artist_id in
  let having =
    Option.fold ~none:String.empty
      ~some:(Fun.const @@ Printf.sprintf "HAVING %s = ?" order_key)
      id
  in
  let order_by =
    match rev with
    | None ->
        String.empty
    | Some rev ->
        let f =
          if rev then
            Printf.sprintf "ORDER BY %s DESC"
          else
            Printf.sprintf "ORDER BY %s ASC"
        in
        f order_key
  in
  let sql_artist_album_format ~ss ~id ~name =
    Printf.sprintf "group_concat(%s || ':' || %s, '%c')" id name ss
  in
  let artist_albums =
    let id = QueryArtist.ArtistAlbumsQuery.p_album_id in
    let name = QueryArtist.ArtistAlbumsQuery.p_album_name in
    sql_artist_album_format ~ss ~id ~name
  in
  let sql_artist_roles_format ~ss ~id =
    Printf.sprintf "group_concat(%s, '%c')" id ss
  in
  let artist_roles = sql_artist_roles_format ~ss ~id:MusicParticipant.p_role in
  let columns =
    QueryArtist.ArtistMusicQuery.p_artists
    @ [ QueryArtist.ArtistMusicQuery.p_roles; artist_albums; artist_stats ]
  in
  let columns = String.concat ", " columns in
  let query =
    Printf.sprintf
      {|
  SELECT %s
  FROM (%s) as %s
  LEFT JOIN (%s) as %s
  ON %s = %s
  AND %s = %s
  GROUP BY %s
  %s
  %s
  ;|}
      columns
      (* FROM *)
      QueryArtist.ArtistMusicQuery.(sql_table artist_roles)
      QueryArtist.ArtistMusicQuery.table_name
      (* LEFT JOIN *)
      QueryArtist.ArtistAlbumsQuery.sql_table
      QueryArtist.ArtistAlbumsQuery.table_name
      (* ON *)
      QueryArtist.ArtistAlbumsQuery.p_album_id
      QueryArtist.ArtistMusicQuery.p_album_id
      (* AND *)
      QueryArtist.ArtistAlbumsQuery.p_artist_id
      QueryArtist.ArtistMusicQuery.p_artist_id
      (* GROUP BY *)
      QueryArtist.ArtistMusicQuery.p_artist_id
      (* HAVING *)
      having
      (* ORDER BY *)
      order_by
  in
  (*let () = Printf.eprintf "%s\n" query in*)
  let statement = Sqlite3.prepare database query in
  let () =
    Option.iter
      (fun id ->
        let _ = Sqlite3.bind_int64 statement 1 id in
        ()
      )
      id
  in
  statement

let column_artists ~ss statement =
  let artist, offset = Artist.columns statement 0 in
  let roles = Sqlite3.column_text statement offset in
  let roles =
    roles |> String.split_on_char ss
    |> List.fold_left
         (fun roles i ->
           match int_of_string_opt i with
           | None ->
               roles
           | Some i ->
               Roles.add (Role.of_int i) roles
         )
         Roles.empty
  in
  let albums = Sqlite3.column_text statement (offset + 1) in
  let albums =
    albums |> String.split_on_char ss
    |> List.fold_left
         (fun map s ->
           match String.split_on_char ':' s with
           | id :: (_ :: _ as xs) -> (
               match Int64.of_string_opt id with
               | Some id ->
                   let name = String.concat ":" xs in
                   Ids.Map.add id name map
               | None ->
                   map
             )
           | _ ->
               map
         )
         Ids.Map.empty
  in

  let stats, _ = Artist.Stats.columns statement (offset + 2) in
  (artist, albums, stats, roles)

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

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

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