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

package net.piedpiper.bremer.dao

import net.piedpiper.bremer.entity.*
import net.piedpiper.bremer.utils.DaoUtils
import net.piedpiper.bremer.utils.SelectSQLBuilder
import net.piedpiper.bremer.utils.WHERE_IN
import net.piedpiper.bremer.utils.WHERE_LIKES
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")
    @Select("SELECT * FROM audio WHERE slug = #{slug} FOR UPDATE")
    fun findOneBySlugWithLock(@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 = "findAllByTagIdIn")
    fun findAllByTagIdIn(@Param("tagIds") tagIds: 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 = "findAllByNameLikesLimit")
    fun findAllByNameLikesLimit(
        @Param("nameLikes") nameLikes: List<String>,
        @Param("limit") limit: Int
    ): List<AudioEntity>

    @UpdateProvider(type = Sql::class, method = "updateOne")
    fun updateOne(@Param("entity") entity: AudioEntity)

    @Delete("DELETE FROM audio WHERE slug = #{slug}")
    fun deleteOneBySlug(@Param("slug") slug: String): Boolean

    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)
                    .ORDER_BY("audio.album_id, audio.sequence")
                    .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)
                    .ORDER_BY("audio.album_id, audio.sequence")
                    .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)
                    .ORDER_BY("audio.album_id, audio.sequence")
                    .toString()

            @JvmStatic
            fun findAllByTagIdIn(@Param("tagIds") tagIds: 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")
                    .leftOuterJoin(AudioTagEntity::class, "audio.id = audio_tag.audio_id")
                    .toSql()
                    .WHERE_IN("audio_tag.tag_id", "tagIds", tagIds.size)
                    .ORDER_BY("audio_tag.tag_id, audio_tag.sequence")
                    .toString()

            @JvmStatic
            fun findAllByNameLikesLimit(
                @Param("nameLikes") nameLikes: List<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_LIKES("audio.name", "nameLikes", nameLikes.size)
                    .OR()
                    .WHERE_LIKES("audio_name.name", "nameLikes", nameLikes.size)
                    .ORDER_BY("audio.album_id, audio.sequence")
                    .LIMIT("#{limit}")
                    .toString()

            @JvmStatic
            fun updateOne(@Param("entity") entity: AudioEntity): String =
                DaoUtils.updateOne(entity)
        }
    }
}