Source file playlistMusics.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/.                 *)
(*                                                                           *)
(*****************************************************************************)

type t = { playlist_id : int64; music_id : int64 }

let table_name = "playlist_musics"

module Column = struct
  let playlist_id = "playlist_id"
  let music_id = "music_id"
end

module Prefix = struct
  let p = Printf.sprintf "%s.%s" table_name
  let playlist_id = p Column.playlist_id
  let music_id = p Column.music_id
end

let init ~playlist_id ~music_id = { playlist_id; music_id }

let sql_table =
  let open Column in
  Printf.sprintf
    {|CREATE TABLE IF NOT EXISTS %s (
    %s INTEGER NOT NULL,
    %s INTEGER NOT NULL,
    FOREIGN KEY (%s) REFERENCES %s (%s)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
    FOREIGN KEY (%s) REFERENCES %s (%s)
      ON UPDATE CASCADE
      ON DELETE CASCADE
    );|}
    table_name playlist_id music_id playlist_id Playlist.table_name
    Playlist.primary_key music_id MusicFile.table_name MusicFile.primary_key

let bind_names t =
  let at = "@" in
  Sqlite3.Data.(
    Column.
      [ (at ^ playlist_id, INT t.playlist_id); (at ^ music_id, INT t.music_id) ]
  )

let columns statement offset =
  let playlist_id = Sqlite3.column_int64 statement offset in
  let music_id = Sqlite3.column_int64 statement (offset + 1) in
  ({ playlist_id; music_id }, 2)

let filter_map database func =
  let request = Printf.sprintf "SELECT * from %s;" table_name in
  let statement = Sqlite3.prepare database request in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun _ ->
        (*HACK: use statement instead of data *)
        let music, _ = columns statement 0 in
        match func music with None -> () | Some music -> Queue.add music queue
    )
  in
  Util.bind_ok database rc @@ fun () -> Ok (List.of_seq (Queue.to_seq queue))

let all database = filter_map database Option.some

let playlist_find_from_id db id =
  let ( let* ) = Util.bind_ok db in
  let playlist_stats =
    Playlist.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 from %s INNER JOIN %s ON %s = %s
      INNER JOIN %s ON %s = %s
      WHERE %s = ?;|}
      Playlist.table_name playlist_stats Playlist.table_name
      (* INNER JOIN *)
      table_name Playlist.Prefix.id Prefix.playlist_id
      (* INNER JOIN *)
      MusicFile.table_name MusicFile.p_id Prefix.music_id
      (* WHERE *)
      Prefix.playlist_id
  in
  let stmt = Sqlite3.prepare db request in
  let* () = Sqlite3.bind_int64 stmt 1 id in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter stmt ~f:(fun _ ->
        (*HACK: use statement instead of data *)
        let playlist, offset = Playlist.columns stmt 0 in
        let stats, _ = Playlist.Stats.columns stmt offset in
        Queue.add (playlist, stats) queue
    )
  in
  Util.bind_ok db rc @@ fun () -> Ok (Queue.take_opt queue)

let playlists_filter_map database func =
  let query =
    let playlist_stats =
      Playlist.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
    Printf.sprintf
      {|SELECT DISTINCT %s.*, %s from %s INNER JOIN %s ON %s = %s
        INNER JOIN %s ON %s = %s
        GROUP BY %s;|}
      Playlist.table_name playlist_stats Playlist.table_name
      (* INNER JOIN *)
      table_name Playlist.Prefix.id Prefix.playlist_id
      (* INNER JOIN *)
      MusicFile.table_name MusicFile.p_id Prefix.music_id
      (* GROUP BY *)
      Playlist.Prefix.id
  in
  let statement = Sqlite3.prepare database query in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun _ ->
        let playlist, offset = Playlist.columns statement 0 in
        let stats, _ = Playlist.Stats.columns statement offset in
        match func (playlist, stats) with
        | Some s ->
            Queue.add s queue
        | None ->
            ()
    )
  in
  let list = queue |> Queue.to_seq |> List.of_seq in
  Util.bind_ok database rc @@ fun () -> Ok list

let playlists database = playlists_filter_map database Option.some

let insert database t =
  let ( let* ) = Util.bind_constraint_ok database in
  let request =
    let open Column in
    Printf.sprintf "INSERT INTO %s (%s, %s) VALUES (@%s, @%s);" table_name
      playlist_id music_id playlist_id music_id
  in
  let values = bind_names t in
  let statement = Sqlite3.prepare database request in
  let* () = Sqlite3.bind_names statement values in
  let* () = Sqlite3.step statement in
  let* () = Sqlite3.finalize statement in
  Ok ()

let remove database t =
  let ( let* ) = Util.bind_ok database in
  let statement =
    let open Column in
    Printf.sprintf "DELETE FROM %s WHERE %s = @%s AND %s = @%s;" table_name
      playlist_id playlist_id music_id music_id
  in
  let statement = Sqlite3.prepare database statement in
  let* () = Sqlite3.bind_names statement (bind_names t) in
  let* () = Sqlite3.step statement in
  let* () = Sqlite3.finalize statement in
  Ok ()

let musics database playlist =
  let ( let* ) = Util.bind_ok database in
  let ss = '\x01' in
  let sv = '\x02' in
  let group_concat =
    Util.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
    LEFT JOIN %s ON %s = %s
    LEFT JOIN %s ON %s = %s
    WHERE %s = ?1 
    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 table_name Prefix.music_id MusicFile.p_id
      Playlist.table_name Playlist.Prefix.id Prefix.playlist_id
      Playlist.Prefix.id MusicFile.p_id
  in
  let statement = Sqlite3.prepare database request in
  let* () = Sqlite3.bind_int64 statement 1 (Playlist.id playlist) in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun _ ->
        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 = Util.sql_participants ~ss participants_blob in
        let properties = Util.sql_properties ~ss ~sv properties_blob in
        Queue.add (music_file, album, participants, properties) queue
    )
  in
  let musics = queue |> Queue.to_seq |> List.of_seq in
  Util.bind_ok database rc @@ fun () -> Ok musics

let playlist_id t = t.playlist_id
let music_id t = t.music_id

(* Test *)
let equal : t -> t -> bool = ( = )
let compare : t -> t -> int = Stdlib.compare