package com.alterdekim.game.repository;

import com.alterdekim.game.component.game.AvatarInventoryType;
import com.alterdekim.game.component.game.avatar.IBodyAvatarItem;
import com.alterdekim.game.component.game.avatar.GoodClothType;
import com.alterdekim.game.component.game.avatar.InitMagicAbility;
import com.alterdekim.game.component.game.inventory.InventoryItem;
import com.alterdekim.game.entity.AvatarInventory;
import com.alterdekim.game.entity.Smile;
import jakarta.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface AvatarInventoryRepository extends JpaRepository<AvatarInventory, Long> {

    @Query(value = "SELECT a FROM AvatarInventory a WHERE a.user.id = :uid AND a.type = :type")
    List<AvatarInventory> findItemsByUserIdAndType(@Param("uid") Integer userId, @Param("type") AvatarInventoryType type);

    @Query(value = "SELECT a FROM AvatarInventory a WHERE a.user.id = :uid AND a.type = :type AND a.isUsed = true")
    List<AvatarInventory> findUsedItemsByUserIdAndType(@Param("uid") Integer userId, @Param("type") AvatarInventoryType type);

    @Query(value = "SELECT a FROM AvatarInventory a WHERE a.user.id = :uid AND a.type = :type AND a.goodId = :good_id")
    List<AvatarInventory> findSpecificitem(@Param("uid") Integer userId, @Param("type") AvatarInventoryType type, @Param("good_id") Long goodId);

    @Transactional
    @Modifying
    @Query(value = "UPDATE AvatarInventory a SET a.isUsed = false WHERE a.user.id = :userId AND a.type = :type")
    void resetUsed(@Param("userId") Integer userId, @Param("type") AvatarInventoryType type);

    @Transactional
    @Modifying
    @Query(value = "UPDATE AvatarInventory a SET a.isUsed = true WHERE a.user.id = :userId AND a.type = :type AND a.goodId = :goodId")
    void setUsed(@Param("userId") Integer userId, @Param("goodId") Long goodId, @Param("type") AvatarInventoryType type);

    @Transactional
    @Modifying
    @Query(value = "UPDATE AvatarInventory a SET a.color = :color WHERE a.user.id = :userId AND a.type = :type AND a.goodId = :goodId")
    void setColor(@Param("userId") Integer userId, @Param("goodId") Long goodId, @Param("type") AvatarInventoryType type, @Param("color") Integer color);

    @Query(value = """
                SELECT 
                        a.good_id AS id, 
                        g.trid AS textResourceId, 
                        0 AS bodyPartId, 
                        g.mrid AS mediaResourceId, 
                        (case when (a.is_used > 0) then true else false end) AS isUsed, 
                        false AS isColorable, 
                        0 AS color, 
                        false AS isLimited, 
                        g.publish_date AS date, 
                        0 AS pollution, 
                        false AS isBodyPart, 
                        0 AS phoneId 
                FROM avatar_inventory AS a 
                        JOIN goods AS g 
                ON a.good_id = g.id 
                WHERE a.user_id = :uid AND a.type = :#{#goodTypeId.getVal().name()}""", nativeQuery = true)
    List<InventoryItem> findInventoryItems(@Param("uid") Integer userId, @Param("goodTypeId") GoodClothType goodClothType);

    @Query(value = "SELECT a FROM AvatarInventory a WHERE a.user.id = :uid")
    List<AvatarInventory> findAllByUserId(@Param("uid") Integer userId);

    @Query(value = """
                 SELECT 
                        b.id AS id, 
                        0 AS textResourceId, 
                        0 AS bodyPartId, 
                        0 AS mediaResourceId, 
                        (case when (a.is_used > 0) then true else false end) AS isUsed, 
                        false AS isColorable, 
                        0 AS color, 
                        false AS isLimited, 
                        '' AS date, 
                        0 AS pollution, 
                        false AS isBodyPart, 
                        0 AS phoneId 
                 FROM backgrounds b 
                        JOIN (SELECT a.good_id, a.is_used FROM avatar_inventory a WHERE a.`type` = 'Backgrounds' AND a.user_id = :uid) AS a 
                 ON a.good_id = b.id""", nativeQuery = true)
    List<InventoryItem> findBackgrounds(@Param("uid") Integer userId);

    @Query(value = """
                  SELECT 
                        p.id AS id, 
                        0 AS textResourceId, 
                        0 AS bodyPartId, 
                        0 AS mediaResourceId, 
                        (case when (a.is_used > 0) then true else false end) AS isUsed, 
                        false AS isColorable, 
                        0 AS color, 
                        false AS isLimited, 
                        '' AS date, 
                        0 AS pollution, 
                        false AS isBodyPart, 
                        p.id AS phoneId 
                  FROM phone_icons p 
                        JOIN (SELECT a.good_id, a.is_used FROM avatar_inventory a WHERE a.`type` = 'Phones' AND a.user_id = :uid) AS a 
                  ON a.good_id = p.id""", nativeQuery = true)
    List<InventoryItem> findPhones(@Param("uid") Integer userId);

    @Query(value = """
                  SELECT 
                        b.`type` AS bodyPartTypeId, 
                        b.id AS id, 
                        a.color AS color, 
                        1 AS isBodyPart, 
                        b.id AS bodyPartId, 
                        0 AS goodId, 
                        b.media_resource_id AS mediaResourceId, 
                        b.`type` AS layerId, 
                        0 AS goodTypeId 
                  FROM bodyparts b 
                        JOIN (SELECT a.color, a.good_id FROM avatar_inventory a WHERE a.user_id = :uid AND a.`type` = 'BodyParts' AND a.is_used = true) AS a 
                  ON a.good_id = b.id""", nativeQuery = true)
    List<IBodyAvatarItem> findUsedBodyPartsByUserId(@Param("uid") Integer userId);

    @Query(value = """
                  SELECT 
                        0 AS bodyPartTypeId, 
                        g.id AS id, 
                        0 AS color, 
                        0 AS isBodyPart, 
                        0 AS bodyPartId, 
                        g.id AS goodId, 
                        g.mrid AS mediaResourceId, 
                        g.layer_id AS layerId, 
                        g.good_type_id AS goodTypeId 
                  FROM goods g 
                        JOIN (SELECT a.good_id FROM avatar_inventory a WHERE a.user_id = :uid AND a.`type` = :#{#goodTypeId.getVal().name()} AND a.is_used = true) AS a 
                  ON a.good_id = g.id""", nativeQuery = true)
    List<IBodyAvatarItem> findUsedClothesByUserId(@Param("uid") Integer userId, @Param("goodTypeId") GoodClothType goodClothType);


    @Query(value = """
                   SELECT
                        g.magic_ability_id AS id,
                        '' AS expirationDate,
                        0 AS isLimited
                   FROM gms g
                        JOIN (SELECT a.good_id FROM avatar_inventory a WHERE a.`type` = 'Magic' AND a.user_id = :uid) a
                   ON g.id = a.good_id""", nativeQuery = true)
    List<InitMagicAbility> findMagicAbilitiesByUserId(@Param("uid") Integer userId);

    @Query(value = "SELECT s FROM Smile s WHERE s.id IN (SELECT a.goodId FROM AvatarInventory a WHERE a.user.id = :uid AND a.type = 'Smile')")
    List<Smile> findSmilesByUserId(@Param("uid") Integer userId);
}
