Proyectos de Subversion Android Microlearning - Inconcert

package com.cesams.twogetskills.inconcert.dao;

import androidx.room.Dao;
import androidx.room.Delete;
import androidx.room.Insert;
import androidx.room.Query;
import androidx.room.Update;

import com.cesams.twogetskills.inconcert.entity.Capsule;
import com.cesams.twogetskills.inconcert.room.ResultCount;

import java.util.List;

@Dao
public interface CapsuleDao {

    @Query("SELECT * FROM tb_capsules WHERE uuid IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c' AND progress <= 100 AND completed = 0 ORDER BY updated_on DESC LIMIT 1) LIMIT 1")
    Capsule selectLastInProgress(String useruUuid);

    @Query("SELECT * FROM tb_capsules WHERE uuid NOT IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c')  ORDER BY name")
    List<Capsule> selectAllPending(String useruUuid);

    @Query("SELECT * FROM tb_capsules WHERE name LIKE :search AND uuid NOT IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c')  ORDER BY name")
    List<Capsule> selectAllPendingWithSearch(String useruUuid, String search);

    @Query("SELECT * FROM tb_capsules WHERE uuid IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c' AND progress <= 100 AND completed = 0 ORDER BY updated_on DESC)  ORDER BY name")
    List<Capsule> selectAllInProgress(String useruUuid);

    @Query("SELECT * FROM tb_capsules WHERE name LIKE :search AND uuid  IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c' AND progress <= 100 AND completed = 0 ORDER BY updated_on DESC)  ORDER BY name")
    List<Capsule> selectAllInProgressWithSearch(String useruUuid, String search);



    @Query("SELECT * FROM tb_capsules WHERE uuid  IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c' AND progress >= 100 AND completed = 1 ORDER BY updated_on DESC)  ORDER BY name")
    List<Capsule> selectAllCompleted(String useruUuid);

    @Query("SELECT * FROM tb_capsules WHERE name LIKE :search AND uuid IN (select capsule_uuid from tb_progress WHERE user_uuid = :useruUuid AND type = 'c' AND progress >= 100 AND completed = 1 ORDER BY updated_on DESC)  ORDER BY name")
    List<Capsule> selectAllCompletedWithSearch(String useruUuid, String search);

    @Query("SELECT * FROM tb_capsules ORDER BY position, name")
    List<Capsule> selectAll();

    @Query("SELECT * FROM tb_capsules WHERE topic_uuid = :topicUuid ORDER BY position, name")
    List<Capsule> selectAllByTopicUuid(String topicUuid);

    @Query("SELECT * FROM tb_capsules WHERE topic_uuid = :topicUuid ORDER BY added_on DESC")
    List<Capsule> selectAllByTopicUuidandOrder(String topicUuid);


    @Query("SELECT * FROM tb_capsules WHERE uuid = :uuid LIMIT 1")
    Capsule selectByUuid(String uuid);

    @Query("SELECT COUNT(*) AS count FROM tb_capsules WHERE topic_uuid = :topicUuid LIMIT 1")
    ResultCount getCountByTopicUuid(String topicUuid);

    @Query("SELECT COUNT(*) AS count FROM tb_capsules LIMIT 1")
    ResultCount getCount();

    @Insert
    void insert(Capsule capsule);


    @Update
    void update(Capsule capsule);

    @Query("UPDATE tb_capsules set total_comments= :total_comments, total_rating= :total_rating where uuid=:uuid")
    void updateByUuid(String uuid, String total_comments, String total_rating);

    @Query("DELETE FROM tb_capsules WHERE uuid = :uuid")
    void removeByUuid(String uuid);

    @Query("DELETE FROM tb_capsules")
    void removeAll();

}