/*
 * Copyright (c) 2023. yo-saito. All Rights Reserved.
 */

package net.piedpiper.bremer.dao

import net.piedpiper.bremer.entity.AlbumEntity
import net.piedpiper.bremer.entity.ArtistEntity
import net.piedpiper.bremer.entity.AudioEntity
import net.piedpiper.bremer.entity.AudioNameEntity
import net.piedpiper.bremer.utils.SelectSQLBuilder
import net.piedpiper.bremer.utils.WHERE_IN
import org.apache.ibatis.annotations.*
import org.springframework.stereotype.Repository

@Repository("bremer.dao.AudioDao")
@Mapper
interface AudioDao {

    @ResultMap("net.piedpiper.bremer.AudioEntity")
    @SelectProvider(type = Sql::class, method = "findOneById")
    fun findOneById(@Param("id") id: Long): AudioEntity?

    @ResultMap("net.piedpiper.bremer.AudioEntity")
    @SelectProvider(type = Sql::class, method = "findOneBySlug")
    fun findOneBySlug(@Param("slug") slug: String): AudioEntity?

    @ResultMap("net.piedpiper.bremer.AudioEntity")
    @SelectProvider(type = Sql::class, method = "findAllByAlbumIdIn")
    fun findAllByAlbumIdIn(@Param("albumIds") albumIds: List<Long>): List<AudioEntity>

    @ResultMap("net.piedpiper.bremer.AudioEntity")
    @SelectProvider(type = Sql::class, method = "findAllByIdIn")
    fun findAllByIdIn(@Param("ids") ids: List<Long>): List<AudioEntity>

    @ResultMap("net.piedpiper.bremer.AudioEntity")
    @SelectProvider(type = Sql::class, method = "findAllBySlugIn")
    fun findAllBySlugIn(@Param("slugs") ids: List<String>): List<AudioEntity>

    @ResultMap("net.piedpiper.bremer.AudioEntity")
    @SelectProvider(type = Sql::class, method = "findAllByNameLikeLimit")
    fun findAllByNameLikeLimit(
        @Param("nameLike") nameLike: String,
        @Param("limit") limit: Int
    ): List<AudioEntity>

    class Sql {
        companion object {
            @JvmStatic
            fun findOneById(): String = SelectSQLBuilder(AudioEntity::class)
                .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id")
                .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class)
                .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id")
                .toSql()
                .WHERE("audio.id = #{id}")
                .toString()

            @JvmStatic
            fun findOneBySlug(): String = SelectSQLBuilder(AudioEntity::class)
                .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id")
                .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class)
                .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id")
                .toSql()
                .WHERE("audio.slug = #{slug}")
                .toString()

            @JvmStatic
            fun findAllByAlbumIdIn(@Param("albumIds") albumIds: List<Long>): String =
                SelectSQLBuilder(AudioEntity::class)
                    .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id")
                    .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class)
                    .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id")
                    .toSql()
                    .WHERE_IN("audio.album_id", "albumIds", albumIds.size)
                    .toString()

            @JvmStatic
            fun findAllByIdIn(@Param("ids") ids: List<Long>): String =
                SelectSQLBuilder(AudioEntity::class)
                    .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id")
                    .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class)
                    .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id")
                    .toSql()
                    .WHERE_IN("audio.id", "ids", ids.size)
                    .toString()

            @JvmStatic
            fun findAllBySlugIn(@Param("slugs") slugs: List<String>): String =
                SelectSQLBuilder(AudioEntity::class)
                    .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id")
                    .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class)
                    .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id")
                    .toSql()
                    .WHERE_IN("audio.slug", "slugs", slugs.size)
                    .toString()

            @JvmStatic
            fun findAllByNameLikeLimit(
                @Param("nameLike") nameLike: String,
                @Param("limit") limit: Long
            ): String =
                SelectSQLBuilder(AudioEntity::class)
                    .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id")
                    .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class)
                    .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id")
                    .toSql()
                    .WHERE(
                        "audio.name LIKE CONCAT('%', #{nameLike}, '%') OR "
                                + "audio_name.name LIKE CONCAT('%', #{nameLike}, '%')"
                    )
                    .ORDER_BY("audio.album_id, audio.sequence")
                    .LIMIT("#{limit}")
                    .toString()
        }
    }
}