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
      ~music_album_key:(MusicFile.table_name, MusicFile.Column.album_id)
      ~id:MusicFile.p_id ~play_count:MusicFile.p_play_count
      ~duration:MusicFile.p_duration ~size:MusicFile.p_size
      ~rating:MusicFile.p_rating ~played_at:(Some MusicFile.p_played_at)
  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 () = 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 column_prefix table_name field = Printf.sprintf "%s_%s" table_name field

let column_fieled table_name source_table field =
  Printf.sprintf "%s.%s_%s" table_name source_table field

let columns_as_as table_name columns =
  let artist_columns = List.map (column_prefix table_name) columns in
  let sql_artist_columns =
    String.concat ", "
    @@ List.map2
         (fun colname asname ->
           Printf.sprintf "%s.%s as %s" table_name colname asname
         )
         columns artist_columns
  in
  (artist_columns, sql_artist_columns)

let artist_prepare_artists_musics =
  let table_name = "skhc_artists_musics" in
  let artist_columns, a_columns_sql =
    columns_as_as Artist.table_name Artist.Column.all
  in
  let _, m_columns_sql =
    columns_as_as MusicFile.table_name MusicFile.Column.all
  in
  ( table_name,
    artist_columns,
    Printf.sprintf
      {|
  SELECT DISTINCT %s, %s
  FROM %s
  INNER JOIN %s ON %s = %s
  INNER JOIN %s ON %s = %s
  |}
      a_columns_sql m_columns_sql
      (* FROM *)
      MusicFile.table_name
      (* INNER JOIN *)
      MusicParticipant.table_name MusicFile.p_id MusicParticipant.p_music_id
      (* INNER JOIN *)
      Artist.table_name Artist.p_id MusicParticipant.p_artist_id
  )

let artist_prepare_artists_albums =
  let table_name = "skhc_artists_albums" in
  let artist_id = column_prefix Artist.table_name Artist.Column.id in
  let album_id = column_prefix Album.table_name Album.Column.id in
  let album_name = column_prefix Album.table_name Album.Column.name in
  ( table_name,
    artist_id,
    album_id,
    album_name,
    Printf.sprintf
      {|
    SELECT DISTINCT %s as %s, %s as %s, %s as %s
    FROM %s
    INNER JOIN %s ON %s = %s
    INNER JOIN %s ON %s = %s
    WHERE %s = %u
    |}
      Artist.p_id artist_id Album.p_id album_id Album.p_name album_name
      (* FROM *)
      Artist.table_name
      (* INNER JOIN *)
      AlbumParticipant.table_name AlbumParticipant.p_artist_id Artist.p_id
      (* INNER JOIN *)
      Album.table_name Album.p_id AlbumParticipant.p_album_id
      (* WHERE *)
      AlbumParticipant.p_role
      Role.(to_int AlbumArtist)
  )

let statement_artist ~rev database id =
  let artists_musics, artist_columns, query_artists_musics =
    artist_prepare_artists_musics
  in
  let ( artists_albums,
        aa_artist_id,
        aa_album_id,
        aa_album_name,
        query_artists_albums ) =
    artist_prepare_artists_albums
  in
  let artist_columns =
    artist_columns
    |> List.map (Printf.sprintf "%s.%s" artists_musics)
    |> String.concat ", "
  in
  let artist_stats =
    let module C = MusicFile.Column in
    let column_fieled = column_fieled artists_musics MusicFile.table_name in
    let music_count = column_fieled C.id in
    let album_count = Printf.sprintf "%s.%s" artists_albums aa_album_id in
    let duration = column_fieled C.duration in
    let play_count = column_fieled C.play_count in
    let size = column_fieled C.size in
    let avg_rating = column_fieled C.rating in
    let played_at = column_fieled C.played_at in
    Artist.Stats.column_query ~music_count ~album_count ~duration ~play_count
      ~size ~avg_rating ~played_at
  in
  let order_key =
    column_fieled artists_musics Artist.table_name Artist.Column.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 = Printf.sprintf "%s.%s" artists_albums aa_album_id in
    let name = Printf.sprintf "%s.%s" artists_albums aa_album_name in
    sql_artist_album_format ~ss ~id ~name
  in
  let query =
    Printf.sprintf
      {|
  SELECT %s, %s, %s
  FROM (%s) as %s
  LEFT JOIN (%s) as %s
  ON %s.%s = %s
  AND %s.%s = %s
  GROUP BY %s
  %s
  %s
  ;|}
      artist_columns artist_albums artist_stats
      (* FROM *)
      query_artists_musics artists_musics
      (* LEFT JOIN *)
      query_artists_albums artists_albums
      (* ON *)
      artists_albums aa_album_id
      (column_fieled artists_musics MusicFile.table_name
         MusicFile.Column.album_id
      )
      (* AND *)
      artists_albums aa_artist_id order_key
      (* GROUP BY *)
      order_key
      (* 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 albums = Sqlite3.column_text statement offset 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 + 1) in
  (artist, albums, stats)

let rec artists ~ss database f acc statement =
  match Sqlite3.step statement with
  | Sqlite3.Rc.ROW -> (
      let artist, albums, stats = column_artists ~ss statement in
      match f artist albums stats 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