package com.alterdekim.game.repository;

import com.alterdekim.game.entity.Relationship;
import com.alterdekim.game.entity.User;
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 RelationshipRepository extends JpaRepository<Relationship, Long> {
    @Query(value = "SELECT r FROM Relationship r WHERE ((r.senderId = :userf AND r.recepientId = :users) OR (r.senderId = :users AND r.recepientId = :userf)) AND r.status = :rel")
    Optional<Relationship> findRelation(@Param("userf") Integer userId, @Param("users") Integer userId1, @Param("rel") Relationship.FriendshipStatus state);

    @Transactional
    @Modifying
    @Query(value = "UPDATE Relationship r SET r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Friendship WHERE r.id = :uid")
    void acceptRequestById(@Param("uid") Long id);

    @Query(value = """
        SELECT new User(r.userId, u.username)
        FROM User u 
        JOIN (
                SELECT 
                    r.recepientId AS userId 
                FROM Relationship r 
                WHERE r.senderId = :uid AND r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Friendship 
                UNION 
                 (
                SELECT 
                    r.senderId AS userId 
                FROM Relationship r 
                WHERE r.recepientId = :uid AND r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Friendship
                )
            ) r
        ON r.userId = u.id
        """)
    List<User> findFriendsOfUserById(@Param("uid") Integer userId);

    @Query(value = """
        SELECT new User(r.userId, u.username)
        FROM User u 
        JOIN (
                SELECT 
                    r.senderId AS userId 
                FROM Relationship r 
                WHERE r.recepientId = :uid AND r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Request 
            ) r
        ON r.userId = u.id
        """)
    List<User> findRequestsOfUserById(@Param("uid") Integer userId);

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM Relationship r WHERE ((r.senderId = :uidf AND r.recepientId = :uids) OR (r.senderId = :uids AND r.recepientId = :uidf)) AND r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Friendship")
    void deleteFriendshipByUserIds(@Param("uidf") Integer userId1, @Param("uids") Integer userId2);

    @Transactional
    @Modifying
    @Query(value = """
        UPDATE Relationship r SET 
            r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Friendship 
        WHERE 
            r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Request AND 
            (
                (r.recepientId = :uid AND r.senderId IN :l) OR 
                (r.senderId = :uid AND r.recepientId IN :l)
            )""")
    void acceptFriendshipWithUsers(@Param("uid") Integer userId, @Param("l") List<Integer> users);

    @Transactional
    @Modifying
    @Query(value = """
        DELETE FROM Relationship r WHERE 
            r.status = com.alterdekim.game.entity.Relationship$FriendshipStatus.Request AND 
            (
                (r.recepientId = :uid AND r.senderId IN :l) OR 
                (r.senderId = :uid AND r.recepientId IN :l)
            )""")
    void denyFriendshipsWithUsers(@Param("uid") Integer userId, @Param("l") List<Integer> users);
}
